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.

Noetix: INV_Period_Close_Details and Process Manufacturing (at Release 12)

If you are one of Oracles (few) Process Manufacturing module customers like us you will already have noticed that there are substantual changes to Noetix when you go from Release 11 to Release 12. One of the most significant is the change from using the GMI_Month_End_Inventory view (which has been rendered obsolete by Oracles changes) to using the INV_Period_Close_Details template.

The first thing you'll notice when running against R12 is that if you do;

SELECT *

  FROM INV_Period_Close_Details;

In your test system you will get ZERO rows (unless you are also an Oracle Discrete Manufacturing customer in which case you will just get your rows from Discrete manufacturing).

The reason for this is the inclusion of the table BOM.CST_PERIOD_CLOSE_SUMMARY as the source for the costing data. This table is not used by Process Manufacturing (the correct table in GMF.GMF_PERIOD_BALANCES).

You have to wonder exactly what Process Manufacturing testing Noetix has done as this was *clearly* never going to work.

Luckily (for everyone else) we have been through the support loop with Noetix and they have produced a modified version of the INV_Period_Close_Details views which not only includes Process Manufacturing Costs but also includes Lot details - something that will be necessary if you are migrating from the Release 11 view to the Release 12 view.

The support reference number is B27089.

What the file does (for those of you trying to test/understand it) is to;

1. Insert a new query (n_view_query_templates) based on the existing query, 
2. Copies across all the tables from the existing query into the new query (including the CST_Period_Close_Summary table), 
3. Add in the MTL tables (MTL_material_Statuses_tl, MTL_Grades_TL, MTL_Item_Locations, and MTL_Lot_Numbers
4. Update the CPCS table (CST_Period_Close_Summary) replacing it with GMF.GMF_PERIOD_BALANCES in the new query,
5. Copy across all the columns from the existing query that are associated with any table included in the new query,
6. Update the Accounted_On_Hand and Period_Close_Quantity columns to use the correct values,
7. Insert new columns based on the new tables (i.e. Lot_Grade_Code, Lot_Number, etc),
8. Insert the requried n_view_col_property_templates records for flexfields,
9. Copy across the existing WHERE-clause records,
10. Insert new where clause components to join up new tables, and finally
11. Update the version for the first query to be pre-Release 12