---
title: "SQL-280: Retrieve PurchaseOrder via RetrievePurchaseOrders"
summary: Read operation against PurchaseOrder via NHibernate ORM.
status: draft
owner: SAIF Doc Gen
last_reviewed: 2026-09-24
library: SAIF.Services
reference_type: package
---

# SQL-280: Retrieve PurchaseOrder via RetrievePurchaseOrders

This statement retrieves `PurchaseOrder` rows via NHibernate LINQ in `PurchaseOrderService.RetrievePurchaseOrders`: when no purchase order number is supplied, it returns purchase orders that are pending issue, not closed, and not cancelled; when a purchase order number fragment is supplied, it returns purchase orders whose number contains that fragment. Executed from `Services/SAIF.Services/Corporate/PurchaseOrderService.cs (L67-69)`.

## Classification

| Field | Value |
| --- | --- |
| **SQL ID** | SQL-280 |
| **Operation type** | Read |
| **Execution method** | ORM (NHibernate LINQ) |
| **Database** | Oracle |
| **Schema** | [unqualified]** |
| **Command type** | N/A (ORM-generated) |
| **Business object** | `PurchaseOrderService` |
| **Criteria property** | N/A — ORM |
| **Lifecycle method** | `RetrievePurchaseOrders` |

## Parameters

| Parameter | Direction | Type | Max length | Description |
| --- | --- | --- | --- | --- |
| `:PurchaseOrderNumber` | Input | string | N/A | `_batchParameters.PurchaseOrderNumber` — matched with `LIKE '%' \|\| :PurchaseOrderNumber \|\| '%'` against `PO_NUMBER` (used only when supplied) |

## Tables

| Table | Access | Columns used |
| --- | --- | --- |
| [PURCHASEORDERSVIEW](../database-tables.md#purchaseordersview) | SELECT | `PO_NUMBER`, `L_INDEX`, `VENDOR`, `VENDOR_VNAME`, `ADDR1`, `ADDR2`, `ADDR3`, `ADDR4`, `CITY_ADDR5`, `STATE_PROV`, `POSTAL_CODE`, `COUNTRY`, `PO_DATE`, `DELIVERY_DATE`, `BUYER`, `TOT_ORDER_AMT`, `PHONE`, `CURRENCY_CODE`, `RELEASED_FL`, `ISSUED_FLAG`, `CANCELLED_FL`, `REVISED`, `ISSUE_PENDING`, `CLOSED_FL`, `LOCATION_ADDR1`, `LOCATION_CITY`, `LOCATION_STATE`, `LOCATION_ZIP`, `R_DESC_01`, `PO_NAME`, `R_NAME` |

## LINQ statement

Source: `Services/SAIF.Services/Corporate/PurchaseOrderService.cs:67`

```csharp
// Services/SAIF.Services/Corporate/PurchaseOrderService.cs:67-69
return (_batchParameters.PurchaseOrderNumber.IsNullOrEmpty())
    ? _unitOfWork.GetRepository<PurchaseOrderModel>().Query().Where(x => x.IsIssuePending == true && x.IsClosed == false && x.Canceled == false).ToList()
    : _unitOfWork.GetRepository<PurchaseOrderModel>().Query().Where(x => x.Id.Contains(_batchParameters.PurchaseOrderNumber)).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
-- PurchaseOrderNumber is null or empty (L68)
SELECT
  POV.PO_NUMBER,
  POV.L_INDEX,
  POV.VENDOR,
  POV.VENDOR_VNAME,
  POV.ADDR1,
  POV.ADDR2,
  POV.ADDR3,
  POV.ADDR4,
  POV.CITY_ADDR5,
  POV.STATE_PROV,
  POV.POSTAL_CODE,
  POV.COUNTRY,
  POV.PO_DATE,
  POV.DELIVERY_DATE,
  POV.BUYER,
  POV.TOT_ORDER_AMT,
  POV.PHONE,
  POV.CURRENCY_CODE,
  POV.RELEASED_FL,
  POV.ISSUED_FLAG,
  POV.CANCELLED_FL,
  POV.REVISED,
  POV.ISSUE_PENDING,
  POV.CLOSED_FL,
  POV.LOCATION_ADDR1,
  POV.LOCATION_CITY,
  POV.LOCATION_STATE,
  POV.LOCATION_ZIP,
  POV.R_DESC_01,
  POV.PO_NAME,
  POV.R_NAME
FROM PurchaseOrdersView POV
WHERE POV.ISSUE_PENDING = 'Y'
  AND POV.CLOSED_FL = 'N'
  AND POV.CANCELLED_FL = 'N'

-- PurchaseOrderNumber supplied (L69)
SELECT
  POV.PO_NUMBER,
  POV.L_INDEX,
  POV.VENDOR,
  POV.VENDOR_VNAME,
  POV.ADDR1,
  POV.ADDR2,
  POV.ADDR3,
  POV.ADDR4,
  POV.CITY_ADDR5,
  POV.STATE_PROV,
  POV.POSTAL_CODE,
  POV.COUNTRY,
  POV.PO_DATE,
  POV.DELIVERY_DATE,
  POV.BUYER,
  POV.TOT_ORDER_AMT,
  POV.PHONE,
  POV.CURRENCY_CODE,
  POV.RELEASED_FL,
  POV.ISSUED_FLAG,
  POV.CANCELLED_FL,
  POV.REVISED,
  POV.ISSUE_PENDING,
  POV.CLOSED_FL,
  POV.LOCATION_ADDR1,
  POV.LOCATION_CITY,
  POV.LOCATION_STATE,
  POV.LOCATION_ZIP,
  POV.R_DESC_01,
  POV.PO_NAME,
  POV.R_NAME
FROM PurchaseOrdersView POV
WHERE POV.PO_NUMBER LIKE '%' || :PurchaseOrderNumber || '%'
```

Translation notes:
- C# ternary chooses one of two statements; the choice is not a SQL predicate.
- `YesNoType` booleans (`PurchaseOrderMap.cs:32`, `:34`, `:35`): `== true` → `'Y'`, `== false` → `'N'`.
- `string.Contains(arg)` → `LIKE '%' || :arg || '%'`.

## Operations and processes
- PurchaseOrderService.RetrievePurchaseOrders — see [Operations and processes](../operations-processes.md)

## Sources
- `Services/SAIF.Services/Corporate/PurchaseOrderService.cs (L68, L69)`