SQL-269: Retrieve PolicyPeriodCoverageBind via GetPolicyPeriodCoverageBind¶

This statement loads the single PolicyPeriodCoverageBind row matching a policy number and policy period effective date, used to determine the coverage bind cancel date for a certificate. It runs via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L512).

Classification¶

Field	Value
SQL ID	SQL-269
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	`GetPolicyPeriodCoverageBind`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:policyNumber`	Input	long	N/A	policy number to match
`:policyPeriodEffectiveDate`	Input	DateTime	N/A	policy period effective date to match

Tables¶

Table	Access	Columns used
POLICY_PERIOD_COVERAGE_BIND	SELECT	`POLICY_NUM`, `POLICY_PERIOD_EFF_DATE`, `BIND_CANCEL_DATE`

LINQ statement¶

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

// Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:512-515
return unitOfWork.GetRepository<PolicyPeriodCoverageBind>()
        .Query()
        .SingleOrDefault(a => a.PolicyNumber == policyNumber &&
                              a.PolicyPeriodEffectiveDate == policyPeriodEffectiveDate);

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
  PPCB.POLICY_NUM,
  PPCB.POLICY_PERIOD_EFF_DATE,
  PPCB.BIND_CANCEL_DATE
FROM POLICY_PERIOD_COVERAGE_BIND PPCB
WHERE PPCB.POLICY_NUM = :policyNumber
  AND PPCB.POLICY_PERIOD_EFF_DATE = :policyPeriodEffectiveDate
-- NHibernate expects at most one row

Translation notes: - CompositeId key properties (PolicyPeriodCoverageBindMap.cs:14-16) are plain columns. - Single/SingleOrDefault → no row limit; NHibernate throws if more than one row is returned.

Operations and processes¶

CertificateInformationService.GetPolicyPeriodCoverageBind — see Operations and processes

Sources¶

Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L512)