dbo.ADS_ZContactInADSGroup

1       create   procedure ADS_ZContactInADSGroup (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int , @CountDeltaOrigin
2 int declare @GenProcID varchar(38) declare @PersonToCheck_L QBM_YSingleGUID declare @UID_BaseTreeAssignToUse varchar(38) = 'ADS-AsgnBT-ADSGroup' declare
3 @DBQueueCurrent QBM_YDBQueueCurrent declare @ObjectKeysToCheck_Projection QBM_YParameterList declare @RowsToReset int declare @MyName nvarchar(64) = object_name
4(@@procid) declare @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|', 0) declare @QBM_BitPatternXMarkedForDel_Out int
5 = dbo.QBM_FGIBitPatternXMarkedForDel('|OutStanding|', 0) declare @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare
6 @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1) declare @PMDKeepMembershipsOfLinkedAccount varchar(16) = left(dbo.QBM_FGIConfigparmValue
7('QER\Person\User\KeepMembershipsOfLinkedAccount'), 16) BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID
8) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount
9 = 0 begin goto EndLabel end insert into @PersonToCheck_L(UID_SingleGuid) select distinct a.UID_Person from @DBQueueCurrent cu join ADSContact a on cu.UID_Parameter
10 = a.UID_ADSContact where a.UID_Person > ' '   insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore , XIsInEffectBefore
11, XIsInEffectAfter ) select 0, 0 , aig.uid_ADSContact, aig.uid_ADSgroup, aig.XOrigin , aig.XIsInEffect, 0 from ADSContactInADSGroup aig join @DBQueueCurrent
12 x on uid_adsContact = x.uid_parameter join ADSGroup g on aig.uid_adsGroup = g.uid_adsgroup   where aig.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay
13 = 0  and g.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out = 0          insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter
14 , Element, AssignedElement , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0, aig.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv , aig.uid_ADSContact,
15 aig.uid_ADSGroup , 0, dbo.TSB_FGIUserInGroupValid_OVR_Ke ( p.IsInActive, p.IsTemporaryDeactivated, p.XMarkedForDeletion, bh.PFDInheritGroup, bh.PTDInheritGroup
16, bh.PMDInheritGroup, 1, p.IsSecurityIncident, bh.PSIInheritGroup, 0 , bh.ADAInheritGroup , ovr.PFDInheritOverwrite, ovr.PTDInheritOverwrite, ovr.PSIInheritOverwrite
17, ovr.PMDInheritOverwrite, ovr.ADAInheritOverwrite, @PMDKeepMembershipsOfLinkedAccount)  from ADSContactInADSGroup aig join @DBQueueCurrent x on aig.uid_adsContact
18 = x.uid_parameter join ADSGroup g on aig.uid_adsGroup = g.uid_adsgroup    and g.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out = 0 join ADSContact
19 n on aig.uid_ADSContact = n.uid_ADSContact  join ADSDomain d on n.UID_ADSDomain = d.UID_ADSDomain left outer join TSBSpecificGroupBehavior ovr on ovr.ObjectKeyGroup
20 = g.XObjectKey  left outer join person p on n.uid_person = p.uid_person  left outer join TSBBehavior bh on n.UID_TSBBehavior = bh.UID_TSBBehavior   insert
21 into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0 , uid_ADSContact
22, uid_ADSgroup, @QBM_BitPatternXOrigin_Inherit , 0, y.XIsInEffect from ( select x.uid_ADSContact, x.uid_ADSgroup, dbo.TSB_FGIUserInGroupValid_OVR_Ke ( 
23p.IsInActive, p.IsTemporaryDeactivated, p.XMarkedForDeletion, bh.PFDInheritGroup, bh.PTDInheritGroup, bh.PMDInheritGroup, 0, p.IsSecurityIncident, bh.PSIInheritGroup
24, 0  , bh.ADAInheritGroup , ovr.PFDInheritOverwrite, ovr.PTDInheritOverwrite, ovr.PSIInheritOverwrite, ovr.PMDInheritOverwrite, ovr.ADAInheritOverwrite
25, @PMDKeepMembershipsOfLinkedAccount  ) as XIsInEffect from (    select nt.uid_ADSContact , ohg.uid_ADSgroup , nt.UID_ADSDomain , nt.uid_person, 0  as 
26AccountDisabled , nt.UID_TSBAccountDef , nt.UID_TSBBehavior, gg.XObjectKey from @DBQueueCurrent x join ADSContact nt on nt.uid_ADSContact = x.uid_parameter
27   and nt.isGroupAccount = 1 join dbo.QER_FTAccountInheritFromOrg_L(@PersonToCheck_L , @UID_BaseTreeAssignToUse ) pfo on pfo.UID_Person = nt.UID_Person
28 join BaseTreeHasADSGroup ohg on pfo.uid_org = ohg.uid_org and ohg.XOrigin > 0 and ohg.XIsInEffect = 1 join ADSgroup gg on ohg.uid_ADSgroup = gg.uid_ADSGroup
29  and gg.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out = 0    and ( nt.UID_ADSDomain = gg.UID_ADSDomain    or exists (select top 1 1 from ADS_VDomainTrustExists
30 td where td.UID_ADSDomainMember = nt.UID_ADSDomain and td.UID_ADSDomainGroup = gg.UID_ADSDomain ) ) where dbo.TSB_FGIGroupAccountMatching( gg.MatchPatternForMembership
31, nt.MatchPatternForMembership) = 1    and exists (select top 1 1 from ADS_VGroupMemberValid_Contact v where v.UID_ADSDomainContainer = gg.UID_ADSDomain
32 and v.uid_adsgroupcontainer = gg.uid_adsgroup and v.UID_ADSDomainMember = nt.UID_ADSDomain ) union all  select nt.uid_ADSContact , gg.uid_ADSgroup , nt.UID_ADSDomain
33 , nt.uid_person , 0  as AccountDisabled , nt.UID_TSBAccountDef , nt.UID_TSBBehavior, gg.XObjectKey from @DBQueueCurrent x join ADSContact nt on nt.uid_ADSContact
34 = x.uid_parameter   and nt.isGroupAccount = 1  join person px on nt.UID_Person = px.uid_person and px.isNoInherite = 0  join personHasESet phe on nt.uid_person
35 = phe.uid_person and phe.XOrigin > 0 and phe.XIsInEffect = 1 join ESetHasEntitlement ehe on phe.uid_ESet = ehe.uid_ESet and ehe.XOrigin > 0 and ehe.XIsInEffect
36 = 1 join ADSgroup gg on ehe.Entitlement = gg.XObjectKey  and gg.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Out = 0    and ( nt.UID_ADSDomain = 
37gg.UID_ADSDomain    or exists (select top 1 1 from ADS_VDomainTrustExists td where td.UID_ADSDomainMember = nt.UID_ADSDomain and td.UID_ADSDomainGroup 
38= gg.UID_ADSDomain ) ) where dbo.TSB_FGIGroupAccountMatching( gg.MatchPatternForMembership, nt.MatchPatternForMembership) = 1    and exists (select top
39 1 1 from ADS_VGroupMemberValid_Contact v where v.UID_ADSDomainContainer = gg.UID_ADSDomain and v.uid_adsgroupcontainer = gg.uid_adsgroup and v.UID_ADSDomainMember
40 = nt.UID_ADSDomain ) and dbo.QBM_FGIModuleExists('RMS') = 1 ) as x join ADSDomain d on x.UID_ADSDomain = d.UID_ADSDomain left outer join TSBSpecificGroupBehavior
41 ovr on ovr.ObjectKeyGroup = x.XObjectKey  left outer join person p on x.uid_person = p.uid_person  left outer join TSBBehavior bh on x.UID_TSBBehavior
42 = bh.UID_TSBBehavior   where (not (isnull(p.XMarkedForDeletion, 0) & @QBM_BitPatternXMarkedForDel_Delay > 0 and bh.PMDInheritGroup is null and @PMDKeepMembershipsOfLinkedAccount
43 <> 'ALL' and ovr.PMDInheritOverwrite is null ) )  ) as y   if 1 = dbo.DPR_FGIProjectionRootRunning ('ADSDomain') begin insert into @ObjectKeysToCheck_Projection
44(Parameter1, Parameter2) select cu.UID_Parameter, ro.ObjectKeyRoot from @DBQueueCurrent cu join @Sourcedata sd on cu.UID_Parameter = sd.Element join AdsGroup
45 g on sd.AssignedElement = g.uid_adsGroup join ADS_VElementAndRoot ro on g.XObjectKey = ro.ObjectKeyElement  join ADSContact a on cu.UID_Parameter = a.UID_ADSContact
46 and a.UID_ADSDomain <> g.UID_ADSDomain  where ro.ElementTable = 'AdsGroup' exec @RowsToReset = DPR_PSlotResetWhileProj_L @DBQueueCurrent, @ObjectKeysToCheck_Projection
47, @MyName, @SlotNumber if @RowsToReset > 0 begin delete  @DBQueueCurrent from @DBQueueCurrent cu left outer join QBMDBQueueCurrent d with (readpast) on
48 cu.UID_DialogDBQueue = d.UID_DialogDBQueue and d.SlotNumber = @SlotNumber where d.UID_DialogDBQueue is null delete  @Sourcedata from @Sourcedata s left
49 outer join @DBQueueCurrent cu on s.Element = cu.UID_Parameter where cu.UID_DialogDBQueue is null end end if dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\GroupExclusion'
50) > ' ' begin if exists (select top 1 1 from ADSGroupExclusion ex with (nolock) join @Sourcedata s on ex.UID_ADSGroupLower  = s.AssignedElement where s.IsUpcommingContent
51 = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 ) begin declare @ExcludeCandidate QER_YExcludeCandidate insert into @ExcludeCandidate(Element, AssignedElement
52, XIsInEffectAfter) select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_ADSGroupLower as UID_Excluded from ADSGroupExclusion
53 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
54, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_ADSGroupHigher as UID_Excluding from ADSGroupExclusion exe ) as ex on s.AssignedElement
55 = ex.UID_Excluding where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 update @ExcludeCandidate set XIsInEffectAfter = 0 
56from @ExcludeCandidate l  join ( select exe.UID_ADSGroupLower as UID_Excluded, exe.UID_ADSGroupHigher as UID_Excluding from ADSGroupExclusion exe ) as 
57ex on l.AssignedElement = ex.UID_Excluded join (select sh.* from @ExcludeCandidate sh  ) as h on l.Element = h.Element and h.AssignedElement = ex.UID_Excluding
58 if @@ROWCOUNT > 0 begin update @Sourcedata set XIsInEffectAfter = 0 from @Sourcedata l join @ExcludeCandidate k on l.Element = k.Element and l.AssignedElement
59 = k.AssignedElement and k.XIsInEffectAfter = 0 where l.IsUpcommingContent = 1 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1 end end  end   exec QBM_PDBQueueCalculateDelta
60 @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin
61 = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent if @CountDeltaOrigin > 0 begin exec
62 QBM_PMNTableOriginUpdate 'ADSContactInADSGroup', 'UID_ADSContact', 'UID_ADSGroup' end if @CountDeltaQantity > 0 begin exec QBM_PMNTableInsert 'ADSContactInADSGroup'
63, 'UID_ADSContact', 'UID_ADSGroup', @TargetIsView = 0 , @FKTableNameElement = 'ADSContact' , @FKColumnNameElement = 'UID_ADSContact' end          END TRY
64 BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: end 
65