SQL-002 Hazard group retrieve¶

Retrieves all HAZARD_GROUP rows whose effective/expiration date window contains the supplied effective date, used to populate clsHazardGroupCollection via the standard BOF retrieve lifecycle.

Classification¶

Field	Value
SQL ID	SQL-002
Operation type	Read
Execution method	Inline SQL, StringBuilder-composed via `BuildWhere()`
Database	Oracle
Schema	`[unqualified]**`
Command type	Text
Business object	`clsHazardGroupCollection`
Criteria property	`RetrieveSQL`
Lifecycle method	`BeforeRetrieve` / `AfterRetrieve`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:EffectiveDate`	Input	Date (`MyCriteria.EffectiveDate`)	N/A	Effective date used to filter hazard groups whose `EFFECTIVE_DATE`/`EXPIRATION_DATE` window contains it.

Tables¶

Table	Access	Columns used
HAZARD_GROUP	SELECT	`HAZARD_GROUP_ID`, `HAZARD_GROUP_SHORT_DESCR`, `HAZARD_GROUP_LONG_DESCR`, `EFFECTIVE_DATE`, `EXPIRATION_DATE`, `CREATE_DATE`, `CREATE_USER`, `UPDATE_DATE`, `UPDATE_USER`, `NCCI_HAZARD_GROUP_CODE`

Sample SQL¶

SELECT
  H.HAZARD_GROUP_ID,
  H.HAZARD_GROUP_SHORT_DESCR,
  H.HAZARD_GROUP_LONG_DESCR,
  H.EFFECTIVE_DATE,
  H.EXPIRATION_DATE,
  H.CREATE_DATE,
  H.CREATE_USER,
  H.UPDATE_DATE,
  H.UPDATE_USER,
  H.NCCI_HAZARD_GROUP_CODE
FROM HAZARD_GROUP H
WHERE :EffectiveDate >= H.EFFECTIVE_DATE
  AND :EffectiveDate < NVL(H.EXPIRATION_DATE, :EffectiveDate + 1)

Reconstructed from String & concatenation for the SELECT list and BuildWhere() for the WHERE clause.

Operations and processes¶

Retrieve hazard groups effective on a date

Sources¶

SAIF.Business.Policy\clsHazardGroupCollection.vb