Back to OIM Explorer

dbo.ATT_ZAttestationReset

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> ATT-K-AttestationHelper / ATT_ZAttestationHelperFill at line 23; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 3.871 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 -> ATT-K-AttestationHelper / ATT_ZAttestationHelperFill at line 23
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task ATT_ZAttestationHelperFill at line 23 Bulk DBQueue insert -> ATT-K-AttestationHelper / ATT_ZAttestationHelperFill at line 23
  • references source dbo.QBM_FCVElementToObjectKey1 source text reference
  • references source dbo.QBM_FCVStringTrimLDSPrefix 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
  • queues DBQueue task ATT-K-AttestationHelper -> ATT_ZAttestationHelperFill QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper' , @DBQueueElements_01 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R retur…

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL167 lines
1CREATE PROCEDURE ATT_ZAttestationReset(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @UID_AttestationCase varchar(38)7  DECLARE @UID_AttestationHistory varchar(38)8  DECLARE @GenProcID varchar(38)9  DECLARE @DebugLevel char(1) = 'W'10  DECLARE @UID_QERWorkingMethod varchar(38)11  DECLARE @UID_PWODecisionMethod varchar(38)12  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')13  DECLARE @ElementLast int14  DECLARE @ElementCount int15  DECLARE @ElementIndex int16  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent17  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    DECLARE @ElementBuffer_reset QBM_YCursorBuffer36    INSERT INTO @ElementBuffer_reset(UID1,37    UID2,38    UID3)39    SELECT40      c.uid_Parameter,41      c.GenProcID,42      p.UID_PWODecisionMethod43    FROM @DBQueueCurrent c44    JOIN AttestationCase ac45      ON c.UID_Parameter = ac.UID_AttestationCase46    JOIN AttestationPolicy p47      ON ac.UID_AttestationPolicy = p.UID_AttestationPolicy48    SELECT @ElementCount = @@ROWCOUNT49    SELECT @ElementIndex = @@IDENTITY - @ElementCount +150    SELECT @ElementLast = @@IDENTITY51    WHILE @ElementIndex <= @ElementLast52    BEGIN53      SELECT54        TOP 1 @UID_AttestationCase = bu.UID1,55        @GenProcID = bu.UID2,56        @UID_PWODecisionMethod = bu.UID357      FROM @ElementBuffer_reset bu58      WHERE59        bu.ElementIndex = @ElementIndex60      EXEC QER_PGIQERWorkingMethod @UID_PWODecisionMethod,61      '',62        @UID_QERWorkingMethod OUTPUT63      EXEC QBM_PSessionContextSet 'GenProcID',64        @GenProcID65      DELETE AttestationHelper66      WHERE67        UID_AttestationCase = @UID_AttestationCase68      EXEC QBM_PSessionContextSet 'GenProcID',69        @GenProcID70      UPDATE AttestationCase71      SET decisionlevel = 0,72      DisplayPersonHead = NULL,73      DateHead = GetUTCDate(),74      ReasonHead = dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Procedure deferred due to changed decision.|'),75      XDateUpdated = GetUTCDate(),76      XUserUpdated = 'DBScheduler',77      UID_QERWorkingMethod = @UID_QERWorkingMethod78      WHERE79        UID_AttestationCase = @UID_AttestationCase80      SELECT @ElementIndex += 181    END82    DECLARE @ElementBuffer_history QBM_YCursorBuffer83    INSERT INTO @ElementBuffer_history(UID1,84    UID2,85    UID3)86    SELECT87      p.uid_parameter,88      newid(),89      p.GenProcID90    FROM @DBQueueCurrent p91    SELECT @ElementCount = @@ROWCOUNT92    SELECT @ElementIndex = @@IDENTITY - @ElementCount +193    SELECT @ElementLast = @@IDENTITY94    WHILE @ElementIndex <= @ElementLast95    BEGIN96      SELECT97        TOP 1 @UID_AttestationCase = bu.UID1,98        @UID_AttestationHistory = bu.UID2,99        @GenProcID = bu.UID3100      FROM @ElementBuffer_history bu101      WHERE102        bu.ElementIndex = @ElementIndex103      EXEC QBM_PSessionContextSet 'GenProcID',104        @GenProcID105      INSERT INTO AttestationHistory(DecisionType,106      IsDecisionBySystem,107      UID_AttestationHistory,108      UID_PersonHead,109      UID_AttestationCase,110      DisplayPersonHead,111      ReasonHead,112      DateHead,113      XDateInserted,114      XDateUpdated,115      XUserInserted,116      XUserUpdated,117      DecisionLevel,118      XTouched,119      XObjectKey)120      SELECT121        'Reset',122        1,123        @UID_AttestationHistory,124        NULL,125        @UID_AttestationCase,126        dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Automatic system approval.|'),127        dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Procedure deferred due to changed decision.|'),128        GetUTCDate(),129        GetUTCDate(),130        GetUTCDate(),131        'sa',132        'sa',133        0,134        '',135        dbo.QBM_FCVElementToObjectKey1('AttestationHistory',136        'UID_AttestationHistory',137        @UID_AttestationHistory)138      SELECT @ElementIndex += 1139    END140    DECLARE @DBQueueElements_01 QBM_YDBQueueRaw141    INSERT INTO @DBQueueElements_01(object,142    subobject,143    genprocid)144    SELECT145      x.uid,146      NULL,147      x.GenProcID148    FROM(149    SELECT150      uid_parameter AS uid,151      p.GenProcID AS GenProcID152    FROM @DBQueueCurrent p) AS x153    EXEC QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper',154      @DBQueueElements_01155  END TRY156  BEGIN CATCH157    EXEC QBM_PSessionErrorAdd DEFAULT158    RAISERROR('',159    18,160    1)161      WITH NOWAIT162  END CATCH163  endLabel:164  EXEC QBM_PSessionContextSet 'GenProcID',165    @GenProcID_R166  RETURN167END
Open raw exported source
SQL · Raw26 lines
1    create   procedure ATT_ZAttestationReset (@SlotNumber int)  as begin  declare @UID_AttestationCase varchar(38) declare @UID_AttestationHistory2 varchar(38) declare @GenProcID varchar(38) declare @DebugLevel char(1) = 'W' declare @UID_QERWorkingMethod varchar(38) declare @UID_PWODecisionMethod 3varchar(38) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @ElementLast int declare @ElementCount int declare @ElementIndex int4 declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select5 UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount6 = 0 begin goto EndLabel end  declare @ElementBuffer_reset QBM_YCursorBuffer insert into @ElementBuffer_reset (UID1, UID2, UID3) select c.uid_Parameter7, c.GenProcID, p.UID_PWODecisionMethod from @DBQueueCurrent c join AttestationCase ac on c.UID_Parameter = ac.UID_AttestationCase join AttestationPolicy8 p on ac.UID_AttestationPolicy = p.UID_AttestationPolicy select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select 9@ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_AttestationCase = bu.UID1 , @GenProcID = bu.UID2 , @UID_PWODecisionMethod10 = bu.UID3 from @ElementBuffer_reset bu where bu.ElementIndex = @ElementIndex      exec QER_PGIQERWorkingMethod @UID_PWODecisionMethod, ''  , @UID_QERWorkingMethod11 output exec QBM_PSessionContextSet 'GenProcID', @GenProcID delete AttestationHelper where UID_AttestationCase = @UID_AttestationCase exec QBM_PSessionContextSet12 'GenProcID', @GenProcID update AttestationCase set decisionlevel = 0, DisplayPersonHead = null, DateHead = GetUTCDate(), ReasonHead = dbo.QBM_FCVStringTrimLDSPrefix13('#LDS#Procedure deferred due to changed decision.|'), XDateUpdated = GetUTCDate(), XUserUpdated = 'DBScheduler' , UID_QERWorkingMethod = @UID_QERWorkingMethod14 where UID_AttestationCase = @UID_AttestationCase select @ElementIndex += 1 end    declare @ElementBuffer_history QBM_YCursorBuffer insert into @ElementBuffer_history15 (UID1, UID2, UID3) select p.uid_parameter, newid(), p.GenProcID from @DBQueueCurrent p select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY16 - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_AttestationCase = bu.UID1 , @UID_AttestationHistory17 = bu.UID2 , @GenProcID = bu.UID3 from @ElementBuffer_history bu where bu.ElementIndex = @ElementIndex exec QBM_PSessionContextSet 'GenProcID', @GenProcID18 insert into AttestationHistory (DecisionType, IsDecisionBySystem, UID_AttestationHistory, UID_PersonHead, UID_AttestationCase, DisplayPersonHead, ReasonHead19, DateHead, XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, DecisionLevel, XTouched, XObjectKey) select 'Reset', 1, @UID_AttestationHistory, 20null, @UID_AttestationCase, dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Automatic system approval.|') , dbo.QBM_FCVStringTrimLDSPrefix('#LDS#Procedure deferred due to changed decision.|'21), GetUTCDate(), GetUTCDate(), GetUTCDate(), 'sa', 'sa', 0, '', dbo.QBM_FCVElementToObjectKey1('AttestationHistory', 'UID_AttestationHistory', @UID_AttestationHistory22) select @ElementIndex += 1 end  declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid23, null, x.GenProcID from ( select uid_parameter as uid, p.GenProcID as GenProcID from @DBQueueCurrent p ) as x  exec QBM_PDBQueueInsert_Bulk 'ATT-K-AttestationHelper'24, @DBQueueElements_01 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet25 'GenProcID', @GenProcID_R return end 26