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

// 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.

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

Business rules

• DOM-BR-120
• DOM-BR-121
• DOM-BR-122
• DOM-BR-123
• DOM-BR-124
• DOM-BR-125
• DOM-BR-126

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