VIEW: APPS.XTR_NI_ELIGIBLE_DEALS_V

Source

View Text - Preformatted

SELECT NULL ACCOUNT_NO, RT.BROKERAGE_AMOUNT BROKERAGE_AMOUNT, NULL CAP_OR_FLOOR, RT.COMPANY_CODE COMPANY_CODE, NULL CONTRACT_CODE, RT.CURRENCY CURRENCYA, NULL CURRENCYB, D.DEAL_DATE DEAL_DATE, RT.DEAL_NUMBER DEAL_NO, RT.DEAL_SUBTYPE DEAL_SUBTYPE, RT.DEAL_TYPE DEAL_TYPE, decode(D.CALC_BASIS,'DISCOUNT','Y','N') DISCOUNT_YIELD, RT.MATURITY_DATE EFFECTIVE_DATE, decode(CP.PARAMETER_VALUE_CODE, 'TRADE', RT.DEAL_DATE, RT.START_DATE) ELIGIBLE_DATE, D.EXPIRY_DATE EXPIRY_DATE, RT.BALANCE_OUT FACE_VALUE, D.MATURITY_BALANCE_AMOUNT /decode(D.MATURITY_BALANCE_HCE_AMOUNT,0,1,D.MATURITY_BALANCE_HCE_AMOUNT) FX_REVAL_PRINCIPAL_BAL, 0 FXO_SELL_REF_AMOUNT, D.MARKET_DATA_SET MARKET_DATA_SET, RT.MATURITY_DATE MATURITY_DATE, RT.PORTFOLIO_CODE PORTFOLIO_CODE, NULL PREMIUM_ACTION, 0 PREMIUM_AMOUNT, NULL PREMIUM_CCY, D.PRICING_MODEL PRICING_MODEL, RT.PRODUCT_TYPE PRODUCT_TYPE, D.SETTLE_ACTION SETTLE_ACTION, D.SETTLE_AMOUNT SETTLE_AMOUNT, D.SETTLE_DATE SETTLE_DATE, D.START_DATE START_DATE, RT.STATUS_CODE STATUS_CODE, NULL SWAP_REF, nvl(RT.TRANSACTION_NUMBER, 1) TRANSACTION_NO, RT.INTEREST_RATE TRANSACTION_RATE, RT.YEAR_BASIS YEAR_BASIS, D.YEAR_CALC_TYPE YEAR_CALC_TYPE from XTR_ROLLOVER_TRANSACTIONS RT, XTR_COMPANY_PARAMETERS CP, XTR_DEALS D WHERE RT.DEAL_TYPE = 'NI' and RT.DEAL_SUBTYPE not in ('SELL','COVER') and D.STATUS_CODE <> 'CANCELLED' and D.PRICING_MODEL <> 'NO_REVAL' and RT.COMPANY_CODE = D.COMPANY_CODE and RT.DEAL_NUMBER = D.DEAL_NO and RT.LAST_REVAL_BATCH_ID is null and RT.COMPANY_CODE = CP.COMPANY_CODE and CP.PARAMETER_CODE = 'ACCNT_TSDTM'

View Text - HTML Formatted

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