TR_AFSTL_SRCHVW

(SQL View)
 Index Back

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_CD , BANK_ACCT_KEY , NAME1 , BANK_NAME , BRANCH_DESCR , BNK_ID_NBR , BANK_ACCOUNT_NUM ,%DatePart(U.DTTM_CREATED) , U.OPRID ,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 , 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 AND U.EOAWSTEP_STATUS='P' 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_CD , BANK_ACCT_KEY , NAME1 , BANK_NAME , BRANCH_DESCR , BNK_ID_NBR , BANK_ACCOUNT_NUM ,%DatePart(U.DTTM_CREATED ) , U.OPRID ,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 , 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 AND U.EOAWSTEP_STATUS='P'

# 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 EOAWSTART_DT Date(10) DATE Start Date
21 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
22 ORIGINATORID Character(30) VARCHAR2(30) NOT NULL Originator Identifier
23 ENTERED_DT Date(10) DATE Entered on
24 ATTACHMENT_EXIST Character(1) VARCHAR2(1) NOT NULL Attachments Exist
N=N
Y=Y