PACKAGE BODY: APPS.MSC_REFRESH_MV

Source

1 PACKAGE BODY MSC_REFRESH_MV AS
2 /* $Header: MSCRFMVB.pls 120.2 2011/09/22 10:13:38 vjuluri 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_apps_schema     VARCHAR2(30); --Bug#12964070
13    l_mv_exist      number;
14    l_complete_refresh_flag  varchar2(1) := 'C' ;
15    l_mv_name            varchar2(240) ;
16 
17    Cursor application_schema IS
18     SELECT a.oracle_username
19     FROM   FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
20     WHERE  a.oracle_id = b.oracle_id
21     AND    b.application_id= p_schema_id;
22 
23    Cursor mv_exist(p_mv_trim_name varchar2, p_schema_name varchar2) IS
24     SELECT 1
25     FROM   all_objects
26     WHERE  object_name = p_mv_trim_name
27     AND    owner = p_schema_name;
28 
29 BEGIN
30       msc_util.msc_log('Begin REFRESH_MAT_VIEWS');
31       RETCODE:= G_SUCCESS;
32 
33 	  select oracle_username
34       into l_apps_schema
35       from fnd_oracle_userid
36       where read_only_flag = 'U';
37 
38       select nvl(ltrim(rtrim(upper(p_mv_name))), '@@@') into l_mv_name from dual;
39 	  msc_util.msc_log('Snapshot is :'||l_mv_name);
40 
41 	  --Bug#12964070
42 	  IF l_mv_name = 'MSC_ATP_PLAN_SN' THEN
43 		l_schema_name := l_apps_schema;
44 
45       ELSE
46 		  OPEN application_schema;
47 		  FETCH application_schema INTO l_schema_name;
48 		  CLOSE application_schema;
49 	  END IF;
50 
51       OPEN mv_exist(l_mv_name, l_schema_name);
52       FETCH mv_exist INTO l_mv_exist;
53       CLOSE mv_exist;
54 
55       if (l_mv_exist = 1) then
56          -- msc_util.msc_log('Refreshing ' || l_mv_name);
57 	 fnd_message.set_name('MSC','MSC_REF_MV_EXIST_PRE');
58 	 fnd_message.set_token('VIEW_NAME',l_mv_name);
59 	 msc_util.msc_log(fnd_message.get);
60          DBMS_SNAPSHOT.REFRESH( l_schema_name||'.'||l_mv_name , l_complete_refresh_flag);
61       else
62          -- msc_util.msc_log(l_schema_name||'.'||l_mv_name ||' does not exist');
63          fnd_message.set_name('MSC','MSC_REF_MV_EXIST_ERR');
64          fnd_message.set_token('VIEW_NAME',l_mv_name);
65          fnd_message.set_token('SCHEMA_NAME',l_schema_name);
66          msc_util.msc_log(fnd_message.get);
67          RETCODE:= G_ERROR;
68       end if;
69 
70       msc_util.msc_log('End REFRESH_MAT_VIEWS');
71 
72    EXCEPTION
73 
74    WHEN OTHERS THEN
75         msc_util.msc_log(sqlerrm);
76         RETCODE:= G_ERROR;
77         ERRBUF:= SQLERRM;
78 
79    END REFRESH_MAT_VIEWS;
80 
81 
82 END MSC_REFRESH_MV;