SQL-268: Retrieve PolicyOutputDocument

This statement finds PolicyOutputDocument rows for two batch scenarios: pending documents whose linked output document date is at or before the release date (ReleaseDocuments), and already-released, agency-inbox-viewable, unviewed documents tied to an agency (GenerateAllExternalAgencyNotifications). It runs via IUnitOfWork.GetRepository<T>(), executed from Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs (L83, L158).

Classification

Field	Value
SQL ID	SQL-268
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	ReleaseDocuments, GenerateAllExternalAgencyNotifications

Parameters

Parameter	Direction	Type	Max length	Description
:releaseDateDate	Input	DateTime	N/A	releaseDate.Date (client-evaluated) — upper bound for the output document date (L83)
:releaseDate	Input	DateTime	N/A	method argument — release date to match (L158)

Tables

Table	Access	Columns used
POLICY_OUTPUT_DOCUMENT	SELECT	output_document_seq_num, update_num, policy_period_eff_date, agency_seq_num, agent_num, agency_viewed_flag, agency_delivery_flag, agency_delete_date, create_user, create_date, update_user, update_date, release_date, hold_task_seq_num, task_user, source_key_text, ACCOUNT_NUM, policy_num, document_status_code
OUTPUT_DOCUMENT	JOIN	output_document_seq_num, document_create_date (L83), output_document_type_code (L158)
OUTPUT_DOCUMENT_TYPE_CODE	JOIN (L158 only)	code, agency_inbox_flag

LINQ statement

Source: Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs:83

// Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs:83-86
var unreleasedDocuments = unitOfWork.GetRepository<PolicyOutputDocument>().Query()
    .Pending()
    .Where(x => x.OutputDocument.DocumentDate.Date <= releaseDate.Date)
    .OrderBy(x => x.Policy.PolicyNumber).ThenBy(x => x.Id).ToList();

// Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs:158-163
var releasedDocuments = unitOfWork.GetRepository<PolicyOutputDocument>().Query()
    .Released()
    .ForReleaseDate(releaseDate)
    .AgencyInboxViewable()
    .NonViewed()
    .Where(a => a.AgencyId != null && a.AgencyId != 0).ToList();

// Data/SAIF.Data/Queries/OutputDocuments/PolicyOutputDocumentExtensions.cs:37-40
public static IQueryable<PolicyOutputDocument> Released(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.DocumentStatus.Code == DocumentStatusCode.ReleasedDocumentStatusCode);
}

// Data/SAIF.Data/Queries/OutputDocuments/PolicyOutputDocumentExtensions.cs:47-50
public static IQueryable<PolicyOutputDocument> AgencyInboxViewable(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.OutputDocument.DocumentType.IsAgencyInboxViewable);
}

// Data/SAIF.Data/Queries/OutputDocuments/PolicyOutputDocumentExtensions.cs:57-60
public static IQueryable<PolicyOutputDocument> Pending(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(x => x.DocumentStatus.Code == DocumentStatusCode.PendingDocumentStatusCode);
}

// Data/SAIF.Data/Queries/OutputDocuments/PolicyOutputDocumentExtensions.cs:82-85
public static IQueryable<PolicyOutputDocument> NonViewed(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.AgencyViewed == false);
}

// Data/SAIF.Data/Queries/OutputDocuments/PolicyOutputDocumentExtensions.cs:112-115
public static IQueryable<PolicyOutputDocument> ForReleaseDate(this IQueryable<PolicyOutputDocument> query, DateTime releaseDate)
{
    return query.Where(p => p.ReleaseDate == releaseDate);
}

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
-- ReleaseDocuments (L83): Pending() + document-date filter
SELECT
  POD.output_document_seq_num,
  POD.update_num,
  POD.policy_period_eff_date,
  POD.agency_seq_num,
  POD.agent_num,
  POD.agency_viewed_flag,
  POD.agency_delivery_flag,
  POD.agency_delete_date,
  POD.create_user,
  POD.create_date,
  POD.update_user,
  POD.update_date,
  POD.release_date,
  POD.hold_task_seq_num,
  POD.task_user,
  POD.source_key_text,
  POD.ACCOUNT_NUM,
  POD.policy_num,
  POD.document_status_code
FROM policy_output_document POD
  INNER JOIN output_document OD
    ON OD.output_document_seq_num = POD.output_document_seq_num
WHERE POD.document_status_code = 'PEND'
  AND TRUNC(OD.document_create_date) <= :releaseDateDate
ORDER BY POD.policy_num, POD.output_document_seq_num

-- GenerateAllExternalAgencyNotifications (L158): Released() + ForReleaseDate() + AgencyInboxViewable() + NonViewed() + agency filter
SELECT
  POD.output_document_seq_num,
  POD.update_num,
  POD.policy_period_eff_date,
  POD.agency_seq_num,
  POD.agent_num,
  POD.agency_viewed_flag,
  POD.agency_delivery_flag,
  POD.agency_delete_date,
  POD.create_user,
  POD.create_date,
  POD.update_user,
  POD.update_date,
  POD.release_date,
  POD.hold_task_seq_num,
  POD.task_user,
  POD.source_key_text,
  POD.ACCOUNT_NUM,
  POD.policy_num,
  POD.document_status_code
FROM policy_output_document POD
  INNER JOIN output_document OD
    ON OD.output_document_seq_num = POD.output_document_seq_num
  INNER JOIN output_document_type_code ODTC
    ON ODTC.code = OD.output_document_type_code
WHERE POD.document_status_code = 'RELE'
  AND POD.release_date = :releaseDate
  AND ODTC.agency_inbox_flag = 'Y'
  AND POD.agency_viewed_flag = 'N'
  AND (POD.agency_seq_num IS NOT NULL
    AND POD.agency_seq_num <> 0)

Translation notes: - Extension methods resolved from Data/SAIF.Data/Queries/OutputDocuments/PolicyOutputDocumentExtensions.cs (imported by using SAIF.Data.Queries.OutputDocuments; at PolicyDocumentsBatchService.cs:26): Released (L37-40), AgencyInboxViewable (L47-50), Pending (L57-60), NonViewed (L82-85), ForReleaseDate (L112-115). Each adds a Where; chained Wheres combine with AND. - Constants: DocumentStatusCode.PendingDocumentStatusCode = "PEND" (Data/SAIF.Data/Model/OutputDocuments/DocumentStatusCode.cs:5), ReleasedDocumentStatusCode = "RELE" (L8). - YesNoType booleans: IsAgencyInboxViewable (agency_inbox_flag, OutputDocumentTypeCodeBaseMap.cs:18) → 'Y'; AgencyViewed == false (agency_viewed_flag) → 'N'. - x.OutputDocument.DocumentDate.Date references the entity → TRUNC(...); releaseDate.Date does not → bound value. - NHibernate may render the implicit joins as theta-style (FROM a, b WHERE b.key = a.key) rather than INNER JOIN; semantics are identical. - .ToList() is the materialization point in both statements.

Operations and processes

• PolicyDocumentsBatchService.GenerateAllExternalAgencyNotifications — see Operations and processes
• PolicyDocumentsBatchService.ReleaseDocuments — see Operations and processes

Business rules

• SVC-BR-POL-091 Release pending documents with DocumentDate ≤ document system date
• SVC-BR-POL-092 Direct-writer notifications only if SAIF agency IsPaperlessForPolicyHolderCopyOfDocuments

Sources

• Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs (L83, L158)