Super Contributor.. JodieOndrejka Super Contributor..
Super Contributor..
216 views

Copy Rules Capability

Are there any plans in the near future (or at all) to provide the capability to copy rules?

0 Likes
3 Replies
bl1 Frequent Contributor.
Frequent Contributor.

Re: Copy Rules Capability

I don't know of any plans to provide a "copy rule" button, but you can use the CTL-C / CTL-V functionality to copy the code portion of a rule between different rules/request types.

0 Likes
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Copy Rules Capability

There is already a ER for this. See QCCR1L54946. I do have a way to copy a rule using a database procedure. Pass this the request type name, rule ID #, your PPM username, and the new Rule name). Run this in an anonymous block. Turn on server output to see any error messages. After running this, you may need to force it to reload by saving the request type, closing it, then re-opening it. You should see the new rule after this.

  PROCEDURE copy_rule(p_request_type IN VARCHAR2, p_rule_seq IN NUMBER,
    p_username IN VARCHAR2, p_new_name IN VARCHAR2) AS
    /******************************************************************
     Developer : Derek Giedd
     Purpose   : This procedure will copy a rule and is only
               : used by developers.
     Inputs    : This function accepts 4 parameters:
               : Parameter 1: Request Type
               : Parameter 2: Rule ID
               : Parameter 3: Username
               : Parameter 4: New Rule Name
     Outputs:  : The results are output to DBMS_OUTPUT.
               : Set Serveroutput On should be run before running this.
    ******************************************************************/

    CURSOR get_results(c_rule_id NUMBER) IS
      SELECT param_rule_result_id, creation_date, created_by,
             last_update_date, last_updated_by, entity_last_update_date,
             param_rule_id, parameter_set_field_id, seq_number,
             source_type_code, source
        FROM knta_param_rule_results
       WHERE param_rule_id = c_rule_id;

    CURSOR get_dependencies(c_rule_id NUMBER) IS
      SELECT param_rule_dependency_id, creation_date, created_by,
             last_update_date, last_updated_by, entity_last_update_date,
             param_rule_id, parameter_set_field_id, visible_parameter,
             parameter, all_values_flag, source_type_code, source,
             condition_code, compare_code, condition_meaning,
             compared_param_set_field_id
        FROM knta_param_rule_dependencies
       WHERE param_rule_id = c_rule_id;

    v_new_rule_id    NUMBER;
    v_new_ruled_id   NUMBER;
    v_new_ruler_id   NUMBER;
    v_userid         NUMBER;
    v_descr          VARCHAR2(300);
    v_seq            NUMBER;
    v_psc_id         NUMBER;
    v_event_code     VARCHAR2(30);
    v_sql            VARCHAR2(4000);
    v_ena_flag       VARCHAR2(1);
    v_source_type    VARCHAR2(30);
    v_source         VARCHAR2(100);
    v_rule_type      VARCHAR2(30);
    v_wf_id          NUMBER;
    v_dept           VARCHAR2(30);
    v_app            VARCHAR2(30);
    v_atu_id         NUMBER;
    v_atg_id         NUMBER;
    v_cascade        VARCHAR2(1);
    v_logic_code     VARCHAR2(20);
    v_next_seq       NUMBER;
    v_rule_id        NUMBER;
    v_cnt            NUMBER := 0;
  BEGIN
    -- Validate parameters
    -- Check for request type
    SELECT COUNT(*)
      INTO v_cnt
      FROM kcrt_request_types
     WHERE request_type_name = p_request_type;

    IF v_cnt > 0 THEN
      DBMS_OUTPUT.put_line('Request Type ' || p_request_type || ' does not exists. Copy aborted.');
      RETURN;
    END IF;

    -- check for duplicate rule name
    SELECT COUNT(*)
      INTO v_cnt
      FROM knta_param_rules pr, kcrt_request_types rt,
           knta_parameter_set_contexts psc
     WHERE rt.request_type_name = p_request_type
       AND pr.param_rule_name = p_new_name
       AND TO_CHAR(rt.request_type_id) = psc.context_value
       AND psc.parameter_set_context_id = pr.parameter_set_context_id;

    IF v_cnt > 0 THEN
      DBMS_OUTPUT.put_line(
        'Rule ' || p_new_name || ' already exists. Copy aborted.');
      RETURN;
    END IF;

    -- check for rule sequence
    SELECT COUNT(*)
      INTO v_cnt
      FROM knta_param_rules pr, kcrt_request_types rt,
           knta_parameter_set_contexts psc
     WHERE rt.request_type_name = p_request_type
       AND pr.seq_number = p_rule_seq
       AND TO_CHAR(rt.request_type_id) = psc.context_value
       AND psc.parameter_set_context_id = pr.parameter_set_context_id;

    IF v_cnt > 0 THEN
      DBMS_OUTPUT.put_line(
        'Rule Seq ' || p_rule_seq || ' does not exists. Copy aborted.');
      RETURN;
    END IF;

    -- check username
    SELECT COUNT(*)
      INTO v_cnt
      FROM knta_users
     WHERE UPPER(username) = UPPER(p_username);

    IF v_cnt > 0 THEN
      DBMS_OUTPUT.put_line(
        'Username ' || p_username || ' was not found. Copy aborted.');
      RETURN;
    END IF;

    -- get current rule info

    SELECT pr.param_rule_id, pr.description, pr.seq_number,
           pr.parameter_set_context_id, pr.rule_event_code, pr.rule_sql,
           pr.enabled_flag, pr.source_type_code, pr.source, pr.rule_type_code,
           pr.workflow_id, pr.department_code, pr.application,
           pr.assigned_to_user_id, pr.assigned_to_group_id, pr.cascade_rules,
           pr.logic_code
      INTO v_rule_id, v_descr, v_seq, v_psc_id, v_event_code, v_sql,
           v_ena_flag, v_source_type, v_source, v_rule_type, v_wf_id, v_dept,
           v_app, v_atu_id, v_atg_id, v_cascade, v_logic_code
      FROM knta_param_rules pr, kcrt_request_types rt,
           knta_parameter_set_contexts psc
     WHERE rt.request_type_name = p_request_type
       AND pr.seq_number = p_rule_seq
       AND TO_CHAR(rt.request_type_id) = psc.context_value
       AND psc.parameter_set_context_id = pr.parameter_set_context_id;

    -- Get next rule sequence #
    SELECT MAX(pr.seq_number) + 1
      INTO v_next_seq
      FROM knta_param_rules pr, kcrt_request_types rt,
           knta_parameter_set_contexts psc
     WHERE rt.request_type_name = p_request_type
       AND TO_CHAR(rt.request_type_id) = psc.context_value
       AND psc.parameter_set_context_id = pr.parameter_set_context_id;

    -- get the userid

    SELECT user_id
      INTO v_userid
      FROM knta_users
     WHERE UPPER(username) = UPPER(p_username);

    -- get the next sequence for the insert
    SELECT knta_param_rules_s.NEXTVAL INTO v_new_rule_id FROM DUAL;

    -- add the new rule
    INSERT INTO knta_param_rules(param_rule_id, creation_date, created_by,
                  last_update_date, last_updated_by, entity_last_update_date,
                  param_rule_name, description, seq_number,
                  parameter_set_context_id, rule_event_code, rule_sql,
                  enabled_flag, source_type_code, source, rule_type_code,
                  workflow_id, department_code, application,
                  assigned_to_user_id, assigned_to_group_id, cascade_rules,
                  logic_code)
    VALUES (v_new_rule_id, SYSDATE, v_userid, SYSDATE, v_userid, SYSDATE,
            p_new_name, v_descr, v_next_seq, v_psc_id, v_event_code, v_sql,
            v_ena_flag, v_source_type, v_source, v_rule_type, v_wf_id, v_dept,
            v_app, v_atu_id, v_atg_id, v_cascade, v_logic_code);

    -- get the next sequence for the insert
    FOR i IN get_dependencies(v_rule_id) LOOP
      SELECT knta_param_rule_dependencies_s.NEXTVAL
        INTO v_new_ruled_id
        FROM DUAL;

      INSERT INTO knta_param_rule_dependencies(param_rule_dependency_id,
                    creation_date, created_by, last_update_date,
                    last_updated_by, entity_last_update_date, param_rule_id,
                    parameter_set_field_id, visible_parameter, parameter,
                    all_values_flag, source_type_code, source, condition_code,
                    compare_code, condition_meaning,
                    compared_param_set_field_id)
      VALUES (v_new_ruled_id, SYSDATE, v_userid, SYSDATE, v_userid, SYSDATE,
              v_new_rule_id, i.parameter_set_field_id, i.visible_parameter, i.parameter,
              i.all_values_flag, i.source_type_code, i.source, i.condition_code,
              i.compare_code, i.condition_meaning, i.compared_param_set_field_id);
    END LOOP;

    FOR i IN get_results(v_rule_id) LOOP
      SELECT knta_param_rule_results_s.NEXTVAL INTO v_new_ruler_id FROM DUAL;

      INSERT INTO knta_param_rule_results(param_rule_result_id, creation_date,
                    created_by, last_update_date, last_updated_by,
                    entity_last_update_date, param_rule_id,
                    parameter_set_field_id, seq_number, source_type_code,
                    source)
      VALUES (v_new_ruler_id, SYSDATE, v_userid, SYSDATE, v_userid, SYSDATE,
              v_new_rule_id, i.parameter_set_field_id, i.seq_number, i.source_type_code,
              i.source);
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.put_line('Rule copy complete.');
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.put_line('ERROR encountered. Copy aborted.');
  END copy_rule;
-- Remember to give Kudos to answers! (click the KUDOS star)
pepdwill Honored Contributor.
Honored Contributor.

Re: Copy Rules Capability

+1 for this request... would love to be able to copy rules... this is a much needed feature. 

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.