API For Element Entry at Assignment Screen

API for Element Entry at Assignment Screen.

API TO Create Element Entry for Eligible Employees

DECLARE

   v_asg_id                 NUMBER         := NULL;

   v_effective_start_date   DATE           := NULL;

   v_effective_end_date     DATE           := NULL;

   v_element_entry_id       NUMBER         := NULL;

   v_ovn                    NUMBER         := NULL;

   v_asg_no                 NUMBER         := NULL;

   v_warning                BOOLEAN        := NULL;

   l_errm                   VARCHAR2 (500) := NULL;

   v_element_link_id        NUMBER         := NULL;

   CURSOR eligible_employee

   IS

      SELECT pasf.assignment_number, pasf.assignment_id,

             pasf.effective_start_date, pasf.effective_end_date

        FROM apps.per_all_assignments_f pasf,

             apps.per_all_people_f papf,

             apps.pay_people_groups ppg,

             apps.fnd_flex_value_sets ffvs,

             apps.fnd_flex_values ffv,

             apps.fnd_flex_values_tl ffvtl,

             apps.hr_lookups hl,

             apps.per_jobs pj,

             apps.per_grades pg,

             apps.per_grade_definitions pgd,

             apps.per_job_definitions pjd,

             apps.per_periods_of_service ppos

       WHERE pasf.person_id = papf.person_id

         AND '29-JAN-2015' BETWEEN pasf.effective_start_date

                               AND pasf.effective_end_date

         AND '29-JAN-2015' BETWEEN papf.effective_start_date

                               AND papf.effective_end_date

         AND pasf.primary_flag = 'Y'

         AND pasf.assignment_type = 'E'

         AND papf.person_type_id IN (66, 67)

         AND pasf.payroll_id IS NOT NULL

         AND pasf.payroll_id = 62

         AND pj.job_id = pasf.job_id

         AND pg.grade_id = pasf.grade_id

         AND pgd.grade_definition_id = pg.grade_definition_id

         AND pjd.job_definition_id = pj.job_definition_id

         AND ppos.period_of_service_id = pasf.period_of_service_id

         AND ppg.people_group_id = pasf.people_group_id

         AND ffvs.flex_value_set_name = 'CONTRACT_TYPE_D'

         AND ffv.flex_value_set_id = ffvs.flex_value_set_id

         AND ffvtl.flex_value_id = ffv.flex_value_id

         AND ffvtl.LANGUAGE = 'US'

         AND ppg.segment2 = ffv.flex_value

         AND hl.lookup_type(+) = 'NATIONALITY'

         AND hl.lookup_code(+) = papf.nationality

         AND (    ffvtl.description NOT IN

                      ('Locum', 'Employee on secondment (From the Hospital)')

              AND hl.meaning = 'Saudi Arabia'

              AND (   ppos.actual_termination_date IS NULL

                   OR ppos.actual_termination_date > '29-JAN-2015'

                  )

             )

         -- CHECK IF EMPLOYEE HAS TAKEN UNPAID LEAVE DURING THIS PERIOD THEN IT IS NOT ELIGIBLE

         AND papf.person_id NOT IN (

                SELECT papf.person_id

                  FROM per_all_people_f papf,

                       per_all_assignments_f paaf,

                       per_absence_attendances paa,

                       per_absence_attendance_types paat

                 WHERE papf.person_id = paaf.person_id

                   AND papf.current_employee_flag = 'Y'

                   AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

                                           AND papf.effective_end_date

                   AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

                                           AND paaf.effective_end_date

                   AND paaf.primary_flag = 'Y'

                   AND paaf.assignment_type = 'E'

                   AND paaf.payroll_id = 62

                   AND paaf.assignment_status_type_id IN (1, 2)

                   AND papf.person_id = paa.person_id

                   AND paa.absence_attendance_type_id =

                                               paat.absence_attendance_type_id

                   AND paat.NAME = 'Unpaid / Unauthorized Leave'

                   AND (   (    paa.date_start <= TO_DATE ('21-JAN-2015')

                            AND paa.date_end >= TO_DATE ('19-FEB-2015')

                           )

                        OR (   paa.date_start BETWEEN TO_DATE ('21-JAN-2015')

                                                  AND TO_DATE ('19-FEB-2015')

                            OR paa.date_end BETWEEN TO_DATE ('21-JAN-2015')

                                                AND TO_DATE ('19-FEB-2015')

                           )

                       )

                UNION

                SELECT papf.person_id

                  FROM hr_api_transactions trn,

                       per_all_people_f papf,

                       per_all_assignments_f paaf

                 WHERE transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'

                   AND selected_person_id = papf.person_id

                   AND trn.assignment_id = paaf.assignment_id

                   AND papf.current_employee_flag = 'Y'

                   AND paaf.primary_flag = 'Y'

                   AND paaf.assignment_type = 'E'

                   AND paaf.payroll_id = 62

                   AND paaf.assignment_status_type_id IN (1, 2)

                   AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

                                           AND papf.effective_end_date

                   AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

                                           AND paaf.effective_end_date

                   AND status = 'Y'

                   AND transaction_identifier = 'ABSENCES'

                   AND apps.ameutil_pkg.get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'AttributeCategory'

                                                          ) =

                                                 'Unpaid / Unauthorized Leave'

                   AND (   (    TO_DATE

                                   (apps.ameutil_pkg.get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'DateStart'

                                                          ),

                                    'RRRR-MM-DD'

                                   ) <= TO_DATE ('21-JAN-2015')

                            AND TO_DATE

                                   (apps.ameutil_pkg.get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'DateEnd'

                                                          ),

                                    'RRRR-MM-DD'

                                   ) >= TO_DATE ('19-FEB-2015')

                           )

                        OR (   TO_DATE

                                  (apps.ameutil_pkg.get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'DateStart'

                                                          ),

                                   'RRRR-MM-DD'

                                  ) BETWEEN TO_DATE ('21-JAN-2015')

                                        AND TO_DATE ('19-FEB-2015')

                            OR TO_DATE

                                  (apps.ameutil_pkg.kfsh_get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'DateEnd'

                                                          ),

                                   'RRRR-MM-DD'

                                  ) BETWEEN TO_DATE ('21-JAN-2015')

                                        AND TO_DATE ('19-FEB-2015')

                           )

                       ))

         -- CHECK IF EMPLOYEE HAS TAKEN Accompany Leave DURING THIS PERIOD THEN IT IS NOT ELIGIBLE

         AND papf.person_id NOT IN (

                SELECT papf.person_id

                  FROM per_all_people_f papf,

                       per_all_assignments_f paaf,

                       per_absence_attendances paa,

                       per_absence_attendance_types paat

                 WHERE papf.person_id = paaf.person_id

                   AND papf.current_employee_flag = 'Y'

                   AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

                                           AND papf.effective_end_date

                   AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

                                           AND paaf.effective_end_date

                   AND paaf.primary_flag = 'Y'

                   AND paaf.assignment_type = 'E'

                   AND paaf.assignment_status_type_id IN (1, 2)

                   AND papf.person_id = paa.person_id

                   AND paaf.payroll_id = 62

                   AND paa.absence_attendance_type_id =

                                               paat.absence_attendance_type_id

                   AND paat.NAME = 'Accompany Leave - Paid'

                   AND (   (    paa.date_start <= TO_DATE ('21-JAN-2015')

                            AND paa.date_end >= TO_DATE ('19-FEB-2015')

                           )

                        OR (   paa.date_start BETWEEN TO_DATE ('21-JAN-2015')

                                                  AND TO_DATE ('19-FEB-2015')

                            OR paa.date_end BETWEEN TO_DATE ('21-JAN-2015')

                                                AND TO_DATE ('19-FEB-2015')

                           )

                       )

                UNION

                SELECT papf.person_id

                  FROM hr_api_transactions trn,

                       per_all_people_f papf,

                       per_all_assignments_f paaf

                 WHERE transaction_ref_table = 'PER_ABSENCE_ATTENDANCES'

                   AND selected_person_id = papf.person_id

                   AND trn.assignment_id = paaf.assignment_id

                   AND papf.current_employee_flag = 'Y'

                   AND paaf.primary_flag = 'Y'

                   AND paaf.assignment_type = 'E'

                   AND paaf.payroll_id = 62

                   AND paaf.assignment_status_type_id IN (1, 2)

                   AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

                                           AND papf.effective_end_date

                   AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

                                           AND paaf.effective_end_date

                   AND status = 'Y'

                   AND transaction_identifier = 'ABSENCES'

                   AND apps.ameutil_pkg.kfsh_get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'AttributeCategory'

                                                          ) =

                                                      'Accompany Leave - Paid'

                   AND (   (    TO_DATE

                                   (apps.ameutil_pkg.get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'DateStart'

                                                          ),

                                    'RRRR-MM-DD'

                                   ) <= TO_DATE ('21-JAN-2015')

                            AND TO_DATE

                                   (apps.ameutil_pkg.kfsh_get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'DateEnd'

                                                          ),

                                    'RRRR-MM-DD'

                                   ) >= TO_DATE ('19-FEB-2015')

                           )

                        OR (   TO_DATE

                                  (apps.ameutil_pkg.kfsh_get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'DateStart'

                                                          ),

                                   'RRRR-MM-DD'

                                  ) BETWEEN TO_DATE ('21-JAN-2015')

                                        AND TO_DATE ('19-FEB-2015')

                            OR TO_DATE

                                  (apps.ameutil_pkg.kfsh_get_trn_attr_value_f

                                                          (trn.transaction_id,

                                                           'DateEnd'

                                                          ),

                                   'RRRR-MM-DD'

                                  ) BETWEEN TO_DATE ('21-JAN-2015')

                                        AND TO_DATE ('19-FEB-2015')

                           )

                       ));

BEGIN

   SELECT element_link_id

     INTO v_element_link_id

     FROM pay_element_links_f pelf, pay_element_types_f petf

    WHERE TRUNC (SYSDATE) BETWEEN pelf.effective_start_date

                              AND pelf.effective_end_date

      AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date

                              AND petf.effective_end_date

      AND pelf.element_type_id = petf.element_type_id

      AND petf.element_name = 'KING_SALMAN_BONUS'

      AND pelf.payroll_id = 23;

   FOR i IN eligible_employee

   LOOP

      v_effective_start_date := NULL;

      v_effective_end_date := NULL;

      v_ovn := NULL;

      l_errm := NULL;

      BEGIN

         pay_element_entry_api.create_element_entry

                 (p_validate                   => FALSE,

                  p_effective_date             => '21-JAN-2015',

                                                     -- i.effective_start_date

                  p_business_group_id          => 44,

                  p_assignment_id              => i.assignment_id,

                  p_element_link_id            => v_element_link_id,

                  p_entry_type                 => 'E',

                  p_effective_start_date       => v_effective_start_date,

                  p_effective_end_date         => v_effective_end_date,

                  p_element_entry_id           => v_element_entry_id,

                  p_object_version_number      => v_ovn,

                  p_create_warning             => v_warning

                 );

         COMMIT;

         INSERT INTO sal_bonus

              VALUES (i.assignment_number, 'SUCCESS', 'RAMADAN',

                      TRUNC (SYSDATE));

      EXCEPTION

         WHEN OTHERS

         THEN

            l_errm := SQLERRM;

            INSERT INTO salm_bonus

                 VALUES (NULL, l_errm, 'RAMADAN', TRUNC (SYSDATE));

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      l_errm := SQLERRM;

      INSERT INTO salm_bonus

           VALUES (NULL, l_errm, 'RAMADAN', TRUNC (SYSDATE));

END;

/