Back to OIM Explorer

dbo.ADS_ZPersonHasObject

Stored ProcedureSQL_STORED_PROCEDURESandbox DB

Stored Procedure.

Source: sandbox-db sys.sql_modules

Source size: 7.622 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_FGIBitPatternXMarkedForDel source text reference
  • references source dbo.QBM_FGIConfigparmValue source text reference
  • references source dbo.QER_FCVXOriginToInheritInfo source text reference
  • references source dbo.QER_FGIBitPatternInheritInfo source text reference
  • references source dbo.QBM_PDBQueueCalculateDelta source text reference
  • references source dbo.QBM_PSessionErrorAdd source text reference
  • references source dbo.QER_PPersonHasObjectPostProc source text reference

References

Referenced By

  • No direct source references extracted.

Complete Source

SQL325 lines
1CREATE PROCEDURE ADS_ZPersonHasObject(2  @SlotNumber int3)4AS5BEGIN6  DECLARE @Sourcedata QBM_YDataForDelta,7  @CountDeltaQantity int,8  @CountDeltaOrigin int9  DECLARE @QER_BitPattern_Inherit_0 int = dbo.QER_FGIBitPatternInheritInfo('|Inherit|',10  0)11  DECLARE @DBQueueCurrent QBM_YDBQueueCurrent12  DECLARE @QBM_BitPatternXMarkedForDel_Delay int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|',13  0)14  DECLARE @QER_BitPatternInherit_Direct int = dbo.QER_FGIBitPatternInheritInfo('|Direct|',15  0)16  DECLARE @QER_BitPatternInherit_Master int = dbo.QER_FGIBitPatternInheritInfo('|Master|',17  0)18  DECLARE @IncludeTSBPersonUsesAccount nvarchar(1024) = dbo.QBM_FGIConfigparmValue('QER\ComplianceCheck\IncludeTSBPersonUsesAccount')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    INSERT INTO @SourceData(IsUpcommingContent,38    XOriginAfter,39    Element,40    AssignedElement,41    XOriginBefore)42    SELECT43      0,44      0,45      pho.UID_Person,46      pho.ObjectKey,47      pho.InheritInfo48    FROM PersonHasObject pho49    JOIN @DBQueueCurrent x50      ON pho.UID_Person = x.uid_parameter51    WHERE52      pho.ObjectKey LIKE '<Key><T>ADSAccount</T>%' OR pho.ObjectKey LIKE '<Key><T>ADSGroup</T>%' OR pho.ObjectKey LIKE '<Key><T>ADSContact</T>%'53    INSERT INTO @SourceData(IsUpcommingContent,54    XOriginBefore,55    Element,56    AssignedElement,57    XOriginAfter)58    SELECT59      1,60      0,61      y.uid_person,62      y.XObjectKey,63      @QER_BitPatternInherit_Direct64    FROM ADSAccount y65    JOIN @DBQueueCurrent x66      ON y.uid_person= x.uid_parameter AND y.UID_Person > ' '67    UNION all68    SELECT69      1,70      0,71      y.uid_person,72      y.XObjectKey,73      @QER_BitPatternInherit_Direct74    FROM ADSContact y75    JOIN @DBQueueCurrent x76      ON y.uid_person= x.uid_parameter AND y.UID_Person > ' '77    IF @IncludeTSBPersonUsesAccount = '1'78    BEGIN79      INSERT INTO @SourceData(IsUpcommingContent,80      XOriginBefore,81      Element,82      AssignedElement,83      XOriginAfter)84      SELECT85        1,86        0,87        y.uid_person,88        y.ObjectKeyAccount,89        @QER_BitPattern_Inherit_090      FROM TSBPersonUsesAccount y91      JOIN @DBQueueCurrent x92        ON y.uid_person= x.uid_parameter93      JOIN ADSAccount a94        ON y.ObjectKeyAccount = a.XObjectKey95      INSERT INTO @SourceData(IsUpcommingContent,96      XOriginBefore,97      Element,98      AssignedElement,99      XOriginAfter)100      SELECT101        1,102        0,103        y.uid_person,104        y.ObjectKeyAccount,105        @QER_BitPattern_Inherit_0106      FROM TSBPersonUsesAccount y107      JOIN @DBQueueCurrent x108        ON y.uid_person= x.uid_parameter109      JOIN ADSContact a110        ON y.ObjectKeyAccount = a.XObjectKey111    END112    INSERT INTO @SourceData(IsUpcommingContent,113    XOriginBefore,114    Element,115    AssignedElement,116    XOriginAfter)117    SELECT118      1,119      0,120      y.uid_person,121      q.XObjectKey,122      dbo.QER_FCVXOriginToInheritInfo(z.XOrigin)123    FROM ADSAccount y124    JOIN @DBQueueCurrent x125      ON y.uid_person = x.uid_parameter AND y.UID_Person > ' '126    JOIN ADSAccountInADSGroup z127      ON y.uid_ADSAccount = z.uid_ADSAccount AND z.XOrigin > 0 AND z.XIsInEffect = 1128    JOIN ADSGroup q129      ON q.uid_ADSGroup = z.uid_ADSGroup130    WHERE131      z.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0132    UNION all133    SELECT134      1,135      0,136      y.uid_person,137      q.XObjectKey,138      dbo.QER_FCVXOriginToInheritInfo(z.XOrigin)139    FROM ADSContact y140    JOIN @DBQueueCurrent x141      ON y.uid_person = x.uid_parameter AND y.UID_Person > ' '142    JOIN ADSContactInADSGroup z143      ON y.UID_ADSContact = z.UID_ADSContact AND z.XOrigin > 0 AND z.XIsInEffect = 1144    JOIN ADSGroup q145      ON q.uid_ADSGroup = z.uid_ADSGroup146    WHERE147      z.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0148    IF @IncludeTSBPersonUsesAccount = '1'149    BEGIN150      INSERT INTO @SourceData(IsUpcommingContent,151      XOriginBefore,152      Element,153      AssignedElement,154      XOriginAfter)155      SELECT156        1,157        0,158        y.uid_person,159        q.XObjectKey,160        @QER_BitPattern_Inherit_0161      FROM TSBPersonUsesAccount y162      JOIN @DBQueueCurrent x163        ON y.uid_person = x.uid_parameter164      JOIN ADSAccount a165        ON y.ObjectKeyAccount = a.XObjectKey166      JOIN ADSAccountInADSGroup z167        ON a.uid_ADSAccount = z.uid_ADSAccount AND z.XOrigin > 0 AND z.XIsInEffect = 1168      JOIN ADSGroup q169        ON q.uid_ADSGroup = z.uid_ADSGroup170      WHERE171        z.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0172      INSERT INTO @SourceData(IsUpcommingContent,173      XOriginBefore,174      Element,175      AssignedElement,176      XOriginAfter)177      SELECT178        1,179        0,180        y.uid_person,181        q.XObjectKey,182        @QER_BitPattern_Inherit_0183      FROM TSBPersonUsesAccount y184      JOIN @DBQueueCurrent x185        ON y.uid_person = x.uid_parameter186      JOIN ADSContact a187        ON y.ObjectKeyAccount = a.XObjectKey188      JOIN ADSContactInADSGroup z189        ON a.uid_ADSContact = z.uid_ADSContact AND z.XOrigin > 0 AND z.XIsInEffect = 1190      JOIN ADSGroup q191        ON q.uid_ADSGroup = z.uid_ADSGroup192      WHERE193        z.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0194    END195    INSERT INTO @SourceData(IsUpcommingContent,196    XOriginBefore,197    Element,198    AssignedElement,199    XOriginAfter)200    SELECT201      1,202      0,203      y.uid_person,204      q.XObjectKey,205      (@QER_BitPatternInherit_Direct) AS XOrigin206    FROM ADSAccount y207    JOIN @DBQueueCurrent x208      ON y.uid_person= x.uid_parameter AND y.UID_Person > ' '209    JOIN ADSGroup q210      ON q.uid_ADSGroup = y.UID_ADSGroupPrimary211    IF @IncludeTSBPersonUsesAccount = '1'212    BEGIN213      INSERT INTO @SourceData(IsUpcommingContent,214      XOriginBefore,215      Element,216      AssignedElement,217      XOriginAfter)218      SELECT219        1,220        0,221        y.uid_person,222        q.XObjectKey,223        @QER_BitPatternInherit_Direct224      FROM TSBPersonUsesAccount y225      JOIN @DBQueueCurrent x226        ON y.uid_person = x.uid_parameter227      JOIN ADSAccount a228        ON y.ObjectKeyAccount = a.XObjectKey229      JOIN ADSGroup q230        ON q.uid_ADSGroup = a.UID_ADSGroupPrimary231    END232    INSERT INTO @SourceData(IsUpcommingContent,233    XOriginBefore,234    Element,235    AssignedElement,236    XOriginAfter)237    SELECT238      1,239      0,240      t.Element,241      t.XObjectKey,242      @QER_BitPattern_Inherit_0243    FROM(244    SELECT245      DISTINCT ho.Element,246      p.XObjectKey247    FROM @SourceData ho248    JOIN ADSGroup g249      ON ho.AssignedElement = g.XObjectKey250    JOIN ADSGroupCollection co251      ON co.UID_ADSGroupChild = g.uid_ADSGroup252    JOIN ADSGroup p253      ON co.UID_ADSGroupParent = p.UID_ADSGroup254    WHERE255      ho.IsUpcommingContent = 1 AND co.UID_ADSGroupChild <> co.UID_ADSGroupParent) AS t256    INSERT INTO @SourceData(IsUpcommingContent,257    XOriginBefore,258    Element,259    AssignedElement,260    XOriginAfter)261    SELECT262      1,263      0,264      m.uid_person,265      pho.objectkey,266      @QER_BitPatternInherit_Master267    FROM @DBQueueCurrent x268    JOIN person m269      ON m.UID_Person = x.uid_parameter270    JOIN person s271      ON s.UID_PersonMasterIdentity = m.uid_Person272    JOIN PersonHasObject pho273      ON pho.uid_person = s.uid_person274    WHERE275      pho.ObjectKey LIKE '<Key><T>ADSAccount</T>%' OR pho.ObjectKey LIKE '<Key><T>ADSGroup</T>%' OR pho.ObjectKey LIKE '<Key><T>ADSContact</T>%'276    IF @IncludeTSBPersonUsesAccount = '1'277    BEGIN278      INSERT INTO @SourceData(IsUpcommingContent,279      XOriginBefore,280      Element,281      AssignedElement,282      XOriginAfter)283      SELECT284        1,285        0,286        m.uid_person,287        pho.ObjectKey,288        @QER_BitPatternInherit_Master289      FROM TSBPersonUsesAccount y290      JOIN @DBQueueCurrent x291        ON y.uid_person = x.uid_parameter292      JOIN person m293        ON m.UID_Person = y.uid_person294      JOIN person s295        ON s.UID_PersonMasterIdentity = m.uid_Person296      JOIN PersonHasObject pho297        ON pho.uid_person = s.uid_person298      WHERE299        pho.ObjectKey LIKE '<Key><T>ADSAccount</T>%' OR pho.ObjectKey LIKE '<Key><T>ADSGroup</T>%' OR pho.ObjectKey LIKE '<Key><T>ADSContact</T>%'300    END301    berechnen:302    EXEC QBM_PDBQueueCalculateDelta @SourceData,303      @DeltaQuantity = 1,304      @DeltaDelete = 1,305      @DeltaInsert = 1,306      @DeltaOrigin = 1,307      @CountDeltaQantity = @CountDeltaQantity OUTPUT,308      @CountDeltaOrigin = @CountDeltaOrigin OUTPUT,309      @UseIsInEffect = 0,310      @SlotNumber = @SlotNumber,311      @DBQueueCurrentExtern = @DBQueueCurrent312    EXEC QER_PPersonHasObjectPostProc @SlotNumber,313      @CountDeltaOrigin,314      @CountDeltaQantity,315    'ADS-K-PersonHasObject'316  END TRY317  BEGIN CATCH318    EXEC QBM_PSessionErrorAdd DEFAULT319    RAISERROR('',320    18,321    1)322      WITH NOWAIT323  END CATCH324  endLabel:325END
Open raw exported source
SQL ยท Raw49 lines
1      create   procedure ADS_ZPersonHasObject (@SlotNumber int) AS begin declare @Sourcedata QBM_YDataForDelta , @CountDeltaQantity int , @CountDeltaOrigin2 int declare @QER_BitPattern_Inherit_0 int = dbo.QER_FGIBitPatternInheritInfo('|Inherit|', 0) declare @DBQueueCurrent QBM_YDBQueueCurrent declare @QBM_BitPatternXMarkedForDel_Delay3 int = dbo.QBM_FGIBitPatternXMarkedForDel('|Delay|', 0) declare @QER_BitPatternInherit_Direct int = dbo.QER_FGIBitPatternInheritInfo('|Direct|', 0) declare4 @QER_BitPatternInherit_Master int = dbo.QER_FGIBitPatternInheritInfo('|Master|', 0)  declare @IncludeTSBPersonUsesAccount nvarchar(1024) = dbo.QBM_FGIConfigparmValue5('QER\ComplianceCheck\IncludeTSBPersonUsesAccount') BEGIN TRY insert into @DBQueueCurrent(UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID6) select UID_DialogDBQueue, UID_Parameter, UID_SubParameter, GenProcID from QBMDBQueueCurrent cu with (readpast) where cu.SlotNumber = @SlotNumber if @@rowcount7 = 0 begin goto EndLabel end insert into @SourceData( IsUpcommingContent, XOriginAfter , Element, AssignedElement, XOriginBefore ) select 0, 0 , pho.UID_Person8, pho.ObjectKey, pho.InheritInfo from PersonHasObject pho join @DBQueueCurrent x on pho.UID_Person = x.uid_parameter where pho.ObjectKey like '<Key><T>ADSAccount</T>%'9 or pho.ObjectKey like '<Key><T>ADSGroup</T>%' or pho.ObjectKey like '<Key><T>ADSContact</T>%'   insert into @SourceData( IsUpcommingContent, XOriginBefore10 , Element, AssignedElement, XOriginAfter ) select 1, 0 , y.uid_person, y.XObjectKey, @QER_BitPatternInherit_Direct from ADSAccount y join @DBQueueCurrent11 x on y.uid_person= x.uid_parameter and y.UID_Person > ' ' union all select 1, 0 , y.uid_person, y.XObjectKey, @QER_BitPatternInherit_Direct from ADSContact12 y join @DBQueueCurrent x on y.uid_person= x.uid_parameter and y.UID_Person > ' '  if @IncludeTSBPersonUsesAccount = '1' begin insert into @SourceData(13 IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter ) select 1, 0 , y.uid_person, y.ObjectKeyAccount, @QER_BitPattern_Inherit_014 from TSBPersonUsesAccount y join @DBQueueCurrent x on y.uid_person= x.uid_parameter join ADSAccount a on y.ObjectKeyAccount = a.XObjectKey insert into15 @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter ) select 1, 0 , y.uid_person, y.ObjectKeyAccount, @QER_BitPattern_Inherit_016 from TSBPersonUsesAccount y join @DBQueueCurrent x on y.uid_person= x.uid_parameter join ADSContact a on y.ObjectKeyAccount = a.XObjectKey end   insert17 into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter ) select 1, 0 , y.uid_person, q.XObjectKey, dbo.QER_FCVXOriginToInheritInfo18(z.XOrigin) from ADSAccount y join @DBQueueCurrent x on y.uid_person = x.uid_parameter and y.UID_Person > ' ' join ADSAccountInADSGroup z on y.uid_ADSAccount19 = z.uid_ADSAccount and z.XOrigin > 0 and z.XIsInEffect = 1  join ADSGroup q on q.uid_ADSGroup = z.uid_ADSGroup where z.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay20 = 0 union all select 1, 0 , y.uid_person, q.XObjectKey, dbo.QER_FCVXOriginToInheritInfo(z.XOrigin) from ADSContact y join @DBQueueCurrent x on y.uid_person21 = x.uid_parameter and y.UID_Person > ' ' join ADSContactInADSGroup z on y.UID_ADSContact = z.UID_ADSContact and z.XOrigin > 0 and z.XIsInEffect = 1  join22 ADSGroup q on q.uid_ADSGroup = z.uid_ADSGroup where z.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0  if @IncludeTSBPersonUsesAccount = '1'23 begin insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter ) select 1, 0 , y.uid_person, q.XObjectKey, 24@QER_BitPattern_Inherit_0 from TSBPersonUsesAccount y join @DBQueueCurrent x on y.uid_person = x.uid_parameter join ADSAccount a on y.ObjectKeyAccount 25= a.XObjectKey join ADSAccountInADSGroup z on a.uid_ADSAccount = z.uid_ADSAccount and z.XOrigin > 0 and z.XIsInEffect = 1  join ADSGroup q on q.uid_ADSGroup26 = z.uid_ADSGroup where z.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay = 0 insert into @SourceData( IsUpcommingContent, XOriginBefore , Element27, AssignedElement, XOriginAfter ) select 1, 0 , y.uid_person, q.XObjectKey, @QER_BitPattern_Inherit_0 from TSBPersonUsesAccount y join @DBQueueCurrent 28x on y.uid_person = x.uid_parameter join ADSContact a on y.ObjectKeyAccount = a.XObjectKey join ADSContactInADSGroup z on a.uid_ADSContact = z.uid_ADSContact29 and z.XOrigin > 0 and z.XIsInEffect = 1  join ADSGroup q on q.uid_ADSGroup = z.uid_ADSGroup where z.XMarkedForDeletion & @QBM_BitPatternXMarkedForDel_Delay30 = 0 end   insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter ) select 1, 0 , y.uid_person, q.XObjectKey31, ( @QER_BitPatternInherit_Direct ) as XOrigin from ADSAccount y join @DBQueueCurrent x on y.uid_person= x.uid_parameter and y.UID_Person > ' ' join ADSGroup32 q on q.uid_ADSGroup = y.UID_ADSGroupPrimary  if @IncludeTSBPersonUsesAccount = '1' begin insert into @SourceData( IsUpcommingContent, XOriginBefore , 33Element, AssignedElement, XOriginAfter ) select 1, 0 , y.uid_person, q.XObjectKey, @QER_BitPatternInherit_Direct from TSBPersonUsesAccount y join @DBQueueCurrent34 x on y.uid_person = x.uid_parameter join ADSAccount a on y.ObjectKeyAccount = a.XObjectKey join ADSGroup q on q.uid_ADSGroup = a.UID_ADSGroupPrimary end35   insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter ) select 1, 0 , t.Element, t.XObjectKey, @QER_BitPattern_Inherit_036 from ( select distinct ho.Element, p.XObjectKey from @SourceData ho join ADSGroup g on ho.AssignedElement = g.XObjectKey  join ADSGroupCollection co on37 co.UID_ADSGroupChild = g.uid_ADSGroup join ADSGroup p on co.UID_ADSGroupParent = p.UID_ADSGroup where ho.IsUpcommingContent = 1 and co.UID_ADSGroupChild38 <> co.UID_ADSGroupParent ) as t          insert into @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter ) select 391, 0 , m.uid_person, pho.objectkey, @QER_BitPatternInherit_Master from @DBQueueCurrent x join person m on m.UID_Person = x.uid_parameter  join person s40 on s.UID_PersonMasterIdentity = m.uid_Person join PersonHasObject pho on pho.uid_person = s.uid_person where pho.ObjectKey like '<Key><T>ADSAccount</T>%'41 or pho.ObjectKey like '<Key><T>ADSGroup</T>%' or pho.ObjectKey like '<Key><T>ADSContact</T>%'   if @IncludeTSBPersonUsesAccount = '1' begin insert into42 @SourceData( IsUpcommingContent, XOriginBefore , Element, AssignedElement, XOriginAfter ) select 1, 0 , m.uid_person, pho.ObjectKey, @QER_BitPatternInherit_Master43 from TSBPersonUsesAccount y join @DBQueueCurrent x on y.uid_person = x.uid_parameter join person m on m.UID_Person = y.uid_person join person s on s.UID_PersonMasterIdentity44 = m.uid_Person join PersonHasObject pho on pho.uid_person = s.uid_person where pho.ObjectKey like '<Key><T>ADSAccount</T>%' or pho.ObjectKey like '<Key><T>ADSGroup</T>%'45 or pho.ObjectKey like '<Key><T>ADSContact</T>%' end   berechnen: exec QBM_PDBQueueCalculateDelta @SourceData, @DeltaQuantity = 1, @DeltaDelete = 1, @DeltaInsert46 = 1, @DeltaOrigin = 1, @CountDeltaQantity = @CountDeltaQantity output , @CountDeltaOrigin = @CountDeltaOrigin output , @UseIsInEffect = 0 , @SlotNumber47 = @SlotNumber , @DBQueueCurrentExtern = @DBQueueCurrent exec QER_PPersonHasObjectPostProc @SlotNumber , @CountDeltaOrigin , @CountDeltaQantity , 'ADS-K-PersonHasObject'48 END TRY BEGIN CATCH exec QBM_PSessionErrorAdd default RAISERROR ('', 18, 1) WITH NOWAIT END CATCH endLabel: end 49