Planning Manager - MRCRLF - And Master Schedule Interface Load - MRCSLW Error ORA-00001 On Index MRP_SCHEDULE_DATES_U1 (Doc ID 578412.1)

Last updated on MAY 15, 2023

Applies to:

Goal

1.  The Planning Manager (MRCRLF) and Master Schedule Interface Load (MRCSLW ) sometimes error out, getting error ORA-00001 while inserting to table MRP_SCHEDULE_DATES on index
MRP_SCHEDULE_DATES_U1. The trace files of the 2 concurrent requests are running the same sql at
the same time which appears to be causing the error due to overlapping concurrent requests:

INSERT INTO mrp_schedule_dates
(inventory_item_id, reference_schedule_id, organization_id,
schedule_designator, schedule_level, schedule_date,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, schedule_quantity, schedule_origination_type,
repetitive_daily_rate, source_forecast_designator, rate_end_date,
mps_transaction_id, schedule_comments, source_organization_id,
source_schedule_designator, schedule_workdate,
original_schedule_quantity, supply_demand_type)
SELECT inventory_item_id, reference_schedule_id, organization_id,
schedule_designator, :b4, schedule_date, last_update_date,
last_updated_by, SYSDATE, last_updated_by, -1, schedule_quantity,
schedule_origination_type, repetitive_daily_rate,
source_forecast_designator, rate_end_date, mps_transaction_id,
schedule_comments, source_organization_id,
source_schedule_designator, schedule_date,
original_schedule_quantity, supply_demand_type
FROM mrp_schedule_dates dates
WHERE NOT EXISTS (
SELECT NULL
FROM mrp_schedule_dates
WHERE mps_transaction_id = dates.mps_transaction_id
AND schedule_level = :b4)
AND schedule_level = :b3
AND (organization_id = :b2 OR :b2 IS NULL)
AND (schedule_designator = :b1 OR :b1 IS NULL)

2.  Planning manager error log:

.mrlscl_schedule_int_load has inserted 502 row(s) from MRP_SCHEDULE_DATE 0:05
Statement with the error:
begin mrp_planner_pk . create_orig_mps_entries ( NULL , NULL ) ; END ;
2683850/mrp/src/mrlscl.pc

3.  Master Schedule Interface load error Log:

mrlscl_schedule_int_load has inserted 220 row(s) from MRP_SCHEDULE_DATES 0:02
Statement with the error:
begin mrp_planner_pk . create_orig_mps_entries ( NULL , NULL ) ; END ;
2683850/mrp/src/mrlscl.pc
APP-FND-01564: ORACLE error 1 in mrlscl_schedule_int_load
Cause: mrlscl_schedule_int_load failed due to ORA-00001: unique constraint
(MRP.MRP_SCHEDULE_DATES_U1) violated
ORA-06512: at "APPS.MRP_PLANNER_PK", line 256
ORA-06512: at line 1

When checking the mrp_schedule_interface table for duplicate records,
A>  Some cases does not find any rows returned
BUT
B> Other cases have been reported where rows in process_status = 3 did have duplicate Transaction_id's

select transaction_id, process_status, count(*)
from mrp_schedule_interface
Group by transaction_id, process_status having count(*)>1;

How do the users fix this Issue?

Solution

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