[Home] [Help]
PACKAGE BODY: APPS.BIM_I_SGMT_FACTS_PKG
Source
1 PACKAGE BODY BIM_I_SGMT_FACTS_PKG AS
2 /*$Header: bimisgfb.pls 120.23.12010000.1 2008/07/29 21:04:47 appldev ship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_I_SGMT_FACTS_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimisgfb.pls';
6 g_initial_start_date DATE ;
7
8
9
10 ------------------------------------------------------------------------------------------------
11 ----
12 ----This procedure finds out if the user is trying to run first_load or subsequent load
13 ----and calls the load_data procedure with the specific parameters to each type of load
14 ----
15 ------------------------------------------------------------------------------------------------
16
17 PROCEDURE POPULATE
18 (
19 p_api_version_number IN NUMBER
20 ,p_init_msg_list IN VARCHAR2
21 ,p_validation_level IN NUMBER
22 ,p_commit IN VARCHAR2
23 ,x_msg_count OUT NOCOPY NUMBER
24 ,x_msg_data OUT NOCOPY VARCHAR2
25 ,x_return_status OUT NOCOPY VARCHAR2
26 ,p_start_date IN DATE
27 ,p_end_date IN DATE
28 ,p_para_num IN NUMBER
29 ,p_truncate_flg IN VARCHAR2
30 ) IS
31
32 l_object_name CONSTANT VARCHAR2(80) := 'BIM_SGMT_SIZE';
33 l_conc_start_date DATE;
34 l_conc_end_date DATE;
35 l_start_date DATE;
36 l_end_date DATE;
37 l_user_id NUMBER := FND_GLOBAL.USER_ID();
38 l_api_version_number CONSTANT NUMBER := 1.0;
39 l_api_name CONSTANT VARCHAR2(30) := 'BIM_I_SGMT_FACTS_PKG';
40 l_mesg_text VARCHAR2(100);
41 l_load_type VARCHAR2(100);
42 l_year_start_date DATE;
43 l_global_date DATE;
44 l_missing_date BOOLEAN := FALSE;
45 l_sysdate DATE;
46
47 l_attribute_table DBMS_SQL.VARCHAR2_TABLE;
48 l_attribute_count NUMBER;
49
50 BEGIN
51
52 IF NOT bis_collection_utilities.setup(l_object_name) THEN
53 bis_collection_utilities.log('Object BIM_SGMT_SIZE Not Setup Properly');
54 RAISE FND_API.G_EXC_ERROR;
55 END IF;
56
57 bis_collection_utilities.log('Start of the Segment Base Summary Program');
58
59 -- Standard call to check for call compatibility.
60 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
61 p_api_version_number,
62 l_api_name,
63 g_pkg_name) THEN
64
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66
67 END IF;
68
69 -- Initialize message list if p_init_msg_list is set to TRUE.
70 IF FND_API.to_Boolean( p_init_msg_list )
71 THEN
72 FND_MSG_PUB.initialize;
73 END IF;
74
75 l_global_date:= bis_common_parameters.get_global_start_date;
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 First Time Base Summary 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
122 l_load_type := 'FIRST_LOAD';
123
124
125 l_sysdate := sysdate;
126
127 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
128 ,p_end_date => l_sysdate
129 ,p_api_version_number => l_api_version_number
130 ,p_init_msg_list => FND_API.G_FALSE
131 ,x_msg_count => x_msg_count
132 ,x_msg_data => x_msg_data
133 ,x_return_status => x_return_status
134 );
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 l_sysdate := sysdate;
143
144 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
145 ,p_end_date => l_sysdate
146 ,p_api_version_number => l_api_version_number
147 ,p_init_msg_list => FND_API.G_FALSE
148 ,x_msg_count => x_msg_count
149 ,x_msg_data => x_msg_data
150 ,x_return_status => x_return_status
151 );
152 ELSE
153 --- Validate Time Dimension Tables
154
155 SELECT ent_year_start_date
156 INTO l_year_start_date
157 FROM fii_Time_Day
158 WHERE report_date = trunc(l_end_date);
159
160 IF l_year_start_date IS NULL THEN
161
162 bis_collection_utilities.log('Time Dimension Not Defined Till '||l_end_date );
163 RAISE FND_API.G_EXC_ERROR;
164
165 END IF;
166
167 fii_time_api.check_missing_date (l_year_start_date, sysdate, l_missing_date);
168
169 IF (l_missing_date) THEN
170
171 --Check it from the year start Date of the Year Passed as used in Active Customer Count
172 bis_collection_utilities.log('Time Dimension has atleast one missing date between ' || l_end_date || ' and ' || sysdate);
173 RAISE FND_API.G_EXC_ERROR;
174
175 END IF;
176
177 l_load_type := 'SUBSEQUENT_LOAD';
178
179 l_sysdate := sysdate;
180
181 INCREMENTAL_LOAD(p_start_date => l_end_date +1/86400 -- add one second
182 ,p_end_date => l_sysdate
183 ,p_year_start_date =>l_year_start_date
184 ,p_api_version_number => l_api_version_number
185 ,p_init_msg_list => FND_API.G_FALSE
186 ,x_msg_count => x_msg_count
187 ,x_msg_data => x_msg_data
188 ,x_return_status => x_return_status
189 );
190 END IF;
191
192 END IF;
193
194 IF x_return_status = FND_API.g_ret_sts_error THEN
195
196 RAISE FND_API.g_exc_error;
197
198 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
199
200 RAISE FND_API.g_exc_unexpected_error;
201
202 END IF;
203
204 --Standard check of commit
205
206 IF FND_API.To_Boolean ( p_commit ) THEN
207
208 COMMIT WORK;
209
210 END IF;
211
212
213 bis_collection_utilities.log('Successful Completion of Segment Base Summary Program');
214
215 -- Standard call to get message count and if count is 1, get message info.
216 FND_MSG_PUB.Count_And_Get
217 (p_count => x_msg_count,
218 p_data => x_msg_data
219 );
220
221 EXCEPTION
222
223 WHEN FND_API.G_EXC_ERROR THEN
224
225 x_return_status := FND_API.G_RET_STS_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
233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
234
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 -- Standard call to get message count and if count=1, get the message
237 FND_msg_PUB.Count_And_Get (
238 p_count => x_msg_count,
239 p_data => x_msg_data
240 );
241
242 WHEN OTHERS THEN
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
244 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
245 THEN
246 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
247 END IF;
248 -- Standard call to get message count and if count=1, get the message
249 FND_msg_PUB.Count_And_Get (
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253
254 END populate;
255
256
257 --------------------------------------------------------------------------------------------------
258 -- This procedure will populate all the data required into Segment facts table for the first load.
259 --
260 -- PROCEDURE FIRST_LOAD
261 --------------------------------------------------------------------------------------------------
262
263 PROCEDURE FIRST_LOAD
264 ( p_start_date IN DATE
265 ,p_end_date IN DATE
266 ,p_api_version_number IN NUMBER
267 ,p_init_msg_list IN VARCHAR2
268 ,x_msg_count OUT NOCOPY NUMBER
269 ,x_msg_data OUT NOCOPY VARCHAR2
270 ,x_return_status OUT NOCOPY VARCHAR2
271 )
272 IS
273
274 l_api_version_number CONSTANT NUMBER := 1.0;
275 l_api_name CONSTANT VARCHAR2(30) := 'FIRST_LOAD';
276 l_table_name VARCHAR2(100);
277 l_check_missing_rate NUMBER;
278 l_return BOOLEAN;
279
280 l_sysdate DATE;
281
282 l_status VARCHAR2(5);
283 l_industry VARCHAR2(5);
284 l_schema VARCHAR2(30);
285
286 l_source VARCHAR2(80);
287 l_sql_id NUMBER(32);
288 l_query_string VARCHAR2(32767);
289
290 cursor c_query_source is
291 select list_query_id, query
292 from ams_list_queries_all ;
293
294 l_found VARCHAR2(1) := 'N';
295 l_master_type VARCHAR2(80);
296 l_master_type_id NUMBER;
297 l_source_object_name VARCHAR2(80);
298 l_source_object_pk_field VARCHAR2(80);
299 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
300 l_from_position NUMBER;
301 l_from_counter NUMBER;
302 l_end_position NUMBER;
303 l_end_counter NUMBER;
304 l_count NUMBER;
305 l_string_copy VARCHAR2(32767);
306 l_length NUMBER;
307
308 BEGIN
309
310 Execute Immediate 'CREATE TABLE ' || ' source_query_sgfb ' || '( sql_id NUMBER(32), source_name VARCHAR2(80) )';
311
312 Execute Immediate 'TRUNCATE TABLE source_query_sgfb';
313
314 OPEN c_query_source;
315 LOOP
316 FETCH c_query_source INTO l_sql_id, l_query_string;
317 EXIT WHEN c_query_source%notfound;
318
319 if ( l_query_string is NULL ) then
320 l_source := 'NO_MASTER_TYPE';
321 else
322 l_count := 0;
323 l_string_copy := l_query_string;
324
325 l_length := length(l_string_copy);
326
327 LOOP
328 l_count := l_count + 1;
329 IF l_length < 1999 THEN
330 l_sql_string_tbl(l_count) := l_string_copy;
331 EXIT;
332 ELSE
333 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
334 l_string_copy := substr(l_string_copy, 2000);
335 END IF;
336 l_length := length(l_string_copy);
337 END LOOP;
338
339 l_found := 'N';
340 AMS_ListGeneration_PKG.validate_sql_string(
341 p_sql_string => l_sql_string_tbl ,
342 p_search_string => 'FROM',
343 p_comma_valid => 'N',
344 x_found => l_found,
345 x_position => l_from_position,
346 x_counter => l_from_counter) ;
347
348
349 l_found := 'N';
350
351 AMS_ListGeneration_PKG.get_master_types (
352 p_sql_string => l_sql_string_tbl,
353 p_start_length => 1,
354 p_start_counter => 1,
355 p_end_length => l_from_position,
356 p_end_counter => l_from_counter,
357 x_master_type_id=> l_master_type_id,
358 x_master_type=> l_master_type,
359 x_found=> l_found,
360 x_source_object_name => l_source_object_name,
361 x_source_object_pk_field => l_source_object_pk_field);
362
363 IF nvl(l_found,'N') = 'N' THEN
364 --No master type.
365 l_source_object_name := 'NO_MASTER_TYPE';
366 END IF;
367
368
369 l_source := l_master_type;
370 END IF;
371 -- bis_collection_utilities.log('running the function --- '||l_source||l_master_type_id||l_master_type);
372 Execute Immediate 'INSERT INTO source_query_sgfb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';
373 l_source := NULL;
374 l_query_string := NULL;
375 l_sql_id := NULL;
376 END LOOP;
377 CLOSE c_query_source;
378
379
380 -- Standard call to check for call compatibility.
381 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
382 p_api_version_number,
383 l_api_name,
384 g_pkg_name)
385 THEN
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387 END IF;
388
389 -- Initialize message list if p_init_msg_list is set to TRUE.
390 IF fnd_api.to_boolean( p_init_msg_list ) THEN
391
392 FND_msg_PUB.initialize;
393
394 END IF;
395
396 -- Initialize API return status to SUCCESS
397 x_return_status := FND_API.G_RET_STS_SUCCESS;
398
399 bis_collection_utilities.log('Running Initial Load of Segment Facts');
400 ---populating Segment Size
401 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
402
403 bis_collection_utilities.log('Truncating Facts Table');
404
405 Execute Immediate 'Truncate Table '||l_schema||'.bim_i_sgmt_facts';
406
407 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_SGMT_SIZE');
408
409 bis_collection_utilities.log('Inserting Segment Size');
410
411 Execute Immediate ' INSERT /*+ append parallel */ INTO bim_i_sgmt_facts ' ||
412 ' (creation_date ' ||
413 ' ,last_update_date ' ||
414 ' ,created_by ' ||
415 ' ,last_updated_by ' ||
416 ' ,last_update_login ' ||
417 ' ,transaction_create_date ' ||
418 ' ,segment_id ' ||
419 ' ,metric_type ' ||
420 ' ,segment_size ' ||
421 ' ,segment_status ' ||
422 ' ,cust_count_week ' ||
423 ' ,cust_count_month ' ||
424 ' ,cust_count_qtr ' ||
425 ' ,cust_count_year) ' ||
426 ' SELECT ' ||
427 ' SYSDATE ' ||
428 ' ,SYSDATE ' ||
432 ' ,TRUNC(sizes.creation_date) ' ||
429 ' ,-1 ' ||
430 ' ,-1 ' ||
431 ' ,-1 ' ||
433 ' ,act_size_used_by_id ' ||
434 ' ,''SIZE'' ' ||
435 ' ,sizes.size_delta ' ||
436 ' ,seg.status_code ' ||
437 ' ,0 ' ||
438 ' ,0 ' ||
439 ' ,0 ' ||
440 ' ,0 ' ||
441 ' FROM (SELECT a.cell_id , a.status_code ' ||
442 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgfb d ' ||
443 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
444 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
445 ' AND b.list_query_id = d.sql_id ' ||
446 ' AND d.source_name = c.source_type_code ' ||
447 ' AND c.based_on_tca_flag = ''Y'' ' ||
448 ' AND a.sel_type =''SQL'' ' ||
449 ' AND a.creation_date >= '''||p_start_date||''''||
450 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
451 ' UNION ALL ' ||
452 ' SELECT a.cell_id , a.status_code ' ||
453 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
454 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
455 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
456 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
457 ' AND d.source_type_code = b.source_object_name ' ||
458 ' AND b.based_on_tca_flag = ''Y'' ' ||
459 ' AND a.sel_type=''DIWB'' ' ||
460 ' AND a.creation_date >= '''||p_start_date||''''||
461 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
462 ' ) seg , ams_act_sizes sizes ' ||
463 ' WHERE seg.cell_id = sizes.act_size_used_by_id ' ||
464 ' AND trunc(sizes.creation_date) BETWEEN '''||p_start_date||''' AND '''||p_end_date||''''||
465 ' AND sizes.arc_act_size_used_by = ''CELL'' ';
466
467
468 COMMIT;
469
470 bis_collection_utilities.log('Inserting Active Customer Count');
471
472 Execute Immediate ' INSERT INTO bim_i_sgmt_facts ' ||
473 ' (creation_date ' ||
474 ' ,last_update_date ' ||
475 ' ,created_by ' ||
476 ' ,last_updated_by ' ||
477 ' ,last_update_login ' ||
478 ' ,transaction_create_date ' ||
479 ' ,segment_id ' ||
480 ' ,metric_type ' ||
481 ' ,segment_size ' ||
482 ' ,cust_count_year ' ||
483 ' ,cust_count_qtr ' ||
484 ' ,cust_count_month ' ||
485 ' ,cust_count_week) ' ||
486 ' SELECT ' ||
487 ' SYSDATE ' ||
488 ' ,SYSDATE ' ||
489 ' ,-1 ' ||
490 ' ,-1 ' ||
491 ' ,-1 ' ||
492 ' ,transaction_create_date ' ||
493 ' ,segment_id ' ||
494 ' ,''CUST'' ' ||
495 ' ,0 ' ||
496 ' ,SUM(ptd_year_cnt) ptd_year_cnt ' ||
497 ' ,SUM(ptd_qtr_cnt ) ptd_qtr_cnt ' ||
498 ' ,SUM(ptd_month_cnt) ptd_month_cnt ' ||
499 ' ,SUM(ptd_week_cnt) ptd_week_cnt ' ||
500 ' FROM ' ||
501 ' ( ' ||
502 ' WITH party_orders AS ' ||
503 ' (SELECT ' ||
504 ' segs1.segment_id ' ||
505 ' ,trunc(ord1.creation_date) transaction_create_date ' ||
506 ' ,segs1.party_id ' ||
507 ' FROM ' ||
508 ' oe_order_headers_all ord1 ' ||
509 ' ,bim_i_party_sgmt_facts segs1 ' ||
510 ' ,(SELECT a.cell_id cell_id ' ||
511 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgfb d ' ||
512 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
513 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
514 ' AND b.list_query_id = d.sql_id ' ||
515 ' AND d.source_name = c.source_type_code ' ||
516 ' AND c.based_on_tca_flag = ''Y'' ' ||
517 ' AND a.sel_type =''SQL'' ' ||
518 ' AND a.creation_date >= '''||p_start_date||''''||
519 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
520 ' UNION ALL ' ||
521 ' SELECT a.cell_id ' ||
522 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
523 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
524 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
525 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
526 ' AND d.source_type_code = b.source_object_name ' ||
527 ' AND b.based_on_tca_flag = ''Y'' ' ||
528 ' AND a.sel_type=''DIWB'' ' ||
529 ' AND a.creation_date >= '''||p_start_date||''''||
530 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
531 ' ) cells ' ||
532 ' ,hz_cust_accounts cust_acct ' ||
533 ' WHERE cust_acct.party_id = segs1.party_id ' ||
534 ' AND cust_acct.cust_account_id = ord1.sold_to_org_id ' ||
535 ' AND trunc(ord1.creation_date) between '''||p_start_date||''' AND '''||p_end_date||''''||
536 ' AND ord1.creation_date between segs1.start_date_active AND segs1.end_date_active ' ||
537 ' AND cells.cell_id=segs1.segment_id ' ||
538 ' ) ' ||
539 ' (SELECT ' ||
540 ' a.segment_id ' ||
541 ' ,a.transaction_create_date transaction_create_date ' ||
542 ' ,(CASE ' ||
543 ' WHEN ROW_NUMBER() OVER (partition by b.week_id,a.party_id,segment_id ORDER BY week_id,a.party_id,segment_id,a.transaction_create_date asc)=1 ' ||
544 ' THEN 1 ' ||
545 ' ELSE 0 ' ||
546 ' END ' ||
547 ' ) ptd_week_cnt ' ||
548 ' ,0 ptd_qtr_cnt ' ||
549 ' ,0 ptd_month_cnt ' ||
550 ' ,0 ptd_year_cnt ' ||
551 ' FROM party_orders a ' ||
552 ' ,fii_time_day b ' ||
553 ' WHERE a.transaction_create_date = b.report_date ' ||
554 ' GROUP BY b.report_date_julian, a.transaction_create_date ,a.party_id,a.segment_id,b.week_id ' ||
558 ' ,a.transaction_create_date transaction_create_date ' ||
555 ' UNION ALL ' ||
556 ' SELECT ' ||
557 ' a.segment_id ' ||
559 ' ,0 ptd_week_cnt ' ||
560 ' ,0 ptd_qtr_cnt ' ||
561 ' ,(CASE ' ||
562 ' when ROW_NUMBER() OVER (partition by b.ent_period_id,a.party_id,segment_id ORDER BY b.ent_period_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
563 ' THEN 1 ' ||
564 ' ELSE 0 ' ||
565 ' END ' ||
566 ' ) ptd_month_cnt ' ||
567 ' ,0 ptd_year_cnt ' ||
568 ' FROM party_orders a ' ||
569 ' ,fii_time_day b ' ||
570 ' WHERE a.transaction_create_date = b.report_date ' ||
571 ' GROUP BY b.report_date_julian, a.transaction_create_date,a.party_id,a.segment_id, b.ent_period_id ' ||
572 ' UNION ALL ' ||
573 ' SELECT ' ||
574 ' a.segment_id ' ||
575 ' ,a.transaction_create_date transaction_create_date ' ||
576 ' ,0 ptd_week_cnt ' ||
577 ' ,(CASE ' ||
578 ' WHEN ROW_NUMBER() OVER (partition by b.ent_qtr_id,a.party_id,segment_id ORDER BY b.ent_qtr_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
579 ' THEN 1 ' ||
580 ' ELSE 0 ' ||
581 ' END ' ||
582 ' ) ptd_qtr_cnt ' ||
583 ' ,0 ptd_month_cnt ' ||
584 ' ,0 ptd_year_cnt ' ||
585 ' FROM party_orders a ' ||
586 ' ,fii_time_day b ' ||
587 ' WHERE a.transaction_create_date = b.report_date ' ||
588 ' GROUP BY b.report_date_julian, a.transaction_create_date,a.party_id,a.segment_id,b.ent_qtr_id ' ||
589 ' UNION ALL ' ||
590 ' SELECT ' ||
591 ' a.segment_id ' ||
592 ' ,a.transaction_create_date transaction_create_date ' ||
593 ' ,0 ptd_week_cnt ' ||
594 ' ,0 ptd_qtr_cnt ' ||
595 ' ,0 ptd_month_cnt ' ||
596 ' ,(CASE ' ||
597 ' WHEN ROW_NUMBER() OVER (partition by b.ent_year_id,a.party_id,segment_id ORDER BY ent_year_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
598 ' THEN 1 ' ||
599 ' ELSE 0 ' ||
600 ' END ' ||
601 ' ) ptd_year_cnt ' ||
602 ' FROM party_orders a ' ||
603 ' ,fii_time_day b ' ||
604 ' WHERE a.transaction_create_date = b.report_date ' ||
605 ' GROUP BY b.report_date_julian,a.transaction_create_date ,a.party_id,a.segment_id, b.ent_year_id ' ||
606 ' ) ) ' ||
607 ' WHERE ptd_year_cnt >0 or ptd_qtr_cnt>0 or ptd_month_cnt>0 or ptd_week_cnt >0 ' ||
608 ' GROUP BY segment_id , transaction_create_date ' ;
609
610 Execute Immediate 'DROP TABLE source_query_sgfb';
611
612 COMMIT;
613
614 bis_collection_utilities.wrapup(p_status => TRUE
615 ,p_count => sql%rowcount
616 ,p_period_from => p_start_date
617 ,p_period_to => p_end_date
618 ,p_attribute1 => 'INITIAL_LOAD_START_DATE'
619 ,p_attribute2 => p_start_date
620 );
621
622 EXCEPTION
623
624 WHEN FND_API.G_EXC_ERROR THEN
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 Execute Immediate 'DROP TABLE source_query_sgfb';
627 -- Standard call to get message count and if count=1, get the message
628 FND_msg_PUB.Count_And_Get (
629 -- p_encoded => FND_API.G_FALSE,
630 p_count => x_msg_count,
631 p_data => x_msg_data
632 );
633
634 bis_collection_utilities.log('BIM_I_SGMT_FACTS_PKG:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
635
636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
637
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 Execute immediate 'DROP TABLE source_query_sgfb';
640
641 -- Standard call to get message count and if count=1, get the message
642 FND_msg_PUB.Count_And_Get (
643 --p_encoded => FND_API.G_FALSE,
644 p_count => x_msg_count,
645 p_data => x_msg_data
646 );
647
648 bis_collection_utilities.log('BIM_I_SGMT_FACTS_PKG:FIRST_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
649
650 WHEN OTHERS THEN
651
652 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
653 Execute immediate 'DROP TABLE source_query_sgfb';
654
655
656 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
657 THEN
658 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
659 END IF;
660
661 -- Standard call to get message count and if count=1, get the message
662 FND_msg_PUB.Count_And_Get (
663 -- p_encoded => FND_API.G_FALSE,
664 p_count => x_msg_count,
665 p_data => x_msg_data
666 );
667
668 bis_collection_utilities.log('BIM_I_SGMT_FACTS_PKG:FIRST_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
669
670
671 END first_load;
672
673 --------------------------------------------------------------------------------------------------
674 -- This procedure will populates all the data required into facts table for incremental load.
675 --
676 -- PROCEDURE INCREMENTAL_LOAD
677 --------------------------------------------------------------------------------------------------
678
679 PROCEDURE INCREMENTAL_LOAD
680 ( p_start_date IN DATE
681 ,p_end_date IN DATE
682 ,p_year_start_date IN DATE
683 ,p_api_version_number IN NUMBER
684 ,p_init_msg_list IN VARCHAR2
688 )
685 ,x_msg_count OUT NOCOPY NUMBER
686 ,x_msg_data OUT NOCOPY VARCHAR2
687 ,x_return_status OUT NOCOPY VARCHAR2
689 IS
690 l_user_id NUMBER := FND_GLOBAL.USER_ID();
691 l_api_version_number CONSTANT NUMBER := 1.0;
692 l_api_name CONSTANT VARCHAR2(30) := 'INCREMENTAL_LOAD';
693 l_table_name VARCHAR2(100);
694 l_conv_opp_status VARCHAR2(30);
695 l_dead_status VARCHAR2(30);
696 l_check_missing_rate NUMBER;
697 l_stmt VARCHAR2(50);
698 l_cert_level VARCHAR2(3);
699
700 l_source VARCHAR2(80);
701 l_sql_id NUMBER(32);
702 l_query_string VARCHAR2(32767);
703
704 cursor c_query_source is
705 select list_query_id, query
706 from ams_list_queries_all ;
707
708 l_found VARCHAR2(1) := 'N';
709 l_master_type VARCHAR2(80);
710 l_master_type_id NUMBER;
711 l_source_object_name VARCHAR2(80);
712 l_source_object_pk_field VARCHAR2(80);
713 l_sql_string_tbl AMS_ListGeneration_PKG.sql_string;
714 l_from_position NUMBER;
715 l_from_counter NUMBER;
716 l_end_position NUMBER;
717 l_end_counter NUMBER;
718 l_count NUMBER;
719 l_string_copy VARCHAR2(32767);
720 l_length NUMBER;
721
722 BEGIN
723
724 Execute Immediate 'CREATE TABLE ' || ' source_query_sgfb ' || '( sql_id NUMBER(32), source_name VARCHAR2(80) )';
725
726 Execute Immediate 'TRUNCATE TABLE source_query_sgfb';
727
728 OPEN c_query_source;
729 LOOP
730 FETCH c_query_source INTO l_sql_id, l_query_string;
731 EXIT WHEN c_query_source%notfound;
732
733 if ( l_query_string is NULL ) then
734 l_source := 'NO_MASTER_TYPE';
735 else
736 l_count := 0;
737 l_string_copy := l_query_string;
738
739 l_length := length(l_string_copy);
740
741 LOOP
742 l_count := l_count + 1;
743 IF l_length < 1999 THEN
744 l_sql_string_tbl(l_count) := l_string_copy;
745 EXIT;
746 ELSE
747 l_sql_string_tbl(l_count) := substr(l_string_copy, 1, 2000);
748 l_string_copy := substr(l_string_copy, 2000);
749 END IF;
750 l_length := length(l_string_copy);
751 END LOOP;
752
753 l_found := 'N';
754 AMS_ListGeneration_PKG.validate_sql_string(
755 p_sql_string => l_sql_string_tbl ,
756 p_search_string => 'FROM',
757 p_comma_valid => 'N',
758 x_found => l_found,
759 x_position => l_from_position,
760 x_counter => l_from_counter) ;
761
762
763 l_found := 'N';
764
765 AMS_ListGeneration_PKG.get_master_types (
766 p_sql_string => l_sql_string_tbl,
767 p_start_length => 1,
768 p_start_counter => 1,
769 p_end_length => l_from_position,
770 p_end_counter => l_from_counter,
771 x_master_type_id=> l_master_type_id,
772 x_master_type=> l_master_type,
773 x_found=> l_found,
774 x_source_object_name => l_source_object_name,
775 x_source_object_pk_field => l_source_object_pk_field);
776
777 IF nvl(l_found,'N') = 'N' THEN
778 --No master type.
779 l_source_object_name := 'NO_MASTER_TYPE';
780 END IF;
781
782
783 l_source := l_master_type;
784 END IF;
785 -- bis_collection_utilities.log('running the function --- '||l_source||l_master_type_id||l_master_type);
786 Execute Immediate 'INSERT INTO source_query_sgfb(sql_id, source_name) VALUES('||l_sql_id||', '''||l_source||''')';
787 l_source := NULL;
788 l_query_string := NULL;
789 l_sql_id := NULL;
790 END LOOP;
791 CLOSE c_query_source;
792
793 -- Standard call to check for call compatibility.
794 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
795 p_api_version_number,
796 l_api_name,
797 g_pkg_name)
798 THEN
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800 END IF;
801
802 -- Initialize message list if p_init_msg_list is set to TRUE.
803 IF FND_API.to_Boolean( p_init_msg_list ) THEN
804
805 FND_msg_PUB.initialize;
806
807 END IF;
808
809 -- Initialize API return status to SUCCESS
810 x_return_status := FND_API.G_RET_STS_SUCCESS;
811
812 bis_collection_utilities.log('Running Incremental Load of Segment Facts');
813
814
815 bis_collection_utilities.log('Inserting Segment Size');
816
817 DELETE FROM bim_i_sgmt_facts
818 WHERE metric_type = 'CUST'
819 AND transaction_create_date = TRUNC(p_start_date);
820
821 COMMIT;
822
823 Execute Immediate ' INSERT /*+ append parallel */ INTO bim_i_sgmt_facts ' ||
824 ' (creation_date ' ||
825 ' ,last_update_date ' ||
826 ' ,created_by ' ||
827 ' ,last_updated_by ' ||
828 ' ,last_update_login ' ||
829 ' ,transaction_create_date ' ||
830 ' ,segment_id ' ||
831 ' ,metric_type ' ||
832 ' ,segment_size ' ||
833 ' ,cust_count_year ' ||
834 ' ,cust_count_qtr ' ||
835 ' ,cust_count_month ' ||
839 ' ,SYSDATE ' ||
836 ' ,cust_count_week) ' ||
837 ' SELECT ' ||
838 ' SYSDATE ' ||
840 ' ,-1 ' ||
841 ' ,-1 ' ||
842 ' ,-1 ' ||
843 ' ,TRUNC(sizes.creation_date) ' ||
844 ' ,act_size_used_by_id ' ||
845 ' ,''SIZE'' ' ||
846 ' ,sizes.size_delta ' ||
847 ' ,0 ' ||
848 ' ,0 ' ||
849 ' ,0 ' ||
850 ' ,0 ' ||
851 ' FROM (SELECT a.cell_id , a.status_code ' ||
852 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgfb d ' ||
853 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
854 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
855 ' AND b.list_query_id = d.sql_id ' ||
856 ' AND d.source_name = c.source_type_code ' ||
857 ' AND c.based_on_tca_flag = ''Y'' ' ||
858 ' AND a.sel_type =''SQL'' ' ||
859 ' AND a.creation_date >= '''||g_initial_start_date||''''||
860 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
861 ' UNION ALL ' ||
862 ' SELECT a.cell_id , a.status_code ' ||
863 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
864 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
865 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
866 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
867 ' AND d.source_type_code = b.source_object_name ' ||
868 ' AND b.based_on_tca_flag = ''Y'' ' ||
869 ' AND a.sel_type=''DIWB'' ' ||
870 ' AND a.creation_date >= '''||g_initial_start_date||''''||
871 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
872 ' ) seg ' ||
873 ' , ams_act_sizes sizes ' ||
874 ' WHERE seg.cell_id = sizes.act_size_used_by_id ' ||
875 ' AND sizes.last_update_date between '''||p_start_date||''' and '''||p_end_date||''''||
876 ' AND sizes.ARC_ACT_SIZE_USED_BY = ''CELL'' ' ||
877 ' UNION ALL ' ||
878 ' SELECT ' ||
879 ' SYSDATE ' ||
880 ' ,SYSDATE ' ||
881 ' ,-1 ' ||
882 ' ,-1 ' ||
883 ' ,-1 ' ||
884 ' ,transaction_create_date ' ||
885 ' ,segment_id ' ||
886 ' ,''CUST'' ' ||
887 ' ,0 ' ||
888 ' ,SUM(ptd_year_cnt) ptd_year_cnt ' ||
889 ' ,SUM(ptd_qtr_cnt ) ptd_qtr_cnt ' ||
890 ' ,SUM(ptd_month_cnt) ptd_month_cnt ' ||
891 ' ,SUM(ptd_week_cnt) ptd_week_cnt ' ||
892 ' FROM ' ||
893 ' ( ' ||
894 ' WITH party_orders AS ' ||
895 ' (SELECT ' ||
896 ' segs1.segment_id ' ||
897 ' ,trunc(ord1.creation_date) transaction_create_date ' ||
898 ' ,segs1.party_id ' ||
899 ' FROM ' ||
900 ' oe_order_headers_all ord1 ' ||
901 ' ,bim_i_party_sgmt_facts segs1 ' ||
902 ' ,(SELECT a.cell_id cell_id ' ||
903 ' FROM ams_cells_all_b a, ams_list_queries_all b, ams_list_src_types c, source_query_sgfb d ' ||
904 ' WHERE b.act_list_query_used_by_id = a.cell_id ' ||
905 ' AND b.arc_act_list_query_used_by =''CELL'' ' ||
906 ' AND b.list_query_id = d.sql_id ' ||
907 ' AND d.source_name = c.source_type_code ' ||
908 ' AND c.based_on_tca_flag = ''Y'' ' ||
909 ' AND a.sel_type =''SQL'' ' ||
910 ' AND a.creation_date >= '''||g_initial_start_date||''''||
911 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
912 ' UNION ALL ' ||
913 ' SELECT a.cell_id ' ||
914 ' FROM ams_cells_all_b a,ams_list_src_types b,ams_act_discoverer_all c,ams_discoverer_sql d ' ||
915 ' WHERE c.act_discoverer_used_by_id = a.cell_id ' ||
916 ' AND c.arc_act_discoverer_used_by =''CELL'' ' ||
917 ' AND c.discoverer_sql_id = d.discoverer_sql_id ' ||
918 ' AND d.source_type_code = b.source_object_name ' ||
919 ' AND b.based_on_tca_flag = ''Y'' ' ||
920 ' AND a.sel_type=''DIWB'' ' ||
921 ' AND a.creation_date >= '''||g_initial_start_date||''''||
922 ' AND a.status_code IN (''AVAILABLE'',''CANCELLED'') ' ||
923 ' ) cells ' ||
924 ' ,hz_cust_accounts cust_acct ' ||
925 ' WHERE cust_acct.party_id = segs1.party_id ' ||
926 ' AND cust_acct.cust_account_id = ord1.sold_to_org_id ' ||
927 ' AND ord1.creation_date between '''||p_year_start_date||''' AND '''||p_end_date||''''||
928 ' AND ord1.creation_date between segs1.start_date_active AND segs1.end_date_active ' ||
929 ' AND cells.cell_id=segs1.segment_id ' ||
930 ' ) ' ||
931 ' (SELECT ' ||
932 ' a.segment_id ' ||
933 ' ,a.transaction_create_date transaction_create_date ' ||
934 ' ,(CASE ' ||
935 ' WHEN ROW_NUMBER() OVER (partition by b.week_id,a.party_id,segment_id ORDER BY week_id,a.party_id,segment_id,a.transaction_create_date asc)=1 ' ||
936 ' THEN 1 ' ||
937 ' ELSE 0 ' ||
938 ' END ' ||
939 ' ) ptd_week_cnt ' ||
940 ' ,0 ptd_qtr_cnt ' ||
941 ' ,0 ptd_month_cnt ' ||
942 ' ,0 ptd_year_cnt ' ||
943 ' FROM party_orders a ' ||
944 ' ,fii_time_day b ' ||
945 ' WHERE a.transaction_create_date = b.report_date ' ||
946 ' GROUP BY b.report_date_julian, a.transaction_create_date ,a.party_id,a.segment_id,b.week_id ' ||
947 ' UNION ALL ' ||
948 ' SELECT ' ||
949 ' a.segment_id ' ||
950 ' ,a.transaction_create_date transaction_create_date ' ||
951 ' ,0 ptd_week_cnt ' ||
952 ' ,0 ptd_qtr_cnt ' ||
953 ' ,(CASE ' ||
954 ' when ROW_NUMBER() OVER (partition by b.ent_period_id,a.party_id,segment_id ORDER BY b.ent_period_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
955 ' THEN 1 ' ||
956 ' ELSE 0 ' ||
957 ' END ' ||
958 ' ) ptd_month_cnt ' ||
959 ' ,0 ptd_year_cnt ' ||
960 ' FROM party_orders a ' ||
961 ' ,fii_time_day b ' ||
962 ' WHERE a.transaction_create_date = b.report_date ' ||
963 ' GROUP BY b.report_date_julian, a.transaction_create_date,a.party_id,a.segment_id, b.ent_period_id ' ||
964 ' UNION ALL ' ||
965 ' SELECT ' ||
966 ' a.segment_id ' ||
967 ' ,a.transaction_create_date transaction_create_date ' ||
968 ' ,0 ptd_week_cnt ' ||
969 ' ,(CASE ' ||
970 ' WHEN ROW_NUMBER() OVER (partition by b.ent_qtr_id,a.party_id,segment_id ORDER BY b.ent_qtr_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
971 ' THEN 1 ' ||
972 ' ELSE 0 ' ||
973 ' END ' ||
974 ' ) ptd_qtr_cnt ' ||
975 ' ,0 ptd_month_cnt ' ||
976 ' ,0 ptd_year_cnt ' ||
977 ' FROM party_orders a ' ||
978 ' ,fii_time_day b ' ||
979 ' WHERE a.transaction_create_date = b.report_date ' ||
980 ' GROUP BY b.report_date_julian, a.transaction_create_date,a.party_id,a.segment_id,b.ent_qtr_id ' ||
981 ' UNION ALL ' ||
982 ' SELECT ' ||
983 ' a.segment_id ' ||
984 ' ,a. transaction_create_date transaction_create_date ' ||
985 ' ,0 ptd_week_cnt ' ||
986 ' ,0 ptd_qtr_cnt ' ||
987 ' ,0 ptd_month_cnt ' ||
988 ' ,(CASE ' ||
989 ' WHEN ROW_NUMBER() OVER (partition by b.ent_year_id,a.party_id,segment_id ORDER BY ent_year_id,a.party_id,segment_id,a.transaction_create_date asc )=1 ' ||
990 ' THEN 1 ' ||
991 ' ELSE 0 ' ||
992 ' END ' ||
993 ' ) ptd_year_cnt ' ||
994 ' FROM party_orders a ' ||
995 ' ,fii_time_day b ' ||
996 ' WHERE a.transaction_create_date = b.report_date ' ||
997 ' GROUP BY b.report_date_julian,a.transaction_create_date ,a.party_id,a.segment_id, b.ent_year_id ' ||
998 ' ) ) ' ||
999 ' WHERE transaction_create_date >= trunc(to_date('''||p_start_date||''',''DD-MON-YY''),''MONTH'')'||
1000 ' AND (ptd_year_cnt >0 or ptd_qtr_cnt>0 or ptd_month_cnt>0 or ptd_week_cnt >0 ) ' ||
1001 ' GROUP BY segment_id , transaction_create_date ' ;
1002
1003 Execute Immediate 'DROP TABLE source_query_sgfb';
1004
1005 COMMIT;
1006
1007 bis_collection_utilities.wrapup(p_status => TRUE
1008 ,p_count => sql%rowcount
1009 ,p_period_from => p_start_date
1010 ,p_period_to => p_end_date
1011 ,p_attribute1 => 'INITIAL_LOAD_START_DATE'
1012 ,p_attribute2 =>g_initial_start_date
1013 );
1014
1015
1016 /***************************************************************/
1017
1018 EXCEPTION
1019
1020 WHEN FND_API.G_EXC_ERROR THEN
1021 x_return_status := FND_API.G_RET_STS_ERROR;
1022 Execute Immediate 'DROP TABLE source_query_sgfb';
1023 -- Standard call to get message count and if count=1, get the message
1024 FND_msg_PUB.Count_And_Get (
1025 -- p_encoded => FND_API.G_FALSE,
1026 p_count => x_msg_count,
1027 p_data => x_msg_data
1028 );
1029
1030 bis_collection_utilities.log('BIM_I_SGMT_FACTS_PKG:INCREMENTAL_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
1031
1032 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1033
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1035 Execute Immediate 'DROP TABLE source_query_sgfb';
1036
1037 -- Standard call to get message count and if count=1, get the message
1038 FND_msg_PUB.Count_And_Get (
1039 --p_encoded => FND_API.G_FALSE,
1040 p_count => x_msg_count,
1041 p_data => x_msg_data
1042 );
1043
1044 bis_collection_utilities.log('BIM_I_SGMT_FACTS_PKG:INCREMENTAL_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
1045
1046 WHEN OTHERS THEN
1047
1048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1049 Execute immediate 'DROP TABLE source_query_sgfb';
1050
1051 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
1052 THEN
1053 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
1054 END IF;
1055
1056 -- Standard call to get message count and if count=1, get the message
1057 FND_msg_PUB.Count_And_Get (
1058 -- p_encoded => FND_API.G_FALSE,
1059 p_count => x_msg_count,
1060 p_data => x_msg_data
1061 );
1062
1063 bis_collection_utilities.log('BIM_I_SGMT_FACTS_PKG:INCREMENTAL_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
1064
1065
1066 END incremental_load;
1067
1068
1069 END bim_i_sgmt_facts_pkg;
1070