---
title: "SQL-268: Retrieve PolicyOutputDocument"
summary: Read operation against PolicyOutputDocument via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# 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](../database-tables.md#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](../database-tables.md#output_document) | JOIN | `output_document_seq_num`, `document_create_date` (L83), `output_document_type_code` (L158) |
| [OUTPUT_DOCUMENT_TYPE_CODE](../database-tables.md#output_document_type_code) | JOIN (L158 only) | `code`, `agency_inbox_flag` |

## LINQ statement

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

```csharp
// 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.

```sql
-- 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 `Where`s 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](../operations-processes.md)
- PolicyDocumentsBatchService.ReleaseDocuments — see [Operations and processes](../operations-processes.md)

## Business rules
- [SVC-BR-POL-091 Release pending documents with DocumentDate ≤ document system date](../business-rules.md#svc-br-pol-091-release-pending-documents-with-documentdate-%E2%89%A4-document-system-date)
- [SVC-BR-POL-092 Direct-writer notifications only if SAIF agency IsPaperlessForPolicyHolderCopyOfDocuments](../business-rules.md#svc-br-pol-092-direct-writer-notifications-only-if-saif-agency-ispaperlessforpolicyholdercopyofdocuments)

## Sources
- `Services/SAIF.Services/OutputDocuments/PolicyDocumentsBatchService.cs (L83, L158)`