[Home] [Help]
PACKAGE BODY: APPS.BIM_KPI_FACT
Source
1 PACKAGE BODY BIM_KPI_FACT AS
2 /* $Header: bimkpifb.pls 120.0 2005/05/31 13:17:49 appldev noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(20) :='BIM_KPI_FACT';
5 G_FILE_NAME CONSTANT VARCHAR2(20) :='bimkpifb.pls';
6
7 ---------------------------------------------------------------------
8 -- FUNCTION
9 -- calculate_days
10 -- NOTE
11 -- PARAMETER
12 -- p_start_date IN DATE,
13 -- p_end_date IN DATE,
14 -- p_aggregate IN VARCHAR2
15 -- p_period IN VARCHAR2
16 -- RETURN NUMBER
17 ---------------------------------------------------------------------
18 FUNCTION calculate_days(
19 p_start_date DATE
20 ,p_end_date DATE
21 ,p_aggregate VARCHAR2
22 ,p_period VARCHAR2) return NUMBER
23 IS
24
25 l_date DATE;
26 l_days number;
27 l_day_code VARCHAR2(30);
28 l_week_code VARCHAR2(30);
29 l_month_code VARCHAR2(30);
30 l_quarter_code VARCHAR2(30);
31 l_year_code VARCHAR2(30);
32 l_cur_period_start_date date;
33 l_cur_period_end_date date;
34 l_prev_period_start_date date;
35 l_prev_period_end_date date;
36 l_period_start_date date;
37 l_period_end_date date;
38 l_temp_start_date date;
39 l_temp_end_date date;
40 l_org_id number;
41
42 BEGIN
43
44 l_day_code := 'DAY';
45 l_week_code := 'WEEK';
46 l_month_code := 'MONTH';
47 l_quarter_code := 'QUARTER';
48 l_year_code := 'YEAR';
49
50 l_date := sysdate - 1;
51 l_org_id := 204;
52
53 IF (p_aggregate = l_day_code) THEN
54 IF (p_aggregate = 'Current') THEN
55 IF (p_end_date >= l_date) THEN
56 RETURN 1;
57 ELSE
58 RETURN 0;
59 END IF;
60 ELSE
61 IF (p_end_date >= l_date -1) THEN
62 RETURN 1;
63 ELSE
64 RETURN 0;
65 END IF;
66 END IF;
67 END IF;
68
69 IF (p_aggregate = l_month_code) THEN
70 l_cur_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_START(l_date, l_org_id);
71 l_cur_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_END(l_date, l_org_id);
72 IF (p_period = 'Previous') THEN
73 l_prev_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_START(l_cur_period_start_date - 1, l_org_id);
74 l_prev_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_END(l_cur_period_start_date - 1, l_org_id);
75 END IF;
76 ELSIF (p_aggregate = l_quarter_code) THEN
77 l_cur_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_START(l_date, l_org_id);
78 l_cur_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_END(l_date, l_org_id);
79 IF (p_period = 'Previous') THEN
80 l_prev_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_START(l_cur_period_start_date - 1, l_org_id);
81 l_prev_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_END(l_cur_period_start_date - 1, l_org_id);
82 END IF;
83 ELSIF (p_aggregate = l_year_code) THEN
84 l_cur_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_START(l_date, l_org_id);
85 l_cur_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_END(l_date, l_org_id);
86 IF (p_period = 'Previous') THEN
87 l_prev_period_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_START(l_cur_period_start_date - 1, l_org_id);
88 l_prev_period_end_date := BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_END(l_cur_period_start_date - 1, l_org_id);
89 END IF;
90 ELSIF (p_aggregate = l_week_code) THEN
91 select next_day(l_date-7, TO_NUMBER(to_char(to_date('01/09/2004', 'DD/MM/RRRR'), 'DD'))) into l_cur_period_start_date from dual;
92 select next_day(l_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) into l_cur_period_end_date from dual;
93 IF (p_period = 'Previous') THEN
94 select next_day(l_date-14, TO_NUMBER(to_char(to_date('01/09/2004', 'DD/MM/RRRR'), 'DD'))) into l_prev_period_start_date from dual;
95 select next_day(l_prev_period_start_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) into l_prev_period_end_date from dual;
96 END IF;
97 END IF;
98 IF (p_period = 'Previous') THEN
99 l_days := trunc(l_date) - trunc(l_cur_period_start_date);
100 IF trunc(l_cur_period_end_date) = trunc(l_date) THEN
101 l_period_end_date := l_prev_period_end_date;
102 ELSE
103 l_period_end_date := l_prev_period_start_date + l_days;
104 END IF;
105 l_period_start_date := l_prev_period_start_date;
106 ELSE
107 l_period_start_date := l_cur_period_start_date;
108 l_period_end_date := l_date;
109 END IF;
110 --dbms_output.put_line('period_start_date -- ' || l_period_start_date);
111 --dbms_output.put_line('period_end_date -- ' || l_period_end_date);
112
113 --dbms_output.put_line('p_start_date -- ' || p_start_date);
114 --dbms_output.put_line('p_end_date -- ' || p_end_date);
115
116 l_days := 0;
117 IF (p_start_date > l_period_end_date) THEN
118 l_days := 0;
119 ELSIF (p_end_date < l_period_start_date) THEN
120 l_days := 0;
121 ELSIF (p_start_date <= l_period_start_date) THEN
122 l_temp_start_date := l_period_start_date;
123 IF(p_end_date >= l_period_end_date) THEN
124 l_temp_end_date := l_period_end_date;
125 ELSE
126 l_temp_end_date := p_end_date;
127 END IF;
128 l_days := trunc(l_temp_end_date) - trunc(l_temp_start_date) + 1;
129 ELSIF (p_start_date > l_period_start_date) THEN
130 l_temp_start_date := p_start_date;
131 IF(p_end_date >= l_period_end_date) THEN
132 l_temp_end_date := l_period_end_date;
133 ELSE
134 l_temp_end_date := p_end_date;
135 END IF;
136 l_days := trunc(l_temp_end_date) - trunc(l_temp_start_date) + 1;
137 END IF;
138
139 --dbms_output.put_line('l_days -- ' || l_days);
140
141 RETURN (l_days);
142 END calculate_days;
143
144 -----------------------------------------------------------------------
145 -- PROCEDURE
146 -- POPULATE
147 --
148 -----------------------------------------------------------------------
149
150 PROCEDURE POPULATE
151 (ERRBUF OUT NOCOPY VARCHAR2,
152 RETCODE OUT NOCOPY NUMBER
153 ) IS
154
155 l_org_id NUMBER;
156 l_days NUMBER;
157 l_date DATE;
158 l_day_code VARCHAR2(30);
159 l_week_code VARCHAR2(30);
160 l_month_code VARCHAR2(30);
161 l_quarter_code VARCHAR2(30);
162 l_year_code VARCHAR2(30);
163 l_cur_year_start_date DATE;
164 l_cur_year_end_date DATE;
165 l_pre_year_start_date DATE;
166 l_pre_year_end_date DATE;
167 l_cur_qtr_start_date DATE;
168 l_cur_qtr_end_date DATE;
169 l_pre_qtr_start_date DATE;
170 l_pre_qtr_end_date DATE;
171 l_cur_month_start_date DATE;
172 l_cur_month_end_date DATE;
173 l_pre_month_start_date DATE;
174 l_pre_month_end_date DATE;
175 l_cur_week_start_date DATE;
176 l_cur_week_end_date DATE;
177 l_pre_week_start_date DATE;
178 l_pre_week_end_date DATE;
179 l_status VARCHAR2(5);
180 l_industry VARCHAR2(5);
181 l_schema VARCHAR2(30);
182 l_return BOOLEAN;
183
184 BEGIN
185
186 ERRBUF :='SUCCESS';
187 RETCODE := 0;
188
189 l_day_code := 'DAY';
190 l_week_code := 'WEEK';
191 l_month_code := 'MONTH';
192 l_quarter_code := 'QUARTER';
193 l_year_code := 'YEAR';
194
195 ams_utility_pvt.write_conc_log('BIM_R_KPI_FACT: POPULATE START');
196 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
197 l_date := sysdate - 1;
198 l_org_id := 204;
199 l_cur_year_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_YEAR_START(l_date, l_org_id);
200 l_cur_year_end_date := l_date;
201 l_pre_year_start_date := BIM_SET_OF_BOOKS.GET_PRE_FISCAL_YEAR_START(l_date, l_org_id);
202 l_pre_year_end_date := BIM_SET_OF_BOOKS.GET_PRE_FISCAL_YEAR_END(l_date, l_org_id);
203 l_cur_qtr_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_QTR_START(l_date, l_org_id);
204 l_cur_qtr_end_date := l_date;
205 l_pre_qtr_start_date := BIM_SET_OF_BOOKS.GET_PRE_FISCAL_QTR_START(l_date, l_org_id);
206 l_pre_qtr_end_date := BIM_SET_OF_BOOKS.GET_PRE_FISCAL_QTR_END(l_date, l_org_id);
207 l_cur_month_start_date := BIM_SET_OF_BOOKS.GET_FISCAL_MONTH_START(l_date, l_org_id);
208 l_cur_month_end_date := l_date;
209 l_pre_month_start_date := BIM_SET_OF_BOOKS.GET_PRE_FISCAL_MONTH_START(l_date, l_org_id);
210 l_pre_month_end_date := BIM_SET_OF_BOOKS.GET_PRE_FISCAL_MONTH_END(l_date, l_org_id);
211 select next_day(trunc(l_date)-7, TO_NUMBER(to_char(to_date('01/09/2004', 'DD/MM/RRRR'), 'DD'))) into l_cur_week_start_date from dual;
212 l_cur_week_end_date := trunc(l_date);
213 l_days := l_cur_week_end_date-l_cur_week_start_date ;
214 --l_days := l_days+1;
215 select next_day(trunc(l_date)-14, TO_NUMBER(to_char(to_date('01/09/2004', 'DD/MM/RRRR'), 'DD'))) into l_pre_week_start_date from dual;
216 l_pre_week_end_date := l_pre_week_start_date+l_days;
217
218 ams_utility_pvt.write_conc_log('BIM_R_KPI_FACT: INSERT START');
219
220 ams_utility_pvt.debug_message('POPULATE BIM_R_KPI_FACT START');
221
222 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_r_kpi_facts';
223
224
225 INSERT
226 INTO bim_r_kpi_facts(
227 creation_date,
228 created_by,
229 last_update_date,
230 last_updated_by,
231 last_update_login,
232 object_id,
233 object_type,
234 region,
235 country,
236 business_unit,
237 start_date,
238 end_date,
239 status,
240 period_type,
241 calculation_type,
242 cost_cur_period,
243 cost_pre_period,
244 leads_cur_period,
245 leads_pre_period,
246 res_cur_period,
247 res_pre_period,
248 reg_cur_period,
249 reg_pre_period,
250 rev_cur_period,
251 rev_pre_period,
252 orders_cur_period,
253 orders_pre_period,
254 aleads_cur_period,
255 aleads_pre_period
256 )
257 SELECT
258 sysdate,
259 -1,
260 sysdate,
261 -1,
262 -1,
263 inner.object_id,
264 inner.object_type,
265 inner.region,
266 inner.country,
267 inner.business_unit,
268 inner.start_date,
269 inner.end_date,
270 inner.status,
271 inner.period_type,
272 inner.calculation_type,
273 inner.cost_cur_period,
274 inner.cost_pre_period,
275 inner.leads_cur_period,
276 inner.leads_pre_period,
277 inner.res_cur_period,
278 inner.res_pre_period,
279 inner.reg_cur_period,
280 inner.reg_pre_period,
281 inner.rev_cur_period,
282 inner.rev_pre_period,
283 inner.orders_cur_period,
284 inner.orders_pre_period,
285 inner.aleads_cur_period,
286 inner.aleads_pre_period
287 FROM (
288 SELECT
289 a.campaign_id object_id,
290 a.transaction_create_date,
291 'CAMP' object_type,
292 a.budget_approved budget_approved,
293 a.campaign_region region,
294 a.campaign_country country,
295 a.business_unit_id business_unit,
296 a.start_date start_date,
297 a.end_date end_date,
298 a.campaign_status status,
299 l_year_code period_type,
300 'Cumulative' calculation_type,
301 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
302 0),
303 0,
304 a.transaction_create_date,
305 a.start_date),
306 end_date,
307 l_year_code,
308 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
309 0),
310 0,
311 ((nvl(a.end_date,
312 sysdate) - a.transaction_create_date)+1),
313 ((nvl(a.end_date,
314 sysdate) - a.start_date)+1)),
315 0,
316 1,
317 DECODE(GREATEST(a.start_date - a.transaction_create_date,
318 0),
319 0,
320 ((nvl(a.end_date,
321 sysdate) - a.transaction_create_date)+1),
322 ((nvl(a.end_date,
323 sysdate) - a.start_date)+1))))) cost_cur_period,
324 0 cost_pre_period,
325 0 leads_cur_period,
326 0 leads_pre_period,
327 0 res_cur_period,
328 0 res_pre_period,
329 0 reg_cur_period,
330 0 reg_pre_period,
331 0 rev_cur_period,
332 0 rev_pre_period,
333 0 orders_cur_period,
334 0 orders_pre_period,
335 0 aleads_cur_period,
336 0 aleads_pre_period
337 FROM
338 bim_r_camp_daily_facts a
339 WHERE
340 a.budget_approved > 0
341 GROUP BY a.campaign_id,
342 a.transaction_create_date,
343 a.start_date,
344 a.end_date,
345 a.budget_approved,
346 a.campaign_region,
347 a.campaign_country,
348 a.business_unit_id,
349 a.campaign_status
350 UNION ALL
351 SELECT
352 a.campaign_id object_id,
353 a.transaction_create_date,
354 'CAMP' object_type,
355 a.budget_approved budget_approved,
356 a.campaign_region region,
357 a.campaign_country country,
358 a.business_unit_id business_unit,
359 a.start_date start_date,
360 a.end_date end_date,
361 a.campaign_status status,
362 l_year_code period_type,
363 'Cumulative' calculation_type,
364 0 cost_cur_period,
365 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
366 0),
367 0,
368 a.transaction_create_date,
369 a.start_date),
370 end_date,
371 l_year_code,
372 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
373 0),
374 0,
375 ((nvl(a.end_date,
376 sysdate) - a.transaction_create_date)+1),
377 ((nvl(a.end_date,
378 sysdate) - a.start_date)+1)),
379 0,
380 1,
381 DECODE(GREATEST(a.start_date - a.transaction_create_date,
382 0),
383 0,
384 ((nvl(a.end_date,
385 sysdate) - a.transaction_create_date)+1),
386 ((nvl(a.end_date,
387 sysdate) - a.start_date)+1))))) cost_cur_period,
388 0 leads_cur_period,
389 0 leads_pre_period,
390 0 res_cur_period,
391 0 res_pre_period,
392 0 reg_cur_period,
393 0 reg_pre_period,
394 0 rev_cur_period,
395 0 rev_pre_period,
396 0 orders_cur_period,
397 0 orders_pre_period,
398 0 aleads_cur_period,
399 0 aleads_pre_period
400 FROM
401 bim_r_camp_daily_facts a
402 WHERE
403 a.budget_approved > 0
404 GROUP BY a.campaign_id,
405 a.transaction_create_date,
406 a.start_date,
407 a.end_date,
408 a.budget_approved,
409 a.campaign_region,
410 a.campaign_country,
411 a.business_unit_id,
412 a.campaign_status
413 UNION ALL
414 SELECT
415 a.campaign_id object_id,
416 a.transaction_create_date,
417 'CAMP' object_type,
418 a.budget_approved budget_approved,
419 a.campaign_region region,
420 a.campaign_country country,
421 a.business_unit_id business_unit,
422 a.start_date start_date,
423 a.end_date end_date,
424 a.campaign_status status,
425 l_quarter_code period_type,
426 'Cumulative' calculation_type,
427 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
428 0),
429 0,
430 a.transaction_create_date,
431 a.start_date),
432 end_date,
433 l_quarter_code,
434 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
435 0),
436 0,
440 sysdate) - a.start_date)+1)),
437 ((nvl(a.end_date,
438 sysdate) - a.transaction_create_date)+1),
439 ((nvl(a.end_date,
441 0,
442 1,
443 DECODE(GREATEST(a.start_date - a.transaction_create_date,
444 0),
445 0,
446 ((nvl(a.end_date,
447 sysdate) - a.transaction_create_date)+1),
448 ((nvl(a.end_date,
449 sysdate) - a.start_date)+1))))) cost_cur_period,
450 0 cost_pre_period,
451 0 leads_cur_period,
452 0 leads_pre_period,
453 0 res_cur_period,
454 0 res_pre_period,
455 0 reg_cur_period,
456 0 reg_pre_period,
457 0 rev_cur_period,
458 0 rev_pre_period,
459 0 orders_cur_period,
460 0 orders_pre_period,
461 0 aleads_cur_period,
462 0 aleads_pre_period
463 FROM
464 bim_r_camp_daily_facts a
465 WHERE
466 a.budget_approved > 0
467 GROUP BY a.campaign_id,
468 a.transaction_create_date,
469 a.start_date,
470 a.end_date,
471 a.budget_approved,
472 a.campaign_region,
473 a.campaign_country,
474 a.business_unit_id,
475 a.campaign_status
476 UNION ALL
477 SELECT
478 a.campaign_id object_id,
479 a.transaction_create_date,
480 'CAMP' object_type,
481 a.budget_approved budget_approved,
482 a.campaign_region region,
483 a.campaign_country country,
484 a.business_unit_id business_unit,
485 a.start_date start_date,
486 a.end_date end_date,
487 a.campaign_status status,
488 l_quarter_code period_type,
489 'Cumulative' calculation_type,
490 0 cost_cur_period,
491 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
492 0),
493 0,
494 a.transaction_create_date,
495 a.start_date),
496 end_date,
497 l_quarter_code,
498 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
499 0),
500 0,
501 ((nvl(a.end_date,
502 sysdate) - a.transaction_create_date)+1),
503 ((nvl(a.end_date,
504 sysdate) - a.start_date)+1)),
505 0,
506 1,
507 DECODE(GREATEST(a.start_date - a.transaction_create_date,
508 0),
509 0,
510 ((nvl(a.end_date,
511 sysdate) - a.transaction_create_date)+1),
512 ((nvl(a.end_date,
513 sysdate) - a.start_date)+1))))) cost_cur_period,
514 0 leads_cur_period,
515 0 leads_pre_period,
516 0 res_cur_period,
517 0 res_pre_period,
518 0 reg_cur_period,
519 0 reg_pre_period,
520 0 rev_cur_period,
521 0 rev_pre_period,
522 0 orders_cur_period,
523 0 orders_pre_period,
524 0 aleads_cur_period,
525 0 aleads_pre_period
526 FROM
527 bim_r_camp_daily_facts a
528 WHERE
529 a.budget_approved > 0
530 GROUP BY a.campaign_id,
531 a.transaction_create_date,
532 a.start_date,
533 a.end_date,
534 a.budget_approved,
535 a.campaign_region,
536 a.campaign_country,
537 a.business_unit_id,
538 a.campaign_status
539 UNION ALL
540 SELECT
541 a.campaign_id object_id,
542 a.transaction_create_date,
543 'CAMP' object_type,
544 a.budget_approved budget_approved,
545 a.campaign_region region,
546 a.campaign_country country,
547 a.business_unit_id business_unit,
548 a.start_date start_date,
549 a.end_date end_date,
550 a.campaign_status status,
551 l_month_code period_type,
552 'Cumulative' calculation_type,
553 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
554 0),
555 0,
556 a.transaction_create_date,
557 a.start_date),
558 end_date,
559 l_month_code,
560 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
561 0),
562 0,
563 ((nvl(a.end_date,
564 sysdate) - a.transaction_create_date)+1),
565 ((nvl(a.end_date,
566 sysdate) - a.start_date)+1)),
567 0,
568 1,
569 DECODE(GREATEST(a.start_date - a.transaction_create_date,
570 0),
571 0,
572 ((nvl(a.end_date,
573 sysdate) - a.transaction_create_date)+1),
574 ((nvl(a.end_date,
575 sysdate) - a.start_date)+1))))) cost_cur_period,
576 0 cost_pre_period,
577 0 leads_cur_period,
578 0 leads_pre_period,
579 0 res_cur_period,
580 0 res_pre_period,
581 0 reg_cur_period,
582 0 reg_pre_period,
583 0 rev_cur_period,
584 0 rev_pre_period,
585 0 orders_cur_period,
586 0 orders_pre_period,
587 0 aleads_cur_period,
588 0 aleads_pre_period
589 FROM
590 bim_r_camp_daily_facts a
591 WHERE
592 a.budget_approved > 0
593 GROUP BY a.campaign_id,
594 a.transaction_create_date,
595 a.start_date,
596 a.end_date,
597 a.budget_approved,
601 a.campaign_status
598 a.campaign_region,
599 a.campaign_country,
600 a.business_unit_id,
602 UNION ALL
603 SELECT
604 a.campaign_id object_id,
605 a.transaction_create_date,
606 'CAMP' object_type,
607 a.budget_approved budget_approved,
608 a.campaign_region region,
609 a.campaign_country country,
610 a.business_unit_id business_unit,
611 a.start_date start_date,
612 a.end_date end_date,
613 a.campaign_status status,
614 l_month_code period_type,
615 'Cumulative' calculation_type,
616 0 cost_cur_period,
617 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
618 0),
619 0,
620 a.transaction_create_date,
621 a.start_date),
622 end_date,
623 l_month_code,
624 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
625 0),
626 0,
627 ((nvl(a.end_date,
628 sysdate) - a.transaction_create_date)+1),
629 ((nvl(a.end_date,
630 sysdate) - a.start_date)+1)),
631 0,
632 1,
633 DECODE(GREATEST(a.start_date - a.transaction_create_date,
634 0),
635 0,
636 ((nvl(a.end_date,
637 sysdate) - a.transaction_create_date)+1),
638 ((nvl(a.end_date,
639 sysdate) - a.start_date)+1))))) cost_cur_period,
640 0 leads_cur_period,
641 0 leads_pre_period,
642 0 res_cur_period,
643 0 res_pre_period,
644 0 reg_cur_period,
645 0 reg_pre_period,
646 0 rev_cur_period,
647 0 rev_pre_period,
648 0 orders_cur_period,
649 0 orders_pre_period,
650 0 aleads_cur_period,
651 0 aleads_pre_period
652 FROM
653 bim_r_camp_daily_facts a
654 WHERE
655 a.budget_approved > 0
656 GROUP BY a.campaign_id,
657 a.transaction_create_date,
658 a.start_date,
659 a.end_date,
660 a.budget_approved,
661 a.campaign_region,
662 a.campaign_country,
663 a.business_unit_id,
664 a.campaign_status
665 UNION ALL
666 SELECT
667 a.campaign_id object_id,
668 a.transaction_create_date,
669 'CAMP' object_type,
670 a.budget_approved budget_approved,
671 a.campaign_region region,
672 a.campaign_country country,
673 a.business_unit_id business_unit,
674 a.start_date start_date,
675 a.end_date end_date,
676 a.campaign_status status,
677 l_week_code period_type,
678 'Cumulative' calculation_type,
679 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
680 0),
681 0,
682 a.transaction_create_date,
683 a.start_date),
684 end_date,
685 l_week_code,
686 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
687 0),
688 0,
689 ((nvl(a.end_date,
690 sysdate) - a.transaction_create_date)+1),
691 ((nvl(a.end_date,
692 sysdate) - a.start_date)+1)),
693 0,
694 1,
695 DECODE(GREATEST(a.start_date - a.transaction_create_date,
696 0),
697 0,
698 ((nvl(a.end_date,
699 sysdate) - a.transaction_create_date)+1),
700 ((nvl(a.end_date,
701 sysdate) - a.start_date)+1))))) cost_cur_period,
702 0 cost_pre_period,
703 0 leads_cur_period,
704 0 leads_pre_period,
705 0 res_cur_period,
706 0 res_pre_period,
707 0 reg_cur_period,
708 0 reg_pre_period,
709 0 rev_cur_period,
710 0 rev_pre_period,
711 0 orders_cur_period,
712 0 orders_pre_period,
713 0 aleads_cur_period,
714 0 aleads_pre_period
715 FROM
716 bim_r_camp_daily_facts a
717 WHERE
718 a.budget_approved > 0
719 GROUP BY a.campaign_id,
720 a.transaction_create_date,
721 a.start_date,
722 a.end_date,
723 a.budget_approved,
724 a.campaign_region,
725 a.campaign_country,
726 a.business_unit_id,
727 a.campaign_status
728 UNION ALL
729 SELECT
730 a.campaign_id object_id,
731 a.transaction_create_date,
732 'CAMP' object_type,
733 a.budget_approved budget_approved,
734 a.campaign_region region,
735 a.campaign_country country,
736 a.business_unit_id business_unit,
737 a.start_date start_date,
738 a.end_date end_date,
739 a.campaign_status status,
740 l_week_code period_type,
741 'Cumulative' calculation_type,
742 0 cost_cur_period,
743 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
744 0),
745 0,
746 a.transaction_create_date,
747 a.start_date),
748 end_date,
749 l_week_code,
750 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
751 0),
752 0,
753 ((nvl(a.end_date,
754 sysdate) - a.transaction_create_date)+1),
755 ((nvl(a.end_date,
759 DECODE(GREATEST(a.start_date - a.transaction_create_date,
756 sysdate) - a.start_date)+1)),
757 0,
758 1,
760 0),
761 0,
762 ((nvl(a.end_date,
763 sysdate) - a.transaction_create_date)+1),
764 ((nvl(a.end_date,
765 sysdate) - a.start_date)+1))))) cost_cur_period,
766 0 leads_cur_period,
767 0 leads_pre_period,
768 0 res_cur_period,
769 0 res_pre_period,
770 0 reg_cur_period,
771 0 reg_pre_period,
772 0 rev_cur_period,
773 0 rev_pre_period,
774 0 orders_cur_period,
775 0 orders_pre_period,
776 0 aleads_cur_period,
777 0 aleads_pre_period
778 FROM
779 bim_r_camp_daily_facts a
780 WHERE
781 a.budget_approved > 0
782 GROUP BY a.campaign_id,
783 a.transaction_create_date,
784 a.start_date,
785 a.end_date,
786 a.budget_approved,
787 a.campaign_region,
788 a.campaign_country,
789 a.business_unit_id,
790 a.campaign_status
791 UNION ALL
792 SELECT
793 a.campaign_id object_id,
794 a.transaction_create_date,
795 'CAMP' object_type,
796 a.budget_approved budget_approved,
797 a.campaign_region region,
798 a.campaign_country country,
799 a.business_unit_id business_unit,
800 a.start_date start_date,
801 a.end_date end_date,
802 a.campaign_status status,
803 l_day_code period_type,
804 'Cumulative' calculation_type,
805 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
806 0),
807 0,
808 a.transaction_create_date,
809 a.start_date),
810 end_date,
811 l_day_code,
812 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
813 0),
814 0,
815 ((nvl(a.end_date,
816 sysdate) - a.transaction_create_date)+1),
817 ((nvl(a.end_date,
818 sysdate) - a.start_date)+1)),
819 0,
820 1,
821 DECODE(GREATEST(a.start_date - a.transaction_create_date,
822 0),
823 0,
824 ((nvl(a.end_date,
825 sysdate) - a.transaction_create_date)+1),
826 ((nvl(a.end_date,
827 sysdate) - a.start_date)+1))))) cost_cur_period,
828 0 cost_pre_period,
829 0 leads_cur_period,
830 0 leads_pre_period,
831 0 res_cur_period,
832 0 res_pre_period,
833 0 reg_cur_period,
834 0 reg_pre_period,
835 0 rev_cur_period,
836 0 rev_pre_period,
837 0 orders_cur_period,
838 0 orders_pre_period,
839 0 aleads_cur_period,
840 0 aleads_pre_period
841 FROM
842 bim_r_camp_daily_facts a
843 WHERE
844 a.budget_approved > 0
845 GROUP BY a.campaign_id,
846 a.transaction_create_date,
847 a.start_date,
848 a.end_date,
849 a.budget_approved,
850 a.campaign_region,
851 a.campaign_country,
852 a.business_unit_id,
853 a.campaign_status
854 UNION ALL
855 SELECT
856 a.campaign_id object_id,
857 a.transaction_create_date,
858 'CAMP' object_type,
859 a.budget_approved budget_approved,
860 a.campaign_region region,
861 a.campaign_country country,
862 a.business_unit_id business_unit,
863 a.start_date start_date,
864 a.end_date end_date,
865 a.campaign_status status,
866 l_day_code period_type,
867 'Cumulative' calculation_type,
868 0 cost_cur_period,
869 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
870 0),
871 0,
872 a.transaction_create_date,
873 a.start_date),
874 end_date,
875 l_day_code,
876 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
877 0),
878 0,
879 ((nvl(a.end_date,
880 sysdate) - a.transaction_create_date)+1),
881 ((nvl(a.end_date,
882 sysdate) - a.start_date)+1)),
883 0,
884 1,
885 DECODE(GREATEST(a.start_date - a.transaction_create_date,
886 0),
887 0,
888 ((nvl(a.end_date,
889 sysdate) - a.transaction_create_date)+1),
890 ((nvl(a.end_date,
891 sysdate) - a.start_date)+1))))) cost_cur_period,
892 0 leads_cur_period,
893 0 leads_pre_period,
894 0 res_cur_period,
895 0 res_pre_period,
896 0 reg_cur_period,
897 0 reg_pre_period,
898 0 rev_cur_period,
899 0 rev_pre_period,
900 0 orders_cur_period,
901 0 orders_pre_period,
902 0 aleads_cur_period,
903 0 aleads_pre_period
904 FROM
905 bim_r_camp_daily_facts a
906 WHERE
907 a.budget_approved > 0
908 GROUP BY a.campaign_id,
909 a.transaction_create_date,
910 a.start_date,
911 a.end_date,
912 a.budget_approved,
913 a.campaign_region,
914 a.campaign_country,
915 a.business_unit_id,
916 a.campaign_status
917 UNION ALL
921 'CAMP' object_type,
918 SELECT
919 a.campaign_id object_id,
920 sysdate transaction_create_date,
922 0 budget_approved,
923 a.campaign_region region,
924 a.campaign_country country,
925 a.business_unit_id business_unit,
926 a.start_date start_date,
927 a.end_date end_date,
928 a.campaign_status status,
929 l_year_code period_type,
930 'Cumulative' calculation_type,
931 0 cost_cur_period,
932 0 cost_pre_period,
933 sum(leads_open + leads_closed) leads_cur_period,
934 0 leads_pre_period,
935 sum(positive_responses) resp_cur_period,
936 0 res_pre_period,
937 0 reg_cur_period,
938 0 reg_pre_period,
939 sum(orders_booked_amt) rev_cur_period,
940 0 rev_pre_period,
941 sum(orders_booked) orders_cur_period,
942 0 orders_pre_period,
943 sum(leads_hot) aleads_cur_period,
944 0 aleads_pre_period
945 FROM
946 bim_r_camp_daily_facts a
947 WHERE
948 a.transaction_create_date between l_cur_year_start_date and l_cur_year_end_date
949 GROUP BY a.campaign_id,
950 a.start_date,
951 a.end_date,
952 a.campaign_region,
953 a.campaign_country,
954 a.business_unit_id,
955 a.campaign_status
956 UNION ALL
957 SELECT
958 a.campaign_id object_id,
959 sysdate transaction_create_date,
960 'CAMP' object_type,
961 0 budget_approved,
962 a.campaign_region region,
963 a.campaign_country country,
964 a.business_unit_id business_unit,
965 a.start_date start_date,
966 a.end_date end_date,
967 a.campaign_status status,
968 l_year_code period_type,
969 'Cumulative' calculation_type,
970 0 cost_cur_period,
971 0 cost_pre_period,
972 0 leads_cur_period,
973 sum(leads_open + leads_closed ) leads_pre_period,
974 0 res_cur_period,
975 sum(positive_responses) resp_pre_period,
976 0 reg_cur_period,
977 0 reg_pre_period,
978 0 rev_cur_period,
979 sum(orders_booked_amt) rev_pre_period,
980 0 orders_cur_period,
981 sum(orders_booked) orders_pre_period,
982 0 aleads_cur_period,
983 sum(leads_hot) aleads_pre_period
984 FROM
985 bim_r_camp_daily_facts a
986 WHERE
987 a.transaction_create_date between l_pre_year_start_date and l_pre_year_end_date
988 GROUP BY a.campaign_id,
989 a.start_date,
990 a.end_date,
991 a.campaign_region,
992 a.campaign_country,
993 a.business_unit_id,
994 a.campaign_status
995 UNION ALL
996 SELECT
997 a.campaign_id object_id,
998 sysdate transaction_create_date,
999 'CAMP' object_type,
1000 0 budget_approved,
1001 a.campaign_region region,
1002 a.campaign_country country,
1003 a.business_unit_id business_unit,
1004 a.start_date start_date,
1005 a.end_date end_date,
1006 a.campaign_status status,
1007 l_quarter_code period_type,
1008 'Cumulative' calculation_type,
1009 0 cost_cur_period,
1010 0 cost_pre_period,
1011 sum(leads_open + leads_closed ) leads_cur_period,
1012 0 leads_pre_period,
1013 sum(positive_responses) resp_cur_period,
1014 0 res_pre_period,
1015 0 reg_cur_period,
1016 0 reg_pre_period,
1017 sum(orders_booked_amt) rev_cur_period,
1018 0 rev_pre_period,
1019 sum(orders_booked) orders_cur_period,
1020 0 orders_pre_period,
1021 sum(leads_hot) aleads_cur_period,
1022 0 aleads_pre_period
1023 FROM
1024 bim_r_camp_daily_facts a
1025 WHERE
1026 a.transaction_create_date between l_cur_qtr_start_date and l_cur_qtr_end_date
1027 GROUP BY a.campaign_id,
1028 a.start_date,
1029 a.end_date,
1030 a.campaign_region,
1031 a.campaign_country,
1032 a.business_unit_id,
1033 a.campaign_status
1034 UNION ALL
1035 SELECT
1036 a.campaign_id object_id,
1037 sysdate transaction_create_date,
1038 'CAMP' object_type,
1039 0 budget_approved,
1040 a.campaign_region region,
1041 a.campaign_country country,
1042 a.business_unit_id business_unit,
1043 a.start_date start_date,
1044 a.end_date end_date,
1045 a.campaign_status status,
1046 l_quarter_code period_type,
1047 'Cumulative' calculation_type,
1048 0 cost_cur_period,
1049 0 cost_pre_period,
1050 0 leads_cur_period,
1051 sum(leads_open + leads_closed ) leads_pre_period,
1052 0 res_cur_period,
1053 sum(positive_responses) resp_pre_period,
1054 0 reg_cur_period,
1055 0 reg_pre_period,
1056 0 rev_cur_period,
1057 sum(orders_booked_amt) rev_pre_period,
1058 0 orders_cur_period,
1059 sum(orders_booked) orders_pre_period,
1060 0 aleads_cur_period,
1061 sum(leads_hot) aleads_pre_period
1062 FROM
1063 bim_r_camp_daily_facts a
1064 WHERE
1065 a.transaction_create_date between l_pre_qtr_start_date and l_pre_qtr_end_date
1066 GROUP BY a.campaign_id,
1067 a.start_date,
1068 a.end_date,
1072 a.campaign_status
1069 a.campaign_region,
1070 a.campaign_country,
1071 a.business_unit_id,
1073 UNION ALL
1074 SELECT
1075 a.campaign_id object_id,
1076 sysdate transaction_create_date,
1077 'CAMP' object_type,
1078 0 budget_approved,
1079 a.campaign_region region,
1080 a.campaign_country country,
1081 a.business_unit_id business_unit,
1082 a.start_date start_date,
1083 a.end_date end_date,
1084 a.campaign_status status,
1085 l_month_code period_type,
1086 'Cumulative' calculation_type,
1087 0 cost_cur_period,
1088 0 cost_pre_period,
1089 sum(leads_open + leads_closed ) leads_cur_period,
1090 0 leads_pre_period,
1091 sum(positive_responses) resp_cur_period,
1092 0 res_pre_period,
1093 0 reg_cur_period,
1094 0 reg_pre_period,
1095 sum(orders_booked_amt) rev_cur_period,
1096 0 rev_pre_period,
1097 sum(orders_booked) orders_cur_period,
1098 0 orders_pre_period,
1099 sum(leads_hot) aleads_cur_period,
1100 0 aleads_pre_period
1101 FROM
1102 bim_r_camp_daily_facts a
1103 WHERE
1104 a.transaction_create_date between l_cur_month_start_date and l_cur_month_end_date
1105 GROUP BY a.campaign_id,
1106 a.start_date,
1107 a.end_date,
1108 a.campaign_region,
1109 a.campaign_country,
1110 a.business_unit_id,
1111 a.campaign_status
1112 UNION ALL
1113 SELECT
1114 a.campaign_id object_id,
1115 sysdate transaction_create_date,
1116 'CAMP' object_type,
1117 0 budget_approved,
1118 a.campaign_region region,
1119 a.campaign_country country,
1120 a.business_unit_id business_unit,
1121 a.start_date start_date,
1122 a.end_date end_date,
1123 a.campaign_status status,
1124 l_month_code period_type,
1125 'Cumulative' calculation_type,
1126 0 cost_cur_period,
1127 0 cost_pre_period,
1128 0 leads_cur_period,
1129 sum(leads_open + leads_closed ) leads_pre_period,
1130 0 res_cur_period,
1131 sum(positive_responses) resp_pre_period,
1132 0 reg_cur_period,
1133 0 reg_pre_period,
1134 0 rev_cur_period,
1135 sum(orders_booked_amt) rev_pre_period,
1136 0 orders_cur_period,
1137 sum(orders_booked) orders_pre_period,
1138 0 aleads_cur_period,
1139 sum(leads_hot) aleads_pre_period
1140 FROM
1141 bim_r_camp_daily_facts a
1142 WHERE
1143 a.transaction_create_date between l_pre_month_start_date and l_pre_month_end_date
1144 GROUP BY a.campaign_id,
1145 a.start_date,
1146 a.end_date,
1147 a.campaign_region,
1148 a.campaign_country,
1149 a.business_unit_id,
1150 a.campaign_status
1151 UNION ALL
1152 SELECT
1153 a.campaign_id object_id,
1154 sysdate transaction_create_date,
1155 'CAMP' object_type,
1156 0 budget_approved,
1157 a.campaign_region region,
1158 a.campaign_country country,
1159 a.business_unit_id business_unit,
1160 a.start_date start_date,
1161 a.end_date end_date,
1162 a.campaign_status status,
1163 l_week_code period_type,
1164 'Cumulative' calculation_type,
1165 0 cost_cur_period,
1166 0 cost_pre_period,
1167 sum(leads_open + leads_closed ) leads_cur_period,
1168 0 leads_pre_period,
1169 sum(positive_responses) resp_cur_period,
1170 0 res_pre_period,
1171 0 reg_cur_period,
1172 0 reg_pre_period,
1173 sum(orders_booked_amt) rev_cur_period,
1174 0 rev_pre_period,
1175 sum(orders_booked) orders_cur_period,
1176 0 orders_pre_period,
1177 sum(leads_hot) aleads_cur_period,
1178 0 aleads_pre_period
1179 FROM
1180 bim_r_camp_daily_facts a
1181 WHERE
1182 a.transaction_create_date between l_cur_week_start_date and l_cur_week_end_date
1183 GROUP BY a.campaign_id,
1184 a.start_date,
1185 a.end_date,
1186 a.campaign_region,
1187 a.campaign_country,
1188 a.business_unit_id,
1189 a.campaign_status
1190 UNION ALL
1191 SELECT
1192 a.campaign_id object_id,
1193 sysdate transaction_create_date,
1194 'CAMP' object_type,
1195 0 budget_approved,
1196 a.campaign_region region,
1197 a.campaign_country country,
1198 a.business_unit_id business_unit,
1199 a.start_date start_date,
1200 a.end_date end_date,
1201 a.campaign_status status,
1202 l_week_code period_type,
1203 'Cumulative' calculation_type,
1204 0 cost_cur_period,
1205 0 cost_pre_period,
1206 0 leads_cur_period,
1207 sum(leads_open + leads_closed) leads_pre_period,
1208 0 res_cur_period,
1209 sum(positive_responses) resp_pre_period,
1210 0 reg_cur_period,
1211 0 reg_pre_period,
1212 0 rev_cur_period,
1213 sum(orders_booked_amt) rev_pre_period,
1214 0 orders_cur_period,
1215 sum(orders_booked) orders_pre_period,
1216 0 aleads_cur_period,
1217 sum(leads_hot) aleads_pre_period
1218 FROM
1219 bim_r_camp_daily_facts a
1220 WHERE
1224 a.end_date,
1221 a.transaction_create_date between l_pre_week_start_date and l_pre_week_end_date
1222 GROUP BY a.campaign_id,
1223 a.start_date,
1225 a.campaign_region,
1226 a.campaign_country,
1227 a.business_unit_id,
1228 a.campaign_status
1229 UNION ALL
1230 SELECT
1231 a.campaign_id object_id,
1232 sysdate transaction_create_date,
1233 'CAMP' object_type,
1234 0 budget_approved,
1235 a.campaign_region region,
1236 a.campaign_country country,
1237 a.business_unit_id business_unit,
1238 a.start_date start_date,
1239 a.end_date end_date,
1240 a.campaign_status status,
1241 l_day_code period_type,
1242 'Cumulative' calculation_type,
1243 0 cost_cur_period,
1244 0 cost_pre_period,
1245 sum(leads_open + leads_closed) leads_cur_period,
1246 0 leads_pre_period,
1247 sum(positive_responses) resp_cur_period,
1248 0 res_pre_period,
1249 0 reg_cur_period,
1250 0 reg_pre_period,
1251 sum(orders_booked_amt) rev_cur_period,
1252 0 rev_pre_period,
1253 sum(orders_booked) orders_cur_period,
1254 0 orders_pre_period,
1255 sum(leads_hot) aleads_cur_period,
1256 0 aleads_pre_period
1257 FROM
1258 bim_r_camp_daily_facts a
1259 WHERE
1260 a.transaction_create_date = trunc(l_date)
1261 GROUP BY a.campaign_id,
1262 a.start_date,
1263 a.end_date,
1264 a.campaign_region,
1265 a.campaign_country,
1266 a.business_unit_id,
1267 a.campaign_status
1268 UNION ALL
1269 SELECT
1270 a.campaign_id object_id,
1271 sysdate transaction_create_date,
1272 'CAMP' object_type,
1273 0 budget_approved,
1274 a.campaign_region region,
1275 a.campaign_country country,
1276 a.business_unit_id business_unit,
1277 a.start_date start_date,
1278 a.end_date end_date,
1279 a.campaign_status status,
1280 l_day_code period_type,
1281 'Cumulative' calculation_type,
1282 0 cost_cur_period,
1283 0 cost_pre_period,
1284 0 leads_cur_period,
1285 sum(leads_open + leads_closed) leads_pre_period,
1286 0 res_cur_period,
1287 sum(positive_responses) resp_pre_period,
1288 0 reg_cur_period,
1289 0 reg_pre_period,
1290 0 rev_cur_period,
1291 sum(orders_booked_amt) rev_pre_period,
1292 0 orders_cur_period,
1293 sum(orders_booked) orders_pre_period,
1294 0 aleads_cur_period,
1295 sum(leads_hot) aleads_pre_period
1296 FROM
1297 bim_r_camp_daily_facts a
1298 WHERE
1299 a.transaction_create_date = trunc(l_date) - 1
1300 GROUP BY a.campaign_id,
1301 a.start_date,
1302 a.end_date,
1303 a.campaign_region,
1304 a.campaign_country,
1305 a.business_unit_id,
1306 a.campaign_status
1307 UNION ALL
1308 SELECT
1309 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1310 a.transaction_create_date,
1311 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1312 a.budget_approved budget_approved,
1313 b.area2_code region,
1314 a.country country,
1315 a.business_unit_id business_unit,
1316 a.start_date start_date,
1317 a.end_date end_date,
1318 a.status status,
1319 l_year_code period_type,
1320 'Cumulative' calculation_type,
1321 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1322 0),
1323 0,
1324 a.transaction_create_date,
1325 a.start_date),
1326 end_date,
1327 l_year_code,
1328 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1329 0),
1330 0,
1331 ((nvl(a.end_date,
1332 sysdate) - a.transaction_create_date)+1),
1333 ((nvl(a.end_date,
1334 sysdate) - a.start_date)+1)),
1335 0,
1336 1,
1337 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1338 0),
1339 0,
1340 ((nvl(a.end_date,
1341 sysdate) - a.transaction_create_date)+1),
1342 ((nvl(a.end_date,
1343 sysdate) - a.start_date)+1))))) cost_cur_period,
1344 0 cost_pre_period,
1345 0 leads_cur_period,
1346 0 leads_pre_period,
1347 0 res_cur_period,
1348 0 res_pre_period,
1349 0 reg_cur_period,
1350 0 reg_pre_period,
1351 0 rev_cur_period,
1352 0 rev_pre_period,
1353 0 orders_cur_period,
1354 0 orders_pre_period,
1355 0 aleads_cur_period,
1356 0 aleads_pre_period
1357 FROM
1358 bim_r_even_daily_facts a
1359 ,jtf_loc_hierarchies_b b
1360 WHERE
1361 a.budget_approved > 0
1362 AND b.location_hierarchy_id = a.country
1363 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1364 decode(a.event_header_id,-999,'EONE','EVEH'),
1365 a.transaction_create_date,
1366 a.start_date,
1367 a.end_date,
1368 a.budget_approved,
1369 b.area2_code,
1370 a.country,
1371 a.business_unit_id,
1372 a.status
1373 UNION ALL
1374 SELECT
1378 a.budget_approved budget_approved,
1375 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1376 a.transaction_create_date,
1377 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1379 b.area2_code region,
1380 a.country country,
1381 a.business_unit_id business_unit,
1382 a.start_date start_date,
1383 a.end_date end_date,
1384 a.status status,
1385 l_year_code period_type,
1386 'Cumulative' calculation_type,
1387 0 cost_cur_period,
1388 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1389 0),
1390 0,
1391 a.transaction_create_date,
1392 a.start_date),
1393 end_date,
1394 l_year_code,
1395 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1396 0),
1397 0,
1398 ((nvl(a.end_date,
1399 sysdate) - a.transaction_create_date)+1),
1400 ((nvl(a.end_date,
1401 sysdate) - a.start_date)+1)),
1402 0,
1403 1,
1404 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1405 0),
1406 0,
1407 ((nvl(a.end_date,
1408 sysdate) - a.transaction_create_date)+1),
1409 ((nvl(a.end_date,
1410 sysdate) - a.start_date)+1))))) cost_cur_period,
1411 0 leads_cur_period,
1412 0 leads_pre_period,
1413 0 res_cur_period,
1414 0 res_pre_period,
1415 0 reg_cur_period,
1416 0 reg_pre_period,
1417 0 rev_cur_period,
1418 0 rev_pre_period,
1419 0 orders_cur_period,
1420 0 orders_pre_period,
1421 0 aleads_cur_period,
1422 0 aleads_pre_period
1423 FROM
1424 bim_r_even_daily_facts a
1425 ,jtf_loc_hierarchies_b b
1426 WHERE
1427 a.budget_approved > 0
1428 AND b.location_hierarchy_id = a.country
1429 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1430 decode(a.event_header_id,-999,'EONE','EVEH'),
1431 a.transaction_create_date,
1432 a.start_date,
1433 a.end_date,
1434 a.budget_approved,
1435 b.area2_code,
1436 a.country,
1437 a.business_unit_id,
1438 a.status
1439 UNION ALL
1440 SELECT
1441 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1442 a.transaction_create_date,
1443 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1444 a.budget_approved budget_approved,
1445 b.area2_code region,
1446 a.country country,
1447 a.business_unit_id business_unit,
1448 a.start_date start_date,
1449 a.end_date end_date,
1450 a.status status,
1451 l_quarter_code period_type,
1452 'Cumulative' calculation_type,
1453 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1454 0),
1455 0,
1456 a.transaction_create_date,
1457 a.start_date),
1458 end_date,
1459 l_quarter_code,
1460 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1461 0),
1462 0,
1463 ((nvl(a.end_date,
1464 sysdate) - a.transaction_create_date)+1),
1465 ((nvl(a.end_date,
1466 sysdate) - a.start_date)+1)),
1467 0,
1468 1,
1469 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1470 0),
1471 0,
1472 ((nvl(a.end_date,
1473 sysdate) - a.transaction_create_date)+1),
1474 ((nvl(a.end_date,
1475 sysdate) - a.start_date)+1))))) cost_cur_period,
1476 0 cost_pre_period,
1477 0 leads_cur_period,
1478 0 leads_pre_period,
1479 0 res_cur_period,
1480 0 res_pre_period,
1481 0 reg_cur_period,
1482 0 reg_pre_period,
1483 0 rev_cur_period,
1484 0 rev_pre_period,
1485 0 orders_cur_period,
1486 0 orders_pre_period,
1487 0 aleads_cur_period,
1488 0 aleads_pre_period
1489 FROM
1490 bim_r_even_daily_facts a
1491 ,jtf_loc_hierarchies_b b
1492 WHERE
1493 a.budget_approved > 0
1494 AND b.location_hierarchy_id = a.country
1495 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1496 decode(a.event_header_id,-999,'EONE','EVEH'),
1497 a.transaction_create_date,
1498 a.start_date,
1499 a.end_date,
1500 a.budget_approved,
1501 b.area2_code,
1502 a.country,
1503 a.business_unit_id,
1504 a.status
1505 UNION ALL
1506 SELECT
1507 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1508 a.transaction_create_date,
1509 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1510 a.budget_approved budget_approved,
1511 b.area2_code region,
1512 a.country country,
1513 a.business_unit_id business_unit,
1514 a.start_date start_date,
1515 a.end_date end_date,
1516 a.status status,
1517 l_quarter_code period_type,
1518 'Cumulative' calculation_type,
1519 0 cost_cur_period,
1520 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1521 0),
1522 0,
1523 a.transaction_create_date,
1527 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1524 a.start_date),
1525 end_date,
1526 l_quarter_code,
1528 0),
1529 0,
1530 ((nvl(a.end_date,
1531 sysdate) - a.transaction_create_date)+1),
1532 ((nvl(a.end_date,
1533 sysdate) - a.start_date)+1)),
1534 0,
1535 1,
1536 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1537 0),
1538 0,
1539 ((nvl(a.end_date,
1540 sysdate) - a.transaction_create_date)+1),
1541 ((nvl(a.end_date,
1542 sysdate) - a.start_date)+1))))) cost_cur_period,
1543 0 leads_cur_period,
1544 0 leads_pre_period,
1545 0 res_cur_period,
1546 0 res_pre_period,
1547 0 reg_cur_period,
1548 0 reg_pre_period,
1549 0 rev_cur_period,
1550 0 rev_pre_period,
1551 0 orders_cur_period,
1552 0 orders_pre_period,
1553 0 aleads_cur_period,
1554 0 aleads_pre_period
1555 FROM
1556 bim_r_even_daily_facts a
1557 ,jtf_loc_hierarchies_b b
1558 WHERE
1559 a.budget_approved > 0
1560 AND b.location_hierarchy_id = a.country
1561 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1562 decode(a.event_header_id,-999,'EONE','EVEH'),
1563 a.transaction_create_date,
1564 a.start_date,
1565 a.end_date,
1566 a.budget_approved,
1567 b.area2_code,
1568 a.country,
1569 a.business_unit_id,
1570 a.status
1571 UNION ALL
1572 SELECT
1573 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1574 a.transaction_create_date,
1575 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1576 a.budget_approved budget_approved,
1577 b.area2_code region,
1578 a.country country,
1579 a.business_unit_id business_unit,
1580 a.start_date start_date,
1581 a.end_date end_date,
1582 a.status status,
1583 l_month_code period_type,
1584 'Cumulative' calculation_type,
1585 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1586 0),
1587 0,
1588 a.transaction_create_date,
1589 a.start_date),
1590 end_date,
1591 l_month_code,
1592 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1593 0),
1594 0,
1595 ((nvl(a.end_date,
1596 sysdate) - a.transaction_create_date)+1),
1597 ((nvl(a.end_date,
1598 sysdate) - a.start_date)+1)),
1599 0,
1600 1,
1601 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1602 0),
1603 0,
1604 ((nvl(a.end_date,
1605 sysdate) - a.transaction_create_date)+1),
1606 ((nvl(a.end_date,
1607 sysdate) - a.start_date)+1))))) cost_cur_period,
1608 0 cost_pre_period,
1609 0 leads_cur_period,
1610 0 leads_pre_period,
1611 0 res_cur_period,
1612 0 res_pre_period,
1613 0 reg_cur_period,
1614 0 reg_pre_period,
1615 0 rev_cur_period,
1616 0 rev_pre_period,
1617 0 orders_cur_period,
1618 0 orders_pre_period,
1619 0 aleads_cur_period,
1620 0 aleads_pre_period
1621 FROM
1622 bim_r_even_daily_facts a
1623 ,jtf_loc_hierarchies_b b
1624 WHERE
1625 a.budget_approved > 0
1626 AND b.location_hierarchy_id = a.country
1627 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1628 decode(a.event_header_id,-999,'EONE','EVEH'),
1629 a.transaction_create_date,
1630 a.start_date,
1631 a.end_date,
1632 a.budget_approved,
1633 b.area2_code,
1634 a.country,
1635 a.business_unit_id,
1636 a.status
1637 UNION ALL
1638 SELECT
1639 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1640 a.transaction_create_date,
1641 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1642 a.budget_approved budget_approved,
1643 b.area2_code region,
1644 a.country country,
1645 a.business_unit_id business_unit,
1646 a.start_date start_date,
1647 a.end_date end_date,
1648 a.status status,
1649 l_month_code period_type,
1650 'Cumulative' calculation_type,
1651 0 cost_cur_period,
1652 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1653 0),
1654 0,
1655 a.transaction_create_date,
1656 a.start_date),
1657 end_date,
1658 l_month_code,
1659 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1660 0),
1661 0,
1662 ((nvl(a.end_date,
1663 sysdate) - a.transaction_create_date)+1),
1664 ((nvl(a.end_date,
1665 sysdate) - a.start_date)+1)),
1666 0,
1667 1,
1668 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1669 0),
1670 0,
1671 ((nvl(a.end_date,
1672 sysdate) - a.transaction_create_date)+1),
1673 ((nvl(a.end_date,
1674 sysdate) - a.start_date)+1))))) cost_cur_period,
1678 0 res_pre_period,
1675 0 leads_cur_period,
1676 0 leads_pre_period,
1677 0 res_cur_period,
1679 0 reg_cur_period,
1680 0 reg_pre_period,
1681 0 rev_cur_period,
1682 0 rev_pre_period,
1683 0 orders_cur_period,
1684 0 orders_pre_period,
1685 0 aleads_cur_period,
1686 0 aleads_pre_period
1687 FROM
1688 bim_r_even_daily_facts a
1689 ,jtf_loc_hierarchies_b b
1690 WHERE
1691 a.budget_approved > 0
1692 AND b.location_hierarchy_id = a.country
1693 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1694 decode(a.event_header_id,-999,'EONE','EVEH'),
1695 a.transaction_create_date,
1696 a.start_date,
1697 a.end_date,
1698 a.budget_approved,
1699 b.area2_code,
1700 a.country,
1701 a.business_unit_id,
1702 a.status
1703 UNION ALL
1704 SELECT
1705 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1706 a.transaction_create_date,
1707 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1708 a.budget_approved budget_approved,
1709 b.area2_code region,
1710 a.country country,
1711 a.business_unit_id business_unit,
1712 a.start_date start_date,
1713 a.end_date end_date,
1714 a.status status,
1715 l_week_code period_type,
1716 'Cumulative' calculation_type,
1717 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1718 0),
1719 0,
1720 a.transaction_create_date,
1721 a.start_date),
1722 end_date,
1723 l_week_code,
1724 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1725 0),
1726 0,
1727 ((nvl(a.end_date,
1728 sysdate) - a.transaction_create_date)+1),
1729 ((nvl(a.end_date,
1730 sysdate) - a.start_date)+1)),
1731 0,
1732 1,
1733 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1734 0),
1735 0,
1736 ((nvl(a.end_date,
1737 sysdate) - a.transaction_create_date)+1),
1738 ((nvl(a.end_date,
1739 sysdate) - a.start_date)+1))))) cost_cur_period,
1740 0 cost_pre_period,
1741 0 leads_cur_period,
1742 0 leads_pre_period,
1743 0 res_cur_period,
1744 0 res_pre_period,
1745 0 reg_cur_period,
1746 0 reg_pre_period,
1747 0 rev_cur_period,
1748 0 rev_pre_period,
1749 0 orders_cur_period,
1750 0 orders_pre_period,
1751 0 aleads_cur_period,
1752 0 aleads_pre_period
1753 FROM
1754 bim_r_even_daily_facts a
1755 ,jtf_loc_hierarchies_b b
1756 WHERE
1757 a.budget_approved > 0
1758 AND b.location_hierarchy_id = a.country
1759 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1760 decode(a.event_header_id,-999,'EONE','EVEH'),
1761 a.transaction_create_date,
1762 a.start_date,
1763 a.end_date,
1764 a.budget_approved,
1765 b.area2_code,
1766 a.country,
1767 a.business_unit_id,
1768 a.status
1769 UNION ALL
1770 SELECT
1771 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1772 a.transaction_create_date,
1773 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1774 a.budget_approved budget_approved,
1775 b.area2_code region,
1776 a.country country,
1777 a.business_unit_id business_unit,
1778 a.start_date start_date,
1779 a.end_date end_date,
1780 a.status status,
1781 l_week_code period_type,
1782 'Cumulative' calculation_type,
1783 0 cost_cur_period,
1784 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1785 0),
1786 0,
1787 a.transaction_create_date,
1788 a.start_date),
1789 end_date,
1790 l_week_code,
1791 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1792 0),
1793 0,
1794 ((nvl(a.end_date,
1795 sysdate) - a.transaction_create_date)+1),
1796 ((nvl(a.end_date,
1797 sysdate) - a.start_date)+1)),
1798 0,
1799 1,
1800 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1801 0),
1802 0,
1803 ((nvl(a.end_date,
1804 sysdate) - a.transaction_create_date)+1),
1805 ((nvl(a.end_date,
1806 sysdate) - a.start_date)+1))))) cost_cur_period,
1807 0 leads_cur_period,
1808 0 leads_pre_period,
1809 0 res_cur_period,
1810 0 res_pre_period,
1811 0 reg_cur_period,
1812 0 reg_pre_period,
1813 0 rev_cur_period,
1814 0 rev_pre_period,
1815 0 orders_cur_period,
1816 0 orders_pre_period,
1817 0 aleads_cur_period,
1818 0 aleads_pre_period
1819 FROM
1820 bim_r_even_daily_facts a
1821 ,jtf_loc_hierarchies_b b
1822 WHERE
1823 a.budget_approved > 0
1824 AND b.location_hierarchy_id = a.country
1825 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1826 decode(a.event_header_id,-999,'EONE','EVEH'),
1830 a.budget_approved,
1827 a.transaction_create_date,
1828 a.start_date,
1829 a.end_date,
1831 b.area2_code,
1832 a.country,
1833 a.business_unit_id,
1834 a.status
1835 UNION ALL
1836 SELECT
1837 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1838 a.transaction_create_date,
1839 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1840 a.budget_approved budget_approved,
1841 b.area2_code region,
1842 a.country country,
1843 a.business_unit_id business_unit,
1844 a.start_date start_date,
1845 a.end_date end_date,
1846 a.status status,
1847 l_day_code period_type,
1848 'Cumulative' calculation_type,
1849 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1850 0),
1851 0,
1852 a.transaction_create_date,
1853 a.start_date),
1854 end_date,
1855 l_day_code,
1856 'Current')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1857 0),
1858 0,
1859 ((nvl(a.end_date,
1860 sysdate) - a.transaction_create_date)+1),
1861 ((nvl(a.end_date,
1862 sysdate) - a.start_date)+1)),
1863 0,
1864 1,
1865 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1866 0),
1867 0,
1868 ((nvl(a.end_date,
1869 sysdate) - a.transaction_create_date)+1),
1870 ((nvl(a.end_date,
1871 sysdate) - a.start_date)+1))))) cost_cur_period,
1872 0 cost_pre_period,
1873 0 leads_cur_period,
1874 0 leads_pre_period,
1875 0 res_cur_period,
1876 0 res_pre_period,
1877 0 reg_cur_period,
1878 0 reg_pre_period,
1879 0 rev_cur_period,
1880 0 rev_pre_period,
1881 0 orders_cur_period,
1882 0 orders_pre_period,
1883 0 aleads_cur_period,
1884 0 aleads_pre_period
1885 FROM
1886 bim_r_even_daily_facts a
1887 ,jtf_loc_hierarchies_b b
1888 WHERE
1889 a.budget_approved > 0
1890 AND b.location_hierarchy_id = a.country
1891 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1892 decode(a.event_header_id,-999,'EONE','EVEH'),
1893 a.transaction_create_date,
1894 a.start_date,
1895 a.end_date,
1896 a.budget_approved,
1897 b.area2_code,
1898 a.country,
1899 a.business_unit_id,
1900 a.status
1901 UNION ALL
1902 SELECT
1903 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1904 a.transaction_create_date,
1905 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1906 a.budget_approved budget_approved,
1907 b.area2_code region,
1908 a.country country,
1909 a.business_unit_id business_unit,
1910 a.start_date start_date,
1911 a.end_date end_date,
1912 a.status status,
1913 l_day_code period_type,
1914 'Cumulative' calculation_type,
1915 0 cost_cur_period,
1916 SUM(calculate_days(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1917 0),
1918 0,
1919 a.transaction_create_date,
1920 a.start_date),
1921 end_date,
1922 l_day_code,
1923 'Previous')*(a.budget_approved/DECODE(DECODE(GREATEST(a.start_date - a.transaction_create_date,
1924 0),
1925 0,
1926 ((nvl(a.end_date,
1927 sysdate) - a.transaction_create_date)+1),
1928 ((nvl(a.end_date,
1929 sysdate) - a.start_date)+1)),
1930 0,
1931 1,
1932 DECODE(GREATEST(a.start_date - a.transaction_create_date,
1933 0),
1934 0,
1935 ((nvl(a.end_date,
1936 sysdate) - a.transaction_create_date)+1),
1937 ((nvl(a.end_date,
1938 sysdate) - a.start_date)+1))))) cost_cur_period,
1939 0 leads_cur_period,
1940 0 leads_pre_period,
1941 0 res_cur_period,
1942 0 res_pre_period,
1943 0 reg_cur_period,
1944 0 reg_pre_period,
1945 0 rev_cur_period,
1946 0 rev_pre_period,
1947 0 orders_cur_period,
1948 0 orders_pre_period,
1949 0 aleads_cur_period,
1950 0 aleads_pre_period
1951 FROM
1952 bim_r_even_daily_facts a
1953 ,jtf_loc_hierarchies_b b
1954 WHERE
1955 a.budget_approved > 0
1956 AND b.location_hierarchy_id = a.country
1957 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
1958 decode(a.event_header_id,-999,'EONE','EVEH'),
1959 a.transaction_create_date,
1960 a.start_date,
1961 a.end_date,
1962 a.budget_approved,
1963 b.area2_code,
1964 a.country,
1965 a.business_unit_id,
1966 a.status
1967 UNION ALL
1968 SELECT
1969 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
1970 sysdate transaction_create_date,
1971 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
1972 0 budget_approved,
1973 b.area2_code region,
1974 a.country country,
1975 a.business_unit_id business_unit,
1976 a.start_date start_date,
1977 a.end_date end_date,
1978 a.status status,
1982 0 cost_pre_period,
1979 l_year_code period_type,
1980 'Cumulative' calculation_type,
1981 0 cost_cur_period,
1983 sum(leads_open + leads_closed) leads_cur_period,
1984 0 leads_pre_period,
1985 0 res_cur_period,
1986 0 res_pre_period,
1987 sum(registrations) reg_cur_period,
1988 0 reg_pre_period,
1989 sum(booked_orders_amt) rev_cur_period,
1990 0 rev_pre_period,
1991 sum(booked_orders) orders_cur_period,
1992 0 orders_pre_period,
1993 sum(leads_hot) aleads_cur_period,
1994 0 aleads_pre_period
1995 FROM
1996 bim_r_even_daily_facts a
1997 ,jtf_loc_hierarchies_b b
1998 WHERE
1999 a.transaction_create_date between l_cur_year_start_date and l_cur_year_end_date
2000 AND b.location_hierarchy_id = a.country
2001 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2002 decode(a.event_header_id,-999,'EONE','EVEH'),
2003 a.start_date,
2004 a.end_date,
2005 b.area2_code,
2006 a.country,
2007 a.business_unit_id,
2008 a.status
2009 UNION ALL
2010 SELECT
2011 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2012 sysdate transaction_create_date,
2013 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2014 0 budget_approved,
2015 b.area2_code region,
2016 a.country country,
2017 a.business_unit_id business_unit,
2018 a.start_date start_date,
2019 a.end_date end_date,
2020 a.status status,
2021 l_year_code period_type,
2022 'Cumulative' calculation_type,
2023 0 cost_cur_period,
2024 0 cost_pre_period,
2025 0 leads_cur_period,
2026 sum(leads_open + leads_closed) leads_pre_period,
2027 0 res_cur_period,
2028 0 res_pre_period,
2029 0 reg_cur_period,
2030 sum(registrations) reg_pre_period,
2031 0 rev_cur_period,
2032 sum(booked_orders_amt) rev_pre_period,
2033 0 orders_cur_period,
2034 sum(booked_orders) orders_pre_period,
2035 0 aleads_cur_period,
2036 sum(leads_hot) aleads_pre_period
2037 FROM
2038 bim_r_even_daily_facts a
2039 ,jtf_loc_hierarchies_b b
2040 WHERE
2041 a.transaction_create_date between l_pre_year_start_date and l_pre_year_end_date
2042 AND b.location_hierarchy_id = a.country
2043 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2044 decode(a.event_header_id,-999,'EONE','EVEH'),
2045 a.start_date,
2046 a.end_date,
2047 b.area2_code,
2048 a.country,
2049 a.business_unit_id,
2050 a.status
2051 UNION ALL
2052 SELECT
2053 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2054 sysdate transaction_create_date,
2055 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2056 0 budget_approved,
2057 b.area2_code region,
2058 a.country country,
2059 a.business_unit_id business_unit,
2060 a.start_date start_date,
2061 a.end_date end_date,
2062 a.status status,
2063 l_quarter_code period_type,
2064 'Cumulative' calculation_type,
2065 0 cost_cur_period,
2066 0 cost_pre_period,
2067 sum(leads_open + leads_closed) leads_cur_period,
2068 0 leads_pre_period,
2069 0 res_cur_period,
2070 0 res_pre_period,
2071 sum(registrations) reg_cur_period,
2072 0 reg_pre_period,
2073 sum(booked_orders_amt) rev_cur_period,
2074 0 rev_pre_period,
2075 sum(booked_orders) orders_cur_period,
2076 0 orders_pre_period,
2077 sum(leads_hot) aleads_cur_period,
2078 0 aleads_pre_period
2079 FROM
2080 bim_r_even_daily_facts a
2081 ,jtf_loc_hierarchies_b b
2082 WHERE
2083 a.transaction_create_date between l_cur_qtr_start_date and l_cur_qtr_end_date
2084 AND b.location_hierarchy_id = a.country
2085 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2086 decode(a.event_header_id,-999,'EONE','EVEH'),
2087 a.start_date,
2088 a.end_date,
2089 b.area2_code,
2090 a.country,
2091 a.business_unit_id,
2092 a.status
2093 UNION ALL
2094 SELECT
2095 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2096 sysdate transaction_create_date,
2097 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2098 0 budget_approved,
2099 b.area2_code region,
2100 a.country country,
2101 a.business_unit_id business_unit,
2102 a.start_date start_date,
2103 a.end_date end_date,
2104 a.status status,
2105 l_quarter_code period_type,
2106 'Cumulative' calculation_type,
2107 0 cost_cur_period,
2108 0 cost_pre_period,
2109 0 leads_cur_period,
2110 sum(leads_open + leads_closed) leads_pre_period,
2111 0 res_cur_period,
2112 0 res_pre_period,
2113 0 reg_cur_period,
2114 sum(registrations) reg_pre_period,
2115 0 rev_cur_period,
2116 sum(booked_orders_amt) rev_pre_period,
2117 0 orders_cur_period,
2118 sum(booked_orders) orders_pre_period,
2119 0 aleads_cur_period,
2123 ,jtf_loc_hierarchies_b b
2120 sum(leads_hot) aleads_pre_period
2121 FROM
2122 bim_r_even_daily_facts a
2124 WHERE
2125 a.transaction_create_date between l_pre_qtr_start_date and l_pre_qtr_end_date
2126 AND b.location_hierarchy_id = a.country
2127 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2128 decode(a.event_header_id,-999,'EONE','EVEH'),
2129 a.start_date,
2130 a.end_date,
2131 b.area2_code,
2132 a.country,
2133 a.business_unit_id,
2134 a.status
2135 UNION ALL
2136 SELECT
2137 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2138 sysdate transaction_create_date,
2139 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2140 0 budget_approved,
2141 b.area2_code region,
2142 a.country country,
2143 a.business_unit_id business_unit,
2144 a.start_date start_date,
2145 a.end_date end_date,
2146 a.status status,
2147 l_month_code period_type,
2148 'Cumulative' calculation_type,
2149 0 cost_cur_period,
2150 0 cost_pre_period,
2151 sum(leads_open + leads_closed) leads_cur_period,
2152 0 leads_pre_period,
2153 0 res_cur_period,
2154 0 res_pre_period,
2155 sum(registrations) reg_cur_period,
2156 0 reg_pre_period,
2157 sum(booked_orders_amt) rev_cur_period,
2158 0 rev_pre_period,
2159 sum(booked_orders) orders_cur_period,
2160 0 orders_pre_period,
2161 sum(leads_hot) aleads_cur_period,
2162 0 aleads_pre_period
2163 FROM
2164 bim_r_even_daily_facts a
2165 ,jtf_loc_hierarchies_b b
2166 WHERE
2167 a.transaction_create_date between l_cur_month_start_date and l_cur_month_end_date
2168 AND b.location_hierarchy_id = a.country
2169 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2170 decode(a.event_header_id,-999,'EONE','EVEH'),
2171 a.start_date,
2172 a.end_date,
2173 b.area2_code,
2174 a.country,
2175 a.business_unit_id,
2176 a.status
2177 UNION ALL
2178 SELECT
2179 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2180 sysdate transaction_create_date,
2181 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2182 0 budget_approved,
2183 b.area2_code region,
2184 a.country country,
2185 a.business_unit_id business_unit,
2186 a.start_date start_date,
2187 a.end_date end_date,
2188 a.status status,
2189 l_month_code period_type,
2190 'Cumulative' calculation_type,
2191 0 cost_cur_period,
2192 0 cost_pre_period,
2193 0 leads_cur_period,
2194 sum(leads_open + leads_closed) leads_pre_period,
2195 0 res_cur_period,
2196 0 res_pre_period,
2197 0 reg_cur_period,
2198 sum(registrations) reg_pre_period,
2199 0 rev_cur_period,
2200 sum(booked_orders_amt) rev_pre_period,
2201 0 orders_cur_period,
2202 sum(booked_orders) orders_pre_period,
2203 0 aleads_cur_period,
2204 sum(leads_hot) aleads_pre_period
2205 FROM
2206 bim_r_even_daily_facts a
2207 ,jtf_loc_hierarchies_b b
2208 WHERE
2209 a.transaction_create_date between l_pre_month_start_date and l_pre_month_end_date
2210 AND b.location_hierarchy_id = a.country
2211 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2212 decode(a.event_header_id,-999,'EONE','EVEH'),
2213 a.start_date,
2214 a.end_date,
2215 b.area2_code,
2216 a.country,
2217 a.business_unit_id,
2218 a.status
2219 UNION ALL
2220 SELECT
2221 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2222 sysdate transaction_create_date,
2223 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2224 0 budget_approved,
2225 b.area2_code region,
2226 a.country country,
2227 a.business_unit_id business_unit,
2228 a.start_date start_date,
2229 a.end_date end_date,
2230 a.status status,
2231 l_week_code period_type,
2232 'Cumulative' calculation_type,
2233 0 cost_cur_period,
2234 0 cost_pre_period,
2235 sum(leads_open + leads_closed) leads_cur_period,
2236 0 leads_pre_period,
2237 0 res_cur_period,
2238 0 res_pre_period,
2239 sum(registrations) reg_cur_period,
2240 0 reg_pre_period,
2241 sum(booked_orders_amt) rev_cur_period,
2242 0 rev_pre_period,
2243 sum(booked_orders) orders_cur_period,
2244 0 orders_pre_period,
2245 sum(leads_hot) aleads_cur_period,
2246 0 aleads_pre_period
2247 FROM
2248 bim_r_even_daily_facts a
2249 ,jtf_loc_hierarchies_b b
2250 WHERE
2251 a.transaction_create_date between l_cur_week_start_date and l_cur_week_end_date
2252 AND b.location_hierarchy_id = a.country
2253 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2254 decode(a.event_header_id,-999,'EONE','EVEH'),
2255 a.start_date,
2256 a.end_date,
2257 b.area2_code,
2258 a.country,
2259 a.business_unit_id,
2260 a.status
2261 UNION ALL
2262 SELECT
2266 0 budget_approved,
2263 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2264 sysdate transaction_create_date,
2265 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2267 b.area2_code region,
2268 a.country country,
2269 a.business_unit_id business_unit,
2270 a.start_date start_date,
2271 a.end_date end_date,
2272 a.status status,
2273 l_week_code period_type,
2274 'Cumulative' calculation_type,
2275 0 cost_cur_period,
2276 0 cost_pre_period,
2277 0 leads_cur_period,
2278 sum(leads_open + leads_closed) leads_pre_period,
2279 0 res_cur_period,
2280 0 res_pre_period,
2281 0 reg_cur_period,
2282 sum(registrations) reg_pre_period,
2283 0 rev_cur_period,
2284 sum(booked_orders_amt) rev_pre_period,
2285 0 orders_cur_period,
2286 sum(booked_orders) orders_pre_period,
2287 0 aleads_cur_period,
2288 sum(leads_hot) aleads_pre_period
2289 FROM
2290 bim_r_even_daily_facts a
2291 ,jtf_loc_hierarchies_b b
2292 WHERE
2293 a.transaction_create_date between l_pre_week_start_date and l_pre_week_end_date
2294 AND b.location_hierarchy_id = a.country
2295 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2296 decode(a.event_header_id,-999,'EONE','EVEH'),
2297 a.start_date,
2298 a.end_date,
2299 b.area2_code,
2300 a.country,
2301 a.business_unit_id,
2302 a.status
2303 UNION ALL
2304 SELECT
2305 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2306 sysdate transaction_create_date,
2307 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2308 0 budget_approved,
2309 b.area2_code region,
2310 a.country country,
2311 a.business_unit_id business_unit,
2312 a.start_date start_date,
2313 a.end_date end_date,
2314 a.status status,
2315 l_day_code period_type,
2316 'Cumulative' calculation_type,
2317 0 cost_cur_period,
2318 0 cost_pre_period,
2319 sum(leads_open + leads_closed) leads_cur_period,
2320 0 leads_pre_period,
2321 0 res_cur_period,
2322 0 res_pre_period,
2323 sum(registrations) reg_cur_period,
2324 0 reg_pre_period,
2325 sum(booked_orders_amt) rev_cur_period,
2326 0 rev_pre_period,
2327 sum(booked_orders) orders_cur_period,
2328 0 orders_pre_period,
2329 sum(leads_hot) aleads_cur_period,
2330 0 aleads_pre_period
2331 FROM
2332 bim_r_even_daily_facts a
2333 ,jtf_loc_hierarchies_b b
2334 WHERE
2335 trunc(a.transaction_create_date) = trunc(l_date)
2336 AND b.location_hierarchy_id = a.country
2337 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2338 decode(a.event_header_id,-999,'EONE','EVEH'),
2339 a.start_date,
2340 a.end_date,
2341 b.area2_code,
2342 a.country,
2343 a.business_unit_id,
2344 a.status
2345 UNION ALL
2346 SELECT
2347 decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id) object_id,
2348 sysdate transaction_create_date,
2349 decode(a.event_header_id,-999,'EONE','EVEH') object_type,
2350 0 budget_approved,
2351 b.area2_code region,
2352 a.country country,
2353 a.business_unit_id business_unit,
2354 a.start_date start_date,
2355 a.end_date end_date,
2356 a.status status,
2357 l_day_code period_type,
2358 'Cumulative' calculation_type,
2359 0 cost_cur_period,
2360 0 cost_pre_period,
2361 0 leads_cur_period,
2362 sum(leads_open + leads_closed) leads_pre_period,
2363 0 res_cur_period,
2364 0 res_pre_period,
2365 0 reg_cur_period,
2366 sum(registrations) reg_pre_period,
2367 0 rev_cur_period,
2368 sum(booked_orders_amt) rev_pre_period,
2369 0 orders_cur_period,
2370 sum(booked_orders) orders_pre_period,
2371 0 aleads_cur_period,
2372 sum(leads_hot) aleads_pre_period
2373 FROM
2374 bim_r_even_daily_facts a
2375 ,jtf_loc_hierarchies_b b
2376 WHERE
2377 trunc(a.transaction_create_date) = trunc(l_date - 1)
2378 AND b.location_hierarchy_id = a.country
2379 GROUP BY decode(a.event_header_id,-999,a.event_offer_id, a.event_header_id),
2380 decode(a.event_header_id,-999,'EONE','EVEH'),
2381 a.start_date,
2382 a.end_date,
2383 b.area2_code,
2384 a.country,
2385 a.business_unit_id,
2386 a.status
2387 ) inner;
2388 COMMIT;
2389
2390 ams_utility_pvt.write_conc_log('BIM_R_KPI_FACT: INSERT END');
2391
2392 UPDATE bim_r_kpi_facts
2393 SET cost_cur_period=0
2394 ,cost_pre_period=0
2395 WHERE status = 'CANCELLED';
2396 COMMIT;
2397
2398 ams_utility_pvt.debug_message('POPULATE BIM_R_KPI_FACT END');
2399
2400 DELETE FROM bim_rep_history
2401 WHERE object='KPILD';
2402 INSERT INTO
2403 bim_rep_history
2404 (creation_date,
2405 last_update_date,
2406 created_by,
2407 last_updated_by,
2408 object,
2409 object_last_updated_date)
2410 VALUES
2411 (sysdate,
2412 sysdate,
2413 FND_GLOBAL.USER_ID(),
2414 FND_GLOBAL.USER_ID(),
2415 'KPILD',
2416 sysdate);
2417 COMMIT;
2418
2419 ams_utility_pvt.write_conc_log('BIM_R_KPI_FACT: POPULATE END');
2420
2421 ams_utility_pvt.write_conc_log('End of KPI Facts Program');
2422
2423 EXCEPTION
2424
2425 WHEN OTHERS THEN
2426 ams_utility_pvt.write_conc_log('BIM_R_KPI_FACT--POPULATE: Error occured '||sqlerrm(sqlcode));
2427 ERRBUF := sqlerrm(sqlcode);
2428 RETCODE := sqlcode;
2429
2430 -- dbms_output.put_line('END OF POPULATING BIM_R_KPI_FACT');
2431 -- dbms_output.put_line('END OF POPULATE');
2432
2433 END POPULATE;
2434
2435 END BIM_KPI_FACT;