I have tried my best to write a query to get batch cost based on landed cost and A/P invoice or goods receipt but could not get it.
I would be grateful if you can provide me a query which gives the batch cost for purchase taking into account landed cost and AP invoice.
The batch cost should be sum of landed cost and AP invoice.
Any body who answers this will be rwarded with maximum points.
Marcella Rivi replied
If I understood correctly you want the item cost for the items with batch numbers.
The information is stored in the table OITW. I would suggest you to use this quick query to pull the information for batch items only:
SELECT distinct T0.[ItemCode], T0.[ItemName], T1.[AvgPrice], T1.[createDate] FROM OITM T0 INNER JOIN OITW T1 ON T0.ItemCode = T1.ItemCode WHERE T0.[MngMethod] = 'R' or T0.[MngMethod] = 'a'
This query will give you the value of the item cost for every warehouse you have for that item.
If you want to see all the documents added then I would suggest you to get rid of the word Distinct.
SAP Business One Forums Team