dbo.QER_POrgSubTree

1   create   procedure QER_POrgSubTree ( @uid_org varchar(38), @Dummy varchar(38) , @GenProcID varchar(38) ) AS begin declare @CountItems int declare
2 @uid_orgRoot varchar(38) declare @AnzahlInRunde int declare @msg nvarchar(1024) declare @DebugLevel char(1) = 'W' declare @QBMDeltaHelper QBM_YDBQueueDeltaHelper
3 declare @WorkInsert QBM_YDBQueueDeltaHelper declare @WorkDelete QBM_YDBQueueDeltaHelper declare @WorkQuantity QBM_YDBQueueDeltaHelper SET XACT_ABORT OFF
4 BEGIN TRY if not exists (select top 1 1 from BaseTree where uid_org = isnull(@uid_org,'') ) begin select @msg = 'BaseTree ' + rtrim(@uid_org) + ' not exists, Job ORGSUBTREE was killed'
5 exec QBM_PJournal @msg, @@procid, 'D', @DebugLevel goto ganzende  end select @uid_orgroot = uid_orgroot from BaseTree where uid_org = @uid_org   insert
6 into @WorkInsert (AssignedElement, Element) select uid_parentorg, uid_org from BaseTreeCollection where uid_org = @uid_org union select @uid_org, @uid_org
7 select @AnzahlInRunde = 1 while @AnzahlInRunde > 0 begin insert into @WorkInsert (AssignedElement, Element) select uid_parentorg, uid_org from ( select
8 oc.uid_parentorg, oc.uid_org from BaseTreeCollection oc join BaseTree o on oc.uid_org = o.uid_org  where o.uid_parentorg in (select v.Element from @WorkInsert
9 v ) union  select uid_parentorg, uid_org from BaseTree where uid_parentorg in (select v.Element from @WorkInsert v ) union select uid_org, uid_org from
10 BaseTree where uid_parentorg in (select v.Element from @WorkInsert v ) ) as y  where not exists (select top 1 1 from @WorkInsert x where x.AssignedElement
11 = y.uid_parentorg and x.Element = y.uid_org ) select @AnzahlInRunde = @@rowcount end  insert into @QBMDeltaHelper (Element, AssignedElement, XOrigin) 
12select distinct v.Element, v.Element , 2 from @WorkInsert v  select @AnzahlInRunde = 1 while @AnzahlInRunde > 0 begin insert into @QBMDeltaHelper (Element
13, AssignedElement, XOrigin) select distinct y.Element, x.UId_parentorg , 2  from BaseTree x, @QBMDeltaHelper y where x.uid_orgroot = @uid_orgroot and x.uid_org
14 = y.AssignedElement and x.uid_parentorg > ' '  and not exists (select top 1 1 from @QBMDeltaHelper af where af.Element = y.Element and af.AssignedElement
15 = x.uid_parentorg )  and x.isCutNode = 0 select @AnzahlInRunde = @@rowcount end    insert into @WorkQuantity (Element) select distinct Element from @WorkInsert
16 delete  @WorkInsert from @WorkInsert v where not exists (select top 1 1 from BaseTreeCollection x where x.uid_org = v.Element and x.uid_parentorg = v.AssignedElement
17 )  insert into @WorkDelete (Element, AssignedElement) select x.Element, x.AssignedElement from @WorkInsert x  where not exists (select top 1 1 from @QBMDeltaHelper
18 af  where af.Element = x.Element and af.AssignedElement = x.AssignedElement ) union select x.Element, x.AssignedElement from @QBMDeltaHelper x  where 
19not exists (select top 1 1 from @WorkInsert v  where v.Element = x.Element and v.AssignedElement = x.AssignedElement ) select @CountItems = @@rowcount 
20  if @CountItems > 0 begin   delete from BaseTreeCollection where uid_org in (select Element from @WorkQuantity) insert into BaseTreeCollection (uid_org
21, uid_parentorg)  select af.Element, af.AssignedElement from @QBMDeltaHelper af declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01
22 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select distinct Element as uid from @WorkDelete ) as x exec QBM_PDBQueueInsert_Bulk
23 'QER-K-AllForOneOrg', @DBQueueElements_01 end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow
24() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ganzende: end 
25