Skip to content

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 
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;

Sources

  • Oracle DBA_SOURCE: WCIS_DBA.sp72151_determine_ncci_stat
  • Business\SAIF.Business.Policy.Premium\clsPremiumSummaryAdjustment.vb