February 4, 2009 
Here is a view to show all Sales Order Processing line items with the SOP-POP link in Dynamics GP. You can use this with either SmartList Builder Crystal Reports, as I have made sure not to put any spaces in the column names. I maintain a list of all the SQL code I’ve published on my GP Reports page so if you’re looking for other scripts, take a look there.
~~~~~
CREATE VIEW view_SOP_POP_Link AS /** view_SOP_POP_Link Created Feb 4m 2009 by Victoria Yudin - Flexible Solutions, Inc. For updates see https://victoriayudin.com/gp-reports/ Shows all SOP line items with SOP - POP link present Updated on Sep 1, 2018 to fix join and unions **/ SELECT SH.CUSTNMBR CustomerID, SH.CUSTNAME CustomerName, S.SOPNUMBE OrderNumber, S.ITEMNMBR Item, S.ITEMDESC ItemDescription, L.PONUMBER PONumber, PH.VENDORID VendorID, PH.VENDNAME VendorName FROM (SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC FROM SOP10200 UNION SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC FROM SOP30300) S INNER JOIN SOP60100 L ON S.SOPTYPE = L.SOPTYPE AND S.SOPNUMBE = L.SOPNUMBE AND S.LNITMSEQ = L.LNITMSEQ LEFT OUTER JOIN (SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME FROM SOP10100 UNION SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME FROM SOP30200) SH ON SH.SOPTYPE = S.SOPTYPE AND SH.SOPNUMBE = S.SOPNUMBE LEFT OUTER JOIN (SELECT PONUMBER, VENDORID, VENDNAME FROM POP10100 UNION SELECT PONUMBER, VENDORID, VENDNAME FROM POP30100) PH ON PH.PONUMBER = L.PONUMBER /** the following will grant permissions to this view to DYNGRP, leave this section off if you do not want to grant permissions **/ GO GRANT SELECT ON view_SOP_POP_Link TO DYNGRP
~~~~~
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.
Victoria Yudin 
Hi Victoria,
I tweeted your SQL above a little to show the functional cost per line item in PO which linked to SO, but the SO/PO line items repeats on the # of line items, Can you help? I am not familiar with the “join” function.
SELECT DISTINCT SH.CUSTNMBR CustomerID, SH.CUSTNAME CustomerName,
S.SOPNUMBE OrderNumber, S.ITEMNMBR Item,
S.ITEMDESC ItemDescription, L.PONUMBER PONumber,
PH.VENDORID VendorID, PH.UNITCOST Functional_Cost, PH.ORUNTCST Originating_Cost
FROM
(SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC
FROM SOP10200
UNION ALL
SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC
FROM SOP30300) S
RIGHT OUTER JOIN
SOP60100 L
ON S.SOPTYPE = L.SOPTYPE AND S.SOPNUMBE = L.SOPNUMBE
AND S.LNITMSEQ = L.LNITMSEQ
LEFT OUTER JOIN
(SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME
FROM SOP10100
UNION ALL
SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME
FROM SOP30200) SH
ON SH.SOPTYPE = S.SOPTYPE AND SH.SOPNUMBE = S.SOPNUMBE
RIGHT JOIN
(SELECT PONUMBER, VENDORID, UNITCOST, ORUNTCST
FROM POP10110
UNION ALL
SELECT PONUMBER, VENDORID, UNITCOST, ORUNTCST
FROM POP30110) PH
ON PH.PONUMBER = L.PONUMBER
LikeLike
Joyce,
Try this:
select sh.CUSTNMBR CustomerID, sh.CUSTNAME CustomerName, s.SOPNUMBE OrderNumber, s.ITEMNMBR Item, s.ITEMDESC ItemDescription, l.PONUMBER PONumber, ph.VENDORID VendorID, ph.VENDNAME VendorName, pd.UNITCOST FunctionalCost, pd.ORUNTCST OriginatingCost from (select SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC from SOP10200 union select SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC from SOP30300) s inner join SOP60100 l on s.SOPTYPE = l.SOPTYPE and s.SOPNUMBE = l.SOPNUMBE and s.LNITMSEQ = l.LNITMSEQ left outer join (select SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME from SOP10100 union select SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME from SOP30200) sh on sh.SOPTYPE = s.SOPTYPE and sh.SOPNUMBE = s.SOPNUMBE left outer join (select PONUMBER, VENDORID, VENDNAME from POP10100 union select PONUMBER, VENDORID, VENDNAME from POP30100) ph on ph.PONUMBER = l.PONUMBER left outer join (select PONUMBER, ORD, UNITCOST, ORUNTCST from POP10110 union select PONUMBER, ORD, UNITCOST, ORUNTCST from POP30110) pd on l.PONUMBER = pd.PONUMBER and l.ORD = pd.ORD-Victoria
LikeLike
Thanks Victoria! It works! 🙂
LikeLike
Victoria, I love this SQL View and it has been very helpful! I have a client that would like to see the Sales Order, associated Sales Invoice, and the linked PO Number and Vendor Details. In thinking through it, I would imagine I would just use the SOP30200 table?
LikeLike
Hi Lisa,
For the sales information, you are right, link to the SOP30200 – just remember you might have multiple invoices for the same order and the same line item, even, so you might need to be careful about how the information is shown. For the PO information, you would want to link to the POP10100 and POP30100 tables, since the PO could be in either, depending on whether it was moved to history or not.
-Victoria
LikeLike
This is fantastic – thank you! I added the QTYONPO and QTYRECVD from the SOP60100- in this company we only use Each as the unit of measure for everything so that works for us.
LikeLike
Victoria,
I love this SQL view!!! I have been bashing my head up against the wall trying to design one that would just show the Sales Order/PO numbers without line items. I have figured out how to remove line items from this view, however, the SO/PO repeats depending on the number of lin items. Any ideas???
LikeLike
Mark,
Without seeing your modifications, the only thing I can think of is putting a DISTINCT after the SELECT at the very top. Can you try that and let me know if that helps?
-Victoria
LikeLike
Victoria,
It looks like that works. Thanks for your help.
Mark
LikeLike
Thanks for your quick reply.
I finally figured it out by checking the tables each time I a made a change. Here’s the code I was able to use.
I use foxpro to communicate to the database but you get my gist.
***First I update the Purchase Order line Detail and change quantity uncommitted to be equal to the committed quantity
“update pop10110 set qtyuncmtbase=qtycmtbase where ponumber='”+ALLTRIM(ponumber)+”‘ AND ord=”+ALLTRIM(STR(ord))
***Then I update the Purchase Order line Detail and change quantity committed base to zero
“update pop10110 set qtycmtbase=0 where ponumber='”+ALLTRIM(ponumber)+”‘ AND ord=”+ALLTRIM(STR(ord))
***Then I Update Sales Order Line Detail and change the quantity on the PO to zero and purchase status to 2
“update sop10200 set qtyonpo=0, purchstat=2 where sopnumbe='”+ALLTRIM(sopnumbe)+”‘ and lnitmseq=”+ALLTRIM(STR(lnitmseq))
***And Then I finally delete the link
“delete from sop60100 where sopnumbe='”+ALLTRIM(sopnumbe)+”‘ and lnitmseq=”+ALLTRIM(STR(lnitmseq))
Before all this I stored the PO number in a user defined field in IV00106.
The first time I tried months back, I only deleted the record in SOP60100 but it errored back that it was committed still. These seem to do the trick.
LikeLike
John,
Wow! Thank you for sharing all this detail. I suspected there were commitment statuses on the POs and Sales Orders, but this is actually more than I thought would be there. Glad you were able to get it working.
-Victoria
LikeLike
Victoria,
Great Blog!
Here’s a question for you about SOP60100 table.
We sell many products that are shipped by case (Purchase Order) but billed by the LB (Sales Order).
We manually delete PO link to the sales order to bill the customer via PO Commitment for Sales Document. I have set up a macro to do this for me but I am looking for a better way via SQL.
I tried to delete the link in SOP60100 in the past, but It gives me errors and won’t allow me to enter the Quantity Ordered.
The question is, do you have a script to programmically delete the PO/SO link via sql.
Thanks
John
LikeLike
John,
I do not have a SQL script for deleting the SOP-POP link, it’s not something that I have been asked about before. I would recommend running SQL Profiler to see everything that is done when you delete the link manually, that should give you enough information to create a script.
-Victoria
LikeLike