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

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

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

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: every export lands on a local/shared drive; the actual transmission to partners (FTP, portal upload, mail) happens outside the application and is invisible to this analysis.
  • 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).
  • 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: the [ORACLE WAREHOUSE] connection (Oracle 8, catalog owner sfpidba) is used by w_hcn_enrollment_dump/w_hms2; scope of the warehouse schema is unknown.