SQL-307: Retrieve Subpoena¶

This statement retrieves subpoenas by status code and issue date, and a single subpoena by ID, from SUBPOENA via NHibernate LINQ over IUnitOfWork.GetRepository<Subpoena>(). It is executed by SubpoenaPaymentService.UpdateSubpoenaStatus, SubpoenaPaymentService.GenerateCheckTransactionsAsync, and SubpoenaPaymentService.GetSubpoenas (Services/SAIF.Services/Legal/Subpoenas/SubpoenaPaymentService.cs (L116, L149, L207)) to drive check-transaction generation for approved subpoenas.

Classification¶

Field	Value
SQL ID	SQL-307
Operation type	Read
Execution method	ORM (NHibernate LINQ)
Database	Oracle
Schema	[unqualified]**
Command type	N/A (ORM-generated)
Business object	`SubpoenaPaymentService`
Criteria property	N/A — ORM
Lifecycle method	`UpdateSubpoenaStatus, GenerateCheckTransactionsAsync, GetSubpoenas`

Parameters¶

Parameter	Direction	Type	Max length	Description
`:fromCode`	Input	String	N/A	Status code (`fromCode`) matched against `SUBPOENA_STATUS_CODE` in `UpdateSubpoenaStatus`
`:IssueDate`	Input	DateTime	N/A	Service property `IssueDate` (`SubpoenaPaymentService.cs:56`), matched against `ISSUE_DATE`
`:Id`	Input	Long	N/A	`subpoena.Id` (loop variable) matched against `SUBPOENA_SEQ_NUM` in `GenerateCheckTransactionsAsync`

Tables¶

Table	Access	Columns used
SUBPOENA	SELECT	`SUBPOENA_SEQ_NUM`, `UPDATE_NUM`, `SUBPOENA_TEXT`, `SUBPOENA_OPENING_TEXT`, `SHOW_APPEARANCE_FLAG`, `ISSUE_DATE`, `REQUESTED_BY_EMPLID`, `APPROVED_DATE`, `APPROVED_BY_EMPLID`, `APPEARANCE_DATE_TIME`, `COMPLIANCE_DATE`, `BODY_PART_DESC`, `RECORDS_REQUEST_FROM_DATE`, `RECORDS_REQUEST_THRU_DATE`, `PAGE_COUNT`, `SAIF_LOCATION_FLAG`, `ADDITIONAL_CLAIM_NUMBERS`, `ADDITIONAL_CASE_NUMBERS`, `AKA_NAME`, `SERVE_BY_DATE`, `SPECIFIC_LOCATION_TEXT`, `SPECIAL_DELIVERY_TEXT`, `FILE_NUMBER`, `CREATE_USER`, `CREATE_DATE`, `UPDATE_USER`, `UPDATE_DATE`, `LEGAL_CASE_SEQ_NUM`, `SUBPOENA_STATUS_CODE`, `SUBPOENA_TYPE_CODE`, `HEARING_LOCATION_SEQ_NUM`, `SPECIAL_HANDLING_CODE`, `PROCESS_SERVER_LOC_SEQ_NUM`

LINQ statement¶

Source: Services/SAIF.Services/Legal/Subpoenas/SubpoenaPaymentService.cs:115

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaPaymentService.cs:115-120
var subpoenas =
    _unitOfWork.GetRepository<SubpoenaData.Subpoena>()
        .Query()
        .Where(x => x.SubpoenaStatus.Code == fromCode
         && x.IssueDate == IssueDate)
        .ToList();

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaPaymentService.cs:149
var subpoenaToPay = _unitOfWork.GetRepository<SubpoenaData.Subpoena>().Query().Where(x => x.Id == subpoena.Id).FirstOrDefault();

// Services/SAIF.Services/Legal/Subpoenas/SubpoenaPaymentService.cs:207-211
var subpoenas = _unitOfWork.GetRepository<SubpoenaData.Subpoena>()
    .Query()
    .Where(s => s.SubpoenaStatus.Code == SubpoenaData.SubpoenaStatus.ApprovedCode && s.IssueDate == IssueDate)
    .OrderByDescending(x => (x.SpecialHandling.Code != "NOML") ? 1 : 0).ThenBy(x => x.SpecialHandling.Code)
    .ToList();

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
-- UpdateSubpoenaStatus (L116)
SELECT
  S.SUBPOENA_SEQ_NUM,
  S.UPDATE_NUM,
  S.SUBPOENA_TEXT,
  S.SUBPOENA_OPENING_TEXT,
  S.SHOW_APPEARANCE_FLAG,
  S.ISSUE_DATE,
  S.REQUESTED_BY_EMPLID,
  S.APPROVED_DATE,
  S.APPROVED_BY_EMPLID,
  S.APPEARANCE_DATE_TIME,
  S.COMPLIANCE_DATE,
  S.BODY_PART_DESC,
  S.RECORDS_REQUEST_FROM_DATE,
  S.RECORDS_REQUEST_THRU_DATE,
  S.PAGE_COUNT,
  S.SAIF_LOCATION_FLAG,
  S.ADDITIONAL_CLAIM_NUMBERS,
  S.ADDITIONAL_CASE_NUMBERS,
  S.AKA_NAME,
  S.SERVE_BY_DATE,
  S.SPECIFIC_LOCATION_TEXT,
  S.SPECIAL_DELIVERY_TEXT,
  S.FILE_NUMBER,
  S.CREATE_USER,
  S.CREATE_DATE,
  S.UPDATE_USER,
  S.UPDATE_DATE,
  S.LEGAL_CASE_SEQ_NUM,
  S.SUBPOENA_STATUS_CODE,
  S.SUBPOENA_TYPE_CODE,
  S.HEARING_LOCATION_SEQ_NUM,
  S.SPECIAL_HANDLING_CODE,
  S.PROCESS_SERVER_LOC_SEQ_NUM
FROM SUBPOENA S
WHERE S.SUBPOENA_STATUS_CODE = :fromCode
  AND S.ISSUE_DATE = :IssueDate

-- GenerateCheckTransactionsAsync (L149): filtered first-row lookup (FirstOrDefault)
SELECT *
FROM (
  SELECT
    S.SUBPOENA_SEQ_NUM,
    S.UPDATE_NUM,
    S.SUBPOENA_TEXT,
    S.SUBPOENA_OPENING_TEXT,
    S.SHOW_APPEARANCE_FLAG,
    S.ISSUE_DATE,
    S.REQUESTED_BY_EMPLID,
    S.APPROVED_DATE,
    S.APPROVED_BY_EMPLID,
    S.APPEARANCE_DATE_TIME,
    S.COMPLIANCE_DATE,
    S.BODY_PART_DESC,
    S.RECORDS_REQUEST_FROM_DATE,
    S.RECORDS_REQUEST_THRU_DATE,
    S.PAGE_COUNT,
    S.SAIF_LOCATION_FLAG,
    S.ADDITIONAL_CLAIM_NUMBERS,
    S.ADDITIONAL_CASE_NUMBERS,
    S.AKA_NAME,
    S.SERVE_BY_DATE,
    S.SPECIFIC_LOCATION_TEXT,
    S.SPECIAL_DELIVERY_TEXT,
    S.FILE_NUMBER,
    S.CREATE_USER,
    S.CREATE_DATE,
    S.UPDATE_USER,
    S.UPDATE_DATE,
    S.LEGAL_CASE_SEQ_NUM,
    S.SUBPOENA_STATUS_CODE,
    S.SUBPOENA_TYPE_CODE,
    S.HEARING_LOCATION_SEQ_NUM,
    S.SPECIAL_HANDLING_CODE,
    S.PROCESS_SERVER_LOC_SEQ_NUM
  FROM SUBPOENA S
  WHERE S.SUBPOENA_SEQ_NUM = :Id
)
WHERE ROWNUM <= 1

-- GetSubpoenas (L207)
SELECT
  S.SUBPOENA_SEQ_NUM,
  S.UPDATE_NUM,
  S.SUBPOENA_TEXT,
  S.SUBPOENA_OPENING_TEXT,
  S.SHOW_APPEARANCE_FLAG,
  S.ISSUE_DATE,
  S.REQUESTED_BY_EMPLID,
  S.APPROVED_DATE,
  S.APPROVED_BY_EMPLID,
  S.APPEARANCE_DATE_TIME,
  S.COMPLIANCE_DATE,
  S.BODY_PART_DESC,
  S.RECORDS_REQUEST_FROM_DATE,
  S.RECORDS_REQUEST_THRU_DATE,
  S.PAGE_COUNT,
  S.SAIF_LOCATION_FLAG,
  S.ADDITIONAL_CLAIM_NUMBERS,
  S.ADDITIONAL_CASE_NUMBERS,
  S.AKA_NAME,
  S.SERVE_BY_DATE,
  S.SPECIFIC_LOCATION_TEXT,
  S.SPECIAL_DELIVERY_TEXT,
  S.FILE_NUMBER,
  S.CREATE_USER,
  S.CREATE_DATE,
  S.UPDATE_USER,
  S.UPDATE_DATE,
  S.LEGAL_CASE_SEQ_NUM,
  S.SUBPOENA_STATUS_CODE,
  S.SUBPOENA_TYPE_CODE,
  S.HEARING_LOCATION_SEQ_NUM,
  S.SPECIAL_HANDLING_CODE,
  S.PROCESS_SERVER_LOC_SEQ_NUM
FROM SUBPOENA S
WHERE S.SUBPOENA_STATUS_CODE = 'APPR'
  AND S.ISSUE_DATE = :IssueDate
ORDER BY CASE WHEN S.SPECIAL_HANDLING_CODE <> 'NOML' THEN 1 ELSE 0 END DESC,
  S.SPECIAL_HANDLING_CODE

Translation notes: - Constants from Data/SAIF.Data/Model/Legal/Subpoenas/SubpoenaStatus.cs: ApprovedCode = "APPR" (L6), ReadyToPayCode = "PAY" (L7), PaidCode = "PAID" (L8). - Conditional (cond) ? 1 : 0 in OrderByDescending → CASE WHEN ... THEN 1 ELSE 0 END DESC. Rows with a NULL SPECIAL_HANDLING_CODE evaluate to 0 in SQL (NULL <> 'NOML' is UNKNOWN), whereas the C# expression would throw on a null reference in memory; inferred difference, not runtime-verified. - L149 runs once per subpoena inside the foreach (L144). - First/FirstOrDefault → Oracle10 dialect row limit SELECT * FROM (...) WHERE ROWNUM <= 1. - SUBPOENA_STATUS_CODE and SUBPOENA_SPECIAL_HANDLING_CODE are not joined — SubpoenaStatus.Code and SpecialHandling.Code are identifiers and short-circuit to FK columns SUBPOENA_STATUS_CODE and SPECIAL_HANDLING_CODE. subpoenaToPay.SubpoenaType.Code (L153) is read in memory from the proxy after materialization. - References (LegalCase, SubpoenaStatus, SubpoenaType, HearingLocation, SpecialHandling, ProcessServerLocation) contribute FK columns only; HasMany(Recipients) is lazy — subpoena.Recipients.Any() after materialization triggers a separate lazy collection load that is not part of this statement.

Operations and processes¶

SubpoenaPaymentService.GenerateCheckTransactionsAsync — see Operations and processes
SubpoenaPaymentService.GetSubpoenas — see Operations and processes
SubpoenaPaymentService.UpdateSubpoenaStatus — see Operations and processes

Sources¶

Services/SAIF.Services/Legal/Subpoenas/SubpoenaPaymentService.cs (L116, L149, L207)