VIEW: APPS.XTR_CA_ELIGIBLE_DEALS_V

Source

View Text - Preformatted

SELECT BA.ACCOUNT_NUMBER ACCOUNT_NO, to_number(null) BROKERAGE_AMOUNT, NULL CAP_OR_FLOOR, BB.COMPANY_CODE COMPANY_CODE, NULL CONTRACT_CODE, BA.CURRENCY CURRENCYA, null CURRENCYB, to_date(NULL) DEAL_DATE, 0 DEAL_NO, decode(sign(BB.INTEREST_SETTLED),-1,'FUND','INVEST') DEAL_SUBTYPE, 'CA' DEAL_TYPE, NULL DISCOUNT_YIELD, BB.BALANCE_DATE EFFECTIVE_DATE, BB.BALANCE_DATE ELIGIBLE_DATE, to_date(NULL) EXPIRY_DATE, BB.BALANCE_CFLOW FACE_VALUE, 0 FX_REVAL_PRINCIPAL_BAL, 0 FXO_SELL_REF_AMOUNT, NULL MARKET_DATA_SET, to_date(NULL) MATURITY_DATE, BA.PORTFOLIO_CODE PORTFOLIO_CODE, NULL PREMIUM_ACTION, to_number(NULL) PREMIUM_AMOUNT, NULL PREMIUM_CCY, NULL PRICING_MODEL, NULL PRODUCT_TYPE, NULL SETTLE_ACTION, to_number(NULL) SETTLE_AMOUNT, to_date(NULL) SETTLE_DATE, to_date(NULL) START_DATE, NULL STATUS_CODE, NULL SWAP_REF, 1 TRANSACTION_NO, BB.INTEREST_RATE TRANSACTION_RATE, to_number(NULL) YEAR_BASIS, 'ACTUAL/ACTUAL' YEAR_CALC_TYPE FROM XTR_BANK_BALANCES BB, XTR_BANK_ACCOUNTS BA, GL_SETS_OF_BOOKS GL, XTR_PARTY_INFO PI WHERE BB.LAST_BATCH_ID is null and BB.ACCOUNT_NUMBER = BA.ACCOUNT_NUMBER and BB.COMPANY_CODE = BA.PARTY_CODE and BB.COMPANY_CODE = PI.PARTY_CODE and PI.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID and GL.CURRENCY_CODE <> BA.CURRENCY and nvl(BA.SETOFF_ACCOUNT_YN, 'N') = 'N' and BA.PRICING_MODEL <> 'NO_REVAL'

View Text - HTML Formatted

SELECT BA.ACCOUNT_NUMBER ACCOUNT_NO
, TO_NUMBER(NULL) BROKERAGE_AMOUNT
, NULL CAP_OR_FLOOR
, BB.COMPANY_CODE COMPANY_CODE
, NULL CONTRACT_CODE
, BA.CURRENCY CURRENCYA
, NULL CURRENCYB
, TO_DATE(NULL) DEAL_DATE
, 0 DEAL_NO
, DECODE(SIGN(BB.INTEREST_SETTLED)
, -1
, 'FUND'
, 'INVEST') DEAL_SUBTYPE
, 'CA' DEAL_TYPE
, NULL DISCOUNT_YIELD
, BB.BALANCE_DATE EFFECTIVE_DATE
, BB.BALANCE_DATE ELIGIBLE_DATE
, TO_DATE(NULL) EXPIRY_DATE
, BB.BALANCE_CFLOW FACE_VALUE
, 0 FX_REVAL_PRINCIPAL_BAL
, 0 FXO_SELL_REF_AMOUNT
, NULL MARKET_DATA_SET
, TO_DATE(NULL) MATURITY_DATE
, BA.PORTFOLIO_CODE PORTFOLIO_CODE
, NULL PREMIUM_ACTION
, TO_NUMBER(NULL) PREMIUM_AMOUNT
, NULL PREMIUM_CCY
, NULL PRICING_MODEL
, NULL PRODUCT_TYPE
, NULL SETTLE_ACTION
, TO_NUMBER(NULL) SETTLE_AMOUNT
, TO_DATE(NULL) SETTLE_DATE
, TO_DATE(NULL) START_DATE
, NULL STATUS_CODE
, NULL SWAP_REF
, 1 TRANSACTION_NO
, BB.INTEREST_RATE TRANSACTION_RATE
, TO_NUMBER(NULL) YEAR_BASIS
, 'ACTUAL/ACTUAL' YEAR_CALC_TYPE
FROM XTR_BANK_BALANCES BB
, XTR_BANK_ACCOUNTS BA
, GL_SETS_OF_BOOKS GL
, XTR_PARTY_INFO PI
WHERE BB.LAST_BATCH_ID IS NULL
AND BB.ACCOUNT_NUMBER = BA.ACCOUNT_NUMBER
AND BB.COMPANY_CODE = BA.PARTY_CODE
AND BB.COMPANY_CODE = PI.PARTY_CODE
AND PI.SET_OF_BOOKS_ID = GL.SET_OF_BOOKS_ID
AND GL.CURRENCY_CODE <> BA.CURRENCY
AND NVL(BA.SETOFF_ACCOUNT_YN
, 'N') = 'N'
AND BA.PRICING_MODEL <> 'NO_REVAL'