Skip to content

coveragelimits_pkg

Overview

Field Value
Schema WCIS_DBA
Type Package
Database Oracle
Calling libraries SAIF.Business.View.Policy
Calling classes clsPolicyPeriod

Purpose

coveragelimits_pkg returns formatted employer-liability coverage-limit rows for current, specific, future, and reference lookups. The package uses policy-period override tables when they exist and falls back to effective-dated standard-limit tables when they do not.

Procedures / Methods

getSpecificLimitsForPolicyNum

Field Value
Returns limit_cursor (emp_liab_stat_code, emp_liab_limit1, emp_liab_limit2, emp_liab_limit3)
Called by clsPolicyPeriod.RetrieveEmployerLiabLimits
Description Returns employer-liability limits for a specific policy-period effective date, including historical and non-WCIS branches.

Parameters

Parameter Direction Type Default Description
aPolicyNum IN NUMBER — Policy number to inspect.
PeriodEffDate IN DATE — Policy-period effective date to evaluate.
recordset IN OUT limit_cursor — Cursor populated with formatted limit rows.

getCurrentLimitsForPolicyNum

Field Value
Returns limit_cursor
Called by clsPolicyPeriod.RetrieveEmployerLiabLimits
Description Returns current employer-liability limits for the active policy period.

Parameters

Parameter Direction Type Default Description
aPolicyNum IN NUMBER — Policy number to inspect.
recordset IN OUT limit_cursor — Cursor populated with formatted limit rows.

getFutureLimitsForPolicyNum

Field Value
Returns limit_cursor
Called by coveragelimits_pkg utility callers
Description Returns employer-liability limits for future-dated policy periods.

Parameters

Parameter Direction Type Default Description
aPolicyNum IN NUMBER — Policy number to inspect.
recordset IN OUT limit_cursor — Cursor populated with future-period limit rows.

getLimitValues

Field Value
Returns limit_cursor
Called by coveragelimits_pkg utility callers
Description Looks up one limit row by employer-liability statute code.

Parameters

Parameter Direction Type Default Description
aStatCode IN VARCHAR2 — Employer-liability statute code.
recordset IN OUT limit_cursor — Cursor populated with the matching limit row.

getAllLimits

Field Value
Returns limits_cursor
Called by coveragelimits_pkg utility callers
Description Returns all standard employer-liability limit rows effective on the supplied date.

Parameters

Parameter Direction Type Default Description
EffectiveDate IN DATE — Date used to select active factor rows.
recordset IN OUT limits_cursor — Cursor populated with effective-dated standard limit rows.

Business Rules

The retrieved database source embeds the following server-side rules.

Rule ID Summary
BR-VP-015 Specific and current limit lookups return POLICY_PERIOD_EMP_LIAB rows when an in-effect policy-period override exists.
BR-VP-016 When no period-specific limit record qualifies, the package falls back to standard-limit rows from EMPLOYERS_LIABILITY_FACTORS.
BR-VP-017 Future and historical limit lookups evaluate factors against the requested policy-period dates instead of SYSDATE.

BR-VP-015 Specific and current requests prefer policy-period employer-liability rows

Field Value
Statement Specific and current limit requests first read POLICY_PERIOD_EMP_LIAB rows joined to EMPLOYERS_LIABILITY_LIMITS when an in-effect override exists for the requested policy period.
Trigger getCurrentLimitsForPolicyNum or getSpecificLimitsForPolicyNum executes for a policy period with matching POLICY_PERIOD_EMP_LIAB rows.
Enforcement Each procedure opens the cursor with a first SELECT or UNION branch over POLICY_PERIOD_EMP_LIAB and EMPLOYERS_LIABILITY_LIMITS before fallback branches.
Outcome The returned cursor exposes policy-specific employer-liability limits instead of standard defaults.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json

BR-VP-016 Limit retrieval falls back to standard factor rows when no specific override exists

Field Value
Statement If no qualifying policy-specific employer-liability row exists, the package returns standard limit rows where standard_limits_flag = 'Y' and the factor effective dates cover the evaluation date.
Trigger A current, specific, future, or all-limits lookup executes without a matching specific period override.
Enforcement Fallback branches join EMPLOYERS_LIABILITY_LIMITS to EMPLOYERS_LIABILITY_FACTORS and require standard_limits_flag = 'Y' plus NOT EXISTS checks against POLICY_PERIOD_EMP_LIAB.
Outcome Callers still receive formatted limit values even when a policy period has no explicit override row.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json

BR-VP-017 Future and historical lookups use the policy periods own effective context

Field Value
Statement Future and older-period lookups compare effective-dated factor rows against the requested policy period or its expiration date rather than always using SYSDATE.
Trigger getFutureLimitsForPolicyNum or the historical branches inside getSpecificLimitsForPolicyNum execute.
Enforcement getFutureLimitsForPolicyNum compares adjustment windows to pp.policy_period_eff_date, and historical branches in getSpecificLimitsForPolicyNum use PeriodEffDate, policy_period_exp_date, or policy_period_non_wcis dates.
Outcome Returned limits reflect the requested future or historical policy period, not only the current-day configuration.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json

Operations

The retrieved database source implements the following internal operations.

Operation Description Trigger
Resolve current or specific policy-period employer-liability limits Return formatted limit rows for the requested current or explicit policy period, preferring policy-period overrides and then standard defaults. Caller invokes getCurrentLimitsForPolicyNum or getSpecificLimitsForPolicyNum.
Resolve future employer-liability limits Return limit rows for future-dated policy periods by evaluating adjustment windows against the future policy-period effective date. Caller invokes getFutureLimitsForPolicyNum.
Lookup reference employer-liability limit rows Return individual or effective-dated standard limit rows from EMPLOYERS_LIABILITY_LIMITS and EMPLOYERS_LIABILITY_FACTORS. Caller invokes getLimitValues or getAllLimits.

Resolve current or specific policy-period employer-liability limits

Field Value
Description Return formatted limit rows for the requested current or explicit policy period, preferring policy-period overrides and then standard defaults.
Trigger Caller invokes getCurrentLimitsForPolicyNum or getSpecificLimitsForPolicyNum.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json

Resolve future employer-liability limits

Field Value
Description Return limit rows for future-dated policy periods by evaluating adjustment windows against the future policy-period effective date.
Trigger Caller invokes getFutureLimitsForPolicyNum.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json

Lookup reference employer-liability limit rows

Field Value
Description Return individual or effective-dated standard limit rows from EMPLOYERS_LIABILITY_LIMITS and EMPLOYERS_LIABILITY_FACTORS.
Trigger Caller invokes getLimitValues or getAllLimits.
Source Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json

Calling code references

Class Method Source File
clsPolicyPeriod RetrieveEmployerLiabLimits (getSpecificLimitsForPolicyNum) SAIF.Business.View.Policy\clsPolicyPeriod.vb

Source Code

PL/SQL package source
PACKAGE Coveragelimits_Pkg AS
    TYPE limit_rec IS RECORD(
      emp_liab_stat_code  employers_liability_limits.emp_liab_stat_code%TYPE,
      emp_liab_limit1    VARCHAR2(15),
      emp_liab_limit2    VARCHAR2(15),
      emp_liab_limit3    VARCHAR2(15));

    TYPE limit_cursor IS REF CURSOR RETURN limit_rec;

    /* 09/07/2006 JERTOD added a new method to get limits for a specific policy period
    getSpecificLimitsForPolicyNum
    */

    PROCEDURE getSpecificLimitsForPolicyNum(aPolicyNum IN NUMBER,PeriodEffDate IN DATE,
            recordset IN OUT limit_cursor);

    PROCEDURE getCurrentLimitsForPolicyNum(aPolicyNum IN NUMBER,
            recordset IN OUT limit_cursor);

    PROCEDURE getFutureLimitsForPolicyNum(aPolicyNum IN NUMBER,
            recordset IN OUT limit_cursor);

    PROCEDURE getLimitValues(aStatCode IN VARCHAR2,
            recordset IN out limit_cursor);

    TYPE limits_rec IS RECORD(
     emp_liab_stat_code   employers_liability_limits.emp_liab_stat_code%TYPE,
     emp_liab_limit1          VARCHAR2(15),
     emp_liab_limit2          VARCHAR2(15),
     emp_liab_limit3          VARCHAR2(15));

    TYPE limits_cursor IS REF CURSOR RETURN limits_rec;

    PROCEDURE getAllLimits(EffectiveDate IN DATE,
        recordset IN out limits_cursor);

    END;
 / 
PACKAGE BODY          Coveragelimits_Pkg AS

-- get current policy period limits for specific policy
--9/7/2011 valrey rewrote the below select statement to handle mid-year limit changes
--and regional policies between their effective date and the date the P401 has printed.
--It still needs to show the pp that you are resting.

    PROCEDURE getCurrentLimitsForPolicyNum(aPolicyNum IN NUMBER,
            recordset IN OUT limit_cursor)
    AS BEGIN
            OPEN recordset FOR SELECT
                 pp.emp_liab_stat_code,
                 to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                 to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                 to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM policy_period_emp_liab pp, employers_liability_limits e,
                 policy_period p
           WHERE p.policy_num = aPolicyNum
             AND p.policy_num = pp.policy_num
             AND p.policy_period_eff_date <= sysdate
             AND p.policy_period_exp_date > sysdate
             AND pp.emp_liab_stat_code = e.emp_liab_stat_code
             AND p.policy_period_eff_date = pp.policy_period_eff_date
             AND prem_adjustment_eff_date <= SYSDATE
             AND NVL(prem_adjustment_exp_date, SYSDATE + 1) > SYSDATE
      UNION
          SELECT e.emp_liab_stat_code,
                 to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                 to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                 to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM employers_liability_limits e,
                 employers_liability_factors f,
                 policy_period p
           WHERE p.policy_num = aPolicyNum
             AND p.policy_period_eff_date <= sysdate
             AND p.policy_period_exp_date > sysdate
             AND p.anniversary_rate_date >= f.emp_liab_eff_date
             AND p.anniversary_rate_date <
                 nvl(f.emp_liab_exp_date, p.anniversary_rate_date + 1)
             AND f.standard_limits_flag = 'Y'
             AND f.emp_liab_stat_code = e.emp_liab_stat_code
             AND NOT EXISTS (SELECT 'x' FROM policy_period_emp_liab pp
             WHERE p.policy_num = pp.policy_num
               AND p.policy_period_eff_date = pp.policy_period_eff_date
               AND prem_adjustment_eff_date <= SYSDATE
               AND NVL(prem_adjustment_exp_date, SYSDATE + 1) > SYSDATE )
   ;

 END getCurrentLimitsForPolicyNum;

--
   PROCEDURE getSpecificLimitsForPolicyNum(aPolicyNum IN NUMBER,PeriodEffDate IN DATE,
            recordset IN OUT limit_cursor)
    AS BEGIN
    --9/7/2011 valrey replaced the existing select with the below new sql.  This will now work for both
    --get specific and get current.
    OPEN recordset FOR SELECT
                 pp.emp_liab_stat_code,
                 to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                 to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                 to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM policy_period_emp_liab pp, employers_liability_limits e,
                 policy_period p
           WHERE p.policy_num =  aPolicyNum
             and p.policy_period_eff_date = PeriodEffDate
             AND p.policy_num = pp.policy_num
             AND p.policy_period_eff_date <= sysdate
              AND p.policy_period_exp_date > sysdate
             AND pp.emp_liab_stat_code = e.emp_liab_stat_code
             AND p.policy_period_eff_date = pp.policy_period_eff_date
             AND pp.prem_adjustment_eff_date <= SYSDATE
             AND NVL(pp.prem_adjustment_exp_date, SYSDATE + 1) > SYSDATE
          union

          --to get the specific limits for regional policies for policies when the period is done.
           SELECT
                 ppel.emp_liab_stat_code,
                 to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                 to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                 to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM policy_period_emp_liab ppel, employers_liability_limits e,
                 policy_period pp
           WHERE pp.policy_num =  aPolicyNum
             and pp.policy_period_eff_date = PeriodEffDate
             AND pp.policy_num = ppel.policy_num
             AND ppel.emp_liab_stat_code = e.emp_liab_stat_code
             AND pp.policy_period_eff_date = ppel.policy_period_eff_date
             AND ppel.prem_adjustment_eff_date = (select max(prem_adjustment_eff_date) from policy_period_emp_liab ppel2
                                                        where ppel.policy_num = ppel2.policy_num
                                                          and ppel.policy_period_eff_date = ppel2.policy_period_Eff_date)
              and ppel.prem_adjustment_exp_date = pp.policy_period_exp_date
          --To get default limits
          Union
          SELECT e.emp_liab_stat_code,
                 to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                 to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                 to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM employers_liability_limits e,
                 employers_liability_factors f,
                 policy_period p
           WHERE p.policy_num =  aPolicyNum
             and p.policy_period_eff_date = PeriodEffDate
             AND p.policy_period_eff_date <= sysdate
             AND p.policy_period_exp_date > sysdate
             AND f.standard_limits_flag = 'Y'
             AND f.emp_liab_stat_code = e.emp_liab_stat_code
             AND F.EMP_LIAB_EFF_DATE <= SYSDATE AND NVL(F.EMP_LIAB_EXP_DATE, SYSDATE + 1) > SYSDATE
             AND NOT EXISTS
                (SELECT 'x' FROM policy_period_emp_liab pp
                  WHERE p.policy_num = pp.policy_num
                    AND p.policy_period_eff_date = pp.policy_period_eff_date
                    AND prem_adjustment_eff_date <= SYSDATE
                    AND NVL(prem_adjustment_exp_date, SYSDATE + 1) > SYSDATE )



           --To get the default limits for regional policies for their period when the expiration date is past sysdate
           union
          SELECT e.emp_liab_stat_code,
                 to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                 to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                 to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM employers_liability_limits e,
                 employers_liability_factors f,
                 policy_period p
           WHERE p.policy_num =  aPolicyNum
             and p.policy_period_eff_date = PeriodEffDate
             AND f.standard_limits_flag = 'Y'
             AND f.emp_liab_stat_code = e.emp_liab_stat_code
             AND F.EMP_LIAB_EFF_DATE <= p.policy_period_exp_date AND NVL(F.EMP_LIAB_EXP_DATE, p.policy_period_eff_date + 1) > p.policy_period_eff_date
             AND NOT EXISTS
                (SELECT 'x' FROM policy_period_emp_liab pp
                  WHERE p.policy_num = pp.policy_num
                    AND p.policy_period_eff_date = pp.policy_period_eff_date
                    AND pp.prem_adjustment_exp_date = p.policy_period_exp_date )



          --To get specific older limits on older periods get the latest one in effect
          union
          SELECT ppel.emp_liab_stat_code,
                  to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                  to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                  to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM
                policy_period_emp_liab ppel,
                policy_period_non_wcis ppnw,
                employers_liability_limits ell
            WHERE ppnw.policy_num =  aPolicyNum
            AND ppnw.policy_period_Eff_date = PeriodEffDate
            AND ppnw.policy_num = ppel.policy_num
            and ppnw.policy_period_eff_date = ppel.policy_period_eff_date
            AND ppel.emp_liab_stat_code IS NOT NULL
            AND ppel.emp_liab_stat_code = ell.emp_liab_stat_code
            and ppel.prem_adjustment_eff_date = (select max(prem_adjustment_eff_date) from policy_period_emp_liab ppel2
                                                        where ppel.policy_num = ppel2.policy_num
                                                          and ppel.policy_period_eff_date = ppel2.policy_period_Eff_date)
            AND ppel.prem_adjustment_exp_date = ppnw.policy_period_exp_date

        --to get the standard limits on older periods if when no specific standard exists
        union
        SELECT
               e.emp_liab_stat_code,
               to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
               to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
               to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
        FROM
               policy_period_non_wcis ppnw,
               employers_liability_limits e,
               employers_liability_factors f

        WHERE  ppnw.policy_num = aPolicyNum
        and ppnw.policy_period_eff_date = PeriodEffDate
        AND f.standard_limits_flag = 'Y'
        AND f.emp_liab_stat_code = e.emp_liab_stat_code
        AND ppnw.policy_period_eff_date >= f.emp_liab_eff_date
        AND ppnw.policy_period_eff_date < nvl(f.emp_liab_exp_date, ppnw.policy_period_eff_date + 1)
        AND NOT EXISTS
                (SELECT 'x' FROM policy_period_emp_liab pp
                  WHERE ppnw.policy_num = pp.policy_num
                    AND ppnw.policy_period_eff_date = pp.policy_period_eff_date
                    AND pp.prem_adjustment_exp_date = ppnw.policy_period_exp_date);

 END getSpecificLimitsForPolicyNum;


--

-- get future policy period limits for specific policy

    PROCEDURE getFutureLimitsForPolicyNum(aPolicyNum IN NUMBER,
       recordset IN OUT limit_cursor)
    AS BEGIN
            OPEN recordset FOR SELECT
                 pp.emp_liab_stat_code,
                 to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                 to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                 to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM policy_period_emp_liab pp, employers_liability_limits e,
                 policy_period p
           WHERE p.policy_num = aPolicyNum
             AND p.policy_num = pp.policy_num
             AND p.policy_period_eff_date > sysdate
             AND pp.emp_liab_stat_code = e.emp_liab_stat_code
             AND p.policy_period_eff_date = pp.policy_period_eff_date
             AND prem_adjustment_eff_date <= pp.policy_period_eff_date
             AND NVL(prem_adjustment_exp_date, pp.policy_period_eff_date + 1)
                 > pp.policy_period_eff_date
      UNION
          SELECT e.emp_liab_stat_code,
                 to_char(emp_liab_limit1,'999,999,999,999') emp_liab_limit1,
                 to_char(emp_liab_limit2,'999,999,999,999') emp_liab_limit2,
                 to_char(emp_liab_limit3,'999,999,999,999') emp_liab_limit3
            FROM employers_liability_limits e,
                 employers_liability_factors f,
                 policy_period p
           WHERE p.policy_num = aPolicyNum
             AND p.policy_period_eff_date > sysdate
             AND p.anniversary_rate_date >= f.emp_liab_eff_date
             AND p.anniversary_rate_date <
                 nvl(f.emp_liab_exp_date, p.anniversary_rate_date + 1)
             AND f.standard_limits_flag = 'Y'
             AND f.emp_liab_stat_code = e.emp_liab_stat_code
             AND NOT EXISTS (SELECT 'x' FROM policy_period_emp_liab pp
             WHERE p.policy_num = pp.policy_num
               AND p.policy_period_eff_date = pp.policy_period_eff_date
               AND prem_adjustment_eff_date <= pp.policy_period_eff_date
               AND NVL(prem_adjustment_exp_date, pp.policy_period_eff_date + 1)
                               > pp.policy_period_eff_date );

 END getFutureLimitsForPolicyNum;

 procedure getLimitValues(aStatCode IN VARCHAR2,
    recordset IN OUT limit_cursor)
  AS BEGIN
     OPEN recordset FOR SELECT
                emp_liab_stat_code,
                emp_liab_limit1,
                emp_liab_limit2,
                emp_liab_limit3
           FROM employers_liability_limits
          WHERE emp_liab_stat_code = aStatCode;

 END getLimitValues;


Procedure getAllLimits(EffectiveDate IN DATE,
       recordset IN OUT limits_cursor)
    AS BEGIN
    OPEN recordset FOR SELECT
         lim.emp_liab_stat_code,
         to_char(emp_liab_limit1, '999,999,999,999') emp_liab_limit1,
         to_char(emp_liab_limit2, '999,999,999,999') emp_liab_limit2,
         to_char(emp_liab_limit3, '999,999,999,999') emp_liab_limit3
    FROM employers_liability_limits lim,
         employers_liability_factors fctr
    WHERE lim.emp_liab_stat_code = fctr.emp_liab_stat_code
      AND EffectiveDate >= emp_liab_eff_date
      AND EffectiveDate < nvl(emp_liab_exp_date,
                              to_date('31-DEC-4700','DD-MON-YYYY'))
 UNION -- Remove the Below Select after 1/1/2003
    SELECT
         lim.emp_liab_stat_code,
         to_char(emp_liab_limit1, '999,999,999,999') emp_liab_limit1,
         to_char(emp_liab_limit2, '999,999,999,999') emp_liab_limit2,
         to_char(emp_liab_limit3, '999,999,999,999') emp_liab_limit3
    FROM employers_liability_limits lim
    WHERE lim.emp_liab_stat_code = '4'
    ORDER BY 1;
 END GetAllLimits;

END;

Sources

  • Oracle retrieval JSON: C:\Users\chrfre\AppData\Local\Temp\oracle-sources\coveragelimits_pkg.json
  • Calling code: SAIF.Business.View.Policy\clsPolicyPeriod.vb