Saturday, April 5, 2014

Oracle HRMS Query - Absence

SELECT   /*+ rule*/
            xx.person_id, xx.assignment_id, flv.meaning absence_category,
            c.NAME absence_type, flv1.meaning absence_reason,
            ABS.date_notification, ABS.date_projected_start,
            ABS.date_projected_end, ABS.date_start, ABS.date_end,
            ABS.absence_days, ABS.attribute1, ABS.attribute2, ABS.attribute3,
            ABS.attribute4, ABS.attribute5, ABS.attribute6, ABS.attribute7,
            ABS.attribute8, ABS.attribute9, ABS.attribute10, ABS.attribute11,
            ABS.attribute12, ABS.attribute13, ABS.attribute14,
            ABS.attribute15, ABS.attribute16, ABS.attribute17,
            ABS.attribute18, ABS.attribute19, ABS.attribute20,
           
            --, LOCATION,
            b.admission_code, b.admission_date, b.amendment_date,
            b.amendment_reason, b.concatenated_segments, b.contact_grade,
            b.contact_type, b.CONTEXT, b.discharge_date, b.disease_name,
            b.hospital_name, b.leave_amended, b.leave_salary_paid,
            b.physician_approved_accident, b.physician_name,
            b.resumption_date
       FROM per_absence_attendances ABS,
            per_absence_attendance_types c,
            per_abs_attendance_reasons d,
            per_absence_attendances_dfv b,
            fnd_lookup_values flv1,
            fnd_lookup_values flv,
            per_all_assignments_f xx                      --xxhr_zainiq_mv  xx
      WHERE ABS.person_id = xx.person_id
        AND ABS.ROWID = b.row_id
        AND ABS.absence_attendance_type_id = c.absence_attendance_type_id(+)
        AND ABS.abs_attendance_reason_id = d.abs_attendance_reason_id(+)
        AND d.NAME = flv1.lookup_code(+)
        AND flv1.lookup_type(+) = 'ABSENCE_REASON'
        AND flv.lookup_code(+) = c.absence_category
        AND flv.lookup_type(+) = 'ABSENCE_CATEGORY'
   ORDER BY ABS.person_id, absence_type, date_start DESC

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect