dbo.QBM_FSQXMarkedForDeletionChg

1       create   function dbo.QBM_FSQXMarkedForDeletionChg (@ParentTable varchar(30) , @operation varchar(16) ) returns nvarchar(max) as begin declare
2 @Pattern varchar(64) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|OutStanding|', 0)), 0) , @Pattern_nurO
3 varchar(64) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0)), 0) , @Pattern_nurO_inv varchar(64
4) = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 1)), 0) declare @UID_DialogTable varchar(38) declare
5 @PKColumnName varchar(30) declare @CountPK int declare @ElementBuffer QBM_YCursorBuffer declare @TSTables QBM_YParameterList  declare @erg nvarchar(max
6) = ''  declare @UID_TaskNormal varchar(38) = 'QBM-K-XMarkedForDeletionPush' select top 1 @UID_DialogTable = t.UID_DialogTable , @PKColumnName = t.PKName1
7 , @CountPK = case when t.PKName2 > ' ' then 2 else 1 end from DialogTable t with (readpast) where t.TableName = @ParentTable select top 1 @UID_TaskNormal
8 = ta.UID_Task from DialogTable t with (readpast) join QBMDBQueueTask ta with (readpast) on ta.UID_Task = left(t.UID_DialogTable, 3) + '-K-XMarkedForDeletionPush'
9 where t.TableName = @ParentTable if @operation in ('Insert', 'Delete') begin goto EndLabel end if dbo.QBM_FGIColumnExists(@ParentTable, 'XMarkedForDeletion'
10) = 1 begin if dbo.QBM_FGIColumnExists(@ParentTable, 'XDateSubItem') = 1 begin select @erg = concat('
11			declare @DBQueueElements_XMarkedForDel QBM_YDBQueueRaw
12	
13			insert into @DBQueueElements_XMarkedForDel (object, subobject, genprocid)
14			select x.uid, '''
15 , @UID_DialogTable , ''',   @GenProcID
16			from ( 			
17					select i.' , @PKColumnName , ' as uid
18	   				from ' , @ParentTable , ' i join deleted d on '
19 , dbo.QBM_FSQTableJoin(@ParentTable, 'd', 'i') , '
20						where (d.XMarkedForDeletion & ' , @Pattern , ')
21						  <>  (i.XMarkedForDeletion & ' , @Pattern
22 , ')
23				) as x 
24
25			exec QBM_PDBQueueInsert_Bulk ''', @UID_TaskNormal , ''' , @DBQueueElements_XMarkedForDel 
26		' )  end     if exists (select top
27 1 1 from sys.tables v with (readpast) where v.name = 'DPRNameSpaceHasDialogTable' )  and exists (select top 1 1 from QBM_VQBMRelationALL a join DialogTable
28 tc with (readpast) on a.UID_DialogTableChild = tc.UID_DialogTable join DialogColumn cc with (readpast) on tc.UID_DialogTable = cc.UID_DialogTable and 
29cc.ColumnName = 'XMarkedForDeletion' where a.ParentTable = @ParentTable and a.IsForUpdateXDateSubItem = 0 and ( tc.IsMAllTable = 1  or tc.isMNTable = 1
30 ) ) begin insert into @TSTables(Parameter1) select distinct t.TableName from DPRNameSpaceHasDialogTable nht with (readpast) join DialogTable t with (readpast
31) on nht.UID_DialogTable = t.UID_DialogTable insert into @ElementBuffer (Ident1, Ident2, Ident3) select a.ParentColumn, a.ChildTable, a.ChildColumn from
32 QBM_VQBMRelationALL a join DialogTable tc with (readpast) on a.UID_DialogTableChild = tc.UID_DialogTable join DialogColumn cc with (readpast) on tc.UID_DialogTable
33 = cc.UID_DialogTable and cc.ColumnName = 'XMarkedForDeletion' join @TSTables etp on etp.parameter1 = @ParentTable join @TSTables etc on etc.Parameter1
34 = a.ChildTable where a.ParentTable = @ParentTable and a.IsForUpdateXDateSubItem = 0 and ( tc.IsMAllTable = 1  or tc.isMNTable = 1 ) and dbo.QBM_FCVGUIDToModuleOwner
35(a.UID_DialogTableParent) = dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild)  and (exists (select top 1 1 from QBMModuleDependCollection co with (readpast
36) where left(co.UID_ModulePredecessor, 3) = 'TSB' and left(co.UID_ModuleFollower, 3) = left(dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild), 3) ) 
37 or dbo.QBM_FCVGUIDToModuleOwner(a.UID_DialogTableChild) = 'UCI' ) and tc.UsageType = 'UserData' if @@ROWCOUNT > 0  begin select @erg = @erg + string_agg
38( convert(nvarchar(max), concat('
39				update ' , bu.Ident2  , '
40						set XMarkedForDeletion = (e.XMarkedForDeletion & ' , @Pattern_nurO_inv , ')
41												 | (l.XMarkedForDeletion  & '
42 , @Pattern_nurO , ')
43												 ', case when dbo.QBM_FGIColumnExistsInSchema(bu.Ident2 , 'XDateUpdated' ) = 1 then ' , XDateUpdated = @XDate, XUserUpdated = @XUser'
44 else '' end, '
45					from ' , bu.Ident2  , ' e join deleted d on e.' , bu.Ident3  , ' = d.' , bu.Ident1 , '
46											join ' + @ParentTable + ' l on d.XObjectKey = l.XObjectKey
47						where (d.XMarkedForDeletion ^ l.XMarkedForDeletion ) & '
48 + @Pattern_nurO + ' > 0
49						' )  )  , '' )  from @ElementBuffer bu end end   if @erg > ' ' begin select @erg = '	
50	if update(XMarkedForDeletion)
51		begin
52		if exists (select top 1 1
53				from deleted d join '
54 + @ParentTable + ' l on d.XObjectKey = l.XObjectKey
55				where (d.XMarkedForDeletion ^ l.XMarkedForDeletion ) & ' + @Pattern + ' > 0
56				)
57		begin
58'
59 + @erg + '
60
61			end
62		end' end  end  endLabel: return(@erg) end 
63