---
name: roche-portfolio-data
description: Read Roche portfolio project metadata (name, phase, DTA, indication, molecule, franchise, priority) from Snowflake DP_PORTFOLIO. Use when a project needs authoritative project/theme metadata, the DP_PORTFOLIO join, governance-body priority logic, or the "overlay portfolio onto BAR" pattern.
---

# 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
```sql
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`:
```python
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)
| Logical field | Source column |
| --- | --- |
| program/name | `PF_PROJECT_NAME` (fallback `TH_DESC`) |
| indication | `IND_DESC` |
| molecule / INN | `MOL_DESC` |
| modality | `PF_MOLECULE_TYPE` |
| phase | `PF_CURRENT_PHASE` |
| DTA | `DTA_CODE` |
| franchise | `PF_FRANCHISE` |
| priority | `PF_PRIORITY_{PRED,GRED,LSPC}` per governance body |

## 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.