dbo.QER_PITShopProductNodeCheck

1         create   procedure QER_PITShopProductNodeCheck(@UID_OrgBO varchar(38) , @GenProcID varchar(38) )  AS begin declare @UID_OrgPR varchar(38
2) declare @startat datetime declare @BasisObjectKey varchar(138) declare @SQLcmd nvarchar(max) declare @DebugSwitch int = 0 declare @MerkTable1 QBM_YSingleGUID
3 declare @MerkTable2 QBM_YSingleGUID  declare @MerkTableNoAssign QBM_YCursorBuffer  declare @MerkTable3 QBM_YParameterList  declare @StringPatternInherit
4 varchar(16) declare @StringPatternInheritInv varchar(16) declare @DebugLevel char(1) = 'W' declare @RowLimitNodeCheck int declare @RowCounterNodeCheck
5 int declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare
6 @PWOsToMove QBM_YParameterList     declare @NodeCreateBuffer QBM_YParameterList declare @WirMachenDasNochEinmal bit = 0 declare @EntriesToFire QBM_YParameterList
7 BEGIN TRY select @StringPatternInherit = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Inherit|',0)), 0) select @StringPatternInheritInv
8 = dbo.QBM_FCVBinaryToString( CONVERT(varbinary, dbo.QBM_FGIBitPatternXOrigin('|Inherit|',1)), 0) select @RowCounterNodeCheck = 0 select @RowLimitNodeCheck
9 = dbo.QBM_FCVStringToInt(dbo.QBM_FGIConfigparmValue('QER\ITShop\LimitOfNodeCheck'), 500)  if not exists (select top 1 1 from BaseTree where uid_org = 
10isnull(@UID_OrgBO,'')) begin select @SQLcmd = N'BaseTree ' + rtrim(@UID_OrgBO) + ' not exists, Job ORGAUTOCHILD was killed' if @DebugSwitch > 0 begin print
11 @SQLCmd end exec QBM_Pjournal @SQLcmd, @@procid, 'D', @DebugLevel goto endLabel  end   select @SQLCmd = string_agg( concat( convert(nvarchar(max), '')
12, '
13	 select b.uid_org 
14				from BaseTree b join ' , t.TableName , ' bha on b.UID_ParentOrg = ''' , @UID_OrgBO , '''
15													and b.uid_org = bha.uid_org
16			where bha.XOrigin & '
17 , @StringPatternInheritInv , ' > 0 
18			 and bha.XIsInEffect = 1
19		' )  , '
20			union all
21		' )   from BaseTreeAssign a join DialogTable t with (readpast
22) on a.UID_DialogTableMN = t.UID_DialogTable where a.IsITShopEnabled = 1 if @DebugSwitch > 0 begin print @SQLCmd end insert into @MerkTable1(UID_SingleGuid
23 ) exec sp_executesql @SQLCmd if @DebugSwitch > 0 begin select * from @MerkTable1 end   select @SQLcmd = string_agg( concat( convert(nvarchar(max), '')
24, '
25	   select c.uid_org
26	     from BaseTree o join ' , t.TableName , ' c on o.uid_org = c.uid_org
27											and o.uid_parentorg = ''' , @UID_OrgBO
28 , '''
29		where not exists (select top 1 1 
30							from ' , t.TableName , ' p 
31							where p.uid_org = ''' , @UID_OrgBO , '''
32							and p.' , c.ColumnName
33 , ' = c.' , c.ColumnName , '
34							and p.XOrigin & ' , @StringPatternInheritInv , ' > 0 
35						)
36			and c.XOrigin & ' , @StringPatternInheritInv ,
37 ' > 0 
38			and c.XIsInEffect = 1
39			' )  , '
40			union all
41		' )   from BaseTreeAssign a join DialogTable t with (readpast) on a.UID_DialogTableMN =
42 t.UID_DialogTable join DialogColumn c with (readpast) on t.UID_DialogTable = c.UID_DialogTable and c.IsPKMember = 1 and c.ColumnName <> 'UID_Org'  where
43 a.IsITShopEnabled = 1 if @DebugSwitch > 0 begin print @SQLCmd end insert into @MerkTable2(UID_SingleGuid ) exec sp_executesql @SQLCmd if @DebugSwitch 
44> 0 begin select * from @MerkTable2 end   insert into @MerkTableNoAssign (UID1 ) select distinct b.UID_Org from BaseTree b where b.UID_ParentOrg = @UID_OrgBO
45   select @SQLcmd = string_agg( concat( convert(nvarchar(max), ''), '
46	 select b.uid_org 
47				from BaseTree b join ' , t.TableName , ' bha on b.UID_ParentOrg = '''
48 , @UID_OrgBO , '''
49													and b.uid_org = bha.uid_org
50			where bha.XOrigin & ' , @StringPatternInheritInv , ' > 0 
51			and bha.XIsInEffect = 1
52		'
53 )  , '
54			union all
55		' )   from BaseTreeAssign a join DialogTable t with (readpast) on a.UID_DialogTableMN = t.UID_DialogTable where a.IsITShopEnabled
56 = 1 if @DebugSwitch > 0 begin print @SQLCmd end insert into @MerkTableNoAssign(UID1 ) exec sp_executesql @SQLCmd if @DebugSwitch > 0 begin select UID1
57 from @MerkTableNoAssign end  exec QBM_PSessionContextSet 'GenProcID', @GenProcID   declare @ElementToDelte QBM_YCursorBuffer insert into @ElementToDelte
58 (UID1) select m.UID_SingleGuid  from @MerkTable1 m group by m.UID_SingleGuid  having COUNT(*) > 1 union select m.UID_SingleGuid  from @MerkTable2 m union
59 select m.UID1  from @MerkTableNoAssign m group by m.UID1  having COUNT(*) = 1  select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount
60 +1 select @ElementLast = @@IDENTITY if @DebugSwitch > 0 begin print str(@ElementCount) + ' zu vernichtende Sachen' end while @ElementIndex <= @ElementLast
61 and @RowCounterNodeCheck < @RowLimitNodeCheck and @WirMachenDasNochEinmal = 0 begin select top 1 @UID_OrgPR = bu.UID1 from @ElementToDelte bu where bu.ElementIndex
62 = @ElementIndex     select @SQLcmd = string_agg( concat(convert(nvarchar(max), ''), '
63				update ' , t.TableName , ' set XOrigin = 0
64						where uid_org = '''
65 , @UID_OrgPR , '''
66			   delete ' , t.TableName , ' 
67			   where uid_org = ''' , @UID_OrgPR , '''' )  , nchar(13) + nchar(10) )  from BaseTreeAssign
68 a join DialogTable t with (readpast) on a.UID_DialogTableMN = t.UID_DialogTable where a.IsITShopEnabled = 1 if @DebugSwitch > 0 begin print @SQLCmd end
69 exec sp_executesql @SQLCmd    delete @PWOsToMove insert into @PWOsToMove (Parameter1, Parameter2, Parameter3, HasContentFull) select pwo.UID_PersonWantsOrg
70, null, @GenProcID, 0 from Personwantsorg pwo where uid_org = @UID_OrgPR exec QER_PITShopOrderMove_bulk @PWOsToMove if @ElementCount > 1  and ( select 
71count(*) from @PWOsToMove ) > 500 begin select @WirMachenDasNochEinmal = 1 end   delete @EntriesToFire insert into @EntriesToFire (Parameter1) select dbo.QBM_FCVElementToObjectKey1
72('ITShopOrg', 'UID_ITShopOrg', @UID_OrgPR) exec QBM_PJobCreate_HOFireEvent_L 'ITShopOrg', @EntriesToFire ,'DeletePRNode', @GenProcID , @CheckForExisting
73 = 1 , @AdditionalObjectKeysAffected = DEFAULT select @RowCounterNodeCheck += 1 select @ElementIndex += 1 end      select @SQLCmd = string_agg( concat(
74 convert(nvarchar(max), ''), '
75	  select zz.XObjectKey
76	   from  ' , t.TableName , ' h join ' , prod.TableName , ' zz on h.' , c.ColumnName , ' = zz.'
77 , c.ColumnName , '
78		where h.uid_org = ''' , @UID_OrgBO , '''
79			and not exists (select top 1 1 from 
80								BaseTree o join ' , t.TableName , ' c on o.uid_org = c.uid_org