SQL-043: Retrieve Provider Address

Retrieves provider address data from Oracle.

Classification

Field	Value
SQL ID	SQL-043
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsProviderAddress
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:ProviderAddressSeqNum	Input	Decimal	N/A	Value from MyCriteria.ProviderAddressSeqNum

Tables

Table	Access	Columns used
PROVIDER_ADDRESS	SELECT	See business-object criteria for key columns.

Sample SQL

Select PROVIDER_ADDRESS.PROVIDER_ADDRESS_SEQ_NUM as ProviderAddressSeqNum, PROVIDER_ADDRESS.PROVIDER_LOCATION_SEQ_NUM as ProviderLocationSeqNum, PROVIDER_ADDRESS.PROVIDER_ADDRESS_CODE as ProviderAddressCode, PROVIDER_ADDRESS.EFF_DATE as EffDate, PROVIDER_ADDRESS.EXP_DATE as ExpDate, PROVIDER_ADDRESS.ADDRESS_LINE1 as AddressLine1, PROVIDER_ADDRESS.ADDRESS_LINE2 as AddressLine2, PROVIDER_ADDRESS.ADDRESS_LINE3 as AddressLine3, PROVIDER_ADDRESS.CITY_NAME as CityName, PROVIDER_ADDRESS.STATE_CODE as StateCode, PROVIDER_ADDRESS.ZIP_CODE as ZipCode, PROVIDER_ADDRESS.ZIP_CODE_EXT as ZipCodeExt, PROVIDER_ADDRESS.COUNTRY_CODE as CountryCode, PROVIDER_ADDRESS.FOREIGN_ADDRESS_FLAG as ForeignAddressFlag, PROVIDER_ADDRESS.UPDATE_USER as UpdateUser, PROVIDER_ADDRESS.UPDATE_NUM as UpdateNum, PROVIDER_ADDRESS.UPDATE_DATE as UpdateDate From PROVIDER_ADDRESS  Where PROVIDER_ADDRESS.PROVIDER_ADDRESS_SEQ_NUM = :ProviderAddressSeqNum

Operations and processes

• Maintain provider address

Business rules

• BR-007: Address effective periods must not overlap
• BR-008: Domestic addresses require city, state, and ZIP data

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Business\SAIF.Business.Provider\clsProviderAddress.vb