[Home] [Help]
PACKAGE BODY: APPS.BIM_I_LEAD_FACTS_PKG
Source
1 PACKAGE BODY BIM_I_LEAD_FACTS_PKG AS
2 /*$Header: bimildfb.pls 120.1 2005/10/11 05:38:48 sbassi noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_I_LEAD_FACTS_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimildfb.pls';
6 l_global_currency_code varchar2(20);
7
8
9 -- Checks for any missing currency from Lead facts table
10
11 FUNCTION Check_Missing_Rates (p_start_date IN Date)
12 Return NUMBER
13 AS
14 l_global_rate Varchar2(30);
15 l_cnt_miss_rate Number := 0;
16 l_msg_name Varchar2(40);
17
18 CURSOR C_missing_rates
19 IS
20 SELECT from_currency from_currency,
21 decode(conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),lead_creation_date) lead_creation_date
22 FROM bim_i_lead_facts_stg
23 WHERE (conversion_rate < 0
24 OR conversion_rate IS NULL)
25 AND from_currency is not null
26 AND lead_creation_date >= p_start_date
27 ORDER BY from_currency;
28 BEGIN
29 l_msg_name := 'BIS_DBI_CURR_NO_LOAD';
30 SELECT COUNT(*) INTO l_cnt_miss_rate FROM bim_i_lead_facts_stg
31 WHERE
32 (conversion_rate < 0
33 OR conversion_rate IS NULL)
34 AND from_currency is not null
35 AND lead_creation_date >= p_start_date;
36
37 l_global_rate := BIS_COMMON_PARAMETERS.Get_Rate_Type;
38
39 If(l_cnt_miss_rate > 0 )
40 Then
41 FND_MESSAGE.Set_Name('FII',l_msg_name);
42 BIS_COLLECTION_UTILITIES.debug(l_msg_name||': '||FND_MESSAGE.get);
43 BIS_COLLECTION_UTILITIES.log('Conversion rate could not be found for the given currency. Please check output file for more details' );
44 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
45
46
47 l_global_currency_code := bis_common_parameters.get_currency_code;
48 FOR rate_record in C_missing_rates
49 LOOP
50 BIS_COLLECTION_UTILITIES.writeMissingRate(
51 p_rate_type => l_global_rate,
52 p_from_currency => rate_record.from_currency,
53 p_to_currency => l_global_currency_code,
54 p_date => rate_record.lead_creation_date);
55 END LOOP;
56 BIS_COLLECTION_UTILITIES.debug('before returning -1' );
57 RETURN -1;
58 ELSE
59 Return 1;
60 End If;
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 BIS_COLLECTION_UTILITIES.Debug('Error in Check_missing_rates:'||sqlerrm);
65 RAISE;
66 END Check_Missing_Rates;
67
68 ------------------------------------------------------------------------------------------------
69 ----
70 ----This procedure finds out if the user is trying to run first_load or subsequent load
71 ----and calls the load_data procedure with the specific parameters to each type of load
72 ----
73 ------------------------------------------------------------------------------------------------
74
75 PROCEDURE POPULATE
76 (
77 p_api_version_number IN NUMBER
78 ,p_init_msg_list IN VARCHAR2
79 ,p_validation_level IN NUMBER
80 ,p_commit IN VARCHAR2
81 ,x_msg_count OUT NOCOPY NUMBER
82 ,x_msg_data OUT NOCOPY VARCHAR2
83 ,x_return_status OUT NOCOPY VARCHAR2
84 ,p_start_date IN DATE
85 ,p_end_date IN DATE
86 ,p_para_num IN NUMBER
87 ,p_truncate_flg IN VARCHAR2
88 --,p_mode IN VARCHAR2
89 ) IS
90
91 l_object_name CONSTANT VARCHAR2(80) := 'BIM_LEADS';
92 l_conc_start_date DATE;
93 l_conc_end_date DATE;
94 l_start_date DATE;
95 l_end_date DATE;
96 l_user_id NUMBER := FND_GLOBAL.USER_ID();
97 l_api_version_number CONSTANT NUMBER := 1.0;
98 l_api_name CONSTANT VARCHAR2(30) := 'BIM_I_LEAD_FACTS_PKG';
99 l_mesg_text VARCHAR2(100);
100 l_load_type VARCHAR2(100);
101 l_global_date DATE;
102 l_missing_date BOOLEAN := FALSE;
103 l_sysdate DATE;
104
105
106 BEGIN
107
108 l_global_date:= bis_common_parameters.get_global_start_date;
109
110 IF NOT bis_collection_utilities.setup(l_object_name) THEN
111 bis_collection_utilities.log('Object BIM_LEADS Not Setup Properly');
112 RAISE FND_API.G_EXC_ERROR;
113 END IF;
114 bis_collection_utilities.log('Start of the Lead Facts Program');
115
116 -- Standard call to check for call compatibility.
117 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
118 p_api_version_number,
119 l_api_name,
120 g_pkg_name)
121 THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
123 END IF;
124
125 -- Initialize message list if p_init_msg_list is set to TRUE.
126 IF FND_API.to_Boolean( p_init_msg_list )
127 THEN
128 FND_MSG_PUB.initialize;
129 END IF;
130
131 -- Initialize API return status to SUCCESS
132 x_return_status := FND_API.G_RET_STS_SUCCESS;
133
134 bis_collection_utilities.get_last_refresh_dates(l_object_name,
135 l_conc_start_date,
136 l_conc_end_date,
137 l_start_date,
138 l_end_date);
139 IF (l_end_date IS NULL) THEN
140
141 IF (p_start_date IS NULL) THEN
142 bis_collection_utilities.log('Please run the Upadate Leads First Time Base Summary concurrent program before running this');
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146 --- Validate Time Dimension Tables
147 fii_time_api.check_missing_date (greatest(l_global_date,p_start_date), sysdate, l_missing_date);
148 IF (l_missing_date) THEN
149 bis_collection_utilities.log('Time dimension has atleast one missing date between ' || greatest(l_global_date,p_start_date) || ' and ' || sysdate);
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153
154 l_load_type := 'FIRST_LOAD';
155
156 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
157 ,p_end_date => sysdate
158 ,p_api_version_number => l_api_version_number
159 ,p_init_msg_list => FND_API.G_FALSE
160 ,x_msg_count => x_msg_count
161 ,x_msg_data => x_msg_data
162 ,x_return_status => x_return_status
163 );
164
165 ELSE
166 --i.e Incremental has to be executed.
167 IF p_truncate_flg = 'Y' THEN
168
169 l_load_type := 'FIRST_LOAD';
170 l_sysdate := sysdate;
171
172 FIRST_LOAD(p_start_date => greatest(l_global_date,p_start_date)
173 ,p_end_date => l_sysdate
174 ,p_api_version_number => l_api_version_number
175 ,p_init_msg_list => FND_API.G_FALSE
176 ,x_msg_count => x_msg_count
177 ,x_msg_data => x_msg_data
178 ,x_return_status => x_return_status
179 );
180 ELSE
181 --- Validate Time Dimension Tables
182 fii_time_api.check_missing_date (l_end_date, sysdate, l_missing_date);
183 IF (l_missing_date) THEN
184 bis_collection_utilities.log('Time dimension has atleast one missing date between ' || l_end_date || ' and ' || sysdate);
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 l_load_type := 'SUBSEQUENT_LOAD';
189
190 INCREMENTAL_LOAD(p_start_date => l_end_date +1/86400 -- add one second
191 ,p_end_date => sysdate
192 ,p_global_date =>l_global_date
193 ,p_api_version_number => l_api_version_number
194 ,p_init_msg_list => FND_API.G_FALSE
195 ,x_msg_count => x_msg_count
196 ,x_msg_data => x_msg_data
197 ,x_return_status => x_return_status
198 );
199 END IF;
200 END IF;
201
202
203 IF x_return_status = FND_API.g_ret_sts_error
204 THEN
205 RAISE FND_API.g_exc_error;
206 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
207 RAISE FND_API.g_exc_unexpected_error;
208 END IF;
209
210 --Standard check of commit
211
212 IF FND_API.To_Boolean ( p_commit ) THEN
213 COMMIT WORK;
214 END IF;
215
216 COMMIT;
217
218
219 -- Standard call to get message count and if count is 1, get message info.
220 FND_MSG_PUB.Count_And_Get
221 (p_count => x_msg_count,
222 p_data => x_msg_data
223 );
224
225 EXCEPTION
226
227 WHEN FND_API.G_EXC_ERROR THEN
228 x_return_status := FND_API.G_RET_STS_ERROR;
229 -- Standard call to get message count and if count=1, get the message
230 FND_msg_PUB.Count_And_Get (
231 -- p_encoded => FND_API.G_FALSE,
232 p_count => x_msg_count,
233 p_data => x_msg_data
234 );
235
236
237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
238
239 /* FOR l_counter IN 1 .. x_msg_count
240 LOOP
241 l_mesg_text := fnd_msg_pub.get (p_encoded => fnd_api.g_false);
242 fnd_msg_pub.dump_msg(l_counter);
243 end loop; */
244
245 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
246 -- Standard call to get message count and if count=1, get the message
247 FND_msg_PUB.Count_And_Get (
248 --p_encoded => FND_API.G_FALSE,
249 p_count => x_msg_count,
250 p_data => x_msg_data
251 );
252
253 WHEN OTHERS THEN
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
256 THEN
257 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
258 END IF;
259 -- Standard call to get message count and if count=1, get the message
260 FND_msg_PUB.Count_And_Get (
261 -- p_encoded => FND_API.G_FALSE,
262 p_count => x_msg_count,
263 p_data => x_msg_data
264 );
265
266 END POPULATE;
267
268
269 --------------------------------------------------------------------------------------------------
270 -- This procedure will populates all the data required into facts table for the first load.
271 --
272 -- PROCEDURE FIRST_LOAD
273 --------------------------------------------------------------------------------------------------
274
275 PROCEDURE FIRST_LOAD
276 ( p_start_date IN DATE
277 ,p_end_date IN DATE
278 ,p_api_version_number IN NUMBER
279 ,p_init_msg_list IN VARCHAR2
280 ,x_msg_count OUT NOCOPY NUMBER
281 ,x_msg_data OUT NOCOPY VARCHAR2
282 ,x_return_status OUT NOCOPY VARCHAR2
283 )
284 IS
285 l_user_id NUMBER := FND_GLOBAL.USER_ID();
286 l_api_version_number CONSTANT NUMBER := 1.0;
287 l_api_name CONSTANT VARCHAR2(30) := 'FIRST_LOAD';
288 l_table_name VARCHAR2(100);
289 l_conv_opp_status VARCHAR2(30);
290 l_dead_status VARCHAR2(30);
291 l_check_missing_rate NUMBER;
292 l_stmt VARCHAR2(50);
293 l_cert_level VARCHAR2(3);
294
295 l_status VARCHAR2(5);
296 l_industry VARCHAR2(5);
297 l_schema VARCHAR2(30);
298 l_return BOOLEAN;
299
300 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
301
302 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
303
304 i NUMBER;
305 l_min_start_date DATE;
306
307 l_org_id number;
308
309 CURSOR get_org_id IS
310 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
311 FROM dual;
312 l_sysdate date;
313
314 BEGIN
315
316 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
317
318 -- Standard call to check for call compatibility.
319 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
320 p_api_version_number,
321 l_api_name,
322 g_pkg_name)
323 THEN
324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
325 END IF;
326
327 -- Initialize message list if p_init_msg_list is set to TRUE.
328 IF FND_API.to_Boolean( p_init_msg_list )
329 THEN
330 FND_msg_PUB.initialize;
331 END IF;
332
333 -- Initialize API return status to SUCCESS
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335
336 --Get status codes for 'opportunity created' and 'dead lead'
337 l_conv_opp_status := nvl(FND_PROFILE.Value('AS_LEAD_LINK_STATUS'),'CONVERTED_TO_OPPORTUNITY');
338 l_dead_status := nvl(FND_PROFILE.Value('AS_DEAD_LEAD_STATUS'),'DEAD_LEAD');
339
340 --Find if the certification level is implemented or not
341 l_cert_level := nvl(FND_PROFILE.Value('HZ_DISPLAY_CERT_STATUS'),'NO');
342
343
344
345 /* Dropping INdexes */
346 BIM_UTL_PKG.DROP_INDEX('BIM_I_LEAD_FACTS');
347
348 /* Truncate Staging table */
349 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
350
351
352 l_table_name := 'BIM_I_LEAD_FACTS';
353 bis_collection_utilities.log('Running Initial Load of Lead Facts');
354
355 l_sysdate :=sysdate;
356
357 INSERT /*+ append parallel */
358 INTO bim_i_lead_facts_stg LDF
359 (
360 lead_id
361 ,lead_line_id
362 ,group_id
363 ,resource_id
364 ,lead_rank_id
365 ,lead_source
366 ,lead_status
367 ,lead_country
368 ,source_code_id
369 ,interest_type_id
370 ,primary_interest_code_id
371 ,secondary_interest_code_id
372 ,item_id
373 ,organization_id
374 ,lead_creation_date
375 ,lead_touched_date
376 ,lead_dead_date
377 ,channel_code
378 -- ,lead_amount
379 -- ,currency_code
380 ,close_reason
381 ,accept_flag
382 ,qualified_flag
383 ,source_primary_reference
384 ,source_secondary_reference
385 ,customer_id
386 ,cust_category
387 ,status_open_flag
388 ,lead_rank_score
389 ,expiration_date
390 -- ,conversion_rate
391 -- ,from_currency
392 ,product_category_id
393 ,CUSTOMER_FLAG
394 ,lead_name
395 )
396 SELECT
397 x.sales_lead_id lead_id
398 ,y.sales_lead_line_id lead_line_id
399 ,x.assign_sales_group_id group_id
400 ,x.assign_to_salesforce_id resource_id
401 ,x.lead_rank_id lead_rank_id
402 ,x.source_system lead_source
403 ,x.status_code lead_status
404 ,x.country lead_country
405 ,x.source_promotion_id source_code_id
406 ,y.interest_type_id interest_type_id
407 ,y.primary_interest_code_id primary_interest_code_id
408 ,y.secondary_interest_code_id secondary_interest_code_id
409 ,nvl(y.inventory_item_id,-1) item_id
410 ,decode(y.inventory_item_id,null,-1,nvl(y.organization_id,-1)) organization_id
411 ,trunc(x.creation_date) lead_creation_date
412 ,(CASE
413 WHEN (x.last_update_date > (x.creation_date+1/1440)) THEN trunc(x.last_update_date)
414 ELSE null
415 END
416 ) lead_touched_date
417 ,decode(x.status_code,l_dead_status,trunc(x.last_update_date),null) lead_dead_date
418 ,x.channel_code channel_code
419 -- ,fii_currency.convert_global_amt_primary(nvl(x.currency_code,'USD'),nvl(x.total_amount,0),X.creation_date) lead_amount
420 -- ,x.currency_code currency_code
421 ,x.close_reason close_reason
422 ,x.accept_flag accept_flag
423 ,x.qualified_flag qualified_flag
424 ,x.source_primary_reference source_primary_reference
425 ,x.source_secondary_reference source_secondary_reference
426 ,x.customer_id customer_id
427 ,NULL cust_category
428 ,x.status_open_flag status_open_flag
429 ,x.lead_rank_score lead_rank_score
430 ,x.expiration_date expiration_date
431 -- ,fii_currency.get_global_rate_primary(nvl(x.currency_code,'USD'),x.creation_date) conversion_rate
432 -- ,nvl(x.currency_code,'USD') from_currency
433 ,y.category_id product_category_id
434 ,'N' CUSTOMER_FLAG
435 ,x.DESCRIPTION
436 FROM
437 as_sales_leads X
438 ,as_sales_lead_lines Y
439 WHERE
440 X.creation_date between p_start_date and l_sysdate
441 AND X.sales_lead_id = Y.sales_lead_id(+) ;
442
443
444
445
446 COMMIT;
447
448 /*l_check_missing_rate := Check_Missing_Rates (p_start_date);
449 if (l_check_missing_rate = -1) then
450 BIS_COLLECTION_UTILITIES.debug('before truncating first time load' );
451 l_stmt := 'TRUNCATE table '||l_schema||'.bim_i_lead_facts_stg';
452 EXECUTE IMMEDIATE l_stmt;
453 commit;
454
455 x_return_status := FND_API.G_RET_STS_ERROR;
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 end if;
458 */
459
460 --update to get customer category
461
462 /*update bim.bim_i_lead_facts_stg stg
463 set stg.cust_category
464 = (select b.class_code from hz_code_assignments b
465 where
466 stg.customer_id=b.OWNER_TABLE_ID
467 and b.OWNER_TABLE_NAME='HZ_PARTIES'
468 and b.Primary_flag = 'Y'
469 and nvl(b.end_date_active,trunc(sysdate)) > = trunc(sysdate)
470 and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
471 and b.status='A')
472 where
473 exists
474 (select b.class_code from hz_code_assignments b
475 where
476 stg.customer_id=b.OWNER_TABLE_ID
477 and b.OWNER_TABLE_NAME='HZ_PARTIES'
478 and b.Primary_flag = 'Y'
479 and nvl(b.end_date_active,trunc(sysdate)) > = trunc(sysdate)
480 and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
481 and b.status='A');
482 */
483
484 update bim_i_lead_facts_stg stg
485 set stg.cust_category
486 = (select b.class_code from hz_code_assignments b
487 where
488 stg.customer_id=b.OWNER_TABLE_ID
489 and b.OWNER_TABLE_NAME='HZ_PARTIES'
490 and b.Primary_flag = 'Y'
491 and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
492 and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
493 and b.status='A'--Active
494 and b.START_DATE_ACTIVE =
495 ( select max(START_DATE_ACTIVE) from hz_code_assignments c
496 where
497 c.OWNER_TABLE_NAME='HZ_PARTIES'
498 and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
499 and c.Primary_flag = 'Y'
500 and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
501 and c.status='A'
502 and b.OWNER_TABLE_ID=c.OWNER_TABLE_ID)
503 )
504 where
505 exists
506 (select b.class_code from hz_code_assignments b
507 where
508 stg.customer_id=b.OWNER_TABLE_ID
509 and b.OWNER_TABLE_NAME='HZ_PARTIES'
510 and b.Primary_flag = 'Y'
511 and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
512 and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
513 and b.status='A'--Active
514 and b.START_DATE_ACTIVE =
515 ( select max(START_DATE_ACTIVE) from hz_code_assignments c
516 where
517 c.OWNER_TABLE_NAME='HZ_PARTIES'
518 and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
519 and c.Primary_flag = 'Y'
520 and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
521 and c.status='A'
522 and b.OWNER_TABLE_ID=c.OWNER_TABLE_ID) );
523
524 --update customer flag
525
526
527 IF l_cert_level = 'YES' THEN
528
529 UPDATE bim_i_lead_facts_stg stg SET CUSTOMER_FLAG='Y'
530 WHERE
531 EXISTS
532 (SELECT 1 from HZ_CUST_ACCOUNTS a,hz_parties b
533 WHERE a.party_id=stg.customer_id
534 AND stg.lead_creation_date >= trunc(a.creation_date)
535 AND a.party_id=b.party_id
536 AND b.certification_level is not null);
537
538 ELSE
539
540 UPDATE bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
541 WHERE
542 EXISTS
543 (SELECT 1 from HZ_CUST_ACCOUNTS a
544 WHERE a.party_id=stg.customer_id
545 AND stg.lead_creation_date >= trunc(a.creation_date)
546 );
547
548 END IF;
549
550 BIS_COLLECTION_UTILITIES.log('Truncating Facts Table');
551
552 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts';
553
554 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_LEADS');
555
556 INSERT /*+ append parallel */
557 INTO bim_i_lead_facts LDF
558 (
559 creation_date
560 ,last_update_date
561 ,created_by
562 ,last_updated_by
563 ,last_update_login
564 ,lead_id
565 ,lead_line_id
566 ,group_id
567 ,resource_id
568 ,lead_rank_id
569 ,lead_source
570 ,lead_status
571 ,lead_region
572 ,lead_country
573 ,source_code_id
574 ,object_type
575 ,object_id
576 ,child_object_type
577 ,child_object_id
578 ,object_region
579 ,object_country
580 ,object_status
581 ,object_purpose
582 ,child_object_region
583 ,child_object_country
584 ,child_object_status
585 ,child_object_purpose
586 ,object_category_id
587 ,business_unit_id
588 ,lead_creation_date
589 ,lead_touched_date
590 ,lead_dead_date
591 ,item_id
592 ,organization_id
593 ,channel_code
594 ,lead_amount
595 ,close_reason
596 ,accept_flag
597 ,qualified_flag
598 ,source_primary_reference
599 ,source_secondary_reference
600 ,customer_id
601 ,cust_category
602 ,status_open_flag
603 ,lead_rank_score
604 ,expiration_date
605 ,product_category_id
606 ,CUSTOMER_FLAG
607 ,lead_name
608 )
609 SELECT
610 sysdate creation_date
611 ,sysdate last_update_date
612 ,-1 created_by
613 ,-1 last_updated_by
614 ,-1 last_update_login
615 ,x.lead_id lead_id
616 ,x.lead_line_id lead_line_id
617 ,x.group_id group_id
618 ,x.resource_id resource_id
619 ,x.lead_rank_id lead_rank_id
620 ,x.lead_source lead_source
621 ,x.lead_status lead_status
622 ,t.parent_territory_code lead_region
623 ,x.lead_country lead_country
624 ,a.source_code_id source_code_id
625 ,a.object_type object_type
626 ,a.object_id object_id
627 ,a.child_object_type child_object_type
628 ,a.child_object_id child_object_id
629 ,a.object_region object_region
630 ,a.object_country object_country
631 ,a.object_status object_status
632 ,a.object_purpose object_purpose
633 ,a.child_object_region child_object_region
634 ,a.child_object_country child_object_country
635 ,a.child_object_status child_object_status
636 ,a.child_object_purpose child_object_purpose
637 ,a.category_id object_category_id
638 ,a.business_unit_id business_unit_id
639 ,x.lead_creation_date lead_creation_date
640 ,x.lead_touched_date lead_touched_date
641 ,x.lead_dead_date lead_dead_date
642 ,x.item_id item_id
643 ,x.organization_id organization_id
644 ,x.channel_code channel_code
645 ,x.lead_amount lead_amount
646 ,x.close_reason close_reason
647 ,x.accept_flag accept_flag
648 ,x.qualified_flag qualified_flag
649 ,x.source_primary_reference source_primary_reference
650 ,x.source_secondary_reference source_secondary_reference
651 ,x.customer_id customer_id
652 ,x.cust_category cust_category
653 ,x.status_open_flag status_open_flag
654 ,x.lead_rank_score lead_rank_score
655 ,x.expiration_date expiration_date
656 ,nvl(x.product_category_id,-1) product_category_id
657 ,x.customer_flag customer_flag
658 ,x.lead_name Lead_name
659 FROM
660 bim_i_lead_facts_stg X
661 ,bim_i_source_codes A
662 ,bis_territory_hierarchies T
663 WHERE
664 X.source_code_id = A.source_code_id(+)
665 AND T.parent_territory_type(+) = 'AREA'
666 AND T.child_territory_type(+) = 'COUNTRY'
667 AND T.child_territory_code(+) = X.lead_country
668 ;
669
670 COMMIT;
671
672
673
674
675 --update date for converted leads
676 UPDATE bim_i_lead_facts facts
677 SET (facts.lead_converted_date, facts.lead_touched_date)
678 = (SELECT TRUNC(MIN(slo.creation_date)), TRUNC(MIN(slo.creation_date))
679 FROM
680 as_sales_lead_opportunity slo
681 WHERE
682 slo.creation_date between p_start_date and l_sysdate
683 AND slo.sales_lead_id = facts.lead_id
684 AND facts.lead_dead_date is null
685 )
686 WHERE
687 EXISTS (SELECT 1
688 FROM
689 as_sales_lead_opportunity slo
690 WHERE
691 slo.creation_date between p_start_date and l_sysdate
692 AND slo.sales_lead_id = facts.lead_id
693 AND facts.lead_dead_date is null
694 );
695 COMMIT;
696
697
698
699 --update lead_closed_date for closed leads other than dead and converted
700 UPDATE bim_i_lead_facts facts
701 SET (facts.lead_closed_date, facts.lead_touched_date)
702 = (SELECT TRUNC(MIN(hist.creation_date)), TRUNC(MIN(hist.creation_date))
703 FROM
704 as_sales_leads_log hist
705 ,as_statuses_b st
706 WHERE
707 hist.last_update_date between p_start_date and l_sysdate
708 AND hist.status_code not in (l_conv_opp_status, l_dead_status)
709 AND hist.status_code = st.status_code
710 AND st.opp_open_status_flag = 'N'
711 AND hist.sales_lead_id = facts.lead_id
712 AND hist.status_code = facts.lead_status
713 AND facts.lead_converted_date is null
714 AND facts.lead_dead_date is null
715 )
716 WHERE
717 EXISTS (SELECT 1
718 FROM
719 as_sales_leads_log hist
720 ,as_statuses_b st
721 WHERE
722 hist.last_update_date between p_start_date and l_sysdate
723 AND hist.status_code not in (l_conv_opp_status, l_dead_status)
724 AND hist.status_code = st.status_code
725 AND st.opp_open_status_flag = 'N'
726 AND hist.sales_lead_id = facts.lead_id
727 AND hist.status_code = facts.lead_status
728 AND facts.lead_converted_date is null
729 AND facts.lead_dead_date is null
730 );
731 COMMIT;
732
733
734
735 --update touched_date for leads that does not have history
736 UPDATE bim_i_lead_facts facts
737 SET facts.lead_touched_date
738 =(CASE
739 WHEN lead_dead_date is not null THEN lead_dead_date
740 WHEN lead_converted_date is not null THEN lead_converted_date
741 WHEN lead_closed_date is not null THEN lead_closed_date
742 ELSE null
743 END
744 )
745 where lead_touched_date is null;
746
747 COMMIT;
748
749
750
751
752 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
753
754 --EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.mlog$_bim_i_lead_facts';
755
756 --dbms_output.put_line(p_start_date);
757
758 --dbms_output.put_line(p_start_date);
759 --dbms_output.put_line(p_end_date);
760 bis_collection_utilities.wrapup(p_status => TRUE
761 ,p_count => sql%rowcount
762 ,p_period_from => p_start_date
763 ,p_period_to => l_sysdate
764 );
765
766 /***************************************************************/
767
768
769 bis_collection_utilities.log('Before Analyze of the table BIM_I_LEAD_FACTS');
770
771 --Analyze the facts table
772 DBMS_STATS.gather_table_stats('BIM','BIM_I_LEAD_FACTS', estimate_percent => 5,
773 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
774
775 /* Recreating Indexes */
776 BIM_UTL_PKG.CREATE_INDEX('BIM_I_LEAD_FACTS');
777
778 bis_collection_utilities.log('Successful Completion of Leads Facts Program');
779
780
781 EXCEPTION
782
783 WHEN FND_API.G_EXC_ERROR THEN
784 x_return_status := FND_API.G_RET_STS_ERROR;
785 -- Standard call to get message count and if count=1, get the message
786 FND_msg_PUB.Count_And_Get (
787 -- p_encoded => FND_API.G_FALSE,
788 p_count => x_msg_count,
789 p_data => x_msg_data
790 );
791
792 ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:FIRST_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
793
794 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
795
796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
797
798 -- Standard call to get message count and if count=1, get the message
799 FND_msg_PUB.Count_And_Get (
800 --p_encoded => FND_API.G_FALSE,
801 p_count => x_msg_count,
802 p_data => x_msg_data
803 );
804
805 ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:FIRST_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
806
807 WHEN OTHERS THEN
808
809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810
811 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
812 THEN
813 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
814 END IF;
815
816 -- Standard call to get message count and if count=1, get the message
817 FND_msg_PUB.Count_And_Get (
818 -- p_encoded => FND_API.G_FALSE,
819 p_count => x_msg_count,
820 p_data => x_msg_data
821 );
822
823 ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:FIRST_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
824
825
826 END FIRST_LOAD;
827
828 --------------------------------------------------------------------------------------------------
829 -- This procedure will populates all the data required into facts table for incremental load.
830 --
831 -- PROCEDURE INCREMENTAL_LOAD
832 --------------------------------------------------------------------------------------------------
833
834 PROCEDURE INCREMENTAL_LOAD
835 ( p_start_date IN DATE
836 ,p_end_date IN DATE
837 ,p_global_date IN DATE
838 ,p_api_version_number IN NUMBER
839 ,p_init_msg_list IN VARCHAR2
840 ,x_msg_count OUT NOCOPY NUMBER
841 ,x_msg_data OUT NOCOPY VARCHAR2
842 ,x_return_status OUT NOCOPY VARCHAR2
843 )
844 IS
845 l_user_id NUMBER := FND_GLOBAL.USER_ID();
846 l_api_version_number CONSTANT NUMBER := 1.0;
847 l_api_name CONSTANT VARCHAR2(30) := 'INCREMENTAL_LOAD';
848 l_table_name VARCHAR2(100);
849 l_conv_opp_status VARCHAR2(30);
850 l_dead_status VARCHAR2(30);
851 l_check_missing_rate NUMBER;
852 l_stmt VARCHAR2(50);
853 l_cert_level VARCHAR2(3);
854
855 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
856
857 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
858
859 i NUMBER;
860 l_min_start_date DATE;
861
862 l_org_id number;
863
864 CURSOR get_org_id IS
865 SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
866 FROM dual;
867
868 l_status VARCHAR2(5);
869 l_industry VARCHAR2(5);
870 l_schema VARCHAR2(30);
871 l_return BOOLEAN;
872 l_sysdate date;
873 BEGIN
874 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
875 -- Standard call to check for call compatibility.
876 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
877 p_api_version_number,
878 l_api_name,
879 g_pkg_name)
880 THEN
881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882 END IF;
883
884 -- Initialize message list if p_init_msg_list is set to TRUE.
885 IF FND_API.to_Boolean( p_init_msg_list )
886 THEN
887 FND_msg_PUB.initialize;
888 END IF;
889
890 -- Initialize API return status to SUCCESS
891 x_return_status := FND_API.G_RET_STS_SUCCESS;
892
893 --Get status codes for 'opportunity created' and 'dead lead'
894 l_conv_opp_status := nvl(FND_PROFILE.Value('AS_LEAD_LINK_STATUS'),'CONVERTED_TO_OPPORTUNITY');
895 l_dead_status := nvl(FND_PROFILE.Value('AS_DEAD_LEAD_STATUS'),'DEAD_LEAD');
896
897
898 --Find if the certification level is implemented or not
899 l_cert_level := nvl(FND_PROFILE.Value('HZ_DISPLAY_CERT_STATUS'),'NO');
900
901
902 l_table_name := 'BIM_I_LEAD_FACTS';
903 bis_collection_utilities.log('Running Incremental Load of Lead Facts');
904
905 /* Truncate Staging table */
906 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
907
908 l_sysdate:=sysdate;
909
910 INSERT /*+ append parallel */
911 INTO bim_i_lead_facts_stg LDF
912 (
913 lead_id
914 ,group_id
915 ,resource_id
916 ,lead_rank_id
917 ,lead_source
918 ,lead_status
919 ,lead_country
920 ,source_code_id
921 ,lead_creation_date
922 ,lead_touched_date
923 ,channel_code--
924 -- ,lead_amount
925 -- ,currency_code
926 ,close_reason
927 ,accept_flag
928 ,qualified_flag
929 ,source_primary_reference
930 ,source_secondary_reference
931 ,customer_id
932 ,cust_category
933 ,status_open_flag
934 ,lead_rank_score
935 ,expiration_date
936 -- ,conversion_rate
937 -- ,from_currency
938 ,CUSTOMER_FLAG
939 ,lead_name
940 )
941 SELECT
942 x.sales_lead_id lead_id
943 ,x.assign_sales_group_id group_id
944 ,x.assign_to_salesforce_id resource_id
945 ,x.lead_rank_id lead_rank_id
946 ,x.source_system lead_source
947 ,x.status_code lead_status
948 ,x.country lead_country
949 ,x.source_promotion_id source_code_id
950 ,trunc(x.creation_date) lead_creation_date
951 ,(CASE
952 WHEN (x.last_update_date > (x.creation_date+1/1440)) THEN trunc(x.last_update_date)
953 ELSE null
954 END
955 ) lead_touched_date
956 ,x.channel_code channel_code
957 -- ,fii_currency.convert_global_amt_primary(nvl(x.currency_code,'USD'),nvl(x.total_amount,0),X.creation_date) lead_amount
958 -- ,x.currency_code currency_code
959 ,x.close_reason close_reason
960 ,x.accept_flag accept_flag
961 ,x.qualified_flag qualified_flag
962 ,x.source_primary_reference source_primary_reference
963 ,x.source_secondary_reference source_secondary_reference
964 ,x.customer_id customer_id
965 ,NULL cust_category
966 ,x.status_open_flag status_open_flag
967 ,x.lead_rank_score lead_rank_score
968 ,x.expiration_date expiration_date
969 -- ,fii_currency.get_global_rate_primary(nvl(x.currency_code,'USD'),x.creation_date) conversion_rate
970 -- ,nvl(x.currency_code,'USD') from_currency
971 ,'N' CUSTOMER_FLAG
972 ,x.DESCRIPTION
973 FROM
974 as_sales_leads X
975 WHERE
976 X.last_update_date between p_start_date and l_sysdate
977 AND X.creation_date >=p_global_date ;
978
979 COMMIT;
980
981 INSERT /*+ append parallel */
982 INTO bim_i_lead_facts_stg LDF
983 (
984 lead_id
985 ,lead_line_id
986 ,group_id
987 ,resource_id
988 ,lead_rank_id
989 ,lead_source
990 ,lead_status
991 ,lead_country
992 ,source_code_id
993 ,interest_type_id
994 ,primary_interest_code_id
995 ,secondary_interest_code_id
996 ,item_id
997 ,organization_id
998 ,lead_creation_date
999 ,lead_touched_date
1000 ,channel_code
1001 -- ,lead_amount
1002 -- ,currency_code
1003 ,close_reason
1004 ,accept_flag
1005 ,qualified_flag
1006 ,source_primary_reference
1007 ,source_secondary_reference
1008 ,customer_id
1009 ,cust_category
1010 ,status_open_flag
1011 ,lead_rank_score
1012 ,expiration_date
1013 -- ,conversion_rate
1014 -- ,from_currency
1015 ,product_category_id
1016 ,CUSTOMER_FLAG
1017 ,lead_name
1018 )
1019 SELECT
1020 x.sales_lead_id lead_id
1021 ,y.sales_lead_line_id lead_line_id
1022 ,x.assign_sales_group_id group_id
1023 ,x.assign_to_salesforce_id resource_id
1024 ,x.lead_rank_id lead_rank_id
1025 ,x.source_system lead_source
1026 ,x.status_code lead_status
1027 ,x.country lead_country
1028 ,x.source_promotion_id source_code_id
1029 ,y.interest_type_id interest_type_id
1030 ,y.primary_interest_code_id primary_interest_code_id
1031 ,y.secondary_interest_code_id secondary_interest_code_id
1032 ,nvl(y.inventory_item_id,-1) item_id
1033 ,decode(y.inventory_item_id,null,-1,nvl(y.organization_id,-1)) organization_id
1034 ,trunc(x.creation_date) lead_creation_date
1035 ,(CASE
1036 WHEN (x.last_update_date > (x.creation_date+1/1440)) THEN trunc(x.last_update_date)
1037 ELSE null
1038 END
1039 ) lead_touched_date
1040 ,x.channel_code channel_code
1041 -- ,fii_currency.convert_global_amt_primary(nvl(x.currency_code,'USD'),nvl(x.total_amount,0),X.creation_date) lead_amount
1042 -- ,x.currency_code currency_code
1043 ,x.close_reason close_reason
1044 ,x.accept_flag accept_flag
1045 ,x.qualified_flag qualified_flag
1046 ,x.source_primary_reference source_primary_reference
1047 ,x.source_secondary_reference source_secondary_reference
1048 ,x.customer_id customer_id
1049 ,NULL cust_category
1050 ,x.status_open_flag status_open_flag
1051 ,x.lead_rank_score lead_rank_score
1052 ,x.expiration_date expiration_date
1053 -- ,fii_currency.get_global_rate_primary(nvl(x.currency_code,'USD'),x.creation_date) conversion_rate
1054 -- ,nvl(x.currency_code,'USD') from_currency
1055 ,y.category_id product_category_id
1056 ,'N' CUSTOMER_FLAG
1057 ,x.DESCRIPTION
1058 FROM
1059 as_sales_leads X
1060 ,as_sales_lead_lines Y
1061 WHERE
1062 (X.last_update_date between p_start_date and l_sysdate OR Y.last_update_date between p_start_date and l_sysdate)
1063 AND X.sales_lead_id = Y.sales_lead_id
1064 AND X.creation_date >=p_global_date ;
1065
1066
1067 COMMIT;
1068
1069 /*l_check_missing_rate := Check_Missing_Rates (p_start_date);
1070 if (l_check_missing_rate = -1) then
1071 BIS_COLLECTION_UTILITIES.debug('before truncating first time load' );
1072 l_stmt := 'TRUNCATE table '||l_schema||'.bim_i_lead_facts_stg';
1073 EXECUTE IMMEDIATE l_stmt;
1074 commit;
1075 x_return_status := FND_API.G_RET_STS_ERROR;
1076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1077 end if;
1078 */
1079
1080 --update to get customer category
1081
1082 update bim_i_lead_facts_stg stg
1083 set stg.cust_category
1084 = (select b.class_code from hz_code_assignments b
1085 where
1086 stg.customer_id=b.OWNER_TABLE_ID
1087 and b.OWNER_TABLE_NAME='HZ_PARTIES'
1088 and b.Primary_flag = 'Y'
1089 and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
1090 and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
1091 and b.status='A'--Active
1092 and b.START_DATE_ACTIVE =
1093 ( select max(START_DATE_ACTIVE) from hz_code_assignments c
1094 where
1095 c.OWNER_TABLE_NAME='HZ_PARTIES'
1096 and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
1097 and c.Primary_flag = 'Y'
1098 and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
1099 and c.status='A'
1100 and b.OWNER_TABLE_ID=c.OWNER_TABLE_ID)
1101 )
1102 where
1103 exists
1104 (select b.class_code from hz_code_assignments b
1105 where
1106 stg.customer_id=b.OWNER_TABLE_ID
1107 and b.OWNER_TABLE_NAME='HZ_PARTIES'
1108 and b.Primary_flag = 'Y'
1109 and nvl(b.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
1110 and b.CLASS_CATEGORY='CUSTOMER_CATEGORY'
1111 and b.status='A'--Active
1112 and b.START_DATE_ACTIVE =
1113 ( select max(START_DATE_ACTIVE) from hz_code_assignments c
1114 where
1115 c.OWNER_TABLE_NAME='HZ_PARTIES'
1116 and c.CLASS_CATEGORY='CUSTOMER_CATEGORY'
1117 and c.Primary_flag = 'Y'
1118 and nvl(c.end_date_active,trunc(l_sysdate)) > = trunc(l_sysdate)
1119 and c.status='A'
1120 and b.OWNER_TABLE_ID=c.OWNER_TABLE_ID) );
1121
1122 --update customer flag
1123
1124 IF l_cert_level = 'YES' THEN
1125
1126 update bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
1127 where
1128 exists
1129 (select 1 from HZ_CUST_ACCOUNTS a,hz_parties b
1130 where a.party_id=stg.customer_id
1131 and stg.lead_creation_date >= trunc(a.creation_date)
1132 and a.party_id=b.party_id
1133 and b.certification_level is not null);
1134
1135 else
1136
1137 update bim_i_lead_facts_stg stg set CUSTOMER_FLAG='Y'
1138 where
1139 exists
1140 (select 1 from HZ_CUST_ACCOUNTS a
1141 where a.party_id=stg.customer_id
1142 and stg.lead_creation_date >= trunc(a.creation_date));
1143
1144 end if;
1145
1146 COMMIT;
1147
1148 MERGE INTO bim_i_lead_facts facts
1149 USING (
1150 SELECT
1151 sysdate creation_date
1152 ,sysdate last_update_date
1153 ,-1 created_by
1154 ,-1 last_updated_by
1155 ,-1 last_update_login
1156 ,x.lead_id lead_id
1157 ,x.lead_line_id lead_line_id
1158 ,x.group_id group_id
1159 ,x.resource_id resource_id
1160 ,x.lead_rank_id lead_rank_id
1161 ,x.lead_source lead_source
1162 ,x.lead_status lead_status
1163 --,x.status_open_flag open_flag
1164 ,t.parent_territory_code lead_region
1165 ,x.lead_country lead_country
1166 ,a.source_code_id source_code_id
1167 ,a.object_type object_type
1168 ,a.object_id object_id
1169 ,a.child_object_type child_object_type
1170 ,a.child_object_id child_object_id
1171 ,a.object_region object_region
1172 ,a.object_country object_country
1173 ,a.object_status object_status
1174 ,a.object_purpose object_purpose
1175 ,a.child_object_region child_object_region
1176 ,a.child_object_country child_object_country
1177 ,a.child_object_status child_object_status
1178 ,a.child_object_purpose child_object_purpose
1179 ,a.category_id object_category_id
1180 ,a.business_unit_id business_unit_id
1181 ,x.lead_creation_date lead_creation_date
1182 ,x.lead_touched_date lead_touched_date
1183 ,nvl(x.item_id,-1) item_id
1184 ,nvl(x.organization_id,-1) organization_id
1185 ,x.channel_code channel_code
1186 ,x.lead_amount lead_amount
1187 ,x.close_reason close_reason
1188 ,x.accept_flag accept_flag
1189 ,x.qualified_flag qualified_flag
1190 ,x.source_primary_reference source_primary_reference
1191 ,x.source_secondary_reference source_secondary_reference
1192 ,x.customer_id customer_id
1193 ,x.cust_category cust_category
1194 ,x.status_open_flag status_open_flag
1195 ,x.lead_rank_score lead_rank_score
1196 ,x.expiration_date expiration_date
1197 ,nvl(x.product_category_id,-1) product_category_id
1198 ,x.customer_flag customer_flag
1199 ,x.lead_name lead_name
1200 FROM
1201 bim_i_lead_facts_stg X
1202 ,bim_i_source_codes A
1203 ,bis_territory_hierarchies T
1204 WHERE
1205 X.source_code_id = A.source_code_id(+)
1206 AND T.parent_territory_type(+) = 'AREA'
1207 AND T.child_territory_type(+) = 'COUNTRY'
1208 AND T.child_territory_code(+) = X.lead_country
1209 ) changes
1210 ON (facts.lead_id = changes.lead_id
1211 AND nvl(facts.lead_line_id,-1) = nvl(changes.lead_line_id,-1)
1212 )
1213 WHEN MATCHED THEN UPDATE SET
1214 facts.last_update_date = changes.last_update_date
1215 ,facts.group_id = changes.group_id
1216 ,facts.resource_id = changes.resource_id
1217 ,facts.lead_rank_id = changes.lead_rank_id
1218 ,facts.lead_source = changes.lead_source
1219 ,facts.lead_status = changes.lead_status
1220 --,facts.open_flag = changes.open_flag
1221 ,facts.lead_region = changes.lead_region
1222 ,facts.lead_country = changes.lead_country
1223 ,facts.source_code_id = changes.source_code_id
1224 ,facts.object_type = changes.object_type
1225 ,facts.object_id = changes.object_id
1226 ,facts.child_object_type = changes.child_object_type
1227 ,facts.child_object_id = changes.child_object_id
1228 ,facts.object_region = changes.object_region
1229 ,facts.object_country = changes.object_country
1230 ,facts.object_status = changes.object_status
1231 ,facts.object_purpose = changes.object_purpose
1232 ,facts.child_object_region = changes.child_object_region
1233 ,facts.child_object_country = changes.child_object_country
1234 ,facts.child_object_status = changes.child_object_status
1235 ,facts.child_object_purpose = changes.child_object_purpose
1236 ,facts.business_unit_id = changes.business_unit_id
1237 ,facts.object_category_id = changes.object_category_id
1238 ,facts.lead_touched_date = decode(facts.lead_touched_date, null, decode(changes.lead_touched_date,null,null,changes.lead_touched_date),facts.lead_touched_date)
1239 ,facts.item_id = changes.item_id
1240 ,facts.organization_id = changes.organization_id
1241 ,facts.channel_code = changes.channel_code
1242 ,facts.lead_amount = changes.lead_amount
1243 ,facts.close_reason = changes.close_reason
1244 ,facts.accept_flag = changes.accept_flag
1245 ,facts.qualified_flag = changes.qualified_flag
1246 ,facts.source_primary_reference = changes.source_primary_reference
1247 ,facts.source_secondary_reference = changes.source_secondary_reference
1248 ,facts.customer_id = changes.customer_id
1249 ,facts.cust_category = changes.cust_category
1250 ,facts.status_open_flag = changes.status_open_flag
1251 ,facts.lead_rank_score = changes.lead_rank_score
1252 ,facts.expiration_date = changes.expiration_date
1253 ,facts.product_category_id = changes.product_category_id
1254 ,facts.CUSTOMER_FLAG = changes.CUSTOMER_FLAG
1255 ,facts.lead_name = changes.lead_name
1256 WHEN NOT MATCHED THEN INSERT
1257 (
1258 facts.creation_date
1259 ,facts.last_update_date
1260 ,facts.created_by
1261 ,facts.last_updated_by
1262 ,facts.last_update_login
1263 ,facts.lead_id
1264 ,facts.lead_line_id
1265 ,facts.group_id
1266 ,facts.resource_id
1267 ,facts.lead_rank_id
1268 ,facts.lead_source
1269 ,facts.lead_status
1270 --,facts.open_flag
1271 ,facts.lead_region
1272 ,facts.lead_country
1273 ,facts.source_code_id
1274 ,facts.object_type
1275 ,facts.object_id
1276 ,facts.child_object_type
1277 ,facts.child_object_id
1278 ,facts.object_region
1279 ,facts.object_country
1280 ,facts.object_status
1281 ,facts.object_purpose
1282 ,facts.child_object_region
1283 ,facts.child_object_country
1284 ,facts.child_object_status
1285 ,facts.child_object_purpose
1286 ,facts.business_unit_id
1287 ,facts.object_category_id
1288 ,facts.lead_creation_date
1289 ,facts.lead_touched_date
1290 ,facts.item_id
1291 ,facts.organization_id
1292 ,facts.channel_code
1293 ,facts.lead_amount
1294 ,facts.close_reason
1295 ,facts.accept_flag
1296 ,facts.qualified_flag
1297 ,facts.source_primary_reference
1298 ,facts.source_secondary_reference
1299 ,facts.customer_id
1300 ,facts.cust_category
1301 ,facts.status_open_flag
1302 ,facts.lead_rank_score
1303 ,facts.expiration_date
1304 ,facts.product_category_id
1305 ,facts.customer_flag
1306 ,facts.lead_name
1307 )
1308 VALUES
1309 (
1310 changes.creation_date
1311 ,changes.last_update_date
1312 ,changes.created_by
1313 ,changes.last_updated_by
1314 ,changes.last_update_login
1315 ,changes.lead_id
1316 ,changes.lead_line_id
1317 ,changes.group_id
1318 ,changes.resource_id
1319 ,changes.lead_rank_id
1320 ,changes.lead_source
1321 ,changes.lead_status
1322 --,changes.open_flag
1323 ,changes.lead_region
1324 ,changes.lead_country
1325 ,changes.source_code_id
1326 ,changes.object_type
1327 ,changes.object_id
1328 ,changes.child_object_type
1329 ,changes.child_object_id
1330 ,changes.object_region
1331 ,changes.object_country
1332 ,changes.object_status
1333 ,changes.object_purpose
1334 ,changes.child_object_region
1335 ,changes.child_object_country
1336 ,changes.child_object_status
1337 ,changes.child_object_purpose
1338 ,changes.business_unit_id
1339 ,changes.object_category_id
1340 ,changes.lead_creation_date
1341 ,changes.lead_touched_date
1342 ,changes.item_id
1343 ,changes.organization_id
1344 ,changes.channel_code
1345 ,changes.lead_amount
1346 ,changes.close_reason
1347 ,changes.accept_flag
1348 ,changes.qualified_flag
1349 ,changes.source_primary_reference
1350 ,changes.source_secondary_reference
1351 ,changes.customer_id
1352 ,changes.cust_category
1353 ,changes.status_open_flag
1354 ,changes.lead_rank_score
1355 ,changes.expiration_date
1356 ,changes.product_category_id
1357 ,changes.customer_flag
1358 ,changes.lead_name
1359 );
1360
1361
1362 --update date for dead leads
1363 UPDATE bim_i_lead_facts facts
1364 SET facts.lead_dead_date
1365 = (SELECT TRUNC(MIN(hist.creation_date))
1366 FROM
1367 as_sales_leads_log hist
1368 WHERE
1369 hist.last_update_date between p_start_date and l_sysdate
1370 AND hist.status_code = l_dead_status
1371 AND hist.sales_lead_id = facts.lead_id
1372 AND hist.status_code = facts.lead_status
1373 AND facts.lead_dead_date is null
1374 AND facts.lead_converted_date is null
1375 AND facts.lead_closed_date is null
1376 )
1377 WHERE
1378 EXISTS (SELECT 1
1379 FROM
1380 as_sales_leads_log hist
1381 WHERE
1382 hist.last_update_date between p_start_date and l_sysdate
1383 AND hist.status_code = l_dead_status
1384 AND hist.sales_lead_id = facts.lead_id
1385 AND hist.status_code = facts.lead_status
1386 AND facts.lead_dead_date is null
1387 AND facts.lead_converted_date is null
1388 AND facts.lead_closed_date is null
1389 );
1390
1391
1392 --update date for converted leads
1393 UPDATE bim_i_lead_facts facts
1394 SET facts.lead_converted_date
1395 = (SELECT TRUNC(MIN(slo.creation_date))
1396 FROM
1397 as_sales_lead_opportunity slo
1398 WHERE
1399 slo.creation_date between p_start_date and l_sysdate
1400 AND slo.sales_lead_id = facts.lead_id
1401 AND facts.lead_dead_date is null
1402 AND facts.lead_converted_date is null
1403 AND facts.lead_closed_date is null
1404 )
1405 WHERE
1406 EXISTS (SELECT 1
1407 FROM
1408 as_sales_lead_opportunity slo
1409 WHERE
1410 slo.creation_date between p_start_date and l_sysdate
1411 AND slo.sales_lead_id = facts.lead_id
1412 AND facts.lead_dead_date is null
1413 AND facts.lead_converted_date is null
1414 AND facts.lead_closed_date is null
1415 );
1416
1417
1418 --update date for closed leads other than dead and converted
1419 UPDATE bim_i_lead_facts facts
1420 SET facts.lead_closed_date
1421 = (SELECT TRUNC(MIN(hist.creation_date))
1422 FROM
1423 as_sales_leads_log hist
1424 ,as_statuses_b st
1425 WHERE
1426 hist.last_update_date between p_start_date and l_sysdate
1427 AND hist.status_code not in (l_conv_opp_status, l_dead_status)
1428 AND hist.status_code = st.status_code
1429 AND st.opp_open_status_flag = 'N'
1430 AND hist.sales_lead_id = facts.lead_id
1431 AND hist.status_code = facts.lead_status
1432 AND facts.lead_dead_date is null
1433 AND facts.lead_converted_date is null
1434 AND facts.lead_closed_date is null
1435 )
1436 WHERE
1437 EXISTS (SELECT 1
1438 FROM
1439 as_sales_leads_log hist
1440 ,as_statuses_b st
1441 WHERE
1442 hist.last_update_date between p_start_date and l_sysdate
1443 AND hist.status_code not in (l_conv_opp_status, l_dead_status)
1444 AND hist.status_code = st.status_code
1445 AND st.opp_open_status_flag = 'N'
1446 AND hist.sales_lead_id = facts.lead_id
1447 AND hist.status_code = facts.lead_status
1448 AND facts.lead_dead_date is null
1449 AND facts.lead_converted_date is null
1450 AND facts.lead_closed_date is null
1451 );
1452
1453 --update touched_date for leads that comes in dead, conveted or closed
1454 UPDATE bim_i_lead_facts facts
1455 SET facts.lead_touched_date
1456 =(CASE
1457 WHEN lead_dead_date is not null THEN lead_dead_date
1458 WHEN lead_converted_date is not null THEN lead_converted_date
1459 WHEN lead_closed_date is not null THEN lead_closed_date
1460 ELSE null
1461 END
1462 )
1463 where lead_touched_date is null
1464 and last_update_date between p_start_date and l_sysdate;
1465
1466
1467 DELETE
1468 FROM bim_i_lead_facts
1469 WHERE lead_line_id IS NULL
1470 AND lead_id in (SELECT
1471 lead_id
1472 FROM bim_i_lead_facts_stg
1473 WHERE lead_line_id is NOT NULL);
1474
1475 COMMIT;
1476
1477 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.bim_i_lead_facts_stg';
1478
1479
1480 --dbms_output.put_line(p_start_date);
1481 --dbms_output.put_line(p_end_date);
1482 bis_collection_utilities.wrapup(p_status => TRUE
1483 ,p_count => sql%rowcount
1484 ,p_period_from => p_start_date
1485 ,p_period_to => l_sysdate
1486 );
1487
1488 /***************************************************************/
1489
1490
1491 bis_collection_utilities.log('Before Analyze of the table BIM_I_LEAD_FACTS');
1492
1493 --Analyze the facts table
1494 DBMS_STATS.gather_table_stats('BIM','BIM_I_LEAD_FACTS', estimate_percent => 5,
1495 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
1496
1497
1498 bis_collection_utilities.log('Successful Completion of Leads Facts Program');
1499
1500
1501 EXCEPTION
1502
1503 WHEN FND_API.G_EXC_ERROR THEN
1504 x_return_status := FND_API.G_RET_STS_ERROR;
1505 -- Standard call to get message count and if count=1, get the message
1506 FND_msg_PUB.Count_And_Get (
1507 -- p_encoded => FND_API.G_FALSE,
1508 p_count => x_msg_count,
1509 p_data => x_msg_data
1510 );
1511
1512 ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:INCREMENTAL_LOAD:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
1513
1514 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1515
1516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1517
1518 -- Standard call to get message count and if count=1, get the message
1519 FND_msg_PUB.Count_And_Get (
1520 --p_encoded => FND_API.G_FALSE,
1521 p_count => x_msg_count,
1522 p_data => x_msg_data
1523 );
1524
1525 ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:INCREMENTAL_LOAD:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
1526
1527 WHEN OTHERS THEN
1528
1529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530
1531 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
1532 THEN
1533 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
1534 END IF;
1535
1536 -- Standard call to get message count and if count=1, get the message
1537 FND_msg_PUB.Count_And_Get (
1538 -- p_encoded => FND_API.G_FALSE,
1539 p_count => x_msg_count,
1540 p_data => x_msg_data
1541 );
1542
1543 ams_utility_pvt.write_conc_log('BIM_I_LEAD_FACTS_PKG:INCREMENTAL_LOAD:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
1544
1545
1546 END INCREMENTAL_LOAD;
1547
1548
1549 END BIM_I_LEAD_FACTS_PKG;
1550