R12.2: LCM/AP: Performance Issue While Matching Invoice With Shipment Number - Huge LCM Shipments (Doc ID 2505515.1)

Last updated on APRIL 04, 2025

Applies to:

Oracle Payables - Version 12.2 and later
Oracle Landed Cost Management - Version 12.2 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.4 version

ACTUAL BEHAVIOR
---------------
Takes a long time matching LCM Shipments with AP Invoice.

EXPECTED BEHAVIOR
-----------------------
Should not take a long time matching LCM Shipments with AP Invoice.
No Performance issue should exist.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Payable responsibility-->Invoices-->Entry--> Invoice-->Query Invoice--> Match the Invoice with LCM shipment number

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot quickly match the invoice with LCM Shipment.

---------------------------------------------
Costliest SQL related to what the customer is describing is SQL ID: 6zwcv3ymmfvsv Plan Hash: 3961810344

The query is below :

SELECT AP_UTILITIES_PKG.AP_ROUND_CURRENCY(SUM (ESTIMATED_AMT) / :b1 , :b2 ) , AP_UTILITIES_PKG.AP_ROUND_CURRENCY(SUM (BILLED_AMT) / :b1 , :b2 ) FROM INL_CHARGE_ALLOCATIONS_V WHERE SHIP_LINE_ID = :b3 AND CHARGE_LINE_TYPE_ID = :b4 AND NVL(CHARGE_SUPPLIER_PARTY_ID , -999 ) = NVL(NVL(:b5 , CHARGE_SUPPLIER_PARTY_ID ) , -999 )

The issue is coming from INL_CHARGE_ALLOCATIONS_V.