API to Create My List in Manager Self Service

DECLARE

   CURSOR get_my_list

   IS

      SELECT paaf.assignment_id, paaf.person_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 paaf.business_group_id = 886

             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 per.employee_number IN (1, 2, 3);

   l_owning_person_id   NUMBER := NULL;

   l_err_msg            VARCHAR2 (1000) := NULL;

   l_counter            NUMBER := 0;

   l_owning_list_t      per_interim_person_list_struct

                           := per_interim_person_list_struct ();

BEGIN

   -- Get Employee Person ID via User Name for which List has to be Created

   BEGIN

      SELECT paaf.person_id

        INTO l_owning_person_id

        FROM per_all_people_f per, per_all_assignments_f paaf, fnd_user fu

       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 paaf.business_group_id = 886

             AND fu.employee_id = per.person_id

             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 fu.user_name = 'BIJOYJ';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_owning_person_id := NULL;

   END;

   FOR fetch_details IN get_my_list

   LOOP

      l_counter := l_counter + 1;

      l_owning_list_t.EXTEND;

      l_owning_list_t (l_counter) :=

         per_interim_person_list_type (fetch_details.person_id,

                                       fetch_details.assignment_id,

                                       'Y');

      hr_util_misc_ss.populateinterimpersonlist (l_owning_list_t);

   END LOOP;

   hr_util_misc_ss.addtomylistfrominterimlist (l_owning_person_id);

   DBMS_OUTPUT.put_line ('Person Added in My List: ');

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      l_err_msg := SUBSTR (SQLERRM, 1, 1000);

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

END;