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	SELECT	`output_document_type_code`, `correspond_type_code`

LINQ statement¶

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

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

-- 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)

Business rules¶

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