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

Absence Day Wise SQL Query in Oracle Cloud HCM

 

The following SQL query will help employees taken the Absence Leaves with Day Wise in Oracle Cloud HCM.

 

with advr as
(select trunc((Select min(start_date) from ANC_PER_ABS_ENTRIES)+lvl) dayy from
  (select level lvl from dual connect by level <= ((Select max(start_date) from ANC_PER_ABS_ENTRIES)-(Select min(start_date) from ANC_PER_ABS_ENTRIES)) ))

SELECT DISTINCT
       papf.person_number "EmployeeNumber"
     , pn.person_id
     , TO_CHAR(PPOS.DATE_START, 'MM/DD/YYYY') AS "HireDate"
     , pn.last_name
     , pn.first_name
     , pn.middle_names
     , pn.display_name
     , pn.full_name
     , hou.name                        DepartmentName
     , hou.organization_id             department_id
     , paa.assignment_id               asg_id
     , paa.assignment_type             asg_type
     , paa.assignment_number           asg_num
     , paa.primary_flag
     , employer.name                   LEemployer_name
     ,DECODE(abs.UOM,'D','Days','C','Calender Days',abs.UOM) UOM
     ,TO_CHAR(abs.start_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') start_date
     ,TO_CHAR(abs.end_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') end_date
     , (CASE WHEN abs.start_date=abs.END_date THEN TO_CHAR(abs.end_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') ELSE TO_CHAR(abs.start_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN')||' - '||TO_CHAR(abs.end_date, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') END ) Absence
     ,TO_CHAR(advr.dayy, 'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') dayy
     ,advr.dayy din
     ,TO_CHAR(advr.dayy, 'Day','NLS_DATE_LANGUAGE=AMERICAN') Day_Name
     ,abs_typ_tl."NAME"                          absence_type_name
     ,PE.EMAIL_ADDRESS
  FROM per_person_names_f_v         pn
     , PER_PERSON_SECURED_LIST_V             papf
     , ANC_PER_ABS_ENTRIES          abs
     , anc_per_abs_type_entries     abs_typ_ent  
     , hr_all_organization_units_vl hou
     , PER_ASSIGNMENT_SECURED_LIST_V        paa
     , hr_all_organization_units_vl employer
     ,advr
     ,fusion.ANC_ABSENCE_TYPES_F                abs_typ_b
     ,fusion.ANC_ABSENCE_TYPES_F_TL             abs_typ_tl
     ,PER_EMAIL_ADDRESSES PE
     ,PER_PERIODS_OF_SERVICE PPOS
 WHERE abs.start_date
       BETWEEN NVL( :pStartDate, abs.start_date )
            AND NVL( :pEndDate, abs.start_date )  
   AND (    :pPersonNumber IS NULL
         OR UPPER(:pPersonNumber) in UPPER(papf.person_number)
        )
   AND abs.person_id = papf.person_id
   AND pn.person_id = abs.person_id
   AND abs.start_date
       BETWEEN pn.EFFECTIVE_START_DATE  AND pn.EFFECTIVE_END_DATE
   AND papf.person_id = abs.person_id
   AND abs.start_date
       BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
   AND abs_typ_ent.per_absence_entry_id
       = abs.per_absence_entry_id
 /******************** Joining with Email Table ******************/       
           AND PAPF.person_id = PE.person_id
    AND PE.EMAIL_TYPE = 'W1'
    /******************** Joining with HireDate Table ******************/
    AND papf.PERSON_ID = PPOS.PERSON_ID
   AND PPOS.PERIOD_OF_SERVICE_ID = PAA.PERIOD_OF_SERVICE_ID
   /******************** Joining with Assignments Table ******************/
  AND paa.person_id  = abs.person_id
  AND abs.absence_status_cd <> 'CANCELED'
  AND paa.assignment_id = abs_typ_ent.assignment_id
  AND abs.start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
  AND paa.assignment_type= 'E'
  AND paa.primary_flag = 'Y'
  /********************  Joining with Departmetn Org Units ************/
  AND hou.organization_id(+) = paa.organization_id

  /********************  Joining Employer Org Units ******************* */
  AND employer.organization_id = paa.legal_entity_id
  AND abs.start_date BETWEEN employer.effective_start_date AND employer.effective_end_date
  AND advr.dayy between abs.start_date and abs.end_date
  AND abs.absence_type_id = abs_typ_b.absence_type_id
  AND abs_typ_b.absence_type_id                 = abs_typ_tl.absence_type_id
            AND abs_typ_tl.language =  USERENV('LANG')
            AND SYSDATE                           BETWEEN abs_typ_b.effective_start_date   AND abs_typ_b.effective_end_date
            AND SYSDATE                             BETWEEN abs_typ_tl.effective_start_date  AND abs_typ_tl.effective_end_date
 order by papf.person_number, advr.dayy