dbo.QER_ZAllForPersonInBaseTree

1     create   procedure QER_ZAllForPersonInBaseTree (@SlotNumber int) as begin  declare @IsCUNode bit declare @IsOrgNode bit select @IsCUNode = 
20 select @IsOrgNode = 0 declare @uid_personwantsorg varchar(38) declare @SQLCmd nvarchar(2000) declare @BasisObjectKey varchar(138) declare @GenProcID 
3varchar(38) declare @CfgUseGenProcID bit = dbo.QBM_FCVStringToBit(dbo.QBM_FGIConfigparmValue('Common\ProcessState\UseGenProcIDFromPWO')) declare @BitpatternDynInv
4 int = dbo.QBM_FGIBitPatternXOrigin('|Dynamic|', 1) declare @UID_PWOStateFinalError varchar(38) declare @Parameter nvarchar(1000)  declare @Ident_OrgRoot
5 nvarchar(64) declare @EntryAll QBM_YCursorBuffer      declare @ElementLast int declare @ElementCount int declare @ElementIndex int declare @XUser nvarchar
6(64) = object_name(@@procid) declare @Xdate datetime = getutcdate() declare @UID_TaskFollow varchar(38) BEGIN TRY  insert into @EntryAll (UID1  , UID2 
7 , ObjectKey1 , Bit1 , UID3  , Int1  ) select cu.UID_Parameter, cu.UID_SubParameter, pio.XObjectKey, sign(len(isnull(pio.UID_Person, ''))) ^1, cu.GenProcID
8 , isnull(pio.XOrigin, 0) from QBMDBQueueCurrent cu with (readpast) left outer join PersonInBaseTree pio on cu.UID_Parameter = pio.UID_Person and cu.UID_SubParameter
9 = pio.UID_Org and pio.XOrigin > 0 where cu.SlotNumber = @SlotNumber update @EntryAll set ObjectKey1  = dbo.QBM_FCVElementToObjectKey2('PersonIn'+dbo.QER_FGIOrgRootName
10(b.uid_orgroot), 'uid_person', d.UID1 , 'UID_' + dbo.QER_FGIOrgRootName(b.uid_orgroot) , b.uid_org) from @EntryAll d join BaseTree b on d.UID2  = b.UID_Org
11 where d.Bit1  = 1   if exists (select top 1 1 from @EntryAll pio join BaseTree b on pio.UID2  = b.UID_Org join DynamicGroup dg on dg.ObjectKeyBaseTree
12 = b.XObjectKey join QERDynamicGroupBlackList bl on bl.UID_DynamicGroup = dg.UID_DynamicGroup and bl.UID_Person = pio.UID1  ) begin update QERDynamicGroupBlackList
13 set IsAssignedByOthers = case when pio.Bit1  = 1 then 0 when pio.Int1  & @BitpatternDynInv > 0 then 1 else bl.IsAssignedByOthers end  , XDateUpdated =
14 @Xdate , XUserUpdated = @XUser from @EntryAll pio join BaseTree b on pio.UID2  = b.UID_Org join DynamicGroup dg on dg.ObjectKeyBaseTree = b.XObjectKey
15 join QERDynamicGroupBlackList bl on bl.UID_DynamicGroup = dg.UID_DynamicGroup and bl.UID_Person = pio.UID1  end if exists (select top 1 1 from @EntryAll
16 a where a.UID2  = 'QER-AEROLE-ITSHOP-INTERVENTION' ) begin select top 1 @GenProcID = a.UID3  from @EntryAll a where a.UID2  = 'QER-AEROLE-ITSHOP-INTERVENTION'
17   exec QBM_PDBQueueInsert_Single 'QBM-K-Commonrecalculate', 'QER-K-ShoppingRackPWOHelperPWO', '', @GenprocID end declare @DBQueueElements_01 QBM_YDBQueueRaw
18 insert into @DBQueueElements_01(Object, SubObject, GenProcID) select distinct h.UID_PersonWantsOrg, '', cu.UID3  from @EntryAll cu join QERWorkingStep
19 s on s.UID_AERoleFallBack = cu.UID2  join PWOHelperPWO h on h.UID_QERWorkingStep = s.UID_QERWorkingStep if @@ROWCOUNT > 0 begin   exec QBM_PDBQueueInsert_Bulk
20 'QER-K-ShoppingRackPWOHelperPWO-Cfg', @DBQueueElements_01 end        if exists (select top 1 1 from BaseTree b join @EntryAll i on b.uid_org = i.UID2 
21 where b.uid_Orgroot = 'QER-V-ITShopOrg' and b.itShopInfo = 'CU' ) begin select @IsCUNode = 1 end      if exists (select top 1 1 from BaseTree b join @EntryAll
22 i on b.uid_org = i.UID2  where (b.uid_orgroot like '____[^V]%' ) ) begin select @IsOrgNode = 1 end  declare @DBQueueElements_02 QBM_YDBQueueRaw insert
23 into @DBQueueElements_02 (object, subobject, genprocid) select x.uid, null, x.GenProcID from    ( select distinct d.UID1  as uid, d.UID3 as GenProcID 
24from @EntryAll d join BaseTree b on d.UID2  = b.uid_org where b.uid_orgroot = 'QER-V-AERole' ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-PersonCheckDynUser'
25, @DBQueueElements_02                  declare @ElementClosing QBM_YCursorBuffer insert into @ElementClosing (UID1, ObjectKey1, UID2) select pwo.uid_personwantsorg
26, pwo.XObjectKey ,dbo.QER_FGIGenProcIDForPWO(pwo.GenProcID, x.UID3  , @CfgUseGenProcID) as GenProcID from @EntryAll x join Delegation d on d.ObjectKeyDelegated
27 = x.ObjectKey1  and x.Bit1  = 1  join personwantsorg pwo on d.uid_personwantsorg = pwo.uid_personwantsorg      where pwo.OrderState in ('Assigned', 'Granted'
28, 'New', 'OrderProduct', 'OrderProlongate', 'OrderUnsubscribe', 'Waiting') select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount
29 +1 select @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @uid_personwantsorg = bu.UID1 , @BasisObjectKey = bu.ObjectKey1
30 , @GenProcID = bu.UID2 from @ElementClosing bu where bu.ElementIndex = @ElementIndex select @SQLcmd = N'uid_personwantsorg = ''' + rtrim(@uid_personwantsorg
31) + N'''' select @UID_PWOStateFinalError = dbo.QER_FGIPWOStateFinalError(@UID_PersonWantsOrg) exec QBM_PJobCreate_HOCallMethod @objecttype = 'personwantsorg'
32 , @whereclause = @SQLcmd , @save = 1 , @MethodName = 'Abort' , @GenProcID = @GenprocID  , @ObjectKeysAffected = DEFAULT , @param1 = '#LDS#Closing delegation because its source was was deleted.|'
33 , @Param2 = @UID_PWOStateFinalError , @Retries = 3 , @isToFreezeOnError = 1  , @WhereClauseAdditional = ' OrderState in (''Assigned'', ''Granted'', ''New'', ''OrderProduct'', ''OrderProlongate'', ''OrderUnsubscribe'', ''Waiting'')'
34  , @BasisObjectKey = @BasisObjectKey , @checkForExisting = 1 select @ElementIndex += 1 end  declare @DBQueueElements_03 QBM_YDBQueueRaw insert into @DBQueueElements_03
35 (object, subobject, genprocid) select x.uid, null, x.GenProcID  from     ( select distinct d.UID1  as uid, d.UID3 as GenProcID from @EntryAll d left outer
36 join BaseTree b on d.UID2  = b.uid_org where isnull(b.uid_orgroot, '') <> 'CPL-V-NonCompliance'  and isnull(b.IsNoInheriteToPerson, 0) = 0  ) as x exec
37 QBM_PDBQueueInsert_Bulk 'QER-K-AllForOnePerson', @DBQueueElements_03   declare @DBQueueElements_04 QBM_YDBQueueRaw insert into @DBQueueElements_04 (object
38, subobject, genprocid) select x.uid, null, x.GenProcID from ( select distinct d.UID1  as uid, d.UID3 as GenProcID from @EntryAll d ) as x exec QBM_PDBQueueInsert_Bulk
39 'QER-K-PersonHasObject', @DBQueueElements_04  if @IsCUNode = 1 begin  declare @DBQueueElements_05 QBM_YDBQueueRaw insert into @DBQueueElements_05 (object
40, subobject, genprocid) select x.uid, x.subobject, x.GenProcID       from ( select distinct pio.UID1  as uid, o.uid_parentorg as subobject, pio.UID3 as
41 GenProcID from @EntryAll pio join BaseTree o on pio.UID2  = o.uid_Org and o.ITShopInfo = 'CU' and o.uid_orgroot = 'QER-V-ITShopOrg' and pio.Bit1  = 1 
42 ) as x exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackMemberDel', @DBQueueElements_05  end    declare @ElementNacharbeit QBM_YCursorBuffer declare @ObjectKeyOrg
43 varchar(138)  insert into @ElementNacharbeit (Ident1, UID3, ObjectKey1)  select distinct dbo.QER_FGIOrgRootName(b.UID_OrgRoot), p.UID3 , b.XObjectKey 
44  from @EntryAll p join BaseTree b on p.UID2  = b.UID_Org select @ElementCount = @@ROWCOUNT select @ElementIndex = @@IDENTITY - @ElementCount +1 select
45 @ElementLast = @@IDENTITY while @ElementIndex <= @ElementLast begin select top 1 @Ident_OrgRoot = bu.Ident1 , @GenProcID = bu.UID3  , @ObjectKeyOrg = 
46bu.ObjectKey1 from @ElementNacharbeit bu where bu.ElementIndex = @ElementIndex  declare @RecalcParameter_01 QBM_YCursorBuffer insert into @RecalcParameter_01
47 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OR', @ObjectKeyOrg, 'I' exec QER_PITShopHelperFill_Recalc @RecalcParameter_01, @GenprocID  if @Ident_OrgRoot
48 = 'AERole' begin  declare @RecalcParameter_02 QBM_YCursorBuffer  insert into @RecalcParameter_02 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-OA'
49, @ObjectKeyOrg, 'I' union select 'TSB-PWODecisionRule-TO', @ObjectKeyOrg, 'I' union select 'AOB-PWODecisionRule-BA', @ObjectKeyOrg, 'I' union select 'AOB-PWODecisionRule-BE'
50, @ObjectKeyOrg, 'I' union select 'ARS-ITShopRule-KA', @ObjectKeyOrg, 'I' union select 'ARS-ITShopRule-PA', @ObjectKeyOrg, 'I' union select 'PAG-PWODecisionRule-PG'
51, @ObjectKeyOrg, 'I'  union select 'QER-PWODecisionRule-DI', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-DR', @ObjectKeyOrg, 'I' union select 
52'QER-PWODecisionRule-ID', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-IL', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-IP', @ObjectKeyOrg
53, 'I' union select 'QER-PWODecisionRule-PI', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-PR', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-RD'
54, @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-RL', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-RP', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-OX'
55, @ObjectKeyOrg, 'I' exec QER_PITShopHelperFill_Recalc @RecalcParameter_02, @GenprocID  end if @Ident_OrgRoot = 'Department' begin  declare @RecalcParameter_03
56 QBM_YCursorBuffer insert into @RecalcParameter_03 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-DI', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-DR'
57, @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-ID', @ObjectKeyOrg, 'I' exec QER_PITShopHelperFill_Recalc @RecalcParameter_03, @GenprocID  end if
58 @Ident_OrgRoot = 'ProfitCenter' begin  declare @RecalcParameter_04 QBM_YCursorBuffer insert into @RecalcParameter_04 (UID1, ObjectKey1, Ident1) select
59 'QER-PWODecisionRule-IP', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-PI', @ObjectKeyOrg, 'I' union select 'QER-PWODecisionRule-PR', @ObjectKeyOrg
60, 'I' exec QER_PITShopHelperFill_Recalc @RecalcParameter_04, @GenprocID  end if @Ident_OrgRoot = 'Locality' begin  declare @RecalcParameter_05 QBM_YCursorBuffer
61 insert into @RecalcParameter_05 (UID1, ObjectKey1, Ident1) select 'QER-PWODecisionRule-IL', @ObjectKeyOrg, 'I' exec QER_PITShopHelperFill_Recalc @RecalcParameter_05
62, @GenprocID  end select @ElementIndex += 1 end     if @IsOrgNode = 1 begin     if exists (select top 1 1    from @EntryAll p join BaseTree b on p.UID2
63  = b.UID_Org  join pwodecisionstep s on b.XObjectKey = s.ObjectKeyOfAssignedOrg ) begin exec QBM_PDBQueueInsert_Single 'QER-K-ShoppingRackPWODecisionMaker'
64, '', '', @GenprocID end  end   declare @DBQueueElements_06 QBM_YDBQueueRaw insert into @DBQueueElements_06 (object, subobject, genprocid) select x.uid
65, x.subobject, x.GenProcID from ( select distinct pio.UID1  as uid, null as subobject, pio.UID3 as GenProcID from @EntryAll pio  where exists (select top
66 1 1 from BaseTree o where o.UID_AERoleManager = pio.UID2 ) ) as x  select @UID_TaskFollow = 'QER-K-HelperHeadOrg' exec QBM_PDBQueueInsert_Bulk @UID_TaskFollow
67, @DBQueueElements_06 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH end 
68