Vendor Statement For Dynamics GP

Looking for a complete Vendor Statement that displays Posted and unposted transactions? Check the view below:

----------POP UNPOSTED----------------------------
SELECT
'RECIEVING UNPOSTED' AS TRXSOURCE,
dbo.POP10300.RECEIPTDATE AS DOCDATE,
dbo.POP10300.VNDDOCNM AS DOCNUMBR,
dbo.POP10300.VENDORID,
dbo.POP10300.SUBTOTAL-dbo.POP10300.TRDISAMT+dbo.POP10300.TAXAMNT AS CREDIT,
0 AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.POP10300
INNER JOIN dbo.PM00200 ON dbo.POP10300.VENDORID = dbo.PM00200.VENDORID
---------------------------------------------------------------
UNION ALL
--------------------POP POSTED---------------------------------
SELECT
'RECIEVING POSTED' AS TRXSOURCE,
dbo.POP30300.RECEIPTDATE AS DOCDATE,
dbo.POP30300.POPRCTNM AS DOCNUMBR,
dbo.POP30300.VENDORID,
dbo.POP30300.SUBTOTAL-dbo.POP30300.TRDISAMT+dbo.POP30300.TAXAMNT AS CREDIT,
0 AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.POP30300
INNER JOIN dbo.PM00200 ON dbo.POP30300.VENDORID = dbo.PM00200.VENDORID
---------------------------------------------------------------
UNION ALL
-------------------PAYMENT UNPOSTED--------------------
SELECT
'PAYMENT UNPOSTED' AS TRXSOURCE,
dbo.PM10400.DOCDATE ,
dbo.PM10400.PMNTNMBR AS DOCNUMBR,
dbo.PM10400.VENDORID,
0 AS CREDIT,
dbo.PM10400.DOCAMNT AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM10400
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM10400.VENDORID
---------------------------------------------------------------
UNION ALL
----------------PAYMENT + PM POSTED-------------------------
SELECT
CASE

WHEN dbo.PM20000.DOCTYPE=1 THEN 'INVOICE POSTED'
WHEN dbo.PM20000.DOCTYPE=2 THEN 'FINANCE CHARGES POSTED'
WHEN dbo.PM20000.DOCTYPE=3 THEN 'MIS CHARGES POSTED'
WHEN dbo.PM20000.DOCTYPE=4 THEN 'RETURN POSTED'
WHEN dbo.PM20000.DOCTYPE=5 THEN 'CREDIT MEMO POSTED'
WHEN dbo.PM20000.DOCTYPE=6 THEN 'PAYMENT POSTED'
END AS TRXSOURCE,
dbo.PM20000.DOCDATE,
dbo.PM20000.DOCNUMBR AS DOCNUMBR,
dbo.PM20000.VENDORID,
ISNULL(CASE
WHEN dbo.PM20000.DOCTYPE=1 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE=2 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE=3 THEN dbo.PM20000.DOCAMNT
END,0) AS CREDIT,
ISNULL(CASE
WHEN dbo.PM20000.DOCTYPE=4 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE=5 THEN dbo.PM20000.DOCAMNT
WHEN dbo.PM20000.DOCTYPE=6 THEN dbo.PM20000.DOCAMNT
END,0) AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM20000 INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM20000.VENDORID
---------------------------------------------------------------
UNION ALL
----------------PAYMENT + PM POSTED HISTORY-------------------------
SELECT
CASE
WHEN dbo.PM30200.DOCTYPE=1 THEN 'INVOICE POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=2 THEN 'FINANCE CHARGES POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=3 THEN 'MIS CHARGES POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=4 THEN 'RETURN POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=5 THEN 'CREDIT MEMO POSTED HISTORY'
WHEN dbo.PM30200.DOCTYPE=6 THEN 'PAYMENT POSTED HISTORY'
END AS TRXSOURCE,
dbo.PM30200.DOCDATE,
dbo.PM30200.DOCNUMBR AS DOCNUMBR,
dbo.PM30200.VENDORID,
ISNULL(CASE
WHEN dbo.PM30200.DOCTYPE=1 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE=2 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE=3 THEN dbo.PM30200.DOCAMNT
END,0) AS CREDIT,
ISNULL(CASE
WHEN dbo.PM30200.DOCTYPE=4 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE=5 THEN dbo.PM30200.DOCAMNT
WHEN dbo.PM30200.DOCTYPE=6 THEN dbo.PM30200.DOCAMNT
END,0) AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM30200
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM30200.VENDORID
WHERE PM30200.VOIDED = 0
---------------------------------------------------------------
UNION ALL
----------PM UNPOSTED-----------------------------------------------
SELECT
CASE
WHEN dbo.PM10000.DOCTYPE=1 THEN 'INVOICE UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=2 THEN 'FINANCE CHARGES UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=3 THEN 'MIS CHARGES UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=4 THEN 'RETURN UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=5 THEN 'CREDIT MEMO UNPOSTED'
WHEN dbo.PM10000.DOCTYPE=6 THEN 'PAYMENT UNPOSTED'
END AS TRXSOURCE,
dbo.PM10000.DOCDATE,
dbo.PM10000.DOCNUMBR AS DOCNUMBR,
dbo.PM10000.VENDORID,
ISNULL(CASE
WHEN dbo.PM10000.DOCTYPE=1 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=2 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=3 THEN dbo.PM10000.DOCAMNT
END,0) AS CREDIT,
ISNULL(CASE
WHEN dbo.PM10000.DOCTYPE=4 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=5 THEN dbo.PM10000.DOCAMNT
WHEN dbo.PM10000.DOCTYPE=6 THEN dbo.PM10000.DOCAMNT END,0) AS DEBIT,
dbo.PM00200.VENDNAME
FROM dbo.PM10000
INNER JOIN dbo.PM00200 ON dbo.PM00200.VENDORID = PM10000.VENDORID

Hope that this helps!

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com