Approval result and package "Approval Status" UDP query

Products

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

Issue/Introduction

How can I know when a Package is in "Approved" state or *Rejected* or "Pending Approval"?

Environment

Harvest Software Change Manager all versions
Tested with Oracle database

Resolution

/* This can be run with SQL Plus and will list all users and groups in the approval list and who has approved, rejected or waiting */

SELECT NVL(HARUSER.REALNAME, HARUSER.USERNAME) AS APPROVER, 
       NVL(HARAPPROVEHISTACTIONVIEW.ACTION, 'Waiting') AS DECISION
  FROM HARPACKAGE
 INNER JOIN HARAPPROVELIST
    ON HARPACKAGE.STATEOBJID = HARAPPROVELIST.STATEOBJID
  LEFT JOIN HARAPPROVEHISTACTIONVIEW
    ON HARPACKAGE.PACKAGEOBJID = HARAPPROVEHISTACTIONVIEW.PACKAGEOBJID
   AND HARAPPROVELIST.USROBJID = HARAPPROVEHISTACTIONVIEW.USROBJID
 INNER JOIN HARUSER
    ON HARUSER.USROBJID = HARAPPROVELIST.USROBJID
 WHERE HARPACKAGE.PACKAGENAME = 'MR- 000001'   /* <---- Name of the package goes here */
UNION
SELECT HARUSERGROUP.USERGROUPNAME AS APPROVER,
  CASE
    WHEN INSTR(Z.ACTIONS, 'Rejected', 1, 1) > 0
    THEN 'Rejected'
    WHEN INSTR(Z.ACTIONS, 'Approved', 1, 1) > 0
    THEN 'Approved'
    ELSE 'Waiting'
  END                        AS DECISION
FROM
  (SELECT R.USRGRPOBJID,
    ListAgg(R.ACTION, ',') Within GROUP (
  ORDER BY R.ACTION) AS ACTIONS
  FROM
    (SELECT HARAPPROVELIST.USRGRPOBJID,
      HARUSERSINGROUP.USROBJID,
      NVL(HARAPPROVEHISTACTIONVIEW.ACTION, 'Waiting') AS ACTION
    FROM HARPACKAGE
    LEFT JOIN HARAPPROVELIST
    ON HARPACKAGE.STATEOBJID = HARAPPROVELIST.STATEOBJID
    LEFT JOIN HARUSERSINGROUP
    ON HARAPPROVELIST.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID
    LEFT JOIN HARAPPROVEHISTACTIONVIEW
    ON HARPACKAGE.PACKAGEOBJID        = HARAPPROVEHISTACTIONVIEW.PACKAGEOBJID
    AND HARUSERSINGROUP.USROBJID      = HARAPPROVEHISTACTIONVIEW.USROBJID
    WHERE HARAPPROVELIST.USRGRPOBJID IS NOT NULL
    AND HARPACKAGE.PACKAGENAME        = 'MR- 000001'   /* <---- Name of the package goes here */
    ) R
  GROUP BY R.USRGRPOBJID
  ) Z
INNER JOIN HARUSERGROUP
ON Z.USRGRPOBJID = HARUSERGROUP.USRGRPOBJID