BizTech Blog

Oracle E-Business – Supply Chain – How to use Oracle BI Publisher based reporting to analyze and reduce your Inventory levels

Posted by Bob Bullman

Find me on:

Dec 4, 2012 3:40:00 PM

Having spent more than a decade focusing on improving Supply Chain, and a high percentage my energy was spent using quantitative information. KPI’s, Cubes, Dashboards, Briefing, Portals, , Data Marts and the list go on. The algorithms have not changes to much. Future projected turns was one recent change for me, but there has been a lot of renaming or repackaging of proven techniques. A colleague of mine decided to rename an old inventory analysis process or classification of Excess and Obsolete (E&O) to Slow Moving Obsolete Product (SLOP). It’s a bit more descriptive than E&O, and has a slight humorous tone.

The real point here is not so much the approach and numbers, but the tool that we used to collect the data. Being a Supply Chain geek, I think the numbers a pretty cool as well. So during a recent inventory reduction project here is what we did. Using BI Publisher and a bunch of queries that I have been using for years, we created a new report in a couple of hours that provided columns of data that we used to stratify and analyze the inventory.

SLOP (Slow Moving Obsolete Product) (XML Report)

Select all items with quantity on-hand and a bunch of the static elements established on each item (Cost, buyer, make/buy, lead time, etc…).

The following are the rest of columns:

  • Planned orders (MRP)
  • WIP (Work-in-Process) scheduled issues (project work order usage)
  • WIP schedule completions (project work order makes)
  • Actual scheduled Purchase order receipts
  • Actual sales order shipment
  • Item category
  • Item quantity and extended on-hand value
  • Make/buy category and much more…

Once in a spreadsheet (one of the big pluses of XML reports) the first pass was selecting all make items with no demand, sorted high to low item inventory value. One of these categories of items can be reworked and used in other activity assembles. Because of the higher than normal levels of inventory there are also space issues. With these numbers you can decide what to move to bulk less expensive or not as accessible inventory areas (in this case outside is an option). In order words, if your warehouse is full move SLOP out of the way. The second pass was buy items with high value and no demand. In a later iteration we added forecasts. Many additional sorts and selects were made. In the end you have a solid understand of your SLOP.

The tool used is the Oracle BI Publishers (prior name was XML Publisher) which has two components. A Query or Data Definition (DD), and a Template is all that you need to create. The Template is an RTF MS Word file that maps the query fields to a Word table. In a future BLOG, I will provide a more detailed review of how to actually create the report including sample code.

Topics: Oracle Business Intelligence