Purchase Order Form Hangs When Approving All Purchase Orders (Doc ID 1352600.1)

Last updated on JUNE 02, 2016

Applies to:

Oracle Purchasing - Version: 11.5.10.2 and later   [Release: 11.5 and later ]
Information in this document applies to any platform.

Symptoms

When submitting a Purchase Order for approval, the system hangs and the user has to kill the Oracle Applications session after several minutes.

STEPS

The issue can be reproduced at will with the following steps:
1. Open the Purchase Order form
2. Enter appropriate data
3. Choose the Approve button, issue occurs

The Trace File shows this offending sql:

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
:B9 , :B8 , :B7 , SYSDATE, :B7 , SYSDATE, POL.LINE_NUM, PLL.SHIPMENT_NUM, 0,
:B6 + ROWNUM, SUBSTR(:B5 ||:B3 ||POL.LINE_NUM||:B3 || :B4 ||:B3
||PLL.SHIPMENT_NUM||:B3 ||:B2 ,1,240), 'PO_SUB_ITEM_NOT_APPROVED' FROM
MTL_SYSTEM_ITEMS MSI, PO_LINE_LOCATIONS_GT PLL, PO_LINES_GT POL,
PO_HEADERS_GT POH, FINANCIALS_SYSTEM_PARAMETERS FSP WHERE POH.PO_HEADER_ID =
:B1 AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND PLL.PO_LINE_ID(+) =
POL.PO_LINE_ID AND PLL.PO_RELEASE_ID IS NULL AND MSI.ORGANIZATION_ID =
PLL.SHIP_TO_ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID AND
POL.ITEM_ID IS NOT NULL AND NVL(PLL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED'
AND NVL(POL.CANCEL_FLAG,'N') = 'N' AND NVL(PLL.CANCEL_FLAG,'N') = 'N' AND
NVL(MSI.MUST_USE_APPROVED_VENDOR_FLAG,'N') = 'Y' AND NOT EXISTS (SELECT
SUM(DECODE(ASR.ALLOW_ACTION_FLAG, 'Y', 1, -1000)) FROM
PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR WHERE
ASL.USING_ORGANIZATION_ID IN (PLL.SHIP_TO_ORGANIZATION_ID, -1) AND
ASL.VENDOR_ID = POH.VENDOR_ID AND NVL(ASL.VENDOR_SITE_ID,
POH.VENDOR_SITE_ID) = POH.VENDOR_SITE_ID AND ASL.ITEM_ID = POL.ITEM_ID AND
ASL.ASL_STATUS_ID = ASR.STATUS_ID AND ASR.BUSINESS_RULE = '1_PO_APPROVAL'
HAVING SUM(DECODE(ASR.ALLOW_ACTION_FLAG, 'Y', 1, -1000)) > 0 UNION ALL
SELECT SUM(DECODE(ASR.ALLOW_ACTION_FLAG, 'Y', 1, -1000)) FROM
PO_APPROVED_SUPPLIER_LIS_VAL_V ASL, PO_ASL_STATUS_RULES ASR WHERE
ASL.USING_ORGANIZATION_ID IN (PLL.SHIP_TO_ORGANIZATION_ID, -1) AND
ASL.VENDOR_ID = POH.VENDOR_ID AND NVL(ASL.VENDOR_SITE_ID,
POH.VENDOR_SITE_ID) = POH.VENDOR_SITE_ID AND ASL.ITEM_ID IS NULL AND NOT
EXISTS (SELECT ASL1.ASL_ID FROM PO_APPROVED_SUPPLIER_LIS_VAL_V ASL1 WHERE
ASL1.ITEM_ID = POL.ITEM_ID AND ASL1.USING_ORGANIZATION_ID IN
(PLL.SHIP_TO_ORGANIZATION_ID, -1)) AND ASL.CATEGORY_ID IN (SELECT
MIC.CATEGORY_ID FROM MTL_ITEM_CATEGORIES MIC WHERE MIC.INVENTORY_ITEM_ID =
POL.ITEM_ID AND MIC.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID) AND
ASL.ASL_STATUS_ID = ASR.STATUS_ID AND ASR.BUSINESS_RULE = '1_PO_APPROVAL'
HAVING SUM(DECODE(ASR.ALLOW_ACTION_FLAG, 'Y', 1, -1000)) > 0)

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