PO Create Documents Workflow Creation Process Has Performance Issue (Doc ID 1329519.1)

Last updated on AUGUST 19, 2015

Applies to:

Oracle Purchasing - Version 11.5.10.2 and later
Information in this document applies to any platform.

Symptoms

When creating Purchase Orders via the PO Create Documents workflow there is a performance issue encountered.

The query that is performing poorly is :

SELECT PLI.ROWID,
PLI.ITEM_ID,
PHI.VENDOR_ID,
PHI.VENDOR_SITE_ID,
PDI.DESTINATION_ORGANIZATION_ID
FROM PO_LINES_INTERFACE PLI, PO_HEADERS_INTERFACE PHI, PO_DISTRIBUTIONS_INTERFACE PDI
WHERE PHI.INTERFACE_HEADER_ID = :B1
AND PHI.INTERFACE_HEADER_ID = PLI.INTERFACE_HEADER_ID
AND PDI.INTERFACE_DISTRIBUTION_ID = (SELECT MIN(PDI2.INTERFACE_DISTRIBUTION_ID)
FROM PO_DISTRIBUTIONS_INTERFACE PDI2
WHERE PDI2.INTERFACE_LINE_ID = PLI.INTERFACE_LINE_ID)
AND PLI.ITEM_ID IS NOT NULL
AND PHI.VENDOR_ID IS NOT NULL
AND PLI.VENDOR_PRODUCT_NUM IS NULL

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Applied required patches like "<Patch 8791241> Procurement Release 11.5.10 Rollup Patch 12"
2. Create Requisitions such that should be created into POs automatically via the PO Create Documents Workflow
3. When the Workflow Background Process runs the process takes too long to complete because of the higher number of transactions each taking too long because of the above query.

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