---
title: "SQL-277: Retrieve ProviderLocation"
summary: Read operation against ProviderLocation via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-277: Retrieve ProviderLocation

This statement retrieves a single `ProviderLocation` by its identifier via NHibernate LINQ, used to resolve the servicing provider location for a bill service line in `BillServiceService.CreateService` and `BillServiceService.UpdateService`, executed from `Services/SAIF.Services/Claims/Bills/BillServiceService.cs (L61, L135)`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-277 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `BillServiceService` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `CreateService`, `UpdateService` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:Id` | Input | long | N/A | `newService.ServicingProviderLocation.Id` (L61) / `updatedServiceLine.ServicingProviderLocation.Id` (L135) — matched against `PROVIDER_LOCATION_SEQ_NUM` |

## 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: `Services/SAIF.Services/Claims/Bills/BillServiceService.cs:60`

```csharp
// Services/SAIF.Services/Claims/Bills/BillServiceService.cs:60-61
newServiceLine.ServicingProviderLocation = newService.ServicingProviderLocation.IsNull()
    ? null : unitOfWork.GetRepository<ProviderLocation>().Query().FirstOrDefault(x => x.Id == newService.ServicingProviderLocation.Id);

// Services/SAIF.Services/Claims/Bills/BillServiceService.cs:134-135
dataServiceLine.ServicingProviderLocation = updatedServiceLine.ServicingProviderLocation.IsNull()
    ? null : unitOfWork.GetRepository<ProviderLocation>().Query().FirstOrDefault(x => x.Id == updatedServiceLine.ServicingProviderLocation.Id);
```

## 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 = :Id
)
WHERE ROWNUM <= 1
```

Translation notes:
- Runs only when the incoming `ServicingProviderLocation` is not null (C# ternary).
- `First`/`FirstOrDefault` → Oracle10 dialect row limit `SELECT * FROM (...) WHERE ROWNUM <= 1` (no `ORDER BY`, so the row returned is not deterministic if more than one matches).
- `HasMany` collections (`Addresses`, `Identities`, `ProviderServices`) are lazy; `References(ProviderType)`/`References(Provider)` contribute FK columns only.

## Operations and processes
- BillServiceService.CreateService — see [Operations and processes](../operations-processes.md)
- BillServiceService.UpdateService — see [Operations and processes](../operations-processes.md)

## Business rules
- [SVC-BR-BIL-120 ProviderServiceCode NotNull, length 1-11 - 'Error - Invalid Primary service code, value is too long.'](../business-rules.md#svc-br-bil-120-providerservicecode-notnull%2C-length-1-11---'error---invalid-primary-service-code%2C-value-is-too-long.')
- [SVC-BR-BIL-121 SecondaryProvServiceCode ≤11 - 'Error - Invalid Secondary service code, value is too long.'](../business-rules.md#svc-br-bil-121-secondaryprovservicecode-%E2%89%A411---'error---invalid-secondary-service-code%2C-value-is-too-long.')
- [SVC-BR-BIL-122 DiagnosisPointerCode1-4 ≤1 char - 'Error - Invalid Diagnosis Pointer Code.'](../business-rules.md#svc-br-bil-122-diagnosispointercode1-4-%E2%89%A41-char---'error---invalid-diagnosis-pointer-code.')
- [SVC-BR-BIL-123 Pointers unique - 'Error - Diagnosis Pointers must be unique.'](../business-rules.md#svc-br-bil-123-pointers-unique---'error---diagnosis-pointers-must-be-unique.')
- [SVC-BR-BIL-124](../business-rules.md#svc-br-bil-124-servicedate-%E2%89%A4-today%2B30%3A-%22error%3A-service-date-must-be-no-more-than-30-days-in-the-future.%22)
- [SVC-BR-BIL-125 EndDate ≥ ServiceDate - 'Error - Service End date must be a valid date.'](../business-rules.md#svc-br-bil-125-enddate-%E2%89%A5-servicedate---'error---service-end-date-must-be-a-valid-date.')
- [SVC-BR-BIL-126 PrescriptionWrittenDate > MinValue - 'Error - Prescription Written date must be a valid date.'](../business-rules.md#svc-br-bil-126-prescriptionwrittendate-%3E-minvalue---'error---prescription-written-date-must-be-a-valid-date.')
- [SVC-BR-BIL-127 Modifiers 1-4 ≤2 - 'Error - Modifiers can only be up to two characters.'](../business-rules.md#svc-br-bil-127-modifiers-1-4-%E2%89%A42---'error---modifiers-can-only-be-up-to-two-characters.')
- [SVC-BR-BIL-128 ServiceLocationCode ≤2 - 'Error - Service location code must be 2 digits long.'](../business-rules.md#svc-br-bil-128-servicelocationcode-%E2%89%A42---'error---service-location-code-must-be-2-digits-long.')
- [SVC-BR-BIL-129 Quantity ≥0 - 'Error - Service quantity can not be negative.'](../business-rules.md#svc-br-bil-129-quantity-%E2%89%A50---'error---service-quantity-can-not-be-negative.')
- [SVC-BR-BIL-130 ToothNumber 0-32 - 'Error - Invalid tooth number.'](../business-rules.md#svc-br-bil-130-toothnumber-0-32---'error---invalid-tooth-number.')
- [SVC-BR-BIL-131 DaysSupply 0-99999 - 'Error - Days supply quantity must be a number.'](../business-rules.md#svc-br-bil-131-dayssupply-0-99999---'error---days-supply-quantity-must-be-a-number.')
- [SVC-BR-BIL-132 DAW length 1 - 'Error - Invalid DAW, can only be 1 character long.'](../business-rules.md#svc-br-bil-132-daw-length-1---'error---invalid-daw%2C-can-only-be-1-character-long.')
- [SVC-BR-BIL-133 PrescribingProviderId ≤10 - 'Error - Invalid Prescriber ID, can only be up to 10 characters.'](../business-rules.md#svc-br-bil-133-prescribingproviderid-%E2%89%A410---'error---invalid-prescriber-id%2C-can-only-be-up-to-10-characters.')

## Sources
- `Services/SAIF.Services/Claims/Bills/BillServiceService.cs (L61, L135)`