SQL-003: Retrieve exemptions by policy

Retrieve corporate officer exemptions for one policy.

Classification

Field	Value
SQL ID	SQL-003
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsCorporateOfficerExemptionCollection
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyNumber	Input	Decimal (NUMBER)	N/A	Policy whose corporate officer exemptions are listed.

Tables

Table	Access	Columns used
CORP_OFFICER_EXEMPTION	SELECT	POLICY_NUM, exemption columns reused from SQL-002

Sample SQL

SELECT
  CORP_OFFICER_EXEMPTION.CORP_OFF_SEQ_NUM as CorporateOfficerExempSeqNum,
  CORP_OFFICER_EXEMPTION.POLICY_NUM as PolicyNumber,
  CORP_OFFICER_EXEMPTION.EXEMPT_REASON_CODE as ExemptionReasonCode,
  CORP_OFFICER_EXEMPTION.EFF_DATE as EffectiveDate,
  CORP_OFFICER_EXEMPTION.EXP_DATE as ExpirationDate,
  CORP_OFFICER_EXEMPTION.AFFILIATE_SSN as AffiliateSSN,
  CORP_OFFICER_EXEMPTION.CREATE_DATE as CreateDate,
  CORP_OFFICER_EXEMPTION.CREATE_USER as CreateUser,
  CORP_OFFICER_EXEMPTION.UPDATE_DATE as UpdateDate,
  CORP_OFFICER_EXEMPTION.UPDATE_USER as UpdateUser,
  CORP_OFFICER_EXEMPTION.ENDORSE_SEQ_NUM as EndorsementSeqNum
FROM CORP_OFFICER_EXEMPTION
WHERE
  CORP_OFFICER_EXEMPTION.POLICY_NUM = :PolicyNumber

Operations and processes

• Retrieve corporate officer exemptions for a policy

Business rules

• BR-AFF-013: Affiliate SSN identifies single-officer exemptions
• BR-AFF-018: The library exposes retrieve-only data access

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.Affiliate\clsCorporateOfficerExemptionCollection.vb