As-Is Architecture

Topology

• Client: a 32-bit PowerBuilder application (originally PB 11, migrated to Appeon PowerBuilder 2022 R2 per homer.xml), deployed as homer.exe plus one .pbd per library. All business logic runs on the client.
• Database: Oracle, connected via native driver with AutoCommit=1 and DisableBind=1 (literal SQL). Connection profiles live in HOMER.INI ([ORACLE PRODUCTION], [ORACLE TEST], …); credentials are entered at login.
• No middle tier — but not "no server-side logic." The database is the only shared state, and beyond the handful of stored procedures the client calls (e.g. sp_aetna_enrollment, sp_rule_defs, sp_rule_contingency_defs) the user guide documents a substantial invisible server-side layer of triggers, packages and scheduled jobs — see Database-implemented functionality below. Separate background executables (adjudication engine, check runs, EDI loader) run from a network share; see Batch Processing.

Application framework

• Built on the PFC (PowerBuilder Foundation Class) framework — the pfc*/pfe* libraries — providing MDI frame services, DataWindow services (sort, filter, row management), error handling, and transaction management. These 8 libraries are infrastructure, not business logic, and do not need functional migration.
• base extends PFC with HOMER's own ancestors: w_sheet (standard MDI sheet), w_maint_selection/w_header_detail_maint (standard maintenance-screen patterns), u_homer_dw (standard DataWindow control), w_homer_wizard (wizard pattern). Most of the 546 windows inherit from these, so screens share consistent behavior: select-then-edit flows, save/cancel semantics, row-level validation on save.
• The application object (homer/homer.sra) instantiates n_cst_sfpiappmanager (homersys), which handles login, INI reading, connection, and opens the MDI frame w_sfpi_frame.
• Inheritance was mandated policy, not accident: "All objects will be inherited from the HOMER system library or the PFE libraries" — standard command buttons, sheets, and DataWindow controls are inherited, never re-created, so screen dimensions and behavior stay uniform (User Guide ch. 23, "Inheritance", "OOP"). Security uses "the PFC Security tables and application" (User Guide ch. 23, "Security Standards"), matching the security-model analysis.

Naming conventions and programming standards (User Guide ch. 23)

Chapter 23 (Programming Standards, rev. 1998-2010) codifies conventions the extract visibly follows — useful when reading the source and when preserving conventions in generated migration artifacts:

• Object prefixes: w_ window, d_ DataWindow object, u_ visual user object, n_/n_cst_ standard/custom class objects, m_ menu, s_ global structure; composed as w_ + module name + window title, d_ + module name + query description, u_ + module name + functional description. Variable scope prefixes (g/i/l/s/a) plus Hungarian datatype prefixes (ls_, ll_, dw_, ds_, …).
• An older 8-character naming scheme (positions 1-3 category, 4-6 program, 7-8 version; categories ACT/ADM/CLM/CM_/ENR/MKT/PLN/PRV/SYS/UR_, programs LOG/MEN/WIN) is documented in ch. 22 — pre-PowerBuilder-convention vintage; window names in the extract follow the ch. 23 w_module_title style instead.
• Transaction discipline: "Do not issue direct COMMITs and ROLLBACKs" — use of_commit() / of_rollback() on the shared n_tr (SQLCA) object so DB errors are logged; never leave "hanging" transactions, because the MDI app shares one transaction object and another open sheet can inadvertently commit them. This is the design intent behind the client-sequenced commit pattern noted under technical debt.
• Embedded SQL isolation: embedded SQL should be avoided or confined to dedicated SQL objects (u_app_sql and per-area equivalents), with DataWindows/DataStores preferred, explicitly "to make conversion to a new or updated database simpler."
• SQL performance rules: no five-plus-table joins; index hints for IN lists; avoid functions on indexed columns in WHERE clauses; explicit IS NOT NULL predicates on nullable indexed columns (the claims LOCK_SEQ_NO lesson).
• MessageBoxes always via gnv_app.inv_error.of_message (PFC error service); sheets opened by string name through OpenSheetWithParm (the mechanism that makes system_menu-driven navigation possible).
• Environment/build: source under SCC (PB source control; earlier Object Cycle), builds staged through R:\PB\DISTRIB\Staging → Test → Sync\SFPI (production), source snapshots zipped per build; version scheme major.minor.revision. Background-process executables deploy separately to Resource\Background Processes.
• Documentation rule: "All programming functionality must be documented in the Homer Users manual. Each module has its own chapter" — i.e. this user guide was the system's requirements record, which is why it fills the gaps client-code analysis flagged.
• Known Oracle date pitfall (ch. 23 Appendix B): with bind variables enabled, PowerBuilder DATE variables round-trip invalid dates into Oracle via OCI (TOAD even displays them as valid); the standard is to use DATETIME variables for all DB dates. This is corroborating context for the DisableBind=1 (literal SQL) setting observed in HOMER.INI.

Data-change "inheritance" model (User Guide ch. 22)

The guide defines the group-hierarchy inheritance the enrollment/funding screens implement — important because it is copy-based, not referential:

• Two distinct hierarchies exist, Client Maintenance and Funding; each inheritable attribute belongs to exactly one. Both share top (no parent) and bottom (no children) levels; master and bottom groups cannot be created via Funding.
• Pull inheritance: on adding a group, the user may copy none/some/all date-effective information from another group (default: the parent). Push inheritance: on attribute change (or on demand without a change), the user picks inheritance sets × descendant groups to push to. Deletes are never propagated; key-column changes never propagate.
• Except for addresses — the only truly shared objects (one address row, many referencing entities) — "inherited" data is duplicated per group, and later edits do not ripple. Rows usable only at the lowest (eligibility) level must be filtered by level for any processing (reporting, billing).
• The rules are hard-coded in the application, deliberately not table-driven; the DBA checklist requires notifying the inheritance owner and the audit-trail owner when adding columns.
• A group without a parent (a new master) can only be created by a role with the specific grant — enforced by a database trigger using the Oracle role as prerequisite.
• Related ch. 22 note ("Adding new Master Groups"): master groups are rows with null parent_group_id, and three code sites hard-code the master list (w_census_rpt_args_sheet open event, d_annlstmt_group_select SQL, n_cst_sales_report_wizard) — a change checklist item until made logical.

Database-implemented functionality (User Guide ch. 22)

The guide's "Database Implemented Functionality" and adjacent sections enumerate the server-side layer that client-code analysis could only infer. Everything below lives in Oracle (or cron) only and must be recovered from the database during migration:

Column-maintenance and validation triggers

• updt_dt / updt_user_id columns are populated by triggers with the system date and the Oracle login — the client never sets them.
• Wherever an eff_dt/exp_dt pair occurs, a trigger enforces that a new or updated eff_dt is no earlier than the latest exp_dt for rows with the same key.
• Parent-to-child cascade triggers propagate expiration-date updates down the enrollment hierarchy.

Mutating-table prevention — SP_MUTATION_PREVENT package

Cascading parent→child updates would trip ORA-04091 (mutating table) because child triggers re-validate the parent. The SP_MUTATION_PREVENT package keeps session-level flags (default 'Y'): the parent trigger sets the flag to 'N', updates the child rows, and resets it; the child trigger skips parent validation while the flag is 'N'. Documented flags:

Flag	Set in (parent trigger)	Checked in (child triggers)
group_fire_trigger	group_member_cat	grp_mbr_cat_val, member_cat_val
grpcov_fire_trigger	group_coverages	grp_covr_elig_rules, elections
grp_fire_trigger	grp_mbr_cat_val	member_cat_val
mbr_eff_fire_trigger	(obsolete, no longer used)	—
mbrelig_fire_trigger	member_eligibilities	member_cat_val, elections, coord_of_bens, election_coverages, elect_covr_pcp
coverage_fire_trigger	elections	election_coverages
rider_fire_trigger	elections	election_riders
grpcatval_fire_trigger	grp_mbr_cat_val	grp_covr_elig_rules

Named example triggers: TU_MEMBER_ELIGIBILITIES, TU_ELECTIONS.

Audit trail — TUA/TDA triggers and PKG_AUDIT_CONFIG

• After-update and after-delete triggers (names prefixed TUA/TDA; inserts deliberately not audited) on enrollment- and group-related tables write to AUD_TRAIL_GRP (keyed by GROUP_ID) and AUD_TRAIL_MBR (keyed by MEMBER_SYS_KEY): when, which table, table key, update vs. delete, who, and a chg_desc of column name with before/after values, plus an optional application comment.
• PKG_AUDIT_CONFIG package — 3 session variables with setter procedures (each returning 0 success / -1 failure):
  • session_audit_flg via AUDIT_FLG('Y'|'N', :rc) — suppress or enable audit rows for the session;
  • wang_transfer_flg via WANG_FLG(...) — suppress or enable Wang transfer rows (as of Aug 1999 implemented for elections changes only);
  • chg_comment via CHG_COMM('text'|NULL, :rc) — comment stamped into audit rows.
• Auditable tables are identifiable by their TUA/TDA triggers in the Oracle dictionary (dba_triggers).

Legacy-sync and warehouse triggers

• HP-to-Wang sync (historical): after-triggers on enrollment changes populate WANG_MEMBER_TRANS for a nightly transfer to the Wang; pending ID-card requests rode the same nightly FTP.
• Warehouse procedural replication: after-row triggers prefixed TDW on every warehoused table queue work into DW_CLAIM_TRANS (driven by claim_id; rows enter the warehouse only once a FUND_REGISTER row exists), DW_MEMBER_TRANS (member_sys_key) and DW_GROUP_TRANS (group_id), each carrying NEED_EXTRACT_FLG, LAST_UPDT_DT, ACTION_IND (U/D). PKG_DW_SYNC (procedures CLAIMS, ENROLLMENT, plus group refresh and Update_claim) drains these queues over DB links to the SFPW warehouse, with commit-count/row-count/end-time bounds; claims are locked during refresh. Reference tables replicate via Oracle snapshots. Scheduling is Unix cron (dwall_night.sh, dwall_morn.sh, dwall_sat.sh on the production host; refresh.sh on the warehouse) with self-checking (errorchk.sh) and e-mailed results.
• Claims delete propagates to the warehouse by setting dw_claim_trans.action_ind = 'D'.

Stored procedures and packages called or documented

Object	Purpose	Citation
pkg_del.del_mbr_elig	Deletes a subscriber's coverages, elections, and eligibility periods (steps 1-3 of the member-division-move update sequence)	ch. 22 "Member Move Function"
SP_MUTATION_PREVENT	Session flags to avoid mutating-table errors (above)	ch. 22
PKG_AUDIT_CONFIG	Audit/Wang/comment session switches (above)	ch. 22
PKG_DW_SYNC	Warehouse procedural refresh (above)	ch. 22
sfpidba.sp_aetna_enrollment, P_BUILD_AETNA, SP_MMO_ENROLLMENT	Partner enrollment extract builds invoked from the client	code + Integrations
SP_POPULATE_REFRESH, full_refresh package (truncate_tables, disable_constraints, enable_constraints)	Test-database refresh driven by REFRESH_EXTRACT_PARMS / REFRESH_PROCESS / REFRESH_TABLES / REFRESH_MOVE_* control tables	ch. 22 "Refresh Test Database Processing"
Provider-update load: procedures Add_new_record, Report_error, Insert_provider_panel; function Std_prov_queries; package Std_prov_seq_val; sequence Std_provider_seq; scripts std_prov.sql / std_hosp.sql / int_perm.sql	Server-side HCN provider file load into STD_PROVIDER staging with match/add/consolidate rules, restartable via LAST_SEQ_NO_TBL, errors to ERROR_TABLE	ch. 22 "Provider Updates"
Group-hierarchy trigger	Blocks creating a parentless group unless the session role has the specific grant	ch. 22 "Inheritance of changes"
Claim-ID assignment	Claim IDs assigned from Oracle (sequence); per-source starting number reset daily by the nightly cycle or a Unix cron job	ch. 22 "Claim Numbering Scheme"

Scheduled/background database work

Daily claims delete (60-day-aged deletion marks, 39 tables in referential order), terminated-client claim purge (term/runout + 6 months), warehouse sync and snapshot crons, claim-ID reset — detailed with schedules in Batch Processing.

Migration significance: every behavior above is invisible in the client source; the DDL extract (triggers, packages, crontabs) is the only way to recover exact logic. The trigger inventory here — TUA*/TDA* audit, TDW* warehouse, TU_* cascade/validation, updt-column and eff/exp-date triggers, the role-gated hierarchy trigger — is the checklist to reconcile against dba_triggers / dba_source.

Navigation model (important for migration)

Navigation is data-driven: the system_menu table stores the function tree (captions, window names, parameters). w_system_menu reads it, filters by the user's security class, and opens sheets via OpenSheet/OpenSheetWithParm on the window name stored in the row. Menu bars on individual sheets are mostly local actions (save, close, print), not global navigation.

Consequence: the definitive list of user-reachable functions lives in database data, not code. The migration must export system_menu (and the security tables that filter it) to enumerate the production navigation tree. Until then, the Traceability Matrix enumerates every window and what it touches, which is a superset of what any user can reach.

Configuration

HOMER.INI sections observed:

• [SETTINGS] — logo/help paths, DB profile selection, SQL spy log, archive dir
• [APP PREFERENCES] — behavioral flags (e.g. directtomember, memberidletter), export paths for PBM/enrollment/withholding file exchange, client segment=SFPI
• [ORACLE *] — named connection profiles
• [Paths] — Melissa Data address-data locations and license key
• [ErrorLog] — client-side error log path

External integration points

Integration	Mechanism	Evidence
PBM — PCS/Caremark	File/table exchange of eligibility & drug claim transactions	drug_covr_pcs_*, caremark_edi_table, pcs_path/exp_path INI entries
Outbound claim EDI	Staging tables written per claim/line/provider/patient	outbound_edi_* tables (claim, line, provider, patient, ub92, dental)
Aetna enrollment	Stored procedure	sp_aetna_enrollment
Address validation	Melissa Data COM/DLL objects, local data files	mdAddr.dll, AddrObj.dll, [Paths] in INI
Check printing & 1099s	Printed forms from DataWindows; print_1099s table	funding/reprint libraries, 1099-NEC form images in app dir
Withholding export	File drop to accounting share	witholding= INI path

Known technical debt / migration-relevant constraints

• Client-side 2-phase logic with AutoCommit=1 means multi-statement operations rely on application sequencing, not transactions — behavior to examine per workflow during migration.
• DisableBind=1 (literal SQL) implies the schema tolerates implicit conversions; exact server-side types must come from DDL.
• Heavy use of Oracle-specific SQL (outer-join (+) syntax, dual, sequences) inside DataWindows.
• Local file-system dependencies (INI, network shares, Melissa Data files) are per-workstation state.