---
title: "SQL-219: Retrieve LegalBill"
summary: Read LegalBill records for the inbox, pending-approval, and single-bill lookups.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-219: Retrieve LegalBill

`BillService` queries `LegalBill` from four call sites: an inbox read filtered by bill-status filter grouping, two pending-approval reads filtered by status code (one additionally by approving employee), and a single-bill lookup by primary key. All four reach the database through NHibernate LINQ over `IUnitOfWork.GetRepository<BillData.LegalBill>().Query()`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-219 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `LegalBill` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `GetInboxItems, GetLegalBill, GetPendingBills` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:employeeId` | Input | long | N/A | `employee.Id`, client-side — statement (2) filters by `APPROVED_BY_EMPLID` |
| `:billId` | Input | long | N/A | statement (4) filters `LEGAL_BILL_SEQ_NUM` by primary key |

Literals `'PAID'` (statement 1) and `'PEND'` (statements 2–3) are `BillStatus.PaidGrouping` / `BillData.BillStatus.PendingApprovalCode`, sent as bind values by NHibernate but shown inline for readability.

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [LEGAL_BILL](../database-tables.md#legal_bill) | SELECT | LEGAL_BILL_SEQ_NUM, UPDATE_NUM, ISSUE_DATE, RECEIVED_DATE, APPROVED_DATE, REQUESTED_BY_EMPLID, APPROVED_BY_EMPLID, INVOICE_TEXT, COMMENT_TEXT, PREPAYMENT_FLAG, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, WITNESS_INFORMATION, PARENT_LEGAL_BILL_SEQ_NUM, LEGAL_CASE_SEQ_NUM, BILL_STATUS_CODE, BILLING_PROV_LOC_SEQ_NUM, RENDERING_PROV_LOC_SEQ_NUM, SPECIAL_HANDLING_CODE |
| [BILL_STATUS_CODE](../database-tables.md#bill_status_code) | JOIN | CODE, FILTER_GROUPING_TEXT (statement 1 only) |

`BILL_STATUS_CODE` is not joined in statements (2)–(4): `x.BillStatus.Code` there is the referenced entity's identifier, so it short-circuits to `LEGAL_BILL.BILL_STATUS_CODE`. `x.LegalCase.ClaimId` (`BillService.cs:81`) is read in memory after statement (1) and lazy-loads `LEGAL_CASE` separately; it is not joined here.

## LINQ statement

Source: `Services/SAIF.Services/Legal/Bills/BillService.cs:77`, also `:145`, `:150`, `:232`.

```csharp
// Services/SAIF.Services/Legal/Bills/BillService.cs:77
var inboxItemsDataBase = _unitOfWork.GetRepository<BillData.LegalBill>().Query().Where(x => x.BillStatus.FilterGroupText != BillStatus.PaidGrouping).ToList();

// Services/SAIF.Services/Legal/Bills/BillService.cs:145
legalBills = _unitOfWork.GetRepository<BillData.LegalBill>().Query().Where(x => x.BillStatus.Code == BillData.BillStatus.PendingApprovalCode && x.ApprovedByEmployeeId == employee.Id).ToList();

// Services/SAIF.Services/Legal/Bills/BillService.cs:150
   legalBills = _unitOfWork.GetRepository<BillData.LegalBill>().Query()
.Where(x => x.BillStatus.Code == BillData.BillStatus.PendingApprovalCode).ToList();

// Services/SAIF.Services/Legal/Bills/BillService.cs:232
BillData.LegalBill LegalBillFromDataBase = _unitOfWork.GetRepository<BillData.LegalBill>().Query().FirstOrDefault(x => x.Id == billId);
```

## 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
-- (1) L77 — GetInboxItems
SELECT LB.LEGAL_BILL_SEQ_NUM,
       LB.UPDATE_NUM,
       LB.ISSUE_DATE,
       LB.RECEIVED_DATE,
       LB.APPROVED_DATE,
       LB.REQUESTED_BY_EMPLID,
       LB.APPROVED_BY_EMPLID,
       LB.INVOICE_TEXT,
       LB.COMMENT_TEXT,
       LB.PREPAYMENT_FLAG,
       LB.CREATE_USER,
       LB.CREATE_DATE,
       LB.UPDATE_USER,
       LB.UPDATE_DATE,
       LB.WITNESS_INFORMATION,
       LB.PARENT_LEGAL_BILL_SEQ_NUM,
       LB.LEGAL_CASE_SEQ_NUM,
       LB.BILL_STATUS_CODE,
       LB.BILLING_PROV_LOC_SEQ_NUM,
       LB.RENDERING_PROV_LOC_SEQ_NUM,
       LB.SPECIAL_HANDLING_CODE
FROM   LEGAL_BILL LB
  INNER JOIN BILL_STATUS_CODE BS ON BS.CODE = LB.BILL_STATUS_CODE
WHERE  BS.FILTER_GROUPING_TEXT <> 'PAID'       -- BillStatus.PaidGrouping

-- (2) L145 — GetPendingBills (employee branch)
SELECT LB.<all 21 mapped LEGAL_BILL columns, as in (1)>
FROM   LEGAL_BILL LB
WHERE  LB.BILL_STATUS_CODE = 'PEND'                        -- PendingApprovalCode
  AND  LB.APPROVED_BY_EMPLID = :employeeId

-- (3) L150 — GetPendingBills (no employee)
SELECT LB.<all 21 mapped LEGAL_BILL columns, as in (1)>
FROM   LEGAL_BILL LB
WHERE  LB.BILL_STATUS_CODE = 'PEND'

-- (4) L232 — GetLegalBill
SELECT * FROM (
  SELECT LB.<all 21 mapped LEGAL_BILL columns, as in (1)>
  FROM   LEGAL_BILL LB
  WHERE  LB.LEGAL_BILL_SEQ_NUM = :billId
) WHERE ROWNUM <= 1
```

Translation notes:
- `FirstOrDefault` (statement 4) → Oracle10 dialect wraps the query in `SELECT * FROM (...) WHERE ROWNUM <= 1`.
- C# `const` values are shown as literals for readability; NHibernate LINQ sends them as bind parameters.
- Statement (1) compares a non-identifier property (`FilterGroupText`) of the referenced `BillStatus`, so NHibernate emits an implicit inner join. The `<>` comparison is not null-safe: rows whose `FILTER_GROUPING_TEXT` is NULL are excluded.

## Operations and processes

## Business rules
- [SVC-BR-LGL-013](../business-rules.md#svc-br-lgl-013-a-bill-is-over-authority-when-its-total-exceeds-both-the-initiate-and-approve-limits)
- [SVC-BR-LGL-014](../business-rules.md#svc-br-lgl-014-legal-support-staff-%3D-dept-%22005201%22%2C-active%2C-title-starting-with-%22legal%22%3B-plus-a-hard-coded-dev-list-outside-production)

## Sources
- `Services/SAIF.Services/Legal/Bills/BillService.cs (L77, L145, L150, L232)`