Noetix: Checking Incremental Refresh

The following script will generate a piece of SQL that will check the current status (and any pending records) for the Noetix Key Flex Field (KFF) Incremental Refresh process;

begin
  for v_Kff in (SELECT N.*,
                       (select max(data_table_key) from n_f_kff_flex_sources) as max_data_table_key
                  FROM n_f_kff_flex_sources N
                 ORDER BY N.data_table_key) loop
    dbms_output.put_line('SELECT ''' || v_kff.kff_view_description ||
                         ''' kff_view_description, ');
    dbms_output.put_line('       ''' || v_kff.target_value_object_name ||
                         ''' kff_cache_table_name, ');
    dbms_output.put_line('       ''' || v_kff.data_application_table_name ||
                         ''' oracle_cache_source_table, ');
    dbms_output.put_line('       (SELECT ut.status ');
    dbms_output.put_line('          FROM user_triggers ut ');
    dbms_output.put_line('         WHERE ut.trigger_name = ''' ||
                         UPPER(v_kff.cd_trigger_name) ||
                         ''') trigger_status, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM ' ||
                         v_kff.target_value_object_name ||
                         ') current_kff_cached_rowcount, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM APPS.' ||
                         v_kff.data_application_table_name ||
                         ') current_oracle_source_rowcount, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM ' ||
                         v_kff.cd_table_name || ') pending_cd_rowcount, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM ' ||
                         v_kff.cde_table_name ||
                         ') pending_cde_rowcount, ');
    dbms_output.put_line('       (SELECT COUNT(*) FROM ' ||
                         v_kff.cdeh_table_name ||
                         ') pending_cdeh_rowcount ');
    dbms_output.put_line('FROM DUAL ');
    if (v_Kff.Max_Data_Table_Key != v_Kff.Data_Table_Key) then
      dbms_output.put_line('UNION');
    end if;
  end loop;
end;

On our system this generates the following SQL;

SELECT 'Accounting Key Flexfield' kff_view_description,
       'N_KFF_GL_Acct' kff_cache_table_name,
       'GL_CODE_COMBINATIONS' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_GL_ACCT') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_GL_Acct) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.GL_CODE_COMBINATIONS) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_GL_Acct) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_GL_Acct) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_GL_Acct) pending_cdeh_rowcount
FROM DUAL
UNION
SELECT 'Item Categories Key Flexfield' kff_view_description,
       'N_KFF_Mtl_Cat' kff_cache_table_name,
       'MTL_CATEGORIES_B' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_MTL_CAT') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_Mtl_Cat) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.MTL_CATEGORIES_B) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_Mtl_Cat) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_Mtl_Cat) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_Mtl_Cat) pending_cdeh_rowcount
FROM DUAL
UNION
SELECT 'Item Catalogs Key Flexfield' kff_view_description,
       'N_KFF_Ctlg_Grp' kff_cache_table_name,
       'MTL_ITEM_CATALOG_GROUPS_B' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_CTLG_GRP') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_Ctlg_Grp) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.MTL_ITEM_CATALOG_GROUPS_B) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_Ctlg_Grp) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_Ctlg_Grp) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_Ctlg_Grp) pending_cdeh_rowcount
FROM DUAL
UNION
SELECT 'System Items Key Flexfield' kff_view_description,
       'N_KFF_Sys_Item' kff_cache_table_name,
       'MTL_SYSTEM_ITEMS_B' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_SYS_ITEM') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_Sys_Item) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.MTL_SYSTEM_ITEMS_B) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_Sys_Item) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_Sys_Item) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_Sys_Item) pending_cdeh_rowcount
FROM DUAL
UNION
SELECT 'Stock Locators Key Flexfield' kff_view_description,
       'N_KFF_Item_Loc' kff_cache_table_name,
       'MTL_ITEM_LOCATIONS' oracle_cache_source_table,
       (SELECT ut.status
          FROM user_triggers ut
         WHERE ut.trigger_name = 'N_CDT_ITEM_LOC') trigger_status,
       (SELECT COUNT(*) FROM N_KFF_Item_Loc) current_kff_cached_rowcount,
       (SELECT COUNT(*) FROM APPS.MTL_ITEM_LOCATIONS) current_oracle_source_rowcount,
       (SELECT COUNT(*) FROM N_CD_Item_Loc) pending_cd_rowcount,
       (SELECT COUNT(*) FROM N_CDE_Item_Loc) pending_cde_rowcount,
       (SELECT COUNT(*) FROM N_CDEH_Item_Loc) pending_cdeh_rowcount
FROM DUAL

This generates output similar to the following (on our production system);

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.

Noetix: Fixing Compilation Errors in the KFF Packages

This blog post provides step-by-step instructions on how to rebuild the KFF packages that are automatically generated by Noetix which populate the cached flex field tables.


The source code to build the packages that keep the KFF tables populated are generated by the packages N_GSEG_PKG and the routine VALUE_TBL_POPULATE.

This routine takes a single parameter the, the data table key from the table N_F_KFF_FLEX_SOURCES which matches to the package you wish to re-create. To get a list from the table use the SQL;

SELECT nfkfs.data_table_key,
       nfkfs.id_flex_code,
       nfkfs.target_value_object_name,
       nfkfs.kff_view_description
  FROM n_f_kff_flex_sources nfkfs;

This will give you results similar to;

When you call the routine you need to record the session ID (as the script to create the new view is written into a temporary table indexed by the session ID). PL/SQL Developer makes this very easy by allowing both input and output variables from code blocks;

begin
  -- Call the procedure
  :id := USERENV ('sessionid');
  n_gseg_pkg.value_tbl_populate(i_data_table_key => :i_data_table_key);
end;

In this case I'm storing the sessionid in :id and using the value in :i_data_table_key to call the routine. If you're using some other tool then you'll need to modify the code above if it doesn't support output parameters in code blocks.

Once you've called the routine the source code will have been inserted into the table N_BUFFER, you can get it at using the SQL;

SELECT nb.TEXT
  FROM n_buffer nb
 WHERE nb.session_id = 7751119
 ORDER BY nb.line_number

This then needs to be extracted and written into the correct file in your Noetix Installation folder. The correct file will be the value of TARGET_VALUE_OBJECT_NAME with "_pkg.sql" appended to the end of it. For example to rebuild the Accounting Key Flexfield KFF package (N_KFF_GL_Acct_pkg, data table key = 1 above) the code will be in the file N_KFF_GL_Acct_pkg.sql.