API to Suspend Assignment

DECLARE

   CURSOR get_details

   IS

      SELECT paaf.assignment_id,

             paaf.object_version_number,

             paaf.soft_coding_keyflex_id

        FROM per_all_people_f per, per_all_assignments_f paaf

       WHERE     per.person_id = paaf.person_id

             AND paaf.primary_flag = 'Y'

             AND paaf.assignment_type = 'E'

             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 per.employee_number = '5925';

   l_err_msg                      VARCHAR2 (1000) := NULL;

   l_cagr_grade_def_id            NUMBER := NULL;

   l_cagr_concatenated_segments   VARCHAR2 (500);

   l_concatenated_segments        VARCHAR2 (500);

   l_soft_coding_keyflex_id       VARCHAR2 (500);

   l_comment_id                   VARCHAR2 (500);

   l_effective_start_date         DATE;

   l_effective_end_date           DATE;

   l_no_managers_warning          BOOLEAN;

   l_other_manager_warning        BOOLEAN;

BEGIN

   --Initialize Session

   fnd_global.

    apps_initialize (user_id => 1092,                        -- DOPA_MIGRATION

                                     resp_id => 50717, -- DOPA HRMS Super User

                                                      resp_appl_id => 800 -- Human Resources

                                                                         );

   FOR i IN get_details

   LOOP

      BEGIN

         l_effective_start_date := NULL;

         l_effective_end_date := NULL;

         hr_assignment_api.

          suspend_emp_asg (

            p_validate                    => FALSE,

            p_effective_date              => TO_DATE ('01-APR-2015'),

            p_datetrack_update_mode       => 'UPDATE',

            p_assignment_id               => i.assignment_id,

            p_change_reason               => NULL,

            p_object_version_number       => i.object_version_number,

            p_assignment_status_type_id   => 2,

            p_effective_start_date        => l_effective_start_date,

            p_effective_end_date          => l_effective_end_date);

         COMMIT;

         DBMS_OUTPUT.put_line (i.assignment_id || ' has been Suspended !!!');

      EXCEPTION

         WHEN OTHERS

         THEN

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

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

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

      DBMS_OUTPUT.put_line ('MAIN EXCEPTION !!!! ' || l_err_msg);

END;