API to Update Job Definition ID in Jobs

DECLARE

   CURSOR c1

   IS

    SELECT pj.job_id,

             pj.object_version_number, pjd.segment1   new_segment1      , pjd.job_definition_id   new_job_definition_id, pj.name job_name ,    (SELECT segment1

                FROM per_job_definitions pjd1

               WHERE pjd1.job_definition_id = pj.job_definition_id)

                old_job_segment1,

                 (SELECT job_definition_id

                FROM per_job_definitions pjd1

               WHERE pjd1.job_definition_id = pj.job_definition_id) old_job_definition_id

        FROM fnd_lookup_values a, per_job_definitions  pjd

        , per_jobs pj

       WHERE     lookup_type = 'XXX_JOBS'

             AND a.language = 'US'

             AND a.lookup_code = pjd.segment1

             AND pjd.id_flex_num = 50414            

             AND pj.business_group_id = 809

             AND upper(pj.name) = upper(a.meaning)

             AND EXISTS

                    (SELECT 'X'

                       FROM per_assignments_x paaf

                      WHERE     pj.job_id = paaf.job_id

                            AND paaf.assignment_type = 'E'

                            AND paaf.assignment_status_type_id IN (1, 2)

                            AND paaf.assignment_type = 'E');

BEGIN

   FOR i IN c1

   LOOP

      BEGIN

         per_job_upd.upd (

            p_job_id                  => i.job_id,

            p_job_definition_id       => i.new_job_definition_id,

            p_object_version_number   => i.object_version_number,

            p_validate                => FALSE);

         DBMS_OUTPUT.put_line (

               'New Job Definition ID '

            || i.new_job_definition_id

            || ' update for '

            || i.job_id

            || '--> Old Job Definition ID:  '

            || i.old_job_definition_id);

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (

               'Inner Exception: ' || i.job_id || '-->  ' || SQLERRM);

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

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

END;