Drug Plans & PBM Interfaces

Purpose

This module maintains prescription (drug) plan definitions, stages member eligibility "transfer" records per pharmacy benefit manager (PBM), and produces fixed-width text files that are sent to the PBMs. It is the bridge between HOMER's enrollment data (election_coverages with benefit_coverage_type_cd = 'Drugs') and the external PBM vendors that adjudicate drug claims.

Primary library: drugplan (6 windows, 51 DataWindows). One closely related screen lives in clmutils (Caremark inbound claim-adjustment worklist, caremark_edi_table).

Data direction, as determined from the code:

• Eligibility: OUTBOUND only. Every workflow in the drugplan library stages member add/change/termination rows in a per-vendor staging table and then writes a text file with FileOpen(..., Write!) / FileWrite (drugplan/w_drugplan_report_transfer, events ue_create_exp_transfer, ue_create_pcs_transfer, ue_create_restat_transfer, ue_create_pcs_std_transfer, ue_create_pcs_std_transfer_cvs, ue_create_pcs_std_transfer_rxb). No file-import (read) code exists in this library.
• Drug claims: INBOUND, but not through this library. Caremark drug claim/adjustment records arrive in caremark_edi_table (populated outside the extracted PowerBuilder code — see Open questions) and are worked on-screen in clmutils/w_caremark_non_claim_record with clmutils/d_caremark_non_claim_record_report.
• "RESTAT" is a PBM vendor, not a claims-restatement process. drug_covr_restat_trans is the eligibility staging table for the Restat PBM; its export event says "is now ready to be transfered to RESTAT" (drugplan/w_drugplan_report_transfer, ue_create_restat_transfer). There is no claim-restatement logic in this domain.

Plan types

A single set of screens is parameterized by a plan-type code. The five types wired into the code (drugplan/w_drugplan_member_maint, ue_retrieve / pfc_save; drugplan/w_drugplan_plan_maint radio buttons) are:

Code	Screen label	Vendor (as evidenced in code)	Staging table
PCS	"PCS"	PCS (later Caremark; 160-byte enrollment format)	drug_covr_pcs_trans
EXP	"EXP"	Express Scripts ("New file layout from Express Scripts", ue_create_exp_transfer)	drug_covr_exp_trans
RESTAT	"RESTAT"	Restat	drug_covr_restat_trans
RX_CLAIM	"RX Claim Drug"	CVS Caremark RxClaim (file paths reference \\SFPIServer1\share\public\cvs\, "CVS_out.txt")	drug_covr_pcs_std_trans
RXB	"RX Benefits"	RX Benefits (Caremark-style 1400-byte layout, different sender id)	drug_covr_pcs_rxb_trans

A sixth, menu-driven path exports "other" (non-PCS/non-Express-Scripts) plans — the only implemented vendor is VALUERX (drugplan/w_drugplan_other_save, of_savevaluerx). Drug-plan codes for these come from codes where code_key = 'DRUG_PLAN' (drugplan/dddw_drugplan_other_select); the transfer-type drop-down on the report/transfer screen comes from codes where code_key = 'DRUGXFER' (drugplan/dddw_drugplan_list).

Key workflows

1. Trigger from enrollment

When enrollment maintenance saves a change affecting a Drugs election, it asks "An event has occured that may indicate a transfer of the current Subscriber's Drug Coverage information to the Drug company is required. Would you like to go to the Drug Plan screen?" and, on Yes, opens w_drugplan_member_maint passing group id, subscriber SSN, plan type (from drug_plan_exts.drug_plan_ext_type_cd for the benefit coverage code) and the drug election effective date (enroll/w_enrollment_maintenance, wf_request_prescription_tsfr). The screen can also be opened standalone (defaults to PCS — drugplan/w_drugplan_member_maint, open).

2. Stage transfer records (member maintenance)

w_drugplan_member_maint ("HOMER - Prescription Plan Member Maintenance"):

1. User picks a plan type (radio buttons PCS / RX Claim Drug / RX Benefits / EXP / RESTAT), enters subscriber SSN (or member id) and a coverage-active date, then Retrieve.
2. The window loads plan parameters via uf_get_drug_plan_info into str_drugplan (system/str_drugplan, system/u_app_sql) and displays plan id / carrier / named-dependent flag / coverage code and level.
3. If the subscriber already has rows in the staging table they are listed (dw_transfer_file, e.g. drugplan/d_drugplan_pcs_trans_members — retrieve by sub_member_sys_key); otherwise an "add members" event builds proposed rows for the subscriber and covered dependents from election_coverages / members / addresses (drugplan/d_drugplan_pcs_retrieve, d_drugplan_pcs_std_retrieve, d_drugplan_pcs_rxb_retrieve, d_drugplan_exp_retrieve, d_drugplan_restat_retrieve).
4. The user reviews per-member rows (action code, dep code, include-in-file flag add), then Save inserts them into the vendor staging table via the edit DataWindow (d_drugplan_pcs_member_edit, d_drugplan_pcs_std_member_edit, d_drugplan_pcs_rxb_member_edit, d_drugplan_exp_member_edit, d_drugplan_restat_member_edit).

3. Report and export (report/transfer window)

w_drugplan_report_transfer ("HOMER- Prescription Plan Reports and Tranfers"):

1. User selects the transfer type from the DRUGXFER drop-down; the census report DataWindow is switched accordingly (d_drugplan_pcs_report, d_drugplan_exp_report, d_drugplan_restat_report, d_drugplan_pcs_std_report, d_drugplan_pcs_rxb_report) (dw_drugplan.itemchanged).
2. Retrieve shows all staged rows ("There are no rows in the ... transfer table to report on." when empty — ue_retrieve).
3. Create Transfer warns: "Once the transfer file is completed, all rows will be deleted from the database and will be unrecoverable" (ue_create_transfer), then dispatches to the vendor-specific event, which:
   • snapshots the staging table into a datastore ("d_..._trans_to_text" objects),
   • prompts for the output file (defaulted from HOMER.INI — see File exchange mechanics), warns before overwriting an existing file,
   • writes header / detail / trailer records in the vendor's fixed-width layout,
   • deletes the exported rows and commits (see DRG-9 for the per-vendor variations).

4. Inbound Caremark claim adjustments (clmutils)

clmutils/w_caremark_non_claim_record ("Caremark Non Claim Records") retrieves caremark_edi_table rows from the last n days (default 7) and classifies each row as Processed, 2 claims found, or 1 claim found by joining against HOMER claims / claim_lines on the subscriber's alternate id or SSN (clmutils/d_caremark_non_claim_record_report, retrieval SQL). Users filter by active (processed_ind = 'N') vs complete ('Y'), match records to claim ids (matching_claim_id, affected_claim_id — double-click opens the claim editor with a claim lock), and mark rows processed. Rows carry pharmacy/prescriber/drug/fill detail and a coded adjustment_reason_cd (values embedded in the DataWindow include "Paid to Wrong Provider", "Claim Overpayment", "Pharmacy Audit Recovery", "Cardholder Terminee Recovery", etc.). Comment in the SQL: when a new claim arrives for a script whose original claim is already PAID, "Instead of VOIDing a PAID claim we will VOID the new claim and adjust the original claim to meet the new claim's parameters".

The multiple PBM generations

The export window contains six generations of eligibility file writer, several of them dead code kept in place:

Generation	Event	Format	Status in code
PCS legacy (mainframe)	ue_create_pcs_transfer_old	Two 80-byte card lines per member wrapped in JCL (//TP32J030 JOB ..., /*EOF)	Dead — not called from ue_create_transfer
PCS 160-byte, pre-2010	ue_create_pcs_transfer_old_20100128	160-byte R01+R21 with sender id ANT2760016000160	Dead — superseded 01/28/2010
PCS 160 Enrollment Format v2.8	ue_create_pcs_transfer	160-byte header/R01/R21/trailer, sender id DNT9930016000160; comment: "SFPI will be using the 160 Enrollment Format Version 2.8 as of 01/28/2010"	Live for type PCS
PCS "standard" RX Claim, 160-byte	ue_create_pcs_std_transfer	160-byte variant; termination default 20391231; trailer count excludes header/trailer ("removed the header and trailer from the count by direction of PCS", 7/29/2004)	Dead — call commented out in ue_create_transfer, replaced by the CVS event
CVS Caremark 1400-byte	ue_create_pcs_std_transfer_cvs	117-field, 1400-byte record ending in *; header id 10233 + "SFPI" address block	Live for type RX_CLAIM
RX Benefits 1400-byte	ue_create_pcs_std_transfer_rxb (plus a one-off full-file variant on button cb_1/RXB)	Same 117-field layout; header id 12155	Live for type RXB

Which looks current: the CVS (RX_CLAIM) and RXB paths. Evidence: they are the dispatch targets in ue_create_transfer; the RX_CLAIM/RXB member add events carry the newest dated comment ("1/15/18 GUY - Per Lin request setting to Caremark Default of 12/31/2039" — ue_add_pcs_std_members, ue_add_pcs_rxb_members); and the RXB full-file DataWindow filters member_elect_exp_dt > 12/31/2019 (drugplan/d_rxb_mater_key_list_fullfile). EXP and RESTAT paths remain wired but show no changes after 2008 and 2004 respectively. The ValueRx export (w_drugplan_other_save) appears to be a legacy side path.

File formats (record layouts)

All exports are fixed-width ASCII text, one record per line, all data upper-cased at write time.

Express Scripts (EXP) — 400-byte member record

Layout documented in code comments and built with f_FixedLength concatenation (drugplan/w_drugplan_report_transfer, ue_create_exp_transfer; source datastore drugplan/d_exp_trans_to_text over drug_covr_exp_trans). No header/trailer records.

Pos	Len	Field	Source / rule
1	1	Record type	exp_record_type (staged as E)
2-5	4	Master carrier	exp_master_carrier (staged as A4)
6-9	4	Subcarrier	exp_subcarrier (staged as S4)
10-27	18	Member ID	exp_member_id (subscriber SSN)
28-29	2	Suffix number	exp_member_number (person code)
30	1	Overage dependent flag	exp_overage_dependent
31-50	20	Last name	exp_last_name
51-65	15	First name	exp_first_name
66	1	Middle initial	exp_mi
67-106	40	Address line 1	exp_address1; X when blank
107-146	40	Address line 2	blank filler
147-161	15	City	exp_city; X when blank
162-163	2	State	exp_state; XX when blank
164-172	9	Postal code	exp_zip9; 900000000 when blank
173-202	30	Filler	spaces
203-210	8	Date of birth	exp_dob YYYYMMDD; 19111111 when blank
211	1	Gender code	exp_gender_code
212-219	8	Effective date	exp_eff_date YYYYMMDD
220-227	8	Termination date	exp_term_date YYYYMMDD; 00000000 when blank
228-242	15	Group number	exp_group_number (plan id)
243-252	10	Misc value	exp_misc_value (HOMER group id)
253-256	4	Misc value 2	spaces
257-262	6	Filler	spaces
263-264	2	Dependent coverage	exp_dependent_coverage
265-266	2	Relationship code	exp_relationship_cd
267	1	Suspend indicator	space
268	1	Card request indicator	derived (see DRG-15)
269-338	70	PCP fields, other-coverage and coverage-type indicators, aux fields, convert-99-suffix	all written as spaces
339	1	Student indicator	exp_student_ind
340-399	—	Filler / client-specific / original suffix	spaces
400	1	Filler	literal X

PCS 160-byte Enrollment Format v2.8 (type PCS)

(drugplan/w_drugplan_report_transfer, ue_create_pcs_transfer; source drugplan/d_pcs_trans_to_text over drug_covr_pcs_trans.)

• Header (160): 00P + space + file timestamp YYYYMMDDHHMMSS + 20 spaces + 01
  • 16 spaces + F + 10 spaces + DNT9930016000160 + 67 spaces + PCSHDH00.
• R01 member record (160): carrier (4) + group/plan (4) + cardholder SSN (9) + 9 spaces + dep type (2) + space + first name (12) + middle initial (1) + last name (19) + 8 spaces + effective date YYYYMMDD (8) + expiration date YYYYMMDD (8)
  • dep code (3) + space + action code (1) + sub-group id pcs_gp (15) + 16 spaces + twin indicator (1) + birth date YYYYMMDD (8) + gender (1) + family status S/T/F (1) + 9 spaces + relationship code 01/02/03 (2) + alternate id (9) + 5 spaces + R01.
• R21 address record (160): carrier (4) + group (4) + SSN (9) + 9 spaces + dep type (2) + space + I + 6 spaces + address line 2 (30) + address line 1 (30) + city (30) + state (2) + zip (9) + 20 spaces + R21. (Address lines are intentionally swapped: addr2 is written before addr1.)
• Trailer (160): 99 + record count right-formatted to 10 + 140 spaces + PCSTRT99. Count = (members × 2) + 2, i.e. header and trailer are included for the v2.8 file (ue_create_pcs_transfer) but excluded (members × 2) in the RX Claim variant "by direction of PCS" (ue_create_pcs_std_transfer).

PCS legacy mainframe format (dead code, kept for reference)

Two 80-byte lines per member preceded by five hard-coded JCL lines (//TP32J030 JOB ('1UCELGBL PRDU ',PROD),'SELF-FUNDED PLANS '..., //TP32030A EXEC TP32P030,DEST=TCP015,INPRM='00080,00124') and terminated by /*EOF. Dates are 2-digit-year MMDDYY; when action code = 1 (termination) the effective date is written in the second line where fillers would otherwise be (ue_create_pcs_transfer_old).

RESTAT — 428-byte member record

(drugplan/w_drugplan_report_transfer, ue_create_restat_transfer; source drugplan/d_restat_trans_to_text over drug_covr_restat_trans.) No header/trailer. Fields, in order (right-padded with spaces via of_padright): customer id (10), client id (15), group id (15), member id (18), person code (3), status (1), gender (1), relationship code (1), effective date YYYYMMDD (8), eligibility type (1), term date YYYYMMDD (8), last name (15), first name (12), address 1 (30), address 2 (25), city (20), state (2), zip formatted NNNNN-NNNN (10), 10 spaces, birth date YYYYMMDD (8), phone (10), anniversary date YYYYMMDD (8), patient id number (18), primary care physician (10), 1 space, alternate member id (18), copay override flag (1), copay override (10), primary coverage insurance (15), filler (102), eligibility validation data (10), pharmacy NABP (12). Total 428 bytes.

CVS Caremark / RX Benefits — 1400-byte eligibility record

(drugplan/w_drugplan_report_transfer, ue_create_pcs_std_transfer_cvs and ue_create_pcs_std_transfer_rxb.) 117 fields totaling exactly 1400 bytes; the last field is a literal * record terminator. Detail data comes from drugplan/d_cvs_elig_partials_by_memsyskey_re (CVS) or drugplan/d_rxb_elig_partials_by_memsyskey_re (RXB), which join members, family, addresses and the staging table by dep_member_sys_key.

• Header (1400 + *): sender block 10233 SFPI 6000 LOMBARDO CENTER, SUITE 201B CLEVELAND OH44131 2165661455 (RXB uses 12155 instead of 10233) + 1 + file date YYMMDD, space-padded.
• Detail — populated fields (all others are fixed fillers/zero-date defaults):

#	Len	Field	Source
1	1	Record type	literal 3 (RECORD_TYPE_D)
2	9	Carrier	drug_covr_pcs_std_trans.pcs_carrier (RXB: ..._rxb_...)
3	15	Account	pcs_gp
4	15	Group	pcs_group
5	18	Member ID	member id root (9) + person code from trailing letter A→01 … L→12
6	3	Person code	same letter-derived code
7	1	Relationship	1 self / 2 spouse / 3 child / 4 other, from member_cat_val RELATIONS
8-12	25/15/1/1/8	Last, first, MI, sex, DOB	members (DOB defaulted 19000101 when null)
13-17	—	Multi-birth 0, member type, language 1, DUR flag/key	fixed
18	9	SSN	dependent SSN, or 000000000 when it equals the subscriber's (ALT_SSN)
19-25	40/40/20/2/11/4/10	Address 1-2, city, state, zip, country USA, phone	addresses / members (address lines swapped: file Address1 = addr2)
27	1	Family type	coverage level mapping (Family→1, Single→2, Emp+Spouse→3, Emp+Deps→4, DepsOnly→6, SpouseOnly→7, Single+1→8, Emp_Child→9)
28	18	Family ID	subscriber SSN
31	7	Effective date	pcs_eff_date, century flag + YYMMDD (0=pre-2000, 1=2000+)
32	7	Termination date	pcs_exp_date, same century encoding
82	18	Member ID (2nd occurrence)	member id + person code
117	1	Record terminator	literal *

Fields 33-81 and 83-116 (plan/copay/Medicare/caretaker/ST-* blocks, fillers) are written as fixed spaces or zero dates.

• Footer (1400 + *): 90233 (RXB 92155 ) + detail record count formatted 000000000 + a fixed block of 45 zeros, space-padded, terminated *.

ValueRx — 177-byte member record

Built entirely in SQL as SSN (9) + dep code (2) + a pre-formatted 166-character "rest of string" (name, coverage level, group id without dashes, gender, eff/term/birth dates as YYMMDD, address, phone) in drugplan/d_drugplan_other_members; of_savevaluerx rejects any line whose length is not 177 (drugplan/w_drugplan_other_save).

Business rules

ID	Rule	Source
DRG-1	A drug plan definition is keyed by benefit coverage type (Drugs), benefit coverage code, and plan type, stored in drug_plan_exts with named-dependent flag, dependent age limits (student and non-student), PCS plan id/carrier/account/effective date, EXP plan id, and Restat customer/client ids.	drugplan/d_drugplan_plan_maint, drugplan/w_drugplan_plan_maint
DRG-2	Plan maintenance warns before delete ("changes are saved immediately") and shows all groups using the coverage code. For RESTAT plans the named-dependent flag is forced to Y and protected; for EXP plans with named-dependent N the non-student dependent age defaults to 19.	drugplan/w_drugplan_plan_maint (pfc_deleterow, ue_add_plan, itemchanged)
DRG-3	Member transfer staging is retrieved by subscriber SSN (must be 9 digits, must belong to a subscriber, not a dependent, and pass per-user security clearance) or, since 2008, by member id (minimum 10 characters). Multiple members sharing an SSN open a chooser window.	drugplan/w_drugplan_member_maint (ue_retrieve, wf_validate_social, wf_mem_from_ssn)
DRG-4	The coverage-active date defaults to the subscriber's latest Drugs election effective date; if that election is expired the expiration date is used, and a future effective date is used as-is.	drugplan/w_drugplan_member_maint (wf_set_coverage_active_date)
DRG-5	Members proposed for transfer are the subscriber plus all dependents holding an active Drugs election for the plan's coverage code on the coverage-active date. If the plan's named-dependent flag is N, PCS-family transfers list the subscriber only, and EXP transfers list the subscriber plus only overage dependents (spouse-type dependents and under-age children are dropped).	drugplan/w_drugplan_member_maint (ue_add_pcs_members, ue_add_exp_members — get_dependents cursors)
DRG-6	A member's PBM person code is read from drug_mbr_exts (pcs_dep_type_cd, exp_mbr_cd, restat_person_code); when absent it is derived from the trailing letter of the member id (A=01, B=02, …) and written back to drug_mbr_exts. Subscribers are always 01 (PCS family), 00 (EXP), 001 (RESTAT). EXP dependents in group 501-383 get code 99.	drugplan/w_drugplan_member_maint (wf_set_member_type_number)
DRG-7	Dependents with person code above 02 who are not full-time students (per students.student_status_cd = 'F' on the active date) default to excluded from the file (include flag n); the user can override on screen. Rows still flagged n at save are discarded.	drugplan/w_drugplan_member_maint (wf_check_student_status, ue_add_pcs_members, save events)
DRG-8	Saving staged rows enforces one batch per subscriber per key: PCS/RX_CLAIM/RXB key on subscriber + action code, EXP on subscriber + effective date, RESTAT on subscriber only. A duplicate prompts "You are attempting to save a duplicate transfer file record" and, on Yes, deletes the existing rows before insert. A PCS-family action code is mandatory on add.	drugplan/w_drugplan_member_maint (ue_pcs_save, ue_exp_save, ue_restat_save, ue_pcs_std_save, ue_pcs_rxb_save)
DRG-9	Creating a transfer file first warns "Once the transfer file is completed, all rows will be deleted from the database and will be unrecoverable." PCS/EXP/RESTAT exports delete exactly the snapshot rows and commit (rollback + operator alert on failure). CVS and RXB exports instead ask "Clear records to send?" and then delete the entire staging table (DELETE from drug_covr_pcs_std_trans / drug_covr_pcs_rxb_trans). The dormant ue_create_pcs_std_transfer has its delete commented out.	drugplan/w_drugplan_report_transfer (ue_create_transfer and the per-vendor events)
DRG-10	Only rows present when the export starts are written: each export snapshots the staging table into a datastore, so "any records added to the transfer table after the snapshot will not be included".	drugplan/w_drugplan_report_transfer (comment in every ue_create_* event)
DRG-11	PCS expiration-date rule: the plan's PCS effective date (month/day) is rolled forward to the current year, or the next year if already past, and serves as the member's expiration date ("the group plan effective date serves as the members experation date … By conversation with Lin"). For RX_CLAIM and RXB this was overridden on 1/15/2018 to the fixed Caremark default 12/31/2039. In the 160-byte export, blank effective dates are written as 20391231, and a second date field carries the effective date only for action codes 1 or 7 (terminations), else 20391231.	drugplan/w_drugplan_member_maint (ue_add_pcs_members, ue_add_pcs_std_members, ue_add_pcs_rxb_members); w_drugplan_report_transfer (ue_create_pcs_std_transfer)
DRG-12	PCS family status indicator is derived from coverage level: Single/SpouseOnly → S; Emp+Spouse/Emp+Child/Single+1 → T; anything else → F. Relationship code is 01/02 for dep types 01/02, else 03.	drugplan/w_drugplan_report_transfer (ue_create_pcs_transfer)
DRG-13	PCS twin indicator: members with a member_cat_val category TWINS = Yes are flagged M (added 03/05/2010).	drugplan/w_drugplan_member_maint (ue_add_pcs_members)
DRG-14	PCS alternate id: when the subscriber's member id does not start with the SSN, the first 9 characters of the member id (zero-padded to 9) are staged as pcs_alternate_id; otherwise blank.	drugplan/w_drugplan_member_maint (ue_add_pcs_members)
DRG-15	EXP card request indicator: Y unless the remark is exactly TERMINATION (case-insensitive; a 2008 fix corrected a comparison that had issued cards on every record). The staged card-request column is ignored ("does not appear to be populated ever … Look at remark to decide if card is needed (Per JSE)").	drugplan/w_drugplan_report_transfer (ue_create_exp_transfer)
DRG-16	EXP missing-data defaults on export: address X, city X, state XX, zip 900000000, birth date 19111111, termination date 00000000.	drugplan/w_drugplan_report_transfer (ue_create_exp_transfer)
DRG-17	EXP effective/termination dates: a termed election (unless expiry reason Re-enroll) sends the termination date; the effective date sent is the earliest effective date of continuous coverage ("EXP wants the earliest effective date in a continuous coverage", per Express Scripts request 5/28/98).	drugplan/w_drugplan_member_maint (ue_add_exp_members)
DRG-18	EXP relationship codes: 01 subscriber, 02 spouse/ex-spouse, 03 child under the plan's student age, 05 full-time student (also sets student and overage-dependent indicators Y), 06 disabled dependent, 08 domestic partner/common-law, 04 other. Age is measured at the greater of today and the drug plan effective date, against the plan's non-student age limit (default 19).	drugplan/w_drugplan_member_maint (ue_add_exp_members)
DRG-19	EXP save: if any staged dependent is a student, the subscriber row's student indicator is forced to Y.	drugplan/w_drugplan_member_maint (ue_exp_save)
DRG-20	RESTAT status code: T if the drug election termed before today, P if the effective date is in the future, else A. Term date is 12/31/9999 for active or Re-enroll members. Effective date doubles as the anniversary date. RESTAT supports named dependents only; a plan without the flag warns and saves "Members Only".	drugplan/w_drugplan_member_maint (ue_add_restat_members)
DRG-21	RESTAT relationship codes: 1 subscriber, 2 spouse, 3 natural/step/adopted child, 5 full-time student, 6 handicapped, 7 legal guardian, 8 common law, 4 other. Gender must be M or F (else blanked with an operator error).	drugplan/w_drugplan_member_maint (ue_add_restat_members)
DRG-22	CVS/RXB export integrity check: the staged row count is compared with the eligibility rows retrieved for the same member keys; a mismatch warns "One or more members not found" and lets the user continue or abort. Output is sorted by effective date then member id.	drugplan/w_drugplan_report_transfer (ue_create_pcs_std_transfer_cvs, ue_create_pcs_std_transfer_rxb)
DRG-23	CVS/RXB person code and relationship are derived in SQL: person code from the member-id suffix letter (A→01 … L→12), relationship from the most recent member_cat_val RELATIONS value (Spouse→2, natural/step/adopted child→3, niece-nephew/legal-guardian/handicapped→4, else 1); dependent SSN equal to the subscriber's is sent as 000000000; dates carry a century flag (0 pre-2000, 1 otherwise) plus YYMMDD.	drugplan/d_cvs_elig_partials_by_memsyskey_re, d_rxb_elig_partials_by_memsyskey_re
DRG-24	ValueRx export: dependent code 03 is incremented per additional child of the same subscriber (03, 04, …); every output line must be exactly 177 bytes or the export aborts and logs. Group 501-498 effective dates are floored at 07/01/1994.	drugplan/w_drugplan_other_save (of_savevaluerx); drugplan/d_drugplan_other_members
DRG-25	"Other" drug plan maintenance restricts visible plans to groups the user is authorized for (security_user_group / security_user_class_user join) and validates group ids against groups before adding coverage codes to a drug plan.	drugplan/d_drugplan_other_maint; drugplan/w_drugplan_other_add
DRG-26	Caremark inbound records are worked from newest n days (default 7), filtered Active (processed_ind = 'N') or Complete ('Y'); marking a row processed re-touches its claim-id fields so the update is captured; unmatched-claim statuses are 2 claims found (new claim for a script whose original is already PAID — void the new claim and adjust the original) and 1 claim found.	clmutils/w_caremark_non_claim_record; clmutils/d_caremark_non_claim_record_report
DRG-27	PCS-lineage codes come from the codes table: action codes code_key = 'PCSAction', EXP dependent codes 'EXPdepcode', EXP remarks 'EXPRemarks', transfer types 'DRUGXFER', other drug plans 'DRUG_PLAN'. Coverage-code choices per plan type come from drug_plan_exts filtered by drug_plan_ext_type_cd.	drugplan/dddw_pcs_action_codes, dddw_exp_action_codes, dddw_drugplan_exp_remarks, dddw_drugplan_list, dddw_drugplan_other_select, dddw_drug_coverage_codes

Data & CRUD

Column-level detail for these tables is in Data model → Drug Plans.

Table	Role	C	R	U	D	Where
drug_plan_exts	Plan definitions per coverage code and plan type	✔	✔	✔	✔	w_drugplan_plan_maint / d_drugplan_plan_maint; w_drugplan_other_maint / d_drugplan_other_maint; read by exports and enrollment
drug_mbr_exts	Persisted per-member PBM person codes	✔	✔	—	—	w_drugplan_member_maint (wf_set_member_type_number)
drug_covr_pcs_trans	PCS staging	✔	✔	✔	✔	member maint save/edit; ue_create_pcs_transfer (delete after export)
drug_covr_pcs_std_trans	RX Claim (CVS) staging	✔	✔	✔	✔	member maint; ue_create_pcs_std_transfer_cvs (DELETE from drug_covr_pcs_std_trans)
drug_covr_pcs_rxb_trans	RX Benefits staging	✔	✔	✔	✔	member maint; ue_create_pcs_std_transfer_rxb (DELETE from drug_covr_pcs_rxb_trans)
drug_covr_exp_trans	Express Scripts staging	✔	✔	✔	✔	member maint; ue_create_exp_transfer (delete after export)
drug_covr_restat_trans	Restat staging	✔	✔	✔	✔	member maint; ue_create_restat_transfer (delete after export)
caremark_edi_table	Inbound Caremark claim/adjustment records	—	✔	✔	—	clmutils/w_caremark_non_claim_record (updates processed_ind, matching_claim_id, affected_claim_id only)
Read-only inputs	election_coverages, member_eligibilities, members, addresses, family, member_cat_val, students, codes, groups, claims/claim_lines (Caremark matching)	—	✔	—	—	retrieve DataWindows and embedded SQL cited above

The three PCS-lineage staging tables are structurally near-identical (name, SSN, carrier/group/gp, dep type/code, action code, eff/exp/birth dates, sex, address, coverage level); drug_covr_pcs_trans alone adds pcs_alternate_id and twins_ind (analysis/schema.json; drugplan/d_pcs_trans_to_text vs d_pcs_std_trans_to_text_for_partials).

File exchange mechanics

Output locations are user preferences persisted in HOMER.INI under section app preferences and read/written with ProfileString / SetProfileString; whenever the user saves to a different folder, the new folder is written back to the INI:

INI key	Used by	Default file name	File-type filter
pcs_path	PCS, RX Claim, CVS, RXB exports	date MMDDYY (comments: "Default the file name to Enrollment\pcsdata\mmddyy"; "Lin stores in \SFPISERVER1\enrollment\pcsdata")	*.pcs (PCS), *.rx (std), *.txt (CVS/RXB)
exp_path	Express Scripts export	S4. + 3-digit day-of-year (e.g. S4.037)	none
restat_path	Restat export	date MMDDYY	*.restat
valuerx_path	ValueRx export	date MMDDYY	*.vrx

(drugplan/w_drugplan_report_transfer, each ue_create_* event; drugplan/w_drugplan_other_save, of_savevaluerx.)

Common mechanics in every export: GetFileSaveName dialog seeded with the INI path; cancel aborts with a "Transfer Canceled" message; an existing file prompts "already exists … Would you like to replace the existing file?"; files are written with FileOpen(..., Linemode!, Write!, LockReadWrite!, Replace!); completion shows "The … transfer file is complete … is now ready to be transfered to …". Transmission to the PBM happens outside HOMER (the operator picks up the file from the share). A commented-out 2003-era option offered to append records from a legacy "Wang" system file (*.wng) to the PCS/EXP output (ue_create_exp_transfer, ue_create_pcs_transfer_old comment blocks).

Migration notes

• Only two export formats need forward-engineering. The live dispatch targets are the PCS 160-byte v2.8 writer and the two Caremark-style 1400-byte writers (CVS id 10233, RXB id 12155); EXP, RESTAT and ValueRx paths should be confirmed as retired with the business before porting (w_drugplan_report_transfer, ue_create_transfer).
• Destructive export semantics. The staging tables double as a send queue: export deletes the rows (CVS/RXB delete the whole table, not just the snapshot — a concurrency hazard worth fixing rather than reproducing). A migration should replace delete-on-export with an exported/batch-id status, which also removes the "unrecoverable" warning burden from users.
• The 1400-byte writer builds its layout in code, per row, in three near-identical copies (CVS event, RXB event, RXB full-file button). These should collapse into one declarative layout definition; the field/width tables above capture the union.
• Person-code derivation from the member-id suffix letter exists in three PowerScript variants and again in SQL (wf_set_member_type_number; d_cvs_elig_partials_by_memsyskey_re). The SQL variant only maps letters A-L, and the RXB full-file variant contains a duplicate WHEN 'H' branch mapping H→02 (d_rxb_elig_partials_full) — likely a latent defect to resolve, not replicate.
• Hard-coded values that must become configuration: PBM sender ids (DNT9930016000160, ANT2760016000160, 10233/12155 + the SFPI address block), EXP master/sub carrier A4/S4, Caremark default termination 12/31/2039, EXP null-value sentinels (900000000, 19111111), group-specific rules (501-383 EXP dep code 99, 501-498 ValueRx date floor, RXB full-file exclusion of master group 501-003).
• Character-set/precision hazards: all layouts are byte-position sensitive; the legacy PCS format uses 2-digit years, and the 1400-byte format uses a bespoke century-flag + YYMMDD date encoding.
• Inbound side is partially outside this codebase. caremark_edi_table is only read/annotated by the extracted code; the loader that fills it (Caremark claim EDI) must be located in the surrounding infrastructure before migration.
• The w_drugplan_report_transfer window carries visible debug scaffolding (hidden test DataWindows dw_2..dw_5, an RXB full-file button, MessageBox("test", ...) calls) that should not be carried forward.

Open questions

1. Which vendor paths are still in production? EXP, RESTAT and ValueRx exports are wired and reachable, but their newest code comments date to 2003-2008. Business confirmation is needed (evidence: w_drugplan_report_transfer, w_drugplan_other_save).
2. What loads caremark_edi_table? No insert into the table exists in the extracted source (analysis/schema.json shows it used only by clmutils); the inbound claim file/EDI job is external and undocumented here.
3. The dormant ue_create_pcs_std_transfer (160-byte ".rx" writer) has its post-export delete commented out ("Cancel Delete of records for now") — was it retired deliberately when the CVS 1400-byte format went live, or is it still invoked in some environment?
4. d_pcs_std_trans_to_text_for_partials DataObject name mismatch: the CVS event assigns 'd_pcs_std_trans_to_text_for partials' (with a space) to its snapshot datastore; the library object is d_pcs_std_trans_to_text_for_partials. Whether this snapshot (which is retrieved but never written to the file) fails silently at runtime needs verification (w_drugplan_report_transfer, ue_create_pcs_std_transfer_cvs).
5. Person-code letter mapping beyond 'L'. The SQL derivation maps suffix letters A-L only; members with later suffix letters would get a null person code in CVS/RXB files (d_cvs_elig_partials_by_memsyskey_re). Intended limit or defect?
6. drug_mbr_exts insert guard: in the PCS branch of wf_set_member_type_number the insert into drug_mbr_exts only runs when ls_dep_type_cd is not null — i.e. a freshly derived code appears never to be persisted for PCS-lineage members (contrast the EXP/RESTAT branches). Confirm actual behavior before porting (w_drugplan_member_maint, wf_set_member_type_number).
7. Field 26 of the 1400-byte layout ("family flag") is sent as a space while field 27 carries the coverage-level-derived family type; Caremark spec alignment of these two fields should be confirmed against vendor documentation.
8. RESTAT phone number: staged phone_no is numeric (decimal(0)) but set from the literal string ' ' at add time — the exported value is likely always blank/null (ue_add_restat_members; d_restat_trans_to_text).