View: XTR_A_ALL_CONTRACTS_V

Product:	XTR - Treasury
Description:
Implementation/DBA Data:	APPS.XTR_A_ALL_CONTRACTS_V

View Text

SELECT D.DEAL_NO CONTRACT_NO
, DECODE(D.DEAL_TYPE
, 'ONC'
, D.TRANSACTION_NO) 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_SUBTYPE CONTRACT_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, NVL(D.PRODUCT_TYPE
, 'NOT APPLIC') PRODUCT
, NVL(D.PORTFOLIO_CODE
, 'NOT APPLIC') 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
, 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'
, D.FACE_VALUE_AMOUNT
, 'IRS'
, 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
, NVL(D.UPDATED_ON
, TO_DATE('01/01/1900'
, 'DD/MM/YYYY' )) UPDATED_ON_DATE
, D.UPDATED_BY UPDATED_BY_USER
, D.DEALER_CODE COMPANY_DEALER
, D.START_ACCOUNT_NO COMPANY_SETTLEMENT_ACCT
, D.CPARTY_REF 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
, D.BROKERAGE_CODE BROKER_CODE
, D.TAX_CODE TAX_CODE
, D.SETTLE_ACTION PAY_REC_SETTLEMENT
, D.PREMIUM_ACTION PAY_REC_PREMIUM
, D.COMMENTS COMMENTS
, D.AUDIT_DATE_STORED AUDIT_DATE_STORED
, 'A' ORDERING_COLUMN
, D.CREATED_BY CREATED_BY_USER
, D.CREATED_ON CREATED_ON_DATE
, D.PRICING_MODEL
, D.MARKET_DATA_SET
, D.DAY_COUNT_TYPE
, D.ROUNDING_TYPE
, D.SETTLE_DAY_COUNT_TYPE
, D.SETTLE_ROUNDING_TYPE
FROM XTR_A_DEALS D
, XTR_DEAL_SUBTYPES ST
, XTR_DEAL_STATUSES DS
WHERE D.DEAL_TYPE NOT IN('ONC'
, 'CMF')
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) 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_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.START_DATE COMMENCE_DATE
, R.MATURITY_DATE MATURITY_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
, D.SETTLE_AMOUNT SETTLEMENT_AMOUNT
, D.PREMIUM_AMOUNT PREMIUM_AMOUNT
, R.BROKERAGE_AMOUNT BROKER_AMOUNT
, R.TAX_AMOUNT TAX_AMOUNT
, R.INTEREST_RATE CONTRACT_RATE
, D.COUPON_RATE COUPON_RATE
, R.BROKERAGE_RATE BROKER_RATE
, R.TAX_RATE TAX_RATE
, D.SETTLE_RATE SETTLEMENT_RATE
, NVL(R.UPDATED_ON
, TO_DATE('01/01/1900'
, 'DD/MM/YYYY')) UPDATED_ON_DATE
, R.UPDATED_BY UPDATED_BY_USER
, R.DEALER_CODE COMPANY_DEALER
, R.PRINCIPAL_ACCOUNT_NO COMPANY_SETTLEMENT_ACCT
, D.CPARTY_REF 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.BROKERAGE_CODE BROKER_CODE
, R.TAX_CODE TAX_CODE
, D.SETTLE_ACTION PAY_REC_SETTLEMENT
, D.PREMIUM_ACTION PAY_REC_PREMIUM
, R.COMMENTS COMMENTS
, TO_DATE(NULL) AUDIT_DATE_STORED
, 'A' ORDERING_COLUMN
, R.CREATED_BY CREATED_BY_USER
, R.CREATED_ON CREATED_ON_DATE
, D.PRICING_MODEL
, D.MARKET_DATA_SET
, D.DAY_COUNT_TYPE
, D.ROUNDING_TYPE
, D.SETTLE_DAY_COUNT_TYPE
, D.SETTLE_ROUNDING_TYPE
FROM XTR_A_ROLLOVER_TRANSACTIONS R
, XTR_DEALS_V D
, XTR_DEAL_SUBTYPES ST
, XTR_DEAL_STATUSES DS
WHERE R.DEAL_TYPE IN ('ONC'
, 'CMF')
AND D.DEAL_NO = R.DEAL_NUMBER
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) UNION ALL SELECT D.DEAL_NO CONTRACT_NO
, DECODE(D.DEAL_TYPE
, 'ONC'
, D.TRANSACTION_NO) 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_SUBTYPE CONTRACT_SUBTYPE
, ST.USER_DEAL_SUBTYPE
, NVL(D.PRODUCT_TYPE
, 'NOT APPLIC') PRODUCT
, NVL(D.PORTFOLIO_CODE
, 'NOT APPLIC') 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
, 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'
, D.FACE_VALUE_AMOUNT
, 'IRS'
, 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
, NVL(D.UPDATED_ON
, TO_DATE('01/01/1900'
, 'DD/MM/YYYY')) UPDATED_ON_DATE
, D.UPDATED_BY UPDATED_BY_USER
, D.DEALER_CODE COMPANY_DEALER
, D.START_ACCOUNT_NO COMPANY_SETTLEMENT_ACCT
, D.CPARTY_REF 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
, D.BROKERAGE_CODE BROKER_CODE
, D.TAX_CODE TAX_CODE
, D.SETTLE_ACTION PAY_REC_SETTLEMENT
, D.PREMIUM_ACTION PAY_REC_PREMIUM
, D.COMMENTS COMMENTS
, TO_DATE(NULL) AUDIT_DATE_STORED
, 'B' ORDERING_COLUMN
, D.CREATED_BY CREATED_BY_USER
, D.CREATED_ON CREATED_ON_DATE
, D.PRICING_MODEL
, D.MARKET_DATA_SET
, D.DAY_COUNT_TYPE
, D.ROUNDING_TYPE
, D.SETTLE_DAY_COUNT_TYPE
, D.SETTLE_ROUNDING_TYPE
FROM XTR_DEALS_V D
, XTR_DEAL_SUBTYPES ST
, XTR_DEAL_STATUSES DS
WHERE D.DEAL_TYPE NOT IN('ONC'
, 'CMF')
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) 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_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.START_DATE COMMENCE_DATE
, R.MATURITY_DATE MATURITY_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
, D.SETTLE_AMOUNT SETTLEMENT_AMOUNT
, D.PREMIUM_AMOUNT PREMIUM_AMOUNT
, R.BROKERAGE_AMOUNT BROKER_AMOUNT
, R.TAX_AMOUNT TAX_AMOUNT
, R.INTEREST_RATE CONTRACT_RATE
, D.COUPON_RATE COUPON_RATE
, R.BROKERAGE_RATE BROKER_RATE
, R.TAX_RATE TAX_RATE
, D.SETTLE_RATE SETTLEMENT_RATE
, NVL(R.UPDATED_ON
, TO_DATE('01/01/1900'
, 'DD/MM/YYYY')) UPDATED_ON_DATE
, R.UPDATED_BY UPDATED_BY_USER
, R.DEALER_CODE COMPANY_DEALER
, R.PRINCIPAL_ACCOUNT_NO COMPANY_SETTLEMENT_ACCT
, D.CPARTY_REF 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.BROKERAGE_CODE BROKER_CODE
, R.TAX_CODE TAX_CODE
, D.SETTLE_ACTION PAY_REC_SETTLEMENT
, D.PREMIUM_ACTION PAY_REC_PREMIUM
, R.COMMENTS COMMENTS
, TO_DATE(NULL) AUDIT_DATE_STORED
, 'B' ORDERING_COLUMN
, R.CREATED_BY CREATED_BY_USER
, R.CREATED_ON CREATED_ON_DATE
, D.PRICING_MODEL
, D.MARKET_DATA_SET
, D.DAY_COUNT_TYPE
, D.ROUNDING_TYPE
, D.SETTLE_DAY_COUNT_TYPE
, D.SETTLE_ROUNDING_TYPE
FROM XTR_ROLLOVER_TRANSACTIONS_V R
, XTR_DEALS_V D
, XTR_DEAL_SUBTYPES ST
, XTR_DEAL_STATUSES DS
WHERE R.DEAL_TYPE IN ('ONC'
, 'CMF')
AND D.DEAL_NO = R.DEAL_NUMBER
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)

Columns