I am currently trying to pull this data from cost adjustment, but I can’t find where this data comes from. Can anyone help?
The data lives in the PartCost table.
I couldn’t find this table accessible in a BAQ and am using an SQL query to pull it.
Here is the query:
SELECT Erp.Part.Company,Erp.Part.PartNum,Erp.Part.PartDescription,Erp.PartCost.AvgMaterialCost,Erp.PartWhse.OnHandQty FROM Erp.PartCost JOIN Erp.Part ON Erp.Part.Company=Erp.PartCost.Company AND Erp.Part.PartNum=Erp.PartCost.PartNum JOIN Erp.PartWhse ON Erp.PartWhse.Company=Erp.Part.Company AND Erp.PartWhse.PartNum=Erp.Part.PartNum WHERE AvgMaterialCost > 0 AND OnHandQty > 0
Thanks @hackaphreaka
@hackaphreaka would you know to do this monthly? The SQL report can be filtered by cutoff date, I am not sure how they accomplish this. Thanks
The PartCost table is where the cost of a part at this moment in time is stored. That doesn’t have anything to do with dates as this is not a transactional table.
What exactly are you trying to do here that needs a cutoff date? If you’re trying to find some cost at a historical point in time, that’s going to be more complicated. I think you might be able to get it from PartTran and look for the Adjust Cost transactions.
Its for graphing month by month stock value accurately. It looks like if I pull the most recent PartTran for the part before the cutoff date, I can use the ExtCost of the most recent record to calculate the cost of the part. Thanks!