dbo.ATT_PAttestationHelperFill

1    create   procedure ATT_PAttestationHelperFill (@UID_AttestationCase varchar(38) , @GenProcID varchar(38) , @isNewDecisionLevel bit = 0  , @CurrentLevelOnly
2 int = 0 ) with execute as 'dbo' as begin declare @SubLevelNumber int declare @LevelNumber int declare @LevelNumber_alt int  declare @ObjectkeyForMail 
3varchar(138)  declare @AttestationHelper_Old QER_YPWOHelper declare @AttestationHelper_New QER_YPWOHelper declare @PersonAndLevel QBM_YParameterList   
4 declare @FunctionName varchar(30)  declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext
5('XUser') declare @WhereclauseForMail nvarchar(1000) declare @SQLCmd nvarchar(max) declare @isToInsert bit declare @isToDelete bit declare @makeEmpty bit
6 declare @CurrentDecisionLevel int declare @UID_PwoDecisionRule varchar(38) declare @UID_QERWorkingStep varchar(38) declare @UID_AttestationHistory varchar
7(38) declare @countApproverNeeded int declare @countApproverAvailable int declare @ElementLast int declare @ElementBuffer_Rules QBM_YCursorBuffer declare
8 @ElementBuffer_Notification QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @XUser nvarchar(64) = object_name(@@procid) 
9declare @Xdate datetime = getutcdate() SET XACT_ABORT OFF BEGIN TRY select top 1 @CurrentDecisionLevel = decisionlevel from attestationCase where UID_AttestationCase
10 = @UID_AttestationCase select @makeEmpty = 0  if exists (select top 1 1 from AttestationCase ac where ac.UID_AttestationCase = @UID_AttestationCase and
11 ac.isclosed = 1 ) begin  select @makeEmpty = 1 end if exists (select top 1 1 from AttestationCase ac join AttestationPolicy ap on ac.UID_AttestationPolicy
12 = ap.UID_AttestationPolicy where ac.UID_AttestationCase = @UID_AttestationCase and ap.isinactive = 1 ) begin  select @makeEmpty = 1 end  if exists (select
13 top 1 1 from AttestationHelper where UID_AttestationCase = @UID_AttestationCase and Decision is null ) begin update AttestationHelper set Decision = ''
14 where UID_AttestationCase = @UID_AttestationCase and Decision is null end   if exists (select top 1 1 from AttestationHelper where UID_AttestationCase
15 = @UID_AttestationCase and Decision = '-' ) begin delete AttestationHelper where UID_AttestationCase = @UID_AttestationCase and Decision = '-' end  insert
16 into @AttestationHelper_Old (UID_PWODecisionRule, uid_personHead, levelnumber, SubLevelNumber, uid_QERWorkingStep, decision , UID_PersonAdditional, UID_PersonInsteadOf
17, IsFromDelegation, RulerLevel , UID_PWORulerOrigin , NextAutomaticDecision ) select isnull(UID_PWODecisionRule, ''), uid_personHead, isnull(levelnumber
18,0), isnull(SubLevelNumber,0), ah.uid_QERWorkingStep, isnull(ah.decision,'') , UID_PersonAdditional, UID_PersonInsteadOf, IsFromDelegation, RulerLevel 
19, UID_PWORulerOrigin , NextAutomaticDecision from AttestationHelper ah where ah.UID_AttestationCase = @UID_AttestationCase  and (@CurrentLevelOnly = 0 
20or ah.LevelNumber = @CurrentDecisionLevel ) if @makeEmpty = 1 begin update @AttestationHelper_Old set isToDelete = 1 goto publizieren end  Update @AttestationHelper_Old
21 set isToDelete = 1 from @AttestationHelper_Old zk join (select * from @AttestationHelper_Old ) as d on zk.UID_PWODecisionRule = d.UID_PWODecisionRule 
22and zk.uid_QERWorkingStep = d.uid_QERWorkingStep and zk.levelnumber = d.levelnumber and zk.Sublevelnumber = d.Sublevelnumber where zk.UID_PersonInsteadOf
23 = d.UID_PersonHead  and d.UID_PersonInsteadOf > ' '  delete  @ElementBuffer_Rules  insert into @ElementBuffer_Rules ( UID1  , UID2  , Ident1  , Int1  
24, Int2  , Int3  ) select distinct ws.UID_PWODecisionRule, ws.UID_QERWorkingStep , dbo.QER_FCVRuleGUIDToFunctionName(ws.UID_PWODecisionRule) , ws.LevelNumber
25 , ws.SubLevelNumber , ws.CountApprover from AttestationCase ac join AttestationPolicy ap on ac.uid_attestationPolicy = ap.uid_attestationPolicy join QERWorkingStep
26 ws on ac.UID_QERWorkingMethod = ws.UID_QERWorkingMethod where ac.UID_AttestationCase = @UID_AttestationCase and (@CurrentLevelOnly = 0 or ( ac.DecisionLevel
27 = @CurrentDecisionLevel and ws.LevelNumber = @CurrentDecisionLevel ) ) select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount
28 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_PWODecisionRule = bu.UID1  , @UID_QERWorkingStep = bu.UID2
29  , @FunctionName = bu.Ident1  , @LevelNumber = bu.Int1  , @SublevelNumber = bu.Int2 , @countApproverNeeded = bu.Int3 from @ElementBuffer_Rules bu where
30 bu.ElementIndex = @ElementIndex   if exists (select top 1 1 from sys.objects o where o.name = @FunctionName and o.type in ('IF'  , 'TF'  ) ) begin select
31 @SQLcmd = concat('
32					select s.uid_person,  max(s.UID_PWORulerOrigin), s.RulerLevel
33						from ( select a1.uid_person, ''0'' as RulerLevel, a1.UID_PWORulerOrigin
34								from dbo.'
35 , @FunctionName , ' (''' + @UID_AttestationCase , ''', ''' , @uid_QERWorkingStep , ''') as a1
36								union all
37								select  pio.UID_Person, ''1'' as Rulerlevel, dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) as UID_PWORulerOrigin
38									from QERWorkingStep s join PersonInBaseTree pio on s.UID_AERoleFallBack = pio.UID_Org
39																					and pio.XOrigin > 0
40									where s.UID_QERWorkingStep = '''
41 , @UID_QERWorkingStep , '''
42								union all 
43								select  pio.UID_Person, ''2'' as Rulerlevel, dbo.QER_FGIPWORulerOrigin(pio.XObjectKey) as UID_PWORulerOrigin
44									from PersonInBaseTree pio 
45									where pio.UID_Org = ''ATT-AEROLE-ATTESTATION-INTERVENTION''
46									and pio.XOrigin > 0
47							) as s
48						group by s.uid_person, s.RulerLevel
49								'
50 ) delete  @PersonAndLevel  insert into @PersonAndLevel (Parameter1, Parameter2, Parameter3) exec sp_executesql @SQLcmd insert into @AttestationHelper_New
51 (UID_PWODecisionRule, uid_personHead, uid_QERWorkingStep , levelnumber, Sublevelnumber, istoInsert, RulerLevel , UID_PWORulerOrigin, UID_PersonSubstituteSender
52 ) select @uid_Pwodecisionrule , s.uid_person, @UID_QERWorkingStep , @levelnumber, @Sublevelnumber, 0, dbo.QBM_FCVStringToInt( s.RulerLevel, 0) , max(s.UID_PWORulerOrigin
53) as UID_PWORulerOrigin, max(isnull(s.UID_PersonSubstituteSender, '')) from ( select p.Parameter1 as UID_Person, p.Parameter2 as UID_PWORulerOrigin, p.Parameter3
54 as RulerLevel, null as UID_PersonSubstituteSender from @PersonAndLevel p union all select us.UID_PersonReceiver, us.UID_PersonWantsOrg as UID_PWORulerOrigin
55, p.Parameter3 as RulerLevel , us.UID_PersonSender as UID_PersonSubstituteSender from @PersonAndLevel p join QERUniversalSubstitute us on p.Parameter1 
56= us.UID_PersonSender where us.IsCurrentlyActive = 1 and us.UseForAttestation = 1 and not exists (select top 1 1 from @PersonAndLevel pal where pal.Parameter1
57 = us.UID_PersonReceiver and pal.Parameter3 = p.Parameter3) ) as s group by s.UID_Person, s.RulerLevel   update @AttestationHelper_New set UID_PWORulerOrigin
58 = su.UID_PersonWantsOrg from @AttestationHelper_New n join QERUniversalSubstitute su on n.UID_PersonHead = su.UID_PersonReceiver  and (su.UseForHeadPerson
59 = 1 or su.UseForHeadOrg = 1) and su.IsCurrentlyActive = 1 where n.UID_PWORulerOrigin is null and exists (select top 1 1 from @AttestationHelper_New se
60 where se.UID_PersonHead = su.UID_PersonSender and se.levelnumber = n.levelnumber and se.Sublevelnumber = n.sublevelnumber )  if dbo.QBM_FGIConfigparmValue
61('QER\Attestation\PersonToAttestNoDecide') = '1' begin delete  @AttestationHelper_New from @AttestationHelper_New ah join QERWorkingStep s on ah.uid_QERWorkingStep
62 = s.UID_QERWorkingStep  and s.IgnoreNoDecideForPerson = 0 join (select he.UID_PersonAlsoMe as UID_Person  from ( select ac.ObjectKey1, ac.ObjectKey2, 
63ac.ObjectKey3, ac.ObjectKeyBase from AttestationCase ac where ac.UID_AttestationCase = @UID_AttestationCase ) r unpivot ( wert for spalte in (ObjectKey1
64, ObjectKey2, ObjectKey3, ObjectKeyBase) ) as un join Person p on un.wert = p.XObjectKey join QER_VPersonsAreMe he on p.UID_Person = he.UID_PersonOrigin
65  ) as weg on weg.UID_Person in (ah.uid_personHead , ah.UID_PersonSubstituteSender) or (isnull(ah.UID_PersonAdditional , '') = weg.UID_Person and ah.RulerLevel
66 < 2 ) or (isnull(ah.UID_PersonInsteadOf , '') = weg.UID_Person and ah.RulerLevel < 2 ) end  select top 1 @countApproverAvailable = COUNT(*) from @AttestationHelper_New
67 n where n.RulerLevel = 0 and n.uid_QERWorkingStep = @UID_QERWorkingStep and n.levelnumber = @LevelNumber and n.Sublevelnumber = @SubLevelNumber  if @countApproverAvailable
68 >= case when @countApproverNeeded < 0 then @countApproverAvailable when @countApproverNeeded = 0 then 1 else @countApproverNeeded end and @countApproverAvailable
69 > 0 begin delete  @AttestationHelper_New from @AttestationHelper_New n where n.RulerLevel = 1 and n.uid_QERWorkingStep = @UID_QERWorkingStep and n.levelnumber
70 = @LevelNumber and n.Sublevelnumber = @SubLevelNumber end    delete  @AttestationHelper_New from @AttestationHelper_New n where n.RulerLevel = 0 and n.uid_QERWorkingStep
71 = @UID_QERWorkingStep and n.levelnumber = @LevelNumber and n.Sublevelnumber = @SubLevelNumber and exists (select top 1 1 from @AttestationHelper_New n1
72 where n1.RulerLevel = 1 and n1.uid_QERWorkingStep = @UID_QERWorkingStep and n1.levelnumber = @LevelNumber and n1.Sublevelnumber = @SubLevelNumber and 
73n1.uid_personHead = n.uid_personHead )   if @UID_PwoDecisionRule in (select r.UID_PWODecisionRule from PWODecisionRule r where r.UID_Task > ' ' ) begin
74 delete  @AttestationHelper_New from @AttestationHelper_New n where n.RulerLevel > 0 and n.uid_QERWorkingStep = @UID_QERWorkingStep and n.levelnumber =
75 @LevelNumber and n.Sublevelnumber = @SubLevelNumber end  end select @ElementIndex += 1 end     update @AttestationHelper_Old set isToDelete = 1 where 
76uid_personhead is null  and isnull(UID_PWODecisionRule, '') not in (select UID_PWODecisionRule from pwodecisionrule where UID_Task > ' '  )  delete  @AttestationHelper_New
77 where uid_personhead is null  and UID_PWODecisionRule not in (select UID_PWODecisionRule from pwodecisionrule where UID_Task > ' '  )   update @AttestationHelper_new
78 set UID_PersonAdditional = o.UID_PersonAdditional , UID_PersonInsteadOf = o.UID_PersonInsteadOf from @AttestationHelper_new n join @AttestationHelper_Old
79 o on n.UID_PWODecisionRule = o.UID_PWODecisionRule and n.uid_personHead = o.uid_personHead and n.uid_QERWorkingStep = o.uid_QERWorkingStep and n.levelnumber
80 = o.levelnumber and n.Sublevelnumber = o.Sublevelnumber where o.UID_PersonAdditional > ' ' or o.UID_PersonInsteadOf > ' '  insert into @AttestationHelper_New