View: QRM_CURRENT_DEALS_V

Product:	QRM - Risk Management
Description:	Current Treasury deals used by Risk Analysis
Implementation/DBA Data:	APPS.QRM_CURRENT_DEALS_V

View Text

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)

Columns