---
title: "pk73211_premium_audit.GetAuditLocationSeqNum 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.GetAuditLocationSeqNum"
source_type: "Package function"
oracle_status: "found"
---

# pk73211_premium_audit.GetAuditLocationSeqNum

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | Package function |
| **Database** | Oracle / WCIS |
| **Calling libraries** | `SAIF.Business.Policy.Audit.Premium` |
| **Calling classes** | `clsAudit`, `clsAuditCollection` |

## Purpose

Picks the single best visitable location for an audit — AUD, then PAY, then POL, then primary BUS — rejecting expired locations and post-office boxes

## Procedures / Methods

### GetAuditLocationSeqNum

| Field | Value |
| --- | --- |
| **Returns** | See Oracle signature / caller behavior |
| **Called by** | `AuditLocationSeqNum` (SQL-018); inline in `GenerateRetrieveSQL` (SQL-027) |

**Description:** Picks the single best visitable location for an audit — AUD, then PAY, then POL, then primary BUS — rejecting expired locations and post-office boxes

## Business Rules

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PROC-RULE-018 | Only four location types can serve as an audit address | `l.location_code IN ('AUD','POL','BUS','PAY')` | Other locations are never selected |
| PROC-RULE-019 | A business location qualifies only when it is the primary one | `l.location_code <> 'BUS' OR (l.location_code = 'BUS' AND l.primary_location_flag = 'Y')` | Non-primary business locations are excluded |
| PROC-RULE-020 | Expired locations are never audit locations | `l.loc_exp_date IS NULL` | Row is excluded |
| PROC-RULE-021 | A post-office box can never be an audit address | `UPPER(l.loc_addr1) NOT LIKE` any of `'PO BOX%'`, `'P.O BOX%'`, `'P.O. BOX%'`, `'PO. BOX%'`, `'P O BOX%'` | Row is excluded — an auditor must be able to visit the address |
| PROC-RULE-022 | Audit-address preference order is AUD → PAY → POL → everything else | `ORDER BY CASE l.location_code WHEN 'AUD' THEN '1' WHEN 'PAY' THEN '2' WHEN 'POL' THEN '3' ELSE '4' END` with `ROWNUM = 1` | Exactly one location sequence number is returned |
| PROC-RULE-023 | A policy with no qualifying location yields no audit location rather than an error | `WHEN NO_DATA_FOUND` | `RETURN NULL` |
| PROC-RULE-024 | Any other failure is escalated with full context | `WHEN OTHERS` | `pk73090_error_handler.raise_an_error('GetAuditLocationSeqNum: ' \ |

## Operations

| Operation | Description | Trigger |
| --- | --- | --- |
| Determine the audit address location | Picks the single best visitable location for an audit — AUD, then PAY, then POL, then primary BUS — rejecting expired locations and post-office boxes | `clsAudit.AuditLocationSeqNum` (SQL-018) and inline in `clsAuditCollection.GenerateRetrieveSQL` (SQL-027); OP-005, OP-006 |

## Source Code

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

```sql
   FUNCTION getauditlocationseqnum( in_policynumber IN policy.policy_num%TYPE)
      RETURN NUMBER
   IS
      v_audit_loc_seq_num   NUMBER := NULL;
   BEGIN
      SELECT auditlocationseqnum
        INTO v_audit_loc_seq_num
        FROM (SELECT loc_seq_num AS auditlocationseqnum
                FROM location l
               WHERE     policy_num = in_policynumber
                     AND l.location_code IN ('AUD'
                                           , 'POL'
                                           , 'BUS'
                                           , 'PAY')
                     AND (   l.location_code <> 'BUS'
                          OR (    l.location_code = 'BUS'
                              AND l.primary_location_flag = 'Y'))
                     AND l.loc_exp_date IS NULL
                     AND upper(l.loc_addr1) NOT LIKE 'PO BOX%'
                     AND upper(l.loc_addr1) NOT LIKE 'P.O BOX%'
                     AND upper(l.loc_addr1) NOT LIKE 'P.O. BOX%'
                     AND upper(l.loc_addr1) NOT LIKE 'PO. BOX%'
                     AND upper(l.loc_addr1) NOT LIKE 'P O BOX%'
              ORDER BY CASE l.location_code
                          WHEN 'AUD' THEN '1'
                          WHEN 'PAY' THEN '2'
                          WHEN 'POL' THEN '3'
                          ELSE '4'
                       END)
       WHERE ROWNUM = 1;

      RETURN v_audit_loc_seq_num;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN NULL;
      WHEN OTHERS THEN
         pk73090_error_handler.raise_an_error( 'GetAuditLocationSeqNum: ' || in_policynumber || ',' || SQLERRM);
   END getauditlocationseqnum;

   /********************************************************************
   | Name             : insert_audit_notice_letter
   | Prepared By      : carade
   | Created          : 12/03/2008
   | Description      : Insert endorsement for audit notice letter.
   | Modification History
   | ------------------------------------------------------------------*/
```

</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.GetAuditLocationSeqNum
- Calling code: ``analysis\oracle-source\pk73211_premium_audit.sql`; `clsAudit.vb`