VIEW: APPS.XTR_ONC_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, RT.DEAL_DATE DEAL_DATE, RT.DEAL_NUMBER DEAL_NO, RT.DEAL_SUBTYPE DEAL_SUBTYPE, RT.DEAL_TYPE DEAL_TYPE, 'N' DISCOUNT_YIELD, nvl(RT.MATURITY_DATE, RT.START_DATE) EFFECTIVE_DATE, decode(CP.PARAMETER_VALUE_CODE,'TRADE', RT.DEAL_DATE, RT.START_DATE) ELIGIBLE_DATE, to_date(NULL) EXPIRY_DATE, RT.PRINCIPAL_ADJUST FACE_VALUE, 0 FX_REVAL_PRINCIPAL_BAL, 0 FXO_SELL_REF_AMOUNT, NULL MARKET_DATA_SET, RT.MATURITY_DATE MATURITY_DATE, RT.PORTFOLIO_CODE PORTFOLIO_CODE, NULL PREMIUM_ACTION, to_number(NULL) PREMIUM_AMOUNT, NULL PREMIUM_CCY, NULL PRICING_MODEL, RT.PRODUCT_TYPE PRODUCT_TYPE, NULL SETTLE_ACTION, to_number(NULL) SETTLE_AMOUNT, to_date(NULL) SETTLE_DATE, RT.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, RT.YEAR_CALC_TYPE YEAR_CALC_TYPE from XTR_ROLLOVER_TRANSACTIONS RT, XTR_PARTY_INFO PI, XTR_COMPANY_PARAMETERS CP, GL_SETS_OF_BOOKS GL, XTR_DEALS D, GL_LEDGER_LE_V GLLE WHERE RT.DEAL_TYPE = 'ONC' and RT.STATUS_CODE <> 'CANCELLED' and (RT.PRINCIPAL_ADJUST <> 0 or RT.REPAY_AMOUNT <> 0) and RT.LAST_REVAL_BATCH_ID is null and RT.COMPANY_CODE = PI.PARTY_CODE and RT.COMPANY_CODE = CP.COMPANY_CODE and CP.PARAMETER_CODE = 'ACCNT_TSDTM' and PI.LEGAL_ENTITY_ID = GLLE.LEGAL_ENTITY_ID and GLLE.LEDGER_ID = GL.SET_OF_BOOKS_ID and GL.CURRENCY_CODE <> RT.CURRENCY and D.DEAL_NO = RT.DEAL_NUMBER and D.PRICING_MODEL <> 'NO_REVAL' and RT.BALANCE_OUT <> 0

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
, RT.DEAL_DATE DEAL_DATE
, RT.DEAL_NUMBER DEAL_NO
, RT.DEAL_SUBTYPE DEAL_SUBTYPE
, RT.DEAL_TYPE DEAL_TYPE
, 'N' DISCOUNT_YIELD
, NVL(RT.MATURITY_DATE
, RT.START_DATE) EFFECTIVE_DATE
, DECODE(CP.PARAMETER_VALUE_CODE
, 'TRADE'
, RT.DEAL_DATE
, RT.START_DATE) ELIGIBLE_DATE
, TO_DATE(NULL) EXPIRY_DATE
, RT.PRINCIPAL_ADJUST FACE_VALUE
, 0 FX_REVAL_PRINCIPAL_BAL
, 0 FXO_SELL_REF_AMOUNT
, NULL MARKET_DATA_SET
, RT.MATURITY_DATE MATURITY_DATE
, RT.PORTFOLIO_CODE PORTFOLIO_CODE
, NULL PREMIUM_ACTION
, TO_NUMBER(NULL) PREMIUM_AMOUNT
, NULL PREMIUM_CCY
, NULL PRICING_MODEL
, RT.PRODUCT_TYPE PRODUCT_TYPE
, NULL SETTLE_ACTION
, TO_NUMBER(NULL) SETTLE_AMOUNT
, TO_DATE(NULL) SETTLE_DATE
, RT.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
, RT.YEAR_CALC_TYPE YEAR_CALC_TYPE
FROM XTR_ROLLOVER_TRANSACTIONS RT
, XTR_PARTY_INFO PI
, XTR_COMPANY_PARAMETERS CP
, GL_SETS_OF_BOOKS GL
, XTR_DEALS D
, GL_LEDGER_LE_V GLLE
WHERE RT.DEAL_TYPE = 'ONC'
AND RT.STATUS_CODE <> 'CANCELLED'
AND (RT.PRINCIPAL_ADJUST <> 0 OR RT.REPAY_AMOUNT <> 0)
AND RT.LAST_REVAL_BATCH_ID IS NULL
AND RT.COMPANY_CODE = PI.PARTY_CODE
AND RT.COMPANY_CODE = CP.COMPANY_CODE
AND CP.PARAMETER_CODE = 'ACCNT_TSDTM'
AND PI.LEGAL_ENTITY_ID = GLLE.LEGAL_ENTITY_ID
AND GLLE.LEDGER_ID = GL.SET_OF_BOOKS_ID
AND GL.CURRENCY_CODE <> RT.CURRENCY
AND D.DEAL_NO = RT.DEAL_NUMBER
AND D.PRICING_MODEL <> 'NO_REVAL'
AND RT.BALANCE_OUT <> 0