Back to OIM Explorer

dbo.QER_ZITShopHelperFill_Recalc

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> ' , @PWOTaskToUse, ' at line 32; Bulk DBQueue insert -> I at line 32; Bulk DBQueue insert -> I at line 32; Bulk DBQueue insert -> I at line 41

Source: sandbox-db sys.sql_modules

Source size: 4.642 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 -> ' , @PWOTaskToUse, ' at line 32
  • Bulk DBQueue insert -> I at line 32
  • Bulk DBQueue insert -> I at line 32
  • Bulk DBQueue insert -> I at line 41
  • Bulk DBQueue insert -> I at line 41
  • Bulk DBQueue insert -> I at line 47
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task ' , @PWOTaskToUse, ' at line 32 Bulk DBQueue insert -> ' , @PWOTaskToUse, ' at line 32
  • queues DBQueue task I at line 32 Bulk DBQueue insert -> I at line 32
  • queues DBQueue task I at line 41 Bulk DBQueue insert -> I at line 41
  • queues DBQueue task I at line 47 Bulk DBQueue insert -> I at line 47
  • references source dbo.QBM_FCVStringToBit source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QER_FCVRuleGUIDToFuncNameReclc source text reference
  • references source dbo.QER_FGIGenProcIDForPWO source text reference
  • references source dbo.QER_FGIPWOTaskToUse source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_ZITShopHelperFill source text reference
  • queues DBQueue task ' , @PWOTaskToUse, ' -> unresolved procedure QBM_PDBQueueInsert_Bulk where not exists (select top 1 1 from DialogDBQueue q with (nolock) where q.UID_Task = ''' , @PWOTaskToUse, ''' and q.Object = x.uid and q.Generation >= 0 ) ') delete @DBQueueElements_01 insert into @DBQueueElements…
  • queues DBQueue task I -> unresolved procedure QBM_PDBQueueInsert_Bulk @PWOTaskToUse, @DBQueueElements_01 end else begin delete @DBQueueElements_01 insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, x.GenProcID from ( select distinct pwo.UID_PersonWantsO…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL173 lines
1CREATE PROCEDURE QER_ZITShopHelperFill_Recalc(2  @SlotNumberDummy int,3  @dummy1 varchar(38),4  @Dummy2 varchar(38),5  @GenProcIDDummy varchar(38)6)7AS8BEGIN9  DECLARE @DebugSwitch int = 010  DECLARE @CfgUseGenProcID BIT = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'))11  DECLARE @PWOTaskToUse varchar(38) = dbo.QER_FGIPWOTaskToUse()12  DECLARE @ElementLast int13  DECLARE @ElementCount int14  DECLARE @ElementIndex int15  DECLARE @RulesAndFunctions QBM_YCursorBuffer16  DECLARE @FunctionName varchar(30)17  DECLARE @SQLCmd nvarchar(max)18  DECLARE @UID_PWODecisionRule varchar(38)19  DECLARE @GenProcID varchar(38)20  BEGIN TRY21    IF dbo.QBM_FGIConfigparmValue('QER\ITShop') = ''22    BEGIN23      GOTO endLabel24    END25    IF @PWOTaskToUse > ' '26    BEGIN27      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw28      INSERT INTO @RulesAndFunctions(UID1,29      Ident1,30      UID2)31      SELECT32        UID_PWODecisionRule,33        dbo.QER_FCVRuleGUIDToFuncNameReclc(UID_PWODecisionRule),34        max(GenProcID)35      FROM QERBufferRecalcDecisionMaker36        WITH(readpast)37      WHERE38        UsageArea = 'I' AND ProcessState = 039      GROUP BY UID_PWODecisionRule,40      dbo.QER_FCVRuleGUIDToFuncNameReclc(UID_PWODecisionRule)41      SELECT @ElementCount = @@ROWCOUNT42      SELECT @ElementIndex = @@IDENTITY - @ElementCount +143      SELECT @ElementLast = @@IDENTITY44      WHILE @ElementIndex <= @ElementLast45      BEGIN46        SELECT47          TOP 1 @FunctionName = p.Ident1,48          @UID_PWODecisionRule = p.UID1,49          @GenProcID = p.UID250        FROM @RulesAndFunctions p51        WHERE52          p.ElementIndex = @ElementIndex53        UPDATE QERBufferRecalcDecisionMaker54        SET ProcessState = 155        FROM QERBufferRecalcDecisionMaker56          WITH(readpast)57        WHERE58          UID_PWODecisionRule = @UID_PWODecisionRule AND ProcessState = 059        IF EXISTS(60          SELECT TOP 1 161          FROM sys.objects o62          WHERE63            o.name = @FunctionName AND o.type IN('IF', 'TF'))64        BEGIN65          SELECT66            @SQLcmd = CONCAT('67							select x.uid, null,  x.GenProcID68							from (	 69								  select distinct pwo.UID_PersonWantsOrg as uid , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, '''70            ,71            @GenProcID,72            ''', ''',73            @CfgUseGenProcID,74            ''') as GenProcID75									from pwodecisionrule r join QERWorkingStep ps on  ps.UID_PWODecisionRule = r.UID_PWODecisionRule76															join dbo.'77            ,78            @FunctionName,79            ' () pwo on ps.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod80									where r.UID_PWODecisionRule = ''',81            @UID_PWODecisionRule,82            '''83										and r.UsageArea =''I''84										and r.UID_Task is null -- keine Systementscheidung85										and pwo.OrderState in (''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe''86															)87									-- oder es gibt potentiell was zu löschen88									or exists ( select top 1 189												from PWOHelperPWO h90												where h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg91												)9293								) as x94							-- 33082 hier haben wir uns wohl die Karten gelegt durch das Abschalten des CheckForExisting im QBM_PDBQueueInsert_Bulk95							where not exists (select top 1 196												from DialogDBQueue q with (nolock)97												where q.UID_Task = '''98            ,99            @PWOTaskToUse,100            '''101												and q.Object = x.uid102												and q.Generation >= 0103											)104									')105          DELETE @DBQueueElements_01106          INSERT INTO @DBQueueElements_01(object,107          subobject,108          genprocid)109          EXEC sp_executesql @SQLcmd110          EXEC QBM_PDBQueueInsert_Bulk @PWOTaskToUse,111            @DBQueueElements_01112        END113        ELSE114        BEGIN115          DELETE @DBQueueElements_01116          INSERT INTO @DBQueueElements_01(object,117          subobject,118          genprocid)119          SELECT120            x.uid,121            NULL,122            x.GenProcID123          FROM(124          SELECT125            DISTINCT pwo.UID_PersonWantsOrg AS uid,126            dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) AS GenProcID127          FROM pwodecisionrule r128          JOIN QERWorkingStep ps129            ON ps.UID_PWODecisionRule = r.UID_PWODecisionRule130          JOIN PersonWantsOrg pwo131            ON ps.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod132          WHERE133            r.UID_PWODecisionRule = @UID_PWODecisionRule AND r.UsageArea = 'I' AND r.UID_Task IS NULL AND pwo.OrderState IN('OrderProduct',134          'OrderProlongate', 'OrderUnsubscribe') OR EXISTS(135          SELECT TOP 1 1136          FROM PWOHelperPWO h137          WHERE138            h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg)) AS x139          WHERE140            NOT EXISTS(141          SELECT TOP 1 1142          FROM DialogDBQueue q143            WITH(nolock)144          WHERE145            q.UID_Task = @PWOTaskToUse AND q.Object = x.uid AND q.Generation >= 0)146          EXEC QBM_PDBQueueInsert_Bulk @PWOTaskToUse,147            @DBQueueElements_01148        END149        UPDATE QERBufferRecalcDecisionMaker150        SET ProcessState = 2151        WHERE152          UID_PWODecisionRule = @UID_PWODecisionRule AND ProcessState = 1153        SELECT @ElementIndex += 1154      END155    END156    ELSE157    BEGIN158      UPDATE QERBufferRecalcDecisionMaker159      SET ProcessState = 2160      WHERE161        UsageArea = 'I' AND ProcessState = 0162    END163  END TRY164  BEGIN CATCH165    EXEC QBM_PSessionErrorAdd DEFAULT166    RAISERROR('',167    18,168    1)169      WITH NOWAIT170  END CATCH171  endLabel:172  RETURN173END
Open raw exported source
SQL · Raw51 lines
1   create   procedure QER_ZITShopHelperFill_Recalc ( @SlotNumberDummy int , @dummy1 varchar(38) , @Dummy2 varchar(38) , @GenProcIDDummy varchar(382) ) as begin declare @DebugSwitch int = 0 declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'3)) declare @PWOTaskToUse varchar(38) = dbo.QER_FGIPWOTaskToUse() declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare @RulesAndFunctions4 QBM_YCursorBuffer declare @FunctionName varchar(30) declare @SQLCmd nvarchar(max) declare @UID_PWODecisionRule varchar(38) declare @GenProcID varchar(385) BEGIN TRY if dbo.QBM_FGIConfigparmValue('QER\ITShop') = '' begin goto endLabel end if @PWOTaskToUse > ' ' begin declare @DBQueueElements_01 QBM_YDBQueueRaw6 insert into @RulesAndFunctions (UID1, Ident1, UID2) select UID_PWODecisionRule, dbo.QER_FCVRuleGUIDToFuncNameReclc(UID_PWODecisionRule), max(GenProcID7) from QERBufferRecalcDecisionMaker with (readpast) where UsageArea = 'I' and ProcessState = 0 group by UID_PWODecisionRule, dbo.QER_FCVRuleGUIDToFuncNameReclc8(UID_PWODecisionRule) select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex9 <= @ElementLast begin select top 1 @FunctionName = p.Ident1 , @UID_PWODecisionRule = p.UID1 , @GenProcID = p.UID2 from @RulesAndFunctions p where p.ElementIndex10 = @ElementIndex update QERBufferRecalcDecisionMaker set ProcessState = 1 from QERBufferRecalcDecisionMaker with (readpast) where UID_PWODecisionRule =11 @UID_PWODecisionRule and ProcessState = 0      if exists (select top 1 1 from sys.objects o where o.name = @FunctionName and o.type in ('IF'  , 'TF'  12) ) begin select @SQLcmd = concat('13							select x.uid, null,  x.GenProcID14							from (	 15								  select distinct pwo.UID_PersonWantsOrg as uid , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, '''16, @GenProcID, ''', ''', @CfgUseGenProcID, ''') as GenProcID17									from pwodecisionrule r join QERWorkingStep ps on  ps.UID_PWODecisionRule = r.UID_PWODecisionRule18															join dbo.'19, @FunctionName, ' () pwo on ps.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod20									where r.UID_PWODecisionRule = ''', @UID_PWODecisionRule, '''21										and r.UsageArea =''I''22										and r.UID_Task is null -- keine Systementscheidung23										and pwo.OrderState in (''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe''24															)25									-- oder es gibt potentiell was zu löschen26									or exists ( select top 1 127												from PWOHelperPWO h28												where h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg29												)3031								) as x32							-- 33082 hier haben wir uns wohl die Karten gelegt durch das Abschalten des CheckForExisting im QBM_PDBQueueInsert_Bulk33							where not exists (select top 1 134												from DialogDBQueue q with (nolock)35												where q.UID_Task = '''36, @PWOTaskToUse, '''37												and q.Object = x.uid38												and q.Generation >= 039											)40									') delete @DBQueueElements_01 insert 41into @DBQueueElements_01 (object, subobject, genprocid) exec sp_executesql @SQLcmd exec QBM_PDBQueueInsert_Bulk @PWOTaskToUse, @DBQueueElements_01 end 42else  begin delete @DBQueueElements_01 insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, x.GenProcID from ( select distinct43 pwo.UID_PersonWantsOrg as uid , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, @GenProcID, @CfgUseGenProcID) as GenProcID from pwodecisionrule r join QERWorkingStep44 ps on ps.UID_PWODecisionRule = r.UID_PWODecisionRule join PersonWantsOrg pwo on ps.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod where r.UID_PWODecisionRule45 = @UID_PWODecisionRule and r.UsageArea ='I' and r.UID_Task is null  and pwo.OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe')  or 46exists ( select top 1 1 from PWOHelperPWO h where h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg ) ) as x  where not exists (select top 1 1 from DialogDBQueue47 q with (nolock) where q.UID_Task = @PWOTaskToUse and q.Object = x.uid and q.Generation >= 0 )  exec QBM_PDBQueueInsert_Bulk @PWOTaskToUse, @DBQueueElements_0148 end    update QERBufferRecalcDecisionMaker set ProcessState = 2 where UID_PWODecisionRule = @UID_PWODecisionRule and ProcessState = 1 select @ElementIndex49 += 1 end  end else begin update QERBufferRecalcDecisionMaker set ProcessState = 2 where UsageArea = 'I' and ProcessState = 0 end END TRY BEGIN CATCH exec50 QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end 51