SQL-PSVC-01 Provider services

Retrieves the list of services offered by a provider location, joining the service code table using legacy comma-style (implicit) join syntax to obtain the service description and DCBS specialty code alongside the primary-service flag.

Classification

Field	Value
SQL ID	SQL-PSVC-01
Operation type	Read
Execution method	Inline SQL (StringBuilder-assembled) via SqlHelper.ResultSetQuery
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	N/A (Application layer)
Criteria property	N/A
Lifecycle method	N/A
Application class	clsProviderServiceList

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderLocationID	Input	Decimal	N/A	Provider location surrogate key

Tables

Table	Access	Columns used
PROVIDER_SERVICE_CODE	JOIN (implicit comma join)	CODE, CODE_DESCR, DCBS_PROVIDER_SPECIALTY_CODE
PROVIDER_SERVICE	JOIN (implicit comma join)	PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SERVICE_CODE, PRIMARY_SERVICE_FLAG

Sample SQL

-- Provider services
SELECT
  PROVIDER_SERVICE_CODE.CODE                          AS ServiceCode,
  PROVIDER_SERVICE_CODE.CODE_DESCR                    AS ServiceDescription,
  PROVIDER_SERVICE_CODE.DCBS_PROVIDER_SPECIALTY_CODE  AS DCBSSpecialtyCode,
  PROVIDER_SERVICE.PRIMARY_SERVICE_FLAG               AS PrimaryServiceFlag
FROM PROVIDER_SERVICE_CODE,
     PROVIDER_SERVICE
WHERE PROVIDER_SERVICE.PROVIDER_LOCATION_SEQ_NUM = :ProviderLocationID
  AND PROVIDER_SERVICE_CODE.CODE = PROVIDER_SERVICE.PROVIDER_SERVICE_CODE

Uses legacy comma-style (implicit) join syntax rather than ANSI JOIN.

Operations and processes

• Load provider services and contracts

Business rules

• BR-PSVC-001: Service contract offset window

Sources

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