Migration Considerations
Technology-agnostic consolidation of what a replacement system must account for, drawn from the 16 domain analyses (582 uniquely numbered, source-cited requirements). Domain-specific migration notes and full open-question lists live at the end of each module page; this page collects the cross-domain themes.
1. The application is only part of the system
The client binaries contain the screens and much of the business logic, but a
server-side layer executes the heavy lifting and is not in this codebase. It
must be recovered from the Oracle database (DDL + PL/SQL export) and/or the
SFPIBACK batch application before functional parity can be claimed:
| Missing component | Evidence | Where documented |
|---|---|---|
| Background adjudication stage driver + duplicate detection | monitored by clmutils/w_bkgd_monitor; statuses advance outside client | Claims |
| Bill-run executor (SUBMITTED/DELAYED → COMPLETE) | client only submits runs; pkg_bills procedures | Billing |
| Check-run engine (check generation, numbering) | client shows only its contract (RFR selection, counters, report_hist blobs) | Funding |
| Batch pricing/repricing rule evaluation | UI maintains rules; results appear in claim_line_pricing_rules | Providers |
| COB validation trigger (moved to DB in 2000) | comment in cob/w_cob_maintenance | COB & Pre-Ex |
Member audit-trail writers (aud_trail_mbr) | no client writer exists | Enrollment |
| COBRA event inserter + letter renderer | no INSERT INTO cobra_event; letters arrive as blobs | COBRA |
| CM trigger evaluation (claim-generated case candidates) | cm_triggers consumed, never produced client-side | UM/CM |
| Caremark claim-adjustment loader | caremark_edi_table worked but never loaded client-side | Drug Plans |
| Stored procedures | sp_rule_defs, sp_rule_contingency_defs, sp_aetna_enrollment, P_BUILD_AETNA, SP_MMO_ENROLLMENT, term_groups_master/division, sfpidba.new_group_coverages, sp_pkg_defs, pkg_bills, SFPIDBA.F_GET_GROUP_MMO_INFO | various |
| Eligibility-expiry cascade triggers | COBRA auto-termination relies on them | COBRA, Enrollment |
| File transmission to partners | all exchanges land on local/shared drives; transport is external | Integrations |
2. Configuration lives in data, not code
Export these tables before design — they ARE the requirements the binaries cannot show:
- Navigation & security:
system_menu,security_*tables,f_useraccessdata,user_ini(favorites, per-user settings) — see Security Model and Navigation - Code tables:
codes/code_keys— every enumeration referenced across the docs (BENRULE,MBREXP,COBRSTAT,SLPERIODS,MOONSTATUS,NOTE_KEY_CODES,FUNDSOURCE,RUNTYPES, APPPATHS, …) - Benefit configuration:
benefit_plans,plan_pkgs,pkg_rules,benefit_rules,rule_defs,rule_contingency_defs,benefit_defs, group options (group_option_vals) - Operational config:
batch_reports,batch_schedules,bill_rule_parm_details,bill_rate_vars,cm_triggers,claim_status_order,homer_versions
3. Executable logic stored as data (highest conversion risk)
Three subsystems store SQL fragments / predicate mini-languages in database columns, resolved at runtime. A replacement must either re-express these as structured configuration or build a compatible interpreter — and the data conversion must parse the grammar:
- Billing rule applicability + rate variables (
bill_rule_parm_details,bill_rate_vars.rate_var_sql) — Billing - Benefit definition criteria (
benefit_defs,*→%wildcards, IN/BETWEEN) — Groups & Plans - Pricing rule criteria (
pricing_rules.*_criterionguided-editor grammar) — Providers
4. Semantic traps for data conversion
- Coinsurance/OOP amounts stored as complements (enter "pay 80%" → stored 20); DED forces 100 — Groups & Plans
- Magic keys: COB rule −100, DEPCARE def −101, case-mgmt fees −111, reject def −1, dup reason −2, internal-CM rule 18443, receipts run −100,
KUMC%claim-id prefix, lifetime accumulator rows keyed 1950-01-01 — Claims, Billing, UM/CM - Meaning-bearing identifiers: 17-char claim ids, UM case ids (user prefix + Julian date + sequence), member ID letter suffixes (A + B–Z dependents)
- Tilde-delimited polymorphic entity keys in notes; two legacy date formats in audit snapshots
- Hard-coded business values: group
501-069Z→ 1099-NEC, USFHP group516-769card prints, per-bank UNC paths and wire constants,SFPIDBAschema literals, "SFPI Examiner" security group
5. Behavior that exists only as manual practice or letter text
Do not assume these are enforced by code: COBRA 30-day grace and admin-% caps
(letter text only), COBRA non-payment termination (report-driven manual work),
pre-existing conditions (captured, never enforced in adjudication), COB order
determination (manual primacy entry, no birthday rule), um_hold_rules and
case-level benefit overrides (maintained but no reader found).
6. Accumulator & financial integrity
member_accumsare updated by adjudication with full compensating back-out on return-to-corrections; mid-year balances must migrate exactly (Claims).- Double-entry
fundtransjournal, void/reissue state machine, reconciliation gates, positive-pay sequencing (Funding). - Stop-loss accumulation is recomputed at report time from claims + register — there is no stored accumulator to migrate, but report conventions must be reproduced (Stop-Loss).
- Client uses
AutoCommit=1: multi-step operations are sequenced, not transactional (e.g. mass termination commits per 1000-key batch). A replacement should make these atomic — flagging where behavior would change.
7. Known defects — do not blindly re-implement
Each domain's notes flag likely bugs found in code (never-firing partial-funding check, self-comparing conditions, loop-index errors in stop-loss family reports, person-code letter map truncation, duplicate CASE branches, empty change-propagation stub, month-unit waiting-period quirk, alphabetical status-precedence risks). Treat these as SME decisions: preserve behavior or fix.
8. Security & compliance carry-over
Individual authenticated identities with per-user audit attribution; layered
function security (app default → class → user); group-level data security;
row-protection of member lists (members_included); HIPAA-relevant outputs (EOBs,
HIPAA certificates); regulated documents (COBRA notices, 1099-NEC, MICR checks).
See Security Model.
9. Open questions register (index)
A mechanical audit of documentation coverage — and the full inventory of what is missing (undocumented tables, objects absent from the binaries, external components) — is at Coverage Audit & Gaps.
Every module page ends with its open-questions list (~120 items total). The blocking ones:
| # | Question | Blocks |
|---|---|---|
| OQ-1 | Export system_menu + security tables (live function tree & role matrix) | Scope & navigation design |
| OQ-2 | Oracle DDL + PL/SQL export (types, constraints, triggers, procedures, jobs) | Data model, §1 recovery list |
| OQ-3 | Locate/recover the SFPIBACK batch application (adjudication driver, bill/check runs, duplicate detection) | Core processing parity |
| OQ-4 | Export codes/code_keys and group options (all enumerations) | Every domain's value lists |
| OQ-5 | Which PBM feeds are live (CVS vs RX Benefits vs EXP/RESTAT/ValueRx) | Interface scope |
| OQ-6 | How are non-USFHP ID cards produced today? | ID cards scope |
| OQ-7 | Confirm which stop-loss contract model (rich sl_contracts vs group_stoploss_parms) is authoritative | Stop-loss design |