OBIA: PLP_WorkforceEventFact_Month_PositionHierarchy_Update Failed In Production With ORA-01427 (Doc ID 2230784.1)

Last updated on JUNE 06, 2017

Applies to:

Business Intelligence Applications Consumer - Version 7.9.6.4 to 7.9.6.4 [Release V7]
Information in this document applies to any platform.

Symptoms

OBIA 7.9.6.4, Loads fail. When investigated in Informatica, it was an issue with PLP_WorkforceEventFact_Month_PositionHierarchy_Update failed on following error.

ERROR : (57049 | WRITER_1_*_1) : (IS | Oracle_BI_IS_P) : node01_stl-pobitl-20.lac1.biz : WRT_8229 : Database errors occurred:
ORA-01427: single-row subquery returns more than one row

Database driver error...
Function Name : Execute
SQL Stmt : UPDATE W_WRKFC_EVT_MONTH_F
SET ETL_PROC_WID = ?,

  W_UPDATE_DT = ?,

  EMP_POSTN_DH_WID = (SELECT TMP_NEW.ROW_WID

  FROM W_POSITION_DH_PRE_CHG_TMP TMP_OLD,

  W_POSITION_DH_POST_CHG_TMP TMP_NEW

  WHERE W_WRKFC_EVT_MONTH_F.EMP_POSTN_DH_WID = TMP_OLD.ROW_WID

  AND TMP_OLD.SCD1_WID = TMP_NEW.SCD1_WID

  AND TMP_NEW.EFFECTIVE_FROM_DT <= W_WRKFC_EVT_MONTH_F.EFFECTIVE_START_DATE

  AND TMP_NEW.EFFECTIVE_TO_DT > W_WRKFC_EVT_MONTH_F.EFFECTIVE_START_DATE

  AND W_WRKFC_EVT_MONTH_F.EMP_POSTN_DH_WID <> TMP_NEW.ROW_WID

  )

WHERE EXISTS

 (SELECT 1

  FROM W_POSITION_DH_PRE_CHG_TMP TMP_OLD,

  W_POSITION_DH_POST_CHG_TMP TMP_NEW

  WHERE W_WRKFC_EVT_MONTH_F.EMP_POSTN_DH_WID = TMP_OLD.ROW_WID

  AND TMP_OLD.SCD1_WID = TMP_NEW.SCD1_WID

  AND TMP_NEW.EFFECTIVE_FROM_DT <= W_WRKFC_EVT_MONTH_F.EFFECTIVE_START_DATE

  AND TMP_NEW.EFFECTIVE_TO_DT > W_WRKFC_EVT_MONTH_F.EFFECTIVE_START_DATE

  AND W_WRKFC_EVT_MONTH_F.EMP_POSTN_DH_WID <> TMP_NEW.ROW_WID

 )

OBIA version: 7.9.6.4
Informatica version: 9.6.1
DAC Version: 11.1.1.6.4
OBIEE Version: 11.1.1.7.140527

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