[Home] [Help]
PACKAGE BODY: APPS.BIM_CAMPAIGN_FACTS
Source
1 PACKAGE BODY BIM_CAMPAIGN_FACTS AS
2 /*$Header: bimcmpfb.pls 120.1 2005/12/05 06:50:19 arvikuma noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_CAMPAIGN_FACTS';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimcmpfb.pls';
6
7 FUNCTION convert_currency(
8 p_from_currency VARCHAR2
9 ,p_from_amount NUMBER) return NUMBER
10 IS
11 l_conversion_type_profile CONSTANT VARCHAR2(30) := 'AMS_CURR_CONVERSION_TYPE';
12 l_user_rate CONSTANT NUMBER := 1;
13 l_max_roll_days CONSTANT NUMBER := -1;
14 l_denominator NUMBER; -- Not used in Marketing.
15 l_numerator NUMBER; -- Not used in Marketing.
16 l_conversion_type VARCHAR2(30); -- Curr conversion type; see API doc for details.
17 l_to_amount NUMBER;
18 l_rate NUMBER;
19 l_to_currency VARCHAR2(100) ;
20 x_return_status varchar2(1);
21 BEGIN
22
23 l_to_currency := fnd_profile.value('AMS_DEFAULT_CURR_CODE');
24
25 -- condition added to pass conversion types
26 l_conversion_type := fnd_profile.VALUE(l_conversion_type_profile);
27
28 -- Conversion type cannot be null in profile
29 IF l_conversion_type IS NULL THEN
30 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
31 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
32 fnd_msg_pub.add;
33 END IF;
34 RETURN 0;
35 END IF;
36
37 -- Call the proper AMS_UTILITY_API API to convert the amount.
38
39 ams_utility_pvt.Convert_Currency (
40 x_return_status ,
41 p_from_currency,
42 l_to_currency,
43 sysdate,
44 p_from_amount,
45 l_to_amount);
46
47 /* gl_currency_api.convert_closest_amount(
48 x_from_currency => p_from_currency
49 ,x_to_currency => l_to_currency
50 ,x_conversion_date =>sysdate
51 ,x_conversion_type => l_conversion_type
52 ,x_user_rate => l_user_rate
53 ,x_amount => p_from_amount
54 ,x_max_roll_days => l_max_roll_days
55 ,x_converted_amount => l_to_amount
56 ,x_denominator => l_denominator
57 ,x_numerator => l_numerator
58 ,x_rate => l_rate); */
59
60 RETURN (l_to_amount);
61
62 EXCEPTION
63 WHEN gl_currency_api.no_rate THEN
64 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
65 fnd_message.set_name('OZF', 'OZF_NO_RATE');
66 fnd_msg_pub.add;
67 END IF;
68 WHEN gl_currency_api.invalid_currency THEN
69 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
70 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
71 fnd_msg_pub.add;
72 END IF;
73 WHEN OTHERS THEN
74 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
75 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_curency');
76 END IF;
77 END convert_currency;
78
79 -------------------------------------------------------------------------------
80
81 FUNCTION ret_max_date(p_sales_lead_id in number) return date is
82 CURSOR get_max_date IS
83 SELECT max(b.creation_date)
84 FROM as_sales_leads a, as_sales_leads_log b
85 WHERE a.sales_lead_id = b.sales_lead_id
86 AND b.sales_lead_id = p_sales_lead_id ;
87
88 l_date date;
89
90 BEGIN
91 OPEN get_max_date;
92 FETCH get_max_date into l_date;
93 CLOSE get_max_date;
94
95 RETURN l_date;
96
97 END;
98
99 ----------------------------------------------------------------------------------------------------
100 /* This procedure will conditionally call the FIRST_LOAD or the DAILY_LOAD */
101 ----------------------------------------------------------------------------------------------------
102
103 PROCEDURE POPULATE
104 (
105 p_api_version_number IN NUMBER
106 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
107 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
108 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
109 ,x_msg_count OUT NOCOPY NUMBER
110 ,x_msg_data OUT NOCOPY VARCHAR2
111 ,x_return_status OUT NOCOPY VARCHAR2
112 ,p_object IN VARCHAR2
113 ,p_start_date IN DATE
114 ,p_end_date IN DATE
115 ,p_para_num IN NUMBER
116 --,p_mode IN VARCHAR2
117 ) IS
118
119 l_profile NUMBER;
120 v_error_code NUMBER;
121 v_error_text VARCHAR2(1500);
122 l_last_update_date DATE;
123 l_start_date DATE;
124 l_end_date DATE;
125 l_user_id NUMBER := FND_GLOBAL.USER_ID();
126 l_sysdate DATE := SYSDATE;
127 l_api_version_number CONSTANT NUMBER := 1.0;
128 l_api_name CONSTANT VARCHAR2(30) := 'AMS_CAMPAIGN_FACTS';
129 l_success VARCHAR2(3);
130 s_date DATE := to_date('01/01/1950 01:01:01', 'DD/MM/YYYY HH:MI:SS') ;
131 l_temp DATE;
132 l_mesg_text VARCHAR2(100);
133 l_load_type VARCHAR2(100);
134 l_period_error VARCHAR2(5000);
135 l_currency_error VARCHAR2(5000);
136 l_err_code NUMBER;
137 l_temp_start_date DATE;
138 l_temp_end_date DATE;
139 l_temp_p_end_date DATE;
140
141 BEGIN
142
143 -- Standard call to check for call compatibility.
144 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
145 p_api_version_number,
146 l_api_name,
147 g_pkg_name)
148 THEN
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151
152 -- Initialize message list if p_init_msg_list is set to TRUE.
153 IF FND_API.to_Boolean( p_init_msg_list )
154 THEN
155 FND_MSG_PUB.initialize;
156 END IF;
157
158 -- Initialize API return status to SUCCESS
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
160
161 -- Debug Message
162 -- AMS_UTILITY_PVT.debug_message('Private API: ' || 'Running the populate procedure');
163
164 /* Find if the data will be loaded for the first time or not.*/
165 DECLARE
166 CURSOR chk_history_data IS
167 SELECT MAX(end_date)
168 FROM bim_rep_history
169 WHERE object = 'CAMPAIGN';
170
171 BEGIN
172 OPEN chk_history_data;
173 FETCH chk_history_data INTO l_end_date;
174 CLOSE chk_history_data;
175 EXCEPTION
176 WHEN OTHERS THEN
177 FND_MSG_PUB.Count_And_Get (
178 -- p_encoded => FND_API.G_FALSE,
179 p_count => x_msg_count,
180 p_data => x_msg_data
181 );
182 END;
183
184 /* End of the code for checking the data will be loaded for the first time or not. */
185
186 IF(trunc(p_end_date) = trunc(sysdate)) THEN
187 l_temp_p_end_date := trunc(p_end_date) - 1;
188 ELSE
189 l_temp_p_end_date := trunc(p_end_date);
190 END IF;
191
192 IF (l_end_date IS NOT NULL AND p_start_date IS NOT NULL)
193 THEN
194 ams_utility_pvt.write_conc_log('First Time Load is already run. Subsequent Load should be run .');
195 ams_utility_pvt.write_conc_log('Concurrent Program Exits Now');
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198
199
200 IF p_start_date IS NOT NULL THEN
201
202 IF (p_start_date >= l_temp_p_end_date) THEN
203 ams_utility_pvt.write_conc_log('The start date cannot be greater than or equal to the current end date');
204 ams_utility_pvt.write_conc_log('Concurrent Program Exits Now ');
205 RAISE FND_API.G_EXC_ERROR;
206 END IF;
207
208 l_temp_start_date := trunc(p_start_date);
209 l_temp_end_date := trunc(l_temp_p_end_date);
210 l_load_type := 'FIRST_LOAD';
211
212 ELSE
213 IF l_end_date IS NOT NULL THEN
214
215 IF (l_temp_p_end_date <= l_end_date) THEN
216 ams_utility_pvt.write_conc_log('This program is already run upto: ' || trunc(l_end_date));
217 ams_utility_pvt.write_conc_log('Concurrent Program Exits Now ');
218 RAISE FND_API.g_exc_error;
219 END IF;
220
221 l_temp_start_date := trunc(l_end_date) + 1;
222 l_temp_end_date := trunc(l_temp_p_end_date);
223 l_load_type := 'SUBSEQUENT_LOAD';
224
225 END IF;
226
227 END IF;
228
229 -- Validate the Periods and Currencies before processing any further
230 --l_err_code := BIM_VALIDITY_CHECK.validate_campaigns(l_temp_start_date,
231 -- l_temp_end_date, l_period_error, l_currency_error);
232 --COMMENT out before checking into arcs
233 l_err_code := 0;
234
235 IF (l_err_code = 0) THEN -- Validation Succesful
236
237 CAMPAIGN_SUBSEQUENT_LOAD(p_start_date => l_temp_start_date
238 ,p_end_date => l_temp_end_date
239 ,p_api_version_number => l_api_version_number
240 ,p_init_msg_list => FND_API.G_FALSE
241 ,p_load_type => l_load_type
242 ,x_msg_count => x_msg_count
243 ,x_msg_data => x_msg_data
244 ,x_return_status => x_return_status
245 );
246
247 IF x_return_status = FND_API.g_ret_sts_error
248 THEN
249 RAISE FND_API.g_exc_error;
250 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
251 RAISE FND_API.g_exc_unexpected_error;
252 END IF;
253 ELSE
254 ams_utility_pvt.write_conc_log('----Period Validation----');
255 ams_utility_pvt.write_conc_log(l_period_error);
256 ams_utility_pvt.write_conc_log('----Currency Validation----');
257 ams_utility_pvt.write_conc_log(l_currency_error);
258
259 END IF;
260
261 --Standard check of commit
262
263 IF FND_API.To_Boolean ( p_commit ) THEN
264 COMMIT WORK;
265 END IF;
266
267 COMMIT;
268 ams_utility_pvt.write_conc_log('Campaigns Concurrent Program Succesfully Completed ');
269
270 -- Standard call to get message count and if count is 1, get message info.
271 FND_MSG_PUB.Count_And_Get
272 (p_count => x_msg_count,
273 p_data => x_msg_data
274 );
275
276 EXCEPTION
277
278 WHEN FND_API.G_EXC_ERROR THEN
279 x_return_status := FND_API.G_RET_STS_ERROR;
280 -- Standard call to get message count and if count=1, get the message
281 FND_msg_PUB.Count_And_Get (
282 -- p_encoded => FND_API.G_FALSE,
283 p_count => x_msg_count,
284 p_data => x_msg_data
285 );
286
287
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289
290 /* FOR l_counter IN 1 .. x_msg_count
291 LOOP
292 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
293 fnd_msg_pub.dump_msg(l_counter);
294 end loop; */
295
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297 -- Standard call to get message count and if count=1, get the message
298 FND_msg_PUB.Count_And_Get (
299 --p_encoded => FND_API.G_FALSE,
300 p_count => x_msg_count,
301 p_data => x_msg_data
302 );
303
304 WHEN OTHERS THEN
305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
307 THEN
308 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
309 END IF;
310 -- Standard call to get message count and if count=1, get the message
311 FND_msg_PUB.Count_And_Get (
312 -- p_encoded => FND_API.G_FALSE,
313 p_count => x_msg_count,
314 p_data => x_msg_data
315 );
316
317 END POPULATE;
318
319 --------------------------------------------------------------------------------------------------
320 /* This procedure will insert a HISTORY record whenever daily or first load is run */
321 --------------------------------------------------------------------------------------------------
322
323 PROCEDURE LOG_HISTORY
324 (--p_api_version_number IN NUMBER
325 --,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
326 --,x_msg_count OUT NOCOPY NUMBER
327 --,x_msg_data OUT NOCOPY VARCHAR2
328 --,x_return_status OUT NOCOPY VARCHAR2
329 p_object IN VARCHAR2,
330 p_start_date IN DATE DEFAULT NULL,
331 p_end_date IN DATE DEFAULT NULL
332 )
333 IS
334 l_user_id NUMBER := FND_GLOBAL.USER_ID();
335 l_sysdate DATE := SYSDATE;
336 l_api_version_number CONSTANT NUMBER := 1.0;
337 l_api_name CONSTANT VARCHAR2(30) := 'AMS_CAMPAIGN_FACTS';
338 l_success VARCHAR2(3);
339
340 BEGIN
341
342 -- Debug Message
343 --AMS_UTILITY_PVT.debug_message('Private API: ' || 'Running the LOG_HISTORY procedure ');
344
345 /* -- Standard call to check for call compatibility.
346 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
347 p_api_version_number,
348 l_api_name,
349 g_pkg_name)
350 THEN
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354 -- Initialize message list if p_init_msg_list is set to TRUE.
355 IF FND_API.to_Boolean( p_init_msg_list )
356 THEN
357 FND_msg_PUB.initialize;
358 END IF;
359
360 -- Initialize API return status to SUCCESS
361 x_return_status := FND_API.G_RET_STS_SUCCESS;
362 */
363
364 INSERT INTO bim_rep_history
365 (CREATION_DATE,
366 LAST_UPDATE_DATE,
367 CREATED_BY,
368 LAST_UPDATED_BY,
369 OBJECT,
370 OBJECT_LAST_UPDATED_DATE,
371 START_DATE,
372 END_DATE)
373 VALUES
374 (sysdate,
375 sysdate,
376 l_user_id,
377 l_user_id,
378 p_object,
379 sysdate,
380 p_start_date,
381 p_end_date);
382
383 /* -- Standard call to get message count and if count is 1, get message info.
384 FND_msg_PUB.Count_And_Get
385 (p_count => x_msg_count,
386 p_data => x_msg_data
387 );
388 EXCEPTION
389
390 WHEN FND_API.G_EXC_ERROR THEN
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 -- Standard call to get message count and if count=1, get the message
393 FND_msg_PUB.Count_And_Get (
394 -- p_encoded => FND_API.G_FALSE,
395 p_count => x_msg_count,
396 p_data => x_msg_data
397 );
398
399 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
401 -- Standard call to get message count and if count=1, get the message
402 FND_msg_PUB.Count_And_Get (
403 --p_encoded => FND_API.G_FALSE,
404 p_count => x_msg_count,
405 p_data => x_msg_data
406 );
407
408 WHEN OTHERS THEN
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
410 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
411 THEN
412 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
413 END IF;
414 -- Standard call to get message count and if count=1, get the message
415 FND_msg_PUB.Count_And_Get (
416 -- p_encoded => FND_API.G_FALSE,
417 p_count => x_msg_count,
418 p_data => x_msg_data
419 );
420 */
421 --COMMIT;
422
423 END LOG_HISTORY;
424
425 --------------------------------------------------------------------------------------------------
426 -- This procedure will excute when data is loaded for the first time, and run the program incrementally.
427
428 -- PROCEDURE CAMPAIGN_FIRST_LOAD
429 --------------------------------------------------------------------------------------------------
430
431 PROCEDURE CAMPAIGN_FIRST_LOAD
432 ( p_start_date IN DATE
433 ,p_end_date IN DATE
434 ,p_api_version_number IN NUMBER
435 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
436 ,p_load_type IN VARCHAR2
437 ,x_msg_count OUT NOCOPY NUMBER
438 ,x_msg_data OUT NOCOPY VARCHAR2
439 ,x_return_status OUT NOCOPY VARCHAR2
440 )
441 IS
442 l_user_id NUMBER := FND_GLOBAL.USER_ID();
443 l_start_date DATE;
444 l_end_date DATE;
445 l_last_update_date DATE;
446 l_success VARCHAR2(3);
447 l_wkdt DATE;
448 l_noleads NUMBER;
449 l_nooppor NUMBER;
450 l_orders NUMBER;
451 l_noposresp NUMBER;
452 l_revenue NUMBER;
453 l_forecasted_cost NUMBER;
454 l_actual_cost NUMBER;
455 l_targeted_customer NUMBER;
456 l_noofnew_customer NUMBER;
457 l_temp NUMBER;
458 l_tempo NUMBER;
459 l_seq NUMBER;
460 l_seqw NUMBER;
461 l_api_version_number CONSTANT NUMBER := 1.0;
462 l_api_name CONSTANT VARCHAR2(30) := 'CAMPAIGN_FIRST_LOAD';
463 l_seq_name VARCHAR(100);
464 l_def_tablespace VARCHAR2(100);
465 l_index_tablespace VARCHAR2(100);
466 l_oracle_username VARCHAR2(100);
467 l_table_name VARCHAR2(100);
468 l_temp_msg VARCHAR2(100);
469
470 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
471
472 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
473
474 l_pct_free generic_number_table;
475 l_ini_trans generic_number_table;
476 l_max_trans generic_number_table;
477 l_initial_extent generic_number_table;
478 l_next_extent generic_number_table;
479 l_min_extents generic_number_table;
480 l_max_extents generic_number_table;
481 l_pct_increase generic_number_table;
482
483
484 l_owner generic_char_table;
485 l_index_name generic_char_table;
486 l_ind_column_name generic_char_table;
487 l_index_table_name generic_char_table;
488 i NUMBER;
489 l_junk NUMBER;
490
491 l_status VARCHAR2(30);
492 l_industry VARCHAR2(30);
493 l_orcl_schema VARCHAR2(30);
494 l_bol BOOLEAN := fnd_installation.get_app_info ('BIM',l_status,l_industry,l_orcl_schema);
495
496
497 CURSOR get_ts_name IS
498 SELECT i.tablespace, i.index_tablespace, u.oracle_username
499 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
500 WHERE a.application_short_name = 'BIM'
501 AND a.application_id = i.application_id
502 AND u.oracle_id = i.oracle_id;
503
504 CURSOR get_index_params (l_schema VARCHAR2) IS
505 SELECT a.owner,a.index_name,b.table_name,b.column_name,pct_free,ini_trans,max_trans
506 ,initial_extent,next_extent,min_extents,
507 max_extents, pct_increase
508 FROM all_indexes a, all_ind_columns b
509 WHERE a.index_name = b.index_name
510 AND a.owner = l_schema
511 AND a.owner = b.index_owner
512 AND a.index_name like 'BIM_R_CAMP_%FACTS%';
513
514
515 l_min_date date;
516
517 l_org_id number;
518
519 CURSOR get_org_id IS
520 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
521 FROM dual;
522 l_status1 VARCHAR2(5);
523 l_industry1 VARCHAR2(5);
524 l_schema VARCHAR2(30);
525 l_return BOOLEAN;
526 BEGIN
527 l_return := fnd_installation.get_app_info('BIM', l_status1, l_industry1, l_schema);
528 ams_utility_pvt.write_conc_log(p_start_date || ' '|| p_end_date);
529 -- Standard call to check for call compatibility.
530 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
531 p_api_version_number,
532 l_api_name,
533 g_pkg_name)
534 THEN
535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
536 END IF;
537
538 -- Initialize message list if p_init_msg_list is set to TRUE.
539 IF FND_API.to_Boolean( p_init_msg_list )
540 THEN
541 FND_msg_PUB.initialize;
542 END IF;
543
544 -- Initialize API return status to SUCCESS
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546
547 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS: Running the First Load '||sqlerrm(sqlcode));
548
549 -- The below four commands are necessary for the purpose of the parallel insertion */
550
551 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
552
553 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_camp_daily_facts nologging ';
554
555 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_camp_weekly_facts nologging ';
556
557 EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_camp_daily_facts_s CACHE 1000 ';
558
559 EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_camp_weekly_facts_s CACHE 1000 ';
560
561 /*Get the tablespace name for the purpose of creating the index on that tablespace. */
562
563
564 OPEN get_ts_name;
565 FETCH get_ts_name INTO l_def_tablespace, l_index_tablespace, l_oracle_username;
566 CLOSE get_ts_name;
567
568 OPEN get_org_id;
569 FETCH get_org_id INTO l_org_id;
570 CLOSE get_org_id;
571
572
573 /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */
574 i := 1;
575 FOR x in get_index_params (l_orcl_schema) LOOP
576
577 l_pct_free(i) := x.pct_free;
578 l_ini_trans(i) := x.ini_trans;
579 l_max_trans(i) := x.max_trans;
580 l_initial_extent(i) := x.initial_extent;
581 l_next_extent(i) := x.next_extent;
582 l_min_extents(i) := x.min_extents;
583 l_max_extents(i) := x.max_extents;
584 l_pct_increase(i) := x.pct_increase;
585
586 l_owner(i) := x.owner;
587 l_index_name(i) := x.index_name;
588 l_index_table_name(i) := x.table_name;
589 l_ind_column_name(i) := x.column_name;
590
591
592 -- Drop the index before the mass upload
593
594 EXECUTE IMMEDIATE 'DROP INDEX '|| l_owner(i) || '.'|| l_index_name(i) ;
595 i := i + 1;
596 END LOOP;
597
598 /* End of Code for dropping the existing indexes */
599
600
601 -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');
602
603 l_org_id := 0;
604
605 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
606 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE FIRST INSERT ' || l_temp_msg);
607
608 INSERT /*+ append parallel(CDF,1) */
609 INTO bim_r_camp_daily_facts CDF
610 (
611 campaign_daily_transaction_id
612 ,creation_date
613 ,last_update_date
614 ,created_by
615 ,last_updated_by
616 ,last_update_login
617 ,campaign_id
618 ,schedule_id
619 ,transaction_create_date
620 ,schedule_source_code
621 ,campaign_source_code
622 ,schedule_activity_type
623 ,schedule_activity_id
624 ,campaign_purpose
625 ,campaign_type
626 ,start_date
627 ,end_date
628 ,schedule_purpose
629 ,business_unit_id
630 ,org_id
631 ,campaign_status
632 ,schedule_status
633 ,campaign_country
634 ,campaign_region
635 ,schedule_region
636 ,schedule_country
637 ,campaign_budget_fc
638 ,schedule_budget_fc
639 ,load_date
640 ,year
641 ,qtr
642 ,month
643 ,leads_open
644 ,leads_closed
645 ,leads_open_amt
646 ,leads_closed_amt
647 ,leads_new
648 ,leads_new_amt
649 ,leads_hot
650 ,leads_converted
651 ,metric1 -- leads_dead
652 ,opportunities
653 ,opportunity_amt
654 ,orders_booked
655 ,orders_booked_amt
656 ,forecasted_revenue
657 ,actual_revenue
658 ,forecasted_cost
659 ,actual_cost
660 ,forecasted_responses
661 ,positive_responses
662 ,targeted_customers
663 ,budget_requested
664 ,budget_approved
665 )
666 SELECT /*+ parallel(OUTER,1) */
667 bim_r_camp_daily_facts_s.nextval
668 ,sysdate
669 ,sysdate
670 ,-1
671 ,-1
672 ,-1
673 ,campaign_id
674 ,schedule_id
675 ,transaction_create_date
676 ,schedule_source_code
677 ,campaign_source_code
678 ,schedule_activity_type
679 ,schedule_activity_id
680 ,campaign_purpose
681 ,campaign_type
682 ,start_date
683 ,end_date
684 ,schedule_purpose
685 ,business_unit_id
686 ,org_id
687 ,campaign_status
688 ,schedule_status
689 ,campaign_country
690 ,campaign_region
691 ,schedule_region
692 ,schedule_country
693 ,0 schedule_budget_fc
694 ,0 campaign_budget_fc
695 ,weekend_date
696 ,year
697 ,qtr
698 ,month
699 ,leads_open
700 ,leads_closed
701 ,leads_open_amt
702 ,leads_closed_amt
703 ,leads_new
704 ,leads_new_amt
705 ,leads_hot
706 ,leads_converted
707 ,leads_dead
708 ,opportunities
709 ,opportunity_amt
710 ,orders_booked
711 ,orders_booked_amt
712 ,forecasted_revenue
713 ,actual_revenue
714 ,forecasted_cost
715 ,actual_cost
716 ,forecasted_responses
717 ,positive_responses
718 ,0 targeted_customers
719 ,0 request_amount
720 ,0 approved_amount
721 FROM
722 (
723 SELECT
724 a.campaign_id campaign_id
725 ,0 schedule_id
726 ,inner.creation_date transaction_create_date
727 ,0 schedule_source_code
728 ,c.source_code_id campaign_source_code_id
729 ,0 schedule_source_code_id
730 ,a.source_code campaign_source_code
731 ,0 schedule_activity_type
732 ,0 schedule_activity_id
733 ,a.campaign_type campaign_purpose
734 ,a.rollup_type campaign_type
735 ,a.actual_exec_start_date start_date
736 ,a.actual_exec_end_date end_date
737 ,0 schedule_purpose
738 ,a.business_unit_id business_unit_id
739 ,0 org_id
740 ,a.status_code campaign_status
741 ,0 schedule_status
742 ,a.city_id campaign_country
743 ,d.area2_code campaign_region
744 ,0 schedule_region
745 ,0 schedule_country
746 ,(decode(decode( to_char(inner.creation_date,'MM') , to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
747 ,'TRUE'
748 ,decode(decode(inner.creation_date , (next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
749 ,'TRUE'
750 ,inner.creation_date
751 ,'FALSE'
752 ,next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
753 ,'FALSE'
754 ,decode(decode(to_char(inner.creation_date,'MM'),to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
755 ,'FALSE'
756 ,last_day(inner.creation_date)))) weekend_date
757 ,bim_set_of_books.get_fiscal_year(inner.creation_date,0) year
758 ,bim_set_of_books.get_fiscal_qtr(inner.creation_date,0) qtr
759 ,bim_set_of_books.get_fiscal_month(inner.creation_date,0) month
760 ,inner.leads_open leads_open
761 ,inner.leads_closed leads_closed
762 ,inner.leads_open_amt leads_open_amt
763 ,inner.leads_closed_amt leads_closed_amt
764 ,inner.leads_new leads_new
765 ,inner.leads_new_amt leads_new_amt
766 ,inner.leads_hot leads_hot
767 ,inner.leads_converted leads_converted
768 ,inner.leads_dead leads_dead
769 ,inner.opportunities opportunities
770 ,inner.opportunity_amt opportunity_amt
771 ,inner.orders_booked orders_booked
772 ,inner.orders_booked_amt orders_booked_amt
773 ,inner.forecasted_revenue forecasted_revenue
774 ,inner.actual_revenue actual_revenue
775 ,inner.forecasted_cost forecasted_cost
776 ,inner.actual_cost actual_cost
777 ,inner.forecasted_responses forecasted_responses
778 ,inner.positive_responses positive_responses
779 ,inner.targeted_customers targeted_customers
780 FROM (
781 SELECT
782 metric.campaign_id campaign_id
783 ,metric.creation_date creation_date
784 ,sum(nvl(metric.leads_open,0)) leads_open
785 ,sum(nvl(metric.leads_closed,0)) leads_closed
786 ,sum(nvl(metric.leads_open_amt,0)) leads_open_amt
787 ,sum(nvl(metric.leads_closed_amt,0)) leads_closed_amt
788 ,sum(nvl(metric.leads_new,0)) leads_new
789 ,sum(nvl(metric.leads_new_amt,0)) leads_new_amt
790 ,sum(nvl(metric.leads_hot,0)) leads_hot
791 ,sum(nvl(metric.leads_converted,0)) leads_converted
792 ,sum(nvl(metric.leads_dead,0)) leads_dead
793 ,sum(nvl(metric.opportunities,0)) opportunities
794 ,sum(nvl(metric.opportunity_amt,0)) opportunity_amt
795 ,sum(nvl(metric.orders_booked,0)) orders_booked
796 ,sum(nvl(metric.orders_booked_amt,0)) orders_booked_amt
797 ,sum(nvl(metric.forecasted_revenue,0)) forecasted_revenue
798 ,sum(nvl(metric.actual_revenue,0)) actual_revenue
799 ,sum(nvl(metric.forecasted_cost,0)) forecasted_cost
800 ,sum(nvl(metric.actual_cost,0)) actual_cost
801 ,sum(nvl(metric.forecasted_responses,0)) forecasted_responses
802 ,sum(nvl(metric.positive_responses,0)) positive_responses
803 ,0 targeted_customers
804 FROM (
805 SELECT
806 A.campaign_id campaign_id
807 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))creation_date
808 ,sum(decode(Y.opp_open_status_flag,'Y',1,0)) leads_open
809 ,sum(decode(Y.opp_open_status_flag,'Y',0,1)) leads_closed
810 ,sum(decode(Y.opp_open_status_flag,'Y',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0)) leads_open_amt
811 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0))) leads_closed_amt
812 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',1,0),0)) leads_new
813 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0),0)) leads_new_amt
814 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.lead_rank_id,10000,1,0),0)) leads_hot
815 ,sum(decode(Y.opp_open_status_flag,'N',decode(X.status_code,'CONVERTED_TO_OPPORTUNITY',1,0),0)) leads_converted
816 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',1,0))) leads_dead
817 ,0 opportunities
818 ,0 opportunity_amt
819 ,0 orders_booked
820 ,0 orders_booked_amt
821 ,0 forecasted_revenue
822 ,0 actual_revenue
823 ,0 forecasted_cost
824 ,0 actual_cost
825 ,0 forecasted_responses
826 ,0 positive_responses
827 ,0 targeted_customers
828 FROM
829 ams_campaigns_all_b A
830 ,ams_source_codes C
831 ,as_sales_leads X
832 ,as_statuses_b Y
833 WHERE
834 X.status_code = Y.status_code
835 AND A.campaign_id = C.source_code_for_id
836 AND C.arc_source_code_for = 'CAMP'
837 AND A.source_code = C.source_code
838 AND C.source_code_id = X.source_promotion_id
839 AND Y.lead_flag = 'Y'
840 AND Y.enabled_flag = 'Y'
841 AND NVL(X.DELETED_FLAG,'N') <> 'Y'
842 AND trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date)) between p_start_date and p_end_date+0.99999
843 GROUP BY
844 a.campaign_id
845 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))
846 UNION ALL
847 SELECT
848 A.campaign_id campaign_id
849 ,trunc(X.creation_date) creation_date
850 ,0 leads_open
851 ,0 leads_closed
852 ,0 leads_open_amt
853 ,0 leads_closed_amt
854 ,0 leads_new
855 ,0 leads_new_amt
856 ,0 leads_hot
857 ,0 leads_converted
858 ,0 leads_dead
859 ,count(distinct X.lead_id) opportunities
860 ,sum(convert_currency(nvl(X.currency_code,'USD'),nvl(X.total_amount,0))) opportunity_amt
861 ,0 orders_booked
862 ,0 orders_booked_amt
863 ,0 forecasted_revenue
864 ,0 actual_revenue
865 ,0 forecasted_cost
866 ,0 actual_cost
867 ,0 forecasted_responses
868 ,0 positive_responses
869 ,0 targeted_customers
870 FROM
871 ams_campaigns_all_b A
872 ,ams_source_codes C
873 ,as_leads_all X
874 WHERE
875 A.campaign_id = C.source_code_for_id
876 AND C.arc_source_code_for = 'CAMP'
877 AND A.source_code = C.source_code
878 AND C.source_code_id = X.source_promotion_id
879 AND trunc(X.creation_date) between p_start_date and p_end_date+0.99999
880 GROUP BY
881 A.campaign_id,trunc(X.creation_date)
882 UNION ALL
883 SELECT
884 A.campaign_id campaign_id
885 ,trunc(H.creation_date) creation_date
886 ,0 leads_open
887 ,0 leads_closed
888 ,0 leads_open_amt
889 ,0 leads_closed_amt
890 ,0 leads_new
891 ,0 leads_new_amt
892 ,0 leads_hot
893 ,0 leads_converted
894 ,0 leads_dead
895 ,0 opportunities
896 ,0 opportunity_amt
897 ,count(distinct(h.header_id)) orders_booked
898 ,sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD')
899 ,nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) orders_booked_amt
900 ,0 forecasted_revenue
901 ,0 actual_revenue
902 ,0 forecasted_cost
903 ,0 actual_cost
904 ,0 forecasted_responses
905 ,0 positive_responses
906 ,0 targeted_customers
907 FROM
908 ams_campaigns_all_b A
909 ,ams_source_codes C
910 ,as_sales_leads D
911 ,as_sales_lead_opportunity D1
912 ,as_leads_all E
913 ,aso_quote_related_objects F
914 ,aso_quote_headers_all G
915 ,oe_order_headers_all H
916 ,oe_order_lines_all I
917 WHERE
918 A.campaign_id = C.source_code_for_id
919 AND C.arc_source_code_for = 'CAMP'
920 AND A.source_code = C.source_code
921 AND C.source_code_id = D.source_promotion_id
922 AND D.sales_lead_id = D1.sales_lead_id
923 AND D1.opportunity_id = E.lead_id
924 AND E.lead_id = F.object_id
925 AND F.relationship_type_code = 'OPP_QUOTE'
926 AND F.quote_object_type_code = 'HEADER'
927 AND F.quote_object_id = G.quote_header_id
928 AND G.order_id = H.header_id
929 AND H.flow_status_code = 'BOOKED'
930 AND NVL(D.deleted_flag,'N') <> 'Y'
931 AND I.header_id = H.header_id
932 AND trunc(H.creation_date) between p_start_date and p_end_date+0.99999
933 GROUP BY
934 A.campaign_id,trunc(H.creation_date)
935 UNION ALL
936 SELECT
937 f3.act_metric_used_by_id campaign_id
938 ,trunc(f3.creation_date) creation_date
939 ,0 leads_open
940 ,0 leads_closed
941 ,0 leads_open_amt
942 ,0 leads_closed_amt
943 ,0 leads_new
944 ,0 leads_new_amt
945 ,0 leads_hot
946 ,0 leads_converted
947 ,0 leads_dead
948 ,0 opportunities
949 ,0 opportunity_amt
950 ,0 orders_booked
951 ,0 orders_booked_amt
952 ,sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_forecasted_delta,0)))
953 forecasted_revenue
954 ,sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_actual_delta,0)))
955 actual_revenue
956 ,0 forecasted_cost
957 ,0 actual_cost
958 ,0 forecasted_responses
959 ,0 positive_responses
960 ,0 targeted_customers
961 FROM
962 ams_act_metric_hst f3
963 ,ams_metrics_all_b g3
964 WHERE
965 f3.arc_act_metric_used_by = 'CAMP'
966 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
967 AND g3.metric_category = 902
968 AND g3.metric_id = f3.metric_id
969 AND trunc(f3.creation_date) between p_start_date and p_end_date+0.99999
970 GROUP BY
971 f3.act_metric_used_by_id,trunc(f3.creation_date)
972 HAVING
973 sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_forecasted_delta,0))) <> 0
974 OR
975 sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_actual_delta,0))) <> 0
976 UNION ALL
977 SELECT
978 f1.act_metric_used_by_id campaign_id
979 ,trunc(f1.creation_date) creation_date
980 ,0 leads_open
981 ,0 leads_closed
982 ,0 leads_open_amt
983 ,0 leads_closed_amt
984 ,0 leads_new
985 ,0 leads_new_amt
986 ,0 leads_hot
987 ,0 leads_converted
988 ,0 leads_dead
989 ,0 opportunities
990 ,0 opportunity_amt
991 ,0 orders_booked
992 ,0 orders_booked_amt
993 ,0 forecasted_revenue
994 ,0 actual_revenue
995 ,sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_forecasted_delta,0)))
996 forecasted_cost
997 ,sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_actual_delta,0)))
998 actual_cost
999 ,0 forecasted_responses
1000 ,0 positive_responses
1001 ,0 targeted_customers
1002 FROM
1003 ams_act_metric_hst f1
1004 ,ams_metrics_all_b g1
1005 WHERE
1006 f1.arc_act_metric_used_by = 'CAMP'
1007 AND g1.metric_category = 901
1008 AND g1.metric_id = f1.metric_id
1009 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
1010 AND trunc(f1.creation_date) between p_start_date and p_end_date+0.99999
1011 GROUP BY
1012 f1.act_metric_used_by_id,trunc(f1.creation_date)
1013 HAVING
1014 sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_forecasted_delta,0))) <> 0
1015 OR
1016 sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_actual_delta,0))) <> 0
1017 UNION ALL
1018 SELECT
1019 f3.act_metric_used_by_id campaign_id
1020 ,trunc(f3.creation_date) creation_date
1021 ,0 leads_open
1022 ,0 leads_closed
1023 ,0 leads_open_amt
1024 ,0 leads_closed_amt
1025 ,0 leads_new
1026 ,0 leads_new_amt
1027 ,0 leads_hot
1028 ,0 leads_converted
1029 ,0 leads_dead
1030 ,0 opportunities
1031 ,0 opportunity_amt
1032 ,0 orders_booked
1033 ,0 orders_booked_amt
1034 ,0 forecasted_revenue
1035 ,0 actual_revenue
1036 ,0 forecasted_cost
1037 ,0 actual_cost
1038 ,sum(nvl(f3.func_forecasted_delta,0)) forecasted_responses
1039 ,0 positive_responses
1040 ,0 targeted_customers
1041 FROM
1042 ams_act_metric_hst f3
1043 ,ams_metrics_all_b g3
1044 WHERE
1045 f3.arc_act_metric_used_by = 'CAMP'
1046 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
1047 AND g3.metric_category = 903
1048 AND g3.metric_id = f3.metric_id
1049 AND trunc(f3.creation_date) between p_start_date and p_end_date+0.99999
1050 GROUP BY
1051 f3.act_metric_used_by_id,trunc(f3.creation_date)
1052 HAVING
1053 sum(nvl(f3.func_forecasted_delta,0)) <> 0
1054 UNION ALL
1055 SELECT
1056 A.campaign_id campaign_id
1057 ,trunc(X.creation_date) creation_date
1058 ,0 leads_open
1059 ,0 leads_closed
1060 ,0 leads_open_amt
1061 ,0 leads_closed_amt
1062 ,0 leads_new
1063 ,0 leads_new_amt
1064 ,0 leads_hot
1065 ,0 leads_converted
1066 ,0 leads_dead
1067 ,0 opportunities
1068 ,0 opportunity_amt
1069 ,0 orders_booked
1070 ,0 orders_booked_amt
1071 ,0 forecasted_revenue
1072 ,0 actual_revenue
1073 ,0 forecasted_cost
1074 ,0 actual_cost
1075 ,0 forecasted_responses
1076 ,count(Y.result_id) positive_responses
1077 ,0 targeted_customers
1078 FROM ams_campaigns_all_b A
1079 ,jtf_ih_interactions X
1080 ,jtf_ih_results_b Y
1081 WHERE
1082 A.source_code = X.source_code
1083 AND X.result_id = Y.result_id
1084 AND Y.positive_response_flag = 'Y'
1085 AND trunc(X.creation_date) between p_start_date and p_end_date+0.99999
1086 GROUP BY
1087 A.campaign_id,trunc(X.creation_date)
1088 ) metric
1089 GROUP BY
1090 metric.campaign_id
1091 ,metric.creation_date
1092 ) inner
1093 ,ams_campaigns_all_b A
1094 ,ams_source_codes C
1095 ,jtf_loc_hierarchies_b D
1096 WHERE
1097 a.campaign_id = inner.campaign_id
1098 AND A.campaign_id = C.source_code_for_id
1099 AND C.arc_source_code_for = 'CAMP'
1100 AND A.source_code = C.source_code
1101 AND a.city_id = d.location_hierarchy_id
1102 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
1103 AND a.rollup_type <> 'RCAM'
1104 AND trunc(a.actual_exec_start_date) >= trunc(p_start_date)
1105 )Outer;
1106
1107 COMMIT;
1108
1109 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1110 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: AFTER FIRST INSERT ' || l_temp_msg);
1111
1112 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1113 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE SECOND INSERT ' || l_temp_msg);
1114
1115 INSERT /*+ append parallel(CDF,1) */
1116 INTO bim_r_camp_daily_facts CDF
1117 (
1118 campaign_daily_transaction_id
1119 ,creation_date
1120 ,last_update_date
1121 ,created_by
1122 ,last_updated_by
1123 ,last_update_login
1124 ,campaign_id
1125 ,schedule_id
1126 ,transaction_create_date
1127 ,schedule_source_code
1128 ,campaign_source_code
1129 ,schedule_activity_type
1130 ,schedule_activity_id
1131 ,campaign_purpose
1132 ,campaign_type
1133 ,start_date
1134 ,end_date
1135 ,schedule_purpose
1136 ,business_unit_id
1137 ,org_id
1138 ,campaign_status
1139 ,schedule_status
1140 ,campaign_country
1141 ,campaign_region
1142 ,schedule_region
1143 ,schedule_country
1144 ,campaign_budget_fc
1145 ,schedule_budget_fc
1146 ,load_date
1147 ,year
1148 ,qtr
1149 ,month
1150 ,leads_open
1151 ,leads_closed
1152 ,leads_open_amt
1153 ,leads_closed_amt
1154 ,leads_new
1155 ,leads_new_amt
1156 ,leads_hot
1157 ,leads_converted
1158 ,metric1 -- leads_dead
1159 ,opportunities
1160 ,opportunity_amt
1161 ,orders_booked
1162 ,orders_booked_amt
1163 ,forecasted_revenue
1164 ,actual_revenue
1165 ,forecasted_cost
1166 ,actual_cost
1167 ,forecasted_responses
1168 ,positive_responses
1169 ,targeted_customers
1170 ,budget_requested
1171 ,budget_approved
1172 )
1173 SELECT /*+ parallel(INNER,1) */
1174 bim_r_camp_daily_facts_s.nextval
1175 ,sysdate
1176 ,sysdate
1177 ,-1
1178 ,-1
1179 ,-1
1180 ,campaign_id
1181 ,schedule_id
1182 ,transaction_create_date
1183 ,schedule_source_code
1184 ,campaign_source_code
1185 ,schedule_activity_type
1186 ,schedule_activity_id
1187 ,campaign_purpose
1188 ,campaign_type
1189 ,start_date
1190 ,end_date
1191 ,schedule_purpose
1192 ,business_unit_id
1193 ,org_id
1194 ,campaign_status
1195 ,schedule_status
1196 ,campaign_country
1197 ,campaign_region
1198 ,schedule_region
1199 ,schedule_country
1200 ,0 schedule_budget_fc
1201 ,0 campaign_budget_fc
1202 ,weekend_date
1203 ,year
1204 ,qtr
1205 ,month
1206 ,leads_open
1207 ,leads_closed
1208 ,leads_open_amt
1209 ,leads_closed_amt
1210 ,leads_new
1211 ,leads_new_amt
1212 ,leads_hot
1213 ,leads_converted
1214 ,leads_dead
1215 ,opportunities
1216 ,opportunity_amt
1217 ,orders_booked
1218 ,orders_booked_amt
1219 ,0 forecasted_revenue
1220 ,0 actual_revenue
1221 ,0 forecasted_cost
1222 ,0 actual_cost
1223 ,0 forecasted_responses
1224 ,positive_responses
1225 ,targeted_customers
1226 ,0 request_amount
1227 ,0 approved_amount
1228 FROM
1229 (
1230 SELECT
1231 a.campaign_id campaign_id
1232 ,e.schedule_id schedule_id
1233 ,inner.creation_date transaction_create_date
1234 ,e.source_code schedule_source_code
1235 ,b2.source_code_id campaign_source_code_id
1236 ,b1.source_code_id schedule_source_code_id
1237 ,a.source_code campaign_source_code
1238 ,e.activity_type_code schedule_activity_type
1239 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id) schedule_activity_id
1240 ,a.campaign_type campaign_purpose
1241 ,a.rollup_type campaign_type
1242 ,e.start_date_time start_date
1243 ,nvl(e.end_date_time, a.actual_exec_end_date) end_date
1244 ,e.objective_code schedule_purpose
1245 ,a.business_unit_id business_unit_id
1246 ,e.org_id org_id
1247 ,a.status_code campaign_status
1248 ,e.status_code schedule_status
1249 ,a.city_id campaign_country
1250 ,d2.area2_code campaign_region
1251 ,d1.area2_code schedule_region
1252 ,e.country_id schedule_country
1253 ,(decode(decode( to_char(inner.creation_date,'MM') , to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
1254 ,'TRUE'
1255 ,decode(decode(inner.creation_date , (next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
1256 ,'TRUE'
1257 ,inner.creation_date
1258 ,'FALSE'
1259 ,next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
1260 ,'FALSE'
1261 ,decode(decode(to_char(inner.creation_date,'MM'),to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
1262 ,'FALSE'
1263 ,last_day(inner.creation_date)))) weekend_date
1264 ,bim_set_of_books.get_fiscal_year(inner.creation_date,0) year
1265 ,bim_set_of_books.get_fiscal_qtr(inner.creation_date,0) qtr
1266 ,bim_set_of_books.get_fiscal_month(inner.creation_date,0) month
1267 ,inner.leads_open leads_open
1268 ,inner.leads_closed leads_closed
1269 ,inner.leads_open_amt leads_open_amt
1270 ,inner.leads_closed_amt leads_closed_amt
1271 ,inner.leads_new leads_new
1272 ,inner.leads_new_amt leads_new_amt
1273 ,inner.leads_hot leads_hot
1274 ,inner.leads_converted leads_converted
1275 ,inner.leads_dead leads_dead
1276 ,inner.opportunities opportunities
1277 ,inner.opportunity_amt opportunity_amt
1278 ,inner.orders_booked orders_booked
1279 ,inner.orders_booked_amt orders_booked_amt
1280 ,inner.forecasted_revenue forecasted_revenue
1281 ,inner.actual_revenue actual_revenue
1282 ,inner.forecasted_cost forecasted_cost
1283 ,inner.actual_cost actual_cost
1284 ,inner.forecasted_responses forecasted_responses
1285 ,inner.positive_responses positive_responses
1286 ,inner.targeted_customers targeted_customers
1287 FROM (
1288 SELECT
1289 metric.schedule_id schedule_id
1290 ,metric.creation_date creation_date
1291 ,sum(nvl(metric.leads_open,0)) leads_open
1292 ,sum(nvl(metric.leads_closed,0)) leads_closed
1293 ,sum(nvl(metric.leads_open_amt,0)) leads_open_amt
1294 ,sum(nvl(metric.leads_closed_amt,0)) leads_closed_amt
1295 ,sum(nvl(metric.leads_new,0)) leads_new
1296 ,sum(nvl(metric.leads_new_amt,0)) leads_new_amt
1297 ,sum(nvl(metric.leads_hot,0)) leads_hot
1298 ,sum(nvl(metric.leads_converted,0)) leads_converted
1299 ,sum(nvl(metric.leads_dead,0)) leads_dead
1300 ,sum(nvl(metric.opportunities,0)) opportunities
1301 ,sum(nvl(metric.opportunity_amt,0)) opportunity_amt
1302 ,sum(nvl(metric.orders_booked,0)) orders_booked
1303 ,sum(nvl(metric.orders_booked_amt,0)) orders_booked_amt
1304 ,0 forecasted_revenue
1305 ,0 actual_revenue
1306 ,0 forecasted_cost
1307 ,0 actual_cost
1308 ,0 forecasted_responses
1309 ,sum(nvl(metric.positive_responses,0)) positive_responses
1310 ,sum(nvl(metric.targeted_customers,0)) targeted_customers
1311 FROM (
1312 SELECT
1313 A.schedule_id schedule_id
1314 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))creation_date
1315 ,sum(decode(Y.opp_open_status_flag,'Y',1,0)) leads_open
1316 ,sum(decode(Y.opp_open_status_flag,'Y',0,1)) leads_closed
1317 ,sum(decode(Y.opp_open_status_flag,'Y',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0)) leads_open_amt
1318 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0))) leads_closed_amt
1319 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',1,0),0)) leads_new
1320 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0),0)) leads_new_amt
1321 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.lead_rank_id,10000,1,0),0)) leads_hot
1322 ,sum(decode(Y.opp_open_status_flag,'N',decode(X.status_code,'CONVERTED_TO_OPPORTUNITY',1,0),0)) leads_converted
1323 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',1,0))) leads_dead
1324 ,0 opportunities
1325 ,0 opportunity_amt
1326 ,0 orders_booked
1327 ,0 orders_booked_amt
1328 ,0 forecasted_revenue
1329 ,0 actual_revenue
1330 ,0 forecasted_cost
1331 ,0 actual_cost
1332 ,0 forecasted_responses
1333 ,0 positive_responses
1334 ,0 targeted_customers
1335 FROM
1336 ams_campaign_schedules_b A
1337 ,ams_source_codes C
1338 ,as_sales_leads X
1339 ,as_statuses_b Y
1340 WHERE
1341 A.schedule_id = C.source_code_for_id
1342 AND C.arc_source_code_for = 'CSCH'
1343 AND A.source_code = C.source_code
1344 AND C.source_code_id = X.source_promotion_id
1345 AND X.status_code = Y.status_code
1346 AND Y.lead_flag = 'Y'
1347 AND Y.enabled_flag = 'Y'
1348 AND NVL(X.DELETED_FLAG,'N') <> 'Y'
1349 AND trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date)) between p_start_date and p_end_date+0.99999
1350 GROUP BY
1351 a.schedule_id
1352 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))
1353 UNION ALL
1354 SELECT
1355 A.schedule_id schedule_id
1356 ,trunc(X.creation_date) creation_date
1357 ,0 leads_open
1358 ,0 leads_closed
1359 ,0 leads_open_amt
1360 ,0 leads_closed_amt
1361 ,0 leads_new
1362 ,0 leads_new_amt
1363 ,0 leads_hot
1364 ,0 leads_converted
1365 ,0 leads_dead
1366 ,count(distinct X.lead_id) opportunities
1367 ,sum(convert_currency(nvl(X.currency_code,'USD'),nvl(X.total_amount,0))) opportunity_amt
1368 ,0 orders_booked
1369 ,0 orders_booked_amt
1370 ,0 forecasted_revenue
1371 ,0 actual_revenue
1372 ,0 forecasted_cost
1373 ,0 actual_cost
1374 ,0 forecasted_responses
1375 ,0 positive_responses
1376 ,0 targeted_customers
1377 FROM
1378 ams_campaign_schedules_b A
1379 ,ams_source_codes C
1380 ,as_leads_all X
1381 WHERE
1382 A.schedule_id = C.source_code_for_id
1383 AND C.arc_source_code_for = 'CSCH'
1384 AND A.source_code = C.source_code
1385 AND C.source_code_id = X.source_promotion_id
1386 AND trunc(X.creation_date) between p_start_date and p_end_date+0.99999
1387 GROUP BY
1388 A.schedule_id,trunc(X.creation_date)
1389 UNION ALL
1390 SELECT
1391 A.schedule_id schedule_id
1392 ,trunc(H.creation_date) creation_date
1393 ,0 leads_open
1394 ,0 leads_closed
1395 ,0 leads_open_amt
1396 ,0 leads_closed_amt
1397 ,0 leads_new
1398 ,0 leads_new_amt
1399 ,0 leads_hot
1400 ,0 leads_converted
1401 ,0 leads_dead
1402 ,0 opportunities
1403 ,0 opportunity_amt
1404 ,count(distinct(h.header_id)) orders_booked
1405 ,sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD')
1406 ,nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) orders_booked_amt
1407 ,0 forecasted_revenue
1408 ,0 actual_revenue
1409 ,0 forecasted_cost
1410 ,0 actual_cost
1411 ,0 forecasted_responses
1412 ,0 positive_responses
1413 ,0 targeted_customers
1414 FROM
1415 ams_campaign_schedules_b A
1416 ,ams_source_codes C
1417 ,as_sales_leads D
1418 ,as_sales_lead_opportunity D1
1419 ,as_leads_all E
1420 ,aso_quote_related_objects F
1421 ,aso_quote_headers_all G
1422 ,oe_order_headers_all H
1423 ,oe_order_lines_all I
1424 WHERE
1425 A.schedule_id = C.source_code_for_id
1426 AND C.arc_source_code_for = 'CSCH'
1427 AND A.source_code = C.source_code
1428 AND C.source_code_id = D.source_promotion_id
1429 AND D.sales_lead_id = D1.sales_lead_id
1430 AND D1.opportunity_id = E.lead_id
1431 AND E.lead_id = F.object_id
1432 AND F.relationship_type_code = 'OPP_QUOTE'
1433 AND F.quote_object_type_code = 'HEADER'
1434 AND F.quote_object_id = G.quote_header_id
1435 AND G.order_id = H.header_id
1436 AND H.flow_status_code = 'BOOKED'
1437 AND NVL(D.deleted_flag,'N') <> 'Y'
1438 AND I.header_id = H.header_id
1439 AND trunc(H.creation_date) between p_start_date and p_end_date+0.99999
1440 GROUP BY
1441 A.schedule_id,trunc(H.creation_date)
1442 UNION ALL
1443 SELECT
1444 f3.act_metric_used_by_id schedule_id
1445 ,trunc(f3.creation_date) creation_date
1446 ,0 leads_open
1447 ,0 leads_closed
1448 ,0 leads_open_amt
1449 ,0 leads_closed_amt
1450 ,0 leads_new
1451 ,0 leads_new_amt
1452 ,0 leads_hot
1453 ,0 leads_converted
1454 ,0 leads_dead
1455 ,0 opportunities
1456 ,0 opportunity_amt
1457 ,0 orders_booked
1458 ,0 orders_booked_amt
1459 ,sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_forecasted_delta,0)))
1460 forecasted_revenue
1461 ,sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_actual_delta,0)))
1462 actual_revenue
1463 ,0 forecasted_cost
1464 ,0 actual_cost
1465 ,0 forecasted_responses
1466 ,0 positive_responses
1467 ,0 targeted_customers
1468 FROM
1469 ams_act_metric_hst f3
1470 ,ams_metrics_all_b g3
1471 WHERE
1472 f3.arc_act_metric_used_by = 'CSCH'
1473 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
1474 AND g3.metric_category = 902
1475 AND g3.metric_id = f3.metric_id
1476 AND trunc(f3.creation_date) between p_start_date and p_end_date+0.99999
1477 GROUP BY
1478 f3.act_metric_used_by_id,trunc(f3.creation_date)
1479 HAVING
1480 sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_forecasted_delta,0))) <> 0
1481 OR
1482 sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_actual_delta,0))) <> 0
1483 UNION ALL
1484 SELECT
1485 f1.act_metric_used_by_id schedule_id
1486 ,trunc(f1.creation_date) creation_date
1487 ,0 leads_open
1488 ,0 leads_closed
1489 ,0 leads_open_amt
1490 ,0 leads_closed_amt
1491 ,0 leads_new
1492 ,0 leads_new_amt
1493 ,0 leads_hot
1494 ,0 leads_converted
1495 ,0 leads_dead
1496 ,0 opportunities
1497 ,0 opportunity_amt
1498 ,0 orders_booked
1499 ,0 orders_booked_amt
1500 ,0 forecasted_revenue
1501 ,0 actual_revenue
1502 ,sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_forecasted_delta,0)))
1503 forecasted_cost
1504 ,sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_actual_delta,0)))
1505 actual_cost
1506 ,0 forecasted_responses
1507 ,0 positive_responses
1508 ,0 targeted_customers
1509 FROM
1510 ams_act_metric_hst f1
1511 ,ams_metrics_all_b g1
1512 WHERE
1513 f1.arc_act_metric_used_by = 'CSCH'
1514 AND g1.metric_category = 901
1515 AND g1.metric_id = f1.metric_id
1516 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
1517 AND trunc(f1.creation_date) between p_start_date and p_end_date+0.99999
1518 GROUP BY
1519 f1.act_metric_used_by_id,trunc(f1.creation_date)
1520 HAVING
1521 sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_forecasted_delta,0))) <> 0
1522 OR
1523 sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_actual_delta,0))) <> 0
1524 UNION ALL
1525 SELECT
1526 f3.act_metric_used_by_id schedule_id
1527 ,trunc(f3.creation_date) creation_date
1528 ,0 leads_open
1529 ,0 leads_closed
1530 ,0 leads_open_amt
1531 ,0 leads_closed_amt
1532 ,0 leads_new
1533 ,0 leads_new_amt
1534 ,0 leads_hot
1535 ,0 leads_converted
1536 ,0 leads_dead
1537 ,0 opportunities
1538 ,0 opportunity_amt
1539 ,0 orders_booked
1540 ,0 orders_booked_amt
1541 ,0 forecasted_revenue
1542 ,0 actual_revenue
1543 ,0 forecasted_cost
1544 ,0 actual_cost
1545 ,sum(nvl(f3.func_forecasted_delta,0)) forecasted_responses
1546 ,0 positive_responses
1547 ,0 targeted_customers
1548 FROM
1549 ams_act_metric_hst f3
1550 ,ams_metrics_all_b g3
1551 WHERE
1552 f3.arc_act_metric_used_by = 'CSCH'
1553 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
1554 AND g3.metric_category = 903
1555 AND g3.metric_id = f3.metric_id
1556 AND trunc(f3.creation_date) between p_start_date and p_end_date+0.99999
1557 GROUP BY
1558 f3.act_metric_used_by_id,trunc(f3.creation_date)
1559 HAVING
1560 sum(nvl(f3.func_forecasted_delta,0)) <> 0
1561 UNION ALL
1562 SELECT
1563 A.schedule_id schedule_id
1564 ,trunc(X.creation_date) creation_date
1565 ,0 leads_open
1566 ,0 leads_closed
1567 ,0 leads_open_amt
1568 ,0 leads_closed_amt
1569 ,0 leads_new
1570 ,0 leads_new_amt
1571 ,0 leads_hot
1572 ,0 leads_converted
1573 ,0 leads_dead
1574 ,0 opportunities
1575 ,0 opportunity_amt
1576 ,0 orders_booked
1577 ,0 orders_booked_amt
1578 ,0 forecasted_revenue
1579 ,0 actual_revenue
1580 ,0 forecasted_cost
1581 ,0 actual_cost
1582 ,0 forecasted_responses
1583 ,sum(decode(A.use_parent_code_flag,'Y',0,1)) positive_responses
1584 ,0 targeted_customers
1585 FROM ams_campaign_schedules_b A
1586 ,jtf_ih_interactions X
1587 ,jtf_ih_results_b Y
1588 WHERE
1589 A.source_code = X.source_code
1590 AND X.result_id = Y.result_id
1591 AND Y.positive_response_flag = 'Y'
1592 AND trunc(X.creation_date) between p_start_date and p_end_date+0.99999
1593 GROUP BY
1594 A.schedule_id,trunc(X.creation_date)
1595 UNION ALL
1596 SELECT
1597 A.schedule_id schedule_id
1598 ,trunc(p.creation_date) creation_date
1599 ,0 leads_open
1600 ,0 leads_closed
1601 ,0 leads_open_amt
1602 ,0 leads_closed_amt
1603 ,0 leads_new
1604 ,0 leads_new_amt
1605 ,0 leads_hot
1606 ,0 leads_converted
1607 ,0 leads_dead
1608 ,0 opportunities
1609 ,0 opportunity_amt
1610 ,0 orders_booked
1611 ,0 orders_booked_amt
1612 ,0 forecasted_revenue
1613 ,0 actual_revenue
1614 ,0 forecasted_cost
1615 ,0 actual_cost
1616 ,0 forecasted_responses
1617 ,0 positive_responses
1618 ,count(p.list_entry_id) targeted_customers
1619 FROM
1620 ams_list_entries p
1621 ,ams_act_lists q
1622 ,ams_campaign_schedules_b A
1623 WHERE
1624 p.list_header_id = q.list_header_id
1625 AND q.list_used_by = 'CSCH'
1626 AND q.list_act_type = 'TARGET'
1627 AND trunc(p.creation_date) between p_start_date and p_end_date+0.99999
1628 AND q.list_used_by_id = A.schedule_id
1629 AND p.enabled_flag='Y'
1630 GROUP BY
1631 A.schedule_id, trunc(p.creation_date)
1632 UNION ALL
1633 SELECT
1634 A.schedule_id schedule_id
1635 ,trunc(p.creation_date) creation_date
1636 ,0 leads_open
1637 ,0 leads_closed
1638 ,0 leads_open_amt
1639 ,0 leads_closed_amt
1640 ,0 leads_new
1641 ,0 leads_new_amt
1642 ,0 leads_hot
1643 ,0 leads_converted
1644 ,0 leads_dead
1645 ,0 opportunities
1646 ,0 opportunity_amt
1647 ,0 orders_booked
1648 ,0 orders_booked_amt
1649 ,0 forecasted_revenue
1650 ,0 actual_revenue
1651 ,0 forecasted_cost
1652 ,0 actual_cost
1653 ,0 forecasted_responses
1654 ,0 positive_responses
1655 ,count(p.list_entry_id) targeted_customers
1656 FROM
1657 ams_list_entries p
1658 ,ams_act_lists q
1659 ,ams_campaign_schedules_b A
1660 WHERE
1661 trunc(p.creation_date) between p_start_date and p_end_date+0.99999
1662 AND p.list_header_id = q.list_header_id
1663 AND q.list_used_by = 'EONE'
1664 AND q.list_act_type = 'TARGET'
1665 AND A.activity_type_code = 'EVENTS'
1666 AND q.list_used_by_id = A.related_event_id
1667 AND p.enabled_flag='Y'
1668 GROUP BY
1669 A.schedule_id, trunc(p.creation_date)
1670 ) metric
1671 GROUP BY
1672 metric.schedule_id
1673 ,metric.creation_date
1674 ) inner
1675 ,ams_campaign_schedules_b E
1676 ,ams_campaigns_all_b A
1677 ,ams_source_codes B1
1678 ,ams_source_codes B2
1679 ,jtf_loc_hierarchies_b D1
1680 ,jtf_loc_hierarchies_b D2
1681 WHERE
1682 e.schedule_id = inner.schedule_id
1683 AND e.campaign_id = a.campaign_id
1684 AND e.country_id = d1.location_hierarchy_id
1685 AND a.city_id = d2.location_hierarchy_id
1686 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
1687 AND e.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
1688 AND a.rollup_type <> 'RCAM'
1689 AND b1.source_code_for_id = decode(e.source_code,a.source_code,a.campaign_id,e.schedule_id)
1690 AND b1.arc_source_code_for = decode(e.source_code,a.source_code,'CAMP','CSCH')
1691 AND b1.source_code = e.source_code
1692 AND b2.source_code_for_id = a.campaign_id
1693 AND b2.arc_source_code_for = 'CAMP'
1694 AND b2.source_code = a.source_code
1695 AND trunc(a.actual_exec_start_date) >= trunc(p_start_date)
1696 )Outer;
1697
1698 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1699 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: AFTER SECOND INSERT ' || l_temp_msg);
1700
1701 COMMIT;
1702 EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_camp_daily_facts_s CACHE 20';
1703
1704 /* This insert deals with the budgets for campaigns */
1705
1706 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1707 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE THIRD INSERT ' || l_temp_msg);
1708
1709 INSERT /*+ append parallel(CDF,1) */
1710 INTO bim_r_camp_daily_facts CDF
1711 (
1712 campaign_daily_transaction_id
1713 ,creation_date
1714 ,last_update_date
1715 ,created_by
1716 ,last_updated_by
1717 ,last_update_login
1718 ,campaign_id
1719 ,schedule_id
1720 ,transaction_create_date
1721 ,schedule_source_code
1722 ,campaign_source_code
1723 ,schedule_activity_type
1724 ,schedule_activity_id
1725 ,campaign_purpose
1726 ,campaign_type
1727 ,start_date
1728 ,end_date
1729 ,schedule_purpose
1730 ,business_unit_id
1731 ,org_id
1732 ,campaign_status
1733 ,schedule_status
1734 ,campaign_country
1735 ,campaign_region
1736 ,schedule_region
1737 ,schedule_country
1738 ,campaign_budget_fc
1739 ,schedule_budget_fc
1740 ,load_date
1741 ,year
1742 ,qtr
1743 ,month
1744 ,leads_open
1745 ,leads_closed
1746 ,leads_open_amt
1747 ,leads_closed_amt
1748 ,leads_new
1749 ,leads_new_amt
1750 ,leads_hot
1751 ,leads_converted
1752 ,metric1 --leads_dead
1753 ,opportunities
1754 ,opportunity_amt
1755 ,orders_booked
1756 ,orders_booked_amt
1757 ,forecasted_revenue
1758 ,actual_revenue
1759 ,forecasted_cost
1760 ,actual_cost
1761 ,forecasted_responses
1762 ,positive_responses
1763 ,targeted_customers
1764 ,budget_requested
1765 ,budget_approved
1766 )
1767 SELECT /*+ parallel(INNER,1) */
1768 bim_r_camp_daily_facts_s.nextval
1769 ,sysdate
1770 ,sysdate
1771 ,-1
1772 ,-1
1773 ,-1
1774 ,inner.act_budget_used_by_id campaign_id
1775 ,0 schedule_id
1776 ,inner.creation_date transaction_create_date
1777 ,0 schedule_source_code
1778 ,inner.campaign_source_code campaign_source_code
1779 ,0 schedule_activity_type
1780 ,0 schedule_activity_id
1781 ,inner.campaign_purpose campaign_purpose
1782 ,inner.campaign_type campaign_type
1783 ,inner.start_date start_date
1784 ,inner.end_date end_date
1785 ,0 schedule_purpose
1786 ,inner.business_unit_id business_unit_id
1787 ,0 org_id
1788 ,inner.campaign_status campaign_status
1789 ,0 schedule_status
1790 ,inner.campaign_country_code campaign_country
1791 ,inner.campaign_region_code campaign_region
1792 ,0 schedule_region
1793 ,0 schedule_country
1794 ,inner.campaign_budget_amount campaign_budget_fc
1795 ,0 schedule_budget_fc
1796 ,(decode(decode( to_char(inner.creation_date,'MM') , to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
1797 ,'TRUE'
1798 ,decode(decode(Inner.creation_date , (next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
1799 ,'TRUE'
1800 ,inner.creation_date
1801 ,'FALSE'
1802 ,next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
1803 ,'FALSE'
1804 ,decode(decode(to_char(inner.creation_date,'MM'),to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
1805 ,'FALSE'
1806 ,last_day(Inner.creation_date)))) weekend_date
1807 ,bim_set_of_books.get_fiscal_year(inner.creation_date,0) year
1808 ,bim_set_of_books.get_fiscal_qtr(inner.creation_date,0) qtr
1809 ,bim_set_of_books.get_fiscal_month(inner.creation_date,0) month
1810 ,0 leads_open
1811 ,0 leads_closed
1812 ,0 leads_open_amt
1813 ,0 leads_closed_amt
1814 ,0 leads_new
1815 ,0 leads_new_amt
1816 ,0 leads_hot
1817 ,0 leads_converted
1818 ,0 leads_dead
1819 ,0 opportunities
1820 ,0 opportunity_amt
1821 ,0 orders_booked
1822 ,0 orders_booked_amt
1823 ,0 forecasted_revenue
1824 ,0 actual_revenue
1825 ,0 forecasted_cost
1826 ,0 actual_cost
1827 ,0 forecasted_responses
1828 ,0 positive_responses
1829 ,0 targeted_customers
1830 ,inner.request_amount request_amount
1831 ,inner.approved_amount approved_amount
1832 FROM
1833 (
1834 SELECT
1835 s.act_budget_used_by_id act_budget_used_by_id
1836 ,decode(s.status_code
1837 ,'PENDING'
1838 ,trunc(nvl(s.request_date,s.creation_date))
1839 ,'APPROVED'
1840 ,trunc(nvl(s.approval_date,s.last_update_date))
1841 ) creation_date
1842 ,sum(decode(s.status_code
1843 ,'PENDING'
1844 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
1845 ,'APPROVED'
1846 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
1847 )) request_amount
1848 ,sum(decode(s.status_code
1849 ,'PENDING'
1850 ,0
1851 ,'APPROVED'
1852 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
1853 )) approved_amount
1854 ,b2.source_code_id campaign_source_code_id
1855 ,a.source_code campaign_source_code
1856 ,a.campaign_type campaign_purpose
1857 ,a.status_code campaign_status
1858 ,a.rollup_type campaign_type
1859 ,a.actual_exec_start_date start_date
1860 ,a.actual_exec_end_date end_date
1861 ,a.business_unit_id business_unit_id
1862 ,a.city_id campaign_country_code
1863 ,d.area2_code campaign_region_code
1864 ,a.budget_amount_fc campaign_budget_amount
1865 FROM ams_act_budgets S
1866 ,ams_campaigns_all_b A
1867 ,ams_source_codes B2
1868 ,jtf_loc_hierarchies_b D
1869 WHERE s.arc_act_budget_used_by = 'CAMP'
1870 AND s.budget_source_type = 'FUND'
1871 --AND s.transfer_type = 'REQUEST'
1872 AND s.act_budget_used_by_id = a.campaign_id
1873 AND a.city_id = d.location_hierarchy_id
1874 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
1875 AND a.rollup_type <> 'RCAM'
1876 AND b2.source_code = a.source_code
1877 AND a.actual_exec_start_date >= p_start_date
1878 AND a.actual_exec_start_date <= p_end_date
1879 AND decode(s.status_code,'PENDING',trunc(nvl(s.request_date,s.creation_date)),'APPROVED',trunc(nvl(s.approval_date,s.last_update_date)) ) <= p_end_date
1880 AND exists (select distinct campaign_id
1881 from ams_campaign_schedules_b x
1882 where x.campaign_id = a.campaign_id)
1883 GROUP BY s.act_budget_used_by_id
1884 ,decode(s.status_code,'PENDING',trunc(nvl(s.request_date,s.creation_date)),'APPROVED',trunc(nvl(s.approval_date,s.last_update_date)) )
1885 ,b2.source_code_id
1886 ,a.source_code
1887 ,a.campaign_type
1888 ,a.status_code
1889 ,a.rollup_type
1890 ,a.actual_exec_start_date
1891 ,a.actual_exec_end_date
1892 ,a.business_unit_id
1893 ,a.city_id
1894 ,d.area2_code
1895 ,a.budget_amount_fc
1896 HAVING sum(decode(s.status_code
1897 ,'PENDING'
1898 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
1899 ,'APPROVED'
1900 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
1901 )) > 0
1902 OR
1903 sum(decode(s.status_code
1904 ,'PENDING'
1905 ,0
1906 ,'APPROVED'
1907 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
1908 )) > 0
1909 UNION ALL
1910 SELECT
1911 s.budget_source_id act_budget_used_by_id
1912 ,trunc(nvl(s.approval_date,s.last_update_date)) creation_date
1913 ,-sum(convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))) request_amount
1914 , -sum(convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))) approved_amount
1915 ,b2.source_code_id campaign_source_code_id
1916 ,a.source_code campaign_source_code
1917 ,a.campaign_type campaign_purpose
1918 ,a.status_code campaign_status
1919 ,a.rollup_type campaign_type
1920 ,a.actual_exec_start_date start_date
1921 ,a.actual_exec_end_date end_date
1922 ,a.business_unit_id business_unit_id
1923 ,a.city_id campaign_country_code
1924 ,d.area2_code campaign_region_code
1925 ,a.budget_amount_fc campaign_budget_amount
1926 FROM ams_act_budgets S
1927 ,ams_campaigns_all_b A
1928 ,ams_source_codes B2
1929 ,jtf_loc_hierarchies_b D
1930 WHERE s.arc_act_budget_used_by = 'FUND'
1931 AND s.budget_source_type = 'CAMP'
1932 --AND s.transfer_type = 'REQUEST'
1933 AND s.budget_source_id = a.campaign_id
1934 AND a.city_id = d.location_hierarchy_id
1935 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
1936 AND a.rollup_type <> 'RCAM'
1937 AND b2.source_code = a.source_code
1938 AND a.actual_exec_start_date >= trunc(p_start_date)
1939 AND a.actual_exec_start_date <= trunc(p_end_date)
1940 AND s.approval_date <= trunc(p_end_date)
1941 AND exists (select distinct campaign_id
1942 from ams_campaign_schedules_b x
1943 where x.campaign_id = a.campaign_id)
1944 GROUP BY s.budget_source_id
1945 ,trunc(nvl(s.approval_date,s.last_update_date))
1946 ,b2.source_code_id
1947 ,a.source_code
1948 ,a.campaign_type
1949 ,a.status_code
1950 ,a.rollup_type
1951 ,a.actual_exec_start_date
1952 ,a.actual_exec_end_date
1953 ,a.business_unit_id
1954 ,a.city_id
1955 ,d.area2_code
1956 ,a.budget_amount_fc
1957 HAVING sum(decode(s.status_code
1958 ,'PENDING'
1959 ,0
1960 ,'APPROVED'
1961 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
1962 )) > 0
1963 )INNER;
1964
1965
1966
1967
1968 COMMIT;
1969
1970
1971 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1972 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: AFTER THIRD INSERT ' || l_temp_msg);
1973
1974
1975 /***************************************************************/
1976 /* This insert deals with schdule budgets */
1977
1978 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
1979 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE FOURTH INSERT ' || l_temp_msg);
1980
1981 INSERT /*+ append parallel(CDF,1) */
1982 INTO bim_r_camp_daily_facts CDF
1983 (
1984 campaign_daily_transaction_id
1985 ,creation_date
1986 ,last_update_date
1987 ,created_by
1988 ,last_updated_by
1989 ,last_update_login
1990 ,campaign_id
1991 ,schedule_id
1992 ,transaction_create_date
1993 ,schedule_source_code
1994 ,campaign_source_code
1995 ,schedule_activity_type
1996 ,schedule_activity_id
1997 ,campaign_purpose
1998 ,campaign_type
1999 ,start_date
2000 ,end_date
2001 ,schedule_purpose
2002 ,business_unit_id
2003 ,org_id
2004 ,campaign_status
2005 ,schedule_status
2006 ,campaign_country
2007 ,campaign_region
2008 ,schedule_region
2009 ,schedule_country
2010 ,campaign_budget_fc
2011 ,schedule_budget_fc
2012 ,load_date
2013 ,year
2014 ,qtr
2015 ,month
2016 ,leads_open
2017 ,leads_closed
2018 ,leads_open_amt
2019 ,leads_closed_amt
2020 ,leads_new
2021 ,leads_new_amt
2022 ,leads_hot
2023 ,leads_converted
2024 ,metric1 --leads_dead
2025 ,opportunities
2026 ,opportunity_amt
2027 ,orders_booked
2028 ,orders_booked_amt
2029 ,forecasted_revenue
2030 ,actual_revenue
2031 ,forecasted_cost
2032 ,actual_cost
2033 ,forecasted_responses
2034 ,positive_responses
2035 ,targeted_customers
2036 ,budget_requested
2037 ,budget_approved
2038 )
2039 SELECT /*+ parallel(INNER,1) */
2040 bim_r_camp_daily_facts_s.nextval
2041 ,sysdate
2042 ,sysdate
2043 ,-1
2044 ,-1
2045 ,-1
2046 ,inner.campaign_id campaign_id
2047 ,inner.schedule_id schedule_id
2048 ,inner.creation_date transaction_create_date
2049 ,inner.schedule_source_code schedule_source_code
2050 ,inner.campaign_source_code campaign_source_code
2051 ,inner.schedule_activity_type schedule_activity_type
2052 ,inner.schedule_activity_id schedule_activity_id
2053 ,inner.campaign_purpose campaign_purpose
2054 ,inner.campaign_type campaign_type
2055 ,inner.start_date start_date
2056 ,inner.end_date end_date
2057 ,inner.schedule_purpose schedule_purpose
2058 ,inner.business_unit_id business_unit_id
2059 ,inner.org_id org_id
2060 ,inner.campaign_status campaign_status
2061 ,inner.status_code schedule_status
2062 ,inner.campaign_country_code campaign_country
2063 ,inner.campaign_region_code campaign_region
2064 ,inner.schedule_region_code schedule_region
2065 ,inner.schedule_country_code schedule_country
2066 ,0 campaign_budget_fc
2067 ,inner.schedule_budget_amount schedule_budget_fc
2068 ,(decode(decode( to_char(inner.creation_date,'MM') , to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
2069 ,'TRUE'
2070 ,decode(decode(Inner.creation_date , (next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
2071 ,'TRUE'
2072 ,inner.creation_date
2073 ,'FALSE'
2074 ,next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
2075 ,'FALSE'
2076 ,decode(decode(to_char(inner.creation_date,'MM'),to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
2077 ,'FALSE'
2078 ,last_day(Inner.creation_date)))) weekend_date
2079 ,bim_set_of_books.get_fiscal_year(inner.creation_date,0) year
2080 ,bim_set_of_books.get_fiscal_qtr(inner.creation_date,0) qtr
2081 ,bim_set_of_books.get_fiscal_month(inner.creation_date,0) month
2082 ,0 leads_open
2083 ,0 leads_closed
2084 ,0 leads_open_amt
2085 ,0 leads_closed_amt
2086 ,0 leads_new
2087 ,0 leads_new_amt
2088 ,0 leads_hot
2089 ,0 leads_converted
2090 ,0 leads_dead
2091 ,0 opportunities
2092 ,0 opportunity_amt
2093 ,0 orders_booked
2094 ,0 orders_booked_amt
2095 ,0 forecasted_revenue
2096 ,0 actual_revenue
2097 ,0 forecasted_cost
2098 ,0 actual_cost
2099 ,0 forecasted_responses
2100 ,0 positive_responses
2101 ,0 targeted_customers
2102 ,inner.request_amount request_amount
2103 ,inner.approved_amount approved_amount
2104 FROM
2105 (
2106 SELECT
2107 s.act_budget_used_by_id act_budget_used_by_id
2108 ,decode(s.status_code
2109 ,'PENDING'
2110 ,trunc(nvl(s.request_date,s.creation_date))
2111 ,'APPROVED'
2112 ,trunc(nvl(s.approval_date,s.last_update_date))
2113 ) creation_date
2114 ,sum(decode(s.status_code
2115 ,'PENDING'
2116 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
2117 ,'APPROVED'
2118 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
2119 )) request_amount
2120 ,sum(decode(s.status_code
2121 ,'PENDING'
2122 ,0
2123 ,'APPROVED'
2124 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
2125 )) approved_amount
2126 ,a.campaign_id campaign_id
2127 ,b2.source_code_id campaign_source_code_id
2128 ,a.source_code campaign_source_code
2129 ,a.campaign_type campaign_purpose
2130 ,a.status_code campaign_status
2131 ,a.rollup_type campaign_type
2132 ,e.schedule_id schedule_id
2133 ,e.source_code schedule_source_code
2134 ,b1.source_code_id schedule_source_code_id
2135 ,e.activity_type_code schedule_activity_type
2136 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id) schedule_activity_id
2137 ,d1.area2_code schedule_region_code
2138 ,e.country_id schedule_country_code
2139 ,e.org_id org_id
2140 ,e.status_code status_code
2141 ,e.start_date_time start_date
2142 ,nvl(e.end_date_time, a.actual_exec_end_date) end_date
2143 ,e.objective_code schedule_purpose
2144 ,a.business_unit_id business_unit_id
2145 ,a.city_id campaign_country_code
2146 ,e.budget_amount_fc schedule_budget_amount
2147 ,d2.area2_code campaign_region_code
2148 FROM ams_act_budgets S
2149 ,ams_campaigns_all_b A
2150 ,ams_source_codes B1
2151 ,ams_source_codes B2
2152 ,jtf_loc_hierarchies_b D1
2153 ,jtf_loc_hierarchies_b D2
2154 ,ams_campaign_schedules_b E
2155 WHERE s.arc_act_budget_used_by = 'CSCH'
2156 AND s.budget_source_type = 'FUND'
2157 --AND s.transfer_type = 'REQUEST'
2158 AND s.act_budget_used_by_id = e.schedule_id
2159 AND e.campaign_id = a.campaign_id
2160 AND e.country_id = d1.location_hierarchy_id
2161 AND a.city_id = d2.location_hierarchy_id
2162 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
2163 --AND e.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
2164 AND a.rollup_type <> 'RCAM'
2165 AND b1.source_code = e.source_code
2166 AND b2.source_code = a.source_code
2167 AND a.actual_exec_start_date >= trunc(p_start_date)
2168 AND a.actual_exec_start_date <= trunc(p_end_date)
2169 AND decode(s.status_code,'PENDING',trunc(nvl(s.request_date,s.creation_date)),'APPROVED',trunc(nvl(s.approval_date,s.last_update_date)) ) <= trunc(p_end_date)
2170 GROUP BY s.act_budget_used_by_id
2171 ,decode(s.status_code,'PENDING',trunc(nvl(s.request_date,s.creation_date)),'APPROVED',trunc(nvl(s.approval_date,s.last_update_date)) )
2172 ,a.campaign_id
2173 ,b2.source_code_id
2174 ,a.source_code
2175 ,a.campaign_type
2176 ,a.status_code
2177 ,a.rollup_type
2178 ,e.schedule_id
2179 ,e.source_code
2180 ,b1.source_code_id
2181 ,e.activity_type_code
2182 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id)
2183 ,d1.area2_code
2184 ,e.country_id
2185 ,e.org_id
2186 ,e.status_code
2187 ,e.start_date_time
2188 ,nvl(e.end_date_time, a.actual_exec_end_date)
2189 ,e.objective_code
2190 ,a.business_unit_id
2191 ,a.city_id
2192 ,e.budget_amount_fc
2193 ,d2.area2_code
2194 HAVING sum(decode(s.status_code
2195 ,'PENDING'
2196 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
2197 ,'APPROVED'
2198 , convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
2199 )) > 0
2200 OR
2201 sum(decode(s.status_code
2202 ,'PENDING'
2203 ,0
2204 ,'APPROVED'
2205 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
2206 )) > 0
2207 UNION ALL
2208 SELECT
2209 s.budget_source_id act_budget_used_by_id
2210 ,trunc(nvl(s.approval_date,s.last_update_date)) creation_date
2211 -- ,0 request_amount
2212 , - sum(convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))) request_amount
2213 , -sum(convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))) approved_amount
2214 ,a.campaign_id campaign_id
2215 ,b2.source_code_id campaign_source_code_id
2216 ,a.source_code campaign_source_code
2217 ,a.campaign_type campaign_purpose
2218 ,a.status_code campaign_status
2219 ,a.rollup_type campaign_type
2220 ,e.schedule_id schedule_id
2221 ,e.source_code schedule_source_code
2222 ,b1.source_code_id schedule_source_code_id
2223 ,e.activity_type_code schedule_activity_type
2224 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id) schedule_activity_id
2225 ,d1.area2_code schedule_region_code
2226 ,e.country_id schedule_country_code
2227 ,e.org_id org_id
2228 ,e.status_code status_code
2229 ,e.start_date_time start_date
2230 ,nvl(e.end_date_time, a.actual_exec_end_date) end_date
2231 ,e.objective_code schedule_purpose
2232 ,a.business_unit_id business_unit_id
2233 ,a.city_id campaign_country_code
2234 ,e.budget_amount_fc schedule_budget_amount
2235 ,d2.area2_code campaign_region_code
2236 FROM ams_act_budgets S
2237 ,ams_campaigns_all_b A
2238 ,ams_source_codes B1
2239 ,ams_source_codes B2
2240 ,jtf_loc_hierarchies_b D1
2241 ,jtf_loc_hierarchies_b D2
2242 ,ams_campaign_schedules_b E
2243 WHERE s.arc_act_budget_used_by = 'FUND'
2244 AND s.budget_source_type = 'CSCH'
2245 --AND s.transfer_type = 'REQUEST'
2246 AND s.budget_source_id = e.schedule_id
2247 AND e.campaign_id = a.campaign_id
2248 AND e.country_id = d1.location_hierarchy_id
2249 AND a.city_id = d2.location_hierarchy_id
2250 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
2251 --AND e.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
2252 AND a.rollup_type <> 'RCAM'
2253 AND b1.source_code = e.source_code
2254 AND b2.source_code = a.source_code
2255 AND a.actual_exec_start_date >= trunc(p_start_date)
2256 AND a.actual_exec_start_date <= trunc(p_end_date)
2257 AND s.approval_date <= trunc(p_end_date)
2258 GROUP BY s.budget_source_id
2259 ,trunc(nvl(s.approval_date,s.last_update_date))
2260 ,a.campaign_id
2261 ,b2.source_code_id
2262 ,a.source_code
2263 ,a.campaign_type
2264 ,a.status_code
2265 ,a.rollup_type
2266 ,e.schedule_id
2267 ,e.source_code
2268 ,b1.source_code_id
2269 ,e.activity_type_code
2270 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id)
2271 ,d1.area2_code
2272 ,e.country_id
2273 ,e.org_id
2274 ,e.status_code
2275 ,e.start_date_time
2276 ,nvl(e.end_date_time, a.actual_exec_end_date)
2277 ,e.objective_code
2278 ,a.business_unit_id
2279 ,a.city_id
2280 ,e.budget_amount_fc
2281 ,d2.area2_code
2282 HAVING sum(decode(s.status_code
2283 ,'PENDING'
2284 ,0
2285 ,'APPROVED'
2286 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
2287 )) > 0
2288 )INNER;
2289
2290
2291 COMMIT;
2292
2293
2294 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2295 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: AFTER FOURTH INSERT ' || l_temp_msg);
2296
2297
2298
2299 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2300 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE ANALYZING DAILY FACTS ' || l_temp_msg);
2301
2302 -- Analyze the daily facts table
2303 DBMS_STATS.gather_table_stats('BIM','BIM_R_CAMP_DAILY_FACTS', estimate_percent => 5,
2304 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2305
2306 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2307 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: AFTER ANALYZING DAILY FACTS ' || l_temp_msg);
2308
2309 /***************************************************************/
2310
2311 /* INSERT INTO WEEKLY SUMMARY TABLE */
2312
2313 /* Here we are inserting the summarized data into the weekly facts by taking it from the daily facts.
2314 For every week we have a record since we group by that weekend date which is nothing but the Load date. */
2315
2316 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2317 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE WEEKLY TABLE INSERT ' || l_temp_msg);
2318
2319 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_r_camp_weekly_facts';
2320
2321 /*BEGIN BLOCK FOR THE WEEKLY INSERT */
2322
2323 l_table_name := 'bim_r_camp_weekly_facts';
2324 l_seq_name := 'bim_r_camp_weekly_facts_s';
2325
2326 INSERT /*+ append parallel(CWF,1) */
2327 INTO bim_r_camp_weekly_facts CWF
2328 (
2329 campaign_weekly_transaction_id
2330 ,creation_date
2331 ,last_update_date
2332 ,created_by
2333 ,last_updated_by
2334 ,campaign_id
2335 ,schedule_id
2336 ,campaign_source_code
2337 ,schedule_source_code
2338 ,campaign_type
2339 ,start_date
2340 ,end_date
2341 ,campaign_region
2342 ,schedule_region
2343 ,campaign_country
2344 ,schedule_country
2345 ,business_unit_id
2346 ,schedule_activity_type
2347 ,schedule_activity_id
2348 ,campaign_purpose
2349 ,campaign_status
2350 ,schedule_status
2351 ,transaction_create_date
2352 ,org_id
2353 ,load_date
2354 ,year
2355 ,qtr
2356 ,month
2357 ,leads_open
2358 ,leads_closed
2359 ,leads_open_amt
2360 ,leads_closed_amt
2361 ,leads_new
2362 ,leads_new_amt
2363 ,leads_hot
2364 ,leads_converted
2365 ,metric1 -- leads_dead
2366 ,opportunities
2367 ,opportunity_amt
2368 ,orders_booked
2369 ,orders_booked_amt
2370 ,forecasted_revenue
2371 ,actual_revenue
2372 ,forecasted_cost
2373 ,actual_cost
2374 ,forecasted_responses
2375 ,positive_responses
2376 ,targeted_customers
2377 ,budget_requested
2378 ,budget_approved
2379 )
2380 SELECT /*+ parallel(INNER,8) */
2381 bim_r_camp_weekly_facts_s.nextval
2382 ,sysdate
2383 ,sysdate
2384 ,l_user_id
2385 ,l_user_id
2386 ,campaign_id
2387 ,schedule_id
2388 ,campaign_source_code
2389 ,schedule_source_code
2390 ,campaign_type
2391 ,start_date
2392 ,end_date
2393 ,campaign_region
2394 ,schedule_region
2395 ,campaign_country
2396 ,schedule_country
2397 ,business_unit_id
2398 ,schedule_activity_type
2399 ,schedule_activity_id
2400 ,campaign_purpose
2401 ,campaign_status
2402 ,schedule_status
2403 ,transaction_create_date
2404 ,org_id
2405 ,load_date
2406 ,year
2407 ,qtr
2408 ,month
2409 ,leads_open
2410 ,leads_closed
2411 ,leads_open_amt
2412 ,leads_closed_amt
2413 ,leads_new
2414 ,leads_new_amt
2415 ,leads_hot
2416 ,leads_converted
2417 ,leads_dead
2418 ,opportunities
2419 ,opportunity_amt
2420 ,orders_booked
2421 ,orders_booked_amt
2422 ,forecasted_revenue
2423 ,actual_revenue
2424 ,forecasted_cost
2425 ,actual_cost
2426 ,forecasted_responses
2427 ,positive_responses
2428 ,targeted_customers
2429 ,budget_requested
2430 ,budget_approved
2431 FROM
2432 (
2433 SELECT
2434 campaign_id campaign_id
2435 ,schedule_id schedule_id
2436 ,campaign_source_code campaign_source_code
2437 ,schedule_source_code schedule_source_code
2438 ,campaign_type campaign_type
2439 ,start_date start_date
2440 ,end_date end_date
2441 ,campaign_region campaign_region
2442 ,schedule_region schedule_region
2443 ,campaign_country campaign_country
2444 ,schedule_country schedule_country
2445 ,nvl(business_unit_id,0) business_unit_id
2446 ,schedule_activity_type schedule_activity_type
2447 ,schedule_activity_id schedule_activity_id
2448 ,campaign_purpose campaign_purpose
2449 ,campaign_status campaign_status
2450 ,schedule_status schedule_status
2451 ,load_date transaction_create_date
2452 ,org_id org_id
2453 ,load_date load_date
2454 ,year year
2455 ,qtr qtr
2456 ,month month
2457 ,sum(leads_open) leads_open
2458 ,sum(leads_closed) leads_closed
2459 ,sum(leads_open_amt) leads_open_amt
2460 ,sum(leads_closed_amt) leads_closed_amt
2461 ,sum(leads_new) leads_new
2462 ,sum(leads_new_amt) leads_new_amt
2463 ,sum(leads_hot) leads_hot
2464 ,sum(leads_converted) leads_converted
2465 ,sum(metric1) leads_dead
2466 ,sum(opportunities) opportunities
2467 ,sum(opportunity_amt) opportunity_amt
2468 ,sum(orders_booked) orders_booked
2469 ,sum(orders_booked_amt) orders_booked_amt
2470 ,sum(forecasted_revenue) forecasted_revenue
2471 ,sum(actual_revenue) actual_revenue
2472 ,sum(forecasted_cost) forecasted_cost
2473 ,sum(actual_cost) actual_cost
2474 ,sum(forecasted_responses) forecasted_responses
2475 ,sum(positive_responses) positive_responses
2476 ,sum(targeted_customers) targeted_customers
2477 ,sum(budget_requested) budget_requested
2478 ,sum(budget_approved) budget_approved
2479 FROM bim_r_camp_daily_facts
2480 -- WHERE transaction_create_date between trunc(p_start_date) and trunc(p_end_date) + 0.99999
2481 GROUP BY campaign_id
2482 ,schedule_id
2483 ,load_date
2484 ,year
2485 ,qtr
2486 ,month
2487 ,campaign_source_code
2488 ,schedule_source_code
2489 ,campaign_type
2490 ,start_date
2491 ,end_date
2492 ,campaign_region
2493 ,schedule_region
2494 ,campaign_country
2495 ,schedule_country
2496 ,nvl(business_unit_id,0)
2497 ,schedule_activity_type
2498 ,schedule_activity_id
2499 ,campaign_purpose
2500 ,campaign_status
2501 ,schedule_status
2502 ,org_id
2503 )INNER;
2504
2505
2506
2507 COMMIT;
2508
2509
2510 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2511 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: AFTER WEEKLY TABLE INSERT ' || l_temp_msg);
2512
2513 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2514 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE ANALYZING WEEKLY FACTS ' || l_temp_msg);
2515
2516 -- Analyze the daily facts table
2517 DBMS_STATS.gather_table_stats('BIM','BIM_R_CAMP_WEEKLY_FACTS', estimate_percent => 5,
2518 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2519
2520 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2521 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: AFTER ANALYZING WEEKLY FACTS ' || l_temp_msg);
2522
2523
2524 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2525 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE COST UPDATE ' || l_temp_msg);
2526
2527
2528 -- Make entry in the history table
2529
2530 LOG_HISTORY('CAMPAIGN', p_start_date, p_end_date);
2531
2532
2533 /* Piece of Code for Recreating the index on the same tablespace with the same storage parameters */
2534
2535 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2536 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: BEFORE CREATE INDEX ' || l_temp_msg);
2537
2538 i := i - 1;
2539 WHILE(i>=1) LOOP
2540 EXECUTE IMMEDIATE 'CREATE INDEX '
2541 || l_owner(i)
2542 || '.'
2543 || l_index_name(i)
2544 ||' ON '
2545 || l_owner(i)
2546 ||'.'
2547 || l_index_table_name(i)
2548 || ' ('
2549 || l_ind_column_name(i)
2550 || ' )'
2551 || ' tablespace ' || l_index_tablespace
2552 || ' pctfree ' || l_pct_free(i)
2553 || ' initrans ' || l_ini_trans(i)
2554 || ' maxtrans ' || l_max_trans(i)
2555 || ' storage ( '
2556 || ' initial ' || l_initial_extent(i)
2557 || ' next ' || l_next_extent(i)
2558 || ' minextents ' || l_min_extents(i)
2559 || ' maxextents ' || l_max_extents(i)
2560 || ' pctincrease ' || l_pct_increase(i)
2561 || ')' ;
2562
2563 i := i - 1;
2564 END LOOP;
2565
2566 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2567 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD: AFTER CREATE INDEX ' || l_temp_msg);
2568
2569 /* End of Code for Recreating the index on the same tablespace with the same storage parameters */
2570
2571
2572 EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_camp_weekly_facts_s CACHE 20';
2573
2574
2575 EXCEPTION
2576
2577 WHEN FND_API.G_EXC_ERROR THEN
2578 x_return_status := FND_API.G_RET_STS_ERROR;
2579 -- Standard call to get message count and if count=1, get the message
2580 FND_msg_PUB.Count_And_Get (
2581 -- p_encoded => FND_API.G_FALSE,
2582 p_count => x_msg_count,
2583 p_data => x_msg_data
2584 );
2585
2586 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
2587
2588 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2589
2590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2591
2592 -- Standard call to get message count and if count=1, get the message
2593 FND_msg_PUB.Count_And_Get (
2594 --p_encoded => FND_API.G_FALSE,
2595 p_count => x_msg_count,
2596 p_data => x_msg_data
2597 );
2598
2599 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
2600
2601 WHEN OTHERS THEN
2602
2603 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2604
2605 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
2606 THEN
2607 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
2608 END IF;
2609
2610 -- Standard call to get message count and if count=1, get the message
2611 FND_msg_PUB.Count_And_Get (
2612 -- p_encoded => FND_API.G_FALSE,
2613 p_count => x_msg_count,
2614 p_data => x_msg_data
2615 );
2616
2617 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:FIRST_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
2618
2619 END CAMPAIGN_FIRST_LOAD;
2620
2621
2622 --------------------------------------------------------------------------------------------------
2623 -- This procedure will excute when data is loaded for the first time, and run the program incrementally.
2624
2625 -- PROCEDURE CAMPAIGN_SUBSEQUENT_LOAD
2626 --------------------------------------------------------------------------------------------------
2627
2628 PROCEDURE CAMPAIGN_SUBSEQUENT_LOAD
2629 ( p_start_date IN DATE
2630 ,p_end_date IN DATE
2631 ,p_api_version_number IN NUMBER
2632 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2633 ,p_load_type IN VARCHAR2
2634 ,x_msg_count OUT NOCOPY NUMBER
2635 ,x_msg_data OUT NOCOPY VARCHAR2
2636 ,x_return_status OUT NOCOPY VARCHAR2
2637 )
2638 IS
2639 l_user_id NUMBER := FND_GLOBAL.USER_ID();
2640 l_start_date DATE;
2641 l_end_date DATE;
2642 l_last_update_date DATE;
2643 l_success VARCHAR2(3);
2644 l_wkdt DATE;
2645 l_noleads NUMBER;
2646 l_nooppor NUMBER;
2647 l_orders NUMBER;
2648 l_noposresp NUMBER;
2649 l_revenue NUMBER;
2650 l_forecasted_cost NUMBER;
2651 l_actual_cost NUMBER;
2652 l_targeted_customer NUMBER;
2653 l_noofnew_customer NUMBER;
2654 l_temp NUMBER;
2655 l_tempo NUMBER;
2656 l_seq NUMBER;
2657 l_seqw NUMBER;
2658 l_api_version_number CONSTANT NUMBER := 1.0;
2659 l_api_name CONSTANT VARCHAR2(30) := 'CAMPAIGN_SUBSEQUENT_LOAD';
2660 l_seq_name VARCHAR(100);
2661 l_def_tablespace VARCHAR2(100);
2662 l_index_tablespace VARCHAR2(100);
2663 l_oracle_username VARCHAR2(100);
2664 l_table_name VARCHAR2(100);
2665 l_temp_msg VARCHAR2(100);
2666
2667 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2668
2669 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
2670
2671 l_pct_free generic_number_table;
2672 l_ini_trans generic_number_table;
2673 l_max_trans generic_number_table;
2674 l_initial_extent generic_number_table;
2675 l_next_extent generic_number_table;
2676 l_min_extents generic_number_table;
2677 l_max_extents generic_number_table;
2678 l_pct_increase generic_number_table;
2679
2680
2681 l_owner generic_char_table;
2682 l_index_name generic_char_table;
2683 l_ind_column_name generic_char_table;
2684 l_index_table_name generic_char_table;
2685 i NUMBER;
2686 l_min_start_date DATE;
2687
2688 l_status VARCHAR2(30);
2689 l_industry VARCHAR2(30);
2690 l_orcl_schema VARCHAR2(30);
2691 l_bol BOOLEAN := fnd_installation.get_app_info ('BIM',l_status,l_industry,l_orcl_schema);
2692
2693 CURSOR get_ts_name IS
2694 SELECT i.tablespace, i.index_tablespace, u.oracle_username
2695 FROM fnd_product_installations i, fnd_application a, fnd_oracle_userid u
2696 WHERE a.application_short_name = 'BIM'
2697 AND a.application_id = i.application_id
2698 AND u.oracle_id = i.oracle_id;
2699
2700 CURSOR get_index_params (l_schema VARCHAR2) IS
2701 SELECT a.owner,a.index_name,b.table_name,b.column_name,pct_free,ini_trans,max_trans
2702 ,initial_extent,next_extent,min_extents,
2703 max_extents, pct_increase
2704 FROM all_indexes a, all_ind_columns b
2705 WHERE a.index_name = b.index_name
2706 AND a.owner = l_schema
2707 AND a.owner = b.index_owner
2708 AND a.index_name like 'BIM_R_CAMP_%FACTS%';
2709
2710 CURSOR chk_history_data IS
2711 SELECT MIN(start_date)
2712 FROM bim_rep_history
2713 WHERE object = 'CAMPAIGN';
2714
2715 l_min_date date;
2716
2717 l_org_id number;
2718
2719 CURSOR get_org_id IS
2720 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
2721 FROM dual;
2722
2723 l_camp number;
2724 l_schema VARCHAR2(30);
2725 l_status1 VARCHAR2(5);
2726 l_industry1 VARCHAR2(5);
2727 l_return boolean;
2728 BEGIN
2729 l_return := fnd_installation.get_app_info('BIM', l_status1, l_industry1, l_schema);
2730 ams_utility_pvt.write_conc_log(p_start_date || ' '|| p_end_date);
2731 l_camp := 0;
2732
2733 -- Standard call to check for call compatibility.
2734 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2735 p_api_version_number,
2736 l_api_name,
2737 g_pkg_name)
2738 THEN
2739 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2740 END IF;
2741
2742 -- Initialize message list if p_init_msg_list is set to TRUE.
2743 IF FND_API.to_Boolean( p_init_msg_list )
2744 THEN
2745 FND_msg_PUB.initialize;
2746 END IF;
2747
2748 -- Initialize API return status to SUCCESS
2749 x_return_status := FND_API.G_RET_STS_SUCCESS;
2750
2751
2752 -- The below four commands are necessary for the purpose of the parallel insertion */
2753
2754 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
2755
2756 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_camp_daily_facts nologging ';
2757
2758 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_camp_weekly_facts nologging ';
2759
2760 EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_camp_daily_facts_s CACHE 1000 ';
2761
2762 EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_camp_weekly_facts_s CACHE 1000 ';
2763
2764 /*Get the tablespace name for the purpose of creating the index on that tablespace. */
2765
2766
2767 OPEN get_ts_name;
2768 FETCH get_ts_name INTO l_def_tablespace, l_index_tablespace, l_oracle_username;
2769 CLOSE get_ts_name;
2770
2771 OPEN get_org_id;
2772 FETCH get_org_id INTO l_org_id;
2773 CLOSE get_org_id;
2774
2775 IF p_load_type = 'FIRST_LOAD' THEN
2776 l_min_start_date := p_start_date;
2777 ELSE
2778 OPEN chk_history_data;
2779 FETCH chk_history_data INTO l_min_start_date;
2780 CLOSE chk_history_data;
2781 END IF;
2782
2783
2784 /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */
2785 i := 1;
2786 FOR x in get_index_params(l_orcl_schema) LOOP
2787
2788 l_pct_free(i) := x.pct_free;
2789 l_ini_trans(i) := x.ini_trans;
2790 l_max_trans(i) := x.max_trans;
2791 l_initial_extent(i) := x.initial_extent;
2792 l_next_extent(i) := x.next_extent;
2793 l_min_extents(i) := x.min_extents;
2794 l_max_extents(i) := x.max_extents;
2795 l_pct_increase(i) := x.pct_increase;
2796
2797 l_owner(i) := x.owner;
2798 l_index_name(i) := x.index_name;
2799 l_index_table_name(i) := x.table_name;
2800 l_ind_column_name(i) := x.column_name;
2801
2802
2803 -- Drop the index before the mass upload
2804
2805 EXECUTE IMMEDIATE 'DROP INDEX '|| l_owner(i) || '.'|| l_index_name(i) ;
2806 i := i + 1;
2807 END LOOP;
2808
2809 /* End of Code for dropping the existing indexes */
2810
2811
2812 -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');
2813
2814 l_org_id := 0;
2815
2816 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
2817 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: BEFORE FIRST INSERT ' || l_temp_msg);
2818
2819 INSERT /*+ append parallel(CDF,1) */
2820 INTO bim_r_camp_daily_facts CDF
2821 (
2822 campaign_daily_transaction_id
2823 ,creation_date
2824 ,last_update_date
2825 ,created_by
2826 ,last_updated_by
2827 ,last_update_login
2828 ,campaign_id
2829 ,schedule_id
2830 ,transaction_create_date
2831 ,schedule_source_code
2832 ,campaign_source_code
2833 ,schedule_activity_type
2834 ,schedule_activity_id
2835 ,campaign_purpose
2836 ,campaign_type
2837 ,start_date
2838 ,end_date
2839 ,schedule_purpose
2840 ,business_unit_id
2841 ,org_id
2842 ,campaign_status
2843 ,schedule_status
2844 ,campaign_country
2845 ,campaign_region
2846 ,schedule_region
2847 ,schedule_country
2848 ,campaign_budget_fc
2849 ,schedule_budget_fc
2850 ,load_date
2851 ,year
2852 ,qtr
2853 ,month
2854 ,leads_open
2855 ,leads_closed
2856 ,leads_open_amt
2857 ,leads_closed_amt
2858 ,leads_new
2859 ,leads_new_amt
2860 ,leads_hot
2861 ,leads_converted
2862 ,metric1 -- leads_dead
2863 ,opportunities
2864 ,opportunity_amt
2865 ,orders_booked
2866 ,orders_booked_amt
2867 ,forecasted_revenue
2868 ,actual_revenue
2869 ,forecasted_cost
2870 ,actual_cost
2871 ,forecasted_responses
2872 ,positive_responses
2873 ,targeted_customers
2874 ,budget_requested
2875 ,budget_approved
2876 )
2877 SELECT /*+ parallel(OUTER,1) */
2878 bim_r_camp_daily_facts_s.nextval
2879 ,sysdate
2880 ,sysdate
2881 ,-1
2882 ,-1
2883 ,-1
2884 ,campaign_id
2885 ,schedule_id
2886 ,transaction_create_date
2887 ,schedule_source_code
2888 ,campaign_source_code
2889 ,schedule_activity_type
2890 ,schedule_activity_id
2891 ,campaign_purpose
2892 ,campaign_type
2893 ,start_date
2894 ,end_date
2895 ,schedule_purpose
2896 ,business_unit_id
2897 ,org_id
2898 ,campaign_status
2899 ,schedule_status
2900 ,campaign_country
2901 ,campaign_region
2902 ,schedule_region
2903 ,schedule_country
2904 ,0 schedule_budget_fc
2905 ,0 campaign_budget_fc
2906 ,weekend_date
2907 ,year
2908 ,qtr
2909 ,month
2910 ,leads_open
2911 ,leads_closed
2912 ,leads_open_amt
2913 ,leads_closed_amt
2914 ,leads_new
2915 ,leads_new_amt
2916 ,leads_hot
2917 ,leads_converted
2918 ,leads_dead
2919 ,opportunities
2920 ,opportunity_amt
2921 ,orders_booked
2922 ,orders_booked_amt
2923 ,forecasted_revenue
2924 ,actual_revenue
2925 ,forecasted_cost
2926 ,actual_cost
2927 ,forecasted_responses
2928 ,positive_responses
2929 ,0 targeted_customers
2930 ,0 request_amount
2931 ,0 approved_amount
2932 FROM
2933 (
2934 SELECT
2935 a.campaign_id campaign_id
2936 ,0 schedule_id
2937 ,inner.creation_date transaction_create_date
2938 ,0 schedule_source_code
2939 ,c.source_code_id campaign_source_code_id
2940 ,0 schedule_source_code_id
2941 ,a.source_code campaign_source_code
2942 ,0 schedule_activity_type
2943 ,0 schedule_activity_id
2944 ,a.campaign_type campaign_purpose
2945 ,a.rollup_type campaign_type
2946 ,a.actual_exec_start_date start_date
2947 ,a.actual_exec_end_date end_date
2948 ,0 schedule_purpose
2949 ,a.business_unit_id business_unit_id
2950 ,0 org_id
2951 ,a.status_code campaign_status
2952 ,0 schedule_status
2953 ,a.city_id campaign_country
2954 ,d.area2_code campaign_region
2955 ,0 schedule_region
2956 ,0 schedule_country
2957 ,(decode(decode( to_char(inner.creation_date,'MM') , to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
2958 ,'TRUE'
2959 ,decode(decode(inner.creation_date , (next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
2960 ,'TRUE'
2961 ,inner.creation_date
2962 ,'FALSE'
2963 ,next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
2964 ,'FALSE'
2965 ,decode(decode(to_char(inner.creation_date,'MM'),to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
2966 ,'FALSE'
2967 ,last_day(inner.creation_date)))) weekend_date
2968 ,bim_set_of_books.get_fiscal_year(inner.creation_date,0) year
2969 ,bim_set_of_books.get_fiscal_qtr(inner.creation_date,0) qtr
2970 ,bim_set_of_books.get_fiscal_month(inner.creation_date,0) month
2971 ,inner.leads_open leads_open
2972 ,inner.leads_closed leads_closed
2973 ,inner.leads_open_amt leads_open_amt
2974 ,inner.leads_closed_amt leads_closed_amt
2975 ,inner.leads_new leads_new
2976 ,inner.leads_new_amt leads_new_amt
2977 ,inner.leads_hot leads_hot
2978 ,inner.leads_converted leads_converted
2979 ,inner.leads_dead leads_dead
2980 ,inner.opportunities opportunities
2981 ,inner.opportunity_amt opportunity_amt
2982 ,inner.orders_booked orders_booked
2983 ,inner.orders_booked_amt orders_booked_amt
2984 ,inner.forecasted_revenue forecasted_revenue
2985 ,inner.actual_revenue actual_revenue
2986 ,inner.forecasted_cost forecasted_cost
2987 ,inner.actual_cost actual_cost
2988 ,inner.forecasted_responses forecasted_responses
2989 ,inner.positive_responses positive_responses
2990 ,inner.targeted_customers targeted_customers
2991 FROM (
2992 SELECT
2993 metric.campaign_id campaign_id
2994 ,metric.creation_date creation_date
2995 ,sum(nvl(metric.leads_open,0)) leads_open
2996 ,sum(nvl(metric.leads_closed,0)) leads_closed
2997 ,sum(nvl(metric.leads_open_amt,0)) leads_open_amt
2998 ,sum(nvl(metric.leads_closed_amt,0)) leads_closed_amt
2999 ,sum(nvl(metric.leads_new,0)) leads_new
3000 ,sum(nvl(metric.leads_new_amt,0)) leads_new_amt
3001 ,sum(nvl(metric.leads_hot,0)) leads_hot
3002 ,sum(nvl(metric.leads_converted,0)) leads_converted
3003 ,sum(nvl(metric.leads_dead,0)) leads_dead
3004 ,sum(nvl(metric.opportunities,0)) opportunities
3005 ,sum(nvl(metric.opportunity_amt,0)) opportunity_amt
3006 ,sum(nvl(metric.orders_booked,0)) orders_booked
3007 ,sum(nvl(metric.orders_booked_amt,0)) orders_booked_amt
3008 ,sum(nvl(metric.forecasted_revenue,0)) forecasted_revenue
3009 ,sum(nvl(metric.actual_revenue,0)) actual_revenue
3010 ,sum(nvl(metric.forecasted_cost,0)) forecasted_cost
3011 ,sum(nvl(metric.actual_cost,0)) actual_cost
3012 ,sum(nvl(metric.forecasted_responses,0)) forecasted_responses
3013 ,sum(nvl(metric.positive_responses,0)) positive_responses
3014 ,0 targeted_customers
3015 FROM (
3016 SELECT
3017 A.campaign_id campaign_id
3018 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))creation_date
3019 ,sum(decode(Y.opp_open_status_flag,'Y',1,0)) leads_open
3020 ,sum(decode(Y.opp_open_status_flag,'Y',0,1)) leads_closed
3021 ,sum(decode(Y.opp_open_status_flag,'Y',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0)) leads_open_amt
3022 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0))) leads_closed_amt
3023 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',1,0),0)) leads_new
3024 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0),0)) leads_new_amt
3025 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.lead_rank_id,10000,1,0),0)) leads_hot
3026 ,sum(decode(Y.opp_open_status_flag,'N',decode(X.status_code,'CONVERTED_TO_OPPORTUNITY',1,0),0)) leads_converted
3027 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',1,0))) leads_dead
3028 ,0 opportunities
3029 ,0 opportunity_amt
3030 ,0 orders_booked
3031 ,0 orders_booked_amt
3032 ,0 forecasted_revenue
3033 ,0 actual_revenue
3034 ,0 forecasted_cost
3035 ,0 actual_cost
3036 ,0 forecasted_responses
3037 ,0 positive_responses
3038 ,0 targeted_customers
3039 FROM
3040 ams_campaigns_all_b A
3041 ,ams_source_codes C
3042 ,as_sales_leads X
3043 ,as_statuses_b Y
3044 WHERE
3045 X.status_code = Y.status_code
3046 AND A.campaign_id = C.source_code_for_id
3047 AND C.arc_source_code_for = 'CAMP'
3048 AND A.source_code = C.source_code
3049 AND C.source_code_id = X.source_promotion_id
3050 AND Y.lead_flag = 'Y'
3051 AND Y.enabled_flag = 'Y'
3052 AND NVL(X.DELETED_FLAG,'N') <> 'Y'
3053 AND trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date)) between p_start_date and p_end_date+0.99999
3054 GROUP BY
3055 a.campaign_id
3056 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))
3057 UNION ALL
3058 SELECT
3059 A.campaign_id campaign_id
3060 ,trunc(X.creation_date) creation_date
3061 ,0 leads_open
3062 ,0 leads_closed
3063 ,0 leads_open_amt
3064 ,0 leads_closed_amt
3065 ,0 leads_new
3066 ,0 leads_new_amt
3067 ,0 leads_hot
3068 ,0 leads_converted
3069 ,0 leads_dead
3070 ,count(distinct X.lead_id) opportunities
3071 ,sum(convert_currency(nvl(X.currency_code,'USD'),nvl(X.total_amount,0))) opportunity_amt
3072 ,0 orders_booked
3073 ,0 orders_booked_amt
3074 ,0 forecasted_revenue
3075 ,0 actual_revenue
3076 ,0 forecasted_cost
3077 ,0 actual_cost
3078 ,0 forecasted_responses
3079 ,0 positive_responses
3080 ,0 targeted_customers
3081 FROM
3082 ams_campaigns_all_b A
3083 ,ams_source_codes C
3084 ,as_leads_all X
3085 WHERE
3086 A.campaign_id = C.source_code_for_id
3087 AND C.arc_source_code_for = 'CAMP'
3088 AND A.source_code = C.source_code
3089 AND C.source_code_id = X.source_promotion_id
3090 AND trunc(X.creation_date) between p_start_date and p_end_date+0.99999
3091 GROUP BY
3092 A.campaign_id,trunc(X.creation_date)
3093 UNION ALL
3094 SELECT
3095 A.campaign_id campaign_id
3096 ,trunc(H.creation_date) creation_date
3097 ,0 leads_open
3098 ,0 leads_closed
3099 ,0 leads_open_amt
3100 ,0 leads_closed_amt
3101 ,0 leads_new
3102 ,0 leads_new_amt
3103 ,0 leads_hot
3104 ,0 leads_converted
3105 ,0 leads_dead
3106 ,0 opportunities
3107 ,0 opportunity_amt
3108 ,count(distinct(h.header_id)) orders_booked
3109 ,sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD')
3110 ,nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) orders_booked_amt
3111 ,0 forecasted_revenue
3112 ,0 actual_revenue
3113 ,0 forecasted_cost
3114 ,0 actual_cost
3115 ,0 forecasted_responses
3116 ,0 positive_responses
3117 ,0 targeted_customers
3118 FROM
3119 ams_campaigns_all_b A
3120 ,ams_source_codes C
3121 ,as_sales_leads D
3122 ,as_sales_lead_opportunity D1
3123 ,as_leads_all E
3124 ,aso_quote_related_objects F
3125 ,aso_quote_headers_all G
3126 ,oe_order_headers_all H
3127 ,oe_order_lines_all I
3128 WHERE
3129 A.campaign_id = C.source_code_for_id
3130 AND C.arc_source_code_for = 'CAMP'
3131 AND A.source_code = C.source_code
3132 AND C.source_code_id = D.source_promotion_id
3133 AND D.sales_lead_id = D1.sales_lead_id
3134 AND D1.opportunity_id = E.lead_id
3135 AND E.lead_id = F.object_id
3136 AND F.relationship_type_code = 'OPP_QUOTE'
3137 AND F.quote_object_type_code = 'HEADER'
3138 AND F.quote_object_id = G.quote_header_id
3139 AND G.order_id = H.header_id
3140 AND H.flow_status_code = 'BOOKED'
3141 AND NVL(D.deleted_flag,'N') <> 'Y'
3142 AND I.header_id = H.header_id
3143 AND trunc(H.creation_date) between p_start_date and p_end_date+0.99999
3144 GROUP BY
3145 A.campaign_id,trunc(H.creation_date)
3146 UNION ALL
3147 SELECT
3148 f3.act_metric_used_by_id campaign_id
3149 ,trunc(f3.creation_date) creation_date
3150 ,0 leads_open
3151 ,0 leads_closed
3152 ,0 leads_open_amt
3153 ,0 leads_closed_amt
3154 ,0 leads_new
3155 ,0 leads_new_amt
3156 ,0 leads_hot
3157 ,0 leads_converted
3158 ,0 leads_dead
3159 ,0 opportunities
3160 ,0 opportunity_amt
3161 ,0 orders_booked
3162 ,0 orders_booked_amt
3163 ,sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_forecasted_delta,0)))
3164 forecasted_revenue
3165 ,sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_actual_delta,0)))
3166 actual_revenue
3167 ,0 forecasted_cost
3168 ,0 actual_cost
3169 ,0 forecasted_responses
3170 ,0 positive_responses
3171 ,0 targeted_customers
3172 FROM
3173 ams_act_metric_hst f3
3174 ,ams_metrics_all_b g3
3175 WHERE
3176 f3.arc_act_metric_used_by = 'CAMP'
3177 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
3178 AND g3.metric_category = 902
3179 AND g3.metric_id = f3.metric_id
3180 AND trunc(f3.creation_date) between p_start_date and p_end_date+0.99999
3181 GROUP BY
3182 f3.act_metric_used_by_id,trunc(f3.creation_date)
3183 HAVING
3184 sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_forecasted_delta,0))) <> 0
3185 OR
3186 sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_actual_delta,0))) <> 0
3187 UNION ALL
3188 SELECT
3189 f1.act_metric_used_by_id campaign_id
3190 ,trunc(f1.creation_date) creation_date
3191 ,0 leads_open
3192 ,0 leads_closed
3193 ,0 leads_open_amt
3194 ,0 leads_closed_amt
3195 ,0 leads_new
3196 ,0 leads_new_amt
3197 ,0 leads_hot
3198 ,0 leads_converted
3199 ,0 leads_dead
3200 ,0 opportunities
3201 ,0 opportunity_amt
3202 ,0 orders_booked
3203 ,0 orders_booked_amt
3204 ,0 forecasted_revenue
3205 ,0 actual_revenue
3206 ,sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_forecasted_delta,0)))
3207 forecasted_cost
3208 ,sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_actual_delta,0)))
3209 actual_cost
3210 ,0 forecasted_responses
3211 ,0 positive_responses
3212 ,0 targeted_customers
3213 FROM
3214 ams_act_metric_hst f1
3215 ,ams_metrics_all_b g1
3216 WHERE
3217 f1.arc_act_metric_used_by = 'CAMP'
3218 AND g1.metric_category = 901
3219 AND g1.metric_id = f1.metric_id
3220 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
3221 AND trunc(f1.creation_date) between p_start_date and p_end_date+0.99999
3222 GROUP BY
3223 f1.act_metric_used_by_id,trunc(f1.creation_date)
3224 HAVING
3225 sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_forecasted_delta,0))) <> 0
3226 OR
3227 sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_actual_delta,0))) <> 0
3228 UNION ALL
3229 SELECT
3230 f3.act_metric_used_by_id campaign_id
3231 ,trunc(f3.creation_date) creation_date
3232 ,0 leads_open
3233 ,0 leads_closed
3234 ,0 leads_open_amt
3235 ,0 leads_closed_amt
3236 ,0 leads_new
3237 ,0 leads_new_amt
3238 ,0 leads_hot
3239 ,0 leads_converted
3240 ,0 leads_dead
3241 ,0 opportunities
3242 ,0 opportunity_amt
3243 ,0 orders_booked
3244 ,0 orders_booked_amt
3245 ,0 forecasted_revenue
3246 ,0 actual_revenue
3247 ,0 forecasted_cost
3248 ,0 actual_cost
3249 ,sum(nvl(f3.func_forecasted_delta,0)) forecasted_responses
3250 ,0 positive_responses
3251 ,0 targeted_customers
3252 FROM
3253 ams_act_metric_hst f3
3254 ,ams_metrics_all_b g3
3255 WHERE
3256 f3.arc_act_metric_used_by = 'CAMP'
3257 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
3258 AND g3.metric_category = 903
3259 AND g3.metric_id = f3.metric_id
3260 AND trunc(f3.creation_date) between p_start_date and p_end_date+0.99999
3261 GROUP BY
3262 f3.act_metric_used_by_id,trunc(f3.creation_date)
3263 HAVING
3264 sum(nvl(f3.func_forecasted_delta,0)) <> 0
3265 UNION ALL
3266 SELECT
3267 A.campaign_id campaign_id
3268 ,trunc(X.creation_date) creation_date
3269 ,0 leads_open
3270 ,0 leads_closed
3271 ,0 leads_open_amt
3272 ,0 leads_closed_amt
3273 ,0 leads_new
3274 ,0 leads_new_amt
3275 ,0 leads_hot
3276 ,0 leads_converted
3277 ,0 leads_dead
3278 ,0 opportunities
3279 ,0 opportunity_amt
3280 ,0 orders_booked
3281 ,0 orders_booked_amt
3282 ,0 forecasted_revenue
3283 ,0 actual_revenue
3284 ,0 forecasted_cost
3285 ,0 actual_cost
3286 ,0 forecasted_responses
3287 ,count(Y.result_id) positive_responses
3288 ,0 targeted_customers
3289 FROM ams_campaigns_all_b A
3290 ,jtf_ih_interactions X
3291 ,jtf_ih_results_b Y
3292 WHERE
3293 A.source_code = X.source_code
3294 AND X.result_id = Y.result_id
3295 AND Y.positive_response_flag = 'Y'
3296 AND trunc(X.creation_date) between p_start_date and p_end_date+0.99999
3297 GROUP BY
3298 A.campaign_id,trunc(X.creation_date)
3299 ) metric
3300 GROUP BY
3301 metric.campaign_id
3302 ,metric.creation_date
3303 ) inner
3304 ,ams_campaigns_all_b A
3305 ,ams_source_codes C
3306 ,jtf_loc_hierarchies_b D
3307 WHERE
3308 a.campaign_id = inner.campaign_id
3309 AND A.campaign_id = C.source_code_for_id
3310 AND C.arc_source_code_for = 'CAMP'
3311 AND A.source_code = C.source_code
3312 AND a.city_id = d.location_hierarchy_id
3313 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
3314 AND a.rollup_type <> 'RCAM'
3315 AND trunc(a.actual_exec_start_date) >= trunc(l_min_start_date)
3316 )Outer;
3317
3318 COMMIT;
3319
3320 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3321 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: AFTER FIRST INSERT ' || l_temp_msg);
3322
3323
3324 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3325 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: BEFORE SECOND INSERT ' || l_temp_msg);
3326
3327 INSERT /*+ append parallel(CDF,1) */
3328 INTO bim_r_camp_daily_facts CDF
3329 (
3330 campaign_daily_transaction_id
3331 ,creation_date
3332 ,last_update_date
3333 ,created_by
3334 ,last_updated_by
3335 ,last_update_login
3336 ,campaign_id
3337 ,schedule_id
3338 ,transaction_create_date
3339 ,schedule_source_code
3340 ,campaign_source_code
3341 ,schedule_activity_type
3342 ,schedule_activity_id
3343 ,campaign_purpose
3344 ,campaign_type
3345 ,start_date
3346 ,end_date
3347 ,schedule_purpose
3348 ,business_unit_id
3349 ,org_id
3350 ,campaign_status
3351 ,schedule_status
3352 ,campaign_country
3353 ,campaign_region
3354 ,schedule_region
3355 ,schedule_country
3356 ,campaign_budget_fc
3357 ,schedule_budget_fc
3358 ,load_date
3359 ,year
3360 ,qtr
3361 ,month
3362 ,leads_open
3363 ,leads_closed
3364 ,leads_open_amt
3365 ,leads_closed_amt
3366 ,leads_new
3367 ,leads_new_amt
3368 ,leads_hot
3369 ,leads_converted
3370 ,metric1 -- leads_dead
3371 ,opportunities
3372 ,opportunity_amt
3373 ,orders_booked
3374 ,orders_booked_amt
3375 ,forecasted_revenue
3376 ,actual_revenue
3377 ,forecasted_cost
3378 ,actual_cost
3379 ,forecasted_responses
3380 ,positive_responses
3381 ,targeted_customers
3382 ,budget_requested
3383 ,budget_approved
3384 )
3385 SELECT /*+ parallel(INNER,1) */
3386 bim_r_camp_daily_facts_s.nextval
3387 ,sysdate
3388 ,sysdate
3389 ,-1
3390 ,-1
3391 ,-1
3392 ,campaign_id
3393 ,schedule_id
3394 ,transaction_create_date
3395 ,schedule_source_code
3396 ,campaign_source_code
3397 ,schedule_activity_type
3398 ,schedule_activity_id
3399 ,campaign_purpose
3400 ,campaign_type
3401 ,start_date
3402 ,end_date
3403 ,schedule_purpose
3404 ,business_unit_id
3405 ,org_id
3406 ,campaign_status
3407 ,schedule_status
3408 ,campaign_country
3409 ,campaign_region
3410 ,schedule_region
3411 ,schedule_country
3412 ,0 schedule_budget_fc
3413 ,0 campaign_budget_fc
3414 ,weekend_date
3415 ,year
3416 ,qtr
3417 ,month
3418 ,leads_open
3419 ,leads_closed
3420 ,leads_open_amt
3421 ,leads_closed_amt
3422 ,leads_new
3423 ,leads_new_amt
3424 ,leads_hot
3425 ,leads_converted
3426 ,leads_dead
3427 ,opportunities
3428 ,opportunity_amt
3429 ,orders_booked
3430 ,orders_booked_amt
3431 ,forecasted_revenue
3432 ,actual_revenue
3433 ,forecasted_cost
3434 ,actual_cost
3435 ,forecasted_responses
3436 ,positive_responses
3437 ,targeted_customers
3438 ,0 request_amount
3439 ,0 approved_amount
3440 FROM
3441 (
3442 SELECT
3443 a.campaign_id campaign_id
3444 ,e.schedule_id schedule_id
3445 ,inner.creation_date transaction_create_date
3446 ,e.source_code schedule_source_code
3447 ,b2.source_code_id campaign_source_code_id
3448 ,b1.source_code_id schedule_source_code_id
3449 ,a.source_code campaign_source_code
3450 ,e.activity_type_code schedule_activity_type
3451 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id) schedule_activity_id
3452 ,a.campaign_type campaign_purpose
3453 ,a.rollup_type campaign_type
3454 ,e.start_date_time start_date
3455 ,nvl(e.end_date_time, a.actual_exec_end_date) end_date
3456 ,e.objective_code schedule_purpose
3457 ,a.business_unit_id business_unit_id
3458 ,e.org_id org_id
3459 ,a.status_code campaign_status
3460 ,e.status_code schedule_status
3461 ,a.city_id campaign_country
3462 ,d2.area2_code campaign_region
3463 ,d1.area2_code schedule_region
3464 ,e.country_id schedule_country
3465 ,(decode(decode( to_char(inner.creation_date,'MM') , to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
3466 ,'TRUE'
3467 ,decode(decode(inner.creation_date , (next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
3468 ,'TRUE'
3469 ,inner.creation_date
3470 ,'FALSE'
3471 ,next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
3472 ,'FALSE'
3473 ,decode(decode(to_char(inner.creation_date,'MM'),to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
3474 ,'FALSE'
3475 ,last_day(inner.creation_date)))) weekend_date
3476 ,bim_set_of_books.get_fiscal_year(inner.creation_date,0) year
3477 ,bim_set_of_books.get_fiscal_qtr(inner.creation_date,0) qtr
3478 ,bim_set_of_books.get_fiscal_month(inner.creation_date,0) month
3479 ,inner.leads_open leads_open
3480 ,inner.leads_closed leads_closed
3481 ,inner.leads_open_amt leads_open_amt
3482 ,inner.leads_closed_amt leads_closed_amt
3483 ,inner.leads_new leads_new
3484 ,inner.leads_new_amt leads_new_amt
3485 ,inner.leads_hot leads_hot
3486 ,inner.leads_converted leads_converted
3487 ,inner.leads_dead leads_dead
3488 ,inner.opportunities opportunities
3489 ,inner.opportunity_amt opportunity_amt
3490 ,inner.orders_booked orders_booked
3491 ,inner.orders_booked_amt orders_booked_amt
3492 ,inner.forecasted_revenue forecasted_revenue
3493 ,inner.actual_revenue actual_revenue
3494 ,inner.forecasted_cost forecasted_cost
3495 ,inner.actual_cost actual_cost
3496 ,inner.forecasted_responses forecasted_responses
3497 ,inner.positive_responses positive_responses
3498 ,inner.targeted_customers targeted_customers
3499 FROM (
3500 SELECT
3501 metric.schedule_id schedule_id
3502 ,metric.creation_date creation_date
3503 ,sum(nvl(metric.leads_open,0)) leads_open
3504 ,sum(nvl(metric.leads_closed,0)) leads_closed
3505 ,sum(nvl(metric.leads_open_amt,0)) leads_open_amt
3506 ,sum(nvl(metric.leads_closed_amt,0)) leads_closed_amt
3507 ,sum(nvl(metric.leads_new,0)) leads_new
3508 ,sum(nvl(metric.leads_new_amt,0)) leads_new_amt
3509 ,sum(nvl(metric.leads_hot,0)) leads_hot
3510 ,sum(nvl(metric.leads_converted,0)) leads_converted
3511 ,sum(nvl(metric.leads_dead,0)) leads_dead
3512 ,sum(nvl(metric.opportunities,0)) opportunities
3513 ,sum(nvl(metric.opportunity_amt,0)) opportunity_amt
3514 ,sum(nvl(metric.orders_booked,0)) orders_booked
3515 ,sum(nvl(metric.orders_booked_amt,0)) orders_booked_amt
3516 ,sum(nvl(metric.forecasted_revenue,0)) forecasted_revenue
3517 ,sum(nvl(metric.actual_revenue,0)) actual_revenue
3518 ,sum(nvl(metric.forecasted_cost,0)) forecasted_cost
3519 ,sum(nvl(metric.actual_cost,0)) actual_cost
3520 ,sum(nvl(metric.forecasted_responses,0)) forecasted_responses
3521 ,sum(nvl(metric.positive_responses,0)) positive_responses
3522 ,sum(nvl(metric.targeted_customers,0)) targeted_customers
3523 FROM (
3524 SELECT
3525 A.schedule_id schedule_id
3526 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))creation_date
3527 ,sum(decode(Y.opp_open_status_flag,'Y',1,0)) leads_open
3528 ,sum(decode(Y.opp_open_status_flag,'Y',0,1)) leads_closed
3529 ,sum(decode(Y.opp_open_status_flag,'Y',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0)) leads_open_amt
3530 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0))) leads_closed_amt
3531 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',1,0),0)) leads_new
3532 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',convert_currency(nvl(X.currency_code,'USD'),nvl(X.budget_amount,0)),0),0)) leads_new_amt
3533 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.lead_rank_id,10000,1,0),0)) leads_hot
3534 ,sum(decode(Y.opp_open_status_flag,'N',decode(X.status_code,'CONVERTED_TO_OPPORTUNITY',1,0),0)) leads_converted
3535 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',1,0))) leads_dead
3536 ,0 opportunities
3537 ,0 opportunity_amt
3538 ,0 orders_booked
3539 ,0 orders_booked_amt
3540 ,0 forecasted_revenue
3541 ,0 actual_revenue
3542 ,0 forecasted_cost
3543 ,0 actual_cost
3544 ,0 forecasted_responses
3545 ,0 positive_responses
3546 ,0 targeted_customers
3547 FROM
3548 ams_campaign_schedules_b A
3549 ,ams_source_codes C
3550 ,as_sales_leads X
3551 ,as_statuses_b Y
3552 WHERE
3553 A.schedule_id = C.source_code_for_id
3554 AND C.arc_source_code_for = 'CSCH'
3555 AND A.source_code = C.source_code
3556 AND C.source_code_id = X.source_promotion_id
3557 AND X.status_code = Y.status_code
3558 AND Y.lead_flag = 'Y'
3559 AND Y.enabled_flag = 'Y'
3560 AND NVL(X.DELETED_FLAG,'N') <> 'Y'
3561 AND trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date)) between p_start_date and p_end_date+0.99999
3562 GROUP BY
3563 a.schedule_id
3564 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))
3565 UNION ALL
3566 SELECT
3567 A.schedule_id schedule_id
3568 ,trunc(X.creation_date) creation_date
3569 ,0 leads_open
3570 ,0 leads_closed
3571 ,0 leads_open_amt
3572 ,0 leads_closed_amt
3573 ,0 leads_new
3574 ,0 leads_new_amt
3575 ,0 leads_hot
3576 ,0 leads_converted
3577 ,0 leads_dead
3578 ,count(distinct X.lead_id) opportunities
3579 ,sum(convert_currency(nvl(X.currency_code,'USD'),nvl(X.total_amount,0))) opportunity_amt
3580 ,0 orders_booked
3581 ,0 orders_booked_amt
3582 ,0 forecasted_revenue
3583 ,0 actual_revenue
3584 ,0 forecasted_cost
3585 ,0 actual_cost
3586 ,0 forecasted_responses
3587 ,0 positive_responses
3588 ,0 targeted_customers
3589 FROM
3590 ams_campaign_schedules_b A
3591 ,ams_source_codes C
3592 ,as_leads_all X
3593 WHERE
3594 A.schedule_id = C.source_code_for_id
3595 AND C.arc_source_code_for = 'CSCH'
3596 AND A.source_code = C.source_code
3597 AND C.source_code_id = X.source_promotion_id
3598 AND trunc(X.creation_date) between p_start_date and p_end_date+0.99999
3599 GROUP BY
3600 A.schedule_id,trunc(X.creation_date)
3601 UNION ALL
3602 SELECT
3603 A.schedule_id schedule_id
3604 ,trunc(H.creation_date) creation_date
3605 ,0 leads_open
3606 ,0 leads_closed
3607 ,0 leads_open_amt
3608 ,0 leads_closed_amt
3609 ,0 leads_new
3610 ,0 leads_new_amt
3611 ,0 leads_hot
3612 ,0 leads_converted
3613 ,0 leads_dead
3614 ,0 opportunities
3615 ,0 opportunity_amt
3616 ,count(distinct(h.header_id)) orders_booked
3617 ,sum(decode(h.flow_status_code,'BOOKED',convert_currency(nvl(H.transactional_curr_code,'USD')
3618 ,nvl(I.unit_selling_price * I.ordered_quantity,0)),0)) orders_booked_amt
3619 ,0 forecasted_revenue
3620 ,0 actual_revenue
3621 ,0 forecasted_cost
3622 ,0 actual_cost
3623 ,0 forecasted_responses
3624 ,0 positive_responses
3625 ,0 targeted_customers
3626 FROM
3627 ams_campaign_schedules_b A
3628 ,ams_source_codes C
3629 ,as_sales_leads D
3630 ,as_sales_lead_opportunity D1
3631 ,as_leads_all E
3632 ,aso_quote_related_objects F
3633 ,aso_quote_headers_all G
3634 ,oe_order_headers_all H
3635 ,oe_order_lines_all I
3636 WHERE
3637 A.schedule_id = C.source_code_for_id
3638 AND C.arc_source_code_for = 'CSCH'
3639 AND A.source_code = C.source_code
3640 AND C.source_code_id = D.source_promotion_id
3641 AND D.sales_lead_id = D1.sales_lead_id
3642 AND D1.opportunity_id = E.lead_id
3643 AND E.lead_id = F.object_id
3644 AND F.relationship_type_code = 'OPP_QUOTE'
3645 AND F.quote_object_type_code = 'HEADER'
3646 AND F.quote_object_id = G.quote_header_id
3647 AND G.order_id = H.header_id
3648 AND H.flow_status_code = 'BOOKED'
3649 AND NVL(D.deleted_flag,'N') <> 'Y'
3650 AND I.header_id = H.header_id
3651 AND trunc(H.creation_date) between p_start_date and p_end_date+0.99999
3652 GROUP BY
3653 A.schedule_id,trunc(H.creation_date)
3654 UNION ALL
3655 SELECT
3656 f3.act_metric_used_by_id schedule_id
3657 ,trunc(f3.creation_date) creation_date
3658 ,0 leads_open
3659 ,0 leads_closed
3660 ,0 leads_open_amt
3661 ,0 leads_closed_amt
3662 ,0 leads_new
3663 ,0 leads_new_amt
3664 ,0 leads_hot
3665 ,0 leads_converted
3666 ,0 leads_dead
3667 ,0 opportunities
3668 ,0 opportunity_amt
3669 ,0 orders_booked
3670 ,0 orders_booked_amt
3671 ,sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_forecasted_delta,0)))
3672 forecasted_revenue
3673 ,sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_actual_delta,0)))
3674 actual_revenue
3675 ,0 forecasted_cost
3676 ,0 actual_cost
3677 ,0 forecasted_responses
3678 ,0 positive_responses
3679 ,0 targeted_customers
3680 FROM
3681 ams_act_metric_hst f3
3682 ,ams_metrics_all_b g3
3683 WHERE
3684 f3.arc_act_metric_used_by = 'CSCH'
3685 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
3686 AND g3.metric_category = 902
3687 AND g3.metric_id = f3.metric_id
3688 AND trunc(f3.creation_date) between p_start_date and p_end_date+0.99999
3689 GROUP BY
3690 f3.act_metric_used_by_id,trunc(f3.creation_date)
3691 HAVING
3692 sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_forecasted_delta,0))) <> 0
3693 OR
3694 sum(convert_currency(nvl(f3.functional_currency_code,'USD'),nvl(f3.func_actual_delta,0))) <> 0
3695 UNION ALL
3696 SELECT
3697 f1.act_metric_used_by_id schedule_id
3698 ,trunc(f1.creation_date) creation_date
3699 ,0 leads_open
3700 ,0 leads_closed
3701 ,0 leads_open_amt
3702 ,0 leads_closed_amt
3703 ,0 leads_new
3704 ,0 leads_new_amt
3705 ,0 leads_hot
3706 ,0 leads_converted
3707 ,0 leads_dead
3708 ,0 opportunities
3709 ,0 opportunity_amt
3710 ,0 orders_booked
3711 ,0 orders_booked_amt
3712 ,0 forecasted_revenue
3713 ,0 actual_revenue
3714 ,sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_forecasted_delta,0)))
3715 forecasted_cost
3716 ,sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_actual_delta,0)))
3717 actual_cost
3718 ,0 forecasted_responses
3719 ,0 positive_responses
3720 ,0 targeted_customers
3721 FROM
3722 ams_act_metric_hst f1
3723 ,ams_metrics_all_b g1
3724 WHERE
3725 f1.arc_act_metric_used_by = 'CSCH'
3726 AND g1.metric_category = 901
3727 AND g1.metric_id = f1.metric_id
3728 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
3729 AND trunc(f1.creation_date) between p_start_date and p_end_date+0.99999
3730 GROUP BY
3731 f1.act_metric_used_by_id,trunc(f1.creation_date)
3732 HAVING
3733 sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_forecasted_delta,0))) <> 0
3734 OR
3735 sum(convert_currency(nvl(f1.functional_currency_code,'USD'),nvl(f1.func_actual_delta,0))) <> 0
3736 UNION ALL
3737 SELECT
3738 f3.act_metric_used_by_id schedule_id
3739 ,trunc(f3.creation_date) creation_date
3740 ,0 leads_open
3741 ,0 leads_closed
3742 ,0 leads_open_amt
3743 ,0 leads_closed_amt
3744 ,0 leads_new
3745 ,0 leads_new_amt
3746 ,0 leads_hot
3747 ,0 leads_converted
3748 ,0 leads_dead
3749 ,0 opportunities
3750 ,0 opportunity_amt
3751 ,0 orders_booked
3752 ,0 orders_booked_amt
3753 ,0 forecasted_revenue
3754 ,0 actual_revenue
3755 ,0 forecasted_cost
3756 ,0 actual_cost
3757 ,sum(nvl(f3.func_forecasted_delta,0)) forecasted_responses
3758 ,0 positive_responses
3759 ,0 targeted_customers
3760 FROM
3761 ams_act_metric_hst f3
3762 ,ams_metrics_all_b g3
3763 WHERE
3764 f3.arc_act_metric_used_by = 'CSCH'
3765 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
3766 AND g3.metric_category = 903
3767 AND g3.metric_id = f3.metric_id
3768 AND trunc(f3.creation_date) between p_start_date and p_end_date+0.99999
3769 GROUP BY
3770 f3.act_metric_used_by_id,trunc(f3.creation_date)
3771 HAVING
3772 sum(nvl(f3.func_forecasted_delta,0)) <> 0
3773 UNION ALL
3774 SELECT
3775 A.schedule_id schedule_id
3776 ,trunc(X.creation_date) creation_date
3777 ,0 leads_open
3778 ,0 leads_closed
3779 ,0 leads_open_amt
3780 ,0 leads_closed_amt
3781 ,0 leads_new
3782 ,0 leads_new_amt
3783 ,0 leads_hot
3784 ,0 leads_converted
3785 ,0 leads_dead
3786 ,0 opportunities
3787 ,0 opportunity_amt
3788 ,0 orders_booked
3789 ,0 orders_booked_amt
3790 ,0 forecasted_revenue
3791 ,0 actual_revenue
3792 ,0 forecasted_cost
3793 ,0 actual_cost
3794 ,0 forecasted_responses
3795 ,sum(decode(A.use_parent_code_flag,'Y',0,1)) positive_responses
3796 ,0 targeted_customers
3797 FROM ams_campaign_schedules_b A
3798 ,jtf_ih_interactions X
3799 ,jtf_ih_results_b Y
3800 WHERE
3801 A.source_code = X.source_code
3802 AND X.result_id = Y.result_id
3803 AND Y.positive_response_flag = 'Y'
3804 AND trunc(X.creation_date) between p_start_date and p_end_date+0.99999
3805 GROUP BY
3806 A.schedule_id,trunc(X.creation_date)
3807 UNION ALL
3808 SELECT
3809 A.schedule_id schedule_id
3810 ,trunc(p.creation_date) creation_date
3811 ,0 leads_open
3812 ,0 leads_closed
3813 ,0 leads_open_amt
3814 ,0 leads_closed_amt
3815 ,0 leads_new
3816 ,0 leads_new_amt
3817 ,0 leads_hot
3818 ,0 leads_converted
3819 ,0 leads_dead
3820 ,0 opportunities
3821 ,0 opportunity_amt
3822 ,0 orders_booked
3823 ,0 orders_booked_amt
3824 ,0 forecasted_revenue
3825 ,0 actual_revenue
3826 ,0 forecasted_cost
3827 ,0 actual_cost
3828 ,0 forecasted_responses
3829 ,0 positive_responses
3830 ,count(p.list_entry_id) targeted_customers
3831 FROM
3832 ams_list_entries p
3833 ,ams_act_lists q
3834 ,ams_campaign_schedules_b A
3835 WHERE
3836 p.list_header_id = q.list_header_id
3837 AND q.list_used_by = 'CSCH'
3838 AND q.list_act_type = 'TARGET'
3839 AND trunc(p.creation_date) between p_start_date and p_end_date+0.99999
3840 AND q.list_used_by_id = A.schedule_id
3841 AND p.enabled_flag='Y'
3842 GROUP BY
3843 A.schedule_id, trunc(p.creation_date)
3844 UNION ALL
3845 SELECT
3846 A.schedule_id schedule_id
3847 ,trunc(p.creation_date) creation_date
3848 ,0 leads_open
3849 ,0 leads_closed
3850 ,0 leads_open_amt
3851 ,0 leads_closed_amt
3852 ,0 leads_new
3853 ,0 leads_new_amt
3854 ,0 leads_hot
3855 ,0 leads_converted
3856 ,0 leads_dead
3857 ,0 opportunities
3858 ,0 opportunity_amt
3859 ,0 orders_booked
3860 ,0 orders_booked_amt
3861 ,0 forecasted_revenue
3862 ,0 actual_revenue
3863 ,0 forecasted_cost
3864 ,0 actual_cost
3865 ,0 forecasted_responses
3866 ,0 positive_responses
3867 ,count(p.list_entry_id) targeted_customers
3868 FROM
3869 ams_list_entries p
3870 ,ams_act_lists q
3871 ,ams_campaign_schedules_b A
3872 WHERE
3873 trunc(p.creation_date) between p_start_date and p_end_date+0.99999
3874 AND p.list_header_id = q.list_header_id
3875 AND q.list_used_by = 'EONE'
3876 AND q.list_act_type = 'TARGET'
3877 AND A.activity_type_code = 'EVENTS'
3878 AND q.list_used_by_id = A.related_event_id
3879 AND p.enabled_flag='Y'
3880 GROUP BY
3881 A.schedule_id, trunc(p.creation_date)
3882 ) metric
3883 GROUP BY
3884 metric.schedule_id
3885 ,metric.creation_date
3886 ) inner
3887 ,ams_campaign_schedules_b E
3888 ,ams_campaigns_all_b A
3889 ,ams_source_codes B1
3890 ,ams_source_codes B2
3891 ,jtf_loc_hierarchies_b D1
3892 ,jtf_loc_hierarchies_b D2
3893 WHERE
3894 e.schedule_id = inner.schedule_id
3895 AND e.campaign_id = a.campaign_id
3896 AND e.country_id = d1.location_hierarchy_id
3897 AND a.city_id = d2.location_hierarchy_id
3898 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
3899 AND e.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
3900 AND a.rollup_type <> 'RCAM'
3901 AND b1.source_code_for_id = decode(e.source_code,a.source_code,a.campaign_id,e.schedule_id)
3902 AND b1.arc_source_code_for = decode(e.source_code,a.source_code,'CAMP','CSCH')
3903 AND b1.source_code = e.source_code
3904 AND b2.source_code_for_id = a.campaign_id
3905 AND b2.arc_source_code_for = 'CAMP'
3906 AND b2.source_code = a.source_code
3907 AND trunc(a.actual_exec_start_date) >= trunc(l_min_start_date)
3908 )Outer;
3909
3910 COMMIT;
3911 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3912 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: AFTER SECOND INSERT ' || l_temp_msg);
3913
3914 EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL dml ';
3915 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
3916
3917 EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_camp_daily_facts_s CACHE 20';
3918
3919 /* The above main INSERT statement handles the amounts associated with the SCHEDULEs. Here with this stmt we
3920 are dealing with the CAMPAIGNs of the campaigns table */
3921
3922 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3923 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: BEFORE THIRD INSERT ' || l_temp_msg);
3924 INSERT /*+ append parallel(CDF,1) */
3925 INTO bim_r_camp_daily_facts CDF
3926 (
3927 campaign_daily_transaction_id
3928 ,creation_date
3929 ,last_update_date
3930 ,created_by
3931 ,last_updated_by
3932 ,last_update_login
3933 ,campaign_id
3934 ,schedule_id
3935 ,transaction_create_date
3936 ,schedule_source_code
3937 ,campaign_source_code
3938 ,schedule_activity_type
3939 ,schedule_activity_id
3940 ,campaign_purpose
3941 ,campaign_type
3942 ,start_date
3943 ,end_date
3944 ,schedule_purpose
3945 ,business_unit_id
3946 ,org_id
3947 ,campaign_status
3948 ,schedule_status
3949 ,campaign_country
3950 ,campaign_region
3951 ,schedule_region
3952 ,schedule_country
3953 ,campaign_budget_fc
3954 ,schedule_budget_fc
3955 ,load_date
3956 ,year
3957 ,qtr
3958 ,month
3959 ,leads_open
3960 ,leads_closed
3961 ,leads_open_amt
3962 ,leads_closed_amt
3963 ,leads_new
3964 ,leads_new_amt
3965 ,leads_hot
3966 ,leads_converted
3967 ,metric1 --leads_dead
3968 ,opportunities
3969 ,opportunity_amt
3970 ,orders_booked
3971 ,orders_booked_amt
3972 ,forecasted_revenue
3973 ,actual_revenue
3974 ,forecasted_cost
3975 ,actual_cost
3976 ,forecasted_responses
3977 ,positive_responses
3978 ,targeted_customers
3979 ,budget_requested
3980 ,budget_approved
3981 )
3982 SELECT /*+ parallel(INNER,1) */
3983 bim_r_camp_daily_facts_s.nextval
3984 ,sysdate
3985 ,sysdate
3986 ,-1
3987 ,-1
3988 ,-1
3989 ,inner.act_budget_used_by_id campaign_id
3990 ,0 schedule_id
3991 ,inner.creation_date transaction_create_date
3992 ,0 schedule_source_code
3993 ,inner.campaign_source_code campaign_source_code
3994 ,0 schedule_activity_type
3995 ,0 schedule_activity_id
3996 ,inner.campaign_purpose campaign_purpose
3997 ,inner.campaign_type campaign_type
3998 ,inner.start_date start_date
3999 ,inner.end_date end_date
4000 ,0 schedule_purpose
4001 ,inner.business_unit_id business_unit_id
4002 ,0 org_id
4003 ,inner.campaign_status campaign_status
4004 ,0 schedule_status
4005 ,inner.campaign_country_code campaign_country
4006 ,inner.campaign_region_code campaign_region
4007 ,0 schedule_region
4008 ,0 schedule_country
4009 ,inner.campaign_budget_amount campaign_budget_fc
4010 ,0 schedule_budget_fc
4011 ,(decode(decode( to_char(inner.creation_date,'MM') , to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
4012 ,'TRUE'
4013 ,decode(decode(Inner.creation_date , (next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
4014 ,'TRUE'
4015 ,inner.creation_date
4016 ,'FALSE'
4017 ,next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
4018 ,'FALSE'
4019 ,decode(decode(to_char(inner.creation_date,'MM'),to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
4020 ,'FALSE'
4021 ,last_day(Inner.creation_date)))) weekend_date
4022 ,bim_set_of_books.get_fiscal_year(inner.creation_date,0) year
4023 ,bim_set_of_books.get_fiscal_qtr(inner.creation_date,0) qtr
4024 ,bim_set_of_books.get_fiscal_month(inner.creation_date,0) month
4025 ,0 leads_open
4026 ,0 leads_closed
4027 ,0 leads_open_amt
4028 ,0 leads_closed_amt
4029 ,0 leads_new
4030 ,0 leads_new_amt
4031 ,0 leads_hot
4032 ,0 leads_converted
4033 ,0 leads_dead
4034 ,0 opportunities
4035 ,0 opportunity_amt
4036 ,0 orders_booked
4037 ,0 orders_booked_amt
4038 ,0 forecasted_revenue
4039 ,0 actual_revenue
4040 ,0 forecasted_cost
4041 ,0 actual_cost
4042 ,0 forecasted_responses
4043 ,0 positive_responses
4044 ,0 targeted_customers
4045 ,inner.request_amount request_amount
4046 ,inner.approved_amount approved_amount
4047 FROM
4048 (
4049 SELECT
4050 s.act_budget_used_by_id act_budget_used_by_id
4051 ,decode(s.status_code
4052 ,'PENDING'
4053 ,trunc(nvl(s.request_date,s.creation_date))
4054 ,'APPROVED'
4055 ,trunc(nvl(s.approval_date,s.last_update_date))
4056 ) creation_date
4057 ,sum(decode(s.status_code
4058 ,'PENDING'
4059 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
4060 ,'APPROVED'
4061 , convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
4062 )) request_amount
4063 ,sum(decode(s.status_code
4064 ,'PENDING'
4065 ,0
4066 ,'APPROVED'
4067 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
4068 )) approved_amount
4069 ,b2.source_code_id campaign_source_code_id
4070 ,a.source_code campaign_source_code
4071 ,a.campaign_type campaign_purpose
4072 ,a.status_code campaign_status
4073 ,a.rollup_type campaign_type
4074 ,a.actual_exec_start_date start_date
4075 ,a.actual_exec_end_date end_date
4076 ,a.business_unit_id business_unit_id
4077 ,a.city_id campaign_country_code
4078 ,d.area2_code campaign_region_code
4079 ,a.budget_amount_fc campaign_budget_amount
4080 FROM ozf_act_budgets S
4081 ,ams_campaigns_all_b A
4082 ,ams_source_codes B2
4083 ,jtf_loc_hierarchies_b D
4084 WHERE s.arc_act_budget_used_by = 'CAMP'
4085 AND s.budget_source_type = 'FUND'
4086 --AND s.transfer_type = 'REQUEST'
4087 AND s.act_budget_used_by_id = a.campaign_id
4088 AND a.city_id = d.location_hierarchy_id
4089 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
4090 AND a.rollup_type <> 'RCAM'
4091 AND b2.source_code = a.source_code
4092 AND trunc(a.actual_exec_start_date) >= trunc(l_min_start_date)
4093 AND a.actual_exec_start_date <= p_end_date
4094 AND decode(s.status_code,'PENDING',trunc(nvl(s.request_date,s.creation_date)),'APPROVED',trunc(nvl(s.approval_date,s.last_update_date)) ) between p_start_date and p_end_date+0.99999
4095 AND exists (select distinct campaign_id
4096 from ams_campaign_schedules_b x
4097 where x.campaign_id = a.campaign_id)
4098 GROUP BY s.act_budget_used_by_id
4099 ,decode(s.status_code,'PENDING',trunc(nvl(s.request_date,s.creation_date)),'APPROVED',trunc(nvl(s.approval_date,s.last_update_date)) )
4100 ,b2.source_code_id
4101 ,a.source_code
4102 ,a.campaign_type
4103 ,a.status_code
4104 ,a.rollup_type
4105 ,a.actual_exec_start_date
4106 ,a.actual_exec_end_date
4107 ,a.business_unit_id
4108 ,a.city_id
4109 ,d.area2_code
4110 ,a.budget_amount_fc
4111 HAVING sum(decode(s.status_code
4112 ,'PENDING'
4113 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
4114 ,'APPROVED'
4115 , convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
4116 )) > 0
4117 OR
4118 sum(decode(s.status_code
4119 ,'PENDING'
4120 ,0
4121 ,'APPROVED'
4122 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
4123 )) > 0
4124 UNION ALL
4125 SELECT
4126 s.budget_source_id act_budget_used_by_id
4127 ,trunc(nvl(s.approval_date,s.last_update_date)) creation_date
4128 ,0 request_amount
4129 ,- sum(convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))) approved_amount
4130 ,b2.source_code_id campaign_source_code_id
4131 ,a.source_code campaign_source_code
4132 ,a.campaign_type campaign_purpose
4133 ,a.status_code campaign_status
4134 ,a.rollup_type campaign_type
4135 ,a.actual_exec_start_date start_date
4136 ,a.actual_exec_end_date end_date
4137 ,a.business_unit_id business_unit_id
4138 ,a.city_id campaign_country_code
4139 ,d.area2_code campaign_region_code
4140 ,a.budget_amount_fc campaign_budget_amount
4141 FROM ozf_act_budgets S
4142 ,ams_campaigns_all_b A
4143 ,ams_source_codes B2
4144 ,jtf_loc_hierarchies_b D
4145 WHERE s.arc_act_budget_used_by = 'FUND'
4146 AND s.budget_source_type = 'CAMP'
4147 --AND s.transfer_type = 'REQUEST'
4148 AND s.budget_source_id = a.campaign_id
4149 AND a.city_id = d.location_hierarchy_id
4150 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
4151 AND a.rollup_type <> 'RCAM'
4152 AND b2.source_code = a.source_code
4153 --AND a.actual_exec_start_date >= trunc(p_start_date)
4154 AND trunc(a.actual_exec_start_date) >= trunc(l_min_start_date)
4155 AND a.actual_exec_start_date <= trunc(p_end_date)
4156 --AND s.approval_date <= trunc(p_end_date)
4157 AND s.approval_date between p_start_date and p_end_date+0.99999
4158 AND exists (select distinct campaign_id
4159 from ams_campaign_schedules_b x
4160 where x.campaign_id = a.campaign_id)
4161 GROUP BY s.budget_source_id
4162 ,trunc(nvl(s.approval_date,s.last_update_date))
4163 ,b2.source_code_id
4164 ,a.source_code
4165 ,a.campaign_type
4166 ,a.status_code
4167 ,a.rollup_type
4168 ,a.actual_exec_start_date
4169 ,a.actual_exec_end_date
4170 ,a.business_unit_id
4171 ,a.city_id
4172 ,d.area2_code
4173 ,a.budget_amount_fc
4174 HAVING sum(decode(s.status_code
4175 ,'PENDING'
4176 ,0
4177 ,'APPROVED'
4178 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
4179 )) > 0
4180 )INNER;
4181
4182
4183 COMMIT;
4184
4185
4186 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4187 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: THIRD SECOND INSERT ' || l_temp_msg);
4188
4189
4190 /***************************************************************/
4191 /* This insert deals with schdule budgets */
4192
4193 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4194 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: BEFORE FOURTH INSERT ' || l_temp_msg);
4195
4196 INSERT /*+ append parallel(CDF,1) */
4197 INTO bim_r_camp_daily_facts CDF
4198 (
4199 campaign_daily_transaction_id
4200 ,creation_date
4201 ,last_update_date
4202 ,created_by
4203 ,last_updated_by
4204 ,last_update_login
4205 ,campaign_id
4206 ,schedule_id
4207 ,transaction_create_date
4208 ,schedule_source_code
4209 ,campaign_source_code
4210 ,schedule_activity_type
4211 ,schedule_activity_id
4212 ,campaign_purpose
4213 ,campaign_type
4214 ,start_date
4215 ,end_date
4216 ,schedule_purpose
4217 ,business_unit_id
4218 ,org_id
4219 ,campaign_status
4220 ,schedule_status
4221 ,campaign_country
4222 ,campaign_region
4223 ,schedule_region
4224 ,schedule_country
4225 ,campaign_budget_fc
4226 ,schedule_budget_fc
4227 ,load_date
4228 ,year
4229 ,qtr
4230 ,month
4231 ,leads_open
4232 ,leads_closed
4233 ,leads_open_amt
4234 ,leads_closed_amt
4235 ,leads_new
4236 ,leads_new_amt
4237 ,leads_hot
4238 ,leads_converted
4239 ,metric1 --leads_dead
4240 ,opportunities
4241 ,opportunity_amt
4242 ,orders_booked
4243 ,orders_booked_amt
4244 ,forecasted_revenue
4245 ,actual_revenue
4246 ,forecasted_cost
4247 ,actual_cost
4248 ,forecasted_responses
4249 ,positive_responses
4250 ,targeted_customers
4251 ,budget_requested
4252 ,budget_approved
4253 )
4254 SELECT /*+ parallel(INNER,1) */
4255 bim_r_camp_daily_facts_s.nextval
4256 ,sysdate
4257 ,sysdate
4258 ,-1
4259 ,-1
4260 ,-1
4261 ,inner.campaign_id campaign_id
4262 ,inner.schedule_id schedule_id
4263 ,inner.creation_date transaction_create_date
4264 ,inner.schedule_source_code schedule_source_code
4265 ,inner.campaign_source_code campaign_source_code
4266 ,inner.schedule_activity_type schedule_activity_type
4267 ,inner.schedule_activity_id schedule_activity_id
4268 ,inner.campaign_purpose campaign_purpose
4269 ,inner.campaign_type campaign_type
4270 ,inner.start_date start_date
4271 ,inner.end_date end_date
4272 ,inner.schedule_purpose schedule_purpose
4273 ,inner.business_unit_id business_unit_id
4274 ,inner.org_id org_id
4275 ,inner.campaign_status campaign_status
4276 ,inner.status_code schedule_status
4277 ,inner.campaign_country_code campaign_country
4278 ,inner.campaign_region_code campaign_region
4279 ,inner.schedule_region_code schedule_region
4280 ,inner.schedule_country_code schedule_country
4281 ,0 campaign_budget_fc
4282 ,inner.schedule_budget_amount schedule_budget_fc
4283 ,(decode(decode( to_char(inner.creation_date,'MM') , to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
4284 ,'TRUE'
4285 ,decode(decode(Inner.creation_date , (next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
4286 ,'TRUE'
4287 ,inner.creation_date
4288 ,'FALSE'
4289 ,next_day(inner.creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
4290 ,'FALSE'
4291 ,decode(decode(to_char(inner.creation_date,'MM'),to_char(next_day(inner.creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
4292 ,'FALSE'
4293 ,last_day(Inner.creation_date)))) weekend_date
4294 ,bim_set_of_books.get_fiscal_year(inner.creation_date,0) year
4295 ,bim_set_of_books.get_fiscal_qtr(inner.creation_date,0) qtr
4296 ,bim_set_of_books.get_fiscal_month(inner.creation_date,0) month
4297 ,0 leads_open
4298 ,0 leads_closed
4299 ,0 leads_open_amt
4300 ,0 leads_closed_amt
4301 ,0 leads_new
4302 ,0 leads_new_amt
4303 ,0 leads_hot
4304 ,0 leads_converted
4305 ,0 leads_dead
4306 ,0 opportunities
4307 ,0 opportunity_amt
4308 ,0 orders_booked
4309 ,0 orders_booked_amt
4310 ,0 forecasted_revenue
4311 ,0 actual_revenue
4312 ,0 forecasted_cost
4313 ,0 actual_cost
4314 ,0 forecasted_responses
4315 ,0 positive_responses
4316 ,0 targeted_customers
4317 ,inner.request_amount request_amount
4318 ,inner.approved_amount approved_amount
4319 FROM
4320 (
4321 SELECT
4322 s.act_budget_used_by_id act_budget_used_by_id
4323 ,decode(s.status_code
4324 ,'PENDING'
4325 ,trunc(nvl(s.request_date,s.creation_date))
4326 ,'APPROVED'
4327 ,trunc(nvl(s.approval_date,s.last_update_date))
4328 ) creation_date
4329 ,sum(decode(s.status_code
4330 ,'PENDING'
4331 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
4332 ,'APPROVED'
4333 , convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
4334 )) request_amount
4335 ,sum(decode(s.status_code
4336 ,'PENDING'
4337 ,0
4338 ,'APPROVED'
4339 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
4340 )) approved_amount
4341 ,a.campaign_id campaign_id
4342 ,b2.source_code_id campaign_source_code_id
4343 ,a.source_code campaign_source_code
4344 ,a.campaign_type campaign_purpose
4345 ,a.status_code campaign_status
4346 ,a.rollup_type campaign_type
4347 ,e.schedule_id schedule_id
4348 ,e.source_code schedule_source_code
4349 ,b1.source_code_id schedule_source_code_id
4350 ,e.activity_type_code schedule_activity_type
4351 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id) schedule_activity_id
4352 ,d1.area2_code schedule_region_code
4353 ,e.country_id schedule_country_code
4354 ,e.org_id org_id
4355 ,e.status_code status_code
4356 ,e.start_date_time start_date
4357 ,nvl(e.end_date_time, a.actual_exec_end_date) end_date
4358 ,e.objective_code schedule_purpose
4359 ,a.business_unit_id business_unit_id
4360 ,a.city_id campaign_country_code
4361 ,e.budget_amount_fc schedule_budget_amount
4362 ,d2.area2_code campaign_region_code
4363 FROM ozf_act_budgets S
4364 ,ams_campaigns_all_b A
4365 ,ams_source_codes B1
4366 ,ams_source_codes B2
4367 ,jtf_loc_hierarchies_b D1
4368 ,jtf_loc_hierarchies_b D2
4369 ,ams_campaign_schedules_b E
4370 WHERE s.arc_act_budget_used_by = 'CSCH'
4371 AND s.budget_source_type = 'FUND'
4372 --AND s.transfer_type = 'REQUEST'
4373 AND s.act_budget_used_by_id = e.schedule_id
4374 AND e.campaign_id = a.campaign_id
4375 AND e.country_id = d1.location_hierarchy_id
4376 AND a.city_id = d2.location_hierarchy_id
4377 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
4378 --AND e.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
4379 AND a.rollup_type <> 'RCAM'
4380 AND b1.source_code = e.source_code
4381 AND b2.source_code = a.source_code
4382 --AND a.actual_exec_start_date >= p_start_date
4383 AND trunc(a.actual_exec_start_date) >= trunc(l_min_start_date)
4384 AND a.actual_exec_start_date <= p_end_date
4385 --AND s.approval_date <= p_end_date
4386 AND decode(s.status_code,'PENDING',trunc(nvl(s.request_date,s.creation_date)),'APPROVED',trunc(nvl(s.approval_date,s.last_update_date)) ) between p_start_date and p_end_date+0.99999
4387 GROUP BY s.act_budget_used_by_id
4388 ,decode(s.status_code,'PENDING',trunc(nvl(s.request_date,s.creation_date)),'APPROVED',trunc(nvl(s.approval_date,s.last_update_date)) )
4389 ,a.campaign_id
4390 ,b2.source_code_id
4391 ,a.source_code
4392 ,a.campaign_type
4393 ,a.status_code
4394 ,a.rollup_type
4395 ,e.schedule_id
4396 ,e.source_code
4397 ,b1.source_code_id
4398 ,e.activity_type_code
4399 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id)
4400 ,d1.area2_code
4401 ,e.country_id
4402 ,e.org_id
4403 ,e.status_code
4404 ,e.start_date_time
4405 ,nvl(e.end_date_time, a.actual_exec_end_date)
4406 ,e.objective_code
4407 ,a.business_unit_id
4408 ,a.city_id
4409 ,e.budget_amount_fc
4410 ,d2.area2_code
4411 HAVING sum(decode(s.status_code
4412 ,'PENDING'
4413 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
4414 ,'APPROVED'
4415 ,convert_currency(nvl(request_currency,'USD'),nvl(s.request_amount,0))
4416 )) > 0
4417 OR
4418 sum(decode(s.status_code
4419 ,'PENDING'
4420 ,0
4421 ,'APPROVED'
4422 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
4423 )) > 0
4424 UNION ALL
4425
4426 SELECT
4427 s.budget_source_id act_budget_used_by_id
4428 ,trunc(nvl(s.approval_date,s.last_update_date)) creation_date
4429 ,0 request_amount
4430 ,- sum(convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))) approved_amount
4431 ,a.campaign_id campaign_id
4432 ,b2.source_code_id campaign_source_code_id
4433 ,a.source_code campaign_source_code
4434 ,a.campaign_type campaign_purpose
4435 ,a.status_code campaign_status
4436 ,a.rollup_type campaign_type
4437 ,e.schedule_id schedule_id
4438 ,e.source_code schedule_source_code
4439 ,b1.source_code_id schedule_source_code_id
4440 ,e.activity_type_code schedule_activity_type
4441 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id) schedule_activity_id
4442 ,d1.area2_code schedule_region_code
4443 ,e.country_id schedule_country_code
4444 ,e.org_id org_id
4445 ,e.status_code status_code
4446 ,e.start_date_time start_date
4447 ,nvl(e.end_date_time, a.actual_exec_end_date) end_date
4448 ,e.objective_code schedule_purpose
4449 ,a.business_unit_id business_unit_id
4450 ,a.city_id campaign_country_code
4451 ,e.budget_amount_fc schedule_budget_amount
4452 ,d2.area2_code campaign_region_code
4453 FROM ozf_act_budgets S
4454 ,ams_campaigns_all_b A
4455 ,ams_source_codes B1
4456 ,ams_source_codes B2
4457 ,jtf_loc_hierarchies_b D1
4458 ,jtf_loc_hierarchies_b D2
4459 ,ams_campaign_schedules_b E
4460 WHERE s.arc_act_budget_used_by = 'FUND'
4461 AND s.budget_source_type = 'CSCH'
4462 --AND s.transfer_type = 'REQUEST'
4463 AND s.budget_source_id = e.schedule_id
4464 AND e.campaign_id = a.campaign_id
4465 AND e.country_id = d1.location_hierarchy_id
4466 AND a.city_id = d2.location_hierarchy_id
4467 AND a.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
4468 --AND e.status_code IN ('ACTIVE', 'CANCELLED', 'COMPLETED','CLOSED')
4469 AND a.rollup_type <> 'RCAM'
4470 AND b1.source_code = e.source_code
4471 AND b2.source_code = a.source_code
4472 --AND a.actual_exec_start_date >= trunc(p_start_date)
4473 AND trunc(a.actual_exec_start_date) >= trunc(l_min_start_date)
4474 AND a.actual_exec_start_date <= trunc(p_end_date)
4475 --AND s.approval_date <= trunc(p_end_date)
4476 AND s.approval_date between p_start_date and p_end_date+0.99999
4477 GROUP BY s.budget_source_id
4478 ,trunc(nvl(s.approval_date,s.last_update_date))
4479 ,a.campaign_id
4480 ,b2.source_code_id
4481 ,a.source_code
4482 ,a.campaign_type
4483 ,a.status_code
4484 ,a.rollup_type
4485 ,e.schedule_id
4486 ,e.source_code
4487 ,b1.source_code_id
4488 ,e.activity_type_code
4489 ,decode(e.activity_type_code,'EVENTS',-9999, e.activity_id)
4490 ,d1.area2_code
4491 ,e.country_id
4492 ,e.org_id
4493 ,e.status_code
4494 ,e.start_date_time
4495 ,nvl(e.end_date_time, a.actual_exec_end_date)
4496 ,e.objective_code
4497 ,a.business_unit_id
4498 ,a.city_id
4499 ,e.budget_amount_fc
4500 ,d2.area2_code
4501 HAVING sum(decode(s.status_code
4502 ,'PENDING'
4503 ,0
4504 ,'APPROVED'
4505 ,convert_currency(nvl(approved_in_currency,'USD'),nvl(s.approved_original_amount,0))
4506 )) > 0
4507
4508 )INNER;
4509
4510
4511 COMMIT;
4512
4513
4514 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4515 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: AFTER FOURTH INSERT ' || l_temp_msg);
4516
4517
4518 /***************************************************************/
4519
4520
4521 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4522 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: BEFORE ANALYZING DAILY FACTS ' || l_temp_msg);
4523
4524 -- Analyze the daily facts table
4525 DBMS_STATS.gather_table_stats('BIM','BIM_R_CAMP_DAILY_FACTS', estimate_percent => 5,
4526 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4527
4528 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4529 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: AFTER ANALYZING DAILY FACTS ' || l_temp_msg);
4530
4531 /***************************************************************/
4532
4533 /* INSERT INTO WEEKLY SUMMARY TABLE */
4534
4535 /* Here we are inserting the summarized data into the weekly facts by taking it from the daily facts.
4536 For every week we have a record since we group by that weekend date which is nothing but the Load date. */
4537
4538 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4539 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: BEFORE WEEKLY TABLE INSERT ' || l_temp_msg);
4540
4541 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_r_camp_weekly_facts';
4542
4543 /*BEGIN BLOCK FOR THE WEEKLY INSERT */
4544
4545 l_table_name := 'bim_r_camp_weekly_facts';
4546 l_seq_name := 'bim_r_camp_weekly_facts_s';
4547
4548 INSERT /*+ append parallel(CWF,1) */
4549 INTO bim_r_camp_weekly_facts CWF
4550 (
4551 campaign_weekly_transaction_id
4552 ,creation_date
4553 ,last_update_date
4554 ,created_by
4555 ,last_updated_by
4556 ,campaign_id
4557 ,schedule_id
4558 ,campaign_source_code
4559 ,schedule_source_code
4560 ,campaign_type
4561 ,start_date
4562 ,end_date
4563 ,campaign_region
4564 ,schedule_region
4565 ,campaign_country
4566 ,schedule_country
4567 ,business_unit_id
4568 ,schedule_activity_type
4569 ,schedule_activity_id
4570 ,campaign_purpose
4571 ,campaign_status
4572 ,schedule_status
4573 ,transaction_create_date
4574 ,org_id
4575 ,load_date
4576 ,year
4577 ,qtr
4578 ,month
4579 ,leads_open
4580 ,leads_closed
4581 ,leads_open_amt
4582 ,leads_closed_amt
4583 ,leads_new
4584 ,leads_new_amt
4585 ,leads_hot
4586 ,leads_converted
4587 ,metric1 -- leads_dead
4588 ,opportunities
4589 ,opportunity_amt
4590 ,orders_booked
4591 ,orders_booked_amt
4592 ,forecasted_revenue
4593 ,actual_revenue
4594 ,forecasted_cost
4595 ,actual_cost
4596 ,forecasted_responses
4597 ,positive_responses
4598 ,targeted_customers
4599 ,budget_requested
4600 ,budget_approved
4601 )
4602 SELECT /*+ parallel(INNER,8) */
4603 bim_r_camp_weekly_facts_s.nextval
4604 ,sysdate
4605 ,sysdate
4606 ,l_user_id
4607 ,l_user_id
4608 ,campaign_id
4609 ,schedule_id
4610 ,campaign_source_code
4611 ,schedule_source_code
4612 ,campaign_type
4613 ,start_date
4614 ,end_date
4615 ,campaign_region
4616 ,schedule_region
4617 ,campaign_country
4618 ,schedule_country
4619 ,business_unit_id
4620 ,schedule_activity_type
4621 ,schedule_activity_id
4622 ,campaign_purpose
4623 ,campaign_status
4624 ,schedule_status
4625 ,transaction_create_date
4626 ,org_id
4627 ,load_date
4628 ,year
4629 ,qtr
4630 ,month
4631 ,leads_open
4632 ,leads_closed
4633 ,leads_open_amt
4634 ,leads_closed_amt
4635 ,leads_new
4636 ,leads_new_amt
4637 ,leads_hot
4638 ,leads_converted
4639 ,leads_dead
4640 ,opportunities
4641 ,opportunity_amt
4642 ,orders_booked
4643 ,orders_booked_amt
4644 ,forecasted_revenue
4645 ,actual_revenue
4646 ,forecasted_cost
4647 ,actual_cost
4648 ,forecasted_responses
4649 ,positive_responses
4650 ,targeted_customers
4651 ,budget_requested
4652 ,budget_approved
4653 FROM
4654 (
4655 SELECT
4656 campaign_id campaign_id
4657 ,schedule_id schedule_id
4658 ,campaign_source_code campaign_source_code
4659 ,schedule_source_code schedule_source_code
4660 ,campaign_type campaign_type
4661 ,start_date start_date
4662 ,end_date end_date
4663 ,campaign_region campaign_region
4664 ,schedule_region schedule_region
4665 ,campaign_country campaign_country
4666 ,schedule_country schedule_country
4667 ,nvl(business_unit_id,0) business_unit_id
4668 ,schedule_activity_type schedule_activity_type
4669 ,schedule_activity_id schedule_activity_id
4670 ,campaign_purpose campaign_purpose
4671 ,campaign_status campaign_status
4672 ,schedule_status schedule_status
4673 ,load_date transaction_create_date
4674 ,org_id org_id
4675 ,load_date load_date
4676 ,year year
4677 ,qtr qtr
4678 ,month month
4679 ,sum(leads_open) leads_open
4680 ,sum(leads_closed) leads_closed
4681 ,sum(leads_open_amt) leads_open_amt
4682 ,sum(leads_closed_amt) leads_closed_amt
4683 ,sum(leads_new) leads_new
4684 ,sum(leads_new_amt) leads_new_amt
4685 ,sum(leads_hot) leads_hot
4686 ,sum(leads_converted) leads_converted
4687 ,sum(metric1) leads_dead
4688 ,sum(opportunities) opportunities
4689 ,sum(opportunity_amt) opportunity_amt
4690 ,sum(orders_booked) orders_booked
4691 ,sum(orders_booked_amt) orders_booked_amt
4692 ,sum(forecasted_revenue) forecasted_revenue
4693 ,sum(actual_revenue) actual_revenue
4694 ,sum(forecasted_cost) forecasted_cost
4695 ,sum(actual_cost) actual_cost
4696 ,sum(forecasted_responses) forecasted_responses
4697 ,sum(positive_responses) positive_responses
4698 ,sum(targeted_customers) targeted_customers
4699 ,sum(budget_requested) budget_requested
4700 ,sum(budget_approved) budget_approved
4701 FROM bim_r_camp_daily_facts
4702 -- WHERE transaction_create_date between trunc(p_start_date) and trunc(p_end_date) + 0.99999
4703 GROUP BY campaign_id
4704 ,schedule_id
4705 ,load_date
4706 ,year
4707 ,qtr
4708 ,month
4709 ,campaign_source_code
4710 ,schedule_source_code
4711 ,campaign_type
4712 ,start_date
4713 ,end_date
4714 ,campaign_region
4715 ,schedule_region
4716 ,campaign_country
4717 ,schedule_country
4718 ,nvl(business_unit_id,0)
4719 ,schedule_activity_type
4720 ,schedule_activity_id
4721 ,campaign_purpose
4722 ,campaign_status
4723 ,schedule_status
4724 ,org_id
4725 )INNER;
4726
4727 LOG_HISTORY('CAMPAIGN', p_start_date, p_end_date);
4728
4729
4730 COMMIT;
4731
4732
4733 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4734 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: AFTER WEEKLY TABLE INSERT ' || l_temp_msg);
4735
4736 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4737 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: BEFORE ANALYZING WEEKLY FACTS ' || l_temp_msg);
4738
4739 -- Analyze the daily facts table
4740 DBMS_STATS.gather_table_stats('BIM','BIM_R_CAMP_WEEKLY_FACTS', estimate_percent => 5,
4741 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
4742
4743 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4744 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: AFTER ANALYZING WEEKLY FACTS ' || l_temp_msg);
4745
4746
4747 /* Piece of Code for Recreating the index on the same tablespace with the same storage parameters */
4748
4749 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4750 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: BEFORE CREATE INDEX ' || l_temp_msg);
4751
4752 i := i - 1;
4753 WHILE(i>=1) LOOP
4754 EXECUTE IMMEDIATE 'CREATE INDEX '
4755 || l_owner(i)
4756 || '.'
4757 || l_index_name(i)
4758 ||' ON '
4759 || l_owner(i)
4760 ||'.'
4761 || l_index_table_name(i)
4762 || ' ('
4763 || l_ind_column_name(i)
4764 || ' )'
4765 || ' tablespace ' || l_index_tablespace
4766 || ' pctfree ' || l_pct_free(i)
4767 || ' initrans ' || l_ini_trans(i)
4768 || ' maxtrans ' || l_max_trans(i)
4769 || ' storage ( '
4770 || ' initial ' || l_initial_extent(i)
4771 || ' next ' || l_next_extent(i)
4772 || ' minextents ' || l_min_extents(i)
4773 || ' maxextents ' || l_max_extents(i)
4774 || ' pctincrease ' || l_pct_increase(i)
4775 || ')' ;
4776
4777 i := i - 1;
4778 END LOOP;
4779
4780 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
4781 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD: AFTER CREATE INDEX ' || l_temp_msg);
4782
4783 /* End of Code for Recreating the index on the same tablespace with the same storage parameters */
4784
4785
4786 EXECUTE IMMEDIATE 'ALTER SEQUENCE '||l_schema||'.bim_r_camp_weekly_facts_s CACHE 20';
4787
4788
4789
4790 EXCEPTION
4791
4792 WHEN FND_API.G_EXC_ERROR THEN
4793 x_return_status := FND_API.G_RET_STS_ERROR;
4794 -- Standard call to get message count and if count=1, get the message
4795 FND_msg_PUB.Count_And_Get (
4796 -- p_encoded => FND_API.G_FALSE,
4797 p_count => x_msg_count,
4798 p_data => x_msg_data
4799 );
4800
4801 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
4802
4803 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4804
4805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4806
4807 -- Standard call to get message count and if count=1, get the message
4808 FND_msg_PUB.Count_And_Get (
4809 --p_encoded => FND_API.G_FALSE,
4810 p_count => x_msg_count,
4811 p_data => x_msg_data
4812 );
4813
4814 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
4815
4816 WHEN OTHERS THEN
4817
4818 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4819
4820 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
4821 THEN
4822 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
4823 END IF;
4824
4825 -- Standard call to get message count and if count=1, get the message
4826 FND_msg_PUB.Count_And_Get (
4827 -- p_encoded => FND_API.G_FALSE,
4828 p_count => x_msg_count,
4829 p_data => x_msg_data
4830 );
4831
4832 ams_utility_pvt.write_conc_log('BIM_CAMPAIGN_FACTS:LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
4833
4834
4835 END CAMPAIGN_SUBSEQUENT_LOAD;
4836
4837
4838 --------------------------------------------------------------------------------------------------
4839 -- this procedure will excute when data is loaded DAILY , and run the program incrementally.
4840
4841 -- PROCEDURE CAMPAIGN_DAILY_LOAD
4842
4843 --------------------------------------------------------------------------------------------------
4844
4845
4846 PROCEDURE CAMPAIGN_DAILY_LOAD
4847 ( p_api_version_number IN NUMBER
4848 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
4849 ,x_msg_count OUT NOCOPY NUMBER
4850 ,x_msg_data OUT NOCOPY VARCHAR2
4851 ,x_return_status OUT NOCOPY VARCHAR2
4852 ,p_date IN DATE
4853 ) IS
4854 l_weekend_date DATE;
4855 l_user_id NUMBER := FND_GLOBAL.USER_ID();
4856 l_seq NUMBER;
4857 l_seqw NUMBER;
4858 l_api_version_number CONSTANT NUMBER := 1.0;
4859 l_api_name CONSTANT VARCHAR2(30) := 'CAMPAIGN_DAILY_LOAD';
4860 l_table_name VARCHAR2(100);
4861 l_seq_name VARCHAR2(100);
4862 l_grace_period NUMBER;
4863
4864 l_min_date DATE;
4865
4866
4867 BEGIN
4868 l_min_date := sysdate;
4869
4870 END CAMPAIGN_DAILY_LOAD;
4871
4872 END BIM_CAMPAIGN_FACTS;