dbo.ATT_ZAttestationReminder

1CREATE PROCEDURE ATT_ZAttestationReminder(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @UID_AttestationCase varchar(38)7  DECLARE @GenProcID varchar(38)8  DECLARE @UID_AttestationHelper varchar(38)9  DECLARE @UID_PersonHead varchar(38)10  DECLARE @MinutesReminder int11  DECLARE @MailExists int12  DECLARE @ObjectkeyForMail varchar(138)13  DECLARE @WhereclauseForMail nvarchar(1000)14  DECLARE @CurrentDecisionLevel int15  DECLARE @SubLevelNumberString varchar(16)16  DECLARE @XUser nvarchar(64) = object_name(@@procid)17  DECLARE @MaxDate2200 datetime = '2200-01-01'18  DECLARE @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('')19  DECLARE @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser')20  DECLARE @ElementLast int21  DECLARE @ElementCount int22  DECLARE @ElementIndex int23  DECLARE @AttestationCasesReminder ATT_YAttestationCase24  DECLARE @AttestationCasesAutomatic ATT_YAttestationCase25  DECLARE @SQLcmd nvarchar(1000)26  DECLARE @AbortMethod nvarchar(64)27  DECLARE @BasisObjectKey varchar(138)28  DECLARE @DecisionLevel int29  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent30  DECLARE @Helper QER_YPWOHelperReminder31  BEGIN TRY32    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,33    UID_Parameter,34    UID_SubParameter,35    GenProcID)36    SELECT37      UID_DialogDBQueue,38      UID_Parameter,39      UID_SubParameter,40      GenProcID41    FROM QBMDBQueueCurrent cu42      WITH(readpast)43    WHERE44      cu.SlotNumber = @SlotNumber45    IF @@rowcount = 046    BEGIN47      GOTO EndLabel48    END49    INSERT INTO @AttestationCasesAutomatic(UID_AttestationCase,50    ObjectKeyBase,51    UID_AttestationPolicy,52    GenProcID,53    XObjectKey,54    UID_QERWorkingMethod,55    IsClosed,56    DecisionLevel,57    XDateInserted)58    SELECT59      DISTINCT ac.UID_AttestationCase,60      ac.ObjectKeyBase,61      ac.UID_AttestationPolicy,62      cu.GenProcID,63      ac.XObjectKey,64      ac.UID_QERWorkingMethod,65      ac.IsClosed,66      ac.DecisionLevel,67      XDateInserted68    FROM @DBQueueCurrent cu69    JOIN AttestationCase ac70      WITH(readpast)71      ON cu.UID_Parameter = ac.UID_AttestationCase72    JOIN QERWorkingStep ws73      ON ws.UID_QERWorkingMethod = ac.UID_QERWorkingMethod74    WHERE75      ws.MinutesAutomaticDecision > 076    INSERT INTO @Helper(UID_HelperKey,77    NextReminder,78    NextAutomaticDecision,79    UID_ElementKey,80    UID_PersonHead,81    UID_QERWorkingStep,82    Decision,83    LevelNumber,84    UID_PWODecisionRule,85    XObjectKey,86    RulerLevel)87    SELECT88      h.UID_AttestationHelper,89      h.NextReminder,90      h.NextAutomaticDecision,91      ac.UID_AttestationCase,92      h.UID_PersonHead,93      h.UID_QERWorkingStep,94      h.Decision,95      h.LevelNumber,96      h.UID_PWODecisionRule,97      h.XObjectKey,98      h.RulerLevel99    FROM @AttestationCasesAutomatic ac100    JOIN AttestationHelper h101      WITH(readpast)102      ON ac.UID_AttestationCase = h.UID_AttestationCase103    SELECT TOP 1 @GenProcID = acs.GenProcID104    FROM @AttestationCasesAutomatic acs105    EXEC QBM_PSessionContextSet 'GenProcID',106      @GenProcID107    EXEC QBM_PSessionContextSet 'XUser',108      @XUser109    UPDATE @Helper110    SET NextAutomaticDecision = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(),111    ws.MinutesAutomaticDecision,112    h.UID_PersonHead),113    IsToUpdateNextAutomaticDec = 1114    FROM @Helper h115    JOIN QERWorkingStep ws116      ON h.UID_QERWorkingStep = ws.UID_QERWorkingStep117    JOIN AttestationCase pwo118      WITH(readpast)119      ON h.UID_ElementKey = pwo.UID_AttestationCase120    JOIN PWODecisionRule r121      ON ws.UID_PWODecisionRule = r.UID_PWODecisionRule122    WHERE123      ws.MinutesAutomaticDecision > 0 AND h.NextAutomaticDecision = @MaxDate2200 AND isnull(decision,124    '') = '' AND r.UID_Task IS NULL AND h.levelnumber = pwo.DecisionLevel125    SELECT TOP 1 @GenProcID = acs.GenProcID126    FROM @AttestationCasesAutomatic acs127    EXEC QBM_PSessionContextSet 'GenProcID',128      @GenProcID129    UPDATE @Helper130    SET NextAutomaticDecision = @MaxDate2200,131    IsToUpdateNextAutomaticDec = 1132    FROM @Helper h133    JOIN QERWorkingStep ws134      ON h.UID_QERWorkingStep = ws.UID_QERWorkingStep135    JOIN AttestationCase pwo136      WITH(readpast)137      ON h.UID_ElementKey = pwo.UID_AttestationCase138    JOIN PWODecisionRule r139      ON ws.UID_PWODecisionRule = r.UID_PWODecisionRule140    WHERE141      h.NextAutomaticDecision < @MaxDate2200 AND(ws.MinutesAutomaticDecision = 0 OR isnull(decision, '') > '' OR h.levelnumber <> pwo.DecisionLevel OR142    r.UID_Task > ' ')143    DECLARE @ElementBuffer_action QBM_YCursorBuffer144    INSERT INTO @ElementBuffer_action(UID1,145    UID2,146    ObjectKey1,147    Ident1,148    Int1,149    Ident2)150    SELECT151      DISTINCT acs.UID_AttestationCase,152      acs.GenProcID,153      acs.XObjectKey,154      s.AutomaticDecision,155      acs.DecisionLevel,156      min(dbo.QBM_FCVIntToString(hp.SubLevelNumber))157    FROM @AttestationCasesAutomatic acs158    JOIN(159    SELECT160      h.UID_ElementKey AS UID_AttestationCase,161      h.uid_QERWorkingStep162    FROM @Helper h163    JOIN @AttestationCasesAutomatic pwo164      ON h.UID_ElementKey = pwo.uid_AttestationCase AND h.levelnumber = pwo.DecisionLevel165    JOIN QERWorkingStep ws166      ON ws.UID_QERWorkingStep = h.UID_QERWorkingStep167    WHERE168      h.NextAutomaticDecision < @MaxDate2200 AND h.RulerLevel <> 2169    GROUP BY h.UID_ElementKey,170    h.UID_QERWorkingStep171    HAVING max(h.NextAutomaticDecision) < getutcdate()) AS x172      ON acs.UID_AttestationCase = x.UID_AttestationCase AND acs.IsClosed = 0173    JOIN QERWorkingStep s174      ON s.uid_QERWorkingStep = x.UID_QERWorkingStep175    JOIN AttestationHelper hp176      ON hp.UID_AttestationCase = acs.UID_AttestationCase AND hp.UID_QERWorkingStep = s.UID_QERWorkingStep177    WHERE178      dbo.QBM_FGIJobCreatedExists(acs.XObjectKey) = 0 AND dbo.ATT_FGIATTDecisionPossible(acs.UID_AttestationCase,179    hp.LevelNumber,180    hp.SubLevelNumber) = 1181    GROUP BY acs.UID_AttestationCase,182    acs.GenProcID,183    acs.XObjectKey,184    s.AutomaticDecision,185    acs.DecisionLevel186    SELECT @ElementCount = @@ROWCOUNT187    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1188    SELECT @ElementLast = @@IDENTITY189    WHILE @ElementIndex <= @ElementLast190    BEGIN191      SELECT192        TOP 1 @uid_AttestationCase = bu.UID1,193        @GenProcID = bu.UID2,194        @BasisObjectKey = bu.ObjectKey1,195        @AbortMethod = bu.Ident1,196        @DecisionLevel = bu.Int1,197        @SubLevelNumberString = bu.Ident2198      FROM @ElementBuffer_action bu199      WHERE200        bu.ElementIndex = @ElementIndex201      SELECT202        @SQLcmd = N 'uid_AttestationCase = ''' + rtrim(@uid_AttestationCase) + N ''' and DecisionLevel = ' + STR(@DecisionLevel)203      IF @AbortMethod IN('TRUE',204      'FALSE')205      BEGIN206        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',207          @whereclause = @SQLcmd,208          @save = 1,209          @MethodName = 'MakeDecision',210          @GenProcID = @GenprocID,211          @ObjectKeysAffected = DEFAULT,212          @param1 = 'sa',213          @Param2 = @AbortMethod,214          @Param3 = '#LDS#Automatic system approval: Decision due to timeout.|',215          @Param5 = @SubLevelNumberString,216          @Retries = 3,217          @isToFreezeOnError = 1,218          @BasisObjectKey = @BasisObjectKey,219          @checkForExisting = 1,220          @WhereClauseAdditional = ' IsClosed = 0 '221      END222      IF @AbortMethod IN('ESCALATE') AND NOT EXISTS(223        SELECT TOP 1 1224        FROM @ElementBuffer_action bu225        WHERE226          bu.ElementIndex < @ElementIndex AND bu.UID1 = @UID_AttestationCase)227      BEGIN228        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',229          @whereclause = @SQLcmd,230          @save = 1,231          @MethodName = 'ESCALATE',232          @GenProcID = @GenprocID,233          @ObjectKeysAffected = DEFAULT,234          @param1 = 'sa',235          @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to timeout.|',236          @Retries = 3,237          @isToFreezeOnError = 1,238          @BasisObjectKey = @BasisObjectKey,239          @checkForExisting = 1,240          @WhereClauseAdditional = ' IsClosed = 0 '241      END242      IF @AbortMethod IN('Abort') AND NOT EXISTS(243        SELECT TOP 1 1244        FROM @ElementBuffer_action bu245        WHERE246          bu.ElementIndex < @ElementIndex AND bu.UID1 = @UID_AttestationCase)247      BEGIN248        EXEC QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase',249          @whereclause = @SQLcmd,250          @save = 1,251          @MethodName = 'Abort',252          @GenProcID = @GenprocID,253          @ObjectKeysAffected = DEFAULT,254          @param1 = '#LDS#Automatic system approval: "Abort" due to timeout.|',255          @Retries = 3,256          @isToFreezeOnError = 1,257          @BasisObjectKey = @BasisObjectKey,258          @checkForExisting = 1,259          @WhereClauseAdditional = ' IsClosed = 0 '260      END261      SELECT @ElementIndex += 1262    END263    INSERT INTO @AttestationCasesReminder(UID_AttestationCase,264    ObjectKeyBase,265    UID_AttestationPolicy,266    GenProcID,267    XObjectKey,268    UID_QERWorkingMethod,269    IsClosed,270    DecisionLevel,271    XDateInserted)272    SELECT273      DISTINCT ac.UID_AttestationCase,274      ac.ObjectKeyBase,275      ac.UID_AttestationPolicy,276      cu.GenProcID,277      ac.XObjectKey,278      ac.UID_QERWorkingMethod,279      ac.IsClosed,280      ac.DecisionLevel,281      ac.XDateInserted282    FROM @DBQueueCurrent cu283    JOIN AttestationCase ac284      WITH(readpast)285      ON cu.UID_Parameter = ac.UID_AttestationCase286    JOIN QERWorkingStep ws287      ON ws.UID_QERWorkingMethod = ac.UID_QERWorkingMethod AND ac.DecisionLevel = ws.levelnumber CROSS apply dbo.ATT_FTATTDecisionPossible(ac.UID_AttestationCase288    ,289    ws.LevelNumber,290    ws.SubLevelNumber) dp291    JOIN AttestationHelper ah292      WITH(readpast)293      ON ac.UID_AttestationCase = ah.UID_AttestationCase AND ac.decisionlevel = ah.levelnumber294    JOIN PWODecisionRule dr295      ON ah.UID_PWODecisionRule = dr.UID_PWODecisionRule296    WHERE297      ac.IsClosed = 0 AND dp.DecisionPossible = 1 AND dr.UID_Task IS NULL AND ws.MinutesReminder > 0298    INSERT INTO @Helper(UID_HelperKey,299    NextReminder,300    NextAutomaticDecision,301    UID_ElementKey,302    UID_PersonHead,303    UID_QERWorkingStep,304    Decision,305    LevelNumber,306    XObjectKey,307    UID_PWODecisionRule,308    RulerLevel)309    SELECT310      h.UID_AttestationHelper,311      h.NextReminder,312      h.NextAutomaticDecision,313      ac.UID_AttestationCase,314      h.UID_PersonHead,315      h.UID_QERWorkingStep,316      h.Decision,317      h.LevelNumber,318      h.XObjectKey,319      h.UID_PWODecisionRule,320      h.RulerLevel321    FROM @AttestationCasesReminder ac322    JOIN AttestationHelper h323      WITH(readpast)324      ON ac.UID_AttestationCase = h.UID_AttestationCase325    WHERE326      NOT EXISTS(327    SELECT TOP 1 1328    FROM @Helper e329    WHERE330      e.UID_HelperKey = h.UID_AttestationHelper)331    SELECT TOP 1 @GenProcID = acs.GenProcID332    FROM @AttestationCasesReminder acs333    EXEC QBM_PSessionContextSet 'GenProcID',334      @GenProcID335    UPDATE @Helper336    SET NextReminder = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(),337    ws.MinutesReminder,338    h.UID_PersonHead),339    IsToUpdateNextReminder = 1340    FROM @AttestationCasesReminder pwo341    JOIN @Helper h342      ON h.UID_ElementKey = pwo.UID_AttestationCase343    JOIN QERWorkingStep ws344      ON h.UID_QERWorkingStep = ws.UID_QERWorkingStep345    JOIN PWODecisionRule r346      ON h.UID_PWODecisionRule = r.UID_PWODecisionRule347    WHERE348      ws.MinutesReminder > 0 AND isnull(h.NextReminder,349    @MaxDate2200) = @MaxDate2200 AND isnull(h.Decision,350    '') IN('',351    'Q') AND r.UID_Task IS NULL AND h.levelnumber = pwo.DecisionLevel352    EXEC QBM_PSessionContextSet 'GenProcID',353      @GenProcID354    UPDATE @Helper355    SET NextReminder = @MaxDate2200,356    IsToUpdateNextReminder = 1357    FROM @Helper h358    JOIN @AttestationCasesReminder acs359      ON h.UID_ElementKey = acs.UID_AttestationCase360    JOIN QERWorkingStep ws361      ON h.UID_QERWorkingStep = ws.UID_QERWorkingStep362    JOIN AttestationCase pwo363      WITH(readpast)364      ON h.UID_ElementKey = pwo.UID_AttestationCase365    JOIN PWODecisionRule r366      ON h.UID_PWODecisionRule = r.UID_PWODecisionRule367    WHERE368      h.NextReminder < @MaxDate2200 AND(ws.MinutesReminder = 0 OR isnull(h.Decision, '') NOT IN('', 'Q') OR h.levelnumber <> pwo.DecisionLevel OR369    r.UID_Task > ' ')370    UPDATE @Helper371    SET NextReminder = @MaxDate2200,372    IsToUpdateNextReminder = 1373    FROM @Helper h374    JOIN @AttestationCasesReminder pwo375      ON h.UID_ElementKey = pwo.UID_AttestationCase376    WHERE377      h.NextReminder < @MaxDate2200 AND isnull(h.Decision,378    '') = '' AND h.levelnumber = pwo.DecisionLevel AND EXISTS(379    SELECT TOP 1 1380    FROM @Helper hq381    WHERE382      hq.UID_ElementKey = h.UID_ElementKey AND hq.Decision = 'Q' AND hq.LevelNumber = h.LevelNumber)383    DECLARE @ElementBuffer_remind QBM_YCursorBuffer384    INSERT INTO @ElementBuffer_remind(UID1,385    UID2,386    UID3,387    Int1,388    Bit1,389    ObjectKey1,390    Int2,391    UID4)392    SELECT393      h.UID_HelperKey,394      pwo.GenProcID,395      pe.UID_person,396      ws.MinutesReminder,397      sign(len(isnull(ws.UID_DialogRichMailReminder, ''))),398      h.XObjectKey,399      pwo.decisionlevel,400      pwo.UID_AttestationCase401    FROM @Helper h402    JOIN @AttestationCasesReminder pwo403      ON h.UID_ElementKey = pwo.UID_AttestationCase AND h.levelnumber = pwo.DecisionLevel404    JOIN person pe405      ON pe.UID_person = h.UID_PersonHead406    JOIN QERWorkingStep ws407      ON h.UID_QERWorkingStep = ws.UID_QERWorkingStep408    WHERE409      h.NextReminder < getutcdate() AND dbo.QBM_FGIJobCreatedExists(h.XObjectKey) = 0 AND h.RulerLevel < 2410    SELECT @ElementCount = @@ROWCOUNT411    SELECT @ElementIndex = @@IDENTITY - @ElementCount +1412    SELECT @ElementLast = @@IDENTITY413    WHILE @ElementIndex <= @ElementLast414    BEGIN415      SELECT416        TOP 1 @UID_AttestationHelper = bu.UID1,417        @GenProcID = bu.UID2,418        @UID_PersonHead = bu.UID3,419        @MinutesReminder = bu.Int1,420        @MailExists = bu.Bit1,421        @ObjectkeyForMail = bu.ObjectKey1,422        @CurrentDecisionLevel = bu.Int2,423        @UID_AttestationCase = bu.UID4424      FROM @ElementBuffer_remind bu425      WHERE426        bu.ElementIndex = @ElementIndex427      EXEC QBM_PSessionContextSet 'GenProcID',428        @GenProcID429      EXEC QBM_PSessionContextSet 'XUser',430        @XUser431      UPDATE @Helper432      SET NextReminder = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(),433      @MinutesReminder,434      @UID_PersonHead),435      IsToUpdateNextReminder = 1436      WHERE437        UID_HelperKey = @UID_AttestationHelper438      IF @MailExists > 0439      BEGIN440        SELECT441          @WhereclauseForMail = N 'XObjectKey = ''' + @ObjectkeyForMail + N '''' + N ' and exists (select top 1 1 from AttestationCase where UID_AttestationCase  = '''442          + @UID_AttestationCase + ''' and decisionlevel = ' + str(@CurrentDecisionLevel) + ' )'443        EXEC QBM_PJobCreate_HOFireEvent_B 'AttestationHelper',444          @WhereclauseForMail,445        'Remind',446          @GenProcID,447          @AdditionalObjectKeysAffected = DEFAULT,448          @checkForExisting = 1,449          @BasisObjectKey = @ObjectkeyForMail450      END451      SELECT @ElementIndex += 1452    END453    UPDATE AttestationHelper454    SET NextReminder = CASE h.IsToUpdateNextReminder455    WHEN 1 THEN456    h.NextReminder457    ELSE t.NextReminder458    END,459    NextAutomaticDecision = CASE h.IsToUpdateNextAutomaticDec460    WHEN 1 THEN461    h.NextAutomaticDecision462    ELSE t.NextAutomaticDecision463    END464    FROM AttestationHelper t465    JOIN @Helper h466      ON t.UID_AttestationHelper = h.UID_HelperKey467    WHERE468      h.IsToUpdateNextReminder = 1 OR h.IsToUpdateNextAutomaticDec = 1469  END TRY470  BEGIN CATCH471    EXEC QBM_PSessionErrorAdd DEFAULT472    RAISERROR('',473    18,474    1)475      WITH NOWAIT476  END CATCH477  endLabel:478  EXEC QBM_PSessionContextSet 'GenProcID',479    @GenProcID_R480  EXEC QBM_PSessionContextSet 'XUser',481    @XUser_R482  RETURN483END