The query below pulls in Purchase Orders that have a receipt against it, but the order status is not marked “Received”. This happens when the QTY received is less than the QTY ordered. We use this as an end of the day cleanup task so that when receipts get turned into accounting for vendor payments that they are ready for processing. Copy and paste the following query into the “Edit Dat Source” area of the report designer. Then drag/drop your form fields to create the report.
SELECT PurchaseOrder.OrderNumber, PurchaseOrder.StatusString, PurchaseOrderView.FacilityID, Receipt.ReceivedDate
FROM “palmate-PalletService”.dbo.PurchaseOrder PurchaseOrder, “palmate-PalletService”.dbo.PurchaseOrderView PurchaseOrderView, “palmate-PalletService”.dbo.Receipt Receipt
WHERE Receipt.OrderEntityID = PurchaseOrder.EntityID AND PurchaseOrderView.EntityID = PurchaseOrder.EntityID AND ((PurchaseOrder.StatusString=’Open’) OR (PurchaseOrder.StatusString=’Incomplete’))