How to create report that show how often custom Workflows use standard Action Packs (PCK.AUTOMIC_*) and the Action names?

[Vlad Navazhylau]

Hopefully experts with AE DB schema can help with Select SQL Query for following task.

Before running upgrades to different standard Automic Action Packs (PCK.AUTOMIC_*) we would like to create report about how often different standard Automic Actions (JOBP with name PCK.AUTOMIC_*) are used within Custom Workflows (JOBP objects that have names that different than PCK.AUTOMIC_*)?

I know there is "Search for Use" option on AWI when we right-click on specific JOBP object, but since there are many standard Actions and system been in use for several years it will be very time consuming to utilize this function on one Action JOBP at a time.

Thanks,

Vlad Navazhylau

[Michael Dolinek]

Hi @Vlad Navazhylau

here a SQL query for MS SQL as a starting point:

select distinct jpp.JPP_Object as 'Action', oh.OH_Name as 'Workflow' from oh, jpp
where 
jpp.JPP_OH_Idnr = oh.OH_Idnr
and oh.OH_Name not like 'PCK.%'
and jpp.JPP_Object like 'PCK.%'
order by jpp.JPP_Object, oh.OH_Name
​

​

------------------------------
Engineering Program Manager
Broadcom
------------------------------

[Vlad Navazhylau]

@Michael Dolinek, 

Thank you for directions.

After small adjustments here is the SQL query we end-up using:
​

SELECT JPP.JPP_OBJECT AS 'ACTION', COUNT(JPP.JPP_OBJECT) AS USAGE_COUNT
FROM OH, JPP
WHERE 
JPP.JPP_OH_IDNR = OH.OH_IDNR
AND OH.OH_NAME NOT LIKE 'PCK.AUTOMIC%'
AND JPP.JPP_OBJECT LIKE 'PCK.AUTOMIC%'
GROUP BY JPP.JPP_OBJECT​