SQL-PRV-01 Provider by primary tax ID

Retrieves a single PROVIDER record joined to its primary PROVIDER_TAX_ID record, using the provider's primary federal tax ID as the lookup key instead of the surrogate provider ID.

Classification

Field	Value
SQL ID	SQL-PRV-01
Operation type	Read
Execution method	Inline SQL (BOF criteria constant)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsProvider
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:PrimaryTaxID	Input	String	9	PROVIDER.PRIMARY_TAX_ID (9-char federal tax ID)

Tables

Table	Access	Columns used
PROVIDER	SELECT	PRIMARY_TAX_ID, PROVIDER_SEQ_NUM, UPDATE_DATE, UPDATE_NUM, UPDATE_USER
PROVIDER_TAX_ID	JOIN (LEFT OUTER)	TAX_ID, TAX_ID_DISPLAY_DESCR, REGISTERED_NAME

Sample SQL

-- Provider by primary tax ID
SELECT
  PRV.PRIMARY_TAX_ID        AS PrimaryTaxID,
  PRV.PROVIDER_SEQ_NUM      AS ProviderID,
  PRV.UPDATE_DATE           AS UpdateDate,
  PRV.UPDATE_NUM            AS UpdateNum,
  PRV.UPDATE_USER           AS UpdateUser,
  TAX.TAX_ID_DISPLAY_DESCR  AS FormattedPrimaryTaxID,
  TAX.REGISTERED_NAME       AS RegisteredName
FROM PROVIDER PRV
  LEFT OUTER JOIN PROVIDER_TAX_ID TAX
    ON TAX.TAX_ID = PRV.PRIMARY_TAX_ID
WHERE PRV.PRIMARY_TAX_ID = :PrimaryTaxID

Operations and processes

• Retrieve provider by ID or tax ID

Business rules

• BR-PRV-001: Primary tax ID format
• BR-PRV-004: Provider retrieved read-only
• BR-DATA-001: Read-only SQL properties

Sources

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