SQL-037: Retrieve zip code mapping¶

Retrieves audit office code, county, and policy region data for a given zip code from PA_ZIP_CODE with outer joins to COUNTY and OFFICE_CODE. The NVL(z.audit_office_code, c.office_code) expression falls back to the county office when no specific audit office is configured.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:ZipCode`	Input	String	5	Zip code to resolve routing data for.

Tables¶

Table	Access	Columns used
PA_ZIP_CODE	SELECT	`ZIP_CODE`, `AUDIT_OFFICE_CODE`, `STATE_CODE`, `COUNTY_CODE`
COUNTY	LEFT OUTER JOIN	`COUNTY_CODE`, `COUNTY_NAME`, `OFFICE_CODE`
OFFICE_CODE	LEFT OUTER JOIN (×2)	`CODE`, `CODE_DESCR`, `POLICY_REGION_CODE`

Sample SQL¶

SELECT
    z.zip_code AS zipcode,
    NVL(z.audit_office_code, c.office_code) AS auditofficecode,
    ao.code_descr AS auditofficename,
    z.state_code AS statecode,
    z.county_code AS countycode,
    c.county_name AS countyname,
    c.office_code AS officecode,
    o.code_descr AS officename,
    o.policy_region_code AS policyregioncode
FROM pa_zip_code z
LEFT OUTER JOIN county c ON (z.county_code = c.county_code)
LEFT OUTER JOIN office_code o ON (c.office_code = o.code)
LEFT OUTER JOIN office_code ao ON (z.audit_office_code = ao.code)
WHERE zip_code = :ZipCode

Operations and processes¶

Zip code routing lookup

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources¶

Business\SAIF.Business.External.Users\clsZipCode.vb