Fast Refresh Materialized View GL_CODE_COMBINATIONS with Description This was my third attempt to get the Fast Refresh Materialized views to work on GL_CODE_COMBINATIONS by tricking Oracle 10g. The descriptions are being fetched by using a function. This function is blanked out to return null just prior to the creation of snapshot. Surely, the function being used to fetch the description ain't complicated. The comments are below with code. So Finally with this, you can make your GL Queries run very quickly even if you desire to list the Description of GL Codes. conn apps/&1 ; PROMPT materialized view log on GL.GL_CODE_COMBINATIONS create materialized view log on GL.GL_CODE_COMBINATIONS including new values ; conn gl/&4; GRANT ALL ON GL.GL_CODE_COMBINATIONS TO xxschema WITH GRANT OPTION; GRANT ALL ON GL.MLOG$_GL_CODE_COMBINATIONS TO xxschema ; conn apps/&1 ; --First create the proper function such that we can Pre-Build Table PROMPT FUNCTION xx_valueset_code_description CREATE OR REPLACE FUNCTION xx_valueset_code_description( p_code IN VARCHAR2 ,p_vs_id IN INTEGER) RETURN VARCHAR2 IS CURSOR c_get IS SELECT ffv.description FROM fnd_flex_values_vl ffv WHERE flex_value_set_id = p_vs_id AND flex_value = p_code; p_get c_get%ROWTYPE; BEGIN OPEN c_get; FETCH c_get INTO p_get; CLOSE c_get; RETURN p_get.description; END xx_valueset_code_description; / GRANT ALL ON xx_valueset_code_description TO xxschema ; GRANT EXECUTE ON xx_valueset_code_description TO xxschema ; --Create the Pre-Build table PROMPT table xxschema.IC_GL_CODE_COMBINATIONS_MV create table xxschema.IC_GL_CODE_COMBINATIONS_MV AS SELECT cc.segment2 costcentre ,apps.xx_valueset_code_description(cc.segment2, 1002651) AS costcentre_desc ,cc.segment3 project ,apps.xx_valueset_code_description(cc.segment3,1002652) project_desc ,cc.segment4 expensetype ,apps.xx_valueset_code_description(cc.segment4,1002653) AS expensetype_desc ,cc.code_combination_id FROM gl.gl_code_combinations cc /*WHERE LAST_UPDATE_DATE > SYSDATE - 100*/; --Here is the trick of fooling Oracle. We make the function to be used in MV look simple PROMPT FUNCTION xx_valueset_code_description CREATE OR REPLACE FUNCTION xx_valueset_code_description( p_code IN VARCHAR2 ,p_vs_id IN INTEGER) RETURN VARCHAR2 IS BEGIN RETURN NULL ; END xx_valueset_code_description; / create or replace synonym IC_GL_CODE_COMBINATIONS_MV for xxschema.IC_GL_CODE_COMBINATIONS_MV ; --Now create the snapshot of MV, poor Oracle thinks its going to use a simple Function PROMPT materialized view xxschema.IC_GL_CODE_COMBINATIONS_MV create materialized view xxschema.IC_GL_CODE_COMBINATIONS_MV on prebuilt table refresh fast on commit as SELECT cc.segment2 costcentre --replace 10001/2/3 by your respective value set id's for GL Segments ,apps.xx_valueset_code_description(cc.segment2, 10001) AS costcentre_desc ,cc.segment3 project ,apps.xx_valueset_code_description(cc.segment3,10002) project_desc ,cc.segment4 expensetype ,apps.xx_valueset_code_description(cc.segment4,10003) AS expensetype_desc ,cc.code_combination_id FROM gl.gl_code_combinations cc ; --Now put the proper function back to where it was PROMPT FUNCTION xx_valueset_code_description CREATE OR REPLACE FUNCTION xx_valueset_code_description( p_code IN VARCHAR2 ,p_vs_id IN INTEGER) RETURN VARCHAR2 IS CURSOR c_get IS SELECT ffv.description FROM fnd_flex_values_vl ffv WHERE flex_value_set_id = p_vs_id AND flex_value = p_code; p_get c_get%ROWTYPE; BEGIN OPEN c_get; FETCH c_get INTO p_get; CLOSE c_get; RETURN p_get.description; END xx_valueset_code_description; / --Ensure that the MV is compiled ALTER materialized view xxschema.IC_GL_CODE_COMBINATIONS_MV COMPILE ; Make sure that you create all the required indexes on the Materialized view, depending upon the structure of your GL Accounting Flexfield.

ORA-12054 for Materialized View on GL_CODE_COMBINATIONS The below script was my second attempt to create a fast refresh Materialized View in Oracle. The last step in the scipt failed because Oracle is unable to create a Fast Materialized view referencing a package function because it deems this to be complicated. Anyway, in the third attempt, we will do a trickery to fool Oracle... conn apps/&1 ; create materialized view log on GL.GL_CODE_COMBINATIONS with rowid ( CODE_COMBINATION_ID ,CHART_OF_ACCOUNTS_ID ,DETAIL_POSTING_ALLOWED_FLAG ,DETAIL_BUDGETING_ALLOWED_FLAG ,ACCOUNT_TYPE ,ENABLED_FLAG ,SUMMARY_FLAG ,SEGMENT1 ,SEGMENT2 ,SEGMENT3 ,SEGMENT4 ,TEMPLATE_ID ,START_DATE_ACTIVE ,END_DATE_ACTIVE ) including new values ; conn gl/&4; GRANT SELECT ON GL.GL_CODE_COMBINATIONS TO xxschema WITH GRANT OPTION; GRANT ALL ON GL.MLOG$_GL_CODE_COMBINATIONS TO xxschema ; conn apps/&1 ; CREATE OR REPLACE PACKAGE xx_mv_util AS FUNCTION get_vs_code_description(p_code IN VARCHAR2 ,p_vs_id IN INTEGER) RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES(get_vs_code_description ,WNDS ,WNPS ,RNPS); END xx_mv_util; / GRANT ALL ON xx_mv_util TO xxschema ; CREATE OR REPLACE PACKAGE BODY xx_mv_util AS FUNCTION get_vs_code_description(p_code IN VARCHAR2 ,p_vs_id IN INTEGER) RETURN VARCHAR2 IS CURSOR c_get IS SELECT ffv.description FROM fnd_flex_values_vl ffv WHERE flex_value_set_id = p_vs_id AND flex_value = p_code; p_get c_get%ROWTYPE; BEGIN OPEN c_get; FETCH c_get INTO p_get; CLOSE c_get; RETURN p_get.description; END get_vs_code_description; END xx_mv_util; / create table xxschema.IC_GL_CODE_COMBINATIONS_MV AS SELECT cc.segment2 costcentre ,xx_mv_util.get_vs_code_description(cc.segment2, 1002651) AS costcentre_desc ,cc.segment3 activity ,xx_mv_util.get_vs_code_description(cc.segment3,1002652) activity_desc ,cc.segment4 analysis ,cc.segment4 new_analysis ,xx_mv_util.get_vs_code_description(cc.segment4,1002653) AS analysis_desc ,cc.code_combination_id FROM gl.gl_code_combinations cc ; create materialized view IC_GL_CODE_COMBINATIONS_MV on prebuilt table refresh fast on commit as SELECT cc.segment2 costcentre ,xx_mv_util.get_vs_code_description(cc.segment2, 1002651) AS costcentre_desc ,cc.segment3 activity ,xx_mv_util.get_vs_code_description(cc.segment3,1002652) activity_desc ,cc.segment4 analysis ,cc.segment4 new_analysis ,xx_mv_util.get_vs_code_description(cc.segment4,1002653) AS analysis_desc ,cc.code_combination_id FROM gl.gl_code_combinations cc ; This errors with ERROR at line 13: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view