SQL-015: US_CLAIM_UNIT / US_POLICY_UNIT fraud flag eligibility lookup

This SQL statement implements the US_CLAIM_UNIT / US_POLICY_UNIT fraud flag eligibility lookup data-access path for clsClaimUnit.IsFullyFraudulent in SAIF.Business.UnitStat. It participates in the read lifecycle handled by IsFullyFraudulent getter.

Classification

Field	Value
SQL ID	SQL-015
Operation type	Read
Execution method	Inline
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	clsClaimUnit.IsFullyFraudulent
Criteria property	N/A (direct scalar SQL)
Lifecycle method	IsFullyFraudulent getter

Parameters

Parameter	Direction	Type	Max length	Description
:FullyFraudulentFlag	Input	String	1	Caller-supplied fraud-conviction flag projected back from the query.
:ClaimId	Input	Decimal	N/A	Claim identifier bound from criteria.
:NonCompensableStartDate	Input	Date	N/A	Cutoff date for 2013 non-compensable claim rules.

Tables

Table	Access	Columns used
US_CLAIM_UNIT	SELECT / JOIN	See sample SQL
US_POLICY_UNIT	SELECT / JOIN	See sample SQL

Sample SQL

SELECT :FullyFraudulentFlag

FROM us_claim_unit cu

JOIN us_policy_unit pu

  ON cu.us_policy_unit_seq_num = pu.us_policy_unit_seq_num

 AND pu.active_unit_flag = 'Y'

 AND pu.historical_flag = 'N'

 AND pu.unit_eff_date >= :NonCompensableStartDate

WHERE cu.claim_id = :ClaimId

Operations and processes

• Denial and fraud eligibility

Business rules

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

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

Sources

• Business\SAIF.Business.UnitStat\clsClaimUnit.vb