Need a BIRT report to show user/group access per project

Products

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

Issue/Introduction

We have a BIRT report that shows user group, users in the group, and to which processes they have been granted access for all projects in a division.  We need to modify this report to show the same for a specific project.  The Project Access report in the Administrator Tool does not show the names of the users in each group, so it does not provide the needed information.

Environment

Release : 13.0.3

Component : CA HARVEST SCM INFRASTRUCTURE (BROKER/AGENT/PEC/SECURITY

Resolution

SELECT ENVIRONMENTNAME AS PROJECT,

       USERGROUPNAME || ':' || CHR(10) || MEMBERS AS GROUPS,

       PROCESSES,

       ENVPROCESSES.NOTE

  FROM HARUSERGROUP,

       (SELECT ENVIRONMENTNAME,

               HARENVIRONMENT.ENVOBJID,

               USRGRPOBJID,

               PROCESSES,

               NOTE

          FROM HARENVIRONMENT,

               (SELECT ENVOBJID,

                       USRGRPOBJID,

                       LISTAGG(PROCESSNAME, CHR(10)) WITHIN GROUP (ORDER BY PROCESSNAME) AS PROCESSES

                  FROM (SELECT DISTINCT HARENVIRONMENT.ENVOBJID,

                               HARSTATEPROCESSACCESS.USRGRPOBJID,

                               HARSTATEPROCESS.PROCESSNAME

                          FROM HARENVIRONMENT

                         INNER JOIN HARSTATE

                               ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID

                         INNER JOIN HARSTATEPROCESS

                               ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID

                         INNER JOIN HARSTATEPROCESSACCESS

                               ON HARSTATEPROCESS.STATEOBJID = HARSTATEPROCESSACCESS.STATEOBJID

                               AND HARSTATEPROCESS.PROCESSOBJID = HARSTATEPROCESSACCESS.PROCESSOBJID

                         WHERE HARSTATEPROCESSACCESS.EXECUTEACCESS = 'Y'

                           AND HARENVIRONMENT.ENVISACTIVE = 'Y'

                       ) PGLIST

                 GROUP BY ENVOBJID, USRGRPOBJID

               ) ADDNOTEFLD

               WHERE HARENVIRONMENT.ENVOBJID = ADDNOTEFLD.ENVOBJID

       ) ENVPROCESSES,

       (SELECT USRGRPOBJID,

               LISTAGG(MEMBERS,CHR(10)) WITHIN GROUP (ORDER BY MEMBERS) AS MEMBERS

  FROM (SELECT DISTINCT HARUSERGROUP.USRGRPOBJID,

                       COALESCE(NULLIF(REALNAME, ''), USERNAME) AS MEMBERS

                  FROM HARUSERGROUP

                 INNER JOIN HARUSERSINGROUP

                       ON HARUSERGROUP.USRGRPOBJID = HARUSERSINGROUP.USRGRPOBJID

                 INNER JOIN HARUSER

                       ON HARUSER.USROBJID = HARUSERSINGROUP.USROBJID

                 WHERE HARUSERGROUP.USERGROUPNAME != 'Public'

                 UNION

                SELECT HARUSERGROUP.USRGRPOBJID, 'All Users'

                  FROM HARUSERGROUP

                 WHERE HARUSERGROUP.USERGROUPNAME = 'Public'

               ) UGLIST

         GROUP BY UGLIST.USRGRPOBJID

       ) GROUPUSERS

WHERE HARUSERGROUP.USRGRPOBJID = ENVPROCESSES.USRGRPOBJID

   AND HARUSERGROUP.USRGRPOBJID = GROUPUSERS.USRGRPOBJID

/* AND ENVPROCESSES.NOTE LIKE '056-006-DOE' */

   AND ENVPROCESSES.ENVOBJID = ${PROJECT_ID}

   ORDER BY lower(PROJECT), lower(GROUPS)