You’re offline. This is a read only version of the page.
3,015
I am using report builder 2.0, GP 2010
Can someone help me write a SQL query that joins the PayablesTransaction and AccountsTransactions view?
PayablesTransaction:
Purchases Amount
Doc Date
Vendor Name
Vendor ID
Document Number
AccountsTransactions:
Account Number
Account Category Number
*This post is locked for comments
KJ, what is the "TOP 100" in your query.
It might be throwing off what I need. It is limiting the amount of transactions. There should be a lot more than what is being displayed.
Mahmoud, it is the purchasing account.
Find the "All Posted Payable Transactions" script on Ms. Victoria website, It may be missing the account number based on you requirements;
SQL view for all posted Payables transactions in Dynamics GP
Can you specify which account are you looking for ? Is it the Purchasing or Account Payable distribution type ?
Hi,
Following script should help you.
SELECT DISTINCT TOP 100 PM00200.VENDORID,VENDNAME,DOCDATE,DOCAMNT as 'Purchase Amount',DOCNUMBR,ACTNUMST as 'Account Number',ACCATNUM
FROM dbo.PM30200 WITH (NOLOCK) join dbo.PM30600 WITH (NOLOCK) on PM30200.VCHRNMBR = PM30600.VCHRNMBR
JOIN dbo.PM00200 WITH (NOLOCK) on PM30200.VENDORID = PM00200.VENDORID
join dbo.GL00105 WITH (NOLOCK) ON PM30600.DSTINDX = ACTINDX
join dbo.GL00100 WITH (NOLOCK) on GL00105.ACTINDX = GL00100.ACTINDX
thanks!
KJ
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (