dbo.CPL_ZITShopMakeDecisionCR

1     create   procedure CPL_ZITShopMakeDecisionCR (@SlotNumber int) as begin  declare @MyName nvarchar(64) = object_name(@@procid) declare @DecisionLevel
2 int declare @UID_Personwantsorg varchar(38) declare @GenProcID varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue
3('Common\ProcessState\UseGenProcIDFromPWO')) declare @Decision nvarchar(16) declare @Reason nvarchar(max) declare @uid_person varchar(38) declare @SQLcmd
4 nvarchar(max) declare @BasisObjectKey varchar(138) declare @xuser nvarchar(64) declare @xdate datetime declare @AutomaticReasonTrue nvarchar(128) declare
5 @AutomaticReasonFalse nvarchar(128) declare @ComplianceRules nvarchar(max) declare @GenProcID_R varchar(38) = dbo.QBM_FGISessionContext('') declare @ElementBuffer
6 QBM_YCursorBuffer declare @ElementCount int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent BEGIN TRY insert into @DBQueueCurrent
7(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent
8 cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end select @xuser = 'DBScheduler' select @xdate = GetUTCDate
9() insert into @ElementBuffer (UID1  , UID2  , UID3  , LongIdent1  , LongIdent2  , ObjectKey1  , Int1  ) select pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO
10(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.uid_personordered , isnull(s.AutomaticReasonTrue, ''), isnull(s.AutomaticReasonFalse,
11 ''), pwo.XObjectKey , pwo.DecisionLevel from @DBQueueCurrent p join personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg join pwohelperpwo ph
12 on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel = ph.levelnumber join QERWorkingStep s on ph.uid_QERWorkingStep = s.uid_QERWorkingStep
13  where ph.UID_PWODecisionRule = 'CPL-PWODecisionRule-CR'   and OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') select @ElementCount
14 = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <= @ElementCount begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  ,
15 @uid_person = bu.UID3  , @AutomaticReasonTrue = bu.LongIdent1  , @AutomaticReasonFalse = bu.LongIdent2  , @BasisObjectKey = bu.ObjectKey1  , @DecisionLevel
16 = bu.Int1  from @ElementBuffer bu where bu.ElementIndex = @ElementIndex   exec QBM_PSessionContextSet 'GenProcID', @GenProcID delete HelperPWOCompliance
17 where uid_personwantsorg = @uid_personwantsorg  exec QBM_PSessionContextSet 'GenProcID', @GenProcID insert into HelperPWOCompliance (UID_HelperPWOCompliance
18, UID_PersonWantsOrg, uid_complianceRule , UID_Person) select newid(), @uid_personwantsorg, xx.uid_ComplianceRule, xx.uid_person from ( select distinct
19 v.uid_complianceRule, v.uid_Person  from dbo.CPL_FTCCSPWOCheck(@uid_personwantsorg) v join ComplianceRule cr on v.uid_complianceRule = cr.uid_complianceRule
20  and cr.IsInActive = 0 and cr.IsWorkingCopy = 0  where (( (cr.DetectRuleTypeForPWO = 'NewRule' and v.isNewRule = 1 ) or (cr.DetectRuleTypeForPWO = 'AnyRule'
21      ) or (cr.DetectRuleTypeForPWO = 'UnGrantedRule' and v.isexceptiongranted = 0 ) )  or ( cr.isToGrantEver = 1 and ( v.isNewRule = 1 or v.isNewObject
22 = 1 ) ) )  and not exists ( select top 1 1 from HelperPWOCompliance x where x.UID_PersonWantsOrg = @uid_personwantsorg and x.uid_complianceRule = v.uid_complianceRule
23 and x.uid_Person = v.uid_Person ) ) as xx select @ComplianceRules = null select @ComplianceRules = string_agg(left(c.Ident_ComplianceRule, 64) , ',') 
24from HelperPWOCompliance hc join ComplianceRule c on hc.UID_ComplianceRule = c.UID_ComplianceRule  and c.IsInActive = 0 and c.IsWorkingCopy = 0  where 
25hc.UID_PersonWantsOrg = @UID_Personwantsorg if @ComplianceRules > ' ' begin   select @Decision = 'False' if @AutomaticReasonFalse > ' ' begin select @Reason
26 = case when @AutomaticReasonFalse like concat('#', 'L', 'D', 'S', '#', '%') then '' else concat( '#', 'L', 'D', 'S', '#' ) end + @AutomaticReasonFalse
27 + '|' + @ComplianceRules + '|' end else begin select @Reason = '#LDS#At least one compliance rule has been violated: {0}.|' + @ComplianceRules + '|' end
28 end else  begin select @Decision = 'True' if @AutomaticReasonTrue > ' ' begin select @Reason = @AutomaticReasonTrue end else begin select @Reason = '#LDS#No compliance rule has been violated.|'
29 end end select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''' and DecisionLevel = ' + STR(@DecisionLevel)  exec QBM_PJobCreate_HOCallMethod
30 @objecttype = 'personwantsorg' , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'MakeDecision' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT
31 , @param1 = 'sa' , @param2 = @decision , @param3 = @Reason , @Retries = 3 , @isToFreezeOnError = 1  , @BasisObjectKey = @BasisObjectKey , @checkForExisting
32 = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'  select @ElementIndex += 1 end  END TRY 
33BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: exec QBM_PSessionContextSet 'GenProcID', @GenProcID_R
34 return end 
35