Back to OIM Explorer

dbo.QER_ZPersonHasQERResource

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 4.702 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_FGIModuleExists source text reference
  • references source dbo.QBM_PDBQueueCalculateDelta source text reference
  • references source dbo.QBM_PJournal 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_FTQERResourcePredecessor source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL191 lines
1CREATE PROCEDURE QER_ZPersonHasQERResource(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 @DebugSwitch int = 013  DECLARE @DebugLevel varchar(1) = 'W'14  DECLARE @CountRows int15  DECLARE @DebugMessage nvarchar(1000)16  DECLARE @UID_BaseTreeAssignToUse varchar(38) = 'QER-AsgnBT-QERResource'17  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent18  DECLARE @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',19  0)20  DECLARE @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|',21  1)22  BEGIN TRY23    INSERT INTO @DBQueueCurrent(UID_DialogDBQueue,24    UID_Parameter,25    UID_SubParameter,26    GenProcID)27    SELECT28      UID_DialogDBQueue,29      UID_Parameter,30      UID_SubParameter,31      GenProcID32    FROM QBMDBQueueCurrent cu33      WITH(readpast)34    WHERE35      cu.SlotNumber = @SlotNumber36    IF @@rowcount = 037    BEGIN38      GOTO EndLabel39    END40    INSERT INTO @SourceData(IsUpcommingContent,41    XOriginAfter,42    Element,43    AssignedElement,44    XOriginBefore,45    XIsInEffectBefore,46    XIsInEffectAfter)47    SELECT48      0,49      0,50      phr.UID_Person,51      phr.UID_QERResource AS UID_QERResource,52      phr.XOrigin,53      phr.XIsInEffect,54      055    FROM PersonHasQERResource phr56    JOIN @DBQueueCurrent x57      ON phr.UID_Person = x.uid_parameter58    SELECT @CountRows = @@ROWCOUNT59    IF @DebugSwitch > 060    BEGIN61      SELECT @DebugMessage = 'vorher ' + STR(@CountRows)62      EXEC QBM_PJournal @DebugMessage,63        @@PROCID,64      'D',65        @DebugLevel66    END67    INSERT INTO @SourceData(IsUpcommingContent,68    XOriginBefore,69    XOriginAfter,70    Element,71    AssignedElement,72    XIsInEffectBefore,73    XIsInEffectAfter)74    SELECT75      1,76      0,77    CASE78      WHEN pd.UID_QERResource = pd.UID_QERResourcePredecessor THEN79    phr.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv80    ELSE @QBM_BitPatternXOrigin_Inherit81    END,82    phr.UID_person,83    pd.UID_QERResourcePredecessor AS UID_QERResource,84    0,85    CASE86      WHEN pd.UID_QERResource = pd.UID_QERResourcePredecessor THEN87    188    ELSE(r.IsNoInheriteToSecurityIncident & p.IsSecurityIncident) ^189    END90    FROM PersonHasQERResource phr91    JOIN @DBQueueCurrent x92      ON UID_person = x.uid_parameter93    JOIN person p94      ON phr.uid_person = p.uid_person95    JOIN QERResource r96      ON phr.uid_QERResource = r.uid_QERResource CROSS apply dbo.QER_FTQERResourcePredecessor(r.UID_QERResource) pd97    WHERE98      phr.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 099    SELECT @CountRows = @@ROWCOUNT100    IF @DebugSwitch > 0101    BEGIN102      SELECT @DebugMessage = 'direkt ' + STR(@CountRows)103      EXEC QBM_PJournal @DebugMessage,104        @@PROCID,105      'D',106        @DebugLevel107    END108    INSERT INTO @SourceData(IsUpcommingContent,109    XOriginBefore,110    Element,111    AssignedElement,112    XOriginAfter,113    XIsInEffectBefore,114    XIsInEffectAfter)115    SELECT116      1,117      0,118      x.UID_person,119      pd.UID_QERResourcePredecessor AS UID_QERResource,120      @QBM_BitPatternXOrigin_Inherit AS XOrigin,121      0,122      (r.IsNoInheriteToSecurityIncident & p.IsSecurityIncident) ^1123    FROM(124    SELECT125      pha.UID_person,126      r.UID_QERResource127    FROM @DBQueueCurrent x128    JOIN PersonHasESet pha129      ON pha.UID_person = x.uid_parameter AND pha.XOrigin > 0 AND pha.XIsInEffect = 1130    JOIN person px131      ON pha.uid_person = px.uid_person AND px.isNoInherite = 0132    JOIN ESetHasEntitlement aha133      ON pha.UID_ESet = aha.UID_ESet AND aha.XOrigin > 0 AND aha.XIsInEffect = 1 AND dbo.QBM_FGIModuleExists('RMS') = 1134    JOIN QERResource r135      ON aha.Entitlement = r.XObjectKey136    UNION all137    SELECT138      pfo.UID_person,139      oha.UID_QERResource140    FROM dbo.QER_FTPersonInheritFromOrg_L(@DBQueueCurrent, @UID_BaseTreeAssignToUse) pfo141    JOIN BaseTreeHasQERResource oha142      ON oha.uid_org = pfo.uid_org AND oha.XOrigin > 0 AND oha.XIsInEffect = 1) AS x143    JOIN person p144      ON x.uid_person = p.uid_person145    JOIN QERResource r146      ON x.uid_QERResource = r.uid_QERResource CROSS apply dbo.QER_FTQERResourcePredecessor(r.UID_QERResource) pd147    SELECT @CountRows = @@ROWCOUNT148    IF @DebugSwitch > 0149    BEGIN150      SELECT @DebugMessage = 'indirekt ' + STR(@CountRows)151      EXEC QBM_PJournal @DebugMessage,152        @@PROCID,153      'D',154        @DebugLevel155    END156    EXEC QBM_PDBQueueCalculateDelta @SourceData,157      @DeltaQuantity = 0,158      @DeltaDelete = 0,159      @DeltaInsert = 1,160      @DeltaOrigin = 1,161      @CountDeltaQantity = @CountDeltaQantity OUTPUT,162      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,163      @UseIsInEffect = 1,164      @SlotNumber = @SlotNumber,165      @DBQueueCurrentExtern = @DBQueueCurrent166    IF @CountDeltaOrigin > 0167    BEGIN168      EXEC QBM_PMNTableOriginUpdate 'PersonHasQERResource',169      'uid_Person',170      'uid_QERResource'171    END172    IF @CountDeltaQantity > 0173    BEGIN174      EXEC QBM_PMNTableInsert 'PersonHasQERResource',175      'uid_Person',176      'uid_QERResource',177        @TargetIsView = 0,178        @FKTableNameElement = 'Person',179        @FKColumnNameElement = 'UID_Person'180    END181  END TRY182  BEGIN CATCH183    EXEC QBM_PSessionErrorAdd DEFAULT184    RAISERROR('',185    18,186    1)187      WITH NOWAIT188  END CATCH189  endLabel:190  RETURN191END
Open raw exported source
SQL ยท Raw31 lines
1       create   procedure QER_ZPersonHasQERResource (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int , 2@CountDeltaOrigin int declare @GenProcID varchar(38) declare @Xdate datetime = getutcdate() declare @XUser nvarchar(64) = object_name(@@procid) declare3 @DebugSwitch int = 0 declare @DebugLevel varchar(1) = 'W' declare @CountRows int declare @DebugMessage nvarchar(1000) declare @UID_BaseTreeAssignToUse4 varchar(38) = 'QER-AsgnBT-QERResource' declare @DBQueueCurrent QBM_YDBQueueCurrent declare @QBM_BitPatternXOrigin_Inherit int = dbo.QBM_FGIBitPatternXOrigin5('|Inherit|', 0) declare @QBM_BitPatternXOrigin_Inherit_inv int = dbo.QBM_FGIBitPatternXOrigin('|Inherit|', 1)  BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue6, UID_Parameter, UID_SubParameter, GenProcID) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast7) where cu.SlotNumber = @SlotNumber if @@rowcount = 0 begin goto EndLabel end  insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement8, XOriginBefore , XIsInEffectBefore, XIsInEffectAfter ) select 0, 0 , phr.UID_Person, phr.UID_QERResource as UID_QERResource, phr.XOrigin , phr.XIsInEffect9, 0 from PersonHasQERResource phr join @DBQueueCurrent x on phr.UID_Person = x.uid_parameter  select @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin 10select @DebugMessage = 'vorher ' + STR(@CountRows) exec QBM_PJournal @DebugMessage, @@PROCID, 'D', @DebugLevel end insert into @SourceData( IsUpcommingContent11, XOriginBefore, XOriginAfter , Element, AssignedElement , XIsInEffectBefore, XIsInEffectAfter ) select 1, 0, case when pd.UID_QERResource = pd.UID_QERResourcePredecessor12 then phr.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv else @QBM_BitPatternXOrigin_Inherit end , phr.UID_person, pd.UID_QERResourcePredecessor as UID_QERResource13 , 0,  case when pd.UID_QERResource = pd.UID_QERResourcePredecessor then 1 else (r.IsNoInheriteToSecurityIncident & p.IsSecurityIncident) ^1 end from PersonHasQERResource14 phr join @DBQueueCurrent x on UID_person = x.uid_parameter  join person p on phr.uid_person = p.uid_person  join QERResource r on phr.uid_QERResource 15= r.uid_QERResource cross apply dbo.QER_FTQERResourcePredecessor(r.UID_QERResource) pd  where phr.XOrigin & @QBM_BitPatternXOrigin_Inherit_inv > 0 select16 @CountRows = @@ROWCOUNT if @DebugSwitch > 0 begin select @DebugMessage = 'direkt ' + STR(@CountRows) exec QBM_PJournal @DebugMessage, @@PROCID, 'D', @DebugLevel17 end insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter , XIsInEffectBefore, XIsInEffectAfter ) select18 1, 0 , x.UID_person,pd.UID_QERResourcePredecessor as UID_QERResource , @QBM_BitPatternXOrigin_Inherit as XOrigin , 0, (r.IsNoInheriteToSecurityIncident19 & p.IsSecurityIncident) ^1 from ( select pha.UID_person, r.UID_QERResource from @DBQueueCurrent x join PersonHasESet pha on pha.UID_person = x.uid_parameter20 and pha.XOrigin > 0 and pha.XIsInEffect = 1  join person px on pha.uid_person = px.uid_person and px.isNoInherite = 0  join ESetHasEntitlement aha on 21pha.UID_ESet = aha.UID_ESet and aha.XOrigin > 0 and aha.XIsInEffect = 1 and dbo.QBM_FGIModuleExists('RMS') = 1 Join QERResource r on aha.Entitlement = 22r.XObjectKey union all select pfo.UID_person, oha.UID_QERResource  from dbo.QER_FTPersonInheritFromOrg_L(@DBQueueCurrent, @UID_BaseTreeAssignToUse) pfo23 join BaseTreeHasQERResource oha on oha.uid_org = pfo.uid_org and oha.XOrigin > 0 and oha.XIsInEffect = 1 ) as x join person p on x.uid_person = p.uid_person24 join QERResource r on x.uid_QERResource = r.uid_QERResource cross apply dbo.QER_FTQERResourcePredecessor(r.UID_QERResource) pd select @CountRows = @@ROWCOUNT25 if @DebugSwitch > 0 begin select @DebugMessage = 'indirekt ' + STR(@CountRows) exec QBM_PJournal @DebugMessage, @@PROCID, 'D', @DebugLevel end  exec QBM_PDBQueueCalculateDelta26 @SourceData, @DeltaQuantity = 0, @DeltaDelete = 0, @DeltaInsert = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin27 = @CountDeltaOrigin output , @UseIsInEffect = 1 , @SlotNumber = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent if @CountDeltaOrigin > 0 begin exec28 QBM_PMNTableOriginUpdate 'PersonHasQERResource', 'uid_Person', 'uid_QERResource' end if @CountDeltaQantity > 0 begin exec QBM_PMNTableInsert 'PersonHasQERResource'29, 'uid_Person', 'uid_QERResource', @TargetIsView = 0 , @FKTableNameElement = 'Person' , @FKColumnNameElement = 'UID_Person' end END TRY BEGIN CATCH exec30 QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH  endLabel: return end 31