VIEW: APPS.XTR_IRS_ELIGIBLE_DEALS_V

Source

View Text - Preformatted

SELECT DISTINCT NULL ACCOUNT_NO, D.BROKERAGE_AMOUNT BROKERAGE_AMOUNT, NULL CAP_OR_FLOOR, D.COMPANY_CODE COMPANY_CODE, NULL CONTRACT_CODE, D.CURRENCY CURRENCYA, NULL CURRENCYB, D.DEAL_DATE DEAL_DATE, D.DEAL_NO DEAL_NO, D.DEAL_SUBTYPE DEAL_SUBTYPE, D.DEAL_TYPE DEAL_TYPE, nvl(D.DISCOUNT,'N') DISCOUNT_YIELD, RT.MATURITY_DATE EFFECTIVE_DATE, decode(CP.PARAMETER_VALUE_CODE, 'TRADE', D.DEAL_DATE, D.START_DATE) ELIGIBLE_DATE, D.EXPIRY_DATE EXPIRY_DATE, D.FACE_VALUE_AMOUNT FACE_VALUE, 0 FX_REVAL_PRINCIPAL_BAL, 0 FXO_SELL_REF_AMOUNT, D.MARKET_DATA_SET MARKET_DATA_SET, RT.MATURITY_DATE MATURITY_DATE, D.PORTFOLIO_CODE PORTFOLIO_CODE, D.PREMIUM_ACTION PREMIUM_ACTION, D.PREMIUM_AMOUNT PREMIUM_AMOUNT, NULL PREMIUM_CCY, D.PRICING_MODEL PRICING_MODEL, D.PRODUCT_TYPE PRODUCT_TYPE, D.SETTLE_ACTION SETTLE_ACTION, D.SETTLE_AMOUNT SETTLE_AMOUNT, D.SETTLE_DATE SETTLE_DATE, D.START_DATE START_DATE, D.STATUS_CODE STATUS_CODE, D.INT_SWAP_REF SWAP_REF, nvl(D.TRANSACTION_NO, 1) TRANSACTION_NO, D.INTEREST_RATE TRANSACTION_RATE, D.YEAR_BASIS YEAR_BASIS, D.YEAR_CALC_TYPE YEAR_CALC_TYPE FROM XTR_DEALS D, XTR_COMPANY_PARAMETERS CP, XTR_ROLLOVER_TRANSACTIONS RT WHERE D.DEAL_TYPE = 'IRS' and d.deal_no = rt.deal_number and D.STATUS_CODE <> 'CANCELLED' and D.PRICING_MODEL <> 'NO_REVAL' and D.LAST_REVAL_BATCH_ID is null and D.COMPANY_CODE = CP.COMPANY_CODE and CP.PARAMETER_CODE = 'ACCNT_TSDTM' and rt.maturity_date = (select max(maturity_date) from xtr_rollover_transactions RT1 where rt.deal_number = rt1.deal_number)

View Text - HTML Formatted

SELECT DISTINCT NULL ACCOUNT_NO
, D.BROKERAGE_AMOUNT BROKERAGE_AMOUNT
, NULL CAP_OR_FLOOR
, D.COMPANY_CODE COMPANY_CODE
, NULL CONTRACT_CODE
, D.CURRENCY CURRENCYA
, NULL CURRENCYB
, D.DEAL_DATE DEAL_DATE
, D.DEAL_NO DEAL_NO
, D.DEAL_SUBTYPE DEAL_SUBTYPE
, D.DEAL_TYPE DEAL_TYPE
, NVL(D.DISCOUNT
, 'N') DISCOUNT_YIELD
, RT.MATURITY_DATE EFFECTIVE_DATE
, DECODE(CP.PARAMETER_VALUE_CODE
, 'TRADE'
, D.DEAL_DATE
, D.START_DATE) ELIGIBLE_DATE
, D.EXPIRY_DATE EXPIRY_DATE
, D.FACE_VALUE_AMOUNT FACE_VALUE
, 0 FX_REVAL_PRINCIPAL_BAL
, 0 FXO_SELL_REF_AMOUNT
, D.MARKET_DATA_SET MARKET_DATA_SET
, RT.MATURITY_DATE MATURITY_DATE
, D.PORTFOLIO_CODE PORTFOLIO_CODE
, D.PREMIUM_ACTION PREMIUM_ACTION
, D.PREMIUM_AMOUNT PREMIUM_AMOUNT
, NULL PREMIUM_CCY
, D.PRICING_MODEL PRICING_MODEL
, D.PRODUCT_TYPE PRODUCT_TYPE
, D.SETTLE_ACTION SETTLE_ACTION
, D.SETTLE_AMOUNT SETTLE_AMOUNT
, D.SETTLE_DATE SETTLE_DATE
, D.START_DATE START_DATE
, D.STATUS_CODE STATUS_CODE
, D.INT_SWAP_REF SWAP_REF
, NVL(D.TRANSACTION_NO
, 1) TRANSACTION_NO
, D.INTEREST_RATE TRANSACTION_RATE
, D.YEAR_BASIS YEAR_BASIS
, D.YEAR_CALC_TYPE YEAR_CALC_TYPE
FROM XTR_DEALS D
, XTR_COMPANY_PARAMETERS CP
, XTR_ROLLOVER_TRANSACTIONS RT
WHERE D.DEAL_TYPE = 'IRS'
AND D.DEAL_NO = RT.DEAL_NUMBER
AND D.STATUS_CODE <> 'CANCELLED'
AND D.PRICING_MODEL <> 'NO_REVAL'
AND D.LAST_REVAL_BATCH_ID IS NULL
AND D.COMPANY_CODE = CP.COMPANY_CODE
AND CP.PARAMETER_CODE = 'ACCNT_TSDTM'
AND RT.MATURITY_DATE = (SELECT MAX(MATURITY_DATE)
FROM XTR_ROLLOVER_TRANSACTIONS RT1
WHERE RT.DEAL_NUMBER = RT1.DEAL_NUMBER)