This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.
This script returns a list of open purchase order lines from POs with a remaining sub total amount.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
PONUMBER AS 'PO Number'
,LineNumber AS 'Line Number'
,CASE WHEN POLNESTA = 1 THEN
'New'
WHEN POLNESTA = 2 THEN
'Released'
WHEN POLNESTA = 3 THEN
'Change Order'
WHEN POLNESTA = 4 THEN
'Received'
WHEN POLNESTA = 5 THEN
'Closed'
ELSE
'Cancelled'
END AS 'PO Line Status'
,CASE WHEN POTYPE = 1 THEN
'Standard'
WHEN POTYPE = 2 THEN
'Drop Ship'
WHEN POTYPE = 3 THEN
'Blanket'
ELSE
'Blank Drop Ship'
END AS 'PO Type'
,ITEMNMBR AS 'Item Number'
,ITEMDESC AS 'Item Description'
,VENDORID AS 'Vendor ID'
,UOFM AS 'Unit of Measure'
,QTYORDER AS 'Qty Ordered'
,UNITCOST AS 'Unit Cost'
,EXTDCOST AS 'Line Subtotal'
,TAXAMNT AS 'Tax Amount'
FROM
POP10110 --Purchase Order Line (POP10110)
WHERE
PONUMBER IN
(
SELECT
PONUMBER
FROM
POP10100 --Purchase Order Work (POP10100)
WHERE
REMSUBTO <> 0
)
GO
