VIEW: APPS.PER_NL_ABSENCE_HISTORY_V

Source

View Text - Preformatted

SELECT ppf1.full_name , ppf1.employee_number, ppf1.national_identifier , paa.absence_attendance_id , paa.business_group_id , paa.absence_attendance_type_id, paa.person_id , paat.name , hrl.meaning , paar.name , paa.occurrence, paa.date_notification , paa.date_projected_start , paa.time_projected_start , paa.date_projected_end , paa.time_projected_end , paa.date_start , paa.time_start , paa.date_end , paa.time_end , paa.absence_days , paa.absence_hours , paa.abs_information1 , ppf2.full_name , ppf2.employee_number , ppf3.full_name , ppf3.employee_number , paa.last_update_date, paa.last_updated_by , paa.last_update_login , paa.created_by, paa.creation_date, paa.attribute1, paa.attribute2, paa.attribute3, paa.attribute4, paa.attribute5, paa.attribute6, paa.attribute7, paa.attribute8, paa.attribute9, paa.attribute10, paa.attribute11, paa.attribute12, paa.attribute13, paa.attribute14, paa.attribute15, paa.attribute16, paa.attribute17, paa.attribute18, paa.attribute19, paa.attribute20 from per_all_people_f ppf1, per_all_people_f ppf2 , per_all_people_f ppf3 , per_absence_attendances paa , per_absence_attendance_types paat , per_abs_attendance_reasons paar , hr_lookups hrl WHERE ppf1.person_id =paa.person_id and paa.absence_attendance_type_id = paat.absence_attendance_type_id and paa.abs_attendance_reason_id = paar.abs_attendance_reason_id(+) and paa.authorising_person_id=ppf2.person_id(+) and paa.replacement_person_id=ppf3.person_id(+) and paa.business_group_id=NVL(hr_general.get_business_group_id,paa.business_group_id) and hrl.lookup_type = 'ABSENCE_CATEGORY' and paat.absence_category=hrl.lookup_code and ppf1.effective_end_date between nvl(ppf2.effective_start_date,ppf1.effective_end_date) and nvl(ppf2.effective_end_date,ppf1.effective_end_date) and ppf1.effective_end_date between nvl(ppf3.effective_start_date,ppf1.effective_end_date) and nvl(ppf3.effective_end_date,ppf1.effective_end_date) and nvl(paa.date_start,paa.date_projected_start) between ppf1.effective_start_date and ppf1.effective_end_date

View Text - HTML Formatted

SELECT PPF1.FULL_NAME
, PPF1.EMPLOYEE_NUMBER
, PPF1.NATIONAL_IDENTIFIER
, PAA.ABSENCE_ATTENDANCE_ID
, PAA.BUSINESS_GROUP_ID
, PAA.ABSENCE_ATTENDANCE_TYPE_ID
, PAA.PERSON_ID
, PAAT.NAME
, HRL.MEANING
, PAAR.NAME
, PAA.OCCURRENCE
, PAA.DATE_NOTIFICATION
, PAA.DATE_PROJECTED_START
, PAA.TIME_PROJECTED_START
, PAA.DATE_PROJECTED_END
, PAA.TIME_PROJECTED_END
, PAA.DATE_START
, PAA.TIME_START
, PAA.DATE_END
, PAA.TIME_END
, PAA.ABSENCE_DAYS
, PAA.ABSENCE_HOURS
, PAA.ABS_INFORMATION1
, PPF2.FULL_NAME
, PPF2.EMPLOYEE_NUMBER
, PPF3.FULL_NAME
, PPF3.EMPLOYEE_NUMBER
, PAA.LAST_UPDATE_DATE
, PAA.LAST_UPDATED_BY
, PAA.LAST_UPDATE_LOGIN
, PAA.CREATED_BY
, PAA.CREATION_DATE
, PAA.ATTRIBUTE1
, PAA.ATTRIBUTE2
, PAA.ATTRIBUTE3
, PAA.ATTRIBUTE4
, PAA.ATTRIBUTE5
, PAA.ATTRIBUTE6
, PAA.ATTRIBUTE7
, PAA.ATTRIBUTE8
, PAA.ATTRIBUTE9
, PAA.ATTRIBUTE10
, PAA.ATTRIBUTE11
, PAA.ATTRIBUTE12
, PAA.ATTRIBUTE13
, PAA.ATTRIBUTE14
, PAA.ATTRIBUTE15
, PAA.ATTRIBUTE16
, PAA.ATTRIBUTE17
, PAA.ATTRIBUTE18
, PAA.ATTRIBUTE19
, PAA.ATTRIBUTE20
FROM PER_ALL_PEOPLE_F PPF1
, PER_ALL_PEOPLE_F PPF2
, PER_ALL_PEOPLE_F PPF3
, PER_ABSENCE_ATTENDANCES PAA
, PER_ABSENCE_ATTENDANCE_TYPES PAAT
, PER_ABS_ATTENDANCE_REASONS PAAR
, HR_LOOKUPS HRL
WHERE PPF1.PERSON_ID =PAA.PERSON_ID
AND PAA.ABSENCE_ATTENDANCE_TYPE_ID = PAAT.ABSENCE_ATTENDANCE_TYPE_ID
AND PAA.ABS_ATTENDANCE_REASON_ID = PAAR.ABS_ATTENDANCE_REASON_ID(+)
AND PAA.AUTHORISING_PERSON_ID=PPF2.PERSON_ID(+)
AND PAA.REPLACEMENT_PERSON_ID=PPF3.PERSON_ID(+)
AND PAA.BUSINESS_GROUP_ID=NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, PAA.BUSINESS_GROUP_ID)
AND HRL.LOOKUP_TYPE = 'ABSENCE_CATEGORY'
AND PAAT.ABSENCE_CATEGORY=HRL.LOOKUP_CODE
AND PPF1.EFFECTIVE_END_DATE BETWEEN NVL(PPF2.EFFECTIVE_START_DATE
, PPF1.EFFECTIVE_END_DATE)
AND NVL(PPF2.EFFECTIVE_END_DATE
, PPF1.EFFECTIVE_END_DATE)
AND PPF1.EFFECTIVE_END_DATE BETWEEN NVL(PPF3.EFFECTIVE_START_DATE
, PPF1.EFFECTIVE_END_DATE)
AND NVL(PPF3.EFFECTIVE_END_DATE
, PPF1.EFFECTIVE_END_DATE)
AND NVL(PAA.DATE_START
, PAA.DATE_PROJECTED_START) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE