Skip to content

Provider search

Provider search is a dedicated orchestrator (clsProviderSearch), distinct from the standard business-object/criteria retrieval pattern used elsewhere in this library. It builds its SQL entirely at runtime from a StringBuilder, adding a join and a bind parameter only for each search field the caller actually populated (BR-003). When the caller supplies a provider name, the query becomes a UNION of a name-based branch (matching last/business name) and a registered-name branch (matching the tax ID record's registered name), so a provider can be found by either its display name or its registered tax name.

Field Value
Operation ID OP-VP-08
Trigger Caller sets fields on clsProviderSearch.SearchCriteria (a nested clsSearchCriteria business object) and calls searchForProvider()
Inputs SearchCriteria.BusinessPerson ("BUSINESS", "PERSON", or blank for both), TaxId, OtherId, ServiceType, MCOName, ProviderName, ProviderType, Street, City, State, ZipCode, AreaCode, PhoneNum — all optional
Outputs Providers — a clsProviders collection of clsSearchResults, each carrying the provider location ID, primary-location flag, business flag, type description, resolved full name, sort name, physical address fields, and phone number, ordered by sort name; capped at 300 results
Primary components clsProviderSearch.searchForProvider, clsProviderSearch.FormatSortName, clsProviderSearch.PRVSearch, clsProviderSearch.PRVSearchRegName
Entry point Business\SAIF.Business.View.Provider\clsProviderSearch.vb

Steps

  1. Reset accumulated results. searchForProvider() starts by replacing m_Providers with a fresh, empty clsProviders collection, discarding any prior search results before running the new search.
  2. Apply the business/person filter. Based on SearchCriteria.BusinessPerson: "BUSINESS" adds WHERE pl.BUSINESS_FLAG = :BusinessFlag bound to "Y"; "PERSON" binds "N"; any other value starts the WHERE clause as 1 = 1 so every subsequent condition can be appended as an AND with no business-flag filter applied (BR-003).
  3. Add a tax ID join when supplied. If TaxId is non-blank: join provider_tax_id TIN on the provider ID and bind :TaxIDNumber to the trimmed tax ID with dashes removed (SQL-015, PROVIDER_TAX_ID) (BR-003).
  4. Add an other-identifier join when supplied. If OtherId is non-blank: join provider_identity ID on the provider location ID and bind :OtherID to the upper-cased, trimmed value (PROVIDER_IDENTITY) (BR-003).
  5. Add a service-type join when supplied. If ServiceType is non-blank: join provider_service SVC on the provider location ID, filtering the service code to the supplied value, and bind :ServiceType (PROVIDER_SERVICE) (BR-003).
  6. Add an MCO plan join when supplied. If MCOName is non-blank: join mco_provider MP on the provider location ID, filtering the MCO plan ID, and bind :MCOPlan (MCO_PROVIDER) (BR-003).
  7. Append name, type, address, and phone predicates when supplied. For each of ProviderName, ProviderType, Street, City, State, ZipCode, AreaCode, and PhoneNum that is non-blank, append the matching AND predicate (and, when a provider name is supplied, the parallel predicate on the registered-name branch) and bind the corresponding parameter (PROVIDER_LOCATION, PROVIDER_ADDRESS) (BR-003).
  8. The provider-name predicate matches LAST_NAME_UPPER_CASE or BUSINESS_NAME_UPPER_CASE with a trailing-wildcard LIKE, so it is effectively a starts-with search on the upper-cased name.
  9. Assemble the final query. The base name-search branch (PRVSearch) and, only when a provider name was supplied, the registered-name branch (PRVSearchRegName) joined by UNION, each with the same dynamically built join and WHERE clauses appended, then a fixed ORDER BY fullNAME ASC (SQL-015, PROVIDER_LOCATION, PROVIDER_TYPE_CODE).
  10. Execute and map results. Each returned row is mapped into a clsSearchResults with the location ID, primary-location flag, business flag, type description, full name, a computed sort name, physical address line/city/state/zip, and phone number.
  11. Sub-step: FormatSortName re-orders a "Last, First Middle" display name into "First Last, ..." for sorting purposes when the row is a person (not flagged as a business) and the name contains a comma; business names pass through unchanged.
  12. Cap the result set. If more than 299 results have already been added, the mapping loop exits immediately (Exit For), leaving the result set capped at 300 rows and silently truncating any further matches.
  13. Sort the final result set. m_Providers.OrderBy(...) sorts by the computed sort name — the return value of OrderBy is discarded (LINQ's OrderBy does not mutate the source collection in place), so this sort call has no observable effect on the returned Providers collection order.*

Process flow

The following diagram visualizes this operation's control flow.

flowchart TD
    Start([searchForProvider called]) --> Reset[Reset Providers collection]
    Reset --> BizFlag{BusinessPerson filter?}
    BizFlag -->|Business| WhereBiz[WHERE BUSINESS_FLAG = Y]
    BizFlag -->|Person| WherePerson[WHERE BUSINESS_FLAG = N]
    BizFlag -->|Neither| WhereAll[WHERE 1 = 1]
    WhereBiz --> Joins
    WherePerson --> Joins
    WhereAll --> Joins
    Joins[Add joins only for supplied filters:<br>tax ID, other ID, service type, MCO plan] --> Preds[Append predicates only for supplied filters:<br>name, type, street, city, state, zip, area code, phone]
    Preds --> NameCheck{Provider name supplied?}
    NameCheck -->|Yes| Union[UNION base branch<br>with registered name branch]
    NameCheck -->|No| Single[Use base branch only]
    Union --> Exec[Execute assembled query,<br>ORDER BY fullNAME]
    Single --> Exec
    Exec --> Loop[Map each row to clsSearchResults;<br>compute sort name]
    Loop --> Cap{More than 299 results added?}
    Cap -->|Yes| Stop([Stop mapping; result set capped at 300])
    Cap -->|No| More{More rows?}
    More -->|Yes| Loop
    More -->|No| Done([Providers collection returned])
    Stop --> Done

Failure and edge-case behavior

  • The result set is silently capped at 300 rows; a search that matches more providers than that returns only the first 300 encountered, with no indication to the caller that results were truncated.
  • The final m_Providers.OrderBy(...) call discards its return value, so it does not actually reorder the Providers collection; the effective result order is whatever ORDER BY fullNAME ASC produced from the database.*
  • When no provider name is supplied, only the base name-search branch runs; a provider findable only by registered tax name (and not by last/business name) will not appear in a search that omits the name filter.
  • The single-quote replacement on the provider name search value (SearchName.Replace("'", "''")) discards its result rather than reassigning it back to SearchName, so embedded single quotes are not actually escaped before being bound as a parameter value — though because the value is passed as a bind parameter rather than concatenated into the SQL text, this does not create a SQL-injection risk, only a functional no-op.*

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts.

Sources

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