TR_AFSTL_FLUIDV

(SQL View)
 Index Back

Settlement View Fluid Approval

Treasury Settlements view for Fluid Approval Framework - Page Composer

SELECT DISTINCT W.TR_SOURCE_CD , W.SOURCE_BUS_UNIT , W.TR_SOURCE_ID , W.BUSINESS_DATE , W.CASH_FLOW_LINE , W.CASH_FLOW_LEG , W.SEQ_NBR , W.PYMNT_DT , PYMNT_METHOD , FORMAT_ID , AMOUNT , AMOUNT_CURRENCY_CD , BANK_SETID , BANK_CD , BANK_ACCT_KEY , NAME1 , BANK_NAME , BRANCH_DESCR , BNK_ID_NBR , BANK_ACCOUNT_NUM , X.ORIGINATORID , VALUE_DT , CASE WHEN ATT.TR_SOURCE_ID = ' ' THEN 'N' ELSE 'Y' END , '' , '' , '' , '' , '' , '' , '' , '' FROM PS_STL_DETAIL_VW2 W , PS_TR_AF_STL_XREF X LEFT OUTER JOIN PS_TR_AF_STL_ATTCH ATT ON ATT.TR_SOURCE_CD=X.TR_SOURCE_CD AND ATT.SOURCE_BUS_UNIT =X.SOURCE_BUS_UNIT AND ATT.TR_SOURCE_ID=X.TR_SOURCE_ID AND ATT.BUSINESS_DATE=X.BUSINESS_DATE AND ATT.CASH_FLOW_LINE=X.CASH_FLOW_LINE AND ATT.CASH_FLOW_LEG =X.CASH_FLOW_LEG AND ATT.SEQ_NBR=X.SEQ_NBR WHERE W.TR_SOURCE_CD=X.TR_SOURCE_CD AND W.SOURCE_BUS_UNIT =X.SOURCE_BUS_UNIT AND W.TR_SOURCE_ID=X.TR_SOURCE_ID AND W.BUSINESS_DATE=X.BUSINESS_DATE AND W.CASH_FLOW_LINE=X.CASH_FLOW_LINE AND W.CASH_FLOW_LEG =X.CASH_FLOW_LEG AND W.SEQ_NBR=X.SEQ_NBR UNION SELECT DISTINCT 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.PYMNT_DT , PYMNT_METHOD , FORMAT_ID , AMOUNT , AMOUNT_CURRENCY_CD , BANK_SETID , BANK_CD , BANK_ACCT_KEY , NAME1 , BANK_NAME , BRANCH_DESCR , BNK_ID_NBR , BANK_ACCOUNT_NUM , X.ORIGINATORID , VALUE_DT , CASE WHEN ATT.TR_SOURCE_ID = ' ' THEN 'N' ELSE 'Y' END , '' , '' , '' , '' , '' , '' , '' , '' FROM PS_STL_DETAIL_VW1 A , PS_TR_AF_STL_XREF X LEFT OUTER JOIN PS_TR_AF_STL_ATTCH ATT ON ATT.TR_SOURCE_CD=X.TR_SOURCE_CD AND ATT.SOURCE_BUS_UNIT =X.SOURCE_BUS_UNIT AND ATT.TR_SOURCE_ID=X.TR_SOURCE_ID AND ATT.BUSINESS_DATE=X.BUSINESS_DATE AND ATT.CASH_FLOW_LINE=X.CASH_FLOW_LINE AND ATT.CASH_FLOW_LEG =X.CASH_FLOW_LEG AND ATT.SEQ_NBR=X.SEQ_NBR WHERE A.TR_SOURCE_CD=X.TR_SOURCE_CD AND A.SOURCE_BUS_UNIT =X.SOURCE_BUS_UNIT AND A.TR_SOURCE_ID=X.TR_SOURCE_ID AND A.BUSINESS_DATE=X.BUSINESS_DATE AND A.CASH_FLOW_LINE=X.CASH_FLOW_LINE AND A.CASH_FLOW_LEG =X.CASH_FLOW_LEG AND A.SEQ_NBR=X.SEQ_NBR

# 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 PYMNT_DT Date(10) DATE Payment Date
9 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
10 FORMAT_ID Character(10) VARCHAR2(10) NOT NULL Format ID
11 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
12 AMOUNT_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
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 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
17 BANK_NAME Character(30) VARCHAR2(30) NOT NULL Bank Name
18 BRANCH_DESCR Character(30) VARCHAR2(30) NOT NULL Branch Description
19 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
20 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
21 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
22 ENTERED_DT Date(10) DATE Entered on
23 ATTACHMENT_EXIST Character(1) VARCHAR2(1) NOT NULL Attachments Exist
N=N
Y=Y
24 DETAIL_DESCR Character(30) VARCHAR2(30) NOT NULL Detail Description
25 DESCR Character(30) VARCHAR2(30) NOT NULL Description
26 FORMAT_NAME Character(30) VARCHAR2(30) NOT NULL Layout Name
27 FROM_BANK_NAME Character(30) VARCHAR2(30) NOT NULL From Bank Name
28 FROM_BRANCH_NAME Character(10) VARCHAR2(10) NOT NULL From Branch Name
29 FROM_BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL From Bank ID Number
30 BANK_ACCOUNT_NUM1 Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given internal partner bank/counterparty acco
31 NAME3 Character(40) VARCHAR2(40) NOT NULL Name 3