Skip to main content

External Integrations

Catalog of every external touchpoint found in client code and configuration. All exchanges are file- or stored-procedure-based; no web services, message queues, or e-mail sends were found in the client source. File locations are user-configurable paths remembered in HOMER.INI / the codes table (code_key = 'APPPATHS').

1. Outbound claim EDI to repricing networks (HCNs)​

Direction: outbound file, with an inbound response file. Trigger: two-step, both user-initiated.

  1. Marking: In the claim editor a menu action inserts the claim into the staging table: SELECT count(*) ... FROM outbound_edi_transactions (dup check), then INSERT INTO outbound_edi_transactions (claim_id, claim_form_type, trading_partner_id) after the user picks a trading partner in w_claims_partners_select; a claim message (code 223, "Claim has been selected for outbound EDI to <partner>") is logged (claims/w_claim_editor line ~1115).
  2. Extract run: clmutils/w_edi_outbound_edi_processor ("Outbound edi main window"), button Outbound EDI Process: fixes up hcn_sys_key on staged rows (hard-coded DECODE(trading_partner_id,'PHCS',2373,'PHCSMTP',2004,2179)), retrieves d_edi_outbound_claims_to_process, and for each claim calls u_edi_outbound_edi.of_load_claim to explode the claim into the outbound_edi_* staging tables (claim, line, patient, subscriber, provider, procedures, occurrences, UB92/dental variants — see the d_edi_* DataWindows in clmutils).

Data/format: u_edi_outbound_edi.of_create_file writes one fixed-width flat file per HCN per claim-form type, named <hcn_sys_key><formtype>_mmddyyyy.txt, with record-type prefixes (header record "1000" + padded recipient name, then provider/patient/claim sections), into the directory configured in codes (APPPATHS / OUTEDI). After the file is written, staged rows with file_created = 'Y' are deleted from all outbound_edi_* tables.

Inbound response: button Inbound EDI Process on the same window lets the user pick the returned file and parses it as fixed-width lines (claim id pos 1-17, line seq 18-20, whole-claim and line pricing amounts, procedure code, claimed amount, service date; wf_process_file). Processed claims are updated with repricing results; failures are placed on hold or returned to the corrections queue with reason RFCP (n_cst_return_claim_to_corrections), and bad lines are written to a sibling .bad file.

Monitoring: clmrpt/w_clmrpt_outbound_edi_older_than_10_days (claims sent but not returned in 10+ days) and homersys/w_edi_report ("EDI submitted by group").

2. PBM (prescription benefit manager) member/eligibility feeds​

Direction: outbound files. Trigger: user-initiated per run from drugplan/w_drugplan_report_transfer ("HOMER- Prescription Plan Reports and Tranfers"), which branches by plan type: PCS, CVS/Caremark standard, and RxBenefits variants (ue_create_pcs_transfer, ue_create_pcs_std_transfer_cvs, ue_create_pcs_std_transfer_rxb).

Data/format: member eligibility extracts built from DataWindows such as d_pcs_trans_to_text, d_pcs_std_trans_to_text, d_restat_trans_to_text, written line-by-line to a .pcs text file; the PCS variant is documented in code as "the 160 Enrollment Format Version 2.8 as of 01/28/2010" (w_drugplan_report_transfer line ~379). Output path defaults from HOMER.INI [APP PREFERENCES] pcs_path / exp_path and is written back after each run. Supporting tables/DataWindows also exist for MagellanRx spreadsheets (base/d_magellanrx_*) and CVS troubleshooting (base/d_cvs_troubleshoot).

Accumulator "flag files": provider/w_provider_caremark_flag_transfer and provider/w_provider_optum_flag_transfer export deductible / out-of-pocket accumulator flag files per division (d_caremark_flag_file*, d_optum_flag_file*, with audit DataWindow d_caremark_report_audit).

Full field-level detail is covered in the drug-plans module documentation.

3. Health-care-network enrollment dumps (CIGNA, Aetna, MMO, HMS)​

Direction: outbound files; two stored-procedure call sites. Trigger: user submits from provider/w_hcn_enrollment_dump ("HCN Enrollment Dump"; choices for HCN vs. enrollment view, group filter, format drop-down, optional six-month restriction).

  • CIGNA: rows retrieved and formatted by n_hcn_dump_cigna.of_write_cigna, written to a text file at [APP PREFERENCES] hcn_path (w_hcn_enrollment_dump lines ~152-165, 695).
  • Aetna: the client executes DB stored procedures to build the extract — sfpidba.sp_aetna_enrollment (full) and sfpidba.P_BUILD_AETNA(:group, :exp_dt) (per group) — then retrieves d_hcn_aetna and writes AETNA<MMDDYYhhmm>.TXT (w_hcn_enrollment_dump lines ~179-194, 534). These are the only sp_aetna* call sites in the source.
  • MMO: executes SFPIDBA.SP_MMO_ENROLLMENT(:seq_no) against a second database connection itr_warehouse (the [ORACLE WAREHOUSE] connection in HOMER.INI, an Oracle 8 warehouse instance) and retrieves the result for output (w_hcn_enrollment_dump line ~279).
  • HMS: provider/w_hms2 ("HCN Enrollment Dump" / titled "HMS REPORTING"), w_hms_reporting, w_hms_report produce HMS extracts; w_hms2 also uses the warehouse connection.

4. Postal address standardization (Melissa Data "Mailers+4")​

Direction: in-process COM/OLE component, no data leaves the site. Trigger: address entry/validation on any screen embedding the component.

  • mailobj/nvo_mailers ("nvo that interfaces to the mailer's+4 software") connects to OLE object AddressObject.AddressCheck, sets a license string, and initializes with three data paths. Paths and key come from codes (APPPATHS values, incl. MAILERSKEY) with fallback to HOMER.INI [Paths] mp4_path / regional_path / national_path / key (the INI points at C:\MelissaData).
  • of_standardize_address(ref str_mailers_address) standardizes an address structure (mailshr/str_mailers_address).
  • homersys/u_mailers is a visual variant hosting the OLE control next to an address DataWindow; client/nvo_standardize_address wraps standardization for client screens.
  • Consumers (about 20 windows): enrollment maintenance, quick enroll, COB maintenance, provider/vendor/HCN maintenance, client and carrier maintenance, claim editor, UM entity maintenance, address search/link (grep nvo_mailers|u_mailers across _re/src).

Documented business rules for address standardization (User Guide ch. 22, "Addresses") — the guide confirms and extends what the code shows:

  • Two address types only, STANDARD and PROVIDER, each keyed by an internal address system key; the original multiple "standard" subtypes were deliberately collapsed. Goals: USPS-preferred print format (lower postage) and de-duplication (many providers share one hospital address).
  • Entry convention: line 1 = street address or PO box, line 2 = suite/apartment; if only line 2 is filled the application moves it to line 1. An address may not contain both a PO box and a street address — that must be entered as two separate addresses (the standardizer cannot handle the combination).
  • Standardization is attempted on all addresses (the per-address opt-out checkbox was dropped); duplicate checking is likewise performed on all addresses, standardized or not — the two are independent concepts.
  • Duplicate checking scope: within address type, and for PROVIDER also within client segment. The same address system key may therefore be shared across referencing entities.
  • Non-standardized addresses must display with a red background — the only visual distinction; std_addr_flg has values U (unchecked), N (non-standardized), Y (standardized).
  • A failed standardization is not a save failure: the user is told the address could not be standardized and asked whether to save anyway; duplicate checking and saving still occur. A state changed by standardization is not treated as an error by the component — the calling screen must detect it.
  • Foreign addresses carry a flag and are exempt from standardization.
  • Save semantics (of_save_address with orig_addr_no/force_update): new addresses insert; a modification with a single reference updates in place; a modification of a multiply-referenced address inserts a new record and the user chooses which references move — matching the shared-key model.
  • Mailing-label print rule: convert to all-capitals, no punctuation, USPS abbreviations; addresses are stored as returned from standardization and converted only at label print time.

Discrepancies for SME confirmation:

  • The user guide (ch. 22) describes Mailers as a distributed PowerBuilder server application reached over WinSock (hosts entry MailersServer, services entry mailers 10025/tcp, Connection/CreateInstance through the mailprxy/mailshr PBLs). The current code embeds the Mailers+4 engine in-process via OLE (AddressObject.AddressCheck in mailobj/nvo_mailers) — the distributed tier appears to have been retired; code wins for behavior, but confirm no Mailers server survives anywhere.
  • The user guide (ch. 22) records that the Melissa Data subscription was not renewed as of Dec 2002 (last data set Nov 2002, expiring after one year) pending a cost review, yet the code and HOMER.INI still configure the component. Confirm with operations whether standardization is live, running on stale data, or effectively disabled.

Note: despite the names, mailobj/mailshr are postal-address libraries; no e-mail integration exists in the application code. The only MAPI mail service objects are unused framework stock (pfemain/n_ms, pfcmain/pfc_n_ms).

5. Group eligibility / enrollment exports​

  • enroll/w_enroll_group_export ("window to export group eligibility"): writes a fixed-format eligibility file for a master group, field by field (creation date, subscriber SSN, member id, dependent SSN, relation, name, gender, address, birth/hire dates, coverage, level, option, effective/expiry, division). Output path defaults from [APP PREFERENCES] enrolldump_path; also offers a DataWindow SaveAs() export (lines ~149-339, 544).
  • Matria disease-management feeds: funding/w_burns_enrollment_dump ("Create Matria Enrollment Files") and funding/w_burns_claim_dump ("Create Matria Claim Files"), plus enrolrpt/w_dep_audit (same comment); the base library carries an extensive family of d_burns_* extract DataWindows (per-coverage enrollment dump variants).

6. Banking​

  • Positive pay: funding/w_pospay exports check-issue data as CSV files via DataWindow SaveAs(..., CSV!) (three export variants, lines ~275, 427, 694) for delivery to the bank.
  • Bank reconciliation import: funding/w_fund_bank_reconciliation_file ("window for processing the bank reconciliation files") — user picks the bank's file (GetFileOpenName), which is read and matched against the check register (line ~359).
  • OME-RESA: funding/w_bill_ome_wire_transfer ("OME-RESA Wire Transfer") and funding/w_fund_omeresa_monthly_claims_data produce files for a school consortium client (e.g. SaveAs("C:\homer\omeresa_folder.txt", Text!)). These record/report wire-transfer billing; no bank transmission occurs in code.

7. State withholding reporting​

funding/w_withholding_reports ("Withholding Reports") prints/prepares claims-withheld and census reports per state and date range (d_withholding_reports_claims, d_withholding_reports_census). The HOMER.INI key [APP PREFERENCES] witholding=\\linuxpdc\public\Accounting\Withhold\ points at a shared accounting folder, but no code reads that key (grep found only the INI); see open questions.

8. Check-register archive​

[SETTINGS] archive_dir (production value U:\mis\archive; default written by homersys/n_cst_sfpiappmanager line ~814). funding/w_fund_manual_check archives each manual-check register as a text file <archive_dir>\YYYYMM\A<report_sys_key>.txt (lines ~274-310). A similar archive of HIPAA certificates exists but is commented out (enroll/w_enroll_hipaa_generate lines ~520-536).

9. Diagnostics and local logs (workstation-side, not external)​

  • SQL spy log: [SETTINGS] spy_file=c:\Homer\sql_spy.txt (homersys/n_cst_sfpiappmanager line ~798).
  • Error log: [ErrorLog] logfile='c:\Homer\errorlog.txt'.

10. Inbound claim EDI intake (operational pipeline outside the client)​

(User Guide ch. 22, "EDI Processing") — the guide documents the end-to-end inbound claim EDI operation, which lives almost entirely outside the 53-library client and explains why no inbound-claim EDI transport code was found in the extract:

  1. Retrieval, per trading partner (manual, daily where applicable):
    • PCS (drug claims): staff retrieve two files (C58300.gen Altru/USFHP, PTH2070.gen SFPI; gen = year digit + incremental transmission number) to a network share; ZIP archives kept in Archive1\Pcs. USFHP processing (load into PCS_CAT_IN, run usfhp_pcs.sql) inactive as of 07/01/2003; the Wang leg is obsolete. A Unix script pcs <gen> runs SQL*Loader into a temporary table then two SQL scripts into the permanent table, after which "MIS runs the normal PCS EDI load utility."
    • Healthstar (HCFA + UB92 repriced claims): dial-up via HyperTerminal, two files received per day.
    • IPN / ENVOY (WebMD): FTP pickup from ftp.interpaynet.com, PGP-protected ZIPs.
    • Medical Mutual (MMO): FTP pickup (edi_pickup), PGP-encrypted files sfmmddhhnn[I|P].pgp; 997 acknowledgement files (hcf997, ub997) are renamed, PGP-encrypted and returned to the partner's edi_dropoff.
    • Scan Solutions: FTP pickup from SFPI's own FTP site (/incoming/scansolutions), PGP files pppyyyymmdd837tt.txt.pgp — X12 837 dental/medical claims.
    • HFN: FTP pickup (/selffund), PGP files per HFN group (Chicago 45189 / Cleveland 47003).
  2. Translation: files are run through Gentran Server / "Mentor" (a commercial EDI translator) to produce ASCII flat files with naming conventions like hstarub92.mmdd, neichcfa.mmdd, hcfYYYYMMDD.txt.
  3. Network split: a Perl script u:\edi\splithcn.pl (driven by callsplit.bat / callsfpi.bat) splits each translated file by repricing network into tyyyymmdd#n.txt (t = U/H/D for UB/HCFA/Dental, n = network suffix); a missing suffix is an error condition. Counts are logged in per-partner edi_counts spreadsheets.
  4. Load into HOMER: a separate PowerBuilder application, the "EDI Claim Processor" (R:\Edi Applications\NewEdi\newedi.exe) — not part of this extract (grep confirms no reference in _re/src) — loads each flat file ("Load File"), then creates claims ("Create Claims"), writing a Submitter Log row per file (file name, archive file, claim form, date loaded, claim count, first/last claim id created, creation date). It requires HOMER.INI [EDI] keys ArchiveDir and ArcBatDir; a DOS batch arcfile.bat moves the processed file into the archive (8.3 directory names required).

Received dates on EDI claims are set by the load process to the run date (User Guide ch. 22, "Altering Register/Check dates on Checkruns").

Credentials, phone numbers and FTP hosts quoted in the guide are 2002-2003 vintage and certainly stale; the shape of the pipeline (manual pickup, PGP, Gentran translation, Perl network split, separate loader app) is the durable fact for migration.

11. CP2100 (IRS B-notice) processing — no client implementation​

(User Guide ch. 22, "CP2100 Processing") — an annual IRS-mandated process (Pub. 1281 backup withholding on missing/incorrect name-TINs): SFPI/M3 issues 1099s to providers on behalf of clients and must resolve TIN/name discrepancies via first and second B notices, follow-up letters (default 10 calendar days later), returned-notice tracking (including a "stop pay switch" set on the Wang), and log-driven reports. The guide specifies a menu of options (enter notices received, track returns, generate notices, reload the "krisfile" from the Wang, print reports) over FoxPro tables (bnot, bnotq, secondb, secbq, cp2100, tinfile, krisfile, irs1099) plus an Oracle bnotice table definition.

Status: no CP2100/B-notice code exists anywhere in the 53-library extract (grep of _re/src). This is an operationally documented side process (legacy FoxPro/Wang era) with no HOMER client implementation; only the 1099 issuance itself touches HOMER (print_1099s, funding/reprint libraries). The migrated platform must decide whether to absorb B-notice tracking or leave it external — see Migration Considerations.

12. Client termination — outbound notifications (manual)​

(User Guide ch. 22, "Terminating Clients") — terminating a client triggers a manual checklist of external notifications with no system support: produce a final enrollment file and accumulator file for the client; notify PPOs/HMOs, EDI companies, drug companies, and the stop-loss carrier of the termination. In-system steps: members terminated, then the group terminated in client maintenance with an optional runout period; claims processing continues until the term/runout date, after which claims are flagged "should be returned"; the system begins deleting claims for terminated clients six months after the termination (or runout end) date — see the claims delete process on Batch Processing.

13. CMS Section 111 mandatory Medicare reporting — no client implementation​

Direction: outbound file to CMS, with an inbound response file. Trigger: manual, per reporting period (monthly, from the file names in the procedure). (User Guide ch. 1, "CMS Section 111")

Section 111 of the MMSEA requires a group health plan's responsible reporting entity to report covered members to CMS so Medicare can coordinate as secondary payer. The guide documents the operator procedure in full but not the file layouts.

Outbound — sending Section 111 files to CMS (User Guide ch. 1):

  1. Open the window named "TIN reference File" and press Create File (the preferred file name defaults itself). Open the resulting file and remove the trailing newline at the end.
  2. Open the window named "MSP Input File" and press Write File (file name again defaults). Open the file and remove the trailing newline.
  3. Press Backup Table, saving as SECTION_111_MSP_INPUT_YYYYMM_PreApply.txt (any distinct name will do).
  4. Press Apply Input File and select the file produced by Write File.
  5. Press Backup Table a second time, saving as SECTION_111_MSP_INPUT_YYYYMM_PostApply.txt.

The guide is emphatic about why the backup precedes the apply: "there is a good chance that an error will occur and work must be rolled back", in which case Reload Table restores the table exactly from the most recent backup — "always be sure to backup the table no matter how trivial or certain you are of a process working" (User Guide ch. 1). The apply step is therefore a destructive, non-transactional table update whose only undo is the manual file backup.

Inbound — receiving the CMS response file (User Guide ch. 1):

  1. Open the window named "CMS Response File". Because one or more records in the file will likely fail, take a good backup first and test before loading.
  2. Press Test File and select the file from CMS. This produces a two-column file: the query that would be run (an INSERT or UPDATE) and that query's status. During testing each query is attempted but never committed; a working line reports None, otherwise the SQL error text is shown. The point of the test is that the final load stops at the first erroring query, so the test reveals all failures at once.
  3. Once all issues are resolved, press Load File.
  4. On completion the operator may save a record of each disposition code into a separate file (kept separate for easier post-processing) together with all queries that were run.

Status: no implementation exists in the extracted client code. A sweep of all 53 libraries in _re/src found no object — by name or by content — containing section 111, section_111, MMSEA, MSP (as a term rather than a developer's initials), TIN reference, CMS response, HICN, MBI, RRE, TPOC, ORM, BCRC/COBC or disposition code, and none of the windows ("TIN reference File", "MSP Input File", "CMS Response File") or buttons ("Backup Table", "Apply Input File", "Reload Table", "Load File") named in the procedure exist anywhere in the source. The only CMS strings in the extract are a CMS publications hotline number and a cms.hhs.gov URL printed as static text on the HIPAA notice form (enroll/d_hipaa_form).

Medicare-aware objects that exist but are not Section 111 reporting, and should not be mistaken for it:

  • COB maintenance treating Medicare as a plan type, with the rule "only one plan type for a given period except for plan type of 'Medicare'" (enroll/w_enrollment_cob_maint; the whole cob library, dddw_cob_*).
  • enrolrpt/d_census_medicare_with_cob (census report filtered on COB status reason Medicare) and clmforms/d_claims_frm_medicare_eob (a letter requesting an EOMB from the member).
  • ESRD coordination inside adjudication (clmsproc/d_adjd_esrd_without_cob, clmsproc/n_claim_adjudication) — pricing and coordination, not reporting.
  • The PBM eligibility extract drugplan/w_drugplan_report_transfer, whose fixed-width record carries Medicare HIC, Medicare participation indicator, Part D thru date, ESRD from date, Part B flag from date and hospice from date. This is the object most likely to be mistaken for Section 111 work: its destination is the pharmacy benefit manager, not CMS.

Section 111 reporting therefore lives outside this PowerBuilder client — most plausibly in database-side tables and procedures driven by a separate utility, given that the procedure manipulates a persistent table (backup, apply, reload) and that the response load is described purely in terms of generated INSERT/UPDATE statements. Recovering it requires the Oracle schema (a SECTION_111_MSP_INPUT-style table and the CMS file layouts), not the PowerBuilder extract; see Open questions.

Requirements​

  • INT-1 Users with claim-edit rights shall be able to mark a claim for outbound EDI to a chosen trading partner exactly once; the action shall be recorded as a claim message and staged in a transaction table (claims/w_claim_editor).
  • INT-2 An outbound-EDI run shall extract all staged, unprocessed claims into per-recipient, per-claim-form-type flat files in a configurable directory, mark rows processed with a process date, and purge staging rows whose file was created (clmutils/w_edi_outbound_edi_processor, clmutils/u_edi_outbound_edi).
  • INT-3 The system shall import the partner's fixed-width repricing response file, apply pricing to claim lines, route unprocessable claims to hold/corrections with a distinct reason code, and produce an error file of rejected lines (clmutils/w_edi_outbound_edi_processor).
  • INT-4 The system shall report claims sent for outbound EDI that have not received a response within a threshold (10 days) (clmrpt/w_clmrpt_outbound_edi_older_than_10_days).
  • INT-5 The system shall generate PBM member-eligibility export files in each partner's fixed format (PCS 160-byte v2.8, CVS/Caremark, RxBenefits, Restat) to a configurable, remembered output path (drugplan/w_drugplan_report_transfer).
  • INT-6 The system shall generate deductible/out-of-pocket accumulator flag files per PBM (Caremark, Optum) with an audit listing of what was sent (provider/w_provider_caremark_flag_transfer, w_provider_optum_flag_transfer).
  • INT-7 The system shall produce network enrollment extracts per partner format (CIGNA, Aetna, MMO, HMS), including server-side extract builds invoked from the client (Aetna, MMO), with the MMO build running against a separate warehouse database (provider/w_hcn_enrollment_dump, provider/w_hms2).
  • INT-8 Address entry screens shall offer postal address standardization through a pluggable address-verification component licensed and configured centrally (mailobj/nvo_mailers, homersys/u_mailers; HOMER.INI [Paths]).
  • INT-9 The system shall export group eligibility and disease-management (Matria) enrollment/claim files on demand in fixed formats (enroll/w_enroll_group_export, funding/w_burns_*).
  • INT-10 The system shall export positive-pay check files (CSV) and import bank reconciliation files to match cleared checks (funding/w_pospay, funding/w_fund_bank_reconciliation_file).
  • INT-11 Manual-check registers shall be archived as durable files in a month-partitioned archive directory (funding/w_fund_manual_check; [SETTINGS] archive_dir).
  • INT-12 File output/input locations shall be configurable per installation and remembered per user after each run (the pcs_path/exp_path/hcn_path/enrolldump_path read-then-write pattern, e.g. provider/w_hcn_enrollment_dump line ~743).
  • INT-13 The system shall import inbound claim EDI flat files (HCFA, UB92, dental) produced by an external translation step, create claims from them, and record a per-file submitter log (file name, archive, claim form, date, claim count, first/last claim id created); processed files shall be archived automatically (User Guide ch. 22, "EDI Processing" — EDI Claim Processor application; external to this extract).
  • INT-14 Inbound claim files shall be split by repricing network before load, and a file that cannot be attributed to a network shall be treated as an error requiring intervention (User Guide ch. 22 — splithcn.pl network-suffix rule).
  • INT-15 Address standardization shall follow the documented rules: apply to all non-foreign addresses, never block a save on standardization failure (warn and allow), check duplicates within address type (and client segment for provider addresses), flag each address U/N/Y, and visually mark non-standardized addresses (User Guide ch. 22, "Addresses"; mailobj/nvo_mailers).
  • INT-16 The system shall support CMS Section 111 mandatory Medicare reporting: produce a TIN reference file and an MSP input file for the reporting period, apply the input file to the MSP reporting table with a restorable backup taken before and after the apply, and load the CMS response file — first in a non-committing test mode that reports the pass/fail status of every generated INSERT/UPDATE so all failures are visible before a real load (which halts on the first error), then producing a per-disposition-code output plus a log of every query run (User Guide ch. 1, "CMS Section 111"). No implementation exists in the extracted client source — see §13.

Open questions​

  • Withholding export: the INI points at an accounting share (witholding=\\linuxpdc\...), but no code reads the key; either a screen was retired or the share is used manually after printing funding/w_withholding_reports. Confirm the current withholding remittance process with accounting.
  • File delivery — partially resolved: every export lands on a local/shared drive; for inbound claim EDI the user guide documents the transport in full (manual FTP/dial-up pickup per partner, PGP decryption, Gentran/Mentor translation, Perl network split, separate loader app — see §10) (User Guide ch. 22). Outbound delivery (PBM eligibility files, HCN dumps, positive pay, MMO 997 acknowledgements aside) remains a manual/desk procedure outside the application; confirm current transport per partner with operations, since the guide's credentials and hosts are 2002-2003 vintage.
  • Hard-coded partner keys: the outbound-EDI run hard-codes HCN system keys for PHCS/PHCSMTP (w_edi_outbound_edi_processor DECODE); confirm the live trading-partner list (provider/w_edi_partners, w_edi_clearinghouse maintain edi_trading_partners / edi_clearinghouses).
  • CMS Section 111 — where does it live? The guide documents a working operator procedure over three named windows, yet no Section 111 / MSP / CMS reporting object exists anywhere in the 53-library extract (§13). The procedure's shape (create file, back up a table, apply the input file to that table, reload from backup, test-then-load a response file as SQL statements) points at an Oracle table plus a separate utility or later application module. Confirm with MIS which tool runs Section 111 today, and obtain the SECTION_111_MSP_INPUT-style table DDL and the CMS input/response file layouts — none are in the guide. Until then, Section 111 is a known gap in the migration inventory, not an absent obligation: it is a federal reporting mandate.
  • Stored-procedure internals: sp_aetna_enrollment, P_BUILD_AETNA, and SP_MMO_ENROLLMENT live in the database and were not available; their logic must be reverse-engineered from the DB during migration.
  • Warehouse database — resolved: the [ORACLE WAREHOUSE] connection (Oracle 8, catalog owner sfpidba) used by w_hcn_enrollment_dump/w_hms2 is the SFPW reporting warehouse documented in the user guide (User Guide ch. 22, "Data Warehouse Contents and Processing"): a report-friendly copy of production HOMER data, holding only claims that have a FUND_REGISTER row (waivered claims included for stop-loss reporting). It is fed two ways — Oracle snapshot replication for lookup/reference tables (diagnosis/procedure codes, providers, vendors, plan packages, stop-loss parms, etc., refreshed nightly) and procedural replication for claims/groups/enrollment via TDW* after-triggers, DW_CLAIM_TRANS/DW_GROUP_TRANS/DW_MEMBER_TRANS linking tables and PKG_DW_SYNC, run from cron. Deleting the parent row in production deletes it from the warehouse. See the database-implemented-functionality section of As-Is Architecture for the full mechanism; the warehouse table designs (DW_FUND_REGISTER, DW_CLAIMS, DW_CLAIM_LINE_RULES, …) are specified in the guide.