dbo.TSB_ZOrgHasUNSGroupB2

1          create   procedure TSB_ZOrgHasUNSGroupB2 (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int ,
2 @CountDeltaOrigin int declare @GenProcID varchar(38) declare @UID_BasetreeAssignToUse varchar(38) = 'TSB-AsgnBT-UNSGroupB2' declare @DBQueueCurrent 
3QBM_YDBQueueCurrent declare @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv
4 int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1)  BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID
5) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount
6 = 0 begin goto EndLabel end  insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore , XIsInEffectBefore )
7 select 0, 0 , UID_org, UID_UNSGroupB2, XOrigin , XIsInEffect from BaseTreeHasUNSGroupB2 join @DBQueueCurrent x on UID_org =x.UID_parameter insert 
8into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement , XIsInEffectAfter ) select 1, 0, ohg.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv
9 , x.UID_parameter, ohg.UID_UNSGroupB2 , 1 from BaseTreeHasUNSGroupB2 ohg join @DBQueueCurrent x on ohg.UID_org = x.UID_parameter  where ohg.XOrigin
10 & @QBM_BitPatternXOrigin_Inherit_inv > 0 insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectAfter
11 ) select 1, 0 , x.UID_parameter, ohg.UID_UNSGroupB2, @QBM_BitPatternXOrigin_Inherit , 1 from BaseTreeHasUNSGroupB2 ohg join BaseTreeCollection oc 
12on ohg.UID_org = oc.UID_parentorg and oc.UID_org <> oc.UID_parentorg and ohg.XOrigin > 0 and ohg.XIsInEffect = 1 join UNSGroupB2 gg on ohg.UID_UNSGroupB2
13 = gg.UID_UNSGroupB2 join @DBQueueCurrent x on oc.UID_org = x.UID_parameter where  ohg.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 0 if dbo.QBM_FGIModuleExists
14('RMS') = 1 begin if dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\NoESetSplitting') = '' begin insert into @SourceData( IsUpcommingContent, XOriginBefore
15 , Element, AssignedElement, XOriginAfter , XIsInEffectAfter ) select 1, 0 , x.UID_parameter, y.UID_UNSGroupB2, @QBM_BitPatternXOrigin_Inherit  , 1 from
16 @DBQueueCurrent x join BaseTreeHasEset bhe on x.UID_Parameter = bhe.UID_org and bhe.XOrigin > 0 and bhe.XIsInEffect = 1 join ESetHasEntitlement ehe on
17 bhe.UID_ESet = ehe.UID_ESet and ehe.XOrigin > 0 and ehe.XIsInEffect = 1 join UNSGroupB2 y on ehe.entitlement = y.XObjectKey join BaseTree b on bhe.UID_Org
18 = b.UID_Org  and not (b.UID_OrgRoot in ( 'QER-V-ITShopOrg', 'QER-V-ITShopSrc') and b.ITShopInfo in ( 'BO', 'PR', 'BG', 'BT') ) join OrgRootAssign oa on
19 oa.UID_OrgRoot = b.UID_OrgRoot and oa.UID_BaseTreeAssign = @UID_BasetreeAssignToUse and oa.IsAssignmentAllowed = 1 end else begin  declare @DBQueueElements_01
20 QBM_YDBQueueRaw insert into @DBQueueElements_01 (object, subobject, genprocid) select x.UID, null, x.GenProcID from ( select cu.UID_Parameter as UID, 
21cu.GenProcID from @DBQueueCurrent cu ) as x exec QBM_PDBQueueInsert_Bulk 'TSB-K-BaseTreeHasObject', @DBQueueElements_01 end end  if dbo.QBM_FGIConfigparmValue
22('QER\Structures\Inherite\GroupExclusion') > ' ' begin if exists (select top 1 1 from UNSGroupB2Exclusion ex with (nolock) join @Sourcedata s on ex.UID_UNSGroupB2Lower
23  = s.AssignedElement where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 ) begin declare @ExcludeCandidate QER_YExcludeCandidate
24 insert into @ExcludeCandidate(Element, AssignedElement, XIsInEffectAfter) select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_UNSGroupB2Lower
25 as UID_Excluded from UNSGroupB2Exclusion exe ) as ex on s.AssignedElement = ex.UID_Excluded where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and
26 s.XIsInEffectAfter = 1 union all select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_UNSGroupB2Higher as UID_Excluding from
27 UNSGroupB2Exclusion exe ) as ex on s.AssignedElement = ex.UID_Excluding where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter
28 = 1  delete  @ExcludeCandidate from @ExcludeCandidate ex where exists (select top 1 1 from BaseTree b where ex.Element = b.UID_Org and b.UID_OrgRoot in
29 ( 'QER-V-ITShopOrg', 'QER-V-ITShopSrc') and b.ITShopInfo in ( 'BO',  'BG', 'BT') )  update @ExcludeCandidate set XIsInEffectAfter = 0 from @ExcludeCandidate
30 l  join ( select exe.UID_UNSGroupB2Lower as UID_Excluded, exe.UID_UNSGroupB2Higher as UID_Excluding from UNSGroupB2Exclusion exe ) as ex on l.AssignedElement
31 = ex.UID_Excluded join (select sh.* from @ExcludeCandidate sh  ) as h on l.Element = h.Element and h.AssignedElement = ex.UID_Excluding if @@ROWCOUNT 
32> 0 begin update @Sourcedata set XIsInEffectAfter = 0 from @Sourcedata l join @ExcludeCandidate k on l.Element = k.Element and l.AssignedElement = k.AssignedElement
33 and k.XIsInEffectAfter = 0 where l.IsUpcommingContent = 1 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1 end end  end   exec QBM_PDBQueueCalculateDelta
34 @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin
35 = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent if @CountDeltaOrigin > 0 begin exec
36 QBM_PMNTableOriginUpdate 'BaseTreeHasUNSGroupB2', 'UID_Org', 'UID_UNSGroupB2' end if @CountDeltaQantity > 0 begin exec QER_PMNTableAddViewProperties
37 'BaseTreeHasUNSGroupB2' exec QBM_PMNTableInsert 'BaseTreeHasUNSGroupB2', 'UID_Org', 'UID_UNSGroupB2', @TargetIsView = 1 , @FKTableNameElement = 
38'BaseTree' , @FKColumnNameElement = 'UID_Org' end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel:
39 end 
40