Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-basetreehasadsgroup-pr-links-and-basetreehasobject-evidence.md

> Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-basetreehasadsgroup-pr-links-and-basetreehasobject-evidence.md

BaseTreeHasADSGroup PR links + BaseTreeHasObject (ADSGroup) — Live sandbox evidence

Environment:

• Host: im.sandbox.local
• Database: OIM (OneIM)
• Access: scripts/sandbox/Invoke-SandboxSql.ps1 (direct TCP 1433, SELECT-only)
• Observed: 2026-04-27

1) BaseTreeHasObject schema (key columns)

BaseTreeHasObject is the “generic” link table between an org node (UID_Org) and an ObjectKey (XML-ish key string).

Live schema (sys.columns) for dbo.BaseTreeHasObject:

• UID_BaseTreeHasObject (NOT NULL)
• ObjectKey (NOT NULL)
• UID_Org (NOT NULL)
• XObjectKey (NOT NULL)
• InheritInfo (DEFAULT 0)

Notes:

• For ADSGroup, ObjectKey matches the ADSGroup.XObjectKey shape: <Key><T>ADSGroup</T><P>{UID_ADSGroup}</P></Key>.
• The UID_Org value for IT Shop product nodes is the same GUID used by ITShopOrg (BaseTree row) — BaseTree.XObjectKey then uses <T>ITShopOrg</T><P>{UID_Org}</P>.

2) ITShopOrgHasADSGroup view includes all node types in the IT Shop subtree

Live view definition:

create view dbo.ITShopOrgHasADSGroup as
select ...
from BaseTreeHasADSGroup
where exists (
  select top 1 1
  from BaseTree y
  where y.UID_Org = BaseTreeHasADSGroup.UID_Org
    and y.UID_OrgRoot = 'QER-V-ITShopOrg'
)

Important consequence:

• dbo.ITShopOrgHasADSGroup does not filter by BaseTree.ITShopInfo.
• Once an entitlement is published and a PR node exists, you should expect two BaseTreeHasADSGroup rows per published ADSGroup:
• one to the BO shelf (ITShopInfo='BO')
• one to the PR product node (ITShopInfo='PR')

3) Concrete example: APP_ATLAS_ADMIN

3.1 PR node + BaseTreeHasObject link

Joined evidence:

• ADSGroup.CN='APP_ATLAS_ADMIN'
• BaseTree PR node exists in IT Shop subtree by BaseTree.UID_AccProduct = ADSGroup.UID_AccProduct
• BaseTreeHasObject.ObjectKey = ADSGroup.XObjectKey on the PR node

Key IDs (live):

• ADSGroup.UID_ADSGroup = 9c7ed825-49c8-48b8-92ff-b7f4bc92a2a4
• ADSGroup.UID_AccProduct = 97f5235e-670f-40fa-9373-2aaee32ea21a
• PR node BaseTree.UID_Org = 2B7E6D9A-B1A1-4AE7-9B4F-6C542364BA75
• BaseTreeHasObject.UID_BaseTreeHasObject = 76EA69D6-CDA7-E8B0-34C1-0BF4F023152A

3.2 BaseTreeHasADSGroup has both BO and PR links

For the same group, BaseTreeHasADSGroup contains two rows in the IT Shop subtree:

• UID_Org = SBX-ITS-5C7F1AC9D4A34DBBB23EA107B77C18 → BaseTree.ITShopInfo='BO' / Ident_Org='Sandbox Applications'
• UID_Org = 2B7E6D9A-B1A1-4AE7-9B4F-6C542364BA75 → BaseTree.ITShopInfo='PR' / Ident_Org='Atlas Office - Administrator'

4) Seeded catalog completeness (97/97)

For all ADSGroup rows with marker [OIM-SANDBOX-SEED:ad-shop-seeding:v1] (97 rows):

• 97 have a BO shelf link in BaseTreeHasADSGroup (BaseTree.ITShopInfo='BO')
• 97 have a PR link in BaseTreeHasADSGroup (BaseTree.ITShopInfo='PR')
• 97 have a PR node in BaseTree (ITShopInfo='PR' + UID_AccProduct set)
• 97 have a BaseTreeHasObject link from the PR node to the ADSGroup.XObjectKey

5) Shipped trigger/procedure behavior (live DB definitions)

5.1 ADS_TIBaseTreeHasADSGroup (insert trigger)

Relevant behavior (excerpt):

• runs QER_PAssignmentCheckValid 'ADS-AsgnBT-ADSGroup', ...
• blocks invalid IT Shop flag combinations at assignment-time via:
• dbo.QER_FGIITShopFlagCombineValid(...)
• RAISERROR '#LDS#Assignment is not permitted due to the combination of IT Shop flags.|'

5.2 ADS_TUBaseTreeHasADSGroup (update trigger)

On XOrigin / XIsInEffect changes it enqueues ADS-K-BaseTreeHasObject (bulk) for affected org nodes.

5.3 ADS_ZBaseTreeHasObject (DBQueue task procedure)

Live procedure logic shows how BaseTreeHasObject rows for ADSGroup are derived from BaseTreeHasADSGroup:

• loads existing BaseTreeHasObject rows where ObjectKey like '<Key><T>ADSGroup</T>%'
• loads “upcoming” source rows from BaseTreeHasADSGroup joined to ADSGroup.XObjectKey
• computes deltas and applies inserts/deletes to keep BaseTreeHasObject consistent