SELECT ALL pet1.effective_start_date effective_start_date, pet1.effective_end_date effective_end_date,
pet2.effective_start_date effective_start_date1, pet2.effective_end_date effective_end_date1,
hxt_work_shifts.sht_id, hxt_shifts.id id1, hxt_work_shifts.week_day,
hxt_shifts.NAME NAME1, hxt_shifts.early_start, hxt_shifts.late_stop,
hxt_shifts.standard_start, hxt_shifts.standard_stop,
hxt_work_shifts.off_shift_prem_id,
pet2.element_name off_shift_premium,
hxt_work_shifts.shift_diff_ovrrd_id,
pet1.element_name shift_diff_override, hxt_work_shifts.tws_id
FROM
pay_element_types_f pet1,
pay_element_types_f pet2,
hxt_work_shifts,
hxt_shifts
WHERE hxt_shifts.id = hxt_work_shifts.sht_id
AND hxt_work_shifts.shift_diff_ovrrd_id = pet1.element_type_id(+)
AND SYSDATE BETWEEN NVL (pet1.effective_start_date, SYSDATE)
AND NVL (pet1.effective_end_date, SYSDATE)
AND hxt_work_shifts.off_shift_prem_id = pet2.element_type_id(+)
AND SYSDATE BETWEEN NVL (pet2.effective_start_date, SYSDATE)
AND NVL (pet2.effective_end_date,SYSDATE)
and hxt_work_shifts.tws_id=:ID |
