STL_APPROVE_VW (SQL View)	Index Back
Settlement Payments View View of CASH_FLOW_TR for settlement payments 03/15/99 - CMH - Eliminated LOCATION_TBL

STL_APPROVE_VW

(SQL View)

Settlement Payments View

View of CASH_FLOW_TR for settlement payments 03/15/99 - CMH - Eliminated LOCATION_TBL

SELECT CF.TR_SOURCE_CD ,CF.SOURCE_BUS_UNIT ,CF.TR_SOURCE_ID ,CF.BUSINESS_DATE ,CF.CASH_FLOW_LINE ,CF.CASH_FLOW_LEG ,CF.PAY_DOC_SEQ ,CF.CASH_TYPE ,%DecMult(CF.AMOUNT,-1) ,CF.CURRENCY_CD ,CF.BANK_SETID ,CF.BANK_CD_CUST ,CF.BANK_CD ,CF.BANK_ACCT_KEY ,CF.ACCT_BUS_UNIT_GL ,CF.BNK_ID_NBR ,CF.BANK_ACCOUNT_NUM ,CF.RECONCILE_DT ,CF.RECONCILE_OPRID ,CF.RECON_TYPE ,CF.RECON_STATUS ,CF.RECON_CYCLE_NBR ,CF.STTLMNT_DT_ACTUAL ,CF.NET_STATUS ,CF.APPROVAL_OPRID ,CF.CPTY_REFERENCE ,CF.NET_REF_ID ,CF.CF_SOURCE_REF_ID ,CF.CP_NETTOR ,CF.PYMNT_METHOD ,CF.SETTLEMENT_ID ,CF.PYMNT_SELCT_STATUS ,CF.APPROVAL_FLG_TR ,CF.APPROV_SUBMITTED ,CF.PYMNT_ID ,CF.CURRENCY_CD_BASE ,%DecMult(CF.BASE_AMT,-1) ,CF.BASE_CURRENCY_BNK ,CF.BNK_RATE_DIV ,CF.BNK_RATE_MULT ,CF.CUR_RT_TYPE_BNK ,CF.CURRENCY_PYMNT ,CF.PAID_AMT ,CF.PAID_AMT_BNK_BSE ,CF.PAID_AMT_BSE ,CF.PYMNT_RATE_DIV ,CF.PYMNT_RATE_MULT ,CF.PAY_CYCLE ,CF.PAY_CYCLE_SEQ_NUM FROM PS_CASH_FLOW_TR CF

# 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 PAY_DOC_SEQ Number(5,0) INTEGER NOT NULL Advice Sequence

8 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

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

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

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

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

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

14 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

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

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

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

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

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

20 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
Default Value: U

21 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
Default Value: UNR

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

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

24 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
Default Value: O

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

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

27 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

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

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

30 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

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

32 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
Default Value: X

33 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
Default Value: N

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

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

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

37 BASE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents a transaction amount calculated and stored in the base currency of its associated General Ledger bus

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

39 BNK_RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL An internal convention utilized for determining whether a currency exchange spot rate is store

40 BNK_RATE_MULT Number(16,8) DECIMAL(15,8) NOT NULL An internal convention utilized for determining whether a currency exchange spot rate is stored either as

41 CUR_RT_TYPE_BNK Character(5) VARCHAR2(5) NOT NULL Currency Rate Type

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

43 PAID_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount

44 PAID_AMT_BNK_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Paid Amount - Base Bank Unit

45 PAID_AMT_BSE Signed Number(28,3) DECIMAL(26,3) NOT NULL Base Paid Amount

46 PYMNT_RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL An internal convention utilized for determining whether a currency exchange rate is store

47 PYMNT_RATE_MULT Number(16,8) DECIMAL(15,8) NOT NULL An internal convention utilized for determining whether a currency exchange rate is stored

48 PAY_CYCLE Character(6) VARCHAR2(6) NOT NULL Pay Cycle

49 PAY_CYCLE_SEQ_NUM Number(6,0) INTEGER NOT NULL Pay Cycle Sequence Number

#	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	PAY_DOC_SEQ	Number(5,0)	INTEGER NOT NULL	Advice Sequence
8	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
9	AMOUNT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Amount
10	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
11	BANK_SETID	Character(5)	VARCHAR2(5) NOT NULL	The PeopleSoft tableset ID associated with a given bank/counterparty.
12	BANK_CD_CUST	Character(5)	VARCHAR2(5) NOT NULL	Represents the customer bank or counterparty in a relationship that pairs differing sets of b
13	BANK_CD	Character(5)	VARCHAR2(5) NOT NULL	Bank Code
14	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
15	ACCT_BUS_UNIT_GL	Character(5)	VARCHAR2(5) NOT NULL	The general ledger business unit associated with a given bank/counterparty account.
16	BNK_ID_NBR	Character(20)	VARCHAR2(20) NOT NULL	A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
17	BANK_ACCOUNT_NUM	Character(35)	VARCHAR2(35) NOT NULL	The literal unique identifier associated with a given bank/counterparty account.
18	RECONCILE_DT	Date(10)	DATE	The transaction reconciliation date.
19	RECONCILE_OPRID	Character(30)	VARCHAR2(30) NOT NULL	The user ID that performed the reconciliation for a given transaction.
20	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 Default Value: U
21	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 Default Value: UNR
22	RECON_CYCLE_NBR	Number(10,0)	DECIMAL(10) NOT NULL	The bank statement ID.
23	STTLMNT_DT_ACTUAL	Date(10)	DATE	Date in which a deal transaction is actually settled with a counterparty.
24	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 Default Value: O
25	APPROVAL_OPRID	Character(30)	VARCHAR2(30) NOT NULL	A system generated value that reflects the operator that approved a given transaction.
26	CPTY_REFERENCE	Character(15)	VARCHAR2(15) NOT NULL	A reference identifier associated with a dealing counterparty transaction.
27	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
28	CF_SOURCE_REF_ID	Character(40)	VARCHAR2(40) NOT NULL	Reference identifier for a treasury system cash flow.
29	CP_NETTOR	Character(30)	VARCHAR2(30) NOT NULL	Allows the user to indicate whether a given dealing counterparty permits netting of inbound and outbou
30	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
31	SETTLEMENT_ID	Character(5)	VARCHAR2(5) NOT NULL	A unique key identifier that represents the settlement instructions for a given bank/counterparty and acc
32	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 Default Value: X
33	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 Default Value: N
34	APPROV_SUBMITTED	Character(1)	VARCHAR2(1) NOT NULL	An internal work field that indicates whether a treasury deal review and/or confirmation transac Default Value: N
35	PYMNT_ID	Character(10)	VARCHAR2(10) NOT NULL	Payment Number
36	CURRENCY_CD_BASE	Character(3)	VARCHAR2(3) NOT NULL	Business Unit Base Currency
37	BASE_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Represents a transaction amount calculated and stored in the base currency of its associated General Ledger bus
38	BASE_CURRENCY_BNK	Character(3)	VARCHAR2(3) NOT NULL	The base currency of the subject bank general ledger business unit.
39	BNK_RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	An internal convention utilized for determining whether a currency exchange spot rate is store
40	BNK_RATE_MULT	Number(16,8)	DECIMAL(15,8) NOT NULL	An internal convention utilized for determining whether a currency exchange spot rate is stored either as
41	CUR_RT_TYPE_BNK	Character(5)	VARCHAR2(5) NOT NULL	Currency Rate Type
42	CURRENCY_PYMNT	Character(3)	VARCHAR2(3) NOT NULL	Payment Currency
43	PAID_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Paid Amount
44	PAID_AMT_BNK_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Paid Amount - Base Bank Unit
45	PAID_AMT_BSE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Base Paid Amount
46	PYMNT_RATE_DIV	Number(16,8)	DECIMAL(15,8) NOT NULL	An internal convention utilized for determining whether a currency exchange rate is store
47	PYMNT_RATE_MULT	Number(16,8)	DECIMAL(15,8) NOT NULL	An internal convention utilized for determining whether a currency exchange rate is stored
48	PAY_CYCLE	Character(6)	VARCHAR2(6) NOT NULL	Pay Cycle
49	PAY_CYCLE_SEQ_NUM	Number(6,0)	INTEGER NOT NULL	Pay Cycle Sequence Number