Skip to content

SQL-167: Retrieve DocumentTemplate

TemplateQuery and TemplateQueryFiltered expose IQueryable<DocumentTemplate> 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 == <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 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.

// Services/SAIF.Services/Documents/TemplateSearchService.cs:72
IQueryable<DocumentTemplate> allDocs = unitOfWork.GetRepository<DocumentTemplate>().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<DocumentTemplate> allDocs = unitOfWork.GetRepository<DocumentTemplate>().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.

-- 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 == <id>)
SELECT <DOCUMENT_TEMPLATE mapped columns as above>
FROM   DOCUMENT_TEMPLATE DT
WHERE  DT.DOCUMENT_TEMPLATE_SEQ_NUM = :id;
-- NHibernate expects at most one row

-- L344 — TemplateQuery.ToList(): SELECT <mapped columns> FROM DOCUMENT_TEMPLATE DT;

-- L514 — SingleOrDefault(a => a.Name.ToUpperInvariant() == changedTemplate.Name.ToUpperInvariant() && a.RunType.Code == changedTemplate.RunType)
SELECT <DOCUMENT_TEMPLATE mapped columns as above>
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

Sources

  • Services/SAIF.Services/Documents/TemplateSearchService.cs (L72, L88)