Skip to main content

Slowness/Application freeze in Shop orders material planning?

Has anyone encountered situations where the Shop orders material planning can intermittently start acting up and causing the Application to lock up or freeze in a post Apps10 UPD12 environment?

It happened in one internal customer environment when using NA Project Explorer after quite a bit of trying. shop material alloc table had over 10M rows.

The statement which locks up is as follows:

 

SELECT NVL(SUM(NVL(QTY_DEMAND,0)), 0), NVL(SUM(NVL(QTY_RESERVED,0)), 0) FROM (SELECT TRUNC(DATE_REQUIRED) DATE_REQUIRED, NVL(QTY_DEMAND,0) QTY_DEMAND, NVL(QTY_RESERVED,0) QTY_RESERVED FROM ORDER_SUPPLY_DEMAND_EXT WHERE CONTRACT = :B7 AND PART_NO = :B6 AND (CONFIGURATION_ID = :B5 OR :B5 IS NULL) AND ((:B4 = 'TRUE' AND PROJECT_ID = '*') OR (:B3 = 'TRUE' AND PROJECT_ID != '*' AND (PROJECT_ID = :B2 OR :B2 IS NULL) AND (ACTIVITY_SEQ = :B1 OR :B1 IS NULL))) UNION ALL SELECT TRUNC(DATE_REQUIRED) DATE_REQUIRED, (QTY_REQUIRED-QTY_ISSUED)*(-1) QTY_DEMAND, QTY_ASSIGNED*(-1) QTY_RESERVED FROM SHOP_MATERIAL_ALLOC_TAB WHERE ORDER_NO = :B11 AND RELEASE_NO = :B10 AND SEQUENCE_NO = :B9 AND LINE_ITEM_NO = :B8 AND (CONFIGURATION_ID = :B5 OR :B5 IS NULL) AND ((:B4 = 'TRUE' AND NVL(DECODE(SUPPLY_CODE, 'IO', '*', PROJECT_ID), '*') = '*') OR (:B3 = 'TRUE' AND NVL(DECODE(SUPPLY_CODE, 'IO', '*', PROJECT_ID), '*') != '*' AND (NVL(DECODE(SUPPLY_CODE, 'IO', '*', PROJECT_ID), '*') = :B2 OR :B2 IS NULL) AND (DECODE(SUPPLY_CODE, 'IO', TO_NUMBER(NULL), ACTIVITY_SEQ) = :B1 OR :B1 IS NULL))) AND ROWSTATE IN ('Planned','Released', 'Reserved', 'Issued', 'Parked') AND QTY_REQUIRED - QTY_ISSUED > 0 AND PART_OWNERSHIP = 'COMPANY OWNED' AND INCLUDE_AS_DEMAND = 'TRUE' UNION ALL SELECT TRUNC(:B12 ) DATE_REQUIRED, (QTY_REQUIRED-QTY_ISSUED) QTY_DEMAND, QTY_ASSIGNED QTY_RESERVED FROM SHOP_MATERIAL_ALLOC_TAB WHERE ORDER_NO = :B11 AND RELEASE_NO = :B10 AND SEQUENCE_NO = :B9 AND LINE_ITEM_NO = :B8 AND ROWSTATE IN ('Planned','Released', 'Reserved', 'Issued', 'Parked') AND QTY_REQUIRED - QTY_ISSUED > 0 AND PART_OWNERSHIP = 'COMPANY OWNED' AND INCLUDE_AS_DEMAND = 'TRUE') WHERE TRUNC(DATE_REQUIRED) <= NVL(:B12 , :B13 )

Hi @Roy Almeida

Please refer the Solution ID  294171 and it’s associated cases.

yes, thanks. I noticed that the customer that I am working on already has that patch. I found another UPD13 customer who exhibits the same slowness after continuous use of the screen. I could recreate the slowness when I started using NA Project Explorer to access IEE client. We have logged this to R&D and also asked System Engineers/DBA’s to examine the database/middleware settings.

Reply


Terms & ConditionsCookie settings