Back to OIM Explorer

dbo.QER_POrgSubTree

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QER-K-AllForOneOrg / QER_ZAllForOneOrg at line 22; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 3.724 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> QER-K-AllForOneOrg / QER_ZAllForOneOrg at line 22
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QER_ZAllForOneOrg at line 22 Bulk DBQueue insert -> QER-K-AllForOneOrg / QER_ZAllForOneOrg at line 22
  • references source dbo.QBM_FGISessionErrorRethrow source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PJournal source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task QER-K-AllForOneOrg -> QER_ZAllForOneOrg QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneOrg', @DBQueueElements_01 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow () RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATC…

References

Referenced By

Complete Source

SQL197 lines
1CREATE PROCEDURE QER_POrgSubTree(2  @uid_org varchar(38),3  @Dummy varchar(38),4  @GenProcID varchar(38)5)6AS7BEGIN8  DECLARE @CountItems int9  DECLARE @uid_orgRoot varchar(38)10  DECLARE @AnzahlInRunde int11  DECLARE @msg nvarchar(1024)12  DECLARE @DebugLevel char(1) = 'W'13  DECLARE @QBMDeltaHelper QBM_YDBQueueDeltaHelper14  DECLARE @WorkInsert QBM_YDBQueueDeltaHelper15  DECLARE @WorkDelete QBM_YDBQueueDeltaHelper16  DECLARE @WorkQuantity QBM_YDBQueueDeltaHelper17  SET XACT_ABORT OFF18  BEGIN TRY19    IF NOT EXISTS(20      SELECT TOP 1 121      FROM BaseTree22      WHERE23        uid_org = isnull(@uid_org, ''))24    BEGIN25      SELECT26        @msg = 'BaseTree ' + rtrim(@uid_org) + ' not exists, Job ORGSUBTREE was killed'27      EXEC QBM_PJournal @msg,28        @@procid,29      'D',30        @DebugLevel31      GOTO ganzende32    END33    SELECT @uid_orgroot = uid_orgroot34    FROM BaseTree35    WHERE36      uid_org = @uid_org37    INSERT INTO @WorkInsert(AssignedElement,38    Element)39    SELECT40      uid_parentorg,41      uid_org42    FROM BaseTreeCollection43    WHERE44      uid_org = @uid_org45    UNION46    SELECT47      @uid_org,48      @uid_org49    SELECT @AnzahlInRunde = 150    WHILE @AnzahlInRunde > 051    BEGIN52      INSERT INTO @WorkInsert(AssignedElement,53      Element)54      SELECT55        uid_parentorg,56        uid_org57      FROM(58      SELECT59        oc.uid_parentorg,60        oc.uid_org61      FROM BaseTreeCollection oc62      JOIN BaseTree o63        ON oc.uid_org = o.uid_org64      WHERE65        o.uid_parentorg IN(66      SELECT v.Element67      FROM @WorkInsert v)68      UNION69      SELECT70        uid_parentorg,71        uid_org72      FROM BaseTree73      WHERE74        uid_parentorg IN(75      SELECT v.Element76      FROM @WorkInsert v)77      UNION78      SELECT79        uid_org,80        uid_org81      FROM BaseTree82      WHERE83        uid_parentorg IN(84      SELECT v.Element85      FROM @WorkInsert v)) AS y86      WHERE87        NOT EXISTS(88      SELECT TOP 1 189      FROM @WorkInsert x90      WHERE91        x.AssignedElement = y.uid_parentorg AND x.Element = y.uid_org)92      SELECT @AnzahlInRunde = @@rowcount93    END94    INSERT INTO @QBMDeltaHelper(Element,95    AssignedElement,96    XOrigin)97    SELECT98      DISTINCT v.Element,99      v.Element,100      2101    FROM @WorkInsert v102    SELECT @AnzahlInRunde = 1103    WHILE @AnzahlInRunde > 0104    BEGIN105      INSERT INTO @QBMDeltaHelper(Element,106      AssignedElement,107      XOrigin)108      SELECT109        DISTINCT y.Element,110        x.UId_parentorg,111        2112      FROM BaseTree x,113      @QBMDeltaHelper y114      WHERE115        x.uid_orgroot = @uid_orgroot AND x.uid_org = y.AssignedElement AND x.uid_parentorg > ' ' AND NOT EXISTS(116      SELECT TOP 1 1117      FROM @QBMDeltaHelper af118      WHERE119        af.Element = y.Element AND af.AssignedElement = x.uid_parentorg) AND x.isCutNode = 0120      SELECT @AnzahlInRunde = @@rowcount121    END122    INSERT INTO @WorkQuantity(Element)123    SELECT124      DISTINCT Element125    FROM @WorkInsert126    DELETE @WorkInsert127    FROM @WorkInsert v128    WHERE129      NOT EXISTS(130    SELECT TOP 1 1131    FROM BaseTreeCollection x132    WHERE133      x.uid_org = v.Element AND x.uid_parentorg = v.AssignedElement)134    INSERT INTO @WorkDelete(Element,135    AssignedElement)136    SELECT137      x.Element,138      x.AssignedElement139    FROM @WorkInsert x140    WHERE141      NOT EXISTS(142    SELECT TOP 1 1143    FROM @QBMDeltaHelper af144    WHERE145      af.Element = x.Element AND af.AssignedElement = x.AssignedElement)146    UNION147    SELECT148      x.Element,149      x.AssignedElement150    FROM @QBMDeltaHelper x151    WHERE152      NOT EXISTS(153    SELECT TOP 1 1154    FROM @WorkInsert v155    WHERE156      v.Element = x.Element AND v.AssignedElement = x.AssignedElement)157    SELECT @CountItems = @@rowcount158    IF @CountItems > 0159    BEGIN160      DELETE161      FROM BaseTreeCollection162      WHERE163        uid_org IN(164      SELECT Element165      FROM @WorkQuantity)166      INSERT INTO BaseTreeCollection(uid_org,167      uid_parentorg)168      SELECT169        af.Element,170        af.AssignedElement171      FROM @QBMDeltaHelper af172      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw173      INSERT INTO @DBQueueElements_01(object,174      subobject,175      genprocid)176      SELECT177        x.uid,178        NULL,179        @GenProcID180      FROM(181      SELECT182        DISTINCT Element AS uid183      FROM @WorkDelete) AS x184      EXEC QBM_PDBQueueInsert_Bulk 'QER-K-AllForOneOrg',185        @DBQueueElements_01186    END187  END TRY188  BEGIN CATCH189    EXEC QBM_PSessionErrorAdd DEFAULT190    DECLARE @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow()191    RAISERROR(@Rethrow,192    18,193    1)194      WITH NOWAIT195  END CATCH196  ganzende:197END
Open raw exported source
SQL · Raw25 lines
1   create   procedure QER_POrgSubTree ( @uid_org varchar(38), @Dummy varchar(38) , @GenProcID varchar(38) ) AS begin declare @CountItems int declare2 @uid_orgRoot varchar(38) declare @AnzahlInRunde int declare @msg nvarchar(1024) declare @DebugLevel char(1) = 'W' declare @QBMDeltaHelper QBM_YDBQueueDeltaHelper3 declare @WorkInsert QBM_YDBQueueDeltaHelper declare @WorkDelete QBM_YDBQueueDeltaHelper declare @WorkQuantity QBM_YDBQueueDeltaHelper SET XACT_ABORT OFF4 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   insert6 into @WorkInsert (AssignedElement, Element) select uid_parentorg, uid_org from BaseTreeCollection where uid_org = @uid_org union select @uid_org, @uid_org7 select @AnzahlInRunde = 1 while @AnzahlInRunde > 0 begin insert into @WorkInsert (AssignedElement, Element) select uid_parentorg, uid_org from ( select8 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 @WorkInsert9 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 from10 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.AssignedElement11 = 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 (Element13, AssignedElement, XOrigin) select distinct y.Element, x.UId_parentorg , 2  from BaseTree x, @QBMDeltaHelper y where x.uid_orgroot = @uid_orgroot and x.uid_org14 = y.AssignedElement and x.uid_parentorg > ' '  and not exists (select top 1 1 from @QBMDeltaHelper af where af.Element = y.Element and af.AssignedElement15 = x.uid_parentorg )  and x.isCutNode = 0 select @AnzahlInRunde = @@rowcount end    insert into @WorkQuantity (Element) select distinct Element from @WorkInsert16 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.AssignedElement17 )  insert into @WorkDelete (Element, AssignedElement) select x.Element, x.AssignedElement from @WorkInsert x  where not exists (select top 1 1 from @QBMDeltaHelper18 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_org21, uid_parentorg)  select af.Element, af.AssignedElement from @QBMDeltaHelper af declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_0122 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select distinct Element as uid from @WorkDelete ) as x exec QBM_PDBQueueInsert_Bulk23 'QER-K-AllForOneOrg', @DBQueueElements_01 end   END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default declare @Rethrow varchar(1000) = dbo.QBM_FGISessionErrorRethrow24() RAISERROR (@Rethrow, 18, 1) WITH NOWAIT END CATCH ganzende: end 25