Absent Member.. kruegke Absent Member..
Absent Member..
317 views

Pre-Populate Table Component Values in Request Type

Jump to solution

I have a Request Type defined with a Table Component field, comprised of 7 columns.  I would like to pre-populate this table with several rows of "standard" table values to be present every time the Request Type is created.

Can anyone advise how this can be accomplished?

Thank you.

0 Likes
1 Solution

Accepted Solutions
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Pre-Populate Table Component Values in Request Type

Jump to solution

We prepopulate some of our tables when the request is created. You will need to create a database function and a workflow execution step to call this function. The code to do this will vary depending on your requirements, below is some code to get you started.

The workflow execution step will be a PL/SQL Function; Workflow Scope: Requests; validation WF - Standard Execution Results w.Reset, Abort. Processing Type: Immediate; Processing Type: Immediate; Exection: insert_tc_docs('[REQ.REQUEST_ID]'). Make this one of the first steps in the workflow.

CREATE OR REPLACE FUNCTION insert_tc_value (p_request_id IN NUMBER)
  RETURN VARCHAR2 IS
  v_col_val_name   VARCHAR2 (200) := '<validation name>'; -- Name of validatiofor TC column to populate
  tabseq           NUMBER; -- Sequence # for the table entries
  nentry           VARCHAR2 (20); -- Text value for tabseq
  v_val_name       VARCHAR2 (200) := '<validation name>'; -- Name of validation used for TC field
  v_field_id       NUMBER;
  v_prompt         VARCHAR2 (200) := '<field prompt>'; -- Request Type Field Name
  v_req_type       VARCHAR2 (200) := '<request type name>';
  v_context_id     NUMBER;

  -- Get the list of validation entries to be added

  CURSOR get_validation_values IS
      SELECT lookup_code, meaning
        FROM knta_lookups
       WHERE lookup_type = v_col_val_name
         AND enabled_flag = 'Y'
    ORDER BY seq;
BEGIN
  -- Get the ID for the field
  SELECT s.parameter_set_field_id field_id
    INTO v_field_id
    FROM knta_parameter_set_fields s, knta_parameter_set_contexts c,
         kcrt_request_types t
   WHERE s.parameter_set_context_id = c.parameter_set_context_id
     AND c.context_value = TO_CHAR (t.request_type_id)
     AND t.request_type_name = v_req_type
     AND s.enabled_flag = 'Y'
     AND s.prompt = v_prompt;

  -- Get the context ID for the field

  SELECT c.parameter_set_context_id
    INTO v_context_id
    FROM knta_validations v, knta_parameter_set_contexts c
   WHERE TO_CHAR (v.validation_id) = c.context_value
     AND v.validation_name = v_val_name;

  tabseq := 0; -- Assumes the table is empty before adding any rows

  -- loop through all values to add
  FOR d IN get_validation_values LOOP
    tabseq := tabseq + 1;

    INSERT INTO kcrt_table_entries (table_entry_id, created_by, creation_date,
                  last_updated_by, last_update_date, request_id,
                  parameter_set_field_id, seq, parameter_set_context_id,
                  parameter1, visible_parameter1)
         VALUES (kcrt_table_entries_s.NEXTVAL, 1, SYSDATE, 1, SYSDATE,
                 p_request_id, flds.field_id, tabseq, v_context_id, d.lookup_code,
                 d.meaning);
  END LOOP;

  -- add the # of entries to the request field
  IF tabseq = 1 THEN
    nentry := tabseq || ' Entry';
  ELSE
    nentry := tabseq || ' Entries';
  END IF;

  -- Update the parameter # and batch_number below to the storage location for the field
  UPDATE kcrt_request_details
     SET parameter7 = tabseq, visible_parameter7 = nentry,
         last_update_date = CURRENT_DATE
   WHERE request_id = p_request_id
     AND batch_number = 1;

  RETURN 'SUCCESS';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'FAILURE';
END insert_tc_value;
-- Remember to give Kudos to answers! (click the KUDOS star)
7 Replies
Absent Member.. DomPPM Absent Member..
Absent Member..

Re: Pre-Populate Table Component Values in Request Type

Jump to solution

Hello 

0 Likes
Absent Member.. kruegke Absent Member..
Absent Member..

Re: Pre-Populate Table Component Values in Request Type

Jump to solution

Thanks.  However I am looking to pre-populate several rows on the table, not just set default values.  What Users are expecting is the table, prepopulated with standard data rows / content, allowing them to add / edit / delete as appropriate.

0 Likes
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Pre-Populate Table Component Values in Request Type

Jump to solution

We prepopulate some of our tables when the request is created. You will need to create a database function and a workflow execution step to call this function. The code to do this will vary depending on your requirements, below is some code to get you started.

The workflow execution step will be a PL/SQL Function; Workflow Scope: Requests; validation WF - Standard Execution Results w.Reset, Abort. Processing Type: Immediate; Processing Type: Immediate; Exection: insert_tc_docs('[REQ.REQUEST_ID]'). Make this one of the first steps in the workflow.

CREATE OR REPLACE FUNCTION insert_tc_value (p_request_id IN NUMBER)
  RETURN VARCHAR2 IS
  v_col_val_name   VARCHAR2 (200) := '<validation name>'; -- Name of validatiofor TC column to populate
  tabseq           NUMBER; -- Sequence # for the table entries
  nentry           VARCHAR2 (20); -- Text value for tabseq
  v_val_name       VARCHAR2 (200) := '<validation name>'; -- Name of validation used for TC field
  v_field_id       NUMBER;
  v_prompt         VARCHAR2 (200) := '<field prompt>'; -- Request Type Field Name
  v_req_type       VARCHAR2 (200) := '<request type name>';
  v_context_id     NUMBER;

  -- Get the list of validation entries to be added

  CURSOR get_validation_values IS
      SELECT lookup_code, meaning
        FROM knta_lookups
       WHERE lookup_type = v_col_val_name
         AND enabled_flag = 'Y'
    ORDER BY seq;
BEGIN
  -- Get the ID for the field
  SELECT s.parameter_set_field_id field_id
    INTO v_field_id
    FROM knta_parameter_set_fields s, knta_parameter_set_contexts c,
         kcrt_request_types t
   WHERE s.parameter_set_context_id = c.parameter_set_context_id
     AND c.context_value = TO_CHAR (t.request_type_id)
     AND t.request_type_name = v_req_type
     AND s.enabled_flag = 'Y'
     AND s.prompt = v_prompt;

  -- Get the context ID for the field

  SELECT c.parameter_set_context_id
    INTO v_context_id
    FROM knta_validations v, knta_parameter_set_contexts c
   WHERE TO_CHAR (v.validation_id) = c.context_value
     AND v.validation_name = v_val_name;

  tabseq := 0; -- Assumes the table is empty before adding any rows

  -- loop through all values to add
  FOR d IN get_validation_values LOOP
    tabseq := tabseq + 1;

    INSERT INTO kcrt_table_entries (table_entry_id, created_by, creation_date,
                  last_updated_by, last_update_date, request_id,
                  parameter_set_field_id, seq, parameter_set_context_id,
                  parameter1, visible_parameter1)
         VALUES (kcrt_table_entries_s.NEXTVAL, 1, SYSDATE, 1, SYSDATE,
                 p_request_id, flds.field_id, tabseq, v_context_id, d.lookup_code,
                 d.meaning);
  END LOOP;

  -- add the # of entries to the request field
  IF tabseq = 1 THEN
    nentry := tabseq || ' Entry';
  ELSE
    nentry := tabseq || ' Entries';
  END IF;

  -- Update the parameter # and batch_number below to the storage location for the field
  UPDATE kcrt_request_details
     SET parameter7 = tabseq, visible_parameter7 = nentry,
         last_update_date = CURRENT_DATE
   WHERE request_id = p_request_id
     AND batch_number = 1;

  RETURN 'SUCCESS';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'FAILURE';
END insert_tc_value;
-- Remember to give Kudos to answers! (click the KUDOS star)
Absent Member.. kruegke Absent Member..
Absent Member..

Re: Pre-Populate Table Component Values in Request Type

Jump to solution

Thanks.  That is what I was expecting to be the solution but needed to be certain (as I have never created a DB function before).

0 Likes
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Pre-Populate Table Component Values in Request Type

Jump to solution

I forgot to mention that if you only want to populate part of the drop down list in the table, you can create a user data field for the validation and set it to a Yes/No value to indicated if it is to be pre-populated.  Then in the code, modify the cursor that gets the list of values so it only includes those with the user data set to include.  That way you can populate some items and let the user choose others to add manually.

-- Remember to give Kudos to answers! (click the KUDOS star)
0 Likes
Absent Member.. kruegke Absent Member..
Absent Member..

Re: Pre-Populate Table Component Values in Request Type

Jump to solution

Thanks so much for this suggested solution . . .  I referred this change to our PPM Technical Support Team and they do not want to make it without HP confirming it to be "acceptable". They are saying this is PPM "customization" that would not be supported by HP.  Did you ever seek HP approval for this change when you made it? 

0 Likes
Derek Giedd Honored Contributor.
Honored Contributor.

Re: Pre-Populate Table Component Values in Request Type

Jump to solution

I have seen this type of response from HPE on several occasions. I am not sure why they allow customizations if they are not going to allow them. If you create a new workflow step, you have the option of selecting PL/SQL Function for the Execution Type. 

No, we did not seek HPE's approval, but have never felt the need to seek it either.  This solution has worked for us since July 2013 without causing any problems.

 

-- Remember to give Kudos to answers! (click the KUDOS star)
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.