Material Readiness (SF_MATRDY) Report Abends at SF_MATRDY.ProcComp.UpdCust Step when Sales Order has Open and Canceled Lines for the Same Item Code (Doc ID 2275143.1)

Last updated on JUNE 08, 2017

Applies to:

PeopleSoft Enterprise SCM Manufacturing - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

Material Readiness (SF_MATRDY) report abends at SF_MATRDY.ProcComp.UpdCust step when sales order has open and canceled lines for the same item code.

When running the Material Readiness report - if an order is canceled and the Customer Name has since changed - the report retrieves more than one row and is expecting only one. -- 09:19:46.310 Process 14980897 ABENDED at Step SF_MATRDY.ProcComp.UpdCust (Action SQL) -- RC = 8601
ROLLBACK
/
-- 09:19:46.330 SQL Error: [IBM][CLI Driver][DB2/AIX64] SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000

(SQLSTATE 21000) -811

If the SQL would look at the IN_DEMAN.IN_FULFILL_STATE <> '90' (canceled) the report / SQL should work fine).

-- 09:19:46.147 ...(SF_MATRDY.ProcComp.UpdCust) (SQL)
UPDATE PS_SF_MR_PID_TAO6 SET SHIP_CUST_NAME1 = ( SELECT DISTINCT
ID.SHIP_CUST_NAME1 FROM PS_IN_DEMAND ID WHERE ID.BUSINESS_UNIT =
PS_SF_MR_PID_TAO6.BUSINESS_UNIT AND ID.DEMAND_SOURCE = 'OM' AND
ID.SOURCE_BUS_UNIT = PS_SF_MR_PID_TAO6.BUSINESS_UNIT_OM AND ID.ORDER_NO =
PS_SF_MR_PID_TAO6.ORDER_NO) WHERE PROCESS_INSTANCE = 14980897 AND CONFIG_CODE
<> ' ' AND EXISTS ( SELECT 'X' FROM PS_IN_DEMAND ID WHERE ID.BUSINESS_UNIT =
PS_SF_MR_PID_TAO6.BUSINESS_UNIT AND ID.DEMAND_SOURCE = 'OM' AND
ID.SOURCE_BUS_UNIT = PS_SF_MR_PID_TAO6.BUSINESS_UNIT_OM AND ID.ORDER_NO =
PS_SF_MR_PID_TAO6.ORDER_NO)

Steps
---------------
1. Change customer name
Customer's > Customer Information > General Information - Customer ID = USA02. Change Name1 field in correction mode and save.

2. Open the sales order that has canceled and open lines for the same item code.

3. Run Material Readiness Report (SF_MATRDY) (navigation: Production Control, Process Production, Release Production, Material Readiness Report)

The BI Publisher SF_MATRDY finished in No Success and below error:

<<<
File: e:\pt85514b-retail\peopletools\src\psappeng\aedebug.hSQL error. Stmt #: 1723 Error Position: 49 Return: 1427 - ORA-01427: single-row subquery returns more than one row
Failed SQL stmt: UPDATE PS_SF_MR_PID_TAO4 SET SHIP_CUST_NAME1 = ( SELECT DISTINCT ID.SHIP_CUST_NAME1 FROM PS_IN_DEMAND ID WHERE ID.BUSINESS_UNIT = PS_SF_MR_PID_TAO4.BUSINESS_UNIT AND ID.DEMAND_SOURCE = 'OM' AND ID.SOURCE_BUS_UNIT = PS_SF_MR_PID_TAO4.BUSINESS_UNIT_OM AND ID.ORDER_NO = PS_SF_MR_PID_TAO4.ORDER_NO) WHERE PROCESS_INSTANCE = 120675 AND CONFIG_CODE <> ' ' AND EXISTS ( SELECT 'X' FROM PS_IN_DEMAND ID WHERE ID.BUSINESS_UNIT = PS_SF_MR_PID_TAO4.BUSINESS_UNIT AND ID.DEMAND_SOURCE = 'OM' AND ID.SOURCE_BUS_UNIT = PS_SF_MR_PID_TAO4.BUSINESS_UNIT_OM AND ID.ORDER_NO = PS_SF_MR_PID_TAO4.ORDER_NO)

Process 120675 ABENDED at Step SF_MATRDY.ProcComp.UpdCust (SQL) -- RC = 1427 (108,524)

>>>

SF_MATRDY report did not create the SF_MATRDY.pdf in the Report Manager.

SF_MATRDY report should not abend Step SF_MATRDY.ProcComp.UpdCust



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