Skip to content

Example: stored procedure document

A procedures/PKG_NAME.md page documents one Oracle package or standalone procedure the code invokes, using source retrieved from the database through the Legends CLI. Doc gen writes one per procedure discovered during database analysis, and the frontmatter records exactly how the source was obtained.

What reviewers should check

  • retrieval_status is one of found, not-found-by-cli, or unresolvable-dynamic-name, and the page body uses the matching variant. There is no "retrieval was skipped" state — a page missing source for any other reason is a pipeline defect.
  • retrieval_command holds the exact Legends CLI command line that was run, and retrieval_date is the day it was run. Both are repeated in ## Sources.
  • For the found variant, the PL/SQL in the Source Code section is the real retrieved text, not a paraphrase or a reconstruction from the calling .NET or VB.NET code.
  • The spec and the body each sit in their own collapsed details block with a sql fence, and the body genuinely matches the procedures listed above it.
  • Every routine in the package has its own ### sub-section with a Returns and Called by table, a description, and a parameter table using Oracle p_ parameter names with IN, OUT, or IN OUT directions.
  • Business rules use PROC-RULE-NNN identifiers with a condition and an action, and each one is carried into the library's business-rules.md rather than living only here.
  • Section order for the found variant is Overview, Purpose, Procedures / Methods, Business Rules, Operations, Source Code, Related Documentation, Sources — and ## Sources is last.

Example

The document below is a complete, correct procedures/pkg-claim-validation.md.

View rendered example — the same document as a real page, so you can compare rendering as well as source.

---
title: "PKG_CLAIM_VALIDATION stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "Claims Systems Team"
last_reviewed: "2026-08-14"
source_schema: "CLAIMS"
source_name: "PKG_CLAIM_VALIDATION"
source_type: "PACKAGE"
retrieval_status: "found"
retrieval_command: "legends oracle query --tns CLMP01 --schema CLAIMS --object PKG_CLAIM_VALIDATION --source"
retrieval_date: "2026-08-12"
---

# PKG_CLAIM_VALIDATION

## Overview

| Field | Value |
| --- | --- |
| **Schema** | CLAIMS |
| **Type** | Package |
| **Database** | CLMP01 |
| **Calling libraries** | `SAIF.Framework.Validation` |
| **Calling classes** | `ClaimValidationGateway`, `RuleDefinitionRepository` |

## Purpose

Supplies the claim-side facts that `SAIF.Framework.Validation` needs before it
can evaluate a rule set, and records the outcome of that evaluation back into
the claims database. The package resolves a claim's policy period, servicing
branch, and jurisdiction into a single context row, applies the date and
jurisdiction checks that must run inside the database because they read tables
the library has no direct grant on, and writes one audit row per blocking
failure. It is the only object in `CLAIMS` that `SAIF.Framework.Validation`
is granted execute on.

## Procedures / Methods

### get_validation_context

| Field | Value |
| --- | --- |
| **Returns** | `SYS_REFCURSOR` — one row containing policy period, jurisdiction, and servicing branch for the claim |
| **Called by** | `ClaimValidationGateway.LoadContext` |

**Description:** Resolves the validation context for a single claim. The cursor
returns the policy effective and expiration dates used by the date-range rules,
the jurisdiction code that selects the state-scoped rule subset, and the
servicing branch used for routing failures. When the claim number does not
exist the cursor is opened empty and `p_context_found` is set to `0`, so the
caller can distinguish "unknown claim" from "no rules applied".

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `p_claim_number` | IN | VARCHAR2 | — | Claim number to resolve, unpadded. |
| `p_as_of_date` | IN | DATE | `SYSDATE` | Date used to pick the policy term when a claim spans a renewal. |
| `p_context_found` | OUT | NUMBER | — | `1` when the claim resolved to a policy term, `0` when it did not. |
| `p_context` | OUT | SYS_REFCURSOR | — | Single-row context cursor consumed by `ClaimValidationGateway`. |

### validate_claim_dates

| Field | Value |
| --- | --- |
| **Returns** | `NUMBER` — count of blocking date failures recorded |
| **Called by** | `ClaimValidationGateway.ValidateDates` |

**Description:** Applies the two date checks that require claims tables the
library cannot read directly: the date of injury must fall inside the policy
term, and the reported date must not precede the date of injury. Each violation
is written to `CLAIM_VALIDATION_AUDIT` through `log_validation_failure` and
counted in the return value. The function performs no commit; the caller owns
the transaction.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `p_claim_number` | IN | VARCHAR2 | — | Claim number being validated. |
| `p_injury_date` | IN | DATE | — | Date of injury supplied by the intake application. |
| `p_reported_date` | IN | DATE | — | Date the claim was reported to SAIF. |
| `p_run_id` | IN | NUMBER | — | Rule set execution identifier used to group audit rows. |

### log_validation_failure

| Field | Value |
| --- | --- |
| **Returns** | Procedure — no return value |
| **Called by** | `ClaimValidationGateway.RecordFailure` |

**Description:** Writes one audit row for a single blocking failure, stamping it
with the rule identifier, the run identifier, and the offending field name.
Duplicate rule identifiers within the same run are collapsed so a claim
re-validated inside one run does not produce repeated rows.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `p_claim_number` | IN | VARCHAR2 | — | Claim the failure belongs to. |
| `p_rule_id` | IN | VARCHAR2 | — | Business rule identifier, for example `VAL-021`. |
| `p_field_name` | IN | VARCHAR2 | — | Field the failure is attributed to, used to highlight the intake screen. |
| `p_message` | IN | VARCHAR2 | — | Operator-facing failure text. |
| `p_run_id` | IN | NUMBER | — | Rule set execution identifier. |

## Business Rules

Rules extracted from this procedure's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PROC-RULE-001 | Date of injury must fall within the policy term, recorded as VAL-014 in business-rules.md | `p_injury_date` is earlier than `policy_effective_date` or later than `policy_expiration_date` | Log a blocking failure against `INJURY_DATE` and increment the failure count |
| PROC-RULE-002 | A claim may not be reported before it occurred, recorded as VAL-021 in business-rules.md | `p_reported_date` is earlier than `p_injury_date` | Log a blocking failure against `REPORTED_DATE` and increment the failure count |
| PROC-RULE-003 | A claim must resolve to exactly one policy term, recorded as VAL-032 in business-rules.md | No policy term covers `p_as_of_date` for the claim's policy number | Open an empty context cursor and return `p_context_found` as `0` |
| PROC-RULE-004 | Audit rows are unique per rule within a run, recorded as VAL-037 in business-rules.md | An audit row already exists for the same claim, rule, and run identifier | Skip the insert and leave the existing row unchanged |

> 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 procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| Resolve claim validation context | Looks up the covering policy term, jurisdiction, and servicing branch for a claim and returns them as a single cursor row | `ClaimValidationGateway.LoadContext` is called at the start of every rule set evaluation |
| Validate claim date fields | Applies the injury and reported date checks and records one audit row per violation | `ClaimValidationGateway.ValidateDates` is called after the context resolves successfully |
| Record validation failure | Inserts a de-duplicated audit row describing one blocking failure | Any blocking failure produced by the database-side checks |

> 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 package specification</summary>

```sql
-- Actual PL/SQL spec retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for CLAIMS.PKG_CLAIM_VALIDATION
CREATE OR REPLACE PACKAGE CLAIMS.PKG_CLAIM_VALIDATION AS

  PROCEDURE get_validation_context (
    p_claim_number  IN  VARCHAR2,
    p_as_of_date    IN  DATE DEFAULT SYSDATE,
    p_context_found OUT NUMBER,
    p_context       OUT SYS_REFCURSOR
  );

  FUNCTION validate_claim_dates (
    p_claim_number  IN VARCHAR2,
    p_injury_date   IN DATE,
    p_reported_date IN DATE,
    p_run_id        IN NUMBER
  ) RETURN NUMBER;

  PROCEDURE log_validation_failure (
    p_claim_number IN VARCHAR2,
    p_rule_id      IN VARCHAR2,
    p_field_name   IN VARCHAR2,
    p_message      IN VARCHAR2,
    p_run_id       IN NUMBER
  );

END PKG_CLAIM_VALIDATION;
```

</details>

<details>
<summary>PL/SQL package/procedure body</summary>

```sql
-- Actual PL/SQL body retrieved from Oracle via Legends CLI
-- Source: DBA_SOURCE / ALL_SOURCE for CLAIMS.PKG_CLAIM_VALIDATION
CREATE OR REPLACE PACKAGE BODY CLAIMS.PKG_CLAIM_VALIDATION AS

  PROCEDURE get_validation_context (
    p_claim_number  IN  VARCHAR2,
    p_as_of_date    IN  DATE DEFAULT SYSDATE,
    p_context_found OUT NUMBER,
    p_context       OUT SYS_REFCURSOR
  ) IS
    v_policy_number CLAIMS.CLAIM.POLICY_NUMBER%TYPE;
  BEGIN
    p_context_found := 0;

    SELECT c.POLICY_NUMBER
      INTO v_policy_number
      FROM CLAIMS.CLAIM c
     WHERE c.CLAIM_NUMBER = p_claim_number;

    SELECT 1
      INTO p_context_found
      FROM CLAIMS.POLICY_TERM t
     WHERE t.POLICY_NUMBER = v_policy_number
       AND p_as_of_date BETWEEN t.EFFECTIVE_DATE AND t.EXPIRATION_DATE
       AND ROWNUM = 1;

    OPEN p_context FOR
      SELECT c.CLAIM_NUMBER,
             t.POLICY_NUMBER,
             t.EFFECTIVE_DATE      AS POLICY_EFFECTIVE_DATE,
             t.EXPIRATION_DATE     AS POLICY_EXPIRATION_DATE,
             t.JURISDICTION_CODE,
             c.SERVICING_BRANCH_CODE
        FROM CLAIMS.CLAIM c
        JOIN CLAIMS.POLICY_TERM t
          ON t.POLICY_NUMBER = c.POLICY_NUMBER
       WHERE c.CLAIM_NUMBER = p_claim_number
         AND p_as_of_date BETWEEN t.EFFECTIVE_DATE AND t.EXPIRATION_DATE;

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_context_found := 0;
      OPEN p_context FOR
        SELECT NULL AS CLAIM_NUMBER,
               NULL AS POLICY_NUMBER,
               NULL AS POLICY_EFFECTIVE_DATE,
               NULL AS POLICY_EXPIRATION_DATE,
               NULL AS JURISDICTION_CODE,
               NULL AS SERVICING_BRANCH_CODE
          FROM DUAL
         WHERE 1 = 0;
  END get_validation_context;

  FUNCTION validate_claim_dates (
    p_claim_number  IN VARCHAR2,
    p_injury_date   IN DATE,
    p_reported_date IN DATE,
    p_run_id        IN NUMBER
  ) RETURN NUMBER IS
    v_failures   NUMBER := 0;
    v_effective  DATE;
    v_expiration DATE;
  BEGIN
    BEGIN
      SELECT t.EFFECTIVE_DATE, t.EXPIRATION_DATE
        INTO v_effective, v_expiration
        FROM CLAIMS.CLAIM c
        JOIN CLAIMS.POLICY_TERM t
          ON t.POLICY_NUMBER = c.POLICY_NUMBER
       WHERE c.CLAIM_NUMBER = p_claim_number
         AND p_injury_date BETWEEN t.EFFECTIVE_DATE AND t.EXPIRATION_DATE
         AND ROWNUM = 1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_effective  := NULL;
        v_expiration := NULL;
    END;

    IF v_effective IS NULL
       OR p_injury_date < v_effective
       OR p_injury_date > v_expiration THEN
      log_validation_failure(
        p_claim_number => p_claim_number,
        p_rule_id      => 'VAL-014',
        p_field_name   => 'INJURY_DATE',
        p_message      => 'Date of injury is outside the policy term.',
        p_run_id       => p_run_id);
      v_failures := v_failures + 1;
    END IF;

    IF p_reported_date < p_injury_date THEN
      log_validation_failure(
        p_claim_number => p_claim_number,
        p_rule_id      => 'VAL-021',
        p_field_name   => 'REPORTED_DATE',
        p_message      => 'Reported date precedes the date of injury.',
        p_run_id       => p_run_id);
      v_failures := v_failures + 1;
    END IF;

    RETURN v_failures;
  END validate_claim_dates;

  PROCEDURE log_validation_failure (
    p_claim_number IN VARCHAR2,
    p_rule_id      IN VARCHAR2,
    p_field_name   IN VARCHAR2,
    p_message      IN VARCHAR2,
    p_run_id       IN NUMBER
  ) IS
  BEGIN
    INSERT INTO CLAIMS.CLAIM_VALIDATION_AUDIT (
      AUDIT_ID,
      CLAIM_NUMBER,
      RULE_ID,
      FIELD_NAME,
      MESSAGE_TEXT,
      RUN_ID,
      CREATED_DATE
    )
    SELECT CLAIMS.SEQ_CLAIM_VALIDATION_AUDIT.NEXTVAL,
           p_claim_number,
           p_rule_id,
           p_field_name,
           p_message,
           p_run_id,
           SYSDATE
      FROM DUAL
     WHERE NOT EXISTS (
             SELECT 1
               FROM CLAIMS.CLAIM_VALIDATION_AUDIT a
              WHERE a.CLAIM_NUMBER = p_claim_number
                AND a.RULE_ID = p_rule_id
                AND a.RUN_ID = p_run_id);
  END log_validation_failure;

END PKG_CLAIM_VALIDATION;
```

</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: CLAIMS.PKG_CLAIM_VALIDATION
- Legends CLI query executed 2026-08-12: `legends oracle query --tns CLMP01 --schema CLAIMS --object PKG_CLAIM_VALIDATION --source`
- Calling code: src/SAIF.Framework.Validation/Data/ClaimValidationGateway.vb