SQL-PTAX-01 Provider tax IDs¶

Retrieves every PROVIDER_TAX_ID record for a given provider, including registered name, W-9 tracking dates, 1099 reporting flag, and the tax ID's mailing address fields.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:ProviderSeqNum`	Input	Decimal	N/A	`PROVIDER_TAX_ID.PROVIDER_SEQ_NUM`

Tables¶

Table	Access	Columns used
PROVIDER_TAX_ID	SELECT	`TAX_ID`, `TAX_ID_DISPLAY_DESCR`, `PROVIDER_SEQ_NUM`, `REGISTERED_NAME`, `REGISTERED_NAME_UPPER_CASE`, `EFF_DATE`, `EXP_DATE`, `W9_SENT_DATE`, `W9_RECEIVED_DATE`, `REPORT_1099_FLAG`, `CITY_NAME`, `ADDRESS_LINE1`-`4`, `STATE_CODE`, `ZIP_CODE`, `ZIP_CODE_EXT`, `COUNTRY_CODE`, `FOREIGN_ADDRESS_FLAG`

Sample SQL¶

-- Provider tax IDs
SELECT
  TAX_ID                              AS TaxID,
  TAX_ID_DISPLAY_DESCR                AS TaxIDDisplayDescr,
  PROVIDER_SEQ_NUM                    AS ProviderSeqNum,
  REGISTERED_NAME                     AS RegisteredName,
  REGISTERED_NAME_UPPER_CASE          AS RegisteredNameUpperCase,
  EFF_DATE                            AS EffDate,
  EXP_DATE                            AS ExpDate,
  W9_SENT_DATE                        AS W9SentDate,
  W9_RECEIVED_DATE                    AS W9ReceivedDate,
  REPORT_1099_FLAG                    AS Report1099Flag,
  CITY_NAME                           AS CityName,
  ADDRESS_LINE1                       AS AddressLine1,
  ADDRESS_LINE2                       AS AddressLine2,
  STATE_CODE                          AS StateCode,
  ZIP_CODE                            AS ZipCode,
  ZIP_CODE_EXT                        AS ZipCodeExt,
  ADDRESS_LINE3                       AS AddressLine3,
  PROVIDER_TAX_ID.ADDRESS_LINE4       AS AddressLine4,
  PROVIDER_TAX_ID.COUNTRY_CODE        AS CountryCode,
  FOREIGN_ADDRESS_FLAG                AS ForeignAddressFlag
FROM PROVIDER_TAX_ID
WHERE PROVIDER_SEQ_NUM = :ProviderSeqNum

Operations and processes¶

Load provider tax IDs

Business rules¶

BR-PRV-002: Payee address resolution

Sources¶

SAIF.Business.View.Provider/clsProviderTaxIDCollection.vb