SQL-165: Retrieve DocumentRequestParameter¶

This statement reads DocumentRequestParameter rows for a document request, ordered by parameter number, for display, single-parameter lookup during save, and to find the VOC (vocational) coordinator selection parameter. DocumentRequestParameter has no ClassMap anywhere in this repository or in the external mapping package queried via Legends, so the table and column names below are unresolved and shown as C# property names.

Classification¶

Field	Value
SQL ID	SQL-165
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`DocumentRequestParameter`
Criteria property	N/A — ORM
Lifecycle method	`GetDocumentRequestParameters, NeedVocCoordinators, SaveChangedDocumentRequestParameters, StoreOriginalDocumentRequestParameters`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:inRequestId`	Input	long	N/A	`a.RequestId == inRequestId` → column RequestId (unresolved)
`:parmId`	Input	long	N/A	`x.Id == parmId` → column Id (unresolved)

Tables¶

Table	Access	Columns used
DocumentRequestParameter**	SELECT	Id, RequestId, ParameterNumber, XmlName, XmlGroup, DataSource, DataType, Prompt, ValueTxt, IsViewable, IsRequired, CreateUser, CreateDate, UpdateUser, UpdateDate, UpdateNumber (C# property names — unresolved mapping)

LINQ statement¶

Source: Services/SAIF.Services/Documents/DocumentParameterService.cs:116. Additional fragments at :180, :199, :223.

// Services/SAIF.Services/Documents/DocumentParameterService.cs:116
IEnumerable<DataModel.DocumentRequestParameter> requestParameters = UnitOfWork.GetRepository<DataModel.DocumentRequestParameter>().Query()
                .Where(a => a.RequestId == inRequestId)
                .OrderBy(a => a.ParameterNumber);

// Services/SAIF.Services/Documents/DocumentParameterService.cs:180
IEnumerable<DataModel.DocumentRequestParameter> requestParameters = UnitOfWork.GetRepository<DataModel.DocumentRequestParameter>().Query()
                .Where(a => a.RequestId == inRequestId)
                .OrderBy(a => a.ParameterNumber);

// Services/SAIF.Services/Documents/DocumentParameterService.cs:199
var aParm = UnitOfWork.GetRepository<DataModel.DocumentRequestParameter>().Query().SingleOrDefault(x => x.Id == parmId);

// Services/SAIF.Services/Documents/DocumentParameterService.cs:223
var aParm = UnitOfWork.GetRepository<DataModel.DocumentRequestParameter>().Query().SingleOrDefault(x => x.RequestId == inRequestId && x.DataType == "SELECTVOC");

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
-- unresolved mapping: DocumentRequestParameter (no ClassMap in repo; Legends returned no mapping) — C# names used throughout
-- (1) L116 / (2) L180 — GetDocumentRequestParameters / StoreOriginalDocumentRequestParameters
SELECT DRP.Id, DRP.RequestId, DRP.ParameterNumber, DRP.XmlName, DRP.XmlGroup, DRP.DataSource, DRP.DataType,
       DRP.Prompt, DRP.ValueTxt, DRP.IsViewable, DRP.IsRequired, DRP.CreateUser, DRP.CreateDate,
       DRP.UpdateUser, DRP.UpdateDate, DRP.UpdateNumber            -- unresolved mapping: DocumentRequestParameter.*
FROM   DocumentRequestParameter DRP                                   -- unresolved mapping: DocumentRequestParameter (table)
WHERE  DRP.RequestId = :inRequestId                                   -- unresolved mapping: DocumentRequestParameter.RequestId
ORDER BY DRP.ParameterNumber;                                         -- unresolved mapping: DocumentRequestParameter.ParameterNumber

-- (3) L199 — SaveChangedDocumentRequestParameters (per parameter in loop)
SELECT <same column list>
FROM   DocumentRequestParameter DRP                                   -- unresolved mapping
WHERE  DRP.Id = :parmId;                                              -- unresolved mapping: DocumentRequestParameter.Id
-- NHibernate expects at most one row

-- (4) L223 — NeedVocCoordinators
SELECT <same column list>
FROM   DocumentRequestParameter DRP                                   -- unresolved mapping
WHERE  DRP.RequestId = :inRequestId
  AND  DRP.DataType = 'SELECTVOC';                                    -- unresolved mapping: DocumentRequestParameter.DataType
-- NHibernate expects at most one row

Translation notes: - ** = unresolved mapping; table and column names are C# names, not physical names. No ClassMap<DocumentRequestParameter> exists under Data/SAIF.Data.NHibernate/Mappings/** or anywhere in the repo, and Legends returned no mapping for this entity. - (1)/(2) are IEnumerable assignments of an ordered IQueryable; SQL runs when enumerated by the caller. - SingleOrDefault/Single add no row limit; NHibernate throws if more than one row is returned.

Operations and processes¶

DocumentParameterService.GetDocumentRequestParameters — see Operations and processes
DocumentParameterService.NeedVocCoordinators — see Operations and processes
DocumentParameterService.SaveChangedDocumentRequestParameters — see Operations and processes
DocumentParameterService.StoreOriginalDocumentRequestParameters — see Operations and processes

Business rules¶

SVC-BR-DOC-010 A VOC coordinator is needed when a request parameter has DataType 'SELECTVOC'

Sources¶

Services/SAIF.Services/Documents/DocumentParameterService.cs (L116, L180, L199, L223)