Planning Manager and MDS Fail with ORA-01400 MRP_SALES_ORDER_UPDATES.NEW_SCHEDULE_DATE (Doc ID 268971.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Materials Requirement Planning - Version 11.5.6 to 12.1 [Release 11.5 to 12.1]
Information in this document applies to any platform.

Materials Requirement Planning - Version: 11.5.6 to 12.1
MRCRLF Planning Manager
MRCSAL1 Load/Copy/Merge MDS


Symptoms

Planning Manager and/or Load/Copy/Merge MDS process fails with error messages
that can contain one or more of the following examples:


begin mrp_manager_pk . compute_sales_order_changes ( :request_id , :us 2479230/mrp/src/mrlscc.pc

ORACLE error 1400 in mrcsoc_compute_sales_order_changes

Cause: mrcsoc_compute_sales_order_changes failed due to
ORA-01400: cannot insert NULL into (MRP.MRP_SALES_ORDER_UPDATES.NEW_SCHEDULE_DATE)



The Planning Manager concurrent request log file can show a message similar to:

Compute sales order changes has started
The value of profile option 'MRP_COMPUTE_SO_CHANGES' is Y
Statement with the error:
begin mrp_manager_pk . compute_sales_order_changes ( :request_id ,
:us 2479230/mrp/src/mrlscc.pc

***** End Of Program - Planning Manager *****

Successfully resubmitted concurrent program MRCRLF with request ID 10817265 to start at 31-JAN-2008 10:24:51 (ROUTINE=AFPSRS)





In the Planning Manager set up form check for the following message:
(Navigate to: Material Planner /Setup / Planning Manager)

ORACLE error 1400 in mrcsoc_compute_sales_order_changes Cause: mrcsoc_compute_sales_order_changes failed due to ORA-01400: cannot insert NULL into (MRP.MRP_SALES_ORDER_UPDATES.NEW_SCHEDULE_DATE)



From SQL you can also check the same by running this script.

-- check the last 24hrs of PLM activity --
SET LINES 120 pages 60
column MESSAGE format A64
SELECT 
to_char(LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') "Last Update",
MESSAGE 
FROM MRP_SCHEDULER_MESSAGES
where LAST_UPDATE_DATE > sysdate - 1
order by last_update_date;



To find the offending sales orders that cause this error please run this SQL script:


-- finds orders with NULL schedule_ship_date and visible_demand_flag Yes --
SET linesize 120 pagesize 60
select ooh.order_number order#,
ool.line_id, ool.inventory_item_id item_id,
ool.ship_from_org_id org_id,
ool.schedule_ship_date,
ool.ordered_quantity ord_qty,
ool.shipped_quantity cmpl_qty,
ool.open_flag,
ool.visible_demand_flag,
ool.item_type_code item_type
from
oe_order_lines_all ool,
oe_order_headers_all ooh
where ooh.header_id = ool.header_id and
       nvl(ool.visible_demand_flag,'N') = 'Y' and
       ool.schedule_ship_date is null
       and ool.line_id in (select line_id from mrp.mrp_so_lines_temp);

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