This discussion is archived
4 Replies Latest reply: Nov 7, 2012 2:08 PM by Voltaire RSS

PLP_WorkforceEventFact_Month Task Failure

Saro Explorer
Currently Being Moderated
Hi,

One of the failed HRMS task
PLP_WorkforceEventFact_Month
Which results in failure due to the
ANOMALY INFO::: Error while executing : CREATE INDEX:W_WRKFC_EVT_MONTH_F:W_WRKFC_EVT_MONTH_F_U1
MESSAGE:::java.lang.Exception: Error while execution : CREATE UNIQUE INDEX 
     W_WRKFC_EVT_MONTH_F_U1 
I searched for the related Failure in Oracle support with the reference Id: 1308343.1 which says to set refresh dates as null for the target tables.
W_WRKFC_EVT_FS
W_WRKFC_EVT_F
W_WRKFC_EVT_MONTH_F
W_WRKFC_EVT_MERGE_F
W_WRKFC_EVT_POW_F
W_WRKFC_EVT_AGE_F
W_WRKFC_EVT_MONTH_F 
Even after setting those dates, and if i restarted the EP means then too that task results in failure. Even the My support solution is also not working for that failed task.

Where im missing.

Regards,
Saro
  • 1. Re: PLP_WorkforceEventFact_Month Task Failure
    Saro Explorer
    Currently Being Moderated
    Hi,

    I tried from the source qualifier query of my failed task "PLP_WorkforceEventFact_Month" and below is FYR
    SELECT
        FCT.ASSIGNMENT_ID,
        FCT.EFFECTIVE_START_DATE  EVT_EFF_START_DATE,
        FCT.WORKER_LAST_ACTIVE_DATE,
        FCT.EFFECTIVE_START_DATE,
        CASE WHEN FCT.TERM_STATUS_IND > 0
             THEN FCT.EFFECTIVE_START_DATE
             WHEN FCT.EFFECTIVE_START_DATE < $$HR_WRKFC_SNAPSHOT_DT OR
                  FCT.EFFECTIVE_END_DATE < CAL.CAL_MONTH_END_DT
             THEN FCT.EFFECTIVE_END_DATE
             ELSE CAL_MONTH_END_DT
        END           EFFECTIVE_END_DATE,
        $$HR_WRKFC_SNAPSHOT_DT ,
        CAL.CAL_MONTH_START_DT,
        CAL.CAL_MONTH_END_DT,
        CAL.CAL_QTR,
        CAL.CAL_YEAR,
        CAL.LAST_MONTH_CAL_QTR_FLG,
        CAL.LAST_MONTH_CAL_YEAR_FLG,
        FCT.CHANGE_EVENT_TYPE,
        FCT.MAX_SEQ_IND,
        FCT.EVENT_SEQ,
        FCT.EVENT_YEAR,
        FCT.ASSIGNMENT_NUMBER,
        FCT.EVENT_DT_WID,
        CAL.ROW_WID EVENT_MONTH_WID,
        FCT.EMPLOYEE_WID,
        FCT.HR_ORG_WID,
        FCT.HR_ORG_PRV_WID,
        FCT.JOB_WID,
        FCT.JOB_PRV_WID,
        FCT.PAY_GRADE_WID,
        FCT.PAY_GRADE_PRV_WID,
        FCT.HR_POSITION_WID,
        FCT.HR_POSITION_PRV_WID,
        FCT.LOCATION_WID,
        FCT.LOCATION_PRV_WID,
        FCT.SUPERVISOR_WID,
        FCT.SUPERVISOR_PRV_WID,
        FCT.EMPLOYMENT_WID,
        FCT.HR_BUSINESS_UNIT_WID,
        FCT.COMPANY_ORG_WID,
        FCT.EVENT_TYPE_RSN_WID,
        FCT.POSTN_DH_BASE_EMP_ID,
        FCT.INTL_ASSGN_WID,
        FCT.AGE_BAND_WID,
        FCT.PERFORMANCE_BAND_WID,
        FCT.PRD_OF_WRK_BAND_WID,
        FCT.HIRE_DT,
        FCT.LAST_HIRE_DT,
        FCT.POW_START_DT,
        FCT.BIRTH_DT,
        FCT.ORG_ENTRY_DT,
        FCT.JOB_ENTRY_DT,
        FCT.GRD_ENTRY_DT,
        FCT.POS_ENTRY_DT,
        FCT.LAST_PROM_DT,
        FCT.LAST_SAL_INCR_DT,
        FCT.HEADCOUNT,
        FCT.FTE,
        FCT.SALARY_ANNL,
        FCT.SALARY_MONTH,
        FCT.SALARY_DAY,
        FCT.SALARY_HOUR,
        FCT.TOTAL_SERVICE_DAYS,
        FCT.NRMLSD_PERF_RATING,
        FCT.ORIG_PERF_RATING,
        FCT.STD_HOURS_MONTH,
        FCT.IASG_ALLCTN_FACTOR,
        FCT.EMP_IND,
        FCT.CWK_IND,
        FCT.MANAGER_ROLE_IND,
        FCT.SUPERVISOR_IND,
        FCT.FIRST_RECORD_IND,
        FCT.HIRE_EVENT_IND,
        FCT.REHIRE_EVENT_IND,
        FCT.TERM_EVENT_IND,
        FCT.ASG_START_EVENT_IND,
        FCT.ASG_END_EVENT_IND,
        FCT.PROM_EVENT_IND,
        FCT.ORG_CHANGE_IND,
        FCT.JOB_CHANGE_IND,
        FCT.POS_CHANGE_IND,
        FCT.GRD_CHANGE_IND,
        FCT.LOC_CHANGE_IND,
        FCT.SUP_CHANGE_IND,
        FCT.SAL_CHANGE_IND,
        FCT.PFRT_CHANGE_IND,
        FCT.HDC_GAIN_IND,
        FCT.HDC_LOSS_IND,
        FCT.FTE_GAIN_IND,
        FCT.FTE_LOSS_IND,
        FCT.TERM_STATUS_IND,
        FCT.HOME_ASSIGNMENT_ID,
        FCT.DELETE_FLG,
        FCT.REJECT_FLG,
        FCT.DOC_CURR_CODE,
        FCT.LOC_CURR_CODE,
        FCT.LOC_EXCH_RATE_TYPE,
        FCT.LOC_EXCHANGE_RATE,
        FCT.EXCHANGE_DT,
        FCT.CREATED_ON_DT,
        FCT.CHANGED_ON_DT,
        FCT.AUX1_CHANGED_ON_DT,
        FCT.AUX2_CHANGED_ON_DT,
        FCT.AUX3_CHANGED_ON_DT,
        FCT.AUX4_CHANGED_ON_DT,
        FCT.DATASOURCE_NUM_ID,
        FCT.INTEGRATION_ID,
        FCT.TENANT_ID,
        FCT.X_CUSTOM,
        FCT.ETL_PROC_WID,
        FCT.CREATED_BY_WID,
        FCT.CHANGED_BY_WID,
        FCT.GLOBAL1_EXCHANGE_RATE,
        FCT.GLOBAL2_EXCHANGE_RATE,
        FCT.GLOBAL3_EXCHANGE_RATE
      FROM
       W_WRKFC_EVT_MERGE_F FCT,
       W_MONTH_D CAL
      WHERE FCT.EVENT_MONTH_WID = CAL.ROW_WID AND
      FCT.EFFECTIVE_START_DATE >= CAL.CAL_MONTH_START_DT AND 
      FCT.EFFECTIVE_START_DATE <= CAL.CAL_MONTH_END_DT
    I checked from the above query for any duplication of the assignment_id in my target system like below
    SELECT
    count(FCT.ASSIGNMENT_ID),  FCT.ASSIGNMENT_ID
     FROM
    W_WRKFC_EVT_MERGE_F FCT,
    W_MONTH_D CAL
    WHERE FCT.EVENT_MONTH_WID = CAL.ROW_WID AND
    FCT.EFFECTIVE_START_DATE >= CAL.CAL_MONTH_START_DT AND 
    FCT.EFFECTIVE_START_DATE <= CAL.CAL_MONTH_END_DT
    group by  FCT.ASSIGNMENT_ID
    having count(FCT.ASSIGNMENT_ID) > 1
    But the above query returned more than 9000 rows with more duplication in the assignment_id.

    Not sure what is still wrong for the failure.

    Regards,
    Saro
  • 2. Re: PLP_WorkforceEventFact_Month Task Failure
    Srini VEERAVALLI Guru
    Currently Being Moderated
    You need to check all the tables involved in populating table W_WRKFC_EVT_MERGE_F, these can be dimension tables or source tables.
  • 3. Re: PLP_WorkforceEventFact_Month Task Failure
    Saro Explorer
    Currently Being Moderated
    Hi svee,

    Sorry if i asked anything wrong.

    I couldnt find the source table which is mapped to the target table "W_WRKFC_EVT_MERGE_F" inorder to troubleshoot the issue.

    Regards,
    Saro
  • 4. Re: PLP_WorkforceEventFact_Month Task Failure
    Voltaire Journeyer
    Currently Being Moderated
    Hi Saro,

    Looks like you missed "W_WRKFC_EVT_MONTH_EQ_TMP" table to set refresh date NULL. Along the 6 tables you have already set refresh date to null should force W_WRKFC_EVT_MONTH_F table for Full Source - Full Target load.

    Regards,
    Jay

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points