Search This Blog

Tuesday, November 7, 2017

Absence Entry Query in Fusion HCM

select  per.person_number,PER_ABSENCE_ENTRY_ID ,abs.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
from ANC_PER_ABS_ENTRIES apae,
         PER_PERIODS_OF_SERVICE pps,
         PER_ALL_PEOPLE_F per,
   ANC_ABSENCE_TYPES_VL abs
where apae.period_of_service_id = pps.period_of_Service_id
    and apae.absence_type_id = abs.absence_type_id
    and pps.person_id = per.person_id
 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 per.person_number = '117889'
 order by per.person_number

HDL to Update existing Absence Leave Entry in Fusion

METADATA|PersonAbsenceEntry|StartDate|StartTime|EndDate|EndTime|ApprovalStatus|AbsenceStatus|advanceRequired(ANC_PER_ABS_ENTRIES_DFF=Annual Leave)|FLEX:ANC_PER_ABS_ENTRIES_DDF|advanceRequired_Display(ANC_PER_ABS_ENTRIES_DFF=Annual Leave)|FLEX:ANC_PER_ABS_ENTRIES_DFF|SourceSystemId|SourceSystemOwner|AbsenceTypeId|Duration
MERGE|PersonAbsenceEntry|2017/09/05|08:00|2017/09/05|18:30|APPROVED|SUBMITTED||||Annual Leave|300000010985134|FUSION|300000003079093|1
MERGE|PersonAbsenceEntry|2017/09/26|08:00|2017/09/26|18:30|APPROVED|SUBMITTED||||Annual Leave|300000010985262|FUSION|300000003079093|1
MERGE|PersonAbsenceEntry|2017/09/28|08:00|2017/09/28|18:30|APPROVED|SUBMITTED||||Annual Leave|300000010985398|FUSION|300000003079093|1


METADATA|PersonAbsenceEntry|Employer|PersonNumber|AbsenceType|StartDate|StartTime|EndDate|EndTime|ApprovalStatus|AbsenceStatus|advanceRequired(ANC_PER_ABS_ENTRIES_DFF=Annual Leave)|FLEX:ANC_PER_ABS_ENTRIES_DDF|advanceRequired_Display(ANC_PER_ABS_ENTRIES_DFF=Annual Leave)|FLEX:ANC_PER_ABS_ENTRIES_DFF
MERGE|PersonAbsenceEntry|XXX|1558|Annual Leave|2017/10/03|8:00|2017/10/03|6:30|APPROVED|SUBMITTED||||Annual Leave
MERGE|PersonAbsenceEntry|XXXX|4522|Annual Leave|2017/10/22|8:00|2017/10/22|6:30|APPROVED|SUBMITTED||||Annual Leave
MERGE|PersonAbsenceEntry|XXXXLLC|5830|Annual Leave|2017/10/02|8:00|2017/10/02|5:00|APPROVED|SUBMITTED||||Annual Leave