SQL-PLOC-03 Provider locations by provider type

Retrieves every PROVIDER_LOCATION record matching a given provider type code, used to filter a clsProviderLocationCollection by ProviderTypeCode.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderTypeCode	Input	String	5	PROVIDER_LOCATION.PROVIDER_TYPE_CODE

Tables

Table	Access	Columns used
PROVIDER_LOCATION	SELECT	APPROVED_FLAG, APPROVED_USER, BUSINESS_FLAG, CRITICAL_ACCESS_HOSPITAL_FLAG, BUSINESS_NAME, FAX_NUM, FIRST_NAME, INELIGIBILITY_FLAG, LAST_NAME, MIDDLE_NAME, PAYEE_NAME, PHONE_AREA_CODE, PHONE_NUM, PHONE_NUM_EXT, POSSIBLE_FRAUD_FLAG, PRIMARY_LOCATION_FLAG, PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SEQ_NUM, PROVIDER_TYPE_CODE, RECENT_ACTIVITY_FLAG, TOLL_FREE_FAX_NUM, TOLL_FREE_PHONE_NUM, UPDATE_DATE, UPDATE_NUM, UPDATE_USER, WEB_SITE_ADDRESS, EMAIL_ADDR

Sample SQL

-- Provider locations by provider type
SELECT
  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
WHERE PROVIDER_TYPE_CODE = :ProviderTypeCode

Operations and processes

• Retrieve provider locations by provider or type

Business rules

• BR-PLOC-004: Ineligibility period exclusion

Sources

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