dataexport: 16
This data as json
| id | dataQuery | dateCreated | dateLastModified | description | lastChangedUserId | name |
|---|---|---|---|---|---|---|
| 16 | SELECT p.num AS `Part Number`, p.description AS `Part Description`, p.abcCode AS `ABC Code`, /* Next FIFO Cost = company-wide next FIFO from costlayer */ ( SELECT (cl.orgTotalCost / NULLIF(cl.orgQty, 0)) AS fifo_unit_cost FROM costlayer cl JOIN costlayerstatus cls ON cls.id = cl.statusId WHERE cl.partId = p.id AND cls.name = 'Open' AND cl.qty > 0 AND cl.orgQty <> 0 ORDER BY COALESCE( /* earliest inventory transaction date tied to the layer (if any) */ ( SELECT MIN(il.eventDate) FROM inventorylogtocostlayer ilt JOIN inventorylog il ON il.id = ilt.inventoryLogId WHERE ilt.costLayerId = cl.id ), cl.dateCreated ) ASC, cl.id ASC LIMIT 1 ) AS `Next FIFO Cost`, /* Last Purchase Price = last received PO unit cost, else last PO-issued line unit cost */ 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 `Last Purchase Price per Unit` FROM part p ORDER BY p.num | 2026-02-18 09:26:28.566000 | 2026-02-18 09:47:17.381000 | Query to list Next FIFO Cast and compare to Last purchase price - not working yet | 17 | Not working - Query Next FIFO and Last PP |