SQL-002: Retrieve corporate officer exemption by sequence

Retrieve one corporate officer exemption by sequence number.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:CorporateOfficerExempSeqNum	Input	Decimal (NUMBER)	N/A	Corporate officer exemption sequence number.

Tables

Table	Access	Columns used
CORP_OFFICER_EXEMPTION	SELECT	CORP_OFF_SEQ_NUM, POLICY_NUM, EXEMPT_REASON_CODE, EFF_DATE, EXP_DATE, AFFILIATE_SSN, ENDORSE_SEQ_NUM, audit columns

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.CORP_OFF_SEQ_NUM = :CorporateOfficerExempSeqNum

Operations and processes

• Retrieve a corporate officer exemption
• Classify a corporate officer exemption
• Resolve a code description

Business rules

• BR-AFF-001: Missing-key retrieval returns a default object
• BR-AFF-013: Affiliate SSN identifies single-officer exemptions
• BR-AFF-014: IsActiveCorporateOfficer requires an expiration date
• BR-AFF-015: Corporate officer details are loaded only for single-officer exemptions
• BR-AFF-016: Code descriptions come from framework code collections
• 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\clsCorporateOfficerExemption.vb