Noetix: Manually Rebuilding Cached KFF Data

Sometimes it's necessary to rebuild the cached KFF data that Noetix uses to speed up it's queries. These are typically the tables under the NOETIX_SYS schema that start with KFF_ followed by a code representing the data to be refreshed (i.e. GL_ACCT for General Ledger Account Code Combinations).

This refresh can take quite a time and it's useful (if you are in a highly controlled environment) to be able to hand over to people some statistics regarding the rebuild. Hence the script below.

The script looks at the packages that have been built under your Noetix schema and then into the tables the packages update to determine the current number of rows then it performs the initial upload (a full refresh) and then re-checks the number of records in the table - hopefully this will have changed!

Here is the script;

declare
  -- Local variables here
  v_Item varchar2(255);
begin
  -- Test statements here
  for v_Data in (SELECT ao.object_name,
                        SUBSTR(ao.object_name,
                               7,
                               LENGTH(ao.object_name) - 10) item_name
                   FROM all_objects ao
                  WHERE ao.owner = 'NOETIX_SYS'
                    AND ao.object_name LIKE 'N_KFF_%_PKG'
                    AND ao.object_type = 'PACKAGE') loop
    dbms_output.put_line(RPAD('===== ', 60, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('Processing ' || v_Data.item_name);
    dbms_output.put_line('  - Package name      : N_KFF_' ||
                         v_Data.item_name || '_PKG');
    dbms_output.put_line('  - Data Table name   : N_KFF_' ||
                         v_Data.item_name);
    EXECUTE IMMEDIATE 'SELECT TO_CHAR(COUNT(*)) FROM N_KFF_' ||
                      v_Data.item_name
      INTO v_Item;
    dbms_output.put_line('  - Cache Table Count : ' || v_Item || ' (BEFORE)');
    dbms_output.put_line(RPAD('== Initial Upload == ', 20, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    EXECUTE IMMEDIATE 'BEGIN N_KFF_' || v_Data.item_name ||
                      '_PKG.Init_Upld(); END;';
    dbms_output.put_line(RPAD('== Initial Upload == ', 20, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('  - Cache Table Count : ' || v_Item || ' (AFTER)');
    dbms_output.put_line(RPAD('===== ', 60, '= ') ||
                         TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  end loop;
end;

And here is a sample of the output generated on our system;


===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing ACCALIAS
  - Package name      : N_KFF_ACCALIAS_PKG
  - Data Table name   : N_KFF_ACCALIAS
  - Cache Table Count : 1 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_AccAlias with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 9
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:46:31
  - Cache Table Count : 1 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing CTLG_GRP
  - Package name      : N_KFF_CTLG_GRP_PKG
  - Data Table name   : N_KFF_CTLG_GRP
  - Cache Table Count : 0 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Ctlg_Grp with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 23
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:46:31
  - Cache Table Count : 0 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:46:31
Processing GL_ACCT
  - Package name      : N_KFF_GL_ACCT_PKG
  - Data Table name   : N_KFF_GL_ACCT
  - Cache Table Count : 639234 (BEFORE)
== Initial Upload ==16-DEC-2011 15:46:31
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_GL_Acct with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 655
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:37
  - Cache Table Count : 639234 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:37
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:37
Processing ITEM_LOC
  - Package name      : N_KFF_ITEM_LOC_PKG
  - Data Table name   : N_KFF_ITEM_LOC
  - Cache Table Count : 30845 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:37
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Item_Loc with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 37
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:38
  - Cache Table Count : 30845 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:38
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:38
Processing MTL_CAT
  - Package name      : N_KFF_MTL_CAT_PKG
  - Data Table name   : N_KFF_MTL_CAT
  - Cache Table Count : 1740 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:38
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Mtl_Cat with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 46
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:47:40
  - Cache Table Count : 1740 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:40
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:47:40
Processing SYS_ITEM
  - Package name      : N_KFF_SYS_ITEM_PKG
  - Data Table name   : N_KFF_SYS_ITEM
  - Cache Table Count : 690403 (BEFORE)
== Initial Upload ==16-DEC-2011 15:47:40
Truncating Table N_KFF_GL_Acct
Initial Upload Started ...
Populating Table N_KFF_Sys_Item with data
Initial Upload insert complete ...
Initial Upload completed ...
Time Taken for gather stats for data cache table in secs : 189
Initial Upload completed ...
 
== Initial Upload ==16-DEC-2011 15:48:35
  - Cache Table Count : 690403 (AFTER)
===== = = = = = = = = = = = = = = = = = = = = = = = = = = = 16-DEC-2011 15:48:35

Noetix: Manually Enabling Incremental Refresh in PL/SQL

Turning on Incremental Refreshing of Key Flex Field (KFF) data in Noetix.

This blog post covers turning on the Incremental Refresh "manually". Noetix provide a Concurrent Request that you can run what will work in almost all circumstances - unfortunately our site was one where it wouldn't run due to a independent value set that should have had some values in being empty.

NOTE: You *must* have initialised the APPS environment (calling APPS.FND_GLOBAL.APPS_INITIALIZE) in order for this to work. See Initialising the APPS Environment for my blog post giving instructions on how to do this in PL/SQL.

Executing the following SQL with turn on the incremental refresh;

declare
  v_error_buf   varchar2(2048);
  v_Return_code number;
begin
  noetix_sys.n_gseg_utility_pkg.enbl_incr(o_error_buf   => v_error_buf,
                                          o_return_code => v_return_code,
                                          i_flex_code   => 'ALL');
end;

This code needs to be run as the APPS user (after ensuring that this user is GRANTed permission to EXECUTE the n_gseq_utility_pkg package).

To check incremental refresh has been successfully enabled (there is absolutely no output from this call - very poor!) you need to query the triggers on the APPS tables that populate the KFF data and check to see if the triggers are enabled or not.

You can also log into Oracle e-Business Suite as the XXNAO user and check to see if the concurrent requests have run.

If the triggers have been enabled then you're in business - otherwise you'll need to, in the first instance, check the logs of the concurrent requests it schedules and if this doesn't work you'll then need to open the package in an editor and add DBMS_OUTPUT lines to echo the output to the screen so you can properly see what is going on.