---
title: "SQL-138: Retrieve ClaimNote"
summary: Read operation against ClaimNote via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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`

```csharp
// 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.

```sql
-- 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
- ClaimNotesService.DeleteNote — see [Operations and processes](../operations-processes.md)
- ClaimNotesService.GetNoteData — see [Operations and processes](../operations-processes.md)
- ClaimNotesService.GetNotes — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Claims/Notes/ClaimNotesService.cs (L105, L165, L236)`