dataexport: 19
This data as json
| id | dataQuery | dateCreated | dateLastModified | description | lastChangedUserId | name |
|---|---|---|---|---|---|---|
| 19 | SELECT p.num AS `Part Number`, p.description AS `Part Description`, pt.name AS `Inventory Type`, /* Last PO Purchase Price: 1) last RECEIVED PO unit cost (preferred) 2) else last PO-issued/created line unit cost (fallback) If neither exists => 'NoPOHist' */ IFNULL( CAST( COALESCE( ( SELECT poi.unitCost FROM receiptitem ri JOIN receipt r ON r.id = ri.receiptId JOIN poitem poi ON poi.id = ri.poItemId WHERE ri.partId = p.id AND r.poId IS NOT NULL AND ri.dateReceived IS NOT NULL AND poi.unitCost IS NOT NULL ORDER BY ri.dateReceived DESC, ri.id DESC LIMIT 1 ), ( SELECT poi.unitCost FROM poitem poi JOIN po ON po.id = poi.poId WHERE poi.partId = p.id AND poi.unitCost IS NOT NULL ORDER BY COALESCE(po.dateIssued, po.dateCreated) DESC, poi.id DESC LIMIT 1 ) ) AS CHAR ), 'NoPOHist' ) AS `Last PO Purchase Price`, /* Last Purchase Price Date: 1) receiptitem.dateReceived for last received PO 2) else PO issue/create date for last PO line If neither exists => 'NoPOHist' */ IFNULL( CAST( DATE( COALESCE( ( SELECT ri.dateReceived FROM receiptitem ri JOIN receipt r ON r.id = ri.receiptId JOIN poitem poi ON poi.id = ri.poItemId WHERE ri.partId = p.id AND r.poId IS NOT NULL AND ri.dateReceived IS NOT NULL AND poi.unitCost IS NOT NULL ORDER BY ri.dateReceived DESC, ri.id DESC LIMIT 1 ), ( SELECT COALESCE(po.dateIssued, po.dateCreated) FROM poitem poi JOIN po ON po.id = poi.poId WHERE poi.partId = p.id AND poi.unitCost IS NOT NULL AND COALESCE(po.dateIssued, po.dateCreated) IS NOT NULL ORDER BY COALESCE(po.dateIssued, po.dateCreated) DESC, poi.id DESC LIMIT 1 ) ) ) AS CHAR ), 'NoPOHist' ) AS `Last Purchase Price Date` FROM part p LEFT JOIN parttype pt ON pt.id = p.typeId ORDER BY p.num | 2026-02-18 10:12:09.009000 | 2026-02-18 10:20:10.099000 | Last PO Purchase Price (most recent received PO unit cost; if none, falls back to most recent PO line unit cost). If no PO history exists at all, it returns NoPOHist | 17 | Last PO Purch Price Query |