4 Replies Latest reply: Nov 7, 2012 4:08 PM by Voltaire RSS

    PLP_WorkforceEventFact_Month Task Failure

    Saro
      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
          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
            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
              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
                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