-- Actual PL/SQL body retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk73069_rating_procs
PACKAGE BODY pk73069_rating_procs
/*******************************************************************************
This package only contains procedures for Enterprise DB.
|*******************************************************************************/
IS
c_vol_variable_min_eff_date CONSTANT DATE := '01-apr-2010';
c_vol_assess_min_same_eff_date constant date := '01-jan-2014';
c_vol_fixed_min_amount CONSTANT NUMBER := 500;
/********************************************************************
Local procedure to get policy period information using prem_hist_set_id as input parm.
********************************************************************/
PROCEDURE get_pp_info(in_prem_hist_set_id IN NUMBER
, out_pp_info OUT pp_info_rec)
IS
CURSOR get_pp_info
IS
SELECT phs.policy_num
, phs.policy_period_eff_date
, pp.policy_period_exp_date
, pp.rating_rule_master_id
, NVL(pp.cancel_date, pp.policy_period_exp_date) period_end_date
, pp.market_type_code
, phs.prem_hist_type_code
, pp.plan_code
, pp.financial_system_code
, pp.anniversary_rate_date
, phs.combo_period_prem_set_seq_num
, phs.create_user_name
FROM prem_hist_summary phs
, policy_period pp
WHERE pp.policy_num = phs.policy_num
AND pp.policy_period_eff_date = phs.policy_period_eff_date
AND prem_hist_set_id = in_prem_hist_set_id;
BEGIN
OPEN get_pp_info;
FETCH get_pp_info
INTO out_pp_info.policy_num
, out_pp_info.policy_period_eff_date
, out_pp_info.policy_period_exp_date
, out_pp_info.rating_rule_master_id
, out_pp_info.policy_period_end_date
, out_pp_info.market_type_code
, out_pp_info.prem_hist_type_code
, out_pp_info.plan_code
, out_pp_info.financial_system_code
, out_pp_info.anniversary_rate_date
, out_pp_info.combo_period_prem_set_seq_num
, out_pp_info.create_user_name;
CLOSE get_pp_info;
END get_pp_info;
/********************************************************************
Get the variable minimum premium and expense constant.
********************************************************************/
PROCEDURE get_variable_min_exp_constant(in_prem_hist_set_id IN NUMBER
, in_reconciliation_flag IN VARCHAR2
, in_market_type_code IN VARCHAR2
, out_minimum_premium OUT NUMBER
, out_expense_constant OUT NUMBER
, out_default_minimum_flag OUT VARCHAR2)
IS
v_minimum_premium NUMBER := 0;
v_min_manual_class_num VARCHAR2(6);
v_prev_manual_class_num VARCHAR2(6);
v_min_rate_file_seq_num NUMBER;
v_rate_file_seq_num NUMBER;
v_expense_constant NUMBER;
CURSOR min_csr
IS
SELECT mcb.min_premium
, phcc.manual_class_num
, phcc.manual_class_eff_date
, phcc.manual_class_period_premium
, phcc.rate_file_seq_num
FROM prem_hist_class_coverage phcc
, manual_class_base mcb
WHERE phcc.prem_hist_set_id = in_prem_hist_set_id
AND phcc.rate_file_seq_num = mcb.rate_file_seq_num
AND phcc.manual_class_num = mcb.manual_class_num
AND mcb.min_premium IS NOT NULL
ORDER BY phcc.manual_class_num
, phcc.manual_class_eff_date;
CURSOR dflt_min_csr
IS
SELECT min_premium
FROM manual_class_base
WHERE rate_file_seq_num = v_rate_file_seq_num
AND manual_class_num LIKE '8810%';
dflt_min_rec dflt_min_csr%ROWTYPE;
CURSOR exp_csr(in_eff_date DATE)
IS
SELECT expense_constant
FROM assigned_risk_expense_constant
WHERE eff_date <= in_eff_date
AND NVL(exp_date, in_eff_date + 1) > in_eff_date;
exp_rec exp_csr%ROWTYPE;
CURSOR rf_csr
IS
SELECT new_outstanding_code
, nr_rate_file_seq_num
, rate_file_eff_date
FROM rate_file_master
WHERE rate_file_seq_num = v_min_rate_file_seq_num;
rf_rec rf_csr%ROWTYPE;
BEGIN
FOR min_rec IN min_csr
LOOP
IF v_rate_file_seq_num IS NULL
OR v_rate_file_seq_num < min_rec.rate_file_seq_num THEN
v_rate_file_seq_num := min_rec.rate_file_seq_num;
END IF;
IF (in_reconciliation_flag = 'Y'
AND min_rec.manual_class_period_premium <> 0)
OR in_reconciliation_flag = 'N' THEN
IF min_rec.manual_class_num <> NVL(v_prev_manual_class_num, 'x')
AND min_rec.min_premium > v_minimum_premium THEN
v_minimum_premium := min_rec.min_premium;
v_min_manual_class_num := min_rec.manual_class_num;
v_min_rate_file_seq_num := min_rec.rate_file_seq_num;
END IF;
v_prev_manual_class_num := min_rec.manual_class_num;
END IF;
END LOOP;
IF v_min_manual_class_num IS NULL THEN
out_default_minimum_flag := 'Y';
OPEN dflt_min_csr;
FETCH dflt_min_csr INTO dflt_min_rec;
CLOSE dflt_min_csr;
v_minimum_premium := dflt_min_rec.min_premium;
v_min_rate_file_seq_num := v_rate_file_seq_num;
ELSE
out_default_minimum_flag := 'N';
END IF;
IF in_market_type_code = 'ARP' THEN
OPEN rf_csr;
FETCH rf_csr INTO rf_rec;
CLOSE rf_csr;
IF rf_rec.new_outstanding_code = 'O' THEN
v_rate_file_seq_num := rf_rec.nr_rate_file_seq_num;
OPEN rf_csr;
FETCH rf_csr INTO rf_rec;
CLOSE rf_csr;
END IF;
OPEN exp_csr(rf_rec.rate_file_eff_date);
FETCH exp_csr INTO exp_rec;
CLOSE exp_csr;
END IF;
DBMS_OUTPUT.put_line(
'min: '
|| v_min_manual_class_num
|| ' '
|| v_min_rate_file_seq_num
|| ' '
|| rf_rec.rate_file_eff_date
|| ' '
|| v_minimum_premium
|| ' '
|| v_expense_constant);
out_minimum_premium := v_minimum_premium;
out_expense_constant := exp_rec.expense_constant;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(
-20069
, 'Error in get_variable_min_exp_constant ' || SQLERRM || ',' || in_prem_hist_set_id);
END get_variable_min_exp_constant;
/********************************************************************
Get the combined annual minimum premium amount for voluntary combination.
********************************************************************/
PROCEDURE get_comb_vol_policy_minimum(in_combo_prem_set_seq_num IN NUMBER
, in_prem_hist_type_code IN VARCHAR2
, in_combo_eff_date IN DATE
, out_minimum OUT NUMBER)
IS
v_expense_constant NUMBER;
v_default_minimum_flag VARCHAR2(1);
v_max_minimum NUMBER;
v_minimum NUMBER;
v_default_minimum NUMBER;
BEGIN
IF in_combo_eff_date < c_vol_variable_min_eff_date THEN
out_minimum := c_vol_fixed_min_amount;
ELSE
FOR phs_rec IN (SELECT phs.prem_hist_set_id
FROM prem_hist_summary phs
WHERE phs.combo_period_prem_set_seq_num = in_combo_prem_set_seq_num)
LOOP
if in_combo_eff_date < c_vol_assess_min_same_eff_date then
get_variable_min_exp_constant(phs_rec.prem_hist_set_id
, CASE
WHEN in_prem_hist_type_code = '1' THEN 'N'
ELSE 'Y'
END
, 'VOL'
, v_minimum
, v_expense_constant
, v_default_minimum_flag);
else
--calculate the minimum the same regardless of the premium history set type
get_variable_min_exp_constant(phs_rec.prem_hist_set_id
,'Y'
, 'VOL'
, v_minimum
, v_expense_constant
, v_default_minimum_flag);
end if;
IF v_default_minimum_flag = 'Y'
AND v_minimum > NVL(v_default_minimum, 0) THEN
v_default_minimum := v_minimum;
ELSIF v_default_minimum_flag = 'N'
AND v_minimum > NVL(v_max_minimum, 0) THEN
v_max_minimum := v_minimum;
END IF;
END LOOP;
out_minimum := NVL(v_max_minimum, v_default_minimum);
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20006
, 'Other error get_comb_vol_policy_minimum ' || SQLERRM);
END get_comb_vol_policy_minimum;
/********************************************************************
Get the annual minimum premium amount for voluntary policy.
********************************************************************/
PROCEDURE get_vol_policy_minimum(in_prem_hist_set_id IN NUMBER
, in_prem_hist_type_code IN VARCHAR2
, in_policy_period_eff_date IN DATE
, out_minimum OUT NUMBER)
IS
v_expense_constant NUMBER;
v_default_minimum_flag VARCHAR2(1);
BEGIN
IF in_policy_period_eff_date < c_vol_variable_min_eff_date THEN
out_minimum := c_vol_fixed_min_amount;
ELSE
if in_policy_period_eff_date < c_vol_assess_min_same_eff_date then
get_variable_min_exp_constant(in_prem_hist_set_id
, CASE
WHEN in_prem_hist_type_code = '1' THEN 'N'
ELSE 'Y'
END
, 'VOL'
, out_minimum
, v_expense_constant
, v_default_minimum_flag);
else
--calculate the minimum the same regardless of the premium history set type
get_variable_min_exp_constant(in_prem_hist_set_id
,'Y'
, 'VOL'
, out_minimum
, v_expense_constant
, v_default_minimum_flag);
end if;
END IF;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20006
, 'Other error get_vol_policy_minimum ' || SQLERRM);
END get_vol_policy_minimum;
/********************************************************************
Get the annual minimum premium and expense constant.
********************************************************************/
PROCEDURE get_minimum_expense_constant(
in_prem_hist_set_id IN prem_hist_prem_adjustment.prem_hist_set_id%TYPE
, out_minimum_premium_amount OUT NUMBER
, out_expense_constant_amount OUT NUMBER)
IS
v_pp_info pp_info_rec;
v_prem_hist_type_code VARCHAR2(5);
v_combination_eff_date DATE;
BEGIN
get_pp_info(in_prem_hist_set_id, v_pp_info);
IF v_pp_info.market_type_code = 'VOL' THEN
IF v_pp_info.combo_period_prem_set_seq_num IS NOT NULL THEN
SELECT cpps.prem_hist_type_code
, cp.combination_eff_date
INTO v_prem_hist_type_code
, v_combination_eff_date
FROM combo_period cp
, combo_period_prem_set cpps
WHERE cpps.combo_period_prem_set_seq_num = v_pp_info.combo_period_prem_set_seq_num
AND cpps.combo_period_seq_num = cp.combo_period_seq_num;
get_comb_vol_policy_minimum(v_pp_info.combo_period_prem_set_seq_num
, v_prem_hist_type_code
, v_combination_eff_date
, out_minimum_premium_amount);
ELSE
get_vol_policy_minimum(in_prem_hist_set_id
, v_pp_info.prem_hist_type_code
, v_pp_info.policy_period_eff_date
, out_minimum_premium_amount);
END IF;
ELSE
sp72759_get_arp_min_ec(in_prem_hist_set_id
, out_minimum_premium_amount
, out_expense_constant_amount);
END IF;
EXCEPTION
WHEN OTHERS THEN
pk73090_error_handler.raise_an_error(
'err in get_minimum_expense_constant: ' || in_prem_hist_set_id || ',' || SQLERRM);
END get_minimum_expense_constant;
/********************************************************************
| Procedure Title : Get the minimum premium and expense constant for arp policy
| Name : SP72759_get_arp_min_ec
| Called By :
| Prepared By : carade
| Created : 09/09/2003
| Modification History
| ------------------------------------------------------------------
|
| Description :
| Gets the applicable minimum premium and expense constant.
*******************************************************************/
PROCEDURE sp72759_get_arp_min_ec(in_prem_hist_set_id IN NUMBER
, out_minimum_premium OUT NUMBER
, out_expense_constant OUT NUMBER)
IS
v_default_minimum_flag VARCHAR2(1);
CURSOR pp_csr
IS
SELECT phs.prem_hist_type_code
, pp.policy_num
, pp.policy_period_eff_date
FROM prem_hist_summary phs
, policy_period pp
WHERE phs.prem_hist_set_id = in_prem_hist_set_id
AND pp.policy_num = phs.policy_num
AND pp.policy_period_eff_date = phs.policy_period_eff_date;
pp_rec pp_csr%ROWTYPE;
BEGIN
OPEN pp_csr;
FETCH pp_csr INTO pp_rec;
CLOSE pp_csr;
get_variable_min_exp_constant(in_prem_hist_set_id
, CASE
WHEN pp_rec.prem_hist_type_code = '1' THEN 'N'
ELSE 'Y'
END
, 'ARP'
, out_minimum_premium
, out_expense_constant
, v_default_minimum_flag);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(
-20069
, 'Error in SP72631 function: SP72759 '
|| SQLERRM
|| pp_rec.policy_num
|| ' '
|| pp_rec.policy_period_eff_date);
END;
END pk73069_rating_procs;