Skip to content

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 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 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 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 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.

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.

Source Code

PL/SQL function signature 
FUNCTION SP72232_Calc_Policy_Exp_Date
   (in_function_code       VARCHAR2,
    in_pol_per_eff_date    DATE,
    in_fs                  VARCHAR2)
   RETURN DATE
PL/SQL package/procedure body 
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;

Sources

  • Business\SAIF.Business\PolicyServices.vb