[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_CONC_MV
Source
1 PACKAGE BODY hri_oltp_conc_mv AS
2 /* $Header: hriocmvr.pkb 120.1.12010000.2 2009/03/17 12:10:58 sudsahu ship $ */
3
4 --
5 g_conc_request_id NUMBER := fnd_global.conc_request_id;
6 g_schema VARCHAR2(50);
7 --
8 --Private Function
9 FUNCTION get_apps_schema_name RETURN VARCHAR2;
10
11 /*
12 **
13 */
14 PROCEDURE refresh_mv_sql
15 (p_mv_name IN VARCHAR2
16 ,p_mv_refresh_mode IN VARCHAR2)
17 IS
18
19 BEGIN
20
21 IF (p_mv_name IS NOT NULL
22 AND p_mv_refresh_mode IN ('C') ) THEN
23 dbms_mview.refresh(p_mv_name, p_mv_refresh_mode);
24 END IF;
25
26
27 -- bug 4775190, disable query rewrite so view compiles on 8i database
28 IF p_mv_name = 'HRI_MDP_SUP_WRKFC_JX_MV' THEN
29
30 EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW HRI_MDP_SUP_WRKFC_JX_MV DISABLE QUERY REWRITE';
31
32 END IF;
33
34 EXCEPTION WHEN OTHERS THEN
35 RAISE;
36
37 END refresh_mv_sql;
38
39 -- -----------------------------------------------------------------------------
40 -- Inserts row into concurrent program log
41 -- -----------------------------------------------------------------------------
42 --
43 PROCEDURE output(p_text VARCHAR2) IS
44 --
45 BEGIN
46 --
47 g_conc_request_id := fnd_global.conc_request_id;
48 --
49 IF g_conc_request_id IS NOT NULL THEN
50 --
51 -- Write to the concurrent request log
52 --
53 fnd_file.put_line(fnd_file.log, p_text);
54 --
55 ELSE
56 --
57 hr_utility.trace(p_text);
58 --
59 END IF;
60 --
61 END output;
62
63
64
65 /*
66 ** Procedure wrapper for concurrent process to call
67 ** dbms_mview.refresh_mv() procedure
68 */
69 PROCEDURE refresh_mv
70 (errbuf OUT NOCOPY VARCHAR2
71 ,retcode OUT NOCOPY VARCHAR2
72 ,p_mv_name IN VARCHAR2
73 ,p_mv_refresh_mode IN VARCHAR2)
74 IS
75
76 -- Variables required for table truncation.
77 --
78 l_dummy1 VARCHAR2(2000);
79 l_dummy2 VARCHAR2(2000);
80
81 BEGIN
82
83 output('Materialized View: ' || p_mv_name);
84 output('Refresh Mode : ' || p_mv_refresh_mode);
85
86 -- Find the schema we are running in.
87 --
88 --IF NOT fnd_installation.get_app_info('APPS',l_dummy1, l_dummy2, g_schema) THEN
89 -- the above statement was commented as part of the fix for the bug#6040715 as the function
90 -- was not initializing g_schema
91 --
92 -- Could not find the schema raising exception.
93 --
94 -- output('Could not find schema to run in.');
95 --
96 --RAISE NO_DATA_FOUND;
97 --
98 --END IF;
99
100 g_schema := get_apps_schema_name;
101 IF g_schema IS NULL then
102 RAISE NO_DATA_FOUND;
103 END IF;
104
105 IF (p_mv_name IS NOT NULL
106 AND p_mv_refresh_mode IN ('C') ) THEN
107
108 output('Refreshing the Materialized View: ' || p_mv_name);
109 output('Start time: ' || to_char(sysdate, 'DD-MON-YYYY HH.MI.SS'));
110 refresh_mv_sql(p_mv_name, p_mv_refresh_mode);
111 output('Gathering table statstics, start: '|| to_char(sysdate, 'DD-MON-YYYY HH.MI.SS'));
112 fnd_stats.gather_table_stats(g_schema, p_mv_name);
113 output('Gathering table statstics, end: '|| to_char(sysdate, 'DD-MON-YYYY HH.MI.SS'));
114
115 output('End time: ' || to_char(sysdate, 'DD-MON-YYYY HH.MI.SS'));
116
117 ELSE
118 output('Error: Invalid paramters');
119 errbuf := 'ERROR';
120 retcode := '2';
121
122 END IF;
123
124 EXCEPTION WHEN OTHERS THEN
125 output('EXCEPTION: ' || substr(SQLERRM, 80) );
126 errbuf := 'ERROR';
127 retcode := '2';
128
129 END refresh_mv;
130
131 --
132 --Following Function is used to fetch oracle schema name used to gather stats for MV's
133 --Added to address bug#6040715
134 --
135 FUNCTION get_apps_schema_name RETURN VARCHAR2 IS
136
137 l_apps_schema_name VARCHAR2(30);
138
139 CURSOR c_apps_schema_name IS
140 SELECT oracle_username
141 FROM fnd_oracle_userid WHERE oracle_id
142 BETWEEN 900 AND 999 AND read_only_flag = 'U';
143 BEGIN
144
145 OPEN c_apps_schema_name;
146 FETCH c_apps_schema_name INTO l_apps_schema_name;
147 CLOSE c_apps_schema_name;
148 RETURN l_apps_schema_name;
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 output('Could not find schema to run in.');
153 Return Null;
154 END get_apps_schema_name;
155
156 END HRI_OLTP_CONC_MV;