dbo.ADS_ZAccountInADSGroup

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