BNK_BTBDDEPTRAN (SQL View)	Index Back
Deposits In Transit Use for Book to Bank Reconciliation This is an aggregate query as there can be many payments per deposit (as well as many items per payment).

BNK_BTBDDEPTRAN

(SQL View)

Deposits In Transit

Use for Book to Bank Reconciliation This is an aggregate query as there can be many payments per deposit (as well as many items per payment).

SELECT D.BNK_ID_NBR , D.BANK_ACCOUNT_NUM , A.FISCAL_YEAR , A.ACCOUNTING_PERIOD , B.DEPOSIT_BU , B.DEPOSIT_ID , 0 , A.ACCOUNTING_DT , 'ARDP' , D.RECON_STATUS , D.RECON_CYCLE_NBR , D.RECORD_SEQ_NUMBER , D.RECON_RUN_ID , A.BUSINESS_UNIT_GL , A.LEDGER , A.ACCOUNT , A.JOURNAL_ID , A.JOURNAL_DATE , SUM(A.MONETARY_AMOUNT) , A.CURRENCY_CD , SUM(A.FOREIGN_AMOUNT) , A.FOREIGN_CURRENCY FROM PS_ITEM_DST A , PS_ITEM_ACTIVITY B , PS_PAYMENT C , PS_DEPOSIT_CONTROL D WHERE A.PRIMARY_LEDGER = 'Y' AND B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.CUST_ID = A.CUST_ID AND B.ITEM = A.ITEM AND B.ITEM_LINE = A.ITEM_LINE AND B.ITEM_SEQ_NUM = A.ITEM_SEQ_NUM AND C.DEPOSIT_BU = B.DEPOSIT_BU AND C.DEPOSIT_ID = B.DEPOSIT_ID AND C.PAYMENT_SEQ_NUM = B.PAYMENT_SEQ_NUM AND D.DEPOSIT_BU = C.DEPOSIT_BU AND D.DEPOSIT_ID = C.DEPOSIT_ID AND EXISTS ( SELECT 'X' FROM PS_BANK_ACCT_CHRT WHERE SETID = D.BANK_SETID AND BANK_CD = D.BANK_CD AND BANK_CD_CPTY = D.BANK_CD AND BANK_ACCT_KEY = D.BANK_ACCT_KEY AND COUNTERPARTY = D.BANK_CD AND BANK_ACCT_LED_TYPE IN ('A', 'C', 'H') AND ACCOUNT = A.ACCOUNT ) AND EXISTS ( SELECT 'X' FROM PS_JRNL_HEADER WHERE BUSINESS_UNIT = A.BUSINESS_UNIT_GL AND JOURNAL_ID = A.JOURNAL_ID AND JOURNAL_DATE = A.JOURNAL_DATE AND UNPOST_SEQ = 0 AND LEDGER_GROUP = A.LEDGER_GROUP AND JRNL_HDR_STATUS = 'P' ) GROUP BY D.BNK_ID_NBR, D.BANK_ACCOUNT_NUM, A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.DEPOSIT_BU, B.DEPOSIT_ID, A.ACCOUNTING_DT, D.RECON_STATUS, D.RECON_CYCLE_NBR, D.RECORD_SEQ_NUMBER, D.RECON_RUN_ID, A.BUSINESS_UNIT_GL, A.LEDGER, A.ACCOUNT, A.JOURNAL_ID, A.JOURNAL_DATE, A.CURRENCY_CD, A.FOREIGN_CURRENCY

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.

2 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.

3 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year

4 ACCOUNTING_PERIOD Number(3,0) SMALLINT NOT NULL Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.

5 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit

6 TRAN_REF_ID Character(20) VARCHAR2(20) NOT NULL Represents the unique transaction identifier in bank reconciliation processing.

7 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number

8 TRAN_DT Date(10) DATE Represents the transaction date in bank reconciliation processing.

9 BTB_SOURCE Character(4) VARCHAR2(4) NOT NULL Book To Bank Source
AP=AP Disbursements
ARCC=AR Deposits - Cash Clearing
ARDD=AR Direct Debits
ARDP=AR Deposits
ARDR=AR Drafts
EXAD=Expense Cash Advance
EXPN=Expense Report
GL=Journal Entry
TR0=Other
TRB=Bank Fees
TRD=Treasury Deals
TRE=Deal Fees
TRF=Facility Fees
TRGL=Manual Treasury Entry
TRR=EFT Fees
TRT=Letter of Credit Fees
TRW=EFT Requests
TRX=Bank Transfers
XT=External Transactions

10 RECON_STATUS Character(3) VARCHAR2(3) NOT NULL Reconciliation Status
ALG=Alignment Check
AMB=Ambiguous Match
AMT=Amounts Not Equal
CUR=Currency not Defined
DAT=Tran Date <> Value Date
DPI=Duplicate Input Transactions
DUP=Duplicate Transaction
MSC=Miscellaneous Transaction
NAM=Payee Names Not Equal
NTB=Not Found in Statement
NTF=Not Found In System
NTP=Not Found in POS Data
NUL=Null Transaction ID Ref
OVR=Overflow
PND=Pending Reconciliation
REC=Reconciled
RPR=Reprinted Check
RR=Requires Review - BSAC
STP=Stop Payment
TYP=Wrong Transaction Type
UNR=Unreconciled
VOI=Voided Check

11 RECON_CYCLE_NBR Number(10,0) DECIMAL(10) NOT NULL The bank statement ID.

12 RECORD_SEQ_NUMBER Number(10,0) DECIMAL(10) NOT NULL An internal work field utilized to enumerate database records in bank statement processing.

13 RECON_RUN_ID Character(15) VARCHAR2(15) NOT NULL Recon Run ID

14 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

15 LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger

16 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account

17 JOURNAL_ID Character(10) VARCHAR2(10) NOT NULL Identifies a journal entry, consisting of a header and one or more lines. The Journal ID itself does not have to be unique, but together with the journal business unit and journal date, it forms a unique journal identifier.

18 JOURNAL_DATE Date(10) DATE Specifies the date the journal was created.

19 MONETARY_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.

20 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

21 FOREIGN_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the monetary amount of a debit or credit (accounting entry line) in the entry currency of the item. The sum of FOREIGN_AMOUNT values on AR lines should equal the ENTRY_AMT on the pending item.

22 FOREIGN_CURRENCY Character(3) VARCHAR2(3) NOT NULL Foreign Currency Code

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
2	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
3	FISCAL_YEAR	Number(4,0)	SMALLINT NOT NULL	Fiscal Year
4	ACCOUNTING_PERIOD	Number(3,0)	SMALLINT NOT NULL	Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.
5	SOURCE_BUS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Source Bus Unit
6	TRAN_REF_ID	Character(20)	VARCHAR2(20) NOT NULL	Represents the unique transaction identifier in bank reconciliation processing.
7	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
8	TRAN_DT	Date(10)	DATE	Represents the transaction date in bank reconciliation processing.
9	BTB_SOURCE	Character(4)	VARCHAR2(4) NOT NULL	Book To Bank Source AP=AP Disbursements ARCC=AR Deposits - Cash Clearing ARDD=AR Direct Debits ARDP=AR Deposits ARDR=AR Drafts EXAD=Expense Cash Advance EXPN=Expense Report GL=Journal Entry TR0=Other TRB=Bank Fees TRD=Treasury Deals TRE=Deal Fees TRF=Facility Fees TRGL=Manual Treasury Entry TRR=EFT Fees TRT=Letter of Credit Fees TRW=EFT Requests TRX=Bank Transfers XT=External Transactions
10	RECON_STATUS	Character(3)	VARCHAR2(3) NOT NULL	Reconciliation Status ALG=Alignment Check AMB=Ambiguous Match AMT=Amounts Not Equal CUR=Currency not Defined DAT=Tran Date <> Value Date DPI=Duplicate Input Transactions DUP=Duplicate Transaction MSC=Miscellaneous Transaction NAM=Payee Names Not Equal NTB=Not Found in Statement NTF=Not Found In System NTP=Not Found in POS Data NUL=Null Transaction ID Ref OVR=Overflow PND=Pending Reconciliation REC=Reconciled RPR=Reprinted Check RR=Requires Review - BSAC STP=Stop Payment TYP=Wrong Transaction Type UNR=Unreconciled VOI=Voided Check
11	RECON_CYCLE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	The bank statement ID.
12	RECORD_SEQ_NUMBER	Number(10,0)	DECIMAL(10) NOT NULL	An internal work field utilized to enumerate database records in bank statement processing.
13	RECON_RUN_ID	Character(15)	VARCHAR2(15) NOT NULL	Recon Run ID
14	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
15	LEDGER	Character(10)	VARCHAR2(10) NOT NULL	Ledger
16	ACCOUNT	Character(10)	VARCHAR2(10) NOT NULL	Account
17	JOURNAL_ID	Character(10)	VARCHAR2(10) NOT NULL	Identifies a journal entry, consisting of a header and one or more lines. The Journal ID itself does not have to be unique, but together with the journal business unit and journal date, it forms a unique journal identifier.
18	JOURNAL_DATE	Date(10)	DATE	Specifies the date the journal was created.
19	MONETARY_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.
20	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
21	FOREIGN_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Specifies the monetary amount of a debit or credit (accounting entry line) in the entry currency of the item. The sum of FOREIGN_AMOUNT values on AR lines should equal the ENTRY_AMT on the pending item.
22	FOREIGN_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Foreign Currency Code