Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-itshoporg-columns-and-service-categories-db-evidence.md

> Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-itshoporg-columns-and-service-categories-db-evidence.md

Sandbox DB Evidence — ITShopOrg columns + service categories (2026-04-27)

Method

Evidence

1) dbo.ITShopOrg columns and required (NOT NULL) fields

Query (columns)

SELECT c.ORDINAL_POSITION, c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA='dbo' AND c.TABLE_NAME='ITShopOrg'
ORDER BY c.ORDINAL_POSITION;

Result (fields with IS_NULLABLE = 'NO')

Query (view filter snippet)

SELECT TOP (1)
  SUBSTRING(m.definition,
    CASE WHEN CHARINDEX('QER-V-ITShopOrg', m.definition) > 140 THEN CHARINDEX('QER-V-ITShopOrg', m.definition) - 140 ELSE 1 END,
    360) AS WhereSnippet
FROM sys.sql_modules m
JOIN sys.objects o ON o.object_id = m.object_id
WHERE o.object_id = OBJECT_ID('dbo.ITShopOrg');

Result (excerpt)

from BaseTree
 where UID_OrgRoot = 'QER-V-ITShopOrg'

Interpretation: in this sandbox, ITShopOrg is a view over BaseTree filtered by UID_OrgRoot='QER-V-ITShopOrg'.

2) dbo.BaseTree nullability for IT Shop-relevant columns

Query

SELECT c.name AS ColumnName,
       t.name AS SqlType,
       c.max_length,
       c.is_nullable,
       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.name IN (
    'UID_Org','UID_ParentOrg','UID_OrgRoot','Ident_Org','XObjectKey',
    'ITShopInfo','UID_AccProduct','InternalName','FullPath','treelevel'
  )
ORDER BY c.name;

Result (high-signal)

3) ITShopInfo enforcement and ShoppingRack DBQueue enqueue (trigger evidence)

The DB trigger logic (compiled into QER_TIBaseTree / QER_TUBaseTree) enforces that ITShopInfo can only be set for specific UID_OrgRoot values, and enqueues ShoppingRack processing for IT Shop nodes.

Live DB snippet (QER_TIBaseTree, ITShopInfo)

... where i.uid_orgroot = 'QER-V-ITShopOrg' and i.itshopinfo in ('SC', 'SH', 'BO', 'PR') ...
exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRack-All', ...

if exists (... where i.ITShopInfo > ' ' and i.UID_OrgRoot not in ('QER-V-ITShopOrg', 'QER-V-ITShopSrc')) begin
  raiserror('#LDS#Cannot set IT Shop information, because role class is not suitable for IT Shop.|', 18, 2) ...
end

Live DB snippet (QER_TUBaseTree)

if update(ITShopInfo) begin
  if exists (... where b.ITShopInfo > ' ' and b.UID_OrgRoot not in ('QER-V-ITShopOrg', 'QER-V-ITShopSrc') ) begin
    raiserror('#LDS#Cannot set IT Shop information, because role class is not suitable for IT Shop.|', 18, 2) ...
  end
end

Host script corroboration (install media)

4) Service categories are linked directly via AccProduct.UID_AccProductGroup

In this sandbox schema, AccProduct links to its service category through a direct column (not a separate mapping table).

Query (columns)

SELECT c.name AS ColumnName, t.name AS SqlType, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.AccProduct')
  AND c.name LIKE '%AccProductGroup%';

Result

Query (count products with/without service category)

SELECT
  COUNT(*) AS TotalProducts,
  SUM(CASE WHEN UID_AccProductGroup IS NULL OR UID_AccProductGroup='' THEN 1 ELSE 0 END) AS ProductsWithoutCategory,
  SUM(CASE WHEN UID_AccProductGroup IS NOT NULL AND UID_AccProductGroup<>'' THEN 1 ELSE 0 END) AS ProductsWithCategory
FROM dbo.AccProduct;

Result

Query (service categories + product counts)

SELECT TOP (20)
  g.UID_AccProductGroup,
  g.Ident_AccProductGroup,
  g.Description,
  g.UID_AccProductGroupParent,
  COUNT(p.UID_AccProduct) AS ProductCount
FROM dbo.AccProductGroup g
LEFT JOIN dbo.AccProduct p ON p.UID_AccProductGroup = g.UID_AccProductGroup
GROUP BY g.UID_AccProductGroup, g.Ident_AccProductGroup, g.Description, g.UID_AccProductGroupParent
ORDER BY COUNT(p.UID_AccProduct) DESC, g.Ident_AccProductGroup;

Result (high-signal)

5) Service-category closure (QER_VACCProductGroupCollection)

Query (columns)

SELECT c.ORDINAL_POSITION, c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA='dbo' AND c.TABLE_NAME='QER_VACCProductGroupCollection'
ORDER BY c.ORDINAL_POSITION;

Result

Example (child category includes itself + ancestors)

SELECT
  UID_AccProductGroup,
  UID_AccProductGroupParent,
  CountSteps
FROM dbo.QER_VACCProductGroupCollection
WHERE UID_AccProductGroup = 'ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4'
ORDER BY CountSteps;

Result (example)