[Home] [Help]
PACKAGE BODY: APPS.BIL_BI_PURGE_OBJ_PKG
Source
1 PACKAGE BODY BIL_BI_PURGE_OBJ_PKG AS
2 /*$Header: bilbprgb.pls 120.3 2005/10/10 04:42:47 vchahal noship $*/
3
4
5 -- Declaring Global variables
6
7 g_retcode VARCHAR2(20);
8 g_debug BOOLEAN;
9 g_errbuf VARCHAR2(1000);
10 g_row_num NUMBER;
11 g_status BOOLEAN;
12 g_end_date DATE;
13 g_end_date_timeid NUMBER;
14 g_pkg VARCHAR2(100);
15 g_setup_valid_error EXCEPTION;
16
17 -- ---------------------------------------------------------------
18 -- Private procedures and Functions Prototypes;
19 -- ---------------------------------------------------------------
20
21 PROCEDURE opdtl_f_purge;
22
23 PROCEDURE fst_dtl_f_purge;
24
25 PROCEDURE pipeline_f_purge;
26
27 PROCEDURE init(p_obj_name IN VARCHAR2);
28
29 PROCEDURE clean_up;
30
31 -- **********************************************************************
32 -- PROCEDURE Trunc_Obj
33 --
34 -- Purpose:
35 -- To Truncate the data from the BIL database object
36 -- This main procedure is called from the Concurrent Program
37 -- 'Delete Complete Data from Sales Intelligence Object'
38 --
39 -- **********************************************************************
40
41 PROCEDURE trunc_obj
42 (
43 errbuf IN OUT NOCOPY VARCHAR2,
44 retcode IN OUT NOCOPY VARCHAR2,
45 p_obj_name IN VARCHAR2
46 ) IS
47 l_proc VARCHAR2(100);
48
49 BEGIN
50 g_pkg := 'bil.patch.115.sql.BIL_BI_PURGE_OBJ_PKG.';
51 l_proc := 'TRUNC_OBJ.';
52
53 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
54 bil_bi_util_collection_pkg.writeLog
55 (
56 p_log_level => fnd_log.LEVEL_PROCEDURE,
57 p_module => g_pkg || l_proc || 'begin',
58 p_msg => 'Start of Procedure '|| l_proc
59 );
60 END IF;
61 errbuf := NULL;
62 retcode := 0;
63
64 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
65 bil_bi_util_collection_pkg.writeLog
66 (p_log_level => fnd_log.LEVEL_STATEMENT,
67 p_module => g_pkg || l_proc || 'End',
68 p_msg => 'p_obj_name =>'||p_obj_name);
69 END IF;
70
71 CASE p_obj_name
72 WHEN 'OPDTL_F' THEN
73 -- Truncate Opportunity Detail Summary Objects
74 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_OPDTL_F');
75 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_OPDTL_STG');
76 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_CURRENCY_RATE');
77
78 -- Delete references from BIS_REFRESH_LOG table
79 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_' || p_obj_name );
80
81 WHEN 'FST_DTL_F' THEN
82
83 -- Truncate Forecast Summary Objects
84 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_FST_DTL_F');
85 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_FST_DTL_STG');
86 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_NEW_FST_ID');
87 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_PROCESSED_FST_ID');
88 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_CURRENCY_RATE');
89
90 -- Delete references from BIS_REFRESH_LOG table
91 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_' || p_obj_name );
92
93 WHEN 'CURRENCY' THEN
94 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_CURRENCY_RATE');
95
96 WHEN 'PIPELINE_F' THEN
97 -- Truncate Pipeline Summary Objects
98 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_PIPELINE_F');
99 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_PIPELINE_STG');
100 -- asolaiy added for 8.0.
101 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_PIPEC_F');
102 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_OPDTL_DENLOG_TMP');
103 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_DENLOG_STG');
104
105 -- Delete references from BIS_REFRESH_LOG table
106 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_PIPELINE_F');
107 -- asolaiy added for 8.0. New current fact table.
108 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_PIPEC_F');
109
110 WHEN 'ALL' THEN
111
112 -- Truncate Opportunity Detail Summary Objects
113 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_OPDTL_F');
114 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_OPDTL_STG');
115 -- Delete references from BIS_REFRESH_LOG table
116 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_' || 'OPDTL_F');
117
118 -- Truncate Forecast Summary Objects
119 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_FST_DTL_F');
120 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_FST_DTL_STG');
121 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_NEW_FST_ID');
122 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_PROCESSED_FST_ID');
123
124 -- Delete references from BIS_REFRESH_LOG table
125 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_' || 'FST_DTL_F');
126
127 -- Truncate Pipeline Summary Objects
128 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_PIPELINE_F');
129 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_PIPELINE_STG');
130 -- asolaiy added for 8.0.
131 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_PIPEC_F');
132 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_OPDTL_DENLOG_TMP');
133 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_DENLOG_STG');
134
135 -- Delete references from BIS_REFRESH_LOG table
136 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_PIPELINE_F');
137 -- asolaiy added for 8.0. New current fact table.
138 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_PIPEC_F');
139
140 -- Truncate Currency Table
141 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_CURRENCY_RATE');
142
143
144 ELSE
145 NULL;
146 END CASE;
147 COMMIT;
148
149 retcode := 0;
150 errbuf := 'Truncated ' || p_obj_name ||
151 ' Object(s) successfully' ;
152
153 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
154 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
155 p_module => g_pkg || l_proc || 'End',
156 p_msg => 'End of Procedure '||l_proc
157 );
158 END IF;
159 EXCEPTION
160 WHEN OTHERS THEN
161 g_retcode := -2;
162 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
163 fnd_message.set_token('Error is : ' ,SQLCODE);
164 fnd_message.set_token('Reason is : ', SQLERRM);
165 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
166 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
167 p_module => g_pkg || l_proc || 'proc_error',
168 p_msg => fnd_message.get,
169 p_force_log => TRUE
170 );
171 END IF;
172 g_errbuf := sqlerrm;
173 retcode := g_retcode;
174 errbuf := g_errbuf ;
175 END trunc_obj;
176
177
178 -- **********************************************************************
179 -- PROCEDURE Purge_Obj
180 --
181 -- Purpose:
182 -- To Purge the data from the BIL database object
183 -- This main procedure is called from the Concurrent Program
184 -- 'Delete Partial Data from Sales Intelligence Object'
185 -- **********************************************************************
186
187 PROCEDURE purge_obj
188 (
189 errbuf IN OUT NOCOPY VARCHAR2,
190 retcode IN OUT NOCOPY VARCHAR2,
191 p_obj_name IN VARCHAR2,
192 p_end_date IN VARCHAR2
193 ) IS
194
195 l_stmt VARCHAR2(400);
196 l_proc VARCHAR2(100) ;
197
198 BEGIN
199 g_pkg := 'bil.patch.115.sql.BIL_BI_PURGE_OBJ_PKG.';
200 l_proc := 'PURGE_OBJ.';
201 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
202 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
203 p_module => g_pkg || l_proc || 'begin',
204 p_msg => 'Start of Procedure '|| l_proc
205 );
206 END IF;
207
208 errbuf := NULL;
209 retcode := 0;
210
211 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
212 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
213 p_module => g_pkg || l_proc ,
214 p_msg => 'p_obj_name =>'||p_obj_name||' p_end_date => '||p_end_date);
215 END IF;
216
217 g_end_date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
218
219 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
220 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
221 p_module => g_pkg || l_proc ,
222 p_msg => ' g_end_date => '||g_end_date);
223 END IF;
224
225 g_end_date_timeid := TO_NUMBER(TO_CHAR(TRUNC(TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS')),'J'));
226
227 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
228 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
229 p_module => g_pkg || l_proc ,
230 p_msg => ' g_end_date_timid => '||g_end_date_timeid);
231 END IF;
232
233 CASE p_obj_name
234
235 WHEN 'OPDTL_F' THEN
236 -- Purge Opportunity Detail Summary Objects
237 opdtl_f_purge;
238 WHEN 'FST_DTL_F' THEN
239 -- Purge Forecast Summary Objects
240 fst_dtl_f_purge;
241 WHEN 'PIPELINE_F' THEN
242 -- Purge Pipeline Summary Objects
243 pipeline_f_purge;
244 WHEN 'ALL' THEN
245 -- Purge Opportunity Detail Summary Objects
246 opdtl_f_purge;
247 -- Purge Forecast Summary Objects
248 fst_dtl_f_purge;
249 -- Purge Pipeline Summary Objects
250 pipeline_f_purge;
251 ELSE
252 NULL;
253 END CASE;
254
255 COMMIT;
256
257 g_retcode := 0;
258 g_status := TRUE;
259 errbuf := 'Purged ' || p_obj_name ||
260 ' Object(s) successfully upto date ' || g_end_date;
261
262 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
263 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
264 p_module => g_pkg || l_proc || 'End',
265 p_msg => 'End of Procedure '||l_proc
266 );
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 ROLLBACK;
272 g_retcode := -2;
273 g_errbuf := sqlerrm;
274 clean_up;
275 retcode := g_retcode;
276 errbuf := g_errbuf;
277 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
278 fnd_message.set_token('Error is : ' ,SQLCODE);
282 p_module => g_pkg || l_proc || 'proc_error',
279 fnd_message.set_token('Reason is : ', SQLERRM);
280 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
281 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
283 p_msg => fnd_message.get,
284 p_force_log => TRUE
285 );
286 END IF;
287 END purge_obj;
288
289 -- **********************************************************************
290 -- PROCEDURE opdtl_f_purge
291 --
292 -- Purpose:
293 -- To purge data from the Opportunity Detail Summary Objects
294 -- for dates less than End date
295 --
296 -- **********************************************************************
297
298 PROCEDURE opdtl_f_purge IS
299
300 l_proc VARCHAR2(100);
301 l_cnt NUMBER;
302
303 BEGIN
304 l_proc := 'OPDTL_F_PURGE.';
305 l_cnt := 0;
306 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
307 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
308 p_module => g_pkg || l_proc || 'begin',
309 p_msg => 'Start of Procedure '|| l_proc
310 );
311 END IF;
312 -- Initialize the Global Variables
313 init(p_obj_name => 'OPDTL_F');
314
315 -- Delete records from Staging Table where Eff Dt/Cre Dt/Cnv Dt less than End Date
316
317 SELECT COUNT(1)
318 INTO l_cnt
319 FROM bil_bi_opdtl_stg;
320
321 IF l_cnt > 0 THEN
322 DELETE FROM bil_bi_opdtl_stg
323 WHERE opty_creation_date <= g_end_date
324 AND effective_date <= g_end_date
325 AND (opty_ld_conversion_date <= g_end_date
326 OR opty_ld_conversion_date IS NULL)
327 AND close_date <= g_end_date; -- added asolaiy after forecast date changes.
328 END IF;
329
330 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
331 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
332 p_module => g_pkg || l_proc ,
333 p_msg => ' Opdtl Stg Tbl records Deleted :'||SQL%ROWCOUNT);
334 END IF;
335
336
337 -- Delete records from Sumry Table where Eff Dt/Cre Dt/Cnv Dt less than End Date
338
339 DELETE FROM bil_bi_opdtl_f
340 WHERE opty_creation_time_id <= g_end_date_timeid
341 AND opty_close_time_id <= g_end_date_timeid
342 AND (opty_ld_conversion_time_id <= g_end_date_timeid
343 OR opty_ld_conversion_time_id IS NULL)
344 AND opty_effective_time_id <= g_end_date_timeid; -- added asolaiy for forecast date changes.
345
346 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
347 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
348 p_module => g_pkg || l_proc ,
349 p_msg => ' Opdtl_f Summary Tbl records Deleted :'||SQL%ROWCOUNT);
350 END IF;
351
352 g_row_num := SQL%ROWCOUNT;
353
354 COMMIT;
355
356 -- Analyze the Tables
357 bil_bi_util_collection_pkg.analyze_table(p_tbl_name => 'BIL_BI_OPDTL_F',
358 p_cascade => TRUE,
359 p_est_pct => 10 ,
360 p_granularity => 'GLOBAL');
361 g_status := TRUE;
362 g_retcode := 0;
363 -- Call BIS wrap up and clean up
364 clean_up;
365
366 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
367 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
368 p_module => g_pkg || l_proc || 'End',
369 p_msg => 'End of Procedure '||l_proc
370 );
371 END IF;
372 EXCEPTION
373 WHEN OTHERS THEN
374 ROLLBACK;
375 g_retcode := -2;
376 g_status := FALSE;
377 g_errbuf := sqlerrm ;
378 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
379 fnd_message.set_token('Error is : ' ,SQLCODE);
380 fnd_message.set_token('Reason is : ', SQLERRM);
381 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
382 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
383 p_module => g_pkg || l_proc || 'proc_error',
384 p_msg => fnd_message.get,
385 p_force_log => TRUE
386 );
387 END IF;
388 END opdtl_f_purge;
389
390
391 -- **********************************************************************
392 -- PROCEDURE pipeline_f_purge
393 --
394 -- Purpose:
395 -- To purge data from the Pipeline Summary Objects
396 -- for dates less than End date
397 --
398 -- **********************************************************************
399
400 PROCEDURE pipeline_f_purge IS
401
402 l_proc VARCHAR2(100);
403 l_cnt NUMBER;
404
405 BEGIN
406 l_proc := 'PIPELINE_F_PURGE.';
407 l_cnt := 0;
408
409 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
410 bil_bi_util_collection_pkg.writeLog
411 (
412 p_log_level => fnd_log.LEVEL_PROCEDURE,
416 END IF;
413 p_module => g_pkg || l_proc || 'begin',
414 p_msg => 'Start of Procedure '|| l_proc
415 );
417
418 -- Initialize the Global Variables
419 init(p_obj_name => 'PIPELINE_F');
420
421 -- asolaiy added for 8.0
422 init(p_obj_name => 'PIPEC_F');
423
424
425 -- Delete records from Staging Table where Snap Dt less than End Date
426 SELECT COUNT(1)
427 INTO l_cnt
428 FROM bil_bi_pipeline_stg;
429
430 IF l_cnt > 0 THEN
431 DELETE FROM bil_bi_pipeline_stg
432 WHERE snap_date <= g_end_date;
433 END IF;
434
435 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
436 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
437 p_module => g_pkg || l_proc ,
438 p_msg => ' Pipeline Stg Tbl records Deleted :'||SQL%ROWCOUNT);
439 END IF;
440
441 -- Delete records from Sumry Table where Snap Dt less than End Date
442
443 DELETE FROM bil_bi_pipeline_f
444 WHERE snap_date <= g_end_date;
445
446
447 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
448 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
449 p_module => g_pkg || l_proc ,
450 p_msg => ' Pipeline_f Summary Tbl records Deleted :'||SQL%ROWCOUNT);
451 END IF;
452
453 g_row_num := SQL%ROWCOUNT;
454
455 -- asolaiy added for 8.0
456 DELETE FROM bil_bi_pipec_f
457 WHERE snap_date <= g_end_date;
458
459
460 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
461 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
462 p_module => g_pkg || l_proc ,
463 p_msg => ' Pipec_f Summary Tbl records Deleted :'||SQL%ROWCOUNT);
464 END IF;
465
466 g_row_num := g_row_num+SQL%ROWCOUNT;
467
468
469 COMMIT;
470
471 -- Analyze the Tables
472 bil_bi_util_collection_pkg.analyze_table
473 (
474 p_tbl_name => 'BIL_BI_PIPELINE_F',
475 p_cascade => TRUE,
476 p_est_pct => 10 ,
477 p_granularity => 'GLOBAL'
478 );
479
480 -- asolaiy added for 8.0. Analyze the Table
481 bil_bi_util_collection_pkg.analyze_table
482 (
483 p_tbl_name => 'BIL_BI_PIPEC_F',
484 p_cascade => TRUE,
485 p_est_pct => 10 ,
486 p_granularity => 'GLOBAL'
487 );
488
489 g_status := TRUE;
490 g_retcode := 0;
491
492 -- Call BIS wrap up and clean up
493 clean_up;
494
495 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
496 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
497 p_module => g_pkg || l_proc || 'End',
498 p_msg => 'End of Procedure '||l_proc
499 );
500 END IF;
501
502 EXCEPTION
503 WHEN OTHERS THEN
504 ROLLBACK;
505 g_retcode := -2;
506 g_status := FALSE;
507 g_errbuf := sqlerrm ;
508 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
509 fnd_message.set_token('Error is : ' ,SQLCODE);
510 fnd_message.set_token('Reason is : ', SQLERRM);
511 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
512 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
513 p_module => g_pkg || l_proc || 'proc_error',
514 p_msg => fnd_message.get,
515 p_force_log => TRUE);
516 END IF;
517
518 END pipeline_f_purge;
519
520
521 -- **********************************************************************
522 -- PROCEDURE fst_dtl_f_purge
523 --
524 -- Purpose:
525 -- To purge data from the Forecast Summary Objects
526 -- for dates less than End date
527 --
528 -- **********************************************************************
529
530 PROCEDURE fst_dtl_f_purge IS
531
532 l_stmt VARCHAR2(3000);
533 l_end_dt_ent_year_id NUMBER;
534 l_end_dt_ent_qtr_id NUMBER;
535 l_end_dt_ent_per_id NUMBER;
536 l_end_dt_week_id NUMBER;
537 l_list DBMS_SQL.VARCHAR2_TABLE;
538 l_val DBMS_SQL.VARCHAR2_TABLE;
539 l_proc VARCHAR2(100);
540
541 BEGIN
542 l_proc := 'FST_DTL_F_PURGE.';
543 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
544 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
545 p_module => g_pkg || l_proc || 'begin',
546 p_msg => 'Start of Procedure '|| l_proc
547 );
548 END IF;
549
550 -- Initialize the WHO Variables
551 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
552 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
553 p_module => g_pkg || l_proc ,
554 p_msg => ' Initialize the WHO Variables ');
555 END IF;
556
560 l_list(1) := 'BIL_BI_MAP_ENT_FST_PERIOD_TYPE';
557 init(p_obj_name => 'FST_DTL_F');
558
559 -- List of Profile for setup check
561 l_list(2) := 'BIL_BI_FST_ROLLUP';
562 --l_list(3) := 'BIL_BI_ASN_IMPLEMENTED';
563
564 l_list(3) := 'ASN_FRCST_FORECAST_CALENDAR';
565
566
567 IF (NOT bis_common_parameters.check_global_parameters(l_list)) THEN -- Check Parameters
568 bis_common_parameters.get_global_parameters(l_list, l_val);
569 fnd_message.set_name('BIL','BIL_BI_SETUP_INCOMPLETE');
570 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
571 p_module => g_pkg || l_proc || 'proc_error',
572 p_msg => fnd_message.get,
573 p_force_log => TRUE);
574 FOR v_counter IN 1..3 LOOP
575 IF (l_val(v_counter) IS NULL) THEN
576 fnd_message.set_name('BIL','BIL_BI_PROFILE_MISSING');
577 fnd_message.set_token('PROFILE_USER_NAME' ,
578 bil_bi_util_collection_pkg.get_user_profile_name(l_list(v_counter)));
579 fnd_message.set_token('PROFILE_INTERNAL_NAME' ,l_list(v_counter));
580 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
581 p_module => g_pkg || l_proc || 'proc_error',
582 p_msg => fnd_message.get,
583 p_force_log => TRUE);
584 END IF;
585 END LOOP;
586 RAISE G_SETUP_VALID_ERROR;
587 ELSE
588 bis_common_parameters.get_global_parameters(l_list, l_val);
589 END IF; -- Check Parameters Ends Here
590
591 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
592 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
593 p_module => g_pkg || l_proc ,
594 p_msg => 'l_val(1) = ' || l_val(1) || ' l_val(2)= ' || l_val(2) ||
595 ' l_val(3)= ' || l_val(3));
596 END IF;
597
598
599
600 BEGIN
601
602 IF UPPER(l_val(2)) NOT IN ('YES','Y') THEN
603 CASE l_val(1)
604 WHEN 'FII_TIME_ENT_YEAR' THEN
605 SELECT ent_year_id
606 INTO l_end_dt_ent_year_id
607 FROM fii_time_ent_year
608 WHERE start_date <= g_end_date
609 AND end_date >= g_end_date;
610
611 DELETE bil_bi_fst_dtl_f sumry
612 WHERE forecast_time_id <= l_end_dt_ent_year_id
613 AND forecast_period_type_id = 128;
614
615 WHEN 'FII_TIME_ENT_QTR' THEN
616 SELECT ent_qtr_id
617 INTO l_end_dt_ent_qtr_id
618 FROM fii_time_ent_qtr
619 WHERE start_date <= g_end_date
620 AND end_date >= g_end_date;
621
622 DELETE bil_bi_fst_dtl_f sumry
623 WHERE forecast_time_id <= l_end_dt_ent_qtr_id
624 AND forecast_period_type_id = 64;
625
626 WHEN 'FII_TIME_ENT_PERIOD' THEN
627
628 SELECT ent_period_id
629 INTO l_end_dt_ent_per_id
630 FROM fii_time_ent_period
631 WHERE start_date <= g_end_date
632 AND end_date >= g_end_date;
633
634 DELETE bil_bi_fst_dtl_f sumry
635 WHERE forecast_time_id <= l_end_dt_ent_per_id
636 AND forecast_period_type_id = 32;
637
638 WHEN 'FII_TIME_WEEK' THEN
639 SELECT week_id
640 INTO l_end_dt_week_id
641 FROM fii_time_week
642 WHERE start_date <= g_end_date
643 AND end_date >= g_end_date;
644
645 DELETE bil_bi_fst_dtl_f sumry
646 WHERE forecast_time_id <= l_end_dt_week_id
647 AND forecast_period_type_id = 16;
648 ELSE
649 NULL;
650 END CASE;
651 ELSE -- Fst Rollup is 'Yes'
652 -- Get the Time Dimension ID for the End Date (Year)
653 SELECT ent_year_id
654 INTO l_end_dt_ent_year_id
655 FROM fii_time_ent_year
656 WHERE start_date <= g_end_date
657 AND end_date >= g_end_date;
658 -- Get the Time Dimension ID for the End Date (Quarter)
659 SELECT ent_qtr_id
660 INTO l_end_dt_ent_qtr_id
661 FROM fii_time_ent_qtr
662 WHERE start_date <= g_end_date
663 AND end_date >= g_end_date;
664 -- Get the Time Dimension ID for the End Date (Period)
665 SELECT ent_period_id
666 INTO l_end_dt_ent_per_id
667 FROM fii_time_ent_period
668 WHERE start_date <= g_end_date
669 AND end_date >= g_end_date;
670 -- Get the Time Dimension ID for the End Date (Week)
671 SELECT week_id
672 INTO l_end_dt_week_id
673 FROM fii_time_week
674 WHERE start_date <= g_end_date
675 AND end_date >= g_end_date;
676
677 l_stmt := ' l_end_dt_ent_year_id =>'||l_end_dt_ent_year_id||' '||
678 ' l_end_dt_ent_qtr_id =>'||l_end_dt_ent_qtr_id||' '||
679 ' l_end_dt_ent_period_id =>'||l_end_dt_ent_per_id||' '||
680 ' l_end_dt_ent_week_id =>'||l_end_dt_week_id ;
681
682 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
683 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
684 p_module => g_pkg || l_proc ,
685 p_msg => 'Parameters '||l_stmt);
686 END IF;
687
691 WHERE ((forecast_time_id <= l_end_dt_week_id
688 -- Delete records from Summary Table where Forecast Dt less than
689 -- input End Date
690 DELETE bil_bi_fst_dtl_f sumry
692 AND forecast_period_type_id = 16)
693 OR (forecast_time_id <= l_end_dt_ent_per_id
694 AND forecast_period_type_id = 32)
695 OR (forecast_time_id <= l_end_dt_ent_qtr_id
696 AND forecast_period_type_id = 64)
697 OR (forecast_time_id <= l_end_dt_ent_year_id
698 AND forecast_period_type_id = 128));
699
700 l_stmt := ' Summary Tbl records Eff Dt < End Dt Deleted';
701 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
702 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
703 p_module => g_pkg || l_proc ,
704 p_msg => l_stmt|| ' '||SQL%ROWCOUNT);
705 END IF;
706
707 END IF; -- IF l_val(2) <> 'Yes' THEN
708
709 EXCEPTION
710
711 WHEN OTHERS THEN
712 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
713 fnd_message.set_token('Error is : ' ,SQLCODE);
714 fnd_message.set_token('Reason is : ', SQLERRM);
715 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
716 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
717 p_module => g_pkg || l_proc || 'proc_error',
718 p_msg => fnd_message.get,
719 p_force_log => TRUE);
720 END IF;
721 END;
722
723 g_row_num := SQL%ROWCOUNT;
724
725 DELETE FROM bil_bi_processed_fst_id
726 WHERE forecast_id in ( SELECT intrnl.forecast_id f
727 FROM as_internal_forecasts intrnl,
728 gl_periods gl
729 WHERE gl.period_name = intrnl.period_name
730 AND gl.period_set_name = l_val(3)
731 AND gl.end_date <= g_end_date
732 );
733 l_stmt := ' Rows deleted from processed_fst_id table ';
734 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
735 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
736 p_module => g_pkg || l_proc ,
737 p_msg => l_stmt|| ''||SQL%ROWCOUNT);
738 END IF;
739 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_CURRENCY_RATE');
740 bil_bi_util_collection_pkg.truncate_table(p_table_name => 'BIL_BI_FST_DTL_STG');
741 -- Analyze the Tables
742 bil_bi_util_collection_pkg.analyze_table(p_tbl_name => 'BIL_BI_FST_DTL_F',
743 p_cascade => TRUE,
744 p_est_pct => 10 ,
745 p_granularity => 'GLOBAL');
746 bil_bi_util_collection_pkg.analyze_table(p_tbl_name => 'BIL_BI_FST_DTL_STG',
747 p_cascade => TRUE,
748 p_est_pct => 10 ,
749 p_granularity => 'GLOBAL');
750 g_status := TRUE;
751 g_retcode := 0;
752 -- Call BIS wrap up and clean up
753 clean_up;
754 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
755 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
756 p_module => g_pkg || l_proc || 'End',
757 p_msg => 'End of Procedure '||l_proc
758 );
759 END IF;
760 EXCEPTION
761
762 WHEN G_SETUP_VALID_ERROR THEN
763 g_retcode := -1;
764 g_status := FALSE;
765 ROLLBACK;
766 WHEN OTHERS THEN
767 ROLLBACK;
768 g_retcode := -2;
769 g_status := FALSE;
770 g_errbuf := sqlerrm ;
771 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
772 fnd_message.set_token('Error is : ' ,SQLCODE);
773 fnd_message.set_token('Reason is : ', SQLERRM);
774 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
775 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
776 p_module => g_pkg || l_proc || 'proc_error',
777 p_msg => fnd_message.get,
778 p_force_log => TRUE
779 );
780 END IF;
781 END fst_dtl_f_purge;
782
783
784 -- **********************************************************************
785 -- PROCEDURE init
786 --
787 -- Purpose:
788 -- To Initialize the Global Variables
789 --
790 -- **********************************************************************
791
792 PROCEDURE init(p_obj_name IN VARCHAR2) IS
793 l_valid_setup BOOLEAN;
794 l_stmt VARCHAR2(3000);
795 l_proc VARCHAR2(100);
796 BEGIN
797 l_valid_setup := FALSE;
798 l_proc := 'INIT.';
799 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
800 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
801 p_module => g_pkg || l_proc || 'begin',
802 p_msg => 'Start of Procedure '||l_proc
803 );
804 END IF;
805
806 g_errbuf := NULL;
807 g_retcode := 0;
808 g_debug := NVL(BIS_COLLECTION_UTILITIES.g_debug,FALSE);
809 g_row_num := 0;
810 g_status := FALSE;
811 --g_obj_name := p_obj_name;
812
813 -- Delete references from BIS_REFRESH_LOG table
814 BIS_COLLECTION_UTILITIES.deleteLogForObject(p_object_name => 'BIL_BI_' || p_obj_name || '_PURGE');
815
816 -- Call generic Setup procedure
817 l_valid_setup := BIS_COLLECTION_UTILITIES.SETUP(p_object_name => 'BIL_BI_'|| p_obj_name ||'_PURGE');
818
819 IF l_valid_setup THEN
820 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
821 bil_bi_util_collection_pkg.writeLog
822 (
823 p_log_level => fnd_log.LEVEL_STATEMENT,
827 END IF;
824 p_module => g_pkg || l_proc ||' BIS Setup ',
825 p_msg => 'BIS Setup successful'
826 );
828 ELSE
829 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
830 bil_bi_util_collection_pkg.writeLog
831 (
832 p_log_level => fnd_log.LEVEL_STATEMENT,
833 p_module => g_pkg || l_proc ||' BIS Setup ',
834 p_msg => 'BIS Setup Failed'
835 );
836 END IF;
837 g_retcode := 2;
838 RETURN;
839 END IF;
840
841 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
842 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
843 p_module => g_pkg || l_proc || 'End',
844 p_msg => 'End of Procedure '||l_proc);
845 END IF;
846
847 EXCEPTION
848 WHEN OTHERS THEN
849 g_retcode := -1;
850 g_errbuf := sqlerrm ;
851 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
852 fnd_message.set_token('Error is : ' ,SQLCODE);
853 fnd_message.set_token('Reason is : ', SQLERRM);
854 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
855 bil_bi_util_collection_pkg.writeLog
856 (
857 p_log_level => fnd_log.LEVEL_UNEXPECTED,
858 p_module => g_pkg || l_proc || 'proc_error',
859 p_msg => fnd_message.get,
860 p_force_log => TRUE
861 );
862 END IF;
863
864 END init;
865
866
867 -- ***********************************************************************
868 -- PROCEDURE clean_up
869 --
870 -- Purpose:
871 -- To Wrap Up and Clean Up
872 --
873 -- ***********************************************************************
874
875 PROCEDURE clean_up IS
876 l_proc VARCHAR2(100);
877 BEGIN
878 l_proc := 'CLEAN_UP.';
879 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
880 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
881 p_module => g_pkg || l_proc || 'begin',
882 p_msg => 'Start of Procedure '||l_proc
883 );
884 END IF;
885 -- Wrap up the BIS set up
886 IF g_status THEN
887 BIS_COLLECTION_UTILITIES.wrapup(p_status => TRUE,
888 p_count => g_row_num,
889 p_message => NULL,
890 p_period_from => NULL,
891 p_period_to => g_end_date);
892 ELSE
893 BIS_COLLECTION_UTILITIES.wrapup(p_status => FALSE,
894 p_count => 0,
895 p_message => NULL,
896 p_period_from => NULL,
897 p_period_to => g_end_date);
898 END IF;
899 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
900 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
901 p_module => g_pkg || l_proc || 'End',
902 p_msg => 'End of Procedure '||l_proc);
903 END IF;
904 EXCEPTION
905 WHEN OTHERS THEN
906 g_retcode := -1;
907 g_errbuf := sqlerrm;
908 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
909 fnd_message.set_token('Error is : ' ,SQLCODE);
910 fnd_message.set_token('Reason is : ', SQLERRM);
911 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
912 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
913 p_module => g_pkg || l_proc || 'proc_error',
914 p_msg => fnd_message.get,
915 p_force_log => TRUE
916 );
917 END IF;
918 END clean_up;
919
920
921 END bil_bi_purge_obj_pkg;