[Home] [Help]
PACKAGE BODY: APPS.BIM_MARKET_FACTS_PKG
Source
1 PACKAGE BODY BIM_MARKET_FACTS_PKG AS
2 /*$Header: bimmktfb.pls 120.8 2005/12/20 02:09:32 arvikuma noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_MARKET_FACTS_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimmktfb.pls';
6 l_global_currency_code CONSTANT varchar2(20) := bis_common_parameters.get_currency_code;
7 l_secondary_currency_code CONSTANT VARCHAR2(20) :=bis_common_parameters.get_secondary_currency_code;
8 l_pgc_rate_type CONSTANT VARCHAR2(20) :=bis_common_parameters.Get_Rate_Type;
9 l_sgc_rate_type CONSTANT VARCHAR2(20) :=bis_common_parameters.Get_secondary_Rate_Type;
10 FUNCTION ret_max_date(p_sales_lead_id in number) return date is
11 CURSOR get_max_date IS
12 SELECT max(b.creation_date)
13 FROM as_sales_leads a, as_sales_leads_log b
14 WHERE a.sales_lead_id = b.sales_lead_id
15 AND b.sales_lead_id = p_sales_lead_id ;
16
17 l_date date;
18
19 BEGIN
20 OPEN get_max_date;
21 FETCH get_max_date into l_date;
22 CLOSE get_max_date;
23
24 RETURN l_date;
25 END;
26
27 -- Checks for any missing currency from marketing facts table
28
29 FUNCTION Check_Missing_Rates (p_start_date IN Date)
30 Return NUMBER
31 AS
32 l_cnt_miss_rate1 Number := 0;
33 l_cnt_miss_rate2 Number := 0;
34 l_msg_name Varchar2(40);
35
36 CURSOR C_missing_rates1
37 IS
38 SELECT tc_code from_currency,
39 decode(prim_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),trx_date) transaction_create_date
40 FROM BIM_I_MKT_RATES
41 WHERE prim_conversion_rate < 0
42 AND tc_code is not null
43 AND trx_date >= p_start_date
44 ORDER BY tc_code,
45 trx_date ;
46
47 CURSOR C_missing_rates2
48 IS
49 SELECT tc_code from_currency,
50 decode(sec_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),trx_date) transaction_create_date
51 FROM BIM_I_MKT_RATES
52 WHERE sec_conversion_rate < 0
53 AND tc_code is not null
54 AND trx_date >= p_start_date
55 ORDER BY tc_code,
56 trx_date ;
57 BEGIN
58 l_msg_name := 'BIS_DBI_CURR_NO_LOAD';
59 SELECT COUNT(*) INTO l_cnt_miss_rate1 FROM BIM_I_MKT_RATES
60 WHERE
61 prim_conversion_rate < 0
62 AND tc_code is not null
63 AND trx_date >= p_start_date;
64
65 SELECT COUNT(*) INTO l_cnt_miss_rate2 FROM BIM_I_MKT_RATES
66 WHERE
67 sec_conversion_rate <0
68 AND tc_code is not null
69 AND trx_date >= p_start_date;
70
71 If(l_cnt_miss_rate1 > 0 )
72 Then
73 FND_MESSAGE.Set_Name('FII',l_msg_name);
74 BIS_COLLECTION_UTILITIES.debug(l_msg_name||': '||FND_MESSAGE.get);
75 BIS_COLLECTION_UTILITIES.log('Primary Conversion rate could not be found for the given currency. Please check output file for more details' );
76 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
77
78 FOR rate_record in C_missing_rates1
79 LOOP
80 BIS_COLLECTION_UTILITIES.writeMissingRate(
81 p_rate_type => l_pgc_rate_type,
82 p_from_currency => rate_record.from_currency,
83 p_to_currency => l_global_currency_code,
84 p_date => rate_record.transaction_create_date);
85 END LOOP;
86 BIS_COLLECTION_UTILITIES.debug('before returning -1' );
87 RETURN -1;
88 ELSE
89 Return 1;
90 End If;
91 If(l_cnt_miss_rate2 > 0 and l_secondary_currency_code is not null )
92 Then
93 FND_MESSAGE.Set_Name('FII',l_msg_name);
94 BIS_COLLECTION_UTILITIES.debug(l_msg_name||': '||FND_MESSAGE.get);
95 BIS_COLLECTION_UTILITIES.log('Secondary Conversion rate could not be found for the given currency. Please check output file for more details' );
96 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
97
98 FOR rate_record in C_missing_rates2
99 LOOP
100 BIS_COLLECTION_UTILITIES.writeMissingRate(
101 p_rate_type => l_sgc_rate_type,
102 p_from_currency => rate_record.from_currency,
103 p_to_currency => l_secondary_currency_code,
104 p_date => rate_record.transaction_create_date);
105 END LOOP;
106 BIS_COLLECTION_UTILITIES.debug('before returning -1' );
107 RETURN -1;
108 ELSE
109 Return 1;
110 End If;
111 EXCEPTION
112 WHEN OTHERS THEN
113 BIS_COLLECTION_UTILITIES.Debug('Error in Check_missing_rates:'||sqlerrm);
114 RAISE;
115 END Check_Missing_Rates;
116
117 ---------------------------------------------------------------------------------------------------
118 /* This procedure will conditionally call the FIRST_LOAD or the SUB_LOAD */
119 ---------------------------------------------------------------------------------------------------
120
121 PROCEDURE POPULATE
122 (
123 p_api_version_number IN NUMBER
124 ,p_init_msg_list IN VARCHAR2
125 ,p_validation_level IN NUMBER
126 ,p_commit IN VARCHAR2
127 ,x_msg_count OUT NOCOPY NUMBER
128 ,x_msg_data OUT NOCOPY VARCHAR2
129 ,x_return_status OUT NOCOPY VARCHAR2
130 ,p_start_date IN DATE
131 ,p_end_date IN DATE
132 ,p_para_num IN NUMBER
133 ,p_truncate_flg IN VARCHAR2
134 ) IS
135
136 l_profile NUMBER;
137 v_error_code NUMBER;
138 v_error_text VARCHAR2(1500);
139 l_last_update_date DATE;
140 l_user_id NUMBER := FND_GLOBAL.USER_ID();
141 l_api_version_number CONSTANT NUMBER := 1.0;
142 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE';
143 l_success VARCHAR2(3);
144 l_mesg_text VARCHAR2(100);
145 l_load_type VARCHAR2(100);
146 l_period_error VARCHAR2(5000);
147 l_currency_error VARCHAR2(5000);
148 l_err_code NUMBER;
149 l_count number := 0;
150 l_global_start_date DATE;
151 l_missing_date BOOLEAN := FALSE;
152
153 l_conc_start_date DATE;
154 l_conc_end_date DATE;
155 l_start_date DATE;
156 l_end_date DATE;
157 l_sysdate DATE;
158 l_global_date DATE;
159
160 BEGIN
161
162 -- Standard call to check for call compatibility.
163 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
164 p_api_version_number,
165 l_api_name,
166 g_pkg_name)
167 THEN
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170
171 -- Initialize message list if p_init_msg_list is set to TRUE.
172 IF FND_API.to_Boolean( p_init_msg_list )
173 THEN
174 FND_MSG_PUB.initialize;
175 END IF;
176
177 -- Initialize API return status to SUCCESS
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 -- Debug Message
181 -- AMS_UTILITY_PVT.debug_message('Private API: ' || 'Running the populate procedure');
182 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: Facts load starts at:'||sysdate);
183
184 l_global_start_date :=BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE();
185
186 /* THIS CODE REPLACES THE GET_LAST_REFRESH_PERIOD TO GET_LAST_REFRESH_DATES */
187
188 bis_collection_utilities.get_last_refresh_dates('MARKETING_FACTS'
189 ,l_conc_start_date,l_conc_end_date,l_start_date,l_end_date);
190
191
192 IF (l_end_date IS NULL) THEN
193
194 IF (p_start_date IS NULL) THEN
195 bis_collection_utilities.log('Please run the Upadate Marketing Facts First Time Base Summary concurrent program before running this');
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198
199 IF (p_start_date >= p_end_date) THEN
200 bis_collection_utilities.log('Start Date Can not be greater than End Date');
201 RAISE FND_API.G_EXC_ERROR;
202 END IF;
203 --Validate time dimension tables
204 fii_time_api.check_missing_date (greatest(l_global_start_date,p_start_date), sysdate, l_missing_date);
205 IF (l_missing_date) THEN
206 bis_collection_utilities.log('Time dimension has at least one missing date between ' || greatest(l_global_start_date,p_start_date) || ' and ' || sysdate);
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209
210 l_load_type := 'FIRST_LOAD';
211 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: First Load');
212 FIRST_LOAD(p_start_date => greatest(l_global_start_date,p_start_date)
213 ,p_end_date => sysdate
214 ,p_api_version_number => l_api_version_number
215 ,p_init_msg_list => FND_API.G_FALSE
216 ,p_load_type => l_load_type
217 ,x_msg_count => x_msg_count
218 ,x_msg_data => x_msg_data
219 ,x_return_status => x_return_status
220 );
221
222 ELSE
223 --i.e Incremental has to be executed.
224 IF p_truncate_flg = 'Y' THEN
225
226 l_load_type := 'FIRST_LOAD';
227 l_sysdate := sysdate;
228
229 FIRST_LOAD(p_start_date => greatest(l_global_start_date,p_start_date)
230 ,p_end_date => l_sysdate
231 ,p_api_version_number => l_api_version_number
232 ,p_init_msg_list => FND_API.G_FALSE
233 ,p_load_type => l_load_type
234 ,x_msg_count => x_msg_count
235 ,x_msg_data => x_msg_data
236 ,x_return_status => x_return_status
237 );
238 ELSE
239
240 IF (l_end_date >= sysdate) THEN
241 bis_collection_utilities.log('Load Progarm already run upto ' || l_end_date);
242 RAISE FND_API.g_exc_error;
243 END IF;
244 --Validate time dimension tables
245 fii_time_api.check_missing_date (l_end_date, sysdate, l_missing_date);
246 IF (l_missing_date) THEN
247 bis_collection_utilities.log('Time dimension has atleast one missing date between ' || l_end_date || ' and ' || sysdate);
248 RAISE FND_API.G_EXC_ERROR;
249 END IF;
250
251 l_load_type := 'SUBSEQUENT_LOAD';
252 /*BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: Incremental Load');
253 SUB_LOAD(p_start_date => l_end_date +1/86400
254 ,p_end_date => sysdate
255 ,p_api_version_number => l_api_version_number
256 ,p_init_msg_list => FND_API.G_FALSE
257 ,p_load_type => l_load_type
258 ,x_msg_count => x_msg_count
259 ,x_msg_data => x_msg_data
260 ,x_return_status => x_return_status
261 );*/
262
263 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: Incremental Load');
264 SUB_LOAD(p_start_date => trunc(l_end_date)
265 ,p_end_date => sysdate
266 ,p_api_version_number => l_api_version_number
267 ,p_init_msg_list => FND_API.G_FALSE
268 ,p_load_type => l_load_type
269 ,x_msg_count => x_msg_count
270 ,x_msg_data => x_msg_data
271 ,x_return_status => x_return_status
272 );
273 END IF;
274 END IF;
275
276 IF x_return_status = FND_API.g_ret_sts_error
277 THEN
278 RAISE FND_API.g_exc_error;
279 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
280 RAISE FND_API.g_exc_unexpected_error;
281 END IF;
282 --Standard check of commit
283
284 IF FND_API.To_Boolean ( p_commit ) THEN
285 COMMIT WORK;
286 END IF;
287
288 COMMIT;
289 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: Facts Concurrent Program Succesfully Completed');
290
291 -- Standard call to get message count and if count is 1, get message info.
292 FND_MSG_PUB.Count_And_Get
293 (p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296
297 EXCEPTION
298
299 WHEN FND_API.G_EXC_ERROR THEN
300 x_return_status := FND_API.G_RET_STS_ERROR;
301 -- Standard call to get message count and if count=1, get the message
302 FND_msg_PUB.Count_And_Get (
303 -- p_encoded => FND_API.G_FALSE,
304 p_count => x_msg_count,
305 p_data => x_msg_data
306 );
307
308
309 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
310
311 /* FOR l_counter IN 1 .. x_msg_count
312 LOOP
313 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
314 fnd_msg_pub.dump_msg(l_counter);
315 end loop; */
316
317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
318 -- Standard call to get message count and if count=1, get the message
319 FND_msg_PUB.Count_And_Get (
320 --p_encoded => FND_API.G_FALSE,
321 p_count => x_msg_count,
322 p_data => x_msg_data
323 );
324
325 WHEN OTHERS THEN
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
328 THEN
329 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
330 END IF;
331 -- Standard call to get message count and if count=1, get the message
332 /* FND_msg_PUB.Count_And_Get (
333 -- p_encoded => FND_API.G_FALSE,
334 p_count => x_msg_count,
335 p_data => x_msg_data
336 );*/
337
338 END POPULATE;
339
340
341 --------------------------------------------------------------------------------------------------
342 -- This procedure will excute when data is loaded for the first time
343
344 -- PROCEDURE FIRST_LOAD
345 --------------------------------------------------------------------------------------------------
346
347 PROCEDURE FIRST_LOAD
348 ( p_start_date IN DATE
349 ,p_end_date IN DATE
350 ,p_api_version_number IN NUMBER
351 ,p_init_msg_list IN VARCHAR2
352 ,p_load_type IN VARCHAR2
353 ,x_msg_count OUT NOCOPY NUMBER
354 ,x_msg_data OUT NOCOPY VARCHAR2
355 ,x_return_status OUT NOCOPY VARCHAR2
356 )
357 IS
358 l_user_id NUMBER := FND_GLOBAL.USER_ID();
359 l_start_date DATE;
360 l_end_date DATE;
361 l_last_update_date DATE;
362 l_success VARCHAR2(3);
363 l_api_version_number CONSTANT NUMBER := 1.0;
364 l_api_name CONSTANT VARCHAR2(30) := 'FIRST_LOAD';
365 l_seq_name VARCHAR(100);
366 l_table_name VARCHAR2(100);
367 l_temp_msg VARCHAR2(100);
368 l_global_currency_code VARCHAR2(50);
369 l_check_missing_rate NUMBER;
370 l_stmt VARCHAR2(50);
371 l_min_date date;
372
373 l_status VARCHAR2(5);
374 l_industry VARCHAR2(5);
375 l_schema VARCHAR2(30);
376 l_return BOOLEAN;
377
378 BEGIN
379
380 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
381
382 --dbms_output.put_line('inside first load:'|| p_start_date || ' '|| p_end_date);
383 l_global_currency_code := bis_common_parameters.get_currency_code;
384
385 -- Standard call to check for call compatibility.
386 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
387 p_api_version_number,
388 l_api_name,
389 g_pkg_name)
390 THEN
391 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
392 END IF;
393
394 -- Initialize message list if p_init_msg_list is set to TRUE.
395 IF FND_API.to_Boolean( p_init_msg_list )
396 THEN
397 FND_msg_PUB.initialize;
398 END IF;
399
400 -- Initialize API return status to SUCCESS
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402
403 --dbms_output.put_line('BIM_I_MARKETING_FACTS: Running the First Load '||sqlerrm(sqlcode));
404
405 -- The below four commands are necessary for the purpose of the parallel insertion */
406 BEGIN
407 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
408 --EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000 ';
409 --EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000 ';
410 --EXECUTE IMMEDIATE 'ALTER TABLE bim_i_marketing_facts nologging ';
411 -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_MARKETING_FACTS_s CACHE 1000 ';
412
413
414 /* Piece of Code for retrieving,storing storage parameters and Dropping the indexes */
415 /*fnd_message.set_name('BIM','BIM_R_DROP_INDEXES');
416 fnd_file.put_line(fnd_file.log,fnd_message.get);*/
417 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Drop index before inserting.');
418 BIM_UTL_PKG.drop_index('BIM_I_MARKETING_FACTS');
419 /* End of Code for dropping the existing indexes */
420 EXCEPTION when others then
421 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: error:'||sqlerrm(sqlcode));
422 --dbms_output.put_line('error first:'||sqlerrm(sqlcode));
423 END;
424 EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_MARKETING_FACTS_STG';
425 EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_MKT_RATES';
426 l_table_name :='bim_i_marketing_facts';
427 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Inserting table bim_i_marketing_facts_stg');
428 /*fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
429 fnd_message.set_token('TABLE_NAME',l_table_name, FALSE);
430 fnd_file.put_line(fnd_file.log,fnd_message.get);*/
431 -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');
432 INSERT /*+ append parallel */
433 INTO BIM_I_MARKETING_FACTS_STG CDF (
434 --MKT_DAILY_TRANSACTION_ID ,
435 CREATION_DATE ,
436 LAST_UPDATE_DATE ,
437 CREATED_BY ,
438 LAST_UPDATED_BY ,
439 LAST_UPDATE_LOGIN ,
440 TRANSACTION_CREATE_DATE ,
441 LEAD_ID ,
442 METRIC_TYPE ,
443 SOURCE_CODE_ID ,
444 OBJECT_TYPE ,
445 OBJECT_ID ,
446 CHILD_OBJECT_TYPE ,
447 CHILD_OBJECT_ID ,
448 LEAD_RANK_ID ,
449 OBJECT_COUNTRY ,
450 OBJECT_REGION ,
451 CHILD_OBJECT_COUNTRY ,
452 CHILD_OBJECT_REGION ,
453 CATEGORY_ID ,
454 BUSINESS_UNIT_ID ,
455 START_DATE ,
456 END_DATE ,
457 OBJECT_STATUS ,
458 CHILD_OBJECT_STATUS ,
459 OBJECT_PURPOSE ,
460 CHILD_OBJECT_PURPOSE ,
461 ACTIVITY_TYPE ,
462 ACTIVITY_ID ,
463 CONVERSION_RATE ,
464 FROM_CURRENCY ,
465 LEADS ,
466 OPPORTUNITIES ,
467 OPPORTUNITY_AMT ,
468 OPPORTUNITIES_OPEN ,
469 ORDERS_BOOKED ,
470 ORDERS_BOOKED_AMT ,
471 REVENUE_FORECASTED ,
472 REVENUE_ACTUAL ,
473 COST_FORECASTED ,
474 COST_ACTUAL ,
475 BUDGET_APPROVED ,
476 BUDGET_REQUESTED ,
477 RESPONSES_FORECASTED ,
478 RESPONSES_POSITIVE ,
479 CUSTOMERS_TARGETED ,
480 CUSTOMERS_NEW ,
481 REGISTRATIONS ,
482 CANCELLATIONS ,
483 ATTENDANCE ,
484 OPPORTUNITY_AMT_S ,
485 ORDERS_BOOKED_AMT_S ,
486 REVENUE_FORECASTED_S ,
487 REVENUE_ACTUAL_S ,
488 COST_FORECASTED_S ,
489 COST_ACTUAL_S ,
490 BUDGET_REQUESTED_S ,
491 BUDGET_APPROVED_S ,
492 CONVERSION_RATE_S ,
493 metric1 ,
494 metric2
495 )
496 SELECT /*+ parallel */
497 -- BIM_I_MARKETING_FACTS_s.nextval ,
498 sysdate
499 ,sysdate
500 ,-1
501 ,-1
502 ,-1
503 ,transaction_create_date
504 ,lead_id
505 ,metric_type
506 ,source_code_id
507 ,object_type
508 ,object_id
509 ,child_object_type
510 ,child_object_id
511 ,lead_rank_id
512 ,object_country
513 ,object_region
514 ,child_object_country
515 ,child_object_region
516 ,nvl(category_id,-1)
517 ,business_unit_id
518 ,start_date
519 ,end_date
520 ,object_status
521 ,child_object_status
522 ,object_purpose
523 ,child_object_purpose
524 ,activity_type
525 ,activity_id
526 ,conversion_rate
527 ,from_currency
528 ,leads
529 ,opportunities
530 ,opportunity_amt
531 ,opportunities_open
532 ,orders_booked
533 ,orders_booked_amt
534 ,revenue_forecasted
535 ,revenue_actual
536 ,cost_forecasted
537 ,cost_actual
538 ,budget_approved
539 ,budget_requested
540 ,responses_forecasted
541 ,responses_positive
542 ,customers_targeted
543 ,customers_new
544 ,registrations
545 ,cancellations
546 ,attendance
547 ,OPPORTUNITY_AMT_S
548 ,ORDERS_BOOKED_AMT_S
549 ,REVENUE_FORECASTED_S
550 ,REVENUE_ACTUAL_S
551 ,COST_FORECASTED_S
552 ,COST_ACTUAL_S
553 ,BUDGET_REQUESTED_S
554 ,BUDGET_APPROVED_S
555 ,CONVERSION_RATE_S
556 ,metric1
557 ,metric2
558 FROM (
559 SELECT transaction_create_date transaction_create_date
560 ,lead_id lead_id
561 ,metric_type metric_type
562 ,source_code_id source_code_id
563 ,object_type object_type
564 ,object_id object_id
565 ,child_object_type child_object_type
566 ,child_object_id child_object_id
567 ,lead_rank_id lead_rank_id
568 ,object_country object_country
569 ,object_region object_region
570 ,child_object_country child_object_country
571 ,child_object_region child_object_region
572 ,category_id category_id
573 ,business_unit_id business_unit_id
574 ,start_date start_date
575 ,end_date end_date
576 ,object_status object_status
577 ,child_object_status child_object_status
578 ,object_purpose object_purpose
579 ,child_object_purpose child_object_purpose
580 ,activity_type activity_type
581 ,activity_id activity_id
582 ,conversion_rate
583 ,from_currency
584 ,sum(leads) leads
585 ,sum(opportunities) opportunities
586 ,sum(opportunity_amt) opportunity_amt
587 ,sum(opportunities_open) opportunities_open
588 ,sum(orders_booked) orders_booked
589 ,sum(orders_booked_amt) orders_booked_amt
590 ,sum(budget_requested) budget_requested
591 ,sum(budget_approved) budget_approved
592 ,sum(revenue_forecasted) revenue_forecasted
593 ,sum(revenue_actual) revenue_actual
594 ,sum(cost_forecasted) cost_forecasted
595 ,sum(cost_actual) cost_actual
596 ,sum(responses_forecasted) responses_forecasted
597 ,sum(responses_positive) responses_positive
598 ,sum(customers_targeted) customers_targeted
599 ,sum(customers_new) customers_new
600 ,sum(registrations) registrations
601 ,sum(cancellations) cancellations
602 ,sum(attendance) attendance
603 ,sum(OPPORTUNITY_AMT_S) OPPORTUNITY_AMT_S
604 ,sum(ORDERS_BOOKED_AMT_S) ORDERS_BOOKED_AMT_S
605 ,sum(REVENUE_FORECASTED_S) REVENUE_FORECASTED_S
606 ,sum(REVENUE_ACTUAL_S ) REVENUE_ACTUAL_S
607 ,sum(COST_FORECASTED_S ) COST_FORECASTED_S
608 ,sum(COST_ACTUAL_S ) COST_ACTUAL_S
609 ,sum(BUDGET_REQUESTED_S ) BUDGET_REQUESTED_S
610 ,sum(BUDGET_APPROVED_S) BUDGET_APPROVED_S
611 ,CONVERSION_RATE_S CONVERSION_RATE_S
612 ,sum(metric1) metric1
613 ,sum(metric2) metric2
614 FROM (
615 --actual revenue
616 SELECT trunc(f3.last_update_date) transaction_create_date
617 ,0 lead_id
618 ,'OTHER' metric_type
619 ,a.source_code_id source_code_id
620 ,a.object_type object_type
621 ,a.object_id object_id
622 ,a.child_object_type child_object_type
623 ,a.child_object_id child_object_id
624 ,0 lead_rank_id
625 ,a.object_country
626 ,a.object_region
627 ,a.child_object_country
628 ,a.child_object_region
629 ,a.category_id
630 ,a.business_unit_id business_unit_id
631 ,a.start_date
632 ,a.end_date
633 ,a.object_status object_status
634 ,a.child_object_status child_object_status
635 ,a.object_purpose object_purpose
636 ,a.child_object_purpose child_object_purpose
637 ,a.activity_type activity_type
638 ,a.activity_id activity_id
639 ,0 conversion_rate
640 ,nvl(f3.functional_currency_code,'USD') from_currency
641 ,0 leads
642 ,0 opportunities
643 ,0 opportunity_amt
644 ,0 opportunities_open
645 ,0 quotes
646 ,0 quotes_open
647 ,0 orders_booked
648 ,0 orders_booked_amt
649 ,0 budget_requested
650 ,0 budget_approved
651 ,0 revenue_forecasted
652 ,sum(nvl(f3.func_actual_delta,0)) revenue_actual
653 ,0 cost_forecasted
654 ,0 cost_actual
655 ,0 responses_forecasted
656 ,0 responses_positive
657 ,0 customers_targeted
658 ,0 customers_new
659 ,0 registrations
660 ,0 cancellations
661 ,0 attendance
662 ,0 OPPORTUNITY_AMT_S
663 ,0 ORDERS_BOOKED_AMT_S
664 ,0 REVENUE_FORECASTED_S
665 ,0 REVENUE_ACTUAL_S
666 ,0 COST_FORECASTED_S
667 ,0 COST_ACTUAL_S
668 ,0 BUDGET_REQUESTED_S
669 ,0 BUDGET_APPROVED_S
670 ,0 conversion_rate_s
671 ,0 metric1
672 ,0 metric2
673 FROM ams_act_metric_hst f3
674 ,ams_metrics_all_b g3
675 ,bim_i_source_codes a
676 WHERE f3.last_update_date between p_start_date and p_end_date
677 AND f3.arc_act_metric_used_by = a.object_type
678 AND f3.act_metric_used_by_id = a.object_id
679 AND a.child_object_id =0
680 AND a.object_type NOT IN ('RCAM')
681 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
682 AND g3.metric_category = 902
683 --AND g3.metric_parent_id IS NULL
684 AND g3.metric_id = f3.metric_id
685 GROUP BY trunc(f3.last_update_date)
686 ,a.source_code_id
687 ,a.object_type
688 ,a.object_id
689 ,a.child_object_type
690 ,a.child_object_id
691 ,a.object_country
692 ,a.object_region
693 ,a.child_object_country
694 ,a.child_object_region
695 ,a.category_id
696 ,a.object_status
697 ,a.child_object_status
698 ,a.object_purpose
699 ,a.child_object_purpose
700 ,a.activity_type
701 ,a.activity_id
702 ,a.start_date
703 ,a.end_date
704 ,a.business_unit_id
705 ,fii_currency.get_global_rate_primary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
706 ,fii_currency.get_global_rate_secondary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
707 ,nvl(f3.functional_currency_code,'USD')
708 HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
709 union all --actual revenue at schedule level
710 SELECT trunc(f3.last_update_date) transaction_create_date
711 ,0 lead_id
712 ,'REVENUE' metric_type
713 ,a.source_code_id source_code_id
714 ,a.object_type object_type
715 ,a.object_id object_id
716 ,a.child_object_type child_object_type
717 ,a.child_object_id child_object_id
718 ,0 lead_rank_id
719 ,a.object_country
720 ,a.object_region
721 ,a.child_object_country
722 ,a.child_object_region
723 ,a.category_id
724 ,a.business_unit_id business_unit_id
725 ,a.start_date
726 ,a.end_date
727 ,a.object_status object_status
728 ,a.child_object_status child_object_status
729 ,a.object_purpose object_purpose
730 ,a.child_object_purpose child_object_purpose
731 ,a.activity_type activity_type
732 ,a.activity_id activity_id
733 ,0 conversion_rate
734 ,nvl(f3.functional_currency_code,'USD') from_currency
735 ,0 leads
736 ,0 opportunities
737 ,0 opportunity_amt
738 ,0 opportunities_open
739 ,0 quotes
740 ,0 quotes_open
741 ,0 orders_booked
742 ,0 orders_booked_amt
743 ,0 budget_requested
744 ,0 budget_approved
745 ,0 revenue_forecasted
746 ,sum(nvl(f3.func_actual_delta,0)) REVENUE_ACTUAL
747 ,0 cost_forecasted
748 ,0 cost_actual
749 ,0 responses_forecasted
750 ,0 responses_positive
751 ,0 customers_targeted
752 ,0 customers_new
753 ,0 registrations
754 ,0 cancellations
755 ,0 attendance
756 ,0 OPPORTUNITY_AMT_S
757 ,0 ORDERS_BOOKED_AMT_S
758 ,0 REVENUE_FORECASTED_S
759 ,0 REVENUE_ACTUAL_S
760 ,0 COST_FORECASTED_S
761 ,0 COST_ACTUAL_S
762 ,0 BUDGET_REQUESTED_S
763 ,0 BUDGET_APPROVED_S
764 ,0 conversion_rate_s
765 ,0 metric1
766 ,0 metric2
767 FROM ams_act_metric_hst f3
768 ,ams_metrics_all_b g3
769 ,bim_i_source_codes a
770 WHERE f3.last_update_date between p_start_date and p_end_date
771 AND f3.arc_act_metric_used_by IN ('CSCH','EVEO')
772 AND f3.act_metric_used_by_id = a.child_object_id
773 AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
774 --AND a.child_object_id =0
775 AND a.object_type NOT IN ('RCAM')
776 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
777 AND g3.metric_category = 902
778 --AND g3.metric_parent_id IS NULL
779 AND g3.metric_id = f3.metric_id
780 GROUP BY trunc(f3.last_update_date)
781 ,a.source_code_id
782 ,a.object_type
783 ,a.object_id
784 ,a.child_object_type
785 ,a.child_object_id
786 ,a.object_country
787 ,a.object_region
788 ,a.child_object_country
789 ,a.child_object_region
790 ,a.category_id
791 ,a.object_status
792 ,a.child_object_status
793 ,a.object_purpose
794 ,a.child_object_purpose
795 ,a.activity_type
796 ,a.activity_id
797 ,a.start_date
798 ,a.end_date
799 ,a.business_unit_id
800 ,fii_currency.get_global_rate_primary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
801 ,fii_currency.get_global_rate_secondary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
802 ,nvl(f3.functional_currency_code,'USD')
803 HAVING sum(nvl(f3.func_actual_delta,0)) <>0
804 union all --cost
805 SELECT
806 case
807 when trunc(f3.last_update_date) < p_start_date then p_start_date
808 else trunc(f3.last_update_date)
809 end transaction_create_date
810 ,0 lead_id
811 ,'COST' metric_type
812 ,a.source_code_id source_code_id
813 ,a.object_type object_type
814 ,a.object_id object_id
815 ,a.child_object_type child_object_type
816 ,a.child_object_id child_object_id
817 ,0 lead_rank_id
818 ,a.object_country
819 ,a.object_region
820 ,a.child_object_country
821 ,a.child_object_region
822 ,a.category_id
823 ,a.business_unit_id business_unit_id
824 ,a.start_date
825 ,a.end_date
826 ,a.object_status object_status
827 ,a.child_object_status child_object_status
828 ,a.object_purpose object_purpose
829 ,a.child_object_purpose child_object_purpose
830 ,a.activity_type activity_type
831 ,a.activity_id activity_id
832 ,0 conversion_rate
833 ,nvl(f3.functional_currency_code,'USD') from_currency
834 ,0 leads
835 ,0 opportunities
836 ,0 opportunity_amt
837 ,0 opportunities_open
838 ,0 quotes
839 ,0 quotes_open
840 ,0 orders_booked
841 ,0 orders_booked_amt
842 ,0 budget_requested
843 ,0 budget_approved
844 ,0 revenue_forecasted
845 ,0 revenue_actual
846 ,0 cost_forecasted
847 ,sum(nvl(f3.func_actual_delta,0)) cost_actual
848 ,0 responses_forecasted
849 ,0 responses_positive
850 ,0 customers_targeted
851 ,0 customers_new
852 ,0 registrations
853 ,0 cancellations
854 ,0 attendance
855 ,0 OPPORTUNITY_AMT_S
856 ,0 ORDERS_BOOKED_AMT_S
857 ,0 REVENUE_FORECASTED_S
858 ,0 REVENUE_ACTUAL_S
859 ,0 COST_FORECASTED_S
860 ,0 COST_ACTUAL_S
861 ,0 BUDGET_REQUESTED_S
862 ,0 BUDGET_APPROVED_S
863 ,0 conversion_rate_s
864 ,0 metric1
865 ,0 metric2
866 FROM ams_act_metric_hst f3
867 ,ams_metrics_all_b g3
868 ,bim_i_source_codes a
869 WHERE f3.last_update_date <= p_end_date
870 AND f3.arc_act_metric_used_by = a.object_type
871 AND f3.act_metric_used_by_id = a.object_id
872 AND a.child_object_id =0
873 AND a.object_type NOT IN ('RCAM')
874 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
875 AND g3.metric_category = 901
876 --AND g3.metric_parent_id IS NULL
877 AND g3.metric_id = f3.metric_id
878 GROUP BY case
879 when trunc(f3.last_update_date) < p_start_date then p_start_date
880 else trunc(f3.last_update_date)
881 end
882 ,a.source_code_id
883 ,a.object_type
884 ,a.object_id
885 ,a.child_object_type
886 ,a.child_object_id
887 ,a.object_country
888 ,a.object_region
889 ,a.child_object_country
890 ,a.child_object_region
891 ,a.category_id
892 ,a.object_status
893 ,a.child_object_status
894 ,a.object_purpose
895 ,a.child_object_purpose
896 ,a.activity_type
897 ,a.activity_id
898 ,a.start_date
899 ,a.end_date
900 ,a.business_unit_id
901 ,fii_currency.get_global_rate_primary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
902 ,fii_currency.get_global_rate_secondary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
903 ,nvl(f3.functional_currency_code,'USD')
904 HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
905 union all --cost at schedule level
906 SELECT case
907 when trunc(f3.last_update_date) < p_start_date then p_start_date
908 else trunc(f3.last_update_date)
909 end transaction_create_date
910 ,0 lead_id
911 ,'OTHER' metric_type
912 ,a.source_code_id source_code_id
913 ,a.object_type object_type
914 ,a.object_id object_id
915 ,a.child_object_type child_object_type
916 ,a.child_object_id child_object_id
917 ,0 lead_rank_id
918 ,a.object_country
919 ,a.object_region
920 ,a.child_object_country
921 ,a.child_object_region
922 ,a.category_id
923 ,a.business_unit_id business_unit_id
924 ,a.start_date
925 ,a.end_date
926 ,a.object_status object_status
927 ,a.child_object_status child_object_status
928 ,a.object_purpose object_purpose
929 ,a.child_object_purpose child_object_purpose
930 ,a.activity_type activity_type
931 ,a.activity_id activity_id
932 ,0 conversion_rate
933 ,nvl(f3.functional_currency_code,'USD') from_currency
934 ,0 leads
935 ,0 opportunities
936 ,0 opportunity_amt
937 ,0 opportunities_open
938 ,0 quotes
939 ,0 quotes_open
940 ,0 orders_booked
941 ,0 orders_booked_amt
942 ,0 budget_requested
943 ,0 budget_approved
944 ,0 revenue_forecasted
945 ,0 revenue_actual
946 ,0 cost_forecasted
947 ,sum(nvl(f3.func_actual_delta,0)) cost_actual
948 ,0 responses_forecasted
949 ,0 responses_positive
950 ,0 customers_targeted
951 ,0 customers_new
952 ,0 registrations
953 ,0 cancellations
954 ,0 attendance
955 ,0 OPPORTUNITY_AMT_S
956 ,0 ORDERS_BOOKED_AMT_S
957 ,0 REVENUE_FORECASTED_S
958 ,0 REVENUE_ACTUAL_S
959 ,0 COST_FORECASTED_S
960 ,0 COST_ACTUAL_S
961 ,0 BUDGET_REQUESTED_S
962 ,0 BUDGET_APPROVED_S
963 ,0 conversion_rate_s
964 ,0 metric1
965 ,0 metric2
966 FROM ams_act_metric_hst f3
967 ,ams_metrics_all_b g3
968 ,bim_i_source_codes a
969 WHERE f3.last_update_date <= p_end_date
970 AND f3.arc_act_metric_used_by IN ('CSCH','EVEO')
971 AND f3.act_metric_used_by_id = a.child_object_id
972 AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
973 --AND a.child_object_id =0
974 AND a.object_type NOT IN ('RCAM')
975 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
976 AND g3.metric_category = 901
977 --AND g3.metric_parent_id IS NULL
978 AND g3.metric_id = f3.metric_id
979 GROUP BY case
980 when trunc(f3.last_update_date) < p_start_date then p_start_date
981 else trunc(f3.last_update_date)
982 end
983 ,a.source_code_id
984 ,a.object_type
985 ,a.object_id
986 ,a.child_object_type
987 ,a.child_object_id
988 ,a.object_country
989 ,a.object_region
990 ,a.child_object_country
991 ,a.child_object_region
992 ,a.category_id
993 ,a.object_status
994 ,a.child_object_status
995 ,a.object_purpose
996 ,a.child_object_purpose
997 ,a.activity_type
998 ,a.activity_id
999 ,a.start_date
1000 ,a.end_date
1001 ,a.business_unit_id
1002 ,fii_currency.get_global_rate_primary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
1003 ,fii_currency.get_global_rate_secondary(nvl(f3.functional_currency_code,'USD'),f3.last_update_date)
1004 ,nvl(f3.functional_currency_code,'USD')
1005 HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
1006 --sbehera 15 jan 2004
1007 --for campaign forecasted response
1008 union all --forecasted response
1009 SELECT trunc(f3.last_update_date) transaction_create_date
1010 ,0 lead_id
1011 ,'OTHER' metric_type
1012 ,a.source_code_id source_code_id
1013 ,a.object_type object_type
1014 ,a.object_id object_id
1015 ,a.child_object_type child_object_type
1016 ,a.child_object_id child_object_id
1017 ,0 lead_rank_id
1018 ,a.object_country
1019 ,a.object_region
1020 ,a.child_object_country
1021 ,a.child_object_region
1022 ,a.category_id
1023 ,a.business_unit_id business_unit_id
1024 ,a.start_date
1025 ,a.end_date
1026 ,a.object_status object_status
1027 ,a.child_object_status child_object_status
1028 ,a.object_purpose object_purpose
1029 ,a.child_object_purpose child_object_purpose
1030 ,a.activity_type activity_type
1031 ,a.activity_id activity_id
1032 ,0 conversion_rate
1033 ,null from_currency
1034 ,0 leads
1035 ,0 opportunities
1036 ,0 opportunity_amt
1037 ,0 opportunities_open
1038 ,0 quotes
1039 ,0 quotes_open
1040 ,0 orders_booked
1041 ,0 orders_booked_amt
1042 ,0 budget_requested
1043 ,0 budget_approved
1044 ,0 revenue_forecasted
1045 ,0 revenue_actual
1046 ,0 cost_forecasted
1047 ,0 cost_actual
1048 ,sum(nvl(f3.func_forecasted_delta,0)) responses_forecasted
1049 ,0 responses_positive
1050 ,0 customers_targeted
1051 ,0 customers_new
1052 ,0 registrations
1053 ,0 cancellations
1054 ,0 attendance
1055 ,0 OPPORTUNITY_AMT_S
1056 ,0 ORDERS_BOOKED_AMT_S
1057 ,0 REVENUE_FORECASTED_S
1058 ,0 REVENUE_ACTUAL_S
1059 ,0 COST_FORECASTED_S
1060 ,0 COST_ACTUAL_S
1061 ,0 BUDGET_REQUESTED_S
1062 ,0 BUDGET_APPROVED_S
1063 ,0 conversion_rate_S
1064 ,0 metric1
1065 ,0 metric2
1066 FROM ams_act_metric_hst f3
1067 ,ams_metrics_all_b g3
1068 ,bim_i_source_codes a
1069 WHERE f3.last_update_date between p_start_date and p_end_date
1070 AND f3.arc_act_metric_used_by = a.object_type
1071 AND f3.act_metric_used_by_id = a.object_id
1072 AND a.child_object_id =0
1073 --AND a.object_type NOT IN ('RCAM')
1074 -- AND a.object_type='CAMP' commented for camp,event and one off
1075 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
1076 AND g3.metric_category = 903
1077 AND g3.metric_id = f3.metric_id
1078 GROUP BY trunc(f3.last_update_date)
1079 ,a.source_code_id
1080 ,a.object_type
1081 ,a.object_id
1082 ,a.child_object_type
1083 ,a.child_object_id
1084 ,a.object_country
1085 ,a.object_region
1086 ,a.child_object_country
1087 ,a.child_object_region
1088 ,a.category_id
1089 ,a.object_status
1090 ,a.child_object_status
1091 ,a.object_purpose
1092 ,a.child_object_purpose
1093 ,a.activity_type
1094 ,a.activity_id
1095 ,a.start_date
1096 ,a.end_date
1097 ,a.business_unit_id
1098 HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
1099 --for campaign schedule forecasted response
1100 union all --forecasted campaign schedule response
1101 SELECT trunc(f3.last_update_date) transaction_create_date
1102 ,0 lead_id
1103 ,'OTHER' metric_type
1104 ,a.source_code_id source_code_id
1105 ,a.object_type object_type
1106 ,a.object_id object_id
1107 ,a.child_object_type child_object_type
1108 ,a.child_object_id child_object_id
1109 ,0 lead_rank_id
1110 ,a.object_country
1111 ,a.object_region
1112 ,a.child_object_country
1113 ,a.child_object_region
1114 ,a.category_id
1115 ,a.business_unit_id business_unit_id
1116 ,a.start_date
1117 ,a.end_date
1118 ,a.object_status object_status
1119 ,a.child_object_status child_object_status
1120 ,a.object_purpose object_purpose
1121 ,a.child_object_purpose child_object_purpose
1122 ,a.activity_type activity_type
1123 ,a.activity_id activity_id
1124 ,0 conversion_rate
1125 ,null from_currency
1126 ,0 leads
1127 ,0 opportunities
1128 ,0 opportunity_amt
1129 ,0 opportunities_open
1130 ,0 quotes
1131 ,0 quotes_open
1132 ,0 orders_booked
1133 ,0 orders_booked_amt
1134 ,0 budget_requested
1135 ,0 budget_approved
1136 ,0 revenue_forecasted
1137 ,0 revenue_actual
1138 ,0 cost_forecasted
1139 ,0 cost_actual
1140 ,sum(nvl(f3.func_forecasted_delta,0)) responses_forecasted
1141 ,0 responses_positive
1142 ,0 customers_targeted
1143 ,0 customers_new
1144 ,0 registrations
1145 ,0 cancellations
1146 ,0 attendance
1147 ,0 OPPORTUNITY_AMT_S
1148 ,0 ORDERS_BOOKED_AMT_S
1149 ,0 REVENUE_FORECASTED_S
1150 ,0 REVENUE_ACTUAL_S
1151 ,0 COST_FORECASTED_S
1152 ,0 COST_ACTUAL_S
1153 ,0 BUDGET_REQUESTED_S
1154 ,0 BUDGET_APPROVED_S
1155 ,0 conversion_rate_S
1156 ,0 metric1
1157 ,0 metric2
1158 FROM ams_act_metric_hst f3
1159 ,ams_metrics_all_b g3
1160 ,bim_i_source_codes a
1161 WHERE f3.last_update_date between p_start_date and p_end_date
1162 AND f3.act_metric_used_by_id = a.child_object_id
1163 AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
1164 --AND a.object_type NOT IN ('RCAM')
1165 AND a.child_object_type in('CSCH','EVEO')
1166 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
1167 AND g3.metric_category = 903
1168 AND g3.metric_id = f3.metric_id
1169 GROUP BY trunc(f3.last_update_date)
1170 ,a.source_code_id
1171 ,a.object_type
1172 ,a.object_id
1173 ,a.child_object_type
1174 ,a.child_object_id
1175 ,a.object_country
1176 ,a.object_region
1177 ,a.child_object_country
1178 ,a.child_object_region
1179 ,a.category_id
1180 ,a.object_status
1181 ,a.child_object_status
1182 ,a.object_purpose
1183 ,a.child_object_purpose
1184 ,a.activity_type
1185 ,a.activity_id
1186 ,a.start_date
1187 ,a.end_date
1188 ,a.business_unit_id
1189 HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
1190 union all --targeted audience
1191 SELECT trunc(p.creation_date) transaction_create_date
1192 ,0 lead_id
1193 ,'OTHER' metric_type
1194 ,a.source_code_id source_code_id
1195 ,a.object_type object_type
1196 ,a.object_id object_id
1197 ,a.child_object_type child_object_type
1198 ,a.child_object_id child_object_id
1199 ,0 lead_rank_id
1200 ,a.object_country
1201 ,a.object_region
1202 ,a.child_object_country
1203 ,a.child_object_region
1204 ,a.category_id
1205 ,a.business_unit_id business_unit_id
1206 ,a.start_date
1207 ,a.end_date
1208 ,a.object_status object_status
1209 ,a.child_object_status child_object_status
1210 ,a.object_purpose object_purpose
1211 ,a.child_object_purpose child_object_purpose
1212 ,a.activity_type activity_type
1213 ,a.activity_id activity_id
1214 ,0 conversion_rate
1215 ,null from_currency
1216 ,0 leads
1217 ,0 opportunities
1218 ,0 opportunity_amt
1219 ,0 opportunities_open
1220 ,0 quotes
1221 ,0 quotes_open
1222 ,0 orders_booked
1223 ,0 orders_booked_amt
1224 ,0 budget_requested
1225 ,0 budget_approved
1226 ,0 revenue_forecasted
1227 ,0 revenue_actual
1228 ,0 cost_forecasted
1229 ,0 cost_actual
1230 ,0 responses_forecasted
1231 ,0 responses_positive
1232 ,count(p.list_entry_id) customers_targeted
1233 ,0 customers_new
1234 ,0 registrations
1235 ,0 cancellations
1236 ,0 attendance
1237 ,0 OPPORTUNITY_AMT_S
1238 ,0 ORDERS_BOOKED_AMT_S
1239 ,0 REVENUE_FORECASTED_S
1240 ,0 REVENUE_ACTUAL_S
1241 ,0 COST_FORECASTED_S
1242 ,0 COST_ACTUAL_S
1243 ,0 BUDGET_REQUESTED_S
1244 ,0 BUDGET_APPROVED_S
1245 ,0 conversion_rate_S
1246 ,0 metric1
1247 ,0 metric2
1248 FROM ams_list_entries p
1249 ,ams_act_lists q
1250 ,bim_i_source_codes a
1251 WHERE p.creation_date between p_start_date and p_end_date
1252 AND p.list_header_id = q.list_header_id
1253 AND q.list_used_by = a.child_object_type
1254 AND q.list_used_by_id = a.child_object_id
1255 AND a.object_type NOT IN ('RCAM')
1256 AND q.list_used_by in ('CSCH','EVEO')
1257 AND q.list_act_type = 'TARGET'
1258 AND p.enabled_flag='Y'
1259 GROUP BY trunc(p.creation_date)
1260 ,a.source_code_id
1261 ,a.object_type
1262 ,a.object_id
1263 ,a.child_object_type
1264 ,a.child_object_id
1265 ,a.object_country
1266 ,a.object_region
1267 ,a.child_object_country
1268 ,a.child_object_region
1269 ,a.category_id
1270 ,a.object_status
1271 ,a.child_object_status
1272 ,a.object_purpose
1273 ,a.child_object_purpose
1274 ,a.activity_type
1275 ,a.activity_id
1276 ,a.start_date
1277 ,a.end_date
1278 ,a.business_unit_id
1279 union all --targeted audience for schedules of type event
1280 SELECT trunc(p.creation_date) transaction_create_date
1281 ,0 lead_id
1282 ,'OTHER' metric_type
1283 ,a.source_code_id source_code_id
1284 ,a.object_type object_type
1285 ,a.object_id object_id
1286 ,a.child_object_type child_object_type
1287 ,a.child_object_id child_object_id
1288 ,0 lead_rank_id
1289 ,a.object_country
1290 ,a.object_region
1291 ,a.child_object_country
1292 ,a.child_object_region
1293 ,a.category_id
1294 ,a.business_unit_id business_unit_id
1295 ,a.start_date
1296 ,a.end_date
1297 ,a.object_status object_status
1298 ,a.child_object_status child_object_status
1299 ,a.object_purpose object_purpose
1300 ,a.child_object_purpose child_object_purpose
1301 ,a.activity_type activity_type
1302 ,a.activity_id activity_id
1303 ,0 conversion_rate
1304 ,null from_currency
1305 ,0 leads
1306 ,0 opportunities
1307 ,0 opportunity_amt
1308 ,0 opportunities_open
1309 ,0 quotes
1310 ,0 quotes_open
1311 ,0 orders_booked
1312 ,0 orders_booked_amt
1313 ,0 budget_requested
1314 ,0 budget_approved
1315 ,0 revenue_forecasted
1316 ,0 revenue_actual
1317 ,0 cost_forecasted
1318 ,0 cost_actual
1319 ,0 responses_forecasted
1320 ,0 responses_positive
1321 ,count(p.list_entry_id) customers_targeted
1322 ,0 customers_new
1323 ,0 registrations
1324 ,0 cancellations
1325 ,0 attendance
1326 ,0 OPPORTUNITY_AMT_S
1327 ,0 ORDERS_BOOKED_AMT_S
1328 ,0 REVENUE_FORECASTED_S
1329 ,0 REVENUE_ACTUAL_S
1330 ,0 COST_FORECASTED_S
1331 ,0 COST_ACTUAL_S
1332 ,0 BUDGET_REQUESTED_S
1333 ,0 BUDGET_APPROVED_S
1334 ,0 conversion_rate_S
1335 ,0 metric1
1336 ,0 metric2
1337 FROM ams_list_entries p
1338 ,ams_act_lists q
1339 ,bim_i_source_codes a
1340 ,ams_campaign_schedules_b sch
1341 WHERE p.creation_date between p_start_date and p_end_date
1342 AND p.list_header_id = q.list_header_id
1343 AND q.list_used_by = 'EONE'
1344 AND q.list_act_type = 'TARGET'
1345 AND sch.schedule_id = a.child_object_id
1346 AND a.child_object_type = 'CSCH'
1347 AND sch.activity_type_code = 'EVENTS'
1348 AND q.list_used_by_id = sch.related_event_id
1349 AND a.object_type NOT IN ('RCAM')
1350 AND p.enabled_flag='Y'
1351 GROUP BY trunc(p.creation_date)
1352 ,a.source_code_id
1353 ,a.object_type
1354 ,a.object_id
1355 ,a.child_object_type
1356 ,a.child_object_id
1357 ,a.object_country
1358 ,a.object_region
1359 ,a.child_object_country
1360 ,a.child_object_region
1361 ,a.category_id
1362 ,a.object_status
1363 ,a.child_object_status
1364 ,a.object_purpose
1365 ,a.child_object_purpose
1366 ,a.activity_type
1367 ,a.activity_id
1368 ,a.start_date
1369 ,a.end_date
1370 ,a.business_unit_id
1371 union all
1372 --budget1
1373 SELECT /*+ USE_HASH(S A B) */
1374 case
1375 when trunc(nvl(s.approval_date,s.last_update_date)) < p_start_date then p_start_date
1376 else trunc(nvl(s.approval_date,s.last_update_date))
1377 end transaction_create_date
1378 ,0 lead_id
1379 ,'OTHER' metric_type
1380 ,a.source_code_id source_code_id
1381 ,a.object_type object_type
1382 ,a.object_id object_id
1383 ,a.child_object_type child_object_type
1384 ,a.child_object_id child_object_id
1385 ,0 lead_rank_id
1386 ,a.object_country
1387 ,a.object_region
1388 ,a.child_object_country
1389 ,a.child_object_region
1390 ,a.category_id
1391 ,a.business_unit_id business_unit_id
1392 ,a.start_date
1393 ,a.end_date
1394 ,a.object_status object_status
1395 ,a.child_object_status child_object_status
1396 ,a.object_purpose object_purpose
1397 ,a.child_object_purpose child_object_purpose
1398 ,a.activity_type activity_type
1399 ,a.activity_id activity_id
1400 ,0 conversion_rate_s
1401 ,nvl(s.request_currency,'USD') from_currency
1402 ,0 leads
1403 ,0 opportunities
1404 ,0 opportunity_amt
1405 ,0 opportunities_open
1406 ,0 quotes
1407 ,0 quotes_open
1408 ,0 orders_booked
1409 ,0 orders_booked_amt
1410 ,0 budget_requested
1411 ,sum(nvl(s.approved_amount,0)) budget_approved
1412 ,0 revenue_forecasted
1413 ,0 revenue_actual
1414 ,0 cost_actual
1415 ,0 cost_forecasted
1416 ,0 responses_forecasted
1417 ,0 responses_positive
1418 ,0 customers_targeted
1419 ,0 customers_new
1420 ,0 registrations
1421 ,0 cancellations
1422 ,0 attendance
1423 ,0 OPPORTUNITY_AMT_S
1424 ,0 ORDERS_BOOKED_AMT_S
1425 ,0 REVENUE_FORECASTED_S
1426 ,0 REVENUE_ACTUAL_S
1427 ,0 COST_FORECASTED_S
1428 ,0 COST_ACTUAL_S
1429 ,0 BUDGET_REQUESTED_S
1430 ,0 BUDGET_APPROVED_S
1431 ,0 conversion_rate_s
1432 ,0 metric1
1433 ,0 metric2
1434 FROM ozf_act_budgets S
1435 ,bim_i_source_codes A
1436 ,ams_source_codes B
1437 WHERE s.act_budget_used_by_id = b.source_code_for_id
1438 AND s.arc_act_budget_used_by = b.arc_source_code_for
1439 AND b.source_code_id = a.source_code_id
1440 AND a.object_type NOT IN ('RCAM')
1441 AND s.budget_source_type = 'FUND'
1442 AND s.parent_act_budget_id IS NULL
1443 AND a.start_date <= p_end_date
1444 AND trunc(nvl(s.approval_date,s.last_update_date)) <= p_end_date
1445 AND s.status_code = 'APPROVED'
1446 GROUP BY
1447 case
1448 when trunc(nvl(s.approval_date,s.last_update_date)) < p_start_date then p_start_date
1449 else trunc(nvl(s.approval_date,s.last_update_date))
1450 end
1451 ,a.source_code_id
1452 ,a.object_id
1453 ,a.object_type
1454 ,a.child_object_type
1455 ,a.child_object_id
1456 ,a.object_country
1457 ,a.child_object_country
1458 ,a.object_region
1459 ,a.child_object_region
1460 ,a.category_id
1461 ,a.object_status
1462 ,a.child_object_status
1463 ,a.object_purpose
1464 ,a.child_object_purpose
1465 ,a.activity_type
1466 ,a.activity_id
1467 ,a.business_unit_id
1468 ,a.start_date
1469 ,a.end_date
1470 ,fii_currency.get_global_rate_primary(nvl(s.request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
1471 ,fii_currency.get_global_rate_secondary(nvl(s.request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
1472 ,nvl(s.request_currency,'USD')
1473 HAVING sum(nvl(s.approved_amount,0)) > 0
1474 --budget2
1475 union all
1476 SELECT /*+ USE_HASH(S A B) */
1477 case
1478 when trunc(a.start_date) < p_start_date then p_start_date
1479 else trunc(a.start_date)
1480 end transaction_create_date
1481 ,0 lead_id
1482 ,'OTHER' metric_type
1483 ,a.source_code_id source_code_id
1484 ,a.object_type object_type
1485 ,a.object_id object_id
1486 ,a.child_object_type child_object_type
1487 ,a.child_object_id child_object_id
1488 ,0 lead_rank_id
1489 ,a.object_country
1490 ,a.object_region
1491 ,a.child_object_country
1492 ,a.child_object_region
1493 ,a.category_id
1494 ,a.business_unit_id business_unit_id
1495 ,a.start_date
1496 ,a.end_date
1497 ,a.object_status object_status
1498 ,a.child_object_status child_object_status
1499 ,a.object_purpose object_purpose
1500 ,a.child_object_purpose child_object_purpose
1501 ,a.activity_type activity_type
1502 ,a.activity_id activity_id
1503 ,0 conversion_rate
1504 ,nvl(request_currency,'USD') from_currency
1505 ,0 leads
1506 ,0 opportunities
1507 ,0 opportunity_amt
1508 ,0 opportunities_open
1509 ,0 quotes
1510 ,0 quotes_open
1511 ,0 orders_booked
1512 ,0 orders_booked_amt
1513 ,0 budget_requested
1514 ,0-sum(nvl(s.approved_amount,0)) budget_approved
1515 ,0 revenue_forecasted
1516 ,0 revenue_actual
1517 ,0 cost_actual
1518 ,0 cost_forecasted
1519 ,0 responses_forecasted
1520 ,0 responses_positive
1521 ,0 customers_targeted
1522 ,0 customers_new
1523 ,0 registrations
1524 ,0 cancellations
1525 ,0 attendance
1526 ,0 OPPORTUNITY_AMT_S
1527 ,0 ORDERS_BOOKED_AMT_S
1528 ,0 REVENUE_FORECASTED_S
1529 ,0 REVENUE_ACTUAL_S
1530 ,0 COST_FORECASTED_S
1531 ,0 COST_ACTUAL_S
1532 ,0 BUDGET_REQUESTED_S
1533 ,0 BUDGET_APPROVED_S
1534 ,0 conversion_rate_s
1535 ,0 metric1
1536 ,0 metric2
1537 FROM ozf_act_budgets S
1538 ,bim_i_source_codes A
1539 ,ams_source_codes B
1540 WHERE s.arc_act_budget_used_by = 'FUND'
1541 AND s.parent_act_budget_id IS NULL
1542 AND s.budget_source_type = b.arc_source_code_for
1543 AND s.budget_source_id = b.source_code_for_id
1544 AND b.source_code_id = a.source_code_id
1545 AND a.object_type NOT IN ('RCAM')
1546 AND a.start_date <= p_end_date
1547 AND s.approval_date <= p_end_date
1548 GROUP BY
1549 case
1550 when trunc(a.start_date) < p_start_date then p_start_date
1551 else trunc(a.start_date)
1552 end
1553 ,a.source_code_id
1554 ,a.object_id
1555 ,a.object_type
1556 ,a.child_object_type
1557 ,a.child_object_id
1558 ,a.object_country
1559 ,a.child_object_country
1560 ,a.object_region
1561 ,a.child_object_region
1562 ,a.category_id
1563 ,a.object_status
1564 ,a.child_object_status
1565 ,a.object_purpose
1566 ,a.child_object_purpose
1567 ,a.activity_type
1568 ,a.activity_id
1569 ,a.business_unit_id
1570 ,a.start_date
1571 ,a.end_date
1572 ,fii_currency.get_global_rate_primary(nvl(request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
1573 ,fii_currency.get_global_rate_secondary(nvl(request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
1574 ,nvl(request_currency,'USD')
1575 union all
1576 --budget1 for Camp Schedules and Event Schedules
1577 SELECT /*+ USE_HASH(S A B) */
1578 trunc(nvl(s.approval_date,s.last_update_date)) transaction_create_date
1579 ,0 lead_id
1580 ,'OTHER' metric_type
1581 ,a.source_code_id source_code_id
1582 ,a.object_type object_type
1583 ,a.object_id object_id
1584 ,a.child_object_type child_object_type
1585 ,a.child_object_id child_object_id
1586 ,0 lead_rank_id
1587 ,a.object_country
1588 ,a.object_region
1589 ,a.child_object_country
1590 ,a.child_object_region
1591 ,a.category_id
1592 ,a.business_unit_id business_unit_id
1593 ,a.start_date
1594 ,a.end_date
1595 ,a.object_status object_status
1596 ,a.child_object_status child_object_status
1597 ,a.object_purpose object_purpose
1598 ,a.child_object_purpose child_object_purpose
1599 ,a.activity_type activity_type
1600 ,a.activity_id activity_id
1601 ,0 conversion_rate_s
1602 ,nvl(s.request_currency,'USD') from_currency
1603 ,0 leads
1604 ,0 opportunities
1605 ,0 opportunity_amt
1606 ,0 opportunities_open
1607 ,0 quotes
1608 ,0 quotes_open
1609 ,0 orders_booked
1610 ,0 orders_booked_amt
1611 ,0 budget_requested
1612 ,0 budget_approved
1613 ,0 revenue_forecasted
1614 ,0 revenue_actual
1615 ,0 cost_actual
1616 ,0 cost_forecasted
1617 ,0 responses_forecasted
1618 ,0 responses_positive
1619 ,0 customers_targeted
1620 ,0 customers_new
1621 ,0 registrations
1622 ,0 cancellations
1623 ,0 attendance
1624 ,0 OPPORTUNITY_AMT_S
1625 ,0 ORDERS_BOOKED_AMT_S
1626 ,0 REVENUE_FORECASTED_S
1627 ,0 REVENUE_ACTUAL_S
1628 ,0 COST_FORECASTED_S
1629 ,0 COST_ACTUAL_S
1630 ,0 BUDGET_REQUESTED_S
1631 ,0 BUDGET_APPROVED_S
1632 ,0 conversion_rate_s
1633 ,sum(nvl(s.approved_amount,0)) metric1
1634 ,0 metric2
1635 FROM ozf_act_budgets S
1636 ,bim_i_source_codes A
1637 ,ams_source_codes B
1638 WHERE s.act_budget_used_by_id = b.source_code_for_id
1639 AND s.arc_act_budget_used_by = b.arc_source_code_for
1640 AND b.source_code_id = a.source_code_id
1641 AND a.child_object_type IN ('CSCH','EVEO')
1642 AND s.budget_source_type = 'FUND'
1643 AND a.start_date >= p_start_date
1644 AND a.start_date <= p_end_date
1645 AND trunc(nvl(s.approval_date,s.last_update_date)) <= p_end_date
1646 AND s.status_code = 'APPROVED'
1647 GROUP BY
1648 trunc(nvl(s.approval_date,s.last_update_date))
1649 ,a.source_code_id
1650 ,a.object_id
1651 ,a.object_type
1652 ,a.child_object_type
1653 ,a.child_object_id
1654 ,a.object_country
1655 ,a.child_object_country
1656 ,a.object_region
1657 ,a.child_object_region
1658 ,a.category_id
1659 ,a.object_status
1660 ,a.child_object_status
1661 ,a.object_purpose
1662 ,a.child_object_purpose
1663 ,a.activity_type
1664 ,a.activity_id
1665 ,a.business_unit_id
1666 ,a.start_date
1667 ,a.end_date
1668 ,fii_currency.get_global_rate_primary(nvl(s.request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
1669 ,fii_currency.get_global_rate_secondary(nvl(s.request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
1670 ,nvl(s.request_currency,'USD')
1671 HAVING sum(nvl(s.approved_amount,0)) > 0
1672 union all
1673 --budget2 for Camp Schedules and Event Schedules
1674 SELECT /*+ USE_HASH(S A B) */
1675 trunc(a.start_date) transaction_create_date
1676 ,0 lead_id
1677 ,'OTHER' metric_type
1678 ,a.source_code_id source_code_id
1679 ,a.object_type object_type
1680 ,a.object_id object_id
1681 ,a.child_object_type child_object_type
1682 ,a.child_object_id child_object_id
1683 ,0 lead_rank_id
1684 ,a.object_country
1685 ,a.object_region
1686 ,a.child_object_country
1687 ,a.child_object_region
1688 ,a.category_id
1689 ,a.business_unit_id business_unit_id
1690 ,a.start_date
1691 ,a.end_date
1692 ,a.object_status object_status
1693 ,a.child_object_status child_object_status
1694 ,a.object_purpose object_purpose
1695 ,a.child_object_purpose child_object_purpose
1696 ,a.activity_type activity_type
1697 ,a.activity_id activity_id
1698 ,0 conversion_rate
1699 ,nvl(request_currency,'USD') from_currency
1700 ,0 leads
1701 ,0 opportunities
1702 ,0 opportunity_amt
1703 ,0 opportunities_open
1704 ,0 quotes
1705 ,0 quotes_open
1706 ,0 orders_booked
1707 ,0 orders_booked_amt
1708 ,0 budget_requested
1709 ,0 budget_approved
1710 ,0 revenue_forecasted
1711 ,0 revenue_actual
1712 ,0 cost_actual
1713 ,0 cost_forecasted
1714 ,0 responses_forecasted
1715 ,0 responses_positive
1716 ,0 customers_targeted
1717 ,0 customers_new
1718 ,0 registrations
1719 ,0 cancellations
1720 ,0 attendance
1721 ,0 OPPORTUNITY_AMT_S
1722 ,0 ORDERS_BOOKED_AMT_S
1723 ,0 REVENUE_FORECASTED_S
1724 ,0 REVENUE_ACTUAL_S
1725 ,0 COST_FORECASTED_S
1726 ,0 COST_ACTUAL_S
1727 ,0 BUDGET_REQUESTED_S
1728 ,0 BUDGET_APPROVED_S
1729 ,0 conversion_rate_s
1730 ,0-sum(nvl(s.approved_amount,0)) metric1
1731 ,0 metric2
1732 FROM ozf_act_budgets S
1733 ,bim_i_source_codes A
1734 ,ams_source_codes B
1735 WHERE s.arc_act_budget_used_by = 'FUND'
1736 AND s.budget_source_type = b.arc_source_code_for
1737 AND s.budget_source_id = b.source_code_for_id
1738 AND b.source_code_id = a.source_code_id
1739 AND a.child_object_type IN ('CSCH','EVEO')
1740 AND a.start_date >= p_start_date
1741 AND a.start_date <= p_end_date
1742 AND s.approval_date <= p_end_date
1743 GROUP BY
1744 trunc(nvl(s.approval_date,s.last_update_date))
1745 ,a.source_code_id
1746 ,a.object_id
1747 ,a.object_type
1748 ,a.child_object_type
1749 ,a.child_object_id
1750 ,a.object_country
1751 ,a.child_object_country
1752 ,a.object_region
1753 ,a.child_object_region
1754 ,a.category_id
1755 ,a.object_status
1756 ,a.child_object_status
1757 ,a.object_purpose
1758 ,a.child_object_purpose
1759 ,a.activity_type
1760 ,a.activity_id
1761 ,a.business_unit_id
1762 ,a.start_date
1763 ,a.end_date
1764 ,fii_currency.get_global_rate_primary(nvl(request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
1765 ,fii_currency.get_global_rate_secondary(nvl(request_currency,'USD'),nvl(s.approval_date,s.last_update_date))
1766 ,nvl(request_currency,'USD')
1767 union all --registration1
1768 SELECT /*+ USE_HASH(S A B) */
1769 trunc(X.last_reg_status_date) transaction_create_date
1770 ,0 lead_id
1771 ,'OTHER' metric_type
1772 ,a.source_code_id source_code_id
1773 ,a.object_type object_type
1774 ,a.object_id object_id
1775 ,a.child_object_type child_object_type
1776 ,a.child_object_id child_object_id
1777 ,0 lead_rank_id
1778 ,a.object_country
1779 ,a.object_region
1780 ,a.child_object_country
1781 ,a.child_object_region
1782 ,a.category_id
1783 ,a.business_unit_id business_unit_id
1784 ,a.start_date
1785 ,a.end_date
1786 ,a.object_status object_status
1787 ,a.child_object_status child_object_status
1788 ,a.object_purpose object_purpose
1789 ,a.child_object_purpose child_object_purpose
1790 ,a.activity_type activity_type
1791 ,a.activity_id activity_id
1792 ,0 conversion_rate
1793 ,null from_currency
1794 ,0 leads
1795 ,0 opportunities
1796 ,0 opportunity_amt
1797 ,0 opportunities_open
1798 ,0 quotes
1799 ,0 quotes_open
1800 ,0 orders_booked
1801 ,0 orders_booked_amt
1802 ,0 budget_requested
1803 ,0 budget_approved
1804 ,0 revenue_forecasted
1805 ,0 revenue_actual
1806 ,0 cost_actual
1807 ,0 cost_forecasted
1808 ,0 responses_forecasted
1809 ,0 responses_positive
1810 ,0 customers_targeted
1811 ,0 customers_new
1812 ,SUM(decode(X.system_status_code,'REGISTERED',1,0)) registrations
1813 ,SUM(decode(X.system_status_code,'CANCELLED',1,0)) cancellations
1814 ,SUM(decode(X.system_status_code,'REGISTERED',decode(attended_flag,'Y',1,0),0)) attendance
1815 ,0 OPPORTUNITY_AMT_S
1816 ,0 ORDERS_BOOKED_AMT_S
1817 ,0 REVENUE_FORECASTED_S
1818 ,0 REVENUE_ACTUAL_S
1819 ,0 COST_FORECASTED_S
1820 ,0 COST_ACTUAL_S
1821 ,0 BUDGET_REQUESTED_S
1822 ,0 BUDGET_APPROVED_S
1823 ,0 conversion_rate_S
1824 ,0 metric1
1825 ,0 metric2
1826 FROM ams_event_registrations X
1827 ,bim_i_source_codes A
1828 WHERE trunc(X.last_reg_status_date) between p_start_date and p_end_date+0.99999
1829 AND X.event_offer_id = A.child_object_id
1830 AND A.child_object_type ='EVEO'
1831 AND a.object_type NOT IN ('RCAM')
1832 AND a.start_date >= p_start_date
1833 AND a.start_date <= p_end_date
1834 GROUP BY
1835 trunc(X.last_reg_status_date)
1836 ,a.source_code_id
1837 ,a.object_id
1838 ,a.object_type
1839 ,a.child_object_type
1840 ,a.child_object_id
1841 ,a.object_country
1842 ,a.child_object_country
1843 ,a.object_region
1844 ,a.child_object_region
1845 ,a.category_id
1846 ,a.object_status
1847 ,a.child_object_status
1848 ,a.object_purpose
1849 ,a.child_object_purpose
1850 ,a.activity_type
1851 ,a.activity_id
1852 ,a.business_unit_id
1853 ,a.start_date
1854 ,a.end_date
1855 union all --registration2
1856 SELECT /*+ USE_HASH(S A B) */
1857 trunc(X.last_reg_status_date) transaction_create_date
1858 ,0 lead_id
1859 ,'OTHER' metric_type
1860 ,a.source_code_id source_code_id
1861 ,a.object_type object_type
1862 ,a.object_id object_id
1863 ,a.child_object_type child_object_type
1864 ,a.child_object_id child_object_id
1865 ,0 lead_rank_id
1866 ,a.object_country
1867 ,a.object_region
1868 ,a.child_object_country
1869 ,a.child_object_region
1870 ,a.category_id
1871 ,a.business_unit_id business_unit_id
1872 ,a.start_date
1873 ,a.end_date
1874 ,a.object_status object_status
1875 ,a.child_object_status child_object_status
1876 ,a.object_purpose object_purpose
1877 ,a.child_object_purpose child_object_purpose
1878 ,a.activity_type activity_type
1879 ,a.activity_id activity_id
1880 ,0 conversion_rate
1881 ,null from_currency
1882 ,0 leads
1883 ,0 opportunities
1884 ,0 opportunity_amt
1885 ,0 opportunities_open
1886 ,0 quotes
1887 ,0 quotes_open
1888 ,0 orders_booked
1889 ,0 orders_booked_amt
1890 ,0 budget_requested
1891 ,0 budget_approved
1892 ,0 revenue_forecasted
1893 ,0 revenue_actual
1894 ,0 cost_actual
1895 ,0 cost_forecasted
1896 ,0 responses_forecasted
1897 ,0 responses_positive
1898 ,0 customers_targeted
1899 ,0 customers_new
1900 ,SUM(decode(X.system_status_code,'REGISTERED',1,0)) registrations
1901 ,SUM(decode(X.system_status_code,'CANCELLED',1,0)) cancellations
1902 ,SUM(decode(X.system_status_code,'REGISTERED',decode(attended_flag,'Y',1,0),0)) attendance
1903 ,0 OPPORTUNITY_AMT_S
1904 ,0 ORDERS_BOOKED_AMT_S
1905 ,0 REVENUE_FORECASTED_S
1906 ,0 REVENUE_ACTUAL_S
1907 ,0 COST_FORECASTED_S
1908 ,0 COST_ACTUAL_S
1909 ,0 BUDGET_REQUESTED_S
1910 ,0 BUDGET_APPROVED_S
1911 ,0 conversion_rate_S
1912 ,0 metric1
1913 ,0 metric2
1914 FROM ams_event_registrations X
1915 ,bim_i_source_codes A
1916 WHERE trunc(X.last_reg_status_date) between p_start_date and p_end_date+0.99999
1917 AND X.event_offer_id = A.object_id
1918 AND A.object_type ='EONE'
1919 AND a.object_type NOT IN ('RCAM')
1920 AND a.start_date >= p_start_date
1921 AND a.start_date <= p_end_date
1922 GROUP BY
1923 trunc(X.last_reg_status_date)
1924 ,a.source_code_id
1925 ,a.object_id
1926 ,a.object_type
1927 ,a.child_object_type
1928 ,a.child_object_id
1929 ,a.object_country
1930 ,a.child_object_country
1931 ,a.object_region
1932 ,a.child_object_region
1933 ,a.category_id
1934 ,a.object_status
1935 ,a.child_object_status
1936 ,a.object_purpose
1937 ,a.child_object_purpose
1938 ,a.activity_type
1939 ,a.activity_id
1940 ,a.business_unit_id
1941 ,a.start_date
1942 ,a.end_date
1943 )
1944 GROUP BY transaction_create_date
1945 ,lead_id
1946 ,metric_type
1947 ,source_code_id
1948 ,object_type
1949 ,object_id
1950 ,child_object_type
1951 ,child_object_id
1952 ,lead_rank_id
1953 ,object_country
1954 ,object_region
1955 ,child_object_country
1956 ,child_object_region
1957 ,category_id
1958 ,business_unit_id
1959 ,start_date
1960 ,end_date
1961 ,object_status
1962 ,child_object_status
1963 ,object_purpose
1964 ,child_object_purpose
1965 ,activity_type
1966 ,activity_id
1967 ,conversion_rate
1968 ,from_currency
1969 ,conversion_rate_s) inner ;
1970
1971 commit;
1972
1973 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Second insert into bim_i_marketing_facts_stg');
1974 -------------------------NEW CODE --------------
1975 INSERT /*+ append parallel */
1976 INTO BIM_I_MARKETING_FACTS_STG CDF (
1977 --MKT_DAILY_TRANSACTION_ID ,
1978 CREATION_DATE ,
1979 LAST_UPDATE_DATE ,
1980 CREATED_BY ,
1981 LAST_UPDATED_BY ,
1982 LAST_UPDATE_LOGIN ,
1983 TRANSACTION_CREATE_DATE ,
1984 LEAD_ID ,
1985 METRIC_TYPE ,
1986 SOURCE_CODE_ID ,
1987 OBJECT_TYPE ,
1988 OBJECT_ID ,
1989 CHILD_OBJECT_TYPE ,
1990 CHILD_OBJECT_ID ,
1991 LEAD_RANK_ID ,
1992 OBJECT_COUNTRY ,
1993 OBJECT_REGION ,
1994 CHILD_OBJECT_COUNTRY ,
1995 CHILD_OBJECT_REGION ,
1996 CATEGORY_ID ,
1997 BUSINESS_UNIT_ID ,
1998 START_DATE ,
1999 END_DATE ,
2000 OBJECT_STATUS ,
2001 CHILD_OBJECT_STATUS ,
2002 OBJECT_PURPOSE ,
2003 CHILD_OBJECT_PURPOSE ,
2004 ACTIVITY_TYPE ,
2005 ACTIVITY_ID ,
2006 CONVERSION_RATE ,
2007 FROM_CURRENCY ,
2008 LEADS ,
2009 OPPORTUNITIES ,
2010 OPPORTUNITY_AMT ,
2011 OPPORTUNITIES_OPEN ,
2012 ORDERS_BOOKED ,
2013 ORDERS_BOOKED_AMT ,
2014 REVENUE_FORECASTED ,
2015 REVENUE_ACTUAL ,
2016 COST_FORECASTED ,
2017 COST_ACTUAL ,
2018 BUDGET_APPROVED ,
2019 BUDGET_REQUESTED ,
2020 RESPONSES_FORECASTED ,
2021 RESPONSES_POSITIVE ,
2022 CUSTOMERS_NEW ,
2023 REGISTRATIONS ,
2024 CANCELLATIONS ,
2025 ATTENDANCE ,
2026 OPPORTUNITY_AMT_S ,
2027 ORDERS_BOOKED_AMT_S ,
2028 REVENUE_FORECASTED_S ,
2029 REVENUE_ACTUAL_S ,
2030 COST_FORECASTED_S ,
2031 COST_ACTUAL_S ,
2032 BUDGET_REQUESTED_S ,
2033 BUDGET_APPROVED_S ,
2034 CONVERSION_RATE_S ,
2035 metric1 ,
2036 metric2)
2037 SELECT /*+ parallel */
2038 --BIM_I_MARKETING_FACTS_s.nextval ,
2039 sysdate
2040 ,sysdate
2041 ,-1
2042 ,-1
2043 ,-1
2044 ,transaction_create_date
2045 ,lead_id
2046 ,metric_type
2047 ,source_code_id
2048 ,object_type
2049 ,object_id
2050 ,child_object_type
2051 ,child_object_id
2052 ,lead_rank_id
2053 ,object_country
2054 ,object_region
2055 ,child_object_country
2056 ,child_object_region
2057 ,nvl(category_id,-1)
2058 ,business_unit_id
2059 ,start_date
2060 ,end_date
2061 ,object_status
2062 ,child_object_status
2063 ,object_purpose
2064 ,child_object_purpose
2065 ,activity_type
2066 ,activity_id
2067 ,conversion_rate
2068 ,from_currency
2069 ,leads
2070 ,opportunities
2071 ,opportunity_amt
2072 ,opportunities_open
2073 ,orders_booked
2074 ,orders_booked_amt
2075 ,revenue_forecasted
2076 ,revenue_actual
2077 ,cost_forecasted
2078 ,cost_actual
2079 ,budget_approved
2080 ,budget_requested
2081 ,responses_forecasted
2082 ,responses_positive
2083 ,customers_new
2084 ,registrations
2085 ,cancellations
2086 ,attendance
2087 ,OPPORTUNITY_AMT_S
2088 ,ORDERS_BOOKED_AMT_S
2089 ,REVENUE_FORECASTED_S
2090 ,REVENUE_ACTUAL_S
2091 ,COST_FORECASTED_S
2092 ,COST_ACTUAL_S
2093 ,BUDGET_REQUESTED_S
2094 ,BUDGET_APPROVED_S
2095 ,CONVERSION_RATE_S
2096 ,METRIC1
2097 ,METRIC2
2098 FROM (
2099 SELECT transaction_create_date transaction_create_date
2100 ,lead_id lead_id
2101 ,metric_type metric_type
2102 ,source_code_id source_code_id
2103 ,object_type object_type
2104 ,object_id object_id
2105 ,child_object_type child_object_type
2106 ,child_object_id child_object_id
2107 ,lead_rank_id lead_rank_id
2108 ,object_country object_country
2109 ,object_region object_region
2110 ,child_object_country child_object_country
2111 ,child_object_region child_object_region
2112 ,category_id category_id
2113 ,business_unit_id business_unit_id
2114 ,start_date start_date
2115 ,end_date end_date
2116 ,object_status object_status
2117 ,child_object_status child_object_status
2118 ,object_purpose object_purpose
2119 ,child_object_purpose child_object_purpose
2120 ,activity_type activity_type
2121 ,activity_id activity_id
2122 ,conversion_rate
2123 ,from_currency
2124 ,sum(leads) leads
2125 ,sum(opportunities) opportunities
2126 ,sum(opportunity_amt) opportunity_amt
2127 ,sum(opportunities_open) opportunities_open
2128 ,sum(orders_booked) orders_booked
2129 ,sum(orders_booked_amt) orders_booked_amt
2130 ,sum(budget_requested) budget_requested
2131 ,sum(budget_approved) budget_approved
2132 ,sum(revenue_forecasted) revenue_forecasted
2133 ,sum(revenue_actual) revenue_actual
2134 ,sum(cost_forecasted) cost_forecasted
2135 ,sum(cost_actual) cost_actual
2136 ,sum(responses_forecasted) responses_forecasted
2137 ,sum(responses_positive) responses_positive
2138 ,sum(customers_new) customers_new
2139 ,sum(registrations) registrations
2140 ,sum(cancellations) cancellations
2141 ,sum(attendance) attendance
2142 ,sum(OPPORTUNITY_AMT_S) OPPORTUNITY_AMT_S
2143 ,sum(ORDERS_BOOKED_AMT_S) ORDERS_BOOKED_AMT_S
2144 ,sum(REVENUE_FORECASTED_S) REVENUE_FORECASTED_S
2145 ,sum(REVENUE_ACTUAL_S ) REVENUE_ACTUAL_S
2146 ,sum(COST_FORECASTED_S ) COST_FORECASTED_S
2147 ,sum(COST_ACTUAL_S ) COST_ACTUAL_S
2148 ,sum(BUDGET_REQUESTED_S ) BUDGET_REQUESTED_S
2149 ,sum(BUDGET_APPROVED_S) BUDGET_APPROVED_S
2150 ,CONVERSION_RATE_S CONVERSION_RATE_S
2151 ,sum(metric1) metric1
2152 ,sum(metric2) metric2
2153 FROM (
2154 SELECT trunc(a.start_date) transaction_create_date
2155 ,0 lead_id
2156 ,'FREV' metric_type
2157 ,a.source_code_id source_code_id
2158 ,a.object_type object_type
2159 ,a.object_id object_id
2160 ,a.child_object_type child_object_type
2161 ,a.child_object_id child_object_id
2162 ,0 lead_rank_id
2163 ,a.object_country
2164 ,a.object_region
2165 ,a.child_object_country
2166 ,a.child_object_region
2167 ,a.category_id category_id
2168 ,a.business_unit_id business_unit_id
2169 ,a.start_date
2170 ,a.end_date
2171 ,a.object_status object_status
2172 ,a.child_object_status child_object_status
2173 ,a.object_purpose object_purpose
2174 ,a.child_object_purpose child_object_purpose
2175 ,a.activity_type activity_type
2176 ,a.activity_id activity_id
2177 ,0 conversion_rate
2178 ,nvl(f3.functional_currency_code,'USD') from_currency
2179 ,0 leads
2180 ,0 opportunities
2181 ,0 opportunity_amt
2182 ,0 opportunities_open
2183 ,0 quotes
2184 ,0 quotes_open
2185 ,0 orders_booked
2186 ,0 orders_booked_amt
2187 ,0 budget_requested
2188 ,0 budget_approved
2189 ,sum(nvl(f3.func_forecasted_delta,0)) revenue_forecasted
2190 ,0 revenue_actual
2191 ,0 cost_forecasted
2192 ,0 cost_actual
2193 ,0 responses_forecasted
2194 ,0 responses_positive
2195 ,0 customers_targeted
2196 ,0 customers_new
2197 ,0 registrations
2198 ,0 cancellations
2199 ,0 attendance
2200 ,0 OPPORTUNITY_AMT_S
2201 ,0 ORDERS_BOOKED_AMT_S
2202 ,0 REVENUE_FORECASTED_S
2203 ,0 REVENUE_ACTUAL_S
2204 ,0 COST_FORECASTED_S
2205 ,0 COST_ACTUAL_S
2206 ,0 BUDGET_REQUESTED_S
2207 ,0 BUDGET_APPROVED_S
2208 ,0 conversion_rate_s
2209 ,0 metric1
2210 ,0 metric2
2211 FROM ams_act_metric_hst f3
2212 ,ams_metrics_all_b g3
2213 ,bim_i_source_codes a
2214 WHERE f3.last_update_date between p_start_date and p_end_date
2215 AND f3.arc_act_metric_used_by = a.object_type
2216 AND f3.act_metric_used_by_id = a.object_id
2217 AND a.child_object_id =0
2218 AND a.object_type NOT IN ('RCAM')
2219 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
2220 AND g3.metric_category = 902
2221 --AND g3.metric_parent_id IS NULL
2222 AND g3.metric_id = f3.metric_id
2223 GROUP BY a.source_code_id
2224 ,a.object_type
2225 ,a.object_id
2226 ,a.child_object_type
2227 ,a.child_object_id
2228 ,a.object_country
2229 ,a.object_region
2230 ,a.child_object_country
2231 ,a.child_object_region
2232 ,a.category_id
2233 ,a.object_status
2234 ,a.child_object_status
2235 ,a.object_purpose
2236 ,a.child_object_purpose
2237 ,a.activity_type
2238 ,a.activity_id
2239 ,a.start_date
2240 ,a.end_date
2241 ,a.business_unit_id
2242 ,nvl(f3.functional_currency_code,'USD')
2243 HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
2244 UNION ALL
2245 SELECT trunc(a.start_date) transaction_create_date
2246 ,0 lead_id
2247 ,'FREV' metric_type
2248 ,a.source_code_id source_code_id
2249 ,a.object_type object_type
2250 ,a.object_id object_id
2251 ,a.child_object_type child_object_type
2252 ,a.child_object_id child_object_id
2253 ,0 lead_rank_id
2254 ,a.object_country
2255 ,a.object_region
2256 ,a.child_object_country
2257 ,a.child_object_region
2258 ,a.category_id category_id
2259 ,a.business_unit_id business_unit_id
2260 ,a.start_date
2261 ,a.end_date
2262 ,a.object_status object_status
2263 ,a.child_object_status child_object_status
2264 ,a.object_purpose object_purpose
2265 ,a.child_object_purpose child_object_purpose
2266 ,a.activity_type activity_type
2267 ,a.activity_id activity_id
2268 ,0 conversion_rate
2269 ,nvl(f3.functional_currency_code,'USD') from_currency
2270 ,0 leads
2271 ,0 opportunities
2272 ,0 opportunity_amt
2273 ,0 opportunities_open
2274 ,0 quotes
2275 ,0 quotes_open
2276 ,0 orders_booked
2277 ,0 orders_booked_amt
2278 ,0 budget_requested
2279 ,0 budget_approved
2280 ,sum(nvl(f3.func_forecasted_delta,0)) revenue_forecasted
2281 ,0 revenue_actual
2282 ,0 cost_forecasted
2283 ,0 cost_actual
2284 ,0 responses_forecasted
2285 ,0 responses_positive
2286 ,0 customers_targeted
2287 ,0 customers_new
2288 ,0 registrations
2289 ,0 cancellations
2290 ,0 attendance
2291 ,0 OPPORTUNITY_AMT_S
2292 ,0 ORDERS_BOOKED_AMT_S
2293 ,0 REVENUE_FORECASTED_S
2294 ,0 REVENUE_ACTUAL_S
2295 ,0 COST_FORECASTED_S
2296 ,0 COST_ACTUAL_S
2297 ,0 BUDGET_REQUESTED_S
2298 ,0 BUDGET_APPROVED_S
2299 ,0 conversion_rate_s
2300 ,0 metric1
2301 ,0 metric2
2302 FROM ams_act_metric_hst f3
2303 ,ams_metrics_all_b g3
2304 ,bim_i_source_codes a
2305 WHERE f3.last_update_date between p_start_date and p_end_date
2306 AND f3.arc_act_metric_used_by IN ('CSCH','EVEO')
2307 AND f3.act_metric_used_by_id = a.child_object_id
2308 AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
2309 -- AND a.child_object_id =0
2310 AND a.object_type NOT IN ('RCAM')
2311 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
2312 AND g3.metric_category = 902
2313 --AND g3.metric_parent_id IS NULL
2314 AND g3.metric_id = f3.metric_id
2315 GROUP BY a.source_code_id
2316 ,a.object_type
2317 ,a.object_id
2318 ,a.child_object_type
2319 ,a.child_object_id
2320 ,a.object_country
2321 ,a.object_region
2322 ,a.child_object_country
2323 ,a.child_object_region
2324 ,a.category_id
2325 ,a.object_status
2326 ,a.child_object_status
2327 ,a.object_purpose
2328 ,a.child_object_purpose
2329 ,a.activity_type
2330 ,a.activity_id
2331 ,a.start_date
2332 ,a.end_date
2333 ,a.business_unit_id
2334 ,nvl(f3.functional_currency_code,'USD')
2335 HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
2336 union all --cost and revenue
2337 SELECT trunc(a.start_date) transaction_create_date
2338 ,0 lead_id
2339 ,'FCOST' metric_type
2340 ,a.source_code_id source_code_id
2341 ,a.object_type object_type
2342 ,a.object_id object_id
2343 ,a.child_object_type child_object_type
2344 ,a.child_object_id child_object_id
2345 ,0 lead_rank_id
2346 ,a.object_country
2347 ,a.object_region
2348 ,a.child_object_country
2349 ,a.child_object_region
2350 ,a.category_id
2351 ,a.business_unit_id business_unit_id
2352 ,a.start_date
2353 ,a.end_date
2354 ,a.object_status object_status
2355 ,a.child_object_status child_object_status
2356 ,a.object_purpose object_purpose
2357 ,a.child_object_purpose child_object_purpose
2358 ,a.activity_type activity_type
2359 ,a.activity_id activity_id
2360 ,0 conversion_rate
2361 ,nvl(f3.functional_currency_code,'USD') from_currency
2362 ,0 leads
2363 ,0 opportunities
2364 ,0 opportunity_amt
2365 ,0 opportunities_open
2366 ,0 quotes
2367 ,0 quotes_open
2368 ,0 orders_booked
2369 ,0 orders_booked_amt
2370 ,0 budget_requested
2371 ,0 budget_approved
2372 ,0 revenue_forecasted
2373 ,0 revenue_actual
2374 ,sum(nvl(f3.func_forecasted_delta,0)) cost_forecasted
2375 ,0 cost_actual
2376 ,0 responses_forecasted
2377 ,0 responses_positive
2378 ,0 customers_targeted
2379 ,0 customers_new
2380 ,0 registrations
2381 ,0 cancellations
2382 ,0 attendance
2383 ,0 OPPORTUNITY_AMT_S
2384 ,0 ORDERS_BOOKED_AMT_S
2385 ,0 REVENUE_FORECASTED_S
2386 ,0 REVENUE_ACTUAL_S
2387 ,0 COST_FORECASTED_S
2388 ,0 COST_ACTUAL_S
2389 ,0 BUDGET_REQUESTED_S
2390 ,0 BUDGET_APPROVED_S
2391 ,0 conversion_rate_s
2392 ,0 metric1
2393 ,0 metric2
2394 FROM ams_act_metric_hst f3
2395 ,ams_metrics_all_b g3
2396 ,bim_i_source_codes a
2397 WHERE f3.last_update_date between p_start_date and p_end_date
2398 AND f3.arc_act_metric_used_by = a.object_type
2399 AND f3.act_metric_used_by_id = a.object_id
2400 AND a.child_object_id =0
2401 AND a.object_type NOT IN ('RCAM')
2402 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
2403 AND g3.metric_category = 901
2404 --AND g3.metric_parent_id IS NULL
2405 AND g3.metric_id = f3.metric_id
2406 GROUP BY a.source_code_id
2407 ,a.object_type
2408 ,a.object_id
2409 ,a.child_object_type
2410 ,a.child_object_id
2411 ,a.object_country
2412 ,a.object_region
2413 ,a.child_object_country
2414 ,a.child_object_region
2415 ,a.category_id
2416 ,a.object_status
2417 ,a.child_object_status
2418 ,a.object_purpose
2419 ,a.child_object_purpose
2420 ,a.activity_type
2421 ,a.activity_id
2422 ,a.start_date
2423 ,a.end_date
2424 ,a.business_unit_id
2425 ,nvl(f3.functional_currency_code,'USD')
2426 HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
2427 union all --cost and revenue at schedule level
2428 SELECT trunc(a.start_date) transaction_create_date
2429 ,0 lead_id
2430 ,'FCOST' metric_type
2431 ,a.source_code_id source_code_id
2432 ,a.object_type object_type
2433 ,a.object_id object_id
2434 ,a.child_object_type child_object_type
2435 ,a.child_object_id child_object_id
2436 ,0 lead_rank_id
2437 ,a.object_country
2438 ,a.object_region
2439 ,a.child_object_country
2440 ,a.child_object_region
2441 ,a.category_id
2442 ,a.business_unit_id business_unit_id
2443 ,a.start_date
2444 ,a.end_date
2445 ,a.object_status object_status
2446 ,a.child_object_status child_object_status
2447 ,a.object_purpose object_purpose
2448 ,a.child_object_purpose child_object_purpose
2449 ,a.activity_type activity_type
2450 ,a.activity_id activity_id
2451 ,0 conversion_rate
2452 ,nvl(f3.functional_currency_code,'USD') from_currency
2453 ,0 leads
2454 ,0 opportunities
2455 ,0 opportunity_amt
2456 ,0 opportunities_open
2457 ,0 quotes
2458 ,0 quotes_open
2459 ,0 orders_booked
2460 ,0 orders_booked_amt
2461 ,0 budget_requested
2462 ,0 budget_approved
2463 ,0 revenue_forecasted
2464 ,0 revenue_actual
2465 ,sum(nvl(f3.func_forecasted_delta,0)) cost_forecasted
2466 ,0 cost_actual
2467 ,0 responses_forecasted
2468 ,0 responses_positive
2469 ,0 customers_targeted
2470 ,0 customers_new
2471 ,0 registrations
2472 ,0 cancellations
2473 ,0 attendance
2474 ,0 OPPORTUNITY_AMT_S
2475 ,0 ORDERS_BOOKED_AMT_S
2476 ,0 REVENUE_FORECASTED_S
2477 ,0 REVENUE_ACTUAL_S
2478 ,0 COST_FORECASTED_S
2479 ,0 COST_ACTUAL_S
2480 ,0 BUDGET_REQUESTED_S
2481 ,0 BUDGET_APPROVED_S
2482 ,0 conversion_rate_s
2483 ,0 metric1
2484 ,0 metric2
2485 FROM ams_act_metric_hst f3
2486 ,ams_metrics_all_b g3
2487 ,bim_i_source_codes a
2488 WHERE f3.last_update_date between p_start_date and p_end_date
2489 AND f3.arc_act_metric_used_by In ('CSCH','EVEO')
2490 AND f3.act_metric_used_by_id = a.child_object_id
2491 AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
2492 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
2493 AND g3.metric_category = 901
2494 --AND g3.metric_parent_id IS NULL
2495 AND g3.metric_id = f3.metric_id
2496 AND a.object_type NOT IN ('RCAM')
2497 GROUP BY a.source_code_id
2498 ,a.object_type
2499 ,a.object_id
2500 ,a.child_object_type
2501 ,a.child_object_id
2502 ,a.object_country
2503 ,a.object_region
2504 ,a.child_object_country
2505 ,a.child_object_region
2506 ,a.category_id
2507 ,a.object_status
2508 ,a.child_object_status
2509 ,a.object_purpose
2510 ,a.child_object_purpose
2511 ,a.activity_type
2512 ,a.activity_id
2513 ,a.start_date
2514 ,a.end_date
2515 ,a.business_unit_id
2516 ,nvl(f3.functional_currency_code,'USD')
2517 HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
2518 )
2519 GROUP BY transaction_create_date
2520 ,lead_id
2521 ,metric_type
2522 ,source_code_id
2523 ,object_type
2524 ,object_id
2525 ,child_object_type
2526 ,child_object_id
2527 ,lead_rank_id
2528 ,object_country
2529 ,object_region
2530 ,child_object_country
2531 ,child_object_region
2532 ,category_id
2533 ,business_unit_id
2534 ,start_date
2535 ,end_date
2536 ,object_status
2537 ,child_object_status
2538 ,object_purpose
2539 ,child_object_purpose
2540 ,activity_type
2541 ,activity_id
2542 ,conversion_rate
2543 ,from_currency
2544 ,conversion_rate_s
2545 ) inner ;
2546 ---------------------END NEW CODE------------------------
2547 commit;
2548 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Third insert into bim_i_marketing_facts_stg');
2549 INSERT /*+ append parallel */
2550 INTO BIM_I_MARKETING_FACTS_STG CDF (
2551 --MKT_DAILY_TRANSACTION_ID ,
2552 CREATION_DATE ,
2553 LAST_UPDATE_DATE ,
2554 CREATED_BY ,
2555 LAST_UPDATED_BY ,
2556 LAST_UPDATE_LOGIN ,
2557 TRANSACTION_CREATE_DATE ,
2558 SOURCE_CODE_ID ,
2559 OBJECT_TYPE ,
2560 OBJECT_ID ,
2561 CHILD_OBJECT_TYPE ,
2562 CHILD_OBJECT_ID ,
2563 LEAD_RANK_ID ,
2564 OBJECT_COUNTRY ,
2565 OBJECT_REGION ,
2566 CHILD_OBJECT_COUNTRY ,
2567 CHILD_OBJECT_REGION ,
2568 CATEGORY_ID ,
2569 BUSINESS_UNIT_ID ,
2570 START_DATE ,
2571 END_DATE ,
2572 OBJECT_STATUS ,
2573 CHILD_OBJECT_STATUS ,
2574 OBJECT_PURPOSE ,
2575 CHILD_OBJECT_PURPOSE ,
2576 ACTIVITY_TYPE ,
2577 ACTIVITY_ID ,
2578 CONVERSION_RATE ,
2579 FROM_CURRENCY ,
2580 LEADS,
2581 OPPORTUNITIES ,
2582 OPPORTUNITY_AMT ,
2583 OPPORTUNITIES_OPEN ,
2584 ORDERS_BOOKED ,
2585 ORDERS_BOOKED_AMT ,
2586 REVENUE_FORECASTED ,
2587 REVENUE_ACTUAL ,
2588 COST_FORECASTED ,
2589 COST_ACTUAL ,
2590 BUDGET_APPROVED ,
2591 BUDGET_REQUESTED ,
2592 RESPONSES_FORECASTED ,
2593 RESPONSES_POSITIVE ,
2594 CUSTOMERS_NEW ,
2595 REGISTRATIONS ,
2596 CANCELLATIONS ,
2597 ATTENDANCE ,
2598 OPPORTUNITY_AMT_S ,
2599 ORDERS_BOOKED_AMT_S ,
2600 REVENUE_FORECASTED_S ,
2601 REVENUE_ACTUAL_S ,
2602 COST_FORECASTED_S ,
2603 COST_ACTUAL_S ,
2604 BUDGET_REQUESTED_S ,
2605 BUDGET_APPROVED_S ,
2606 CONVERSION_RATE_S ,
2607 METRIC1 ,
2608 METRIC2
2609 )
2610 SELECT /*+ parallel */
2611 sysdate
2612 ,sysdate
2613 ,-1
2614 ,-1
2615 ,-1
2616 ,trunc(s.start_date)
2617 ,s.source_code_id
2618 ,s.object_type
2619 ,s.object_id
2620 ,s.child_object_type
2621 ,s.child_object_id
2622 ,0
2623 ,s.object_country
2624 ,s.object_region
2625 ,s.child_object_country
2626 ,s.child_object_region
2627 ,nvl(s.category_id,-1)
2628 ,s.business_unit_id
2629 ,s.start_date
2630 ,s.end_date
2631 ,s.object_status
2632 ,s.child_object_status
2633 ,s.object_purpose
2634 ,s.child_object_purpose
2635 ,s.activity_type
2636 ,s.activity_id
2637 ,0
2638 ,null
2639 ,0
2640 ,0
2641 ,0
2642 ,0
2643 ,0
2644 ,0
2645 ,0
2646 ,0
2647 ,0
2648 ,0
2649 ,0
2650 ,0
2651 ,0
2652 ,0
2653 ,0
2654 ,0
2655 ,0
2656 ,0
2657 ,0
2658 ,0
2659 ,0
2660 ,0
2661 ,0
2662 ,0
2663 ,0
2664 ,0
2665 ,0
2666 ,0
2667 ,0
2668 FROM bim_i_source_codes s,
2669 bim_i_marketing_facts f
2670 where s.child_object_id = 0
2671 and f.child_object_id(+) = 0
2672 and s.object_id = f.object_id (+)
2673 and s.object_type = f.object_type (+)
2674 AND s.object_type NOT IN ('RCAM')
2675 and f.object_id IS NULL;
2676 commit;
2677
2678 bis_collection_utilities.log('Truncating Facts Table');
2679
2680 Execute Immediate 'Truncate Table '||l_schema||'.bim_i_marketing_facts';
2681 BIS_COLLECTION_UTILITIES.deleteLogForObject('MARKETING_FACTS');
2682
2683
2684 --insert schedules
2685 INSERT /*+ append parallel */
2686 INTO BIM_I_MARKETING_FACTS CDF (
2687 --MKT_DAILY_TRANSACTION_ID ,
2688 CREATION_DATE ,
2689 LAST_UPDATE_DATE ,
2690 CREATED_BY ,
2691 LAST_UPDATED_BY ,
2692 LAST_UPDATE_LOGIN ,
2693 TRANSACTION_CREATE_DATE ,
2694 SOURCE_CODE_ID ,
2695 OBJECT_TYPE ,
2696 OBJECT_ID ,
2697 CHILD_OBJECT_TYPE ,
2698 CHILD_OBJECT_ID ,
2699 LEAD_RANK_ID ,
2700 OBJECT_COUNTRY ,
2701 OBJECT_REGION ,
2702 CHILD_OBJECT_COUNTRY ,
2703 CHILD_OBJECT_REGION ,
2704 CATEGORY_ID ,
2705 BUSINESS_UNIT_ID ,
2706 START_DATE ,
2707 END_DATE ,
2708 OBJECT_STATUS ,
2709 CHILD_OBJECT_STATUS ,
2710 OBJECT_PURPOSE ,
2711 CHILD_OBJECT_PURPOSE ,
2712 ACTIVITY_TYPE ,
2713 ACTIVITY_ID ,
2714 CONVERSION_RATE ,
2715 FROM_CURRENCY ,
2716 LEADS,
2717 OPPORTUNITIES ,
2718 OPPORTUNITY_AMT ,
2719 OPPORTUNITIES_OPEN ,
2720 ORDERS_BOOKED ,
2721 ORDERS_BOOKED_AMT ,
2722 REVENUE_FORECASTED ,
2723 REVENUE_ACTUAL ,
2724 COST_FORECASTED ,
2725 COST_ACTUAL ,
2726 BUDGET_APPROVED ,
2727 BUDGET_REQUESTED ,
2728 RESPONSES_FORECASTED ,
2729 RESPONSES_POSITIVE ,
2730 CUSTOMERS_NEW ,
2731 REGISTRATIONS ,
2732 CANCELLATIONS ,
2733 ATTENDANCE ,
2734 OPPORTUNITY_AMT_S ,
2735 ORDERS_BOOKED_AMT_S ,
2736 REVENUE_FORECASTED_S ,
2737 REVENUE_ACTUAL_S ,
2738 COST_FORECASTED_S ,
2739 COST_ACTUAL_S ,
2740 BUDGET_REQUESTED_S ,
2741 BUDGET_APPROVED_S ,
2742 CONVERSION_RATE_S ,
2743 metric1 ,
2744 metric2
2745 )
2746 SELECT /*+ parallel */
2747 sysdate
2748 ,sysdate
2749 ,-1
2750 ,-1
2751 ,-1
2752 ,trunc(s.start_date)
2753 ,s.source_code_id
2754 ,s.object_type
2755 ,s.object_id
2756 ,s.child_object_type
2757 ,s.child_object_id
2758 ,0
2759 ,s.object_country
2760 ,s.object_region
2761 ,s.child_object_country
2762 ,s.child_object_region
2763 ,nvl(s.category_id,-1)
2764 ,s.business_unit_id
2765 ,s.start_date
2766 ,s.end_date
2767 ,s.object_status
2768 ,s.child_object_status
2769 ,s.object_purpose
2770 ,s.child_object_purpose
2771 ,s.activity_type
2772 ,s.activity_id
2773 ,0
2774 ,0
2775 ,0
2776 ,0
2777 ,0
2778 ,0
2779 ,0
2780 ,0
2781 ,0
2782 ,0
2783 ,0
2784 ,0
2785 ,0
2786 ,0
2787 ,0
2788 ,0
2789 ,0
2790 ,0
2791 ,0
2792 ,0
2793 ,0
2794 ,0
2795 ,0
2796 ,0
2797 ,0
2798 ,0
2799 ,0
2800 ,0
2801 ,null
2802 ,0
2803 ,0
2804 FROM bim_i_source_codes s,
2805 bim_i_marketing_facts f
2806 where s.child_object_id > 0
2807 and f.child_object_id(+) > 0
2808 and s.child_object_id = f.child_object_id (+)
2809 and s.child_object_type = f.child_object_type (+)
2810 and f.child_object_id is null;
2811 commit;
2812
2813 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Insert into bim_i_mkt_rates.');
2814 --Insert rates_temp table
2815 INSERT /*+ append parallel */
2816 INTO BIM_I_MKT_RATES MRT(tc_code,
2817 trx_date,
2818 prim_conversion_rate,
2819 sec_conversion_rate)
2820 SELECT from_currency,
2821 transaction_create_date,
2822 FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
2823 FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
2824 FROM (select distinct from_currency from_currency,
2825 transaction_create_date transaction_create_date
2826 from bim_i_marketing_facts_stg);
2827 commit;
2828 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:After Insert into bim_i_mkt_rates.');
2829 l_check_missing_rate := Check_Missing_Rates (p_start_date);
2830 if (l_check_missing_rate = -1) then
2831 BIS_COLLECTION_UTILITIES.debug('before truncating first time load' );
2832 l_stmt := 'TRUNCATE table '||L_SCHEMA||'.BIM_I_MARKETING_FACTS_STG';
2833 EXECUTE IMMEDIATE l_stmt;
2834 commit;
2835 x_return_status := FND_API.G_RET_STS_ERROR;
2836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2837 end if;
2838 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:After check missing rates');
2839 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Final Insert into bim_i_marketing_facts.');
2840 INSERT /*+ append parallel */
2841 INTO BIM_I_MARKETING_FACTS CDF (
2842 --MKT_DAILY_TRANSACTION_ID ,
2843 CREATION_DATE ,
2844 LAST_UPDATE_DATE ,
2845 CREATED_BY ,
2846 LAST_UPDATED_BY ,
2847 LAST_UPDATE_LOGIN ,
2848 TRANSACTION_CREATE_DATE ,
2849 LEAD_ID ,
2850 METRIC_TYPE ,
2851 SOURCE_CODE_ID ,
2852 OBJECT_TYPE ,
2853 OBJECT_ID ,
2854 CHILD_OBJECT_TYPE ,
2855 CHILD_OBJECT_ID ,
2856 LEAD_RANK_ID ,
2857 OBJECT_COUNTRY ,
2858 OBJECT_REGION ,
2859 CHILD_OBJECT_COUNTRY ,
2860 CHILD_OBJECT_REGION ,
2861 CATEGORY_ID ,
2862 BUSINESS_UNIT_ID ,
2863 START_DATE ,
2864 END_DATE ,
2865 OBJECT_STATUS ,
2866 CHILD_OBJECT_STATUS ,
2867 OBJECT_PURPOSE ,
2868 CHILD_OBJECT_PURPOSE ,
2869 ACTIVITY_TYPE ,
2870 ACTIVITY_ID ,
2871 CONVERSION_RATE ,
2872 FROM_CURRENCY ,
2873 LEADS ,
2874 OPPORTUNITIES ,
2875 OPPORTUNITY_AMT ,
2876 OPPORTUNITIES_OPEN ,
2877 ORDERS_BOOKED ,
2878 ORDERS_BOOKED_AMT ,
2879 REVENUE_FORECASTED ,
2880 REVENUE_ACTUAL ,
2881 COST_FORECASTED ,
2882 COST_ACTUAL ,
2883 BUDGET_APPROVED ,
2884 BUDGET_REQUESTED ,
2885 RESPONSES_FORECASTED ,
2886 RESPONSES_POSITIVE ,
2887 CUSTOMERS_TARGETED ,
2888 CUSTOMERS_NEW ,
2889 REGISTRATIONS ,
2890 CANCELLATIONS ,
2891 ATTENDANCE ,
2892 OPPORTUNITY_AMT_S ,
2893 ORDERS_BOOKED_AMT_S ,
2894 REVENUE_FORECASTED_S ,
2895 REVENUE_ACTUAL_S ,
2896 COST_FORECASTED_S ,
2897 COST_ACTUAL_S ,
2898 BUDGET_REQUESTED_S ,
2899 BUDGET_APPROVED_S ,
2900 CONVERSION_RATE_S ,
2901 metric1 ,
2902 metric2
2903 )
2904 SELECT /*+ parallel */
2905 -- BIM_I_MARKETING_FACTS_s.nextval ,
2906 sysdate
2907 ,sysdate
2908 ,-1
2909 ,-1
2910 ,-1
2911 ,transaction_create_date
2912 ,lead_id
2913 ,metric_type
2914 ,source_code_id
2915 ,object_type
2916 ,object_id
2917 ,child_object_type
2918 ,child_object_id
2919 ,lead_rank_id
2920 ,object_country
2921 ,object_region
2922 ,child_object_country
2923 ,child_object_region
2924 ,nvl(category_id,-1)
2925 ,business_unit_id
2926 ,start_date
2927 ,end_date
2928 ,object_status
2929 ,child_object_status
2930 ,object_purpose
2931 ,child_object_purpose
2932 ,activity_type
2933 ,activity_id
2934 ,conversion_rate
2935 ,from_currency
2936 ,leads
2937 ,opportunities
2938 ,opportunity_amt*rt.prim_conversion_rate
2939 ,opportunities_open
2940 ,orders_booked
2941 ,orders_booked_amt*rt.prim_conversion_rate
2942 ,revenue_forecasted*rt.prim_conversion_rate
2943 ,revenue_actual*rt.prim_conversion_rate
2944 ,cost_forecasted*rt.prim_conversion_rate
2945 ,cost_actual*rt.prim_conversion_rate
2946 ,budget_approved*rt.prim_conversion_rate
2947 ,budget_requested*rt.prim_conversion_rate
2948 ,responses_forecasted
2949 ,responses_positive
2950 ,customers_targeted
2951 ,customers_new
2952 ,registrations
2953 ,cancellations
2954 ,attendance
2955 ,OPPORTUNITY_AMT*sec_conversion_rate
2956 ,ORDERS_BOOKED_AMT*sec_conversion_rate
2957 ,REVENUE_FORECASTED*sec_conversion_rate
2958 ,REVENUE_ACTUAL*sec_conversion_rate
2959 ,COST_FORECASTED*sec_conversion_rate
2960 ,COST_ACTUAL*sec_conversion_rate
2961 ,BUDGET_REQUESTED*sec_conversion_rate
2962 ,BUDGET_APPROVED*sec_conversion_rate
2963 ,CONVERSION_RATE_S
2964 ,metric1*rt.prim_conversion_rate
2965 ,metric1*sec_conversion_rate
2966 FROM bim_i_marketing_facts_stg a, bim_i_mkt_rates rt
2967 where a.from_currency = rt.tc_code(+)
2968 and a.transaction_create_date= rt.trx_date(+);
2969 commit;
2970
2971 --BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Inserted '||SQL%COUNT);
2972 EXECUTE IMMEDIATE 'COMMIT';
2973 -- EXECUTE IMMEDIATE 'ALTER SEQUENCE bim_i_marketing_facts_s CACHE 20';
2974
2975 -- Analyze the daily facts table
2976 DBMS_STATS.gather_table_stats('BIM','BIM_I_MARKETING_FACTS', estimate_percent => 5,
2977 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
2978 --dbms_output.put_line('b4 put into history');
2979
2980 -- EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.MLOG$_BIM_I_MARKETING_FACT';
2981
2982 -- Make entry in the history table
2983 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: Wrapup');
2984 BEGIN
2985 IF (Not BIS_COLLECTION_UTILITIES.setup('MARKETING_FACTS')) THEN
2986 RAISE FND_API.G_EXC_ERROR;
2987 return;
2988 END IF;
2989
2990 BIS_COLLECTION_UTILITIES.WRAPUP(
2991 p_status =>TRUE ,
2992 p_period_from =>p_start_date,
2993 p_period_to => sysdate--p_end_date
2994 );
2995 Exception when others then
2996 Rollback;
2997 BIS_COLLECTION_UTILITIES.WRAPUP(
2998 p_status => FALSE,
2999 p_period_from =>p_start_date,
3000 p_period_to => sysdate--p_end_date
3001 );
3002 RAISE FND_API.G_EXC_ERROR;
3003 END;
3004
3005 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Before create index');
3006
3007 BIM_UTL_PKG.CREATE_INDEX('BIM_I_MARKETING_FACTS');
3008 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3009 --ams_utility_pvt.write_conc_log('BIM_I_MARKETING_FACTS:FIRST_LOAD: AFTER CREATE INDEX ' || l_temp_msg);
3010 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:After create index');
3011 /*fnd_message.set_name('BIM','BIM_R_PROG_COMPLETION');
3012 fnd_message.set_token('program_name', 'Marketing first load', FALSE);
3013 fnd_file.put_line(fnd_file.log,fnd_message.get);*/
3014 EXCEPTION
3015 WHEN FND_API.G_EXC_ERROR THEN
3016 x_return_status := FND_API.G_RET_STS_ERROR;
3017 -- Standard call to get message count and if count=1, get the message
3018 FND_msg_PUB.Count_And_Get (
3019 -- p_encoded => FND_API.G_FALSE,
3020 p_count => x_msg_count,
3021 p_data => x_msg_data
3022 );
3023
3024 ams_utility_pvt.write_conc_log('BIM_I_MARKETING_FACTS:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
3025 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3027 -- Standard call to get message count and if count=1, get the message
3028 FND_msg_PUB.Count_And_Get (
3029 --p_encoded => FND_API.G_FALSE,
3030 p_count => x_msg_count,
3031 p_data => x_msg_data
3032 );
3033 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Unexpected'||sqlerrm(sqlcode));
3034
3035 WHEN OTHERS THEN
3036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3037 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
3038 THEN
3039 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
3040 END IF;
3041
3042 -- Standard call to get message count and if count=1, get the message
3043 FND_msg_PUB.Count_And_Get (
3044 -- p_encoded => FND_API.G_FALSE,
3045 p_count => x_msg_count,
3046 p_data => x_msg_data
3047 );
3048 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:IN OTHERS EXCEPTION'||sqlerrm(sqlcode));
3049 --end;
3050 END FIRST_LOAD;
3051 --------------------------------------------------------------------------------------------------
3052 -- This procedure will execute when data is loaded for subsequent time.
3053
3054 -- PROCEDURE SUB_LOAD
3055 --------------------------------------------------------------------------------------------------
3056 PROCEDURE SUB_LOAD
3057 ( p_start_date IN DATE
3058 ,p_end_date IN DATE
3059 ,p_api_version_number IN NUMBER
3060 ,p_init_msg_list IN VARCHAR2
3061 ,p_load_type IN VARCHAR2
3062 ,x_msg_count OUT NOCOPY NUMBER
3063 ,x_msg_data OUT NOCOPY VARCHAR2
3064 ,x_return_status OUT NOCOPY VARCHAR2
3065 )
3066 IS
3067 l_user_id NUMBER := FND_GLOBAL.USER_ID();
3068 l_start_date DATE;
3069 l_end_date DATE;
3070 l_last_update_date DATE;
3071 l_conc_start_date DATE;
3072 l_conc_end_date DATE;
3073 l_sc_s_date DATE;
3074 l_sc_e_date DATE;
3075 l_success VARCHAR2(3);
3076 l_api_version_number CONSTANT NUMBER := 1.0;
3077 l_api_name CONSTANT VARCHAR2(30) := 'SUB_LOAD';
3078 l_table_name VARCHAR2(100);
3079 l_temp_msg VARCHAR2(100);
3080 l_check_missing_rate NUMBER;
3081 l_min_start_date DATE;
3082 l_min_date date;
3083 l_stmt VARCHAR2(100);
3084 l_status VARCHAR2(5);
3085 l_industry VARCHAR2(5);
3086 l_schema VARCHAR2(30);
3087 l_return BOOLEAN;
3088
3089 BEGIN
3090 --dbms_output.put_line('inside sub load:'||p_start_date || ' '|| p_end_date);
3091 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
3092 -- Standard call to check for call compatibility.
3093 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3094 p_api_version_number,
3095 l_api_name,
3096 g_pkg_name)
3097 THEN
3098 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3099 END IF;
3100
3101 -- Initialize message list if p_init_msg_list is set to TRUE.
3102 IF FND_API.to_Boolean( p_init_msg_list )
3103 THEN
3104 FND_msg_PUB.initialize;
3105 END IF;
3106
3107 -- Initialize API return status to SUCCESS
3108 x_return_status := FND_API.G_RET_STS_SUCCESS;
3109
3110 --dbms_output.put_line('inside sub load 2:');
3111
3112 -- The below four commands are necessary for the purpose of the parallel insertion */
3113 BEGIN
3114 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
3115 --EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE=100000000 ';
3116 --EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE=100000000 ';
3117 --EXECUTE IMMEDIATE 'ALTER TABLE BIM_I_MARKETING_FACTS nologging ';
3118 -- EXECUTE IMMEDIATE 'ALTER SEQUENCE BIM_I_MARKETING_FACTS_s CACHE 1000 ';
3119 EXCEPTION
3120 when others then
3121 --dbms_output.put_line('inside sub load:'||sqlerrm(sqlcode));
3122 l_temp_msg := to_char( sysdate, 'dd/mm/yyyy:hh:mi:ss');
3123 END;
3124 --dbms_output.put_line('right b4 inserting');
3125 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:SUBSEQUENT_LOAD start');
3126 --ams_utility_pvt.write_conc_log('BIM_I_MARKETING_FACTS:SUBSEQUENT_LOAD: BEFORE FIRST INSERT ' || l_temp_msg);
3127 BEGIN
3128 DELETE from bim_i_marketing_facts where transaction_create_date>= p_start_date and metric_type is not null;
3129 COMMIT;
3130 EXCEPTION
3131 when others then
3132 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS: Error in first insert:'||sqlerrm(sqlcode));
3133 --dbms_output.put_line('error inserting:'||sqlerrm(sqlcode));
3134 END;
3135 EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_MARKETING_FACTS_STG';
3136 EXECUTE IMMEDIATE 'TRUNCATE table '||l_schema||'.BIM_I_MKT_RATES';
3137
3138 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:First insert BIM_I_MARKETING_FACTS_STG');
3139 --BEGIN
3140 --dbms_output.put_line('right b4 inserting'||sqlerrm(sqlcode));
3141 INSERT
3142 INTO BIM_I_MARKETING_FACTS_STG CDF
3143 ( -- MKT_DAILY_TRANSACTION_ID ,
3144 CREATION_DATE ,
3145 LAST_UPDATE_DATE ,
3146 CREATED_BY ,
3147 LAST_UPDATED_BY ,
3148 LAST_UPDATE_LOGIN ,
3149 LEAD_ID ,
3150 METRIC_TYPE ,
3151 TRANSACTION_CREATE_DATE ,
3152 SOURCE_CODE_ID ,
3153 OBJECT_TYPE ,
3154 OBJECT_ID ,
3155 CHILD_OBJECT_TYPE ,
3156 CHILD_OBJECT_ID ,
3157 LEAD_RANK_ID ,
3158 OBJECT_COUNTRY ,
3159 OBJECT_REGION ,
3160 CHILD_OBJECT_COUNTRY ,
3161 CHILD_OBJECT_REGION ,
3162 CATEGORY_ID ,
3163 BUSINESS_UNIT_ID ,
3164 START_DATE ,
3165 END_DATE ,
3166 OBJECT_STATUS ,
3167 CHILD_OBJECT_STATUS ,
3168 OBJECT_PURPOSE ,
3169 CHILD_OBJECT_PURPOSE ,
3170 ACTIVITY_TYPE ,
3171 ACTIVITY_ID ,
3172 CONVERSION_RATE ,
3173 FROM_CURRENCY ,
3174 LEADS,
3175 OPPORTUNITIES ,
3176 OPPORTUNITY_AMT ,
3177 OPPORTUNITIES_OPEN ,
3178 ORDERS_BOOKED ,
3179 ORDERS_BOOKED_AMT ,
3180 REVENUE_FORECASTED ,
3181 REVENUE_ACTUAL ,
3182 COST_FORECASTED ,
3183 COST_ACTUAL ,
3184 BUDGET_APPROVED ,
3185 BUDGET_REQUESTED ,
3186 RESPONSES_FORECASTED ,
3187 RESPONSES_POSITIVE ,
3188 CUSTOMERS_TARGETED ,
3189 CUSTOMERS_NEW ,
3190 REGISTRATIONS ,
3191 CANCELLATIONS ,
3192 ATTENDANCE ,
3193 OPPORTUNITY_AMT_S ,
3194 ORDERS_BOOKED_AMT_S ,
3195 REVENUE_FORECASTED_S ,
3196 REVENUE_ACTUAL_S ,
3197 COST_FORECASTED_S ,
3198 COST_ACTUAL_S ,
3199 BUDGET_REQUESTED_S ,
3200 BUDGET_APPROVED_S ,
3201 CONVERSION_RATE_S ,
3202 metric1 ,
3203 metric2
3204 )
3205 SELECT
3206 --BIM_I_MARKETING_FACTS_s.nextval,
3207 sysdate
3208 ,sysdate
3209 ,-1
3210 ,-1
3211 ,-1
3212 ,0
3213 ,'OTHER'
3214 ,transaction_create_date
3215 ,source_code_id
3216 ,object_type
3217 ,object_id
3218 ,child_object_type
3219 ,child_object_id
3220 ,lead_rank_id
3221 ,object_country
3222 ,object_region
3223 ,child_object_country
3224 ,child_object_region
3225 ,nvl(category_id,-1)
3226 ,business_unit_id
3227 ,start_date
3228 ,end_date
3229 ,object_status
3230 ,child_object_status
3231 ,object_purpose
3232 ,child_object_purpose
3233 ,activity_type
3234 ,activity_id
3235 ,conversion_rate
3236 ,from_currency
3237 ,leads
3238 ,opportunities
3239 ,opportunity_amt
3240 ,opportunities_open
3241 ,orders_booked
3242 ,orders_booked_amt
3243 ,revenue_forecasted
3244 ,revenue_actual
3245 ,cost_forecasted
3246 ,cost_actual
3247 ,budget_approved
3248 ,budget_requested
3249 ,responses_forecasted
3250 ,responses_positive
3251 ,customers_targeted
3252 ,customers_new
3253 ,registrations
3254 ,cancellations
3255 ,attendance
3256 ,OPPORTUNITY_AMT_S
3257 ,ORDERS_BOOKED_AMT_S
3258 ,REVENUE_FORECASTED_S
3259 ,REVENUE_ACTUAL_S
3260 ,COST_FORECASTED_S
3261 ,COST_ACTUAL_S
3262 ,BUDGET_REQUESTED_S
3263 ,BUDGET_APPROVED_S
3264 ,CONVERSION_RATE_S
3265 ,metric1
3266 ,metric2
3267 FROM (
3268 SELECT transaction_create_date transaction_create_date
3269 ,source_code_id source_code_id
3270 ,object_type object_type
3271 ,object_id object_id
3272 ,child_object_type child_object_type
3273 ,child_object_id child_object_id
3274 ,lead_rank_id lead_rank_id
3275 ,object_country object_country
3276 ,object_region object_region
3277 ,child_object_country child_object_country
3278 ,child_object_region child_object_region
3279 ,category_id category_id
3280 ,business_unit_id business_unit_id
3281 ,start_date start_date
3282 ,end_date end_date
3283 ,object_status object_status
3284 ,child_object_status child_object_status
3285 ,object_purpose object_purpose
3286 ,child_object_purpose child_object_purpose
3287 ,activity_type activity_type
3288 ,activity_id activity_id
3289 ,conversion_rate
3290 ,from_currency
3291 ,sum(leads) leads
3292 ,sum(opportunities) opportunities
3293 ,sum(opportunity_amt) opportunity_amt
3294 ,sum(opportunities_open) opportunities_open
3295 ,sum(orders_booked) orders_booked
3296 ,sum(orders_booked_amt) orders_booked_amt
3297 ,sum(budget_requested) budget_requested
3298 ,sum(budget_approved) budget_approved
3299 ,sum(revenue_forecasted) revenue_forecasted
3300 ,sum(revenue_actual) revenue_actual
3301 ,sum(cost_forecasted) cost_forecasted
3302 ,sum(cost_actual) cost_actual
3303 ,sum(responses_forecasted) responses_forecasted
3304 ,sum(responses_positive) responses_positive
3305 ,sum(customers_targeted) customers_targeted
3306 ,sum(customers_new) customers_new
3307 ,sum(registrations) registrations
3308 ,sum(cancellations) cancellations
3309 ,sum(attendance) attendance
3310 ,sum(OPPORTUNITY_AMT_S) OPPORTUNITY_AMT_S
3311 ,sum(ORDERS_BOOKED_AMT_S) ORDERS_BOOKED_AMT_S
3312 ,sum(REVENUE_FORECASTED_S) REVENUE_FORECASTED_S
3313 ,sum(REVENUE_ACTUAL_S ) REVENUE_ACTUAL_S
3314 ,sum(COST_FORECASTED_S ) COST_FORECASTED_S
3315 ,sum(COST_ACTUAL_S ) COST_ACTUAL_S
3316 ,sum(BUDGET_REQUESTED_S ) BUDGET_REQUESTED_S
3317 ,sum(BUDGET_APPROVED_S) BUDGET_APPROVED_S
3318 ,CONVERSION_RATE_S CONVERSION_RATE_S
3319 ,sum(metric1) metric1
3320 ,sum(metric2) metric2
3321 FROM (
3322 --cost and revenue
3323 SELECT /*+ USE_NL(F3 A G3) ordered */ trunc(f3.last_update_date) transaction_create_date
3324 ,a.source_code_id source_code_id
3325 ,a.object_type object_type
3326 ,a.object_id object_id
3327 ,a.child_object_type child_object_type
3328 ,a.child_object_id child_object_id
3329 ,0 lead_rank_id
3330 ,a.object_country
3331 ,a.object_region
3332 ,a.child_object_country
3333 ,a.child_object_region
3334 ,a.category_id
3335 ,a.business_unit_id business_unit_id
3336 ,a.start_date
3337 ,a.end_date
3338 ,a.object_status object_status
3339 ,a.child_object_status child_object_status
3340 ,a.object_purpose object_purpose
3341 ,a.child_object_purpose child_object_purpose
3342 ,a.activity_type activity_type
3343 ,a.activity_id activity_id
3344 ,0 conversion_rate
3345 ,nvl(f3.functional_currency_code,'USD') from_currency
3346 ,0 leads
3347 ,0 opportunities
3348 ,0 opportunity_amt
3349 ,0 opportunities_open
3350 ,0 quotes
3351 ,0 quotes_open
3352 ,0 orders_booked
3353 ,0 orders_booked_amt
3354 ,0 budget_requested
3355 ,0 budget_approved
3356 ,0 revenue_forecasted
3357 ,sum(nvl(f3.func_actual_delta,0)) REVENUE_ACTUAL
3358 ,0 cost_forecasted
3359 ,0 cost_actual
3360 ,0 responses_forecasted
3361 ,0 responses_positive
3362 ,0 customers_targeted
3363 ,0 customers_new
3364 ,0 registrations
3365 ,0 cancellations
3366 ,0 attendance
3367 ,0 OPPORTUNITY_AMT_S
3368 ,0 ORDERS_BOOKED_AMT_S
3369 ,0 REVENUE_FORECASTED_S
3370 ,0 REVENUE_ACTUAL_S
3371 ,0 COST_FORECASTED_S
3372 ,0 COST_ACTUAL_S
3373 ,0 BUDGET_REQUESTED_S
3374 ,0 BUDGET_APPROVED_S
3375 ,0 conversion_rate_s
3376 ,0 metric1
3377 ,0 metric2
3378 FROM
3379 ams_act_metric_hst f3
3380 ,ams_metrics_all_b g3
3381 ,bim_i_source_codes a
3382 WHERE f3.last_update_date between p_start_date and p_end_date
3383 AND f3.arc_act_metric_used_by = a.object_type
3384 AND f3.act_metric_used_by_id = a.object_id
3385 AND a.child_object_id =0
3386 AND a.object_type NOT IN ('RCAM')
3387 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
3388 AND g3.metric_category = 902
3389 --AND g3.metric_parent_id IS NULL
3390 AND g3.metric_id = f3.metric_id
3391 GROUP BY trunc(f3.last_update_date)
3392 ,a.source_code_id
3393 ,a.object_type
3394 ,a.object_id
3395 ,a.child_object_type
3396 ,a.child_object_id
3397 ,a.object_country
3398 ,a.object_region
3399 ,a.child_object_country
3400 ,a.child_object_region
3401 ,a.category_id
3402 ,a.object_status
3403 ,a.child_object_status
3404 ,a.object_purpose
3405 ,a.child_object_purpose
3406 ,a.activity_type
3407 ,a.activity_id
3408 ,a.start_date
3409 ,a.end_date
3410 ,a.business_unit_id
3411 ,nvl(f3.functional_currency_code,'USD')
3412 HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
3413 union all --cost and revenue
3414 SELECT /*+ USE_NL(F3 A G3) ordered */ trunc(f3.last_update_date) transaction_create_date
3415 ,a.source_code_id source_code_id
3416 ,a.object_type object_type
3417 ,a.object_id object_id
3418 ,a.child_object_type child_object_type
3419 ,a.child_object_id child_object_id
3420 ,0 lead_rank_id
3421 ,a.object_country
3422 ,a.object_region
3423 ,a.child_object_country
3424 ,a.child_object_region
3425 ,a.category_id
3426 ,a.business_unit_id business_unit_id
3427 ,a.start_date
3428 ,a.end_date
3429 ,a.object_status object_status
3430 ,a.child_object_status child_object_status
3431 ,a.object_purpose object_purpose
3432 ,a.child_object_purpose child_object_purpose
3433 ,a.activity_type activity_type
3434 ,a.activity_id activity_id
3435 ,0 conversion_rate
3436 ,nvl(f3.functional_currency_code,'USD') from_currency
3437 ,0 leads
3438 ,0 opportunities
3439 ,0 opportunity_amt
3440 ,0 opportunities_open
3441 ,0 quotes
3442 ,0 quotes_open
3443 ,0 orders_booked
3444 ,0 orders_booked_amt
3445 ,0 budget_requested
3446 ,0 budget_approved
3447 ,0 revenue_forecasted
3448 ,sum(nvl(f3.func_actual_delta,0)) REVENUE_ACTUAL
3449 ,0 cost_forecasted
3450 ,0 cost_actual
3451 ,0 responses_forecasted
3452 ,0 responses_positive
3453 ,0 customers_targeted
3454 ,0 customers_new
3455 ,0 registrations
3456 ,0 cancellations
3457 ,0 attendance
3458 ,0 OPPORTUNITY_AMT_S
3459 ,0 ORDERS_BOOKED_AMT_S
3460 ,0 REVENUE_FORECASTED_S
3461 ,0 REVENUE_ACTUAL_S
3462 ,0 COST_FORECASTED_S
3463 ,0 COST_ACTUAL_S
3464 ,0 BUDGET_REQUESTED_S
3465 ,0 BUDGET_APPROVED_S
3466 ,0 conversion_rate_s
3467 ,0 metric1
3468 ,0 metric2
3469 FROM
3470 ams_act_metric_hst f3
3471 ,ams_metrics_all_b g3
3472 ,bim_i_source_codes a
3473 WHERE f3.last_update_date between p_start_date and p_end_date
3474 AND f3.arc_act_metric_used_by in ('CSCH','EVEO')
3475 AND f3.act_metric_used_by_id = a.child_object_id
3476 AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
3477 AND a.object_type NOT IN ('RCAM')
3478 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
3479 AND g3.metric_category = 902
3480 --AND g3.metric_parent_id IS NULL
3481 AND g3.metric_id = f3.metric_id
3482 GROUP BY trunc(f3.last_update_date)
3483 ,a.source_code_id
3484 ,a.object_type
3485 ,a.object_id
3486 ,a.child_object_type
3487 ,a.child_object_id
3488 ,a.object_country
3489 ,a.object_region
3490 ,a.child_object_country
3491 ,a.child_object_region
3492 ,a.category_id
3493 ,a.object_status
3494 ,a.child_object_status
3495 ,a.object_purpose
3496 ,a.child_object_purpose
3497 ,a.activity_type
3498 ,a.activity_id
3499 ,a.start_date
3500 ,a.end_date
3501 ,a.business_unit_id
3502 ,nvl(f3.functional_currency_code,'USD')
3503 HAVING sum(nvl(f3.func_actual_delta,0)) <> 0
3504 UNION ALL
3505 SELECT /*+ USE_NL(F1 G1 A) ordered */
3506 trunc(f1.last_update_date) creation_date
3507 ,a.source_code_id source_code_id
3508 ,a.object_type object_type
3509 ,a.object_id object_id
3510 ,a.child_object_type child_object_type
3511 ,a.child_object_id child_object_id
3512 ,0 lead_rank_id
3513 ,a.object_country
3514 ,a.object_region
3515 ,a.child_object_country
3516 ,a.child_object_region
3517 ,a.category_id
3518 ,a.business_unit_id business_unit_id
3519 ,a.start_date
3520 ,a.end_date
3521 ,a.object_status object_status
3522 ,a.child_object_status child_object_status
3523 ,a.object_purpose object_purpose
3524 ,a.child_object_purpose child_object_purpose
3525 ,a.activity_type activity_type
3526 ,a.activity_id activity_id
3527 ,0 conversion_rate
3528 ,nvl(f1.functional_currency_code,'USD') from_currency
3529 ,0 leads
3530 ,0 opportunities
3531 ,0 opportunity_amt
3532 ,0 opportunities_open
3533 ,0 quotes
3534 ,0 quotes_open
3535 ,0 orders_booked
3536 ,0 orders_booked_amt
3537 ,0 budget_requested
3538 ,0 budget_approved
3539 ,0 revenue_forecasted
3540 ,0 revenue_actual
3541 ,0 cost_forecasted
3542 ,sum(nvl(f1.func_actual_delta,0)) cost_actual
3543 ,0 responses_forecasted
3544 ,0 responses_positive
3545 ,0 customers_targeted
3546 ,0 customers_new
3547 ,0 registrations
3548 ,0 cancellations
3549 ,0 attendance
3550 ,0 OPPORTUNITY_AMT_S
3551 ,0 ORDERS_BOOKED_AMT_S
3552 ,0 REVENUE_FORECASTED_S
3553 ,0 REVENUE_ACTUAL_S
3554 ,0 COST_FORECASTED_S
3555 ,0 COST_ACTUAL_S
3556 ,0 BUDGET_REQUESTED_S
3557 ,0 BUDGET_APPROVED_S
3558 ,0 CONVERSION_RATE_S
3559 ,0 metric1
3560 ,0 metric2
3561 FROM bim_i_source_codes a
3562 ,ams_act_metric_hst f1
3563 ,ams_metrics_all_b g1
3564 WHERE f1.last_update_date between p_start_date and p_end_date
3565 AND f1.arc_act_metric_used_by = a.object_type
3566 AND f1.act_metric_used_by_id =a.object_id
3567 AND a.child_object_id =0
3568 AND a.object_type NOT IN ('RCAM')
3569 AND g1.metric_category = 901
3570 AND g1.metric_id = f1.metric_id
3571 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
3572 GROUP BY trunc(f1.last_update_date)
3573 ,a.source_code_id
3574 ,a.object_type
3575 ,a.object_id
3576 ,a.child_object_type
3577 ,a.child_object_id
3578 ,a.object_country
3579 ,a.object_region
3580 ,a.child_object_country
3581 ,a.child_object_region
3582 ,a.category_id
3583 ,a.object_status
3584 ,a.child_object_status
3585 ,a.object_purpose
3586 ,a.child_object_purpose
3587 ,a.activity_type
3588 ,a.activity_id
3589 ,a.start_date
3590 ,a.end_date
3591 ,a.business_unit_id
3592 ,nvl(f1.functional_currency_code,'USD')
3593 HAVING sum(nvl(f1.func_actual_delta,0)) <> 0
3594 UNION ALL
3595 SELECT /*+ USE_NL(F1 G1 A) ordered */
3596 trunc(f1.last_update_date) creation_date
3597 ,a.source_code_id source_code_id
3598 ,a.object_type object_type
3599 ,a.object_id object_id
3600 ,a.child_object_type child_object_type
3601 ,a.child_object_id child_object_id
3602 ,0 lead_rank_id
3603 ,a.object_country
3604 ,a.object_region
3605 ,a.child_object_country
3606 ,a.child_object_region
3607 ,a.category_id
3608 ,a.business_unit_id business_unit_id
3609 ,a.start_date
3610 ,a.end_date
3611 ,a.object_status object_status
3612 ,a.child_object_status child_object_status
3613 ,a.object_purpose object_purpose
3614 ,a.child_object_purpose child_object_purpose
3615 ,a.activity_type activity_type
3616 ,a.activity_id activity_id
3617 ,0 conversion_rate
3618 ,nvl(f1.functional_currency_code,'USD') from_currency
3619 ,0 leads
3620 ,0 opportunities
3621 ,0 opportunity_amt
3622 ,0 opportunities_open
3623 ,0 quotes
3624 ,0 quotes_open
3625 ,0 orders_booked
3626 ,0 orders_booked_amt
3627 ,0 budget_requested
3628 ,0 budget_approved
3629 ,0 revenue_forecasted
3630 ,0 revenue_actual
3631 ,0 cost_forecasted
3632 ,sum(nvl(f1.func_actual_delta,0)) cost_actual
3633 ,0 responses_forecasted
3634 ,0 responses_positive
3635 ,0 customers_new
3636 ,0 customers_targeted
3637 ,0 registrations
3638 ,0 cancellations
3639 ,0 attendance
3640 ,0 OPPORTUNITY_AMT_S
3641 ,0 ORDERS_BOOKED_AMT_S
3642 ,0 REVENUE_FORECASTED_S
3643 ,0 REVENUE_ACTUAL_S
3644 ,0 COST_FORECASTED_S
3645 ,0 COST_ACTUAL_S
3646 ,0 BUDGET_REQUESTED_S
3647 ,0 BUDGET_APPROVED_S
3648 ,0 CONVERSION_RATE_S
3649 ,0 metric1
3650 ,0 metric2
3651 FROM bim_i_source_codes a
3652 ,ams_act_metric_hst f1
3653 ,ams_metrics_all_b g1
3654 WHERE f1.last_update_date between p_start_date and p_end_date
3655 AND f1.arc_act_metric_used_by in ('CSCH','EVEO')
3656 AND f1.act_metric_used_by_id =a.child_object_id
3657 AND f1.ARC_ACT_METRIC_USED_BY = a.child_object_type
3658 AND a.object_type NOT IN ('RCAM')
3659 AND g1.metric_category = 901
3660 AND g1.metric_id = f1.metric_id
3661 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
3662 GROUP BY trunc(f1.last_update_date)
3663 ,a.source_code_id
3664 ,a.object_type
3665 ,a.object_id
3666 ,a.child_object_type
3667 ,a.child_object_id
3668 ,a.object_country
3669 ,a.object_region
3670 ,a.child_object_country
3671 ,a.child_object_region
3672 ,a.category_id
3673 ,a.object_status
3674 ,a.child_object_status
3675 ,a.object_purpose
3676 ,a.child_object_purpose
3677 ,a.activity_type
3678 ,a.activity_id
3679 ,a.start_date
3680 ,a.end_date
3681 ,a.business_unit_id
3682 ,nvl(f1.functional_currency_code,'USD')
3683 HAVING sum(nvl(f1.func_actual_delta,0)) <> 0
3684 --sbehera 15 jan 2004
3685 --for campaign forecasted response
3686 union all --forecasted response
3687 SELECT trunc(f3.last_update_date) transaction_create_date
3688 ,a.source_code_id source_code_id
3689 ,a.object_type object_type
3690 ,a.object_id object_id
3691 ,a.child_object_type child_object_type
3692 ,a.child_object_id child_object_id
3693 ,0 lead_rank_id
3694 ,a.object_country
3695 ,a.object_region
3696 ,a.child_object_country
3697 ,a.child_object_region
3698 ,a.category_id
3699 ,a.business_unit_id business_unit_id
3700 ,a.start_date
3701 ,a.end_date
3702 ,a.object_status object_status
3703 ,a.child_object_status child_object_status
3704 ,a.object_purpose object_purpose
3705 ,a.child_object_purpose child_object_purpose
3706 ,a.activity_type activity_type
3707 ,a.activity_id activity_id
3708 ,0 conversion_rate
3709 ,null from_currency
3710 ,0 leads
3711 ,0 opportunities
3712 ,0 opportunity_amt
3713 ,0 opportunities_open
3714 ,0 quotes
3715 ,0 quotes_open
3716 ,0 orders_booked
3717 ,0 orders_booked_amt
3718 ,0 budget_requested
3719 ,0 budget_approved
3720 ,0 revenue_forecasted
3721 ,0 revenue_actual
3722 ,0 cost_forecasted
3723 ,0 cost_actual
3724 ,sum(nvl(f3.func_forecasted_delta,0)) responses_forecasted
3725 ,0 responses_positive
3726 ,0 customers_targeted
3727 ,0 customers_new
3728 ,0 registrations
3729 ,0 cancellations
3730 ,0 attendance
3731 ,0 OPPORTUNITY_AMT_S
3732 ,0 ORDERS_BOOKED_AMT_S
3733 ,0 REVENUE_FORECASTED_S
3734 ,0 REVENUE_ACTUAL_S
3735 ,0 COST_FORECASTED_S
3736 ,0 COST_ACTUAL_S
3737 ,0 BUDGET_REQUESTED_S
3738 ,0 BUDGET_APPROVED_S
3739 ,0 conversion_rate_S
3740 ,0 metric1
3741 ,0 metric2
3742 FROM ams_act_metric_hst f3
3743 ,ams_metrics_all_b g3
3744 ,bim_i_source_codes a
3745 WHERE f3.last_update_date between p_start_date and p_end_date
3746 AND f3.arc_act_metric_used_by = a.object_type
3747 AND f3.act_metric_used_by_id = a.object_id
3748 AND a.child_object_id =0
3749 --AND a.object_type NOT IN ('RCAM')
3750 -- AND a.object_type='CAMP' commented for camp.,event,one off
3751 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
3752 AND g3.metric_category = 903
3753 AND g3.metric_id = f3.metric_id
3754 GROUP BY trunc(f3.last_update_date)
3755 ,a.source_code_id
3756 ,a.object_type
3757 ,a.object_id
3758 ,a.child_object_type
3759 ,a.child_object_id
3760 ,a.object_country
3761 ,a.object_region
3762 ,a.child_object_country
3763 ,a.child_object_region
3764 ,a.category_id
3765 ,a.object_status
3766 ,a.child_object_status
3767 ,a.object_purpose
3768 ,a.child_object_purpose
3769 ,a.activity_type
3770 ,a.activity_id
3771 ,a.start_date
3772 ,a.end_date
3773 ,a.business_unit_id
3774 HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
3775 --for campaign schedule forecasted response
3776 union all --forecasted campaign schedule response
3777 SELECT trunc(f3.last_update_date) transaction_create_date
3778 ,a.source_code_id source_code_id
3779 ,a.object_type object_type
3780 ,a.object_id object_id
3781 ,a.child_object_type child_object_type
3782 ,a.child_object_id child_object_id
3783 ,0 lead_rank_id
3784 ,a.object_country
3785 ,a.object_region
3786 ,a.child_object_country
3787 ,a.child_object_region
3788 ,a.category_id
3789 ,a.business_unit_id business_unit_id
3790 ,a.start_date
3791 ,a.end_date
3792 ,a.object_status object_status
3793 ,a.child_object_status child_object_status
3794 ,a.object_purpose object_purpose
3795 ,a.child_object_purpose child_object_purpose
3796 ,a.activity_type activity_type
3797 ,a.activity_id activity_id
3798 ,0 conversion_rate
3799 ,null from_currency
3800 ,0 leads
3801 ,0 opportunities
3802 ,0 opportunity_amt
3803 ,0 opportunities_open
3804 ,0 quotes
3805 ,0 quotes_open
3806 ,0 orders_booked
3807 ,0 orders_booked_amt
3808 ,0 budget_requested
3809 ,0 budget_approved
3810 ,0 revenue_forecasted
3811 ,0 revenue_actual
3812 ,0 cost_forecasted
3813 ,0 cost_actual
3814 ,sum(nvl(f3.func_forecasted_delta,0)) responses_forecasted
3815 ,0 responses_positive
3816 ,0 customers_targeted
3817 ,0 customers_new
3818 ,0 registrations
3819 ,0 cancellations
3820 ,0 attendance
3821 ,0 OPPORTUNITY_AMT_S
3822 ,0 ORDERS_BOOKED_AMT_S
3823 ,0 REVENUE_FORECASTED_S
3824 ,0 REVENUE_ACTUAL_S
3825 ,0 COST_FORECASTED_S
3826 ,0 COST_ACTUAL_S
3827 ,0 BUDGET_REQUESTED_S
3828 ,0 BUDGET_APPROVED_S
3829 ,0 conversion_rate_S
3830 ,0 metric1
3831 ,0 metric2
3832 FROM ams_act_metric_hst f3
3833 ,ams_metrics_all_b g3
3834 ,bim_i_source_codes a
3835 WHERE f3.last_update_date between p_start_date and p_end_date
3836 AND f3.act_metric_used_by_id = a.child_object_id
3837 AND f3.ARC_ACT_METRIC_USED_BY = a.child_object_type
3838 --AND a.object_type NOT IN ('RCAM')
3839 AND a.child_object_type in ('CSCH','EVEO')
3840 AND g3.metric_calculation_type IN ('MANUAL','FUNCTION')
3841 AND g3.metric_category = 903
3842 AND g3.metric_id = f3.metric_id
3843 GROUP BY trunc(f3.last_update_date)
3844 ,a.source_code_id
3845 ,a.object_type
3846 ,a.object_id
3847 ,a.child_object_type
3848 ,a.child_object_id
3849 ,a.object_country
3850 ,a.object_region
3851 ,a.child_object_country
3852 ,a.child_object_region
3853 ,a.category_id
3854 ,a.object_status
3855 ,a.child_object_status
3856 ,a.object_purpose
3857 ,a.child_object_purpose
3858 ,a.activity_type
3859 ,a.activity_id
3860 ,a.start_date
3861 ,a.end_date
3862 ,a.business_unit_id
3863 HAVING sum(nvl(f3.func_forecasted_delta,0)) <> 0
3864 union all --targeted audience
3865 SELECT trunc(p.creation_date) transaction_create_date
3866 ,a.source_code_id source_code_id
3867 ,a.object_type object_type
3868 ,a.object_id object_id
3869 ,a.child_object_type child_object_type
3870 ,a.child_object_id child_object_id
3871 ,0 lead_rank_id
3872 ,a.object_country
3873 ,a.object_region
3874 ,a.child_object_country
3875 ,a.child_object_region
3876 ,a.category_id
3877 ,a.business_unit_id business_unit_id
3878 ,a.start_date
3879 ,a.end_date
3880 ,a.object_status object_status
3881 ,a.child_object_status child_object_status
3882 ,a.object_purpose object_purpose
3883 ,a.child_object_purpose child_object_purpose
3884 ,a.activity_type activity_type
3885 ,a.activity_id activity_id
3886 ,0 conversion_rate
3887 ,null from_currency
3888 ,0 leads
3889 ,0 opportunities
3890 ,0 opportunity_amt
3891 ,0 opportunities_open
3892 ,0 quotes
3893 ,0 quotes_open
3894 ,0 orders_booked
3895 ,0 orders_booked_amt
3896 ,0 budget_requested
3897 ,0 budget_approved
3898 ,0 revenue_forecasted
3899 ,0 revenue_actual
3900 ,0 cost_forecasted
3901 ,0 cost_actual
3902 ,0 responses_forecasted
3903 ,0 responses_positive
3904 ,count(p.list_entry_id) customers_targeted
3905 ,0 customers_new
3906 ,0 registrations
3907 ,0 cancellations
3908 ,0 attendance
3909 ,0 OPPORTUNITY_AMT_S
3910 ,0 ORDERS_BOOKED_AMT_S
3911 ,0 REVENUE_FORECASTED_S
3912 ,0 REVENUE_ACTUAL_S
3913 ,0 COST_FORECASTED_S
3914 ,0 COST_ACTUAL_S
3915 ,0 BUDGET_REQUESTED_S
3916 ,0 BUDGET_APPROVED_S
3917 ,0 conversion_rate_S
3918 ,0 metric1
3919 ,0 metric2
3920 FROM ams_list_entries p
3921 ,ams_act_lists q
3922 ,bim_i_source_codes a
3923 WHERE p.creation_date between p_start_date and p_end_date
3924 AND p.list_header_id = q.list_header_id
3925 AND q.list_used_by = a.child_object_type
3926 AND q.list_used_by_id = a.child_object_id
3927 AND a.object_type NOT IN ('RCAM')
3928 AND q.list_used_by in ('CSCH','EVEO')
3929 AND q.list_act_type = 'TARGET'
3930 AND p.enabled_flag='Y'
3931 GROUP BY trunc(p.creation_date)
3932 ,a.source_code_id
3933 ,a.object_type
3934 ,a.object_id
3935 ,a.child_object_type
3936 ,a.child_object_id
3937 ,a.object_country
3938 ,a.object_region
3939 ,a.child_object_country
3940 ,a.child_object_region
3941 ,a.category_id
3942 ,a.object_status
3943 ,a.child_object_status
3944 ,a.object_purpose
3945 ,a.child_object_purpose
3946 ,a.activity_type
3947 ,a.activity_id
3948 ,a.start_date
3949 ,a.end_date
3950 ,a.business_unit_id
3951 union all --targeted audience for schedules of type event
3952 SELECT trunc(p.creation_date) transaction_create_date
3953 ,a.source_code_id source_code_id
3954 ,a.object_type object_type
3955 ,a.object_id object_id
3956 ,a.child_object_type child_object_type
3957 ,a.child_object_id child_object_id
3958 ,0 lead_rank_id
3959 ,a.object_country
3960 ,a.object_region
3961 ,a.child_object_country
3962 ,a.child_object_region
3963 ,a.category_id
3964 ,a.business_unit_id business_unit_id
3965 ,a.start_date
3966 ,a.end_date
3967 ,a.object_status object_status
3968 ,a.child_object_status child_object_status
3969 ,a.object_purpose object_purpose
3970 ,a.child_object_purpose child_object_purpose
3971 ,a.activity_type activity_type
3972 ,a.activity_id activity_id
3973 ,0 conversion_rate
3974 ,null from_currency
3975 ,0 leads
3976 ,0 opportunities
3977 ,0 opportunity_amt
3978 ,0 opportunities_open
3979 ,0 quotes
3980 ,0 quotes_open
3981 ,0 orders_booked
3982 ,0 orders_booked_amt
3983 ,0 budget_requested
3984 ,0 budget_approved
3985 ,0 revenue_forecasted
3986 ,0 revenue_actual
3987 ,0 cost_forecasted
3988 ,0 cost_actual
3989 ,0 responses_forecasted
3990 ,0 responses_positive
3991 ,count(p.list_entry_id) customers_targeted
3992 ,0 customers_new
3993 ,0 registrations
3994 ,0 cancellations
3995 ,0 attendance
3996 ,0 OPPORTUNITY_AMT_S
3997 ,0 ORDERS_BOOKED_AMT_S
3998 ,0 REVENUE_FORECASTED_S
3999 ,0 REVENUE_ACTUAL_S
4000 ,0 COST_FORECASTED_S
4001 ,0 COST_ACTUAL_S
4002 ,0 BUDGET_REQUESTED_S
4003 ,0 BUDGET_APPROVED_S
4004 ,0 conversion_rate_S
4005 ,0 metric1
4006 ,0 metric2
4007 FROM ams_list_entries p
4008 ,ams_act_lists q
4009 ,bim_i_source_codes a
4010 ,ams_campaign_schedules_b sch
4011 WHERE p.creation_date between p_start_date and p_end_date
4012 AND p.list_header_id = q.list_header_id
4013 AND q.list_used_by = 'EONE'
4014 AND q.list_act_type = 'TARGET'
4015 AND sch.schedule_id = a.child_object_id
4016 AND a.child_object_type = 'CSCH'
4017 AND sch.activity_type_code = 'EVENTS'
4018 AND q.list_used_by_id = sch.related_event_id
4019 AND a.object_type NOT IN ('RCAM')
4020 AND p.enabled_flag='Y'
4021 GROUP BY trunc(p.creation_date)
4022 ,a.source_code_id
4023 ,a.object_type
4024 ,a.object_id
4025 ,a.child_object_type
4026 ,a.child_object_id
4027 ,a.object_country
4028 ,a.object_region
4029 ,a.child_object_country
4030 ,a.child_object_region
4031 ,a.category_id
4032 ,a.object_status
4033 ,a.child_object_status
4034 ,a.object_purpose
4035 ,a.child_object_purpose
4036 ,a.activity_type
4037 ,a.activity_id
4038 ,a.start_date
4039 ,a.end_date
4040 ,a.business_unit_id
4041 union all --budgets
4042 SELECT /*+ USE_NL(A B S) */
4043 trunc(nvl(s.approval_date,s.last_update_date)) transaction_create_date
4044 ,a.source_code_id source_code_id
4045 ,a.object_type object_type
4046 ,a.object_id object_id
4047 ,a.child_object_type child_object_type
4048 ,a.child_object_id child_object_id
4049 ,0 lead_rank_id
4050 ,a.object_country
4051 ,a.object_region
4052 ,a.child_object_country
4053 ,a.child_object_region
4054 ,a.category_id
4055 ,a.business_unit_id business_unit_id
4056 ,a.start_date
4057 ,a.end_date
4058 ,a.object_status object_status
4059 ,a.child_object_status child_object_status
4060 ,a.object_purpose object_purpose
4061 ,a.child_object_purpose child_object_purpose
4062 ,a.activity_type activity_type
4063 ,a.activity_id activity_id
4064 ,0 conversion_rate_s
4065 ,nvl(s.request_currency,'USD') from_currency
4066 ,0 leads
4067 ,0 opportunities
4068 ,0 opportunity_amt
4069 ,0 opportunities_open
4070 ,0 quotes
4071 ,0 quotes_open
4072 ,0 orders_booked
4073 ,0 orders_booked_amt
4074 ,0 budget_requested
4075 ,sum(nvl(s.approved_amount,0)) budget_approved
4076 ,0 revenue_forecasted
4077 ,0 revenue_actual
4078 ,0 cost_actual
4079 ,0 cost_forecasted
4080 ,0 responses_forecasted
4081 ,0 responses_positive
4082 ,0 customers_targeted
4083 ,0 customers_new
4084 ,0 registrations
4085 ,0 cancellations
4086 ,0 attendance
4087 ,0 OPPORTUNITY_AMT_S
4088 ,0 ORDERS_BOOKED_AMT_S
4089 ,0 REVENUE_FORECASTED_S
4090 ,0 REVENUE_ACTUAL_S
4091 ,0 COST_FORECASTED_S
4092 ,0 COST_ACTUAL_S
4093 ,0 BUDGET_REQUESTED_S
4094 ,0 BUDGET_APPROVED_S
4095 ,0 conversion_rate_s
4096 ,0 metric1
4097 ,0 metric2
4098 FROM ozf_act_budgets S
4099 ,bim_i_source_codes A
4100 ,ams_source_codes B
4101 WHERE s.act_budget_used_by_id = b.source_code_for_id
4102 AND s.arc_act_budget_used_by = b.arc_source_code_for
4103 AND b.source_code_id = a.source_code_id
4104 AND a.object_type NOT IN ('RCAM')
4105 AND s.budget_source_type = 'FUND'
4106 AND s.parent_act_budget_id IS NULL
4107 AND s.status_code = 'APPROVED'
4108 AND trunc(nvl(s.approval_date,s.last_update_date)) between p_start_date and p_end_date
4109 GROUP BY
4110 trunc(nvl(s.approval_date,s.last_update_date))
4111 ,a.source_code_id
4112 ,a.object_id
4113 ,a.object_type
4114 ,a.child_object_type
4115 ,a.child_object_id
4116 ,a.object_country
4117 ,a.child_object_country
4118 ,a.object_region
4119 ,a.child_object_region
4120 ,a.category_id
4121 ,a.object_status
4122 ,a.child_object_status
4123 ,a.object_purpose
4124 ,a.child_object_purpose
4125 ,a.activity_type
4126 ,a.activity_id
4127 ,a.business_unit_id
4128 ,a.start_date
4129 ,a.end_date
4130 ,nvl(s.request_currency,'USD')
4131 HAVING sum(nvl(s.approved_amount,0)) > 0
4132 union all --budget2
4133 SELECT /*+ USE_NL(A B S) */
4134 trunc(a.start_date) transaction_create_date
4135 ,a.source_code_id source_code_id
4136 ,a.object_type object_type
4137 ,a.object_id object_id
4138 ,a.child_object_type child_object_type
4139 ,a.child_object_id child_object_id
4140 ,0 lead_rank_id
4141 ,a.object_country
4142 ,a.object_region
4143 ,a.child_object_country
4144 ,a.child_object_region
4145 ,a.category_id
4146 ,a.business_unit_id business_unit_id
4147 ,a.start_date
4148 ,a.end_date
4149 ,a.object_status object_status
4150 ,a.child_object_status child_object_status
4151 ,a.object_purpose object_purpose
4152 ,a.child_object_purpose child_object_purpose
4153 ,a.activity_type activity_type
4154 ,a.activity_id activity_id
4155 ,0 conversion_rate
4156 ,nvl(request_currency,'USD') from_currency
4157 ,0 leads
4158 ,0 opportunities
4159 ,0 opportunity_amt
4160 ,0 opportunities_open
4161 ,0 quotes
4162 ,0 quotes_open
4163 ,0 orders_booked
4164 ,0 orders_booked_amt
4165 ,0 budget_requested
4166 ,0-sum(nvl(s.approved_amount,0)) budget_approved
4167 ,0 revenue_forecasted
4168 ,0 revenue_actual
4169 ,0 cost_actual
4170 ,0 cost_forecasted
4171 ,0 responses_forecasted
4172 ,0 responses_positive
4173 ,0 customers_targeted
4174 ,0 customers_new
4175 ,0 registrations
4176 ,0 cancellations
4177 ,0 attendance
4178 ,0 OPPORTUNITY_AMT_S
4179 ,0 ORDERS_BOOKED_AMT_S
4180 ,0 REVENUE_FORECASTED_S
4181 ,0 REVENUE_ACTUAL_S
4182 ,0 COST_FORECASTED_S
4183 ,0 COST_ACTUAL_S
4184 ,0 BUDGET_REQUESTED_S
4185 ,0 BUDGET_APPROVED_S
4186 ,0 conversion_rate_s
4187 ,0 metric1
4188 ,0 metric2
4189 FROM ozf_act_budgets S
4190 ,bim_i_source_codes A
4191 ,ams_source_codes B
4192 WHERE s.arc_act_budget_used_by = 'FUND'
4193 AND s.parent_act_budget_id IS NULL
4194 AND s.budget_source_type = b.arc_source_code_for
4195 AND s.budget_source_id = b.source_code_for_id
4196 AND b.source_code_id = a.source_code_id
4197 AND a.object_type NOT IN ('RCAM')
4198 AND nvl(s.approval_date,s.last_update_date) between p_start_date and p_end_date
4199 GROUP BY
4200 trunc(nvl(s.approval_date,s.last_update_date))
4201 ,a.source_code_id
4202 ,a.object_id
4203 ,a.object_type
4204 ,a.child_object_type
4205 ,a.child_object_id
4206 ,a.object_country
4207 ,a.child_object_country
4208 ,a.object_region
4209 ,a.child_object_region
4210 ,a.category_id
4211 ,a.object_status
4212 ,a.child_object_status
4213 ,a.object_purpose
4214 ,a.child_object_purpose
4215 ,a.activity_type
4216 ,a.activity_id
4217 ,a.business_unit_id
4218 ,a.start_date
4219 ,a.end_date
4220 ,nvl(request_currency,'USD')
4221 union all --budget1 for campaign schedules and event schedules
4222 SELECT /*+ USE_NL(A B S) */
4223 trunc(nvl(s.approval_date,s.last_update_date)) transaction_create_date
4224 ,a.source_code_id source_code_id
4225 ,a.object_type object_type
4226 ,a.object_id object_id
4227 ,a.child_object_type child_object_type
4228 ,a.child_object_id child_object_id
4229 ,0 lead_rank_id
4230 ,a.object_country
4231 ,a.object_region
4232 ,a.child_object_country
4233 ,a.child_object_region
4234 ,a.category_id
4235 ,a.business_unit_id business_unit_id
4236 ,a.start_date
4237 ,a.end_date
4238 ,a.object_status object_status
4239 ,a.child_object_status child_object_status
4240 ,a.object_purpose object_purpose
4241 ,a.child_object_purpose child_object_purpose
4242 ,a.activity_type activity_type
4243 ,a.activity_id activity_id
4244 ,0 conversion_rate_s
4245 ,nvl(s.request_currency,'USD') from_currency
4246 ,0 leads
4247 ,0 opportunities
4248 ,0 opportunity_amt
4249 ,0 opportunities_open
4250 ,0 quotes
4251 ,0 quotes_open
4252 ,0 orders_booked
4253 ,0 orders_booked_amt
4254 ,0 budget_requested
4255 ,0 budget_approved
4256 ,0 revenue_forecasted
4257 ,0 revenue_actual
4258 ,0 cost_actual
4259 ,0 cost_forecasted
4260 ,0 responses_forecasted
4261 ,0 responses_positive
4262 ,0 customers_targeted
4263 ,0 customers_new
4264 ,0 registrations
4265 ,0 cancellations
4266 ,0 attendance
4267 ,0 OPPORTUNITY_AMT_S
4268 ,0 ORDERS_BOOKED_AMT_S
4269 ,0 REVENUE_FORECASTED_S
4270 ,0 REVENUE_ACTUAL_S
4271 ,0 COST_FORECASTED_S
4272 ,0 COST_ACTUAL_S
4273 ,0 BUDGET_REQUESTED_S
4274 ,0 BUDGET_APPROVED_S
4275 ,0 conversion_rate_s
4276 ,sum(nvl(s.approved_amount,0)) metric1
4277 ,0 metric2
4278 FROM ozf_act_budgets S
4279 ,bim_i_source_codes A
4280 ,ams_source_codes B
4281 WHERE s.act_budget_used_by_id = b.source_code_for_id
4282 AND s.arc_act_budget_used_by = b.arc_source_code_for
4283 AND b.source_code_id = a.source_code_id
4284 AND a.child_object_type IN ('CSCH','EVEO')
4285 AND s.budget_source_type = 'FUND'
4286 AND s.status_code = 'APPROVED'
4287 AND trunc(nvl(s.approval_date,s.last_update_date)) between p_start_date and p_end_date
4288 GROUP BY
4289 trunc(nvl(s.approval_date,s.last_update_date))
4290 ,a.source_code_id
4291 ,a.object_id
4292 ,a.object_type
4293 ,a.child_object_type
4294 ,a.child_object_id
4295 ,a.object_country
4296 ,a.child_object_country
4297 ,a.object_region
4298 ,a.child_object_region
4299 ,a.category_id
4300 ,a.object_status
4301 ,a.child_object_status
4302 ,a.object_purpose
4303 ,a.child_object_purpose
4304 ,a.activity_type
4305 ,a.activity_id
4306 ,a.business_unit_id
4307 ,a.start_date
4308 ,a.end_date
4309 ,nvl(s.request_currency,'USD')
4310 HAVING sum(nvl(s.approved_amount,0)) > 0
4311 union all --budget2 for campaign schedules and Events
4312 SELECT /*+ USE_NL(A B S) */
4313 trunc(a.start_date) transaction_create_date
4314 ,a.source_code_id source_code_id
4315 ,a.object_type object_type
4316 ,a.object_id object_id
4317 ,a.child_object_type child_object_type
4318 ,a.child_object_id child_object_id
4319 ,0 lead_rank_id
4320 ,a.object_country
4321 ,a.object_region
4322 ,a.child_object_country
4323 ,a.child_object_region
4324 ,a.category_id
4325 ,a.business_unit_id business_unit_id
4326 ,a.start_date
4327 ,a.end_date
4328 ,a.object_status object_status
4329 ,a.child_object_status child_object_status
4330 ,a.object_purpose object_purpose
4331 ,a.child_object_purpose child_object_purpose
4332 ,a.activity_type activity_type
4333 ,a.activity_id activity_id
4334 ,0 conversion_rate
4335 ,nvl(request_currency,'USD') from_currency
4336 ,0 leads
4337 ,0 opportunities
4338 ,0 opportunity_amt
4339 ,0 opportunities_open
4340 ,0 quotes
4341 ,0 quotes_open
4342 ,0 orders_booked
4343 ,0 orders_booked_amt
4344 ,0 budget_requested
4345 ,0 budget_approved
4346 ,0 revenue_forecasted
4347 ,0 revenue_actual
4348 ,0 cost_actual
4349 ,0 cost_forecasted
4350 ,0 responses_forecasted
4351 ,0 responses_positive
4352 ,0 customers_targeted
4353 ,0 customers_new
4354 ,0 registrations
4355 ,0 cancellations
4356 ,0 attendance
4357 ,0 OPPORTUNITY_AMT_S
4358 ,0 ORDERS_BOOKED_AMT_S
4359 ,0 REVENUE_FORECASTED_S
4360 ,0 REVENUE_ACTUAL_S
4361 ,0 COST_FORECASTED_S
4362 ,0 COST_ACTUAL_S
4363 ,0 BUDGET_REQUESTED_S
4364 ,0 BUDGET_APPROVED_S
4365 ,0 conversion_rate_s
4366 ,0-sum(nvl(s.approved_amount,0)) metric1
4367 ,0 metric2
4368 FROM ozf_act_budgets S
4369 ,bim_i_source_codes A
4370 ,ams_source_codes B
4371 WHERE s.arc_act_budget_used_by = 'FUND'
4372 AND s.budget_source_type = b.arc_source_code_for
4373 AND s.budget_source_id = b.source_code_for_id
4374 AND b.source_code_id = a.source_code_id
4375 AND a.child_object_type IN ('CSCH','EVEO')
4376 AND nvl(s.approval_date,s.last_update_date) between p_start_date and p_end_date
4377 GROUP BY
4378 trunc(nvl(s.approval_date,s.last_update_date))
4379 ,a.source_code_id
4380 ,a.object_id
4381 ,a.object_type
4382 ,a.child_object_type
4383 ,a.child_object_id
4384 ,a.object_country
4385 ,a.child_object_country
4386 ,a.object_region
4387 ,a.child_object_region
4388 ,a.category_id
4389 ,a.object_status
4390 ,a.child_object_status
4391 ,a.object_purpose
4392 ,a.child_object_purpose
4393 ,a.activity_type
4394 ,a.activity_id
4395 ,a.business_unit_id
4396 ,a.start_date
4397 ,a.end_date
4398 ,nvl(request_currency,'USD')
4399 union all --registration1
4400 SELECT /*+ USE_NL(X A) */
4401 trunc(X.last_reg_status_date) transaction_create_date
4402 ,a.source_code_id source_code_id
4403 ,a.object_type object_type
4404 ,a.object_id object_id
4405 ,a.child_object_type child_object_type
4406 ,a.child_object_id child_object_id
4407 ,0 lead_rank_id
4408 ,a.object_country
4409 ,a.object_region
4410 ,a.child_object_country
4411 ,a.child_object_region
4412 ,a.category_id
4413 ,a.business_unit_id business_unit_id
4414 ,a.start_date
4415 ,a.end_date
4416 ,a.object_status object_status
4417 ,a.child_object_status child_object_status
4418 ,a.object_purpose object_purpose
4419 ,a.child_object_purpose child_object_purpose
4420 ,a.activity_type activity_type
4421 ,a.activity_id activity_id
4422 ,0 conversion_rate
4423 ,null from_currency
4424 ,0 leads
4425 ,0 opportunities
4426 ,0 opportunity_amt
4427 ,0 opportunities_open
4428 ,0 quotes
4429 ,0 quotes_open
4430 ,0 orders_booked
4431 ,0 orders_booked_amt
4432 ,0 budget_requested
4433 ,0 budget_approved
4434 ,0 revenue_forecasted
4435 ,0 revenue_actual
4436 ,0 cost_actual
4437 ,0 cost_forecasted
4438 ,0 responses_forecasted
4439 ,0 responses_positive
4440 ,0 customers_targeted
4441 ,0 customers_new
4442 ,SUM(decode(X.system_status_code,'REGISTERED',1,0)) registrations
4443 ,SUM(decode(X.system_status_code,'CANCELLED',1,0)) cancellations
4444 ,SUM(decode(X.system_status_code,'REGISTERED',decode(attended_flag,'Y',1,0),0)) attendance
4445 ,0 OPPORTUNITY_AMT_S
4446 ,0 ORDERS_BOOKED_AMT_S
4447 ,0 REVENUE_FORECASTED_S
4448 ,0 REVENUE_ACTUAL_S
4449 ,0 COST_FORECASTED_S
4450 ,0 COST_ACTUAL_S
4451 ,0 BUDGET_REQUESTED_S
4452 ,0 BUDGET_APPROVED_S
4453 ,0 conversion_rate_S
4454 ,0 metric1
4455 ,0 metric2
4456 FROM ams_event_registrations X
4457 ,bim_i_source_codes A
4458 WHERE trunc(X.last_reg_status_date) between p_start_date and p_end_date+0.99999
4459 AND X.event_offer_id = A.child_object_id
4460 AND A.child_object_type ='EVEO'
4461 AND a.object_type NOT IN ('RCAM')
4462 GROUP BY
4463 trunc(X.last_reg_status_date)
4464 ,a.source_code_id
4465 ,a.object_id
4466 ,a.object_type
4467 ,a.child_object_type
4468 ,a.child_object_id
4469 ,a.object_country
4470 ,a.child_object_country
4471 ,a.object_region
4472 ,a.child_object_region
4473 ,a.category_id
4474 ,a.object_status
4475 ,a.child_object_status
4476 ,a.object_purpose
4477 ,a.child_object_purpose
4478 ,a.activity_type
4479 ,a.activity_id
4480 ,a.business_unit_id
4481 ,a.start_date
4482 ,a.end_date
4483 union all --registration2
4484 SELECT /*+ USE_NL(X A) */
4485 trunc(X.last_reg_status_date) transaction_create_date
4486 ,a.source_code_id source_code_id
4487 ,a.object_type object_type
4488 ,a.object_id object_id
4489 ,a.child_object_type child_object_type
4490 ,a.child_object_id child_object_id
4491 ,0 lead_rank_id
4492 ,a.object_country
4493 ,a.object_region
4494 ,a.child_object_country
4495 ,a.child_object_region
4496 ,a.category_id
4497 ,a.business_unit_id business_unit_id
4498 ,a.start_date
4499 ,a.end_date
4500 ,a.object_status object_status
4501 ,a.child_object_status child_object_status
4502 ,a.object_purpose object_purpose
4503 ,a.child_object_purpose child_object_purpose
4504 ,a.activity_type activity_type
4505 ,a.activity_id activity_id
4506 ,0 conversion_rate
4507 ,null from_currency
4508 ,0 leads
4509 ,0 opportunities
4510 ,0 opportunity_amt
4511 ,0 opportunities_open
4512 ,0 quotes
4513 ,0 quotes_open
4514 ,0 orders_booked
4515 ,0 orders_booked_amt
4516 ,0 budget_requested
4517 ,0 budget_approved
4518 ,0 revenue_forecasted
4519 ,0 revenue_actual
4520 ,0 cost_actual
4521 ,0 cost_forecasted
4522 ,0 responses_forecasted
4523 ,0 responses_positive
4524 ,0 customers_targeted
4525 ,0 customers_new
4526 ,SUM(decode(X.system_status_code,'REGISTERED',1,0)) registrations
4527 ,SUM(decode(X.system_status_code,'CANCELLED',1,0)) cancellations
4528 ,SUM(decode(X.system_status_code,'REGISTERED',decode(attended_flag,'Y',1,0),0)) attendance
4529 ,0 OPPORTUNITY_AMT_S
4530 ,0 ORDERS_BOOKED_AMT_S
4531 ,0 REVENUE_FORECASTED_S
4532 ,0 REVENUE_ACTUAL_S
4533 ,0 COST_FORECASTED_S
4534 ,0 COST_ACTUAL_S
4535 ,0 BUDGET_REQUESTED_S
4536 ,0 BUDGET_APPROVED_S
4537 ,0 conversion_rate_S
4538 ,0 metric1
4539 ,0 metric2
4540 FROM ams_event_registrations X
4541 ,bim_i_source_codes A
4542 WHERE trunc(X.last_reg_status_date) between p_start_date and p_end_date+0.99999
4543 AND X.event_offer_id = A.object_id
4544 AND A.object_type ='EONE'
4545 AND a.object_type NOT IN ('RCAM')
4546 GROUP BY
4547 trunc(X.last_reg_status_date)
4548 ,a.source_code_id
4549 ,a.object_id
4550 ,a.object_type
4551 ,a.child_object_type
4552 ,a.child_object_id
4553 ,a.object_country
4554 ,a.child_object_country
4555 ,a.object_region
4556 ,a.child_object_region
4557 ,a.category_id
4558 ,a.object_status
4559 ,a.child_object_status
4560 ,a.object_purpose
4561 ,a.child_object_purpose
4562 ,a.activity_type
4563 ,a.activity_id
4564 ,a.business_unit_id
4565 ,a.start_date
4566 ,a.end_date
4567 )
4568 GROUP BY transaction_create_date
4569 ,source_code_id
4570 ,object_type
4571 ,object_id
4572 ,child_object_type
4573 ,child_object_id
4574 ,lead_rank_id
4575 ,object_country
4576 ,object_region
4577 ,child_object_country
4578 ,child_object_region
4579 ,category_id
4580 ,business_unit_id
4581 ,start_date
4582 ,end_date
4583 ,object_status
4584 ,child_object_status
4585 ,object_purpose
4586 ,child_object_purpose
4587 ,activity_type
4588 ,activity_id
4589 ,conversion_rate
4590 ,from_currency
4591 ,conversion_rate_s) inner;
4592
4593 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Second insert BIM_I_MARKETING_FACTS_STG');
4594 ------------------------------NEW CODE----------------------
4595 INSERT INTO bim_i_marketing_facts_STG
4596 ( --MKT_DAILY_TRANSACTION_ID ,
4597 CREATION_DATE ,
4598 LAST_UPDATE_DATE ,
4599 CREATED_BY ,
4600 LAST_UPDATED_BY ,
4601 LAST_UPDATE_LOGIN ,
4602 TRANSACTION_CREATE_DATE ,
4603 LEAD_ID ,
4604 METRIC_TYPE ,
4605 SOURCE_CODE_ID ,
4606 OBJECT_TYPE ,
4607 OBJECT_ID ,
4608 CHILD_OBJECT_TYPE ,
4609 CHILD_OBJECT_ID ,
4610 LEAD_RANK_ID ,
4611 OBJECT_COUNTRY ,
4612 OBJECT_REGION ,
4613 CHILD_OBJECT_COUNTRY ,
4614 CHILD_OBJECT_REGION ,
4615 CATEGORY_ID ,
4616 BUSINESS_UNIT_ID ,
4617 START_DATE ,
4618 END_DATE ,
4619 OBJECT_STATUS ,
4620 CHILD_OBJECT_STATUS ,
4621 OBJECT_PURPOSE ,
4622 CHILD_OBJECT_PURPOSE ,
4623 ACTIVITY_TYPE ,
4624 ACTIVITY_ID ,
4625 CONVERSION_RATE ,
4626 FROM_CURRENCY ,
4627 LEADS ,
4628 OPPORTUNITIES ,
4629 OPPORTUNITY_AMT ,
4630 OPPORTUNITIES_OPEN ,
4631 ORDERS_BOOKED ,
4632 ORDERS_BOOKED_AMT ,
4633 REVENUE_FORECASTED ,
4634 REVENUE_ACTUAL ,
4635 COST_FORECASTED ,
4636 COST_ACTUAL ,
4637 BUDGET_APPROVED ,
4638 BUDGET_REQUESTED ,
4639 RESPONSES_FORECASTED ,
4640 RESPONSES_POSITIVE ,
4641 CUSTOMERS_NEW ,
4642 REGISTRATIONS ,
4643 CANCELLATIONS ,
4644 ATTENDANCE ,
4645 OPPORTUNITY_AMT_S ,
4646 ORDERS_BOOKED_AMT_S ,
4647 REVENUE_FORECASTED_S ,
4648 REVENUE_ACTUAL_S ,
4649 COST_FORECASTED_S ,
4650 COST_ACTUAL_S ,
4651 BUDGET_REQUESTED_S ,
4652 BUDGET_APPROVED_S ,
4653 CONVERSION_RATE_S ,
4654 metric1 ,
4655 metric2)
4656 (
4657 SELECT /*+ use_hash(INNER) */
4658 sysdate creation_date
4659 ,sysdate last_update_date
4660 ,-1 created_by
4661 ,-1 last_updated_by
4662 ,-1 last_update_login
4663 ,inner.transaction_create_date transaction_create_date
4664 ,0 lead_id
4665 ,inner.metric_type metric_type
4666 ,inner.source_code_id source_code_id
4667 ,inner.object_type object_type
4668 ,inner.object_id object_id
4669 ,inner.child_object_type child_object_type
4670 ,inner.child_object_id child_object_id
4671 ,inner.lead_rank_id lead_rank_id
4672 ,inner.object_country object_country
4673 ,inner.object_region object_region
4674 ,inner.child_object_country child_object_country
4675 ,inner.child_object_region child_object_region
4676 ,inner.category_id category_id
4677 ,inner.business_unit_id business_unit_id
4678 ,inner.start_date start_date
4679 ,inner.end_date end_date
4680 ,inner.object_status object_status
4681 ,inner.child_object_status child_object_status
4682 ,inner.object_purpose object_purpose
4683 ,inner.child_object_purpose child_object_purpose
4684 ,inner.activity_type activity_type
4685 ,inner.activity_id activity_id
4686 ,inner.conversion_rate conversion_rate
4687 ,inner.from_currency from_currency
4688 ,inner.leads leads
4689 ,inner.opportunities opportunities
4690 ,inner.opportunity_amt opportunity_amt
4691 ,inner.opportunities_open opportunities_open
4692 ,inner.orders_booked orders_booked
4693 ,inner.orders_booked_amt orders_booked_amt
4694 ,inner.revenue_forecasted revenue_forecasted
4695 ,inner.revenue_actual revenue_actual
4696 ,inner.cost_forecasted cost_forecasted
4697 ,inner.cost_actual cost_actual
4698 ,inner.budget_approved budget_approved
4699 ,inner.budget_requested budget_requested
4700 ,inner.responses_forecasted responses_forecasted
4701 ,inner.responses_positive responses_positive
4702 ,inner.customers_new customers_new
4703 ,inner.registrations registrations
4704 ,inner.cancellations cancellations
4705 ,inner.attendance attendance
4706 ,inner.OPPORTUNITY_AMT_S OPPORTUNITY_AMT_S
4707 ,inner.ORDERS_BOOKED_AMT_S ORDERS_BOOKED_AMT_S
4708 ,inner.REVENUE_FORECASTED_S REVENUE_FORECASTED_S
4709 ,inner.REVENUE_ACTUAL_S REVENUE_ACTUAL_S
4710 ,inner.COST_FORECASTED_S COST_FORECASTED_S
4711 ,inner.COST_ACTUAL_S COST_ACTUAL_S
4712 ,inner.BUDGET_REQUESTED_S BUDGET_REQUESTED_S
4713 ,inner.BUDGET_APPROVED_S BUDGET_APPROVED_S
4714 ,inner.CONVERSION_RATE_S CONVERSION_RATE_S
4715 ,inner.metric1 metric1
4716 ,inner.metric2 metric2
4717 FROM (
4718 SELECT /*+ USE_NL(F1 G1 A) ordered */
4719 trunc(a.start_date) transaction_create_date
4720 ,'FCOST' metric_type
4721 ,a.source_code_id source_code_id
4722 ,a.object_type object_type
4723 ,a.object_id object_id
4724 ,a.child_object_type child_object_type
4725 ,a.child_object_id child_object_id
4726 ,0 lead_rank_id
4727 ,a.object_country
4728 ,a.object_region
4729 ,a.child_object_country
4730 ,a.child_object_region
4731 ,a.category_id category_id
4732 ,a.business_unit_id business_unit_id
4733 ,a.start_date
4734 ,a.end_date
4735 ,a.object_status object_status
4736 ,a.child_object_status child_object_status
4737 ,a.object_purpose object_purpose
4738 ,a.child_object_purpose child_object_purpose
4739 ,a.activity_type activity_type
4740 ,a.activity_id activity_id
4741 ,0 conversion_rate
4742 ,nvl(f1.functional_currency_code,'USD') from_currency
4743 ,0 leads
4744 ,0 opportunities
4745 ,0 opportunity_amt
4746 ,0 opportunities_open
4747 ,0 quotes
4748 ,0 quotes_open
4749 ,0 orders_booked
4750 ,0 orders_booked_amt
4751 ,0 budget_requested
4752 ,0 budget_approved
4753 ,0 revenue_forecasted
4754 ,0 revenue_actual
4755 ,sum(nvl(f1.func_forecasted_delta,0)) cost_forecasted
4756 ,0 cost_actual
4757 ,0 responses_forecasted
4758 ,0 responses_positive
4759 ,0 customers_targeted
4760 ,0 customers_new
4761 ,0 registrations
4762 ,0 cancellations
4763 ,0 attendance
4764 ,0 OPPORTUNITY_AMT_S
4765 ,0 ORDERS_BOOKED_AMT_S
4766 ,0 REVENUE_FORECASTED_S
4767 ,0 REVENUE_ACTUAL_S
4768 ,0 COST_FORECASTED_S
4769 ,0 COST_ACTUAL_S
4770 ,0 BUDGET_REQUESTED_S
4771 ,0 BUDGET_APPROVED_S
4772 ,0 conversion_rate_s
4773 ,0 metric1
4774 ,0 metric2
4775 FROM bim_i_source_codes a
4776 ,ams_act_metric_hst f1
4777 ,ams_metrics_all_b g1
4778 WHERE
4779 f1.arc_act_metric_used_by = a.object_type
4780 AND f1.act_metric_used_by_id = a.object_id
4781 AND a.object_type NOT IN ('RCAM')
4782 AND a.child_object_id = 0
4783 AND g1.metric_category = 901
4784 AND g1.metric_id = f1.metric_id
4785 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
4786 GROUP BY
4787 a.source_code_id
4788 ,a.object_type
4789 ,a.object_id
4790 ,a.child_object_type
4791 ,a.child_object_id
4792 ,a.object_country
4793 ,a.object_region
4794 ,a.child_object_country
4795 ,a.child_object_region
4796 ,a.category_id
4797 ,a.object_status
4798 ,a.child_object_status
4799 ,a.object_purpose
4800 ,a.child_object_purpose
4801 ,a.activity_type
4802 ,a.activity_id
4803 ,a.start_date
4804 ,a.end_date
4805 ,a.business_unit_id
4806 ,nvl(f1.functional_currency_code,'USD')
4807 HAVING sum(nvl(f1.func_forecasted_delta,0)) <> 0
4808 UNION ALL
4809 SELECT /*+ USE_NL(F1 G1 A) ordered */
4810 trunc(a.start_date) transaction_create_date
4811 ,'FCOST' metric_type
4812 ,a.source_code_id source_code_id
4813 ,a.object_type object_type
4814 ,a.object_id object_id
4815 ,a.child_object_type child_object_type
4816 ,a.child_object_id child_object_id
4817 ,0 lead_rank_id
4818 ,a.object_country
4819 ,a.object_region
4820 ,a.child_object_country
4821 ,a.child_object_region
4822 ,category_id category_id
4823 ,a.business_unit_id business_unit_id
4824 ,a.start_date
4825 ,a.end_date
4826 ,a.object_status object_status
4827 ,a.child_object_status child_object_status
4828 ,a.object_purpose object_purpose
4829 ,a.child_object_purpose child_object_purpose
4830 ,a.activity_type activity_type
4831 ,a.activity_id activity_id
4832 ,0 conversion_rate
4833 ,nvl(f1.functional_currency_code,'USD') from_currency
4834 ,0 leads
4835 ,0 opportunities
4836 ,0 opportunity_amt
4837 ,0 opportunities_open
4838 ,0 quotes
4839 ,0 quotes_open
4840 ,0 orders_booked
4841 ,0 orders_booked_amt
4842 ,0 budget_requested
4843 ,0 budget_approved
4844 ,0 revenue_forecasted
4845 ,0 revenue_actual
4846 ,sum(nvl(f1.func_forecasted_delta,0)) cost_forecasted
4847 ,0 cost_actual
4848 ,0 responses_forecasted
4849 ,0 responses_positive
4850 ,0 customers_targeted
4851 ,0 customers_new
4852 ,0 registrations
4853 ,0 cancellations
4854 ,0 attendance
4855 ,0 OPPORTUNITY_AMT_S
4856 ,0 ORDERS_BOOKED_AMT_S
4857 ,0 REVENUE_FORECASTED_S
4858 ,0 REVENUE_ACTUAL_S
4859 ,0 COST_FORECASTED_S
4860 ,0 COST_ACTUAL_S
4861 ,0 BUDGET_REQUESTED_S
4862 ,0 BUDGET_APPROVED_S
4863 ,0 conversion_rate_s
4864 ,0 metric1
4865 ,0 metric2
4866 FROM bim_i_source_codes a
4867 ,ams_act_metric_hst f1
4868 ,ams_metrics_all_b g1
4869 WHERE
4870 f1.arc_act_metric_used_by in ('CSCH','EVEO')
4871 AND f1.act_metric_used_by_id = a.child_object_id
4872 AND a.object_type NOT IN ('RCAM')
4873 AND f1.ARC_ACT_METRIC_USED_BY = a.child_object_type
4874 AND g1.metric_category = 901
4875 AND g1.metric_id = f1.metric_id
4876 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
4877 GROUP BY
4878 a.source_code_id
4879 ,a.object_type
4880 ,a.object_id
4881 ,a.child_object_type
4882 ,a.child_object_id
4883 ,a.object_country
4884 ,a.object_region
4885 ,a.child_object_country
4886 ,a.child_object_region
4887 ,a.category_id
4888 ,a.object_status
4889 ,a.child_object_status
4890 ,a.object_purpose
4891 ,a.child_object_purpose
4892 ,a.activity_type
4893 ,a.activity_id
4894 ,a.start_date
4895 ,a.end_date
4896 ,a.business_unit_id
4897 ,nvl(f1.functional_currency_code,'USD')
4898 HAVING sum(nvl(f1.func_forecasted_delta,0)) <> 0
4899 UNION ALL
4900 SELECT /*+ USE_NL(F1 G1 A) ordered */
4901 trunc(a.start_date) transaction_create_date
4902 ,'FREV' metric_type
4903 ,a.source_code_id source_code_id
4904 ,a.object_type object_type
4905 ,a.object_id object_id
4906 ,a.child_object_type child_object_type
4907 ,a.child_object_id child_object_id
4908 ,0 lead_rank_id
4909 ,a.object_country
4910 ,a.object_region
4911 ,a.child_object_country
4912 ,a.child_object_region
4913 ,a.category_id
4914 ,a.business_unit_id business_unit_id
4915 ,a.start_date
4916 ,a.end_date
4917 ,a.object_status object_status
4918 ,a.child_object_status child_object_status
4919 ,a.object_purpose object_purpose
4920 ,a.child_object_purpose child_object_purpose
4921 ,a.activity_type activity_type
4922 ,a.activity_id activity_id
4923 ,0 conversion_rate
4924 ,nvl(f1.functional_currency_code,'USD') from_currency
4925 ,0 leads
4926 ,0 opportunities
4927 ,0 opportunity_amt
4928 ,0 opportunities_open
4929 ,0 quotes
4930 ,0 quotes_open
4931 ,0 orders_booked
4932 ,0 orders_booked_amt
4933 ,0 budget_requested
4934 ,0 budget_approved
4935 ,sum(nvl(f1.func_forecasted_delta,0)) revenue_forecasted
4936 ,0 revenue_actual
4937 ,0 cost_forecasted
4938 ,0 cost_actual
4939 ,0 responses_forecasted
4940 ,0 responses_positive
4941 ,0 customers_targeted
4942 ,0 customers_new
4943 ,0 registrations
4944 ,0 cancellations
4945 ,0 attendance
4946 ,0 OPPORTUNITY_AMT_S
4947 ,0 ORDERS_BOOKED_AMT_S
4948 ,0 REVENUE_FORECASTED_S
4949 ,0 REVENUE_ACTUAL_S
4950 ,0 COST_FORECASTED_S
4951 ,0 COST_ACTUAL_S
4952 ,0 BUDGET_REQUESTED_S
4953 ,0 BUDGET_APPROVED_S
4954 ,0 conversion_rate_s
4955 ,0 metric1
4956 ,0 metric2
4957 FROM bim_i_source_codes a
4958 ,ams_act_metric_hst f1
4959 ,ams_metrics_all_b g1
4960 WHERE f1.arc_act_metric_used_by = a.object_type
4961 AND f1.act_metric_used_by_id = a.object_id
4962 AND a.object_type NOT IN ('RCAM')
4963 AND a.child_object_id = 0
4964 AND g1.metric_category = 902
4965 AND g1.metric_id = f1.metric_id
4966 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
4967 GROUP BY
4968 a.source_code_id
4969 ,a.object_type
4970 ,a.object_id
4971 ,a.child_object_type
4972 ,a.child_object_id
4973 ,a.object_country
4974 ,a.object_region
4975 ,a.child_object_country
4976 ,a.child_object_region
4977 ,a.category_id
4978 ,a.object_status
4979 ,a.child_object_status
4980 ,a.object_purpose
4981 ,a.child_object_purpose
4982 ,a.activity_type
4983 ,a.activity_id
4984 ,a.start_date
4985 ,a.end_date
4986 ,a.business_unit_id
4987 ,nvl(f1.functional_currency_code,'USD')
4988 HAVING sum(nvl(f1.func_forecasted_delta,0)) <> 0
4989 UNION ALL
4990 SELECT /*+ USE_NL(F1 G1 A) ordered */
4991 trunc(a.start_date) transaction_create_date
4992 ,'FREV' metric_type
4993 ,a.source_code_id source_code_id
4994 ,a.object_type object_type
4995 ,a.object_id object_id
4996 ,a.child_object_type child_object_type
4997 ,a.child_object_id child_object_id
4998 ,0 lead_rank_id
4999 ,a.object_country
5000 ,a.object_region
5001 ,a.child_object_country
5002 ,a.child_object_region
5003 ,a.category_id
5004 ,a.business_unit_id business_unit_id
5005 ,a.start_date
5006 ,a.end_date
5007 ,a.object_status object_status
5008 ,a.child_object_status child_object_status
5009 ,a.object_purpose object_purpose
5010 ,a.child_object_purpose child_object_purpose
5011 ,a.activity_type activity_type
5012 ,a.activity_id activity_id
5013 ,0 conversion_rate
5014 ,nvl(f1.functional_currency_code,'USD') from_currency
5015 ,0 leads
5016 ,0 opportunities
5017 ,0 opportunity_amt
5018 ,0 opportunities_open
5019 ,0 quotes
5020 ,0 quotes_open
5021 ,0 orders_booked
5022 ,0 orders_booked_amt
5023 ,0 budget_requested
5024 ,0 budget_approved
5025 ,sum(nvl(f1.func_forecasted_delta,0)) revenue_forecasted
5026 ,0 revenue_actual
5027 ,0 cost_forecasted
5028 ,0 cost_actual
5029 ,0 responses_forecasted
5030 ,0 responses_positive
5031 ,0 customers_targeted
5032 ,0 customers_new
5033 ,0 registrations
5034 ,0 cancellations
5035 ,0 attendance
5036 ,0 OPPORTUNITY_AMT_S
5037 ,0 ORDERS_BOOKED_AMT_S
5038 ,0 REVENUE_FORECASTED_S
5039 ,0 REVENUE_ACTUAL_S
5040 ,0 COST_FORECASTED_S
5041 ,0 COST_ACTUAL_S
5042 ,0 BUDGET_REQUESTED_S
5043 ,0 BUDGET_APPROVED_S
5044 ,0 conversion_rate_s
5045 ,0 metric1
5046 ,0 metric2
5047 FROM bim_i_source_codes a
5048 ,ams_act_metric_hst f1
5049 ,ams_metrics_all_b g1
5050 WHERE
5051 f1.arc_act_metric_used_by in ('CSCH','EVEO')
5052 AND f1.act_metric_used_by_id = a.child_object_id
5053 AND f1.ARC_ACT_METRIC_USED_BY = a.child_object_type
5054 AND a.object_type NOT IN ('RCAM')
5055 AND g1.metric_category = 902
5056 AND g1.metric_id = f1.metric_id
5057 AND g1.metric_calculation_type IN ('MANUAL','FUNCTION')
5058 GROUP BY
5059 a.source_code_id
5060 ,a.object_type
5061 ,a.object_id
5062 ,a.child_object_type
5063 ,a.child_object_id
5064 ,a.object_country
5065 ,a.object_region
5066 ,a.child_object_country
5067 ,a.child_object_region
5068 ,a.category_id
5069 ,a.object_status
5070 ,a.child_object_status
5071 ,a.object_purpose
5072 ,a.child_object_purpose
5073 ,a.activity_type
5074 ,a.activity_id
5075 ,a.start_date
5076 ,a.end_date
5077 ,a.business_unit_id
5078 ,nvl(f1.functional_currency_code,'USD')
5079 HAVING sum(nvl(f1.func_forecasted_delta,0)) <> 0
5080 ) inner
5081 WHERE NOT EXISTS
5082 (SELECT source_code_id from bim_i_marketing_facts facts
5083 WHERE facts.object_id = inner.object_id
5084 AND facts.object_type = inner.object_type
5085 AND facts.source_code_id = inner.source_code_id
5086 AND facts.metric_type = inner.metric_type
5087 AND facts.child_object_type = inner.child_object_type
5088 AND facts.child_object_id = inner.child_object_id)
5089 );
5090 /* WHEN MATCHED THEN UPDATE SET
5091 facts.last_update_date = changes.last_update_date
5092 WHEN NOT MATCHED THEN INSERT
5093 (
5094 facts.creation_date
5095 ,facts.last_update_date
5096 ,facts.created_by
5097 ,facts.last_updated_by
5098 ,facts.last_update_login
5099 ,facts.metric_type
5100 ,facts.lead_id
5101 ,facts.transaction_create_date
5102 ,facts.source_code_id
5103 ,facts.object_type
5104 ,facts.object_id
5105 ,facts.child_object_type
5106 ,facts.child_object_id
5107 ,facts.lead_rank_id
5108 ,facts.object_country
5109 ,facts.object_region
5110 ,facts.child_object_country
5111 ,facts.child_object_region
5112 ,facts.category_id
5113 ,facts.business_unit_id
5114 ,facts.start_date
5115 ,facts.end_date
5116 ,facts.object_status
5117 ,facts.child_object_status
5118 ,facts.object_purpose
5119 ,facts.child_object_purpose
5120 ,facts.activity_type
5121 ,facts.activity_id
5122 ,facts.conversion_rate
5123 ,facts.from_currency
5124 ,facts.leads
5125 ,facts.opportunities
5126 ,facts.opportunity_amt
5127 ,facts.opportunities_open
5128 ,facts.orders_booked
5129 ,facts.orders_booked_amt
5130 ,facts.revenue_forecasted
5131 ,facts.revenue_actual
5132 ,facts.cost_forecasted
5133 ,facts.cost_actual
5134 ,facts.budget_approved
5135 ,facts.budget_requested
5136 ,facts.responses_forecasted
5137 ,facts.responses_positive
5138 ,facts.customers_new
5139 ,facts.registrations
5140 ,facts.cancellations
5141 ,facts.attendance
5142 ,facts.OPPORTUNITY_AMT_S OPPORTUNITY_AMT_S
5143 ,facts.ORDERS_BOOKED_AMT_S ORDERS_BOOKED_AMT_S
5144 ,facts.REVENUE_FORECASTED_S REVENUE_FORECASTED_S
5145 ,facts.REVENUE_ACTUAL_S REVENUE_ACTUAL_S
5146 ,facts.COST_FORECASTED_S COST_FORECASTED_S
5147 ,facts.COST_ACTUAL_S COST_ACTUAL_S
5148 ,facts.BUDGET_REQUESTED_S BUDGET_REQUESTED_S
5149 ,facts.BUDGET_APPROVED_S BUDGET_APPROVED_S
5150 ,facts.CONVERSION_RATE_S CONVERSION_RATE_S
5151 )
5152 VALUES
5153 (
5154 changes.creation_date
5155 ,changes.last_update_date
5156 ,changes.created_by
5157 ,changes.last_updated_by
5158 ,changes.last_update_login
5159 ,changes.metric_type
5160 ,changes.lead_id
5161 ,changes.transaction_create_date
5162 ,changes.source_code_id
5163 ,changes.object_type
5164 ,changes.object_id
5165 ,changes.child_object_type
5166 ,changes.child_object_id
5167 ,changes.lead_rank_id
5168 ,changes.object_country
5169 ,changes.object_region
5170 ,changes.child_object_country
5171 ,changes.child_object_region
5172 ,nvl(changes.category_id,-1)
5173 ,changes.business_unit_id
5174 ,changes.start_date
5175 ,changes.end_date
5176 ,changes.object_status
5177 ,changes.child_object_status
5178 ,changes.object_purpose
5179 ,changes.child_object_purpose
5180 ,changes.activity_type
5181 ,changes.activity_id
5182 ,changes.conversion_rate
5183 ,changes.from_currency
5184 ,changes.leads
5185 ,changes.opportunities
5186 ,changes.opportunity_amt
5187 ,changes.opportunities_open
5188 ,changes.orders_booked
5189 ,changes.orders_booked_amt
5190 ,changes.revenue_forecasted
5191 ,changes.revenue_actual
5192 ,changes.cost_forecasted
5193 ,changes.cost_actual
5194 ,changes.budget_approved
5195 ,changes.budget_requested
5196 ,changes.responses_forecasted
5197 ,changes.responses_positive
5198 ,changes.customers_new
5199 ,changes.registrations
5200 ,changes.cancellations
5201 ,changes.attendance
5202 ,changes.OPPORTUNITY_AMT_S OPPORTUNITY_AMT_S
5203 ,changes.ORDERS_BOOKED_AMT_S ORDERS_BOOKED_AMT_S
5204 ,changes.REVENUE_FORECASTED_S REVENUE_FORECASTED_S
5205 ,changes.REVENUE_ACTUAL_S REVENUE_ACTUAL_S
5206 ,changes.COST_FORECASTED_S COST_FORECASTED_S
5207 ,changes.COST_ACTUAL_S COST_ACTUAL_S
5208 ,changes.BUDGET_REQUESTED_S BUDGET_REQUESTED_S
5209 ,changes.BUDGET_APPROVED_S BUDGET_APPROVED_S
5210 ,changes.CONVERSION_RATE_S CONVERSION_RATE_S
5211 ); */
5212
5213 -----------------------NEW CODE------------------------------
5214
5215 --dbms_output.put_line('inserted :'||SQL%ROWCOUNT);
5216 COMMIT;
5217
5218 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Third insert BIM_I_MARKETING_FACTS_STG');
5219 bis_collection_utilities.get_last_refresh_dates('BIM_SOURCE_CODE' ,l_conc_start_date,l_conc_end_date,l_sc_s_date,l_sc_e_date);
5220
5221 INSERT INTO BIM_I_MARKETING_FACTS_STG CDF (
5222 --MKT_DAILY_TRANSACTION_ID ,
5223 CREATION_DATE ,
5224 LAST_UPDATE_DATE ,
5225 CREATED_BY ,
5226 LAST_UPDATED_BY ,
5227 LAST_UPDATE_LOGIN ,
5228 TRANSACTION_CREATE_DATE ,
5229 SOURCE_CODE_ID ,
5230 OBJECT_TYPE ,
5231 OBJECT_ID ,
5232 CHILD_OBJECT_TYPE ,
5233 CHILD_OBJECT_ID ,
5234 LEAD_RANK_ID ,
5235 OBJECT_COUNTRY ,
5236 OBJECT_REGION ,
5237 CHILD_OBJECT_COUNTRY ,
5238 CHILD_OBJECT_REGION ,
5239 CATEGORY_ID ,
5240 BUSINESS_UNIT_ID ,
5241 START_DATE ,
5242 END_DATE ,
5243 OBJECT_STATUS ,
5244 CHILD_OBJECT_STATUS ,
5245 OBJECT_PURPOSE ,
5246 CHILD_OBJECT_PURPOSE ,
5247 ACTIVITY_TYPE ,
5248 ACTIVITY_ID ,
5249 from_currency ,
5250 LEADS,
5251 OPPORTUNITIES ,
5252 OPPORTUNITY_AMT ,
5253 OPPORTUNITIES_OPEN ,
5254 ORDERS_BOOKED ,
5255 ORDERS_BOOKED_AMT ,
5256 REVENUE_FORECASTED ,
5257 REVENUE_ACTUAL ,
5258 COST_FORECASTED ,
5259 COST_ACTUAL ,
5260 BUDGET_APPROVED ,
5261 BUDGET_REQUESTED ,
5262 RESPONSES_FORECASTED ,
5263 RESPONSES_POSITIVE ,
5264 CUSTOMERS_NEW ,
5265 REGISTRATIONS ,
5266 CANCELLATIONS ,
5267 ATTENDANCE ,
5268 OPPORTUNITY_AMT_S ,
5269 ORDERS_BOOKED_AMT_S ,
5270 REVENUE_FORECASTED_S ,
5271 REVENUE_ACTUAL_S ,
5272 COST_FORECASTED_S ,
5273 COST_ACTUAL_S ,
5274 BUDGET_REQUESTED_S ,
5275 BUDGET_APPROVED_S ,
5276 CONVERSION_RATE_S ,
5277 METRIC1 ,
5278 METRIC2
5279 )
5280 SELECT sysdate
5281 ,sysdate
5282 ,-1
5283 ,-1
5284 ,-1
5285 ,TRUNC(a.start_date)
5286 ,a.source_code_id
5287 ,a.object_type
5288 ,a.object_id
5289 ,a.child_object_type
5290 ,a.child_object_id
5291 ,0
5292 ,a.object_country
5293 ,a.object_region
5294 ,a.child_object_country
5295 ,a.child_object_region
5296 ,nvl(a.category_id,-1)
5297 ,a.business_unit_id
5298 ,a.start_date
5299 ,a.end_date
5300 ,a.object_status
5301 ,a.child_object_status
5302 ,a.object_purpose
5303 ,a.child_object_purpose
5304 ,a.activity_type
5305 ,a.activity_id
5306 ,null
5307 ,0
5308 ,0
5309 ,0
5310 ,0
5311 ,0
5312 ,0
5313 ,0
5314 ,0
5315 ,0
5316 ,0
5317 ,0
5318 ,0
5319 ,0
5320 ,0
5321 ,0
5322 ,0
5323 ,0
5324 ,0
5325 ,0
5326 ,0
5327 ,0
5328 ,0
5329 ,0
5330 ,0
5331 ,0
5332 ,0
5333 ,0
5334 ,0
5335 ,0
5336 FROM bim_i_source_codes a
5337 where a.child_object_id =0
5338 and a.obj_last_update_date >l_sc_s_date
5339 and not exists (
5340 select b.object_id,b.object_type from
5341 bim_i_marketing_facts b
5342 where b.child_object_id =0
5343 and a.object_id = b.object_id
5344 AND a.object_type NOT IN ('RCAM')
5345 and a.object_type = b.object_type);
5346
5347 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Inserting into BIM_I_MKT_RATES');
5348 --insert into bim_i_mkt_rates
5349 INSERT
5350 INTO BIM_I_MKT_RATES MRT(tc_code,
5351 trx_date,
5352 prim_conversion_rate,
5353 sec_conversion_rate)
5354 SELECT from_currency,
5355 transaction_create_date,
5356 FII_CURRENCY.get_rate(from_currency,l_global_currency_code,transaction_create_date,l_pgc_rate_type),
5357 FII_CURRENCY.get_rate(from_currency,l_secondary_currency_code,transaction_create_date,l_sgc_rate_type)
5358 FROM (select distinct from_currency from_currency,
5359 transaction_create_date transaction_create_date
5360 from bim_i_marketing_facts_stg);
5361
5362 l_check_missing_rate := Check_Missing_Rates (p_start_date);
5363 if (l_check_missing_rate = -1) then
5364 DELETE from bim_i_marketing_facts_stg where transaction_create_date>= p_start_date;
5365 commit;
5366 x_return_status := FND_API.G_RET_STS_ERROR;
5367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5368 end if;
5369 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Sub Load:after calling checking_missing_rates');
5370 BIS_COLLECTION_UTILITIES.log('BIM_I_MARKETING_FACTS:Final insert into bim_i_marketing_facts');
5371 INSERT
5372 INTO BIM_I_MARKETING_FACTS CDF (
5373 --MKT_DAILY_TRANSACTION_ID ,
5374 CREATION_DATE ,
5375 LAST_UPDATE_DATE ,
5376 CREATED_BY ,
5377 LAST_UPDATED_BY ,
5378 LAST_UPDATE_LOGIN ,
5379 TRANSACTION_CREATE_DATE ,
5380 LEAD_ID ,
5381 METRIC_TYPE ,
5382 SOURCE_CODE_ID ,
5383 OBJECT_TYPE ,
5384 OBJECT_ID ,
5385 CHILD_OBJECT_TYPE ,
5386 CHILD_OBJECT_ID ,
5387 LEAD_RANK_ID ,
5388 OBJECT_COUNTRY ,
5389 OBJECT_REGION ,
5390 CHILD_OBJECT_COUNTRY ,
5391 CHILD_OBJECT_REGION ,
5392 CATEGORY_ID ,
5393 BUSINESS_UNIT_ID ,
5394 START_DATE ,
5395 END_DATE ,
5396 OBJECT_STATUS ,
5397 CHILD_OBJECT_STATUS ,
5398 OBJECT_PURPOSE ,
5399 CHILD_OBJECT_PURPOSE ,
5400 ACTIVITY_TYPE ,
5401 ACTIVITY_ID ,
5402 CONVERSION_RATE ,
5403 FROM_CURRENCY ,
5404 LEADS ,
5405 OPPORTUNITIES ,
5406 OPPORTUNITY_AMT ,
5407 OPPORTUNITIES_OPEN ,
5408 ORDERS_BOOKED ,
5409 ORDERS_BOOKED_AMT ,
5410 REVENUE_FORECASTED ,
5411 REVENUE_ACTUAL ,
5412 COST_FORECASTED ,
5413 COST_ACTUAL ,
5414 BUDGET_APPROVED ,
5415 BUDGET_REQUESTED ,
5416 RESPONSES_FORECASTED ,
5417 RESPONSES_POSITIVE ,
5418 CUSTOMERS_TARGETED ,
5419 CUSTOMERS_NEW ,
5420 REGISTRATIONS ,
5421 CANCELLATIONS ,
5422 ATTENDANCE ,
5423 OPPORTUNITY_AMT_S ,
5424 ORDERS_BOOKED_AMT_S ,
5425 REVENUE_FORECASTED_S ,
5426 REVENUE_ACTUAL_S ,
5427 COST_FORECASTED_S ,
5428 COST_ACTUAL_S ,
5429 BUDGET_REQUESTED_S ,
5430 BUDGET_APPROVED_S ,
5431 CONVERSION_RATE_S ,
5432 metric1 ,
5433 metric2
5434 )
5435 SELECT /*+ parallel */
5436 -- BIM_I_MARKETING_FACTS_s.nextval ,
5437 sysdate
5438 ,sysdate
5439 ,-1
5440 ,-1
5441 ,-1
5442 ,transaction_create_date
5443 ,lead_id
5444 ,metric_type
5445 ,source_code_id
5446 ,object_type
5447 ,object_id
5448 ,child_object_type
5449 ,child_object_id
5450 ,lead_rank_id
5451 ,object_country
5452 ,object_region
5453 ,child_object_country
5454 ,child_object_region
5455 ,nvl(category_id,-1)
5456 ,business_unit_id
5457 ,start_date
5458 ,end_date
5459 ,object_status
5460 ,child_object_status
5461 ,object_purpose
5462 ,child_object_purpose
5463 ,activity_type
5464 ,activity_id
5465 ,conversion_rate
5466 ,from_currency
5467 ,leads
5468 ,opportunities
5469 ,opportunity_amt*rt.prim_conversion_rate
5470 ,opportunities_open
5471 ,orders_booked
5472 ,orders_booked_amt*rt.prim_conversion_rate
5473 ,revenue_forecasted*rt.prim_conversion_rate
5474 ,revenue_actual*rt.prim_conversion_rate
5475 ,cost_forecasted*rt.prim_conversion_rate
5476 ,cost_actual*rt.prim_conversion_rate
5477 ,budget_approved*rt.prim_conversion_rate
5478 ,budget_requested*rt.prim_conversion_rate
5479 ,responses_forecasted
5480 ,responses_positive
5481 ,customers_targeted
5482 ,customers_new
5483 ,registrations
5484 ,cancellations
5485 ,attendance
5486 ,OPPORTUNITY_AMT*sec_conversion_rate
5487 ,ORDERS_BOOKED_AMT*sec_conversion_rate
5488 ,REVENUE_FORECASTED*sec_conversion_rate
5489 ,REVENUE_ACTUAL*sec_conversion_rate
5490 ,COST_FORECASTED*sec_conversion_rate
5491 ,COST_ACTUAL*sec_conversion_rate
5492 ,BUDGET_REQUESTED*sec_conversion_rate
5493 ,BUDGET_APPROVED*sec_conversion_rate
5494 ,CONVERSION_RATE_S
5495 ,metric1*rt.prim_conversion_rate
5496 ,metric1*sec_conversion_rate
5497 FROM bim_i_marketing_facts_stg a, bim_i_mkt_rates rt
5498 where a.from_currency = rt.tc_code(+)
5499 and a.transaction_create_date= rt.trx_date(+);
5500
5501 --Insert schedules
5502 INSERT INTO BIM_I_MARKETING_FACTS CDF (
5503 --MKT_DAILY_TRANSACTION_ID ,
5504 CREATION_DATE ,
5505 LAST_UPDATE_DATE ,
5506 CREATED_BY ,
5507 LAST_UPDATED_BY ,
5508 LAST_UPDATE_LOGIN ,
5509 TRANSACTION_CREATE_DATE ,
5510 SOURCE_CODE_ID ,
5511 OBJECT_TYPE ,
5512 OBJECT_ID ,
5513 CHILD_OBJECT_TYPE ,
5514 CHILD_OBJECT_ID ,
5515 LEAD_RANK_ID ,
5516 OBJECT_COUNTRY ,
5517 OBJECT_REGION ,
5518 CHILD_OBJECT_COUNTRY ,
5519 CHILD_OBJECT_REGION ,
5520 CATEGORY_ID ,
5521 BUSINESS_UNIT_ID ,
5522 START_DATE ,
5523 END_DATE ,
5524 OBJECT_STATUS ,
5525 CHILD_OBJECT_STATUS ,
5526 OBJECT_PURPOSE ,
5527 CHILD_OBJECT_PURPOSE ,
5528 ACTIVITY_TYPE ,
5529 ACTIVITY_ID ,
5530 LEADS,
5531 OPPORTUNITIES ,
5532 OPPORTUNITY_AMT ,
5533 OPPORTUNITIES_OPEN ,
5534 ORDERS_BOOKED ,
5535 ORDERS_BOOKED_AMT ,
5536 REVENUE_FORECASTED ,
5537 REVENUE_ACTUAL ,
5538 COST_FORECASTED ,
5539 COST_ACTUAL ,
5540 BUDGET_APPROVED ,
5541 BUDGET_REQUESTED ,
5542 RESPONSES_FORECASTED ,
5543 RESPONSES_POSITIVE ,
5544 CUSTOMERS_NEW ,
5545 REGISTRATIONS ,
5546 CANCELLATIONS ,
5547 ATTENDANCE ,
5548 OPPORTUNITY_AMT_S ,
5549 ORDERS_BOOKED_AMT_S ,
5550 REVENUE_FORECASTED_S ,
5551 REVENUE_ACTUAL_S ,
5552 COST_FORECASTED_S ,
5553 COST_ACTUAL_S ,
5554 BUDGET_REQUESTED_S ,
5555 BUDGET_APPROVED_S ,
5556 CONVERSION_RATE_S ,
5557 metric1 ,
5558 metric2)
5559 SELECT sysdate
5560 ,sysdate
5561 ,-1
5562 ,-1
5563 ,-1
5564 ,TRUNC(a.start_date)
5565 ,a.source_code_id
5566 ,a.object_type
5567 ,a.object_id
5568 ,a.child_object_type
5569 ,a.child_object_id
5570 ,0
5571 ,a.object_country
5572 ,a.object_region
5573 ,a.child_object_country
5574 ,a.child_object_region
5575 ,nvl(a.category_id,-1)
5576 ,a.business_unit_id
5577 ,a.start_date
5578 ,a.end_date
5579 ,a.object_status
5580 ,a.child_object_status
5581 ,a.object_purpose
5582 ,a.child_object_purpose
5583 ,a.activity_type
5584 ,a.activity_id
5585 ,0
5586 ,0
5587 ,0
5588 ,0
5589 ,0
5590 ,0
5591 ,0
5592 ,0
5593 ,0
5594 ,0
5595 ,0
5596 ,0
5597 ,0
5598 ,0
5599 ,0
5600 ,0
5601 ,0
5602 ,0
5603 ,0
5604 ,0
5605 ,0
5606 ,0
5607 ,0
5608 ,0
5609 ,0
5610 ,0
5611 ,null
5612 ,0
5613 ,0
5614 FROM bim_i_source_codes a
5615 where a.child_object_id >0
5616 and a.obj_last_update_date >l_sc_s_date
5617 and not exists (
5618 select b.child_object_id,b.child_object_type from
5619 bim_i_marketing_facts b
5620 where b.child_object_id >0
5621 and a.child_object_id = b.child_object_id
5622 AND a.object_type NOT IN ('RCAM')
5623 and a.child_object_type = b.child_object_type);
5624
5625 -- Analyze the daily facts table
5626 DBMS_STATS.gather_table_stats('BIM','BIM_I_MARKETING_FACTS', estimate_percent => 5,
5627 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
5628
5629 --dbms_output.put_line('after analyze log'||sqlerrm(sqlcode));
5630 /***************************************************************/
5631 --dbms_output.put_line('b4 inserting log');
5632 BIS_COLLECTION_UTILITIES.log('Before Insert into log.');
5633 BEGIN
5634 IF (Not BIS_COLLECTION_UTILITIES.setup('MARKETING_FACTS')) THEN
5635 RAISE FND_API.G_EXC_ERROR;
5636 return;
5637 END IF;
5638 BIS_COLLECTION_UTILITIES.WRAPUP(
5639 p_status => TRUE ,
5640 p_period_from =>p_start_date,
5641 p_period_to => sysdate--p_end_date
5642 );
5643 Exception when others then
5644 Rollback;
5645 BIS_COLLECTION_UTILITIES.WRAPUP(
5646 p_status => FALSE,
5647 p_period_from =>p_start_date,
5648 p_period_to =>sysdate-- p_end_date
5649 );
5650 RAISE FND_API.G_EXC_ERROR;
5651 END;
5652 BIS_COLLECTION_UTILITIES.log('After Insert into log.');
5653 EXCEPTION
5654 WHEN FND_API.G_EXC_ERROR THEN
5655 x_return_status := FND_API.G_RET_STS_ERROR;
5656 -- Standard call to get message count and if count=1, get the message
5657 FND_msg_PUB.Count_And_Get (
5658 -- p_encoded => FND_API.G_FALSE,
5659 p_count => x_msg_count,
5660 p_data => x_msg_data
5661 );
5662
5663 ams_utility_pvt.write_conc_log('SUBSEQUENT_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
5664 BIS_COLLECTION_UTILITIES.log('SUBSEQUENT_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
5665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5666
5667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5668
5669 -- Standard call to get message count and if count=1, get the message
5670 FND_msg_PUB.Count_And_Get (
5671 --p_encoded => FND_API.G_FALSE,
5672 p_count => x_msg_count,
5673 p_data => x_msg_data
5674 );
5675
5676 ams_utility_pvt.write_conc_log('BIM_I_MARKETING_FACTS:SUBSEQUENT_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
5677 BIS_COLLECTION_UTILITIES.log('SUBSEQUENT_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
5678 WHEN OTHERS THEN
5679
5680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5681
5682 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
5683 THEN
5684 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
5685 END IF;
5686
5687 -- Standard call to get message count and if count=1, get the message
5688 FND_msg_PUB.Count_And_Get (
5689 -- p_encoded => FND_API.G_FALSE,
5690 p_count => x_msg_count,
5691 p_data => x_msg_data
5692 );
5693 ams_utility_pvt.write_conc_log('BIM_I_MARKETING_FACTS:SUBSEQUENT_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
5694 BIS_COLLECTION_UTILITIES.log('SUBSEQUENT_LOAD:IN other EXCEPTION '||sqlerrm(sqlcode));
5695 END SUB_LOAD;
5696 END BIM_MARKET_FACTS_PKG;