dbo.TSB_PUNSAccountBInUNSGroupB1_K

1      create   procedure TSB_PUNSAccountBInUNSGroupB1_K  (@DBQueueCurrent QBM_YDBQueueCurrent readonly , @Slotnumber int , @UserHasGroupList bit
2 , @CountDeltaQantity int output , @CountDeltaOrigin int output ) as begin declare @PersonToCheck_L QBM_YSingleGUID declare @UID_BaseTreeAssignToUse varchar
3(38) = 'TSB-AsgnBT-UNSGroupB1' declare @Sourcedata QBM_YDataForDelta declare @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel
4('|Delay|', 0) declare @QBM_BitPatternXMarkedForDel_Out int = dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0) declare @QBM_BitPatternXOrigin_Direct
5 int = dbo.QBM_FGIBitPatternXOrigin('|Direct|', 0) declare @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv
6 int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1) declare @PMDKeepMembershipsOfLinkedAccount varchar(16) = left(dbo.QBM_FGIConfigparmValue('QER\Person\User\KeepMembershipsOfLinkedAccount'
7), 16) SET XACT_ABORT OFF BEGIN TRY insert into @PersonToCheck_L(UID_SingleGuid) select distinct a.UID_Person from @DBQueueCurrent cu join UNSAccountB 
8a on cu.UID_Parameter = a.UID_UNSAccountB where a.UID_Person > ' '  if @UserHasGroupList = 0 begin insert into @SourceData( IsUpcommingContent, XOriginAfter
9 , Element, AssignedElement, XOriginBefore , XIsInEffectBefore, XIsInEffectAfter ) select 0, 0 , aig.UID_UNSAccountB, aig.UID_UNSGroupB1, aig.XOrigin
10 , aig.XIsInEffect, 0 from UNSAccountBInUNSGroupB1 aig join @DBQueueCurrent x on aig.uid_UNSAccountB = x.uid_parameter join UNSGroupB1 g on aig.UID_UNSGroupB1
11 = g.UID_UNSGroupB1 and g.HasReadOnlyMemberships = 0  where aig.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0  and g.XMarkedForDeletion
12 & @QBM_BitPatternXMarkedForDel_Out = 0  insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement , XIsInEffectBefore
13, XIsInEffectAfter ) select 1, 0, aig.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv , aig.uid_UNSAccountB, aig.uid_UNSGroupB1 , 0, dbo.TSB_FGIUserInGroupValid_OVR_Ke
14 ( p.IsInActive, p.IsTemporaryDeactivated, p.XMarkedForDeletion, bh.PFDInheritGroup, bh.PTDInheritGroup, bh.PMDInheritGroup, 1, p.IsSecurityIncident, bh.PSIInheritGroup
15, n.AccountDisabled, bh.ADAInheritGroup , ovr.PFDInheritOverwrite, ovr.PTDInheritOverwrite, ovr.PSIInheritOverwrite, ovr.PMDInheritOverwrite, ovr.ADAInheritOverwrite
16, @PMDKeepMembershipsOfLinkedAccount)  & case when n.UID_UNSRootB = g.UID_UNSRootB then 1  when ns.IsExtendedInheritance = 0 then 0 when ns.IsExtendedInheritance
17 = 1 and ns.UID_DPRNameSpace = rg.UID_DPRNameSpace then 1 else 0 end  from UNSAccountBInUNSGroupB1 aig join @DBQueueCurrent x on aig.uid_UNSAccountB 
18= x.uid_parameter join UNSAccountB n on aig.uid_UNSAccountB = n.uid_UNSAccountB join UNSGroupB1 g on aig.UID_UNSGroupB1 = g.UID_UNSGroupB1 and g.HasReadOnlyMemberships
19 = 0   and g.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out = 0  join UNSRootB rn on n.UID_UNSRootB = rn.UID_UNSRootB join DPRNameSpace ns on rn.UID_DPRNameSpace
20 = ns.UID_DPRNameSpace join UNSRootB rg on g.UID_UNSRootB = rg.UID_UNSRootB left outer join TSBSpecificGroupBehavior ovr on ovr.ObjectKeyGroup = g.XObjectKey
21  left outer join person p on n.uid_person = p.uid_person  left outer join TSBBehavior bh on n.UID_TSBBehavior = bh.UID_TSBBehavior  end else  begin insert
22 into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore , XIsInEffectBefore, XIsInEffectAfter ) select 0, 0 , aig.UID_UNSAccountB
23, aig.UID_UNSGroupB1, aig.XOrigin , aig.XIsInEffect, 0 from UNSAccountBHasUNSGroupB1 aig join @DBQueueCurrent x on aig.uid_UNSAccountB = x.uid_parameter
24 join UNSGroupB1 g on aig.UID_UNSGroupB1 = g.UID_UNSGroupB1 and g.HasReadOnlyMemberships = 0   and g.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out
25 = 0 where aig.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0  insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , 
26Element, AssignedElement , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0, aig.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv , aig.uid_UNSAccountB, aig.uid_UNSGroupB1
27 , 0, dbo.TSB_FGIUserInGroupValid_OVR_Ke ( p.IsInActive, p.IsTemporaryDeactivated, p.XMarkedForDeletion, bh.PFDInheritGroup, bh.PTDInheritGroup, bh.PMDInheritGroup
28, 1, p.IsSecurityIncident, bh.PSIInheritGroup, n.AccountDisabled, bh.ADAInheritGroup , ovr.PFDInheritOverwrite, ovr.PTDInheritOverwrite, ovr.PSIInheritOverwrite
29, ovr.PMDInheritOverwrite, ovr.ADAInheritOverwrite, @PMDKeepMembershipsOfLinkedAccount)  & case when n.UID_UNSRootB = g.UID_UNSRootB then 1  when ns.IsExtendedInheritance
30 = 0 then 0 when ns.IsExtendedInheritance = 1 and ns.UID_DPRNameSpace = rg.UID_DPRNameSpace then 1 else 0 end from UNSAccountBHasUNSGroupB1 aig join 
31@DBQueueCurrent x on aig.uid_UNSAccountB = x.uid_parameter join UNSAccountB n on aig.uid_UNSAccountB = n.uid_UNSAccountB join UNSGroupB1 g on aig.UID_UNSGroupB1
32 = g.UID_UNSGroupB1 and g.HasReadOnlyMemberships = 0   and g.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out = 0  join UNSRootB rn on n.UID_UNSRootB
33 = rn.UID_UNSRootB join DPRNameSpace ns on rn.UID_DPRNameSpace = ns.UID_DPRNameSpace join UNSRootB rg on g.UID_UNSRootB = rg.UID_UNSRootB left outer join
34 TSBSpecificGroupBehavior ovr on ovr.ObjectKeyGroup = g.XObjectKey  left outer join person p on n.uid_person = p.uid_person left outer join TSBBehavior
35 bh on n.UID_TSBBehavior = bh.UID_TSBBehavior end  insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter 
36, XIsInEffectBefore, XIsInEffectAfter ) select 1, 0 , x.uid_UNSAccountB, x.uid_UNSGroupB1, @QBM_BitPatternXOrigin_Inherit , 0, dbo.TSB_FGIUserInGroupValid_OVR_Ke
37 ( p.IsInActive, p.IsTemporaryDeactivated, p.XMarkedForDeletion, bh.PFDInheritGroup, bh.PTDInheritGroup, bh.PMDInheritGroup, 0, p.IsSecurityIncident, bh.PSIInheritGroup
38, x.AccountDisabled, bh.ADAInheritGroup , ovr.PFDInheritOverwrite, ovr.PTDInheritOverwrite, ovr.PSIInheritOverwrite, ovr.PMDInheritOverwrite, ovr.ADAInheritOverwrite
39, @PMDKeepMembershipsOfLinkedAccount)  from (  select nt.uid_UNSAccountB , ohg.uid_UNSGroupB1 , nt.UID_UNSRootB , nt.uid_person, nt.AccountDisabled ,
40 nt.UID_TSBAccountDef, nt.UID_TSBBehavior, gg.XObjectKey from @DBQueueCurrent x join UNSAccountB nt on nt.uid_UNSAccountB = x.uid_parameter   and nt.IsGroupAccount_UNSGroupB1
41 = 1 join dbo.QER_FTAccountInheritFromOrg_L(@PersonToCheck_L , @UID_BaseTreeAssignToUse ) pfo on pfo.UID_Person = nt.UID_Person join BaseTreeHasUNSGroupB1
42 ohg on pfo.uid_org = ohg.uid_org and ohg.XOrigin > 0 and ohg.XIsInEffect = 1 join UNSGroupB1 gg on ohg.uid_UNSGroupB1 = gg.uid_UNSGroupB1 and gg.HasReadOnlyMemberships
43 = 0   and gg.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out = 0  join UNSRootB rn on nt.UID_UNSRootB = rn.UID_UNSRootB join DPRNameSpace ns on 
44rn.UID_DPRNameSpace = ns.UID_DPRNameSpace join UNSRootB rg on gg.UID_UNSRootB = rg.UID_UNSRootB  where dbo.TSB_FGIGroupAccountMatching( gg.MatchPatternForMembership
45, nt.MatchPatternForMembership) = 1 and 1 = case when nt.UID_UNSRootB = gg.UID_UNSRootB then 1  when ns.IsExtendedInheritance = 0 then 0 when ns.IsExtendedInheritance
46 = 1 and ns.UID_DPRNameSpace = rg.UID_DPRNameSpace then 1 else 0 end union all  select nt.uid_UNSAccountB , gg.uid_UNSGroupB1 , nt.UID_UNSRootB , nt.uid_person
47 , nt.AccountDisabled , nt.UID_TSBAccountDef , nt.UID_TSBBehavior , gg.XObjectKey from @DBQueueCurrent x join UNSAccountB nt on nt.uid_UNSAccountB = x.uid_parameter
48  and nt.IsGroupAccount_UNSGroupB1 = 1 join personHasESet phe on nt.uid_person = phe.uid_person and phe.XOrigin > 0 and phe.XIsInEffect = 1  join person
49 px on nt.UID_Person = px.uid_person and px.isNoInherite = 0  join ESetHasEntitlement ehe on phe.uid_ESet = ehe.uid_ESet and ehe.XOrigin > 0 and ehe.XIsInEffect
50 = 1 and dbo.QBM_FGIModuleExists('RMS') = 1 join UNSGroupB1 gg on ehe.Entitlement = gg.XObjectKey and gg.HasReadOnlyMemberships = 0   and gg.XMarkedForDeletion
51 & @QBM_BitPatternXMarkedForDel_Out = 0  join UNSRootB rn on nt.UID_UNSRootB = rn.UID_UNSRootB join DPRNameSpace ns on rn.UID_DPRNameSpace = ns.UID_DPRNameSpace
52 join UNSRootB rg on gg.UID_UNSRootB = rg.UID_UNSRootB where dbo.TSB_FGIGroupAccountMatching( gg.MatchPatternForMembership, nt.MatchPatternForMembership
53) = 1 and 1 = case when nt.UID_UNSRootB = gg.UID_UNSRootB then 1  when ns.IsExtendedInheritance = 0 then 0 when ns.IsExtendedInheritance = 1 and ns.UID_DPRNameSpace
54 = rg.UID_DPRNameSpace then 1 else 0 end ) as x join UNSRootb d on x.UID_UNSRootB = d.UID_UNSRootB left outer join person p on x.uid_person = p.uid_person
55  left outer join TSBBehavior bh on x.UID_TSBBehavior = bh.UID_TSBBehavior  left outer join TSBSpecificGroupBehavior ovr on ovr.ObjectKeyGroup = x.XObjectKey
56   where (not (isnull(p.XMarkedForDeletion, 0) & @QBM_BitPatternXMarkedForDel_Delay > 0 and bh.PMDInheritGroup is null and @PMDKeepMembershipsOfLinkedAccount
57 <> 'ALL' and ovr.PMDInheritOverwrite is null ) )  if dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\GroupExclusion') > ' ' begin if exists (select
58 top 1 1 from UNSGroupB1Exclusion ex with (nolock) join @Sourcedata s on ex.UID_UNSGroupB1Lower  = s.AssignedElement where s.IsUpcommingContent = 1
59 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 ) begin declare @ExcludeCandidate QER_YExcludeCandidate insert into @ExcludeCandidate(Element, AssignedElement
60, XIsInEffectAfter) select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_UNSGroupB1Lower as UID_Excluded from UNSGroupB1Exclusion
61 exe ) as ex on s.AssignedElement = ex.UID_Excluded where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 union all select s.Element
62, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_UNSGroupB1Higher as UID_Excluding from UNSGroupB1Exclusion exe ) as ex on s.AssignedElement
63 = ex.UID_Excluding where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 update @ExcludeCandidate set XIsInEffectAfter = 0 
64from @ExcludeCandidate l  join ( select exe.UID_UNSGroupB1Lower as UID_Excluded, exe.UID_UNSGroupB1Higher as UID_Excluding from UNSGroupB1Exclusion
65 exe ) as ex on l.AssignedElement = ex.UID_Excluded join (select sh.* from @ExcludeCandidate sh  ) as h on l.Element = h.Element and h.AssignedElement 
66= ex.UID_Excluding if @@ROWCOUNT > 0 begin update @Sourcedata set XIsInEffectAfter = 0 from @Sourcedata l join @ExcludeCandidate k on l.Element = k.Element
67 and l.AssignedElement = k.AssignedElement and k.XIsInEffectAfter = 0 where l.IsUpcommingContent = 1 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1 
68end end  end   exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert = 1, @DeltaOrigin = 1, @CountDeltaQantity
69 = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern = 
70@DBQueueCurrent END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: end 
71