SQL-006: Retrieve preferred worker by key

Retrieve one preferred-worker association by policy, affiliate, and effective date.

Classification

Field	Value
SQL ID	SQL-006
Operation type	Read
Execution method	Inline SQL composed from shared constants
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsPolicyPreferredWorker
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:AffiliateID	Input	String (AFFILIATE_SSN)	11	Affiliate SSN of the preferred worker.
:PolicyNumber	Input	Decimal (NUMBER)	N/A	Policy number of the association.
:EffectiveDate	Input	Date (DATE)	N/A	Association effective date.

Tables

Table	Access	Columns used
BUSINESS_AFFILIATE	SELECT/JOIN	association identity and shared affiliate columns
AFFILIATE	JOIN	name, birth date, audit columns

Sample SQL

SELECT
  BA.AFFILIATE_SSN as AffiliateID,
  BA.POLICY_NUM as PolicyNumber,
  BA.BUS_SEQ_NUM as BusinessSequenceNumber,
  BA.AFFILIATE_EFF_DATE as EffectiveDate,
  BA.AFFILIATE_CANCEL_DATE as CancelDate,
  BA.AFFILIATE_TYPE_CODE as AffiliateTypeCode,
  BA.CREATE_DATE as CreateDate,
  BA.CREATE_USER as CreateUser,
  BA.UPDATE_DATE as UpdateDate,
  BA.UPDATE_USER as UpdateUser,
  BA.INSURED_CODE as InsuredCode,
  BA.AFFILIATE_DUTIES as Duties,
  BA.REMUNERATION as Remuneration,
  BA.OWNERSHIP_FLAG as OwnershipFlag,
  BA.PERCENT_OWNERSHIP as OwnershipPercent,
  BA.DIRECTOR_FLAG as DirectorFlag,
  BA.AFFILIATE_TITLE_CODE as TitleCode,
  BA.PERSONAL_ELECTION_NUM as PersonalElectionNumber,
  BA.OFFICER_OF_NOTICE_FLAG as OfficerOfNoticeFlag,
  A.AFFILIATE_FIRST_NAME as FirstName,
  A.AFFILIATE_INITIAL as MiddleInitial,
  A.AFFILIATE_LAST_NAME as LastName,
  A.AFFILIATE_SUFFIX as NameSuffix,
  A.AFFILIATE_DOB as BirthDate,
  A.CREATE_DATE as CreateDate,
  A.CREATE_USER as CreateUser,
  A.UPDATE_DATE as UpdateDate,
  A.UPDATE_USER as UpdateUser
FROM
  BUSINESS_AFFILIATE BA,
  AFFILIATE A
WHERE
  BA.AFFILIATE_TYPE_CODE = '2'
  AND BA.AFFILIATE_SSN = A.AFFILIATE_SSN
  AND BA.AFFILIATE_SSN = :AffiliateID
  AND BA.POLICY_NUM = :PolicyNumber
  AND BA.AFFILIATE_EFF_DATE = :EffectiveDate

Operations and processes

• Retrieve a policy preferred worker

Business rules

• BR-AFF-001: Missing-key retrieval returns a default object
• BR-AFF-004: Affiliate associations join to people by SSN
• BR-AFF-009: Preferred-worker queries require type code 2
• BR-AFF-017: Policy-affiliate identity is a three-part key
• BR-AFF-018: The library exposes retrieve-only data access
• BR-AFF-019: Flag columns convert through framework boolean helpers

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\clsPolicyPreferredWorker.vb