Skip to content

SQL Scripts – SOP to POP Margin Analysis

April 9, 2010

The following script will look at all open sales line items and purchase line items that are linked together and evaluate their margins.  The default script looks for margins less than 25% or .25.  You can change your percentage by change the @marginPct value in the SET command.

The script using the following views:

SOP to POP Links (tspvSopToPop)

Open Sales Line Items (tspvSalesLineItem)

Purchase Order Line Items (tspvPurchaseOrderLineItems)

Open Sales Documents (tspvSalesDocument)

**SQL Script***

DECLARE @marginPct AS FLOAT
SET @marginPct=.25

SELECT  d.Doc_Date,
        d.Sales_Doc_ID,
        a.Sales_Doc_Num,
        d.Customer_Name,
        a.PO_Number,
        a.Qty_On_PO,
        a.Qty_Received,
        b.Item_Number,
        b.Item_Description,
        b.Is_Non_Inventory,
        Is_Dropship,
        Unit_Of_Measure,
        d.Warehouse_Code,
        Unit_Price,
        Extended_Price,
        Quantity,
        Purchasing_Status,
        Vendor_Name,
        c.Unit_Cost,
        c.Extended_Cost,
        [Total_Margin%]=(1 – ( c.Unit_Cost / ( b.Unit_Price + .01 ) ))
FROM    dbo.tspvSopToPop a
JOIN    dbo.tspvSalesLineItem b ON a.Sales_Doc_Num = b.Sales_Doc_Num
                                   AND a.SO_Line_Item_Seq = b.Line_Num
JOIN    dbo.tspvPurchaseLineItem c ON a.PO_Number = c.PO_Number
                                      AND a.PO_Line_Item_Seq = c.Line_Seq
JOIN    dbo.tspvSalesDocument d ON d.Sales_Doc_Num = a.Sales_Doc_Num
WHERE   ( 1 – ( c.Unit_Cost / ( b.Unit_Price + .01 ) ) < @marginPct )

Advertisements

From → Dynamics GP

2 Comments
  1. Patrick permalink

    Ron

    I’m a newby so keep it simple, but my question is whether this view and it’s supporting views will work with a system set up for extended pricing. We are using 2010 Dynamics with extended pricing on a server farm system. I understand basic SQL queries and I am learning view and some support type actions. I believe this comparison would be valued by both our purchasing department and management.

    Patrick

  2. Ron permalink

    Patrick,

    This view will work. All it is doing is looking at the sales order’s line item pricing (which has already been determined by your Extended Pricing module). It then looks to see if you have a purchase order linked to it. If so, then it will check your linked purchase orders “purchase price” and compare that to the sales orders “selling price” and compare that margin to the margin you set in the @marginPct variable.

    Let me know if you need anything else.

    Ron

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: