SQL-214: Retrieve Investigation

This statement runs hand-written native SQL — not LINQ — through Session.CreateSqlQuery in InvestigationRepositoryExtensions, which the Investigation repository exposes as GetSubjectName and GetClaimId extension methods. FraudInvestigationService.GetSubjectNameAsync calls GetSubjectName(caseNumber) to resolve the worker/company name, worker ID, and policy name for an investigation subject, and FraudInvestigationService.GetClaimId calls GetClaimId(caseNumber) to fetch the claim ID for an investigation. The SQL text is defined at Data/SAIF.Data.NHibernate/Repositories/Investigations/InvestigationRepositoryExtensions.cs:10-25, 49-54.

Classification

Field	Value
SQL ID	SQL-214
Operation type	Read
Execution method	Inline SQL (NHibernate CreateSqlQuery)
Database	Oracle
Schema	[unqualified]**
Command type	Text
Business object	FraudInvestigationService
Criteria property	N/A
Lifecycle method	GetClaimId, GetSubjectNameAsync

Parameters

Parameter	Direction	Type	Max length	Description
:investigationId	Input	long	N/A	caseNumber, bound via SetParameter<long> (L26 / L55)

Tables

Table	Access	Columns used
INVESTIGATION	SELECT	SUBJECT_SEQ_NUM, PROVIDER_LOCATION_SEQ_NUM, POLICY_NUM, INVESTIGATION_SEQ_NUM (1); CLAIM_ID, INVESTIGATION_SEQ_NUM (2)
SUBJECT	JOIN	SUBJECT_SEQ_NUM, PERSON_SEQ_NUM, COMPANY_SEQ_NUM, WORKER_ID
PERSON	JOIN	PERSON_SEQ_NUM, NAME
COMPANY	JOIN	COMPANY_SEQ_NUM, COMPANY_NAME
PROVIDER_LOCATION	JOIN	PROVIDER_LOCATION_SEQ_NUM, BUSINESS_NAME
POLICY	JOIN	POLICY_NUM, POLICY_NAME

Sample SQL

-- (1) InvestigationRepositoryExtensions.GetSubjectName — L10-25
SELECT
  nvl(per.NAME,
    nvl(com.company_name,
      nvl(loc.business_name, ''))) as worker_name,
  sub.worker_id,
  pol.policy_name
FROM investigation inv
JOIN subject sub ON inv.subject_seq_num = sub.subject_seq_num
LEFT OUTER JOIN person per ON per.person_seq_num = sub.person_seq_num
LEFT OUTER JOIN company com ON com.company_seq_num = sub.company_seq_num
LEFT OUTER JOIN provider_location loc ON loc.provider_location_seq_num = inv.provider_location_seq_num
LEFT OUTER JOIN policy pol ON pol.policy_num = inv.policy_num
WHERE investigation_seq_num = :investigationId

-- (2) InvestigationRepositoryExtensions.GetClaimId — L49-54
SELECT inv.claim_id
FROM investigation inv
WHERE investigation_seq_num = :investigationId

Operations and processes

• FraudInvestigationService.GetClaimId — see Operations and processes
• FraudInvestigationService.GetSubjectNameAsync — see Operations and processes

Sources

• Services/SAIF.Services/Investigations/FraudInvestigationService.cs (L23, L45)
• Data/SAIF.Data.NHibernate/Repositories/Investigations/InvestigationRepositoryExtensions.cs (L10-25, L49-54)