Back to OIM Explorer

dbo.TSB_ZOrgHasUNSGroupB

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure. Bulk DBQueue insert -> TSB-K-BaseTreeHasObject / TSB_ZBaseTreeHasObject at line 21; References QBM_PDBQueueInsert_Bulk

Source: sandbox-db sys.sql_modules

Source size: 5.934 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.
  • DBQueue relation detected. Follow the task procedure and referenced-by list for async processing.

Relations

  • Bulk DBQueue insert -> TSB-K-BaseTreeHasObject / TSB_ZBaseTreeHasObject at line 21
  • References QBM_PDBQueueInsert_Bulk

Typed Edges

  • queues DBQueue task TSB_ZBaseTreeHasObject at line 21 Bulk DBQueue insert -> TSB-K-BaseTreeHasObject / TSB_ZBaseTreeHasObject at line 21
  • references source dbo.QBM_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGIModuleExists source text reference
  • references source dbo.QBM_PDBQueueCalculateDelta source text reference
  • references source dbo.QBM_PDBQueueInsert_Bulk source text reference
  • references source dbo.QBM_PMNTableInsert source text reference
  • references source dbo.QBM_PMNTableOriginUpdate source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PMNTableAddViewProperties source text reference
  • queues DBQueue task TSB-K-BaseTreeHasObject -> TSB_ZBaseTreeHasObject QBM_PDBQueueInsert_Bulk 'TSB-K-BaseTreeHasObject', @DBQueueElements_01 end end if dbo.QBM_FGIConfigparmValue ('QER\Structures\Inherite\GroupExclusion') > ' ' begin if exists (select top 1 1 from UNSGroupBExclusion ex with (nolock) join @So…

References

Referenced By

Complete Source

SQL246 lines
1CREATE PROCEDURE TSB_ZOrgHasUNSGroupB(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @Sourcedata QBM_YDataForDelta,7  @CountDeltaQantity int,8  @CountDeltaOrigin int9  DECLARE @GenProcID varchar(38)10  DECLARE @UID_BasetreeAssignToUse varchar(38) = 'TSB-AsgnBT-UNSGroupB'11  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent12  DECLARE @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',13  0)14  DECLARE @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',15  1)16  BEGIN TRY17    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,18    UID_Parameter,19    UID_SubParameter,20    GenProcID)21    SELECT22      UID_DialogDBQueue,23      UID_Parameter,24      UID_SubParameter,25      GenProcID26    FROM QBMDBQueueCurrent cu27      WITH(readpast)28    WHERE29      cu.SlotNumber = @SlotNumber30    IF @@rowcount = 031    BEGIN32      GOTO EndLabel33    END34    INSERT INTO @SourceData(IsUpcommingContent,35    XOriginAfter,36    Element,37    AssignedElement,38    XOriginBefore,39    XIsInEffectBefore)40    SELECT41      0,42      0,43      UID_org,44      UID_UNSGroupB,45      XOrigin,46      XIsInEffect47    FROM BaseTreeHasUNSGroupB48    JOIN @DBQueueCurrent x49      ON UID_org =x.UID_parameter50    INSERT INTO @SourceData(IsUpcommingContent,51    XOriginBefore,52    XOriginAfter,53    Element,54    AssignedElement,55    XIsInEffectAfter)56    SELECT57      1,58      0,59      ohg.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv,60      x.UID_parameter,61      ohg.UID_UNSGroupB,62      163    FROM BaseTreeHasUNSGroupB ohg64    JOIN @DBQueueCurrent x65      ON ohg.UID_org = x.UID_parameter66    WHERE67      ohg.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 068    INSERT INTO @SourceData(IsUpcommingContent,69    XOriginBefore,70    Element,71    AssignedElement,72    XOriginAfter,73    XIsInEffectAfter)74    SELECT75      1,76      0,77      x.UID_parameter,78      ohg.UID_UNSGroupB,79      @QBM_BitPatternXOrigin_Inherit,80      181    FROM BaseTreeHasUNSGroupB ohg82    JOIN BaseTreeCollection oc83      ON ohg.UID_org = oc.UID_parentorg AND oc.UID_org <> oc.UID_parentorg AND ohg.XOrigin > 0 AND ohg.XIsInEffect = 184    JOIN UNSGroupB gg85      ON ohg.UID_UNSGroupB = gg.UID_UNSGroupB86    JOIN @DBQueueCurrent x87      ON oc.UID_org = x.UID_parameter88    WHERE89      ohg.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 090    IF dbo.QBM_FGIModuleExists('RMS') = 191    BEGIN92      IF dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\NoESetSplitting') = ''93      BEGIN94        INSERT INTO @SourceData(IsUpcommingContent,95        XOriginBefore,96        Element,97        AssignedElement,98        XOriginAfter,99        XIsInEffectAfter)100        SELECT101          1,102          0,103          x.UID_parameter,104          y.UID_UNSGroupB,105          @QBM_BitPatternXOrigin_Inherit,106          1107        FROM @DBQueueCurrent x108        JOIN BaseTreeHasEset bhe109          ON x.UID_Parameter = bhe.UID_org AND bhe.XOrigin > 0 AND bhe.XIsInEffect = 1110        JOIN ESetHasEntitlement ehe111          ON bhe.UID_ESet = ehe.UID_ESet AND ehe.XOrigin > 0 AND ehe.XIsInEffect = 1112        JOIN UNSGroupB y113          ON ehe.entitlement = y.XObjectKey114        JOIN BaseTree b115          ON bhe.UID_Org = b.UID_Org AND NOT(b.UID_OrgRoot IN('QER-V-ITShopOrg', 'QER-V-ITShopSrc') AND b.ITShopInfo IN('BO',116        'PR', 'BG', 'BT'))117        JOIN OrgRootAssign oa118          ON oa.UID_OrgRoot = b.UID_OrgRoot AND oa.UID_BaseTreeAssign = @UID_BasetreeAssignToUse AND oa.IsAssignmentAllowed = 1119      END120      ELSE121      BEGIN122        DECLARE @DBQueueElements_01 QBM_YDBQueueRaw123        INSERT INTO @DBQueueElements_01(object,124        subobject,125        genprocid)126        SELECT127          x.UID,128          NULL,129          x.GenProcID130        FROM(131        SELECT132          cu.UID_Parameter AS UID,133          cu.GenProcID134        FROM @DBQueueCurrent cu) AS x135        EXEC QBM_PDBQueueInsert_Bulk 'TSB-K-BaseTreeHasObject',136          @DBQueueElements_01137      END138    END139    IF dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\GroupExclusion') > ' '140    BEGIN141      IF EXISTS(142        SELECT TOP 1 1143        FROM UNSGroupBExclusion ex144        WITH(nolock)145      JOIN @Sourcedata s146        ON ex.UID_UNSGroupBLower = s.AssignedElement147      WHERE148        s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1)149      BEGIN150        DECLARE @ExcludeCandidate QER_YExcludeCandidate151        INSERT INTO @ExcludeCandidate(Element,152        AssignedElement,153        XIsInEffectAfter)154        SELECT155          s.Element,156          s.AssignedElement,157          1158        FROM @Sourcedata s159        JOIN(160        SELECT exe.UID_UNSGroupBLower AS UID_Excluded161        FROM UNSGroupBExclusion exe) AS ex162          ON s.AssignedElement = ex.UID_Excluded163        WHERE164          s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1165        UNION all166        SELECT167          s.Element,168          s.AssignedElement,169          1170        FROM @Sourcedata s171        JOIN(172        SELECT exe.UID_UNSGroupBHigher AS UID_Excluding173        FROM UNSGroupBExclusion exe) AS ex174          ON s.AssignedElement = ex.UID_Excluding175        WHERE176          s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1177        DELETE @ExcludeCandidate178        FROM @ExcludeCandidate ex179        WHERE180          EXISTS(181        SELECT TOP 1 1182        FROM BaseTree b183        WHERE184          ex.Element = b.UID_Org AND b.UID_OrgRoot IN('QER-V-ITShopOrg', 'QER-V-ITShopSrc') AND b.ITShopInfo IN('BO',185        'BG', 'BT'))186        UPDATE @ExcludeCandidate187        SET XIsInEffectAfter = 0188        FROM @ExcludeCandidate l189        JOIN(190        SELECT191          exe.UID_UNSGroupBLower AS UID_Excluded,192          exe.UID_UNSGroupBHigher AS UID_Excluding193        FROM UNSGroupBExclusion exe) AS ex194          ON l.AssignedElement = ex.UID_Excluded195        JOIN(196        SELECT sh.*197        FROM @ExcludeCandidate sh) AS h198          ON l.Element = h.Element AND h.AssignedElement = ex.UID_Excluding199        IF @@ROWCOUNT > 0200        BEGIN201          UPDATE @Sourcedata202          SET XIsInEffectAfter = 0203          FROM @Sourcedata l204          JOIN @ExcludeCandidate k205            ON l.Element = k.Element AND l.AssignedElement = k.AssignedElement AND k.XIsInEffectAfter = 0206          WHERE207            l.IsUpcommingContent = 1 AND l.XOriginAfter > 0 AND l.XIsInEffectAfter = 1208        END209      END210    END211    EXEC QBM_PDBQueueCalculateDelta @SourceData,212      @DeltaQuantity = 0,213      @DeltaDelete = 0,214      @DeltaInsert = 1,215      @DeltaOrigin = 1,216      @CountDeltaQantity = @CountDeltaQantity OUTPUT,217      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,218      @UseIsInEffect = 1,219      @SlotNumber = @SlotNumber,220      @DBQueueCurrentExtern = @DBQueueCurrent221    IF @CountDeltaOrigin > 0222    BEGIN223      EXEC QBM_PMNTableOriginUpdate 'BaseTreeHasUNSGroupB',224      'UID_Org',225      'UID_UNSGroupB'226    END227    IF @CountDeltaQantity > 0228    BEGIN229      EXEC QER_PMNTableAddViewProperties 'BaseTreeHasUNSGroupB'230      EXEC QBM_PMNTableInsert 'BaseTreeHasUNSGroupB',231      'UID_Org',232      'UID_UNSGroupB',233        @TargetIsView = 1,234        @FKTableNameElement = 'BaseTree',235        @FKColumnNameElement = 'UID_Org'236    END237  END TRY238  BEGIN CATCH239    EXEC QBM_PSessionErrorAdd DEFAULT240    RAISERROR('',241    18,242    1)243      WITH NOWAIT244  END CATCH245  endLabel:246END
Open raw exported source
SQL · Raw40 lines
1          create   procedure TSB_ZOrgHasUNSGroupB (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int ,2 @CountDeltaOrigin int declare @GenProcID varchar(38) declare @UID_BasetreeAssignToUse varchar(38) = 'TSB-AsgnBT-UNSGroupB' declare @DBQueueCurrent 3QBM_YDBQueueCurrent declare @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv4 int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1)  BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID5) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount6 = 0 begin goto EndLabel end  insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore , XIsInEffectBefore )7 select 0, 0 , UID_org, UID_UNSGroupB, XOrigin , XIsInEffect from BaseTreeHasUNSGroupB 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_inv9 , x.UID_parameter, ohg.UID_UNSGroupB , 1 from BaseTreeHasUNSGroupB ohg join @DBQueueCurrent x on ohg.UID_org = x.UID_parameter  where ohg.XOrigin10 & @QBM_BitPatternXOrigin_Inherit_inv > 0 insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectAfter11 ) select 1, 0 , x.UID_parameter, ohg.UID_UNSGroupB, @QBM_BitPatternXOrigin_Inherit , 1 from BaseTreeHasUNSGroupB 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 UNSGroupB gg on ohg.UID_UNSGroupB13 = gg.UID_UNSGroupB join @DBQueueCurrent x on oc.UID_org = x.UID_parameter where  ohg.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 0 if dbo.QBM_FGIModuleExists14('RMS') = 1 begin if dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\NoESetSplitting') = '' begin insert into @SourceData( IsUpcommingContent, XOriginBefore15 , Element, AssignedElement, XOriginAfter , XIsInEffectAfter ) select 1, 0 , x.UID_parameter, y.UID_UNSGroupB, @QBM_BitPatternXOrigin_Inherit  , 1 from16 @DBQueueCurrent x join BaseTreeHasEset bhe on x.UID_Parameter = bhe.UID_org and bhe.XOrigin > 0 and bhe.XIsInEffect = 1 join ESetHasEntitlement ehe on17 bhe.UID_ESet = ehe.UID_ESet and ehe.XOrigin > 0 and ehe.XIsInEffect = 1 join UNSGroupB y on ehe.entitlement = y.XObjectKey join BaseTree b on bhe.UID_Org18 = 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 on19 oa.UID_OrgRoot = b.UID_OrgRoot and oa.UID_BaseTreeAssign = @UID_BasetreeAssignToUse and oa.IsAssignmentAllowed = 1 end else begin  declare @DBQueueElements_0120 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_FGIConfigparmValue22('QER\Structures\Inherite\GroupExclusion') > ' ' begin if exists (select top 1 1 from UNSGroupBExclusion ex with (nolock) join @Sourcedata s on ex.UID_UNSGroupBLower23  = s.AssignedElement where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 ) begin declare @ExcludeCandidate QER_YExcludeCandidate24 insert into @ExcludeCandidate(Element, AssignedElement, XIsInEffectAfter) select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_UNSGroupBLower25 as UID_Excluded from UNSGroupBExclusion exe ) as ex on s.AssignedElement = ex.UID_Excluded where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and26 s.XIsInEffectAfter = 1 union all select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_UNSGroupBHigher as UID_Excluding from27 UNSGroupBExclusion exe ) as ex on s.AssignedElement = ex.UID_Excluding where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter28 = 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 in29 ( 'QER-V-ITShopOrg', 'QER-V-ITShopSrc') and b.ITShopInfo in ( 'BO',  'BG', 'BT') )  update @ExcludeCandidate set XIsInEffectAfter = 0 from @ExcludeCandidate30 l  join ( select exe.UID_UNSGroupBLower as UID_Excluded, exe.UID_UNSGroupBHigher as UID_Excluding from UNSGroupBExclusion exe ) as ex on l.AssignedElement31 = 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.AssignedElement33 and k.XIsInEffectAfter = 0 where l.IsUpcommingContent = 1 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1 end end  end   exec QBM_PDBQueueCalculateDelta34 @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin35 = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent if @CountDeltaOrigin > 0 begin exec36 QBM_PMNTableOriginUpdate 'BaseTreeHasUNSGroupB', 'UID_Org', 'UID_UNSGroupB' end if @CountDeltaQantity > 0 begin exec QER_PMNTableAddViewProperties37 'BaseTreeHasUNSGroupB' exec QBM_PMNTableInsert 'BaseTreeHasUNSGroupB', 'UID_Org', 'UID_UNSGroupB', @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