TRX_CSHFLW_VW_2 (SQL View)	Index Back
Treasury Deals Cashflows Listing of all Treasury Cashflows. This will include all future flows. Built for Actual v Projected

TRX_CSHFLW_VW_2

(SQL View)

Treasury Deals Cashflows

Listing of all Treasury Cashflows. This will include all future flows. Built for Actual v Projected

SELECT C.ACCT_BUS_UNIT_GL, E.CALENDAR_DATE, H.TREAS_HEADER_ID, C.CASH_FLOW_LEG, C.TRANSACTION_LINE, C.BUSINESS_DATE, C.BANK_ACCOUNT_NUM, C.BNK_ID_NBR, H.COUNTERPARTY, B.BANK_CD, Y.COUNTRY, C.AMOUNT, -C.AMOUNT, C.CURRENCY_CD, H.INSTRUMENT_TYPE, D.INSTRMNT_BASE_TYPE, H.TRANSACTION_ISSUER, H.TRANSACTION_BROKER, H.DEALER_OPRID, H.TRANSACTION_DT, H.TREASURY_PORTFOLIO, B.BANK_TYPE, H.LIMIT_RESERVED, H.DEAL_STATUS, %DATEDIFF( D.SETTLEMENT_DT, D.MATURITY_DT), %DATEDIFF(D.ISSUE_DT, D.MATURITY_DT), D.SETTLEMENT_DT, D.MATURITY_DT, H.FCLTY_ID, C.BANK_SETID, C.BANK_ACCT_KEY, C.RECON_STATUS, C.RECONCILE_DT FROM PS_CASH_FLOW_TR C, PS_BANK_CD_TBL B, PS_CURRENCY_CD_TBL Y, PS_TRX_DETAIL_TR D, PS_TRX_HEADER_TR H, PS_POS_DAYS_TBL E WHERE C.TR_SOURCE_CD = 'D' AND C.CASH_FLOW_LINE <> 0 AND C.NET_STATUS <> 'D' AND B.SETID = C.BANK_SETID AND B.BANK_CD = C.BANK_CD AND C.TR_SOURCE_ID = H.TREAS_HEADER_ID AND C.SOURCE_BUS_UNIT = H.BUSINESS_UNIT AND C.SOURCE_BUS_UNIT = D.BUSINESS_UNIT AND C.TR_SOURCE_ID = D.TREAS_HEADER_ID AND C.TRANSACTION_LINE = D.TRANSACTION_LINE AND Y.CURRENCY_CD = C.CURRENCY_CD and y.effdt = (select max(effdt) from ps_currency_cd_tbl where currency_cd = y.currency_cd and effdt <= %CurrentDateIn ) and Y.eff_status = 'A' and E.CALENDAR_DATE = %CurrentDateIn

Parent record: TRX_DETAIL_TR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 FORECAST_DATE Date(10) DATE Forecast Date

3 TREAS_HEADER_ID Character(12) VARCHAR2(12) NOT NULL The unique key identifier for a given deal transaction.

4 TRANSACTION_LEG Number(1,0) SMALLINT NOT NULL The separate and distinct business positions of a given deal transaction line.
1=Pay Leg
2=Receive Leg

5 TRANSACTION_LINE Number(3,0) SMALLINT NOT NULL The separate and distinct base instrument type components of a given deal transaction.

6 BUSINESS_DATE Date(10) DATE Represents a date upon which business is conducted within a given company in a given country.

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

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

9 COUNTERPARTY Character(5) VARCHAR2(5) NOT NULL Represents a treasury dealing counterpart.

10 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code

11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

12 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount

13 INVERSE_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Inverse amount used for Actual v Projected.

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

15 INSTRUMENT_TYPE Character(10) VARCHAR2(10) NOT NULL Instrument type

16 INSTRMNT_BASE_TYPE Character(2) VARCHAR2(2) NOT NULL Instrument base type used as a building block for Treasury deals
01=Interest Rate Physical
02=Interest Rate Swap
03=FX Deal Physical
04=Option
05=Option - Binary Payoff
06=Futures Contract
07=Commodity
08=Generic Instrument
09=Equity

17 TRANSACTION_ISSUER Character(5) VARCHAR2(5) NOT NULL The entity or organization that sponsors the issuance of a given financial instrument on behalf of the tre

18 TRANSACTION_BROKER Character(5) VARCHAR2(5) NOT NULL "The deal transaction intermediary

19 DEALER_OPRID Character(30) VARCHAR2(30) NOT NULL A system generated value that reflects the operator that originally created and saved a given deal tran

20 TRANSACTION_DT Date(10) DATE Transaction date (often used as trade date)

21 TREASURY_PORTFOLIO Character(15) VARCHAR2(15) NOT NULL A unique key identifier for a position portfolio that may be associated with a given deal transaction.

22 BANK_TYPE Character(1) VARCHAR2(1) NOT NULL "An attribute that determines whether a bank or counterparty is defined as being internal or external
E=External
I=Internal
N=Netting
O=Origin

23 LIMIT_RESERVED Character(1) VARCHAR2(1) NOT NULL An internal workfield that indicates whether a portion of a given treasury position's available exposu
N=UnReserved
Y=Limit Reserved

24 DEAL_STATUS Character(2) VARCHAR2(2) NOT NULL Represents the specific state or point in the life cycle of a deal transaction.
01=Under Negotiation
02=Sample
03=Open
04=Matured
05=Deactivated
06=Rejected
07=Sold / Bought back
08=Forecasted
09=Partially Sold/Bought Back

25 TR_TERM Signed Number(6,0) DECIMAL(5) NOT NULL "Negotiated length of a deal transaction; also

26 TR_ORIGINAL_TERM Signed Number(5,0) DECIMAL(4) NOT NULL Initial stated term for a deal transaction.

27 SETTLEMENT_DT Date(10) DATE The settlement date for a given cash -based transaction, or the start date for a Treasury Deal.

28 MATURITY_DT Date(10) DATE The maturity date for a deal transaction.

29 FCLTY_ID Character(12) VARCHAR2(12) NOT NULL A unique key identifier that represents a given counterparty facility.

30 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.

31 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank

32 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

33 RECONCILE_DT Date(10) DATE The transaction reconciliation date.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	FORECAST_DATE	Date(10)	DATE	Forecast Date
3	TREAS_HEADER_ID	Character(12)	VARCHAR2(12) NOT NULL	The unique key identifier for a given deal transaction.
4	TRANSACTION_LEG	Number(1,0)	SMALLINT NOT NULL	The separate and distinct business positions of a given deal transaction line. 1=Pay Leg 2=Receive Leg
5	TRANSACTION_LINE	Number(3,0)	SMALLINT NOT NULL	The separate and distinct base instrument type components of a given deal transaction.
6	BUSINESS_DATE	Date(10)	DATE	Represents a date upon which business is conducted within a given company in a given country.
7	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
8	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
9	COUNTERPARTY	Character(5)	VARCHAR2(5) NOT NULL	Represents a treasury dealing counterpart.
10	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
11	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country
12	AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount
13	INVERSE_AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Inverse amount used for Actual v Projected.
14	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
15	INSTRUMENT_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Instrument type
16	INSTRMNT_BASE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Instrument base type used as a building block for Treasury deals 01=Interest Rate Physical 02=Interest Rate Swap 03=FX Deal Physical 04=Option 05=Option - Binary Payoff 06=Futures Contract 07=Commodity 08=Generic Instrument 09=Equity
17	TRANSACTION_ISSUER	Character(5)	VARCHAR2(5) NOT NULL	The entity or organization that sponsors the issuance of a given financial instrument on behalf of the tre
18	TRANSACTION_BROKER	Character(5)	VARCHAR2(5) NOT NULL	"The deal transaction intermediary
19	DEALER_OPRID	Character(30)	VARCHAR2(30) NOT NULL	A system generated value that reflects the operator that originally created and saved a given deal tran
20	TRANSACTION_DT	Date(10)	DATE	Transaction date (often used as trade date)
21	TREASURY_PORTFOLIO	Character(15)	VARCHAR2(15) NOT NULL	A unique key identifier for a position portfolio that may be associated with a given deal transaction.
22	BANK_TYPE	Character(1)	VARCHAR2(1) NOT NULL	"An attribute that determines whether a bank or counterparty is defined as being internal or external E=External I=Internal N=Netting O=Origin
23	LIMIT_RESERVED	Character(1)	VARCHAR2(1) NOT NULL	An internal workfield that indicates whether a portion of a given treasury position's available exposu N=UnReserved Y=Limit Reserved
24	DEAL_STATUS	Character(2)	VARCHAR2(2) NOT NULL	Represents the specific state or point in the life cycle of a deal transaction. 01=Under Negotiation 02=Sample 03=Open 04=Matured 05=Deactivated 06=Rejected 07=Sold / Bought back 08=Forecasted 09=Partially Sold/Bought Back
25	TR_TERM	Signed Number(6,0)	DECIMAL(5) NOT NULL	"Negotiated length of a deal transaction; also
26	TR_ORIGINAL_TERM	Signed Number(5,0)	DECIMAL(4) NOT NULL	Initial stated term for a deal transaction.
27	SETTLEMENT_DT	Date(10)	DATE	The settlement date for a given cash -based transaction, or the start date for a Treasury Deal.
28	MATURITY_DT	Date(10)	DATE	The maturity date for a deal transaction.
29	FCLTY_ID	Character(12)	VARCHAR2(12) NOT NULL	A unique key identifier that represents a given counterparty facility.
30	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
31	BANK_ACCT_KEY	Character(4)	VARCHAR2(4) NOT NULL	A user defined unique identifier that facilitates the identification of a given account with a given bank
32	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
33	RECONCILE_DT	Date(10)	DATE	The transaction reconciliation date.