Finding all notify processes in a project

Products

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

Issue/Introduction

Do you know of any reports out there that would list all the email notification UDPs for a project?

Environment

Harvest Software Change Manager all versions and platforms
valid for both Oracle and SQL Server environments

Resolution

The following queries have been tested with both Oracle or SQL Server environments.

This query will identify all notify processes in the entire repository:

SELECT
  HARENVIRONMENT.ENVIRONMENTNAME,
  HARENVIRONMENT.ENVISACTIVE,
  HARSTATE.STATENAME,
  HARSTATE.STATEORDER,
  '' AS PARENTPROCESS,
  '' AS PROCESSPRELINK,
  HARSTATEPROCESS.PROCESSNAME
FROM HARENVIRONMENT
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEPROCESS ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID
INNER JOIN HARNOTIFY ON HARSTATEPROCESS.PROCESSOBJID = HARNOTIFY.PROCESSOBJID
           AND HARSTATEPROCESS.STATEOBJID = HARNOTIFY.STATEOBJID
UNION SELECT
  HARENVIRONMENT.ENVIRONMENTNAME,
  HARENVIRONMENT.ENVISACTIVE,
  HARSTATE.STATENAME,
  HARSTATE.STATEORDER,
  HARSTATEPROCESS.PROCESSNAME AS PARENTPROCESS,
  HARLINKEDPROCESS.PROCESSPRELINK,
  HARNOTIFY.PROCESSNAME
FROM HARENVIRONMENT
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEPROCESS ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID
INNER JOIN HARLINKEDPROCESS ON HARSTATEPROCESS.STATEOBJID = HARLINKEDPROCESS.STATEOBJID
           AND HARSTATEPROCESS.PROCESSOBJID = HARLINKEDPROCESS.PARENTPROCOBJID
INNER JOIN HARNOTIFY ON HARLINKEDPROCESS.PROCESSOBJID = HARNOTIFY.PROCESSOBJID
           AND HARLINKEDPROCESS.PARENTPROCOBJID = HARNOTIFY.PARENTPROCOBJID

To make it specific to a single project, add a where clause:

SELECT
  HARENVIRONMENT.ENVIRONMENTNAME,
  HARENVIRONMENT.ENVISACTIVE,
  HARSTATE.STATENAME,
  HARSTATE.STATEORDER,
  '' AS PARENTPROCESS,
  '' AS PROCESSPRELINK,
  HARSTATEPROCESS.PROCESSNAME
FROM HARENVIRONMENT
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEPROCESS ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID
INNER JOIN HARNOTIFY ON HARSTATEPROCESS.PROCESSOBJID = HARNOTIFY.PROCESSOBJID
           AND HARSTATEPROCESS.STATEOBJID = HARNOTIFY.STATEOBJID
WHERE HARENVIRONMENT.ENVIRONMENTNAME = 'My Project Name' /* <=== Project Name goes here */
UNION SELECT
  HARENVIRONMENT.ENVIRONMENTNAME,
  HARENVIRONMENT.ENVISACTIVE,
  HARSTATE.STATENAME,
  HARSTATE.STATEORDER,
  HARSTATEPROCESS.PROCESSNAME AS PARENTPROCESS,
  HARLINKEDPROCESS.PROCESSPRELINK,
  HARNOTIFY.PROCESSNAME
FROM HARENVIRONMENT
INNER JOIN HARSTATE ON HARENVIRONMENT.ENVOBJID = HARSTATE.ENVOBJID
INNER JOIN HARSTATEPROCESS ON HARSTATE.STATEOBJID = HARSTATEPROCESS.STATEOBJID
INNER JOIN HARLINKEDPROCESS ON HARSTATEPROCESS.STATEOBJID = HARLINKEDPROCESS.STATEOBJID
           AND HARSTATEPROCESS.PROCESSOBJID = HARLINKEDPROCESS.PARENTPROCOBJID
INNER JOIN HARNOTIFY ON HARLINKEDPROCESS.PROCESSOBJID = HARNOTIFY.PROCESSOBJID
           AND HARLINKEDPROCESS.PARENTPROCOBJID = HARNOTIFY.PARENTPROCOBJID
WHERE HARENVIRONMENT.ENVIRONMENTNAME = 'My Project Name' /* <=== Project Name goes here */