0 Replies Latest reply: Mar 26, 2012 7:31 AM by 864658 RSS

    Oracle HRMS Contingent Worker Termination Query Help

    864658
      Hi All,

      I am new to Oracle HRMS and I have some requirement like Rehire Employee to Contingent Worker and vice versa. I am able to get the Employee person type records from Oracle HRMS to OIM 11g(Using trusted recon) and also getting Contingent Worker records also. But while terminating a contingent user in HRMS, I am getting error like No records found. I thought that there is a problem with my query.

      Please find the below query I tried to use to terminate the Contingent Worker.

      ### Terminated Contingent Worker

      SELECT PAPF.PERSON_ID AS PERSON_ID1, \
      PAPF.PERSON_ID AS PERSON_ID2, \
      PAPF.FIRST_NAME AS FIRST_NAME, \
      PAPF.LAST_NAME AS LAST_NAME, \
      PAPF.EMAIL_ADDRESS AS EMAIL_ADDRESS, \
      PPT.USER_PERSON_TYPE AS USER_PERSON_TYPE, \
      TO_CHAR(PAPF.EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE, \
      PAPF.EMPLOYEE_NUMBER AS EMPLOYEE_NUMBER, \
      TO_CHAR(PAPF.EFFECTIVE_END_DATE) AS EFFECTIVE_END_DATE, \
      PAPF.BUSINESS_GROUP_ID AS BUSINESS_GROUP_ID, \
      PAAF.SUPERVISOR_ID AS SUPERVISOR_ID , \
      SUP.FULL_NAME AS SUPERVISOR_NAME, \
      PJ.NAME AS JOB, \
      PG.NAME AS GRADE, \
      HAOU.NAME AS DEPARTMENT \
      FROM PER_ALL_PEOPLE_F PAPF, \
      PER_PERIODS_OF_SERVICE PPS, \
      PER_PERSON_TYPES PPT , \
      PER_PERSON_TYPE_USAGES_F PPU, \
      PER_ALL_ASSIGNMENTS_F PAAF, \
      PER_ALL_PEOPLE_F SUP, \
      PER_JOBS PJ, \
      PER_GRADES PG, \
      HR_ALL_ORGANIZATION_UNITS HAOU \
      WHERE PPT.USER_PERSON_TYPE IN('Contingent Worker') \
      AND PPU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID \
      AND PPU.PERSON_ID = PAPF.PERSON_ID \
      AND PAAF.PERSON_ID = PAPF.PERSON_ID \
      AND PAAF.PRIMARY_FLAG = 'Y' \
      AND PAAF.SUPERVISOR_ID = SUP.person_id(+) \
      AND PAAF.job_id = PJ.job_id(+) \
      AND PAAF.grade_id = PG.grade_id(+) \
      AND PAAF.organization_id = HAOU.organization_id(+) \
      AND PAAF.period_of_service_id = PPS.period_of_service_id(+) \
      AND ACTUAL_TERMINATION_DATE BETWEEN :fromDate AND :toDate \
      AND ACTUAL_TERMINATION_DATE BETWEEN PPU.EFFECTIVE_START_DATE AND PPU.EFFECTIVE_END_DATE \
      AND ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE \
      AND (TRUNC(SYSDATE) BETWEEN SUP.EFFECTIVE_START_DATE(+) AND SUP.EFFECTIVE_END_DATE(+)) \
      AND PAPF.BUSINESS_GROUP_ID = :businessGroupID \
      AND PAPF.PERSON_ID = PPS.PERSON_ID \
      AND ACTUAL_TERMINATION_DATE = PAPF.EFFECTIVE_END_DATE \
      AND ( (ROUND((PAPF.LAST_UPDATE_DATE -TO_DATE('01011970', 'DDMMYYYY')) *1440 *60 *1000) > :lastExecutionTime) \
      OR (ROUND((PAAF.LAST_UPDATE_DATE -TO_DATE('01011970', 'DDMMYYYY')) *1440 *60 *1000) > :lastExecutionTime) ) \
      ORDER BY PAPF.PERSON_ID;

      I need this Scenario is urgent in my case. So could you please provide me correct query to terminate Contingent Workers from Oracle HRMS to OIM using trusted recon.

      Thanks for your help, in advance.

      Thanks,
      Mani