dbo.CPL_ZITShopMakeDecisionOH

1    create   procedure CPL_ZITShopMakeDecisionOH (@SlotNumber int) as begin  declare @DecisionLevel int declare @UID_Personwantsorg varchar(38) 
2declare @GenProcID varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO'
3)) declare @Decision nvarchar(16) declare @Reason nvarchar(255) declare @uid_person varchar(38) declare @Levelnumber int declare @SQLcmd nvarchar(max) 
4declare @IsDecision bit declare @AnzahlVerstoss int declare @AnzahlGenehmigt int declare @BasisObjectKey varchar(138) declare @GenProcID_R varchar(38) 
5= dbo.QBM_FGISessionContext('') declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent
6 declare @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|', 0)  declare @OrderState nvarchar(16) BEGIN TRY insert into
7 @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from
8 QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end insert into @ElementBuffer (UID1  , UID2
9  , UID3  , Int1  , ObjectKey1  , Int2  , LongIdent1  ) select distinct pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID,
10 @CfgUseGenProcID) as GenProcID , pwo.uid_personordered, ph.levelnumber, pwo.XObjectKey , pwo.DecisionLevel, pwo.OrderState from @DBQueueCurrent p join
11 personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg join PWOHelperPWO ph on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel
12 = ph.levelnumber  where ph.UID_PWODecisionRule = 'CPL-PWODecisionRule-OH'  select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex
13 <= @ElementCount begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  , @uid_person = bu.UID3  , @levelnumber = bu.Int1  , @BasisObjectKey
14 = bu.ObjectKey1  , @DecisionLevel = bu.Int2 , @OrderState = bu.LongIdent1 from @ElementBuffer bu where bu.ElementIndex = @ElementIndex  select @IsDecision
15 = 0 select @Decision = '' select @Reason = ''  if not exists (select top 1 1 from HelperPWOCompliance where uid_PersonWantsOrg = @UID_Personwantsorg) 
16begin   select @Decision = 'True'  select @Reason = '#LDS#No compliance rule has been violated., (missing CC step?) detected in Rule OH).|' select @IsDecision
17 = 1 goto istEntschieden end   if exists (select top 1 1 from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule
18  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0  where pir.uid_PersonWantsOrg = @UID_Personwantsorg and isnull(cr.IsExceptionAllowed,0) = 0 ) begin select
19 @Decision = 'False'  select @Reason = '#LDS#Compliance rules have been violated that have no valid exceptions. Rule: {0}.|OH|' select @IsDecision = 1 
20goto istEntschieden end   if exists (select top 1 1 from PWOHelperPWO where uid_personWantsOrg = @UID_Personwantsorg and LevelNumber = @levelnumber and
21 uid_personHead is null and decision = 'R' ) begin select @Decision = 'False'  select @Reason = '#LDS#There is no approver available for rule {0}.|OH|'
22 select @IsDecision = 1 end  if exists (select top 1 1 from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule
23  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0  where pir.uid_PersonWantsOrg = @UID_Personwantsorg and cr.isToGrantEver = 1 ) begin select @Decision 
24= '' select @Reason = '#LDS#At least one rule has been violated that requires approval for every exception. Rule: {0}.|OH|' select @IsDecision = 0 goto
25 istEntschieden end   select @AnzahlVerstoss = count(*) from HelperPWOCompliance where uid_PersonWantsOrg = @UID_Personwantsorg select @AnzahlGenehmigt
26 = count(*) from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule and pir.uid_PersonWantsOrg = @UID_Personwantsorg
27  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0    and cr.DetectRuleTypeForPWO <> 'AnyRule'  join personinBaseTree  pin on pin.uid_org  = cr.uid_nonCompliance
28 and pin.uid_person = pir.uid_person and pin.IsExceptiongranted = 1 and pin.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0 and pin.XOrigin
29 > 0 if (@AnzahlVerstoss = @AnzahlGenehmigt) begin select @Decision = 'True'  select @Reason = '#LDS#All affected compliance rules have been granted exceptions. Rule: {0}.|OH|'
30 select @IsDecision = 1 end else  begin select @IsDecision = 0 end  if exists (select top 1 1 from HelperPWOCompliance where uid_PersonWantsOrg = @UID_Personwantsorg
31) begin   if exists (select top 1 1 from HelperPWOCompliance pir join ComplianceRule cr on pir.uid_ComplianceRule = cr.uid_ComplianceRule  and cr.IsInActive
32 = 0 and cr.IsWorkingCopy = 0  where pir.uid_PersonWantsOrg = @UID_Personwantsorg and isnull(cr.IsExceptionAllowed,0) = 0 ) begin select @Decision = 'False'
33  select @Reason = '#LDS#Compliance rules have been violated that have no valid exceptions. Rule: {0}.|OH|' select @IsDecision = 1 end end istEntschieden:
34 if @IsDecision = 1 begin select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''' and DecisionLevel = ' + STR(@DecisionLevel)  
35exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'MakeDecision' , @GenProcID = @GenprocID
36 , @ObjectKeysAffected = DEFAULT , @param1 = 'sa' , @param2 = @decision , @param3 = @reason , @Retries = 3 , @isToFreezeOnError = 1  , @BasisObjectKey 
37= @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'  end
38  else begin exec QBM_PSessionContextSet 'GenProcID', @GenProcID update PWOHelperPWO set decision = '' where levelnumber = @levelnumber and uid_personWantsOrg
39 = @uid_personwantsorg and Decision in ( 'R')  update PWOHelperPWO set Decision = '' from PWOHelperPWO ph join ( select top 1 h.UID_ComplianceRule from
40 PWOHelperPWO h join ComplianceRule cr on h.UID_ComplianceRule = cr.UID_ComplianceRule where h.levelnumber = @levelnumber and h.UID_PersonWantsOrg = @uid_personwantsorg
41 and h.Decision in ('+') order by isnull(cr.RuleSeverity, 0.0) desc, cr.UID_ComplianceRule asc ) as x on x.UID_ComplianceRule = ph.UID_ComplianceRule where
42 ph.levelnumber = @levelnumber and ph.UID_PersonWantsOrg = @uid_personwantsorg and ph.Decision in ('+') and @OrderState = 'OrderProlongate'  end select
43 @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet
44 'GenProcID', @GenProcID_R return end 
45