[Home] [Help]
PACKAGE BODY: APPS.BIM_LEAD_FACTS_PKG
Source
1 PACKAGE BODY BIM_LEAD_FACTS_PKG AS
2 /*$Header: bimldsfb.pls 120.2 2005/11/11 05:08:51 arvikuma noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_LEAD_FACTS_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimldsfb.pls';
6
7 ------------------------------------------------------------------------------------------------
8 ----
9 ----This procedure finds out if the user is trying to run first_load or subsequent load
10 ----and calls the load_data procedure with the specific parameters to each type of load
11 ----
12 ------------------------------------------------------------------------------------------------
13
14 PROCEDURE POPULATE
15 (
16 p_api_version_number IN NUMBER
17 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
19 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
20 ,x_msg_count OUT NOCOPY NUMBER
21 ,x_msg_data OUT NOCOPY VARCHAR2
22 ,x_return_status OUT NOCOPY VARCHAR2
23 ,p_object IN VARCHAR2
24 ,p_start_date IN DATE
25 ,p_end_date IN DATE
26 ,p_para_num IN NUMBER
27 --,p_mode IN VARCHAR2
28 ) IS
29
30 l_profile NUMBER;
31 v_error_code NUMBER;
32 v_error_text VARCHAR2(1500);
33 l_last_update_date DATE;
34 l_start_date DATE;
35 l_end_date DATE;
36 l_user_id NUMBER := FND_GLOBAL.USER_ID();
37 l_sysdate DATE := SYSDATE;
38 l_api_version_number CONSTANT NUMBER := 1.0;
39 l_api_name CONSTANT VARCHAR2(30) := 'BIM_LEAD_FACTS_PKG';
40 l_success VARCHAR2(3);
41 l_temp DATE;
42 l_mesg_text VARCHAR2(100);
43 l_load_type VARCHAR2(100);
44 l_period_error VARCHAR2(5000);
45 l_currency_error VARCHAR2(5000);
46 l_err_code NUMBER;
47 l_temp_start_date DATE;
48 l_temp_end_date DATE;
49 l_temp_p_end_date DATE;
50 l_status VARCHAR2(5);
51 l_industry VARCHAR2(5);
52 l_schema VARCHAR2(30);
53 l_return BOOLEAN;
54 BEGIN
55 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
56 fnd_message.set_name('BIM','BIM_R_START_FACTS');
57 fnd_message.set_token('P_OBJECT', 'LEADS', FALSE);
58 fnd_file.put_line(fnd_file.log,fnd_message.get);
59
60
61 -- Standard call to check for call compatibility.
62 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
63 p_api_version_number,
64 l_api_name,
65 g_pkg_name)
66 THEN
67 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
68 END IF;
69
70 -- Initialize message list if p_init_msg_list is set to TRUE.
71 IF FND_API.to_Boolean( p_init_msg_list )
72 THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 -- Initialize API return status to SUCCESS
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 -- Debug Message
80 -- AMS_UTILITY_PVT.debug_message('Private API: ' || 'Running the populate procedure');
81
82 /* Find if the data will be loaded for the first time or not.*/
83 DECLARE
84 CURSOR chk_history_data IS
85 SELECT MIN(start_date),MAX(end_date)
86 FROM bim_rep_history
87 WHERE object = 'LEADS';
88 BEGIN
89 OPEN chk_history_data;
90 FETCH chk_history_data INTO l_start_date,l_end_date;
91 CLOSE chk_history_data;
92 EXCEPTION
93 WHEN OTHERS THEN
94 FND_MSG_PUB.Count_And_Get (
95 -- p_encoded => FND_API.G_FALSE,
96 p_count => x_msg_count,
97 p_data => x_msg_data
98 );
99 END;
100
101
102 IF(trunc(p_end_date) = trunc(sysdate)) THEN
103 l_temp_p_end_date := trunc(p_end_date) - 1;
104 ELSE
105 l_temp_p_end_date := trunc(p_end_date);
106 END IF;
107
108 IF (l_end_date IS NOT NULL AND p_start_date IS NOT NULL)
109 THEN
110 fnd_message.set_name('BIM','BIM_R_FIRST_LOAD');
111 fnd_message.set_token('END_DATE',to_char(l_end_date,'DD-MON-RR'),FALSE);
112 fnd_file.put_line(fnd_file.log,fnd_message.get);
113
114 RAISE FND_API.G_EXC_ERROR;
115
116 ELSIF (l_end_date IS NULL AND p_start_date IS NULL) THEN
117 fnd_message.set_name('BIM','BIM_R_FIRST_SUBSEQUENT');
118 fnd_file.put_line(fnd_file.log,fnd_message.get);
119 RAISE FND_API.G_EXC_ERROR;
120 END IF;
121
122
123 IF p_start_date IS NOT NULL THEN
124
125 IF (p_start_date >= l_temp_p_end_date) THEN
126 fnd_message.set_name('BIM','BIM_R_DATE_VALIDATION');
127 fnd_file.put_line(fnd_file.log,fnd_message.get);
128 RAISE FND_API.G_EXC_ERROR;
129 END IF;
130
131 l_temp_start_date := trunc(p_start_date);
132 l_temp_end_date := trunc(l_temp_p_end_date);
133 l_load_type := 'FIRST_LOAD';
134 LOAD_DATA(p_start_date => l_temp_start_date
135 ,p_end_date => l_temp_end_date
136 ,p_api_version_number => l_api_version_number
137 ,p_init_msg_list => FND_API.G_FALSE
138 ,x_msg_count => x_msg_count
139 ,x_msg_data => x_msg_data
140 ,x_return_status => x_return_status
141 );
142
143 ELSE
144 IF l_end_date IS NOT NULL THEN
145
146 IF (l_temp_p_end_date <= l_end_date) THEN
147 fnd_message.set_name('BIM','BIM_R_SUBSEQUENT_LOAD');
148 fnd_message.set_token('END_DATE',to_char(l_end_date,'DD-MON-RR'),FALSE);
149 fnd_file.put_line(fnd_file.log,fnd_message.get);
150 RAISE FND_API.g_exc_error;
151 END IF;
152
153 l_temp_start_date := trunc(l_end_date) + 1;
154 l_temp_end_date := trunc(l_temp_p_end_date);
155 l_load_type := 'SUBSEQUENT_LOAD';
156
157 LOAD_DATA(p_start_date => l_temp_start_date
158 ,p_end_date => l_temp_end_date
159 ,p_api_version_number => l_api_version_number
160 ,p_init_msg_list => FND_API.G_FALSE
161 ,x_msg_count => x_msg_count
162 ,x_msg_data => x_msg_data
163 ,x_return_status => x_return_status
164 );
165
166
167 END IF;
168
169 END IF;
170
171
172 IF x_return_status = FND_API.g_ret_sts_error
173 THEN
174 RAISE FND_API.g_exc_error;
175 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
176 RAISE FND_API.g_exc_unexpected_error;
177 END IF;
178
179 --Standard check of commit
180
181 IF FND_API.To_Boolean ( p_commit ) THEN
182 COMMIT WORK;
183 END IF;
184
185 COMMIT;
186
187 fnd_message.set_name('BIM','BIM_R_END_FACTS');
188 fnd_message.set_token('OBJECT_NAME', 'LEADS', FALSE);
189 fnd_file.put_line(fnd_file.log,fnd_message.get);
190
191 -- Standard call to get message count and if count is 1, get message info.
192 FND_MSG_PUB.Count_And_Get
193 (p_count => x_msg_count,
194 p_data => x_msg_data
195 );
196
197 EXCEPTION
198
199 WHEN FND_API.G_EXC_ERROR THEN
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 -- Standard call to get message count and if count=1, get the message
202 FND_msg_PUB.Count_And_Get (
203 -- p_encoded => FND_API.G_FALSE,
204 p_count => x_msg_count,
205 p_data => x_msg_data
206 );
207
208
209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
210
211 /* FOR l_counter IN 1 .. x_msg_count
212 LOOP
213 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
214 fnd_msg_pub.dump_msg(l_counter);
215 end loop; */
216
217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
218 -- Standard call to get message count and if count=1, get the message
219 FND_msg_PUB.Count_And_Get (
220 --p_encoded => FND_API.G_FALSE,
221 p_count => x_msg_count,
222 p_data => x_msg_data
223 );
224
225 WHEN OTHERS THEN
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
228 THEN
229 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
230 END IF;
231 -- Standard call to get message count and if count=1, get the message
232 FND_msg_PUB.Count_And_Get (
233 -- p_encoded => FND_API.G_FALSE,
234 p_count => x_msg_count,
235 p_data => x_msg_data
236 );
237
238 END POPULATE;
239
240 --------------------------------------------------------------------------------------------------
241 /* This procedure will insert a HISTORY record whenever first or subsequent load is run */
242 --------------------------------------------------------------------------------------------------
243
244 PROCEDURE LOG_HISTORY
245 (--p_api_version_number IN NUMBER
246 --,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
247 --,x_msg_count OUT NOCOPY NUMBER
248 --,x_msg_data OUT NOCOPY VARCHAR2
249 --,x_return_status OUT NOCOPY VARCHAR2
250 p_object IN VARCHAR2,
251 p_start_date IN DATE DEFAULT NULL,
252 p_end_date IN DATE DEFAULT NULL
253 )
254 IS
255 l_user_id NUMBER := FND_GLOBAL.USER_ID();
256 l_sysdate DATE := SYSDATE;
257 l_api_version_number CONSTANT NUMBER := 1.0;
258 l_api_name CONSTANT VARCHAR2(30) := 'BIM_LEAD_FACTS_PKG';
259 l_success VARCHAR2(3);
260
261 BEGIN
262
263 -- Debug Message
264 --AMS_UTILITY_PVT.debug_message('Private API: ' || 'Running the LOG_HISTORY procedure ');
265
266 /* -- Standard call to check for call compatibility.
267 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
268 p_api_version_number,
269 l_api_name,
270 g_pkg_name)
271 THEN
272 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
273 END IF;
274
275 -- Initialize message list if p_init_msg_list is set to TRUE.
276 IF FND_API.to_Boolean( p_init_msg_list )
277 THEN
278 FND_msg_PUB.initialize;
279 END IF;
280
281 -- Initialize API return status to SUCCESS
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283 */
284
285 INSERT INTO bim_rep_history
286 (CREATION_DATE,
287 LAST_UPDATE_DATE,
288 CREATED_BY,
289 LAST_UPDATED_BY,
290 OBJECT,
291 OBJECT_LAST_UPDATED_DATE,
292 START_DATE,
293 END_DATE)
294 VALUES
295 (sysdate,
296 sysdate,
297 l_user_id,
298 l_user_id,
299 p_object,
300 sysdate,
301 p_start_date,
302 p_end_date);
303
304 /* -- Standard call to get message count and if count is 1, get message info.
305 FND_msg_PUB.Count_And_Get
306 (p_count => x_msg_count,
307 p_data => x_msg_data
308 );
309 EXCEPTION
310
311 WHEN FND_API.G_EXC_ERROR THEN
312 x_return_status := FND_API.G_RET_STS_ERROR;
313 -- Standard call to get message count and if count=1, get the message
314 FND_msg_PUB.Count_And_Get (
315 -- p_encoded => FND_API.G_FALSE,
316 p_count => x_msg_count,
317 p_data => x_msg_data
318 );
319
320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
321 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
322 -- Standard call to get message count and if count=1, get the message
323 FND_msg_PUB.Count_And_Get (
324 --p_encoded => FND_API.G_FALSE,
325 p_count => x_msg_count,
326 p_data => x_msg_data
327 );
328
329 WHEN OTHERS THEN
330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
331 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
332 THEN
333 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
334 END IF;
335 -- Standard call to get message count and if count=1, get the message
336 FND_msg_PUB.Count_And_Get (
337 -- p_encoded => FND_API.G_FALSE,
338 p_count => x_msg_count,
339 p_data => x_msg_data
340 );
341 */
342 --COMMIT;
343
344 END LOG_HISTORY;
345
346 --------------------------------------------------------------------------------------------------
347 -- This procedure will populates all the data required into daily facts and weekly facts.
348 --
349 -- PROCEDURE LOAD_DATA
350 --------------------------------------------------------------------------------------------------
351
352 PROCEDURE LOAD_DATA
353 ( p_start_date IN DATE
354 ,p_end_date IN DATE
355 ,p_api_version_number IN NUMBER
356 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
357 ,x_msg_count OUT NOCOPY NUMBER
358 ,x_msg_data OUT NOCOPY VARCHAR2
359 ,x_return_status OUT NOCOPY VARCHAR2
360 )
361 IS
362 l_user_id NUMBER := FND_GLOBAL.USER_ID();
363 l_start_date DATE;
364 l_end_date DATE;
365 l_last_update_date DATE;
366 l_success VARCHAR2(3);
367 l_wkdt DATE;
368 l_noleads NUMBER;
369 l_nooppor NUMBER;
370 l_orders NUMBER;
371 l_noposresp NUMBER;
372 l_revenue NUMBER;
373 l_forecasted_cost NUMBER;
374 l_actual_cost NUMBER;
375 l_targeted_customer NUMBER;
376 l_noofnew_customer NUMBER;
377 l_temp NUMBER;
378 l_tempo NUMBER;
379 l_seq NUMBER;
380 l_seqw NUMBER;
381 l_api_version_number CONSTANT NUMBER := 1.0;
382 l_api_name CONSTANT VARCHAR2(30) := 'LOAD_DATA';
383 l_seq_name VARCHAR(100);
384 l_def_tablespace VARCHAR2(100);
385 l_index_tablespace VARCHAR2(100);
386 l_oracle_username VARCHAR2(100);
387 l_table_name VARCHAR2(100);
388 l_temp_msg VARCHAR2(100);
389
390 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
391
392 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
393
394 i NUMBER;
395 l_min_start_date DATE;
396
397
398
399 l_org_id number;
400
401 CURSOR get_org_id IS
402 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
403 FROM dual;
404
405
406 l_status VARCHAR2(5);
407 l_industry VARCHAR2(5);
408 l_schema VARCHAR2(30);
409 l_return BOOLEAN;
410
411 BEGIN
412 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
413
414 -- Standard call to check for call compatibility.
415 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
416 p_api_version_number,
417 l_api_name,
418 g_pkg_name)
419 THEN
420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
421 END IF;
422
423 -- Initialize message list if p_init_msg_list is set to TRUE.
424 IF FND_API.to_Boolean( p_init_msg_list )
425 THEN
426 FND_msg_PUB.initialize;
427 END IF;
428
429 -- Initialize API return status to SUCCESS
430 x_return_status := FND_API.G_RET_STS_SUCCESS;
431
432 --ams_utility_pvt.write_conc_log('BIM_LEAD_FACTS_PKG: Running the Load_data ');
433
434 -- The below four commands are necessary for the purpose of the parallel insertion */
435 --COMMIT;
436
437 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
438
439 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_lead_daily_facts nologging ';
440
441 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'.bim_r_lead_weekly_facts nologging ';
442
443 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_lead_daily_facts_s CACHE 1000 ';
444
445 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_lead_weekly_facts_s CACHE 1000 ';
446
447 /* Dropping INdexes */
448 BIM_UTL_PKG.DROP_INDEX('BIM_R_LEAD_DAILY_FACTS');
449 BIM_UTL_PKG.DROP_INDEX('BIM_R_LEAD_WEEKLY_FACTS');
450
451
452
453 l_table_name := 'BIM_R_LEAD_DAILY_FACTS';
454 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
455 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
456 fnd_file.put_line(fnd_file.log,fnd_message.get);
457
458
459 -- dbms_output.put_Line('JUST BEFORE THE MAIN INSERT STATMENT');
460
461
462
463 INSERT /*+ append parallel(LDF,1) */
464 INTO bim_r_lead_daily_facts LDF
465 (
466 lead_daily_transaction_id
467 ,creation_date
468 ,last_update_date
469 ,created_by
470 ,last_updated_by
471 ,last_update_login
472 ,transaction_create_date
473 ,group_id
474 ,lead_rank_id
475 ,lead_source
476 ,lead_status
477 ,open_flag
478 ,object_type
479 ,object_id
480 ,region
481 ,country
482 ,business_unit_id
483 ,year
484 ,qtr
485 ,month
486 ,leads_open
487 ,leads_closed
488 ,leads_new
489 ,leads_dead
490 ,leads_changed
491 ,leads_unchanged
492 ,leads_assigned
493 ,opportunities
494 ,opportunities_open
495 ,quotes
496 ,quotes_open
497 ,orders
498 ,weekend_date
499 )
500 SELECT /*+ parallel(OUTER,1) */
501 bim_r_lead_daily_facts_s.nextval
502 ,sysdate
503 ,sysdate
504 ,-1
505 ,-1
506 ,-1
507 ,transaction_create_date
508 ,group_id
509 ,lead_rank_id
510 ,lead_source
511 ,lead_status
512 ,open_flag
513 ,object_type
514 ,object_id
515 ,region
516 ,country
517 ,business_unit_id
518 ,year
519 ,qtr
520 ,month
521 ,leads_open
522 ,leads_closed
523 ,leads_new
524 ,leads_dead
525 ,leads_changed
526 ,leads_unchanged
527 ,leads_assigned
528 ,opportunities
529 ,opportunities_open
530 ,quotes
531 ,quotes_open
532 ,orders
533 ,weekend_date
534 FROM
535 (
536 SELECT
537 inner.group_id group_id
538 ,inner.transaction_create_date transaction_create_date
539 ,inner.lead_rank_id lead_rank_id
540 ,inner.lead_source lead_source
541 ,inner.lead_status lead_status
542 ,inner.open_flag open_flag
543 ,inner.object_type object_type
544 ,inner.object_id object_id
545 ,loc.region region
546 ,inner.country country
547 ,inner.business_unit_id business_unit_id
548 ,a.fiscal_year year
549 ,a.fiscal_qtr qtr
550 ,a.fiscal_month month
551 ,inner.leads_open leads_open
552 ,inner.leads_closed leads_closed
553 ,inner.leads_new leads_new
554 ,inner.leads_dead leads_dead
555 ,inner.leads_changed leads_changed
556 ,inner.leads_unchanged leads_unchanged
557 ,inner.leads_assigned leads_assigned
558 ,inner.opportunities opportunities
559 ,inner.opportunities_open opportunities_open
560 ,inner.quotes quotes
561 ,inner.quotes_open quotes_open
562 ,inner.orders orders
563 ,(decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
564 ,'TRUE'
565 ,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
566 ,'TRUE'
567 ,inner.transaction_create_date
568 ,'FALSE'
569 ,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
570 ,'FALSE'
571 ,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
572 ,'FALSE'
573 ,last_day(inner.transaction_create_date)))) weekend_date
574 FROM (
575 SELECT
576 metric.group_id group_id
577 ,metric.transaction_create_date transaction_create_date
578 ,metric.lead_rank_id lead_rank_id
579 ,metric.lead_source lead_source
580 ,metric.lead_status lead_status
581 ,metric.open_flag open_flag
582 ,metric.object_type object_type
583 ,metric.object_id object_id
584 ,metric.country country
585 ,metric.business_unit_id business_unit_id
586 ,sum(nvl(metric.leads_open,0)) leads_open
587 ,sum(nvl(metric.leads_closed,0)) leads_closed
588 ,sum(nvl(metric.leads_new,0)) leads_new
589 ,sum(nvl(metric.leads_dead,0)) leads_dead
590 ,sum(nvl(metric.leads_changed,0)) leads_changed
591 ,sum(nvl(metric.leads_unchanged,0)) leads_unchanged
592 ,sum(nvl(metric.leads_assigned,0)) leads_assigned
593 ,sum(nvl(metric.opportunities,0)) opportunities
594 ,sum(nvl(metric.opportunities_open,0)) opportunities_open
595 ,sum(nvl(metric.quotes,0)) quotes
596 ,sum(nvl(metric.quotes_open,0)) quotes_open
597 ,sum(nvl(metric.orders,0)) orders
598 FROM (
599 SELECT
600 x.assign_sales_group_id group_id
601 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))transaction_create_date
602 ,x.lead_rank_id lead_rank_id
603 ,x.source_system lead_source
604 ,x.status_code lead_status
605 ,decode(x.status_open_flag,'Y','Yes','No') open_flag
606 ,a.parent_object_type object_type
607 ,a.parent_object_id object_id
608 ,x.country country
609 ,a.business_unit_id business_unit_id
610 ,sum(decode(Y.opp_open_status_flag,'Y',1,0)) leads_open
611 ,sum(decode(Y.opp_open_status_flag,'Y',0,1)) leads_closed
612 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',1,0),0)) leads_new
613 ,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',1,0))) leads_dead
614 ,sum(decode(X.created_by,X.last_updated_by,0,1)) leads_changed
615 ,sum(decode(X.created_by,X.last_updated_by,1,0)) leads_unchanged
616 ,sum(decode(Y.opp_open_status_flag,'Y',decode(X.assign_to_salesforce_id,null,0,1),0)) leads_assigned
617 ,0 opportunities
618 ,0 opportunities_open
619 ,0 quotes
620 ,0 quotes_open
621 ,0 orders
622 FROM
623 as_sales_leads X
624 ,as_statuses_b Y
625 ,bim_r_source_codes A
626 WHERE
627 trunc(X.creation_date) between p_start_date and p_end_date
628 AND X.status_code = Y.status_code
629 AND Y.lead_flag = 'Y'
630 AND Y.enabled_flag = 'Y'
631 AND NVL(X.DELETED_FLAG,'N') <> 'Y'
632 AND X.source_promotion_id = a.source_code_id(+)
633 GROUP BY
634 x.assign_sales_group_id
635 ,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))
636 ,x.lead_rank_id
637 ,x.source_system
638 ,x.status_code
639 ,decode(x.status_open_flag,'Y','Yes','No')
640 ,a.parent_object_type
641 ,a.parent_object_id
642 ,x.country
643 ,a.business_unit_id
644 ---------
645 UNION ALL
646 ---------
647 SELECT
648 x.assign_sales_group_id group_id
649 ,trunc(decode(Y.OPP_OPEN_STATUS_FLAG,'Y',d.creation_date,d.last_update_date)) transaction_create_date
650 ,x.lead_rank_id lead_rank_id
651 ,x.source_system lead_source
652 ,x.status_code lead_status
653 ,decode(x.status_open_flag,'Y','Yes','No') open_flag
654 ,a.parent_object_type object_type
655 ,a.parent_object_id object_id
656 ,x.country country
657 ,a.business_unit_id business_unit_id
658 ,0 leads_open
659 ,0 leads_closed
660 ,0 leads_new
661 ,0 leads_dead
662 ,0 leads_changed
663 ,0 leads_unchanged
664 ,0 leads_assigned
665 ,count(e.lead_id) opportunities
666 ,sum(decode(Y.opp_open_status_flag,'Y',1,0)) opportunities_open
667 ,0 quotes
668 ,0 quotes_open
669 ,0 orders
670 FROM
671 as_sales_leads X
672 ,as_statuses_b Y
673 ,bim_r_source_codes A
674 ,as_sales_lead_opportunity D
675 ,as_leads_all E
676 WHERE
677 trunc(d.creation_date) between p_start_date and p_end_date
678 AND X.sales_lead_id = D.sales_lead_id
679 AND D.opportunity_id = E.lead_id
680 AND E.status = Y.status_code
681 AND NVL(X.DELETED_FLAG,'N') <> 'Y'
682 AND X.source_promotion_id = a.source_code_id(+)
683 GROUP BY
684 x.assign_sales_group_id
685 ,trunc(decode(Y.OPP_OPEN_STATUS_FLAG,'Y',d.creation_date,d.last_update_date))
686 ,x.lead_rank_id
687 ,x.source_system
688 ,x.status_code
689 ,decode(x.status_open_flag,'Y','Yes','No')
690 ,a.parent_object_type
691 ,a.parent_object_id
692 ,x.country
693 ,a.business_unit_id
694 ---------
695 UNION ALL
696 ---------
697 SELECT
698 x.assign_sales_group_id group_id
699 ,trunc(g.creation_date) transaction_create_date
700 ,x.lead_rank_id lead_rank_id
701 ,x.source_system lead_source
702 ,x.status_code lead_status
703 ,decode(x.status_open_flag,'Y','Yes','No') open_flag
704 ,a.parent_object_type object_type
705 ,a.parent_object_id object_id
706 ,x.country country
707 ,a.business_unit_id business_unit_id
708 ,0 leads_open
709 ,0 leads_closed
710 ,0 leads_new
711 ,0 leads_dead
712 ,0 leads_changed
713 ,0 leads_unchanged
714 ,0 leads_assigned
715 ,0 opportunities
716 ,0 opportunities_open
717 ,count(g.quote_header_id) quotes
718 ,sum(decode(g.resource_id, null,0,decode(g.order_id, null, 1,0))) quotes_open
719 ,0 orders
720 FROM
721 as_sales_leads X
722 ,as_statuses_b Y
723 ,bim_r_source_codes A
724 ,as_sales_lead_opportunity D
725 ,as_leads_all E
726 ,aso_quote_related_objects F
727 ,aso_quote_headers_all G
728 WHERE
729 trunc(f.creation_date) between p_start_date and p_end_date
730 AND X.sales_lead_id = D.sales_lead_id
731 AND D.opportunity_id = E.lead_id
732 AND F.object_id = E.lead_id
733 AND F.relationship_type_code = 'OPP_QUOTE'
734 AND F.quote_object_type_code = 'HEADER'
735 AND F.quote_object_id = G.quote_header_id
736 AND NVL(G.quote_expiration_date, p_start_date+1) > p_start_date
737 AND NVL(X.DELETED_FLAG,'N') <> 'Y'
738 AND X.source_promotion_id = a.source_code_id(+)
739 AND X.status_code = Y.status_code
740 AND Y.lead_flag = 'Y'
741 AND Y.enabled_flag = 'Y'
742 GROUP BY
743 x.assign_sales_group_id
744 ,g.creation_date
745 ,x.lead_rank_id
746 ,x.source_system
747 ,x.status_code
748 ,decode(x.status_open_flag,'Y','Yes','No')
749 ,a.parent_object_type
750 ,a.parent_object_id
751 ,x.country
752 ,a.business_unit_id
753 ---------
754 UNION ALL
755 ---------
756 SELECT
757 x.assign_sales_group_id group_id
758 ,trunc(i.creation_date) transaction_create_date
759 ,x.lead_rank_id lead_rank_id
760 ,x.source_system lead_source
761 ,x.status_code lead_status
762 ,decode(x.status_open_flag,'Y','Yes','No') open_flag
763 ,a.parent_object_type object_type
764 ,a.parent_object_id object_id
765 ,x.country country
766 ,a.business_unit_id business_unit_id
767 ,0 leads_open
768 ,0 leads_closed
769 ,0 leads_new
770 ,0 leads_dead
771 ,0 leads_changed
772 ,0 leads_unchanged
773 ,0 leads_assigned
774 ,0 opportunities
775 ,0 opportunities_open
776 ,0 quotes
777 ,0 quotes_open
778 ,count(h.header_id) orders
779 FROM
780 as_sales_leads X
781 ,as_statuses_b Y
782 ,bim_r_source_codes A
783 ,as_sales_lead_opportunity D
784 ,as_leads_all E
785 ,aso_quote_related_objects F
786 ,aso_quote_headers_all G
787 ,oe_order_headers_all H
788 ,oe_order_lines_all I
789 WHERE
790 trunc(i.creation_date) between p_start_date and p_end_date
791 AND X.sales_lead_id = D.sales_lead_id
792 AND D.opportunity_id = E.lead_id
793 AND F.object_id = E.lead_id
794 AND F.relationship_type_code = 'OPP_QUOTE'
795 AND F.quote_object_type_code = 'HEADER'
796 AND F.quote_object_id = G.quote_header_id
797 AND G.order_id = H.HEADER_ID
798 AND H.header_id = I.header_id
799 AND NVL(X.DELETED_FLAG,'N') <> 'Y'
800 AND X.source_promotion_id = a.source_code_id(+)
801 AND X.status_code = Y.status_code
802 AND Y.lead_flag = 'Y'
803 AND Y.enabled_flag = 'Y'
804 GROUP BY
805 x.assign_sales_group_id
806 ,i.creation_date
807 ,x.lead_rank_id
808 ,x.source_system
809 ,x.status_code
810 ,decode(x.status_open_flag,'Y','Yes','No')
811 ,a.parent_object_type
812 ,a.parent_object_id
813 ,x.country
814 ,a.business_unit_id
815 ) METRIC
816 GROUP BY
817 metric.group_id
818 ,metric.transaction_create_date
819 ,metric.lead_rank_id
820 ,metric.lead_source
821 ,metric.lead_status
822 ,metric.open_flag
823 ,metric.object_type
824 ,metric.object_id
825 ,metric.country
826 ,metric.business_unit_id
827 ) INNER
828 ,bim_r_locations LOC
829 ,bim_intl_dates A
830 WHERE
831 A.trdate = INNER.transaction_create_date
832 AND LOC.country (+) = INNER.country
833 )OUTER;
834
835 COMMIT;
836
837
838 /***************************************************************/
839
840
841 l_table_name := 'BIM_R_LEAD_DAILY_FACTS';
842 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
843 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
844 fnd_file.put_line(fnd_file.log,fnd_message.get);
845
846 -- Analyze the daily facts table
847 DBMS_STATS.gather_table_stats('BIM','BIM_R_LEAD_DAILY_FACTS', estimate_percent => 5,
848 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
849
850
851 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_lead_daily_facts_s CACHE 20';
852
853 /***************************************************************/
854
855 /* INSERT INTO WEEKLY SUMMARY TABLE */
856
857 /* Here we are inserting the summarized data into the weekly facts by taking it from the daily facts.
858 For every week we have a record since we group by that weekend date which is nothing but the Load date. */
859
860 l_table_name := 'BIM_R_LEAD_WEEKLY_FACTS';
861 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
862 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
863 fnd_file.put_line(fnd_file.log,fnd_message.get);
864
865 EXECUTE IMMEDIATE 'truncate table '||l_schema||'.bim_r_lead_weekly_facts';
866
867 /*BEGIN BLOCK FOR THE WEEKLY INSERT */
868
869 l_table_name := 'bim_r_lead_weekly_facts';
870 l_seq_name := 'bim_r_lead_weekly_facts_s';
871
872 INSERT /*+ append parallel(LWF,1) */
873 INTO bim_r_lead_weekly_facts LWF
874 (
875 lead_weekly_transaction_id
876 ,creation_date
877 ,last_update_date
878 ,created_by
879 ,last_updated_by
880 ,weekend_date
881 ,group_id
882 ,lead_rank_id
883 ,lead_source
884 ,lead_status
885 ,open_flag
886 ,object_type
887 ,object_id
888 ,region
889 ,country
890 ,business_unit_id
891 ,year
892 ,qtr
893 ,month
894 ,leads_open
895 ,leads_closed
896 ,leads_new
897 ,leads_dead
898 ,leads_changed
899 ,leads_unchanged
900 ,leads_assigned
901 ,opportunities
902 ,opportunities_open
903 ,quotes
904 ,quotes_open
905 ,orders
906 )
907 SELECT /*+ parallel(INNER,1) */
908 bim_r_lead_weekly_facts_s.nextval
909 ,sysdate
910 ,sysdate
911 ,l_user_id
912 ,l_user_id
913 ,weekend_date
914 ,group_id
915 ,lead_rank_id
916 ,lead_source
917 ,lead_status
918 ,open_flag
919 ,object_type
920 ,object_id
921 ,region
922 ,country
923 ,business_unit_id
924 ,year
925 ,qtr
926 ,month
927 ,leads_open
928 ,leads_closed
929 ,leads_new
930 ,leads_dead
931 ,leads_changed
932 ,leads_unchanged
933 ,leads_assigned
934 ,opportunities
935 ,opportunities_open
936 ,quotes
937 ,quotes_open
938 ,orders
939 FROM
940 (
941 SELECT
942 weekend_date weekend_date
943 ,group_id group_id
944 ,lead_rank_id lead_rank_id
945 ,lead_source lead_source
946 ,lead_status lead_status
947 ,open_flag open_flag
948 ,object_type object_type
949 ,object_id object_id
950 ,region region
951 ,country country
952 ,business_unit_id business_unit_id
953 ,year year
954 ,qtr qtr
955 ,month month
956 ,sum(leads_open) leads_open
957 ,sum(leads_closed) leads_closed
958 ,sum(leads_new) leads_new
959 ,sum(leads_dead) leads_dead
960 ,sum(leads_changed) leads_changed
961 ,sum(leads_unchanged) leads_unchanged
962 ,sum(leads_assigned) leads_assigned
963 ,sum(opportunities) opportunities
964 ,sum(opportunities_open) opportunities_open
965 ,sum(quotes) quotes
966 ,sum(quotes_open) quotes_open
967 ,sum(orders) orders
968 FROM bim_r_lead_daily_facts
969 -- WHERE transaction_create_date between trunc(p_start_date) and trunc(p_end_date) + 0.99999
970 GROUP BY
971 weekend_date
972 ,year
973 ,qtr
974 ,month
975 ,group_id
976 ,lead_rank_id
977 ,lead_source
978 ,lead_status
979 ,open_flag
980 ,object_type
981 ,object_id
982 ,region
983 ,country
984 ,business_unit_id
985 )INNER;
986
987 LOG_HISTORY('LEADS', p_start_date, p_end_date);
988
989
990 COMMIT;
991
992 l_table_name := 'BIM_R_LEAD_WEEKLY_FACTS';
993 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
994 fnd_message.set_token('TABLE_NAME',l_table_name,FALSE);
995 fnd_file.put_line(fnd_file.log,fnd_message.get);
996
997 -- Analyze the daily facts table
998 DBMS_STATS.gather_table_stats('BIM','BIM_R_LEAD_WEEKLY_FACTS', estimate_percent => 5,
999 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1000
1001
1002 /* Recreating Indexes */
1003 BIM_UTL_PKG.CREATE_INDEX('BIM_R_LEAD_DAILY_FACTS');
1004 BIM_UTL_PKG.CREATE_INDEX('BIM_R_LEAD_WEEKLY_FACTS');
1005
1006
1007
1008 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'.bim_r_lead_weekly_facts_s CACHE 20';
1009
1010
1011
1012 EXCEPTION
1013
1014 WHEN FND_API.G_EXC_ERROR THEN
1015 x_return_status := FND_API.G_RET_STS_ERROR;
1016 -- Standard call to get message count and if count=1, get the message
1017 FND_msg_PUB.Count_And_Get (
1018 -- p_encoded => FND_API.G_FALSE,
1019 p_count => x_msg_count,
1020 p_data => x_msg_data
1021 );
1022
1023 ams_utility_pvt.write_conc_log('BIM_LEAD_FACTS_PKG:LOAD_DATA:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
1024
1025 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1026
1027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1028
1029 -- Standard call to get message count and if count=1, get the message
1030 FND_msg_PUB.Count_And_Get (
1031 --p_encoded => FND_API.G_FALSE,
1032 p_count => x_msg_count,
1033 p_data => x_msg_data
1034 );
1035
1036 ams_utility_pvt.write_conc_log('BIM_LEAD_FACTS_PKG:LOAD_DATA:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
1037
1038 WHEN OTHERS THEN
1039
1040 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041
1042 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
1043 THEN
1044 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
1045 END IF;
1046
1047 -- Standard call to get message count and if count=1, get the message
1048 FND_msg_PUB.Count_And_Get (
1049 -- p_encoded => FND_API.G_FALSE,
1050 p_count => x_msg_count,
1051 p_data => x_msg_data
1052 );
1053
1054 ams_utility_pvt.write_conc_log('BIM_LEAD_FACTS_PKG:LOAD_DATA:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
1055
1056
1057 END LOAD_DATA;
1058
1059
1060 END BIM_LEAD_FACTS_PKG;
1061