[Home] [Help]
PACKAGE BODY: APPS.AML_LEAD_DEDUPE_PVT
Source
1 PACKAGE BODY aml_lead_dedupe_pvt AS
2 /* $Header: amlvddpb.pls 115.12 2004/07/06 08:28:02 bmuthukr noship $ */
3
4 -- Start of Comments
5 -- Package name : aml_lead_dedupe_pvt
6 -- Purpose : To find duplicate lead
7 -- NOTE :
8 -- History :
9 -- 11-Aug-2003 AANJARIA Created.
10 --
11 -- 06-Jul-2004 BMUTHUKR Fixed bug # 3737789.
12 -- End of Comments
13
14 l_product_interest VARCHAR2 (1) := 'N';
15 l_budget_status VARCHAR2 (1) := 'N';
16 l_purchase_amt VARCHAR2 (1) := 'N';
17 l_budget_amt VARCHAR2 (1) := 'N';
18 l_purchase_timeframe VARCHAR2 (1) := 'N';
19 l_project VARCHAR2 (1) := 'N';
20 l_note_type VARCHAR2 (1) := 'N';
21 l_promotion_code VARCHAR2 (1) := 'N';
22 l_vehicle_response_code VARCHAR2 (1) := 'N';
23 l_contact VARCHAR2 (1) := 'N';
24 l_address VARCHAR2 (1) := 'N';
25 l_customer VARCHAR2 (1) := 'N';
26
27 --------------------------------------------------------------
28 -- Procedure : DebugMsg
29 -- Description : Print debug msg
30 --------------------------------------------------------------
31
32 PROCEDURE DebugMsg (Msg IN VARCHAR2)
33 IS
34 BEGIN
35 --DBMS_OUTPUT.put_line (Msg);
36 /*
37 fnd_file.put(1, substr(Msg,1,255));
38 fnd_file.new_line(1,1);
39 */
40 null;
41 END DebugMsg;
42
43 --------------------------------------------------------------
44 -- Procedure : Remove_duplicate
45 -- Description : Remove duplicate product interest type from
46 -- category_id_tbl table
47 --------------------------------------------------------------
48
49 PROCEDURE Remove_duplicate (px_category_tbl IN OUT NOCOPY category_id_type)
50 IS
51 out_tbl category_id_type;
52 idx NUMBER;
53 fnd NUMBER;
54 BEGIN
55 idx := 1;
56
57 --Browse table, identify duplicate and populate out_tbl
58 FOR i IN 1 .. px_category_tbl.COUNT
59 LOOP
60 fnd := 0;
61
62 FOR j IN 1..out_tbl.COUNT
63 LOOP
64 IF px_category_tbl(i) = out_tbl(j) THEN
65 fnd := 1;
66 END IF;
67 END LOOP;
68
69 IF fnd = 0 THEN
70 out_tbl(idx) := px_category_tbl(i);
71 idx := idx+1;
72 END IF;
73 END LOOP;
74
75 FOR i IN 1..out_tbl.COUNT
76 LOOP
77 DebugMsg ('Fin -'||to_char(out_tbl(i)));
78 END LOOP;
79
80 px_category_tbl := out_tbl;
81
82 END remove_duplicate;
83
84 --------------------------------------------------------------
85 -- Procedure : Get_attributes
86 -- Description : Get attributes from rule
87 --------------------------------------------------------------
88
89 PROCEDURE Get_attributes
90 IS
91 -- Query to get all matching attributes in dedupe rule
92 CURSOR c_get_matching_attr
93 IS
94 SELECT ruleattr.attribute_id, attr.NAME
95 FROM pv_enty_select_criteria ruleattr, pv_attributes_vl attr
96 WHERE ruleattr.process_rule_id = 60
97 AND ruleattr.attribute_id = attr.attribute_id
98 AND ruleattr.selection_type_code = 'CRITERION';
99 BEGIN
100 -- Reset values
101 l_product_interest := 'N';
102 l_budget_status := 'N';
103 l_purchase_amt := 'N';
104 l_budget_amt := 'N';
105 l_purchase_timeframe := 'N';
106 l_project := 'N';
107 l_note_type := 'N';
108 l_promotion_code := 'N';
109 l_vehicle_response_code := 'N';
110 l_contact := 'N';
111 l_address := 'N';
112 l_customer := 'N';
113
114 -- Get all matching attributes
115 FOR attrs IN c_get_matching_attr
116 LOOP
117 IF attrs.attribute_id = 500
118 THEN
119 l_customer := 'Y';
120 --DebugMsg (TO_CHAR (attrs.attribute_id));
121 END IF;
122
123 IF attrs.attribute_id = 501
124 THEN
125 l_address := 'Y';
126 --DebugMsg (TO_CHAR (attrs.attribute_id));
127 END IF;
128
129 IF attrs.attribute_id = 576
130 THEN
131 l_contact := 'Y';
132 --DebugMsg (TO_CHAR (attrs.attribute_id));
133 END IF;
134
135 IF attrs.attribute_id = 513
136 THEN
137 l_vehicle_response_code := 'Y';
138 --DebugMsg (TO_CHAR (attrs.attribute_id));
139 END IF;
140
141 IF attrs.attribute_id = 16
142 THEN
143 l_promotion_code := 'Y';
144 --DebugMsg (TO_CHAR (attrs.attribute_id));
145 END IF;
146
147 IF attrs.attribute_id = 572
148 THEN
149 l_note_type := 'Y';
150 --DebugMsg (TO_CHAR (attrs.attribute_id));
151 END IF;
152
153 IF attrs.attribute_id = 514
154 THEN
155 l_project := 'Y';
156 --DebugMsg (TO_CHAR (attrs.attribute_id));
157 END IF;
158
159 IF attrs.attribute_id = 505
160 THEN
161 l_purchase_timeframe := 'Y';
162 --DebugMsg (TO_CHAR (attrs.attribute_id));
163 END IF;
164
165 IF attrs.attribute_id = 509
166 THEN
167 l_budget_amt := 'Y';
168 --DebugMsg (TO_CHAR (attrs.attribute_id));
169 END IF;
170
171 IF attrs.attribute_id = 522
172 THEN
173 l_purchase_amt := 'Y';
174 --DebugMsg (TO_CHAR (attrs.attribute_id));
175 END IF;
176
177 IF attrs.attribute_id = 506
178 THEN
179 l_budget_status := 'Y';
180 --DebugMsg (TO_CHAR (attrs.attribute_id));
181 END IF;
182
183 IF attrs.attribute_id = 510
184 THEN
185 l_product_interest := 'Y';
186 --DebugMsg (TO_CHAR (attrs.attribute_id));
187 END IF;
188
189 --DebugMsg (attrs.NAME);
190 END LOOP;
191 END Get_attributes;
192
193 --------------------------------------------------------------
194 -- Procedure : Check_contact_cp
195 -- Description : Check for duplicate contact and contact point
196 --------------------------------------------------------------
197
198 PROCEDURE Check_contact_cp (
199 p_dup_rec IN dedupe_rec_type,
200 p_contact_id OUT NOCOPY NUMBER
201 )
202 IS
203
204 CURSOR c_contact_cp IS
205 SELECT hzpc.party_id
206 FROM hz_parties hzpc,
207 hz_parties hzpr,
208 hz_relationships hzr
209 WHERE hzpc.party_id = p_dup_rec.contact_id
210 AND hzpc.party_id = hzr.subject_id
211 AND hzr.party_id = hzpr.party_id
212 AND hzr.relationship_code IN ('CONTACT_OF', 'EMPLOYEE_OF')
213 ORDER BY hzpc.party_id DESC;
214
215 BEGIN
216
217 OPEN c_contact_cp;
218 FETCH c_contact_cp INTO p_contact_id;
219 CLOSE c_contact_cp;
220 --DebugMsg ('dup contact_id- ' || TO_CHAR (p_contact_id));
221
222 END Check_contact_cp;
223
224 --------------------------------------------------------------
225 -- Procedure : Check_address
226 -- Description : To check duplicate address
227 --------------------------------------------------------------
228
229 PROCEDURE Check_address (
230 p_dup_rec IN dedupe_rec_type,
231 l_customer_id OUT NOCOPY NUMBER,
232 l_party_site_id OUT NOCOPY NUMBER
233 )
234 IS
235 CURSOR c_customer_location_chk
236 IS
237 SELECT party_site_id, hzp.party_id
238 FROM hz_locations hzl, hz_party_sites hzs, hz_parties hzp
239 WHERE hzp.party_id = p_dup_rec.party_id
240 AND hzp.party_id = hzs.party_id
241 AND hzs.location_id = hzl.location_id
242 AND hzs.party_site_id = p_dup_rec.party_site_id
243 ORDER BY hzp.party_id DESC;
244 BEGIN
245
246 OPEN c_customer_location_chk;
247 FETCH c_customer_location_chk INTO l_party_site_id, l_customer_id;
248 CLOSE c_customer_location_chk;
249 --DebugMsg ( 'dup cust location- '|| TO_CHAR (l_customer_id)|| ' - '
250 -- || TO_CHAR (l_party_site_id));
251
252 END Check_address;
253
254 --------------------------------------------------------------
255 -- Procedure : Check_dup_lead
256 -- Description : To check duplicate lead
257 --------------------------------------------------------------
258
259 PROCEDURE Check_dup_lead (
260 p_dup_rec IN dedupe_rec_type,
261 p_customer_id IN NUMBER,
262 p_party_site_id IN NUMBER,
263 p_contact_id IN NUMBER,
264 x_sales_lead_id OUT NOCOPY NUMBER
265 )
266 IS
267 CURSOR C_get_created_within_days
268 IS
269 SELECT attrvals.attribute_value
270 FROM pv_enty_select_criteria ruleattr,
271 pv_selected_attr_values attrvals
272 WHERE ruleattr.process_rule_id = 60
273 AND ruleattr.selection_type_code = 'INPUT_FILTER'
274 AND ruleattr.selection_criteria_id = attrvals.selection_criteria_id;
275
276 TYPE c_find_dup_lead_type IS REF CURSOR;
277 C_find_dup_lead c_find_dup_lead_type;
278 C_find_lead_lines c_find_dup_lead_type;
279
280 l_created_within_days NUMBER;
281 l_from_clause VARCHAR2 (100);
282 l_where_clause VARCHAR2 (4000);
283 l_sql_string VARCHAR2 (4000);
284 l_sql_lines VARCHAR2 (4000);
285 l_order_by_clause VARCHAR2 (100);
286 l_sales_lead_id NUMBER;
287 l_count NUMBER;
288 l_def_vrc VARCHAR2(30);
289 l_def_budget_status VARCHAR2(30);
290 l_def_decision_timeframe VARCHAR2(30);
291 category_tbl category_id_type;
292
293 l_cursor number;
294 l_cur_exec number;
295
296 l_line_cursor number;
297 l_line_cur_exec number;
298
299 BEGIN
300
301 DebugMsg('Starting lead dup');
302 -- Get value for created_within days attribute
303 OPEN c_get_created_within_days;
304 FETCH c_get_created_within_days INTO l_created_within_days;
305 CLOSE c_get_created_within_days;
306
307 l_def_vrc := FND_PROFILE.VALUE ('AS_DEFAULT_LEAD_VEHICLE_RESPONSE_CODE');
308 l_def_budget_status := FND_PROFILE.VALUE ('AS_DEFAULT_LEAD_BUDGET_STATUS');
309 l_def_decision_timeframe := FND_PROFILE.VALUE ('AS_DEFAULT_LEAD_DECISION_TIMEFRAME');
310
311 -- Now form the FROM and WHERE clause depending on the selected attributes in rule
312
313 -- First form mendatory select clause
314 l_sql_string := 'SELECT sales_lead_id ';
315
316 l_from_clause := 'FROM as_sales_leads asleads ';
317
318 l_where_clause := ' WHERE asleads.customer_id = :party_id ';
319 l_where_clause := l_where_clause ||' AND asleads.creation_date > trunc(sysdate - :l_created_within_days ) ';
320
321 IF l_promotion_code = 'Y' THEN
322 IF p_dup_rec.source_code IS NOT NULL THEN
323 l_from_clause := l_from_clause || ', ams_source_codes amc ';
324 l_where_clause := l_where_clause ||' AND asleads.source_promotion_id = amc.source_code_id ';
325 l_where_clause := l_where_clause ||' AND amc.source_code = UPPER(:source_code) ';
326 ELSIF p_dup_rec.source_code IS NULL THEN
327 l_where_clause := l_where_clause ||' AND asleads.source_promotion_id IS NULL ';
328 END IF;
329 END IF;
330
331 IF l_vehicle_response_code = 'Y' THEN
332 l_where_clause := l_where_clause ||' AND nvl(asleads.vehicle_response_code,nvl(:l_def_vrc,''xx'')) ';
333 l_where_clause := l_where_clause ||' = nvl(:vehicle_response_code ,nvl(:l_def_vrc,''xx'')) ';
334 END IF;
335
336 -- Add where clause based on attributes in rule
337 IF l_contact = 'Y' THEN
338 IF p_dup_rec.contact_id IS NOT NULL THEN
339 l_where_clause := l_where_clause ||' AND asleads.primary_cnt_person_party_id = :contact_id ';
340 ELSIF p_dup_rec.contact_id IS NULL THEN
341 l_where_clause := l_where_clause ||' AND asleads.primary_cnt_person_party_id IS NULL ';
342 END IF;
343 END IF;
344
345 IF l_address = 'Y' THEN
346 IF p_dup_rec.party_site_id IS NOT NULL THEN
347 l_where_clause := l_where_clause ||' AND asleads.address_id = :party_site_id ';
348 ELSIF p_dup_rec.party_site_id IS NULL THEN
349 l_where_clause := l_where_clause ||' AND asleads.address_id IS NULL ';
350 END IF;
351 END IF;
352
353 IF l_note_type = 'Y'
354 THEN
355 l_from_clause := l_from_clause ||', jtf_notes_vl notes ';
356 l_where_clause := l_where_clause||' AND asleads.sales_lead_id = notes.source_object_id(+)';
357 l_where_clause := l_where_clause||' AND nvl(notes.source_object_code,''LEAD'') = ''LEAD''';
358 IF p_dup_rec.lead_note IS NOT NULL THEN --bug 3436346
359 l_where_clause := l_where_clause||' AND nvl(notes.note_type,''AS_USER'') = nvl(:note_type,''AS_USER'') ';
360 END IF;
361 l_where_clause := l_where_clause||' AND nvl(notes.notes,''xx'') = nvl(:lead_note,''xx'') ';
362 END IF;
363
364 IF l_budget_status = 'Y' THEN
365 l_where_clause := l_where_clause ||' AND nvl(asleads.budget_status_code,nvl(:l_def_budget_status,''xx'')) ';
366 l_where_clause := l_where_clause ||' = nvl(:budget_status_code ,nvl(:l_def_budget_status,''xx'')) ';
367 END IF;
368
369 IF l_purchase_amt = 'Y' THEN
370 IF p_dup_rec.purchase_amount IS NULL THEN
371 l_where_clause := l_where_clause || ' AND nvl(asleads.total_amount,0) = 0 ';
372 ELSE
373 l_where_clause := l_where_clause || ' AND nvl(asleads.total_amount,0) = :purchase_amount ';
374 END IF;
375 END IF;
376
377 IF l_budget_amt = 'Y' THEN
378 IF p_dup_rec.budget_amount IS NULL THEN
379 l_where_clause := l_where_clause || ' AND nvl(asleads.budget_amount,0) = 0 ';
380 ELSE
381 l_where_clause := l_where_clause || ' AND nvl(asleads.budget_amount,0) = :budget_amount ';
382 END IF;
383 END IF;
384
385 IF l_purchase_timeframe = 'Y' THEN
386 l_where_clause := l_where_clause ||' AND nvl(asleads.decision_timeframe_code,nvl(:l_def_decision_timeframe,''xx'')) ';
387 l_where_clause := l_where_clause ||' = nvl(:purchase_timeframe_code, nvl(:l_def_decision_timeframe,''xx'')) ';
388 END IF;
389
390 IF l_project = 'Y' THEN
391 l_where_clause := l_where_clause || ' AND nvl(asleads.parent_project,''xx'') = nvl(:project_code,''xx'') ';
392 END IF;
393
394 l_order_by_clause := ' ORDER BY asleads.creation_date DESC ';
395
396 l_sql_string := l_sql_string || l_from_clause || l_where_clause || l_order_by_clause;
397 --DebugMsg (l_from_clause);
398
399 /*
400 insert into aaa_log
401 values(sysdate, l_sql_string);
402 */
403
404 --PARSE
405 l_cursor := DBMS_SQL.open_cursor;
406 DBMS_SQL.parse(l_cursor, l_sql_string, DBMS_SQL.native);
407
408 --DEFINE_COLUMN
409 DBMS_SQL.define_column(l_cursor, 1, l_sales_lead_id);
410
411 --BIND VARIABLES
412 DBMS_SQL.bind_variable(l_cursor, 'party_id', p_dup_rec.party_id);
413 DBMS_SQL.bind_variable(l_cursor, 'l_created_within_days', l_created_within_days);
414
415 IF l_promotion_code = 'Y' AND p_dup_rec.source_code IS NOT NULL THEN
416 DBMS_SQL.bind_variable(l_cursor, 'source_code', p_dup_rec.source_code);
417 END IF;
418
419 IF l_vehicle_response_code = 'Y' THEN
420 DBMS_SQL.bind_variable(l_cursor, 'l_def_vrc', l_def_vrc);
421 DBMS_SQL.bind_variable(l_cursor, 'vehicle_response_code', p_dup_rec.vehicle_response_code);
422 END IF;
423
424 IF l_contact = 'Y' AND p_dup_rec.contact_id IS NOT NULL THEN
425 DBMS_SQL.bind_variable(l_cursor, 'contact_id', p_dup_rec.contact_id);
426 END IF;
427
428 IF l_address = 'Y' AND p_dup_rec.party_site_id IS NOT NULL THEN
429 DBMS_SQL.bind_variable(l_cursor, 'party_site_id', p_dup_rec.party_site_id);
430 END IF;
431
432 IF l_note_type = 'Y' THEN
433 IF p_dup_rec.lead_note IS NOT NULL THEN
434 DBMS_SQL.bind_variable(l_cursor, 'note_type', p_dup_rec.note_type);
435 END IF;
436 DBMS_SQL.bind_variable(l_cursor, 'lead_note', p_dup_rec.lead_note);
437 END IF;
438
439 IF l_budget_status = 'Y' THEN
440 DBMS_SQL.bind_variable(l_cursor, 'l_def_budget_status', l_def_budget_status);
441 DBMS_SQL.bind_variable(l_cursor, 'budget_status_code', p_dup_rec.budget_status_code);
442 END IF;
443
444 IF l_purchase_amt = 'Y' AND p_dup_rec.purchase_amount IS NOT NULL THEN
445 DBMS_SQL.bind_variable(l_cursor, 'purchase_amount', p_dup_rec.purchase_amount);
446 END IF;
447
448 IF l_budget_amt = 'Y' AND p_dup_rec.budget_amount IS NOT NULL THEN
449 DBMS_SQL.bind_variable(l_cursor, 'budget_amount', p_dup_rec.budget_amount);
450 END IF;
451
452 IF l_purchase_timeframe = 'Y' THEN
453 DBMS_SQL.bind_variable(l_cursor, 'l_def_decision_timeframe', l_def_decision_timeframe);
454 DBMS_SQL.bind_variable(l_cursor, 'purchase_timeframe_code', p_dup_rec.purchase_timeframe_code);
455 END IF;
456
457 IF l_project = 'Y' THEN
458 DBMS_SQL.bind_variable(l_cursor, 'project_code', p_dup_rec.project_code);
459 END IF;
460
461 --EXECUTE CURSOR
462 l_cur_exec := DBMS_SQL.execute(l_cursor);
463
464 l_sales_lead_id := null;
465
466 IF l_product_interest = 'Y' AND p_dup_rec.category_id_tbl.COUNT > 0 THEN
467
468 LOOP
469 IF DBMS_SQL.fetch_rows(l_cursor)>0 THEN
470
471 -- get column values of the row
472 DBMS_SQL.column_value(l_cursor, 1, l_sales_lead_id);
473
474 category_tbl := p_dup_rec.category_id_tbl;
475 remove_duplicate (category_tbl);
476
477 l_sql_string := 'SELECT count(distinct category_id) l_count ';
478 l_from_clause := 'FROM as_sales_lead_lines ';
479 l_where_clause := 'WHERE sales_lead_id = :p_sales_lead_id ';
480 l_where_clause := l_where_clause ||' AND category_id IN (';
481
482 FOR i IN 1..category_tbl.COUNT LOOP
483 IF i <> 1 THEN
484 l_where_clause := l_where_clause || ',';
485 END IF;
486 l_where_clause := l_where_clause || to_char(category_tbl(i));
487 END LOOP;
488 l_where_clause := l_where_clause || ') ';
489
490 l_sql_lines := l_sql_string || l_from_clause || l_where_clause;
491
492 --PARSE
493 l_line_cursor := DBMS_SQL.open_cursor;
494 DBMS_SQL.parse(l_line_cursor, l_sql_lines, DBMS_SQL.native);
495
496 --DEFINE_COLUMN
497 DBMS_SQL.define_column(l_line_cursor, 1, l_count);
498
499 --BIND VARIABLES
500 DBMS_SQL.bind_variable(l_line_cursor, 'p_sales_lead_id', l_sales_lead_id);
501
502 --EXECUTE CURSOR
503 l_line_cur_exec := DBMS_SQL.execute(l_line_cursor);
504 IF DBMS_SQL.fetch_rows(l_line_cursor)>0 THEN
505 -- get column values of the row
506 DBMS_SQL.column_value(l_line_cursor, 1, l_count);
507 END IF;
508
509 DebugMsg(to_char(l_sales_lead_id)||'-'||to_char(l_count)||'-'||to_char(category_tbl.COUNT));
510
511 DBMS_SQL.close_cursor(l_line_cursor); -- Added by bmuthukr to fix bug# 3737789.
512
513 IF l_count = category_tbl.COUNT THEN
514 x_sales_lead_id := l_sales_lead_id;
515 EXIT;
516 END IF;
517
518 ELSE --fetch_rows = 0
519 EXIT;
520 END IF;
521
522 END LOOP;
523 --CLOSE C_find_dup_lead;
524 --DBMS_SQL.close_cursor(l_line_cursor);--The cursor should be closed inside its scope.
525 ELSE --product interest
526
527 IF DBMS_SQL.FETCH_ROWS(l_cursor)>0 THEN
528 -- get column values of the row
529 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_sales_lead_id);
530 END IF;
531 x_sales_lead_id := l_sales_lead_id;
532
533 END IF;
534 DBMS_SQL.close_cursor(l_cursor);
535
536 END Check_dup_lead;
537
538 --------------------------------------------------------------
539 -- Procedure : Main
540 -- Description : This is the main procedure which will be
541 -- called by the integrating application
542 --------------------------------------------------------------
543
544 PROCEDURE Main (
545 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
546 p_dedupe_rec IN dedupe_rec_type, -- Input Lead Record
547 x_duplicate_flag OUT NOCOPY VARCHAR2, -- D/U
548 x_sales_lead_id OUT NOCOPY NUMBER,
549 x_return_status OUT NOCOPY VARCHAR2,
550 x_msg_count OUT NOCOPY NUMBER,
551 x_msg_data OUT NOCOPY VARCHAR2
552 )
553 IS
554 l_duplicate_flag VARCHAR2 (1) := 'U';
555 l_contact_id hz_parties.party_id%TYPE;
556 l_party_id hz_parties.party_id%TYPE;
557 l_party_site_id hz_party_sites.party_site_id%TYPE;
558 l_sales_lead_id as_sales_leads.sales_lead_id%TYPE;
559
560 BEGIN
561 SAVEPOINT MAIN_PVT;
562
563 -- Initialize message list IF p_init_msg_list is set to TRUE.
564 IF FND_API.to_Boolean( p_init_msg_list )
565 THEN
566 FND_MSG_PUB.initialize;
567 END IF;
568
569 -- Initialize API return status to SUCCESS
570 x_return_status := FND_API.G_RET_STS_SUCCESS;
571
572 IF fnd_profile.VALUE ('PV_RUN_LEAD_DUPLICATION_RULE') = 'N'
573 THEN
574 DebugMsg ('Profile value is NO..exiting');
575 x_duplicate_flag := l_duplicate_flag;
576 return;
577 END IF;
578
579 --First get all the attributes selected in dedupe rule
580 Get_attributes;
581
582 IF l_contact = 'Y' AND p_dedupe_rec.contact_id IS NOT NULL
583 THEN
584 DebugMsg ('in contact ddupe check');
585 --Check for contact and contact point existence
586 Check_contact_cp (p_dedupe_rec, l_contact_id);
587 --DebugMsg ('contact_id frm check_con_cp: '|| TO_CHAR (l_contact_id));
588
589 IF l_contact_id IS NULL
590 THEN
591 x_duplicate_flag := l_duplicate_flag;
592 DebugMsg ('Dup contact cp failed..exiting');
593 RETURN;
594 END IF;
595 END IF;
596
597 IF l_address = 'Y' AND p_dedupe_rec.party_site_id IS NOT NULL
598 THEN
599 DebugMsg ('in address ddupe check');
600 --Check for customer address existence
601 Check_address (p_dedupe_rec, l_party_id, l_party_site_id);
602
603 IF l_party_site_id IS NULL
604 THEN
605 x_duplicate_flag := l_duplicate_flag;
606 DebugMsg ('Dup address failed..exiting');
607 RETURN;
608 END IF;
609 END IF;
610
611 Check_dup_lead (p_dedupe_rec,
612 l_party_id,
613 l_party_site_id,
614 l_contact_id,
615 l_sales_lead_id
616 );
617 DebugMsg ('Dup lead- ' || l_sales_lead_id);
618
619 IF l_sales_lead_id IS NULL
620 THEN
621 x_duplicate_flag := l_duplicate_flag;
622 x_sales_lead_id := NULL;
623 ELSE
624 x_duplicate_flag := 'D';
625 x_sales_lead_id := l_sales_lead_id;
626 END IF;
627
628 EXCEPTION
629 WHEN others THEN
630 DebugMsg('aml_lead_dedupe_pvt failed');
631
632 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
633 P_API_NAME => 'MAIN'
634 ,P_PKG_NAME => 'AML_LEAD_DEDUPE_PVT'
635 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
636 ,P_SQLCODE => SQLCODE
637 ,P_SQLERRM => SQLERRM
638 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
639 ,X_MSG_COUNT => X_MSG_COUNT
640 ,X_MSG_DATA => X_MSG_DATA
641 ,X_RETURN_STATUS => X_RETURN_STATUS);
642
643 END Main;
644
645 END aml_lead_dedupe_pvt;