# Future-AI Reference Blurb

Paste this into any AI chat (Claude, Copilot, ChatGPT) when you need help
adjusting the tool. It gives the assistant the full context in one shot.

---

We are maintaining the **LMG / NCAM Census Translation Engine** — an Excel
**Office Script** (TypeScript) for Humana ancillary census processing. It
converts a carrier census into two outputs:

1. **LMG** — LaunchMyGroup enrollment upload format.
2. **NCAM** — employees-only quoting format derived from LMG.

**Delivery:** a single Office Script pasted into Excel → Automate tab → Run. It
operates on the open workbook. No backend, no install.

**Output workbook tabs:** `LMG` (always worksheet #1 — LMG uploads the first
sheet only), `NCAM`, `SUMMARY`, `VALIDATION ISSUES`, `PROCESSING LOG`,
`RAW DATA` (untouched original).

**Source of truth for columns:** the LMG platform field list and alias variants
live in `LMG_Column_Dictionary.xlsx` (CensaScrub project). Output columns follow
that dictionary's order and names.

**Key processing rules:**
- Headers: strip parenthetical text; map common variants to LMG field names.
- Relationship → Employee / Spouse / Domestic Partner / Child (unknown = flag).
- Names: Last/First UPPERCASE; Middle Initial = 1 char; Relationship Proper Case.
- Gender → M / F (else flag).
- Dates (DOB, hire, prior-carrier) → MM/DD/YYYY; handles slashes/dashes/ISO/
  2-digit years/Excel serials.
- SSN: 9 digits, digits only; employee missing SSN = error.
- Address 1/2/3 merged → MEMBER STREET ADDRESS (UPPERCASE).
- State names → USPS 2-letter; invalid = flag.
- Zip → first 5 digits; restore dropped leading zero.
- Phone → 10 digits (strip leading country 1); invalid = flag + blanked.
- Salary: strip $ and commas; `$X/hr` annualized as rate × hours × 52
  (hours default 40); salary < $1,000 flagged as possible hourly.
- Occupation: UPPERCASE, abbreviation table, max 25 chars (drop vowels, then
  truncate + flag).
- Disabled? blank → No.
- Coverage tier → EE / ES / EC / FAM / W.
  - LMG: waived dental/vision → `Waive with other coverage`; blank employee
    tier (if the group carries that product) → `Waive with other coverage`.
  - NCAM: convert those back to `W` (NCAM defaults blanks to EE).
- Primary Beneficiary: employee with Basic/Voluntary Life but blank bene → `ON FILE`.
- Voluntary Life: employee blank/waive → 0.

**Pre-pass (reshaping, runs before normalization):**
- Stage 1 collapses stacked product rows (same person across many rows, one per
  coverage line) into one row — gated on a Product column; groups by SSN+name+DOB;
  merges non-blank cells; flags value conflicts.
- Stage 2 explodes `Dependent N <field>` columns into Spouse/Child rows under the
  employee, inheriting address/location; flags defaulted relationships and guessed
  name splits.
- Reshapes only when confident; flags anything out of the norm. Governing rule:
  auto-handle the normal patterns, flag the rest, never guess silently.

**Validation:** ERROR (missing/invalid required), WARNING (likely wrong),
INFO (FYI). Columns: SEVERITY, ROW NUMBER, MEMBER NAME, COLUMN, VALUE, ISSUE,
RECOMMENDED ACTION.

**Deliberate deviation from earlier spec:** duplicates (same last+first+DOB+
gender) are **flagged, not silently merged** — an upload file must never drop an
enrollee silently. The user reviews and removes.

**NCAM columns:** Last Name, Gender, Date of Birth, Dental Coverage Tier,
Vision Coverage Tier; plus Annual Salary, Occupation, STD Election, STD Class,
LTD Election, LTD Class **only if** STD or LTD coverage is present.

**Planned next versions (not yet built):**
- V2: family-unit assignment, duplicate merge, voluntary-life roll-ups,
  beneficiary logic refinements.
- V3: richer NCAM column selection and reconciliation guarantees.
- V4: expanded SUMMARY (per-plan, per-tier, enrolled/waived/eligible).

**When I bring you a problem,** I'll paste a real example: the case, the column,
the source value, and the wrong output. Help me adjust the matching script
(`LMG-NCAM-Census-Engine.ts`) — give me the exact edited code block, not
pseudocode.