Rem drv: <create type="types" pos="ecm/ecm_types.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/jobs/jobs_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:14 rbali Exp $
Rem
Rem jobs_types.sql
Rem
Rem Copyright (c) 2002, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/latest/jobs/jobs_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/jobs/jobs_types.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      jobs_types.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    gbafna      02/03/25 - 
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    rmacidlo    11/03/20 - custom type MGMT_JOB_PURGE_RESULT
Rem    orazoma     03/20/15 - Bug 19483646: Increased size of
Rem                           MGMT_JOB_DNAME_TYPE_PROP_REC.target_name
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    lsatyapr    05/22/14 - Move 13.1 - 12.2
Rem    lsatyapr    04/08/13 - Bug16572647 Fix ordering of type creation
Rem    rdabbott    03/15/13 - use varchar2
Rem    amathur     11/27/12 - 13.1 upgrade loc change
Rem    nitijain    10/12/11 - bug 11821439 - type MGMT_JOB_STEP_LIST_ARRAY
Rem    nitijain    10/12/11 - bug 11821439 - added type MGMT_JOB_STEP_LIST_ARRAY
Rem    rdabbott    06/26/11 - fix 10131443: add step res bundle to simple st
Rem                           rec
Rem    shapatel    05/24/11 - move mgmt_job_int_array type to internalSDK
Rem    emeijer     03/02/11 - bug 11735165 - Add ready_time, priority and start_time
Rem    lsatyapr    08/23/10 - Fix size of jobtype in types
Rem    amathur     07/08/10 - add credentials as part of step details
Rem    jvishen     04/29/10 - move types to SDK
Rem    lsatyapr    04/20/10 - Add plugin and sdklevel
Rem    amathur     02/03/10 - add credential data structure
Rem    rdabbott    11/08/08 - Backport rdabbott_bug-7366969 from
Rem                           st_emcore_10.2.0.1.0
Rem    rdabbott    09/17/08 - fix 7366969: rm MGMT_JOB_S_OR_V_PARAM
Rem    anoopkum    10/24/08 - adding MGMT_JOB_STEP_VECTOR_PARAMS
Rem    anoopkum    10/22/08 - bug 7427803: add MGMT_JOB_STEP_PARAM_RECORD
Rem    supal       09/02/08 - Target specific params
Rem    lsatyapr    08/21/08 - Backport from MAIN
Rem    anoopkum    07/23/08 - add MGMT_JOB_RETRY_ERROR_RECORD and MGMT_JOB_RETRY_RUN_ERRORS
Rem    rdabbott    07/08/08 - Change MGMT_JOB_STEP_RECORD
Rem    rdabbott    06/20/08 - rm wizard support
Rem    rdabbott    06/17/08 - rm plug in
Rem    rdabbott    05/14/08 - rm cmdb
Rem    lsatyapr    04/29/07 - Merge 11g jobs types from sql/11.0.0.0
Rem    lsatyapr    01/04/07 - Separate 11g types
Rem    lsatyapr    12/28/06 - Fault handling support
Rem    jvishen     12/04/06 - ADF uri support
Rem    rdabbott    12/26/06 - use defn for nlsid size
Rem    lsatyapr    11/12/06 - Upgrade MGMT_JOB_VALUE_PARAMS
Rem    ashugupt    11/14/06 - added job typemetadata to task metadata
Rem    ashugupt    10/23/06 - added MGMT_JOB_CHARACTERSTICS_RECORD
Rem    nqureshi    09/17/06 - adding param_type in MGMT_JOB_PARAMETER_ARRAY
Rem    skini       08/25/06 - Cutover to Lohits types
Rem    skini       08/22/06 - Fix rep errors
Rem    kmanicka    07/31/06 - trusted remoteOp
Rem    kmanicka    07/10/06 - bug 5365979 add job_params to MGMT_JOB_STEP_RECORD
Rem    jvishen     08/02/06 - cut over to MGMT_JOB_PRMETER_FIELD_ARRAY
Rem    lsatyapr    07/31/06 - Add types for LValues
Rem    lsatyapr    07/07/06 - Add types for holding structured data 
Rem    aptrived    05/11/06 - Bug#5151248, correcting size of property_name 
Rem    pkantawa    09/08/05 - Fix 4582672: Fix MGMT_PARAM_CRED_TGT_MET 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    nqureshi    06/21/05 - adding support for online help topic for 
Rem                           multitask job 
Rem    nqureshi    06/14/05 - adding new Types for Target props validation 
Rem    ashugupt    05/15/05 - Fix 4338761 
Rem    jvishen     05/19/05 - change broken CA job ID 
Rem    jvishen     04/12/05 - edit Multitask broken CA 
Rem    jvishen     03/28/05 - update mgmt_target_fixca_record 
Rem    pkantawa    03/22/05 - Fix 4148623: Remove old unused types 
Rem    jvishen     03/20/05 - update for broken ca links 
Rem    dsahrawa    02/24/05 - add mgmt_job_guid_list 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    nqureshi    02/01/05 - adding new type to hold job Notification info 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    nqureshi    01/06/05 - modying show_target_property 
Rem    nqureshi    12/22/04 - modfying MGMT_JOB_PRMTER_URI_INF_RECORD
Rem    jvishen     12/23/04 - change CA severity reasons from Strings to 
Rem                           Number 
Rem    pkantawa    12/13/04 - Return target name instead of display name 
Rem    jaysmith    11/18/04 - populate broken CA reason 
Rem    jvishen     10/29/04 - Object for target with broken credentials 
Rem                           Changed 
Rem    dsahrawa    10/01/04 - impl start grace periods 
Rem    pkantawa    10/14/04 - Add job type category to JobRunObjType 
Rem    pkantawa    10/08/04 - grabtrans 'ashugupt_edit_multi_task_page' 
Rem    pkantawa    10/07/04 - add types for target records used to get 
Rem                           timezones 
Rem    ashugupt    10/05/04 - added new types for fetching task metadata 
Rem    jvishen     09/28/04 - 
Rem    jvishen     09/21/04 - 
Rem    ashugupt    08/20/04 - adding guid to mgmt_credential_source_record 
Rem    nqureshi    08/07/04 - added new job types for data driven Parameters 
Rem                           page 
Rem    pkantawa    08/09/04 - grabtrans 'nqureshi_naim_parameter_2' 
Rem    ashugupt    08/03/04 - new sql types added for credential page 
Rem    dcawley     07/07/04 - Increase user name size 
Rem    ashugupt    07/07/04 - job system credential page changes 
Rem    dsahrawa    06/10/04 - skipped executions implementation 
Rem    jberesni    10/28/03 - JobRunObjType.scheduled_date=>scheduled_time
Rem    tzimmerm    10/24/03 - Replacing job ui PL/SQL 
Rem    tzimmerm    09/24/03 - Fix group links - rem exec submitted to 
Rem    tzimmerm    09/12/03 - Add execution row and table types 
Rem    skini       09/09/03 - Add timezone_region to schedule record 
Rem    tzimmerm    08/26/03 - Add exec_id to API for run links 
Rem    tzimmerm    08/21/03 - Modify job run API for Run Details links 
Rem    tzimmerm    08/19/03 - Add job run tmp table type defs 
Rem    skini       06/11/03 - Large parameter support
Rem    skini       04/15/03 - New type for paramsource
Rem    skini       02/21/03 - Change in param size to 4000
Rem    skini       09/17/02 - Add job lock record
Rem    rpinnama    09/18/02 - Make all types create or replace
Rem    skini       07/12/02 - Change in target_name column size
Rem    skini       06/14/02 - continue implementing blackouts
Rem    skini       06/12/02 - 
Rem    skini       06/07/02 - Changes to support TIMEZONE_SPECIFIED
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/14/02 - Created
Rem

@&EM_SQL_ROOT/core/latest/jobs/jobs_defines.sql

-- Create all the SDK job types
@&EM_SQL_ROOT/core/latest/emSDK/automation/sdk_job_types.sql
-- Create all internal SDK types
@&EM_SQL_ROOT/core/latest/emInternalSDK/automation/isdk_job_types.sql

--type used in restart_job_run
CREATE OR REPLACE TYPE MGMT_JOB_RETRY_ERROR_RECORD AS OBJECT
(
    p_target_name   VARCHAR2(256),
    p_error_code    VARCHAR2(256),
    p_error_message VARCHAR2(4000)
);
/

--type used in restart_job_run
CREATE OR REPLACE TYPE MGMT_JOB_RETRY_RUN_ERRORS AS TABLE OF MGMT_JOB_RETRY_ERROR_RECORD;
/ 

-- The type that represents values of a vector parameter with max size of varchar2
CREATE OR REPLACE TYPE MGMT_JOB_BIG_VECTOR_PARAMS AS VARRAY(&VECTOR_ARRAY_SIZE) 
    OF VARCHAR2(&FULL_SIZE_VARCHAR2);
/

CREATE OR REPLACE TYPE MGMT_JOB_STEP_PARAM_RECORD UNDER MGMT_JOB_PARAM_RECORD
( 
    big_scalar_value VARCHAR2(&FULL_SIZE_VARCHAR2),
    big_vector_value MGMT_JOB_BIG_VECTOR_PARAMS,

    -- add following functions to update scalar and vector values
    OVERRIDING MEMBER FUNCTION  get_scalar_value   RETURN VARCHAR2,
    OVERRIDING MEMBER PROCEDURE set_scalar_value   ( p_scalar_value IN VARCHAR2),
    OVERRIDING MEMBER FUNCTION  get_vector_length  RETURN INTEGER,
    OVERRIDING MEMBER FUNCTION  get_vector_element ( p_pos INTEGER ) RETURN VARCHAR2,
    OVERRIDING MEMBER PROCEDURE set_vector_element ( p_pos IN INTEGER, p_value IN VARCHAR2)
); 
/

CREATE OR REPLACE TYPE MGMT_JOB_STEP_STATUS AS OBJECT (
    step_id INTEGER,
    status INTEGER
);
/

CREATE OR REPLACE TYPE MGMT_JOB_TARGET_LIST_ARRAY AS TABLE OF MGMT_JOB_TARGET_LIST;
/

CREATE OR REPLACE TYPE MGMT_JOB_TARGET_TYPE AS OBJECT (
    target_guid &GUID_TYPE
);
/

CREATE OR REPLACE TYPE MGMT_JOB_GUID_LIST AS TABLE OF MGMT_JOB_TARGET_TYPE;
/





-- This object serves as one criterion in a purge policy
CREATE OR REPLACE TYPE MGMT_JOB_PURGE_CRITERION AS OBJECT (
    -- The criterion type. This could be CRITERION_TARGET, CRITERION_USER,
    -- CRITERION_JOBTYPE or CRITERION_JOBNAME
    criterion_type NUMBER,

    -- Set to true(1) if the sense of the condition is to be negated.
    -- For example, when used with targets, indicates purging of all
    -- jobs that do not contain the specified target(s).
    negated NUMBER,

    -- The possible values the criterion could take. For CRITERION_USER, 
    -- CRITERION_JOBTYPE and CRITERION_JOBNAME, the values are in the 
    -- criterion_values array. For CRITERION_TARGET, the target names are 
    -- in the criterion_values array, and the target types are in the 
    -- addl_criterion_values array. 
    criterion_values MGMT_JOB_VECTOR_PARAMS,
    addl_criterion_values MGMT_JOB_VECTOR_PARAMS
);
/

CREATE OR REPLACE TYPE MGMT_JOB_PURGE_CRITERION_LIST AS TABLE OF 
       MGMT_JOB_PURGE_CRITERION;
/

-- Internally used types
CREATE OR REPLACE TYPE MGMT_JOB_TARGET_WITH_INDEX AS OBJECT (
    target_name varchar2(256),
    target_type varchar2(64),
    container_path varchar2(4000),
    target_index NUMBER
);
/
CREATE OR REPLACE TYPE MGMT_JOB_TARGET_INDEX_LIST AS 
    TABLE OF MGMT_JOB_TARGET_WITH_INDEX;
/

CREATE OR REPLACE TYPE MGMT_JOB_LOCK_RECORD AS OBJECT (
   lock_guid &GUID_TYPE,
   lock_name VARCHAR2(32),
   lock_type NUMBER,
   lock_mode NUMBER,
   target_name VARCHAR2(256),
   target_type VARCHAR2(64),
   target_guid &GUID_TYPE
);
/

CREATE OR REPLACE TYPE MGMT_JOB_LOCK_ARRAY AS TABLE OF MGMT_JOB_LOCK_RECORD;
/

CREATE OR REPLACE TYPE MGMT_JOB_PARAMSRC_RECORD AS OBJECT (
    source_id &GUID_TYPE,
    source_type VARCHAR2(32),
    source_data VARCHAR2(4000),
    override_user NUMBER(1),
    required NUMBER(1),
    encrypted NUMBER(1),
    parameter_names MGMT_JOB_VECTOR_PARAMS

);
/

CREATE OR REPLACE TYPE MGMT_JOB_PARAMSRC_ARRAY AS TABLE OF MGMT_JOB_PARAMSRC_RECORD;
/

-- -----------------------------------------------------------------------------
--  Job Run type definitions
-- -----------------------------------------------------------------------------
create or replace TYPE JobRunObjType as OBJECT
   (job_id              &GUID_TYPE
   ,job_name            VARCHAR2(64)
   ,job_owner           VARCHAR2(256)
   ,job_type            VARCHAR2(&JOB_TYPE_NAME_SIZE)
   ,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      &GUID_TYPE
   ,job_type_category   NUMBER(1)
   );
/

create or replace TYPE JobRunObjTbl as TABLE of JobRunObjType;
/

-- -----------------------------------------------------------------------------
--  job credentials metadata definitions
-- -----------------------------------------------------------------------------

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 VARCHAR2(&NLSID_SIZE)
);
/

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(&NLSID_SIZE),
    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(&NLSID_SIZE),
    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(&NLSID_SIZE),
    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)
);
/

-- -----------------------------------------------------------------------------
--  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,
    task_name VARCHAR2(64),
    cred_set_nlsid VARCHAR2(&NLSID_SIZE),
    cred_set_disp_name VARCHAR2(64)
);
/

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 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(&JOB_TYPE_NAME_SIZE),    
    uri        VARCHAR2(4000),    
    class      VARCHAR2(4000),    
    online_help_topic VARCHAR2(2000),
    online_task_help_topic VARCHAR2(2000),
    show_target_properties NUMBER(1),
    file_type NUMBER(1)
);
/

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(&JOB_TYPE_NAME_SIZE),
uri_use    NUMBER(2),    
uri        VARCHAR2(4000),    
class      VARCHAR2(4000),
file_type  NUMBER(1)
);
/

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(64),
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 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(&NLSID_SIZE), 
    severity           NUMBER(2),
    ca                 VARCHAR2(64),
    current_owner      VARCHAR2(256),
    job_id             &GUID_TYPE,
    metric_key_value   VARCHAR2(1300),
    broken_reason      NUMBER(2),
    policy_type        NUMBER(2),
    job_type           VARCHAR2(&JOB_TYPE_NAME_SIZE)
);
/

REM Represents an array of broken credentials of targets
CREATE OR REPLACE TYPE MGMT_TARGET_FIXCA_ARRAY AS
    TABLE OF MGMT_TARGET_FIXCA_RECORD;
/
 

-- -----------------------------------------------------------------------------
--  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;
/


-- Internally used types
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 
     );
/

CREATE OR REPLACE TYPE MGMT_JOB_SHORT_STR_ARR_TABLE AS TABLE OF MGMT_SHORT_STRING_ARRAY ;
/
CREATE OR REPLACE TYPE MGMT_JOB_TARGET_LIST_TABLE AS TABLE OF MGMT_JOB_TARGET_LIST ;
/

CREATE OR REPLACE TYPE MGMT_JOB_DNAME_TYPE_PROP_REC AS OBJECT
(
 target_display_name VARCHAR2(256),
 target_name VARCHAR2(256),
 target_type VARCHAR2(64),
 propName VARCHAR2(64)

);
/

CREATE OR REPLACE TYPE MGMT_JOB_DNAME_TYPE_PROP_TABLE AS TABLE OF  MGMT_JOB_DNAME_TYPE_PROP_REC;
/


CREATE OR REPLACE TYPE MGMT_JOB_SIMPLE_STEP_RECORD AS OBJECT (
    command_name   VARCHAR2(&COMMAND_NAME_SIZE),
    command_type   NUMBER(1),
    command_class  VARCHAR2(&CLASS_NAME_SIZE),
    command_bundle VARCHAR2(&CLASS_NAME_SIZE),
    step_params    MGMT_JOB_PARAM_LIST,
    agent_bound    INTEGER,
    trusted        NUMBER(1),
    step_creds     GC_JOB_CRED_ARRAY
);
/

CREATE OR REPLACE TYPE MGMT_JOB_STMT_EXEC_DETAILS AS OBJECT (
    job_type_id &GUID_TYPE,
    job_id &GUID_TYPE,
    execution_id &GUID_TYPE,
    step_id INTEGER,
    source_step_id INTEGER,
    original_step_id INTEGER,
    restart_mode INTEGER,
    step_name VARCHAR2(&STEP_NAME_SIZE),
    step_type NUMBER(2),
    command_type NUMBER(1),
    iterate_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
    iterate_param_index NUMBER(8),
    parent_step_id INTEGER,
    step_status NUMBER(2),
    start_time DATE,
    end_time DATE,
    timezone_region VARCHAR2(64),

    CONSTRUCTOR FUNCTION MGMT_JOB_STMT_EXEC_DETAILS
        RETURN SELF AS RESULT
);
/

-- Easier to recreate than to alter!
-- NOTE: This type must already be dropped in upgrade
CREATE OR REPLACE TYPE MGMT_JOB_STEP_RECORD AS OBJECT (
    job_type     VARCHAR2(&JOB_TYPE_NAME_SIZE),
    job_name     VARCHAR2(&JOB_PARAM_NAME_SIZE),
    job_owner    VARCHAR2(&USERNAME_SIZE),
    run_identity VARCHAR2(&USERNAME_SIZE),
    job_id       &GUID_TYPE,
    execution_id &GUID_TYPE,
    step_name    VARCHAR2(&JOB_PARAM_NAME_SIZE),
    step_type    NUMBER(2),
    step_id      INTEGER,
    step_status  NUMBER(2),

    iterate_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
    iterate_param_index NUMBER(6),

    first_step   NUMBER(1),

    job_params   MGMT_JOB_PARAM_LIST,

    -- Valid for steps only
    step_info    MGMT_JOB_SIMPLE_STEP_RECORD,

    plugin       VARCHAR2(&PLUGIN_ID_SIZE),
    sdk_level    INTEGER,

    -- Step-scheduler produced data.
    start_time   DATE,
    ready_time   DATE,
    priority     INTEGER,

    CONSTRUCTOR FUNCTION MGMT_JOB_STEP_RECORD(p_job_id       RAW,
                                              p_execution_id RAW,
                                              p_step_name    VARCHAR2,
                                              p_step_type    NUMBER,
                                              p_step_id      INTEGER,
                                              p_step_status  NUMBER,
                                              p_command_name VARCHAR2,
                                              p_command_type NUMBER)
        RETURN SELF AS RESULT,

    CONSTRUCTOR FUNCTION MGMT_JOB_STEP_RECORD(p_stmt         MGMT_JOB_STMT_EXEC_DETAILS,
                                              p_job_type     VARCHAR2,
                                              p_job_name     VARCHAR2,
                                              p_job_owner    VARCHAR2,
                                              p_run_identity VARCHAR2,
                                              p_first_step   NUMBER,
                                              p_iterate_param_index NUMBER,
                                              p_job_params   MGMT_JOB_PARAM_LIST,
                                              p_step_info    MGMT_JOB_SIMPLE_STEP_RECORD,
                                              p_plugin       VARCHAR2,
                                              p_sdk_level    INTEGER)
        RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE MGMT_JOB_STEP_LIST AS TABLE 
       OF MGMT_JOB_STEP_RECORD;
/


CREATE OR REPLACE TYPE MGMT_JOB_STEP_LIST_ARRAY AS TABLE 
       OF MGMT_JOB_STEP_LIST;
/
/**
* Custom object type used to pass purge statistics from different
* procedures/functions
**/
CREATE OR REPLACE TYPE MGMT_JOB_PURGE_RESULT AS OBJECT (
   total_rows NUMBER,
   failed_rows NUMBER,
   snapshot_errors NUMBER,
   start_time DATE,
   end_time DATE,
   CONSTRUCTOR FUNCTION MGMT_JOB_PURGE_RESULT(p_total_rows NUMBER, 
                                              p_failed_rows NUMBER, 
                                              p_snapshot_errors NUMBER, 
                                              p_start_time DATE DEFAULT NULL)
      RETURN SELF AS RESULT,
   CONSTRUCTOR FUNCTION MGMT_JOB_PURGE_RESULT(p_total_rows NUMBER, 
                                              p_failed_rows NUMBER, 
                                              p_start_time DATE DEFAULT NULL)
      RETURN SELF AS RESULT,
   CONSTRUCTOR FUNCTION MGMT_JOB_PURGE_RESULT(p_total_rows NUMBER, 
                                              p_start_time DATE DEFAULT NULL)
      RETURN SELF AS RESULT,
   CONSTRUCTOR FUNCTION MGMT_JOB_PURGE_RESULT(p_start_time DATE DEFAULT NULL)
      RETURN SELF AS RESULT,
   MEMBER PROCEDURE inc_total_rows     (p_total_rows NUMBER DEFAULT 1),
   MEMBER PROCEDURE inc_failed_rows    (p_failed_rows NUMBER DEFAULT 1),
   MEMBER PROCEDURE inc_snapshot_errors(p_snapshot_errors NUMBER DEFAULT 1)
);
/

REM Represents a parameter field checkbox,radio buttons, drop down list.
CREATE OR REPLACE TYPE MGMT_JOB_PRMETER_FIELD_RECORD AS OBJECT (
    is_default NUMBER(1),
    value varchar2(32),
    option_text_nlsid VARCHAR2(&NLSID_SIZE),
    option_text_default VARCHAR2(256),
    param_order number(3)
);
/

REM Represents an array of One parameters FIELD VALUES (checkbox,dropdown,
REM radio buttons)
CREATE OR REPLACE TYPE MGMT_JOB_PRMETER_FIELD_ARRAY AS 
    TABLE OF MGMT_JOB_PRMETER_FIELD_RECORD;
/

REM represent one row of parameters
CREATE OR REPLACE TYPE MGMT_JOB_PARAMETER_RECORD AS OBJECT (
    job_type   VARCHAR2(&JOB_TYPE_NAME_SIZE),
    parameter_name VARCHAR2(&JOB_PARAM_NAME_SIZE),
    show_in_create NUMBER(1),
    show_in_results NUMBER(1),
    label_nlsid VARCHAR2(&NLSID_SIZE),
    label_default VARCHAR2(256),
    hint_nlsid VARCHAR2(&NLSID_SIZE),
    hint_default VARCHAR2(256),
    display_mode NUMBER(2),
    num_lines NUMBER(2),
    default_text VARCHAR2(4000),
    default_nlsid VARCHAR2(&NLSID_SIZE),
    param_order number(3),
    ARRAY_PARAMETER_FIELD MGMT_JOB_PRMETER_FIELD_ARRAY,
    parameter_required number(1),
    parameter_encrypted number(1),
    param_type number(3)
    );
/

REM Represents an array of ALL PARAMETERS set
CREATE OR REPLACE TYPE MGMT_JOB_PARAMETER_ARRAY AS 
        TABLE OF MGMT_JOB_PARAMETER_RECORD;
/

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 Job type metadata including job characterstics
create or replace TYPE MGMT_JOBTYPE_METADATA as object
(
    jobtype_editable NUMBER(1),
    can_submit_to_group NUMBER(1),
    uses_default_ui NUMBER(1),
    create_uri VARCHAR2(4000),
    file_type NUMBER(1),
    default_target_type VARCHAR2(64),
    create_text_nlsid VARCHAR2(&NLSID_SIZE),
    create_text_default VARCHAR2(256),
    context NUMBER(1),
    is_task_only NUMBER(1),
    target_selection_type NUMBER(1),
    pick_target_first NUMBER(1),
    use_default_credentials NUMBER(1),
    schedule NUMBER(1),
    CONSTRUCTOR FUNCTION MGMT_JOBTYPE_METADATA(
                        p_context NUMBER,
                        p_is_task_only NUMBER,
                        p_target_selection_type NUMBER,
                        p_pick_target_first NUMBER,
                        p_use_default_credentials NUMBER,
                        p_schedule NUMBER)
    RETURN SELF AS RESULT

);
/

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(&JOB_TYPE_NAME_SIZE),
    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),
    jobtype_metadata MGMT_JOBTYPE_METADATA
);
/

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;
/

/**
 * DB Object type for the list of credential types that can be specified in the 
 * filtering criteria for the credential usage metadata element.
 */

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

CREATE OR REPLACE TYPE EM_JOB_CREDSET_LIST AS
    VARRAY(&EM_MAX_CREDTYPE_ARRAY_SIZE) OF VARCHAR2(&CRED_SET_NAME_SIZE);
/

grant execute on EM_JOB_CREDSET_LIST to sysman;

BEGIN
    SYSMAN.CUTOVER_SYNONYM('EM_JOB_CREDSET_LIST');
END;
/

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;

/**
 * DB object for a job retrieval from repos,  data to mimic the java object.
 * Henceforth this is the object which should be used to load java object from repos
 * a new job or edit a job.
 * @param name job name
 */
CREATE OR REPLACE TYPE EM_JOB_RECORD UNDER GC_JOB_RECORD
( 
    owner                VARCHAR2(&USERNAME_SIZE),
    is_library           NUMBER,
    is_corrective_action NUMBER
); 
/
-- new types in 12.2.0.0
@&EM_SQL_ROOT/core/12.2.0.0/jobs/jobs_types.sql


