Looking for updates HarWeb SQL to pull data

Document ID : KB000107177
Last Modified Date : 02/08/2018
Show Technical Document Details
Introduction:
For auditing purposes, we occasionally have to provide Production deployment information.  I currently use the SQL below to pull the information we need via HarWeb, but I am looking for help to update this.  The following SQL attaches the User ID of the person that created the package..  I need to be able to pull the ID that promotes/deploys to Production....which is usually not the package creator.  Is it possible to get the ID from the history when going to Prod, and if so, what change would the SQL need ?   Thanks much for any info you can provide !  ( see SQL below ) :

SELECT distinct a2.Packagename, a1.Environmentname, a3.Viewname, a2.Creationtime, a4.Username
from HARENVIRONMENT a1,
     HARPACKAGE a2,
     HARVIEW a3,
     HARALLUSERS a4 
where (a3.viewname = 'Production'
      and a2.viewobjid = a3.viewobjid 
      and a2.creationtime between '01-jan-17'AND '17-aug-17'      
      )
      and a1.envobjid = a2.envobjid
      and a2.creatorid = a4.usrobjid
order by a1.environmentname;
Environment:
CA Harvest SCM all versions and platforms.
Query was written for an Oracle database
Instructions:
Actually, the information you need is in the HARPKGHISTORY table, which records package creation, promotes, demotes and switch-package processes. This query will come closer to what you need: 

SELECT HARPACKAGE.PACKAGEOBJID, 
HARPACKAGE.PACKAGENAME, 
HARPKGHISTORY.ENVIRONMENTNAME, 
HARPKGHISTORY.STATENAME, 
HARPKGHISTORY.ACTION, 
TO_CHAR(HARPKGHISTORY.EXECDTIME, 'mm/dd/yyyy hh:mi:ss') EXECDTIME, 
HARUSER.USERNAME 
FROM HARPACKAGE 
INNER JOIN HARPKGHISTORY ON HARPACKAGE.PACKAGEOBJID = HARPKGHISTORY.PACKAGEOBJID 
INNER JOIN HARUSER ON HARUSER.USROBJID = HARPKGHISTORY.USROBJID 
WHERE UPPER(HARPKGHISTORY.ACTION) = 'PROMOTE' 
AND HARPKGHISTORY.STATENAME = 'Production' 
AND HARPKGHISTORY.EXECDTIME BETWEEN '01-jan-17'AND '17-aug-17'