Back to OIM Explorer

dbo.RMS_ZESetHasEntitlement

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.624 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_PDBQueueCalculateDelta source text reference
  • references source dbo.QBM_PMNTableInsert source text reference
  • references source dbo.QBM_PMNTableInsertOwnPK source text reference
  • references source dbo.QBM_PMNTableOriginUpdate source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL266 lines
1CREATE PROCEDURE RMS_ZESetHasEntitlement(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @Sourcedata QBM_YDataForDelta,7  @CountDeltaQantity int,8  @CountDeltaOrigin int9  DECLARE @GenProcID varchar(38)10  DECLARE @DebugSwitch int = 011  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    XIsInEffectAfter)41    SELECT42      0,43      0,44      ehe.UID_ESet,45      ehe.Entitlement,46      ehe.XOrigin,47      ehe.XIsInEffect,48      049    FROM ESetHasEntitlement ehe50    JOIN @DBQueueCurrent x51      ON ehe.uid_ESet = x.uid_parameter52    WHERE53      ehe.Entitlement LIKE '<Key><T>ESet</T>%'54    INSERT INTO @SourceData(IsUpcommingContent,55    XOriginBefore,56    XOriginAfter,57    Element,58    AssignedElement,59    XIsInEffectBefore,60    XIsInEffectAfter)61    SELECT62      1,63      0,64      y.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv,65      y.UID_ESet,66      y.Entitlement,67      0,68      (es.IsInActive ^ 1) *(pa.isInactive ^ 1)69    FROM ESetHasEntitlement y70    JOIN @DBQueueCurrent x71      ON y.UID_ESet= x.uid_parameter72    JOIN ESet es73      ON y.UID_ESet = es.uid_ESet74    JOIN ESet pa75      ON y.Entitlement = pa.XObjectKey76    WHERE77      y.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 078    IF @DebugSwitch > 079    BEGIN80      print 'BasisInfo drin'81      SELECT82        IsUpcommingContent,83        XOriginBefore,84        XOriginAfter,85        XIsInEffectBefore,86        XIsInEffectAfter,87        Element,88        AssignedElement89      FROM @SourceData s90    END91    INSERT INTO @SourceData(IsUpcommingContent,92    XOriginBefore,93    Element,94    AssignedElement,95    XOriginAfter,96    XIsInEffectBefore,97    XIsInEffectAfter)98    SELECT99      DISTINCT 1,100      0,101      co.UID_ESet,102      pa.XObjectKey,103      @QBM_BitPatternXOrigin_Inherit,104      0,105      (es.isinactive ^1) *(pa.isinactive ^ 1)106    FROM @DBQueueCurrent x107    JOIN ESetCollection co108      ON x.uid_parameter = co.uid_ESet AND co.uid_ESet <> co.UID_EsetChild109    JOIN ESet es110      ON co.UID_ESet = es.uid_ESet111    JOIN ESet pa112      ON co.UID_EsetChild = pa.uid_ESet113    WHERE114      NOT EXISTS(115    SELECT TOP 1 1116    FROM @SourceData s117    WHERE118      s.IsUpcommingContent = 1 AND s.Element = co.UID_ESet AND s.AssignedElement = pa.XObjectKey AND s.XOriginAfter > 0)119    IF @DebugSwitch > 0120    BEGIN121      print 'Nach Collection befüllen'122      SELECT123        IsUpcommingContent,124        XOriginBefore,125        XOriginAfter,126        XIsInEffectBefore,127        XIsInEffectAfter,128        Element,129        AssignedElement130      FROM @SourceData s131    END132    IF @DebugSwitch > 0133    BEGIN134      print 'die Exclude def'135      SELECT136        l.uid_ESet AS UID_Excluded,137        h.uid_Eset AS UID_Excluding,138        h.XObjectKey AS ObjectKeyExcluding,139        l.XObjectKey AS ObjectKeyExcluded140      FROM ESetExcludesEset exe141      JOIN Eset h142        ON exe.uid_Eset = h.UID_ESet143      JOIN ESet l144        ON exe.UID_ESetExcluded = l.UID_ESet145    END146    IF dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\ESetExclusion') > ' '147    BEGIN148      IF EXISTS(149        SELECT TOP 1 1150        FROM ESetExcludesESet)151      BEGIN152        INSERT INTO @SourceData(IsUpcommingContent,153        XOriginBefore,154        Element,155        AssignedElement,156        XOriginAfter,157        XIsInEffectBefore,158        XIsInEffectAfter)159        SELECT160          1,161          0,162          e.UID_ESet,163          e.XObjectKey,164          0,165          0,166          0167        FROM @DBQueueCurrent x168        JOIN ESet e169          ON x.uid_parameter = e.uid_ESet170        UPDATE @Sourcedata171        SET XIsInEffectAfter = 0172        FROM @Sourcedata l173        JOIN(174        SELECT sh.*175        FROM @Sourcedata sh176        WHERE177          sh.IsUpcommingContent = 1 AND sh.XOriginAfter > 0 AND sh.XIsInEffectAfter = 1) AS vh178          ON l.Element = vh.Element179        JOIN(180        SELECT181          l.uid_ESet AS UID_Excluded,182          h.uid_Eset AS UID_Excluding,183          h.XObjectKey AS ObjectKeyExcluding,184          l.XObjectKey AS ObjectKeyExcluded185        FROM ESetExcludesEset exe186        JOIN Eset h187          ON exe.uid_Eset = h.UID_ESet188        JOIN ESet l189          ON exe.UID_ESetExcluded = l.UID_ESet) AS ex190          ON l.AssignedElement = ex.ObjectKeyExcluded AND vh.AssignedElement = ex.ObjectKeyExcluding191        WHERE192          l.IsUpcommingContent = 1 AND l.XOriginAfter > 0 AND l.XIsInEffectAfter = 1193        UPDATE @Sourcedata194        SET XIsInEffectAfter = 0195        FROM @Sourcedata l196        JOIN(197        SELECT198          l.uid_ESet AS UID_Excluded,199          h.uid_Eset AS UID_Excluding,200          h.XObjectKey AS ObjectKeyExcluding,201          l.XObjectKey AS ObjectKeyExcluded202        FROM ESetExcludesEset exe203        JOIN Eset h204          ON exe.uid_Eset = h.UID_ESet205        JOIN ESet l206          ON exe.UID_ESetExcluded = l.UID_ESet) AS ex207          ON l.Element = ex.UID_Excluding AND l.AssignedElement = ex.ObjectKeyExcluded208        WHERE209          l.IsUpcommingContent = 1 AND l.XOriginAfter > 0 AND l.XIsInEffectAfter = 1210      END211    END212    IF @DebugSwitch > 0213    BEGIN214      print 'nach Exclude'215      SELECT216        IsUpcommingContent,217        XOriginBefore,218        XOriginAfter,219        XIsInEffectBefore,220        XIsInEffectAfter,221        Element,222        AssignedElement223      FROM @SourceData s224    END225    berechnen:226    EXEC QBM_PDBQueueCalculateDelta @SourceData,227      @DeltaQuantity = 0,228      @DeltaDelete = 0,229      @DeltaInsert = 1,230      @DeltaOrigin = 1,231      @CountDeltaQantity = @CountDeltaQantity OUTPUT,232      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,233      @UseIsInEffect = 1,234      @SlotNumber = @SlotNumber,235      @DBQueueCurrentExtern = @DBQueueCurrent236    IF @CountDeltaOrigin > 0237    BEGIN238      EXEC QBM_PMNTableOriginUpdate 'ESetHasEntitlement',239      'uid_ESet',240      'Entitlement'241    END242    IF @CountDeltaQantity > 0243    BEGIN244      IF @DebugSwitch > 0245      BEGIN246        print 'RMS_ZESetHasEntitlement einzufügende Sätze'247        SELECT *248        FROM #QBMDeltaInsert249      END250      EXEC QBM_PMNTableInsertOwnPK 'ESetHasEntitlement',251      'uid_ESet',252      'Entitlement',253        @FKTableNameElement = 'ESet',254        @FKColumnNameElement = 'uid_ESet'255    END256  END TRY257  BEGIN CATCH258    EXEC QBM_PSessionErrorAdd DEFAULT259    RAISERROR('',260    18,261    1)262      WITH NOWAIT263  END CATCH264  endLabel:265  RETURN266END
Open raw exported source
SQL · Raw37 lines
1        create   procedure RMS_ZESetHasEntitlement (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int , @CountDeltaOrigin2 int declare @GenProcID varchar(38) declare @DebugSwitch int = 0 declare @DBQueueCurrent QBM_YDBQueueCurrent declare @QBM_BitPatternXOrigin_Inherit int3 = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1)  BEGIN TRY4 insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, 5GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end               insert into6 @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore , XIsInEffectBefore, XIsInEffectAfter ) select 0, 0 , ehe.UID_ESet7, ehe.Entitlement, ehe.XOrigin , ehe.XIsInEffect, 0 from ESetHasEntitlement ehe join @DBQueueCurrent x on ehe.uid_ESet = x.uid_parameter  where ehe.Entitlement8 like '<Key><T>ESet</T>%'  insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement , XIsInEffectBefore, XIsInEffectAfter9 ) select 1, 0, y.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv , y.UID_ESet, y.Entitlement , 0, (es.IsInActive ^ 1) * (pa.isInactive ^ 1) from ESetHasEntitlement10 y join @DBQueueCurrent x on y.UID_ESet= x.uid_parameter join ESet es on y.UID_ESet = es.uid_ESet  join ESet pa on y.Entitlement = pa.XObjectKey  where11 y.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 0 if @DebugSwitch > 0 begin print 'BasisInfo drin' select IsUpcommingContent , XOriginBefore , XOriginAfter12 , XIsInEffectBefore , XIsInEffectAfter, Element, AssignedElement from @SourceData s end insert into @SourceData( IsUpcommingContent, XOriginBefore , Element13, AssignedElement, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select distinct 1, 0 , co.UID_ESet, pa.XObjectKey, @QBM_BitPatternXOrigin_Inherit14 , 0, (es.isinactive ^1) * (pa.isinactive ^ 1) from @DBQueueCurrent x join ESetCollection co on x.uid_parameter = co.uid_ESet and co.uid_ESet <> co.UID_EsetChild15 join ESet es on co.UID_ESet = es.uid_ESet  join ESet pa on co.UID_EsetChild = pa.uid_ESet   where not exists (select top 1 1 from @SourceData s where 16s.IsUpcommingContent = 1 and s.Element = co.UID_ESet and s.AssignedElement = pa.XObjectKey and s.XOriginAfter > 0 ) if @DebugSwitch > 0 begin print 'Nach Collection befüllen'17 select IsUpcommingContent , XOriginBefore , XOriginAfter , XIsInEffectBefore , XIsInEffectAfter, Element, AssignedElement from @SourceData s end  if @DebugSwitch18 > 0 begin   print 'die Exclude def' select l.uid_ESet as UID_Excluded, h.uid_Eset as UID_Excluding, h.XObjectKey as ObjectKeyExcluding, l.XObjectKey as19 ObjectKeyExcluded from ESetExcludesEset exe join Eset h on exe.uid_Eset = h.UID_ESet join ESet l on exe.UID_ESetExcluded = l.UID_ESet end if dbo.QBM_FGIConfigparmValue20('QER\Structures\Inherite\ESetExclusion') > ' '  begin if exists (select top 1 1 from ESetExcludesESet) begin     insert into @SourceData( IsUpcommingContent21, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0 , e.UID_ESet, e.XObjectKey, 0 , 0, 0 from 22@DBQueueCurrent x join ESet e on x.uid_parameter = e.uid_ESet  update @Sourcedata set XIsInEffectAfter = 0 from @Sourcedata l  join (select sh.* from @Sourcedata23 sh where sh.IsUpcommingContent = 1 and sh.XOriginAfter > 0  and sh.XIsInEffectAfter = 1 ) as vh on l.Element = vh.Element join ( select l.uid_ESet as 24UID_Excluded, h.uid_Eset as UID_Excluding, h.XObjectKey as ObjectKeyExcluding, l.XObjectKey as ObjectKeyExcluded from ESetExcludesEset exe join Eset h 25on exe.uid_Eset = h.UID_ESet join ESet l on exe.UID_ESetExcluded = l.UID_ESet ) as ex on l.AssignedElement = ex.ObjectKeyExcluded and vh.AssignedElement26 = ex.ObjectKeyExcluding where l.IsUpcommingContent = 1 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1  update @Sourcedata set XIsInEffectAfter = 0 27from @Sourcedata l  join ( select l.uid_ESet as UID_Excluded, h.uid_Eset as UID_Excluding, h.XObjectKey as ObjectKeyExcluding, l.XObjectKey as ObjectKeyExcluded28 from ESetExcludesEset exe join Eset h on exe.uid_Eset = h.UID_ESet join ESet l on exe.UID_ESetExcluded = l.UID_ESet ) as ex on l.Element = ex.UID_Excluding29 and l.AssignedElement = ex.ObjectKeyExcluded where l.IsUpcommingContent = 1 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1 end  end  if @DebugSwitch30 > 0 begin print 'nach Exclude' select IsUpcommingContent , XOriginBefore , XOriginAfter , XIsInEffectBefore , XIsInEffectAfter, Element, AssignedElement31 from @SourceData s end  berechnen: exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert = 1, @DeltaOrigin =32 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern33 = @DBQueueCurrent if @CountDeltaOrigin > 0 begin exec QBM_PMNTableOriginUpdate 'ESetHasEntitlement', 'uid_ESet', 'Entitlement' end if @CountDeltaQantity34 > 0 begin if @DebugSwitch > 0 begin print 'RMS_ZESetHasEntitlement einzufügende Sätze' select * from #QBMDeltaInsert end exec QBM_PMNTableInsertOwnPK 35'ESetHasEntitlement', 'uid_ESet', 'Entitlement' , @FKTableNameElement = 'ESet' , @FKColumnNameElement = 'uid_ESet'  end  END TRY BEGIN CATCH exec QBM_PSessionErrorAdd36 default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: return end 37