Noetix: Disabling Material Category (XXK_MTL_CAT) caching

This is slightly more complicated than the other "removal of caching" blog posts I've done earlier as there are multiple views (one for each structure) which all need to be updated and replaced at the same time. I've adopted a fairly simple approach;

• Remove any backup (_BK) views
• Create a new view to replace the root XXK_MTL_CAT view
• Go through the ALL_VIEWS system view looking for the structure-specific views and then individually replace each of them with a view based on the new XXK_MTL_CAT view created above
• Rebuild all the objects that have been invalidated by this change

Here is the script (you'll notice the * instead of the important piece of SQL, more details on that later);

@utlspon xu6_2012_replace_xxk_mtl_cat_view

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

RENAME XXK_MTL_CAT TO XXK_MTL_CAT_BK
/

CREATE OR REPLACE VIEW XXK_MTL_CAT AS

*

/

begin
  for v_view in (select av.*
                   from all_views av
                  where av.owner = USER
                    and av.view_name like 'XXK_MTL_CAT_%') loop
    if instr(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT') > 0 then
        execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || '_BK AS ' || v_view.text;
        execute immediate 'CREATE OR REPLACE VIEW ' || v_view.view_name || ' AS ' || replace(v_view.text, 'NOETIX_SYS.N_KFF_MTL_CAT', 'NOETIX_SYS.XXK_MTL_CAT');
        end if;
  end loop;
end;
/

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

*- Now Noetix owns the code for this bit and as it's going to be distinct to every individual system (as it includes references to ID's) you'll need to do a bit of detective work and work it out yourself. I'll tell you how below.


First of all do a search for the object N_KFF_Mtl_Cat in all the .SQL files in your install directory. You shouldn't find many, the one you're after is named something like N_KFF_Mtl_Cat_pkg.sql, open the file and then scroll down to the line;

INSERT  /*+ APPEND */ INTO N_KFF_Mtl_Cat

You'll see that that the basis for this insert statement is a select statement. You need to take this select statement, take the columns in the table and re-format the SQL to return all the columns as specified in table.

When you've done this you'll notice that the column in the source table STRUCTURE_ID is mapping to both the STRUCTURE_ID and STRUCTURE_NAME in the view. If you scroll down quite a bit further you'll notice that there is also an UPDATE statement;

UPDATE N_KFF_Mtl_Cat dtbl
   SET dtbl.STRUCTURE_NAME =

You need to extract this UPDATE statement and put it into the view you're building (as the STRUCTURE_NAME) column.

In the end you'll have a piece of SQL that you can drop into the script above. If everything works when you run the script there will be no (well, the same number as you started with!) invalid views.