SQL-272: Retrieve PolicyTeamFunction via CreateNotificationTask¶

This statement loads the single PolicyTeamFunction row for a policy and the retention marketing rep team function code, then reads the associated employee's identifier to create a notification task. It runs via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs (L378).

Classification¶

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

Parameters¶

Parameter	Direction	Type	Max length	Description
`:policyNumber`	Input	long	N/A	policy number 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/OutputDocuments/PolicyDocumentsBatchService.cs:378

// Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs:378-382
var marketingRepUserId = unitOfWork.GetRepository<PolicyTeamFunction>()
    .Query()
    .Single(ptf => ptf.PolicyNumber == policyNumber && ptf.TeamFunctionCode == TeamFunctionCode.RetentionMarketingRepCode)
    .WcisEmployee
    .Id;

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 = 'MKTRP'
-- NHibernate expects at most one row

Translation notes: - TeamFunctionCode.RetentionMarketingRepCode = "MKTRP" (Data/SAIF.Data/Model/Employees/TeamFunctionCode.cs:7) inlined as a literal. - Single throws when no row matches.

Operations and processes¶

PolicyDocumentsBatchService.CreateNotificationTask — see Operations and processes

Sources¶

Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs (L378)