SQL-266: Retrieve Policy via ProcessAllServicePlans¶
For each row of a batch of RCT service-plan changes, this statement counts matching Policy rows by policy number to confirm the policy exists before the service-plan change is applied. It runs from RctServicePlanService.ProcessAllServicePlans via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Rct/RctServicePlanService.cs (L73).
Classification¶
| Field | Value |
|---|---|
| SQL ID | SQL-266 |
| 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 | ProcessAllServicePlans |
Parameters¶
| Parameter | Direction | Type | Max length | Description |
|---|---|---|---|---|
:PolicyNumber |
Input | long | N/A | servicePlanChange.PolicyNumber — the policy number being validated |
Tables¶
| Table | Access | Columns used |
|---|---|---|
| POLICY | SELECT | POLICY_NUM |
LINQ statement¶
Source: Services/SAIF.Services/Rct/RctServicePlanService.cs:73
// Services/SAIF.Services/Rct/RctServicePlanService.cs:73
var validPolicies = _unitOfWork.GetRepository<Model.Policies.Policy>().Query();
// Services/SAIF.Services/Rct/RctServicePlanService.cs:78-79
var policyIsValid =
validPolicies.Count(x => x.PolicyNumber == servicePlanChange.PolicyNumber) > 0;
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 POLICY P
WHERE P.POLICY_NUM = :PolicyNumber
Translation notes:
- L73 only builds IQueryable<Policy>; the statement executes at the terminal Count(...) (L79), once per iteration of the foreach over rctServicePlanChangeList.
- > 0 is evaluated in memory on the returned count.
Operations and processes¶
- RctServicePlanService.ProcessAllServicePlans — see Operations and processes
Business rules¶
- SVC-BR-POL-100 Service plan policy must exist - 'Error - The PolicyNumber '{n}' is not valid'
- SVC-BR-POL-101
- SVC-BR-POL-102 A per-row processing failure rolls back the whole batch
Sources¶
Services/SAIF.Services/Rct/RctServicePlanService.cs (L73)