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

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

Sandbox DB Evidence — Service categories vs decision method + preprocessor gates (2026-04-27)

Goal: clarify what AccProductGroup (service categories) *actually does* in this sandbox’s IT Shop flows, and record where preprocessor-relevant config parameters appear as gates in live DB logic.

Method: SELECT-only queries via scripts/sandbox/Invoke-SandboxSql.ps1 -Database OIM against im.sandbox.local (DB name resolves to OneIM via scripts/sandbox/roles.psd1).

1) Service categories are not an availability gate for PR nodes (in this sandbox)

Evidence query:

SELECT
  CASE WHEN ap.UID_AccProductGroup IS NULL OR ap.UID_AccProductGroup = '' THEN 0 ELSE 1 END AS HasAccProductGroup,
  COUNT(*) AS Cnt
FROM dbo.BaseTree bt
JOIN dbo.AccProduct ap ON ap.UID_AccProduct = bt.UID_AccProduct
WHERE bt.UID_OrgRoot = N'QER-V-ITShopOrg'
  AND bt.ITShopInfo = N'PR'
GROUP BY CASE WHEN ap.UID_AccProductGroup IS NULL OR ap.UID_AccProductGroup = '' THEN 0 ELSE 1 END
ORDER BY HasAccProductGroup;

Result (live DB, 2026-04-27):

HasAccProductGroup = 0 → Cnt = 7
HasAccProductGroup = 1 → Cnt = 9

Interpretation (sandbox-specific):

AccProduct.UID_AccProductGroup is not required for a requestable IT Shop PR node to exist in BaseTree in this environment.
This is a concrete divergence from “service categories are mandatory” heuristics found in some docs/training; treat as an environment-specific fact until proven otherwise by supported UI/API flows.

2) `AccProductGroup` strongly influences `UID_PWODecisionMethod` resolution

Evidence query (PR nodes decision method presence):

SELECT
  SUM(CASE WHEN ap.UID_PWODecisionMethod IS NULL OR ap.UID_PWODecisionMethod = '' THEN 1 ELSE 0 END) AS AccProductNull,
  SUM(CASE WHEN bt.UID_PWODecisionMethod IS NULL OR bt.UID_PWODecisionMethod = '' THEN 1 ELSE 0 END) AS ProductNodeNull,
  SUM(CASE WHEN (ap.UID_PWODecisionMethod IS NULL OR ap.UID_PWODecisionMethod='') AND (bt.UID_PWODecisionMethod IS NOT NULL AND bt.UID_PWODecisionMethod<>'') THEN 1 ELSE 0 END) AS ProductNodeSetButAccProductNull
FROM dbo.BaseTree bt
JOIN dbo.AccProduct ap ON ap.UID_AccProduct = bt.UID_AccProduct
WHERE bt.UID_OrgRoot = N'QER-V-ITShopOrg'
  AND bt.ITShopInfo = N'PR';

Result (live DB, 2026-04-27):

AccProductNull = 8
ProductNodeNull = 0
ProductNodeSetButAccProductNull = 8

Implication:

In this sandbox, PR nodes consistently get a decision method, even when the service item (AccProduct) has none.
A plausible explanation is the procedural fill chain observed in shipped DB code:
AccProduct.UID_PWODecisionMethod (if present)
else AccProductGroup.UID_PWODecisionMethod (category)
else BO helper-board method (PWOHelperBoardMethod.UID_PWODecisionMethod by shelf)

2.1) Evidence: `dbo.QER_FTMethodForPRNode` resolves via service-category parent chain

Snippet (from sys.sql_modules around AccProductGroup):

... update @ProductNodeHasPWODecisionMethod set UID3 = a.UID_PWODecisionMethod ...
select @MaxAccProductGroupLevel = MAX(v.CountSteps) from QER_VACCProductGroupCollection v
...
update @ProductNodeHasPWODecisionMethod set UID3 = gp.UID_PWODecisionMethod
from @ProductNodeHasPWODecisionMethod b
join accproduct a on b.UID4 = a.UID_AccProduct
join accproductGroup g on a.uid_accproductGroup = g.uid_accproductGroup
join QER_VACCProductGroupCollection co on co.UID_ACCProductG...

2.2) Evidence: `dbo.QER_PITShopProductNodeCreate_b` fills decision method from `AccProductGroup`

Snippet (from sys.sql_modules around AccProductGroup):

update #Struktur set UID_PWODecisionMethod = a.UID_PWODecisionMethod
from #Struktur s join AccProduct a on a.UID_AccProduct = s.UID_ACCProduct
where s.UID_PWODecisionMethod is null and a.UID_PWODecisionMethod > ' ' and s.CreatePRNode = 1

update #Struktur set UID_PWODecisionMethod = g.UID_PWODecisionMethod
from #Struktur s
join AccProduct a on a.UID_AccProduct = s.UID_ACCProduct
join AccProductGroup g on a.UID_AccProductGroup = g.UID_AccProductGroup
where s.UID_PWODecisionMethod is null and g.UID_PWODecisionMethod > ' ' and s.CreatePRNode = 1

3) Preprocessor-relevant config parameters: visible in DB, but code can be “missing by design”

3.1) Evidence: `QER\ITShop\AutoPublish\ADSGroup` is preprocessor relevant and disabled

Live rows (dbo.DialogConfigParm, 2026-04-27):

QER\ITShop\AutoPublish → Enabled=1 / IsEnabledResulting=1 / not preprocessor-relevant
QER\ITShop\AutoPublish\ADSGroup → Enabled=0 / IsEnabledResulting=0 / IsPreprocessorCondition=1 (description explicitly says “requires recompiling the database”)
QER\ITShop\Templates → Enabled=0 / IsEnabledResulting=0 / IsPreprocessorCondition=1

3.2) Evidence: no SQL modules reference `AutoPublish` + `ADSGroup` in this compiled sandbox DB

Evidence query:

SELECT COUNT(*) AS RefCount
FROM sys.sql_modules m
WHERE m.definition LIKE N'%AutoPublish%'
  AND m.definition LIKE N'%ADSGroup%';

Result (live DB, 2026-04-27):

RefCount = 0

Interpretation:

The preprocessor-relevant flag plausibly explains why “AutoPublish ADSGroup logic” is not discoverable via string search in the compiled SQL modules in this sandbox — enabling the parameter requires compile, which may inject/activate additional templates/triggers/procedures.

4) Trigger-level preprocessor gates: `ADS_TUAdsGroup` checks `DialogColumn.IsDeactivatedByPreProcessor`

Trigger evidence (snippet from dbo.ADS_TUAdsGroup around IT Shop fields):

if update(isForITShop) or update(isITShopOnly) begin
  ... exec QER_PIsForITShopFlagCheck 'AdsGroup', @FlagTest, 'ADSAccountInADSGroup|ADSMachineInADSGroup'
end

if update(uid_accproduct) begin
  ...
  join dialogColumn c
    on c.UID_DialogTable = 'QER-T-ITShopOrg'
   and c.columnname = 'uid_ACCProduct'
   and c.IsDeactivatedByPreProcessor = 0
  ...
end

Supporting evidence: the referenced column is not deactivated in this sandbox:

DialogColumn row:
UID_DialogTable = 'QER-T-ITShopOrg'
ColumnName = 'UID_AccProduct'
IsDeactivatedByPreProcessor = 0

Practical troubleshooting note:

When “expected IT Shop side effects” do not occur, a useful sandbox-specific diagnostic is to check whether related DialogColumn entries are deactivated by the preprocessor (which can make trigger paths no-op even though the trigger exists).

Sandbox DB Evidence — Service categories vs decision method + preprocessor gates (2026-04-27)

1) Service categories are not an availability gate for PR nodes (in this sandbox)

2) AccProductGroup strongly influences UID_PWODecisionMethod resolution

2.1) Evidence: dbo.QER_FTMethodForPRNode resolves via service-category parent chain

2.2) Evidence: dbo.QER_PITShopProductNodeCreate_b fills decision method from AccProductGroup

3) Preprocessor-relevant config parameters: visible in DB, but code can be “missing by design”

3.1) Evidence: QER\ITShop\AutoPublish\ADSGroup is preprocessor relevant and disabled

3.2) Evidence: no SQL modules reference AutoPublish + ADSGroup in this compiled sandbox DB

4) Trigger-level preprocessor gates: ADS_TUAdsGroup checks DialogColumn.IsDeactivatedByPreProcessor

2) `AccProductGroup` strongly influences `UID_PWODecisionMethod` resolution

2.1) Evidence: `dbo.QER_FTMethodForPRNode` resolves via service-category parent chain

2.2) Evidence: `dbo.QER_PITShopProductNodeCreate_b` fills decision method from `AccProductGroup`

3.1) Evidence: `QER\ITShop\AutoPublish\ADSGroup` is preprocessor relevant and disabled

3.2) Evidence: no SQL modules reference `AutoPublish` + `ADSGroup` in this compiled sandbox DB

4) Trigger-level preprocessor gates: `ADS_TUAdsGroup` checks `DialogColumn.IsDeactivatedByPreProcessor`