View: XTR_REVIEW_ALL_CONTRACTS_V

Product:	XTR - Treasury
Description:	This view contains full deal information for all instruments with the exception of exposure transactions. Its main purpose is to inquire on deal information across all deal types.
Implementation/DBA Data:	APPS.XTR_REVIEW_ALL_CONTRACTS_V

View Text

SELECT D.DEAL_NO CONTRACT_NO
, 1 ROLLOVER_TRANSACTION_NO
, D.COMPANY_CODE COMPANY
, D.CPARTY_CODE COUNTERPARTY
, D.CLIENT_CODE CLIENT
, D.ACCEPTOR_NAME ACCEPTOR
, D.ENDORSER_NAME ENDORSER
, D.DRAWER_NAME DRAWER
, D.DEAL_TYPE CONTRACT_TYPE
, DT.USER_DEAL_TYPE
, ST.DEAL_SUBTYPE CONTRACT_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, NVL(D.PRODUCT_TYPE
, 'NOT APPLIC') PRODUCT
, NVL(D.PORTFOLIO_CODE
, 'NOTAPPLIC') PORTFOLIO
, DECODE(D.DEAL_TYPE
, 'FX'
, D.CURRENCY_BUY
, 'FXO'
, D.CURRENCY_BUY
, D.CURRENCY) CURRENCY
, D.CURRENCY_SELL CURRENCY_SELL
, D.DEAL_DATE CONTRACT_DEALT_ON
, D.DUAL_AUTHORISATION_ON AUTHORISED_ON
, DECODE(D.DEAL_TYPE
, 'FX'
, D.VALUE_DATE
, 'FXO'
, D.OPTION_COMMENCEMENT
, 'IRO'
, D.OPTION_COMMENCEMENT
, D.START_DATE) COMMENCE_DATE
, D.MATURITY_DATE MATURITY_DATE
, D.SETTLE_DATE SETTLEMENT_DATE
, D.PREMIUM_DATE PREMIUM_DATE
, D.EXPIRY_DATE OPTION_EXPIRY
, DECODE(D.DEAL_TYPE
, 'FX'
, D.BUY_AMOUNT
, 'FXO'
, D.BUY_AMOUNT
, 'FRA'
, D.FACE_VALUE_AMOUNT
, 'FUT'
, D.FACE_VALUE_AMOUNT
, 'TMM'
, D.FACE_VALUE_AMOUNT
, 'IRO'
, NVL(D.FACE_VALUE_AMOUNT
, D.MATURITY_AMOUNT)
, 'IRS'
, D.FACE_VALUE_AMOUNT
, 'DEB'
, D.FACE_VALUE_AMOUNT
, 'SWPTN'
, D.FACE_VALUE_AMOUNT
, D.MATURITY_AMOUNT) AMOUNT
, D.SELL_AMOUNT SELL_AMOUNT
, D.START_AMOUNT CONSIDERATION_AMOUNT
, D.SETTLE_AMOUNT SETTLEMENT_AMOUNT
, D.PREMIUM_AMOUNT PREMIUM_AMOUNT
, D.BROKERAGE_AMOUNT BROKER_AMOUNT
, D.TAX_AMOUNT TAX_AMOUNT
, DECODE(D.DEAL_TYPE
, 'FX'
, D.TRANSACTION_RATE
, 'FXO'
, D.TRANSACTION_RATE
, 'FUT'
, D.CONTRACT_RATE
, D.INTEREST_RATE) CONTRACT_RATE
, D.COUPON_RATE COUPON_RATE
, D.BROKERAGE_RATE BROKER_RATE
, D.TAX_RATE TAX_RATE
, D.SETTLE_RATE SETTLEMENT_RATE
, D.CREATED_ON ENTERED_INTO_SYSTEM_ON
, D.CREATED_BY ENTERED_INTO_SYSTEM_BY
, D.DEALER_CODE COMPANY_DEALER
, D.DUAL_AUTHORISATION_BY AUTHORISED_BY
, DECODE(D.DEAL_TYPE
, 'STOCK'
, D.SETTLE_ACCOUNT_NO
, 'NI'
, D.MATURITY_ACCOUNT_NO
, 'BOND'
, D.MATURITY_ACCOUNT_NO
, D.START_ACCOUNT_NO) COMPANY_SETTLEMENT_ACCT
, BA.BANK_SHORT_CODE COUNTERPARTY_ACCT_REF
, D.PREMIUM_ACCOUNT_NO PREMIUM_ACCT
, D.BUY_ACCOUNT_NO FX_BUY_SETTLEMENT_ACCT
, D.SELL_ACCOUNT_NO FX_SELL_SETTLEMENT_ACCT
, D.SETTLE_ACCOUNT_NO SETTLEMENT_ACCT
, D.STATUS_CODE CONTRACT_STATUS
, DS.USER_STATUS_CODE
, D.LIMIT_CODE LIMIT_CODE
, D.FIXED_OR_FLOATING_RATE FIXED_FLOAT_BASIS
, D.DEAL_LINKING_CODE CONTRACT_LINK_CODE
, NVL(NVL(D.SYNTHETIC_PRINTED_YN
, D.PRINTED_YN)
, 'N') LETTER_PRINTED_YN
, D.BROKERAGE_CODE BROKER_CODE
, D.TAX_CODE TAX_CODE
, D.SETTLE_ACTION PAY_REC_SETTLEMENT
, D.PREMIUM_ACTION PAY_REC_PREMIUM
, D.FX_RO_DEAL_NO ROLLED_FROM_FX_CONTRACT
, D.FX_PD_DEAL_NO PREDELIVERED_FROM_FX_CONTRACT
, D.COMMENTS COMMENTS
, D.ACTION_STATUS ACTION_STATUS
, DT.NAME CONTRACT_TYPE_NAME
, DECODE(D.DEAL_TYPE
, 'STOCK'
, BOND_ISSUE
, 'BOND'
, BOND_ISSUE
, NULL) ISSUE_CODE
, DECODE(D.DEAL_TYPE
, 'STOCK'
, CAPITAL_PRICE
, 'BOND'
, CAPITAL_PRICE
, NULL) PRICE
, QUANTITY
, D.INCOME_TAX_CODE INCOME_TAX_CODE
, D.INCOME_TAX_RATE INCOME_TAX_RATE
, TO_NUMBER(NULL) INCOME_TAX_AMOUNT
, TO_DATE(NULL) INTEREST_DUE_ON
, MATURE_SETTLEMENT_BASIS
, PAYMENT_SETTLEMENT_BASIS
, D.CPARTY_ACCOUNT_NO
, NULL ACTION FROM XTR_DEALS_V D
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_DEAL_STATUSES DS
, XTR_BANK_ACCOUNTS BA
WHERE D.DEAL_TYPE NOT IN('ONC'
, 'CMF'
, 'TMM'
, 'IRS'
, 'RTMM') AND D.DEAL_TYPE = DT.DEAL_TYPE AND (D.DEAL_TYPE = ST.DEAL_TYPE
AND D.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) AND (D.DEAL_TYPE = DS.DEAL_TYPE
AND D.STATUS_CODE = DS.STATUS_CODE) AND D.CPARTY_CODE = BA.PARTY_CODE (+) AND D.CPARTY_ACCOUNT_NO = BA.ACCOUNT_NUMBER (+) AND NVL(D.CURRENCY
, D.CURRENCY_SELL) = BA.CURRENCY (+) UNION ALL SELECT R.DEAL_NUMBER CONTRACT_NO
, R.TRANSACTION_NUMBER ROLLOVER_TRANSACTION_NO
, R.COMPANY_CODE COMPANY
, R.CPARTY_CODE COUNTERPARTY
, R.CLIENT_CODE CLIENT
, D.ACCEPTOR_NAME ACCEPTOR
, D.ENDORSER_NAME ENDORSER
, D.DRAWER_NAME DRAWER
, R.DEAL_TYPE CONTRACT_TYPE
, DT.USER_DEAL_TYPE
, R.DEAL_SUBTYPE CONTRACT_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, NVL(R.PRODUCT_TYPE
, 'NOT APPLIC') PRODUCT
, NVL(R.PORTFOLIO_CODE
, 'NOT APPLIC') PORTFOLIO
, R.CURRENCY CURRENCY
, D.CURRENCY_SELL CURRENCY_SELL
, R.DEAL_DATE CONTRACT_DEALT_ON
, R.DUAL_AUTHORISATION_ON AUTHORISED_ON
, R.START_DATE COMMENCE_DATE
, R.MATURITY_DATE MATURITY_DATE
, DECODE(R.DEAL_TYPE
, 'TMM'
, R.SETTLE_DATE
, D.SETTLE_DATE) SETTLEMENT_DATE
, D.PREMIUM_DATE PREMIUM_DATE
, D.EXPIRY_DATE OPTION_EXPIRY
, R.PRINCIPAL_ADJUST AMOUNT
, D.SELL_AMOUNT SELL_AMOUNT
, D.START_AMOUNT CONSIDERATION_AMOUNT
, DECODE(R.DEAL_TYPE
, 'TMM'
, R.PI_AMOUNT_RECEIVED
, D.SETTLE_AMOUNT) SETTLEMENT_AMOUNT
, D.PREMIUM_AMOUNT PREMIUM_AMOUNT
, R.BROKERAGE_AMOUNT BROKER_AMOUNT
, R.PRINCIPAL_TAX_AMOUNT TAX_AMOUNT
, R.INTEREST_RATE CONTRACT_RATE
, D.COUPON_RATE COUPON_RATE
, R.BROKERAGE_RATE BROKER_RATE
, R.PRINCIPAL_TAX_RATE TAX_RATE
, D.SETTLE_RATE SETTLEMENT_RATE
, R.CREATED_ON ENTERED_INTO_SYSTEM_ON
, R.CREATED_BY ENTERED_INTO_SYSTEM_BY
, R.DEALER_CODE COMPANY_DEALER
, R.DUAL_AUTHORISATION_BY AUTHORISED_BY
, DECODE(D.DEAL_TYPE
, 'ONC'
, D.START_ACCOUNT_NO
, D.SETTLE_ACCOUNT_NO) COMPANY_SETTLEMENT_ACCT
, BA.BANK_SHORT_CODE COUNTERPARTY_ACCT_REF
, D.PREMIUM_ACCOUNT_NO PREMIUM_ACCT
, D.BUY_ACCOUNT_NO FX_BUY_SETTLEMENT_ACCT
, D.SELL_ACCOUNT_NO FX_SELL_SETTLEMENT_ACCT
, D.SETTLE_ACCOUNT_NO SETTLEMENT_ACCT
, R.STATUS_CODE CONTRACT_STATUS
, DS.USER_STATUS_CODE
, R.LIMIT_CODE LIMIT_CODE
, D.FIXED_OR_FLOATING_RATE FIXED_FLOAT_BASIS
, R.DEAL_LINKING_CODE CONTRACT_LINK_CODE
, R.PRINTED_YN LETTER_PRINTED_YN
, R.BROKERAGE_CODE BROKER_CODE
, R.PRINCIPAL_TAX_CODE TAX_CODE
, D.SETTLE_ACTION PAY_REC_SETTLEMENT
, D.PREMIUM_ACTION PAY_REC_PREMIUM
, D.FX_RO_DEAL_NO ROLLED_FROM_FX_CONTRACT
, D.FX_PD_DEAL_NO PREDELIVERED_FROM_FX_CONTRACT
, NVL(R.COMMENTS
, D.COMMENTS) COMMENTS
, R.ACTION_STATUS ACTION_STATUS
, DT.NAME CONTRACT_TYPE_NAME
, DECODE(R.DEAL_TYPE
, 'STOCK'
, D.BOND_ISSUE
, NULL) ISSUE_CODE
, DECODE(R.DEAL_TYPE
, 'STOCK'
, D.CAPITAL_PRICE
, NULL) PRICE
, DECODE(R.DEAL_TYPE
, 'STOCK'
, R.QUANTITY
, NULL) QUANTITY
, R.TAX_CODE INCOME_TAX_CODE
, R.TAX_RATE INCOME_TAX_RATE
, R.TAX_AMOUNT INCOME_TAX_AMOUNT
, DECODE(R.DEAL_TYPE
, 'TMM'
, R.SETTLE_DATE
, 'ONC'
, DECODE(R.PREPAID_INTEREST
, 'Y'
, R.START_DATE
, R.MATURITY_DATE)
, 'IRS'
, R.SETTLE_DATE
, R.MATURITY_DATE) INTEREST_DUE_ON
, D.MATURE_SETTLEMENT_BASIS
, D.PAYMENT_SETTLEMENT_BASIS
, D.CPARTY_ACCOUNT_NO
, NULL ACTION FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_DEALS_V D
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_DEAL_STATUSES DS
, XTR_BANK_ACCOUNTS BA WHERE R.DEAL_TYPE IN ('ONC'
, 'CMF'
, 'TMM'
, 'IRS'
, 'RTMM'
, 'STOCK') AND D.DEAL_NO = R.DEAL_NUMBER AND R.DEAL_TYPE = DT.DEAL_TYPE AND (R.DEAL_TYPE = ST.DEAL_TYPE
AND R.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) AND (R.DEAL_TYPE = DS.DEAL_TYPE
AND R.STATUS_CODE = DS.STATUS_CODE) AND D.CPARTY_CODE = BA.PARTY_CODE (+) AND D.CPARTY_ACCOUNT_NO = BA.ACCOUNT_NUMBER (+) AND NVL(D.CURRENCY
, D.CURRENCY_SELL) = BA.CURRENCY (+) UNION ALL SELECT 0 CONTRACT_NO
, EX.TRANSACTION_NUMBER ROLLOVER_TRANSACTION_NO
, EX.COMPANY_CODE COMPANY
, EX.THIRDPARTY_CODE COUNTERPARTY
, NULL CLIENT_CODE
, NULL ACCEPTOR
, NULL ENDORSER
, NULL DRAWER
, EX.DEAL_TYPE CONTRACT_TYPE
, DT.USER_DEAL_TYPE
, EX.DEAL_SUBTYPE CONTRACT_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, EX.EXPOSURE_TYPE PRODUCT
, NVL(EX.PORTFOLIO_CODE
, 'NOT APPLIC') PORTFOLIO
, EX.CURRENCY CURRENCY
, NULL CURRENCY_SELL
, EX.CREATED_ON CONTRACT_DEALT_ON
, EX.DUAL_AUTHORISATION_ON AUTHORISED_ON
, EX.VALUE_DATE COMMENCE_DATE
, TO_DATE(NULL) MATURITY_DATE
, TO_DATE(NULL) SETTLEMENT_DATE
, TO_DATE(NULL) PREMIUM_DATE
, TO_DATE(NULL) OPTION_EXPIRY
, EX.AMOUNT AMOUNT
, TO_NUMBER(NULL) SELL_AMOUNT
, TO_NUMBER(NULL) CONSIDERATION_AMOUNT
, TO_NUMBER(NULL) SETTLEMENT_AMOUNT
, TO_NUMBER(NULL) PREMIUM_AMOUNT
, TO_NUMBER(NULL) BROKER_AMOUNT
, TO_NUMBER(NULL) TAX_AMOUNT
, TO_NUMBER(NULL) CONTRACT_RATE
, TO_NUMBER(NULL) COUPON_RATE
, TO_NUMBER(NULL) BROKER_RATE
, TO_NUMBER(NULL) TAX_RATE
, TO_NUMBER(NULL) SETTLEMENT_RATE
, EX.CREATED_ON ENTERED_INTO_SYSTEM_ON
, EX.CREATED_BY ENTERED_INTO_SYSTEM_BY
, NULL COMPANY_DEALER
, EX.DUAL_AUTHORISATION_BY AUTHORISED_BY
, EX.ACCOUNT_NO COMPANY_SETTLEMENT_ACCT
, BA.BANK_SHORT_CODE COUNTERPARTY_ACCT_REF
, NULL PREMIUM_ACCT
, NULL FX_BUY_SETTLEMENT_ACCT
, NULL FX_SELL_SETTLEMENT_ACCT
, NULL SETTLEMENT_ACCT
, EX.STATUS_CODE CONTRACT_STATUS
, DS.USER_STATUS_CODE
, NULL LIMIT_CODE
, NULL FIXED_FLOAT_BASIS
, EX.DEAL_LINK_CODE CONTRACT_LINK_CODE
, NULL LETTER_PRINTED_YN
, NULL BROKER_CODE
, NULL TAX_CODE
, EX.ACTION_CODE PAY_REC_SETTLEMENT
, NULL PAY_REC_PREMIUM
, TO_NUMBER(NULL) ROLLED_FROM_FX_CONTRACT
, TO_NUMBER(NULL) PREDELIVERED_FROM_FX_CONTRACT
, EX.COMMENTS COMMENTS
, NULL ACTION_STATUS
, DT.NAME CONTRACT_TYPE_NAME
, NULL ISSUE_CODE
, TO_NUMBER(NULL) PRICE
, TO_NUMBER(NULL) QUANTITY
, NULL INCOME_TAX_CODE
, TO_NUMBER(NULL) INCOME_TAX_RATE
, TO_NUMBER(NULL) INCOME_TAX_AMOUNT
, TO_DATE(NULL) INTEREST_DUE_ON
, NULL MATURE_SETTLEMENT_BASIS
, NULL PAYMENT_SETTLEMENT_BASIS
, EX.CPARTY_ACCOUNT_NO
, EX.ACTION_CODE ACTION FROM XTR_EXPOSURE_TRANSACTIONS_V EX
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_DEAL_STATUSES DS
, XTR_BANK_ACCOUNTS BA WHERE EX.DEAL_TYPE = DT.DEAL_TYPE AND (EX.DEAL_TYPE = ST.DEAL_TYPE
AND EX.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) AND (EX.DEAL_TYPE = DS.DEAL_TYPE
AND EX.STATUS_CODE = DS.STATUS_CODE) AND EX.THIRDPARTY_CODE = BA.PARTY_CODE (+) AND EX.CPARTY_ACCOUNT_NO = BA.ACCOUNT_NUMBER (+) AND EX.CURRENCY = BA.CURRENCY (+) UNION ALL SELECT 0 CONTRACT_NO
, IA.TRANSACTION_NUMBER ROLLOVER_TRANSACTION_NO
, IA.COMPANY_CODE COMPANY
, IA.COMPANY_CODE COUNTERPARTY
, NULL CLIENT_CODE
, NULL ACCEPTOR
, NULL ENDORSER
, NULL DRAWER
, IA.DEAL_TYPE CONTRACT_TYPE
, DT.USER_DEAL_TYPE
, IA.DEAL_SUBTYPE CONTRACT_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, IA.PRODUCT_TYPE PRODUCT
, IA.PORTFOLIO_CODE PORTFOLIO
, IA.CURRENCY CURRENCY
, NULL CURRENCY_SELL
, IA.DEAL_DATE CONTRACT_DEALT_ON
, IA.DUAL_AUTHORISATION_ON AUTHORISED_ON
, NULL COMMENCE_DATE
, TO_DATE(NULL) MATURITY_DATE
, IA.TRANSFER_DATE SETTLEMENT_DATE
, TO_DATE(NULL) PREMIUM_DATE
, TO_DATE(NULL) OPTION_EXPIRY
, IA.TRANSFER_AMOUNT AMOUNT
, TO_NUMBER(NULL) SELL_AMOUNT
, TO_NUMBER(NULL) CONSIDERATION_AMOUNT
, TO_NUMBER(NULL) SETTLEMENT_AMOUNT
, TO_NUMBER(NULL) PREMIUM_AMOUNT
, TO_NUMBER(NULL) BROKER_AMOUNT
, TO_NUMBER(NULL) TAX_AMOUNT
, TO_NUMBER(NULL) CONTRACT_RATE
, TO_NUMBER(NULL) COUPON_RATE
, TO_NUMBER(NULL) BROKER_RATE
, TO_NUMBER(NULL) TAX_RATE
, TO_NUMBER(NULL) SETTLEMENT_RATE
, IA.CREATION_DATE ENTERED_INTO_SYSTEM_ON
, IA.DEALER_CODE ENTERED_INTO_SYSTEM_BY
, IA.DEALER_CODE COMPANY_DEALER
, IA.DUAL_AUTHORISATION_BY AUTHORISED_BY
, IA.ACCOUNT_NO_FROM COMPANY_SETTLEMENT_ACCT
, IA.ACCOUNT_NO_TO COUNTERPARTY_ACCT_REF
, NULL PREMIUM_ACCT
, NULL FX_BUY_SETTLEMENT_ACCT
, NULL FX_SELL_SETTLEMENT_ACCT
, NULL SETTLEMENT_ACCT
, NULL CONTRACT_STATUS
, NULL
, NULL LIMIT_CODE
, NULL FIXED_FLOAT_BASIS
, NULL CONTRACT_LINK_CODE
, NULL LETTER_PRINTED_YN
, NULL BROKER_CODE
, NULL TAX_CODE
, NULL PAY_REC_SETTLEMENT
, NULL PAY_REC_PREMIUM
, TO_NUMBER(NULL) ROLLED_FROM_FX_CONTRACT
, TO_NUMBER(NULL) PREDELIVERED_FROM_FX_CONTRACT
, IA.COMMENTS COMMENTS
, NULL ACTION_STATUS
, DT.NAME CONTRACT_TYPE_NAME
, NULL ISSUE_CODE
, TO_NUMBER(NULL) PRICE
, TO_NUMBER(NULL) QUANTITY
, NULL INCOME_TAX_CODE
, TO_NUMBER(NULL) INCOME_TAX_RATE
, TO_NUMBER(NULL) INCOME_TAX_AMOUNT
, TO_DATE(NULL) INTEREST_DUE_ON
, NULL MATURE_SETTLEMENT_BASIS
, NULL PAYMENT_SETTLEMENT_BASIS
, IA.ACCOUNT_NO_TO
, NULL ACTION FROM XTR_INTERACCT_TRANSFERS IA
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_BANK_ACCOUNTS BA WHERE IA.DEAL_TYPE = DT.DEAL_TYPE AND (IA.DEAL_TYPE = ST.DEAL_TYPE
AND IA.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) AND IA.COMPANY_CODE = BA.PARTY_CODE (+) AND IA.ACCOUNT_NO_TO = BA.ACCOUNT_NUMBER (+) AND IA.CURRENCY = BA.CURRENCY (+)

Columns