Skip to content

SQL Script – Orders Needing Purchase

August 20, 2011

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

About these ads

From → Dynamics GP, SQL, Views

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: