[Home] [Help]
PACKAGE BODY: APPS.BIM_I_SGMT_ACT_FACTS_PKG
Source
1 PACKAGE BODY BIM_I_SGMT_ACT_FACTS_PKG AS
2 /*$Header: bimisafb.pls 120.15.12010000.1 2008/07/29 21:04:39 appldev ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'BIM_I_SGMT_ACT_FACTS_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimisafb.pls';
6 g_initial_start_date DATE ;
7
8
9 ------------------------------------------------------------------------------------------------
10 ----
11 ----This procedure finds out if the user is trying to run first_load or subsequent load
12 ----and calls the Inital Load or Incremental Load accordingly
13 ----
14 ------------------------------------------------------------------------------------------------
15
16 PROCEDURE POPULATE
17 (
18 p_api_version_number IN NUMBER
19 ,p_init_msg_list IN VARCHAR2
20 ,p_validation_level IN NUMBER
21 ,p_commit IN VARCHAR2
22 ,x_msg_count OUT NOCOPY NUMBER
23 ,x_msg_data OUT NOCOPY VARCHAR2
24 ,x_return_status OUT NOCOPY VARCHAR2
25 ,p_start_date IN DATE
26 ,p_end_date IN DATE
27 ,p_para_num IN NUMBER
28 ,p_truncate_flg IN VARCHAR2
29 ) IS
30
31 l_object_name CONSTANT VARCHAR2(80) := 'BIM_SGMT_ACT';
32 l_conc_start_date DATE;
33 l_conc_end_date DATE;
34 l_start_date DATE;
35 l_end_date DATE;
36 l_user_id NUMBER := FND_GLOBAL.USER_ID();
37 l_api_version_number CONSTANT NUMBER := 1.0;
38 l_api_name CONSTANT VARCHAR2(30) := 'BIM_I_SGMT_ACT_FACTS_PKG';
39 l_mesg_text VARCHAR2(100);
40 l_load_type VARCHAR2(100);
41 l_year_start_date DATE;
42 l_global_date DATE;
43 l_missing_date BOOLEAN := FALSE;
44 l_sysdate DATE;
45
46 l_attribute_table DBMS_SQL.VARCHAR2_TABLE;
47 l_attribute_count NUMBER;
48
49 BEGIN
50
51
52
53 IF NOT bis_collection_utilities.setup(l_object_name) THEN
54 bis_collection_utilities.log('Object BIM_SGMT_ACT Not Setup Properly');
55 RAISE FND_API.G_EXC_ERROR;
56 END IF;
57
58 bis_collection_utilities.log('Start of the Segment Activity Facts Program');
59
60 -- Standard call to check for call compatibility.
61 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
62 p_api_version_number,
63 l_api_name,
64 g_pkg_name) THEN
65
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67
68 END IF;
69
70 -- Initialize message list if p_init_msg_list is set to TRUE.
71 IF FND_API.to_Boolean( p_init_msg_list )
72 THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 l_global_date:= bis_common_parameters.get_global_start_date;
77
78 -- Initialize API return status to SUCCESS
79
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 --Get last Refresh dates of the program
83
84 bis_collection_utilities.get_last_refresh_dates(l_object_name,
85 l_conc_start_date,
86 l_conc_end_date,
87 l_start_date,
88 l_end_date);
89
90 bis_collection_utilities.get_last_user_attributes(l_object_name,
91 l_attribute_table,
92 l_attribute_count);
93
94 IF l_attribute_count > 0 THEN
95
96 IF l_attribute_table(1) = 'INITIAL_LOAD_START_DATE' THEN
97
98 g_initial_start_date := l_attribute_table(2);
99
100 END IF;
101
102 END IF;
103
104 IF (l_end_date IS NULL) THEN
105 --i.e the First Time Base Summary is not executed. so execute First_load
106 --before executing make sure the user called the correct program i.e inital load
107
108 IF (p_start_date IS NULL) THEN
109 --i.e the user initiated incremental program request. raise exception and exit
110 bis_collection_utilities.log('Please run the Update Segment Activity Base Summary - Initial Load Concurrent Program before running this');
111 RAISE FND_API.G_EXC_ERROR;
112 END IF;
113
114 --- Validate Time Dimension Tables
115 fii_time_api.check_missing_date (GREATEST(l_global_date,p_start_date), SYSDATE, l_missing_date);
116
117 IF (l_missing_date) THEN
118 bis_collection_utilities.log('Time Dimension has atleast one missing date between ' || greatest(l_global_date,p_start_date) || ' and ' || sysdate);
119 RAISE FND_API.G_EXC_ERROR;
120 END IF;
121
122 l_load_type := 'FIRST_LOAD';
123
124
125 l_sysdate := sysdate;
126 bis_collection_utilities.log('Before calling FIRST_LOAD');
127
128 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
129 ,p_end_date => l_sysdate
130 ,p_api_version_number => l_api_version_number
131 ,p_init_msg_list => FND_API.G_FALSE
132 ,x_msg_count => x_msg_count
133 ,x_msg_data => x_msg_data
134 ,x_return_status => x_return_status
135 );
136
137 ELSE
138 --i.e Incremental has to be executed.
139 IF p_truncate_flg = 'Y' THEN
140
141 l_load_type := 'FIRST_LOAD';
142
143 l_sysdate := sysdate;
144
145 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
146 ,p_end_date => l_sysdate
147 ,p_api_version_number => l_api_version_number
148 ,p_init_msg_list => FND_API.G_FALSE
149 ,x_msg_count => x_msg_count
150 ,x_msg_data => x_msg_data
151 ,x_return_status => x_return_status
152 );
153
154 ELSE
155
156 --- Validate Time Dimension Tables
157
158 fii_time_api.check_missing_date (l_end_date, sysdate, l_missing_date);
159
160 IF (l_missing_date) THEN
161
162 --Check it from the year start Date of the Year Passed as used in Active Customer Count
163 bis_collection_utilities.log('Time Dimension has atleast one missing date between ' || l_end_date || ' and ' || sysdate);
164 RAISE FND_API.G_EXC_ERROR;
165
166 END IF;
167
168 l_load_type := 'SUBSEQUENT_LOAD';
169
170 l_sysdate := sysdate;
171
172 INCREMENTAL_LOAD(p_start_date => l_end_date +1/86400 -- add one second
173 ,p_end_date => l_sysdate
174 ,p_api_version_number => l_api_version_number
175 ,p_init_msg_list => FND_API.G_FALSE
176 ,x_msg_count => x_msg_count
177 ,x_msg_data => x_msg_data
178 ,x_return_status => x_return_status
179 );
180 END IF;
181
182 END IF;
183
184 ---If Incremental/Inital Load Executes Sucessfully, then call the Load_Schedule_Data Procedure
185
186 IF x_return_status = FND_API.g_ret_sts_error THEN
187
188 RAISE FND_API.g_exc_error;
189
190 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
191
192 RAISE FND_API.g_exc_unexpected_error;
193
194 END IF;
195
196 --Standard check of commit
197
198 IF FND_API.To_Boolean ( p_commit ) THEN
199
200 COMMIT WORK;
201
202 END IF;
203
204 bis_collection_utilities.log('Successful Completion of Segment Activity Facts Program');
205
206 -- Standard call to get message count and if count is 1, get message info.
207 FND_MSG_PUB.Count_And_Get
208 (p_count => x_msg_count,
209 p_data => x_msg_data
210 );
211
212 EXCEPTION
213
214 WHEN FND_API.G_EXC_ERROR THEN
215
216 x_return_status := FND_API.G_RET_STS_ERROR;
217 -- Standard call to get message count and if count=1, get the message
218 FND_msg_PUB.Count_And_Get (
219 p_count => x_msg_count,
220 p_data => x_msg_data
221 );
222
223
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 -- Standard call to get message count and if count=1, get the message
228 FND_msg_PUB.Count_And_Get (
229 p_count => x_msg_count,
230 p_data => x_msg_data
231 );
232
233 WHEN OTHERS THEN
234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
235 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
236 THEN
237 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
238 END IF;
239 -- Standard call to get message count and if count=1, get the message
240 FND_msg_PUB.Count_And_Get (
241 p_count => x_msg_count,
242 p_data => x_msg_data
243 );
244
245 END populate;
246
247
248 --------------------------------------------------------------------------------------------------
249 -- This procedure will populates all the data required into Party Segment facts
250 -- table for the first load.
251 -- PROCEDURE FIRST_LOAD
252 --------------------------------------------------------------------------------------------------
253
254 PROCEDURE FIRST_LOAD
255 ( p_start_date IN DATE
256 ,p_end_date IN DATE
257 ,p_api_version_number IN NUMBER
258 ,p_init_msg_list IN VARCHAR2
259 ,x_msg_count OUT NOCOPY NUMBER
260 ,x_msg_data OUT NOCOPY VARCHAR2
261 ,x_return_status OUT NOCOPY VARCHAR2
262 )
263 IS
264
265 l_api_version_number CONSTANT NUMBER := 1.0;
266 l_api_name CONSTANT VARCHAR2(30) := 'FIRST_LOAD';
267 l_table_name VARCHAR2(100);
268 l_check_missing_rate NUMBER;
269 l_return BOOLEAN;
270
271 l_status VARCHAR2(5);
272 l_industry VARCHAR2(5);
273 l_schema VARCHAR2(30);
274
275 l_source VARCHAR2(80);
276 l_sql_id NUMBER(32);
277 l_query_string VARCHAR2(32767);
278
279 cursor c_query_source is
280 select list_query_id, query
281 from ams_list_queries_all ;
282
283 l_found VARCHAR2(1) := 'N';
284 l_master_type VARCHAR2(80);
285 l_master_type_id NUMBER;
286 l_source_object_name VARCHAR2(80);
287 l_source_object_pk_field VARCHAR2(80);
288 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
289 l_from_position NUMBER;
290 l_from_counter NUMBER;
291 l_end_position NUMBER;
292 l_end_counter NUMBER;
293 l_count NUMBER;
294 l_string_copy VARCHAR2(32767);
295 l_length NUMBER;
296
297 BEGIN
298
299 Execute Immediate 'CREATE TABLE ' || ' source_query_safb ' || '( sql_id NUMBER(32), source_name VARCHAR2(80) )';
300
301 OPEN c_query_source;
302 LOOP
303 FETCH c_query_source INTO l_sql_id, l_query_string;
304 EXIT WHEN c_query_source%notfound;
305
306 if ( l_query_string is NULL ) then
307 l_source := 'NO_MASTER_TYPE';
308 else
309 l_count := 0;
310 l_string_copy := l_query_string;
311
312 l_length := length(l_string_copy);
313
314 LOOP
315 l_count := l_count + 1;
316 IF l_length < 1999 THEN
317 l_sql_string_tbl(l_count) := l_string_copy;
318 EXIT;
319 ELSE
320 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
321 l_string_copy := substr(l_string_copy, 2000);
322 END IF;
323 l_length := length(l_string_copy);
324 END LOOP;
325
326 l_found := 'N';
327 AMS_ListGeneration_PKG.validate_sql_string(
328 p_sql_string => l_sql_string_tbl ,
329 p_search_string => 'FROM',
330 p_comma_valid => 'N',
331 x_found => l_found,
332 x_position => l_from_position,
333 x_counter => l_from_counter) ;
334
335
336 l_found := 'N';
337
338 AMS_ListGeneration_PKG.get_master_types (
339 p_sql_string => l_sql_string_tbl,
340 p_start_length => 1,
341 p_start_counter => 1,
342 p_end_length => l_from_position,
343 p_end_counter => l_from_counter,
344 x_master_type_id=> l_master_type_id,
345 x_master_type=> l_master_type,
346 x_found=> l_found,
347 x_source_object_name => l_source_object_name,
348 x_source_object_pk_field => l_source_object_pk_field);
349
350 IF nvl(l_found,'N') = 'N' THEN
351 --No master type.
352 l_source_object_name := 'NO_MASTER_TYPE';
353 END IF;
354
355
356 l_source := l_master_type;
357 END IF;
358 bis_collection_utilities.log('source_object_name --- '||l_source_object_name);
359 bis_collection_utilities.log('master_type_id --- '||l_master_type_id);
360 bis_collection_utilities.log('master_type --- '||l_master_type);
361 Execute Immediate 'INSERT INTO source_query_safb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';
362 l_source := NULL;
363 l_query_string := NULL;
364 l_sql_id := NULL;
365 END LOOP;
366 CLOSE c_query_source;
367
368 bis_collection_utilities.log('Populated source_name column in source_query_safb table');
369
370
371 -- Standard call to check for call compatibility.
372 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
373 p_api_version_number,
374 l_api_name,
375 g_pkg_name)
376 THEN
377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
378 END IF;
379
380 -- Initialize message list if p_init_msg_list is set to TRUE.
381 IF fnd_api.to_boolean( p_init_msg_list ) THEN
382
383 FND_msg_PUB.initialize;
384
385 END IF;
386
387 -- Initialize API return status to SUCCESS
388 x_return_status := FND_API.G_RET_STS_SUCCESS;
389
390 bis_collection_utilities.log('Running Initial Load of Segment Activity Facts');
391
392 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
393
394 bis_collection_utilities.log('Truncating Facts Table');
395
396 Execute Immediate 'Truncate Table '||l_schema||'.bim_i_sgmt_act_facts';
397
398 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SGMT_ACT');
399
400 ---populating Segment Size
401 bis_collection_utilities.log('Inserting Segment Activity Details');
402
403 Execute Immediate 'INSERT /*+ append parallel*/ ' ||
404 ' INTO bim_i_sgmt_act_facts ' ||
405 ' (creation_date ' ||
406 ' ,last_update_date ' ||
407 ' ,created_by ' ||
408 ' ,last_updated_by ' ||
409 ' ,last_update_login ' ||
410 ' ,segment_id ' ||
411 ' ,segment_association_date ' ||
412 ' ,schedule_id ' ||
413 ' ,schedule_source_code ' ||
414 ' ,source_code_id ' ||
415 ' ) ' ||
416 ' SELECT ' ||
417 ' SYSDATE ' ||
418 ' ,SYSDATE ' ||
419 ' ,-1 ' ||
420 ' ,-1 ' ||
421 ' ,-1 ' ||
422 ' ,c.cell_id segment_id ' ||
423 ' ,a.creation_date ' ||
424 ' ,b.schedule_id ' ||
425 ' ,b.source_code ' ||
426 ' ,d.source_code_id ' ||
427 ' FROM ams_act_lists a ' ||
428 ' , ams_campaign_schedules_b b ' ||
429 ' , (SELECT a.cell_id ' ||
430 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_safb d ' ||
431 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
432 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
433 ' AND b.list_query_id = d.sql_id ' ||
434 ' AND d.source_name = c.source_type_code ' ||
435 ' AND c.based_on_tca_flag = ''Y'' ' ||
436 ' AND a.sel_type =''SQL'' ' ||
437 ' AND a.creation_date >= '''||p_start_date||''''||
438 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
439 ' UNION ALL ' ||
440 ' SELECT a.cell_id ' ||
441 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
442 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
443 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
444 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
445 ' AND d.source_type_code = b.source_object_name ' ||
446 ' AND b.based_on_tca_flag = ''Y'' ' ||
447 ' AND a.sel_type=''DIWB'' ' ||
448 ' AND a.creation_date >= '''||p_start_date||''''||
449 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
450 ' ) c ' ||
451 ' , bim_i_source_codes d ' ||
452 ' WHERE a.list_header_id= c.cell_id ' ||
453 ' AND a.list_act_type = ''CELL'' ' ||
454 ' AND a.list_used_by=''CSCH'' ' ||
455 ' AND a.list_used_by_id = b.schedule_id ' ||
456 ' AND b.schedule_id = d.child_object_id ' ||
457 ' AND d.child_object_type = ''CSCH'' ' ||
458 ' AND b.status_code IN (''COMPLETED'', ''CANCELLED'', ''CLOSED'', ''ACTIVE'', ''ON_HOLD'') ' ||
459 ' AND trunc(a.creation_date) BETWEEN '''|| p_start_date ||''' AND '''||p_end_date||'''' ;
460
461 Execute immediate 'DROP TABLE source_query_safb';
462 bis_collection_utilities.log('table source_query_safb dropped');
463
464 COMMIT;
465
466
467 bis_collection_utilities.wrapup(p_status => TRUE
468 ,p_count => sql%rowcount
469 ,p_period_from => p_start_date
470 ,p_period_to => p_end_date
471 ,p_attribute1 => 'INITIAL_LOAD_START_DATE'
472 ,p_attribute2 => p_start_date
473 );
474
475
476
477 /***************************************************************/
478
479 EXCEPTION
480
481 WHEN FND_API.G_EXC_ERROR THEN
482 x_return_status := FND_API.G_RET_STS_ERROR;
483 Execute Immediate 'DROP TABLE source_query_safb';
484 -- Standard call to get message count and if count=1, get the message
485 FND_msg_PUB.Count_And_Get (
486 -- p_encoded => FND_API.G_FALSE,
487 p_count => x_msg_count,
488 p_data => x_msg_data
489 );
490
491 bis_collection_utilities.log('BIM_I_SGMT_ACT_FACTS_PKG : FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
492
493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
494
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
496 Execute Immediate 'DROP TABLE source_query_safb';
497
498 -- Standard call to get message count and if count=1, get the message
499 FND_msg_PUB.Count_And_Get (
500 --p_encoded => FND_API.G_FALSE,
501 p_count => x_msg_count,
502 p_data => x_msg_data
503 );
504
505 bis_collection_utilities.log('BIM_I_SGMT_ACT_FACTS_PKG : FIRST_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
506
507 WHEN OTHERS THEN
508
509 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
510 Execute Immediate 'DROP TABLE source_query_safb';
511
512 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
513 THEN
514 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
515 END IF;
516
517 -- Standard call to get message count and if count=1, get the message
518 FND_msg_PUB.Count_And_Get (
519 -- p_encoded => FND_API.G_FALSE,
520 p_count => x_msg_count,
521 p_data => x_msg_data
522 );
523
524 bis_collection_utilities.log('BIM_I_SGMT_ACT_FACTS_PKG : FIRST_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
525
526
527
528 END first_load;
529
530 --------------------------------------------------------------------------------------------------
531 -- This procedure will populates all the data required into facts table for incremental load.
532 --
533 -- PROCEDURE INCREMENTAL_LOAD
534 --------------------------------------------------------------------------------------------------
535
536 PROCEDURE INCREMENTAL_LOAD
537 ( p_start_date IN DATE
538 ,p_end_date IN DATE
539 ,p_api_version_number IN NUMBER
540 ,p_init_msg_list IN VARCHAR2
541 ,x_msg_count OUT NOCOPY NUMBER
542 ,x_msg_data OUT NOCOPY VARCHAR2
543 ,x_return_status OUT NOCOPY VARCHAR2
544 )
545 IS
546 l_user_id NUMBER := FND_GLOBAL.USER_ID();
547 l_api_version_number CONSTANT NUMBER := 1.0;
548 l_api_name CONSTANT VARCHAR2(30) := 'INCREMENTAL_LOAD';
549 l_table_name VARCHAR2(100);
550 l_conv_opp_status VARCHAR2(30);
551 l_dead_status VARCHAR2(30);
552 l_check_missing_rate NUMBER;
553 l_stmt VARCHAR2(50);
554 l_cert_level VARCHAR2(3);
555
556 l_sql_id NUMBER(32);
557 l_source VARCHAR2(80);
558 l_query_string VARCHAR2(32767);
559
560 cursor c_query_source is
561 select list_query_id, query
562 from ams_list_queries_all ;
563
564 l_found VARCHAR2(1) := 'N';
565 l_master_type VARCHAR2(80);
566 l_master_type_id NUMBER;
567 l_source_object_name VARCHAR2(80);
568 l_source_object_pk_field VARCHAR2(80);
569 l_sql_string VARCHAR2(32767);
570 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
571 l_from_position NUMBER;
572 l_from_counter NUMBER;
573 l_end_position NUMBER;
574 l_end_counter NUMBER;
575 l_count NUMBER;
576 l_string_copy VARCHAR2(32767);
577 l_length NUMBER;
578
579 BEGIN
580
581 Execute Immediate 'CREATE TABLE ' || ' source_query_safb ' || '( sql_id NUMBER(32), source_name VARCHAR2(80) )';
582
583 OPEN c_query_source;
584 LOOP
585 FETCH c_query_source INTO l_sql_id, l_query_string;
586 EXIT WHEN c_query_source%notfound;
587
588 if ( l_query_string is NULL ) then
589 l_source := 'NO_MASTER_TYPE';
590 else
591 l_count := 0;
592 l_string_copy := l_query_string;
593
594 l_length := length(l_string_copy);
595
596 LOOP
597 l_count := l_count + 1;
598 IF l_length < 1999 THEN
599 l_sql_string_tbl(l_count) := l_string_copy;
600 EXIT;
601 ELSE
602 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
603 l_string_copy := substr(l_string_copy, 2000);
604 END IF;
605 l_length := length(l_string_copy);
606 END LOOP;
607
608 l_found := 'N';
609 AMS_ListGeneration_PKG.validate_sql_string(
610 p_sql_string => l_sql_string_tbl ,
611 p_search_string => 'FROM',
612 p_comma_valid => 'N',
613 x_found => l_found,
614 x_position => l_from_position,
615 x_counter => l_from_counter) ;
616
617
618 l_found := 'N';
619
620 AMS_ListGeneration_PKG.get_master_types (
621 p_sql_string => l_sql_string_tbl,
622 p_start_length => 1,
623 p_start_counter => 1,
624 p_end_length => l_from_position,
625 p_end_counter => l_from_counter,
626 x_master_type_id=> l_master_type_id,
627 x_master_type=> l_master_type,
628 x_found=> l_found,
629 x_source_object_name => l_source_object_name,
630 x_source_object_pk_field => l_source_object_pk_field);
631
632 IF nvl(l_found,'N') = 'N' THEN
633 --No master type.
634 l_source_object_name := 'NO_MASTER_TYPE';
635 END IF;
636
637
638 l_source := l_master_type;
639 END IF;
640 bis_collection_utilities.log('source_object_name --- '||l_source_object_name);
641 bis_collection_utilities.log('master_type_id --- '||l_master_type_id);
642 bis_collection_utilities.log('master_type --- '||l_master_type);
643 Execute Immediate 'INSERT INTO source_query_safb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';
644 l_source := NULL;
645 l_query_string := NULL;
646 l_sql_id := NULL;
647 END LOOP;
648 CLOSE c_query_source;
649
650 bis_collection_utilities.log('Populated source_name column in source_query_safb table');
651
652 -- Standard call to check for call compatibility.
653 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
654 p_api_version_number,
655 l_api_name,
656 g_pkg_name)
657 THEN
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 END IF;
660
661 -- Initialize message list if p_init_msg_list is set to TRUE.
662 IF FND_API.to_Boolean( p_init_msg_list ) THEN
663
664 FND_msg_PUB.initialize;
665
666 END IF;
667
668 -- Initialize API return status to SUCCESS
669 x_return_status := FND_API.G_RET_STS_SUCCESS;
670
671 bis_collection_utilities.log('Running Incremental Load of Segment Activity Facts');
672
673 bis_collection_utilities.log('Inserting Segment Activity Details');
674
675 Execute Immediate 'INSERT /*+ append parallel*/ ' ||
676 ' INTO bim_i_sgmt_act_facts ' ||
677 ' (creation_date ' ||
678 ' ,last_update_date ' ||
679 ' ,created_by ' ||
680 ' ,last_updated_by ' ||
681 ' ,last_update_login ' ||
682 ' ,segment_id ' ||
683 ' ,segment_association_date ' ||
684 ' ,schedule_id ' ||
685 ' ,schedule_source_code ' ||
686 ' ,source_code_id ' ||
687 ' ) ' ||
688 ' SELECT ' ||
689 ' SYSDATE ' ||
690 ' ,SYSDATE ' ||
691 ' ,-1 ' ||
692 ' ,-1 ' ||
693 ' ,-1 ' ||
694 ' ,c.cell_id segment_id ' ||
695 ' ,a.creation_date ' ||
696 ' ,b.schedule_id ' ||
697 ' ,b.source_code ' ||
698 ' ,d.source_code_id ' ||
699 ' FROM ams_act_lists a ' ||
700 ' , ams_campaign_schedules_b b ' ||
701 ' , (SELECT a.cell_id ' ||
702 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_safb d ' ||
703 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
704 ' AND b.arc_act_list_query_used_by = ''CELL'' ' ||
705 ' AND b.list_query_id = d.sql_id ' ||
706 ' AND d.source_name = c.source_type_code ' ||
707 ' AND c.based_on_tca_flag = ''Y'' ' ||
708 ' AND a.sel_type =''SQL'' ' ||
709 ' AND a.creation_date >= '''||g_initial_start_date||''''||
710 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
711 ' UNION ALL ' ||
712 ' SELECT a.cell_id ' ||
713 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
714 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
715 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
716 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
717 ' AND d.source_type_code = b.source_object_name ' ||
718 ' AND b.based_on_tca_flag = ''Y'' ' ||
719 ' AND a.sel_type=''DIWB'' ' ||
720 ' AND a.creation_date >= '''||g_initial_start_date||''''||
721 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
722 ' ) c ' ||
723 ' , bim_i_source_codes d ' ||
724 ' WHERE a.list_header_id= c.cell_id ' ||
725 ' AND a.list_act_type = ''CELL'' ' ||
726 ' AND a.list_used_by=''CSCH'' ' ||
727 ' AND a.list_used_by_id = b.schedule_id ' ||
728 ' AND b.schedule_id = d.child_object_id ' ||
729 ' AND d.child_object_type = ''CSCH'' ' ||
730 ' AND b.status_code IN (''COMPLETED'', ''CANCELLED'', ''CLOSED'', ''ACTIVE'', ''ON_HOLD'') ' ||
731 ' AND trunc(d.obj_last_update_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
732 ' AND NOT EXISTS ( SELECT 1 ' ||
733 ' FROM bim_i_sgmt_act_facts fct ' ||
734 ' WHERE fct.segment_id = a.list_header_id ' ||
735 ' AND fct.source_code_id = d.source_code_id)';
736
737 execute immediate 'DROP TABLE source_query_safb';
738
739 COMMIT;
740
741 bis_collection_utilities.wrapup(p_status => TRUE
742 ,p_count => sql%rowcount
743 ,p_period_from => p_start_date
744 ,p_period_to => p_end_date
745 ,p_attribute1 => 'INITIAL_LOAD_START_DATE'
746 ,p_attribute2 =>g_initial_start_date
747 );
748
749
750
751 EXCEPTION
752
753 WHEN FND_API.G_EXC_ERROR THEN
754 x_return_status := FND_API.G_RET_STS_ERROR;
755 Execute Immediate 'DROP TABLE source_query_safb';
756 -- Standard call to get message count and if count=1, get the message
757 FND_msg_PUB.Count_And_Get (
758 -- p_encoded => FND_API.G_FALSE,
759 p_count => x_msg_count,
760 p_data => x_msg_data
761 );
762
763 bis_collection_utilities.log('BIM_I_SGMT_ACT_FACTS_PKG:INCREMENTAL_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
764
765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
766
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
768 Execute Immediate 'DROP TABLE source_query_safb';
769
770 -- Standard call to get message count and if count=1, get the message
771 FND_msg_PUB.Count_And_Get (
772 --p_encoded => FND_API.G_FALSE,
773 p_count => x_msg_count,
774 p_data => x_msg_data
775 );
776
777 bis_collection_utilities.log('BIM_I_SGMT_ACT_FACTS_PKG:INCREMENTAL_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
778
779 WHEN OTHERS THEN
780
781 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
782 Execute Immediate 'DROP TABLE source_query_safb';
783
784 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
785 THEN
786 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
787 END IF;
788
789 -- Standard call to get message count and if count=1, get the message
790 FND_msg_PUB.Count_And_Get (
791 -- p_encoded => FND_API.G_FALSE,
792 p_count => x_msg_count,
793 p_data => x_msg_data
794 );
795
796 bis_collection_utilities.log('BIM_I_SGMT_ACT_FACTS_PKG:INCREMENTAL_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
797
798
799 END incremental_load;
800
801
802
803 END BIM_I_SGMT_ACT_FACTS_PKG;
804