Skip to content

SQL-138: Retrieve ClaimNote

This ORM statement performs a read operation on ClaimNote via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs (L105, L165, L236).

Classification

Field Value
SQL ID SQL-138
Operation type Read
Execution method ORM (NHibernate LINQ)
Database Oracle
Schema [unqualified]**
Command type N/A (ORM-generated)
Business object ClaimNote
Criteria property N/A — ORM
Lifecycle method DeleteNote, GetNoteData, GetNotes

Parameters

Parameter Direction Type Max length Description
criteria.ClaimId Input ** N/A x.ClaimId == criteria.ClaimId → column CLAIM_ID
noteId Input ** N/A x.Id == noteId → column CLAIM_ACTIVITY_SEQ_NUM

Tables

Table Access Columns used
CLAIM_ACTIVITY SELECT CLAIM_ACTIVITY_SEQ_NUM, CLAIM_ID, ACTIVITY_DATE, EXTERNAL_VIEW_FLAG, COMMENT_TEXT, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, UPDATE_NUM, CLAIM_ACTIVITY_CATEGORY_CODE, CLAIM_ACTIVITY_TYPE_CODE

LINQ statement

Source: Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:105

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:105-106
            var query = UnitOfWork.GetRepository<ClaimNote>().Query()
                .Where(x => x.ClaimId == criteria.ClaimId);

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:109
                query = query.Where(x => x.NoteDate.Date >= criteria.FromDate);

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:112
                query = query.Where(x => x.NoteDate.Date <= criteria.ThruDate);

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:115
                query = query.Where(x => x.ClaimNoteCategory.Code == criteria.CategoryCode);

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:118
                query = query.Where(x => x.ClaimNoteType.Code == criteria.TypeCode);

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:120
            var notes = Mapper.Map<Note[]>(query.ToList());

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:165-166
            var note = UnitOfWork.GetRepository<ClaimNote>().Query()
                        .SingleOrDefault(x => x.Id == noteId);

// Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:236-237
            var note = UnitOfWork.GetRepository<ClaimNote>().Query().SingleOrDefault(x => x.Id == noteId);
            UnitOfWork.GetRepository<ClaimNote>().Delete(note);

Sample SQL

Inferred SQL: This SQL was inferred from the LINQ statement above and the NHibernate mappings. It is not concrete SQL from source code; NHibernate generates the executed statement at runtime, so aliases, column order, join syntax, and bind-parameter names may differ.

-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
-- Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:105
SELECT
  CA.CLAIM_ACTIVITY_SEQ_NUM,
  CA.CLAIM_ID,
  CA.ACTIVITY_DATE,
  CA.EXTERNAL_VIEW_FLAG,
  CA.COMMENT_TEXT,
  CA.CREATE_USER,
  CA.CREATE_DATE,
  CA.UPDATE_USER,
  CA.UPDATE_DATE,
  CA.UPDATE_NUM,
  CA.CLAIM_ACTIVITY_CATEGORY_CODE,
  CA.CLAIM_ACTIVITY_TYPE_CODE
FROM CLAIM_ACTIVITY CA
WHERE CA.CLAIM_ID = :ClaimId
  AND TRUNC(CA.ACTIVITY_DATE) >= :FromDate -- applied only when criteria.FromDate has a value (L109)
  AND TRUNC(CA.ACTIVITY_DATE) <= :ThruDate -- applied only when criteria.ThruDate has a value (L112)
  AND CA.CLAIM_ACTIVITY_CATEGORY_CODE = :CategoryCode -- only when CategoryCode is not ALL/empty (L115)
  AND CA.CLAIM_ACTIVITY_TYPE_CODE = :TypeCode -- only when TypeCode is not ALL/null (L118)

-- Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:165
SELECT
  CA.CLAIM_ACTIVITY_SEQ_NUM,
  CA.CLAIM_ID,
  CA.ACTIVITY_DATE,
  CA.EXTERNAL_VIEW_FLAG,
  CA.COMMENT_TEXT,
  CA.CREATE_USER,
  CA.CREATE_DATE,
  CA.UPDATE_USER,
  CA.UPDATE_DATE,
  CA.UPDATE_NUM,
  CA.CLAIM_ACTIVITY_CATEGORY_CODE,
  CA.CLAIM_ACTIVITY_TYPE_CODE
FROM CLAIM_ACTIVITY CA
WHERE CA.CLAIM_ACTIVITY_SEQ_NUM = :noteId
-- NHibernate expects at most one row

-- Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs:236
SELECT
  CA.CLAIM_ACTIVITY_SEQ_NUM,
  CA.CLAIM_ID,
  CA.ACTIVITY_DATE,
  CA.EXTERNAL_VIEW_FLAG,
  CA.COMMENT_TEXT,
  CA.CREATE_USER,
  CA.CREATE_DATE,
  CA.UPDATE_USER,
  CA.UPDATE_DATE,
  CA.UPDATE_NUM,
  CA.CLAIM_ACTIVITY_CATEGORY_CODE,
  CA.CLAIM_ACTIVITY_TYPE_CODE
FROM CLAIM_ACTIVITY CA
WHERE CA.CLAIM_ACTIVITY_SEQ_NUM = :noteId
-- NHibernate expects at most one row

Translation notes: - x.NoteDate.Date -> TRUNC(CA.ACTIVITY_DATE) (NHibernate maps DateTime.Date to the dialect date-truncation function; exact rendering is version/dialect dependent). - L105-118 is materialized by query.ToList() at L120; later secured-access filtering (L122 onward) runs in memory. - L236 is followed by Delete(note) (L237) - a separate CRUD statement not documented under this SQL ID. UPDATE_NUM is a plain Map here (not a Version).

Operations and processes

Sources

  • Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs (L105, L165, L236)