SQL-007 Max prem hist set id¶

Retrieves the highest prem_hist_set_id for a policy/period restricted to premium-history types '1' and '3', used by PolicyInformation.MaxPremiumHistorySetId (BR-POL-007).

Classification¶

Field	Value
SQL ID	SQL-007
Operation type	Read
Execution method	Inline SQL (`CommandType.Text`, `DataRequestType.Scalar`)
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	N/A (Application layer)
Criteria property	N/A
Lifecycle method	N/A
Application class	`PolicyInformation` (`MaxPremiumHistorySetId` property getter)

Parameters¶

Parameter	Direction	Type	Max length	Description
`:PolicyNumber`	Input	Decimal (Oracle `NUMBER`)	N/A	Policy number whose maximum premium-history set id is requested.
`:PolicyPeriodEffectiveDate`	Input	Date (Oracle `DATE`)	N/A	Policy period effective date scoping the premium-history summary rows.

Tables¶

Table	Access	Columns used
PREM_HIST_SUMMARY	SELECT	`PREM_HIST_SET_ID`, `POLICY_NUM`, `POLICY_PERIOD_EFF_DATE`, `PREM_HIST_TYPE_CODE`

Sample SQL¶

SELECT
  MAX(prem_hist_set_id)
FROM prem_hist_summary phs
WHERE phs.policy_num = :PolicyNumber
  AND phs.policy_period_eff_date = :PolicyPeriodEffectiveDate
  AND phs.prem_hist_type_code IN ('1', '3')

A NULL result (no matching rows) is normalized to 0 via an explicit IsDBNull guard.

Operations and processes¶

OP-08 Latest premium history set id (see Operations and processes)

Business rules¶

BR-POL-007: Current premium history set selection

Sources¶

SAIF.Business.Policy/PolicyInformation.vb