Kintana Script : Package Line Transaction History

Purpose

Lists the transactions you see when highlighting a package line and clicking on [View] 'Line Trans History' that are COMPLETE or IN PROGRESS.

Just because a step completed does not mean that it succceeded! The 'Result' field has to say 'Succeeded' for that to be the case.

Tested

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

Usage

Paste the script into Toad or SQL Runner

select wst.top_instance_source_set_id "Package#",
       dpl.seq "Line#",
       nu.first_name || ' ' || nu.last_name "User Name",
       wst.last_update_date "Date",
       nu.username "User",
       wws.sort_order "Step",
       wst.status "Status",
       wst.visible_user_status_value "Result",
       ww.workflow_name,
       wws.step_name,
       wst.instance_source_type_code
from   KDLV_PACKAGE_LINES dpl,
       KWFL_WORKFLOW_STEPS wws,
       KWFL_STEP_TRANSACTIONS wst,
       KNTA_USERS nu,
       KWFL_WORKFLOW_INSTANCES wwi,
       KWFL_WORKFLOWS ww
where  wst.instance_source_id = dpl.package_Line_id
   and wws.workflow_step_id = wst.workflow_step_id
   and wst.last_updated_by = nu.user_id
   and wst.workflow_instance_id = wwi.workflow_instance_id
   and wwi.workflow_id = ww.workflow_id
  

-- Limit to a Specific Package

-- and wst.top_instance_source_set_id = 43832

-- Limit to a Specific Line

-- and dpl.seq = '6'

-- limit to a date range

-- and wst.creation_date >= to_date('01-01-2008 01:00:01 AM', 'mm-dd-yyyy hh:mi:ss AM')--  Obtain a Workflow Step Id to feed into the above query
-- and wst.creation_date <= to_date('12-31-2008 12:59:00 PM', 'mm-dd-yyyy hh:mi:ss AM')

-- Limit to migrations that were 'Complete' and that 'Succeeded'

--and wst.status = 'COMPLETE'
-- or wst.status = 'IN_PROGRESS' )
--and wst.visible_user_status_value = 'Succeeded'

-- Limit to production migrations

-- and ( wws.step_name like '%Migrate%' and wws.step_name like '%Prod%' )

order by wst.top_instance_source_set_id, dpl.seq, wst.last_update_date

-- Obtain workflow_step_id to feed into the above query

select ww.workflow_name,
       wws.step_name,
       wws.sort_order
from KWFL_WORKFLOW_STEPS wws,
     KWFL_WORKFLOWS ww
where wws.step_name like 'Migrate%'
  and wws.step_name like '%Prod%'
  and wws.workflow_id = ww.workflow_id
  and ww.workflow_name not like '%Old%'
order by ww.workflow_name

Download

KintanaScripts.zip (64 Kb)