DAD_CONF3_VW

(SQL View)
Index Back

Deal Confo View3

12/15/97 AK - Created. Rec settlement IDs is present but missing Pay settlement ID. MC - See Treasury Issue FS-MCRANNESS-194 "N"

SELECT DISTINCT H.TREAS_HEADER_ID ,H.CPTY_REFERENCE ,H.CPTY_DEALER ,H.TRANSACTION_DT ,H.DEAL_CONF_STATUS ,D.INSTRMNT_BASE_TYPE ,D.SETTLEMENT_DT ,D.MATURITY_DT ,%DateDiff(D.MATURITY_DT, D.SETTLEMENT_DT) ,D.TRANSACT_CURRENCY ,D.TRANSACTION_AMT ,D.TRANS_CURRENCY_2 ,D.TRANSACTION_AMT_2 ,D.TRANSACT_PROCEEDS ,D.TRANSACTION_RATE ,D.PAY_SETTLEMENT_ID ,' ' ,' ' ,' ' ,D.REC_SETTLEMENT_ID ,SR.BRANCH_ID ,SR.BANK_ACCOUNT_NUM ,SR.DESCR ,BK.BANK_CD ,%subrec(address_sbr,BK) ,' ' ,SR.IBAN_ID FROM PS_TRX_HEADER_TR H ,PS_TRX_DETAIL_TR D ,PS_STL_INSTRUCTION SR ,PS_BANK_ACCT_CPTY CR ,PS_BANK_ACCT_DEFN AR ,PS_BANK_CD_TBL BK WHERE D.BUSINESS_UNIT = H.BUSINESS_UNIT AND D.TREAS_HEADER_ID = H.TREAS_HEADER_ID AND D.PAY_SETTLEMENT_ID = ' ' AND CR.SETID = D.REC_BANK_SETID AND CR.BANK_CD_CUST = D.REC_BANK_CD_CUST AND CR.COUNTERPARTY = D.REC_BANK_CD AND CR.BANK_ACCT_KEY = D.REC_BANK_ACCT_KEY AND AR.SETID = CR.SETID AND AR.BANK_CD = CR.BANK_CD AND AR.BANK_CD_CPTY = CR.BANK_CD_CPTY AND AR.BANK_ACCT_KEY = CR.BANK_ACCT_KEY AND SR.SETID = AR.SETID AND SR.SETTLEMENT_ID = D.REC_SETTLEMENT_ID AND SR.CURRENCY_CD = AR.CURRENCY_CD AND BK.SETID = H.COUNTERPARTY_SETID AND BK.BANK_CD = H.COUNTERPARTY

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 TREAS_HEADER_ID Character(12) VARCHAR2(12) NOT NULL The unique key identifier for a given deal transaction.
2 CPTY_REFERENCE Character(15) VARCHAR2(15) NOT NULL A reference identifier associated with a dealing counterparty transaction.
3 CPTY_DEALER Character(10) VARCHAR2(10) NOT NULL The assigned external dealer or trader associated with a given dealing counterparty.
4 TRANSACTION_DT Date(10) DATE Transaction date (often used as trade date)
5 DEAL_CONF_STATUS Character(2) VARCHAR2(2) NOT NULL Reflects the confirmation status of a given deal transaction at any particular point in time.
01=Pending
02=Confirmed
03=Invalid
6 INSTRMNT_BASE_TYPE Character(2) VARCHAR2(2) NOT NULL Instrument base type used as a building block for Treasury deals
01=Interest Rate Physical
02=Interest Rate Swap
03=FX Deal Physical
04=Option
05=Option - Binary Payoff
06=Futures Contract
07=Commodity
08=Generic Instrument
09=Equity
7 SETTLEMENT_DT Date(10) DATE The settlement date for a given cash -based transaction, or the start date for a Treasury Deal.
8 MATURITY_DT Date(10) DATE The maturity date for a deal transaction.
9 TR_TERM Signed Number(6,0) DECIMAL(5) NOT NULL "Negotiated length of a deal transaction; also
10 TRANSACT_CURRENCY Character(3) VARCHAR2(3) NOT NULL "The nominal
11 TRANSACTION_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL The nominal or actual deal transaction amount.
12 TRANS_CURRENCY_2 Character(3) VARCHAR2(3) NOT NULL "The nominal
13 TRANSACTION_AMT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL The nominal transaction amount for the second leg of a deal transacation.
14 TRANSACT_PROCEEDS Signed Number(28,3) DECIMAL(26,3) NOT NULL The amount of funds that changes hands when a deal is complete.
15 TRANSACTION_RATE Number(16,8) DECIMAL(15,8) NOT NULL The applicable interest rate for a given deal transaction.
16 PAY_SETTLEMENT_ID Character(5) VARCHAR2(5) NOT NULL Represents the settlement instructions identifier associated with the submitter of a
17 BRANCH_ID Character(10) VARCHAR2(10) NOT NULL The local branch office identifier associated with a given bank/counterparty.
18 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
19 DESCR_LN1 Character(50) VARCHAR2(50) NOT NULL Description Line 1
20 REC_SETTLEMENT_ID Character(5) VARCHAR2(5) NOT NULL The settlement instructions associated with the bank and account on the receiving side
21 BRANCH_ID_FLD Character(18) VARCHAR2(18) NOT NULL The local branch office identifier associated with a given bank/counterparty.
22 BANK_ACCOUNT_NUM1 Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given internal partner bank/counterparty acco
23 DESCR_LN2 Character(50) VARCHAR2(50) NOT NULL Description Line 2
24 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
25 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

26 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
27 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
28 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
29 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
30 CITY Character(30) VARCHAR2(30) NOT NULL City
31 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
32 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
33 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
34 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
35 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
36 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
37 COUNTY Character(30) VARCHAR2(30) NOT NULL County
38 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: %EDIT_STATE

39 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
40 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
41 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

42 IBAN_ID Character(34) VARCHAR2(34) NOT NULL IBAN - Internation Bank Account Number is used in European countries to uniquely identify Bank accounts across borders
43 IBAN_ID1 Character(34) VARCHAR2(34) NOT NULL IBAN - Internation Bank Account Number is used in European countries to uniquely identify Bank accounts across borders