I have published SQL views that return all posted Payables payments in Dynamics GP and Payables apply information to help identify what payments (or credits) paid off an invoice, however another frequent request is for a list of payments and their apply information. Below is a view that returns a list of all posted payments with details on how they were applied. Any payment that was applied to more than one transaction will show up as multiple lines.
For other SQL views on GP data, please visit my GP Reports page.
create view view_Payables_Apply_Detail as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ -- view_Payables_Apply_Detail -- Created Oct 22 2009 by Victoria Yudin, Flexible Solutions Inc -- For updates see http://victoriayudin.com/gp-reports/ -- Does not take Multicurrency into account. -- Will return multiple lines for payments that were applied to -- more than one transaction. -- Tables Used: -- PM20000 – Open/Posted Transactions -- PM30200 – Historical/Paid Transactions -- PM00200 – Vendor Master -- PM10200 – Apply To Work/Open -- PM30300 – Apply To History -- Updated Nov 19 2013 to add apply to doc due date -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ select P.VENDORID Vendor_ID, V.VENDNAME Vendor_Name, V.VNDCHKNM Vendor_Check_Name, case P.PYENTTYP when 0 then 'Check' when 1 then 'Cash' when 2 then 'Credit Card' when 3 then 'EFT' else 'Other' end Payment_Type, case when P.PYENTTYP in (0,1,3) then P.CHEKBKID else '' end Checkbook_ID, case P.PYENTTYP when 2 then P.CARDNAME else '' end Credit_Card_ID, P.DOCDATE Payment_Date, P.PSTGDATE Payment_GL_Date, P.VCHRNMBR Payment_Voucher_Number, P.DOCNUMBR Payment_Document_Number, P.DOCAMNT Payment_Functional_Amount, coalesce(PA.APTVCHNM,'') Apply_To_Voucher_Number, case PA.APTODCTY when 1 then 'Invoice' when 2 then 'Finance Charge' when 3 then 'Misc Charge' else '' end Apply_To_Doc_Type, coalesce(PA.APTODCNM,'') Apply_To_Doc_Number, coalesce(PA.APTODCDT,'1/1/1900') Apply_To_Doc_Date, coalesce(PA.ApplyToGLPostDate,'1/1/1900') Apply_To_GL_Date, coalesce(AD.DUEDATE,'1/1/1900') Apply_To_Due_Date, coalesce(PA.APPLDAMT,0) Applied_Amount from (select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME from PM30200 union select VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME from PM20000) P inner join PM00200 V on P.VENDORID = V.VENDORID left outer join (select VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT from PM10200 union select VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT from PM30300) PA on P.VCHRNMBR = PA.VCHRNMBR and P.VENDORID = PA.VENDORID and P.DOCTYPE = PA.DOCTYPE left outer join -- added for due date (select DOCTYPE, VCHRNMBR, DUEDATE from PM30200 union select DOCTYPE, VCHRNMBR, DUEDATE from PM20000) AD on PA.APTODCTY = AD.DOCTYPE and PA.APTVCHNM = AD.VCHRNMBR where P.DOCTYPE = 6 and P.VOIDED = 0 -- add permissions for DYNGRP go grant select on view_Payables_Apply_Detail to DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone