Back to OIM Explorer

dbo.QER_ZHelperHeadOrg

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.996 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_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_PDBQueueCalculateDelta source text reference
  • references source dbo.QBM_PMNTableInsert source text reference
  • references source dbo.QBM_PMNTableOriginUpdate source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL236 lines
1CREATE PROCEDURE QER_ZHelperHeadOrg(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @Sourcedata QBM_YDataForDelta,7  @CountDeltaQantity int,8  @CountDeltaOrigin int9  DECLARE @DebugSwitch int = 010  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent11  DECLARE @QBM_BitPatternXOrigin_Direct int = dbo.QBM_FGIBitPatternXOrigin('|Direct|',12  0)13  DECLARE @QBM_BitPatternXOrigin_PWO int = dbo.QBM_FGIBitPatternXOrigin('|PWO|',14  0)15  DECLARE @QBM_BitPatternXOrigin_Dynamic int = dbo.QBM_FGIBitPatternXOrigin('|Dynamic|',16  0)17  BEGIN TRY18    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,19    UID_Parameter,20    UID_SubParameter,21    GenProcID)22    SELECT23      UID_DialogDBQueue,24      UID_Parameter,25      UID_SubParameter,26      GenProcID27    FROM QBMDBQueueCurrent cu28      WITH(readpast)29    WHERE30      cu.SlotNumber = @SlotNumber31    IF @@rowcount = 032    BEGIN33      GOTO EndLabel34    END35    INSERT INTO @SourceData(IsUpcommingContent,36    XOriginAfter,37    Element,38    AssignedElement,39    XOriginBefore)40    SELECT41      0,42      0,43      hho.UID_PersonHead,44      hho.UID_Org,45      hho.XOrigin46    FROM @DBQueueCurrent x47    JOIN HelperHeadOrg hho48      ON hho.uid_personHead = x.uid_parameter49    IF @DebugSwitch > 050    BEGIN51      print 'vorher'52      SELECT *53      FROM @SourceData54    END55    INSERT INTO @SourceData(IsUpcommingContent,56    XOriginBefore,57    XOriginAfter,58    Element,59    AssignedElement)60    SELECT61      1,62      0,63      hho.XOrigin & @QBM_BitPatternXOrigin_PWO,64      hho.uid_personHead,65      hho.uid_Org66    FROM @DBQueueCurrent x67    JOIN HelperHeadOrg hho68      ON hho.uid_personHead = x.UID_Parameter AND EXISTS(69    SELECT TOP 1 170    FROM personwantsorg71    WHERE72      ObjectKeyAssignment = hho.XObjectkey)73    IF @DebugSwitch > 074    BEGIN75      print 'nachher Delegierungen'76      SELECT *77      FROM @SourceData78    END79    INSERT INTO @SourceData(IsUpcommingContent,80    XOriginBefore,81    XOriginAfter,82    Element,83    AssignedElement)84    SELECT85      1,86      0,87      @QBM_BitPatternXOrigin_Direct,88      b.uid_personHead,89      b.uid_Org90    FROM @DBQueueCurrent x91    JOIN BaseTree b92      ON x.uid_parameter = b.uid_PersonHead93    JOIN Person p94      ON x.UID_Parameter = p.UID_Person95    IF @DebugSwitch > 096    BEGIN97      print 'nachher direkt uid_personHead'98      SELECT *99      FROM @SourceData100    END101    INSERT INTO @SourceData(IsUpcommingContent,102    XOriginBefore,103    XOriginAfter,104    Element,105    AssignedElement)106    SELECT107      1,108      0,109      @QBM_BitPatternXOrigin_Direct,110      b.uid_personHeadSecond,111      b.uid_Org112    FROM @DBQueueCurrent x113    JOIN BaseTree b114      ON x.uid_parameter = b.uid_PersonHeadSecond115    JOIN Person p116      ON x.UID_Parameter = p.UID_Person117    IF @DebugSwitch > 0118    BEGIN119      print 'nachher direkt uid_personHeadSecond'120      SELECT *121      FROM @SourceData122    END123    INSERT INTO @SourceData(IsUpcommingContent,124    XOriginBefore,125    XOriginAfter,126    Element,127    AssignedElement)128    SELECT129      1,130      0,131      pio.XOrigin,132      x.UID_Parameter AS UID_Person,133      b.uid_Org134    FROM @DBQueueCurrent x135    JOIN PersonInBaseTree pio136      ON x.uid_parameter = pio.UID_Person137    JOIN BaseTree b138      ON b.UID_AERoleManager = pio.UID_Org139    IF @DebugSwitch > 0140    BEGIN141      print 'nachher mit UID_AERoleManager'142      SELECT *143      FROM @SourceData144    END145    INSERT INTO @SourceData(IsUpcommingContent,146    XOriginBefore,147    XOriginAfter,148    Element,149    AssignedElement)150    SELECT151      1,152      0,153      @QBM_BitPatternXOrigin_Dynamic,154      us.UID_PersonReceiver AS uid_personHead,155      b.UID_Org156    FROM @DBQueueCurrent x157    JOIN QERUniversalSubstitute us158      ON x.UID_Parameter = us.UID_PersonReceiver159    JOIN BaseTree b160      ON us.UID_PersonSender IN(b.UID_PersonHead,161    b.UID_PersonHeadSecond)162    JOIN QERUniversalSubstituteInRoot usr163      ON usr.UID_QERUniversalSubstitute = us.UID_QERUniversalSubstitute AND b.UID_OrgRoot = usr.UID_OrgRoot164    WHERE165      us.IsCurrentlyActive = 1 AND us.UseForHeadOrg = 1166    INSERT INTO @SourceData(IsUpcommingContent,167    XOriginBefore,168    XOriginAfter,169    Element,170    AssignedElement)171    SELECT172      1,173      0,174      @QBM_BitPatternXOrigin_Direct,175      x.UID_Parameter AS UID_Person,176      b.uid_Org177    FROM @DBQueueCurrent x178    JOIN QERUniversalSubstitute us179      ON x.UID_Parameter = us.UID_PersonReceiver180    JOIN PersonInBaseTree pio181      ON us.UID_PersonSender = pio.UID_Person182    JOIN BaseTree b183      ON b.UID_AERoleManager = pio.UID_Org184    JOIN QERUniversalSubstituteInRoot usr185      ON usr.UID_QERUniversalSubstitute = us.UID_QERUniversalSubstitute AND usr.UID_OrgRoot = 'QER-V-AERole'186    WHERE187      us.IsCurrentlyActive = 1 AND us.UseForHeadOrg = 1188    IF @DebugSwitch > 0189    BEGIN190      print 'nachher mit QERUniversalSubstitute'191      SELECT *192      FROM @SourceData193    END194    EXEC QBM_PDBQueueCalculateDelta @SourceData,195      @DeltaQuantity = 0,196      @DeltaDelete = 0,197      @DeltaInsert = 1,198      @DeltaOrigin = 1,199      @CountDeltaQantity = @CountDeltaQantity OUTPUT,200      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,201      @UseIsInEffect = 0,202      @SlotNumber = @SlotNumber,203      @DBQueueCurrentExtern = @DBQueueCurrent204    IF @DebugSwitch > 0205    BEGIN206      print 'nach Berechnung' print '@CountDeltaQantity' + str(@CountDeltaQantity) print '@CountDeltaOrigin' + str(@CountDeltaOrigin)207    END208    IF @CountDeltaOrigin > 0209    BEGIN210      EXEC QBM_PMNTableOriginUpdate 'HelperHeadOrg',211      'uid_personHead',212      'UID_Org'213    END214    IF @CountDeltaQantity > 0215    BEGIN216      IF @DebugSwitch > 0217      BEGIN218        print 'delta quantity gefunden'219        SELECT *220        FROM @SourceData221      END222      EXEC QBM_PMNTableInsert 'HelperHeadOrg',223      'uid_personHead',224      'UID_Org'225    END226  END TRY227  BEGIN CATCH228    EXEC QBM_PSessionErrorAdd DEFAULT229    RAISERROR('',230    18,231    1)232      WITH NOWAIT233  END CATCH234  endLabel:235  RETURN236END
Open raw exported source
SQL ยท Raw33 lines
1       create   procedure QER_ZHelperHeadOrg (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int , @CountDeltaOrigin2 int declare @DebugSwitch int = 0 declare @DBQueueCurrent QBM_YDBQueueCurrent declare @QBM_BitPatternXOrigin_Direct int = dbo.QBM_FGIBitPatternXOrigin(3'|Direct|', 0) declare @QBM_BitPatternXOrigin_PWO int = dbo.QBM_FGIBitPatternXOrigin('|PWO|', 0) declare @QBM_BitPatternXOrigin_Dynamic int = dbo.QBM_FGIBitPatternXOrigin4('|Dynamic|', 0)  BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter5, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end  insert6 into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore ) select 0, 0 , hho.UID_PersonHead, hho.UID_Org, hho.XOrigin7 from @DBQueueCurrent x join HelperHeadOrg hho on hho.uid_personHead = x.uid_parameter  if @DebugSwitch > 0 begin print 'vorher' select * from @SourceData8 end insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement ) select 1, 0, hho.XOrigin & @QBM_BitPatternXOrigin_PWO9 , hho.uid_personHead, hho.uid_Org from @DBQueueCurrent x join HelperHeadOrg hho on hho.uid_personHead = x.UID_Parameter    and exists (select top 1 1 10from personwantsorg where ObjectKeyAssignment = hho.XObjectkey) if @DebugSwitch > 0 begin print 'nachher Delegierungen' select * from @SourceData end insert11 into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement ) select 1, 0, @QBM_BitPatternXOrigin_Direct , b.uid_personHead12, b.uid_Org from @DBQueueCurrent x join BaseTree b on x.uid_parameter = b.uid_PersonHead  join Person p on x.UID_Parameter = p.UID_Person if @DebugSwitch13 > 0 begin print 'nachher direkt uid_personHead' select * from @SourceData end insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter14 , Element, AssignedElement ) select 1, 0, @QBM_BitPatternXOrigin_Direct , b.uid_personHeadSecond, b.uid_Org from @DBQueueCurrent x join BaseTree b on 15x.uid_parameter = b.uid_PersonHeadSecond  join Person p on x.UID_Parameter = p.UID_Person if @DebugSwitch > 0 begin print 'nachher direkt uid_personHeadSecond'16 select * from @SourceData end insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement ) select 1, 0, pio.XOrigin17 , x.UID_Parameter as UID_Person, b.uid_Org from @DBQueueCurrent x join PersonInBaseTree pio on x.uid_parameter = pio.UID_Person join BaseTree b on b.UID_AERoleManager18 = pio.UID_Org if @DebugSwitch > 0 begin print 'nachher mit UID_AERoleManager' select * from @SourceData end insert into @SourceData( IsUpcommingContent19, XOriginBefore, XOriginAfter , Element, AssignedElement ) select 1, 0, @QBM_BitPatternXOrigin_Dynamic , us.UID_PersonReceiver as uid_personHead, b.UID_Org20 from @DBQueueCurrent x join QERUniversalSubstitute us on x.UID_Parameter = us.UID_PersonReceiver join BaseTree b on us.UID_PersonSender in (b.UID_PersonHead21, b.UID_PersonHeadSecond) join QERUniversalSubstituteInRoot usr on usr.UID_QERUniversalSubstitute = us.UID_QERUniversalSubstitute and b.UID_OrgRoot = usr.UID_OrgRoot22 where us.IsCurrentlyActive = 1 and us.UseForHeadOrg = 1 insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement23 ) select 1, 0, @QBM_BitPatternXOrigin_Direct , x.UID_Parameter as UID_Person, b.uid_Org from @DBQueueCurrent x join QERUniversalSubstitute us on x.UID_Parameter24 = us.UID_PersonReceiver join PersonInBaseTree pio on us.UID_PersonSender = pio.UID_Person join BaseTree b on b.UID_AERoleManager = pio.UID_Org join QERUniversalSubstituteInRoot25 usr on usr.UID_QERUniversalSubstitute = us.UID_QERUniversalSubstitute and usr.UID_OrgRoot = 'QER-V-AERole' where us.IsCurrentlyActive = 1 and us.UseForHeadOrg26 = 1 if @DebugSwitch > 0 begin print 'nachher mit QERUniversalSubstitute' select * from @SourceData end exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity27 = 0, @DeltaDelete = 0, @DeltaInsert = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output28 , @UseIsInEffect = 0 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent if @DebugSwitch > 0 begin print 'nach Berechnung' print '@CountDeltaQantity'29 + str(@CountDeltaQantity) print '@CountDeltaOrigin' + str(@CountDeltaOrigin) end if @CountDeltaOrigin > 0 begin exec QBM_PMNTableOriginUpdate 'HelperHeadOrg'30, 'uid_personHead', 'UID_Org' end if @CountDeltaQantity > 0 begin if @DebugSwitch > 0 begin print 'delta quantity gefunden' select * from @SourceData end31 exec QBM_PMNTableInsert 'HelperHeadOrg', 'uid_personHead', 'UID_Org'   end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 132) WITH NOWAIT END CATCH  endLabel: return end 33