SQL-MCO-01 MCO provider plans¶

Retrieves the list of managed care organization (MCO) plan enrollments associated with a provider location, including patient-acceptance, directory-listing, and administrative fee attributes for each plan.

Classification¶

Field	Value
SQL ID	SQL-MCO-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	`clsMCOProviderList`

Parameters¶

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

Tables¶

Table	Access	Columns used
MCO_PROVIDER	SELECT	`PROVIDER_LOCATION_SEQ_NUM`, `MCO_PLAN_ID`, `EFF_DATE`, `EXP_DATE`, `NEW_PATIENTS_FLAG`, `DIRECTORY_FLAG`, `ADMIN_FEE_RATE`, `ADMIN_FEE_FLAG`

Sample SQL¶

-- MCO provider plans
SELECT
  PROVIDER_LOCATION_SEQ_NUM AS ProviderLocationID,
  MCO_PLAN_ID               AS McoPlanID,
  EFF_DATE                  AS EffectiveDate,
  EXP_DATE                  AS ExpirationDate,
  NEW_PATIENTS_FLAG         AS NewPatientsFlag,
  DIRECTORY_FLAG            AS DirectoryFlag,
  ADMIN_FEE_RATE            AS AdminFeeRate,
  ADMIN_FEE_FLAG            AS AdminFeeFlag
FROM MCO_PROVIDER
WHERE PROVIDER_LOCATION_SEQ_NUM = :ProviderLocationID

Operations and processes¶

Load MCO enrollments

Business rules¶

BR-MCO-001: MCO plan match validation

Sources¶

SAIF.Business.View.Provider/clsMCOProvider.vb