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

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

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

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