NetSuite Template Flow

The monthly close pipeline. Six steps from generating a fresh template through final audit. Every step has a CLI command — see CLI Commands for the full reference, or Finance and Transactions in the CLI section.

The six-step flow

1. generate-netsuite-template  →  empty .xlsx with dim_account + dim_program_id
2. (Excel)                     →  fill in via NetSuite Solution7 add-in
3. upload-netsuite             →  parse formula results, resolve FKs, insert to stg_financials_raw
4. upload-income-statements    →  upsert confirmed GL data
5. audit                       →  compare staging vs confirmed
6. diagnose                    →  check FK resolution + data gaps

Each step in detail:

1. Generate the template

optimal finance template --output ./netsuite-template-march.xlsx

Pulls the current dim_account and dim_program_id from the ReturnPro Supabase. Produces an .xlsx with the rows pre-filled and the cells the close team needs to populate left blank for Solution7 formulas.

2. Fill in Excel

Open the .xlsx (rename to .xlsm if Solution7 needs the macro extension) in Excel with the NetSuite add-in installed. The Solution7 NSGLAPBAL.LOCKED formula caches values into the cells; ExcelJS later reads those cached .result values, not the formulas themselves.

The canonical formula (validated March 2026):

=-NSGLAPBAL.LOCKED(
    "ReturnPro (Consolidated)",
    <acct>,
    <period>,
    <period>,
    "Program ID",
    <programid>
  )

See KPIs for the three formula defects to audit for: NSGLADBAL typo, "New Program" filter dimension, and IF(TRUE, hardcoded, NSGLAPBAL(...)) wrappers.

3. Upload to staging

optimal finance upload --file ./netsuite-march.xlsm --user-id <uuid>

Parses the cached formula results, resolves account / program / client foreign keys against the dim tables, and inserts rows into stg_financials_raw.

No upsert

upload is INSERT-only. Re-uploading the same file creates duplicate rows. Delete the existing rows for the month first if you need to re-run:

DELETE FROM stg_financials_raw WHERE period = '2026-03';

4. Upload the confirmed income statement

optimal finance upload-confirmed --file ./income-stmt-march.csv --user-id oracle

Upserts on (account_code, period) so it is safe to re-run. Populates confirmed_income_statements.

5. Audit

optimal finance audit --months 2026-03

Compares stg_financials_raw against confirmed_income_statements for the given month(s). Flags any account-level dollar variance above the tolerance threshold.

6. Diagnose

optimal finance diagnose --months 2026-03

Checks:

• Foreign-key resolution — were any rows dropped because the account or program ID didn't match the dim tables?
• Data gaps — are there expected accounts with zero rows?
• Sign-multiplier sanity — do revenue / refund / cost / opex aggregate to plausible signs?

Dim maintenance reminder

optimal finance sync-dims is deactivate-only. Every close also needs:

• New IS GL codes upserted into dim_account (with correct sign_multiplier: +1 revenue, −1 refunds, −1 cost/opex).
• NetSuite-export programids stuck inactive reactivated in dim_program_id.

Reference scripts: optimal-cli/scripts/march-dim-fix.ts (dim expansion), optimal-cli/scripts/fix-signs-from-audit.ts (data-driven sign correction).

R1 volume uploads

R1 volumes (checked-in, order-closed, ops-complete) are uploaded separately:

optimal finance upload-r1 \
  --file ./check-in-march.xlsx \
  --user-id <uuid> \
  --month 2026-03 \
  --volume-type checked_in

--volume-type accepts checked_in, order_closed, or ops_complete. Each upload produces three insert sets per program group: Unit (TRGID count), Pallet (LocationID count), and Qty (allocation-based). Like upload, this command is INSERT-only — delete the old batch before re-running.

fpa_baseline_units rename

The FP&A baseline table was renamed from fpa_wes_imports to fpa_baseline_units in migration 20260330300000. If you maintain external integrations or queries against this table, switch to the new name — the old name no longer exists.