---
title: "SQL-018: Last completed audit date lookup"
summary: Retrieve the most recent completed final audit date.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-31
library: SAIF.Business.View.Policy
reference_type: package
---

# SQL-018: Last completed audit date lookup

`clsPolicyData` uses this statement to report when a policy was last audited. Only completed final audits qualify, and a fixed selection-category priority breaks ties between audits that end on the same date.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-018 |
| **Operation type** | Read |
| **Execution method** | Inline SQL |
| **Database** | Oracle |
| **Schema** | `[unqualified]`** |
| **Command type** | Text |
| **Business object** | `clsPolicyData` |
| **Criteria property** | N/A (ad hoc method, not `IBusinessCriteria`) |
| **Lifecycle method** | `GetLastAuditDate` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PolicyNumber` | Input | Decimal | not specified | Policy number whose most recent completed final audit is requested; results are ranked by a fixed selection-category priority. |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PA_AUDIT](../database-tables.md#pa-audit) | SELECT | POLICY_NUM, PA_TYPE_CODE, PA_STATUS_CODE, COMPLETED_DATE, END_DATE, PA_SLCTN_CATEGORY_GROUP_CODE, PA_AUDIT_SEQ_NUM |

## Sample SQL

```sql
select completed_date from (
select  completed_date
from    pa_audit
where   policy_num = :PolicyNumber and pa_type_code = 'FINL' and pa_status_code = 'COMPL'
order by end_date desc,
         case pa_slctn_category_group_code
            when 'COMP' then 1
            when 'BUSR' then 2
            when 'GAC' then 10
            when 'CYCL' then 20
            when 'OTHER' then 30
            else 50 end,
         pa_audit_seq_num desc)
where rownum = 1
```

## Operations and processes

- [Lazy-load policy detail](../operations/lazy-load-policy-detail.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

- `Business/SAIF.Business.View.Policy/clsPolicyData.vb`