SQL-035: Retrieve clsRateTier data

This document records the SQL used by clsRateTier in SAIF.Business.Policy.Rating.

Classification

Field	Value
SQL ID	SQL-035
Operation type	Read
Execution method	Data portal — criteria SQL (dynamic predicate builder)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsRateTier
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

Parameter	Direction	Type	Max length	Description
:MarketTypeCode	Input	Not specified	not specified	Bound parameter.
:EffDate	Input	Not specified	not specified	Bound parameter.
:IhcFlag	Input	Not specified	not specified	Bound parameter.
:McoFlag	Input	Not specified	not specified	Bound parameter.

Tables

Table	Access	Columns used
RISK_TYPE_CODE	SELECT	See sample SQL and source for column usage.

Sample SQL

Select
  RISK_TYPE_CODE.CODE as Code,
  RISK_TYPE_CODE.DEFAULT_FLAG as DefaultFlag,
  RISK_TYPE_CODE.EFF_DATE as EffDate,
  RISK_TYPE_CODE.EXP_DATE as ExpDate,
  RISK_TYPE_CODE.IHC_FLAG as IhcFlag,
  RISK_TYPE_CODE.MARKET_TYPE_CODE as MarketTypeCode,
  RISK_TYPE_CODE.MCO_FLAG as McoFlag,
  RISK_TYPE_CODE.TIER_RANKING as TierRanking
From
  RISK_TYPE_CODE
Where
  RISK_TYPE_CODE.MARKET_TYPE_CODE = :MarketTypeCode
  AND RISK_TYPE_CODE.EFF_DATE <= :EffDate
  AND (RISK_TYPE_CODE.EXP_DATE IS NULL OR RISK_TYPE_CODE.EXP_DATE > :EffDate)
  AND RISK_TYPE_CODE.IHC_FLAG = :IhcFlag
  AND RISK_TYPE_CODE.MCO_FLAG = :McoFlag
  AND RISK_TYPE_CODE.DEFAULT_FLAG = 'Y'

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.Rating\clsRateTier.vb:455-463