VIEW: APPS.QRM_CURRENT_DEALS_V

Source

View Text - Preformatted

SELECT D.DEAL_NO DEAL_NO, NVL(RT.TRANSACTION_NUMBER, 1) TRANSACTION_NO, D.MARKET_DATA_SET MARKET_DATA_SET, D.CAP_OR_FLOOR CALL_OR_PUT, NULL OPTION_STYLE, NULL ISSUER_CODE, RT.COMPANY_CODE COMPANY_CODE, RT.DEALER_CODE DEALER_CODE, RT.CLIENT_CODE CLIENT_CODE, RT.CPARTY_CODE CPARTY_CODE, RT.PORTFOLIO_CODE PORTFOLIO_CODE, RT.DEAL_LINKING_CODE LINK_CODE, NULL PRICING_MODEL, D.CURRENCY DEAL_CCY, NULL BUY_CCY, NULL SELL_CCY, NULL FOREIGN_CCY, NULL DOMESTIC_CCY, NULL BASE_CCY, NULL CONTRA_CCY, D.CURRENCY SENSITIVITY_CCY, NULL PREMIUM_CCY, TO_NUMBER(NULL) BUY_AMOUNT, TO_NUMBER(NULL) SELL_AMOUNT, TO_NUMBER(NULL) FOREIGN_AMOUNT, TO_NUMBER(NULL) DOMESTIC_AMOUNT, TO_NUMBER(NULL) BASE_CCY_AMOUNT, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT, RT.PRINCIPAL_ADJUST START_AMOUNT, RT.BALANCE_OUT FACE_VALUE, RT.INTEREST INTEREST, RT.ACCUM_INTEREST_BF ACCUM_INTEREST_BF, RT.ACCUM_INT_ACTION ACCUM_INT_ACTION, TO_NUMBER(NULL) ACCRUED_INTEREST, TO_NUMBER(NULL) INTEREST_SETTLED, TRUNC(RT.DEAL_DATE) DEAL_DATE, TRUNC(RT.MATURITY_DATE) END_DATE, TRUNC(TO_DATE(NULL)) GAP_DATE, RT.DEAL_SUBTYPE DEAL_SUBTYPE, D.DEAL_TYPE DEAL_TYPE, 'N' DISCOUNT_YIELD, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE, TRUNC(RT.MATURITY_DATE) MATURITY_DATE, NVL(RT.NO_OF_DAYS,0) NO_OF_DAYS, 'M' MARKET_TYPE, 'P' INSTR_TYPE, DECODE(D.DEAL_SUBTYPE, 'FUND', 'F', 'INVEST', 'I') MM_FX_INSTR_TYPE, TRUNC(D.VALUE_DATE) VALUE_DATE, TRUNC(RT.SETTLE_DATE) SETTLE_DATE, D.PREMIUM_AMOUNT PREMIUM_AMOUNT, RT.PRODUCT_TYPE PRODUCT_TYPE, TRUNC(RT.START_DATE) START_DATE, TRUNC(RT.START_DATE) PHY_START_DATE, NULL KNOCK_TYPE, TO_NUMBER(NULL) KNOCK_LEVEL, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS, D.INT_SWAP_REF INT_SWAP_REF, NULL INT_REF, D.BOND_ISSUE BOND_ISSUE_CODE, D.SECURITY_ID SECURITY_ID, TO_NUMBER(NULL) REF_SPOT_RATE, D.COUPON_ACTION COUPON_ACTION, RT.INTEREST_RATE COUPON_RATE, D.MARGIN MARGIN, RT.INTEREST_RATE TRANSACTION_RATE, RT.INTEREST_FREQ INTEREST_FREQUENCY, DECODE(RT.INTEREST_FREQ, 'M', 12, 'Q', 4, 'S', 2, 'W', 52, 'Y', 1, NULL) COUPON_FREQUENCY, TRUNC(D.NEXT_COUPON_DATE) NEXT_COUPON_DATE, RT.YEAR_CALC_TYPE DAY_COUNT_BASIS, TO_NUMBER(NULL) QUANTITY_REMAINING, TO_NUMBER(NULL) QUANTITY_REMAINING_USD, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB, D.ROUNDING_TYPE ROUNDING_TYPE, D.DAY_COUNT_TYPE DAY_COUNT_TYPE, RT.PREPAID_INTEREST PREPAID_INTEREST FROM XTR_DEALS D, XTR_ROLLOVER_TRANSACTIONS RT WHERE D.DEAL_NO = RT.DEAL_NUMBER AND RT.DEAL_TYPE = 'ONC' AND RT.STATUS_CODE = 'CURRENT' AND RT.PRINCIPAL_ADJUST<>0 AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE FROM XTR_COMPANY_AUTHORITIES XCA, XTR_DEALER_CODES XDC WHERE D.COMPANY_CODE = XCA.PARTY_CODE AND XCA.DEALER_CODE = XDC.DEALER_CODE AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y' AND XDC.USER_ID = FND_GLOBAL.USER_ID) AND RT.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE FROM XTR_TY_SUB_PRD_COMBIN_V) union all SELECT D.DEAL_NO DEAL_NO, NVL(D.TRANSACTION_NO, -4) TRANSACTION_NO, D.MARKET_DATA_SET MARKET_DATA_SET, D.CAP_OR_FLOOR CALL_OR_PUT, DECODE(D.DEAL_TYPE, 'IRO', DECODE(D.OPTION_COMMENCEMENT, NULL, 'EUROPEAN', 'AMERICAN'), 'SWPTN', DECODE(D.OPTION_COMMENCEMENT, NULL, 'EUROPEAN', 'AMERICAN'), NULL) OPTION_STYLE, DECODE(D.DEAL_TYPE, 'STOCK', D.ACCEPTOR_CODE, 'NI', D.ACCEPTOR_CODE, NULL) ISSUER_CODE, D.COMPANY_CODE COMPANY_CODE, D.DEALER_CODE DEALER_CODE, D.CLIENT_CODE CLIENT_CODE, D.CPARTY_CODE CPARTY_CODE, D.PORTFOLIO_CODE PORTFOLIO_CODE, D.DEAL_LINKING_CODE LINK_CODE, DECODE(D.DEAL_TYPE,'STOCK', 'MARKET', 'NI', 'DISC_METHOD', 'TMM', 'DISC_CASHFLOW', 'IRS', 'DISC_CASHFLOW', 'RTMM', 'DISC_CASHFLOW', 'FRA',DECODE(D.PRICING_MODEL,'FRA_YIELD','FRA_YIELD', 'FRA_DISC','FRA_DISC', 'FRA_YIELD'), 'IRO', 'BLACK', NULL) PRICING_MODEL, D.CURRENCY DEAL_CCY, NULL BUY_CCY, NULL SELL_CCY, NULL FOREIGN_CCY, NULL DOMESTIC_CCY, NULL BASE_CCY, NULL CONTRA_CCY, D.CURRENCY SENSITIVITY_CCY, DECODE(D.DEAL_TYPE, 'IRO', D.CURRENCY, 'SWPTN', D.CURRENCY, NULL) PREMIUM_CCY, TO_NUMBER(NULL) BUY_AMOUNT, TO_NUMBER(NULL) SELL_AMOUNT, TO_NUMBER(NULL) FOREIGN_AMOUNT, TO_NUMBER(NULL) DOMESTIC_AMOUNT, TO_NUMBER(NULL) BASE_CCY_AMOUNT, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT, D.START_AMOUNT START_AMOUNT, DECODE(D.DEAL_TYPE, 'FRA', FACE_VALUE_AMOUNT, 'IRO', FACE_VALUE_AMOUNT, 'IRS', D.FACE_VALUE_AMOUNT, 'NI', D.MATURITY_BALANCE_AMOUNT, 'RTMM', D.FACE_VALUE_AMOUNT, 'SWPTN', D.FACE_VALUE_AMOUNT, 'TMM', D.FACE_VALUE_AMOUNT, 0) FACE_VALUE, D.INTEREST_AMOUNT INTEREST, TO_NUMBER(NULL) ACCUM_INTEREST_BF, NULL ACCUM_INT_ACTION, 0 ACCRUED_INTEREST, TO_NUMBER(NULL) INTEREST_SETTLED, TRUNC(D.DEAL_DATE) DEAL_DATE, TRUNC(DECODE(D.DEAL_TYPE, 'FRA', D.START_DATE, 'IRO', D.EXPIRY_DATE, 'IRS', D.MATURITY_DATE, 'NI', D.MATURITY_DATE, 'RTMM', D.MATURITY_DATE, 'TMM', D.MATURITY_DATE, 'SWPTN', D.EXPIRY_DATE, TO_DATE(NULL))) END_DATE, TRUNC(DECODE(D.DEAL_TYPE, 'FRA', D.START_DATE, 'IRO', D.EXPIRY_DATE, 'NI', D.MATURITY_DATE, 'SWPTN', D.EXPIRY_DATE, TO_DATE(NULL))) GAP_DATE, D.DEAL_SUBTYPE DEAL_SUBTYPE, D.DEAL_TYPE DEAL_TYPE, DECODE(D.DEAL_TYPE, 'NI', DECODE(D.CALC_BASIS, 'DISCOUNT','Y','N'), NVL(D.DISCOUNT, 'N')) DISCOUNT_YIELD, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE, TRUNC(D.MATURITY_DATE) MATURITY_DATE, NVL(D.NO_OF_DAYS,0) NO_OF_DAYS, DECODE (D.DEAL_TYPE,'STOCK', 'E', 'M') MARKET_TYPE, DECODE(D.DEAL_TYPE, 'IRO', 'D', 'FRA', 'D', 'IRS', 'D', 'SWPTN', 'D', 'P') INSTR_TYPE, DECODE(D.DEAL_TYPE,'FRA', 'D', 'IRO', 'D', 'IRS', 'D', 'NI', DECODE(D.DEAL_SUBTYPE, 'ISSUE', 'F', 'SHORT', 'F', 'I'), 'RTMM', DECODE(D.DEAL_SUBTYPE,'FUND','F', 'INVEST', 'I'), 'SWPTN', 'D', 'TMM', DECODE(D.DEAL_SUBTYPE,'FUND','F', 'INVEST','I'), 'STOCK', 'I', NULL) MM_FX_INSTR_TYPE, TRUNC(D.VALUE_DATE) VALUE_DATE, TRUNC(D.SETTLE_DATE) SETTLE_DATE, DECODE(D.PREMIUM_ACTION, 'PAY', (-1)*D.PREMIUM_AMOUNT, 'REC', D.PREMIUM_AMOUNT) PREMIUM_AMOUNT, D.PRODUCT_TYPE PRODUCT_TYPE, TRUNC(DECODE(D.DEAL_TYPE, 'IRO', D.OPTION_COMMENCEMENT, 'SWPTN', D.OPTION_COMMENCEMENT, D.START_DATE)) START_DATE, TRUNC(D.START_DATE) PHY_START_DATE, NULL KNOCK_TYPE, TO_NUMBER(NULL) KNOCK_LEVEL, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS, D.INT_SWAP_REF INT_SWAP_REF, DECODE(D.DEAL_TYPE, 'IRS', DECODE(D.FIXED_OR_FLOATING_RATE, 'FLOAT', D.RATE_BASIS, NULL), NULL) INT_REF, D.BOND_ISSUE BOND_ISSUE_CODE, D.SECURITY_ID SECURITY_ID, TO_NUMBER(NULL) REF_SPOT_RATE, D.COUPON_ACTION COUPON_ACTION, DECODE(D.DEAL_TYPE, 'SWPTN', D.INTEREST_RATE, 'IRO', D.INTEREST_RATE, 'NI', D.INTEREST_RATE, D.COUPON_RATE) COUPON_RATE, D.MARGIN MARGIN, DECODE(D.DEAL_TYPE, 'STOCK', D.CAPITAL_PRICE, 'FRA', D.INTEREST_RATE, 'IRO', D.INTEREST_RATE, 'IRS', D.INTEREST_RATE, 'NI', D.INTEREST_RATE, 'RTMM', D.INTEREST_RATE, 'SWPTN',D.INTEREST_RATE, 'TMM',D.INTEREST_RATE) TRANSACTION_RATE, NULL INTEREST_FREQUENCY, DECODE(D.DEAL_TYPE, 'IRS', 12/D.FREQUENCY, 'SWPTN',12/D.FREQUENCY, 'TMM', DECODE(D.PAYMENT_FREQ, 'ANNUAL', 1, 'SEMI ANNUAL', 2, 'QUARTERLY', 4, 'BI MONTHLY', 6, 'MONTHLY', 12, 'WEEKLY', 52, NULL), 'RTMM', DECODE(D.PAYMENT_FREQ, 'ANNUAL', 1, 'BI MONTHLY', 6, 'FORTNIGHTLY', 24, 'FOUR WEEKLY', 12, 'MONTHLY', 12, 'QUARTERLY', 4, 'SEMI ANNUAL', 2, 'WEEKLY', 52, NULL), NULL) COUPON_FREQUENCY, TRUNC(DECODE(D.DEAL_TYPE, 'SWPTN', ADD_MONTHS(D.START_DATE,D.FREQUENCY), D.NEXT_COUPON_DATE)) NEXT_COUPON_DATE, D.YEAR_CALC_TYPE DAY_COUNT_BASIS, TO_NUMBER(NULL) QUANTITY_REMAINING, TO_NUMBER(NULL) QUANTITY_REMAINING_USD, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB, D.ROUNDING_TYPE ROUNDING_TYPE, D.DAY_COUNT_TYPE DAY_COUNT_TYPE, D.PREPAID_INTEREST PREPAID_INTEREST FROM XTR_DEALS D WHERE (D.DEAL_TYPE <> 'ONC') AND D.DEAL_TYPE <> 'FX' AND D.DEAL_TYPE <> 'FXO' AND D.DEAL_TYPE <> 'BOND' AND D.DEAL_TYPE <> 'BDO' AND D.DEAL_TYPE <> 'STOCK' AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE, 'NI', 'SELL', 'QRM') AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE, 'NI', 'COVER', 'QRM') AND D.STATUS_CODE = 'CURRENT' AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE FROM XTR_COMPANY_AUTHORITIES XCA, XTR_DEALER_CODES XDC WHERE D.COMPANY_CODE = XCA.PARTY_CODE AND XCA.DEALER_CODE = XDC.DEALER_CODE AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y' AND XDC.USER_ID = FND_GLOBAL.USER_ID) AND D.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE FROM XTR_TY_SUB_PRD_COMBIN_V) union all SELECT D.DEAL_NO DEAL_NO, NVL(D.TRANSACTION_NO, -4) TRANSACTION_NO, D.MARKET_DATA_SET MARKET_DATA_SET, D.CAP_OR_FLOOR CALL_OR_PUT, NULL OPTION_STYLE, D.ACCEPTOR_CODE ISSUER_CODE, D.COMPANY_CODE COMPANY_CODE, D.DEALER_CODE DEALER_CODE, D.CLIENT_CODE CLIENT_CODE, D.CPARTY_CODE CPARTY_CODE, D.PORTFOLIO_CODE PORTFOLIO_CODE, D.DEAL_LINKING_CODE LINK_CODE, 'MARKET' PRICING_MODEL, D.CURRENCY DEAL_CCY, NULL BUY_CCY, NULL SELL_CCY, NULL FOREIGN_CCY, NULL DOMESTIC_CCY, NULL BASE_CCY, NULL CONTRA_CCY, D.CURRENCY SENSITIVITY_CCY, NULL PREMIUM_CCY, TO_NUMBER(NULL) BUY_AMOUNT, TO_NUMBER(NULL) SELL_AMOUNT, TO_NUMBER(NULL) FOREIGN_AMOUNT, TO_NUMBER(NULL) DOMESTIC_AMOUNT, TO_NUMBER(NULL) BASE_CCY_AMOUNT, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT, D.START_AMOUNT START_AMOUNT, 0 FACE_VALUE, D.INTEREST_AMOUNT INTEREST, TO_NUMBER(NULL) ACCUM_INTEREST_BF, NULL ACCUM_INT_ACTION, 0 ACCRUED_INTEREST, TO_NUMBER(NULL) INTEREST_SETTLED, TRUNC(D.DEAL_DATE) DEAL_DATE, TRUNC(TO_DATE(NULL)) END_DATE, TRUNC(TO_DATE(NULL)) GAP_DATE, D.DEAL_SUBTYPE DEAL_SUBTYPE, D.DEAL_TYPE DEAL_TYPE, NVL(D.DISCOUNT, 'N') DISCOUNT_YIELD, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE, TRUNC(D.MATURITY_DATE) MATURITY_DATE, NVL(D.NO_OF_DAYS,0) NO_OF_DAYS, 'E' MARKET_TYPE, 'P' INSTR_TYPE, 'I' MM_FX_INSTR_TYPE, TRUNC(D.VALUE_DATE) VALUE_DATE, TRUNC(D.SETTLE_DATE) SETTLE_DATE, DECODE(D.PREMIUM_ACTION, 'PAY', (-1)*D.PREMIUM_AMOUNT, 'REC', D.PREMIUM_AMOUNT) PREMIUM_AMOUNT, D.PRODUCT_TYPE PRODUCT_TYPE, TRUNC(D.START_DATE) START_DATE, TRUNC(D.START_DATE) PHY_START_DATE, NULL KNOCK_TYPE, TO_NUMBER(NULL) KNOCK_LEVEL, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS, D.INT_SWAP_REF INT_SWAP_REF, NULL INT_REF, D.BOND_ISSUE BOND_ISSUE_CODE, D.SECURITY_ID SECURITY_ID, TO_NUMBER(NULL) REF_SPOT_RATE, D.COUPON_ACTION COUPON_ACTION, D.COUPON_RATE COUPON_RATE, D.MARGIN MARGIN, D.CAPITAL_PRICE TRANSACTION_RATE, NULL INTEREST_FREQUENCY, TO_NUMBER(NULL) COUPON_FREQUENCY, TRUNC(D.NEXT_COUPON_DATE) NEXT_COUPON_DATE, D.YEAR_CALC_TYPE DAY_COUNT_BASIS, D.REMAINING_QUANTITY QUANTITY_REMAINING, D.REMAINING_QUANTITY QUANTITY_REMAINING_USD, D.REMAINING_QUANTITY QUANTITY_REMAINING_SOB, D.ROUNDING_TYPE ROUNDING_TYPE, D.DAY_COUNT_TYPE DAY_COUNT_TYPE, D.PREPAID_INTEREST PREPAID_INTEREST FROM XTR_DEALS D WHERE D.DEAL_TYPE = 'STOCK' AND D.DEAL_SUBTYPE = 'BUY' AND D.STATUS_CODE = 'CURRENT' AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE FROM XTR_COMPANY_AUTHORITIES XCA, XTR_DEALER_CODES XDC WHERE D.COMPANY_CODE = XCA.PARTY_CODE AND XCA.DEALER_CODE = XDC.DEALER_CODE AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y' AND XDC.USER_ID = FND_GLOBAL.USER_ID) AND D.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE FROM XTR_TY_SUB_PRD_COMBIN_V) union all SELECT D.DEAL_NO DEAL_NO, NVL(D.TRANSACTION_NO, -4) TRANSACTION_NO, D.MARKET_DATA_SET MARKET_DATA_SET, D.CAP_OR_FLOOR CALL_OR_PUT, DECODE(D.DEAL_TYPE, 'BDO', DECODE(D.OPTION_COMMENCEMENT, NULL, 'EUROPEAN', 'AMERICAN'), NULL) OPTION_STYLE, DECODE(D.DEAL_TYPE, 'BOND', D.ACCEPTOR_CODE, NULL) ISSUER_CODE, D.COMPANY_CODE COMPANY_CODE, D.DEALER_CODE DEALER_CODE, D.CLIENT_CODE CLIENT_CODE, D.CPARTY_CODE CPARTY_CODE, D.PORTFOLIO_CODE PORTFOLIO_CODE, D.DEAL_LINKING_CODE LINK_CODE, DECODE(D.DEAL_TYPE,'BOND', 'MARKET', NULL) PRICING_MODEL, D.CURRENCY DEAL_CCY, NULL BUY_CCY, NULL SELL_CCY, NULL FOREIGN_CCY, NULL DOMESTIC_CCY, NULL BASE_CCY, NULL CONTRA_CCY, D.CURRENCY SENSITIVITY_CCY, DECODE(D.DEAL_TYPE, 'BDO', D.CURRENCY, NULL) PREMIUM_CCY, TO_NUMBER(NULL) BUY_AMOUNT, TO_NUMBER(NULL) SELL_AMOUNT, TO_NUMBER(NULL) FOREIGN_AMOUNT, TO_NUMBER(NULL) DOMESTIC_AMOUNT, TO_NUMBER(NULL) BASE_CCY_AMOUNT, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT, D.START_AMOUNT START_AMOUNT, DECODE(D.DEAL_TYPE, 'BDO', FACE_VALUE_AMOUNT, 'BOND', D.MATURITY_BALANCE_AMOUNT) FACE_VALUE, D.INTEREST_AMOUNT INTEREST, TO_NUMBER(NULL) ACCUM_INTEREST_BF, NULL ACCUM_INT_ACTION, DECODE(D.DEAL_TYPE, 'BOND', D.ACCRUED_INTEREST_PRICE, 0) ACCRUED_INTEREST, TO_NUMBER(NULL) INTEREST_SETTLED, TRUNC(D.DEAL_DATE) DEAL_DATE, TRUNC(DECODE(D.DEAL_TYPE, 'BDO', D.EXPIRY_DATE, 'BOND', D.MATURITY_DATE)) END_DATE, TRUNC(DECODE(D.DEAL_TYPE, 'BDO', D.EXPIRY_DATE, 'BOND', D.MATURITY_DATE)) GAP_DATE, D.DEAL_SUBTYPE DEAL_SUBTYPE, D.DEAL_TYPE DEAL_TYPE, NVL(D.DISCOUNT, 'N') DISCOUNT_YIELD, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE, TRUNC(D.MATURITY_DATE) MATURITY_DATE, NVL(D.NO_OF_DAYS,0) NO_OF_DAYS, 'M' MARKET_TYPE, DECODE(D.DEAL_TYPE, 'BDO', 'D', 'P') INSTR_TYPE, DECODE(D.DEAL_TYPE,'BDO', 'D', 'BOND', DECODE(D.DEAL_SUBTYPE, 'ISSUE', 'F', 'SHORT', 'F', 'I')) MM_FX_INSTR_TYPE, TRUNC(D.VALUE_DATE) VALUE_DATE, TRUNC(D.SETTLE_DATE) SETTLE_DATE, DECODE(D.PREMIUM_ACTION, 'PAY', (-1)*D.PREMIUM_AMOUNT, 'REC', D.PREMIUM_AMOUNT) PREMIUM_AMOUNT, D.PRODUCT_TYPE PRODUCT_TYPE, TRUNC(DECODE(D.DEAL_TYPE, 'BDO', D.OPTION_COMMENCEMENT, D.START_DATE)) START_DATE, TRUNC(D.START_DATE) PHY_START_DATE, NULL KNOCK_TYPE, TO_NUMBER(NULL) KNOCK_LEVEL, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS, D.INT_SWAP_REF INT_SWAP_REF, NULL INT_REF, D.BOND_ISSUE BOND_ISSUE_CODE, B.SECURITY_ID SECURITY_ID, TO_NUMBER(NULL) REF_SPOT_RATE, D.COUPON_ACTION COUPON_ACTION, D.COUPON_RATE COUPON_RATE, D.MARGIN MARGIN, D.CAPITAL_PRICE TRANSACTION_RATE, NULL INTEREST_FREQUENCY, D.FREQUENCY COUPON_FREQUENCY, TRUNC(DECODE(D.DEAL_TYPE, 'BOND', DECODE(D.COUPON_ACTION,'CUM',D.CUM_COUPON_DATE), D.NEXT_COUPON_DATE)) NEXT_COUPON_DATE, B.YEAR_CALC_TYPE DAY_COUNT_BASIS, TO_NUMBER(NULL) QUANTITY_REMAINING, TO_NUMBER(NULL) QUANTITY_REMAINING_USD, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB, D.ROUNDING_TYPE ROUNDING_TYPE, D.DAY_COUNT_TYPE DAY_COUNT_TYPE, D.PREPAID_INTEREST PREPAID_INTEREST FROM XTR_DEALS D, XTR_BOND_ISSUES B WHERE D.DEAL_TYPE IN ('BOND', 'BDO') AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE, 'BOND','SELL', 'QRM') AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE, 'BOND','COVER', 'QRM') AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE, 'BOND','REPURCH', 'QRM') AND D.STATUS_CODE = 'CURRENT' AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE FROM XTR_COMPANY_AUTHORITIES XCA, XTR_DEALER_CODES XDC WHERE D.COMPANY_CODE = XCA.PARTY_CODE AND XCA.DEALER_CODE = XDC.DEALER_CODE AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y' AND XDC.USER_ID = FND_GLOBAL.USER_ID) AND D.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE FROM XTR_TY_SUB_PRD_COMBIN_V) AND B.BOND_ISSUE_CODE=D.BOND_ISSUE union all SELECT D.DEAL_NO DEAL_NO, NVL(D.TRANSACTION_NO, -4) TRANSACTION_NO, D.MARKET_DATA_SET MARKET_DATA_SET, D.CAP_OR_FLOOR CALL_OR_PUT, DECODE(D.DEAL_TYPE, 'FXO', DECODE(D.OPTION_COMMENCEMENT, NULL, 'EUROPEAN', 'AMERICAN'), NULL) OPTION_STYLE, NULL ISSUER_CODE, D.COMPANY_CODE COMPANY_CODE, D.DEALER_CODE DEALER_CODE, D.CLIENT_CODE CLIENT_CODE, D.CPARTY_CODE CPARTY_CODE, D.PORTFOLIO_CODE PORTFOLIO_CODE, D.DEAL_LINKING_CODE LINK_CODE, DECODE(D.DEAL_TYPE, 'FX', 'FX_FORWARD', 'FXO', 'GARMAN_KOHL') PRICING_MODEL, NULL DEAL_CCY, D.CURRENCY_BUY BUY_CCY, D.CURRENCY_SELL SELL_CCY, D.REF_CCY FOREIGN_CCY, D.CTY_CCY DOMESTIC_CCY, C.CURRENCY_FIRST BASE_CCY, C.CURRENCY_SECOND CONTRA_CCY, DECODE(D.DEAL_TYPE, 'FX', C.CURRENCY_SECOND, 'FXO', D.CTY_CCY) SENSITIVITY_CCY, D.CURRENCY PREMIUM_CCY, D.BUY_AMOUNT BUY_AMOUNT, (-1)*D.SELL_AMOUNT SELL_AMOUNT, DECODE(D.REF_CCY, D.CURRENCY_BUY, D.BUY_AMOUNT, D.CURRENCY_SELL, (-1)*D.SELL_AMOUNT) FOREIGN_AMOUNT, DECODE(D.CTY_CCY, D.CURRENCY_BUY, D.BUY_AMOUNT, D.CURRENCY_SELL, (-1)*D.SELL_AMOUNT) DOMESTIC_AMOUNT, DECODE(C.CURRENCY_FIRST, D.CURRENCY_BUY, D.BUY_AMOUNT, D.CURRENCY_SELL,(-1)*D.SELL_AMOUNT) BASE_CCY_AMOUNT, DECODE(C.CURRENCY_SECOND, D.CURRENCY_BUY, D.BUY_AMOUNT, D.CURRENCY_SELL,(-1)*D.SELL_AMOUNT) CONTRA_CCY_AMOUNT, D.START_AMOUNT START_AMOUNT, NVL(D.FACE_VALUE_AMOUNT,0) FACE_VALUE, D.INTEREST_AMOUNT INTEREST, TO_NUMBER(NULL) ACCUM_INTEREST_BF, NULL ACCUM_INT_ACTION, NVL(D.ACCRUED_INTEREST_PRICE,0) ACCRUED_INTEREST, TO_NUMBER(NULL) INTEREST_SETTLED, TRUNC(D.DEAL_DATE) DEAL_DATE, TRUNC(DECODE(D.DEAL_TYPE, 'FX', D.VALUE_DATE, 'FXO', D.EXPIRY_DATE)) END_DATE, TRUNC(TO_DATE(NULL)) GAP_DATE, D.DEAL_SUBTYPE DEAL_SUBTYPE, D.DEAL_TYPE DEAL_TYPE, NVL(D.DISCOUNT, 'N') DISCOUNT_YIELD, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE, TRUNC(D.MATURITY_DATE) MATURITY_DATE, NVL(D.NO_OF_DAYS,0) NO_OF_DAYS, 'F' MARKET_TYPE, DECODE(D.DEAL_TYPE, 'FXO', 'D', 'P') INSTR_TYPE, NULL MM_FX_INSTR_TYPE, TRUNC(D.VALUE_DATE) VALUE_DATE, TRUNC(D.SETTLE_DATE) SETTLE_DATE, DECODE(D.PREMIUM_ACTION, 'PAY', (-1)*D.PREMIUM_AMOUNT, 'REC', D.PREMIUM_AMOUNT) PREMIUM_AMOUNT, D.PRODUCT_TYPE PRODUCT_TYPE, TRUNC(DECODE(D.DEAL_TYPE, 'FXO', D.OPTION_COMMENCEMENT, D.START_DATE)) START_DATE, TRUNC(D.START_DATE) PHY_START_DATE, DECODE(D.DEAL_TYPE, 'FXO', D.KNOCK_TYPE, NULL) KNOCK_TYPE, DECODE(D.DEAL_TYPE, 'FXO', D.KNOCK_LEVEL, TO_NUMBER(NULL)) KNOCK_LEVEL, TRUNC(DECODE(D.DEAL_TYPE, 'FXO', D.KNOCK_EXECUTE_DATE, TO_DATE(NULL))) KNOCK_EXECUTE_DATE, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS, D.INT_SWAP_REF INT_SWAP_REF, NULL INT_REF, D.BOND_ISSUE BOND_ISSUE_CODE, D.SECURITY_ID SECURITY_ID, DECODE(D.DEAL_TYPE, 'FX', D.BASE_RATE, 'FXO', TO_NUMBER(NULL)) REF_SPOT_RATE, D.COUPON_ACTION COUPON_ACTION, D.COUPON_RATE COUPON_RATE, D.MARGIN MARGIN, D.TRANSACTION_RATE TRANSACTION_RATE, NULL INTEREST_FREQUENCY, D.FREQUENCY COUPON_FREQUENCY, TRUNC(D.NEXT_COUPON_DATE) NEXT_COUPON_DATE, D.YEAR_CALC_TYPE DAY_COUNT_BASIS, TO_NUMBER(NULL) QUANTITY_REMAINING, TO_NUMBER(NULL) QUANTITY_REMAINING_USD, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB, D.ROUNDING_TYPE ROUNDING_TYPE, D.DAY_COUNT_TYPE DAY_COUNT_TYPE, D.PREPAID_INTEREST PREPAID_INTEREST FROM XTR_DEALS D, XTR_BUY_SELL_COMBINATIONS C WHERE ((D.DEAL_TYPE = 'FX') OR (D.DEAL_TYPE = 'FXO')) AND D.CURRENCY_BUY = C.CURRENCY_BUY AND D.CURRENCY_SELL = C.CURRENCY_SELL AND D.STATUS_CODE = 'CURRENT' AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE FROM XTR_COMPANY_AUTHORITIES XCA, XTR_DEALER_CODES XDC WHERE D.COMPANY_CODE = XCA.PARTY_CODE AND XCA.DEALER_CODE = XDC.DEALER_CODE AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y' AND XDC.USER_ID = FND_GLOBAL.USER_ID) AND D.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE FROM XTR_TY_SUB_PRD_COMBIN_V) union all SELECT IG.DEAL_NUMBER DEAL_NO, NVL(IG.TRANSACTION_NUMBER, -4) TRANSACTION_NO, NULL MARKET_DATA_SET, NULL CALL_OR_PUT, NULL OPTION_STYLE, NULL ISSUER_CODE, IG.COMPANY_CODE COMPANY_CODE, NULL DEALER_CODE, NULL CLIENT_CODE, IG.PARTY_CODE CPARTY_CODE, IG.PORTFOLIO PORTFOLIO_CODE, NULL LINK_CODE, NULL PRICING_MODEL, IG.CURRENCY DEAL_CCY, IG.CURRENCY BUY_CCY, NULL SELL_CCY, NULL FOREIGN_CCY, NULL DOMESTIC_CCY, NULL BASE_CCY, NULL CONTRA_CCY, IG.CURRENCY SENSITIVITY_CCY, NULL PREMIUM_CCY, TO_NUMBER(NULL) BUY_AMOUNT, TO_NUMBER(NULL) SELL_AMOUNT, TO_NUMBER(NULL) FOREIGN_AMOUNT, TO_NUMBER(NULL) DOMESTIC_AMOUNT, TO_NUMBER(NULL) BASE_CCY_AMOUNT, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT, TO_NUMBER(NULL) START_AMOUNT, IG.BALANCE_OUT FACE_VALUE, IG.INTEREST INTEREST, IG.ACCUM_INTEREST_BF ACCUM_INTEREST_BF, NULL ACCUM_INT_ACTION, IG.ACCUM_INTEREST_BF+IG.INTEREST ACCRUED_INTEREST, IG.INTEREST_SETTLED INTEREST_SETTLED, TRUNC(IG.TRANSFER_DATE) DEAL_DATE, TRUNC(TO_DATE(NULL)) END_DATE, TRUNC(TO_DATE(NULL)) GAP_DATE, DECODE(IG.PRINCIPAL_ACTION, 'REC', 'FUND', 'INVEST') DEAL_SUBTYPE, IG.DEAL_TYPE DEAL_TYPE, NULL DISCOUNT_YIELD, TRUNC(TO_DATE(NULL)) EXPIRATION_DATE, TRUNC(TO_DATE(NULL)) MATURITY_DATE, TO_NUMBER(NULL) NO_OF_DAYS, 'M' MARKET_TYPE, 'P' INSTR_TYPE, DECODE(SIGN(IG.BALANCE_OUT), -1, 'F', 'I') MM_FX_INSTR_TYPE, TRUNC(TO_DATE(NULL)) VALUE_DATE, TRUNC(TO_DATE(NULL)) SETTLE_DATE, TO_NUMBER(NULL) PREMIUM_AMOUNT, IG.PRODUCT_TYPE PRODUCT_TYPE, TRUNC(IG.TRANSFER_DATE) START_DATE, TRUNC(IG.TRANSFER_DATE) PHY_START_DATE, NULL KNOCK_TYPE, TO_NUMBER(NULL) KNOCK_LEVEL, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE, 'FIXED' INITIAL_BASIS, NULL INT_SWAP_REF, NULL INT_REF, NULL BOND_ISSUE_CODE, NULL SECURITY_ID, TO_NUMBER(NULL) REF_SPOT_RATE, NULL COUPON_ACTION, IG.INTEREST_RATE COUPON_RATE, TO_NUMBER(NULL) MARGIN, IG.INTEREST_RATE TRANSACTION_RATE, NULL INTEREST_FREQUENCY, TO_NUMBER(NULL) COUPON_FREQUENCY, TRUNC(TO_DATE(NULL)) NEXT_COUPON_DATE, NULL DAY_COUNT_BASIS, TO_NUMBER(NULL) QUANTITY_REMAINING, TO_NUMBER(NULL) QUANTITY_REMAINING_USD, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB, IG.ROUNDING_TYPE ROUNDING_TYPE, IG.DAY_COUNT_TYPE DAY_COUNT_TYPE, NULL PREPAID_INTEREST FROM XTR_INTERGROUP_TRANSFERS IG WHERE DEAL_TYPE='IG' AND IG.COMPANY_CODE IN (SELECT XCA.PARTY_CODE FROM XTR_COMPANY_AUTHORITIES XCA, XTR_DEALER_CODES XDC WHERE IG.COMPANY_CODE = XCA.PARTY_CODE AND XCA.DEALER_CODE = XDC.DEALER_CODE AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y' AND XDC.USER_ID = FND_GLOBAL.USER_ID) AND IG.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE FROM XTR_TY_SUB_PRD_COMBIN_V)

View Text - HTML Formatted

SELECT D.DEAL_NO DEAL_NO
, NVL(RT.TRANSACTION_NUMBER
, 1) TRANSACTION_NO
, D.MARKET_DATA_SET MARKET_DATA_SET
, D.CAP_OR_FLOOR CALL_OR_PUT
, NULL OPTION_STYLE
, NULL ISSUER_CODE
, RT.COMPANY_CODE COMPANY_CODE
, RT.DEALER_CODE DEALER_CODE
, RT.CLIENT_CODE CLIENT_CODE
, RT.CPARTY_CODE CPARTY_CODE
, RT.PORTFOLIO_CODE PORTFOLIO_CODE
, RT.DEAL_LINKING_CODE LINK_CODE
, NULL PRICING_MODEL
, D.CURRENCY DEAL_CCY
, NULL BUY_CCY
, NULL SELL_CCY
, NULL FOREIGN_CCY
, NULL DOMESTIC_CCY
, NULL BASE_CCY
, NULL CONTRA_CCY
, D.CURRENCY SENSITIVITY_CCY
, NULL PREMIUM_CCY
, TO_NUMBER(NULL) BUY_AMOUNT
, TO_NUMBER(NULL) SELL_AMOUNT
, TO_NUMBER(NULL) FOREIGN_AMOUNT
, TO_NUMBER(NULL) DOMESTIC_AMOUNT
, TO_NUMBER(NULL) BASE_CCY_AMOUNT
, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT
, RT.PRINCIPAL_ADJUST START_AMOUNT
, RT.BALANCE_OUT FACE_VALUE
, RT.INTEREST INTEREST
, RT.ACCUM_INTEREST_BF ACCUM_INTEREST_BF
, RT.ACCUM_INT_ACTION ACCUM_INT_ACTION
, TO_NUMBER(NULL) ACCRUED_INTEREST
, TO_NUMBER(NULL) INTEREST_SETTLED
, TRUNC(RT.DEAL_DATE) DEAL_DATE
, TRUNC(RT.MATURITY_DATE) END_DATE
, TRUNC(TO_DATE(NULL)) GAP_DATE
, RT.DEAL_SUBTYPE DEAL_SUBTYPE
, D.DEAL_TYPE DEAL_TYPE
, 'N' DISCOUNT_YIELD
, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE
, TRUNC(RT.MATURITY_DATE) MATURITY_DATE
, NVL(RT.NO_OF_DAYS
, 0) NO_OF_DAYS
, 'M' MARKET_TYPE
, 'P' INSTR_TYPE
, DECODE(D.DEAL_SUBTYPE
, 'FUND'
, 'F'
, 'INVEST'
, 'I') MM_FX_INSTR_TYPE
, TRUNC(D.VALUE_DATE) VALUE_DATE
, TRUNC(RT.SETTLE_DATE) SETTLE_DATE
, D.PREMIUM_AMOUNT PREMIUM_AMOUNT
, RT.PRODUCT_TYPE PRODUCT_TYPE
, TRUNC(RT.START_DATE) START_DATE
, TRUNC(RT.START_DATE) PHY_START_DATE
, NULL KNOCK_TYPE
, TO_NUMBER(NULL) KNOCK_LEVEL
, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE
, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS
, D.INT_SWAP_REF INT_SWAP_REF
, NULL INT_REF
, D.BOND_ISSUE BOND_ISSUE_CODE
, D.SECURITY_ID SECURITY_ID
, TO_NUMBER(NULL) REF_SPOT_RATE
, D.COUPON_ACTION COUPON_ACTION
, RT.INTEREST_RATE COUPON_RATE
, D.MARGIN MARGIN
, RT.INTEREST_RATE TRANSACTION_RATE
, RT.INTEREST_FREQ INTEREST_FREQUENCY
, DECODE(RT.INTEREST_FREQ
, 'M'
, 12
, 'Q'
, 4
, 'S'
, 2
, 'W'
, 52
, 'Y'
, 1
, NULL) COUPON_FREQUENCY
, TRUNC(D.NEXT_COUPON_DATE) NEXT_COUPON_DATE
, RT.YEAR_CALC_TYPE DAY_COUNT_BASIS
, TO_NUMBER(NULL) QUANTITY_REMAINING
, TO_NUMBER(NULL) QUANTITY_REMAINING_USD
, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB
, D.ROUNDING_TYPE ROUNDING_TYPE
, D.DAY_COUNT_TYPE DAY_COUNT_TYPE
, RT.PREPAID_INTEREST PREPAID_INTEREST
FROM XTR_DEALS D
, XTR_ROLLOVER_TRANSACTIONS RT
WHERE D.DEAL_NO = RT.DEAL_NUMBER
AND RT.DEAL_TYPE = 'ONC'
AND RT.STATUS_CODE = 'CURRENT'
AND RT.PRINCIPAL_ADJUST<>0
AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
WHERE D.COMPANY_CODE = XCA.PARTY_CODE
AND XCA.DEALER_CODE = XDC.DEALER_CODE
AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID)
AND RT.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE
FROM XTR_TY_SUB_PRD_COMBIN_V) UNION ALL SELECT D.DEAL_NO DEAL_NO
, NVL(D.TRANSACTION_NO
, -4) TRANSACTION_NO
, D.MARKET_DATA_SET MARKET_DATA_SET
, D.CAP_OR_FLOOR CALL_OR_PUT
, DECODE(D.DEAL_TYPE
, 'IRO'
, DECODE(D.OPTION_COMMENCEMENT
, NULL
, 'EUROPEAN'
, 'AMERICAN')
, 'SWPTN'
, DECODE(D.OPTION_COMMENCEMENT
, NULL
, 'EUROPEAN'
, 'AMERICAN')
, NULL) OPTION_STYLE
, DECODE(D.DEAL_TYPE
, 'STOCK'
, D.ACCEPTOR_CODE
, 'NI'
, D.ACCEPTOR_CODE
, NULL) ISSUER_CODE
, D.COMPANY_CODE COMPANY_CODE
, D.DEALER_CODE DEALER_CODE
, D.CLIENT_CODE CLIENT_CODE
, D.CPARTY_CODE CPARTY_CODE
, D.PORTFOLIO_CODE PORTFOLIO_CODE
, D.DEAL_LINKING_CODE LINK_CODE
, DECODE(D.DEAL_TYPE
, 'STOCK'
, 'MARKET'
, 'NI'
, 'DISC_METHOD'
, 'TMM'
, 'DISC_CASHFLOW'
, 'IRS'
, 'DISC_CASHFLOW'
, 'RTMM'
, 'DISC_CASHFLOW'
, 'FRA'
, DECODE(D.PRICING_MODEL
, 'FRA_YIELD'
, 'FRA_YIELD'
, 'FRA_DISC'
, 'FRA_DISC'
, 'FRA_YIELD')
, 'IRO'
, 'BLACK'
, NULL) PRICING_MODEL
, D.CURRENCY DEAL_CCY
, NULL BUY_CCY
, NULL SELL_CCY
, NULL FOREIGN_CCY
, NULL DOMESTIC_CCY
, NULL BASE_CCY
, NULL CONTRA_CCY
, D.CURRENCY SENSITIVITY_CCY
, DECODE(D.DEAL_TYPE
, 'IRO'
, D.CURRENCY
, 'SWPTN'
, D.CURRENCY
, NULL) PREMIUM_CCY
, TO_NUMBER(NULL) BUY_AMOUNT
, TO_NUMBER(NULL) SELL_AMOUNT
, TO_NUMBER(NULL) FOREIGN_AMOUNT
, TO_NUMBER(NULL) DOMESTIC_AMOUNT
, TO_NUMBER(NULL) BASE_CCY_AMOUNT
, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT
, D.START_AMOUNT START_AMOUNT
, DECODE(D.DEAL_TYPE
, 'FRA'
, FACE_VALUE_AMOUNT
, 'IRO'
, FACE_VALUE_AMOUNT
, 'IRS'
, D.FACE_VALUE_AMOUNT
, 'NI'
, D.MATURITY_BALANCE_AMOUNT
, 'RTMM'
, D.FACE_VALUE_AMOUNT
, 'SWPTN'
, D.FACE_VALUE_AMOUNT
, 'TMM'
, D.FACE_VALUE_AMOUNT
, 0) FACE_VALUE
, D.INTEREST_AMOUNT INTEREST
, TO_NUMBER(NULL) ACCUM_INTEREST_BF
, NULL ACCUM_INT_ACTION
, 0 ACCRUED_INTEREST
, TO_NUMBER(NULL) INTEREST_SETTLED
, TRUNC(D.DEAL_DATE) DEAL_DATE
, TRUNC(DECODE(D.DEAL_TYPE
, 'FRA'
, D.START_DATE
, 'IRO'
, D.EXPIRY_DATE
, 'IRS'
, D.MATURITY_DATE
, 'NI'
, D.MATURITY_DATE
, 'RTMM'
, D.MATURITY_DATE
, 'TMM'
, D.MATURITY_DATE
, 'SWPTN'
, D.EXPIRY_DATE
, TO_DATE(NULL))) END_DATE
, TRUNC(DECODE(D.DEAL_TYPE
, 'FRA'
, D.START_DATE
, 'IRO'
, D.EXPIRY_DATE
, 'NI'
, D.MATURITY_DATE
, 'SWPTN'
, D.EXPIRY_DATE
, TO_DATE(NULL))) GAP_DATE
, D.DEAL_SUBTYPE DEAL_SUBTYPE
, D.DEAL_TYPE DEAL_TYPE
, DECODE(D.DEAL_TYPE
, 'NI'
, DECODE(D.CALC_BASIS
, 'DISCOUNT'
, 'Y'
, 'N')
, NVL(D.DISCOUNT
, 'N')) DISCOUNT_YIELD
, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE
, TRUNC(D.MATURITY_DATE) MATURITY_DATE
, NVL(D.NO_OF_DAYS
, 0) NO_OF_DAYS
, DECODE (D.DEAL_TYPE
, 'STOCK'
, 'E'
, 'M') MARKET_TYPE
, DECODE(D.DEAL_TYPE
, 'IRO'
, 'D'
, 'FRA'
, 'D'
, 'IRS'
, 'D'
, 'SWPTN'
, 'D'
, 'P') INSTR_TYPE
, DECODE(D.DEAL_TYPE
, 'FRA'
, 'D'
, 'IRO'
, 'D'
, 'IRS'
, 'D'
, 'NI'
, DECODE(D.DEAL_SUBTYPE
, 'ISSUE'
, 'F'
, 'SHORT'
, 'F'
, 'I')
, 'RTMM'
, DECODE(D.DEAL_SUBTYPE
, 'FUND'
, 'F'
, 'INVEST'
, 'I')
, 'SWPTN'
, 'D'
, 'TMM'
, DECODE(D.DEAL_SUBTYPE
, 'FUND'
, 'F'
, 'INVEST'
, 'I')
, 'STOCK'
, 'I'
, NULL) MM_FX_INSTR_TYPE
, TRUNC(D.VALUE_DATE) VALUE_DATE
, TRUNC(D.SETTLE_DATE) SETTLE_DATE
, DECODE(D.PREMIUM_ACTION
, 'PAY'
, (-1)*D.PREMIUM_AMOUNT
, 'REC'
, D.PREMIUM_AMOUNT) PREMIUM_AMOUNT
, D.PRODUCT_TYPE PRODUCT_TYPE
, TRUNC(DECODE(D.DEAL_TYPE
, 'IRO'
, D.OPTION_COMMENCEMENT
, 'SWPTN'
, D.OPTION_COMMENCEMENT
, D.START_DATE)) START_DATE
, TRUNC(D.START_DATE) PHY_START_DATE
, NULL KNOCK_TYPE
, TO_NUMBER(NULL) KNOCK_LEVEL
, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE
, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS
, D.INT_SWAP_REF INT_SWAP_REF
, DECODE(D.DEAL_TYPE
, 'IRS'
, DECODE(D.FIXED_OR_FLOATING_RATE
, 'FLOAT'
, D.RATE_BASIS
, NULL)
, NULL) INT_REF
, D.BOND_ISSUE BOND_ISSUE_CODE
, D.SECURITY_ID SECURITY_ID
, TO_NUMBER(NULL) REF_SPOT_RATE
, D.COUPON_ACTION COUPON_ACTION
, DECODE(D.DEAL_TYPE
, 'SWPTN'
, D.INTEREST_RATE
, 'IRO'
, D.INTEREST_RATE
, 'NI'
, D.INTEREST_RATE
, D.COUPON_RATE) COUPON_RATE
, D.MARGIN MARGIN
, DECODE(D.DEAL_TYPE
, 'STOCK'
, D.CAPITAL_PRICE
, 'FRA'
, D.INTEREST_RATE
, 'IRO'
, D.INTEREST_RATE
, 'IRS'
, D.INTEREST_RATE
, 'NI'
, D.INTEREST_RATE
, 'RTMM'
, D.INTEREST_RATE
, 'SWPTN'
, D.INTEREST_RATE
, 'TMM'
, D.INTEREST_RATE) TRANSACTION_RATE
, NULL INTEREST_FREQUENCY
, DECODE(D.DEAL_TYPE
, 'IRS'
, 12/D.FREQUENCY
, 'SWPTN'
, 12/D.FREQUENCY
, 'TMM'
, DECODE(D.PAYMENT_FREQ
, 'ANNUAL'
, 1
, 'SEMI ANNUAL'
, 2
, 'QUARTERLY'
, 4
, 'BI MONTHLY'
, 6
, 'MONTHLY'
, 12
, 'WEEKLY'
, 52
, NULL)
, 'RTMM'
, DECODE(D.PAYMENT_FREQ
, 'ANNUAL'
, 1
, 'BI MONTHLY'
, 6
, 'FORTNIGHTLY'
, 24
, 'FOUR WEEKLY'
, 12
, 'MONTHLY'
, 12
, 'QUARTERLY'
, 4
, 'SEMI ANNUAL'
, 2
, 'WEEKLY'
, 52
, NULL)
, NULL) COUPON_FREQUENCY
, TRUNC(DECODE(D.DEAL_TYPE
, 'SWPTN'
, ADD_MONTHS(D.START_DATE
, D.FREQUENCY)
, D.NEXT_COUPON_DATE)) NEXT_COUPON_DATE
, D.YEAR_CALC_TYPE DAY_COUNT_BASIS
, TO_NUMBER(NULL) QUANTITY_REMAINING
, TO_NUMBER(NULL) QUANTITY_REMAINING_USD
, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB
, D.ROUNDING_TYPE ROUNDING_TYPE
, D.DAY_COUNT_TYPE DAY_COUNT_TYPE
, D.PREPAID_INTEREST PREPAID_INTEREST
FROM XTR_DEALS D
WHERE (D.DEAL_TYPE <> 'ONC')
AND D.DEAL_TYPE <> 'FX'
AND D.DEAL_TYPE <> 'FXO'
AND D.DEAL_TYPE <> 'BOND'
AND D.DEAL_TYPE <> 'BDO'
AND D.DEAL_TYPE <> 'STOCK'
AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE
, 'NI'
, 'SELL'
, 'QRM')
AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE
, 'NI'
, 'COVER'
, 'QRM')
AND D.STATUS_CODE = 'CURRENT'
AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
WHERE D.COMPANY_CODE = XCA.PARTY_CODE
AND XCA.DEALER_CODE = XDC.DEALER_CODE
AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID)
AND D.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE
FROM XTR_TY_SUB_PRD_COMBIN_V) UNION ALL SELECT D.DEAL_NO DEAL_NO
, NVL(D.TRANSACTION_NO
, -4) TRANSACTION_NO
, D.MARKET_DATA_SET MARKET_DATA_SET
, D.CAP_OR_FLOOR CALL_OR_PUT
, NULL OPTION_STYLE
, D.ACCEPTOR_CODE ISSUER_CODE
, D.COMPANY_CODE COMPANY_CODE
, D.DEALER_CODE DEALER_CODE
, D.CLIENT_CODE CLIENT_CODE
, D.CPARTY_CODE CPARTY_CODE
, D.PORTFOLIO_CODE PORTFOLIO_CODE
, D.DEAL_LINKING_CODE LINK_CODE
, 'MARKET' PRICING_MODEL
, D.CURRENCY DEAL_CCY
, NULL BUY_CCY
, NULL SELL_CCY
, NULL FOREIGN_CCY
, NULL DOMESTIC_CCY
, NULL BASE_CCY
, NULL CONTRA_CCY
, D.CURRENCY SENSITIVITY_CCY
, NULL PREMIUM_CCY
, TO_NUMBER(NULL) BUY_AMOUNT
, TO_NUMBER(NULL) SELL_AMOUNT
, TO_NUMBER(NULL) FOREIGN_AMOUNT
, TO_NUMBER(NULL) DOMESTIC_AMOUNT
, TO_NUMBER(NULL) BASE_CCY_AMOUNT
, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT
, D.START_AMOUNT START_AMOUNT
, 0 FACE_VALUE
, D.INTEREST_AMOUNT INTEREST
, TO_NUMBER(NULL) ACCUM_INTEREST_BF
, NULL ACCUM_INT_ACTION
, 0 ACCRUED_INTEREST
, TO_NUMBER(NULL) INTEREST_SETTLED
, TRUNC(D.DEAL_DATE) DEAL_DATE
, TRUNC(TO_DATE(NULL)) END_DATE
, TRUNC(TO_DATE(NULL)) GAP_DATE
, D.DEAL_SUBTYPE DEAL_SUBTYPE
, D.DEAL_TYPE DEAL_TYPE
, NVL(D.DISCOUNT
, 'N') DISCOUNT_YIELD
, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE
, TRUNC(D.MATURITY_DATE) MATURITY_DATE
, NVL(D.NO_OF_DAYS
, 0) NO_OF_DAYS
, 'E' MARKET_TYPE
, 'P' INSTR_TYPE
, 'I' MM_FX_INSTR_TYPE
, TRUNC(D.VALUE_DATE) VALUE_DATE
, TRUNC(D.SETTLE_DATE) SETTLE_DATE
, DECODE(D.PREMIUM_ACTION
, 'PAY'
, (-1)*D.PREMIUM_AMOUNT
, 'REC'
, D.PREMIUM_AMOUNT) PREMIUM_AMOUNT
, D.PRODUCT_TYPE PRODUCT_TYPE
, TRUNC(D.START_DATE) START_DATE
, TRUNC(D.START_DATE) PHY_START_DATE
, NULL KNOCK_TYPE
, TO_NUMBER(NULL) KNOCK_LEVEL
, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE
, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS
, D.INT_SWAP_REF INT_SWAP_REF
, NULL INT_REF
, D.BOND_ISSUE BOND_ISSUE_CODE
, D.SECURITY_ID SECURITY_ID
, TO_NUMBER(NULL) REF_SPOT_RATE
, D.COUPON_ACTION COUPON_ACTION
, D.COUPON_RATE COUPON_RATE
, D.MARGIN MARGIN
, D.CAPITAL_PRICE TRANSACTION_RATE
, NULL INTEREST_FREQUENCY
, TO_NUMBER(NULL) COUPON_FREQUENCY
, TRUNC(D.NEXT_COUPON_DATE) NEXT_COUPON_DATE
, D.YEAR_CALC_TYPE DAY_COUNT_BASIS
, D.REMAINING_QUANTITY QUANTITY_REMAINING
, D.REMAINING_QUANTITY QUANTITY_REMAINING_USD
, D.REMAINING_QUANTITY QUANTITY_REMAINING_SOB
, D.ROUNDING_TYPE ROUNDING_TYPE
, D.DAY_COUNT_TYPE DAY_COUNT_TYPE
, D.PREPAID_INTEREST PREPAID_INTEREST
FROM XTR_DEALS D
WHERE D.DEAL_TYPE = 'STOCK'
AND D.DEAL_SUBTYPE = 'BUY'
AND D.STATUS_CODE = 'CURRENT'
AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
WHERE D.COMPANY_CODE = XCA.PARTY_CODE
AND XCA.DEALER_CODE = XDC.DEALER_CODE
AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID)
AND D.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE
FROM XTR_TY_SUB_PRD_COMBIN_V) UNION ALL SELECT D.DEAL_NO DEAL_NO
, NVL(D.TRANSACTION_NO
, -4) TRANSACTION_NO
, D.MARKET_DATA_SET MARKET_DATA_SET
, D.CAP_OR_FLOOR CALL_OR_PUT
, DECODE(D.DEAL_TYPE
, 'BDO'
, DECODE(D.OPTION_COMMENCEMENT
, NULL
, 'EUROPEAN'
, 'AMERICAN')
, NULL) OPTION_STYLE
, DECODE(D.DEAL_TYPE
, 'BOND'
, D.ACCEPTOR_CODE
, NULL) ISSUER_CODE
, D.COMPANY_CODE COMPANY_CODE
, D.DEALER_CODE DEALER_CODE
, D.CLIENT_CODE CLIENT_CODE
, D.CPARTY_CODE CPARTY_CODE
, D.PORTFOLIO_CODE PORTFOLIO_CODE
, D.DEAL_LINKING_CODE LINK_CODE
, DECODE(D.DEAL_TYPE
, 'BOND'
, 'MARKET'
, NULL) PRICING_MODEL
, D.CURRENCY DEAL_CCY
, NULL BUY_CCY
, NULL SELL_CCY
, NULL FOREIGN_CCY
, NULL DOMESTIC_CCY
, NULL BASE_CCY
, NULL CONTRA_CCY
, D.CURRENCY SENSITIVITY_CCY
, DECODE(D.DEAL_TYPE
, 'BDO'
, D.CURRENCY
, NULL) PREMIUM_CCY
, TO_NUMBER(NULL) BUY_AMOUNT
, TO_NUMBER(NULL) SELL_AMOUNT
, TO_NUMBER(NULL) FOREIGN_AMOUNT
, TO_NUMBER(NULL) DOMESTIC_AMOUNT
, TO_NUMBER(NULL) BASE_CCY_AMOUNT
, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT
, D.START_AMOUNT START_AMOUNT
, DECODE(D.DEAL_TYPE
, 'BDO'
, FACE_VALUE_AMOUNT
, 'BOND'
, D.MATURITY_BALANCE_AMOUNT) FACE_VALUE
, D.INTEREST_AMOUNT INTEREST
, TO_NUMBER(NULL) ACCUM_INTEREST_BF
, NULL ACCUM_INT_ACTION
, DECODE(D.DEAL_TYPE
, 'BOND'
, D.ACCRUED_INTEREST_PRICE
, 0) ACCRUED_INTEREST
, TO_NUMBER(NULL) INTEREST_SETTLED
, TRUNC(D.DEAL_DATE) DEAL_DATE
, TRUNC(DECODE(D.DEAL_TYPE
, 'BDO'
, D.EXPIRY_DATE
, 'BOND'
, D.MATURITY_DATE)) END_DATE
, TRUNC(DECODE(D.DEAL_TYPE
, 'BDO'
, D.EXPIRY_DATE
, 'BOND'
, D.MATURITY_DATE)) GAP_DATE
, D.DEAL_SUBTYPE DEAL_SUBTYPE
, D.DEAL_TYPE DEAL_TYPE
, NVL(D.DISCOUNT
, 'N') DISCOUNT_YIELD
, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE
, TRUNC(D.MATURITY_DATE) MATURITY_DATE
, NVL(D.NO_OF_DAYS
, 0) NO_OF_DAYS
, 'M' MARKET_TYPE
, DECODE(D.DEAL_TYPE
, 'BDO'
, 'D'
, 'P') INSTR_TYPE
, DECODE(D.DEAL_TYPE
, 'BDO'
, 'D'
, 'BOND'
, DECODE(D.DEAL_SUBTYPE
, 'ISSUE'
, 'F'
, 'SHORT'
, 'F'
, 'I')) MM_FX_INSTR_TYPE
, TRUNC(D.VALUE_DATE) VALUE_DATE
, TRUNC(D.SETTLE_DATE) SETTLE_DATE
, DECODE(D.PREMIUM_ACTION
, 'PAY'
, (-1)*D.PREMIUM_AMOUNT
, 'REC'
, D.PREMIUM_AMOUNT) PREMIUM_AMOUNT
, D.PRODUCT_TYPE PRODUCT_TYPE
, TRUNC(DECODE(D.DEAL_TYPE
, 'BDO'
, D.OPTION_COMMENCEMENT
, D.START_DATE)) START_DATE
, TRUNC(D.START_DATE) PHY_START_DATE
, NULL KNOCK_TYPE
, TO_NUMBER(NULL) KNOCK_LEVEL
, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE
, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS
, D.INT_SWAP_REF INT_SWAP_REF
, NULL INT_REF
, D.BOND_ISSUE BOND_ISSUE_CODE
, B.SECURITY_ID SECURITY_ID
, TO_NUMBER(NULL) REF_SPOT_RATE
, D.COUPON_ACTION COUPON_ACTION
, D.COUPON_RATE COUPON_RATE
, D.MARGIN MARGIN
, D.CAPITAL_PRICE TRANSACTION_RATE
, NULL INTEREST_FREQUENCY
, D.FREQUENCY COUPON_FREQUENCY
, TRUNC(DECODE(D.DEAL_TYPE
, 'BOND'
, DECODE(D.COUPON_ACTION
, 'CUM'
, D.CUM_COUPON_DATE)
, D.NEXT_COUPON_DATE)) NEXT_COUPON_DATE
, B.YEAR_CALC_TYPE DAY_COUNT_BASIS
, TO_NUMBER(NULL) QUANTITY_REMAINING
, TO_NUMBER(NULL) QUANTITY_REMAINING_USD
, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB
, D.ROUNDING_TYPE ROUNDING_TYPE
, D.DAY_COUNT_TYPE DAY_COUNT_TYPE
, D.PREPAID_INTEREST PREPAID_INTEREST
FROM XTR_DEALS D
, XTR_BOND_ISSUES B
WHERE D.DEAL_TYPE IN ('BOND'
, 'BDO')
AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE
, 'BOND'
, 'SELL'
, 'QRM')
AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE
, 'BOND'
, 'COVER'
, 'QRM')
AND D.DEAL_SUBTYPE <> DECODE (D.DEAL_TYPE
, 'BOND'
, 'REPURCH'
, 'QRM')
AND D.STATUS_CODE = 'CURRENT'
AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
WHERE D.COMPANY_CODE = XCA.PARTY_CODE
AND XCA.DEALER_CODE = XDC.DEALER_CODE
AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID)
AND D.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE
FROM XTR_TY_SUB_PRD_COMBIN_V)
AND B.BOND_ISSUE_CODE=D.BOND_ISSUE UNION ALL SELECT D.DEAL_NO DEAL_NO
, NVL(D.TRANSACTION_NO
, -4) TRANSACTION_NO
, D.MARKET_DATA_SET MARKET_DATA_SET
, D.CAP_OR_FLOOR CALL_OR_PUT
, DECODE(D.DEAL_TYPE
, 'FXO'
, DECODE(D.OPTION_COMMENCEMENT
, NULL
, 'EUROPEAN'
, 'AMERICAN')
, NULL) OPTION_STYLE
, NULL ISSUER_CODE
, D.COMPANY_CODE COMPANY_CODE
, D.DEALER_CODE DEALER_CODE
, D.CLIENT_CODE CLIENT_CODE
, D.CPARTY_CODE CPARTY_CODE
, D.PORTFOLIO_CODE PORTFOLIO_CODE
, D.DEAL_LINKING_CODE LINK_CODE
, DECODE(D.DEAL_TYPE
, 'FX'
, 'FX_FORWARD'
, 'FXO'
, 'GARMAN_KOHL') PRICING_MODEL
, NULL DEAL_CCY
, D.CURRENCY_BUY BUY_CCY
, D.CURRENCY_SELL SELL_CCY
, D.REF_CCY FOREIGN_CCY
, D.CTY_CCY DOMESTIC_CCY
, C.CURRENCY_FIRST BASE_CCY
, C.CURRENCY_SECOND CONTRA_CCY
, DECODE(D.DEAL_TYPE
, 'FX'
, C.CURRENCY_SECOND
, 'FXO'
, D.CTY_CCY) SENSITIVITY_CCY
, D.CURRENCY PREMIUM_CCY
, D.BUY_AMOUNT BUY_AMOUNT
, (-1)*D.SELL_AMOUNT SELL_AMOUNT
, DECODE(D.REF_CCY
, D.CURRENCY_BUY
, D.BUY_AMOUNT
, D.CURRENCY_SELL
, (-1)*D.SELL_AMOUNT) FOREIGN_AMOUNT
, DECODE(D.CTY_CCY
, D.CURRENCY_BUY
, D.BUY_AMOUNT
, D.CURRENCY_SELL
, (-1)*D.SELL_AMOUNT) DOMESTIC_AMOUNT
, DECODE(C.CURRENCY_FIRST
, D.CURRENCY_BUY
, D.BUY_AMOUNT
, D.CURRENCY_SELL
, (-1)*D.SELL_AMOUNT) BASE_CCY_AMOUNT
, DECODE(C.CURRENCY_SECOND
, D.CURRENCY_BUY
, D.BUY_AMOUNT
, D.CURRENCY_SELL
, (-1)*D.SELL_AMOUNT) CONTRA_CCY_AMOUNT
, D.START_AMOUNT START_AMOUNT
, NVL(D.FACE_VALUE_AMOUNT
, 0) FACE_VALUE
, D.INTEREST_AMOUNT INTEREST
, TO_NUMBER(NULL) ACCUM_INTEREST_BF
, NULL ACCUM_INT_ACTION
, NVL(D.ACCRUED_INTEREST_PRICE
, 0) ACCRUED_INTEREST
, TO_NUMBER(NULL) INTEREST_SETTLED
, TRUNC(D.DEAL_DATE) DEAL_DATE
, TRUNC(DECODE(D.DEAL_TYPE
, 'FX'
, D.VALUE_DATE
, 'FXO'
, D.EXPIRY_DATE)) END_DATE
, TRUNC(TO_DATE(NULL)) GAP_DATE
, D.DEAL_SUBTYPE DEAL_SUBTYPE
, D.DEAL_TYPE DEAL_TYPE
, NVL(D.DISCOUNT
, 'N') DISCOUNT_YIELD
, TRUNC(D.EXPIRY_DATE) EXPIRATION_DATE
, TRUNC(D.MATURITY_DATE) MATURITY_DATE
, NVL(D.NO_OF_DAYS
, 0) NO_OF_DAYS
, 'F' MARKET_TYPE
, DECODE(D.DEAL_TYPE
, 'FXO'
, 'D'
, 'P') INSTR_TYPE
, NULL MM_FX_INSTR_TYPE
, TRUNC(D.VALUE_DATE) VALUE_DATE
, TRUNC(D.SETTLE_DATE) SETTLE_DATE
, DECODE(D.PREMIUM_ACTION
, 'PAY'
, (-1)*D.PREMIUM_AMOUNT
, 'REC'
, D.PREMIUM_AMOUNT) PREMIUM_AMOUNT
, D.PRODUCT_TYPE PRODUCT_TYPE
, TRUNC(DECODE(D.DEAL_TYPE
, 'FXO'
, D.OPTION_COMMENCEMENT
, D.START_DATE)) START_DATE
, TRUNC(D.START_DATE) PHY_START_DATE
, DECODE(D.DEAL_TYPE
, 'FXO'
, D.KNOCK_TYPE
, NULL) KNOCK_TYPE
, DECODE(D.DEAL_TYPE
, 'FXO'
, D.KNOCK_LEVEL
, TO_NUMBER(NULL)) KNOCK_LEVEL
, TRUNC(DECODE(D.DEAL_TYPE
, 'FXO'
, D.KNOCK_EXECUTE_DATE
, TO_DATE(NULL))) KNOCK_EXECUTE_DATE
, D.FIXED_OR_FLOATING_RATE INITIAL_BASIS
, D.INT_SWAP_REF INT_SWAP_REF
, NULL INT_REF
, D.BOND_ISSUE BOND_ISSUE_CODE
, D.SECURITY_ID SECURITY_ID
, DECODE(D.DEAL_TYPE
, 'FX'
, D.BASE_RATE
, 'FXO'
, TO_NUMBER(NULL)) REF_SPOT_RATE
, D.COUPON_ACTION COUPON_ACTION
, D.COUPON_RATE COUPON_RATE
, D.MARGIN MARGIN
, D.TRANSACTION_RATE TRANSACTION_RATE
, NULL INTEREST_FREQUENCY
, D.FREQUENCY COUPON_FREQUENCY
, TRUNC(D.NEXT_COUPON_DATE) NEXT_COUPON_DATE
, D.YEAR_CALC_TYPE DAY_COUNT_BASIS
, TO_NUMBER(NULL) QUANTITY_REMAINING
, TO_NUMBER(NULL) QUANTITY_REMAINING_USD
, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB
, D.ROUNDING_TYPE ROUNDING_TYPE
, D.DAY_COUNT_TYPE DAY_COUNT_TYPE
, D.PREPAID_INTEREST PREPAID_INTEREST
FROM XTR_DEALS D
, XTR_BUY_SELL_COMBINATIONS C
WHERE ((D.DEAL_TYPE = 'FX') OR (D.DEAL_TYPE = 'FXO'))
AND D.CURRENCY_BUY = C.CURRENCY_BUY
AND D.CURRENCY_SELL = C.CURRENCY_SELL
AND D.STATUS_CODE = 'CURRENT'
AND D.COMPANY_CODE IN (SELECT XCA.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
WHERE D.COMPANY_CODE = XCA.PARTY_CODE
AND XCA.DEALER_CODE = XDC.DEALER_CODE
AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID)
AND D.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE
FROM XTR_TY_SUB_PRD_COMBIN_V) UNION ALL SELECT IG.DEAL_NUMBER DEAL_NO
, NVL(IG.TRANSACTION_NUMBER
, -4) TRANSACTION_NO
, NULL MARKET_DATA_SET
, NULL CALL_OR_PUT
, NULL OPTION_STYLE
, NULL ISSUER_CODE
, IG.COMPANY_CODE COMPANY_CODE
, NULL DEALER_CODE
, NULL CLIENT_CODE
, IG.PARTY_CODE CPARTY_CODE
, IG.PORTFOLIO PORTFOLIO_CODE
, NULL LINK_CODE
, NULL PRICING_MODEL
, IG.CURRENCY DEAL_CCY
, IG.CURRENCY BUY_CCY
, NULL SELL_CCY
, NULL FOREIGN_CCY
, NULL DOMESTIC_CCY
, NULL BASE_CCY
, NULL CONTRA_CCY
, IG.CURRENCY SENSITIVITY_CCY
, NULL PREMIUM_CCY
, TO_NUMBER(NULL) BUY_AMOUNT
, TO_NUMBER(NULL) SELL_AMOUNT
, TO_NUMBER(NULL) FOREIGN_AMOUNT
, TO_NUMBER(NULL) DOMESTIC_AMOUNT
, TO_NUMBER(NULL) BASE_CCY_AMOUNT
, TO_NUMBER(NULL) CONTRA_CCY_AMOUNT
, TO_NUMBER(NULL) START_AMOUNT
, IG.BALANCE_OUT FACE_VALUE
, IG.INTEREST INTEREST
, IG.ACCUM_INTEREST_BF ACCUM_INTEREST_BF
, NULL ACCUM_INT_ACTION
, IG.ACCUM_INTEREST_BF+IG.INTEREST ACCRUED_INTEREST
, IG.INTEREST_SETTLED INTEREST_SETTLED
, TRUNC(IG.TRANSFER_DATE) DEAL_DATE
, TRUNC(TO_DATE(NULL)) END_DATE
, TRUNC(TO_DATE(NULL)) GAP_DATE
, DECODE(IG.PRINCIPAL_ACTION
, 'REC'
, 'FUND'
, 'INVEST') DEAL_SUBTYPE
, IG.DEAL_TYPE DEAL_TYPE
, NULL DISCOUNT_YIELD
, TRUNC(TO_DATE(NULL)) EXPIRATION_DATE
, TRUNC(TO_DATE(NULL)) MATURITY_DATE
, TO_NUMBER(NULL) NO_OF_DAYS
, 'M' MARKET_TYPE
, 'P' INSTR_TYPE
, DECODE(SIGN(IG.BALANCE_OUT)
, -1
, 'F'
, 'I') MM_FX_INSTR_TYPE
, TRUNC(TO_DATE(NULL)) VALUE_DATE
, TRUNC(TO_DATE(NULL)) SETTLE_DATE
, TO_NUMBER(NULL) PREMIUM_AMOUNT
, IG.PRODUCT_TYPE PRODUCT_TYPE
, TRUNC(IG.TRANSFER_DATE) START_DATE
, TRUNC(IG.TRANSFER_DATE) PHY_START_DATE
, NULL KNOCK_TYPE
, TO_NUMBER(NULL) KNOCK_LEVEL
, TRUNC(TO_DATE(NULL)) KNOCK_EXECUTE_DATE
, 'FIXED' INITIAL_BASIS
, NULL INT_SWAP_REF
, NULL INT_REF
, NULL BOND_ISSUE_CODE
, NULL SECURITY_ID
, TO_NUMBER(NULL) REF_SPOT_RATE
, NULL COUPON_ACTION
, IG.INTEREST_RATE COUPON_RATE
, TO_NUMBER(NULL) MARGIN
, IG.INTEREST_RATE TRANSACTION_RATE
, NULL INTEREST_FREQUENCY
, TO_NUMBER(NULL) COUPON_FREQUENCY
, TRUNC(TO_DATE(NULL)) NEXT_COUPON_DATE
, NULL DAY_COUNT_BASIS
, TO_NUMBER(NULL) QUANTITY_REMAINING
, TO_NUMBER(NULL) QUANTITY_REMAINING_USD
, TO_NUMBER(NULL) QUANTITY_REMAINING_SOB
, IG.ROUNDING_TYPE ROUNDING_TYPE
, IG.DAY_COUNT_TYPE DAY_COUNT_TYPE
, NULL PREPAID_INTEREST
FROM XTR_INTERGROUP_TRANSFERS IG
WHERE DEAL_TYPE='IG'
AND IG.COMPANY_CODE IN (SELECT XCA.PARTY_CODE
FROM XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
WHERE IG.COMPANY_CODE = XCA.PARTY_CODE
AND XCA.DEALER_CODE = XDC.DEALER_CODE
AND XCA.COMPANY_AUTHORISED_FOR_INPUT = 'Y'
AND XDC.USER_ID = FND_GLOBAL.USER_ID)
AND IG.PRODUCT_TYPE IN (SELECT PRODUCT_TYPE
FROM XTR_TY_SUB_PRD_COMBIN_V)