---
title: "SQL-164: Retrieve DocumentRequestLog"
summary: Read operation against DocumentRequestLog via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-164: Retrieve DocumentRequestLog

This statement reads `DocumentRequestLog` rows two ways: `GetRecentRequests` lists a user's requests from the last three months in descending create-date order, and `DeleteTemplateRequests` finds every request tied to a given template so they can be removed.

## Classification

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

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:currentUserIdUpper` | Input | string | N/A | `= currentUser.UserId.ToUpper()`, client-side → column CREATE_USER |
| `:threeMonthsAgo` | Input | DateTime | N/A | `= DateTime.Now.AddMonths(-3)`, client-side → column CREATE_DATE |
| `:templateId` | Input | long | N/A | `a.MyDocTemplate.Id == templateId` → column DOCUMENT_TEMPLATE_SEQ_NUM |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [DOCUMENT_REQUEST_LOG](../database-tables.md#document_request_log) | SELECT | all mapped columns; filters CREATE_USER, CREATE_DATE, DOCUMENT_TEMPLATE_SEQ_NUM |

## LINQ statement

Source: `Services/SAIF.Services/Documents/TemplateSearchService.cs:183`. Second fragment at `Services/SAIF.Services/Documents/TemplateSearchService.cs:873`.

```csharp
// Services/SAIF.Services/Documents/TemplateSearchService.cs:183
IEnumerable<DocumentRequestLog> requests = unitOfWork.GetRepository<DocumentRequestLog>().Query()
    .Where(a => a.CreateUser.ToUpper() == currentUser.UserId.ToUpper() && a.CreateDate > System.DateTime.Now.AddMonths(-3))
    .OrderByDescending(a => a.CreateDate).ToList();   //this sort uses hardly any time.

// Services/SAIF.Services/Documents/TemplateSearchService.cs:873
IEnumerable<DocumentRequestLog> results = unitOfWork.GetRepository<DocumentRequestLog>().Query()
                                          .Where(a => a.MyDocTemplate.Id == templateId);
```

## 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
-- (1) TemplateSearchService.cs:183 — GetRecentRequests
SELECT DRL.DOCUMENT_REQUEST_LOG_SEQ_NUM,
       DRL.DOC_REQUEST_LOG_STATUS_CODE,
       DRL.SAIF_MAIL_ID,
       DRL.CREATE_USER,
       DRL.CREATE_DATE,
       DRL.UPDATE_USER,
       DRL.UPDATE_DATE,
       DRL.UPDATE_NUM,
       DRL.CONTEXT_VALUE,
       DRL.DOCUMENT_TEMPLATE_SEQ_NUM
FROM   DOCUMENT_REQUEST_LOG DRL
WHERE  UPPER(DRL.CREATE_USER) = :currentUserIdUpper
  AND  DRL.CREATE_DATE > :threeMonthsAgo
ORDER BY DRL.CREATE_DATE DESC;

-- (2) TemplateSearchService.cs:873 — DeleteTemplateRequests (enumerated by foreach at L878)
SELECT DRL.DOCUMENT_REQUEST_LOG_SEQ_NUM, DRL.DOC_REQUEST_LOG_STATUS_CODE, DRL.SAIF_MAIL_ID, DRL.CREATE_USER,
       DRL.CREATE_DATE, DRL.UPDATE_USER, DRL.UPDATE_DATE, DRL.UPDATE_NUM, DRL.CONTEXT_VALUE, DRL.DOCUMENT_TEMPLATE_SEQ_NUM
FROM   DOCUMENT_REQUEST_LOG DRL
WHERE  DRL.DOCUMENT_TEMPLATE_SEQ_NUM = :templateId;
```

Translation notes:
- (1) is materialized by `.ToList()` at L185; the subsequent `requests.Where(...)` at L190/L192/L193 and the `Select/Distinct/Take` at L196-199 operate on `IEnumerable` in memory (they read `x.MyDocTemplate.BusinessGroup/Subgroup/EffectiveDate/ExpirationDate/IsEnabled` via lazy-loaded `DOCUMENT_TEMPLATE` proxies, not as joins on this statement).
- `System.DateTime.Now.AddMonths(-3)` is evaluated client-side and bound as a parameter.
- (2) `IsNull()` at L875 does not enumerate; the `foreach` at L878 does. `a.MyDocTemplate.Id` is the reference identifier → FK column `DOCUMENT_TEMPLATE_SEQ_NUM`, no join.

## Operations and processes
- TemplateSearchService.DeleteTemplateRequests — see [Operations and processes](../operations-processes.md)
- TemplateSearchService.GetRecentRequests — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Documents/TemplateSearchService.cs (L183, L873)`