View: XTR_BIS_CASHFLOWS_V

Product:	XTR - Treasury
Description:	This view shows the cashflows generated by deals with CURRENT status. You can use this view to see the net cashflow and cash book summaries.
Implementation/DBA Data:	APPS.XTR_BIS_CASHFLOWS_V

View Text

SELECT TO_CHAR(CA.DEAL_NUMBER)
, CA.DDA_DEAL_TYPE
, CA.DDA_DEAL_SUBTYPE
, CA.TRANSACTION_RATE
, CA.STATUS_CODE
, CA.CURRENCY_CODE
, CA.TRX_DATE
, CA.AMOUNT
, CA.BANK_CODE
, NULL
, CA.COMPANY_CODE
, PA.SHORT_NAME
, CA.COMPANY_ACCOUNT
, CA.PRODUCT_TYPE
, CA.PORTFOLIO_CODE
, CA.CPARTY_CODE
, CA.CLIENT_CODE
, CA.DEAL_TYPE
, CA.CATEGORY
, CA.DEAL_SUBTYPE
, CA.TYPE_OF_AMOUNT
, CA.AMOUNT_TYPE
, CA.TRANSACTION_NUMBER
, CA.DEALER
, CA.SETTLE
, CA.MULTIPLE_SETTLEMENTS
FROM XTR_CASHFLOWS_V CA
, XTR_PARTIES_V PA
WHERE CA.COMPANY_CODE = PA.PARTY_CODE
AND CA.TRX_DATE >= TRUNC(SYSDATE) UNION ALL SELECT TO_CHAR(DDA.DEAL_NUMBER)
, DDA.DEAL_TYPE
, DDA.DEAL_SUBTYPE
, DDA.TRANSACTION_RATE
, DDA.STATUS_CODE
, DDA.CURRENCY
, DDA.AMOUNT_DATE
, DECODE(DDA.DEAL_SUBTYPE
, 'INVEST'
, DDA.AMOUNT
, -1*(DDA.AMOUNT))
, BA.BANK_CODE
, NULL
, DDA.COMPANY_CODE
, PA.SHORT_NAME
, DDA.ACCOUNT_NO
, 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.MULTIPLE_SETTLEMENTS
FROM XTR_DEAL_DATE_AMOUNTS DDA
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_AMOUNT_TYPES AT
, XTR_PARTIES_V PA
, XTR_BANK_ACCOUNTS BA
WHERE DDA.DEAL_TYPE = 'CA'
AND DDA.COMPANY_CODE = PA.PARTY_CODE
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 UNION ALL SELECT HEADER.STATEMENT_NUMBER
, 'INTRA'
, DECODE(INTRA.TRX_TYPE
, 'CREDIT'
, 'RECEIPT'
, 'DEBIT'
, 'PAYMENT'
, 'MISC_CREDIT'
, 'RECEIPT'
, 'MISC_DEBIT'
, 'PAYMENT'
, 'NSF'
, 'PAYMENT'
, 'REJECTED'
, 'PAYMENT'
, 'STOP'
, 'RECEIPT'
, 'STATEMENT'
, 'RECEIPT')
, 1
, 'CURRENT'
, ABA.CURRENCY_CODE
, NVL(INTRA.EFFECTIVE_DATE
, INTRA.TRX_DATE)
, DECODE(INTRA.TRX_TYPE
, 'CREDIT'
, INTRA.AMOUNT
, 'DEBIT'
, -INTRA.AMOUNT
, 'MISC_CREDIT'
, INTRA.AMOUNT
, 'MISC_DEBIT'
, -INTRA.AMOUNT
, 'NSF'
, -INTRA.AMOUNT
, 'REJECTED'
, -INTRA.AMOUNT
, 'STOP'
, INTRA.AMOUNT
, INTRA.AMOUNT)
, BA.BANK_CODE
, INTRA.BANK_TRX_NUMBER
, PTY.PARTY_CODE
, PTY.SHORT_NAME
, BA.ACCOUNT_NUMBER
, NULL
, NULL
, NULL
, NULL
, FND_A.MEANING
, 'XX'
, FND_B.MEANING
, INTRA.TRX_TYPE
, CE.MEANING
, INTRA.LINE_NUMBER
, NULL
, 'N'
, 'N'
FROM CE_INTRA_STMT_LINES INTRA
, CE_INTRA_STMT_HEADERS HEADER
, CE_BANK_ACCOUNTS ABA
, XTR_BANK_ACCOUNTS BA
, XTR_PARTY_INFO PTY
, FND_LOOKUPS FND_A
, FND_LOOKUPS FND_B
, CE_LOOKUPS CE
, HR_OPERATING_UNITS HOU
WHERE (FND_A.LOOKUP_TYPE = 'XTR_INTRA_DAY_DEAL_TYPE'
AND FND_A.LOOKUP_CODE = 'INTRA')
AND INTRA.STATEMENT_HEADER_ID = HEADER.STATEMENT_HEADER_ID
AND HEADER.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.SET_OF_BOOKS_ID = (SELECT GLLE.LEDGER_ID
FROM GL_LEDGER_LE_V GLLE
WHERE GLLE.LEGAL_ENTITY_ID = (PTY.LEGAL_ENTITY_ID)
AND GLLE.LEDGER_CATEGORY_CODE = 'PRIMARY')
AND ABA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
AND ABA.BANK_ACCOUNT_ID = HEADER.BANK_ACCOUNT_ID
AND PTY.PARTY_CODE = BA.PARTY_CODE
AND HEADER.BANK_ACCOUNT_ID = BA.CE_BANK_ACCOUNT_ID
AND ABA.CURRENCY_CODE = BA.CURRENCY
AND TRUNC(HEADER.STATEMENT_DATE) = TRUNC(SYSDATE)
AND (FND_B.LOOKUP_TYPE = 'XTR_INTRA_DAY_DEAL_SUBTYPE'
AND FND_B.LOOKUP_CODE = DECODE(INTRA.TRX_TYPE
, 'CREDIT'
, 'RECEIPT'
, 'DEBIT'
, 'PAYMENT'
, 'MISC_CREDIT'
, 'RECEIPT'
, 'MISC_DEBIT'
, 'PAYMENT'
, 'NSF'
, 'PAYMENT'
, 'REJECTED'
, 'PAYMENT'
, 'STOP'
, 'RECEIPT'))
AND (CE.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND CE.LOOKUP_CODE = INTRA.TRX_TYPE) UNION ALL SELECT HEADER.STATEMENT_NUMBER
, 'INTRA'
, DECODE(INTRA.TRX_TYPE
, 'CREDIT'
, 'RECEIPT'
, 'DEBIT'
, 'PAYMENT'
, 'MISC_CREDIT'
, 'RECEIPT'
, 'MISC_DEBIT'
, 'PAYMENT'
, 'NSF'
, 'PAYMENT'
, 'REJECTED'
, 'PAYMENT'
, 'STOP'
, 'RECEIPT'
, 'STATEMENT'
, 'RECEIPT')
, 1
, 'CURRENT'
, ABA.CURRENCY_CODE
, NVL(INTRA.EFFECTIVE_DATE
, INTRA.TRX_DATE)
, DECODE(INTRA.TRX_TYPE
, 'CREDIT'
, INTRA.AMOUNT
, 'DEBIT'
, -INTRA.AMOUNT
, 'MISC_CREDIT'
, INTRA.AMOUNT
, 'MISC_DEBIT'
, -INTRA.AMOUNT
, 'NSF'
, -INTRA.AMOUNT
, 'REJECTED'
, -INTRA.AMOUNT
, 'STOP'
, INTRA.AMOUNT
, INTRA.AMOUNT)
, NULL
, INTRA.BANK_TRX_NUMBER
, PTY.PARTY_CODE
, PTY.SHORT_NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, FND_A.MEANING
, 'XX'
, FND_B.MEANING
, INTRA.TRX_TYPE
, CE.MEANING
, INTRA.LINE_NUMBER
, NULL
, 'N'
, 'N'
FROM CE_INTRA_STMT_LINES INTRA
, CE_INTRA_STMT_HEADERS HEADER
, CE_BANK_ACCOUNTS ABA
, XTR_PARTY_INFO PTY
, FND_LOOKUPS FND_A
, FND_LOOKUPS FND_B
, CE_LOOKUPS CE
, HR_OPERATING_UNITS HOU
WHERE (FND_A.LOOKUP_TYPE = 'XTR_INTRA_DAY_DEAL_TYPE'
AND FND_A.LOOKUP_CODE = 'INTRA')
AND INTRA.STATEMENT_HEADER_ID = HEADER.STATEMENT_HEADER_ID
AND HEADER.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.SET_OF_BOOKS_ID = (SELECT GLLE.LEDGER_ID
FROM GL_LEDGER_LE_V GLLE
WHERE GLLE.LEGAL_ENTITY_ID = (PTY.LEGAL_ENTITY_ID)
AND GLLE.LEDGER_CATEGORY_CODE = 'PRIMARY')
AND ABA.ACCOUNT_CLASSIFICATION = 'INTERNAL'
AND ABA.BANK_ACCOUNT_ID = HEADER.BANK_ACCOUNT_ID
AND TRUNC(HEADER.STATEMENT_DATE) = TRUNC(SYSDATE)
AND (FND_B.LOOKUP_TYPE = 'XTR_INTRA_DAY_DEAL_SUBTYPE'
AND FND_B.LOOKUP_CODE = DECODE(INTRA.TRX_TYPE
, 'CREDIT'
, 'RECEIPT'
, 'DEBIT'
, 'PAYMENT'
, 'MISC_CREDIT'
, 'RECEIPT'
, 'MISC_DEBIT'
, 'PAYMENT'
, 'NSF'
, 'PAYMENT'
, 'REJECTED'
, 'PAYMENT'
, 'STOP'
, 'RECEIPT'))
AND (CE.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND CE.LOOKUP_CODE = INTRA.TRX_TYPE)
AND NOT EXISTS (SELECT 1
FROM XTR_BANK_ACCOUNTS BA2
WHERE BA2.CE_BANK_ACCOUNT_ID = HEADER.BANK_ACCOUNT_ID)

Columns