employee absence plan & type balance

-----------------employee absence plan & type balance-----------------------

----------parameters

1.p_legal_entity 2.p_department 3.p_grade 4.p_employee 5.p_plan_name 6.p_effective_start_date 7.p_effective_end_date

----------------list of values

------employee name  lovSELECT DISTINCT ppnf.display_name

FROM            per_person_names_f ppnf

WHERE           Trunc(sysdate) BETWEEN Trunc(ppnf.effective_start_date) AND             Trunc(ppnf.effective_end_date)

AND             ppnf.name_type='GLOBAL'

------plan name  lovSELECT DISTINCT NAME

FROM            (

                       SELECT NAME

                       FROM   anc_absence_plans_vl

                       WHERE  1 = 1

                       AND    plan_status = 'A'

                       UNION

                       SELECT NAME

                       FROM   anc_absence_types_vl

                       WHERE  1 = 1

                       AND    status = 'A')

         -------grade  lovSELECT DISTINCT NAME

         FROM            per_grades_f_vl

         --------department name lovSELECT DISTINCT NAME

         FROM            per_departments

         -------legal entity lovSELECT DISTINCT haou1.NAME

         FROM            hr_organization_units_f_tl haou1 ,

                         per_all_assignments_f paam

         WHERE           1=1

         AND             Trunc(sysdate) BETWEEN Trunc(paam.effective_start_date) AND             Trunc(paam.effective_end_date)

         AND             paam.assignment_type IN ('E',

                                                  'C')

         AND             paam.primary_flag='Y'

         AND             paam.system_person_type='EMP'

         AND             paam.effective_latest_change='Y'

         AND             paam.legal_entity_id=haou1.organization_id

         AND             Trunc(sysdate) BETWEEN Trunc(haou1.effective_start_date) AND             Trunc(haou1.effective_end_date)

         AND             haou1.language='US'

                         --and rownum<=1

--------------------------main query

SELECT *

FROM  ((SELECT papf.person_number,

               PAPF.person_id,

               ppnf.display_name                                person_name,

               houft.name                                       legalemployer,

               ppos.worker_number,

               houft.organization_id,

               aapft.name                                       AS PLAN_NAME,

               aapft.absence_plan_id,

               To_char(aapft.effective_start_date, 'MM/DD/YYYY')PLAN_START_DATE,

               To_char(aapft.effective_end_date, 'MM/DD/YYYY')  PLAN_END_DATE,

               To_char(appe.enrt_st_dt, 'MM/DD/YYYY')

               Enrollment_Start_Date,

               To_char(appe.last_accrual_run, 'MM/DD/YYYY')

                      Balance_Calculation_Date,

               appe.last_accrual_run,

               Nvl(apae.end_bal, 0)

               Absence_Plan_Balance,

               Decode (aap.plan_uom, 'D', 'Days',

                                     'H', 'Hours')              plan_balance_dh,

               (SELECT To_char(Trunc(SYSDATE), 'MM/DD/YYYY')

                FROM   dual)                                    SYS,

               (SELECT user_status

                FROM   per_assignment_status_types_vl

                WHERE  assignment_status_type_id =

                       paam.assignment_status_type_id

                       AND ROWNUM <= 1)

               assignment_status,

               (SELECT name

                FROM   per_departments

                WHERE  organization_id = paam.organization_id

                       AND Trunc(Trunc(SYSDATE)) BETWEEN

                           Trunc(effective_start_date) AND Trunc(

                           effective_end_date)

                       AND ROWNUM <= 1)                         department,

               (SELECT organization_id

                FROM   per_departments

                WHERE  organization_id = paam.organization_id

                       AND Trunc(Trunc(SYSDATE)) BETWEEN

                           Trunc(effective_start_date) AND Trunc(

                           effective_end_date)

                       AND ROWNUM <= 1)                         department_ID,

               (SELECT name

                FROM   per_grades_f_vl

                WHERE  grade_id = paam.grade_id

                       AND Trunc(Trunc(SYSDATE)) BETWEEN

                           Trunc(effective_start_date) AND Trunc(

                           effective_end_date)

                       AND ROWNUM <= 1)                         grade,

               (SELECT grade_id

                FROM   per_grades_f_vl

                WHERE  grade_id = paam.grade_id

                       AND Trunc(Trunc(SYSDATE)) BETWEEN

                           Trunc(effective_start_date) AND Trunc(

                           effective_end_date)

                       AND ROWNUM <= 1)                         grade_ID,

               (SELECT start_date

                FROM   anc_per_abs_entries f

                WHERE  person_id = paam.person_id

                       AND period_of_service_id = ppos.period_of_service_id

                       AND ROWNUM <= 1)

               absence_start_date,

               (SELECT end_date

                FROM   anc_per_abs_entries f

                WHERE  person_id = paam.person_id

                       AND period_of_service_id = ppos.period_of_service_id

                       AND ROWNUM <= 1)                         absence_end_date

        FROM   per_all_people_f papf,

               per_person_names_f ppnf,

               per_all_assignments_m paam,

               per_periods_of_service ppos,

               hr_organization_units_f_tl houft,

               anc_per_plan_enrollment appe,

               anc_absence_plans_f_tl aapft,

               anc_per_accrual_entries apae,

               anc_absence_plans_vl aap

        WHERE  1 = 1

               AND ppnf.person_id = papf.person_id

               AND ppnf.name_type (+) = 'GLOBAL'

               AND papf.person_id = paam.person_id

               AND paam.primary_flag = 'Y'

               AND paam.assignment_type = 'E'

               AND paam.effective_latest_change = 'Y'

               AND ppos.period_of_service_id = paam.period_of_service_id

               AND ppos.date_start = (SELECT Max(date_start)

                                      FROM   per_periods_of_service ppos1

                                      WHERE  1 = 1

                                             AND ppos1.person_id =

                                                 papf.person_id

                                             AND period_type = 'E')

               AND houft.organization_id = paam.legal_entity_id

               AND houft.LANGUAGE = 'US'

               AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

                                          papf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

                                          ppnf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                          paam.effective_end_date

               AND Trunc(SYSDATE) BETWEEN houft.effective_start_date AND

                                          houft.effective_end_date

               AND appe.person_id(+) = papf.person_id

               AND Trunc(SYSDATE) BETWEEN appe.enrt_st_dt AND appe.enrt_end_dt

               AND aapft.absence_plan_id = appe.plan_id

               AND aapft.LANGUAGE = 'US'

               AND Trunc(SYSDATE) BETWEEN aapft.effective_start_date AND

                                          aapft.effective_end_date

               AND apae.per_plan_enrt_id = appe.per_plan_enrt_id

               AND apae.accrual_period = appe.last_accrual_run

               AND aapft.absence_plan_id = aap.absence_plan_id

               AND aap.LANGUAGE = 'US'

               AND Trunc(SYSDATE) BETWEEN aap.effective_start_date AND

                                          aap.effective_end_date

               AND aap.plan_status = 'A'

       --and apae.end_bal is not null

       --AND papf.person_number = '485'

       )

       UNION

       (SELECT papf.person_number,

               PAPF.person_id,

               ppnf.display_name                       person_name,

               houft.name                              legalemployer,

               ppos.worker_number,

               houft.organization_id,

               aatl.name                               AS PLAN_NAME,

               aatl.absence_type_id,

               To_char(apaes.start_date, 'MM/DD/YYYY') PLAN_START_DATE,

               To_char(apaes.end_date, 'MM/DD/YYYY')   PLAN_END_DATE,

               NULL                                    Enrollment_Start_Date,

               --to_char(appe.enrt_st_dt,'DD/MM/YYYY') Enrollment_Start_Date,

               To_char(apaes.start_date, 'MM/DD/YYYY') Balance_Calculation_Date,

               apaes.start_date                        last_accrual_run,

               Nvl(CASE

                     WHEN aatl.name = 'Annual Leave' THEN 30

                     WHEN aatl.name = 'Marriage Leave' THEN 5

                     ELSE aatl.max_duration

                   END - apaes.duration, 0)            Absence_Plan_Balance,

               Decode (apaes.uom, 'D', 'Days',

                                  'H', 'Hours')        plan_balance_dh,

               (SELECT To_char(Trunc(SYSDATE), 'MM/DD/YYYY')

                FROM   dual)                           SYS,

               (SELECT user_status

                FROM   per_assignment_status_types_vl

                WHERE  assignment_status_type_id =

                       paam.assignment_status_type_id

                       AND ROWNUM <= 1)                assignment_status,

               (SELECT name

                FROM   per_departments

                WHERE  organization_id = paam.organization_id

                       AND Trunc(Trunc(SYSDATE)) BETWEEN

                           Trunc(effective_start_date) AND Trunc(

                           effective_end_date)

                       AND ROWNUM <= 1)                department,

               (SELECT organization_id

                FROM   per_departments

                WHERE  organization_id = paam.organization_id

                       AND Trunc(Trunc(SYSDATE)) BETWEEN

                           Trunc(effective_start_date) AND Trunc(

                           effective_end_date)

                       AND ROWNUM <= 1)                department_ID,

               (SELECT name

                FROM   per_grades_f_vl

                WHERE  grade_id = paam.grade_id

                       AND Trunc(Trunc(SYSDATE)) BETWEEN

                           Trunc(effective_start_date) AND Trunc(

                           effective_end_date)

                       AND ROWNUM <= 1)                grade,

               (SELECT grade_id

                FROM   per_grades_f_vl

                WHERE  grade_id = paam.grade_id

                       AND Trunc(Trunc(SYSDATE)) BETWEEN

                           Trunc(effective_start_date) AND Trunc(

                           effective_end_date)

                       AND ROWNUM <= 1)                grade_ID,

               (SELECT start_date

                FROM   anc_per_abs_entries f

                WHERE  person_id = paam.person_id

                       AND period_of_service_id = ppos.period_of_service_id

                       AND ROWNUM <= 1)                absence_start_date,

               (SELECT end_date

                FROM   anc_per_abs_entries f

                WHERE  person_id = paam.person_id

                       AND period_of_service_id = ppos.period_of_service_id

                       AND ROWNUM <= 1)                absence_end_date

        FROM   per_all_people_f papf,

               per_person_names_f ppnf,

               per_all_assignments_m paam,

               per_periods_of_service ppos,

               hr_organization_units_f_tl houft,

               anc_per_abs_entries apaes,

               anc_absence_types_vl aatl

        WHERE  1 = 1

               AND aatl.name NOT IN( 'Annual Leave', 'Short Leave' )

               AND apaes.period_of_service_id = ppos.period_of_service_id

               AND apaes.absence_type_id = aatl.absence_type_id

               AND ppnf.person_id = papf.person_id

               AND ppnf.name_type (+) = 'GLOBAL'

               AND papf.person_id = paam.person_id

               AND apaes.approval_status_cd = 'APPROVED'

               AND apaes.absence_status_cd != 'ORA_WITHDRAWN'

               AND paam.primary_flag = 'Y'

               AND paam.assignment_type = 'E'

               AND paam.effective_latest_change = 'Y'

               AND ppos.period_of_service_id = paam.period_of_service_id

               AND ppos.date_start = (SELECT Max(date_start)

                                      FROM   per_periods_of_service ppos1

                                      WHERE  1 = 1

                                             AND ppos1.person_id =

                                                 papf.person_id

                                             AND period_type = 'E')

               AND houft.organization_id = paam.legal_entity_id

               AND houft.LANGUAGE = 'US'

               AND aatl.LANGUAGE = 'US'

               AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

                                          papf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

                                          ppnf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                          paam.effective_end_date

               AND Trunc(SYSDATE) BETWEEN houft.effective_start_date AND

                                          houft.effective_end_date

               AND aatl.status = 'A'

       --AND papf.person_number = '485'

       )) a

WHERE  1 = 1

       AND ( a.legalemployer IN ( :p_legal_entity )

              OR Coalesce(:p_legal_entity, NULL)IS NULL )

       AND ( a.department IN ( :p_department )

              OR Coalesce(:p_department, NULL)IS NULL )

       AND ( A.grade IN ( :p_grade )

              OR Coalesce(:p_grade, NULL)IS NULL )

       AND ( a.plan_name IN ( :p_PLAN_NAME )

              OR Coalesce(:p_PLAN_NAME, NULL)IS NULL )

       AND ( a.person_name IN ( :p_employee )

              OR Coalesce(:p_employee, NULL)IS NULL )

       AND Trunc (a.last_accrual_run) BETWEEN

           Nvl(:P_EFFECTIVE_START_DATE, a.last_accrual_run) AND

               Nvl(:P_EFFECTIVE_END_DATE, a.last_accrual_run)

ORDER  BY a.worker_number ASC