SQL-022: Retrieve clsManualClassBaseCollection data

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

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:RateFileSeqNum	Input	Not specified	not specified	Bound parameter.

Tables

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

Sample SQL

Select
  MANUAL_CLASS_BASE.AUDIT_ADDITIONAL_CLASS_FLAG as AuditAdditionalClassFlag,
  MANUAL_CLASS_BASE.BENEFIT_JURISDICTION_CODE as BenefitJurisdictionCode,
  MANUAL_CLASS_BASE.CLAIM_FREQUENCY as ClaimFrequency,
  MANUAL_CLASS_BASE.CONSTRUCTION_FLAG as ConstructionFlag,
  MANUAL_CLASS_BASE.CREATE_DATE as CreateDate,
  MANUAL_CLASS_BASE.CREATE_USER_NAME as CreateUserName,
  MANUAL_CLASS_BASE.DISEASE_RATIO as DiseaseRatio,
  MANUAL_CLASS_BASE.EMOD_RATABLE_FLAG as EmodRatableFlag,
  MANUAL_CLASS_BASE.EXPECTED_LOSS_RATE_1 as ExpectedLossRate1,
  MANUAL_CLASS_BASE.EXPECTED_LOSS_RATE_2 as ExpectedLossRate2,
  MANUAL_CLASS_BASE.EXPOSURE_BASE_CODE as ExposureBaseCode,
  MANUAL_CLASS_BASE.HAZARD_GROUP as HazardGroup,
  MANUAL_CLASS_BASE.INDUSTRY_GROUP_CODE as IndustryGroupCode,
  MANUAL_CLASS_BASE.LOSS_CONSTANT as LossConstant,
  MANUAL_CLASS_BASE.MANUAL_CLASS_DESCR as ManualClassDescr,
  MANUAL_CLASS_BASE.MANUAL_CLASS_NUM as ManualClassNum,
  MANUAL_CLASS_BASE.MANUAL_CLASS_RATE as ManualClassRate,
  MANUAL_CLASS_BASE.MANUAL_CLASS_RATE_EFF_DATE as ManualClassRateEffDate,
  MANUAL_CLASS_BASE.MANUAL_CLASS_RATE_EXP_DATE as ManualClassRateExpDate,
  MANUAL_CLASS_BASE.MANUAL_CLASS_TYPE_CODE as ManualClassTypeCode,
  MANUAL_CLASS_BASE.MIN_PREMIUM as MinPremium,
  MANUAL_CLASS_BASE.NCCI_LOSS_COST as NcciLossCost,
  MANUAL_CLASS_BASE.NON_RATEABLE_ELEMENT_FLAG as NonRateableElementFlag,
  MANUAL_CLASS_BASE.PAY_INCL_ANOTHER_CLASS_FLAG as PayInclAnotherClassFlag,
  MANUAL_CLASS_BASE.RATE_FILE_SEQ_NUM as RateFileSeqNum,
  MANUAL_CLASS_BASE.RATE_TYPE_CODE as RateTypeCode,
  MANUAL_CLASS_BASE.REPLACEMENT_MANUAL_CLASS_NUM as ReplacementManualClassNum,
  MANUAL_CLASS_BASE.STAT_CREDIBILITY as StatCredibility,
  MANUAL_CLASS_BASE.UPDATE_DATE as UpdateDate,
  MANUAL_CLASS_BASE.time_loss_frequency_num as TimeLossFrequency,
  MANUAL_CLASS_BASE.UPDATE_USER_NAME as UpdateUserName,
  DECODE(mcdIsDefault.manual_class_num, NULL, 'N', 'Y') as IsDefaultClassFlag,
  NVL(mcdismunicipal.MUNICIPAL_CLASS_FLAG, 'N') as IsMunicipalClassFlag
from
  MANUAL_CLASS_BASE
  join manual_class_detail mcd on (
    Substr(MANUAL_CLASS_BASE.MANUAL_CLASS_NUM,1,4) = mcd.MANUAL_CLASS_NUM
    AND Substr(MANUAL_CLASS_BASE.MANUAL_CLASS_NUM,5,2) = mcd.default_usage_code)
Where
  MANUAL_CLASS_BASE.RATE_FILE_SEQ_NUM = :RateFileSeqNum
  AND MANUAL_CLASS_BASE.MANUAL_CLASS_RATE_EFF_DATE <>
    NVL(MANUAL_CLASS_BASE.MANUAL_CLASS_RATE_EXP_DATE,
      MANUAL_CLASS_BASE.MANUAL_CLASS_RATE_EFF_DATE + 1)

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\clsManualClassBaseCollection.vb:471-508