Pool Performance with 'Import Standard Purchase Order' When Importing Large Number of Purchase Orders (Doc ID 2207674.1)

Last updated on NOVEMBER 25, 2016

Applies to:

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

Symptoms

It is experiencing poor performance with 'Import Standard Purchase Order' (PDOI) when importing 90000+ Standard Purchase Orders.

Following query was captured in Trace file which consumed most of the time

INSERT INTO PO_ONLINE_REPORT_TEXT_GT(ONLINE_REPORT_ID, LAST_UPDATE_LOGIN,
LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, LINE_NUM,
SHIPMENT_NUM, DISTRIBUTION_NUM, SEQUENCE, TEXT_LINE, MESSAGE_NAME) SELECT
:B5 , :B4 , :B3 , SYSDATE, :B3 , SYSDATE, 0, 0, 0, :B2 + ROWNUM, SUBSTR(:B1
,1,240), 'PO_PDOI_INVALID_VDR_CNTCT' FROM DUAL WHERE NOT EXISTS ( SELECT
'Y' FROM AP_SUPPLIER_CONTACTS PVC , HZ_PARTIES HP , HZ_RELATIONSHIPS HPR ,
HZ_PARTY_SITES HPS , HZ_ORG_CONTACTS HOC , HZ_PARTIES HP2 , AP_SUPPLIERS
APS, PO_HEADERS_GT POH WHERE PVC.PER_PARTY_ID = HP.PARTY_ID AND
PVC.REL_PARTY_ID = HP2.PARTY_ID AND PVC.ORG_CONTACT_ID =
HOC.ORG_CONTACT_ID(+) AND PVC.RELATIONSHIP_ID = HPR.RELATIONSHIP_ID AND
HPR.DIRECTIONAL_FLAG ='F' AND APS.VENDOR_ID = POH.VENDOR_ID AND NVL(
APS.VENDOR_TYPE_LOOKUP_CODE, 'DUMMY' ) <> 'EMPLOYEE' AND (
(PVC.PARTY_SITE_ID = HPS.PARTY_SITE_ID AND SYSDATE < NVL(
LEAST(NVL(HPR.END_DATE, TO_DATE('12/31/4712','MM/DD/RRRR')),
NVL(PVC.INACTIVE_DATE, TO_DATE('12/31/4712','MM/DD/RRRR'))), SYSDATE+1) AND
EXISTS (SELECT 1 FROM AP_SUPPLIER_SITES_ALL PVS WHERE PVS.PARTY_SITE_ID =
PVC.ORG_PARTY_SITE_ID AND PVS.VENDOR_ID = APS.VENDOR_ID)) OR
(PVC.ORG_PARTY_SITE_ID IS NULL AND PVC.VENDOR_SITE_ID IS NULL AND
HPR.OBJECT_ID = APS.PARTY_ID AND HPR.RELATIONSHIP_CODE = 'CONTACT_OF' AND
HPR.OBJECT_TYPE = 'ORGANIZATION' AND SYSDATE < NVL(HPR.END_DATE, SYSDATE+1)
) ) AND POH.VENDOR_CONTACT_ID = PVC.VENDOR_CONTACT_ID AND POH.PO_HEADER_ID= :B6 )

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