--- title: "sp72151_determine_ncci_stat stored procedure documentation" summary: Analysis of Oracle package/procedure including business rules and operations. status: draft owner: Technical Writer last_reviewed: 2026-07-31 library: SAIF.Business.Policy.Premium reference_type: package source_schema: WCIS_DBA source_name: sp72151_determine_ncci_stat source_type: Function oracle_status: found --- # sp72151_determine_ncci_stat ## Overview | Field | Value | | --- | --- | | **Schema** | WCIS_DBA | | **Type** | Function | | **Database** | Oracle | | **Calling libraries** | `SAIF.Business.Policy.Premium` | | **Calling classes** | clsPremiumSummaryAdjustment (GetStatClassNumber) | ## Purpose Resolve the NCCI statistical reporting class code for a premium adjustment, using one of three lookup strategies keyed by the adjustment type code (CRP/MRP/SARAP/MARIT/FELA vs. INC vs. general). ## Procedures / Methods ### sp72151_determine_ncci_stat | Field | Value | | --- | --- | | **Returns** | `VARCHAR2(6)` — the resolved NCCI statistical reporting class code, or `NULL` when no matching row is found by the selected lookup strategy. | | **Called by** | clsPremiumSummaryAdjustment (GetStatClassNumber) | **Description:** Resolve the NCCI statistical reporting class code for a premium adjustment, using one of three lookup strategies keyed by the adjustment type code (CRP/MRP/SARAP/MARIT/FELA vs. INC vs. general). #### Parameters | Parameter | Direction | Type | Default | Description | | --- | --- | --- | --- | --- | | `in_policy_num` | IN | NUMBER | — | Policy number for the premium adjustment (declared but not referenced by any of the three lookup cursors). | | `in_pp_eff_date` | IN | DATE | — | Policy period effective date (declared but not referenced by any of the three lookup cursors). | | `in_rr_master_id` | IN | NUMBER | — | Rating rule master identifier used to filter the general stat-code lookup (`prem_rating_ncci_stat_code`). | | `in_ps_eff_date` | IN | DATE | — | Premium summary effective date; the CRP/INC lookups require this date to fall within the adjustment's effective/expiration range. | | `in_ps_exp_date` | IN | DATE | — | Premium summary expiration date; the CRP/INC lookups require this date to fall within the adjustment's effective/expiration range. | | `in_ph_set_id` | IN | NUMBER | — | Premium history set identifier used to filter the CRP and INC lookups against `prem_hist_prem_adjustment`. | | `in_pa_type_code` | IN | VARCHAR2 | — | Premium adjustment type code that selects which of the three lookup strategies (CRP/MRP/SARAP/MARIT/FELA, INC, or general) is used. | ## Business Rules | Rule ID | Rule | Condition | Action | | --- | --- | --- | --- | | PROC-BR-006 | Adjustment types CRP, MRP, SARAP, MARIT and FELA resolve their NCCI stat code directly from the matching premium-history premium-adjustment row for the given period (no employer-liability-limit join). | in_pa_type_code IN ('CRP','MRP','SARAP','MARIT','FELA') | Open crp_stat_code_cur: SELECT ncci_stat_code FROM prem_hist_prem_adjustment phpa WHERE phpa.prem_hist_set_id = in_ph_set_id AND phpa.prem_adjustment_type_code = in_pa_type_code AND in_ps_eff_date BETWEEN prem_adjustment_eff_date AND prem_adjustment_exp_date AND in_ps_exp_date BETWEEN prem_adjustment_eff_date AND prem_adjustment_exp_date; NULL if not found | | PROC-BR-007 | Adjustment type INC resolves its NCCI stat code by joining the matching premium-adjustment row to employers_liability_limits on the stat code itself. | in_pa_type_code = 'INC' | Open inc_stat_code_cur: join prem_hist_prem_adjustment phpa to employers_liability_limits b on phpa.ncci_stat_code = b.emp_liab_stat_code, filtered by period overlap; NULL if not found | | PROC-BR-008 | All other adjustment types resolve their NCCI stat code from the general rating-rule-master stat code table. | in_pa_type_code NOT IN ('CRP','MRP','SARAP','MARIT','FELA','INC') | Open general_stat_code_cur: SELECT ncci_stat_code FROM prem_rating_ncci_stat_code WHERE rating_rule_master_id = in_rr_master_id AND prem_adjustment_type_code = in_pa_type_code; NULL if not found | ## Operations | Operation | Description | Trigger | | --- | --- | --- | | Determine NCCI statistical class | Resolve the NCCI statistical reporting class code for a premium adjustment, using one of three lookup strategies keyed by the adjustment type code (CRP/MRP/SARAP/MARIT/FELA vs. INC vs. general). | clsPremiumSummaryAdjustment.GetStatClassNumber (OP-009) — statistical reporting | ## Source Code
PL/SQL source ```sql FUNCTION sp72151_determine_ncci_stat( in_policy_num IN NUMBER , in_pp_eff_date IN DATE , in_rr_master_id IN NUMBER , in_ps_eff_date IN DATE , in_ps_exp_date IN DATE , in_ph_set_id IN NUMBER , in_pa_type_code IN VARCHAR2) RETURN VARCHAR2 /* Valrey 06/21/2000 Modified function to get the default stat code from the employers_liability_limits table. */ /* carade rel 6.0 csr 13 Changed to get stat code for SARAP, MINF, MINM and MRP. */ /* carade rel 7.0.2 Added MINFD and MINMD.*/ /* carade rel 7.0.4 Changed MINFD and MINMD. */ /* carade rel 7.0.5 Removed MINM, MINMD, MINF, MINFD. These will fall thru the general stat code logic. */ /* carade GW Added MARIT and FELA to crp transalation */ IS v_stat_code VARCHAR2(6); v_policy_premium NUMBER; v_policy_fed_minimum NUMBER; CURSOR general_stat_code_cur IS SELECT ncci_stat_code FROM prem_rating_ncci_stat_code WHERE rating_rule_master_id = in_rr_master_id AND prem_adjustment_type_code = in_pa_type_code; CURSOR crp_stat_code_cur IS SELECT ncci_stat_code FROM prem_hist_prem_adjustment phpa WHERE phpa.prem_hist_set_id = in_ph_set_id AND phpa.prem_adjustment_type_code = in_pa_type_code AND in_ps_eff_date BETWEEN prem_adjustment_eff_date AND prem_adjustment_exp_date AND in_ps_exp_date BETWEEN prem_adjustment_eff_date AND prem_adjustment_exp_date; CURSOR inc_stat_code_cur IS SELECT b.ncci_stat_code FROM prem_hist_prem_adjustment phpa , employers_liability_limits b WHERE phpa.prem_hist_set_id = in_ph_set_id AND phpa.prem_adjustment_type_code = in_pa_type_code AND in_ps_eff_date BETWEEN prem_adjustment_eff_date AND prem_adjustment_exp_date AND in_ps_exp_date BETWEEN prem_adjustment_eff_date AND prem_adjustment_exp_date AND phpa.ncci_stat_code = emp_liab_stat_code; BEGIN IF in_pa_type_code IN('CRP', 'MRP', 'SARAP', 'MARIT', 'FELA') THEN OPEN crp_stat_code_cur; FETCH crp_stat_code_cur INTO v_stat_code; IF crp_stat_code_cur%NOTFOUND THEN v_stat_code := NULL; END IF; CLOSE crp_stat_code_cur; ELSIF in_pa_type_code = 'INC' THEN OPEN inc_stat_code_cur; FETCH inc_stat_code_cur INTO v_stat_code; IF inc_stat_code_cur%NOTFOUND THEN v_stat_code := NULL; END IF; CLOSE inc_stat_code_cur; ELSE OPEN general_stat_code_cur; FETCH general_stat_code_cur INTO v_stat_code; IF general_stat_code_cur%NOTFOUND THEN v_stat_code := NULL; END IF; CLOSE general_stat_code_cur; END IF; RETURN v_stat_code; END; ```
## Related documentation - [Procedure catalog](index.md) - [Business rules](../business-rules.md) - [Operations and processes](../operations-processes.md) - [SQL catalog](../sql/index.md) ## Sources - Oracle DBA_SOURCE: WCIS_DBA.sp72151_determine_ncci_stat - `Business\SAIF.Business.Policy.Premium\clsPremiumSummaryAdjustment.vb`