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

Last updated on MARCH 23, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.

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

Setting up flat file directories: Checking for DIR: /opt/app/p1epl1m1/inst/apps/EPROD_bspeap13d/logs/appl/conc/out/data3002/
APP-FND-01564: ORACLE error 1455 in mrsres_load_reservations
Cause: mrsres_load_reservations failed due to ORA-01455: converting column overflows integer datatype

The SQL statement being executed at the time of the error was: select res.transaction_id ,NVL(res.parent_demand_id,(-23453)) ,res.inv and was executed from the file /d3/R121_64bit_wkrAuto/msc/src/mslsdi.pc.
APP-MRP-22075: An internal error has occurred (mrsndi_snp_demand_info, 1, , )
APP-MRP-22075: An internal error has occurred (mrnspxt, 19, 10008, )
APP-MRP-22075: An internal error has occurred (mrnspgpt, 14, 202 , 10008, )
APP-MRP-22075: An internal error has occurred (main, 13, , )

Error string=
Statement with the error:
select res.transaction_id ,NVL(res.parent_demand_id,(-23453)) ,res.inv
/d3/R121_64bit_wkrAuto/msc/src/mslsdi.pc



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

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