[Home] [Help]
PACKAGE BODY: APPS.FEM_MV_REFRESH_PVT
Source
1 PACKAGE BODY FEM_MV_Refresh_Pvt AS
2 /* $Header: FEMVMVREFRESHB.pls 120.3 2008/02/20 06:50:58 jcliving noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'FEM_MV_Refresh_Pvt' ;
5
6 -- Global variables.
7 g_debug_msg VARCHAR2(2000) := NULL ;
8 g_user_id NUMBER := 0 ;
9 g_sys_date DATE := SYSDATE ;
10
11 /*===========================================================================+
12 | PROCEDURE pd |
13 +===========================================================================*/
14 -- API to print debug information used during development.
15 PROCEDURE pd( p_message IN VARCHAR2) IS
16 BEGIN
17 NULL ;
18 --DBMS_OUTPUT.Put_Line(p_message) ;
19 END pd ;
20 /*---------------------------------------------------------------------------*/
21
22
23 /*===========================================================================+
24 | PROCEDURE Register_MV |
25 +===========================================================================*/
26 -- API to register an MV.
27 PROCEDURE Register_MV
28 (
29 p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
31 p_commit IN VARCHAR2 := FND_API.G_FALSE,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 --
36 p_mv_name IN VARCHAR2,
37 p_base_table_name IN VARCHAR2,
38 p_refresh_group_sequence IN NUMBER := NULL
39 )
40 IS
41
42 l_api_name CONSTANT VARCHAR2(30) := 'Register_MV';
43 l_api_version CONSTANT NUMBER := 1.0;
44 l_return_status VARCHAR2(1);
45 l_msg_count NUMBER;
46 l_msg_data VARCHAR2(2000);
47 --
48 l_object_found_flag VARCHAR2(30);
49 --
50 CURSOR l_object_exists_csr
51 ( c_object_name VARCHAR2,
52 c_object_type VARCHAR2)
53 IS
54 SELECT 'Y'
55 FROM user_objects
56 WHERE object_name = c_object_name
57 AND object_type = c_object_type ;
58
59 BEGIN
60
61 SAVEPOINT Register_MV_Pvt ;
62 IF NOT FND_API.Compatible_API_Call ( l_api_version,
63 p_api_version,
64 l_api_name,
65 G_PKG_NAME )
66 THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
68 END IF ;
69 --
70 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
71 FND_MSG_PUB.initialize ;
72 END IF ;
73 x_return_status := FND_API.G_RET_STS_SUCCESS ;
74
75 --
76 -- Validate input parameters. Note we do not validate if the base table
77 -- is indeed being used by the MV or not.
78 --
79 l_object_found_flag := 'N' ;
80 OPEN l_object_exists_csr ( p_mv_name , 'MATERIALIZED VIEW' ) ;
81 FETCH l_object_exists_csr INTO l_object_found_flag ;
82 CLOSE l_object_exists_csr ;
83
84 IF l_object_found_flag = 'N' THEN
85 FND_MESSAGE.Set_Name('FND', 'FEM_MV_INVALID_MV_NAME') ;
86 FND_MSG_PUB.Add;
87 RAISE FND_API.G_EXC_ERROR;
88 END IF ;
89
90 l_object_found_flag := 'N' ;
91 OPEN l_object_exists_csr ( p_base_table_name , 'SYNONYM' ) ;
92 FETCH l_object_exists_csr INTO l_object_found_flag ;
93 CLOSE l_object_exists_csr ;
94
95 IF l_object_found_flag = 'N' THEN
96 FND_MESSAGE.Set_Name('FND', 'FEM_MV_INVALID_BASE_TABLE_NAME') ;
97 FND_MSG_PUB.Add;
98 RAISE FND_API.G_EXC_ERROR;
99 END IF ;
100 --
101 -- End validating input parameters.
102 --
103
104 -- We will take an UPSERT approach to update MV repository table.
105 MERGE INTO fem_mv_refresh_objects s
106 USING ( SELECT p_mv_name mv_name ,
107 p_base_table_name base_table_name ,
108 p_refresh_group_sequence refresh_group_sequence
109 FROM dual ) t
110 ON ( s.mv_name = t.mv_name )
111 WHEN MATCHED THEN
112 UPDATE SET s.refresh_group_sequence =
113 NVL(t.refresh_group_sequence, s.refresh_group_sequence),
114 s.base_table_name = t.base_table_name ,
115 s.last_updated_by = g_user_id ,
116 s.last_update_date = g_sys_date ,
117 s.last_update_login = g_user_id
118 WHEN NOT MATCHED THEN
119 INSERT ( s.mv_name ,
120 s.refresh_group_sequence,
121 s.base_table_name ,
122 s.created_by ,
123 s.creation_date ,
124 s.last_updated_by ,
125 s.last_update_date ,
126 s.last_update_login
127 )
128 VALUES
129 ( t.mv_name ,
130 NVL(t.refresh_group_sequence, 10),
131 t.base_table_name ,
132 g_user_id ,
133 g_sys_date ,
134 g_user_id ,
135 g_sys_date ,
136 g_user_id
137 ) ;
138
139 IF FND_API.To_Boolean ( p_commit ) THEN
140 COMMIT WORK ;
141 END IF ;
142 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
143 p_data => x_msg_data ) ;
144 EXCEPTION
145
146 WHEN OTHERS THEN
147 --
148 ROLLBACK TO Register_MV_Pvt ;
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
150 --
151 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
152 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
153 l_api_name) ;
154 END IF ;
155 --
156 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
157 p_data => x_msg_data ) ;
158 END Register_MV ;
159 /*---------------------------------------------------------------------------*/
160
161
162 /*===========================================================================+
163 | PROCEDURE Unregister_MV |
164 +===========================================================================*/
165 -- API to unregister an MV.
166 PROCEDURE Unregister_MV
167 (
168 p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
170 p_commit IN VARCHAR2 := FND_API.G_FALSE,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 --
175 p_mv_name IN VARCHAR2
176 )
177 IS
178
179 l_api_name CONSTANT VARCHAR2(30) := 'Unregister_MV';
180 l_api_version CONSTANT NUMBER := 1.0;
181 l_return_status VARCHAR2(1);
182 l_msg_count NUMBER;
183 l_msg_data VARCHAR2(2000);
184
185 BEGIN
186
187 SAVEPOINT Unregister_MV_Pvt ;
188 IF NOT FND_API.Compatible_API_Call ( l_api_version,
189 p_api_version,
190 l_api_name,
191 G_PKG_NAME )
192 THEN
193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
194 END IF ;
195 --
196 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
197 FND_MSG_PUB.initialize ;
198 END IF ;
199 x_return_status := FND_API.G_RET_STS_SUCCESS ;
200
201 -- Remove the MV from the repository table.
202 DELETE fem_mv_refresh_objects
203 WHERE mv_name = p_mv_name ;
204
205 IF SQL%ROWCOUNT <= 0 THEN
206 FND_MESSAGE.Set_Name('FND', 'FEM_MV_INVALID_MV_NAME') ;
207 FND_MSG_PUB.Add;
208 RAISE FND_API.G_EXC_ERROR;
209 END IF ;
210
211 IF FND_API.To_Boolean ( p_commit ) THEN
212 COMMIT WORK ;
213 END IF ;
214 --
215 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
216 p_data => x_msg_data ) ;
217 EXCEPTION
218
219 WHEN OTHERS THEN
220 --
221 ROLLBACK TO Unregister_MV_Pvt ;
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
223 --
224 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
225 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
226 l_api_name) ;
227 END IF ;
228 --
229 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
230 p_data => x_msg_data ) ;
231 END Unregister_MV;
232 /*---------------------------------------------------------------------------*/
233
234
235 /*===========================================================================+
236 | PROCEDURE Refresh_MV_CP |
237 +===========================================================================*/
238 -- This is the execution file for the concurrent program 'Refresh MV'.
239 PROCEDURE Refresh_MV_CP
240 (
241 errbuf OUT NOCOPY VARCHAR2 ,
242 retcode OUT NOCOPY VARCHAR2 ,
243 --
244 p_base_table_name IN VARCHAR2
245 )
246 IS
247
248 l_api_name CONSTANT VARCHAR2(30) := 'Refresh_MV_CP';
249 l_return_status VARCHAR2(1) ;
250 l_msg_count NUMBER ;
251 l_msg_data VARCHAR2(2000) ;
252
253 BEGIN
254
255 retcode := 2 ;
256 FEM_MV_Refresh_Pvt.Refresh_MV
257 (
258 p_api_version => 1.0,
259 p_init_msg_list => FND_API.G_TRUE,
260 p_commit => FND_API.G_FALSE,
261 x_return_status => l_return_status,
262 x_msg_count => l_msg_count,
263 x_msg_data => l_msg_data,
264 --
265 p_base_table_name => p_base_table_name
266 ) ;
267 --
268 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
269 RAISE FND_API.G_EXC_ERROR;
270 END IF ;
271
272 retcode := 0 ;
273 COMMIT WORK ;
274
275 EXCEPTION
276
277 WHEN OTHERS THEN
278 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
279 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
280 l_api_name ) ;
281 END IF ;
282
283 -- Print the error message stack.
284 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
285 p_data => l_msg_data ) ;
286 FOR i IN 1..l_msg_count
287 LOOP
288 FND_MSG_PUB.Get( p_msg_index => i ,
289 p_encoded => FND_API.G_FALSE ,
290 p_data => l_msg_data ,
291 p_msg_index_out => l_msg_count
292 );
293 FEM_ENGINES_PKG.User_Message ( p_msg_text => l_msg_data ) ;
294 END LOOP;
295
296 END Refresh_MV_CP ;
297 /*---------------------------------------------------------------------------*/
298
299
300 /*===========================================================================+
301 | PROCEDURE Refresh_MV |
302 +===========================================================================*/
303 -- API to refresh MVs.
304 PROCEDURE Refresh_MV
305 (
306 p_api_version IN NUMBER,
307 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
308 p_commit IN VARCHAR2 := FND_API.G_FALSE,
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_msg_data OUT NOCOPY VARCHAR2,
312 --
313 p_base_table_name IN VARCHAR2
314 )
315 IS
316
317 l_api_name CONSTANT VARCHAR2(30) := 'Refresh_MV';
318 l_api_version CONSTANT NUMBER := 1.0;
319 l_return_status VARCHAR2(1);
320 l_msg_count NUMBER;
321 l_msg_data VARCHAR2(2000);
322 --
323 l_mv_list VARCHAR2(2000);
324 l_last_refresh_group_sequence NUMBER;
325
326 BEGIN
327
328 -- No savepoints applicable as refresh seems to perform an implicit commit.
329 IF NOT FND_API.Compatible_API_Call ( l_api_version,
330 p_api_version,
331 l_api_name,
332 G_PKG_NAME )
333 THEN
334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
335 END IF ;
336 --
337 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
338 FND_MSG_PUB.initialize ;
339 END IF ;
340 x_return_status := FND_API.G_RET_STS_SUCCESS ;
341
342 FND_FILE.Put_Line( FND_FILE.LOG, 'The following is the parameter list:') ;
343 FND_FILE.Put_Line( FND_FILE.LOG, 'p_base_table_name: ' || p_base_table_name );
344
345 -- Initialize the local variables.
346 l_last_refresh_group_sequence := NULL ;
347 l_mv_list := NULL ;
348
349 --
350 -- Get all MVs that meet given base table name. We will collect them into a
351 -- comma separated list by refresh group sequence for refresh submission.
352 --
353 FOR l_mv_rec IN
354 (
355 SELECT mv_name, refresh_group_sequence
356 FROM fem_mv_refresh_objects
357 WHERE base_table_name = NVL(p_base_table_name, base_table_name)
358 ORDER BY refresh_group_sequence
359 )
360 LOOP
361
362 IF l_last_refresh_group_sequence IS NULL THEN
363
364 l_mv_list := l_mv_rec.mv_name ;
365
366 ELSIF l_mv_rec.refresh_group_sequence = l_last_refresh_group_sequence THEN
367
368 l_mv_list := l_mv_list || ',' || l_mv_rec.mv_name ;
369
370 ELSE
371
372 --pd( 'Procesing: ' || l_mv_list ) ;
373 DBMS_MVIEW.REFRESH
374 ( list => l_mv_list,
375 method => 'C',
376 rollback_seg => '',
377 push_deferred_rpc => TRUE,
378 refresh_after_errors => TRUE,
379 purge_option => 0,
380 parallelism => 0,
381 heap_size => 0,
382 atomic_refresh => FALSE
383 ) ;
384 l_mv_list := l_mv_rec.mv_name ;
385
386 END IF ;
387
388 l_last_refresh_group_sequence := l_mv_rec.refresh_group_sequence ;
389
390 END LOOP ;
391 -- End processing MVs.
392
393 -- Refresh the very last group of MVs left out due to loop exiting.
394 IF l_mv_list IS NOT NULL THEN
395
396 --pd( 'Processing last group: ' || l_mv_list ) ;
397 DBMS_MVIEW.REFRESH
398 ( list => l_mv_list,
399 method => 'C',
400 rollback_seg => '',
401 push_deferred_rpc => TRUE,
402 refresh_after_errors => TRUE,
403 purge_option => 0,
404 parallelism => 0,
405 heap_size => 0,
406 atomic_refresh => FALSE
407 ) ;
408
409 END IF ;
410
411 IF FND_API.To_Boolean ( p_commit ) THEN
412 COMMIT WORK ;
413 END IF ;
414 --
415 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
416 p_data => x_msg_data ) ;
417 EXCEPTION
418
419 WHEN OTHERS THEN
420 --
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
422 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
423 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
424 l_api_name) ;
425 END IF ;
426 --
427 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
428 p_data => x_msg_data ) ;
429 END Refresh_MV;
430 /*---------------------------------------------------------------------------*/
431
432
433 END FEM_MV_Refresh_Pvt ;