Kintana Script : List Notes for a Request Type

Purpose

Trying to locate that elusive note you remember seeing way back when? This script will find it for you.

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 Notes for a Request Type
spool c:\request_type_notes_yyyymmdd.txt
set linesize 1000

select crt.request_type_name,
       cr.request_id,
       nne.authored_date,
       nu.first_name,
       nu.last_name,
       nne.note_context_visible_value "Request Status Name"
       dbms_lob.substr(nne.note, 1800, 1) "Note 1800"
from KNTA_NOTE_ENTRIES nne,
     KNTA_USERS nu,
     KNTA_ENTITIES ne,
     KCRT_REQUESTS cr,
     KCRT_REQUEST_TYPES crt
 

where nne.author_id = nu.user_id
  and nne.parent_entity_id = ne.entity_id
  and ne.entity_name = 'Request Type'
  and nne.parent_entity_primary_key = cr.request_id
  and cr.request_type_id = crt.request_type_id

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

  -- and crt.request_type_name = 'My Request Type Name'

  -- limit by date range

  -- and nne.authored_date >= to_date('01-01-2011 01:00:00 AM', 'mm-dd-yyyy hh:mi:ss AM')
  -- and nne.authored_date <= to_date('12-31-2011 12:59:59 PM', 'mm-dd-yyyy hh:mi:ss AM')

  -- Locate notes containing a distinctive sub-string

  -- and dbms_lob.substr(nne.note, 1800, 1) like '%My String%'

order by crt.request_type_name, cr.request_id

spool off

REQUEST_TYPE_NAME REQUEST_ID AUTHORED_DATE            FIRST_NAME LAST_NAME Request Status Name  Note 1800
Inventory         301234     03-12-2011 11:12:17 AM   Dave       Goodall   Assign Bin Codes     Recoded as Bin 48 - foz
Inventory         302796     04-23-2011 8:15:45 PM    Dave       Goodall   DNS Inspection       Rejected per MIL-456-54

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

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

Notes

parameter 2 of dbms_lob.substr() specifies the substring length.

parameter 3 specifies the offset of the start of the substring.

For more information on decoding CLOB's see Natalka Roshak's article 'LEARNING TO LOVE LOBS'

Download

KintanaScripts.zip (79 Kb)