SQL-VP-007 Retrieve Officer of Notice contact for a policy

Resolves the "Officer of Notice" contact for a policy by joining the business-affiliate and affiliate tables, filtered to affiliate type 'OFN'. Skipped entirely for demo users (see BR-VP-011).

Classification

Field	Value
SQL ID	SQL-VP-007
Operation type	Read
Execution method	Inline SQL (StringBuilder-built, executed as Scalar)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsPolicyAddress
Criteria property	RetrieveOfficerOfNoticeSql (ad hoc method)
Lifecycle method	ad hoc method

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyNum	Input	Decimal	N/A	Policy number to look up the Officer of Notice for.

Tables

Table	Access	Columns used
BUSINESS_AFFILIATE	SELECT / JOIN	AFFILIATE_TYPE_CODE, AFFILIATE_SEQ_NUM, POLICY_NUM
AFFILIATE	SELECT / JOIN	AFFILIATE_SEQ_NUM, AFFILIATE_NAME, ADDRESS_LINE_1, ADDRESS_LINE_2, CITY, STATE_CODE, ZIP_CODE

Sample SQL

SELECT
  BUSINESS_AFFILIATE.AFFILIATE_TYPE_CODE,
  AFFILIATE.AFFILIATE_NAME,
  AFFILIATE.ADDRESS_LINE_1,
  AFFILIATE.ADDRESS_LINE_2,
  AFFILIATE.CITY,
  AFFILIATE.STATE_CODE,
  AFFILIATE.ZIP_CODE
FROM
  BUSINESS_AFFILIATE
  INNER JOIN AFFILIATE
    ON BUSINESS_AFFILIATE.AFFILIATE_SEQ_NUM = AFFILIATE.AFFILIATE_SEQ_NUM
WHERE
  BUSINESS_AFFILIATE.POLICY_NUM = :PolicyNum
  AND BUSINESS_AFFILIATE.AFFILIATE_TYPE_CODE = 'OFN'

Exact column list and join condition are inferred/approximate reconstructions of the StringBuilder-built query; actual column and literal values should be re-verified against the live RetrieveOfficerOfNoticeSql method body if higher precision is required.*

Business rules

• BR-VP-011: Demo-user address/contact data masking

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

• SAIF.Business.View.Policy\clsPolicyAddress.vb