Skip to content

SQL-SRCH-01 Provider search

Performs a dynamically assembled provider search across PROVIDER_LOCATION and its related tables, joining tax ID, identity, service, and MCO plan tables only when the caller supplies the corresponding search criteria, and unioning a registered-name match branch for business entities.

Classification

Field Value
SQL ID SQL-SRCH-01
Operation type Read
Execution method Dynamically assembled (const fragments + conditional predicates, all bind-parameterized)
Database Oracle
Schema [unqualified]**
Command type Text
Business object N/A (Application layer)
Criteria property N/A
Lifecycle method N/A
Application class clsProviderSearch

Parameters

Parameter Direction Type Max length Description
:BusinessFlag Input String 1 'Y' for BUSINESS, 'N' for PERSON; omitted when search type is ALL
:TaxIDNumber Input String 9 Optional join predicate on PROVIDER_TAX_ID.TAX_ID
:OtherID Input String 16 Optional join predicate on RTRIM(PROVIDER_IDENTITY.IDENTITY_VALUE)
:ServiceType Input String 5 Optional join predicate on TRIM(PROVIDER_SERVICE.PROVIDER_SERVICE_CODE)
:MCOPlan Input String 2 Optional join predicate on MCO_PROVIDER.MCO_PLAN_ID
:ProviderName Input String 50 Value has "%" appended; matched with LIKE against upper-case name columns
:ProviderType Input String 5 Optional pl.PROVIDER_TYPE_CODE equality predicate
:StreetAddress Input String 40 Value has "%" appended; LIKE against TRIM(a1.ADDRESS_LINE1)
:CityName Input String 25 Equality against TRIM(a1.CITY_NAME)
:StateCode Input String 2 Equality against a1.STATE_CODE
:ZipCode Input String 5 Equality against a1.ZIP_CODE
:PhoneAreaCode Input String 3 Equality against pl.PHONE_AREA_CODE
:PhoneNumber Input String 7 - and . stripped, "%" appended; LIKE against pl.PHONE_NUM

Tables

Table Access Columns used
PROVIDER_LOCATION SELECT PROVIDER_LOCATION_SEQ_NUM, PRIMARY_LOCATION_FLAG, BUSINESS_FLAG, BUSINESS_NAME, LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE_AREA_CODE, PHONE_NUM, PROVIDER_TYPE_CODE
PROVIDER_TYPE_CODE JOIN CODE, CODE_DESCR
PROVIDER_ADDRESS JOIN (LEFT OUTER, optional) ADDRESS_LINE1, CITY_NAME, STATE_CODE, ZIP_CODE, PROVIDER_ADDRESS_CODE, EXP_DATE
PROVIDER_TAX_ID JOIN (optional) PROVIDER_SEQ_NUM, TAX_ID, REGISTERED_NAME, REGISTERED_NAME_UPPER_CASE
PROVIDER_IDENTITY JOIN (optional) PROVIDER_LOCATION_SEQ_NUM, IDENTITY_VALUE
PROVIDER_SERVICE JOIN (optional) PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SERVICE_CODE
MCO_PROVIDER JOIN (optional) PROVIDER_LOCATION_SEQ_NUM, MCO_PLAN_ID

Sample SQL

Fully assembled example with the registered-name UNION branch active and every optional predicate applied. Optional joins (TIN, ID, SVC, MP) and each AND predicate are appended only when the corresponding search field is populated.

-- Provider search
SELECT
  pl.PROVIDER_LOCATION_SEQ_NUM AS provLoc,
  pl.PRIMARY_LOCATION_FLAG     AS primaryLoc,
  pl.BUSINESS_FLAG,
  PL.BUSINESS_NAME,
  TYPE.CODE_DESCR              AS 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)
    AND TIN.TAX_ID = :TaxIDNumber
  JOIN provider_identity ID
    ON (PL.PROVIDER_LOCATION_SEQ_NUM = ID.PROVIDER_LOCATION_SEQ_NUM)
    AND RTRIM(ID.IDENTITY_VALUE) = :OtherID
  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 (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 AS provLoc,
  pl.PRIMARY_LOCATION_FLAG     AS primaryLoc,
  pl.BUSINESS_FLAG,
  PL.BUSINESS_NAME,
  TYPE.CODE_DESCR              AS 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_type_code TYPE
    ON PL.PROVIDER_TYPE_CODE = TYPE.CODE
  JOIN provider_tax_id T
    ON (PL.PROVIDER_SEQ_NUM = T.PROVIDER_SEQ_NUM)
  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)
WHERE pl.BUSINESS_FLAG = :BusinessFlag
  AND t.REGISTERED_NAME_UPPER_CASE LIKE :ProviderName
ORDER BY fullNAME ASC

When no business/person filter is requested the WHERE seed is WHERE 1 = 1. The caller truncates the result set at 300 rows (If m_Providers.Count > 299 Then Exit For).

Operations and processes

Business rules

Sources

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