SQL-088: Retrieve policy period collection

Retrieve policy period collection in the context of clsPolicyPeriodCollection.

Classification

Field	Value
SQL ID	SQL-088
Operation type	Read
Execution method	IDataRequest.SQLStatement (CommandType.Text)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsPolicyPeriodCollection
Criteria property	RetrieveSQL
Lifecycle method	BeforeRetrieve / AfterRetrieve

Parameters

This statement has no bind parameters.

Tables

Table	Access	Columns used
POLICY_PERIOD	SELECT	See sample SQL

Sample SQL

SELECT
  POLICY_PERIOD.POLICY_NUM as PolicyNumber,
  POLICY_PERIOD.POLICY_PERIOD_EFF_DATE as PolicyPeriodEffDate,
  POLICY_PERIOD.POLICY_PERIOD_EXP_DATE as PolicyPeriodExpDate,
  POLICY_PERIOD.ANNIVERSARY_RATE_DATE as AnniversaryRateDate,
  POLICY_PERIOD.RATING_EFFECTIVE_DATE as RatingEffectiveDate,
  POLICY_PERIOD.POLICY_PERIOD_STATUS_CODE as PolicyPeriodStatusCode,
  POLICY_PERIOD.RATING_RULE_MASTER_ID as RatingRuleMasterID,
  POLICY_PERIOD.CANCEL_DATE as CancelDate,
  POLICY_PERIOD.DEPOSIT_AMT as DepositAmt,
  POLICY_PERIOD.HOLD_RENEWAL_FLAG as HoldRenewalFlag,
  POLICY_PERIOD.RISK_TYPE_CODE as RiskTypeCode,
  POLICY_PERIOD.PLAN_CODE as PlanCode,
  POLICY_PERIOD.RENEWAL_AGENT_NUM as RenewalAgentNum,
  POLICY_PERIOD.POLICY_PERIOD_TYPE_CODE as PolicyPeriodTypeCode,
  POLICY_PERIOD.CREATE_DATE as CreateDate,
  POLICY_PERIOD.CREATE_USER as CreateUser,
  POLICY_PERIOD.UPDATE_DATE as UpdateDate,
  POLICY_PERIOD.UPDATE_USER as UpdateUser,
  POLICY_PERIOD.HOLD_CANCEL_USER as HoldCancelUser,
  POLICY_PERIOD.AOI_ACCOUNT_FLAG as AoiAccountFlag,
  POLICY_PERIOD.MARKET_TYPE_CODE as MarketTypeCode,
  POLICY_PERIOD.CLAIMS_REIMBURSEMENT_FLAG as ClaimsReimbursementFlag,
  POLICY_PERIOD.CLAIMS_REIMB_EFF_DATE as ClaimsReimbEffDate,
  POLICY_PERIOD.CLAIMS_REIMB_EXP_DATE as ClaimsReimbExpDate,
  POLICY_PERIOD.CONVERSION_FLAG as ConversionFlag,
  POLICY_PERIOD.PAY_RPT_ISSUE_FLAG as PayRptIssueFlag,
  POLICY_PERIOD.RENEWAL_PRINT_FLAG as RenewalPrintFlag,
  POLICY_PERIOD.ARD_EXTENSION_FLAG as ArdExtensionFlag,
  POLICY_PERIOD.PERPETUAL_MID_POLICY_ARD_FLAG as PerpetualMidPolicyArdFlag,
  POLICY_PERIOD.RED_EXTENSION_FLAG as RedExtensionFlag,
  POLICY_PERIOD.PERPETUAL_MID_POLICY_RED_FLAG as PerpetualMidPolicyRedFlag,
  POLICY_PERIOD.FINANCIAL_SYSTEM_CODE as FinancialSystemCode,
  POLICY_PERIOD.CANCELLED_PLCYPD_STATUS_CODE as CancelledPlcypdStatusCode,
  POLICY_PERIOD.PAYROLL_REPORT_FREQ_DATE as PayrollReportFreqDate,
  POLICY_PERIOD.PAYROLL_REPORT_FREQUENCY_CODE as PayrollReportFrequencyCode,
  POLICY_PERIOD.POLICY_RENEWAL_TRACK_CODE as PolicyRenewalTrackCode,
  POLICY_PERIOD.AUTO_PRICING_REQUIRED_FLAG as AutoPricingRequiredFlag,
  POLICY_PERIOD.EXP_DT_OVERRIDE_FLAG as ExpDTOverrideFlag,
  POLICY_PERIOD.PRIOR_PP_EXP_DATE as PriorPPExpDate,
  POLICY_PERIOD.PAYROLL_RPT_FREQ_REASON_CODE as PayrollRptFreqReasonCode,
  POLICY_PERIOD.FINANCE_PREMIUM as FinancePremium,
  POLICY_PERIOD.FINANCE_PREMIUM_FLAG as FinancePremiumFlag,
  POLICY_PERIOD.DEPOSIT_PERCENT as DepositPercentRequested,
  POLICY_PERIOD.DEPOSIT_REQUEST_DATE as DepositRequestDate,
  POLICY_PERIOD.DEPOSIT_REQUESTOR_USER_NAME as DepositRequestUser,
  POLICY_PERIOD.other_states_policy_num as OtherStatesPolNum,
  POLICY_PERIOD.other_states_pol_per_eff_date as OtherStatesPolPerEffDate,
  POLICY_PERIOD.other_states_pol_per_exp_date as OtherStatesPolPerExpDate,
  POLICY_PERIOD.SOURCE_SYSTEM_CODE as SourceSystem
FROM
  POLICY_PERIOD {0} {1}

Operations and processes

• Build a unified policy period timeline
• Configure dynamic period retrieval SQL
• Select the relevant period from a set

Business rules

• BR-004: The current period is the first period flagged
• BR-005: Experience-rating base periods are those whose begin date
• BR-010: A policy's period history is the union of
• BR-011: Association-based period retrieval uses a different predicate per
• BR-012: An association type outside the supported set is

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.Core\clsPolicyPeriodCollection.vb