SQL-002 Hazard group retrieve

Retrieves HAZARD_GROUP rows in force on a supplied effective date, reconstructed from Criteria.RetrieveSQL string concatenation plus the BuildWhere() StringBuilder fragment. This is the library's only statement routed through the BOF business-object data portal (BR-POL-001).

Classification

Field	Value
SQL ID	SQL-002
Operation type	Read
Execution method	Inline SQL (string concatenation + StringBuilder WHERE)
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 (Oracle DATE)	N/A	Effective date used twice in the predicate: lower bound against H.EFFECTIVE_DATE and upper bound against NVL(H.EXPIRATION_DATE, :EffectiveDate + 1). Bound once via DataRequest.AddParm("EffectiveDate", MyCriteria.EffectiveDate).

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)

Operations and processes

• OP-01 Retrieve hazard groups effective on a date (see Operations and processes)
• OP-02 Hydrate a hazard group from a reader (see Operations and processes)

Business rules

• BR-POL-001: Hazard group effective-dating
• BR-POL-002: Hazard group data is read-only

Sources

• SAIF.Business.Policy/clsHazardGroupCollection.vb
• SAIF.Business.Policy/clsHazardGroup.vb