Procedure - Generate Partial EPX Report - Precisely_EnterWorks - EnterWorks - 11.0

-- Extract Users for comparison
print '===========================Users==========================='
-- Extract Roles for comparison
print '===========================Roles==========================='
-- Extract Role Members for comparison
print '===========================Role Members==========================='

AND a2.DELETED_IND = 0

-- Extract Process Flows for comparison
print '===========================Process Flows==========================='
SELECT 'Flow=' + p.[NAME] as ProcessFlowName
          , 'Desc=' + isnull(p.DESCRIPTION, '') as ProcessFlowDescription
          ,case when (p.[PROCESS_TYPE_CODE] = 1) then 'Process Flow'
           when (p.[PROCESS_TYPE_CODE] = 2) then 'SubFlow'
           when (p.[PROCESS_TYPE_CODE] = 3) then 'Personal SubFlow'
           else CAST(p.[PROCESS_TYPE_CODE] as VARCHAR) end as FlowType
           ,'FlowValid=' + case when (p.[VALID_IND] = 1) then 'Yes' else 'No' end as Valid
FROM [P_PROCESS] p
WHERE p.NAME in ('New Brand Approval')
           order by p.[NAME]

-- Extract Process Activities for comparison
print '===========================Process Flow Activities==========================='
SELECT '**** Flow=' + p.[NAME] as ProcessFlowName
           , 'Activity=' + a.NAME as ActivityName
           , 'Type=' + case 
		   when (a.ACTIVITY_TYPE_CODE = 1) then 'AUTOMATIC: ' + a.ARC_ACTOR_NAME
           when (a.ACTIVITY_TYPE_CODE = 2) then 'SUBFLOW'
           when (a.ACTIVITY_TYPE_CODE = 3) then 'DECISION_POINT'
           when (a.ACTIVITY_TYPE_CODE = 4) then 'DISTRIBUTED_SUBFLOW'
           when (a.ACTIVITY_TYPE_CODE = 5) then 'WORK_ITEM_MERGE'
           when (a.ACTIVITY_TYPE_CODE = 6) then 'ITERATION'
           when (a.ACTIVITY_TYPE_CODE = 7) then 'JOIN: ' + case when (a.JOIN_TYPE = 1) then
 'OR' else 'AND' end
           when (a.ACTIVITY_TYPE_CODE = 8) then 'MANUAL'
           when (a.ACTIVITY_TYPE_CODE = 9) then 'ANONYMOUS'
           when (a.ACTIVITY_TYPE_CODE = 10) then 'PERSONAL_SUBFLOW'
           when (a.ACTIVITY_TYPE_CODE = 11) then 'SPLIT'
           when (a.ACTIVITY_TYPE_CODE = 12) then 'WORK_ITEM_REPEATER'
           when (a.ACTIVITY_TYPE_CODE = 14) then 'WORK_ITEM_PURGE'
           when (a.ACTIVITY_TYPE_CODE = 16) then 'ENDING_POINT'
           when (a.ACTIVITY_TYPE_CODE = 17) then 'LOAD_BALANCE'
           when (a.ACTIVITY_TYPE_CODE = 18) then 'CHANGE_PRIORITY'
           when (a.ACTIVITY_TYPE_CODE = 19) then 'SUBFLOW_EXIT'
           end as ActivityType
           , 'Enabled=' + case when (a.enabled_ind = 1) then 'E' else 'D' end as Enabled
           , 'Start=' + case when (a.start_point_ind = 1) then 'Y' else 'N' end as Start
           , 'End=' + case when (a.end_point_ind = 1) then 'Y' else 'N' end as [End]
           , 'SendOnError=' + case when (a.ERROR_SEND_IND = 1) then 'Y' else 'N' end as [SendOnError]
           , 'Valid=' + case when (a.VALID_IND = 1) then 'Y' else 'N' end as ActivityValid
           , 'Key=' + ap.PROPERTY_KEY + ' = ' + 
           isnull(case when (ap.PROPERTY_KEY = 'lastSentDate') THEN ''
           else case when (ap.PROPERTY_VALUE is not null) then ap.PROPERTY_VALUE 
           else isnull(convert(varchar(max), convert(varbinary(max),ap.PROPERTY_VALUE_BLOB)),'') 
           end
           end, '') as ActivityPropertyKey
           FROM [P_PROCESS] p
           join P_ACTIVITY a on p.PROCESS_ID = a.PROCESS_ID
           left outer join P_ACTIVITY_PROPERTY ap on a.ACTIVITY_ID = ap.ACTIVITY_ID
           where a.DELETED_IND = 0
           AND p.NAME in ('New Brand Approval')
           order by p.[NAME], a.NAME, ap.PROPERTY_KEY

-- Extract Process Activity Viewers for comparison
print '===========================Process Flow Activity Viewers==========================='
SELECT '**** Flow=' + p.[NAME] as ProcessFlowName
           , 'Activity=' + a.NAME as ActivityName
           , 'Start=' + case when (a.start_point_ind = 1) then 'Y' else 'N' end as Start
           , 'Viewer=' + wiv.NAME as ViewerName
           , 'URL=' + wiv.URL as ViewerURL
           , 'Default=' + case when wiv.DEFAULT_IND = 0 then 'No' 
           when wiv.DEFAULT_IND = 1 then 'Yes'
           else 'Unknown:' + CAST(wiv.default_ind as VARCHAR)
           end as DefaultViewer
           FROM [P_PROCESS] p
           join P_ACTIVITY a on p.PROCESS_ID = a.PROCESS_ID
           join p_ACTIVITY_VIEWER av on av.ACTIVITY_ID = a.ACTIVITY_ID
           join P_WORK_ITEM_VIEWER wiv on av.VIEWER_ID = wiv.VIEWER_ID
           WHERE a.DELETED_IND = 0
           AND p.NAME in ('New Brand Approval')
           order by p.[NAME], a.NAME, wiv.NAME

-- Extract Process Flow Activity Transitions for comparison
print '===========================Process Flow Activity Transitions==========================='
SELECT 'Flow=' + p.[NAME] as ProcessFlowName
           , 'From=' + a.NAME as ActivityName
           , 'To=' + isnull(a2.NAME, '') as TargetActivityName
           , case when (tc.CONDITION_ID IS not null) then
           'Con=' + case when (c.CONDITION_TYPE_CODE = 3) then 'Otherwise'
           when (c.CONDITION_TYPE_CODE = 1) then 'Simple: ' + c.COMPARE_KEY + C.OPERATOR_CODE + c.COMPARE_VALUE
           when (c.CONDITION_TYPE_CODE = 2) then 'Advanced: ' + isnull(convert(nvarchar(max),c.FREE_EXPRESSION_CLOB), '')
           else 'Unknown: ' + cast(c.CONDITION_TYPE_CODE as varchar) end 
           else '' end as Condition
           FROM [P_PROCESS] p
           join P_ACTIVITY a on p.PROCESS_ID = a.PROCESS_ID
           left outer join P_TRANSITION t on t.PARENT_ACTIVITY_ID = a.ACTIVITY_ID
           left outer join P_ACTIVITY a2 on t.CHILD_ACTIVITY_ID = a2.ACTIVITY_ID
           left outer join P_TRANSITION_CONDITION tc on tc.TRANSITION_ID = t.TRANSITION_ID
           left outer join P_CONDITION c on tc.CONDITION_ID = c.CONDITION_ID
           WHERE a.DELETED_IND = 0
           AND a2.DELETED_IND = 0
           AND p.NAME in ('New Brand Approval') 
           order by 1,2,3,4