Rem drv: <create type="pkgbodys" pos="policy/policy_pkgbody.sql+"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/compliance/compliance_config_std_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/06/19 09:29:00 suvmalho Exp $
Rem
Rem compliance_config_std_pkgbody.sql
Rem
Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      compliance_config_std_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    groyal      11/20/06 - Remove references to 11g specific tables
Rem    niramach    10/16/06 - Fix for bug 5557714 (Security fix).
Rem    groyal      08/25/06 - Replace total_violations with crit_violations,
Rem                           warn_violations and info_violations, Rework trend-related procedures
Rem    sthiruna    08/10/06 - Fix for Bug 5404180. Modifying the logic for error
Rem                           status in log_cs_evaluation_results.
Rem    sthiruna    08/03/06 - Commenting 11g packages
Rem    niramach    07/20/06 - Add update_cs_cqs_valid procedure to em_cs_compile_util pkg. 
Rem    rreilly     07/17/06 - add ics rqs_guid generation 
Rem    rreilly     07/10/06 - fix compliance score and do bulk calculation 
Rem    aragarwa    06/28/06 - Fixing xsd changes : removing context, adding 
Rem                           targettype 
Rem    rreilly     06/29/06 - adding the cs compile and evaluate packages
Rem    sthiruna    06/08/06 - Fix for bug 5263883 
Rem    aragarwa    06/11/06 - Removing default for few elements 
Rem    aragarwa    05/17/06 - Removing hierarchy dname columns. 
Rem    niramach    05/03/06 - Changes related to delete eval results API
Rem                         - 10.3 and 11g table unification     
Rem                         - target filteration for trend pages
Rem    aragarwa    05/01/06 - Adding support for reusable queries and cs 
Rem                           inclusion 
Rem    niramach    04/11/06 - Add delete cs eval results Api. 
Rem    aragarwa    03/31/06  - Change MGMT_SHORT_STRING_TABLE to 
Rem                            MGMT_MEDIUM_STRING_TABLE for delete cs policies. 
Rem    niramach    03/27/06 - Add procedures for trend overview sqls.
Rem    aragarwa    02/28/06 -  reflecting XSD and table changes. 
Rem    niramach    03/05/06 - Remove logging compliance score for rulefolder 
Rem                           level. 
Rem    niramach    02/28/06 - Add performance log for cs evaluation. 
Rem    kchiasso    02/23/06 - change owner to author
Rem    niramach    01/25/06 - Add procedures for cs evaluation.
Rem    aragarwa    01/23/06 - Adding procedures and functions for adding rows 
Rem                           in cs tables. 
Rem    niramach    01/13/06 - Package body for configuration standards. 
Rem    niramach    01/13/06 - Created
Rem

Rem ****************************************************************************
Rem EM_CONFIG_STD package body
Rem ****************************************************************************


CREATE OR REPLACE PACKAGE BODY em_config_std
AS

PROCEDURE commit_delete_cs_txn(p_execute_stmt VARCHAR2 , p_config_std_guid RAW,
  p_is_super_user INTEGER )
IS
  l_counter       INTEGER :=0;

BEGIN
  LOOP
    IF EMDW_LOG.P_IS_DEBUG_SET THEN
        EMDW_LOG.DEBUG(' Executing SQL ' || p_execute_stmt, G_MODULE_NAME);
    END IF;
    EXECUTE IMMEDIATE p_execute_stmt USING p_config_std_guid, p_is_super_user,
    MGMT_USER.USER_HAS_PRIV, MGMT_USER.GET_CURRENT_EM_USER, MGMT_USER.OPERATOR_TARGET,
            MGMT_USER.USER_HAS_PRIV, mgmt_global.MAX_COMMIT;

    l_counter := SQL%ROWCOUNT;

    COMMIT;
    IF l_counter < mgmt_global.MAX_COMMIT THEN
      EXIT;
    END IF;
  END LOOP;
END commit_delete_cs_txn;


FUNCTION add_rulefolder (
  p_cs_guid                  RAW,
  p_rulefolder_iname         VARCHAR2,
  p_rulefolder_dname         VARCHAR2,
  p_parent_guid              RAW,
  p_child_position           NUMBER,
  p_description              VARCHAR2  DEFAULT NULL,
  p_reference_url            VARCHAR2  DEFAULT NULL
  ) RETURN RAW 
IS
  l_rulefolder_guid       RAW(16);
BEGIN

  INSERT INTO mgmt_cs_rulefolder (cs_guid, rulefolder_iname, rulefolder_dname,
	    description, reference_url) 
  VALUES (p_cs_guid, p_rulefolder_iname, p_rulefolder_dname, 
	    p_description, p_reference_url) 
  RETURNING rulefolder_guid INTO l_rulefolder_guid;

  INSERT INTO mgmt_cs_hierarchy (parent_guid,child_guid,
      child_type, child_position) 
  VALUES (p_parent_guid, l_rulefolder_guid,
      MGMT_CONFIG_STD.G_ELEMENT_RULEFOLDER, p_child_position);

  RETURN l_rulefolder_guid;

END add_rulefolder;

FUNCTION add_rule(
  p_cs_guid                  RAW,
  p_rule_iname               VARCHAR2, 
  p_rule_dname               VARCHAR2,
  p_parent_guid              RAW,
  p_child_position           NUMBER,
  p_test                     VARCHAR2  DEFAULT NULL,
  p_importance_level         NUMBER    DEFAULT MGMT_CONFIG_STD.G_IMPORTANCE_NORMAL,
  p_test_type                NUMBER    DEFAULT NULL,
  p_reference_url            VARCHAR2  DEFAULT NULL,
  p_description              VARCHAR2  DEFAULT NULL,
  p_rationale                VARCHAR2  DEFAULT NULL,
  p_fixtext                  VARCHAR2  DEFAULT NULL,
  p_warning                  VARCHAR2  DEFAULT NULL,
  p_message                  VARCHAR2  DEFAULT NULL,
  p_clear_message            VARCHAR2  DEFAULT NULL,
  p_severity                 NUMBER    DEFAULT MGMT_CONFIG_STD.G_VIOL_SEVERITY_INFO,
  p_policy_guid              RAW       DEFAULT NULL,
  p_change_advisor_tag       MGMT_CS_CHANGE_ADVISOR_DEF DEFAULT NULL,
  p_simple_test_condition    NUMBER    DEFAULT NULL
  ) RETURN RAW
IS
  l_rule_guid              RAW(16);
  l_change_advisor_type    VARCHAR2(256) := NULL;
  l_change_advisor_subtype VARCHAR2(256) := NULL;
  l_change_advisor_reason  VARCHAR2(256) := NULL;
BEGIN

  IF ( p_change_advisor_tag IS NOT NULL ) THEN
    l_change_advisor_type     :=  p_change_advisor_tag.change_advisor_type;
    l_change_advisor_subtype  :=  p_change_advisor_tag.change_advisor_subtype;
    l_change_advisor_reason   :=  p_change_advisor_tag.change_advisor_reason;
  END IF;

  INSERT INTO mgmt_cs_rule (cs_guid, rule_iname, rule_dname,
      importance_level, description, reference_url, 
      rationale, fixtext, warning,
      test_type, test, policy_guid, 
      change_advisor_type, change_advisor_subtype, change_advisor_reason,
      message, clear_message, severity, simple_test_condition)
  VALUES (p_cs_guid, p_rule_iname, p_rule_dname,
      p_importance_level, p_description, p_reference_url,
      p_rationale, p_fixtext, p_warning,
      p_test_type, p_test, p_policy_guid,
      l_change_advisor_type,l_change_advisor_subtype,l_change_advisor_reason,
      p_message, p_clear_message, p_severity, p_simple_test_condition) 
  RETURNING rule_guid INTO l_rule_guid ;

  INSERT INTO mgmt_cs_hierarchy (parent_guid, child_guid,
      child_type, child_position) 
  VALUES (p_parent_guid, l_rule_guid,
      MGMT_CONFIG_STD.G_ELEMENT_RULE, p_child_position);
      
  RETURN l_rule_guid;
END add_rule;

PROCEDURE add_simple_test(
  p_rule_guid                RAW,
  p_property                 VARCHAR2,
  p_operator                 NUMBER,
  p_value                    VARCHAR2,
  p_position                 NUMBER
  )
IS
BEGIN
    INSERT INTO mgmt_cs_rule_simple_test(
     rule_guid, property, operator, value, position)
    VALUES (
     p_rule_guid, p_property, p_operator, p_value, p_position);

END add_simple_test ;
   
PROCEDURE add_fixlink(
  p_rule_guid                RAW,
  p_display_label            VARCHAR2,
  p_link_template            VARCHAR2,
  p_link_encode              NUMBER,
  p_is_link_empage           NUMBER
  )
IS
BEGIN

    INSERT INTO mgmt_cs_rule_fix_link(
    rule_guid, display_label, link_template,
    link_encode,is_link_em_page )
    VALUES (
    p_rule_guid,p_display_label,p_link_template,
    p_link_encode, p_is_link_empage);

END add_fixlink ;

-- Add keyword to configuration standard.
PROCEDURE add_keyword(
  p_cs_guid                  RAW,
  p_keyword                  VARCHAR2
  )
IS
BEGIN

    INSERT INTO mgmt_cs_keyword (keyword, cs_guid) 
    VALUES (p_keyword, p_cs_guid);

END add_keyword;

PROCEDURE add_reusable_query(
    p_cs_guid                RAW,
    p_query_iname            VARCHAR2,
    p_description            VARCHAR2  DEFAULT NULL,
    p_query                  VARCHAR2
)
IS
BEGIN

   INSERT INTO mgmt_cs_reusable_query (cs_guid, 
      reusable_query_iname, description, reusable_query)
   VALUES(p_cs_guid, 
      p_query_iname, p_description, p_query);

END add_reusable_query;


PROCEDURE add_overridden_parameter(
    p_cs_inclusion_guid      RAW,
    p_name                   VARCHAR2,
    p_value                  VARCHAR2,
    p_reference              VARCHAR2  DEFAULT NULL
)
IS
BEGIN

   INSERT INTO mgmt_cs_inclusion_parameter (cs_inclusion_guid, 
      param_iname, param_override_value, ref_param_iname)
   VALUES( p_cs_inclusion_guid,
      p_name, p_value, p_reference);

END add_overridden_parameter;

PROCEDURE add_violation_context (
    p_rule_guid              RAW,
    p_column_iname           VARCHAR2,
    p_column_dname           VARCHAR2,
    p_column_type            NUMBER,
    p_column_position        NUMBER,
    p_is_key                 NUMBER,
    p_is_hidden              NUMBER,
    p_link_template          VARCHAR2,
    p_link_encode            NUMBER,
    p_is_empage              NUMBER
 )
 IS
 BEGIN

   INSERT INTO mgmt_cs_rule_viol_ctx (rule_guid, 
       column_iname, column_dname, column_type, column_position, is_key, 
       is_hidden, link_template,link_encode, is_link_em_page) 
   VALUES( p_rule_guid,
       p_column_iname, p_column_dname, p_column_type, p_column_position, p_is_key, 
       p_is_hidden, p_link_template, p_link_encode, p_is_empage);

 END add_violation_context; 

PROCEDURE add_parameter(
    p_cs_guid                RAW,
    p_param_iname            VARCHAR2,
    p_description            VARCHAR2  DEFAULT NULL,
    p_param_type             NUMBER    DEFAULT MGMT_CONFIG_STD.G_STD_PARAM_NUMTYPE,
    p_param_value            VARCHAR2,
    p_prohibit_changes       NUMBER    DEFAULT MGMT_CONFIG_STD.G_STD_PARAM_PROCHANGE_FALSE,
    p_lower_bound            NUMBER    DEFAULT 0,
    p_upper_bound            NUMBER    DEFAULT 0,
    p_choice_list            MGMT_CS_CHOICE_LIST_ARRAY DEFAULT NULL,
    p_must_match             NUMBER    DEFAULT 0
  )
IS
    l_choice                 VARCHAR2(1024);
    l_param_guid             RAW(16);
    choice_ctr               NUMBER;
BEGIN

   INSERT INTO mgmt_cs_parameter (cs_guid, param_iname,
      param_type,param_value,
      description,prohibit_changes, must_match,
      lower_bound, upper_bound)
   VALUES(p_cs_guid, p_param_iname,
      p_param_type, p_param_value, 
      p_description, p_prohibit_changes,p_must_match,
      p_lower_bound, p_upper_bound)
      RETURNING param_guid INTO l_param_guid ;


  -- Insert into parameters
  IF ((p_choice_list IS NOT NULL) AND (p_choice_list.COUNT > 0))
  THEN
    FOR choice_ctr IN p_choice_list.FIRST..p_choice_list.LAST
    LOOP
	l_choice  :=  p_choice_list(choice_ctr);

	INSERT INTO mgmt_cs_parameter_choices( param_guid, choice_value )
	VALUES (  l_param_guid , l_choice);
    END LOOP;
  END IF;
      
END add_parameter;

PROCEDURE delete_cs_data(
      p_cs_guid        RAW)
  IS
    l_delete_stmt   VARCHAR2(4000);
    l_counter       INTEGER :=0;
  BEGIN

    -- Remove from all tables that have cs guid
    FOR crec IN ( SELECT o.name table_name, tgc.name target_column_name,
                         mgc.name cs_column_name
                    FROM sys.obj$ o, sys.tab$ t, sys.col$ tgc, sys.col$ mgc
                   WHERE o.owner# = userenv('SCHEMAID')
                     AND tgc.name like ('%TARGET_GUID%')
                     AND tgc.obj# = o.obj#
                     AND mgc.name like ('%CS_GUID%')
                     AND mgc.obj# = o.obj#
                     AND o.name like ('MGMT_%')
                     AND o.obj# = t.obj#
                     AND bitand(t.property, 1) = 0
                     AND bitand(tgc.property,32) = 0
                     AND bitand(tgc.property,512) = 0
                     AND bitand(mgc.property,32) = 0
                     AND bitand(mgc.property,512) = 0
                   ORDER BY o.name )
    LOOP
      l_delete_stmt := 'DELETE FROM ' || EM_CHECK.simple_sql_name(crec.table_name) ||
                       ' WHERE ' || EM_CHECK.simple_sql_name(crec.cs_column_name) || ' = HEXTORAW(:cs_guid)' ||
                       '   AND ROWNUM <= :MAX_COMMIT_SIZE' ;

      LOOP
        EXECUTE IMMEDIATE l_delete_stmt USING p_cs_guid, mgmt_global.MAX_COMMIT;
        l_counter := SQL%ROWCOUNT;

        COMMIT;
        IF l_counter < mgmt_global.MAX_COMMIT THEN
          EXIT;
        END IF;

      END LOOP;
    END LOOP;


  END delete_cs_data;

PROCEDURE delete_cs_metadata(
      p_cs_guid        RAW)
  IS

  BEGIN
    -- Remove all copied policies
    remove_copied_policies(p_cs_guid);

    DELETE FROM mgmt_cs_keyword
     WHERE cs_guid = p_cs_guid; 
  
    DELETE FROM mgmt_cs_hierarchy
    WHERE child_guid in 
            (select rule_guid from mgmt_cs_rule
                      where cs_guid = p_cs_guid
             union all
             select rulefolder_guid from mgmt_cs_rulefolder
                      where cs_guid = p_cs_guid
	     union all
	     select cs_inclusion_guid from mgmt_cs_inclusion
		      where parent_cs_guid = p_cs_guid );

    -- first clear the choices table and then clean up parameters.
    DELETE FROM mgmt_cs_parameter_choices 
	WHERE param_guid IN 
	    ( SELECT param_guid FROM mgmt_cs_parameter 
	      WHERE cs_guid = p_cs_guid );

    DELETE FROM mgmt_cs_parameter
     WHERE cs_guid = p_cs_guid; 

    DELETE FROM mgmt_cs_reusable_query
     WHERE cs_guid = p_cs_guid; 

    DELETE FROM mgmt_cs_inclusion_parameter 
	WHERE cs_inclusion_guid IN 
	    ( SELECT cs_inclusion_guid FROM mgmt_cs_inclusion 
	      WHERE parent_cs_guid = p_cs_guid );

    DELETE FROM mgmt_cs_rule_fix_link 
	WHERE rule_guid IN 
	    ( SELECT rule_guid FROM mgmt_cs_rule
	      WHERE cs_guid = p_cs_guid AND TEST_TYPE = MGMT_CONFIG_STD.G_TEST_TYPE_DMQL);

    DELETE FROM mgmt_cs_rule_viol_ctx 
	WHERE rule_guid IN 
	    ( SELECT rule_guid FROM mgmt_cs_rule
	      WHERE cs_guid = p_cs_guid AND TEST_TYPE = MGMT_CONFIG_STD.G_TEST_TYPE_DMQL);

    DELETE FROM mgmt_cs_rule_simple_test 
	WHERE rule_guid IN 
	    ( SELECT rule_guid FROM mgmt_cs_rule
	      WHERE cs_guid = p_cs_guid);

    DELETE FROM mgmt_cs_inclusion
     WHERE parent_cs_guid = p_cs_guid; 

    DELETE FROM mgmt_cs_rule
     WHERE cs_guid = p_cs_guid; 

    DELETE FROM mgmt_cs_rulefolder
     WHERE cs_guid = p_cs_guid; 

    DELETE FROM mgmt_cs_config_standard
     WHERE cs_guid = p_cs_guid; 

  END delete_cs_metadata; 

PROCEDURE remove_copied_policies(
      p_cs_guid        RAW)
  IS
    l_policy_names   MGMT_MEDIUM_STRING_TABLE := MGMT_MEDIUM_STRING_TABLE();
    l_target_types   MGMT_MEDIUM_STRING_TABLE := MGMT_MEDIUM_STRING_TABLE();

    l_policy_name    mgmt_policies.policy_name%TYPE;
    l_target_type    mgmt_policies.target_type%TYPE;
  BEGIN

    SELECT p.policy_name, p.target_type 
    BULK COLLECT INTO l_policy_names, l_target_types 
    FROM mgmt_cs_rule r, mgmt_policies p
    WHERE r.cs_guid = p_cs_guid 
      AND p.policy_guid = r.policy_guid;  

    IF ((l_policy_names IS NOT NULL) AND (l_policy_names.COUNT > 0))
    THEN
      FOR ctr IN l_policy_names.FIRST..l_policy_names.LAST
      LOOP
        l_policy_name := l_policy_names(ctr);    
        l_target_type := l_target_types(ctr);

        MGMT_POLICY.delete_policy(p_target_type => l_target_type,
                                  p_policy_name => l_policy_name);
      END LOOP;
    END IF;

  END remove_copied_policies;

--
-- PURPOSE
--
-- To set the repository timing flag of the given configuration standard.This flag
-- is to enable/disable logging the performance details of the cs evaluation in the
-- the system performance logs.
--
-- Parameters
-- p_config_std_name : Configuration Standard internal name
-- p_config_std_author: Author of the configuration standard
-- p_version : Version of the configuration standard.
-- p_enabled: Enabled/disabled flag . (0/1).

PROCEDURE set_repo_timing(
  p_config_std_name     IN VARCHAR2,
  p_config_std_author   IN VARCHAR2,
  p_version             IN NUMBER DEFAULT 1,
  p_enabled             IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE)
IS
  BEGIN
    em_check.check_range(p_value=>p_enabled,
                         p_min_value=>0,
                         p_max_value=>1,
                         p_param_name=>' p_enabled ') ;

    UPDATE mgmt_cs_config_standard
       SET repo_timing_enabled = p_enabled
    WHERE cs_iname = p_config_std_name AND
          author = p_config_std_author AND        
          version = p_version ;

    IF SQL%NOTFOUND
    THEN
      raise_application_error(MGMT_GLOBAL.CS_DOES_NOT_EXIST_ERR,
        MGMT_GLOBAL.CS_DOES_NOT_EXIST_ERR_M||'Config Std Name='|| p_config_std_name || ' author=' 
        || p_config_std_author || ' version='|| p_version);
    END IF ; 
END set_repo_timing;  


  -- ---------------------------------------------------
  -- Procedure: LOG_CS_EVALUATION_RESULTS
  -- 
  -- Description: 
  --     
  --     Called by MGMT_CONFIG_STD.EVALUATE_CONFIG_STD
  --     and from Java Classes.
  --
  --     After all CS rules have been evaluated, summary
  --     counts and the compliance score are calculated
  --     for all targets.  The results of these 
  --     calculations are stored in MGMT_CS_EVAL_SUMM_* tables.
  -- 
  -- Parameters:
  -- 
  --     p_config_std_name - Configuration Standard Name
  --     p_rqs_guid - CS Runtime Query Set Guid
  --     p_cs_guid - Configuration Standard Guid
  --     p_target_type - target type
  --     p_targets_list - MGMT_TARGET_ARRAY having the list of MGMT_TARGET_OBJ
  --     p_repo_timing_enabled - Flag to turn on/off performance data logging.
  --     p_start_time - time the evaluation started
  --
  -- NOTE: 
  --     p_targets_list and p_start_time are NULL for DMQL CS.
  --       These values are retrieved from repository tables
  --       for this case.
  -- ---------------------------------------------------

  PROCEDURE log_cs_evaluation_results( 
              p_config_std_name       IN VARCHAR2, 
              p_rqs_guid              IN RAW,   
              p_cs_guid               IN RAW,   
              p_target_type           IN VARCHAR2,
              p_targets_list          IN MGMT_TARGET_ARRAY DEFAULT NULL,
              p_repo_timing_enabled   IN NUMBER,
              p_start_time            IN DATE DEFAULT NULL)
  IS  

    TYPE l_targets_cur IS REF CURSOR;

    l_targets      GUID_ARRAY;
    l_targets_sql  l_targets_cur;

    l_start_time  DATE    := p_start_time;
    l_proceed     BOOLEAN := TRUE;  
    l_cs_type     NUMBER(1);
    l_target_guid RAW(16); 

    l_total_rules_evaluated NUMBER  := 0;
    l_rules_evaluated       NUMBER  := 0;

  BEGIN  

    IF EMDW_LOG.P_IS_DEBUG_SET THEN
      EMDW_LOG.DEBUG('log_cs_evaluation_results:Enter',G_MODULE_NAME) ;      
    END IF ;   

    -- Get the Type of the CS rules: Policy or DMQL
    -- Currrently all CS rules must be of the same type
    BEGIN
      SELECT max(test_type), count(rule_iname) 
        INTO l_cs_type, l_total_rules_evaluated
        FROM mgmt_cs_rule
        WHERE cs_guid=p_cs_guid
        GROUP BY cs_guid; 

      EXCEPTION WHEN NO_DATA_FOUND THEN
        EMDW_LOG.ERROR('log_cs_evaluation_results: Error: ' || 
                       'The CS ' || p_cs_guid || ' has no associated rules.',
                       G_MODULE_NAME);
        l_proceed := FALSE;
    END;

    -- Get the List of Targets
    IF l_cs_type = mgmt_config_std.G_TEST_TYPE_DMQL THEN
        EMDW_LOG.ERROR('log_cs_evaluation_results: Error: The type was DMQL.',
                       G_MODULE_NAME);
        l_proceed := FALSE;
    ELSE
      OPEN l_targets_sql FOR       
        SELECT t.target_guid 
        FROM   MGMT_TARGETS t, TABLE(CAST(p_targets_list as MGMT_TARGET_ARRAY)) p
        WHERE  t.target_name = p.target_name 
        AND    t.target_type = p.target_type;
    END IF;

    FETCH l_targets_sql BULK COLLECT INTO l_targets;
    CLOSE l_targets_sql;

    -- Check for No Targets
    IF (l_targets IS NULL OR l_targets.COUNT <= 0) THEN
       EMDW_LOG.ERROR('log_cs_evaluation_results: Error: ' || 
                      'No Targets were evaluated for CS ' || p_cs_guid,
                      G_MODULE_NAME);
       l_proceed := FALSE;
    END IF;

    IF (l_proceed = TRUE) THEN

      -- POLICY CS ONLY: Save rule summary info for all rules and targets
      IF (l_cs_type = mgmt_config_std.G_TEST_TYPE_POLICY) THEN
        MERGE INTO
          mgmt_cs_eval_summ_rule e
        USING
          (SELECT 
             rt.target_guid AS target_guid,
             rt.rule_guid AS rule_guid,
             nvl(p.total_violations_logged, 0) AS total_violations,
             nvl(p.last_evaluation_date, l_start_time) AS evaluation_date,
             nvl(err.status,
                  case nvl(p.total_violations_logged, -1)
                    when -1 then mgmt_config_std.G_UNKNOWN_RULES
                    when 0 then mgmt_config_std.G_COMPLIANT_RULES
                    else mgmt_config_std.G_NONCOMPLIANT_RULES end) AS status
           FROM  
             mgmt_policy_assoc_eval_summ p,
             (SELECT 
                ar.rule_guid AS rule_guid, 
                ar.policy_guid AS policy_guid, 
                at.column_value AS target_guid
              FROM mgmt_cs_rule ar, TABLE(cast(l_targets AS GUID_ARRAY)) at
              WHERE ar.cs_guid = p_cs_guid) rt,
             (SELECT 
                distinct policy_guid, target_guid, 
                mgmt_config_std.G_ERROR_RULES AS status 
              FROM  mgmt_policy_assoc_eval_details
              WHERE last_error_message IS NOT NULL) err
            WHERE rt.policy_guid = p.policy_guid(+) 
            AND   rt.target_guid = p.target_guid(+)
            AND   p.policy_guid = err.policy_guid(+)
            AND   p.target_guid = err.target_guid(+)) t            
        ON (e.rqs_guid = p_rqs_guid
            AND t.target_guid = e.target_guid
            AND t.rule_guid = e.rule_guid)
        WHEN MATCHED THEN        
          UPDATE SET total_violations = t.total_violations,
            evaluation_date=t.evaluation_date, status = t.status
        WHEN NOT MATCHED THEN
          INSERT 
              (rqs_guid,target_guid,rule_guid,root_cs_guid,
               root_target_guid,total_violations,status,evaluation_date)        
            VALUES 
              (p_rqs_guid, t.target_guid,t.rule_guid,p_cs_guid,
               t.target_guid, t.total_violations,t.status,t.evaluation_date); 
      END IF;

      -- Calculate Rule Folder Counts
      process_rule_folders(
            p_rqs_guid => p_rqs_guid,
            p_cs_guid => p_cs_guid,
            p_targets => l_targets); 

      -- Calculate CS Counts and Compliance Score
      process_cs(
            p_rqs_guid => p_rqs_guid,
            p_cs_guid => p_cs_guid,
            p_targets => l_targets,
            p_eval_date => l_start_time); 
        
      -- Performance Numbers      
      IF p_repo_timing_enabled = MGMT_GLOBAL.G_TRUE THEN            
        MGMT_LOG.log_performance
          (v_job_name_in    => G_MODULE_NAME,
           v_duration_in    => (SYSDATE  - l_start_time)*86400000,
           v_time_in        => SYSDATE,
           v_is_total_in    => 'Y',
           v_name_in        => 'Total rules evaluated for all selected targets' ,            
           v_value_in       => (l_total_rules_evaluated * l_targets.COUNT),
           v_client_data_in => p_config_std_name) ;
      END IF ; 
     
    END IF;   
  
    IF EMDW_LOG.P_IS_DEBUG_SET THEN
      EMDW_LOG.DEBUG('log_cs_evaluation_results:Exit Normal',G_MODULE_NAME) ;        
    END IF ;  
  
  END log_cs_evaluation_results;


  -- ---------------------------------------------------
  -- Procedure: PROCESS_RULE_FOLDERS
  -- 
  -- Description: 
  --
  --     Calculates CS evaluation results for all rule
  --     folders.  Rule Folders deepest in the hierarchy
  --     are evaluated evaluated first.  This is because
  --     all the child rule folder counts are needed to get
  --     the values for a parent rule folder.
  -- 
  -- Parameters:
  --
  --     p_rqs_guid - CS Runtime Query Set Guid
  --     p_cs_guid - Unique ID for the CS
  --     p_targets - table of evaluated targets 
  --
  -- ---------------------------------------------------

  PROCEDURE process_rule_folders(
              p_rqs_guid                IN RAW,
              p_cs_guid                 IN RAW, 
              p_targets                 IN GUID_ARRAY)
  IS

    CURSOR l_getFoldersCursor IS
      SELECT rf.child_guid
        FROM (SELECT child_guid, child_type, LEVEL as child_level
                FROM mgmt_cs_hierarchy 
                START WITH parent_guid=p_cs_guid
                CONNECT BY PRIOR child_guid = parent_guid) rf
        WHERE rf.child_type = mgmt_config_std.G_ELEMENT_RULEFOLDER
        ORDER BY rf.child_level DESC;

    l_folders  GUID_ARRAY;
    l_folder_guid  RAW(16);

  BEGIN
 
    -- Get all rule folders, lowest folders first..
    OPEN l_getFoldersCursor;
    FETCH l_getFoldersCursor BULK COLLECT INTO l_folders;
    CLOSE l_getFoldersCursor;

    -- NOTE: Rule Folders are processessed one at a time, rule
    --       folders with the greatest LEVEL values must be 
    --       processed first.
    IF ((l_folders IS NOT NULL) AND (l_folders.COUNT > 0)) THEN
      FOR i IN l_folders.FIRST..l_folders.LAST LOOP

        l_folder_guid := l_folders(i);

        -- MERGE all data for this rule folder into 
        -- MGMT_CS_EVAL_SUMM_RULEFOLDER for all targets
        MERGE INTO
          mgmt_cs_eval_summ_rulefolder e
        USING
          (SELECT 
             c.target_guid, c.root_cs_guid, c.root_target_guid,
             sum(c.compliant_rules) as compliant_rules,
             sum(c.non_compliant_rules) as non_compliant_rules,
             sum(c.error_rules) as error_rules,
             sum(c.unknown_rules) as unknown_rules,
             sum(c.crit_violations) as crit_violations,
             sum(c.warn_violations) as warn_violations,
             sum(c.info_violations) as info_violations
            FROM
             (SELECT 
                  s.target_guid, s.root_cs_guid, s.root_target_guid,
                  decode(s.status, mgmt_config_std.G_COMPLIANT_RULES, 1, 0)
                    as compliant_rules,
                  decode(s.status, mgmt_config_std.G_NONCOMPLIANT_RULES, 1, 0)
                    as non_compliant_rules,
                  decode(s.status, mgmt_config_std.G_ERROR_RULES, 1, 0)
                     as error_rules,
                  decode(s.status, mgmt_config_std.G_UNKNOWN_RULES, 1, 0)
                     as unknown_rules,
                  decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_CRITICAL, s.total_violations, 0) 
                     as crit_violations,
                  decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_WARNING, s.total_violations, 0) 
                     as warn_violations,
                  decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_INFO, s.total_violations, 0) 
                     as info_violations
                FROM
                   mgmt_cs_eval_summ_rule s,
                   mgmt_cs_rule r,
                   TABLE(CAST(p_targets as GUID_ARRAY)) trg,
                   (SELECT child_guid as rule_guid
                      FROM  mgmt_cs_hierarchy
                      WHERE parent_guid = l_folder_guid
                      AND   child_type =  mgmt_config_std.G_ELEMENT_RULE) chld
                WHERE s.rqs_guid = p_rqs_guid 
                AND   s.target_guid = trg.column_value 
                AND   s.rule_guid = chld.rule_guid
                AND   r.rule_guid = s.rule_guid
              UNION ALL
              SELECT 
                  sf.target_guid, sf.root_cs_guid, sf.root_target_guid,
                  sf.compliant_rules,
                  sf.non_compliant_rules,
                  sf.error_rules,
                  sf.unknown_rules,
                  sf.crit_violations,
                  sf.warn_violations,
                  sf.info_violations
                FROM   
                  mgmt_cs_eval_summ_rulefolder sf,
                  TABLE(CAST(p_targets as GUID_ARRAY)) trg,
                  (SELECT child_guid as rulefolder_guid
                     FROM  mgmt_cs_hierarchy
                     WHERE parent_guid = l_folder_guid
                     AND   child_type = mgmt_config_std.G_ELEMENT_RULEFOLDER) chld
                WHERE sf.rqs_guid = p_rqs_guid 
                AND   sf.target_guid = trg.column_value 
                AND   sf.rulefolder_guid = chld.rulefolder_guid) c
            GROUP BY c.target_guid, c.root_cs_guid, c.root_target_guid) t            
        ON (e.rulefolder_guid = l_folder_guid
            AND e.rqs_guid = p_rqs_guid
            AND e.target_guid = t.target_guid)
        WHEN MATCHED THEN        
          UPDATE SET compliant_rules = t.compliant_rules,
                     non_compliant_rules = t.non_compliant_rules,
                     error_rules = t.error_rules,
                     unknown_rules = t.unknown_rules,
                     crit_violations = t.crit_violations,
                     warn_violations = t.warn_violations,
                     info_violations = t.info_violations
        WHEN NOT MATCHED THEN
          INSERT 
              (rqs_guid,target_guid,rulefolder_guid,root_cs_guid,
               root_target_guid,compliant_rules, non_compliant_rules,
               error_rules, unknown_rules, 
               crit_violations, warn_violations, info_violations)        
            VALUES 
              (p_rqs_guid, t.target_guid,l_folder_guid,t.root_cs_guid,
               t.root_target_guid, t.compliant_rules, t.non_compliant_rules,
               t.error_rules, t.unknown_rules, 
               t.crit_violations, t.warn_violations, t.info_violations); 
      END LOOP;
    END IF;
 
  END process_rule_folders;

  -- ---------------------------------------------------
  -- Procedure: PROCESS_CS
  -- 
  -- Description: 
  --
  --     Calculates CS counts and compliance score for
  --     all targets.
  -- 
  -- Parameters:
  --
  --     p_rqs_guid - CS Runtime Query Set Guid
  --     p_cs_guid - Unique ID for the CS
  --     p_targets - table of evaluated targets 
  --     p_eval_date - cs evaluation date
  --
  -- ---------------------------------------------------

  PROCEDURE process_cs(
              p_rqs_guid                IN RAW,
              p_cs_guid                 IN RAW, 
              p_targets                 IN GUID_ARRAY,
              p_eval_date               IN DATE)
  IS
  BEGIN

    -- MERGE CS data for all targets into the MGMT_CS_EVAL_SUMM_RQS table
    -- NOTE: the USING query ignores all rules that have an importance_level
    --       of not scored.  It does this, because importance_level of 
    --       not scored is 0 and summing 0 or multiplying by 0 = 0, and 0
    --       means ignored.  So, if G_IMPORTANCE_NOT_SCORED changes from 0
    --       to another value, this query will break and the refrences to
    --       r.importance_level will need to be wrapped in a CASE statement
    --       to force not scored rules to be ignored.
    MERGE INTO
      mgmt_cs_eval_summ_rqs e
    USING
      (SELECT 
         c.target_guid, c.root_cs_guid, c.root_target_guid,
         c.compliant_rules, c.non_compliant_rules,
         c.error_rules, c.unknown_rules, 
         c.crit_violations, c.warn_violations, c.info_violations,
         (CASE WHEN c.importance_total = 0 THEN 0
           ELSE (c.weighted_total/c.importance_total) END)
           as compliance_score
       FROM 
         (SELECT 
            s.target_guid, s.root_cs_guid, s.root_target_guid,
            sum(CASE WHEN s.status = mgmt_config_std.G_COMPLIANT_RULES THEN 1
                ELSE 0 END) as compliant_rules,
            sum(CASE WHEN s.status = mgmt_config_std.G_NONCOMPLIANT_RULES THEN 1
                ELSE 0 END) as non_compliant_rules,
            sum(CASE WHEN s.status = mgmt_config_std.G_ERROR_RULES THEN 1
                ELSE 0 END) as error_rules,
            sum(CASE WHEN s.status = mgmt_config_std.G_UNKNOWN_RULES THEN 1
                ELSE 0 END) as unknown_rules,
            sum(decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_CRITICAL, s.total_violations, 0))
               as crit_violations,
            sum(decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_WARNING, s.total_violations, 0))
               as warn_violations,
            sum(decode(r.severity, mgmt_config_std.G_VIOL_SEVERITY_INFO, s.total_violations, 0))
               as info_violations,
            sum(CASE WHEN s.status = mgmt_config_std.G_COMPLIANT_RULES THEN 
                  r.importance_level
                WHEN s.status = mgmt_config_std.G_NONCOMPLIANT_RULES THEN
                  r.importance_level
                ELSE 0 END) as importance_total,
            sum(CASE WHEN s.status = mgmt_config_std.G_COMPLIANT_RULES THEN
                     (r.importance_level * 100)
                ELSE 0 END) as weighted_total
         FROM  mgmt_cs_rule r, mgmt_cs_eval_summ_rule s,
               TABLE(CAST(p_targets as GUID_ARRAY)) trg
         WHERE r.cs_guid = p_cs_guid
         AND   s.rqs_guid = p_rqs_guid
         AND   s.target_guid = trg.column_value
         AND   r.rule_guid = s.rule_guid 
         GROUP BY s.rqs_guid, s.target_guid, 
                  s.root_cs_guid, s.root_target_guid) c
       ) t            
    ON (e.rqs_guid = p_rqs_guid 
        AND e.target_guid = t.target_guid)
    WHEN MATCHED THEN        
      UPDATE SET compliant_rules = t.compliant_rules,
                 non_compliant_rules = t.non_compliant_rules,
                 error_rules = t.error_rules,
                 unknown_rules = t.unknown_rules,
                 crit_violations = t.crit_violations,
                 warn_violations = t.warn_violations,
                 info_violations = t.info_violations,
                 last_evaluation_date = p_eval_date,
                 compliance_score = t.compliance_score
    WHEN NOT MATCHED THEN
      INSERT 
        (rqs_guid, target_guid,root_cs_guid, root_target_guid,
         compliant_rules, non_compliant_rules, error_rules, 
         unknown_rules, crit_violations, warn_violations, info_violations, last_evaluation_date, 
         compliance_score)        
      VALUES 
         (p_rqs_guid, t.target_guid,t.root_cs_guid, t.root_target_guid,
          t.compliant_rules, t.non_compliant_rules, t.error_rules,
          t.unknown_rules, t.crit_violations, t.warn_violations, t.info_violations, p_eval_date,
          t.compliance_score); 

  END process_cs;


-- ----------------------------------------------------
-- Compliance trend overview charts related procedures.
-- ----------------------------------------------------

--
-- PURPOSE
-- Returns the min and max timestamps for data availability for the specified
-- metric.
--
-- Parameters
-- p_rqs_guid : Guid of config std.
-- p_object_guid : Guid of config std/rule folder/rule for which trend data is required.
-- p_metric_name : Name of metric for which trend data to be shown
-- p_metric_column : Name of metric column for which trend data to be shown
-- p_target_name : Target for which trend data to be shown
-- p_min_collection_time: Min data availablity
-- p_max_collection_time: Max data availablity
--
PROCEDURE get_min_max_collection_time(      
  p_rqs_guid                            IN RAW,  
  p_object_guid                         IN RAW,  
  p_metric_name                         IN VARCHAR2,
  p_metric_column                       IN VARCHAR2,
  p_target_name                         IN VARCHAR2 DEFAULT '%',
  p_min_collection_time                 OUT DATE,
  p_max_collection_time                 OUT DATE
  )
IS  
  l_repos_tgt_guid mgmt_targets.target_guid%TYPE;
  
BEGIN
  l_repos_tgt_guid := get_repos_target_guid();  
  
  SELECT MIN_TIME, MAX_TIME INTO p_min_collection_time, p_max_collection_time
  FROM 
    (with metric_info as 
      (select l_repos_tgt_guid as target_guid, m.metric_guid, k.composite_key 
      from        
         mgmt_targets tgt,
         mgmt_metrics m,
         mgmt_metrics_composite_keys k 
      where       
         m.target_type = 'oracle_emrep' and 
         m.metric_name =  p_metric_name and 
         m.metric_column = p_metric_column and 
         k.target_guid = l_repos_tgt_guid and 
         k.key_part1_value = p_rqs_guid and          
         k.key_part2_value = p_object_guid and 
         k.key_part3_value = tgt.target_guid and 
         tgt.target_name like p_target_name
      ) 
      select min(t) as MIN_TIME, max(t) as MAX_TIME from 
      ( 
        select rm.collection_timestamp as t 
        from metric_info, mgmt_metrics_raw rm 
        where 
            rm.target_guid = metric_info.target_guid and 
            rm.metric_guid = metric_info.metric_guid and 
            rm.key_value = metric_info.composite_key 
        union all 
        select hm.rollup_timestamp as t 
        from metric_info, mgmt_metrics_1hour hm 
        where 
            hm.target_guid = metric_info.target_guid and 
            hm.metric_guid = metric_info.metric_guid and
            hm.key_value = metric_info.composite_key 
        union all 
        select dm.rollup_timestamp as t 
        from metric_info, mgmt_metrics_1day dm 
        where 
            dm.target_guid = metric_info.target_guid and 
            dm.metric_guid = metric_info.metric_guid and 
            dm.key_value = metric_info.composite_key 
     ));

END get_min_max_collection_time;

--
-- Returns the trend(historic) data for a given configuration standard and
-- time period.
--
-- Parameters
-- p_rqs_guid : Guid of config standard for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate   : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
-- p_comp_score_buckets_cursor: Cursor for compliance score buckets chart
--
PROCEDURE get_cs_trend_data( 
  p_rqs_guid                            IN RAW,  
  p_target_name                         IN VARCHAR2 DEFAULT '%',  
  p_historyForNDays                     IN NUMBER DEFAULT NULL,  
  p_historyStartDate                    IN DATE DEFAULT NULL,
  p_historyEndDate                      IN DATE DEFAULT NULL,  
  p_eval_summ_cursor                    OUT sys_refcursor,    
  p_comp_score_buckets_cursor           OUT sys_refcursor
  )
IS  
  l_table_type      NUMBER := G_RAW_TABLE_TYPE;  
  l_table_name      VARCHAR2(30);
  l_repos_tgt_guid  mgmt_targets.target_guid%TYPE;
  l_time_range      VARCHAR2(2000);
  l_summ_sql        VARCHAR2(32767);
  l_bucket_sql      VARCHAR2(32767);
  l_inDays          BOOLEAN;
  
BEGIN
  l_repos_tgt_guid := get_repos_target_guid(); 
  l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);      

  IF (p_historyForNDays IS NOT NULL)
  THEN
    l_inDays := TRUE;   
    l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);
  ELSE
    l_inDays := FALSE;   
    l_table_type := get_trend_table_type(p_historyStartDate => p_historyStartDate);
  END IF;
  
  IF (l_table_type = G_RAW_TABLE_TYPE)
  THEN
      IF (l_inDays)
      THEN
        l_time_range := 'v.collection_timestamp >= sysdate - :4';
      ELSE
        l_time_range := 'v.collection_timestamp >= :4 and v.collection_timestamp <= :5';  
      END IF;
  
      l_summ_sql := 
        'SELECT
            key.key_part1_value as rqs_guid,          
            round(avg(to_number(v.value))) as total_violations,
            round(avg(to_number(vs.value))) as compliance_score,
            count(distinct(key.key_part3_value)) as targets_evaluated,
            v.collection_timestamp as time
        FROM          
            mgmt_metrics m,
            mgmt_metrics ms,
            mgmt_metrics_raw v, 
            mgmt_metrics_raw vs,         
            mgmt_metrics_composite_keys key,           
            mgmt_targets t
        WHERE             
            m.target_type = ''oracle_emrep'' and 
            m.metric_name = ''CS_EVAL_SUMM_STANDARD'' and 
            ms.target_type = m.target_type and 
            ms.metric_name = m.metric_name and            
            m.metric_column = ''VIOLATION_COUNT'' and   
            ms.metric_column = ''COMPLIANCE_SCORE'' and                             
            v.target_guid = :1 and 
            v.metric_guid = m.metric_guid and           
            vs.target_guid = v.target_guid and 
            vs.metric_guid = ms.metric_guid and 
            key.target_guid = v.target_guid and
            key.composite_key = v.key_value and
            key.composite_key = vs.key_value and                            
            key.key_part1_value = :2 and        
            t.target_name like :3 and 
            key.key_part3_value = t.target_guid and '   
         || l_time_range || ' and
            vs.collection_timestamp = v.collection_timestamp         
        GROUP BY key.key_part1_value, v.collection_timestamp
        ORDER BY v.collection_timestamp';
        
      l_bucket_sql := 
        'WITH buckets as
        (
          SELECT
              avgs.target_guid,
              avgs.rqs_guid,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=1) then 1 else 0 end as bucket1,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=2) then 1 else 0 end as bucket2,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=3) then 1 else 0 end as bucket3,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=4) then 1 else 0 end as bucket4,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=5) then 1 else 0 end as bucket5,
              avgs.time
          FROM
              (SELECT  
                  key.key_part1_value as rqs_guid,
                  key.key_part3_value as target_guid,
                  round(avg(to_number(v.value))) as compliance_score,
                  v.collection_timestamp as time
              FROM               
                mgmt_metrics m,
                mgmt_metrics_raw v,         
                mgmt_metrics_composite_keys key,
                mgmt_targets t
              WHERE                 
                  m.target_type = ''oracle_emrep'' and 
                  m.metric_name = ''CS_EVAL_SUMM_STANDARD'' and 
                  m.metric_column = ''COMPLIANCE_SCORE'' and 
                  v.target_guid = :1  and 
                  v.metric_guid = m.metric_guid and 
                  key.target_guid = v.target_guid and
                  key.composite_key = v.key_value and
                  key.key_part3_value = t.target_guid and
                  key.key_part1_value = :2 and
                  t.target_name like :3 and '
               || l_time_range || ' 
                  GROUP BY key.key_part1_value, key.key_part3_value, v.collection_timestamp) avgs
        )
        SELECT         
            x.rqs_guid as rqs_guid,      
            SUM(x.bucket1) as bucket1,
            SUM(x.bucket2) as bucket2,
            SUM(x.bucket3) as bucket3,
            SUM(x.bucket4) as bucket4,
            SUM(x.bucket5) as bucket5,
            x.time
        FROM buckets x                    
        GROUP BY x.rqs_guid,time
        ORDER BY time';
    
  ELSE
      IF (l_inDays)
      THEN
        l_time_range := 'v.rollup_timestamp >= sysdate - :4';
      ELSE
        l_time_range := 'v.rollup_timestamp >= :4 and v.rollup_timestamp <= :5';  
      END IF;
  
      l_table_name := 
        CASE l_table_type
          WHEN G_1HOUR_TABLE_TYPE THEN 'mgmt_metrics_1hour'
          WHEN G_1DAY_TABLE_TYPE  THEN 'mgmt_metrics_1day'
          ELSE 'mgmt_metrics_1hour'
        END;
        
      l_summ_sql :=         
        'SELECT
            key.key_part1_value as rqs_guid,          
            round(avg(to_number(v.value_average))) as total_violations,
            round(avg(to_number(vs.value_average))) as compliance_score,
            count(distinct(key.key_part3_value)) as targets_evaluated,
            v.rollup_timestamp as time
        FROM          
            mgmt_metrics m,
            mgmt_metrics ms, '
        ||  l_table_name || ' v, '
        ||  l_table_name || ' vs,         
            mgmt_metrics_composite_keys key ,
            mgmt_targets t
        WHERE             
            m.target_type = ''oracle_emrep'' and 
            m.metric_name = ''CS_EVAL_SUMM_STANDARD'' and 
            ms.target_type = m.target_type and 
            ms.metric_name = m.metric_name and            
            m.metric_column = ''VIOLATION_COUNT'' and   
            ms.metric_column = ''COMPLIANCE_SCORE'' and                             
            v.target_guid = :1 and 
            v.metric_guid = m.metric_guid and           
            vs.target_guid = v.target_guid and 
            vs.metric_guid = ms.metric_guid and 
            key.target_guid = v.target_guid and
            key.composite_key = v.key_value and
            key.composite_key = vs.key_value and                            
            key.key_part1_value = :2 and        
            t.target_name like :3 and 
            key.key_part3_value = t.target_guid and '       
         || l_time_range || ' and
            vs.rollup_timestamp = v.rollup_timestamp         
        GROUP BY key.key_part1_value, v.rollup_timestamp
        ORDER BY v.rollup_timestamp';  
        
      l_bucket_sql := 
        'WITH buckets as
        (
          SELECT
              avgs.target_guid,
              avgs.rqs_guid,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=1) then 1 else 0 end as bucket1,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=2) then 1 else 0 end as bucket2,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=3) then 1 else 0 end as bucket3,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=4) then 1 else 0 end as bucket4,
              case when(width_bucket(avgs.compliance_score, 0, 101, 5)=5) then 1 else 0 end as bucket5,
              avgs.time
          FROM
              (SELECT  
                  key.key_part1_value as rqs_guid,
                  key.key_part3_value as target_guid,
                  round(avg(to_number(v.value_average))) as compliance_score,
                  v.rollup_timestamp as time
              FROM               
                mgmt_metrics m, '
            ||  l_table_name || ' v,            
                mgmt_metrics_composite_keys key,
                mgmt_targets t
              WHERE                 
                  m.target_type = ''oracle_emrep'' and 
                  m.metric_name = ''CS_EVAL_SUMM_STANDARD'' and 
                  m.metric_column = ''COMPLIANCE_SCORE'' and 
                  v.target_guid = :1  and 
                  v.metric_guid = m.metric_guid and 
                  key.target_guid = v.target_guid and
                  key.composite_key = v.key_value and
                  key.key_part3_value = t.target_guid and
                  key.key_part1_value = :2 and
                  t.target_name like :3 and '
               || l_time_range || ' 
                  GROUP BY key.key_part1_value, key.key_part3_value, v.rollup_timestamp) avgs
        )
        SELECT         
            x.rqs_guid as rqs_guid,      
            SUM(x.bucket1) as bucket1,
            SUM(x.bucket2) as bucket2,
            SUM(x.bucket3) as bucket3,
            SUM(x.bucket4) as bucket4,
            SUM(x.bucket5) as bucket5,
            x.time
        FROM buckets x                    
        GROUP BY x.rqs_guid,time
        ORDER BY time';              
  END IF;
    
  IF (l_inDays)
  THEN
      OPEN p_eval_summ_cursor FOR 
      l_summ_sql USING l_repos_tgt_guid, p_rqs_guid, p_target_name, p_historyForNDays;
       
      OPEN p_comp_score_buckets_cursor FOR
      l_bucket_sql USING l_repos_tgt_guid, p_rqs_guid, p_target_name, p_historyForNDays;
  ELSE
      OPEN p_eval_summ_cursor FOR 
      l_summ_sql USING l_repos_tgt_guid, p_rqs_guid, p_target_name, p_historyStartDate, p_historyEndDate;
       
      OPEN p_comp_score_buckets_cursor FOR
      l_bucket_sql USING l_repos_tgt_guid, p_rqs_guid, p_target_name, p_historyStartDate, p_historyEndDate;
  END IF;

END get_cs_trend_data;

--
-- PURPOSE
-- Returns the trend(historic) data for a given rule folder and time period.
--
-- Parameters
-- p_rqs_guid: Guid of the config standard for which trend data is required.
-- p_folder_guid : Guid of folder for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate   : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
--
PROCEDURE get_folder_trend_data(      
  p_rqs_guid                            IN RAW,  
  p_folder_guid                         IN RAW,  
  p_target_name                         IN VARCHAR2 DEFAULT '%',  
  p_historyForNDays                     IN NUMBER DEFAULT NULL,  
  p_historyStartDate                    IN DATE DEFAULT NULL,
  p_historyEndDate                      IN DATE DEFAULT NULL,  
  p_eval_summ_cursor                    OUT sys_refcursor
  )
IS  
  l_table_type      NUMBER := G_RAW_TABLE_TYPE;  
  l_table_name      VARCHAR2(30);
  l_repos_tgt_guid  mgmt_targets.target_guid%TYPE;
  l_time_range      VARCHAR2(2000);  
  l_summ_sql        VARCHAR2(32767);
  l_inDays          BOOLEAN;
  
BEGIN
  l_repos_tgt_guid := get_repos_target_guid(); 
  l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);   

  IF (p_historyForNDays IS NOT NULL)
  THEN
    l_inDays := TRUE;
    l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);
  ELSE
    l_inDays := FALSE;
    l_table_type := get_trend_table_type(p_historyStartDate => p_historyStartDate);
  END IF;

    
  IF (l_table_type = G_RAW_TABLE_TYPE)
  THEN
      IF (l_inDays)
      THEN
        l_time_range := 'v.collection_timestamp >= sysdate - :5';
      ELSE
        l_time_range := 'v.collection_timestamp >= :5 and v.collection_timestamp <= :6';  
      END IF;  
  
      l_summ_sql :=     
        'SELECT
            key.key_part2_value as folder_guid,          
            round(avg(to_number(v.value))) as total_violations,
            count(distinct(key.key_part3_value)) as targets_evaluated, 
            v.collection_timestamp as time
        FROM          
            mgmt_metrics m,
            mgmt_metrics_raw v, 
            mgmt_metrics_composite_keys key ,
            mgmt_targets t
        WHERE             
            m.target_type = ''oracle_emrep'' and 
            m.metric_name = ''CS_EVAL_SUMM_FOLDER'' and 
            m.metric_column = ''VIOLATION_COUNT'' and   
            v.target_guid = :1 and 
            v.metric_guid = m.metric_guid and           
            key.target_guid = v.target_guid and
            key.composite_key = v.key_value and
            key.key_part1_value = :2 and
            key.key_part2_value = :3 and        
            t.target_name like :4 and 
            key.key_part3_value = t.target_guid and '   
         || l_time_range || ' and
            v.collection_timestamp = v.collection_timestamp and
            v.collection_timestamp = v.collection_timestamp        
        GROUP BY key.key_part2_value, v.collection_timestamp
        ORDER BY v.collection_timestamp';
  ELSE
      IF (l_inDays)
      THEN
        l_time_range := 'v.rollup_timestamp >= sysdate - :5';
      ELSE
        l_time_range := 'v.rollup_timestamp >= :5 and v.rollup_timestamp <= :6';  
      END IF;
  
      l_table_name := 
        CASE l_table_type
          WHEN G_1HOUR_TABLE_TYPE THEN 'mgmt_metrics_1hour'
          WHEN G_1DAY_TABLE_TYPE  THEN 'mgmt_metrics_1day'
          ELSE 'mgmt_metrics_1hour'
        END;
        
      l_summ_sql :=       
        'SELECT
            key.key_part2_value as folder_guid,          
            round(avg(to_number(v.value_average))) as total_violations,
            count(distinct(key.key_part3_value)) as targets_evaluated, 
            v.rollup_timestamp as time
        FROM          
            mgmt_metrics m, '
         || l_table_name || ' v, 
            mgmt_metrics_composite_keys key ,
            mgmt_targets t
        WHERE             
            m.target_type = ''oracle_emrep'' and 
            m.metric_name = ''CS_EVAL_SUMM_FOLDER'' and 
            m.metric_column = ''VIOLATION_COUNT'' and   
            v.target_guid = :1 and 
            v.metric_guid = m.metric_guid and           
            key.target_guid = v.target_guid and
            key.composite_key = v.key_value and    
            key.key_part1_value = :2 and
            key.key_part2_value = :3 and        
            t.target_name like :4 and 
            key.key_part3_value = t.target_guid and '   
         || l_time_range || ' and
            v.rollup_timestamp = v.rollup_timestamp and
            v.rollup_timestamp = v.rollup_timestamp         
        GROUP BY key.key_part2_value, v.rollup_timestamp
        ORDER BY v.rollup_timestamp';  
  END IF;

  IF (l_inDays)
  THEN
      OPEN p_eval_summ_cursor FOR l_summ_sql 
      USING l_repos_tgt_guid, p_rqs_guid, p_folder_guid, p_target_name, p_historyForNDays;
  ELSE
      OPEN p_eval_summ_cursor FOR l_summ_sql 
      USING l_repos_tgt_guid, p_rqs_guid, p_folder_guid, p_target_name, p_historyStartDate, p_historyEndDate;
  END IF;

END get_folder_trend_data;

--
-- PURPOSE
-- Returns the trend(historic) data for a given rule and time period.
--
-- Parameters
-- p_rqs_guid : Guid of config standard for which trend data is required.
-- p_rule_guid : Guid of rule for which trend data is required.
-- p_target_name : Target for which trend data to be shown
-- p_historyForNDays : History for how many days?
-- p_historyStartDate : Start date
-- p_historyEndDate   : End date
-- p_eval_summ_cursor: Cursor evaluation summary trend data
--
PROCEDURE get_rule_trend_data(     
  p_rqs_guid                            IN RAW,  
  p_rule_guid                           IN RAW,  
  p_target_name                         IN VARCHAR2 DEFAULT '%',  
  p_historyForNDays                     IN NUMBER DEFAULT NULL,  
  p_historyStartDate                    IN DATE DEFAULT NULL,
  p_historyEndDate                      IN DATE DEFAULT NULL,  
  p_eval_summ_cursor                    OUT sys_refcursor
  )
IS  
  l_table_type      NUMBER := G_RAW_TABLE_TYPE;  
  l_table_name      VARCHAR2(30);
  l_repos_tgt_guid  mgmt_targets.target_guid%TYPE;
  l_time_range      VARCHAR2(2000);  
  l_summ_sql        VARCHAR2(32767);
  l_inDays          BOOLEAN;
  
BEGIN
  l_repos_tgt_guid := get_repos_target_guid(); 
  
  IF (p_historyForNDays IS NOT NULL)
  THEN
    l_inDays := TRUE;
    l_table_type := get_trend_table_type(p_historyForNDays => p_historyForNDays);
  ELSE
    l_inDays := FALSE;
    l_table_type := get_trend_table_type(p_historyStartDate => p_historyStartDate);
  END IF;
    
  IF (l_table_type = G_RAW_TABLE_TYPE)
  THEN
      IF (l_inDays)
      THEN
        l_time_range := 'v.collection_timestamp >= sysdate - :5';
      ELSE
        l_time_range := 'v.collection_timestamp >= :5 and v.collection_timestamp <= :6';  
      END IF;  
  
      l_summ_sql := 
        'SELECT
            key.key_part2_value as rule_guid,          
            round(avg(to_number(v.value))) as total_violations,
            count(distinct(key.key_part3_value)) as targets_evaluated, 
            v.collection_timestamp as time
        FROM          
            mgmt_metrics m,
            mgmt_metrics_raw v,
            mgmt_metrics_composite_keys key ,
            mgmt_targets t
        WHERE             
            m.target_type = ''oracle_emrep'' and 
            m.metric_name = ''CS_EVAL_SUMM_RULE'' and 
            m.metric_column = ''VIOLATION_COUNT'' and   
            v.target_guid = :1 and 
            v.metric_guid = m.metric_guid and           
            key.target_guid = v.target_guid and
            key.composite_key = v.key_value and  
            key.key_part1_value = :2 and
            key.key_part2_value = :3 and        
            t.target_name like :4 and 
            key.key_part3_value = t.target_guid and '   
         || l_time_range || '         
        GROUP BY key.key_part2_value, v.collection_timestamp
        ORDER BY v.collection_timestamp';
  ELSE
      IF (l_inDays)
      THEN
        l_time_range := 'v.rollup_timestamp >= sysdate - :5';
      ELSE
        l_time_range := 'v.rollup_timestamp >= :5 and v.rollup_timestamp <= :6';  
      END IF;
 
      l_table_name := 
        CASE l_table_type
          WHEN G_1HOUR_TABLE_TYPE THEN 'mgmt_metrics_1hour'
          WHEN G_1DAY_TABLE_TYPE  THEN 'mgmt_metrics_1day'
          ELSE 'mgmt_metrics_1hour'
        END;
        
      l_summ_sql := 
        'SELECT
            key.key_part2_value as rule_guid,          
            round(avg(to_number(v.value_average))) as total_violations,
            count(distinct(key.key_part3_value)) as targets_evaluated, 
            v.rollup_timestamp as time
        FROM          
            mgmt_metrics m, '
        ||  l_table_name || ' v,  
            mgmt_metrics_composite_keys key ,
            mgmt_targets t
        WHERE             
            m.target_type = ''oracle_emrep'' and 
            m.metric_name = ''CS_EVAL_SUMM_RULE'' and 
            m.metric_column = ''VIOLATION_COUNT'' and   
            v.target_guid = :1 and 
            v.metric_guid = m.metric_guid and           
            key.target_guid = v.target_guid and
            key.composite_key = v.key_value and      
            key.key_part1_value = :2 and 
            key.key_part2_value = :3 and        
            t.target_name like :4 and 
            key.key_part3_value = t.target_guid and '   
         || l_time_range || '    
        GROUP BY key.key_part2_value, v.rollup_timestamp
        ORDER BY v.rollup_timestamp';        
  END IF;

  IF (l_inDays)
  THEN
      OPEN p_eval_summ_cursor FOR l_summ_sql 
      USING l_repos_tgt_guid, p_rqs_guid, p_rule_guid, p_target_name, p_historyForNDays;
  ELSE
      OPEN p_eval_summ_cursor FOR l_summ_sql 
      USING l_repos_tgt_guid, p_rqs_guid, p_rule_guid, p_target_name, p_historyStartDate, p_historyEndDate;
  END IF;

END get_rule_trend_data;

--
-- PURPOSE
-- Returns the table type (Raw/1hour/1day) from which the historical data to be fetched.
--
-- Parameters
-- p_historyForNDays : Number of days for which historic data is required
--  
FUNCTION get_trend_table_type(
    p_historyForNDays                     IN NUMBER DEFAULT 7    
  ) RETURN NUMBER
IS    
  l_table_type              NUMBER := G_1HOUR_TABLE_TYPE;    
BEGIN  
    --More than 7 days history
    IF (p_historyForNDays > 7)
    THEN     
      l_table_type := G_1DAY_TABLE_TYPE;
    --More than 1 day history      
    ELSIF (p_historyForNDays > 1)
    THEN      
      l_table_type := G_1HOUR_TABLE_TYPE;            
    ELSE    
      l_table_type := G_RAW_TABLE_TYPE;            
    END IF;      
  RETURN l_table_type;
END get_trend_table_type;

--
-- PURPOSE
-- Returns the table type (Raw/1hour/1day) from which the historical data to be fetched.
--
-- Parameters
-- p_historyStartDate : Start date from when onwards the historic data is required.
--  
FUNCTION get_trend_table_type(
  p_historyStartDate                    IN DATE
  ) RETURN NUMBER
IS
  l_raw_retention_window    NUMBER := 0;
  l_1hour_retention_window  NUMBER := 0;  
  l_table_type              NUMBER := G_RAW_TABLE_TYPE;
  l_period                  NUMBER := 0;
    
BEGIN
  --Get the retention windows 
  l_raw_retention_window    := emd_loader.get_raw_retention_window;
  l_1hour_retention_window  := emd_loader.get_1hour_retention_window; 
    
  --If start date is given    
    IF (l_period <= (l_raw_retention_window*86400000))
      THEN     
        l_table_type := G_RAW_TABLE_TYPE;
      ELSIF (l_period <= (l_1hour_retention_window*86400000))  
      THEN        
        l_table_type := G_1HOUR_TABLE_TYPE;            
      ELSE      
        l_table_type := G_1DAY_TABLE_TYPE;
    END IF;            
    
  RETURN l_table_type;
END get_trend_table_type;

--
-- PURPOSE
--
-- To delete the evaluation results of the given configuration standard for all targets 
-- for which the user has operator target privileges.
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE delete_cs_eval_results ( p_config_std_guid RAW )              
              
IS
  l_delete_stmt   VARCHAR2(4000);  
  is_super_user   INTEGER := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;  
  
BEGIN
  
  is_super_user := MGMT_USER.has_priv(MGMT_USER.GET_CURRENT_EM_USER,MGMT_USER.SUPER_USER);  
    
  --There could be more number of records,so handle commit along the way.
  l_delete_stmt := 'DELETE FROM mgmt_cs_eval_summ_rqs ' ||
              'WHERE ' ||
                  'rqs_guid = HEXTORAW(:config_std_guid ) AND '||  
                  '((:is_super_user = :USER_HAS_PRIV) OR '|| 
                  '(MGMT_USER.has_priv(:current_em_user,:OPERATOR_TARGET,'||
                    'target_guid) = :USER_HAS_PRIV)) AND '||
                    ' ROWNUM <= :MAX_COMMIT_SIZE' ;
                
  
  --Executes the delete statement and commits the txn appropriately
  em_config_std.commit_delete_cs_txn(p_execute_stmt => l_delete_stmt,
    p_config_std_guid => p_config_std_guid,
    p_is_super_user => is_super_user);
  
  --submit job which deletes the evaluation results from rule folder and policy
  -- tables.
  submit_delete_eval_results_job(p_config_std_guid => p_config_std_guid);
  
END delete_cs_eval_results;

--
-- PURPOSE
--
-- To delete the evaluation results of the rule folder of the given config std for
-- all targets evaluated for which the user has the target operator privileges.
-- This api will be called from delete eval results job.
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE delete_eval_results_data (
          p_config_std_guid RAW)
          
IS
  l_policy_guids MGMT_TARGET_GUID_ARRAY := MGMT_TARGET_GUID_ARRAY();
  l_policy_guid mgmt_policies.policy_guid%TYPE;
  l_delete_stmt   VARCHAR2(4000);
  l_counter       INTEGER :=0;  
  l_annot_purged  NUMBER := 0;
  l_notify_log_purged NUMBER := 0;
  l_notify_rq     NUMBER := 0;

  is_super_user   INTEGER := MGMT_USER.USER_DOES_NOT_HAVE_PRIV;
  
BEGIN
    
  is_super_user := MGMT_USER.has_priv(MGMT_USER.GET_CURRENT_EM_USER,MGMT_USER.SUPER_USER);      
  --delete for all targets evaluated
  l_delete_stmt := 'DELETE FROM mgmt_cs_eval_summ_rulefolder '||
                   'WHERE '||
                      'rqs_guid = HEXTORAW(:config_std_guid) AND '||
                      '((:is_super_user = :USER_HAS_PRIV) OR '|| 
                      '(MGMT_USER.has_priv(:current_em_user,:OPERATOR_TARGET,'||
                          'target_guid) = :USER_HAS_PRIV)) AND '||
                      'ROWNUM <= :MAX_COMMIT_SIZE';
  
  
  --Executes the delete statement and commits the txn appropriately
  em_config_std.commit_delete_cs_txn(p_execute_stmt => l_delete_stmt, 
    p_config_std_guid => p_config_std_guid, 
    p_is_super_user => is_super_user);
    
  --Get the policy guids of the rules under the rule folders
  SELECT policy_guid BULK COLLECT INTO l_policy_guids
  FROM mgmt_cs_rule
  WHERE cs_guid = p_config_std_guid;
  
  IF ((l_policy_guids IS NOT NULL) AND (l_policy_guids.COUNT > 0))
  THEN
    FOR ctr IN l_policy_guids.FIRST..l_policy_guids.LAST
    LOOP
      l_policy_guid := l_policy_guids(ctr);    
      --disable the trigger on MGMT_VIOLATION for delete
      EM_SEVERITY.g_from_purge_api := TRUE ; 
  
      -- Remove from all tables that have policy guid
      -- MGMT_VIOLATION_*, MGMT_POLICY_EVAL_* tables are included here
      FOR crec IN ( SELECT o.name table_name, tgc.name target_column_name,
                           mgc.name policy_column_name
                      FROM sys.obj$ o, sys.tab$ t, sys.col$ tgc, sys.col$ mgc
                     WHERE o.owner# = userenv('SCHEMAID')
                       AND tgc.name like ('%TARGET_GUID%')
                       AND tgc.obj# = o.obj#
                       AND mgc.name like ('%POLICY_GUID%') 
                       AND mgc.obj# = o.obj#
                       AND o.name like ('MGMT_%')
                       AND o.obj# = t.obj#
                       AND bitand(t.property, 1) = 0
                       AND bitand(tgc.property,32) = 0
                       AND bitand(tgc.property,512) = 0
                       AND bitand(mgc.property,32) = 0
                       AND bitand(mgc.property,512) = 0
                     ORDER BY o.name )
      LOOP
          --delete for targets evaluated
          l_delete_stmt := 'DELETE FROM ' || EM_CHECK.simple_sql_name(crec.table_name) ||
                         ' WHERE ' || EM_CHECK.simple_sql_name(crec.policy_column_name) || ' = HEXTORAW(:policy_guid) '||
                         '   AND ((:is_super_user = :USER_HAS_PRIV) OR '|| 
                         '     (MGMT_USER.has_priv(:current_em_user,:OPERATOR_TARGET,'||
                          EM_CHECK.simple_sql_name(crec.target_column_name) ||') = :USER_HAS_PRIV))'||
                          ' AND ROWNUM <= :MAX_COMMIT_SIZE' ;
                         
                            
        
        LOOP

          IF EMDW_LOG.P_IS_DEBUG_SET THEN
            EMDW_LOG.DEBUG(' Executing SQL ' || l_delete_stmt, G_MODULE_NAME);
          END IF;
          
          EXECUTE IMMEDIATE l_delete_stmt USING l_policy_guid , is_super_user, 
            MGMT_USER.USER_HAS_PRIV, MGMT_USER.GET_CURRENT_EM_USER, MGMT_USER.OPERATOR_TARGET, 
            MGMT_USER.USER_HAS_PRIV, mgmt_global.MAX_COMMIT;
            
          l_counter := SQL%ROWCOUNT;
  
          -- Delete from other table through trigger has been disabled
          -- for MGMT_VIOLATIONS using EM_SEVERITY.G_FROM_PURGE_API flag
          -- hence deleting it using procedure.
          IF crec.table_name = 'MGMT_VIOLATIONS' AND
            EM_SEVERITY.p_violation_guids IS NOT NULL AND
            EM_SEVERITY.p_violation_guids.count > 0 THEN
            
            --CHECK: Is this call required?  
            EM_SEVERITY.purge_for_violation_guids
            (
              p_violation_guids    => EM_SEVERITY.p_violation_guids,
              p_annot_purged       => l_annot_purged,
              p_notify_log_purged  => l_notify_log_purged,
              p_notify_rq          => l_notify_rq
            );
  
            EM_SEVERITY.p_violation_guids.delete;
            EM_SEVERITY.p_violation_guids := MGMT_TARGET_GUID_ARRAY();             
          END IF;
          
          COMMIT;
          
          IF l_counter < mgmt_global.MAX_COMMIT THEN
            EXIT;
          END IF;  
        END LOOP;                       --END OF COMMIT CHECK LOOP
        
      END LOOP;                         -- END OF FOR crec IN LOOP
      --enable the trigger on MGMT_VIOLATION for delete
      EM_SEVERITY.g_from_purge_api := FALSE ; 
           
   END LOOP;                            -- END OF policy guids LOOP
   
   --Delete corresponding rule eval results records from mgmt_cs_eval_summ_rule table
    l_delete_stmt := 'DELETE FROM mgmt_cs_eval_summ_rule '||
                   'WHERE '||
                      'rqs_guid = HEXTORAW(:config_std_guid) AND '||
                      '((:is_super_user = :USER_HAS_PRIV) OR '|| 
                      '(MGMT_USER.has_priv(:current_em_user,:OPERATOR_TARGET,'||
                          'target_guid) = :USER_HAS_PRIV)) '||
                    ' AND ROWNUM <= :MAX_COMMIT_SIZE' ;
                       
    
    --Executes the delete statement and commits the txn appropriately
    em_config_std.commit_delete_cs_txn(p_execute_stmt => l_delete_stmt, 
      p_config_std_guid => p_config_std_guid, 
      p_is_super_user => is_super_user);   
   
  END IF;
END delete_eval_results_data;

--
-- PURPOSE
--
-- Executes the given delete cs statement and commits along the way based on the MAX_COMMIT value.
-- 
--
-- Parameters
-- p_execute_stmt : Statement to be executed
-- p_config_std_guid : Configuration Standard Guid
-- p_is_super_user : Whether the current em user is super user or not.
--
/*
PROCEDURE commit_delete_cs_txn(p_execute_stmt VARCHAR2 , p_config_std_guid RAW, 
  p_is_super_user INTEGER )
IS
  l_counter       INTEGER :=0;
    
BEGIN
  LOOP        
    IF EMDW_LOG.P_IS_DEBUG_SET THEN
        EMDW_LOG.DEBUG(' Executing SQL ' || p_execute_stmt, G_MODULE_NAME);
    END IF;
    EXECUTE IMMEDIATE EM_CHECK.NOOP(p_execute_stmt) USING p_config_std_guid, p_is_super_user,
    MGMT_USER.USER_HAS_PRIV, MGMT_USER.GET_CURRENT_EM_USER, MGMT_USER.OPERATOR_TARGET, 
            MGMT_USER.USER_HAS_PRIV, mgmt_global.MAX_COMMIT;
    
    l_counter := SQL%ROWCOUNT;          
    
    COMMIT;    
    IF l_counter < mgmt_global.MAX_COMMIT THEN
      EXIT;
    END IF;  
  END LOOP;
END commit_delete_cs_txn;
*/
--
-- PURPOSE
--
-- Submits the delete eval results job which takes care of deleting the evaluation
-- results from rule folder summary and policy evaluation results tables.
-- 
--
-- Parameters
-- p_config_std_guid : Configuration Standard GUID
--
PROCEDURE submit_delete_eval_results_job(
          p_config_std_guid RAW)
          
                     
IS
  l_job_params MGMT_JOB_PARAM_LIST;
  l_job_targets MGMT_JOB_TARGET_LIST;
  l_schedule MGMT_JOB_SCHEDULE_RECORD;
  l_job_name VARCHAR2(64);
  l_job_id RAW(16);
  l_execution_id RAW(16);  
  
BEGIN    
    -- This job has no targets.
    l_job_targets := MGMT_JOB_TARGET_LIST();

    l_job_params := MGMT_JOB_PARAM_LIST();        
    l_job_params.extend(1);               
    l_job_params(1) := MGMT_JOB_PARAM_RECORD('configStdGuid', 1,
                                             p_config_std_guid, null);    
                                             
    -- Its schedule is immediate.
    l_schedule := MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE,
                                           SYSDATE, null, 0, 0, 0, null, null,
                                           MGMT_JOBS.TIMEZONE_REPOSITORY, 
                                           0, 0, null);
  
    l_job_name := 'DEL_EVALRES_JOB_' || SYS_GUID();
    
    BEGIN  
      MGMT_JOBS.submit_job(p_job_name => l_job_name,
                       p_description => 'This is a delete compliance evaluation results job',
                       p_job_type => MGMT_CONFIG_STD.G_DELETE_EVAL_RESULTS,
                       p_job_targets => l_job_targets,
                       p_job_params => l_job_params,
                       p_schedule => l_schedule,
                       p_job_id_out => l_job_id,
                       p_execution_id_out => l_execution_id,
                       p_owner => MGMT_USER.get_current_em_user(),
                       p_system_job => MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY,
                       p_job_creds => null,
                       p_job_target_type => null,
                       p_job_notify_states => null);                                                               
  
  EXCEPTION
    WHEN OTHERS THEN    
      RAISE;
  END;           

END submit_delete_eval_results_job;

--Returns the repository target guid.
FUNCTION get_repos_target_guid RETURN RAW
IS
    l_repos_tgt_guid mgmt_targets.target_guid%TYPE;
    l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user();
    
BEGIN
  BEGIN  
  
    -- Enter super-user mode to get the repository guid since only users having
    -- access to repository can get the repository guid
    SETEMUSERCONTEXT(MGMT_USER.get_repository_owner,
                     MGMT_USER.OP_SET_IDENTIFIER); 
    
    --Assumption: Only one target for repository target type.
    --Get the repository target guid from mgmt_targets in super user mode.
    SELECT target_guid INTO l_repos_tgt_guid
    FROM mgmt_targets
    WHERE target_type = 'oracle_emrep';
    
    
    -- Revert back to being the same user as we entered
    SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);    
    EXCEPTION
      WHEN OTHERS THEN
        IF l_current_user IS NOT NULL THEN
          SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER);
        END IF;
        RAISE;
    END;
    
  RETURN l_repos_tgt_guid;  
END get_repos_target_guid;

END em_config_std;
/
show errors;