---
title: "SQL-022: Endorse code lookup for renewal packets (EndorseCode)"
summary: Reads the output-document-type codes for correspondence-type E endorsements included in renewal packets.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Domain
reference_type: package
---

# SQL-022: Endorse code lookup for renewal packets (EndorseCode)

`PacketDefinition.GetDocCodesIncludedInRenewalPackets` queries `EndorseCode` for correspondence-type "E" endorsements and selects the associated output-document-type codes; because `OutputDocumentType.Code` is the referenced entity's identifier, the projection short-circuits to the `output_document_type_code` foreign-key column on `EndorseCode` with no join.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-022 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle* |
| **Schema** | [unqualified]** |
| **Command type** | N/A — ORM (NHibernate generates SQL at runtime from the ClassMaps in `Data/SAIF.Data.NHibernate/Mappings/**`) |
| **Business object** | `PacketDefinition` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `GetDocCodesIncludedInRenewalPackets` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [ENDORSE_CODE](../database-tables.md#endorse_code) | SELECT | `output_document_type_code`, `correspond_type_code` |

## LINQ statement

Source: `Domain/SAIF.Domain/Tasks/Documents/Packets/PacketDefinition.cs:448`

```csharp
// Domain/SAIF.Domain/Tasks/Documents/Packets/PacketDefinition.cs:447-450
var endorseDocCodes =
    unitOfWork.GetRepository<EndorseCode>()
        .Query()
        .Where(ec => ec.CorrespondTypeCode == "E" && ec.OutputDocumentType != null).Select(ec => ec.OutputDocumentType.Code).ToList();
```

## 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
  EC.output_document_type_code
FROM ENDORSE_CODE EC
WHERE EC.correspond_type_code = 'E'
  AND EC.output_document_type_code IS NOT NULL
```

Translation notes:
- Projection `Select(ec => ec.OutputDocumentType.Code)`: `Code` is the identifier of `OutputDocumentTypeCode` (`OutputDocuments/OutputDocumentTypeCodeBaseMap.cs:14`, `Id(m => m.Code)`), so it short-circuits to the FK `EC.output_document_type_code` (`EndorseCodeMap.cs:20`) — no join.
- `ec.OutputDocumentType != null` → `IS NOT NULL` on the same FK — no join.
- Literal `"E"` inlined. Column names keep the lower-case spelling declared in `EndorseCodeMap.cs:18-20` (Oracle folds unquoted identifiers).
- `ToList()` materializes; `returnVal.AddRange(...)` (line 452) is in memory.

## Operations and processes
- OP-DOM-19 Packets: PacketManager.CreatePackets / PacketGenerator — evaluates each PacketDefinition, reading EndorseCode for correspondence-type E (see [Operations and processes](../operations-processes.md))

## Business rules
- [DOM-BR-200](../business-rules.md#dom-br-200-packet-creation)
- [DOM-BR-201](../business-rules.md#dom-br-201-packet-creation)
- [DOM-BR-202](../business-rules.md#dom-br-202-packet-evaluation)
- [DOM-BR-203](../business-rules.md#dom-br-203-packet-evaluation)

> **Note:** Statements marked with `*` were inferred from code structure, naming
> conventions, or contextual evidence where direct confirmation was not available
> in source artifacts. Statements marked with `**` could not be confirmed; treat
> as unknown until verified.

## Sources
- Domain/SAIF.Domain/Tasks/Documents/Packets/PacketDefinition.cs