Execute PL/SQL functions With A Parameter and Creating Transitions Based on the Results

In this example, you create a PL/SQL function with a parameter that can be passed. Then you create PL/SQL function execution workflow steps to run the function in order to route a PPM workflow based on the results the function returns. You configure the workflow transitions and execute the PL/SQL function execution workflow steps to pass the parameter in order to populate request type fields.

Perform the following steps.

1. Create a function with a parameter that can be passed.

   CREATE OR REPLACE FUNCTION sample_param (p_num IN NUMBER)
   RETURN NUMBER IS
   tmpVar NUMBER;
   BEGIN
      tmpVar := p_num;
      RETURN tmpVar;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          NULL;
        WHEN OTHERS THEN
          -- Consider logging the error and then re-raise
          RAISE;
   END sample_param;
   /

2. Create a validation to match the values to be returned by the function.

   Make sure to select Enabled and Use in Workflow checkboxes.

   Save the validation.

3. Create a new workflow and a workflow Execution step.

   1. On the PPM Workbench shortcut bar, select Configuration > Workflows. Click New Workflow. In the workflow window that opens, provide necessary values.

   2. From the Workflow Step Sources window, click New. The Execution window opens.

   3. For the Execution Type field, select PL/SQL Function from the drop-down list; For the Validation field, select the validation you just created in step 2; Type the function you created in step 1 in the Execution text area.

   4. Click Verify.

   5. Save the execution step.

4. Create a new Request Type with two new fields.

   1. On the PPM Workbench shortcut bar, select Demand Mgmt > Request Types. The Request Type Workbench opens.

   2. Click New Request Type. Provide values as necessary for the new request type.

   3. On the Fields tab, add two new fields using the validation you created in step 2.

   4. Go to Commands tab, create a command to populate the new fields.

   5. Go to Rules tab, add a rule for the request type using the workflow you created earlier.

   6. Save the changes.

5. Go back to the Workflow workbench. Create a workflow Execution step to execute the command you just created in step d.

6. Go to Layout tab of the workflow, add the execution step to the workflow to populate the new fields using the command you just created.

   • From the Workflow Step Sources window, drag the workflow step of your choice and drop it to the Layout tab area.

   • In the Workflow Step window that opens, set Source Environment.

   • Go to Security tab,click New. In the Workflow Step Security dialog box, add yourself to make sure you have the step execution security.

   • Configure the transitions, and save your changes.

     Note: Other Results are based on the validation.

7. In PPM, create a request using the Request Type you created earlier and submit it.

8. Open the request you just created, set a value for the first field, which is used in PL/SQL Function.

9. The toolbar displays available actions you can take for the first execution step you configured earlier. Click Execute Now.

   The execution step with PL/SQL function determines which transition to take next based on the value you set for the first field. In this example, it is Step 4 (as shown below).

   From the toolbar, click Execute Now.

10. The second request type field is successfully populated by the execution steps.

    Click Graphical View to view the execution status.