SQL-009 Officer of notice lookup¶

Retrieve the current officer-of-notice name for a policy.

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:PolicyNum`	Input	Decimal	N/A	Policy number

Tables¶

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

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 addresses

Business rules¶

BR-VP-010: Demo users see masked data

Sources¶

SAIF.Business.View.Policy/clsPolicyAddress.vb