1 PACKAGE BODY FII_MV_REFRESH AS
2 /*$Header: FIIMVRSB.pls 120.31 2006/06/15 17:44:07 juding ship $*/
3
4 g_phase VARCHAR2(50);
5 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
6 g_retcode VARCHAR2(20) := NULL;
7 g_apps_schema_name VARCHAR2(120) := NVL(FII_UTIL.get_apps_schema_name, 'APPS');
8
9 ----------------------------------------------------
10 -- PROCEDURE GL_REFRESH
11 ----------------------------------------------------
12 PROCEDURE GL_REFRESH (Errbuf IN OUT NOCOPY Varchar2,
13 Retcode IN OUT NOCOPY Varchar2,
14 p_program_type IN VARCHAR2) IS
15
16 l_dir VARCHAR2(150) := NULL;
17 l_parallel_degree NUMBER := 0;
18 index_exception EXCEPTION;
19 l_ret_val BOOLEAN := FALSE;
20
21 BEGIN
22
23 Errbuf := NULL;
24 Retcode := '0';
25
26 ------------------------------------------------------
27 -- Set default directory in case if the profile option
28 -- BIS_DEBUG_LOG_DIRECTORY is not set up
29 ------------------------------------------------------
30 l_dir := FII_UTIL.get_utl_file_dir;
31
32 ----------------------------------------------------------------
33 -- fii_util.initialize will get profile options FII_DEBUG_MODE
34 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
35 -- the log files and output files are written to
36 ---------------------------------------------------------------
37 FII_UTIL.initialize('FII_GL_MV_REFRESH.log', 'FII_GL_MV_REFRESH.out',l_dir, 'FII_MV_REFRESH');
38
39 l_parallel_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
40 IF (l_parallel_degree =1) THEN
41 l_parallel_degree := 0;
42 END IF;
43
44 --------------------------------------------------------
45 -- Refreshing the MVs
46 ----------------------------------------------------------
47
48 ---------------------------------*****---
49 --bug 3242732: refresh FII_GL_BASE_MV, this is a temporary fix
50 IF (p_program_type = 'L')
51 THEN
52 -- bug 4115002 - changed from dbms_mview.refresh to bis_mv_refresh
53 BIS_MV_REFRESH.refresh_wrapper ( 'FII_GL_BASE_MV', 'C',l_parallel_degree );
54
55 --(only) Analyze the MV LOG after full refresh
56 fnd_stats.gather_table_stats (ownname=>g_apps_schema_name,
57 tabname=>'MLOG$_FII_GL_BASE_MV');
58 ELSE
59 BIS_MV_REFRESH.refresh_wrapper ( 'FII_GL_BASE_MV', '?',l_parallel_degree );
60 END IF;
61
62 ---------------------------------*****---
63
64 -- Drop index for FII_GL_MGMT_CCC_MV in Initial Mode
65 IF (p_program_type = 'L') THEN
66 fii_index_util.drop_index('FII_GL_MGMT_CCC_MV', g_apps_schema_name, Retcode);
67 IF Retcode <> '0' THEN
68 RAISE index_exception;
69 END IF;
70 END IF;
71
72 -- Refresh FII_GL_MGMT_CCC_MV
73 IF g_debug_flag = 'Y' THEN
74 FII_UTIL.start_timer();
75 END IF;
76
77 ------------------------------------------------------------------------------
78 --Bug 3155474: call BIS wrapper to handle force parallel on MVs
79 -----
80 ----- dbms_mview.refresh( list => 'FII_GL_MGMT_CCC_MV',
81 ----- method => '?',
82 ----- parallelism => l_parallel_degree );
83
84 IF (p_program_type = 'L') THEN
85 BIS_MV_REFRESH.refresh_wrapper ('FII_GL_MGMT_CCC_MV', 'C', l_parallel_degree);
86 ELSE
87 BIS_MV_REFRESH.refresh_wrapper ('FII_GL_MGMT_CCC_MV', '?', l_parallel_degree);
88 END IF;
89 ------------------------------------------------------------------------------
90
91 IF g_debug_flag = 'Y' THEN
92 FII_UTIL.stop_timer();
93 FII_UTIL.put_line( 'FII_GL_MGMT_CCC_MV has been refreshed successfully' );
94 FII_UTIL.print_timer();
95 END IF;
96
97 -- Re-create index for FII_GL_MGMT_CCC_MV in Initial Mode
98 IF (p_program_type = 'L') THEN
99 fii_index_util.create_index('FII_GL_MGMT_CCC_MV', g_apps_schema_name, Retcode );
100 IF Retcode <> '0' THEN
101 RAISE index_exception;
102 END IF;
103 END IF;
104
105 -- Gather statistics for FII_GL_MGMT_CCC_MV
106 g_phase := 'Calling FND_STATS API to gather table statstics';
107 IF g_debug_flag = 'Y' THEN
108 FII_UTIL.put_line(g_phase ||' for FII_GL_MGMT_CCC_MV' );
109 FII_UTIL.put_line('');
110 END IF;
111
112 fnd_stats.gather_table_stats (ownname=>g_apps_schema_name,
113 tabname=>'FII_GL_MGMT_CCC_MV');
114
115 --(only) Analyze the MV LOG after full refresh
116 IF (p_program_type = 'L') THEN
117 fnd_stats.gather_table_stats (ownname=>g_apps_schema_name,
118 tabname=>'MLOG$_FII_GL_MGMT_CCC_MV');
119 END IF;
120
121 -- Drop index for FII_GL_MGMT_SUM_MV in Initial Mode
122 IF (p_program_type = 'L') THEN
123 fii_index_util.drop_index('FII_GL_MGMT_SUM_MV', g_apps_schema_name, Retcode);
124 IF Retcode <> '0' THEN
125 RAISE index_exception;
126 END IF;
127 END IF;
128
129 -- Refresh FII_GL_MGMT_SUM_MV
130 IF g_debug_flag = 'Y' THEN
131 FII_UTIL.start_timer();
132 END IF;
133
134 ------------------------------------------------------------------------------
135 --Bug 3155474: call BIS wrapper to handle force parallel on MVs
136 --
137 ----- dbms_mview.refresh( list => 'FII_GL_MGMT_SUM_MV',
138 ----- method => '?',
139 ----- parallelism => l_parallel_degree );
140
141 IF (p_program_type = 'L') THEN
142 BIS_MV_REFRESH.refresh_wrapper ('FII_GL_MGMT_SUM_MV', 'C', l_parallel_degree);
143 ELSE
144 BIS_MV_REFRESH.refresh_wrapper ('FII_GL_MGMT_SUM_MV', '?', l_parallel_degree);
145 END IF;
146 ------------------------------------------------------------------------------
147
148 IF g_debug_flag = 'Y' THEN
149 FII_UTIL.stop_timer();
150 FII_UTIL.put_line( 'FII_GL_MGMT_SUM_MV has been refreshed successfully' );
151 FII_UTIL.print_timer();
152 END IF;
153
154 -- Re-create index for FII_GL_MGMT_SUM_MV in Initial Mode
155 IF (p_program_type = 'L') THEN
156 fii_index_util.create_index('FII_GL_MGMT_SUM_MV', g_apps_schema_name, Retcode);
157 IF Retcode <> '0' THEN
158 RAISE index_exception;
159 END IF;
160 END IF;
161
162 EXCEPTION
163
164 WHEN index_exception THEN
165 Errbuf:= sqlerrm;
166 Retcode:=sqlcode;
167 IF g_debug_flag = 'Y' THEN
168 FII_UTIL.put_line('Index Exception in index drop/create');
169 FII_UTIL.put_line('-->'||Retcode||':'||Errbuf);
170 END IF;
171 l_ret_val := FND_CONCURRENT.Set_Completion_Status
172 (status => 'ERROR', message => substr(errbuf,1,180));
173 rollback;
174
175 WHEN OTHERS THEN
176 Errbuf:= sqlerrm;
177 Retcode:=sqlcode;
178 IF g_debug_flag = 'Y' THEN
179 FII_UTIL.put_line('Other error in GL_REFRESH');
180 FII_UTIL.put_line('-->'||Retcode||':'||Errbuf);
181 END IF;
182 l_ret_val := FND_CONCURRENT.Set_Completion_Status
183 (status => 'ERROR', message => substr(errbuf,1,180));
184 rollback;
185
186 END GL_REFRESH;
187
188
189 ---------------------------------------------------------------
190 -- PROCEDURE AR_REFRESH
191 ---------------------------------------------------------------
192 PROCEDURE AR_REFRESH(Errbuf in out NOCOPY Varchar2,
193 Retcode in out NOCOPY Varchar2) IS
194
195 l_dir VARCHAR2(150) := NULL;
196 l_min DATE;
197 l_max DATE;
198 l_check_time_dim BOOLEAN;
199 l_parallel_degree NUMBER := 0;
200 l_count NUMBER := 0;
201 l_ret_val BOOLEAN := FALSE;
202
203 BEGIN
204
205 ------------------------------------------------------
206 -- Set default directory in case if the profile option
207 -- BIS_DEBUG_LOG_DIRECTORY is not set up
208 ------------------------------------------------------
209 l_dir:= FII_UTIL.get_utl_file_dir;
210
211 ----------------------------------------------------------------
212 -- fii_util.initialize will get profile options FII_DEBUG_MODE
213 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
214 -- the log files and output files are written to
215 ----------------------------------------------------------------
216 FII_UTIL.initialize('FII_AR_MV_REFRESH.log', 'FII_AR_MV_REFRESH.out',l_dir, 'FII_MV_REFRESH');
217
218
219
220 g_phase := 'Refreshing AR Revenue MV';
221 if g_debug_flag = 'Y' then
222 FII_UTIL.put_line(g_phase);
223 FII_UTIL.put_line('');
224 end if;
225
226 -----------------------------------------------------------
227 -- If there are no records in the base summary table,
228 -- then give a message to run Load program and exit this program.
229 -- If there are records in base summary table, proceed
230 -- further and refresh the summary table.
231 -----------------------------------------------------------
232
233 g_phase := 'Checking base table for records';
234 if g_debug_flag = 'Y' then
235 FII_UTIL.put_line(g_phase);
236 FII_UTIL.put_line('');
237 end if;
238
239 begin
240 SELECT 1 INTO l_count FROM FII_AR_REVENUE_B
241 WHERE ROWNUM = 1;
242 exception
243 when NO_DATA_FOUND then
244 l_count := 0;
245 end;
246
247 IF l_count = 0 THEN -- no records in fii_ar_revenue_b
248
249 FII_MESSAGE.write_log(msg_name => 'FII_AR_REV_NO_RECS', token_num => 0);
250
251 retcode := 1;
252
253 RETURN;
254
255 ELSE -- there are records in fii_ar_revenue_b
256
257 -----------------------------------------------------------
258 -- If we find record in the base summary table which references
259 -- time records which does not exist in FII_TIME_DAY
260 -- table, then we will exit the program with warning
261 -- status
262 -----------------------------------------------------------
263 g_phase := 'Checking time dimension';
264 if g_debug_flag = 'Y' then
265 FII_UTIL.put_line(g_phase);
266 FII_UTIL.put_line('');
267 end if;
268
269 SELECT MIN(t.gl_date),Max(t.gl_date)
270 INTO l_min, l_max
271 FROM FII_AR_REVENUE_B t;
272
273 FII_TIME_API.check_missing_date(l_min, l_max, l_check_time_dim);
274
275 --------------------------------------
276 -- If there are missing time records
277 --------------------------------------
278 IF (l_check_time_dim) THEN
279 if g_debug_flag = 'Y' then
280 FII_UTIL.put_line('Time Dimension is not fully populated. Please populate Time
281 dimension to cover the date range you are refreshing');
282 end if;
283 retcode := 1;
284 RETURN;
285
286 END IF;
287
288 FII_UTIL.start_timer;
289 ------------------------------------------------
290 --Begin call HRI_OPL_PER_ORGCC.Load to populate
291 ------------------------------------------------
292 HRI_OPL_PER_ORGCC.Load (errbuf,
293 retcode,
294 NULL, --chunk_size
295 NULL, --start_date
296 NULL, --end_date
297 NULL); --full_refresh
298 FII_UTIL.stop_timer;
299 FII_UTIL.print_timer('Duration for populating table HRI_CS_PER_ORGCC_CT: ');
300
301 g_phase := 'Refreshing MV table';
302 if g_debug_flag = 'Y' then
303 FII_UTIL.put_line(g_phase);
304 FII_UTIL.put_line('');
305 end if;
306
307 l_parallel_degree := BIS_COMMON_PARAMETERS.GET_DEGREE_OF_PARALLELISM();
308 IF (l_parallel_degree =1) THEN
309 l_parallel_degree := 0;
310 END IF;
311
312 dbms_mview.refresh( list => 'FII_AR_REV_SUM_MV',
313 method => '?',
314 parallelism => l_parallel_degree );
315
316 --------------------------------------------------------
317 -- Gather statistics for the use of cost-based optimizer
318 --------------------------------------------------------
319 g_phase := 'Calling FND_STATS API to gather table statstics';
320 if g_debug_flag = 'Y' then
321 FII_UTIL.put_line(g_phase);
322 FII_UTIL.put_line('');
323 end if;
324
325 fnd_stats.gather_table_stats (ownname=>g_apps_schema_name, tabname=>'FII_AR_REV_SUM_MV');
326
327 END IF; -- l_count
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 Errbuf:= sqlerrm;
332 Retcode:=sqlcode;
333 if g_debug_flag = 'Y' then
334 FII_UTIL.put_line('Other error in AR_REFRESH');
335 FII_UTIL.put_line('-->'||Retcode||':'||Errbuf);
336 END IF;
337 l_ret_val := FND_CONCURRENT.Set_Completion_Status
338 (status => 'ERROR', message => substr(errbuf,1,180));
339 rollback;
340
341 END AR_REFRESH;
342
343 -----------------------------------------------------------------------------
344 -- PROCEDURE RSG_CALLOUT_API
345 -----------------------------------------------------------------------------
346 /* This API will be seeded for individual MVs and base summary tables and
347 will be called by BIS with different input table values during the initial
348 and incremental request sets. This API peforms the following functions:
349 1. Before initial load, drop MV log on base summary table.
350 2. After initial load, recreate MV log on base summary table.
351 3. Before initial refresh, drop indexes on MVs.
352 4. After initial refresh, recreate indexes on MVs. */
353
354 PROCEDURE RSG_CALLOUT_API(p_param IN OUT NOCOPY BIS_BIA_RSG_PARAMETER_TBL) IS
355 l_api_type VARCHAR2(300);
356 l_mode VARCHAR2(300);
357 l_obj_name VARCHAR2(300);
358 l_obj_type VARCHAR2(300);
359
360 l_retcode VARCHAR2(50) := '0';
361 l_index_exception EXCEPTION;
362 BEGIN
363
364 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Retrieving Parameters');
365
366 l_api_type := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param,
367 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_API_TYPE);
368 l_mode := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param,
369 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MODE);
370 l_obj_name := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param,
371 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_OBJECT_NAME);
372 l_obj_type := BIS_BIA_RSG_CUSTOM_API_MGMNT.GET_PARAM(p_param,
373 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_OBJECT_TYPE);
374
375
376 IF l_api_type = BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_INDEX_MGT THEN
377 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('API Type is MV Index Management');
378 IF l_mode = BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_BEFORE THEN
379 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Dropping indexes on object ' || l_obj_name);
380 FII_INDEX_UTIL.Drop_Index(l_obj_name, g_apps_schema_name, l_retcode);
381 IF l_retcode <> '0' THEN
382 RAISE l_index_exception;
383 END IF;
384 ELSE --AFTER Mode
385 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Recreating indexes on object ' || l_obj_name);
386 FII_INDEX_UTIL.Create_Index(l_obj_name, g_apps_schema_name, l_retcode);
387 IF l_retcode <> '0' THEN
388 RAISE l_index_exception;
389 END IF;
390 END IF;
391 ELSIF l_api_type = BIS_BIA_RSG_CUSTOM_API_MGMNT.TYPE_MV_LOG_MGT THEN
392 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('API Type is MV Log Management');
393 IF l_mode = BIS_BIA_RSG_CUSTOM_API_MGMNT.MODE_BEFORE THEN
394 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Dropping MV Log on object ' || l_obj_name);
395 BIS_BIA_RSG_LOG_MGMNT.base_sum_mlog_capture_and_drop(l_obj_name);
396 ELSE --AFTER Mode
397 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Recreating MV Log for object ' || l_obj_name);
398 BIS_BIA_RSG_LOG_MGMNT.base_sum_mlog_recreate(l_obj_name);
399 END IF;
400 ELSE
401 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('API Type is NOT MV Index Management or MV Log Managment');
402 END IF;
403
404 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param,
405 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_COMPLETE_STATUS,
406 BIS_BIA_RSG_CUSTOM_API_MGMNT.STATUS_SUCCESS);
407 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param,
408 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MESSAGE, 'Succeeded');
409
410 EXCEPTION
411 WHEN l_index_exception THEN
412 BIS_BIA_RSG_CUSTOM_API_MGMNT.LOG('Index Exception in FII_MV_REFRESH.RSG_CALLOUT_API');
413 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param,
414 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_COMPLETE_STATUS,
415 BIS_BIA_RSG_CUSTOM_API_MGMNT.STATUS_FAILURE);
416 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param,
417 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MESSAGE, sqlerrm);
418 WHEN OTHERS THEN
419 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param,
420 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_COMPLETE_STATUS,
421 BIS_BIA_RSG_CUSTOM_API_MGMNT.STATUS_FAILURE);
422 BIS_BIA_RSG_CUSTOM_API_MGMNT.SET_PARAM(p_param,
423 BIS_BIA_RSG_CUSTOM_API_MGMNT.PARA_MESSAGE, sqlerrm);
424 END RSG_CALLOUT_API;
425
426
427 END FII_MV_REFRESH;
[Home] [Help]