Skip to content

SQL-007: clsCurrentLoss

This SQL artifact supports clsCurrentLoss by executing a read operation through Inline.

Classification

Field Value
SQL ID SQL-007
Operation type Read
Execution method Inline
Database Oracle
Schema [unqualified]**
Command type Text
Business object clsCurrentLoss
Criteria property GetCurrentClaimInfoSQL
Lifecycle method Cls Current Loss read

Parameters

Parameter Direction Type Max length Description
:claimID Input Number N/A Source value ClaimID

Tables

Table Access Columns used
us_policy_unit READ See sample SQL.
us_claim_unit READ See sample SQL.
cl_injury_claim READ See sample SQL.
cl_injury_source_code READ See sample SQL.
cl_injury_nature_code READ See sample SQL.
cl_body_part_code READ See sample SQL.
us_unit_report READ See sample SQL.
us_report_batch READ See sample SQL.
us_reported_loss READ See sample SQL.
us_coverage_combination READ See sample SQL.
us_catastrophic_claim READ See sample SQL.
us_catastrophe READ See sample SQL.

Sample SQL

SELECT ic.injury_date as InjuryDate, ic.saif_prov_payroll_class_code as ClassificationCode, sc.ncci_injury_source_code as NCCIInjurySourceCode, nc.ncci_injury_nature_code as NCCIInjuryNatureCode, bp.ncci_body_part_code as NCCIBodyPartCode, pk73304_loss_summary.get_ncci_claim_type_code(ic.claim_id, cov.partb_claim_id, ic.coverage_code) as ClaimTypeCode, pk73304_loss_summary.get_ncci_loss_act_code(ic.jurisdiction_code, ic.saif_prov_payroll_class_code) as LossActCode, pk73304_loss_summary.get_ncci_loss_type_code(ic.inj_occupational_disease_code) as LossTypeCode, CASE WHEN pk73304_loss_summary.report_non_compensable(ic.claim_id) = 'Y' THEN CASE WHEN ic.denied_final_flag = 'Y' THEN pk73304_loss_summary.get_ncci_settlement_type_code('USTAT', ic.claim_id, cov.partb_claim_id) WHEN rl.settlement_type_code = '05' THEN pk73304_loss_summary.get_ncci_settlement_type_code('USTAT', ic.claim_id, cov.partb_claim_id) ELSE rl.settlement_type_code END ELSE rl.settlement_type_code END as SettlementTypeCode, CASE WHEN pk73304_loss_summary.report_non_compensable(ic.claim_id) = 'Y' THEN pk73304_loss_summary.get_ncci_fraud_type_code(ic.claim_id, cov.partb_claim_id) ELSE rl.fraudulent_claim_code END as FraudClaimCode, NVL(cat.catastrophe_code, '00') as CatastropheNumber, ic.worker_occupation_descr as WorkerOccupationDescription FROM us_policy_unit pu JOIN us_claim_unit cu ON cu.us_policy_unit_seq_num = pu.us_policy_unit_seq_num AND cu.active_unit_flag = 'Y' JOIN cl_injury_claim ic ON ic.claim_id = cu.claim_id AND ic.claim_status_code <> 'V' JOIN cl_injury_source_code sc ON sc.code = ic.injury_source_code JOIN cl_injury_nature_code nc ON nc.code = ic.primary_injury_nature_code JOIN cl_body_part_code bp ON bp.code = ic.primary_body_part_code JOIN us_unit_report ur ON ur.us_policy_unit_seq_num = pu.us_policy_unit_seq_num AND ur.rejected_report_flag = 'N' JOIN us_report_batch rb ON rb.us_report_batch_seq_num = ur.us_report_batch_seq_num JOIN us_reported_loss rl ON rl.us_unit_report_seq_num = ur.us_unit_report_seq_num AND rl.claim_id = cu.claim_id AND rl.update_type_code <> 'D' LEFT OUTER JOIN us_coverage_combination cov ON cov.parta_claim_id = ic.claim_id LEFT OUTER JOIN us_catastrophic_claim cc ON cc.claim_id = ic.claim_id LEFT OUTER JOIN us_catastrophe cat ON cat.us_catastrophe_seq_num = cc.us_catastrophe_seq_num WHERE ic.claim_id = :claimID AND pu.active_unit_flag = 'Y' AND pu.historical_flag = 'N'

Note: Statements marked with * were inferred from code structure, naming conventions, or contextual evidence where direct confirmation was not available in source artifacts. Statements marked with ** could not be confirmed; treat as unknown until verified.

Sources

  • Business\SAIF.Business.UnitStat.Claim\Current Loss\clsCurrentLoss.vb