---
title: "SQL-020: Provider location lookup for EOB generation (ProviderLocation)"
summary: Reads a lookup dictionary of provider locations for the provider IDs referenced by the current EOB batch.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Domain
reference_type: package
---

# SQL-020: Provider location lookup for EOB generation (ProviderLocation)

`EobBuilder.GeneratePaymentEobs` queries `ProviderLocation` for the set of provider IDs referenced by the current EOB batch and builds a lookup dictionary keyed by location ID.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-020 |
| **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** | `EobBuilder` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `GeneratePaymentEobs` |

## 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/Claims/Ebilling/EobBuilder.cs:113`

```csharp
// Domain/SAIF.Domain/Claims/Ebilling/EobBuilder.cs:112-115
var providerIds = eobs.Select(x => x.ProviderLocationId).Distinct().ToArray();
var providerLookup = factory.GetRepository<ProviderLocation>().Query()
    .Where(x => providerIds.Contains(x.Id))
    .ToDictionary(k => k.Id, v => v);
```

## 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
  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 IN (:providerIds)
```

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.
- `providerIds.Contains(x.Id)` → `IN (:providerIds)`; NHibernate expands one bind per element.
- `ToDictionary(...)` is the materialization point — dictionary building is in memory.
- Unlike the ebill lookup (SQL-021), this list is **not** chunked to 900; more than 1000 distinct ids would exceed Oracle's IN-list limit (observation, not SQL).

## Operations and processes
- OP-DOM-12 EobBuilder.GenerateEobs — loads ebills and provider locations to build the EOB data set (see [Operations and processes](../operations-processes.md))

## Business rules
- [DOM-BR-120](../business-rules.md#dom-br-120-eob-generation)
- [DOM-BR-121](../business-rules.md#dom-br-121-eob-generation)
- [DOM-BR-122](../business-rules.md#dom-br-122-eob-generation)
- [DOM-BR-123](../business-rules.md#dom-br-123-eob-generation)
- [DOM-BR-124](../business-rules.md#dom-br-124-eob-generation)
- [DOM-BR-125](../business-rules.md#dom-br-125-eob-generation)
- [DOM-BR-126](../business-rules.md#dom-br-126-eob-regenerate)

> **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/Claims/Ebilling/EobBuilder.cs