VIEW: APPS.HRFV_ABSENCES

Source

View Text - Preformatted

SELECT bgrt.name business_group_name ,ppf.full_name person_name ,att.date_start date_start ,att.time_start time_start ,att.date_end date_end ,att.time_end time_end ,att.absence_days absence_days ,att.absence_hours absence_hours ,att.date_projected_start projected_start_date ,att.time_projected_start projected_start_time ,att.date_projected_end projected_end_date ,att.time_projected_end projected_end_time ,ppf.employee_number employee_number ,typ.name absence_type ,hr_bis.bis_decode_lookup('ABSENCE_REASON',rea.name) absence_reason ,att.occurrence occurrence ,hr_bis.bis_decode_lookup('ABSENCE_CATEGORY',typ.absence_category) absence_category ,aut.full_name authorising_person_name ,rep.full_name replacement_person_name ,ass.assignment_number assignment_number ,orgt.name organization_name ,jbt.name job_name ,pft.name position_name ,gdt.name grade_name ,loct.location_code location_name ,'_DF:PER:PER_ABSENCE_ATTENDANCES:att' ,'_DF:PER:PER_ABSENCE_ATTENDANCE_TYPES:typ' ,rea.abs_attendance_reason_id abs_attendance_reason_id ,att.absence_attendance_id absence_attendance_id ,typ.absence_attendance_type_id absence_attendance_type_id ,ass.assignment_id assignment_id ,att.authorising_person_id authorising_person_id ,att.business_group_id business_group_id ,ass.person_id person_id ,att.replacement_person_id replacement_person_id FROM hr_locations_all_tl loct ,hr_all_organization_units_tl orgt ,hr_all_organization_units_tl bgrt ,per_jobs_tl jbt ,hr_all_positions_f_tl pft ,per_grades_tl gdt ,per_abs_attendance_reasons rea ,per_absence_attendance_types typ ,per_all_people_f ppf ,per_all_people_f aut ,per_all_people_f rep ,per_assignments_f ass ,per_absence_attendances att WHERE att.absence_attendance_type_id = typ.absence_attendance_type_id AND att.abs_attendance_reason_id = rea.abs_attendance_reason_id (+) AND att.person_id = ppf.person_id AND att.date_start BETWEEN ppf.effective_start_date AND ppf.effective_end_date AND att.authorising_person_id = aut.person_id(+) AND att.date_start BETWEEN aut.effective_start_date(+) AND aut.effective_end_date(+) AND att.replacement_person_id = rep.person_id(+) AND att.date_start BETWEEN rep.effective_start_date(+) AND rep.effective_end_date(+) AND att.date_start is not null AND ass.organization_id = orgt.organization_id (+) AND orgt.language (+) = userenv('LANG') AND ass.job_id = jbt.job_id(+) AND jbt.language (+) = userenv('LANG') AND ass.position_id = pft.position_id(+) AND pft.language (+) = userenv('LANG') AND ass.grade_id = gdt.grade_id(+) AND gdt.language (+) = userenv('LANG') AND ass.location_id = locT.location_id(+) AND loct.language (+) = userenv('LANG') AND att.date_start BETWEEN ass.effective_start_date AND ass.effective_end_date AND ass.person_id = att.person_id AND ass.assignment_type = 'E' AND ass.primary_flag = 'Y' AND ass.business_group_id = bgrt.organization_id AND bgrt.language = userenv('LANG') AND att.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, att.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PPF.FULL_NAME PERSON_NAME
, ATT.DATE_START DATE_START
, ATT.TIME_START TIME_START
, ATT.DATE_END DATE_END
, ATT.TIME_END TIME_END
, ATT.ABSENCE_DAYS ABSENCE_DAYS
, ATT.ABSENCE_HOURS ABSENCE_HOURS
, ATT.DATE_PROJECTED_START PROJECTED_START_DATE
, ATT.TIME_PROJECTED_START PROJECTED_START_TIME
, ATT.DATE_PROJECTED_END PROJECTED_END_DATE
, ATT.TIME_PROJECTED_END PROJECTED_END_TIME
, PPF.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, TYP.NAME ABSENCE_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('ABSENCE_REASON'
, REA.NAME) ABSENCE_REASON
, ATT.OCCURRENCE OCCURRENCE
, HR_BIS.BIS_DECODE_LOOKUP('ABSENCE_CATEGORY'
, TYP.ABSENCE_CATEGORY) ABSENCE_CATEGORY
, AUT.FULL_NAME AUTHORISING_PERSON_NAME
, REP.FULL_NAME REPLACEMENT_PERSON_NAME
, ASS.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, GDT.NAME GRADE_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, '_DF:PER:PER_ABSENCE_ATTENDANCES:ATT'
, '_DF:PER:PER_ABSENCE_ATTENDANCE_TYPES:TYP'
, REA.ABS_ATTENDANCE_REASON_ID ABS_ATTENDANCE_REASON_ID
, ATT.ABSENCE_ATTENDANCE_ID ABSENCE_ATTENDANCE_ID
, TYP.ABSENCE_ATTENDANCE_TYPE_ID ABSENCE_ATTENDANCE_TYPE_ID
, ASS.ASSIGNMENT_ID ASSIGNMENT_ID
, ATT.AUTHORISING_PERSON_ID AUTHORISING_PERSON_ID
, ATT.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASS.PERSON_ID PERSON_ID
, ATT.REPLACEMENT_PERSON_ID REPLACEMENT_PERSON_ID
FROM HR_LOCATIONS_ALL_TL LOCT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_JOBS_TL JBT
, HR_ALL_POSITIONS_F_TL PFT
, PER_GRADES_TL GDT
, PER_ABS_ATTENDANCE_REASONS REA
, PER_ABSENCE_ATTENDANCE_TYPES TYP
, PER_ALL_PEOPLE_F PPF
, PER_ALL_PEOPLE_F AUT
, PER_ALL_PEOPLE_F REP
, PER_ASSIGNMENTS_F ASS
, PER_ABSENCE_ATTENDANCES ATT
WHERE ATT.ABSENCE_ATTENDANCE_TYPE_ID = TYP.ABSENCE_ATTENDANCE_TYPE_ID
AND ATT.ABS_ATTENDANCE_REASON_ID = REA.ABS_ATTENDANCE_REASON_ID (+)
AND ATT.PERSON_ID = PPF.PERSON_ID
AND ATT.DATE_START BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND ATT.AUTHORISING_PERSON_ID = AUT.PERSON_ID(+)
AND ATT.DATE_START BETWEEN AUT.EFFECTIVE_START_DATE(+)
AND AUT.EFFECTIVE_END_DATE(+)
AND ATT.REPLACEMENT_PERSON_ID = REP.PERSON_ID(+)
AND ATT.DATE_START BETWEEN REP.EFFECTIVE_START_DATE(+)
AND REP.EFFECTIVE_END_DATE(+)
AND ATT.DATE_START IS NOT NULL
AND ASS.ORGANIZATION_ID = ORGT.ORGANIZATION_ID (+)
AND ORGT.LANGUAGE (+) = USERENV('LANG')
AND ASS.JOB_ID = JBT.JOB_ID(+)
AND JBT.LANGUAGE (+) = USERENV('LANG')
AND ASS.POSITION_ID = PFT.POSITION_ID(+)
AND PFT.LANGUAGE (+) = USERENV('LANG')
AND ASS.GRADE_ID = GDT.GRADE_ID(+)
AND GDT.LANGUAGE (+) = USERENV('LANG')
AND ASS.LOCATION_ID = LOCT.LOCATION_ID(+)
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND ATT.DATE_START BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND ASS.PERSON_ID = ATT.PERSON_ID
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG')
AND ATT.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ATT.BUSINESS_GROUP_ID) WITH READ ONLY