PACKAGE BODY: APPS.MSC_REFRESH_MV

Source

1 PACKAGE BODY MSC_REFRESH_MV AS
2 /* $Header: MSCRFMVB.pls 120.1 2005/06/19 23:03:45 appldev ship $ */
3 
4 PROCEDURE REFRESH_MAT_VIEWS(
5                       ERRBUF             OUT NOCOPY VARCHAR2, /* file.sql.39 change 4405879 */
6                       RETCODE            OUT NOCOPY NUMBER,  /* file.sql.39 change 4405879 */
7                       p_mv_name          IN  VARCHAR2,
8                       p_schema_id        IN  NUMBER DEFAULT 724)
9    IS
10 
11    l_schema_name     VARCHAR2(30);
12    l_mv_exist      number;
13    l_complete_refresh_flag  varchar2(1) := 'C' ;
14    l_mv_name            varchar2(240) ;
15 
16    Cursor application_schema IS
17     SELECT a.oracle_username
18     FROM   FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
19     WHERE  a.oracle_id = b.oracle_id
20     AND    b.application_id= p_schema_id;
21 
22    Cursor mv_exist(p_mv_trim_name varchar2, p_schema_name varchar2) IS
23     SELECT 1
24     FROM   all_objects
25     WHERE  object_name = p_mv_trim_name
26     AND    owner = p_schema_name;
27 
28 BEGIN
29       msc_util.msc_log('Begin REFRESH_MAT_VIEWS');
30       RETCODE:= G_SUCCESS;
31 
32       select nvl(ltrim(rtrim(upper(p_mv_name))), '@@@') into l_mv_name from dual;
33 
34       OPEN application_schema;
35       FETCH application_schema INTO l_schema_name;
36       CLOSE application_schema;
37 
38       OPEN mv_exist(l_mv_name, l_schema_name);
39       FETCH mv_exist INTO l_mv_exist;
40       CLOSE mv_exist;
41 
42       if (l_mv_exist = 1) then
43          -- msc_util.msc_log('Refreshing ' || l_mv_name);
44 	 fnd_message.set_name('MSC','MSC_REF_MV_EXIST_PRE');
45 	 fnd_message.set_token('VIEW_NAME',l_mv_name);
46 	 msc_util.msc_log(fnd_message.get);
47          DBMS_SNAPSHOT.REFRESH( l_schema_name||'.'||l_mv_name , l_complete_refresh_flag);
48       else
49          -- msc_util.msc_log(l_schema_name||'.'||l_mv_name ||' does not exist');
50          fnd_message.set_name('MSC','MSC_REF_MV_EXIST_ERR');
51          fnd_message.set_token('VIEW_NAME',l_mv_name);
52          fnd_message.set_token('SCHEMA_NAME',l_schema_name);
53          msc_util.msc_log(fnd_message.get);
54          RETCODE:= G_ERROR;
55       end if;
56 
57       msc_util.msc_log('End REFRESH_MAT_VIEWS');
58 
59    EXCEPTION
60 
61    WHEN OTHERS THEN
62         msc_util.msc_log(sqlerrm);
63         RETCODE:= G_ERROR;
64         ERRBUF:= SQLERRM;
65 
66    END REFRESH_MAT_VIEWS;
67 
68 
69 END MSC_REFRESH_MV;