SQL-286: Retrieve RctPolicyServicePlan via ProcessOneServicePlan

This NHibernate LINQ query reads LC_POLICY_SERVICE_PLAN filtered by policy number and finds the currently-active service plan row (one whose expiration date is null or the NHibernate default-date sentinel). RctServicePlanService.ProcessOneServicePlan uses the first matching row returned by the Oracle10 row-limit wrapper.

Classification

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

Parameters

Parameter	Direction	Type	Max length	Description
:PolicyNumber	Input	long	N/A	servicePlanChange.PolicyNumber

Tables

Table	Access	Columns used
LC_POLICY_SERVICE_PLAN	SELECT	POLICY_NUM, EFF_DATE, EXP_DATE, LC_SERVICE_PLAN_CODE, CREATE_USER, CREATED_DATE, UPDATE_USER, UPDATE_DATE

LINQ statement

Source: Services/SAIF.Services/Rct/RctServicePlanService.cs:160-162

// Services/SAIF.Services/Rct/RctServicePlanService.cs:160-162
var servicePlans = _unitOfWork.GetRepository<Model.Rct.RctPolicyServicePlan>().Query();
var activeServicePlan = servicePlans.FirstOrDefault(x => x.PolicyNumber == servicePlanChange.PolicyNumber
    && (x.ExpirationDate == null || x.ExpirationDate == DateTime.MinValue));

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
    LPSP.POLICY_NUM,
    LPSP.EFF_DATE,
    LPSP.EXP_DATE,
    LPSP.LC_SERVICE_PLAN_CODE,
    LPSP.CREATE_USER,
    LPSP.CREATED_DATE,
    LPSP.UPDATE_USER,
    LPSP.UPDATE_DATE
  FROM LC_POLICY_SERVICE_PLAN LPSP
  WHERE LPSP.POLICY_NUM = :PolicyNumber
    AND (LPSP.EXP_DATE IS NULL
      OR LPSP.EXP_DATE = TO_DATE('0001-01-01', 'YYYY-MM-DD'))
)
WHERE ROWNUM <= 1

Translation notes: - L160 builds the IQueryable; the statement executes at FirstOrDefault (L161). - DateTime.MinValue inlined as 0001-01-01. - First/FirstOrDefault → Oracle10 dialect row limit SELECT * FROM (...) WHERE ROWNUM <= 1 (no ORDER BY, so the row returned is not deterministic if more than one matches).

Operations and processes

• RctServicePlanService.ProcessOneServicePlan — see Operations and processes

Sources

• Services/SAIF.Services/Rct/RctServicePlanService.cs (L160)