Kintana Script : List Request Field Information

Purpose

You're interested in a KCRT_REQUEST_DETAILS parameter.
Now you need to identify the field stored in this parameter.

The time honoured way to locate the field information is to open the
request object at the Field tab, open fields, and pull open their
storage attribute tab, hoping that if they were generally implemented
in batch number and parameter order, you will eventually strike paydirt.

Or you could run this script.

Tested

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

Usage

Paste the script into SQL Runner

Change the 'and crt.request_type_name' request type name 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 Request field information
spool c:\request_field_info_yyyymmdd.txt
set linesize 1000

select crt.request_type_id, crt.request_type_name,
       ns.section_id, nsl.section_type_code, ns.section_name,
       npsf.prompt, npsf.parameter_token,
       npsf.batch_number, npsf.parameter_column_number
from KNTA_SECTION_LAYOUTS nsl, KCRT_REQUEST_TYPES crt,
     KNTA_SECTIONS ns, KNTA_PARAMETER_SET_FIELDS npsf
where crt.enabled_flag = 'Y'
  and nsl.entity_primary_key = crt.request_type_id
  and nsl.section_id = ns.section_id
  and nsl.section_id = npsf.section_id

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

  and crt.request_type_name = 'Inventory Restock Request'

-- to locate field by parameter and batch number

 order by npsf.batch_number, npsf.parameter_column_number

-- OR
-- to list fields in visual order of appearance on the request

 -- order by nsl.entity_primary_key, nsl.seq, npsf.row_number, npsf.column_number

spool off

REQUEST  REQUEST   SECTION  SECTION SECTION_NAME PROMPT         PARAMETER     BATCH_  PARAMETER
TYPE_ID  _TYPE     _ID      _TYPE                               TOKEN         NUMBER  _COLUMN
         _NAME              _CODE                                                     _NUMBER
-------  ---------- ------  ------ ------------ --------        -----------   ------  ---------
  43929  Inventory  20992  HEADER  Summary      Request No:     REQUEST_ID    [NULL]   1
  43929  Inventory  20992  HEADER  Summary      Request Status: STATUS_ID     [NULL]   2
  43929  Inventory  20992  HEADER  Summary      Created On:     CREATION_DATE [NULL]   3
...
  43929  Inventory  20992  DETAIL  Warehouse    Aisle:          WH_AISLE           1  22
  43929  Inventory  20992  DETAIL  Inspection   Spec Number     INS_SPEC_NO        1  23
  43929  Inventory  20992  DETAIL  Warehouse    Bin Number      WH_BIN_NO          4   3
  43929  Inventory  20992  DETAIL  Inspection   Inspected By:   INS_BY             4  14
...

REQUEST  REQUEST   SECTION SECTION SECTION_NAME PROMPT          PARAMETER     BATCH_  PARAMETER
TYPE_ID  _TYPE     _ID     _TYPE                                TOKEN         NUMBER  _COLUMN
         _NAME             _CODE                                                      _NUMBER
-------  ---------- ------ ------- ------------ --------        -----------   ------  ---------
  43929  Inventory  20992  HEADER  Summary      Request No:     REQUEST_ID    [NULL]   1
  43929  Inventory  20992  HEADER  Summary      Request Status: STATUS_ID     [NULL]   2
  43929  Inventory  20992  HEADER  Summary      Created On:     CREATION_DATE [NULL]   3
  43929  Inventory  20992  HEADER  Summary      Requestor Name: CONTACT_NAME  [NULL]   9
  43929  Inventory  20992  HEADER  Summary      Request Type:   REQUEST_TYPE  [NULL]   8
..

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 (64 Kb)