dbo.RMS_ZOrgHasESet

1       create   procedure RMS_ZOrgHasESet (@SlotNumber int) AS begin declare @GenProcID varchar(38) declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity
2 int , @CountDeltaOrigin int declare @DebugSwitch int = 0 declare @UID_BasetreeAssignToUse varchar(38) = 'RMS-AsgnBT-Eset' declare @DBQueueCurrent QBM_YDBQueueCurrent
3 declare @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin
4('|Inherit|', 1)  BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter
5, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end if 
6dbo.QBM_FGITableCountAll('Eset') = 0 begin goto endLabel end insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore
7 , XIsInEffectBefore ) select 0, 0 , oha.uid_org, oha.uid_ESet, oha.XOrigin , oha.XIsInEffect from BaseTreeHasESet oha join @DBQueueCurrent x on oha.uid_org
8 =x.uid_parameter insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement , XIsInEffectAfter ) select 1, 0,
9 oha.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv , oha.uid_org, oha.uid_ESet , e.IsInActive ^ 1 from BaseTreeHasESet oha join @DBQueueCurrent x on oha.uid_org
10 = x.uid_parameter  join ESet e on oha.UID_ESet = e.UID_ESet where oha.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 0 insert into @SourceData( IsUpcommingContent
11, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectAfter ) select 1, 0 , x.uid_parameter, e.UID_ESet, @QBM_BitPatternXOrigin_Inherit
12 , e.IsInActive ^ 1 from @DBQueueCurrent x join BaseTreeCollection oc on oc.uid_org = x.uid_parameter and oc.uid_org <> oc.uid_parentorg join BaseTreeHasESet
13 oha on oha.uid_org = oc.uid_parentorg and oha.XOrigin > 0 and oha.XIsInEffect = 1 join ESet e on oha.UID_ESet = e.UID_ESet where oha.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv
14 > 0  insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectAfter ) select 1, 0 , x.uid_parameter
15, e.UID_Eset, @QBM_BitPatternXOrigin_Inherit , e.IsInActive ^ 1 from @DBQueueCurrent x join  @Sourcedata oha on oha.Element = x.uid_parameter and oha.IsUpcommingContent
16 = 1 and oha.XOriginAfter > 0 and oha.XIsInEffectAfter = 1 join ESetCollection ec on oha.AssignedElement = ec.uid_ESet and ec.uid_eset <> ec.UID_EsetChild
17 join ESet e on ec.UID_ESetChild = e.UID_ESet  join BaseTree b on oha.Element = b.UID_Org where not (b.UID_OrgRoot in ( 'QER-V-ITShopOrg', 'QER-V-ITShopSrc'
18) and b.ITShopInfo in ( 'BO', 'PR', 'BG', 'BT') ) if dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\ESetExclusion') > ' '  begin if exists (select
19 top 1 1 from ESetExcludesEset ex with (nolock) join @Sourcedata s on ex.UID_ESetExcluded  = s.AssignedElement where s.IsUpcommingContent = 1 and s.XOriginAfter
20 > 0 and s.XIsInEffectAfter = 1 ) begin declare @ExcludeCandidate QER_YExcludeCandidate insert into @ExcludeCandidate(Element, AssignedElement, XIsInEffectAfter
21) select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_ESetExcluded as UID_Excluded from ESetExcludesEset exe ) as ex on s.AssignedElement
22 = ex.UID_Excluded where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 union all select s.Element, s.AssignedElement, 1 from
23 @Sourcedata s join ( select exe.UID_ESet as UID_Excluding from ESetExcludesEset exe ) as ex on s.AssignedElement = ex.UID_Excluding where s.IsUpcommingContent
24 = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1  delete  @ExcludeCandidate from @ExcludeCandidate ex where exists (select top 1 1 from BaseTree 
25b where ex.Element = b.UID_Org and b.UID_OrgRoot in ( 'QER-V-ITShopOrg', 'QER-V-ITShopSrc') and b.ITShopInfo in ( 'BO',  'BG', 'BT') )  update @ExcludeCandidate
26 set XIsInEffectAfter = 0 from @ExcludeCandidate l  join ( select exe.UID_ESetExcluded as UID_Excluded, exe.UID_ESet as UID_Excluding from ESetExcludesEset
27 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 
28= ex.UID_Excluding if @@ROWCOUNT > 0 begin update @Sourcedata set XIsInEffectAfter = 0 from @Sourcedata l join @ExcludeCandidate k on l.Element = k.Element
29 and l.AssignedElement = k.AssignedElement and k.XIsInEffectAfter = 0 where l.IsUpcommingContent = 1 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1 
30end end  end   exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert = 1, @DeltaOrigin = 1, @CountDeltaQantity
31 = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern = 
32@DBQueueCurrent if @DebugSwitch = 1 begin print '#QBMDeltaOrigin' select * from #QBMDeltaOrigin print '#QBMDeltaInsert' select * from #QBMDeltaInsert print
33 '#QBMDeltaDelete' select * from #QBMDeltaDelete end if @CountDeltaOrigin > 0 begin exec QBM_PMNTableOriginUpdate 'BaseTreeHasESet', 'UID_Org', 'UID_Eset'
34 end if @CountDeltaQantity > 0 begin exec QER_PMNTableAddViewProperties 'BaseTreeHasESet' exec QBM_PMNTableInsert 'BaseTreeHasESet', 'UID_Org', 'UID_Eset'
35, @TargetIsView = 1 , @FKTableNameElement = 'BaseTree' , @FKColumnNameElement = 'UID_Org' end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR
36 ('', 18, 1) WITH NOWAIT END CATCH endLabel: end 
37