Kintana Script : List Requests Over 30 days old

Purpose

You need to keep on top of requests more than a month old that have not been closed or cancelled.

This little script will get you hardcopy you can send out for action.

Tested

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

Usage

Paste the script into SQL Runner

Change the html 'ampersand-lt-semicolon' to a single 'less than' character.

Change 'cr.creation_date 'DD-MMM-YYYY' to 1 month before today's date.

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 Create Requests Over 30 days old
spool c:\open_requests_over_30_days_old_at_yyyymmdd.txt
set linesize 1000

select cr.request_number, cr.creation_date, crt.request_type_name,
cs.status_name, nu.username, cr.description
from KCRT_REQUESTS cr, KCRT_STATUSES cs, KNTA_USERS nu, KCRT_REQUEST_TYPES crt

where cr.creation_date < 'DD-MMM-YYYY'

and cr.assigned_to_user_id = nu.user_id
and cr.request_type_id = crt.request_type_id
and cr.status_id = cs.status_id
and cr.status_id not in (4,6,13) /* 4=cancelled 6=closed 13=complete */
order by nu.username, cr.creation_date desc

spool off

REQUEST_NUMBER    CREATION_DATE  REQUEST_TYPE_NAME STATUS_NAME  USERNAME  DESCRIPTION
--------------    -------------  -----------------  ----------  --------  -----------
 30002            06 Feb 2005    Restock            In Progress John Doe  Raleigh 504
 etc etc

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