Back to OIM Explorer

dbo.RMS_ZOrgHasESet

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.625 characters

Interpretation

  • Database routine. Review parameters, called procedures, DBQueue inserts, and QBM_PJobCreate helper calls before assuming side effects.

Relations

  • No extracted relations.

Typed Edges

  • references source dbo.QBM_FGIBitPatternXOrigin source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QBM_FGITableCountAll source text reference
  • references source dbo.QBM_PDBQueueCalculateDelta 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

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL239 lines
1CREATE PROCEDURE RMS_ZOrgHasESet(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @GenProcID varchar(38)7  DECLARE @Sourcedata QBM_YDataForDelta,8  @CountDeltaQantity int,9  @CountDeltaOrigin int10  DECLARE @DebugSwitch int = 011  DECLARE @UID_BasetreeAssignToUse varchar(38) = 'RMS-AsgnBT-Eset'12  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent13  DECLARE @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',14  0)15  DECLARE @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',16  1)17  BEGIN TRY18    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,19    UID_Parameter,20    UID_SubParameter,21    GenProcID)22    SELECT23      UID_DialogDBQueue,24      UID_Parameter,25      UID_SubParameter,26      GenProcID27    FROM QBMDBQueueCurrent cu28      WITH(readpast)29    WHERE30      cu.SlotNumber = @SlotNumber31    IF @@rowcount = 032    BEGIN33      GOTO EndLabel34    END35    IF dbo.QBM_FGITableCountAll('Eset') = 036    BEGIN37      GOTO endLabel38    END39    INSERT INTO @SourceData(IsUpcommingContent,40    XOriginAfter,41    Element,42    AssignedElement,43    XOriginBefore,44    XIsInEffectBefore)45    SELECT46      0,47      0,48      oha.uid_org,49      oha.uid_ESet,50      oha.XOrigin,51      oha.XIsInEffect52    FROM BaseTreeHasESet oha53    JOIN @DBQueueCurrent x54      ON oha.uid_org =x.uid_parameter55    INSERT INTO @SourceData(IsUpcommingContent,56    XOriginBefore,57    XOriginAfter,58    Element,59    AssignedElement,60    XIsInEffectAfter)61    SELECT62      1,63      0,64      oha.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv,65      oha.uid_org,66      oha.uid_ESet,67      e.IsInActive ^ 168    FROM BaseTreeHasESet oha69    JOIN @DBQueueCurrent x70      ON oha.uid_org = x.uid_parameter71    JOIN ESet e72      ON oha.UID_ESet = e.UID_ESet73    WHERE74      oha.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 075    INSERT INTO @SourceData(IsUpcommingContent,76    XOriginBefore,77    Element,78    AssignedElement,79    XOriginAfter,80    XIsInEffectAfter)81    SELECT82      1,83      0,84      x.uid_parameter,85      e.UID_ESet,86      @QBM_BitPatternXOrigin_Inherit,87      e.IsInActive ^ 188    FROM @DBQueueCurrent x89    JOIN BaseTreeCollection oc90      ON oc.uid_org = x.uid_parameter AND oc.uid_org <> oc.uid_parentorg91    JOIN BaseTreeHasESet oha92      ON oha.uid_org = oc.uid_parentorg AND oha.XOrigin > 0 AND oha.XIsInEffect = 193    JOIN ESet e94      ON oha.UID_ESet = e.UID_ESet95    WHERE96      oha.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 097    INSERT INTO @SourceData(IsUpcommingContent,98    XOriginBefore,99    Element,100    AssignedElement,101    XOriginAfter,102    XIsInEffectAfter)103    SELECT104      1,105      0,106      x.uid_parameter,107      e.UID_Eset,108      @QBM_BitPatternXOrigin_Inherit,109      e.IsInActive ^ 1110    FROM @DBQueueCurrent x111    JOIN @Sourcedata oha112      ON oha.Element = x.uid_parameter AND oha.IsUpcommingContent = 1 AND oha.XOriginAfter > 0 AND oha.XIsInEffectAfter = 1113    JOIN ESetCollection ec114      ON oha.AssignedElement = ec.uid_ESet AND ec.uid_eset <> ec.UID_EsetChild115    JOIN ESet e116      ON ec.UID_ESetChild = e.UID_ESet117    JOIN BaseTree b118      ON oha.Element = b.UID_Org119    WHERE120      NOT(b.UID_OrgRoot IN('QER-V-ITShopOrg', 'QER-V-ITShopSrc') AND b.ITShopInfo IN('BO', 'PR', 'BG',121    'BT'))122    IF dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\ESetExclusion') > ' '123    BEGIN124      IF EXISTS(125        SELECT TOP 1 1126        FROM ESetExcludesEset ex127        WITH(nolock)128      JOIN @Sourcedata s129        ON ex.UID_ESetExcluded = s.AssignedElement130      WHERE131        s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1)132      BEGIN133        DECLARE @ExcludeCandidate QER_YExcludeCandidate134        INSERT INTO @ExcludeCandidate(Element,135        AssignedElement,136        XIsInEffectAfter)137        SELECT138          s.Element,139          s.AssignedElement,140          1141        FROM @Sourcedata s142        JOIN(143        SELECT exe.UID_ESetExcluded AS UID_Excluded144        FROM ESetExcludesEset exe) AS ex145          ON s.AssignedElement = ex.UID_Excluded146        WHERE147          s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1148        UNION all149        SELECT150          s.Element,151          s.AssignedElement,152          1153        FROM @Sourcedata s154        JOIN(155        SELECT exe.UID_ESet AS UID_Excluding156        FROM ESetExcludesEset exe) AS ex157          ON s.AssignedElement = ex.UID_Excluding158        WHERE159          s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1160        DELETE @ExcludeCandidate161        FROM @ExcludeCandidate ex162        WHERE163          EXISTS(164        SELECT TOP 1 1165        FROM BaseTree b166        WHERE167          ex.Element = b.UID_Org AND b.UID_OrgRoot IN('QER-V-ITShopOrg', 'QER-V-ITShopSrc') AND b.ITShopInfo IN('BO',168        'BG', 'BT'))169        UPDATE @ExcludeCandidate170        SET XIsInEffectAfter = 0171        FROM @ExcludeCandidate l172        JOIN(173        SELECT174          exe.UID_ESetExcluded AS UID_Excluded,175          exe.UID_ESet AS UID_Excluding176        FROM ESetExcludesEset exe) AS ex177          ON l.AssignedElement = ex.UID_Excluded178        JOIN(179        SELECT sh.*180        FROM @ExcludeCandidate sh) AS h181          ON l.Element = h.Element AND h.AssignedElement = ex.UID_Excluding182        IF @@ROWCOUNT > 0183        BEGIN184          UPDATE @Sourcedata185          SET XIsInEffectAfter = 0186          FROM @Sourcedata l187          JOIN @ExcludeCandidate k188            ON l.Element = k.Element AND l.AssignedElement = k.AssignedElement AND k.XIsInEffectAfter = 0189          WHERE190            l.IsUpcommingContent = 1 AND l.XOriginAfter > 0 AND l.XIsInEffectAfter = 1191        END192      END193    END194    EXEC QBM_PDBQueueCalculateDelta @SourceData,195      @DeltaQuantity = 0,196      @DeltaDelete = 0,197      @DeltaInsert = 1,198      @DeltaOrigin = 1,199      @CountDeltaQantity = @CountDeltaQantity OUTPUT,200      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,201      @UseIsInEffect = 1,202      @SlotNumber = @SlotNumber,203      @DBQueueCurrentExtern = @DBQueueCurrent204    IF @DebugSwitch = 1205    BEGIN206      print '#QBMDeltaOrigin'207      SELECT *208      FROM #QBMDeltaOrigin print '#QBMDeltaInsert'209      SELECT *210      FROM #QBMDeltaInsert print '#QBMDeltaDelete'211      SELECT *212      FROM #QBMDeltaDelete213    END214    IF @CountDeltaOrigin > 0215    BEGIN216      EXEC QBM_PMNTableOriginUpdate 'BaseTreeHasESet',217      'UID_Org',218      'UID_Eset'219    END220    IF @CountDeltaQantity > 0221    BEGIN222      EXEC QER_PMNTableAddViewProperties 'BaseTreeHasESet'223      EXEC QBM_PMNTableInsert 'BaseTreeHasESet',224      'UID_Org',225      'UID_Eset',226        @TargetIsView = 1,227        @FKTableNameElement = 'BaseTree',228        @FKColumnNameElement = 'UID_Org'229    END230  END TRY231  BEGIN CATCH232    EXEC QBM_PSessionErrorAdd DEFAULT233    RAISERROR('',234    18,235    1)236      WITH NOWAIT237  END CATCH238  endLabel:239END
Open raw exported source
SQL ยท Raw37 lines
1       create   procedure RMS_ZOrgHasESet (@SlotNumber int) AS begin declare @GenProcID varchar(38) declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity2 int , @CountDeltaOrigin int declare @DebugSwitch int = 0 declare @UID_BasetreeAssignToUse varchar(38) = 'RMS-AsgnBT-Eset' declare @DBQueueCurrent QBM_YDBQueueCurrent3 declare @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin4('|Inherit|', 1)  BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter5, 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, XOriginBefore7 , XIsInEffectBefore ) select 0, 0 , oha.uid_org, oha.uid_ESet, oha.XOrigin , oha.XIsInEffect from BaseTreeHasESet oha join @DBQueueCurrent x on oha.uid_org8 =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_org10 = x.uid_parameter  join ESet e on oha.UID_ESet = e.UID_ESet where oha.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 0 insert into @SourceData( IsUpcommingContent11, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectAfter ) select 1, 0 , x.uid_parameter, e.UID_ESet, @QBM_BitPatternXOrigin_Inherit12 , e.IsInActive ^ 1 from @DBQueueCurrent x join BaseTreeCollection oc on oc.uid_org = x.uid_parameter and oc.uid_org <> oc.uid_parentorg join BaseTreeHasESet13 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_inv14 > 0  insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectAfter ) select 1, 0 , x.uid_parameter15, e.UID_Eset, @QBM_BitPatternXOrigin_Inherit , e.IsInActive ^ 1 from @DBQueueCurrent x join  @Sourcedata oha on oha.Element = x.uid_parameter and oha.IsUpcommingContent16 = 1 and oha.XOriginAfter > 0 and oha.XIsInEffectAfter = 1 join ESetCollection ec on oha.AssignedElement = ec.uid_ESet and ec.uid_eset <> ec.UID_EsetChild17 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 (select19 top 1 1 from ESetExcludesEset ex with (nolock) join @Sourcedata s on ex.UID_ESetExcluded  = s.AssignedElement where s.IsUpcommingContent = 1 and s.XOriginAfter20 > 0 and s.XIsInEffectAfter = 1 ) begin declare @ExcludeCandidate QER_YExcludeCandidate insert into @ExcludeCandidate(Element, AssignedElement, XIsInEffectAfter21) select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( select exe.UID_ESetExcluded as UID_Excluded from ESetExcludesEset exe ) as ex on s.AssignedElement22 = ex.UID_Excluded where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 union all select s.Element, s.AssignedElement, 1 from23 @Sourcedata s join ( select exe.UID_ESet as UID_Excluding from ESetExcludesEset exe ) as ex on s.AssignedElement = ex.UID_Excluding where s.IsUpcommingContent24 = 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 @ExcludeCandidate26 set XIsInEffectAfter = 0 from @ExcludeCandidate l  join ( select exe.UID_ESetExcluded as UID_Excluded, exe.UID_ESet as UID_Excluding from ESetExcludesEset27 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.Element29 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, @CountDeltaQantity31 = @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 print33 '#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 RAISERROR36 ('', 18, 1) WITH NOWAIT END CATCH endLabel: end 37