Rem drv: <migrate type="schema_downgrade" version="10.1.0.4"/>
Rem
Rem $Header: jobs_schema_downgrade.sql 01-dec-2006.21:53:49 aptrived Exp $
Rem
Rem jobs_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      jobs_schema_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    aptrived    12/01/06 - Adding CASCADE DROP INDEX for PK_JOB_TYPE_INFO
Rem    chyu        07/19/05 - modifying the new rep manager header 
Rem    nqureshi    06/21/05 - adding entry for target props 
Rem    skini       06/06/05 - grabtrans 'skini_bug-4377344' 
Rem    dsahrawa    05/25/05 - fix bug 4388845, diffs from upgrade 
Rem    skini       04/15/05 - Add broken ca tables 
Rem    pkantawa    05/04/05 - Fix bug 4348693: upgrade MGMT_JOB_STEP_RECORD 
Rem    pkantawa    03/25/05 - remove runs/exec related types 
Rem    skini       03/03/05 - Add valueOf column 
Rem    scgrover    02/17/05 - reorder index 
Rem    skini       02/16/05 - skini_job_bo_migration
Rem    skini       02/16/05 - Add type migration 
Rem    skini       02/10/05 - Jobs upgrade 
Rem    dcawley     02/08/05 - Add corrective actions table 
Rem    rpinnama    02/06/05 - rpinnama_add_upgrade_scripts
Rem    rpinnama    02/04/05 - Created
Rem

REM Downgrade the step record type
ALTER TYPE MGMT_JOB_STEP_RECORD MODIFY ATTRIBUTE (step_type NUMBER(1)) CASCADE;

Rem Downgrade job pl/sql types
DELETE TYPE MGMT_JOB_NOTIF_EMAIL_SCH_INFO;
DELETE TYPE MGMT_JOB_TASK_TGT_ARRAY;
DELETE TYPE MGMT_JOB_TASK_TGT_RECORD;
DELETE TYPE MGMT_PARAM_CRED_TGT_MET;
DELETE TYPE MGMT_TARGET_FIXCA_ARRAY;
DELETE TYPE MGMT_TARGET_FIXCA_RECORD;
DELETE TYPE MGMT_JOB_PARAMETER_METADATA;
DELETE TYPE MGMT_JOB_PRMTER_URI_INF_RECORD;
DELETE TYPE MGMT_TARGET_TYPE_PROP_ARRAY;
DELETE TYPE MGMT_TARGET_TYPE_PROP_RECORD;
DELETE TYPE MGMT_JOB_TYPE_URI_INFO_ARRAY;
DELETE TYPE MGMT_JOB_TYPE_URI_INFO_RECORD;
DELETE TYPE MGMT_JOB_PARAMETER_ARRAY;
DELETE TYPE MGMT_JOB_PARAMETER_RECORD;
DELETE TYPE MGMT_JOB_PRMETER_DRPDN_ARRAY;
DELETE TYPE MGMT_JOB_PRMETER_DRPDN_RECORD;
DELETE TYPE MGMT_JOB_TASK_CRED_SET_ARRAY;
DELETE TYPE MGMT_JOB_TGT_CRED_SET_ARRAY;
DELETE TYPE MGMT_JOB_TGT_CRED_SET_RECORD;
DELETE TYPE MGMT_JOB_CREDENTIAL_RECORD;
DELETE TYPE MGMT_JOB_CRED_SOURCE_ARRAY;
DELETE TYPE MGMT_JOB_CRED_SOURCE_RECORD;
DELETE TYPE MGMT_JOB_CRED_SET_ARRAY;
DELETE TYPE MGMT_JOB_CRED_SET_RECORD;
DELETE TYPE MGMT_JOB_CRED_TYPE_RECORD;
DELETE TYPE MGMT_JOB_CRED_SET_COL_ARRAY;
DELETE TYPE MGMT_JOB_CRED_TYPE_COL_ARRAY;
DELETE TYPE MGMT_JOB_CRED_SET_COL_RECORD;
DELETE TYPE MGMT_JOB_CRED_TYPE_COL_RECORD;
DELETE TYPE MGMT_JOB_CRED_COL_VAL_ARRAY;
DELETE TYPE MGMT_JOB_CRED_COL_VAL_RECORD;
DELETE TYPE MGMT_JOB_SHORT_STR_ARR_TABLE;
DELETE  TYPE MGMT_JOB_TARGET_LIST_TABLE ;
DELETE TYPE MGMT_JOB_DNAME_TYPE_PROP_TABLE ;
DELETE TYPE MGMT_JOB_DNAME_TYPE_PROP_REC ;

REM Represents job run row
CREATE OR REPLACE TYPE runBasicRowType AS OBJECT
    (job_id                     RAW(16),
     job_name                   VARCHAR2(64), 
     job_type                   VARCHAR2(32),
     owner                      VARCHAR2(256),
--     scheduled_start_str        VARCHAR2(21),
     scheduled_time             DATE,
     timezone_region            VARCHAR2(264),    
     repeating                  NUMBER(2) 
     );
/

REM Represents a detailed job run row
CREATE OR REPLACE TYPE runDetailedRowType AS OBJECT
    (job_id                     RAW(16),
     execution_id               RAW(16),
     job_name                   VARCHAR2(64), 
     job_type                   VARCHAR2(32),
     owner                      VARCHAR2(256),
     target_guid                RAW(16),
     target_name                VARCHAR2(256),
     target_type                VARCHAR2(28),
     target_count               NUMBER(6),
     group_flag                 NUMBER(1),
     scheduled_time             DATE,
--     scheduled_start            DATE,
     timezone_region            VARCHAR2(264),     
     next_scheduled             DATE,
     repeating                  NUMBER(2), 
     job_access                 NUMBER(2), 
     stat_bucket_problem        NUMBER(6),
     stat_bucket_suspended      NUMBER(6),
     stat_bucket_active         NUMBER(6),
     stat_scheduled             NUMBER(6),
     stat_executing             NUMBER(6),
     stat_aborted               NUMBER(6),
     stat_failed                NUMBER(6),
     stat_completed             NUMBER(6),   
     stat_agent_down            NUMBER(6),
     stat_stopped               NUMBER(6),
     stat_suspended             NUMBER(6),      
     stat_suspended_lock        NUMBER(6),
     stat_suspended_event       NUMBER(6),
     stat_suspended_blackout    NUMBER(6)                                                        
     );     
/

REM Represents table of runs
CREATE OR REPLACE TYPE jobRunTableType AS TABLE OF runDetailedRowType;
/

REM Represents an execution row
CREATE OR REPLACE TYPE executionRowType AS OBJECT
    (job_id                     RAW(16),
     execution_id               RAW(16),
     job_name                   VARCHAR2(64), 
     job_type                   VARCHAR2(32),
     owner                      VARCHAR2(256),
     target_guid                RAW(16),
     target_name                VARCHAR2(256),
     target_type                VARCHAR2(28),
     target_count               NUMBER(6),
     group_flag                 NUMBER(1),
     scheduled_time             DATE,             -- used to be varchar2(40)
     timezone_region            VARCHAR2(264),    -- new     
     repeating                  VARCHAR2(32), 
     status                     NUMBER(2)                                                        
     );     
/

REM Represents a table of executions
CREATE OR REPLACE TYPE jobExecTableType AS TABLE OF executionRowType;
/

---------------------------------------------------------------------------------------
----------------------------------- SCHEMA DOWNGRADE ----------------------------------
---------------------------------------------------------------------------------------

-- New tables
DROP TABLE MGMT_JOB_STATE_CHANGES;
DROP TABLE MGMT_JOB_NOTIFY_STATES;
DROP TABLE MGMT_JOB_PARAM_URI_INFO;
DROP TABLE MGMT_JOB_TYPE_PARAM_DSPLY_INFO;
DROP TABLE MGMT_JOB_TYPE_PARAM_DROPDOWNS;
DROP TABLE MGMT_JOB_DISPLAY_ERROR_CODES;

-- Drop indexes prior to schema downgrade, so we recreate them after
-- altering constraints
DROP INDEX MGMT_JOB_IDX02 ;
DROP INDEX MGMT_JOB_PARAM_SOURCE_IDX01;
DROP INDEX MGMT_JOB_SEC_INFO_IDX01;
DROP INDEX MGMT_JOB_LOCK_INFO_IDX01;

DROP INDEX MGMT_JT_URI_INFO_IDX01;
DROP INDEX MGMT_JT_DISPLAY_PARAM_IDX01;
DROP INDEX MGMT_JT_DISPLAY_INFO_IDX01;



-- 10GR1 has only one version of the job type registered. The other
-- versions MUST have been introduced after the upgrade. Nuke all
-- the other versions
DELETE FROM MGMT_JOB_TYPE_INFO 
WHERE (major_version,minor_version1,minor_version2) NOT IN ((1,0,1));


DROP TABLE MGMT_JOB_TYPE_MAX_VERSIONS;

-- MGMT_JOB_SINGLE_TARGET_TYPES
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP CONSTRAINT PK_SINGLE_TARGET_TYPES;
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP CONSTRAINT FK_SINGLE_TARGET_TYPES;

ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_SINGLE_TARGET_TYPES t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP (job_type_id);

-- Add the constraints back in
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES ADD (
    CONSTRAINT PK_SINGLE_TARGET_TYPES 
        PRIMARY KEY(job_type, single_target_type),
    CONSTRAINT FK_SINGLE_TARGET_TYPES
        FOREIGN KEY(job_type) REFERENCES
            MGMT_JOB_TYPE_INFO(job_type)
                ON DELETE CASCADE
);


-- MGMT_JOB_EXECPLAN TBD Remove all multitask jobs and CAs
ALTER TABLE MGMT_JOB_EXECPLAN DROP CONSTRAINT PK_MGMT_JOB_EXECPLAN;
ALTER TABLE MGMT_JOB_EXECPLAN DROP CONSTRAINT FK_MGMT_JOB_EXECPLAN;

ALTER TABLE MGMT_JOB_EXECPLAN DROP (
    nested_job_target_type VARCHAR2(64) DEFAULT NULL,
    switch_var_index VARCHAR2(96),
    step_nlsid varchar2(64), 
    step_default VARCHAR2(256)
);

ALTER TABLE MGMT_JOB_EXECPLAN ADD (
    job_type VARCHAR2(32)
);

ALTER TABLE MGMT_JOB_EXECPLAN MODIFY (
    origin_step_type NUMBER(1),
    step_type NUMBER(1)
);

UPDATE  MGMT_JOB_EXECPLAN t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_EXECPLAN DROP (job_type_id);

ALTER TABLE MGMT_JOB_EXECPLAN ADD (
   CONSTRAINT PK_MGMT_JOB_EXECPLAN 
        PRIMARY KEY(job_type, step_name, step_type),
    CONSTRAINT FK_MGMT_JOB_EXECPLAN FOREIGN KEY(job_type_id)
        REFERENCES MGMT_JOB_TYPE_INFO(job_type)
          ON DELETE CASCADE
);

-- MGMT_JOB_STEP_PARAMS
ALTER TABLE MGMT_JOB_STEP_PARAMS DROP CONSTRAINT PK_MGMT_JOB_STEP_PARAMS;

ALTER TABLE MGMT_JOB_STEP_PARAMS DROP (value_of);

ALTER TABLE MGMT_JOB_STEP_PARAMS ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_STEP_PARAMS t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_STEP_PARAMS DROP (job_type_id);

ALTER TABLE MGMT_JOB_STEP_PARAMS ADD (CONSTRAINT PK_MGMT_JOB_STEP_PARAMS
        PRIMARY KEY(job_type, step_name, param_name));

-- MGMT_JOB_NESTED_JOB_TARGETS
ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_NESTED_JOB_TARGETS t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS DROP (job_type_id);

-- DROP the MGMT_JOB_NESTED_JOB_CRED_INFO table
DROP TABLE MGMT_JOB_NESTED_JOB_CRED_INFO;

-- MGMT_JOB_PARAM_SOURCE
ALTER TABLE MGMT_JOB_PARAM_SOURCE ADD (
    job_type VARCHAR2(32)
);

ALTER TABLE MGMT_JOB_PARAM_SOURCE MODIFY (step_type NUMBER (1));
-- Set the job_type columns correctly
UPDATE  MGMT_JOB_PARAM_SOURCE t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_PARAM_SOURCE DROP (job_type_id);

-- MGMT_JOB_CRED_PARAMS 
-- TBD These need to be computed
ALTER TABLE MGMT_JOB_CRED_PARAMS DROP (
    base_cred_type_name,
    base_cred_type_target_type,
    base_cred_type_columns
);

-- MGMT_JOB_SEC_INFO
ALTER TABLE MGMT_JOB_SEC_INFO ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_SEC_INFO t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_SEC_INFO DROP (job_type_id);

-- MGMT_JOB_LOCK_INFO
ALTER TABLE MGMT_JOB_LOCK_INFO DROP CONSTRAINT PK_MGMT_JOB_LOCK_INFO;
ALTER TABLE MGMT_JOB_LOCK_INFO DROP CONSTRAINT FK_MGMT_JOB_LOCK_INFO;

ALTER TABLE MGMT_JOB_LOCK_INFO ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_LOCK_INFO t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_LOCK_INFO DROP (job_type_id);

-- Add the constraints back
ALTER TABLE MGMT_JOB_LOCK_INFO ADD (
   CONSTRAINT PK_MGMT_JOB_LOCK_INFO 
       PRIMARY KEY(lock_guid, job_type),
    CONSTRAINT FK_MGMT_JOB_LOCK_INFO FOREIGN KEY(job_type)
       REFERENCES MGMT_JOB_TYPE_INFO(job_type)
       ON DELETE CASCADE
);

-- MGMT_JOB_LOCK_TARGETS
ALTER TABLE MGMT_JOB_LOCK_TARGETS DROP CONSTRAINT FK_JOB_LOCK_TARGETS;

ALTER TABLE MGMT_JOB_LOCK_TARGETS ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_LOCK_TARGETS t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_LOCK_TARGETS DROP (job_type_id);

ALTER TABLE MGMT_JOB_LOCK_TARGETS ADD (
   CONSTRAINT FK_JOB_LOCK_TARGETS FOREIGN KEY(lock_guid, job_type)
         REFERENCES MGMT_JOB_LOCK_INFO(lock_guid, job_type)
         ON DELETE CASCADE
);

-- MGMT_JOB_SCHEDULE
ALTER TABLE MGMT_JOB_SCHEDULE DROP (start_grace_period);

-- MGMT_JOB
ALTER TABLE MGMT_JOB DROP (
    job_type_major_version,
    nested_job_type_id,
    is_corrective_action,
    restartable,
    broken,
    broken_reason
);

ALTER TABLE MGMT_JOB MODIFY (job_owner VARCHAR2(32));

-- We must have deleted all the corrective actions by now
DROP TABLE MGMT_CORRECTIVE_ACTION;

DROP TABLE MGMT_HTTP_SESSION_OBJECTS;
DROP TABLE MGMT_HTTP_SESSION_CALLBACKS;

-- MGMT_JOB_EXEC_SUMMARY

-- TBD Schedule waiting executions for all recurring jobs, compute
-- expected_start_time
ALTER TABLE MGMT_JOB_EXEC_SUMMARY DROP (
    job_type_id,
    status_code,
    status_code_category,
    status_detail,
    expected_start_time,
    triggering_severity
);


-- MGMT_JOB_EXEC_LOCKS
ALTER TABLE MGMT_JOB_EXEC_LOCKS ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_EXEC_LOCKS t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_EXEC_LOCKS DROP (job_type_id);

-- MGMT_JOB_HISTORY
ALTER TABLE MGMT_JOB_HISTORY MODIFY (step_type NUMBER(1));

ALTER TABLE MGMT_JOB_HISTORY DROP (
    step_status_code_category,
    oms_name
);

-- MGMT_JOB_EXECUTION
ALTER TABLE MGMT_JOB_EXECUTION MODIFY (step_type NUMBER(1));

ALTER TABLE MGMT_JOB_EXECUTION DROP (
    step_status_code_category,
    oms_name
);

 
-- MGMT_JOB_TYPE_URI_INFO
-- TBD change entries for SQLScript, OSCommand, etc?
ALTER TABLE MGMT_JOB_TYPE_URI_INFO DROP CONSTRAINT FK_JOB_TYPE_URI_INFO;
ALTER TABLE MGMT_JOB_TYPE_URI_INFO ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_TYPE_URI_INFO t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_TYPE_URI_INFO DROP (job_type_id);

ALTER TABLE MGMT_JOB_TYPE_URI_INFO ADD (
    CONSTRAINT FK_JOB_TYPE_URI_INFO FOREIGN KEY(job_type)
        REFERENCES MGMT_JOB_TYPE_INFO(job_type)
          ON DELETE CASCADE
);

-- MGMT_JOB_TYPE_DISPLAY_PARAM 
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM DROP CONSTRAINT FK_JOB_TYPE_DISPLAY_PARAM;

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_TYPE_DISPLAY_PARAM t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM DROP (job_type_id);

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM ADD (
    CONSTRAINT FK_JOB_TYPE_DISPLAY_PARAM FOREIGN KEY(job_type)
        REFERENCES MGMT_JOB_TYPE_INFO(job_type)
          ON DELETE CASCADE
);

-- MGMT_JOB_TYPE_DISPLAY_INFO
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO DROP CONSTRAINT FK_JOB_TYPE_DISPLAY_INFO;
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO DROP (
    use_default_create_ui
);

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_TYPE_DISPLAY_INFO t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

UPDATE  MGMT_JOB_TYPE_DISPLAY_INFO
SET     use_default_create_ui=0;

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO DROP (job_type_id);

-- MGMT_JOB_CALLBACKS
ALTER TABLE MGMT_JOB_CALLBACKS DROP CONSTRAINT PK_MGMT_JOB_CALLBACKS;
ALTER TABLE MGMT_JOB_CALLBACKS DROP CONSTRAINT FK_MGMT_JOB_CALLBACKS;

ALTER TABLE MGMT_JOB_CALLBACKS ADD (
    job_type VARCHAR2(32)
);

-- Set the job_type_id columns correctly
UPDATE  MGMT_JOB_CALLBACKS t
SET     job_type=(SELECT job_type FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=t.job_type_id);

ALTER TABLE MGMT_JOB_CALLBACKS DROP (job_type_id);

ALTER TABLE MGMT_JOB_CALLBACKS ADD (
   CONSTRAINT PK_MGMT_JOB_CALLBACKS
        PRIMARY KEY(job_type, callback_type, callback_name),
    CONSTRAINT FK_MGMT_JOB_CALLBACKS
        FOREIGN KEY(job_type) REFERENCES MGMT_JOB_TYPE_INFO(job_type)
           ON DELETE CASCADE
);


ALTER TABLE MGMT_JOB_TYPE_INFO DROP CONSTRAINT PK_JOB_TYPE_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_INFO DROP (
    job_type_id,
    job_type_description,
    job_type_owner,
    job_type_category,
    last_modified_by,
    last_modified_date,
    all_target_types,
    major_version,
    minor_version1,
    minor_version2,
    job_type_nlsid,
    job_type_default,
    create_text_nlsid,
    create_text_default,
    delete_text_nlsid,
    delete_text_default
);
ALTER TABLE MGMT_JOB_TYPE_INFO ADD CONSTRAINT PK_JOB_TYPE_INFO(job_type) PRIMARY KEY;

------------------------------------------------------------------------------------
---------------------------- INDEX DOWNGRADE ----------------------------------------
-----------------------------------------------------------------------------------
CREATE INDEX MGMT_JOB_IDX02 ON MGMT_JOB(job_type)
       STORAGE (INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_PARAM_SOURCE_IDX01 ON
    MGMT_JOB_PARAM_SOURCE(job_type);

CREATE INDEX MGMT_JOB_SEC_INFO_IDX01 ON
    MGMT_JOB_SEC_INFO(job_type);

CREATE INDEX MGMT_JOB_LOCK_INFO_IDX01 ON
    MGMT_JOB_LOCK_INFO(job_type);

CREATE  INDEX MGMT_JT_URI_INFO_IDX01 ON
   MGMT_JOB_TYPE_URI_INFO(job_type);

CREATE  INDEX MGMT_JT_DISPLAY_PARAM_IDX01 ON
   MGMT_JOB_TYPE_DISPLAY_PARAM(job_type);


CREATE  INDEX MGMT_JT_DISPLAY_INFO_IDX01 ON
   MGMT_JOB_TYPE_DISPLAY_INFO(job_type);


-- Recreate types
CREATE OR REPLACE TYPE MGMT_JOB_SCHEDULE_RECORD AS OBJECT 
(
    -- The frequency code for the schedule.
    frequency_code NUMBER,
    
    -- The schedule start time. Use SYSDATE if the job is to start 
    -- immediately. For daily, days of week and days of month schedules, 
    -- the start_time denotes when the job should start. Only the time part 
    -- is used, and the date part is ignored. 
    start_time DATE,

    -- For all periodic schedules, the last date (and time) to run the job. 
    -- For daily, day of week and day of month schedules, only the date 
    -- portion is used. For interval schedules, the time is taken into 
    -- account as well.
    end_time DATE,

    -- The "execution time" for daily, day of week/month/year schedules. 
    -- Indicates the time of the day at which the job will execute. Hours 
    -- are specified in 24-hour format (0 to 23), and minutes is a number 
    -- between 0 and 59. 
    execution_hours NUMBER,
    execution_minutes NUMBER,

    -- The interval, in minutes for interval jobs.
    interval NUMBER,

    -- For days-of-year schedules, this indicates the "month" portion of each
    -- (month, day) pair in the schedule. The "days" portion is specified
    -- using "days", below.
    months MGMT_JOB_INT_ARRAY,

    -- For day of week/month jobs, the day(s) of the week,
    -- or month as the case may be.
    -- For days of week, this is a set of numbers from 1-7 indicating the days
    --    sunday through saturday, in that order.
    -- For days of month, this is a set of numbers from 1-31 indicating the 
    --    date of the month. A special constant called LAST_DAY (-1) 
    --    can be use for day-of-month schedules to indicate the last 
    --    day of the month.
    -- For days of the year, this is a set of numbers from 1-32 
    --    indicating the date of the month. A special constant called 
    --    LAST_DAY (-1) can be used for day-of-year schedules to indicate 
    --    the last day of the month. For day-of-year schedules, the 
    --    corresponding months are taken from the "months" field above 
    days MGMT_JOB_INT_ARRAY,

    -- Specifies which timezone the start and end times are specified in
    --    TIMEZONE_REPOSITORY: The timezone of the repository
    --    TIMEZONE_TARGET: The timezone of one of the targets in the 
    --        target list, whose index in the target list is specified 
    --        by timezone_target_index 
    --    TIMEZONE_SPECIFIED: The timezone offset is specified in the
    --       field timezone_offset
    timezone_info NUMBER,

    -- The index of the target in the target list(s) whose timezone the 
    -- times are specified in; only relevant when 
    -- timezone_info=TIMEZONE_TARGET
    timezone_target_index NUMBER,

    -- The actual timezone offset. Relevent only when
    -- timezone_info=TIMEZONE_SPECIFIED
    timezone_offset NUMBER,

    -- The actual timezone region. Relevent only when
    -- timezone_info=TIMEZONE_RGN_SPECIFIED
    timezone_region VARCHAR2(64)    
);
/

DROP TYPE JobRunObjTbl;

create or replace TYPE JobRunObjType as OBJECT
   (job_id              RAW(16)
   ,job_name            VARCHAR2(64)
   ,job_owner           VARCHAR2(256)
   ,job_type            VARCHAR2(32)
   ,scheduled_time      DATE
   ,frequency_code      NUMBER(2)
   ,timezone_region     VARCHAR2(64)
   ,timezone_info       NUMBER(2)
   ,target_name         VARCHAR2(256)
   ,target_type         VARCHAR2(64)
   ,type_display_name   VARCHAR2(128)
   ,target_count        NUMBER(5)
   ,status_bucket       NUMBER(2)
   ,status_bucket_count NUMBER(5)
   ,sample_exec_id      RAW(16)
   );
/

create or replace TYPE JobRunObjTbl as TABLE of JobRunObjType;
/

DROP INDEX MGMT_JOB_EXEC_IDX04;

CREATE INDEX MGMT_JOB_EXEC_IDX04 ON
  MGMT_JOB_EXECUTION(step_status, emd_url, start_time);

DROP TABLE MGMT_JOB_CA_CRED_INFO;
DROP TABLE MGMT_JOB_EXEC_CRED_INFO;