API to Create Assignment Set and its Amendments

API to Create Assignment Set

DECLARE

   l_payroll_id            NUMBER := NULL;

   l_assignment_set_name   VARCHAR2 (500) := 'ASSIGNMENT_SET_DEMO_API';

   l_assignment_set_id     NUMBER := NULL;

BEGIN

   -- Get Payroll ID

   BEGIN

      SELECT payroll_id

        INTO l_payroll_id

        FROM pay_payrolls_f

       WHERE UPPER (payroll_name) = 'XXX MONTHLY PAYROLL'

             AND SYSDATE BETWEEN effective_start_date AND effective_end_date;

   EXCEPTION

      WHEN OTHERS

      THEN

         l_payroll_id := NULL;

   END;

   BEGIN

      hr_jp_ast_utility_pkg.

       create_asg_set (p_assignment_set_name   => l_assignment_set_name,

                       p_business_group_id     => 81,

                       p_payroll_id            => l_payroll_id,

                       p_assignment_set_id     => l_assignment_set_id);

      COMMIT;

      DBMS_OUTPUT.

       put_line (l_assignment_set_id || ' has been Created Successfully !!!');

   EXCEPTION

      WHEN OTHERS

      THEN

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

   END;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;

API to Create Assignment Amendments

DECLARE

   l_assignment_set_id   NUMBER := NULL;

   CURSOR c1

   IS

      SELECT paaf.assignment_id

        FROM per_all_people_f per, per_all_assignments_f paaf

       WHERE     per.person_id = paaf.person_id

             AND paaf.assignment_type = 'E'

             AND paaf.primary_flag = 'Y'

             AND paaf.assignment_status_type_id IN (1, 2)

             AND SYSDATE BETWEEN per.effective_start_date

                             AND per.effective_end_date

             AND SYSDATE BETWEEN paaf.effective_start_date

                             AND paaf.effective_end_date

             AND ROWNUM <= 10;

BEGIN

   -- Get Assignment Set ID

   BEGIN

      SELECT assignment_set_id

        INTO l_assignment_set_id

        FROM hr_assignment_sets

       WHERE assignment_set_name = 'ASSIGNMENT_SET_DEMO_API';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_assignment_set_id := NULL;

   END;

   FOR i IN c1

   LOOP

      BEGIN

         hr_jp_ast_utility_pkg.

          create_asg_set_amd (p_assignment_set_id    => l_assignment_set_id,

                              p_assignment_id        => i.assignment_id,

                              p_include_or_exclude   => 'I');

         COMMIT;

         DBMS_OUTPUT.

          put_line (i.assignment_id || ' has been Added Successfully !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

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

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;