13 Replies Latest reply on May 23, 2008 6:51 PM by Brad Windecker

    Inventory Usage / Inventory Turns Report

    Darren Campbell

      I am looking for a report that will show me inventory usage by month for any given item as well as on hand inventory so I can calculate avg months on hand and monthly usage.  I can not use sales by item since a majority of my sales is from a phantom item.

      Any thoughts

      Thanks

      Darren

        • 1. RE: Inventory Usage / Inventory Turns Report

          I think MCBA will give you what you're looking for.  It provides drilldown for a lot of info - receipts, usgage, material balance, stock value -- and calculates turnover based on consumption and average total stock for the period.

          • 2. RE: Inventory Usage / Inventory Turns Report
            James McCord

            Mark,

            Is MCBA an Add-On?
            Where can you get it?

            • 3. RE: Inventory Usage / Inventory Turns Report
              Andee DiCola

              MCBA looks like a SAP code for mySAP or R3 not SAP Business One.

              • 4. RE: Inventory Usage / Inventory Turns Report
                Mike Grant

                Darren: We, too, need a report or query that gives usage by month for any item, to calc months on hand, etc.  Did you figure a way yet?  I thought of trying to first solve it with Crystal Reports, once I figure out the right tables and fields to use and link.  Then it could probably be turned into a B1 query.  I think one of the transaction tables is IGE1 (linked to OIGE), with different BaseTypes, but there are 100+ fields in each table. It will take some time and trial/error.  Your thoughts/plans?  Mike

                • 5. RE: Inventory Usage / Inventory Turns Report
                  Anthony Flores

                  Gentlemen,

                  This report can be made with the Query Generator or SQL program. To find the table, turn on System Information under the View tab at the top left of the screen, move your mouse to the specific form and field and look at the bottom left corner display. You will see that it gives the table, field name etc.

                  Anthony

                  • 6. RE: Inventory Usage / Inventory Turns Report
                    Dean Garrison

                    Darren/Mike,

                    Here is what I use, it gives me 90 day actual usage (sales from Invoices) with a calculated Minimum (1 months avg usage) Maximum ( 2 months avg usage). Not exactly what you wanted, but it should put you on the right track and allow you to get what you need. Anthony just posted a way to find your table/column references so you can modify this for use from other sources than OINV/INV1. 

                    SELECT sum (T0.Quantity) as '90DayUse',Ceiling(sum (T0.Quantity)/3) AS 'MIN',(sum (T0.Quantity)*2) AS 'MAX',  T0.ItemCode AS 'Item No.', T1.ItemName AS 'Item Description'
                    FROM

                      .[OITM] T1 INNER JOIN .[INV1] T0 ON T1.ItemCode = T0.ItemCode  INNER JOIN .[OINV] T2 ON T2.DocEntry = T0.DocEntry AND T1.Canceled <> 'Y'
                    WHERE T2.DocDate >= Dateadd(DD,-90, GETDATE())
                    GROUP BY T0.ItemCode, T1.ItemName
                    ORDER BY 'Item No.' desc

                    Dean

                    • 7. RE: Inventory Usage / Inventory Turns Report
                      Mike Grant

                      Anthony: I am already aware of how to find tables and field names/descriptions in Query.  The real problem becomes which table (for example, purchasing has ten tables) and which field is the one you want (there are a hundred plus fields in each of the OIGE and IGE1 transaction tables I mentioned earlier).  And they often have odd field names like &quot;BaseType&quot; and a vague description like &quot;Base Document Type.&quot;  What does this mean?? 

                      Only by browsing the data tables and fields one by one, and experimentation, can you determine what you need.  Unless someone else has already figured it out and gives you a tip!  Well, I got a tip: BaseType holds the transaction codes (&quot;-1&quot; for inventory Goods Issued and &quot;202&quot; for Issue to Production). So now I can try to use that information in a Crystal Report or Query.  But should I also add Deliveries from Sales Orders, in order to figure out usage?  And where would I find this data: what table and field?

                      We all need to share these tips and discoveries amongst the members here.  Mike

                      • 8. RE: Inventory Usage / Inventory Turns Report
                        Darren Campbell

                        This is what i had written for my use. It seems to work pretty well - hopefully it has most information.  It shows the information monthly.  I know there are a few glitches such as inv adj but it gives reasonable info to use for planning

                        SELECT OITM.ItemCode, OITM.ItemName AS 'Description', OITM.CardCode AS 'Vendor',
                        SUM(OITW.OnHand) AS 'On Hand', SUM(OITW.OnOrder) AS 'On Order', SUM(OITW.IsCommited) AS 'Committed',

                        (SUM(OITW.OnHand)+SUM(OITW.OnOrder)-SUM(OITW.IsCommited)) AS 'Available', OITM.AvgPrice AS 'Unit Cost',(SUM(OITW.OnHand)*OITM.AvgPrice) AS 'Value $',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND YEAR(OINM.DocDate)=(YEAR(getdate())-1) AND OINM.ItemCode = OITM.ItemCode GROUP BY

                        OINM.ItemCode) AS 'Prev. Year',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='1' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'JAN',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='2' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'FEB',  
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='3' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'MAR',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='4' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'APR',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='5' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'MAY',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='6' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'JUN',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='7' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'JUL',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='8' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'AUG',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='9' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'SEP',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='10' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'OCT',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='11' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'NOV',
                        (SELECT SUM(OINM.OutQty) FROM OINM WHERE OINM.TransType <> '67' AND MONTH(OINM.DocDate)='12' AND YEAR(OINM.DocDate)=YEAR(getdate()) AND OINM.ItemCode =

                        OITM.ItemCode GROUP BY OINM.ItemCode) AS 'DEC'
                        FROM OITM, OITW
                        WHERE OITM.ItemCode=OITW.ItemCode
                        GROUP BY OITM.ItemCode, OITM.ItemName, OITM.CardCode, OITM.AvgPrice ORDER BY OITM.ItemCode

                         

                         

                        • 9. RE: Inventory Usage / Inventory Turns Report
                          Mike Grant

                          Darren:

                          Thanks for sharing your Inventory Usage / Inventory Turns Report.  I tried putting pieces of your SQL code into B1's Query Generator, but got numerous errors. What SQL program do you run your program in?

                          Mike

                          • 10. RE: Inventory Usage / Inventory Turns Report
                            Mike Grant

                            Darren:  Anthony showed me the edit pencil in Query Generator and how to run your code just the way you listed it.  It worked great.  Thanks to both of you!

                            Mike

                            • 11. RE: Inventory Usage / Inventory Turns Report
                              Mike Grant

                              Dean:

                              Thanks for sharing your code for 90-day usage from sales invoices. I learn most about SQL and queries from examing the code examples from other B1 users, not from books or manuals.

                              After studying your code carefully, I believe I found an error.   In your explanation paragraph, you say it gives you &quot;Maximum ( 2 months avg usage).&quot;  Well I think your code for it, (sum (T0.Quantity)*2) AS 'MAX', will give you 2 X the sum of your INV1.Quantity for 90 days, or 180 days usage...not 2 months.

                              If I am correct, then this should yield what you want, 2 months' usage: (sum (T0.Quantity)/3*2) AS 'MAX'.  I think your code is missing the &quot;/3&quot;.  This would divide 90 days by 3 (1 mo. avg.) before multiplying by 2 (2 mo. avg.).  If I am wrong, then I must go back to school!  Please let me know.

                              Mike

                              • 12. RE: Inventory Usage / Inventory Turns Report
                                Dean Garrison

                                Mike,

                                You are right and my explanation was wrong, sorry about that!  We do use a 6mos max due to the nature of our purchasing habits.  Guess I should review what I type a little better!

                                Dean

                                • 13. Re: Inventory Usage / Inventory Turns Report
                                  Brad Windecker

                                   

                                  Thanks for providing the queries everyone.

                                   

                                   

                                  However, given the complexity of the queries above, room for error, and challenge of altering them if a change needs to be made, I would highly suggest looking into XL Reporter for this.  For those not as technically capable as the query writers above, XLR is a good avenue.

                                   

                                   

                                   

                                  You could easily show the activity of items for multiple documents (Invoices, Adjustments, etc.) in one report, allowing for a summary view and drill down into the actual document data.  If you added date filter to the report with prompts, you'd be able to run it for any given date range or preset the date range and have it automatically generated periodically.  It'd also be slick to have the columns set as Expanding Columns to allow for a summary view orexpansion into monthly view.

                                   

                                   

                                  Once XLR pulls the data in, standard Excel formulas could give you Average Months on Hand and Monthly Usage.

                                   

                                   

                                  I teach an XLR Course in Portland, OR if you're interested in learning more.

                                   

                                   

                                  Brad Windecker

                                  bwindecker@omegagroup.com