Noetix: Removing Item Information Caching @ Release 12

We use Oracle E-Business Suite to manage our (pharmaceutical) production facility. Because of the nature of the work we do we create items, batches, and lots every single day of the week. Normally this isn't a problem but in Noetix views item information is cached in the N_KFF_SYS_Item table and is only refreshable by using a supplied concurrent processes.

Clearly as we're creating multiple items every day it's just not practical for us to add a "Now run the concurrent process" step across our business to make sure that the reporting information is up to date for those running reports. It also creates an issue of "trust" - if users are looking at cached data is it up to date or not?

Looking at the source code for the Noetix views which access the cached information rather than going directly to the cached table they instead go via a view; XXK_Sys_Item which just does a direct select from the table  (I'm making a point here of not just copy/pasting blocks of Noetix source code so you'll find a lot of this is instructions on how to find the code rather than the code itself). The benefit of this approach is that if we want to stop using caching we just replace the view with a new view that goes directly to the table. You may be thinking that it might actually be easier to replace the references to the view in the Noetix configuration tables but unfortunately this isn't actually possible. If you look in the table N_View_Table_Templates (or just N_View_Tables) for the view_label INV_Items you will notice that the only NOETIX table in use is N_MFG_LOOKUPS_VL - no mention of the XXK_Sys_Item view. This must be added "elsewhere".

Doing a search of the source code (.sql) files in the Noetix install directory for the table name reveals that the regenerate process builds a package called N_KFF_SYS_Item_Pkg to handle the management of the cached table.

So in summary here is the structure (with INVG0_Items being the public view we're going to use for testing);

Looking in more detail at the N_Kff_SYS_Item_Pkg package we can see that the routine called Init_Upld (which is the basis for the concurrent request) does the initial upload of rows into the table - it's the SQL to do this that we need to build into a view to replace the XXK_Sys_Item view.

In the source code (in the N_KFF_Sys_Item_pkg.sql file) I'm looking at line 145 which does the insert (it reads "INSERT  /*+ APPEND */ INTO N_KFF_Sys_Item" if you want to search for it in your Source code. The format for the insert is a simple SELECT ... which makes life a lot easier when you're looking at creating a view!

Looking at the columns inserted not all of them are making it through to the view (last_update_date for instance) so this column isn't needed in our replacement view. Clearly some clever logic has gone into building this view which would pick up multiple segments and structures but in our system it seems a lot of this is wasted as the data (probably explaining why we feel caching is unnecessary) seems to default to a single value. Looking at this code seems to indicate that the SQL we need to build the replacement view is;

CREATE OR REPLACE VIEW XXK_SYS_ITEM AS
SELECT
       msib.SEGMENT1 CV$System_Item_KFF,
       msib.INVENTORY_ITEM_ID,
       msib.ORGANIZATION_ID,
       msib.SEGMENT1 SV$ITEM,
       'Item' SEGMENT_NAME_LIST,
       101 STRUCTURE_ID,
       'System Items' STRUCTURE_NAME,
       null Z$XXK_SYS_ITEM
  FROM INV.MTL_SYSTEM_ITEMS_B msib;

Clearly this is quite a substantial change so what we need to do is provide us with a way to back it out quickly after it's gone live in the event of substantial problems being found (low level-testing has been done but this will never cover 350+ reports) so the script to make the update will rename the existing view rather than just overwriting it so we can do a quick rollback*. Here is the script;

@utlspon xu6_replace_xxk_sys_item_view

BEGIN
  for v_Data in (SELECT 'x'
                   FROM ALL_VIEWS av
                  WHERE av.owner = USER
                    and av.view_name = 'XXK_SYS_ITEM_BK') loop
    execute immediate 'drop view XXK_SYS_ITEM_BK'; -- Remove an existing backup view if it exists
  end loop;
END;
/

RENAME XXK_SYS_ITEM TO XXK_SYS_ITEM_BK
/

CREATE OR REPLACE VIEW XXK_SYS_ITEM AS
SELECT
       msib.SEGMENT1 CV$System_Item_KFF,
       msib.INVENTORY_ITEM_ID,
       msib.ORGANIZATION_ID,
       msib.SEGMENT1 SV$ITEM,
       'Item' SEGMENT_NAME_LIST,
       101 STRUCTURE_ID,
       'System Items' STRUCTURE_NAME,
       null Z$XXK_SYS_ITEM
  FROM INV.MTL_SYSTEM_ITEMS_B msib
/


begin
  dbms_output.put_line('Recompiling invalid Views under NOETIX_SYS Schema');
  dbms_output.put_line('-------------------------------------------------');
  dbms_output.put_line('Remove Invalid Noetix Views (that are not set to be ommitted)');
  for v_SQL in (select 'ALTER VIEW NOETIX_SYS.' || do.object_name || ' COMPILE' text
                  from dba_objects do
                 where do.status = 'INVALID'
                   and do.OWNER = 'NOETIX_SYS'
                   and do.OBJECT_TYPE = 'VIEW'
                   and do.OBJECT_NAME not in
                       (select view_name
                          from n_views
                         where nvl(omit_flag, 'N') = 'N')) loop
    dbms_output.put_line('  ' || v_SQL.Text);
    execute immediate v_SQL.Text;
  end loop;
  dbms_output.put_line('END');
end;
/
 
COMMIT;

@utlspoff

This is available as a file here (via Google Docs).

You then need to add a call in XU6 to call the new file.

*- Just drop the new view and reverse the "RENAME ..."

UPDATE (11-JAN-2012): I've added in a section to the script to do a COMPILE of the views that are invalidated by this change. Normally it wouldn't be required as the first query will automatically recompile the view - however we have a check in XU6 for invalid views so they need to be valid by that stage otherwise our regenerate fails.