SQL-007: Load preferred-worker eligibility

Load the earliest preferred-worker eligibility row covering the association dates.

Classification

Field	Value
SQL ID	SQL-007
Operation type	Read
Execution method	Inline SQL
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	N/A (Application layer)
Criteria property	N/A
Lifecycle method	N/A
Application class	clsPolicyPreferredWorker

Parameters

Parameter	Direction	Type	Max length	Description
:AffiliateID	Input	String (AFFILIATE_SSN)	AffiliateID.Length	Affiliate SSN whose preferred-worker eligibility is looked up.
:EffectiveDate	Input	Date (DATE)	N/A	Association effective date that must fall inside the eligibility window.
:CancelDate	Input	clsSmartDate → DATE	N/A	Association cancel date, defaulted to 12/31/9999 when null.

Tables

Table	Access	Columns used
PREFERRED_WORKER	SELECT	PREFERRED_WORKER_ID, PW_CONTRACT_TYPE_CODE, PW_ELIGIBILITY_EFF_DATE, PW_ELIGIBILITY_EXP_DATE, AFFILIATE_SSN

Sample SQL

SELECT
  PreferredWorkerID,
  ContractTypeCode,
  EligibilityEffectiveDate,
  EligibilityExpirationDate
FROM (
  SELECT
    pw.preferred_worker_id PreferredWorkerID,
    pw.pw_contract_type_code ContractTypeCode,
    pw.pw_eligibility_eff_date EligibilityEffectiveDate,
    pw.pw_eligibility_exp_date EligibilityExpirationDate
  FROM preferred_worker pw
  WHERE pw.affiliate_ssn = :AffiliateID
    AND :EffectiveDate between
      pw.pw_eligibility_eff_date and nvl(pw.pw_eligibility_exp_date, :EffectiveDate)
    AND nvl(:CancelDate, to_date('12/31/9999','mm/dd/yyyy')) between
      pw.pw_eligibility_eff_date and nvl(pw.pw_eligibility_exp_date, :CancelDate)
  ORDER BY pw.pw_eligibility_eff_date
)
WHERE rownum = 1

Operations and processes

• Load preferred-worker eligibility

Business rules

• BR-AFF-010: Eligibility rows must cover both effective and cancel dates
• BR-AFF-011: Earliest qualifying eligibility row wins
• BR-AFF-012: Missing eligibility returns sentinel values

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