dbo.QER_ZITShopMakeDecisionRI

1    create   procedure QER_ZITShopMakeDecisionRI (@SlotNumber int) as begin  declare @UID_Personwantsorg varchar(38) declare @GenProcID varchar(38
2) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) declare @Decision nvarchar
3(16) declare @Reason nvarchar(255) declare @cond nvarchar(max) declare @BasisObjectKey varchar(138) declare @CountOfRows int declare @DecisionLevel int
4 declare @RiskIndexLimit float declare @objectkeyassignment varchar(138) = null declare @TargetTableOrder varchar(30) = null declare @ObjectKeyOrder varchar
5(138) = null declare @ObjectKeyElementUsedInAssign varchar(138) = null declare @SQLCmd nvarchar(max) declare @AutomaticReasonTrue nvarchar(128) declare
6 @AutomaticReasonFalse nvarchar(128) declare @DebugSwitch int = 0 declare @TargetTableAssign1 varchar(30) = null declare @TargetTableAssign2 varchar(30
7) = null declare @ObjectKeyFinal1 varchar(138) = null declare @ObjectKeyFinal2 varchar(138) = null declare @ElementBuffer QBM_YCursorBuffer declare @ElementCount
8 int declare @ElementIndex int declare @DBQueueCurrent QBM_YDBQueueCurrent SET XACT_ABORT OFF BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, 
9UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast
10) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end insert into @ElementBuffer (UID1  , UID2  , LongIdent1  , LongIdent2  , ObjectKey1
11  , Int1  , Ident3  , ObjectKey2 , ObjectKey3  , ContentShort  ) select pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID,
12 @CfgUseGenProcID) as GenProcID , isnull(ds.AutomaticReasonTrue, ''), isnull(ds.AutomaticReasonFalse, ''), pwo.XObjectKey , pwo.DecisionLevel , dbo.QBM_FCVFloatToString
13( dbo.QBM_FCVStringToFloat( ds.WhereClause , 1.0))  , pwo.ObjectKeyAssignment, pwo.ObjectKeyOrdered , pwo.ObjectKeyElementUsedInAssign from @DBQueueCurrent
14 p join personwantsorg pwo on p.uid_parameter = pwo.uid_personwantsorg join PWOHelperPWO ph on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel
15 = ph.levelnumber join QERWorkingStep ds on ph.UID_QERWorkingStep = ds.UID_QERWorkingStep where ph.UID_PWODecisionRule = 'QER-PWODecisionRule-RI'   and
16 OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') select @ElementCount = @@ROWCOUNT select @ElementIndex = 1 while @ElementIndex <=
17 @ElementCount begin select top 1 @uid_personwantsorg = bu.UID1  , @GenProcID = bu.UID2  , @AutomaticReasonTrue = bu.LongIdent1  , @AutomaticReasonFalse
18 = bu.LongIdent2  , @BasisObjectKey = bu.ObjectKey1  , @DecisionLevel = bu.Int1  , @RiskIndexLimit = dbo.QBM_FCVStringToFloat(bu.Ident3, 0)  , @ObjectKeyAssignment
19 = bu.ObjectKey2 , @ObjectKeyOrder = bu.ObjectKey3  , @ObjectKeyElementUsedInAssign = bu.ContentShort from @ElementBuffer bu where bu.ElementIndex = @ElementIndex
20  select @TargetTableAssign1 = null select @TargetTableAssign2 = null select @ObjectKeyFinal1 = null select @ObjectKeyFinal2 = null if @objectkeyassignment
21 > ' ' begin if dbo.QBM_FCVObjectkeyToElement('UID_DialogTable', @ObjectKeyAssignment) <> 'QER-T-ESetHasEntitlement' begin    select @ObjectKeyFinal1 =
22 dbo.QBM_FCVObjectKeyMNToParentKey(@objectkeyassignment, 0) select top 1 @TargetTableAssign1 = r.ParentTable from QBM_VQBMRelation r join DialogColumn 
23c on r.UID_DialogTableParent = c.UID_DialogTable and c.ColumnName = 'RiskIndex' where r.ChildTable = dbo.QBM_FCVObjectkeyToElement('TableName', @objectkeyassignment
24) and r.IsMNRelation = 1 and @ObjectKeyFinal1 like '<Key><T>' + r.ParentTable + '</T>%'  select @ObjectKeyFinal2 = dbo.QBM_FCVObjectKeyMNToParentKey(@objectkeyassignment
25, 1) select top 1 @TargetTableAssign2 = r.ParentTable from QBM_VQBMRelation r join DialogColumn c on r.UID_DialogTableParent = c.UID_DialogTable and c.ColumnName
26 = 'RiskIndex' where r.ChildTable = dbo.QBM_FCVObjectkeyToElement('TableName', @objectkeyassignment) and r.IsMNRelation = 1 and @ObjectKeyFinal2 like '<Key><T>'
27 + r.ParentTable + '</T>%' if @DebugSwitch > 0 begin print @TargetTableAssign1 print @objectkeyFinal1 print @TargetTableAssign2 print @objectkeyFinal2 
28end end else begin   select @ObjectKeyFinal1 = @ObjectKeyElementUsedInAssign select top 1 @TargetTableAssign1 = x.tab from ( select dbo.QBM_FCVObjectkeyToElement
29('TableName', @ObjectKeyElementUsedInAssign) as tab ) x join DialogTable t with (readpast) on x.tab = t.TableName join DialogColumn c on t.UID_DialogTable
30 = c.UID_DialogTable and c.ColumnName = 'RiskIndex' if @DebugSwitch > 0 begin print @TargetTableAssign1 print @ObjectKeyFinal1 end end end else begin if
31 @ObjectKeyOrder > ' ' begin select top 1 @TargetTableOrder = x.tab from ( select dbo.QBM_FCVObjectkeyToElement('TableName', @ObjectKeyOrder) as tab ) 
32x join DialogTable t with (readpast) on x.tab = t.TableName join DialogColumn c on t.UID_DialogTable = c.UID_DialogTable and c.ColumnName = 'RiskIndex'
33 if @DebugSwitch > 0 begin print @TargetTableOrder print @ObjectKeyOrder end end end if @TargetTableAssign1 > ' ' or @TargetTableAssign2 > ' ' or @TargetTableOrder
34 > ' ' begin select @SQLCmd = '
35				if 1 = 0
36				  ' + case when @TargetTableAssign1 is null then '' else ' or ( select top 1 isnull(Riskindex, 0.0) 
37										from '
38 + @TargetTableAssign1 + ' 
39										where XObjectKey = ''' + @ObjectKeyFinal1 + ''' 
40									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + 
41'
42					' end + case when @TargetTableAssign2 is null then '' else ' or ( select top 1 isnull(Riskindex, 0.0) 
43										from ' + @TargetTableAssign2 
44+ ' 
45										where XObjectKey = ''' + @ObjectKeyFinal2 + ''' 
46									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + '
47					' end + case when
48 @TargetTableOrder is null then '' else ' or ( select top 1 isnull(Riskindex, 0.0) 
49										from ' + @TargetTableOrder + ' 
50										where XObjectKey = '''
51 + @ObjectKeyOrder + ''' 
52									) >= ' + dbo.QBM_FCVFloatToString(@RiskIndexLimit) + '
53					' end + '
54				 begin
55					print '''' -- 0 rows affected
56				 end
57				else
58				 begin
59					select 1 as Decision
60				 end
61					'
62 end else begin  select @SQLCmd = '
63					select 1 as Decision' end if @DebugSwitch > 0 begin print @SQLCmd end  BEGIN TRY exec sp_executesql @SQLcmd select
64 @CountOfRows = @@Rowcount END TRY BEGIN CATCH select @CountOfRows = -1 END CATCH if @CountOfRows > 0 begin  select @decision = 'True' if @AutomaticReasonTrue
65 > ' ' begin select @Reason = @AutomaticReasonTrue end else begin select @reason = '#LDS#Automatic system approval with method RI : Riskindex lower then limit.|'
66 end end if @CountOfRows = 0  begin select @decision = 'False'  if @AutomaticReasonFalse > ' ' begin select @Reason = @AutomaticReasonFalse end else begin
67 select @reason = '#LDS#Automatic system approval with method RI : Riskindex equal or higher then limit.|' end end if @CountOfRows < 0  begin select @decision
68 = 'False' select @reason = '#LDS#Automatic system approval: Condition caused runtime error using method RI.|' end  select @cond = 'uid_personwantsorg = '''
69 + rtrim(@uid_personwantsorg) + ''' and DecisionLevel = ' + STR(@DecisionLevel)  exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg' , @whereclause
70 = @cond , @save = 1 , @MethodName = 'MakeDecision' , @GenProcID = @GenprocID , @ObjectKeysAffected = DEFAULT , @param1 = 'sa' , @param2 = @decision , 
71@param3 = @reason , @Retries = 3 , @isToFreezeOnError = 1  , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'
72  select @ElementIndex += 1 end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default, @SQLcmd RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return
73 end 
74