SQL-PRV-01 Provider by provider ID

Retrieves a single PROVIDER record joined to its primary PROVIDER_TAX_ID record, using the provider's surrogate key. This is the primary lookup used by clsProvider.Retrieve.

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
:ProviderID	Input	Decimal	N/A	PROVIDER.PROVIDER_SEQ_NUM

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 provider 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.PROVIDER_SEQ_NUM = :ProviderID

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