VIEW: APPS.CE_200_TRANSACTIONS_V

Source

View Text - Preformatted

SELECT C.ROWID, /* 1 */ 'N', /* 2 */ ABA.BANK_ACCOUNT_ID, /* 3 */ ABA.BANK_ACCOUNT_NAME, /* 4 */ ABA.BANK_ACCOUNT_NUM, /* 5 */ BankParty.PARTY_NAME, /* 6 */ BranchParty.PARTY_NAME, /* 7 */ C.CHECK_ID, /* 8 */ 'PAYMENT', /* 9 */ L2.MEANING, /* 10 */ TO_CHAR(C.CHECK_NUMBER), /* 11 */ C.CHECK_NUMBER, /* 12 */ C.CURRENCY_CODE, /* 13 */ C.AMOUNT, /* 14 */ DECODE(C.CURRENCY_CODE,SOB.CURRENCY_CODE, C.AMOUNT, ABA.CURRENCY_CODE, C.AMOUNT, C.AMOUNT * C.EXCHANGE_RATE), /* 15 */ DECODE( C.CURRENCY_CODE,SOB.CURRENCY_CODE, /* 16 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE,'CLEARED BUT UNACCOUNTED', 'CLEARED', C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_AMOUNT), ABA.CURRENCY_CODE, DECODE(DECODE(C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED', C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_AMOUNT), DECODE( DECODE(C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED', C.STATUS_LOOKUP_CODE), 'CLEARED', NVL(C.CLEARED_BASE_AMOUNT,C.CLEARED_AMOUNT))), DECODE(APH.transaction_type, 'PAYMENT CLEARING', APH.ACCOUNTING_DATE), /* 17 */ L1.DISPLAYED_FIELD, /* 18 */ C.STATUS_LOOKUP_CODE, /* 19 */ C.CHECK_DATE, /* 20 */ C.CLEARED_DATE, /* 21 */ C.FUTURE_PAY_DUE_DATE, /* 22 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE, /* 23 */ 'CLEARED BUT UNACCOUNTED','CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_EXCHANGE_DATE,C.EXCHANGE_DATE), DECODE( DECODE( C.STATUS_LOOKUP_CODE, /* 24 */ 'CLEARED BUT UNACCOUNTED' , 'CLEARED', C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_EXCHANGE_RATE_TYPE,C.EXCHANGE_RATE_TYPE), DECODE( DECODE( C.STATUS_LOOKUP_CODE, /* 25 */ 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED',GDCC.USER_CONVERSION_TYPE,GDC.USER_CONVERSION_TYPE), DECODE( DECODE( C.STATUS_LOOKUP_CODE, /* 26 */ 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED',C.CLEARED_EXCHANGE_RATE,C.EXCHANGE_RATE), DECODE( C.CURRENCY_CODE,SOB.CURRENCY_CODE, /* 27 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED' ,C.CLEARED_CHARGES_AMOUNT), ABA.CURRENCY_CODE, DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED',C.CLEARED_CHARGES_AMOUNT), DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_CHARGES_BASE_AMOUNT)), DECODE( C.CURRENCY_CODE,SOB.CURRENCY_CODE, /* 28 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED' , C.CLEARED_ERROR_AMOUNT), ABA.CURRENCY_CODE, DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_ERROR_AMOUNT), DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_ERROR_BASE_AMOUNT)), nvl(to_char(C.PAYMENT_INSTRUCTION_ID), C.CHECKRUN_NAME), /* 29 */ nvl(C.PAYMENT_INSTRUCTION_ID, C.CHECKRUN_ID), /* 30 */ C.VENDOR_NAME, /* 31 */ NULL, /* 32 */ C.PAYMENT_METHOD_CODE, /* LK.DISPLAYED_FIELD,*/ /* 33 */ C.VENDOR_NAME, /* 34 */ TO_NUMBER(NULL), /* 35 */ C.VENDOR_ID, /* 36*/ NULL, /* 37 */ NULL, /* 38 */ TO_NUMBER(NULL), /* 39 */ DECODE(CRE.STATUS_FLAG,'M', /* 40 */ DECODE(C.CURRENCY_CODE,SOB.CURRENCY_CODE,C.CLEARED_AMOUNT, ABA.CURRENCY_CODE, C.CLEARED_AMOUNT, NVL(C.CLEARED_BASE_AMOUNT,C.CLEARED_AMOUNT)) - NVL(CLEARED_CHARGES_BASE_AMOUNT,0) -NVL(CLEARED_ERROR_BASE_AMOUNT,0)), C.CREATION_DATE, /* 41 */ C.CREATED_BY, /* 42 */ C.LAST_UPDATE_DATE, /* 43 */ C.LAST_UPDATED_BY, /* 44 */ decode(C.PAYMENT_INSTRUCTION_ID, null, B.BATCH_IDENTIFIER, to_char(C.PAYMENT_INSTRUCTION_ID)), /* 45 */ TO_NUMBER(decode(C.PAYMENT_INSTRUCTION_ID, null, 200, 673)), /* 46 */ 200, /* 47 */ 0, /* 48 */ ABA.CURRENCY_CODE, /* 49 */ DECODE(C.CURRENCY_CODE,SOB.CURRENCY_CODE, 'FUNCTIONAL', /* 50 */ ABA.CURRENCY_CODE, 'BANK','FOREIGN'), nvl(gac.AP_ASSET_CCID , ABA.ASSET_CODE_COMBINATION_ID), /* 51 */ NULL, /* 52 */ NULL, /* 53 */ TO_NUMBER(NULL), /* 54 */ TO_NUMBER(NULL), /* 55 */ decode(c.payment_type_flag, 'R', 'REFUND', 'PAYMENT'), /* 56 */ NULL, /* 57 */ DECODE( DECODE(C.STATUS_LOOKUP_CODE, /* 58 */ 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED',C.CLEARED_BASE_AMOUNT,C.BASE_AMOUNT), TO_NUMBER(NULL), /* 59 */ TO_NUMBER(NULL), /* 60 */ NULL, /* 61 */ TO_DATE(NULL), /* 62 */ C.ACTUAL_VALUE_DATE, NULL, BAU.BANK_ACCT_USE_ID, TO_NUMBER(NULL), C.ORG_ID FROM CE_BANK_ACCOUNTS ABA, /*CE_BANK_ACCT_USES_OU_V BAU,*/ CE_BANK_ACCT_USES_ALL BAU, CE_SECURITY_PROFILES_GT OU, CE_GL_ACCOUNTS_CCID GAC, /*CE_BANK_BRANCHES_V ABB, */ HZ_PARTIES BankParty, HZ_PARTIES BranchParty, AP_CHECKS_ALL C, GL_DAILY_CONVERSION_TYPES GDC, GL_DAILY_CONVERSION_TYPES GDCC, CE_LOOKUPS L2, AP_LOOKUP_CODES L1,/* AP_LOOKUP_CODES LK,*/ GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS, AP_INV_SELECTION_CRITERIA_ALL B, /*IBY_FD_PAYMENTS_V PAY,*/ AP_PAYMENT_HISTORY_ALL APH, CE_STATEMENT_RECONCILS_ALL CRE WHERE /*ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID*/ ABA.BANK_BRANCH_ID = BranchParty.PARTY_ID AND ABA.BANK_ID = BankParty.PARTY_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 = SYS.ORG_ID*/ AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID and BAU.BANK_ACCT_USE_ID = C.CE_BANK_ACCT_USE_ID AND BAU.ORG_ID = C.ORG_ID and c.payment_type_flag != 'R' and GDC.CONVERSION_TYPE(+) = C.EXCHANGE_RATE_TYPE AND GDCC.CONVERSION_TYPE(+) = C.CLEARED_EXCHANGE_RATE_TYPE /*AND LK.LOOKUP_CODE = C.PAYMENT_METHOD_CODE AND LK.LOOKUP_TYPE = 'PAYMENT METHOD'*/ AND L2.LOOKUP_TYPE = 'TRX_TYPE' AND L2.LOOKUP_CODE = 'PAYMENT' AND L1.LOOKUP_TYPE = 'CHECK STATE' AND L1.LOOKUP_CODE = C.STATUS_LOOKUP_CODE AND C.STATUS_LOOKUP_CODE IN ('NEGOTIABLE', DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1, 'NEGOTIABLE' ,'VOIDED'), DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1, 'NEGOTIABLE','STOP INITIATED' ), DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1,'NEGOTIABLE', DECODE(SYS.SHOW_CLEARED_FLAG, 'N', 'NEGOTIABLE','CLEARED')), DECODE( CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1,'NEGOTIABLE', DECODE(SYS.SHOW_CLEARED_FLAG, 'N', 'NEGOTIABLE', 'CLEARED BUT UNACCOUNTED'))) AND DECODE(DECODE(C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED'), 'CLEARED',NVL(C.CLEARED_DATE,C.CHECK_DATE),C.CHECK_DATE) >=SYS.CASHBOOK_BEGIN_DATE AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID /*and c.payment_id = pay.payment_id (+)*/ AND APH.ORG_ID (+) = C.ORG_ID AND APH.CHECK_ID(+) = C.CHECK_ID AND APH.TRANSACTION_TYPE (+) like 'PAYMENT '|| decode(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1,'UNCLEARING', '%CLEARING') and not exists (select null from ap_payment_history aph2 where aph2.check_id = c.check_id and aph2.transaction_type in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING') and aph2.creation_date > aph.creation_date) AND CRE.ORG_ID (+) = C.ORG_ID AND CRE.REFERENCE_ID(+) = C.CHECK_ID AND CRE.REFERENCE_TYPE(+) = 'PAYMENT' AND NVL(CRE.STATUS_FLAG,'U') = 'U' AND NVL(CRE.CURRENT_RECORD_FLAG, 'Y') = 'Y' AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID UNION ALL SELECT C.ROWID, /* 1 */ 'N', /* 2 */ ABA.BANK_ACCOUNT_ID, /* 3 */ ABA.BANK_ACCOUNT_NAME, /* 4 */ ABA.BANK_ACCOUNT_NUM, /* 5 */ BankParty.PARTY_NAME, /* 6 */ BranchParty.PARTY_NAME, /* 7 */ C.CHECK_ID, /* 8 */ 'REFUND', /* 9 */ L2.MEANING, /* 10 */ TO_CHAR(C.CHECK_NUMBER), /* 11 */ C.CHECK_NUMBER, /* 12 */ C.CURRENCY_CODE, /* 13 */ C.AMOUNT, /* 14 */ DECODE(C.CURRENCY_CODE,SOB.CURRENCY_CODE, C.AMOUNT, ABA.CURRENCY_CODE, C.AMOUNT, C.AMOUNT * C.EXCHANGE_RATE), /* 15 */ DECODE( C.CURRENCY_CODE,SOB.CURRENCY_CODE, /* 16 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE,'CLEARED BUT UNACCOUNTED', 'CLEARED', C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_AMOUNT), ABA.CURRENCY_CODE, DECODE(DECODE(C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED', C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_AMOUNT), DECODE( DECODE(C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED', C.STATUS_LOOKUP_CODE), 'CLEARED', NVL(C.CLEARED_BASE_AMOUNT,C.CLEARED_AMOUNT))), DECODE(APH.transaction_type, 'PAYMENT CLEARING', APH.ACCOUNTING_DATE), /* 17 */ L1.DISPLAYED_FIELD, /* 18 */ C.STATUS_LOOKUP_CODE, /* 19 */ C.CHECK_DATE, /* 20 */ C.CLEARED_DATE, /* 21 */ C.FUTURE_PAY_DUE_DATE, /* 22 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE, /* 23 */ 'CLEARED BUT UNACCOUNTED','CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_EXCHANGE_DATE,C.EXCHANGE_DATE), DECODE( DECODE( C.STATUS_LOOKUP_CODE, /* 24 */ 'CLEARED BUT UNACCOUNTED' , 'CLEARED', C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_EXCHANGE_RATE_TYPE,C.EXCHANGE_RATE_TYPE), DECODE( DECODE( C.STATUS_LOOKUP_CODE, /* 25 */ 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED',GDCC.USER_CONVERSION_TYPE,GDC.USER_CONVERSION_TYPE), DECODE( DECODE( C.STATUS_LOOKUP_CODE, /* 26 */ 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED',C.CLEARED_EXCHANGE_RATE,C.EXCHANGE_RATE), DECODE( C.CURRENCY_CODE,SOB.CURRENCY_CODE, /* 27 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED' ,C.CLEARED_CHARGES_AMOUNT), ABA.CURRENCY_CODE, DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED',C.CLEARED_CHARGES_AMOUNT), DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_CHARGES_BASE_AMOUNT)), DECODE( C.CURRENCY_CODE,SOB.CURRENCY_CODE, /* 28 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED' , C.CLEARED_ERROR_AMOUNT), ABA.CURRENCY_CODE, DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_ERROR_AMOUNT), DECODE( DECODE( C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED', C.CLEARED_ERROR_BASE_AMOUNT)), nvl(to_char(C.PAYMENT_INSTRUCTION_ID), C.CHECKRUN_NAME), /* 29 */ nvl(C.PAYMENT_INSTRUCTION_ID, C.CHECKRUN_ID), /* 30 */ C.VENDOR_NAME, /* 31 */ NULL, /* 32 */ C.PAYMENT_METHOD_CODE, /* LK.DISPLAYED_FIELD,*/ /* 33 */ C.VENDOR_NAME, /* 34 */ TO_NUMBER(NULL), /* 35 */ C.VENDOR_ID, /* 36*/ NULL, /* 37 */ NULL, /* 38 */ TO_NUMBER(NULL), /* 39 */ DECODE(CRE.STATUS_FLAG,'M', /* 40 */ DECODE(C.CURRENCY_CODE,SOB.CURRENCY_CODE,C.CLEARED_AMOUNT, ABA.CURRENCY_CODE, C.CLEARED_AMOUNT, NVL(C.CLEARED_BASE_AMOUNT,C.CLEARED_AMOUNT)) - NVL(CLEARED_CHARGES_BASE_AMOUNT,0) -NVL(CLEARED_ERROR_BASE_AMOUNT,0)), C.CREATION_DATE, /* 41 */ C.CREATED_BY, /* 42 */ C.LAST_UPDATE_DATE, /* 43 */ C.LAST_UPDATED_BY, /* 44 */ decode(C.PAYMENT_INSTRUCTION_ID, null, B.BATCH_IDENTIFIER, to_char(C.PAYMENT_INSTRUCTION_ID)), /* 45 */ TO_NUMBER(decode(C.PAYMENT_INSTRUCTION_ID, null, 200, 673)), /* 46 */ 200, /* 47 */ 0, /* 48 */ ABA.CURRENCY_CODE, /* 49 */ DECODE(C.CURRENCY_CODE,SOB.CURRENCY_CODE, 'FUNCTIONAL', /* 50 */ ABA.CURRENCY_CODE, 'BANK','FOREIGN'), nvl(gac.AP_ASSET_CCID , ABA.ASSET_CODE_COMBINATION_ID), /* 51 */ NULL, /* 52 */ NULL, /* 53 */ TO_NUMBER(NULL), /* 54 */ TO_NUMBER(NULL), /* 55 */ decode(c.payment_type_flag, 'R', 'REFUND', 'PAYMENT'), /* 56 */ NULL, /* 57 */ DECODE( DECODE(C.STATUS_LOOKUP_CODE, /* 58 */ 'CLEARED BUT UNACCOUNTED', 'CLEARED',C.STATUS_LOOKUP_CODE), 'CLEARED',C.CLEARED_BASE_AMOUNT,C.BASE_AMOUNT), TO_NUMBER(NULL), /* 59 */ TO_NUMBER(NULL), /* 60 */ NULL, /* 61 */ TO_DATE(NULL), /* 62 */ C.ACTUAL_VALUE_DATE, NULL, BAU.BANK_ACCT_USE_ID, TO_NUMBER(NULL), C.ORG_ID FROM CE_BANK_ACCOUNTS ABA, /*CE_BANK_ACCT_USES_OU_V BAU,*/ CE_BANK_ACCT_USES_ALL BAU, CE_SECURITY_PROFILES_GT OU, CE_GL_ACCOUNTS_CCID GAC, /*CE_BANK_BRANCHES_V ABB, */ HZ_PARTIES BankParty, HZ_PARTIES BranchParty, AP_CHECKS_ALL C, GL_DAILY_CONVERSION_TYPES GDC, GL_DAILY_CONVERSION_TYPES GDCC, CE_LOOKUPS L2, AP_LOOKUP_CODES L1,/* AP_LOOKUP_CODES LK,*/ GL_SETS_OF_BOOKS SOB, CE_SYSTEM_PARAMETERS SYS, AP_INV_SELECTION_CRITERIA_ALL B, /*IBY_FD_PAYMENTS_V PAY,*/ AP_PAYMENT_HISTORY_ALL APH, CE_STATEMENT_RECONCILS_ALL CRE WHERE /*ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID*/ ABA.BANK_BRANCH_ID = BranchParty.PARTY_ID AND ABA.BANK_ID = BankParty.PARTY_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 BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID and BAU.BANK_ACCT_USE_ID = C.CE_BANK_ACCT_USE_ID and c.payment_type_flag = 'R' and GDC.CONVERSION_TYPE(+) = C.EXCHANGE_RATE_TYPE AND GDCC.CONVERSION_TYPE(+) = C.CLEARED_EXCHANGE_RATE_TYPE /*AND LK.LOOKUP_CODE = C.PAYMENT_METHOD_CODE AND LK.LOOKUP_TYPE = 'PAYMENT METHOD'*/ AND L2.LOOKUP_TYPE = 'TRX_TYPE' AND L2.LOOKUP_CODE = 'REFUND' AND L1.LOOKUP_TYPE = 'CHECK STATE' AND L1.LOOKUP_CODE = C.STATUS_LOOKUP_CODE AND C.STATUS_LOOKUP_CODE IN ('NEGOTIABLE', DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1, 'NEGOTIABLE' ,'VOIDED'), DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1, 'NEGOTIABLE','STOP INITIATED' ), DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1,'NEGOTIABLE', DECODE(SYS.SHOW_CLEARED_FLAG, 'N', 'NEGOTIABLE','CLEARED')), DECODE( CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1,'NEGOTIABLE', DECODE(SYS.SHOW_CLEARED_FLAG, 'N', 'NEGOTIABLE', 'CLEARED BUT UNACCOUNTED'))) AND DECODE(DECODE(C.STATUS_LOOKUP_CODE, 'CLEARED BUT UNACCOUNTED', 'CLEARED'), 'CLEARED',NVL(C.CLEARED_DATE,C.CHECK_DATE),C.CHECK_DATE) >=SYS.CASHBOOK_BEGIN_DATE AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID /*and c.payment_id = pay.payment_id (+)*/ AND APH.ORG_ID (+) = C.ORG_ID AND APH.CHECK_ID(+) = C.CHECK_ID AND APH.TRANSACTION_TYPE (+) like 'PAYMENT '|| decode(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING, 1,'UNCLEARING', '%CLEARING') and not exists (select null from ap_payment_history aph2 where aph2.check_id = c.check_id and aph2.transaction_type in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING') and aph2.creation_date > aph.creation_date) AND CRE.ORG_ID (+) = C.ORG_ID AND CRE.REFERENCE_ID(+) = C.CHECK_ID AND CRE.REFERENCE_TYPE(+) = 'PAYMENT' AND NVL(CRE.STATUS_FLAG,'U') = 'U' AND NVL(CRE.CURRENT_RECORD_FLAG, 'Y') = 'Y' AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID

View Text - HTML Formatted

SELECT C.ROWID
, /* 1 */ 'N'
, /* 2 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ ABA.BANK_ACCOUNT_NAME
, /* 4 */ ABA.BANK_ACCOUNT_NUM
, /* 5 */ BANKPARTY.PARTY_NAME
, /* 6 */ BRANCHPARTY.PARTY_NAME
, /* 7 */ C.CHECK_ID
, /* 8 */ 'PAYMENT'
, /* 9 */ L2.MEANING
, /* 10 */ TO_CHAR(C.CHECK_NUMBER)
, /* 11 */ C.CHECK_NUMBER
, /* 12 */ C.CURRENCY_CODE
, /* 13 */ C.AMOUNT
, /* 14 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.AMOUNT
, ABA.CURRENCY_CODE
, C.AMOUNT
, C.AMOUNT * C.EXCHANGE_RATE)
, /* 15 */ DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 16 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_AMOUNT)
, DECODE( DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)))
, DECODE(APH.TRANSACTION_TYPE
, 'PAYMENT CLEARING'
, APH.ACCOUNTING_DATE)
, /* 17 */ L1.DISPLAYED_FIELD
, /* 18 */ C.STATUS_LOOKUP_CODE
, /* 19 */ C.CHECK_DATE
, /* 20 */ C.CLEARED_DATE
, /* 21 */ C.FUTURE_PAY_DUE_DATE
, /* 22 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 23 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_DATE
, C.EXCHANGE_DATE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 24 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_RATE_TYPE
, C.EXCHANGE_RATE_TYPE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 25 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, GDCC.USER_CONVERSION_TYPE
, GDC.USER_CONVERSION_TYPE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 26 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_RATE
, C.EXCHANGE_RATE)
, DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 27 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_AMOUNT)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_BASE_AMOUNT))
, DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 28 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_AMOUNT)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_BASE_AMOUNT))
, NVL(TO_CHAR(C.PAYMENT_INSTRUCTION_ID)
, C.CHECKRUN_NAME)
, /* 29 */ NVL(C.PAYMENT_INSTRUCTION_ID
, C.CHECKRUN_ID)
, /* 30 */ C.VENDOR_NAME
, /* 31 */ NULL
, /* 32 */ C.PAYMENT_METHOD_CODE
, /* LK.DISPLAYED_FIELD
, */ /* 33 */ C.VENDOR_NAME
, /* 34 */ TO_NUMBER(NULL)
, /* 35 */ C.VENDOR_ID
, /* 36*/ NULL
, /* 37 */ NULL
, /* 38 */ TO_NUMBER(NULL)
, /* 39 */ DECODE(CRE.STATUS_FLAG
, 'M'
, /* 40 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.CLEARED_AMOUNT
, ABA.CURRENCY_CODE
, C.CLEARED_AMOUNT
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)) - NVL(CLEARED_CHARGES_BASE_AMOUNT
, 0) -NVL(CLEARED_ERROR_BASE_AMOUNT
, 0))
, C.CREATION_DATE
, /* 41 */ C.CREATED_BY
, /* 42 */ C.LAST_UPDATE_DATE
, /* 43 */ C.LAST_UPDATED_BY
, /* 44 */ DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, B.BATCH_IDENTIFIER
, TO_CHAR(C.PAYMENT_INSTRUCTION_ID))
, /* 45 */ TO_NUMBER(DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, 200
, 673))
, /* 46 */ 200
, /* 47 */ 0
, /* 48 */ ABA.CURRENCY_CODE
, /* 49 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, /* 50 */ ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(GAC.AP_ASSET_CCID
, ABA.ASSET_CODE_COMBINATION_ID)
, /* 51 */ NULL
, /* 52 */ NULL
, /* 53 */ TO_NUMBER(NULL)
, /* 54 */ TO_NUMBER(NULL)
, /* 55 */ DECODE(C.PAYMENT_TYPE_FLAG
, 'R'
, 'REFUND'
, 'PAYMENT')
, /* 56 */ NULL
, /* 57 */ DECODE( DECODE(C.STATUS_LOOKUP_CODE
, /* 58 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_BASE_AMOUNT
, C.BASE_AMOUNT)
, TO_NUMBER(NULL)
, /* 59 */ TO_NUMBER(NULL)
, /* 60 */ NULL
, /* 61 */ TO_DATE(NULL)
, /* 62 */ C.ACTUAL_VALUE_DATE
, NULL
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, C.ORG_ID
FROM CE_BANK_ACCOUNTS ABA
, /*CE_BANK_ACCT_USES_OU_V BAU
, */ CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_GL_ACCOUNTS_CCID GAC
, /*CE_BANK_BRANCHES_V ABB
, */ HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, AP_CHECKS_ALL C
, GL_DAILY_CONVERSION_TYPES GDC
, GL_DAILY_CONVERSION_TYPES GDCC
, CE_LOOKUPS L2
, AP_LOOKUP_CODES L1
, /* AP_LOOKUP_CODES LK
, */ GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, AP_INV_SELECTION_CRITERIA_ALL B
, /*IBY_FD_PAYMENTS_V PAY
, */ AP_PAYMENT_HISTORY_ALL APH
, CE_STATEMENT_RECONCILS_ALL CRE
WHERE /*ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID*/ ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_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 = SYS.ORG_ID*/
AND ABA.ACCOUNT_OWNER_ORG_ID = SYS.LEGAL_ENTITY_ID
AND BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID
AND BAU.BANK_ACCT_USE_ID = C.CE_BANK_ACCT_USE_ID
AND BAU.ORG_ID = C.ORG_ID
AND C.PAYMENT_TYPE_FLAG != 'R'
AND GDC.CONVERSION_TYPE(+) = C.EXCHANGE_RATE_TYPE
AND GDCC.CONVERSION_TYPE(+) = C.CLEARED_EXCHANGE_RATE_TYPE /*AND LK.LOOKUP_CODE = C.PAYMENT_METHOD_CODE
AND LK.LOOKUP_TYPE = 'PAYMENT METHOD'*/
AND L2.LOOKUP_TYPE = 'TRX_TYPE'
AND L2.LOOKUP_CODE = 'PAYMENT'
AND L1.LOOKUP_TYPE = 'CHECK STATE'
AND L1.LOOKUP_CODE = C.STATUS_LOOKUP_CODE
AND C.STATUS_LOOKUP_CODE IN ('NEGOTIABLE'
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, 'VOIDED')
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, 'STOP INITIATED' )
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'NEGOTIABLE'
, 'CLEARED'))
, DECODE( CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'NEGOTIABLE'
, 'CLEARED BUT UNACCOUNTED')))
AND DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED')
, 'CLEARED'
, NVL(C.CLEARED_DATE
, C.CHECK_DATE)
, C.CHECK_DATE) >=SYS.CASHBOOK_BEGIN_DATE
AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID /*AND C.PAYMENT_ID = PAY.PAYMENT_ID (+)*/
AND APH.ORG_ID (+) = C.ORG_ID
AND APH.CHECK_ID(+) = C.CHECK_ID
AND APH.TRANSACTION_TYPE (+) LIKE 'PAYMENT '|| DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'UNCLEARING'
, '%CLEARING')
AND NOT EXISTS (SELECT NULL
FROM AP_PAYMENT_HISTORY APH2
WHERE APH2.CHECK_ID = C.CHECK_ID
AND APH2.TRANSACTION_TYPE IN ('PAYMENT CLEARING'
, 'PAYMENT UNCLEARING')
AND APH2.CREATION_DATE > APH.CREATION_DATE)
AND CRE.ORG_ID (+) = C.ORG_ID
AND CRE.REFERENCE_ID(+) = C.CHECK_ID
AND CRE.REFERENCE_TYPE(+) = 'PAYMENT'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID UNION ALL SELECT C.ROWID
, /* 1 */ 'N'
, /* 2 */ ABA.BANK_ACCOUNT_ID
, /* 3 */ ABA.BANK_ACCOUNT_NAME
, /* 4 */ ABA.BANK_ACCOUNT_NUM
, /* 5 */ BANKPARTY.PARTY_NAME
, /* 6 */ BRANCHPARTY.PARTY_NAME
, /* 7 */ C.CHECK_ID
, /* 8 */ 'REFUND'
, /* 9 */ L2.MEANING
, /* 10 */ TO_CHAR(C.CHECK_NUMBER)
, /* 11 */ C.CHECK_NUMBER
, /* 12 */ C.CURRENCY_CODE
, /* 13 */ C.AMOUNT
, /* 14 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.AMOUNT
, ABA.CURRENCY_CODE
, C.AMOUNT
, C.AMOUNT * C.EXCHANGE_RATE)
, /* 15 */ DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 16 */ DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_AMOUNT)
, DECODE( DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)))
, DECODE(APH.TRANSACTION_TYPE
, 'PAYMENT CLEARING'
, APH.ACCOUNTING_DATE)
, /* 17 */ L1.DISPLAYED_FIELD
, /* 18 */ C.STATUS_LOOKUP_CODE
, /* 19 */ C.CHECK_DATE
, /* 20 */ C.CLEARED_DATE
, /* 21 */ C.FUTURE_PAY_DUE_DATE
, /* 22 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 23 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_DATE
, C.EXCHANGE_DATE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 24 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_RATE_TYPE
, C.EXCHANGE_RATE_TYPE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 25 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, GDCC.USER_CONVERSION_TYPE
, GDC.USER_CONVERSION_TYPE)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, /* 26 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_EXCHANGE_RATE
, C.EXCHANGE_RATE)
, DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 27 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_AMOUNT)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_CHARGES_BASE_AMOUNT))
, DECODE( C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, /* 28 */ DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_AMOUNT)
, ABA.CURRENCY_CODE
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_AMOUNT)
, DECODE( DECODE( C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_ERROR_BASE_AMOUNT))
, NVL(TO_CHAR(C.PAYMENT_INSTRUCTION_ID)
, C.CHECKRUN_NAME)
, /* 29 */ NVL(C.PAYMENT_INSTRUCTION_ID
, C.CHECKRUN_ID)
, /* 30 */ C.VENDOR_NAME
, /* 31 */ NULL
, /* 32 */ C.PAYMENT_METHOD_CODE
, /* LK.DISPLAYED_FIELD
, */ /* 33 */ C.VENDOR_NAME
, /* 34 */ TO_NUMBER(NULL)
, /* 35 */ C.VENDOR_ID
, /* 36*/ NULL
, /* 37 */ NULL
, /* 38 */ TO_NUMBER(NULL)
, /* 39 */ DECODE(CRE.STATUS_FLAG
, 'M'
, /* 40 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, C.CLEARED_AMOUNT
, ABA.CURRENCY_CODE
, C.CLEARED_AMOUNT
, NVL(C.CLEARED_BASE_AMOUNT
, C.CLEARED_AMOUNT)) - NVL(CLEARED_CHARGES_BASE_AMOUNT
, 0) -NVL(CLEARED_ERROR_BASE_AMOUNT
, 0))
, C.CREATION_DATE
, /* 41 */ C.CREATED_BY
, /* 42 */ C.LAST_UPDATE_DATE
, /* 43 */ C.LAST_UPDATED_BY
, /* 44 */ DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, B.BATCH_IDENTIFIER
, TO_CHAR(C.PAYMENT_INSTRUCTION_ID))
, /* 45 */ TO_NUMBER(DECODE(C.PAYMENT_INSTRUCTION_ID
, NULL
, 200
, 673))
, /* 46 */ 200
, /* 47 */ 0
, /* 48 */ ABA.CURRENCY_CODE
, /* 49 */ DECODE(C.CURRENCY_CODE
, SOB.CURRENCY_CODE
, 'FUNCTIONAL'
, /* 50 */ ABA.CURRENCY_CODE
, 'BANK'
, 'FOREIGN')
, NVL(GAC.AP_ASSET_CCID
, ABA.ASSET_CODE_COMBINATION_ID)
, /* 51 */ NULL
, /* 52 */ NULL
, /* 53 */ TO_NUMBER(NULL)
, /* 54 */ TO_NUMBER(NULL)
, /* 55 */ DECODE(C.PAYMENT_TYPE_FLAG
, 'R'
, 'REFUND'
, 'PAYMENT')
, /* 56 */ NULL
, /* 57 */ DECODE( DECODE(C.STATUS_LOOKUP_CODE
, /* 58 */ 'CLEARED BUT UNACCOUNTED'
, 'CLEARED'
, C.STATUS_LOOKUP_CODE)
, 'CLEARED'
, C.CLEARED_BASE_AMOUNT
, C.BASE_AMOUNT)
, TO_NUMBER(NULL)
, /* 59 */ TO_NUMBER(NULL)
, /* 60 */ NULL
, /* 61 */ TO_DATE(NULL)
, /* 62 */ C.ACTUAL_VALUE_DATE
, NULL
, BAU.BANK_ACCT_USE_ID
, TO_NUMBER(NULL)
, C.ORG_ID
FROM CE_BANK_ACCOUNTS ABA
, /*CE_BANK_ACCT_USES_OU_V BAU
, */ CE_BANK_ACCT_USES_ALL BAU
, CE_SECURITY_PROFILES_GT OU
, CE_GL_ACCOUNTS_CCID GAC
, /*CE_BANK_BRANCHES_V ABB
, */ HZ_PARTIES BANKPARTY
, HZ_PARTIES BRANCHPARTY
, AP_CHECKS_ALL C
, GL_DAILY_CONVERSION_TYPES GDC
, GL_DAILY_CONVERSION_TYPES GDCC
, CE_LOOKUPS L2
, AP_LOOKUP_CODES L1
, /* AP_LOOKUP_CODES LK
, */ GL_SETS_OF_BOOKS SOB
, CE_SYSTEM_PARAMETERS SYS
, AP_INV_SELECTION_CRITERIA_ALL B
, /*IBY_FD_PAYMENTS_V PAY
, */ AP_PAYMENT_HISTORY_ALL APH
, CE_STATEMENT_RECONCILS_ALL CRE
WHERE /*ABA.BANK_BRANCH_ID = ABB.BRANCH_PARTY_ID*/ ABA.BANK_BRANCH_ID = BRANCHPARTY.PARTY_ID
AND ABA.BANK_ID = BANKPARTY.PARTY_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 BAU.BANK_ACCT_USE_ID = GAC.BANK_ACCT_USE_ID
AND BAU.BANK_ACCT_USE_ID = C.CE_BANK_ACCT_USE_ID
AND C.PAYMENT_TYPE_FLAG = 'R'
AND GDC.CONVERSION_TYPE(+) = C.EXCHANGE_RATE_TYPE
AND GDCC.CONVERSION_TYPE(+) = C.CLEARED_EXCHANGE_RATE_TYPE /*AND LK.LOOKUP_CODE = C.PAYMENT_METHOD_CODE
AND LK.LOOKUP_TYPE = 'PAYMENT METHOD'*/
AND L2.LOOKUP_TYPE = 'TRX_TYPE'
AND L2.LOOKUP_CODE = 'REFUND'
AND L1.LOOKUP_TYPE = 'CHECK STATE'
AND L1.LOOKUP_CODE = C.STATUS_LOOKUP_CODE
AND C.STATUS_LOOKUP_CODE IN ('NEGOTIABLE'
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, 'VOIDED')
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, 'STOP INITIATED' )
, DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'NEGOTIABLE'
, 'CLEARED'))
, DECODE( CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'NEGOTIABLE'
, DECODE(SYS.SHOW_CLEARED_FLAG
, 'N'
, 'NEGOTIABLE'
, 'CLEARED BUT UNACCOUNTED')))
AND DECODE(DECODE(C.STATUS_LOOKUP_CODE
, 'CLEARED BUT UNACCOUNTED'
, 'CLEARED')
, 'CLEARED'
, NVL(C.CLEARED_DATE
, C.CHECK_DATE)
, C.CHECK_DATE) >=SYS.CASHBOOK_BEGIN_DATE
AND B.CHECKRUN_ID(+) = C.CHECKRUN_ID /*AND C.PAYMENT_ID = PAY.PAYMENT_ID (+)*/
AND APH.ORG_ID (+) = C.ORG_ID
AND APH.CHECK_ID(+) = C.CHECK_ID
AND APH.TRANSACTION_TYPE (+) LIKE 'PAYMENT '|| DECODE(CE_AUTO_BANK_CLEAR.GET_MANUAL_CLEARING
, 1
, 'UNCLEARING'
, '%CLEARING')
AND NOT EXISTS (SELECT NULL
FROM AP_PAYMENT_HISTORY APH2
WHERE APH2.CHECK_ID = C.CHECK_ID
AND APH2.TRANSACTION_TYPE IN ('PAYMENT CLEARING'
, 'PAYMENT UNCLEARING')
AND APH2.CREATION_DATE > APH.CREATION_DATE)
AND CRE.ORG_ID (+) = C.ORG_ID
AND CRE.REFERENCE_ID(+) = C.CHECK_ID
AND CRE.REFERENCE_TYPE(+) = 'PAYMENT'
AND NVL(CRE.STATUS_FLAG
, 'U') = 'U'
AND NVL(CRE.CURRENT_RECORD_FLAG
, 'Y') = 'Y'
AND SOB.SET_OF_BOOKS_ID = SYS.SET_OF_BOOKS_ID