BNK_BTB_GTRP_VW (SQL View)	Index Back
In Transit: TR CFs thru PCM TR Cash Flows paid through Pay Cycle Manager and posted to GL

BNK_BTB_GTRP_VW

(SQL View)

In Transit: TR CFs thru PCM

TR Cash Flows paid through Pay Cycle Manager and posted to GL

SELECT 'TR' %Concat A.TR_SOURCE_CD ,A.SOURCE_BUS_UNIT ,A.TR_SOURCE_ID ,A.BUSINESS_DATE ,A.CASH_FLOW_LINE ,A.CASH_FLOW_LEG ,Z.RECON_STATUS ,Z.RECON_CYCLE_NBR ,Z.RECORD_SEQ_NUMBER ,Z.RECON_RUN_ID ,Z.STTLMNT_DT_ACTUAL ,A.BNK_ID_NBR ,A.BANK_ACCOUNT_NUM ,D.FISCAL_YEAR ,D.ACCOUNTING_PERIOD ,A.ACCT_BUS_UNIT_GL ,D.LEDGER ,D.ACCOUNT ,C.ACCOUNTING_ID ,D.JOURNAL_ID ,D.JOURNAL_DATE ,D.MONETARY_AMOUNT ,D.CURRENCY_CD ,D.FOREIGN_AMOUNT ,D.FOREIGN_CURRENCY ,A.TR_SOURCE_CD ,I.DESCR FROM PS_CASH_FLOW_TR A ,PS_PAYMENT_TBL Z ,PS_TRA_EVENT_CAL B ,PS_TRA_ACCTG_HDR C ,PS_TRA_ACCTG_LINE D ,PS_JRNL_HEADER I WHERE A.BANK_CD = A.BANK_CD_CUST AND A.PYMNT_ID <> ' ' AND A.CASH_TYPE = '01' AND A.NET_STATUS IN ('O', 'N') AND Z.BANK_SETID = A.BANK_SETID AND Z.BANK_CD = A.BANK_CD AND Z.BANK_ACCT_KEY = A.BANK_ACCT_KEY AND Z.PYMNT_ID = A.PYMNT_ID AND B.BUSINESS_UNIT = A.SOURCE_BUS_UNIT AND B.TR_SOURCE_CD = A.TR_SOURCE_CD AND B.EVENT_ID = A.TR_SOURCE_ID AND B.EVENT_DT = A.BUSINESS_DATE AND B.TRANSACTION_LEG = A.CASH_FLOW_LEG AND (A.TR_SOURCE_CD = 'X' OR (A.TR_SOURCE_CD = 'D' AND B.CASH_FLOW_LINE = A.CASH_FLOW_LINE) OR (A.TR_SOURCE_CD IN ('W', 'F', 'E', 'R', 'T') AND B.SEQ_NBR = A.CASH_FLOW_LINE)) AND B.ACCTG_EVENT_TYPE IN ('01', '02', '03', '06', '07', '08', '14', '21', '24', '25') AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.TRA_SOURCE_CD = B.TR_SOURCE_CD AND C.EVENT_ID = B.EVENT_ID AND C.ACCOUNTING_DT = B.EVENT_DT AND C.SEQ_NBR = B.SEQ_NBR AND C.TRANSACTION_LEG = B.TRANSACTION_LEG AND D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.ACCOUNTING_ID = C.ACCOUNTING_ID AND I.BUSINESS_UNIT = D.BUSINESS_UNIT_GL AND I.JOURNAL_ID = D.JOURNAL_ID AND I.JOURNAL_DATE = D.JOURNAL_DATE AND I.LEDGER_GROUP = D.LEDGER_GROUP AND I.UNPOST_SEQ = 0 AND I.JRNL_HDR_STATUS = 'P' AND EXISTS ( SELECT 'X' FROM PS_BANK_ACCT_CHRT WHERE SETID = A.BANK_SETID AND BANK_CD = A.BANK_CD AND BANK_CD_CPTY = A.BANK_CD AND BANK_ACCT_KEY = A.BANK_ACCT_KEY AND COUNTERPARTY = A.BANK_CD AND BANK_ACCT_LED_TYPE IN ('A', 'C', 'H') AND ACCOUNT = D.ACCOUNT )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 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

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

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

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

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

6 CASH_FLOW_LEG Number(1,0) SMALLINT NOT NULL A sequential number that indicates the relationship between a cash flow and its corresponding deal transaction leg

7 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

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

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

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

11 RECON_BANK_DT Date(10) DATE The bank statement date.

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

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

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

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

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

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

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

19 ACCOUNTING_ID Character(10) VARCHAR2(10) NOT NULL Represents the unique alphanumeric identifier for treasury-specific accounting entries.

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

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

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

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

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

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

26 TR_SOURCE_CD Character(1) VARCHAR2(1) NOT NULL Note: Changes to Translate Values must also be made to TRA_SOURCE_CD and SOURCE_TYPE. These 3 fields must be kept in sync. Values that are inactive are for use in TR Accounting only
B=BSP
D=Deals
E=Deal Fees
F=Facility Fees
H=Hedges
I=Interest
M=Deal: Estimated Maturity Accr
N=Netted Deal
O=Other
P=Investment Pools
R=EFT Fees
S=Securities
T=LC Fees
W=EFT Requests
X=Bank Transfers

27 DESCR Character(30) VARCHAR2(30) NOT NULL Description

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	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
2	SOURCE_BUS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Source Bus Unit
3	TRAN_REF_ID	Character(20)	VARCHAR2(20) NOT NULL	Represents the unique transaction identifier in bank reconciliation processing.
4	TRAN_DT	Date(10)	DATE	Represents the transaction date in bank reconciliation processing.
5	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
6	CASH_FLOW_LEG	Number(1,0)	SMALLINT NOT NULL	A sequential number that indicates the relationship between a cash flow and its corresponding deal transaction leg
7	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
8	RECON_CYCLE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	The bank statement ID.
9	RECORD_SEQ_NUMBER	Number(10,0)	DECIMAL(10) NOT NULL	An internal work field utilized to enumerate database records in bank statement processing.
10	RECON_RUN_ID	Character(15)	VARCHAR2(15) NOT NULL	Recon Run ID
11	RECON_BANK_DT	Date(10)	DATE	The bank statement date.
12	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
13	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
14	FISCAL_YEAR	Number(4,0)	SMALLINT NOT NULL	Fiscal Year
15	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.
16	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
17	LEDGER	Character(10)	VARCHAR2(10) NOT NULL	Ledger
18	ACCOUNT	Character(10)	VARCHAR2(10) NOT NULL	Account
19	ACCOUNTING_ID	Character(10)	VARCHAR2(10) NOT NULL	Represents the unique alphanumeric identifier for treasury-specific accounting entries.
20	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.
21	JOURNAL_DATE	Date(10)	DATE	Specifies the date the journal was created.
22	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.
23	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
24	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.
25	FOREIGN_CURRENCY	Character(3)	VARCHAR2(3) NOT NULL	Foreign Currency Code
26	TR_SOURCE_CD	Character(1)	VARCHAR2(1) NOT NULL	Note: Changes to Translate Values must also be made to TRA_SOURCE_CD and SOURCE_TYPE. These 3 fields must be kept in sync. Values that are inactive are for use in TR Accounting only B=BSP D=Deals E=Deal Fees F=Facility Fees H=Hedges I=Interest M=Deal: Estimated Maturity Accr N=Netted Deal O=Other P=Investment Pools R=EFT Fees S=Securities T=LC Fees W=EFT Requests X=Bank Transfers
27	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description