Skip to content

SQL-016: Provider service contracts by location

This statement supports the clsProviderServiceContractList retrieval path by reading provider-domain data from Oracle tables and projecting the result into business objects or DTO lists.

Classification

Field Value
SQL ID SQL-016
Operation type Read
Execution method StringBuilder-assembled
Database Oracle
Schema [unqualified]**
Command type Text
Business object clsProviderServiceContractList
Criteria property N/A - direct query
Lifecycle method Constructor

Parameters

Parameter Direction Type Max length Description
:ProviderLocationID Input Decimal N/A Filters service contracts to one provider location.
:Offset Input Decimal N/A Optional; only bound when inOffset > 0, expanding the expiration lookback window.

Tables

Table Access Columns used
PROVIDER_SERVICE_CONTRACT SELECT PROV_SERVICE_CONTRACT_SEQ_NUM, PROVIDER_LOCATION_SEQ_NUM, MCO_PLAN_ID

Sample SQL

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

Operations and processes

Business rules

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

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