Rem drv: <migrate type="data_upgrade" version="10.2.0.0" pos="credentials/credentials_data_upgrade.sql+"/>
Rem
Rem $Header: jobs_data_upgrade.sql 26-jul-2005.19:32:12 chyu Exp $
Rem
Rem jobs_data_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      jobs_data_upgrade.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    chyu        07/26/05 - modifying the migrate header position 
Rem    chyu        07/18/05 - updating the new rep manager header 
Rem    skini       07/09/05 - Cross-merge beta fixes 
Rem    skini       05/09/05 - Workaround incorrectly computed 
Rem                           created_at_submit 
Rem    rpinnama    05/12/05 - Move callback registrations to jobs_init 
Rem    dsahrawa    04/20/05 - use pre instead of post target delete callback 
Rem    skini       03/21/05 - Fix issues with data upgrade 
Rem    skini       03/15/05 - Fix buf 
Rem    skini       02/14/05 - Continue migration 
Rem    skini       02/10/05 - Jobs upgrade 
Rem    rpinnama    02/06/05 - rpinnama_add_upgrade_scripts
Rem    rpinnama    02/04/05 - Created
Rem


Rem put index compression here since we need to call a plsql procedure to do it.
BEGIN
 EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_JOB_PARAMETER_IDX01', 1, FALSE);
 EMD_MAINTENANCE.COMPRESS_INDEX('PK_MGMT_JOB_PARAM', 2, FALSE);
END;
/

-- Delete the unused callback
BEGIN
  EM_TARGET.del_tgt_post_deletion_callback('MGMT_JOB_ENGINE.handle_target_delete');

  DELETE FROM MGMT_TARGET_DELETE_EXCEPTIONS
   WHERE table_name = 'SMP_MGMT_JOB_TARGET';

  COMMIT;
END;
/

@&EM_SQL_ROOT/core/v102010/jobs/jobs_init.sql



REM Compute the expected_start_time column for all executions
UPDATE  MGMT_JOB_EXEC_SUMMARY
SET     expected_start_time=MGMT_GLOBAL.to_utc(scheduled_time, timezone_region);

REM Insert waiting executions for all current jobs with recurring schedules
DECLARE
l_schedule MGMT_JOB_SCHEDULE_RECORD;
l_next_execution_time DATE;
l_active NUMBER;
l_target_guid MGMT_TARGETS.target_guid%TYPE;
l_next_exec_id MGMT_JOB_EXEC_SUMMARY.execution_id%TYPE;
l_next_start_time DATE;
BEGIN
    -- Note: there can be just one scheduled or running execution
    -- per (execution_id, target_list_index) combination
    FOR CREC IN (SELECT j.job_id, e.execution_id, e.target_list_index,
                        e.start_time, e.timezone_region, 
                        ft.target_guid
                 FROM   MGMT_JOB j, MGMT_JOB_SCHEDULE js, 
                        MGMT_JOB_EXEC_SUMMARY e,
                        MGMT_JOB_FLAT_TARGETS ft
                 WHERE  j.schedule_id=js.schedule_id
                 AND    j.job_id=e.job_id
                 AND    e.job_id=ft.job_id(+)
                 AND    e.target_list_index=ft.target_list_index(+)
                 AND    status IN (MGMT_JOB_ENGINE.SCHEDULED_STATUS,
                                   MGMT_JOB_ENGINE.EXECUTING_STATUS,
                                   MGMT_JOB_ENGINE.SUSPENDED_STATUS,
                                   MGMT_JOB_ENGINE.AGENTDOWN_STATUS,
                                   MGMT_JOB_ENGINE.STOP_PENDING_STATUS,
                                   MGMT_JOB_ENGINE.SUSPEND_PENDING_STATUS,
                                   MGMT_JOB_ENGINE.SUSPENDED_LOCK_STATUS,
                                   MGMT_JOB_ENGINE.SUSPENDED_EVENT_STATUS,
                                   MGMT_JOB_ENGINE.SUSPENDED_BLACKOUT_STATUS)
                 AND    js.frequency_code != MGMT_JOB_ENGINE.ONE_TIME_FREQUENCY_CODE) LOOP
      BEGIN
        l_schedule := MGMT_JOB_ENGINE.get_current_schedule(crec.job_id);
        
        l_next_start_time := 
            MGMT_JOB_ENGINE.get_next_execution_time(l_schedule,
                                                    crec.start_time,
                                                    crec.timezone_region);

        -- l_next_start_time could be null if this is the last
        -- execution
        IF l_next_start_time IS NOT NULL THEN
            l_next_exec_id := 
                MGMT_JOB_ENGINE.schedule_execution(crec.job_id,
                                   null,
                                   null,
                                   null,
                                   l_schedule,
                                   crec.target_list_index,
                                   crec.target_guid,
                                   crec.start_time,
                                   null,
                                   l_next_start_time,
                                   crec.start_time,
                                   MGMT_JOB_ENGINE.WAITING_STATUS,
                                   MGMT_JOB_ENGINE.START_WAITING_STATUS);
        END IF;

        COMMIT;

        EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK;
                DBMS_OUTPUT.put_line('JOB upgrade: could not upgrade execution ' || crec.execution_id);
        END;        
    END LOOP;
END;
/

-- Register the OMS failover callback for the "interactive" job area
BEGIN
  MGMT_FAILOVER.REGISTER_CALLBACK('MGMT_HTTP_SESSION.handle_oms_death');
  COMMIT;
END;
/

-- Workaround bug 4349191: fix incorrectly computed created_at_submit flag
-- ALL submission-time parameters can be marked create_at_submit=1 since
-- we do not have any way of generating a submission-time parameter at
-- run-time
UPDATE  MGMT_JOB_PARAMETER
SET     created_at_submit=1
WHERE   execution_id LIKE '000000%';

COMMIT;    

UPDATE  MGMT_JOB_PARAMETER p
SET     created_at_submit=1
WHERE   execution_id NOT LIKE '000000%'
AND     EXISTS
        (SELECT 1
         FROM   MGMT_JOB_PARAMETER
         WHERE  job_id=p.job_id
         AND    execution_id LIKE '000000%'
         AND    parameter_name=p.parameter_name);

COMMIT;