[Home] [Help]
PACKAGE BODY: APPS.BIM_LEAD_IMPORT_FACTS_PKG
Source
1 PACKAGE BODY BIM_LEAD_IMPORT_FACTS_PKG AS
2 /* $Header: bimlisfb.pls 120.2 2005/11/11 05:09:14 arvikuma noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(200) :='BIM_LEAD_IMPORT_FACTS_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(20) :='bimldsfb.pls';
6 -----------------------------------------------------------------------
7 -- PROCEDURE
8 -- POPULATE
9 --
10 -- Note
11 -- Main procedure called outside of the pacakge, it calls different
12 -- procedures depending on the parameters passed FROM concurrent
13 -- program.
14 -----------------------------------------------------------------------
15 PROCEDURE POPULATE
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 ) IS
28 l_profile NUMBER;
29 v_error_code NUMBER;
30 v_error_text VARCHAR2(1500);
31 l_max_end_date DATE;
32 l_start_date DATE;
33 l_end_date DATE;
34 l_user_id NUMBER := FND_GLOBAL.USER_ID();
35 l_api_version_number CONSTANT NUMBER := 1.0;
36 l_api_name CONSTANT VARCHAR2(30) := 'populate';
37 l_date DATE;
38 l_sdate DATE :=to_date('01/01/1950 12:34:56', 'DD/MM/YYYY HH:MI:SS') ;
39 l_err_code NUMBER;
40 l_period_error VARCHAR2(5000);
41 l_currency_error VARCHAR2(5000);
42
43 CURSOR chk_history_data IS
44 SELECT MAX(end_date)
45 FROM bim_rep_history
46 WHERE object = 'LEAD_IMPORT';
47
48 BEGIN
49
50 -- Standard call to check for call compatibility.
51 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
52 p_api_version_number,
53 l_api_name,
54 G_PKG_NAME)
55 THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58
59 -- Initialize message list IF p_init_msg_list IS set to TRUE.
60 IF FND_API.to_Boolean( p_init_msg_list )
61 THEN
62 FND_MSG_PUB.initialize;
63 END IF;
64
65 -- Debug Message
66 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
67
68 -- Initialize API return status to SUCCESS
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70
71
72 /* Find if the data will be loaded for the first time or not.*/
73
74 OPEN chk_history_data;
75 FETCH chk_history_data INTO l_max_end_date;
76 CLOSE chk_history_data;
77
78 IF p_end_date = trunc(sysdate) then
79 l_end_date := p_end_date -1;
80 else
81 l_end_date := p_end_date;
82 end if;
83
84 --check the validation
85 --l_err_code := BIM_VALIDITY_CHECK.validate_events(p_start_date,
86 -- l_end_date, l_period_error, l_currency_error);
87 IF (l_max_end_date IS NOT NULL AND p_start_date IS NOT NULL)
88 THEN
89 fnd_message.set_name('BIM','BIM_R_FIRST_LOAD');
90 fnd_message.set_token('END_DATE', l_max_end_date, FALSE);
91 ams_utility_pvt.write_conc_log(fnd_message.get);
92 RAISE FND_API.G_EXC_ERROR;
93 elsif (l_max_end_date IS NULL AND p_start_date IS NULL) THEN
94 fnd_message.set_name('BIM','BIM_R_FIRST_SUBSEQUENT');
95 ams_utility_pvt.write_conc_log(fnd_message.get);
96 RAISE FND_API.G_EXC_ERROR;
97 END IF;
98
99
100 IF p_start_date IS NOT NULL THEN
101
102 IF (p_start_date > p_end_date) THEN
103 fnd_message.set_name('BIM','BIM_R_DATE_VALIDATION');
104 ams_utility_pvt.write_conc_log(fnd_message.get);
105 RAISE FND_API.G_EXC_ERROR;
106 END IF;
107 LOAD_DATA(p_start_datel => p_start_date
108 ,p_end_datel => l_end_date
109 ,p_api_version_number => l_api_version_number
110 ,x_msg_count=>x_msg_count
111 ,x_msg_data=>x_msg_data
112 ,x_return_status=>x_return_status
113 );
114
115 IF x_return_status = FND_API.g_ret_sts_error
116 THEN
117 RAISE FND_API.g_exc_error;
118 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
119 RAISE FND_API.g_exc_unexpected_error;
120 END IF;
121 ELSE
122
123 IF l_max_end_date IS NOT NULL THEN
124
125 IF (p_end_date <= l_max_end_date) THEN
126 fnd_message.set_name('BIM','BIM_R_SUBSEQUENT_LOAD');
127 fnd_message.set_token('END_DATE', l_max_end_date, FALSE);
128 ams_utility_pvt.write_conc_log(fnd_message.get);
129 RAISE FND_API.g_exc_error;
130 END IF;
131
132 LOAD_DATA(p_start_datel => l_max_end_date + 1
133 ,p_end_datel => l_end_date
134 ,p_api_version_number => l_api_version_number
135 ,x_msg_count=>x_msg_count
136 ,x_msg_data=>x_msg_data
137 ,x_return_status=>x_return_status
138 );
139 END IF;
140
141 IF x_return_status = FND_API.g_ret_sts_error
142 THEN
143 RAISE FND_API.g_exc_error;
144 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
145 RAISE FND_API.g_exc_unexpected_error;
146 END IF;
147 END IF;
148
149 IF FND_API.To_Boolean ( p_commit ) THEN
150 COMMIT WORK;
151 END IF;
152 commit;
153
154 fnd_message.set_name('BIM','BIM_R_END_FACTS');
155 fnd_message.set_token('OBJECT_NAME', 'LEAD IMPORT', FALSE);
156 fnd_file.put_line(fnd_file.log,fnd_message.get);
157
158 -- Standard call to get message count and IF count IS 1, get message info.
159 FND_msg_PUB.Count_And_Get
160 (p_count => x_msg_count,
161 p_data => x_msg_data
162 );
163 EXCEPTION
164
165 WHEN FND_API.G_EXC_ERROR THEN
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 -- Standard call to get message count and IF count=1, get the message
168 FND_msg_PUB.Count_And_Get (
169 -- p_encoded => FND_API.G_FALSE,
170 p_count => x_msg_count,
171 p_data => x_msg_data
172 );
173 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175 -- Standard call to get message count and IF count=1, get the message
176 FND_msg_PUB.Count_And_Get (
177 --p_encoded => FND_API.G_FALSE,
178 p_count => x_msg_count,
179 p_data => x_msg_data
180 );
181
182 WHEN OTHERS THEN
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184 IF FND_msg_PUB.Check_msg_Level ( FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
185 THEN
186 FND_msg_PUB.Add_Exc_msg( G_PKG_NAME,l_api_name);
187 END IF;
188 -- Standard call to get message count and IF count=1, get the message
189 FND_msg_PUB.Count_And_Get (
190 -- p_encoded => FND_API.G_FALSE,
191 p_count => x_msg_count,
192 p_data => x_msg_data
193 );
194
195 END POPULATE;
196 -----------------------------------------------------------------------
197 -- PROCEDURE
198 -- LOG_HISTORY
199 --
200 --Note: This procedure will insert a HISTORY record into bim_rep_history
201 --table whenever first and subsequent load has run successfully
202 -----------------------------------------------------------------------
203 PROCEDURE LOG_HISTORY(
204 p_object VARCHAR2,
205 p_start_date DATE,
206 p_end_date DATE )
207 IS
208 l_user_id NUMBER := FND_GLOBAL.USER_ID();
209 p_table_name VARCHAR2(100):='BIM_REP_HISTORY';
210 BEGIN
211 INSERT INTO
212 bim_rep_history
213 (creation_date,
214 last_update_date,
215 created_by,
216 last_updated_by,
217 object,
218 object_last_updated_date,
219 start_date,
220 end_date)
221 VALUES
222 (sysdate,
223 sysdate,
224 l_user_id,
225 l_user_id,
226 p_object,
227 sysdate,
228 p_start_date,
229 p_end_date);
230
231 END LOG_HISTORY;
232
233
234 -----------------------------------------------------------------------
235 -- PROCEDURE
236 -- LOG_HISTORY
237 --
238 --Note: This procedure will excute when data is loaded for the first time, and run the program incrementally.
239 -----------------------------------------------------------------------
240
241 PROCEDURE LOAD_DATA
242 (p_start_datel DATE,
243 p_end_dateL DATE,
244 p_api_version_number NUMBER,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 x_return_status OUT NOCOPY VARCHAR2
248 )
249 IS
250 l_user_id NUMBER := FND_GLOBAL.USER_ID();
251 l_success VARCHAR2(1) := 'F';
252 l_api_version_number CONSTANT NUMBER := 1.0;
253 l_api_name CONSTANT VARCHAR2(30) := 'LEAD_IMPORT_LOAD_DATA';
254 l_profile NUMBER;
255 p_table_name VARCHAR2(100);
256 l_temp_msg VARCHAR2(100);
257 l_def_tablespace VARCHAR2(100);
258 l_index_tablespace VARCHAR2(100);
259 l_oracle_username VARCHAR2(100);
260 l_max_end_date DATE;
261
262 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
263
264 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
265
266 l_pct_free generic_number_table;
267 l_ini_trans generic_number_table;
268 l_max_trans generic_number_table;
269 l_initial_extent generic_number_table;
270 l_next_extent generic_number_table;
271 l_min_extents generic_number_table;
272 l_max_extents generic_number_table;
273 l_pct_increase generic_number_table;
274 l_owner generic_char_table;
275 l_index_name generic_char_table;
276 l_ind_column_name generic_char_table;
277 l_index_table_name generic_char_table;
278 i NUMBER;
279 l_uniqueness generic_char_table;
280 temp_column_string VARCHAR2(2000);
281 temp_column_position NUMBER;
282 temp_index_name VARCHAR2(30);
283 is_unique VARCHAR2(30);
284 l_column_position generic_number_table;
285
286
287 l_event_offer number;
288 l_min_date date;
289 l_object varchar(100);
290 l_status VARCHAR2(5);
291 l_industry VARCHAR2(5);
292 l_schema VARCHAR2(30);
293 l_return BOOLEAN;
294 BEGIN
295 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
296
297 l_object := 'LEAD IMPORT';
298 fnd_message.set_name('BIM','BIM_R_START_FACTS');
299 fnd_message.set_token('P_OBJECT', l_object, FALSE);
300 ams_utility_pvt.write_conc_log(fnd_message.get);
301
302 -- Standard call to check for call compatibility.
303 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
304 l_api_version_number,
305 l_api_name,
306 G_PKG_NAME)
307 THEN
308 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
309 END IF;
310
311
312 -- Initialize API return status to SUCCESS
313 x_return_status := FND_API.G_RET_STS_SUCCESS;
314
315 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL dml ';
316 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'. bim_r_limp_daily_facts nologging';
317 EXECUTE IMMEDIATE 'ALTER TABLE '||l_schema||'. bim_r_limp_weekly_facts nologging';
318 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'. bim_r_limp_daily_facts_s CACHE 1000';
319
320 p_table_name := 'BIM_R_LIMP_DAILY_FACTS';
321
322 fnd_message.set_name('BIM','BIM_R_DROP_INDEXES');
323 ams_utility_pvt.write_conc_log(fnd_message.get);
324
325 BIM_UTL_PKG.DROP_INDEX(p_table_name);
326 BIM_UTL_PKG.DROP_INDEX('BIM_R_LIMP_WEEKLY_FACTS');
327
328
329 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
330 fnd_message.set_token('TABLE_NAME', 'BIM_R_LIMP_DAILY_FACTS', FALSE);
331 ams_utility_pvt.write_conc_log(fnd_message.get);
332
333 BEGIN
334 INSERT INTO /*+ append parallel(EDF,1) */
335 BIM_R_LIMP_DAILY_FACTS EDF(
336 Daily_transaction_id,
337 Creation_date,
338 Last_update_date,
339 Created_by,
340 Last_updated_by,
341 Last_update_login,
342 parent_object_id,
343 Object_id,
344 parent_object_type,
345 object_type,
346 lead_region,
347 lead_country,
348 object_business_unit_id,
349 lead_import_status,
350 Failure_reason,
351 month,
352 qtr,
353 year,
354 leads_valid,
355 leads_invalid,
356 leads_new,
357 Transaction_create_date,
358 weekend_date)
359 select /*+ parallel(OUTER,1) */
360 BIM_R_LIMP_DAILY_FACTS_S.nextval,
361 sysdate,
362 sysdate,
363 l_user_id,
364 l_user_id,
365 l_user_id,
366 parent_object_id,
367 Object_id,
368 parent_object_type,
369 object_type,
370 lead_region,
371 lead_country,
372 object_business_unit_id,
373 lead_import_status,
374 Failure_reason,
375 month,
376 qtr,
377 year,
378 leads_valid,
379 leads_invalid,
380 leads_new,
381 creation_date,
382 trunc((decode(decode( to_char(creation_date,'MM') , to_char(next_day(creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
383 ,'TRUE'
384 ,decode(decode(creation_date , (next_day(creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
385 ,'TRUE'
386 ,creation_date
387 ,'FALSE'
388 ,next_day(creation_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
389 ,'FALSE'
390 ,decode(decode(to_char(creation_date,'MM'),to_char(next_day(creation_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
391 ,'FALSE'
392 ,last_day(creation_date))))) weekend_date
393 from(
394 SELECT b.parent_object_type parent_object_type,
395 b.object_type object_type,
396 b.parent_object_id parent_object_id,
397 b.object_id object_id,
398 b.business_unit_id object_business_unit_id,
399 a.last_update_date creation_date,
400 c.country lead_country,
401 c.region lead_region,
402 a.load_status lead_import_status,
403 d.fiscal_month month,
404 d.fiscal_qtr qtr,
405 d.fiscal_year year,
406 a.Failure_reason Failure_reason,
407 sum(a.leads_valid) leads_valid,
408 SUM(a.leads_new) leads_new,
409 SUM(a.leads_Invalid) leads_Invalid
410 FROM(
411 SELECT a.promotion_code,
412 a.country,
413 TRUNC(a.last_update_date) last_update_date,
414 a.Import_interface_id,
415 a.load_status,
416 NULL failure_reason,
417 decode(A.Load_Status, 'SUCCESS', 1, 0) leads_valid,
418 decode(A.Load_Status, 'NEW', 1, 0) leads_new,
419 0 leads_invalid
420 from AS_IMPORT_INTERFACE A
421 where a.last_update_date between p_start_datel and p_end_datel+0.9999
422 AND a.load_status IN ('SUCCESS', 'NEW')
423 group by a.promotion_code,
424 a.country,
425 TRUNC(a.last_update_date),
426 a.Import_interface_id,
427 a.load_status
428 UNION ALL
429 SELECT a.promotion_code,
430 a.country,
431 MAX(TRUNC(B.last_update_date)) last_update_date,
432 a.Import_interface_id,
433 a.load_status,
434 max(b.error_text) FAILURE_REASON,
435 0 leads_valid,
436 0 leads_new,
437 decode(A.Load_Status, 'ERROR', 1,'UNEXP_ERROR', 1, 0) leads_Invalid
438 from AS_IMPORT_INTERFACE a,
439 AS_LEAD_IMPORT_ERRORS B
440 where a.last_update_date between p_start_datel and p_end_datel +0.9999
441 and a.Import_interface_id = B.import_interface_id(+)
442 AND a.load_status IN ('ERROR', 'UNEXP_ERROR')
443 group by a.promotion_code,
444 a.country,
445 TRUNC(a.last_update_date),
446 a.Import_interface_id,
447 a.load_status) A,
448 bim_r_source_codes b,
449 bim_r_locations c,
450 bim_intl_dates d
451 where a.promotion_code = b.source_code(+)
452 and a.country = c.country
453 and a.last_update_date = d.trdate
454 group by b.parent_object_type,
455 b.object_type,
456 b.parent_object_id,
457 b.object_id,
458 b.business_unit_id,
459 a.last_update_date,
460 c.country,
461 c.region,
462 a.load_status,
463 b.business_unit_id,
464 d.fiscal_month,
465 d.fiscal_qtr,
466 d.fiscal_year,
467 a.failure_reason) OUTER;
468
469 commit;
470 -- dbms_output.put_line('after daily insert ');
471
472 -- analyze the bim_r_limp_daily_facts with dbms_stats
473 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
474 fnd_message.set_token('table_name', p_table_name, FALSE);
475 ams_utility_pvt.write_conc_log(fnd_message.get);
476
477
478 BEGIN
479 DBMS_STATS.gather_table_stats('BIM','bim_r_limp_daily_facts', estimate_percent => 5,
480 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
481 END;
482
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'. bim_r_limp_daily_facts_s CACHE 20';
487 x_return_status := FND_API.G_RET_STS_ERROR;
488 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
489 FND_MESSAGE.Set_token('table_name', p_table_name, FALSE);
490 FND_MSG_PUB.Add;
491 ams_utility_pvt.write_conc_log('LEAD IMPORT: EXCEPTION FOR FIRST INSERT. '||sqlerrm(sqlcode));
492 RAISE FND_API.G_EXC_ERROR;
493 end;
494
495 p_table_name :='BIM_R_LIMP_WEEKLY_FACTS';
496
497 fnd_message.set_name('BIM','BIM_R_TRUNCATE_TABLE');
498 fnd_message.set_token('table_name', p_table_name, FALSE);
499 ams_utility_pvt.write_conc_log(fnd_message.get);
500
501 EXECUTE IMMEDIATE 'truncate table '||l_schema||'. bim_r_limp_weekly_facts';
502 --insert into BIM_R_LIMP_WEEKLY_FACTS table
503 BEGIN
504
505 fnd_message.set_name('BIM','BIM_R_POPULATE_TABLE');
506 fnd_message.set_token('TABLE_NAME', 'BIM_R_LIMP_WEEKLY_FACTS', FALSE);
507 ams_utility_pvt.write_conc_log(fnd_message.get);
508
509 INSERT INTO
510 BIM_R_LIMP_WEEKLY_FACTS EDF(
511 weekly_transaction_id,
512 Creation_date,
513 Last_update_date,
514 Created_by,
515 Last_updated_by,
516 Last_update_login,
517 Lead_Region,
518 Lead_Country,
519 Object_Business_unit_id,
520 Lead_Import_Status,
521 Object_id,
522 Object_type,
523 Parent_object_id,
524 Parent_object_type,
525 FAILURE_REASON,
526 Month,
527 Qtr,
528 Year,
529 leads_valid,
530 leads_invalid,
531 leads_new,
532 Weekend_date
533 )
534 SELECT
535 BIM_R_LIMP_WEEKLY_FACTS_S.nextval,
536 sysdate,
537 sysdate,
538 l_user_id,
539 l_user_id,
540 l_user_id,
541 Lead_Region,
542 Lead_Country,
543 Object_Business_unit_id,
544 Lead_Import_Status,
545 Object_id,
546 Object_type,
547 Parent_object_id,
548 Parent_object_type,
549 FAILURE_REASON,
550 Month,
551 Qtr,
552 Year,
553 leads_valid,
554 leads_invalid,
555 leads_new,
556 Weekend_date
557 from( select Lead_Region,
558 Lead_Country,
559 Object_Business_unit_id,
560 Lead_Import_Status,
561 Object_id,
562 Object_type,
563 Parent_object_id,
564 Parent_object_type,
565 FAILURE_REASON,
566 Month,
567 Qtr,
568 Year,
569 sum(Leads_valid) leads_valid,
570 sum(Leads_invalid) leads_invalid,
571 sum(Leads_new) leads_new,
572 Weekend_date
573 FROM BIM_R_LIMP_DAILY_FACTS
574 group by
575 Lead_Region,
576 Lead_Country,
577 Object_Business_unit_id,
578 Lead_Import_Status,
579 Object_id,
580 Object_type,
581 Parent_object_id,
582 Parent_object_type,
583 FAILURE_REASON,
584 Month,
585 Qtr,
586 Year,
587 Weekend_date);
588
589
590 LOG_HISTORY(
591 'LEAD_IMPORT',
592 p_start_datel,
593 p_end_datel
594 );
595
596 COMMIT;
597
598 -- analyze the bim_r_limp_daily_facts with dbms_stats
599 fnd_message.set_name('BIM','BIM_R_ANALYZE_TABLE');
600 fnd_message.set_token('table_name', p_table_name, FALSE);
601 ams_utility_pvt.write_conc_log(fnd_message.get);
602
603 BEGIN
604 DBMS_STATS.gather_table_stats('BIM','bim_r_limp_weekly_facts', estimate_percent => 5,
605 degree => 8, granularity => 'GLOBAL', cascade =>TRUE);
606 END;
607
608 fnd_message.set_name('BIM','BIM_R_RECREATE_INDEXES');
609 ams_utility_pvt.write_conc_log(fnd_message.get);
610
611 BIM_UTL_PKG.CREATE_INDEX('BIM_R_LIMP_DAILY_FACTS');
612 BIM_UTL_PKG.CREATE_INDEX('BIM_R_LIMP_WEEKLY_FACTS');
613
614 EXECUTE IMMEDIATE 'ALTER sequence '||l_schema||'. bim_r_limp_weekly_facts_s CACHE 20';
615
616
617 EXCEPTION
618
619 WHEN FND_API.G_EXC_ERROR THEN
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 -- Standard call to get message count and if count=1, get the message
622 FND_msg_PUB.Count_And_Get (
623 -- p_encoded => FND_API.G_FALSE,
624 p_count => x_msg_count,
625 p_data => x_msg_data
626 );
627
628 ams_utility_pvt.write_conc_log('BIM_LEAD_FACTS_PKG:LOAD_DATA:IN EXPECTED EXCEPTION '||sqlerrm(sqlcode));
629
630 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
631
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
633
634 -- Standard call to get message count and if count=1, get the message
635 FND_msg_PUB.Count_And_Get (
636 --p_encoded => FND_API.G_FALSE,
637 p_count => x_msg_count,
638 p_data => x_msg_data
639 );
640
641 ams_utility_pvt.write_conc_log('BIM_LEAD_FACTS_PKG:LOAD_DATA:IN UNEXPECTED EXCEPTION '||sqlerrm(sqlcode));
642
643 WHEN OTHERS THEN
644
645 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
646
647 IF FND_msg_PUB.Check_msg_Level (FND_msg_PUB.G_msg_LVL_UNEXP_ERROR)
648 THEN
649 FND_msg_PUB.Add_Exc_msg( g_pkg_name,l_api_name);
650 END IF;
651
652 -- Standard call to get message count and if count=1, get the message
653 FND_msg_PUB.Count_And_Get (
654 -- p_encoded => FND_API.G_FALSE,
655 p_count => x_msg_count,
656 p_data => x_msg_data
657 );
658
659 ams_utility_pvt.write_conc_log('BIM_LEAD_IMPORT_FACTS_PKG:LOAD_DATA:IN OTHERS EXCEPTION '||sqlerrm(sqlcode));
660 END;
661 END LOAD_DATA;
662
663 END BIM_LEAD_IMPORT_FACTS_PKG;