Kintana Script : Workflow Steps that call PL/SQL functions

Purpose

If you change the input parameters or return codes for a custom PL/SQL function then you'll need to locate and change the function call in executions of type 'PL/SQL Function' which invoke it, and the workflow steps based on that execution.

Tested

With HP PPM (Project and Portfolio Management) Kintana v8.0.

Usage

Paste the script into SQL Runner

Omit the comment, spool, and linesize statements unless you're using a PL-SQL editor. SQL Runner only supports select statements and you'll get a KNTA-10648 error. If using PL-SQL change 'yyyymmdd' in the output file name to today's date.

-- List workflow steps based on type 'PL/SQL Function' executions
-- that call a specific PL/SQL function.
spool c:\workflow_steps_calling_pl_sql_functions_yyyymmdd.txt
set linesize 1000

select ww.workflow_name,
       wws.sort_order "Step#",
       wws.step_name,
       we.execution_name,
       we.execution_type_code,
       we.execution
from KWFL_WORKFLOW_STEPS wws,
     KWFL_WORKFLOWS ww,
     KWFL_EXECUTIONS we
where wws.workflow_id = ww.workflow_id
  and wws.step_type_code = 'EXECUTION'
  and wws.step_source_id = we.execution_id
  and we.execution_type_code ='PLSQL'
  -- locate specific function call
  and we.execution like '%myFunctionName%'
order by ww.workflow_name

spool off

WORKFLOW_NAME,   Step#         STEP_NAME       EXECUTION_NAME   EXECUTION_TYPE_CODE   EXECUTION
Stock Control    14            Goods Inwards   stockPointCodes  PL/SQL                CF.stockPointCodes('[REQ.REQUEST_ID]', '[REQD.P.SPCODE]' )
Stock Control    45            Restocking      stockPointCodes  PL/SQL                CF.stockPointCodes('[REQ.REQUEST_ID]', '[REQD.P.SPCODE]' )

To extract the output, click on the [Open as Text] button.

This opens a text window from which you can copy and paste.

Download

KintanaScripts.zip (79 Kb)