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