---
title: "PK73193_submission.get_rate_file_seq_num stored procedure documentation"
summary: Function analysis for active rate-file resolution.
status: draft
owner: Technical Writer
last_reviewed: 2026-07-31
library: SAIF.Business.Policy.Rating
reference_type: package
source_schema: WCIS_DBA
source_name: PK73193_submission.get_rate_file_seq_num
source_type: PACKAGE FUNCTION
oracle_status: found
---

# PK73193_submission.get_rate_file_seq_num

## Overview

| Field | Value |
| --- | --- |
| **Schema** | WCIS_DBA |
| **Type** | Package function |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business.Policy.Rating` |
| **Calling classes** | `clsManualClassBaseCollection.GetRateFileSeqNum` |

## Purpose

Returns the active non-outstanding rate-file sequence number for an anniversary rating date and risk type, or zero when no filing matches.

## Procedures / Methods

### get_rate_file_seq_num

| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` — the matching `RATE_FILE_MASTER.RATE_FILE_SEQ_NUM`, or `0` when no active non-outstanding filing matches. |
| **Called by** | `clsManualClassBaseCollection.GetRateFileSeqNum` |

**Description:** Looks up `RATE_FILE_MASTER` for a row where `NEW_OUTSTANDING_CODE = 'N'`, the risk type matches, the filing is effective on or before the supplied ARD, and the filing has no expiration date or expires after the ARD. The lookup is wrapped in its own `BEGIN...EXCEPTION` block so that `NO_DATA_FOUND` is trapped locally and converted to a `0` return value rather than propagating as an unhandled exception.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `inARD` | IN | `RATE_FILE_MASTER.rate_file_eff_date%TYPE` (DATE) | — | Anniversary rating date to match against each candidate filing's effective/expiration window. |
| `inRiskTypeCode` | IN | `RATE_FILE_MASTER.RISK_TYPE_CODE%TYPE` (VARCHAR2) | — | Risk-type/rate-tier code used to filter candidate filings. |

## Business Rules

Rules extracted from this function's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| [BR-041](../business-rules.md#br-041-rate-file-lookup-returns-zero-when-no-active-filing-matches-ard-and-risk-type) | Rate-file lookup returns zero when no active filing matches ARD and risk type. | Matching `RATE_FILE_MASTER` row exists where `NEW_OUTSTANDING_CODE = 'N'`, `RISK_TYPE_CODE = inRiskTypeCode`, `rate_file_eff_date <= inARD`, and (`rate_file_exp_date IS NULL` or `rate_file_exp_date > inARD`); `NO_DATA_FOUND` is trapped locally. | Return the matching `RATE_FILE_SEQ_NUM`; otherwise return `0`. |

> These rules are documented in [business-rules.md](../business-rules.md). Newly introduced rules must be added to `business-rules.md` as part of the same writing task.

## Operations

Operations and processes implemented in this function:

| Operation | Description | Trigger |
| --- | --- | --- |
| Return active rate-file sequence number | Looks up the active non-outstanding rate file for an anniversary rating date and risk type. | `clsManualClassBaseCollection.GetRateFileSeqNum` (SQL-021). |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>PL/SQL function</summary>

```sql
FUNCTION get_rate_file_seq_num
(    inARD IN RATE_FILE_MASTER.rate_file_eff_date%TYPE,
    inRiskTypeCode IN RATE_FILE_MASTER.RISK_TYPE_CODE%TYPE
)
 RETURN NUMBER IS

v_rate_file_seq_num             NUMBER := 0;

BEGIN
    BEGIN
        SELECT rate_file_seq_num
        INTO v_rate_file_seq_num
        FROM RATE_FILE_MASTER
        WHERE NEW_OUTSTANDING_CODE = 'N'
        AND RISK_TYPE_CODE = inRiskTypeCode
        AND rate_file_eff_date <= inARD
        AND (rate_file_exp_date IS NULL OR rate_file_exp_date > inARD);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
        v_rate_file_seq_num := 0;
    END;

    RETURN v_rate_file_seq_num;
END get_rate_file_seq_num;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- Oracle DBA_SOURCE: WCIS_DBA.PK73193_submission
- `Business\SAIF.Business.Policy.Rating\clsManualClassBaseCollection.vb`