pk73069_rating_procs

Overview

Field	Value
Schema	WCIS_DBA
Type	Package
Database	Oracle (resolved via the caller's IDataPortalInfo; not named in code)
Calling libraries	SAIF.Business.Policy
Calling classes	PolicyInformation

Purpose

Package pk73069_rating_procs calculates the minimum premium and, on the assigned-risk (ARP) branch, the expense constant for a given premium-history set. Its public entry point, get_minimum_expense_constant, resolves policy-period rating context from the premium-history set id and then branches on market type — voluntary (VOL) business is routed to the VOL minimum-premium calculation, while every other market type is routed to the ARP minimum/expense-constant calculation via sp72759_get_arp_min_ec.

Procedures / Methods

get_minimum_expense_constant

Field	Value
Returns	None (procedure; results returned via OUT parameters)
Called by	PolicyInformation.PolicyMinimumPremium — SAIF.Business.Policy/PolicyInformation.vb:188–222

Description: Resolves policy-period rating context for the supplied premium-history set id via the private get_pp_info helper, then branches on market type: voluntary (VOL) business is routed to get_comb_vol_policy_minimum (combination periods) or get_vol_policy_minimum (single policy periods); every other market type is routed to sp72759_get_arp_min_ec, the only path that populates the expense-constant output. Any unhandled exception is converted into an application error via pk73090_error_handler.raise_an_error.

Parameters

Parameter	Direction	Type	Default	Description
in_prem_hist_set_id	IN	prem_hist_prem_adjustment.prem_hist_set_id%TYPE (anchored numeric)	—	Premium-history set identifier. Sole driver of the lookup: resolved into full policy-period context via the private get_pp_info helper, which joins prem_hist_summary to policy_period. Supplied by the .NET caller as inPremHistSetID As Decimal (PolicyInformation.vb:188, bound at line 202).
out_minimum_premium_amount	OUT	NUMBER	—	Calculated minimum premium amount. This is the only output the .NET caller reads (PolicyInformation.vb:217).
out_expense_constant_amount	OUT	NUMBER	—	Calculated expense-constant amount. Only populated on the non-VOL (ARP) branch via sp72759_get_arp_min_ec; left unset on the VOL branch. The .NET caller registers this parameter (line 204) but discards the returned value.

get_pp_info (private helper)

Field	Value
Returns	Populates a pp_info_rec OUT parameter
Called by	First statement of get_minimum_expense_constant (internal call only; not called from .NET)

Description: Populates a pp_info_rec with policy number, period effective/expiration dates, rating rule master id, period end date (NVL(pp.cancel_date, pp.policy_period_exp_date)), market type, premium-history type, plan code, financial system code, anniversary rate date, combo period premium set seq num, and create user name, joining prem_hist_summary to policy_period.

sp72759_get_arp_min_ec (private helper)

Field	Value
Returns	None (procedure; results returned via OUT parameters passed through from get_minimum_expense_constant)
Called by	Non-VOL branch of get_minimum_expense_constant (internal call only; not called from .NET)

Description: Reads premium-history type, policy number, and period effective date for the set, then delegates to get_variable_min_exp_constant with market context 'ARP'. This is the only path that returns an expense constant.

Business Rules

Rules extracted from this procedure's logic:

Rule ID	Rule	Condition	Action
PR-BP-001	Minimum-premium calculation is routed by market type: voluntary business uses the VOL minimum-premium rules; all other market types use the ARP (assigned-risk) minimum/expense-constant calculation.	v_pp_info.market_type_code = 'VOL' (market type resolved from policy_period.market_type_code by get_pp_info)	If true → VOL branch (get_comb_vol_policy_minimum or get_vol_policy_minimum). If false → sp72759_get_arp_min_ec, which is the only path that populates out_expense_constant_amount.
PR-BP-002	A voluntary policy that belongs to a combination (combo) period has its minimum premium calculated at the combination level rather than the individual policy level.	Market type is VOL and v_pp_info.combo_period_prem_set_seq_num IS NOT NULL	Look up prem_hist_type_code and combination_eff_date from combo_period_prem_set joined to combo_period, then call get_comb_vol_policy_minimum. Otherwise call get_vol_policy_minimum for the single policy period.
PR-BP-003	Voluntary policy periods effective before 01-Apr-2010 receive a fixed minimum premium of 500; on or after that date the minimum is variable and derived from rating data.	in_policy_period_eff_date < c_vol_variable_min_eff_date (CONSTANT DATE := '01-apr-2010')	If true → out_minimum := c_vol_fixed_min_amount (CONSTANT NUMBER := 500). If false → delegate to get_variable_min_exp_constant.
PR-BP-004	From 01-Jan-2014 onward the voluntary minimum is calculated identically regardless of premium-history set type; before that date, set type '1' is treated differently from all other set types.	in_policy_period_eff_date < c_vol_assess_min_same_eff_date (CONSTANT DATE := '01-jan-2014')	If true → pass 'N' when in_prem_hist_type_code = '1', otherwise 'Y', to get_variable_min_exp_constant. If false → always pass 'Y' (source comment: "calculate the minimum the same regardless of the premium history set type").
PR-BP-005	Any unhandled failure in minimum/expense-constant calculation is converted into an application error by the central error handler; it is not silently ignored at the database layer.	WHEN OTHERS exception raised anywhere in the procedure body	Call pk73090_error_handler.raise_an_error('err in get_minimum_expense_constant: ' \|\| in_prem_hist_set_id \|\| ',' \|\| SQLERRM). Note: the .NET caller wraps ProcessPortalRequest in an empty Try … Catch (PolicyInformation.vb:210–213) and returns 0, so the database-raised error is suppressed at the application layer.

PR-BP-003 / PR-BP-004 are stated at helper-procedure level because get_minimum_expense_constant reaches them unconditionally on the VOL branch.

These rules are documented in business-rules.md. Newly introduced rules must be added to business-rules.md as part of the same writing task.

Operations

Operations and processes implemented in this procedure:

Operation	Description	Trigger
Resolve minimum premium and expense constant for a premium-history set	Resolves policy-period context from a premium-history set id, then branches on market type to compute the applicable minimum premium and (ARP only) expense constant.	PolicyInformation.PolicyMinimumPremium invoked with a prem_hist_set_id (documented as OP-09).
Resolve policy-period rating context	Populates a pp_info_rec with policy number, period effective/expiration dates, rating rule master id, period end date, market type, premium-history type, plan code, financial system code, anniversary rate date, combo period premium set seq num, and create user name.	Called as the first statement of get_minimum_expense_constant.
Compute ARP minimum premium and expense constant	Reads premium-history type / policy number / period effective date for the set, then delegates to get_variable_min_exp_constant with market context 'ARP'. Only path that returns an expense constant.	Non-VOL branch of get_minimum_expense_constant.

These operations are also listed in operations-processes.md with source reference back to this document.

Source Code

PL/SQL package specification

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);

PL/SQL package body

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;

Effective-date constants governing `PR-BP-003` / `PR-BP-004`:

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;

Related Documentation

• Procedure catalog
• Business rules
• Operations and processes
• SQL catalog

Sources

• Oracle DBA_SOURCE: WCIS_DBA.pk73069_rating_procs
• Legends CLI query executed 2026-08-31: legends oracle query --name pk73069_rating_procs
• Calling code: SAIF.Business.Policy/PolicyInformation.vb:188–222