Skip to content

SQL-033: BillQueryProvider.GetBillAmounts

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by BillQueryProvider.GetBillAmounts.

Classification

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

Parameters

Parameter Direction Type Max length Description
claimId Input long N/A Method argument of BillQueryProvider.GetBillAmounts bound into the ORM expression; NHibernate generates the bind variable at runtime
paymentCategory Input string N/A Method argument of BillQueryProvider.GetBillAmounts bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables

Table Access Columns used
CBP_BILL** SELECT (cross join) BILL_ID, CURRENT_BILL_STATUS_CODE; claim predicate unresolved (Claim)
CBP_AUDITED_SERVICE SELECT (cross join) BILL_ID, ALLOWED_AMT, BILL_PAYMENT_CATEGORY_CODE
CBP_AUDIT JOIN BILL_ID

LINQ statement

Source: Data/SAIF.Data/Queries/Claims/BillQueryProvider.cs:79, the same statement as SQL-032. This page covers the second range variable, asv (:80), and the second execution at :88.

var reviewedBills = from bil in Factory.GetRepository<CbpBill>().Query()
                    from asv in Factory.GetRepository<CbpAuditedService>().Query()
                    where bil.Id == asv.Audit.Bill.Id
                          && bil.Claim.Id == claimId
                          && billtype.Contains(bil.BillStatus.Code)
                          && asv.PaymentCategoryCode.Code == paymentCategory
                    select asv.AllowedAmount;

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
SELECT SUM(CAS.ALLOWED_AMT)
FROM CBP_BILL CB,
     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 -- unresolved mapping: CbpBill.Claim
  AND CB.CURRENT_BILL_STATUS_CODE IN (:billtype0, :billtype1, :billtypeN)
  AND CAS.BILL_PAYMENT_CATEGORY_CODE = :paymentCategory

Translation notes: - Duplicate of SQL-032: this is the same statement; see SQL-032 for the full translation notes. - bil.BillStatus.Code and asv.PaymentCategoryCode.Code are identifier accesses, so they resolve to CB.CURRENT_BILL_STATUS_CODE and CAS.BILL_PAYMENT_CATEGORY_CODE with no join; CBP_BILL_STATUS_CODE and CL_PAYMENT_CATEGORY_CODE are not accessed as separate tables. - CL_INJURY_CLAIM is unmapped, so it is not included. - Unresolved mapping CbpBill.Claim: the same gap as SQL-032. The likely intended predicate is CB.CLAIM_ID = :claimId.

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/Claims/BillQueryProvider.cs:79