SQL Script Linking Purchase Order Number in Purchasing Module to Journal Entry Number in Financial Module

(0) Share

Report

Posted on by Community Member

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

I have the same question (0)

All responses (7)

Answers (0)

Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: SQL Script Linking Purchase Order Number in Purchasing Module to Journal Entry Number in Financial Module

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: SQL Script Linking Purchase Order Number in Purchasing Module to Journal Entry Number in Financial Module

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

Was this reply helpful? Yes No
Suggested answer

Beat Bucher GP Gee... 28,058 Moderator on at

Like (1)

Report

RE: SQL Script Linking Purchase Order Number in Purchasing Module to Journal Entry Number in Financial Module

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.

Was this reply helpful? Yes No
AClark714 10 on at

Like (0)

Report

RE: SQL Script Linking Purchase Order Number in Purchasing Module to Journal Entry Number in Financial Module

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?

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: SQL Script Linking Purchase Order Number in Purchasing Module to Journal Entry Number in Financial Module

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 ..

Was this reply helpful? Yes No
AClark714 10 on at

Like (0)

Report

RE: SQL Script Linking Purchase Order Number in Purchasing Module to Journal Entry Number in Financial Module

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:

Was this reply helpful? Yes No
Beat Bucher GP Gee... 28,058 Moderator on at

Like (0)

Report

RE: SQL Script Linking Purchase Order Number in Purchasing Module to Journal Entry Number in Financial Module

Can you paste here the whole SQL script to have a look at the joins ? this looks rather strange to me

Was this reply helpful? Yes No