dbo.QBM_PDialogProcessShrink_fill

1  create   procedure QBM_PDialogProcessShrink_fill ( @RepairSubstituteOnly bit = 0   ) as begin   declare @info nvarchar(64) declare @datum datetime
2 select @datum = GetUTCDate()  SET XACT_ABORT OFF BEGIN TRY  if exists (select top 1 1 from DialogProcessSubstitute x where Not exists (select top 1 1 
3from DialogProcess p where p.GenProcID = x.GenProcIDOrigin ) ) begin select @info = N'DialogProcessSubstitute' insert into dialogprocess (GenProcID, BasisObjectType
4 , ObjectKey, ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated , CustomComment, XTouched, DisplayName ) select GenProcID, BasisObjectType
5 , ObjectKey, ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated , CustomComment, XTouched, DisplayName from (select x.GenProcIDOrigin
6 as GenProcID, N'unknown Object' BasisObjectType,  null as ObjectKey,  'E' as ProcessState, @datum as XDateInserted, @datum as XDateUpdated, 'GenProcIDRepair'
7 as XUserInserted, 'GenProcIDRepair' as XUserUpdated, N'Inserted from ' + @info as CustomComment, '' as XTouched, N'Inserted from ' + @info as DisplayName
8 from DialogProcessSubstitute x left outer join Dialogprocess p on x.GenProcIDOrigin = p.GenProcID  where  p.GenProcID is null group by x.GenProcIDOrigin
9 ) as z where Not exists (select top 1 1 from DialogProcess y where y.GenProcID = z.GenProcID ) end if exists (select top 1 1 from DialogProcessSubstitute
10 x where Not exists (select top 1 1 from DialogProcess p where p.GenProcID = x.GenProcIDNew ) ) begin select @info = N'DialogProcessSubstitute' insert 
11into dialogprocess (GenProcID, BasisObjectType , ObjectKey, ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated , CustomComment, XTouched
12, DisplayName ) select GenProcID, BasisObjectType , ObjectKey, ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated , CustomComment
13, XTouched, DisplayName from (select x.GenProcIDNew as GenProcID, N'unknown Object' BasisObjectType,  null as ObjectKey,  'E' as ProcessState, @datum as
14 XDateInserted, @datum as XDateUpdated, 'GenProcIDRepair' as XUserInserted, 'GenProcIDRepair' as XUserUpdated, N'Inserted from ' + @info as CustomComment
15, '' as XTouched, N'Inserted from ' + @info as DisplayName from DialogProcessSubstitute x left outer join Dialogprocess p on x.GenProcIDNew = p.GenProcID
16  where  p.GenProcID is null group by x.GenProcIDnew ) as z where Not exists (select top 1 1 from dialogprocess y where y.GenProcID = z.GenProcID ) end
17 if @RepairSubstituteOnly = 1 begin goto endLabel end   if exists (select top 1 1 from DialogWatchOperation x where Not exists (select top 1 1 from DialogProcess
18 p where p.GenProcID = x.GenProcID ) ) begin select @info = N'DialogWatchOperation' insert into dialogprocess (GenProcID, BasisObjectType , ObjectKey, 
19ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated , CustomComment, XTouched, DisplayName ) select GenProcID , case when z.BasisObjectType
20 = '<unknown Object>' and z.ObjectKey > ' ' then dbo.QBM_FCVObjectkeyToElement('TableName', z.ObjectKey) else z.BasisObjectType end as BasisObjectType 
21, ObjectKey , ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated , CustomComment, XTouched, DisplayName from (select x.GenProcID,
22 '<unknown Object>' as BasisObjectType, max(x.ObjectKeyOfRow) as ObjectKey, 'E' as ProcessState, min(x.OperationDate) as XDateInserted, min(x.OperationDate
23) as XDateUpdated, max(x.OperationUser) as XUserInserted, max(x.OperationUser) as XUserUpdated, N'Inserted from ' + @info as CustomComment, '' as XTouched
24, max(x.DisplayValue) as DisplayName from DialogWatchOperation x left outer join Dialogprocess p on x.GenProcID = p.GenProcID  where  p.GenProcID is null
25 group by x.GenProcID ) as z where Not exists (select top 1 1 from dialogprocess y where y.GenProcID = z.GenProcID ) end   if exists (select top 1 1 from
26 Dialogprocesschain x where Not exists (select top 1 1 from DialogProcess p where p.GenProcID = x.GenProcID ) ) begin select @info = N'Dialogprocesschain'
27 insert into dialogprocess (GenProcID, BasisObjectType , ObjectKey, ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated , CustomComment
28, XTouched, DisplayName ) select GenProcID, BasisObjectType , ObjectKey, ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated , CustomComment
29, XTouched, DisplayName from (select x.GenProcID, max(x.BasisObjectType) as BasisObjectType , max(x.ObjectKey) as ObjectKey , 'E' as ProcessState, max(x.XDateInserted
30) as XDateInserted , max(x.XDateUpdated) as XDateUpdated , max(x.XUserInserted) as XUserInserted , max(x.XUserUpdated) as XUserUpdated , max(x.JobChainName
31) as CustomComment , '' as XTouched , max(x.DisplayName) as DisplayName from Dialogprocesschain x left outer join Dialogprocess p on x.GenProcID = p.GenProcID
32  where  p.GenProcID is null group by x.GenProcID ) as z where Not exists (select top 1 1 from dialogprocess y where y.GenProcID = z.GenProcID ) end   
33if exists (  select top 1 1 from Jobhistory x  left outer join Dialogprocess p on x.GenProcID = p.GenProcID  where p.GenProcID is null ) begin select @info
34 = N'JobHistory'  insert into dialogprocess (GenProcID, BasisObjectType , ObjectKey, ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated
35 , CustomComment, XTouched, DisplayName ) select GenProcID, BasisObjectType , ObjectKey, ProcessState, XDateInserted, XDateUpdated , XUserInserted , XUserUpdated
36 , CustomComment, XTouched, DisplayName from (select x.GenProcID, max(case when x.BasisObjectKey > ' ' then dbo.QBM_FCVObjectkeyToElement('TableName', 
37x.BasisObjectKey) else '<unknown Object>' end )as BasisObjectType, max(case when x.BasisObjectKey > ' ' then x.BasisObjectKey else null end )as ObjectKey
38, 'E' as ProcessState, max(x.XDateInserted) as XDateInserted, max(x.XDateUpdated) as XDateUpdated, max(x.XUserInserted) as XUserInserted, max(x.XUserUpdated
39) as XUserUpdated, max(JobChainName) as CustomComment, '' as XTouched, max(JobChainName) as DisplayName from Jobhistory x left outer join Dialogprocess
40 p on x.GenProcID = p.GenProcID  where p.GenProcID is null group by x.GenProcID ) as z where Not exists (select top 1 1 from dialogprocess y where y.GenProcID
41 = z.GenProcID )  end  update dialogprocess set xdateupdated = GetUTCDate() where xdateupdated is null  update dialogprocess set xdateinserted = GetUTCDate
42() where xdateinserted is null  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()
43 RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH endLabel: return end 
44