View: CE_999_TRANSACTIONS_V

Product:	CE - Cash Management
Description:	Available transactions for reconciliation via Open Interface
Implementation/DBA Data:	APPS.CE_999_TRANSACTIONS_V

View Text

SELECT T.ROW_ID
, /* 1 */ 'N'
, /* 2 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ ABA.BANK_ACCOUNT_NAME
, /* 4 */ ABA.BANK_ACCOUNT_NUM
, /* 5 */ ABB.BANK_NAME
, /* 6 */ ABB.BANK_BRANCH_NAME
, /* 7 */ T.TRX_ID
, /* 8 */ T.TRX_TYPE
, /* 9 */ T.TRX_TYPE_DSP
, /* 10 */ T.TRX_NUMBER
, /* 11 */ T.CURRENCY_CODE
, /* 12 */ T.AMOUNT
, /* 13 */ DECODE(T.CURRENCY_CODE
, /* 14 */ SOB.CURRENCY_CODE
, T.AMOUNT
, ABA.CURRENCY_CODE
, T.AMOUNT
, T.ACCTD_AMOUNT)
, T.CLEARED_AMOUNT
, /* 15 */ T.GL_DATE
, /* 16 */ T.STATUS_DSP
, /* 17 */ T.STATUS
, /* 18 */ TRUNC(T.TRX_DATE)
, /* 19 */ T.CLEARED_DATE
, /* 20 */ TO_DATE(NULL)
, /* 21 */ T.EXCHANGE_RATE_DATE
, /* 22 */ T.EXCHANGE_RATE_TYPE
, /* 23 */ GLC.USER_CONVERSION_TYPE
, /* 24 */ T.EXCHANGE_RATE
, /* 25 */ DECODE(T.CURRENCY_CODE
, /* 26 */ SOB.CURRENCY_CODE
, T.CHARGES_AMOUNT
, ABA.CURRENCY_CODE
, T.CHARGES_AMOUNT
, T.ACCTD_CHARGES_AMOUNT)
, DECODE(T.CURRENCY_CODE
, /* 27 */ SOB.CURRENCY_CODE
, T.ERROR_AMOUNT
, ABA.CURRENCY_CODE
, T.ERROR_AMOUNT
, T.ACCTD_ERROR_AMOUNT)
, NULL
, /* 28 */ TO_NUMBER(NULL)
, /* 29 */ NULL
, /* 30 */ NULL
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33 */ TO_NUMBER(NULL)
, /* 34 */ TO_NUMBER(NULL)
, /* 35 */ NULL
, /* 36 */ NULL
, /* 37 */ TO_NUMBER(NULL)
, /* 38 */ DECODE(CRE.STATUS_FLAG
, /* 39 */ 'M'
, DECODE(T.TRX_TYPE
, 'CASH'
, DECODE(T.CURRENCY_CODE
, SOB.CURRENCY_CODE
, T.AMOUNT + NVL(T.CHARGES_AMOUNT
, 0) + NVL(T.ERROR_AMOUNT
, 0)
, ABA.CURRENCY_CODE
, T.AMOUNT + NVL(T.CHARGES_AMOUNT
, 0) + NVL(T.ERROR_AMOUNT
, 0)
, T.ACCTD_AMOUNT + NVL(T.ACCTD_CHARGES_AMOUNT
, 0) + NVL(T.ACCTD_ERROR_AMOUNT
, 0))
, 'PAYMENT'
, DECODE(T.CURRENCY_CODE
, SOB.CURRENCY_CODE
, T.AMOUNT - NVL(T.CHARGES_AMOUNT
, 0) - NVL(T.ERROR_AMOUNT
, 0)
, ABA.CURRENCY_CODE
, T.AMOUNT - NVL(T.CHARGES_AMOUNT
, 0) - NVL(T.ERROR_AMOUNT
, 0)
, T.ACCTD_AMOUNT - NVL(T.ACCTD_CHARGES_AMOUNT
, 0) - NVL(T.ACCTD_ERROR_AMOUNT
, 0))))
, T.CREATION_DATE
, /* 40 */ T.CREATED_BY
, /* 41 */ T.LAST_UPDATE_DATE
, /* 42 */ T.LAST_UPDATED_BY
, /* 43 */ NULL
, /* 44 */ TO_NUMBER(NULL)
, /* 45 */ 999
, /* 46 */ 0
, /* 47 */ ABA.CURRENCY_CODE
, /* 48 */ DECODE(T.CURRENCY_CODE
, /* 49 */ SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, ABA.ASSET_CODE_COMBINATION_ID
, /* 50 */ NULL
, /* 51 */ NULL
, /* 52 */ TO_NUMBER(NULL)
, /* 53 */ TO_NUMBER(NULL)
, /* 54 */ 'ROI_LINE'
, /* 55 */ NULL
, /* 56 */ T.ACCTD_AMOUNT
, /* 57 */ NULL
, /* 58 */ TO_NUMBER(NULL)
, /* 59 */ TO_DATE(NULL)
, /* 60 */ TO_NUMBER(NULL)
, /* 61 */ TO_NUMBER(NULL)
, /* 62 */ NULL
, /* 63 */ TO_DATE(NULL)
, /* 64 */ TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM GL_DAILY_CONVERSION_TYPES GLC
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_BANK_ACCTS_GT_V ABA
, /* CE_BANK_ACCT_USES_ALL BAU
, CE_GL_ACCOUNTS_CCID GAC
, */ CE_BANK_BRANCHES_V ABB
, CE_999_INTERFACE_V T
, CE_STATEMENT_RECONCILS_ALL CRE
WHERE GLC.CONVERSION_TYPE(+) = T.EXCHANGE_RATE_TYPE
AND SOB.SET_OF_BOOKS_ID (+) = SYS.SET_OF_BOOKS_ID AND ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID
AND ABA.BANK_ACCOUNT_ID = T.BANK_ACCOUNT_ID /*AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID (+)
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID (+)
AND ABA.ACCOUNT_OWNER_ORG_ID = BAU.LEGAL_ENTITY_ID (+)*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND CRE.REFERENCE_ID(+) = T.TRX_ID
AND CRE.REFERENCE_TYPE(+) = 'ROI_LINE'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND T.STATUS = DECODE(SYS.SHOW_CLEARED_FLAG
, 'Y'
, T.STATUS
, ABA.RECON_OI_FLOAT_STATUS)
AND T.TRX_DATE >= SYS.CASHBOOK_BEGIN_DATE UNION ALL SELECT ROW_ID
, MULTI_SELECT
, BANK_ACCOUNT_ID
, BANK_ACCOUNT_NAME
, BANK_ACCOUNT_NUM
, BANK_NAME
, BANK_BRANCH_NAME
, TRX_ID
, TRX_TYPE
, TYPE_MEANING
, TRX_NUMBER
, CURRENCY_CODE
, AMOUNT
, BANK_ACCOUNT_AMOUNT
, AMOUNT_CLEARED
, GL_DATE
, STATUS_DSP
, STATUS
, TRX_DATE
, CLEARED_DATE
, MATURITY_DATE
, EXCHANGE_RATE_DATE
, EXCHANGE_RATE_TYPE
, USER_EXCHANGE_RATE_TYPE
, EXCHANGE_RATE
, BANK_CHARGES
, BANK_ERRORS
, BATCH_NAME
, BATCH_ID
, AGENT_NAME
, CUSTOMER_NAME
, PAYMENT_METHOD
, VENDOR_NAME
, CUSTOMER_ID
, SUPPLIER_ID
, REFERENCE_TYPE_DSP
, REFERENCE_TYPE
, REFERENCE_ID
, ACTUAL_AMOUNT_CLEARED
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, REMITTANCE_NUMBER
, CASH_RECEIPT_ID
, APPLICATION_ID
, COUNT_CLEARED
, BANK_CURRENCY_CODE
, TRX_CURRENCY_TYPE
, CODE_COMBINATION_ID
, PERIOD_NAME
, JOURNAL_ENTRY_NAME
, DOCUMENT_NUMBER
, JOURNAL_ENTRY_LINE_NUMBER
, CLEARING_TRX_TYPE
, JOURNAL_CATEGORY
, BASE_AMOUNT
, REVERSED_RECEIPT_FLAG
, CHECK_NUMBER
, VALUE_DATE
, RECEIPT_CLASS_ID
, RECEIPT_METHOD_ID
, RECEIPT_CLASS_NAME
, DEPOSIT_DATE
, CE_BANK_ACCT_USE_ID
, LEGAL_ENTITY_ID
, ORG_ID
FROM CE_185_TRANSACTIONS_V

Columns