Notifications
Announcements
No record found.
I am looking for a SQL Query to return all AP Vouchers.
I can run AP Voucher Report but need a Query since I need to Join it with Project Tracking tables.
*This post is locked for comments
Do you want all vouchers? Work(PM10000), Open(PM20000) and Historical(PM30200)?
All posted transactions
All posted transactions will be in either the PM20000(open) or PM30200(historical) tables. What do you mean by project tracking tables? PA Billing? Purchase orders?
You can use the pm20000..vchrnmbr and pm20000.docnumbr and the pm30200..vchrnmbr and pm30200.docnumbr and link back to the PA tables.
Thanks but I was looking for an actual query, not just the table name.
You can checkout the below Link from Victoria. There are many other helpful views also you might be able to get.
https://victoriayudin.com/2009/10/12/sql-view-for-all-posted-payables-transactions-in-dynamics-gp/
Here is a small section of code from the center of one of my scripts. I have an external table full of transactions(jl) that is full of voucher and document numbers. This links them to payables documents. The loop goes through all current jobs. You probably need something different. There are many different project document types and their data goes into different tables. You are going to need to research which tables have the data you need and then build your query from there. Go to Tools->Resource Descriptions->Tables and choose the Project Series to get started. You can then go into SSMS and look in the tables to confirm which tables to use. There is no simple query for this. I spent months writing and refining mine based on what I needed.
SELECT distinct rtrim(PACONTNUMBER) as pacontnumber
FROM [PA01101]
) as P
on jl.jobnumber = p.PACONTNUMBER
LEFT OUTER JOIN [pm10000] as pw
ON jl.transnmbr = pw.vchnumwk and jl.docnumbr = pw.docnumbr
LEFT OUTER JOIN [pm20000] as po
ON jl.transnmbr = po.vchrnmbr and jl.docnumbr = po.docnumbr
LEFT OUTER JOIN [pm30200] as ph
ON jl.transnmbr = ph.vchrnmbr and jl.docnumbr = ph.docnumbr
Thank you!
Under review
Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
mtabor 1
