I am trying to wrap my head around building an analytics tool for sales order profitability.
I am hoping to capture any experience out there on the gotcha’s when trying to do this.
We typically have a mix of non qty bearing part (eg freight), BTO parts and make to order job parts on a sales order.
What are the pitfalls of aggregating the costs from the related PartTran records for each SO line? What will I miss? Do I risk double counting anything?
Try using the InvcDtl table. It shows costs, qty, and price.
The only way I’ve found of doing this that hasn’t had people coming back to me complaining of inconsistencies has been to work off the GL data after all the costs have been posted. You can work off PartTran if you’re careful, and as @Jason_Woods says, the relevant data is also in the Invoice tables. For “live” estimates of GP we also pull costs into custom fields in the Sales Order tables as the orders are entered. None of that tends to quite match the final figures produced by Finance, though, and at our company that niggles people so our reporting is based on the GL.