SQL-221: Check for duplicate LegalBillService

BillRateService.CheckForDuplicateService looks up LegalBillService rows against the same billing/rendering provider and case, first with an exact match on bill-service code, service-from date, and billed amount, then (if none found) with a looser match on provider/case/date only. Both queries join LEGAL_BILL to reach the provider and case filters.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:providerLocationId	Input	long	N/A	method argument, matched against billing or rendering provider
:caseId	Input	long	N/A	method argument, matched against the bill's legal case
:billId	Input	long	N/A	method argument, excluded bill (<>)
:billServiceCode	Input	string	N/A	serviceLine.BillService.Code (statement 1 only)
:serviceFromDate	Input	DateTime	N/A	serviceLine.ServiceFromDate
:billedAmount	Input	decimal	N/A	serviceLine.BilledAmount (statement 1 only)

Tables

Table	Access	Columns used
LEGAL_BILL_SERVICE	SELECT	all 18 mapped columns; filters BILL_SERVICE_CODE, SERVICE_FROM_DATE, BILLED_AMOUNT
LEGAL_BILL	JOIN	LEGAL_BILL_SEQ_NUM, BILLING_PROV_LOC_SEQ_NUM, RENDERING_PROV_LOC_SEQ_NUM, LEGAL_CASE_SEQ_NUM

PROVIDER_LOCATION, LEGAL_CASE, and BILL_SERVICE_CODE are not joined. LegalBill.BillingProvider.Id, RenderingProvider.Id, and LegalCase.Id are FK identifiers on LEGAL_BILL (the first-level navigation LegalBill is joined, but its second-level navigations short-circuit to its own FK columns), and x.BillService.Code is the FK identifier LEGAL_BILL_SERVICE.BILL_SERVICE_CODE. LEGAL_BILL itself IS joined: its FK columns are needed and are not available on LEGAL_BILL_SERVICE.

LINQ statement

Source: Services/SAIF.Services/Legal/Bills/BillRateService.cs:79, also :92.

// Services/SAIF.Services/Legal/Bills/BillRateService.cs:79
var legalBillService = _unitOfWork.GetRepository<BillData.LegalBillService>().Query()
      .FirstOrDefault(x => (x.LegalBill.BillingProvider.Id == providerLocationId || x.LegalBill.RenderingProvider.Id == providerLocationId)
                        && x.LegalBill.LegalCase.Id == caseId
                        && x.LegalBill.Id != billId
                        && x.BillService.Code == serviceLine.BillService.Code
                        && x.ServiceFromDate == serviceLine.ServiceFromDate
                        && x.BilledAmount == serviceLine.BilledAmount);

// Services/SAIF.Services/Legal/Bills/BillRateService.cs:92
legalBillService = _unitOfWork.GetRepository<BillData.LegalBillService>().Query()
    .FirstOrDefault(x => (x.LegalBill.BillingProvider.Id == providerLocationId || x.LegalBill.RenderingProvider.Id == providerLocationId)
                      && x.LegalBill.LegalCase.Id == caseId
                      && x.LegalBill.Id != billId
                      && x.ServiceFromDate == serviceLine.ServiceFromDate);

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) L79 — duplicate check including service code and billed amount
SELECT * FROM (
  SELECT S.LEGAL_BILL_SERVICE_SEQ_NUM,
       S.UPDATE_NUM,
       S.AMENDED_FLAG,
       S.SERVICE_FROM_DATE,
       S.SERVICE_THRU_DATE,
       S.BILLED_AMOUNT,
       S.PAID_AMOUNT,
       S.BILLED_UNITS,
       S.ADJUSTMENT_REASON_TEXT,
       S.PARENT_BILL_SERVICE_SEQ_NUM,
       S.CREATE_USER,
       S.CREATE_DATE,
       S.UPDATE_USER,
       S.UPDATE_DATE,
       S.LEGAL_BILL_SEQ_NUM,
       S.BILL_SERVICE_CODE,
       S.BILL_SERVICE_RATE_TYPE_CODE,
       S.ADJUSTMENT_REASON_CODE
  FROM   LEGAL_BILL_SERVICE S
    INNER JOIN LEGAL_BILL LB ON LB.LEGAL_BILL_SEQ_NUM = S.LEGAL_BILL_SEQ_NUM
  WHERE  (LB.BILLING_PROV_LOC_SEQ_NUM = :providerLocationId
          OR LB.RENDERING_PROV_LOC_SEQ_NUM = :providerLocationId)
    AND  LB.LEGAL_CASE_SEQ_NUM = :caseId
    AND  LB.LEGAL_BILL_SEQ_NUM <> :billId
    AND  S.BILL_SERVICE_CODE = :billServiceCode
    AND  S.SERVICE_FROM_DATE = :serviceFromDate
    AND  S.BILLED_AMOUNT = :billedAmount
) WHERE ROWNUM <= 1

-- (2) L92 — possible-duplicate check (provider/case/bill/from-date only)
SELECT * FROM (
  SELECT S.<all 18 mapped LEGAL_BILL_SERVICE columns, as in (1)>
  FROM   LEGAL_BILL_SERVICE S
    INNER JOIN LEGAL_BILL LB ON LB.LEGAL_BILL_SEQ_NUM = S.LEGAL_BILL_SEQ_NUM
  WHERE  (LB.BILLING_PROV_LOC_SEQ_NUM = :providerLocationId
          OR LB.RENDERING_PROV_LOC_SEQ_NUM = :providerLocationId)
    AND  LB.LEGAL_CASE_SEQ_NUM = :caseId
    AND  LB.LEGAL_BILL_SEQ_NUM <> :billId
    AND  S.SERVICE_FROM_DATE = :serviceFromDate
) WHERE ROWNUM <= 1

Translation notes: - FirstOrDefault → Oracle10 dialect wraps each query in SELECT * FROM (...) WHERE ROWNUM <= 1. - Only the first-level navigation x.LegalBill needs a join. The second-level .BillingProvider.Id / .RenderingProvider.Id / .LegalCase.Id are short-circuited to FK columns on the joined LEGAL_BILL, so PROVIDER_LOCATION and LEGAL_CASE are never joined.

Operations and processes

• BillRateService.CheckForDuplicateService — see Operations and processes

Sources

• Services/SAIF.Services/Legal/Bills/BillRateService.cs (L79, L92)