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

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

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

Sources¶

Services/SAIF.Services/Corporate/PurchaseOrderService.cs (L68, L69)