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	SELECT	`BILL_ID`, `CLAIM_ID`, `CURRENT_BILL_STATUS_CODE`
CBP_AUDITED_SERVICE	SELECT	`ALLOWED_AMT`, `BILL_ID`, `BILL_PAYMENT_CATEGORY_CODE`
CBP_AUDIT	JOIN	`BILL_ID`

LINQ statement¶

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

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

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

Business rules¶

SVC-BR-BIL-004

Sources¶

Services/SAIF.Services/Claims/Bills/BillService.cs (L113)