Back to OIM Explorer

dbo.ATT_ZAttHelperFill_Recalc

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> ' , @AttTaskToUse, ' at line 31; Bulk DBQueue insert -> A at line 31; Bulk DBQueue insert -> A at line 31; Bulk DBQueue insert -> A at line 40

Source: sandbox-db sys.sql_modules

Source size: 4.394 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 -> ' , @AttTaskToUse, ' at line 31
  • Bulk DBQueue insert -> A at line 31
  • Bulk DBQueue insert -> A at line 31
  • Bulk DBQueue insert -> A at line 40
  • Bulk DBQueue insert -> A at line 40
  • Bulk DBQueue insert -> A at line 45
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task ' , @AttTaskToUse, ' at line 31 Bulk DBQueue insert -> ' , @AttTaskToUse, ' at line 31
  • queues DBQueue task A at line 31 Bulk DBQueue insert -> A at line 31
  • queues DBQueue task A at line 40 Bulk DBQueue insert -> A at line 40
  • queues DBQueue task A at line 45 Bulk DBQueue insert -> A at line 45
  • references source dbo.ATT_FGIPWOTaskToUse source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QER_FCVRuleGUIDToFuncNameReclc source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • queues DBQueue task ' , @AttTaskToUse, ' -> unresolved procedure QBM_PDBQueueInsert_Bulk where not exists (select top 1 1 from DialogDBQueue q with (nolock) where q.UID_Task = ''' , @AttTaskToUse, ''' and q.Object = x.uid and q.Generation >= 0 ) ') delete @DBQueueElements_01 insert into @DBQueueElements…
  • queues DBQueue task A -> unresolved procedure QBM_PDBQueueInsert_Bulk @AttTaskToUse, @DBQueueElements_01 end else begin delete @DBQueueElements_01 insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, x.GenProcID from ( select distinct ac.UID_AttestationCa…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL175 lines
1CREATE PROCEDURE ATT_ZAttHelperFill_Recalc(2  @SlotNumberDummy int,3  @dummy1 varchar(38),4  @Dummy2 varchar(38),5  @GenProcIDDummy varchar(38)6)7AS8BEGIN9  DECLARE @DebugSwitch int = 010  DECLARE @ElementLast int11  DECLARE @ElementCount int12  DECLARE @ElementIndex int13  DECLARE @RulesAndFunctions QBM_YCursorBuffer14  DECLARE @FunctionName varchar(30)15  DECLARE @SQLCmd nvarchar(max)16  DECLARE @UID_PWODecisionRule varchar(38)17  DECLARE @GenProcID varchar(38)18  DECLARE @AttTaskToUse varchar(38) = dbo.ATT_FGIPWOTaskToUse()19  BEGIN TRY20    IF dbo.QBM_FGIConfigparmValue('QER\Attestation') = ''21    BEGIN22      GOTO endLabel23    END24    IF @AttTaskToUse > ' '25    BEGIN26      DECLARE @DBQueueElements_01 QBM_YDBQueueRaw27      INSERT INTO @RulesAndFunctions(UID1,28      Ident1,29      UID2)30      SELECT31        UID_PWODecisionRule,32        dbo.QER_FCVRuleGUIDToFuncNameReclc(UID_PWODecisionRule),33        max(GenProcID)34      FROM QERBufferRecalcDecisionMaker35        WITH(readpast)36      WHERE37        UsageArea = 'A' AND ProcessState = 038      GROUP BY UID_PWODecisionRule,39      dbo.QER_FCVRuleGUIDToFuncNameReclc(UID_PWODecisionRule)40      SELECT @ElementCount = @@ROWCOUNT41      SELECT @ElementIndex = @@IDENTITY - @ElementCount +142      SELECT @ElementLast = @@IDENTITY43      WHILE @ElementIndex <= @ElementLast44      BEGIN45        SELECT46          TOP 1 @FunctionName = p.Ident1,47          @UID_PWODecisionRule = p.UID1,48          @GenProcID = p.UID249        FROM @RulesAndFunctions p50        WHERE51          p.ElementIndex = @ElementIndex52        UPDATE QERBufferRecalcDecisionMaker53        SET ProcessState = 154        FROM QERBufferRecalcDecisionMaker55          WITH(readpast)56        WHERE57          UID_PWODecisionRule = @UID_PWODecisionRule AND ProcessState = 058        IF EXISTS(59          SELECT TOP 1 160          FROM AttestationHelper61          WHERE62            UID_PWODecisionRule = @UID_PWODecisionRule)63        BEGIN64          IF EXISTS(65            SELECT TOP 1 166            FROM sys.objects o67            WHERE68              o.name = @FunctionName AND o.type IN('IF', 'TF'))69          BEGIN70            SELECT71              @SQLcmd = CONCAT('72							select x.uid, null,  x.GenProcID73							from (	 74								  select distinct ac.UID_AttestationCase as uid, '''75              ,76              @GenProcID,77              ''' as GenProcID78									from pwodecisionrule r join QERWorkingStep ps on  ps.UID_PWODecisionRule = r.UID_PWODecisionRule79															join AttestationCase a on ps.UID_QERWorkingMethod = a.UID_QERWorkingMethod80															join dbo.'81              ,82              @FunctionName,83              ' () ac on ac.UID_AttestationCase = a.UID_AttestationCase84									where r.UID_PWODecisionRule = ''',85              @UID_PWODecisionRule,86              '''87										and r.UsageArea =''A''88										and r.UID_Task is null -- keine Systementscheidung89									-- oder es gibt potentiell was zu löschen90									or exists ( select top 1 191												from AttestationHelper h92												where h.UID_AttestationCase = ac.UID_AttestationCase93												)9495								) as x96							-- 33082 hier haben wir uns wohl die Karten gelegt durch das Abschalten des CheckForExisting im QBM_PDBQueueInsert_Bulk97							where not exists (select top 1 198												from DialogDBQueue q with (nolock)99												where q.UID_Task = '''100              ,101              @AttTaskToUse,102              '''103												and q.Object = x.uid104												and q.Generation >= 0105											)106									')107            DELETE @DBQueueElements_01108            INSERT INTO @DBQueueElements_01(object,109            subobject,110            genprocid)111            EXEC sp_executesql @SQLcmd112            EXEC QBM_PDBQueueInsert_Bulk @AttTaskToUse,113              @DBQueueElements_01114          END115          ELSE116          BEGIN117            DELETE @DBQueueElements_01118            INSERT INTO @DBQueueElements_01(object,119            subobject,120            genprocid)121            SELECT122              x.uid,123              NULL,124              x.GenProcID125            FROM(126            SELECT127              DISTINCT ac.UID_AttestationCase AS uid,128              @GenProcID AS GenProcID129            FROM pwodecisionrule r130            JOIN QERWorkingStep ps131              ON ps.UID_PWODecisionRule = r.UID_PWODecisionRule132            JOIN AttestationCase ac133              ON ps.UID_QERWorkingMethod = ac.UID_QERWorkingMethod134            WHERE135              r.UID_PWODecisionRule = @UID_PWODecisionRule AND r.UsageArea = 'A' AND r.UID_Task IS NULL OR EXISTS(136            SELECT TOP 1 1137            FROM AttestationHelper h138            WHERE139              h.UID_AttestationCase = ac.UID_AttestationCase)) AS x140            WHERE141              NOT EXISTS(142            SELECT TOP 1 1143            FROM DialogDBQueue q144              WITH(nolock)145            WHERE146              q.UID_Task = @AttTaskToUse AND q.Object = x.uid AND q.Generation >= 0)147            EXEC QBM_PDBQueueInsert_Bulk @AttTaskToUse,148              @DBQueueElements_01149          END150        END151        UPDATE QERBufferRecalcDecisionMaker152        SET ProcessState = 2153        WHERE154          UID_PWODecisionRule = @UID_PWODecisionRule AND ProcessState = 1155        SELECT @ElementIndex += 1156      END157    END158    ELSE159    BEGIN160      UPDATE QERBufferRecalcDecisionMaker161      SET ProcessState = 2162      WHERE163        UsageArea = 'A' AND ProcessState = 0164    END165  END TRY166  BEGIN CATCH167    EXEC QBM_PSessionErrorAdd DEFAULT168    RAISERROR('',169    18,170    1)171      WITH NOWAIT172  END CATCH173  endLabel:174  RETURN175END
Open raw exported source
SQL · Raw49 lines
1   create   procedure ATT_ZAttHelperFill_Recalc ( @SlotNumberDummy int , @dummy1 varchar(38) , @Dummy2 varchar(38) , @GenProcIDDummy varchar(38)2 ) as begin declare @DebugSwitch int = 0 declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare @RulesAndFunctions QBM_YCursorBuffer3 declare @FunctionName varchar(30) declare @SQLCmd nvarchar(max) declare @UID_PWODecisionRule varchar(38) declare @GenProcID varchar(38) declare @AttTaskToUse4 varchar(38) = dbo.ATT_FGIPWOTaskToUse() BEGIN TRY if dbo.QBM_FGIConfigparmValue('QER\Attestation') = '' begin goto endLabel end if @AttTaskToUse > ' '5 begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @RulesAndFunctions (UID1, Ident1, UID2) select UID_PWODecisionRule, dbo.QER_FCVRuleGUIDToFuncNameReclc6(UID_PWODecisionRule), max(GenProcID) from QERBufferRecalcDecisionMaker with (readpast) where UsageArea = 'A' and ProcessState = 0 group by UID_PWODecisionRule7, dbo.QER_FCVRuleGUIDToFuncNameReclc(UID_PWODecisionRule) select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select8 @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @FunctionName = p.Ident1 , @UID_PWODecisionRule = p.UID1 , @GenProcID9 = p.UID2 from @RulesAndFunctions p where p.ElementIndex = @ElementIndex update QERBufferRecalcDecisionMaker set ProcessState = 1 from QERBufferRecalcDecisionMaker10 with (readpast) where UID_PWODecisionRule = @UID_PWODecisionRule and ProcessState = 0  if exists (select top 1 1 from AttestationHelper where UID_PWODecisionRule11 = @UID_PWODecisionRule) begin  if exists (select top 1 1 from sys.objects o where o.name = @FunctionName and o.type in ('IF'  , 'TF'  ) ) begin select12 @SQLcmd = concat('13							select x.uid, null,  x.GenProcID14							from (	 15								  select distinct ac.UID_AttestationCase as uid, ''', @GenProcID16, ''' as GenProcID17									from pwodecisionrule r join QERWorkingStep ps on  ps.UID_PWODecisionRule = r.UID_PWODecisionRule18															join AttestationCase a on ps.UID_QERWorkingMethod = a.UID_QERWorkingMethod19															join dbo.'20, @FunctionName, ' () ac on ac.UID_AttestationCase = a.UID_AttestationCase21									where r.UID_PWODecisionRule = ''', @UID_PWODecisionRule, '''22										and r.UsageArea =''A''23										and r.UID_Task is null -- keine Systementscheidung24									-- oder es gibt potentiell was zu löschen25									or exists ( select top 1 126												from AttestationHelper h27												where h.UID_AttestationCase = ac.UID_AttestationCase28												)2930								) as x31							-- 33082 hier haben wir uns wohl die Karten gelegt durch das Abschalten des CheckForExisting im QBM_PDBQueueInsert_Bulk32							where not exists (select top 1 133												from DialogDBQueue q with (nolock)34												where q.UID_Task = '''35, @AttTaskToUse, '''36												and q.Object = x.uid37												and q.Generation >= 038											)39									') delete @DBQueueElements_01 insert 40into @DBQueueElements_01 (object, subobject, genprocid) exec sp_executesql @SQLcmd exec QBM_PDBQueueInsert_Bulk @AttTaskToUse, @DBQueueElements_01 end 41else  begin delete @DBQueueElements_01 insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, x.GenProcID from ( select distinct42 ac.UID_AttestationCase as uid, @GenProcID as GenProcID from pwodecisionrule r join QERWorkingStep ps on ps.UID_PWODecisionRule = r.UID_PWODecisionRule43 join AttestationCase ac on ps.UID_QERWorkingMethod = ac.UID_QERWorkingMethod where r.UID_PWODecisionRule = @UID_PWODecisionRule and r.UsageArea ='A' and44 r.UID_Task is null   or exists ( select top 1 1 from AttestationHelper h where h.UID_AttestationCase = ac.UID_AttestationCase ) ) as x  where not exists45 (select top 1 1 from DialogDBQueue q with (nolock) where q.UID_Task = @AttTaskToUse and q.Object = x.uid and q.Generation >= 0 ) exec QBM_PDBQueueInsert_Bulk46 @AttTaskToUse, @DBQueueElements_01 end  end  update QERBufferRecalcDecisionMaker set ProcessState = 2 where UID_PWODecisionRule = @UID_PWODecisionRule47 and ProcessState = 1 select @ElementIndex += 1 end  end else begin update QERBufferRecalcDecisionMaker set ProcessState = 2 where UsageArea = 'A' and 48ProcessState = 0 end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end 49