Absence query in Fusion HCM

Absence query in Fusion HCM

Hi friends , we are going to discuss about the Absence query in Fusion HCM. We will share the detail sql query which helps to extract the complete employee absence data in oracle fusion. We will share the complete table and the sql query , using this we can get the employee leaves details and the status of each. Absence module is one of the most important HCM cloud module , in this absence modules , we do get the employee leaves data. We will also share the important absence tables which helps to store the employee absence details in oracle fusion HCM. We have also some important sql queries too related to employee absence details using this we can get the employee absence data in oracle fusion HCM. Using this absence query we can develop the custom bip report in oracle fusion. Please find below the complete detail about Absence query in Fusion HCM.

TOP 5 Employee Absence Tables in Oracle Fusion HCM

Here below is the list of important absence tables in oracle fusion HCM.

1.Per_Absence_Attendances

2.ANC_PER_ABS_ENTRIES

3.PER_ABSENCE_ATTENDANCE_TYPES_B

4.hrc_txn_header

5.hrc_txn_data

Absence query in Fusion HCM

Detail SQL query to Employee absence data in oracle fusion HCM

Here below is the detail sql query to get the employee absence data in oracle fusion hcm.

This SQL query selects the list of absence record transactions which are waiting for the approval:

select * from Per_Absence_Attendances where absence_Attendance_Id in ( select object_id from hrc_txn_header WHERE module_identifier LIKE 'AbsencesApproval')

For getting the absence type transactions list, you can use below SQL query:

select * from PER_ABSENCE_ATTENDANCE_TYPES_B where absence_attendance_type_id in (select object_id from hrc_txn_header where module_identifier like

'AbsenceTypesApproval')

Absence Query 1:-

select ( select first_name ||' '|| last_name  from per_person_names_f_v personNames where person_id=PersonId and sysdate between effective_start_date and effective_end_date) Person_name,

(select name from PER_ABS_ATTENDANCE_TYPES_VL where absence_attendance_type_id=AbsenceAttendanceTypeId) AbsenceTypeName, txn.*

from ( select extractvalue( xmltype('<root>'||txndata.data_cache||'</root>'),

'root/TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/AbsenceEORow[1]/CEO[1]/EO[1]/AbsenceAssignmentEORow[1]/AbsenceDays[1]/DATA[1]' ) as Absence_days,

extractvalue( xmltype('<root>'||txndata.data_cache||'</root>'),

'root/TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/AbsenceEORow[1]/CEO[1]/EO[1]/AbsenceAssignmentEORow[1]/AbsenceHours[1]/DATA[1]' ) as Absence_hours,

extractvalue( xmltype('<root>'||txndata.data_cache||'</root>'),

'root/TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/AbsenceEORow[1]/DateStart[1]/DATA[1]' ) as Date_Start,

extractvalue( xmltype('<root>'||txndata.data_cache||'</root>'),

'root/TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/AbsenceEORow[1]/DateEnd[1]/DATA[1]' ) as Date_End,

extractvalue( xmltype('<root>'||txndata.data_cache||'</root>'),

'root/TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/AbsenceEORow[1]/DateNotification[1]/DATA[1]' ) as DateNotification,

extractvalue( xmltype('<root>'||txndata.data_cache||'</root>'),

'root/TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/AbsenceEORow[1]/PersonId[1]/DATA[1]' ) as PersonId,

extractvalue( xmltype('<root>'||txndata.data_cache||'</root>'),

'root/TRANSACTION[1]/BUSINESSDATA[1]/AM[1]/TXN[1]/EO[1]/AbsenceEORow[1]/AbsenceAttendanceTypeId[1]/DATA[1]' ) as AbsenceAttendanceTypeId,

txndata.status,

txnhe.*

from hrc_txn_data txndata, hrc_txn_header txnhe where txnhe.module_identifier

LIKE 'AbsencesApproval' and txnhe.transaction_id=txndata.transaction_id and txndata.status like 'PENDING'  order by txnhe.creation_date desc ) txn

Absence Query 2:-

select  per.person_number,

abs.absence_type_id "Absence Type Id",

            abs.name absence_type,

            to_char(apae.start_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')  start_date,

            to_char(apae.end_date,'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')  end_date,

            duration "Absence Duration"

from     ANC_PER_ABS_ENTRIES apae,

         PER_PERIODS_OF_SERVICE pps,

         PER_ALL_PEOPLE_F per,

        ANC_ABSENCE_TYPES_VL abs

where 1=1

and pps.person_id = per.person_id

and per.person_number = :P_EMP_NUMBER

and trunc(sysdate) between abs.effective_start_date and abs.effective_end_Date

and trunc(sysdate) between per.effective_start_date and per.effective_end_Date

and apae.period_of_service_id = pps.period_of_Service_id

and apae.absence_type_id = abs.absence_type_id

order by 1

Absence query in Fusion HCM