SQL Scripts–SOP to POP Freight Analysis

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.***