SQL-075: Read Pa Zip Code Data

This statement supports the premium-audit library by read Pa Zip Code Data.

Classification

Field	Value
SQL ID	SQL-075
Operation type	Read
Execution method	IDataRequest.SQLStatement (CommandType.Text)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsZipCodeCollection
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
—	—	—	—	No bound parameters detected in source.

Tables

Table	Access	Columns used
PA_ZIP_CODE	JOIN	See sample SQL.
COUNTY	JOIN	See sample SQL.
OFFICE_CODE	JOIN	See sample SQL.

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)

Operations and processes

• Assign renewal (batch) audits to auditors
• Assign off-cycle audits to auditors

Business rules

• BR-012
• BR-068

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.Policy.Audit.Premium/clsZipCodeCollection.vb:466