dbo.QBM_FSQXDateSubItemUpdate

1        create   function dbo.QBM_FSQXDateSubItemUpdate (@ChildTable varchar(30) , @operation varchar(16) ) returns nvarchar(max) as begin declare
2 @ProzedurnameAusschluss varchar(64) = 'QBM_ZXMarkedForDeletionPush' declare @IstMitXOrigin bit = 0 declare @IstMitXIsInEffect bit = 0 declare @IstMitXMarkedForDeletion
3 bit = 0 declare @StringPatternOutStanding varchar(16) declare @StringPatternOutStandingInv varchar(16)  declare @ElementBuffer QBM_YCursorBuffer    declare
4 @UID_TaskNormal varchar(38) = 'QBM-K-XDateSubItemUpdate' declare @UID_Task_FU varchar(38) = 'QBM-K-XDateSubItemUpdateFU' select top 1 @UID_TaskNormal 
5= ta.UID_Task from DialogTable t with (readpast) join QBMDBQueueTask ta with (readpast) on ta.UID_Task = left(t.UID_DialogTable, 3) + '-K-XDateSubItemUpdate'
6 where t.TableName = @ChildTable select top 1 @UID_Task_FU = ta.UID_Task from DialogTable t with (readpast) join QBMDBQueueTask ta with (readpast) on ta.UID_Task
7 = left(t.UID_DialogTable, 3) + '-K-XDateSubItemUpdateFU' where t.TableName = @ChildTable select @StringPatternOutStanding = dbo.QBM_FCVBinaryToString(
8 CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',0)), 0) select @StringPatternOutStandingInv = dbo.QBM_FCVBinaryToString( CONVERT
9(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|',1)), 0) declare @opSynonym varchar(16) select @opSynonym = case @operation when 'insert'
10 then 'inserted' when 'delete' then 'deleted' else '' end declare @Vorspann nvarchar(max) = '
11			declare @DBQueueElements_@ChildCol@ QBM_YDBQueueRaw
12		
13			insert into @DBQueueElements_@ChildCol@ (object, subobject, genprocid)'
14 declare @Nachspann nvarchar(max) = '
15				) as x 
16
17			exec QBM_PDBQueueInsert_Bulk @UID_TaskXDateSubItemUpdate , @DBQueueElements_@ChildCol@ 
18	' declare
19 @Condition nvarchar(max) = ''  declare @AllStatements nvarchar(max) = ''  if exists (select top 1 1 from DialogColumn c with (readpast) join DialogTable
20 t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where t.TableName = @ChildTable and c.ColumnName = 'XOrigin' ) begin select @IstMitXOrigin 
21= 1 if exists (select top 1 1 from DialogColumn c with (readpast) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where t.TableName
22 = @ChildTable and c.ColumnName = 'XIsInEffect' ) begin select @IstMitXIsInEffect = 1 end end if exists (select top 1 1 from DialogColumn c with (readpast
23) join DialogTable t with (readpast) on c.UID_DialogTable = t.UID_DialogTable where t.TableName = @ChildTable and c.ColumnName = 'XMarkedForDeletion' )
24 begin select @IstMitXMarkedForDeletion = 1 end if @IstMitXOrigin = 1 begin select @Condition = case @operation when 'insert' then 'where i.XOrigin > 0'
25 when 'update' then 'where (dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, i.XOrigin, 1, 1) = 1 ' + NCHAR(13) + NCHAR(10) + '  @25923@   ' + NCHAR(13) + NCHAR
26(10) + '    ) ' + NCHAR(13) + NCHAR(10) + '@xdc@' when 'delete' then '' else '' end if @IstMitXIsInEffect = 1 begin select @Condition = case @operation
27 when 'insert' then 'where i.XIsInEffect > 0' when 'update' then 'where (dbo.QBM_FGIXOriginChanged_Effect(d.XOrigin, i.XOrigin, d.XIsInEffect, i.XIsInEffect) = 1'
28 + NCHAR(13) + NCHAR(10) + '  @25923@   ' + NCHAR(13) + NCHAR(10) + '    ) ' + NCHAR(13) + NCHAR(10) + '@xdc@' when 'delete' then '' else '' end if @operation
29 = 'update' begin select @Vorspann = '
30		if update(XOrigin)
31		 or update(XIsInEffect)
32		 or @xdi@
33		 begin
34				' + @Vorspann select @Nachspann = @Nachspann
35 + '
36		 end
37				' end end else begin  if @operation = 'update' begin select @Vorspann = '
38		if update(XOrigin)
39		 or @xdi@
40		 begin
41				' + @Vorspann
42 select @Nachspann = @Nachspann + '
43		 end
44				' end end end else begin  select @Condition = case @operation when 'insert' then '-- where 1=1' when 'update'
45 then 'where 1 = 0'+NCHAR(13) +NCHAR(10) + '@xdc@' when 'delete' then '@xdd@' else '' end if @operation = 'update' and @IstMitXMarkedForDeletion = 1 begin
46 select @Vorspann = '
47		if @xdi@
48		 begin
49				' + @Vorspann select @Nachspann = @Nachspann + '
50		 end
51				' end end if @operation = 'update' and @IstMitXMarkedForDeletion
52 = 1 begin select @Condition = replace(@Condition, '@xdc@', '
53							or (d.XMarkedForDeletion & ' + @StringPatternOutStanding + ' > 0
54								and i.XMarkedForDeletion & '
55 + @StringPatternOutStanding + ' = 0
56								and @ActionUser <> ''' + @ProzedurnameAusschluss + '''
57								' + case @IstMitXOrigin when 1 then ' and i.XOrigin > 0'
58 else '' end + '
59							  )' ) end if @IstMitXMarkedForDeletion = 1 begin select @vorspann = replace(@vorspann, '@xdi@', dbo.QBM_FCVStringToIndent( ' (update(XMarkedForDeletion)
60				and @ActionUser <> '''
61 + @ProzedurnameAusschluss + '''
62			)
63	', 3)) select @Condition = replace(replace(@condition , '@25923@', 'and d.XMarkedForDeletion & ' + @StringPatternOutStanding
64 + ' = 0') , '@xdd@', 'where i.XMarkedForDeletion & ' + @StringPatternOutStanding + ' = 0') end else begin select @vorspann = replace(@vorspann, '@xdi@'
65, dbo.QBM_FCVStringToIndent( ' 1=0' , 3)) select @Condition = replace(replace(@condition , '@25923@', '') , '@xdd@', '-- where 1=1') end insert into @ElementBuffer
66 (ContentFull) select  replace(@Vorspann, '@ChildCol@', cc.ColumnName) + '
67			select x.uid , ''' + tp.UID_DialogTable + ''' ,   @GenProcID
68		    from ( 			
69					select distinct p.'
70 + PKp.ColumnName + ' as uid
71			    ' + case @operation when 'update' then '	from ' + tc.TableName + ' i join deleted d on ' + dbo.QBM_FSQTableJoin(tc.TableName
72, 'd', 'i') + '
73										join ' + tp.TableName + ' p on i.' + cc.ColumnName + ' = p.' + cp.ColumnName + '
74			' else '	from ' + @opSynonym + ' /*' + 
75tc.TableName + '*/  i join ' + tp.TableName + ' p on i.' + cc.ColumnName + ' = p.' + cp.ColumnName + '
76			' end + '			' + @Condition + replace(@Nachspann
77, '@ChildCol@', cc.ColumnName) from QBMRelation r with (readpast) join DialogColumn cc with (readpast) on r.UID_ChildColumn = cc.UID_DialogColumn join 
78DialogTable tc with (readpast) on cc.UID_DialogTable = tc.UID_DialogTable and tc.TableType = 'T' join DialogColumn cp with (readpast) on r.UID_ParentColumn
79 = cp.UID_DialogColumn join DialogTable tp with (readpast) on cp.UID_DialogTable = tp.UID_DialogTable and tp.TableType = 'T' join DialogColumn cpch with
80 (readpast) on tp.UID_DialogTable = cpch.UID_DialogTable and cpch.ColumnName = 'XDateSubItem' join DialogColumn PKp with (readpast) on pkp.UID_DialogTable