Job Cost Detail Query for SAP Business One
SAP Business One does a great job of capturing material and labor to a Production Order. However, the reporting side of the Job is fairly limited. Here is a report that list all material going into a Production Order and all items coming out of a Production Order. In this report, I have hard coded my MSRP price list as Price List 4, so you may want to change that reference to match your MSRP price list.
SELECT
T0."BaseRef" "WO",
'Production Issue' "Direction",
T0."DocDate" "Transaction Date",
T0."ItemCode" "Item",
T0."Dscription" "Item Desc",
T0."WhsCode" "Warehouse",
T0."UomCode" "UOM",
T0."Quantity" "Quantity",
T0."Price" "Unit Cost",
T1."Price" "Unit MSRP",
(T0."Quantity" * T0."Price") "Total Cost",
(T0."Quantity" * T1."Price") "Total MSRP"
FROM IGE1 T0 INNER JOIN ITM1 T1 ON T0."ItemCode" = T1."ItemCode" AND T1."PriceList" = 4
WHERE T0."BaseType" = 202
UNION
SELECT
T0."BaseRef" "WO",
'Production Receipt' "Direction",
T0."DocDate" "Transaction Date",
T0."ItemCode" "Item",
T0."Dscription" "Item Desc",
T0."WhsCode" "Warehouse",
T0."UomCode" "UOM",
T0."Quantity" "Quantity",
T0."Price" "Unit Cost",
T1."Price" "Unit MSRP",
(T0."Quantity" * T0."Price") "Total Cost",
(T0."Quantity" * T1."Price") "Total MSRP"
FROM IGN1 T0 INNER JOIN ITM1 T1 ON T0."ItemCode" = T1."ItemCode" AND T1."PriceList" = 4
WHERE T0."BaseType" = 202