---
title: "pk73211_premium_audit.GetExpiringPolicies 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.Audit.Premium
reference_type: package
source_schema: "`WCIS_DBA`"
source_name: "pk73211_premium_audit.GetExpiringPolicies"
source_type: "Package procedure (ref cursor out)"
oracle_status: "found"
---

# 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

<details>
<summary>PL/SQL package or method excerpt</summary>

```sql
   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
   | ------------------------------------------------------------------*/
```

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