Skip to content

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-014, PROVIDER_LOCATION, PROVIDER_RELATIONSHIP).
  4. 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.
  5. RetrieveParentRelationshipsSQL filters WHERE pl.provider_location_seq_num = :ProviderLocationID, returning the parent locations, ordered by the parent's business name.
  6. 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.
  7. 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.
  8. 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 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.

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

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