API to Update Element Entry

DECLARE

   l_effective_start_date   DATE := NULL;

   l_effective_end_date     DATE := NULL;

   l_update_warning         BOOLEAN;

   l_element_type_id        NUMBER := NULL;

   l_input_stop_salary_id   NUMBER := NULL;

   l_business_group_id      NUMBER := 2734;

   l_effective_date         DATE := TO_DATE ('01-NOV-2014');

   CURSOR c1

   IS

      SELECT *

        FROM pay_element_entries_f peef

       WHERE element_type_id = 23770

             AND effective_start_date >= TO_DATE ('01-OCT-2014');

BEGIN

   -- Get Element Type ID

   SELECT petf.element_type_id

     INTO l_element_type_id

     FROM pay_element_types_f petf

    WHERE SYSDATE BETWEEN petf.effective_start_date

                      AND petf.effective_end_date

          AND UPPER (petf.element_name) = 'STOP SALARY'

          AND petf.business_group_id = l_effective_date;

   -- Get Input Value ID

   SELECT pivf.input_value_id

     INTO l_input_stop_salary_id

     FROM pay_input_values_f pivf

    WHERE pivf.element_type_id = l_element_type_id

          AND UPPER (pivf.name) = 'STOP SALARY'; 

   FOR i IN c1

   LOOP

      BEGIN

         pay_element_entry_api.

          update_element_entry (

            p_validate                => FALSE,

            p_datetrack_update_mode   => 'UPDATE',

            p_effective_date          => l_effective_date,

            p_business_group_id       => l_business_group_id,

            p_element_entry_id        => i.element_entry_id,

            p_object_version_number   => i.object_version_number,

            p_input_value_id1         => l_input_stop_salary_id,

            p_entry_value1            => 'N',

            p_effective_start_date    => l_effective_start_date,

            p_effective_end_date      => l_effective_end_date,

            p_update_warning          => l_update_warning);

         COMMIT;

         DBMS_OUTPUT.

          put_line ('Element Entry has been Updated: ' || i.element_entry_id);

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.

             put_line (

               'Inner Exception: ' || SQLERRM || ' - ' || i.element_entry_id);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);

END;