Auto-void orphaned Action Items (GET_ORPHANED_ACTION_ITEMS).SQL

-- This SQL can be used as the source for the GET_ORPHANED_ACTION_ITEMS data source
-- This SQL is an example to show how you can construct similar queries for other modules

-- Change Management Action Items
	SELECT 'CHANGE' AS APP_NAME,
		(SELECT F.FORM_NAME
		 FROM ENGINE.FORM_SETTINGS F
		 JOIN ENGINE.APPLICATION_FORMS AF ON F.FORM_ID=AF.FORM_ID
		 JOIN ENGINE.APPLICATION_SETTINGS A ON A.APPLICATION_ID=AF.APPLICATION_ID
		 WHERE A.APPLICATION_NAME='CHANGE'
		   AND F.TABLE_NAME='ACTION_ITEM') AS FORM_NAME,
		ACTION_ITEM.ACTION_ITEM_ID DOC_ID,
		--ACTION_ITEM.ETQ$CURRENT_PHASE AS CURRENT_PHASE,
		(SELECT PS.PHASE_ID
		 FROM ENGINE.WF_PHASE_INFO CP
		 JOIN ENGINE.WF_PHASE_INFO WP ON CP.WORKFLOW_ID=WP.WORKFLOW_ID
		 JOIN ENGINE.PHASE_SETTINGS PS ON WP.PHASE_ID=PS.PHASE_ID
		 WHERE CP.PHASE_ID=ACTION_ITEM.ETQ$CURRENT_PHASE
		   AND PS.PHASE_TYPE=3) AS VOID_PHASE
		FROM CHANGE.ACTION_ITEM ACTION_ITEM
		LEFT JOIN CHANGE.ETQ$ACTION_ITEM_SL SL ON SL.ACTION_ITEM_ID=ACTION_ITEM.ACTION_ITEM_ID
		LEFT JOIN CHANGE.ETQ$DOCUMENT_LINKS DL ON (SL.ETQ$SOURCE_LINK = DL.LINK_ID AND  DL.FORM_ID = 33 )
		LEFT JOIN CHANGE.CHANGE_DOCUMENT CD ON (DL.DOCUMENT_ID = CD.CHANGE_ID )
		LEFT JOIN ENGINE.PHASE_SETTINGS CDP ON CD.ETQ$CURRENT_PHASE=CDP.PHASE_ID
		LEFT JOIN ENGINE.PHASE_SETTINGS AS PHASE ON (ACTION_ITEM.ETQ$CURRENT_PHASE = PHASE.PHASE_ID)
		WHERE CDP.PHASE_TYPE = 3		     -- parent change request is voided
		  AND PHASE.PHASE_TYPE IN (0, 5) -- action item is still open