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. 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 with source reference back to this document.

Source Code¶

PL/SQL package specification

-- 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;

PL/SQL package/procedure body

-- 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;

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

PKG_CLAIM_VALIDATION¶

Overview¶

Purpose¶

Procedures / Methods¶

get_validation_context¶

Parameters¶

validate_claim_dates¶

Parameters¶

log_validation_failure¶

Parameters¶

Business Rules¶

Operations¶

Source Code¶

Related Documentation¶

Sources¶