Clients, Groups & Benefit Plans

Libraries: client, benplans, bensupport, sales (63 windows, 272 DataWindows) Requirement prefix: GRP

Purpose

This domain maintains the TPA's book of business: employer clients and their group hierarchies, the benefit coverages each group offers, and the benefit-plan rule structures that drive claims adjudication. It covers:

• Group maintenance (client): group identity, hierarchy (parent / master group), addresses, contacts, enrollment options, coverages offered, member categories, coverage-eligibility rules, plan-year and enrollment periods, group-level options, client-segment assignment, group/coverage termination, and stop-loss waiver entry.
• Benefit plan design (benplans): benefit plans, benefit packages, benefit rules (deductible / coinsurance / copay / maxima / exclusions / holds), benefit definitions (the service-classification criteria that rules attach to), rule contingencies, riders, and package-level rule interdependencies.
• Plan-design support data (bensupport): procedure / diagnosis / tooth / second-opinion code groups, carryover rules, coverage-level schedules, step-rate schedules, rounding rules, reason codes, and plan-conversion utilities.
• Sales / client reporting (sales): client-based reporting wizard (paid claims, lag triangles, IBNR, claimant registry), group claim-detail reports, and benefit service-category maintenance per client segment.

Actors

Actor	Evidence
Group / client administration staff — create and maintain groups, coverages, contacts, periods	client/w_client_enrollment opens client/w_client_maintenance in ADD / CHANGE / DETAIL modes
Plan-build analysts — create plans, packages, rules, definitions	benplans/w_benefit_plan_selection, benplans/w_create_benefit_rule, benplans/w_benefit_pkg_maint, benplans/w_benefit_def_maint
Plan-support analysts — maintain code groups, schedules, reason codes	bensupport/w_codes_maint, bensupport/w_level_sched_maint, bensupport/w_reason_code_maint
Sales / account managers — run client-based financial reports per client segment	sales/w_sales_report_wizard, sales/w_sales_benefit_category_maintenance
Security layer — user classes limit which groups a user may see	group pick lists join security_user_class_user and security_user_group (client/d_client_group_select, client/d_client_parent_group_ids)

Group hierarchy model

Groups form a tree via groups.parent_group_id (self-reference). Key attributes on groups: group_id (char 15, natural key), group_name, master_group_flg, grp_info_flg (informational-only group), funding_parent_group_id, group_type_cd, eff_dt / exp_dt / grp_exp_cd, tax_id, eligibility waiting-period fields (elig_waiting_period_type_cd, elig_waiting_period_cd, elig_waiting_period_no, elig_waiting_period_dt_from_cd), and external-carrier cross-reference fields (ext_elr, ext_control, ext_suffix, ext_account, ext_plan_number — shown as "ELR / CONTROL / SUFFIX / ACCOUNT / PLAN NO" in client/d_client_group_info_new_ext).

• The master group for any group is found by walking parent_group_id upward until a group with master_group_flg = 'Y' is reached (client/w_client_maintenance.wf_get_master_group_id, recursive).
• Hierarchy traversal uses Oracle CONNECT BY prior parent_group_id = group_id (upward, client/d_heirarchy) or CONNECT BY parent_group_id = PRIOR group_id (downward, e.g. client/w_client_term_group, bensupport/d_plan_rpt_group_rule_analysis).
• Coverages are resolved at leaf groups: the plan-analysis report gathers group_coverages only for groups with no children under the requested group (bensupport/d_plan_rpt_group_rule_analysis).
• Informational groups (grp_info_flg = 'Y') are excluded from hierarchy rollups (client/d_heirarchy filters grp_info_flg <> 'Y').

Key workflows

Group setup

1. User opens the client-enrollment browser (client/w_client_enrollment), searches by group id / name / contact, and chooses Add, Change, or View. The selection opens client/w_client_maintenance with mode ADD / CHANGE / DETAIL (wf_open_group_detail; DETAIL renders the window read-only — title suffix "(View Only Mode)" vs "(Edit Mode)" in ue_retrieve_windows).
2. Group Maintenance is a tabbed window (client/w_client_maintenance): Information (group info + contacts), Enrollment (enrollment options, enrollment/flex periods, group options), Coverages (coverages offered + per-coverage options), Categories (standard / non-standard member categories and values — master groups only), Coverage Eligibility (member-category-based eligibility rules per coverage).
3. On saving a new group, security rows are auto-created: security_user_group entries for user classes SFPI (skipped when the group id starts with 501-069), SFPI-Admin, and develop (wf_add_security).
4. A child group can be initialized by copying from its parent: the Copy Group Info dialog (client/w_client_copy_group_info) lets the user tick which sections to copy — group info, contacts, enrollment options, group options, coverages, categories (categories disabled unless the source is a master group) — each with its own source group and effective date. Billing and eligibility copy fields exist in the DataWindow but the eligibility copy call is commented out.

Coverage setup ("what the group offers")

Per group, group_coverages rows pair a coverage type (benefit_coverage_type_cd, from coverages) with a plan code (benefit_coverage_cd, from benefits), with eff_dt / exp_dt, waiting-period overrides, salary-based volume calculation fields (multiplier, increment, min/max/default amounts, rounding), ID-card form numbers, COBRA flags, tax status, and an "applied to members" mechanism (applied_to_members_dt, unapplied_chg_flg, mbr_cov_applied_cd, mbr_cov_reqd_flg) that records whether the coverage row has been pushed down to member elections (client/d_client_group_coverage_info, client/d_client_group_coverage_opts_new).

Plan configuration (see the full model below)

Plans, packages, rules, and definitions are built in benplans and linked so that a group's coverage row resolves, at adjudication time, to an ordered set of benefit packages and prioritized rules.

Renewal / plan periods

There is no explicit "renewal" transaction in this domain. Renewal is represented by effective-dated rows:

• group_periods stores period definitions per group (period_type_cd, eff_dt, exp_dt, begin/end month + day, extended_year_flg). The Enrollment tab retrieves types ENROLLMENT and FLEX (client/w_client_maintenance line region lsa_period_types[1]='ENROLLMENT'; lsa_period_types[2]='FLEX'); open enrollment is the ENROLLMENT-type row with exp_dt IS NULL (client/d_enrollment_periods_open). group_periods.period_type_cd also participates in claims-side plan-year logic (clmsproc has CRUD on group_periods per the schema CRUD matrix).
• Period changes made on a parent can be extended to divisions: the window copies the edited period row to every sub-group via a datastore loop (client/w_client_maintenance event ue_extend_to_div).
• Coverage rows, category rows, enrollment options, and group options are all effective/expiration dated, so a renewal that changes plan design is entered as a new dated row rather than an update in place.

Group / coverage termination

client/w_client_term_group ("Term Groups") terminates a whole group or drops one coverage type, with a chain of confirmation prompts and a final warning that the action "will take hours to undo":

• Drop one coverage: blocked while any member in the group subtree still has an active election of that coverage; otherwise updates group_coverages in the subtree with exp_dt, coverage_exp_cd, run_out_exp_dt.
• Term whole group: blocked while any member_eligibilities row in the subtree has exp_dt IS NULL; otherwise delegates to database procedures term_groups_master or term_groups_division depending on master_group_flg.
• A companion "term members" path expires (or deletes future-dated) elections / election_coverages rows in the subtree, or calls term_members_master / term_members_division.
• A pre-termination report of affected members can be produced (client/d_client_term_group_report).

Plan conversion

bensupport/w_convert_plan moves selected group-coverage rows from one plan code to another by executing stored procedure sfpidba.new_group_coverages(group_id, new_plan, old_plan, coverage_type, 'N', 'N') per selected row, committing after each.

Stop-loss support in this domain

• client/w_carrier_maint / client/w_carrier_select maintain stop-loss carriers (carriers: code, name, contact, phone/fax/e-mail, active_ind, waiver_ind).
• client/w_member_waivers (opened from client/w_member_waiver_select) records per-member stop-loss waivers (stoploss_waivers: waiver from/thru and incurred from/thru dates); client/w_stoploss_period_select picks the applicable stop-loss contract year from group_stoploss_parms (specific carrier joined to carriers, falling back from division to master group when no period exists at the division — see the message logic in w_member_waivers).
• client/w_stoploss_group / client/w_stoploss_contract provide read access to stop-loss contract data (contract detail itself belongs to the Stop-loss domain).

Business rules

Each rule is stated so it can be tested against the legacy behavior.

ID	Rule	Source
GRP-1	Every group has a unique 15-character group id; a group may reference a parent group, forming a tree, and exactly the groups flagged master_group_flg = 'Y' act as hierarchy roots for category and stop-loss resolution.	client/d_client_group_info, client/w_client_maintenance.wf_get_master_group_id
GRP-2	The master group of any group is resolved by following parent links upward until a group with the master flag is found; if a group has no parent it is its own master.	client/w_client_maintenance.wf_get_master_group_id
GRP-3	Groups flagged informational (grp_info_flg = 'Y') are excluded from hierarchy traversals used for coverage resolution.	client/d_heirarchy
GRP-4	Group pick lists only show groups linked to one of the requesting user's security user classes.	client/d_client_group_select, client/d_client_parent_group_ids, client/d_client_child_group_ids
GRP-5	When a new group is saved, security access rows are created automatically for the SFPI, SFPI-Admin, and develop user classes; the SFPI row is skipped for group ids beginning 501-069.	client/w_client_maintenance.wf_add_security
GRP-6	A group cannot be changed from master to non-master while it still has member categories defined.	client/w_client_maintenance.wf_set_cat_tabpage
GRP-7	A group expiration requires an expiration reason code (grp_exp_cd); the code list is a maintained code table.	client/w_client_maintenance.wf_validate_exp_code, client/d_client_group_info_new (dddw on exp code)
GRP-8	A group carries an eligibility waiting period (type, unit, length, from-date basis) that defaults into coverage-level waiting-period fields; coverage rows may override it.	client/d_client_group_info, client/d_client_group_coverage_opts_new
GRP-9	Each coverage a group offers is a group_coverages row keyed by group, coverage type, and plan code, effective-dated, with an ordering sequence (group_coverage_seq_no) assigned as max existing + 1 per group / plan / type.	client/d_client_group_coverage_info, client/w_client_maintenance.wf_set_seq_no
GRP-10	Coverage rows track application to members: applied_to_members_dt records when the coverage was applied, unapplied_chg_flg marks pending unapplied changes, mbr_cov_applied_cd states how it applies, mbr_cov_reqd_flg whether it is mandatory. The same mechanism exists for member categories and category values.	client/d_client_group_coverage_opts, client/d_client_group_mbr_categories, client/d_client_category_values
GRP-11	Group-level enrollment options are code-table values (codes.code_key = 'ENROLL') attached to a group with effective / expiration dates.	client/d_client_group_enroll_opts, client/dddw_client_enrollment_opts
GRP-12	Group options are a typed name/value store: group_options defines each option and how its value is saved (val_saved_as_cd — code, date, number, or character); group_option_vals stores effective-dated values with one populated column among option_val_cd / option_val_dt / option_val_no / option_val_char. Consumers select the populated column via DECODE and match on the run date between eff_dt and exp_dt (null-as-open-ended, defaulted to 12/31/2049).	client/d_client_group_options, client/d_client_census_group_option_vals
GRP-13	Member categories can be attached only to master groups; category effective/expiration date changes are validated against existing values across the master group.	client/w_client_maintenance.wf_set_cat_tabpage, wf_check_cat_eff_dates, wf_check_cat_exp_dates
GRP-14	Coverage eligibility rules restrict a coverage to members with a given category value: rows of (group, coverage type, plan code, member category, category value, eff/exp dates).	client/d_client_grp_coverage_eligibility_rules, grp_covr_elig_rules table
GRP-15	Group periods (plan-year / enrollment / flex windows) are defined per group as recurring month+day ranges with effective dating and an extended-year flag; the currently open enrollment period is the ENROLLMENT row with no expiration date.	client/d_enrollment_periods, client/d_enrollment_periods_open
GRP-16	Enrollment-period changes entered on a parent group can be propagated to all of its sub-groups in one action.	client/w_client_maintenance event ue_extend_to_div
GRP-17	A new child group may be initialized by selectively copying the parent's group info, contacts, enrollment options, group options, coverages, and (for master-group sources only) categories, each as of a chosen effective date.	client/w_client_copy_group_info
GRP-18	A coverage type cannot be dropped from a group while any member in the group's subtree still holds an active election of that coverage as of the drop date.	client/w_client_term_group (count of election_coverages before update)
GRP-19	A group cannot be terminated while any member eligibility in its subtree is still open (no expiration date).	client/w_client_term_group (count of member_eligibilities before term)
GRP-20	Dropping a coverage expires every matching group_coverages row in the subtree with the drop date, reason code, and optional run-out date; whole-group termination is delegated to database procedures that differ for master groups vs divisions.	client/w_client_term_group, procedures term_groups_master / term_groups_division
GRP-21	Group termination requires a termination reason and multi-step user confirmation, including explicit confirmation of drop date, run-out date, and reason.	client/w_client_term_group
GRP-22	A group belongs to at most one client segment used for sales reporting (client_segment_groups), editable from group maintenance.	client/d_client_client_segment, client/w_client_maintenance (dw list)
GRP-23	A benefit plan is identified by coverage type + plan code (benefit_plans keyed by benefit_coverage_type_cd, benefit_coverage_cd) and carries claim-filing and appeal limits (length + period), COB method, lifetime maximum, dependent age limits (student / non-student), pre-existing condition periods (subscriber and dependent, prior and elapsed), package-search logic (pkg_logic_cd), plan status, and WDI payment schedule.	benplans/d_benefit_plan_maint, benefit_plans columns
GRP-24	A plan attaches an ordered list of benefit packages (plan_pkgs), each effective-dated, optionally network-specific (hcn_sys_key), with a pricing plan, pricing logic, UR firm, and CM firm; search_order_seq controls package search order and is renumbered from display order on save.	benplans/d_plan_packages, benplans/w_create_benefit_plan.wf_set_sequence
GRP-25	Every plan must include a default (no-network) package attachment — a plan_pkgs row with null HCN and unexpired date — except drug plans whose status is ASMEDICAL, which may have zero packages.	benplans/w_create_benefit_plan.wf_check_default_pkg
GRP-26	Two attachments of packages with the same HCN under one plan may not have overlapping effective-date ranges.	benplans/w_create_benefit_plan.wf_check_dates
GRP-27	Renaming a plan code cascades the new code to the plan's package attachments in the same save.	benplans/w_create_benefit_plan.wf_rename_coverage_cd
GRP-28	A plan code must be unique within its coverage type.	benplans/w_create_benefit_plan.wf_check_cd_duplicates
GRP-29	A benefit package (benefit_pkgs) must have a package code, a default benefit definition, an unassigned-definition action (Hold / Exclude / Pay), and an unassigned-definition reason before it can be saved.	benplans/w_benefit_pkg_maint pfc_validation
GRP-30	Rules are attached to packages as effective-dated pkg_rules rows with a priority, member-scope flags (subscriber / spouse / dependent), and managed-care indicators (PCP, auth, pre-cert, pre-admission, referral).	benplans/d_pkg_rule_maint
GRP-31	A benefit rule (benefit_rules) has a type from code table BENRULE; observed types and their editors: DED (deductible), COI (coinsurance), COPAY, OOP (out-of-pocket), 100PCT (pay at 100%), CLAIMED / PAID (claimed / paid limits), EXCL (exclusion), HOLD.	benplans/w_create_benefit_rule.wf_set_accum_type, benplans/dddw_benrule
GRP-32	Rule amounts are either retained flat values (retain_val, individual_max_val, family_max_val, max_individuals_met) or scheduled by coverage level (level_sched_flg = 'Y' with level_sched_sys_key); the two are mutually exclusive and unused columns are nulled before save, per rule type.	benplans/w_create_benefit_rule.wf_reset_rule_detail
GRP-33	For deductible rules the retained percentage is fixed at 100; for COI and OOP rules the user enters a "pay at" percentage and the system stores its complement (100 − entered value) in retain_val.	benplans/w_create_benefit_rule (wf_reset_rule_detail, pfc_save)
GRP-34	Exclusion rules carry no accumulator, schedule, carryover, per-day, or unit fields — all are cleared on save; HOLD rules similarly have no detail.	benplans/w_create_benefit_rule.wf_reset_rule_detail, wf_set_accum_type
GRP-35	Each rule accumulates against a named accumulator (accum_sys_key → reason_codes) with an accumulator type of RETAINED or CONSIDERED depending on the rule type; the reason-code pick list is filtered to reasons of the rule's type or type ACCUM.	benplans/w_create_benefit_rule (wf_set_accum_type, event ue_filter_reason_cds), benplans/dddw_reason_accum
GRP-36	A rule's scope can be constrained by rule period (length + PLANPERIOD code), member age band (min/max length + period), considered-amount band (min/max), consider percentage, accident indicator, days of week, per-day basis (PERDAY codes), per-unit flag, unit counts (min/max), and quantity ratio.	benplans/d_ben_rules, benplans/d_ben_rule_ded
GRP-37	Benefit rule codes may contain only letters, digits, and - / \.	benplans/w_create_benefit_rule pfc_save match check
GRP-38	A rule is linked to the benefit definitions it governs through rule_defs (rule ↔ definition many-to-many).	benplans/d_rule_def_link
GRP-39	A rule may carry contingency definitions (rule_contingency_defs) plus a with/without indicator and look-back period: contingency_ind = 'Y' means the rule applies only when a claim matching a contingency definition exists within the period; 'N' means it applies only when none exists. If a rule has no contingency definitions, the indicator and period are cleared and disabled.	benplans/w_create_benefit_rule (wf_set_contingency, contingency radio buttons), adjudication in clmsproc/n_claim_adjudication
GRP-40	Saving a rule updates the rule, its definition links, contingency links, tooth-type links, and package attachments in a single transaction; any failure rolls back all of it.	benplans/w_create_benefit_rule pfc_save
GRP-41	A benefit definition (benefit_defs) classifies services by up to seven criteria — procedure (up to three code-type-specific criteria), diagnosis (up to three), place of service, specialty, modifier, revenue code — plus an inpatient/outpatient indicator, and may instead be a compound definition composed of child definitions (compound_ben_defs).	benplans/w_benefit_def_maint, benplans/d_compound_ben_def, benefit_defs columns
GRP-42	Definition criteria are stored as SQL-style predicate expressions (IN pick lists, BETWEEN ranges, wildcards with * translated to %, AND/OR combinations); a criterion that is a single IN list is rendered back to the user as a pick list.	benplans/w_benefit_def_maint (wf_prepare_where_clause, wf_ispicklist, wf_decode)
GRP-43	Procedure and diagnosis criteria may reference named reusable code groups (procedure_groups / procedure_code_groups, diagnosis_groups / diagnosis_code_groups) maintained in plan support.	benplans/d_benefit_def_procs, bensupport/w_create_proc_group, bensupport/w_create_diag_group
GRP-44	Package-level rule interactions are stored per package: dependencies (apply a rule only in proportion to another rule's accumulator — apply_pct), requirements (rule applies only when an accumulator value is within a min/max band), and fulfillments (satisfying one rule fulfills another).	benplans/d_pkg_rule_dep, d_pkg_rule_req, d_pkg_rule_ful
GRP-45	Packages may attach riders (pkg_riders) and rider-conditional rule actions (rider_rules with rider_action_flg), so a member's rider election alters which rules apply.	benplans/d_pkg_rider_maint, benplans/d_rider_rule
GRP-46	Carryover rules (code, percent, period length/type, and a flag for whether carryover also satisfies the rule) are reusable objects attached to rules via carryover_rule_sys_key.	bensupport/d_carryover_rules_maint, benplans/d_ben_rule_ded
GRP-47	A coverage-level schedule (level_scheds / level_sched_items) computes a rule amount per coverage level as flat_amount + (member-category count × multiplier), optionally rounded by a rounding rule and/or step-rated by a step-rate schedule.	bensupport/d_level_scheds_items (labels form the formula), bensupport/w_level_sched_maint
GRP-48	Step-rate schedules map value bands (from/thru) to rates; rounding rules define direction and significance.	bensupport/d_step_rate_items, bensupport/d_rounding_rule_maint
GRP-49	Second-opinion lists define penalty percent, penalty maximum, and a penalize-all flag, and are attached to packages (benefit_pkgs.second_opinion_sys_key).	bensupport/d_second_opinion, benplans/d_benefit_packages
GRP-50	Converting groups to a new plan code is executed by stored procedure sfpidba.new_group_coverages per group coverage row, committed row by row.	bensupport/w_convert_plan
GRP-51	Stop-loss waivers are recorded per member with waiver and incurred date ranges; the applicable stop-loss period is selected from the group's stop-loss parameters, falling back to the master group when the division has none.	client/d_member_waivers, client/w_member_waivers, client/d_contract_picklist
GRP-52	Sales reporting classifies benefit definitions into report service categories per client segment and report type (rpt_svc_cats / rpt_svc_cat_defs).	sales/w_sales_benefit_category_maintenance, sales/d_sales_ben_cat_maintenance_chosen

Benefit plan configuration model

This is the heart of the domain: what a "plan" is, and how claims adjudication reads it.

Entity chain

The canonical join (used verbatim by the plan-analysis report and mirrored in claims adjudication) is:

groups → group_coverages (leaf groups of the subtree) → plan_pkgs on coverage type + plan code → pkg_rules on benefit_pkg_sys_key → benefit_rules on benefit_rule_sys_key, with benefit_pkgs and benefit_plans joined for descriptions and reason_codes for the accumulator (bensupport/d_plan_rpt_group_rule_analysis).

Plan (benefit_plans)

One row per coverage type + plan code. Holds plan-wide adjudication parameters:

Field group	Columns	Notes
Identity	benefit_coverage_type_cd, benefit_coverage_cd, description, plan_status_cd	Plan code doubles as the group's group_coverages.benefit_coverage_cd; status includes at least ASMEDICAL for drug plans (GRP-25)
Timely filing / appeals	filing_period_length + filing_period_type_cd, appeal_period_length + appeal_period_type_cd	Period codes from code table PERIOD
Package search	pkg_logic_cd	Code table PKGLOGIC; defaulted to NORMAL on new plans (benplans/w_create_benefit_plan)
COB / maxima	cob_method_cd, lifetime_max_amt
Dependent ages	dep_age_student, dep_age_non_student
Pre-existing	pre_ex_sub_prior_*, pre_ex_sub_elapsed_*, pre_ex_dep_prior_*, pre_ex_dep_elapsed_*	Prior and elapsed period length/type for subscriber and dependent
Other	auto_assignment_ind, wdi_pmt_sched_cd

Package attachment (plan_pkgs) — versioning and network selection

plan_pkgs is the effective-dated, ordered link from plan to package. Each row: eff_dt / exp_dt, search_order_seq, hcn_sys_key (health-care network; null = default/no-network package), pricing_plan_sys_key, pricing_logic_cd (code table PRICELOGIC), ur_firm_sys_key, cm_firm_sys_key. Plan versioning is expressed here: changing a plan's design over time means expiring one plan_pkgs row and adding a new one; the plan header itself is not dated. Overlap of same-HCN attachments is rejected (GRP-26) and one live no-network attachment is required (GRP-25).

Package (benefit_pkgs)

A reusable bundle of rules per coverage type: benefit_pkg_cd, description, default_benefit_def_sys_key (definition assigned when no rule's definitions match), unassigned_action_cd (HOLD / EXCLUDE / PAY, with reason unassigned_reason_sys_key), pricing_plan_sys_key, second_opinion_sys_key, extraction_flg, procedure_flg. Package maintenance (benplans/w_benefit_pkg_maint) manages the attached rules (pkg_rules), riders, and the dependency / requirement / fulfillment structures.

Rule (benefit_rules) — the adjudication atom

Rule type (rule_type_cd, code table BENRULE) governs which fields are meaningful (GRP-31..36). Amounts are retained values or coverage-level schedules; accumulators are reason codes with RETAINED vs CONSIDERED accumulation; scope filters include period, age band, considered-amount band, weekdays, accident-only, per-day and per-unit bases. benefit_rules rows are shared masters — attaching to packages happens in pkg_rules (with its own effective dating and priority), so one rule can serve many packages, and the rule editor updates pkg_rules attachments in the same transaction (GRP-40).

Definitions and contingencies at adjudication time

Claims adjudication (clmsproc/n_claim_adjudication, outside this domain but the consumer of this model) resolves, per claim line:

1. The benefit definition of the service, using package definition priorities (stored procedures sfpidba.sp_pkg_defs, results staged in temp_benefit_defs).
2. The candidate rules of the package (pkg_rules by priority), expanding each rule's definitions via sfpidba.sp_rule_defs (and d_adjd_* DataWindows over rule_defs).
3. Contingencies: for each rule with contingency_ind set, the contingency definitions (sfpidba.sp_rule_contingency_defs / rule_contingency_defs) are checked against the member's claim history within contingency_period_length + contingency_period_type_cd; the rule is skipped unless the with/without condition matches (GRP-39).

Tables read by the claims side (schema CRUD matrix): clmsproc reads benefit_plans, benefit_rules, benefit_defs, benefit_pkgs, pkg_rules, plan_pkgs, rule_defs, rule_contingency_defs, carryover_rules, level_scheds, group_coverages, group_periods, and writes accumulator-affecting structures (pkg_rule_requirements, pkg_rule_fulfillments).

Effective dating summary

Layer	Dated?	Mechanism
benefit_plans	No	Undated header; create/update audit columns only
plan_pkgs	Yes	eff_dt / exp_dt per attachment; overlap checks per HCN
benefit_pkgs	No	Undated master
pkg_rules	Yes	eff_dt / exp_dt per rule attachment
benefit_rules	No	Undated master; period fields describe accumulation windows, not row validity
group_coverages	Yes	eff_dt / exp_dt (+ run_out_exp_dt, applied_to_members_dt)
group_option_vals, group_enroll_opts, group_member_cat, grp_mbr_cat_val, grp_covr_elig_rules, group_periods	Yes	eff_dt / exp_dt pattern, null exp = open-ended

Sales / broker tracking (sales library)

The sales library is a client-facing reporting module (no broker/commission tables surfaced in it):

• Report wizard (sales/w_sales_report_wizard + u_sales_wizard_* steps, n_cst_sales_report_wizard / n_cst_sales_clntbased_report_wizard): the user picks a report type from report_info (sales/d_sales_report_select), selects groups (u_sales_wizard_group_select) and parameters, and the result renders in sales/w_sales_report_sheet.
• Client-based financials: paid claims by month (d_sales_clntbased_paid_claim, d_sales_clntbased_claims_by_month), lag / incurred triangles (n_cst_sales_clntbased_lag_triangle, d_sales_clntbased_lag_incurred), IBNR estimates (n_cst_sales_clntbased_ibnr, d_sales_clntbased_ibnr — driven off groups + group_coverages), HCN summaries, and coverage lists that exclude non-claims coverage types such as life / AD&D / LTD (d_sales_clntbased_coverages).
• Claimant registry (d_sales_clmnt_reg): payments per claimant from the fund register joined to claims, members, and vendors, with confidential variants.
• Detailed claims listing (sales/w_sales_rpt_group_claim_detail): claims by selected group subtree, claim type, and paid-date range, with discount and confidential variants, including per-line rule data (d_sales_rpt_group_claim_rule_data).
• Benefit category maintenance (sales/w_sales_benefit_category_maintenance, title "Benefit Category Maintenance for Segment:"): assigns benefit definitions to report service categories (rpt_svc_cats / rpt_svc_cat_defs) per client segment and report type; segments come from client_segments and groups map to segments via client_segment_groups (edited on the group screen, GRP-22).

Data + CRUD

Primary tables owned (CRUD) by this domain, with main outside readers:

Table	Purpose	CRUD here	Notable readers elsewhere
groups	Group master + hierarchy	client	~30 libraries; billing, enroll, funding, stoploss also write
group_coverages	Coverages offered per group	client, bensupport	enroll, clmsproc, billing (billfunc), cobra, stoploss, funding
group_contacts	Group contact people	client	census / report DWs
group_option_vals / group_options	Typed group option values	client	enroll, funding, idcard, provider, um_maint
group_enroll_opts	Enrollment options	client	enroll
group_member_cat, grp_mbr_cat_val	Member categories + values	client	enroll
grp_covr_elig_rules	Coverage eligibility by category	client	(client only)
group_periods	Plan-year / enrollment periods	client	billfunc, clmsproc (also write), enrollutils
benefit_plans	Plan headers	benplans	clmsproc, billing, enrollutils, um_*
plan_pkgs	Plan → package attachments	benplans	17 libraries incl. clmsproc, enroll, funding
benefit_pkgs	Package masters	benplans	claims, clmsproc
pkg_rules	Package → rule attachments	benplans	clmsproc, clmentry, clmutils
benefit_rules	Rule masters	benplans (also clmsproc)	clmentry, chkrundw, stoploss, sales
benefit_defs, benefit_def_procs, benefit_def_diags	Definition criteria	benplans	clmsproc, clmentry, pricing, um_main, sales
rule_defs, rule_contingency_defs	Rule ↔ definition links	benplans	clmsproc, claims
pkg_rule_dependencies / _requirements / _fulfillments	Rule interactions	benplans	clmsproc (requirements/fulfillments also written there)
rider_rules, pkg_riders	Rider effects on rules	benplans
carryover_rules, level_scheds, level_sched_items, step_rate_scheds, step_rate_items, rounding_rules, second_opinion_lists, reason_codes	Rule support objects	bensupport	clmsproc
procedure_groups, procedure_code_groups, diagnosis_groups, diagnosis_code_groups, tooth-type tables	Reusable code groups	bensupport	clmsproc, um_*
carriers, stoploss_waivers	Stop-loss carriers + member waivers	client	stoploss
client_segments, client_segment_groups, rpt_svc_cats, rpt_svc_cat_defs	Sales segmentation / categories	client, sales
grp_chg_prop_hist	Parent→child change propagation history	client

Migration notes

1. The rule engine is data, not code. Deductibles, coinsurance, copays, OOP maxima, visit limits, exclusions, and holds are all rows in benefit_rules typed by BENRULE codes, attached to packages with priorities and member-scope flags. A target system must reproduce: retained-vs-scheduled amounts, the stored complement convention for COI/OOP (GRP-33 — displayed "pay at 80%" is stored retain_val = 20), accumulator identity via reason codes with RETAINED/CONSIDERED accumulation, carryover rules, and the contingency with/without look-back mechanism.
2. Definition criteria are embedded SQL predicates (GRP-42). Migrating benefit_defs requires parsing predicate strings (IN lists, BETWEEN ranges, % wildcards, AND/OR) for procedure/diagnosis/POS/specialty/modifier/revenue columns, plus group references and compound (parent/child) definitions. This is the highest data-conversion risk in the domain.
3. Plan identity is a composite natural key (coverage type + plan code) shared verbatim between group_coverages and benefit_plans / plan_pkgs — including RTRIM-sensitive char(15) padding (joins use RPAD(:grp, 15) / RTRIM). Target models should introduce surrogate keys but must preserve the code pair for interfaces.
4. Effective dating conventions: null exp_dt = open-ended; some queries substitute 12/31/2049 as the open-ended date (client/d_client_census_group_option_vals). Versioning granularity differs by layer (see table above): plan headers and rule masters are undated, package and rule attachments are dated.
5. Hierarchy semantics rely on Oracle CONNECT BY; coverage resolution happens at leaf groups, master-group resolution walks upward. Any re-platform must reimplement subtree expansion for termination cascades, plan analysis, and stop-loss fallback.
6. Stored-procedure logic lives outside the extracted source: term_groups_master, term_groups_division, term_members_master, term_members_division, sfpidba.new_group_coverages, sfpidba.sp_pkg_defs, sfpidba.sp_rule_defs, sfpidba.sp_rule_contingency_defs are called but not defined in the PowerBuilder code. Their bodies must be recovered from the database before migration.
7. "Apply to members" is a deferred-propagation pattern: coverage / category changes are marked (unapplied_chg_flg) and applied to member elections later; the application step lives in enrollment-side code. Preserve the two-phase semantics.
8. Security coupling: group visibility is enforced in the data layer by joining security tables into every pick list; group creation writes security rows with hard-coded user classes (GRP-5) and one hard-coded group-id prefix exception.
9. Legacy cross-references: groups carries external carrier identifiers (ELR/control/suffix/account/plan no) and bensupport/w_wang_map maintains mappings from a predecessor system (wang_rule_map, wang_group_rule_map) — decide whether these survive migration.
10. Sales reporting reads adjudicated claims and fund-register views; migrating it is mostly a BI/report exercise, but rpt_svc_cats category mappings are per client segment and per report type and must be carried over for report continuity.

Open questions

1. Parent→child change propagation appears unfinished. The propagation dialog (client/w_client_grp_chg_prop_hist) recursively walks children and records selection, but at the point marked "apply changes to as_group_id here" no change is actually applied, and history rows in grp_chg_prop_hist are only read, never inserted, in this window. Confirm with users whether propagation works via another path (triggers/procedures) or is dead functionality.
2. BENRULE code values: rule types are driven by the codes table; the source evidences DED, COI, COPAY, OOP, 100PCT, CLAIMED, PAID, EXCL, HOLD — the live code table may contain more. Extract codes where code_key = 'BENRULE' to confirm.
3. pkg_logic_cd semantics (PKGLOGIC codes, default NORMAL): how alternate values change package search in adjudication is implemented claims-side and in stored procedures; not determinable from this domain's source alone.
4. Stored-procedure bodies (see Migration note 6) — required to fully specify group termination and definition-priority resolution.
5. benefit_rules CRUD from clmsproc: the claims library shows create/update access to benefit_rules in the CRUD matrix; confirm whether adjudication actually writes rule rows (e.g., temp copies) or whether this is incidental.
6. Eligibility-rule copy in the copy-group dialog is stubbed out (commented call to wf_copy_eligibility_info); confirm intended behavior.
7. w_caremark_group_setup ("Create Matria Claim Files") builds vendor accumulator extract parameters (deductible / OOP accumulator codes per member tier); its output files and vendor interface contract need business confirmation.
8. Group option catalog: the set of group_options.group_option_cd values and their meanings (per code_key / val_saved_as_cd) lives in data, not source; extract for the migration data dictionary.
9. Renewal process: no explicit renewal transaction exists in source; confirm the operational renewal procedure (new dated rows vs plan conversion utility vs external scripts).