dbo.QER_PITShopHelperFill_bulk

1create   procedure QER_PITShopHelperFill_bulk (@PWOs QBM_YParameterlist readonly      ) with execute as 'dbo' as begin declare @GenProcID_R varchar
2(38) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit
3(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) declare @ElementLast int declare @ElementBufferMulti_1 QBM_YCursorBuffer declare
4 @ElementCount int declare @ElementIndex int declare @ElementCountPWO int declare @ElementIndexPWO int declare @DBQueueElements_CheckDecision QBM_YDBQueueRaw
5 declare @DBQueueElements_MakeDecisionOC QBM_YDBQueueRaw declare @DBQueueElements_MakeDecisionOH QBM_YDBQueueRaw declare @DBQueueElements_MakeDecisionEX
6 QBM_YDBQueueRaw declare @PWOToCheck QER_YPWOBufferForHelper declare @PWOHelperPWO_Old QER_YPWOHelper declare @PWOHelperPWO_New QER_YPWOHelper declare 
7@hilfstab QER_YPWOHelperCPL declare @CountItemsException int declare @UID_PWO varchar(38) declare @SubLevelNumber int declare @LevelNumber int declare 
8@LevelNumber_alt int declare @uid_complianceRule varchar(38) declare @uid_complianceRule_max varchar(38) declare @UID_PersonNonCompliant varchar(38) declare
9 @FunctionName varchar(30) declare @UID_PWODecisionRule varchar(38) declare @UID_QERWorkingStep varchar(38) declare @countApproverNeeded int declare @SQLCmd
10 nvarchar(max) declare @countApproverAvailable int declare @GenProcIDToUse varchar(38) declare @DecisionLevelToUse int declare @SonderBehandlungException
11 bit = 0 declare @DebugSwitch int = 0 declare @cfgPersonOrderedNoDecide bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonOrderedNoDecide'
12)) declare @cfgPersonInsertedNoDecide bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('QER\ITShop\PersonInsertedNoDecide')) declare @OrderState
13 nvarchar(16) declare @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|', 0) declare @XUser nvarchar(64) = object_name
14(@@procid) declare @Xdate datetime = getutcdate() declare @PWOAndLevel table ( UID_PersonWantsOrg varchar(38) collate database_default , LevelNumber int
15 default 0 , index PWOAndLevel1 (UID_PersonWantsOrg) ) declare @Calltype varchar(1)  SET XACT_ABORT OFF BEGIN TRY drop table if exists #PWOHelperPWO_New
16 create table #PWOHelperPWO_New( UID_PWODecisionRule varchar(38) collate database_default , uid_personHead varchar(38) collate database_default , uid_QERWorkingStep
17 varchar(38) collate database_default , levelnumber int default 0 , Sublevelnumber int default 0 , istoInsert bit default 0 , UID_PWORulerOrigin varchar
18(38) collate database_default , RulerLevel int default 0 , UID_PersonWantsOrg varchar(38) collate database_default , UID_PersonSubstituteSender varchar
19(38) collate database_default )  insert into @PWOToCheck (UID_PersonWantsOrg, GenProcid, CurrentLevelOnly, IsNewDecisionLevel , makeEmpty , Decisionlevel
20 , UID_QERWorkingMethod , Orderstate , SonderlockeException , UID_ITShopOrgFinal , validFrom, ValidUntil, isOrderforWorkDesk, UID_PersonOrdered, UID_OrgParent
21, UID_PersonInserted, isReserved, UID_PersonHead ) select distinct p.Parameter1, dbo.QER_FGIGenProcIDForPWO(isnull(pwo.GenProcID, p.Parameter2) , p.Parameter2
22, @CfgUseGenProcID) as GenProcid , dbo.QBM_FCVStringToInt(p.Parameter3, 0) as CurrentLevelOnly , p.HasContentFull as IsNewDecisionLevel  ,  case when pwo.UID_PersonWantsOrg
23 is null then 1 when pwo.OrderState not in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') then 1 else 0 end as makeEmpty , pwo.decisionlevel ,
24 pwo.UID_QERWorkingMethod , pwo.OrderState  , sign(len(isnull(sOC.UID_QERWorkingStep, ''))) as SonderlockeException , pwo.UID_ITShopOrgFinal , pwo.ValidFrom
25, pwo.ValidUntil, isOrderforWorkDesk, UID_PersonOrdered, UID_OrgParent, pwo.UID_PersonInserted, pwo.IsReserved, UID_PersonHead from @PWOs p left outer 
26join PersonWantsOrg pwo on p.Parameter1 = pwo.UID_PersonWantsOrg  left outer join QERWorkingStep sOC on pwo.UID_QERWorkingMethod = sOC.UID_QERWorkingMethod
27 and sOC.UID_PWODecisionRule in ('CPL-PWODecisionRule-OC', 'CPL-PWODecisionRule-OH') and (dbo.QBM_FCVStringToInt(p.Parameter3, 0)  = 0 or (  sOC.LevelNumber
28 = pwo.DecisionLevel ) ) select @ElementCountPWO = @@ROWCOUNT       insert into @PWOAndLevel (UID_PersonWantsOrg, LevelNumber) select h.UID_PersonWantsOrg
29, h.LevelNumber from @PWOToCheck pwo join PWOHelperPWO h on h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg group by h.UID_PersonWantsOrg, h.LevelNumber 
30 , pwo.DecisionLevel having ( (max(pwo.CurrentLevelOnly) = 1 and pwo.DecisionLevel = h.LevelNumber)  or (max(pwo.CurrentLevelOnly) = 0 and (MAX(isnull(h.Decision
31, '')) = ''  or pwo.DecisionLevel = h.LevelNumber  ) ) ) union select pwo.UID_PersonWantsOrg, s.LevelNumber from @PWOToCheck pwo join QERWorkingStep s 
32on pwo.UID_QERWorkingMethod = s.UID_QERWorkingMethod where not exists (select top 1 1 from PWOHelperPWO h where h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg
33   and h.LevelNumber = s.LevelNumber and h.SubLevelNumber = s.SubLevelNumber )  if @DebugSwitch > 0 begin print 'initialbefüllung @PWOToCheck' select *
34 from @PWOToCheck end insert into @PWOHelperPWO_Old (UID_PWODecisionRule, uid_personHead, levelnumber, SubLevelNumber, UID_QERWorkingStep, decision, uid_complianceRule
35, uid_PersonNonCompliant, UID_PWORulerOrigin , UID_PersonAdditional, UID_PersonInsteadOf, IsFromDelegation, RulerLevel , IsToDelete , UID_PWOHelperPWO 
36, isToUpdateDecision , UID_PersonWantsOrg , NextAutomaticDecision ) select isnull(UID_PWODecisionRule, ''), pwoh.UID_PersonHead, levelnumber, SubLevelNumber
37, pwoh.uid_QERWorkingStep, isnull(pwoh.decision,''), uid_complianceRule, uid_PersonNonCompliant, UID_PWORulerOrigin , UID_PersonAdditional, UID_PersonInsteadOf
38, IsFromDelegation, RulerLevel , case when c.makeEmpty = 1 then 1   when pwoh.Decision = '-' then 1 else 0 end as IsToDelete , pwoh.UID_PWOHelperPWO , 
39case when pwoh.Decision is null then 1 else 0 end as isToUpdateDecision , c.UID_PersonWantsOrg , pwoh.NextAutomaticDecision from PWOHelperPWO pwoh with
40 (readpast) join @PWOToCheck c on pwoh.UID_PersonWantsOrg = c.UID_PersonWantsOrg  where   exists (select top 1 1 from @PWOAndLevel pl where pl.UID_PersonWantsOrg
41 = pwoh.UID_PersonWantsOrg and pl.LevelNumber = pwoh.LevelNumber ) or c.makeEmpty = 1 update @PWOToCheck set CheckSumHelper = x.CheckSumHelper from @PWOToCheck
42 p join ( select c.UID_PersonWantsOrg, checksum_agg(checksum(concat(h.UID_PWOHelperPWO, h.Decision))) as CheckSumHelper from @PWOToCheck c left outer join
43 PWOHelperPWO h with (readpast) on c.UID_PersonWantsOrg = h.UID_PersonWantsOrg  group by c.UID_PersonWantsOrg ) as x on p.UID_PersonWantsOrg = x.UID_PersonWantsOrg
44  if @DebugSwitch > 0 begin print 'initialbefüllung @PWOHelperPWO_Old' select * from @PWOHelperPWO_Old end                   if exists (select top 1 1 
45from @PWOToCheck c where c.SonderlockeException = 1         ) begin select @SonderBehandlungException = 1 end  if @SonderBehandlungException = 1 begin 
46insert into @hilfstab (uid_complianceRule, uid_personNonCompliant, SubLevelNumber, RuleSeverity, isHighestSeverity , Levelnumber , UID_PersonWantsOrg )
47 select pic.uid_complianceRule, pic.uid_person, -1, isnull(c.RuleSeverity, 0.0), 0 , sOC.Levelnumber , pwo.UID_PersonWantsOrg from HelperPWOCompliance 
48pic join @PWOToCheck  pwo on pic.uid_personwantsorg = pwo.uid_personwantsorg and pwo.SonderlockeException = 1 join ComplianceRule c on pic.uid_complianceRule
49 = c.uid_complianceRule  and c.IsInActive = 0 and c.IsWorkingCopy = 0  join QERWorkingStep sOC on sOC.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod and
50 sOC.UID_PWODecisionRule in ('CPL-PWODecisionRule-OC', 'CPL-PWODecisionRule-OH') join QERWorkingStep sCC on sCC.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod
51 and sCC.UID_PWODecisionRule in ('CPL-PWODecisionRule-CR') and sCC.levelnumber + sCC.NegativeSteps = sOC.Levelnumber   group by pwo.UID_PersonWantsOrg,
52 pic.uid_complianceRule, pic.uid_Person, isnull(c.RuleSeverity, 0.0), sOC.Levelnumber order by pwo.UID_PersonWantsOrg, pic.uid_complianceRule, pic.uid_Person
53, isnull(c.RuleSeverity, 0.0), sOC.Levelnumber  update @PWOToCheck set CountItemsException = x.anzahl from @PWOToCheck c join ( select h.UID_PersonWantsOrg
54, count(*) as anzahl from @hilfstab h group by h.UID_PersonWantsOrg ) as x on x.UID_PersonWantsOrg = c.UID_PersonWantsOrg where c.SonderlockeException 
55= 1  insert into PWODecisionHistory ( UID_PWODecisionHistory , XObjectKey , UID_PersonRelated , UID_PersonWantsOrg, DisplayPersonHead, ReasonHead, DateHead
56 , XDateInserted, XDateUpdated, XUserInserted, XUserUpdated, DecisionLevel , ValidUntil, ValidFrom , DecisionType , IsDecisionBySystem , UID_PWODecisionRule
57 , OrderState , UID_ITShopOrgFinal , IsToHideInHistory , UID_ComplianceRule ) select c.UID_HelperPWOCompliance , dbo.QBM_FCVElementToObjectKey1('PWODecisionHistory'
58, 'UID_PWODecisionHistory', c.UID_HelperPWOCompliance) , c.UID_Person , pwo.UID_PersonWantsOrg, 'compliance violation detected', 'compliance violation detected'
59, GETUTCDATE() , GETUTCDATE(), GETUTCDATE(), 'sa', 'sa', pwo.DecisionLevel , pwo.ValidUntil, pwo.ValidFrom , 'Compliance' , 1  , null , pwo.OrderState 
60, pwo.UID_ITShopOrgFinal , 1  , c.UID_ComplianceRule from HelperPWOCompliance c join @PWOToCheck  pwo on c.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg 
61where not exists (select top 1 1 from PWODecisionHistory h where h.UID_PWODecisionHistory = c.UID_HelperPWOCompliance ) and not exists (select top 1 1 
62from PWODecisionHistory h where h.UID_ComplianceRule = c.UID_ComplianceRule and h.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg )   select @ElementIndexPWO
63 = 1 while @ElementIndexPWO <= @ElementCountPWO begin select top 1 @CountItemsException = c.CountItemsException , @UID_PWO = c.UID_PersonWantsOrg from 
64@PWOToCheck c where c.ElementIndex = @ElementIndexPWO if @CountItemsException > 0  begin select @SubLevelNumber = 0 select @Levelnumber_alt = -100 delete
65  @ElementBufferMulti_1  insert into @ElementBufferMulti_1 (Int1, UID1, UID2) select h.Levelnumber, h.UID_ComplianceRule, h.UID_PersonNonCompliant from
66 @hilfstab h where h.UID_PersonWantsOrg = @UID_PWO order by h.Levelnumber, h.UID_ComplianceRule, h.UID_PersonNonCompliant select @ElementCount = @@ROWCOUNT
67 select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @Levelnumber
68 = bu.Int1 , @uid_complianceRule = bu.UID1 , @UID_PersonNonCompliant = bu.UID2 from @ElementBufferMulti_1 bu where bu.ElementIndex = @ElementIndex if @levelnumber
69 <> @Levelnumber_alt begin  select @levelnumber_alt = @levelnumber select @SubLevelNumber = 0  select top 1 @uid_complianceRule_max = h.UID_ComplianceRule
70 from @hilfstab h where h.levelnumber = @levelnumber and h.UID_PersonWantsOrg = @UID_PWO order by isnull(h.RuleSeverity, 0.0) desc, uid_compliancerule 
71asc update @hilfstab set isHighestSeverity = 1 from @hilfstab h where h.UID_ComplianceRule = @uid_complianceRule_max and h.Levelnumber = @Levelnumber and
72 h.UID_PersonWantsOrg = @UID_PWO end else  begin select @SubLevelNumber = @SubLevelNumber + 1 end update @Hilfstab set SubLevelNumber = @SubLevelNumber
73 from @hilfstab h where h.UID_ComplianceRule = @uid_complianceRule and UID_PersonNonCompliant = @UID_PersonNonCompliant and h.Levelnumber = @levelnumber
74 and h.UID_PersonWantsOrg = @UID_PWO select @ElementIndex += 1 end  end  select @ElementIndexPWO += 1 end  end Update @PWOHelperPWO_Old set isToDelete 
75= 1 from @PWOHelperPWO_Old zk join (select * from @PWOHelperPWO_Old ) as d on zk.UID_PWODecisionRule = d.UID_PWODecisionRule and zk.uid_QERWorkingStep 
76= d.uid_QERWorkingStep and zk.levelnumber = d.levelnumber and zk.Sublevelnumber = d.Sublevelnumber and zk.UID_PersonWantsOrg = d.UID_PersonWantsOrg where
77 zk.UID_PersonInsteadOf = d.UID_PersonHead  and d.UID_PersonInsteadOf > ' '       declare @ElementAufruf QBM_YCursorbuffer insert into @ElementAufruf (UID1
78, UID2 , Ident1  , Int1  , Int2  , Int3  , UID3  , Ident2  ) select distinct s.UID_PWODecisionRule, s.UID_QERWorkingStep , dbo.QER_FCVRuleGUIDToFunctionName
79(s.UID_PWODecisionRule) , s.LevelNumber, s.SubLevelNumber , s.CountApprover , pwo.UID_PersonWantsOrg , pwo.Orderstate from @PWOToCheck  pwo join QERWorkingStep
80 s on s.UID_QERWorkingMethod = pwo.UID_QERWorkingMethod where  ( pwo.isOrderforWorkDesk = 1 or exists (select top 1 1 from BaseTree bo join BaseTree sh