SQL-PREL-02 Parent relationships of a child location

Retrieves the parent provider locations related to a given child provider location through PROVIDER_RELATIONSHIP, returning the parent location's full detail columns and the relationship's effective/termination dates and code.

Classification

Field	Value
SQL ID	SQL-PREL-02
Operation type	Read
Execution method	Inline SQL (BOF criteria constant)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsProviderRelationshipCollection
Criteria property	RetrieveParentRelationshipsSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderLocationID	Input	Decimal	N/A	Child location key; returns parent relationships

Tables

Table	Access	Columns used
PROVIDER_LOCATION	JOIN	PROVIDER_LOCATION_SEQ_NUM and full detail columns (APPROVED_FLAG, BUSINESS_NAME, BUSINESS_FLAG, etc.)
PROVIDER_RELATIONSHIP	JOIN	PROVIDER_LOCATION_SEQ_NUM, PARENT_PROVIDER_LOC_SEQ_NUM, EFF_DATE, EXP_DATE, PROVIDER_RELATIONSHIP_CODE

Sample SQL

-- Parent relationships of a child location
SELECT
  PR.Provider_Location_Seq_Num           AS PRProviderLocationID,
  PR.PARENT_PROVIDER_LOC_SEQ_NUM         AS ParentProviderLocationID,
  PR.EFF_DATE                            AS StartDate,
  PR.EXP_DATE                            AS TerminationDate,
  PR.PROVIDER_RELATIONSHIP_CODE          AS ProviderRelationshipCode,
  parentPL.APPROVED_FLAG                 AS IsApproved,
  parentPL.APPROVED_USER                 AS ApprovedUser,
  parentPL.BUSINESS_FLAG                 AS IsBusiness,
  parentPL.CRITICAL_ACCESS_HOSPITAL_FLAG AS IsCriticalAccessHospital,
  parentPL.BUSINESS_NAME                 AS BusinessName,
  parentPL.FAX_NUM                       AS FaxNum,
  parentPL.FIRST_NAME                    AS FirstName,
  parentPL.INELIGIBILITY_FLAG            AS IsIneligible,
  parentPL.LAST_NAME                     AS LastName,
  parentPL.MIDDLE_NAME                   AS MiddleName,
  parentPL.PAYEE_NAME                    AS PayeeName,
  parentPL.PHONE_AREA_CODE               AS PhoneAreaCode,
  parentPL.PHONE_NUM                     AS PhoneNum,
  parentPL.PHONE_NUM_EXT                 AS PhoneNumExt,
  parentPL.POSSIBLE_FRAUD_FLAG           AS IsPossibleFraud,
  parentPL.PRIMARY_LOCATION_FLAG         AS IsPrimaryLocation,
  parentPL.PROVIDER_LOCATION_SEQ_NUM     AS ProviderLocationID,
  parentPL.PROVIDER_SEQ_NUM              AS ProviderID,
  parentPL.PROVIDER_TYPE_CODE            AS ProviderTypeCode,
  parentPL.RECENT_ACTIVITY_FLAG          AS HasRecentActivity,
  parentPL.TOLL_FREE_FAX_NUM             AS TollFreeFaxNum,
  parentPL.TOLL_FREE_PHONE_NUM           AS TollFreePhoneNum,
  parentPL.UPDATE_DATE                   AS UpdateDate,
  parentPL.UPDATE_NUM                    AS UpdateNum,
  parentPL.UPDATE_USER                   AS UpdateUser,
  parentPL.WEB_SITE_ADDRESS              AS WebSiteAddress,
  parentPL.EMAIL_ADDR                    AS EmailAddress
FROM provider_location pl
  JOIN provider_relationship pr
    ON pr.provider_location_seq_num = PL.provider_location_seq_num
  LEFT JOIN provider_location parentPL
    ON parentPL.provider_location_seq_num = pr.parent_provider_loc_seq_num
WHERE pl.provider_location_seq_num = :ProviderLocationID
ORDER BY parentPL.BUSINESS_NAME

Note: RetrieveSQL returns the child statement when SourceRelationshipType is Parent (the Case Else branch) and the parent statement when it is Child.*

Operations and processes

• Load provider relationships

Business rules

• BR-PREL-001: Works-for relationship code
• BR-PREL-002: Active-only works-for filter
• BR-PREL-003: Relationship active window
• BR-PREL-004: Reciprocal relationship direction

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• SAIF.Business.View.Provider/clsProviderRelationshipCollection.vb