Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/jobs/jobs_ui_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/3 2009/03/11 23:41:06 lsatyapr Exp $
Rem
Rem jobs_ui_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      jobs_ui_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      PL/SQL for the job UI
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jvishen     02/15/09  - XbranchMerge jvishen_bug-6367987 from main
Rem    jvishen     01/28/09  - XbranchMerge jvishen_bug-6476184 from
Rem                            st_emcore_10.2.0.1.0
Rem    jvishen     06/05/08  - Backport jvishen_bug-6214949 from main
Rem    jvishen     11/08/07  - perf:fix get_task_details
Rem    nqureshi    07/16/07  - bug 6157568 fortify security issues
Rem    nqureshi    03/28/06  - bug 5116980 
Rem    nqureshi    05/09/07  - Backport nqureshi_bg-5116980 from main
Rem    nqureshi    04/18/07  - XbranchMerge kmanicka_pdp5 from main
Rem    skini       08/09/06  - Fix merge errors
Rem    skini       08/08/06  - Fix scheduled executions
Rem    skini       08/07/06  - Code review changes
Rem    skini       08/04/06  - Return error log if present
Rem    skini       08/08/06  - Fix scheduled executions
Rem    skini       08/07/06  - Code review changes
Rem    skini       08/04/06  - Return error log if present
Rem    skini       08/09/06  - Backport skini_bug-5395103 from main
Rem    lefeng      07/26/06  - Bug 4634618: fix default target mode for 
Rem                            service  target type 
Rem    lefeng      08/09/06  - Backport lefeng_bug-4634618 from main
Rem    nqureshi    06/29/06  - adding pdp support for credentials 
Rem    nqureshi    06/28/06  - adding pdpinfo while fetching jobInfo 
Rem    rdabbott    06/13/06  - fix 5105608: perf of group status rollup 
Rem    rdabbott    07/16/06  - Backport rdabbott_bug-5105608 from main
Rem    aptrived    07/11/06  - Backport aptrived_bug-4898267 from main 
Rem    jvishen     12/07/05  - Backport jvishen_bug4633215 from main 
Rem    jvishen     12/02/05  - fix #4633215 
Rem    nqureshi    09/16/05  - is_corrective_action set for nested cas 
Rem    jvishen     09/11/05  - fix 4595498 
Rem    skini       09/09/05  - Pick up correct version of job type in result 
Rem                            queries 
Rem    nqureshi    08/31/05  - bug 4585496 renaming get_job_type -> 
Rem                            get_job_details, adding few out parameters 
Rem    ashugupt    09/01/05  - Fix 4582689 
Rem    dcawley     08/30/05  - Change api 
Rem    jaysmith    08/26/05  - accept aggregate target types as 
Rem                            initial_target_type 
Rem    nqureshi    08/22/05  - adding procedure to get the jobType from the 
Rem                            executionId 
Rem    pkantawa    08/23/05  - Fix 4522120: exclude CAs 
Rem    jvishen     08/04/05  - fix 4534858 
Rem    skini       08/02/05  - Propogate change to get_exec_details and 
Rem                            get_task_details 
Rem    skini       08/01/05  - Fix bug 4305911: order by start_time in 
Rem                            addition to step id 
Rem    pkantawa    07/29/05  - Fix 4522120: rollup queries to exclude system 
Rem                            jobs 
Rem    dsahrawa    07/28/05  - change mgmt_job_step_params.is_scalar to 
Rem                            mgmt_job_step_params.parameter_type 
Rem    pkantawa    07/27/05  - always get data for default target type 
Rem    rdabbott    07/19/05  - fix 4284953: corner case - number too big 
Rem    jvishen     07/13/05  - update get_task_breadcrumbs to get job or CA 
Rem    pkantawa    07/01/05  - Select only target types that have targets
Rem    rdabbott    06/30/05  - respect passed in time frame
Rem    jaysmith    06/21/05  - target-types for MS_*_CAs in get_job_info 
Rem    jaysmith    06/10/05  - set MS CA default_target_type_out 
Rem    ashugupt    06/17/05  - Fix 3750693 
Rem    nqureshi    06/21/05  - adding support for online help topic for 
Rem                            multitask job 
Rem    nqureshi    06/14/05  - adding do_repos_Validation 
Rem    rdabbott    06/17/05  - werner review 
Rem    rdabbott    06/16/05  - scope create like name to current user 
Rem    rdabbott    06/03/05  - fix 4284953: generate create like/submit name 
Rem                            suggestion 
Rem    nqureshi    06/03/05  - fixing 4404087 display execution elapsed time 
Rem                            in case of display of single step job 
Rem    rdabbott    05/20/05  - status in clause update 
Rem    rdabbott    05/11/05  - add 3rd value to get run status 
Rem    rdabbott    05/09/05  - rename get exec status -> get run status 
Rem    pkantawa    05/23/05  - Fix 4286126: Check if user has VIEW on all 
Rem                            submitted targets 
Rem    jvishen     05/03/05  - fix bug #4343471 
Rem    pkantawa    05/02/05  - Fix 4337833: guard against no_data_found 
Rem    ashugupt    04/12/05  - added procedure get_execution_status 
Rem    pkantawa    04/27/05  - Remove nls entries from get_job_info 
Rem    jvishen     04/12/05  - edit Multitask broken CA 
Rem    pkantawa    04/11/05  - Return job status for regular and multi task 
Rem                            jobs 
Rem    ashugupt    03/28/05  - fixed bug in get_preferred_cred_not_set 
Rem    nqureshi    03/29/05  - fixing 4259864 
Rem    pkantawa    03/22/05  - Fix 4148623: Remove dynamic sql 
Rem    nqureshi    03/21/05  - removing timezone list from get_job_info and 
Rem                            get_multitask_info 
Rem    jvishen     03/15/05  - update for broken CA links 
Rem    ashugupt    03/10/05  - changing get_execution_details api to return 
Rem                            output_id 
Rem    pkantawa    03/09/05  - exclude delete pending jobs 
Rem    pkantawa    03/01/05  - Add procedures for 'kill step' feature 
Rem    kmanicka    03/04/05  - move to EMDW_LOG from dbms_output
Rem    skini       02/28/05  - Code review comments 
Rem    skini       02/28/05  - Fix status issues for single-step jobs 
Rem    skini       02/20/05  - Fix results page issues 
Rem    dcawley     02/21/05  - Replace enter super user mode 
Rem    nqureshi    02/18/05  - fixing 4188359 
Rem    nqureshi    02/19/05  - grabtrans 'nqureshi_bugs' 
Rem    nqureshi    02/17/05  - fixing 4188359 
Rem    nqureshi    02/14/05  - fixing jobNotificationCount for Execution and 
Rem                            task Pages 
Rem    nqureshi    02/09/05  - fixing 4169864 
Rem    nqureshi    02/07/05  - integrating jobs Notification to Multi task job 
Rem    nqureshi    02/07/05  - fixing multy target job Sqlexception 
Rem    nqureshi    02/01/05  - adding new in/out parameter in get_job_info for 
Rem                            job Notification 
Rem    nqureshi    01/28/05  - modifying get_task_breadcrumbs 
Rem    ashugupt    01/17/05  - fetch info abt error codes in executions 
Rem    ramalhot    01/17/05  - assoc def name changed from member to contains 
Rem    ramalhot    01/17/05  - g_member_guid->g_contains_guid
Rem    nqureshi    01/17/05  - implementing job_status for Execution 
Rem                            Details,Task details also 
Rem    jvishen     01/11/05  - update for CAs 
Rem    skini       01/07/05  - Fix bug 3794702: respect target privileges on 
Rem                            run details page 
Rem    pkantawa    01/11/05  - Remove timezone procedures 
Rem    pkantawa    01/07/05  - Return timezones in create job initialization 
Rem                            procedures 
Rem    pkantawa    01/04/05  - Fix queries that query aggregates and clusters 
Rem    jvishen     12/23/04  - modifying  CA Severity Texts.
Rem    ashugupt    12/29/04  - added fetching of job_status in run details 
Rem    nqureshi    12/13/04  - modifing procedure to display submitted targets 
Rem    pkantawa    12/09/04  - Get create text for create job 
Rem    pkantawa    11/30/04  - Fix aggregate check query 
Rem    ashugupt    11/26/04  - added fetching of large parameter id 
Rem    jaysmith    11/18/04  - populate broken CA reason 
Rem    skini       11/17/04  - Cutover credentials_disabled column to broken 
Rem                            column 
Rem    ashugupt    11/16/04  - fix for superuser list in multitask job 
Rem    skini       11/15/04  - Fix step nls default 
Rem    skini       11/09/04  - Fix broken query 
Rem    skini       11/09/04  - Fix latest versioning queries 
Rem    skini       11/01/04  - Remove hack for all target types 
Rem    pkantawa    11/04/04  - fix computeAssociatedTypes 
Rem    dsahrawa    10/08/04  - 
Rem    ashugupt    11/03/04  - added new fn to fetch targets for target 
Rem                            aggregates 
Rem    nqureshi    11/02/04  - adding schedule info in get_run_details 
Rem                            ,get_execution_details etc .. 
Rem    jvishen     10/29/04  - Object for target with broken credentials 
Rem                            Changed 
Rem    jaysmith    10/27/04  - support multi-task in CA From Library select 
Rem                            list 
Rem    nqureshi    10/28/04  - adding procedures for results UI 
Rem    pkantawa    10/25/04  - Changes to enter create UI in scope of a target 
Rem    skini       10/27/04  - Versioning support, part 2 
Rem    skini       10/20/04  - Versioning changes 
Rem    jaysmith    10/22/04  - pass target type to get_multitask_job_info 
Rem    jaysmith    10/22/04  - library CAs need not have a target type 
Rem    pkantawa    10/18/04  - Add function to check if an aggregate target 
Rem                            contains atleast one member of base type 
Rem    yxie        10/08/04  - grabtrans 'yxie_difs_solve' 
Rem    jaysmith    10/13/04  - ca selection list 
Rem    pkantawa    10/12/04  - account for Multi Task jobs in queries 
Rem    ashugupt    10/12/04  - multi task job changes 
Rem    pkantawa    10/08/04  - grabtrans 'ashugupt_edit_multi_task_page' 
Rem    pkantawa    10/07/04  - add procedure to get target timezone 
Rem    ashugupt    10/07/04  - added new APIs to fetch task metadata 
Rem    ramalhot    10/05/04 -  g_contains_guid removed from dynamic sql 
Rem    pkantawa    10/05/04 - Update get_param_creds_info 
Rem    pkantawa    10/03/04 - Make task metadata fetching conditional 
Rem    pkantawa    09/30/04 - Add procedures to initialize task info 
Rem    jvishen     09/28/04 - Added to get Broken Creds for targets in a group 
Rem    jaysmith    09/15/04 - allow cas in get_job_info 
Rem    jaysmith    09/14/04 - fix CA typo 
Rem    jaysmith    09/09/04 - CA Library query 
Rem    pkantawa    09/16/04 - add uses_default_create_ui function 
Rem    pkantawa    09/14/04 - Take execId as input parameter in get_job_info 
Rem    pkantawa    09/13/04 - add get_multitask_job_info procedure 
Rem    skini       09/08/04 - Support for OSCommand on other target types 
Rem    pkantawa    09/01/04 - Fixed create uri query 
Rem    pkantawa    09/01/04 - grabtrans 'nqureshi_schedule_page' 
Rem    pkantawa    09/01/04 - grabtrans 'ashugupt_jobui_newedit' 
Rem    pkantawa    08/30/04 - Make job type IN/OUT, add support for fetching 
Rem                           is_jsp flag 
Rem    nqureshi    08/26/04 - modified get_group_timezones added return 
Rem                           statement 
 
Rem    pkantawa    08/20/04 - 
Rem    skini       08/20/04 - Add schedule procedure 
Rem    skini       08/20/04 - Fixes and additions to get_job_data 
Rem    ashugupt    08/19/04 - removing fetching credentials values from 
Rem                           get_job_credential_metadata procedure 
Rem    skini       08/16/04 - Fix flag to creds routine 
Rem    skini       08/14/04 - Add procedure to get job data 
Rem    ashugupt    08/02/04 - Added new procedure to validate preferred 
Rem                           credentials 
Rem    dcawley     07/07/04 - Increase user name size 
Rem    rdabbott    03/08/04 - fix 3480003: replace encrypted by *** w/decode 
Rem    rdabbott    01/18/04 - Fix 3359637: detect empty result set for params 
Rem    xshen       12/03/03 - performance tuning OF status queries 
Rem    jmcclung    11/20/03 - get_visible_params returns large_value (bug 3134351) 
Rem    xshen       11/17/03 - check COUNT after mutates target list 
Rem    xshen       11/13/03 - tune sql on home pages 
Rem    rdabbott    11/05/03 - fix 3168375: show correct step targets 
Rem    rdabbott    11/05/03 - fix 3214990: invalid column name 
Rem    xshen       11/05/03 - do not prune direct group members 
Rem    xshen       11/04/03 - more changes 
Rem    xshen       11/04/03 - nested composite GROUP prefcred validation
Rem    xshen       10/31/03 - FOR only accessible targets on console home page
Rem    xshen       10/30/03 - do not count t-less jobs for console HP 
Rem    xshen       10/13/03 - fix console home page query 
Rem    xshen       10/10/03 - allow viewing status of all jobs on target 
Rem                           sitemap for a user having view access to the 
Rem    rdabbott    10/09/03 - Fix 3149316: duplicate param for nested job 
Rem    rdabbott    10/08/03 - add get uri method 
Rem    tzimmerm    10/01/03 - Use flattened group table 
Rem    xshen       09/24/03 - add library call 
Rem    tzimmerm    09/24/03 - Fix group links - rem exec submitted to 
Rem    tzimmerm    09/22/03 - Fix ARU jobs & other bugs 
Rem    rdabbott    09/19/03 - user new step target table 
Rem    xshen       09/15/03 - add pref cred validation calls 
Rem    tzimmerm    09/16/03 - Fix refresh merge issues 
Rem    tzimmerm    09/04/03 - Add job access & show only support 
Rem    xshen       09/15/03 - add pref cred validation calls 
Rem    skini       09/09/03 - Remove display columns 
Rem    xshen       09/02/03 - query for rac and cluster 
Rem    xshen       08/28/03 - add target rollup intg and library info calls 
Rem    tzimmerm    08/26/03 - Add exec_id to API for run links
Rem    tzimmerm    08/19/03 - Adding job run procedures 
Rem    skini       07/24/03 - Change in signature of substitute_step_params
Rem    skini       06/11/03 - Large param support
Rem    rdabbott    12/10/02 - return correct types from dual
Rem    rdabbott    12/09/02 - get job params
Rem    aholser     10/25/02 - use internal name for display name
Rem    rdabbott    07/11/02 - Fix 2440315: handle target display names
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Restructured.
Rem    skini       04/18/02 - SMP_MGMT_JOB=>MGMT_JOB
Rem    rdabbott    04/16/02 - rdabbott_step_targets
Rem    rdabbott    04/08/02 - Created
Rem

---------------------------------------------
CREATE OR REPLACE PACKAGE BODY MGMT_JOB_UI AS
---------------------------------------------
---------------------------------------------

-- Constant for job step target paramger
PARAM_STEP_TARGET constant varchar2(32) := '%argetName%';

-- Constants for CA Severities
CA_WARNING_ONLY constant NUMBER(2) := 0;
CA_CRITICAL_ONLY constant NUMBER(2) := 1;
CA_WARNING_CRITICAL_BOTH constant NUMBER(2) := 2;
CA_NONE constant NUMBER(2) := -1;

CREATE_LIKE_DELIM      constant varchar2(2) := '.';
MAX_CREATE_LIKE_SELECT constant NUMBER := '2000';

---------------------------------------------
---------------  helpers   ------------------


-- get the "version number" of this job
-- number n := get_job_num ( 'MY JOB.2',  'MY JOB.' );
--        n is 2
-- number m := get_job_num ( 'MY JOB.52', 'MY JOB.' );
--        m is 52
-- number p := get_job_num ( 'MY JOB.A',  'MY JOB.' );
--        p is -1
-- number p := get_job_num ( 'MY JOB.1.1','MY JOB.' );
--        p is -1 because of the second .
FUNCTION get_job_num ( p_job_name VARCHAR2, p_prefix VARCHAR2 ) RETURN NUMBER IS
   l_suffix MGMT_JOB.job_name%TYPE;
   l_in_str NUMBER;
BEGIN
    -- todo: verify that p_job_name really starts with p_prefix?
    l_suffix := substr ( p_job_name, length(p_prefix) + 1 );

    l_in_str := instr  ( l_suffix, CREATE_LIKE_DELIM );

    -- instr check allows me to remove the NOT LIKE from the where clause
    IF ( l_in_str > 0 ) THEN
        -- additional delim found - ignore this job
        RETURN -1;
    END IF;

    RETURN to_number ( l_suffix );

EXCEPTION
    WHEN OTHERS THEN
        RETURN -1;
END;


-- Find latest unused job name starting with passed in name
--  If My Job.1, My Job.55 exist,
--   returns My Job.56 rather than My Job.2
FUNCTION get_create_like_name_max ( p_job_name VARCHAR2  ) RETURN VARCHAR2 IS
   l_increment       NUMBER := 1;
   l_max_num         NUMBER := 0;
   l_found           NUMBER := 0;
   l_unused_job_name MGMT_JOB.job_name%TYPE;
   l_job_prefix      MGMT_JOB.job_name%TYPE;
   l_current_user    VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
BEGIN
    l_job_prefix := p_job_name || CREATE_LIKE_DELIM;

    BEGIN
         -- pick jobs that have one suffix
         -- pick p_job_name.2,   p_job_name.3
         --   or p_job_name.2.2, p_job_name.3.17
         -- NOTE the second type will be filtered out by get_job_num
        SELECT   max ( get_job_num(job_name, l_job_prefix) )
        INTO     l_max_num
        FROM     MGMT_JOB
        WHERE    job_name LIKE l_job_prefix || '%'
          AND    job_owner   = l_current_user;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            --  there are no copies of this job yet.
            l_max_num := 0;
    END;

    IF ( l_max_num is null or l_max_num <= 0 ) THEN
        -- only non numeric matches were found
        l_max_num := 0;
    END IF;

    l_unused_job_name := l_job_prefix || (l_max_num+1);

-- Not worth implementing this corner case at this time
--    l_increment := 1;
--    WHILE ( l_max_num = ( l_max_num + l_increment ) )
--    LOOP
--        -- corner case: l_max_num is beyond the percision of pl/sql
--        -- the increment is getting rounded down; try a bigger increment
--        l_increment := 10 * l_increment;
--        l_unused_job_name := l_job_prefix || (l_max_num+l_increment);
--    END LOOP;

    RETURN l_unused_job_name;

-- comment this block out when debugging this procedure
EXCEPTION
    WHEN OTHERS THEN
        --  the job_name is already the max length
        RETURN p_job_name;
END;


-- Find next unused job num starting with passed in name
--  If My Job.1, My Job.55 exist, 
--   returns My Job.2
FUNCTION get_job_num_open ( p_job_name VARCHAR2 ) RETURN NUMBER IS
   l_num             NUMBER := 10000;
   l_next_num        NUMBER := 10000;
   l_unused_job_name MGMT_JOB.job_name%TYPE;
   l_job_prefix      MGMT_JOB.job_name%TYPE;
   l_job_num_array   MGMT_JOB_INT_ARRAY;
   l_current_user    VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
BEGIN
    l_job_prefix := p_job_name || CREATE_LIKE_DELIM;

    SELECT   job_num
    BULK COLLECT 
    INTO     l_job_num_array
    FROM     
        (
          SELECT
          DISTINCT get_job_num(job_name, l_job_prefix) as job_num
          FROM
          -- pick jobs that have the suffix
          (
            SELECT   job_name
            FROM     MGMT_JOB
            WHERE    job_name  LIKE l_job_prefix || '%'
              AND    job_owner    = l_current_user
            -- todo: do we need both order bys? get rid of this one?
            ORDER BY job_name
          )
          ORDER BY job_num
        )
    WHERE ROWNUM < MAX_CREATE_LIKE_SELECT;

    l_next_num := 1;

    FOR i IN 1..l_job_num_array.COUNT LOOP

        l_num := l_job_num_array(i);

        IF    ( l_next_num < l_num ) THEN
            -- found a gap in the numbers
            RETURN l_next_num;

        ELSIF ( l_next_num = l_num ) THEN
            -- found this value, try the next one
            l_next_num := l_next_num + 1;

     -- ELSIF ( l_next_num > l_num ) THEN
            -- look for l_next_num later in the array
        END IF;

    END LOOP;

    IF ( l_job_num_array.COUNT < MAX_CREATE_LIKE_SELECT ) THEN
        -- selected all the matching values
        -- l_next_num is an available value
        RETURN l_next_num;
    ELSE
        -- some matching values were not selected.  
        -- Check if l_next_num is already taken
        SELECT count(job_name)
        INTO   l_num
        FROM   MGMT_JOB
        WHERE  job_name = l_job_prefix || l_next_num;
        IF ( l_num = 0 ) THEN
            -- this value is not used, return it
            RETURN l_next_num;
        END IF;
    END IF;

    -- couldn't find an available number
    RETURN -1;

EXCEPTION
    WHEN OTHERS THEN
        --  the job_name would be too long
        RETURN -1;
END;


-- Find next unused job name starting with passed in name
--  If My Job.1, My Job.55 exist, 
--   returns My Job.2
FUNCTION get_create_like_name_open ( p_job_name VARCHAR2  ) RETURN VARCHAR2 IS
   l_next_num        NUMBER := 10000;
   l_unused_job_name MGMT_JOB.job_name%TYPE;
BEGIN
    l_next_num := get_job_num_open ( p_job_name );

    IF ( l_next_num < 0 ) THEN
        RETURN p_job_name;
    END IF;

    l_unused_job_name := p_job_name || CREATE_LIKE_DELIM || l_next_num;

    RETURN l_unused_job_name;

EXCEPTION
    WHEN OTHERS THEN
        --  the job_name is already the max length
        RETURN p_job_name;
END;


FUNCTION get_create_like_name ( p_job_name VARCHAR2  ) RETURN VARCHAR2 IS
BEGIN
    -- if ct complain and WANT gaps filled in, use get_create_like_open
    RETURN get_create_like_name_max ( p_job_name );
END;


-------------------------------------------------------------------

-- Convert string array to delim separated string
FUNCTION concat_string_array ( p_arr   SMP_EMD_STRING_ARRAY,
                               p_delim VARCHAR2  ) RETURN VARCHAR2 IS

l_concat VARCHAR2(32000) := '';

BEGIN
    IF ( p_arr.count >= 1 ) THEN
        l_concat := p_arr(1);
    END IF;

    FOR j IN 2..p_arr.count LOOP
        l_concat := l_concat || p_delim || p_arr(j);
    END LOOP;

    RETURN l_concat;
END;



PROCEDURE add_to_array (
            p_job_id                    VARCHAR2,
            p_execution_id              VARCHAR2,
            p_job_name                  VARCHAR2,
            p_job_owner                 VARCHAR2,
            p_step_id                   NUMBER,
            p_iterate_param             VARCHAR2,
            p_iterate_param_index       NUMBER,
            p_param_value               VARCHAR2,
            p_target_name_arr   IN OUT  SMP_EMD_STRING_ARRAY ) IS

l_target_name MGMT_TARGETS.TARGET_NAME%TYPE := '';

BEGIN
    IF ( INSTR(p_param_value, '%') <= 0 ) THEN
        -- Note: the value could be hard coded.  Why: test? well known name?
        l_target_name := p_param_value;
    ELSE
        l_target_name := MGMT_JOB_ENGINE.substitute_params(
                p_job_id,
                p_execution_id,
                p_step_id,
                p_iterate_param,
                p_iterate_param_index,
                p_job_name,
                p_job_owner,
                p_param_value );

    END IF;

    p_target_name_arr.extend(1);
    p_target_name_arr(p_target_name_arr.count) := l_target_name;
END;

--
-- Function to determine if current user has view privilege on all targets
-- on which job is submitted.
--
-- INPUT PARAMETERS
--   p_job_id Job id
--
-- RETURN VALUE
--   0 if user does not have view on one or more targets
--   1 if user has view on all targets
FUNCTION has_view_on_job_targets(p_job_id IN RAW)
    RETURN NUMBER IS
l_view_on_targets NUMBER := 1;
l_view_target_count NUMBER;
l_target_count NUMBER;
BEGIN
    SELECT COUNT(t.target_guid)
    INTO   l_view_target_count
    FROM   MGMT_TARGETS t, MGMT_JOB_TARGET jt
    WHERE  jt.target_guid = t.target_guid
    AND    jt.job_id = p_job_id
    AND    jt.execution_id = MGMT_JOB_ENGINE.NO_EXECUTION;
    
    SELECT COUNT(jt.target_guid)
    INTO   l_target_count
    FROM   MGMT_JOB_TARGET jt
    WHERE  jt.job_id = p_job_id
    AND    jt.execution_id = MGMT_JOB_ENGINE.NO_EXECUTION
    AND    jt.target_guid != MGMT_JOB_ENGINE.NO_GUID; --exclude deleted targets
    
    IF l_view_target_count < l_target_count THEN
        l_view_on_targets := 0;
    END IF;
    
    RETURN l_view_on_targets;
END;   
    
---------------------------------------------
------------ public functions ---------------


-- Get the targets for this step, if any
FUNCTION get_step_targets ( p_step_id NUMBER,
                            p_return_display_names BOOLEAN 
                          ) RETURN SMP_EMD_STRING_ARRAY IS

--  target arr info
l_target_name_arr       SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
l_target_disp_name_arr  SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();

BEGIN
    SELECT        target_name,       display_name
    BULK COLLECT 
    INTO          l_target_name_arr, l_target_disp_name_arr
    FROM          MGMT_JOB_STEP_TARGETS st, 
                  MGMT_TARGETS          t
    WHERE         st.target_guid = t.target_guid
      AND         st.step_id     = p_step_id;

    -- convert internal names to display names
    IF p_return_display_names THEN
        l_target_name_arr := l_target_disp_name_arr;
    END IF;

    RETURN l_target_name_arr;
END;


-- Get the targets for this step, if any
FUNCTION get_step_targets_orig ( p_step_id NUMBER,
                            p_return_display_names BOOLEAN 
                          ) RETURN SMP_EMD_STRING_ARRAY IS

--  target arr info
l_target_name_arr       SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();

--  step info for subst call 
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_job_id                MGMT_JOB.job_id%TYPE;
l_exec_id               MGMT_JOB_HISTORY.execution_id%TYPE;
l_step_name             MGMT_JOB.job_name%TYPE;
l_iterate_param         MGMT_JOB_HISTORY.iterate_param%TYPE;
l_iterate_param_index   MGMT_JOB_HISTORY.iterate_param_index%TYPE;

--  display name temp
l_display_name          MGMT_TARGETS.DISPLAY_NAME%TYPE := '';

l_job_name              MGMT_JOB.job_name%TYPE;
l_job_owner             MGMT_JOB.job_owner%TYPE;

BEGIN
    SELECT  job_id,   execution_id, step_name,   iterate_param,   iterate_param_index 
    INTO    l_job_id, l_exec_id, l_step_name, l_iterate_param, l_iterate_param_index 
    FROM    MGMT_JOB_HISTORY 
    WHERE   step_id = p_step_id;

    l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(l_job_id, l_exec_id);

    SELECT job_name, job_owner INTO l_job_name, l_job_owner FROM
       MGMT_JOB WHERE job_id=l_job_id;

    FOR param IN (SELECT param_name, parameter_type, scalar_value, vector_value
                  FROM MGMT_JOB_STEP_PARAMS 
                  WHERE
                    job_type_id=l_job_type_id     AND
                    step_name=l_step_name   AND
                    param_name like PARAM_STEP_TARGET) 
    LOOP
    -- can have more than one e.g., srcTargetName, destTargetName

        IF ( param.parameter_type=MGMT_JOBS.PARAM_TYPE_SCALAR ) THEN
            add_to_array(l_job_id, 
                         l_exec_id,
                         l_job_name,
                         l_job_owner, 
                         p_step_id,
                         l_iterate_param, 
                         l_iterate_param_index,
                         param.scalar_value,
                         l_target_name_arr);
        ELSIF ( param.parameter_type=MGMT_JOBS.PARAM_TYPE_VECTOR ) THEN
            FOR j IN 1..param.vector_value.count LOOP
                add_to_array(l_job_id, 
                             l_exec_id, 
                             l_job_name,
                             l_job_owner,
                             p_step_id,
                             l_iterate_param, 
                             l_iterate_param_index,
                             param.vector_value(j),
                             l_target_name_arr);
            END LOOP;
        END IF;

    END LOOP;

    -- convert internal names to display names
    IF p_return_display_names THEN

        FOR k IN 1..l_target_name_arr.count LOOP
            
            SELECT  target_name 
            INTO    l_display_name 
            FROM    MGMT_TARGETS 
            WHERE   target_name = l_target_name_arr(k);

            --  overwrite internal name with display name
            l_target_name_arr(k) := l_display_name;

        END LOOP;

    END IF;

    -- We're done, so reset parameters for this session
    MGMT_JOB_ENGINE.reset_params();

    RETURN l_target_name_arr;
END;

-- Get the targets for this step, if any
FUNCTION get_step_targets_str ( p_step_id NUMBER,
                                p_return_display_names BOOLEAN 
                              ) RETURN VARCHAR2 IS
BEGIN
    RETURN concat_string_array ( 
                get_step_targets ( p_step_id, p_return_display_names ), 
                ', ' );
END;


-- Get the parameters for this job, filter as specified for this jobtype
PROCEDURE get_visible_params ( p_job_id         RAW,
                               p_exec_id        RAW,
                               p_params_out OUT CURSOR_TYPE) IS

l_job_type     MGMT_JOB.job_type%TYPE;
l_show_params  NUMBER;
l_specified    NUMBER;

l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
BEGIN
    l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(p_job_id, p_exec_id);

    BEGIN
        SELECT  show_param
        INTO    l_show_params
        FROM    MGMT_JOB_TYPE_DISPLAY_INFO
        WHERE   job_type_id = l_job_type_id;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            -- default to true
            l_show_params := 1;
    END;

    IF EMDW_LOG.p_is_debug_set THEN
        EMDW_LOG.debug('l_show_params=' || l_show_params,MGMT_JOB_ENGINE.MODULE_NAME);
    END IF;

    -- is there at least one param_name specified?
    SELECT  count(param_name)
    INTO    l_specified
    FROM    MGMT_JOB_TYPE_DISPLAY_PARAM
    WHERE   job_type_id = l_job_type_id
        AND rownum=1;


    IF l_specified = 0 THEN

        IF l_show_params = 0 THEN

            --  hide all
            OPEN     p_params_out 
            FOR
            SELECT   'p1' parameter_name, 'pt' parameter_type, 0 encrypted, 
                     1 parameter_type, 'v1' scalar_value, NULL vector_value
                     , NULL large_value
            FROM     dual
            WHERE    rownum < 1;

        ELSE

            --  show all
            OPEN     p_params_out 
            FOR
            SELECT   parameter_name, encrypted, 
                     parameter_type, 
                     decode(encrypted, 0, scalar_value, '******') scalar_value,
                     vector_value, large_value
            FROM     MGMT_JOB_PARAMETER
            WHERE    execution_id  = p_exec_id AND
                     job_id        = p_job_id 
            ORDER BY parameter_name;

        END IF;

    ELSE
        --  MGMT_JOB_TYPE_DISPLAY_PARAM.param_name has entries

        IF l_show_params = 0 THEN

            --  hide specified params
            OPEN     p_params_out 
            FOR
            SELECT   parameter_name, encrypted, 
                     parameter_type, 
                     decode(encrypted, 0, scalar_value, '******') scalar_value,
                     vector_value, large_value
            FROM     MGMT_JOB_PARAMETER
            WHERE    execution_id  = p_exec_id AND
                     job_id        = p_job_id  AND
                     parameter_name NOT IN 
                         ( SELECT dp.param_name 
                           FROM   MGMT_JOB_TYPE_DISPLAY_PARAM dp
                           WHERE  dp.job_type_id = l_job_type_id )
            ORDER BY parameter_name;

        ELSE

            --  show specified params
            OPEN     p_params_out 
            FOR
            SELECT   jp.parameter_name, jp.encrypted, 
                     jp.parameter_type, 
                     decode(jp.encrypted, 0, jp.scalar_value, '******') scalar_value,
                     jp.vector_value, jp.large_value
            FROM     MGMT_JOB_PARAMETER jp, MGMT_JOB_TYPE_DISPLAY_PARAM dp
            WHERE    jp.execution_id   = p_exec_id AND
                     jp.job_id         = p_job_id  AND
                     jp.parameter_name = dp.param_name AND
                     dp.job_type_id = l_job_type_id
            ORDER BY dp.param_order;

        END IF;

    END IF;

END;


-- Get the URI for this uri_use
-- see emSDK/job/dtd/UriSource.java for uri_use constants
FUNCTION  get_display_uri     ( p_job_type IN VARCHAR2,
                                p_uri_use  IN NUMBER
                              ) RETURN VARCHAR2 IS
l_uri  MGMT_JOB_TYPE_URI_INFO.uri%TYPE;

l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_major_version MGMT_JOB_TYPE_INFO.major_version%TYPE;
BEGIN
    MGMT_JOB_ENGINE.get_max_versions(p_job_type, l_major_version, 
                                     l_job_type_id);

    BEGIN
        SELECT  uri
        INTO    l_uri
        FROM    MGMT_JOB_TYPE_URI_INFO
        WHERE   job_type_id = l_job_type_id
            AND uri_use  = p_uri_use;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN 
                NULL;
    END;

    RETURN l_uri;
END;

------------------------------------------------
-- Job Status Intg -> Target Home Pages Calls --
------------------------------------------------
PROCEDURE get_status_rollup_counts( p_target_name IN VARCHAR2, 
                                    p_target_type IN VARCHAR2,
                                    p_for_composite_members IN NUMBER DEFAULT 0,
                                    p_ignore_access IN NUMBER DEFAULT 1,
                                    p_timeframe     IN NUMBER DEFAULT 7,
                                    p_status_cursor OUT CURSOR_TYPE )
IS 
   l_is_group MGMT_TARGETS.IS_GROUP%TYPE := 0;
   l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
   l_tmp_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
BEGIN
   -- Not composite members
   IF ( p_for_composite_members = 0 ) THEN      
      -- -------------------------------------------------------- --
      -- Single Target Home Page or Group Home Page Group Column  --
      -- Show all jobs regardless of access for the job as long   --
      -- as user can VIEW the target                              --
      -- -------------------------------------------------------- --
      -- Target name present: single target or composite target
      IF ( p_target_name IS NOT NULL AND LENGTH( p_target_name ) > 0 ) THEN
         
         -- First look at if this is a group target
         SELECT target_guid, is_group INTO l_target_guid, l_is_group
           FROM mgmt_targets 
          WHERE target_name = p_target_name
            AND target_type = p_target_type;

         -- !!!  Enter super user mode to leave VPD layer !!! --
         IF ( p_ignore_access = 1 ) THEN
            SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                             MGMT_USER.OP_SET_IDENTIFIER);
         END IF ;

         IF ( l_is_group = 0 ) THEN
            -- -------------------------------------------------- --
            -- Non-Group single target INCLUDING RAC and Clusters --
            -- -------------------------------------------------- --
            OPEN p_status_cursor FOR 
              SELECT /*+ INDEX(JobTgt) */
                     COUNT(JobSumm.execution_id) as status_count,
                     JobSumm.status_bucket as status
                FROM MGMT_JOB_EXT_TARGETS JobTgt, 
                     MGMT_JOB_EXEC_SUMMARY JobSumm,
                     MGMT_JOB Job
               WHERE JobSumm.scheduled_time > SYSDATE - p_timeframe
                 AND JobSumm.status_bucket IN
                       ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
                         MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
                         MGMT_JOBS.STATUS_BUCKET_RUNNING,
                         MGMT_JOBS.STATUS_BUCKET_SCHEDULED
                       )
                 AND JobTgt.target_guid  = HEXTORAW( l_target_guid )
                 AND JobTgt.execution_id = JobSumm.execution_id
                 AND JobTgt.job_id       = JobSumm.job_id
                 AND JobSumm.job_id      = Job.job_id
                 AND Job.system_job+0    = 0
                 AND Job.is_corrective_action = 0
                 AND JobSumm.status NOT IN (MGMT_JOB_ENGINE.DELETE_PENDING_STATUS,
                                            MGMT_JOB_ENGINE.WAITING_STATUS)
            GROUP BY JobSumm.status_bucket;
            
--                SELECT COUNT(JobSumm.execution_id) as status_count, 
--                       JobSumm.status_bucket as status 
--                  FROM MGMT_JOB_EXEC_SUMMARY JobSumm
--                 WHERE JobSumm.scheduled_time > SYSDATE - p_timeframe
--                   AND JobSumm.status_bucket IN 
--                       ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
--                         MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
--                         MGMT_JOBS.STATUS_BUCKET_RUNNING, 
--                         MGMT_JOBS.STATUS_BUCKET_SCHEDULED
--                       )
--                   AND EXISTS 
--                       ( SELECT * 
--                           FROM MGMT_JOB_TARGET JobTgt                          
--                          WHERE JobTgt.target_guid  = HEXTORAW( l_target_guid )   
--                            AND JobTgt.execution_id = JobSumm.execution_id )
--                  GROUP BY JobSumm.status_bucket;
                                         
            -- ------------------------------------------------------ --
            -- End Non-Group single target INCLUDING RAC and Clusters --
            -- ------------------------------------------------------ --
         ELSE
            -- ----------------------------- --
            -- Group Home Page Group Column  --
            -- ----------------------------- --
            
            OPEN p_status_cursor FOR 
              SELECT COUNT(*) as status_count,
                     JobSumm.status_bucket as status
                FROM MGMT_JOB_EXEC_SUMMARY JobSumm
               WHERE JobSumm.scheduled_time > SYSDATE - p_timeframe
                 AND JobSumm.status_bucket IN
                ( MGMT_JOBS.STATUS_BUCKET_PROBLEM, 
                  MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
                  MGMT_JOBS.STATUS_BUCKET_RUNNING, 
                  MGMT_JOBS.STATUS_BUCKET_SCHEDULED
                )
                 AND JobSumm.status NOT IN (MGMT_JOB_ENGINE.DELETE_PENDING_STATUS,
                                            MGMT_JOB_ENGINE.WAITING_STATUS)
                 AND EXISTS
                     ( SELECT *
                         FROM MGMT_JOB_TARGET JobTgt,
                              MGMT_JOB Job
                        WHERE JobTgt.execution_id = HEXTORAW( MGMT_JOB_ENGINE.NO_EXECUTION )
                          AND JobTgt.target_guid  = HEXTORAW( l_target_guid )
                          AND JobTgt.job_id       = JobSumm.job_id
                          AND Job.job_id          = JobSumm.job_id
                          AND Job.system_job+0    = 0
                          AND Job.is_corrective_action = 0)
            GROUP BY JobSumm.status_bucket;
  
--              SELECT COUNT(*) as status_count,
--                     JobSumm.status_bucket as status
--                FROM MGMT_JOB_EXEC_SUMMARY JobSumm       
--               WHERE JobSumm.scheduled_time > SYSDATE - p_timeframe
--                 AND JobSumm.status_bucket IN 
--                       ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
--                         MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
--                         MGMT_JOBS.STATUS_BUCKET_RUNNING, 
--                         MGMT_JOBS.STATUS_BUCKET_SCHEDULED
--                       )
--                 AND EXISTS
--                     ( SELECT *
--                         FROM MGMT_JOB_EXT_TARGETS ExecTargets,
--                              MGMT_JOB_TARGET JobTgt,
--                              MGMT_JOB Job
--                        WHERE JobSumm.job_id         = Job.job_id 
--                          AND Job.is_library         = 0
--                          AND JobTgt.execution_id    = HEXTORAW( MGMT_JOB_ENGINE.NO_EXECUTION )
--                          AND JobTgt.target_guid     = HEXTORAW( l_target_guid )
--                          AND JobTgt.job_id          = Job.job_id
--                          AND ExecTargets.job_id     = JobSumm.job_id 
--                          AND ExecTargets.execution_id = JobSumm.execution_id 
--                      )
--            GROUP BY JobSumm.status_bucket; 
              
            -- --------------------------------- --
            -- END Group Home Page Group Column  --
            -- --------------------------------- --
         
         END IF;         
         
         -- !!!  Leave super user mode !!! --         
         IF ( p_ignore_access = 1 ) THEN
            SETEMUSERCONTEXT(l_tmp_current_user, MGMT_USER.OP_SET_IDENTIFIER);
         END IF ;
         -- -------------------------------------------------------- --
         -- End Single Target Home Page or Group HP Group Column     --
         -- -------------------------------------------------------- --
         
      ELSE 
         
         -- ---------------------------------------------------------- --
         -- Used by Console Home Page: Show jobs on the targets        --
         -- on which user has VIEW privillege.  Use VPD layer          -- 
         -- for both MGMT_JOB and MGMT_TARGETS                         --
         -- ---------------------------------------------------------- --
         -- Target type on Console Home
         IF ( p_target_type IS NOT NULL AND LENGTH( p_target_type ) > 0 ) 
            THEN            
               OPEN p_status_cursor FOR
                 SELECT COUNT(JobSumm.execution_id) as status_count,
                        status_bucket as status
                   FROM MGMT_JOB_EXEC_SUMMARY JobSumm
                  WHERE scheduled_time > SYSDATE - p_timeframe
                    AND ( status_bucket = MGMT_JOBS.STATUS_BUCKET_PROBLEM 
                       or status_bucket = MGMT_JOBS.STATUS_BUCKET_SUSPENDED 
                        )
                    AND status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
                    AND EXISTS
                        ( SELECT *
                            FROM MGMT_JOB_EXT_TARGETS jt, 
                                 MGMT_TARGETS t, 
                                 MGMT_JOB j
                           WHERE j.job_id        = jt.job_id
                             AND j.job_id        = JobSumm.job_id 
                             AND t.target_guid   = jt.target_guid 
                             AND t.target_type   = p_target_type 
                             AND jt.execution_id = JobSumm.execution_id
                             AND j.system_job+0  = 0
                             AND j.is_corrective_action = 0
                        )
                   GROUP BY status_bucket;
         ELSE            
            -- All targets
            -- Exclude target-less jobs
                 
              OPEN p_status_cursor FOR
                 SELECT COUNT(JobSumm.execution_id) as status_count,
                        JobSumm.status_bucket as status
                   FROM MGMT_JOB_EXEC_SUMMARY JobSumm
                  WHERE JobSumm.scheduled_time > SYSDATE - p_timeframe
                    AND ( JobSumm.status_bucket = MGMT_JOBS.STATUS_BUCKET_PROBLEM 
                       or JobSumm.status_bucket = MGMT_JOBS.STATUS_BUCKET_SUSPENDED 
                        )
                    AND JobSumm.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
                    AND EXISTS
                        ( SELECT *
                            FROM MGMT_JOB_EXT_TARGETS jt, 
                                 MGMT_TARGETS t, 
                                 MGMT_JOB j
                           WHERE j.job_id        = jt.job_id
                             AND j.job_id        = JobSumm.job_id 
                             AND t.target_guid   = jt.target_guid 
                             AND jt.execution_id = JobSumm.execution_id
                             AND j.system_job+0  = 0
                             AND j.is_corrective_action = 0
                        )
                   GROUP BY status_bucket;
         END IF;         
                    
         -- ---------------------------------------------------------- --
         -- End of Console Home Page                                   --
         -- ---------------------------------------------------------- --
         
      END IF ;
      
   -- Composite members       
   ELSE
      
      -- Obtain composite guid for the group target
      SELECT target_guid INTO l_target_guid
        FROM mgmt_targets 
       WHERE target_name = p_target_name
         AND target_type = p_target_type;
            
      -- !!!  Enter super user mode to leave VPD layer !!! --
      IF ( p_ignore_access = 1 ) THEN
        SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, 
                         MGMT_USER.OP_SET_IDENTIFIER);
      END IF ;
      
      OPEN p_status_cursor FOR 
              SELECT /*+ INDEX(JobSumm) */
                     COUNT(JobSumm.execution_id) as status_count,
                     JobSumm.status_bucket as status
                FROM MGMT_JOB_EXEC_SUMMARY JobSumm, MGMT_JOB Job,
                     MGMT_FLAT_TARGET_ASSOC f, MGMT_JOB_EXT_TARGETS t
               WHERE f.SOURCE_TARGET_GUID = HEXTORAW( l_target_guid )
                 AND f.is_membership      = 1  
                 AND f.assoc_target_guid  = t.target_guid
                 AND t.execution_id       > HEXTORAW('0000000000000000')--index
                 AND t.execution_id       = JobSumm.execution_id
                 AND t.job_id             = JobSumm.job_id
                 AND Job.job_id           = JobSumm.job_id
                 AND Job.system_job       = 0
                 AND Job.is_corrective_action = 0
                 AND JobSumm.scheduled_time > SYSDATE - p_timeframe
                 AND JobSumm.status_bucket IN 
                       ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
                         MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
                         MGMT_JOBS.STATUS_BUCKET_RUNNING, 
                         MGMT_JOBS.STATUS_BUCKET_SCHEDULED
                       )
                 AND JobSumm.status NOT IN (MGMT_JOB_ENGINE.DELETE_PENDING_STATUS,
                                            MGMT_JOB_ENGINE.WAITING_STATUS)
                GROUP BY JobSumm.status_bucket;
      
      -- !!!  Leave super user mode !!! --         
      IF ( p_ignore_access = 1 ) THEN
        SETEMUSERCONTEXT(l_tmp_current_user, MGMT_USER.OP_SET_IDENTIFIER);
      END IF ;
      
   END IF;   

EXCEPTION
WHEN OTHERS
THEN
  -- Make sure to catch all errors and reset current user
  SETEMUSERCONTEXT(l_tmp_current_user, MGMT_USER.OP_SET_IDENTIFIER);
  RAISE;

   
END get_status_rollup_counts;

PROCEDURE get_aggregate_job_activity( p_target_name_in            IN VARCHAR2, 
                                      p_target_type_in            IN VARCHAR2,
                                      p_ignore_access_in          IN NUMBER DEFAULT 1,
                                      p_timeframe_in              IN NUMBER DEFAULT 7,
                                      p_tgt_status_cursor_out     OUT CURSOR_TYPE, 
                                      p_mbr_tgt_status_cursor_out OUT CURSOR_TYPE,
                                      p_job_types_out             OUT SMP_EMD_STRING_ARRAY)
IS
    l_editable MGMT_JOB_INT_ARRAY;
BEGIN
    
    IF p_target_name_in IS NULL OR
       p_target_type_in IS NULL
    THEN
        RETURN;
    END IF;
        
    -- get job types supported for target types
    SELECT DISTINCT ji.job_type, ji.editable 
            BULK COLLECT INTO p_job_types_out, l_editable
    FROM   MGMT_JOB_SINGLE_TARGET_TYPES stt, MGMT_JOB_TYPE_DISPLAY_INFO di, 
           MGMT_JOB_TYPE_INFO ji, MGMT_JOB_TYPE_MAX_VERSIONS mv
    WHERE  di.job_type_id=stt.job_type_id
    AND    ji.job_type_id=di.job_type_id 
    AND    ji.job_type=mv.job_type
    AND    mv.major_version=(SELECT MAX(major_version) FROM
                             MGMT_JOB_TYPE_MAX_VERSIONS
                             WHERE job_type=ji.job_type)
    AND    di.use_default_create_ui=1 
    AND    stt.single_target_type IN
            ( SELECT DISTINCT member_target_type
              FROM MGMT_FLAT_TARGET_MEMBERSHIPS
              WHERE COMPOSITE_TARGET_NAME = p_target_name_in
                AND COMPOSITE_TARGET_TYPE = p_target_type_in
             UNION
              SELECT p_target_type_in FROM DUAL);
    
    --if atleast one job type is editable, add multitask to the list
    IF l_editable IS NOT NULL AND l_editable.COUNT > 0 
    THEN
        FOR i IN l_editable.FIRST..l_editable.LAST LOOP
            IF l_editable(i) = 1 
            THEN
                p_job_types_out.extend(1);
                p_job_types_out(p_job_types_out.COUNT) := MGMT_JOB_ENGINE.MULTITASK_JOB_TYPE;
                EXIT;
            END IF;
        END LOOP;
    END IF;
    
    --get status cursor for target
    get_status_rollup_counts( p_target_name_in,
                              p_target_type_in,
                              0,
                              p_ignore_access_in,
                              p_timeframe_in,
                              p_tgt_status_cursor_out);
                              
    --get status cursor for members of the target
    get_status_rollup_counts( p_target_name_in,
                              p_target_type_in,
                              1,
                              p_ignore_access_in,
                              p_timeframe_in,
                              p_mbr_tgt_status_cursor_out);
                              
END;

----------------------------------------------------
-- End Job Status Intg -> Target Home Pages Calls --
----------------------------------------------------


------------------------------------------------
-- Preferred Credential Validation Calls      --
------------------------------------------------
-- Get a list of targets that do NOT 
-- have preferred credential set
-- For 4.1 this only handles:
-- A single group/composite target
-- A homogenous type of basic targets
PROCEDURE get_targets_prefcreds_not_set
  ( p_target_list   IN OUT MGMT_JOB_TARGET_LIST,
    p_target_type   IN VARCHAR2 DEFAULT NULL,
    p_cred_set_name IN VARCHAR2 )
IS 
   l_em_user     VARCHAR2(256) := MGMT_USER.get_current_em_user();
   l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE;
   l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
   l_target_list MGMT_JOB_TARGET_LIST;   -- Needed to store targets have creds
   l_cred_guid   MGMT_ENTERPRISE_CREDENTIALS.CREDENTIAL_GUID%TYPE;
BEGIN
   -- Call flattening function 
   -- Filter on p_target_type
   get_flattened_aggr_targets( p_target_list, p_target_type );
      
   IF EMDW_LOG.p_is_debug_set THEN
       EMDW_LOG.debug( ' Flattened target list: ' || p_target_list.COUNT,MGMT_JOB_ENGINE.MODULE_NAME);
   END IF;
   
   IF ( p_target_list IS NOT NULL AND p_target_list.COUNT > 0 ) THEN 
      
      -- By this time it would be guaranteed that we
      -- have a list of oracle_database,host,or cluster targets
      -- Look at enterprise level first
      -- Only deal with a type here
      l_target_type := p_target_list(1).target_type;
        
      BEGIN
         SELECT credential_guid INTO l_cred_guid
           FROM MGMT_ENTERPRISE_CREDENTIALS
           WHERE target_type = l_target_type 
           AND   credential_set_name = p_cred_set_name 
           AND   user_name = l_em_user;
         EXCEPTION WHEN no_data_found THEN NULL;
      END;
      
      IF ( l_cred_guid IS NOT NULL ) THEN 
         IF EMDW_LOG.p_is_debug_set THEN
             EMDW_LOG.debug(' Enterprise creds found ',MGMT_JOB_ENGINE.MODULE_NAME);
         END IF;
         p_target_list := NULL;
      ELSE
         IF EMDW_LOG.p_is_debug_set THEN
             EMDW_LOG.debug( ' No enterprise creds found ',MGMT_JOB_ENGINE.MODULE_NAME);
         END IF;
         
         -- Not on enterprise level
         -- Check if target if on target-level (if contains cluster on cluster level)
         -- For 4.1 we enforce cluster level credentials for jobs
         -- Obtain all targets that have pref cred set
         BEGIN
            SELECT MGMT_JOB_TARGET_RECORD( t.target_name, t.target_type )
              BULK COLLECT INTO l_target_list
              FROM MGMT_TARGETS t, MGMT_TARGET_CREDENTIALS c 
              WHERE c.target_guid = t.target_guid  
              AND   c.credential_set_name = p_cred_set_name
              AND   c.user_name = l_em_user;
         EXCEPTION WHEN NO_DATA_FOUND THEN 
           l_target_list := NULL;
           NULL;
         END;

         IF l_target_list IS NOT NULL AND l_target_list.count > 0 THEN            
            -- Filter out any good targets from p_target_list
            FOR i IN l_target_list.FIRST..l_target_list.LAST LOOP
               
               IF ( p_target_list IS NULL       OR   -- not expected
                    p_target_list.FIRST IS NULL OR 
                    p_target_list.COUNT <= 0               
                  ) THEN
                  EXIT;                              -- already all pruned.
               END IF;
                                 
               FOR j IN p_target_list.FIRST..p_target_list.LAST LOOP
                  IF ( p_target_list.EXISTS(j) AND 
                       l_target_list(i).target_name = p_target_list(j).target_name AND
                       l_target_list(i).target_type = p_target_list(j).target_type ) THEN
                     p_target_list.DELETE(j);
                     EXIT;
                  END IF;
               END LOOP;
            END LOOP; 
         END IF;                           
         
      END IF; -- End validate at Enterprise Level 
   END IF;
      
END get_targets_prefcreds_not_set;

PROCEDURE get_preferred_creds_not_set
  ( p_pref_cred_data IN OUT MGMT_JOB_TASK_CRED_SET_ARRAY,
    p_job_owner_user_name IN VARCHAR2 )
IS 
   l_em_user     VARCHAR2(32) := p_job_owner_user_name;
   l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE;
   l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
   l_target_list MGMT_JOB_TARGET_LIST;
   l_orig_target_list MGMT_JOB_TARGET_LIST;
   l_cluster_target_list MGMT_JOB_TARGET_LIST := MGMT_JOB_TARGET_LIST();
   l_error_exists NUMBER(1) := 0;
   l_set_error_exists NUMBER(1) := 0;
   l_cluster_target_types SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
BEGIN
   IF p_pref_cred_data IS NOT NULL AND  p_pref_cred_data.COUNT > 0 THEN
       FOR task_index IN p_pref_cred_data.FIRST..p_pref_cred_data.LAST LOOP
           IF p_pref_cred_data(task_index).COUNT > 0 THEN
               FOR set_index IN p_pref_cred_data(task_index).FIRST..p_pref_cred_data(task_index).LAST LOOP
                   -- Call flattening function 
                   -- Filter on p_target_type
                   l_target_list := p_pref_cred_data(task_index)(set_index).target_list;
                   -- Call flattening fn to honour target aggrigates
                   get_flattened_aggr_targets(l_target_list, 
                                              p_pref_cred_data(task_index)(set_index).target_type);                                      

                   IF ( l_target_list IS NOT NULL AND l_target_list.COUNT > 0 ) THEN 
                          pref_creds_set_targets(l_target_list,
                                                 p_pref_cred_data(task_index)(set_index),
                                                 l_em_user);
                   
                   END IF;

                   IF l_target_list IS NOT NULL AND l_target_list.COUNT > 0 THEN
                       l_set_error_exists := 1;
                   END IF;

                   -- check for the preferred creds at aggregate target level
                   -- This will have little impact on performance ONLY when the target
                   -- list contains the cluster type targets AND member target validation
                   -- has returned validation error (some checking
                   -- will be redundent because it will has been already done 
                   -- previous call to pref_creds_set_targets method)
                   IF l_set_error_exists = 1 THEN                       
                       -- get all the cluster target types for the given job/task target type
                       BEGIN
                           SELECT target_type
                           BULK COLLECT INTO l_cluster_target_types
                           FROM MGMT_TYPE_PROPERTIES WHERE
                           property_name = MGMT_GLOBAL.G_CLUSTER_MEMBER_TYPE_PROP AND
                           property_value = p_pref_cred_data(task_index)(set_index).target_type; 

                           EXCEPTION WHEN no_data_found THEN NULL;                   
                       END;
                       
                       l_orig_target_list := p_pref_cred_data(task_index)(set_index).target_list;

                       -- initializing the list of all cluster type targets in the original targets list
                       IF l_cluster_target_types IS NOT NULL AND l_cluster_target_types.COUNT > 0 THEN
                           FOR i IN l_orig_target_list.FIRST..l_orig_target_list.LAST LOOP
                               FOR j IN l_cluster_target_types.FIRST..l_cluster_target_types.LAST LOOP
                                   IF l_orig_target_list(i).target_type = l_cluster_target_types(j) THEN
                                       l_cluster_target_list.extend(1);
                                       l_cluster_target_list(l_cluster_target_list.COUNT) := l_orig_target_list(i);
                                       EXIT;
                                   END IF;
                               END LOOP;
                           END LOOP;
                       END IF;

                       IF l_cluster_target_list.COUNT > 0 THEN
                           -- if control comes here means cluster target valudation must be done
                           l_target_list := p_pref_cred_data(task_index)(set_index).target_list;

                           -- Call flattening fn to honour target aggrigates except cluster targets
                           get_flattened_aggr_targets(l_target_list, 
                                                  p_pref_cred_data(task_index)(set_index).target_type,
                                                  l_cluster_target_list);

                           IF ( l_target_list IS NOT NULL AND l_target_list.COUNT > 0 ) THEN 
                              pref_creds_set_targets(l_target_list,
                                                     p_pref_cred_data(task_index)(set_index),
                                                     l_em_user);                   
                           END IF;

                           IF l_target_list IS NOT NULL AND l_target_list.COUNT > 0 THEN
                               l_set_error_exists := 1; 
                           ELSE
                               l_set_error_exists := 0;
                           END IF;
                       END IF;
                   END IF;

                   -- The preferred credentials for the "cred set" is considered to be set
                   -- EITHER when 1. ALL member targets for the cluster target's preferred
                   -- credentials are set. 2. When cluster target's preferred credentials are set
                   IF l_set_error_exists = 1 THEN
                       l_error_exists := 1;                       
                   END IF;
                   
                   l_set_error_exists := 0;
                   p_pref_cred_data(task_index)(set_index).target_list := l_target_list;
               END LOOP;
           END IF;
       END LOOP;  
       
    END IF;
    IF l_error_exists = 0 THEN
          p_pref_cred_data := NULL;
    END IF;
END get_preferred_creds_not_set;

--
PROCEDURE pref_creds_set_targets
  ( p_target_list IN OUT MGMT_JOB_TARGET_LIST,
    p_pref_cred_set_data IN MGMT_JOB_TGT_CRED_SET_RECORD,
    p_em_user IN VARCHAR2)
IS 
   l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE;
   l_cred_guid   MGMT_ENTERPRISE_CREDENTIALS.CREDENTIAL_GUID%TYPE;
   l_temp_target_list MGMT_JOB_TARGET_LIST;
BEGIN  
  -- By this time it would be guaranteed that we
  -- have a list of oracle_database,host,or cluster targets
  -- Look at enterprise level first
  -- Only deal with a type here
  l_target_type := p_target_list(1).target_type;
  -- Fix 3750693: Cred GUID should be reset to null here
  -- l_cred_guid := NULL;
    
  BEGIN
     SELECT credential_guid INTO l_cred_guid
       FROM MGMT_ENTERPRISE_CREDENTIALS
       WHERE target_type = l_target_type 
       AND   credential_set_name = p_pref_cred_set_data.cred_set_name 
       AND   user_name = p_em_user;
     EXCEPTION WHEN no_data_found THEN NULL;
  END;
  
  IF ( l_cred_guid IS NOT NULL ) THEN 
     IF EMDW_LOG.p_is_debug_set THEN
         EMDW_LOG.debug( ' Enterprise creds found ',MGMT_JOB_ENGINE.MODULE_NAME);
     END IF;
     p_target_list := NULL;
  ELSE
     IF EMDW_LOG.p_is_debug_set THEN
         EMDW_LOG.debug( ' No enterprise creds found ',MGMT_JOB_ENGINE.MODULE_NAME);
     END IF;
     
     -- Not on enterprise level
     -- Check if target if on target-level (if contains cluster on cluster level)
     -- For 4.1 we enforce cluster level credentials for jobs
     -- Obtain all targets that have pref cred set
     BEGIN
        SELECT MGMT_JOB_TARGET_RECORD( t.target_name, t.target_type )
          BULK COLLECT INTO l_temp_target_list
          FROM MGMT_TARGETS t, MGMT_TARGET_CREDENTIALS c 
          WHERE c.target_guid = t.target_guid  
          AND   c.credential_set_name = p_pref_cred_set_data.cred_set_name
          AND   c.user_name = p_em_user;
     EXCEPTION WHEN NO_DATA_FOUND THEN 
       l_temp_target_list := NULL;
       NULL;
     END;

     IF l_temp_target_list IS NOT NULL AND l_temp_target_list.count > 0 THEN            
        -- Filter out any good targets from p_target_list
        FOR i IN l_temp_target_list.FIRST..l_temp_target_list.LAST LOOP
           
           IF ( p_target_list IS NULL       OR   -- not expected
                p_target_list.FIRST IS NULL OR 
                p_target_list.COUNT <= 0               
              ) THEN
              EXIT;                              -- already all pruned.
           END IF;
                             
           FOR j IN p_target_list.FIRST..p_target_list.LAST LOOP
              IF ( p_target_list.EXISTS(j) AND 
                   l_temp_target_list(i).target_name = p_target_list(j).target_name AND
                   l_temp_target_list(i).target_type = p_target_list(j).target_type ) THEN
                 p_target_list.DELETE(j);
                 EXIT;
              END IF;
           END LOOP;
        END LOOP; 
     END IF;                           
  END IF; -- End validate at Enterprise Level 
END pref_creds_set_targets;
--

-- Private helper that 'flattens' a aggregate 
-- into a member list.
PROCEDURE get_flattened_aggr_targets
  ( p_target_list IN OUT MGMT_JOB_TARGET_LIST,
    p_target_type IN VARCHAR2,
    p_skip_target_list IN MGMT_JOB_TARGET_LIST DEFAULT NULL)
IS 
   l_target_list MGMT_JOB_TARGET_LIST;
   l_all_target_list MGMT_JOB_TARGET_LIST;
   l_set NUMBER := 0;
   l_duplicate NUMBER := 0;
   l_skip NUMBER := 0;
   l_tgt_name_type_map MGMT_TGT_NAME_TYPE_MAP;
BEGIN
   IF p_target_list IS NULL THEN
      RETURN;
   END IF;   
   l_all_target_list := MGMT_JOB_TARGET_LIST();
   
   FOR i_tgt_list IN p_target_list.FIRST..p_target_list.LAST LOOP
       l_tgt_name_type_map(p_target_list(i_tgt_list).target_name 
                           || ':' || p_target_list(i_tgt_list).target_type) := 1;
   END LOOP;

   FOR i IN p_target_list.FIRST..p_target_list.LAST LOOP
     l_target_list := NULL;     
     -- check if this target needs to be skipped
     IF p_skip_target_list IS NOT NULL AND p_skip_target_list.COUNT > 0 THEN
         FOR s IN p_skip_target_list.FIRST..p_skip_target_list.LAST LOOP
             IF p_skip_target_list(s).target_name = p_target_list(i).target_name AND 
                p_skip_target_list(s).target_type = p_target_list(i).target_type THEN
                 l_skip := 1;
                 EXIT;
             END IF;
         END LOOP;
     END IF;

     IF p_target_type <> p_target_list(i).target_type AND l_skip <> 1 THEN
       BEGIN
         SELECT MGMT_JOB_TARGET_RECORD(member_target_name, member_target_type)
         BULK COLLECT INTO l_target_list
         FROM MGMT_FLAT_TARGET_MEMBERSHIPS
         WHERE MEMBER_TARGET_TYPE = p_target_type
         AND COMPOSITE_TARGET_NAME = p_target_list(i).target_name;             
       
         FOR j IN l_target_list.FIRST..l_target_list.LAST LOOP
           -- check first if flattened members does not already exist
           -- in the original list
           BEGIN
               l_duplicate := l_tgt_name_type_map(l_target_list(j).target_name || 
                                              ':' || l_target_list(j).target_type);
               EXCEPTION WHEN NO_DATA_FOUND THEN
                   l_duplicate := 0;
           END;

           IF l_duplicate = 0 THEN
               l_all_target_list.extend(1);
               l_all_target_list(l_all_target_list.COUNT) := l_target_list(j);
           END IF;
           l_set := 1;
           l_duplicate := 0;
         END LOOP;
       EXCEPTION WHEN NO_DATA_FOUND THEN NULL;       
       END;     
     END IF;       
     
     IF l_set = 0 THEN
       l_all_target_list.extend(1);
       l_all_target_list(l_all_target_list.COUNT) := p_target_list(i);
     END IF;
     l_set := 0;
     l_skip := 0;
   END LOOP;
   p_target_list := l_all_target_list;
END get_flattened_aggr_targets;

----------------------------------------------------
-- End Preferred Credential Validation Calls      --
----------------------------------------------------

--------------------------------------------------------------
-- Library Calls                                            --
-- p_job_type:   job type filter if null show all jobs      --
-- p_job_name:   job name filter if null show all jobs      --
-- p_job_owner:  job owner filter if null show all jobs     --
--   This call would get a cursor of library jobs based on
--   search filters.
--------------------------------------------------------------
PROCEDURE get_library_data( p_job_type       IN VARCHAR2,
                            p_job_name       IN VARCHAR2,
                            p_job_owner      IN VARCHAR2,
                            p_library_cursor OUT CURSOR_TYPE )
IS 
   l_em_user             VARCHAR2(256)   := MGMT_USER.get_current_em_user();   

   l_category_a MGMT_JOB_TYPE_INFO.job_type_category%TYPE;
   l_category_b MGMT_JOB_TYPE_INFO.job_type_category%TYPE;
   l_job_type_in MGMT_JOB_TYPE_INFO.job_type%TYPE := p_job_type;

BEGIN

   --compute allowable categories
    compute_jobtype_categories(p_job_type, l_category_a, l_category_b);
   
   --if job type is multitask, set it to wildcard because query will be driven
   --by category
   IF l_job_type_in = MGMT_JOB_ENGINE.MULTITASK_JOB_TYPE
   THEN
        l_job_type_in := '%';
   END IF;

   OPEN p_library_cursor 
   FOR
        --we use inner query so that we honor the VPD layer, as well as return
        --jobs that do not have any targets using a single query
        SELECT Job.job_id as job_id,
               MAX(Job.job_type) as job_type,
               MAX(Job.job_name) as job_name, 
               MAX(Job.job_owner) as job_owner, 
               DECODE(COUNT(*),1,MAX(JobTargets.target_name),count(*)) as target_count,
               MAX(JobTargets.target_type) as target_type ,
               MAX(jobtype_info.job_type_category) as job_type_category
        FROM   MGMT_JOB Job,
               (
                SELECT innerJob.job_id, tgt.target_name, tgt.target_type
                FROM MGMT_JOB innerJob, MGMT_JOB_TARGET jobTgt, MGMT_TARGETS tgt
                WHERE innerJob.job_id = jobTgt.job_id
                  AND jobTgt.target_guid = tgt.target_guid
                ) JobTargets,
               MGMT_JOB_TYPE_INFO jobtype_info,
               MGMT_JOB_TYPE_MAX_VERSIONS max_versions
        WHERE Job.is_library+0   = 1 
          AND Job.job_id         = JobTargets.job_id (+)
          AND Job.job_type       = max_versions.job_type
          AND Job.job_type_major_version    = max_versions.major_version
          AND max_versions.job_type_id      = jobtype_info.job_type_id
          AND jobtype_info.job_type_category in (l_category_a, l_category_b)
          --
          AND Job.job_name like p_job_name
          AND Job.job_type like l_job_type_in
          AND Job.job_owner like p_job_owner
        GROUP BY
            Job.job_id;
           
END get_library_data;

----------------------------
-- End Library Calls      --
----------------------------

--------------------------------------------------------------
-- Corrective Action Library Calls                          --
-- p_job_name:   job name filter; if null show all jobs     --
-- p_job_type:   job type filter; if null show all jobs     --
-- p_job_owner:  target type filter; if null show all jobs  --
--   This call would get a cursor of library corrective     --
--   actions owned by the current user.                     --
--------------------------------------------------------------
PROCEDURE get_ca_library_data( p_job_name          IN VARCHAR2,
                               p_job_type          IN VARCHAR2,
                               p_job_owner         IN VARCHAR2,
                               p_target_type       IN VARCHAR2,
                               p_library_cursor    OUT CURSOR_TYPE )
IS 
   
   l_category_a MGMT_JOB_TYPE_INFO.job_type_category%TYPE;
   l_category_b MGMT_JOB_TYPE_INFO.job_type_category%TYPE;
   l_job_type_in MGMT_JOB_TYPE_INFO.job_type%TYPE := p_job_type;

BEGIN

   --compute allowable categories
    compute_jobtype_categories(p_job_type, l_category_a, l_category_b);
   
   --if job type is multitask, set it to wildcard because query will be driven
   --by category
   IF l_job_type_in = MGMT_JOB_ENGINE.MULTITASK_JOB_TYPE
   THEN
        l_job_type_in := '%';
   END IF;

   IF p_target_type = '%' THEN
       --return rows that may or may not have target type
       OPEN p_library_cursor 
       FOR
           SELECT Job.job_type as job_type,
                   Job.job_name as job_name, 
                   Job.job_owner as job_owner, 
                   Job.job_description as job_description,
                   ttype.target_type as target_type, 
                   Job.job_id as job_id,
                   jobtype_info.job_type_category as job_type_category
            FROM MGMT_JOB Job, 
                 MGMT_CORRECTIVE_ACTION CA, 
                 MGMT_TARGET_TYPES ttype,
                 MGMT_JOB_TYPE_INFO jobtype_info,
                 MGMT_JOB_TYPE_MAX_VERSIONS max_versions
            WHERE Job.job_id                 = CA.job_id
            AND   Job.job_type            like l_job_type_in
            AND   Job.job_name            like p_job_name
            AND   Job.job_type               = max_versions.job_type
            AND   Job.job_type_major_version = max_versions.major_version
            AND   max_versions.job_type_id   = jobtype_info.job_type_id
            and   jobtype_info.job_type_category in (l_category_a, l_category_b)
            --outer join - to include rows that have no job target type
            AND   Job.target_type            = ttype.target_type (+)
            AND   Job.job_owner              like p_job_owner
            AND   CA.ca_scope                =  MGMT_CA.CA_SCOPE_USER; 
   ELSE
       --return rows for a specific target type filter
       OPEN p_library_cursor 
       FOR
           SELECT Job.job_type as job_type,
                   Job.job_name as job_name, 
                   Job.job_owner as job_owner, 
                   Job.job_description as job_description,
                   ttype.type_display_name as target_type, 
                   Job.job_id as job_id,
                   jobtype_info.job_type_category as job_type_category
            FROM MGMT_JOB Job, 
                 MGMT_CORRECTIVE_ACTION CA, 
                 MGMT_TARGET_TYPES ttype,
                 MGMT_JOB_TYPE_INFO jobtype_info,
                 MGMT_JOB_TYPE_MAX_VERSIONS max_versions
            WHERE Job.job_id                 = CA.job_id
            AND   Job.job_type            like l_job_type_in
            AND   Job.job_name            like p_job_name
            --
            AND   Job.target_type like p_target_type
            --
            AND   Job.job_type               = max_versions.job_type
            AND   Job.job_type_major_version = max_versions.major_version
            AND   max_versions.job_type_id   = jobtype_info.job_type_id
            and   jobtype_info.job_type_category in (l_category_a, l_category_b)
            AND   Job.target_type            = ttype.target_type
            AND   Job.job_owner              like p_job_owner
            AND   CA.ca_scope                =  MGMT_CA.CA_SCOPE_USER;
    END IF;

END get_ca_library_data;

--------------------------------------------------------------
-- Corrective Action Select List                            --
-- p_job_name:   job name filter; if % show all jobs     --
-- p_job_owner:  target type; never wild card                    --
-- p_ascending:  order by ASC or DESC; default ASC          --
-- p_order_by:   column name order by is operated on        --
--   This call would get a cursor of library corrective     --
--   actions owned by the current user.                     --
--------------------------------------------------------------
PROCEDURE get_ca_select_from_lib_data( p_job_name          IN VARCHAR2,
                                       p_target_type       IN VARCHAR2,
                                       p_library_cursor    OUT CURSOR_TYPE )
IS 
BEGIN

   OPEN p_library_cursor 
   FOR
        SELECT Job.job_name as job_name,
               Job.job_description as job_description,
               Job.job_id as job_id,
               Job.job_type as job_type,
               Job.job_owner as job_owner,
               JTI.job_type_category as job_type_category
        FROM   MGMT_JOB Job,
               MGMT_CORRECTIVE_ACTION CA,
               MGMT_JOB_TYPE_INFO JTI,
               MGMT_JOB_TYPE_MAX_VERSIONS mv
        WHERE  Job.job_id         = CA.job_id
        AND    Job.job_name       like p_job_name
        AND    (
                Job.target_type    like p_target_type
                OR
                Job.target_type IS NULL
               ) 
        AND    Job.job_type       = mv.job_type
        AND    Job.job_type_major_version = mv.major_version
        AND    JTI.job_type_id    = mv.job_type_id
        AND    CA.ca_scope        =  MGMT_CA.CA_SCOPE_USER;
        
END get_ca_select_from_lib_data;

----------------------------
-- End Corrective Action Library Calls      --
----------------------------


--------------------------
-- PL/SQL procedures to seed job UI
-------------------------

PROCEDURE compute_associated_types(p_target_type VARCHAR2,
                                   p_cluster_types_out OUT SMP_EMD_STRING_ARRAY,
                                   p_group_types_out OUT SMP_EMD_STRING_ARRAY) IS
BEGIN
    IF p_target_type IS NOT NULL THEN
        -- Select all cluster types that contain atleast one target of the 
        -- specified type
        SELECT  DISTINCT p.target_type BULK COLLECT INTO p_cluster_types_out
        FROM    MGMT_TYPE_PROPERTIES p, MGMT_FLAT_TARGET_MEMBERSHIPS t
        WHERE   p.property_name=MGMT_GLOBAL.G_IS_CLUSTER_PROP
        AND     p.property_value=1   
        AND     p.target_type=t.composite_target_type
        AND     EXISTS
                (SELECT * 
                 FROM   MGMT_FLAT_TARGET_MEMBERSHIPS tm
                 WHERE  tm.composite_target_type=t.composite_target_type
                 AND    tm.member_target_type=p_target_type);
        
        -- Select all composite types that contain a target of the specified
        -- type (exclude cluster types).
        -- Note that the composites that contain a cluster of one of the 
        -- above types will also get selected even if they don't directly have
        -- members of p_target_type type because even the cluster will
        -- be flattened in MGMT_FLAT_TARGET_MEMBERSHIPS, and the flattened list
        -- would contain atleast one target of type p_target_type
        SELECT  DISTINCT ft.composite_target_type 
        BULK COLLECT INTO p_group_types_out
        FROM    MGMT_FLAT_TARGET_MEMBERSHIPS ft
        WHERE   ft.member_target_type=p_target_type
        AND     NOT EXISTS
                    (SELECT p.target_type 
                     FROM   MGMT_TYPE_PROPERTIES p
                     WHERE  p.target_type=ft.composite_target_type
                     AND    p.property_name=MGMT_GLOBAL.G_IS_CLUSTER_PROP
                     AND    p.property_value=1);

    END IF;

END;

PROCEDURE get_job_sch_email_notif(p_job_id VARCHAR2, p_jobcreate_notif_out OUT MGMT_JOB_NOTIF_EMAIL_SCH_INFO) is 
l_current_user MGMT_CREATED_USERS.user_name%TYPE := MGMT_USER.get_current_em_user;
l_schedule_count number;
l_email_count number;
l_job_notify_status MGMT_INTEGER_ARRAY ;
BEGIN
      SELECT count(*) into l_email_count 
      FROM mgmt_notify_devices
      WHERE profile_name = l_current_user
      AND type IN (EMD_NOTIFICATION.SHORT_EMAIL_DEVICE_TYPE,
                              EMD_NOTIFICATION.LONG_EMAIL_DEVICE_TYPE)
       AND device_name = email_address;
        
      SELECT count(*) into l_schedule_count 
      FROM  MGMT_NOTIFY_SCHEDULES 
      WHERE Schedule_owner = l_current_user 
      AND schedule_name = ' ' ;

    --SCHEDULE /EMAIL NOTIFICATION
    IF p_job_id IS NOT NULL THEN
          SELECT NOTIFY_STATE bulk collect into l_job_notify_status 
          FROM mgmt_job_notify_states
          WHERE job_id = p_job_id;
    END IF;

    p_jobcreate_notif_out := MGMT_JOB_NOTIF_EMAIL_SCH_INFO(l_email_count,l_schedule_count,l_job_notify_status);

END;


PROCEDURE get_job_info(p_job_type IN OUT VARCHAR2,
                       p_mode NUMBER,
                       p_job_id IN OUT VARCHAR2,
                       p_exec_id VARCHAR2,
                       p_initial_target_type VARCHAR2,
                       p_initial_target VARCHAR2,
                       p_initial_member_target_type VARCHAR2,
                       p_job_name_out OUT VARCHAR2,
                       p_job_owner_out OUT VARCHAR2,
                       p_description_out OUT VARCHAR2,
                       p_initial_targets_out OUT TARGETS_CURSOR,
                       p_targets_out OUT TARGETS_CURSOR,
                       p_params_out OUT MGMT_JOB_PARAM_LIST,
                       p_creds_out OUT CREDS_CURSOR,
                       p_schedule_out OUT MGMT_JOB_SCHEDULE_RECORD,
                       p_access_list_out OUT GRANT_CURSOR,
                       p_editable_by_curr_user_out OUT NUMBER,
                       p_expired_out OUT NUMBER,
                       p_param_metadata_out OUT MGMT_JOB_PARAMETER_METADATA,
                       p_creds_metadata_out OUT MGMT_JOB_CREDENTIAL_RECORD,
                       p_ttype_metadata_out OUT MGMT_TARGET_TYPE_PROP_ARRAY,
                       p_superuser_list_out OUT SMP_EMD_STRING_ARRAY,
                       p_cluster_types_out OUT SMP_EMD_STRING_ARRAY,
                       p_group_types_out OUT SMP_EMD_STRING_ARRAY,
                       p_jobtype_editable_out OUT NUMBER,
                       p_can_submit_to_group_out OUT NUMBER,
                       p_uses_default_ui_out OUT NUMBER,
                       p_create_uri_out OUT VARCHAR2,
                       p_is_jsp_out OUT NUMBER,
                       p_job_target_types_out OUT SMP_EMD_STRING_ARRAY,
                       p_default_target_type_out OUT VARCHAR2,
                       p_create_text_nlsid OUT VARCHAR2,
                       p_create_text_default OUT VARCHAR2,
                       p_create_like_name_out OUT VARCHAR2,
                       p_jobcreate_notif_out OUT MGMT_JOB_NOTIF_EMAIL_SCH_INFO,
                       p_job_status_out OUT NUMBER,
                       p_ctx_type_out OUT NUMBER,
                       p_has_view_on_targets OUT NUMBER
                       ) IS

l_current_user MGMT_CREATED_USERS.user_name%TYPE := MGMT_USER.get_current_em_user;
l_is_corrective_action MGMT_JOB.is_corrective_action%TYPE;

l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_major_version MGMT_JOB_TYPE_INFO.major_version%TYPE;
l_is_library NUMBER;
BEGIN
    --if execution id is specified, query job id based on it
    IF p_exec_id IS NOT NULL THEN
        BEGIN
            SELECT job_id INTO p_job_id
            FROM   MGMT_JOB_EXEC_SUMMARY
            WHERE  execution_id=p_exec_id
            AND    status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                raise_application_error(MGMT_GLOBAL.INVALID_EXECUTION_ERR,
                 'The specified execution does not exist');
        END;
    END IF;
    
    IF p_job_id = ' ' THEN
        p_job_id := NULL;
    END IF;
    
    IF p_job_id IS NOT NULL THEN
        BEGIN
            SELECT job_type INTO p_job_type
            FROM   MGMT_JOB
            WHERE  job_id=p_job_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                 'The specified job does not exist ' || p_job_id);
        END;
    
        l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(p_job_id);
    ELSE
        MGMT_JOB_ENGINE.get_max_versions(p_job_type, l_major_version,
                                         l_job_type_id);
    END IF;

    get_job_sch_email_notif(p_job_id ,p_jobcreate_notif_out );

    ------------------------ METADATA ----------------------------------
    BEGIN
    -- Check whether this job type uses the default UI
    
    --get default ui flag
    p_uses_default_ui_out := uses_default_create_ui(p_job_type);
    
    -- Obtain the create URI
    BEGIN
        SELECT uri, is_jsp INTO p_create_uri_out, p_is_jsp_out
        FROM   MGMT_JOB_TYPE_URI_INFO
        WHERE  job_type_id=l_job_type_id
        AND    uri_use=MGMT_JOBS.URI_USE_CREATE;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            p_create_uri_out := NULL ;
            p_is_jsp_out := 0 ;
    END;

    -- In create mode, go no further if the job type does NOT
    -- use the default UI
    IF p_mode=JOB_INFO_MODE_CREATE AND p_uses_default_ui_out=0 THEN
        RETURN;
    END IF;

    -- The job target types - select only types for which targets exist
    SELECT stt.single_target_type BULK COLLECT INTO p_job_target_types_out
    FROM   MGMT_JOB_SINGLE_TARGET_TYPES stt
    WHERE  stt.job_type_id=l_job_type_id
    AND    EXISTS
           (
             SELECT tgt.target_type
             FROM   MGMT_TARGETS tgt
             WHERE  tgt.target_type=stt.single_target_type
           );

    -- The editable flag, default target type, group info, instruction text
    -- Note for now we're returning true for p_can_submit_to_group_out
    -- if the job type is single-target. This may need to be refined
    -- down the line once we add more group-related metadata
    SELECT  editable, default_target_type, single_target, create_text_nlsid, create_text_default
    INTO    p_jobtype_editable_out, p_default_target_type_out,
            p_can_submit_to_group_out, p_create_text_nlsid, p_create_text_default
    FROM    MGMT_JOB_TYPE_INFO
    WHERE   job_type_id=l_job_type_id;

    -- Make sure the default target type is set in cases where the job type
    -- works with just one target type
    IF p_job_id IS NOT NULL THEN
	SELECT target_type INTO p_default_target_type_out
	FROM   MGMT_JOB
	WHERE  job_id=p_job_id;
    ELSIF p_default_target_type_out IS NULL AND
       p_job_target_types_out IS NOT NULL AND
       p_job_target_types_out.COUNT > 0 THEN
        p_default_target_type_out := p_job_target_types_out(1);
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
        raise_application_error(MGMT_GLOBAL.INVALID_JOB_TYPE_ERR,
         'The specified job type does not exist');
    END;


    --if the create ui needs to be initialized with a target,
    --get the necessary data

    -- Use get_initial_job_target in all cases where we don't have
    -- a job_id to get the targets and target_types from.  This includes
    -- CREATEs of all sorts of jobs and CAs, but also EDITs and CREATE_LIKEs
    -- of MS_TARGET and MS_TEMPLATE CAs, where p_initial_target_type and
    -- p_initial_target will have been set from the model object passed
    -- from monitoring settings.
    IF p_job_id IS NULL 
    THEN
        get_initial_job_target(p_initial_target_type,
                               p_initial_target,
                               p_initial_member_target_type,
                               p_job_target_types_out,
                               p_default_target_type_out,
                               p_initial_targets_out);
    ELSE
         -- Some version of jdbc cannot handle null cursors
        OPEN p_initial_targets_out FOR   
        SELECT 1,2,3,4,5,6,7 FROM DUAL WHERE ROWNUM<1;
    END IF;
       
    -- Parameters metadata
    MGMT_JOB_ENGINE.get_job_type_metadata(p_job_type, p_param_metadata_out);

    -- Target type metadata (needed by params page)
    MGMT_JOB_ENGINE.get_target_type_data(p_default_target_type_out,
                                         p_ttype_metadata_out);

    -- Credentials metadata
    MGMT_JOB_ENGINE.get_job_credential_metadata(p_job_type, 
                                                p_default_target_type_out,
                                                p_creds_metadata_out);

    -- Superuser list
    SELECT  grantee BULK COLLECT INTO p_superuser_list_out
    FROM    MGMT_PRIV_GRANTS
    WHERE   PRIV_NAME=MGMT_USER.SUPER_USER;

    -- Fetch associated and group target types for the currently selected one
    compute_associated_types(p_default_target_type_out,
                             p_cluster_types_out,
                             p_group_types_out);
    
    
    ------------------------- END METADATA -------------------------------

    ---------------------- JOB DATA ------------------------------------
    
    IF p_job_id IS NULL THEN
        RETURN;
    END IF;

    -- Job name, owner, description, expired, status
    SELECT  job_name, job_description, expired, is_corrective_action, job_status, is_library
    INTO    p_job_name_out, p_description_out,
            p_expired_out, l_is_corrective_action, p_job_status_out, l_is_library
    FROM    MGMT_JOB
    WHERE   job_id=p_job_id;

    IF p_mode=JOB_INFO_MODE_CREATE_LIKE THEN
        p_create_like_name_out := get_create_like_name ( p_job_name_out );
    END IF;

    -- Job targets
    OPEN p_targets_out FOR
    SELECT  t.target_guid, t.host_name, t.display_name,
            t.target_name, t.target_type, t.timezone_region, t.emd_url
    FROM    MGMT_TARGETS t, MGMT_JOB_TARGET jt 
    WHERE   jt.target_guid = t.target_guid
    AND     jt.job_id = p_job_id
    AND     jt.execution_id = MGMT_JOB_ENGINE.NO_EXECUTION;
    
    --Check if user has view privilege on all submission time targets
    IF p_mode=JOB_INFO_MODE_EDIT 
    THEN
        p_has_view_on_targets := has_view_on_job_targets(p_job_id);
    ELSE
        p_has_view_on_targets := 1;
    END IF;    
    
    -- Job access information
    MGMT_JOBS.get_job_grants(p_job_id, p_job_owner_out, p_access_list_out);

    -- Parameter information
    SELECT  MGMT_JOB_PARAM_RECORD(parameter_name, parameter_type,
                            MGMT_JOB_ENGINE.decrypt_scalar(encrypted, scalar_value),
                            MGMT_JOB_ENGINE.decrypt_vector(encrypted, vector_value))
    BULK COLLECT INTO p_params_out
    FROM    MGMT_JOB_PARAMETER
    WHERE   job_id=p_job_id
    AND     execution_id=MGMT_JOB_ENGINE.NO_EXECUTION;

    -- Credentials information; assumes override is set for
    -- all targets
    OPEN p_creds_out FOR
    SELECT  jc.credential_guid, 
            container_location, credential_set_name, user_name,
            c.credential_set_column, 
            decode(c.credential_value, null, null, 
                   decrypt(credential_value)),pdp_data 
    FROM    MGMT_JOB_CREDENTIALS jc,
            MGMT_CREDENTIALS2 c, MGMT_JOB j 
    WHERE   jc.credential_guid=c.credential_guid
    AND     jc.job_id=j.job_id
    AND     j.job_id=p_job_id
    ORDER BY jc.credential_guid;

    -- Schedule
    IF l_is_corrective_action = 0 THEN
        SELECT  MGMT_JOB_SCHEDULE_RECORD(frequency_code, start_time, end_time, start_grace_period,
                                         execution_hours, execution_minutes,
                                         interval, months, days, timezone_info,
                                         timezone_target_index,
                                         timezone_offset, timezone_region)
        INTO    p_schedule_out
        FROM    MGMT_JOB_SCHEDULE s, MGMT_JOB j 
        WHERE   s.schedule_id=j.schedule_id 
        AND     j.job_id=p_job_id;
    ELSE
        p_schedule_out := null;
    END IF;

    -- Can the current user edit the job?
    p_editable_by_curr_user_out := MGMT_USER.has_priv(l_current_user,
                                                      MGMT_USER.FULL_JOB,
                                                      p_job_id);
    -- Set ctx type
    -- 0 -> Active job
    -- 1 -> Library job
    IF l_is_corrective_action = 0 THEN
        p_ctx_type_out := l_is_library;
    END IF;
    -- TODO: set CA ctx type here

END;
                       

PROCEDURE get_param_creds_info(p_job_type VARCHAR2,
                               p_new_target_type VARCHAR2,
                               p_fetch_param_creds_ttypes NUMBER,
                               p_cluster_types_out OUT SMP_EMD_STRING_ARRAY,
                               p_group_types_out OUT SMP_EMD_STRING_ARRAY,
                               p_param_metadata_out OUT MGMT_JOB_PARAMETER_METADATA,
                               p_creds_metadata_out OUT MGMT_JOB_CREDENTIAL_RECORD,
                               p_ttype_metadata_out OUT MGMT_TARGET_TYPE_PROP_ARRAY) IS
BEGIN
    IF p_fetch_param_creds_ttypes=1 THEN
        -- Parameters metadata
        MGMT_JOB_ENGINE.get_job_type_metadata(p_job_type, p_param_metadata_out);
    
        -- Target type metadata (needed by params page)
        MGMT_JOB_ENGINE.get_target_type_data(p_new_target_type,
                                             p_ttype_metadata_out);
    
        -- Credentials metadata
        MGMT_JOB_ENGINE.get_job_credential_metadata(p_job_type, 
                                                    p_new_target_type,
                                                    p_creds_metadata_out);
    END IF;
    
    -- Fetch associated and group target types for the currently selected one
    compute_associated_types(p_new_target_type,
                             p_cluster_types_out,
                             p_group_types_out);
END;

FUNCTION get_tgt_types_with_host_creds
    RETURN SMP_EMD_STRING_ARRAY IS
l_target_types SMP_EMD_STRING_ARRAY;
BEGIN
    SELECT  DISTINCT s.target_type 
    BULK COLLECT INTO l_target_types
    FROM    MGMT_CREDENTIAL_SETS s, MGMT_CREDENTIAL_TYPE_REF r
    WHERE   s.credential_type_name=r.type_name
    AND     s.target_type=r.target_type
    AND     s.target_type_meta_ver=r.target_type_meta_ver
    AND     r.ref_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE
    AND     r.ref_type_name=EM_CREDENTIAL.HOST_CREDS;

    -- Put the host type at the top of the array
    IF l_target_types IS NULL THEN
        l_target_types := SMP_EMD_STRING_ARRAY();
    END IF;

    l_target_types.extend(1);

    IF l_target_types.COUNT >1 THEN        
        FOR i IN REVERSE 2..l_target_types.COUNT LOOP
            l_target_types(i) := l_target_types(i-1);
        END LOOP;
    END IF;

    l_target_types(1) := MGMT_GLOBAL.G_HOST_TARGET_TYPE;
    
    return l_target_types;
    
END;


FUNCTION get_job_types_for_target_type(p_target_type VARCHAR2)
    RETURN SMP_EMD_STRING_ARRAY IS
l_job_types SMP_EMD_STRING_ARRAY;
BEGIN
    SELECT DISTINCT ji.job_type BULK COLLECT INTO l_job_types
    FROM   MGMT_JOB_SINGLE_TARGET_TYPES stt, MGMT_JOB_TYPE_DISPLAY_INFO di, 
           MGMT_JOB_TYPE_INFO ji, MGMT_JOB_TYPE_MAX_VERSIONS mv
    WHERE  di.job_type_id=stt.job_type_id
    AND    ji.job_type_id=di.job_type_id
    AND    ji.job_type=mv.job_type
    AND    mv.major_version=(SELECT MAX(major_version) FROM
                             MGMT_JOB_TYPE_MAX_VERSIONS
                             WHERE job_type=ji.job_type)
    AND    di.use_default_create_ui=1 
    AND    ji.editable=1 
    AND    single_target_type = p_target_type;
    return l_job_types;
END;
    
PROCEDURE get_multitask_job_info(p_job_id VARCHAR2,
                                 p_mode NUMBER,
                                 p_target_type VARCHAR2,
                                 p_target_list MGMT_JOB_TARGET_LIST,
                                 p_initial_target_type VARCHAR2,
                                 p_initial_target VARCHAR2,
                                 p_initial_member_target_type VARCHAR2,
                                 p_default_target_type_out OUT VARCHAR2,
                                 p_job_target_types_out OUT SMP_EMD_STRING_ARRAY,
                                 p_cluster_types_out OUT SMP_EMD_STRING_ARRAY,
                                 p_group_types_out OUT SMP_EMD_STRING_ARRAY,
                                 p_job_types_all_out OUT SMP_EMD_STRING_ARRAY,
                                 p_job_types_for_tgt_type_out OUT SMP_EMD_STRING_ARRAY,
                                 p_superuser_list_out OUT SMP_EMD_STRING_ARRAY,
                                 p_access_list_out OUT GRANT_CURSOR,
                                 p_editable_by_curr_user_out OUT NUMBER,
                                 p_expired_out OUT NUMBER,
                                 p_initial_targets_out OUT TARGETS_CURSOR,
                                 p_targets_out OUT TARGETS_CURSOR,
                                 p_tasks_metadata IN OUT MGMT_PARAM_CRED_TGT_MET_ARR,
                                 p_create_like_name_out OUT VARCHAR2,
                                 p_jobcreate_notif_out OUT MGMT_JOB_NOTIF_EMAIL_SCH_INFO,
                                 p_job_status_out OUT NUMBER,
                                 p_ctx_type_out OUT NUMBER,
                                 p_has_view_on_targets OUT NUMBER
                                ) IS
l_current_user MGMT_CREATED_USERS.user_name%TYPE := MGMT_USER.get_current_em_user;
l_job_owner VARCHAR2(256) := '';
l_job_name VARCHAR2(256) := '';
l_job_id mgmt_job.job_id%TYPE;
l_is_library NUMBER;
l_is_corrective_action NUMBER;
BEGIN
    --job target types
    SELECT  DISTINCT stt.single_target_type BULK COLLECT INTO p_job_target_types_out 
    FROM    MGMT_JOB_SINGLE_TARGET_TYPES stt, 
            MGMT_JOB_TYPE_DISPLAY_INFO di, 
            MGMT_JOB_TYPE_INFO ji
    WHERE   di.job_type_id=stt.job_type_id
    AND     ji.job_type_id=di.job_type_id
    AND     di.use_default_create_ui=1 
    AND     ji.editable=1
    AND     EXISTS
            (
              SELECT tgt.target_type
              FROM   MGMT_TARGETS tgt
              WHERE  tgt.target_type=stt.single_target_type
            );

    IF p_job_id = ' ' THEN
        l_job_id := NULL;
    ELSE
        l_job_id := p_job_id;
    END IF;
    
    -- get selected target type
    IF l_job_id IS NOT NULL THEN
        BEGIN
            SELECT target_type INTO p_default_target_type_out
            FROM   MGMT_JOB
            WHERE  job_id=l_job_id;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                 'The specified job does not exist');
        END;
    ELSIF p_target_type IS NOT NULL THEN
        p_default_target_type_out := p_target_type;
    END IF;
    
    --default to host type and retrieve data for host type in any case
    IF p_default_target_type_out IS NULL THEN
        p_default_target_type_out := MGMT_GLOBAL.G_HOST_TARGET_TYPE;
    END IF;
    
    get_job_sch_email_notif(l_job_id, p_jobcreate_notif_out );

    --if the create ui needs to be initialized with a target, get the necessary
    --data
    -- Use get_initial_job_target in all cases where we don't have
    -- a job_id to get the targets and target_types from.  This includes
    -- CREATEs of all sorts of jobs and CAs, but also EDITs and CREATE_LIKEs
    -- of MS_TARGET and MS_TEMPLATE CAs, where p_initial_target_type and
    -- p_initial_target will have been set from the model object passed
    -- from monitoring settings.
    IF l_job_id IS NULL 
    THEN
        get_initial_job_target(p_initial_target_type,
                               p_initial_target,
                               p_initial_member_target_type,
                               p_job_target_types_out,
                               p_default_target_type_out,
                               p_initial_targets_out);
    ELSE
        -- Some versions of jdbc cannot handle null cursors
        OPEN p_initial_targets_out FOR   
        SELECT 1,2,3,4,5,6,7 FROM DUAL WHERE ROWNUM<1;
    END IF;
    
    -- Fetch associated and group target types for the currently selected one
    compute_associated_types(p_default_target_type_out,
                             p_cluster_types_out,
                             p_group_types_out);
                             
    --All job types that can be used as tasks
    --Select only job types for which targets are visible to current user
    SELECT DISTINCT ji.job_type BULK COLLECT INTO p_job_types_all_out
    FROM   MGMT_JOB_TYPE_DISPLAY_INFO di, MGMT_JOB_TYPE_INFO ji
    WHERE  di.job_type_id=ji.job_type_id
    AND    di.use_default_create_ui=1
    AND    ji.editable=1
    AND (
          EXISTS(
                 SELECT stt.single_target_type
                 FROM   MGMT_JOB_SINGLE_TARGET_TYPES stt, MGMT_TARGETS t
                 WHERE  stt.job_type_id=ji.job_type_id
                 AND    stt.single_target_type=t.target_type
                ) 
         OR
          NOT EXISTS(
                     SELECT single_target_type
                     FROM   MGMT_JOB_SINGLE_TARGET_TYPES
                     WHERE  job_type_id=ji.job_type_id
                    ) 
        );

    --No job types apply to null default target type
    IF p_default_target_type_out IS NOT NULL THEN
    --All job types that can be used as tasks for selected target type
        p_job_types_for_tgt_type_out := get_job_types_for_target_type(p_default_target_type_out);
    END IF;         
    
    
    IF l_job_id IS NOT NULL THEN
        -- Job access information
        MGMT_JOBS.get_job_grants(l_job_id, l_job_owner, p_access_list_out);
    END IF;        

    -- Superuser list
    SELECT  grantee BULK COLLECT INTO p_superuser_list_out
    FROM    MGMT_PRIV_GRANTS
    WHERE   PRIV_NAME=MGMT_USER.SUPER_USER;    
    
    IF l_job_id IS NOT NULL THEN
        --expired, job_status, is library
        SELECT  expired, job_status, is_library, is_corrective_action, job_name 
        INTO p_expired_out, p_job_status_out, l_is_library, l_is_corrective_action, l_job_name
        FROM    MGMT_JOB
        WHERE   job_id=l_job_id;

        IF p_mode=JOB_INFO_MODE_CREATE_LIKE THEN
            p_create_like_name_out := get_create_like_name ( l_job_name );
        END IF;
        
        -- Can the current user edit the job?
        p_editable_by_curr_user_out := MGMT_USER.has_priv(l_current_user,
                                                          MGMT_USER.FULL_JOB,
                                                          l_job_id);
    END IF;
    
    -- Task targets
    IF p_target_list IS NOT NULL THEN
        OPEN p_targets_out FOR
        SELECT t.target_guid, t.host_name, t.display_name,
               t.target_name, t.target_type, t.timezone_region, t.emd_url
        FROM   MGMT_TARGETS t,
               TABLE(CAST(p_target_list AS MGMT_JOB_TARGET_LIST)) jt
        WHERE  t.target_name=jt.target_name
        AND    t.target_type=jt.target_type;
    
    END IF;

    IF p_tasks_metadata IS NOT NULL THEN
        get_tasks_metadata(p_tasks_metadata);
    END IF;

    --check if user has VIEW privilege on all submission time targets
    IF l_job_id IS NOT NULL AND p_mode=JOB_INFO_MODE_EDIT 
    THEN
        p_has_view_on_targets := has_view_on_job_targets(p_job_id);
    ELSE
        p_has_view_on_targets := 1;
    END IF;
    
    -- Set ctx type
    -- 0 -> Active job
    -- 1 -> Library job
    IF l_is_corrective_action = 0 THEN
        p_ctx_type_out := l_is_library;
    END IF;
    -- TODO: set CA ctx type here
END;


FUNCTION uses_default_create_ui( p_job_type IN VARCHAR2)
    RETURN NUMBER IS
l_use_default_create_ui NUMBER;

l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_major_version MGMT_JOB_TYPE_INFO.major_version%TYPE;
BEGIN
    MGMT_JOB_ENGINE.get_max_versions(p_job_type, l_major_version, l_job_type_id);
    --if no row is found, it means the job type does not have a displayinfo
    --section, and hence does not use default UI
    BEGIN
        SELECT use_default_create_ui INTO l_use_default_create_ui
        FROM MGMT_JOB_TYPE_DISPLAY_INFO
        WHERE job_type_id=l_job_type_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            l_use_default_create_ui := 0;
    END;
    
    return l_use_default_create_ui;
    
END;
    
    
PROCEDURE change_multitask_target_type(p_target_type VARCHAR2,
                                       p_job_types_for_tgt_type_out OUT SMP_EMD_STRING_ARRAY,
                                       p_cluster_types_out OUT SMP_EMD_STRING_ARRAY,
                                       p_group_types_out OUT SMP_EMD_STRING_ARRAY) IS
BEGIN
    p_job_types_for_tgt_type_out := get_job_types_for_target_type(p_target_type);
    
    compute_associated_types(p_target_type,
                             p_cluster_types_out,
                             p_group_types_out);
END;


PROCEDURE get_single_exec_task_info(p_job_type VARCHAR2,
                                    p_target_type IN OUT VARCHAR2,
                                    p_target_list MGMT_JOB_TARGET_LIST,
                                    p_cluster_types_out OUT SMP_EMD_STRING_ARRAY,
                                    p_group_types_out OUT SMP_EMD_STRING_ARRAY,
                                    p_job_target_types_out OUT SMP_EMD_STRING_ARRAY,
                                    p_targets_out OUT TARGETS_CURSOR,
                                    p_tasks_metadata IN OUT MGMT_PARAM_CRED_TGT_MET_ARR) IS
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_major_version MGMT_JOB_TYPE_INFO.major_version%TYPE;
BEGIN
    MGMT_JOB_ENGINE.get_max_versions(p_job_type, l_major_version, l_job_type_id);    
    -- if target type is not available as input parameter, set it to null
    -- and later get it
    
    IF p_target_type = ' ' THEN
        p_target_type := NULL;
    END IF;
    -- The job target types
    SELECT stt.single_target_type BULK COLLECT INTO p_job_target_types_out
    FROM   MGMT_JOB_SINGLE_TARGET_TYPES stt
    WHERE  stt.job_type_id=l_job_type_id
    AND    EXISTS
           (
             SELECT tgt.target_type
             FROM   MGMT_TARGETS tgt
             WHERE  tgt.target_type=stt.single_target_type
           );

    IF p_target_type IS NULL THEN
        SELECT default_target_type INTO p_target_type
        FROM MGMT_JOB_TYPE_INFO
        WHERE job_type_id = l_job_type_id;
    END IF;
    
    IF p_target_type IS NULL AND
       p_job_target_types_out IS NOT NULL AND
       p_job_target_types_out.COUNT > 0 THEN
        p_target_type := p_job_target_types_out(1);
    END IF;
    
    -- Fetch associated and group target types for the currently selected one
    compute_associated_types(p_target_type,
                             p_cluster_types_out,
                             p_group_types_out);
    -- Task targets
    IF p_target_list IS NOT NULL THEN
        OPEN p_targets_out FOR
        SELECT t.target_guid, t.host_name, t.display_name,
               t.target_name, t.target_type, t.timezone_region 
        FROM   MGMT_TARGETS t,
               TABLE(CAST(p_target_list AS MGMT_JOB_TARGET_LIST)) jt
        WHERE  t.target_name=jt.target_name
        AND    t.target_type=jt.target_type;
    
    END IF;

    IF p_tasks_metadata IS NOT NULL AND p_tasks_metadata.COUNT = 1 THEN
        p_tasks_metadata(1).target_type := p_target_type;
        get_tasks_metadata(p_tasks_metadata);
    END IF;
END;


-- get the broken credentials for a Target.
PROCEDURE get_target_broken_creds(target_name_in IN VARCHAR2 DEFAULT NULL ,
                                  target_type_in IN VARCHAR2 DEFAULT NULL,
                                  language_code_in IN VARCHAR2,
                                  country_code_in IN VARCHAR2,
                                  policy_type_in IN NUMBER DEFAULT 1,
                                  cred_arr_out out MGMT_TARGET_FIXCA_ARRAY  )
IS
 --target type peroperty
    l_target_guid            mgmt_targets.target_guid%TYPE;
    l_policy_guid            mgmt_policies.policy_guid%TYPE;
    l_job_id                 mgmt_job.job_id%TYPE;
    l_job_name               mgmt_job.job_name%TYPE;
    l_object_guid            mgmt_policy_assoc_cfg.object_guid%TYPE;
    l_coll_name              mgmt_policy_assoc_cfg.coll_name%TYPE;
    l_key_operator           mgmt_policy_assoc_cfg.key_operator%TYPE;
    l_crit_action_job_id     mgmt_policy_assoc_cfg.crit_action_job_id%TYPE;
    l_warn_action_job_id     mgmt_policy_assoc_cfg.warn_action_job_id%TYPE;
    l_info_action_job_id     mgmt_policy_assoc_cfg.info_action_job_id%TYPE;
    l_policy_label_nlsid     mgmt_policies.policy_label_nlsid%TYPE;
    l_policy_name            mgmt_policies.policy_name%TYPE;
    
    l_type_meta_ver mgmt_targets.type_meta_ver%TYPE;
    l_category_prop_1 mgmt_targets.category_prop_1%TYPE;
    l_category_prop_2 mgmt_targets.category_prop_2%TYPE;
    l_category_prop_3 mgmt_targets.category_prop_3%TYPE;
    l_category_prop_4 mgmt_targets.category_prop_4%TYPE;
    l_category_prop_5 mgmt_targets.category_prop_5%TYPE;

    l_key_part1_value  mgmt_metrics_composite_keys.key_part1_value%TYPE;
    l_key_part2_value  mgmt_metrics_composite_keys.key_part2_value%TYPE;
    l_key_part3_value  mgmt_metrics_composite_keys.key_part3_value%TYPE;
    l_key_part4_value  mgmt_metrics_composite_keys.key_part4_value%TYPE;
    l_key_part5_value  mgmt_metrics_composite_keys.key_part5_value%TYPE;

    l_policy_type  mgmt_policies.policy_type%TYPE;
--  fields in the objects.

    l_target_name            mgmt_targets.target_name%TYPE;
    l_target_type            mgmt_targets.target_type%TYPE;
    l_column_label           mgmt_metrics.column_label%TYPE;
    l_column_label_nlsid     mgmt_metrics.column_label_nlsid%TYPE;
    l_policy_name_display    VARCHAR2(256);
    l_job_owner              mgmt_job.job_owner%TYPE;
    l_broken_reason          mgmt_job.broken_reason%TYPE;
    l_severity               NUMBER(2);
    l_key_value              mgmt_policy_assoc_cfg.key_value%TYPE;
    l_key_all_values         VARCHAR2(1300);    
    l_job_type               mgmt_job.job_type%TYPE;
    l_job_type_category           mgmt_job_type_info.job_type_category%TYPE;

-- counter

    L_TARGET_COUNTER  INTEGER :=1;

--object that contains the rows of the target with broken credentials

    CRED_ARRAY  MGMT_TARGET_FIXCA_ARRAY ;

    CURSOR C_BROKEN_CREDS IS
        SELECT job.job_name, job.job_id ,job.job_owner, job.broken_reason,
               mpac.object_guid, mpac.policy_guid, mpac.coll_name, 
               mpac.key_value, mpac.key_operator               
        FROM mgmt_corrective_action ca, mgmt_job job, mgmt_policy_assoc_cfg mpac
        WHERE ca.job_id   = job.job_id AND
              ca.ca_scope = 1 AND
              ca.ca_target_guid = l_target_guid AND  
              job.broken = 1 AND
              (mpac.crit_action_job_id = ca.job_id OR
               mpac.warn_action_job_id = ca.job_id OR
               mpac.info_action_job_id = ca.job_id);
    -- get the target guids for target type/name with broken CAs or
    -- for all targets with broken CAs if target type/name not specified
        
    CURSOR C_ALL_TARGET_GUIDS IS
       SELECT DISTINCT target_guid , target_name, target_type, type_meta_ver,
              category_prop_1, category_prop_2, category_prop_3,
              category_prop_4, category_prop_5 
        FROM mgmt_targets t,
             ( SELECT c.ca_target_guid
               FROM mgmt_corrective_action c ,
                    mgmt_job j
               WHERE j.job_id = c.job_id
                AND  j.broken=1
                AND  c.ca_scope =1 ) bcat
        WHERE t.target_name LIKE  l_target_name  AND
              t.target_type LIKE  l_target_type  AND
              t.target_guid = bcat.ca_target_guid;

 BEGIN
    IF(target_name_in IS NOT NULL )  THEN
    BEGIN
      l_target_name := target_name_in;
    END;   
    ELSE
    BEGIN
      l_target_name := '%%';
    END;
    END IF;

    IF( target_type_in  IS NOT NULL )  THEN
    BEGIN
      l_target_type := target_type_in;
    END;   
    ELSE
    BEGIN
      l_target_type := '%%';
    END;
    END IF;

    OPEN  C_ALL_TARGET_GUIDS ;
          CRED_ARRAY := MGMT_TARGET_FIXCA_ARRAY(); 
         LOOP
          fetch C_ALL_TARGET_GUIDS INTO l_target_guid, l_target_name, l_target_type, 
                  l_type_meta_ver, l_category_prop_1 , l_category_prop_2,
                  l_category_prop_3, l_category_prop_4, l_category_prop_5 ;
          exit when  C_ALL_TARGET_GUIDS%NOTFOUND;     

--     get the CAs for the target with broken credentials.
       
    OPEN C_BROKEN_CREDS ;
    LOOP
     fetch C_BROKEN_CREDS INTO l_job_name , l_job_id , 
                               l_job_owner, l_broken_reason,
                               l_object_guid, l_policy_guid, l_coll_name, 
                               l_key_value, l_key_operator;
     exit when  C_BROKEN_CREDS%NOTFOUND;
          -- get the severity for the target
    BEGIN
     -- get job type
       BEGIN
           SELECT  job.job_type  
             INTO l_job_type    
             FROM mgmt_job job, mgmt_job_type_info jobinfo, 
                mgmt_job_type_max_versions mv ,
                mgmt_corrective_action ca
           WHERE job.job_type = mv.job_type
            AND  job.job_type_major_version = mv.major_version
            AND  mv.job_type_id = jobinfo.job_type_id 
            AND  job.job_id = ca.job_id 
            AND  jobinfo.job_type_category != 3 --hide multi task
            AND  ca.ca_scope =  1 -- target scope CA
            AND job.job_id = l_job_id ;         
         EXCEPTION 
          WHEN NO_DATA_FOUND THEN
              l_job_type := NULL;
       END ;
   
     IF(policy_type_in =3)
      THEN 
       BEGIN 
         SELECT p.policy_guid, p.policy_name, 
                p.policy_label_nlsid, mpac.crit_action_job_id,
                mpac.warn_action_job_id, mpac.info_action_job_id,
                mpac.key_value, p.policy_type
          INTO  l_policy_guid, l_policy_name,
                l_policy_label_nlsid, l_crit_action_job_id,l_warn_action_job_id,
                l_info_action_job_id, l_key_value, l_policy_type
         FROM mgmt_policy_assoc_cfg mpac, mgmt_policies p
         WHERE mpac.object_guid = l_target_guid AND
               mpac.policy_guid = l_policy_guid AND
               mpac.coll_name = l_coll_name AND
               mpac.key_value = l_key_value AND
               mpac.key_operator = l_key_operator AND
               mpac.policy_guid = p.policy_guid; 
       END;
     ELSE 
         SELECT p.policy_guid, p.policy_name,
                p. policy_label_nlsid, mpac.crit_action_job_id, 
                mpac.warn_action_job_id, info_action_job_id,
                key_value, p.policy_type
          INTO  l_policy_guid, l_policy_name,
                l_policy_label_nlsid,l_crit_action_job_id, l_warn_action_job_id,
                l_info_action_job_id, l_key_value, l_policy_type
          FROM mgmt_policy_assoc_cfg mpac, mgmt_policies p
          WHERE mpac.object_guid = l_target_guid AND
                mpac.policy_guid = l_policy_guid AND
                mpac.coll_name = l_coll_name AND
                mpac.key_value = l_key_value AND
                mpac.key_operator = l_key_operator AND
                mpac.policy_guid = p.policy_guid AND
                p.policy_type = policy_type_in; 
     END IF;

       
    IF(l_key_value IS NOT NULL AND l_key_value != ' ')
     THEN
      BEGIN
          SELECT key_part1_value ,
                 key_part2_value, 
                 key_part3_value,
                 key_part4_value,
                 key_part5_value 
           INTO  l_key_part1_value,
                 l_key_part2_value,
                 l_key_part3_value,
                 l_key_part4_value,
                 l_key_part5_value
          FROM mgmt_metrics_composite_keys
          WHERE composite_key = l_key_value 
           AND target_guid = l_target_guid;

    -- Accumulated key
        IF(l_key_part1_value  IS NOT NULL AND l_key_part1_value != ' ')THEN
           l_key_all_values := l_key_part1_value;
        END IF; 
        IF(l_key_part2_value  IS NOT NULL AND l_key_part2_value != ' ')THEN
           l_key_all_values := l_key_all_values ||','|| l_key_part2_value;
        END IF; 
        IF(l_key_part3_value  IS NOT NULL AND l_key_part3_value != ' ')THEN
           l_key_all_values := l_key_all_values ||','|| l_key_part3_value ;
        END IF; 
        IF(l_key_part4_value  IS NOT NULL AND l_key_part4_value != ' ')THEN
           l_key_all_values := l_key_all_values ||','||l_key_part4_value ;
        END IF; 
        IF(l_key_part5_value  IS NOT NULL AND l_key_part5_value != ' ')THEN
           l_key_all_values := l_key_all_values ||','|| l_key_part5_value;
        END IF; 

      EXCEPTION
         WHEn NO_DATA_FOUND THEN
         l_key_all_values := l_key_value ;

      END ;

     ELSE
         l_key_all_values := l_key_value;
    END IF; 


    IF( l_crit_action_job_id  IS NOT NULL AND
                     l_warn_action_job_id  IS NOT NULL)
    THEN
       l_severity := CA_WARNING_CRITICAL_BOTH;

     ELSIF( l_crit_action_job_id  IS NOT NULL)
     THEN
       l_severity := CA_CRITICAL_ONLY;
     ELSIF(l_warn_action_job_id  IS NOT NULL)
     THEN
       l_severity := CA_WARNING_ONLY;
     ELSE
       l_severity := CA_NONE;

    END IF;

     -- get the metric name/policy name
    IF(l_policy_type = 1)
     THEN
      BEGIN
         SELECT column_label ,column_label_nlsid
         INTO  l_column_label ,l_column_label_nlsid
         FROM  mgmt_metrics
         WHERE metric_guid = l_policy_guid
          AND  type_meta_ver = l_type_meta_ver
          AND  (category_prop_1 = l_category_prop_1 OR category_prop_1 = ' ')
          AND  (category_prop_2 = l_category_prop_2 OR category_prop_2 = ' ')
          AND  (category_prop_3 = l_category_prop_3 OR category_prop_3 = ' ')
          AND  (category_prop_4 = l_category_prop_4 OR category_prop_4 = ' ')
          AND  (category_prop_5 = l_category_prop_5 OR category_prop_5 = ' ');

        l_policy_name_display := NULL;
       EXCEPTION 
         WHEN NO_DATA_FOUND THEN
                NULL;
      END; 
     ELSE
       BEGIN
        l_policy_name_display := MGMT_MESSAGE.GET_MESSAGE(l_policy_label_nlsid, 'POLICY',
                               language_code_in, country_code_in, l_policy_name);
        l_column_label  := NULL ;
        l_column_label_nlsid  := NULL ;
       END;
    END IF; 

    -- assign in the array Object.
    CRED_ARRAY.extend(1);
    CRED_ARRAY(L_TARGET_COUNTER) := MGMT_TARGET_FIXCA_RECORD(
                                                         l_target_name,
                                                         l_target_type,
                                                         l_policy_name_display,
                                                         l_column_label,
                                                         l_column_label_nlsid,
                                                         l_severity ,
                                                         l_job_name ,
                                                         l_job_owner,
                                                         l_job_id,
                                                         l_key_all_values,
                                                         l_broken_reason,
                                                         l_policy_type,
                                                         l_job_type
                                                       );

        L_TARGET_COUNTER :=  L_TARGET_COUNTER + 1 ;
    EXCEPTION
    WHEN NO_DATA_FOUND  THEN NULL;
    END;
    END LOOP ;
    close C_BROKEN_CREDS ;
    END LOOP ;
    close C_ALL_TARGET_GUIDS ;
    cred_arr_out :=   CRED_ARRAY;
END get_target_broken_creds;

-- get broken credentials for a group of targets.

PROCEDURE get_target_group_broken_creds ( group_name_in IN VARCHAR2,
                               language_code_in IN VARCHAR2,
                               country_code_in IN VARCHAR2,
                               cred_arr_out  OUT MGMT_TARGET_FIXCA_ARRAY ) IS 
        l_group_id  MGMT_TARGETS.TARGET_GUID%TYPE ;
 
        l_member_target_name  MGMT_TARGETS.TARGET_NAME%TYPE ;
        l_member_target_type  MGMT_TARGETS.TARGET_TYPE%TYPE ;
        l_member_target_guid  MGMT_TARGETS.TARGET_GUID%TYPE ;
 

        L_TARGET_COUNTER     INTEGER :=1;
        i INTEGER :=1;
 
        l_CRED_ARRAY_PASS  MGMT_TARGET_FIXCA_ARRAY ;
        l_CRED_ARRAY  MGMT_TARGET_FIXCA_ARRAY := MGMT_TARGET_FIXCA_ARRAY();
 
 -- Define the cursor which contains all the target names for this 
 -- group name 
 
        cursor C_GROUP_BROKEN_CREDS(p_group_id IN MGMT_TARGETS.target_guid%TYPE)  
        IS
        SELECT mt.target_name member_target_name, 
               mt.target_type member_target_type , 
               a.assoc_target_guid member_target_guid 
        FROM MGMT_FLAT_TARGET_ASSOC a, MGMT_TARGETS mt
        WHERE a.source_target_guid = p_group_id 
         AND  a.assoc_target_guid = mt.target_guid          
         AND  a.is_membership = 1;
     BEGIN
 
 -- get the group id for the group name , it should be single row.
 -- to-do , is there any othe way to make sure that we get only 
 -- one single row for a group name.
 
       SELECT DISTINCT source_target_guid 
       INTO l_group_id
       FROM MGMT_FLAT_TARGET_ASSOC a, MGMT_TARGETS ct
       WHERE ct.target_name = group_name_in
       AND a.is_membership = 1
       AND a.source_target_guid = ct.target_guid;


       FOR crec IN C_GROUP_BROKEN_CREDS(l_group_id)
       LOOP
       get_target_broken_creds( crec.member_target_name ,
                                crec.member_target_type ,
                                language_code_in,
                                country_code_in ,
                                3, 
                                l_CRED_ARRAY_PASS ); 
   
       i := l_CRED_ARRAY_PASS.FIRST;  -- get subscript of first element
       WHILE i IS NOT NULL LOOP
      
      l_CRED_ARRAY.extend(1);
      l_CRED_ARRAY(L_TARGET_COUNTER)  := l_CRED_ARRAY_PASS(i);
      L_TARGET_COUNTER :=  L_TARGET_COUNTER + 1 ;
      i := l_CRED_ARRAY_PASS.NEXT(i);  -- get subscript of next element
 
  
  END LOOP;
  END LOOP;
-- close C_GROUP_BROKEN_CREDS ;
cred_arr_out := l_CRED_ARRAY ;

END;
 

PROCEDURE get_tasks_metadata(p_tasks_metadata IN OUT MGMT_PARAM_CRED_TGT_MET_ARR) IS
l_parameters_names MGMT_JOB_VECTOR_PARAMS;
l_target_type_count NUMBER(4);

l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_major_version MGMT_JOB_TYPE_INFO.major_version%TYPE;

BEGIN
    
    FOR i IN 1..p_tasks_metadata.count LOOP
        -- Parameters metadata
        MGMT_JOB_ENGINE.get_job_type_metadata(p_tasks_metadata(i).job_type, 
                                              p_tasks_metadata(i).parameter_metadata);

        IF p_tasks_metadata(i).target_type IS NOT NULL AND
           p_tasks_metadata(i).job_type IS NOT NULL THEN
            -- Credentials metadata
            MGMT_JOB_ENGINE.get_job_credential_metadata(p_tasks_metadata(i).job_type, 
                                                        p_tasks_metadata(i).target_type,
                                                        p_tasks_metadata(i).credential_metadata);
        END IF;

        -- Target type metadata (needed by params page)
        MGMT_JOB_ENGINE.get_target_type_data(p_tasks_metadata(i).target_type,
                                             p_tasks_metadata(i).target_properties);

        p_tasks_metadata(i).required_params := SMP_EMD_STRING_ARRAY();

        MGMT_JOB_ENGINE.get_max_versions(p_tasks_metadata(i).job_type,
                                         l_major_version,
                                         l_job_type_id);
                                            
        ---starting fetching required and encrypted parameter information 
        FOR crec IN (SELECT parameter_names 
                     FROM MGMT_JOB_PARAM_SOURCE
                     WHERE  job_type_id = l_job_type_id
                        AND (required =1)) 
        LOOP
            l_parameters_names := crec.parameter_names;

            -- Go through each one of these parameters
            FOR j IN 1..l_parameters_names.COUNT LOOP
                p_tasks_metadata(i).required_params.extend;
                p_tasks_metadata(i).required_params(p_tasks_metadata(i).required_params.LAST) 
                                                                    := l_parameters_names(j);
            END LOOP;
        END LOOP;

        -- The job target types
        SELECT count(*) INTO l_target_type_count
        FROM   MGMT_JOB_SINGLE_TARGET_TYPES
        WHERE  job_type_id = l_job_type_id;
        
        IF l_target_type_count > 0 THEN
            p_tasks_metadata(i).targets_required := 1;
        ELSE
            p_tasks_metadata(i).targets_required := 0;
        END IF;        
    END LOOP;
END;

PROCEDURE get_meta_group_cluster_info(p_job_type VARCHAR2,
                                      p_new_target_type VARCHAR2,
                                      p_fetch_metadata NUMBER,
                                      p_tasks_metadata OUT MGMT_PARAM_CRED_TGT_MET_ARR,
                                      p_cluster_types_out OUT SMP_EMD_STRING_ARRAY,
                                      p_group_types_out OUT SMP_EMD_STRING_ARRAY) IS
BEGIN
    IF p_fetch_metadata = 1 THEN
        -- Construct an array with only one element
        p_tasks_metadata := MGMT_PARAM_CRED_TGT_MET_ARR();
        p_tasks_metadata.extend;
        p_tasks_metadata(p_tasks_metadata.LAST) := MGMT_PARAM_CRED_TGT_MET(p_job_type,
                                                       p_new_target_type, 
                                                       NULL, NULL, NULL, NULL, 0);

        -- populate the metadata into array
        get_tasks_metadata(p_tasks_metadata);
    END IF;
    -- Fetch associated and group target types for the currently selected one
    compute_associated_types(p_new_target_type,
                             p_cluster_types_out,
                             p_group_types_out);
END;
    
PROCEDURE compute_jobtype_categories(p_job_type VARCHAR2,
                                     p_category_a IN OUT NUMBER,
                                     p_category_b IN OUT NUMBER) IS
BEGIN
    IF p_job_type IS NULL OR p_job_type IN ('_','%')
    THEN
        --null or wild card entry
        p_category_a := MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_INTERNAL;
        p_category_b := MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_HIDDEN;
    ELSIF p_job_type = MGMT_JOB_ENGINE.MULTITASK_JOB_TYPE
    THEN
        --job type is multitask
        p_category_a := MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_HIDDEN;
        p_category_b := MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_HIDDEN;
    ELSE
        --job type is some other type except multitask
        p_category_a := MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_INTERNAL;
        p_category_b := MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_INTERNAL;
    END IF;        

END;


FUNCTION check_aggregate_membership(p_aggregate_target MGMT_JOB_TARGET_RECORD,
                                    p_base_target_type VARCHAR2)
    RETURN NUMBER IS
l_return NUMBER := 0;
BEGIN
    IF p_aggregate_target IS NULL OR p_base_target_type IS NULL 
    THEN
        RETURN 0;
    END IF;
    
    SELECT COUNT(*) INTO l_return
    FROM MGMT_FLAT_TARGET_MEMBERSHIPS, MGMT_TYPE_PROPERTIES
    WHERE composite_target_name = p_aggregate_target.target_name
      AND composite_target_type =  p_aggregate_target.target_type
      AND member_target_type = p_base_target_type
      AND target_type=p_aggregate_target.target_type
      AND property_name='is_aggregate'
      AND property_value=1;

    return l_return;
    
END;

PROCEDURE get_initial_job_target(p_initial_target_type VARCHAR2,
                                 p_initial_target VARCHAR2,
                                 p_initial_member_target_type VARCHAR2,
                                 p_job_target_types SMP_EMD_STRING_ARRAY,
                                 p_default_target_type IN OUT VARCHAR2,
                                 p_initial_targets_out OUT TARGETS_CURSOR) IS
l_fetch_initial_target NUMBER := 0;
l_target_record MGMT_JOB_TARGET_RECORD;
l_member_target_types SMP_EMD_STRING_ARRAY;
BEGIN
    IF p_initial_target IS NOT NULL AND
       p_initial_target_type IS NOT NULL
    THEN
        --adjust default target type if required
        IF p_job_target_types IS NOT NULL
        THEN
            l_target_record := MGMT_JOB_TARGET_RECORD(p_initial_target,
                                                      p_initial_target_type);
                                                      
            --Case 1: target, target type, member target type are specified
            IF p_initial_member_target_type IS NOT NULL
            THEN
                --if target is an aggregate, and if it has atleast one member
                --of initial target type
                IF check_aggregate_membership(l_target_record,  
                                              p_initial_member_target_type) != 0  
                THEN
                    --check if member target type is in list of allowed types
                    FOR i IN 1..p_job_target_types.COUNT LOOP
                        IF p_job_target_types(i)=p_initial_member_target_type 
                        THEN
                            p_default_target_type := p_initial_member_target_type;
                            l_fetch_initial_target := 1;
                            exit;
                        END IF;
                    END LOOP;
                END IF;
            ELSE
            -- target, target type are specified, but member target type is not

            -- Case 2: see if target type itself is on list of allowed types.
            -- If it is, even if it's an aggregate type, it is the default
                FOR i IN 1..p_job_target_types.COUNT LOOP
                    IF p_job_target_types(i)=p_initial_target_type 
                    THEN
                        p_default_target_type := p_initial_target_type;
                        l_fetch_initial_target := 1;
                        exit;
                    END IF;
                END LOOP;

                IF l_fetch_initial_target != 1
                THEN
                -- Case 3: target type is an aggregate type, and has at least
                -- one member of the default target type
                    IF check_aggregate_membership(l_target_record,  
                                                  p_default_target_type) != 0
                    THEN
                        l_fetch_initial_target := 1;

                -- Case 4: target is an aggregate type, and one of its members
                -- is of a type that the job type supports,
                    ELSIF is_aggregate_type(p_initial_target_type) != 0
                    THEN
                        SELECT DISTINCT member_target_type BULK COLLECT INTO l_member_target_types
                        FROM MGMT_FLAT_TARGET_MEMBERSHIPS
                        WHERE composite_target_name = l_target_record.target_name
                          AND composite_target_type =  l_target_record.target_type
                        ORDER BY member_target_type;

                        --check if any of the member target types is in list of allowed types
                        --select the first one found to be default target type
                        IF l_member_target_types IS NOT NULL AND 
                           l_member_target_types.COUNT > 0
                        THEN
                            FOR i IN 1..l_member_target_types.COUNT LOOP
                                FOR j IN 1..p_job_target_types.COUNT LOOP
                                    IF p_job_target_types(j)=l_member_target_types(i) 
                                    THEN
                                        p_default_target_type := l_member_target_types(i);
                                        l_fetch_initial_target := 1;
                                        EXIT;
                                    END IF;
                                END LOOP;
                                IF l_fetch_initial_target = 1 THEN
                                     EXIT;
                                END IF;
                            END LOOP;
                        END IF;
                    --Case 5. target is an aggregate type,
                    --but no member is a default target type
                    --no member has a type that the job type supports
                    ELSE
                        --no default target type should be seleted
                        p_default_target_type := null; 
                    END IF;        
                END IF;
            END IF;
        ELSE
            --if job has no target types, it can operate on all types
            l_fetch_initial_target := 1;
        END IF;

   END IF;
   --for template CAs we provide only p_initial_target_type
    IF p_initial_target IS NULL AND
       p_initial_target_type IS NOT NULL
    THEN
     p_default_target_type := p_initial_target_type ;
    END IF;
   --fetch initial targets only if target type/member target type is valid
   IF l_fetch_initial_target = 1
   THEN
        -- Initial Job targets
        OPEN p_initial_targets_out FOR
        SELECT  target_guid, host_name, display_name,
                target_name, target_type, timezone_region, emd_url
        FROM    MGMT_TARGETS t
        WHERE   target_name=p_initial_target
          AND   target_type=p_initial_target_type;
    ELSE
         -- Some versions of jdbc cannot handle null cursors
        OPEN p_initial_targets_out FOR   
        SELECT 1,2,3,4,5,6,7 FROM DUAL WHERE ROWNUM<1;
    END IF;

END;

-- Get the job Notification Count to display Link in the Results and task Pages
procedure get_job_notification_count(p_execution_id IN RAW,
                          p_notification_count_out OUT NUMBER) is 

BEGIN
 select count(notif.message) count into  p_notification_count_out
          from mgmt_job_state_changes state,  
               mgmt_notification_log notif 
          where state.execution_id = p_execution_id
             and state.state_change_guid = notif.source_obj_guid 
             and notif.source_obj_type = 3 ; 
END;


-- Internal method to open the step cursor for the run details
-- page. 
PROCEDURE open_run_details_step_cursor(p_job_id RAW,
                                       p_scheduled_time DATE,
                                       p_status NUMBER,
                                       p_target_name VARCHAR2,
                                       p_submitted_targets_count NUMBER,
                                       p_steps_cursor_out OUT STEPS_CURSOR) IS
BEGIN

    IF EMDW_LOG.p_is_debug_set THEN
        EMDW_LOG.debug('IN MGMT_JOB_UI.open_run_details_step_cursor()', 
                      MGMT_JOB_ENGINE.MODULE_NAME);
    END IF;

    -- Handle targetless jobs first. Note that the target filter
    -- does not apply for targetless jobs
    IF p_submitted_targets_count = 0 THEN
        IF p_status IS NULL THEN
            OPEN p_steps_cursor_out FOR
            SELECT  DISTINCT h.execution_id, h.job_id, h.step_id, h.step_name,
                    h.step_type, h.step_status, t.target_name, t.target_type,
                    h.start_time, h.end_time,
                    h.parent_step_id, h.TIMEZONE_REGION,
                    (( DECODE ( h.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h.end_time ) -h.start_time)*24*60*60) run_time_seconds ,
                    execplan.step_nlsid as step_nlsid,
                    execplan.step_default as step_default,
                    execplan.job_type as job_type
            FROM    MGMT_JOB_HISTORY h, MGMT_JOB_EXEC_SUMMARY e,
                    MGMT_JOB_STEP_TARGETS jst,
                    MGMT_TARGETS t, 
                    (  SELECT j.job_id as job_id, j.job_type as job_type, ep.step_name, ep.step_type, step_nlsid, step_default 
                       FROM   MGMT_JOB_HISTORY h, MGMT_JOB_EXEC_SUMMARY e, 
                              MGMT_JOB j, MGMT_JOB_EXECPLAN ep
                       WHERE  e.execution_id=h.execution_id
                       AND    h.step_type=MGMT_JOB_ENGINE.STEPTYPE_STEP
                       AND    e.job_id=p_job_id
                       AND    scheduled_time=p_scheduled_time
                       AND    h.job_id=j.parent_job_id
                       AND    ep.job_type_id=j.nested_job_type_id
                     UNION
                       SELECT job_id, ji.job_type, ep.step_name, ep.step_type, step_nlsid, step_default
                       FROM   MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN ep,
                              MGMT_JOB_TYPE_INFO ji
                       WHERE  e.job_id=p_job_id
                       AND    scheduled_time=p_scheduled_time
                       AND    e.job_type_id=ep.job_type_id
                       AND    ep.job_type_id=ji.job_type_id
                    ) execplan
            WHERE   h.step_id=jst.step_id(+)
            AND     jst.target_guid=t.target_guid (+)
            AND     e.job_id=p_job_id
            AND     e.scheduled_time=p_scheduled_time
            AND     e.execution_id=h.execution_id
            AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            AND     h.job_id=execplan.job_id (+) 
            AND     h.step_name=execplan.step_name (+)
            AND     h.step_type=execplan.step_type (+)
            ORDER BY execution_id, step_id;
        ELSE
            OPEN p_steps_cursor_out FOR
            SELECT  DISTINCT h.execution_id, h.job_id, h.step_id, h.step_name,
                    h.step_type, h.step_status, t.target_name, t.target_type,
                    h.start_time, h.end_time,
                    h.parent_step_id, h.TIMEZONE_REGION,
                    (( DECODE ( h.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h.end_time ) -h.start_time)*24*60*60) run_time_seconds ,
                    execplan.step_nlsid as step_nlsid,
                    execplan.step_default as step_default,
                    execplan.job_type as job_type
            FROM    MGMT_JOB_HISTORY h, MGMT_JOB_EXEC_SUMMARY e,
                    MGMT_JOB_STEP_TARGETS jst,
                    MGMT_TARGETS t, 
                    (  SELECT j.job_id as job_id, j.job_type as job_type, ep.step_name, ep.step_type, step_nlsid, step_default 
                       FROM   MGMT_JOB_HISTORY h, MGMT_JOB_EXEC_SUMMARY e, 
                              MGMT_JOB j, MGMT_JOB_EXECPLAN ep
                       WHERE  e.execution_id=h.execution_id
                       AND    h.step_type=MGMT_JOB_ENGINE.STEPTYPE_STEP
                       AND    e.job_id=p_job_id
                       AND    scheduled_time=p_scheduled_time
                       AND    h.job_id=j.parent_job_id
                       AND    ep.job_type_id=j.nested_job_type_id
                     UNION
                       SELECT job_id, ji.job_type, ep.step_name, ep.step_type, step_nlsid, step_default
                       FROM   MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN ep,
                              MGMT_JOB_TYPE_INFO ji
                       WHERE  e.job_id=p_job_id
                       AND    scheduled_time=p_scheduled_time
                       AND    e.job_type_id=ep.job_type_id
                       AND    ep.job_type_id=ji.job_type_id
                    ) execplan
            WHERE   h.step_id=jst.step_id(+)
            AND     jst.target_guid=t.target_guid (+)
            AND     e.job_id=p_job_id
            AND     e.scheduled_time=p_scheduled_time
            AND     e.execution_id=h.execution_id
            AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            AND     h.job_id=execplan.job_id (+) 
            AND     h.step_name=execplan.step_name (+)
            AND     h.step_type=execplan.step_type (+)
            AND     (
                      (e.status = p_status AND p_status >= 0)
                       OR
                      (e.status_bucket = p_status AND p_status <0)
                       OR
                      (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                       OR
                      ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                      AND 
                      e.status_bucket in (MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                          ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                          ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                      )
                    )
            ORDER BY execution_id, step_id;
        END IF;

        RETURN;
    END IF;

    -- The following processing is only for jobs with targets
    -- Only select executions whose targets the current user
    -- has VIEW on
    IF p_target_name IS NULL AND p_status IS NULL THEN

        OPEN p_steps_cursor_out FOR
        SELECT  DISTINCT h.execution_id, h.job_id, h.step_id, h.step_name,
                h.step_type, h.step_status, t.target_name, t.target_type,
                h.start_time, h.end_time,
                h.parent_step_id, h.TIMEZONE_REGION,
                (( DECODE ( h.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h.end_time ) -h.start_time)*24*60*60) run_time_seconds ,
                execplan.step_nlsid as step_nlsid,
                execplan.step_default as step_default,
                execplan.job_type as job_type
        FROM    MGMT_JOB_HISTORY h, 
                MGMT_JOB_STEP_TARGETS jst,
                MGMT_TARGETS t,
                (  SELECT j.job_id as job_id, j.job_type as job_type, ep.step_name, ep.step_type, step_nlsid, step_default 
                   FROM   MGMT_JOB_HISTORY h, MGMT_JOB_EXEC_SUMMARY e, 
                          MGMT_JOB j, MGMT_JOB_EXECPLAN ep
                   WHERE  e.execution_id=h.execution_id
                   AND    h.step_type=MGMT_JOB_ENGINE.STEPTYPE_STEP
                   AND    e.job_id=p_job_id
                   AND    scheduled_time=p_scheduled_time
                   AND    h.job_id=j.parent_job_id
                   AND    ep.job_type_id=j.nested_job_type_id
                 UNION
                   SELECT job_id, ji.job_type, ep.step_name, ep.step_type, step_nlsid, step_default
                   FROM   MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN ep,
                          MGMT_JOB_TYPE_INFO ji
                   WHERE  e.job_id=p_job_id
                   AND    scheduled_time=p_scheduled_time
                   AND    e.job_type_id=ep.job_type_id
                   AND    ep.job_type_id=ji.job_type_id
                ) execplan,
                (SELECT e.execution_id
                 FROM   MGMT_JOB_EXEC_SUMMARY e,
                        MGMT_TARGETS t, MGMT_JOB_EXT_TARGETS jt
                 WHERE  e.job_id=p_job_id
                 AND    e.scheduled_time=p_scheduled_time
                 AND    e.job_id=jt.job_id
                 AND    e.execution_id=jt.execution_id
                 AND    jt.target_guid=t.target_guid
                 AND    e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) jobExecs
        WHERE   h.step_id=jst.step_id(+)
        AND     jst.target_guid=t.target_guid (+)
        AND     jobExecs.execution_id=h.execution_id
        AND     h.job_id=execplan.job_id (+) 
        AND     h.step_name=execplan.step_name (+)
        AND     h.step_type=execplan.step_type (+)
        ORDER BY execution_id,  step_id;
    ELSIF p_target_name IS NOT NULL AND p_status IS NULL THEN
        OPEN p_steps_cursor_out FOR
        SELECT  DISTINCT h.execution_id, h.job_id, h.step_id, h.step_name,
                h.step_type, h.step_status, t.target_name, t.target_type,
                h.start_time, h.end_time,
                h.parent_step_id, h.TIMEZONE_REGION,
                (( DECODE ( h.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h.end_time ) -h.start_time)*24*60*60) run_time_seconds ,
                execplan.step_nlsid as step_nlsid,
                execplan.step_default as step_default,
                execplan.job_type as job_type
        FROM    MGMT_JOB_HISTORY h, 
                MGMT_JOB_STEP_TARGETS jst,
                MGMT_TARGETS t,
                (  SELECT j.job_id as job_id, j.job_type as job_type, ep.step_name, ep.step_type, step_nlsid, step_default 
                   FROM   MGMT_JOB_HISTORY h, MGMT_JOB_EXEC_SUMMARY e, 
                          MGMT_JOB j, MGMT_JOB_EXECPLAN ep
                   WHERE  e.execution_id=h.execution_id
                   AND    h.step_type=MGMT_JOB_ENGINE.STEPTYPE_STEP
                   AND    e.job_id=p_job_id
                   AND    scheduled_time=p_scheduled_time
                   AND    h.job_id=j.parent_job_id
                   AND    ep.job_type_id=j.nested_job_type_id
                 UNION
                   SELECT job_id, ji.job_type, ep.step_name, ep.step_type, step_nlsid, step_default
                   FROM   MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN ep,
                          MGMT_JOB_TYPE_INFO ji
                   WHERE  e.job_id=p_job_id
                   AND    scheduled_time=p_scheduled_time
                   AND    e.job_type_id=ep.job_type_id
                   AND    ep.job_type_id=ji.job_type_id
                ) execplan,
                (SELECT e.execution_id
                 FROM   MGMT_JOB_EXEC_SUMMARY e,
                        MGMT_TARGETS t, MGMT_JOB_EXT_TARGETS jt
                 WHERE  e.job_id=p_job_id
                 AND    e.scheduled_time=p_scheduled_time
                 AND    e.job_id=jt.job_id
                 AND    e.execution_id=jt.execution_id
                 AND    jt.target_guid=t.target_guid
                 AND    t.target_name LIKE p_target_name
                 AND    e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) jobExecs
        WHERE   h.step_id=jst.step_id(+)
        AND     jst.target_guid=t.target_guid (+)
        AND     jobExecs.execution_id=h.execution_id
        AND     h.job_id=execplan.job_id (+) 
        AND     h.step_name=execplan.step_name (+)
        AND     h.step_type=execplan.step_type (+)
        ORDER BY execution_id,  step_id;
    ELSIF p_target_name IS NOT NULL AND p_status IS NOT NULL THEN
        OPEN p_steps_cursor_out FOR
        SELECT  DISTINCT h.execution_id, h.job_id, h.step_id, h.step_name,
                h.step_type, h.step_status, t.target_name, t.target_type,
                h.start_time, h.end_time,
                h.parent_step_id, h.TIMEZONE_REGION,
                (( DECODE ( h.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h.end_time ) -h.start_time)*24*60*60) run_time_seconds ,
                execplan.step_nlsid as step_nlsid,
                execplan.step_default as step_default,
                execplan.job_type as job_type
        FROM    MGMT_JOB_HISTORY h, 
                MGMT_JOB_STEP_TARGETS jst,
                MGMT_TARGETS t,
                (  SELECT j.job_id as job_id, j.job_type as job_type, ep.step_name, ep.step_type, step_nlsid, step_default 
                   FROM   MGMT_JOB_HISTORY h, MGMT_JOB_EXEC_SUMMARY e, 
                          MGMT_JOB j, MGMT_JOB_EXECPLAN ep
                   WHERE  e.execution_id=h.execution_id
                   AND    h.step_type=MGMT_JOB_ENGINE.STEPTYPE_STEP
                   AND    e.job_id=p_job_id
                   AND    scheduled_time=p_scheduled_time
                   AND    h.job_id=j.parent_job_id
                   AND    ep.job_type_id=j.nested_job_type_id
                 UNION
                   SELECT job_id, ji.job_type, ep.step_name, ep.step_type, step_nlsid, step_default
                   FROM   MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN ep,
                          MGMT_JOB_TYPE_INFO ji
                   WHERE  e.job_id=p_job_id
                   AND    scheduled_time=p_scheduled_time
                   AND    e.job_type_id=ep.job_type_id
                   AND    ep.job_type_id=ji.job_type_id
                ) execplan,
                (SELECT e.execution_id
                 FROM   MGMT_JOB_EXEC_SUMMARY e,
                        MGMT_TARGETS t, MGMT_JOB_EXT_TARGETS jt
                 WHERE  e.job_id=p_job_id
                 AND    e.scheduled_time=p_scheduled_time
                 AND    e.job_id=jt.job_id
                 AND    e.execution_id=jt.execution_id
                 AND    jt.target_guid=t.target_guid
                 AND    t.target_name LIKE p_target_name
                 AND    (
                          (e.status = p_status AND p_status >= 0)
                          OR
                          (e.status_bucket = p_status AND p_status <0)
                          OR
                          (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                          OR
                          ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                          AND 
                          e.status_bucket in (MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                              ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                              ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                          )
                        )
                 AND    e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) jobExecs
        WHERE   h.step_id=jst.step_id(+)
        AND     jst.target_guid=t.target_guid (+)
        AND     jobExecs.execution_id=h.execution_id
        AND     h.job_id=execplan.job_id (+) 
        AND     h.step_name=execplan.step_name (+)
        AND     h.step_type=execplan.step_type (+)
        ORDER BY execution_id,  step_id;
    ELSIF p_target_name IS NULL AND p_status IS NOT NULL THEN
        OPEN p_steps_cursor_out FOR
        SELECT  DISTINCT h.execution_id, h.job_id, h.step_id, h.step_name,
                h.step_type, h.step_status, t.target_name, t.target_type,
                h.start_time, h.end_time,
                h.parent_step_id, h.TIMEZONE_REGION,
                (( DECODE ( h.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h.end_time ) -h.start_time)*24*60*60) run_time_seconds ,
                execplan.step_nlsid as step_nlsid,
                execplan.step_default as step_default,
                execplan.job_type as job_type
        FROM    MGMT_JOB_HISTORY h, 
                MGMT_JOB_STEP_TARGETS jst,
                MGMT_TARGETS t,
                (  SELECT j.job_id as job_id, j.job_type as job_type, ep.step_name, ep.step_type, step_nlsid, step_default 
                   FROM   MGMT_JOB_HISTORY h, MGMT_JOB_EXEC_SUMMARY e, 
                          MGMT_JOB j, MGMT_JOB_EXECPLAN ep
                   WHERE  e.execution_id=h.execution_id
                   AND    h.step_type=MGMT_JOB_ENGINE.STEPTYPE_STEP
                   AND    e.job_id=p_job_id
                   AND    scheduled_time=p_scheduled_time
                   AND    h.job_id=j.parent_job_id
                   AND    ep.job_type_id=j.nested_job_type_id
                 UNION
                   SELECT job_id, ji.job_type, ep.step_name, ep.step_type, step_nlsid, step_default
                   FROM   MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN ep,
                          MGMT_JOB_TYPE_INFO ji
                   WHERE  e.job_id=p_job_id
                   AND    scheduled_time=p_scheduled_time
                   AND    e.job_type_id=ep.job_type_id
                   AND    ep.job_type_id=ji.job_type_id
                ) execplan,
                (SELECT e.execution_id
                 FROM   MGMT_JOB_EXEC_SUMMARY e,
                        MGMT_TARGETS t, MGMT_JOB_EXT_TARGETS jt
                 WHERE  e.job_id=p_job_id
                 AND    e.scheduled_time=p_scheduled_time
                 AND    e.job_id=jt.job_id
                 AND    e.execution_id=jt.execution_id
                 AND    jt.target_guid=t.target_guid
                 AND    (
                          (e.status = p_status AND p_status >= 0)
                          OR
                          (e.status_bucket = p_status AND p_status <0)
                          OR
                          (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                          OR
                          ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                          AND 
                          e.status_bucket in (MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                              ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                              ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                          )
                        )
                 AND    e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS) jobExecs
        WHERE   h.step_id=jst.step_id(+)
        AND     jst.target_guid=t.target_guid (+)
        AND     jobExecs.execution_id=h.execution_id
        AND     h.job_id=execplan.job_id (+) 
        AND     h.step_name=execplan.step_name (+)
        AND     h.step_type=execplan.step_type (+)
        ORDER BY execution_id,  step_id;
    END IF;
END;

-- Get all information needed to display the run details page
-- p_filter_target_name The target name to filter on, null if none selected
-- p_filter_status The status to filter on, -1 if none selected
PROCEDURE get_run_details(p_execution_id IN RAW,
                          p_target_name IN VARCHAR2,
                          p_status IN NUMBER,
                          p_steps_cursor_out OUT STEPS_CURSOR,
                          p_exec_targets_cursor_out OUT EXEC_TARGETS_CURSOR,
                          p_scheduled_time_out OUT DATE,
                          p_scheduled_time_zone OUT VARCHAR2,
                          p_job_info_out OUT JOB_INFO_CURSOR,
                          p_task_tgt_runs_cursor_out  OUT TASK_INFO_CURSOR,
                          p_job_name_out OUT VARCHAR2,
                          p_schedule_out OUT MGMT_JOB_SCHEDULE_RECORD,
                          p_job_category_out OUT NUMBER,
                          p_submitted_targets_string_out OUT VARCHAR2,
                          p_ca_reference_count OUT NUMBER,
                          p_is_corrective_action OUT NUMBER
                          ) IS

l_job_id MGMT_JOB.job_id%TYPE;
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_submitted_targets_count NUMBER;

BEGIN
    BEGIN
        SELECT  e.job_id, job_name, scheduled_time, timezone_region, 
                j.is_corrective_action, ca.ca_reference_count
        INTO    l_job_id, p_job_name_out, p_scheduled_time_out, 
                p_scheduled_time_zone, p_is_corrective_action, p_ca_reference_count
        FROM    MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB j, MGMT_CORRECTIVE_ACTION ca
        WHERE   execution_id=p_execution_id
        AND     e.job_id=j.job_id
        AND     j.job_id=ca.job_id(+)
        AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS; 
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_EXECUTION_ERR,
                                    'Invalid execution id');
    END;

    get_submitted_targets (l_job_id, p_submitted_targets_string_out);
    
    OPEN p_job_info_out FOR
        SELECT  job_type, job_owner, job_description, job_status, job_name
        FROM    MGMT_JOB
        WHERE   job_id=l_job_id;

    l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(l_job_id);

    SELECT  job_type_category INTO p_job_category_out 
    FROM    MGMT_JOB_TYPE_INFO
    WHERE   job_type_id = l_job_type_id;
        
    get_results_schedule(l_job_id, p_schedule_out);

    -- Figure out whether the job is targetless or not...
    SELECT  COUNT(*) INTO l_submitted_targets_count
    FROM    MGMT_JOB_EXT_TARGETS
    WHERE   job_id=l_job_id
    AND     ROWNUM=1;

    -- Open the steps cursor
    open_run_details_step_cursor(l_job_id, p_scheduled_time_out,
                                 p_status, p_target_name,
                                 l_submitted_targets_count,
                                 p_steps_cursor_out);

    -- Open the exec targets and task target cursors
    IF p_target_name IS NULL AND p_status IS NULL THEN
        OPEN p_exec_targets_cursor_out FOR
            SELECT  jt.execution_id, target_name, target_type
            FROM    MGMT_JOB_EXT_TARGETS jt, MGMT_TARGETS t,
                    MGMT_JOB_EXEC_SUMMARY e
            WHERE   jt.target_guid=t.target_guid
            AND     jt.job_id=e.job_id
            AND     jt.execution_id=e.execution_id
            AND     e.job_id=l_job_id
            AND     e.scheduled_time=p_scheduled_time_out
            AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            ORDER BY jt.execution_id, target_type, target_name;

        OPEN p_task_tgt_runs_cursor_out FOR
        SELECT h1.step_id, target_name, target_type,h1.execution_id
            FROM   MGMT_TARGETS t,
                   MGMT_JOB_EXEC_SUMMARY e,
                   MGMT_JOB_HISTORY h1,
                   MGMT_JOB_HISTORY h2,
                   MGMT_JOB_EXT_TARGETS jt
            WHERE  e.job_id=l_job_id
            AND       e.scheduled_time=p_scheduled_time_out
            AND       h1.execution_id=e.execution_id
            AND       h1.parent_step_id > 0
            AND       h1.step_type = 7
            AND       h1.step_id=h2.parent_step_id
            AND       h2.job_id=jt.job_id
            AND       h2.execution_id=jt.execution_id
            AND       jt.target_guid=t.target_guid
            AND       e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            ORDER BY h1.execution_id, h1.step_id;

    ELSIF p_target_name IS NOT NULL AND p_status IS NULL THEN
        OPEN p_exec_targets_cursor_out FOR
            SELECT  jt.execution_id, target_name, target_type
            FROM    MGMT_JOB_EXT_TARGETS jt, MGMT_TARGETS t,
                    MGMT_JOB_EXEC_SUMMARY e
            WHERE   jt.target_guid=t.target_guid
            AND     jt.job_id=e.job_id
            AND     jt.execution_id=e.execution_id
            AND     e.job_id=l_job_id
            AND     e.scheduled_time=p_scheduled_time_out
            AND     t.target_name LIKE p_target_name
            AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            ORDER BY jt.execution_id, target_type, target_name;

        OPEN p_task_tgt_runs_cursor_out FOR
        SELECT h1.step_id, targets_for_task.target_name,
               targets_for_task.target_type, h1.execution_id
            FROM   MGMT_TARGETS targets_for_task,
                   MGMT_JOB_EXEC_SUMMARY e,
                   MGMT_JOB_HISTORY h1,
                   MGMT_JOB_HISTORY h2,
                   MGMT_JOB_EXT_TARGETS task_targets,
                   MGMT_JOB_EXT_TARGETS exec_targets,
		   MGMT_TARGETS targets_for_exec
            WHERE  e.job_id=l_job_id
            AND    e.scheduled_time=p_scheduled_time_out
            AND    e.job_id=exec_targets.job_id
            AND    e.execution_id=exec_targets.execution_id
            AND    exec_targets.target_guid=targets_for_exec.target_guid
            AND    targets_for_exec.target_name LIKE p_target_name
            AND    h1.execution_id=e.execution_id
            AND    h1.parent_step_id > 0
            AND    h1.step_type = 7
            AND    h1.step_id=h2.parent_step_id
            AND    h2.job_id=task_targets.job_id
            AND    h2.execution_id=task_targets.execution_id
            AND    task_targets.target_guid=targets_for_task.target_guid
            AND    e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            ORDER BY h1.execution_id, h1.step_id;


   ELSIF p_target_name IS NOT NULL AND p_status IS NOT NULL THEN
        OPEN p_exec_targets_cursor_out FOR
            SELECT  jt.execution_id, target_name, target_type
            FROM    MGMT_JOB_EXT_TARGETS jt, MGMT_TARGETS t,
                    MGMT_JOB_EXEC_SUMMARY e
            WHERE   jt.target_guid=t.target_guid
            AND     jt.job_id=e.job_id
            AND     jt.execution_id=e.execution_id
            AND     e.job_id=l_job_id
            AND     e.scheduled_time=p_scheduled_time_out
            AND     t.target_name like p_target_name
            AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            AND (
                 (e.status = p_status AND p_status >= 0)
                 OR
                 (e.status_bucket = p_status AND p_status <0)
                 OR
                 (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                 OR
                ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                 AND e.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                                ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                                ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                )                            
            )
            ORDER BY jt.execution_id, target_type, target_name;

        OPEN p_task_tgt_runs_cursor_out FOR
        SELECT h1.step_id, targets_for_task.target_name,
               targets_for_task.target_type,h1.execution_id
            FROM   MGMT_TARGETS targets_for_task,
                   MGMT_JOB_EXEC_SUMMARY e,
                   MGMT_JOB_HISTORY h1,
                   MGMT_JOB_HISTORY h2,
                   MGMT_JOB_EXT_TARGETS task_targets,
                   MGMT_JOB_EXT_TARGETS exec_targets,
		   MGMT_TARGETS targets_for_exec
            WHERE  e.job_id=l_job_id
            AND    e.scheduled_time=p_scheduled_time_out
            AND    e.job_id=exec_targets.job_id
            AND    e.execution_id=exec_targets.execution_id
            AND    exec_targets.target_guid=targets_for_exec.target_guid
            AND    targets_for_exec.target_name LIKE p_target_name
            AND    e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            AND    (
                     (e.status = p_status AND p_status >= 0)
                     OR
                     (e.status_bucket = p_status AND p_status <0)
                     OR
                     (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                     OR
                     ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                         AND e.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                                        ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                                        ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                        )                            
                   )
            AND    h1.execution_id=e.execution_id
            AND    h1.parent_step_id > 0
            AND    h1.step_type = 7
            AND    h1.step_id=h2.parent_step_id
            AND    h2.job_id=task_targets.job_id
            AND    h2.execution_id=task_targets.execution_id
            AND    task_targets.target_guid=targets_for_task.target_guid
            ORDER BY h1.execution_id, h1.step_id;

    ELSIF p_target_name IS NULL AND p_status IS NOT NULL THEN
        OPEN p_exec_targets_cursor_out FOR
            SELECT  jt.execution_id, target_name, target_type
            FROM    MGMT_JOB_EXT_TARGETS jt, MGMT_TARGETS t,
                    MGMT_JOB_EXEC_SUMMARY e
            WHERE   jt.target_guid=t.target_guid
            AND     jt.job_id=e.job_id
            AND     jt.execution_id=e.execution_id
            AND     e.job_id=l_job_id
            AND     e.scheduled_time=p_scheduled_time_out
            AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            AND (
                 (e.status = p_status AND p_status >= 0)
                 OR
                 (e.status_bucket = p_status AND p_status <0)
                 OR
                 (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                 OR
                ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                 AND e.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                                ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                                ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                )                            
            )
            ORDER BY jt.execution_id, target_type, target_name;

        OPEN p_task_tgt_runs_cursor_out FOR
        SELECT h1.step_id, target_name, target_type,h1.execution_id
            FROM    MGMT_TARGETS t,
                   MGMT_JOB_EXEC_SUMMARY e,
                   MGMT_JOB_HISTORY h1,
                   MGMT_JOB_HISTORY h2,
                   MGMT_JOB_EXT_TARGETS jt
            WHERE  e.job_id=l_job_id
            AND    e.scheduled_time=p_scheduled_time_out
            AND    e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
            AND    (
                        (e.status = p_status AND p_status >= 0)
                        OR
                        (e.status_bucket = p_status AND p_status <0)
                        OR
                        (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                        OR
                        ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                              AND e.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                                             ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                                             ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                        )                            
                    )
            AND       h1.execution_id=e.execution_id
            AND       h1.parent_step_id > 0
            AND       h1.step_type = 7
            AND       h1.step_id=h2.parent_step_id
            AND       h2.job_id=jt.job_id
            AND       h2.execution_id=jt.execution_id
            AND       jt.target_guid=t.target_guid
            ORDER BY h1.execution_id, h1.step_id;

   END IF;
END;

-- Helper function to get the error output ids
FUNCTION get_error_output_ids(p_job_id IN RAW, 
                              p_execution_id IN RAW)
RETURN SMP_EMD_STRING_ARRAY IS
l_error_output_ids SMP_EMD_STRING_ARRAY;
l_error_ids SMP_EMD_STRING_ARRAY;
BEGIN
    SELECT output_id BULK COLLECT INTO l_error_output_ids 
    FROM    MGMT_JOB_HISTORY
    WHERE   job_id = p_job_id
    AND        execution_id = p_execution_id
    AND        step_type != 1
    AND         step_status IN (MGMT_JOBS.ABORTED_STATUS, MGMT_JOBS.FAILED_STATUS)
    AND        (output_id IS NOT NULL);


    SELECT error_id BULK COLLECT INTO l_error_ids 
    FROM    MGMT_JOB_HISTORY
    WHERE   job_id = p_job_id
    AND        execution_id = p_execution_id
    AND        step_type != 1
    AND         step_status IN (MGMT_JOBS.ABORTED_STATUS, MGMT_JOBS.FAILED_STATUS)
    AND        (error_id IS NOT NULL);

    IF l_error_ids IS NOT NULL AND l_error_ids.COUNT > 0 THEN
       FOR i IN l_error_ids.FIRST..l_error_ids.LAST LOOP
          l_error_output_ids.extend(1);
          l_error_output_ids(l_error_output_ids.COUNT) := l_error_ids(i);
       END LOOP;
    END IF;

    RETURN l_error_output_ids;
EXCEPTION
    WHEN OTHERS THEN RETURN null;
END get_error_output_ids;

-- Get all information needed to display the run details page
PROCEDURE get_execution_details(p_execution_id IN RAW,
                          p_steps_cursor_out OUT STEPS_CURSOR,
                          p_exec_targets_cursor_out OUT EXEC_TARGETS_CURSOR,
                          p_scheduled_time_out OUT DATE,
                          p_scheduled_time_zone OUT varchar2,
                          p_job_info_out OUT JOB_INFO_CURSOR,
                          p_task_params_out  out PARAMS_CURSOR,
                          p_task_tgt_runs_cursor_out  OUT TASK_INFO_CURSOR,
                          p_job_name_out OUT VARCHAR2,
                          No_of_executions_out OUT NUMBER,
                          p_schedule_out OUT MGMT_JOB_SCHEDULE_RECORD,
                          p_job_category_out OUT NUMBER,
                          isSingleStepJob_out OUT nocopy NUMBER,
                          p_uriSource_resInc_out OUT NUMBER,
                          p_ca_reference_count OUT NUMBER,
                          p_is_corrective_action OUT NUMBER,
                          p_status OUT NUMBER,
                          p_status_code OUT NUMBER,
                          p_status_code_category OUT NUMBER,
                          p_status_detail OUT NUMBER,
                          p_job_notification_count_out OUT NUMBER,
                          p_error_output_ids OUT SMP_EMD_STRING_ARRAY,
			  p_previous_job_info_out OUT PREVIOUS_JOB_INFO,
			  p_next_job_info_out OUT NEXT_JOB_INFO
                                             ) IS

l_job_id MGMT_JOB.job_id%TYPE;
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_count NUMBER;
l_error_ids SMP_EMD_STRING_ARRAY; 
l_source_execution_id MGMT_JOB_EXEC_SUMMARY.execution_id%TYPE;
BEGIN

    BEGIN
        SELECT  e.job_id, j.job_name, scheduled_time, timezone_region,
                j.is_corrective_action, ca.ca_reference_count, 
                e.status, e.status_code, e.status_code_category, e.status_detail
        INTO    l_job_id, p_job_name_out, p_scheduled_time_out,  
                p_scheduled_time_zone, p_is_corrective_action, p_ca_reference_count,
                p_status, p_status_code, p_status_code_category, p_status_detail
        FROM    MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB j, MGMT_CORRECTIVE_ACTION ca
        WHERE   execution_id=p_execution_id
        AND     e.job_id=j.job_id
        AND     j.job_id = ca.job_id(+)
        AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_EXECUTION_ERR,
                                    'Invalid execution id');
    END;

    OPEN p_job_info_out FOR
        SELECT  job_type, job_owner, job_description,job_status,job_name
        FROM    MGMT_JOB
        WHERE   job_id=l_job_id;

    l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(l_job_id);

    SELECT  job_type_category, job_type_id  
    INTO    p_job_category_out, l_job_type_id 
    FROM    MGMT_JOB_TYPE_INFO
    WHERE   job_type_id = l_job_type_id; 

    SELECT COUNT(1) INTO p_uriSource_resInc_out FROM  MGMT_JOB_TYPE_URI_INFO 
    WHERE job_type_id = l_job_type_id 
    AND uri_use in ( 1,2,3);

    SELECT COUNT(1) INTO No_of_executions_out
    FROM   MGMT_JOB_EXEC_SUMMARY 
    WHERE  job_id=l_job_id
    AND    scheduled_time = p_scheduled_time_out
    AND    status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
    AND    ROWNUM <3;      

    get_results_schedule(l_job_id, p_schedule_out);

    --gettting the job Notification Count
    get_job_notification_count(p_execution_id,p_job_notification_count_out);

    SELECT COUNT(*) INTO l_count FROM MGMT_JOB_EXECPLAN
    WHERE   job_type_id=l_job_type_id
    AND    step_type = MGMT_JOB_ENGINE.STEPTYPE_STEP;

    IF l_count = 1 THEN
          -- Eliminate the possibility that the single step is enclosed in an iterative stepset
          SELECT COUNT(*) INTO l_count FROM MGMT_JOB_EXECPLAN
          WHERE   job_type_id = l_job_type_id
          AND     step_type IN (MGMT_JOB_ENGINE.STEPTYPE_ITSERIAL_STEPSET,
                                   MGMT_JOB_ENGINE.STEPTYPE_ITPLL_STEPSET);
          IF l_count = 0 THEN
                --Its a single Step Job
                isSingleStepJob_out := 1;
          ELSE
                --Its NOT a single Step Job
                 isSingleStepJob_out := 0;
          END IF;
    ELSE
         --Its NOT a single Step Job
         isSingleStepJob_out := 0;
    END IF;


    OPEN p_exec_targets_cursor_out FOR
        SELECT  jt.execution_id, target_name, target_type
        FROM    MGMT_JOB_EXT_TARGETS jt, MGMT_TARGETS t,
                MGMT_JOB_EXEC_SUMMARY e
        WHERE   jt.target_guid=t.target_guid
        AND     jt.job_id=e.job_id
        AND     jt.execution_id=e.execution_id
        AND     e.execution_id=p_execution_id
        AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS
        ORDER BY jt.execution_id, target_type, target_name;

    OPEN p_steps_cursor_out FOR
         SELECT h.execution_id, h.job_id, h.step_id, h.step_name, h.step_type,
                h.step_status, h.target_name, h.target_type,
                h.start_time, h.end_time, h.parent_step_id, h.timezone_region,
                h.run_time_seconds, ep.step_nlsid, ep.step_default, 
                jt.job_type
         FROM   MGMT_JOB_EXECPLAN ep,
                MGMT_JOB_TYPE_INFO jt,
                (SELECT h.execution_id, h.job_id, h.step_id, h.step_name, h.step_type,
                        h.step_status, t.target_name, t.target_type,
                        h.start_time, h.end_time, h.parent_step_id,h.timezone_region,
                        (( DECODE ( h.end_time, NULL, MGMT_GLOBAL.sysdate_UTC,
                                    h.end_time ) -h.start_time)*24*60*60) 
                        run_time_seconds,
                        DECODE(j.nested_job_type_id, NULL, s.job_type_id, 
                               j.nested_job_type_id) job_type_id
                 FROM   MGMT_JOB_HISTORY h,
                        MGMT_JOB_STEP_TARGETS jst,
                        MGMT_TARGETS t,
                        MGMT_JOB_EXEC_SUMMARY s,
                        MGMT_JOB j
                 WHERE  s.execution_id = HEXTORAW(p_execution_id)
                 AND    h.execution_id = s.execution_id
                 AND    h.job_id       = j.job_id
                 AND    h.step_id      = jst.step_id (+)
                 AND    jst.target_guid= t.target_guid (+)) h
          WHERE  jt.job_type_id = h.job_type_id
          AND    h.job_type_id = ep.job_type_id (+)
          AND    h.step_name = ep.step_name (+)
          AND    h.step_type = ep.step_type (+)
          ORDER BY execution_id,  step_id;


   OPEN p_task_params_out FOR
        SELECT  jp.job_id,jp.parameter_name, parameter_type, encrypted,
                MGMT_JOB_ENGINE.decrypt_scalar(encrypted, scalar_value) scalarvalue,
                MGMT_JOB_ENGINE.decrypt_vector(encrypted, vector_value) vectorvalue ,label_nlsid ,label_default,jp.LARGE_VALUE
        FROM    MGMT_JOB_PARAMETER jp, MGMT_JOB j, 
                MGMT_JOB_TYPE_MAX_VERSIONS mv,
                MGMT_JOB_TYPE_PARAM_DSPLY_INFO i
        WHERE   jp.job_id=l_job_id 
        AND     execution_id = p_execution_id
        AND     jp.job_id=j.job_id
        AND     j.job_type=mv.job_type
        AND     j.job_type_major_version=mv.major_version
        AND     mv.job_type_id=i.job_type_id
        AND     jp.parameter_name=i.parameter_name
        AND     i.show_in_results=1;

     --To get all task targets in an execution:
    OPEN p_task_tgt_runs_cursor_out FOR
        SELECT h1.step_id, target_name, target_type,h1.execution_id
        FROM    MGMT_TARGETS t,
                MGMT_JOB_HISTORY h1,
                MGMT_JOB_HISTORY h2,
                MGMT_JOB_EXT_TARGETS jt
        WHERE   h1.execution_id = p_execution_id
        AND     h1.parent_step_id > 0
        AND     h1.step_type= 7
        AND     h1.step_id=h2.parent_step_id
        AND     h2.job_id=jt.job_id
        AND     h2.execution_id=jt.execution_id
        AND     jt.target_guid=t.target_guid
        ORDER BY h1.execution_id, h1.step_id;
  



    p_error_output_ids := get_error_output_ids(l_job_id, p_execution_id);

	--now retrive the source execution id 
	SELECT JobSumm.source_execution_id into l_source_execution_id 
	from  MGMT_JOB_EXEC_SUMMARY JobSumm 
	Where JobSumm.execution_id=HEXTORAW( p_execution_id ) 
	AND JobSumm.status NOT IN ( MGMT_JOB_ENGINE.WAITING_STATUS ,MGMT_JOB_ENGINE.DELETE_PENDING_STATUS );
	
	--now retrive the fetch the previous execution data
	IF l_source_execution_id IS NOT NULL AND l_source_execution_id <> p_execution_id then 
	     OPEN  p_previous_job_info_out FOR 
		SELECT JobSumm.execution_id ,JobSumm.scheduled_time, 
		JobSumm.timezone_region 
		FROM  MGMT_JOB_EXEC_SUMMARY JobSumm 
		WHERE JobSumm.execution_id=HEXTORAW( l_source_execution_id ) 
		AND JobSumm.status NOT IN ( MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.DELETE_PENDING_STATUS ) ;
	ELSE
	     --OPEN A DUMMY CURSOR THAT WILL RETURN NO ROWS to PREVENT EXCEPTION IN JAVA
	     OPEN  p_previous_job_info_out FOR 
		SELECT JobSumm.execution_id ,JobSumm.scheduled_time, 
		JobSumm.timezone_region 
		FROM  MGMT_JOB_EXEC_SUMMARY JobSumm 
		WHERE 1 = 2;
	END IF;

        OPEN p_next_job_info_out for 
	SELECT JobSumm.execution_id retried_next_execution_id,JobSumm.scheduled_time,
	JobSumm.timezone_region  
	FROM MGMT_JOB_EXEC_SUMMARY JobSumm  
	WHERE JobSumm.source_execution_id != JobSumm.execution_id  
	AND JobSumm.source_execution_id = p_execution_id 
	AND JobSumm.status NOT IN ( MGMT_JOB_ENGINE.WAITING_STATUS, MGMT_JOB_ENGINE.DELETE_PENDING_STATUS ) ;


END;


-- Return task details
-- Get all information needed to display the task details page
PROCEDURE get_task_details(p_task_step_id IN NUMBER,
                          p_steps_cursor_out OUT STEPS_CURSOR,
                          p_task_targets_cursor_out OUT EXEC_TARGETS_CURSOR,
                          p_scheduled_time_out OUT DATE,
                          p_scheduled_time_zone OUT varchar2,
                          p_job_info_out OUT JOB_INFO_CURSOR,
                          p_task_params_out  out PARAMS_CURSOR,
                          p_task_tgt_runs_cursor_out  OUT TASK_INFO_CURSOR,
                          p_job_name_out OUT VARCHAR2,
                          p_schedule_out OUT MGMT_JOB_SCHEDULE_RECORD,
                          p_job_category_out OUT NUMBER,
                          p_status OUT NUMBER,
                          p_status_code OUT NUMBER,
                          p_status_code_category OUT NUMBER,
                          p_status_detail OUT NUMBER,
                          p_error_output_ids OUT SMP_EMD_STRING_ARRAY,
                          p_is_corrective_action OUT NUMBER
                          ) IS

l_job_id MGMT_JOB.job_id%TYPE;
l_top_level_job_id MGMT_JOB.job_id%TYPE;
l_execution_id MGMT_JOB_EXEC_SUMMARY.execution_id%TYPE;
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_error_ids SMP_EMD_STRING_ARRAY; 
BEGIN
    -- The job id associated with the entry is really the job id of the parent job.
    -- To get information about the child job, get the job id from one of the
    -- child steps of the nested job entry
    BEGIN
        SELECT  execution_id, job_id
        INTO    l_execution_id, l_job_id
        FROM    MGMT_JOB_HISTORY
        WHERE   parent_step_id = p_task_step_id
        AND     rownum=1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                                    'Invalid execution id');
    END;

    BEGIN
        SELECT  j.job_name, scheduled_time, timezone_region,
                e.status, e.status_code, e.status_code_category , e.status_detail,
                j.is_corrective_action
        INTO    p_job_name_out, p_scheduled_time_out, p_scheduled_time_zone, 
                p_status, p_status_code, p_status_code_category, p_status_detail,
                p_is_corrective_action
        FROM    MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB j
        WHERE   execution_id=l_execution_id
        AND     e.job_id=j.job_id ;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
    END;


    get_results_schedule(l_top_level_job_id, p_schedule_out);

    OPEN p_job_info_out FOR
        SELECT  job_type, job_owner, job_description,job_status, job_name
        FROM    MGMT_JOB
        WHERE   job_id=l_job_id;

    l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(l_job_id);

    SELECT  job_type_category
    INTO    p_job_category_out
    FROM    MGMT_JOB_TYPE_INFO
    WHERE   job_type_id = l_job_type_id; 

   OPEN p_task_targets_cursor_out FOR
        SELECT  jt.execution_id, target_name, target_type
        FROM    MGMT_JOB_EXT_TARGETS jt, MGMT_TARGETS t
        WHERE   jt.target_guid=t.target_guid
        AND     jt.job_id=l_job_id
        AND     jt.execution_id=l_execution_id
        ORDER BY jt.execution_id, target_type, target_name;

   OPEN p_steps_cursor_out FOR
        SELECT h.execution_id, h.job_id, h.step_id, h.step_name, h.step_type,
               h.step_status, h.target_name, h.target_type,
               h.start_time, h.end_time, h.parent_step_id, h.timezone_region,
               h.run_time_seconds, ep.step_nlsid, ep.step_default, jt.job_type
        FROM   MGMT_JOB_EXECPLAN ep,
               MGMT_JOB_TYPE_INFO jt,
               (SELECT h.execution_id, h.job_id, h.step_id, h.step_name, h.step_type,
                       h.step_status,t.target_name, t.target_type,
                       h.start_time, h.end_time, h.parent_step_id,h.timezone_region,
                       (( DECODE ( h.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h.end_time ) -h.start_time)*24*60*60) run_time_seconds,
                        DECODE(j.nested_job_type_id, NULL, s.job_type_id, j.nested_job_type_id) job_type_id
                FROM   MGMT_JOB_HISTORY h,
                       MGMT_JOB_STEP_TARGETS jst,
                       MGMT_TARGETS t,
                       MGMT_JOB_EXEC_SUMMARY s,
                       MGMT_JOB j
                WHERE  s.execution_id=l_execution_id
                AND    h.execution_id=s.execution_id
                AND    h.job_id = j.job_id
                AND    h.step_id = jst.step_id (+)
                AND    jst.target_guid=t.target_guid (+)
                START WITH h.step_id = p_task_step_id
                CONNECT BY PRIOR h.step_id = h.parent_step_id) h
        WHERE  jt.job_type_id = h.job_type_id
        AND    h.job_type_id = ep.job_type_id (+)
        AND    h.step_name = ep.step_name (+)
        AND    h.step_type = ep.step_type (+)
        ORDER BY execution_id,  step_id;

   OPEN p_task_params_out FOR
        SELECT  jp.job_id,jp.parameter_name, parameter_type, encrypted,
                MGMT_JOB_ENGINE.decrypt_scalar(encrypted, scalar_value) scalarvalue,
                MGMT_JOB_ENGINE.decrypt_vector(encrypted, vector_value) vectorvalue ,label_nlsid ,label_default,jp.LARGE_VALUE
        FROM    MGMT_JOB_PARAMETER jp, MGMT_JOB j, 
                MGMT_JOB_TYPE_MAX_VERSIONS mv,
                MGMT_JOB_TYPE_PARAM_DSPLY_INFO i
        WHERE   jp.job_id=l_job_id 
        AND     execution_id = l_execution_id
        AND     jp.job_id=j.job_id
        AND     j.job_type=mv.job_type
        AND     j.job_type_major_version=mv.major_version
        AND     mv.job_type_id=i.job_type_id
        AND     jp.parameter_name=i.parameter_name
        AND     i.show_in_results=1;

     --To get all task targets in an execution:
    OPEN p_task_tgt_runs_cursor_out FOR
        SELECT h1.step_id, target_name, target_type,h1.execution_id
        FROM    MGMT_TARGETS t,
                       MGMT_JOB_HISTORY h1,
                       MGMT_JOB_HISTORY h2,
                       MGMT_JOB_EXT_TARGETS jt
        WHERE
                  h1.execution_id = l_execution_id
        AND       h1.parent_step_id > 0
        AND       h1.step_type= 7
        AND       h1.step_id=h2.parent_step_id
        AND       h2.job_id=jt.job_id
        AND       h2.execution_id=jt.execution_id
        AND       jt.target_guid=t.target_guid
        ORDER BY h1.execution_id, h1.step_id;

    p_error_output_ids := get_error_output_ids(l_job_id, l_execution_id);
END;

PROCEDURE get_single_step_job_details(p_execution_id IN RAW,
                          p_target_name IN VARCHAR2,
                          p_status IN NUMBER,
                          isSingleStepJob_out OUT nocopy NUMBER,
                          p_scheduled_time_out OUT DATE,
                          p_scheduled_time_zone_out OUT VARCHAR2,
                          p_job_info_out OUT TASK_INFO_CURSOR,
                          p_steps_cursor_out OUT STEPS_CURSOR,
                          p_job_name_out OUT VARCHAR2,
                          No_of_executions_out OUT NUMBER,
                          p_schedule_out OUT MGMT_JOB_SCHEDULE_RECORD,
                          p_uriSource_resInc_out OUT NUMBER,
                          p_job_category_out OUT NUMBER,
                          p_submitted_targets_string_out OUT VARCHAR2,
                          p_ca_reference_count OUT NUMBER,
                          p_is_corrective_action OUT NUMBER
                          ) IS

l_job_id MGMT_JOB.job_id%TYPE;
l_count NUMBER;
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;
l_submitted_targets_count NUMBER;


BEGIN
    BEGIN

        SELECT  e.job_id, scheduled_time, timezone_region, job_name,
                j.is_corrective_action, ca_reference_count
        INTO    l_job_id, p_scheduled_time_out, 
                p_scheduled_time_zone_out, p_job_name_out,
                p_is_corrective_action, p_ca_reference_count
        FROM    MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB j, MGMT_CORRECTIVE_ACTION ca
        WHERE   execution_id=p_execution_id
        AND     e.job_id=j.job_id
        AND     j.job_id=ca.job_id(+)
        AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS;
     EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_EXECUTION_ERR,
                                    'Invalid execution id');
     END;

    get_submitted_targets (l_job_id ,p_submitted_targets_string_out);

    -- Figure out whether the job is targetless or not...
    SELECT  COUNT(*) INTO l_submitted_targets_count
    FROM    MGMT_JOB_EXT_TARGETS
    WHERE   job_id=l_job_id
    AND     ROWNUM=1;

    l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(l_job_id);

    get_results_schedule(l_job_id, p_schedule_out);
    
    SELECT  job_type_category
    INTO    p_job_category_out
    FROM    MGMT_JOB_TYPE_INFO
    WHERE   job_type_id = l_job_type_id;

    SELECT COUNT(1) INTO p_uriSource_resInc_out 
    FROM   MGMT_JOB_TYPE_URI_INFO 
    WHERE  job_type_id = l_job_type_id 
    AND    uri_use in ( 1,2,3);

    SELECT COUNT(1) INTO No_of_executions_out
    FROM    MGMT_JOB_EXEC_SUMMARY 
    WHERE  job_id=l_job_id
    AND    scheduled_time = p_scheduled_time_out
    AND ROWNUM<3;

    SELECT COUNT(*) INTO l_count FROM MGMT_JOB_EXECPLAN
    WHERE   job_type_id=l_job_type_id
    AND    step_type=MGMT_JOB_ENGINE.STEPTYPE_STEP;

    IF l_count = 1 THEN
        -- Eliminate the possibility that the single step is enclosed 
        -- in an iterative stepset
        SELECT  COUNT(*) INTO l_count FROM MGMT_JOB_EXECPLAN
        WHERE   job_type_id = l_job_type_id
        AND     step_type IN (MGMT_JOB_ENGINE.STEPTYPE_ITSERIAL_STEPSET,
                              MGMT_JOB_ENGINE.STEPTYPE_ITPLL_STEPSET);

        IF l_count = 0 THEN
            --Its a single Step Job
            isSingleStepJob_out := 1;
        ELSE
             --Its NOT a single Step Job
             isSingleStepJob_out := 0;
        END IF;
    ELSE
         --Its NOT a single Step Job
         isSingleStepJob_out := 0;
    END IF;

    ---WE NEED to QUERY FURTHER IF ONLY ITS A SINGLE STEP JOB ELSE JUST RETURN
    IF isSingleStepJob_out = 1 THEN

        OPEN p_job_info_out FOR
            SELECT  job_type, job_owner, job_description, job_status, job_name
            FROM    MGMT_JOB
            WHERE   job_id=l_job_id;

        IF p_target_name IS NULL AND p_status IS NULL THEN
            IF l_submitted_targets_count=0 THEN
                -- Targetless job
                OPEN p_steps_cursor_out FOR
                    SELECT   h.execution_id, h.step_id, h.step_name,
                             h.status "STEP_STATUS",
                             h1.start_time "START_TIME", 
                             h1.end_time,  target_name, target_type,
                             output,
                             h.timezone_region,
                            (( DECODE ( h1.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h1.end_time ) - h1.start_time)*24*60*60) run_time_seconds ,
                            'dummy' as step_nlsid
                   FROM      MGMT_JOB_HISTORY h1,
                            (SELECT    e.job_id, e.execution_id, 
                                       e.status, e.timezone_region,
                                       step_name, step_type, step_id,
                                       NVL(error_id, NVL(output_id, MGMT_JOB_ENGINE.NO_GUID)) "OUTPUT_ID"
                             FROM      MGMT_JOB_HISTORY h2, MGMT_JOB_EXEC_SUMMARY e
                             WHERE     e.job_id=l_job_id
                             AND       e.scheduled_time = p_scheduled_time_out
                             AND       e.execution_id=h2.execution_id (+)
                             AND       MGMT_JOB_ENGINE.STEPTYPE_STEP=
                                            h2.step_type (+)) h,
                             MGMT_JOB_TARGET jt,
                             MGMT_TARGETS t,
                             MGMT_JOB_OUTPUT o
                   WHERE     h.execution_id=h1.execution_id (+) 
                   AND       MGMT_JOB_ENGINE.STEPTYPE_JOB=h1.step_type (+)                   
                   AND       h.output_id=o.output_id (+)
                   AND       h.job_id=jt.job_id (+)
                   AND       h.execution_id=jt.execution_id (+)
                   AND       jt.target_guid=t.target_guid (+)
                   ORDER BY step_id DESC;
            ELSE
                OPEN p_steps_cursor_out FOR
                    SELECT   h.execution_id, h.step_id, h.step_name,
                             h.status "STEP_STATUS",
                             h1.start_time "START_TIME", 
                             h1.end_time,  target_name, target_type,
                             output,
                             h.timezone_region,
                            (( DECODE ( h1.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h1.end_time ) -h1.start_time)*24*60*60) run_time_seconds ,
                            'dummy' as step_nlsid
                   FROM      MGMT_JOB_HISTORY h1,
                             (SELECT    e.job_id, e.execution_id, 
                                        e.status, e.timezone_region,
                                        step_name, step_type, step_id,
                                        NVL(error_id, output_id) "OUTPUT_ID"
                              FROM      MGMT_JOB_HISTORY h2, MGMT_JOB_EXEC_SUMMARY e
                              WHERE     e.job_id=l_job_id
                              AND       e.scheduled_time = p_scheduled_time_out
                              AND       e.execution_id=h2.execution_id (+)
                              AND       MGMT_JOB_ENGINE.STEPTYPE_STEP=
                                            h2.step_type (+)) h,
                             MGMT_JOB_TARGET jt,
                             MGMT_TARGETS t, 
                             MGMT_JOB_OUTPUT o
                   WHERE     h.execution_id=h1.execution_id (+)
                   AND       MGMT_JOB_ENGINE.STEPTYPE_JOB=h1.step_type (+)
                   AND       h.output_id=o.output_id (+)
                   AND       h.job_id=jt.job_id
                   AND       h.execution_id=jt.execution_id
                   AND       jt.target_guid=t.target_guid
                   ORDER BY step_id DESC;
            END IF;           
        ELSIF p_target_name IS NOT NULL AND p_status IS NULL THEN
            OPEN p_steps_cursor_out FOR
                  SELECT    h.execution_id, h.step_id, h.step_name, 
                            h.status "STEP_STATUS",
                            h1.start_time "START_TIME", 
                            h1.end_time,  
                            target_name, target_type, output,
                            h.timezone_region,
                            (( DECODE ( h1.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h1.end_time ) -h1.start_time)*24*60*60) run_time_seconds ,
                            'dummy' as step_nlsid
                  FROM      MGMT_JOB_HISTORY h1,
                            (SELECT    e.job_id, e.execution_id, 
                                       e.status, e.timezone_region,
                                       step_name, step_type, step_id,
                                       NVL(error_id, output_id) "OUTPUT_ID"
                             FROM      MGMT_JOB_HISTORY h2, MGMT_JOB_EXEC_SUMMARY e
                             WHERE     e.job_id=l_job_id
                             AND       e.scheduled_time = p_scheduled_time_out
                             AND       e.execution_id=h2.execution_id (+)
                             AND       MGMT_JOB_ENGINE.STEPTYPE_STEP=
                                            h2.step_type (+)) h,
                            MGMT_JOB_TARGET jt,
                            MGMT_TARGETS t,
                            MGMT_JOB_OUTPUT o
                  WHERE     h.execution_id=h1.execution_id (+)
                  AND       MGMT_JOB_ENGINE.STEPTYPE_JOB=h1.step_type (+)                  
                  AND       h.output_id=o.output_id (+)
                  AND       h.job_id=jt.job_id (+)
                  AND       h.execution_id=jt.execution_id (+)
                  AND       jt.target_guid=t.target_guid (+)
                  AND       t.target_name LIKE p_target_name
                  ORDER BY step_id DESC;

        ELSIF p_target_name IS NOT NULL AND p_status IS NOT NULL THEN
            OPEN p_steps_cursor_out FOR
                SELECT  h.execution_id, h.step_id, h.step_name,
                        h.status "STEP_STATUS",
                        h1.start_time "START_TIME", 
                        h1.end_time,  
                        target_name, target_type, output,
                        h.timezone_region,
                        (( DECODE ( h1.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h1.end_time ) -h1.start_time)*24*60*60) run_time_seconds ,
                        'dummy' as step_nlsid
                FROM    MGMT_JOB_HISTORY h1,
                       (SELECT    e.job_id, e.execution_id, e.status_bucket,
                                  e.status, e.timezone_region,
                                  step_name, step_type, step_id,
                                  NVL(error_id, output_id) "OUTPUT_ID"
                        FROM      MGMT_JOB_HISTORY h2, MGMT_JOB_EXEC_SUMMARY e
                        WHERE     e.job_id=l_job_id
                        AND       e.scheduled_time = p_scheduled_time_out
                        AND       e.execution_id=h2.execution_id (+)
                        AND       MGMT_JOB_ENGINE.STEPTYPE_STEP=
                                            h2.step_type (+)) h,
                        MGMT_JOB_TARGET jt,
                        MGMT_TARGETS t,
                        MGMT_JOB_OUTPUT o
                WHERE   h.execution_id=h1.execution_id (+) 
                AND     MGMT_JOB_ENGINE.STEPTYPE_JOB=h1.step_type (+)                
                AND     t.target_name LIKE p_target_name
                AND     h.output_id=o.output_id (+)
                AND     h.job_id=jt.job_id (+)
                AND     h.execution_id=jt.execution_id (+)
                AND     jt.target_guid=t.target_guid (+)
                AND     (
                         (h.status = p_status AND p_status >= 0)
                         OR
                         (h.status_bucket = p_status AND p_status <0)
                         OR
                         (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                         OR
                        ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                         AND h.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                                        ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                                        ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                        )                            
                       )
                ORDER BY step_id DESC;

        ELSIF p_target_name IS NULL AND p_status IS NOT NULL THEN
            IF l_submitted_targets_count=0 THEN
                OPEN p_steps_cursor_out FOR
                    SELECT  h.execution_id, h.step_id, h.step_name,
                            h.status "STEP_STATUS",
                            h1.start_time "START_TIME", 
                            h1.end_time,  
                            t.target_name, t.target_type, o.output,
                            h.timezone_region,
                            (( DECODE ( h1.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h1.end_time ) -h1.start_time)*24*60*60) run_time_seconds ,
                        'dummy' as step_nlsid
                    FROM    MGMT_JOB_HISTORY h1,
                            (SELECT   e.job_id, e.execution_id, e.status_bucket,
                                      e.status, e.timezone_region,
                                      h2.step_name, h2.step_type, h2.step_id,
                                      NVL(h2.error_id, h2.output_id) "OUTPUT_ID"
                            FROM      MGMT_JOB_HISTORY h2, MGMT_JOB_EXEC_SUMMARY e
                            WHERE     e.job_id=l_job_id
                            AND       e.scheduled_time = p_scheduled_time_out
                            AND       e.execution_id=h2.execution_id (+)
                            AND       MGMT_JOB_ENGINE.STEPTYPE_STEP=
                                            h2.step_type (+)) h,
                            MGMT_JOB_TARGET jt,
                            MGMT_TARGETS t,
                            MGMT_JOB_OUTPUT o
                    WHERE   h.execution_id=h1.execution_id  (+)  
                    AND     MGMT_JOB_ENGINE.STEPTYPE_JOB=h1.step_type (+)
                    AND     h.output_id=o.output_id (+)
                    AND     h.job_id=jt.job_id (+)
                    AND     h.execution_id=jt.execution_id (+)
                    AND     jt.target_guid=t.target_guid (+)
                    AND      (
                                  (h.status = p_status AND p_status >= 0)
                                  OR
                                  (h.status_bucket = p_status AND p_status <0)
                                  OR
                                  (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                                  OR
                                  ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                                  AND h.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                                                 ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                                                 ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                                    )                            
                          )
                    ORDER BY step_id DESC;
            ELSE
                OPEN p_steps_cursor_out FOR
                    SELECT  h.execution_id, h.step_id, h.step_name,
                            h.status "STEP_STATUS",
                            h1.start_time "START_TIME", 
                            h1.end_time,  
                            target_name, target_type, output,
                            h.timezone_region,
                            (( DECODE ( h1.end_time, NULL, MGMT_GLOBAL.sysdate_UTC, h1.end_time ) -h1.start_time)*24*60*60) run_time_seconds ,
                            'dummy' as step_nlsid
                    FROM     MGMT_JOB_HISTORY h1,
                             (SELECT   e.job_id, e.execution_id, e.status_bucket,
                                       e.status, e.timezone_region,
                                       step_name, step_type, step_id,
                                       NVL(error_id, output_id) "OUTPUT_ID"
                              FROM     MGMT_JOB_HISTORY h2, MGMT_JOB_EXEC_SUMMARY e
                              WHERE     e.job_id=l_job_id
                              AND       e.scheduled_time = p_scheduled_time_out
                              AND       e.execution_id=h2.execution_id (+)
                              AND       MGMT_JOB_ENGINE.STEPTYPE_STEP=
                                            h2.step_type (+)) h,
                             MGMT_JOB_TARGET jt,
                             MGMT_TARGETS t,
                             MGMT_JOB_OUTPUT o
                    WHERE    h.execution_id=h1.execution_id (+) 
                    AND      MGMT_JOB_ENGINE.STEPTYPE_JOB=h1.step_type (+) 
                    AND      h.output_id=o.output_id (+)
                    AND      h.job_id=jt.job_id
                    AND      h.execution_id=jt.execution_id
                    AND      jt.target_guid=t.target_guid
                    AND      (
                                  (h.status = p_status AND p_status >= 0)
                                  OR
                                  (h.status_bucket = p_status AND p_status <0)
                                  OR
                                  (NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ALL) = MGMT_JOB_ENGINE.STATUS_BUCKET_ALL)
                                  OR
                                  ((NVL(p_status, MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE) = MGMT_JOB_ENGINE.STATUS_BUCKET_ACTIVE)
                                  AND h.status_bucket in ( MGMT_JOB_ENGINE.STATUS_BUCKET_SCHEDULED
                                                                 ,MGMT_JOB_ENGINE.STATUS_BUCKET_RUNNING
                                                                 ,MGMT_JOB_ENGINE.STATUS_BUCKET_SUSPENDED)
                                    )                            
                              )
                    ORDER BY step_id DESC;

            END IF;
        END IF;
    END IF;
END;


PROCEDURE get_task_breadcrumbs(p_execution_id IN RAW,
                          p_task_step_id IN NUMBER,
                          job_name_out out varchar2,
                          p_task_bredcrums_out OUT TASK_INFO_CURSOR,
                          No_of_executions_out OUT NUMBER,
                          p_uriSource_resInc_out OUT NUMBER,
                          p_job_category_out OUT NUMBER,
                          isSingleStepJob_out OUT nocopy NUMBER,
                          p_execution_target OUT SMP_EMD_STRING_ARRAY,
                          p_is_corrective_action_out OUT NUMBER
                              ) IS
l_job_id MGMT_JOB.job_id%TYPE;
l_count NUMBER;
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;



   BEGIN

     OPEN p_task_bredcrums_out FOR
      		SELECT    step_id, step_name,execution_id,STEP_TYPE
                FROM       MGMT_JOB_HISTORY h
                WHERE     h.step_type in(7,1)
                AND          execution_id = p_execution_id
                START WITH  h.step_id= p_task_step_id
                CONNECT BY h.step_id=prior parent_step_id
                ORDER BY level desc;

                SELECT job_name, is_corrective_action  into job_name_out,
                                             p_is_corrective_action_out
                FROM    MGMT_JOB j, MGMT_JOB_EXEC_SUMMARY e
                WHERE  j.job_id=e.job_id
                AND      e.execution_id= p_execution_id
                AND rownum = 1;
        BEGIN

        SELECT  job_id
        INTO    l_job_id
        FROM    MGMT_JOB_EXEC_SUMMARY
        WHERE   execution_id=p_execution_id;

     EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                                    'Invalid execution id');
     END;

    l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(l_job_id);
    SELECT  job_type_category
    INTO    p_job_category_out
    FROM    MGMT_JOB_TYPE_INFO
    WHERE   job_type_id = l_job_type_id;

    SELECT COUNT(1) INTO p_uriSource_resInc_out from  MGMT_JOB_TYPE_URI_INFO 
    where job_type_id = l_job_type_id 
    and uri_use in ( 1,2,3);      

        SELECT   target_name bulk collect 
        into     p_execution_target
        FROM     MGMT_JOB_EXT_TARGETS jt, 
                 MGMT_TARGETS t
        WHERE    jt.target_guid=t.target_guid
        AND      jt.job_id= l_job_id
        AND      jt.execution_id= p_execution_id;
    

      get_execution_count(p_execution_id,No_of_executions_out);

        --getting the Single Ste[p Job Information
         SELECT COUNT(*) INTO l_count FROM MGMT_JOB_EXECPLAN
        WHERE   job_type_id=l_job_type_id
        AND    step_type = MGMT_JOB_ENGINE.STEPTYPE_STEP;

        IF l_count = 1 THEN
              -- Eliminate the possibility that the single step is enclosed in an iterative stepset
              SELECT COUNT(*) INTO l_count FROM MGMT_JOB_EXECPLAN
              WHERE   job_type_id = l_job_type_id
              AND     step_type IN (MGMT_JOB_ENGINE.STEPTYPE_ITSERIAL_STEPSET,
                                   MGMT_JOB_ENGINE.STEPTYPE_ITPLL_STEPSET);
              IF l_count = 0 THEN
                    --Its a single Step Job
                    isSingleStepJob_out := 1;
              ELSE
                    --Its NOT a single Step Job
                     isSingleStepJob_out := 0;
              END IF;
        ELSE
                    --Its NOT a single Step Job
                     isSingleStepJob_out := 0;

        END IF;

end;

PROCEDURE get_execution_count(p_execution_id IN RAW,
                                      No_of_executions_out OUT NUMBER
                                     ) IS

l_job_id MGMT_JOB.job_id%TYPE;
l_scheduled_time  DATE;
l_count NUMBER;
BEGIN

        BEGIN

        SELECT  job_id,scheduled_time 
        INTO    l_job_id, l_scheduled_time
        FROM    MGMT_JOB_EXEC_SUMMARY
        WHERE   execution_id=p_execution_id
        AND     status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS;

     EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_EXECUTION_ERR,
                                    'Invalid execution id');
     end;

        SELECT COUNT(1) INTO No_of_executions_out
        FROM    MGMT_JOB_EXEC_SUMMARY 
        WHERE  job_id=l_job_id
        AND       scheduled_time = l_scheduled_time
        AND ROWNUM <3;
end;

PROCEDURE get_results_schedule(p_job_id IN RAW,
                               p_schedule_out OUT MGMT_JOB_SCHEDULE_RECORD) is 
BEGIN
    SELECT  MGMT_JOB_SCHEDULE_RECORD(frequency_code, start_time, end_time,
                                     start_grace_period, execution_hours, execution_minutes,
                                     interval, months, days, timezone_info,
                                     timezone_target_index,
                                     timezone_offset, timezone_region)
    INTO    p_schedule_out
    FROM    MGMT_JOB_SCHEDULE s, MGMT_JOB j 
    WHERE   s.schedule_id=j.schedule_id 
    AND     j.job_id = p_job_id;

EXCEPTION 
    WHEN NO_DATA_FOUND THEN
        NULL;
END;

FUNCTION is_aggregate_type(p_target_type IN VARCHAR2)
    RETURN NUMBER IS
    l_return NUMBER := 0;
BEGIN
    SELECT COUNT(*) INTO l_return
    FROM MGMT_TYPE_PROPERTIES
    WHERE target_type = p_target_type
      AND property_name = 'is_aggregate'
      AND property_value = 1;
    
    RETURN l_return;
END;


-- Get the targetsName if Number of targets == 1 
-- else return the targets count against which job is submitted
-- p_job_id The job is of the Job
-- String will contain targets name is count ==1 else targets count
procedure get_submitted_targets(p_job_id IN RAW,
                                p_targets_info_out OUT VARCHAR2) is 
 l_submitted_targets_count NUMBER := 0;
BEGIN
    -- The join with MGMT_TARGETS is needed in order to enforce
    -- security (view privs)
    SELECT  COUNT(distinct jt.target_guid) into l_submitted_targets_count 
    FROM    MGMT_JOB_TARGET jt, MGMT_TARGETS t
    WHERE   job_id = p_job_id
    AND     jt.target_guid=t.target_guid
    AND     execution_id = '0000000000000000';

    IF l_submitted_targets_count = 1 THEN
        -- Return the actual target name
        SELECT  distinct Targets.target_name into p_targets_info_out 
        FROM    MGMT_JOB_TARGET jobTargets, MGMT_TARGETS Targets
        WHERE   jobTargets.job_id = p_job_id
        AND     jobTargets.execution_id = '0000000000000000'
        AND     jobTargets.target_guid = Targets.target_guid;
    ELSE
        p_targets_info_out := l_submitted_targets_count;
    END IF;
END;



-- Get the custom data
procedure get_custom_data(p_job_id IN RAW,
                          p_target_count OUT NUMBER,
                          p_targets_info_out OUT VARCHAR2,                          
                          p_job_type OUT VARCHAR2,
                          p_delete_text_default OUT VARCHAR2,
                          p_delete_text_nlsid OUT VARCHAR2) is 
 l_submitted_targets_count NUMBER := 0;
BEGIN
    SELECT  COUNT(distinct target_guid) into l_submitted_targets_count 
    FROM    MGMT_JOB_TARGET
    WHERE   job_id = p_job_id
    AND     execution_id = '0000000000000000';

    IF l_submitted_targets_count = 1 THEN
        -- Return the actual target name
        SELECT  distinct Targets.target_name into p_targets_info_out 
        FROM    MGMT_JOB_TARGET jobTargets, MGMT_TARGETS Targets
        WHERE   jobTargets.job_id = p_job_id
        AND     jobTargets.execution_id = '0000000000000000'
        AND     jobTargets.target_guid = Targets.target_guid;
    ELSE
        p_targets_info_out := l_submitted_targets_count;
    END IF;
    
    p_target_count := l_submitted_targets_count;

    SELECT jobinfo.JOB_TYPE, jobinfo.DELETE_TEXT_DEFAULT, jobinfo.DELETE_TEXT_NLSID
    INTO p_job_type, p_delete_text_default, p_delete_text_nlsid
    FROM MGMT_JOB_TYPE_INFO jobinfo, MGMT_JOB_TYPE_MAX_VERSIONS mv, MGMT_JOB job
    WHERE job.job_type=mv.job_type
    AND   job.job_type_major_version=mv.major_version
    AND   mv.job_type_id=jobinfo.job_type_id
    AND   job.JOB_ID = p_job_id;
END;  

procedure allow_step_kill(p_step_id IN NUMBER,
                          p_status_ok OUT NUMBER,
                          p_access_ok OUT NUMBER,
                          p_agent_ok OUT NUMBER) is

l_step_status  MGMT_JOB_HISTORY.step_status%TYPE;
l_current_user VARCHAR2(256) := MGMT_USER.GET_CURRENT_EM_USER;
l_job_id   MGMT_JOB.job_id%type;
l_emd_url  MGMT_JOB_EXECUTION.emd_url%type;

BEGIN
    IF EMDW_LOG.p_is_debug_set THEN
      EMDW_LOG.debug('Validating step kill for step: ' || p_step_id, MODULE_NAME);
    END IF;
    
    --initialize output variables
    p_status_ok := -1;
    p_access_ok := -1;
    p_agent_ok := -1;
    
    --check status
    BEGIN
        SELECT step_status, job_id
        INTO l_step_status, l_job_id
        FROM MGMT_JOB_HISTORY
        WHERE step_id=p_step_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_STEP_ERR,
                 'The specified step does not exist');
    END;

    IF l_step_status = MGMT_JOB_ENGINE.EXECUTING_STATUS THEN
        p_status_ok := 1;
    ELSE
        p_status_ok := 0;
        RETURN;
    END IF;
    
    --check access
    BEGIN
        MGMT_JOB_ENGINE.check_modify_step(p_step_id);
        p_access_ok := 1;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_STEP_ERR,
                 'The specified step does not exist');
        WHEN OTHERS THEN
            p_access_ok := 0;
    END;
            
    IF p_access_ok < 1 THEN
        RETURN;
    END IF;
    
    --check EMD URL
    BEGIN
        SELECT emd_url
        INTO l_emd_url
        FROM MGMT_JOB_EXECUTION
        WHERE step_id=p_step_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            p_agent_ok := 0;
            RETURN;
    END;
    
    IF l_emd_url IS NULL THEN
        p_agent_ok := 0;
    ELSE
        p_agent_ok := 1;
    END IF;
      
END;


procedure get_step_kill_details(p_step_id IN NUMBER,
                                p_source_info_valid OUT NUMBER,
                                p_emd_url OUT VARCHAR2,
                                p_username OUT VARCHAR2,
                                p_password OUT VARCHAR2,
                                p_source_emd_url OUT VARCHAR2,
                                p_source_username OUT VARCHAR2,
                                p_source_password OUT VARCHAR2) is
l_status_ok NUMBER;
l_access_ok NUMBER;
l_agent_ok  NUMBER;

l_job_id MGMT_JOB.job_id%type;
l_execution_id MGMT_JOB_EXECUTION.execution_id%type;
l_step_name MGMT_JOB_HISTORY.step_name%type;
l_step_type MGMT_JOB_HISTORY.step_type%type;
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%type;
l_iterate_param MGMT_JOB_HISTORY.iterate_param%type;
l_iterate_param_index MGMT_JOB_HISTORY.iterate_param_index%type;

l_dummy MGMT_JOB_INT_ARRAY;
l_step_params MGMT_JOB_PARAM_LIST;

l_command_name MGMT_JOB_EXECPLAN.command_name%type;

l_source_targetName MGMT_TARGETS.target_name%type;
l_source_targetType MGMT_TARGETS.target_type%type;

l_username_found NUMBER := 0;
l_password_found NUMBER := 0;

l_source_username_found NUMBER := 0;
l_source_password_found NUMBER := 0;
l_source_targetName_found NUMBER := 0;
l_source_targetType_found NUMBER := 0;

BEGIN
    IF EMDW_LOG.p_is_debug_set THEN
      EMDW_LOG.debug('Getting kill details for step: ' || p_step_id, MODULE_NAME);
    END IF;
    
    --validate that step can be killed
    allow_step_kill(p_step_id, l_status_ok, l_access_ok, l_agent_ok);
    
    IF l_status_ok = 0 THEN
        raise_application_error(MGMT_GLOBAL.STEP_NOT_EXECUTING_ERR,
                                MGMT_GLOBAL.STEP_NOT_EXECUTING_ERR_M);
    END IF;
    
    IF l_access_ok = 0 THEN
        raise_application_error(MGMT_GLOBAL.INVALID_STEP_ACCESS_ERR,
                                MGMT_GLOBAL.INVALID_STEP_ACCESS_ERR_M);
    END IF;
    
    IF l_agent_ok = 0 THEN
        raise_application_error(MGMT_GLOBAL.STEP_NOT_FOR_AGENT_ERR,
                                MGMT_GLOBAL.STEP_NOT_FOR_AGENT_ERR_M);
    END IF;
    
    --get EMD URL
    BEGIN
        SELECT emd_url
        INTO p_emd_url
        FROM MGMT_JOB_EXECUTION
        WHERE step_id=p_step_id;
    EXCEPTION
        --if the validation indicated that emd url was present, but if we
        --don't find it now, then the only reason could be that step just 
        --finished executing.  So we should throw appropriate exception.
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.STEP_NOT_EXECUTING_ERR,
                                    MGMT_GLOBAL.STEP_NOT_EXECUTING_ERR_M);
    END;
    
    
    --get more information about the step
    BEGIN    
        SELECT job_id, execution_id, step_name, step_type, iterate_param, iterate_param_index
        INTO l_job_id, l_execution_id, l_step_name, l_step_type, l_iterate_param, l_iterate_param_index
        FROM MGMT_JOB_HISTORY
        WHERE step_id=p_step_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_STEP_ERR,
                 'The specified step does not exist');
    END;
    
    l_job_type_id := MGMT_JOB_ENGINE.get_job_type_id(l_job_id, l_execution_id);
    
    --get step parameters
    l_step_params := MGMT_JOB_ENGINE.get_job_step_params(l_job_id,
                                                         l_execution_id,
                                                         l_step_name,
                                                         p_step_id,
                                                         l_job_type_id, 
                                                         0,
                                                         l_iterate_param,
                                                         l_iterate_param_index,
                                                         false,
                                                         l_dummy);
    
    IF l_step_params IS NOT NULL THEN
        
        --get command name
        SELECT command_name
        INTO l_command_name
        FROM MGMT_JOB_EXECPLAN
        WHERE job_type_id=l_job_type_id
          AND step_name=l_step_name
          AND step_type=l_step_type;
          
        IF l_command_name = MGMT_JOB_ENGINE.COMMAND_REMOTE_OP THEN
            p_source_info_valid := 0;
            FOR i IN l_step_params.FIRST..l_step_params.LAST LOOP  
                IF l_step_params(i).param_name = 'username' THEN
                    p_username := l_step_params(i).scalar_value;
                    l_username_found := 1;
                ELSIF l_step_params(i).param_name = 'password' THEN
                    p_password := l_step_params(i).scalar_value;
                    l_password_found := 1;
                END IF;
                
                IF l_username_found = 1 AND l_password_found = 1 THEN
                    EXIT;
                END IF;
            END LOOP;
        ELSIF l_command_name = MGMT_JOB_ENGINE.COMMAND_FILE_TRANSFER THEN
            p_source_info_valid := 1;
            FOR i IN l_step_params.FIRST..l_step_params.LAST LOOP  
                IF l_step_params(i).param_name = 'sourceUsername' THEN
                    p_source_username := l_step_params(i).scalar_value;
                    l_source_username_found := 1;
                ELSIF l_step_params(i).param_name = 'sourcePassword' THEN
                    p_source_password := l_step_params(i).scalar_value;
                    l_source_password_found := 1;
                ELSIF l_step_params(i).param_name = 'destUsername' THEN
                    p_username := l_step_params(i).scalar_value;
                    l_username_found := 1;
                ELSIF l_step_params(i).param_name = 'destPassword' THEN
                    p_password := l_step_params(i).scalar_value;
                    l_password_found := 1;
                ELSIF l_step_params(i).param_name = 'sourceTargetName' THEN
                    l_source_targetName := l_step_params(i).scalar_value;
                    l_source_targetName_found := 1;
                ELSIF l_step_params(i).param_name = 'sourceTargetType' THEN
                    l_source_targetType := l_step_params(i).scalar_value;
                    l_source_targetType_found := 1;
                END IF;
                
                IF l_source_username_found = 1 AND
                   l_source_password_found = 1 AND
                   l_username_found = 1 AND 
                   l_password_found = 1 AND
                   l_source_targetName_found = 1 AND
                   l_source_targetType_found = 1 THEN
                   
                    EXIT;
                END IF;
            END LOOP;
            
            -- get source emd url based on target name and target type
            BEGIN
                SELECT emd_url
                INTO p_source_emd_url
                FROM MGMT_TARGETS
                WHERE target_name = l_source_targetName
                  AND target_type = l_source_targetType;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                    raise_application_error(MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR,
                                            MGMT_GLOBAL.TARGET_DOES_NOT_EXIST_ERR_M);
            END;
        END IF;
    END IF;
END;
                                
--
-- Procedure to get the status of job execution
--    bitwise or of the following
-- 8 means a future run is waiting
-- 4 means a later run is active
     -- todo: should this be any other run, rather than only later runs?
-- 2 means one or more executions in run is active 
-- 1 means one or more executions in job is active 
-- 0 means neither run nor any executions in job is active or waiting
--                              
PROCEDURE get_run_status(p_execution_id IN RAW,
                         p_status OUT NUMBER
                         ) IS

l_job_id MGMT_JOB.job_id%TYPE;
l_scheduled_time  DATE;
l_count_run_waiting NUMBER;
l_count_run_later NUMBER;
l_count_run NUMBER;
l_count_job NUMBER;
BEGIN

    BEGIN

      SELECT  job_id,scheduled_time 
      INTO    l_job_id, l_scheduled_time
      FROM    MGMT_JOB_EXEC_SUMMARY
      WHERE   execution_id=p_execution_id
      AND     status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS;

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
          raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                                  'Invalid execution id');
    END;

    p_status := 0;

    SELECT COUNT(1) INTO l_count_run
    FROM    MGMT_JOB_EXEC_SUMMARY 
    WHERE  job_id=l_job_id
    AND    scheduled_time = l_scheduled_time
    AND status not in 
    (MGMT_JOB_ENGINE.COMPLETED_STATUS, 
    MGMT_JOB_ENGINE.FAILED_STATUS,
    MGMT_JOB_ENGINE.ABORTED_STATUS,
    MGMT_JOB_ENGINE.STOPPED_STATUS,
    MGMT_JOB_ENGINE.SKIPPED_STATUS,
    MGMT_JOB_ENGINE.DELETE_PENDING_STATUS,
    MGMT_JOB_ENGINE.WAITING_STATUS);

    IF l_count_run > 0 THEN

        -- the run is active (so is the job)
        p_status := 1 + 2;

    ELSE

        -- the run is not active, check if the job is
        SELECT COUNT(1) INTO l_count_job
        FROM    MGMT_JOB_EXEC_SUMMARY 
        WHERE  job_id=l_job_id
        AND    status not in
        (MGMT_JOB_ENGINE.COMPLETED_STATUS,
        MGMT_JOB_ENGINE.FAILED_STATUS,
        MGMT_JOB_ENGINE.ABORTED_STATUS,
        MGMT_JOB_ENGINE.STOPPED_STATUS,
        MGMT_JOB_ENGINE.SKIPPED_STATUS,
        MGMT_JOB_ENGINE.DELETE_PENDING_STATUS,
        MGMT_JOB_ENGINE.WAITING_STATUS);
    
        IF l_count_job > 0 THEN
            p_status := 1;
        END IF;

    END IF;

    -- check for later runs
    SELECT COUNT(1) INTO l_count_run_later
    FROM    MGMT_JOB_EXEC_SUMMARY 
    WHERE  job_id=l_job_id
    AND    scheduled_time > l_scheduled_time
    AND    status not in 
    (MGMT_JOB_ENGINE.COMPLETED_STATUS, 
    MGMT_JOB_ENGINE.FAILED_STATUS,
    MGMT_JOB_ENGINE.ABORTED_STATUS,
    MGMT_JOB_ENGINE.STOPPED_STATUS,
    MGMT_JOB_ENGINE.SKIPPED_STATUS,
    MGMT_JOB_ENGINE.DELETE_PENDING_STATUS,
    MGMT_JOB_ENGINE.WAITING_STATUS);

    IF l_count_run_later > 0 THEN
        -- a later run is active
        p_status := p_status + 4;
    END IF;

    -- check for waiting runs 
    SELECT COUNT(1) INTO l_count_run_waiting
    FROM    MGMT_JOB_EXEC_SUMMARY 
    WHERE  job_id=l_job_id
    AND    scheduled_time > l_scheduled_time
    AND    status = MGMT_JOB_ENGINE.WAITING_STATUS;

    IF l_count_run_waiting > 0 THEN
        -- a future run is waiting
        p_status := p_status + 8;
    END IF;


end;





--- This procedure will check for targets properties for each of the targets 
-- will return the targets display Name and target property pair for where its not able to find the target property 
--   p_tgt_list_in   List of targets Types against which we want to validate
--   p_props_list_in    List of targets properties we want to check the existenance
--   p_job_target_type_in  targets Type of the job
--   p_target_name_array_out  return array of taregts display name and target properties

Procedure  VALIDATE_ACTUAL_TARGET_PROPS (
                                          p_tgt_list_in   In MGMT_JOB_TARGET_LIST_TABLE ,
                                          p_props_list_in   In MGMT_JOB_SHORT_STR_ARR_TABLE,
                                          p_job_target_type_in in SMP_EMD_STRING_ARRAY,
                                          p_target_name_array_out OUT MGMT_JOB_DNAME_TYPE_PROP_TABLE 
                               )  IS
  tgt_list MGMT_JOB_TARGET_LIST ;
  l_props_list MGMT_SHORT_STRING_ARRAY;
  l_target_details_table MGMT_JOB_DNAME_TYPE_PROP_TABLE := MGMT_JOB_DNAME_TYPE_PROP_TABLE();
  l_type_name_prop_rec MGMT_JOB_DNAME_TYPE_PROP_rec;
  p_target_name_array MGMT_JOB_DNAME_TYPE_PROP_TABLE := MGMT_JOB_DNAME_TYPE_PROP_TABLE();
  p_count number;
  BEGIN
    if p_tgt_list_in IS NOT NULL THEN
      FOR j IN 1..p_tgt_list_in.count 
      LOOP
        tgt_list := p_tgt_list_in(j);
        MGMT_JOB_UI.get_flattened_aggr_targets(tgt_list,p_job_target_type_in(j));
        l_props_list := p_props_list_in(j);


        FOR k IN 1..tgt_list.count 
        LOOP
          FOR kk IN 1..l_props_list.count 
          LOOP
            l_target_details_table.extend;
            p_count := l_target_details_table.last;
            l_target_details_table(p_count) := MGMT_JOB_DNAME_TYPE_PROP_rec('',tgt_list(k).TARGET_NAME,tgt_list(k).TARGET_TYPE ,l_props_list(kk) ) ;
          END LOOP;
        END LOOP;
      END LOOP;
    END IF;

    select MGMT_JOB_DNAME_TYPE_PROP_REC(mt.DISPLAY_NAME ,t1.target_name,t1.target_type,t1.propName)
          bulk collect into p_target_name_array
    from  mgmt_targets mt, table(cast(l_target_details_table as MGMT_JOB_DNAME_TYPE_PROP_TABLE)) t1
    where mt.target_name = t1.target_name 
    AND   mt.target_type = t1.target_type 
    AND  (mt.target_guid,t1.propName)  
         NOT IN (select target_guid ,property_name from mgmt_target_properties );

    p_target_name_array_out := p_target_name_array;

  END;



 -- Jobs will use this proecdure to do preSubmit repository validations
 -- currently its calling from MainJobBean and MultitaskJobBean any change in this procedure 
 -- must be reflected in both the java classes
 --  p_pref_cred_data  credentails records
--   p_job_owner_user_name job Owner Name
--   p_tgt_list_in   List of targets Types against which we want to validate
--   p_props_list_in    List of targets properties we want to check the existenance
--   p_job_target_type_in  targets Type of the job
--   p_target_name_array_out  return array of taregts display name and target properties

Procedure  DO_ACTIVE_JOB_VALIDATION (
                             p_pref_cred_data IN OUT MGMT_JOB_TASK_CRED_SET_ARRAY,
                             p_job_owner_user_name IN VARCHAR2,
                             p_tgt_list_in   In MGMT_JOB_TARGET_LIST_TABLE ,
                             p_props_list_in   In MGMT_JOB_SHORT_STR_ARR_TABLE,
                             p_job_target_type_in in SMP_EMD_STRING_ARRAY,
                             p_target_name_array_out OUT MGMT_JOB_DNAME_TYPE_PROP_TABLE 
                               )  IS

begin

 IF p_pref_cred_data is not NULL THEN
    get_preferred_creds_not_set
                              ( p_pref_cred_data ,
                                p_job_owner_user_name ) ;
 END IF;


-- validate targest properties against each targets if targets is composite flatten the targets
--and bvalidate if any of the property is missing return a targetDisplayName and targets props pair
 IF p_pref_cred_data is NULL THEN

    p_pref_cred_data := NULL;
    validate_actual_target_props (
                              p_tgt_list_in   ,
                              p_props_list_in   ,
                              p_job_target_type_in ,
                              p_target_name_array_out 
                               ) ;
 END IF;
end;


Procedure get_job_details( p_execution_id IN RAW ,p_job_id_out OUT VARCHAR2,p_job_type_out OUT VARCHAR2, p_job_name_out OUT VARCHAR2,p_job_owner_out OUT VARCHAR2, p_job_status_out OUT NUMBER,p_scheduled_time_out OUT DATE) IS

BEGIN
     BEGIN
        SELECT  e.job_id,j.job_type, j.job_name,j.job_owner,j.job_status, scheduled_time
        INTO    p_job_id_out,p_job_type_out, p_job_name_out,p_job_owner_out, p_job_status_out,p_scheduled_time_out
        FROM    MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB j
        WHERE   execution_id=p_execution_id
        AND     e.job_id=j.job_id
        AND     e.status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_EXECUTION_ERR,
                                    'Invalid execution id');
    END;

END;

PROCEDURE get_output(p_output_ids_in IN MGMT_USER_GUID_ARRAY,output_out OUT PARAMS_CURSOR) IS
BEGIN
    OPEN output_out 
    FOR 
       select output FROM MGMT_JOB_OUTPUT WHERE OUTPUT_ID IN (select * from TABLE(p_output_ids_in));
END get_output;

--  End package
--
END MGMT_JOB_UI;

/
show errors;