Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-accproductinbasetree-views-and-trigger-hooks-db-evidence.md

> Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-accproductinbasetree-views-and-trigger-hooks-db-evidence.md

Sandbox DB Evidence — AccProductInBaseTree, views, and trigger hooks (2026-04-27)

Scope

This note captures live sandbox DB evidence about:

• why some “availability” views can appear empty (AccProductInITShopOrg),
• which views back common troubleshooting queries (ITShopOrg, ITShopOrgHasADSGroup),
• which triggers/procs are directly involved when IT Shop-related flags/links change.

All evidence was gathered read-only via scripts/sandbox/Invoke-SandboxSql.ps1 against the OneIM database on im.sandbox.local on 2026-04-27.

1) Key views (live DB definitions)

ITShopOrg (BaseTree filter)

create view dbo.ITShopOrg as
...
from BaseTree
where UID_OrgRoot = 'QER-V-ITShopOrg'

ITShopOrgHasADSGroup (BaseTreeHasADSGroup + “is inside ITShopOrg subtree”)

create view dbo.ITShopOrgHasADSGroup as
...
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'
)

AccProductInITShopOrg (depends solely on AccProductInBaseTree)

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

2) The “empty mapping table” mismatch (counts)

In this sandbox, AccProductInBaseTree is empty even though PR nodes exist with BaseTree.UID_AccProduct:

{
  "AccProductInBaseTree_Count": 0,
  "BaseTree_PR_WithAccProduct": 16
}

Practical troubleshooting implication (sandbox-specific):

• SELECT * FROM AccProductInITShopOrg returns 0 here (because it reads only from AccProductInBaseTree).
• To find “product nodes with service items”, query BaseTree (ITShopInfo='PR') and join AccProduct by BaseTree.UID_AccProduct.

3) Service categories are not a gate in this environment (PR snapshot)

Live snapshot of PR nodes in QER-V-ITShopOrg:

• PR nodes total: 16
• PR nodes whose service item has AccProduct.UID_AccProductGroup IS NULL: 7

Example of “no service category” PR nodes (subset):

Challenge loss of role membership
Delegation
Deputy (temporary)
New manager assignment
Role entitlement assignment
Role membership

4) Minimal required columns (schema evidence)

BaseTreeHasADSGroup (MN; no separate UID PK)

NOT NULL columns:

UID_Org (varchar 38)
UID_ADSGroup (varchar 38)
XObjectKey (varchar 138)

BaseTreeHasObject (PR node ↔ entitlement ObjectKey)

NOT NULL columns:

UID_BaseTreeHasObject (varchar 38)
UID_Org (varchar 38)
ObjectKey (varchar 138)
XObjectKey (varchar 138)

5) Trigger/procedure hooks worth knowing (live DB evidence)

ADS_TUAdsGroup calls flag validation + updates existing PR nodes on product changes

Observed behaviors:

• IsForITShop / IsITShopOnly changes call QER_PIsForITShopFlagCheck('AdsGroup', ...).
• UID_AccProduct changes trigger a QBM_PJobCreate_HOUpdate_B N'ITShopOrg', ... against existing PR nodes (via QER_VPWOProductNodesSlim).

QER_TUBaseTree enqueues ShoppingRack tasks for PR nodes

On PR node BaseTree.UID_AccProduct changes (IT Shop subtree only), the trigger enqueues:

• QER-K-ShoppingRackProductNode (bulk) for the affected PR nodes.

Raw query bundle (local artifacts)

This run saved the raw JSON envelopes (from Invoke-SandboxSql -Json) under:

• .tmp/oim-kb-update/2026-04-27-run24/