--- title: "SQL-163: Retrieve DocumentRequestLog via RequestLogQuery (property)" 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-163: Retrieve DocumentRequestLog via RequestLogQuery (property) `RequestLogQuery` exposes an `IQueryable` over `DOCUMENT_REQUEST_LOG`. Its composing consumers look up a single request by ID, find the most recent "new" macro or non-macro request for a user, or list all pending requests, driving document-request workflow decisions in `DocumentRequestService`. ## Classification | Field | Value | | --- | --- | | **SQL ID** | SQL-163 | | **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** | `RequestLogQuery (property)` | ## Parameters | Parameter | Direction | Type | Max length | Description | | --- | --- | --- | --- | --- | | `:requestLogId` | Input | long | N/A | `a.Id == requestLogId` → column DOCUMENT_REQUEST_LOG_SEQ_NUM | | `:userIdLower` | Input | string | N/A | `= userId.ToLower()`, evaluated client-side → column SAIF_MAIL_ID | ## Tables | Table | Access | Columns used | | --- | --- | --- | | [DOCUMENT_REQUEST_LOG](../database-tables.md#document_request_log) | SELECT | all mapped columns; filters on DOCUMENT_REQUEST_LOG_SEQ_NUM, SAIF_MAIL_ID, DOC_REQUEST_LOG_STATUS_CODE; order CREATE_DATE | | [DOCUMENT_TEMPLATE](../database-tables.md#document_template) | JOIN | DOCUMENT_TEMPLATE_SEQ_NUM (join), RUN_TYPE_CODE (filter) — macro/non-macro consumers only | ## LINQ statement Source: `Services/SAIF.Services/Documents/DocumentRequestService.cs:78`. Composing consumers at `Services/SAIF.Services/Documents/DocumentRequestService.cs:83`, `:191`, `:207`, `:218`, `:257`. ```csharp // Services/SAIF.Services/Documents/DocumentRequestService.cs:78 return UnitOfWork.GetRepository().Query(); // Services/SAIF.Services/Documents/DocumentRequestService.cs:83 var request = RequestLogQuery.SingleOrDefault(a => a.Id == requestLogId); // Services/SAIF.Services/Documents/DocumentRequestService.cs:191 var request = RequestLogQuery.Where(a => a.UserId.ToLower() == userId.ToLower() && a.DocRequestLogStatusCode == DocumentRequestLog.NewRequest && a.MyDocTemplate.RunType.Code == "MACRO") .OrderByDescending(a => a.CreateDate) .FirstOrDefault(); // Services/SAIF.Services/Documents/DocumentRequestService.cs:207 var request = RequestLogQuery.Where(a => a.UserId.ToLower() == userId.ToLower() && a.DocRequestLogStatusCode == DocumentRequestLog.NewRequest) // && a.MyDocTemplate.RunType.Code == "MACRO") .OrderByDescending(a => a.CreateDate) .FirstOrDefault(); // Services/SAIF.Services/Documents/DocumentRequestService.cs:218 var request = RequestLogQuery.Where(a => a.UserId.ToLower() == userId.ToLower() && a.DocRequestLogStatusCode == DocumentRequestLog.NewRequest && a.MyDocTemplate.RunType.Code != "MACRO") .OrderByDescending(a => a.CreateDate) .FirstOrDefault(); // Services/SAIF.Services/Documents/DocumentRequestService.cs:257 IEnumerable requests = RequestLogQuery .Where(a => a.DocRequestLogStatusCode == DocumentRequestLog.PendingRequest); ``` ## 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 -- Base: RequestLogQuery (DocumentRequestService.cs:78) — IQueryable only; SQL is emitted by each consumer. -- (a) L83 / L539 / L566 — SingleOrDefault(a => a.Id == requestLogId) 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_REQUEST_LOG_SEQ_NUM = :requestLogId; -- NHibernate expects at most one row -- (b) L191 — GetLatestMacroRequestForUserOrDefault SELECT * FROM ( 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 INNER JOIN DOCUMENT_TEMPLATE DT ON DT.DOCUMENT_TEMPLATE_SEQ_NUM = DRL.DOCUMENT_TEMPLATE_SEQ_NUM WHERE LOWER(DRL.SAIF_MAIL_ID) = :userIdLower AND DRL.DOC_REQUEST_LOG_STATUS_CODE = 'NEW' -- DocumentRequestLog.NewRequest AND DT.RUN_TYPE_CODE = 'MACRO' ORDER BY DRL.CREATE_DATE DESC ) WHERE ROWNUM <= 1; -- (c) L207 — GetLatestRequestForUserOrDefault (no template join; RunType predicate is commented out in source) SELECT * FROM ( 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 LOWER(DRL.SAIF_MAIL_ID) = :userIdLower AND DRL.DOC_REQUEST_LOG_STATUS_CODE = 'NEW' ORDER BY DRL.CREATE_DATE DESC ) WHERE ROWNUM <= 1; -- (d) L218 — GetLatestXpRequestForUserOrDefault: same as (b) but DT.RUN_TYPE_CODE <> 'MACRO' -- (e) L257-258 — GetPendingRequests (materialized later; L260 filter runs in memory, see notes) 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.DOC_REQUEST_LOG_STATUS_CODE = 'PEND'; -- DocumentRequestLog.PendingRequest ``` Translation notes: - L78 is a queryable-property definition; each consumer composes its own statement (consumers: L83, L191, L207, L218, L257, L539, L566). - `DocumentRequestLog.NewRequest`="NEW", `PendingRequest`="PEND". C# `const` values are shown as literals for readability; NHibernate LINQ sends them as bind parameters. - `a.MyDocTemplate.RunType.Code` is a two-hop path: `MyDocTemplate` needs an INNER JOIN to `DOCUMENT_TEMPLATE` (because `RunType` is not its identifier), but `RunType.Code` is the identifier of `RunTypeCode` so it resolves to `DT.RUN_TYPE_CODE` with no further join (the `RUN_TYPE_CODE` code table itself is never joined). - `FirstOrDefault` → Oracle10 dialect wraps the query in `SELECT * FROM (...) WHERE ROWNUM <= 1`. - `SingleOrDefault`/`Single` add no row limit; NHibernate throws if more than one row is returned. - (e): `requests` is declared `IEnumerable`, so the L260 `.Where(a => a.UserId.ToUpper() == overrideUserId || a.CreateUser.ToUpper() == overrideUserId)` is LINQ-to-Objects applied after the L258 query is enumerated — it does not appear in SQL. ## Operations and processes - DocumentRequestService.RequestLogQuery (property) — see [Operations and processes](../operations-processes.md) ## Sources - `Services/SAIF.Services/Documents/DocumentRequestService.cs (L78)`