pk73211_premium_audit.GetExpiringPolicies

Overview

Field	Value
Schema	WCIS_DBA
Type	Package procedure (ref cursor out)
Database	Oracle / WCIS
Calling libraries	SAIF.Business.Policy.Audit.Premium
Calling classes	clsAuditSelectionPolicyPeriod, clsAuditSelectionPolicyPeriodCollection

Purpose

Opens a ref cursor of policy periods expiring on a given date, tagged 'EXP', excluding cancelled and pending periods

Procedures / Methods

GetExpiringPolicies

Field	Value
Returns	See Oracle signature / caller behavior
Called by	RetrieveSQL (SQL-060, SQL-061)

Description: Opens a ref cursor of policy periods expiring on a given date, tagged 'EXP', excluding cancelled and pending periods

Business Rules

Rule ID	Rule	Condition	Action
PROC-RULE-001	An unusable expiration date suppresses the entire selection run rather than returning an empty cursor	in_expirationdate <= '01-JAN-1900'	The OPEN … FOR is skipped; the out parameter is left unopened
PROC-RULE-002	Only policy periods expiring on exactly the requested date are candidates	pp.policy_period_exp_date = in_expirationdate	Row is included
PROC-RULE-003	A cancelled policy period is never an expiring-audit candidate	pp.cancel_date IS NULL	Row is excluded when a cancel date exists
PROC-RULE-004	Pending and pending-renewal periods are not auditable	pp.policy_period_status_code NOT IN ('P','PR')	Row is excluded
PROC-RULE-005	Every expiring candidate is tagged 'EXP'	Always	'EXP' AS selectiontype is projected, driving the caller's audit reason

Operations

Operation	Description	Trigger
Retrieve expiring policy periods for audit selection	Opens a ref cursor of policy periods expiring on a given date, tagged 'EXP', excluding cancelled and pending periods	clsAuditSelectionPolicyPeriodCollection.RetrieveSQL with AuditSelectionType.ExpiringPolicies (SQL-060/061); OP-001

Source Code

PL/SQL package or method excerpt

   PROCEDURE getexpiringpolicies(in_expirationdate   IN     DATE
                               , out_recordset          OUT ref_cursor)
   IS
   BEGIN
      IF in_expirationdate > '01-JAN-1900' THEN
         OPEN out_recordset FOR
            SELECT pp.policy_num AS policynum
                 , pp.policy_period_eff_date AS policyperiodeffdate
                 , pp.policy_period_exp_date AS policyperiodexpdate
                 , pp.market_type_code AS markettypecode
                 , 'EXP' AS selectiontype
              FROM policy_period pp
             WHERE     pp.policy_period_exp_date = in_expirationdate
                   AND pp.cancel_date IS NULL
                   AND pp.policy_period_status_code NOT IN ('P'
                                                          , 'PR')
            ORDER BY 1
                   , 2;
      END IF;
   END getexpiringpolicies;

   /************************************************************************
   | Run sql to extract off cycle policies in batch mode on a daily basis
   | Called from PremiumAuditProject clsAuditSelectionPolicyPeriodCollection
   | ------------------------------------------------------------------*/

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: pk73211_premium_audit.GetExpiringPolicies
• Calling code: `analysis\oracle-source\pk73211_premium_audit.sql; Business\SAIF.Business.Policy.Audit.Premium\clsAuditSelectionPolicyPeriodCollection.vb