Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="group/group_schema_upgrade.sql+"/>
Rem
Rem $Header: jobs_schema_upgrade.sql 06-aug-2007.02:41:41 lsatyapr Exp $
Rem
Rem jobs_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, 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    lsatyapr    08/06/07 - Bug6316745 Drop constraint with index
Rem    aptrived    12/01/06 - Adding CASCADE DROP INDEX for PK_JOB_TYPE_INFO
Rem                           and for PK_MGMT_JOB_PARAM
Rem    bmallipe    08/13/06 - fixing the DBControl upgrade errors
Rem    pshishir    08/23/05 - Creating INDEX on MGMT_CA table 
Rem    pkantawa    08/23/05 - Fix beta->prod upgrade issues 
Rem    pkantawa    08/05/05 - Fix 4432894: fix column length 
Rem    skini       07/29/05 - Fix bug 4325569: disable triggers prior to 
Rem                           updating tables 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    chyu        07/18/05 - adding the <migration> header 
Rem    pkantawa    06/24/05 - Fix 4453976: Add column in type 
Rem    nqureshi    06/21/05 - adding new attribute in the table 
Rem                           MGMT_JOB_TYPE_PARAM_URI_INFO time 
Rem    nqureshi    06/20/05 - adding types created for targets props 
Rem                           validation 
Rem    skini       06/06/05 - grabtrans 'skini_bug-4377344' 
Rem    skini       05/27/05 - Fix syntax error 
Rem    skini       05/27/05 - Upgrade fixes 
Rem    dsahrawa    05/25/05 - fix bug 4388845, diffs from upgrade 
Rem    ashugupt    05/17/05 - Fix 4338761 
Rem    jvishen     05/19/05 - change job_id type 
Rem    pkantawa    05/04/05 - Fix bug 4348693: upgrade MGMT_JOB_STEP_RECORD 
Rem    pkantawa    04/17/05 - Fix 4305554: Upgrade errors 
Rem    chyu        03/29/05 - syncing up the mgmt_target_fixca_record 
Rem                           definition 
Rem    pkantawa    03/27/05 - Change Delete to Drop 
Rem    pkantawa    03/25/05 - remove runs/exec related types 
Rem    eujang      03/08/05 - syncing up the column definitions 
Rem    eujang      03/08/05 - adding the missing tables 
Rem    skini       03/03/05 - Add valueOf column 
Rem    scgrover    02/16/05 - reorder job index
Rem    skini       02/16/05 - Add type migration 
Rem    skini       02/14/05 - Finish upgrade 
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
rem This script requires the following arguments
rem  1. Name of the ecm_depot tablespace where all LOBs are stored
rem
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"

DEFINE JOB_PARAM_NAME_SIZE=64
DEFINE STEP_NAME_SIZE=64

Rem Upgrade jobs types

REM Represents an record of credential column values
CREATE OR REPLACE TYPE MGMT_JOB_CRED_COL_VAL_RECORD AS OBJECT(
    col_value VARCHAR2(64),
    col_value_nlsid VARCHAR(64)
);
/

REM Represents an array of credential column values
CREATE OR REPLACE TYPE MGMT_JOB_CRED_COL_VAL_ARRAY AS
    TABLE OF MGMT_JOB_CRED_COL_VAL_RECORD;
/

REM Represents a credentials type column
CREATE OR REPLACE TYPE MGMT_JOB_CRED_TYPE_COL_RECORD AS OBJECT (
    type_column_name VARCHAR2(64),
    type_column_display_name VARCHAR2(64),
    base_type_column_name VARCHAR2(64),
    type_column_nlsid VARCHAR2(64),
    type_column_value VARCHAR2(4000),
    default_value_index NUMBER(6),
    current_value_index NUMBER(6),
    key NUMBER(1),
    allowable_values MGMT_JOB_CRED_COL_VAL_ARRAY
);
/

REM Represents a credential set columns
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SET_COL_RECORD AS OBJECT (
    set_column_name VARCHAR2(64),
    set_column_display_name VARCHAR2(64),
    type_column_name VARCHAR2(64),
    default_value_index NUMBER(6),
    current_value_index NUMBER(6),    
    allowable_values MGMT_JOB_CRED_COL_VAL_ARRAY
);
/

REM Represents an array of credential type columns
CREATE OR REPLACE TYPE MGMT_JOB_CRED_TYPE_COL_ARRAY AS
    TABLE OF MGMT_JOB_CRED_TYPE_COL_RECORD;
/

REM Represents an array of credential set columns
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SET_COL_ARRAY AS
    TABLE OF MGMT_JOB_CRED_SET_COL_RECORD;
/

REM Represents a credential type record
CREATE OR REPLACE TYPE MGMT_JOB_CRED_TYPE_RECORD AS OBJECT (
    type_name VARCHAR2(64),
    type_display_name VARCHAR2(64),
    type_nlsid VARCHAR2(64),
    target_type VARCHAR2(64),
    target_type_meta_ver VARCHAR2(64),
    cred_type_cols MGMT_JOB_CRED_TYPE_COL_ARRAY

);
/

REM Represents a credential set record
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SET_RECORD AS OBJECT (
    set_name VARCHAR2(64),
    set_display_name VARCHAR2(64),
    set_nlsid VARCHAR2(64),
    target_type VARCHAR2(64),
    target_type_meta_ver VARCHAR2(64),
    cred_set_cols MGMT_JOB_CRED_SET_COL_ARRAY

);
/

REM Represents an array of credential set
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SET_ARRAY AS
    TABLE OF MGMT_JOB_CRED_SET_RECORD;
/

REM Represents a credentials source
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SOURCE_RECORD AS OBJECT (
    cred_set_name_var VARCHAR2(64),
    cred_set_target_type_var VARCHAR2(64),
    b_name_var NUMBER(1),
    b_target_type_var NUMBER(1),
    base_cred_type MGMT_JOB_CRED_TYPE_RECORD,
    derived_cred_type MGMT_JOB_CRED_TYPE_RECORD,
    base_cred_type_columns MGMT_JOB_VECTOR_PARAMS,
    cred_set MGMT_JOB_CRED_SET_ARRAY,
    cred_column_param VARCHAR2(64),
    job_cred_set_name VARCHAR2(64),
    guid VARCHAR2(64)
);
/

REM Represents an array of credential source
CREATE OR REPLACE TYPE MGMT_JOB_CRED_SOURCE_ARRAY AS
    TABLE OF MGMT_JOB_CRED_SOURCE_RECORD;
/   
    
REM Represents a job credential
CREATE OR REPLACE TYPE MGMT_JOB_CREDENTIAL_RECORD AS OBJECT (
    cred_source MGMT_JOB_CRED_SOURCE_ARRAY,
    is_preferred NUMBER(1)
);
/

DECLARE
    l_count NUMBER := 0;
BEGIN
    SELECT count(*)
    INTO   l_count
    FROM   all_types
    WHERE  upper(type_name) = 'RUNBASICROWTYPE';

    IF (l_count > 0) THEN
        EXECUTE IMMEDIATE 'DROP TYPE RUNBASICROWTYPE';
    END IF;

    SELECT count(*)
    INTO   l_count
    FROM   all_types
    WHERE  upper(type_name) = 'JOBRUNTABLETYPE';

    IF (l_count > 0) THEN
        EXECUTE IMMEDIATE 'DROP TYPE JOBRUNTABLETYPE';
    END IF;

    SELECT count(*)
    INTO   l_count
    FROM   all_types
    WHERE  upper(type_name) = 'RUNDETAILEDROWTYPE';

    IF (l_count > 0) THEN
        EXECUTE IMMEDIATE 'DROP TYPE RUNDETAILEDROWTYPE';
    END IF;

    SELECT count(*)
    INTO   l_count
    FROM   all_types
    WHERE  upper(type_name) = 'JOBEXECTABLETYPE';

    IF (l_count > 0) THEN
        EXECUTE IMMEDIATE 'DROP TYPE JOBEXECTABLETYPE';
    END IF;

    SELECT count(*)
    INTO   l_count
    FROM   all_types
    WHERE  upper(type_name) = 'EXECUTIONROWTYPE';

    IF (l_count > 0) THEN
        EXECUTE IMMEDIATE 'DROP TYPE EXECUTIONROWTYPE';
    END IF;
END;
/

REM Upgrade the step record type
ALTER TYPE MGMT_JOB_STEP_RECORD MODIFY ATTRIBUTE (step_type NUMBER(2)) CASCADE;
ALTER TYPE MGMT_JOB_STEP_RECORD MODIFY ATTRIBUTE (iterate_param VARCHAR2(64)) CASCADE;

-- -----------------------------------------------------------------------------
--  SQL types for job credentials validation
--  These types are used by jobs ui package
-- -----------------------------------------------------------------------------

REM Represents one credential set data for a job task. The target type
REM here represents target type for the entire job.
REM The target type in MGMT_JOB_TARGET_RECORD object represents the target type
REM of group or cluster if job is submitted for a whole group otherwise its same as
REM job type of entire job.
CREATE OR REPLACE TYPE MGMT_JOB_TGT_CRED_SET_RECORD AS OBJECT (
    cred_set_name VARCHAR2(64),
    target_type VARCHAR2(64),
    target_list MGMT_JOB_TARGET_LIST
);  
/   

REM Represents an array of job target credential set. Each job task may be associated
REM to multiple credential sets and this object holds the cred set data for each job task.
CREATE OR REPLACE TYPE MGMT_JOB_TGT_CRED_SET_ARRAY AS
    TABLE OF MGMT_JOB_TGT_CRED_SET_RECORD;
/

REM Represents an array of job task credential set, each of which represents
REM credential set data for the task.
CREATE OR REPLACE TYPE MGMT_JOB_TASK_CRED_SET_ARRAY AS
    TABLE OF MGMT_JOB_TGT_CRED_SET_ARRAY;
/



REM Represents a parameter DropDown values record added by nqureshi
CREATE OR REPLACE TYPE MGMT_JOB_PRMETER_DRPDN_RECORD AS OBJECT (
    is_default NUMBER(1),
    value varchar2(32),
    option_text_nlsid VARCHAR2(32),
    option_text_default VARCHAR2(256),
    param_order number(3)
);
/

REM Represents an array of One parameters DROPDOWN VALUES set
CREATE OR REPLACE TYPE MGMT_JOB_PRMETER_DRPDN_ARRAY AS
    TABLE OF MGMT_JOB_PRMETER_DRPDN_RECORD;
/

REM represent one row of parameters
    CREATE OR REPLACE TYPE MGMT_JOB_PARAMETER_RECORD AS OBJECT (
    job_type   VARCHAR2(32),
    parameter_name VARCHAR2(64),    
    show_in_create NUMBER(1),    
    show_in_results NUMBER(1),    
    label_nlsid VARCHAR2(32),    
    label_default VARCHAR2(256),
    hint_nlsid VARCHAR2(32),
    hint_default VARCHAR2(256),     
    display_mode NUMBER(2),     
    num_lines NUMBER(2),    
    default_text VARCHAR2(4000),    
    default_nlsid VARCHAR2(32),
    param_order number(3),
    ARRAY_PARAMETER_DROPDOWN MGMT_JOB_PRMETER_DRPDN_ARRAY,
    parameter_required number(1),
    parameter_encrypted number(1)
    ); 
/   
    
REM Represents an array of ALL PARAMETERS set
CREATE OR REPLACE TYPE MGMT_JOB_PARAMETER_ARRAY AS
        TABLE OF MGMT_JOB_PARAMETER_RECORD;
/

rem this will Hold the one row  the MGMT_JOB_TYPE_URI_INFO
CREATE OR REPLACE TYPE MGMT_JOB_TYPE_URI_INFO_RECORD AS OBJECT  (
job_type   VARCHAR2(32),
uri_use    NUMBER(2),
uri        VARCHAR2(4000),
is_jsp     NUMBER(1),
class      VARCHAR2(4000)
);
/


REM Represents an array of One parameters DROPDOWN VALUES set
CREATE OR REPLACE TYPE MGMT_JOB_TYPE_URI_INFO_ARRAY AS
    TABLE OF MGMT_JOB_TYPE_URI_INFO_RECORD;
/

REM to hold the ONE ROW OF THE TARGET_PROPERTY
CREATE OR REPLACE TYPE MGMT_TARGET_TYPE_PROP_RECORD AS OBJECT  (
property_name   VARCHAR2(32),
property_display_name    VARCHAR2(255),
property_display_nls_id    VARCHAR2(255)
);
/

REM Represents an array of One TARGET PROPERTY VALUES
CREATE OR REPLACE TYPE MGMT_TARGET_TYPE_PROP_ARRAY AS
    TABLE OF MGMT_TARGET_TYPE_PROP_RECORD;
/


rem this will Hold the onr row  the MGMT_JOB_PARAMETER_URI_INFO
CREATE OR REPLACE TYPE MGMT_JOB_PRMTER_URI_INF_RECORD AS OBJECT (
    job_type   VARCHAR2(32),
    uri        VARCHAR2(4000),
    class      VARCHAR2(4000),
    online_help_topic VARCHAR2(4000),
    show_target_properties NUMBER(1)
);
/

REM The Super Type that will contain all the sub types 
create or replace type MGMT_JOB_PARAMETER_METADATA as Object
(   
    uri_info_metaData   MGMT_JOB_TYPE_URI_INFO_ARRAY ,
    parameter_uri_info_metaData  MGMT_JOB_PRMTER_URI_INF_RECORD ,
    parameter_metaData MGMT_JOB_PARAMETER_ARRAY
);
/
REM to hold the one row of the broken credential of a target
create or replace TYPE MGMT_TARGET_FIXCA_RECORD as object (
    target_name        VARCHAR2(256),
    target_type        VARCHAR2(64),
    policy_name_display VARCHAR2(128),
    column_label       VARCHAR2(64),
    column_label_nlsid VARCHAR2(64),
    severity           NUMBER(2),
    ca                 VARCHAR2(64),
    current_owner      VARCHAR2(256),
    job_id             VARCHAR2(256),
    metric_key_value   VARCHAR2(1300),
    broken_reason      NUMBER(2),
    policy_type        NUMBER(2),
    job_type           VARCHAR2(32)
);
/
REM Represents an array of broken credentials of targets
CREATE OR REPLACE TYPE MGMT_TARGET_FIXCA_ARRAY AS
    TABLE OF MGMT_TARGET_FIXCA_RECORD;
/

REM holds the metadata info for a combination of job type and target type
CREATE OR REPLACE TYPE MGMT_PARAM_CRED_TGT_MET AS OBJECT
(
    job_type VARCHAR2(32),
    target_type VARCHAR2(64),
    parameter_metadata MGMT_JOB_PARAMETER_METADATA ,
    credential_metadata MGMT_JOB_CREDENTIAL_RECORD ,
    target_properties MGMT_TARGET_TYPE_PROP_ARRAY ,
    required_params SMP_EMD_STRING_ARRAY ,
    targets_required NUMBER(1)
);
/

REM Represents array of MGMT_PARAM_CRED_TGT_MET
CREATE OR REPLACE TYPE MGMT_PARAM_CRED_TGT_MET_ARR AS
    TABLE OF MGMT_PARAM_CRED_TGT_MET;
/

-- -----------------------------------------------------------------------------
--  SQL types for fetching timezones of all targets of all tasks in a multi
--  task job
--  These types are used by jobs ui package
-- -----------------------------------------------------------------------------

REM Represents one set of target list for a task. The target type
REM here represents target type of the task.
CREATE OR REPLACE TYPE MGMT_JOB_TASK_TGT_RECORD AS OBJECT (
    target_list MGMT_JOB_TARGET_LIST,
    target_type VARCHAR2(64)
);
/


REM Represents an array of MGMT_JOB_TASK_TGT_RECORD - one for each task
CREATE OR REPLACE TYPE MGMT_JOB_TASK_TGT_ARRAY AS
    TABLE OF MGMT_JOB_TASK_TGT_RECORD;
/

CREATE OR REPLACE TYPE MGMT_JOB_NOTIF_EMAIL_SCH_INFO AS OBJECT (
     email_address_count  NUMBER(3),
     schedule_count NUMBER(3),
     job_notify_status MGMT_INTEGER_ARRAY 
     );
/



---------------------------------------------------------------------------------------
----------------------------------- SCHEMA UPGRADE ----------------------------------
---------------------------------------------------------------------------------------
-- Drop indexes prior to schema upgrade
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;

-- Drop foreign key constraints first
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP CONSTRAINT PK_SINGLE_TARGET_TYPES CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP CONSTRAINT FK_SINGLE_TARGET_TYPES CASCADE DROP INDEX;

ALTER TABLE MGMT_JOB_EXECPLAN DROP CONSTRAINT PK_MGMT_JOB_EXECPLAN CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_EXECPLAN DROP CONSTRAINT FK_MGMT_JOB_EXECPLAN CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_STEP_PARAMS DROP CONSTRAINT PK_MGMT_JOB_STEP_PARAMS CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_LOCK_TARGETS DROP CONSTRAINT FK_JOB_LOCK_TARGETS CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_LOCK_INFO DROP CONSTRAINT PK_MGMT_JOB_LOCK_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_LOCK_INFO DROP CONSTRAINT FK_MGMT_JOB_LOCK_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_URI_INFO DROP CONSTRAINT FK_JOB_TYPE_URI_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM DROP CONSTRAINT FK_JOB_TYPE_DISPLAY_PARAM CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO DROP CONSTRAINT FK_JOB_TYPE_DISPLAY_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_CALLBACKS DROP CONSTRAINT PK_MGMT_JOB_CALLBACKS CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_CALLBACKS DROP CONSTRAINT FK_MGMT_JOB_CALLBACKS CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_PARAMETER DROP CONSTRAINT PK_MGMT_JOB_PARAM CASCADE DROP INDEX;

ALTER TABLE MGMT_JOB_TYPE_INFO ADD (
    job_type_id RAW(16) DEFAULT SYS_GUID(),
    job_type_description VARCHAR2(256),
    job_type_owner VARCHAR2(256),
    job_type_category NUMBER(1) DEFAULT 0,
    last_modified_by VARCHAR2(256),
    last_modified_date DATE DEFAULT SYSDATE,
    all_target_types NUMBER(1) DEFAULT 0,
    major_version NUMBER DEFAULT 1,
    minor_version1 NUMBER DEFAULT 0,
    minor_version2 NUMBER DEFAULT 1,
    job_type_nlsid VARCHAR2(64),
    job_type_default VARCHAR2(256),
    create_text_nlsid VARCHAR2(64),
    create_text_default VARCHAR2(256),
    delete_text_nlsid VARCHAR2(64),
    delete_text_default VARCHAR2(256)
);


ALTER TABLE MGMT_JOB_TYPE_INFO DROP CONSTRAINT PK_JOB_TYPE_INFO CASCADE DROP INDEX;
ALTER TABLE MGMT_JOB_TYPE_INFO ADD CONSTRAINT PK_JOB_TYPE_INFO PRIMARY KEY(job_type_id);

CREATE TABLE MGMT_JOB_TYPE_MAX_VERSIONS (
    job_type VARCHAR2(32),
    job_type_id RAW(16),
    major_version NUMBER DEFAULT 1,
    minor_version1 NUMBER DEFAULT 0,
    minor_version2 NUMBER DEFAULT 1,
    CONSTRAINT PK_JOB_TYPE_MAX_VERSIONS PRIMARY KEY(job_type, major_version)
)
MONITORING;

INSERT INTO MGMT_JOB_TYPE_MAX_VERSIONS(job_type, job_type_id, major_version,
                                       minor_version1, minor_version2)
SELECT job_type, job_type_id, major_version,
       minor_version1, minor_version2
FROM   MGMT_JOB_TYPE_INFO;

-- MGMT_JOB_SINGLE_TARGET_TYPES
ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES ADD (
    job_type_id RAW(16)
);

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

ALTER TABLE MGMT_JOB_SINGLE_TARGET_TYPES DROP (job_type);

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


-- MGMT_JOB_EXECPLAN
ALTER TABLE MGMT_JOB_EXECPLAN ADD (
    job_type_id RAW(16),
    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 MODIFY (
    origin_step_type NUMBER(2),
    step_type NUMBER(2)
);

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

ALTER TABLE MGMT_JOB_EXECPLAN DROP (job_type);

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

-- MGMT_JOB_STEP_PARAMS
ALTER TABLE MGMT_JOB_STEP_PARAMS ADD (
    job_type_id RAW(16),
    value_of VARCHAR2(64) DEFAULT NULL
);

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

ALTER TABLE MGMT_JOB_STEP_PARAMS DROP (job_type);

ALTER TABLE MGMT_JOB_STEP_PARAMS MODIFY (
    PARAM_NAME VARCHAR2(64)
);

ALTER TABLE MGMT_JOB_STEP_PARAMS ADD CONSTRAINT PK_MGMT_JOB_STEP_PARAMS
        PRIMARY KEY(job_type_id, step_name, param_name)
USING INDEX TABLESPACE &EM_ECM_DEPOT_TABLESPACE;

-- MGMT_JOB_NESTED_JOB_TARGETS
ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS ADD (
    job_type_id RAW(16)
);

-- MGMT_JOB_EXEC_EVENT_PARAMS
ALTER TABLE MGMT_JOB_EXEC_EVENT_PARAMS MODIFY (
    PARAM_NAME VARCHAR2(64)
);

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

ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS DROP (job_type);

-- Create the MGMT_JOB_NESTED_JOB_CRED_INFO table
CREATE TABLE MGMT_NESTED_JOB_CRED_INFO
(
    job_type_id RAW(16),
    nested_job_name VARCHAR2(64),
    target_guid RAW(16),
    target_type VARCHAR2(64),
    container_location VARCHAR2(256),
    credential_set_name VARCHAR2(32),
    credential_guid RAW(16),
    CONSTRAINT PK_MGMT_NESTED_JOB_CRED_INFO
        PRIMARY KEY (job_type_id, nested_job_name, target_guid, 
                     target_type, container_location, credential_set_name),
    CONSTRAINT FK_MGMT_NESTED_JOB_CRED_INFO
        FOREIGN KEY (job_type_id)
        REFERENCES MGMT_JOB_TYPE_INFO (job_type_id)
        ON DELETE CASCADE
)
MONITORING;

-- MGMT_JOB_PARAM_SOURCE
ALTER TABLE MGMT_JOB_PARAM_SOURCE ADD (
    job_type_id RAW(16),
    apply_on_retry NUMBER(1) DEFAULT 0
);

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

ALTER TABLE MGMT_JOB_PARAM_SOURCE DROP (job_type);

-- MGMT_JOB_CRED_PARAMS 
-- TBD These need to be computed
ALTER TABLE MGMT_JOB_CRED_PARAMS ADD (
    base_cred_type_name VARCHAR2(256),
    base_cred_type_target_type VARCHAR2(256),
    base_cred_type_columns MGMT_JOB_VECTOR_PARAMS
);

ALTER TABLE MGMT_JOB_CRED_PARAMS MODIFY (
    CONTAINER_PATHS_PARAM VARCHAR2(64),
    TARGET_NAMES_PARAM VARCHAR2(64),
    TARGET_TYPES_PARAM VARCHAR2(64)
);

-- MGMT_JOB_EXECPLAN
ALTER TABLE MGMT_JOB_EXECPLAN ADD (
    FLATTENED_TARGETS NUMBER(1) DEFAULT 0
);

-- MGMT_JOB_SEC_INFO
ALTER TABLE MGMT_JOB_SEC_INFO ADD (
    job_type_id RAW(16)
);

ALTER TABLE MGMT_JOB_SEC_INFO MODIFY (
    TARGET_NAMES_PARAM VARCHAR2(64),
    TARGET_TYPES_PARAM VARCHAR2(64)
);

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

ALTER TABLE MGMT_JOB_SEC_INFO DROP (job_type);

-- MGMT_JOB_LOCK_INFO
ALTER TABLE MGMT_JOB_LOCK_INFO ADD (
    job_type_id RAW(16)
);

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

ALTER TABLE MGMT_JOB_LOCK_INFO DROP (job_type);

-- Add the constraints back
ALTER TABLE MGMT_JOB_LOCK_INFO ADD 
   CONSTRAINT PK_MGMT_JOB_LOCK_INFO 
       PRIMARY KEY(lock_guid, job_type_id)
USING INDEX TABLESPACE &EM_ECM_DEPOT_TABLESPACE;

ALTER TABLE MGMT_JOB_LOCK_INFO ADD
    CONSTRAINT FK_MGMT_JOB_LOCK_INFO FOREIGN KEY(job_type_id)
       REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
       ON DELETE CASCADE;

-- MGMT_JOB_LOCK_TARGETS
ALTER TABLE MGMT_JOB_LOCK_TARGETS ADD (
    job_type_id RAW(16)
);

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

ALTER TABLE MGMT_JOB_LOCK_TARGETS DROP (job_type);

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

-- MGMT_JOB_SCHEDULE
ALTER TABLE MGMT_JOB_SCHEDULE ADD (start_grace_period NUMBER DEFAULT -1);

-- MGMT_JOB
ALTER TABLE MGMT_JOB ADD (
    job_type_major_version NUMBER DEFAULT 1,
    nested_job_type_id RAW(16),
    is_corrective_action NUMBER(1) DEFAULT 0,
    restartable NUMBER(1) DEFAULT 1,
    broken NUMBER(1) DEFAULT 0,
    broken_reason NUMBER(2)
);

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

-- Compute the right job type major version
UPDATE  MGMT_JOB j
SET     job_type_major_version=
            (SELECT major_version FROM MGMT_JOB_TYPE_INFO WHERE job_type=j.job_type);

UPDATE  MGMT_JOB j
SET     is_corrective_action=0, restartable=1, broken=0;

-- Compute job type id for nested jobs
UPDATE  MGMT_JOB j
SET     nested_job_type_id=
            (SELECT job_type_id FROM MGMT_JOB_TYPE_INFO WHERE job_type=j.job_type)
WHERE   nested=1;

-- MGMT_CORRECTIVE_ACTION
CREATE TABLE MGMT_CORRECTIVE_ACTION (
    job_id RAW(16) DEFAULT SYS_GUID(),
    ca_scope NUMBER(1) DEFAULT 1,
    ca_target_guid RAW(16),
    ca_template_guid RAW(16),
    ca_reference_count NUMBER,
    CONSTRAINT "MGMT_CORRECTIVE_ACTION_PK" PRIMARY KEY("JOB_ID")
)
MONITORING;

-- MGMT_JOB_EXEC_SUMMARY

ALTER TABLE MGMT_JOB_EXEC_SUMMARY DISABLE ALL TRIGGERS;
-- TBD Schedule waiting executions for all recurring jobs, compute
-- expected_start_time
ALTER TABLE MGMT_JOB_EXEC_SUMMARY ADD (
    job_type_id RAW(16),
    status_code INTEGER,
    status_code_category NUMBER(1) DEFAULT 1,
    status_detail INTEGER DEFAULT 0,
    expected_start_time DATE DEFAULT null,
    triggering_severity RAW(16)
);

-- Compute the job type ids
UPDATE  MGMT_JOB_EXEC_SUMMARY e
SET     job_type_id=
            (SELECT job_type_id FROM MGMT_JOB_TYPE_INFO ji, MGMT_JOB j
             WHERE  j.job_type=ji.job_type
             AND    j.job_id=e.job_id);

ALTER TABLE MGMT_JOB_EXEC_SUMMARY ENABLE ALL TRIGGERS;

-- MGMT_JOB_EXEC_LOCKS
ALTER TABLE MGMT_JOB_EXEC_LOCKS ADD (
    job_type_id RAW(16)
);

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

ALTER TABLE MGMT_JOB_EXEC_LOCKS DROP (job_type);

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

ALTER TABLE MGMT_JOB_HISTORY ADD (
    step_status_code_category NUMBER(1) DEFAULT 1,
     oms_name VARCHAR2(256) DEFAULT NULL
);

-- MGMT_JOB_EXECUTION
ALTER TABLE MGMT_JOB_EXECUTION MODIFY (
    step_type NUMBER(2),
    ITERATE_PARAM VARCHAR2(64)
);

ALTER TABLE MGMT_JOB_EXECUTION ADD (
    step_status_code_category NUMBER(1) DEFAULT 1,
     oms_name VARCHAR2(256) DEFAULT NULL
);

-- MGMT_JOB_HISTORY
ALTER TABLE MGMT_JOB_HISTORY MODIFY (
    ITERATE_PARAM VARCHAR2(64)
);

-- MGMT_JOB_LOCK_INFO
ALTER TABLE MGMT_JOB_LOCK_INFO MODIFY (
    TARGET_NAMES_PARAM VARCHAR2(64),
    TARGET_TYPES_PARAM VARCHAR2(64)
);

-- MGMT_JOB_PARAMETER
ALTER TABLE MGMT_JOB_PARAMETER MODIFY (
    PARAMETER_NAME VARCHAR2(64)
);

ALTER TABLE MGMT_JOB_PARAMETER ADD (
    CONSTRAINT PK_MGMT_JOB_PARAM 
      PRIMARY KEY(job_id, execution_id, parameter_name)
      USING INDEX (CREATE UNIQUE INDEX PK_MGMT_JOB_PARAM
        ON MGMT_JOB_PARAMETER (job_id, execution_id, parameter_name)
        COMPRESS 2
        TABLESPACE &EM_ECM_DEPOT_TABLESPACE)
);

-- MGMT_JOB_PROP_PARAMS
ALTER TABLE MGMT_JOB_PROP_PARAMS MODIFY (
    TARGET_NAMES_PARAM VARCHAR2(64),
    TARGET_TYPES_PARAM VARCHAR2(64)
);
 
-- MGMT_JOB_TYPE_URI_INFO
-- TBD change entries for SQLScript, OSCommand, etc?
ALTER TABLE MGMT_JOB_TYPE_URI_INFO ADD (
    job_type_id RAW(16)
);

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

ALTER TABLE MGMT_JOB_TYPE_URI_INFO DROP (job_type);

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

-- MGMT_JOB_TYPE_DISPLAY_PARAM 
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM ADD (
    job_type_id RAW(16)
);

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM MODIFY (
    PARAM_NAME VARCHAR2(64)
);

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

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_PARAM DROP (job_type);

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

-- MGMT_JOB_TYPE_DISPLAY_INFO
ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO ADD (
    job_type_id RAW(16),
    use_default_create_ui number(1) DEFAULT 0
);

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

UPDATE  MGMT_JOB_TYPE_DISPLAY_INFO
SET     use_default_create_ui=0;

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO DROP (job_type);

ALTER TABLE MGMT_JOB_TYPE_DISPLAY_INFO ADD (
    CONSTRAINT FK_JOB_TYPE_DISPLAY_INFO FOREIGN KEY(job_type_id)
        REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
          ON DELETE CASCADE
);

-- MGMT_JOB_CALLBACKS
ALTER TABLE MGMT_JOB_CALLBACKS ADD (
    job_type_id RAW(16)
);

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

ALTER TABLE MGMT_JOB_CALLBACKS DROP (job_type);

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

-- MGMT_JOB_QUEUES
ALTER TABLE MGMT_JOB_QUEUES MONITORING;

-- New tables
CREATE TABLE MGMT_JOB_STATE_CHANGES
(state_change_guid RAW(16) DEFAULT SYS_GUID(),
 job_id RAW(16),
 execution_id RAW(16),
 step_id INTEGER,
 logged TIMESTAMP DEFAULT SYSTIMESTAMP,
 occurred DATE,
 newstate NUMBER(2),
 status_bucket NUMBER(2),
 type NUMBER,
 violation_guid RAW(16),
 CONSTRAINT PK_MGMT_JOB_ST_CHGS PRIMARY KEY(state_change_guid),
 CONSTRAINT FK_MGMT_JOB_ST_CHGS_1
        FOREIGN KEY(execution_id) REFERENCES
            MGMT_JOB_EXEC_SUMMARY(execution_id)
                ON DELETE CASCADE
)
MONITORING;

CREATE TABLE MGMT_JOB_NOTIFY_STATES (
    job_id RAW(16),
    notify_state NUMBER(2),
    CONSTRAINT PK_MGMT_JOB_NOTIF_STATE PRIMARY KEY(job_id, notify_state),
    CONSTRAINT FK_MGMT_JOB_NOTIF_STATE_1 
         FOREIGN KEY(job_id) REFERENCES
            MGMT_JOB(job_id) ON DELETE CASCADE
) MONITORING;


CREATE TABLE MGMT_JOB_TYPE_PARAM_URI_INFO
(
  JOB_TYPE_ID   RAW(16),
  URI          VARCHAR2(2000),
  CLASS        VARCHAR2(2000),
  HELP_TOPIC   VARCHAR2(2000),
  SHOW_TARGET_PROPERTIES NUMBER(1) default 1 ,
  CONSTRAINT PK_PARAM_URI_INFO PRIMARY KEY(job_type_id),
  CONSTRAINT FK_PARAM_URI_INFO  FOREIGN KEY(job_type_id)
     REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)
       ON DELETE CASCADE
) MONITORING;
 

CREATE TABLE MGMT_JOB_TYPE_PARAM_DSPLY_INFO (    
     job_type_id RAW(16),  
     parameter_name VARCHAR2(64),    
     show_in_create NUMBER(1),    
     show_in_results NUMBER(1),    
     label_nlsid VARCHAR2(64),    
     label_default VARCHAR2(256),    
     hint_nlsid VARCHAR2(64),    
     hint_default VARCHaR2(256),    
     display_mode NUMBER(2),    
     num_lines NUMBER(2),    
     default_text VARCHAR2(4000),    
     default_nlsid VARCHAR2(64), 
     param_order NUMBER(3) NOT NULL ,
     CONSTRAINT FK_JOB_TYPE_PARAM_INFO FOREIGN KEY(job_type_id)        
        REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)          
            ON DELETE CASCADE 
) MONITORING;

CREATE TABLE MGMT_JOB_TYPE_PARAM_DROPDOWNS (    
    job_type_id RAW(16),
    parameter_name VARCHAR2(64),    
    is_default NUMBER(1),
    option_value varchar2(32),
    option_text_nlsid VARCHAR2(64),    
    option_text_default VARCHAR2(256),
    param_order number(3) not null,
    CONSTRAINT FK_JOB_TYPE_PARAM_DRPDWNS FOREIGN KEY(job_type_id)        
        REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)          
            ON DELETE CASCADE 
 ) MONITORING; 


CREATE TABLE MGMT_JOB_DISPLAY_ERROR_CODES (
    job_type_id RAW(16),
    id VARCHAR2(64),
    nlsid VARCHAR2(64),
    default_value VARCHAR2(256),
    CONSTRAINT PK_MGMT_JOB_DSPLY_ERR_CODES PRIMARY KEY(job_type_id, id),
    CONSTRAINT FK_JOB_TYPE_DSPLY_ERR_CODES FOREIGN KEY(job_type_id)        
        REFERENCES MGMT_JOB_TYPE_INFO(job_type_id)          
            ON DELETE CASCADE 
) MONITORING;



------------------------------------------------------------------------------------
---------------------------- INDEX UPGRADE ----------------------------------------
-----------------------------------------------------------------------------------
CREATE INDEX MGMT_JOB_IDX02 ON MGMT_JOB(job_type, job_type_major_version)
       STORAGE (INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_IDX03 ON MGMT_JOB(is_corrective_action, broken)
       STORAGE (INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_EXEC_SUMM_IDX05 ON
    MGMT_JOB_EXEC_SUMMARY(status, status_bucket, scheduled_time)
    STORAGE(INITIAL 64K NEXT 64K);

CREATE INDEX MGMT_JOB_PARAM_SOURCE_IDX01 ON
    MGMT_JOB_PARAM_SOURCE(job_type_id);

CREATE INDEX MGMT_JOB_SEC_INFO_IDX01 ON
    MGMT_JOB_SEC_INFO(job_type_id);

CREATE INDEX MGMT_JOB_LOCK_INFO_IDX01 ON
    MGMT_JOB_LOCK_INFO(job_type_id);

CREATE  INDEX MGMT_JT_URI_INFO_IDX01 ON
   MGMT_JOB_TYPE_URI_INFO(job_type_id);

CREATE  INDEX MGMT_JT_DISPLAY_PARAM_IDX01 ON
   MGMT_JOB_TYPE_DISPLAY_PARAM(job_type_id);


CREATE  INDEX MGMT_JT_DISPLAY_INFO_IDX01 ON
   MGMT_JOB_TYPE_DISPLAY_INFO(job_type_id);

REM
REM Indexes for MGMT_JOB_STATE_CHANGES
REM 
CREATE INDEX MGMT_JOB_STATE_CHGS_IDX01 ON MGMT_JOB_STATE_CHANGES(execution_id);

REM 
REM Indexes for MGMT_CORRECTIVE_ACTION
REM    
CREATE INDEX MGMT_CORRECTIVE_ACTION_IDX01 ON 
    MGMT_CORRECTIVE_ACTION(ca_target_guid);

-- Create the http session tables
CREATE TABLE MGMT_HTTP_SESSION_OBJECTS (
    session_id VARCHAR2(256),
    oms_failover_id NUMBER,
    object_type VARCHAR2(32),
    object_guid RAW(16),
    CONSTRAINT PK_MGMT_HTTP_SESS_OBJS 
        PRIMARY KEY(session_id, object_type, object_guid)
);


CREATE TABLE MGMT_HTTP_SESSION_CALLBACKS (
    object_type VARCHAR2(32),
    callback_name VARCHAR2(64),
    CONSTRAINT PK_MGMT_HTTP_SESS_CBS
        PRIMARY KEY(object_type, callback_name)
);

rem
rem MGMT_JOB_ASSOC_PARAMS
rem
rem PURPOSE
rem  The MGMT_JOB_ASSOC_PARAMS table stores the parameters
rem  referenced in the ASSOC parameter sources in a job type.
rem 
rem COLUMNS
rem
rem SOURCE_ID The unique id of this source
rem TARGET_NAMES_PARAM The name of the vector parameter which 
rem will contain the associated target names.
rem TARGET_TYPES_PARAM The name of the vecotr parameter which
rem will contain the associated target types.
rem ASSOC_NAME The association that is used
rem SRC_TARGET_NAME The target which is the source for the association
rem SRC_TARGET_TYPE The target type of the source target
rem ASSOC_TARGET_TYPE The desired target type for associated targets 
rem Note. If ASSOC_TARGET_TYPE is NULL it implies ALL targets
rem filter the associated targets
rem

CREATE TABLE MGMT_JOB_ASSOC_PARAMS (
    source_id RAW(16), 
    target_names_param VARCHAR2(&JOB_PARAM_NAME_SIZE) NOT NULL,
    target_types_param VARCHAR2(&JOB_PARAM_NAME_SIZE) NOT NULL,
    assoc_name VARCHAR2(64) NOT NULL,
    src_target_name VARCHAR2(&EM_TARGET_NAME_SIZE) NOT NULL,
    src_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE) NOT NULL,
    assoc_target_type VARCHAR2(&EM_TARGET_TYPE_SIZE),
    CONSTRAINT PK_ASSOC_SRC PRIMARY KEY(source_id),
    CONSTRAINT FK_ASSOC_PARAM_SRC FOREIGN KEY(source_id) 
         REFERENCES MGMT_JOB_PARAM_SOURCE(source_id)
               ON DELETE CASCADE
)  TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;

rem
rem  MGMT_NESTED_JOB_TARGETS
rem
rem  PURPOSE
rem    This table holds the targets for nested jobs and flatten steps.
rem
rem  COLUMNS
rem    JOB_TYPE_ID The job type id
rem    STEP_NAME The name of the step
rem    STEP_TYPE The type of the step
rem    TARGET_NAME The target name
rem    TARGET_TYPE The target type
rem
rem  NOTES
rem
rem
DECLARE
    t_cnt NUMBER := 0;
BEGIN
    BEGIN
        select count(*) into t_cnt
            from ALL_OBJECTS
            where OBJECT_NAME = 'MGMT_JOB_NESTED_JOB_TARGETS'
              and OBJECT_TYPE = 'TABLE';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    IF (t_cnt = 0) THEN
        EXECUTE IMMEDIATE
       'CREATE TABLE MGMT_NESTED_JOB_TARGETS (
            job_type_id RAW(16),
            step_name VARCHAR2(&STEP_NAME_SIZE),
            step_type NUMBER(2),
            target_name VARCHAR2(&EM_TARGET_NAME_SIZE),
            target_type VARCHAR2(&EM_TARGET_TYPE_SIZE)
        )MONITORING';
    ELSE
        EXECUTE IMMEDIATE
       'ALTER TABLE MGMT_JOB_NESTED_JOB_TARGETS RENAME TO MGMT_NESTED_JOB_TARGETS';
        EXECUTE IMMEDIATE
       'ALTER TABLE MGMT_NESTED_JOB_TARGETS RENAME COLUMN NESTED_JOB_NAME TO STEP_NAME';
        EXECUTE IMMEDIATE
       'ALTER TABLE MGMT_NESTED_JOB_TARGETS ADD (
            step_type NUMBER(2)
        )';
    END IF;
END;
/

CREATE OR REPLACE TYPE MGMT_JOB_TARGET_TYPE AS OBJECT (
    target_guid RAW(16)
);
/

CREATE OR REPLACE TYPE MGMT_JOB_GUID_LIST AS TABLE OF MGMT_JOB_TARGET_TYPE;
/

-- Upgrade for MGMT_JOB_SCHEDULE_RECORD. Do this at the end so as to
-- not invalidate any packages
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 grace period for starting the job specified in minutes. 
    -- If the job does not start between start_time and 
    -- start_time + start_grace_period it is skipped. The default of -1
    -- indicates an infinite grace period.
    start_grace_period NUMBER,

    -- 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)
   ,job_type_category   NUMBER(1)
   );
/

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(start_time, step_status, emd_url);