Listing the latest versions in all packages in a state

Products

• CA Harvest Software Change Manager - OpenMake Meister
• CA Harvest Software Change Manager

Issue/Introduction

How can we create a report that scans through all packages in a state and lists the latest versions of all items in those packages?

Environment

Harvest Software Change Manager v12.x and higher

Resolution

Here is a SQL query that will list the latest versions in all packages in a specified project and state:

(works with both Oracle and SQL Server)

SELECT PACKAGENAME, PATHFULLNAME, ITEMNAME, MAPPEDVERSION
FROM HARVERSIONS,
(SELECT HARENVIRONMENT.ENVOBJID,
    HARSTATE.STATEOBJID,
    HARPACKAGE.PACKAGENAME,
    HARPATHFULLNAME.PATHFULLNAME,
    HARITEMNAME.ITEMNAME,
    MAX(HARVERSIONS.VERSIONOBJID) VERSIONOBJID
  FROM HARENVIRONMENT
    INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
    INNER JOIN HARPACKAGE ON HARSTATE.STATEOBJID = HARPACKAGE.STATEOBJID
    INNER JOIN HARVERSIONS ON HARPACKAGE.PACKAGEOBJID = HARVERSIONS.PACKAGEOBJID
    INNER JOIN HARPATHFULLNAME ON HARVERSIONS.PATHVERSIONID = HARPATHFULLNAME.VERSIONOBJID
    INNER JOIN HARITEMNAME ON HARITEMNAME.NAMEOBJID = HARVERSIONS.ITEMNAMEID
  WHERE HARPACKAGE.PACKAGENAME <> 'BASE'
  GROUP BY HARENVIRONMENT.ENVOBJID,
    HARSTATE.STATEOBJID,
    HARPACKAGE.PACKAGENAME,
    HARPATHFULLNAME.PATHFULLNAME,
    HARITEMNAME.ITEMNAME) LATESTVER,
  (SELECT HARENVIRONMENT.ENVOBJID, HARENVIRONMENT.ENVIRONMENTNAME, HARSTATE.STATEOBJID, HARSTATE.STATENAME
    FROM HARENVIRONMENT
    INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID) ENVNAME
WHERE HARVERSIONS.VERSIONOBJID = LATESTVER.VERSIONOBJID
AND ENVNAME.ENVOBJID = LATESTVER.ENVOBJID
AND ENVNAME.STATEOBJID = LATESTVER.STATEOBJID
AND ENVNAME.ENVIRONMENTNAME = 'Project Name Goes Here'  /* <--- Type project name here */
AND ENVNAME.STATENAME = 'State Name Goes Here'  /* <--- Type statae name here */