DAD_CONF3_VW(SQL View) |
Index Back |
---|---|
Deal Confo View312/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 |