SAIF.Business.UnitStat database tables

This page catalogs the Oracle tables and code tables accessed by this library.

Table catalog

Table	Database	Schema/Link	CRUD	Purpose	Primary business object
CALCULATION_TYPE_CODE	Oracle	[unqualified]**	R	Stores calculation-type code values loaded into the extended mapping layer.	CodeMappings.clsCalculationTypeMap
CL_INJURY_CLAIM	Oracle	[unqualified]**	R	Supplies the denial-final flag used by non-compensable checks.	clsClaimUnit.IsDeniedFinal
US_AUDIT_CONDITION	Oracle	[unqualified]**	C/R/D	Stores current audit and edit conditions associated with policy units, exposures, and claims.	clsAuditCondition, clsAuditConditionCollection
US_AUDIT_CONDITION_HISTORY	Oracle	[unqualified]**	C/R/U	Stores historical audit-condition observations keyed by policy unit, claim, and description.	clsAuditConditionHistory, clsAuditConditionHistoryCollection
US_CLAIM_UNIT	Oracle	[unqualified]**	C/R/U/D	Stores the claim-to-policy-unit association and its reporting-state flags.	clsClaimUnit, clsClaimUnitCollection
US_COST_TYPE_CODE	Oracle	[unqualified]**	R	Stores cost-type code values loaded into the extended mapping layer.	CodeMappings.clsCostTypeMap
US_OFFSET_TYPE_CODE	Oracle	[unqualified]**	R	Stores offset-type code values loaded into the extended mapping layer.	CodeMappings.clsOffsetTypeMap
US_POLICY_UNIT	Oracle	[unqualified]**	C/R/U/D	Stores the policy-level reporting unit and its valuation, activity, correction, and retention flags.	clsPolicyUnit, clsPolicyUnitCollection, clsClaimUnit, clsAuditConditionCollection

CALCULATION_TYPE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Stores calculation-type code values loaded into the extended mapping layer.
Access pattern	direct SQL
CRUD operations	R
Key columns used	See SQL catalog for exact column usage.
Sequence	N/A
Related tables	See relationship diagram and SQL catalog.
Business objects	CodeMappings.clsCalculationTypeMap

Operations referencing this table

• Code map loading

SQL referencing this table

• SQL-017: CALCULATION_TYPE_CODE code-table load

---

CL_INJURY_CLAIM

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Supplies the denial-final flag used by non-compensable checks.
Access pattern	direct SQL
CRUD operations	R
Key columns used	See SQL catalog for exact column usage.
Sequence	N/A
Related tables	See relationship diagram and SQL catalog.
Business objects	clsClaimUnit.IsDeniedFinal

Operations referencing this table

• Denial and fraud eligibility

Business rules referencing this table

• BR-US-027: Denial-final checks and fully-fraudulent checks only apply to policy units effective on or after 2013-01-01

SQL referencing this table

• SQL-014: CL_INJURY_CLAIM denial-final lookup

---

US_AUDIT_CONDITION

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Stores current audit and edit conditions associated with policy units, exposures, and claims.
Access pattern	direct SQL
CRUD operations	C/R/D
Key columns used	See SQL catalog for exact column usage.
Sequence	US_AUDIT_CONDITION_SEQ
Related tables	See relationship diagram and SQL catalog.
Business objects	clsAuditCondition, clsAuditConditionCollection

Operations referencing this table

• Audit condition maintenance

Business rules referencing this table

• BR-US-004: Audit condition description must have non-zero length
• BR-US-024: A policy unit has errors when its audit condition collection contains conditions with non-null exposure summary seq num AND non-null claim ID

SQL referencing this table

• SQL-001: US_AUDIT_CONDITION insert
• SQL-005: US_AUDIT_CONDITION by sequence
• SQL-006: US_AUDIT_CONDITION all rows
• SQL-007: US_AUDIT_CONDITION by policy period
• SQL-008: US_AUDIT_CONDITION by policy unit
• SQL-009: US_AUDIT_CONDITION by update user
• SQL-030: US_AUDIT_CONDITION delete by edit type
• SQL-031: US_AUDIT_CONDITION delete by policy period
• SQL-032: US_AUDIT_CONDITION delete by policy period and edit type
• SQL-033: US_AUDIT_CONDITION delete by policy unit and edit type

---

US_AUDIT_CONDITION_HISTORY

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Stores historical audit-condition observations keyed by policy unit, claim, and description.
Access pattern	direct SQL
CRUD operations	C/R/U
Key columns used	See SQL catalog for exact column usage.
Sequence	N/A
Related tables	See relationship diagram and SQL catalog.
Business objects	clsAuditConditionHistory, clsAuditConditionHistoryCollection

Operations referencing this table

• Audit condition history tracking

Business rules referencing this table

• BR-US-005: Audit condition history description must have non-zero length
• BR-US-020: Database contention on update or delete is detected by checking RowsAffected <> 1

SQL referencing this table

• SQL-002: US_AUDIT_CONDITION_HISTORY insert
• SQL-010: US_AUDIT_CONDITION_HISTORY by composite key
• SQL-011: US_AUDIT_CONDITION_HISTORY collection by composite key
• SQL-026: US_AUDIT_CONDITION_HISTORY update last-update date
• SQL-027: US_AUDIT_CONDITION_HISTORY collection update

---

US_CLAIM_UNIT

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Stores the claim-to-policy-unit association and its reporting-state flags.
Access pattern	direct SQL
CRUD operations	C/R/U/D
Key columns used	See SQL catalog for exact column usage.
Sequence	N/A
Related tables	See relationship diagram and SQL catalog.
Business objects	clsClaimUnit, clsClaimUnitCollection

Operations referencing this table

• Claim unit maintenance
• Denial and fraud eligibility

Business rules referencing this table

• BR-US-002: Claim unit policy unit sequence number must be greater than zero
• BR-US-003: Claim unit claim ID must be greater than zero
• BR-US-018: ActiveUnitFlag for claim units defaults to 'Y'
• BR-US-019: A claim unit active lookup filters to ACTIVE_UNIT_FLAG = 'Y'
• BR-US-020: Database contention on update or delete is detected by checking RowsAffected <> 1
• BR-US-027: Denial-final checks and fully-fraudulent checks only apply to policy units effective on or after 2013-01-01

SQL referencing this table

• SQL-003: US_CLAIM_UNIT insert
• SQL-012: US_CLAIM_UNIT active row by claim
• SQL-013: US_CLAIM_UNIT row by claim and policy unit
• SQL-015: US_CLAIM_UNIT / US_POLICY_UNIT fraud flag eligibility lookup
• SQL-016: US_CLAIM_UNIT collection by policy unit
• SQL-028: US_CLAIM_UNIT update
• SQL-034: US_CLAIM_UNIT delete

---

US_COST_TYPE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Stores cost-type code values loaded into the extended mapping layer.
Access pattern	direct SQL
CRUD operations	R
Key columns used	See SQL catalog for exact column usage.
Sequence	N/A
Related tables	See relationship diagram and SQL catalog.
Business objects	CodeMappings.clsCostTypeMap

Operations referencing this table

• Code map loading

SQL referencing this table

• SQL-018: US_COST_TYPE_CODE code-table load

---

US_OFFSET_TYPE_CODE

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Stores offset-type code values loaded into the extended mapping layer.
Access pattern	direct SQL
CRUD operations	R
Key columns used	See SQL catalog for exact column usage.
Sequence	N/A
Related tables	See relationship diagram and SQL catalog.
Business objects	CodeMappings.clsOffsetTypeMap

Operations referencing this table

• Code map loading

SQL referencing this table

• SQL-019: US_OFFSET_TYPE_CODE code-table load

---

US_POLICY_UNIT

Field	Value
Database	Oracle
Schema/Link	[unqualified]**
Purpose	Stores the policy-level reporting unit and its valuation, activity, correction, and retention flags.
Access pattern	direct SQL
CRUD operations	C/R/U/D
Key columns used	See SQL catalog for exact column usage.
Sequence	US_POLICY_UNIT_SEQ
Related tables	See relationship diagram and SQL catalog.
Business objects	clsPolicyUnit, clsPolicyUnitCollection, clsClaimUnit, clsAuditConditionCollection

Operations referencing this table

• Policy unit CRUD
• Denial and fraud eligibility
• Audit condition maintenance

Business rules referencing this table

• BR-US-001: Policy unit sequence number must be greater than zero
• BR-US-006: A policy unit retrieved by natural key must be active (ACTIVE_UNIT_FLAG = 'Y')
• BR-US-007: A policy unit retrieved by injury date must be active
• BR-US-012: Activating a policy unit that has been previously reported must also set correction flags
• BR-US-013: Non-compensable rules apply only to policy units effective on or after 2013-01-01
• BR-US-014: Active unit flag defaults to 'Y' when not set
• BR-US-015: LossEditFlag and ExposureEditFlag default to 'Y' when not set
• BR-US-016: Correction flags (UnitCorrectionFlag, ExposureCorrectionFlag, LossCorrectionFlag) default to 'N'
• BR-US-017: HistoricalFlag defaults to 'N' when not set
• BR-US-020: Database contention on update or delete is detected by checking RowsAffected <> 1

SQL referencing this table

• SQL-004: US_POLICY_UNIT insert
• SQL-007: US_AUDIT_CONDITION by policy period
• SQL-015: US_CLAIM_UNIT / US_POLICY_UNIT fraud flag eligibility lookup
• SQL-020: US_POLICY_UNIT active row by retrieve date
• SQL-021: US_POLICY_UNIT active row by natural key
• SQL-022: US_POLICY_UNIT row by sequence
• SQL-023: US_POLICY_UNIT rows requiring loss edits
• SQL-024: US_POLICY_UNIT collection by policy
• SQL-025: US_POLICY_UNIT collection by policy period
• SQL-029: US_POLICY_UNIT update
• SQL-031: US_AUDIT_CONDITION delete by policy period
• SQL-032: US_AUDIT_CONDITION delete by policy period and edit type
• SQL-035: US_POLICY_UNIT delete

---

Table relationship diagram

The following diagram shows the discoverable relationships across unit-stat tables and claim lookups.

erDiagram
    US_POLICY_UNIT ||--o{ US_CLAIM_UNIT : "US_POLICY_UNIT_SEQ_NUM"
    US_POLICY_UNIT ||--o{ US_AUDIT_CONDITION : "US_POLICY_UNIT_SEQ_NUM"
    US_POLICY_UNIT ||--o{ US_AUDIT_CONDITION_HISTORY : "US_POLICY_UNIT_SEQ_NUM"
    US_CLAIM_UNIT }o--|| CL_INJURY_CLAIM : "CLAIM_ID"
    US_CLAIM_UNIT ||--o{ US_AUDIT_CONDITION : "CLAIM_ID"
    US_CLAIM_UNIT ||--o{ US_AUDIT_CONDITION_HISTORY : "CLAIM_ID"

Related documentation

• Index
• Overview
• Architecture
• Dependencies
• Operations and processes
• Business rules
• SQL catalog
• API reference
• Configuration

Note: Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

• Business\SAIF.Business.UnitStat\App.config
• Business\SAIF.Business.UnitStat\clsAuditCondition.vb
• Business\SAIF.Business.UnitStat\clsAuditConditionCollection.vb
• Business\SAIF.Business.UnitStat\clsAuditConditionHistory.vb
• Business\SAIF.Business.UnitStat\clsAuditConditionHistoryCollection.vb
• Business\SAIF.Business.UnitStat\clsClaimUnit.vb
• Business\SAIF.Business.UnitStat\clsClaimUnitCollection.vb
• Business\SAIF.Business.UnitStat\clsPolicyUnit.vb
• Business\SAIF.Business.UnitStat\clsPolicyUnitCollection.vb
• Business\SAIF.Business.UnitStat\mdlCodeMaps.vb