---
title: "SQL-019: Provider location lookup for legal representative (ProviderLocation)"
summary: Reads the claim's legal representative location by provider-location sequence number.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Domain
reference_type: package
---

# SQL-019: Provider location lookup for legal representative (ProviderLocation)

`Claim.ClaimLegalRepresentative` queries `ProviderLocation` by the worker representative's provider-location sequence number and returns the first match, exposing the claim's legal representative location. This is an ORM operation, not raw SQL — NHibernate generates the actual query at runtime from the `ProviderLocation` ClassMap.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-019 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle* |
| **Schema** | [unqualified]** |
| **Command type** | N/A — ORM (NHibernate generates SQL at runtime from the ClassMaps in `Data/SAIF.Data.NHibernate/Mappings/**`) |
| **Business object** | `Claim` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `ClaimLegalRepresentative` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PROVIDER_LOCATION](../database-tables.md#provider_location) | SELECT | `PROVIDER_LOCATION_SEQ_NUM`, `UPDATE_NUM`, `BUSINESS_NAME`, `PAYEE_NAME`, `PRIMARY_LOCATION_FLAG`, `BUSINESS_FLAG`, `PHONE_AREA_CODE`, `PHONE_NUM`, `PHONE_NUM_EXT`, `TOLL_FREE_PHONE_NUM`, `FAX_NUM`, `LAST_NAME`, `FIRST_NAME`, `MIDDLE_NAME`, `PROVIDER_TYPE_CODE`, `PROVIDER_SEQ_NUM` |

## LINQ statement

Source: `Domain/SAIF.Domain/Model/Claims/Claim.cs:132`

```csharp
// Domain/SAIF.Domain/Model/Claims/Claim.cs:132-133
var repo = Factory.GetRepository<Providers.ProviderLocation>().Query()
 .Where(x => x.Id == workerRep[0].ProviderLocSeqNumber).FirstOrDefault();
```

## Sample SQL

> **Inferred SQL:** This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

```sql
-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
SELECT *
FROM (
  SELECT
    PL.PROVIDER_LOCATION_SEQ_NUM,
    PL.UPDATE_NUM,
    PL.BUSINESS_NAME,
    PL.PAYEE_NAME,
    PL.PRIMARY_LOCATION_FLAG,
    PL.BUSINESS_FLAG,
    PL.PHONE_AREA_CODE,
    PL.PHONE_NUM,
    PL.PHONE_NUM_EXT,
    PL.TOLL_FREE_PHONE_NUM,
    PL.FAX_NUM,
    PL.LAST_NAME,
    PL.FIRST_NAME,
    PL.MIDDLE_NAME,
    PL.PROVIDER_TYPE_CODE,
    PL.PROVIDER_SEQ_NUM
  FROM PROVIDER_LOCATION PL
  WHERE PL.PROVIDER_LOCATION_SEQ_NUM = :ProviderLocSeqNumber
)
WHERE ROWNUM <= 1
```

Translation notes:
- Entity materialization — every column `ProviderLocationMap` loads is selected (id, version, `Map` columns, `References` FKs). `HasMany` `Addresses`/`Identities`/`ProviderServices` load lazily with their own statements and are not selected.
- `x.Id` → `PROVIDER_LOCATION_SEQ_NUM` (`ProviderLocationMap.cs:14`) — the prior page's column `ID` is incorrect.
- `FirstOrDefault()` on Oracle10 → `ROWNUM <= 1` wrapper.
- `PRIMARY_LOCATION_FLAG`, `BUSINESS_FLAG` are `YesNoType` (`'Y'`/`'N'`).
- The Guidewire contacts call (line 120) is HTTP, not SQL.

## Operations and processes
- OP-DOM-18 Claim read model — loads Guidewire-derived data on demand and exposes derived claim properties (see [Operations and processes](../operations-processes.md))

## Business rules
No business rule directly enforced by this statement.

> **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
- Domain/SAIF.Domain/Model/Claims/Claim.cs