SQL Script – Orders Needing Purchase

So…you know that you had something that you needed to order for a customer, but you cannot remember what it is to save your life?  Well the following view will show you a listing of all open sales order with line items that need to be purchased.  Whether the line item is back ordered, or a drop ship, this view will show you what needs to be purchased (or linked to an existing purchase order).

This script uses the following views:

Sales Document (tspvSalesDocument)

Sales Line Item (tspvSalesLineItem)

***SQL Script***
SELECT a.Sales_Doc_Num,
Doc_Date,
Sales_Batch,
Customer_Num,
Customer_Name,
Created_By,
Item_Number,
Item_Description,
Is_Dropship,
Quantity,
Notes
FROM dbo.tspvSalesDocument a
JOIN dbo.tspvSalesLineItem b ON a.Sales_Doc_Num = b.Sales_Doc_Num
WHERE b.Purchasing_Status = ‘needs purchase’
ORDER BY Sales_Batch,
Sales_Doc_ID