MRPWCPWB MRP Workbench Is Not Showing Dependent Demand For Components Of MPS End Items Intermittently (Doc ID 1204156.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Materials Requirement Planning - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
Form:MRPSCPWB.FMB - Supply Chain Planner Workbench
Executable:MRCNSP - Memory-based Snapshot

ConcurrentProgram:MRCNSW - Memory-based Snapshot Worker


Goal

On : 11.5.10.2 version, Materials Requirements Plan in PROD.

A. Users are on MRP RUP#16. They started having a problem where the MRP plan run is not generating any demand/supply last Tuesday 08-JUN-2010. Before this it was fine. There are no errors in the log files. They first run an MPS plan which contains only end items and it plans supply/demand and planned orders and all appears to be fine in that plan run. They attach the MPS as a supply schedule and MDS as demand schedule in an SCP plan and it has only just the 1 organization in it. Last Tuesday, probably 98%+ of the MRP items were not planning. They had problems with this all of last week until Friday 11-JUN-2010 when everything started planning properly again. What we found when it was not planning was the Memory Based Snapshot Worker (MRCNSW) was only loading about 12 records when things were not planning properly but thousands of records for Selecting MPS supply entries when it planned correctly. Here is what the log showed on a bad run:

Selecting MPS supply entries 12

B. The last time before then it had run and planned correctly, the Memory Based Snapshot picks up about 62,000+ records. When things were back to normal on Friday 11-JUN-2010, the day before 10-JUN-2010 they had found a memory type of issue that they explain as follows:

The server detected ECC Memory Correctable Errors in a memory module (CPU 1 Slot 6). This caused the system to degrade the memory and put it in Advanced ECC Memory mode. This happed at 11:35pm on 6/8/10. The memory was replaced at 6:30pm on 6/9/10.

Advanced ECC memory is the default memory protection mode for this server. In Advanced ECC, the server is protected against correctable memory errors. The server notifies you if the level of correctable errors exceeds a predefined threshold rate. The server does not fail because of correctable memory errors. Advanced ECC provides additional protection over Standard ECC because it is can correct certain memory errors that are otherwise be uncorrectable and result in a server failure.

Where Standard ECC can correct single-bit memory errors, Advanced ECC can correct single-bit memory errors and multi-bit memory errors if all failed bits are on the same DRAM device on the DIMM.

C. So then without any other changes all was well last Friday 11-JUN-2010 so we thought the issue was at least resolved and we were looking at log files to see what may have occurred. We could not find anything significant other than the fact the Memory Based Snapshot Worker during the MRP plan run was not picking up all the data for some reason. And now this morning 14-JUN-2010 the problem returned. This time the Snapshot worker Selecting MPS supply entries = about 1006 and it should be 62,000+

D. The following is the sql being run at the time to gather the MPS data

SELECT source.compile_designator,
NVL(recom.transaction_id, -23453),
source.inventory_item_id,
source.organization_id,
NVL(TO_CHAR(recom.firm_date, 'J'), -23453),
NVL((NVL(recom.firm_quantity, 0) -
NVL(recom.implemented_quantity, 0)
- DECODE(recom.implement_quantity, NULL,
NVL(recom.quantity_in_process, 0),0)), 0),
NVL(recom.source_organization_id, -23453),
NVL(recom.source_vendor_id, -23453),
NVL(recom.source_vendor_site_id, -23453),
NVL(recom.project_id, -23453),
NVL(recom.task_id, -23453),
NVL(recom.planning_group, '-23453'),
NVL(recom.alternate_bom_designator, '-23453'),
NVL(recom.alternate_routing_designator, '-23453'),
NVL(recom.line_id, -23453),
NVL(recom.end_item_unit_number, '-23453'),
1,
2
FROM mrp_recommendations recom,
mrp_system_items dest,
mrp_system_items source,
mrp_plan_schedules_v scheds
WHERE recom.order_type(+) = 5
AND recom.firm_planned_type(+) = 1
AND recom.firm_date(+) <=TO_DATE(:cutoff_date, 'J')
AND recom.firm_quantity(+) > 0
AND recom.inventory_item_id(+) = source.inventory_item_id
AND recom.organization_id(+) = source.organization_id
AND recom.compile_designator(+)=source.compile_designator
AND dest.mrp_planning_code IN (
DECODE(scheds.input_designator_type,4,8, 0),
DECODE(scheds.input_designator_type,4,7, 0),
DECODE(scheds.input_designator_type,4,9, 0),
DECODE(scheds.input_designator_type,3,3, 0),
DECODE(scheds.input_designator_type,3,7,0))
AND dest.repetitive_type != 2
AND dest.inventory_item_id = source.inventory_item_id
AND dest.compile_designator=scheds.compile_designator
AND dest.organization_id=source.organization_id
AND source.repetitive_type != 2
AND source.compile_designator = scheds.input_designator_name
AND source.organization_id=scheds.input_organization_id
AND scheds.input_designator_type IN (3, 4)
AND scheds.compile_designator = 'VER-MRP'
AND scheds.organization_id = 227
ORDER BY 3, 2;

SELECT source.compile_designator,
NVL(recom.transaction_id, -23453),
source.inventory_item_id,
source.organization_id,
NVL(TO_CHAR(recom.firm_date, 'J'), -23453),
NVL((NVL(recom.firm_quantity, 0) -
NVL(recom.implemented_quantity, 0)
- DECODE(recom.implement_quantity, NULL,
NVL(recom.quantity_in_process, 0),0)), 0),
NVL(recom.source_organization_id, -23453),
NVL(recom.source_vendor_id, -23453),
NVL(recom.source_vendor_site_id, -23453),
NVL(recom.project_id, -23453),
NVL(recom.task_id, -23453),
NVL(recom.planning_group, '-23453'),
NVL(recom.alternate_bom_designator, '-23453'),
NVL(recom.alternate_routing_designator, '-23453'),
NVL(recom.line_id, -23453),
NVL(recom.end_item_unit_number, '-23453'),
1,
2
FROM mrp_recommendations recom,
mrp_system_items dest,
mrp_system_items source,
mrp_plan_schedules_v scheds
WHERE recom.order_type(+) = 5
AND recom.firm_planned_type(+) = 1
AND recom.firm_date(+) <=TO_DATE(:cutoff_date, 'J')
AND recom.firm_quantity(+) > 0
AND recom.inventory_item_id(+) = source.inventory_item_id
AND recom.organization_id(+) = source.organization_id
AND recom.compile_designator(+)=source.compile_designator
AND dest.mrp_planning_code IN (
DECODE(scheds.input_designator_type,4,8, 0),
DECODE(scheds.input_designator_type,4,7, 0),
DECODE(scheds.input_designator_type,4,9, 0),
DECODE(scheds.input_designator_type,3,3, 0),
DECODE(scheds.input_designator_type,3,7,0))
AND dest.repetitive_type != 2
AND dest.inventory_item_id = source.inventory_item_id
AND dest.compile_designator=scheds.compile_designator
AND dest.organization_id=source.organization_id
AND source.repetitive_type != 2
AND source.compile_designator = scheds.input_designator_name
AND source.organization_id=scheds.input_organization_id
AND scheds.input_designator_type IN (3, 4)
AND scheds.compile_designator = 'VER-MPS'
AND scheds.organization_id = 227
ORDER BY 3, 2;

E. There are no errors in the log files

F. We did make sure the MPS and MRP weren't over running each other and a manual rerun of the MRP plan does not help, nor does a new plan name.

G. I checked the calendar and it's built out to 01-JAN-2012 and they run the plan with MRP:Cutoff Date Offset Months = 6 so we're not outside the calendar. Also they are planning with ALL Planned Items in the plan options.

EXPECTED BEHAVIOR
-----------------------
Expect the MRP plan to correctly plan the supply schedules from the MPS plan and generate supply demand during the MRP run

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run MPS Plan - everything plans properly for supply/demand
2. Run MRP Plan - almost no planning data at all
3. Issue went away after a hardware fix but is now back again

How is this problem resolved?


Solution

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