Skip to content

SQL Scripts–SOP to POP Freight Analysis

September 13, 2011

The following SQL Script will show analyze the freight you charged to a customer versus the freight you were billed from you vendor provided there is a SOP to POP link between the sales order and purchase order.

This question was raised in the Dynamics GP Community by JCohen:

We are looking for a way to associate outgoing freight charges from 3rd party carriers (not invoiced to customers) with applicable sales invoices in order to better track the profitability of customers. It seems, to me, that it is the flip-side of landed costs but I have not found a way to link them. Any suggestions?

https://community.dynamics.com/product/gp/f/32/t/63015.aspx

***SQL Script***

SELECT  a.Sales_Doc_Num,
        c.DOCNUMBR Payables_Document,
        c.PORDNMBR PO_Number,
        c.POSTEDDT Posted_Date,
        c.FRTAMNT Vendor_Freight_Amount,
        a.Freight Sales_Freight_Charged,
        [Freight_Diff]=a.Freight-c.FRTAMNT
FROM    dbo.tspvSalesDocumentHistory a
        JOIN dbo.tspvSopToPop b ON a.Sales_Doc_Num = b.Sales_Doc_Num
        JOIN pm30200 c ON b.PO_Number = c.PORDNMBR

***One thing to note, since this script combines Sales Document Headers and Purchase Line Items (Headers vs Details) the freight analysis for each Sales Order will be repeated for the number of lines on the Purchase Order.  This can easily be cleaned up in a report.  Feel free to Contact Me if you need any help.***

About these ads

From → Dynamics GP, SQL

3 Comments
  1. Nothing may be farther within the truth. >>Here you just need to discuss your preferences and
    design of the cards. Cream Cheese Wontons.

  2. Check the grocery store ads for every stofe in your area.

    The Sunday paper is another good place to look. Try tto find
    out stores wwhere youu can easily use these coupons.

Trackbacks & Pingbacks

  1. SQL Scripts–SOP to POP Freight Analysis « Real Life Dynamics User - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community

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

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: