Hello,
Has anyone created a script that will link purchase order numbers in the purchasing module to the journal entry number in the financial module? One of the accounts we perform a reconciliation for out of the financial module would be dynamic if we can show the purchase order number associated with the transaction. Whether its an SQL script or from the database view in Smartlist Designer, your guidance will be much appreciated.
Thank you,
JME
*This post is locked for comments
Can you paste here the whole SQL script to have a look at the joins ? this looks rather strange to me
Apologies, should have stated the report I'm running is filtering to the Accrued Payable GL account, it should only show one line per debit or credit depending on if it's a receiving transaction or matched invoice - as an example:
This will always return at least 2 records by receipt entry, since the GL transaction will always have a Debit & Credit side for each transaction..
Which means if you want this to work, you'll have to filter out one side of the transaction, based likely on the Posting type of side ..
The join suggested works to bring in the PO# but returns duplicate records in the results.
I attempted both join types available in Excel Report Builder (left outer, and equals) and got the same results.
Do you know of any way to resolve that issue?
Jaime,
There is no need to create new views or link table together.. All you have to do is to pull the relevant fields from [AccountTransactions] & [ReceivingsLineItems] views and join them on the POP Receipt Number .. (which would be the Originating Document Number in the GL transactions view).
Alternatively you may want to have a look at page and her AP SQL views :
https://victoriayudin.com/gp-reports/payables-sql-views/
The precooked SQL views that are already in GP are the same that are used by the various modules in SmartList, Excel & SSRS reports.
Hi Béat,
How do I link the PO # to the receipt # in the Financial module so that one Smartlist report pulls it in. Currently I am running a Smartlist report in Purchasing that displays the PO # & Receipt # and exporting to Excel. Then running a reconciliation Smartlist report in Financial module and exporting to Excel. I'm using an Index Match Formula to pull in the PO# in which the link is the Receipt # and Originating Document #. But I want one Smartlist report I can run out of the Financial module that will add a column for PO#. Is this possible? If so, do you have the SQL script or detail step by step how to link the database tables?
Much Appreciated
HI Jaime,
You could use the existing GP view (in SQL SSMS or SL Designer) called [AccountTransactions] and filter on the "series = Purchasing"..
This will return every GL transactions with the reference to the PO Receipt number and the Vendor ID. With the RCPT number you'd be able to link to the corresponding PO if this is required too.
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,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (