[Home] [Help]
PACKAGE BODY: APPS.BIM_I_SGMT_CUST_FACTS_PKG
Source
1 PACKAGE BODY BIM_I_SGMT_CUST_FACTS_PKG AS
2 /*$Header: bimiscfb.pls 120.16.12010000.1 2008/07/29 21:04:43 appldev ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'BIM_I_SGMT_CUST_FACTS_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimispfb.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_CUST';
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_CUST_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 IF NOT bis_collection_utilities.setup(l_object_name) THEN
52 bis_collection_utilities.log('Object BIM_CUST_SGMT Not Setup Properly');
53 RAISE FND_API.G_EXC_ERROR;
54 END IF;
55
56 bis_collection_utilities.log('Start of the Segment Customer Facts Program');
57
58 -- Standard call to check for call compatibility.
59 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
60 p_api_version_number,
61 l_api_name,
62 g_pkg_name) THEN
63
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65
66 END IF;
67
68 -- Initialize message list if p_init_msg_list is set to TRUE.
69
70 l_global_date:= bis_common_parameters.get_global_start_date;
71
72 IF FND_API.to_Boolean( p_init_msg_list )
73 THEN
74 FND_MSG_PUB.initialize;
75 END IF;
76
77 -- Initialize API return status to SUCCESS
78
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80
81 --Get last Refresh dates of the program
82
83 bis_collection_utilities.get_last_refresh_dates(l_object_name,
84 l_conc_start_date,
85 l_conc_end_date,
86 l_start_date,
87 l_end_date);
88
89 bis_collection_utilities.get_last_user_attributes(l_object_name,
90 l_attribute_table,
91 l_attribute_count);
92
93 IF l_attribute_count > 0 THEN
94
95 IF l_attribute_table(1) = 'INITIAL_LOAD_START_DATE' THEN
96
97 g_initial_start_date := l_attribute_table(2);
98
99 END IF;
100
101 END IF;
102
103 IF (l_end_date IS NULL) THEN
104 --i.e the First Time Base Summary is not executed. so execute First_load
105 --before executing make sure the user called the correct program i.e inital load
106
107 IF (p_start_date IS NULL) THEN
108 --i.e the user initiated incremental program request. raise exception and exit
109 bis_collection_utilities.log('Please run the Update Segment Customer Base Summary - Initial Load Concurrent Program before running this');
110 RAISE FND_API.G_EXC_ERROR;
111 END IF;
112
113 --- Validate Time Dimension Tables
114 fii_time_api.check_missing_date (GREATEST(l_global_date,p_start_date), SYSDATE, l_missing_date);
115
116 IF (l_missing_date) THEN
117 bis_collection_utilities.log('Time Dimension has atleast one missing date between ' || greatest(l_global_date,p_start_date) || ' and ' || sysdate);
118 RAISE FND_API.G_EXC_ERROR;
119 END IF;
120
121 l_load_type := 'FIRST_LOAD';
122
123
124 l_sysdate := sysdate;
125
126 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
127 ,p_end_date => l_sysdate
128 ,p_api_version_number => l_api_version_number
129 ,p_init_msg_list => FND_API.G_FALSE
130 ,x_msg_count => x_msg_count
131 ,x_msg_data => x_msg_data
132 ,x_return_status => x_return_status
133 );
134
135 ELSE
136 --i.e Incremental has to be executed.
137 IF p_truncate_flg = 'Y' THEN
138
139 l_load_type := 'FIRST_LOAD';
140
141 l_sysdate := sysdate;
142
143 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
144 ,p_end_date => l_sysdate
145 ,p_api_version_number => l_api_version_number
146 ,p_init_msg_list => FND_API.G_FALSE
147 ,x_msg_count => x_msg_count
148 ,x_msg_data => x_msg_data
149 ,x_return_status => x_return_status
150 );
151
152 ELSE
153
154 --- Validate Time Dimension Tables
155
156 fii_time_api.check_missing_date (l_end_date, sysdate, l_missing_date);
157
158 IF (l_missing_date) THEN
159
160 --Check it from the year start Date of the Year Passed as used in Active Customer Count
161 bis_collection_utilities.log('Time Dimension has atleast one missing date between ' || l_end_date || ' and ' || sysdate);
162 RAISE FND_API.G_EXC_ERROR;
163
164 END IF;
165
166 l_load_type := 'SUBSEQUENT_LOAD';
167
168 l_sysdate := sysdate;
169
170
171 INCREMENTAL_LOAD(p_start_date => l_end_date +1/86400 -- add one second
172 ,p_end_date => l_sysdate
173 ,p_api_version_number => l_api_version_number
174 ,p_init_msg_list => FND_API.G_FALSE
175 ,x_msg_count => x_msg_count
176 ,x_msg_data => x_msg_data
177 ,x_return_status => x_return_status
178 );
179 END IF;
180
181 END IF;
182
183 ---If Incremental/Inital Load Executes Sucessfully, then call the Load_Schedule_Data Procedure
184
185 IF x_return_status = FND_API.g_ret_sts_error THEN
186
187 RAISE FND_API.g_exc_error;
188
189 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
190
191 RAISE FND_API.g_exc_unexpected_error;
192
193 END IF;
194
195 --Standard check of commit
196
197 IF FND_API.To_Boolean ( p_commit ) THEN
198
199 COMMIT WORK;
200
201 END IF;
202
203 bis_collection_utilities.log('Successful Completion of Segment Customer Facts Program');
204
205 -- Standard call to get message count and if count is 1, get message info.
206 FND_MSG_PUB.Count_And_Get
207 (p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210
211 EXCEPTION
212
213 WHEN FND_API.G_EXC_ERROR THEN
214
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 -- Standard call to get message count and if count=1, get the message
217 FND_msg_PUB.Count_And_Get (
218 p_count => x_msg_count,
219 p_data => x_msg_data
220 );
221
222
223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
224
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 -- Standard call to get message count and if count=1, get the message
227 FND_msg_PUB.Count_And_Get (
228 p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231
232 WHEN OTHERS THEN
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
235 THEN
236 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
237 END IF;
238 -- Standard call to get message count and if count=1, get the message
239 FND_msg_PUB.Count_And_Get (
240 p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243
244 END populate;
245
246
247 --------------------------------------------------------------------------------------------------
248 -- This procedure will populates all the data required into Party Segment facts
249 -- table for the first load.
250 -- PROCEDURE FIRST_LOAD
251 --------------------------------------------------------------------------------------------------
252
253 PROCEDURE FIRST_LOAD
254 ( p_start_date IN DATE
255 ,p_end_date IN DATE
256 ,p_api_version_number IN NUMBER
257 ,p_init_msg_list IN VARCHAR2
258 ,x_msg_count OUT NOCOPY NUMBER
259 ,x_msg_data OUT NOCOPY VARCHAR2
260 ,x_return_status OUT NOCOPY VARCHAR2
261 )
262 IS
263
264 l_api_version_number CONSTANT NUMBER := 1.0;
265 l_api_name CONSTANT VARCHAR2(30) := 'FIRST_LOAD';
266 l_table_name VARCHAR2(100);
267 l_check_missing_rate NUMBER;
268 l_return BOOLEAN;
269
270 l_status VARCHAR2(5);
271 l_industry VARCHAR2(5);
272 l_schema VARCHAR2(30);
273
274 l_source VARCHAR2(80);
275 l_sql_id NUMBER(32);
276 l_query_string VARCHAR2(32767);
277
278 cursor c_query_source is
279 select list_query_id, query
280 from ams_list_queries_all ;
281
282 l_found VARCHAR2(1) := 'N';
283 l_master_type VARCHAR2(80);
284 l_master_type_id NUMBER;
285 l_source_object_name VARCHAR2(80);
286 l_source_object_pk_field VARCHAR2(80);
287 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
288 l_from_position NUMBER;
289 l_from_counter NUMBER;
290 l_end_position NUMBER;
291 l_end_counter NUMBER;
292 l_count NUMBER;
293 l_string_copy VARCHAR2(32767);
294 l_length NUMBER;
295
296 BEGIN
297
298 Execute Immediate 'CREATE TABLE ' || ' source_query_scfb ' || '( sql_id NUMBER(32), source_name VARCHAR2(80) )';
299
300 Execute Immediate 'TRUNCATE TABLE source_query_scfb';
301
302 OPEN c_query_source;
303 LOOP
304 FETCH c_query_source INTO l_sql_id, l_query_string;
305 EXIT WHEN c_query_source%notfound;
306
307 if ( l_query_string is NULL ) then
308 l_source := 'NO_MASTER_TYPE';
309 else
310 l_count := 0;
311 l_string_copy := l_query_string;
312
313 l_length := length(l_string_copy);
314
315 LOOP
316 l_count := l_count + 1;
317 IF l_length < 1999 THEN
318 l_sql_string_tbl(l_count) := l_string_copy;
319 EXIT;
320 ELSE
321 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
322 l_string_copy := substr(l_string_copy, 2000);
323 END IF;
324 l_length := length(l_string_copy);
325 END LOOP;
326
327 l_found := 'N';
328 AMS_ListGeneration_PKG.validate_sql_string(
329 p_sql_string => l_sql_string_tbl ,
330 p_search_string => 'FROM',
331 p_comma_valid => 'N',
332 x_found => l_found,
333 x_position => l_from_position,
334 x_counter => l_from_counter) ;
335
336
337 l_found := 'N';
338
339 AMS_ListGeneration_PKG.get_master_types (
340 p_sql_string => l_sql_string_tbl,
341 p_start_length => 1,
342 p_start_counter => 1,
343 p_end_length => l_from_position,
344 p_end_counter => l_from_counter,
345 x_master_type_id=> l_master_type_id,
346 x_master_type=> l_master_type,
347 x_found=> l_found,
348 x_source_object_name => l_source_object_name,
349 x_source_object_pk_field => l_source_object_pk_field);
350
351 IF nvl(l_found,'N') = 'N' THEN
352 --No master type.
353 l_source_object_name := 'NO_MASTER_TYPE';
354 END IF;
355
356
357 l_source := l_master_type;
358 END IF;
359 -- bis_collection_utilities.log('running the function --- '||l_source||l_master_type_id||l_master_type);
360 Execute Immediate 'INSERT INTO source_query_scfb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';
361 l_source := NULL;
362 l_query_string := NULL;
363 l_sql_id := NULL;
364 END LOOP;
365 CLOSE c_query_source;
366
367
368 -- Standard call to check for call compatibility.
369 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
370 p_api_version_number,
371 l_api_name,
372 g_pkg_name)
373 THEN
374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
375 END IF;
376
377 -- Initialize message list if p_init_msg_list is set to TRUE.
378 IF fnd_api.to_boolean( p_init_msg_list ) THEN
379
380 FND_msg_PUB.initialize;
381
382 END IF;
383
384 -- Initialize API return status to SUCCESS
385 x_return_status := FND_API.G_RET_STS_SUCCESS;
386
387 bis_collection_utilities.log('Running Initial Load of Segment Customer Facts');
388
389 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
390
391 bis_collection_utilities.log('Truncating Facts Table');
392
393 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_party_sgmt_facts' ;
394
395 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SGMT_CUST');
396
397 ---populating Segment Size
398 bis_collection_utilities.log('Inserting Segment Customer Details');
399
400 Execute Immediate 'INSERT/*+ append parallel*/ INTO bim_i_party_sgmt_facts ' ||
401 ' (segment_id ' ||
402 ' ,party_id ' ||
403 ' ,start_date_active ' ||
404 ' ,end_date_active ' ||
405 ' ,status,account_close_date ' ||
406 ' ,creation_date ' ||
407 ' ,last_update_date ' ||
408 ' ,created_by ' ||
409 ' ,last_updated_by ' ||
410 ' ,last_update_login ' ||
411 ' ) ' ||
412 ' SELECT segment_id ' ||
413 ' ,inner.party_id ' ||
414 ' ,TRUNC(start_date_active) ' ||
415 ' ,TRUNC(end_date_active) ' ||
416 ' ,party.status status ' ||
417 ' ,CASE WHEN party.status=''I'' AND party.last_update_date BETWEEN start_date_active and end_date_active THEN ' ||
418 ' party.last_update_date ' ||
419 ' ELSE ' ||
420 ' NULL ' ||
421 ' END account_close_date ' ||
422 ' ,sysdate ' ||
423 ' ,sysdate ' ||
424 ' ,-1 ' ||
425 ' ,-1 ' ||
426 ' ,-1 ' ||
427 ' FROM ' ||
428 ' (SELECT market_segment_id segment_id ' ||
429 ' ,nvl(reln.subject_id,a.party_id) party_id ' ||
430 ' ,min(start_date_active) start_date_active ' ||
431 ' ,max(nvl(end_date_active,TO_DATE(''31/12/2199'',''DD/MM/YYYY''))) end_date_active ' ||
432 ' FROM ams_party_market_segments a ' ||
433 ' ,(SELECT a.cell_id cell_id ' ||
434 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_scfb d ' ||
435 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
436 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
437 ' AND b.list_query_id = d.sql_id ' ||
438 ' AND d.source_name = c.source_type_code ' ||
439 ' AND c.based_on_tca_flag = ''Y'' ' ||
440 ' AND a.sel_type =''SQL'' ' ||
441 ' AND a.creation_date >= '''||p_start_date||''''||
442 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
443 ' UNION ALL ' ||
444 ' SELECT a.cell_id ' ||
445 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
446 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
447 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
448 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
449 ' AND d.source_type_code = b.source_object_name ' ||
450 ' AND b.based_on_tca_flag = ''Y'' ' ||
451 ' AND a.sel_type=''DIWB'' ' ||
452 ' AND a.creation_date >= '''||p_start_date||''''||
453 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
454 ' ) b ' ||
455 ' , hz_relationships reln ' ||
456 ' WHERE a.market_segment_id = b.cell_id ' ||
457 ' AND trunc(a.creation_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
458 ' AND a.party_id = reln.party_id(+) ' ||
459 ' AND reln.subject_type(+) =''ORGANIZATION'' ' ||
460 ' AND object_type(+)=''PERSON'' ' ||
461 ' GROUP BY nvl(reln.subject_id,a.party_id),a.market_segment_id) inner,hz_parties party ' ||
462 ' WHERE inner.party_id=party.party_id';
463
464 Execute Immediate ' DROP TABLE source_query_scfb ';
465
466 COMMIT;
467
468 UPDATE bim_i_party_sgmt_facts a
469 SET account_open_date=(SELECT GREATEST(min(TRUNC(creation_date)),trunc(a.start_date_active))
470 FROM hz_cust_accounts b
471 WHERE a.party_id=b.party_id)
472 WHERE EXISTS
473 ( SELECT 1
474 FROM hz_cust_accounts b
475 WHERE a.party_id=b.party_id);
476
477
478 commit;
479 bis_collection_utilities.wrapup(p_status => TRUE
480 ,p_count => sql%rowcount
481 ,p_period_from => p_start_date
482 ,p_period_to => p_end_date
483 ,p_attribute1 => 'INITIAL_LOAD_START_DATE'
484 ,p_attribute2 => p_start_date
485 );
486
487 /***************************************************************/
488
489 EXCEPTION
490
491 WHEN FND_API.G_EXC_ERROR THEN
492 x_return_status := FND_API.G_RET_STS_ERROR;
493 Execute Immediate 'DROP TABLE source_query_scfb';
494 -- Standard call to get message count and if count=1, get the message
495 FND_msg_PUB.Count_And_Get (
496 -- p_encoded => FND_API.G_FALSE,
497 p_count => x_msg_count,
498 p_data => x_msg_data
499 );
500
501 bis_collection_utilities.log('BIM_I_SGMT_CUST_FACTS_PKG : FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
502
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 Execute Immediate 'DROP TABLE source_query_scfb';
507
508 -- Standard call to get message count and if count=1, get the message
509 FND_msg_PUB.Count_And_Get (
510 --p_encoded => FND_API.G_FALSE,
511 p_count => x_msg_count,
512 p_data => x_msg_data
513 );
514
515 bis_collection_utilities.log('BIM_I_SGMT_CUST_FACTS_PKG : FIRST_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
516
517 WHEN OTHERS THEN
518
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
520 Execute Immediate 'DROP TABLE source_query_scfb';
521
522 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
523 THEN
524 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
525 END IF;
526
527 -- Standard call to get message count and if count=1, get the message
528 FND_msg_PUB.Count_And_Get (
529 -- p_encoded => FND_API.G_FALSE,
530 p_count => x_msg_count,
531 p_data => x_msg_data
532 );
533
534 bis_collection_utilities.log('BIM_I_SGMT_CUST_FACTS_PKG : FIRST_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
535
536 END first_load;
537
538 --------------------------------------------------------------------------------------------------
539 -- This procedure will populates all the data required into facts table for incremental load.
540 --
541 -- PROCEDURE INCREMENTAL_LOAD
542 --------------------------------------------------------------------------------------------------
543
544 PROCEDURE INCREMENTAL_LOAD
545 ( p_start_date IN DATE
546 ,p_end_date IN DATE
547 ,p_api_version_number IN NUMBER
548 ,p_init_msg_list IN VARCHAR2
549 ,x_msg_count OUT NOCOPY NUMBER
550 ,x_msg_data OUT NOCOPY VARCHAR2
551 ,x_return_status OUT NOCOPY VARCHAR2
552 )
553 IS
554 l_user_id NUMBER := FND_GLOBAL.USER_ID();
555 l_api_version_number CONSTANT NUMBER := 1.0;
556 l_api_name CONSTANT VARCHAR2(30) := 'INCREMENTAL_LOAD';
557 l_table_name VARCHAR2(100);
558 l_conv_opp_status VARCHAR2(30);
559 l_dead_status VARCHAR2(30);
560 l_check_missing_rate NUMBER;
561 l_stmt VARCHAR2(50);
562 l_cert_level VARCHAR2(3);
563
564 l_return BOOLEAN;
565 l_status VARCHAR2(5);
566 l_industry VARCHAR2(5);
567 l_schema VARCHAR2(30);
568
569 l_source VARCHAR2(80);
570 l_sql_id NUMBER(32);
571 l_query_string VARCHAR2(32767);
572
573 cursor c_query_source is
574 select list_query_id, query
575 from ams_list_queries_all ;
576
577 l_found VARCHAR2(1) := 'N';
578 l_master_type VARCHAR2(80);
579 l_master_type_id NUMBER;
580 l_source_object_name VARCHAR2(80);
581 l_source_object_pk_field VARCHAR2(80);
582 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
583 l_from_position NUMBER;
584 l_from_counter NUMBER;
585 l_end_position NUMBER;
586 l_end_counter NUMBER;
587 l_count NUMBER;
588 l_string_copy VARCHAR2(32767);
589 l_length NUMBER;
590
591 BEGIN
592
593 Execute Immediate 'CREATE TABLE ' || ' source_query_scfb ' || '( sql_id NUMBER(32), source_name VARCHAR2(80) )';
594
595 Execute Immediate 'TRUNCATE TABLE source_query_scfb';
596
597 OPEN c_query_source;
598 LOOP
599 FETCH c_query_source INTO l_sql_id, l_query_string;
600 EXIT WHEN c_query_source%notfound;
601
602 if ( l_query_string is NULL ) then
603 l_source := 'NO_MASTER_TYPE';
604 else
605 l_count := 0;
606 l_string_copy := l_query_string;
607
608 l_length := length(l_string_copy);
609
610 LOOP
611 l_count := l_count + 1;
612 IF l_length < 1999 THEN
613 l_sql_string_tbl(l_count) := l_string_copy;
614 EXIT;
615 ELSE
616 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
617 l_string_copy := substr(l_string_copy, 2000);
618 END IF;
619 l_length := length(l_string_copy);
620 END LOOP;
621
622 l_found := 'N';
623 AMS_ListGeneration_PKG.validate_sql_string(
624 p_sql_string => l_sql_string_tbl ,
625 p_search_string => 'FROM',
626 p_comma_valid => 'N',
627 x_found => l_found,
628 x_position => l_from_position,
629 x_counter => l_from_counter) ;
630
631
632 l_found := 'N';
633
634 AMS_ListGeneration_PKG.get_master_types (
635 p_sql_string => l_sql_string_tbl,
636 p_start_length => 1,
637 p_start_counter => 1,
638 p_end_length => l_from_position,
639 p_end_counter => l_from_counter,
640 x_master_type_id=> l_master_type_id,
641 x_master_type=> l_master_type,
642 x_found=> l_found,
643 x_source_object_name => l_source_object_name,
644 x_source_object_pk_field => l_source_object_pk_field);
645
646 IF nvl(l_found,'N') = 'N' THEN
647 --No master type.
648 l_source_object_name := 'NO_MASTER_TYPE';
649 END IF;
650
651
652 l_source := l_master_type;
653 END IF;
654 -- bis_collection_utilities.log('running the function --- '||l_source||l_master_type_id||l_master_type);
655 Execute Immediate 'INSERT INTO source_query_scfb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';
656 l_source := NULL;
657 l_query_string := NULL;
658 l_sql_id := NULL;
659 END LOOP;
660 CLOSE c_query_source;
661
662
663 -- Standard call to check for call compatibility.
664 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
665 p_api_version_number,
666 l_api_name,
667 g_pkg_name)
668 THEN
669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
670 END IF;
671
672 -- Initialize message list if p_init_msg_list is set to TRUE.
673 IF FND_API.to_Boolean( p_init_msg_list ) THEN
674
675 FND_msg_PUB.initialize;
676
677 END IF;
678
679 -- Initialize API return status to SUCCESS
680 x_return_status := FND_API.G_RET_STS_SUCCESS;
681
682
683 bis_collection_utilities.log('Running Incremental Load of Segment Customer Facts');
684
685 bis_collection_utilities.log('Inserting Segment Customer Details');
686
687 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
688
689 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_party_sgmt_stg';
690
691 --SQL TO COLLECT PARTIES AND SEGMENT COMBINATION WHICH IS PRESENT IN FACT
692 --TABLE AND POPULATE THIS DATA IN STAGING TABLE
693
694 Execute Immediate 'INSERT /*+Append parallel*/ INTO bim_i_party_sgmt_stg ' ||
695 ' ( ' ||
696 ' segment_id ' ||
697 ' ,party_id ' ||
698 ' ,end_date_active ' ||
699 ' ,status ' ||
700 ' ,account_close_date ' ||
701 ' ) ' ||
702 ' SELECT ' ||
703 ' stg.segment_id segment_id ' ||
704 ' ,stg.party_id party_id ' ||
705 ' ,TRUNC(max(segs_party.end_date_active)) end_date_active ' ||
706 ' ,parties.status status ' ||
707 ' ,(CASE WHEN parties.status IN (''I'',''A'') THEN TRUNC(parties.last_update_date) ELSE NULL END) account_closure_date ' ||
708 ' FROM ams_party_market_segments a ' ||
709 ' ,(SELECT a.cell_id ' ||
710 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_scfb d ' ||
711 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
712 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
713 ' AND b.list_query_id = d.sql_id ' ||
714 ' AND d.source_name = c.source_type_code ' ||
715 ' AND c.based_on_tca_flag = ''Y'' ' ||
716 ' AND a.sel_type =''SQL'' ' ||
717 ' AND a.creation_date >= '''||g_initial_start_date||''''||
718 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
719 ' UNION ALL ' ||
720 ' SELECT a.cell_id ' ||
721 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
722 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
723 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
724 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
725 ' AND d.source_type_code = b.source_object_name ' ||
726 ' AND b.based_on_tca_flag = ''Y'' ' ||
727 ' AND a.sel_type=''DIWB'' ' ||
728 ' AND a.creation_date >= '''||g_initial_start_date||''''||
729 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
730 ' ) b ' ||
731 ' ,hz_relationships reln ' ||
732 ' ,bim_i_party_sgmt_facts stg ' ||
733 ' ,hz_relationships reln2 ' ||
734 ' ,ams_party_market_segments segs_party ' ||
735 ' ,hz_parties parties ' ||
736 ' WHERE a.market_segment_id=b.cell_id ' ||
737 ' AND stg.party_id=nvl(reln.subject_id,a.party_id) ' ||
738 ' AND stg.segment_id=a.market_segment_id ' ||
739 ' AND a.party_id=reln.party_id(+) ' ||
740 ' AND reln.subject_type(+) =''ORGANIZATION'' ' ||
741 ' AND reln.object_type(+)=''PERSON'' ' ||
742 ' AND reln.relationship_code(+)=''CONTACT'' ' ||
743 ' AND a.party_id=reln.party_id(+) ' ||
744 ' AND reln2.subject_type(+) =''PERSON'' ' ||
745 ' AND reln2.object_type(+)=''ORGANIZATION'' ' ||
746 ' AND reln2.relationship_code(+)=''CONTACT_OF'' ' ||
747 ' AND reln2.object_id(+)=stg.party_id ' ||
748 ' AND ((segs_party.party_id=reln2.party_id) or (segs_party.party_id=stg.party_id)) ' ||
749 ' AND segs_party.market_segment_id=stg.segment_id ' ||
750 ' AND stg.party_id=parties.party_id ' ||
751 ' AND trunc(a.last_update_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
752 ' GROUP BY stg.segment_id,stg.party_id,parties.status,parties.last_update_date' ;
753
754
755
756 COMMIT;
757
758 --UPDATE STATEMENT FOR END_DATE_ACTIVE IF END_DATE_ACTIVE IS DIFFRENT FROM LAST RUN
759
760 UPDATE bim_i_party_sgmt_facts facts
761 SET end_date_Active = ( SELECT TRUNC(end_date_active)
762 FROM bim_i_party_sgmt_stg stg
763 WHERE facts.segment_id=stg.segment_id
764 AND facts.party_id = stg.party_id
765 )
766 WHERE EXISTS (SELECT 1
767 FROM bim_i_party_sgmt_stg stg
768 WHERE facts.segment_id=stg.segment_id
769 AND facts.party_id = stg.party_id
770 AND facts.end_date_active<>stg.end_date_active
771 ) ;
772
773 --UPDATE STATEMENT FOR ACCOUNT_CLOSURE_DATE IF ACCOUNT_CLOSURE_DATE IS DIFFRENT FROM LAST RUN
774
775 UPDATE bim_i_party_sgmt_facts facts
776 SET account_close_date = ( SELECT TRUNC(account_close_date)
777 FROM bim_i_party_sgmt_stg stg
778 WHERE facts.segment_id=stg.segment_id
779 AND facts.party_id = stg.party_id
780 )
781 WHERE EXISTS (SELECT 1
782 FROM bim_i_party_sgmt_stg stg
783 WHERE facts.segment_id=stg.segment_id
784 AND facts.party_id = stg.party_id
785 AND facts.account_close_date <> stg.account_close_date
786 );
787
788 --Insert rows for newly created segment and party combination
789
790 Execute Immediate ' INSERT /*+ append parallel */INTO bim_i_party_sgmt_facts ' ||
791 ' (segment_id ' ||
792 ' ,party_id ' ||
793 ' ,start_date_active ' ||
794 ' ,end_date_active ' ||
795 ' ,status ' ||
796 ' ,account_close_date ' ||
797 ' ,creation_date ' ||
798 ' ,last_update_date ' ||
799 ' ,created_by ' ||
800 ' ,last_updated_by ' ||
801 ' ,last_update_login) ' ||
802 ' SELECT ' ||
803 ' market_segment_id segment_id ' ||
804 ' ,nvl(reln.subject_id,a.party_id) party_id ' ||
805 ' ,trunc(min(start_date_active)) start_date_active ' ||
806 ' ,trunc(max(nvl(end_date_active,TO_DATE(''31/12/2199'',''DD/MM/YYYY'')))) end_date_active ' ||
807 ' ,parties.status status ' ||
808 ' ,CASE WHEN parties.status=''I'' then parties.last_update_date ELSE NULL END ' ||
809 ' ,sysdate ' ||
810 ' ,sysdate ' ||
811 ' ,-1 ' ||
812 ' ,-1 ' ||
813 ' ,-1 ' ||
814 ' FROM ams_party_market_segments a ' ||
815 ' ,(SELECT a.cell_id ' ||
816 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_scfb d ' ||
817 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
818 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
819 ' AND b.list_query_id = d.sql_id ' ||
820 ' AND d.source_name = c.source_type_code ' ||
821 ' AND c.based_on_tca_flag = ''Y'' ' ||
822 ' AND a.sel_type =''SQL'' ' ||
823 ' AND a.creation_date >= '''||g_initial_start_date||''''||
824 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
825 ' UNION ALL ' ||
826 ' SELECT a.cell_id ' ||
827 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
828 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
829 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
830 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
831 ' AND d.source_type_code = b.source_object_name ' ||
832 ' AND b.based_on_tca_flag = ''Y'' ' ||
833 ' AND a.sel_type=''DIWB'' ' ||
834 ' AND a.creation_date >= '''||g_initial_start_date||''''||
835 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
836 ' ) b ' ||
837 ' ,hz_relationships reln ' ||
838 ' ,hz_parties parties ' ||
839 ' WHERE a.market_segment_id=b.cell_id ' ||
840 ' AND a.party_id=reln.party_id(+) ' ||
841 ' AND reln.subject_type(+) =''ORGANIZATION'' ' ||
842 ' AND OBJECT_TYPE(+)=''PERSON'' ' ||
843 ' AND nvl(reln.subject_id,a.party_id)=parties.party_id ' ||
844 ' AND trunc(a.last_update_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
845 ' AND NOT EXISTS ( SELECT 1 ' ||
846 ' FROM bim_i_party_sgmt_stg stg ' ||
847 ' WHERE stg.segment_id=a.market_segment_id ' ||
848 ' AND nvl(reln.subject_id,a.party_id)=stg.party_id ' ||
849 ' ) ' ||
850 ' GROUP BY nvl(reln.subject_id,a.party_id),a.market_segment_id,parties.status,parties.last_update_date ' ;
851
852 Execute Immediate ' DROP TABLE source_query_scfb ';
853
854 COMMIT;
855
856 --update account creation date for those parties which does not have acoounts(in case of prospect)
857
858 UPDATE bim_i_party_sgmt_facts a
859 SET account_open_date=(SELECT GREATEST(min(TRUNC(creation_date)),trunc(a.start_date_active))
860 FROM hz_cust_accounts b
861 WHERE a.party_id=b.party_id
862 AND b.creation_date >= g_initial_start_date)
863 WHERE EXISTS( SELECT 1
864 FROM hz_cust_accounts b
865 WHERE a.party_id=b.party_id
866 AND b.creation_date >= g_initial_start_date)
867 AND account_open_date IS NULL;
868
869
870 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_party_sgmt_stg';
871
872 bis_collection_utilities.wrapup(p_status => TRUE
873 ,p_count => sql%rowcount
874 ,p_period_from => p_start_date
875 ,p_period_to => p_end_date
876 ,p_attribute1 => 'INITIAL_LOAD_START_DATE'
877 ,p_attribute2 =>g_initial_start_date
878 );
879
880 COMMIT;
881
882 EXCEPTION
883
884 WHEN FND_API.G_EXC_ERROR THEN
885 x_return_status := FND_API.G_RET_STS_ERROR;
886 Execute Immediate 'DROP TABLE source_query_scfb';
887 -- Standard call to get message count and if count=1, get the message
888 FND_msg_PUB.Count_And_Get (
889 -- p_encoded => FND_API.G_FALSE,
890 p_count => x_msg_count,
891 p_data => x_msg_data
892 );
893
894 bis_collection_utilities.log('BIM_I_SGMT_CUST_FACTS_PKG:INCREMENTAL_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
895
896 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
897
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899 Execute Immediate 'DROP TABLE source_query_scfb';
900
901 -- Standard call to get message count and if count=1, get the message
902 FND_msg_PUB.Count_And_Get (
903 --p_encoded => FND_API.G_FALSE,
904 p_count => x_msg_count,
905 p_data => x_msg_data
906 );
907
908 bis_collection_utilities.log('BIM_I_SGMT_CUST_FACTS_PKG:INCREMENTAL_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
909
910 WHEN OTHERS THEN
911
912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913 Execute immediate 'DROP TABLE source_query_scfb';
914
915 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
916 THEN
917 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
918 END IF;
919
920 -- Standard call to get message count and if count=1, get the message
921 FND_msg_PUB.Count_And_Get (
922 -- p_encoded => FND_API.G_FALSE,
923 p_count => x_msg_count,
924 p_data => x_msg_data
925 );
926
927 bis_collection_utilities.log('BIM_I_SGMT_CUST_FACTS_PKG:INCREMENTAL_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
928
929
930 END incremental_load;
931
932
933 END BIM_I_SGMT_CUST_FACTS_PKG;
934