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

# SQL-128: Retrieve CbpNote via GetBillNotes

This ORM statement performs a read operation on `CbpNote` via `IUnitOfWork.GetRepository<T>()`, executed from `Services/SAIF.Services/Claims/Bills/NoteService.cs (L67)`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-128 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `CbpNote` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `GetBillNotes` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `claimId` | Input | ** | N/A | `x.ClaimId == claimId` → column CLAIM_ID |
| `showInactiveToo` | Input | ** | N/A | `x.IsActive != showInactiveToo` → column ACTIVE_FLAG |
| `billId` | Input | ** | N/A | `x.Bill.Id == billId` → column join via `Bill` (BILL_ID) → `Bill.Id` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CBP_NOTE](../database-tables.md#cbp_note) | SELECT | `CBP_NOTE_SEQ_NUM`, `UPDATE_NUM`, `ACTIVE_FLAG`, `CREATE_DATE`, `CREATE_USER`, `UPDATE_DATE`, `UPDATE_USER`, `NOTE_TXT`, `CLAIM_ID`, `NOTE_TYPE_CODE`, `NOTE_CATEGORY_CODE`, `BILL_ID` |
| [CBP_NOTE_TYPE_CODE](../database-tables.md#cbp_note_type_code) | JOIN | `CODE`, `CBP_NOTE_CATEGORY_CODE` |
| [CBP_NOTE_CATEGORY_CODE](../database-tables.md#cbp_note_category_code) | JOIN | `CODE`, `CODE_DESCR` |

## LINQ statement

Source: `Services/SAIF.Services/Claims/Bills/NoteService.cs:67`

```csharp
// Services/SAIF.Services/Claims/Bills/NoteService.cs:67-69
            var auditNotes = unitOfWork.GetRepository<CbpNote>().Query()
                               .Where(x => (x.ClaimId == claimId && (x.IsActive || x.IsActive != showInactiveToo)) ||
                                   (x.Bill.Id == billId && (x.IsActive || x.IsActive != showInactiveToo)));

// Services/SAIF.Services/Claims/Bills/NoteService.cs:71
            var items = Mapper.Map<AuditNoteItem[]>(auditNotes.OrderBy(x => x.NoteType.Category.Description).ThenByDescending(x => x.CreateDate));
```

## 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/Bills/NoteService.cs:67
SELECT
  CN.CBP_NOTE_SEQ_NUM,
  CN.UPDATE_NUM,
  CN.ACTIVE_FLAG,
  CN.CREATE_DATE,
  CN.CREATE_USER,
  CN.UPDATE_DATE,
  CN.UPDATE_USER,
  CN.NOTE_TXT,
  CN.CLAIM_ID,
  CN.NOTE_TYPE_CODE,
  CN.NOTE_CATEGORY_CODE,
  CN.BILL_ID
FROM CBP_NOTE CN
  INNER JOIN CBP_NOTE_TYPE_CODE CNTC
    ON CNTC.CODE = CN.NOTE_TYPE_CODE
    AND CNTC.CBP_NOTE_CATEGORY_CODE = CN.NOTE_CATEGORY_CODE
  INNER JOIN CBP_NOTE_CATEGORY_CODE CNCC
    ON CNCC.CODE = CNTC.CBP_NOTE_CATEGORY_CODE
WHERE (CN.CLAIM_ID = :claimId AND (CN.ACTIVE_FLAG = 'Y' OR CN.ACTIVE_FLAG <> :showInactiveToo))
  OR (CN.BILL_ID = :billId AND (CN.ACTIVE_FLAG = 'Y' OR CN.ACTIVE_FLAG <> :showInactiveToo))
ORDER BY CNCC.CODE_DESCR, CN.CREATE_DATE DESC
```

Translation notes:
- The `OrderBy(x => x.NoteType.Category.Description).ThenByDescending(x => x.CreateDate)` at L71 is applied to the still-unmaterialized `IQueryable` before `Mapper.Map` enumerates it -> it is SQL, not in-memory.
- `NoteType` is a composite `References(...).Columns("NOTE_TYPE_CODE","NOTE_CATEGORY_CODE")` (CbpNoteMap.cs:29) -> INNER JOIN `CBP_NOTE_TYPE_CODE` on both key columns (CbpNoteTypeCodeMap.cs:14-16). `Category` is a `KeyReference` on `CBP_NOTE_CATEGORY_CODE`; `Description` is non-identifier -> INNER JOIN `CBP_NOTE_CATEGORY_CODE` (CbpNoteCategoryCodeMap.cs:10). Some NHibernate versions may skip the `CBP_NOTE_TYPE_CODE` hop because `Category` is part of its composite key (joining `CNCC.CODE = CN.NOTE_CATEGORY_CODE` directly); the join-both form follows the skill's per-hop rule. The inner joins drop notes whose type is null.
- `IsActive` is `YesNoType` -> `x.IsActive` = `ACTIVE_FLAG = 'Y'`; `x.IsActive != showInactiveToo` -> `ACTIVE_FLAG <> :showInactiveToo` with the bool bound through `YesNoType` (`'Y'`/`'N'`).

## Operations and processes
- NoteService.GetBillNotes — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Claims/Bills/NoteService.cs (L67)`