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

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

Sandbox DB evidence — IT Shop (2026-04-27)

Read-only evidence captured from the sandbox OneIM database via scripts/sandbox/Invoke-SandboxSql.ps1.

A) ITShopOrg is a view over BaseTree

Query

SELECT name, type_desc
FROM sys.objects
WHERE name LIKE '%ITShopOrg%'
  AND type_desc IN ('USER_TABLE','VIEW')
ORDER BY type_desc, name;

Observed

• Only views exist (ITShopOrg, ITShopOrgHas*, ITShopOrgCollection, …); no ITShopOrg user table.

Query

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.ITShopOrg')) AS ViewDefinition;

Observed

• dbo.ITShopOrg selects from BaseTree with UID_OrgRoot = 'QER-V-ITShopOrg'.
• The full view definition also shows key column mappings:
• BaseTree.UID_Org → ITShopOrg.UID_ITShopOrg
• BaseTree.UID_ParentOrg → ITShopOrg.UID_ParentITShopOrg

A2) BaseTree required (NOT NULL) columns relevant for IT Shop nodes

Query

SELECT
  c.name AS ColumnName,
  t.name AS SqlType,
  c.is_nullable AS IsNullable,
  dc.definition AS DefaultDefinition
FROM sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
LEFT JOIN sys.default_constraints dc
  ON dc.parent_object_id = c.object_id
 AND dc.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID('[dbo].[BaseTree]')
  AND c.is_nullable = 0
ORDER BY c.column_id;

Observed

• BaseTree has only these NOT NULL columns (and none has a default):
• UID_Org
• Ident_Org
• UID_OrgRoot
• XObjectKey

B) ITShopInfo codes observed in this sandbox

Query

SELECT TOP 50
  UID_ITShopOrg,
  UID_ParentITShopOrg,
  Ident_Org,
  ITShopInfo,
  UID_AccProduct,
  treelevel,
  FullPath
FROM dbo.ITShopOrg
ORDER BY treelevel, Ident_Org;

Observed

• ITShopInfo values in the tree: SH, BO, PR, CU.
• Product nodes (PR) have UID_AccProduct set.

Query

SELECT
  COUNT(*) AS Nodes,
  SUM(CASE WHEN UID_AccProduct IS NOT NULL AND UID_AccProduct > ' ' THEN 1 ELSE 0 END) AS NodesWithAccProduct,
  SUM(CASE WHEN ITShopInfo='PR' THEN 1 ELSE 0 END) AS ProductNodes
FROM dbo.BaseTree
WHERE UID_OrgRoot='QER-V-ITShopOrg';

Observed

• Nodes: 26
• Product nodes: 16 (and NodesWithAccProduct == 16)

C) Entitlement “IT Shop flags” exist (but not all are used here)

Query

SELECT
  o.name AS TableName,
  c.name AS ColumnName
FROM sys.columns c
JOIN sys.objects o ON o.object_id=c.object_id
WHERE o.type='U'
  AND c.name LIKE '%ITShop%'
ORDER BY o.name, c.name;

Observed (examples)

• ADSGroup: IsForITShop, IsITShopOnly (0 groups flagged in this sandbox on 2026-04-27)
• QERReuse: IsForITShop, IsITShopOnly (6 rows exist; all flagged)
• AccProduct: IsToHideFromITShop
• BaseTreeAssign: IsITShopEnabled

D) BaseTreeAssign indicates which object types are IT Shop-enabled

Query

SELECT TOP 80
  UID_BaseTreeAssign,
  UID_DialogTableElement,
  UID_DialogTableMN,
  IsITShopEnabled,
  IsReusePossible,
  IsReusePossibleUS,
  DisplayNameElement
FROM dbo.BaseTreeAssign
WHERE IsITShopEnabled = 1
ORDER BY DisplayNameElement;

Observed (examples)

• ADS-T-ADSGroup (Active Directory groups)
• QER-T-QERReuse, QER-T-QERReuseUS (multi-request resources)
• QER-T-QERAssign, QER-T-QERResource (assignment resources / resources)
• TSB-T-TSBAccountDef (account definitions)

E) QERReuse rows are linked to AccProduct

Query

SELECT
  SUM(CASE WHEN IsForITShop = 1 THEN 1 ELSE 0 END) AS ForITShop,
  SUM(CASE WHEN IsITShopOnly = 1 THEN 1 ELSE 0 END) AS ITShopOnly,
  COUNT(*) AS Total
FROM dbo.QERReuse;

Observed

• Total rows: 6
• ForITShop: 6
• ITShopOnly: 6

F) Trigger validation + ShoppingRack DBQueue tasks exist

Query

SELECT o.name AS TableName, tr.name AS TriggerName
FROM sys.triggers tr
JOIN sys.objects o ON o.object_id = tr.parent_id
WHERE o.name = 'BaseTree'
ORDER BY tr.name;

Observed

• Triggers include: QER_TIBaseTree, QER_TUBaseTree, QER_TDBaseTree, plus several GEN_* and ATT_*.

Observed (trigger snippet highlights)

• QER_TIBaseTree enqueues the “ShoppingRack” recalculation task on IT Shop node insertion:
• exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRack-All', ...
• QER_TIBaseTree also enforces basic IT Shop safety rules:
• cannot set ITShopInfo outside UID_OrgRoot IN ('QER-V-ITShopOrg','QER-V-ITShopSrc') (raises an error)
• prevents inserting a node if the predecessor does not exist in the same role class (raises an error)

Query

SELECT TOP 50 UID_Task, ProcedureName
FROM dbo.QBMDBQueueTask
WHERE UID_Task LIKE '%ShoppingRack%'
   OR UID_Task LIKE '%ITShop%'
ORDER BY UID_Task;

Observed (examples)

• QER-K-ShoppingRack-All → QER_ZITShopCheckStructure
• QER-K-ITShopCheck → QER_ZITShopCheck

G) AccProductInITShopOrg is a view over AccProductInBaseTree

Query

SELECT OBJECT_DEFINITION(OBJECT_ID('[dbo].[AccProductInITShopOrg]')) AS ViewDefinition;

Observed

• dbo.AccProductInITShopOrg selects from AccProductInBaseTree and filters to the IT Shop subtree via BaseTree.UID_OrgRoot='QER-V-ITShopOrg'.

H) AccProductInBaseTree exists but is empty in this sandbox

Query

SELECT COUNT(*) AS AccProductInBaseTreeCount
FROM dbo.AccProductInBaseTree;

Observed

• AccProductInBaseTreeCount = 0 (which explains why AccProductInITShopOrg returns 0 rows here).