---
title: SQL-015: Provider search by dynamic criteria
summary: Provider search by dynamic criteria retrieval query.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-07-24
library: SAIF.Business.View.Provider
reference_type: package
---

# 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](../database-tables.md#provider_location) | SELECT | PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SEQ_NUM, PROVIDER_TYPE_CODE |
| [PROVIDER_TYPE_CODE](../database-tables.md#provider_type_code) | SELECT | CODE, CODE_DESCR, CLAIM_PROFILE_FLAG, DCBS_PROVIDER_TYPE_CODE |
| [PROVIDER_ADDRESS](../database-tables.md#provider_address) | SELECT | PROVIDER_LOCATION_SEQ_NUM, PROVIDER_ADDRESS_SEQ_NUM, PROVIDER_ADDRESS_CODE, CITY_NAME, STATE_CODE, ZIP_CODE |
| [PROVIDER_TAX_ID](../database-tables.md#provider_tax_id) | SELECT | PROVIDER_SEQ_NUM, TAX_ID, REGISTERED_NAME |
| [PROVIDER_IDENTITY](../database-tables.md#provider_identity) | SELECT | PROVIDER_LOCATION_SEQ_NUM, IDENTITY_VALUE |
| [PROVIDER_SERVICE](../database-tables.md#provider_service) | SELECT | PROVIDER_LOCATION_SEQ_NUM, PROVIDER_SERVICE_CODE, PRIMARY_SERVICE_FLAG |
| [MCO_PROVIDER](../database-tables.md#mco_provider) | SELECT | PROVIDER_LOCATION_SEQ_NUM, MCO_PLAN_ID, EFF_DATE, EXP_DATE |

## Sample SQL

```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
- [Search providers](../operations/provider-search.md)

## Business rules
- [BR-003: Provider search dynamic join filtering](../business-rules.md#br-003-provider-search-dynamic-join-filtering)

## Related documentation
- [Index](../index.md)
- [Overview](../overview.md)
- [Architecture](../architecture.md)
- [Dependencies](../dependencies.md)
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [Database tables](../database-tables.md)
- [SQL catalog](../sql/index.md)
- [API reference](../api/index.md)

> **Note:** Statements marked with `**` could not be confirmed; treat as unknown until verified.
## Sources
- `Business\SAIF.Business.View.Provider\clsProviderSearch.vb`