dbo.QER_TUBaseTree

1create   trigger QER_TUBaseTree on BaseTree  for UPDATE not for Replication as begin  declare @PWOTaskToUse varchar(38) = dbo.QER_FGIPWOTaskToUse
2() BEGIN TRY if exists (select top 1 1 from inserted) goto start if exists (select top 1 1 from deleted) goto start return start: declare @GenProcID varchar
3(38) select @GenProcID = dbo.QBM_FGISessionContext('')   declare @alt_uid_Org varchar(38) declare @alt_uid_Orgroot varchar(38) declare @neu_uid_Orgroot
4 varchar(38) declare @alt_uid_Parentorg varchar(38) declare @neu_uid_Parentorg varchar(38) declare @Ordernumber int declare @OL_only_Upper bit declare 
5@alt_isCutNode bit declare @neu_isCutNode bit declare @alt_ITShopInfo nvarchar(2) declare @neu_ITShopInfo nvarchar(2)  declare @alt_ident_orgtype nvarchar
6(64) declare @neu_ident_orgtype nvarchar(64) declare @IsTopDown bit declare @QER_ITShop_OnWorkflowAssign varchar(16) if update(UID_PWODecisionMethod) begin
7 select @QER_ITShop_OnWorkflowAssign = dbo.QBM_FGIConfigparmValue('QER\ITShop\OnWorkflowAssign') if @QER_ITShop_OnWorkflowAssign in ('RESET', 'ABORT') 
8begin declare @DBQueueElements_01 QBM_YDBQueueRaw insert into @DBQueueElements_01(Object, SubObject, GenProcID) select x.uid, null, @GenProcID from ( select
9 pwo.UID_PersonWantsOrg as uid from BaseTree b join deleted d on b.UID_Org = d.UID_Org and isnull(b.UID_PWODecisionMethod, '') <> isnull(d.UID_PWODecisionMethod
10, '') and b.UID_PWODecisionMethod > ' ' join PersonWantsOrg pwo on pwo.UID_Org = b.UID_Org      and pwo.OrderState in ('Assigned', 'Granted', 'New', 'OrderProduct'
11, 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') ) as x if @QER_ITShop_OnWorkflowAssign = 'RESET' begin exec QBM_PDBQueueInsert_Bulk 'QER-K-ITSHOPOrder-RESET'
12, @DBQueueElements_01 end if @QER_ITShop_OnWorkflowAssign = 'ABORT' begin exec QBM_PDBQueueInsert_Bulk 'QER-K-ITSHOPOrder-ABORT', @DBQueueElements_01 end
13 end   declare @DBQueueElements_02 QBM_YDBQueueRaw insert into @DBQueueElements_02(Object, SubObject, GenProcID) select x.uid, null, @GenProcID from ( 
14select pwo.UID_PersonWantsOrg as uid from BaseTree b join deleted d on b.UID_Org = d.UID_Org and isnull(b.UID_PWODecisionMethod, '') <> isnull(d.UID_PWODecisionMethod
15, '') and b.UID_PWODecisionMethod is null join PersonWantsOrg pwo on pwo.UID_Org = b.UID_Org      and pwo.OrderState in ('Assigned', 'Granted', 'New', 
16'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-ITSHOPOrder-ABORT', @DBQueueElements_02 end
17  if update(IsInvalidForDynamicGroup) begin if exists (select top 1 1 from deleted d join BaseTree b on d.UID_Org = b.UID_Org join DynamicGroup g on b.XObjectKey
18 = g.ObjectKeyBaseTree where d.IsInvalidForDynamicGroup = 0 and b.IsInvalidForDynamicGroup = 1 ) begin raiserror( '#LDS#Change of IsInvalidForDynamicGroup is invalid because element is used for dynamic groups.|'
19, 18, 1) with nowait end end if update(UID_DialogGroup)  begin  if exists (select top 1 1 from inserted i join DialogGroup g on i.UID_DialogGroup = g.UID_DialogGroup
20 where g.IsRoleBasedOnly = 0 ) begin raiserror('#LDS#Assigment is only permitted for DialogGroup.IsRoleBasedOnly true.|', 18, 2) with nowait end  declare
21 @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select distinct
22 pio.uid_person as uid from BaseTree b join deleted d on b.uid_org = d.uid_org and isnull(b.UID_DialogGroup, '') <> isnull(d.UID_DialogGroup, '') and b.uid_orgroot
23 = 'QER-V-AERole'  join BaseTreecollection co on b.uid_org = co.uid_ParentOrg join personinBaseTree pio on pio.uid_org = co.uid_org and pio.XOrigin > 0
24 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-PersonCheckDynUser', @DBQueueElements_03 end  if update(uid_orgroot) begin  if exists (select top 1 1 from 
25deleted d join BaseTree b on d.UID_Org = b.UID_Org where d.UID_OrgRoot <> b.UID_OrgRoot ) begin  raiserror('#LDS#Cannot update node because OrgRoot was changed.|'
26, 18, 1) with nowait end end  if update(uid_Parentorg) begin  if exists (select top 1 1 from deleted d join BaseTree b on d.UID_Org = b.UID_Org left outer
27 join BaseTree p on b.UID_ParentOrg = p.UID_Org and b.UID_OrgRoot = p.UID_OrgRoot where b.UID_ParentOrg > ' ' and p.UID_Org is null ) begin raiserror( 
28'#LDS#Cannot change node, because predecessor does not exist in same role class.|', 18, 1) with nowait end   if exists (select top 1 1 from deleted d join
29 BaseTree b on d.UID_Org = b.UID_Org left outer join BaseTree sh on b.UID_ParentOrg = sh.UID_Org and sh.ITShopInfo = 'SH' where b.UID_OrgRoot = 'QER-V-ITShopOrg'
30 and d.ITShopInfo = 'BO' and isnull(d.UID_ParentOrg, '') <> isnull(b.UID_ParentOrg, '') and sh.UID_Org is null ) begin raiserror( '#LDS#Cannot change shelf properties, because predecessor is not a Shop.|'
31, 18, 2) with nowait end end  if update(ITShopInfo) begin  if exists (select top 1 1 from deleted d join BaseTree b on d.UID_Org = b.UID_Org where b.ITShopInfo
32 > ' ' and b.UID_OrgRoot not in ('QER-V-ITShopOrg', 'QER-V-ITShopSrc') ) begin raiserror( '#LDS#Cannot set IT Shop information, because role class is not suitable for IT Shop.|'
33, 18, 2) with nowait end   if exists (select top 1 1 from deleted d join BaseTree b on d.UID_Org = b.UID_Org join BaseTree pr on pr.UID_ParentOrg = d.UID_Org
34 where d.ITShopInfo = 'BO' and isnull( b.ITShopInfo , '') <> 'BO' )  begin raiserror( '#LDS#Cannot change shelf in IT Shop, because product nodes still exist.|'
35, 18, 2) with nowait end if exists (select top 1 1 from deleted d join BaseTree b on d.UID_Org = b.UID_Org join BaseTree pa with (nolock) on ( b.UID_ParentOrg
36 = pa.UID_Org or d.UID_ParentOrg = pa.UID_Org ) where isnull(d.ITShopInfo, '') <> isnull(b.ITShopInfo, '') and pa.ITShopInfo = 'BO' ) begin raiserror( 
37'#LDS#Cannot change shelf property, because predecessor already is a shelf.|', 18, 2) with nowait end  if exists (select top 1 1 from deleted d join BaseTree
38 b on d.UID_Org = b.UID_Org where b.UID_OrgRoot = 'QER-V-ITShopSrc' and isnull(d.ITShopInfo, '') <> isnull(b.ITShopInfo, '') ) begin raiserror( '#LDS#Cannot change shelf properties, because changing the type of template is not suitable for IT Shop Source.|'
39, 18, 2) with nowait end   if exists (select top 1 1 from deleted d join BaseTree b on d.UID_Org = b.UID_Org join PersonInBaseTree pio on b.UID_Org = pio.UID_Org
40 where b.UID_OrgRoot = 'QER-V-ITShopOrg' and isnull(d.ITShopInfo, '') <> isnull(b.ITShopInfo, '') and d.ITShopInfo = 'CU' ) begin raiserror( '#LDS#Cannot change IT Shop information, because customer assignment in shop already exists.|'
41, 18, 2) with nowait end end  if update(uid_personhead) or update(uid_personheadSecond) begin                                declare @DBQueueElements_04
42 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select uid_personHead as uid from
43 inserted where uid_personHead > ' ' union select uid_personHeadSecond as uid from Inserted where uid_personHeadSecond > ' ' union select uid_personHead
44 as uid from deleted where uid_personHead > ' ' union select uid_personHeadSecond as uid from deleted where uid_personHeadSecond > ' '  union select us.UID_PersonReceiver
45 as uid from inserted i join QERUniversalSubstitute us on us.UID_PersonSender in (i.UID_PersonHead, i.UID_PersonHeadSecond) where us.IsCurrentlyActive 
46= 1 and us.UseForHeadOrg = 1 union select us.UID_PersonReceiver as uid from deleted d join QERUniversalSubstitute us on us.UID_PersonSender in (d.UID_PersonHead
47, d.UID_PersonHeadSecond) where us.IsCurrentlyActive = 1 and us.UseForHeadOrg = 1 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-HelperHeadOrg', @DBQueueElements_04
48 end if UPDATE(UID_AERoleManager) begin declare @DBQueueElements_05 QBM_YDBQueueRaw insert into @DBQueueElements_05 (object, subobject, genprocid) select
49 x.uid, null, @GenProcID from( select pio.UID_Person as uid from inserted i join PersonInBaseTree pio on i.UID_AERoleManager = pio.UID_Org union select
50 us.UID_PersonReceiver as uid from inserted i join PersonInBaseTree pio on i.UID_AERoleManager = pio.UID_Org join QERUniversalSubstitute us on us.UID_PersonSender
51 = pio.UID_Person where us.IsCurrentlyActive = 1 and us.UseForHeadOrg = 1 union select pio.UID_Person as uid from deleted d join PersonInBaseTree pio on
52 d.UID_AERoleManager = pio.UID_Org union select us.UID_PersonReceiver as uid from deleted d join PersonInBaseTree pio on d.UID_AERoleManager = pio.UID_Org
53 join QERUniversalSubstitute us on us.UID_PersonSender = pio.UID_Person where us.IsCurrentlyActive = 1 and us.UseForHeadOrg = 1 ) as x exec QBM_PDBQueueInsert_Bulk
54 'QER-K-HelperHeadOrg', @DBQueueElements_05 end if update(UID_AERoleManager) begin declare @RecalcParameter_OX QBM_YCursorBuffer insert into @RecalcParameter_OX
55 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OX', i.XObjectKey, 'I'  from inserted i exec QER_PITShopHelperFill_Recalc @RecalcParameter_OX, 
56@GenprocID end  if update(UID_OrgDepartment) begin declare @RecalcParameter_01 QBM_YCursorBuffer  insert into @RecalcParameter_01 (UID1, ObjectKey1, Ident1
57) select y.DecisionRule, x.XObjectKey, 'I' from (select i.XObjectKey from inserted i where i.UID_OrgRoot = 'QER-V-ITShopOrg' ) as x join (select 'QER-PWODecisionRule-D0'
58 as DecisionRule union select 'QER-PWODecisionRule-D1' union select 'QER-PWODecisionRule-D2' ) as y on 1 = 1 exec QER_PITShopHelperFill_Recalc @RecalcParameter_01
59, @GenprocID end  if update(UID_OrgProfitCenter) begin declare @RecalcParameter_02 QBM_YCursorBuffer  insert into @RecalcParameter_02 (UID1, ObjectKey1
60, Ident1) select y.DecisionRule, x.XObjectKey, 'I' from (select i.XObjectKey from inserted i where i.UID_OrgRoot = 'QER-V-ITShopOrg' ) as x join (select
61 'QER-PWODecisionRule-P0' as DecisionRule union select 'QER-PWODecisionRule-P1' union select 'QER-PWODecisionRule-P2' ) as y on 1 = 1 exec QER_PITShopHelperFill_Recalc
62 @RecalcParameter_02, @GenprocID end  if update(UID_AccProduct) begin declare @RecalcParameter_03 QBM_YCursorBuffer  insert into @RecalcParameter_03 (UID1
63, ObjectKey1, Ident1) select y.DecisionRule, x.XObjectKey, 'I' from (select i.XObjectKey from inserted i where i.UID_OrgRoot = 'QER-V-ITShopOrg' ) as x
64 join (select 'QER-PWODecisionRule-OA' as DecisionRule ) as y on 1 = 1 exec QER_PITShopHelperFill_Recalc @RecalcParameter_03, @GenprocID end  if   update
65(UID_OrgType) or update(uid_rulercontainer) or update(uid_rulercontainerIT) or update(uid_parentorg)  begin if @PWOTaskToUse > ' ' begin  declare @DBQueueElements_06
66 QBM_YDBQueueRaw insert into @DBQueueElements_06 (object, subobject, genprocid) select x.uid, null, @GenProcID from (  select pwo.UID_PersonWantsOrg as
67 uid from BaseTree b join deleted d on b.uid_org = d.uid_org and isnull(b.uid_accproduct,'') <> isnull(d.uid_accproduct,'') join PersonWantsOrg pwo on 
68(b.UID_Org = pwo.UID_Org or b.UID_Org = pwo.UID_ITShopOrgFinal ) where b.ItshopInfo = 'PR' and b.uid_OrgRoot = 'QER-V-ITShopOrg' union select pwo.UID_PersonWantsOrg
69 as uid from BaseTree b join deleted d on b.uid_org = d.uid_org and ( isnull(b.uid_rulercontainer,'') <> isnull(d.uid_rulercontainer,'') or isnull(b.uid_rulercontainerIT
70,'') <> isnull(d.uid_rulercontainerIT,'') or isnull(b.uid_orgdepartment,'') <> isnull(d.uid_orgdepartment,'') or isnull(b.uid_orgProfitCenter,'') <> isnull
71(d.uid_orgProfitCenter,'') ) join PersonWantsOrg pwo on (b.UID_Org = pwo.UID_Org or b.UID_Org = pwo.UID_OrgParent or b.UID_Org = pwo.UID_OrgParentOfParent
72 ) where b.ItshopInfo in ( 'PR', 'BO', 'SH') and b.uid_OrgRoot = 'QER-V-ITShopOrg' and pwo.OrderState like 'Order%' ) as x  exec QBM_PDBQueueInsert_Bulk
73 @PWOTaskToUse, @DBQueueElements_06 if exists (select top 1 1 from BaseTree b join deleted d on b.uid_org = d.uid_org where (b.uid_OrgRoot <> 'QER-V-ITShopOrg'
74 or isnull(b.ItshopInfo, '') = 'SC' ) and ( isnull(b.uid_rulercontainer,'') <> isnull(d.uid_rulercontainer,'') or isnull(b.uid_rulercontainerIT,'') <> 
75isnull(d.uid_rulercontainerIT,'') or isnull(b.uid_orgdepartment,'') <> isnull(d.uid_orgdepartment,'') or isnull(b.uid_orgProfitCenter,'') <> isnull(d.uid_orgProfitCenter
76,'') or isnull(b.uid_parentorg,'') <> isnull(d.uid_parentorg,'') ) ) begin  exec QBM_PDBQueueInsert_Single 'QBM-K-CommonReCalculate', @PWOTaskToUse, ''
77, @GenProcID end end  exec QBM_PDBQueueInsert_Single 'QER-K-ShoppingRackPWODecisionMaker', '', '', @GenProcID end  if update(uid_Accproduct) begin declare
78 @DBQueueElements_07 QBM_YDBQueueRaw insert into @DBQueueElements_07 (object, subobject, genprocid) select x.uid, null, @GenProcID from ( select d.uid_org
79 as uid from BaseTree b join deleted d on b.uid_org = d.uid_org and isnull(b.uid_accproduct,'') <> isnull(d.uid_accproduct,'') where b.ItshopInfo = 'PR'
80  and b.uid_OrgRoot = 'QER-V-ITShopOrg'  ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackProductNode', @DBQueueElements_07 end  if update(uid_parentorg