Skip to content

SQL-240: Retrieve OtherStatesPolicyPeriod via HasOscPeriodForPolicyPeriod

This ORM statement checks whether an other-states-coverage (OSC) policy period exists for a given policy number that covers a given effective date, via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L497).

Classification

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

Parameters

Parameter Direction Type Max length Description
:policyNumber Input long N/A Policy number matched against OSC_POLICY_VIEW.POLICY_NUM
:effectiveDateDate Input DateTime N/A effectiveDate.Date, evaluated client-side
:effectiveDate Input DateTime N/A The candidate effective date

Tables

Table Access Columns used
OSC_POLICY_PERIOD SELECT OSC_POLICY_SEQ_NUM, OSC_POLICY_PERIOD_EFF_DATE, OSC_POLICY_PERIOD_EXP_DATE
OSC_POLICY_VIEW JOIN OSC_POLICY_SEQ_NUM, POLICY_NUM

LINQ statement

Source: Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:497

// Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:497-501
return unitOfWork.GetRepository<OtherStatesPolicyPeriod>()
    .Query()
    .Any(osc => osc.Policy.Policy.PolicyNumber == policyNumber &&
                        (osc.EffectiveDate >= effectiveDate.Date &&
                          effectiveDate < osc.ExpirationDate.Date));

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 1
  FROM OSC_POLICY_PERIOD OPP
    INNER JOIN OSC_POLICY_VIEW OPV
      ON OPV.OSC_POLICY_SEQ_NUM = OPP.OSC_POLICY_SEQ_NUM
  WHERE OPV.POLICY_NUM = :policyNumber
    AND (OPP.OSC_POLICY_PERIOD_EFF_DATE >= :effectiveDateDate
      AND :effectiveDate < TRUNC(OPP.OSC_POLICY_PERIOD_EXP_DATE))
)
WHERE ROWNUM <= 1

Translation notes: - Expressions that do not reference the lambda parameter (for example x.Trim().ToLower() on a method argument, ToString(), AddDays(...), .Date on a captured value) are evaluated client-side by NHibernate's partial evaluator and bound as a single parameter value. effectiveDate.Date is bound as :effectiveDateDate (root-prefixed because effectiveDate itself is also bound). - osc.ExpirationDate.Date references the entity and is translated to TRUNC(...) (Oracle dialect date function). - Terminal Any() → SELECT 1 with ROWNUM <= 1 per skill; NHibernate may render it as CASE WHEN EXISTS (...). - osc.Policy.Policy.PolicyNumber: the first hop (OtherStatesPolicyPeriod.Policy → OtherStatesPolicy, FK OSC_POLICY_SEQ_NUM) must be joined because the next member (Policy) is a non-identifier property of OtherStatesPolicy; the second hop reads PolicyNumber, which is the identifier of Policy, so it short-circuits to OPV.POLICY_NUM and POLICY is never joined.

Operations and processes

Sources

  • Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L497)