dbo.QER_TUPersonWantsOrg

1create   trigger QER_TUPersonWantsOrg on PersonWantsOrg  for UPDATE not for Replication as begin  declare @uid_Personwantsorg varchar(38) declare
2 @isNewDecisionLevel bit declare @DecisionOnInsert int = 0 declare @IsOrderStarted bit declare @PWOBuffer QBM_YCursorBuffer declare @ElementCount int declare
3 @ElementIndex int declare @ElementLast int declare @SQLCmd nvarchar(max) declare @CursorBuffer QBM_YCursorBuffer declare @BasisObjectKey varchar(138) 
4declare @PWOsAreFinished QBM_YParameterList declare @CountPWOsAreFinished int declare @PWOsToResetGenProcID QBM_YParameterList declare @CountPWOsToResetGenProcID
5 int declare @GenProcIDToUse varchar(38) declare @UID_PWOStateFinalError varchar(64) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue
6('Common\ProcessState\UseGenProcIDFromPWO')) declare @PWOsToFillHelper QBM_YParameterlist      declare @PWOsToFillHasObject QBM_YParameterlist declare 
7@PWOHelperPWOToDeleteImmediate QBM_YParameterList declare @PWOHelperPWOToDeleteLater QBM_YParameterList declare @DeleteWhereClause nvarchar(max) declare
8 @newDecisionLevel int declare @GenProcID varchar(38) = dbo.QBM_FGISessionContext('') declare @PWOTaskToUse varchar(38) = dbo.QER_FGIPWOTaskToUse() declare
9 @XUser nvarchar(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists
10 (select top 1 1 from deleted) goto start return start:  if update(UID_ITShopOrgFinal) and dbo.QBM_FGIColumnUpdatedOthers ('PersonWantsOrg', 'UID_ITShopOrgFinal'
11, columns_updated()) = 0 and not exists (select top 1 1 from inserted i where i.DecisionLevel > 0 ) begin  goto ende end if dbo.QBM_FGIConfigparmValue(
12'QER\ITShop\DecisionOnInsert') > ' ' begin select @DecisionOnInsert = 1 end if update(Orderstate) or update(UID_QERWorkingMethod) begin if exists (select
13 top 1 1  from inserted i join PersonWantsOrg pwo on i.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg join QER_VPWOOrderState s on pwo.OrderState = s.OrderState
14 where  sign(len(isnull(pwo.UID_QERWorkingMethod, ''))) ^ s.HasWorkingMethod = 1 ) begin raiserror('#LDS#Invalid combination of Orderstate and QERWorkingMethod.|'
15, 18, 1) with nowait end end if update(uid_Org) or update(UID_ITShopOrgFinal)  or update(OrderState) begin insert into @PWOsToFillHasObject(Parameter1,
16 Parameter2) select pwo.UID_PersonWantsOrg , @GenProcID from personwantsorg pwo join deleted d on pwo.uid_personwantsorg = d.uid_personwantsorg   and pwo.OrderState
17 in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe') where (isnull(pwo.uid_Org, '') <> isnull(d.UID_Org, '') or isnull(pwo.UID_ITShopOrgFinal, ''
18) <> isnull(d.UID_ITShopOrgFinal, '')  or ( isnull(pwo.OrderState, '') = 'OrderProlongate' and isnull(d.OrderState, '') <> 'OrderProlongate' )  ) end  
19if not update(UID_PersonWantsOrgParent) begin      if dbo.QBM_FGIColumnUpdatedOthers ('PersonWantsOrg', 'OrderState', columns_updated()) = 1 and dbo.QBM_FGIColumnUpdatedOthers
20 ('PersonWantsOrg', 'DecisionLevel', columns_updated()) = 1 begin insert into @PWOsToFillHelper(Parameter1, Parameter2, Parameter3, HasContentFull) select
21 pwo.uid_personwantsorg , @GenProcID  , '1' as CurrentLevelOnly  , case when isnull(d.DecisionLevel,0) <> isnull(pwo.DecisionLevel, 0) then 1 when d.OrderState
22 not in ( 'OrderProlongate' , 'OrderUnsubscribe') and pwo.OrderState in ( 'OrderProlongate' , 'OrderUnsubscribe') then 1 else 0 end as isNewDecisionLevel
23 from deleted d join personwantsorg pwo on d.uid_personwantsorg = pwo.uid_personwantsorg where datediff(ss, isnull(d.Xdateinserted, '1997-01-01') , getUTCDate
24()) < 10 end  end    if dbo.QBM_FGIColumnUpdatedOthers ('PersonWantsOrg', 'OrderState', columns_updated()) = 1 and dbo.QBM_FGIColumnUpdatedOthers ('PersonWantsOrg'
25, 'DecisionLevel', columns_updated()) = 1  and (update(UID_PersonHead) or update(ReasonHead) or update(DateHead) ) begin declare @DBQueueElements_01 QBM_YDBQueueRaw
26 insert into @DBQueueElements_01 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select pwo.uid_personwantsorg as uid from deleted 
27d join personwantsorg pwo on d.uid_personwantsorg = pwo.uid_personwantsorg where (isnull(d.DateHead, '1899-12-30') <> isnull(pwo.DateHead, '1899-12-30'
28) or isnull(d.ReasonHead, N'') <> isnull(pwo.ReasonHead, N'') ) and ( isnull(d.UID_PersonHead, '') <> isnull(pwo.UID_PersonHead, '')   or exists (select
29 top 1 1 from pwohelperpwo h where h.uid_personwantsorg = pwo.uid_personwantsorg and h.LevelNumber = pwo.decisionlevel  and (h.UID_PersonAdditional > ' '
30 or h.UID_PersonInsteadOf > ' '  or h.Decision = '-' ) ) ) union select h.UID_PersonWantsOrg from deleted d join PersonWantsOrg pwo on d.uid_personwantsorg
31 = pwo.uid_personwantsorg join PWOHelperPWO h on h.UID_PWORulerOrigin = pwo.UID_PersonWantsOrg    where d.OrderState in ('Assigned', 'Granted', 'New', 
32'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') and pwo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' ) ) as x  exec QBM_PDBQueueInsert_Bulk
33 'QER-K-ShoppingRackPWOHelperPWO', @DBQueueElements_01 end  if update(UID_Department) or update(UID_ProfitCenter) begin if @PWOTaskToUse > ' ' begin declare
34 @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select d.UID_PersonWantsOrg
35 as uid from deleted d join PersonWantsOrg pwo on d.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg where isnull(d.UID_Department, '') <> isnull(pwo.UID_Department
36, '') or isnull(d.UID_ProfitCenter, '') <> isnull(pwo.UID_ProfitCenter, '') ) as x  exec QBM_PDBQueueInsert_Bulk @PWOTaskToUse, @DBQueueElements_02 end
37  end  if update(OrderState) begin  declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid
38, null, @GenProcID from (select pwo.UID_PersonWantsOrg as uid from deleted d join personwantsorg pwo on d.uid_personwantsorg = pwo.uid_Personwantsorg  
39  where d.OrderState in ('Assigned', 'OrderProlongate', 'OrderUnsubscribe' ) and pwo.OrderState in ('Aborted', 'Dismissed', 'Unsubscribed' ) ) as x exec
40 QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckWaiting', @DBQueueElements_03  declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04
41 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select pwo.UID_PersonWantsOrg as uid from deleted d join personwantsorg pwo on d.uid_PersonWantsOrg
42 = pwo.uid_PersonWantsOrg   join QER_VPWOPersonPropertyReset r on pwo.UID_PersonWantsOrg = r.UID_PersonWantsOrg where d.OrderState in ('Assigned', 'Granted'
43, 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-ITShopRepairClosed', @DBQueueElements_04
44  insert into @PWOsAreFinished(Parameter1) select pwo.uid_personwantsorg from personwantsorg pwo join deleted d on pwo.uid_Personwantsorg = d.uid_personwantsorg
45    where pwo.OrderState in ('Aborted', 'Assigned', 'Dismissed', 'Granted', 'New', 'Unsubscribed', 'Waiting') and d.OrderState in ('OrderProduct', 'OrderProlongate'
46, 'OrderUnsubscribe') select @CountPWOsAreFinished = @@ROWCOUNT  declare @DBQueueElements_05 QBM_YDBQueueRaw insert into @DBQueueElements_05 (object, subobject
47, genprocid) select x.uid, null, @GenProcID from (select d.UID_PersonWantsOrg as uid from deleted d join PersonWantsOrg pwo on d.UID_PersonWantsOrg = pwo.UID_PersonWantsOrg
48  left outer join @PWOsAreFinished fi on fi.Parameter1 = d.UID_PersonWantsOrg where isnull(d.OrderState, '') <> isnull(pwo.OrderState, '') and fi.Parameter1
49 is null ) as x  exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackPWOHelperPWO', @DBQueueElements_05  insert into @PWOsToResetGenProcID(Parameter1) select
50 pwo.uid_personwantsorg from personwantsorg pwo join deleted d on pwo.uid_Personwantsorg = d.uid_personwantsorg  where pwo.OrderState in ('Aborted', 'Assigned'
51, 'Dismissed', 'Unsubscribed') and d.OrderState in ('OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Granted', 'New', 'Waiting') select @CountPWOsToResetGenProcID
52 = @@ROWCOUNT if @CountPWOsAreFinished > 0 begin  insert into @PWOHelperPWOToDeleteLater(Parameter1) select  h.XObjectKey  from PWOHelperPWO h with (readpast
53) where uid_Personwantsorg in ( select Parameter1 from @PWOsAreFinished ) if @@ROWCOUNT > 0 begin exec QBM_PJobCreate_SQLDel_L 'PWOHelperPWO', @PWOHelperPWOToDeleteLater
54 , @GenProcID = @GenProcID , @AdditionalObjectKeysAffected = default , @Retries = 5 , @isToFreezeOnError = 1 end  if exists (select top 1 1 from HelperPWOPersonHasObject
55 h with (readpast) join @PWOsAreFinished f on h.UID_PersonWantsOrg = f.Parameter1 ) begin select @DeleteWhereClause = 'UID_PersonWantsOrg in ' + dbo.QBM_FCVParameter1AsOneInClause
56(@PWOsAreFinished) exec QBM_PJobCreate_SQLDel_B 'HelperPWOPersonHasObject' , @whereclause = @DeleteWhereClause , @GenProcID = @GenProcID , @AdditionalObjectKeysAffected
57 = default , @isToFreezeOnError = 1 , @Retries = 5 end  if exists (select top 1 1 from HelperPWOCompliance h with (readpast) join @PWOsAreFinished f on
58 h.UID_PersonWantsOrg = f.Parameter1 ) begin select @DeleteWhereClause = 'UID_PersonWantsOrg in ' + dbo.QBM_FCVParameter1AsOneInClause(@PWOsAreFinished
59) exec QBM_PJobCreate_SQLDel_B 'HelperPWOCompliance' , @whereclause = @DeleteWhereClause , @GenProcID = @GenProcID , @AdditionalObjectKeysAffected = default
60 , @isToFreezeOnError = 1 , @Retries = 5 end  end  if @CountPWOsToResetGenProcID > 0 begin update PersonWantsOrg set GenProcID = null  , XDateUpdated =
61 @Xdate , XUserUpdated = @XUser where uid_Personwantsorg in ( select Parameter1 from @PWOsToResetGenProcID ) end   end  if update(DecisionLevel) or update
62(OrderState)  or update(UID_PersonInserted) or update(UID_PersonOrdered) begin  delete  @PWOBuffer insert into @PWOBuffer(UID1 , Bit1 , int1 , Int2  ) 
63select pwo.uid_personwantsorg  , case  when isnull(d.DecisionLevel,0) <> isnull(pwo.DecisionLevel, 0) then 1  when d.OrderState not in ( 'OrderProlongate'
64 , 'OrderUnsubscribe') and pwo.OrderState in ( 'OrderProlongate' , 'OrderUnsubscribe') then 1 else 0 end as isNewDecisionLevel  , pwo.DecisionLevel   ,
65 case when d.OrderState not in ( 'OrderProlongate' , 'OrderUnsubscribe') and pwo.OrderState in ( 'OrderProlongate' , 'OrderUnsubscribe') then 1 else 0 
66end as IsOrderStarted  from deleted d join Personwantsorg pwo on d.uid_personwantsorg = pwo.uid_personwantsorg  left outer join @PWOsAreFinished fi on 
67fi.Parameter1 = d.UID_PersonWantsOrg where d.DecisionLevel <> pwo.DecisionLevel and fi.Parameter1 is null   or pwo.OrderState in ('OrderProduct', 'OrderProlongate'
68, 'OrderUnsubscribe')  select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select @ElementLast = @@IDENTITY while @ElementIndex
69 <= @ElementLast begin select top 1 @uid_Personwantsorg = bu.UID1  , @isNewDecisionLevel = bu.bit1  , @newDecisionLevel = bu.int1  , @IsOrderStarted = 
70sign(bu.Int2)  from @PWOBuffer bu where bu.ElementIndex = @ElementIndex if @isNewDecisionLevel = 1 begin   exec QER_PITShopOrderResetOneLevel @uid_personwantsorg
71, @newDecisionLevel, @GenProcID end select @ElementIndex += 1 end   insert into @PWOsToFillHelper(Parameter1, Parameter2, Parameter3, HasContentFull) select
72 bu.UID1 as UID_PersonwantsOrg , @GenProcID  , '1' as CurrentLevelOnly , bu.bit1 as IsNewDecisionLevel from @PWOBuffer bu where (sign(bu.Int2)  = 1 and
73 @DecisionOnInsert = 1 or sign(bu.Int2)  = 0 ) and not exists (select top 1 1 from @PWOsToFillHelper e where e.Parameter1 = bu.UID1 )  declare @DBQueueElements_06
74 QBM_YDBQueueRaw insert into @DBQueueElements_06 (object, subobject, genprocid) select x.uid, null, @GenProcID from (select i.UID_PersonWantsOrg as uid
75 from inserted i  left outer join @PWOsAreFinished fi on fi.Parameter1 = i.UID_PersonWantsOrg where fi.Parameter1 is null ) as x  exec QBM_PDBQueueInsert_Bulk
76 'QER-K-ShoppingRackPWOHelperPWO', @DBQueueElements_06 exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackCheckDecision', @DBQueueElements_06  end  if update
77(OrderState) or update(DecisionLevel) begin  insert into @PWOHelperPWOToDeleteImmediate(Parameter1) select h.UID_PWOHelperPWO from PWOHelperPWO h with 
78(readpast) join inserted i on i.uid_PersonWantsOrg = h.uid_PersonWantsOrg  left outer join @PWOsAreFinished fi on fi.Parameter1 = i.UID_PersonWantsOrg 
79where UID_PWODecisionRule = 'QER-PWODecisionRule-QP' and fi.Parameter1 is null end if update(isReserved) begin   insert into @PWOHelperPWOToDeleteImmediate
80(Parameter1) select h.UID_PWOHelperPWO from PWOHelperPWO h with (readpast)  left outer join @PWOsAreFinished fi on fi.Parameter1 = h.UID_PersonWantsOrg