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);