Kintana Script : List Field Status Dependency Exceptions for a Request Type

Purpose

If your request type contains a large number of fields and runs on a workflow with many steps then the number of dependencies ( fields x step statuses) can easily get into the tens of thousands, much too large a number to review easily through the gui.

This query lets you focus on fields that you have constrained in some way. The output is limited to fields at approval (decision) step statuses where the field is either not editable or has one or more of the Required, Reconfirm or Clear flags set

In other words just the exceptions to vanilla editable fields with no special constraints

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'

       -- list exceptions to the norm  (field is editable with no special constraints).

       and ( cfd.disabled_field_flag  = 'N'   -- Y=IsEditable N= NotEditable
          or cfd.required_field_flag = 'Y'
          or cfd.reconfirm_field_flag = 'Y'
          or cfd.clear_field_flag = 'Y'
       )

order by cfd.disabled_field_flag desc, cfd.required_field_flag,
         cfd.reconfirm_field_flag, cfd.clear_field_flag,
         wws.sort_order, 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    Main Section  Customer No:     Restock      Y       N        N        N         N      MS_CUSTOMER_NO
Inventory     Goods Inwards    24  Cage In    Main Section  Date Received:   Restock      Y       Y        Y        N         N      MS_DATE_RECEIVED
Inventory     Goods Inwards    24  Cage In    Bin Info      SKU:             Restock      Y       Y        Y        N         Y      BI_SKU
Inventory     Goods Inwards    24  Cage In    Bin Info      Bay-Rack-Shelf:  Restock      Y       Y        Y        N         N      BI_BAT_RACK_SHELF
Inventory     Goods Inwards    24  Cage In    Inspection    Inspected By:    Restock      Y       Y        N        Y         N      IN_INSPECTED_BY
Inventory     Goods Inwards    24  Cage In    Inspection    Passed As:       Restock      Y       Y        N        Y         N      IN_PASSED_AS
...

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)