Kintana Script : List Status Dependencies for a Specified Request Type Field

Purpose

This query lets you focus in on a specific field or fields on a request type, and see the constraints (Visible, Editable, Reconfirm, Clear) that you have set at each approval (decision) step.

The chosen sort order lists the results in step sequence order within the request type. If you have adopted the best practice procedure of numbering your workflow steps in increasing temporal order, then you will typically see that a field is editable in the early stages and it set non-editable at some later point.

Tested

With HP PPM (Project and Portfolio Management) Kintana v7.5.

Usage

Paste the script into SQL Runner

Change 'My Request Type Name' to your request type name.

Change 'My Workflow Name' to the Workflow the request type runs on or comment it out.

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 Status Dependency exceptions for a Request Type
spool c:\request_field_dependency_exceptions_yyyymmdd.txt
set linesize 1000

select crt.request_type_name "Request Type",
       ww.workflow_name,
       wws.sort_order "Step#",
       wws.step_name,
       ns.section_name,
       npsf.prompt,
       cs.status_name,
       cfd.visible_field_flag "Visible",
       cfd.disabled_field_flag "Editable",
       cfd.required_field_flag "Required",
       cfd.reconfirm_field_flag "Reconfirm",
       cfd.clear_field_flag "Clear" --,
       --npsf.parameter_token
from  KCRT_REQUEST_TYPES crt,
      KCRT_REQUEST_TYPE_STATUSES crts,
      KCRT_STATUSES cs,
      KCRT_FIELD_DEPENDENCIES cfd,
      KNTA_PARAMETER_SET_FIELDS npsf,
      KNTA_SECTION_LAYOUTS nsl,
      KNTA_SECTIONS ns,
      KWFL_WORKFLOWS ww,
      KWFL_WORKFLOW_STEPS wws
where

      -- limit to a single request type. Comment out to list all request types

      crt.request_type_name = 'My Request Type Name'

      -- limit to a single workflow. Comment out to list all workflows

      and ww.workflow_name = 'My Workflow'
      and ww.workflow_id = wws.workflow_id
      and crts.status_id = wws.parent_status
      and wws.step_type_code = 'APPROVAL'
      and wws.product_scope_code = 'RI'
      and crt.request_type_id = crts.request_type_id
      and crts.status_id = cs.status_id
      and crts.request_type_status_id = cfd.request_type_status_id
      and cfd.parameter_set_field_id = npsf.parameter_set_field_id
      and npsf.section_id = nsl.section_id
      and nsl.entity_primary_key = crt.request_type_id
      and nsl.section_id = ns.section_id
      and cfd.visible_field_flag = 'Y'

      -- Limit to one or more fields by supplying the field token(s)

      and (    npsf.parameter_token = 'MY_SPECIFIC_TOKEN'
            or npsf.parameter_token = 'MY_OTHER_TOKEN'
          )

order by ww.workflow_name, wws.sort_order, crt.request_type_name,
         cs.status_name, nsl.seq, ns.section_name, npsf.prompt

Request Type  WORKFLOW_NAME  STEP# STEP_NAME  SECTION_NAME  PROMPT           STATUS_NAME  Visible Editable Required Reconfirm Clear  PARAMETER_TOKEN

Inventory     Goods Inwards    24  Cage In    Bin Info      Bay-Rack-Shelf:  Restock      Y       Y        Y        N         N      BI_BAT_RACK_SHELF
Inventory     Goods Inwards    28  Stock Take Bin Info      Bay-Rack-Shelf:  Stock Take   Y       N        N        N         N      NI_BAT_RACK_SHELF
...

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)