API to Update Assignment Supervisor

DECLARE

   CURSOR fetch_det

   IS

      SELECT per.person_id,

             paaf.assignment_id,

             paaf.object_version_number,

             paaf.soft_coding_keyflex_id,

             paaf.people_group_id,

             paaf.special_ceiling_step_id

        FROM per_people_x per, per_assignments_x paaf

       WHERE     per.person_id = paaf.person_id

             AND per.person_type_id IN (1126, 1127)

             AND paaf.assignment_type = 'E'

             AND paaf.assignment_status_type_id IN (1, 2)

             AND paaf.primary_flag = 'Y'

             AND paaf.supervisor_id IS NULL;

   l_err_msg                  VARCHAR2 (1000) := NULL;

   l_concatenated_segments    VARCHAR2 (1000) := NULL;

   l_soft_coding_keyflex_id   NUMBER := 61;

   l_comment_id               NUMBER := NULL;

   l_effective_start_date     DATE := NULL;

   l_effective_end_date       DATE := NULL;

   l_no_managers_warning      BOOLEAN;

   l_other_manager_warning    BOOLEAN;

BEGIN

   FOR get_det IN fetch_det

   LOOP

      BEGIN

         l_concatenated_segments := NULL;

         l_comment_id := NULL;

         l_effective_start_date := NULL;

         l_effective_end_date := NULL;

         l_no_managers_warning := NULL;

         l_other_manager_warning := NULL;

         hr_assignment_api.

          update_emp_asg (

            p_validate                 => FALSE,

            p_effective_date           => SYSDATE,

            p_datetrack_update_mode    => 'CORRECTION',

            p_assignment_id            => get_det.assignment_id,

            p_object_version_number    => get_det.object_version_number,

            p_supervisor_id            => 1083 --  ,p_assignment_number            in     varchar2

                                              --  ,p_change_reason                in     varchar2

                                              --  ,p_comments                     in     varchar2

                                              --  ,p_date_probation_end           in     date

                                              --  ,p_default_code_comb_id         in     number

                                              --  ,p_frequency                    in     varchar2

                                              --  ,p_internal_address_line        in     varchar2

                                              --  ,p_manager_flag                 in     varchar2

                                              --  ,p_normal_hours                 in     number

                                              --  ,p_perf_review_period           in     number

                                              --  ,p_perf_review_period_frequency in     varchar2

                                              --  ,p_probation_period             in     number

                                              --  ,p_probation_unit               in     varchar2

                                              --  ,p_sal_review_period            in     number

                                              --  ,p_sal_review_period_frequency  in     varchar2

                                              --  ,p_set_of_books_id              in     number

                                              --  ,p_source_type                  in     varchar2

                                              --  ,p_time_normal_finish           in     varchar2

                                              --  ,p_time_normal_start            in     varchar2

                                              --  ,p_bargaining_unit_code         in     varchar2

                                              --  ,p_labour_union_member_flag     in     varchar2

                                              --  ,p_hourly_salaried_code         in     varchar2

                                              --  ,p_ass_attribute_category       in     varchar2

                                              --  ,p_ass_attribute1               in     varchar2

                                              --  ,p_ass_attribute2               in     varchar2

                                              --  ,p_ass_attribute3               in     varchar2

                                              --  ,p_ass_attribute4               in     varchar2

                                              --  ,p_ass_attribute5               in     varchar2

                                              --  ,p_ass_attribute6               in     varchar2

                                              --  ,p_ass_attribute7               in     varchar2

                                              --  ,p_ass_attribute8               in     varchar2

                                              --  ,p_ass_attribute9               in     varchar2

                                              --  ,p_ass_attribute10              in     varchar2

                                              --  ,p_ass_attribute11              in     varchar2

                                              --  ,p_ass_attribute12              in     varchar2

                                              --  ,p_ass_attribute13              in     varchar2

                                              --  ,p_ass_attribute14              in     varchar2

                                              --  ,p_ass_attribute15              in     varchar2

                                              --  ,p_ass_attribute16              in     varchar2

                                              --  ,p_ass_attribute17              in     varchar2

                                              --  ,p_ass_attribute18              in     varchar2

                                              --  ,p_ass_attribute19              in     varchar2

                                              --  ,p_ass_attribute20              in     varchar2

                                              --  ,p_ass_attribute21              in     varchar2

                                              --  ,p_ass_attribute22              in     varchar2

                                              --  ,p_ass_attribute23              in     varchar2

                                              --  ,p_ass_attribute24              in     varchar2

                                              --  ,p_ass_attribute25              in     varchar2

                                              --  ,p_ass_attribute26              in     varchar2

                                              --  ,p_ass_attribute27              in     varchar2

                                              --  ,p_ass_attribute28              in     varchar2

                                              --  ,p_ass_attribute29              in     varchar2

                                              --  ,p_ass_attribute30              in     varchar2

                                              --  ,p_title                        in     varchar2

                                              --  ,p_segment1                     in     varchar2

                                              --  ,p_segment2                     in     varchar2

                                              --  ,p_segment3                     in     varchar2

                                              --  ,p_segment4                     in     varchar2

                                              --  ,p_segment5                     in     varchar2

                                              --  ,p_segment6                     in     varchar2

                                              --  ,p_segment7                     in     varchar2

                                              --  ,p_segment8                     in     varchar2

                                              --  ,p_segment9                     in     varchar2

                                              --  ,p_segment10                    in     varchar2

                                              --  ,p_segment11                    in     varchar2

                                              --  ,p_segment12                    in     varchar2

                                              --  ,p_segment13                    in     varchar2

                                              --  ,p_segment14                    in     varchar2

                                              --  ,p_segment15                    in     varchar2

                                              --  ,p_segment16                    in     varchar2

                                              --  ,p_segment17                    in     varchar2

                                              --  ,p_segment18                    in     varchar2

                                              --  ,p_segment19                    in     varchar2

                                              --  ,p_segment20                    in     varchar2

                                              --  ,p_segment21                    in     varchar2

                                              --  ,p_segment22                    in     varchar2

                                              --  ,p_segment23                    in     varchar2

                                              --  ,p_segment24                    in     varchar2

                                              --  ,p_segment25                    in     varchar2

                                              --  ,p_segment26                    in     varchar2

                                              --  ,p_segment27                    in     varchar2

                                              --  ,p_segment28                    in     varchar2

                                              --  ,p_segment29                    in     varchar2

                                              --  ,p_segment30                    in     varchar2

                                              -- Bug fix for 944911

                                              -- p_concatenated_segments has been changed from in out to out

                                              -- Added new param p_concat_segments as in param

                                              --  ,p_concat_segments              in     varchar2

                                              --  ,p_supervisor_assignment_id     in     number

            ,

            p_concatenated_segments    => l_concatenated_segments,

            p_soft_coding_keyflex_id   => l_soft_coding_keyflex_id -- bug 2359997

                                                                  ,

            p_comment_id               => l_comment_id,

            p_effective_start_date     => l_effective_start_date,

            p_effective_end_date       => l_effective_end_date,

            p_no_managers_warning      => l_no_managers_warning,

            p_other_manager_warning    => l_other_manager_warning); 

         COMMIT;

         DBMS_OUTPUT.

          put_line (get_det.assignment_id || ' HAS BEEN UPDATED !!!!');

      EXCEPTION

         WHEN OTHERS

         THEN

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

            DBMS_OUTPUT.

             put_line (

               get_det.assignment_id || ' HAS  FAILED !!!! ' || 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;