Rem
Rem $Header: jobs_data_downgrade.sql 16-feb-2005.12:36:37 skini Exp $
Rem
Rem jobs_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      jobs_data_downgrade.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem       skini    02/16/05 - skini_job_bo_migration


SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

REM Remove all multitask jobs/cas
BEGIN
    FOR crec IN (SELECT job_id, is_corrective_action, is_library
                 FROM   MGMT_JOB j, MGMT_JOB_TYPE_MAX_VERSIONS jt,
                        MGMT_JOB_TYPE_INFO ji
                 WHERE  j.job_type=jt.job_type
                 AND    jt.job_type_major_version=jt.major_version
                 AND    jt.job_type_id=ji.job_type_id
                 AND    ji.job_type_category=MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_HIDDEN) LOOP
        IF crec.is_library=0 THEN
            MGMT_JOB_ENGINE.stop_all_executions_with_id(crec.job_id, true);
        END IF;

        IF crec.is_corrective_action=1 THEN
            MGMT_JOB_ENGINE.delete_ca(crec.job_id, 1);
        ELSE
            MGMT_JOB_ENGINE.delete_job(crec.job_id, 1);
        END IF;
    END LOOP;
END;
/

REM Remove all other cas
BEGIN
    FOR crec IN (SELECT job_id, is_library
                 FROM   MGMT_JOB j
                 WHERE  is_corrective_action=1) LOOP
        IF crec.is_library=0 THEN
            MGMT_JOB_ENGINE.stop_all_executions_with_id(crec.job_id, true);
        END IF;

        MGMT_JOB_ENGINE.delete_ca(crec.job_id, 1);
    END LOOP;
END;
/