dbo.RMS_ZPersonHasESet

1       create   procedure RMS_ZPersonHasESet (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int , @CountDeltaOrigin
2 int declare @GenProcID varchar(38) declare @Xdate datetime = getutcdate() declare @XUser nvarchar(64) = object_name(@@procid) declare @MyName nvarchar
3(64) = object_name(@@procid) declare @UID_BaseTreeAssignToUse varchar(38) = 'RMS-AsgnBT-ESet' declare @DBQueueCurrent QBM_YDBQueueCurrent declare @QBM_BitPatternXOrigin_Inherit
4 int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1)  BEGIN
5 TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter
6, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end if dbo.QBM_FGITableCountAll
7('Eset') = 0 begin goto endLabel end  insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore , XIsInEffectBefore
8, XIsInEffectAfter ) select 0, 0 , uid_person, UID_ESet, XOrigin , phe.XIsInEffect, 0 from PersonHasESet phe join @DBQueueCurrent x on uid_person = x.uid_parameter
9 insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement , XIsInEffectBefore, XIsInEffectAfter ) select 1, 
100, phe.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv , phe.UID_Person, e.UID_ESet , 0, e.IsInActive ^ 1 from PersonHasESet phe join @DBQueueCurrent x on
11 uid_person= x.uid_parameter join ESet e on phe.UID_ESet = e.UID_ESet insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement
12, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0 , x.uid_person, x.UID_ESet, @QBM_BitPatternXOrigin_Inherit ,0 , x.IsInActive ^ 1 from
13 ( select pfo.uid_person, e.UID_Eset as uid_ESet , e.IsInActive from  dbo.QER_FTPersonInheritFromOrg_L(@DBQueueCurrent, @UID_BaseTreeAssignToUse) pfo join
14 BaseTreeHasESet oha on pfo.uid_org = oha.uid_org and oha.XOrigin > 0 and oha.XIsInEffect = 1 join ESet e on oha.UID_ESet = e.UID_ESet         ) as x  
15insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0
16 , x.uid_person, x.UID_ESet, @QBM_BitPatternXOrigin_Inherit ,0 , x.IsInActive ^ 1 from ( select s.Element as uid_Person, e.UID_ESet as UID_Eset, e.IsInActive
17 from @SourceData s join ESetCollection ec on s.AssignedElement = ec.uid_ESet and ec.uid_ESet <> ec.UID_EsetChild and s.XOriginAfter > 0 and s.IsUpcommingContent
18 = 1 join ESet e on ec.UID_ESetChild = e.UID_ESet join Person p on p.UID_Person = s.Element and p.IsNoInherite = 0 where s.IsUpcommingContent = 1 ) as 
19x if dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\ESetExclusion') > ' '  begin if exists (select top 1 1 from ESetExcludesEset ex with (nolock) 
20join @Sourcedata s on ex.UID_ESetExcluded  = s.AssignedElement where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 ) begin
21 declare @ExcludeCandidate QER_YExcludeCandidate insert into @ExcludeCandidate(Element, AssignedElement, XIsInEffectAfter) select s.Element, s.AssignedElement
22, 1 from @Sourcedata s join ( select exe.UID_ESetExcluded as UID_Excluded from ESetExcludesEset exe ) as ex on s.AssignedElement = ex.UID_Excluded where
23 s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 union all select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( 
24select exe.UID_ESet as UID_Excluding from ESetExcludesEset exe ) as ex on s.AssignedElement = ex.UID_Excluding where s.IsUpcommingContent = 1 and s.XOriginAfter
25 > 0 and s.XIsInEffectAfter = 1 update @ExcludeCandidate set XIsInEffectAfter = 0 from @ExcludeCandidate l  join ( select exe.UID_ESetExcluded as UID_Excluded
26, exe.UID_ESet as UID_Excluding from ESetExcludesEset exe ) as ex on l.AssignedElement = ex.UID_Excluded join (select sh.* from @ExcludeCandidate sh  )
27 as h on l.Element = h.Element and h.AssignedElement = ex.UID_Excluding if @@ROWCOUNT > 0 begin update @Sourcedata set XIsInEffectAfter = 0 from @Sourcedata
28 l join @ExcludeCandidate k on l.Element = k.Element and l.AssignedElement = k.AssignedElement and k.XIsInEffectAfter = 0 where l.IsUpcommingContent = 
291 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1 end end  end   exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert
30 = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber
31 = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent if @CountDeltaOrigin > 0 begin exec QBM_PMNTableOriginUpdate 'personhasESet', 'uid_Person', 'uid_ESet'
32 end if @CountDeltaQantity > 0 begin exec QBM_PMNTableInsert 'personhasESet', 'uid_Person', 'uid_ESet', @TargetIsView = 0 , @FKTableNameElement = 'Person'
33 , @FKColumnNameElement = 'UID_Person' end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: 
34end 
35