---
title: "sp72814_get_cont_cov_date stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Core
reference_type: package
source_schema: "*(unqualified; standalone)*"
source_name: "sp72814_get_cont_cov_date"
source_type: "Standalone function"
oracle_status: "found"
---

# sp72814_get_cont_cov_date

## Overview

| Field | Value |
| --- | --- |
| **Schema** | *(unqualified; standalone)* |
| **Type** | Standalone function |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.Policy.Core` |
| **Calling classes** | `WCISInquiry`, `clsPolicy` |

## Purpose

Finds the most recent break in the policy-period chain and in the explicit coverage-gap table, then picks the appropriate resumption date

## Procedures / Methods

### sp72814_get_cont_cov_date

| Field | Value |
| --- | --- |
| **Returns** | See PL/SQL source below. |
| **Called by** | `WCISInquiry`, `clsPolicy` |

**Description:** Finds the most recent break in the policy-period chain and in the explicit coverage-gap table, then picks the appropriate resumption date

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `in_policy_num` | IN | `NUMBER` | — | Policy number whose continuous-coverage date since the last gap is being returned. |

## Business Rules

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PROC-RULE-028 | A coverage gap exists where a policy period's effective date does not continue the previous period's cancel/expiration date | `NVL(p1.cancel_date, p1.policy_period_exp_date) <> p2.policy_period_eff_date` over `POLICY_PERIOD_ALL`, restricted to statuses with `POLICY_PERIOD_STATUS_CODE.pms_interface_flag = 'Y'` and excluding `'FC'` (flat cancel) and `'PPROS'` (PMS prospect) | The greatest such following effective date becomes the candidate continuous-coverage date |
| PROC-RULE-029 | With no gap in either the period chain or the coverage-gap table, coverage is continuous from the policy's history | `v_max_eff_date IS NULL AND v_max_gap_date IS NULL` **or** `v_orig_date = v_max_eff_date` | Return `NVL(policy.hist_cont_coverage_date, policy.policy_orig_eff_date)` |
| PROC-RULE-030 | The most recent evidence of a gap wins | `v_max_gap_date IS NULL` **or** `v_max_eff_date > v_max_gap_date` | Return the maximum period effective date |
| PROC-RULE-031 | An explicit recorded coverage gap resumes coverage the day after the gap expires | Otherwise (`POLICY_COVERAGE_GAP.gap_exp_date` is the latest evidence) | Return `v_max_gap_date + 1` |
| PROC-RULE-032 | A missing policy row is tolerated | `NO_DATA_FOUND` on the `POLICY` lookup | Leave historical and original dates NULL and continue; only unexpected errors raise `-20002` |

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| Derive the continuous-coverage date | Finds the most recent break in the policy-period chain and in the explicit coverage-gap table, then picks the appropriate resumption date | `WCISInquiry.GetContiniousCoverageDate` (SQL-108) and inline in `clsPolicy.Criteria.RETRIEVE_SQL` (SQL-073) |

## Source Code

<details>
<summary>PL/SQL source</summary>

```sql
FUNCTION sp72814_get_cont_cov_date(
/****************************************************************
*Procedure Title  : Get Continous Coverage Date
*Name             : sp72814_get_cont_cov_date
*Called by        : UW295S
*****************************************************************
* Description:  Return the policy period effective date since the
*               last coverage gap.
*
* Maintenance Log:
* Name    CSR      Date     Description
* -----  -----  ----------  ----------------------------------
* Lanpet   9    02/03/2004  Initial Release
* Valrey  5.16  11/24/2004  Changed were derivation from policy_period
*                           to policy_period_all and policy_coverage_gap tables.
*                           (This procedure will be also used by view policy application.)
* stevan  5.16  12/03/2004  CSR 26 - add historical continuous coverage date
*                           to derivation of continuous coverage date
*
******************************************************************/
   in_policy_num               IN NUMBER)

RETURN DATE IS

  v_return_date		          DATE;

--v_max_cancel_date           DATE    := NULL;
--v_min_eff_date              DATE    := NULL;
  v_orig_date                 DATE    := NULL;
  v_hist_cont_cov_date        DATE    := NULL;
  v_max_eff_date              DATE    := NULL;
  v_max_gap_date              DATE    := NULL;

BEGIN

-- get the historical informtion on the policy'
begin
   SELECT hist_cont_coverage_date, policy_orig_eff_date
   INTO   v_hist_cont_cov_date, v_orig_date
   FROM   policy
   WHERE  policy_num = in_policy_num;

exception
	when no_data_found then
		-- do nothing
		null;
	when others then
		raise_application_error(-20002, sqlerrm);
end;

-- check for gaps first in policy_period_all
-- this select will find the most recent policy period effective date that
-- is the earliest effective date after the most recent effective date
-- that does not correspond to a policy period expiration date
-- all of the policy periods must not be either flat cnacels or PMS
-- prospects.

begin

   SELECT max(p2.policy_period_eff_date)
   INTO   v_max_eff_date
   FROM   policy_period_all p1,
          policy_period_all p2
   WHERE  p1.policy_num = in_policy_num
   AND    p1.policy_num = p2.policy_num
   AND    nvl(p1.cancel_date, p1.policy_period_exp_date) <> p2.policy_period_eff_date
   AND    p1.policy_period_status_code IN (
          SELECT code
          FROM   policy_period_status_code
          WHERE  pms_interface_flag = 'Y'
          AND    code NOT IN ('FC', 'PPROS'))
   AND    p1.policy_period_eff_date <> (
          SELECT max(policy_period_eff_date)
          FROM   policy_period_all
          WHERE  policy_num = p1.policy_num
          AND    policy_period_status_code IN (
                 SELECT code
                 FROM   policy_period_status_code
                 WHERE  pms_interface_flag = 'Y'
                 AND    code NOT IN ('FC', 'PPROS')))
          AND    p2.policy_period_eff_date = (
          SELECT min(policy_period_eff_date)
          FROM   policy_period_all
          WHERE  policy_num = p1.policy_num
          AND    policy_period_eff_date > p1.policy_period_eff_date
          AND    policy_period_status_code IN (
                 SELECT code
                 FROM   policy_period_status_code
                 WHERE  pms_interface_flag = 'Y'
                 AND    code NOT IN ('FC', 'PPROS')));

exception
	when no_data_found then
		-- do nothing
		null;
	when others then
		raise_application_error(-20002, sqlerrm);
end;

begin
-- check for gaps in the coverage gap table

 SELECT max(gap_exp_date)
   INTO v_max_gap_date
   FROM policy_coverage_gap
  WHERE policy_num = in_policy_num;

exception
	when no_data_found then
		-- do nothing
		null;
	when others then
		raise_application_error(-20002, sqlerrm);
end;
 CASE
 -- first case: if both the maximum effective date (from policy_period_all)
 -- and the maximum gap date (from policy_coverage_gap) are null
 -- OR the max effective date is the same as the policy original date
 -- then return the historical continuous coverage date or the policy
 -- original coverage date

    WHEN (v_max_eff_date IS NULL AND v_max_gap_date IS NULL)
      OR v_orig_date = v_max_eff_date THEN

       v_return_date := nvl(v_hist_cont_cov_date, v_orig_date);

 -- second case: if not the above, then if the gap date is null or
 -- the maximum effective date is greater than the gap date
 -- then return the maximum effective date

    WHEN v_max_gap_date IS NULL
      OR v_max_eff_date > v_max_gap_date THEN

       v_return_date := v_max_eff_date;

 -- when all else fails: return the day after the maximum gap date

    WHEN TRUE THEN

       v_return_date := v_max_gap_date + 1;

END CASE;

RETURN v_return_date;

EXCEPTION
     WHEN others THEN
         RAISE_APPLICATION_ERROR(-20001,'Error in sp72814: '
                                  || SQLCODE
                                  || ' '
                                  || SQLERRM);

END sp72814_get_cont_cov_date;
```

</details>

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

> **Note:** Statements marked with * were inferred from code structure, naming conventions, or
> contextual evidence where direct confirmation was not available in source artifacts. Statements
> marked with ** could not be confirmed; treat as unknown until verified.

## Sources
- Oracle DBA_SOURCE: sp72814_get_cont_cov_date
- `Business\SAIF.Business.Policy.Core\clsPolicy.vb`