INVDVDSD Item Supply Demand Form Performance/Error: APP-INV-05647: The Remote Call Failed No Manager Was Available And/Or APP-INV-05649 The Remote Call failed due to a time out After All Rollup Patches Applied (Doc ID 1624856.1)

Last updated on APRIL 03, 2025

Applies to:

Symptoms

On : 11.5.10.2 version, Item Supply Demand

Users used to not be on any INV_SD rollup patches. They applied INV_SD Rollup #7 Patch 7350872 on 12-OCT-2013. In Test and PROD they're running into severe performance problems where many times, but not all, the item supply demand form will query for 5 minutes (ie 300 seconds as per profile INV: RPC Timeout). It should be noted that sometimes the error does occur after 8-10 seconds. See errors below and related times for each. This can happen for any item in any organization so it's not isolated. We abandoned Test as it appeared to us that they had severe I/O problem and went back to working in PROD

Please note RDBMS is 11.2.0.3.0 and O/S is IBM AIX

File versions:

inldsd.ppc 115.58.115100.19 - latest is .21 but for different issues - confirms they're on INVSD RUP#7 though
inudsd.ppc 115.38.115100.8 - latest is .11 - same as above

There seems to be no problem for the most part in the morning using the form. Problem seems to happen more often than not in the afternoon


Here are the two errors they run into:

"APP-INV-05647: The Remote Call failed because no manager was available." This occurs after 5 minutes and is the error they see most of the time
or
APP-INV-05649 The Remote Call failed due to a time out - This doesn't occur as much and was more related to Test than PROD

Things we tried:

1. Increasing INV Remote Procedure Manager processes from 3 to 5 to 10 to 15 to 20 and bouncing they still run into the problem more often than not. They say that sometimes the form works in the morning but is then a problem for the rest of the afternoon

2. We found relinking was done on INCTM but we did it again and confirmed the key file versions but that didn't help

3. We found 6379665 records with line_status = 5 (closed) in table mtl_txn_request_lines. We got that down to under 100K and retested but that didn't help. We also confirmed they have all the indexes on this same table (U1, U2, N1 thru N10). We also rebuilt the indexes and gathered table statistics at 30% but it did not help

4. We found no useful information - in fact very little was written in INV debug log.

5. Running the diagnostic from Note 404459.1 gets hung and will not complete nor provide output as far as the output. We did run the diagnostic with performance = Yes and found it hanging on this SQL

SQL ID: 5q6gb1byk8m9f Plan Hash: 4048796650

SELECT D.RESERVATION_TYPE, DECODE(D.DEMAND_SOURCE_TYPE,2,
  DECODE(D.RESERVATION_TYPE,1,2,3,23,9),8,DECODE( D.RESERVATION_TYPE,1,21,22),
  D.DEMAND_SOURCE_TYPE) , DECODE(D.DEMAND_SOURCE_TYPE,8,2,
  D.DEMAND_SOURCE_TYPE) , D.DEMAND_SOURCE_HEADER_ID , 1 ,
  -1*(D.PRIMARY_UOM_QUANTITY-D.TOTAL_RESERVATION_QUANTITY-D.COMPLETED_QUANTIT
  Y) , TO_NUMBER(TO_CHAR( DECODE(NVL(D.MFG_LEAD_TIME,0), 0, C.PRIOR_DATE,
  DECODE( I.BOM_ITEM_TYPE, 1, C.PRIOR_DATE, MRP_CALENDAR.DATE_OFFSET (
  D.ORGANIZATION_ID, 1, C.PRIOR_DATE, -1*(D.MFG_LEAD_TIME)))) , 'J')) ,
  CHARTOROWID('0') , V.INVENTORY_ITEM_ID , V.ORGANIZATION_ID ,
  DECODE(D.RESERVATION_TYPE, 2, TO_NUMBER(:B1 ), C.PRIOR_SEQ_NUM) ,
  D.DEMAND_SOURCE_NAME
FROM
 MTL_GROUP_ITEM_ATPS_VIEW V, MTL_PARAMETERS P , MTL_SYSTEM_ITEMS I ,
  MTL_ATP_RULES R , BOM_CALENDAR_DATES C , MRP_DEMAND_OM_RESERVATIONS_V D
  WHERE D.OPEN_FLAG = 'Y' AND D.RESERVATION_TYPE != 2 AND
  R.PAST_DUE_DEMAND_CUTOFF_FENCE IS NULL AND D.ORGANIZATION_ID =
  V.ORGANIZATION_ID AND D.PRIMARY_UOM_QUANTITY >
  (D.TOTAL_RESERVATION_QUANTITY+ D.COMPLETED_QUANTITY) AND
  D.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID AND ( D.VISIBLE_DEMAND_FLAG = 'Y'
  OR ( NVL(D.VISIBLE_DEMAND_FLAG,'N')='N' AND D.ATO_LINE_ID IS NOT NULL AND
  NOT EXISTS ( SELECT NULL FROM OE_ORDER_LINES_ALL OOL, MTL_DEMAND MD WHERE
  TO_CHAR(OOL.LINE_ID) =MD.DEMAND_SOURCE_LINE AND OOL.ATO_LINE_ID =
  D.ATO_LINE_ID AND OOL.ITEM_TYPE_CODE = 'CONFIG' AND MD.RESERVATION_TYPE IN
  (2,3) ) AND NOT EXISTS ( SELECT 1 FROM WIP_FLOW_SCHEDULES,
  OE_ORDER_LINES_ALL OOL WHERE DEMAND_SOURCE_LINE = TO_CHAR(OOL.LINE_ID) AND
  SCHEDULED_FLAG = 1 AND ( STATUS = 1 OR ( STATUS = 2 AND QUANTITY_COMPLETED
  > 0 ) ) AND ROWNUM = 1 ) ) ) AND D.RESERVATION_TYPE !=
  DECODE(NVL(V.N_COLUMN1,R.INCLUDE_ONHAND_AVAILABLE), 2 , 2, -1) AND
  D.RESERVATION_TYPE != DECODE(R.DEMAND_CLASS_ATP_FLAG, 1, 2, -1) AND
  D.DEMAND_SOURCE_TYPE != DECODE(R.INCLUDE_SALES_ORDERS, 2, 2, -1) AND
  D.DEMAND_SOURCE_TYPE != DECODE(R.INCLUDE_INTERNAL_ORDERS, 2, 8, -1) AND
  EXISTS ( SELECT NULL FROM MTL_SECONDARY_INVENTORIES S WHERE
  S.ORGANIZATION_ID =D.ORGANIZATION_ID AND S.INVENTORY_ATP_CODE =
  DECODE(R.DEFAULT_ATP_SOURCES,1, 1,NULL, 1, S.INVENTORY_ATP_CODE) AND
  S.AVAILABILITY_TYPE =DECODE(R.DEFAULT_ATP_SOURCES, 2,1, S.AVAILABILITY_TYPE)
  AND S.SECONDARY_INVENTORY_NAME = D.SUBINVENTORY UNION ALL SELECT NULL FROM
  DUAL WHERE D.SUBINVENTORY IS NULL ) AND V.AVAILABLE_TO_ATP = 1 AND
  V.ATP_RULE_ID = R.RULE_ID AND V.ATP_GROUP_ID = :B2 AND I.ORGANIZATION_ID =
  V.ORGANIZATION_ID AND I.INVENTORY_ITEM_ID = V.INVENTORY_ITEM_ID AND
  NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))= DECODE(
  R.DEMAND_CLASS_ATP_FLAG, 1,NVL(V.DEMAND_CLASS,NVL(P.DEFAULT_DEMAND_CLASS,
  '@@@')),NVL(D.DEMAND_CLASS, NVL(P.DEFAULT_DEMAND_CLASS,'@@@'))) AND
  P.ORGANIZATION_ID = NVL(V.ATP_CALENDAR_ORGANIZATION_ID,V.ORGANIZATION_ID)
  AND C.PRIOR_SEQ_NUM >= DECODE(D.RESERVATION_TYPE,2,C.PRIOR_SEQ_NUM,DECODE(
  R.PAST_DUE_DEMAND_CUTOFF_FENCE, NULL, C.PRIOR_SEQ_NUM, :B1 -
  R.PAST_DUE_DEMAND_CUTOFF_FENCE)) AND C.PRIOR_SEQ_NUM <
  DECODE(D.RESERVATION_TYPE,2,C.PRIOR_SEQ_NUM+1,NVL( :B1 + (DECODE(
  R.INFINITE_SUPPLY_FENCE_CODE,1, I.CUMULATIVE_TOTAL_LEAD_TIME,2,
  I.CUM_MANUFACTURING_LEAD_TIME,3,
  I.PREPROCESSING_LEAD_TIME+I.FULL_LEAD_TIME+ I.POSTPROCESSING_LEAD_TIME,4,
  R.INFINITE_SUPPLY_TIME_FENCE)), C.PRIOR_SEQ_NUM +1)) AND P.CALENDAR_CODE =
  C.CALENDAR_CODE AND P.CALENDAR_EXCEPTION_SET_ID = C.EXCEPTION_SET_ID AND
  C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE) AND V.INVENTORY_ITEM_ID =
  DECODE(D.RESERVATION_TYPE,1,DECODE( D.PARENT_DEMAND_ID, NULL,
  V.INVENTORY_ITEM_ID,-1),2,V.INVENTORY_ITEM_ID,3,
  DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1,V.INVENTORY_ITEM_ID,DECODE(
  R.INCLUDE_NONSTD_WIP_RECEIPTS, 1,V.INVENTORY_ITEM_ID, -1)),-1) AND
  V.INVENTORY_ITEM_ID=DECODE(R.INCLUDE_SALES_ORDERS, 2,DECODE(
  R.INCLUDE_INTERNAL_ORDERS, 2,DECODE(R.INCLUDE_ONHAND_AVAILABLE, 2,DECODE(
  R.INCLUDE_NONSTD_WIP_RECEIPTS, 2,DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 2,
  - 1,V.INVENTORY_ITEM_ID),V.INVENTORY_ITEM_ID),V.INVENTORY_ITEM_ID),
  V.INVENTORY_ITEM_ID),V.INVENTORY_ITEM_ID)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 2 0
Execute 1 0.06 0.09 0 0 0 0
Fetch 1 935.72 1362.38 5 40317524 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 935.78 1362.48 5 40317524 2 0

6. Had them run apscheck.sql, AWR report

A. apscheck.sql output

9.2 Complete listing of RDBMS settings from v$parameter:
aq_tm_processes = 1 (maybe this should be 2 ???)
job_queue_processes = 12 (Should be higher perhaps ???)

B. From AWR report - appears to be from before the trace was done above (up to 18:00:00 on 03-DEC but traces run around 18:43:00 so need another run for this time period



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Inventory: On-Hand Availability >
2. Item Supply/Demand - some times during the day it works fine but suddenly that error message appears:
  "RFC failure because no managers available" and nobody can inquiry supply/demand information

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.