STL_SRC_MAP_VW

(SQL View)
 Index Back

Settlements View Map Approvals

Treasury Settlements view for Map Approvals

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 , W.PYMNT_METHOD , W.FORMAT_ID , W.AMOUNT , W.AMOUNT_CURRENCY_CD , W.BANK_CD , W.BANK_ACCT_KEY , W.NAME1 , W.BANK_NAME , W.BRANCH_DESCR , W.BNK_ID_NBR , W.BANK_ACCOUNT_NUM , W.VALUE_DT , CASE WHEN ATT.TR_SOURCE_ID = ' ' THEN 'N' ELSE 'Y' END , %DatePart(U.DTTM_CREATED) , U.OPRID , U.EOAWORIG_OPRID , U.EOAWDTTM_COMPLETE , S.EOAWTHREAD_ID , U.EOAWSTEP_STATUS , S.EOAWPRCS_ID , S.EOAWDEFN_ID , S.EFFDT , S.EOAWSTEP_NBR , X.ORIGINATORID , X.EOAWREQUESTOR_ID , X.EOAWTHREAD_STATUS , X.EOAWDTTM_MODIFIED , U.DTTM_CREATED , CASE WHEN W.PYMNT_METHOD = 'WIR' THEN '1' ELSE '2' 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 , PS_EOAW_STEPINST S , PS_EOAW_USERINST U 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 AND X.EOAWTHREAD_ID = S.EOAWTHREAD_ID AND X.EOAWPRCS_ID = S.EOAWPRCS_ID AND X.EOAWDEFN_ID = S.EOAWDEFN_ID AND S.EOAWSTEP_INSTANCE = U.EOAWSTEP_INSTANCE 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 , A.PYMNT_METHOD , A.FORMAT_ID , A.AMOUNT , A.AMOUNT_CURRENCY_CD , A.BANK_CD , A.BANK_ACCT_KEY , A.NAME1 , A.BANK_NAME , A.BRANCH_DESCR , A.BNK_ID_NBR , A.BANK_ACCOUNT_NUM , A.VALUE_DT , CASE WHEN ATT.TR_SOURCE_ID = ' ' THEN 'N' ELSE 'Y' END , %DatePart(U.DTTM_CREATED) , U.OPRID , U.EOAWORIG_OPRID , U.EOAWDTTM_COMPLETE , S.EOAWTHREAD_ID , U.EOAWSTEP_STATUS , S.EOAWPRCS_ID , S.EOAWDEFN_ID , S.EFFDT , S.EOAWSTEP_NBR , X.ORIGINATORID , X.EOAWREQUESTOR_ID , X.EOAWTHREAD_STATUS , X.EOAWDTTM_MODIFIED , U.DTTM_CREATED , CASE WHEN A.PYMNT_METHOD = 'WIR' THEN '1' ELSE '2' 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 , PS_EOAW_STEPINST S , PS_EOAW_USERINST U 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 AND X.EOAWTHREAD_ID = S.EOAWTHREAD_ID AND X.EOAWPRCS_ID = S.EOAWPRCS_ID AND X.EOAWDEFN_ID = S.EOAWDEFN_ID AND S.EOAWSTEP_INSTANCE = U.EOAWSTEP_INSTANCE

# 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_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 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
16 BANK_NAME Character(30) VARCHAR2(30) NOT NULL Bank Name
17 BRANCH_DESCR Character(30) VARCHAR2(30) NOT NULL Branch Description
18 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
19 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
20 ENTERED_DT Date(10) DATE Entered on
21 ATTACHMENT_EXIST Character(1) VARCHAR2(1) NOT NULL Attachments Exist
N=N
Y=Y
22 EOAWSTART_DT Date(10) DATE Start Date
23 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
24 EOAWORIG_OPRID Character(30) VARCHAR2(30) NOT NULL User ID
25 EOAWDTTM_COMPLETE DateTime(26) TIMESTAMP Date/Time Completed
26 EOAWTHREAD_ID Number(15,0) DECIMAL(15) NOT NULL Approval Framework Thread ID
27 EOAWSTEP_STATUS Character(1) VARCHAR2(1) NOT NULL Step Instance Status
A=Approved
B=Pushed back
C=Auto Approved
D=Denied
E=Administrator denied
F=Deleted
G=Bypassed
H=On Hold
I=No Action Taken
K=Skipped
L=Terminated
M=Administrator approved
N=Not Active
P=Pending
R=No longer in role
T=Terminated
U=Auto approved
X=Deleted
Z=Pending Denial
28 EOAWPRCS_ID Character(30) VARCHAR2(30) NOT NULL Approval Process ID
29 EOAWDEFN_ID Character(30) VARCHAR2(30) NOT NULL Approval Framework Definition ID
30 EFFDT Date(10) DATE Effective Date

Default Value: %date

31 EOAWSTEP_NBR Number(6,2) DECIMAL(5,2) NOT NULL Sequence number of step in its path
32 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
33 EOAWREQUESTOR_ID Character(30) VARCHAR2(30) NOT NULL Requestor id
34 EOAWTHREAD_STATUS Character(1) VARCHAR2(1) NOT NULL Thread Status
A=Approved
C=Complete
D=Denied
E=Pending Denial
H=Hard Deny
I=Initial
N=Not Active
P=Pending
S=Awaiting Further Approvals
T=Terminated
W=Canceled
X=Suspended/Pending Denial
35 EOAWDTTM_MODIFIED DateTime(26) TIMESTAMP Datetime modified
36 DTTM_CREATED DateTime(26) TIMESTAMP Datetime Created
37 PRIORITY_FLG Character(1) VARCHAR2(1) NOT NULL Priority Flag
1=High
2=Medium
3=Low