Coding the Variation Report in SQL

In the last newsletter, we discussed the interactions between BoMs and Production orders. This time we'll dive deeper into building a report to indicate variations between items in a BoM and the corresponding Items in a productions order.

We left off with a formula of

variation1 = BoM Quantity * Planned Quantit, y Header - Planned quantity row

and this is wrong. The BoM quantity is based on an expected quantity. Its quantity may be different then the quantity in the production order. To be sure we are using the correct quantities, we'll take the quantity in the row of the BoM and divide it by the quantity of the header so we have one unit.

variation1 = ((BoM line / BoM Header) * Planned Qty Header) - Planned Qty row

So for coding in SQL, we'll need four tables OITT and ITT1 for the BoM, and OWOR and WOR1 for the Production Order. I'll code for listing the production order first, making a report listing the item code for the production order and the rows, and the planned quantity for the production order then, base and planned quantity for the rows.

SELECT
? ?T0.Itemcode,
? ?t1.Itemcode,
? ?T1.ItemName,?
? ?t0.PlannedQty as "Header Qty",?
? ?t1.PlannedQty?as "Row Qty",
? ?t1.BaseQty
FROM 
   OWOR T0
   INNER JOIN WOR1 T1 ON T0.Docentry = T1.DocEntry
WHERE 
   T0.DocNum = [%0]
        

That's relatively simple if you've taken my course SAP Business One reporting and customization or have some knowledge of SQL. Expand the query by adding information about the BoM next. I'll add the item code to make sure they match and set up the tables with Inner Joins

SELECT
? ?T0.Itemcode,
? ?T1.Itemcode,
? ?T1.ItemName,?
? ?T0.PlannedQty as "Header Qty",?
? ?T1.PlannedQty?as "Row Qty",
? ?T1.BaseQty,
   T3.code
FROM 
   OWOR T0
   INNER JOIN WOR1 T1 ON T0.Docentry = T1.DocEntry
   INNER JOIN OITT T2 ON T0.ItemCode = T2.Code
? ?INNER JOIN ITT1 T3 ON T2.Code = T3.father AND?T1.LineNum = T3.ChildNum
WHERE 
   T0.DocNum = [%0]        

Notice ITT1 has two conditions: The link between the rows and header of the BoM and the line numbers of rows in the production order.

I can run this for production order number 15 and see that the item codes match up.

Now we get into our formulas. You might be tempted to make our first formula for getting the adjusted Bom Quantity with this

T3.Quantity/T2.Qauntity        

While yes, SAP misspelld Quantity for T2(OITT), that is not what wrong here. What happens if T2 is zero for any reason? Your query will crash. Even in cases protected from zeroes, I tend to get paranoid around division by zero. Instead, I'll place the formula in a CASE so I handle the zero.

SELECT
? ?T0.Itemcode,
? ?T1.Itemcode,
? ?T1.ItemName,?
? ?T0.PlannedQty as "Header Qty",?
? ?T1.PlannedQty?as "Row Qty",
? ?T1.BaseQty,
   T3.code,
   CASE 
      WHEN T2.Qauntity <> 0 THEN (T3.Quantity / T2.Qauntity )
      ELSE 0 
   END AS "BoM Base Qty"
FROM 
   OWOR T0
   INNER JOIN WOR1 T1 ON T0.Docentry = T1.DocEntry
   INNER JOIN OITT T2 ON T0.ItemCode = T2.Code
? ?INNER JOIN ITT1 T3 ON T2.Code = T3.father AND?T1.LineNum = T3.ChildNum
WHERE 
   T0.DocNum = [%0]        

Using the planned quantity from the header, I multply against this result. I'll copy and modify the case like this:

SELECT
? ?T0.Itemcode,
? ?T1.Itemcode,
? ?T1.ItemName,?
? ?T0.PlannedQty as "Header Qty",?
? ?T1.PlannedQty?as "Row Qty",
? ?T1.BaseQty,
   T3.code,
   CASE 
      WHEN T2.Qauntity <> 0 THEN (T3.Quantity / T2.Qauntity )
      ELSE 0 
   END AS "BoM Base Qty",
   CASE 
      WHEN T2.Qauntity <> 0 
         THEN (T3.Quantity / T2.Qauntity ) * T0.PlannedQty
      ELSE 0 
   END AS "Expected Qty"

FROM 
   OWOR T0
   INNER JOIN WOR1 T1 ON T0.Docentry = T1.DocEntry
   INNER JOIN OITT T2 ON T0.ItemCode = T2.Code
? ?INNER JOIN ITT1 T3 ON T2.Code = T3.father AND?T1.LineNum = T3.ChildNum
WHERE 
   T0.DocNum = [%0]        

I'll do this again to get the difference between expected and the planned quantity entered on the Production order.

SELECT
? ?T0.Itemcode,
? ?T1.Itemcode,
? ?T1.ItemName,?
? ?T0.PlannedQty as "Header Qty",?
? ?T1.PlannedQty,
? ?T1.BaseQty,
   T3.code,
   CASE 
      WHEN T2.Qauntity <> 0 THEN 
         (T3.Quantity / T2.Qauntity )
      ELSE 0 
   END AS "BoM Base Qty",
   CASE 
      WHEN T2.Qauntity <> 0 THEN  
         (T3.Quantity / T2.Qauntity ) * T0.PlannedQty
      ELSE 0 
   END AS "Expected Qty",
   CASE 
      WHEN T2.Qauntity <> 0 THEN 
         ((T3.Quantity / T2.Qauntity ) * T0.PlannedQty) - T1.PlannedQty
      ELSE T1.PlannedQty 
   END AS "Variation"


FROM 
   OWOR T0
   INNER JOIN WOR1 T1 ON T0.Docentry = T1.DocEntry
   INNER JOIN OITT T2 ON T0.ItemCode = T2.Code
? ?INNER JOIN ITT1 T3 ON T2.Code = T3.father AND?T1.LineNum = T3.ChildNum
WHERE 
   T0.DocNum = [%0]        

In the else, I make a change to handle our division by zero problem. If the Expected Qty is Zero I'll give the value of T1.PlannedQty.

I'll run this for Production order 15, which is for item P10001- PC - 8x core, DDR 32GB, 2TB HDD.

And it works, well at least for now. Try a production order 156 for SDL-01 Huli Pizza Bites and you'll see something is very wrong.

Production order 156 looks like this:

But the associated BoM Looks Like this:

When you get fancy with the rows, the query breaks. In the next newsletter, we'll discuss how to solve these bugs and what to do as your work when you're comparing detail rows in different tables.