SQL query will help the decide which employees to hire tomorrow in Oracle Fusion Cloud HCM

 The following SQL query will help the organization decide which employees to hire tomorrow. It will also prepare a report and scheduled as per businesses requirement in Oracle Fusion Cloud HCM

 

SELECT DISTINCT
        PAPF.PERSON_NUMBER as "EmployeeNumber",
         TO_CHAR (PAAM.PROJECTED_START_DATE,'DD-Mon-YYYY', 'nls_date_language=American') as "HireDate",
        PAAM.PROJECTED_START_DATE,
        PPNF.FIRST_NAME as "FirstName",
        PPNF.LAST_NAME as "LastName",
        PPNF.DISPLAY_NAME,
        PPNF.FIRST_NAME || (CASE WHEN PPNF.MIDDLE_NAMES IS NOT NULL THEN ' '||PPNF.MIDDLE_NAMES END) || ' '||PPNF.LAST_NAME as "FullName",
        PLDFT.LOCATION_NAME as "LocationName",
        PAPF_MGR.PERSON_NUMBER as "ManagerEmpNo",
        PPNF_MGR.DISPLAY_NAME as "ManagerName",
        HAPFT_MGR.NAME as "ManagerPostion",
        PJFT_MGR.NAME as "ManagerJob",
        PEA_MGR_W.EMAIL_ADDRESS "ManagerEmail",
        HL_WORK_CAT.MEANING as "WorkerCategory",
        HL_EMP_CAT.MEANING as "EmployeeCategory",
        (SELECT PEA.EMAIL_ADDRESS,
                   FROM PER_EMAIL_ADDRESSES PEA
                   WHERE EMAIL_TYPE ='W1'
                   AND PEA.PERSON_ID =PAPF.PERSON_ID AND Rownum=1 ) as "Work_Email"
FROM
        PER_ALL_ASSIGNMENTS_M PAAM,
        PER_PERIODS_OF_SERVICE PPOS,
        PER_ALL_PEOPLE_F PAPF,
        PER_PERSON_NAMES_F PPNF,
        --------------Location starts------------------
        PER_LOCATION_DETAILS_F PLDF,
        PER_LOCATION_DETAILS_F_TL PLDFT,
        -------------Manager starts--------------------
        PER_ASSIGNMENT_SUPERVISORS_F PASF,
        PER_ALL_PEOPLE_F PAPF_MGR,
        PER_PERSON_NAMES_F PPNF_MGR,
        PER_ALL_ASSIGNMENTS_M PAAM_MGR,
        HR_ALL_POSITIONS_F_TL HAPFT_MGR,
        PER_JOBS_F_TL PJFT_MGR,
        -----------------Manager emails--------
        PER_EMAIL_ADDRESSES PEA_MGR_W,
        ------------work category lookup--------
        HCM_LOOKUPS HL_WORK_CAT,
        ------------Employee category lookup--------
        HCM_LOOKUPS HL_EMP_CAT,
        ------------Phone category lookup--------
        PER_PHONES PP
WHERE    1=1
        AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
        AND PAAM.ASSIGNMENT_TYPE IN ('P')
        AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
        AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
        AND PAAM.PERSON_ID = PAPF.PERSON_ID
        AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
        AND PAAM.PERSON_ID = PPNF.PERSON_ID
        AND PPNF.NAME_TYPE(+) = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE
        -------------location starts-------------------
        AND PAAM.LOCATION_ID = PLDF.LOCATION_ID(+)
        AND PLDF.LOCATION_DETAILS_ID = PLDFT.LOCATION_DETAILS_ID(+)
        AND PLDFT.LANGUAGE(+) = USERENV('LANG')
        AND TRUNC(SYSDATE) BETWEEN PLDF.EFFECTIVE_START_DATE(+) AND PLDF.EFFECTIVE_END_DATE(+)
        AND TRUNC(SYSDATE) BETWEEN PLDFT.EFFECTIVE_START_DATE(+) AND PLDFT.EFFECTIVE_END_DATE(+)
        ----------------Manager starts-----------------
        AND PAAM.ASSIGNMENT_ID = PASF.ASSIGNMENT_ID(+)
        AND PASF.MANAGER_TYPE(+) = 'LINE_MANAGER'
        AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE(+) AND PASF.EFFECTIVE_END_DATE(+)
        AND PASF.MANAGER_ID = PAPF_MGR.PERSON_ID(+)
        AND PASF.MANAGER_ASSIGNMENT_ID = PAAM_MGR.ASSIGNMENT_ID(+)
        AND PAAM_MGR.ASSIGNMENT_TYPE(+) = 'E'
        AND PAAM_MGR.EFFECTIVE_LATEST_CHANGE(+) = 'Y'
        AND TRUNC(SYSDATE) BETWEEN PAAM_MGR.EFFECTIVE_START_DATE(+) AND PAAM_MGR.EFFECTIVE_END_DATE(+)
        AND TRUNC(SYSDATE) BETWEEN PAPF_MGR.EFFECTIVE_START_DATE(+) AND PAPF_MGR.EFFECTIVE_END_DATE(+)
        AND PAPF_MGR.PERSON_ID = PPNF_MGR.PERSON_ID(+)
        AND PPNF_MGR.NAME_TYPE(+) = 'GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNF_MGR.EFFECTIVE_START_DATE(+) AND PPNF_MGR.EFFECTIVE_END_DATE(+)
        AND PAAM_MGR.POSITION_ID = HAPFT_MGR.POSITION_ID(+)
        AND TRUNC(SYSDATE) BETWEEN HAPFT_MGR.EFFECTIVE_START_DATE(+) AND HAPFT_MGR.EFFECTIVE_END_DATE(+)
        AND HAPFT_MGR.LANGUAGE(+) = SYS_CONTEXT('USERENV', 'LANG')
        AND PAAM_MGR.JOB_ID = PJFT_MGR.JOB_ID(+)
        AND PJFT_MGR.LANGUAGE(+) = SYS_CONTEXT('USERENV', 'LANG')
        AND TRUNC(SYSDATE) BETWEEN PJFT_MGR.EFFECTIVE_START_DATE(+) AND PJFT_MGR.EFFECTIVE_END_DATE(+)
        -----------------Manager emails --------
        AND PAAM_MGR.PERSON_ID = PEA_MGR_W.PERSON_ID(+)
        AND PEA_MGR_W.EMAIL_TYPE(+) = 'W1'
        ------------work category lookup starts--------
        AND HL_WORK_CAT.LOOKUP_TYPE(+) = 'EMPLOYEE_CATG'
        AND HL_WORK_CAT.LOOKUP_CODE(+) = PAAM.EMPLOYEE_CATEGORY
        ------------work category lookup ends----------
        AND HL_EMP_CAT.LOOKUP_TYPE(+) = 'EMP_CAT'
        AND HL_EMP_CAT.LOOKUP_CODE(+) = PAAM.EMPLOYMENT_CATEGORY
        AND PP.PERSON_ID(+) = PAAM.PERSON_ID
        AND PP.PHONE_TYPE(+) = 'HM'
        AND TRUNC(PAAM.PROJECTED_START_DATE) = TRUNC(SYSDATE+1)
        ORDER BY PAAM.PROJECTED_START_DATE