TR_DASH_03_VW(SQL View) |
Index Back |
|---|
| SELECT DISTINCT B.OPRID , A.BUSINESS_UNIT , A.TREAS_HEADER_ID , A.INSTRMNT_BASE_TYPE , A.SETTLEMENT_DT , A.MATURITY_DT , A.TRANSACTION_PRICE , A.RATE_RESET_TYPE , A.TRANSACTION_AMT , A.TRANSACT_CURRENCY , A.TRANSACTION_AMT_2 , A. TRANS_CURRENCY_2 , C.DEAL_STATUS , C.SALE_SETTLEMENT_DT , C.SALE_TRANS_AMT , C.TREAS_NEXT_ID , C.TREAS_PREVIOUS_ID , C.TREAS_MASTER_ID , A.ASSET_LIABILITY , CASE WHEN %DateDiff(A.SETTLEMENT_DT, A.MATURITY_DT) > B.TR_TERM THEN 'L' WHEN %DateDiff(A.SETTLEMENT_DT,A.MATURITY_DT) <= B.TR_TERM THEN 'S'END FROM PS_TRX_DETAIL_TR A LEFT OUTER JOIN PS_TR_DASH_03_CFG B ON 1 = 1 , PS_TRX_HEADER_TR C , PS_INSTR_DETAIL_TR D WHERE C.BUSINESS_UNIT = A.BUSINESS_UNIT AND C.TREAS_HEADER_ID = A.TREAS_HEADER_ID AND D.SETID = ( SELECT R.SETID FROM PS_SET_CNTRL_REC R WHERE R.SETCNTRLVALUE = C.BUSINESS_UNIT AND R.RECNAME = 'INSTR_DETAIL_TR' ) AND D.INSTRUMENT_TYPE = C.INSTRUMENT_TYPE AND D.INSTRMNT_TEMPLATE = C.INSTRMNT_TEMPLATE AND D.INSTRUMENT_LINE = A.TRANSACTION_LINE AND D.OPEN_MATDT_CB <> 'Y' AND C.DEAL_STATUS IN ('03','04','07','09') |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 3 | TREAS_HEADER_ID | Character(12) | VARCHAR2(12) NOT NULL | The unique key identifier for a given deal transaction. |
| 4 | 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 |
| 5 | SETTLEMENT_DT | Date(10) | DATE | The settlement date for a given cash -based transaction, or the start date for a Treasury Deal. |
| 6 | MATURITY_DT | Date(10) | DATE | The maturity date for a deal transaction. |
| 7 | TRANSACTION_PRICE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | The preferred business price for a given deal transaction. |
| 8 | 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 |
| 9 | TRANSACTION_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | The nominal or actual deal transaction amount. |
| 10 | TRANSACT_CURRENCY | Character(3) | VARCHAR2(3) NOT NULL | "The nominal |
| 11 | TRANSACTION_AMT_2 | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | The nominal transaction amount for the second leg of a deal transacation. |
| 12 | TRANS_CURRENCY_2 | Character(3) | VARCHAR2(3) NOT NULL | "The nominal |
| 13 | 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 |
| 14 | SALE_SETTLEMENT_DT | Date(10) | DATE | The agreed upon settlement date for a buy/sellback transaction. |
| 15 | SALE_TRANS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Unwind Par Amount |
| 16 | TREAS_NEXT_ID | Character(12) | VARCHAR2(12) NOT NULL | The new deal id created by Partial Sell/Buy back activity. |
| 17 | TREAS_PREVIOUS_ID | Character(12) | VARCHAR2(12) NOT NULL | Previous ID for a given deal header for Partial Sell/Buy back. |
| 18 | TREAS_MASTER_ID | Character(12) | VARCHAR2(12) NOT NULL | Master ID for a given deal header. |
| 19 | ASSET_LIABILITY | Character(1) | VARCHAR2(1) NOT NULL |
Whether the deal transaction leg represents an asset or a liability
A=Asset L=Liability |
| 20 | TR_DASH_TERM_TYPE | Character(1) | VARCHAR2(1) NOT NULL | Treasury Term Type |