eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: CE_200_RECONCILED_V

Product:	CE - Cash Management
Description:	Reconciled AP payments for unreconciling
Implementation/DBA Data:	APPS.CE_200_RECONCILED_V

View Text

SELECT C.ROWID
, /* 1 */ 'N'
, /* 2 */ SL.STATEMENT_LINE_ID
, /* 3 */ SH.BANK_ACCOUNT_ID
, /* 4 */ C.CHECK_ID
, /* 5 */ 'PAYMENT'
, /* 6 */ (SELECT L2.MEANING
FROM CE_LOOKUPS L2
WHERE L2.LOOKUP_TYPE = 'TRX_TYPE'
AND L2.LOOKUP_CODE = 'PAYMENT')
, /* 7 */ TO_CHAR(C.CHECK_NUMBER)
, /* 8 */ C.CURRENCY_CODE
, /* 9 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, /* 10 */ ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, C.AMOUNT
, /* 11 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.AMOUNT
, /* 12 */ ABA.CURRENCY_CODE
, C.AMOUNT
, NVL(C.BASE_AMOUNT
, C.AMOUNT))
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 13 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_AMOUNT
, 'VOIDED'
, C.AMOUNT
, 'STOP INITIATED'
, C.AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_AMOUNT
, 'VOIDED'
, C.AMOUNT
, 'STOP INITIATED'
, C.AMOUNT)
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)
, 'VOIDED'
, NVL(C.BASE_AMOUNT
, C.AMOUNT)
, 'STOP INITIATED'
, NVL(C.BASE_AMOUNT
, C.AMOUNT)))
, APH.ACCOUNTING_DATE
, /* 14 */ L3.DISPLAYED_FIELD
, /* 15 */ C.STATUS_LOOKUP_CODE
, /* 16 */ C.CHECK_DATE
, /* 17 */ C.CLEARED_DATE
, /* 18 */ C.FUTURE_PAY_DUE_DATE
, /* 19 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, /* 20 */ 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_EXCHANGE_DATE
, C.EXCHANGE_DATE)
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, /* 21 */ 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, GDCC.USER_CONVERSION_TYPE
, GDC.USER_CONVERSION_TYPE)
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, /* 22 */ 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_EXCHANGE_RATE
, C.EXCHANGE_RATE)
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 23 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_CHARGES_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_CHARGES_AMOUNT)
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_CHARGES_BASE_AMOUNT))
, DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 24 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_ERROR_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_ERROR_AMOUNT)
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_ERROR_BASE_AMOUNT))
, NVL(TO_CHAR(C.PAYMENT_INSTRUCTION_ID)
, C.CHECKRUN_NAME)
, /* 25 */ NVL(C.PAYMENT_INSTRUCTION_ID
, C.CHECKRUN_ID)
, /* 26 */ C.VENDOR_NAME
, /* 27 */ NULL
, /* 28 */ C.VENDOR_NAME
, /* 29 */ TO_NUMBER(NULL)
, /* 30 */ C.VENDOR_ID
, /* 31 */ NULL
, /* 32 */ NULL
, /* 33 */ TO_NUMBER(NULL)
, /* 34 */ CRE.CREATION_DATE
, /* 35 */ CRE.CREATED_BY
, /* 36 */ CRE.LAST_UPDATE_DATE
, /* 37 */ CRE.LAST_UPDATED_BY
, /* 38 */ SL.STATEMENT_HEADER_ID
, /* 39 */ TO_NUMBER(NULL)
, /* 40 */ ABA.BANK_BRANCH_ID
, /* 41 */ C.CHECK_DATE
, /* 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 */ DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, B.BATCH_IDENTIFIER
, TO_CHAR(C.PAYMENT_INSTRUCTION_ID))
, /* 58 */ SH.CONTROL_END_BALANCE
, /* 59 */ 200
, /* 60 */ 1
, /* 61 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 62 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_AMOUNT - (NVL(C.CLEARED_CHARGES_AMOUNT
, 0)+ NVL(C.CLEARED_ERROR_AMOUNT
, 0)))
, ABA.CURRENCY_CODE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, C.CLEARED_AMOUNT - (NVL(C.CLEARED_CHARGES_AMOUNT
, 0)+ NVL(C.CLEARED_ERROR_AMOUNT
, 0)))
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'RECONCILED'
, 'RECONCILED UNACCOUNTED'
, 'RECONCILED'
, C.STATUS_LOOKUP_CODE)
, 'RECONCILED'
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT - (NVL(C.CLEARED_CHARGES_BASE_AMOUNT
, 0)+ NVL(C.CLEARED_ERROR_BASE_AMOUNT
, 0)))))
, 'PAYMENT'
, /* 63 */ CRE.REQUEST_ID
, /* 64 */ SL.INVOICE_TEXT
, /* 65 */ SL.BANK_ACCOUNT_TEXT
, /* 66 */ SL.CUSTOMER_TEXT
, /* 67 */ C.ACTUAL_VALUE_DATE
, BAU.BANK_ACCT_USE_ID
, C.ORG_ID
, TO_NUMBER(NULL) FROM CE_STATEMENT_HEADERS SH
, CE_TRANSACTION_CODES TC
, AP_LOOKUP_CODES L3
, CE_LOOKUPS L1
, GL_DAILY_CONVERSION_TYPES GDC
, GL_DAILY_CONVERSION_TYPES GDCC
, GL_LEDGERS SOB
, CE_SYSTEM_PARAMETERS SYS
, CE_BANK_ACCOUNTS ABA
, CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, AP_INVOICE_SELECTION_CRITERIA B
, AP_CHECKS_ALL C
, AP_PAYMENT_HISTORY_ALL APH
, CE_STATEMENT_RECONCILS_ALL CRE
, CE_STATEMENT_LINES SL
WHERE TC.TRANSACTION_CODE_ID(+) = SL.TRX_CODE_ID AND L3.LOOKUP_TYPE(+) = 'CHECK STATE' AND L3.LOOKUP_CODE(+) = C.STATUS_LOOKUP_CODE AND L1.LOOKUP_TYPE = 'BANK_TRX_TYPE' AND L1.LOOKUP_CODE = SL.TRX_TYPE AND GDC.CONVERSION_TYPE(+) = C.EXCHANGE_RATE_TYPE AND GDCC.CONVERSION_TYPE(+) = C.CLEARED_EXCHANGE_RATE_TYPE AND SOB.LEDGER_ID = SYS.SET_OF_BOOKS_ID
AND SOB.OBJECT_TYPE_CODE = 'L'
AND NVL(SOB.COMPLETE_FLAG
, 'Y') = 'Y' AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID AND C.CHECK_ID = CRE.REFERENCE_ID AND ABA.BANK_ACCOUNT_ID = SH.BANK_ACCOUNT_ID AND ABA.BANK_ACCOUNT_ID = BAU.BANK_ACCOUNT_ID
AND BAU.AP_USE_ENABLE_FLAG = 'Y'
AND BAU.ORG_ID = OU.ORGANIZATION_ID
AND OU.ORGANIZATION_TYPE = 'OPERATING_UNIT' AND BAU.ORG_ID = C.ORG_ID /*AND BAU.ORG_ID = SYS.ORG_ID*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND APH.CHECK_ID (+) = C.CHECK_ID
AND APH.TRANSACTION_TYPE (+) = 'PAYMENT CLEARING'
AND NOT EXISTS (SELECT NULL
FROM AP_PAYMENT_HISTORY APH2
WHERE APH2.CHECK_ID = C.CHECK_ID
AND APH2.TRANSACTION_TYPE IN ( DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'PAYMENT CLEARING'
, 'PAYMENT UNCLEARING')
, 'PAYMENT UNCLEARING')
AND APH2.PAYMENT_HISTORY_ID > APH.PAYMENT_HISTORY_ID) AND SH.STATEMENT_HEADER_ID = SL.STATEMENT_HEADER_ID AND SL.STATEMENT_LINE_ID = CRE.STATEMENT_LINE_ID AND BAU.ORG_ID = CRE.ORG_ID AND CRE.CURRENT_RECORD_FLAG = 'Y' AND CRE.REFERENCE_TYPE = 'PAYMENT' AND CRE.STATUS_FLAG = 'M'

Columns

Name
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
REQUEST_ID
INVOICE_TEXT
BANK_ACCOUNT_TEXT
CUSTOMER_TEXT
VALUE_DATE
CE_BANK_ACCT_USE_ID
ORG_ID
LEGAL_ENTITY_ID