SQL-PREL-01 Child relationships of a parent location

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

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderLocationID	Input	Decimal	N/A	Parent location key; returns child 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

-- Child relationships of a parent 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,
  PL.APPROVED_FLAG                 AS IsApproved,
  PL.APPROVED_USER                 AS ApprovedUser,
  PL.BUSINESS_FLAG                 AS IsBusiness,
  PL.CRITICAL_ACCESS_HOSPITAL_FLAG AS IsCriticalAccessHospital,
  PL.BUSINESS_NAME                 AS BusinessName,
  PL.FAX_NUM                       AS FaxNum,
  PL.FIRST_NAME                    AS FirstName,
  PL.INELIGIBILITY_FLAG            AS IsIneligible,
  PL.LAST_NAME                     AS LastName,
  PL.MIDDLE_NAME                   AS MiddleName,
  PL.PAYEE_NAME                    AS PayeeName,
  PL.PHONE_AREA_CODE               AS PhoneAreaCode,
  PL.PHONE_NUM                     AS PhoneNum,
  PL.PHONE_NUM_EXT                 AS PhoneNumExt,
  PL.POSSIBLE_FRAUD_FLAG           AS IsPossibleFraud,
  PL.PRIMARY_LOCATION_FLAG         AS IsPrimaryLocation,
  PL.PROVIDER_LOCATION_SEQ_NUM     AS ProviderLocationID,
  PL.PROVIDER_SEQ_NUM              AS ProviderID,
  PL.PROVIDER_TYPE_CODE            AS ProviderTypeCode,
  PL.RECENT_ACTIVITY_FLAG          AS HasRecentActivity,
  PL.TOLL_FREE_FAX_NUM             AS TollFreeFaxNum,
  PL.TOLL_FREE_PHONE_NUM           AS TollFreePhoneNum,
  PL.UPDATE_DATE                   AS UpdateDate,
  PL.UPDATE_NUM                    AS UpdateNum,
  PL.UPDATE_USER                   AS UpdateUser,
  PL.WEB_SITE_ADDRESS              AS WebSiteAddress,
  PL.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 parentpl.provider_location_seq_num = :ProviderLocationID
ORDER BY PL.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