BNK_RCN_DEALR (SQL View)	Index Back
Revaluation: TR Cashflows View that revalues Treasury Cashflows into bank account's default currency

BNK_RCN_DEALR

(SQL View)

Revaluation: TR Cashflows

View that revalues Treasury Cashflows into bank account's default currency

SELECT A.TR_SOURCE_CD , A.SOURCE_BUS_UNIT , A.TR_SOURCE_ID , A.BUSINESS_DATE , A.CASH_FLOW_LINE , A.CASH_FLOW_LEG , A.SEQ_NBR , A.PAY_DOC_SEQ , A.CASH_TYPE , A.AMOUNT , A.CURRENCY_CD , A.BANK_SETID , A.BANK_CD_CUST , A.BANK_CD , A.BANK_ACCT_KEY , A.ACCT_BUS_UNIT_GL , A.BNK_ID_NBR , A.BANK_ACCOUNT_NUM , A.TRANSACTION_LINE , A.RECONCILE_DT , A.RECONCILE_OPRID , A.RECON_TYPE , A.RECON_STATUS , A.RECON_CYCLE_NBR , A.RECORD_SEQ_NUMBER , A.STTLMNT_DT_ACTUAL , A.NET_STATUS , A.APPROVAL_OPRID , A.CPTY_REFERENCE , A.NET_REF_ID , A.CF_SOURCE_REF_ID , A.CP_NETTOR , A.PYMNT_METHOD , A.SETTLEMENT_ID , A.PYMNT_SELCT_STATUS , A.APPROVAL_FLG_TR , A.APPROV_SUBMITTED , A.PYMNT_ID , A.CURRENCY_CD_BASE , B.BASE_CURRENCY_BNK , A.CURRENCY_PYMNT , A.RECON_RUN_ID , %Round(%DecMult(%DecDiv(A.AMOUNT , B.RATE_DIV) , B.RATE_MULT) , B.DECIMAL_POSITIONS) , PMT_ID FROM PS_BNK_RCN_DEALS A , PS_BNK_CURR_RVL_VW B WHERE B.SETID = A.BANK_SETID AND B.BANK_CD = A.BANK_CD AND B.BANK_ACCT_KEY = A.BANK_ACCT_KEY AND B.BNK_ID_NBR = A.BNK_ID_NBR AND B.BANK_ACCOUNT_NUM = A.BANK_ACCOUNT_NUM AND B.TXN_CURRENCY_CD = A.CURRENCY_CD AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_BNK_CURR_RVL_V2 WHERE SETID = B.SETID AND BANK_CD = B.BANK_CD AND BANK_ACCT_KEY = B.BANK_ACCT_KEY AND BASE_CURRENCY_BNK = B.BASE_CURRENCY_BNK AND TXN_CURRENCY_CD = B.TXN_CURRENCY_CD AND EFFDT <= A.BUSINESS_DATE)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 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

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

3 TR_SOURCE_ID Character(12) VARCHAR2(12) NOT NULL An internal work field that represents a unique identifier for records depicting a given treasury po

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

5 CASH_FLOW_LINE Number(15,0) DECIMAL(15) NOT NULL A sequential number that indicates the time order sequence of a given deal transaction's cash flows.

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 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number

8 PAY_DOC_SEQ Number(5,0) INTEGER NOT NULL Advice Sequence

9 CASH_TYPE Character(2) VARCHAR2(2) NOT NULL Represents the type of cash flow stored in the Treasury database.
01=Actual
02=Provisional
03=Sample
04=Deactivated

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

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

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

13 BANK_CD_CUST Character(5) VARCHAR2(5) NOT NULL Represents the customer bank or counterparty in a relationship that pairs differing sets of b

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

15 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

16 ACCT_BUS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL The general ledger business unit associated with a given bank/counterparty account.

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

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

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

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

21 RECONCILE_OPRID Character(30) VARCHAR2(30) NOT NULL The user ID that performed the reconciliation for a given transaction.

22 RECON_TYPE Character(1) VARCHAR2(1) NOT NULL Represents a series of options as to how a treasury wishes to perform reconciliation for a given bank
A=Automatically Reconciled
B=Balance System(s) to BankTran
F=Forced to Reconcile
M=Manually Reconciled
S=Semi Manual
U=Unreconciled

23 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

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

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

26 STTLMNT_DT_ACTUAL Date(10) DATE Date in which a deal transaction is actually settled with a counterparty.

27 NET_STATUS Character(1) VARCHAR2(1) NOT NULL Indicates the counterparty netting status of a given settlement transaction.
D=Netted
N=Net
O=Not Netted

28 APPROVAL_OPRID Character(30) VARCHAR2(30) NOT NULL A system generated value that reflects the operator that approved a given transaction.

29 CPTY_REFERENCE Character(15) VARCHAR2(15) NOT NULL A reference identifier associated with a dealing counterparty transaction.

30 NET_REF_ID Character(12) VARCHAR2(12) NOT NULL A reference identifier associated with a cash transaction subject to netting with a given bank/count

31 CF_SOURCE_REF_ID Character(40) VARCHAR2(40) NOT NULL Reference identifier for a treasury system cash flow.

32 CP_NETTOR Character(30) VARCHAR2(30) NOT NULL Allows the user to indicate whether a given dealing counterparty permits netting of inbound and outbou

33 PYMNT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
ACH=Automated Clearing House
BEF=Draft - Customer EFT
BOO=Draft - Customer Initiated
CHK=System Check
D=Deposit
DD=Direct Debit
DFT=Draft - Supplier Initiated
DRA=Draft
EFT=Electronic Funds Transfer
GE=Giro - EFT
GM=Giro - Manual
LC=Letter of Credit
MAN=Manual Check
TRW=Treasury Wire
WIR=Wire Transfer

34 SETTLEMENT_ID Character(5) VARCHAR2(5) NOT NULL A unique key identifier that represents the settlement instructions for a given bank/counterparty and acc

35 PYMNT_SELCT_STATUS Character(1) VARCHAR2(1) NOT NULL Payment Selection Status
C=Closed
D=Negative Voucher Sum
E=Externally Paid
F=Federal Sanctions Error
I=In Process - EFT
N=Not Selected for Payment
O=Financial Sanctions Review
P=Paid
R=Requested for Payment
S=Settlement Payment
V=Convert Error
X=Canceled
Y=Prepaid

36 APPROVAL_FLG_TR Character(1) VARCHAR2(1) NOT NULL An internal work field that indicates that a given treasury transaction has been approved by
A=Approved
D=Denied
N=None Required
P=Pending Approval

37 APPROV_SUBMITTED Character(1) VARCHAR2(1) NOT NULL An internal work field that indicates whether a treasury deal review and/or confirmation transac

38 PYMNT_ID Character(10) VARCHAR2(10) NOT NULL Payment Number

39 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency

40 BASE_CURRENCY_BNK Character(3) VARCHAR2(3) NOT NULL The base currency of the subject bank general ledger business unit.

41 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency

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

43 BANK_ACCT_RVL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Field used to store the amount on a transaction revalued in the base currency of the transactions bank account.

44 PMT_ID Character(15) VARCHAR2(15) NOT NULL Payment id

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	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
2	SOURCE_BUS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Source Bus Unit
3	TR_SOURCE_ID	Character(12)	VARCHAR2(12) NOT NULL	An internal work field that represents a unique identifier for records depicting a given treasury po
4	BUSINESS_DATE	Date(10)	DATE	Represents a date upon which business is conducted within a given company in a given country.
5	CASH_FLOW_LINE	Number(15,0)	DECIMAL(15) NOT NULL	A sequential number that indicates the time order sequence of a given deal transaction's cash flows.
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	SEQ_NBR	Number(15,0)	DECIMAL(15) NOT NULL	Sequence Number
8	PAY_DOC_SEQ	Number(5,0)	INTEGER NOT NULL	Advice Sequence
9	CASH_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Represents the type of cash flow stored in the Treasury database. 01=Actual 02=Provisional 03=Sample 04=Deactivated
10	AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount
11	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
12	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
13	BANK_CD_CUST	Character(5)	VARCHAR2(5) NOT NULL	Represents the customer bank or counterparty in a relationship that pairs differing sets of b
14	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
15	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
16	ACCT_BUS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	The general ledger business unit associated with a given bank/counterparty account.
17	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
18	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
19	TRANSACTION_LINE	Number(3,0)	SMALLINT NOT NULL	The separate and distinct base instrument type components of a given deal transaction.
20	RECONCILE_DT	Date(10)	DATE	The transaction reconciliation date.
21	RECONCILE_OPRID	Character(30)	VARCHAR2(30) NOT NULL	The user ID that performed the reconciliation for a given transaction.
22	RECON_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Represents a series of options as to how a treasury wishes to perform reconciliation for a given bank A=Automatically Reconciled B=Balance System(s) to BankTran F=Forced to Reconcile M=Manually Reconciled S=Semi Manual U=Unreconciled
23	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
24	RECON_CYCLE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	The bank statement ID.
25	RECORD_SEQ_NUMBER	Number(10,0)	DECIMAL(10) NOT NULL	An internal work field utilized to enumerate database records in bank statement processing.
26	STTLMNT_DT_ACTUAL	Date(10)	DATE	Date in which a deal transaction is actually settled with a counterparty.
27	NET_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Indicates the counterparty netting status of a given settlement transaction. D=Netted N=Net O=Not Netted
28	APPROVAL_OPRID	Character(30)	VARCHAR2(30) NOT NULL	A system generated value that reflects the operator that approved a given transaction.
29	CPTY_REFERENCE	Character(15)	VARCHAR2(15) NOT NULL	A reference identifier associated with a dealing counterparty transaction.
30	NET_REF_ID	Character(12)	VARCHAR2(12) NOT NULL	A reference identifier associated with a cash transaction subject to netting with a given bank/count
31	CF_SOURCE_REF_ID	Character(40)	VARCHAR2(40) NOT NULL	Reference identifier for a treasury system cash flow.
32	CP_NETTOR	Character(30)	VARCHAR2(30) NOT NULL	Allows the user to indicate whether a given dealing counterparty permits netting of inbound and outbou
33	PYMNT_METHOD	Character(3)	VARCHAR2(3) NOT NULL	Payment Method ACH=Automated Clearing House BEF=Draft - Customer EFT BOO=Draft - Customer Initiated CHK=System Check D=Deposit DD=Direct Debit DFT=Draft - Supplier Initiated DRA=Draft EFT=Electronic Funds Transfer GE=Giro - EFT GM=Giro - Manual LC=Letter of Credit MAN=Manual Check TRW=Treasury Wire WIR=Wire Transfer
34	SETTLEMENT_ID	Character(5)	VARCHAR2(5) NOT NULL	A unique key identifier that represents the settlement instructions for a given bank/counterparty and acc
35	PYMNT_SELCT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payment Selection Status C=Closed D=Negative Voucher Sum E=Externally Paid F=Federal Sanctions Error I=In Process - EFT N=Not Selected for Payment O=Financial Sanctions Review P=Paid R=Requested for Payment S=Settlement Payment V=Convert Error X=Canceled Y=Prepaid
36	APPROVAL_FLG_TR	Character(1)	VARCHAR2(1) NOT NULL	An internal work field that indicates that a given treasury transaction has been approved by A=Approved D=Denied N=None Required P=Pending Approval
37	APPROV_SUBMITTED	Character(1)	VARCHAR2(1) NOT NULL	An internal work field that indicates whether a treasury deal review and/or confirmation transac
38	PYMNT_ID	Character(10)	VARCHAR2(10) NOT NULL	Payment Number
39	CURRENCY_CD_BASE	Character(3)	VARCHAR2(3) NOT NULL	Business Unit Base Currency
40	BASE_CURRENCY_BNK	Character(3)	VARCHAR2(3) NOT NULL	The base currency of the subject bank general ledger business unit.
41	CURRENCY_PYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Currency
42	RECON_RUN_ID	Character(15)	VARCHAR2(15) NOT NULL	Recon Run ID
43	BANK_ACCT_RVL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Field used to store the amount on a transaction revalued in the base currency of the transactions bank account.
44	PMT_ID	Character(15)	VARCHAR2(15) NOT NULL	Payment id