SQL-022: AuditQueryProvider.GetPhysicalMedicineCount

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

Classification

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

Parameters

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

Tables

Table	Access	Columns used
CBP_SERVICE_PHYSICAL_MEDICINE	SELECT (subquery)	CBP_SERVICE_CODE
CBP_AUDITED_SERVICE	SELECT (outer)	BILL_ID, SERVICE_DATE, BILL_SERVICE_CODE
CBP_BILL	JOIN (outer)	BILL_ID, CLAIM_ID

LINQ statement

Source: Data/SAIF.Data/Queries/AuditQueryProvider.cs:144, the subquery portion of the same statement as SQL-021 (:142-149).

Factory.GetRepository<CbpServicePhysicalMedicine>().Query()
  .Select(y => y.CbpServiceCode).Distinct()
  .Contains(x.BillServiceCode))

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 COUNT(*)
FROM (
  SELECT DISTINCT CAS.SERVICE_DATE
  FROM CBP_AUDITED_SERVICE CAS
    INNER JOIN CBP_BILL CB
      ON CB.BILL_ID = CAS.BILL_ID
  WHERE CB.CLAIM_ID = :claimId
    AND CAS.BILL_SERVICE_CODE IN (
      SELECT DISTINCT CSPM.CBP_SERVICE_CODE
      FROM CBP_SERVICE_PHYSICAL_MEDICINE CSPM
    )
)

Translation notes: - This fragment is not a separate round trip; it is embedded inside the SQL-021 statement (services.Count() at :149). - SQL-022 documents the same physical statement as SQL-021; see that page for the full inferred SQL and translation notes.

Operations and processes

• Bill audit evaluation

Business rules

• SD-BR-030 Nurse review required if any audited service code flags it
• SD-BR-031 Duplicate services excluded from count
• SD-BR-032 Unset thru-date treated as open-ended

Sources

• Data/SAIF.Data/Queries/AuditQueryProvider.cs:142