dbo.ATT_ZAttestationCheckValid

1     create   procedure ATT_ZAttestationCheckValid (@SlotNumber int) as begin  declare @SQLcmd nvarchar(1000) declare @UID_AttestationCase varchar
2(38) declare @GenProcID varchar(38) declare @BasisObjectKey varchar(138) declare @AbortMethod nvarchar(64) declare @UID_Task varchar(38) declare @EscalateIfNoApprover
3 bit declare @DecisionLevel int declare @TableName varchar(30) declare @Cmd nvarchar(max) declare @WaitingForEx_30965 varchar(32) = 'waiting for EX' declare
4 @AttCaseObjectMissing QBM_YParameterList declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @ElementLast int declare @ElementCount
5 int declare @ElementIndex int declare @AttestationCaseShadow ATT_YAttestationCase declare @DBQueueElements_spezial QBM_YDBQueueRaw BEGIN TRY insert into
6 @AttestationCaseShadow(UID_AttestationCase, ObjectKeyBase, UID_AttestationPolicy , GenProcID, XObjectKey, UID_QERWorkingMethod, IsClosed, DecisionLevel
7, XDateInserted ) select ac.UID_AttestationCase, ac.ObjectKeyBase, ac.UID_AttestationPolicy , cu.GenProcID, ac.XObjectKey, ac.UID_QERWorkingMethod, ac.IsClosed
8, ac.DecisionLevel, XDateInserted from QBMDBQueueCurrent cu with (readpast) join AttestationCase ac on cu.UID_Parameter = ac.UID_AttestationCase and cu.SlotNumber=
9 @SlotNumber  and ac.IsUnderConstruction = 0    declare @ElementBuffer_missing QBM_YCursorBuffer insert into @ElementBuffer_missing (UID1) select  distinct
10 case when t.TableName is null then 'DialogHistoryDB' else t.TableName end AS TableName from ( select distinct dbo.QBM_FCVObjectkeyToElement('TableName'
11, acs.ObjectKeyBase ) as TableName from @AttestationCaseShadow acs   ) as y left outer join DialogTable t with (readpast) on y.TableName = t.TableName 
12select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast
13 begin select top 1 @TableName = bu.UID1 from @ElementBuffer_missing bu where bu.ElementIndex = @ElementIndex select @Cmd = '
14	select ac.UID_AttestationCase, cu.GenProcID, ac.XObjectKey
15			from QBMDBQueueCurrent cu  with (readpast) join AttestationCase ac  with (nolock) on cu.UID_Parameter = ac.UID_AttestationCase
16														and ac.ObjectKeyBase like ''<Key><T>'
17 + @TableName + '</T>%''
18								left outer join ' + @TableName + ' x  with (nolock) on x.XObjectKey = ac.ObjectKeyBase
19		where cu.SlotNumber = ' + str
20(@Slotnumber) + '
21		-- 35760
22		and ac.IsUnderConstruction = 0
23		and x.XObjectKey is null
24		'  insert into @AttCaseObjectMissing(Parameter1 , Parameter2
25 , Parameter3 ) exec sp_executesql @cmd select @ElementIndex += 1 end   declare @ElementBuffer_close QBM_YCursorBuffer insert into @ElementBuffer_close
26 (UID1, UID2, ObjectKey1) select distinct ac.Parameter1  , ac.Parameter2 , ac.Parameter3  from @AttCaseObjectMissing ac select @ElementCount = @@ROWCOUNT
27 select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_AttestationCase
28 = bu.UID1 , @GenProcID = bu.UID2 , @BasisObjectKey = bu.ObjectKey1 from @ElementBuffer_close bu where bu.ElementIndex = @ElementIndex select @SQLcmd =
29 N'UID_AttestationCase = ''' + rtrim(@UID_AttestationCase) + N'''' exec QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase' , @whereclause = @SQLcmd
30 , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Automatic system approval: object to attest does not exist.|'
31 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' IsClosed = 0 '  delete
32  @AttestationCaseShadow  where UID_AttestationCase = @UID_AttestationCase select @ElementIndex += 1 end    declare @ElementBuffer_deactive QBM_YCursorBuffer
33 insert into @ElementBuffer_deactive (UID1, UID2, ObjectKey1) select distinct acs.UID_AttestationCase , acs.GenProcID, acs.XObjectKey  from @AttestationCaseShadow
34 acs join attestationpolicy ap on ap.UID_AttestationPolicy = acs.UID_AttestationPolicy where ap.IsInactive = 1  select @ElementCount = @@ROWCOUNT select
35 @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_AttestationCase
36 = bu.UID1 , @GenProcID = bu.UID2 , @BasisObjectKey = bu.ObjectKey1 from @ElementBuffer_deactive bu where bu.ElementIndex = @ElementIndex select @SQLcmd
37 = N'UID_AttestationCase = ''' + rtrim(@UID_AttestationCase) + N'''' exec QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase' , @whereclause = 
38@SQLcmd , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Automatic system approval:  Policy is inactive.|'
39 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' IsClosed = 0 '  delete
40  @AttestationCaseShadow  where UID_AttestationCase = @UID_AttestationCase select @ElementIndex += 1 end    declare @ElementBuffer_noDecider QBM_YCursorBuffer
41 insert into @ElementBuffer_noDecider (UID1, UID2, ObjectKey1, Bit1, Int1) select distinct acs.UID_AttestationCase , acs.GenProcID, acs.XObjectKey , ws.EscalateIfNoApprover
42, acs.DecisionLevel  from @AttestationCaseShadow acs join QERWorkingStep ws on ws.UID_QERWorkingMethod = acs.UID_QERWorkingMethod and acs.IsClosed = 0 
43and acs.DecisionLevel = ws.levelnumber where not exists ( select top 1 1 from AttestationHelper ah where acs.UID_AttestationCase = ah.UID_AttestationCase
44 and ws.LevelNumber = ah.LevelNumber and ws.SubLevelNumber = ah.SubLevelNumber and ws.UID_PWODecisionRule = ah.UID_PWODecisionRule and ah.RulerLevel in
45 (0, 1) ) select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <=
46 @ElementLast begin select top 1 @UID_AttestationCase = bu.UID1 , @GenProcID = bu.UID2 , @BasisObjectKey = bu.ObjectKey1 , @EscalateIfNoApprover = bu.Bit1
47 , @DecisionLevel = bu.Int1 from @ElementBuffer_noDecider bu where bu.ElementIndex = @ElementIndex  if @EscalateIfNoApprover = 0 begin select @SQLcmd =
48 N'UID_AttestationCase = ''' + rtrim(@UID_AttestationCase) + N'''' exec QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase' , @whereclause = @SQLcmd
49 , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Automatic system approval:  No approver available.|'
50 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' IsClosed = 0 '  end else
51 begin select @SQLcmd = N'uid_AttestationCase = ''' + rtrim(@uid_AttestationCase) + N''' and DecisionLevel = ' + STR(@DecisionLevel) exec QBM_PJobCreate_HOCallMethod
52 @objecttype = 'AttestationCase' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'ESCALATE' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT
53 , @param1 = 'sa' , @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to no approver available.|' , @Retries = 3 , @isToFreezeOnError 
54= 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' IsClosed = 0 '  end delete  @AttestationCaseShadow  where 
55UID_AttestationCase = @UID_AttestationCase select @ElementIndex += 1 end    declare @ElementBuffer_lessDecider QBM_YCursorBuffer insert into @ElementBuffer_lessDecider
56 (UID1, UID2, ObjectKey1, Bit1, Int1) select distinct acs.UID_AttestationCase , acs.GenProcID, acs.XObjectKey , ws.EscalateIfNoApprover, acs.DecisionLevel
57  from @AttestationCaseShadow acs join QERWorkingStep ws on ws.UID_QERWorkingMethod = acs.UID_QERWorkingMethod and acs.IsClosed = 0 and acs.DecisionLevel
58 = ws.levelnumber cross apply dbo.ATT_FTATTDecisionPossible(acs.UID_AttestationCase, ws.LevelNumber, ws.SubLevelNumber) dp where dp.DecisionPossible = 
59-1  and acs.isclosed = 0 select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while 
60@ElementIndex <= @ElementLast begin select top 1 @UID_AttestationCase = bu.UID1 , @GenProcID = bu.UID2 , @BasisObjectKey = bu.ObjectKey1 , @EscalateIfNoApprover
61 = bu.Bit1 , @DecisionLevel = bu.Int1 from @ElementBuffer_lessDecider bu where bu.ElementIndex = @ElementIndex  if @EscalateIfNoApprover = 0 begin select
62 @SQLcmd = N'UID_AttestationCase = ''' + rtrim(@UID_AttestationCase) + N'''' exec QBM_PJobCreate_HOCallMethod @objecttype = 'AttestationCase' , @whereclause
63 = @SQLcmd , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Automatic system approval: Not enough approvers available.|'
64 , @Retries = 3 , @isToFreezeOnError = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' IsClosed = 0 '  end else
65 begin select @SQLcmd = N'uid_AttestationCase = ''' + rtrim(@uid_AttestationCase) + N''' and DecisionLevel = ' + STR(@DecisionLevel) exec QBM_PJobCreate_HOCallMethod
66 @objecttype = 'AttestationCase' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'ESCALATE' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT
67 , @param1 = 'sa' , @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to not enough approvers available.|' , @Retries = 3 , @isToFreezeOnError
68 = 1 , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 , @WhereClauseAdditional = ' IsClosed = 0 '  end delete  @AttestationCaseShadow  where
69 UID_AttestationCase = @UID_AttestationCase select @ElementIndex += 1 end   declare @ElementBuffer_decidePos QBM_YCursorBuffer insert into @ElementBuffer_decidePos
70 (UID1) select distinct dr.UID_Task  from @AttestationCaseShadow acs join AttestationHelper ah on acs.UID_AttestationCase = ah.UID_AttestationCase and 
71acs.decisionlevel = ah.levelnumber join pwodecisionrule dr on ah.UID_PWODecisionRule = dr.UID_PWODecisionRule where dr.UID_Task > ' '  and acs.isclosed
72 = 0   and not (dr.UID_Task = 'ATT-K-AttestationMakeDecisionEX' and isnull(ah.ReasonHead, '') = @WaitingForEx_30965 ) select @ElementCount = @@ROWCOUNT
73 select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_Task
74 = bu.UID1 from @ElementBuffer_decidePos bu where bu.ElementIndex = @ElementIndex delete  @DBQueueElements_spezial  insert into @DBQueueElements_spezial
75 (object, subobject, genprocid) select x.uid, null, x.GenProcID from ( select acs.UID_AttestationCase as uid , acs.GenProcID as GenProcID  from @AttestationCaseShadow
76 acs join AttestationHelper ah on acs.UID_AttestationCase = ah.UID_AttestationCase and acs.decisionlevel = ah.levelnumber join pwodecisionrule dr on ah.UID_PWODecisionRule
77 = dr.UID_PWODecisionRule where  acs.isclosed = 0 and dr.UID_Task = @UID_Task  and not (dr.UID_Task = 'ATT-K-AttestationMakeDecisionEX' and isnull(ah.ReasonHead
78, '') = @WaitingForEx_30965 ) ) as x exec QBM_PDBQueueInsert_Bulk @UID_Task, @DBQueueElements_spezial delete  @AttestationCaseShadow where UID_AttestationCase
79 in (select e.Object from @DBQueueElements_spezial e ) select @ElementIndex += 1 end    declare @ElementBuffer_TimeCheck QBM_YCursorBuffer insert into 
80@ElementBuffer_TimeCheck (UID1  , UID2  , ObjectKey1  , Ident1  ) select distinct acs.UID_AttestationCase , acs.genprocID, acs.XObjectKey, 'Abort'  from