Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-adsgroup-publish-sequence-db-evidence.md

> Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-adsgroup-publish-sequence-db-evidence.md

Sandbox DB Evidence — ADSGroup Minimal IT Shop Publish Sequence (2026-04-27)

Documents the concrete DB objects and column shapes needed to manually publish a synced

ADSGroup as a requestable item in the IT Shop. Derived from live OneIM DB evidence

on im.sandbox.local (2026-04-27), SELECT-only queries via Invoke-SandboxSql.ps1.

AccProduct — minimum required columns

Only two columns are NOT NULL with no default:

All 55 columns queried; only UID_AccProduct and XObjectKey are hard NOT NULL.

ADSGroup — IT Shop-relevant columns

IsForITShopAutomatic was not found as a column — not present in this sandbox schema.

BaseTreeHasADSGroup — required columns

XObjectKey format: <Key><T>BaseTreeHasADSGroup</T><P>{UID_Org}</P><P>{UID_ADSGroup}</P></Key>

ITShopOrgHasADSGroup view definition

create view dbo.ITShopOrgHasADSGroup as
  select RiskIndexCalculated, UID_ADSGroup, UID_Org as UID_ITShopOrg, ...
  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'
  )

Interpretation: ITShopOrgHasADSGroup is a filtered view of BaseTreeHasADSGroup showing

only rows where the linked UID_Org node is in the IT Shop role class.

ADS_TUAdsGroup trigger — IT Shop-relevant sections

When IsForITShop or IsITShopOnly changes:

if update(isForITShop) or update(isITShopOnly) begin
  -- Collect changed rows into @FlagTest
  exec QER_PIsForITShopFlagCheck 'AdsGroup', @FlagTest,
       'ADSAccountInADSGroup|ADSMachineInADSGroup'
end

When UID_AccProduct changes (links to a new service item):

if update(uid_accproduct) begin
  -- For each ADSGroup whose AccProduct changed:
  exec QBM_PJobCreate_HOUpdate_B N'ITShopOrg', @whereclauseOrg, @GenProcID,
       @p1 = 'uid_ACCProduct', @v1 = @uid_accproduct
  -- Purpose: update BaseTree PR nodes whose UID_AccProduct pointed to the OLD AccProduct
end

Consequence: changing ADSGroup.UID_AccProduct refreshes the ITShopOrg.UID_AccProduct

on any PR nodes that were previously associated with this group.

QER_PIsForITShopFlagCheck procedure — key validation patterns

Uses BaseTreeAssign to find the BaseTreeHas<type> MN table for the entity type,

then validates:

1. Setting IsForITShop=0, IsITShopOnly=0 while group is assigned to an IT Shop node

(via BaseTreeHasADSGroup where XOrigin > 0 and BaseTree.XObjectKey LIKE '<Key><T>ITShop___</T>%'):

→ RAISERROR raised (assignment still exists in IT Shop).

2. Setting IsForITShop=1, IsITShopOnly=1 while group is assigned to a non-IT-Shop node:

→ RAISERROR raised.

3. Child table check (ADSAccountInADSGroup, ADSMachineInADSGroup):

If any child assignment has XOrigin & 0xFFFFFFFD > 0 when IsITShopOnly=1 on the group:

→ RAISERROR raised.

Consequence: setting IsForITShop=1 with no existing BaseTreeHasADSGroup rows passes

silently. Setting it to 0 after the group has been placed in an IT Shop shelf will fail.

Seeded Sandbox IT Shop shelves (BO nodes) — live DB snapshot

From BaseTree where UID_OrgRoot='QER-V-ITShopOrg' and Ident_Org LIKE 'Sandbox%':

Seeded Sandbox service categories (AccProductGroup) — live DB snapshot

Minimal sequence to manually publish one synced ADSGroup

This is a direct-DML sequence for understanding. In production OIM use the REST API or

Manager UI. In the sandbox, each DML step requires -IUnderstandDirectOimDml + -Reason.

Prerequisites

• ADSGroup exists in ADSGroup table (synced from sandbox.local AD via OIM).
• Know the ADSGroup's UID_ADSGroup and XObjectKey.

Step 1 — Create AccProduct (service item)

DECLARE @uid_ap varchar(38) = 'SBX-APRD-<stable-guid-here>';
INSERT INTO AccProduct (UID_AccProduct, XObjectKey, Ident_AccProduct, UID_AccProductGroup)
VALUES (
  @uid_ap,
  '<Key><T>AccProduct</T><P>' + @uid_ap + '</P></Key>',
  'APP_ATLAS_READER',   -- display name shown in portal
  'SBX-APG-18479F84C2374DD3B31FD4DDE8B45A'  -- "Sandbox Applications" category
);

Step 2 — Link AccProduct to ADSGroup

UPDATE ADSGroup
SET UID_AccProduct = 'SBX-APRD-<stable-guid-here>'
WHERE UID_ADSGroup = '<uid_adsgroup>';

Trigger ADS_TUAdsGroup fires on UID_AccProduct change but only refreshes existing PR

nodes (there are none for this group yet). No visible side effects.

Step 3 — Set IsForITShop flag

UPDATE ADSGroup
SET IsForITShop = 1
WHERE UID_ADSGroup = '<uid_adsgroup>';

Trigger ADS_TUAdsGroup fires → QER_PIsForITShopFlagCheck validates: no

BaseTreeHasADSGroup rows exist yet → passes silently.

Step 4 — Place group on BO shelf

DECLARE @uid_org varchar(38) = 'SBX-ITS-5C7F1AC9D4A34DBBB23EA107B77C18'; -- "Sandbox Applications" shelf
DECLARE @uid_adsg varchar(38) = '<uid_adsgroup>';
INSERT INTO BaseTreeHasADSGroup (UID_Org, UID_ADSGroup, XObjectKey, XOrigin, XIsInEffect)
VALUES (
  @uid_org,
  @uid_adsg,
  '<Key><T>BaseTreeHasADSGroup</T><P>' + @uid_org + '</P><P>' + @uid_adsg + '</P></Key>',
  1,  -- XOrigin: direct/manual
  1   -- XIsInEffect: in effect immediately
);

After this INSERT, ITShopOrgHasADSGroup will return this row (the BO shelf is in IT

Shop subtree). The BaseTree.UID_OrgRoot = 'QER-V-ITShopOrg' check passes.

Step 5 — DBQueue processes ShoppingRack

QER-K-ShoppingRack-All (→ QER_ZITShopCheckStructure) or

QER-K-ShoppingRackProductNode (→ QER_ZITShopCheckMethodPR) runs and calls

QER_PITShopProductNodeCreate_b which:

1. Finds the BaseTreeHasADSGroup entry for the BO shelf.

2. Creates a BaseTree PR node under the BO shelf (ITShopInfo='PR').

3. Sets BaseTree.UID_AccProduct on the PR node (from ADSGroup.UID_AccProduct).

4. Creates BaseTreeHasADSGroup for the PR node.

5. Creates BaseTreeHasObject with the ADSGroup's XObjectKey.

6. Enqueues QBM_PJobCreate_HOUpdate to update ITShopOrg.UID_AccProduct on the PR node.

The group then appears as requestable in the Web Portal under the "Sandbox Applications"

shelf.

Sequence ordering matters

Reverting (for test/rollback)

-- Remove BO shelf assignment (triggers no RAISERROR if IsForITShop is still 1,
-- but leaves an inconsistent state — set IsForITShop=0 immediately after)
DELETE FROM BaseTreeHasADSGroup WHERE UID_Org = @uid_org AND UID_ADSGroup = @uid_adsg;

-- Remove PR node (if DBQueue already ran)
DELETE FROM BaseTree WHERE UID_OrgRoot='QER-V-ITShopOrg' AND ITShopInfo='PR' AND UID_AccProduct = @uid_ap;

-- Unset flags and AccProduct link on ADSGroup
UPDATE ADSGroup SET IsForITShop = 0, UID_AccProduct = NULL WHERE UID_ADSGroup = @uid_adsg;

-- Remove AccProduct
DELETE FROM AccProduct WHERE UID_AccProduct = @uid_ap;

Warning: DBQueue jobs may have already created additional rows (e.g., BaseTreeHasObject,

additional BaseTreeHasADSGroup for the PR node). Run QER-K-ShoppingRack-All again

after rollback to stabilize.