PACKAGE pk72195_renewal
IS
PROCEDURE sp72319_get_premiums (
in_policy_num IN NUMBER,
in_policy_period_eff_date IN DATE,
in_policy_period_exp_date IN DATE,
in_prem_hist_set_id IN NUMBER,
out_neap OUT NUMBER,
out_man_premium OUT NUMBER,
out_annual_man_premium OUT NUMBER);
FUNCTION sp72187_get_standard_prem(
in_policy_num IN VARCHAR2,
in_set_id IN NUMBER)
RETURN NUMBER;
FUNCTION sp72190_calc_weighted_freq(in_policy_num IN NUMBER
, in_renewal_date IN DATE
, in_process_mode IN VARCHAR2)
RETURN NUMBER;
FUNCTION calc_manual_on_specified_tier
(
in_policy_num in number,
in_policy_period_eff_date in date,
in_prem_hist_set_id in number,
in_calculate_tier in varchar2
)
return number;
PRAGMA RESTRICT_REFERENCES (sp72187_get_standard_prem, WNDS);
--PRAGMA RESTRICT_REFERENCES (sp72188_calc_cust_freq, WNDS);
--PRAGMA RESTRICT_REFERENCES (sp72215_get_est_std_prem, WNDS);
END pk72195_renewal;
/
PACKAGE BODY Pk72195_Renewal
IS
/********************************************************
* Procedure Title : Auto pricing and exception processing
* Name : pk72195_renewal
* Called by :
* Prepared By : Brian Cottings/Clay Andrews
* Created : 09/01/983
* Modification History
* --------------------------------------------------------
* Split out procedures for Enterprise DB
*********************************************************/
/********************************************************
* Procedure Title : get premiums
* Name : sp72319_get_premiums
* Called by :
* Prepared By : Carade
* Created : 08/20/2001
* Modification History
* --------------------------------------------------------
*
*********************************************************/
PROCEDURE sp72319_get_premiums (
in_policy_num IN NUMBER,
in_policy_period_eff_date IN DATE,
in_policy_period_exp_date IN DATE,
in_prem_hist_set_id IN NUMBER,
out_neap OUT NUMBER,
out_man_premium OUT NUMBER,
out_annual_man_premium OUT NUMBER)
IS
v_prem_hist_set_id NUMBER;
v_neap NUMBER := 0;
v_man_premium NUMBER := 0;
v_annual_man_premium NUMBER := 0;
BEGIN
IF in_prem_hist_set_id IS NULL THEN
SELECT MAX(prem_hist_set_id)
INTO v_prem_hist_set_id
FROM PREM_HIST_SUMMARY
WHERE policy_num = in_policy_num
AND policy_period_eff_date = in_policy_period_eff_date;
ELSE
v_prem_hist_set_id := in_prem_hist_set_id;
END IF;
v_man_premium := PK73270_PC_LOSS_EXPOSURE.GET_MANUAL_PREMIUM(v_prem_hist_set_id);
SELECT prem_summary_base_amount
INTO v_neap
FROM PREM_HIST_PREM_SUMMARY
WHERE prem_hist_set_id = v_prem_hist_set_id
AND PREM_ADJUSTMENT_TYPE_CODE = 'NEAP';
IF ADD_MONTHS(in_policy_period_eff_date,12) = in_policy_period_exp_date
THEN
v_annual_man_premium := v_man_premium;
ELSE
/* annualize the premium */
v_annual_man_premium :=
ROUND(
v_man_premium *
-- ratio of days in 1 year to the period length
((ADD_MONTHS(in_policy_period_eff_date,12)
- in_policy_period_eff_date) /
(in_policy_period_exp_date
- in_policy_period_eff_date))
,2);
END IF;
out_man_premium := v_man_premium;
out_neap := v_neap;
out_annual_man_premium := v_annual_man_premium;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR (-20001, 'Error in sp72319 ' || SQLERRM);
END sp72319_get_premiums;
/********************************************************
* Procedure Title :
* Name : sp72187_get_standard_prem
* Called by :
* Prepared By : Brian Cottings/Clay Andrews
* Created : 09/01/98
* Modification History
* --------------------------------------------------------
*
*********************************************************/
FUNCTION sp72187_get_standard_prem (
in_policy_num IN VARCHAR2,
in_set_id IN NUMBER
)
RETURN NUMBER
IS
v_standard_prem NUMBER;
BEGIN
SELECT SUM (prem_summary_diff_amount)
INTO v_standard_prem
FROM PREM_HIST_PREM_SUMMARY
WHERE prem_hist_set_id = in_set_id
AND priority_num <=
( SELECT priority_num
FROM PREM_RATING_RULE_PRIORITY
WHERE PREM_ADJUSTMENT_TYPE_CODE = 'MOD'
AND rating_rule_master_id =
PREM_HIST_PREM_SUMMARY.rating_rule_master_id);
RETURN (v_standard_prem);
END sp72187_get_standard_prem;
/********************************************************
* Procedure Title :
* Name : sp72190_calc_weighted_freq
* Called by :
* Prepared By : Brian Cottings/Clay Andrews
* Created : 09/01/98
* Modification History
* --------------------------------------------------------
*
*********************************************************/
FUNCTION sp72190_calc_weighted_freq (
in_policy_num IN NUMBER,
in_renewal_date IN DATE,
in_process_mode IN VARCHAR2
)
RETURN NUMBER
IS
BEGIN
RETURN WCIS_DBA.PK72195_RENEWAL.SP72190_CALC_WEIGHTED_FREQ@POLDB(in_policy_num,in_renewal_date,in_process_mode);
END sp72190_calc_weighted_freq;
FUNCTION calc_manual_on_specified_tier
(
in_policy_num in number,
in_policy_period_eff_date in date,
in_prem_hist_set_id in number,
in_calculate_tier in varchar2
)
return number as
v_current_tier RISK_TYPE_CODE.CODE%type;
v_policy_period_exp_date policy_period.policy_period_exp_date%type;
v_man_premium number;
v_current_tier_factor RISK_TYPE_DESCRIPTION.CODE_DESCR%type;
v_new_tier_factor RISK_TYPE_DESCRIPTION.CODE_DESCR%type;
v_calc_man_premium number;
function get_tier_factor
(in_risk_type_code RISK_TYPE_CODE.CODE%type,
in_pp_eff_date policy_period.policy_period_eff_date%type,
in_pp_exp_date policy_period.policy_period_exp_date%type)
return varchar2 is
v_tier_factor risk_type_description.code_descr%type;
begin
select risk_type_factor
into v_tier_factor
from risk_type_description
where descr_eff_date <= in_pp_eff_date
and (descr_exp_date is null or descr_exp_date > in_pp_eff_date)
and risk_type_code = in_risk_type_code;
return v_tier_factor;
exception
when no_data_found then
return 0;
when others then
RAISE_APPLICATION_ERROR (-20037, 'in calc_manual_on_specified_tier ' || in_policy_num || ' ' || in_prem_hist_set_id || ' ' || SQLERRM);
end;
BEGIN
select risk_type_code, policy_period_exp_date
into v_current_tier, v_policy_period_exp_date
from policy_period
where policy_num = in_policy_num
and policy_period_eff_date = in_policy_period_eff_date;
select SUM(prem_summary_diff_amount)
into v_man_premium
from prem_hist_prem_summary
where prem_hist_set_id = in_prem_hist_set_id
and prem_adjustment_type_code = 'MAN';
if v_current_tier = nvl(in_calculate_tier,'E')
or v_man_premium = 0 then
dbms_output.put_Line('v_man_premium ' || v_man_premium);
return v_man_premium;
else
--current tier factor
v_current_tier_factor := get_tier_factor (v_current_tier, in_policy_period_eff_date, v_policy_period_exp_date);
dbms_output.put_line('v_current_tier_factor ' || v_current_tier_factor);
--new tier factor
v_new_tier_factor := get_tier_factor (in_calculate_tier, in_policy_period_eff_date, v_policy_period_exp_date);
dbms_Output.put_line ('v_new_tier_factor ' || v_new_tier_factor);
--calculate to new manual premium based on passed in tier
if v_current_tier_factor > 0 and v_new_tier_factor > 0 then
v_calc_man_premium := round((v_man_premium / v_current_tier_factor) * v_new_tier_factor,2);
dbms_output.put_line('v_calc_man_premium ' || v_calc_man_premium);
return v_calc_man_premium;
else
return 0;
end if;
end if;
END calc_manual_on_specified_tier;
END Pk72195_Renewal;