Expense Review step: AE and UCPath transaction tabs

[rmartinsen-ucd]

Overview

Implement the display for the Expense Review step of the DataHelper workflow, currently a "Coming soon" placeholder. This covers the AE Transactions and UCPath Transactions tabs. The By Financial Dept and By SFN tabs render as tab buttons with "coming soon" content.

The step is read only. It lets the user confirm the right transactions are included before triggering auto-associations. Inclusion is rule driven and every excluded transaction carries its reason(s): excluded by fund, account, financial dept, activity, purpose, date, account not in AE, unclassified.

Depends on #31 (expense data import pipeline), which populates the transaction tables, segment reference data, and persisted exclusion columns this UI reads. Stage completion persistence is #30; the Continue button here is navigation only until that lands.

Approach

Everything displayed is aggregated at read time:

• One view per source table joins [data].[ChartStringSegments] and the segment reference tables, deriving the step 2 based exclusions (fund, account, financial dept, activity), fund SFN, rollup levels, and display names live. Step 2 edits are reflected immediately. Unclassified or unmatched segments fail closed as excluded with reason unclassified.
• ERN/DOS classification gates FTE only, not dollars: the UCPath view derives an FteExcluded flag from the Ern segment classification plus the FringeBenefitSalaryCd = 'S' rule. FTE sums honor it; a row can be included for dollars while contributing no FTE. Dollar status is unaffected.
• The grid never renders raw rows. Every request groups by the currently selected fields and returns one row per combination with summed amount, summed FTE (UCPath), and underlying transaction count.
• Status (included/excluded) and Reason are derived dimensions that behave like every other field: selectable as columns (participating in the group by) and filterable as chips. A row is included when it has no exclusion reason.

UI behavior

• Tab row: AE Transactions, UCPath Transactions, By Financial Dept ("coming soon"), By SFN ("coming soon"). No counts in the tab headers. Separate tabs because UCPath carries many columns AE doesn't (employee, job code, ERN code, hours, FTE, pay period).
• Field selector per tab: chooses which columns display, and the grid aggregates at exactly that grain. More fields means finer grain; selecting everything approaches raw transaction level.
  • AE defaults: Financial Dept, Fund, Account, AE Project, SFN, Amount
  • UCPath defaults: Financial Dept, Fund, Account, AE Project, Employee, SFN, Amount, FTE
• Status toggle: Included (default), Excluded, All. A preset on the status filter, applied to transactions before aggregation, so the Included and Excluded views always reconcile to the import total. Status can also be selected as a column to see both buckets side by side.
• Reason: available as a field (splits groups by reason combination when selected, e.g. to break a fund's exclusions down by cause) and as a filter chip (e.g. group by Financial Dept, filter reason = Date). When not selected, an informational column can show the distinct reasons present in a group.
• Filter chips: Financial Dept, Fund, Account, AE Project, SFN, Status, Reason, plus Clear all.
• Header totals: total included dollars and total FTE across both sources, FTE honoring the ERN and fringe rules.
• Footer: Export CSV (exports current tab at current grouping and filters), Continue to the next step (navigation only until Real workflow stage state (replace mock snapshot) #30).

Tasks

Database (database/data/)

• One view per source table joining [data].[ChartStringSegments] and the segment reference tables to derive exclusions, SFN, rollup levels, and names. Unclassified fails closed with reason unclassified. UCPath view adds the FteExcluded derivation
• Dynamic group-by sproc over the views: whitelisted group-by column list, filter params, status and reason as derived groupable and filterable columns, paging, sort. Companion counts/totals result (included count, excluded count, excluded counts by reason, total included dollars, total FTE)
• Indexes to support the view joins and grouping

Backend API (server/Controllers/)

• Aggregated transactions endpoint per source: group-by field list, filters, sort, page. Returns grouped rows with summed amount, summed FTE, transaction count, and reasons
• Counts and totals endpoint (included count, excluded count, excluded counts by reason, total included dollars, total FTE across both sources)
• Filter options endpoint (distinct values per filterable column, per source)
• CSV export endpoint that streams the current grouping and filters

Frontend (client/)

• Replace the expense-review "Coming soon" placeholder in workflow.$stageId.tsx with the real step (likely split a per-stage component)
• Tab row: AE Transactions and UCPath Transactions, By Financial Dept and By SFN render "coming soon"
• Field selector per tab with the defaults above, driving both visible columns and aggregation grain
• Status toggle (Included, Excluded, All) wired to the status filter
• Filter chips including Status and Reason, plus Clear all
• Aggregated grid using the shared DataTable with server side paging
• Header totals: total included dollars, total FTE
• Footer: Export CSV, Continue to the next step
• Update workflow.test.tsx and add tests for the new step

Out of scope

• By Financial Dept and By SFN rollup tabs (render "coming soon")
• Any editing of inclusion or exclusion from this screen
• Drill down from an aggregated row to its underlying transactions (possible follow-up)
• Persisting stage completion on Continue (Real workflow stage state (replace mock snapshot) #30)

Notes

• Aggregation runs over roughly 2M detail rows plus the segments and reference joins. With good indexes this should be fine; if group-by queries get slow we can materialize or pre-aggregate later.