Skip to content

SQL-015: Provider search by dynamic criteria

This statement supports the clsProviderSearch 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-015
Operation type Read
Execution method StringBuilder-assembled
Database Oracle
Schema [unqualified]**
Command type Text
Business object clsProviderSearch
Criteria property N/A - direct query
Lifecycle method searchForProvider()

Parameters

Parameter Direction Type Max length Description
:BusinessFlag Input String 1 Optional; added only when BusinessPerson is BUSINESS or PERSON.
:TaxIDNumber Input String 9 Optional; exact tax ID filter against provider_tax_id.
:OtherID Input String 16 Optional; provider identity filter against provider_identity.IDENTITY_VALUE.
:ServiceType Input String 5 Optional; provider service filter.
:MCOPlan Input String 2 Optional; MCO plan filter.
:ProviderName Input String 50 Optional; prefix match for provider last/business/registered names.
:ProviderType Input String 5 Optional; provider type code filter.
:StreetAddress Input String 40 Optional; prefix match on physical address line 1.
:CityName Input String 25 Optional; exact city filter.
:StateCode Input String 2 Optional; exact state filter.
:ZipCode Input String 5 Optional; exact ZIP filter.
:PhoneAreaCode Input String 3 Optional; exact phone area-code filter.
:PhoneNumber Input String 7 Optional; prefix match on phone number after punctuation removal.

Tables

Table Access Columns used
PROVIDER_LOCATION SELECT PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SEQ_NUM, PROVIDER_TYPE_CODE
PROVIDER_TYPE_CODE SELECT CODE, CODE_DESCR, CLAIM_PROFILE_FLAG, DCBS_PROVIDER_TYPE_CODE
PROVIDER_ADDRESS SELECT PROVIDER_LOCATION_SEQ_NUM, PROVIDER_ADDRESS_SEQ_NUM, PROVIDER_ADDRESS_CODE, CITY_NAME, STATE_CODE, ZIP_CODE
PROVIDER_TAX_ID SELECT PROVIDER_SEQ_NUM, TAX_ID, REGISTERED_NAME
PROVIDER_IDENTITY SELECT PROVIDER_LOCATION_SEQ_NUM, IDENTITY_VALUE
PROVIDER_SERVICE SELECT PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SERVICE_CODE, PRIMARY_SERVICE_FLAG
MCO_PROVIDER SELECT PROVIDER_LOCATION_SEQ_NUM, MCO_PLAN_ID, EFF_DATE, EXP_DATE

Sample SQL

SELECT
  pl.PROVIDER_LOCATION_SEQ_NUM provLoc,
  pl.PRIMARY_LOCATION_FLAG primaryLoc,
  pl.BUSINESS_FLAG,
  PL.BUSINESS_NAME,
  TYPE.CODE_DESCR typeDescr,
  a1.ADDRESS_LINE1 AS physADDRESS,
  a1.CITY_NAME AS physCITY,
  a1.STATE_CODE AS physState,
  a1.ZIP_CODE AS physZip,
  CASE pl.BUSINESS_FLAG
    WHEN 'N' THEN NVL(RTRIM(pl.LAST_NAME), '') || ', ' || NVL(RTRIM(pl.FIRST_NAME), '') || ' ' || NVL(RTRIM(pl.MIDDLE_NAME), '')
    ELSE RTRIM(pl.BUSINESS_NAME)
  END AS fullNAME,
  CASE NVL(RTRIM(PL.PHONE_NUM) || ' ', ' ')
    WHEN ' ' THEN ''
    ELSE pl.PHONE_AREA_CODE || '.' || SUBSTR(pl.PHONE_NUM, 1, 3) || '.' || SUBSTR(pl.PHONE_NUM, 4)
  END AS PHONE
FROM provider_location PL
JOIN provider_type_code TYPE ON PL.PROVIDER_TYPE_CODE = TYPE.CODE
LEFT OUTER JOIN provider_address a1
  ON (
    pl.PROVIDER_LOCATION_SEQ_NUM = a1.PROVIDER_LOCATION_SEQ_NUM
    AND a1.PROVIDER_ADDRESS_CODE = 'PHY'
    AND a1.EXP_DATE IS NULL
  )
JOIN provider_tax_id TIN
  ON (PL.PROVIDER_SEQ_NUM = TIN.PROVIDER_SEQ_NUM)
JOIN provider_identity ID
  ON (PL.PROVIDER_LOCATION_SEQ_NUM = ID.PROVIDER_LOCATION_SEQ_NUM)
JOIN provider_service SVC
  ON (PL.PROVIDER_LOCATION_SEQ_NUM = SVC.PROVIDER_LOCATION_SEQ_NUM AND TRIM(SVC.PROVIDER_SERVICE_CODE) = :ServiceType)
JOIN mco_provider MP
  ON (PL.PROVIDER_LOCATION_SEQ_NUM = MP.PROVIDER_LOCATION_SEQ_NUM AND MP.MCO_PLAN_ID = :MCOPlan)
WHERE pl.BUSINESS_FLAG = :BusinessFlag
  AND TIN.TAX_ID = :TaxIDNumber
  AND RTRIM(ID.IDENTITY_VALUE) = :OtherID
  AND (pl.LAST_NAME_UPPER_CASE LIKE :ProviderName OR pl.BUSINESS_NAME_UPPER_CASE LIKE :ProviderName)
  AND pl.PROVIDER_TYPE_CODE = :ProviderType
  AND TRIM(a1.ADDRESS_LINE1) LIKE :StreetAddress
  AND TRIM(a1.CITY_NAME) = :CityName
  AND a1.STATE_CODE = :StateCode
  AND a1.ZIP_CODE = :ZipCode
  AND pl.PHONE_AREA_CODE = :PhoneAreaCode
  AND pl.PHONE_NUM LIKE :PhoneNumber
UNION
SELECT
  pl.PROVIDER_LOCATION_SEQ_NUM provLoc,
  pl.PRIMARY_LOCATION_FLAG primaryLoc,
  pl.BUSINESS_FLAG,
  PL.BUSINESS_NAME,
  TYPE.CODE_DESCR typeDescr,
  a1.ADDRESS_LINE1 AS physADDRESS,
  a1.CITY_NAME AS physCITY,
  a1.STATE_CODE AS physState,
  a1.ZIP_CODE AS physZip,
  TRIM(T.REGISTERED_NAME) AS fullNAME,
  CASE NVL(RTRIM(PL.PHONE_NUM) || ' ', ' ')
    WHEN ' ' THEN ''
    ELSE pl.PHONE_AREA_CODE || '.' || SUBSTR(pl.PHONE_NUM, 1, 3) || '.' || SUBSTR(pl.PHONE_NUM, 4)
  END AS PHONE
FROM provider_location PL
JOIN provider_tax_id T
  ON (PL.PROVIDER_SEQ_NUM = T.PROVIDER_SEQ_NUM)
JOIN provider_type_code TYPE ON PL.PROVIDER_TYPE_CODE = TYPE.CODE
LEFT OUTER JOIN provider_address a1
  ON (
    pl.PROVIDER_LOCATION_SEQ_NUM = a1.PROVIDER_LOCATION_SEQ_NUM
    AND a1.PROVIDER_ADDRESS_CODE = 'PHY'
    AND a1.EXP_DATE IS NULL
  )
JOIN provider_tax_id TIN
  ON (PL.PROVIDER_SEQ_NUM = TIN.PROVIDER_SEQ_NUM)
JOIN provider_identity ID
  ON (PL.PROVIDER_LOCATION_SEQ_NUM = ID.PROVIDER_LOCATION_SEQ_NUM)
JOIN provider_service SVC
  ON (PL.PROVIDER_LOCATION_SEQ_NUM = SVC.PROVIDER_LOCATION_SEQ_NUM AND TRIM(SVC.PROVIDER_SERVICE_CODE) = :ServiceType)
JOIN mco_provider MP
  ON (PL.PROVIDER_LOCATION_SEQ_NUM = MP.PROVIDER_LOCATION_SEQ_NUM AND MP.MCO_PLAN_ID = :MCOPlan)
WHERE pl.BUSINESS_FLAG = :BusinessFlag
  AND TIN.TAX_ID = :TaxIDNumber
  AND RTRIM(ID.IDENTITY_VALUE) = :OtherID
  AND T.REGISTERED_NAME_UPPER_CASE LIKE :ProviderName
  AND pl.PROVIDER_TYPE_CODE = :ProviderType
  AND TRIM(a1.ADDRESS_LINE1) LIKE :StreetAddress
  AND TRIM(a1.CITY_NAME) = :CityName
  AND a1.STATE_CODE = :StateCode
  AND a1.ZIP_CODE = :ZipCode
  AND pl.PHONE_AREA_CODE = :PhoneAreaCode
  AND pl.PHONE_NUM LIKE :PhoneNumber
ORDER BY fullNAME ASC

Operations and processes

Business rules

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

Sources

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