Back to OIM Explorer

dbo.QER_ZAccProductGroupCollection

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.887 characters

Interpretation

Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

No extracted relations.

Typed Edges

references source dbo.QBM_FCVElementToObjectKey2 source text reference
references source dbo.QBM_FGISessionContext source text reference
references source dbo.QBM_PDBQueueCalculateDelta source text reference
references source dbo.QBM_PSessionContextSet source text reference
references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

No direct source references extracted.

Complete Source

1CREATE PROCEDURE QER_ZAccProductGroupCollection(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @Sourcedata QBM_YDataForDelta,7  @CountDeltaQantity int,8  @CountDeltaOrigin int9  DECLARE @lauf int10  DECLARE @Debugswitch int = 011  DECLARE @Debugmessage nvarchar(1000)12  DECLARE @ElementCount int13  DECLARE @ElementIndex int14  DECLARE @ElementLast int15  DECLARE @WhereClauseForJob nvarchar(max)16  DECLARE @GenProcID varchar(38)17  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')18  DECLARE @QBMDeltaHelper QBM_YDBQueueDeltaHelper19  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent20  BEGIN TRY21    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,22    UID_Parameter,23    UID_SubParameter,24    GenProcID)25    SELECT26      UID_DialogDBQueue,27      UID_Parameter,28      UID_SubParameter,29      GenProcID30    FROM QBMDBQueueCurrent cu31      WITH(readpast)32    WHERE33      cu.SlotNumber = @SlotNumber34    IF @@rowcount = 035    BEGIN36      GOTO EndLabel37    END38    INSERT INTO @SourceData(IsUpcommingContent,39    XOriginAfter,40    Element,41    AssignedElement,42    XOriginBefore)43    SELECT44      0,45      0,46      a.UID_AccProductGroupChild,47      a.UID_AccProductGroupParent,48      a.Levelnumber + 149    FROM AccProductGroupCollection a50    JOIN @DBQueueCurrent x51      ON a.UID_AccProductGroupChild = x.uid_parameter52    INSERT INTO @QBMDeltaHelper(Element,53    AssignedElement,54    xOrigin)55    SELECT56      uid_parameter,57      uid_parameter,58      159    FROM @DBQueueCurrent p60    SELECT @lauf = 161    WHILE @lauf > 062    BEGIN63      INSERT INTO @QBMDeltaHelper(Element,64      AssignedElement,65      XOrigin)66      SELECT67        DISTINCT a.element,68        ag.UID_AccProductGroupParent,69        a.XOrigin +170      FROM @QBMDeltaHelper a71      JOIN AccProductGroup ag72        ON a.AssignedElement = ag.UID_AccProductGroup73      WHERE74        ag.UID_AccProductGroupParent > ' ' AND NOT EXISTS(75      SELECT TOP 1 176      FROM @QBMDeltaHelper e77      WHERE78        e.Element = a.Element AND e.AssignedElement = ag.UID_AccProductGroupParent)79      SELECT @lauf = @@rowcount80    END81    INSERT INTO @SourceData(IsUpcommingContent,82    XOriginAfter,83    Element,84    AssignedElement,85    XOriginBefore)86    SELECT87      1,88      XOrigin,89      Element,90      AssignedElement,91      092    FROM @QBMDeltaHelper93    EXEC QBM_PDBQueueCalculateDelta @SourceData,94      @DeltaQuantity = 1,95      @DeltaDelete = 1,96      @DeltaInsert = 1,97      @DeltaOrigin = 1,98      @CountDeltaQantity = @CountDeltaQantity OUTPUT,99      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,100      @UseIsInEffect = 0,101      @SlotNumber = @SlotNumber,102      @DBQueueCurrentExtern = @DBQueueCurrent103    IF @CountDeltaQantity > 0104    BEGIN105      DECLARE @GenProcIDs_CountDeltaQuantity QBM_YCursorBuffer106      INSERT INTO @GenProcIDs_CountDeltaQuantity(UID1)107      SELECT de.GenProcID108      FROM #QBMDeltaDelete de109      UNION110      SELECT de.GenProcID111      FROM #QBMDeltaInsert de112      SELECT @ElementCount = @@ROWCOUNT113      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1114      SELECT @ElementLast = @@IDENTITY115      WHILE @ElementIndex <= @ElementLast116      BEGIN117        SELECT TOP 1 @GenProcID = bu.UID1118        FROM @GenProcIDs_CountDeltaQuantity bu119        WHERE120          bu.ElementIndex = @ElementIndex121        IF EXISTS(122          SELECT TOP 1 1123          FROM #QBMDeltaDelete x124          WHERE125            x.GenProcID = @GenProcID)126        BEGIN127          EXEC QBM_PSessionContextSet 'GenProcID',128            @GenProcID129          DELETE AccProductGroupCollection130          FROM AccProductGroupCollection co131          WHERE132            EXISTS(133          SELECT TOP 1 1134          FROM #QBMDeltaDelete d135          WHERE136            d.AssignedElement = co.UID_AccProductGroupParent AND d.Element = co.UID_AccProductGroupChild AND d.GenProcID = @GenProcID)137        END138        IF EXISTS(139          SELECT TOP 1 1140          FROM #QBMDeltaInsert x141          WHERE142            x.GenProcID = @GenProcID)143        BEGIN144          EXEC QBM_PSessionContextSet 'GenProcID',145            @GenProcID146          INSERT INTO AccProductGroupCollection(UID_AccProductGroupChild,147          UID_AccProductGroupParent,148          LevelNumber,149          XObjectKey)150          SELECT151            x.UID_AccProductGroupChild,152            x.UID_AccProductGroupParent,153            x.LevelNumber - 1,154            dbo.QBM_FCVElementToObjectKey2('AccProductGroupCollection',155            'UID_AccProductGroupChild',156            x.UID_AccProductGroupChild,157            'UID_AccProductGroupParent',158            x.UID_AccProductGroupParent)159          FROM(160          SELECT161            Element AS UID_AccProductGroupChild,162            AssignedElement AS UID_AccProductGroupParent,163            xorigin AS LevelNumber164          FROM #QBMDeltaInsert165          WHERE166            GenProcID = @GenProcID) AS x167        END168        SELECT @ElementIndex += 1169      END170    END171    IF @CountDeltaOrigin > 0172    BEGIN173      DECLARE @GenProcIDs_CountDeltaOrigin QBM_YCursorBuffer174      INSERT INTO @GenProcIDs_CountDeltaOrigin(UID1)175      SELECT176        DISTINCT de.GenProcID177      FROM #QBMDeltaOrigin de178      SELECT @ElementCount = @@ROWCOUNT179      SELECT @ElementIndex = @@IDENTITY - @ElementCount +1180      SELECT @ElementLast = @@IDENTITY181      WHILE @ElementIndex <= @ElementLast182      BEGIN183        SELECT TOP 1 @GenProcID = bu.UID1184        FROM @GenProcIDs_CountDeltaOrigin bu185        WHERE186          bu.ElementIndex = @ElementIndex187        EXEC QBM_PSessionContextSet 'GenProcID',188          @GenProcID189        UPDATE AccProductGroupCollection190        SET LevelNumber = di.XOrigin -1191        FROM AccProductGroupCollection co192        JOIN #QBMDeltaOrigin di193          ON co.UID_AccProductGroupChild = di.Element AND co.UID_AccProductGroupParent = di.AssignedElement194        WHERE195          di.GenProcID = @GenProcID196        SELECT @ElementIndex += 1197      END198    END199  END TRY200  BEGIN CATCH201    EXEC QBM_PSessionErrorAdd DEFAULT202    RAISERROR('',203    18,204    1)205      WITH NOWAIT206  END CATCH207  endLabel:208  EXEC QBM_PSessionContextSet 'GenProcID',209    @GenProcID_R210  RETURN211END

Open raw exported source

1    create   procedure QER_ZAccProductGroupCollection (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int 2, @CountDeltaOrigin int declare @lauf int declare @Debugswitch int = 0 declare @Debugmessage nvarchar(1000) declare @ElementCount int declare @ElementIndex3 int declare @ElementLast int declare @WhereClauseForJob nvarchar(max) declare @GenProcID varchar(38) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext4('') declare @QBMDeltaHelper QBM_YDBQueueDeltaHelper declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue5, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast6) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement7, XOriginBefore ) select 0, 0 , a.UID_AccProductGroupChild, a.UID_AccProductGroupParent, a.Levelnumber + 1  from AccProductGroupCollection a join @DBQueueCurrent8 x on a.UID_AccProductGroupChild = x.uid_parameter  insert into @QBMDeltaHelper (Element, AssignedElement, xOrigin) select uid_parameter, uid_parameter9, 1 from @DBQueueCurrent p  select @lauf = 1 while @lauf > 0 begin insert into @QBMDeltaHelper (Element, AssignedElement, XOrigin) select distinct a.element10, ag.UID_AccProductGroupParent, a.XOrigin +1 from @QBMDeltaHelper a join AccProductGroup ag on a.AssignedElement = ag.UID_AccProductGroup where ag.UID_AccProductGroupParent11 > ' ' and Not exists (select top 1 1 from @QBMDeltaHelper e where e.Element = a.Element and e.AssignedElement = ag.UID_AccProductGroupParent ) select 12@lauf = @@rowcount end insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore ) select 1,XOrigin , Element13, AssignedElement, 0 from @QBMDeltaHelper exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity = 1, @DeltaDelete = 1, @DeltaInsert = 1, @DeltaOrigin14 = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output , @UseIsInEffect = 0 , @SlotNumber = @SlotNumber , 15@DBQueueCurrentExtern = @DBQueueCurrent if @CountDeltaQantity > 0 begin declare @GenProcIDs_CountDeltaQuantity QBM_YCursorBuffer insert into @GenProcIDs_CountDeltaQuantity16 (UID1) select de.GenProcID from #QBMDeltaDelete de union select de.GenProcID from #QBMDeltaInsert de select @ElementCount = @@ROWCOUNT select @ElementIndex17 = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @GenProcID = bu.UID1 from @GenProcIDs_CountDeltaQuantity18 bu where bu.ElementIndex = @ElementIndex if exists (select top 1 1 from #QBMDeltaDelete x where x.GenProcID = @GenProcID ) begin exec QBM_PSessionContextSet19 'GenProcID', @GenProcID delete AccProductGroupCollection from AccProductGroupCollection co where exists (select top 1 1 from #QBMDeltaDelete d where d.AssignedElement20 = co.UID_AccProductGroupParent and d.Element = co.UID_AccProductGroupChild and d.GenProcID = @GenProcID ) end  if exists (select top 1 1 from #QBMDeltaInsert21 x where x.GenProcID = @GenProcID ) begin exec QBM_PSessionContextSet 'GenProcID', @GenProcID insert into AccProductGroupCollection ( UID_AccProductGroupChild22, UID_AccProductGroupParent, LevelNumber, XObjectKey ) select x.UID_AccProductGroupChild, x.UID_AccProductGroupParent, x.LevelNumber - 1 ,dbo.QBM_FCVElementToObjectKey223('AccProductGroupCollection', 'UID_AccProductGroupChild', x.UID_AccProductGroupChild, 'UID_AccProductGroupParent', x.UID_AccProductGroupParent) from ( 24select Element as UID_AccProductGroupChild, AssignedElement as UID_AccProductGroupParent, xorigin as LevelNumber from #QBMDeltaInsert where GenProcID =25 @GenProcID ) as x end  select @ElementIndex += 1 end  end  if @CountDeltaOrigin > 0 begin declare @GenProcIDs_CountDeltaOrigin QBM_YCursorBuffer insert26 into @GenProcIDs_CountDeltaOrigin (UID1) select distinct de.GenProcID from #QBMDeltaOrigin de select @ElementCount = @@ROWCOUNT select @ElementIndex =27 @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @GenProcID = bu.UID1 from @GenProcIDs_CountDeltaOrigin28 bu where bu.ElementIndex = @ElementIndex exec QBM_PSessionContextSet 'GenProcID', @GenProcID update AccProductGroupCollection set LevelNumber = di.XOrigin29 -1 from AccProductGroupCollection co join #QBMDeltaOrigin di on co.UID_AccProductGroupChild = di.Element and co.UID_AccProductGroupParent = di.AssignedElement30 where di.GenProcID = @GenProcID select @ElementIndex += 1 end  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT31 END CATCH  endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R return end 32