Back to OIM Explorer

dbo.RMS_ZPersonHasESet

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 5.186 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.QER_FTPersonInheritFromOrg_L source text reference
  • 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

Referenced By

  • No direct source references extracted.

Complete Source

SQL230 lines
1CREATE PROCEDURE RMS_ZPersonHasESet(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @Sourcedata QBM_YDataForDelta,7  @CountDeltaQantity int,8  @CountDeltaOrigin int9  DECLARE @GenProcID varchar(38)10  DECLARE @Xdate datetime = getutcdate()11  DECLARE @XUser nvarchar(64) = object_name(@@procid)12  DECLARE @MyName nvarchar(64) = object_name(@@procid)13  DECLARE @UID_BaseTreeAssignToUse varchar(38) = 'RMS-AsgnBT-ESet'14  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent15  DECLARE @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',16  0)17  DECLARE @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',18  1)19  BEGIN TRY20    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,21    UID_Parameter,22    UID_SubParameter,23    GenProcID)24    SELECT25      UID_DialogDBQueue,26      UID_Parameter,27      UID_SubParameter,28      GenProcID29    FROM QBMDBQueueCurrent cu30      WITH(readpast)31    WHERE32      cu.SlotNumber = @SlotNumber33    IF @@rowcount = 034    BEGIN35      GOTO EndLabel36    END37    IF dbo.QBM_FGITableCountAll('Eset') = 038    BEGIN39      GOTO endLabel40    END41    INSERT INTO @SourceData(IsUpcommingContent,42    XOriginAfter,43    Element,44    AssignedElement,45    XOriginBefore,46    XIsInEffectBefore,47    XIsInEffectAfter)48    SELECT49      0,50      0,51      uid_person,52      UID_ESet,53      XOrigin,54      phe.XIsInEffect,55      056    FROM PersonHasESet phe57    JOIN @DBQueueCurrent x58      ON uid_person = x.uid_parameter59    INSERT INTO @SourceData(IsUpcommingContent,60    XOriginBefore,61    XOriginAfter,62    Element,63    AssignedElement,64    XIsInEffectBefore,65    XIsInEffectAfter)66    SELECT67      1,68      0,69      phe.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv,70      phe.UID_Person,71      e.UID_ESet,72      0,73      e.IsInActive ^ 174    FROM PersonHasESet phe75    JOIN @DBQueueCurrent x76      ON uid_person= x.uid_parameter77    JOIN ESet e78      ON phe.UID_ESet = e.UID_ESet79    INSERT INTO @SourceData(IsUpcommingContent,80    XOriginBefore,81    Element,82    AssignedElement,83    XOriginAfter,84    XIsInEffectBefore,85    XIsInEffectAfter)86    SELECT87      1,88      0,89      x.uid_person,90      x.UID_ESet,91      @QBM_BitPatternXOrigin_Inherit,92      0,93      x.IsInActive ^ 194    FROM(95    SELECT96      pfo.uid_person,97      e.UID_Eset AS uid_ESet,98      e.IsInActive99    FROM dbo.QER_FTPersonInheritFromOrg_L(@DBQueueCurrent, @UID_BaseTreeAssignToUse) pfo100    JOIN BaseTreeHasESet oha101      ON pfo.uid_org = oha.uid_org AND oha.XOrigin > 0 AND oha.XIsInEffect = 1102    JOIN ESet e103      ON oha.UID_ESet = e.UID_ESet) AS x104    INSERT INTO @SourceData(IsUpcommingContent,105    XOriginBefore,106    Element,107    AssignedElement,108    XOriginAfter,109    XIsInEffectBefore,110    XIsInEffectAfter)111    SELECT112      1,113      0,114      x.uid_person,115      x.UID_ESet,116      @QBM_BitPatternXOrigin_Inherit,117      0,118      x.IsInActive ^ 1119    FROM(120    SELECT121      s.Element AS uid_Person,122      e.UID_ESet AS UID_Eset,123      e.IsInActive124    FROM @SourceData s125    JOIN ESetCollection ec126      ON s.AssignedElement = ec.uid_ESet AND ec.uid_ESet <> ec.UID_EsetChild AND s.XOriginAfter > 0 AND s.IsUpcommingContent = 1127    JOIN ESet e128      ON ec.UID_ESetChild = e.UID_ESet129    JOIN Person p130      ON p.UID_Person = s.Element AND p.IsNoInherite = 0131    WHERE132      s.IsUpcommingContent = 1) AS x133    IF dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\ESetExclusion') > ' '134    BEGIN135      IF EXISTS(136        SELECT TOP 1 1137        FROM ESetExcludesEset ex138        WITH(nolock)139      JOIN @Sourcedata s140        ON ex.UID_ESetExcluded = s.AssignedElement141      WHERE142        s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1)143      BEGIN144        DECLARE @ExcludeCandidate QER_YExcludeCandidate145        INSERT INTO @ExcludeCandidate(Element,146        AssignedElement,147        XIsInEffectAfter)148        SELECT149          s.Element,150          s.AssignedElement,151          1152        FROM @Sourcedata s153        JOIN(154        SELECT exe.UID_ESetExcluded AS UID_Excluded155        FROM ESetExcludesEset exe) AS ex156          ON s.AssignedElement = ex.UID_Excluded157        WHERE158          s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1159        UNION all160        SELECT161          s.Element,162          s.AssignedElement,163          1164        FROM @Sourcedata s165        JOIN(166        SELECT exe.UID_ESet AS UID_Excluding167        FROM ESetExcludesEset exe) AS ex168          ON s.AssignedElement = ex.UID_Excluding169        WHERE170          s.IsUpcommingContent = 1 AND s.XOriginAfter > 0 AND s.XIsInEffectAfter = 1171        UPDATE @ExcludeCandidate172        SET XIsInEffectAfter = 0173        FROM @ExcludeCandidate l174        JOIN(175        SELECT176          exe.UID_ESetExcluded AS UID_Excluded,177          exe.UID_ESet AS UID_Excluding178        FROM ESetExcludesEset exe) AS ex179          ON l.AssignedElement = ex.UID_Excluded180        JOIN(181        SELECT sh.*182        FROM @ExcludeCandidate sh) AS h183          ON l.Element = h.Element AND h.AssignedElement = ex.UID_Excluding184        IF @@ROWCOUNT > 0185        BEGIN186          UPDATE @Sourcedata187          SET XIsInEffectAfter = 0188          FROM @Sourcedata l189          JOIN @ExcludeCandidate k190            ON l.Element = k.Element AND l.AssignedElement = k.AssignedElement AND k.XIsInEffectAfter = 0191          WHERE192            l.IsUpcommingContent = 1 AND l.XOriginAfter > 0 AND l.XIsInEffectAfter = 1193        END194      END195    END196    EXEC QBM_PDBQueueCalculateDelta @SourceData,197      @DeltaQuantity = 0,198      @DeltaDelete = 0,199      @DeltaInsert = 1,200      @DeltaOrigin = 1,201      @CountDeltaQantity = @CountDeltaQantity OUTPUT,202      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,203      @UseIsInEffect = 1,204      @SlotNumber = @SlotNumber,205      @DBQueueCurrentExtern = @DBQueueCurrent206    IF @CountDeltaOrigin > 0207    BEGIN208      EXEC QBM_PMNTableOriginUpdate 'personhasESet',209      'uid_Person',210      'uid_ESet'211    END212    IF @CountDeltaQantity > 0213    BEGIN214      EXEC QBM_PMNTableInsert 'personhasESet',215      'uid_Person',216      'uid_ESet',217        @TargetIsView = 0,218        @FKTableNameElement = 'Person',219        @FKColumnNameElement = 'UID_Person'220    END221  END TRY222  BEGIN CATCH223    EXEC QBM_PSessionErrorAdd DEFAULT224    RAISERROR('',225    18,226    1)227      WITH NOWAIT228  END CATCH229  endLabel:230END
Open raw exported source
SQL ยท Raw35 lines
1       create   procedure RMS_ZPersonHasESet (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int , @CountDeltaOrigin2 int declare @GenProcID varchar(38) declare @Xdate datetime = getutcdate() declare @XUser nvarchar(64) = object_name(@@procid) declare @MyName nvarchar3(64) = object_name(@@procid) declare @UID_BaseTreeAssignToUse varchar(38) = 'RMS-AsgnBT-ESet' declare @DBQueueCurrent QBM_YDBQueueCurrent declare @QBM_BitPatternXOrigin_Inherit4 int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1)  BEGIN5 TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter6, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end if dbo.QBM_FGITableCountAll7('Eset') = 0 begin goto endLabel end  insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore , XIsInEffectBefore8, XIsInEffectAfter ) select 0, 0 , uid_person, UID_ESet, XOrigin , phe.XIsInEffect, 0 from PersonHasESet phe join @DBQueueCurrent x on uid_person = x.uid_parameter9 insert into @SourceData( IsUpcommingContent, XOriginBefore, XOriginAfter , Element, AssignedElement , XIsInEffectBefore, XIsInEffectAfter ) select 1, 100, phe.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv , phe.UID_Person, e.UID_ESet , 0, e.IsInActive ^ 1 from PersonHasESet phe join @DBQueueCurrent x on11 uid_person= x.uid_parameter join ESet e on phe.UID_ESet = e.UID_ESet insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement12, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0 , x.uid_person, x.UID_ESet, @QBM_BitPatternXOrigin_Inherit ,0 , x.IsInActive ^ 1 from13 ( select pfo.uid_person, e.UID_Eset as uid_ESet , e.IsInActive from  dbo.QER_FTPersonInheritFromOrg_L(@DBQueueCurrent, @UID_BaseTreeAssignToUse) pfo join14 BaseTreeHasESet oha on pfo.uid_org = oha.uid_org and oha.XOrigin > 0 and oha.XIsInEffect = 1 join ESet e on oha.UID_ESet = e.UID_ESet         ) as x  15insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select 1, 016 , x.uid_person, x.UID_ESet, @QBM_BitPatternXOrigin_Inherit ,0 , x.IsInActive ^ 1 from ( select s.Element as uid_Person, e.UID_ESet as UID_Eset, e.IsInActive17 from @SourceData s join ESetCollection ec on s.AssignedElement = ec.uid_ESet and ec.uid_ESet <> ec.UID_EsetChild and s.XOriginAfter > 0 and s.IsUpcommingContent18 = 1 join ESet e on ec.UID_ESetChild = e.UID_ESet join Person p on p.UID_Person = s.Element and p.IsNoInherite = 0 where s.IsUpcommingContent = 1 ) as 19x if dbo.QBM_FGIConfigparmValue('QER\Structures\Inherite\ESetExclusion') > ' '  begin if exists (select top 1 1 from ESetExcludesEset ex with (nolock) 20join @Sourcedata s on ex.UID_ESetExcluded  = s.AssignedElement where s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 ) begin21 declare @ExcludeCandidate QER_YExcludeCandidate insert into @ExcludeCandidate(Element, AssignedElement, XIsInEffectAfter) select s.Element, s.AssignedElement22, 1 from @Sourcedata s join ( select exe.UID_ESetExcluded as UID_Excluded from ESetExcludesEset exe ) as ex on s.AssignedElement = ex.UID_Excluded where23 s.IsUpcommingContent = 1 and s.XOriginAfter > 0 and s.XIsInEffectAfter = 1 union all select s.Element, s.AssignedElement, 1 from @Sourcedata s join ( 24select exe.UID_ESet as UID_Excluding from ESetExcludesEset exe ) as ex on s.AssignedElement = ex.UID_Excluding where s.IsUpcommingContent = 1 and s.XOriginAfter25 > 0 and s.XIsInEffectAfter = 1 update @ExcludeCandidate set XIsInEffectAfter = 0 from @ExcludeCandidate l  join ( select exe.UID_ESetExcluded as UID_Excluded26, exe.UID_ESet as UID_Excluding from ESetExcludesEset exe ) as ex on l.AssignedElement = ex.UID_Excluded join (select sh.* from @ExcludeCandidate sh  )27 as h on l.Element = h.Element and h.AssignedElement = ex.UID_Excluding if @@ROWCOUNT > 0 begin update @Sourcedata set XIsInEffectAfter = 0 from @Sourcedata28 l join @ExcludeCandidate k on l.Element = k.Element and l.AssignedElement = k.AssignedElement and k.XIsInEffectAfter = 0 where l.IsUpcommingContent = 291 and l.XOriginAfter > 0 and l.XIsInEffectAfter = 1 end end  end   exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert30 = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber31 = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent if @CountDeltaOrigin > 0 begin exec QBM_PMNTableOriginUpdate 'personhasESet', 'uid_Person', 'uid_ESet'32 end if @CountDeltaQantity > 0 begin exec QBM_PMNTableInsert 'personhasESet', 'uid_Person', 'uid_ESet', @TargetIsView = 0 , @FKTableNameElement = 'Person'33 , @FKColumnNameElement = 'UID_Person' end END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: 34end 35