Copy Rules Capability

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

Top Replies

  • 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.

  • 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;
  • 1 for this request... would love to be able to copy rules... this is a much needed feature.