Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-eset-it-shop-placement-and-flag-enforcement-db-evidence.md

> Source: projects/identity-management/oim-kb-update/sandbox-db/2026-04-27-eset-it-shop-placement-and-flag-enforcement-db-evidence.md

Goal

Capture how system roles (ESet) are wired into IT Shop availability in this sandbox at the database layer:

• Which tables/views represent placement of system roles in the IT Shop tree
• Which flags exist on ESet (IT Shop enablement + “IT Shop only”)
• Which trigger logic enforces consistency when changing those flags

This is intended as troubleshooting evidence (why an ESet is not requestable / why a flag update fails).

Findings (summary)

• ESet is a direct entitlement table with IsForITShop + IsITShopOnly flags and an UID_AccProduct service-item link.
• Placement of an ESet into the IT Shop is modeled via BaseTreeHasESet (and the filtered view ITShopOrgHasESet for entries inside the IT Shop subtree).
• Trigger RMS_TUESet enforces IT Shop flag consistency by calling QER_PIsForITShopFlagCheck 'ESet', ... and running additional checks involving ESetHasEntitlement + dynamic reference metadata (DialogValidDynamicRef).
• In this sandbox snapshot, ESet currently has 0 rows, so this note is schema/trigger evidence only (no live row examples yet).

Evidence

1) ESet has IT Shop flags + UID_AccProduct

Observed column list (subset relevant to IT Shop):

• IsForITShop
• IsITShopOnly
• UID_AccProduct

Query:

SELECT c.column_id, c.name AS ColumnName
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.ESet')
ORDER BY c.column_id;

Snapshot (counts):

SELECT
  SUM(CASE WHEN IsForITShop = 1 THEN 1 ELSE 0 END) AS ESet_IsForITShop_True,
  SUM(CASE WHEN IsITShopOnly = 1 THEN 1 ELSE 0 END) AS ESet_IsITShopOnly_True,
  COUNT(*) AS ESet_Total
FROM ESet;

Result (2026-04-27):

• ESet_Total = 0

2) Placement table: BaseTreeHasESet

The IT Shop placement link table for system roles is BaseTreeHasESet.

Columns:

UID_Org
UID_ESet
XTouched
XObjectKey
RiskIndexCalculated
XMarkedForDeletion
XOrigin
XDateInserted
XDateUpdated
XUserInserted
XUserUpdated
XIsInEffect

Query:

SELECT c.column_id, c.name AS ColumnName
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('dbo.BaseTreeHasESet')
ORDER BY c.column_id;

3) IT Shop filtered view: ITShopOrgHasESet

ITShopOrgHasESet is a thin filter over BaseTreeHasESet that keeps only rows whose UID_Org is inside the IT Shop subtree (BaseTree.UID_OrgRoot = 'QER-V-ITShopOrg').

Definition (excerpted from sys.sql_modules):

create   view dbo.ITShopOrgHasESet as
 select
    RiskIndexCalculated as RiskIndexCalculated,
    UID_ESet as UID_ESet,
    UID_Org as UID_ITShopOrg,
    XDateInserted as XDateInserted,
    XDateUpdated as XDateUpdated,
    XIsInEffect as XIsInEffect,
    XMarkedForDeletion as XMarkedForDeletion,
    XObjectKey as XObjectKey,
    XOrigin as XOrigin,
    XTouched as XTouched,
    XUserInserted as XUserInserted,
    XUserUpdated as XUserUpdated
 from BaseTreeHasESet
 where exists (
   select top 1 1
   from BaseTree y
   where y.UID_Org = BaseTreeHasESet.UID_Org
     and y.UID_OrgRoot = 'QER-V-ITShopOrg'
 )

4) Flag enforcement: RMS_TUESet trigger

When ESet.IsForITShop or ESet.IsITShopOnly is updated, the trigger RMS_TUESet:

• Builds a parameter list containing UID_ESet, XObjectKey, IsForITShop, IsITShopOnly
• Calls QER_PIsForITShopFlagCheck for ESet
• Performs additional checks involving ESetHasEntitlement and entitlements that expose IT Shop flags

Snippet (from sys.sql_modules, around the flag checks):

if update(isForITShop) or update(isITShopOnly) begin
  declare @FlagTest QBM_YParameterlist
  insert into @FlagTest(Parameter1, Parameter2, HasContentFull, e.Parameter3)
    select i.UID_ESet, i.XObjectKey, i.IsForITShop, dbo.QBM_FCVBigIntToString(i.IsITShopOnly)
    from inserted i

  exec QER_PIsForITShopFlagCheck 'ESet', @FlagTest

  if exists (
    select top 1 1
    from inserted i
      join ESetHasEntitlement ehe on i.XObjectKey = ehe.Entitlement and ehe.XOrigin > 0
      join ESet e on ehe.uid_ESet = e.uid_Eset and e.IsITShopOnly = 0
    where i.IsForITShop = 1 and i.IsITShopOnly = 1
  ) begin
    raiserror('#LDS#Changes cannot take place, because assignments to system roles still exist that may not be used exclusively in IT Shop.|', 18, 2) with nowait
  end

  -- then: dynamic checks over entitlement tables that have IsForITShop/IsITShopOnly columns
end

Notes:

• This shows that ESet flag changes are validated not only locally, but also against:
• existing assignments (ESetHasEntitlement)
• whether included entitlements are “IT Shop only”
• the dynamic-ref catalog of entitlement tables that expose IsForITShop (DialogValidDynamicRef + DialogColumn)

Open questions / next DB experiments

• What is the minimal working publish sequence for ESet (system role) analogous to the ADSGroup 5-step sequence:
• set UID_AccProduct?
• set IsForITShop=1?
• insert BaseTreeHasESet on a BO shelf?
• which DBQueue task creates the PR node?
• Which shipped SQL (QER/RMS) explicitly enqueues QER-K-ShoppingRackProductNode for BaseTreeHasESet changes (if any), or is it handled by a periodic ShoppingRack refresh?