Memory Based Snapshot Worker Error Ora-01455 mrsres_load_reservations (Doc ID 1569691.1)

Last updated on APRIL 04, 2025

Applies to:

Symptoms

On : 12.1.3 version, Memory Based Planner in PROD:

When attempting to run an ASCP plan the following error occurs in MSCNSWWS64 module: Memory Based Snapshot Worker 64 bit Sun

Workaround: Disable Net Reservations in all organizations in the plan options and rerun the ASCP plan

Analysis: This is being caused by data collections source view Discussed with DEV. In ASCP data collections we have a view called MRP_AP_HARD_RESERVATIONS_V. The setup of this view is as follows:

prompt ***** Creating View MRP_AP_HARD_RESERVATIONS_V ********
CREATE OR REPLACE FORCE VIEW MRP_AP_HARD_RESERVATIONS_V AS
SELECT OOL.INVENTORY_ITEM_ID,
  OOL.ORGANIZATION_ID,
  MR.RESERVATION_ID*2 DEMAND_ID, ---Here's the problem. We have to multiply by 2 to keep data straight in ASCP collections from running over other data
  OOL.LINE_ID PARENT_DEMAND_ID,
  INV_SALESORDER.GET_SALESORDER_FOR_OEHEADER(OOL.HEADER_ID) DISPOSITION_ID,
  OOL.SCHEDULE_SHIP_DATE REQUIREMENT_DATE,
  TO_CHAR(NULL) REVISION,

Now we can see from customer source instance the following:

MIN(RESERVATION_ID) MAX(RESERVATION_ID)
------------------- -------------------
  303 1074356492 - multiply this by 2 and we get 2148712984 - this is greater than max value = 2147483647 allowed in ASCP

So when this sql executes based on the above that's when the ORA-01455 error is thrown

 EXEC SQL DECLARE RESERVE_CUR CURSOR FOR
  SELECT res.transaction_id,
  NVL(res.parent_demand_id,NULL_VALUE),
  res.inventory_item_id,
  res.organization_id,
  res.sr_instance_id,
  NVL(TO_NUMBER(TO_CHAR(res.requirement_date, 'J')),NULL_VALUE),
  NVL(res.revision,'-23453'),
  res.reserved_quantity,
  res.nonnet_quantity_reserved,
  res.disposition_type,
  nvl(res.disposition_id,-23453),
  NVL(res.subinventory, '-23453'),
  NVL(res.project_id, NULL_VALUE),
  NVL(res.task_id, NULL_VALUE),
  NVL(res.planning_group, '-23453'),
  NVL(res.SUPPLY_SOURCE_HEADER_ID , '-23453'), /* ds change start */
NVL(res.SUPPLY_SOURCE_TYPE_ID, '-23453'), /* ds change end */
NVL(res.repair_line_id, NULL_VALUE),
NVL(res.reservation_type, NULL_VALUE)
  FROM msc_sub_inventories sub,
  msc_reservations res,
  msc_system_items items,
  msc_plan_organizations_v orgs
  WHERE sub.sub_inventory_code(+) = res.subinventory
  AND sub.plan_id(+) = :plan_id
  /*AND res.reservation_flag = SYS_YES*/
  AND res.plan_id = PLAN_NEUTRAL_ID
  AND res.sr_instance_id = items.sr_instance_id
  AND res.organization_id = items.organization_id
  AND res.inventory_item_id = items.inventory_item_id
  AND items.plan_id = orgs.plan_id
  AND items.sr_instance_id = orgs.sr_instance_id
  AND items.organization_id = orgs.planned_organization
  AND orgs.net_reservations = SYS_YES
  AND orgs.plan_id = :plan_id
  AND (:snapshot_mode = SYS_YES
  or
  (:snapshot_mode = NET_CHANGE
  and items.netchange_replan_flag = SYS_YES))
  ORDER BY orgs.planned_organization,
  items.inventory_item_id,
  orgs.sr_instance_id;

We can't only allow 1/2 of a source sequence to be used. The code needs to be fixed. This is similar to what's being done in ASCP bug 15977203 but not quite the same. In this case, it's the view definition that creates this problem.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
Advanced supply chain planner
1. launch plan
2. Plan fails
3. Disable Net reservations in all orgs in plan options and plan succeeds

Cause

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