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