Skip to content

SQL-137: EbillQueryProvider.SetCompoundDrugForAllServiceLinesForTheBill

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

Classification

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

Parameters

Parameter Direction Type Max length Description
bill Input Ebill N/A Method argument of EbillQueryProvider.SetCompoundDrugForAllServiceLinesForTheBill bound into the ORM expression; NHibernate generates the bind variable at runtime

Tables

Table Access Columns used
EBILL_SERVICE SELECT EBILL_SEQ_NUM, NATIONAL_DRUG_CODE, EBILL_SERVICE_SEQ_NUM, UPDATE_NUM, EBILL_SERVICE_SUB_NUM, LINE_CONTROL_NUM, BILL_SERVICE_DATE, BILL_SERVICE_END_DATE, CHARGE_AMOUNT, SERVICE_QUANTITY, UNIT_TYPE_CODE, SERVICE_MODIFIER_CODE1, SERVICE_MODIFIER_CODE2, SERVICE_MODIFIER_CODE3, SERVICE_MODIFIER_CODE4, BILL_SERVICE_CODE, TOOTH_CODE, RENDERING_PROVIDER_NPI, REFERRING_PROVIDER_NPI, DIAGNOSIS_POINTER_CODE1, DIAGNOSIS_POINTER_CODE2, DIAGNOSIS_POINTER_CODE3, DIAGNOSIS_POINTER_CODE4, REVENUE_CODE, SERVICE_LOCATION_CODE, DISPENSE_AS_WRITTEN_CODE, DAYS_SUPPLY_QTY, UPDATE_USER, UPDATE_DATE, PRESCRIBER_LAST_NAME, PRESCRIBER_FIRST_NAME, TAX_CHARGED_AMT, COMPOUND_DRUG_FLAG, PRESCRIPTION_WRITTEN_DATE, REFERRING_PROVIDER_LOC_SEQ_NUM, RENDERING_PROVIDER_LOC_SEQ_NUM

LINQ statement

Source: Data/SAIF.Data/Queries/Claims/Ebilling/EbillQueryProvider.cs:311.

// Data/SAIF.Data/Queries/Claims/Ebilling/EbillQueryProvider.cs:311
var billServices = Factory.GetRepository<EbillService>().Query().Where(x => x.Bill == bill && x.NationalDrugCode != null);
if (billServices.Select(x => x.PrescriptionNumber).Distinct().Count() <= 1) return;
foreach (var service in billServices)

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
-- Statement 1: L312 billServices.Select(x => x.PrescriptionNumber).Distinct().Count()
-- unresolved mapping: EbillService.PrescriptionNumber
-- The projected property has no column mapping, so the DISTINCT/COUNT projection cannot be reliably inferred.
-- The base filter that is translatable is:
--   FROM EBILL_SERVICE ES
--   WHERE ES.EBILL_SEQ_NUM = :bill
--     AND ES.NATIONAL_DRUG_CODE IS NOT NULL

-- Statement 2: L313 foreach (var service in billServices), entity enumeration
SELECT
  ES.EBILL_SERVICE_SEQ_NUM,
  ES.UPDATE_NUM,
  ES.EBILL_SERVICE_SUB_NUM,
  ES.LINE_CONTROL_NUM,
  ES.BILL_SERVICE_DATE,
  ES.BILL_SERVICE_END_DATE,
  ES.CHARGE_AMOUNT,
  ES.SERVICE_QUANTITY,
  ES.UNIT_TYPE_CODE,
  ES.SERVICE_MODIFIER_CODE1,
  ES.SERVICE_MODIFIER_CODE2,
  ES.SERVICE_MODIFIER_CODE3,
  ES.SERVICE_MODIFIER_CODE4,
  ES.BILL_SERVICE_CODE,
  ES.NATIONAL_DRUG_CODE,
  ES.TOOTH_CODE,
  ES.RENDERING_PROVIDER_NPI,
  ES.REFERRING_PROVIDER_NPI,
  ES.DIAGNOSIS_POINTER_CODE1,
  ES.DIAGNOSIS_POINTER_CODE2,
  ES.DIAGNOSIS_POINTER_CODE3,
  ES.DIAGNOSIS_POINTER_CODE4,
  ES.REVENUE_CODE,
  ES.SERVICE_LOCATION_CODE,
  ES.DISPENSE_AS_WRITTEN_CODE,
  ES.DAYS_SUPPLY_QTY,
  ES.UPDATE_USER,
  ES.UPDATE_DATE,
  ES.PRESCRIBER_LAST_NAME,
  ES.PRESCRIBER_FIRST_NAME,
  ES.TAX_CHARGED_AMT,
  ES.COMPOUND_DRUG_FLAG,
  ES.PRESCRIPTION_WRITTEN_DATE,
  ES.EBILL_SEQ_NUM,
  ES.REFERRING_PROVIDER_LOC_SEQ_NUM,
  ES.RENDERING_PROVIDER_LOC_SEQ_NUM
FROM EBILL_SERVICE ES
WHERE ES.EBILL_SEQ_NUM = :bill
  AND ES.NATIONAL_DRUG_CODE IS NOT NULL

Translation notes:

  • billServices (L311) is an un-materialized IQueryable, so it runs up to twice: once for the Distinct().Count() at L312, and again when the foreach at L313 enumerates it, if the count is greater than 1.
  • Unresolved mapping: PrescriptionNumber is not mapped. At runtime NHibernate LINQ would throw when it tries to resolve the property path, so the L312 projection SQL is not written. Writers must not fabricate a COUNT(DISTINCT ...) column.
  • Inside the loop, service.IsCompoundDrug = true changes a persistent entity. On flush/commit NHibernate issues a versioned UPDATE EBILL_SERVICE of COMPOUND_DRUG_FLAG (YesNoType). That write comes from dirty checking, not LINQ, so it is described here but not written as SQL.

Operations and processes

Business rules

Sources

  • Data/SAIF.Data/Queries/Claims/Ebilling/EbillQueryProvider.cs:311