Skip to content

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 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 JOIN CODE, CBP_NOTE_CATEGORY_CODE
CBP_NOTE_CATEGORY_CODE JOIN CODE, CODE_DESCR

LINQ statement

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

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

-- 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

Sources

  • Services/SAIF.Services/Claims/Bills/NoteService.cs (L67)