Stop-Loss Module

Library: stoploss (12 windows, 47 DataWindows, 2 non-visual objects, 3 menus, 1 structure) Requirement prefix: STL

Purpose

The Stop-Loss module tracks specific (per-person / per-family) and aggregate stop-loss protection for self-funded health plans administered by the TPA. It provides:

• Maintenance of carrier stop-loss contracts (sl_contracts and seven child tables) via a tabbed contract editor (stoploss/w_sl_contract_maint).
• Accumulation reporting of claim payments against group stop-loss plan parameters (group_stoploss_parms, group_stoploss_coverages): a daily "first appearance" specific stop-loss report, on-demand specific/family summaries with percent-met thresholds, per-member claim listings, and an aggregate percent-met summary.
• A legacy Wang ID-card batch export that also lives in this library (stoploss/w_wang_idcard_response), historically packaged here but functionally unrelated to stop-loss accumulation.

Two parallel data models exist side by side (see Contract parameter model and Open questions):

1. Group stop-loss parameters (group_stoploss_parms + group_stoploss_coverages) — the model every accumulation report in this library actually reads.
2. Stop-loss contracts (sl_contracts + sl_contract_groups, sl_contract_coverages, sl_contract_factors, sl_contract_excl_plans, sl_contract_lasers, sl_contract_domestic_tins, sl_contract_option_vals) — a richer contract model maintained in stoploss/w_sl_contract_maint, with no accumulation report in this library reading it.

Key workflows

1. Stop-loss contract setup

stoploss/w_sl_contract_selection (search screen) opens stoploss/w_sl_contract_maint, a four-tab editor:

• General — contract header (stoploss/d_sl_contract_maint, shared with hidden updater d_sl_contract_header): code, description, carrier, level, contract period, start/end dates, incurred/paid date ranges, deductible, minimum attachment, TLO (terminal liability option) fields, run-in/run-out periods, reimbursement percentages and limits, policy number, notes.
• Groups — attach client groups to the contract from a hierarchical group pick list (sl_contract_groups).
• Coverages — coverage-type checklist (stoploss/d_sl_contract_coverages_checkbox), excluded plan checklist (stoploss/d_sl_contract_excl_plan_checklist), and — for aggregate contracts only — a factor grid (stoploss/d_sl_contract_factor_grid) with Standard and TLO factor tabs.
• Options — metadata-driven contract options (stoploss/d_sl_contract_option_val_list), laser members (stoploss/d_sl_contract_laser_maint), and domestic provider TINs (stoploss/d_sl_contract_domestic_tin_maint).

All checklist/grid tabs are read-only presentation surfaces; hidden window-level DataWindows perform the actual inserts/updates/deletes by diffing against the visible checklists at save time (ue_preupdate events in stoploss/w_sl_contract_maint).

2. Daily specific stop-loss report (first-appearance tracking)

stoploss/w_sl_specific_rpt collects group, paid date and time window and calls n_stoploss.of_SpecificStoplossRpt (stoploss/n_stoploss), which:

1. Finds every master group with claim payments in the run window (stoploss/d_sl_master_groups).
2. Loads that group's stop-loss plans and coverages into a bounded structure (of_GroupSpecPlans, stoploss/str_spec_plan).
3. Retrieves families exceeding the family specific limit (stoploss/d_sl_family_4_4_2000) and patients exceeding the individual limit (stoploss/d_sl_patient_4_4_2000).
4. Prints a claim-level detail report per exceeder (stoploss/d_sl_spec_rpt) and a summary listing (stoploss/d_sl_spec_listing) flagging first appearances.

(Source: stoploss/n_stoploss of_GroupSpecPlans, of_SpecRegTotal, of_SpecificStoplossRpt; stoploss/d_sl_family_4_4_2000; stoploss/d_sl_patient_4_4_2000; stoploss/d_sl_spec_listing.)

3. Specific stop-loss summary (threshold alerts)

stoploss/w_sl_spec_summary retrieves an individual (stoploss/d_sl_specific_stoploss_summary) or family (stoploss/d_sl_specific_stoploss_family_summary) summary for a group and plan start date, then lets the user filter interactively to members over 50%, over 100%, over a custom percent, or over a dollar amount — optionally counting pending payable amounts — and export to a spreadsheet layout (stoploss/d_sl_specific_stoploss_summary_excel). An older window stoploss/w_sl_summary (and orphan stoploss/w_sl_specific_summary) runs equivalent specific and aggregate summaries per stop-loss group using stoploss/d_sl_stoploss_groups, stoploss/d_sl_specific_summary, d_sl_spec_summary, and d_sl_spec_2.

4. Member claim listings

• stoploss/w_sl_spec_clm_listing — all payments feeding a member's stop-loss accumulation for one plan year (stoploss/d_sl_spec_clm_listing), plan resolved from the member's eligibility group (stoploss/d_sl_member_stoploss_parms).
• stoploss/w_sl_variable_clm_listing — same listing over a user-chosen paid date range instead of a plan year (stoploss/d_sl_variable_clm_listing), with spreadsheet export (stoploss/d_sl_export_maz).

5. Aggregate accumulation

stoploss/d_sl_agg_summary computes total paid against the aggregate minimum attachment point (LEAST(SUM(paid)/aggr_min_attchment_pt, 100)). The aggregate report path in stoploss/w_sl_summary (ue_agg) retrieves DataWindow d_sl_spec_2, which is not present in the extracted library (see Open questions).

6. Carrier filing / reimbursement

Not coded in this library. sl_contracts stores reimbursement percentages and limits (reim_domestic_pct, reim_standard_pct, member_reimb_limit, contract_reimb_limit) and sl_contract_domestic_tins marks domestic providers, but no window or DataWindow in stoploss generates a carrier filing, reimbursement request, or reimbursement transaction. See Open questions.

7. Wang ID-card batch export (legacy)

stoploss/w_wang_idcard_sheet immediately opens stoploss/w_wang_idcard_response, which reads outstanding ID-card requests (stoploss/d_wang_idcard_req) or a prior day's completed requests, formats fixed-length 314-byte records (stoploss/d_wang_idcard_data), saves them to a .CRD file (default path G:\APPS\IDCARDS\), and stamps requests complete.

Business rules

Contract maintenance

• STL-1 — A stop-loss contract must have a non-blank contract code before it can be saved. (stoploss/w_sl_contract_maint pfc_preupdate, dw_sl_contract.pfc_validation)
• STL-2 — A contract level is required. Levels are drop-down codes under code key SLLEVELS; code values observed in logic: INDIVIDUAL, FAMILY, AGGREGATE. (stoploss/w_sl_contract_maint dw_sl_contract.constructor, wf_contractlevel)
• STL-3 — Contract level drives which tabs/controls are active: factor tabs and the factor grid are enabled only for AGGREGATE; the laser-member list is enabled only for INDIVIDUAL and FAMILY; an unrecognized level disables all tabs. (stoploss/w_sl_contract_maint wf_contractlevel; dw_tab_factors.ue_filter additionally blanks the grid for non-aggregate contracts)
• STL-4 — Contract start and end dates are required, must not be the sentinel 01/01/1900, and end date must not precede start date. (stoploss/w_sl_contract_maint dw_sl_contract.pfc_validation)
• STL-5 — A stop-loss carrier must be selected. (stoploss/w_sl_contract_maint dw_tab_sl_contract.pfc_validation)
• STL-6 — For AGGREGATE contracts a minimum attachment amount greater than zero is required; for all other levels a specific deductible amount greater than zero is required. (stoploss/w_sl_contract_maint dw_tab_sl_contract.pfc_validation)
• STL-7 — Run-in, run-out, and TLO periods are each a length + period-type pair: if the length is empty or zero both fields are cleared; if a length is entered a period type is mandatory; negative lengths are rejected. Period types come from code key PERIOD; the contract period from code key SLPERIODS. (stoploss/w_sl_contract_maint dw_tab_sl_contract.pfc_validation, dw_tab_sl_contract.constructor)
• STL-8 — If the TLO-exercised flag is set, the TLO period (length and type) and TLO attachment point must be completed. (stoploss/w_sl_contract_maint dw_tab_sl_contract.pfc_validation)
• STL-9 — A contract must carry an incurred date range and/or a paid date range; each entered range must be internally valid (thru not before from, no 01/01/1900 sentinel) and must overlap the contract period. (stoploss/w_sl_contract_maint dw_tab_sl_contract.pfc_validation)
• STL-10 — New contract keys are drawn from database sequence SL_CONTRACT_SEQ, and the key is propagated into every pending child row (groups, coverages, factors, excluded plans, lasers, domestic TINs, option values) before update. (stoploss/w_sl_contract_maint wf_getkey, pfc_preupdate)
• STL-11 — Entering an existing contract code defaults blank fields (description, carrier, level) from the most recent contract with that code, and defaults the new period to begin the day after the prior contract's end date and end one year later. (stoploss/w_sl_contract_maint dw_sl_contract.ue_afteritemchanged)
• STL-12 — Group attachment: selecting a group from the hierarchical pick list adds only rows flagged as base groups (base = 'Y'), walks child rows of the selected hierarchy level, and skips groups already attached. On retrieve, the pick list is seeded with the master group of the first attached group (hierarchical CONNECT BY to the MASTER_GROUP_FLG = 'Y' ancestor). (stoploss/w_sl_contract_maint dw_tab_sl_contract_groups.pfc_addrow, dw_sl_contract_groups.pfc_retrieve)
• STL-13 — The coverage checklist offers only coverage types listed under code key SLCOVERAGE; checked entries are persisted to sl_contract_coverages by diffing the checklist against stored rows at save time (unchecked rows deleted, newly checked rows inserted). (stoploss/d_sl_contract_coverages_checkbox; stoploss/w_sl_contract_maint dw_sl_contract_coverages.ue_preupdate)
• STL-14 — The excluded-plans checklist lists group coverages (benefit plans) of the contract's attached groups whose effective window overlaps the contract's incurred dates, filtered on screen to the currently selected coverage types; checked entries persist to sl_contract_excl_plans by the same diff pattern. (stoploss/d_sl_contract_excl_plan_checklist; stoploss/w_sl_contract_maint dw_tab_excl_plans.ue_filter, dw_sl_contract_excl_plans.ue_preupdate)
• STL-15 — Aggregate factors are kept per coverage type, per coverage level, and per factor type (STANDARD or TLO, from code key SLFACTORS), including a <Default> coverage row per election level; the grid saves to sl_contract_factors by diffing grid rows with non-null factor amounts. A "2-tier only" option restricts visible levels to Single/Family and is auto-checked when stored factors use only those levels. The on-screen help text states factors default backwards from selected coverage/level to Family level and then to the <Default> coverage. (stoploss/d_sl_contract_factor_grid; stoploss/w_sl_contract_maint dw_tab_factors.ue_filter, dw_sl_contract_factors.ue_preupdate, dw_sl_contract_factors.pfc_retrieve, static text st_2)
• STL-16 — Contract options are metadata-driven: the option list comes from sl_contract_options (one row shown per available option), each value is typed by val_saved_as_cd (NUMBER, DATE, CODE, else character) and stored in the matching column of sl_contract_option_vals; clearing a value deletes the stored row; new option rows default effective date 1950-01-01; only the latest effective row per option is shown. CODE options draw their value list from the option's own code key. (stoploss/d_sl_contract_option_val_list; stoploss/w_sl_contract_maint dw_tab_sl_contract_option_vals.pfc_update, rowfocuschanged; stoploss/dddw_sl_option_codes)
• STL-17 — Laser members are added through a member search restricted (when a contract key exists) to members eligible in the contract's attached groups; each laser row must carry a deductible amount greater than zero. The stored laser identifies subscriber and dependent member keys, and display derives the relationship as Subscriber, a member-category value, or Dependent. (stoploss/w_sl_member_selection; stoploss/w_sl_contract_maint dw_tab_lasers.pfc_addrow, dw_tab_lasers.pfc_validation; stoploss/d_sl_contract_laser_maint)
• STL-18 — Domestic provider TIN entries accept digits only; rows with a blank TIN are silently deleted at validation. (stoploss/w_sl_contract_maint dw_tab_domestic_tins.key, editchanged, pfc_validation)
• STL-19 — Contract search supports filters on code (prefix), description, group (including all descendants of the entered group via hierarchical subquery against sl_contract_groups), level, carrier, and "within years" (contract end date within the last N years). (stoploss/w_sl_contract_selection control Tag filter expressions)

Plan windows and accumulation

• STL-20 — For a group specific stop-loss plan, the incurred window runs from the plan end date minus spec_contract_inc months (plus one day) through the plan end date, and the paid window runs from the plan start date through spec_contract_paid months after the start date minus one day. This is the incurred/paid contract basis displayed on reports as spec_contract_inc / spec_contract_paid (e.g. a 12/12 or 15/12 style contract). (stoploss/n_stoploss of_GroupSpecPlans; SQL equivalents ADD_MONTHS(END_DT, -1 * SPEC_CONTRACT_INC) + 1 and ADD_MONTHS(START_DT, SPEC_CONTRACT_PAID) - 1/86400 in stoploss/d_sl_spec_clm_listing, stoploss/d_sl_specific_stoploss_summary, stoploss/d_sl_agg_summary; header compute "Contract Type (Inc/Paid)" in stoploss/d_sl_specific_stoploss_summary)
• STL-21 — If the plan's specific TLO indicator is Y with a positive TLO month count, the paid window is extended by that many months. (stoploss/n_stoploss of_GroupSpecPlans)
• STL-22 — At most 10 stop-loss plans per group and 10 coverage types per plan are processed, loaded newest first; overflow of coverages raises a warning. (stoploss/str_spec_plan array bounds; stoploss/n_stoploss of_GroupSpecPlans)
• STL-23 — A fund-register payment accumulates toward a specific plan only when: the paid date falls in the paid window, both the claim's earliest-from and latest-thru service dates fall inside the incurred window, and the claim's type-of-claim matches one of the plan's covered coverage types. A claim only partially inside the incurred window is not accumulated and raises "Claim crossed stoploss period". (stoploss/n_stoploss of_SpecRegTotal)
• STL-24 — Coverage matching between claims and plan coverages is by upper-cased, trimmed benefit coverage type code, and plan coverages are restricted to rows with stoploss_type = 'SPECIFIC'. (stoploss/n_stoploss of_GroupSpecPlans filter; stoploss/d_sl_spec_clm_listing; stoploss/d_sl_specific_stoploss_summary)
• STL-25 — The daily report's candidate population is every master group (hierarchical roll-up with MASTER_GROUP_FLG = 'Y') that had fund-register payments in the run window, excluding claim IDs beginning with W. (stoploss/d_sl_master_groups)
• STL-26 — Family accumulation sums payments by subscriber across all family patients; individual accumulation sums by patient. A member/family is reported only when it has a new payment on the report date and its running total exceeds the applicable limit (HAVING TOTAL_NEW > 0 AND TOTAL_PAID > :limit). (stoploss/d_sl_family_4_4_2000; stoploss/d_sl_patient_4_4_2000)
• STL-27 — Patients belonging to a family that already exceeded the family limit are removed from the individual exceeder list so they are not reported twice. (stoploss/n_stoploss of_SpecificStoplossRpt, family filter and RowsDiscard)
• STL-28 — "First appearance" is flagged when total paid minus the report date's new payments is at or below the limit (i.e. the limit was crossed this run); when the attachment was already crossed before this run, the detail report is limited to the report date's payments only. (stoploss/n_stoploss of_SpecificStoplossRpt; stoploss/d_sl_spec_rpt compute compute_1; stoploss/d_sl_spec_listing first_flg)
• STL-29 — The report's paid/incurred windows are truncated at the report date when the plan window is open-ended, null, or extends past the report date. (stoploss/n_stoploss of_SpecificStoplossRpt)
• STL-30 — The summary report prints a "no members found exceeding stoploss for this run" line when no exceeders exist. (stoploss/n_stoploss of_SpecificStoplossRpt)

Summary reports and thresholds

• STL-31 — Percent met is total paid divided by the specific limit times 100, capped at 100; a parallel pending percentage adds pending payable amounts to paid before dividing. (stoploss/d_sl_specific_stoploss_summary computes pct_met, pct_pending, compute_6; stoploss/d_sl_agg_summary LEAST(... , 100))
• STL-32 — Pending amounts are split into pending payable (claims at or past status RFAL in the claim status order: total payable amount) and pending claimed (claims between RFCP and PAID but before RFAL: total claimed), computed from claims with no applied fund-register payment. (stoploss/d_sl_claims_pending; stoploss/d_sl_specific_stoploss_summary PENDING_PAYABLE / PENDING_CLAIMED expressions)
• STL-33 — Threshold filtering options are: all members; over 50%; over 100%; over a user percent; or over a user dollar amount — filtering on pct_met / total_paid_amt, or on pct_pending / total_pending_amt when "Include pending" is checked. In the older w_sl_summary flow, a blank level defaults to 100 percent. (stoploss/w_sl_spec_summary dw_report.ue_filter; stoploss/w_sl_summary ue_spec)
• STL-34 — Summary retrieval validates: group id entered, group passes the user's group security (f_check_group_security, added 9/24/2003), group exists, plan start date valid, and a group_stoploss_parms row exists for that group/start date with spec_individual_limit > 0 (individual mode) or spec_family_limit > 0 (family mode). If no date is entered, the current (or else latest) plan start date for the group is looked up as the default. (stoploss/w_sl_spec_summary dw_report.pfc_retrieve)
• STL-35 — Family mode swaps the report DataWindow to the family summary layout; individual mode de-duplicates repeated patient rows after retrieval. (stoploss/w_sl_spec_summary cbx_family.clicked, dw_report.pfc_retrieve)
• STL-36 — Members with life status Fake or PCS are excluded from summary populations. (stoploss/d_sl_specific_stoploss_summary; stoploss/d_sl_specific_stoploss_family_summary; stoploss/d_sl_specific_summary)
• STL-37 — Waiver payments (fund-register WAIVER_IND = 'Y' with no paid date) are treated as paid using the register date and payable amount in the "over spec" and claim-listing variants; payments of type EXPECTED are always excluded; backed-out unapplied waivers are excluded. (stoploss/d_sl_patient_over_spec; stoploss/d_sl_family_over_spec; stoploss/d_sl_spec_clm_listing; stoploss/d_sl_variable_clm_listing)
• STL-38 — Group IDs are space-padded to 15 characters before being passed to retrievals (RPAD(:as_group_id, 15) on the SQL side). (stoploss/w_sl_summary, stoploss/w_sl_specific_rpt, stoploss/n_stoploss of_SpecificStoplossRpt(date) overload; stoploss/d_sl_specific_stoploss_summary)
• STL-39 — After retrieval, the individual summary rewrites each row's displayed member group to the member's most recent eligibility group (change dated 03/09/2021), then re-sorts by group, subscriber name, patient id. (stoploss/w_sl_spec_summary dw_report.pfc_retrieve)
• STL-40 — The spreadsheet export recomputes percent met (capped at 100) and pending percent per row and appends grand totals for paid, pending claimed and pending payable. (stoploss/w_sl_spec_summary cb_1.clicked; stoploss/d_sl_specific_stoploss_summary_excel)
• STL-41 — Aggregate percent met is total covered paid divided by aggr_min_attchment_pt, capped at 100, over the same incurred/paid windowing as specific. (stoploss/d_sl_agg_summary)

Member claim listings

• STL-42 — The member claim listing resolves the member's stop-loss plan from subscriber eligibility: the current eligibility group (or, failing that, the last eligibility) is walked up the group hierarchy to find group_stoploss_parms rows overlapping the entered date/year; exactly one plan must match — zero or multiple matches are rejected with instructions to enter a specific plan start date. (stoploss/w_sl_spec_clm_listing dw_report.pfc_retrieve; stoploss/d_sl_member_stoploss_parms; stoploss/n_stoploss_sql)
• STL-43 — The plan date input accepts either a 4-digit year (expanded to Jan 1 – Dec 31) or a specific date; it defaults to the current year. (stoploss/w_sl_spec_clm_listing dw_report.pfc_retrieve, sle_sl_date.constructor)
• STL-44 — The variable claim listing requires a from/thru paid date pair (thru not before from) and defaults to the trailing twelve months; the claim population is windowed by the paid range rather than a plan year, and the plan coverage set is any specific coverage row of a plan overlapping the range. (stoploss/w_sl_variable_clm_listing dw_report.pfc_retrieve, em_sl_from_dt/em_sl_thru_dt constructors; stoploss/d_sl_variable_clm_listing)
• STL-45 — Listings show the payment reference as check number, else worksheet number, Waiver for unapplied waivers, and Adjustment for PREPAID payment types; the payee number shows the vendor TIN for vendor payees. Only the latest funding transaction per register entry is joined. (stoploss/d_sl_spec_clm_listing computes and NOT EXISTS on FUNDTRANS; stoploss/d_sl_variable_clm_listing)

Wang ID-card export (legacy, in this library)

• STL-46 — ID-card runs are either Miscellaneous (E) or Group (G) batches; a blank date processes outstanding requests (REQ_COMPLETE_IND = 'N'), while an entered date reprocesses requests completed that day. (stoploss/w_wang_idcard_response cb_ok.clicked, wf_process_idcards; stoploss/d_wang_idcard_req; stoploss/d_wang_idcard_req_complete)
• STL-47 — Each request is formatted into a fixed-length 314-character record (group, SSN formatted @@@-@@-@@@@, date/time, employer, names, coverage level, effective date, contact/mail addresses, coverage type letter M/D/V, option code, COBRA flag); family and single-plus-one coverage levels produce two card records, single produces one. (stoploss/w_wang_idcard_response wf_process_idcards)
• STL-48 — The output file defaults to G:\APPS\IDCARDS\misc<MMDD>.crd or grp<MMDD>.crd; the user is warned before overwriting; requests are stamped complete and committed only after the file saves successfully, otherwise the completion update is rolled back with an operator warning that the next run will repeat these cards. (stoploss/w_wang_idcard_response wf_process_idcards)

Miscellaneous

• STL-49 — The developer utility window opens an arbitrary sheet by class name, but only for users granted the Oracle DEVELOPER role. (stoploss/w_open_windowname cb_open.clicked)
• STL-50 — Long-running report processes support user cancellation with confirmation, periodic UI yields, and status text callbacks to a registered status control. (stoploss/n_stoploss of_cancel, of_yield, of_UpdateStatusInfo, of_RegisterStatusObject; stoploss/w_sl_specific_rpt cb_status, cb_cancel)

Contract parameter model

sl_contracts (contract header)

Key and identity: sl_contract_sys_key (from sequence SL_CONTRACT_SEQ), sl_contract_cd, description, policy_no, notes, carrier_sys_key → carriers. (stoploss/d_sl_contract_header; _re/analysis/schema.json)

Facet	Columns	Notes (as coded)
Level and period	contract_level_cd, contract_period_cd, start_dt, end_dt	Level from codes SLLEVELS (INDIVIDUAL / FAMILY / AGGREGATE handled in logic); period from codes SLPERIODS (code values not present in extract)
Claim windows	incurred_from_dt, incurred_thru_dt, paid_from_dt, paid_thru_dt	At least one range required; must overlap contract period (STL-9)
Specific terms	deductible_amt	Required > 0 for non-aggregate contracts (STL-6)
Aggregate terms	min_attachment_amt, min_attachment_pct, aggr_specific_amt, corridor_pct, max_change_pct	Only min_attachment_amt is validated; the others are stored/edited with no computation found in this library
TLO	tlo_period_type_cd, tlo_period_length, tlo_exercised_flg, tlo_attachment_amt	STL-7/STL-8
Run-in / run-out	run_in_period_type_cd, run_in_period_length, run_in_limit_amt, run_out_period_type_cd, run_out_period_length	Pair validation only (STL-7); no accumulation logic reads them here
Reimbursement	reim_domestic_pct, reim_standard_pct, member_reimb_limit, contract_reimb_limit	Stored only; no reimbursement calculation found in this library

Child tables (all keyed by sl_contract_sys_key): sl_contract_groups (group_id, eff/exp dates), sl_contract_coverages (coverage types), sl_contract_excl_plans (coverage type + benefit coverage cd), sl_contract_factors (coverage type, coverage level, factor type STANDARD/TLO, start date, factor amount, units), sl_contract_lasers (sub/dep member keys, per-laser deductible and incurred/paid windows), sl_contract_domestic_tins (TIN, TIN type), sl_contract_option_vals (option cd, eff/exp, typed value columns) with option metadata in sl_contract_options. (stoploss/d_sl_contract_group_maint, d_sl_contract_coverage_maint, d_sl_contract_excl_plan_maint, d_sl_contract_factor_maint, d_sl_contract_laser_maint, d_sl_contract_domestic_tin_maint, d_sl_contract_option_val_list)

group_stoploss_parms (group plan parameters — read by all accumulation reports)

One row per group + plan start_dt (with end_dt), carrying both specific and aggregate terms:

Facet	Columns	Behavior in code
Specific limits	spec_individual_limit, spec_family_limit	Attachment thresholds for individual/family reporting (STL-26, STL-34)
Specific basis	spec_contract_inc, spec_contract_paid	Months defining incurred and paid windows (STL-20); shown as "Inc / Paid" contract type
Specific TLO	spec_tlo_ind, spec_tlo_mm	Extends the paid window (STL-21)
Specific carrier	spec_carrier	Joined to carriers elsewhere; not used by reports in this library
Aggregate	aggr_min_attchment_pt, aggr_carrier, aggr_contract_inc, aggr_contract_paid, aggr_tlo_ind, aggr_tlo_mm	aggr_min_attchment_pt drives d_sl_agg_summary percent met; note d_sl_agg_summary windows dates using the spec month columns, not the aggregate ones
Aggregating-specific	agspec_ind, agspec_amt	Stored; no logic found in this library
Composite factors	comp_factor_single/family/composite/tier3/tier4, comp_tlo_factor_*, gsp_election_tier3_desc, gsp_election_tier4_desc	Retrieved by stoploss/d_sl_parms; no calculation found in this library
Lifecycle	start_dt, end_dt, termination_reason	Plan selection by start date; current plan = start ≤ today ≤ end (stoploss/w_sl_spec_summary pfc_retrieve)

Covered coverage types per plan live in group_stoploss_coverages (group_id, start_dt, stoploss_type SPECIFIC — aggregate rows carry per-coverage factor columns aggr_factor_*, tlo_factor_* per stoploss/d_sl_coverages).

How claim payments feed stop-loss accumulation

Accumulation is computed at report time from the claims funding ledger — there is no stored stop-loss accumulator table in this library:

1. Claim adjudication produces claims rows; funding produces fund_register rows (paid_dt/applied_dt, paid_amt/applied_amt, payment_type_cd, waiver_ind, backout_ind) tied to claims. (Read-only here: _re/analysis/schema.json crud for stoploss marks claims, fund_register, fundtrans, v_fund_register_rpt as R.)
2. Reports select payments where the paid/applied date is inside the plan's paid window and the claim's service dates are inside the incurred window (STL-20, STL-23), the claim's type_of_claim is among the plan's SPECIFIC coverage types (STL-24), the group matches the plan's group hierarchy, and payment type is not EXPECTED (STL-37).
3. Sums by patient (individual) or subscriber (family) are compared to spec_individual_limit / spec_family_limit (STL-26); pending payable/claimed are layered on from unapplied register rows and claim status order (STL-32).
4. The daily report additionally requires a payment on the report date (TOTAL_NEW > 0) so a member appears the day a payment moves them, and flags the first crossing (STL-28).

Data + CRUD

CRUD as recorded for the stoploss library (_re/analysis/schema.json):

Table	CRUD	Where
sl_contracts	CRUD	stoploss/w_sl_contract_maint (d_sl_contract_header updater)
sl_contract_groups	CRUD	stoploss/d_sl_contract_group_maint
sl_contract_coverages	CRUD	stoploss/d_sl_contract_coverage_maint
sl_contract_factors	CRUD	stoploss/d_sl_contract_factor_maint
sl_contract_excl_plans	CRUD	stoploss/d_sl_contract_excl_plan_maint
sl_contract_lasers	CRUD	stoploss/d_sl_contract_laser_maint
sl_contract_domestic_tins	CRUD	stoploss/d_sl_contract_domestic_tin_maint
sl_contract_option_vals	CRUD	stoploss/d_sl_contract_option_val_list
sl_contract_options	R	option metadata (stoploss/d_sl_contract_option_val_list, dddw_sl_option_codes)
group_stoploss_parms	CRUD flag, but only reads observed in this library	stoploss/d_sl_parms declares updatable columns; maintenance windows live in the client library (client/w_stoploss_contract, client/w_stoploss_group)
group_stoploss_coverages	CRUD flag, reads observed	stoploss/d_sl_coverages
id_card_req	CRUD	stoploss/w_wang_idcard_response (marks req_complete_dt)
claims, claim_lines, fund_register, fundtrans, v_fund_register_rpt, claim_status_order, claim_statuses, members, member_eligibilities, groups, group_coverages, coverages, codes, carriers, elections, election_coverages, family, providers, vendors, addresses, group_contacts, member_cat_val, diagnosis_codes, procedure_codes, benefit_defs, benefit_rules, claim_line_rules, claim_entry_diag_codes, user_role_privs	R	report/lookup joins across the module

Key relationships (from _re/analysis/schema.json relationships): group_stoploss_coverages(group_id, start_dt) → group_stoploss_parms; group_stoploss_parms.group_id → groups; group_stoploss_parms.spec_carrier and sl_contracts.carrier_sys_key → carriers; sl_contract_groups.group_id → groups; sl_contract_lasers.sub/dep_member_sys_key → members / member_eligibilities; sl_contract_option_vals.sl_contract_option_cd → sl_contract_options.

Reports

Report	DataWindow(s)	Launched from	Content
Daily specific stop-loss detail	stoploss/d_sl_spec_rpt	w_sl_specific_rpt via n_stoploss	Claim-level payments for each individual/family exceeding the specific limit on the run date, with first-appearance flag
Daily specific stop-loss listing	stoploss/d_sl_spec_listing	same run	One line per reported member: limits, plan dates, new vs total paid, first appearance
Specific stop-loss summary (individual)	stoploss/d_sl_specific_stoploss_summary	w_sl_spec_summary	Per-patient paid, percent met, pending claimed/payable; threshold filters
Specific stop-loss summary (family)	stoploss/d_sl_specific_stoploss_family_summary	w_sl_spec_summary (Family Specific)	Per-family equivalents
Summary spreadsheet export	stoploss/d_sl_specific_stoploss_summary_excel	w_sl_spec_summary Export to Excel	Flattened rows plus totals
Specific summary (legacy per stop-loss group)	stoploss/d_sl_specific_summary, stoploss/d_sl_spec_summary	w_sl_summary / w_sl_specific_summary	Printed per stop-loss group, saved as .psr
Aggregate summary	stoploss/d_sl_agg_summary; d_sl_spec_2 (not in extract)	w_sl_summary aggregate path	Total paid vs aggregate minimum attachment, percent met
Member specific claims listing	stoploss/d_sl_spec_clm_listing	w_sl_spec_clm_listing	All payments in one member's plan-year accumulation; save-as export
Variable-period claims listing	stoploss/d_sl_variable_clm_listing + d_sl_export_maz	w_sl_variable_clm_listing	Same, over an arbitrary paid range, with claim-level export
Claim drill-down parts	stoploss/d_sl_claim_line_detail, d_sl_claim_line_rules, d_sl_diag_detail	(nested report components)	Claim line detail, applied benefit rules/reasons, diagnoses
Wang ID-card file	stoploss/d_wang_idcard_data (from d_wang_idcard_req / d_wang_idcard_req_complete)	w_wang_idcard_response	Fixed-length .CRD batch file

Migration notes

• Two overlapping models must be reconciled. All accumulation logic reads group_stoploss_parms / group_stoploss_coverages; the richer sl_contracts model is maintained but never consumed by any report in this library. A target system should pick one contract model and migrate both sources into it, confirming with the business which sl_contracts facets (lasers, excluded plans, factors, options, run-in/run-out, reimbursement terms) are actually in production use. (Citations: contrast stoploss/w_sl_contract_maint with stoploss/n_stoploss and every d_sl_*summary/listing SQL.)
• Accumulation is recomputed from the funding ledger at report time (no accumulator table). A replacement can either preserve this (view/materialized query over payments) or introduce explicit accumulators; if the latter, the waiver, backout, EXPECTED-payment, and pending-claim conventions (STL-32, STL-37) must be reproduced. (stoploss/d_sl_specific_stoploss_summary, d_sl_patient_over_spec)
• Windowing arithmetic is month-based with second-level end-of-window adjustments (ADD_MONTHS(...) - 1/86400); date semantics (inclusive ends, 01:00:00/23:59:59 daily run bounds in w_sl_specific_rpt) should be captured as explicit period rules. (stoploss/d_sl_spec_clm_listing; stoploss/n_stoploss of_SpecificStoplossRpt)
• Sentinel dates 01/01/1900 (invalid), 01/01/1950 (option default), 12/31/2499 (open end) pervade validations and SQL and must be mapped to proper nulls. (stoploss/w_sl_contract_maint; stoploss/d_sl_member_stoploss_parms; stoploss/d_sl_variable_clm_listing)
• Bounded arrays limit plan history: only the latest 10 plans / 10 coverages are processed by the daily report (STL-22) — a silent functional ceiling to remove in migration. (stoploss/str_spec_plan)
• Hierarchy semantics matter: group roll-ups use Oracle CONNECT BY both downward (all leaf groups under a master) and upward (find master ancestor with MASTER_GROUP_FLG='Y'); member plan resolution walks eligibility group upward. (stoploss/d_sl_master_groups; stoploss/w_sl_contract_maint dw_sl_contract_groups.pfc_retrieve; stoploss/d_sl_member_stoploss_parms)
• Row-level security: report entry points enforce f_check_group_security per group (global function outside this library); the equivalent authorization layer is required in the target. (stoploss/w_sl_spec_summary; stoploss/w_sl_specific_rpt)
• Suspected defects to verify before porting behavior verbatim (in stoploss/n_stoploss of_SpecificStoplossRpt): the family-member patient array is filled using the loop count rather than the loop index (ll_patient[ll_member_row] = ... [ll_member_count]), and family paid/new amounts are read from the patient datastore indexed by the family row. Confirm intent with report output before reimplementing.
• Embedded one-off fixes exist in report code (commented 2018 per-group override for group 501-883; 2021 loop overwriting member group ids to latest eligibility) — decide whether these are policy or workarounds. (stoploss/w_sl_spec_summary dw_report.pfc_retrieve)
• The Wang ID-card export belongs functionally to ID-card/enrollment, writes to a hard-coded drive path, and encodes a fixed record layout; treat as a separate legacy interface to retire or re-home. (stoploss/w_wang_idcard_response)

Open questions

1. Missing DataWindows. d_sl_spec_2 (aggregate report layout used by w_sl_summary/w_sl_specific_summary), d_sl_group_select, d_sl_member_selection, d_sl_parm_select, and d_group_hierarchy_bottom_up are referenced from stoploss windows but are not present in the extracted source of any library (verified: they appear only as name references inside compiled code, with no source entry in any PBL — likely deleted objects or leftovers from an unshipped library). d_sl_spec_summary was recovered (_re/src/stoploss/d_sl_spec_summary.srd) and can be documented on request. Their SQL (and therefore the aggregate report's exact accumulation) cannot be documented from this extract.
2. DataObject name mismatch. stoploss/w_wang_idcard_response wf_process_idcards assigns DataObject = 'd_wang_idcard_req_completed' but the library ships d_wang_idcard_req_complete (no trailing "d"). Is the completed-date rerun path functional in production?
3. Which contract model is authoritative? No code in this library feeds accumulation from sl_contracts (deductibles, lasers, excluded plans, run-in/run-out, TLO attachment, reimbursement fields, factors, options). Is another module (e.g. claims adjudication or funding) consuming these tables, or is the contract editor record-keeping only?
4. Unused parameters. sl_contracts.min_attachment_pct, corridor_pct, max_change_pct, aggr_specific_amt, member_reimb_limit, contract_reimb_limit, run_in_limit_amt; group_stoploss_parms.agspec_ind/agspec_amt, comp_factor_*, comp_tlo_factor_*, spec_carrier/aggr_carrier, termination_reason — stored/edited but with no computation found in this library. What business behavior should they drive?
5. Aggregate windowing uses specific columns. stoploss/d_sl_agg_summary windows paid/incurred dates with SPEC_CONTRACT_PAID / SPEC_CONTRACT_INC rather than AGGR_CONTRACT_PAID / AGGR_CONTRACT_INC, and its coverage subquery does not filter stoploss_type. Intentional (aggregate follows the specific basis) or defect?
6. "TLO" expansion and code sets. Code tables SLPERIODS, SLLEVELS, SLFACTORS, SLCOVERAGE, SLOPTIONS, PERIOD, ELECTLEVEL values are data, not in the extract; the meaning of contract period codes (e.g. whether they encode 12/12, 15/12 paid-vs-incurred bases) and the exact expansion of "TLO" need confirmation from the database or business.
7. Two summary generations. w_sl_summary (menu m_stoploss1, prints .psr files per stop-loss group) vs w_sl_spec_summary (interactive thresholds, pending, Excel) implement overlapping specific summaries with different DataWindows and filter defaults. Which is the surviving business process?
8. w_sl_specific_summary is a plain window duplicating w_sl_summary logic with no menu reference found in this library — dead code?
9. Laser and domestic-TIN effect on claims. Laser rows carry their own deductible and date windows and domestic TINs mark providers for the reim_domestic_pct rate, but no claim-side application exists in this library. Where (if anywhere) are they applied?
10. d_sl_parms updatability. The DataWindow marks all group_stoploss_parms columns updatable, yet the module only retrieves through it; actual maintenance appears in the client library (client/w_stoploss_contract, client/w_stoploss_group). Confirm the system of record for plan parameters when scoping migration.
11. Suspected loop-index defects in of_SpecificStoplossRpt (see Migration notes) — bug or accidental-but-relied-upon behavior?