dbo.ATT_ZAttestationReminder

1     create   procedure ATT_ZAttestationReminder (@SlotNumber int) as begin declare @UID_AttestationCase varchar(38) declare @GenProcID varchar(38
2) declare @UID_AttestationHelper varchar(38) declare @UID_PersonHead varchar(38) declare @MinutesReminder int declare @MailExists int declare @ObjectkeyForMail
3 varchar(138) declare @WhereclauseForMail nvarchar(1000) declare @CurrentDecisionLevel int declare @SubLevelNumberString varchar(16) declare @XUser nvarchar
4(64) = object_name(@@procid) declare @MaxDate2200 datetime = '2200-01-01' declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R
5 nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare @AttestationCasesReminder
6 ATT_YAttestationCase declare @AttestationCasesAutomatic ATT_YAttestationCase declare @SQLcmd nvarchar(1000) declare @AbortMethod nvarchar(64) declare 
7@BasisObjectKey varchar(138) declare @DecisionLevel int declare @DBQueueCurrent QBM_YDBQueueCurrent declare @Helper QER_YPWOHelperReminder BEGIN TRY insert
8 into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID
9 from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end insert into @AttestationCasesAutomatic
10(UID_AttestationCase, ObjectKeyBase, UID_AttestationPolicy , GenProcID, XObjectKey, UID_QERWorkingMethod, IsClosed, DecisionLevel, XDateInserted ) select
11 distinct ac.UID_AttestationCase, ac.ObjectKeyBase, ac.UID_AttestationPolicy , cu.GenProcID, ac.XObjectKey, ac.UID_QERWorkingMethod, ac.IsClosed, ac.DecisionLevel
12, XDateInserted from @DBQueueCurrent cu join AttestationCase ac with (readpast)on cu.UID_Parameter = ac.UID_AttestationCase join QERWorkingStep ws on ws.UID_QERWorkingMethod
13 = ac.UID_QERWorkingMethod where ws.MinutesAutomaticDecision > 0  insert into @Helper(UID_HelperKey, NextReminder, NextAutomaticDecision, UID_ElementKey
14, UID_PersonHead, UID_QERWorkingStep, Decision, LevelNumber, UID_PWODecisionRule, XObjectKey, RulerLevel) select h.UID_AttestationHelper, h.NextReminder
15, h.NextAutomaticDecision, ac.UID_AttestationCase, h.UID_PersonHead, h.UID_QERWorkingStep, h.Decision, h.LevelNumber, h.UID_PWODecisionRule, h.XObjectKey
16, h.RulerLevel from @AttestationCasesAutomatic ac join AttestationHelper h with (readpast) on ac.UID_AttestationCase = h.UID_AttestationCase  select top
17 1 @GenProcID = acs.GenProcID  from @AttestationCasesAutomatic acs  exec QBM_PSessionContextSet 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser'
18, @XUser update @Helper set NextAutomaticDecision = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(), ws.MinutesAutomaticDecision, h.UID_PersonHead) , IsToUpdateNextAutomaticDec
19 = 1 from @Helper h   join QERWorkingStep ws on h.UID_QERWorkingStep = ws.UID_QERWorkingStep join AttestationCase pwo with (readpast) on h.UID_ElementKey
20 = pwo.UID_AttestationCase join PWODecisionRule r on ws.UID_PWODecisionRule = r.UID_PWODecisionRule where ws.MinutesAutomaticDecision > 0  and h.NextAutomaticDecision
21 = @MaxDate2200 and isnull(decision, '') = '' and r.UID_Task is null and h.levelnumber = pwo.DecisionLevel  select top 1 @GenProcID = acs.GenProcID  from
22 @AttestationCasesAutomatic acs  exec QBM_PSessionContextSet 'GenProcID', @GenProcID update @Helper set NextAutomaticDecision = @MaxDate2200 , IsToUpdateNextAutomaticDec
23 = 1 from @Helper h   join QERWorkingStep ws on h.UID_QERWorkingStep = ws.UID_QERWorkingStep join AttestationCase pwo with (readpast) on h.UID_ElementKey
24 = pwo.UID_AttestationCase join PWODecisionRule r on ws.UID_PWODecisionRule = r.UID_PWODecisionRule where h.NextAutomaticDecision < @MaxDate2200  and (
25 ws.MinutesAutomaticDecision = 0 or isnull(decision, '') > '' or h.levelnumber <> pwo.DecisionLevel or r.UID_Task > ' ' )  declare @ElementBuffer_action
26 QBM_YCursorBuffer insert into @ElementBuffer_action (UID1  , UID2  , ObjectKey1  , Ident1  , Int1  , Ident2  ) select distinct  acs.UID_AttestationCase
27 , acs.GenProcID, acs.XObjectKey, s.AutomaticDecision , acs.DecisionLevel , min(dbo.QBM_FCVIntToString(hp.SubLevelNumber))  from @AttestationCasesAutomatic
28 acs join ( select h.UID_ElementKey as UID_AttestationCase, h.uid_QERWorkingStep  from @Helper h  join @AttestationCasesAutomatic pwo on h.UID_ElementKey
29 = pwo.uid_AttestationCase and h.levelnumber = pwo.DecisionLevel join QERWorkingStep ws on ws.UID_QERWorkingStep = h.UID_QERWorkingStep where h.NextAutomaticDecision
30 < @MaxDate2200      and h.RulerLevel <> 2 group by h.UID_ElementKey, h.UID_QERWorkingStep having max(h.NextAutomaticDecision) < getutcdate() ) as x on
31 acs.UID_AttestationCase  = x.UID_AttestationCase  and acs.IsClosed = 0 join QERWorkingStep s on s.uid_QERWorkingStep = x.UID_QERWorkingStep join AttestationHelper
32 hp on hp.UID_AttestationCase = acs.UID_AttestationCase and hp.UID_QERWorkingStep = s.UID_QERWorkingStep where  dbo.QBM_FGIJobCreatedExists(acs.XObjectKey
33) = 0  and dbo.ATT_FGIATTDecisionPossible(acs.UID_AttestationCase, hp.LevelNumber, hp.SubLevelNumber) = 1 group by acs.UID_AttestationCase , acs.GenProcID
34, acs.XObjectKey, s.AutomaticDecision , acs.DecisionLevel select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select
35 @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @uid_AttestationCase = bu.UID1  , @GenProcID = bu.UID2  , @BasisObjectKey
36 = bu.ObjectKey1  , @AbortMethod = bu.Ident1  , @DecisionLevel = bu.Int1  , @SubLevelNumberString = bu.Ident2 from @ElementBuffer_action bu where bu.ElementIndex
37 = @ElementIndex select @SQLcmd = N'uid_AttestationCase = ''' + rtrim(@uid_AttestationCase) + N''' and DecisionLevel = ' + STR(@DecisionLevel)  if @AbortMethod
38 in ('TRUE', 'FALSE') begin  exec QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'MakeDecision'
39 , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = 'sa' , @Param2 = @AbortMethod , @Param3 = '#LDS#Automatic system approval: Decision due to timeout.|'
40  , @Param5 = @SubLevelNumberString , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional
41 = ' IsClosed = 0 '  end if @AbortMethod in ('ESCALATE')  and not exists (select top 1 1 from @ElementBuffer_action bu where bu.ElementIndex < @ElementIndex
42 and bu.UID1 = @UID_AttestationCase ) begin  exec QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase' , @whereclause = @SQLcmd , @save = 1 , @MethodName
43 = 'ESCALATE' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = 'sa' , @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to timeout.|'
44 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' IsClosed = 0 '  end if
45 @AbortMethod in ('Abort')  and not exists (select top 1 1 from @ElementBuffer_action bu where bu.ElementIndex < @ElementIndex and bu.UID1 = @UID_AttestationCase
46 ) begin exec QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID
47 , @ObjectKeysAffected = DEFAULT , @param1 ='#LDS#Automatic system approval: "Abort" due to timeout.|' , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey
48 = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' IsClosed = 0 '  end      select @ElementIndex += 1 end  insert into @AttestationCasesReminder
49(UID_AttestationCase, ObjectKeyBase, UID_AttestationPolicy , GenProcID, XObjectKey, UID_QERWorkingMethod, IsClosed, DecisionLevel, XDateInserted ) select
50 distinct ac.UID_AttestationCase, ac.ObjectKeyBase, ac.UID_AttestationPolicy , cu.GenProcID, ac.XObjectKey, ac.UID_QERWorkingMethod, ac.IsClosed, ac.DecisionLevel
51, ac.XDateInserted from @DBQueueCurrent cu join AttestationCase ac with (readpast) on cu.UID_Parameter = ac.UID_AttestationCase join QERWorkingStep ws 
52on ws.UID_QERWorkingMethod = ac.UID_QERWorkingMethod and ac.DecisionLevel = ws.levelnumber cross apply dbo.ATT_FTATTDecisionPossible(ac.UID_AttestationCase
53, ws.LevelNumber, ws.SubLevelNumber) dp join AttestationHelper ah with (readpast) on ac.UID_AttestationCase = ah.UID_AttestationCase and ac.decisionlevel
54 = ah.levelnumber join PWODecisionRule dr on ah.UID_PWODecisionRule = dr.UID_PWODecisionRule  where ac.IsClosed = 0 and dp.DecisionPossible = 1 and dr.UID_Task
55 is null and ws.MinutesReminder > 0    insert into @Helper(UID_HelperKey, NextReminder, NextAutomaticDecision, UID_ElementKey, UID_PersonHead, UID_QERWorkingStep
56, Decision, LevelNumber, XObjectKey, UID_PWODecisionRule, RulerLevel) select h.UID_AttestationHelper, h.NextReminder, h.NextAutomaticDecision, ac.UID_AttestationCase
57, h.UID_PersonHead, h.UID_QERWorkingStep, h.Decision, h.LevelNumber, h.XObjectKey, h.UID_PWODecisionRule, h.RulerLevel from @AttestationCasesReminder ac
58 join AttestationHelper h with (readpast) on ac.UID_AttestationCase = h.UID_AttestationCase  where not exists (select top 1 1 from @Helper e where e.UID_HelperKey
59 = h.UID_AttestationHelper )    select top 1 @GenProcID = acs.GenProcID  from @AttestationCasesReminder acs  exec QBM_PSessionContextSet 'GenProcID', @GenProcID
60 update @Helper set NextReminder = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(), ws.MinutesReminder, h.UID_PersonHead     ) , IsToUpdateNextReminder = 
611 from @AttestationCasesReminder pwo   join @Helper h on h.UID_ElementKey = pwo.UID_AttestationCase join QERWorkingStep ws on h.UID_QERWorkingStep = ws.UID_QERWorkingStep
62 join PWODecisionRule r on h.UID_PWODecisionRule = r.UID_PWODecisionRule where ws.MinutesReminder > 0  and isnull(h.NextReminder, @MaxDate2200) = @MaxDate2200
63 and isnull(h.Decision, '') in ( '', 'Q') and r.UID_Task is null and h.levelnumber = pwo.DecisionLevel   exec QBM_PSessionContextSet 'GenProcID', @GenProcID
64 update @Helper set NextReminder = @MaxDate2200 , IsToUpdateNextReminder = 1 from @Helper h  join @AttestationCasesReminder acs on h.UID_ElementKey = acs.UID_AttestationCase
65 join QERWorkingStep ws on h.UID_QERWorkingStep = ws.UID_QERWorkingStep join AttestationCase pwo with (readpast) on h.UID_ElementKey = pwo.UID_AttestationCase
66 join PWODecisionRule r on h.UID_PWODecisionRule = r.UID_PWODecisionRule where h.NextReminder < @MaxDate2200  and ( ws.MinutesReminder = 0 or isnull(h.Decision
67, '') not in ( '', 'Q') or h.levelnumber <> pwo.DecisionLevel or r.UID_Task > ' ' )  update @Helper set NextReminder = @MaxDate2200 , IsToUpdateNextReminder
68 = 1 from @Helper h  join @AttestationCasesReminder pwo on h.UID_ElementKey = pwo.UID_AttestationCase where h.NextReminder < @MaxDate2200 and isnull(h.Decision
69, '') = '' and h.levelnumber = pwo.DecisionLevel and exists (select top 1 1 from @Helper hq where hq.UID_ElementKey = h.UID_ElementKey and hq.Decision 
70= 'Q' and hq.LevelNumber = h.LevelNumber )  declare @ElementBuffer_remind QBM_YCursorBuffer insert into @ElementBuffer_remind (UID1  , UID2  , UID3  , 
71Int1  , Bit1  , ObjectKey1  , Int2  , UID4  ) select h.UID_HelperKey , pwo.GenProcID, pe.UID_person, ws.MinutesReminder, sign(len(isnull(ws.UID_DialogRichMailReminder
72, ''))), h.XObjectKey , pwo.decisionlevel , pwo.UID_AttestationCase from @Helper h  join @AttestationCasesReminder pwo on h.UID_ElementKey = pwo.UID_AttestationCase
73 and h.levelnumber = pwo.DecisionLevel join person pe on pe.UID_person = h.UID_PersonHead     join QERWorkingStep ws on h.UID_QERWorkingStep = ws.UID_QERWorkingStep
74 where h.NextReminder < getutcdate()  and dbo.QBM_FGIJobCreatedExists(h.XObjectKey) = 0  and h.RulerLevel < 2 select @ElementCount = @@ROWCOUNT select 
75@ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_AttestationHelper
76 = bu.UID1  , @GenProcID = bu.UID2  , @UID_PersonHead = bu.UID3  , @MinutesReminder = bu.Int1  , @MailExists = bu.Bit1  , @ObjectkeyForMail = bu.ObjectKey1
77  , @CurrentDecisionLevel = bu.Int2 , @UID_AttestationCase = bu.UID4  from @ElementBuffer_remind bu where bu.ElementIndex = @ElementIndex exec QBM_PSessionContextSet
78 'GenProcID', @GenProcID exec QBM_PSessionContextSet 'XUser', @XUser update @Helper set NextReminder = dbo.QER_FCVTimeToWorkTimePerson(getutcdate(), @MinutesReminder
79, @UID_PersonHead) , IsToUpdateNextReminder = 1 where UID_HelperKey = @UID_AttestationHelper if @MailExists > 0 begin  select @WhereclauseForMail = N'XObjectKey = '''
80 + @ObjectkeyForMail + N''''  + N' and exists (select top 1 1 from AttestationCase where UID_AttestationCase  = ''' + @UID_AttestationCase + ''' and decisionlevel = '