Skip to main content

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.

The user guide (ch. 17) records the redesign that produced the second model: its table-change list drops the month-count columns (spec_contract_inc/paid, aggr_contract_inc/paid), the TLO indicator/month columns, and the composite-factor columns from group_stoploss_parms, and specifies contract types, run-in/run-out periods and limits, reimbursement rates (domestic vs standard), coinsurance/maximum reimbursement limits, TLO period/exercised fields, a common-accident flag, plus new Laser, Domestic Provider, and State Surcharge tables — very nearly the feature set the sl_contracts family implements. sl_contracts is therefore the intended successor model per the design record; the accumulation reports were never re-pointed to it. (User Guide ch. 17)

Actors​

The module carries no explicit role model; access is governed by group-level security and one Oracle role.

ActorEvidence
Stop-loss department staff — maintain contracts, run the daily specific report, answer carrier queriesstoploss/w_sl_contract_selection → w_sl_contract_maint; stoploss/w_sl_specific_rpt. The guide assigns the department the missed-renewal notification (STL-69) (User Guide ch. 17)
Account managers / analysts — run threshold summaries and member claim listings, export to spreadsheetstoploss/w_sl_spec_summary, w_sl_spec_clm_listing, w_sl_variable_clm_listing
Accounting — release held worksheets when a carrier check arrives (waiver clients); record and forward carrier checks (non-waiver clients)(User Guide ch. 17); no screen in this library implements it (STL-63, STL-65)
Any report user — constrained to their permitted groupsf_check_group_security is called per group on report entry (STL-34)
Developers — open an arbitrary window by class namestoploss/w_open_windowname, gated on the Oracle DEVELOPER role (STL-49)

Business context (User Guide ch. 17)​

The guide's stop-loss chapter is a design-era document (definitions, parameter-screen rules, and 1996–1999 working-session notes). It documents intended business behavior; statements below are business/design intent unless tied to an STL requirement verified in code.

Stop-loss is insurance purchased by the TPA's self-funded clients to cap their liability when claims in a stop-loss period reach extreme dollar amounts. Clients contract directly with a stop-loss carrier; the TPA (SFPI/M3) manages the financial and data exchanges in two roles: paying stop-loss premiums on the client's behalf, and filing claims against the carrier when deductible amounts are exceeded.

Glossary:

  • Specific stop-loss — accumulates claim dollars per member or per family. When the specific deductible is hit, the TPA reports the entire claim amount plus the excess over the deductible in order to file against the carrier; filing requires that the client has funded up to the specific deductible. Applies to medical claims only.
  • Aggregate stop-loss — accumulates across all members of a client group, counting only eligible dollars paid by the client; member/family-paid amounts and carrier-paid amounts (over specific deductibles) are excluded. May cover any mixture of coverages.
  • Aggregated specific stop-loss — a client-level fund applied after each member/family specific deductible and before carrier liability; medical claims only, and client dollars paid against it still accumulate toward aggregate stop-loss. Worked example from the guide: with a $100K specific and $50K aggregated specific, a $300K claim consumes $100K (specific) then $50K (aggregated specific), leaving $150K carrier liability; across multiple claims the aggregated-specific fund depletes claim by claim until exhausted.
  • Attachment point — the dollar level at which the carrier becomes liable under aggregate rules; generally aggregate factor(s) times membership, calculated monthly as a performance benchmark, with a final attachment point at stop-loss year end determining carrier liability.
  • Minimum attachment point — annual floor under the calculated attachment point, applied at plan year end; the attachment point may not drop below it even if membership falls.
  • % Rule — the covered units used in a month's attachment-point calculation may not fall more than a set percentage below the prior month's; the percentage is always 0%, 5%, or 10%.
  • Waiver — the portion of a claim remaining when the client pays only up to its specific deductible; the client stops funding that member and the held amount waits for carrier reimbursement. Waivers apply to specific stop-loss only. (Non-waiver clients instead pay the full claim immediately and await reimbursement.)
  • TLO (Terminal Liability Option) — purchased by a client to extend stop-loss coverage at the end of a plan year when the case terminates with the TPA or carriers change; a mid-year carrier change or termination must not trigger TLO. TLO duration is always entered in months and acts to extend the contract's paid-date end.
  • Run-in limit — admits claims incurred a limited time (usually three months) before the group effective date; the time limit is implied by the contract type and may be paired with an additional flat dollar limit.
  • Laser — a member carrying a unique specific-deductible override amount.
  • Stop-loss contract types — 0/12 (paid, unlimited run-in), 12/12 (incurred and paid), 15/12 (paid, 3-month run-in), 24/12 (paid, 12-month run-in), 12/15 and 12/24 (incurred and paid plus 3 / plus 12 months), 12/12 with TLO, and paid with TLO.

Ineligible claims — claims the plan document says to deny but which the client has chosen to pay anyway — must always be bypassed by all stop-loss processing. (User Guide ch. 17)

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, but the intended business process is documented in the user guide (ch. 17):

  • When a member hits the specific deductible, a submission letter to the carrier is generated — as part of the check-run or manually requested. Claims never span submissions.
  • When carrier payments are received and deposited, an acknowledgement letter is generated.
  • Submissions and acknowledgements are tracked per member per contract period in dedicated tables (submission header typed Submission/Acknowledgement, associated claim numbers, and adjusting entries typed Refund, Underpayment, or Full Payment).
  • For waiver clients, reimbursement-form generation is driven by waiver status (see STL-63); when the carrier check arrives it is routed to accounting to release the held worksheets. For non-waiver clients, the returned carrier check is recorded and forwarded back to the client with a letter.

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 (the guide's Domestic Provider table exists to drive the domestic vs standard reimbursement rate), but no window or DataWindow in stoploss generates a carrier filing, reimbursement request, submission-tracking row, or reimbursement transaction. The guide (ch. 17) prescribes system-generated reimbursement forms and submission-tracking updates — functionality not found in this library; discrepancy for SME confirmation whether it was implemented elsewhere (funding/accounting) or remained manual. See STL-61 through STL-66 and 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 and stoploss/d_sl_specific_stoploss_summary; header compute "Contract Type (Inc/Paid)" in stoploss/d_sl_specific_stoploss_summary.) Two divergences to carry into any re-implementation: the PowerBuilder code adds the day before subtracting months (of_RelativeMonth(end_dt + 1 day, -inc)) while the SQL subtracts months then adds a day — equivalent in ordinary cases but not identical at month boundaries; and stoploss/d_sl_agg_summary ends its paid window with - 1 (a whole day) rather than - 1/86400 (one second), so the aggregate report's paid window is a day shorter than the specific reports'.
  • 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 (STL-24). A claim only partially inside the incurred window — either end outside — is not accumulated and raises "Claim crossed stoploss period". Two further conditions shape the population: register rows with a zero paid amount are skipped before any date test, and the window test (of_Between) treats a null bound as unbounded on that side, so a plan with a null paid-thru or incurred-thru date has an open-ended window rather than an empty one. (stoploss/n_stoploss of_SpecRegTotal, of_Between)
  • STL-24 — Plan coverages are restricted to rows with stoploss_type = 'SPECIFIC'. Coverage matching between a claim and those coverages is an exact string comparison, and the two sides are normalized differently: the plan side is stored Upper(Trim(...)) when loaded into the plan structure, while the claim side is compared as the raw claims_type_of_claim value off the fund register. A register value carrying lower case or leading whitespace therefore fails to match silently and the payment is not accumulated — a data-quality trap to reproduce or deliberately fix in migration. (stoploss/n_stoploss of_GroupSpecPlans filter and of_SpecRegTotal compare; 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. The aggregate report's percentage is computed differently and is not effectively capped — see STL-41.)
  • 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) The register-date convention matches the guide's rule that a waivered claim's plan year is set by the date it became eligible to appear on a register (STL-64). (User Guide ch. 17)
  • 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 — LEAST(SUM(FUND_REGISTER.PAID_AMT) / GROUP_STOPLOSS_PARMS.AGGR_MIN_ATTCHMENT_PT, 100) — over the same incurred/paid windowing as specific (STL-20). The cap does not do what it appears to: the retrieved PCT_MET is a ratio, and the detail band re-renders it as pct_met * 100 with a #,###.00% format, so the LEAST(..., 100) guard binds at 10,000%, not at 100%. Percentages above 100 therefore display unclamped on this report, unlike the specific summaries (STL-31). Treat as a suspected defect. (stoploss/d_sl_agg_summary line 21 and detail-band expression)

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)

Business-process rules from the user guide​

These requirements come from the design-era user guide, not from extracted code; where the code partially implements one, the code citation is added. Treat them as business intent to confirm with SMEs before implementing.

  • STL-51 — Specific and aggregated-specific stop-loss apply to medical claims only; aggregate stop-loss can apply to any mixture of coverages. (User Guide ch. 17)
  • STL-52 — Ineligible claims (denied by the plan document but paid at the client's direction) must always be bypassed by all stop-loss processing. (User Guide ch. 17)
  • STL-53 — Aggregate accumulation counts only eligible dollars paid by the client; amounts paid by members/families and amounts reimbursed by carriers over specific deductibles are excluded from the aggregate total. Aggregated-specific likewise looks only at client-paid dollars above each member/family specific. (User Guide ch. 17)
  • STL-54 — Aggregated specific stop-loss is applied after the member/family specific deductible and before carrier liability, drawing down a client-level fund; client dollars applied to it still accumulate toward aggregate stop-loss. (User Guide ch. 17; group_stoploss_parms.agspec_ind/agspec_amt and sl_contracts.aggr_specific_amt store the fund with no consuming logic in this library)
  • STL-55 — The aggregate attachment point is calculated monthly as aggregate factor(s) times membership (a performance benchmark); a final attachment point at stop-loss year end determines carrier liability; the attachment point may never fall below the minimum attachment point, which is applied annually at plan year end. (User Guide ch. 17)
  • STL-56 — The % Rule: covered units used in any month's attachment-point calculation may not be reduced by more than a set percentage of the prior month's total covered units; the percentage is always 0%, 5%, or 10%. (User Guide ch. 17 — semantics for sl_contracts.max_change_pct, which the code stores as a free numeric with no 0/5/10 restriction; discrepancy for SME confirmation)
  • STL-57 — Aggregate factors are entered as Single and Family together, or else Composite alone; factors entered at the tab/"factors area" level apply to all selected coverages unless a per-coverage amount overrides them, and the TLO factor area follows the same rule. Factor amounts are always cleared (and must be re-entered) when a contract is added via the "inherit contract" function. (User Guide ch. 17; the code's factor grid adds <Default> rows, tier3/tier4 levels and a "2-tier only" restriction — STL-15)
  • STL-58 — Stop-loss contract types (0/12, 12/12, 15/12, 24/12, 12/15, 12/24, with-TLO variants) are implemented as incurred/paid date ranges per stop-loss year; the system generates default ranges from the selected type (paid range starts at plan year start; incurred range ends at plan year end) which the user may override. If two stop-loss years' ranges overlap for a client, the parameters of the most recent stop-loss year win. Stop-loss years correspond to plan years and only expire when a termination date is entered. (User Guide ch. 17)
  • STL-59 — TLO is triggered by the termination reason entered with the stop-loss termination date: a reason indicating carrier change or case termination (at the end of a stop-loss period) adds the TLO duration to the ending paid date; changing the reason away from those values subtracts it. TLO duration is always entered in months. (User Guide ch. 17 — semantics for group_stoploss_parms.termination_reason and the sl_contracts TLO fields)
  • STL-60 — Run-in limits admit claims incurred for a limited time (usually three months) before the group effective date; the run-in time limit is implied by the selected contract type, and an aggregated-specific amount and flat dollar limit may additionally be entered when a run-in limit exists. (User Guide ch. 17 — semantics for sl_contracts.run_in_*)
  • STL-61 — Waiver status exists at both member/family and claim level, date-ranged for the stop-loss period: a waivered member's period runs through the end of the stop-loss year and must be extended to the new TLO end date when TLO kicks in. Whether subscriber, dependent, or both are waivered follows the plan's single/family handling parameters. Waivers must not be allowed for aggregated-specific claims. (User Guide ch. 17; no waiver-status table or aggregated-specific enforcement found in this library — the code only reads fund_register.waiver_ind, STL-37)
  • STL-62 — Waiver placement is semi-automatic: staff manually put payments (or parts of payments) into waiver, and the system then puts the member/family into waiver; staff may also waiver a member/family directly, which automatically waivers all stop-loss-eligible unfunded payments; as claims transition from adjudication into funding, payments for a waivered member/family are automatically put into waiver. Claims do not come off waiver until they have been funded. (User Guide ch. 17)
  • STL-63 — Reimbursement forms are system-generated: whenever a payment enters waiver and the specific deductible has been funded, generate a reimbursement form to the carrier and update the submission-tracking tables; when later payments bring the funded amount up to the specific deductible, generate forms for the paid claims already in waiver. For non-waiver groups, generate the form when the deductible is met and then for each following claim as it is paid. (User Guide ch. 17; not implemented in this library)
  • STL-64 — Plan-year determination for waivered claims: a member not in waiver uses the claim's paid date (so a 12/31 claim that would trigger waiver requires a check run before 1/1); a member in waiver uses the date the claim became eligible to appear on a register. (User Guide ch. 17; corroborates STL-37's use of the register date for unpaid waiver payments)
  • STL-65 — Submission tracking: a member may have multiple submission and acknowledgement rows per contract period; each submission may reference many claims but claims never span submissions; each submission may carry adjusting entries typed Refund, Underpayment, or Full Payment. Submission letters are generated at deductible hit (check-run or manual); acknowledgement letters when carrier payments are received and deposited. (User Guide ch. 17; no such tables/screens exist in this library)
  • STL-66 — Filing prerequisites: before filing for specific reimbursement the client must have paid up to the limit of the specific deductible, and the filing reports the entire claim amount as well as the amount above the specific deductible. (User Guide ch. 17)
  • STL-67 — 50% notification: a report must list claimants exceeding 50% of their specific deductible, tracked by amount processed and/or paid; the carrier record carries a flag for whether the carrier requires notification at 50%. (User Guide ch. 17, tracing to a 10/28/94 work request; the code's over-50% summary filter (STL-33) and the legacy report's automatic specific-at-50% output are the observed implementations)
  • STL-68 — Premiums: specific and aggregate stop-loss have separate premiums with separate rates, may be held by different carriers, and may be paid separately; premium is a set rate times the stop-loss membership count (members or families) or, for some clients, a flat monthly dollar amount. Payment routes: TPA checks the carrier directly, or the client remits through the TPA which records and forwards the check. COBRA members must be included in all stop-loss counts and reports. (User Guide ch. 17 — context for spec_carrier/aggr_carrier and the composite factor columns)
  • STL-69 — Missed-renewal detection: a weekly automatic report lists client groups flagged as having stop-loss but lacking stop-loss parameters, notifying the stop-loss department on completion; client maintenance carries a has-stop-loss indicator defaulting to Yes. (User Guide ch. 17; not found in this library)
  • STL-70 — Lasers identify members with unique specific-deductible override amounts; the domestic provider list identifies providers (by tax id, EIN or SSN) reimbursed at the domestic rate; a state surcharge list restricts stop-loss surcharge application, per specific/aggregate, to the listed states. (User Guide ch. 17 — intent for sl_contract_lasers and sl_contract_domestic_tins; no state-surcharge table appears in the extracted model)
  • STL-71 — Split claims: the pre-deductible portion of a claim may be paid while the above-deductible portion is held; and for clients requiring authorization prior to payment, the client must approve all payments regardless of stop-loss. (User Guide ch. 17)
  • STL-72 — Common-accident accumulators. Determining whether the specific deductible has been met requires two accumulators per member, not one: deductible attributable to the member, and deductible arriving from other members of the same family through a common accident. A matching pair is required for the lifetime stop-loss accumulators. The sl_contracts redesign adds a common-accident flag to carry the switch; the accumulation logic itself is adjudication-side and outside this library. This doubles the accumulator cardinality a target system must model — plan accordingly. (User Guide ch. 17; common-accident logic is specified in ch. 11, Adjudication)
  • STL-73 — Carrier notification of plan and repricing changes; date-ranged carrier fees. The TPA carries an obligation to notify stop-loss carriers when a client's benefit plans or repricing rules change; amendments are sent whenever the change produces new rates for the carrier fees. The guide records this process as entirely manual and names it an automation candidate once the triggering data elements and internal notification paths are identified. Separately, the system must be able to date-range the carrier fees. Neither capability exists in the extracted library. (User Guide ch. 17)
  • STL-74 — Contract-intake control procedure. Stop-loss contract review is a checklist, not free-form data entry: check the contract's factors against the SFPI proposal, check the rates, confirm aggregate-specific-liability coverage and any endorsement, record the policy number, and remove the case from the pending list. A parallel checklist governs life and LTD policies. This is the department's only documented control over what reaches sl_contracts; no screen enforces it. (User Guide ch. 17)

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)

FacetColumnsNotes (as coded)
Level and periodcontract_level_cd, contract_period_cd, start_dt, end_dtLevel from codes SLLEVELS (INDIVIDUAL / FAMILY / AGGREGATE handled in logic); period from codes SLPERIODS (code values not present in extract)
Claim windowsincurred_from_dt, incurred_thru_dt, paid_from_dt, paid_thru_dtAt least one range required; must overlap contract period (STL-9)
Specific termsdeductible_amtRequired > 0 for non-aggregate contracts (STL-6)
Aggregate termsmin_attachment_amt, min_attachment_pct, aggr_specific_amt, corridor_pct, max_change_pctOnly min_attachment_amt is validated; the others are stored/edited with no computation found in this library. Guide semantics: aggr_specific_amt = aggregated-specific fund (STL-54); max_change_pct = the % Rule, always 0/5/10 (STL-56); corridor_pct = the guide's aggregate corridor percentage; minimum attachment applied annually (STL-55) (User Guide ch. 17)
TLOtlo_period_type_cd, tlo_period_length, tlo_exercised_flg, tlo_attachment_amtSTL-7/STL-8. TLO = Terminal Liability Option: months extending the paid-date end at case termination / carrier change, triggered by termination reason (STL-59) (User Guide ch. 17)
Run-in / run-outrun_in_period_type_cd, run_in_period_length, run_in_limit_amt, run_out_period_type_cd, run_out_period_lengthPair validation only (STL-7); no accumulation logic reads them here. Run-in admits pre-effective-date incurred claims, time limit implied by contract type plus optional dollar cap (STL-60) (User Guide ch. 17)
Reimbursementreim_domestic_pct, reim_standard_pct, member_reimb_limit, contract_reimb_limitStored only; no reimbursement calculation found in this library. Guide semantics: separate reimbursement rates for domestic providers (per the domestic-TIN list) vs standard providers, with per-member coinsurance and maximum limits (User Guide ch. 17)

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:

FacetColumnsBehavior in code
Specific limitsspec_individual_limit, spec_family_limitAttachment thresholds for individual/family reporting (STL-26, STL-34)
Specific basisspec_contract_inc, spec_contract_paidMonths defining incurred and paid windows (STL-20); shown as "Inc / Paid" contract type
Specific TLOspec_tlo_ind, spec_tlo_mmExtends the paid window (STL-21)
Specific carrierspec_carrierJoined to carriers elsewhere; not used by reports in this library. Specific and aggregate may be held by different carriers with separate premiums (STL-68) (User Guide ch. 17)
Aggregateaggr_min_attchment_pt, aggr_carrier, aggr_contract_inc, aggr_contract_paid, aggr_tlo_ind, aggr_tlo_mmaggr_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-specificagspec_ind, agspec_amtStored; no logic found in this library. Guide: the aggregated-specific fund applied after member/family specific, before carrier liability (STL-54) (User Guide ch. 17)
Composite factorscomp_factor_single/family/composite/tier3/tier4, comp_tlo_factor_*, gsp_election_tier3_desc, gsp_election_tier4_descRetrieved by stoploss/d_sl_parms; no calculation found in this library. Guide: attachment-point factors (rate × membership per election level) used in aggregate premium/attachment calculations; Single+Family together or Composite alone (STL-55, STL-57, STL-68) (User Guide ch. 17)
Lifecyclestart_dt, end_dt, termination_reasonPlan selection by start date; current plan = start ≤ today ≤ end (stoploss/w_sl_spec_summary pfc_retrieve). Guide: termination reason is the TLO trigger (STL-59), and stop-loss years only expire when a termination date is entered (STL-58) (User Guide ch. 17)

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

TableCRUDWhere
sl_contractsCRUDstoploss/w_sl_contract_maint (d_sl_contract_header updater)
sl_contract_groupsCRUDstoploss/d_sl_contract_group_maint
sl_contract_coveragesCRUDstoploss/d_sl_contract_coverage_maint
sl_contract_factorsCRUDstoploss/d_sl_contract_factor_maint
sl_contract_excl_plansCRUDstoploss/d_sl_contract_excl_plan_maint
sl_contract_lasersCRUDstoploss/d_sl_contract_laser_maint
sl_contract_domestic_tinsCRUDstoploss/d_sl_contract_domestic_tin_maint
sl_contract_option_valsCRUDstoploss/d_sl_contract_option_val_list
sl_contract_optionsRoption metadata (stoploss/d_sl_contract_option_val_list, dddw_sl_option_codes)
group_stoploss_parmsCRUD flag, but only reads observed in this librarystoploss/d_sl_parms declares updatable columns; maintenance windows live in the client library (client/w_stoploss_contract, client/w_stoploss_group)
group_stoploss_coveragesCRUD flag, reads observedstoploss/d_sl_coverages
id_card_reqCRUDstoploss/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_privsRreport/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​

ReportDataWindow(s)Launched fromContent
Daily specific stop-loss detailstoploss/d_sl_spec_rptw_sl_specific_rpt via n_stoplossClaim-level payments for each individual/family exceeding the specific limit on the run date, with first-appearance flag
Daily specific stop-loss listingstoploss/d_sl_spec_listingsame runOne line per reported member: limits, plan dates, new vs total paid, first appearance
Specific stop-loss summary (individual)stoploss/d_sl_specific_stoploss_summaryw_sl_spec_summaryPer-patient paid, percent met, pending claimed/payable; threshold filters
Specific stop-loss summary (family)stoploss/d_sl_specific_stoploss_family_summaryw_sl_spec_summary (Family Specific)Per-family equivalents
Summary spreadsheet exportstoploss/d_sl_specific_stoploss_summary_excelw_sl_spec_summary Export to ExcelFlattened rows plus totals
Specific summary (legacy per stop-loss group)stoploss/d_sl_specific_summary, stoploss/d_sl_spec_summaryw_sl_summary / w_sl_specific_summaryPrinted per stop-loss group, saved as .psr
Aggregate summarystoploss/d_sl_agg_summary; d_sl_spec_2 (not in extract)w_sl_summary aggregate pathTotal paid vs aggregate minimum attachment, percent met
Member specific claims listingstoploss/d_sl_spec_clm_listingw_sl_spec_clm_listingAll payments in one member's plan-year accumulation; save-as export
Variable-period claims listingstoploss/d_sl_variable_clm_listing + d_sl_export_mazw_sl_variable_clm_listingSame, over an arbitrary paid range, with claim-level export
Claim drill-down partsstoploss/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 filestoploss/d_wang_idcard_data (from d_wang_idcard_req / d_wang_idcard_req_complete)w_wang_idcard_responseFixed-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. The user guide (ch. 17) shows sl_contracts is the intended successor per the recorded redesign (see Open questions #3), so the migration direction is legacy → contract model. A target system should still confirm 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; User Guide ch. 17.)

  • 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. Four, in rough order of blast radius:

    1. Aggregate percent met is not clamped — the LEAST(..., 100) in d_sl_agg_summary guards a ratio that the detail band then multiplies by 100, so it binds at 10,000% (STL-41).
    2. Aggregate paid window is a day shorter than specific — d_sl_agg_summary ends its paid window with - 1 (a whole day) where the specific reports use - 1/86400 (one second) (STL-20).
    3. Coverage-code comparison is normalized on one side only, so case or whitespace variance in the register silently drops payments from accumulation (STL-24).
    4. Loop-index errors in 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.

    Each changes reported dollars or percentages, so confirm against known report output before reimplementing — and decide explicitly whether the target reproduces or repairs them.

  • 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? Partially resolved (User Guide ch. 17): the guide's redesign record drops the legacy month-count/TLO/composite columns from group_stoploss_parms and specifies exactly the contract-type, run-in/run-out, reimbursement, TLO, laser and domestic-provider features that sl_contracts implements — so sl_contracts is the intended successor and group_stoploss_parms the legacy model the redesign meant to replace. Still open: whether any other module (adjudication, funding) consumes sl_contracts in production, or whether the editor is record-keeping only while accumulation stayed on the legacy model.
  4. Unused parameters. Semantics resolved (User Guide ch. 17) for most of the stored-but-unused columns: aggr_specific_amt / agspec_ind/agspec_amt = aggregated-specific fund (STL-54); max_change_pct = % Rule 0/5/10 (STL-56); corridor_pct = aggregate corridor; min_attachment_pct relates to the annualized minimum-attachment calculation; member_reimb_limit/contract_reimb_limit and reim_domestic_pct/reim_standard_pct = reimbursement coinsurance/maximum limits and domestic-vs-standard rates; run_in_limit_amt = flat dollar run-in cap (STL-60); comp_factor_*/comp_tlo_factor_* = attachment-point/premium factors (STL-55, STL-57); spec_carrier/aggr_carrier = separate specific/aggregate carriers (STL-68); termination_reason = TLO trigger (STL-59). Still open: which of these are populated and acted on in production, and where the consuming logic lives (not in this library).
  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 — verified: AGGR_MIN_ATTCHMENT_PT is the only AGGR_ column referenced anywhere in the object — and its coverage subquery does not filter stoploss_type (unlike d_sl_spec_clm_listing and d_sl_specific_stoploss_summary, which both require 'SPECIFIC'), so the aggregate total draws on every coverage row of the plan. It also ends the paid window a full day earlier than the specific reports (STL-20). Intentional (aggregate follows the specific basis) or defect? Note: the user guide (ch. 17) treats incurred/paid date ranges as properties of the stop-loss year shared by the contract, and its redesign drops both the spec and aggr month-count columns — mildly supporting "intentional", but the guide also defines aggregate-specific contract types separately (Agg_contract_type etc.), so SME confirmation is still required.
  6. Code sets. Partially resolved (User Guide ch. 17): TLO expands to Terminal Liability Option, and the contract period codes encode the paid/incurred contract types 0/12, 12/12, 15/12, 24/12, 12/15, 12/24 and the with-TLO variants (STL-58) — SLPERIODS presumably carries these. Still open: the literal code values of SLPERIODS, SLLEVELS, SLFACTORS, SLCOVERAGE, SLOPTIONS, PERIOD, ELECTLEVEL are data, not in the extract, and need confirmation from the database.
  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. Intent resolved (User Guide ch. 17): lasers are members with unique specific-deductible override amounts, and the domestic-provider list exists so those providers are reimbursed at the domestic rate (reim_domestic_pct) rather than the standard rate (STL-70). Still open: 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?
  12. Electronic submissions. The user guide (ch. 17) lists "Processing Electronic Submissions" as a bare workflow heading for both specific and aggregate stop-loss with no content — no carrier electronic interfaces, formats, or partners are described anywhere in the chapter, and none exist in this library (the Wang ID-card .CRD export is enrollment-related, not a stop-loss submission). Confirm with SMEs whether any carrier electronic-submission interface was ever built, and where.
  13. Submission/acknowledgement tracking. The guide (ch. 17) specifies a submission-tracking data model (submissions, acknowledgements, claims-per-submission, Refund/Underpayment/Full Payment adjustments — STL-65) and system-generated reimbursement forms (STL-63), and states "a set of tables and screens will be required". No such tables or screens exist in the extracted stoploss library or its schema — discrepancy for SME confirmation: implemented elsewhere (funding/accounting), replaced by the pre-Homer WANG/FoxPro logs, or never built?
  14. Composite factor columns. The guide's redesign (ch. 17) drops comp_factor_composite and all comp_tlo_factor_* columns from group_stoploss_parms ("if composite, enter the composite amount in single and family") and drops aggr_factor_composite/tlo_factor_* from the coverages table — yet the extracted schema retains them and stoploss/d_sl_parms/d_sl_coverages still carry them. The user guide (ch. 17) states these columns were to be removed — discrepancy for SME confirmation whether they are dead columns or still populated.