My Oracle Support Banner

Performance Canceling One Shipment Line on a Large PO (Doc ID 2436739.1)

Last updated on AUGUST 17, 2018

Applies to:

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

Symptoms

On : 12.1.3 version, Purchasing Processes

ACTUAL BEHAVIOR
---------------
Performance of po_document_control_pub.control_document

Cancel one PO shipment through po_document_control_pub.control_document takes from 18 to 90 seconds if PO have large amount of shipments.

Trace shipments being updated instead of just the one being canceled

UPDATE PO_LINE_LOCATIONS POLL SET POLL.SHIPMENT_CLOSED_DATE =
DECODE(POLL.CLOSED_CODE, 'CLOSED', NVL(POLL.SHIPMENT_CLOSED_DATE,
PO_ACTIONS.GET_CLOSURE_DATES('CLOSE', POLL.LINE_LOCATION_ID)), NULL) ,
POLL.CLOSED_FOR_RECEIVING_DATE = DECODE(POLL.CLOSED_CODE, 'CLOSED FOR
RECEIVING', NVL(POLL.CLOSED_FOR_RECEIVING_DATE,
PO_ACTIONS.GET_CLOSURE_DATES('RECEIVE CLOSE', POLL.LINE_LOCATION_ID)),
'CLOSED FOR INVOICE', NULL, 'CLOSED', NVL(POLL.CLOSED_FOR_RECEIVING_DATE,
PO_ACTIONS.GET_CLOSURE_DATES('RECEIVE CLOSE', POLL.LINE_LOCATION_ID)),
'OPEN', NULL) , POLL.CLOSED_FOR_INVOICE_DATE = DECODE(POLL.CLOSED_CODE,
'CLOSED FOR RECEIVING', NULL, 'CLOSED FOR INVOICE',
NVL(POLL.CLOSED_FOR_INVOICE_DATE, PO_ACTIONS.GET_CLOSURE_DATES('INVOICE
CLOSE', POLL.LINE_LOCATION_ID)), 'CLOSED', NVL(POLL.CLOSED_FOR_INVOICE_DATE,
PO_ACTIONS.GET_CLOSURE_DATES('INVOICE CLOSE', POLL.LINE_LOCATION_ID)),
'OPEN', NULL) , POLL.LAST_UPDATE_DATE = SYSDATE , POLL.LAST_UPDATED_BY =
:B3 , POLL.LAST_UPDATE_LOGIN = :B2
WHERE
POLL.LINE_LOCATION_ID = :B1 AND NVL(POLL.CLOSED_CODE, 'OPEN') <> 'FINALLY
CLOSED'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 6.85 7.99 215 69967 335104 10896
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 6.85 7.99 215 69967 335104 10896

 

Changes

 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.