Kintana Script : Locate packages and lines by user, file, and object.

Purpose

This swiss army knife type query can help you identify:

users who have created package lines

packages and lines created

to migrate a specified objects or file between specified dates.

Tested

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

Usage

Paste the script into Toad or SQL Runner

You'll need to run this query once to determine the field numbers for the file name, target environment, and to add any other favorite fields.

select * from KDLV_PACKAGE_LINES dpl

select nu.first_name || ' ' || nu.last_name "User",
       dpl.object_name,
       dpl.app_code,
       dpl.creation_date,
       dpl.package_id,
       dpl.seq "Line",
       dpl.parameterF  "File",       -- Modify 'F' to the number used for the file name field
       dpl.parameterT  "Target"      -- Modify 'T' to the number used for the target environment
from KDLV_PACKAGE_LINES dpl,
     KNTA_USERS nu
where nu.user_id = dpl.created_by and nu.end_date is NULL        -- Limit to active users
--
-- Limit to a specified date range
and dpl.creation_date >= to_date('01-01-2008 01:00:00 AM', 'mm-dd-yyyy hh:mi:ss AM')
and dpl.creation_date <= to_date('12-31-2008 12:59:00 PM', 'mm-dd-yyyy hh:mi:ss PM')
--Modify 'F' to the number used for the file name field
and dpl.parameterF  = 'MyFile.sql'                         -- Limit by file name
and dpl.object_name = 'My Object'                          -- Limit by object type
--and dpl.package_id  = '99999'                              -- Limit by package number
--and (nu.first_name  = 'Fred' and nu.last_name = 'Grandy')  -- Limit by user
order by 1 asc

Download

KintanaScripts.zip (79 Kb)