Skip to content

SQL View – SOP to POP Links

April 6, 2010

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

Advertisements

From → Dynamics GP

7 Comments
  1. Nice one. Just used this researching an issue for a customer. Many thanks. Ian.

  2. Ron permalink

    Thanks for the feedback Ian. This is such a great little view. For whatever reason, I have never thought that the SOP to POP table is not the easiest to read. Congrats on your MVP status!

  3. Mark Barzotto permalink

    Does this work on open Sales orders to open Purchase orders? or just historical sales orders and purchase orders?

Trackbacks & Pingbacks

  1. SQL Scripts – SOP to POP Margin Analysis « Real Life Dynamics User
  2. SQL View – SOP to POP Links - DynamicAccounting.net
  3. SQL Scripts – SOP to POP Margin Analysis - Real Life Dynamics User (RLDU)
  4. DynamicAccounting.net | SQL View – SOP to POP LinksSQL View – SOP to POP Links - DynamicAccounting.net

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: