VIEW: APPS.CE_999_TRANSACTIONS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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