Query to Get Leave Balance for different Leaves


Query to Get Leave Balance for different Leaves
SELECT   a.employee_number staff_no,
a.full_name staff_name,
a.start_date date_of_join,
d.name Position,
c.parent_org_name department,
c.child_org_name section,
(SELECT   NVL (SUM (paa.absence_days), 0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
pat.absence_attendance_type_id
AND pat.absence_attendance_type_id IN (63, 79, 80)
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
annual_leave_utilized_2016,
— xxhy_leave_bal_fun (b.assignment_id) annual_leave_balance,
get_net_entitlement (b.assignment_id, 61, :p_to)
annual_leave_balance,
(SELECT   NVL (SUM (paa.absence_days), 0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
pat.absence_attendance_type_id
AND pat.absence_attendance_type_id = 1081
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
componsa_leave_util_2016,
get_net_entitlement (b.assignment_id, 1061, :p_to)
compen_leave_balance,
(SELECT   NVL (SUM (paa.absence_days), 0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
pat.absence_attendance_type_id
AND pat.absence_attendance_type_id = 77
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
Sick_leave_util_2016,
(SELECT   NVL (SUM (paa.absence_days), 0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
pat.absence_attendance_type_id
AND pat.absence_attendance_type_id = 67
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
Emergency_leave_util_2016,(SELECT   NVL (SUM (paa.absence_days),
0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
pat.absence_attendance_type_id
AND pat.absence_attendance_type_id not in ( 67,77,63, 79,
80)
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
other_leave_util_2016
— xxhy_leave_bal_compen_fun (b.assignment_id) compen_leave_balance
FROM   per_all_people_f a,
per_all_assignments_f b,
xxhw_organization_hierarchy_v1 c,
per_positions d
WHERE       1 = 1
AND a.person_id = b.person_id               — and a.person_id=1102
AND b.organization_id = c.child_org_id
AND TRUNC (SYSDATE) BETWEEN a.effective_start_date
AND  a.effective_end_date
AND TRUNC (SYSDATE) BETWEEN b.effective_start_date
AND  b.effective_end_date
AND TRUNC (SYSDATE) BETWEEN c.effective_date_from
AND  c.effective_date_to
AND a.current_employee_flag = ‘Y’
–AND a.employee_number LIKE ‘004014’
AND b.assignment_type = ‘E’
AND b.position_id(+) = d.position_id
–and c.parent_org_name=’Telecom unit_DT’
ORDER BY   1

Advertisements

About pacesettergraam

A good and realistic person

Posted on December 8, 2016, in Oracle, oracle apps and tagged , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: