Fast Refresh Materialized View GL_CODE_COMBINATIONS with DescriptionThis 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. |
About Me
- Name: Anil Passi
- Location: United Kingdom
The desire to be extraordinary is a very ordinary desire. To relax and to be ordinary is really extraordinary
Previous Posts
- ORA-12054 for Materialized View on GL_CODE_COMBINA...
- Scripts for Oracle Workflow Training Lesson 3The w...
- Scripts for Workflow Training Lesson 2 This page c...
- Oracle Apps FND User name Locks RecordEver wondere...
- Oracle APPS Warn Bounce of Environment to Recent U...
- Oracle Activity on FND LOGINS FND USERMy client as...
- Oracle API for GL Interface Package BodyCREATE OR ...
- Oracle API for GL Interface Package HeaderCREATE O...
Comments on ""