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

PL/SQL source

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;

Related Documentation

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

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