VIEW: APPS.CE_999_RECONCILED_V

Source

View Text - Preformatted

SELECT T.ROW_ID, /* 1 */ 'N', /* 2 */ SL.STATEMENT_LINE_ID, /* 3 */ ABA.BANK_ACCOUNT_ID, /* 4 */ T.TRX_ID, /* 5 */ T.TRX_TYPE, /* 6 */ T.TRX_TYPE_DSP, /* 7 */ T.TRX_NUMBER, /* 8 */ T.CURRENCY_CODE, /* 9 */ DECODE(T.CURRENCY_CODE, /* 10 */ SOB.CURRENCY_CODE, 'FUNCTIONAL', ABA.CURRENCY_CODE, 'BANK', 'FOREIGN'), T.AMOUNT, /* 11 */ DECODE(T.CURRENCY_CODE, /* 12 */ SOB.CURRENCY_CODE, T.AMOUNT, ABA.CURRENCY_CODE, T.AMOUNT, NVL(T.ACCTD_AMOUNT,T.AMOUNT)), DECODE(T.CURRENCY_CODE, /* 13 */ SOB.CURRENCY_CODE, NVL(T.CLEARED_AMOUNT, T.AMOUNT), ABA.CURRENCY_CODE, NVL(T.CLEARED_AMOUNT, T.AMOUNT), NVL(T.ACCTD_CLEARED_AMOUNT, NVL(T.CLEARED_AMOUNT, T.AMOUNT))), T.GL_DATE, /* 14 */ T.STATUS_DSP, /* 15 */ T.STATUS, /* 16 */ trunc(T.TRX_DATE), /* 17 */ NVL(T.CLEARED_DATE, trunc(T.TRX_DATE)), /* 18 */ TO_DATE(NULL), /* 19 */ T.EXCHANGE_RATE_DATE, /* 20 */ GDC.USER_CONVERSION_TYPE, /* 21 */ T.EXCHANGE_RATE, /* 22 */ DECODE(T.CURRENCY_CODE, /* 23 */ SOB.CURRENCY_CODE, T.CHARGES_AMOUNT, ABA.CURRENCY_CODE, T.CHARGES_AMOUNT, NVL(T.ACCTD_CHARGES_AMOUNT, T.CHARGES_AMOUNT)), DECODE(T.CURRENCY_CODE, /* 24 */ SOB.CURRENCY_CODE, T.ERROR_AMOUNT, ABA.CURRENCY_CODE, T.ERROR_AMOUNT, NVL(T.ACCTD_ERROR_AMOUNT, T.ERROR_AMOUNT)), NULL, /* 25 */ TO_NUMBER(NULL), /* 26 */ NULL, /* 27 */ NULL, /* 28 */ NULL, /* 29 */ TO_NUMBER(NULL), /* 30 */ TO_NUMBER(NULL), /* 31 */ NULL, /* 32 */ NULL, /* 33 */ TO_NUMBER(NULL), /* 34 */ T.CREATION_DATE, /* 35 */ T.CREATED_BY, /* 36 */ T.LAST_UPDATE_DATE, /* 37 */ T.LAST_UPDATED_BY, /* 38 */ SL.STATEMENT_HEADER_ID, /* 39 */ TO_NUMBER(NULL), /* 40 */ ABA.BANK_BRANCH_ID, /* 41 */ TO_DATE(NULL), /* 42 */ NULL, /* 43 */ TO_DATE(NULL), /* 44 */ L1.MEANING, /* 45 */ SL.LINE_NUMBER, /* 46 */ SL.TRX_DATE, /* 47 */ SL.AMOUNT, /* 48 */ SL.STATUS, /* 49 */ TC.DESCRIPTION, /* 50 */ SH.STATEMENT_NUMBER, /* 51 */ SH.STATEMENT_DATE, /* 52 */ ABA.BANK_ACCOUNT_NAME, /* 53 */ ABA.BANK_ACCOUNT_NUM, /* 54 */ ABA.CURRENCY_CODE, /* 55 */ SH.DOC_SEQUENCE_VALUE, /* 56 */ SL.TRX_TYPE, /* 57 */ NULL, /* 58 */ SH.CONTROL_END_BALANCE, /* 59 */ 999, /* 60 */ 1, /* 61 */ DECODE(T.TRX_TYPE, /* 62 */ '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))), 'ROI_LINE', /* 63 */ to_number(NULL), to_number(NULL), to_number(NULL) FROM CE_LOOKUPS L1, CE_STATEMENT_HEADERS SH, CE_TRANSACTION_CODES TC, GL_DAILY_CONVERSION_TYPES GDC, GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS, CE_BANK_ACCTS_GT_V ABA, CE_999_INTERFACE_V T, CE_STATEMENT_RECONCILS_ALL CRE, CE_STATEMENT_LINES SL WHERE TC.TRANSACTION_CODE_ID(+) = SL.TRX_CODE_ID AND GDC.CONVERSION_TYPE(+) = T.EXCHANGE_RATE_TYPE AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID AND L1.LOOKUP_TYPE = 'BANK_TRX_TYPE' AND L1.LOOKUP_CODE = SL.TRX_TYPE AND T.TRX_ID = CRE.REFERENCE_ID AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID AND CRE.CURRENT_RECORD_FLAG = 'Y' AND CRE.REFERENCE_TYPE = 'ROI_LINE' AND CRE.STATUS_FLAG = 'M' UNION ALL SELECT ROW_ID ,MULTI_SELECT ,STATEMENT_LINE_ID ,BANK_ACCOUNT_ID ,TRX_ID ,TRX_TYPE ,TYPE_MEANING ,TRX_NUMBER ,CURRENCY_CODE ,TRX_CURRENCY_TYPE ,AMOUNT ,BANK_ACCOUNT_AMOUNT ,AMOUNT_CLEARED ,GL_DATE ,STATUS_DSP ,STATUS ,TRX_DATE ,CLEARED_DATE ,MATURITY_DATE ,EXCHANGE_RATE_DATE ,EXCHANGE_RATE_TYPE ,EXCHANGE_RATE ,BANK_CHARGES ,BANK_ERRORS ,BATCH_NAME ,BATCH_ID ,AGENT_NAME ,CUSTOMER_NAME ,VENDOR_NAME ,CUSTOMER_ID ,SUPPLIER_ID ,REFERENCE_TYPE_DSP ,REFERENCE_TYPE ,REFERENCE_ID ,CREATION_DATE ,CREATED_BY ,LAST_UPDATE_DATE ,LAST_UPDATED_BY ,STATEMENT_HEADER_ID ,CASH_RECEIPT_ID ,BANK_BRANCH_ID ,RECEIPT_DATE ,POSTED_FLAG ,POSTED_DATE ,LINE_TYPE_MEANING ,STATEMENT_LINE_NUMBER ,STATEMENT_LINE_DATE ,STATEMENT_LINE_AMOUNT ,STATEMENT_LINE_STATUS_MEANING ,STATEMENT_LINE_TYPE_MEANING ,STATEMENT_HEADER_NUMBER ,STATEMENT_HEADER_DATE ,BANK_ACCOUNT_NAME ,BANK_ACCOUNT_NUM ,BANK_CURRENCY_CODE ,STATEMENT_DOC_SEQUENCE_VALUE ,LINE_TRX_TYPE ,REMITTANCE_NUMBER ,CONTROL_END_BALANCE ,APPLICATION_ID ,COUNT_CLEARED ,ACTUAL_AMOUNT_CLEARED ,CLEARING_TRX_TYPE ,CE_BANK_ACCT_USE_ID ,ORG_ID ,LEGAL_ENTITY_ID FROM CE_185_RECONCILED_V

View Text - HTML Formatted

SELECT T.ROW_ID
, /* 1 */ 'N'
, /* 2 */ SL.STATEMENT_LINE_ID
, /* 3 */ ABA.BANK_ACCOUNT_ID
, /* 4 */ T.TRX_ID
, /* 5 */ T.TRX_TYPE
, /* 6 */ T.TRX_TYPE_DSP
, /* 7 */ T.TRX_NUMBER
, /* 8 */ T.CURRENCY_CODE
, /* 9 */ DECODE(T.CURRENCY_CODE
, /* 10 */ SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, T.AMOUNT
, /* 11 */ DECODE(T.CURRENCY_CODE
, /* 12 */ SOB.CURRENCY_CODE
, T.AMOUNT
, ABA.CURRENCY_CODE
, T.AMOUNT
, NVL(T.ACCTD_AMOUNT
, T.AMOUNT))
, DECODE(T.CURRENCY_CODE
, /* 13 */ SOB.CURRENCY_CODE
, NVL(T.CLEARED_AMOUNT
, T.AMOUNT)
, ABA.CURRENCY_CODE
, NVL(T.CLEARED_AMOUNT
, T.AMOUNT)
, NVL(T.ACCTD_CLEARED_AMOUNT
, NVL(T.CLEARED_AMOUNT
, T.AMOUNT)))
, T.GL_DATE
, /* 14 */ T.STATUS_DSP
, /* 15 */ T.STATUS
, /* 16 */ TRUNC(T.TRX_DATE)
, /* 17 */ NVL(T.CLEARED_DATE
, TRUNC(T.TRX_DATE))
, /* 18 */ TO_DATE(NULL)
, /* 19 */ T.EXCHANGE_RATE_DATE
, /* 20 */ GDC.USER_CONVERSION_TYPE
, /* 21 */ T.EXCHANGE_RATE
, /* 22 */ DECODE(T.CURRENCY_CODE
, /* 23 */ SOB.CURRENCY_CODE
, T.CHARGES_AMOUNT
, ABA.CURRENCY_CODE
, T.CHARGES_AMOUNT
, NVL(T.ACCTD_CHARGES_AMOUNT
, T.CHARGES_AMOUNT))
, DECODE(T.CURRENCY_CODE
, /* 24 */ SOB.CURRENCY_CODE
, T.ERROR_AMOUNT
, ABA.CURRENCY_CODE
, T.ERROR_AMOUNT
, NVL(T.ACCTD_ERROR_AMOUNT
, T.ERROR_AMOUNT))
, NULL
, /* 25 */ TO_NUMBER(NULL)
, /* 26 */ NULL
, /* 27 */ NULL
, /* 28 */ NULL
, /* 29 */ TO_NUMBER(NULL)
, /* 30 */ TO_NUMBER(NULL)
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33 */ TO_NUMBER(NULL)
, /* 34 */ T.CREATION_DATE
, /* 35 */ T.CREATED_BY
, /* 36 */ T.LAST_UPDATE_DATE
, /* 37 */ T.LAST_UPDATED_BY
, /* 38 */ SL.STATEMENT_HEADER_ID
, /* 39 */ TO_NUMBER(NULL)
, /* 40 */ ABA.BANK_BRANCH_ID
, /* 41 */ TO_DATE(NULL)
, /* 42 */ NULL
, /* 43 */ TO_DATE(NULL)
, /* 44 */ L1.MEANING
, /* 45 */ SL.LINE_NUMBER
, /* 46 */ SL.TRX_DATE
, /* 47 */ SL.AMOUNT
, /* 48 */ SL.STATUS
, /* 49 */ TC.DESCRIPTION
, /* 50 */ SH.STATEMENT_NUMBER
, /* 51 */ SH.STATEMENT_DATE
, /* 52 */ ABA.BANK_ACCOUNT_NAME
, /* 53 */ ABA.BANK_ACCOUNT_NUM
, /* 54 */ ABA.CURRENCY_CODE
, /* 55 */ SH.DOC_SEQUENCE_VALUE
, /* 56 */ SL.TRX_TYPE
, /* 57 */ NULL
, /* 58 */ SH.CONTROL_END_BALANCE
, /* 59 */ 999
, /* 60 */ 1
, /* 61 */ DECODE(T.TRX_TYPE
, /* 62 */ '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)))
, 'ROI_LINE'
, /* 63 */ TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
FROM CE_LOOKUPS L1
, CE_STATEMENT_HEADERS SH
, CE_TRANSACTION_CODES TC
, GL_DAILY_CONVERSION_TYPES GDC
, GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_BANK_ACCTS_GT_V ABA
, CE_999_INTERFACE_V T
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_STATEMENT_LINES SL
WHERE TC.TRANSACTION_CODE_ID(+) = SL.TRX_CODE_ID
AND GDC.CONVERSION_TYPE(+) = T.EXCHANGE_RATE_TYPE
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID
AND L1.LOOKUP_TYPE = 'BANK_TRX_TYPE'
AND L1.LOOKUP_CODE = SL.TRX_TYPE
AND T.TRX_ID = CRE.REFERENCE_ID
AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID
AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID
AND CRE.CURRENT_RECORD_FLAG = 'Y'
AND CRE.REFERENCE_TYPE = 'ROI_LINE'
AND CRE.STATUS_FLAG = 'M' UNION ALL SELECT ROW_ID
, MULTI_SELECT
, STATEMENT_LINE_ID
, BANK_ACCOUNT_ID
, TRX_ID
, TRX_TYPE
, TYPE_MEANING
, TRX_NUMBER
, CURRENCY_CODE
, TRX_CURRENCY_TYPE
, AMOUNT
, BANK_ACCOUNT_AMOUNT
, AMOUNT_CLEARED
, GL_DATE
, STATUS_DSP
, STATUS
, TRX_DATE
, CLEARED_DATE
, MATURITY_DATE
, EXCHANGE_RATE_DATE
, EXCHANGE_RATE_TYPE
, EXCHANGE_RATE
, BANK_CHARGES
, BANK_ERRORS
, BATCH_NAME
, BATCH_ID
, AGENT_NAME
, CUSTOMER_NAME
, VENDOR_NAME
, CUSTOMER_ID
, SUPPLIER_ID
, REFERENCE_TYPE_DSP
, REFERENCE_TYPE
, REFERENCE_ID
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, STATEMENT_HEADER_ID
, CASH_RECEIPT_ID
, BANK_BRANCH_ID
, RECEIPT_DATE
, POSTED_FLAG
, POSTED_DATE
, LINE_TYPE_MEANING
, STATEMENT_LINE_NUMBER
, STATEMENT_LINE_DATE
, STATEMENT_LINE_AMOUNT
, STATEMENT_LINE_STATUS_MEANING
, STATEMENT_LINE_TYPE_MEANING
, STATEMENT_HEADER_NUMBER
, STATEMENT_HEADER_DATE
, BANK_ACCOUNT_NAME
, BANK_ACCOUNT_NUM
, BANK_CURRENCY_CODE
, STATEMENT_DOC_SEQUENCE_VALUE
, LINE_TRX_TYPE
, REMITTANCE_NUMBER
, CONTROL_END_BALANCE
, APPLICATION_ID
, COUNT_CLEARED
, ACTUAL_AMOUNT_CLEARED
, CLEARING_TRX_TYPE
, CE_BANK_ACCT_USE_ID
, ORG_ID
, LEGAL_ENTITY_ID
FROM CE_185_RECONCILED_V