Keep up to date with the latest information about Microsfot Dynamics GP through articles from the Perfect Image and azurecurve blog.
SQL View For Customer Item Link From SOP Transaction Line
October 9th, 2015 by
This one came up from a query a client had about linking a sales order transaction line to the customer item in SmartList Builder. The problem is that to join two tables together, you need all of the key fields to be on the same table; unfortunately, with the SOP Transaction table, this isn’t the case when you want to link to the customer item.
SOP10100 (Sales Transaction Work) holds the CUSTNMBR (Customer Number), but SOP10200 (Sales Transaction Amounts Work) holds the ITEMNMBR (Item Number) which are both needed to link to SOP60300 (Sales Customer Item Cross Reference) which holds the customer item number and description.
While there may be a way to do this in SmartList Builder I’ve not been able to work it out (other than using two calculated fields), it is easier, quicker and more reusable, to create a simple SQL View which returns the relevant information.
In this case the view I created works only for transactions which are at a status of work:
CREATE VIEW uv_PI_SOPCustomerItemLink AS SELECT SOP101.CUSTNMBR ,SOP102.ITEMNMBR ,SOP603.CUSTITEMNMBR ,SOP603.CUSTITEMDESC FROM SOP10200 AS SOP102 INNER JOIN SOP10100 AS SOP101 ON SOP101.SOPNUMBE = SOP102.SOPNUMBE AND SOP101.SOPOwner = SOP102.SOPOwner INNER JOIN SOP60300 AS SOP603 ON SOP603.CUSTNMBR = SOP101.CUSTNMBR AND SOP603.ITEMNMBR = SOP102.ITEMNMBR GO GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP GO
The SQL above includes the Grant statement used to add select permissions for the DYNGRP.