[Home] [Help]
PACKAGE BODY: APPS.BIM_PERIODIC_FACTS
Source
1 PACKAGE BODY bim_periodic_facts AS
2 /* $Header: bimrlfab.pls 120.0 2005/06/01 13:01:20 appldev noship $*/
3
4 --g_pkg_name CONSTANT VARCHAR2(20) :='BIM_PERIODIC_FACTS';
5 --G_FILE_NAME CONSTANT VARCHAR2(20) :='bimrlfab.pls';
6
7 PROCEDURE invoke_object
8 (ERRBUF OUT NOCOPY VARCHAR2,
9 RETCODE OUT NOCOPY NUMBER,
10 p_api_version_number IN NUMBER ,
11 p_object IN VARCHAR2 DEFAULT NULL,
12 p_end_dt IN VARCHAR2 DEFAULT NULL,
13 p_proc_num IN NUMBER DEFAULT 8,
14 p_full_refresh IN VARCHAR2 DEFAULT 'N'
15 ) IS
16 CURSOR min_log_date IS
17 SELECT TRUNC(MIN(start_date))
18 FROM bim_rep_history ;
19
20 cursor max_log_date IS
21 select TRUNC(max(object_last_updated_date))
22 from bim_rep_history
23 where object='DATES';
24 v_error_code NUMBER;
25 v_error_text VARCHAR2(1500);
26 l_start_date DATE;
27 l_end_date DATE;
28 l_user_id NUMBER := FND_GLOBAL.USER_ID();
29 l_api_version_number CONSTANT NUMBER := 1.0;
30 l_api_name CONSTANT VARCHAR2(30) := 'invoke_object';
31 x_msg_count NUMBER;
32 x_msg_data VARCHAR2(240);
33 x_return_status VARCHAR2(1) ;
34 l_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
35 l_min_date DATE;
36 l_max_date DATE;
37 p_end_date DATE := FND_DATE.CANONICAL_TO_DATE(p_end_dt);
38
39 /* ==================== Truncation ==================*/
40 PROCEDURE truncate_facts (p_object VARCHAR2 , p_confirm VARCHAR2 ) IS
41
42 CURSOR trunc_camp_tables(p_owner varchar2) IS
43 SELECT 'TRUNCATE TABLE '|| owner ||'.' || table_name sqlstmt
44 FROM all_tables
45 WHERE table_name like 'BIM_R_CAMP_%LY%_FACTS'
46 AND owner = p_owner
47 AND table_name not like '%_MV%';
48
49 CURSOR trunc_even_tables(p_owner varchar2) IS
50 SELECT 'TRUNCATE TABLE '|| owner ||'.' || table_name sqlstmt
51 FROM all_tables
52 WHERE table_name like 'BIM_R_EVEN_%LY%_FACTS'
53 AND owner = p_owner
54 AND table_name not like '%_MV%';
55
56 CURSOR trunc_fund_tables(p_owner varchar2) IS
57 SELECT 'TRUNCATE TABLE '|| owner ||'.' || table_name sqlstmt
58 FROM all_tables
59 WHERE (table_name like 'BIM_R_FUND_%LY%_FACTS' or table_name like 'BIM_R_FDSP_%LY%_FACTS')
60 AND owner = p_owner
61 AND table_name not like '%_MV%';
62
63 CURSOR trunc_lead_tables(p_owner varchar2) IS
64 SELECT 'TRUNCATE TABLE ' || owner ||'.'||table_name sqlstmt
65 FROM all_tables
66 WHERE table_name like 'BIM_R_LEAD_%_FACTS'
67 AND owner = p_owner
68 AND table_name not like '%_MV%';
69
70 CURSOR trunc_limp_tables(p_owner varchar2) IS
71 SELECT 'TRUNCATE TABLE ' || owner ||'.'||table_name sqlstmt
72 FROM all_tables
73 WHERE table_name like 'BIM_R_LIMP_%LY%_FACTS'
74 AND owner = p_owner
75 AND table_name not like '%_MV%';
76
77 CURSOR trunc_resp_tables(p_owner varchar2) IS
78 SELECT 'TRUNCATE TABLE ' || owner ||'.'||table_name sqlstmt
79 FROM all_tables
80 WHERE (table_name like 'BIM_R_RRSN_%LY%_FACTS' OR table_name like 'BIM_R_RGRD_%LY%_FACTS')
81 AND owner = p_owner
82 AND table_name not like '%_MV%';
83
84 ddl_curs integer;
85
86 l_status VARCHAR2(5);
87 l_industry VARCHAR2(5);
88 l_schema VARCHAR2(30);
89 l_return BOOLEAN;
90
91 BEGIN
92
93 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
94
95 ------------------------------------------
96
97 IF p_confirm = 'Y' THEN
98
99 IF p_object = 'CAMPAIGN' THEN
100
101 ddl_curs := dbms_sql.open_cursor;
102
103 FOR rec in trunc_camp_tables(l_schema) LOOP
104
105 /* Parse implicitly executes the DDL statements */
106
107 dbms_sql.parse(ddl_curs, rec.sqlstmt,dbms_sql.native) ;
108
109 END LOOP;
110
111 dbms_sql.close_cursor(ddl_curs);
112
113 DELETE bim_rep_history
114 WHERE object = 'CAMPAIGN';
115
116 END IF;
117
118 ------------------------------------------
119
120 IF p_object = 'EVENT' THEN
121
122 ddl_curs := dbms_sql.open_cursor;
123
124 FOR rec in trunc_even_tables(l_schema) LOOP
125
126 /* Parse implicitly executes the DDL statements */
127
128 dbms_sql.parse(ddl_curs, rec.sqlstmt,dbms_sql.native) ;
129
130 END LOOP;
131
132 dbms_sql.close_cursor(ddl_curs);
133
134 DELETE bim_rep_history
135 WHERE object = 'EVENT';
136
137 END IF;
138
139 ------------------------------------------
140
141 IF p_object = 'BUDGET' THEN
142
143 ddl_curs := dbms_sql.open_cursor;
144
145 FOR rec in trunc_fund_tables(l_schema) LOOP
146
147 /* Parse implicitly executes the DDL statements */
148
149 dbms_sql.parse(ddl_curs, rec.sqlstmt,dbms_sql.native) ;
150
151 END LOOP;
152
153 dbms_sql.close_cursor(ddl_curs);
154
155 DELETE BIM_REP_HISTORY
156 WHERE object = 'FUND';
157
158 END IF;
159
160 ------------------------------------------
161
162 IF p_object = 'LEADS' THEN
163
164 ddl_curs := dbms_sql.open_cursor;
165
166 FOR rec in trunc_lead_tables(l_schema) LOOP
167
168 /* Parse implicitly executes the DDL statements */
169
170 dbms_sql.parse (ddl_curs, rec.sqlstmt, dbms_sql.native);
171
172 END LOOP;
173
174 dbms_sql.close_cursor (ddl_curs);
175
176 DELETE BIM_REP_HISTORY
177 WHERE object = 'LEADS';
178
179 END IF;
180
181 ------------------------------------------
182
183 IF p_object = 'LEAD_IMPORT' THEN
184
185 ddl_curs := dbms_sql.open_cursor;
186
187 FOR rec in trunc_limp_tables(l_schema) LOOP
188
189 /* Parse implicitly executes the DDL statements */
190
191 dbms_sql.parse (ddl_curs, rec.sqlstmt, dbms_sql.native);
192
193 END LOOP;
194
195 dbms_sql.close_cursor (ddl_curs);
196
197 DELETE BIM_REP_HISTORY
198 WHERE object = 'LEAD_IMPORT';
199
200 END IF;
201
202 ------------------------------------------
203
204 IF p_object = 'RESPONSE' THEN
205
206 ddl_curs := dbms_sql.open_cursor;
207
208 FOR rec in trunc_resp_tables(l_schema) LOOP
209
210 /* Parse implicitly executes the DDL statements */
211
212 dbms_sql.parse (ddl_curs, rec.sqlstmt, dbms_sql.native);
213
214 END LOOP;
215
216 dbms_sql.close_cursor (ddl_curs);
217
218 DELETE BIM_REP_HISTORY
219 WHERE object = 'RESPONSE';
220
221 END IF;
222
223 ------------------------------------------
224
225 IF p_object = 'ALL' THEN
226
227 ddl_curs := dbms_sql.open_cursor;
228
229 FOR rec in trunc_camp_tables(l_schema) LOOP
230
231 /* Parse implicitly executes the DDL statements */
232
233 dbms_sql.parse(ddl_curs, rec.sqlstmt,dbms_sql.native) ;
234
235 END LOOP;
236
237 DELETE bim_rep_history
238 WHERE object = 'CAMPAIGN';
239
240 FOR rec in trunc_even_tables(l_schema) LOOP
241
242 /* Parse implicitly executes the DDL statements */
243
244 dbms_sql.parse(ddl_curs, rec.sqlstmt,dbms_sql.native) ;
245
246 END LOOP;
247
248 DELETE bim_rep_history
249 WHERE object = 'EVENT';
250
251 FOR rec in trunc_fund_tables(l_schema) LOOP
252
253 /* Parse implicitly executes the DDL statements */
254
255 dbms_sql.parse(ddl_curs, rec.sqlstmt,dbms_sql.native) ;
256
257 END LOOP;
258
259 DELETE bim_rep_history
260 WHERE object = 'FUND';
261
262
263 FOR rec in trunc_lead_tables(l_schema) LOOP
264
265 /* Parse implicitly executes the DDL statements */
266
267 dbms_sql.parse (ddl_curs, rec.sqlstmt, dbms_sql.native);
268
269 END LOOP;
270
271 DELETE BIM_REP_HISTORY
272 WHERE object = 'LEADS';
273
274 FOR rec in trunc_limp_tables(l_schema) LOOP
275
276 /* Parse implicitly executes the DDL statements */
277
278 dbms_sql.parse (ddl_curs, rec.sqlstmt, dbms_sql.native);
279
280 END LOOP;
281
282 DELETE BIM_REP_HISTORY
283 WHERE object = 'LEAD_IMPORT';
284
285 FOR rec in trunc_resp_tables(l_schema) LOOP
286
287 /* Parse implicitly executes the DDL statements */
288
289 dbms_sql.parse (ddl_curs, rec.sqlstmt, dbms_sql.native);
290
291 END LOOP;
292
293 DELETE BIM_REP_HISTORY
294 WHERE object = 'RESPONSE';
295
296 dbms_sql.close_cursor(ddl_curs);
297
298 END IF;
299
300 DELETE bim_rep_history
301 WHERE object = 'DATES';
302
303 END IF;
304
305 ------------------------------------------
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 NULL;
310 END;
311 /* ==================== Truncation ==================*/
312 BEGIN
313
314
315 -- Standard call to check for call compatibility.
316 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
317 p_api_version_number,
318 l_api_name,
319 g_pkg_name)
320 THEN
321 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
322 END IF;
323
324
325 -- Initialize message list if p_init_msg_list is set to TRUE.
326
327
328 -- Debug Message
329 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
330
331
332 -- Initialize API return status to SUCCESS
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334 OPEN min_log_date;
335 FETCH min_log_date into l_min_date;
336 CLOSE min_log_date;
337
338 IF p_full_refresh = 'Y' AND l_min_date is NOT NULL THEN
339 BEGIN
340 truncate_facts (p_object ,'Y');
341 BEGIN
342 bim_first_load_facts.invoke_object (ERRBUF,
343 RETCODE,
344 1,
345 p_object,
346 TO_CHAR(l_min_date,'YYYY/MM/DD HH24:MI:SS'),
347 TO_CHAR(p_end_date,'YYYY/MM/DD HH24:MI:SS'),
348 p_proc_num);
349 END;
350 END;
351 ELSIF p_full_refresh = 'Y' AND l_min_date is NULL THEN
352 ams_utility_pvt.write_conc_log('First Time Load is not run. Please run the Initial Load.');
353 ams_utility_pvt.write_conc_log('Concurrent Program Exits Now');
354 RAISE FND_API.G_EXC_ERROR;
355 ELSE
356 BEGIN
357 OPEN max_log_date;
358 FETCH max_log_date into l_max_date;
359 CLOSE max_log_date;
360 IF (l_max_date is null) or (l_max_date < TRUNC(sysdate)) THEN
361 BIM_POPDATES_PKG.pop_intl_dates(l_min_date);
362 END IF;
363
364
365 BIM_SOURCE_CODE_PKG.LOAD_DATA(p_api_version_number=>1
366 ,x_msg_count => x_msg_count
367 ,x_msg_data => x_msg_data
368 ,x_return_status => x_return_status);
369
370
371 IF p_object = 'CAMPAIGN' THEN
372
373 bim_campaign_facts.populate
374 (p_api_version_number => 1.0
375 ,p_init_msg_list => FND_API.G_FALSE
376 ,x_msg_count => x_msg_count
377 ,x_msg_data => x_msg_data
378 ,x_return_status => x_return_status
379 ,p_object => p_object
380 ,p_start_date => NULL
381 ,p_end_date => p_end_date
382 ,p_para_num => p_proc_num
383 );
384 IF x_return_status = FND_API.g_ret_sts_error
385 THEN
386 RAISE FND_API.g_exc_error;
387 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
388 RAISE FND_API.g_exc_unexpected_error;
389 END IF;
390
391
392 ELSIF
393 p_object = 'EVENT' THEN
394
395
396 bim_event_facts.POPULATE (
397 p_api_version_number => 1.0,
398 p_init_msg_list => FND_API.G_FALSE,
399 x_msg_count => x_msg_count ,
400 x_msg_data => x_msg_data ,
401 x_return_status => x_return_status ,
402 p_object => 'EVENT',
403 p_start_date => NULL,
404 p_end_date => p_end_date,
405 p_para_num => p_proc_num
406 );
407 IF x_return_status = FND_API.g_ret_sts_error
408 THEN
409 RAISE FND_API.g_exc_error;
410 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
411 RAISE FND_API.g_exc_unexpected_error;
412 END IF;
413
414 ELSIF
415 p_object = 'RESPONSE' THEN
416
417 bim_response_facts_pkg.populate (
418 p_api_version_number => 1.0,
419 p_init_msg_list => FND_API.G_FALSE,
420 x_msg_count => x_msg_count ,
421 x_msg_data => x_msg_data ,
422 x_return_status => x_return_status ,
423 p_start_date => NULL,
424 p_end_date => p_end_date,
425 p_para_num => p_proc_num
426 );
427 IF x_return_status = FND_API.g_ret_sts_error
428 THEN
429 RAISE FND_API.g_exc_error;
430 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
431 RAISE FND_API.g_exc_unexpected_error;
432 END IF;
433 ELSIF
434 p_object = 'LEAD_IMPORT' THEN
435
436 bim_lead_import_facts_pkg.POPULATE (
437 p_api_version_number => 1.0,
438 p_init_msg_list => FND_API.G_FALSE,
439 x_msg_count => x_msg_count ,
440 x_msg_data => x_msg_data ,
441 x_return_status => x_return_status ,
442 p_object => 'EVENT',
443 p_start_date => NULL,
444 p_end_date => p_end_date,
445 p_para_num => p_proc_num
446 );
447 IF x_return_status = FND_API.g_ret_sts_error
448 THEN
449 RAISE FND_API.g_exc_error;
450 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
451 RAISE FND_API.g_exc_unexpected_error;
452 END IF;
453 ELSIF
454 p_object = 'LEADS' THEN
455
456 bim_lead_facts_pkg.POPULATE (
457 p_api_version_number => 1.0,
458 p_init_msg_list => FND_API.G_FALSE,
459 x_msg_count => x_msg_count ,
460 x_msg_data => x_msg_data ,
461 x_return_status => x_return_status ,
462 p_object => p_object,
463 p_start_date => NULL,
464 p_end_date => p_end_date,
465 p_para_num => p_proc_num
466 );
467 IF x_return_status = FND_API.g_ret_sts_error
468 THEN
469 RAISE FND_API.g_exc_error;
470 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
471 RAISE FND_API.g_exc_unexpected_error;
472 END IF;
473
474
475 ELSIF
476 p_object = 'BUDGET'THEN
477
478 bim_fund_facts.populate (
479 p_api_version_number => 1.0,
480 p_init_msg_list => FND_API.G_FALSE,
481 x_msg_count => x_msg_count ,
482 x_msg_data => x_msg_data ,
483 x_return_status => x_return_status ,
484 P_OBJECT => 'FUND',
485 p_start_date => NULL,
486 P_END_DATE => p_end_date,
487 p_para_num => p_proc_num
488 );
489 IF x_return_status = FND_API.g_ret_sts_error
490 THEN
491 RAISE FND_API.g_exc_error;
492 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
493 RAISE FND_API.g_exc_unexpected_error;
494 END IF;
495
496 ELSIF
497 p_object = 'ALL' THEN
498
499 bim_campaign_facts.populate
500 (p_api_version_number => 1.0
501 ,p_init_msg_list => FND_API.G_FALSE
502 ,x_msg_count => x_msg_count
503 ,x_msg_data => x_msg_data
504 ,x_return_status => x_return_status
505 ,p_object => 'CAMPAIGN'
506 ,p_start_date => NULL
507 ,p_end_date => p_end_date
508 ,p_para_num => p_proc_num
509 );
510
511 IF x_return_status = FND_API.g_ret_sts_error
512 THEN
513 RAISE FND_API.g_exc_error;
514 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
515 RAISE FND_API.g_exc_unexpected_error;
516 END IF;
517
518 bim_event_facts.populate (
519 p_api_version_number => 1.0,
520 p_init_msg_list => FND_API.G_FALSE,
521 x_msg_count => x_msg_count ,
522 x_msg_data => x_msg_data ,
523 x_return_status => x_return_status ,
524 p_object => 'EVENT',
525 p_start_date => NULL,
526 p_end_date => p_end_date,
527 p_para_num => p_proc_num
528 );
529 IF x_return_status = FND_API.g_ret_sts_error
530 THEN
531 RAISE FND_API.g_exc_error;
532 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
533 RAISE FND_API.g_exc_unexpected_error;
534 END IF;
535
536 bim_response_facts_pkg.populate (
537 p_api_version_number => 1.0,
538 p_init_msg_list => FND_API.G_FALSE,
539 x_msg_count => x_msg_count ,
540 x_msg_data => x_msg_data ,
541 x_return_status => x_return_status ,
542 p_start_date => NULL,
543 p_end_date => p_end_date,
544 p_para_num => p_proc_num
545 );
546 IF x_return_status = FND_API.g_ret_sts_error
547 THEN
548 RAISE FND_API.g_exc_error;
549 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
550 RAISE FND_API.g_exc_unexpected_error;
551 END IF;
552
553 bim_lead_facts_pkg.POPULATE (
554 p_api_version_number => 1.0,
555 p_init_msg_list => FND_API.G_FALSE,
556 x_msg_count => x_msg_count ,
557 x_msg_data => x_msg_data ,
558 x_return_status => x_return_status ,
559 p_object => p_object,
560 p_start_date => NULL,
561 p_end_date => p_end_date,
562 p_para_num => p_proc_num
563 );
564 IF x_return_status = FND_API.g_ret_sts_error
565 THEN
566 RAISE FND_API.g_exc_error;
567 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
568 RAISE FND_API.g_exc_unexpected_error;
569 END IF;
570
571 bim_lead_import_facts_pkg.POPULATE (
572 p_api_version_number => 1.0,
573 p_init_msg_list => FND_API.G_FALSE,
574 x_msg_count => x_msg_count ,
575 x_msg_data => x_msg_data ,
576 x_return_status => x_return_status ,
577 p_object => 'EVENT',
578 p_start_date => NULL,
579 p_end_date => p_end_date,
580 p_para_num => p_proc_num
581 );
582 IF x_return_status = FND_API.g_ret_sts_error
583 THEN
584 RAISE FND_API.g_exc_error;
585 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
586 RAISE FND_API.g_exc_unexpected_error;
587 END IF;
588
589 bim_fund_facts.populate (
590 p_api_version_number => 1.0,
591 p_init_msg_list => FND_API.G_FALSE,
592 x_msg_count => x_msg_count ,
593 x_msg_data => x_msg_data ,
594 x_return_status => x_return_status ,
595 p_object => 'FUND',
596 p_start_date => NULL,
597 p_end_date => p_end_date,
598 p_para_num => p_proc_num
599 );
600
601 IF x_return_status = FND_API.g_ret_sts_error
602 THEN
603 RAISE FND_API.g_exc_error;
604 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
605 RAISE FND_API.g_exc_unexpected_error;
606 END IF;
607
608 END IF;
609
610 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'End');
611
612 EXCEPTION
613
614 WHEN FND_API.G_EXC_ERROR THEN
615 x_return_status := FND_API.g_ret_sts_error ;
616 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
617 p_count => x_msg_count,
618 p_data => x_msg_data);
619 ERRBUF := x_msg_data;
620 RETCODE := 2;
621 ams_utility_pvt.write_conc_log(sqlerrm(sqlcode));
622 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
623 x_return_status := FND_API.g_ret_sts_unexp_error ;
624 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
625 p_count => x_msg_count,
626 p_data => x_msg_data);
627 ERRBUF := x_msg_data;
628 RETCODE := 2;
629 ams_utility_pvt.write_conc_log(sqlerrm(sqlcode));
630 WHEN OTHERS THEN
631 x_return_status := FND_API.g_ret_sts_unexp_error ;
632 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
633 p_count => x_msg_count,
634 p_data => x_msg_data);
635 ERRBUF := sqlerrm(sqlcode);
636 RETCODE := sqlcode;
637 ams_utility_pvt.write_conc_log(sqlerrm(sqlcode));
638 END;
639 END IF;
640 END invoke_object;
641 END bim_periodic_facts;