VIEW: APPS.CE_200_RECONCILED_V

Source

View Text - Preformatted

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 c.status_lookup_code <> 'VOIDED' ) 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'

View Text - HTML Formatted

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 C.STATUS_LOOKUP_CODE <> 'VOIDED' )
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'