PLP_MfgWorkOrderAggregate failing with error code ORA-01427
Last updated on OCTOBER 23, 2017
Applies to:Business Intelligence Applications Consumer - Version 184.108.40.206.2 and later
Information in this document applies to any platform.
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)
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.
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