Roche portfolio data (DP_PORTFOLIO)

Where authoritative project metadata lives and how to read it. Assessment/scoring tables (e.g. BAR) should only own their assessment data — everything about the project itself (name, phase, molecule type, DTA, indication…) comes from the portfolio, joined by THEME_NO.

Tables (schema: DP_RDP_PORTFOLIO, database PPP_PROD)

• DP_PORTFOLIO (p) — one authoritative row of metadata per theme.
• DP_PORTFOLIO_ACTIVITY_PVT (a) — milestone/activity rows (several per theme).
• Join on p.PF_THEME_NO = a.PF_THEME_NO (the same join the family-A services use).

Canonical query

SELECT
  p.PF_THEME_NO        AS THEME_NO,
  p.PF_PROJECT_NAME    AS PROJECT_NAME,
  p.TH_DESC            AS THEME_DESC,
  p.PF_CURRENT_PHASE   AS CURRENT_PHASE,
  p.PF_STAGE           AS PROJECT_STAGE,
  p.DTA_CODE           AS DTA_CODE,          -- ONC / NS / IMM / CVM / OPH / ID / OTH
  p.IND_DESC           AS IND_DESC,          -- indication
  p.MOL_DESC           AS MOL_DESC,          -- molecule / INN
  p.PF_MOLECULE_TYPE   AS PF_MOLECULE_TYPE,  -- modality
  p.PF_FRANCHISE       AS PF_FRANCHISE,
  p.PF_GOVERNANCE_BODY AS GOVERNANCE_BODY,
  p.PF_PRIORITY_PRED   AS PF_PRIORITY_PRED,
  p.PF_PRIORITY_GRED   AS PF_PRIORITY_GRED,
  p.PF_PRIORITY_LSPC   AS PF_PRIORITY_LSPC,
  p.PF_TYPE            AS PF_TYPE,
  p.PF_STATUS          AS PF_STATUS
FROM DP_PORTFOLIO_ACTIVITY_PVT a
JOIN DP_PORTFOLIO p ON p.PF_THEME_NO = a.PF_THEME_NO

De-dup in Python: the activity join yields several rows per theme; project metadata is identical across them, so first-seen wins per THEME_NO.

Priority is governance-body-dependent

There is no single priority column — pick the one matching PF_GOVERNANCE_BODY:

gov = (governance_body or "").lower()
if "gred" in gov:            priority = PF_PRIORITY_GRED
elif "lspc" in gov or "lscp" in gov: priority = PF_PRIORITY_LSPC
elif "pred" in gov:          priority = PF_PRIORITY_PRED
else:                        priority = None

Column cheat-sheet (logical → source)

The overlay pattern

When another table (BAR, etc.) also has projects keyed by THEME_NO:

1. Fetch portfolio metadata into {theme_no: {field: value}}.
2. For each project, setattr(proj, field, value) only when the portfolio value is truthy — keep the assessment table’s value as a fallback.
3. Wrap in try/except: a portfolio outage must not break the assessment grid.

Reference implementation

bar-dashboard/backend/app/services/portfolio_service.py (query + priority logic) and bar_service._overlay_portfolio_metadata (overlay). See also [[reference-dta-colors]] for turning DTA_CODE into the canonical colour palette.