SQL-255: Retrieve PharmacyDispenseAsWrittenCode

BillServiceService.CreateService and UpdateService look up a single PharmacyDispenseAsWrittenCode row by its code, using a LINQ query over an NHibernate-backed repository. NHibernate translates this expression into the row-limited SELECT shown below at execution time.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:PharmacyDispenseAsWrittenCode	Input	string	N/A	newService.PharmacyDispenseAsWrittenCode (CreateService) / updatedServiceLine.PharmacyDispenseAsWrittenCode (UpdateService) — the DAW code to look up

Tables

Table	Access	Columns used
CL_PHARMACY_DAW_CODE	SELECT	CODE, UPDATE_NUM, CODE_DESCR, UPDATE_DATE, UPDATE_USER

LINQ statement

Source: Services/SAIF.Services/Claims/Bills/BillServiceService.cs:59

// Services/SAIF.Services/Claims/Bills/BillServiceService.cs:59
newServiceLine.PharmacyDispenseAsWrittenCode = unitOfWork.GetRepository<PharmacyDispenseAsWrittenCode>().Query().FirstOrDefault(x => x.Code == newService.PharmacyDispenseAsWrittenCode);

// Services/SAIF.Services/Claims/Bills/BillServiceService.cs:126-127
dataServiceLine.PharmacyDispenseAsWrittenCode = updatedServiceLine.PharmacyDispenseAsWrittenCode.IsNull() ? null : unitOfWork.GetRepository<PharmacyDispenseAsWrittenCode>()
    .Query().FirstOrDefault(x => x.Code == updatedServiceLine.PharmacyDispenseAsWrittenCode);

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
  CPDC.CODE,
  CPDC.UPDATE_NUM,
  CPDC.CODE_DESCR,
  CPDC.UPDATE_DATE,
  CPDC.UPDATE_USER
  FROM CL_PHARMACY_DAW_CODE CPDC
  WHERE CPDC.CODE = :PharmacyDispenseAsWrittenCode
)
WHERE ROWNUM <= 1

Translation notes: - Both call sites have the same shape; L126 only runs when the incoming code is not null (ternary in C#, not a SQL predicate).

Operations and processes

• BillServiceService.CreateService — see Operations and processes
• BillServiceService.UpdateService — see Operations and processes

Business rules

• SVC-BR-BIL-120 ProviderServiceCode NotNull, length 1-11 - 'Error - Invalid Primary service code, value is too long.'
• SVC-BR-BIL-121 SecondaryProvServiceCode ≤11 - 'Error - Invalid Secondary service code, value is too long.'
• SVC-BR-BIL-122 DiagnosisPointerCode1-4 ≤1 char - 'Error - Invalid Diagnosis Pointer Code.'
• SVC-BR-BIL-123 Pointers unique - 'Error - Diagnosis Pointers must be unique.'
• SVC-BR-BIL-124
• SVC-BR-BIL-125 EndDate ≥ ServiceDate - 'Error - Service End date must be a valid date.'
• SVC-BR-BIL-126 PrescriptionWrittenDate > MinValue - 'Error - Prescription Written date must be a valid date.'
• SVC-BR-BIL-127 Modifiers 1-4 ≤2 - 'Error - Modifiers can only be up to two characters.'
• SVC-BR-BIL-128 ServiceLocationCode ≤2 - 'Error - Service location code must be 2 digits long.'
• SVC-BR-BIL-129 Quantity ≥0 - 'Error - Service quantity can not be negative.'
• SVC-BR-BIL-130 ToothNumber 0-32 - 'Error - Invalid tooth number.'
• SVC-BR-BIL-131 DaysSupply 0-99999 - 'Error - Days supply quantity must be a number.'
• SVC-BR-BIL-132 DAW length 1 - 'Error - Invalid DAW, can only be 1 character long.'
• SVC-BR-BIL-133 PrescribingProviderId ≤10 - 'Error - Invalid Prescriber ID, can only be up to 10 characters.'

Sources

• Services/SAIF.Services/Claims/Bills/BillServiceService.cs (L59, L126)