Kintana Script : Re-assign Requests when a user leaves

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,
       nu.first_name || ' ' || nu.last_name "User",
       cr.request_number,
       crt.request_type_name,
       cr.creation_date,
       cs.status_name,
       cr.description
 from KCRT_REQUESTS cr,
      KNTA_USERS nu,
      KCRT_REQUEST_TYPES crt,
      KCRT_STATUSES cs
 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.last_name = 'Baggins'
   and nu.end_date is not NULL
   --and nu.end_date < '14-NOV-2008'
   and cr.status_id not in (4,6)   --4=Cancelled 6=Closed
   and cs.status_name not like 'Closed%'
   and cr.status_id = cs.status_id

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