--- title: "SQL-217: Retrieve LegalBill" summary: Read operation against LegalBill via NHibernate ORM. status: draft owner: SAIF Doc Gen last_reviewed: 2026-09-24 library: SAIF.Services reference_type: package --- # SQL-217: Retrieve LegalBill This page covers nine related `LegalBill` reads in `BillPaymentService`: bills ready to pay with no paid service amount (`GenerateZeroPaymentLetters`), pending-approval bill counts grouped by approving attorney (`GetAttorneysWithPendingLegalBills`), bills by status code (`GenerateCheckTransactionsAsync`, `UpdateBillStatus`), single bills by identifier (`GenerateCheckTransactionAsync`, `GenerateTransactionsForAdjustedBillAsync`, `ReturnBillPayment`, `CreateGlTransferActivity`), and a parent bill lookup filtered to the paid-grouping status. All are issued through NHibernate LINQ against the `LegalBill` entity from `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:112, 144, 174, 213, 272, 282, 365, 409, 493`. ## Classification | Field | Value | | --- | --- | | **SQL ID** | SQL-217 | | **Operation type** | Read | | **Execution method** | ORM (NHibernate LINQ) | | **Database** | Oracle | | **Schema** | [unqualified]** | | **Command type** | N/A (ORM-generated) | | **Business object** | `BillPaymentService` | | **Criteria property** | N/A — ORM | | **Lifecycle method** | `CreateGlTransferActivity, GenerateCheckTransactionAsync, GenerateCheckTransactionsAsync, GenerateTransactionsForAdjustedBillAsync, GenerateZeroPaymentLetters, GetAttorneysWithPendingLegalBills, ReturnBillPayment, UpdateBillStatus` | ## Parameters | Parameter | Direction | Type | Max length | Description | | --- | --- | --- | --- | --- | | `:ids*` | Input | long (list) | N/A | (2) expanded from `ids`, the attorney employee ids computed client-side at L142 | | `:billId` | Input | long | N/A | (4),(5) `bill.Id`; (8) `billId` | | `:parentBillId` | Input | long | N/A | (6) `billToPay.ParentBillId`; (9) `parentBillId` | | `:statusCode` | Input | string | N/A | (3) `fromCode` (method argument; e.g. `ApprovedCode`="APPR", `ReadyToPayCode`="PAY") | Constants (bound as bind parameters by NHibernate; shown as literals for readability): `ReadyToPayCode`="PAY", `PendingApprovalCode`="PEND", `PaidCode`="PAID", `SAIF.Core.Contracts.Legal.Bills.BillStatus.PaidGrouping`="PAID" (`Core/SAIF.Core/Contracts/Legal/Bills/BillStatus.cs:9`). ## Tables | Table | Access | Columns used | | --- | --- | --- | | [LEGAL_BILL](../database-tables.md#legal_bill) | SELECT | all 21 mapped columns (entity loads); filters LEGAL_BILL_SEQ_NUM, BILL_STATUS_CODE, APPROVED_BY_EMPLID; GROUP BY APPROVED_BY_EMPLID | | [LEGAL_BILL_SERVICE](../database-tables.md#legal_bill_service) | SELECT (subquery) | LEGAL_BILL_SEQ_NUM, PAID_AMOUNT (correlated SUM subquery in (1) only) | ## LINQ statement Source: `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:112`, `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:144`, `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:174`, `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:213`, `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:272`, `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:282`, `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:365`, `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:409`, `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:493` ```csharp // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:112 _unitOfWork.GetRepository() .Query() .Where(x => x.BillStatus.Code == BillData.BillStatus.ReadyToPayCode && x.BillServices.Sum(s => s.PaidAmount) == 0) .ToList(); // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:144 var billcount = _unitOfWork.GetRepository().Query() .Where(x => x.BillStatus.Code == BillData.BillStatus.PendingApprovalCode && ids.Contains(x.ApprovedByEmployeeId)) .GroupBy(x => x.ApprovedByEmployeeId) .Select(b => new { b.Key, myCount = b.Count() }).ToList(); // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:174 var approvedBills = _unitOfWork.GetRepository() .Query() .Where(b => b.BillStatus.Code == BillData.BillStatus.ReadyToPayCode) .ToList(); // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:213 var billToPay = _unitOfWork.GetRepository().Query().Where(x => x.Id == bill.Id).FirstOrDefault(); // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:272 var billToPay = _unitOfWork.GetRepository().Query().Where(x => x.Id == bill.Id).FirstOrDefault(); // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:282 var parentBill = _unitOfWork.GetRepository() .Query() .FirstOrDefault(p => p.Id == billToPay.ParentBillId && p.BillStatus.Code == BillStatus.PaidGrouping); // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:365 _unitOfWork.GetRepository() .Query() .Where(x => x.BillStatus.Code == fromCode) .ToList(); // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:409 var bill = _unitOfWork.GetRepository() .Query().FirstOrDefault(x => x.Id == billId && x.BillStatus.Code == BillData.BillStatus.PaidCode); // Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:493 var bill = _unitOfWork.GetRepository() .Query().FirstOrDefault(x => x.Id == parentBillId && x.BillStatus.Code == BillData.BillStatus.PaidCode); ``` ## 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) L112 — GenerateZeroPaymentLetters 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 WHERE LB.BILL_STATUS_CODE = 'PAY' -- BillStatus.ReadyToPayCode AND (SELECT SUM(S.PAID_AMOUNT) FROM LEGAL_BILL_SERVICE S WHERE S.LEGAL_BILL_SEQ_NUM = LB.LEGAL_BILL_SEQ_NUM) = 0; -- (2) L144 — GetAttorneysWithPendingLegalBills SELECT LB.APPROVED_BY_EMPLID, COUNT(*) FROM LEGAL_BILL LB WHERE LB.BILL_STATUS_CODE = 'PEND' -- PendingApprovalCode AND LB.APPROVED_BY_EMPLID IN (:ids0, :ids1, ...) -- ids.Contains(...) GROUP BY LB.APPROVED_BY_EMPLID; -- (3) L174 GenerateCheckTransactionsAsync, (7) L365 UpdateBillStatus SELECT LB. FROM LEGAL_BILL LB WHERE LB.BILL_STATUS_CODE = :statusCode; -- (3) 'PAY'; (7) :fromCode -- (4) L213 GenerateCheckTransactionAsync, (5) L272 GenerateTransactionsForAdjustedBillAsync SELECT * FROM ( SELECT LB. FROM LEGAL_BILL LB WHERE LB.LEGAL_BILL_SEQ_NUM = :billId ) WHERE ROWNUM <= 1; -- (6) L282 — parent bill SELECT * FROM ( SELECT LB. FROM LEGAL_BILL LB WHERE LB.LEGAL_BILL_SEQ_NUM = :parentBillId -- billToPay.ParentBillId (PARENT_LEGAL_BILL_SEQ_NUM value) AND LB.BILL_STATUS_CODE = 'PAID' -- BillStatus.PaidGrouping (Core contract const) ) WHERE ROWNUM <= 1; -- (8) L409 ReturnBillPayment, (9) L493 CreateGlTransferActivity SELECT * FROM ( SELECT LB. FROM LEGAL_BILL LB WHERE LB.LEGAL_BILL_SEQ_NUM = :billId -- (8) billId; (9) parentBillId AND LB.BILL_STATUS_CODE = 'PAID' -- BillData.BillStatus.PaidCode ) WHERE ROWNUM <= 1; ``` Translation notes: - `FirstOrDefault` → 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. - (1): `x.BillServices.Sum(s => s.PaidAmount) == 0` becomes a correlated scalar subquery over the `HasMany(x => x.BillServices).KeyColumn("LEGAL_BILL_SEQ_NUM")` collection (`LegalBillMap.cs:37`). If a bill has no service lines, SUM returns NULL and the bill is NOT selected. - (2): `GroupBy(...).Select(new { b.Key, myCount = b.Count() })` is translated to GROUP BY with COUNT(*); `.ToList()` is at L152. - (6): `BillStatus.PaidGrouping` is the *contract* constant (`using` of the Core contract type); its value "PAID" is compared against the status **code** FK, not `FILTER_GROUPING_TEXT`. - `bill.Id` in (4)/(5) is a client-side value from an earlier in-memory object. - Every `x.BillStatus.Code` reads the identifier of the referenced `BillStatus` and resolves to the FK column `LEGAL_BILL.BILL_STATUS_CODE`; the `BILL_STATUS_CODE` table is not joined. `BillServices.Count` (L274, L277) and the Attorney-profile query at L140 run in memory or on other pages. ## Operations and processes - BillPaymentService.CreateGlTransferActivity — see [Operations and processes](../operations-processes.md) - BillPaymentService.GenerateCheckTransactionAsync — see [Operations and processes](../operations-processes.md) - BillPaymentService.GenerateCheckTransactionsAsync — see [Operations and processes](../operations-processes.md) - BillPaymentService.GenerateTransactionsForAdjustedBillAsync — see [Operations and processes](../operations-processes.md) - BillPaymentService.GenerateZeroPaymentLetters — see [Operations and processes](../operations-processes.md) - BillPaymentService.GetAttorneysWithPendingLegalBills — see [Operations and processes](../operations-processes.md) - BillPaymentService.ReturnBillPayment — see [Operations and processes](../operations-processes.md) - BillPaymentService.UpdateBillStatus — see [Operations and processes](../operations-processes.md) ## Sources - `Services/SAIF.Services/Legal/Bills/BillPaymentService.cs (L112, L144, L174, L213, L272, L282, L365, L409, L493)`