R12: CE: Performance issue with General Ledger Reconciliation Report "CEXRECRE" (Doc ID 1573505.1)

Last updated on NOVEMBER 12, 2015

Applies to:

Oracle Cash Management - Version 12.1.1 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.
Performance issue with General Ledger Reconciliation Report "CEXRECRE"

Symptoms

                                                                                                                            

SQL ID: 7gbgb3jnudfxp

/*---- * BUG 5887373: * Redesigned query Q_AP_PAYMENTS to fetch payments
which: * 1. Have not hit GL cash, are not reconciled but appear on * a bank
statement. (query 1 of Union) * 2. Have not hit GL cash, but are reconciled.
(query 2 of Union) * 3. Have hit GL cash, but are not reconciled. (query 3 of
Union) ----*/ /*--++++++++++++++ QUERY 1 +++++++++++++++++*/ SELECT 'PAYMENT'
C_AP_TYPE, C.check_id C_AP_ID, DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE,
NVL(NVL(C.cleared_base_amount, C.base_amount), C.amount), C.amount)
C_AP_AMOUNT, C.vendor_name C_AP_SUPPLIER_NAME, AEH.accounting_date
C_AP_GL_DATE, C.check_date C_AP_PAYMENT_DATE, C.payment_method_code
C_AP_PAYMENT_METHOD, /* 13146206 - Changed */ C.check_number
C_AP_PAYMENT_NUMBER, C.currency_code C_AP_CURRENCY, C.amount
C_AP_TRANS_AMOUNT, C.status_lookup_code C_AP_STATUS,
FND_ACCESS_CONTROL_UTIL.get_org_name(C.org_id) C_ORG_NAME_AP FROM
ap_checks_all C, ce_bank_acct_uses_all BAU, ce_security_profiles_gt OU,
ce_ban k_accounts BA, ce_system_parameters SYS, xla_transaction_entities TRX,
xla_ae_headers AEH WHERE trunc(C.check_date) <= :C_AS_OF_DATE AND
C.ce_bank_acct_use_id = BAU.bank_acct_use_id AND C.org_id = BAU.org_id AND
BAU.bank_account_id = :P_BANK_ACCOUNT_ID AND BAU.org_id = OU.organization_id
AND OU.organization_type = 'OPERATING_UNIT' AND BAU.bank_account_id =
BA.bank_account_id AND BA.account_owner_org_id = SYS.legal_entity_id AND
TRX.application_id = 200 /* 13536461 - added */ AND TRX.ledger_id =
SYS.set_of_books_id /* 13536461 - added */ AND nvl(TRX.source_id_int_1, -99)
= C.check_id AND TRX.entity_code = 'AP_PAYMENTS' AND AEH.entity_id(+) =
TRX.entity_id AND nvl(AEH.ledger_id, SYS.set_of_books_id) =
SYS.set_of_books_id AND nvl(AEH.application_id, 200) = 200 /* 13536461 -
added */ /* Fetch latest accounted event before as_of_date */ AND
(AEH.event_id = ( SELECT MAX(event_id) FROM xla_events xe WHERE xe.entity_id
= TRX.entity_id AND xe.event_number = ( SELECT max(event_number) FROM
xla_events xe2 WHERE xe2.entity_id = xe.entity_id AND xe2.event_date <=
:C_AS_OF_DATE AND xe2.event_date >= SYS.cashbook_begin_date AND
xe2.event_status_code = 'P' AND xe2.event_type_code NOT in ('PAYMENT MATURITY
ADJUSTED', 'MANUAL PAYMENT ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING
ADJUSTED', 'MANUAL REFUND ADJUSTED', 'REFUND ADJUSTED' ))) /* 8241869 */ OR
AEH.event_id IS NULL) /* 8229671 Checks voided in current period to not
appear on report */ AND AEH.event_type_code <> 'PAYMENT CANCELLED' /*
12610508: Ignore 11i voided payments without PAYMENT CANCELLED events */ AND
EXISTS ( SELECT 1 FROM ap_payment_history_all H2 WHERE H2.check_id =
C.check_id AND H2.transaction_type LIKE Decode(C.void_date, null,
H2.transaction_type, '%CANCEL%') ) /* Ensure accounting event does not hit GL
Cash account */ AND NOT EXISTS ( SELECT NULL FROM xla_ae_lines AEL1 WHERE
ae_header_id = AEH.ae_header_id AND AEH.event_type_code NOT IN ('PAYMENT
UNCLEARED' , 'PAYMENT CANCELLED') AND AEL1.accounting_class_code = 'CASH' )
/* Check that payment is not reconciled */ AND NOT EXISTS( SELECT NULL FROM
ce_statement_reconcils_all CSR, ce_Statement_lines CSL, ce_statement_headers
CSH, ap_payment_history_all APH WHERE CSR.reference_id = C.check_id AND
CSR.current_record_flag = 'Y' AND CSR.reference_type = 'PAYMENT' AND
CSR.status_flag = 'M' AND CSR.statement_line_id = CSL.statement_line_id AND
CSL.statement_header_id = CSH.statement_header_id AND CSH.statement_date <=
:C_AS_OF_DATE -- 7716069 AND APH.payment_history_id = ( SELECT
max(payment_history_id) FROM ap_payment_history_all APH1 WHERE APH1.check_id
= C.check_id AND APH1.accounting_date = ( SELECT Max(APH2.accounting_date)
FROM ap_payment_history_all APH2 WHERE APH2.check_id = APH1.check_id AND
APH2.transaction_type IN ( 'PAYMENT CLEARING', 'PAYMENT CREATED', 'REFUND
RECORDED', /* 12534616 - Added*/ 'PAYMENT MATURED') /* 12534616 - Added*/ AND
APH2.accounting_date <= :C_AS_OF_DATE ) ) ) /* Find a matchi ng payment in
Bank Statment */ AND EXISTS( SELECT NULL FROM ce_statement_lines CSL,
ce_statement_headers CSH, ce_transaction_codes_v COD WHERE CSL.trx_type =
'DEBIT' AND CSL.status != 'RECONCILED' AND CSL.trx_code = COD.trx_code AND
COD.bank_account_id = BA.bank_account_id AND COD.reconcile_flag = 'AP' AND
CSL.bank_trx_number = to_char(C.check_number) AND CSH.statement_header_id =
CSL.statement_header_id AND CSH.bank_account_id = BA.bank_account_id AND
CSH.statement_date <= :C_AS_OF_DATE /* 7716069 */ AND CSH.statement_date >=
SYS.cashbook_begin_date /* 7716069 */ ) UNION /*++++++++++++++++ QUERY 2
+++++++++++++++++++++*/ SELECT 'PAYMENT' C_AP_TYPE, C.check_id C_AP_ID,
DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE, NVL(NVL(C.cleared_base_amount,
C.base_amount) , C.amount), C.amount) C_AP_AMOUNT, C.vendor_name
C_AP_SUPPLIER_NAME, AEH.accounting_date C_AP_GL_DATE, C.check_date
C_AP_PAYMENT_DATE, C.payment_method_code C_AP_PAYMENT_METHOD, /* 13146206 -
Changed */ C.check_nu mber C_AP_PAYMENT_NUMBER, C.currency_code
C_AP_CURRENCY, C.amount C_AP_TRANS_AMOUNT, C.status_lookup_code C_AP_STATUS,
FND_ACCESS_CONTROL_UTIL.get_org_name(C.org_id) C_ORG_NAME_AP FROM
ap_checks_all C, ce_bank_acct_uses_all BAU, ce_security_profiles_gt OU,
ce_bank_accounts BA, ce_system_parameters SYS, xla_transaction_entities TRX,
xla_ae_headers AEH WHERE trunc(C.check_date)<= :C_AS_OF_DATE AND
C.ce_bank_acct_use_id = BAU.bank_acct_use_id AND C.org_id = BAU.org_id AND
BAU.bank_account_id = :P_BANK_ACCOUNT_ID AND BAU.org_id = OU.organization_id
AND OU.organization_type = 'OPERATING_UNIT' AND BAU.bank_account_id =
BA.bank_account_id AND BA.account_owner_org_id = SYS.legal_entity_id AND
TRX.application_id = 200 /* 13536461 - added */ AND TRX.ledger_id =
SYS.set_of_books_id /* 13536461 - added */ AND nvl(TRX.source_id_int_1, -99)
= C.check_id AND TRX.entity_code = 'AP_PAYMENTS' AND AEH.entity_id(+) =
TRX.entity_id AND nvl(AEH.ledger_id, SYS.set_of_books_id) = SYS.set_of_books
_id AND nvl(AEH.application_id , 200)= 200 /* 13536461 - added */ /* Fetch
latest accounted event before as_of_date */ AND (AEH.event_id = ( SELECT
MAX(event_id) FROM xla_events xe WHERE xe.entity_id = TRX.entity_id AND
xe.event_number = ( SELECT Max(event_number) from xla_events xe2 where
xe2.entity_id = xe.entity_id AND xe2.event_date <= :C_AS_OF_DATE AND
xe2.event_date >= SYS.cashbook_begin_date AND xe2.event_status_code = 'P' AND
xe2.event_type_code NOT in ('PAYMENT MATURITY ADJUSTED', 'MANUAL PAYMENT
ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED', 'MANUAL REFUND
ADJUSTED', 'REFUND ADJUSTED' ))) -- 8241869 OR AEH.event_id IS NULL) /*
8229671 Checks voided in current period to not appear on report */ AND
AEH.event_type_code <> 'PAYMENT CANCELLED' /* 12610508: Ignore 11i voided
payments without PAYMENT CANCELLED events */ AND EXISTS ( SELECT 1 FROM
ap_payment_history_all H2 WHERE H2.check_id = C.check_id AND
H2.transaction_type LIKE Decode(C.void_date, n ull, H2.transaction_type,
'%CANCEL%') ) /* Ensure accounting event does not hit GL Cash account */ AND
NOT EXISTS ( SELECT NULL FROM xla_ae_lines AEL1 WHERE ae_header_id =
AEH.ae_header_id AND AEH.event_type_code NOT IN ('PAYMENT UNCLEARED',
'PAYMENT CANCELLED') AND AEL1.accounting_class_code = 'CASH' ) /* Check that
payment is reconciled */ AND EXISTS( SELECT NULL FROM
ce_statement_reconcils_all CSR, ce_Statement_lines CSL, ce_statement_headers
CSH, ap_payment_history_all APH WHERE CSR.reference_id = C.check_id AND
CSR.current_record_flag = 'Y' AND CSR.reference_type = 'PAYMENT' AND
CSR.status_flag = 'M' AND CSR.statement_line_id = CSL.statement_line_id AND
CSL.statement_header_id = CSH.statement_header_id AND CSH.statement_date <=
:C_AS_OF_DATE /* 7716069 */ AND CSH.statement_date >= SYS.cashbook_begin_date
/* 7716069 */ AND APH.payment_history_id = ( SELECT max(payment_history_id)
FROM ap_payment_history_all APH1 WHERE APH1.check_id = C.check_id AND
aph1.accounting_date = ( SELECT Max(aph2.accounting_date) FROM
ap_payment_history_all aph2 WHERE aph2.check_id = aph1.check_id AND
APH2.transaction_type IN ( 'PAYMENT CLEARING', 'PAYMENT CREATED', 'REFUND
RECORDED', /* 12534616 - Added*/ 'PAYMENT MATURED') /* 12534616 - Added*/ AND
APH2.accounting_date <= :C_AS_OF_DATE) ) ) UNION --++++++++++++++++ QUERY 3
+++++++++++++++++++++ SELECT 'PAYMENT' C_AP_TYPE, C.check_id C_AP_ID,
-1*DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE,
NVL(NVL(C.cleared_base_amount, C.base_amount) , C.amount), C.amount)
C_AP_AMOUNT, C.vendor_name C_AP_SUPPLIER_NAME, AEH.accounting_date
C_AP_GL_DATE, C.check_date C_AP_PAYMENT_DATE, C.payment_method_code
C_AP_PAYMENT_METHOD, /* 13146206 - Changed */ C.check_number
C_AP_PAYMENT_NUMBER, C.currency_code C_AP_CURRENCY, C.amount
C_AP_TRANS_AMOUNT, C.status_lookup_code C_AP_STATUS,
FND_ACCESS_CONTROL_UTIL.get_org_name(C.org_id) C_ORG_NAME_AP FROM
ap_checks_all C, ce_bank_acct_uses_all BAU, ce_security_profiles_gt OU,
ce_ban k_accounts BA, ce_system_parameters SYS, xla_transaction_entities TRX,
xla_ae_headers AEH, xla_ae_lines AEL WHERE trunc(C.check_date) <=
:C_AS_OF_DATE AND C.ce_bank_acct_use_id = BAU.bank_acct_use_id AND C.org_id =
BAU.org_id AND BAU.bank_account_id = :P_BANK_ACCOUNT_ID AND BAU.org_id =
OU.organization_id AND OU.organization_type = 'OPERATING_UNIT' AND
BAU.bank_account_id = BA.bank_account_id AND BA.account_owner_org_id =
SYS.legal_entity_id AND nvl(TRX.source_id_int_1, -99) = C.check_id AND
TRX.entity_code = 'AP_PAYMENTS' AND TRX.application_id = 200 /* 13536461 -
added */ AND AEH.entity_id = TRX.entity_id AND AEH.application_id = 200 /*
13536461 - added */ /* Fetch latest accounted event before as_of_date and
check that it hits the GL Cash Account */ AND AEH.event_type_code IN (
'PAYMENT CLEARED', 'PAYMENT CREATED', 'REFUND RECORDED', 'PAYMENT MATURED')
/* 12534616 - Added */ AND nvl(AEH.ledger_id, SYS.set_of_books_id) =
SYS.set_of_books_id AND AEH.event_id = ( SELECT MAX(e vent_id) FROM
xla_events xe WHERE xe.entity_id = TRX.entity_id AND xe.event_number = (
SELECT Max(event_number) FROM xla_events xe2 WHERE xe2.entity_id =
xe.entity_id AND xe2.event_date <= :C_AS_OF_DATE AND xe2.event_date >=
SYS.cashbook_begin_date AND xe2.event_status_code = 'P' AND
xe2.event_type_code NOT in ('PAYMENT MATURITY ADJUSTED', 'MANUAL PAYMENT
ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED', 'MANUAL REFUND
ADJUSTED', 'REFUND ADJUSTED' ))) /* 8241869 */ AND AEH.ae_header_id =
AEL.ae_header_id AND AEL.application_id = 200 /* 13536461 - added */ AND
AEL.accounting_class_code = 'CASH' AND EXISTS ( SELECT 1 FROM
ap_payment_history_all H2 WHERE H2.check_id = C.check_id AND
H2.transaction_type LIKE Decode(C.void_date, null, H2.transaction_type,
'%CANCEL%') ) /* Check that payment is not reconciled */ AND NOT EXISTS(
SELECT NULL FROM ce_statement_reconcils_all CSR, ce_Statement_lines CSL,
ce_statement_headers CSH, ap_payment_history_all APH WHERE CSR.ref erence_id
= C.check_id AND CSR.current_record_flag = 'Y' AND CSR.reference_type =
'PAYMENT' AND CSR.status_flag = 'M' AND CSR.statement_line_id =
CSL.statement_line_id AND CSL.statement_header_id = CSH.statement_header_id
AND CSH.statement_date <= :C_AS_OF_DATE /* 7716069 */ AND
APH.payment_history_id = ( SELECT max(payment_history_id) FROM
ap_payment_history_all APH1 WHERE APH1.check_id = C.check_id AND
APH1.accounting_date = ( SELECT Max(aph2.accounting_date) FROM
ap_payment_history_all aph2 WHERE aph2.check_id = aph1.check_id AND
APH2.transaction_type IN ( 'PAYMENT CLEARING', 'PAYMENT CREATED', 'REFUND
RECORDED', 'PAYMENT MATURED') /* 12534616 - Added*/ AND APH2.accounting_date
<= :C_AS_OF_DATE) ) ) ORDER BY C_AP_GL_DATE NULLS FIRST

or

SQL ID = a7m664z3v2dy7

SELECT /*+ index(aeh XLA_AE_HEADERS_N3) */ 'PAYMENT' C_AP_TYPE, C.check_id C_AP_ID, DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE, NVL(NVL(C.cleared_base_amount, C.base_amount) , C.amount), C.amount) C_AP_AMOUNT, C.vendor_name C_AP_SUPPLIER_NAME, AEH.accounting_date C_AP_GL_DATE, C.check_date C_AP_PAYMENT_DATE, C.payment_method_code C_AP_PAYMENT_METHOD, /* 13146206 - Changed */ C.check_number C_AP_PAYMENT_NUMBER, C.currency_code C_AP_CURRENCY, C.amount C_AP_TRANS_AMOUNT, C.status_lookup_code C_AP_STATUS, FND_ACCESS_CONTROL_UTIL.get_org_name(C.org_id) C_ORG_NAME_AP
FROM ap_checks_all C, ce_bank_acct_uses_all BAU, ce_security_profiles_gt OU, ce_bank_accounts BA, ce_system_parameters SYS, xla_transaction_entities TRX, xla_ae_headers AEH
WHERE C.check_date <= trunc(:C_AS_OF_ DATE) + 1 - 1/24/60/60 /* 17078656: performance change */ AND C.ce_bank_acct_use_id = BAU.bank_acct_use_id AND C.org_id = BAU.org_id AND BAU.bank_account_id = :P_BANK_ACCOUNT_ID AND BAU.org_id = OU.organization_id AND OU.organization_type = 'OPERATING_UNIT' AND BAU.bank_account_id = BA.bank_account_id AND BA.account_owner_org_id = SYS.legal_entity_id AND TRX.application_id = 200 /* 13536461 - added */ AND TRX.ledger_id = SYS.set_of_books_id /* 13536461 - added */ AND nvl(TRX.source_id_int_1, -99) = C.check_id AND TRX.entity_code = 'AP_PAYMENTS' AND AEH.entity_id(+) = TRX.entity_id AND nvl(AEH.application_id , 200) = 200 /* 13536461 - added */ AND nvl(AEH.ledger_id, SYS.set_of_books_id) = SYS.set_of_books_id /* 17078656 - nvl added for outer join */ AND nvl(AEH.event_type_code, 'X') NOT IN ('PAYMENT CANCELLED', 'REFUND CANCELLED') /* Fetch latest accounted event before as_of_date */ AND (AEH.event_id = ( SELECT MAX(event_id)
FROM xla_events xe WHERE xe.application_id = 200 /* 14698507 - Added */ AND xe.entity_id = TRX.entity_id AND xe.event_number = ( SELECT Max(event_number)
from xla_events xe2 where xe2.application_id = 200 /* 14698507 - Added */ AND xe2.entity_id = xe.entity_id AND xe2.event_date <= :C_AS_OF_DATE AND xe2.event_date >= SYS.cashbook_begin_date AND xe2.event_status_code = 'P' AND xe2.event_type_code NOT in ( 'PAYMENT MATURITY ADJUSTED', 'MANUAL PAYMENT ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED', 'MANUAL REFUND ADJUSTED', 'REFUND ADJUSTED'))) /* 8241869 */ OR AEH.event_id IS NULL) /* 8229671 Checks voided in current period to not appear on report */ /* 14698507 Voided checks event can be 'REFUND CANCELLED' */ /* 12610508: Ignore 11i voided payments without PAYMENT CANCELLED events */ AND EXISTS ( SELECT 1
FROM ap_payment_history_all H2 WHERE H2.check_id = C.check_id AND H2.transaction_type LIKE Decode(C.void_date, null, H2.transaction_type, '%CANCEL%')) /* Ensure accounting event does not hit GL Cash account */ AND NOT EX ISTS ( /* 17078656: Added optimizer hint */ SELECT /*+ push_subq no_unnest */ NULL
FROM xla_ae_lines AEL1 WHERE AEL1.application_id = 200 /* 14698507 - Added */ AND AEL1.ae_header_id = AEH.ae_header_id AND AEH.event_type_code NOT IN ('PAYMENT UNCLEARED', 'PAYMENT CANCELLED', 'REFUND CANCELLED') /* 14698507 added*/ AND AEL1.accounting_class_code = 'CASH') /* Check that payment is reconciled */ AND EXISTS( /* 17078656: Removed join to APH */ SELECT NULL
FROM ce_statement_reconcils_all CSR, ce_Statement_lines CSL, ce_statement_headers CSH WHERE CSR.reference_id = C.check_id AND CSR.current_record_flag = 'Y' AND CSR.reference_type = 'PAYMENT' AND CSR.status_flag = 'M' AND CSR.statement_line_id = CSL.statement_line_id AND CSL.statement_header_id = CSH.statement_header_id AND CSH.statement_date <= :C_AS_OF_DATE /* 7716069 */ AND CSH.statement_date >= SYS.cashbook_begin_date /* 7716069 */ ) UNION ALL /* 17078656: Added optimizer hint */ SELECT /*+ index(aeh XLA_AE_HEADERS_N3) */ 'PA YMENT' C_AP_TYPE, C.check_id C_AP_ID, -1*DECODE(:C_BANK_CURR_DSP, :C_GL_CURRENCY_CODE, NVL(NVL(C.cleared_base_amount, C.base_amount) , C.amount), C.amount) C_AP_AMOUNT, C.vendor_name C_AP_SUPPLIER_NAME, AEH.accounting_date C_AP_GL_DATE, C.check_date C_AP_PAYMENT_DATE, C.payment_method_code C_AP_PAYMENT_METHOD, /* 13146206 - Changed */ C.check_number C_AP_PAYMENT_NUMBER, C.currency_code C_AP_CURRENCY, C.amount C_AP_TRANS_AMOUNT, C.status_lookup_code C_AP_STATUS, FND_ACCESS_CONTROL_UTIL.get_org_name(C.org_id) C_ORG_NAME_AP
FROM ap_checks_all C, ce_bank_acct_uses_all BAU, ce_security_profiles_gt OU, ce_bank_accounts BA, ce_system_parameters SYS, xla_transaction_entities TRX, xla_ae_headers AEH
WHERE C.check_date <= trunc(:C_AS_OF_DATE) + 1 - 1/24/60/60 /* 17078656: performance change */ AND C.ce_bank_acct_use_id = BAU.bank_acct_use_id AND C.org_id = BAU.org_id AND BAU.bank_account_id = :P_BANK_ACCOUNT_ID AND BAU.org_id = OU.organization_id AND OU.organization_type = ' OPERATING_UNIT' AND BAU.bank_account_id = BA.bank_account_id AND BA.account_owner_org_id = SYS.legal_entity_id AND nvl(TRX.source_id_int_1, -99) = C.check_id AND TRX.entity_code = 'AP_PAYMENTS' AND TRX.application_id = 200 /* 13536461 - added */ AND TRX.ledger_id = SYS.set_of_books_id /* 17078656 - added */ AND AEH.entity_id = TRX.entity_id AND AEH.application_id = 200 /* 13536461 - added */ /* Fetch latest accounted event before as_of_date and check that it hits the GL Cash Account */ AND AEH.event_type_code IN ( 'PAYMENT CLEARED', 'PAYMENT CREATED', 'REFUND RECORDED', 'PAYMENT MATURED') /* 12534616 - Added */ AND AEH.ledger_id = SYS.set_of_books_id /* 17078656 - nvl not required here */ AND AEH.event_id = ( SELECT MAX(event_id) FROM xla_events xe WHERE xe.application_id = 200 /* 14698507 - added */ AND xe.entity_id = TRX.entity_id AND xe.event_number = ( SELECT Max(event_number)
FROM xla_events xe2 WHERE xe2.application_id = 200 /* 14698507 - added */ AND xe2.entity_id = xe.entity _id AND xe2.event_date <= :C_AS_OF_DATE AND xe2.event_date >= SYS.cashbook_begin_date AND xe2.event_status_code = 'P' AND xe2.event_type_code NOT in ( 'PAYMENT MATURITY ADJUSTED', 'MANUAL PAYMENT ADJUSTED', 'PAYMENT ADJUSTED', 'PAYMENT CLEARING ADJUSTED', 'MANUAL REFUND ADJUSTED', 'REFUND ADJUSTED'))) /* 8241869 */ AND exists ( select 'x'
from xla_ae_lines AEL where AEH.ae_header_id = AEL.ae_header_id AND AEL.application_id = 200 /* 13536461 - added */ AND AEL.accounting_class_code = 'CASH' ) AND EXISTS ( SELECT 1
FROM ap_payment_history_all H2 WHERE H2.check_id = C.check_id AND H2.transaction_type LIKE Decode(C.void_date, null, H2.transaction_type, '%CANCEL%')) /* Check that payment is not reconciled */ AND NOT EXISTS( /* 17078656: Added optimizer hint */ /* 17078656: Removed join to APH */ SELECT /*+ PUSH_SUBQ NO_UNNEST */ NULL
FROM ce_statement_reconcils_all CSR, ce_Statement_lines CSL, ce_statement_headers CSH WHERE CSR.reference_id = C.check_id AND CSR.reference_ty pe = 'PAYMENT' AND CSR.status_flag = 'M' AND CSR.current_record_flag = 'Y' AND CSR.statement_line_id = CSL.statement_line_id AND CSL.statement_header_id = CSH.statement_header_id AND CSH.statement_date <= :C_AS_OF_DATE /* 7716069 */) ORDER BY C_AP_GL_DATE NULLS FIRST

 

Changes

 None

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms