VIEW: APPS.XTR_REVIEW_ALL_CONTRACTS_V

Source

View Text - Preformatted

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 (+)

View Text - HTML Formatted

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 (+)