Wednesday, January 22, 2014

API to Create SIT

PROCEDURE xxxx_create_passport_p
   IS
      CURSOR get_details
      IS
         SELECT *
           FROM xxxx_passport_sit_t
          WHERE process_flag = 'N';

      l_err_msg                     VARCHAR2 (500) := NULL;
      l_id_flex_num                 NUMBER (9) := NULL;
      l_analysis_criteria_id        NUMBER (9) := NULL;
      l_person_analysis_id          NUMBER (9) := NULL;
      l_pea_object_version_number   NUMBER (9) := NULL;
   BEGIN
      --Initialize Session
      fnd_global.
       apps_initialize (user_id => 2234,                     -- XXXX_MIGRATION
                                        resp_id => 50637, -- XXXX HRMS Manager
                                                         resp_appl_id => 800 -- Human Resouces
                                                                            );

      SELECT fifs.id_flex_num
        INTO l_id_flex_num
        FROM fnd_id_flex_structures fifs
       WHERE id_flex_structure_code = 'XXXX_PASSPORT_DETAILS';

      FOR fetch_details IN get_details
      LOOP
         l_err_msg := NULL;
         l_analysis_criteria_id := NULL;
         l_person_analysis_id := NULL;
         l_pea_object_version_number := NULL;

         BEGIN
            hr_sit_api.
             create_sit (
               p_validate                    => FALSE,
               p_person_id                   => fetch_details.person_id,
               p_business_group_id           => l_business_group_id_gl,
               p_id_flex_num                 => l_id_flex_num,
               p_effective_date              => SYSDATE,
               p_comments                    => NULL,
               p_date_from                   => fetch_details.start_date,
               p_date_to                     => fetch_details.end_date,
               p_segment1                    => fetch_details.passport_number,
               p_segment2                    => NULL,
               p_segment3                    => fetch_details.place_of_issue,
               p_segment4                    => fetch_details.issue_date,
               p_segment5                    => fetch_details.expiry_date,
               p_analysis_criteria_id        => l_analysis_criteria_id,
               p_person_analysis_id          => l_person_analysis_id,
               p_pea_object_version_number   => l_pea_object_version_number);


            UPDATE xxxx_passport_sit_t dps
               SET dps.process_flag = 'Y', dps.err_msg = NULL
             WHERE dps.emp_no = fetch_details.emp_no;

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_err_msg := SQLERRM;

               UPDATE xxxx_passport_sit_t dps
                  SET dps.process_flag = 'N', dps.err_msg = l_err_msg
                WHERE dps.emp_no = fetch_details.emp_no;

               COMMIT;
         END;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_err_msg := SQLERRM;
         DBMS_OUTPUT.put_line ('Main Exception: ' || l_err_msg);

   END xxxx_create_passport_p;

No comments:

Post a Comment