JanSrp
commented
2 years ago -- Dotaz na vrácení procesů bez retention policy ;with CTE as( SELECT [PROCESS_ID] ,[PROCESS_NAME] ,[VERSION] ,[LATEST_VERSION] ,KeepForever=[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/KeepForever)[1]', 'bit') ,Years=[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/RetentionPolicyDuration/Years)[1]', 'int') ,Months=[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/RetentionPolicyDuration/Months)[1]', 'int') ,Days=[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/RetentionPolicyDuration/Days)[1]', 'int') ,Hours=[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/RetentionPolicyDuration/Hours)[1]', 'int') ,Minutes=[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/RetentionPolicyDuration/Minutes)[1]', 'int') --,[CATEGORY_ID] --,[CHANGE_DATE] --,[CREATION_DATE] --,[CREATOR] ,[PROCESS_TYPE] --,[SETTINGS_XML] FROM [dbo].[BUSINESS_PROCESS] ) select * from CTE where KeepForever = 1 and [LATEST_VERSION] = 1 and [PROCESS_TYPE] != 5 -- nezobrazuju BR order by 1
use [TotalAgility] -- Process
-- Save unique process Ids with latest version and category for further use IF OBJECT_ID('tempdb.dbo.#ProcessId', 'U') IS NOT NULL DROP TABLE #ProcessId;
create table #ProcessId (CategoryId binary(16),CategoryName nvarchar(255), ProcessId binary(16), ProcessName nvarchar(255), ProcessVersion decimal(8,3))
insert into #ProcessId (ProcessId) select distinct [PROCESS_ID] from [dbo].[BUSINESS_PROCESS]
UPDATE #ProcessId SET #ProcessId.CategoryName = c.[NAME] ,#ProcessId.CategoryId = c.[CATEGORY_ID] ,#ProcessId.ProcessName = bp.[PROCESS_NAME] ,#ProcessId.ProcessVersion = bp.[VERSION] FROM #ProcessId b cross apply (select top 1 from [dbo].[BUSINESS_PROCESS] bp1 where bp1.[PROCESS_ID]=b.[ProcessId] order by bp1.[VERSION] desc) bp left join [dbo].[CATEGORY] c on c.[CATEGORY_ID] = bp.[CATEGORY_ID] --select from #ProcessId -- #Debug#
-- Process details select Category=p.CategoryName ,Process=p.ProcessName ,ProcessId=convert(nvarchar(max),p.ProcessId,2) ,Version=p.ProcessVersion ,Type=case when bp.[PROCESS_TYPE]=0 then 'Business process' when bp.[PROCESS_TYPE]=5 then 'Business rule' else 'Unknown process type' end ,Description=isnull(REPLACE(REPLACE(bp.[DESCRIPTION], CHAR(13), '#CR#'), CHAR(10), '#LF#'),'') ,HelpText=isnull(REPLACE(REPLACE(bp.[HELP_TEXT], CHAR(13), '#CR#'), CHAR(10), '#LF#'),'') ,UpdateDate=bp.[CHANGE_DATE] ,UpdateBy=case when nullif(awr.RESOURCE_NAME,'') is null then '' else concat(awr.RESOURCE_NAME,' (',ntr.NT_NAME,')') end ,Details='-----' ,BusinessRuleId=br.UNIQUE_ID ,Priority=bp.[PRIORITY] ,CaptureEnabled=bp.[CAPTURE_ENABLED] ,IncludeToPI=bp.ARCHIVE_TO_PI ,Synchronous=bp.[STRAIGHT_THROUGH] ,VariableHistory=bp.[STORE_VARIABLE_HISTORY] ,Retention=concat( 'Years: ',bp.[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/RetentionPolicyDuration/Years)[1]', 'int') ,' Months: ',bp.[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/RetentionPolicyDuration/Months)[1]', 'int') ,' Days: ',bp.[SETTINGS_XML].value('(BusinessProcessSettings/RetentionPolicySettings/RetentionPolicyDuration/Days)[1]', 'int') ) ,CreateJob_FormName=isnull(bp.[CNJ_FORM_NAME],'') ,CreateJob_FormId=bp.[CNJ_FORM_ID] ---- #Optional# --,bp.[SETTINGS_XML] --,bp.[DESIGN_TIME_SETTINGS] --,bp.[EMAIL_TO] --,bp.[SEND_EMAIL] from [dbo].[BUSINESS_PROCESS] bp inner join #ProcessId p on p.ProcessId=bp.[PROCESS_ID] and p.ProcessVersion = bp.[VERSION] left join [dbo].[AW_RESOURCE] awr on awr.RESOURCE_ID = bp.LAST_MODIFY_RESOURCE left join [dbo].[NT_RESOURCE] ntr on ntr.RESOURCE_ID = bp.LAST_MODIFY_RESOURCE left join [dbo].[BUSINESS_RULE] br on br.PROCESS_ID = bp.PROCESS_ID and br.VERSION = bp.VERSION order by p.CategoryName,p.ProcessName
IF OBJECT_ID('tempdb.dbo.#ProcessId', 'U') IS NOT NULL DROP TABLE #ProcessId;