This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted 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 payables management invoices for vendors who also have purchase orders.
/*
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
['Payables Transactions'].VENDORID AS 'Control Type'
,['Payables Transactions'].VENDORID AS 'Vendor ID'
,['Payables Transactions'].VCHRNMBR AS 'Voucher Number'
,['Payables Transactions'].DOCTYPE AS 'Document Type'
,['Payables Transactions'].TRXDSCRN AS 'Description'
,['Payables Transactions'].DOCDATE AS 'Document Date'
,['Payables Transactions'].DOCNUMBR AS 'Document Number'
,['Payables Transactions'].DOCAMNT AS 'Document Amount'
,['Purchase Orders'].POCOUNT AS 'Count of POs'
FROM
(
SELECT
CNTRLTYP
,DOCTYPE
,VCHRNMBR
,TRXDSCRN
,VENDORID
,DOCDATE
,DOCNUMBR
,DOCAMNT
,PORDNMBR
,BCHSOURC
FROM
PM20000 AS PM WITH (NOLOCK)
WHERE
DOCTYPE = 1
AND
BCHSOURC = 'PM_Trxent'
UNION ALL
SELECT
CNTRLTYP
,DOCTYPE
,VCHRNMBR
,TRXDSCRN
,VENDORID
,DOCDATE
,DOCNUMBR
,DOCAMNT
,PORDNMBR
,BCHSOURC
FROM
PM30200 AS PM WITH (NOLOCK)
WHERE
DOCTYPE = 1
AND
BCHSOURC = 'PM_Trxent'
) AS ['Payables Transactions']
INNER JOIN
(
SELECT
VENDORID
,SUM(POCOUNT) AS POCOUNT
FROM
(
SELECT
VENDORID
,COUNT(PONUMBER) AS POCOUNT
FROM
POP10100 WITH (NOLOCK)
GROUP BY
VENDORID
UNION
SELECT
VENDORID
,COUNT(PONUMBER) AS POCOUNT
FROM
POP30100 WITH (NOLOCK)
GROUP BY
VENDORID
) AS ['Purchase Orders']
GROUP BY
VENDORID
) AS ['Purchase Orders']
ON
['Purchase Orders'].VENDORID = ['Payables Transactions'].VENDORID
WHERE
['Purchase Orders'].POCOUNT > 0
GO
