BNK_BTB_DPTRAN2 (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_BTB_DPTRAN2

(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 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_BTB_DEPTRAN 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