Skip to content

Search providers

clsProviderSearch builds a conditional SQL statement one clause at a time based on which search fields the caller populated, executes it through the generic SqlHelper.ResultSetQuery helper (not the BOF CRUD data portal), and caps the result set at 300 rows.

Field Value
Operation ID OP-PRV-02
Trigger Caller populates clsSearchCriteria (nested class) and calls clsProviderSearch.searchForProvider()
Inputs ProviderName, TaxId, OtherId, ProviderType, ServiceType, MCOName, Street, City, State, ZipCode, AreaCode, PhoneNum, BusinessPerson, EligibleSearchFlag
Outputs clsProviders collection of clsSearchResults rows, capped at 300
Primary components clsProviderSearch.searchForProvider, clsProviderSearch.clsSearchCriteria, SqlHelper.ResultSetQuery, clsProviderSearch.FormatSortName
Entry point Business\SAIF.Business.Provider\clsProviderSearch.vb

Steps

  1. Caller sets criteria. The caller populates one or more fields on the nested clsSearchCriteria object; resetSearchCriteria() is available to clear a prior search.
  2. Business/person filter. Based on BusinessPerson ("BUSINESS", "PERSON", or any other value), the base WHERE clause is seeded with pl.BUSINESS_FLAG = :BusinessFlag or left as WHERE 1 = 1 so every subsequent condition can be appended with AND.
  3. Conditional joins are appended only for populated fields:
  4. TaxId → joins PROVIDER_TAX_ID as TIN on TIN.TAX_ID = :TaxIDNumber (hyphens stripped before binding).
  5. OtherId → joins provider_identity as ID on RTRIM(ID.IDENTITY_VALUE) = :OtherID.
  6. ServiceType → joins PROVIDER_SERVICE as SVC on a trimmed service-code match.
  7. MCOName → joins mco_provider as MP on MP.MCO_PLAN_ID = :MCOPlan.
  8. Each present join is appended to SQLPrimaryJoin and applied identically to both the primary query and, when a name search is active, the registered-name union query.
  9. Name search adds a UNION. When ProviderName is populated, the search also executes PRVSearchRegName, which joins PROVIDER_TAX_ID to resolve REGISTERED_NAME matches, and combines it with the primary result set via UNION.
  10. Remaining fields append straightforward WHERE predicates (mirrored onto the union query when active): ProviderType, Street (prefix LIKE), City, State, ZipCode, AreaCode, PhoneNum (digits only, prefix LIKE), and EligibleSearchFlag (pl.INELIGIBILITY_FLAG = 'N').
  11. The ZIP-numeric requirement (BR-002) is enforced by the caller populating a numeric ZIP before this step; clsProviderSearch itself binds whatever ZIP value is supplied without re-validating format.*
  12. Assemble and execute. The primary query, its join clause, and its WHERE clause are appended in order; if a name search is active, the registered-name union query is appended next; the combined statement is ordered by fullNAME ASC and executed via SqlHelper.ResultSetQuery.Execute(...) against PROVIDER_LOCATION joined to PROVIDER_ADDRESS and provider_type_code.
  13. Validation before this step is enforced by BR-001 (at least one criterion) and BR-002 (numeric ZIP), both raised by clsProviderSearch rule methods before searchForProvider() is expected to run.
  14. Materialize each row. For every row returned, a clsSearchResults is constructed with location seq num, primary/business flags, provider type description, resolved full name, formatted phone, and physical address columns; FormatSortName derives a "Last, First" sort key for individuals (business names sort as-is).
  15. Stop at 300 rows. The loop exits as soon as m_Providers.Count > 299, silently truncating any remaining matches — the comment in the source notes an attempted warning here "didnt work", so no truncation notice reaches the caller.*
  16. Return the collection. m_Providers.OrderBy(...) is called but its result is not reassigned back to m_Providers in this VB code, so the final ordering exposed to the caller is the SQL ORDER BY fullNAME ASC order, not the in-memory OrderBy.*

Process flow

flowchart TD
    Start([Caller populates clsSearchCriteria]) --> AtLeastOne{At least one criterion?}
    AtLeastOne -->|No| Blocked([Missing criteria<br>blocks search])
    AtLeastOne -->|Yes| ZipCheck{ZIP supplied?}
    ZipCheck -->|Yes, non-numeric| BlockedZip([Non-numeric ZIP<br>blocks search])
    ZipCheck -->|Numeric or blank| BuildJoins[Append conditional joins:<br>TIN, identity, service, MCO]
    BuildJoins --> BuildWhere[Append WHERE predicates<br>for populated fields]
    BuildWhere --> NameSearch{ProviderName supplied?}
    NameSearch -->|Yes| Union[UNION registered-name query]
    NameSearch -->|No| Execute
    Union --> Execute[Execute combined query,<br>ORDER BY fullNAME]
    Execute --> Loop[Materialize clsSearchResults rows]
    Loop --> Cap{Count > 299?}
    Cap -->|Yes| Truncate([Stop; remaining rows silently dropped])
    Cap -->|No| More{More rows?}
    More -->|Yes| Loop
    More -->|No| Done([Return clsProviders collection])

Failure and edge-case behavior

  • Searches with more than 300 matching rows are silently truncated; the code comments confirm an attempted warning mechanism was never wired up.*
  • The final OrderBy call on the returned collection does not reassign its result, so callers effectively receive SQL-order results rather than a client-side re-sort.*
  • The registered-name union path duplicates every join and filter condition against a second FROM clause; a mismatch between the two clause-building blocks would silently omit matches from one side of the union.*
  • searchForProvider() uses SqlHelper.ResultSetQuery directly rather than the BOF CRUD data portal, so there is no RowsAffected/concurrency handling — this operation is read-only.

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.Provider\clsProviderSearch.vb