---
title: "SQL-273: Retrieve PositivePayView via PositivePayViewQuery (property)"
summary: Read operation against PositivePayView via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-273: Retrieve PositivePayView via PositivePayViewQuery (property)

This statement queries `POSITIVE_PAY_VIEW` rows for two positive-pay processing scenarios: newly issued checks matching a positive pay date and specific reconciliation statuses with the default reconciliation date, and newly cancelled checks matching a reconciliation date and reconciliation statuses. The unexecuted `IQueryable` is exposed via `PositivePayService.PositivePayViewQuery` and materialized in `ProcessDetailRecords`, executed from `Services/SAIF.Services/PositivePay/PositivePayService.cs (L22)`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-273 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | WCIS_DBA (explicit qualifier in Table()) |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `PositivePayService` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `PositivePayViewQuery` (property); consumed in `ProcessDetailRecords` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:positivePayDate` | Input | DateTime | N/A | date matched against `IssueDate` (L40) or `ReconciliationDate` (L47) |
| `:rs0`..`:rs4` | Input | int | N/A | elements of `newlyIssuedRecStatuses` = 1, 3, 4, 5, 6 (L40); `newlyCanceledRecStatuses` = 3, 4, 5, 6 (L47) |
| `:defaultReconciliationDate` | Input | DateTime | N/A | `new DateTime(1700, 1, 1)` |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [POSITIVE_PAY_VIEW](../database-tables.md#positive_pay_view) | SELECT | `SERIAL_NUMBER`, `ACCOUNT_NUMBER`, `DOLLAR_AMOUNT`, `VOID_CANCEL`, `FIRST_PAYEE_NAME`, `SECOND_PAYEE_NAME`, `ISSUE_DATE`, `REC_STATUS`, `RECON_DATE`, `ARP_ISSUE_DATE` |

## LINQ statement

Source: `Services/SAIF.Services/PositivePay/PositivePayService.cs:22`

```csharp
// Services/SAIF.Services/PositivePay/PositivePayService.cs:22
private IQueryable<PositivePayView> PositivePayViewQuery => _unitOfWork.GetRepository<PositivePayView>().Query();

// Services/SAIF.Services/PositivePay/PositivePayService.cs:40-42
var newlyIssued = PositivePayViewQuery.Where(x => x.IssueDate == positivePayDate &&
                                                  newlyIssuedRecStatuses.Contains(x.ReconciliationStatus) &&
                                                  x.ReconciliationDate == defaultReconciliationDate).ToList();

// Services/SAIF.Services/PositivePay/PositivePayService.cs:47-48
var newlyCanceled = PositivePayViewQuery.Where(x => x.ReconciliationDate == positivePayDate &&
                                                    newlyCanceledRecStatuses.Contains(x.ReconciliationStatus)).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.

```sql
-- Inferred from LINQ (NHibernate mapping translation); not concrete SQL
-- newlyIssued (L40)
SELECT
  PPV.SERIAL_NUMBER,
  PPV.ACCOUNT_NUMBER,
  PPV.DOLLAR_AMOUNT,
  PPV.VOID_CANCEL,
  PPV.FIRST_PAYEE_NAME,
  PPV.SECOND_PAYEE_NAME,
  PPV.ISSUE_DATE,
  PPV.REC_STATUS,
  PPV.RECON_DATE,
  PPV.ARP_ISSUE_DATE
FROM WCIS_DBA.POSITIVE_PAY_VIEW PPV
WHERE PPV.ISSUE_DATE = :positivePayDate
  AND PPV.REC_STATUS IN (:rs0, :rs1, :rs2, :rs3, :rs4)
  AND PPV.RECON_DATE = :defaultReconciliationDate

-- newlyCanceled (L47)
SELECT
  PPV.SERIAL_NUMBER,
  PPV.ACCOUNT_NUMBER,
  PPV.DOLLAR_AMOUNT,
  PPV.VOID_CANCEL,
  PPV.FIRST_PAYEE_NAME,
  PPV.SECOND_PAYEE_NAME,
  PPV.ISSUE_DATE,
  PPV.REC_STATUS,
  PPV.RECON_DATE,
  PPV.ARP_ISSUE_DATE
FROM WCIS_DBA.POSITIVE_PAY_VIEW PPV
WHERE PPV.RECON_DATE = :positivePayDate
  AND PPV.REC_STATUS IN (:rs0, :rs1, :rs2, :rs3)
```

Translation notes:
- L22 is an expression-bodied property returning an unexecuted `IQueryable`; the SQL runs at the consumers' `.ToList()` (L42, L48).
- `List<int>.Contains(column)` → `IN` with one bind per element.
- Expressions that do not reference the lambda parameter (captured locals, members of already-loaded objects, `Convert.ToInt64(...)`, `DateTime.Today`) are evaluated client-side by NHibernate's partial evaluator and bound as a single parameter value.

## Operations and processes
- PositivePayService.PositivePayViewQuery (property) — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/PositivePay/PositivePayService.cs (L22)`