SQL-012: Officer-of-notice name lookup¶

clsPolicyAddress uses this statement to resolve the officer of notice for a policy — the affiliated individual designated to receive legal notice. The name parts are assembled and trimmed in SQL, and cancelled affiliations are excluded.

Classification¶

Field	Value
SQL ID	SQL-012
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	`[unqualified]`**
Command type	Text
Business object	`clsPolicyAddress`
Criteria property	`RetrieveOfficerOfNoticeSql`
Lifecycle method	`OfficerOfNotice` property getter

Parameters¶

Parameter	Direction	Type	Max length	Description
`:PolicyNum`	Input	Decimal	not specified	Policy number whose active officer-of-notice affiliate is requested.

Tables¶

Table	Access	Columns used
BUSINESS_AFFILIATE	SELECT	POLICY_NUM, OFFICER_OF_NOTICE_FLAG, AFFILIATE_CANCEL_DATE, AFFILIATE_SSN
AFFILIATE	JOIN	AFFILIATE_SSN, AFFILIATE_FIRST_NAME, AFFILIATE_INITIAL, AFFILIATE_LAST_NAME

Sample SQL¶

SELECT TRIM(REPLACE(
  NVL(aff.AFFILIATE_FIRST_NAME,'') || ' ' ||
  NVL(aff.AFFILIATE_INITIAL,'') || ' ' ||
  NVL(aff.AFFILIATE_LAST_NAME,''),'  ',' ') ) OON
FROM BUSINESS_AFFILIATE ba, AFFILIATE aff
WHERE policy_num = :PolicyNum
  AND officer_of_notice_flag = 'Y'
  AND NVL(ba.AFFILIATE_CANCEL_DATE,SYSDATE + 1) > SYSDATE
  AND aff.affiliate_ssn = ba.AFFILIATE_SSN

Operations and processes¶

Retrieve supporting policy aggregates

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.View.Policy/clsPolicyAddress.vb