VIEW: APPS.XTR_BIS_CASHFLOWS_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)