Skip to content

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 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, left joining PROVIDER_LOCATION twice to derive both display names (SQL-055); BeforeRetrieve binds only ProviderLocationSeqNum, even though the SQL text also references ParentProviderLocSeqNum and EffDate.*
  4. Validate before save.
  5. MissingProviderLocation/UnknownProviderLocation and MissingParentProviderLocation/UnknownParentProviderLocation: hard errors requiring both locations to be entered and to resolve to a nonblank FullNameChild/FullNameParent.
  6. MissingRelationshipCode: hard error requiring ProviderRelationshipCode.
  7. BR-013: ParentSameAsChild blocks a relationship where the child and parent location are identical.
  8. MissingEffDate, ValidEffExpDate (expiration not before effective), and ValidEffDate (effective date not before today, new rows only) enforce BR-014.
  9. DuplicateRelationship: hard error running RelationshipExists against 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).
  10. BR-024: 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.*
  11. Insert. Writes PROVIDER_LOCATION_SEQ_NUM, PARENT_PROVIDER_LOC_SEQ_NUM, PROVIDER_RELATIONSHIP_CODE, EFF_DATE/EXP_DATE, and audit fields to PROVIDER_RELATIONSHIP (SQL-030).
  12. On a rows-affected value other than 1: add "Insert Failed".
  13. On success, only if ProviderRelationshipCode = "WRK": build ProviderRelationshipDto (providerNumber, RelatedProviderLocationSeqNumber = ParentProviderLocSeqNum, RelationshipType via ProviderTranslator.RelationshipCodeToCMRelationshipType) and call clsProviderRelationshipApi.AddProviderRelationship(...) against route provider/{providerLocationSeqNumber}/relationship.
  14. Update. Writes the same fields to PROVIDER_RELATIONSHIP (SQL-078) with concurrency check on the original composite key (OrigProviderLocationSeqNum, OrigParentProviderLocSeqNum, OrigEffDate) plus UPDATE_NUM.
  15. 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.
  16. Delete. Deletes by the original composite key only, without an UPDATE_NUM check (SQL-100).
  17. On success, for "WRK" relationships: call the API delete against route provider/{providerLocationSeqNumber}/relationship/remove.

Process flow

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

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