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.