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

# SQL-111: Retrieve CbpAuditedService via GetBillAmounts

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

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| (none) | N/A | N/A | N/A | No inline filter — full-entity read (`Query().ToList()`/projection) or filters appended later |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [CBP_BILL](../database-tables.md#cbp_bill) | SELECT | `BILL_ID`, `CLAIM_ID`, `CURRENT_BILL_STATUS_CODE` |
| [CBP_AUDITED_SERVICE](../database-tables.md#cbp_audited_service) | SELECT | `ALLOWED_AMT`, `BILL_ID`, `BILL_PAYMENT_CATEGORY_CODE` |
| [CBP_AUDIT](../database-tables.md#cbp_audit) | JOIN | `BILL_ID` |

## LINQ statement

Source: `Services/SAIF.Services/Claims/Bills/BillService.cs:111`

```csharp
// Services/SAIF.Services/Claims/Bills/BillService.cs:111-118
            var billtype = billTypes.ToList();
            var reviewedBills = from bil in unitOfWork.GetRepository<CbpBill>().Query()
                                from asv in unitOfWork.GetRepository<CbpAuditedService>().Query()
                                where bil.Id == asv.Audit.Bill.Id
                                      && bil.ClaimId == claimId
                                      && billtype.Contains(bil.BillStatus.Code)
                                      && asv.PaymentCategoryCode.Code == paymentCategory
                                select asv.AllowedAmount;

// Services/SAIF.Services/Claims/Bills/BillService.cs:120-122
            return (reviewedBills.Sum() != null)
                ? (decimal)reviewedBills.Sum()
                : 0;
```

## 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/BillService.cs:113
SELECT SUM(CAS.ALLOWED_AMT)
FROM CBP_BILL CB
  CROSS JOIN CBP_AUDITED_SERVICE CAS
  INNER JOIN CBP_AUDIT CA
    ON CA.BILL_ID = CAS.BILL_ID
WHERE CB.BILL_ID = CA.BILL_ID
  AND CB.CLAIM_ID = :claimId
  AND CB.CURRENT_BILL_STATUS_CODE IN (:billtype)
  AND CAS.BILL_PAYMENT_CATEGORY_CODE = :paymentCategory
```

Translation notes:
- Two `from` clauses produce a cross product filtered by the `where`; NHibernate emits it theta-style (`FROM CBP_BILL cb, CBP_AUDITED_SERVICE cas ...`).
- `asv.Audit.Bill.Id`: `Audit` is a `KeyReference` on `BILL_ID` (CbpAuditedServiceMap.cs:19). `CbpAudit` has an anonymous identifier `Id().Column("BILL_ID")` (CbpAuditMap.cs:14), so `Bill` is a non-identifier `References` (CbpAuditMap.cs:16) -> INNER JOIN `CBP_AUDIT`, then `Bill.Id` short-circuits to `CA.BILL_ID`. The join is logically redundant (both columns are `BILL_ID`) but follows the mapping; `CBP_BILL` is not joined a second time.
- `billtype.Contains(bil.BillStatus.Code)` -> `IN (:billtype)` (list parameter).
- `reviewedBills.Sum()` is invoked twice (L120 and L121) -> the identical aggregate statement executes twice per call.
- Same statement as SQL-117 (documented from the `CbpAuditedService` side).

## Operations and processes
- BillService.GetBillAmounts — see [Operations and processes](../operations-processes.md)

## Business rules
- [SVC-BR-BIL-004](../business-rules.md#svc-br-bil-004-amount-%3D-sum-of-allowedamount-over-audited-services-for-claim%2Fstatus-list%2Fpayment-category%3B-null-%E2%86%92-0)

## Sources
- `Services/SAIF.Services/Claims/Bills/BillService.cs (L113)`