VIEW: APPS.XTR_DISC_CASHFLOWS_V

Source

View Text - Preformatted

SELECT dda.DEAL_NUMBER, dda.DEAL_TYPE, dda.DEAL_SUBTYPE, dda.TRANSACTION_RATE, dda.STATUS_CODE, dda.CURRENCY, nvl(dda.ACTUAL_SETTLEMENT_DATE, dda.AMOUNT_DATE), dda.CASHFLOW_AMOUNT, dda.COMPANY_CODE, dda.ACCOUNT_NO, ba.BANK_CODE, dda.PRODUCT_TYPE, dda.PORTFOLIO_CODE, dda.CPARTY_CODE, dda.CLIENT_CODE, dt.USER_DEAL_TYPE, dt.FX_MM_EXP_FLAG, st.USER_DEAL_SUBTYPE, dda.AMOUNT_TYPE, at.USER_AMOUNT_TYPE, dda.TRANSACTION_NUMBER, dda.DEALER_CODE, dda.SETTLE, dda.DEAL_TYPE, dda.MULTIPLE_SETTLEMENTS , DT.NAME DEAL_TYPE_NAME, ST.NAME DEAL_SUBTYPE_NAME, A.FULL_NAME COMPANY_FULL_NAME, B.FULL_NAME CPARTY_FULL_NAME, BA.BANK_NAME BANK_NAME, AT.AMOUNT_NAME AMOUNT_TYPE_NAME from XTR_DEAL_DATE_AMOUNTS dda, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st, XTR_AMOUNT_TYPES at, XTR_BANK_ACCOUNTS ba, XTR_COMPANY_AUTHORITIES xca, XTR_DEALER_CODES xdc, XTR_PARTY_INFO A, XTR_PARTY_INFO B WHERE dda.COMPANY_CODE = A.PARTY_CODE AND dda.CPARTY_CODE = B.PARTY_CODE(+) AND dda.STATUS_CODE <> 'CANCELLED' and dda.CASHFLOW_AMOUNT <> 0 and dda.DEAL_TYPE <> 'CA' and nvl(dda.MULTIPLE_SETTLEMENTS,'N') = 'N' and dt.DEAL_TYPE = dda.DEAL_TYPE and (st.DEAL_TYPE = dda.DEAL_TYPE and st.DEAL_SUBTYPE = dda.DEAL_SUBTYPE) and (at.DEAL_TYPE(+) = dda.DEAL_TYPE and at.AMOUNT_TYPE(+) = dda.AMOUNT_TYPE) and ba.ACCOUNT_NUMBER (+) = dda.ACCOUNT_NO and ba.PARTY_CODE (+) = dda.COMPANY_CODE and ba.CURRENCY (+) = dda.CURRENCY AND dda.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 union all select 0, 'EXT', 'INDIC', 1, 'CURRENT', ex.CURRENCY, ex.AMOUNT_DATE, decode(ex.TRX_TYPE, 'ARI', ex.AMOUNT, 'ARR', ex.AMOUNT, 'OEO', ex.AMOUNT, 'OII', ex.AMOUNT, 'UDI', ex.AMOUNT, 'API', -(ex.AMOUNT), 'APP', -(ex.AMOUNT), 'PAY', -(ex.AMOUNT), 'POP', -(ex.AMOUNT), 'POR', -(ex.AMOUNT), 'OIO', -(ex.AMOUNT), 'UDO', -(ex.AMOUNT), ex.AMOUNT), ex.COMPANY_CODE, ba.ACCOUNT_NUMBER, ba.BANK_CODE, null, null, null, null, dt.USER_DEAL_TYPE, 'XX', st.USER_DEAL_SUBTYPE, ex.TRX_TYPE, at.USER_AMOUNT_TYPE, 0, null, 'N', 'EXT', 'N' , DT.NAME DEAL_TYPE_NAME, ST.NAME DEAL_SUBTYPE_NAME, A.FULL_NAME COMPANY_FULL_NAME, null CPARTY_FULL_NAME, BA.BANK_NAME BANK_NAME, AT.AMOUNT_NAME AMOUNT_TYPE_NAME from XTR_EXTERNAL_CASHFLOWS ex, XTR_BANK_ACCOUNTS ba, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st, XTR_AMOUNT_TYPES at, XTR_COMPANY_AUTHORITIES xca, XTR_DEALER_CODES xdc, XTR_PARTY_INFO A WHERE ex.COMPANY_CODE = A.PARTY_CODE AND dt.DEAL_TYPE = 'EXT' and ex.AP_BANK_ACCOUNT_ID = ba.AP_BANK_ACCOUNT_ID (+) and ex.CURRENCY = ba.CURRENCY (+) and ex.COMPANY_CODE = ba.PARTY_CODE (+) and (st.deal_type = 'EXT' and st.deal_subtype = 'INDIC') and (at.deal_type = 'EXT' and at.amount_type = ex.TRX_TYPE) AND ex.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

View Text - HTML Formatted

SELECT DDA.DEAL_NUMBER
, DDA.DEAL_TYPE
, DDA.DEAL_SUBTYPE
, DDA.TRANSACTION_RATE
, DDA.STATUS_CODE
, DDA.CURRENCY
, NVL(DDA.ACTUAL_SETTLEMENT_DATE
, DDA.AMOUNT_DATE)
, DDA.CASHFLOW_AMOUNT
, DDA.COMPANY_CODE
, DDA.ACCOUNT_NO
, BA.BANK_CODE
, DDA.PRODUCT_TYPE
, DDA.PORTFOLIO_CODE
, DDA.CPARTY_CODE
, DDA.CLIENT_CODE
, DT.USER_DEAL_TYPE
, DT.FX_MM_EXP_FLAG
, ST.USER_DEAL_SUBTYPE
, DDA.AMOUNT_TYPE
, AT.USER_AMOUNT_TYPE
, DDA.TRANSACTION_NUMBER
, DDA.DEALER_CODE
, DDA.SETTLE
, DDA.DEAL_TYPE
, DDA.MULTIPLE_SETTLEMENTS
, DT.NAME DEAL_TYPE_NAME
, ST.NAME DEAL_SUBTYPE_NAME
, A.FULL_NAME COMPANY_FULL_NAME
, B.FULL_NAME CPARTY_FULL_NAME
, BA.BANK_NAME BANK_NAME
, AT.AMOUNT_NAME AMOUNT_TYPE_NAME
FROM XTR_DEAL_DATE_AMOUNTS DDA
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_AMOUNT_TYPES AT
, XTR_BANK_ACCOUNTS BA
, XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
, XTR_PARTY_INFO A
, XTR_PARTY_INFO B
WHERE DDA.COMPANY_CODE = A.PARTY_CODE
AND DDA.CPARTY_CODE = B.PARTY_CODE(+)
AND DDA.STATUS_CODE <> 'CANCELLED'
AND DDA.CASHFLOW_AMOUNT <> 0
AND DDA.DEAL_TYPE <> 'CA'
AND NVL(DDA.MULTIPLE_SETTLEMENTS
, 'N') = 'N'
AND DT.DEAL_TYPE = DDA.DEAL_TYPE
AND (ST.DEAL_TYPE = DDA.DEAL_TYPE
AND ST.DEAL_SUBTYPE = DDA.DEAL_SUBTYPE)
AND (AT.DEAL_TYPE(+) = DDA.DEAL_TYPE
AND AT.AMOUNT_TYPE(+) = DDA.AMOUNT_TYPE)
AND BA.ACCOUNT_NUMBER (+) = DDA.ACCOUNT_NO
AND BA.PARTY_CODE (+) = DDA.COMPANY_CODE
AND BA.CURRENCY (+) = DDA.CURRENCY
AND DDA.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 UNION ALL SELECT 0
, 'EXT'
, 'INDIC'
, 1
, 'CURRENT'
, EX.CURRENCY
, EX.AMOUNT_DATE
, DECODE(EX.TRX_TYPE
, 'ARI'
, EX.AMOUNT
, 'ARR'
, EX.AMOUNT
, 'OEO'
, EX.AMOUNT
, 'OII'
, EX.AMOUNT
, 'UDI'
, EX.AMOUNT
, 'API'
, -(EX.AMOUNT)
, 'APP'
, -(EX.AMOUNT)
, 'PAY'
, -(EX.AMOUNT)
, 'POP'
, -(EX.AMOUNT)
, 'POR'
, -(EX.AMOUNT)
, 'OIO'
, -(EX.AMOUNT)
, 'UDO'
, -(EX.AMOUNT)
, EX.AMOUNT)
, EX.COMPANY_CODE
, BA.ACCOUNT_NUMBER
, BA.BANK_CODE
, NULL
, NULL
, NULL
, NULL
, DT.USER_DEAL_TYPE
, 'XX'
, ST.USER_DEAL_SUBTYPE
, EX.TRX_TYPE
, AT.USER_AMOUNT_TYPE
, 0
, NULL
, 'N'
, 'EXT'
, 'N'
, DT.NAME DEAL_TYPE_NAME
, ST.NAME DEAL_SUBTYPE_NAME
, A.FULL_NAME COMPANY_FULL_NAME
, NULL CPARTY_FULL_NAME
, BA.BANK_NAME BANK_NAME
, AT.AMOUNT_NAME AMOUNT_TYPE_NAME
FROM XTR_EXTERNAL_CASHFLOWS EX
, XTR_BANK_ACCOUNTS BA
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_AMOUNT_TYPES AT
, XTR_COMPANY_AUTHORITIES XCA
, XTR_DEALER_CODES XDC
, XTR_PARTY_INFO A
WHERE EX.COMPANY_CODE = A.PARTY_CODE
AND DT.DEAL_TYPE = 'EXT'
AND EX.AP_BANK_ACCOUNT_ID = BA.AP_BANK_ACCOUNT_ID (+)
AND EX.CURRENCY = BA.CURRENCY (+)
AND EX.COMPANY_CODE = BA.PARTY_CODE (+)
AND (ST.DEAL_TYPE = 'EXT'
AND ST.DEAL_SUBTYPE = 'INDIC')
AND (AT.DEAL_TYPE = 'EXT'
AND AT.AMOUNT_TYPE = EX.TRX_TYPE)
AND EX.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