Skip to content

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

// Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:112
_unitOfWork.GetRepository<BillData.LegalBill>()
    .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<BillData.LegalBill>().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<BillData.LegalBill>()
        .Query()
        .Where(b => b.BillStatus.Code == BillData.BillStatus.ReadyToPayCode)
        .ToList();

// Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:213
var billToPay = _unitOfWork.GetRepository<BillData.LegalBill>().Query().Where(x => x.Id == bill.Id).FirstOrDefault();

// Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:272
var billToPay = _unitOfWork.GetRepository<BillData.LegalBill>().Query().Where(x => x.Id == bill.Id).FirstOrDefault();

// Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:282
var parentBill = _unitOfWork.GetRepository<BillData.LegalBill>()
    .Query()
    .FirstOrDefault(p => p.Id == billToPay.ParentBillId && p.BillStatus.Code == BillStatus.PaidGrouping);

// Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:365
_unitOfWork.GetRepository<BillData.LegalBill>()
    .Query()
    .Where(x => x.BillStatus.Code == fromCode)
    .ToList();

// Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:409
var bill = _unitOfWork.GetRepository<BillData.LegalBill>()
    .Query().FirstOrDefault(x => x.Id == billId && x.BillStatus.Code == BillData.BillStatus.PaidCode);

// Services/SAIF.Services/Legal/Bills/BillPaymentService.cs:493
var bill = _unitOfWork.GetRepository<BillData.LegalBill>()
    .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.

-- 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.<all 21 mapped LEGAL_BILL columns, as in (1)>
FROM   LEGAL_BILL LB
WHERE  LB.BILL_STATUS_CODE = :statusCode;    -- (3) 'PAY'; (7) :fromCode

-- (4) L213 GenerateCheckTransactionAsync, (5) L272 GenerateTransactionsForAdjustedBillAsync
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;

-- (6) L282 — parent bill
SELECT * FROM (
  SELECT LB.<all 21 mapped LEGAL_BILL columns, as in (1)>
  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.<all 21 mapped LEGAL_BILL columns, as in (1)>
  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

Sources

  • Services/SAIF.Services/Legal/Bills/BillPaymentService.cs (L112, L144, L174, L213, L272, L282, L365, L409, L493)