---
title: Provider relationship retrieval
summary: Retrieves the child or parent provider-location relationships for a given location, resolving each related location inline from the same result set and offering a filtered works-for view.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-30
library: SAIF.Business.View.Provider
reference_type: package
operation_id: OP-VP-04
trigger: Caller reads clsProviderLocation.ChildRelationships or ParentRelationships, or calls clsProviderRelationshipCollection.GetChildProviderRelationshipCollection(dpi, providerLocationID) / GetParentProviderRelationshipCollection(dpi, providerLocationID) directly
---

# Provider relationship retrieval

Provider relationship retrieval reads `PROVIDER_RELATIONSHIP` rows joined to `PROVIDER_LOCATION` twice — once for the location itself and once for its related (parent) location — to answer one of two questions: "which locations report to this location as their parent?" (child relationships) or "which locations does this location report to?" (parent relationships). Both queries return the full related-location column set in the same row, so each `clsProviderRelationship` is populated together with a complete related `clsProviderLocation` from a single result set, without a second round trip.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-VP-04 |
| **Trigger** | `clsProviderRelationshipCollection.GetChildProviderRelationshipCollection(inDataportal, inProviderLocationID)` — invoked directly or via `clsProviderLocation.ChildRelationships`; `clsProviderRelationshipCollection.GetParentProviderRelationshipCollection(inDataportal, inProviderLocationID)` — invoked directly or via `clsProviderLocation.ParentRelationships` |
| **Inputs** | Provider location ID (Decimal) |
| **Outputs** | A populated `clsProviderRelationshipCollection`, each item carrying an embedded related `clsProviderLocation`; a derived `ProviderLocations` collection of every related location; a `WorksFor`/`WorksFor(date)` filtered subset |
| **Primary components** | `clsProviderRelationshipCollection.GetChildProviderRelationshipCollection`, `clsProviderRelationshipCollection.GetParentProviderRelationshipCollection`, `clsProviderRelationshipCollection.Criteria.RetrieveParentRelationshipsSQL`, `clsProviderRelationshipCollection.Criteria.RetrieveChildRelationshipsSQL`, `clsProviderRelationship.Load` |
| **Entry point** | `Business\SAIF.Business.View.Provider\clsProviderRelationshipCollection.vb` |

## Steps

1. **Select the relationship direction.** `GetChildProviderRelationshipCollection` builds a criteria with `RelationshipTypes.Parent` as the source relationship type (meaning: find locations for which the given location is the parent); `GetParentProviderRelationshipCollection` builds a criteria with `RelationshipTypes.Child` (meaning: find the locations the given location reports to as a child).
2. **Bind the location parameter.** `BeforeRetrieve` binds `:ProviderLocationID` and caches it on the collection instance.
3. **Choose the query variant.** The criteria's `RetrieveSQL` returns `RetrieveParentRelationshipsSQL` when the source type is Child, or `RetrieveChildRelationshipsSQL` when the source type is Parent — the naming is inverted from the public factory method names because each SQL variant is named for the *result* it returns, not the caller's perspective ([SQL-013](../sql/SQL-013-saif-business-view-provider.md), [SQL-014](../sql/SQL-014-saif-business-view-provider.md), [PROVIDER_LOCATION](../database-tables.md#provider_location), [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship)).
   - `RetrieveChildRelationshipsSQL` joins `PROVIDER_RELATIONSHIP` from the parent's perspective and filters `WHERE parentpl.provider_location_seq_num = :ProviderLocationID`, returning the child locations, ordered by the child's business name.
   - `RetrieveParentRelationshipsSQL` filters `WHERE pl.provider_location_seq_num = :ProviderLocationID`, returning the parent locations, ordered by the parent's business name.
4. **Populate each relationship and its related location together.** `AfterRetrieve` iterates the result set; for each row, a new `clsProviderRelationship` is constructed and its `Load` fills both the relationship fields (start date, termination date, provider relationship code, parent/child location IDs) and constructs and fills a full `clsProviderLocation` from the same row via the location's own `Fill` logic, tagging the relationship with the source relationship type used for the query.
5. **Build the related-locations projection.** As each relationship is loaded, its destination provider location is also added to an internal `clsProviderLocationCollection`, exposed as the `ProviderLocations` property once retrieval completes.
6. **Resolve the opposite-side location lazily when needed.** `DestinationProviderLocation` returns the location embedded directly in the row for the relationship's own direction; `ChildProviderLocation`/`ParentProviderLocation` lazily retrieve the *other* side's location by ID only when that side was not the one embedded in the original query.

   > **Origin**: This on-demand lazy load delegates to the [Provider location retrieval](provider-location-retrieval.md) operation implemented in this library.
7. **Filter to works-for relationships.** `WorksFor` (with `ActiveOnly = True`, the default) returns items whose relationship code is `WRK` and whose active window covers now; `WorksFor` with `ActiveOnly = False` skips the active-window check; `WorksFor(activeDate)` filters to `WRK` relationships whose window covers the supplied date regardless of the `ActiveOnly` flag.

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
flowchart TD
	Start([ChildRelationships or ParentRelationships requested]) --> Which{Which direction?}
	Which -->|Child relationships| ChildQ[Query RetrieveParentRelationshipsSQL<br>parent perspective]
	Which -->|Parent relationships| ParentQ[Query RetrieveChildRelationshipsSQL<br>child perspective]
	ChildQ --> Loop[For each row: create relationship,<br>Load relationship and embedded location]
	ParentQ --> Loop
	Loop --> Proj[Add destination location<br>to ProviderLocations projection]
	Proj --> Done([Populated relationship collection<br>and location projection returned])
	Done --> Use{Consumer request}
	Use -->|WorksFor| Filter[Filter to WRK code,<br>optionally active-window checked]
	Use -->|Opposite side location| Lazy[Lazily retrieve the<br>non embedded side by ID]
```

## Failure and edge-case behavior

- The public factory method names (`GetChildProviderRelationshipCollection` / `GetParentProviderRelationshipCollection`) name the relationship from the caller's perspective, while the internal SQL property names (`RetrieveChildRelationshipsSQL` / `RetrieveParentRelationshipsSQL`) name it from the query's result perspective — the two are deliberately inverted, and a reader tracing the code without noticing this mapping could infer the wrong direction.*
- A location with no relationships in the requested direction returns an empty collection with no exception raised.
- `WorksFor(activeDate)` computes an end-of-window value inline using `IIf`, comparing against `Date.MinValue` rather than reusing the class's own `TerminationDate` null-handling convention (`Date.MaxValue`) used elsewhere in this collection — a relationship with a genuinely unset termination date could be evaluated inconsistently between `IsActive` and `WorksFor(activeDate)`.*

## Related documentation

- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [SQL catalog](../sql/index.md)
- [Database tables](../database-tables.md)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts.

## Sources

- `Business\SAIF.Business.View.Provider\clsProviderRelationship.vb`
- `Business\SAIF.Business.View.Provider\clsProviderRelationshipCollection.vb`