SQL-141: EbillServiceQueryProvider.GetEbillService

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

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:ebillId	Input	long	N/A	Evaluated client-side from the Ebill returned by SQL-140 (ebill.Id)
:bsvId	Input	long	N/A	Evaluated client-side from bsv.Id (CbpBillService.Id, BILL_SERVICE_SEQ_NUM)

Tables

Table	Access	Columns used
EBILL_SERVICE	SELECT	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, NATIONAL_DRUG_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, EBILL_SEQ_NUM, REFERRING_PROVIDER_LOC_SEQ_NUM, RENDERING_PROVIDER_LOC_SEQ_NUM

LINQ statement

Source: Data/SAIF.Data/Queries/Claims/Ebilling/EbillServiceQueryProvider.cs:47

// Data/SAIF.Data/Queries/Claims/Ebilling/EbillServiceQueryProvider.cs:18
repository = Factory.GetRepository<EbillService>().Query();

// Data/SAIF.Data/Queries/Claims/Ebilling/EbillServiceQueryProvider.cs:47
var esv = repository.FirstOrDefault(x => x.Bill.Id == ebill.Id && x.SubNumber == bsv.Id);

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 *
FROM (
  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 = :ebillId
    AND ES.EBILL_SERVICE_SUB_NUM = :bsvId
)
WHERE ROWNUM <= 1

Translation notes: - Oracle10 row limit for FirstOrDefault(): the query (including any ORDER BY) is wrapped in ROWNUM <= 1 (limit written inline; NHibernate binds it as a parameter at runtime). - This runs only after the SQL-140 lookup, which supplies ebill.

Operations and processes

• E-bill processing queries

Sources

• Data/SAIF.Data/Queries/Claims/Ebilling/EbillServiceQueryProvider.cs:47