SQL view to show items with SOP POP link


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. 4 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
**/

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

LEFT OUTER JOIN
(SELECT PONUMBER, VENDORID, VENDNAME
FROM POP10100
UNION ALL
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.

12 Responses to “SQL view to show items with SOP POP link”

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

    • 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

      Like

Trackbacks/Pingbacks

  1. DynamicAccounting.net | SQL View to Show SOP POP LinkSQL View to Show SOP POP Link - DynamicAccounting.net - April 25, 2016

    […] on February 5, 2009 by Mark Polino Victoria Yudin has a SQL View to show items with a SOP POP link on her […]

    Like

  2. SQL View to Show SOP POP Link - DynamicAccounting.net - February 5, 2009

    […] View to Show SOP POP Link Victoria Yudin has a SQL View to show items with a SOP POP link on her blog. Published: Wednesday, February 04, 2009, 09:50 […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: