VIEW: APPS.XTR_A_ALL_CONTRACTS_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)