BNK_BTBDDEP_VW2 (SQL View)	Index Back
Deposits In Transit-Cash Clear 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_BTBDDEP_VW2

(SQL View)

Deposits In Transit-Cash Clear

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 A.BNK_ID_NBR , A.BANK_ACCOUNT_NUM , A.FISCAL_YEAR , A.ACCOUNTING_PERIOD , A.SOURCE_BUS_UNIT , A.TRAN_REF_ID , A.SEQ_NBR , A.TRAN_DT , A.BTB_SOURCE , A.RECON_STATUS , B.RECON_BANK_DT , A.BUSINESS_UNIT , A.LEDGER , A.ACCOUNT , A.JOURNAL_ID , A.JOURNAL_DATE , A.MONETARY_AMOUNT , A.CURRENCY_CD , A.FOREIGN_AMOUNT , A.FOREIGN_CURRENCY FROM PS_BNK_BTBDDEP_VW A , PS_BANK_STMT_TBL B WHERE B.BNK_ID_NBR = A.BNK_ID_NBR AND B.BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND B.RECON_CYCLE_NBR = A.RECON_CYCLE_NBR AND B.RECORD_SEQ_NUMBER = A.RECORD_SEQ_NUMBER

# 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_BANK_DT Date(10) DATE The bank statement date.

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

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

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

15 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.

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

17 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.

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

19 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.

20 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_BANK_DT	Date(10)	DATE	The bank statement date.
12	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
13	LEDGER	Character(10)	VARCHAR2(10) NOT NULL	Ledger
14	ACCOUNT	Character(10)	VARCHAR2(10) NOT NULL	Account
15	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.
16	JOURNAL_DATE	Date(10)	DATE	Specifies the date the journal was created.
17	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.
18	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
19	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.
20	FOREIGN_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Foreign Currency Code