---
title: "SP72232_Calc_Policy_Exp_Date stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "SAIF"
last_reviewed: "2026-07-24"
source_schema: "`WCIS_DBA`"
source_name: "SP72232_Calc_Policy_Exp_Date"
source_type: "`Standalone function`"
oracle_status: "found"
library: SAIF.Business
reference_type: package
---

# SP72232_Calc_Policy_Exp_Date

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | `Standalone function` |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business` |
| **Calling classes** | `PolicyServices` |

## Purpose

Policy-expiration function that calculates a default expiration month from the function code, effective date, and financial-system input.

## Procedures / Methods

### SP72232_Calc_Policy_Exp_Date

| Field | Value |
| --- | --- |
| **Returns** | A `DATE` containing the calculated policy expiration month boundary. |
| **Called by** | `PolicyServices.GetDefaultExpirationDate` |

**Description:** Calculates policy expiration dates for MAX, DEFAULT, and AR-quarter-snapped scenarios.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_function_code` | IN | `VARCHAR2` | — | Function code controlling MAX versus DEFAULT expiration logic. |
| `in_pol_per_eff_date` | IN | `DATE` | — | Policy effective date used in the expiration calculation. |
| `in_fs` | IN | `VARCHAR2` | — | Financial-system code used for AR quarter snapping. |

## Business Rules

Rules extracted from this procedure's verified logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [PROC-RULE-029](../business-rules.md#proc-rule-029-max-mode-returns-the-first-day-of-the-month-15-months-after-the-effective-date) | MAX mode returns the first day of the month 15 months after the effective date. | `UPPER(in_function_code) = 'MAX'`. | Set `v_out_exp_date := TRUNC(ADD_MONTHS(in_pol_per_eff_date, 15), 'month')`. |
| [PROC-RULE-030](../business-rules.md#proc-rule-030-default-mode-uses-a-13-month-horizon-for-second-half-effective-dates-and-12-months-otherwise) | Default mode uses a 13-month horizon for second-half effective dates and 12 months otherwise. | `UPPER(in_function_code) <> 'MAX'` and `v_eff_day > 15` versus `v_eff_day <= 15`. | Set `v_out_exp_date` to the first day of the month 13 or 12 months after the effective date. |
| [PROC-RULE-031](../business-rules.md#proc-rule-031-ar-policies-snap-the-calculated-expiration-month-to-quarter-boundaries) | AR policies snap the calculated expiration month to quarter boundaries. | `UPPER(in_fs) = 'AR'` and the derived `MMDD` falls into the defined quarter ranges. | Adjust the output month to `04`, `07`, `10`, or `01`, incrementing the year after October. |
| [PROC-RULE-032](../business-rules.md#proc-rule-032-unhandled-calculation-failures-are-surfaced-as-application-errors) | Unhandled calculation failures are surfaced as application errors. | `WHEN OTHERS` exception. | Raise `RAISE_APPLICATION_ERROR(-20002, 'Error in sp72232: ' || SQLERRM)`. |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| OP-023 | Computes a policy expiration date from the function code, effective date, and financial-system inputs. | `PolicyServices.GetDefaultExpirationDate` invokes the function. |

> These operations are also listed in [operations-processes.md](../operations-processes.md).

## Source Code

<details>
<summary>PL/SQL function signature</summary>

```sql
FUNCTION SP72232_Calc_Policy_Exp_Date
   (in_function_code       VARCHAR2,
    in_pol_per_eff_date    DATE,
    in_fs                  VARCHAR2)
   RETURN DATE
```

</details>

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

```sql
FUNCTION SP72232_Calc_Policy_Exp_Date
   (in_function_code       VARCHAR2,
    in_pol_per_eff_date    DATE,
    in_fs                  VARCHAR2)
   RETURN DATE IS
  v_eff_day          NUMBER;
  v_out_exp_date     DATE;
  v_new_eff_day      NUMBER;
  v_new_eff_month    NUMBER;
  v_month            VARCHAR2(2);
  v_year             NUMBER;
  v_new_mmdd         NUMBER;
BEGIN
  IF UPPER(in_function_code) = 'MAX' THEN
     v_out_exp_date := TRUNC(ADD_MONTHS(in_pol_per_eff_date, 15),'month');
  ELSE
     v_eff_day := TO_NUMBER(TO_CHAR(in_pol_per_eff_date, 'DD'));
     IF v_eff_day > 15 THEN
         v_out_exp_date := TRUNC(ADD_MONTHS(in_pol_per_eff_date, 13),'month');
     ELSE
         v_out_exp_date := TRUNC(ADD_MONTHS(in_pol_per_eff_date, 12),'month');
     END IF;
     IF upper(in_fs) = 'AR' THEN
         v_new_mmdd := TO_NUMBER(TO_CHAR(v_out_exp_date, 'MMDD'));
         v_month := to_char(v_out_exp_date, 'MM');
         v_year := TO_NUMBER(TO_CHAR(v_out_exp_date, 'YYYY'));
         IF v_new_mmdd > 101 AND v_new_mmdd < 402 THEN
             v_month := '04';
         ELSIF v_new_mmdd > 401 AND v_new_mmdd < 702 THEN
             v_month := '07';
         ELSIF v_new_mmdd > 701 AND v_new_mmdd < 1002 THEN
             v_month := '10';
         ELSIF v_new_mmdd > 1001 THEN
             v_month := '01';
             v_year := v_year + 1;
         END IF;
         v_out_exp_date := to_date(v_month || to_char(v_out_exp_date,'/dd/') || v_year,'mm/dd/yyyy');
     END IF;
  END IF;
  RETURN v_out_exp_date;
EXCEPTION
  WHEN others THEN
    RAISE_APPLICATION_ERROR(-20002,'Error in sp72232: ' || SQLERRM);
END;
```

</details>

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

## Sources
- `Business\SAIF.Business\PolicyServices.vb`