search cancel

How to find all notify processes in a project?

book

Article ID: 254717

calendar_today

Updated On:

Products

CA Harvest Software Change Manager

Issue/Introduction

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

Environment

CA Harvest Software Change Manager

Release : all versions and platforms

Resolution

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 */