SQL-PSVC-02 Provider service contracts

Retrieves the service-contract records for a provider location, ordered by effective date descending, optionally filtered to contracts still active or expired within a caller-specified day offset from the current date.

Classification

Field	Value
SQL ID	SQL-PSVC-02
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	clsProviderServiceContractList

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderLocationID	Input	Decimal	N/A	Provider location surrogate key
:Offset	Input	Decimal	N/A	Day offset (default 721); only bound when the expiry-date filter is appended

Tables

Table	Access	Columns used
PROVIDER_SERVICE_CONTRACT	SELECT	PROV_SERVICE_CONTRACT_SEQ_NUM, PROVIDER_LOCATION_SEQ_NUM, MCO_PLAN_ID, EFF_DATE, EXP_DATE, PURCHASE_CODE, DISCOUNT_RATE, ADDITIONAL_FEE_DISCOUNT_RATE, MAX_CHARGE_AMT, CBP_SERVICE_CODE, SERVICE_CODE, SERVICE_TYPE_CODE, CONTACT_NAME, SERVICE_MODIFIER_CODE

Sample SQL

-- Provider service contracts
SELECT
  PROV_SERVICE_CONTRACT_SEQ_NUM AS ContractID,
  PROVIDER_LOCATION_SEQ_NUM     AS ProviderLocationID,
  MCO_PLAN_ID                   AS McoPlanID,
  EFF_DATE                      AS EffectiveDate,
  EXP_DATE                      AS ExpirationDate,
  PURCHASE_CODE                 AS PurchaseCode,
  DISCOUNT_RATE                 AS DiscountRate,
  ADDITIONAL_FEE_DISCOUNT_RATE  AS AdditionalFeeDiscountRate,
  MAX_CHARGE_AMT                AS MaxChargeAmount,
  CBP_SERVICE_CODE              AS CBPServiceCode,
  SERVICE_CODE                  AS ServiceCode,
  SERVICE_TYPE_CODE             AS ServiceTypeCode,
  CONTACT_NAME                  AS ContactName,
  SERVICE_MODIFIER_CODE         AS ServiceModifierCode
FROM PROVIDER_SERVICE_CONTRACT
WHERE PROVIDER_LOCATION_SEQ_NUM = :ProviderLocationID
  AND (exp_date IS NULL OR exp_date > (sysdate - :Offset))
ORDER BY eff_date DESC

The exp_date predicate is appended only when the caller requests the date-window filter; inOffset defaults to 721 days.

Operations and processes

• Load provider services and contracts

Business rules

• BR-PSVC-001: Service contract offset window
• BR-MCO-001: MCO plan match validation
• BR-DATE-001: Date-window effectiveness

Sources

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