SQL View – SOP to POP Links

The following view returns the all the Sales Order Processing to Purchase Order Processing (SOP to POP) links in your GP database.

This view is also a modification of the spvSopToPop view from Salespad (www.salespad.net).

You can name the view whatever you want by change the [tspvSopToPop] below.  Also the USE [TSP] tells the script to only execute on my database named TSP.  You will need to modify the [TSP] to be whatever your database name is.

USE [TSP]
GO
/****** Object:  View [dbo].[tspvSopToPop]    Script Date: 04/06/2010 10:53:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[tspvSopToPop]
as
select
[Sales_Doc_Num]=sopnumbe,
[Sales_Doc_Type]=case when soptype=’1′ then ‘Quote’ when soptype=’2′ then ‘Order’ when soptype=’3′ then ‘Invoice’ when soptype=’4′ then ‘Return’ when soptype=’5′ then ‘Backorder’ end,
[SO_Line_Item_Seq]=lnitmseq,
[PO_Number]=ponumber,
[PO_Line_Item_Seq]=ord,
[Qty_On_PO]=qtyonpo,
[Qty_on_PO_Base]=qtyonpobase,
[Qty_Received]=qtyrecvd,
[Qty_Received_PO_Base]=qtyrecinbase,
[Location]=locncode,
[Dex_Row_ID]=Dex_Row_ID
from sop60100