Rem
Rem $Header: sdk_ca_pkgbody.sql 28-aug-2005.11:36:03 skini Exp $
Rem
Rem sdk_ca_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_ca_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem     This is the PL/SQL SDK interface to the corrective action
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED  (MM/DD/YY)
Rem       skini   08/28/05 - Remove update of mgmt_job when editing target CAs 
Rem       skini   08/23/05 - Do not release lock at end of API 
Rem       skini   08/12/05 - Mo broken CA work 
Rem       skini   06/13/05 - Removal of edit_ca 
Rem       skini   04/15/05 - Broken CAs 
Rem       skini   04/12/05 - Do not save overridden credentials for template 
Rem                          CAs 
Rem       skini   12/01/04 - Fix saving template CA 
Rem       jaysmit 11/24/04 - add ca update call 
Rem       skini   11/20/04 - Fix retry 
Rem       skini   11/17/04 - Cutover credentials_disabled column to broken 
Rem                          column 
Rem       skini   10/22/04 - Versioning changes 
Rem       skini   09/30/04 - Fix null reference 

Rem       pshishi 09/23/04 - Added template copy APIs
Rem       jaysmit 09/16/04 - allow no override credentials at creation
Rem       pshishi 09/15/04 - pshishir_ca_trans
Rem       pshishi 09/08/04 - 

CREATE OR REPLACE PACKAGE BODY MGMT_CA
AS


/****** PUBLIC METHODS ******/


-- Insert a Correctice action with the specified name, type, description,
-- targets and owner. This call returns the job id that is created from these attributes,
-- via the out parameters. Each Corrective action has a unique values in 
-- corrective_action_target_guid(Which is a combination of target_name and target_type)
-- and MGMT_JOB.job_name.

PROCEDURE create_target_ca(p_ca_name IN VARCHAR2, 
                           p_target IN VARCHAR2, 
				           p_target_type VARCHAR2, 
	        		       p_description IN VARCHAR2, 
                           p_job_type IN VARCHAR2, 
                           p_job_params IN MGMT_JOB_PARAM_LIST, 
                           p_owner VARCHAR2, 
                           p_job_creds MGMT_JOB_CRED_ARRAY, 
                           p_job_id_out OUT RAW) IS

l_target_guid RAW(16);
cnt INTEGER := 0;
l_lock_handle varchar2(256) ;
l_lock_status NUMBER;
l_target_list MGMT_JOB_TARGET_LIST := MGMT_JOB_TARGET_LIST() ;
l_target_list_index NUMBER:=1;
l_job_id RAW(16);

l_creds_set NUMBER;
BEGIN

    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    
    l_target_guid := MGMT_TARGET.get_target_guid(p_target, p_target_type);
    l_target_list := MGMT_JOB_TARGET_LIST(MGMT_JOB_TARGET_RECORD(p_target, 
                                                            p_target_type));
     
    -- Validate that there is no other CA with the same name
    BEGIN
        SELECT  COUNT(j.job_id) 
        INTO    cnt 
        FROM    MGMT_JOB j, MGMT_CORRECTIVE_ACTION c 
        WHERE   j.job_id=c.job_id
        AND     j.job_name=upper(p_ca_name)
        AND     j.is_corrective_action=1
        AND     c.ca_scope=CA_SCOPE_TARGET
        AND     c.ca_target_guid=l_target_guid;
           	
        IF cnt > 0 THEN
      	    raise_application_error(MGMT_GLOBAL.JOB_EXISTS_ERR,
                'A corrective action with the same name has already been configured for this target');
    	END IF;
    END;
	
    -- Insert the schema entries for the CA
    MGMT_JOB_ENGINE.insert_job(p_ca_name, 
                               p_owner, 0,
                               p_target_type, 
                               p_description,
                               p_job_type,
                               p_job_params,
                               null,
                               p_job_creds,
                               null, null, 1, 0, 1,
                               CA_SCOPE_TARGET, l_target_guid, null,
                               p_job_id_out); 
      
    -- Insert the target list for this corrective action
    MGMT_JOB_ENGINE.insert_target_list(p_job_id_out,
                                       l_target_list,
                                       l_target_list_index);

END;   

-- Create a correctice action as a template. It should not have any schedule.
-- It takes the Corrective action name, template name, target type, description,
-- owner and job credentials and it returns job id via output parameter.Each 
-- Corrective action has a unique values in corrective_action_template_guid
-- (Which is a combination of template_name and target_type)
-- and MGMT_JOB.job_name.

PROCEDURE create_template_ca(p_ca_name IN VARCHAR2, 
                             p_template IN VARCHAR2, 
                             p_target_type VARCHAR2, 
                             p_description IN VARCHAR2, 
                             p_job_type IN VARCHAR2, 
                             p_job_params IN MGMT_JOB_PARAM_LIST, 
                             p_owner VARCHAR2, 
                             p_job_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, 
                             p_job_id_out OUT RAW)IS 


l_template_guid RAW(16);
cnt INTEGER := 0;
l_lock_handle varchar2(256) ;
l_lock_status NUMBER;

l_category MGMT_JOB_TYPE_INFO.job_type_category%TYPE;
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;

BEGIN
    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;

    l_template_guid:= MGMT_TEMPLATE.generate_template_guid(p_target_type, 
                                                           p_template);

    -- Validate that there is no other CA with the same name
    BEGIN
        SELECT  COUNT(j.job_id) INTO cnt 
        FROM    MGMT_JOB j, MGMT_CORRECTIVE_ACTION c 
        WHERE   j.job_id=c.job_id 
        AND     j.job_name=upper(p_ca_name)
        AND     j.is_corrective_action=1
        AND     c.ca_scope=CA_SCOPE_TEMPLATE
        AND     c.ca_template_guid=l_template_guid;
           	
        IF cnt > 0 THEN
      	    raise_application_error(mgmt_global.JOB_EXISTS_ERR,
                'A corrective action with the same name has already been configured for this template');
    	END IF;
    END;

    -- Insert the schema entries for the CA
    -- Note: we will not accept overridden credentials for
    -- template CAs unless we use target credentials
    MGMT_JOB_ENGINE.insert_job(p_ca_name, 
                               p_owner, 0,
                               p_target_type, 
                               p_description,
                               p_job_type,
                               p_job_params,
                               null,
                               null,
                               null, null, 1, 0, 1,
                               CA_SCOPE_TEMPLATE, null, l_template_guid,
                               p_job_id_out); 


    -- For multitask corrective actions, remove all nested job
    -- credentials
    SELECT  job_type_category, ji.job_type_id 
    INTO    l_category, l_job_type_id
    FROM    MGMT_JOB j, MGMT_JOB_TYPE_MAX_VERSIONS mv, MGMT_JOB_TYPE_INFO ji
    WHERE   j.job_type=mv.job_type
    AND     j.job_type_major_version=mv.major_version
    AND     mv.job_type_id=ji.job_type_id
    AND     j.job_id=p_job_id_out;

    IF l_category=MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_HIDDEN THEN
        DELETE  FROM MGMT_NESTED_JOB_CRED_INFO
        WHERE   job_type_id=l_job_type_id;
    END IF;

END;       
  
-- Create corrective action for a particular target type. 
-- It takes inputs as corrective action name, 
-- target type, description, job type, job parameters, and job credentials 
-- and it returns job id via output parameter. 
-- Each Corrective Action has a unique pair of values in 
-- MGMT_JOB.target_type and MGMT_JOB.job_name
PROCEDURE create_policy_rule_default_ca(p_ca_name IN VARCHAR2, 
                                        p_target_type VARCHAR2, 
                                        p_description IN VARCHAR2, 
                                        p_job_type IN VARCHAR2, 
                                        p_job_params IN MGMT_JOB_PARAM_LIST, 
                                        p_job_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, 
                                        p_job_id_out OUT RAW)IS

l_lock_handle varchar2(256) ;
cnt INTEGER := 0;
l_lock_status NUMBER;

BEGIN
    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    BEGIN
        SELECT  COUNT(j.job_id) INTO cnt 
        FROM    MGMT_JOB j, MGMT_CORRECTIVE_ACTION c 
        WHERE   c.job_id = j.job_id 
        AND     j.job_name = upper(p_ca_name) 
        AND    	j.target_type=p_target_type
        AND    	j.is_corrective_action=1
        AND     c.ca_scope=CA_SCOPE_TARGET_TYPE;
           	
	    IF cnt > 0 THEN
      	        raise_application_error(mgmt_global.JOB_EXISTS_ERR,
                    'A corrective action with the same name has already been configured for this target type');
        END IF;
    END;

    -- Insert the schema entries for the CA
    MGMT_JOB_ENGINE.insert_job(p_ca_name, 
                               MGMT_USER.get_current_em_user, 0,
                               p_target_type, 
                               p_description,
                               p_job_type,
                               p_job_params,
                               null,
                               p_job_creds,
                               null, null, 1, 0, 1,
                               CA_SCOPE_TARGET_TYPE, null, null,
                               p_job_id_out); 
 END;         

-- Create user defined Corrective action. It takes inputs as corrective action name, 
-- owner's name, target_type, description, job_type, job parameters and job credentials 
-- and return job id via output parameter. Each Corrective action has a unique pair of values
-- in MGMT_JOB.job_owner and MGMT_JOB.job_name
PROCEDURE create_user_ca(p_ca_name IN VARCHAR2, 
                         p_owner IN VARCHAR2, 
                         p_target_type VARCHAR2, 
                         p_description IN VARCHAR2, 
                         p_job_type IN VARCHAR2, 
                         p_job_params IN MGMT_JOB_PARAM_LIST, 
                         p_job_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, 
                         p_job_id_out OUT RAW) IS


l_lock_handle varchar2(256) ;
cnt INTEGER := 0;
l_lock_status NUMBER;

BEGIN
    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;

    BEGIN
        SELECT  COUNT(j.job_id) INTO cnt 
        FROM    MGMT_JOB j, MGMT_CORRECTIVE_ACTION c 
        WHERE   c.job_id = j.job_id
        AND     j.job_name = upper(p_ca_name)
        AND	    j.is_corrective_action = 1
        AND     j.job_owner=upper(p_owner)
        AND     c.ca_scope=CA_SCOPE_USER;
           	
        IF cnt > 0 THEN
      	    raise_application_error(mgmt_global.JOB_EXISTS_ERR,
                'A user corrective action with the same name already exists');
    	END IF;
    END;

    -- Insert the schema entries for the CA
    MGMT_JOB_ENGINE.insert_job(p_ca_name, 
                               p_owner, 0,
                               p_target_type, 
                               p_description,
                               p_job_type,
                               p_job_params,
                               null,
                               p_job_creds,
                               null, null, 1, 0, 1,
                               CA_SCOPE_USER, null, null,
                               p_job_id_out); 
END;    

-- Get target scoped job id

FUNCTION get_target_scoped_job_id(p_ca_name VARCHAR2, 
                                  p_target VARCHAR2, 
                                  p_target_type VARCHAR2) RETURN RAW IS  

l_target_guid RAW(16);
l_job_id MGMT_JOB.job_id%TYPE;

BEGIN
    -- Check for not null of corrective action name
    EM_CHECK.check_not_null(p_ca_name, 'CA Name');

    -- Check for not null of target name
    EM_CHECK.check_not_null(p_target, 'Target Name');

    -- Check for not null of target type
    EM_CHECK.check_not_null(p_target_type, 'Target type');			

    l_target_guid:= MGMT_TARGET.get_target_guid(p_target, p_target_type);
    BEGIN
        SELECT j.job_id INTO l_job_id 
            FROM MGMT_JOB j, MGMT_CORRECTIVE_ACTION c 
	    WHERE
	    c.job_id = j.job_id AND
	    j.job_name = upper(p_ca_name) AND
	    j.is_corrective_action = 1 AND
      	    c.ca_target_guid = l_target_guid AND
      	    c.ca_scope=CA_SCOPE_TARGET;
	EXCEPTION
      	    WHEN NO_DATA_FOUND THEN
                raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                    'Corrective Action doesnot exist');
    END;

    RETURN l_job_id;
END;
  

-- Edit the specified Corrective action, using the specified name,
-- target, target_type, description, parameters and credentials.

PROCEDURE edit_target_ca(p_ca_name VARCHAR2, 
 			             p_target VARCHAR2, 
                         p_target_type VARCHAR2, 
                         p_description VARCHAR2, 
                         p_params MGMT_JOB_PARAM_LIST, 
                         p_job_creds MGMT_JOB_CRED_ARRAY) IS

l_job_id MGMT_JOB.job_id%TYPE;
l_target_list MGMT_JOB_TARGET_LIST_ARRAY := MGMT_JOB_TARGET_LIST_ARRAY() ;
l_lock_handle varchar2(256) ;
BEGIN
    
    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    l_job_id:=get_target_scoped_job_id(p_ca_name, p_target, p_target_type);
    l_target_list:=mgmt_job_target_list_array(mgmt_job_target_list(mgmt_job_target_record(p_target,p_target_type)));
	
    MGMT_JOB_ENGINE.edit_job(p_job_id=>l_job_id,
                             p_description=>p_description, 
                             p_params=>p_params,
                             p_targets=>l_target_list,
                             p_schedule=>null,
                             p_overridden_creds=>p_job_creds);	
END;

-- Get job id for template scoped corrective action

FUNCTION get_template_scoped_job_id(p_ca_name VARCHAR2,
                                    p_template VARCHAR2, 
                                    p_target_type VARCHAR2) RETURN RAW IS  

l_template_guid RAW(16);
l_job_id RAW(16);

BEGIN
    -- Check for not null of Corrective action name
    EM_CHECK.check_not_null(p_ca_name, 'CA Name');

    -- Check for not null of template name
    EM_CHECK.check_not_null(p_template, 'Template Name');

    -- Check for not null of target type
    EM_CHECK.check_not_null(p_target_type, 'Target type');			

    l_template_guid:= MGMT_TEMPLATE.generate_template_guid(p_target_type,
                                                           p_template);

    BEGIN
        SELECT  j.job_id INTO l_job_id 
        FROM    MGMT_JOB j, MGMT_CORRECTIVE_ACTION c 
        WHERE   j.job_id = c.job_id
        AND     j.job_name = upper(p_ca_name)
        AND	    j.is_corrective_action = 1 
        AND     c.ca_template_guid = l_template_guid
        AND     c.ca_scope IN (CA_SCOPE_TEMPLATE, CA_SCOPE_TEMPLATE_COPY);
	EXCEPTION
      	    WHEN NO_DATA_FOUND THEN
                raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                    'Template scoped Corrective Action does not exist');
    END;

    RETURN l_job_id;
END;

-- Edit the specified Corrective action for a template, using the specified name,
-- template name, target_type, description, parameters and credentials.

 PROCEDURE edit_template_ca(p_ca_name VARCHAR2, 
                            p_template VARCHAR2, 
                            p_target_type VARCHAR2, 
                            p_description VARCHAR2, 
                            p_params MGMT_JOB_PARAM_LIST, 
                            p_job_creds MGMT_JOB_CRED_ARRAY) IS

l_job_id RAW(16);
l_target_list MGMT_JOB_TARGET_LIST_ARRAY :=null ;
l_lock_handle varchar2(256) ;

l_category MGMT_JOB_TYPE_INFO.job_type_category%TYPE;
l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE;

BEGIN

    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    l_job_id:= get_template_scoped_job_id(p_ca_name, p_template, p_target_type);
    -- Note: we will not accept overridden credentials for
    -- template CAs unless we use target credentials
    MGMT_JOB_ENGINE.edit_job(l_job_id,
                             p_description,
                             p_params,
                             l_target_list,
                             null,
                             null);
	
    -- For multitask corrective actions, remove all nested job
    -- credentials
    SELECT  job_type_category, ji.job_type_id 
    INTO    l_category, l_job_type_id
    FROM    MGMT_JOB j, MGMT_JOB_TYPE_MAX_VERSIONS mv, MGMT_JOB_TYPE_INFO ji
    WHERE   j.job_type=mv.job_type
    AND     j.job_type_major_version=mv.major_version
    AND     mv.job_type_id=ji.job_type_id
    AND     j.job_id=l_job_id;

    IF l_category=MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_HIDDEN THEN
        DELETE  FROM MGMT_NESTED_JOB_CRED_INFO
        WHERE   job_type_id=l_job_type_id;
    END IF;
	
END;

-- Get Target Type scoped job id

FUNCTION get_target_type_scoped_job_id( p_ca_name VARCHAR2, 
				        p_target_type VARCHAR2) RETURN RAW IS  

l_job_id RAW(16);

BEGIN
    -- Check for not null of Corrective action name
    EM_CHECK.check_not_null(p_ca_name, 'CA Name');

    -- Check for not null of target type
    EM_CHECK.check_not_null(p_target_type, 'Target type');			

    BEGIN
        SELECT  j.job_id INTO l_job_id 
        FROM    MGMT_JOB j, MGMT_CORRECTIVE_ACTION c 
        WHERE   c.job_id = j.job_id
        AND     j.job_name = upper(p_ca_name)
        AND     j.target_type=p_target_type
        AND     j.is_corrective_action=1
        AND     c.ca_scope=CA_SCOPE_TARGET_TYPE;
  	EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                    'Target type Corrective Action doesnot exist');
    END;

    RETURN l_job_id;
END;

-- Edit the specified Corrective action for a target type, using the specified name,
-- target type, description, parameters and credentials.

PROCEDURE edit_policy_rule_default_ca(p_ca_name VARCHAR2, 
				      p_target_type VARCHAR2, 
				      p_description VARCHAR2, 
				      p_params MGMT_JOB_PARAM_LIST, 
				      p_job_creds MGMT_JOB_CRED_ARRAY) IS 

l_job_id RAW(16);
l_target_list MGMT_JOB_TARGET_LIST_ARRAY :=null;
l_lock_handle varchar2(256) ;

BEGIN

    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    l_job_id:= get_target_type_scoped_job_id(p_ca_name, p_target_type);
	    
    MGMT_JOB_ENGINE.edit_job(l_job_id,
                             p_description,
                             p_params,
                             l_target_list,
                             null,
                             p_job_creds);
	
END;

-- Get job id for  user scoped corrective action

FUNCTION get_user_scoped_job_id( p_ca_name VARCHAR2, 
				 p_owner VARCHAR2) RETURN RAW IS  

l_job_id RAW(16);

BEGIN
    
    -- Check for not null of ca name
    EM_CHECK.check_not_null(p_ca_name, 'CA Name');

    --Check for not null of owner name
    EM_CHECK.check_not_null(p_owner, 'Owner Name');

    BEGIN
        SELECT  j.job_id INTO l_job_id 
        FROM    MGMT_JOB j, MGMT_CORRECTIVE_ACTION c 
        WHERE   c.job_id = j.job_id
        AND     j.job_name = upper(p_ca_name)
        AND     j.is_corrective_action = 1
        AND     j.job_owner=upper(p_owner)
        AND     c.ca_scope=CA_SCOPE_USER;
	EXCEPTION
      	    WHEN NO_DATA_FOUND THEN
                raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR,
                    'User scoped Corrective Action doesnot exist');
    END;	

    RETURN l_job_id;
END;

-- Edit the specified user craeted Corrective action, using the specified name,
-- owner, description, parameters and credentials.

PROCEDURE edit_user_ca(p_ca_name VARCHAR2, 
                       p_owner VARCHAR2, 
		       p_description VARCHAR2, 
		       p_params MGMT_JOB_PARAM_LIST, 
		       p_job_creds MGMT_JOB_CRED_ARRAY)IS 

l_target_list MGMT_JOB_TARGET_LIST_ARRAY :=null ;
l_job_id RAW(16);
l_lock_handle varchar2(256) ;

BEGIN

    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    l_job_id:=get_user_scoped_job_id(p_ca_name, p_owner);

    MGMT_JOB_ENGINE.edit_job(l_job_id,
          	                p_description,
                  	        p_params,
                            l_target_list,
                            null,
                            p_job_creds);
END;




-- Delete the specified Corrective action for a target, using the specified name,
-- target name and target_type.

PROCEDURE delete_target_ca(p_ca_name VARCHAR2, 
                           p_target VARCHAR2, 
                           p_target_type VARCHAR2, 
                           p_commit NUMBER DEFAULT 0)IS
 
l_job_id RAW(16);
l_lock_handle varchar2(256) ;

BEGIN

    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    l_job_id:=get_target_scoped_job_id(p_ca_name, p_target, p_target_type);
   
     MGMT_JOB_ENGINE.delete_ca(p_job_id=>l_job_id, p_commit=>p_commit);

END;


-- Delete the specified Corrective action for a template, using the specified name,
-- template name and target_type.

PROCEDURE delete_template_ca(p_ca_name VARCHAR2, 
                             p_template VARCHAR2, 
		 	                 p_target_type VARCHAR2, 
                             p_commit NUMBER DEFAULT 0)IS 

l_job_id RAW(16);
l_lock_handle varchar2(256) ;

BEGIN

    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    l_job_id:= get_template_scoped_job_id(p_ca_name, p_template, p_target_type);

    MGMT_JOB_ENGINE.delete_ca(p_job_id=>l_job_id, p_commit=>p_commit);

END;

-- Delete the specified Corrective action for a target type, using the specified name,
-- and target_type.

PROCEDURE delete_target_type_ca(p_ca_name VARCHAR2, 
                   		        p_target_type VARCHAR2, 
                                p_commit NUMBER DEFAULT 0)IS

l_job_id RAW(16);
l_lock_handle varchar2(256) ;

BEGIN

    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    l_job_id:= get_target_type_scoped_job_id(p_ca_name, p_target_type);
    
    MGMT_JOB_ENGINE.delete_ca(p_job_id=>l_job_id, p_commit=>1);
	
END;


-- Delete the specified user created Corrective action , using the specified name,
-- and owner name.

PROCEDURE delete_user_ca(p_ca_name VARCHAR2, 
                         p_owner VARCHAR2, 
                         p_commit NUMBER DEFAULT 0)IS 

l_job_id RAW(16);
l_lock_handle varchar2(256) ;

BEGIN

    l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ;
    l_job_id:=get_user_scoped_job_id(p_ca_name, p_owner);

    MGMT_JOB_ENGINE.delete_ca(p_job_id=>l_job_id, p_commit=>1);

END;

-- While executing, the following procedure takes
-- execution_id and stop the execution of the corrective action.
 
PROCEDURE stop_ca_execution(p_execution_id IN RAW) IS 

BEGIN
    MGMT_JOB_ENGINE.stop_execution(p_execution_id);
END;


-- The following function takes execution_id and restart the execution 
-- of the corrective action and return new execution id.

FUNCTION restart_ca_execution(p_exec_id IN RAW) RETURN RAW IS

l_success NUMBER:=0;
l_target_guid RAW(16);
l_violation_guid RAW(16);
l_restart_execution_id RAW(16);

l_job_name MGMT_JOB.job_name%TYPE;
BEGIN
    BEGIN
	    SELECT  triggering_severity, job_name
        INTO    l_violation_guid, l_job_name
        FROM    MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB j
        WHERE   execution_id=p_exec_id
        AND     e.job_id=j.job_id;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            raise_application_error(MGMT_GLOBAL.INVALID_EXECUTION_ERR,
              'The specified execution does not exist or is inaccessible');
    END;

    l_restart_execution_id := 
        MGMT_JOB_ENGINE.restart_job_execution(p_exec_id);

    EMD_SCHEMA.add_severity_annotation(l_violation_guid, 
                'Corrective Action ' || l_job_name || ' restarted');  

    RETURN l_restart_execution_id;
END;


END MGMT_CA;
/


show errors;