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