PLP_MfgWorkOrderAggregate failing with error code ORA-01427

(Doc ID 2278183.1)

Last updated on OCTOBER 23, 2017

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.9.2 and later
Information in this document applies to any platform.

Symptoms

OBIA 11.1.1.9.2
Data Source - EBS 12

PLP_MfgWorkOrderAggregate failing with error:

ODI-1240: Flow Run PLP_MfgWorkOrderAggregate.W_MFG_WORK_ORDER_A fails while performing a Integration operation. This flow loads target table W_MFG_WORK_ORDER_A.
ODI-1228: Task PLP_MfgWorkOrderAggregate.W_MFG_WORK_ORDER_A (Integration) fails on the target ORACLE connection EBS_PRD2.
Caused By: java.sql.SQLException: ORA-01427: single-row subquery returns more than one row

You have already applied the fix found in: OBIA 11g ODI-1217: Session PLP_PLP_MFGWORKORDERAGGREGATE (1207425500) Fails With Return Code 1427 (Doc ID 2050888.1)

Scenario:
For a Work Order, there is one operation in EBS, but in the W_MFG_OPERATION_DETAIL_F table, there are two records. One for older operation id (A) and one for the new operation id (B). It seems like the users changed the original operation (A) record which is what the BOM routing shows to operation (B) causing another record to be added to the data warehouse rather than be updated. The operation (B) records seems to show current data (start date, end date, completed qty, etc.) but the operation (A) record is stale causing there to be differences when the data is aggregated together for W_MFG_WORK_ORDER_A.

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