dependency issue on demotion

Document ID : KB000103757
Last Modified Date : 29/06/2018
Show Technical Document Details
Introduction:
When attempting to demote a package the following error is produced: 

---------- Begin <Demote from QA Testing to Development> Process --------------- 
E03020014: Detected version dependency on the package <package name> in state <state name>
E03020011: The package Demote verification failed. 
E03060019: Process Execute failed. Process Name: Demote from QA Testing to Development. 
---------- End <Demote from QA Testing to Development> Process --------------- 

which is weird because that package does not exist. I ran the following SQL directly against the database to verify that the package does not exist. 

Select E.environmentname, S.statename, P.packagename, P.CREATIONTIME, P.MODIFIEDTIME 
from harpackage P, harenvironment E, harstate S 
where p.STATEOBJID = s.stateobjid 
and p.envobjid = e.envobjid 
and P.packagename like '<package name>' 
order by E.environmentname, P.packagename; 

my question is; what is the sql that is being run to detect dependencies so that I can run it by hand. Thank you.
Environment:
CA Harvest SCM all versions and platforms
Instructions:
It would be good to find the package. If we use left joins rather than “=” we might find if there is a broken link somewhere. Can you try this: 

SELECT HARPACKAGE.PACKAGEOBJID, 
HARPACKAGE.PACKAGENAME, 
HARPACKAGE.ENVOBJID, 
HARPACKAGE.STATEOBJID, 
HARSTATE.STATENAME, 
HARSTATE.ENVOBJID AS ENVOBJID1, 
HARENVIRONMENT.ENVIRONMENTNAME 
FROM HARPACKAGE 
LEFT JOIN HARSTATE ON HARPACKAGE.STATEOBJID = HARSTATE.STATEOBJID 
LEFT JOIN HARENVIRONMENT ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID 
WHERE UPPER(HARPACKAGE.PACKAGENAME) LIKE '<package name>' 
ORDER BY HARENVIRONMENT.ENVIRONMENTNAME, HARPACKAGE.PACKAGENAME 

Based on your error message we’re looking for higher (newer) versions of the items in the package (or list of packages) that exist in the current state’s data view and are not included in the selected package (or list of packages). It can get complicated pretty fast, but if we narrow the scope to just one package we’re trying to demote it can simplify things a bit. I think this should work: 

SELECT HARENVIRONMENT.ENVIRONMENTNAME AS SELECTEDENVIRONMENTNAME, 
HARSTATE.STATENAME AS SELECTEDSTATENAME, 
HARPACKAGE.PACKAGENAME AS SELECTEDPACKAGENAME, 
PKGVERSIONS.VERSIONOBJID AS SELECTEDVERSIONOBJID, 
VVPACKAGE.PACKAGENAME AS NEWERPACKAGENAME, 
VVPACKAGE.PACKAGEOBJID AS NEWERPACKAGEOBJID, 
HARPATHFULLNAME.PATHFULLNAME AS NEWERPATHFULLNAME, 
HARITEMNAME.ITEMNAME AS NEWERITEMNAME, 
VIEWVERSION.MAPPEDVERSION AS NEWERMAPPEDVERSION, 
VIEWVERSION.VERSIONSTATUS AS NEWERVERSIONSTATUS, 
VIEWVERSION.CREATIONTIME AS NEWERCREATIONTIME, 
VIEWVERSION.MODIFIEDTIME AS NEWERMODIFIEDTIME 
FROM HARENVIRONMENT 
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID 
INNER JOIN HARPACKAGE ON HARSTATE.STATEOBJID = HARPACKAGE.STATEOBJID 
INNER JOIN HARVERSIONS PKGVERSIONS ON HARPACKAGE.PACKAGEOBJID = PKGVERSIONS.PACKAGEOBJID 
INNER JOIN HARVERSIONINVIEW ON HARVERSIONINVIEW.VIEWOBJID = HARSTATE.VIEWOBJID 
INNER JOIN HARVERSIONS VIEWVERSION ON HARVERSIONINVIEW.VERSIONOBJID = VIEWVERSION.VERSIONOBJID 
AND PKGVERSIONS.ITEMOBJID = VIEWVERSION.ITEMOBJID 
INNER JOIN HARPACKAGE VVPACKAGE ON VVPACKAGE.PACKAGEOBJID = VIEWVERSION.PACKAGEOBJID 
INNER JOIN HARPATHFULLNAME ON VIEWVERSION.PATHVERSIONID = HARPATHFULLNAME.VERSIONOBJID 
INNER JOIN HARITEMNAME ON HARITEMNAME.NAMEOBJID = VIEWVERSION.ITEMNAMEID 
WHERE HARENVIRONMENT.ENVIRONMENTNAME = 'SampleProject' /* <- - - project name goes here */ 
AND HARSTATE.STATENAME = 'Test' /* <- - - state name goes here */ 
AND HARPACKAGE.PACKAGENAME = 'Older_version' /* <- - - package name goes here */ 
AND PKGVERSIONS.VERSIONOBJID < VIEWVERSION.VERSIONOBJID 
AND HARPACKAGE.PACKAGEOBJID <> VVPACKAGE.PACKAGEOBJID