Migration Considerations

Technology-agnostic consolidation of what a replacement system must account for, drawn from the 19 domain analyses on this site (792 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.

Read it as ten claims about the replacement's scope, in rough order of risk: what is missing from the codebase (§1), what lives in data rather than code (§2–3), what will break silently during conversion (§4–5), what must not be built (§6), what must balance to the cent (§7–8), what must carry over (§9), and what nobody can answer from the artifacts alone (§10).

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), the operating system's crontab, and the separate background executables before functional parity can be claimed.

SFPIBACK is now identified. It is not a mystery program name but the background-process database identity: rows created by the server-side layer are stamped create_user_id = SFPIBACK, which is how the guide's claim-rollback procedure distinguishes background-created claim lines from user-entered ones (User Guide ch. 22). The literal appears in six client objects — including clmsproc/n_claim_adjudication and clmshare/n_cst_return_claim_to_corrections — always as a filter on work someone else did. Recovering "SFPIBACK" therefore means recovering the set of background executables that run under that identity, not a single binary.

Client-visible evidence of the missing layer

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
Nightly production print cycle	QMCSO and HIPAA letters are printed from a request table that does not exist in the client — the letter-production job is external, exactly as for COBRA letters (User Guide ch. 1)	Enrollment
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

What the user guide reveals about the database layer (User Guide ch. 22, ch. 3)

The official guide's "Database Implemented Functionality" chapter confirms the server-side layer's architecture and names its components. This converts several of our inferences into specifics to hand the DBA doing the export:

Component	What the guide states
SP_MUTATION_PREVENT package	Session-level flags let parent/child triggers coordinate cascading updates without hitting ORA-04091 (mutating table). Named flags: group_fire_trigger, grpcov_fire_trigger, grp_fire_trigger, mbrelig_fire_trigger, coverage_fire_trigger, rider_fire_trigger, grpcatval_fire_trigger (plus obsolete mbr_eff_fire_trigger), each naming the setting table and the tables it suppresses re-checks in. The cascade logic we observed from the client side is implemented as a trigger network built on this package.
Audit-trail triggers	Answers the enrollment open question directly: audit rows are written by "after" triggers on UPDATE and DELETE only — inserts were deliberately not recorded — into AUD_TRAIL_GRP (keyed GROUP_ID) and AUD_TRAIL_MBR (keyed MEMBER_SYS_KEY), capturing table, key, action, user, timestamp, and a chg_desc of column before/after values. The audited tables are enumerable from the data dictionary by trigger-name prefix TUA/TDA (dba_triggers). Scope is limited to enrollment- and group-related tables.
PKG_AUDIT_CONFIG package	The application controls auditing per session via three variables — session_audit_flg, wang_transfer_flg, chg_comment — set by procedures AUDIT_FLG, WANG_FLG, CHG_COMM (each returning 0 success / −1 failure). This is how screens attach an explanatory comment to audit rows and how bulk operations suppress auditing.
Effective/expiration-date triggers	"Wherever the combination of eff_dt and exp_dt occurs in a table, a trigger will be built to ensure that when an eff_dt is created or updated, it is no earlier than the latest exp_dt for a row that otherwise has the same key." This qualifies our finding that eligibility overlap prevention looked client-side only — a database-level guard was specified; whether it was built for every such table must be verified against the DDL.
Named triggers	TU_MEMBER_ELIGIBILITIES, TU_ELECTIONS (cited as the reference examples), and TU_COBRA_QB — which on QB election clones the prior member row by soc_sec_no and adds the waiting period; the guide records a defect (no sql%rowcount guard after INSERT INTO members) (ch. 3).
COBRA event creation	Automatic and server-side: qualifying events and QB records are generated from the termination reason entered in enrollment, per member class (ch. 3) — closing our "nothing inserts cobra_event" question.
"COBRA background process"	A continuously running server-side application that creates election/termination/notification letters, terminates coverage at 63 days past the payment deadline / at the max deadline / at the response deadline, tracks a secondary 30-day insignificant-amount billing cycle, and writes four categories of error rows with clear-and-retry semantics (ch. 3).
HP-to-Wang synchronization	After-triggers on enrollment data populate WANG_MEMBER_TRANS for a legacy sync process (detailed in an unavailable CONV.DOC). Almost certainly dead, but it explains Wang-era artifacts throughout the schema.
HIPAA certificate triggers	Two triggers maintain the HIPAA certificate table (processed_ind, dep_member_sys_key, sub_member_sys_key) on loss of medical coverage, reinstatement, and change of termination reason, with an exclusion list of six termination reasons (ch. 1).
COB duplicate / date-range trigger	The validation moved server-side on 3/13/2000; the guide records the rule content (one Primary per coverage type per overlapping period; plan-type uniqueness except Medicare) but not the definition (ch. 1).
Periodic ID-card production run	Cards are not produced at request time: a scheduled "ID card run" drains the ID_CARD_REQ queue, printed in-house (ch. 1).
Nightly print-queue cycle	QMCSO and HIPAA letters are generated by a nightly print-queue process (ch. 1).
Nightly adjudication batch (UM/CM)	The single largest undocumented component. Per ch. 18–19 it owns: claim-to-case matching (per-category key sets), hold-rule enforcement (um_hold_rules; held claims are excluded from subsequent nightly runs), writing claim_line_um_rules (case/service/rule assigned, pricing method, allowed amount, units), CM negotiated pricing and rent-to-own, UM indicator checks including the "UR checked once" package switch, CM exclusion-exceptions displacing plan exclusions, CM/UR quantity limits displacing plan limit rules, MVA/workers-comp reactions, COB-Unknown holds, and mutual close of potentials and messages. Its "Audit step" evaluates cm_triggers to create cm_potentials — only after a claim fails to match an existing CM case — once per claim detail line, always at status Normal.
Dummy-patient matching trigger	A database trigger matches "dummy" patient UM cases to members as they are enrolled (ch. 19).
CM time-billing claim generator	A process bills case-management time as claims with the KUMC% id prefix against the CM Rates benefit definition (ch. 18–19).
UM error-message resolver	A background process clears UM data-error messages (ch. 19).
Column-stamping triggers	updt_dt / updt_user_id are populated by triggers from the system date and the Oracle login; the client never sets them. Any conversion that writes these columns directly will diverge from production semantics.
TDW warehouse triggers + PKG_DW_SYNC	After-row triggers prefixed TDW on every warehoused table queue work into DW_CLAIM_TRANS / DW_MEMBER_TRANS / DW_GROUP_TRANS; PKG_DW_SYNC drains them to the SFPW warehouse over DB links. A replacement's reporting/warehouse feed must be re-specified, not ported.
Role-gated hierarchy trigger	Creating a group with a null parent_group_id (a new master group) is permitted only when the session's Oracle role holds the specific grant — authorization enforced in the database, not the UI.
pkg_del.del_mbr_elig	Deletes a subscriber's coverages, elections and eligibility periods as steps 1–3 of the member-division-move sequence.
SP_POPULATE_REFRESH + full_refresh package	Table-driven refresh of the test database from production (REFRESH_* control tables). Environment tooling, but it documents the referential order in which the schema can be safely copied — useful for migration cutover rehearsals.
Claim-ID assignment	Claim IDs come from an Oracle sequence, with each source's starting number reset daily by a scheduled job. Identifier generation is a server-side responsibility.
Fund-page coverage uniqueness	A database-enforced constraint: a funding node may not carry a duplicate coverage type, nor All alongside another type. Contrast the Claims Reserve minimum, which is application-enforced only — conversion must re-assert the latter itself.
Deferred member updates	A group-coverage change either calls a stored procedure to apply it to members immediately (stamping applied_to_member_dt) or is deferred to the nightly run. Mid-migration extracts can therefore contain group configuration that has not yet reached member rows.

Scheduled OS-level jobs (crontab) (User Guide ch. 22)

A third location of business logic, distinct from both the client and the database: jobs scheduled by the operating system on the Oracle host. Neither the binaries nor the DDL will reveal these — the live crontab is a separate artifact to request.

Job	Schedule	What it does
Data-warehouse sync	Weekday evening and morning passes, plus a Saturday run	Drains the DW_*_TRANS queues to the warehouse; claims are locked during refresh
Snapshot refresh	Monday–Saturday	Oracle snapshot replication of lookup and reference tables to the warehouse
Claims delete ("claims delta")	Daily	Purges claims marked for deletion and 60+ days old, in referential order across 39 tables, then nulls claim_eob_info_details.claim_id and sets dw_claim_trans.action_ind = 'D'
Claim-ID reset	Daily	Resets each claim source's starting claim ID

The claims-delete job is the one with migration teeth: it defines the system's actual retention behaviour and the exact referential order of the claim sub-schema. Both are reusable directly as conversion and archival specifications.

Systems outside HOMER entirely (User Guide ch. 22)

Several business processes the guide documents as part of "how the work gets done" do not run in HOMER at all. A replacement must consciously decide to absorb, re-integrate, or retire each one — they are invisible in the code and easy to omit from scope:

Process	Where it actually runs
CP2100 / B-notice processing	FoxPro application, plus a WANG krisfile and a WANG stop-pay switch
ID card production	Nightly FTP to the WANG, with a Visual Basic application driving a Windows 3.x-era printer
EDI claim import	Separate executable newedi.exe
Provider file loads	SQL*Loader plus shell scripts on the server
Accounting, billing and stop-loss working files	Microsoft Access, one process described by the guide as "All manually keyed, no ties to Homer"

The Access and FoxPro artifacts are unmanaged business logic on end-user workstations. Locating them is an inventory task that should start now, because nothing in the Oracle export or the PBL extract will point to them.

Inheritance is the notable exception to the server-side pattern: the guide states the rules are deliberately not table-driven but hard-coded in the application, spanning two hierarchies (Client Maintenance and Funding) with "push" (parent to descendants) and "pull" (on group creation) semantics, and that only Addresses are truly shared objects — everything else is copied. That is the specification the empty change-propagation stub in client/w_client_grp_chg_prop_hist was meant to implement (see Groups & Plans).

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_useraccess data, 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:

1. Billing rule applicability + rate variables (bill_rule_parm_details, bill_rate_vars.rate_var_sql) — Billing
2. Benefit definition criteria (benefit_defs, *→% wildcards, IN/BETWEEN) — Groups & Plans
3. Pricing rule criteria (pricing_rules.*_criterion guided-editor grammar) — Providers

4. Semantic traps for data conversion

Columns that mean the opposite of what they look like

The most dangerous category, because a conversion that reads these at face value produces plausible, silently wrong data:

Trap	Reality	Where
Coinsurance / OOP percentages	Stored as complements — the user enters "plan pays 80%", the column holds 20. DED forces 100	Groups & Plans
ID-card queue error_flg = 1	Means the row is valid, not in error. Inverted against its own name	ID Cards
COB method REDUCECLM returning −100	A sentinel for "COB not applicable", not a negative payable amount. Read numerically, it corrupts claim amounts	COB & Pre-Ex

Profile every such column against its documented meaning before conversion, and do not let the column name settle the question.

Other semantic traps

• 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.
• The write-off keys are a documented trap. −102 is declared as a write-off rule but is dead: it has no live readers and its only consumer, of_EobWriteOff, is marked "Abandoned" — do not carry it forward. The live write-off key is −104 (savings type WRITEOFF, claims/w_claim_line_pricing_rules). Worse for anyone verifying by search: −102 is live in a different namespace, as the medical lifetime-maximum accumulator key (accm_sys_key), so a naive grep for "−102" finds real hits that have nothing to do with write-offs. Confirm by namespace, not by value.
• Two clocks for one timestamp: the ID-card request timestamp is written by two callers using different clocks — database time in one path, workstation time in the other. Expect skew in the column and do not assume a single ordering
• Meaning-bearing identifiers: 17-char claim ids, UM case ids (user prefix + Julian date + sequence), member ID letter suffixes (subscriber A, dependents B–Z). The suffix scheme has no overflow branch — a 26th dependent silently produces a colliding member ID; see §8
• Tilde-delimited polymorphic entity keys in notes; two legacy date formats in audit snapshots
• Hard-coded business values: group 501-069Z → 1099-NEC, USFHP group 516-769 card prints, per-bank UNC paths and wire constants, SFPIDBA schema literals, "SFPI Examiner" security group
• The master-group list is a literal, not a query. Exactly three groups have a null parent_group_id — SFPI, M/III and IBA — and three code sites name them in source rather than selecting them: sales/w_census_rpt_args_sheet (open event), sales/d_annlstmt_group_select (SQL) and sales/n_cst_sales_report_wizard (isa_start_groups array). Any fourth master group added to production is invisible to those three reports. Make the list logical in the replacement, and check the conversion for a fourth

5. Behavior that is not what it appears

Enforced by practice, not by code

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

Present in code but dormant in production

Address standardization has almost certainly not run for ~20 years. The Mailers / Melissa Data reference data was not renewed after November 2002 (User Guide ch. 22), yet the client code, the [Paths] INI section, the mdAddr.dll objects and the std_addr.exe batch job are all still present and documented. Reference data that old cannot validate a current address.

This materially changes the requirement. Before scoping address validation into a replacement, establish whether the business wants the capability restored (a new address-verification service, procured and integrated) or dropped — it is not a port of working functionality either way. Treat the same question as open for any other feature whose licensed reference data may have lapsed; the UCR/R&C tables from MDR are the obvious one to check next.

6. Features the guide advertises that the client never implemented

Do not scope these into a replacement on the strength of the user guide's table of contents. The User Guide Reconciliation verifies each against the extracted source:

• Capitation (ch. 15), Managed Care / Budgeting (ch. 21), Commissions (ch. 8) — the menu items exist but are dead: capitation and managed care are hidden and disabled with the tag "For future use"; Commissions is visible but opens a window titled "Under Development". No supporting objects or tables exist. The only persisted traces are commission_pct columns on group_stoploss_coverages / group_ancillary_coverages.
• SFPI AP / HR / AR (ch. 24, 25, 26) — corporate accounting, payroll and receivables have no objects or tables in HOMER; they were separate systems.
• Sales proposals / quoting / underwriting (ch. 20) — the sales library is real but is reporting (IBNR, lag triangles, claimant registers, census); nothing implements proposals or underwriting.
• QMCSO letters (User Guide ch. 1) — the guide specifies a seven-letter set plus four declination reasons, printed from a request table by the nightly production cycle. Neither the request table nor the job exists in the client. QMCSO data entry is real (enroll/w_enroll_qmcso_maint and friends); QMCSO correspondence is not.
• "Produce Free Form HIPAA Certificate" (User Guide ch. 1) — a live menu item that opens homersys/w_construct, the shared window whose title is literally "Under Development". Like Commissions, this was promised to users and never delivered.
• Five of those chapters (15, 21, 24, 25, 26) are empty files — a title page and a 1/13/98 footer. They were reserved in the 1998 table of contents and never written, which corroborates "never built" over "documentation lost".

A caution for anyone reading the schema: drg_cap_rate / default_drg_cap_rate are DRG payment caps, not capitation.

The exception: CMS Section 111 is a compliance gap, not a descoped feature

Everything above is an unbuilt convenience — safe to leave out of a replacement. CMS Section 111 reporting is categorically different and must not be filed alongside them.

Section 111 of the MMSEA imposes a federal statutory obligation on group health plans to report covered individuals to CMS for Medicare secondary-payer coordination. The user guide describes the process operating against a persistent Oracle table — back up, apply changes, reload; the response load emits INSERT and UPDATE activity (User Guide ch. 1).

A sweep of all 53 extracted libraries found zero implementation: no Section 111, MMSEA, MSP, TIN-reference or CMS-response objects of any kind. The only CMS string anywhere in the source is static text on enroll/d_hipaa_form.

Two readings, and the migration must establish which is true before design:

1. The reporting runs entirely server-side or outside HOMER — in which case the Oracle table, its load/response jobs and the CMS file layouts are missing artifacts to recover, and they belong in the OQ-2 export request (below).
2. It is not happening — in which case the replacement programme has surfaced an open regulatory exposure, and that finding needs an owner in the business immediately, independent of any migration timeline.

Either way the replacement inherits the obligation. Scope Section 111 in.

7. Accumulator & financial integrity

• member_accums are updated by adjudication with full compensating back-out on return-to-corrections; mid-year balances must migrate exactly (Claims).
• Double-entry fundtrans journal, 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.

8. Known defects — do not blindly re-implement

Each domain's notes flag likely bugs found in code. Most are SME decisions (preserve behaviour or fix), but three are live data-integrity risks that the migration itself must detect in the production data, not merely decide about:

Defect	Why it matters to the conversion	Where
Member-ID suffix overflow	wf_get_member_suffix maps dependents to letters with no default branch. A 26th dependent therefore receives no suffix and silently produces a colliding member ID. Profile production for duplicate member IDs before conversion; a naive migration will merge two people	Enrollment
Unqualified DELETE in the CVS / RX Benefits PBM export	After writing the export file, the job deletes the staging table without a predicate, destroying rows added since the snapshot was taken — contradicting the comment immediately above it. Any rows lost this way are already gone; it also means the staging table is not a reliable audit of what was sent	Drug Plans
Claims message 167 collision	Two unrelated usages of the same message number in different libraries — confirmed a defect, not a modelling choice. Message-code conversion must disambiguate by context, not by number	Claims

One more to profile rather than port: the UM MONTHS derivation is implemented twice and inconsistently — one copy subtracts 1, the other does not. Historical data will contain both conventions (UM/CM).

The remaining flagged defects are behaviour-preservation decisions: never-firing partial-funding check, self-comparing conditions, loop-index errors in stop-loss family reports, person-code letter map truncation, duplicate CASE branches, the empty change-propagation stub, the month-unit waiting-period quirk, and alphabetical status-precedence risks.

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

10. 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 and cross-cutting page ends with its own open-questions list — about 150 items in total. The table below is the blocking subset: until these are answered, design decisions downstream of them cannot be made with confidence. Each names what it blocks so it can be assigned to an owner rather than admired.

#	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). Now specific, thanks to the user guide: request the SP_MUTATION_PREVENT, PKG_AUDIT_CONFIG and PKG_DW_SYNC packages; every trigger whose name starts TUA/TDA (audit trail), TDW (warehouse) or TU_ (cascades — e.g. TU_MEMBER_ELIGIBILITIES, TU_ELECTIONS, TU_COBRA_QB); the eff_dt/exp_dt guard triggers, the updt_dt/updt_user_id stamping triggers and the role-gated master-group trigger; the stored procedures listed in §1; and the CMS Section 111 input/response table DDL plus the CMS file layouts (§6)	Data model, §1 recovery list, Section 111 scope
OQ-2b	Export the crontab from the Oracle host — a third home for business logic that neither the binaries nor the DDL will reveal (§1)	Retention, warehouse feed, claim-ID assignment
OQ-3	Recover the background executables running under the SFPIBACK identity (adjudication driver, bill/check runs, duplicate detection) and the separate continuously-running "COBRA background process" (§1). The identity is now known; the binaries are not — start from Resource\Background Processes on the network share	Core processing parity
OQ-3b	Locate the non-HOMER systems (§1): the FoxPro CP2100/B-notice application, newedi.exe, the ID-card VB application, the provider-load shell scripts, and the Access accounting/billing/stop-loss workbooks	Integration scope; unmanaged business logic
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
OQ-8	Is CMS Section 111 reporting happening today, and by what means? (§6) Escalate as a compliance question, not a scoping one — it has an owner in the business regardless of the migration timeline	Regulatory exposure; Section 111 design
OQ-9	Does the business want address standardization restored (new verification service) or dropped? The Melissa Data reference set lapsed in Nov 2002 (§5). Check the MDR UCR/R&C tables for the same lapse	Address quality; pricing accuracy
OQ-10	Confirm the master-group list is still exactly SFPI / M/III / IBA (§4); a fourth master group would be silently missing from three sales reports	Reporting correctness