pk73069_rating_procs¶
Overview¶
| Field | Value |
| --- | --- |
| Schema | WCIS_DBA |
| Type | Package |
| Database | Oracle |
| Calling libraries | SAIF.Business.Policy |
| Calling classes | PolicyInformation |
Purpose¶
pk73069_rating_procs calculates minimum premium results for a premium-history set. The package first resolves the policy-period context from prem_hist_set_id, then applies voluntary-market fixed or variable minimum rules, handles combination policies, and routes ARP policies through separate ARP minimum and expense-constant logic.
Procedures and methods¶
get_minimum_expense_constant¶
| Field | Value |
| --- | --- |
| Returns | out_minimum_premium_amount OUT NUMBER, out_expense_constant_amount OUT NUMBER |
| Called by | PolicyInformation.PolicyMinimumPremium |
Description: Entry point used by application code. It loads policy-period context with get_pp_info, uses voluntary minimum logic when market_type_code = 'VOL', and otherwise delegates to sp72759_get_arp_min_ec.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_prem_hist_set_id | IN | prem_hist_prem_adjustment.prem_hist_set_id%TYPE | — | Premium-history set ID whose minimum premium is being calculated. |
| out_minimum_premium_amount | OUT | NUMBER | — | Minimum premium amount returned to the caller. |
| out_expense_constant_amount | OUT | NUMBER | — | Expense constant amount returned for ARP processing. |
get_vol_policy_minimum¶
| Field | Value |
| --- | --- |
| Returns | out_minimum OUT NUMBER |
| Called by | Internal package logic only* |
Description: Calculates the annual minimum premium for a single voluntary policy. Policies effective before 01-apr-2010 return the fixed minimum 500; later policies use get_variable_min_exp_constant.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_prem_hist_set_id | IN | NUMBER | — | Premium-history set ID for the voluntary policy. |
| in_prem_hist_type_code | IN | VARCHAR2 | — | Premium-history type code used to determine the reconciliation flag before 01-jan-2014. |
| in_policy_period_eff_date | IN | DATE | — | Policy-period effective date used to choose fixed versus variable minimum logic. |
| out_minimum | OUT | NUMBER | — | Calculated annual minimum premium. |
get_comb_vol_policy_minimum¶
| Field | Value |
| --- | --- |
| Returns | out_minimum OUT NUMBER |
| Called by | Internal package logic only* |
Description: Calculates the annual minimum premium for a combined voluntary policy by iterating all related premium-history sets in the combination and selecting the highest qualifying minimum.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_combo_prem_set_seq_num | IN | NUMBER | — | Combined premium-set sequence number identifying the grouped policy periods. |
| in_prem_hist_type_code | IN | VARCHAR2 | — | Combination premium-history type code used before 01-jan-2014. |
| in_combo_eff_date | IN | DATE | — | Combination effective date used to choose fixed versus variable minimum logic. |
| out_minimum | OUT | NUMBER | — | Highest applicable minimum premium across the combination. |
get_variable_min_exp_constant¶
| Field | Value |
| --- | --- |
| Returns | out_minimum_premium OUT NUMBER, out_expense_constant OUT NUMBER, out_default_minimum_flag OUT VARCHAR2 |
| Called by | Internal package logic only* |
Description: Evaluates manual-class minimum premiums for a premium-history set, chooses either the highest qualifying class minimum or a default minimum, and optionally loads the ARP expense constant from the selected rate file.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_prem_hist_set_id | IN | NUMBER | — | Premium-history set ID being evaluated. |
| in_reconciliation_flag | IN | VARCHAR2 | — | Controls whether zero-premium class rows are ignored. |
| in_market_type_code | IN | VARCHAR2 | — | Market type; ARP triggers expense-constant lookup. |
| out_minimum_premium | OUT | NUMBER | — | Highest qualifying or fallback minimum premium. |
| out_expense_constant | OUT | NUMBER | — | Expense constant loaded for ARP calculations. |
| out_default_minimum_flag | OUT | VARCHAR2 | — | Y when the package had to fall back to the default minimum. |
sp72759_get_arp_min_ec¶
| Field | Value |
| --- | --- |
| Returns | out_minimum_premium OUT NUMBER, out_expense_constant OUT NUMBER |
| Called by | Internal package logic only* |
Description: ARP-specific wrapper that loads the premium-history type and policy-period context, converts the type code into the reconciliation flag, and then reuses get_variable_min_exp_constant for ARP minimum and expense-constant calculation.
Parameters¶
| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| in_prem_hist_set_id | IN | NUMBER | — | Premium-history set ID for the ARP policy. |
| out_minimum_premium | OUT | NUMBER | — | ARP minimum premium result. |
| out_expense_constant | OUT | NUMBER | — | ARP expense constant result. |
Business Rules¶
Fallback to default manual class 8810¶
| Field | Value |
| --- | --- |
| Rule ID | BR-POL-002 |
| Statement | If no qualifying manual-class minimum is selected for the premium-history set, the package falls back to the default minimum premium from manual_class_base where manual_class_num LIKE '8810%'. |
| Trigger | get_variable_min_exp_constant finishes scanning class-coverage rows without setting v_min_manual_class_num. |
| Enforcement | The procedure sets out_default_minimum_flag = 'Y', fetches the 8810% default minimum, and returns it as v_minimum_premium. |
| Outcome | Callers receive a default minimum premium instead of a class-specific minimum. |
Voluntary policies before April 1 2010 use a fixed 500 minimum premium¶
| Field | Value |
| --- | --- |
| Rule ID | BR-POL-003 |
| Statement | Voluntary policies effective before 01-apr-2010 use the fixed minimum premium constant 500. |
| Trigger | get_vol_policy_minimum or get_comb_vol_policy_minimum receives an effective date earlier than c_vol_variable_min_eff_date. |
| Enforcement | The procedure bypasses variable-minimum logic and assigns c_vol_fixed_min_amount. |
| Outcome | Older voluntary policies always return 500 as the annual minimum premium. |
Combined voluntary minimums prefer the highest explicit class minimum¶
| Field | Value |
| --- | --- |
| Rule ID | BR-POL-004 |
| Statement | For combined voluntary policies using variable minimum logic, the package returns the highest non-default minimum across related premium-history sets; if none exists, it returns the highest default minimum. |
| Trigger | get_comb_vol_policy_minimum iterates premium-history sets for a combination on or after 01-apr-2010. |
| Enforcement | Each set is evaluated with get_variable_min_exp_constant; the procedure tracks v_max_minimum for explicit class minimums and v_default_minimum for fallback minimums, then returns NVL(v_max_minimum, v_default_minimum). |
| Outcome | Combination policies favor a real class-specific minimum whenever one exists. |
ARP minimums use ARP-specific expense constant logic¶
| Field | Value |
| --- | --- |
| Rule ID | BR-POL-005 |
| Statement | Non-voluntary policies are processed as ARP minimum-premium requests, and ARP expense constants are resolved from the selected rate file effective date. |
| Trigger | get_minimum_expense_constant receives policy-period context whose market_type_code is not VOL, or get_variable_min_exp_constant is called with in_market_type_code = 'ARP'. |
| Enforcement | The package routes the request to sp72759_get_arp_min_ec, optionally switches to nr_rate_file_seq_num when new_outstanding_code = 'O', and reads assigned_risk_expense_constant for the rate-file effective date. |
| Outcome | ARP policies return minimum-premium and expense-constant values from ARP-specific rate data rather than voluntary-policy logic. |
Operations¶
Determine policy minimum premium and expense constant¶
| Field | Value |
| --- | --- |
| Purpose | Resolve policy-period context and return the minimum premium result for the supplied premium-history set. |
| Trigger | PolicyInformation.PolicyMinimumPremium calls pk73069_rating_procs.get_minimum_expense_constant. |
| Key steps | Load policy-period context with get_pp_info; branch by market_type_code; use either voluntary minimum logic or ARP minimum logic; return output parameters. |
| Outputs | Minimum premium amount, plus an expense constant for ARP processing. |
Determine voluntary combination minimum premium¶
| Field | Value |
| --- | --- |
| Purpose | Calculate one annual minimum premium for a combined voluntary policy period. |
| Trigger | get_minimum_expense_constant sees market_type_code = 'VOL' and a non-null combo_period_prem_set_seq_num. |
| Key steps | Load combination type and effective date; iterate related premium-history sets; evaluate each set with get_variable_min_exp_constant; prefer the highest explicit minimum over fallback defaults. |
| Outputs | One minimum premium amount representing the combined policy. |
Determine ARP minimum premium and expense constant¶
| Field | Value |
| --- | --- |
| Purpose | Calculate minimum premium and expense constant values for ARP policies. |
| Trigger | get_minimum_expense_constant routes non-voluntary policies to sp72759_get_arp_min_ec. |
| Key steps | Read premium-history type and policy-period keys; convert type code into the reconciliation flag; run get_variable_min_exp_constant with market type ARP; load the effective expense constant. |
| Outputs | ARP minimum premium and expense constant values. |
Source Code¶
PL/SQL package specification
PACKAGE pk73069_rating_procs
IS
TYPE pp_info_rec IS RECORD(
policy_num policy_period.policy_num%TYPE
, policy_period_eff_date policy_period.policy_period_eff_date%TYPE
, policy_period_exp_date policy_period.policy_period_exp_date%TYPE
, rating_rule_master_id policy_period.rating_rule_master_id%TYPE
, policy_period_end_date policy_period.policy_period_exp_date%TYPE
, market_type_code policy_period.market_type_code%TYPE
, prem_hist_type_code prem_hist_summary.prem_hist_type_code%TYPE
, plan_code policy_period.plan_code%TYPE
, financial_system_code policy_period.financial_system_code%TYPE
, anniversary_rate_date policy_period.anniversary_rate_date%TYPE
, combo_period_prem_set_seq_num prem_hist_summary.combo_period_prem_set_seq_num%TYPE
, create_user_name prem_hist_summary.create_user_name%TYPE);
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);
PROCEDURE sp72759_get_arp_min_ec(
in_prem_hist_set_id IN NUMBER
, out_minimum_premium OUT NUMBER
, out_expense_constant OUT NUMBER);
END pk73069_rating_procs;
/
PL/SQL package body
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;
Calling Code References¶
| Class | Method | Source File |
| --- | --- | --- |
| PolicyInformation | PolicyMinimumPremium (Get) | SAIF.Business.Policy\PolicyInformation.vb |
Related Documentation¶
Note: Statements marked with
*were inferred from code structure, namingconventions, or contextual evidence where direct confirmation was not available
in source artifacts.
Sources¶
-
Oracle retrieval JSON:
C:\Users\chrfre\AppData\Local\Temp\oracle-sources\pk73069_rating_procs.json -
SAIF.Business.Policy\PolicyInformation.vb