Skip to content

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 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

// 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.

-- 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

Sources

  • Services/SAIF.Services/PositivePay/PositivePayService.cs (L22)