Victoria Yudin

CREATE VIEW view_SOP_POP_Link
AS

/**
view_SOP_POP_Link
Created Feb. 4 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://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

Posted in GP Reports, GP SQL scripts | Tagged GP Reports, GP SQL view, Purchase Order Processing, Sales Order Processing, SQL code | 5 Comments

5 Responses

1. on May 28, 2009 at 11:23 am | Reply John McDonald

   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.

   
   
   • on May 28, 2009 at 11:41 am | Reply Victoria Yudin

     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

     
     
2. on May 27, 2009 at 11:51 am | Reply John McDonald

   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

   
   
   • on May 28, 2009 at 4:14 am | Reply Victoria Yudin

     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

     
     
3. on February 5, 2009 at 9:04 am | Reply SQL View to Show SOP POP Link - DynamicAccounting.net

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