TR_IPIT_DR_VW(SQL View) |
Index Back |
|---|---|
Treasury Investment Type |
| SELECT H.BUSINESS_UNIT , H.TREAS_HEADER_ID ,D.SETTLEMENT_DT , D.MATURITY_DT ,D.INSTRMNT_BASE_TYPE , D.ASSET_LIABILITY , D.RATE_RESET_TYPE ,D.TRANSACT_CURRENCY ,D.TRANSACTION_AMT ,M.MTM_ACCTG_VALUE ,D.PAY_BANK_CD ,D.PAY_BANK_ACCT_KEY ,D.PAY_BANK_ACCT_NUM ,CASE WHEN H.TREASURY_PORTFOLIO = ' ' THEN 'U' ELSE H.TREASURY_PORTFOLIO END FROM PS_TRX_HEADER_TR H INNER JOIN PS_TRX_DETAIL_TR D ON H.BUSINESS_UNIT = D.BUSINESS_UNIT AND H.TREAS_HEADER_ID = D.TREAS_HEADER_ID LEFT JOIN PS_RSK_DEAL_MTM M ON H.BUSINESS_UNIT = M.BUSINESS_UNIT AND H.TREAS_HEADER_ID = M.TREAS_HEADER_ID WHERE D.MATURITY_DT >= %CurrentDateIn AND D.INSTRMNT_BASE_TYPE IN ('01') AND D.ASSET_LIABILITY = 'A' AND H.DEAL_STATUS='03' AND D.SETTLEMENT_DT <= %CurrentDateIn AND (M.AS_OF_DATE IN (( SELECT MAX(MTM.AS_OF_DATE) FROM PS_RSK_DEAL_MTM MTM WHERE H.BUSINESS_UNIT = MTM.BUSINESS_UNIT AND H.TREAS_HEADER_ID = MTM.TREAS_HEADER_ID AND MTM.AS_OF_DATE <= %CurrentDateIn)) OR M.AS_OF_DATE IS NULL) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | TREAS_HEADER_ID | Character(12) | VARCHAR2(12) NOT NULL | The unique key identifier for a given deal transaction. |
| 3 | SETTLEMENT_DT | Date(10) | DATE | The settlement date for a given cash -based transaction, or the start date for a Treasury Deal. |
| 4 | MATURITY_DT | Date(10) | DATE | The maturity date for a deal transaction. |
| 5 | 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 |
| 6 | ASSET_LIABILITY | Character(1) | VARCHAR2(1) NOT NULL |
Whether the deal transaction leg represents an asset or a liability
A=Asset L=Liability |
| 7 | RATE_RESET_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
Describes whether a given deal transaction is subject to a fixed or floating interest rate.
FL=Floating FX=Fixed |
| 8 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 9 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 10 | AMOUNT1 | Signed Number(27,2) | DECIMAL(25,2) NOT NULL | Amount |
| 11 | PAY_BANK_CD | Character(5) | VARCHAR2(5) NOT NULL | The bank for the disbursing side of a settlement transaction. |
| 12 | PAY_BANK_ACCT_KEY | Character(4) | VARCHAR2(4) NOT NULL | The bank account for the disbursing side of a settlement transaction. |
| 13 | PAY_BANK_ACCT_NUM | Character(35) | VARCHAR2(35) NOT NULL | The bank account number for the disbursing side of a settlement transaction. |
| 14 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |