TR_ID_IPBS_D_VW(SQL View) |
Index Back |
|---|
| SELECT H.BUSINESS_UNIT ,H.TREAS_HEADER_ID ,H.INSTRUMENT_TYPE ,H.TRANSACTION_DT , D.EQUITY_SYMBOL , CASE WHEN H.TREASURY_PORTFOLIO = ' ' THEN 'U' ELSE H.TREASURY_PORTFOLIO END ,D.TRANSACT_CURRENCY ,D.TRANSACTION_AMT ,CASE WHEN M.MTM_ACCTG_VALUE IS NULL THEN D.TRANSACTION_AMT ELSE M.MTM_ACCTG_VALUE END ,CASE WHEN M.MTM_ACCTG_VALUE IS NULL THEN 0 ELSE M.MTM_ACCTG_VALUE - D.TRANSACTION_AMT END ,H.DEAL_STATUS ,H.COUNTERPARTY ,D.PAY_BANK_CD ,D.PAY_BANK_ACCT_KEY 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 IN ('03' ,'04') 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) UNION ALL SELECT H.BUSINESS_UNIT ,H.TREAS_HEADER_ID ,H.INSTRUMENT_TYPE ,H.TRANSACTION_DT , D.EQUITY_SYMBOL , CASE WHEN H.TREASURY_PORTFOLIO = ' ' THEN 'U' ELSE H.TREASURY_PORTFOLIO END ,D.TRANSACT_CURRENCY ,D.TRANSACTION_AMT ,CASE WHEN M.MTM_ACCTG_VALUE IS NULL THEN D.TRANSACTION_AMT ELSE M.MTM_ACCTG_VALUE END ,CASE WHEN M.MTM_ACCTG_VALUE IS NULL THEN 0 ELSE M.MTM_ACCTG_VALUE - D.TRANSACTION_AMT END ,H.DEAL_STATUS ,COUNTERPARTY ,D.PAY_BANK_CD ,D.PAY_BANK_ACCT_KEY 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.INSTRMNT_BASE_TYPE IN ('09') AND D.ASSET_LIABILITY = 'A' AND H.DEAL_STATUS IN ('03' ,'04') 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) UNION ALL SELECT ' ' ,' ' ,' ' , VALUE_DT ,' ' ,'B' , CURRENCY_CD , STMT_BAL_AVAIL , STMT_BAL_AVAIL , 0 ,' ' ,' ' ,' ' ,' ' FROM PS_BANK_BAL_DAILY |
| # | 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 | INSTRUMENT_TYPE | Character(10) | VARCHAR2(10) NOT NULL | Instrument type |
| 4 | TRANSACTION_DT | Date(10) | DATE | Transaction date (often used as trade date) |
| 5 | EQUITY_SYMBOL | Character(15) | VARCHAR2(15) NOT NULL | Ticker symbol to identify an equity on the exchange that it is traded on |
| 6 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 7 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 8 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 9 | AMOUNT1 | Signed Number(27,2) | DECIMAL(25,2) NOT NULL | Amount |
| 10 | VARIANCE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Variance Amount |
| 11 | DEAL_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
Represents the specific state or point in the life cycle of a deal transaction.
01=Under Negotiation 02=Sample 03=Open 04=Matured 05=Deactivated 06=Rejected 07=Sold / Bought back 08=Forecasted 09=Partially Sold/Bought Back |
| 12 | COUNTERPARTY | Character(5) | VARCHAR2(5) NOT NULL | Represents a treasury dealing counterpart. |
| 13 | PAY_BANK_CD | Character(5) | VARCHAR2(5) NOT NULL | The bank for the disbursing side of a settlement transaction. |
| 14 | PAY_BANK_ACCT_KEY | Character(4) | VARCHAR2(4) NOT NULL | The bank account for the disbursing side of a settlement transaction. |