--- title: "SQL-167: Retrieve DocumentTemplate" summary: Read operation against DocumentTemplate via NHibernate ORM. status: draft owner: SAIF Doc Gen last_reviewed: 2026-09-24 library: SAIF.Services reference_type: package --- # SQL-167: Retrieve DocumentTemplate `TemplateQuery` and `TemplateQueryFiltered` expose `IQueryable` properties over `DOCUMENT_TEMPLATE`. `TemplateQueryFiltered` restricts to currently effective, enabled templates (excluding admin-only groups unless the caller may see them) and is composed further for name/title search; `TemplateQuery` is the unfiltered base used to look up individual templates by ID, list all templates, or validate a run-type change. ## Classification | Field | Value | | --- | --- | | **SQL ID** | SQL-167 | | **Operation type** | Read | | **Execution method** | ORM (NHibernate LINQ) | | **Database** | Oracle | | **Schema** | [unqualified]** | | **Command type** | N/A (ORM-generated) | | **Business object** | `DocumentTemplate` | | **Criteria property** | N/A — ORM | | **Lifecycle method** | `TemplateQuery (property), TemplateQueryFiltered (property)` | ## Parameters | Parameter | Direction | Type | Max length | Description | | --- | --- | --- | --- | --- | | `:now` | Input | DateTime | N/A | `DateTime.Now` → columns EFFECTIVE_DATE, EXPIRATION_DATE | | `:searchTermUpper` | Input | string | N/A | `= searchTerm.ToUpper()` → columns TEMPLATE_NAME, TEMPLATE_TITLE | | `:id` | Input | long | N/A | `a.Id == ` → column DOCUMENT_TEMPLATE_SEQ_NUM | | `:changedNameUpper` | Input | string | N/A | `= changedTemplate.Name.ToUpperInvariant()` → column TEMPLATE_NAME | | `:changedRunType` | Input | string | N/A | `= changedTemplate.RunType` → column RUN_TYPE_CODE | ## Tables | Table | Access | Columns used | | --- | --- | --- | | [DOCUMENT_TEMPLATE](../database-tables.md#document_template) | SELECT | all mapped columns; filters EFFECTIVE_DATE, EXPIRATION_DATE, ENABLED_FLAG, BUSINESS_GROUP_DESCRIPTION, SUB_GROUP_DESCRIPTION, TEMPLATE_NAME, TEMPLATE_TITLE, DOCUMENT_TEMPLATE_SEQ_NUM, RUN_TYPE_CODE | ## LINQ statement Source: `Services/SAIF.Services/Documents/TemplateSearchService.cs:72`. Additional fragments at `:73`, `:74`, `:77`, `:238`, `:265`, `:88`, `:337`, `:514`. ```csharp // Services/SAIF.Services/Documents/TemplateSearchService.cs:72 IQueryable allDocs = unitOfWork.GetRepository().Query(); // Services/SAIF.Services/Documents/TemplateSearchService.cs:73 allDocs = allDocs.Where(x => (x.EffectiveDate <= DateTime.Now || !x.EffectiveDate.HasValue) && (x.ExpirationDate > DateTime.Now || !x.ExpirationDate.HasValue)); // Services/SAIF.Services/Documents/TemplateSearchService.cs:74 allDocs = allDocs.Where(x => x.IsEnabled); // Services/SAIF.Services/Documents/TemplateSearchService.cs:77 allDocs = allDocs.Where(x => x.BusinessGroup != AdminOnlyBusinessGroup || x.Subgroup != AdminOnlySubgroup); // Services/SAIF.Services/Documents/TemplateSearchService.cs:238 var templates = TemplateQueryFiltered .Where(a => a.Name.ToUpper().Contains(searchTerm.ToUpper()) || a.Title.ToUpper().Contains(searchTerm.ToUpper())) .ToList(); // Services/SAIF.Services/Documents/TemplateSearchService.cs:265 var templates = TemplateQueryFiltered.ToList(); // Services/SAIF.Services/Documents/TemplateSearchService.cs:88 IQueryable allDocs = unitOfWork.GetRepository().Query(); // Services/SAIF.Services/Documents/TemplateSearchService.cs:337 DocumentTemplate template = TemplateQuery.SingleOrDefault(a => a.Id == templateId); // + 30000 // Services/SAIF.Services/Documents/TemplateSearchService.cs:514 DocumentTemplate checkRunType = TemplateQuery.SingleOrDefault(a => a.Name.ToUpperInvariant() == changedTemplate.Name.ToUpperInvariant() && a.RunType.Code == changedTemplate.RunType); ``` ## 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 -- TemplateQueryFiltered (L72-78) composed with consumer L238 (GetSearchResults) SELECT DT.DOCUMENT_TEMPLATE_SEQ_NUM, DT.TEMPLATE_NAME, DT.TEMPLATE_TITLE, DT.RUN_TEMPLATE_NAME, DT.RUN_ROUTINE_NAME, DT.RUN_PARAMETER_FORM_NAME, DT.NEXT_DOC_TEMPLATE_SEQ_NUM, DT.ADDITIONAL_PAGE_NUM, DT.BUSINESS_GROUP_DESCRIPTION, DT.SUB_GROUP_DESCRIPTION, DT.ENABLED_FLAG, DT.EFFECTIVE_DATE, DT.EXPIRATION_DATE, DT.NOTE_TXT, DT.IMAGE_FILE_CODE, DT.IMAGE_CATEGORY_CODE, DT.IMAGE_DOCUMENT_TYPE_CODE, DT.IMAGE_DOCUMENT_NAME_CODE, DT.IMAGE_DOCUMENT_NAME, DT.IMAGE_REQUIRED_FLAG, DT.SHARED_MESSAGE_TXT, DT.MAIN_BANNER_PAGE_TXT, DT.ADDITIONAL_BANNER_PAGE_TXT, DT.UPDATE_NUM, DT.UPDATE_USER, DT.UPDATE_DATE, DT.CREATE_USER, DT.CREATE_DATE, DT.RUN_TYPE_CODE, DT.CONTEXT_CODE FROM DOCUMENT_TEMPLATE DT WHERE (DT.EFFECTIVE_DATE <= :now OR DT.EFFECTIVE_DATE IS NULL) AND (DT.EXPIRATION_DATE > :now OR DT.EXPIRATION_DATE IS NULL) AND DT.ENABLED_FLAG = 'Y' -- conditional (L75-78): only when !userCanSeeAll(currentUser) AND (DT.BUSINESS_GROUP_DESCRIPTION <> 'Corporate' OR DT.SUB_GROUP_DESCRIPTION <> 'Admin') -- consumer L238-240 AND (UPPER(DT.TEMPLATE_NAME) LIKE '%' || :searchTermUpper || '%' OR UPPER(DT.TEMPLATE_TITLE) LIKE '%' || :searchTermUpper || '%'); -- Consumer L265: same statement without the L239-240 search predicate. -- TemplateQuery (L88) consumers: -- L337 / L532 / L720 / L895 / L915 / L941 — SingleOrDefault(a => a.Id == ) SELECT FROM DOCUMENT_TEMPLATE DT WHERE DT.DOCUMENT_TEMPLATE_SEQ_NUM = :id; -- NHibernate expects at most one row -- L344 — TemplateQuery.ToList(): SELECT FROM DOCUMENT_TEMPLATE DT; -- L514 — SingleOrDefault(a => a.Name.ToUpperInvariant() == changedTemplate.Name.ToUpperInvariant() && a.RunType.Code == changedTemplate.RunType) SELECT FROM DOCUMENT_TEMPLATE DT WHERE UPPER(DT.TEMPLATE_NAME) = :changedNameUpper AND DT.RUN_TYPE_CODE = :changedRunType; -- NHibernate expects at most one row ``` Translation notes: - L72 and L88 are queryable-property definitions; SQL is emitted by consumers (TemplateQueryFiltered: L238, L265; TemplateQuery: L337, L344, L514, L532, L720, L895, L915, L941). - `DateTime.Now` is evaluated client-side by NHibernate and sent as a bind parameter (`:now`); it is not translated to `SYSDATE`. - `ENABLED_FLAG` is `YesNoType` → `= 'Y'`. - `AdminOnlyBusinessGroup`="Corporate", `AdminOnlySubgroup`="Admin". C# `const` values are shown as literals for readability; NHibernate LINQ sends them as bind parameters. - `string.Contains` → `LIKE '%' || :p || '%'` (NHibernate generates the concatenated pattern). - `ToUpperInvariant()` is translated by NHibernate like `ToUpper()` → `UPPER()`; the right-hand side is captured and evaluated client-side. - `a.RunType.Code` (L514) is the identifier of the referenced `RunTypeCode`, resolved to FK column `DOCUMENT_TEMPLATE.RUN_TYPE_CODE`; the `RUN_TYPE_CODE` code table is never joined. ## Operations and processes - TemplateSearchService.TemplateQuery (property) — see [Operations and processes](../operations-processes.md) - TemplateSearchService.TemplateQueryFiltered (property) — see [Operations and processes](../operations-processes.md) ## Sources - `Services/SAIF.Services/Documents/TemplateSearchService.cs (L72, L88)`