Back to OIM Explorer

dbo.QER_ZITShopOrderReset

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> QER-K-ShoppingRackPWOHelperPWO / QER_ZITShopHelperFill at line 33; Bulk DBQueue insert -> QER-K-ShoppingRackPersonHasObject / QER_ZITShopPersonHasObjectFill at line 33; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 5.366 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-ShoppingRackPWOHelperPWO / QER_ZITShopHelperFill at line 33
  • Bulk DBQueue insert -> QER-K-ShoppingRackPersonHasObject / QER_ZITShopPersonHasObjectFill at line 33
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task QER_ZITShopHelperFill at line 33 Bulk DBQueue insert -> QER-K-ShoppingRackPWOHelperPWO / QER_ZITShopHelperFill at line 33
  • queues DBQueue task QER_ZITShopPersonHasObjectFill at line 33 Bulk DBQueue insert -> QER-K-ShoppingRackPersonHasObject / QER_ZITShopPersonHasObjectFill at line 33
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVStringTrimLDSPrefix source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGISessionContext source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionContextSet source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PGIQERWorkingMethod source text reference
  • references source dbo.QER_PITShopHelperFill source text reference
  • references source dbo.QER_PITShopHelperFill_bulk source text reference
  • references source dbo.QER_PITShopPersonHasObjFill_b source text reference
  • queues DBQueue task QER-K-ShoppingRackPWOHelperPWO -> QER_ZITShopHelperFill QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPWOHelperPWO', @DBQueueElements_01 exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPersonHasObject' , @DBQueueElements_01 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1…
  • queues DBQueue task QER-K-ShoppingRackPersonHasObject -> QER_ZITShopPersonHasObjectFill QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPersonHasObject' , @DBQueueElements_01 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProc…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL231 lines
1CREATE PROCEDURE QER_ZITShopOrderReset(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @uid_personwantsorg varchar(38)7  DECLARE @UID_PWODecisionHistory varchar(38)8  DECLARE @GenProcID varchar(38)9  DECLARE @cond nvarchar(1000)10  DECLARE @BasisObjectKey varchar(138)11  DECLARE @AbortMethod nvarchar(64)12  DECLARE @reason nvarchar(1000)13  DECLARE @MyName nvarchar(64) = object_name(@@procid)14  DECLARE @DecisionOnInsert int = 015  DECLARE @UID_QERWorkingMethod varchar(38)16  DECLARE @UID_PWODecisionMethod varchar(38)17  DECLARE @OrderState varchar(16)18  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')19  DECLARE @ElementLast int20  DECLARE @ElementCount int21  DECLARE @ElementIndex int22  DECLARE @PWOsToFill QBM_YParameterlist23  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent24  BEGIN TRY25    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,26    UID_Parameter,27    UID_SubParameter,28    GenProcID)29    SELECT30      UID_DialogDBQueue,31      UID_Parameter,32      UID_SubParameter,33      GenProcID34    FROM QBMDBQueueCurrent cu35      WITH(readpast)36    WHERE37      cu.SlotNumber = @SlotNumber38    IF @@rowcount = 039    BEGIN40      GOTO EndLabel41    END42    DECLARE @ElementReset QBM_YCursorBuffer43    INSERT INTO @ElementReset(UID1,44    UID2,45    UID3)46    SELECT47      p.uid_parameter,48      newid(),49      p.GenProcID50    FROM @DBQueueCurrent p51    SELECT @ElementCount = @@ROWCOUNT52    SELECT @ElementIndex = @@IDENTITY - @ElementCount +153    SELECT @ElementLast = @@IDENTITY54    WHILE @ElementIndex <= @ElementLast55    BEGIN56      SELECT57        TOP 1 @uid_personwantsorg = bu.UID1,58        @UID_PWODecisionHistory = bu.UID2,59        @GenProcID = bu.UID360      FROM @ElementReset bu61      WHERE62        bu.ElementIndex = @ElementIndex63      EXEC QBM_PSessionContextSet 'GenProcID',64        @GenProcID65      INSERT INTO PWODecisionHistory(DecisionType,66      IsDecisionBySystem,67      UID_PWODecisionHistory,68      UID_PWOState,69      UID_PersonHead,70      UID_PersonWantsOrg,71      DisplayPersonHead,72      ReasonHead,73      DateHead,74      XDateInserted,75      XDateUpdated,76      XUserInserted,77      XUserUpdated,78      DecisionLevel,79      XTouched,80      ValidUntil,81      ValidFrom,82      XObjectKey,83      UID_ITShopOrgFinal,84      OrderState)85      SELECT86        'Reset',87        1,88        @UID_PWODecisionHistory,89        NULL,90        NULL,91        @uid_personwantsorg,92        dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Automatic system approval.|'),93        dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Procedure deferred due to changed decision.|'),94        GetUTCDate(),95        GetUTCDate(),96        GetUTCDate(),97        'sa',98        'sa',99        0,100        NULL,101        NULL,102        NULL,103        dbo.QBM_FCVElementToObjectKey1('PWODecisionHistory',104        'UID_PWODecisionHistory',105        @UID_PWODecisionHistory),106        pwo.UID_ITShopOrgFinal,107        pwo.OrderState108      FROM PersonWantsOrg pwo109      WHERE110        pwo.UID_PersonWantsOrg = @uid_personwantsorg111      SELECT @ElementIndex += 1112    END113    IF dbo.QBM_FGIConfigparmValue('QER\ITShop\DecisionOnInsert') > ' '114    BEGIN115      SELECT @DecisionOnInsert = 1116    END117    DECLARE @ElementDieAnderen QBM_YCursorBuffer118    INSERT INTO @ElementDieAnderen(UID1,119    UID2,120    UID3,121    Ident1)122    SELECT123      p.uid_parameter,124      p.GenProcID,125      pr.UID_PWODecisionMethod,126      pwo.OrderState127    FROM @DBQueueCurrent p128    JOIN PersonWantsOrg pwo129      ON p.uid_parameter = pwo.UID_PersonWantsOrg130    JOIN BaseTree pr131      ON pwo.UID_Org = pr.UID_Org AND pr.ITShopInfo = 'PR'132    SELECT @ElementCount = @@ROWCOUNT133    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1134    SELECT @ElementLast = @@IDENTITY135    WHILE @ElementIndex <= @ElementLast136    BEGIN137      SELECT138        TOP 1 @UID_Personwantsorg = bu.UID1,139        @GenProcID = bu.UID2,140        @UID_PWODecisionMethod = bu.UID3,141        @OrderState = bu.Ident1142      FROM @ElementDieAnderen bu143      WHERE144        bu.ElementIndex = @ElementIndex145      EXEC QER_PGIQERWorkingMethod @UID_PWODecisionMethod,146        @OrderState,147        @UID_QERWorkingMethod OUTPUT148      EXEC QBM_PSessionContextSet 'GenProcID',149        @GenProcID150      UPDATE personwantsorg151      SET decisionlevel = 0,152      UID_PWOState = NULL,153      UID_PersonHead = NULL,154      DisplayPersonHead = NULL,155      DateHead = GetUTCDate(),156      OrderDate = GetUTCDate(),157      ReasonHead = dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Procedure deferred due to changed decision.|'),158      XDateUpdated = GetUTCDate(),159      XUserUpdated = 'DBScheduler',160      UID_QERWorkingMethod = @UID_QERWorkingMethod161      FROM PersonWantsOrg pwo162      WHERE163        uid_personwantsorg = @UID_Personwantsorg164      EXEC QBM_PSessionContextSet 'GenProcID',165        @GenProcID166      DELETE HelperPWOCompliance167      WHERE168        uid_personwantsorg = @UID_Personwantsorg169      EXEC QBM_PSessionContextSet 'GenProcID',170        @GenProcID171      DELETE HelperPWOPersonhasObject172      WHERE173        uid_personwantsorg = @UID_Personwantsorg174      EXEC QBM_PSessionContextSet 'GenProcID',175        @GenProcID176      DELETE pwohelperpwo177      WHERE178        uid_personwantsorg = @UID_Personwantsorg179      SELECT @ElementIndex += 1180    END181    IF @DecisionOnInsert = 1182    BEGIN183      DELETE @PWOsToFill184      INSERT INTO @PWOsToFill(Parameter1,185      Parameter2,186      Parameter3,187      HasContentFull)188      SELECT189        p.uid_parameter,190        p.GenProcID,191        '0',192        1193      FROM @DBQueueCurrent p194      JOIN PersonWantsOrg pwo195        ON p.uid_parameter = pwo.UID_PersonWantsOrg196      JOIN BaseTree pr197        ON pwo.UID_Org = pr.UID_Org AND pr.ITShopInfo = 'PR'198      EXEC QER_PITShopHelperFill_bulk @PWOsToFill199      EXEC QER_PITShopPersonHasObjFill_b @PWOsToFill200    END201    Restaufgaben:202    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw203    INSERT INTO @DBQueueElements_01(object,204    subobject,205    genprocid)206    SELECT207      x.uid,208      NULL,209      x.GenProcID210    FROM(211    SELECT212      uid_parameter AS uid,213      genprocid AS GenProcID214    FROM @DBQueueCurrent p) AS x215    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPWOHelperPWO',216      @DBQueueElements_01217    EXEC QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPersonHasObject',218      @DBQueueElements_01219  END TRY220  BEGIN CATCH221    EXEC QBM_PSessionErrorAdd DEFAULT222    RAISERROR('',223    18,224    1)225      WITH NOWAIT226  END CATCH227  endLabel:228  EXEC QBM_PSessionContextSet 'GenProcID',229    @GenProcID_R230  RETURN231END
Open raw exported source
SQL · Raw36 lines
1      create   procedure QER_ZITShopOrderReset (@SlotNumber int)  as begin  declare @uid_personwantsorg varchar(38) declare @UID_PWODecisionHistory2 varchar(38) declare @GenProcID varchar(38) declare @cond nvarchar(1000) declare @BasisObjectKey varchar(138) declare @AbortMethod nvarchar(64)  declare3 @reason nvarchar(1000) declare @MyName nvarchar(64) = object_name(@@procid) declare @DecisionOnInsert int = 0 declare @UID_QERWorkingMethod varchar(384) declare @UID_PWODecisionMethod varchar(38) declare @OrderState varchar(16) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @ElementLast5 int declare @ElementCount int declare @ElementIndex int declare @PWOsToFill QBM_YParameterlist      declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN 6TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter7, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end   declare @ElementReset8 QBM_YCursorBuffer insert into @ElementReset (UID1, UID2, UID3) select p.uid_parameter, newid(), p.GenProcID from @DBQueueCurrent p select @ElementCount9 = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top10 1 @uid_personwantsorg = bu.UID1 , @UID_PWODecisionHistory = bu.UID2 , @GenProcID = bu.UID3 from @ElementReset bu where bu.ElementIndex = @ElementIndex11 exec QBM_PSessionContextSet 'GenProcID', @GenProcID insert into PWODecisionHistory (DecisionType, IsDecisionBySystem, UID_PWODecisionHistory, UID_PWOState12, UID_PersonHead, UID_PersonWantsOrg, DisplayPersonHead, ReasonHead, DateHead, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, DecisionLevel,13 XTouched, ValidUntil, ValidFrom , XObjectKey  , UID_ITShopOrgFinal, OrderState) select 'Reset', 1, @UID_PWODecisionHistory, null, null, @uid_personwantsorg14, dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Automatic system approval.|') , dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Procedure deferred due to changed decision.|'15), GetUTCDate(), GetUTCDate(), GetUTCDate(), 'sa', 'sa', 0, null, null, null, dbo.QBM_FCVElementToObjectKey1('PWODecisionHistory', 'UID_PWODecisionHistory'16, @UID_PWODecisionHistory)  , pwo.UID_ITShopOrgFinal, pwo.OrderState from PersonWantsOrg pwo where pwo.UID_PersonWantsOrg = @uid_personwantsorg select 17@ElementIndex += 1 end   if dbo.QBM_FGIConfigparmValue('QER\ITShop\DecisionOnInsert') > ' ' begin select @DecisionOnInsert = 1 end declare @ElementDieAnderen18 QBM_YCursorBuffer insert into @ElementDieAnderen (UID1, UID2, UID3, Ident1) select p.uid_parameter, p.GenProcID, pr.UID_PWODecisionMethod, pwo.OrderState19 from @DBQueueCurrent p join PersonWantsOrg pwo on p.uid_parameter = pwo.UID_PersonWantsOrg join BaseTree pr on pwo.UID_Org = pr.UID_Org and pr.ITShopInfo20 = 'PR' select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= 21@ElementLast begin select top 1 @UID_Personwantsorg = bu.UID1 , @GenProcID = bu.UID2 , @UID_PWODecisionMethod = bu.UID3 , @OrderState = bu.Ident1 from 22@ElementDieAnderen bu where bu.ElementIndex = @ElementIndex        exec QER_PGIQERWorkingMethod @UID_PWODecisionMethod, @OrderState, @UID_QERWorkingMethod23 output exec QBM_PSessionContextSet 'GenProcID', @GenProcID update personwantsorg set decisionlevel = 0, UID_PWOState = null, UID_PersonHead = null, DisplayPersonHead24 = null, DateHead = GetUTCDate(), OrderDate = GetUTCDate(), ReasonHead = dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Procedure deferred due to changed decision.|'25), XDateUpdated = GetUTCDate(), XUserUpdated = 'DBScheduler' , UID_QERWorkingMethod = @UID_QERWorkingMethod from PersonWantsOrg pwo where uid_personwantsorg26 = @UID_Personwantsorg exec QBM_PSessionContextSet 'GenProcID', @GenProcID  delete HelperPWOCompliance where uid_personwantsorg = @UID_Personwantsorg exec27 QBM_PSessionContextSet 'GenProcID', @GenProcID delete HelperPWOPersonhasObject where uid_personwantsorg = @UID_Personwantsorg exec QBM_PSessionContextSet28 'GenProcID', @GenProcID delete pwohelperpwo where uid_personwantsorg = @UID_Personwantsorg         select @ElementIndex += 1 end  if @DecisionOnInsert29 = 1 begin delete  @PWOsToFill  insert into @PWOsToFill(Parameter1, Parameter2, Parameter3, HasContentFull) select p.uid_parameter, p.GenProcID, '0', 130 from @DBQueueCurrent p join PersonWantsOrg pwo on p.uid_parameter = pwo.UID_PersonWantsOrg join BaseTree pr on pwo.UID_Org = pr.UID_Org and pr.ITShopInfo31 = 'PR' exec QER_PITShopHelperFill_bulk @PWOsToFill exec QER_PITShopPersonHasObjFill_b @PWOsToFill end Restaufgaben: declare @DBQueueElements_01 QBM_YDBQueueRaw32 insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, x.GenProcID from ( select uid_parameter as uid, genprocid as GenProcID33 from @DBQueueCurrent p ) as x  exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPWOHelperPWO', @DBQueueElements_01  exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPersonHasObject'34, @DBQueueElements_01 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet35 'GenProcID', @GenProcID_R return end 36