Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-it-shop-enabled-types-and-availability-joins-db-evidence.md

> Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-it-shop-enabled-types-and-availability-joins-db-evidence.md

Sandbox DB Evidence — IT Shop enabled types + availability joins (2026-04-27)

Goal: capture which entitlement/resource types are “IT Shop enabled”, and the concrete DB join path that makes an entitlement show up as a requestable product in the IT Shop tree.

Method

• Queries executed via scripts/sandbox/Invoke-SandboxSql.ps1 against the sandbox OIM database (OneIM).
• SELECT-only (no DML).

Evidence

1) BaseTreeAssign: IT-Shop-enabled element types (live)

Schema (relevant columns):

• IsITShopEnabled (bit)
• UID_DialogTableElement → element table (e.g., ADSGroup)
• UID_DialogTableMN → “assignment table” (e.g., BaseTreeHasADSGroup)
• XObjectKey identifies the assignment definition

Live inventory (where IsITShopEnabled=1):

Raw query:

SELECT
  dte.TableName  AS ElementTable,
  dtmn.TableName AS MnTable,
  bta.IsITShopEnabled,
  bta.IsEsetEnabled,
  bta.IsReusePossible,
  bta.IsReusePossibleUS,
  bta.DelegationType,
  bta.XObjectKey
FROM BaseTreeAssign bta
LEFT JOIN DialogTable dte  ON dte.UID_DialogTable  = bta.UID_DialogTableElement
LEFT JOIN DialogTable dtmn ON dtmn.UID_DialogTable = bta.UID_DialogTableMN
WHERE bta.IsITShopEnabled = 1
ORDER BY dte.TableName, dtmn.TableName;

2) “Direct entitlement” pattern: IsForITShop + IsITShopOnly + UID_AccProduct (schema)

From sys.objects/sys.columns inventory, the following element tables in this sandbox have all three of:

• IsForITShop
• IsITShopOnly
• UID_AccProduct (service item link)

ADSGroup, ESet, LDAPGroup, QERAssign, QERResource, QERReuse, QERReuseUS, RPSReport, TSBAccountDef, UNSGroupB, UNSGroupB1, UNSGroupB2, UNSGroupB3.

This is the most concrete “DB-level checklist” for an entitlement being requestable:

1) ElementTable.IsForITShop=1

2) ElementTable.UID_AccProduct IS NOT NULL

3) BaseTreeHas<ElementTable> rows exist in the IT Shop subtree (both board and product node assignments)

3) Current sandbox counts: requestable elements and placement (live)

Counts per element table (Total, IsForITShop=1, UID_AccProduct IS NOT NULL):

Placement in the IT Shop subtree is table-backed:

• BaseTreeHas<Element> tables contain assignments to BaseTree.UID_Org.
• Views ITShopOrgHas<Element> are just filters (“node exists in BaseTree with UID_OrgRoot='QER-V-ITShopOrg'”).

For requestable elements in this sandbox, BaseTreeHas<Element> has exactly the expected BO + PR assignment split:

• BaseTreeHasADSGroup in IT Shop: BO=97, PR=97
• BaseTreeHasQERAssign in IT Shop: BO=5, PR=5
• BaseTreeHasQERResource in IT Shop: BO=3, PR=3
• BaseTreeHasTSBAccountDef in IT Shop: BO=2, PR=2

Implication: “availability” is not only IsForITShop; it is also a concrete pair of assignments:

• Board (BO shelf): where it appears in the catalog
• Product node (PR): the requestable node tied to AccProduct (BaseTree.UID_AccProduct)

4) ITShopOrgHasUNSGroup is a union/compatibility view (definition excerpt)

In this sandbox, ITShopOrgHasUNSGroup is defined as a UNION ALL over multiple “group types”, including re-mapping ITShopOrgHasADSGroup to UID_UNSGroup plus a fixed namespace identifier:

-- part from ITShopOrgHasUNSGroup : ITShopOrgHasADSGroup
select
  RiskIndexCalculated as RiskIndexCalculated,
  UID_ADSGroup        as UID_UNSGroup,
  UID_ITShopOrg       as UID_ITShopOrg,
  ...
  'ADS-DPRNameSpace-ADS' as UID_DPRNameSpace
from ITShopOrgHasADSGroup

This matters for troubleshooting because you might see “UNSGroup” joins in shipped logic even when you are actually dealing with ADSGroup or LDAPGroup.

5) AccProductInBaseTree is present but empty (live)

• AccProductInBaseTree exists as a table (UID_Org, UID_AccProduct, …) but currently has 0 rows in this sandbox.
• In contrast, the product nodes are visible directly in BaseTree (ITShopInfo='PR' + UID_AccProduct set).

Open question (host artifact scan): which shipped proc/DBQueue task is supposed to fill AccProductInBaseTree, and under which conditions.