SQL-271: Retrieve PolicyTeamFunction via GetSaifContactData¶

This statement loads the single PolicyTeamFunction row for a policy and team function code, used to look up SAIF contact information for a certificate. It runs via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs (L528).

Classification¶

Field	Value
SQL ID	SQL-271
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	`GetSaifContactData`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:policyNumber`	Input	long	N/A	policy number to match
`:teamFunctionCode`	Input	string	N/A	team function code to match

Tables¶

Table	Access	Columns used
POLICY_TEAM_FUNCTION	SELECT	`POLICY_NUM`, `TEAM_FUNCTION_CODE`, `UPDATE_DATE`, `UPDATE_USER`, `USER_NAME`

LINQ statement¶

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

// Services/SAIF.Services/Policies/Certificates/Information/CertificateInformationService.cs:528-530
return unitOfWork.GetRepository<PolicyTeamFunction>()
        .Query()
        .SingleOrDefault(t => t.PolicyNumber == policyNumber && t.TeamFunctionCode == teamFunctionCode);

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
  PTF.POLICY_NUM,
  PTF.TEAM_FUNCTION_CODE,
  PTF.UPDATE_DATE,
  PTF.UPDATE_USER,
  PTF.USER_NAME
FROM POLICY_TEAM_FUNCTION PTF
WHERE PTF.POLICY_NUM = :policyNumber
  AND PTF.TEAM_FUNCTION_CODE = :teamFunctionCode
-- NHibernate expects at most one row

Translation notes: - Single/SingleOrDefault → no row limit; NHibernate throws if more than one row is returned.

Operations and processes¶

CertificateInformationService.GetSaifContactData — see Operations and processes

Sources¶

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