A Simple PL/SQL Function for Execution Steps and Field Population

In this example, you create a simple PL/SQL function to return a boolean value. Then you create PL/SQL function execution workflow steps to run the PL/SQL function and return its results as the results of the PL/SQL function execution workflow steps. You configure the workflow transitions and run the workflow to populate a request type field.

Follow the steps below:

  1. Create a simple function.

    CREATE OR REPLACE FUNCTION yes_or_no
      RETURN VARCHAR2
    IS
      tmpvar   VARCHAR2 (10);
    BEGIN
      tmpvar := 'YES';
      RETURN tmpvar;
    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         RAISE;
    END yes_or_no;

  2. Log on to PPM, and open PPM Workbench. On the shortcut bar, select Configuration > Validations, click New Validation and create a new validation.

    Make sure to select Enabled and Use in Workflow checkboxes.

    Save the validation.

  3. On the PPM Workbench shortcut bar, select Configuration > Workflows. Click List, locate a workflow of your choice and open it.

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

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

      Note: Other option for the Execution Type field: SQL Statement.

    3. Click Verify.

    4. Save the execution step.

  4. Go to Layout tab of the workflow, drag necessary workflow steps from the Workflow Step Sources window and drop to the Layout tab area, configure the transitions, and save your changes.

  5. Create a new Request Type with a new field.

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

    2. Click New Request Type. Provide values as necessary.

    3. Go to the Fields tab, click New to add a new field.

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

    5. Go to Rules tab, add a rule for the request type.

    6. Save the changes.

  6. Go back to the Workflow workbench. Create a workflow Execution step, which is later used to execute the command you just created in step d.

  7. Add a step to the workflow to populate the field using the command you just created.

    Note: Make sure to set Source Environment.

  8. In PPM Center, create a request using the Request Type you created in step 5 and submit it.

  9. Open the request you just created, click Execute Now to trigger the PL/SQL function.

  10. Click View Full Status Below > Graphical View to view the execution status.