---
title: "pk73069_rating_procs stored procedure documentation"
summary: "Analysis of Oracle package including business rules and operations."
status: draft
owner: "Technical Writer"
last_reviewed: "2026-07-31"
source_schema: "WCIS_DBA"
source_name: "pk73069_rating_procs"
source_type: "PACKAGE"
oracle_status: "found"
library: SAIF.Business.Policy
reference_type: package
---

# pk73069_rating_procs

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.Policy` |
| **Calling classes** | `PolicyInformation.PolicyMinimumPremium` |

## Purpose

Computes minimum premium and expense constant values for a premium-history set by branching into voluntary, combination-voluntary, or assigned-risk logic.

## Procedures / Methods

### get_minimum_expense_constant

| Field | Value |
| --- | --- |
| **Returns** | Procedure/function result consumed by the caller |
| **Called by** | `PolicyInformation.PolicyMinimumPremium` |

**Description:** Computes minimum premium and expense constant values for a premium-history set by branching into voluntary, combination-voluntary, or assigned-risk logic.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| See [SQL catalog](../sql/index.md) | Mixed | Oracle package parameters | — | The application-facing call signature is documented on the corresponding SQL page. |

## Business Rules

| Rule ID | Rule | Source | Link |
| --- | --- | --- | --- |
| [Business rules](../business-rules.md) | Procedure-derived rules are cataloged on the library business-rules page. | `pk73069_rating_procs` | [Details](../business-rules.md) |

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| Procedure operation | Computes minimum premium and expense constant values for a premium-history set by branching into voluntary, combination-voluntary, or assigned-risk logic. | Called from `PolicyInformation.PolicyMinimumPremium` |

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
-- Actual PL/SQL spec retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for WCIS_DBA.pk73069_rating_procs
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;
```

</details>

<details>
<summary>PL/SQL package/procedure body</summary>

```sql
-- 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;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: WCIS_DBA.pk73069_rating_procs
- `Business\SAIF.Business.Policy\PolicyInformation.vb`