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;