dbo.ATT_ZAttestationCheckValid

1CREATE PROCEDURE ATT_ZAttestationCheckValid(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @SQLcmd nvarchar(1000)7  DECLARE @UID_AttestationCase varchar(38)8  DECLARE @GenProcID varchar(38)9  DECLARE @BasisObjectKey varchar(138)10  DECLARE @AbortMethod nvarchar(64)11  DECLARE @UID_Task varchar(38)12  DECLARE @EscalateIfNoApprover BIT13  DECLARE @DecisionLevel int14  DECLARE @TableName varchar(30)15  DECLARE @Cmd nvarchar(max)16  DECLARE @WaitingForEx_30965 varchar(32) = 'waiting for EX'17  DECLARE @AttCaseObjectMissing QBM_YParameterList18  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')19  DECLARE @ElementLast int20  DECLARE @ElementCount int21  DECLARE @ElementIndex int22  DECLARE @AttestationCaseShadow ATT_YAttestationCase23  DECLARE @DBQueueElements_spezial QBM_YDBQueueRaw24  BEGIN TRY25    INSERT INTO @AttestationCaseShadow(UID_AttestationCase,26    ObjectKeyBase,27    UID_AttestationPolicy,28    GenProcID,29    XObjectKey,30    UID_QERWorkingMethod,31    IsClosed,32    DecisionLevel,33    XDateInserted)34    SELECT35      ac.UID_AttestationCase,36      ac.ObjectKeyBase,37      ac.UID_AttestationPolicy,38      cu.GenProcID,39      ac.XObjectKey,40      ac.UID_QERWorkingMethod,41      ac.IsClosed,42      ac.DecisionLevel,43      XDateInserted44    FROM QBMDBQueueCurrent cu45      WITH(readpast)46    JOIN AttestationCase ac47      ON cu.UID_Parameter = ac.UID_AttestationCase AND cu.SlotNumber= @SlotNumber AND ac.IsUnderConstruction = 048    DECLARE @ElementBuffer_missing QBM_YCursorBuffer49    INSERT INTO @ElementBuffer_missing(UID1)50    SELECT51      DISTINCT CASE52      WHEN t.TableName IS NULL THEN53      'DialogHistoryDB'54    ELSE t.TableName55    END AS TableName56    FROM(57    SELECT58      DISTINCT dbo.QBM_FCVObjectkeyToElement('TableName', acs.ObjectKeyBase) AS TableName59    FROM @AttestationCaseShadow acs) AS y60    LEFT61    OUTER62    JOIN DialogTable t63      WITH(readpast)64      ON y.TableName = t.TableName65    SELECT @ElementCount = @@ROWCOUNT66    SELECT @ElementIndex = @@IDENTITY - @ElementCount +167    SELECT @ElementLast = @@IDENTITY68    WHILE @ElementIndex <= @ElementLast69    BEGIN70      SELECT TOP 1 @TableName = bu.UID171      FROM @ElementBuffer_missing bu72      WHERE73        bu.ElementIndex = @ElementIndex74      SELECT75        @Cmd = '76	select ac.UID_AttestationCase, cu.GenProcID, ac.XObjectKey77			from QBMDBQueueCurrent cu  with (readpast) join AttestationCase ac  with (nolock) on cu.UID_Parameter = ac.UID_AttestationCase78														and ac.ObjectKeyBase like ''<Key><T>'79        + @TableName + '</T>%''80								left outer join ' + @TableName + ' x  with (nolock) on x.XObjectKey = ac.ObjectKeyBase81		where cu.SlotNumber = '82        + str(@Slotnumber) + '83		-- 3576084		and ac.IsUnderConstruction = 085		and x.XObjectKey is null86		'87      INSERT INTO @AttCaseObjectMissing(Parameter1,88      Parameter2,89      Parameter3)90      EXEC sp_executesql @cmd91      SELECT @ElementIndex += 192    END93    DECLARE @ElementBuffer_close QBM_YCursorBuffer94    INSERT INTO @ElementBuffer_close(UID1,95    UID2,96    ObjectKey1)97    SELECT98      DISTINCT ac.Parameter1,99      ac.Parameter2,100      ac.Parameter3101    FROM @AttCaseObjectMissing ac102    SELECT @ElementCount = @@ROWCOUNT103    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1104    SELECT @ElementLast = @@IDENTITY105    WHILE @ElementIndex <= @ElementLast106    BEGIN107      SELECT108        TOP 1 @UID_AttestationCase = bu.UID1,109        @GenProcID = bu.UID2,110        @BasisObjectKey = bu.ObjectKey1111      FROM @ElementBuffer_close bu112      WHERE113        bu.ElementIndex = @ElementIndex114      SELECT @SQLcmd = N 'UID_AttestationCase = ''' + rtrim(@UID_AttestationCase) + N ''''115      EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',116        @whereclause = @SQLcmd,117        @save = 1,118        @MethodName = 'Abort',119        @GenProcID = @GenprocID,120        @ObjectKeysAffected = DEFAULT,121        @param1 = '#LDS#Automatic system approval: object to attest does not exist.|',122        @Retries = 3,123        @isToFreezeOnError = 1,124        @BasisObjectKey = @BasisObjectKey,125        @checkForExisting = 1,126        @WhereClauseAdditional = ' IsClosed = 0 '127      DELETE @AttestationCaseShadow128      WHERE129        UID_AttestationCase = @UID_AttestationCase130      SELECT @ElementIndex += 1131    END132    DECLARE @ElementBuffer_deactive QBM_YCursorBuffer133    INSERT INTO @ElementBuffer_deactive(UID1,134    UID2,135    ObjectKey1)136    SELECT137      DISTINCT acs.UID_AttestationCase,138      acs.GenProcID,139      acs.XObjectKey140    FROM @AttestationCaseShadow acs141    JOIN attestationpolicy ap142      ON ap.UID_AttestationPolicy = acs.UID_AttestationPolicy143    WHERE144      ap.IsInactive = 1145    SELECT @ElementCount = @@ROWCOUNT146    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1147    SELECT @ElementLast = @@IDENTITY148    WHILE @ElementIndex <= @ElementLast149    BEGIN150      SELECT151        TOP 1 @UID_AttestationCase = bu.UID1,152        @GenProcID = bu.UID2,153        @BasisObjectKey = bu.ObjectKey1154      FROM @ElementBuffer_deactive bu155      WHERE156        bu.ElementIndex = @ElementIndex157      SELECT @SQLcmd = N 'UID_AttestationCase = ''' + rtrim(@UID_AttestationCase) + N ''''158      EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',159        @whereclause = @SQLcmd,160        @save = 1,161        @MethodName = 'Abort',162        @GenProcID = @GenprocID,163        @ObjectKeysAffected = DEFAULT,164        @param1 = '#LDS#Automatic system approval:  Policy is inactive.|',165        @Retries = 3,166        @isToFreezeOnError = 1,167        @BasisObjectKey = @BasisObjectKey,168        @checkForExisting = 1,169        @WhereClauseAdditional = ' IsClosed = 0 '170      DELETE @AttestationCaseShadow171      WHERE172        UID_AttestationCase = @UID_AttestationCase173      SELECT @ElementIndex += 1174    END175    DECLARE @ElementBuffer_noDecider QBM_YCursorBuffer176    INSERT INTO @ElementBuffer_noDecider(UID1,177    UID2,178    ObjectKey1,179    Bit1,180    Int1)181    SELECT182      DISTINCT acs.UID_AttestationCase,183      acs.GenProcID,184      acs.XObjectKey,185      ws.EscalateIfNoApprover,186      acs.DecisionLevel187    FROM @AttestationCaseShadow acs188    JOIN QERWorkingStep ws189      ON ws.UID_QERWorkingMethod = acs.UID_QERWorkingMethod AND acs.IsClosed = 0 AND acs.DecisionLevel = ws.levelnumber190    WHERE191      NOT EXISTS(192    SELECT TOP 1 1193    FROM AttestationHelper ah194    WHERE195      acs.UID_AttestationCase = ah.UID_AttestationCase AND ws.LevelNumber = ah.LevelNumber AND ws.SubLevelNumber = ah.SubLevelNumber AND ws.UID_PWODecisionRule196    = ah.UID_PWODecisionRule AND ah.RulerLevel IN(0, 1))197    SELECT @ElementCount = @@ROWCOUNT198    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1199    SELECT @ElementLast = @@IDENTITY200    WHILE @ElementIndex <= @ElementLast201    BEGIN202      SELECT203        TOP 1 @UID_AttestationCase = bu.UID1,204        @GenProcID = bu.UID2,205        @BasisObjectKey = bu.ObjectKey1,206        @EscalateIfNoApprover = bu.Bit1,207        @DecisionLevel = bu.Int1208      FROM @ElementBuffer_noDecider bu209      WHERE210        bu.ElementIndex = @ElementIndex211      IF @EscalateIfNoApprover = 0212      BEGIN213        SELECT @SQLcmd = N 'UID_AttestationCase = ''' + rtrim(@UID_AttestationCase) + N ''''214        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',215          @whereclause = @SQLcmd,216          @save = 1,217          @MethodName = 'Abort',218          @GenProcID = @GenprocID,219          @ObjectKeysAffected = DEFAULT,220          @param1 = '#LDS#Automatic system approval:  No approver available.|',221          @Retries = 3,222          @isToFreezeOnError = 1,223          @BasisObjectKey = @BasisObjectKey,224          @checkForExisting = 1,225          @WhereClauseAdditional = ' IsClosed = 0 '226      END227      ELSE228      BEGIN229        SELECT230          @SQLcmd = N 'uid_AttestationCase = ''' + rtrim(@uid_AttestationCase) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)231        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',232          @whereclause = @SQLcmd,233          @save = 1,234          @MethodName = 'ESCALATE',235          @GenProcID = @GenprocID,236          @ObjectKeysAffected = DEFAULT,237          @param1 = 'sa',238          @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to no approver available.|',239          @Retries = 3,240          @isToFreezeOnError = 1,241          @BasisObjectKey = @BasisObjectKey,242          @checkForExisting = 1,243          @WhereClauseAdditional = ' IsClosed = 0 '244      END245      DELETE @AttestationCaseShadow246      WHERE247        UID_AttestationCase = @UID_AttestationCase248      SELECT @ElementIndex += 1249    END250    DECLARE @ElementBuffer_lessDecider QBM_YCursorBuffer251    INSERT INTO @ElementBuffer_lessDecider(UID1,252    UID2,253    ObjectKey1,254    Bit1,255    Int1)256    SELECT257      DISTINCT acs.UID_AttestationCase,258      acs.GenProcID,259      acs.XObjectKey,260      ws.EscalateIfNoApprover,261      acs.DecisionLevel262    FROM @AttestationCaseShadow acs263    JOIN QERWorkingStep ws264      ON ws.UID_QERWorkingMethod = acs.UID_QERWorkingMethod AND acs.IsClosed = 0 AND acs.DecisionLevel = ws.levelnumber CROSS apply dbo.ATT_FTATTDecisionPossible265    (acs.UID_AttestationCase,266    ws.LevelNumber,267    ws.SubLevelNumber) dp268    WHERE269      dp.DecisionPossible = -1 AND acs.isclosed = 0270    SELECT @ElementCount = @@ROWCOUNT271    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1272    SELECT @ElementLast = @@IDENTITY273    WHILE @ElementIndex <= @ElementLast274    BEGIN275      SELECT276        TOP 1 @UID_AttestationCase = bu.UID1,277        @GenProcID = bu.UID2,278        @BasisObjectKey = bu.ObjectKey1,279        @EscalateIfNoApprover = bu.Bit1,280        @DecisionLevel = bu.Int1281      FROM @ElementBuffer_lessDecider bu282      WHERE283        bu.ElementIndex = @ElementIndex284      IF @EscalateIfNoApprover = 0285      BEGIN286        SELECT @SQLcmd = N 'UID_AttestationCase = ''' + rtrim(@UID_AttestationCase) + N ''''287        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',288          @whereclause = @SQLcmd,289          @save = 1,290          @MethodName = 'Abort',291          @GenProcID = @GenprocID,292          @ObjectKeysAffected = DEFAULT,293          @param1 = '#LDS#Automatic system approval: Not enough approvers available.|',294          @Retries = 3,295          @isToFreezeOnError = 1,296          @BasisObjectKey = @BasisObjectKey,297          @checkForExisting = 1,298          @WhereClauseAdditional = ' IsClosed = 0 '299      END300      ELSE301      BEGIN302        SELECT303          @SQLcmd = N 'uid_AttestationCase = ''' + rtrim(@uid_AttestationCase) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)304        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',305          @whereclause = @SQLcmd,306          @save = 1,307          @MethodName = 'ESCALATE',308          @GenProcID = @GenprocID,309          @ObjectKeysAffected = DEFAULT,310          @param1 = 'sa',311          @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to not enough approvers available.|',312          @Retries = 3,313          @isToFreezeOnError = 1,314          @BasisObjectKey = @BasisObjectKey,315          @checkForExisting = 1,316          @WhereClauseAdditional = ' IsClosed = 0 '317      END318      DELETE @AttestationCaseShadow319      WHERE320        UID_AttestationCase = @UID_AttestationCase321      SELECT @ElementIndex += 1322    END323    DECLARE @ElementBuffer_decidePos QBM_YCursorBuffer324    INSERT INTO @ElementBuffer_decidePos(UID1)325    SELECT326      DISTINCT dr.UID_Task327    FROM @AttestationCaseShadow acs328    JOIN AttestationHelper ah329      ON acs.UID_AttestationCase = ah.UID_AttestationCase AND acs.decisionlevel = ah.levelnumber330    JOIN pwodecisionrule dr331      ON ah.UID_PWODecisionRule = dr.UID_PWODecisionRule332    WHERE333      dr.UID_Task > ' ' AND acs.isclosed = 0 AND NOT(dr.UID_Task = 'ATT-K-AttestationMakeDecisionEX' AND isnull(ah.ReasonHead,334    '') = @WaitingForEx_30965)335    SELECT @ElementCount = @@ROWCOUNT336    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1337    SELECT @ElementLast = @@IDENTITY338    WHILE @ElementIndex <= @ElementLast339    BEGIN340      SELECT TOP 1 @UID_Task = bu.UID1341      FROM @ElementBuffer_decidePos bu342      WHERE343        bu.ElementIndex = @ElementIndex344      DELETE @DBQueueElements_spezial345      INSERT INTO @DBQueueElements_spezial(object,346      subobject,347      genprocid)348      SELECT349        x.uid,350        NULL,351        x.GenProcID352      FROM(353      SELECT354        acs.UID_AttestationCase AS uid,355        acs.GenProcID AS GenProcID356      FROM @AttestationCaseShadow acs357      JOIN AttestationHelper ah358        ON acs.UID_AttestationCase = ah.UID_AttestationCase AND acs.decisionlevel = ah.levelnumber359      JOIN pwodecisionrule dr360        ON ah.UID_PWODecisionRule = dr.UID_PWODecisionRule361      WHERE362        acs.isclosed = 0 AND dr.UID_Task = @UID_Task AND NOT(dr.UID_Task = 'ATT-K-AttestationMakeDecisionEX' AND isnull(ah.ReasonHead,363      '') = @WaitingForEx_30965)) AS x364      EXEC QBM_PDBQueueInsert_Bulk @UID_Task,365        @DBQueueElements_spezial366      DELETE @AttestationCaseShadow367      WHERE368        UID_AttestationCase IN(369      SELECT e.Object370      FROM @DBQueueElements_spezial e)371      SELECT @ElementIndex += 1372    END373    DECLARE @ElementBuffer_TimeCheck QBM_YCursorBuffer374    INSERT INTO @ElementBuffer_TimeCheck(UID1,375    UID2,376    ObjectKey1,377    Ident1)378    SELECT379      DISTINCT acs.UID_AttestationCase,380      acs.genprocID,381      acs.XObjectKey,382      'Abort'383    FROM @AttestationCaseShadow acs384    JOIN QERWorkingMethod wm385      ON acs.UID_QERWorkingMethod = wm.UID_QERWorkingMethod386    WHERE387      dateadd(dd,388    wm.DaysToAbort,389    acs.Xdateinserted) < getutcdate() AND isnull(wm.DaysToAbort,390    0) > 0 AND acs.isClosed = 0 AND dbo.QBM_FGIJobCreatedExists(acs.XObjectKey) = 0391    SELECT @ElementCount = @@ROWCOUNT392    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1393    SELECT @ElementLast = @@IDENTITY394    WHILE @ElementIndex <= @ElementLast395    BEGIN396      SELECT397        TOP 1 @uid_AttestationCase = bu.UID1,398        @GenProcID = bu.UID2,399        @BasisObjectKey = bu.ObjectKey1,400        @AbortMethod = bu.Ident1401      FROM @ElementBuffer_TimeCheck bu402      WHERE403        bu.ElementIndex = @ElementIndex404      SELECT @SQLcmd = N 'uid_AttestationCase = ''' + rtrim(@uid_AttestationCase) + N ''''405      EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',406        @whereclause = @SQLcmd,407        @save = 1,408        @MethodName = @AbortMethod,409        @GenProcID = @GenprocID,410        @ObjectKeysAffected = DEFAULT,411        @param1 = '#LDS#Automatic system approval: Number of days has been exceeded, as specified in decision method.|',412        @Retries = 3,413        @isToFreezeOnError = 1,414        @BasisObjectKey = @BasisObjectKey,415        @checkForExisting = 1,416        @WhereClauseAdditional = ' IsClosed = 0 '417      DELETE @AttestationCaseShadow418      WHERE419        UID_AttestationCase = @UID_AttestationCase420      SELECT @ElementIndex += 1421    END422  END TRY423  BEGIN CATCH424    EXEC QBM_PSessionErrorAdd DEFAULT425    RAISERROR('',426    18,427    1)428      WITH NOWAIT429  END CATCH430  endLabel:431  EXEC QBM_PSessionContextSet 'GenProcID',432    @GenProcID_R433  RETURN434END