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

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

Sandbox DB Evidence — IT Shop Availability (2026-04-27)

This note captures DB-backed evidence from the live OneIM DB in the sandbox about service items (AccProduct), service categories (AccProductGroup), and how products appear (or do not appear) in placement/mapping tables/views.

Method

Queries executed from the workstation via pwsh -NoProfile -File scripts/sandbox/Invoke-SandboxSql.ps1 -Database OIM.
Read-only queries only (no DML performed in this run).

Evidence

1) Relevant DB objects (type)

SELECT name, type_desc
FROM sys.objects
WHERE name IN ('AccProductInBaseTree','AccProductInITShopOrg','BaseTree','AccProduct','AccProductGroup')
ORDER BY name;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":44,"rowsAffected":null,"rowCount":5,"rows":[{"name":"AccProduct","type_desc":"USER_TABLE"},{"name":"AccProductGroup","type_desc":"USER_TABLE"},{"name":"AccProductInBaseTree","type_desc":"USER_TABLE"},{"name":"AccProductInITShopOrg","type_desc":"VIEW"},{"name":"BaseTree","type_desc":"USER_TABLE"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

2) Counts: mapping table empty, but PR nodes exist

SELECT
  (SELECT COUNT(1) FROM AccProductInBaseTree) AS AccProductInBaseTreeCnt,
  (SELECT COUNT(1) FROM BaseTree WHERE UID_OrgRoot='QER-V-ITShopOrg' AND ITShopInfo='PR') AS ItShopPrNodeCnt,
  (SELECT COUNT(1) FROM BaseTreeHasObject WHERE ObjectKey LIKE '%AccProduct%') AS BaseTreeHasObjectAccProductCnt;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":44,"rowsAffected":null,"rowCount":1,"rows":{"AccProductInBaseTreeCnt":0,"ItShopPrNodeCnt":16,"BaseTreeHasObjectAccProductCnt":0},"preSnapshot":null,"postSnapshot":null,"committed":null}

3) View definition: `AccProductInITShopOrg` reads `AccProductInBaseTree`

SELECT SUBSTRING(
  CAST(OBJECT_DEFINITION(OBJECT_ID('AccProductInITShopOrg')) AS nvarchar(max)),
  1,
  2000
) AS Def;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":61,"rowsAffected":null,"rowCount":1,"rows":{"Def":"create   view dbo.AccProductInITShopOrg as \r\n select \r\n\tUID_AccProduct as UID_AccProduct,\r\n\tUID_Org as UID_ITShopOrg,\r\n\tXDateInserted as XDateInserted,\r\n\tXDateUpdated as XDateUpdated,\r\n\tXMarkedForDeletion as XMarkedForDeletion,\r\n\tXObjectKey as XObjectKey,\r\n\tXTouched as XTouched,\r\n\tXUserInserted as XUserInserted,\r\n\tXUserUpdated as XUserUpdated\r\n from AccProductInBaseTree\r\n where exists (select top 1 1 from BaseTree y where y.UID_Org = AccProductInBaseTree.UID_Org and y.UID_OrgRoot = 'QER-V-ITShopOrg')\r\n"},"preSnapshot":null,"postSnapshot":null,"committed":null}

4) PR nodes in `BaseTree` carry `UID_AccProduct` (top 10)

SELECT TOP 10
  UID_Org,
  Ident_Org,
  UID_AccProduct,
  UID_ParentOrg,
  XDateInserted
FROM BaseTree
WHERE UID_OrgRoot='QER-V-ITShopOrg'
  AND ITShopInfo='PR'
ORDER BY XDateInserted DESC;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":49,"rowsAffected":null,"rowCount":10,"rows":[{"UID_Org":"F5888005-B84A-4FC3-A76E-21212250765B","Ident_Org":"Standard LDAP Account","UID_AccProduct":"e44592f5-64e4-4c5a-ac26-bea979f662f5","UID_ParentOrg":"QER-3c05f166d3224c4aa3f069913bc36c1d","XDateInserted":"2026-04-18T17:34:11.247"},{"UID_Org":"3A812A6C-2664-4875-967D-FDF75383402E","Ident_Org":"Sandbox Domain Accounts","UID_AccProduct":"e981f143-afa0-4a50-bd1e-bb8668188908","UID_ParentOrg":"QER-3c05f166d3224c4aa3f069913bc36c1d","XDateInserted":"2026-03-17T18:23:53.963"},{"UID_Org":"ADS-ITSHOPORG-ADS-NEWGROUP-SG","Ident_Org":"New Active Directory security group","UID_AccProduct":"ADS-FADE2DA6501F4A26AC7791D257A128DB","UID_ParentOrg":"ADS-ITSHOPORG-GROUPS-BO","XDateInserted":"2025-06-27T16:00:17.493"},{"UID_Org":"ADS-ITSHOPORG-ADS-NEWGROUP-DL","Ident_Org":"New Active Directory distribution group","UID_AccProduct":"ADS-EFFB4A3D760248949257BC4781592E7C","UID_ParentOrg":"ADS-ITSHOPORG-GROUPS-BO","XDateInserted":"2025-06-27T16:00:17.493"},{"UID_Org":"ADS-ITSHOPORG-ADS-GROUPDELETE","Ident_Org":"Delete Active Directory group","UID_AccProduct":"ADS-A261D1D7E0D04060899D679C002A7543","UID_ParentOrg":"ADS-ITSHOPORG-GROUPS-BO","XDateInserted":"2025-06-27T16:00:17.493"},{"UID_Org":"ADS-ITSHOPORG-ADS-GROUPCHANGE","Ident_Org":"Modify Active Directory group","UID_AccProduct":"ADS-81F10A79075441E8B47EEC91D04E01D4","UID_ParentOrg":"ADS-ITSHOPORG-GROUPS-BO","XDateInserted":"2025-06-27T16:00:17.493"},{"UID_Org":"RMS-ED373920E29A6B40B8198B587C6DAA1A","Ident_Org":"System role entitlement assignment","UID_AccProduct":"RMS-79b6410a215f48aa8648cd20bf1d4ed1","UID_ParentOrg":"QER-ITSHOPORG-DELEGATION-BO","XDateInserted":"2025-06-27T15:59:45.707"},{"UID_Org":"QER-ITSHOPORG-DELEGATION-MEMBER-PR","Ident_Org":"Role membership","UID_AccProduct":"QER-E4C02D1588604613B2E3743B65BF5453","UID_ParentOrg":"QER-ITSHOPORG-DELEGATION-BO","XDateInserted":"2025-06-27T15:59:23.757"},{"UID_Org":"QER-ITSHOPORG-DELEGATION-ASSIGN-PR","Ident_Org":"Role entitlement assignment","UID_AccProduct":"QER-49D549178FF441228884C3F0754A8DE1","UID_ParentOrg":"QER-ITSHOPORG-DELEGATION-BO","XDateInserted":"2025-06-27T15:59:23.757"},{"UID_Org":"QER-7B26B28F2EFE4989A20B0A9D1A01C108","Ident_Org":"New Starling Cloud Assistant recipient","UID_AccProduct":"QER-3072C3AEF9124F4CBF0B2F67F47B82F5","UID_ParentOrg":"QER-3c05f166d3224c4aa3f069913bc36c1d","XDateInserted":"2025-06-27T15:59:23.757"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

5) IT Shop flags appear on common entitlement tables

SELECT o.name AS tableName, c.name AS columnName
FROM sys.objects o
JOIN sys.columns c ON c.object_id=o.object_id
WHERE o.type='U'
  AND c.name IN ('IsForITShop','IsITShopOnly','IsToHideFromITShop')
ORDER BY o.name, c.name;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":72,"rowsAffected":null,"rowCount":27,"rows":[{"tableName":"AccProduct","columnName":"IsToHideFromITShop"},{"tableName":"ADSGroup","columnName":"IsForITShop"},{"tableName":"ADSGroup","columnName":"IsITShopOnly"},{"tableName":"ESet","columnName":"IsForITShop"},{"tableName":"ESet","columnName":"IsITShopOnly"},{"tableName":"LDAPGroup","columnName":"IsForITShop"},{"tableName":"LDAPGroup","columnName":"IsITShopOnly"},{"tableName":"QERAssign","columnName":"IsForITShop"},{"tableName":"QERAssign","columnName":"IsITShopOnly"},{"tableName":"QERResource","columnName":"IsForITShop"},{"tableName":"QERResource","columnName":"IsITShopOnly"},{"tableName":"QERReuse","columnName":"IsForITShop"},{"tableName":"QERReuse","columnName":"IsITShopOnly"},{"tableName":"QERReuseUS","columnName":"IsForITShop"},{"tableName":"QERReuseUS","columnName":"IsITShopOnly"},{"tableName":"RPSReport","columnName":"IsForITShop"},{"tableName":"RPSReport","columnName":"IsITShopOnly"},{"tableName":"TSBAccountDef","columnName":"IsForITShop"},{"tableName":"TSBAccountDef","columnName":"IsITShopOnly"},{"tableName":"UNSGroupB","columnName":"IsForITShop"},{"tableName":"UNSGroupB","columnName":"IsITShopOnly"},{"tableName":"UNSGroupB1","columnName":"IsForITShop"},{"tableName":"UNSGroupB1","columnName":"IsITShopOnly"},{"tableName":"UNSGroupB2","columnName":"IsForITShop"},{"tableName":"UNSGroupB2","columnName":"IsITShopOnly"},{"tableName":"UNSGroupB3","columnName":"IsForITShop"},{"tableName":"UNSGroupB3","columnName":"IsITShopOnly"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

AccProductInBaseTree object type

`sql

SELECT name, type_desc FROM sys.objects WHERE name IN ('AccProductInBaseTree','ITShopOrg','BaseTreeHasObject','AccProduct','AccProductGroup');

`\r\n

`json

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":63,"rowsAffected":null,"rowCount":5,"rows":[{"name":"AccProduct","type_desc":"USER_TABLE"},{"name":"AccProductGroup","type_desc":"USER_TABLE"},{"name":"AccProductInBaseTree","type_desc":"USER_TABLE"},{"name":"BaseTreeHasObject","type_desc":"USER_TABLE"},{"name":"ITShopOrg","type_desc":"VIEW"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

`\r\n

AccProductInBaseTree definition (if view)

`sql

SELECT OBJECT_DEFINITION(OBJECT_ID('AccProductInBaseTree')) AS Definition;

`\r\n

`json

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":40,"rowsAffected":null,"rowCount":1,"rows":{"Definition":null},"preSnapshot":null,"postSnapshot":null,"committed":null}

`\r\n

Tables/views containing 'AccProduct'

`sql

SELECT name, type_desc FROM sys.objects WHERE name LIKE '%AccProduct%' ORDER BY type_desc, name;

`\r\n

`json

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":96,"rowsAffected":null,"rowCount":27,"rows":[{"name":"QER_FGIReasonTypeForACCProduct","type_desc":"SQL_SCALAR_FUNCTION"},{"name":"QER_RAccProductGroupCollection","type_desc":"SQL_STORED_PROCEDURE"},{"name":"QER_ZAccProductGroupCollection","type_desc":"SQL_STORED_PROCEDURE"},{"name":"ARS_TUAccProduct","type_desc":"SQL_TRIGGER"},{"name":"ATT_TUAccProduct","type_desc":"SQL_TRIGGER"},{"name":"ATT_TUAccProductGroup","type_desc":"SQL_TRIGGER"},{"name":"QER_TDAccProductGroup","type_desc":"SQL_TRIGGER"},{"name":"QER_TIAccProductGroup","type_desc":"SQL_TRIGGER"},{"name":"QER_TUAccProduct","type_desc":"SQL_TRIGGER"},{"name":"QER_TUAccProductGroup","type_desc":"SQL_TRIGGER"},{"name":"AccProduct","type_desc":"USER_TABLE"},{"name":"AccProductDependencies","type_desc":"USER_TABLE"},{"name":"AccProductGroup","type_desc":"USER_TABLE"},{"name":"AccProductGroupCollection","type_desc":"USER_TABLE"},{"name":"AccProductInAccProduct","type_desc":"USER_TABLE"},{"name":"AccProductInBaseTree","type_desc":"USER_TABLE"},{"name":"AccProductInFunctionalArea","type_desc":"USER_TABLE"},{"name":"AccProductParamCategory","type_desc":"USER_TABLE"},{"name":"AccProductParameter","type_desc":"USER_TABLE"},{"name":"AccProductInDepartment","type_desc":"VIEW"},{"name":"AccProductInITShopOrg","type_desc":"VIEW"},{"name":"AccProductInLocality","type_desc":"VIEW"},{"name":"AccProductInOrg","type_desc":"VIEW"},{"name":"AccProductInProfitCenter","type_desc":"VIEW"},{"name":"QER_VACCProductGroupCollection","type_desc":"VIEW"},{"name":"QER_VAccProductNotAssigned","type_desc":"VIEW"},{"name":"QERAccProductUsage","type_desc":"VIEW"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

`\r\n

AccProduct key columns (name-like filters)

`sql

SELECT c.name, t.name AS sql_type, c.max_length, c.is_nullable FROM sys.columns c JOIN sys.types t ON c.user_type_id=t.user_type_id WHERE c.object_id=OBJECT_ID('AccProduct') AND (c.name LIKE 'UID_%' OR c.name LIKE 'Ident_%' OR c.name LIKE '%ITShop%' OR c.name LIKE 'Is%Shop%' OR c.name LIKE '%ObjectKey%' OR c.name LIKE '%Group%') ORDER BY c.name;

`\r\n

`json

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":53,"rowsAffected":null,"rowCount":14,"rows":[{"name":"Ident_AccProduct","sql_type":"nvarchar","max_length":512,"is_nullable":true},{"name":"IsCopyOnShopChange","sql_type":"bit","max_length":1,"is_nullable":true},{"name":"IsToHideFromITShop","sql_type":"bit","max_length":1,"is_nullable":true},{"name":"UID_AccProduct","sql_type":"varchar","max_length":38,"is_nullable":false},{"name":"UID_AccProductGroup","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_AccProductParamCategory","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_FirmPartner","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_FunctionalArea","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_OrgAttestator","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_OrgRuler","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_ProfitCenter","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_PWODecisionMethod","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_QERTermsOfUse","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"XObjectKey","sql_type":"varchar","max_length":138,"is_nullable":false}],"preSnapshot":null,"postSnapshot":null,"committed":null}

`\r\n

AccProductGroup key columns (name-like filters)

`sql

SELECT c.name, t.name AS sql_type, c.max_length, c.is_nullable FROM sys.columns c JOIN sys.types t ON c.user_type_id=t.user_type_id WHERE c.object_id=OBJECT_ID('AccProductGroup') AND (c.name LIKE 'UID_%' OR c.name LIKE 'Ident_%' OR c.name LIKE '%Shop%' OR c.name LIKE '%ObjectKey%' OR c.name LIKE 'Is%') ORDER BY c.name;

`\r\n

`json

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":51,"rowsAffected":null,"rowCount":9,"rows":[{"name":"Ident_AccProductGroup","sql_type":"nvarchar","max_length":512,"is_nullable":true},{"name":"IsSpecialGroup","sql_type":"bit","max_length":1,"is_nullable":true},{"name":"UID_AccProductGroup","sql_type":"varchar","max_length":38,"is_nullable":false},{"name":"UID_AccProductGroupParent","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_AccProductParamCategory","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_OrgAttestator","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_OrgRuler","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_PWODecisionMethod","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"XObjectKey","sql_type":"varchar","max_length":138,"is_nullable":false}],"preSnapshot":null,"postSnapshot":null,"committed":null}

`\r\n

BaseTree columns related to IT Shop/product placement

`sql

SELECT c.name, t.name AS sql_type, c.max_length, c.is_nullable FROM sys.columns c JOIN sys.types t ON c.user_type_id=t.user_type_id WHERE c.object_id=OBJECT_ID('BaseTree') AND (c.name IN ('UID_OrgRoot','UID_BaseTreeParent','ITShopInfo','UID_AccProduct') OR c.name LIKE '%ObjectKey%' OR c.name LIKE 'Ident_%') ORDER BY c.name;

`\r\n

`json

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":53,"rowsAffected":null,"rowCount":5,"rows":[{"name":"Ident_Org","sql_type":"nvarchar","max_length":512,"is_nullable":false},{"name":"ITShopInfo","sql_type":"nvarchar","max_length":4,"is_nullable":true},{"name":"UID_AccProduct","sql_type":"varchar","max_length":38,"is_nullable":true},{"name":"UID_OrgRoot","sql_type":"varchar","max_length":38,"is_nullable":false},{"name":"XObjectKey","sql_type":"varchar","max_length":138,"is_nullable":false}],"preSnapshot":null,"postSnapshot":null,"committed":null}

`\r\n

BaseTreeHasObject columns

`sql

`\r\n

`json

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":52,"rowsAffected":null,"rowCount":6,"rows":[{"name":"UID_BaseTreeHasObject","sql_type":"varchar","max_length":38,"is_nullable":false},{"name":"ObjectKey","sql_type":"varchar","max_length":138,"is_nullable":false},{"name":"UID_Org","sql_type":"varchar","max_length":38,"is_nullable":false},{"name":"XTouched","sql_type":"nchar","max_length":2,"is_nullable":true},{"name":"XObjectKey","sql_type":"varchar","max_length":138,"is_nullable":false},{"name":"InheritInfo","sql_type":"int","max_length":4,"is_nullable":true}],"preSnapshot":null,"postSnapshot":null,"committed":null}

`\r\n

AccProductInBaseTree columns

`sql

SELECT c.column_id, c.name FROM sys.columns c WHERE c.object_id=OBJECT_ID('AccProductInBaseTree') ORDER BY c.column_id;

`\r\n

`json

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":60,"rowsAffected":null,"rowCount":9,"rows":[{"column_id":1,"name":"UID_Org"},{"column_id":2,"name":"UID_AccProduct"},{"column_id":3,"name":"XDateInserted"},{"column_id":4,"name":"XDateUpdated"},{"column_id":5,"name":"XUserInserted"},{"column_id":6,"name":"XUserUpdated"},{"column_id":7,"name":"XTouched"},{"column_id":8,"name":"XObjectKey"},{"column_id":9,"name":"XMarkedForDeletion"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

`\r\n

AccProductInBaseTree row count

`sql

SELECT COUNT(1) AS RowCount FROM AccProductInBaseTree;

`\r\n

`json

`\r\n

6) `BaseTreeAssign` shows which entitlement types can be placed in IT Shop (and via which `BaseTreeHas<type>` table)

The live DB BaseTreeAssign rows with IsITShopEnabled=1 explain which entitlement tables can participate in IT Shop placement and which “MN table” is used for shelf/object assignment.

SELECT
  dt.TableName AS RightTable,
  mn.TableName AS MnTable,
  bta.IsITShopEnabled,
  bta.IsReusePossible,
  bta.DisplayNameElement,
  bta.UID_BaseTreeAssign
FROM BaseTreeAssign bta
JOIN DialogTable dt ON dt.UID_DialogTable=bta.UID_DialogTableElement
LEFT JOIN DialogTable mn ON mn.UID_DialogTable=bta.UID_DialogTableMN
WHERE bta.IsITShopEnabled=1
ORDER BY dt.TableName;

{"RightTable":"ADSGroup","MnTable":"BaseTreeHasADSGroup","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"Active Directory groups","UID_BaseTreeAssign":"ADS-AsgnBT-ADSGroup"}
{"RightTable":"ESet","MnTable":"BaseTreeHasESet","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"System roles","UID_BaseTreeAssign":"RMS-AsgnBT-ESet"}
{"RightTable":"LDAPGroup","MnTable":"BaseTreeHasLDAPGroup","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"LDAP groups","UID_BaseTreeAssign":"LDP-AsgnBT-LDAPGroup"}
{"RightTable":"QERAssign","MnTable":"BaseTreeHasQERAssign","IsITShopEnabled":true,"IsReusePossible":true,"DisplayNameElement":"Assignment resources","UID_BaseTreeAssign":"QER-AsgnBT-QERAssign"}
{"RightTable":"QERResource","MnTable":"BaseTreeHasQERResource","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"Resources","UID_BaseTreeAssign":"QER-AsgnBT-QERResource"}
{"RightTable":"QERReuse","MnTable":"BaseTreeHasQERReuse","IsITShopEnabled":true,"IsReusePossible":true,"DisplayNameElement":"Multi-request resources","UID_BaseTreeAssign":"QER-AsgnBT-QERReuse"}
{"RightTable":"QERReuseUS","MnTable":"BaseTreeHasQERReuseUS","IsITShopEnabled":true,"IsReusePossible":true,"DisplayNameElement":"Multi requestable/unsubscribable resources","UID_BaseTreeAssign":"QER-AsgnBT-QERReuseUS"}
{"RightTable":"RPSReport","MnTable":"BaseTreeHasRPSReport","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"Subscribable reports","UID_BaseTreeAssign":"RPS-AsgnBT-RPSReport"}
{"RightTable":"TSBAccountDef","MnTable":"BaseTreeHasTSBAccountDef","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"Account definitions","UID_BaseTreeAssign":"TSB-AsgnBT-TSBAccountDef"}
{"RightTable":"UNSGroupB","MnTable":"BaseTreeHasUNSGroupB","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"Groups","UID_BaseTreeAssign":"TSB-AsgnBT-UNSGroupB"}
{"RightTable":"UNSGroupB1","MnTable":"BaseTreeHasUNSGroupB1","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"System entitlements 1","UID_BaseTreeAssign":"TSB-AsgnBT-UNSGroupB1"}
{"RightTable":"UNSGroupB2","MnTable":"BaseTreeHasUNSGroupB2","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"System entitlements 2","UID_BaseTreeAssign":"TSB-AsgnBT-UNSGroupB2"}
{"RightTable":"UNSGroupB3","MnTable":"BaseTreeHasUNSGroupB3","IsITShopEnabled":true,"IsReusePossible":false,"DisplayNameElement":"System entitlements 3","UID_BaseTreeAssign":"TSB-AsgnBT-UNSGroupB3"}

Interpretation (sandbox-specific): IT Shop “placement” for these types is modeled via BaseTree (shelf/product nodes) + the corresponding BaseTreeHas<type> MN table; AccProductInBaseTree is not involved in the product-node creation path.

7) `QER_PITShopProductNodeCreate_b` shows how PR nodes are built from shelf assignments (and why `AccProductInBaseTree` stays empty here)

This excerpt comes from the live DB copy of QER_PITShopProductNodeCreate_b (via sys.sql_modules) and shows the core mechanism:

derive “right table” + MN table (BaseTreeHas<type>) from BaseTreeAssign and dialog metadata,
check if the entitlement is assigned to the BO shelf via the MN table,
create a BaseTree PR node (ITShopInfo='PR') and ensure:
the MN assignment is created for the PR node,
BaseTreeHasObject is created for the PR node (ObjectKeyElement),
ITShopOrg.UID_AccProduct is updated via QBM_PJobCreate_HOUpdate.

Selected lines (line numbers are within the stored procedure definition text):

 17: ... join BaseTreeAssign ba ... and ba.IsITShopEnabled = 1 join DialogTable mb ...
 35: update #Struktur set RemoveThePRNode = sign(len(ISNULL(c.XObjectKey, ''))) ^ 1
 36: from #Struktur s join BaseTree bo ... and bo.UID_Org = @uid_orgBO and bo.ITShopInfo = 'BO'
 41: left outer join <MiddleBaseName> c on bo.UID_Org = c.UID_Org and c.<MiddleBaseFKRightName> = s.RightTablePKValue
 55: update #Struktur set CreatePRNode = 1, UID_OrgPR = NEWID() where RemoveThePRNode = 0 and UID_OrgPR is null
 93: insert into BaseTree(..., ITShopInfo, XObjectKey, ...) select ..., 'PR', dbo.QBM_FCVElementToObjectKey1(...)
 99: ... 'insert into ' + bu.UID1 + ' (UID_Org, <fk>, ... XOrigin, XIsInEffect) select s.uid_OrgPR, s.RightTablePKValue, ..., 1, 1 ...
120: insert into BaseTreeHasObject (... UID_Org, ObjectKey, InheritInfo, XObjectKey) select ..., s.ObjectKeyElement, ...
169: insert into @ElementBufferJob(UID1,UID2) select distinct s.UID_OrgPR, s.UID_ACCProduct ...
173: exec QBM_PJobCreate_HOUpdate 'ITShopOrg', ... @p1='uid_ACCProduct', @v1=@uid_accproduct ...

Key observation: there are no references to AccProductInBaseTree in this procedure path.

8) `QER_TUAccProduct` shows how product/category changes enqueue ShoppingRack updates for PR nodes

From the live DB trigger QER_TUAccProduct (excerpt):

if update(UID_PWODecisionMethod) begin
  ... select b.uid_org as uid from BaseTree b join inserted i on b.uid_accproduct = i.uid_accproduct ...
  exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackProductNode', @DBQueueElements_01
end
if update(UID_AccProductGroup) begin
  ... select b.uid_org as uid from BaseTree b join inserted i on b.uid_accproduct = i.uid_accproduct ...
  exec QBM_PDBQueueInsert_Bulk 'QER-K-ShoppingRackProductNode', @DBQueueElements_02
end

Interpretation: in this environment, BaseTree.ITShopInfo='PR' nodes + BaseTree.UID_AccProduct are the anchor points for “product node refresh”; changes to AccProduct.UID_AccProductGroup get propagated via ShoppingRack tasks rather than via AccProductInBaseTree.

AccProductInBaseTree sample rows

SELECT TOP 20 * FROM AccProductInBaseTree;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":41,"rowsAffected":null,"rowCount":0,"rows":null,"preSnapshot":null,"postSnapshot":null,"committed":null}

AccProductGroup rows (newest first)

SELECT TOP 10 UID_AccProductGroup, Ident_AccProductGroup, FullPath, XDateInserted
FROM AccProductGroup
ORDER BY XDateInserted DESC;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":71,"rowsAffected":null,"rowCount":10,"rows":[{"UID_AccProductGroup":"SBX-APG-B36C891B92E94E0DB8A579C8483B6A","Ident_AccProductGroup":"Sandbox Business Roles","FullPath":"Sandbox Business Roles","XDateInserted":"2026-04-27T00:01:24.79"},{"UID_AccProductGroup":"SBX-APG-528C6FE7956945E3B5830F6858649C","Ident_AccProductGroup":"Sandbox System Role Bundles","FullPath":"Sandbox System Role Bundles","XDateInserted":"2026-04-27T00:01:24.79"},{"UID_AccProductGroup":"SBX-APG-50DA5741B2744380A37CB4105D189B","Ident_AccProductGroup":"Sandbox Distribution Lists","FullPath":"Sandbox Distribution Lists","XDateInserted":"2026-04-27T00:01:24.79"},{"UID_AccProductGroup":"SBX-APG-18479F84C2374DD3B31FD4DDE8B45A","Ident_AccProductGroup":"Sandbox Applications","FullPath":"Sandbox Applications","XDateInserted":"2026-04-27T00:01:24.79"},{"UID_AccProductGroup":"74f39ec5-9da9-4666-b03a-b8bcade31c74","Ident_AccProductGroup":"Accounts","FullPath":"Accounts","XDateInserted":"2026-03-17T18:21:41.27"},{"UID_AccProductGroup":"ADS-C2C72EBAEC53472F971B2D53EF0051E9","Ident_AccProductGroup":"Distribution Groups","FullPath":"Active Directory Groups\\\\Distribution Groups","XDateInserted":"2025-06-27T16:00:17.54"},{"UID_AccProductGroup":"ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4","Ident_AccProductGroup":"Security Groups","FullPath":"Active Directory Groups\\\\Security Groups","XDateInserted":"2025-06-27T16:00:17.54"},{"UID_AccProductGroup":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","Ident_AccProductGroup":"Active Directory Groups","FullPath":"Active Directory Groups","XDateInserted":"2025-06-27T16:00:17.54"},{"UID_AccProductGroup":"QER-F72C69B14FD8E3459068649237F39532","Ident_AccProductGroup":"Starling Cloud Assistant","FullPath":"Access Lifecycle\\\\Starling Cloud Assistant","XDateInserted":"2025-06-27T15:59:23.653"},{"UID_AccProductGroup":"QER-f33d9f6ec3e744a3ab69a474c10f6ff4","Ident_AccProductGroup":"Access Lifecycle","FullPath":"Access Lifecycle","XDateInserted":"2025-06-27T15:59:23.653"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

AccProduct rows (newest first)

SELECT TOP 10 UID_AccProduct, Ident_AccProduct, UID_AccProductGroup, IsInActive, IsToHideFromITShop, XDateInserted
FROM AccProduct
ORDER BY XDateInserted DESC;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":48,"rowsAffected":null,"rowCount":10,"rows":[{"UID_AccProduct":"e44592f5-64e4-4c5a-ac26-bea979f662f5","Ident_AccProduct":"Standard LDAP Account","UID_AccProductGroup":"74f39ec5-9da9-4666-b03a-b8bcade31c74","IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2026-04-18T16:00:41.427"},{"UID_AccProduct":"e981f143-afa0-4a50-bd1e-bb8668188908","Ident_AccProduct":"Sandbox Domain Accounts","UID_AccProductGroup":"74f39ec5-9da9-4666-b03a-b8bcade31c74","IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2026-03-17T18:22:09.347"},{"UID_AccProduct":"ADS-FADE2DA6501F4A26AC7791D257A128DB","Ident_AccProduct":"New Active Directory security group","UID_AccProductGroup":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2025-06-27T16:00:17.567"},{"UID_AccProduct":"ADS-EFFB4A3D760248949257BC4781592E7C","Ident_AccProduct":"New Active Directory distribution group","UID_AccProductGroup":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2025-06-27T16:00:17.567"},{"UID_AccProduct":"ADS-D86AD5EC9FBD4E6A973976758A145CCA","Ident_AccProduct":"Remove memberships in system entitlements","UID_AccProductGroup":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2025-06-27T16:00:17.567"},{"UID_AccProduct":"ADS-B419930348664A3E9298268446F22D45","Ident_AccProduct":"Active Directory user account home storage space expansion","UID_AccProductGroup":null,"IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2025-06-27T16:00:17.567"},{"UID_AccProduct":"ADS-A261D1D7E0D04060899D679C002A7543","Ident_AccProduct":"Delete Active Directory group","UID_AccProductGroup":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2025-06-27T16:00:17.567"},{"UID_AccProduct":"ADS-81F10A79075441E8B47EEC91D04E01D4","Ident_AccProduct":"Modify Active Directory group","UID_AccProductGroup":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2025-06-27T16:00:17.567"},{"UID_AccProduct":"RMS-79b6410a215f48aa8648cd20bf1d4ed1","Ident_AccProduct":"System role entitlement assignment","UID_AccProductGroup":null,"IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2025-06-27T15:59:44.877"},{"UID_AccProduct":"QER-49D549178FF441228884C3F0754A8DE1","Ident_AccProduct":"Role entitlement assignment","UID_AccProductGroup":null,"IsInActive":false,"IsToHideFromITShop":false,"XDateInserted":"2025-06-27T15:59:21.793"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

BaseTree PR nodes with UID_AccProduct (ITShopOrg root)

SELECT TOP 10 UID_Org, Ident_Org, UID_AccProduct, UID_ParentOrg, XDateInserted
FROM BaseTree
WHERE UID_OrgRoot='QER-V-ITShopOrg' AND ITShopInfo='PR'
ORDER BY XDateInserted DESC;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":85,"rowsAffected":null,"rowCount":10,"rows":[{"UID_Org":"F5888005-B84A-4FC3-A76E-21212250765B","Ident_Org":"Standard LDAP Account","UID_AccProduct":"e44592f5-64e4-4c5a-ac26-bea979f662f5","UID_ParentOrg":"QER-3c05f166d3224c4aa3f069913bc36c1d","XDateInserted":"2026-04-18T17:34:11.247"},{"UID_Org":"3A812A6C-2664-4875-967D-FDF75383402E","Ident_Org":"Sandbox Domain Accounts","UID_AccProduct":"e981f143-afa0-4a50-bd1e-bb8668188908","UID_ParentOrg":"QER-3c05f166d3224c4aa3f069913bc36c1d","XDateInserted":"2026-03-17T18:23:53.963"},{"UID_Org":"ADS-ITSHOPORG-ADS-NEWGROUP-SG","Ident_Org":"New Active Directory security group","UID_AccProduct":"ADS-FADE2DA6501F4A26AC7791D257A128DB","UID_ParentOrg":"ADS-ITSHOPORG-GROUPS-BO","XDateInserted":"2025-06-27T16:00:17.493"},{"UID_Org":"ADS-ITSHOPORG-ADS-NEWGROUP-DL","Ident_Org":"New Active Directory distribution group","UID_AccProduct":"ADS-EFFB4A3D760248949257BC4781592E7C","UID_ParentOrg":"ADS-ITSHOPORG-GROUPS-BO","XDateInserted":"2025-06-27T16:00:17.493"},{"UID_Org":"ADS-ITSHOPORG-ADS-GROUPDELETE","Ident_Org":"Delete Active Directory group","UID_AccProduct":"ADS-A261D1D7E0D04060899D679C002A7543","UID_ParentOrg":"ADS-ITSHOPORG-GROUPS-BO","XDateInserted":"2025-06-27T16:00:17.493"},{"UID_Org":"ADS-ITSHOPORG-ADS-GROUPCHANGE","Ident_Org":"Modify Active Directory group","UID_AccProduct":"ADS-81F10A79075441E8B47EEC91D04E01D4","UID_ParentOrg":"ADS-ITSHOPORG-GROUPS-BO","XDateInserted":"2025-06-27T16:00:17.493"},{"UID_Org":"RMS-ED373920E29A6B40B8198B587C6DAA1A","Ident_Org":"System role entitlement assignment","UID_AccProduct":"RMS-79b6410a215f48aa8648cd20bf1d4ed1","UID_ParentOrg":"QER-ITSHOPORG-DELEGATION-BO","XDateInserted":"2025-06-27T15:59:45.707"},{"UID_Org":"QER-ITSHOPORG-DELEGATION-MEMBER-PR","Ident_Org":"Role membership","UID_AccProduct":"QER-E4C02D1588604613B2E3743B65BF5453","UID_ParentOrg":"QER-ITSHOPORG-DELEGATION-BO","XDateInserted":"2025-06-27T15:59:23.757"},{"UID_Org":"QER-ITSHOPORG-DELEGATION-ASSIGN-PR","Ident_Org":"Role entitlement assignment","UID_AccProduct":"QER-49D549178FF441228884C3F0754A8DE1","UID_ParentOrg":"QER-ITSHOPORG-DELEGATION-BO","XDateInserted":"2025-06-27T15:59:23.757"},{"UID_Org":"QER-7B26B28F2EFE4989A20B0A9D1A01C108","Ident_Org":"New Starling Cloud Assistant recipient","UID_AccProduct":"QER-3072C3AEF9124F4CBF0B2F67F47B82F5","UID_ParentOrg":"QER-3c05f166d3224c4aa3f069913bc36c1d","XDateInserted":"2025-06-27T15:59:23.757"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

BaseTreeHasObject rows for IT Shop product nodes

SELECT TOP 20 h.UID_Org, h.ObjectKey, h.InheritInfo
FROM BaseTreeHasObject h
JOIN BaseTree b ON b.UID_Org=h.UID_Org
WHERE b.UID_OrgRoot='QER-V-ITShopOrg' AND b.ITShopInfo='PR'
ORDER BY b.XDateInserted DESC;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":61,"rowsAffected":null,"rowCount":16,"rows":[{"UID_Org":"F5888005-B84A-4FC3-A76E-21212250765B","ObjectKey":"<Key><T>TSBAccountDef</T><P>d86a904f-a333-4443-9889-1ef04711506d</P></Key>","InheritInfo":1},{"UID_Org":"3A812A6C-2664-4875-967D-FDF75383402E","ObjectKey":"<Key><T>TSBAccountDef</T><P>a413090a-8e32-41a1-9232-7ab700cd7bff</P></Key>","InheritInfo":1},{"UID_Org":"ADS-ITSHOPORG-ADS-GROUPCHANGE","ObjectKey":"<Key><T>QERReuse</T><P>ADS-89C1EA19AA924AE88AEDE241DBD36851</P></Key>","InheritInfo":1},{"UID_Org":"ADS-ITSHOPORG-ADS-GROUPDELETE","ObjectKey":"<Key><T>QERReuse</T><P>ADS-26A1C136D18F4A91A4737C97C40931DF</P></Key>","InheritInfo":1},{"UID_Org":"ADS-ITSHOPORG-ADS-NEWGROUP-DL","ObjectKey":"<Key><T>QERReuse</T><P>ADS-57174D748E554AA9B3F7E0F2E0C62542</P></Key>","InheritInfo":1},{"UID_Org":"ADS-ITSHOPORG-ADS-NEWGROUP-SG","ObjectKey":"<Key><T>QERReuse</T><P>ADS-AAA8432349E04BD0BFD40AC4482B3EB7</P></Key>","InheritInfo":1},{"UID_Org":"RMS-ED373920E29A6B40B8198B587C6DAA1A","ObjectKey":"<Key><T>QERAssign</T><P>RMS-277bdcca8c9b446e84d5f5fbcddf81b5</P></Key>","InheritInfo":1},{"UID_Org":"QER-320A1570F5B84819A361FF18B159A6D1","ObjectKey":"<Key><T>QERResource</T><P>QER-3144255E3331493B87BB3E890AE09D7F</P></Key>","InheritInfo":1},{"UID_Org":"QER-6DF4E012563B402891CB37A0A86915E6","ObjectKey":"<Key><T>QERResource</T><P>QER-41F8B1D1CE68F64481520298F9BF94D6</P></Key>","InheritInfo":1},{"UID_Org":"QER-7B26B28F2EFE4989A20B0A9D1A01C108","ObjectKey":"<Key><T>QERResource</T><P>QER-84F21C97B0704E318DD524C99F7C4EEA</P></Key>","InheritInfo":1},{"UID_Org":"QER-ITSHOPORG-DELEGATION-ASSIGN-PR","ObjectKey":"<Key><T>QERAssign</T><P>QER-A9CDE494426E4CEC9654C95D974755F1</P></Key>","InheritInfo":1},{"UID_Org":"QER-ITSHOPORG-DELEGATION-MEMBER-PR","ObjectKey":"<Key><T>QERAssign</T><P>QER-8B72EB66A8F945F39BBC7E2ABE0718D5</P></Key>","InheritInfo":1},{"UID_Org":"QER-ITSHOPORG-DELEGATION-NEWMGR","ObjectKey":"<Key><T>QERReuse</T><P>QER-EA30315BD59040198212CF5C5E972885</P></Key>","InheritInfo":1},{"UID_Org":"QER-ITSHOPORG-DELEGATION-PR","ObjectKey":"<Key><T>QERAssign</T><P>QER-297CC8DAD20F4E10A79D6FB20820C22F</P></Key>","InheritInfo":1},{"UID_Org":"QER-ITSHOPORG-ROLE-CHALLENGE-PR","ObjectKey":"<Key><T>QERAssign</T><P>QER-6C7A73575A6141408406C91C80C10070</P></Key>","InheritInfo":1},{"UID_Org":"QER-ITSHOPORG-SUBSTITUTE-PR","ObjectKey":"<Key><T>QERReuseUS</T><P>QER-12345678900F4E10A79D6FB20820C22F</P></Key>","InheritInfo":1}],"preSnapshot":null,"postSnapshot":null,"committed":null}

Join: product nodes -> AccProduct -> BaseTreeHasObject

SELECT TOP 10 b.Ident_Org, p.Ident_AccProduct, g.FullPath AS AccProductGroupFullPath, h.ObjectKey AS PlacementObjectKey
FROM BaseTree b
LEFT JOIN AccProduct p ON p.UID_AccProduct=b.UID_AccProduct
LEFT JOIN AccProductGroup g ON g.UID_AccProductGroup=p.UID_AccProductGroup
LEFT JOIN BaseTreeHasObject h ON h.UID_Org=b.UID_Org
WHERE b.UID_OrgRoot='QER-V-ITShopOrg' AND b.ITShopInfo='PR'
ORDER BY b.XDateInserted DESC;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":48,"rowsAffected":null,"rowCount":10,"rows":[{"Ident_Org":"Standard LDAP Account","Ident_AccProduct":"Standard LDAP Account","AccProductGroupFullPath":"Accounts","PlacementObjectKey":"<Key><T>TSBAccountDef</T><P>d86a904f-a333-4443-9889-1ef04711506d</P></Key>"},{"Ident_Org":"Sandbox Domain Accounts","Ident_AccProduct":"Sandbox Domain Accounts","AccProductGroupFullPath":"Accounts","PlacementObjectKey":"<Key><T>TSBAccountDef</T><P>a413090a-8e32-41a1-9232-7ab700cd7bff</P></Key>"},{"Ident_Org":"Modify Active Directory group","Ident_AccProduct":"Modify Active Directory group","AccProductGroupFullPath":"Active Directory Groups","PlacementObjectKey":"<Key><T>QERReuse</T><P>ADS-89C1EA19AA924AE88AEDE241DBD36851</P></Key>"},{"Ident_Org":"Delete Active Directory group","Ident_AccProduct":"Delete Active Directory group","AccProductGroupFullPath":"Active Directory Groups","PlacementObjectKey":"<Key><T>QERReuse</T><P>ADS-26A1C136D18F4A91A4737C97C40931DF</P></Key>"},{"Ident_Org":"New Active Directory distribution group","Ident_AccProduct":"New Active Directory distribution group","AccProductGroupFullPath":"Active Directory Groups","PlacementObjectKey":"<Key><T>QERReuse</T><P>ADS-57174D748E554AA9B3F7E0F2E0C62542</P></Key>"},{"Ident_Org":"New Active Directory security group","Ident_AccProduct":"New Active Directory security group","AccProductGroupFullPath":"Active Directory Groups","PlacementObjectKey":"<Key><T>QERReuse</T><P>ADS-AAA8432349E04BD0BFD40AC4482B3EB7</P></Key>"},{"Ident_Org":"System role entitlement assignment","Ident_AccProduct":"System role entitlement assignment","AccProductGroupFullPath":null,"PlacementObjectKey":"<Key><T>QERAssign</T><P>RMS-277bdcca8c9b446e84d5f5fbcddf81b5</P></Key>"},{"Ident_Org":"Add Slack channel to recipient","Ident_AccProduct":"Add Slack channel to recipient","AccProductGroupFullPath":"Access Lifecycle\\\\Starling Cloud Assistant","PlacementObjectKey":"<Key><T>QERResource</T><P>QER-3144255E3331493B87BB3E890AE09D7F</P></Key>"},{"Ident_Org":"Add Teams channel to recipient","Ident_AccProduct":"Add Teams channel to recipient","AccProductGroupFullPath":"Access Lifecycle\\\\Starling Cloud Assistant","PlacementObjectKey":"<Key><T>QERResource</T><P>QER-41F8B1D1CE68F64481520298F9BF94D6</P></Key>"},{"Ident_Org":"New Starling Cloud Assistant recipient","Ident_AccProduct":"New Starling Cloud Assistant recipient","AccProductGroupFullPath":"Access Lifecycle\\\\Starling Cloud Assistant","PlacementObjectKey":"<Key><T>QERResource</T><P>QER-84F21C97B0704E318DD524C99F7C4EEA</P></Key>"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

9) `AccProduct.IsToHideFromITShop` is currently unused in this sandbox (2026-04-27 run 7)

SELECT IsToHideFromITShop, COUNT(*) AS Cnt
FROM AccProduct
GROUP BY IsToHideFromITShop
ORDER BY IsToHideFromITShop;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":59,"rowsAffected":null,"rowCount":1,"rows":{"IsToHideFromITShop":false,"Cnt":20},"preSnapshot":null,"postSnapshot":null,"committed":null}

DB-side note: in this sandbox, QER_TUAccProduct (trigger) does not mention IsToHideFromITShop (so no obvious DBQueue refresh on hide-flag changes).

10) Service categories (`AccProductGroup`) are not required for PR nodes (2026-04-27 run 7)

SELECT
  SUM(CASE WHEN a.IsToHideFromITShop = 1 THEN 1 ELSE 0 END) AS HiddenProductNodes,
  SUM(CASE WHEN a.UID_AccProductGroup IS NULL THEN 1 ELSE 0 END) AS ProductNodesWithoutCategory,
  COUNT(*) AS TotalProductNodes
FROM BaseTree b
JOIN AccProduct a ON a.UID_AccProduct = b.UID_AccProduct
WHERE b.UID_OrgRoot = 'QER-V-ITShopOrg'
  AND b.ITShopInfo = 'PR';

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":43,"rowsAffected":null,"rowCount":1,"rows":{"HiddenProductNodes":0,"ProductNodesWithoutCategory":7,"TotalProductNodes":16},"preSnapshot":null,"postSnapshot":null,"committed":null}

11) AutoPublish ADSGroup config parameters live in `DialogConfigParm` (2026-04-27 run 7)

SELECT TOP 10 FullPath, Value, Enabled, IsCrypted, DisplayName, UID_ParentConfigparm
FROM DialogConfigParm
WHERE FullPath LIKE 'QER\ITShop\AutoPublish\ADSGroup%'
ORDER BY FullPath;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":43,"rowsAffected":null,"rowCount":3,"rows":[{"FullPath":"QER\\ITShop\\AutoPublish\\ADSGroup","Value":"1","Enabled":false,"IsCrypted":false,"DisplayName":"Active Directory groups","UID_ParentConfigparm":"QER-50F919687701574E9C828808EF5608D8"},{"FullPath":"QER\\ITShop\\AutoPublish\\ADSGroup\\AutoFillDisplayName","Value":"1","Enabled":false,"IsCrypted":false,"DisplayName":null,"UID_ParentConfigparm":"ADS-49E68B01EF4EBD4C8686955F11DE1DC2"},{"FullPath":"QER\\ITShop\\AutoPublish\\ADSGroup\\ExcludeList","Value":".*Administrator.*|Exchange.*|.*Admins|.*Operators|IIS_IUSRS","Enabled":true,"IsCrypted":false,"DisplayName":"Exclude list for Active Directory groups","UID_ParentConfigparm":"ADS-49E68B01EF4EBD4C8686955F11DE1DC2"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

SELECT FullPath, Value, Enabled
FROM DialogConfigParm
WHERE FullPath IN ('QER\ITShop\AutoPublish','QER\ITShop','QER')
ORDER BY FullPath;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":42,"rowsAffected":null,"rowCount":3,"rows":[{"FullPath":"QER","Value":"1","Enabled":true},{"FullPath":"QER\\ITShop","Value":"1","Enabled":true},{"FullPath":"QER\\ITShop\\AutoPublish","Value":"1","Enabled":true}],"preSnapshot":null,"postSnapshot":null,"committed":null}

12) Service-category closure + ShoppingRack wiring (2026-04-27 run 8)

AccProductGroup category hierarchy is maintained via a closure/collection table and is wired to ShoppingRack refreshes through triggers and DBQueue task definitions.

AccProductGroupCollection = closure/collection table

SELECT c.column_id, c.name, c.is_nullable
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('AccProductGroupCollection')
ORDER BY c.column_id;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":70,"rowsAffected":null,"rowCount":4,"rows":[{"column_id":1,"name":"UID_AccProductGroupParent","is_nullable":false},{"column_id":2,"name":"LevelNumber","is_nullable":true},{"column_id":3,"name":"UID_AccProductGroupChild","is_nullable":false},{"column_id":4,"name":"XObjectKey","is_nullable":false}],"preSnapshot":null,"postSnapshot":null,"committed":null}

SELECT TOP 5 * FROM AccProductGroupCollection;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":45,"rowsAffected":null,"rowCount":5,"rows":[{"UID_AccProductGroupParent":"74f39ec5-9da9-4666-b03a-b8bcade31c74","LevelNumber":0,"UID_AccProductGroupChild":"74f39ec5-9da9-4666-b03a-b8bcade31c74","XObjectKey":"<Key><T>AccProductGroupCollection</T><P>74f39ec5-9da9-4666-b03a-b8bcade31c74</P><P>74f39ec5-9da9-4666-b03a-b8bcade31c74</P></Key>"},{"UID_AccProductGroupParent":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","LevelNumber":0,"UID_AccProductGroupChild":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","XObjectKey":"<Key><T>AccProductGroupCollection</T><P>ADS-55BA7CB5B5D14166A016C8F993CD0BDC</P><P>ADS-55BA7CB5B5D14166A016C8F993CD0BDC</P></Key>"},{"UID_AccProductGroupParent":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","LevelNumber":1,"UID_AccProductGroupChild":"ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4","XObjectKey":"<Key><T>AccProductGroupCollection</T><P>ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4</P><P>ADS-55BA7CB5B5D14166A016C8F993CD0BDC</P></Key>"},{"UID_AccProductGroupParent":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","LevelNumber":1,"UID_AccProductGroupChild":"ADS-C2C72EBAEC53472F971B2D53EF0051E9","XObjectKey":"<Key><T>AccProductGroupCollection</T><P>ADS-C2C72EBAEC53472F971B2D53EF0051E9</P><P>ADS-55BA7CB5B5D14166A016C8F993CD0BDC</P></Key>"},{"UID_AccProductGroupParent":"ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4","LevelNumber":0,"UID_AccProductGroupChild":"ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4","XObjectKey":"<Key><T>AccProductGroupCollection</T><P>ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4</P><P>ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4</P></Key>"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

QER_VACCProductGroupCollection = category-to-ancestor mapping view

SELECT c.column_id, c.name
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('QER_VACCProductGroupCollection')
ORDER BY c.column_id;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":41,"rowsAffected":null,"rowCount":3,"rows":[{"column_id":1,"name":"UID_ACCProductGroup"},{"column_id":2,"name":"UID_AccProductGroupParent"},{"column_id":3,"name":"CountSteps"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

SELECT TOP 10 * FROM QER_VACCProductGroupCollection;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":43,"rowsAffected":null,"rowCount":10,"rows":[{"UID_ACCProductGroup":"74f39ec5-9da9-4666-b03a-b8bcade31c74","UID_AccProductGroupParent":"74f39ec5-9da9-4666-b03a-b8bcade31c74","CountSteps":0},{"UID_ACCProductGroup":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","UID_AccProductGroupParent":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","CountSteps":0},{"UID_ACCProductGroup":"ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4","UID_AccProductGroupParent":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","CountSteps":1},{"UID_ACCProductGroup":"ADS-C2C72EBAEC53472F971B2D53EF0051E9","UID_AccProductGroupParent":"ADS-55BA7CB5B5D14166A016C8F993CD0BDC","CountSteps":1},{"UID_ACCProductGroup":"ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4","UID_AccProductGroupParent":"ADS-9D94E9834FFD4224A6F29EFD9D6EA5C4","CountSteps":0},{"UID_ACCProductGroup":"ADS-C2C72EBAEC53472F971B2D53EF0051E9","UID_AccProductGroupParent":"ADS-C2C72EBAEC53472F971B2D53EF0051E9","CountSteps":0},{"UID_ACCProductGroup":"QER-f33d9f6ec3e744a3ab69a474c10f6ff4","UID_AccProductGroupParent":"QER-f33d9f6ec3e744a3ab69a474c10f6ff4","CountSteps":0},{"UID_ACCProductGroup":"QER-F72C69B14FD8E3459068649237F39532","UID_AccProductGroupParent":"QER-f33d9f6ec3e744a3ab69a474c10f6ff4","CountSteps":1},{"UID_ACCProductGroup":"QER-F72C69B14FD8E3459068649237F39532","UID_AccProductGroupParent":"QER-F72C69B14FD8E3459068649237F39532","CountSteps":0},{"UID_ACCProductGroup":"SBX-APG-18479F84C2374DD3B31FD4DDE8B45A","UID_AccProductGroupParent":"SBX-APG-18479F84C2374DD3B31FD4DDE8B45A","CountSteps":0}],"preSnapshot":null,"postSnapshot":null,"committed":null}

DBQueue task definitions relevant to category/PR refresh

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

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":82,"rowsAffected":null,"rowCount":22,"rows":[{"UID_Task":"CPL-K-ShoppingRackMakeDecisionCR","ProcedureName":"CPL_ZITShopMakeDecisionCR"},{"UID_Task":"CPL-K-ShoppingRackMakeDecisionOC","ProcedureName":"CPL_ZITShopMakeDecisionOC"},{"UID_Task":"CPL-K-ShoppingRackMakeDecisionOH","ProcedureName":"CPL_ZITShopMakeDecisionOH"},{"UID_Task":"QER-K-AccProductGroupCollection","ProcedureName":"QER_ZAccProductGroupCollection"},{"UID_Task":"QER-K-ShoppingRack-All","ProcedureName":"QER_ZITShopCheckStructure"},{"UID_Task":"QER-K-ShoppingRackCheckDecision","ProcedureName":"QER_ZITShopCheckDecision"},{"UID_Task":"QER-K-ShoppingRackCheckValid","ProcedureName":"QER_ZITShopCheckValid"},{"UID_Task":"QER-K-ShoppingRackCheckWaiting","ProcedureName":"QER_ZITShopCheckWaiting"},{"UID_Task":"QER-K-ShoppingRackMakeDecisionCD","ProcedureName":"QER_ZITShopMakeDecisionCD"},{"UID_Task":"QER-K-ShoppingRackMakeDecisionEX","ProcedureName":"QER_ZITShopMakeDecisionEX"},{"UID_Task":"QER-K-ShoppingRackMakeDecisionRI","ProcedureName":"QER_ZITShopMakeDecisionRI"},{"UID_Task":"QER-K-ShoppingRackMakeDecisionSB","ProcedureName":"QER_ZITShopMakeDecisionSB"},{"UID_Task":"QER-K-ShoppingRackMakeDecisionWC","ProcedureName":"QER_ZITShopMakeDecisionWC"},{"UID_Task":"QER-K-ShoppingRackMemberDel","ProcedureName":"QER_ZITShopMemberDelete"},{"UID_Task":"QER-K-ShoppingRackMethod","ProcedureName":"QER_ZITShopCheckMethodBO"},{"UID_Task":"QER-K-ShoppingRackPersonHasObject","ProcedureName":"QER_ZITShopPersonHasObjectFill"},{"UID_Task":"QER-K-ShoppingRackProductNode","ProcedureName":"QER_ZITShopCheckMethodPR"},{"UID_Task":"QER-K-ShoppingRackPWODecisionMaker","ProcedureName":"QER_ZITShopDecisionMakerFill"},{"UID_Task":"QER-K-ShoppingRackPWOHelperPWO","ProcedureName":"QER_ZITShopHelperFill"},{"UID_Task":"QER-K-ShoppingRackPWOHelperPWO-Cfg","ProcedureName":"QER_ZITShopHelperFill_Cfg"},{"UID_Task":"QER-K-ShoppingRackPWOHelperPWO-Del","ProcedureName":"QER_ZITShopHelperFill_Delete"},{"UID_Task":"QER-K-ShoppingRackPWOHelperPWO-Rec","ProcedureName":"QER_ZITShopHelperFill_Recalc"}],"preSnapshot":null,"postSnapshot":null,"committed":null}

Task details + dependencies for PR node refresh

The PR-node refresh task carries a built-in “recalc query” and has explicit dependencies that show the expected troubleshooting order.

SELECT UID_Task, ProcedureName, IsBulkEnabled, CountParameter, QueryForRecalculate
FROM dbo.QBMDBQueueTask
WHERE UID_Task = 'QER-K-ShoppingRackProductNode';

Observed:

ProcedureName = QER_ZITShopCheckMethodPR
QueryForRecalculate = select uid_itshoporg from itshoporg where itshopinfo = N'PR'

SELECT UID_TaskPredecessor, UID_TaskFollower, IsPhysicalDependency
FROM dbo.QBMDBQueueTaskDepend
WHERE UID_TaskPredecessor = 'QER-K-ShoppingRackProductNode'
   OR UID_TaskFollower = 'QER-K-ShoppingRackProductNode'
ORDER BY UID_TaskPredecessor, UID_TaskFollower;

Observed (key edges):

QER-K-AccProductGroupCollection → QER-K-ShoppingRackProductNode (physical)
QER-K-ShoppingRackMethod → QER-K-ShoppingRackProductNode (physical)
QER-K-ShoppingRackProductNode → QER-K-ITSHOPOrder-ABORT (physical)

13) `ITShopInfo` codes `BG`/`BT` are not used in this sandbox (2026-04-27 run 8)

The MDK contains module-specific procedures referencing ITShopInfo IN ('BO','PR','BG','BT'), but the live sandbox DB currently has no BaseTree rows with ITShopInfo BG/BT.

SELECT ITShopInfo, UID_OrgRoot, COUNT(*) AS C
FROM dbo.BaseTree
WHERE ITShopInfo IN ('BG','BT')
GROUP BY ITShopInfo, UID_OrgRoot
ORDER BY ITShopInfo, C DESC;

{"_v":1,"database":"OIM","dbName":"OneIM","connectionMode":"Local","kind":"reader","durationMs":50,"rowsAffected":null,"rowCount":0,"rows":null,"preSnapshot":null,"postSnapshot":null,"committed":null}

Sandbox DB Evidence — IT Shop Availability (2026-04-27)

Method

Evidence

1) Relevant DB objects (type)

2) Counts: mapping table empty, but PR nodes exist

3) View definition: AccProductInITShopOrg reads AccProductInBaseTree

4) PR nodes in BaseTree carry UID_AccProduct (top 10)

5) IT Shop flags appear on common entitlement tables

AccProductInBaseTree object type

AccProductInBaseTree definition (if view)

Tables/views containing 'AccProduct'

AccProduct key columns (name-like filters)

AccProductGroup key columns (name-like filters)

BaseTree columns related to IT Shop/product placement

BaseTreeHasObject columns

AccProductInBaseTree columns

AccProductInBaseTree row count

6) BaseTreeAssign shows which entitlement types can be placed in IT Shop (and via which BaseTreeHas<type> table)

7) QER_PITShopProductNodeCreate_b shows how PR nodes are built from shelf assignments (and why AccProductInBaseTree stays empty here)

8) QER_TUAccProduct shows how product/category changes enqueue ShoppingRack updates for PR nodes

AccProductInBaseTree sample rows

AccProductGroup rows (newest first)

AccProduct rows (newest first)

BaseTree PR nodes with UID_AccProduct (ITShopOrg root)

BaseTreeHasObject rows for IT Shop product nodes

Join: product nodes -> AccProduct -> BaseTreeHasObject

9) AccProduct.IsToHideFromITShop is currently unused in this sandbox (2026-04-27 run 7)

10) Service categories (AccProductGroup) are not required for PR nodes (2026-04-27 run 7)

11) AutoPublish ADSGroup config parameters live in DialogConfigParm (2026-04-27 run 7)

12) Service-category closure + ShoppingRack wiring (2026-04-27 run 8)

AccProductGroupCollection = closure/collection table

QER_VACCProductGroupCollection = category-to-ancestor mapping view

DBQueue task definitions relevant to category/PR refresh

Task details + dependencies for PR node refresh

13) ITShopInfo codes BG/BT are not used in this sandbox (2026-04-27 run 8)

3) View definition: `AccProductInITShopOrg` reads `AccProductInBaseTree`

4) PR nodes in `BaseTree` carry `UID_AccProduct` (top 10)

6) `BaseTreeAssign` shows which entitlement types can be placed in IT Shop (and via which `BaseTreeHas<type>` table)

7) `QER_PITShopProductNodeCreate_b` shows how PR nodes are built from shelf assignments (and why `AccProductInBaseTree` stays empty here)

8) `QER_TUAccProduct` shows how product/category changes enqueue ShoppingRack updates for PR nodes

9) `AccProduct.IsToHideFromITShop` is currently unused in this sandbox (2026-04-27 run 7)

10) Service categories (`AccProductGroup`) are not required for PR nodes (2026-04-27 run 7)

11) AutoPublish ADSGroup config parameters live in `DialogConfigParm` (2026-04-27 run 7)

13) `ITShopInfo` codes `BG`/`BT` are not used in this sandbox (2026-04-27 run 8)