---
title: Maintain provider relationship
summary: Persists provider-to-provider relationships, prevents invalid or conflicting links, blocks deletion of in-use relationships, and syncs WRK relationships to Contact Manager.
status: draft
owner: SAIF
last_reviewed: 2026-07-30
library: SAIF.Business.Provider
reference_type: package
operation_id: OP-PRV-08
trigger: Caller creates or retrieves a clsProviderRelationship, edits properties, and calls Save() or Delete()
---

# Maintain provider relationship

`clsProviderRelationship` links two provider locations (child and parent) under a relationship code and effective date range. Relationships coded `"WRK"` are additionally synchronized to Contact Manager through `clsProviderRelationshipApi`, and relationships referenced by an attending physician or claim legal representative cannot be deleted outright.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-PRV-08 |
| **Trigger** | Caller calls `NewProviderRelationship`/`GetProviderRelationship`, edits properties, and calls `Save()` or `Delete()` |
| **Inputs** | `ProviderLocationSeqNum` (child), `ParentProviderLocSeqNum`, `ProviderRelationshipCode`, `EffDate`/`ExpDate` |
| **Outputs** | Persisted [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship) row; ContactManager `ProviderRelationshipDto` sync for `"WRK"` relationships |
| **Primary components** | `clsProviderRelationship.Create`, `BeforeInsert/BeforeUpdate/BeforeDelete`, `RelationshipExists`, `RelationshipInUse`, `clsProviderRelationshipApi.AddProviderRelationship` |
| **Entry point** | `Business\SAIF.Business.Provider\clsProviderRelationship.vb` |

## Steps

1. **Create.** `Create()` defaults both `ProviderLocationSeqNum` (child) and `ParentProviderLocSeqNum` to the same criteria value, requiring the caller to subsequently set a distinct parent.
2. **Resolve display names.** Setting either the child or parent location seq num calls `clsProviderLocation.GetProviderLocation(...).FormattedName` to populate `FullNameChild`/`FullNameParent` for display and validation.
3. **Retrieve.** The retrieve SQL selects from [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship), left joining [PROVIDER_LOCATION](../database-tables.md#provider_location) twice to derive both display names ([SQL-055](../sql/SQL-055-retrieve-provider-relationship.md)); `BeforeRetrieve` binds only `ProviderLocationSeqNum`, even though the SQL text also references `ParentProviderLocSeqNum` and `EffDate`.*
4. **Validate before save.**
   - `MissingProviderLocation`/`UnknownProviderLocation` and `MissingParentProviderLocation`/`UnknownParentProviderLocation`: hard errors requiring both locations to be entered and to resolve to a nonblank `FullNameChild`/`FullNameParent`.
   - `MissingRelationshipCode`: hard error requiring `ProviderRelationshipCode`.
   - [BR-013](../business-rules.md#br-013-relationship-participants-must-exist-and-be-distinct): `ParentSameAsChild` blocks a relationship where the child and parent location are identical.
   - `MissingEffDate`, `ValidEffExpDate` (expiration not before effective), and `ValidEffDate` (effective date not before today, new rows only) enforce [BR-014](../business-rules.md#br-014-relationship-dates-must-be-valid-and-non-overlapping).
   - `DuplicateRelationship`: hard error running `RelationshipExists` against [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship); overlap logic treats an open-ended new expiration as `DateTime.MaxValue` and checks whether the new range starts inside, ends inside, or fully contains an existing range for the same participants, excluding the row being edited by its own `update_date` ([BR-014](../business-rules.md#br-014-relationship-dates-must-be-valid-and-non-overlapping)).
   - [BR-024](../business-rules.md#br-024-a-provider-relationship-in-use-cannot-be-deleted-only-expired): `RelationshipInUseRule` runs `RelationshipInUse`, which queries `ATTENDING_PHYSICIAN` and `CL_CLAIM_LEGAL_REPRESENTATIVE`, but only when an **existing** relationship's child or parent location is being changed; it is not re-invoked as a hard delete-time gate in the visible `AfterDelete()` path.*
5. **Insert.** Writes `PROVIDER_LOCATION_SEQ_NUM`, `PARENT_PROVIDER_LOC_SEQ_NUM`, `PROVIDER_RELATIONSHIP_CODE`, `EFF_DATE`/`EXP_DATE`, and audit fields to [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship) ([SQL-030](../sql/SQL-030-insert-provider-relationship.md)).
   - On a rows-affected value other than `1`: add `"Insert Failed"`.
   - On success, **only if `ProviderRelationshipCode = "WRK"`**: build `ProviderRelationshipDto` (`providerNumber`, `RelatedProviderLocationSeqNumber = ParentProviderLocSeqNum`, `RelationshipType` via `ProviderTranslator.RelationshipCodeToCMRelationshipType`) and call `clsProviderRelationshipApi.AddProviderRelationship(...)` against route `provider/{providerLocationSeqNumber}/relationship`.
6. **Update.** Writes the same fields to [PROVIDER_RELATIONSHIP](../database-tables.md#provider_relationship) ([SQL-078](../sql/SQL-078-update-provider-relationship.md)) with concurrency check on the original composite key (`OrigProviderLocationSeqNum`, `OrigParentProviderLocSeqNum`, `OrigEffDate`) plus `UPDATE_NUM`.
   - For `"WRK"` relationships, on success: if the row's expiration is now set, call the API delete; otherwise attempt an API update, falling back to an API insert if the update fails; failed API calls are JSON-logged and surfaced as `ApiDeleteError`, `ApiInsertError`, or `ApiUpdateError`.
7. **Delete.** Deletes by the original composite key only, without an `UPDATE_NUM` check ([SQL-100](../sql/SQL-100-delete-provider-relationship.md)).
   - On success, for `"WRK"` relationships: call the API delete against route `provider/{providerLocationSeqNumber}/relationship/remove`.

## Process flow

```mermaid
flowchart TD
	Start([Caller edits relationship]) --> Distinct{Child and parent distinct?}
	Distinct -->|No| Fail([Missing or identical<br>participants block save])
	Distinct -->|Yes| Dates{Effective/expiration dates valid?}
	Dates -->|No| Fail2([Invalid or overlapping<br>dates block save])
	Dates -->|Yes| Overlap{Overlaps existing relationship?}
	Overlap -->|Yes| Fail2
	Overlap -->|No| InUse{Existing relationship's<br>parties changing and in use?}
	InUse -->|Yes| Fail3([In-use relationship<br>blocks delete/change])
	InUse -->|No| Persist[Insert/update/delete PROVIDER_RELATIONSHIP]
	Persist --> Rows{RowsAffected = 1?}
	Rows -->|No| Contention([Insert Failed / DB contention error])
	Rows -->|Yes| WrkCheck{Relationship code = WRK?}
	WrkCheck -->|Yes| Api[Sync to Contact Manager<br>via clsProviderRelationshipApi]
	WrkCheck -->|No| Done([Save complete])
	Api --> Done
```

## Failure and edge-case behavior

- The retrieve SQL text references `ParentProviderLocSeqNum` and `EffDate` but only `ProviderLocationSeqNum` is bound as a parameter, so a retrieve keyed only by child location may return an unfiltered or unexpected row set.*
- The in-use protection ([BR-024](../business-rules.md#br-024-a-provider-relationship-in-use-cannot-be-deleted-only-expired)) is only triggered when editing an existing relationship's participants — a direct delete call does not appear to re-run this check in the visible code, so its effectiveness as a delete guard depends on how callers invoke it.*
- Only relationships coded `"WRK"` synchronize to Contact Manager; all other relationship types persist to the database with no external side effect.
- Update-time API handling for `"WRK"` relationships falls back from update to insert on failure, which could create a duplicate remote record if the original update failed for a reason other than a missing remote record.*

## 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.Provider\clsProviderRelationship.vb`
- `Business\SAIF.Business.Provider\Api\clsProviderRelationshipApi.vb`
- `Business\SAIF.Business.Provider\clsProviderLocation.vb`