dbo.QER_ZITShopCheckDecision

1      create   procedure QER_ZITShopCheckDecision (@SlotNumber int) as begin  declare @SQLcmd nvarchar(1000) declare @uid_personwantsorg varchar
2(38) declare @GenProcID varchar(38) declare @BasisObjectKey varchar(138)  declare @AbortMethod nvarchar(64) declare @UID_PWOStateFinalError varchar(38)
3 = null declare @EscalateIfNoApprover bit declare @UID_PWOHelperPWO varchar(38) declare @UID_PersonHead varchar(38) declare @MinutesReminder int declare
4 @MailExists int declare @ObjectkeyForMail varchar(138) declare @ObjectkeySForMail QBM_YParameterList  declare @WhereclauseForMail nvarchar(1000) declare
5 @CurrentDecisionLevel int declare @SubLevelNumberString varchar(16) declare @UID_Task varchar(38) declare @DBQueueElements_spezial QBM_YDBQueueRaw declare
6 @Dummystring nvarchar(100) declare @MyName nvarchar(64) = object_name(@@procid) declare @DecisionLevel int declare @XUser nvarchar(64) = object_name(@@procid
7) declare @Debugswitch int = 0 declare @MaxDate2200 datetime = '2200-01-01' declare @Mindate1899 datetime = '1899-12-30' declare @GenProcID_R varchar(38
8) = dbo.QBM_FGISessionContext('') declare @XUser_R nvarchar(64) = dbo.QBM_FGISessionContext('XUser') declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit
9(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare
10 @DBQueueCurrent QBM_YDBQueueCurrent declare @DaysToAbort QBM_YDBQueueCurrent declare @Helper QER_YPWOHelperReminder BEGIN TRY insert into @DBQueueCurrent
11(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent
12 cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end  declare @DBQueueElements_36443 QBM_YDBQueueRaw declare
13 @Task36443 varchar(38) = 'QER-K-ITSHOPOrder-RESET' insert into @DBQueueElements_36443(Object, GenProcID) select x.UID_PersonWantsOrg, x.GenProcID from
14 ( select pwo.UID_PersonWantsOrg, pwo.OrderState, pwo.DecisionLevel, h.SubLevelNumber , sum( case h.Decision when 'P' then 1 else 0 end ) as CountPositive
15 , COUNT(*) as CountDecider , max(ws.CountApprover) as CountApprover   , po.DecisionPossible  , max(dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, cu.GenProcID
16, @CfgUseGenProcID)) as GenProcID  from @DBQueueCurrent cu join PersonWantsOrg pwo with (readpast) on cu.UID_Parameter = pwo.UID_PersonWantsOrg join PWOHelperPWO
17 h with (readpast) on pwo.UID_PersonWantsOrg = h.UID_PersonWantsOrg and pwo.DecisionLevel = h.LevelNumber join QERWorkingStep ws on ws.UID_QERWorkingMethod
18 = pwo.UID_QERWorkingMethod and ws.LevelNumber = pwo.DecisionLevel and ws.SubLevelNumber = h.SubLevelNumber join PWODecisionRule ru on ws.UID_PWODecisionRule
19 = ru.UID_PWODecisionRule and ru.UID_Task is null  cross apply dbo.QER_FTPWODecisionPossible(pwo.UID_PersonWantsOrg, pwo.DecisionLevel, h.SubLevelNumber
20) po where pwo.OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') group by pwo.UID_PersonWantsOrg, pwo.OrderState, pwo.DecisionLevel
21, h.SubLevelNumber, po.DecisionPossible ) as x where x.CountApprover = -1 and x.DecisionPossible = 0 and x.CountPositive = x.CountDecider and x.CountPositive
22 > 0 group by x.UID_PersonWantsOrg, x.GenProcID if @@ROWCOUNT > 0 begin exec QBM_PDBQueueInsert_Bulk @Task36443, @DBQueueElements_36443 delete  @DBQueueCurrent
23 from @DBQueueCurrent cu join @DBQueueElements_36443 el on cu.UID_Parameter = el.Object end   Declare @PWOOhneEntscheider QBM_YCursorBuffer insert into
24 @PWOOhneEntscheider (UID1, UID2, ObjectKey1, Ident1, Bit1, Int1) select pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID
25, @CfgUseGenProcID) as GenProcID , pwo.XObjectKey, case pwo.Orderstate when 'OrderProduct' then 'Abort' else 'AbortOrder' end , ws.EscalateIfNoApprover
26 , pwo.DecisionLevel   from @DBQueueCurrent p join personwantsorg pwo with (readpast) on p.uid_parameter = pwo.uid_personwantsorg join QERWorkingStep ws
27 on pwo.UID_QERWorkingMethod = ws.UID_QERWorkingMethod and pwo.DecisionLevel = ws.LevelNumber where not exists ( select top 1 1 from PWOHelperPWO ph with
28 (readpast) where pwo.uid_personwantsorg = ph.uid_personwantsorg and ws.LevelNumber = ph.LevelNumber and ws.SubLevelNumber = ph.SubLevelNumber and (ws.UID_PWODecisionRule
29 = ph.UID_PWODecisionRule or ph.UID_PWODecisionRule = 'QER-PWODecisionRule-QP' )  and ph.RulerLevel in (0, 1) )    and pwo.OrderState in ('OrderProduct'
30, 'OrderProlongate', 'OrderUnsubscribe')  union select pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID
31) as GenProcID , pwo.XObjectKey, case pwo.Orderstate when 'OrderProduct' then 'Abort' else 'AbortOrder' end , 0  , pwo.DecisionLevel   from @DBQueueCurrent
32 p join personwantsorg pwo with (readpast) on p.uid_parameter = pwo.uid_personwantsorg where not exists (select top 1 1 from QERWorkingStep ws where pwo.UID_QERWorkingMethod
33 = ws.UID_QERWorkingMethod ) and pwo.UID_QERWorkingMethod > ' ' and pwo.OrderState = 'OrderProduct'  select @ElementCount = @@ROWCOUNT select @ElementIndex
34 = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @uid_personwantsorg = bu.UID1 
35, @GenProcID = bu.UID2 , @BasisObjectKey = bu.ObjectKey1 , @AbortMethod = bu.Ident1 , @EscalateIfNoApprover = bu.Bit1 , @DecisionLevel = bu.Int1 from @PWOOhneEntscheider
36 bu where bu.ElementIndex = @ElementIndex  if @EscalateIfNoApprover = 0 begin select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) 
37+ N'''' select @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg) exec QBM_PJobCreate_HOCallMethod 'personwantsorg', @SQLcmd,
38 @save = 1 , @Methodname = @AbortMethod , @GenProcID = @GenProcID  , @ObjectKeysAffected = DEFAULT , @Param1 = '#LDS#Automatic system approval: No approver available (in this shop).|'
39 , @Param2 = @UID_PWOStateFinalError , @Retries = 3 , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'
40  , @BasisObjectKey = @BasisObjectKey end else begin select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''' and DecisionLevel = '
41 + STR(@DecisionLevel) exec QBM_PJobCreate_HOCallMethod 'personwantsorg', @SQLcmd, @save = 1 , @Methodname = 'ESCALATE' , @GenProcID = @GenProcID  , @ObjectKeysAffected
42 = DEFAULT , @Param1 = 'sa' , @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to no approver available.|' , @Retries = 3 , @checkForExisting
43 = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'  , @BasisObjectKey = @BasisObjectKey , @isToFreezeOnError
44 = 1 end  delete  @DBQueueCurrent from @DBQueueCurrent cu where cu.UID_Parameter = @uid_personwantsorg if @Debugswitch > 0 begin print 'Zurücksetzen 1'
45 end select @ElementIndex += 1 end    Declare @PWOZuWenigEntscheider QBM_YCursorBuffer insert into @PWOZuWenigEntscheider (UID1, UID2, ObjectKey1, Ident1
46, Bit1, Int1) select distinct pwo.uid_personwantsorg , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.XObjectKey
47, case pwo.Orderstate when 'OrderProduct' then 'Abort' else 'AbortOrder' end , ws.EscalateIfNoApprover , pwo.DecisionLevel from @DBQueueCurrent p join 
48personwantsorg pwo with (readpast) on p.uid_parameter = pwo.uid_personwantsorg join QERWorkingStep ws on pwo.UID_QERWorkingMethod = ws.UID_QERWorkingMethod
49 and pwo.decisionlevel = ws.levelnumber cross apply dbo.QER_FTPWODecisionPossible(pwo.uid_personwantsorg, ws.LevelNumber, ws.SubLevelNumber) dp where dp.DecisionPossible
50 = -1   and pwo.OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY
51 - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @uid_personwantsorg = bu.UID1 , @GenProcID 
52= bu.UID2 , @BasisObjectKey = bu.ObjectKey1 , @AbortMethod = bu.Ident1 , @EscalateIfNoApprover = bu.Bit1 , @DecisionLevel = bu.Int1 from @PWOZuWenigEntscheider
53 bu where bu.ElementIndex = @ElementIndex  if @EscalateIfNoApprover = 0 begin select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) 
54+ N'''' select @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg) exec QBM_PJobCreate_HOCallMethod 'personwantsorg', @SQLcmd,
55 @save = 1 , @Methodname = @AbortMethod , @GenProcID = @GenProcID  , @ObjectKeysAffected = DEFAULT , @Param1 = '#LDS#Automatic system approval: Not enough approvers available.|'
56 , @Param2 = @UID_PWOStateFinalError , @Retries = 3 , @checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'
57  , @BasisObjectKey = @BasisObjectKey end else begin select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg) + N''' and DecisionLevel = '
58 + STR(@DecisionLevel) exec QBM_PJobCreate_HOCallMethod 'personwantsorg', @SQLcmd, @save = 1 , @Methodname = 'ESCALATE' , @GenProcID = @GenProcID  , @ObjectKeysAffected
59 = DEFAULT , @Param1 = 'sa' , @Param2 = '#LDS#Automatic system approval: Decision "escalate" due to not enough approvers available.|' , @Retries = 3 , 
60@checkForExisting = 1  , @WhereClauseAdditional = ' OrderState in(''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'')'  , @BasisObjectKey = @BasisObjectKey
61 , @isToFreezeOnError = 1 end  delete  @DBQueueCurrent from @DBQueueCurrent cu where cu.UID_Parameter = @uid_personwantsorg if @Debugswitch > 0 begin print
62 'Zurücksetzen 2' end select @ElementIndex += 1 end   insert into @DaysToAbort(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue
63, UID_Parameter, UID_SubParameter, GenProcID from @DBQueueCurrent Declare @PWOPositiv QBM_YCursorBuffer insert into @PWOPositiv (UID1) select distinct 
64dr.UID_Task from @DBQueueCurrent p join personwantsorg pwo with (readpast) on p.uid_parameter = pwo.uid_personwantsorg join PWOHelperPWO ph with (readpast
65) on pwo.uid_personwantsorg = ph.uid_personwantsorg and pwo.decisionlevel = ph.levelnumber join pwodecisionrule dr on ph.UID_PWODecisionRule = dr.UID_PWODecisionRule
66 where dr.UID_Task > ' '   and OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') select @ElementCount = @@ROWCOUNT select @ElementIndex
67 = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @UID_Task = bu.UID1 from @PWOPositiv
68 bu where bu.ElementIndex = @ElementIndex delete  @DBQueueElements_spezial insert into @DBQueueElements_spezial (object, subobject, genprocid) select x.uid
69, null, x.GenProcID from ( select pwo.uid_personwantsorg as uid , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID
70 from @DBQueueCurrent p join personwantsorg pwo with (readpast) on p.uid_parameter = pwo.uid_personwantsorg join PWOHelperPWO ph with (readpast) on pwo.uid_personwantsorg
71 = ph.uid_personwantsorg and pwo.decisionlevel = ph.levelnumber join pwodecisionrule dr on ph.UID_PWODecisionRule = dr.UID_PWODecisionRule   and OrderState
72 in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') and dr.UID_Task = @UID_Task ) as x  delete  @DBQueueCurrent from @DBQueueCurrent cu where cu.UID_Parameter
73 in (select object from @DBQueueElements_spezial)  delete  @DBQueueElements_spezial  from @DBQueueElements_spezial e join PersonWantsOrg pwo with (readpast
74) on e.Object = pwo.UID_PersonWantsOrg where @UID_Task = 'QER-K-ShoppingRackMakeDecisionEX' and (pwo.DecisionLevel > 0 or (pwo.DecisionLevel = 0 and dbo.QBM_FGIConfigparmValue
75('QER\ITShop\DecisionOnInsert') > ' ' ) )   exec QBM_PDBQueueInsert_Bulk @UID_Task, @DBQueueElements_spezial  if @Debugswitch > 0 begin print 'Zurücksetzen 3'
76 end select @ElementIndex += 1 end   Declare @PWOZeit QBM_YCursorBuffer insert into @PWOZeit (UID1, UID2, ObjectKey1, Ident1) select distinct p.uid_parameter
77 , dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, p.GenProcID, @CfgUseGenProcID) as GenProcID , pwo.XObjectKey, case pwo.Orderstate when 'OrderProduct' then
78 'Abort' else 'AbortOrder' end from personwantsorg pwo with (readpast) join @DaysToAbort p on pwo.uid_personwantsorg = p.uid_parameter join PWOHelperPWO
79 h with (readpast) on pwo.uid_personwantsorg = h.uid_personwantsorg join QERWorkingMethod wm on pwo.UID_QERWorkingMethod = wm.UID_QERWorkingMethod left
80 outer join  ( select h1.UID_PersonWantsOrg, MAX(h1.XDateInserted) as OrderDate from @DaysToAbort p1 join PWODecisionHistory h1 on p1.UID_Parameter = h1.UID_PersonWantsOrg