Quote from Administrator on May 29, 2020, 9:42 am

For Make to Order companies running SAP Business One. This is an excellent little query that pulls data out of the SAP Business One Sales Order, and then explodes the Finished Goods down to the component level (Only 1 level right now), and explodes the BOM qty by the Unit of Measure quantity, comparing to what's on-hand. Shortages are easily identified. So in seconds, the order entry person will know if they can Make to Order what the customer wants. If not, the component Lead Time is also displayed, to calculate when the order can be produced for the customer.

For example, you enter an order for 2 pallets of goods. There are 600 items per pallet. This report checks to see if the components are in stock to make 1200 finished goods i.e. 2 pallets.

***** This query has been designed to be run as a Formatted Search. I used the B1Usability Package to add this query as a button on the Sales Order. If you do not have the B1Usability Package, you need it! Buy it from me! Seriously though, you can add a UDF and connect this query as a Formatted Search to the UDF or any field for that matter. If you do not have the B1Usability Package, simply replace WHERE T0."DocNum" = $[$8.0.NUMBER] with WHERE T0."DocNum" = [%0] so it will then prompt the user for the Sales Order Number.

SELECT
T1."ItemCode" "Finished Good",
CASE
WHEN (T1."NumPerMsr" * T3."Quantity") > T4."OnHand" THEN 'Shortage!'
WHEN (T1."NumPerMsr" * T3."Quantity") < T4."OnHand" THEN 'Good'
ELSE 'Shortage'
END AS "Component Status",
T3."Code" "Ingrediants",
T4."ItemName" "Ingr. Desc",
T1."NumPerMsr" * T3."Quantity" "Exploded Qty",
T4."OnHand" "In Stock",
T4."IsCommited" "Allocated to WIP",
T4."OnOrder" "On Order",
T4."LeadTime" "Lead Time"
FROM ORDR T0  INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
RIGHT JOIN OITT T2 ON T1."ItemCode" = T2."Code"
RIGHT JOIN ITT1 T3 ON T2."Code" = T3."Father"
RIGHT JOIN OITM T4 ON T3."Code" = T4."ItemCode"
WHERE T0."DocNum" = $[$8.0.NUMBER]