Noetix: New view template INV_Material_Status_History

INVG0_Material_Status_History

Using Oracle's Process Manufacturing (OPM) module was part of Oracle e-Business Suite is never easy; there are so few users of process manufacturing - as opposed to it's much more commonly used bigger brother discrete manufacturing - that we often get left out.

(What's the difference between Process and Discrete manufacturing? Discrete manufacturing makes individual items (Cars, TV sets, iPads, etc) these are either complete or not complete while Process Manufacturing feeds ingredients into a Process until they are all used up (Bread, Soft Drinks, Pharmaceuticals, etc). You would be amazed at how many times I have had to explain this to various companies!)

The issue has arisen because as part of our month-end processes in Finance we look at the INV_Period_Close_Details view and get our total stock on-hand. The problem is that we need to see the Material Status at the point in time the period was closed as, obviously, items in Quarantine (or Rejected) status have a value of zero whereas items which are Approved have a saleable value.

(We have already customised done some heavy customisation in this area as, unbelievably, several key fields are missing which make *all* reports meaningless - for example the Inventory Type; in what case would you not wish to differentiate between Finished Goods sitting in the Warehouse and Raw Materials?!)

We had the option of adding the table we needed (INV.MTL_Material_Status_History) into the INV_Period_Close_Details template but the more we looked at it the more other uses we could see for the data (i.e. lot genealogy) and we couldn't see a way of marrying up all the different requirements without creating a new, custom, view.

Here is the list of columns we selected (I've removed the A$ and Z$ columns and truncated the item flexfield);

chart_of_accounts_name
item$... <item flexfields>
item$item
item_description
item_long_description
locator$inventory_location_id
locator$organization_id
locator$sv$stock_locator
locator$stock_locator
lot_number
master_organization
master_organization_name
material_status
material_status_code
organization_code
organization_id
organization_name
primary_unit_of_measure
status_end_date
status_start_date
subinventory

The script necessary to create the view is available in Google Docs here. Save this as an SQL file in your Noetix directory and call it from WNOETXU2.sql. It seems to me that this is something the NoetixViews Product should actually include as there is a clear, demonstrable, use for it - the first question any auditor will ask when you show them your month end stock levels is how much it's worth!

I have not had a great deal of luck getting the changes I've been pushing for into the product. If anyone has any suggestions I'd be happy to hear them!

NOTE: While this view has passed IT-testing it is currently with the business for end-user testing. If any issues come up I'll update this blog post with details as well as a fix.