Kintana Script : Package Line Transaction History

Purpose

Lists the information you see when highlighting a package line and clicking on [View] 'Line Trans History'.

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#",
       kpl.Seq "Line#",
       kuv2.Full_Name "Creator",
       wst.Creation_Date "Creation Date",
       wst.Last_Update_Date "Decision Date",
       wst.Visible_User_Status_Value "Decision",
       kuv.Full_Name "Decision Made By"
from   CLM.KDLV_PACKAGE_LINES dpl,
       CLM.KWFL_WORKFLOW_STEPS wws,
       CLM.KWFL_STEP_TRANSACTIONS wst,
       CLM.KNTA_USERS_V nuv,
       CLM.KNTA_USERS_V nuv2
where   wst.Instance_Source_Id = dpl.Package_Line_Id
    and wws.Workflow_Step_Id = wst.Workflow_Step_Id
    and wst.Last_Updated_By = nuv.User_Id
    and wst.Created_By = nuv2.User_id

-- Limit to a Specific Package

-- and wst.Top_Instance_Source_Set_Id = 60832

-- Limit to a specific step

-- and wst.Workflow_Step_Id = 23905

-- limit to a date range

-- and wst.creation_date >= to_date('01-01-2007 01:00:01 AM', 'mm-dd-yyyy hh:mi:ss AM')
-- and wst.creation_date <= to_date('12-31-2007 12:59:00 PM', 'mm-dd-yyyy hh:mi:ss AM')

order by wst.Top_Instance_Source_Set_Id, dpl.seq

--  Workflow Step Id

select wws.workflow_step_id, wws.step_name, ww.workflow_name,
from KWFL_WORKFLOW_STEPS wws,
     KWFL_WORKFLOWS ww
where wws.workflow_id = ww.workflow_id

Download

KintanaScripts.zip (58 Kb)