SQL-140: EbillServiceQueryProvider.GetEbillService

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

Classification

Field	Value
SQL ID	SQL-140
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
:bsvBillId	Input	long	N/A	Evaluated client-side from bsv.Bill.Id (CbpBillService.Bill.Id); bound into the ORM expression

Tables

Table	Access	Columns used
EBILL	SELECT	EBILL_SEQ_NUM, UPDATE_NUM, CLEARINGHOUSE_CONTROL_NUM, STATEMENT_FROM_DATE, STATEMENT_THRU_DATE, INJURY_DATE, ADMISSION_DATE, DISCHARGE_DATE, ORIGINAL_BILL_ID, BILL_TOTAL_AMOUNT, UB_BILL_TYPE_CODE, SERVICE_LOCATION_CODE, FACILITY_NPI, SUBMITTER_CONTROL_NUM, TRANSACTION_SET_CONTROL_NUM, UPDATE_USER, UPDATE_DATE, CLEARINGHOUSE_RECEIVED_DATE, CBP_ADMISSION_TYPE_CODE, BILL_ID, EBILL_RECEIPT_BATCH_SEQ_NUM, EBILL_PATIENT_SEQ_NUM, EBILL_STATUS_CODE, FACILITY_ADDRESS_SEQ_NUM, EBILL_SET_TYPE_CODE, EBILL_FIRST_RESPONSE_SEQ_NUM, EBILL_LAST_RESPONSE_SEQ_NUM, EBILL_RESPONSE_SET_SEQ_NUM, EBILL_EOB_SEQ_NUM

LINQ statement

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

// Data/SAIF.Data/Queries/Claims/Ebilling/EbillServiceQueryProvider.cs:44
var ebill = Factory.GetRepository<Ebill>().Query().FirstOrDefault(x => x.Bill.Id == bsv.Bill.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
    E.EBILL_SEQ_NUM,
    E.UPDATE_NUM,
    E.CLEARINGHOUSE_CONTROL_NUM,
    E.STATEMENT_FROM_DATE,
    E.STATEMENT_THRU_DATE,
    E.INJURY_DATE,
    E.ADMISSION_DATE,
    E.DISCHARGE_DATE,
    E.ORIGINAL_BILL_ID,
    E.BILL_TOTAL_AMOUNT,
    E.UB_BILL_TYPE_CODE,
    E.SERVICE_LOCATION_CODE,
    E.FACILITY_NPI,
    E.SUBMITTER_CONTROL_NUM,
    E.TRANSACTION_SET_CONTROL_NUM,
    E.UPDATE_USER,
    E.UPDATE_DATE,
    E.CLEARINGHOUSE_RECEIVED_DATE,
    E.CBP_ADMISSION_TYPE_CODE,
    E.BILL_ID,
    E.EBILL_RECEIPT_BATCH_SEQ_NUM,
    E.EBILL_PATIENT_SEQ_NUM,
    E.EBILL_STATUS_CODE,
    E.FACILITY_ADDRESS_SEQ_NUM,
    E.EBILL_SET_TYPE_CODE,
    E.EBILL_FIRST_RESPONSE_SEQ_NUM,
    E.EBILL_LAST_RESPONSE_SEQ_NUM,
    E.EBILL_RESPONSE_SET_SEQ_NUM,
    E.EBILL_EOB_SEQ_NUM
  FROM EBILL E
  WHERE E.BILL_ID = :bsvBillId
)
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). - The repository is obtained inline (Factory.GetRepository<Ebill>().Query()).

Operations and processes

• E-bill processing queries

Sources

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