Need a list of latest versions in all packages in a state

Document ID : KB000045193
Last Modified Date : 14/02/2018
Show Technical Document Details

Question: 

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

Environment:  

CA Harvest SCM all versions and platforms

Answer: 

Here is a BIRT report that will list the latest versions in all packages in a state:

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
WHERE HARVERSIONS.VERSIONOBJID = LATESTVER.VERSIONOBJID
AND ENVOBJID = ${PROJECT_ID}
AND STATEOBJID = ${STATE_ID}

Additional Information:

Information about creating and using BIRT reports in SCM Workbench can be found in the Workbench User Guide: SCM Workbench BIRT Reporting

As always, please contact CA Technologies support for CA Datacom if you have further questions.