---
title: "SQL-043: PolicyOutputDocumentRepository.GetPolicyOutputDocumentsForInbox"
summary: Read operation for PolicyOutputDocumentRepository
status: draft
owner: SAIF.Core documentation team
last_reviewed: 2026-09-24
library: SAIF.Data.NHibernate
reference_type: package
---


# SQL-043: PolicyOutputDocumentRepository.GetPolicyOutputDocumentsForInbox

This statement is a read operation invoked through ORM (NHibernate LINQ). Executed by `PolicyOutputDocumentRepository.GetPolicyOutputDocumentsForInbox` (2 overloads).

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:releaseDate` | Input | DateTime | N/A | Method argument bound into the LINQ expression |
| `:agencyId` | Input | long | N/A | Method argument bound into the LINQ expression |
| `:producerId` | Input | long | N/A | Method argument bound into the LINQ expression (second overload only) |

## 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 (LEFT OUTER, fetch) | `output_document_seq_num`, `update_num`, `image_seq_num`, `document_create_date`, `description`, `create_user`, `create_date`, `update_user`, `update_date`, `output_document_type_code` |
| [output_document_type_code](../database-tables.md#output_document_type_code) | JOIN (LEFT OUTER, fetch) | `code`, `code_descr`, `agency_view_flag`, `agency_inbox_flag`, `manual_add_flag`, `freeform_flag`, `sort_priority_num`, `SOURCE_SYSTEM_CODE`, `PACKAGE_NAME`, `image_file_code`, `image_category_code`, `image_document_type_code`, `output_document_category_code`, `Direct_Write_Notify_Text_Code` |
| [POLICY](../database-tables.md#policy) | JOIN (LEFT OUTER, fetch; two aliases — `P` = `Policy`, `P2` = `Policy.RelationshipPolicy`) | `POLICY_NUM`, `POLICY_NAME`, `LEGAL_NAME`, `SECURE_POLICY_CODE`, `OFFICE_CODE`, `ACCOUNT_NUM`, `POLICY_STATUS_CODE`, `MARKET_TYPE_CODE`, `POLICY_RELATIONSHIP_POLICY_NUM`, `POLICY_RELATIONSHIP_CODE`, `customer_service_team_code`, `ENTITY_CODE`, `TRADING_SEQ_NUM`, `RATE_ACCOUNT`, `AGENT_NUM` (both aliases) |
| [SUB_POLICY](../database-tables.md#sub_policy) | JOIN (LEFT OUTER, collection fetch) | `Sub_Policy_Seq_Num`, `SUBMISSION_STATUS_CODE`, `POLICY_NUM` |

## LINQ statement

Source: `Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:84` (three-argument overload) and `:89` (with `producerId`). Both call `ReleasedAgencyInboxViewableQuery` (`:94`), which composes `ReleasedAgencyViewablePolicyOutputDocumentQuery()` (`:159`) and `EagerLoadQuery()` (`:27`).

```csharp
public IEnumerable<PolicyOutputDocument> GetPolicyOutputDocumentsForInbox(ShowOnlyDocuments showOnlyDocuments, DateTime releaseDate, long agencyId)
{
    return ReleasedAgencyInboxViewableQuery(showOnlyDocuments, releaseDate, agencyId).ToList();
}

public IEnumerable<PolicyOutputDocument> GetPolicyOutputDocumentsForInbox(ShowOnlyDocuments showOnlyDocuments, DateTime releaseDate, long agencyId, long producerId)
{
    return ReleasedAgencyInboxViewableQuery(showOnlyDocuments, releaseDate, agencyId).ForProducer(producerId).ToList();
}

private IQueryable<PolicyOutputDocument> ReleasedAgencyInboxViewableQuery(ShowOnlyDocuments showOnlyDocuments, DateTime releaseDate, long agencyId)
{
    var query = ReleasedAgencyViewablePolicyOutputDocumentQuery().AgencyInboxViewable().ReleasedOnOrAfter(releaseDate).ForAgency(agencyId);

    switch (showOnlyDocuments)
    {
        case ShowOnlyDocuments.Unread:
            query = query.NonViewed().NotAgencyDeleted();
            break;
        case ShowOnlyDocuments.All:
            query = query.NotAgencyDeleted();
            break;
        case ShowOnlyDocuments.Deleted:
            query = query.AgencyDeleted();
            break;
        default:
            query = query.NonViewed().NotAgencyDeleted();
            break;
    }

    return query;
}

private IQueryable<PolicyOutputDocument> ReleasedAgencyViewablePolicyOutputDocumentQuery()
{
    return EagerLoadQuery().AgencyViewable().Released();
}

private IQueryable<PolicyOutputDocument> EagerLoadQuery()
{
    return Query()
            .Fetch(p => p.OutputDocument)
            .ThenFetch(o => o.DocumentType)
            .Fetch(p => p.Policy)
            .ThenFetch(p => p.SubmissionPolicies)
            .Fetch(p => p.Policy)
            .ThenFetch(p => p.RelationshipPolicy);
}

public static IQueryable<PolicyOutputDocument> AgencyViewable(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.OutputDocument.DocumentType.IsAgencyViewable);
}

public static IQueryable<PolicyOutputDocument> Released(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.DocumentStatus.Code == DocumentStatusCode.ReleasedDocumentStatusCode);
}

public static IQueryable<PolicyOutputDocument> AgencyInboxViewable(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.OutputDocument.DocumentType.IsAgencyInboxViewable);
}

public static IQueryable<PolicyOutputDocument> ReleasedOnOrAfter(this IQueryable<PolicyOutputDocument> query, DateTime date)
{
    return query.Where(p => p.ReleaseDate >= date);
}

public static IQueryable<PolicyOutputDocument> ForAgency(this IQueryable<PolicyOutputDocument> query, long agencyId)
{
    return query.Where(p => p.AgencyId == agencyId);
}

public static IQueryable<PolicyOutputDocument> NonViewed(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.AgencyViewed == false);
}

public static IQueryable<PolicyOutputDocument> NotAgencyDeleted(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.AgencyDeleteDate == null);
}

public static IQueryable<PolicyOutputDocument> AgencyDeleted(this IQueryable<PolicyOutputDocument> query)
{
    return query.Where(p => p.AgencyDeleteDate != null);
}

public static IQueryable<PolicyOutputDocument> ForProducer(this IQueryable<PolicyOutputDocument> query, long producerId)
{
    return query.Where(p => p.AgentId == producerId);
}
```

## 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
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,
  OD.output_document_seq_num,
  OD.update_num,
  OD.image_seq_num,
  OD.document_create_date,
  OD.description,
  OD.create_user,
  OD.create_date,
  OD.update_user,
  OD.update_date,
  OD.output_document_type_code,
  ODTC.code,
  ODTC.code_descr,
  ODTC.agency_view_flag,
  ODTC.agency_inbox_flag,
  ODTC.manual_add_flag,
  ODTC.freeform_flag,
  ODTC.sort_priority_num,
  ODTC.SOURCE_SYSTEM_CODE,
  ODTC.PACKAGE_NAME,
  ODTC.image_file_code,
  ODTC.image_category_code,
  ODTC.image_document_type_code,
  ODTC.output_document_category_code,
  ODTC.Direct_Write_Notify_Text_Code,
  P.POLICY_NUM,
  P.POLICY_NAME,
  P.LEGAL_NAME,
  P.SECURE_POLICY_CODE,
  P.OFFICE_CODE,
  P.ACCOUNT_NUM,
  P.POLICY_STATUS_CODE,
  P.MARKET_TYPE_CODE,
  P.POLICY_RELATIONSHIP_POLICY_NUM,
  P.POLICY_RELATIONSHIP_CODE,
  P.customer_service_team_code,
  P.ENTITY_CODE,
  P.TRADING_SEQ_NUM,
  P.RATE_ACCOUNT,
  P.AGENT_NUM,
  SP.Sub_Policy_Seq_Num,
  SP.SUBMISSION_STATUS_CODE,
  SP.POLICY_NUM,
  P2.POLICY_NUM,
  P2.POLICY_NAME,
  P2.LEGAL_NAME,
  P2.SECURE_POLICY_CODE,
  P2.OFFICE_CODE,
  P2.ACCOUNT_NUM,
  P2.POLICY_STATUS_CODE,
  P2.MARKET_TYPE_CODE,
  P2.POLICY_RELATIONSHIP_POLICY_NUM,
  P2.POLICY_RELATIONSHIP_CODE,
  P2.customer_service_team_code,
  P2.ENTITY_CODE,
  P2.TRADING_SEQ_NUM,
  P2.RATE_ACCOUNT,
  P2.AGENT_NUM
FROM policy_output_document POD
  LEFT OUTER JOIN output_document OD
    ON OD.output_document_seq_num = POD.output_document_seq_num
  LEFT OUTER JOIN output_document_type_code ODTC
    ON ODTC.code = OD.output_document_type_code
  LEFT OUTER JOIN POLICY P
    ON P.POLICY_NUM = POD.policy_num
  LEFT OUTER JOIN SUB_POLICY SP
    ON SP.POLICY_NUM = P.POLICY_NUM
  LEFT OUTER JOIN POLICY P2
    ON P2.POLICY_NUM = P.POLICY_RELATIONSHIP_POLICY_NUM
WHERE ODTC.agency_view_flag = 'Y'
  AND POD.document_status_code = 'RELE'
  AND ODTC.agency_inbox_flag = 'Y'
  AND POD.release_date >= :releaseDate
  AND POD.agency_seq_num = :agencyId
  -- showOnlyDocuments = Unread (and default):
  AND POD.agency_viewed_flag = 'N'
  AND POD.agency_delete_date IS NULL
  -- showOnlyDocuments = All: replace the two lines above with
  --   AND POD.agency_delete_date IS NULL
  -- showOnlyDocuments = Deleted: replace the two lines above with
  --   AND POD.agency_delete_date IS NOT NULL
  AND POD.agent_num = :producerId -- overload with producerId only
```

Translation notes:
- No `GetAccountNumber` statement is issued: the parameterless `ReleasedAgencyViewablePolicyOutputDocumentQuery()` used here has no policy filter.
- The `switch` runs in .NET while the query is composed. Only one predicate set is present in any given statement. The default branch is the same as `Unread`.
- `IsAgencyViewable` / `IsAgencyInboxViewable` / `AgencyViewed` are `YesNoType` flags, so a boolean member test becomes `= 'Y'` and `== false` becomes `= 'N'`.
- `EagerLoadQuery()` contributes the same five `LEFT OUTER JOIN`s described on SQL-036. The second `.Fetch(p => p.Policy)` reuses the same `POLICY P` join.
- `ThenFetch(p => p.SubmissionPolicies)` fetches a collection: without `Distinct()`, the materialized list can contain the same `PolicyOutputDocument` more than once per matching `SUB_POLICY` row.
- Where-clause navigation through `OutputDocument` / `OutputDocument.DocumentType` is written against the fetch aliases `OD` / `ODTC`. NHibernate 4.0 may emit a separate implicit (theta-style inner) join alias for the Where navigation in addition to the fetch join; the filtering meaning is the same.
- `p.DocumentStatus.Code` is the identifier of `DocumentStatusCode`, so it short-circuits to the FK `POD.document_status_code`; `document_status_code` is never joined.
- `OutputDocumentTypeCode.ImageDocumentTypes` (`HasManyToMany` via `output_doc_image_type_xref`) is lazy and never fetched or filtered, so `output_doc_image_type_xref` is **not** accessed. `ODTC.image_*` columns are selected only as the `References(ImageDocumentType)` FK; `image_document_type_code` is not joined.
- `POLICY.OFFICE_CODE` is mapped twice (`Map(OfficeCode)` and `References(Office)`) and is selected once. `Policy` `HasMany` collections other than `SubmissionPolicies` load lazily and are not selected.
- `OutputDocument.PolicyOutputDocument` (`HasOne(...).Cascade.All()`) is the inverse side of the same shared-PK one-to-one; the row is already in the session, so no additional statement is fabricated.

## Operations and processes

- [Policy output documents](../operations/policy-output-documents.md)

## Business rules

- [SDN-BR-060](../business-rules.md#sdn-br-060-agency-inbox%3A-unread-%3D-not-viewed-and-not-agency-deleted)
- [SDN-BR-061](../business-rules.md#sdn-br-061-agencies-see-only-released%2C-agency-viewable-documents)

> **Note:** Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources
- `Data/SAIF.Data.NHibernate/Repositories/OutputDocuments/PolicyOutputDocumentRepository.cs:89`