Kintana Script : Request Reassignments

Purpose

When someone leaves or is transferred, you'll need ensure that the requests they were working on are re-assigned.

Tested

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

Usage

Paste the following script into SQL Runner

Omit the comment and spool statements unless you're using a PL-SQL editor.
SQL Runner only supports select statements and you'll get a KNTA-10648 error.

-- List Request Reassignments
spool c:\requests_to_be_re_assigned_yyyymmdd.txt
set linesize 1000

select nu.username, cr.request_number, crt.request_type_name,
       cr.creation_date, cr.description
from KCRT_REQUESTS cr, KNTA_USERS nu, KCRT_REQUEST_TYPES crt
where cr.creation_date >= '1-JAN-2005'
and cr.request_type_id = crt.request_type_id
and cr.assigned_to_user_id = nu.user_id
and (nu.end_date is not null or nu.user_name = "bbaggins" )
and nu.end_date < '17-FEB-2005'
and cr.status_id not in (4,6)  /* 4=Cancelled 6=Closed */

spool off

USERNAME     REQUEST_NUMBER  REQUEST_TYPE_NAME    CREATION_DATE   DESCRIPTION
-----------  --------------  -------------------- -------------   --------------
fbaggins     304062          Ring Bearing         22-Jan-05       Transportation
mbrandybuck  201020          Pipeweed Procurement 14-Dec-04       Sourcing

Download

KintanaScripts.zip (58 Kb)