Vendor Statement For 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 INNER
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

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

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS