[Home] [Help]
PACKAGE BODY: APPS.AS_IMPORT_SL_PVT
Source
1 package body as_import_sl_pvt as
2 /* $Header: asxslimb.pls 120.10 2006/01/27 17:43:03 solin ship $ */
3
4 --impView as_imp_sl_v%rowtype;
5 --subtype leadImpView is impView%Type;
6 cursor imptype is select * from as_import_interface;
7 subtype leadImpType is impType%rowtype;
8
9 cursor cptype is select * from as_imp_cnt_pnt_interface;
10 subtype cntPntType is cptype%rowtype;
11
12 G_LOCAL_ORG_CONTACT_ID number := null; -- This is to store org_contact_id
13 G_SL_LINE_COUNT number := 0; -- This is to count SL Lines
14
15 -- Added by Ajoy
16 G_SL_SALESFORCE_ID number; -- This is to identify salesforce_id of the logged in user
17
18 -- Bugfix# 2835357, Call user hook once
19 G_CALL_USER_HOOK boolean := JTF_USR_HKS.Ok_to_execute('AS_IMPORT_SL_PVT', 'IS_DUPLICATE_LEAD','B','C');
20
21 --------------------------------------------------------
22 -- name: write_log
23 -- scope: private
24 -- used to write to log or output
25 --------------------------------------------------------
26 procedure write_log(p_mode in Number,p_mesg in Varchar2) is
27 begin
28 -- p_mode = 1 means write to output
29 -- p_mode = 2 means write to log
30 -- p_mode = 3 means debug mode
31
32 if (p_mode in (2,3)) then -- debugging
33 if ((p_mode = 3) and (G_DEBUGFLAG = 'Y'))then
34 fnd_file.put(log_fpt, substr(p_mesg,1,255));
35 fnd_file.new_line(log_fpt,1);
36 elsif (p_mode =2) then
37 fnd_file.put(log_fpt, substr(p_mesg,1,255));
38 fnd_file.new_line(log_fpt,1);
39 else
40 null;
41 end if;
42 else
43 fnd_file.put(output_fpt, substr(p_mesg,1,255));
44 fnd_file.new_line(output_fpt,1);
45 end if;
46
47 -- dbms_output.put_line (substr(p_mesg,1,255));
48 end write_log;
49
50 --------------------------------------------------------
51 -- name: write_errors
52 -- scope: private
53 -- insert to error table
54 -- swkhanna April 23,2002, added an extra variable p_error_type
55 -- to take care of unexpected errors
56 --------------------------------------------------------
57 procedure write_errors(
58 pI IN leadImpType,
59 p_error_type IN varchar2,
60 G_return_status OUT NOCOPY varchar2) Is
61 l_msg_data VARCHAR2(2000) := Null;
62 l_msg_index_out number;
63 Begin
64
65 if p_error_type in ('EXP','UNEXP','OTHER') then
66 G_MESG_COUNT := FND_MSG_PUB.Count_Msg;
67 write_log(3, 'Message Count:'||G_MESG_COUNT);
68 For i IN 1..G_MESG_COUNT Loop
69 FND_MSG_PUB.Get(
70 p_msg_index => i,
71 p_encoded => FND_API.G_FALSE,
72 p_data => l_msg_data,
73 p_msg_index_out =>l_msg_index_out
74 );
75
76 -- ffang 042601, for bug 1751324, add 4 new columns
77 insert into as_lead_import_errors(
78 lead_import_error_id,
79 last_updated_by,
80 last_update_date ,
81 creation_date,
82 created_by,
83 last_update_login,
84 import_interface_id ,
85 batch_id ,
86 error_text ,
87 request_id,
88 program_application_id,
89 program_id,
90 program_update_date
91 )
92 values (
93 as_lead_import_errors_s.nextval,
94 nvl(FND_GLOBAL.User_id, -1),
95 sysdate,
96 sysdate,
97 nvl(FND_GLOBAL.User_id, -1),
98 nvl(FND_GLOBAL.Login_id, -1),
99 pI.import_interface_id,
100 nvl(pI.batch_id,-1),
101 l_msg_data,
102 pI.request_id,
103 pI.program_application_id,
104 pI.program_id,
105 pI.program_update_date
106 );
107 End Loop;
108 end if;
109 --
110 if p_error_type in ('UNEXP','OTHER') then
111 l_msg_data := substr(SQLERRM,1,2000);
112 -- insert sqlerrm
113 insert into as_lead_import_errors(
114 lead_import_error_id,
115 last_updated_by,
116 last_update_date ,
117 creation_date,
118 created_by,
119 last_update_login,
120 import_interface_id ,
121 batch_id ,
122 error_text ,
123 request_id,
124 program_application_id,
125 program_id,
126 program_update_date
127 )
128 values (
129 as_lead_import_errors_s.nextval,
130 nvl(FND_GLOBAL.User_id, -1),
131 sysdate,
132 sysdate,
133 nvl(FND_GLOBAL.User_id, -1),
134 nvl(FND_GLOBAL.Login_id, -1),
135 pI.import_interface_id,
136 nvl(pI.batch_id,-1),
137 l_msg_data,
138 pI.request_id,
139 pI.program_application_id,
140 pI.program_id,
141 pI.program_update_date
142 );
143 end if;
144 -- dbms_output.put_line(l_msg_data);
145 Exception
146 when others then
147 write_log(2, 'write_errors failed!');
148 G_return_status := FND_API.G_RET_STS_ERROR;
149 END write_errors;
150
151 --------------------------------------------------------
152 -- name: writeBak
153 -- scope: private
154 -- Updates the as_import_interface table
155 --------------------------------------------------------
156 procedure writeBak(
157 pI IN leadImpType,
158 G_return_status OUT NOCOPY varchar2)
159 IS
160 BEGIN
161 if (pI.load_status = G_LOAD_STATUS_SUCC) then
162 update as_import_interface
163 set load_status = G_LOAD_STATUS_SUCC,
164 party_id = pI.party_id,
165 party_site_id = pI.party_site_id,
166 location_id = pI.location_id,
167 sales_lead_id = pI.sales_lead_id,
168 contact_party_id = pI.contact_party_id,
169 rel_party_id = pI.rel_party_id,
170 new_party_flag = pI.new_party_flag,
171 new_loc_flag = pI.new_loc_flag,
172 new_ps_flag = pI.new_ps_flag,
173 new_rel_flag = pI.new_rel_flag,
174 -- ffang 102301, bug 2071826, write new_con_flag back.
175 new_con_flag = pI.new_con_flag,
176 -- end 102301
177 last_update_date = sysdate,
178 last_updated_by = nvl(FND_GLOBAL.User_id, -1),
179 last_update_login = nvl(FND_GLOBAL.Login_id, -1),
180 request_id = nvl(FND_GLOBAL.conc_request_id, -1),
181 program_application_id = nvl(FND_GLOBAL.Prog_appl_id, -1),
182 program_id = nvl(FND_GLOBAL.conc_program_id, -1),
183 program_update_date = sysdate,
184 -- ffang 101601, bug 2053591, populate promotion_id / promotion_code
185 promotion_id = pI.promotion_id,
186 -- swkhanna 05/28/02 2385197
187 promotion_code = UPPER(pI.promotion_code),
188 -- swkhanna 07/30/02 write bal assign_to_person_id
189 assign_to_person_id = pI.assign_to_person_id
190 where import_interface_id = pI.import_interface_id;
191 --where rowid = pI.rowid;
192 elsif (pI.load_status = G_LOAD_STATUS_ERR) then
193 update as_import_interface
194 set load_status = G_LOAD_STATUS_ERR,
195 -- ffang 101001, bug 2044483, if error, don't update those ids/flags
196 -- party_id = pI.party_id,
197 -- party_site_id = pI.party_site_id,
198 -- location_id = pI.location_id,
199 -- sales_lead_id = pI.sales_lead_id,
200 -- contact_party_id = pI.contact_party_id,
201 -- rel_party_id = pI.rel_party_id,
202 -- new_party_flag = pI.new_party_flag,
203 -- new_loc_flag = pI.new_loc_flag,
204 -- new_ps_flag = pI.new_ps_flag,
205 -- new_rel_flag = pI.new_rel_flag,
206 last_update_date = sysdate,
207 last_updated_by = nvl(FND_GLOBAL.User_id, -1),
208 last_update_login = nvl(FND_GLOBAL.Login_id, -1),
209 request_id = nvl(FND_GLOBAL.conc_request_id, -1),
210 program_application_id = nvl(FND_GLOBAL.Prog_appl_id, -1),
211 program_id = nvl(FND_GLOBAL.conc_program_id, -1),
212 program_update_date = sysdate
213 where import_interface_id = pI.import_interface_id;
214 --where rowid = pI.rowid;
215 elsif (pI.load_status = G_LOAD_STATUS_UNEXP_ERR) then
216 update as_import_interface
217 set load_status = G_LOAD_STATUS_UNEXP_ERR,
218 -- ffang 101001, bug 2044483, if error, don't update those ids/flags
219 -- party_id = pI.party_id,
220 -- party_site_id = pI.party_site_id,
221 -- location_id = pI.location_id,
222 -- sales_lead_id = pI.sales_lead_id,
223 -- contact_party_id = pI.contact_party_id,
224 -- rel_party_id = pI.rel_party_id,
225 -- new_party_flag = pI.new_party_flag,
226 -- new_loc_flag = pI.new_loc_flag,
227 -- new_ps_flag = pI.new_ps_flag,
228 -- new_rel_flag = pI.new_rel_flag,
229 last_update_date = sysdate,
230 last_updated_by = nvl(FND_GLOBAL.User_id, -1),
231 last_update_login = nvl(FND_GLOBAL.Login_id, -1),
232 request_id = nvl(FND_GLOBAL.conc_request_id, -1),
233 program_application_id = nvl(FND_GLOBAL.Prog_appl_id, -1),
234 program_id = nvl(FND_GLOBAL.conc_program_id, -1),
235 program_update_date = sysdate
236 where import_interface_id = pI.import_interface_id;
237 --where rowid = pI.rowid;
238 elsif (pI.load_status = 'DUPLICATE') then
239 update as_import_interface
240 set load_status = 'DUPLICATE', sales_lead_id = pI.sales_lead_id
241 where import_interface_id = pI.import_interface_id;
242 else
243 G_return_status := FND_API.G_RET_STS_ERROR;
244 End if;
245 Exception
246 when others then
247 write_log(2, 'writeBak failed!');
248 G_return_status := FND_API.G_RET_STS_ERROR;
249 end writeBak;
250
251 --------------------------------------------------------
252 -- name: cont_pnt_dedupe
253 -- scope: private
254 -- used to check duplicate contact points
255 -------------------------------- ------------------------
256
257 procedure cont_pnt_dedupe(pI IN OUT NOCOPY leadImpType,
258 p_dup_phone OUT NOCOPY varchar2,
259 p_dup_email OUT NOCOPY varchar2,
260 p_dup_fax OUT NOCOPY varchar2,
261 p_dup_url OUT NOCOPY varchar2
262 ) is
263
264 cursor c_chk_cont_pnt (c_contact_point_type varchar2,
265 c_owner_table_id number,
266 c_email_address varchar2,
267 c_phone_area_code varchar2,
268 c_phone_number varchar2,
269 c_phone_line_type varchar2,
270 c_url varchar2) IS
271 select contact_point_id
272 from hz_contact_points
273 where owner_table_id = c_owner_table_id
274 and owner_table_name = 'HZ_PARTIES'
275 and nvl(email_address,'1') = nvl(c_email_address,'1')
276 and nvl(phone_area_code,'1') = nvl(c_phone_area_code,'1')
277 and nvl(phone_number,'1') = nvl(c_phone_number,'1')
278 and nvl(phone_line_type,'1') = nvl(c_phone_line_type,'1')
279 and nvl(url,'1') = nvl(c_url,'1')
280 and contact_point_type = c_contact_point_type
281 ;
282
283 l_contact_point_id number;
284 BEGIN
285 --dbms_output.put_line('c_owner_table_id:'||pI.rel_party_id);
286 --dbms_output.put_line('c_email_address:'||pI.email_address);
287 --dbms_output.put_line('phone_area_code:'||pI.area_code);
288 --dbms_output.put_line('phone_number:'||pI.phone_number);
289 --dbms_output.put_line('phone_line_type:'||pI.phone_type);
290 --dbms_output.put_line('url:'||pI.url);
291
292 p_dup_phone := 'N';
293 p_dup_email := 'N';
294 p_dup_fax := 'N';
295 p_dup_url := 'N';
296
297 If (pI.phone_number is not null) then
298 --
299 open c_chk_cont_pnt ('PHONE',pI.rel_party_id,null,
300 pI.area_code,pI.phone_number,pI.phone_type,
301 null);
302
303 fetch c_chk_cont_pnt into l_contact_point_id;
304 if c_chk_cont_pnt%FOUND then
305 p_dup_phone := 'Y';
306 else
307 p_dup_phone := 'N';
308 end if;
309 close c_chk_cont_pnt;
310 end if;
311
312
313 If (pI.email_address is not null) then
314 --
315 open c_chk_cont_pnt ('EMAIL',pI.rel_party_id,pI.email_address,
316 null,null,null, null);
317
318 fetch c_chk_cont_pnt into l_contact_point_id;
319 if c_chk_cont_pnt%FOUND then
320 p_dup_email := 'Y';
321 else
322 p_dup_email := 'N';
323 end if;
324 close c_chk_cont_pnt;
325 end if;
326
327 If (pI.fax_number is not null) then
328 --
329 open c_chk_cont_pnt ('PHONE',pI.rel_party_id,null,
330 null,pI.fax_number,'FAX',
331 null);
332 fetch c_chk_cont_pnt into l_contact_point_id;
333 if c_chk_cont_pnt%FOUND then
334 p_dup_fax := 'Y';
335 else
336 p_dup_fax := 'N';
337 end if;
338 close c_chk_cont_pnt;
339 end if;
340
341 If (pI.url is not null) then
342 --
343 open c_chk_cont_pnt ('WEB',pI.rel_party_id,null,
344 null,null,null,
345 pI.url);
346
347 fetch c_chk_cont_pnt into l_contact_point_id;
348 if c_chk_cont_pnt%FOUND then
349 p_dup_url := 'Y';
350 else
351 p_dup_url := 'N';
352 end if;
353 close c_chk_cont_pnt;
354 end if;
355
356 -- dbms_output.put_line('l_contact_point_id:'||l_contact_point_id);
357
358
359 END;
360
361
362 --------------------------------------------------------
363 -- name: deDupe_Check
364 -- scope: private
365 -- used to check duplicate leads
366 -------------------------------- ------------------------
367 procedure deDupe_Check(pI IN OUT NOCOPY leadImpType,
368 x_duplicate_lead OUT NOCOPY varchar2,
369 x_dup_sales_lead_id OUT NOCOPY number) is
370
371 x_return_status varchar2(1);
372 x_msg_count number;
373 x_msg_data varchar2(2000);
374 l_contact_party_id number;
375 total_amount number;
376
377 dup_rec AML_LEAD_DEDUPE_PVT.dedupe_rec_type;
378 int_rec AML_LEAD_DEDUPE_PVT.category_id_type;
379 idx number;
380
381 CURSOR C_get_lines (c_import_interface_id number)
382 IS
383 select category_id, sum(budget_amount) budget_amount
384 from as_imp_lines_interface
385 where import_interface_id = c_import_interface_id
386 and category_id is not null
387 group by category_id;
388
389 BEGIN
390
391 write_log(3, 'Inside dedupe_check');
392 /*
393 IF pI.party_type = 'PERSON' THEN -- for person dedupe check
394 l_contact_party_id := pI.party_id;
395 ELSE -- for org dedupe check
396 l_contact_party_id := pI.contact_party_id;
397 END IF;
398 */
399 --Populate inerest type tbl
400 idx := 0;
401 total_amount := 0;
402
403 IF pI.category_id_1 IS NOT NULL THEN
404 idx := idx + 1;
405 int_rec(idx) := pI.category_id_1;
406 total_amount := total_amount + nvl(pI.budget_amount_1,0);
407 END IF;
408
409 IF pI.category_id_2 IS NOT NULL THEN
410 idx := idx + 1;
411 int_rec(idx) := pI.category_id_2;
412 total_amount := total_amount + nvl(pI.budget_amount_2,0);
413 END IF;
414
415 IF pI.category_id_3 IS NOT NULL THEN
416 idx := idx + 1;
417 int_rec(idx) := pI.category_id_3;
418 total_amount := total_amount + nvl(pI.budget_amount_3,0);
419 END IF;
420
421 IF pI.category_id_4 IS NOT NULL THEN
422 idx := idx + 1;
423 int_rec(idx) := pI.category_id_4;
424 total_amount := total_amount + nvl(pI.budget_amount_4,0);
425 END IF;
426
427 IF pI.category_id_5 IS NOT NULL THEN
428 idx := idx + 1;
429 int_rec(idx) := pI.category_id_5;
430 total_amount := total_amount + nvl(pI.budget_amount_5,0);
431 END IF;
432
433 FOR line IN C_get_lines(pI.import_interface_id) LOOP
434 idx := idx + 1;
435 int_rec(idx) := line.category_id;
436 total_amount := total_amount + nvl(line.budget_amount,0);
437 END LOOP;
438
439 dup_rec.party_id := pI.party_id;
440 dup_rec.party_site_id := pI.party_site_id;
441 dup_rec.contact_id := pI.contact_party_id;
442 dup_rec.vehicle_response_code := pI.vehicle_response_code;
443 dup_rec.source_code := pI.promotion_code;
444 dup_rec.lead_note := pI.lead_note;
445 dup_rec.note_type := pI.note_type;
446 dup_rec.budget_amount := pI.budget_amount;
447 dup_rec.purchase_amount := total_amount;
448 dup_rec.budget_status_code := pI.budget_status_code;
449 dup_rec.project_code := pI.parent_project;
450 dup_rec.purchase_timeframe_code := pI.decision_timeframe_code;
451 dup_rec.category_id_tbl := int_rec;
452
453
454 --Call dedupe API
455 AML_LEAd_DEDUPE_PVT.Main (
456 'T', dup_rec, x_duplicate_lead,
457 x_dup_sales_lead_id,
458 x_return_status,
459 x_msg_count,
460 x_msg_data
461 );
462
463 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
465 END IF;
466
467 End deDupe_Check;
468
469 --------------------------------------------------------
470 -- name: validate_primary_cp
471 -- scope: private
472 -- used to validate the primary contact point for PHONE type.
473 -- After the contact_points are created, query the database to
474 -- see if there are at least one primary contact point for the
475 -- contact_type = 'PHONE'. If there are not, update 1 primary
476 -- contact as primary contact point. This will be done by a new
477 -- procedure validate_primary_cp.
478 --
479 -- Added by Ajoy, bugfix : 2098158
480 --------------------------------------------------------
481 procedure validate_primary_cp( pI IN OUT NOCOPY leadImpType,
482 G_return_status OUT NOCOPY varchar2) is
483
484 l_no_of_primary_cps NUMBER;
485
486 begin
487 SELECT COUNT(*) NO_PRIMARY_CPS
488 INTO l_no_of_primary_cps
489 FROM HZ_CONTACT_POINTS
490 WHERE CONTACT_POINT_TYPE = 'PHONE' AND PRIMARY_FLAG = 'Y'
491 AND OWNER_TABLE_NAME = 'HZ_PARTIES' AND OWNER_TABLE_ID = pI.rel_party_id;
492
493 write_log(3, 'AC : No. of primary contact points found ' || l_no_of_primary_cps || ' for part rel id : ' || pI.rel_party_id);
494
495 -- Validation
496 If l_no_of_primary_cps < 1 then
497 UPDATE HZ_CONTACT_POINTS
498 SET PRIMARY_FLAG = 'Y'
499 WHERE owner_table_name = 'HZ_PARTIES'
500 AND contact_point_type = 'PHONE'
501 AND owner_table_id = pI.rel_party_id
502 AND ROWNUM = 1; --to update 1 row
503
504 write_log(3, 'Primary contact point is set from validate_primary_cp API');
505 end if;
506 end validate_primary_cp;
507
508
509 --------------------------------------------------------
510 -- name: do_assign_flex
511 -- scope: private
512 -- assign the Recs with flex values
513 -- note: currently, the following entities are only supported
514 -- 1. HZ_PARTIES
515 -- 2. HZ_LOCATIONS
516 -- 3. HZ_CONTACT_POINTS
517 -- 4. HZ_PARTY_SITES
518 -- 5. HZ_ORG_CONTACTS
519 -- 6. AS_SALES_LEADS
520 -- 7. AS_SALES_LEAD_LINES
521 -- 8. AS_SALES_LEAD_CONTACTS
522 --------------------------------------------------------
523 procedure do_assign_flex (
524 pHzpRec in OUT NOCOPY hz_party_v2pub.party_rec_type,
525 pHzlRec in OUT NOCOPY hz_location_v2pub.location_rec_type,
526 pHzcpRec in OUT NOCOPY hz_contact_point_v2pub.contact_point_rec_type,
527 pHzpsRec in OUT NOCOPY hz_party_site_v2pub.party_site_rec_type,
528 pHzocRec in OUT NOCOPY hz_party_contact_v2pub.org_contact_rec_type,
529 -- pHocrRec in out hz_party_pub.org_contact_role_rec_type,
530 pAsslRec in OUT NOCOPY as_sales_leads_pub.sales_lead_rec_type,
531 pAssllTbl in OUT NOCOPY as_sales_leads_pub.sales_lead_line_tbl_type,
532 pAsslcTbl in OUT NOCOPY as_sales_leads_pub.sales_lead_contact_tbl_type,
533 pEntity in varchar2,
534 pIId in Number,
535 G_return_status OUT NOCOPY varchar2)
536 IS
537 Cursor cGetFlex is
538 Select attr_val_category, attr_val_1, attr_val_2,
539 attr_val_3, attr_val_4, attr_val_5, attr_val_6,
540 attr_val_7, attr_val_8, attr_val_9, attr_val_10,
541 attr_val_11, attr_val_12, attr_val_13, attr_val_14,
542 attr_val_15, attr_val_16, attr_val_17, attr_val_18,
543 attr_val_19, attr_val_20, attr_val_21, attr_val_22,
544 attr_val_23, attr_val_24-- , gattr_val_category,
545 -- gattr_val_1, gattr_val_2, gattr_val_4, gattr_val_3,
546 -- gattr_val_5, gattr_val_6, gattr_val_7, gattr_val_8,
547 -- gattr_val_9, gattr_val_10, gattr_val_11, gattr_val_12,
548 -- gattr_val_13, gattr_val_14, gattr_val_15, gattr_val_16,
549 -- gattr_val_17, gattr_val_18, gattr_val_19, gattr_val_20
550 From as_imp_sl_flex
551 Where import_interface_id = pIId
552 and entity_name = pEntity;
553 l_index NUMBER := 1;
554
555 BEGIN
556 For I in cGetFlex Loop
557 if (pEntity = 'HZ_PARTIES') then
558 pHzpRec.attribute_category := I.attr_val_category;
559 pHzpRec.attribute1 := I.attr_val_1;
560 pHzpRec.attribute2 := I.attr_val_2;
561 pHzpRec.attribute3 := I.attr_val_3;
562 pHzpRec.attribute4 := I.attr_val_4;
563 pHzpRec.attribute5 := I.attr_val_5;
564 pHzpRec.attribute6 := I.attr_val_6;
565 pHzpRec.attribute7 := I.attr_val_7;
566 pHzpRec.attribute8 := I.attr_val_8;
567 pHzpRec.attribute9 := I.attr_val_9;
568 pHzpRec.attribute10 := I.attr_val_10;
569 pHzpRec.attribute11 := I.attr_val_11;
570 pHzpRec.attribute12 := I.attr_val_12;
571 pHzpRec.attribute13 := I.attr_val_13;
572 pHzpRec.attribute14 := I.attr_val_14;
573 pHzpRec.attribute15 := I.attr_val_15;
574 pHzpRec.attribute16 := I.attr_val_16;
575 pHzpRec.attribute17 := I.attr_val_17;
576 pHzpRec.attribute18 := I.attr_val_18;
577 pHzpRec.attribute19 := I.attr_val_19;
578 pHzpRec.attribute20 := I.attr_val_20;
579 pHzpRec.attribute21 := I.attr_val_21;
580 pHzpRec.attribute22 := I.attr_val_22;
581 pHzpRec.attribute23 := I.attr_val_23;
582 pHzpRec.attribute24 := I.attr_val_24;
583 -- pHzpRec.global_attribute_category:= I.gattr_val_category;
584 -- pHzpRec.global_attribute1 := I.gattr_val_1;
585 -- pHzpRec.global_attribute2 := I.gattr_val_2;
586 -- pHzpRec.global_attribute3 := I.gattr_val_3;
587 -- pHzpRec.global_attribute4 := I.gattr_val_4;
588 -- pHzpRec.global_attribute5 := I.gattr_val_5;
589 -- pHzpRec.global_attribute6 := I.gattr_val_6;
590 -- pHzpRec.global_attribute7 := I.gattr_val_7;
591 -- pHzpRec.global_attribute8 := I.gattr_val_8;
592 -- pHzpRec.global_attribute9 := I.gattr_val_9;
593 -- pHzpRec.global_attribute10 := I.gattr_val_10;
594 -- pHzpRec.global_attribute11 := I.gattr_val_11;
595 -- pHzpRec.global_attribute12 := I.gattr_val_12;
596 -- pHzpRec.global_attribute13 := I.gattr_val_13;
597 -- pHzpRec.global_attribute14 := I.gattr_val_14;
598 -- pHzpRec.global_attribute15 := I.gattr_val_15;
599 -- pHzpRec.global_attribute16 := I.gattr_val_16;
600 -- pHzpRec.global_attribute17 := I.gattr_val_17;
601 -- pHzpRec.global_attribute18 := I.gattr_val_18;
602 -- pHzpRec.global_attribute19 := I.gattr_val_19;
603 -- pHzpRec.global_attribute20 := I.gattr_val_20;
604 end if;
605 if (pEntity = 'HZ_LOCATIONS') then
606 pHzlRec.attribute_category := I.attr_val_category;
607 pHzlRec.attribute1 := I.attr_val_1;
608 pHzlRec.attribute2 := I.attr_val_2;
609 pHzlRec.attribute3 := I.attr_val_3;
610 pHzlRec.attribute4 := I.attr_val_4;
611 pHzlRec.attribute5 := I.attr_val_5;
612 pHzlRec.attribute6 := I.attr_val_6;
613 pHzlRec.attribute7 := I.attr_val_7;
614 pHzlRec.attribute8 := I.attr_val_8;
615 pHzlRec.attribute9 := I.attr_val_9;
616 pHzlRec.attribute10 := I.attr_val_10;
617 pHzlRec.attribute11 := I.attr_val_11;
618 pHzlRec.attribute12 := I.attr_val_12;
619 pHzlRec.attribute13 := I.attr_val_13;
620 pHzlRec.attribute14 := I.attr_val_14;
621 pHzlRec.attribute15 := I.attr_val_15;
622 pHzlRec.attribute16 := I.attr_val_16;
623 pHzlRec.attribute17 := I.attr_val_17;
624 pHzlRec.attribute18 := I.attr_val_18;
625 pHzlRec.attribute19 := I.attr_val_19;
626 pHzlRec.attribute20 := I.attr_val_20;
627 -- pHzlRec.global_attribute_category:= I.gattr_val_category;
628 -- pHzlRec.global_attribute1 := I.gattr_val_1;
629 -- pHzlRec.global_attribute2 := I.gattr_val_2;
630 -- pHzlRec.global_attribute3 := I.gattr_val_3;
631 -- pHzlRec.global_attribute4 := I.gattr_val_4;
632 -- pHzlRec.global_attribute5 := I.gattr_val_5;
633 -- pHzlRec.global_attribute6 := I.gattr_val_6;
634 -- pHzlRec.global_attribute7 := I.gattr_val_7;
635 -- pHzlRec.global_attribute8 := I.gattr_val_8;
636 -- pHzlRec.global_attribute9 := I.gattr_val_9;
637 -- pHzlRec.global_attribute10 := I.gattr_val_10;
638 -- pHzlRec.global_attribute11 := I.gattr_val_11;
639 -- pHzlRec.global_attribute12 := I.gattr_val_12;
640 -- pHzlRec.global_attribute13 := I.gattr_val_13;
641 -- pHzlRec.global_attribute14 := I.gattr_val_14;
642 -- pHzlRec.global_attribute15 := I.gattr_val_15;
643 -- pHzlRec.global_attribute16 := I.gattr_val_16;
644 -- pHzlRec.global_attribute17 := I.gattr_val_17;
645 -- pHzlRec.global_attribute18 := I.gattr_val_18;
646 -- pHzlRec.global_attribute19 := I.gattr_val_19;
647 -- pHzlRec.global_attribute20 := I.gattr_val_20;
648 end if;
649 if (pEntity = 'HZ_CONTACT_POINTS') then
650 pHzcpRec.attribute_category := I.attr_val_category;
651 pHzcpRec.attribute1 := I.attr_val_1;
652 pHzcpRec.attribute2 := I.attr_val_2;
653 pHzcpRec.attribute3 := I.attr_val_3;
654 pHzcpRec.attribute4 := I.attr_val_4;
655 pHzcpRec.attribute5 := I.attr_val_5;
656 pHzcpRec.attribute6 := I.attr_val_6;
657 pHzcpRec.attribute7 := I.attr_val_7;
658 pHzcpRec.attribute8 := I.attr_val_8;
659 pHzcpRec.attribute9 := I.attr_val_9;
660 pHzcpRec.attribute10 := I.attr_val_10;
661 pHzcpRec.attribute11 := I.attr_val_11;
662 pHzcpRec.attribute12 := I.attr_val_12;
663 pHzcpRec.attribute13 := I.attr_val_13;
664 pHzcpRec.attribute14 := I.attr_val_14;
665 pHzcpRec.attribute15 := I.attr_val_15;
666 pHzcpRec.attribute16 := I.attr_val_16;
667 pHzcpRec.attribute17 := I.attr_val_17;
668 pHzcpRec.attribute18 := I.attr_val_18;
669 pHzcpRec.attribute19 := I.attr_val_19;
670 pHzcpRec.attribute20 := I.attr_val_20;
671 -- pHzcpRec.global_attribute_category:= I.gattr_val_category;
672 -- pHzcpRec.global_attribute1 := I.gattr_val_1;
673 -- pHzcpRec.global_attribute2 := I.gattr_val_2;
674 -- pHzcpRec.global_attribute3 := I.gattr_val_3;
675 -- pHzcpRec.global_attribute4 := I.gattr_val_4;
676 -- pHzcpRec.global_attribute5 := I.gattr_val_5;
677 -- pHzcpRec.global_attribute6 := I.gattr_val_6;
678 -- pHzcpRec.global_attribute7 := I.gattr_val_7;
679 -- pHzcpRec.global_attribute8 := I.gattr_val_8;
680 -- pHzcpRec.global_attribute9 := I.gattr_val_9;
681 -- pHzcpRec.global_attribute10 := I.gattr_val_10;
682 -- pHzcpRec.global_attribute11 := I.gattr_val_11;
683 -- pHzcpRec.global_attribute12 := I.gattr_val_12;
684 -- pHzcpRec.global_attribute13 := I.gattr_val_13;
685 -- pHzcpRec.global_attribute14 := I.gattr_val_14;
686 -- pHzcpRec.global_attribute15 := I.gattr_val_15;
687 -- pHzcpRec.global_attribute16 := I.gattr_val_16;
688 -- pHzcpRec.global_attribute17 := I.gattr_val_17;
689 -- pHzcpRec.global_attribute18 := I.gattr_val_18;
690 -- pHzcpRec.global_attribute19 := I.gattr_val_19;
691 -- pHzcpRec.global_attribute20 := I.gattr_val_20;
692 end if;
693 if (pEntity = 'HZ_PARTY_SITES') then
694 pHzpsRec.attribute_category := I.attr_val_category;
695 pHzpsRec.attribute1 := I.attr_val_1;
696 pHzpsRec.attribute2 := I.attr_val_2;
697 pHzpsRec.attribute3 := I.attr_val_3;
698 pHzpsRec.attribute4 := I.attr_val_4;
699 pHzpsRec.attribute5 := I.attr_val_5;
700 pHzpsRec.attribute6 := I.attr_val_6;
701 pHzpsRec.attribute7 := I.attr_val_7;
702 pHzpsRec.attribute8 := I.attr_val_8;
703 pHzpsRec.attribute9 := I.attr_val_9;
704 pHzpsRec.attribute10 := I.attr_val_10;
705 pHzpsRec.attribute11 := I.attr_val_11;
706 pHzpsRec.attribute12 := I.attr_val_12;
707 pHzpsRec.attribute13 := I.attr_val_13;
708 pHzpsRec.attribute14 := I.attr_val_14;
709 pHzpsRec.attribute15 := I.attr_val_15;
710 pHzpsRec.attribute16 := I.attr_val_16;
711 pHzpsRec.attribute17 := I.attr_val_17;
712 pHzpsRec.attribute18 := I.attr_val_18;
713 pHzpsRec.attribute19 := I.attr_val_19;
714 pHzpsRec.attribute20 := I.attr_val_20;
715 -- pHzpsRec.global_attribute_category:= I.gattr_val_category;
716 -- pHzpsRec.global_attribute1 := I.gattr_val_1;
717 -- pHzpsRec.global_attribute2 := I.gattr_val_2;
718 -- pHzpsRec.global_attribute3 := I.gattr_val_3;
719 -- pHzpsRec.global_attribute4 := I.gattr_val_4;
720 -- pHzpsRec.global_attribute5 := I.gattr_val_5;
721 -- pHzpsRec.global_attribute6 := I.gattr_val_6;
722 -- pHzpsRec.global_attribute7 := I.gattr_val_7;
723 -- pHzpsRec.global_attribute8 := I.gattr_val_8;
724 -- pHzpsRec.global_attribute9 := I.gattr_val_9;
725 -- pHzpsRec.global_attribute10 := I.gattr_val_10;
726 -- pHzpsRec.global_attribute11 := I.gattr_val_11;
727 -- pHzpsRec.global_attribute12 := I.gattr_val_12;
728 -- -- pHzpsRec.global_attribute13 := I.gattr_val_13;
729 -- pHzpsRec.global_attribute14 := I.gattr_val_14;
730 -- pHzpsRec.global_attribute15 := I.gattr_val_15;
731 -- pHzpsRec.global_attribute16 := I.gattr_val_16;
732 -- pHzpsRec.global_attribute17 := I.gattr_val_17;
733 -- pHzpsRec.global_attribute18 := I.gattr_val_18;
734 -- pHzpsRec.global_attribute19 := I.gattr_val_19;
735 -- pHzpsRec.global_attribute20 := I.gattr_val_20;
736 end if;
737 if (pEntity = 'HZ_ORG_CONTACTS') then
738 pHzocRec.attribute_category := I.attr_val_category;
739 pHzocRec.attribute1 := I.attr_val_1;
740 pHzocRec.attribute2 := I.attr_val_2;
741 pHzocRec.attribute3 := I.attr_val_3;
742 pHzocRec.attribute4 := I.attr_val_4;
743 pHzocRec.attribute5 := I.attr_val_5;
744 pHzocRec.attribute6 := I.attr_val_6;
745 pHzocRec.attribute7 := I.attr_val_7;
746 pHzocRec.attribute8 := I.attr_val_8;
747 pHzocRec.attribute9 := I.attr_val_9;
748 pHzocRec.attribute10 := I.attr_val_10;
749 pHzocRec.attribute11 := I.attr_val_11;
750 pHzocRec.attribute12 := I.attr_val_12;
751 pHzocRec.attribute13 := I.attr_val_13;
752 pHzocRec.attribute14 := I.attr_val_14;
753 pHzocRec.attribute15 := I.attr_val_15;
754 pHzocRec.attribute16 := I.attr_val_16;
755 pHzocRec.attribute17 := I.attr_val_17;
756 pHzocRec.attribute18 := I.attr_val_18;
757 pHzocRec.attribute19 := I.attr_val_19;
758 pHzocRec.attribute20 := I.attr_val_20;
759 pHzocRec.attribute21 := I.attr_val_21;
760 pHzocRec.attribute22 := I.attr_val_22;
761 pHzocRec.attribute23 := I.attr_val_23;
762 pHzocRec.attribute24 := I.attr_val_24;
763 -- pHzocRec.global_attribute_category:= I.gattr_val_category;
764 -- pHzocRec.global_attribute1 := I.gattr_val_1;
765 -- pHzocRec.global_attribute2 := I.gattr_val_2;
766 -- pHzocRec.global_attribute3 := I.gattr_val_3;
767 -- pHzocRec.global_attribute4 := I.gattr_val_4;
768 -- pHzocRec.global_attribute5 := I.gattr_val_5;
769 -- pHzocRec.global_attribute6 := I.gattr_val_6;
770 -- pHzocRec.global_attribute7 := I.gattr_val_7;
771 -- pHzocRec.global_attribute8 := I.gattr_val_8;
772 -- pHzocRec.global_attribute9 := I.gattr_val_9;
773 -- pHzocRec.global_attribute10 := I.gattr_val_10;
774 -- pHzocRec.global_attribute11 := I.gattr_val_11;
775 -- pHzocRec.global_attribute12 := I.gattr_val_12;
776 -- pHzocRec.global_attribute13 := I.gattr_val_13;
777 -- pHzocRec.global_attribute14 := I.gattr_val_14;
778 -- pHzocRec.global_attribute15 := I.gattr_val_15;
779 -- pHzocRec.global_attribute16 := I.gattr_val_16;
780 -- pHzocRec.global_attribute17 := I.gattr_val_17;
781 -- pHzocRec.global_attribute18 := I.gattr_val_18;
782 -- pHzocRec.global_attribute19 := I.gattr_val_19;
783 -- pHzocRec.global_attribute20 := I.gattr_val_20;
784 end if;
785 /* *** ffang 082001, HZ_ORG_CONTACT_ROLES' flexfileds are going to be
786 obsolete by TCA
787 if (pEntity = 'HZ_ORG_CONTACT_ROLES') then
788 pHocrRec.attribute_category := I.attr_val_category;
789 pHocrRec.attribute1 := I.attr_val_1;
790 pHocrRec.attribute2 := I.attr_val_2;
791 pHocrRec.attribute3 := I.attr_val_3;
792 pHocrRec.attribute4 := I.attr_val_4;
793 pHocrRec.attribute5 := I.attr_val_5;
794 pHocrRec.attribute6 := I.attr_val_6;
795 pHocrRec.attribute7 := I.attr_val_7;
796 pHocrRec.attribute8 := I.attr_val_8;
797 pHocrRec.attribute9 := I.attr_val_9;
798 pHocrRec.attribute10 := I.attr_val_10;
799 pHocrRec.attribute11 := I.attr_val_11;
800 pHocrRec.attribute12 := I.attr_val_12;
801 pHocrRec.attribute13 := I.attr_val_13;
802 pHocrRec.attribute14 := I.attr_val_14;
803 pHocrRec.attribute15 := I.attr_val_15;
804 end if;
805 *** */
806 if (pEntity = 'AS_SALES_LEADS') then
807 pAsslRec.attribute_category := I.attr_val_category;
808 pAsslRec.attribute1 := I.attr_val_1;
809 pAsslRec.attribute2 := I.attr_val_2;
810 pAsslRec.attribute3 := I.attr_val_3;
811 pAsslRec.attribute4 := I.attr_val_4;
812 pAsslRec.attribute5 := I.attr_val_5;
813 pAsslRec.attribute6 := I.attr_val_6;
814 pAsslRec.attribute7 := I.attr_val_7;
815 pAsslRec.attribute8 := I.attr_val_8;
816 pAsslRec.attribute9 := I.attr_val_9;
817 pAsslRec.attribute10 := I.attr_val_10;
818 pAsslRec.attribute11 := I.attr_val_11;
819 pAsslRec.attribute12 := I.attr_val_12;
820 pAsslRec.attribute13 := I.attr_val_13;
821 pAsslRec.attribute14 := I.attr_val_14;
822 pAsslRec.attribute15 := I.attr_val_15;
823 end if;
824 if (pEntity = 'AS_SALES_LEAD_LINES') then
825 pAssllTbl(l_index).attribute_category := I.attr_val_category;
826 pAssllTbl(l_index).attribute1 := I.attr_val_1;
827 pAssllTbl(l_index).attribute2 := I.attr_val_2;
828 pAssllTbl(l_index).attribute3 := I.attr_val_3;
829 pAssllTbl(l_index).attribute4 := I.attr_val_4;
830 pAssllTbl(l_index).attribute5 := I.attr_val_5;
831 pAssllTbl(l_index).attribute6 := I.attr_val_6;
832 pAssllTbl(l_index).attribute7 := I.attr_val_7;
833 pAssllTbl(l_index).attribute8 := I.attr_val_8;
834 pAssllTbl(l_index).attribute9 := I.attr_val_9;
835 pAssllTbl(l_index).attribute10 := I.attr_val_10;
836 pAssllTbl(l_index).attribute11 := I.attr_val_11;
837 pAssllTbl(l_index).attribute12 := I.attr_val_12;
838 pAssllTbl(l_index).attribute13 := I.attr_val_13;
839 pAssllTbl(l_index).attribute14 := I.attr_val_14;
840 pAssllTbl(l_index).attribute15 := I.attr_val_15;
841 end if;
842 if (pEntity = 'AS_SALES_LEAD_CONTACTS') then
843 pAsslcTbl(l_index).attribute_category := I.attr_val_category;
844 pAsslcTbl(l_index).attribute1 := I.attr_val_1;
845 pAsslcTbl(l_index).attribute2 := I.attr_val_2;
846 pAsslcTbl(l_index).attribute3 := I.attr_val_3;
847 pAsslcTbl(l_index).attribute4 := I.attr_val_4;
848 pAsslcTbl(l_index).attribute5 := I.attr_val_5;
849 pAsslcTbl(l_index).attribute6 := I.attr_val_6;
850 pAsslcTbl(l_index).attribute7 := I.attr_val_7;
851 pAsslcTbl(l_index).attribute8 := I.attr_val_8;
852 pAsslcTbl(l_index).attribute9 := I.attr_val_9;
853 pAsslcTbl(l_index).attribute10 := I.attr_val_10;
854 pAsslcTbl(l_index).attribute11 := I.attr_val_11;
855 pAsslcTbl(l_index).attribute12 := I.attr_val_12;
856 pAsslcTbl(l_index).attribute13 := I.attr_val_13;
857 pAsslcTbl(l_index).attribute14 := I.attr_val_14;
858 pAsslcTbl(l_index).attribute15 := I.attr_val_15;
859 end if;
860
861 l_index := l_index + 1;
862 End Loop;
863 G_return_status := FND_API.G_RET_STS_SUCCESS ;
864
865 Exception
866 when NO_DATA_FOUND then
867 G_return_status := FND_API.G_RET_STS_SUCCESS ;
868 when others then
869 G_return_status := FND_API.G_RET_STS_ERROR;
870 write_log(2, sqlerrm);
871 End do_assign_flex;
872
873 --------------------------------------------------------
874 -- name: do_create_person
875 -- scope: private
876 -- calls the HZ.create_person API.
877 -- used to insert contact or consumer
878 --------------------------------------------------------
879 procedure do_create_person(
880 pI IN OUT NOCOPY leadImpType,
881 pType IN varchar2,
882 G_return_status OUT NOCOPY varchar2)
883 IS
884 l_per_rec HZ_PARTY_V2PUB.person_rec_type;
885 l_profile number;
886 l_partyNumber number;
887 l_msg_data VARCHAR2(2000);
888
889 -- aanjaria enh tcav2
890 -- Dummy
891 --l_dummy_rec1 hz_party_v2pub.party_rec_type;
892 l_dummy_rec2 hz_location_v2pub.location_rec_type;
893 l_dummy_rec3 hz_contact_point_v2pub.contact_point_rec_type;
894 l_dummy_rec4 hz_party_site_v2pub.party_site_rec_type;
895 l_dummy_rec5 hz_party_contact_v2pub.org_contact_rec_type;
896 l_dummy_rec7 as_sales_leads_pub.sales_lead_rec_type;
897 l_dummy_tbl8 as_sales_leads_pub.sales_lead_line_tbl_type;
898 l_dummy_tbl9 as_sales_leads_pub.sales_lead_contact_tbl_type;
899
900 Begin
901 -- Assigning HZ_PARTY_V2PUB.person_rec_type
902 -- Srikanth March 13th 2001: Per Mrinal, OSO and OTS UI
903 -- The current association is
904 -- person_title -- person_pre_name_adjunct
905 -- second title -- person_academic_title
906 -- Though the above assignments is wrong, it is beging followed as to
907 -- be consistent with OSO and OTS. The above associations must be changed
908 -- to the following at a later date along with data migration script.
909 -- l_per_rec.pre_name_adjunct := substr(pI.salutation,1,30);
910 -- l_per_rec.title := pI.title;
911
912 l_per_rec.known_as := pI.known_as;
913 l_per_rec.known_as2 := pI.known_as2;
914 l_per_rec.known_as3 := pI.known_as3;
915 l_per_rec.known_as4 := pI.known_as4;
916 l_per_rec.known_as5 := pI.known_as5;
917 -- l_per_rec.tax_name := pI.tax_name;
918 -- l_per_rec.middle_name_phonetic :=
919 l_per_rec.jgzz_fiscal_code := pI.jgzz_fiscal_code;
920 l_per_rec.person_iden_type := pI.person_iden_type;
921 l_per_rec.person_identifier := pI.person_identifier;
922 l_per_rec.gender:= pI.sex_code; --! hz code does not store this in HP table!
923 l_per_rec.party_rec.party_number := pI.party_number;
924 l_per_rec.party_rec.validated_flag := pI.parties_validated_flag;
925 -- orig_system_reference
926 if (pI.orig_system_reference is not null) or
927 (pI.orig_system_reference <> FND_API.G_MISS_CHAR) Then
928 l_per_rec.party_rec.orig_system_reference := pI.orig_system_reference;
929 else
930 l_per_rec.party_rec.orig_system_reference := pI.import_interface_id;
931 end if;
932 l_per_rec.party_rec.status := 'A';
933 l_per_rec.party_rec.category_code := pI.customer_category_code;
934 l_per_rec.party_rec.salutation := pI.salutation;
935
936 -- SOLIN, bug 4602573
937 IF (pType = 'PERSON') THEN
938 l_per_rec.person_pre_name_adjunct := substr(pI.title,1,30); --bmuthukr for bug 3737765 added substr to take the first 30 chars
939 ELSIF (pType = 'CONTACT') THEN
940 l_per_rec.person_pre_name_adjunct := substr(pI.org_cnt_title,1,30);
941 END IF;
942 -- SOLIN, end
943
944 l_per_rec.person_first_name := pI.first_name;
945 l_per_rec.person_middle_name:= pI.middle_initial;
946 l_per_rec.person_last_name := pI.last_name;
947 l_per_rec.person_name_suffix := pI.person_name_suffix;
948 l_per_rec.person_title := pI.salutation;
949 l_per_rec.person_academic_title := pI.salutation;
950 l_per_rec.person_previous_last_name := pI.person_previous_last_name;
951 l_per_rec.person_first_name_phonetic := pI.person_first_name_phonetic;
952 l_per_rec.person_last_name_phonetic := pI.person_last_name_phonetic;
953 l_per_rec.person_initials := pI.person_initials; --added for enh# 2221805 aanjaria
954 l_per_rec.created_by_module := 'AML_LEAD_IMPORT';
955 l_per_rec.application_id := 530;
956
957 do_assign_flex (
958 l_per_rec.party_rec,
959 l_dummy_rec2,
960 l_dummy_rec3,
961 l_dummy_rec4,
962 l_dummy_rec5 ,
963 -- l_dummy_rec6 ,
964 l_dummy_rec7 ,
965 l_dummy_tbl8,
966 l_dummy_tbl9,
967 'HZ_PARTIES',
968 pI.import_interface_id,
969 G_return_status
970 );
971 if G_return_status = FND_API.G_RET_STS_SUCCESS Then
972 If (pType = 'PERSON') then
973 -- l_per_rec.party_rec.customer_key := pI.customer_key;
974 HZ_PARTY_V2PUB.create_person (
975 p_init_msg_list => FND_API.G_FALSE,
976 p_person_rec => l_per_rec,
977 x_return_status => G_return_status,
978 x_msg_count => G_MESG_COUNT,
979 x_msg_data => l_msg_data,
980 x_party_id => pI.party_id,
981 x_party_number => l_partyNumber,
982 x_profile_id => l_profile
983 );
984 Elsif (pType = 'CONTACT') then
985 -- l_per_rec.party_rec.customer_key := pI.contact_key;
986 HZ_PARTY_V2PUB.create_person (
987 p_init_msg_list => FND_API.G_FALSE,
988 p_person_rec => l_per_rec,
989 x_return_status => G_return_status,
990 x_msg_count => G_MESG_COUNT,
991 x_msg_data => l_msg_data,
992 x_party_id => pI.contact_party_id,
993 x_party_number => l_partyNumber,
994 x_profile_id => l_profile
995 );
996 Else
997 G_return_status := FND_API.G_RET_STS_ERROR;
998 End if;
999 End If;
1000
1001 -- If error raise exception
1002 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1003 write_log(3, 'Creating Person failed');
1004 RAISE FND_API.G_EXC_ERROR;
1005 ELSE
1006 If (pType = 'PERSON') then
1007 write_log (3, 'Person created: '||pI.party_id);
1008 Elsif (pType = 'CONTACT') then
1009 write_log (3, 'Contact created: '||pI.contact_party_id);
1010 END IF;
1011 END IF;
1012
1013 End do_create_person;
1014
1015 --------------------------------------------------------
1016 -- name: do_contact_preference
1017 -- scope: private
1018 -- calls HZ_CONTACT_POINT_V2PUB.create_contact_preference
1019 -- inserts contact preference for pary and party site
1020 ----------------------------------------------------------
1021 procedure do_contact_preference(
1022 pI IN OUT NOCOPY leadImpType,
1023 G_return_status OUT NOCOPY varchar2)
1024 IS
1025 l_res_rec HZ_CONTACT_PREFERENCE_V2PUB.contact_preference_rec_type;
1026 l_res_id number;
1027 l_msg_data VARCHAR2(2000);
1028 Begin
1029
1030 l_res_rec.created_by_module := 'AML_LEAD_IMPORT';
1031 l_res_rec.application_id := 530;
1032 l_res_rec.requested_by := 'PARTY';
1033 l_res_rec.preference_code := 'DO_NOT';
1034
1035 IF ((pI.addr_do_not_mail_flag is not Null)
1036 and (upper(pI.addr_do_not_mail_flag) = 'Y'))
1037 THEN
1038 Begin
1039 l_res_id := NULL;
1040 select contact_preference_id
1041 into l_res_id
1042 from hz_contact_preferences
1043 where contact_level_table_id = pI.party_site_id
1044 and contact_type = 'MAIL'
1045 and contact_level_table = 'HZ_PARTY_SITES';
1046
1047 Exception
1048 when NO_DATA_FOUND then
1049 l_res_rec.contact_type := 'MAIL';
1050 l_res_rec.preference_start_date := sysdate;
1051 l_res_rec.contact_level_table := 'HZ_PARTY_SITES';
1052 l_res_rec.contact_level_table_id:= pI.party_site_id;
1053
1054 write_log (3, 'Creating CntPreference: AMAIL');
1055 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference (
1056 p_init_msg_list => FND_API.G_FALSE,
1057 p_contact_preference_rec => l_res_rec,
1058 x_return_status => G_return_status,
1059 x_msg_count => G_MESG_COUNT,
1060 x_msg_data => l_msg_data,
1061 x_contact_preference_id => l_res_id
1062 );
1063 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1064 write_log(3, 'Create CntPreference failed');
1065 ELSE
1066 write_log(3, 'CntPreference created: ' || l_res_id);
1067 END IF;
1068 when others then
1069 write_log(3, 'Select on Contact Preference Failed');
1070 RAISE FND_API.G_EXC_ERROR;
1071 End;
1072 End if;
1073
1074 IF (pI.cont_do_not_mail_flag is not Null)
1075 and (upper(pI.cont_do_not_mail_flag) = 'Y')
1076 THEN
1077 Begin
1078 l_res_id := NULL;
1079 select contact_preference_id
1080 into l_res_id
1081 from hz_contact_preferences
1082 where contact_level_table_id = pI.rel_party_id
1083 and contact_type = 'MAIL'
1084 and contact_level_table = 'HZ_PARTIES';
1085
1086 Exception
1087 when NO_DATA_FOUND then
1088 l_res_rec.contact_type := 'MAIL';
1089 l_res_rec.preference_start_date := sysdate;
1090 l_res_rec.contact_level_table := 'HZ_PARTIES';
1091 -- l_res_rec.subject_id:= pI.contact_party_id;
1092 l_res_rec.contact_level_table_id:= pI.rel_party_id;
1093
1094 write_log (3, 'Creating CntPreference: CMAIL');
1095 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference (
1096 p_init_msg_list => FND_API.G_FALSE,
1097 p_contact_preference_rec => l_res_rec,
1098 x_return_status => G_return_status,
1099 x_msg_count => G_MESG_COUNT,
1100 x_msg_data => l_msg_data,
1101 x_contact_preference_id => l_res_id
1102 );
1103 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1104 write_log(3, 'Create CntPreference failed');
1105 ELSE
1106 write_log(3, 'CntPreference created: ' || l_res_id);
1107 END IF;
1108 when others then
1109 RAISE FND_API.G_EXC_ERROR;
1110 End;
1111 End if;
1112
1113 If (pI.do_not_phone_flag is not Null) and
1114 (upper(pI.do_not_phone_flag) = 'Y') and
1115 (pI.phone_number is not null)
1116 then
1117 Begin
1118 l_res_id := NULL;
1119 select contact_preference_id
1120 into l_res_id
1121 from hz_contact_preferences
1122 where contact_level_table_id = pI.rel_party_id
1123 and contact_type = 'CALL'
1124 and contact_level_table = 'HZ_PARTIES';
1125
1126 Exception
1127 when NO_DATA_FOUND then
1128 l_res_rec.contact_type := 'CALL';
1129 l_res_rec.preference_start_date := sysdate;
1130 l_res_rec.contact_level_table := 'HZ_PARTIES';
1131 -- l_res_rec.subject_id:= pI.contact_party_id;
1132 l_res_rec.contact_level_table_id:= pI.rel_party_id;
1133
1134 write_log (3, 'Creating CntPreference: PHONE');
1135 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference (
1136 p_init_msg_list => FND_API.G_FALSE,
1137 p_contact_preference_rec => l_res_rec,
1138 x_return_status => G_return_status,
1139 x_msg_count => G_MESG_COUNT,
1140 x_msg_data => l_msg_data,
1141 x_contact_preference_id => l_res_id
1142 );
1143 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1144 write_log(3, 'Create CntPreference failed');
1145 ELSE
1146 write_log(3, 'CntPreference created: ' || l_res_id);
1147 END IF;
1148 when others then
1149 RAISE FND_API.G_EXC_ERROR;
1150 End;
1151 -- SOLIN, bug 4637420
1152 Begin
1153 l_res_id := NULL;
1154 select contact_preference_id
1155 into l_res_id
1156 from hz_contact_preferences
1157 where contact_level_table_id = pI.phone_id
1158 and contact_type = 'CALL'
1159 and contact_level_table = 'HZ_CONTACT_POINTS';
1160
1161 Exception
1162 when NO_DATA_FOUND then
1163 l_res_rec.contact_type := 'CALL';
1164 l_res_rec.preference_start_date := sysdate;
1165 l_res_rec.contact_level_table := 'HZ_CONTACT_POINTS';
1166 -- l_res_rec.subject_id:= pI.contact_party_id;
1167 l_res_rec.contact_level_table_id:= pI.phone_id;
1168
1169 write_log (3, 'Creating CntPreference: PHONE');
1170 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference (
1171 p_init_msg_list => FND_API.G_FALSE,
1172 p_contact_preference_rec => l_res_rec,
1173 x_return_status => G_return_status,
1174 x_msg_count => G_MESG_COUNT,
1175 x_msg_data => l_msg_data,
1176 x_contact_preference_id => l_res_id
1177 );
1178 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1179 write_log(3, 'Create CntPreference failed');
1180 ELSE
1181 write_log(3, 'CntPreference created: ' || l_res_id);
1182 END IF;
1183 when others then
1184 RAISE FND_API.G_EXC_ERROR;
1185 End;
1186 -- SOLIN, end bug 4637420
1187 End if;
1188
1189 If (pI.do_not_fax_flag is not Null) and
1190 (upper(pI.do_not_fax_flag) = 'Y') then
1191 Begin
1192 l_res_id := NULL;
1193 select contact_preference_id
1194 into l_res_id
1195 from hz_contact_preferences
1196 where contact_level_table_id = pI.rel_party_id
1197 and contact_type = 'FAX'
1198 and contact_level_table = 'HZ_PARTIES';
1199
1200 Exception
1201 when NO_DATA_FOUND then
1202 l_res_rec.contact_type := 'FAX';
1203 l_res_rec.preference_start_date := sysdate;
1204 l_res_rec.contact_level_table := 'HZ_PARTIES';
1205 -- l_res_rec.subject_id:= pI.contact_party_id;
1206 l_res_rec.contact_level_table_id:= pI.rel_party_id;
1207
1208 write_log (3, 'Creating CntPreference: FAX');
1209 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference (
1210 p_init_msg_list => FND_API.G_FALSE,
1211 p_contact_preference_rec => l_res_rec,
1212 x_return_status => G_return_status,
1213 x_msg_count => G_MESG_COUNT,
1214 x_msg_data => l_msg_data,
1215 x_contact_preference_id => l_res_id
1216 );
1217 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1218 write_log(3, 'Create CntPreference failed');
1219 ELSE
1220 write_log(3, 'CntPreference created: ' || l_res_id);
1221 END IF;
1222 when others then
1223 RAISE FND_API.G_EXC_ERROR;
1224 End;
1225 End if;
1226
1227 If (pI.do_not_email_flag is not Null) and
1228 (upper(pI.do_not_email_flag) = 'Y') then
1229 Begin
1230 l_res_id := NULL;
1231 select contact_preference_id
1232 into l_res_id
1233 from hz_contact_preferences
1234 where contact_level_table_id = pI.rel_party_id
1235 and contact_type = 'EMAIL'
1236 and contact_level_table = 'HZ_PARTIES';
1237
1238 Exception
1239 when NO_DATA_FOUND then
1240 l_res_rec.contact_type := 'EMAIL';
1241 l_res_rec.preference_start_date := sysdate;
1242 l_res_rec.contact_level_table := 'HZ_PARTIES';
1243 -- l_res_rec.subject_id:= pI.contact_party_id;
1244 l_res_rec.contact_level_table_id:= pI.rel_party_id;
1245
1246 write_log (3, 'Creating CntPreference: EMAIL');
1247 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference (
1248 p_init_msg_list => FND_API.G_FALSE,
1249 p_contact_preference_rec => l_res_rec,
1250 x_return_status => G_return_status,
1251 x_msg_count => G_MESG_COUNT,
1252 x_msg_data => l_msg_data,
1253 x_contact_preference_id => l_res_id
1254 );
1255 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1256 write_log(3, 'Create CntPreference failed');
1257 ELSE
1258 write_log(3, 'CntPreference created: ' || l_res_id);
1259 END IF;
1260 when others then
1261 RAISE FND_API.G_EXC_ERROR;
1262 End;
1263 End if;
1264 End do_contact_preference;
1265
1266
1267 --------------------------------------------------------
1268 -- name: do_create_contact_points_old
1269 -- scope: private
1270 -- calls HZ_CONTACT_POINT_V2PUB.do_create_contact_points
1271 -- inserts contact point (in as_import_interface) for pary and party site
1272 ----------------------------------------------------------
1273 procedure do_create_contact_points_old(
1274 pI IN OUT NOCOPY leadImpType,
1275 l_dup_phone IN VARCHAR2,
1276 l_dup_fax IN VARCHAR2,
1277 l_dup_email IN VARCHAR2,
1278 l_dup_url IN VARCHAR2,
1279 G_return_status OUT NOCOPY varchar2)
1280 IS
1281 l_cp_rec hz_contact_point_v2pub.contact_point_rec_type;
1282 l_email_rec hz_contact_point_v2pub.email_rec_type;
1283 l_ph_rec hz_contact_point_v2pub.phone_rec_type;
1284 l_web_rec hz_contact_point_v2pub.web_rec_type ;
1285 l_msg_data VARCHAR2(2000);
1286 l_cpid number;
1287
1288 -- Dummy
1289 l_dummy_rec1 hz_party_v2pub.party_rec_type;
1290 l_dummy_rec2 hz_location_v2pub.location_rec_type;
1291 l_dummy_rec3 hz_contact_point_v2pub.contact_point_rec_type;
1292 l_dummy_rec4 hz_party_site_v2pub.party_site_rec_type;
1293 l_dummy_rec5 hz_party_contact_v2pub.org_contact_rec_type;
1294 --l_dummy_rec6 hz_party_pub.org_contact_role_rec_type;
1295 l_dummy_rec7 as_sales_leads_pub.sales_lead_rec_type;
1296 l_dummy_tbl8 as_sales_leads_pub.sales_lead_line_tbl_type;
1297 l_dummy_tbl9 as_sales_leads_pub.sales_lead_contact_tbl_type;
1298
1299 Begin
1300 -- swkhanna 8/13 - check for duplicate contact_points
1301 -- cont_pnt_dedupe(pI,l_dup_phone,l_dup_email, l_dup_fax, l_dup_url ) ;
1302 --dbms_output.put_line('l_dup_phone:'||l_dup_phone);
1303 --dbms_output.put_line('l_dup_email:'||l_dup_email);
1304 --dbms_output.put_line('l_dup_fax:'||l_dup_fax);
1305 --dbms_output.put_line('l_dup_url:'||l_dup_url);
1306
1307 l_cp_rec.created_by_module := 'AML_LEAD_IMPORT';
1308 l_cp_rec.application_id := 530;
1309
1310 l_cp_rec.status := 'A';
1311 l_cp_rec.owner_table_name := 'HZ_PARTIES';
1312 If(pI.party_type = 'ORGANIZATION') Then
1313 l_cp_rec.owner_table_id := pI.rel_party_id;
1314 else
1315 l_cp_rec.owner_table_id := pI.party_id;
1316 END IF;
1317 -- swkhanna 5/20/02 commented for Bug 2381261
1318 --l_cp_rec.primary_flag := 'Y';
1319 l_cp_rec.orig_system_reference := pI.import_interface_id;
1320 IF (pI.cnt_pnt_content_source_type is not null and
1321 pI.cnt_pnt_content_source_type <> FND_API.G_MISS_CHAR)
1322 THEN
1323 l_cp_rec.content_source_type := pI.cnt_pnt_content_source_type;
1324 ELSE
1325 l_cp_rec.content_source_type := 'USER_ENTERED';
1326 END IF;
1327
1328 do_assign_flex (
1329 l_dummy_rec1,
1330 l_dummy_rec2,
1331 l_cp_rec,
1332 l_dummy_rec4,
1333 l_dummy_rec5 ,
1334 -- l_dummy_rec6 ,
1335 l_dummy_rec7 ,
1336 l_dummy_tbl8 ,
1337 l_dummy_tbl9 ,
1338 'HZ_CONTACT_POINTS',
1339 pI.import_interface_id,
1340 G_return_status
1341 );
1342
1343 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
1344 If l_dup_phone = 'N' then
1345 If (pI.phone_number is not null) then
1346 l_cp_rec.contact_point_type := 'PHONE';
1347 -- swkhanna 5/20 for bug 2381261
1348 IF pI.phone_type = 'GEN' Then
1349 l_cp_rec.primary_flag := 'Y';
1350 END IF;
1351 l_ph_rec.phone_country_code := pI.phone_country_code;
1352 l_ph_rec.phone_area_code := pI.area_code;
1353 l_ph_rec.phone_number := pI.phone_number;
1354 l_ph_rec.phone_extension := pI.extension;
1355 l_ph_rec.phone_line_type := pI.phone_type;
1356 l_ph_rec.phone_calling_calendar := pI.phone_calling_calendar;
1357 -- l_ph_rec.time_zone := pI.cnt_pnt_time_zone;
1358 l_ph_rec.raw_phone_number := pI.raw_phone_number;
1359
1360 write_log(3, 'Inserting the phone rec');
1361
1362 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1363 p_init_msg_list => FND_API.G_FALSE,
1364 p_contact_point_rec => l_cp_rec,
1365 p_phone_rec => l_ph_rec,
1366 x_return_status => G_return_status,
1367 x_msg_count => G_MESG_COUNT,
1368 x_msg_data => l_msg_data,
1369 x_contact_point_id => l_cpid
1370 );
1371
1372 -- ffang 062001, put contact_point_id into phone_id
1373 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1374 pI.phone_id := l_cpid;
1375 write_log(3, 'Contact Point created-PHONE: ' || l_cpid);
1376 ELSE
1377 write_log(3, 'Contact Point creation faild for PHONE');
1378 return;
1379 END IF;
1380 END IF;
1381 ELSE
1382 write_log(3, 'Duplicate PHONE');
1383 END IF;
1384
1385
1386 IF l_dup_fax = 'N' then
1387 If (pI.fax_number is not null) then
1388 -- swkhanna 5/20 for bug 2381261
1389 l_cp_rec.primary_flag := 'N';
1390 l_cp_rec.contact_point_type := 'PHONE';
1391 l_ph_rec.phone_line_type := 'FAX';
1392 l_ph_rec.phone_country_code := pI.fax_country_code;
1393 l_ph_rec.phone_area_code := pI.fax_area_code;
1394 l_ph_rec.phone_number := pI.fax_number;
1395 l_ph_rec.phone_extension := pI.fax_extension;
1396 l_ph_rec.phone_calling_calendar := pI.phone_calling_calendar;
1397 -- l_ph_rec.time_zone := pI.time_zone;
1398 l_ph_rec.raw_phone_number := NULL;
1399
1400 write_log(3, 'Inserting the fax rec');
1401 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1402 p_init_msg_list => FND_API.G_FALSE,
1403 p_contact_point_rec => l_cp_rec,
1404 p_phone_rec => l_ph_rec,
1405 x_return_status => G_return_status,
1406 x_msg_count => G_MESG_COUNT,
1407 x_msg_data => l_msg_data,
1408 x_contact_point_id => l_cpid
1409 );
1410 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1411 write_log(3, 'Contact Point created-FAX: ' || l_cpid);
1412 ELSE
1413 write_log(3, 'Contact Point creation faild for FAX');
1414 return;
1415 END IF;
1416 END IF;
1417 ELSE
1418 write_log(3, 'Duplicate FAX');
1419 END IF;
1420
1421 If l_dup_email = 'N' then
1422 If (pI.email_address is not null) then
1423 -- swkhanna 8/14/02
1424 l_cp_rec.primary_flag := 'Y';
1425 l_cp_rec.contact_point_type := 'EMAIL';
1426 l_email_rec.email_format := pI.email_format;
1427 l_email_rec.email_address := pI.email_address;
1428 write_log(3, 'Inserting an email rec');
1429
1430 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1431 p_init_msg_list => FND_API.G_FALSE,
1432 p_contact_point_rec => l_cp_rec,
1433 p_email_rec => l_email_rec,
1434 x_return_status => G_return_status,
1435 x_msg_count => G_MESG_COUNT,
1436 x_msg_data => l_msg_data,
1437 x_contact_point_id => l_cpid
1438 );
1439 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1440 write_log(3, 'Contact Point created-EMAIL: ' || l_cpid);
1441 ELSE
1442 write_log(3, 'Contact Point creation faild for EMAIL');
1443 return;
1444 END IF;
1445 End If;
1446 ELSE
1447 write_log(3, 'Duplicate EMAIL');
1448 End If;
1449
1450 If l_dup_url = 'N' then
1451 If (pI.url is not null) then
1452 -- swkhanna 8/14/02
1453 l_cp_rec.primary_flag := 'Y';
1454 l_cp_rec.contact_point_type := 'WEB';
1455 l_web_rec.web_type := 'http';
1456 l_web_rec.url := pI.url;
1457 write_log(3, 'Inserting an url rec');
1458
1459 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1460 p_init_msg_list => FND_API.G_FALSE,
1461 p_contact_point_rec => l_cp_rec,
1462 p_web_rec => l_web_rec,
1463 x_return_status => G_return_status,
1464 x_msg_count => G_MESG_COUNT,
1465 x_msg_data => l_msg_data,
1466 x_contact_point_id => l_cpid
1467 );
1468 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1469 write_log(3, 'Contact Point created-URL: ' || l_cpid);
1470 ELSE
1471 write_log(3, 'Contact Point creation faild for WEB');
1472 return;
1473 END IF;
1474 End If;
1475 ELSE
1476 write_log(3, 'Duplicate URL');
1477 End If;
1478 End If;
1479 End do_create_contact_points_old;
1480
1481
1482 --------------------------------------------------------
1483 -- name: do_create_contact_points
1484 -- scope: private
1485 -- calls HZ_CONTACT_POINT_V2PUB.do_create_contact_points
1486 -- inserts contact point (in as_imp_cnt_pnt_interface) for pary and party site
1487 ----------------------------------------------------------
1488 procedure do_create_contact_points(
1489 pI IN OUT NOCOPY leadImpType,
1490 pCP IN OUT NOCOPY cntPntType,
1491 owner_type IN varchar2,
1492 G_return_status OUT NOCOPY varchar2)
1493 IS
1494 l_cp_rec hz_contact_point_v2pub.contact_point_rec_type;
1495 l_email_rec hz_contact_point_v2pub.email_rec_type;
1496 l_ph_rec hz_contact_point_v2pub.phone_rec_type;
1497 l_web_rec hz_contact_point_v2pub.web_rec_type ;
1498 l_msg_data VARCHAR2(2000);
1499 l_cpid number;
1500
1501 l_dup_phone varchar2(1):= 'N';
1502 l_dup_email varchar2(1):= 'N';
1503 l_dup_fax varchar2(1):= 'N';
1504 l_dup_url varchar2(1):= 'N';
1505 Begin
1506
1507
1508 -- swkhanna 8/13 - check for duplicate contact_points
1509 cont_pnt_dedupe(pI,l_dup_phone,l_dup_email, l_dup_fax, l_dup_url ) ;
1510
1511 --dbms_output.put_line('l_dup_phone:'||l_dup_phone);
1512 --dbms_output.put_line('l_dup_email:'||l_dup_email);
1513 --dbms_output.put_line('l_dup_fax:'||l_dup_fax);
1514 --dbms_output.put_line('l_dup_url:'||l_dup_url);
1515
1516 -- fill up contact_point_rec_type
1517
1518 l_cp_rec.created_by_module := 'AML_LEAD_IMPORT';
1519 l_cp_rec.application_id := 530;
1520
1521 l_cp_rec.status := 'A';
1522 -- ffang 082101, is it OK to use interface table's owner_table_name and
1523 -- owner_table_id?
1524 IF (pCP.owner_table_name is not null and
1525 pCP.owner_table_name <> FND_API.G_MISS_CHAR)
1526 THEN
1527 l_cp_rec.owner_table_name := pCP.owner_table_name;
1528 ELSE
1529 l_cp_rec.owner_table_name := 'HZ_PARTIES';
1530 END IF;
1531 IF (pCP.owner_table_id is not null and
1532 pCP.owner_table_id <> FND_API.G_MISS_NUM)
1533 THEN
1534 l_cp_rec.owner_table_id := pCP.owner_table_id;
1535 ELSE
1536 If(pI.party_type = 'ORGANIZATION') Then
1537 l_cp_rec.owner_table_id := pI.rel_party_id;
1538 else
1539 l_cp_rec.owner_table_id := pI.party_id;
1540 End if;
1541 END IF;
1542 IF (pCP.primary_flag is not null AND
1543 pCP.primary_flag <> FND_API.G_MISS_CHAR) THEN
1544 l_cp_rec.primary_flag := pCP.primary_flag;
1545 ELSE
1546 l_cp_rec.primary_flag := 'N';
1547 END IF;
1548 IF (pCP.orig_system_reference is not NULL AND
1549 pCP.orig_system_reference <> FND_API.G_MISS_CHAR) THEN
1550 l_cp_rec.orig_system_reference := pCP.orig_system_reference;
1551 ELSE
1552 l_cp_rec.orig_system_reference := pI.import_interface_id;
1553 END IF;
1554 IF (pCP.content_source_type is not null and
1555 pCP.content_source_type <> FND_API.G_MISS_CHAR)
1556 THEN
1557 l_cp_rec.content_source_type := pCP.content_source_type;
1558 ELSE
1559 l_cp_rec.content_source_type := 'USER_ENTERED';
1560 END IF;
1561 l_cp_rec.contact_point_type:= pCP.contact_point_type; --'PHONE';
1562
1563 -- ffang 091301, since do_create_contact_points_old has done do_assign_flex,
1564 -- we don't need this here
1565 /*
1566 do_assign_flex (
1567 l_dummy_rec1,
1568 l_dummy_rec2,
1569 l_cp_rec,
1570 l_dummy_rec4,
1571 l_dummy_rec5 ,
1572 -- l_dummy_rec6 ,
1573 l_dummy_rec7 ,
1574 l_dummy_tbl8 ,
1575 l_dummy_tbl9 ,
1576 'HZ_CONTACT_POINTS',
1577 pI.import_interface_id,
1578 G_return_status
1579 );
1580 */
1581
1582 write_log (3, pCP.contact_point_type ||'-'||pCP.phone_line_type||':'
1583 ||G_return_status);
1584 -- ffang 082101, should use contact_point_type and phone_line_type to
1585 -- determine what's the type.
1586 -- If G_return_status = FND_API.G_RET_STS_SUCCESS Then
1587 -- If (pI.phone_number is not null) then
1588 IF pCP.contact_point_type = 'PHONE' THEN
1589
1590 IF pCP.phone_line_type <> 'FAX' THEN -- 'PHONE'
1591 --swkhanna 8/14/02
1592 IF l_dup_phone = 'N' THEN
1593 -- l_cp_rec.contact_point_type:= 'PHONE';
1594 -- l_cp_rec.status := PI.phone_status;
1595 l_ph_rec.phone_country_code := pCP.phone_country_code;
1596 l_ph_rec.phone_area_code := pCP.phone_area_code; --pI.area_code;
1597 l_ph_rec.phone_number := pCP.phone_number; --pI.phone_number;
1598 l_ph_rec.phone_extension := pCP.phone_extension; --pI.extension;
1599 l_ph_rec.phone_line_type:= pCP.phone_line_type; --pI.phone_type;
1600 l_ph_rec.phone_calling_calendar := pCP.phone_calling_calendar;
1601 -- l_ph_rec.timezone_id := pCP.timezone_id;
1602 -- l_ph_rec.time_zone := pCP.time_zone;
1603 l_ph_rec.raw_phone_number := pCP.raw_phone_number;
1604
1605 write_log(3, 'Inserting the phone rec');
1606 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1607 p_init_msg_list => FND_API.G_FALSE,
1608 p_contact_point_rec => l_cp_rec,
1609 p_phone_rec => l_ph_rec,
1610 x_return_status => G_return_status,
1611 x_msg_count => G_MESG_COUNT,
1612 x_msg_data => l_msg_data,
1613 x_contact_point_id => l_cpid
1614 );
1615 -- ffang 062001, put contact_point_id into phone_id
1616 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1617 pI.phone_id := l_cpid;
1618 write_log(3, 'Contact Point created: ' || l_cpid);
1619 END IF;
1620 END IF; -- if l_dup_phone = N
1621 ELSIF pCP.phone_line_type = 'FAX' THEN -- 'FAX'
1622 --swkhanna 8/14/02
1623 IF l_dup_fax = 'N' THEN
1624 -- If (pI.fax_number is not null) then
1625 -- ffang 071601, bug1810279, for fax number,
1626 -- contact_point_type should be 'PHONE' and
1627 -- phone_line_type should be 'FAX'
1628 -- l_ph_rec.phone_line_type := pI.phone_type;
1629 -- l_cp_rec.contact_point_type := 'FAX';
1630 -- l_cp_rec.contact_point_type:= 'PHONE';
1631 -- l_cp_rec.status := PI.phone_status;
1632 l_ph_rec.phone_country_code := pCP.phone_country_code;
1633 l_ph_rec.phone_area_code := pCP.phone_area_code; --pI.area_code;
1634 l_ph_rec.phone_number := pCP.phone_number; --pI.phone_number;
1635 l_ph_rec.phone_extension := pCP.phone_extension; --pI.extension;
1636 l_ph_rec.phone_line_type:= pCP.phone_line_type; --'FAX';
1637 l_ph_rec.phone_calling_calendar := pCP.phone_calling_calendar;
1638 --l_ph_rec.timezone_id := pCP.timezone_id;
1639 -- l_ph_rec.time_zone := pCP.time_zone;
1640 l_ph_rec.raw_phone_number := pCP.raw_phone_number;
1641
1642 write_log(3, 'Inserting the fax rec');
1643 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1644 p_init_msg_list => FND_API.G_FALSE,
1645 p_contact_point_rec => l_cp_rec,
1646 p_phone_rec => l_ph_rec,
1647 x_return_status => G_return_status,
1648 x_msg_count => G_MESG_COUNT,
1649 x_msg_data => l_msg_data,
1650 x_contact_point_id => l_cpid
1651 );
1652 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1653 write_log(3, 'Contact Point created: ' || l_cpid);
1654 END IF;
1655 End If;
1656 End If;
1657 ELSIF pCP.contact_point_type = 'EMAIL' THEN -- 'EMAIL'
1658 IF l_dup_email = 'N' THEN
1659 -- If (pI.email_address is not null) then
1660 -- l_cp_rec.contact_point_type := 'EMAIL';
1661 l_email_rec.email_format := pCP.email_format;
1662 l_email_rec.email_address := pCP.email_address; --pI.email_address;
1663
1664 write_log(3, 'Inserting an email rec');
1665 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1666 p_init_msg_list => FND_API.G_FALSE,
1667 p_contact_point_rec => l_cp_rec,
1668 p_email_rec => l_email_rec,
1669 x_return_status => G_return_status,
1670 x_msg_count => G_MESG_COUNT,
1671 x_msg_data => l_msg_data,
1672 x_contact_point_id => l_cpid
1673 );
1674 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1675 write_log(3, 'Contact Point created: ' || l_cpid);
1676 END IF;
1677 END IF;
1678 -- End If;
1679 ELSIF pCP.contact_point_type = 'WEB' THEN -- 'WEB'
1680 IF l_dup_url = 'N' THEN
1681 -- If (pI.url is not null) then
1682 -- l_cp_rec.contact_point_type := 'WEB';
1683 l_web_rec.web_type := pCP.web_type; -- 'http';
1684 l_web_rec.url := pCP.url; -- pI.url;
1685
1686 write_log(3, 'Inserting an url rec');
1687 HZ_CONTACT_POINT_V2PUB.create_contact_point (
1688 p_init_msg_list => FND_API.G_FALSE,
1689 p_contact_point_rec => l_cp_rec,
1690 p_web_rec => l_web_rec,
1691 x_return_status => G_return_status,
1692 x_msg_count => G_MESG_COUNT,
1693 x_msg_data => l_msg_data,
1694 x_contact_point_id => l_cpid
1695 );
1696 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1697 write_log(3, 'Contact Point created: ' || l_cpid);
1698 END IF;
1699 -- End If;
1700 End If;
1701 End If;
1702 -- End If;
1703 End do_create_contact_points;
1704
1705 ----------------------------------------------------------
1706 -- name: do_create_location
1707 -- scope: private
1708 -- calls HZ_LOCATION_V2PUB.create_location
1709 -- inserts location details
1710 ----------------------------------------------------------
1711 procedure do_create_location(
1712 pI IN OUT NOCOPY leadImpType,
1713 G_return_status OUT NOCOPY varchar2)
1714 IS
1715 -- aanjaria enh tcav2
1716 l_location_rec hz_location_v2pub.location_rec_type;
1717 l_msg_data VARCHAR2(2000);
1718
1719 -- Dummy
1720 l_dummy_rec1 hz_party_v2pub.party_rec_type;
1721 --l_dummy_rec2 hz_location_pub.location_rec_type;
1722 l_dummy_rec3 hz_contact_point_v2pub.contact_point_rec_type;
1723 l_dummy_rec4 hz_party_site_v2pub.party_site_rec_type;
1724 l_dummy_rec5 hz_party_contact_v2pub.org_contact_rec_type;
1725 l_dummy_rec6 hz_party_contact_v2pub.org_contact_role_rec_type;
1726 l_dummy_rec7 as_sales_leads_pub.sales_lead_rec_type;
1727 l_dummy_tbl8 as_sales_leads_pub.sales_lead_line_tbl_type;
1728 l_dummy_tbl9 as_sales_leads_pub.sales_lead_contact_tbl_type;
1729
1730 Begin
1731
1732 -- Assigning the HZ_LOCATION_PUB.LOCATION_REC_TYPE
1733 l_location_rec.orig_system_reference := pI.import_interface_id;
1734 l_location_rec.country := pI.country;
1735 l_location_rec.address1 := pI.address1;
1736 l_location_rec.address2 := pI.address2;
1737 l_location_rec.address3 := pI.address3;
1738 l_location_rec.address4 := pI.address4;
1739 l_location_rec.city := pI.city;
1740 l_location_rec.postal_code := pI.postal_code;
1741 l_location_rec.state := pI.state;
1742 l_location_rec.province := pI.province;
1743 l_location_rec.county := pI.county;
1744 l_location_rec.address_style := pI.address_style;
1745 l_location_rec.validated_flag := pI.loc_validated_flag;
1746 l_location_rec.address_lines_phonetic := pI.address_lines_phonetic;
1747 -- SOLIN, bug 4602573
1748 --l_location_rec.po_box_number := pI.po_box_number;
1749 --l_location_rec.house_number := pI.house_number;
1750 --l_location_rec.street_suffix := pI.street_suffix;
1751 --l_location_rec.street := pI.street;
1752 --l_location_rec.street_number := pI.street_number;
1753 --l_location_rec.floor := pI.floor;
1754 --l_location_rec.suite := pI.suite;
1755 -- SOLIN, end
1756 l_location_rec.postal_plus4_code := pI.postal_plus4_code;
1757 l_location_rec.position := pI.position;
1758 l_location_rec.address_effective_date := pI.address_effective_date;
1759 l_location_rec.language := pI.language;
1760 l_location_rec.short_description := pI.short_description;
1761 l_location_rec.description := pI.loc_description;
1762 l_location_rec.loc_hierarchy_id := pI.loc_hierarchy_id;
1763 l_location_rec.sales_tax_geocode := pI.sales_tax_geocode;
1764 l_location_rec.sales_tax_inside_city_limits :=
1765 pI.sales_tax_inside_city_limits;
1766 l_location_rec.fa_location_id := pI.fa_location_id;
1767
1768 -- aanjaria enh tcav2
1769 -- l_location_rec.time_zone := pI.time_zone;
1770 -- l_location_rec.address_key := pI.address_key;
1771 l_location_rec.created_by_module := 'AML_LEAD_IMPORT';
1772 l_location_rec.application_id := 530;
1773
1774 IF (pI.content_source_type is not NULL AND
1775 pI.content_source_type <> FND_API.G_MISS_CHAR)
1776 THEN
1777 l_location_rec.content_source_type := pI.content_source_type;
1778 ELSE
1779 l_location_rec.content_source_type := 'USER_ENTERED';
1780 END IF;
1781
1782 do_assign_flex (
1783 l_dummy_rec1,
1784 l_location_rec,
1785 l_dummy_rec3,
1786 l_dummy_rec4,
1787 l_dummy_rec5 ,
1788 -- l_dummy_rec6 ,
1789 l_dummy_rec7 ,
1790 l_dummy_tbl8 ,
1791 l_dummy_tbl9 ,
1792 'HZ_LOCATIONS',
1793 pI.import_interface_id,
1794 G_return_status
1795 );
1796
1797 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
1798 HZ_LOCATION_V2PUB.create_location (
1799 p_init_msg_list => FND_API.G_FALSE,
1800 p_location_rec => l_location_rec,
1801 x_return_status => G_return_status,
1802 x_msg_count => G_MESG_COUNT,
1803 x_msg_data => l_msg_data,
1804 x_location_id => pI.location_id
1805 );
1806 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
1807 write_log(3, 'Create location failed');
1808 ELSE
1809 write_log(3, 'Location created: ' || pI.location_id);
1810 END IF;
1811 End if;
1812 End do_create_location;
1813
1814 ----------------------------------------------------------
1815 -- name: do_create_organization
1816 -- scope: private
1817 -- calls HZ_LOCATION_V2PUB.create_organization
1818 -- inserts party with party type as ORGANIZATION
1819 ----------------------------------------------------------
1820
1821
1822 procedure do_create_organization(
1823 pI IN OUT NOCOPY leadImpType,
1824 G_return_status OUT NOCOPY varchar2)
1825 IS
1826 l_org_rec HZ_PARTY_V2PUB.organization_rec_type;
1827 l_hz_partyNumber number;
1828 l_hz_profile number;
1829 l_msg_data VARCHAR2(2000);
1830
1831 -- aanjaria enh tcav2
1832 -- Dummy
1833 l_dummy_rec2 hz_location_v2pub.location_rec_type;
1834 l_dummy_rec3 hz_contact_point_v2pub.contact_point_rec_type;
1835 l_dummy_rec4 hz_party_site_v2pub.party_site_rec_type;
1836 l_dummy_rec5 hz_party_contact_v2pub.org_contact_rec_type;
1837 l_dummy_rec6 hz_party_contact_v2pub.org_contact_role_rec_type;
1838 l_dummy_rec7 as_sales_leads_pub.sales_lead_rec_type;
1839 l_dummy_tbl8 as_sales_leads_pub.sales_lead_line_tbl_type;
1840 l_dummy_tbl9 as_sales_leads_pub.sales_lead_contact_tbl_type;
1841
1842 Begin
1843 -- Assining HZ_PARTY_V2PUB.organization_rec_type
1844 l_org_rec.organization_name := pI.customer_name;
1845 l_org_rec.sic_code := pI.sic_code;
1846 l_org_rec.sic_code_type := pI.sic_code_type;
1847 l_org_rec.hq_branch_ind := pI.hq_branch_ind;
1848 l_org_rec.tax_reference := pI.tax_reference;
1849 l_org_rec.jgzz_fiscal_code := pI.jgzz_fiscal_code;
1850 l_org_rec.fiscal_yearend_month := pI.fiscal_yearend_month;
1851 l_org_rec.employees_total := pI.num_of_employees;
1852 l_org_rec.curr_fy_potential_revenue := pI.potential_revenue_curr_fy;
1853 l_org_rec.next_fy_potential_revenue := pI.potential_revenue_next_fy;
1854 l_org_rec.year_established := pI.year_established;
1855 l_org_rec.GSA_INDICATOR_FLAG := pI.GSA_INDICATOR_FLAG;
1856 l_org_rec.MISSION_STATEMENT := pI.MISSION_STATEMENT;
1857 l_org_rec.ORGANIZATION_NAME_PHONETIC := pI.ORGANIZATION_NAME_PHONETIC;
1858 l_org_rec.analysis_fy := pI.analysis_fy;
1859 -- ffang 103001, bug 2080069, populate pref_functional_currency
1860 l_org_rec.pref_functional_currency := pI.currency_code;
1861 l_org_rec.known_as := pI.known_as;
1862 l_org_rec.known_as2 := pI.known_as2;
1863 l_org_rec.known_as3 := pI.known_as3;
1864 l_org_rec.known_as4 := pI.known_as4;
1865 l_org_rec.known_as5 := pI.known_as5;
1866
1867 l_org_rec.party_rec.party_number := pI.party_number;
1868 l_org_rec.party_rec.validated_flag := pI.parties_validated_flag;
1869 -- The expectation is pI.orig_system_reference will be concatenated with
1870 -- pI.orig_system_code.
1871 if (pI.orig_system_reference is not null) or
1872 (pI.orig_system_reference <> FND_API.G_MISS_CHAR) Then
1873 l_org_rec.party_rec.orig_system_reference := pI.orig_system_reference;
1874 else
1875 l_org_rec.party_rec.orig_system_reference := pI.import_interface_id;
1876 end if;
1877 l_org_rec.party_rec.status := 'A';
1878 l_org_rec.party_rec.category_code := pI.customer_category_code;
1879
1880 -- aanjaria enh tcav2
1881 -- l_org_rec.duns_number := pI.duns_number;
1882 -- l_org_rec.tax_name := pI.tax_name;
1883 -- l_org_rec.party_rec.customer_key := pI.customer_key;
1884 -- l_org_rec.party_rec.total_num_of_orders := pI.total_num_of_orders; --0;
1885 -- l_org_rec.party_rec.total_ordered_amount := pI.total_ordered_amount;
1886 -- l_org_rec.party_rec.last_ordered_date := pI.last_ordered_date;
1887 l_org_rec.duns_number_c := pI.duns_number_c; --bug# 3170261
1888 l_org_rec.created_by_module := 'AML_LEAD_IMPORT';
1889 l_org_rec.application_id := 530;
1890
1891 do_assign_flex (
1892 l_org_rec.party_rec,
1893 l_dummy_rec2,
1894 l_dummy_rec3,
1895 l_dummy_rec4,
1896 l_dummy_rec5 ,
1897 -- l_dummy_rec6 ,
1898 l_dummy_rec7 ,
1899 l_dummy_tbl8 ,
1900 l_dummy_tbl9 ,
1901 'HZ_PARTIES',
1902 pI.import_interface_id,
1903 G_return_status
1904 );
1905
1906 if G_return_status = FND_API.G_RET_STS_SUCCESS Then
1907 HZ_PARTY_V2PUB.create_organization (
1908 p_init_msg_list => FND_API.G_FALSE,
1909 p_organization_rec => l_org_rec,
1910 x_return_status => G_return_status,
1911 x_msg_count => G_MESG_COUNT,
1912 x_msg_data => l_msg_data,
1913 x_party_id => pI.party_id,
1914 x_party_number => l_hz_partyNumber,
1915 x_profile_id => l_hz_profile
1916 );
1917 IF G_return_status = FND_API.G_RET_STS_SUCCESS Then
1918 write_log (3, 'Organization created: '||pI.party_id);
1919 END IF;
1920 End if ;
1921
1922 End do_create_organization;
1923
1924 ----------------------------------------------------------
1925 -- name: do_create_ps_psu
1926 -- scope: private
1927 -- calls HZ_LOCATION_V2PUB.create_organization
1928 -- inserts party with party type as ORGANIZATION
1929 ----------------------------------------------------------
1930 procedure do_create_ps_psu(
1931 pI IN OUT NOCOPY leadImpType,
1932 p_party_id IN NUMBER,
1933 p_type IN varchar2,
1934 G_return_status OUT NOCOPY varchar2)
1935 IS
1936 -- aanjaria enh tcav2
1937 l_ps_rec hz_party_site_v2pub.party_site_rec_type;
1938 l_ps_use_rec HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
1939 l_ps_use_id number;
1940 l_msg_data VARCHAR2(2000);
1941 l_hz_psNumber VARCHAR2(30);
1942 l_hz_psid NUMBER;
1943
1944 -- Dummy
1945 l_dummy_rec1 hz_party_v2pub.party_rec_type;
1946 l_dummy_rec2 hz_location_v2pub.location_rec_type;
1947 l_dummy_rec3 hz_contact_point_v2pub.contact_point_rec_type;
1948 --l_dummy_rec4 hz_party_pub.party_site_rec_type;
1949 l_dummy_rec5 hz_party_contact_v2pub.org_contact_rec_type;
1950 l_dummy_rec6 hz_party_contact_v2pub.org_contact_role_rec_type;
1951 l_dummy_rec7 as_sales_leads_pub.sales_lead_rec_type;
1952 l_dummy_tbl8 as_sales_leads_pub.sales_lead_line_tbl_type;
1953 l_dummy_tbl9 as_sales_leads_pub.sales_lead_contact_tbl_type;
1954
1955 Begin
1956 -- l_ps_rec.party_id := pI.party_id;
1957 l_ps_rec.party_id := p_party_id;
1958 l_ps_rec.location_id := pI.location_id;
1959 l_ps_rec.orig_system_reference := pI.import_interface_id;
1960 l_ps_rec.party_site_number := pI.party_site_number;
1961 l_ps_rec.addressee := pI.addressee;
1962 l_ps_rec.mailstop := pI.mailstop;
1963 l_ps_rec.party_site_name := pI.party_site_name;
1964 -- ffang 071101, bug 1874947, always pass 'N', AR API will take care of it.
1965 l_ps_rec.identifying_address_flag := 'N';
1966 l_ps_rec.status := 'A';
1967
1968 l_ps_rec.created_by_module := 'AML_LEAD_IMPORT';
1969 l_ps_rec.application_id := 530;
1970
1971 write_log(3, 'Creating PartySite for '||l_ps_rec.party_id||':'||
1972 l_ps_rec.location_id);
1973
1974 -- swkhanna 6/12/02 Bug 2404796
1975 --IF p_type = 'ORG' THEN
1976 IF p_type in ( 'ORGANIZATION','PERSON') THEN
1977 do_assign_flex (
1978 l_dummy_rec1,
1979 l_dummy_rec2,
1980 l_dummy_rec3,
1981 l_ps_rec,
1982 l_dummy_rec5 ,
1983 -- l_dummy_rec6 ,
1984 l_dummy_rec7 ,
1985 l_dummy_tbl8 ,
1986 l_dummy_tbl9 ,
1987 'HZ_PARTY_SITES',
1988 pI.import_interface_id,
1989 G_return_status
1990 );
1991 ELSIF p_type = 'REL' THEN
1992 G_return_status := FND_API.G_RET_STS_SUCCESS;
1993 END IF;
1994
1995 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
1996 HZ_PARTY_SITE_V2PUB.create_party_site (
1997 p_init_msg_list => FND_API.G_FALSE,
1998 p_party_site_rec => l_ps_rec,
1999 x_return_status => G_return_status,
2000 x_msg_count => G_MESG_COUNT,
2001 x_msg_data => l_msg_data,
2002 x_party_site_id => l_hz_psid, -- pI.hz_psid,
2003 x_party_site_number=> l_hz_psNumber
2004 );
2005
2006 -- If error raise exception
2007 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2008 write_log(3, 'Creating Party Site failed');
2009 write_log(3, 'insert error messages into as_imp_errors table');
2010 RAISE FND_API.G_EXC_ERROR;
2011 ELSE
2012 write_log(3, p_type||' Party Site created: '||l_hz_psid);
2013 -- IF p_type = 'ORG' THEN
2014 IF p_type in ('ORGANIZATION','PERSON') THEN
2015 pI.party_site_id := l_hz_psid;
2016 END IF;
2017 END IF;
2018
2019 write_log(3, 'Creating Party Site Use');
2020 l_ps_use_rec.party_site_id := l_hz_psid; -- pI.hz_psid;
2021 -- l_ps_use_rec.begin_date := sysdate;
2022 -- ffang 100501, if site_use_type is not passed in, then default it
2023 -- to 'BILL_TO'
2024 IF (pI.site_use_type is null OR pI.site_use_type = FND_API.G_MISS_CHAR)
2025 THEN
2026 l_ps_use_rec.site_use_type := 'BILL_TO';
2027 ELSE
2028 l_ps_use_rec.site_use_type := pI.site_use_type; -- 'BILL_TO';
2029 END IF;
2030 l_ps_use_rec.comments := pI.ps_uses_comments;
2031 l_ps_use_rec.PRIMARY_PER_TYPE := pI.PRIMARY_PER_TYPE;
2032 l_ps_use_rec.STATUS := 'A';
2033
2034 l_ps_use_rec.created_by_module := 'AML_LEAD_IMPORT';
2035 l_ps_use_rec.application_id := 530;
2036
2037 HZ_PARTY_SITE_V2PUB.create_party_site_use (
2038 p_init_msg_list => FND_API.G_FALSE,
2039 p_party_site_use_rec => l_ps_use_rec,
2040 x_return_status => G_return_status,
2041 x_msg_count => G_MESG_COUNT,
2042 x_msg_data => l_msg_data,
2043 x_party_site_use_id => l_ps_use_id
2044 );
2045 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2046 write_log(3, 'Creating Party Site Use failed');
2047 ELSE
2048 write_log(3, 'PS Use created: ' || l_ps_use_id);
2049 END IF;
2050
2051 End If;
2052 End do_create_ps_psu;
2053
2054 ----------------------------------------------------------
2055 -- name: do_create_relationship
2056 -- scope: private
2057 -- calls HZ_PARTY_CONTACT_V2PUB.create_org_contact
2058 -- inserts party relationship and creates org_contact
2059 ----------------------------------------------------------
2060 procedure do_create_relationship(
2061 pI IN OUT NOCOPY leadImpType,
2062 G_return_status OUT NOCOPY varchar2)
2063 IS
2064 l_org_con_rec hz_party_contact_v2pub.org_contact_rec_type;
2065 l_role_rec hz_party_contact_v2pub.org_contact_role_rec_type;
2066 -- l_rel_rec hz_party_pub.party_rel_rec_type;
2067 --l_org_contact_id NUMBER;
2068 l_party_rel_id NUMBER;
2069 l_party_id NUMBER;
2070 l_role_id NUMBER;
2071 l_party_number VARCHAR2(30);
2072 l_msg_data VARCHAR2(2000);
2073
2074 -- Dummy
2075 l_dummy_rec1 hz_party_v2pub.party_rec_type;
2076 l_dummy_rec2 hz_location_v2pub.location_rec_type;
2077 l_dummy_rec3 hz_contact_point_v2pub.contact_point_rec_type;
2078 l_dummy_rec4 hz_party_site_v2pub.party_site_rec_type;
2079 l_dummy_rec5 hz_party_contact_v2pub.org_contact_rec_type;
2080 l_dummy_rec6 hz_party_contact_v2pub.org_contact_role_rec_type;
2081 l_dummy_rec7 as_sales_leads_pub.sales_lead_rec_type;
2082 l_dummy_tbl8 as_sales_leads_pub.sales_lead_line_tbl_type;
2083 l_dummy_tbl9 as_sales_leads_pub.sales_lead_contact_tbl_type;
2084
2085 cursor c_cnt_role (c_import_interface_id NUMBER) is
2086 select * from AS_IMP_CNT_ROL_INTERFACE
2087 where import_interface_id = c_import_interface_id;
2088
2089 Begin
2090 --check if the relationship is existing or not.
2091 -- if the relationship exists, then we can assume that the
2092 -- org contact rec exists.
2093 Begin
2094 Select party_id into pI.rel_party_id
2095 from hz_relationships
2096 where subject_id = pI.contact_party_id
2097 and object_id = pI.party_id
2098 and subject_table_name = 'HZ_PARTIES'
2099 and object_table_name = 'HZ_PARTIES'
2100 and relationship_code = 'CONTACT_OF';
2101
2102 /* *****
2103 -- ffang 073101, use hz_relationships instead of hz_party_relationships
2104 Select party_id into pI.rel_party_id
2105 from hz_party_relationships
2106 where subject_id = pI.contact_party_id
2107 and object_id = pI.party_id
2108 and party_relationship_type = 'CONTACT_OF';
2109 ***** */
2110
2111 Exception
2112 When NO_DATA_FOUND Then
2113 write_log(3, 'Creating Relationship');
2114
2115 write_log(3, 'subject_id '||to_char(pI.contact_party_id));
2116 write_log(3, 'object_id '||to_char(pI.party_id));
2117
2118 l_org_con_rec.comments := pI.org_cnt_comments;
2119 l_org_con_rec.contact_number:= pI.contact_number;
2120 l_org_con_rec.department_code := pI.department_code;
2121 l_org_con_rec.department := pI.department;
2122 --l_org_con_rec.title := pI.org_cnt_title; -- SOLIN, bug 4602573
2123 l_org_con_rec.job_title := pI.job_title;
2124 l_org_con_rec.job_title_code := pI.job_title_code;
2125 l_org_con_rec.decision_maker_flag := pI.decision_maker_flag;
2126 l_org_con_rec.reference_use_flag := pI.reference_use_flag;
2127 l_org_con_rec.rank := pI.rank;
2128 l_org_con_rec.party_site_id := pI.party_site_id;
2129 l_org_con_rec.orig_system_reference := pI.import_interface_id;
2130 l_org_con_rec.party_rel_rec.subject_id := pI.contact_party_id;
2131 l_org_con_rec.party_rel_rec.subject_type := 'PERSON';
2132 l_org_con_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
2133 l_org_con_rec.party_rel_rec.object_id := pI.party_id;
2134 l_org_con_rec.party_rel_rec.object_type := 'ORGANIZATION';
2135 l_org_con_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
2136 l_org_con_rec.party_rel_rec.relationship_type := 'CONTACT';
2137 l_org_con_rec.party_rel_rec.start_date:= sysdate;
2138 l_org_con_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
2139
2140 -- aanjaria enh tcav2
2141 -- l_org_con_rec.mail_stop := pI.mail_stop;
2142 -- l_org_con_rec.contact_key := substr(pI.contact_key,1,50);
2143 l_org_con_rec.created_by_module := 'AML_LEAD_IMPORT';
2144 l_org_con_rec.application_id := 530;
2145
2146 do_assign_flex (
2147 l_dummy_rec1,
2148 l_dummy_rec2,
2149 l_dummy_rec3,
2150 l_dummy_rec4,
2151 l_org_con_rec ,
2152 -- l_dummy_rec6 ,
2153 l_dummy_rec7 ,
2154 l_dummy_tbl8 ,
2155 l_dummy_tbl9 ,
2156 'HZ_ORG_CONTACTS',
2157 pI.import_interface_id,
2158 G_return_status
2159 );
2160 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
2161 write_log(3, 'Creating OrgContact');
2162 HZ_PARTY_CONTACT_V2PUB.create_org_contact (
2163 p_init_msg_list => FND_API.G_FALSE,
2164 p_org_contact_rec => l_org_con_rec,
2165 x_return_status => G_return_status,
2166 x_msg_count => G_MESG_COUNT,
2167 x_msg_data => l_msg_data,
2168 x_org_contact_id => G_LOCAL_ORG_CONTACT_ID,
2169 x_party_rel_id => l_party_rel_id,
2170 x_party_id => pI.rel_party_id,
2171 x_party_number => l_party_number
2172 );
2173 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
2174 write_log (3, 'OrgContact created: '||
2175 G_LOCAL_ORG_CONTACT_ID);
2176 write_log (3, 'Party Relationship created: '||
2177 l_party_rel_id||':'||pI.rel_party_id);
2178 -- ffang 102401, for bug 2075424, if location is not
2179 -- created, don't create party_site
2180 IF (pI.location_id is not null and
2181 pI.location_id <> FND_API.G_MISS_NUM)
2182 THEN
2183 write_log(3,'Creating party site for Relationship');
2184 do_create_ps_psu(pI, pI.rel_party_id, 'REL',
2185 G_return_status);
2186 -- If error raise exception
2187 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2188 write_log(3, 'Creating PS for Rel failed');
2189 END IF;
2190 END IF;
2191
2192 -- Creating OrgContact Role(in as_imp_cnt_rol_interface)
2193 IF (pI.customer_rank is not NULL AND
2194 pI.customer_rank <> FND_API.G_MISS_CHAR) THEN
2195 l_role_rec.role_type := pI.customer_rank;
2196 l_role_rec.primary_flag := 'N'; --'Y';
2197 l_role_rec.org_contact_id := G_LOCAL_ORG_CONTACT_ID;
2198 l_role_rec.orig_system_reference :=
2199 pI.import_interface_id;
2200 l_role_rec.role_level := pI.role_level;
2201 l_role_rec.primary_contact_per_role_type :=
2202 pI.primary_contact_per_role_type;
2203 l_role_rec.status := 'A';
2204
2205 l_role_rec.created_by_module := 'AML_LEAD_IMPORT';
2206 l_role_rec.application_id := 530;
2207
2208 If (( l_role_rec.role_type is not null) OR
2209 (l_role_rec.role_type <> FND_API.G_MISS_CHAR))
2210 Then
2211 If G_return_status = FND_API.G_RET_STS_SUCCESS
2212 Then
2213 write_log(3, 'Creating OrgContactRole(1)');
2214 HZ_PARTY_CONTACT_V2PUB.create_org_contact_role (
2215 p_init_msg_list => FND_API.G_FALSE,
2216 p_org_contact_role_rec => l_role_rec,
2217 x_return_status => G_return_status,
2218 x_msg_count => G_MESG_COUNT,
2219 x_msg_data => l_msg_data,
2220 x_org_contact_role_id => l_role_id
2221 );
2222 If G_return_status <>
2223 FND_API.G_RET_STS_SUCCESS
2224 Then
2225 write_log(3,
2226 'Creating OrgCntRole failed');
2227 ELSE
2228 write_log(3, 'orgCntRole created: ' ||
2229 l_role_id);
2230 END IF;
2231 End if;
2232 End if;
2233 End if;
2234
2235 -- Creating OrgContact Role(in as_imp_cnt_rol_interface)
2236 FOR OCR IN c_cnt_role(pI.import_interface_id) LOOP
2237 l_role_rec.role_type := OCR.role_type;
2238 -- pI.customer_rank;
2239 IF (OCR.primary_flag is not NULL AND
2240 OCR.primary_flag <> FND_API.G_MISS_CHAR) THEN
2241 l_role_rec.primary_flag := OCR.primary_flag;
2242 ELSE
2243 l_role_rec.primary_flag := 'N';
2244 END IF;
2245 l_role_rec.org_contact_id := G_LOCAL_ORG_CONTACT_ID;
2246 IF (OCR.orig_system_reference is not NULL AND
2247 OCR.orig_system_reference<>FND_API.G_MISS_CHAR)
2248 THEN
2249 l_role_rec.orig_system_reference :=
2250 OCR.orig_system_reference;
2251 ELSE
2252 l_role_rec.orig_system_reference :=
2253 pI.import_interface_id;
2254 END IF;
2255 l_role_rec.role_level := OCR.role_level;
2256 l_role_rec.primary_contact_per_role_type :=
2257 OCR.primary_contact_per_role_type;
2258 l_role_rec.status := 'A';
2259
2260 l_role_rec.created_by_module := 'AML_LEAD_IMPORT';
2261 l_role_rec.application_id := 530;
2262
2263 If (( l_role_rec.role_type is not null) OR
2264 (l_role_rec.role_type <> FND_API.G_MISS_CHAR))
2265 Then
2266 -- ffang 082001, hz_org_contacts_roles'
2267 -- flexfields are going to be obsolete.
2268 -- No need to populate.
2269 /* ***
2270 do_assign_flex (
2271 l_dummy_rec1,
2272 l_dummy_rec2,
2273 l_dummy_rec3,
2274 l_dummy_rec4,
2275 l_dummy_rec5 ,
2276 -- l_role_rec ,
2277 l_dummy_rec7 ,
2278 l_dummy_tbl8 ,
2279 l_dummy_tbl9 ,
2280 'HZ_ORG_CONTACT_ROLES',
2281 pI.import_interface_id,
2282 G_return_status
2283 );
2284 *** */
2285 If G_return_status = FND_API.G_RET_STS_SUCCESS
2286 Then
2287 write_log(3, 'Creating OrgContactRole(2)');
2288 HZ_PARTY_CONTACT_V2PUB.create_org_contact_role (
2289 p_init_msg_list => FND_API.G_FALSE,
2290 p_org_contact_role_rec => l_role_rec,
2291 x_return_status => G_return_status,
2292 x_msg_count => G_MESG_COUNT,
2293 x_msg_data => l_msg_data,
2294 x_org_contact_role_id => l_role_id
2295 );
2296 If G_return_status <>
2297 FND_API.G_RET_STS_SUCCESS
2298 Then
2299 write_log(3,
2300 'Creating OrgContRole failed');
2301 ELSE
2302 -- ffang 082201, write back to role
2303 -- interface table
2304 update as_imp_cnt_rol_interface
2305 set org_contact_role_id = l_role_id,
2306 org_contact_id =
2307 G_LOCAL_ORG_CONTACT_ID
2308 where imp_cnt_rol_interface_id =
2309 OCR.imp_cnt_rol_interface_id;
2310
2311 write_log(3, 'orgCntRole created: ' ||
2312 l_role_id);
2313 END IF;
2314 End if;
2315 END IF;
2316 End LOOP;
2317 ELSE
2318 write_log(3, 'Creating OrgContact failed');
2319 End if;
2320 End if;
2321 When Others Then
2322 RAISE FND_API.G_EXC_ERROR;
2323 End;
2324 END do_create_relationship;
2325
2326 ----------------------------------------------------------
2327 -- name: do_create_saleslead
2328 -- scope: private
2329 -- calls as_sales_leads_pub.create_sales_lead
2330 -- inserts sales lead header and lines
2331 ----------------------------------------------------------
2332 procedure do_create_saleslead( pI IN OUT NOCOPY leadImpType,
2333 G_return_status OUT NOCOPY varchar2)
2334 IS
2335 l_sales_lead_rec as_sales_leads_pub.sales_lead_rec_type;
2336 l_sales_lead_line_rec as_sales_leads_pub.sales_lead_line_rec_type;
2337 l_sales_lead_line_tbl as_sales_leads_pub.sales_lead_line_tbl_type;
2338 l_sales_lead_contact_rec as_sales_leads_pub.sales_lead_contact_rec_type;
2339 l_sales_lead_contact_tbl as_sales_leads_pub.sales_lead_contact_tbl_type;
2340 l_sales_lead_line_out_tbl as_sales_leads_pub.sales_lead_line_out_tbl_type;
2341 l_sales_lead_cnt_out_tbl as_sales_leads_pub.sales_lead_cnt_out_tbl_type;
2342 l_sales_lead_profile_tbl as_utility_pub.profile_tbl_type;
2343 l_sales_lead_id NUMBER;
2344 l_msg_data VARCHAR2(2000) := NULL;
2345 l_api_message VARCHAR2(2000);
2346 l_api_name CONSTANT VARCHAR2(30) := 'create_sales_lead';
2347 l_temp_promotion_id NUMBER;
2348 -- ffang 101601, bug 2053591
2349 l_temp_promotion_code VARCHAR2(50);
2350 l_contact_party_id NUMBER;
2351 l_retcode VARCHAR2(1) := NULL; -- used by create_lead_note
2352 l_lead_note_id NUMBER;
2353
2354 -- primary contact point id of the primary contact
2355 l_contact_point_id NUMBER;
2356
2357 -- Dummy
2358 l_dummy_rec1 hz_party_v2pub.party_rec_type;
2359 l_dummy_rec2 hz_location_v2pub.location_rec_type;
2360 l_dummy_rec3 hz_contact_point_v2pub.contact_point_rec_type;
2361 l_dummy_rec4 hz_party_site_v2pub.party_site_rec_type;
2362 l_dummy_rec5 hz_party_contact_v2pub.org_contact_rec_type;
2363 l_dummy_rec6 hz_party_contact_v2pub.org_contact_role_rec_type;
2364 l_dummy_rec7 as_sales_leads_pub.sales_lead_rec_type;
2365 l_dummy_tbl8 as_sales_leads_pub.sales_lead_line_tbl_type;
2366 l_dummy_tbl9 as_sales_leads_pub.sales_lead_contact_tbl_type;
2367
2368 CURSOR c_get_source_code (c_promotion_id number) IS
2369 SELECT source_code_id, source_code
2370 FROM ams_p_source_codes_v
2371 WHERE source_code_id = c_promotion_id
2372 AND source_type in ('CAMP','CSCH','EONE', 'EVEH','EVEO')
2373 AND status in ('ACTIVE','ONHOLD', 'COMPLETED');
2374
2375 CURSOR c_get_promotion_id (c_promotion_code VARCHAR2) IS
2376 SELECT source_code_id
2377 -- SOLIN, bug 4927392, use view ams_p_source_codes_v
2378 FROM ams_p_source_codes_v
2379 WHERE upper(source_code) = upper(c_promotion_code)
2380 AND source_type in ('CAMP','CSCH','EONE', 'EVEH','EVEO')
2381 AND status in ('ACTIVE','ONHOLD', 'COMPLETED');
2382
2383 -- ffang 082401, for supporting multiple lines
2384 CURSOR c_get_lines (c_import_interface_id number) IS
2385 select * from as_imp_lines_interface
2386 where import_interface_id = c_import_interface_id;
2387
2388 l_index NUMBER;
2389 -- end ffang 082401
2390
2391 -- ajchatto, for retrieving primary contact point of primary contact
2392 CURSOR c_get_primary_cp (c_rel_party_id number) IS
2393 SELECT CONTACT_POINT_ID
2394 FROM HZ_CONTACT_POINTS
2395 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES' AND CONTACT_POINT_TYPE = 'PHONE'
2396 AND PRIMARY_FLAG = 'Y' AND OWNER_TABLE_ID = c_rel_party_id
2397 AND ROWNUM = 1; --TO SELECT ONE ROW
2398
2399 -- swkhanna 7/30/02 get assign_to_person_id if not being passed in table
2400 Cursor c_get_person_id (c_salesforce_id number) is
2401 select source_id
2402 from jtf_rs_resource_extns
2403 where resource_id = c_salesforce_id;
2404
2405 l_assign_to_person_id number;
2406 l_validation_level number;
2407
2408 BEGIN
2409
2410 write_log(3, 'do_create_saleslead:Start');
2411
2412 -- PROMOTION_ID/PROMOTION_CODE
2413 IF (pI.promotion_id is not null AND pI.promotion_id <> FND_API.G_MISS_NUM)
2414 THEN
2415 l_temp_promotion_id := NULL;
2416 l_temp_promotion_code := NULL;
2417
2418 -- Validate promotion_id and get promotion_code
2419 OPEN c_get_source_code ( pI.PROMOTION_ID);
2420 FETCH c_get_source_code into l_temp_promotion_id, l_temp_promotion_code;
2421 IF c_get_source_code%NOTFOUND THEN
2422 write_log(3,'Invalid promotion id:'||pI.promotion_id);
2423 CLOSE c_get_source_code;
2424
2425 -- ffang 052301, push error message into stack
2426 AS_UTILITY_PVT.Set_Message(
2427 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2428 p_msg_name => 'API_INVALID_ID',
2429 p_token1 => 'COLUMN',
2430 p_token1_value => 'PROMOTION ID',
2431 p_token2 => 'VALUE',
2432 p_token2_value => pI.PROMOTION_ID );
2433 -- RAISE NO_DATA_FOUND;
2434 RAISE FND_API.G_EXC_ERROR;
2435 ELSE
2436 -- l_sales_lead_rec.SOURCE_PROMOTION_ID := l_temp_promotion_id;
2437 pI.promotion_id := l_temp_promotion_id;
2438 END IF;
2439 CLOSE c_get_source_code;
2440
2441 -- ffang 101601, bug 2053591, if promotion_code is not null, match them
2442 IF (pI.promotion_code is not null
2443 AND pI.promotion_code <> FND_API.G_MISS_CHAR)
2444 THEN
2445 IF UPPER(pI.promotion_code) <> UPPER(l_temp_promotion_code) THEN
2446 write_log(3,'promotion_id and promotion_code not match:');
2447 write_log(3,'promotion_id: '||pI.promotion_id);
2448 write_log(3,'promotion_code: '||pI.promotion_code);
2449
2450 AS_UTILITY_PVT.Set_Message(
2451 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2452 p_msg_name => 'AS_NOT_MATCHING_ID_CODE',
2453 p_token1 => 'VALUE1',
2454 p_token1_value => pI.promotion_id,
2455 p_token2 => 'VALUE2',
2456 p_token2_value => pI.promotion_code );
2457 RAISE FND_API.G_EXC_ERROR;
2458 END IF;
2459 ELSE
2460 -- promotion_code is not given, populate it in as_import_interface
2461 -- swkhanna 05/28/02
2462 pI.promotion_code := UPPER(l_temp_promotion_code);
2463 END IF;
2464
2465 ELSE -- promotion_id is not given
2466
2467 IF (pI.promotion_code is not null AND
2468 pI.promotion_code <> FND_API.G_MISS_CHAR)
2469 THEN
2470 l_temp_promotion_id := NULL;
2471
2472 -- Validate promotion_code and get promotion_id
2473 OPEN c_get_promotion_id (pI.PROMOTION_CODE);
2474 FETCH c_get_promotion_id into l_temp_promotion_id;
2475 IF c_get_promotion_id%NOTFOUND THEN
2476 write_log(3,'Invalid promotion code:'||pI.promotion_code);
2477 CLOSE c_get_promotion_id;
2478
2479 -- ffang 052301, push error message into stack
2480 AS_UTILITY_PVT.Set_Message(
2481 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2482 p_msg_name => 'API_INVALID_ID',
2483 p_token1 => 'COLUMN',
2484 p_token1_value => 'PROMOTION CODE',
2485 p_token2 => 'VALUE',
2486 p_token2_value => pI.PROMOTION_CODE );
2487 -- RAISE NO_DATA_FOUND;
2488 RAISE FND_API.G_EXC_ERROR;
2489 ELSE
2490 -- l_sales_lead_rec.SOURCE_PROMOTION_ID := l_temp_promotion_id;
2491 pI.promotion_id := l_temp_promotion_id;
2492 END IF;
2493 CLOSE c_get_promotion_id;
2494 END IF;
2495
2496 END IF;
2497
2498 -- Sales lead header
2499 l_sales_lead_rec.LEAD_NUMBER := nvl(pI.LEAD_NUMBER, '-1');
2500 l_sales_lead_rec.STATUS_CODE := pI.STATUS_CODE ;
2501 l_sales_lead_rec.CUSTOMER_ID := pI.party_id ;
2502 l_sales_lead_rec.ADDRESS_ID := pI.party_site_id;
2503 l_sales_lead_rec.SOURCE_PROMOTION_ID := pI.promotion_id;
2504 l_sales_lead_rec.ORIG_SYSTEM_REFERENCE := pI.orig_system_reference;
2505 l_sales_lead_rec.CONTACT_ROLE_CODE := pI.CONTACT_ROLE_CODE;
2506 l_sales_lead_rec.CHANNEL_CODE := pI.CHANNEL_CODE ;
2507 l_sales_lead_rec.BUDGET_AMOUNT := pI.BUDGET_AMOUNT ;
2508 l_sales_lead_rec.currency_code := pI.currency_code;
2509 l_sales_lead_rec.DECISION_TIMEFRAME_CODE := pI.DECISION_TIMEFRAME_CODE ;
2510 l_sales_lead_rec.CLOSE_REASON := pI.CLOSE_REASON;
2511 l_sales_lead_rec.LEAD_RANK_ID := pI.LEAD_RANK_ID;
2512 l_sales_lead_rec.PARENT_PROJECT := pI.PARENT_PROJECT;
2513 l_sales_lead_rec.DESCRIPTION := pI.DESCRIPTION;
2514
2515 /* -- Removed logic for lead_name
2516 IF (pI.DESCRIPTION is not NULL AND pI.DESCRIPTION <> FND_API.G_MISS_CHAR)
2517 THEN
2518 l_sales_lead_rec.DESCRIPTION := pI.DESCRIPTION;
2519 ELSE
2520 IF (pI.last_name is not NULL AND pI.last_name <> FND_API.G_MISS_CHAR)
2521 THEN
2522 IF(pI.first_name is not NULL AND pI.first_name<>FND_API.G_MISS_CHAR)
2523 THEN
2524 l_sales_lead_rec.DESCRIPTION:=pI.last_name||', '||pI.first_name;
2525 ELSE
2526 l_sales_lead_rec.DESCRIPTION := pI.last_name;
2527 END IF;
2528 ELSE
2529 IF(pI.first_name is not NULL AND pI.first_name<>FND_API.G_MISS_CHAR)
2530 THEN
2531 l_sales_lead_rec.DESCRIPTION := pI.first_name;
2532 END IF;
2533 END IF;
2534 END IF;
2535 */
2536
2537 -- 7/30/02 swkhanna - get assign_to_person if not passed in
2538 if pI.ASSIGN_TO_PERSON_ID is null and pI.ASSIGN_TO_SALESFORCE_ID is not null then
2539 open c_get_person_id(pI.ASSIGN_TO_SALESFORCE_ID);
2540 fetch c_get_person_id into l_assign_to_person_id;
2541 close c_get_person_id;
2542
2543 pI.ASSIGN_TO_PERSON_ID := l_assign_to_person_id;
2544
2545 elsif pI.ASSIGN_TO_PERSON_ID is not null then
2546 l_assign_to_person_id := pI.ASSIGN_TO_PERSON_ID;
2547 end if;
2548
2549 --l_sales_lead_rec.ASSIGN_TO_PERSON_ID := pI.ASSIGN_TO_PERSON_ID;
2550 l_sales_lead_rec.ASSIGN_TO_PERSON_ID := l_assign_to_person_id;
2551 l_sales_lead_rec.ASSIGN_TO_SALESFORCE_ID := pI.ASSIGN_TO_SALESFORCE_ID;
2552 l_sales_lead_rec.ASSIGN_SALES_GROUP_ID := pI.ASSIGN_SALES_GROUP_ID;
2553 l_sales_lead_rec.ASSIGN_DATE := pI.ASSIGN_DATE;
2554 l_sales_lead_rec.BUDGET_STATUS_CODE := pI.BUDGET_STATUS_CODE;
2555 l_sales_lead_rec.ACCEPT_FLAG := pI.ACCEPT_FLAG;
2556 l_sales_lead_rec.VEHICLE_RESPONSE_CODE := pI.VEHICLE_RESPONSE_CODE;
2557 l_sales_lead_rec.SCORECARD_ID := pI.SCORECARD_ID;
2558 l_sales_lead_rec.KEEP_FLAG := pI.KEEP_FLAG;
2559 l_sales_lead_rec.URGENT_FLAG := pI.URGENT_FLAG;
2560 l_sales_lead_rec.IMPORT_FLAG := NVL(pI.IMPORT_FLAG,'Y');
2561 l_sales_lead_rec.REJECT_REASON_CODE := pI.REJECT_REASON_CODE;
2562 l_sales_lead_rec.DELETED_FLAG := pI.DELETED_FLAG;
2563 l_sales_lead_rec.OFFER_ID := pI.OFFER_ID;
2564 l_sales_lead_rec.INCUMBENT_PARTNER_PARTY_ID :=
2565 pI.INCUMBENT_PARTNER_PARTY_ID;
2566 l_sales_lead_rec.INCUMBENT_PARTNER_RESOURCE_ID :=
2567 pI.INCUMBENT_PARTNER_RESOURCE_ID;
2568 l_sales_lead_rec.PRM_EXEC_SPONSOR_FLAG := pI.PRM_EXEC_SPONSOR_FLAG;
2569 l_sales_lead_rec.PRM_PRJ_LEAD_IN_PLACE_FLAG :=
2570 pI.PRM_PRJ_LEAD_IN_PLACE_FLAG;
2571 l_sales_lead_rec.PRM_SALES_LEAD_TYPE := pI.PRM_SALES_LEAD_TYPE;
2572 l_sales_lead_rec.PRM_IND_CLASSIFICATION_CODE :=
2573 pI.PRM_IND_CLASSIFICATION_CODE;
2574 l_sales_lead_rec.QUALIFIED_FLAG := upper(pI.QUALIFIED_FLAG);
2575 l_sales_lead_rec.ORIG_SYSTEM_CODE := pI.ORIG_SYSTEM_CODE;
2576 l_sales_lead_rec.PRM_ASSIGNMENT_TYPE := pI.PRM_ASSIGNMENT_TYPE;
2577 l_sales_lead_rec.AUTO_ASSIGNMENT_TYPE := pI.AUTO_ASSIGNMENT_TYPE;
2578 --
2579 -- 5/24/02 swkhanna , Bug 2341515, Bug 2368075
2580 l_sales_lead_rec.LEAD_DATE := pI.LEAD_DATE ;
2581 l_sales_lead_rec.SOURCE_SYSTEM := pI.SOURCE_SYSTEM;
2582 l_sales_lead_rec.COUNTRY := pI.COUNTRY;
2583
2584 --Purging changes --aanjaria
2585 l_sales_lead_rec.marketing_score := pI.marketing_score;
2586 l_sales_lead_rec.interaction_score := pI.interaction_score;
2587 l_sales_lead_rec.source_primary_reference := pI.source_primary_reference;
2588 l_sales_lead_rec.source_secondary_reference := pI.source_secondary_reference;
2589 l_sales_lead_rec.sales_methodology_id := pI.sales_methodology_id;
2590
2591 -- Sales lead lines
2592 l_index := 0;
2593 FOR LL in c_get_lines(pI.import_interface_id) LOOP
2594 l_index := l_index + 1;
2595
2596 l_sales_lead_line_tbl(l_index).status_code := null;
2597 l_sales_lead_line_tbl(l_index).budget_amount := LL.budget_amount;
2598
2599 -- Single Product Hierarchy Uptake
2600 l_sales_lead_line_tbl(l_index).category_id := LL.category_id;
2601
2602 l_sales_lead_line_tbl(l_index).inventory_item_id:= LL.inventory_item_id;
2603 l_sales_lead_line_tbl(l_index).organization_id := LL.organization_id;
2604 l_sales_lead_line_tbl(l_index).quantity := LL.quantity;
2605 l_sales_lead_line_tbl(l_index).uom_code := LL.uom_code;
2606 l_sales_lead_line_tbl(l_index).source_promotion_id :=
2607 LL.source_promotion_id;
2608 l_sales_lead_line_tbl(l_index).offer_id := LL.offer_id;
2609 END LOOP;
2610
2611 -- ffang 091201, support not only the lines in as_imp_lines_interface
2612 -- but also in as_import_interface
2613 -- Sales lead line 1 in as_import_interface
2614 G_SL_LINE_COUNT := 0;
2615 IF pI.category_id_1 IS NOT NULL OR (pI.inventory_item_id_1 IS NOT NULL AND pI.organization_id_1 IS NOT NULL) THEN
2616 l_index := l_index + 1;
2617
2618 -- l_sales_lead_line_tbl(l_index).status_code := pI.status_code_1;
2619 -- Single Product Hierarchy Uptake
2620 l_sales_lead_line_tbl(l_index).category_id := pI.category_id_1;
2621
2622 l_sales_lead_line_tbl(l_index).inventory_item_id :=
2623 pI.inventory_item_id_1;
2624 l_sales_lead_line_tbl(l_index).organization_id := pI.organization_id_1;
2625 l_sales_lead_line_tbl(l_index).uom_code := pI.uom_code_1;
2626 l_sales_lead_line_tbl(l_index).budget_amount := pI.budget_amount_1;
2627 l_sales_lead_line_tbl(l_index).quantity := pI.quantity_1;
2628 l_sales_lead_line_tbl(l_index).source_promotion_id :=
2629 pI.source_promotion_id_1;
2630 --l_sales_lead_rec.SOURCE_PROMOTION_ID;
2631 l_sales_lead_line_tbl(l_index).offer_id:= pI.offer_id_1;
2632 G_SL_LINE_COUNT := 1;
2633 END IF;
2634
2635 -- Sales lead line 2 in as_import_interface
2636 IF pI.category_id_2 IS NOT NULL OR (pI.inventory_item_id_2 IS NOT NULL AND pI.organization_id_2 IS NOT NULL) THEN
2637 l_index := l_index + 1;
2638
2639 -- l_sales_lead_line_tbl(l_index).status_code := pI.status_code_2;
2640 -- Single Product Hierarchy Uptake
2641 l_sales_lead_line_tbl(l_index).category_id := pI.category_id_2;
2642
2643 l_sales_lead_line_tbl(l_index).inventory_item_id :=
2644 pI.inventory_item_id_2;
2645 l_sales_lead_line_tbl(l_index).organization_id := pI.organization_id_2;
2646 l_sales_lead_line_tbl(l_index).uom_code := pI.uom_code_2;
2647 l_sales_lead_line_tbl(l_index).budget_amount := pI.budget_amount_2;
2648 l_sales_lead_line_tbl(l_index).quantity := pI.quantity_2;
2649 l_sales_lead_line_tbl(l_index).source_promotion_id :=
2650 pI.source_promotion_id_2;
2651 --l_sales_lead_rec.SOURCE_PROMOTION_ID;
2652 l_sales_lead_line_tbl(l_index).offer_id := pI.offer_id_2;
2653 G_SL_LINE_COUNT := 2;
2654 END IF;
2655
2656 -- Sales lead line 3 in as_import_interface
2657 IF pI.category_id_3 IS NOT NULL OR (pI.inventory_item_id_3 IS NOT NULL AND pI.organization_id_3 IS NOT NULL) THEN
2658 l_index := l_index + 1;
2659
2660 -- l_sales_lead_line_tbl(l_index).status_code := pI.status_code_3;
2661 -- Single Product Hierarchy Uptake
2662 l_sales_lead_line_tbl(l_index).category_id := pI.category_id_3;
2663
2664 l_sales_lead_line_tbl(l_index).inventory_item_id :=
2665 pI.inventory_item_id_3;
2666 l_sales_lead_line_tbl(l_index).organization_id := pI.organization_id_3;
2667 l_sales_lead_line_tbl(l_index).uom_code := pI.uom_code_3;
2668 l_sales_lead_line_tbl(l_index).budget_amount := pI.budget_amount_3;
2669 l_sales_lead_line_tbl(l_index).quantity := pI.quantity_3;
2670 l_sales_lead_line_tbl(l_index).source_promotion_id :=
2671 pI.source_promotion_id_3;
2672 --l_sales_lead;_rec.SOURCE_PROMOTION_ID;
2673 l_sales_lead_line_tbl(l_index).offer_id := pI.offer_id_3;
2674 G_SL_LINE_COUNT := 3;
2675 END IF;
2676
2677 -- Sales lead line 4 in as_import_interface
2678 IF pI.category_id_4 IS NOT NULL OR (pI.inventory_item_id_4 IS NOT NULL AND pI.organization_id_4 IS NOT NULL) THEN
2679 l_index := l_index + 1;
2680
2681 -- l_sales_lead_line_tbl(l_index).status_code := pI.status_code_4;
2682 -- Single Product Hierarchy Uptake
2683 l_sales_lead_line_tbl(l_index).category_id := pI.category_id_4;
2684
2685 l_sales_lead_line_tbl(l_index).inventory_item_id:=
2686 pI.inventory_item_id_4;
2687 l_sales_lead_line_tbl(l_index).organization_id := pI.organization_id_4;
2688 l_sales_lead_line_tbl(l_index).uom_code := pI.uom_code_4;
2689 l_sales_lead_line_tbl(l_index).budget_amount := pI.budget_amount_4;
2690 l_sales_lead_line_tbl(l_index).quantity := pI.quantity_4;
2691 l_sales_lead_line_tbl(l_index).source_promotion_id :=
2692 pI.source_promotion_id_4;
2693 --l_sales_lead_rec.SOURCE_PROMOTION_ID;
2694 l_sales_lead_line_tbl(l_index).offer_id := pI.offer_id_4;
2695 G_SL_LINE_COUNT := 4;
2696 END IF;
2697
2698 -- Sales lead line 5 in as_import_interface
2699 IF pI.category_id_5 IS NOT NULL OR (pI.inventory_item_id_5 IS NOT NULL AND pI.organization_id_5 IS NOT NULL) THEN
2700 l_index := l_index + 1;
2701
2702 -- l_sales_lead_line_tbl(l_index).status_code := pI.status_code_5;
2703 -- Single Product Hierarchy Uptake
2704 l_sales_lead_line_tbl(l_index).category_id := pI.category_id_5;
2705
2706 l_sales_lead_line_tbl(l_index).inventory_item_id:=
2707 pI.inventory_item_id_5;
2708 l_sales_lead_line_tbl(l_index).organization_id := pI.organization_id_5;
2709 l_sales_lead_line_tbl(l_index).uom_code := pI.uom_code_5;
2710 l_sales_lead_line_tbl(l_index).budget_amount := pI.budget_amount_5;
2711 l_sales_lead_line_tbl(l_index).quantity := pI.quantity_5;
2712 l_sales_lead_line_tbl(l_index).source_promotion_id :=
2713 pI.source_promotion_id_5;
2714 --l_sales_lead_rec.SOURCE_PROMOTION_ID;
2715 l_sales_lead_line_tbl(l_index).offer_id := pI.offer_id_5;
2716 G_SL_LINE_COUNT := 5;
2717 END IF;
2718 -- end ffang 091201
2719
2720 write_log(3, 'Total Lead Lines: '||l_index);
2721
2722 -- Sales lead contact
2723 -- ffang 100901, for bug 2042181, if rel_party_id is null, then there is
2724 -- no lead contact
2725 IF (pI.rel_party_id is not null) THEN
2726 l_sales_lead_contact_tbl(1).contact_party_id := pI.rel_party_id ;
2727 l_sales_lead_contact_tbl(1).enabled_flag := 'Y';
2728 l_sales_lead_contact_tbl(1).rank := pI.CUSTOMER_RANK;
2729 l_sales_lead_contact_tbl(1).customer_id := pI.party_id;
2730 l_sales_lead_contact_tbl(1).address_id := pI.party_site_id;
2731 l_sales_lead_contact_tbl(1).contact_role_code := pI.contact_role_code;
2732
2733 -- The primary contact point of the primary contact needs to be populated in the
2734 -- phone_id column.
2735 -- Since, there can be only one contact in the sales lead import interface table,
2736 -- always, set the contact as the primary contact.
2737 -- bugfix 2098158.
2738 /*
2739 l_sales_lead_contact_tbl(1).phone_id := pI.phone_id;
2740
2741 IF (pI.primary_contact_flag is not null and pI.primary_contact_flag <> FND_API.G_MISS_CHAR) THEN
2742 l_sales_lead_contact_tbl(1).primary_contact_flag := pI.primary_contact_flag;
2743 ELSE
2744 l_sales_lead_contact_tbl(1).primary_contact_flag := 'N';
2745 END IF;
2746 */
2747 -- swkhanna : 04/29/02 moved primary contact flag out of the loop
2748 l_sales_lead_contact_tbl(1).primary_contact_flag := 'Y';
2749
2750 -- ajoy,
2751 -- Get the primary contact point id of the primary contact and use it in phone_id
2752 OPEN c_get_primary_cp (pI.REL_PARTY_ID);
2753 FETCH c_get_primary_cp INTO l_contact_point_id;
2754 CLOSE c_get_primary_cp;
2755
2756 if (l_contact_point_id is not null) then
2757 -- Always set it to Primary
2758 write_log(3, 'Primary contact point found for sales lead contact ' || l_contact_point_id);
2759 -- swkhanna 04/29/02 commented out to get rid out cannot insert null error
2760 --l_sales_lead_contact_tbl(1).primary_contact_flag := 'Y';
2761 l_sales_lead_contact_tbl(1).phone_id := l_contact_point_id;
2762 else
2763 write_log(3, 'Primary contact point not found for sales lead contact ');
2764 end if;
2765
2766
2767 -- Always set it to Primary
2768 l_sales_lead_contact_tbl(1).primary_contact_flag := 'Y';
2769
2770 /* swkhanna 7/16
2771 -- commented out the following to fix bug 2462211
2772
2773 -- Get the primary contact point id of the primary contact and use it in phone_id
2774 SELECT CONTACT_POINT_ID
2775 INTO l_contact_point_id
2776 FROM HZ_CONTACT_POINTS
2777 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES' AND CONTACT_POINT_TYPE = 'PHONE'
2778 AND PRIMARY_FLAG = 'Y' AND OWNER_TABLE_ID = pI.REL_PARTY_ID
2779 AND ROWNUM = 1; --TO SELECT ONE ROW
2780
2781 l_sales_lead_contact_tbl(1).phone_id := l_contact_point_id;
2782 write_log(3, 'Primary contact point found for sales lead contact ' || l_contact_point_id);
2783 */
2784
2785 ELSE
2786 write_log(3, 'no lead contact record');
2787 END IF;
2788
2789 -- Flex fields
2790 do_assign_flex (
2791 l_dummy_rec1,
2792 l_dummy_rec2,
2793 l_dummy_rec3,
2794 l_dummy_rec4,
2795 l_dummy_rec5,
2796 -- l_dummy_rec6,
2797 l_sales_lead_rec,
2798 l_dummy_tbl8,
2799 l_dummy_tbl9,
2800 'AS_SALES_LEADS',
2801 pI.import_interface_id,
2802 G_return_status
2803 );
2804 do_assign_flex (
2805 l_dummy_rec1,
2806 l_dummy_rec2,
2807 l_dummy_rec3,
2808 l_dummy_rec4,
2809 l_dummy_rec5,
2810 -- l_dummy_rec6,
2811 l_dummy_rec7,
2812 l_sales_lead_line_tbl,
2813 l_dummy_tbl9,
2814 'AS_SALES_LEAD_LINES',
2815 pI.import_interface_id,
2816 G_return_status
2817 );
2818 do_assign_flex (
2819 l_dummy_rec1,
2820 l_dummy_rec2,
2821 l_dummy_rec3,
2822 l_dummy_rec4,
2823 l_dummy_rec5,
2824 -- l_dummy_rec6,
2825 l_dummy_rec7,
2826 l_dummy_tbl8,
2827 l_sales_lead_contact_tbl,
2828 'AS_SALES_LEAD_CONTACTS',
2829 pI.import_interface_id,
2830 G_return_status
2831 );
2832
2833 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
2834 write_log(3, 'create_sales_lead:Start');
2835
2836 --Bug 3680824: non resource user can import the lead for sales campaign
2837 -- in which case, user validation needs to be bypassed
2838 IF pI.source_system = 'SALES_CAMPAIGN' THEN
2839 l_validation_level := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM;
2840 ELSE
2841 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
2842 END IF;
2843
2844 as_sales_leads_pvt.create_sales_lead(
2845 p_api_version_number => 2.0,
2846 p_init_msg_list => FND_API.G_FALSE,
2847 p_commit => FND_API.G_FALSE,
2848 p_validation_level => l_validation_level,
2849 p_check_access_flag => 'N',
2850 p_admin_flag => 'N',
2851 p_admin_group_id => NULL,
2852 p_identity_salesforce_id => G_SL_SALESFORCE_ID,
2853 p_Sales_Lead_Profile_Tbl => l_Sales_Lead_Profile_Tbl,
2854 p_sales_lead_rec => l_sales_lead_rec,
2855 p_sales_lead_line_tbl => l_sales_lead_line_tbl,
2856 p_sales_lead_contact_tbl => l_sales_lead_contact_tbl,
2857 x_sales_lead_id => pI.sales_lead_id,
2858 x_return_status => G_return_status,
2859 x_msg_count => G_MESG_COUNT,
2860 x_msg_data => l_msg_data,
2861 x_sales_lead_line_out_tbl => l_sales_lead_line_out_tbl,
2862 x_sales_lead_cnt_out_tbl => l_sales_lead_cnt_out_tbl);
2863 End if;
2864
2865 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
2866 write_log(3, 'Sales lead created: ' || pI.sales_lead_id);
2867
2868 select last_update_date into l_sales_lead_rec.last_update_date
2869 from as_sales_leads where sales_lead_id=pI.sales_lead_id;
2870 write_log(3, 'last_update_date: '||l_sales_lead_rec.last_update_date);
2871 ELSE
2872 write_log(3, l_msg_data);
2873 End IF;
2874
2875 END do_create_saleslead;
2876
2877 ----------------------------------------------------------
2878 -- name: do_create_interest
2879 -- scope: private
2880 -- calls AS_INTEREST_PUB.Create_Interest
2881 -- create an entry in as_interest_all table
2882 ----------------------------------------------------------
2883 procedure do_create_interest(
2884 pI IN OUT NOCOPY leadImpType,
2885 G_return_status OUT NOCOPY varchar2)
2886 Is
2887 l_classification_tbl as_interest_pub.interest_tbl_type;
2888 l_interest_use_code varchar2(30);
2889 l_interest_out_id NUMBER;
2890 l_msg_data VARCHAR2(2000) := NULL;
2891
2892 CURSOR c_get_lines_1 (c_import_interface_id number) IS
2893 select * from as_imp_lines_interface
2894 where import_interface_id = c_import_interface_id;
2895 l_ll_index NUMBER;
2896
2897 Begin
2898 write_log(3, 'do_create_interest:Start');
2899
2900 -- For the lines in as_import_interface
2901 write_log(3, 'G_SL_LINE_COUNT: ' || G_SL_LINE_COUNT);
2902 For i IN 1..G_SL_LINE_COUNT Loop
2903 l_classification_tbl(i).customer_id := pI.party_id;
2904 l_classification_tbl(i).address_id := pI.party_site_id;
2905 l_classification_tbl(i).contact_id := G_LOCAL_ORG_CONTACT_ID;
2906
2907 if (i = 1) then
2908 l_classification_tbl(i).interest_type_id := pI.interest_type_id_1;
2909 l_classification_tbl(i).primary_interest_code_id :=
2910 pI.primary_interest_code_id_1;
2911 l_classification_tbl(i).secondary_interest_code_id :=
2912 pI.secondary_interest_code_id_1;
2913 end if;
2914
2915 if (i = 2) then
2916 l_classification_tbl(i).interest_type_id := pI.interest_type_id_2;
2917 l_classification_tbl(i).primary_interest_code_id :=
2918 pI.primary_interest_code_id_2;
2919 l_classification_tbl(i).secondary_interest_code_id :=
2920 pI.secondary_interest_code_id_2;
2921 end if;
2922
2923 if (i = 3) then
2924 l_classification_tbl(i).interest_type_id := pI.interest_type_id_3;
2925 l_classification_tbl(i).primary_interest_code_id :=
2926 pI.primary_interest_code_id_3;
2927 l_classification_tbl(i).secondary_interest_code_id :=
2928 pI.secondary_interest_code_id_3;
2929 end if;
2930
2931 if (i = 4) then
2932 l_classification_tbl(i).interest_type_id := pI.interest_type_id_4;
2933 l_classification_tbl(i).primary_interest_code_id :=
2934 pI.primary_interest_code_id_4;
2935 l_classification_tbl(i).secondary_interest_code_id :=
2936 pI.secondary_interest_code_id_4;
2937 end if;
2938
2939 if (i = 5) then
2940 l_classification_tbl(i).interest_type_id := pI.interest_type_id_5;
2941 l_classification_tbl(i).primary_interest_code_id :=
2942 pI.primary_interest_code_id_5;
2943 l_classification_tbl(i).secondary_interest_code_id :=
2944 pI.secondary_interest_code_id_5;
2945 end if;
2946
2947
2948 if pI.party_type = 'PERSON' then
2949 l_interest_use_code := 'CONTACT_INTEREST';
2950 elsif pI.party_type = 'ORGANIZATION' then
2951 l_interest_use_code := 'COMPANY_CLASSIFICATION' ;
2952 end if;
2953
2954 AS_INTEREST_PUB.Create_Interest(
2955 p_api_version_number => 2.0 ,
2956 p_init_msg_list => FND_API.G_FALSE,
2957 p_Commit => FND_API.G_FALSE,
2958 p_interest_rec => l_classification_tbl(i),
2959 p_customer_id => pI.party_id,
2960 p_address_id => pI.party_site_id,
2961 p_contact_id => G_local_org_contact_id,
2962 p_lead_id => null,
2963 p_interest_use_code => l_interest_use_code,
2964 p_check_access_flag => 'N',
2965 p_admin_flag => null,
2966 p_admin_group_id => null,
2967 p_identity_salesforce_id => G_SL_SALESFORCE_ID,
2968 p_access_profile_rec => null,
2969 p_return_status => G_return_status,
2970 p_msg_count => G_MESG_COUNT,
2971 p_msg_data => l_msg_data,
2972 p_interest_out_id => l_interest_out_id) ;
2973 End Loop;
2974
2975 -- For the lines in as_imp_lines_interface
2976 l_ll_index := 0;
2977 FOR LL1 in c_get_lines_1(pI.import_interface_id) LOOP
2978 l_ll_index := l_ll_index + 1;
2979
2980 l_classification_tbl(l_ll_index).customer_id := pI.party_id;
2981 l_classification_tbl(l_ll_index).address_id := pI.party_site_id;
2982 l_classification_tbl(l_ll_index).contact_id := G_LOCAL_ORG_CONTACT_ID;
2983 l_classification_tbl(l_ll_index).interest_type_id :=
2984 LL1.interest_type_id;
2985 l_classification_tbl(l_ll_index).primary_interest_code_id :=
2986 LL1.primary_interest_code_id;
2987 l_classification_tbl(l_ll_index).secondary_interest_code_id :=
2988 LL1.secondary_interest_code_id;
2989
2990 if pI.party_type = 'PERSON' then
2991 l_interest_use_code := 'CONTACT_INTEREST';
2992 elsif pI.party_type = 'ORGANIZATION' then
2993 l_interest_use_code := 'COMPANY_CLASSIFICATION' ;
2994 end if;
2995
2996 AS_INTEREST_PUB.Create_Interest(
2997 p_api_version_number => 2.0 ,
2998 p_init_msg_list => FND_API.G_FALSE,
2999 p_Commit => FND_API.G_FALSE,
3000 p_interest_rec => l_classification_tbl(l_ll_index),
3001 p_customer_id => pI.party_id,
3002 p_address_id => pI.party_site_id,
3003 p_contact_id => G_local_org_contact_id,
3004 p_lead_id => null,
3005 p_interest_use_code => l_interest_use_code,
3006 p_check_access_flag => 'N',
3007 p_admin_flag => null,
3008 p_admin_group_id => null,
3009 p_identity_salesforce_id => G_SL_SALESFORCE_ID,
3010 p_access_profile_rec => null,
3011 p_return_status => G_return_status,
3012 p_msg_count => G_MESG_COUNT,
3013 p_msg_data => l_msg_data,
3014 p_interest_out_id => l_interest_out_id) ;
3015 End Loop;
3016 End do_create_interest;
3017
3018 ----------------------------------------------------------
3019 -- name: do_create_LeadNoteAndContext
3020 -- scope: private
3021 -- calls
3022 -- inserts sales lead Note , Note Contexts for SalesLead
3023 -- and Party
3024 ----------------------------------------------------------
3025 procedure do_create_LeadNoteAndContext(
3026 pI IN OUT NOCOPY leadImpType,
3027 G_return_status OUT NOCOPY varchar2) Is
3028
3029 l_note_context_rec jtf_notes_pub.jtf_note_contexts_rec_type;
3030 l_note_context_rec_tbl jtf_notes_pub.jtf_note_contexts_tbl_type;
3031 l_msg_data VARCHAR2(2000);
3032 l_jtf_note_id NUMBER;
3033
3034 BEGIN
3035
3036 --Assign values to context rec type
3037
3038 l_note_context_rec.NOTE_CONTEXT_TYPE := 'LEAD';
3039 l_note_context_rec.NOTE_CONTEXT_TYPE_ID := pI.sales_lead_id;
3040 l_note_context_rec.LAST_UPDATE_DATE := SYSDATE;
3041 l_note_context_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3042 l_note_context_rec.CREATION_DATE := SYSDATE;
3043 l_note_context_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3044 l_note_context_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
3045
3046 l_note_context_rec_tbl(1) := l_note_context_rec;
3047
3048 -- SOLIN, bug 4227632, use 'PARTY' always
3049 --If pI.party_type = 'ORGANIZATION' then
3050 -- l_note_context_rec.NOTE_CONTEXT_TYPE := 'PARTY_ORGANIZATION';
3051 --else
3052 l_note_context_rec.NOTE_CONTEXT_TYPE := 'PARTY';
3053 --end if;
3054
3055 l_note_context_rec.NOTE_CONTEXT_TYPE_ID := pI.party_id;
3056 l_note_context_rec.LAST_UPDATE_DATE := SYSDATE;
3057 l_note_context_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3058 l_note_context_rec.CREATION_DATE := SYSDATE;
3059 l_note_context_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3060 l_note_context_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
3061
3062 l_note_context_rec_tbl(2) := l_note_context_rec;
3063
3064 -- Call Jtf_notes_pub.create_note()
3065
3066 JTF_NOTES_PUB.Create_Note (
3067 p_parent_note_id => NULL
3068 , p_jtf_note_id => NULL
3069 , p_api_version => 1.0
3070 , p_init_msg_list => 'T'
3071 , p_commit => 'F'
3072 , p_validation_level => 100
3073 , x_return_status => G_return_status
3074 , x_msg_count => G_mesg_count
3075 , x_msg_data => l_msg_data
3076 , p_org_id => NULL
3077 , p_source_object_id => pI.sales_lead_id
3078 , p_source_object_code => 'LEAD'
3079 , p_notes => pI.lead_note
3080 , p_notes_detail => NULL --EMPTY_CLOB()
3081 , p_note_status => NULL
3082 , p_entered_by => FND_GLOBAL.USER_ID
3083 , p_entered_date => SYSDATE
3084 , x_jtf_note_id => l_jtf_note_id
3085 , p_last_update_date => SYSDATE
3086 , p_last_updated_by => FND_GLOBAL.USER_ID
3087 , p_creation_date => SYSDATE
3088 , p_created_by => FND_GLOBAL.USER_ID
3089 , p_last_update_login => FND_GLOBAL.USER_ID
3090 , p_attribute1 => NULL
3091 , p_attribute2 => NULL
3092 , p_attribute3 => NULL
3093 , p_attribute4 => NULL
3094 , p_attribute5 => NULL
3095 , p_attribute6 => NULL
3096 , p_attribute7 => NULL
3097 , p_attribute8 => NULL
3098 , p_attribute9 => NULL
3099 , p_attribute10 => NULL
3100 , p_attribute11 => NULL
3101 , p_attribute12 => NULL
3102 , p_attribute13 => NULL
3103 , p_attribute14 => NULL
3104 , p_attribute15 => NULL
3105 , p_context => NULL
3106 , p_note_type => NVL(pI.note_type,'AS_USER')
3107 , p_jtf_note_contexts_tab => l_note_context_rec_tbl
3108 );
3109
3110 write_log(3, 'do_create_LeadNoteAndContext:End - Note_id - '||to_char(l_jtf_note_id));
3111
3112 End do_create_LeadNoteAndContext;
3113
3114 ----------------------------------------------------------
3115 -- name: do_update_party
3116 -- scope: private
3117 -- calls
3118 ----------------------------------------------------------
3119 procedure do_update_party(
3120 pI IN OUT NOCOPY leadImpType,
3121 G_return_status OUT NOCOPY varchar2)
3122 IS
3123 l_org_rec HZ_PARTY_V2PUB.organization_rec_type;
3124 l_hz_partyNumber number;
3125 l_hz_profile number;
3126 l_msg_data VARCHAR2(2000);
3127 l_per_rec HZ_PARTY_V2PUB.person_rec_type;
3128 l_osysref varchar2(240) := Null;
3129
3130 Begin
3131
3132 -- l_org_rec.party_rec.party_id := pI.hz_partyId;
3133 -- l_per_rec.party_rec.party_id := pI.hz_partyId;
3134
3135 IF (pI.orig_system_reference is not null) or
3136 (pI.orig_system_reference <> FND_API.G_MISS_CHAR)
3137 THEN
3138 -- l_org_rec.party_rec.orig_system_reference:= pI.orig_system_reference;
3139 -- l_per_rec.party_rec.orig_system_reference:= pI.orig_system_reference;
3140 l_osysref := pI.orig_system_reference;
3141 else
3142 -- l_org_rec.party_rec.orig_system_reference := pI.import_interface_id;
3143 -- l_per_rec.party_rec.orig_system_reference := pI.import_interface_id;
3144 l_osysref := pI.import_interface_id;
3145 end if;
3146
3147 -- The following lines were commented as this is erroring out
3148 -- and not supported by hz_party_pub ARHPTYSB.pls 115.72
3149 -- Rashmi Goyal sent an email stating that this is a non updatable column.
3150 -- Until that gets fixed, it was decided by Sr Management that we update
3151 -- the table directly.
3152 update hz_parties
3153 set orig_system_reference = l_osysref
3154 where party_id = pI.party_id;
3155
3156 /* ***
3157 If (pI.party_type = 'ORGANIZATION') then
3158 hz_party_pub.update_organization (
3159 p_api_version => G_api_version,
3160 p_init_msg_list => FND_API.G_FALSE,
3161 p_commit => FND_API.G_FALSE,
3162 p_organization_rec => l_org_rec,
3163 p_party_last_update_date => pI.last_update_date,
3164 x_return_status => G_return_status,
3165 x_msg_count => G_MESG_COUNT,
3166 x_msg_data => l_msg_data,
3167 x_profile_id => l_hz_profile,
3168 p_validation_level => FND_API.G_VALID_LEVEL_FULL
3169 );
3170 elsif (pI.party_type ='PERSON') then
3171 hz_party_pub.update_person (
3172 p_api_version => G_api_version,
3173 p_init_msg_list => FND_API.G_FALSE,
3174 p_commit => FND_API.G_FALSE,
3175 p_person_rec => l_per_rec,
3176 p_party_last_update_date => pI.last_update_date,
3177 x_profile_id => l_hz_profile,
3178 x_return_status => G_return_status,
3179 x_msg_count => G_MESG_COUNT,
3180 x_msg_data => l_msg_data,
3181 p_validation_level => FND_API.G_VALID_LEVEL_FULL
3182 );
3183 else
3184 null;
3185 end if;
3186 *** */
3187 END do_update_party;
3188
3189 ----------------------------------------------------------
3190 -- Name: trans_custkey
3191 -- Scope: Public
3192 -- Select customer key from HZ_PARTIES when osysref matches
3193 ----------------------------------------------------------
3194 function trans_custkey (p_osysref IN Varchar2) Return Varchar2
3195 IS
3196 l_tmp hz_parties.customer_key%type;
3197 BEGIN
3198 Select customer_key into l_tmp
3199 from hz_parties hzp
3200 where hzp.orig_system_reference = p_osysref
3201 and hzp.status = 'A'
3202 and rownum < 2;
3203 Return l_tmp;
3204
3205 exception when others then
3206 Return Null;
3207 END trans_custkey;
3208
3209
3210 ----------------------------------------------------------
3211 -- Name: party_echeck
3212 -- Scope: Public
3213 -- Existence checking for parties in the same batch
3214 ----------------------------------------------------------
3215 procedure party_echeck(p_imp_id IN Number,
3216 p_party_id IN OUT NOCOPY Number,
3217 p_plupd_date IN OUT NOCOPY Date,
3218 p_psite_id IN OUT NOCOPY Number,
3219 p_loc_id IN OUT NOCOPY Number)
3220 IS
3221 Cursor exists_party (p_interface_id Number) IS
3222 select decode(p2.address_key,l.address_key,0,1)+
3223 decode(p2.country,l.country,0,2) match_rank,
3224 p2.party_id, p2.customer_key ,p2.address_key,
3225 p2.country, p2.identifying_address_flag,
3226 max(p2.party_id) mparty_id,
3227 max(p2.party_site_id) party_site_id,
3228 max(p2.location_id) location_id
3229 from (select s.customer_key, p.party_id,
3230 p.last_update_date, s.address_key, s.country,
3231 nvl(ps.identifying_address_flag, 'N')
3232 identifying_address_flag,
3233 ps.location_id, ps.party_site_id
3234 from as_import_interface s, hz_parties p, hz_party_sites ps
3235 where s.load_status = 'NEW'
3236 and s.import_interface_id = p_interface_id
3237 and p.customer_key (+) = s.customer_key
3238 and p.party_type (+) = s.party_type
3239 and p.status (+) = 'A'
3240 and ps.status (+) = 'A'
3241 and ps.party_id (+) = p.party_id) p2,
3242 hz_locations l
3243 where l.country (+) = p2.country
3244 and l.location_id (+) = p2.location_id
3245 group by decode(p2.address_key,l.address_key,0,1)+
3246 decode(p2.country,l.country,0,2),
3247 p2.party_id, p2.identifying_address_flag,
3248 p2.customer_key, p2.address_key, p2.country
3249 order by match_rank asc, identifying_address_flag desc,
3250 party_id desc;
3251
3252 l_country hz_locations.country%Type;
3253 l_akey hz_locations.address_key%Type;
3254 BEGIN
3255 p_plupd_date := Null;
3256 If (p_imp_id is Null) then
3257 return;
3258 End If;
3259 If not (p_party_id is not null and p_psite_id is not null
3260 and p_loc_id is not null)
3261 then
3262 For I in exists_party (p_imp_id) Loop
3263 If I.match_rank = 0 then
3264 if (p_party_id is null or (p_party_id is not null
3265 and p_party_id = I.mparty_id))
3266 then
3267 p_psite_id := nvl(p_psite_id, I.party_site_id);
3268 p_loc_id := nvl(p_loc_id, I.location_id);
3269 end if;
3270 End If;
3271 p_party_id := nvl(p_party_id, I.mparty_id);
3272 l_akey := I.address_key;
3273 l_country := I.country;
3274 Exit;
3275 End Loop;
3276 if (p_loc_id is null) then
3277 select max(location_id) into p_loc_id
3278 from hz_locations
3279 where address_key = l_akey and country = l_country;
3280 End if;
3281 End If;
3282 if (p_party_id is not null) then
3283 select last_update_date into p_plupd_date
3284 from hz_parties
3285 where party_id = p_party_id and rownum = 1;
3286 End If;
3287
3288 exception when others then
3289 Null;
3290 end party_echeck;
3291
3292 ----------------------------------------------------------
3293 -- Name: is_duplicate_lead
3294 -- Scope: Public
3295 -- Sales Lead existence checking
3296 ----------------------------------------------------------
3297 FUNCTION is_duplicate_lead (pI IN leadImpType) RETURN BOOLEAN
3298 IS
3299 l_call_user_hook BOOLEAN;
3300 l_duplicate_flag VARCHAR2(1);
3301 l_return_status VARCHAR2(1);
3302 l_msg_count NUMBER;
3303 l_msg_data VARCHAR2(2000);
3304 BEGIN
3305 -- SOLIN, add customer user hook
3306 -- USER HOOK standard : customer pre-processing section - mandatory
3307 -- l_call_user_hook := JTF_USR_HKS.Ok_to_execute('AS_IMPORT_SL_PVT','IS_DUPLICATE_LEAD','B','C');
3308
3309 IF G_CALL_USER_HOOK
3310 THEN
3311 write_log(3, 'Call user_hook is true');
3312 AS_IMPORT_SL_CUHK.Is_Duplicate_Lead_Pre(
3313 p_api_version_number => 2.0,
3314 p_init_msg_list => FND_API.G_FALSE,
3315 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3316 p_commit => FND_API.G_FALSE,
3317 p_import_interface_id => pI.import_interface_id,
3318 x_duplicate_flag => l_duplicate_flag,
3319 x_return_status => l_return_status,
3320 x_msg_count => l_msg_count,
3321 x_msg_data => l_msg_data);
3322
3323 write_log(3, 'x_duplicate_flag=' || l_duplicate_flag);
3324 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3325 RAISE FND_API.G_EXC_ERROR;
3326 END IF;
3327
3328 IF l_duplicate_flag = 'Y' THEN
3329 RETURN TRUE;
3330 ELSE
3331 RETURN FALSE;
3332 END IF;
3333 ELSE
3334 write_log(3, 'Call user_hook is false');
3335 END IF;
3336 -- end SOLIN
3337
3338 -- return FALSE for current implementation
3339 RETURN FALSE;
3340 END is_duplicate_lead;
3341
3342
3343 ----------------------------------------------------------
3344 -- Name: TCA_DQM_processing
3345 -- Scope: Private
3346 -- Using DQM check for existance of TCA entities
3347 -- If they do not exist in TCA repository then create new
3348 ----------------------------------------------------------
3349
3350 procedure TCA_DQM_processing (I IN OUT NOCOPY leadImpType, l_create_party OUT NOCOPY VARCHAR2, l_create_party_site OUT NOCOPY VARCHAR2,
3351 l_create_contact OUT NOCOPY VARCHAR2, -- l_create_contact_point OUT NOCOPY VARCHAR2,
3352 l_create_location OUT NOCOPY VARCHAR2, ld_phone OUT NOCOPY VARCHAR2,
3353 ld_fax OUT NOCOPY VARCHAR2, ld_email OUT NOCOPY VARCHAR2, ld_url OUT NOCOPY VARCHAR2)
3354 IS
3355
3356 -- Declare Variables for passing search criteria
3357
3358 -- Pass Party search criteria in this variable
3359 party_cond HZ_PARTY_SEARCH.PARTY_SEARCH_REC_TYPE;
3360 -- Pass Party Site search criteria in this variable
3361 party_site_cond HZ_PARTY_SEARCH.PARTY_SITE_LIST;
3362 -- Pass Contact search criteria in this variable
3363 contact_cond HZ_PARTY_SEARCH.CONTACT_LIST;
3364 -- Pass Contact Point search criteria in this variable
3365 contact_point_cond HZ_PARTY_SEARCH.CONTACT_POINT_LIST;
3366
3367 -- The Match Rule to use for the dup identification.
3368 l_rule_id NUMBER;
3369
3370 -- The Search Context ID returned by the API.
3371 l_search_context_id NUMBER;
3372
3373 -- Other OUT parameters returned by the API.
3374 l_return_status VARCHAR2(1);
3375 l_msg_count NUMBER;
3376 l_msg_data VARCHAR2(2000);
3377
3378 -- API also returns the number of matches.
3379 l_num_matches NUMBER;
3380
3381 -- Local variables
3382 l_org_contact_id NUMBER(15);
3383 l_party_id NUMBER(15);
3384 l_party_site_id NUMBER(15);
3385 l_contact_point_id NUMBER(15);
3386 l_creation_date DATE;
3387 l_score NUMBER;
3388
3389 l_dup_phone varchar2(1):= 'N';
3390 l_dup_email varchar2(1):= 'N';
3391 l_dup_fax varchar2(1):= 'N';
3392 l_dup_url varchar2(1):= 'N';
3393 l_contact_provided varchar2(1) := 'N';
3394
3395 l_orig_sys_party_found VARCHAR2(1);
3396 l_identifying_addr_flag VARCHAR2(1);
3397 l_index NUMBER(2);
3398
3399 -- SOLIN, BUG 3528579
3400 l_activate_flag VARCHAR2(1);
3401 l_status VARCHAR2(1);
3402 l_object_version_number NUMBER;
3403 l_profile_id NUMBER;
3404 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
3405 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
3406 -- SOLIN, BUG 3528579 end
3407
3408 --l_restrict_sql VARCHAR2(4000);
3409
3410 -- Cursor for getting matched party_id from hz_matched_parties_gt
3411 CURSOR C_matched_party(p_party_type VARCHAR2)
3412 IS
3413 SELECT HZMP.party_id, score, hzp.creation_date
3414 FROM HZ_MATCHED_PARTIES_GT hzmp, HZ_PARTIES hzp
3415 WHERE search_context_id = l_search_context_id
3416 AND hzp.party_id = hzmp.party_id
3417 AND hzp.party_type = p_party_type
3418 AND nvl(hzp.status,'A') = 'A' --bug# 3319259
3419 ORDER BY score desc, hzp.creation_date desc;
3420
3421 -- SOLIN, Bug 3528579
3422 -- Cursor for getting matched party_id, ignore party status
3423 CURSOR C_matched_party2(p_party_type VARCHAR2)
3424 IS
3425 SELECT HZMP.party_id, score, hzp.creation_date, hzp.status
3426 , hzp.object_version_number
3427 FROM HZ_MATCHED_PARTIES_GT hzmp, HZ_PARTIES hzp
3428 WHERE search_context_id = l_search_context_id
3429 AND hzp.party_id = hzmp.party_id
3430 AND hzp.party_type = p_party_type
3431 ORDER BY score desc, hzp.creation_date desc;
3432
3433 -- Cursor for getting matched party_site from hz_matched_party_sites_gt
3434 CURSOR C_matched_party_sites
3435 IS
3436 SELECT hzmps.party_id, hzmps.party_site_id, score , hzps.creation_date
3437 FROM hz_matched_party_sites_gt hzmps, hz_party_sites hzps
3438 WHERE search_context_id = l_search_context_id
3439 AND hzps.party_site_id = hzmps.party_site_id
3440 AND hzps.party_id = hzmps.party_id
3441 AND nvl(hzps.status,'A') = 'A' --bug# 3319259
3442 ORDER BY score desc, hzps.creation_date desc;
3443
3444 -- Cursor for getting matched contacts from hz_matched_contacts_gt
3445 CURSOR C_matched_contacts
3446 IS
3447 SELECT hzmc.party_id, hzmc.org_contact_id, score , hzoc.creation_date
3448 FROM hz_matched_contacts_gt hzmc, hz_org_contacts hzoc
3449 WHERE search_context_id = l_search_context_id
3450 AND hzmc.org_contact_id = hzoc.org_contact_id
3451 AND nvl(hzoc.status,'A') = 'A' --bug# 3319259
3452 ORDER BY score desc, hzoc.creation_date desc;
3453
3454 -- Cursor for getting matched contact_point from hz_matched_cpts_gt
3455 CURSOR C_matched_contact_points(cp_type VARCHAR2, p_plt VARCHAR2)
3456 IS
3457 SELECT hzmcp.party_id, hzmcp.contact_point_id, score , hzcp.creation_date
3458 FROM hz_matched_cpts_gt hzmcp, hz_contact_points hzcp
3459 WHERE search_context_id = l_search_context_id
3460 AND hzmcp.contact_point_id = hzcp.contact_point_id
3461 AND hzcp.contact_point_type = cp_type
3462 AND nvl(hzcp.phone_line_type,'xx') = nvl(p_plt,'xx')
3463 AND nvl(hzcp.status,'A') = 'A' --bug# 3319259
3464 ORDER BY score desc, hzcp.creation_date desc;
3465
3466 -- Cursor for getting contact_party_id and rel_party_id
3467 CURSOR C_get_contact_info
3468 IS
3469 SELECT decode(subject_type,'PERSON',subject_id, object_id) contact_party_id, party_id
3470 FROM hz_org_contacts hzoc, hz_relationships hzr
3471 WHERE hzoc.org_contact_id = l_org_contact_id
3472 AND hzr.relationship_id = hzoc.party_relationship_id
3473 and hzr.relationship_code = 'CONTACT_OF';
3474
3475 -- Cursor for Orig_system_reference dup check
3476 CURSOR c_check_orig_sys_ref(p_orig_system_ref varchar)
3477 IS
3478 SELECT party_id
3479 FROM hz_parties hzp
3480 WHERE hzp.orig_system_reference = p_orig_system_ref
3481 AND nvl(hzp.status,'A') = 'A';
3482 -- AND rownum < 2;
3483
3484 -- Cursor for getting party_site_id given the party_id
3485 CURSOR C_get_party_site_id(p_party_id number)
3486 IS
3487 SELECT party_site_id, nvl(identifying_address_flag,'N')
3488 FROM hz_party_sites
3489 WHERE party_id = p_party_id
3490 AND nvl(start_date_active,sysdate) <= sysdate
3491 AND nvl(end_date_active,sysdate) >= sysdate
3492 ORDER BY nvl(identifying_address_flag,'N') DESC;
3493
3494 -- Cursor for getting rel_party_id
3495 CURSOR C_get_rel_party_id(p_contact_party_id number, p_party_id number)
3496 IS
3497 SELECT party_id
3498 FROM hz_relationships hzr
3499 WHERE hzr.relationship_code in ('CONTACT_OF','EMPLOYEE_OF')
3500 AND subject_id in (p_contact_party_id, p_party_id)
3501 AND object_id in (p_contact_party_id, p_party_id)
3502 AND hzr.status = 'A'
3503 AND nvl(hzr.start_date,sysdate) <= sysdate
3504 AND nvl(hzr.end_date,sysdate) >= sysdate;
3505
3506 BEGIN
3507
3508 l_create_party := 'N';
3509 -- l_create_contact_point := 'N';
3510 l_create_party_site := 'N';
3511 l_create_location := 'N';
3512 l_create_contact := 'N';
3513
3514 l_dup_phone := 'N';
3515 l_dup_email := 'N';
3516 l_dup_fax := 'N';
3517 l_dup_url := 'N';
3518
3519
3520 -- Data Assignment to DQM datatypes
3521
3522 -- 1. Pass Party search criteria in party_cond
3523
3524 party_cond.party_type := I.party_type;
3525 IF I.party_type = 'ORGANIZATION' THEN
3526 party_cond.party_name := I.customer_name;
3527 ELSIF I.party_type = 'PERSON' THEN
3528 party_cond.party_name := I.first_name||' '||I.last_name;
3529 END IF;
3530 party_cond.party_all_names := party_cond.party_name;
3531
3532 party_cond.party_number := I.party_number;
3533 party_cond.duns_number_c := I.duns_number_c;
3534 party_cond.tax_reference := I.tax_reference;
3535 party_cond.person_name := I.first_name||' '||I.last_name;
3536 party_cond.person_first_name := I.first_name;
3537 party_cond.person_last_name := I.last_name;
3538 party_cond.person_initials := I.person_initials;
3539 party_cond.person_name := I.first_name||' '||I.last_name;
3540 party_cond.sic_code := I.sic_code;
3541 party_cond.sic_code_type := I.sic_code_type;
3542 party_cond.category_code := I.customer_category_code;
3543 party_cond.year_established := I.year_established;
3544 party_cond.employees_total := I.num_of_employees;
3545 party_cond.curr_fy_potential_revenue := I.potential_revenue_curr_fy;
3546 party_cond.next_fy_potential_revenue := I.potential_revenue_next_fy;
3547 party_cond.tax_reference := I.tax_reference;
3548 party_cond.tax_name := I.tax_name;
3549 party_cond.salutation := I.salutation;
3550 party_cond.organization_name_phonetic := I.organization_name_phonetic;
3551
3552 -- 2. Pass Party Site search criteria in party_site_cond
3553 party_site_cond(1).address1 := I.address1;
3554 party_site_cond(1).address2 := I.address2;
3555 party_site_cond(1).address3 := I.address3;
3556 party_site_cond(1).address4 := I.address4;
3557 party_site_cond(1).country := I.country;
3558 party_site_cond(1).city := I.city;
3559 party_site_cond(1).province := I.province;
3560 party_site_cond(1).postal_code := I.postal_code;
3561 -- SOLIN, bug 4633401
3562 -- add space between address?
3563 party_site_cond(1).address := I.address1 || ' ' || I.address2 || ' '
3564 || I.address3 || ' ' || I.address4;
3565 -- SOLIN, end
3566 party_site_cond(1).state := I.state;
3567 party_site_cond(1).county := I.county;
3568 party_site_cond(1).party_site_name := I.party_site_name;
3569 party_site_cond(1).party_site_number := I.party_site_number;
3570 --party_site_cond(1).floor := I.floor;
3571 --party_site_cond(1).house_number := I.house_number;
3572 --party_site_cond(1).po_box_number := I.po_box_number;
3573 party_site_cond(1).position := I.position;
3574 party_site_cond(1).postal_plus4_code := I.postal_plus4_code;
3575 --party_site_cond(1).street := I.street;
3576 --party_site_cond(1).street_suffix := I.street_suffix;
3577 --party_site_cond(1).street_number := I.street_number;
3578 --party_site_cond(1).suite := I.suite;
3579 party_site_cond(1).address_effective_date := I.address_effective_date;
3580 party_site_cond(1).mailstop := I.mailstop;
3581 party_site_cond(1).address_lines_phonetic := I.address_lines_phonetic;
3582
3583 -- 3. Pass Contact search criteria in contact_cond
3584 contact_cond(1).contact_name := I.first_name||' '||I.last_name;
3585 contact_cond(1).contact_number := I.contact_number;
3586 contact_cond(1).person_name := I.first_name||' '||I.last_name;
3587 contact_cond(1).person_first_name := I.first_name;
3588 contact_cond(1).person_last_name := I.last_name;
3589 contact_cond(1).person_initials := I.person_initials;
3590 contact_cond(1).job_title := I.job_title;
3591 contact_cond(1).job_title_code := I.job_title_code;
3592 contact_cond(1).mail_stop := I.mail_stop;
3593 contact_cond(1).content_source_type := I.content_source_type;
3594 contact_cond(1).person_first_name_phonetic := I.person_first_name_phonetic;
3595 contact_cond(1).person_last_name_phonetic := I.person_last_name_phonetic;
3596 contact_cond(1).person_name_suffix := I.person_name_suffix;
3597 contact_cond(1).person_previous_last_name := I.person_previous_last_name;
3598
3599 -- 4. Pass Contact Point search criteria in contact_point_cond
3600 l_index := 1;
3601 IF I.email_address IS NOT NULL THEN
3602 contact_point_cond(l_index).CONTACT_POINT_TYPE := 'EMAIL';
3603 contact_point_cond(l_index).EMAIL_ADDRESS := I.email_address;
3604 contact_point_cond(l_index).EMAIL_FORMAT := I.email_format;
3605 l_index := l_index + 1;
3606 END IF;
3607 IF I.phone_number IS NOT NULL THEN
3608 contact_point_cond(l_index).CONTACT_POINT_TYPE := 'PHONE';
3609 contact_point_cond(l_index).PHONE_NUMBER := I.phone_number;
3610 contact_point_cond(l_index).PHONE_LINE_TYPE := nvl(I.phone_type,'GEN');
3611 contact_point_cond(l_index).PHONE_AREA_CODE := I.area_code;
3612 contact_point_cond(l_index).PHONE_EXTENSION := I.extension;
3613 contact_point_cond(l_index).PHONE_COUNTRY_CODE := I.phone_country_code;
3614
3615 contact_point_cond(l_index).raw_phone_number:= I.phone_country_code||I.area_code||I.phone_number;
3616 contact_point_cond(l_index).flex_format_phone_number:= I.phone_country_code||I.area_code||I.phone_number;
3617
3618 l_index := l_index + 1;
3619 END IF;
3620 IF I.fax_number IS NOT NULL THEN
3621 contact_point_cond(l_index).CONTACT_POINT_TYPE := 'PHONE';
3622 contact_point_cond(l_index).PHONE_LINE_TYPE := 'FAX';
3623 contact_point_cond(l_index).PHONE_NUMBER := I.fax_number;
3624 contact_point_cond(l_index).PHONE_AREA_CODE := I.fax_area_code;
3625 contact_point_cond(l_index).PHONE_EXTENSION := I.fax_extension;
3626 contact_point_cond(l_index).PHONE_COUNTRY_CODE := I.fax_country_code;
3627
3628 --bmuthukr modified the following code to pass fax# details to fix bug 3748665
3629 --contact_point_cond(l_index).raw_phone_number:= I.phone_country_code||I.area_code||I.phone_number;
3630 --contact_point_cond(l_index).flex_format_phone_number:= I.phone_country_code||I.area_code||I.phone_number;
3631 contact_point_cond(l_index).raw_phone_number:= I.fax_country_code||I.fax_area_code||I.fax_number;
3632 contact_point_cond(l_index).flex_format_phone_number:= I.fax_country_code||I.fax_area_code||I.fax_number;
3633 --Ends changes..
3634
3635 l_index := l_index + 1;
3636 END IF;
3637 IF I.url IS NOT NULL THEN
3638 contact_point_cond(l_index).CONTACT_POINT_TYPE := 'WEB';
3639 contact_point_cond(l_index).WEB_TYPE := 'http';
3640 contact_point_cond(l_index).URL := I.url;
3641 END IF;
3642
3643 IF I.party_id IS NULL THEN --Skip party existance check if provided in import
3644
3645 --Orig System Regerence check
3646 l_orig_sys_party_found := 'N';
3647
3648 IF I.orig_system_reference IS NOT NULL THEN
3649
3650 OPEN c_check_orig_sys_ref(I.orig_system_reference);
3651 FETCH c_check_orig_sys_ref INTO l_party_id;
3652 IF c_check_orig_sys_ref%NOTFOUND THEN
3653 l_orig_sys_party_found := 'N';
3654 ELSE
3655 l_orig_sys_party_found := 'Y';
3656 END IF;
3657 CLOSE c_check_orig_sys_ref;
3658 /*
3659 l_restrict_sql := null;
3660 FOR osr IN c_check_orig_sys_ref(I.orig_system_reference) LOOP
3661 l_restrict_sql := l_restrict_sql || to_char(osr.party_id) ||',';
3662 END LOOP;
3663
3664 IF l_restrict_sql IS NULL THEN
3665 l_orig_sys_party_found := 'N';
3666 ELSE
3667 l_orig_sys_party_found := 'Y';
3668 l_restrict_sql := ' party_id in ('|| l_restrict_sql ||'0) ';
3669 END IF;
3670 */
3671 END IF;
3672
3673 l_party_id := null;
3674
3675 ----- Begin PARTY SEARCH -----
3676 -- Get rule_id from profile
3677 IF I.party_type = 'ORGANIZATION' THEN
3678 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_PARTY'));
3679 ELSIF I.party_type = 'PERSON' THEN
3680 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_PERSON'));
3681 ELSE
3682 --else bad party_type
3683 AS_UTILITY_PVT.Set_Message(
3684 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
3685 p_msg_name => 'AS_INVALID_PARTY_TYPE',
3686 p_token1 => 'VALUE',
3687 p_token1_value => I.party_type);
3688 write_log (3, 'Party_type is invalid');
3689 RAISE FND_API.G_EXC_ERROR;
3690 END IF;
3691
3692 write_log(3,'#1 :: Calling FIND_PARTIES with OSR found = '||l_orig_sys_party_found);
3693 write_log(3,'rule_id '||to_char(l_rule_id));
3694
3695 IF l_orig_sys_party_found = 'N' THEN
3696 -- Full search
3697 HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond, NULL,
3698 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3699 ELSE
3700 -- l_restrict_sql := ' party_id in (select party_id from hz_parties where orig_system_reference = '''||I.orig_system_reference||''')';
3701 write_log(3,'In Restrict sql');
3702
3703 -- Restricted search by passing p_restrict_sql
3704 HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond,
3705 '/* SELECTIVE */ party_id in (select party_id from hz_parties where ORIG_SYSTEM_REFERENCE = '''||I.orig_system_reference||''') ',
3706 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3707
3708 IF l_num_matches = 0 THEN
3709 -- Full search
3710 write_log(3,'performing full search');
3711 HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond, NULL,
3712 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3713 END IF;
3714 END IF;
3715
3716 IF l_return_status <> FND_API.g_ret_sts_success THEN
3717 RAISE FND_API.g_exc_error;
3718 END IF;
3719
3720 write_log(3,'After find_parties matches '||to_char(l_num_matches));
3721
3722 IF l_num_matches > 0 THEN
3723 -- A possible duplicate has been found.
3724 -- Get the party id the matched parties having highest score.
3725 -- SOLIN, Bug 3528579
3726 l_activate_flag := NVL(FND_PROFILE.value('AS_ACTIVATE_PARTIES_FROM_IMPORT'), 'N');
3727 IF l_activate_flag = 'N'
3728 THEN
3729 OPEN C_matched_party(I.party_type);
3730 FETCH C_matched_party INTO l_party_id, l_score, l_creation_date;
3731 CLOSE C_matched_party;
3732 ELSE
3733 OPEN C_matched_party2(I.party_type);
3734 FETCH C_matched_party2 INTO l_party_id, l_score, l_creation_date,
3735 l_status, l_object_version_number;
3736 CLOSE C_matched_party2;
3737
3738 -- activate the party if it's inactive
3739 IF l_status = 'I'
3740 THEN
3741 write_log(3, 'Activating party ' || l_party_id);
3742 IF I.party_type = 'ORGANIZATION'
3743 THEN
3744 l_organization_rec.party_rec.party_id := l_party_id;
3745 l_organization_rec.party_rec.status := 'A';
3746 HZ_PARTY_V2PUB.update_organization(
3747 p_init_msg_list => FND_API.G_FALSE,
3748 p_organization_rec => l_organization_rec,
3749 p_party_object_version_number => l_object_version_number,
3750 x_profile_id => l_profile_id,
3751 x_return_status => l_return_status,
3752 x_msg_count => l_MSG_COUNT,
3753 x_msg_data => l_msg_data
3754 );
3755 ELSIF I.party_type = 'PERSON'
3756 THEN
3757 l_person_rec.party_rec.party_id := l_party_id;
3758 l_person_rec.party_rec.status := 'A';
3759 HZ_PARTY_V2PUB.update_person(
3760 p_init_msg_list => FND_API.G_FALSE,
3761 p_person_rec => l_person_rec,
3762 p_party_object_version_number => l_object_version_number,
3763 x_profile_id => l_profile_id,
3764 x_return_status => l_return_status,
3765 x_msg_count => l_MSG_COUNT,
3766 x_msg_data => l_msg_data
3767 );
3768 END IF;
3769 write_log(3, 'l_return_status=' || l_return_status
3770 || ',l_msg_data=' || l_msg_data);
3771 END IF; -- l_status = 'I'
3772 END IF; -- l_activate_flag = 'N'
3773 write_log(3, 'Matched party - '||to_char(l_party_id)||' score '
3774 ||to_char(l_score)||' created '||to_char(l_creation_date)
3775 || 'status=' || l_status || ' activate?' || l_activate_flag);
3776 -- SOLIN, Bug 3528579 end
3777 I.party_id := l_party_id; --assign the matched party_id
3778 IF I.party_id IS NOT NULL THEN
3779 l_create_party := 'N';
3780 ELSE
3781 l_create_party := 'Y';
3782 END IF;
3783 ELSE
3784 write_log(3,'No party match found !');
3785 l_create_party := 'Y';
3786 END IF;
3787
3788 ----- End PARTY SEARCH -----
3789 ELSE -- party_id Provided
3790 l_create_party := 'N';
3791 write_log(3,'Skip party echeck - Party_id provided - '||to_char(I.party_id));
3792 END IF; --If party_id IS NULL
3793
3794 ----- Begin PARTY SITE SEARCH -----
3795
3796 IF I.party_id IS NOT NULL THEN -- do party_site search based on that party_id
3797 -- get party_site_id in case no address info is provided bug# 2760262
3798 IF I.party_site_id IS NULL AND I.address1 IS NULL THEN
3799 OPEN C_get_party_site_id(I.party_id);
3800 FETCH C_get_party_site_id INTO I.party_site_id, l_identifying_addr_flag;
3801 CLOSE C_get_party_site_id;
3802 END IF;
3803
3804 IF I.party_site_id IS NULL AND I.address1 IS NOT NULL THEN
3805 write_log(3,'#2 :: Calling GET_MATCHING_PARTY_SITES with party_id: '||to_char(I.party_id));
3806
3807 -- SOLIN, Bug 4942209
3808 -- create a new rule profile for DQM party site match
3809 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_PARTY_SITE'));
3810 HZ_PARTY_SEARCH.get_matching_party_sites ('T',l_rule_id, I.party_id, party_site_cond,
3811 contact_point_cond, l_search_context_id, l_return_status, l_msg_count, l_msg_data);
3812
3813 IF l_return_status <> FND_API.g_ret_sts_success THEN
3814 RAISE FND_API.g_exc_error;
3815 END IF;
3816
3817 OPEN C_matched_party_sites;
3818 FETCH C_matched_party_sites INTO l_party_id, l_party_site_id, l_score, l_creation_date;
3819 IF C_matched_party_sites%FOUND THEN
3820 write_log(3,'Matched party_site - '||to_char(l_party_site_id)|| ' score '||to_char(l_score)||' created '||to_char(l_creation_date));
3821 I.party_site_id := l_party_site_id;
3822 SELECT location_id INTO I.location_ID
3823 FROM hz_party_sites
3824 WHERE party_site_id = I.party_site_id;
3825 l_create_party_site := 'N';
3826 l_create_location := 'N';
3827 ELSE
3828 write_log(3,'No party_site match found !');
3829 l_create_party_site := 'Y';
3830 l_create_location := 'Y';
3831 END IF;
3832 CLOSE C_matched_party_sites;
3833
3834 ELSE -- IF party_site_id is not null
3835 IF I.location_id IS NULL AND I.party_site_id IS NOT NULL THEN
3836 SELECT location_id INTO I.location_ID
3837 FROM hz_party_sites
3838 WHERE party_site_id = I.party_site_id;
3839 END IF;
3840 l_create_party_site := 'N';
3841 l_create_location := 'N';
3842 write_log(3,'Skip party_site echeck - party_site_id provided- '||to_char(I.party_site_id));
3843 END IF;
3844 ELSE --if party_id is null then
3845 l_create_party_site := 'Y';
3846 l_create_location := 'Y';
3847 END IF;
3848
3849 ----- End PARTY SITE SEARCH -----
3850
3851 ----- Begin CONTACT SEARCH -----
3852
3853 IF party_cond.party_type = 'ORGANIZATION' THEN --1
3854 IF I.contact_party_id IS NULL THEN --2
3855 IF I.first_name IS NOT NULL AND I.last_name IS NOT NULL THEN --3
3856 IF I.party_id IS NOT NULL THEN --4
3857 write_log(3,'#3 :: Calling GET_MATCHING_CONTACTS with party_id: '||to_char(I.party_id));
3858
3859 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_CONTACT'));
3860 HZ_PARTY_SEARCH.get_matching_contacts('T',l_rule_id, I.party_id, contact_cond, contact_point_cond,
3861 l_search_context_id, l_return_status, l_msg_count, l_msg_data);
3862
3863 IF l_return_status <> FND_API.g_ret_sts_success THEN
3864 RAISE FND_API.g_exc_error;
3865 END IF;
3866
3867 OPEN C_matched_contacts;
3868 FETCH C_matched_contacts INTO l_party_id, l_org_contact_id, l_score, l_creation_date;
3869 IF C_matched_contacts%FOUND THEN
3870 OPEN C_get_contact_info;
3871 FETCH C_get_contact_info INTO I.contact_party_id, I.rel_party_id;
3872 CLOSE C_get_contact_info;
3873 IF I.party_id is null THEN
3874 I.rel_party_id := null; --create new relationship as reusing contact
3875 END IF;
3876 write_log(3,'Matched contact_party_id - '||to_char(I.contact_party_id)|| ' score '||to_char(l_score)||' created '||to_char(l_creation_date));
3877 l_create_contact := 'N';
3878 ELSE
3879 l_create_contact := 'Y';
3880 write_log(3,'No contact match found !');
3881 END IF;
3882 CLOSE C_matched_contacts;
3883 END IF; --4 party_id is not null
3884
3885 IF I.contact_party_id IS NULL THEN --4
3886 -- Blind search for matching person in TCA
3887 write_log(3, 'Contact blind search begin: using FIND_PARTIES (Person)');
3888
3889 --Reset values for person search
3890 party_cond.party_type := 'PERSON';
3891 party_cond.party_name := I.first_name||' '||I.last_name;
3892
3893 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_PERSON'));
3894 HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond, NULL,
3895 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3896
3897 IF l_num_matches > 0 THEN --match found
3898 OPEN C_matched_party('PERSON');
3899 FETCH C_matched_party INTO l_party_id, l_score, l_creation_date;
3900 CLOSE C_matched_party;
3901 write_log(3, 'Matched person - '||to_char(l_party_id)||' score '||to_char(l_score)||' created '||to_char(l_creation_date));
3902 I.contact_party_id := l_party_id; --assign the matched party_id to contact_party_id
3903 IF I.contact_party_id IS NOT NULL THEN
3904 l_create_contact := 'N';
3905 ELSE
3906 l_create_contact := 'Y';
3907 END IF;
3908 ELSE
3909 write_log(3,'No person match found !');
3910 l_create_contact := 'Y';
3911 END IF;
3912
3913 END IF; --if party_id is not null --4
3914
3915 ELSE --First name, Last name not provided --3
3916
3917 write_log(3, 'Contact Firstname, Lastname not provided');
3918 l_create_contact := 'N';
3919 END IF; --3
3920
3921 ELSE -- if I.contact_party_id is not null --2
3922 IF I.rel_party_id IS NULL and I.party_id IS NOT NULL THEN
3923 OPEN C_get_rel_party_id(I.contact_party_id, I.party_id);
3924 FETCH C_get_rel_party_id INTO I.rel_party_id;
3925 CLOSE C_get_rel_party_id;
3926 END IF;
3927 l_create_contact := 'N';
3928 l_contact_provided := 'Y';
3929 write_log(3,'Skip contact echeck- provided contact_party_id- '||to_char(I.contact_party_id));
3930 END IF; --2
3931 END IF; --1
3932
3933 ----- End CONTACT SEARCH -----
3934
3935 ----- Begin CONTACT POINT SEARCH -----
3936
3937 IF (I.party_type = 'ORGANIZATION' and I.rel_party_id is not null) or
3938 (I.party_type <> 'ORGANIZATION' and I.party_id is not null) THEN
3939 IF l_contact_provided = 'N' AND I.first_name IS NOT NULL AND I.last_name IS NOT NULL THEN
3940 IF I.email_address IS NOT NULL or I.phone_number IS NOT NULL THEN
3941 write_log(3,'#4 :: Calling GET_MATCHING_CONTACT_POINTS with party_id: '||to_char(I.party_id));
3942 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_CONTACT'));
3943 HZ_PARTY_SEARCH.get_matching_contact_points('T',l_rule_id, I.party_id, contact_point_cond, l_search_context_id, l_return_status, l_msg_count, l_msg_data);
3944
3945 IF l_return_status <> FND_API.g_ret_sts_success THEN
3946 RAISE FND_API.g_exc_error;
3947 END IF;
3948
3949 -- Check EMAIL
3950 IF I.email_address IS NOT NULL THEN
3951 OPEN C_matched_contact_points('EMAIL',null);
3952 FETCH C_matched_contact_points INTO l_party_id, l_contact_point_id, l_score, l_creation_date;
3953 IF C_matched_contact_points%FOUND THEN
3954 write_log(3,'EMAIL found - '||to_char(l_contact_point_id)|| ' score '||to_char(l_score)||' created '||to_char(l_creation_date)||to_char(l_search_context_id));
3955 l_dup_email := 'Y';
3956 ELSE
3957 l_dup_email := 'N';
3958 write_log(3,'EMAIL NOT match found !');
3959 END IF;
3960 CLOSE C_matched_contact_points;
3961 END IF; --EMAIL
3962
3963 -- Check PHONE
3964 IF I.phone_number IS NOT NULL THEN
3965 OPEN C_matched_contact_points('PHONE','GEN');
3966 FETCH C_matched_contact_points INTO l_party_id, l_contact_point_id, l_score, l_creation_date;
3967 IF C_matched_contact_points%FOUND THEN
3968 write_log(3,'PHONE found - '||to_char(l_contact_point_id)|| ' score '||to_char(l_score)||' created '||to_char(l_creation_date)||to_char(l_search_context_id));
3969 I.phone_id := l_contact_point_id;
3970 l_dup_phone := 'Y';
3971 ELSE
3972 write_log(3,'PHONE NOT match found !');
3973 l_dup_phone := 'N';
3974 END IF;
3975 CLOSE C_matched_contact_points;
3976 END IF; --PHONE
3977
3978 -- Check FAX
3979 IF I.fax_number IS NOT NULL THEN
3980 OPEN C_matched_contact_points('PHONE','FAX');
3981 FETCH C_matched_contact_points INTO l_party_id, l_contact_point_id, l_score, l_creation_date;
3982 IF C_matched_contact_points%FOUND THEN
3983 write_log(3,'FAX found - '||to_char(l_contact_point_id)|| ' score '||to_char(l_score)||' created '||to_char(l_creation_date)||to_char(l_search_context_id));
3984 l_dup_fax := 'Y';
3985 ELSE
3986 write_log(3,'FAX NOT match found !');
3987 l_dup_fax := 'N';
3988 END IF;
3989 CLOSE C_matched_contact_points;
3990 END IF; --FAX
3991
3992 -- Check URL
3993 IF I.url IS NOT NULL THEN
3994 OPEN C_matched_contact_points('WEB',null);
3995 FETCH C_matched_contact_points INTO l_party_id, l_contact_point_id, l_score, l_creation_date;
3996 IF C_matched_contact_points%FOUND THEN
3997 write_log(3,'URL found - '||to_char(l_contact_point_id)|| ' score '||to_char(l_score)||' created '||to_char(l_creation_date)||to_char(l_search_context_id));
3998 l_dup_url := 'Y';
3999 ELSE
4000 write_log(3,'URL NOT match found !');
4001 l_dup_url := 'N';
4002 END IF;
4003 CLOSE C_matched_contact_points;
4004 END IF; --URL
4005 END IF;
4006 END IF;
4007 END IF;
4008
4009 ld_phone := l_dup_phone;
4010 ld_email := l_dup_email;
4011 ld_fax := l_dup_fax;
4012 ld_url := l_dup_url;
4013
4014 ----- End CONTACT POINT SEARCH -----
4015
4016 END TCA_DQM_processing;
4017
4018
4019 ----------------------------------------------------------
4020 -- Name: do_lead_import
4021 -- Scope: Public
4022 -- Sales Lead Import logic implemented
4023 -- Calls all other provate procedures and functions
4024 ----------------------------------------------------------
4025 procedure do_lead_import(
4026 -- errbuf varchar2,
4027 -- errcode varchar2,
4028 p_source_system in varchar2,
4029 p_debug_msg_flag in varchar2 := 'N',
4030 p_parent_request_id in number,
4031 p_child_request_id in number,
4032 p_resource_id in number, -- SOLIN, bug 4702335
4033 p_group_id in number -- SOLIN, bug 4702335
4034 )
4035 IS
4036 l_hz_conpartyid number;
4037 l_batch_unexp number := 0;
4038 l_batch_err number := 0;
4039 l_batch_succ number := 0;
4040 l_batch_size number := 0;
4041 l_duplicate_lead varchar2(1):= 'U';
4042 l_source_system number := 0;
4043 l_party_id number;
4044 l_orig_sys_party_found varchar2(1) default NULL ;
4045 l_return_status VARCHAR2(1);
4046 x_return_status VARCHAR2(1);
4047 l_msg_count NUMBER;
4048 l_msg_data VARCHAR2(2000);
4049
4050 p_dup_phone varchar2(1):= 'N';
4051 p_dup_email varchar2(1):= 'N';
4052 p_dup_fax varchar2(1):= 'N';
4053 p_dup_url varchar2(1):= 'N';
4054
4055 l_lead_engines_out_rec AS_SALES_LEADS_PUB.LEAD_ENGINES_OUT_Rec_Type;
4056 l_error_type varchar2(100);
4057 l_group_id number;
4058
4059 -- Bugfix for concurrency control
4060 cursor c_main (l_parent_request_id number, l_child_request_id number) is
4061 select * from as_import_interface --as_imp_sl_v
4062 where request_id = l_parent_request_id
4063 and child_request_id = l_child_request_id
4064 and load_status = 'RUNNING'
4065 and source_system = p_source_system;
4066
4067 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
4068 SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.ASSIGN_TO_SALESFORCE_ID,
4069 SL.ASSIGN_TO_PERSON_ID, SL.ASSIGN_SALES_GROUP_ID,
4070 SL.QUALIFIED_FLAG, SL.PARENT_PROJECT,
4071 SL.CHANNEL_CODE, SL.DECISION_TIMEFRAME_CODE, SL.BUDGET_AMOUNT,
4072 SL.BUDGET_STATUS_CODE, SL.SOURCE_PROMOTION_ID, SL.STATUS_CODE,
4073 SL.REJECT_REASON_CODE, SL.LEAD_RANK_ID,
4074 -- swkhanna 5/24/02
4075 SL.LEAD_DATE, SL.SOURCE_SYSTEM, SL.COUNTRY
4076 FROM AS_SALES_LEADS SL
4077 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
4078
4079 CURSOR C_Get_SLAESFORCE(C_User_Id NUMBER) IS
4080 SELECT JS.RESOURCE_ID
4081 FROM JTF_RS_RESOURCE_EXTNS JS
4082 WHERE JS.USER_ID = C_User_Id;
4083
4084 -- Cursor to select currency_code is passed null
4085 CURSOR C_currency_code (C_Terr_Code VARCHAR2) IS
4086 SELECT DECODE (derive_type, --enh 3098798
4087 NULL, currency_code,
4088 derive_type, 'EUR'
4089 ) currency_code
4090 FROM fnd_currencies
4091 WHERE issuing_territory_code = C_Terr_Code
4092 and nvl(start_date_active, sysdate) <= sysdate
4093 and nvl(end_date_active, sysdate) >= sysdate
4094 and enabled_flag = 'Y';
4095
4096 -- Cursor for fetching rows from as_imp_cnt_pnt_interface
4097 CURSOR c_cnt_pnt (c_owner_type varchar2, c_import_interface_id number)
4098 IS
4099 SELECT *
4100 FROM AS_IMP_CNT_PNT_INTERFACE
4101 WHERE owner_type = c_owner_type
4102 AND import_interface_id = c_import_interface_id;
4103
4104 -- Find the sales group of the person being added
4105 -- bugfix # 2772260
4106 CURSOR c_get_group_id (c_resource_id NUMBER, c_rs_group_member VARCHAR2,
4107 c_sales VARCHAR2, c_telesales VARCHAR2,
4108 c_fieldsales VARCHAR2, c_prm VARCHAR2, c_y VARCHAR2)
4109 IS
4110 SELECT grp.group_id
4111 FROM JTF_RS_GROUP_MEMBERS mem,
4112 JTF_RS_ROLE_RELATIONS rrel,
4113 JTF_RS_ROLES_B role,
4114 JTF_RS_GROUP_USAGES u,
4115 JTF_RS_GROUPS_B grp
4116 WHERE mem.group_member_id = rrel.role_resource_id
4117 AND rrel.role_resource_type = c_rs_group_member --'RS_GROUP_MEMBER'
4118 AND rrel.role_id = role.role_id
4119 AND role.role_type_code in (c_sales, c_telesales, c_fieldsales, c_prm) --'SALES','TELESALES','FIELDSALES','PRM')
4120 AND mem.delete_flag <> c_y --'Y'
4121 AND rrel.delete_flag <> c_y --'Y'
4122 AND SYSDATE BETWEEN rrel.start_date_active AND
4123 NVL(rrel.end_date_active,SYSDATE)
4124 AND mem.resource_id = c_resource_id
4125 AND mem.group_id = u.group_id
4126 AND u.usage = c_sales --'SALES'
4127 AND mem.group_id = grp.group_id
4128 AND SYSDATE BETWEEN grp.start_date_active AND
4129 NVL(grp.end_date_active,SYSDATE)
4130 AND ROWNUM < 2;
4131
4132 l_currency_code VARCHAR2(15);
4133 l_isQualified VARCHAR2(1);
4134 l_sales_lead_rec as_sales_leads_pub.sales_lead_rec_type;
4135 l_sales_lead_log_id NUMBER;
4136 x_sales_team_flag VARCHAR2(1);
4137 l_curr_time VARCHAR2(15);
4138
4139 l_create_party VARCHAR2(1) := 'N';
4140 l_create_party_site VARCHAR2(1) := 'N';
4141 l_create_location VARCHAR2(1) := 'N';
4142 l_create_contact VARCHAR2(1) := 'N';
4143 l_dup_sales_lead_id NUMBER;
4144
4145 l_validation_level NUMBER;
4146 l_hz_execute_api_callouts VARCHAR2(240);
4147
4148 BEGIN
4149 G_DEBUGFLAG := p_debug_msg_flag;
4150
4151 write_log(3, 'Sales Lead Import Child #'||p_child_request_id||' started at '
4152 ||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'));
4153
4154 write_log(3, 'Getting the saleasforce_id for the user ...');
4155
4156 -- SOLIN, bug 4702335
4157 IF p_resource_id IS NOT NULL
4158 THEN
4159 G_SL_SALESFORCE_ID := p_resource_id;
4160 l_group_id := p_group_id;
4161 ELSE
4162 OPEN C_Get_SLAESFORCE(fnd_global.user_id);
4163 FETCH C_Get_SLAESFORCE INTO G_SL_SALESFORCE_ID;
4164 CLOSE C_Get_SLAESFORCE;
4165
4166 If (G_SL_SALESFORCE_ID is null) then
4167 G_SL_SALESFORCE_ID := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
4168 end if;
4169
4170 -- Find the sales group of the person being added
4171 -- bugfix # 2772260
4172 OPEN c_get_group_id (G_SL_SALESFORCE_ID, 'RS_GROUP_MEMBER', 'SALES',
4173 'TELESALES', 'FIELDSALES', 'PRM', 'Y');
4174 FETCH c_get_group_id INTO l_group_id;
4175 CLOSE c_get_group_id;
4176 end if;
4177 -- SOLIN, end
4178
4179 write_log(3, 'Salesforce_id for the logged in user is : ' || G_SL_SALESFORCE_ID);
4180 write_log(3, 'Slaes Group id : ' || l_group_id);
4181
4182 -- ajchatto 050602, check for SOURCE_SYSTEM
4183 -- bug# 2351782
4184 -- Bugfix# 2835357, check if the source system is valid or not once.
4185 SELECT count(*)
4186 INTO l_source_system
4187 FROM as_lookups
4188 WHERE lookup_type = 'SOURCE_SYSTEM'
4189 AND lookup_code = p_source_system;
4190
4191 -- SOLIN, bug 4494009
4192 l_hz_execute_api_callouts := fnd_profile.value('HZ_EXECUTE_API_CALLOUTS');
4193 write_log(3, 'Profile HZ_EXECUTE_API_CALLOUTS: '|| l_hz_execute_api_callouts);
4194 fnd_profile.put('HZ_EXECUTE_API_CALLOUTS', 'N');
4195 -- SOLIN, end
4196
4197 -- For each lead
4198 For I in c_main(p_parent_request_id, p_child_request_id)
4199 Loop
4200 l_batch_size := l_batch_size +1;
4201 FND_MSG_PUB.Initialize;
4202
4203 Begin
4204
4205 IF I.party_type IS NULL THEN
4206 I.party_type := 'ORGANIZATION';
4207 END IF;
4208
4209 -- resetting flag bug# 2574165
4210 l_orig_sys_party_found := NULL;
4211 l_duplicate_lead := 'U';
4212 l_dup_sales_lead_id := NULL;
4213 write_log(3, 'Processing import_interface_id: '||to_char(I.import_interface_id));
4214
4215 IF l_source_system < 1 THEN
4216 AS_UTILITY_PVT.Set_Message(
4217 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
4218 p_msg_name => 'API_INVALID_ID',
4219 p_token1 => 'COLUMN',
4220 p_token1_value => 'SOURCE_SYSTEM',
4221 p_token2 => 'VALUE',
4222 p_token2_value => I.SOURCE_SYSTEM );
4223 RAISE FND_API.G_EXC_ERROR;
4224 END IF;
4225
4226 -- Begin enh: Support for Currency : aanjaria 100402
4227 IF I.currency_code IS NULL AND I.budget_amount IS NOT NULL THEN
4228 -- SOLIN, Bug 4956232
4229 -- throw exception, not get currency_code for not null budget_amount
4230 AS_UTILITY_PVT.Set_Message(
4231 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
4232 p_msg_name => 'API_INVALID_ID',
4233 p_token1 => 'COLUMN',
4234 p_token1_value => 'CURRENCY_CODE',
4235 p_token2 => 'VALUE',
4236 p_token2_value => 'NULL' );
4237 write_log(3, 'Please enter currency_code when your budget_amount is entered.');
4238 raise FND_API.G_EXC_ERROR;
4239
4240 -- Get the currency of the customer country
4241 --OPEN C_currency_code(I.country);
4242 --FETCH C_currency_code INTO l_currency_code;
4243 --IF C_currency_code%NOTFOUND THEN
4244 -- Set default currency
4245 -- l_currency_code := fnd_profile.value('JTF_PROFILE_DEFAULT_CURRENCY');
4246 --END IF;
4247 --CLOSE C_currency_code;
4248 --I.currency_code := l_currency_code;
4249 END IF; --if currency_code is null
4250 write_log(3, 'Value of currency :'||I.currency_code);
4251 -- End enh: Support for Currency
4252
4253 -- Check profile for executing custom hook
4254 IF (fnd_profile.value ('AS_LEAD_IMP_EXEC_CUSTOM_CODE')='Y') Then
4255
4256 write_log(3, 'Before calling custom hook for party match');
4257 -- Call custom hook
4258 aml_find_party_match_pvt.main(I, --IN OUT param
4259 x_return_status --OUT param
4260 );
4261
4262 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
4263 write_log(3, 'aml_find_party_match failed');
4264 raise FND_API.G_EXC_ERROR;
4265 END IF;
4266
4267 write_log(3, 'Returned from custom hook with party_id: '||to_char(I.party_id));
4268
4269 END IF;
4270
4271 --End custom hook
4272
4273 --Start DQM entities processing
4274 --aanjaria 11.29.2002
4275 write_log(3, 'Start DQM - TCA Processing');
4276
4277 SELECT to_char(sysdate,'yyyymmddhhmiss')
4278 INTO l_curr_time
4279 FROM dual;
4280 write_log(3, 'Starting DQM - TCA time: '||l_curr_time);
4281
4282 TCA_DQM_processing (I, l_create_party, l_create_party_site, l_create_contact, -- l_create_contact_point,
4283 l_create_location, p_dup_phone, p_dup_fax, p_dup_email, p_dup_url );
4284 write_log(3, 'DQM returned: '||l_create_party||l_create_party_site||l_create_contact||l_create_location||'-'||p_dup_phone||p_dup_email||p_dup_fax||p_dup_url);
4285
4286 -- Check for lead duplication before creating it !
4287 IF I.party_id IS NOT NULL AND I.source_system <> 'INTERACTION' THEN --bug 3601263 bypass dedupe for interaction
4288 deDupe_Check(pI => I, x_duplicate_lead => l_duplicate_lead, x_dup_sales_lead_id => l_dup_sales_lead_id );
4289 write_log(3,'back from dedupe checking'||l_duplicate_lead||'-'||to_char(l_dup_sales_lead_id));
4290 END IF;
4291
4292 IF l_duplicate_lead = 'D' THEN
4293 I.sales_lead_id := l_dup_sales_lead_id;
4294 write_log(3, 'Duplicate lead');
4295 I.load_status := 'DUPLICATE';
4296 writeBak(I, G_return_status);
4297 commit;
4298 IF ((G_return_status <> FND_API.G_RET_STS_SUCCESS) AND (G_return_status <> 'W')) THEN
4299 write_log(3, 'writeBak failed');
4300 RAISE FND_API.G_EXC_ERROR;
4301 END IF;
4302 END IF;
4303
4304 IF l_duplicate_lead = 'U' THEN
4305
4306 -- ffang 092601, for bug 2017445, lead existence checking should
4307 -- be check at the begining to prevent customer/address/contact creation.
4308 IF Is_duplicate_lead(I) THEN
4309 write_log(3, 'Duplicate lead');
4310 I.load_status := 'DUPLICATE';
4311 writeBak(I, G_return_status);
4312 IF ((G_return_status <> FND_API.G_RET_STS_SUCCESS) AND
4313 (G_return_status <> 'W'))
4314 THEN
4315 write_log(3, 'writeBak failed');
4316 RAISE FND_API.G_EXC_ERROR;
4317 END IF;
4318
4319 ELSE
4320 -- Create TCA entities
4321
4322 -- Create Location
4323 IF l_create_location = 'Y' or I.location_id IS NULL THEN
4324 -- ffang 100901, for bug 2042175, if address1 or country
4325 -- does not exist, skip create location
4326 IF (I.address1 IS NOT NULL AND I.country IS NOT NULL) THEN
4327 write_log(3, 'Creating location');
4328 do_create_location(I, G_return_status);
4329 -- If error raise exception
4330 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4331 write_log(3, 'Creating location failed');
4332 RAISE FND_API.G_EXC_ERROR;
4333 END IF;
4334 I.new_loc_flag := 1; -- new location flag set
4335 ELSE
4336 write_log (3, 'No add1/country-skip creating location');
4337 END IF;
4338 ELSE
4339 write_log (3, 'dup location:' || I.location_id);
4340 END IF;
4341
4342 -- Create Party
4343 IF l_create_party = 'Y' or I.party_id IS NULL THEN
4344 IF I.party_type = 'ORGANIZATION' THEN
4345 do_create_organization(I, G_return_status);
4346 -- If error raise exception
4347 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4348 write_log(3, 'Creating Organization failed');
4349 RAISE FND_API.G_EXC_ERROR;
4350 END IF;
4351 I.new_party_flag := 1; --new party flag set
4352 ELSIF I.party_type ='PERSON' THEN
4353 do_create_person(I, I.party_type, G_return_status);
4354 -- If error raise exception
4355 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4356 write_log(3, 'do_create_Person failed');
4357 RAISE FND_API.G_EXC_ERROR;
4358 END IF;
4359 I.new_party_flag := 1; --new party flag set
4360 ELSE
4361 -- ffang 101201, bug2050535, push error message
4362 AS_UTILITY_PVT.Set_Message(
4363 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
4364 p_msg_name => 'AS_INVALID_PARTY_TYPE',
4365 p_token1 => 'VALUE',
4366 p_token1_value => I.party_type);
4367 write_log (3, 'Party_type is invalid');
4368 -- end ffang 101201
4369 RAISE FND_API.G_EXC_ERROR;
4370 END IF; -- party_type condition end
4371 --2851215: orig_system_reference should not be updated
4372 /*
4373 ELSIF l_create_party = 'N' and I.party_id IS NOT NULL THEN
4374 IF I.party_type IS NOT NULL THEN
4375 write_log (3, 'dup party:' || I.party_id || '-' || I.party_type);
4376 do_update_party(I, G_return_status);
4377 -- If error raise exception
4378 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4379 write_log(3, 'do_update_party failed');
4380 RAISE FND_API.G_EXC_ERROR;
4381 END IF;
4382 END IF;
4383 */
4384 END IF; -- l_party_found condition end
4385
4386 -- Create Party Site
4387 IF (l_create_party_site = 'Y' or I.party_site_id IS NULL) THEN
4388 -- ffang 100901, for bug 2042175, if location is not
4389 -- created, skip create party site and party site use
4390 IF (I.location_id IS NOT NULL) THEN
4391 -- write_log(3, 'Creating party site');
4392 write_log(3, 'Creating party site for Organization');
4393 -- swkhanna 6/12/02 Bug 2404796
4394 -- do_create_ps_psu(I, I.party_id, 'ORG', G_return_status);
4395 do_create_ps_psu(I, I.party_id, I.party_type, G_return_status);
4396 -- If error raise exception
4397 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4398 write_log(3, 'Creating Party Site / Use failed');
4399 RAISE FND_API.G_EXC_ERROR;
4400 END IF;
4401 I.new_ps_flag := 1; --new party flag set
4402 ELSE
4403 write_log(3,'no location created-skip create PS');
4404 END IF;
4405 END IF;
4406
4407 -- Create contact points for 'PERSON'
4408 FOR cpp in c_cnt_pnt('PERSON', I.import_interface_id) LOOP
4409 write_log(3, 'Creating contact points for PERSON');
4410 do_create_contact_points(I, cpp, 'PERSON', G_return_status);
4411 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4412 write_log(3, 'createContactPoints for person failed');
4413 RAISE FND_API.G_EXC_ERROR;
4414 END IF;
4415 END LOOP;
4416
4417 -- Create contacts
4418 IF I.party_type = 'ORGANIZATION' THEN
4419 IF I.contact_party_id IS NULL or l_create_contact = 'Y' THEN
4420 -- ffang 100901, bug 2042181, if first name or last name
4421 -- does not exist, skip create contact
4422 IF (I.first_name IS NOT NULL and I.last_name IS NOT NULL) THEN
4423 write_log(3, 'Creating the Contact');
4424 do_create_person(I, 'CONTACT', G_return_status);
4425 -- If error raise exception
4426 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4427 write_log(3, 'do_create_Person-contact failed');
4428 RAISE FND_API.G_EXC_ERROR;
4429 END IF;
4430 I.new_con_flag := 1; -- new contact flag set
4431 ELSE
4432 write_log(3, 'no first/last name-skip create cnt');
4433 END IF;
4434 ELSE
4435 write_log(3, 'dup contact: ' || I.contact_party_id);
4436 END IF; --create_contact = 'Y'
4437
4438 -- Check and createOrgContact, Relationship and OrgContactRoles
4439 -- ffang 100901, for bug 2042181, if contact is not created,
4440 -- then don't create relationship
4441 IF (I.rel_party_id is NULL and I.contact_party_id is not NULL) THEN
4442 do_create_relationship(I, G_return_status);
4443 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4444 write_log(3, 'createRelationship failed');
4445 RAISE FND_API.G_EXC_ERROR;
4446 END IF;
4447 I.new_rel_flag := 1; -- new relationship flag set
4448 END IF;
4449 END IF; --if party_type = 'ORGANIZATION'
4450
4451 -- Create Contact Point
4452 IF (I.party_type = 'ORGANIZATION' and I.rel_party_id is not null) or
4453 (I.party_type <> 'ORGANIZATION' and I.party_id is not null) THEN
4454 write_log (3, 'Creating contact point (1)');
4455
4456 do_create_contact_points_old (I, p_dup_phone, p_dup_fax, p_dup_email, p_dup_url, G_return_status);
4457 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4458 write_log(3, 'createContactPoints for contact (1) failed');
4459 RAISE FND_API.G_EXC_ERROR;
4460 END IF;
4461
4462 -- SOLIN, bug 4637420
4463 -- create_contact_preferences should be called after
4464 -- contact point is created.
4465 do_contact_preference(I, G_return_status);
4466 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4467 write_log(3, 'createContactPreference failed');
4468 RAISE FND_API.G_EXC_ERROR;
4469 END IF;
4470 -- SOLIN, end bug 4637420
4471 ELSE
4472 write_log (3, ' Contact Point Not Created ');
4473 END IF;
4474
4475 -- create contact points (in as_imp_cnt_pnt_interface) for 'CONTACT'
4476 FOR cpc in c_cnt_pnt('CONTACT', I.import_interface_id) LOOP
4477 write_log (3, 'Creating contact point (2)');
4478 do_create_contact_points(I, cpc, 'CONTACT',G_return_status);
4479 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4480 write_log(3, 'createContactPoints for contact failed');
4481 RAISE FND_API.G_EXC_ERROR;
4482 END IF;
4483 END LOOP;
4484
4485 SELECT to_char(sysdate,'yyyymmddhhmiss')
4486 INTO l_curr_time
4487 FROM dual;
4488 write_log(3, 'End DQM - TCA time: '||l_curr_time);
4489
4490
4491 -- After all the contact points are created
4492 -- check if there are at least one contact point,
4493 -- If not, update one of the phone contact as primary
4494 -- Added by Ajoy
4495 -- Not currently used as HZ_CONTACT_POINT_PUB.create_contact_points
4496 -- takes care of setting the primary flag for PHONE, EMAIL, WEB etc.
4497
4498 -- validate_primary_cp (I, G_return_status);
4499
4500
4501 -- new call goes here.
4502
4503 -- createSalesLead
4504 -- ffang 080201, for bug 1852338, check if leads is a duplicate
4505 -- lead or not before creating lead.
4506 -- ffang 092601, for bug 2017445, lead existence checking should
4507 -- be check at the begining to prevent customer/address/contact
4508 -- creation.
4509 -- If not is_duplicate_lead(I) THEN
4510
4511 SELECT to_char(sysdate,'yyyymmddhhmiss')
4512 INTO l_curr_time
4513 FROM dual;
4514 write_log(3, 'Start Create - Process Lead time: '||l_curr_time);
4515
4516
4517 do_create_saleslead(I, G_return_status);
4518
4519 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4520 write_log(3, 'do_create_saleslead failed');
4521 RAISE FND_API.G_EXC_ERROR;
4522 End If;
4523
4524 -- Added by Ajoy, 08/21, bugfix# 2521850
4525 -- If the lead creation is successful, update the import record with sales_lead_id
4526 -- so that the marketing_score (lead_score) attribute can be used in rule engine
4527 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
4528 /* --redundent update..after purge project this update is not needed
4529 UPDATE as_import_interface
4530 SET sales_lead_id = I.sales_lead_id
4531 WHERE import_interface_id = I.import_interface_id;
4532 */
4533 UPDATE aml_interaction_leads
4534 SET sales_lead_id = I.sales_lead_id
4535 WHERE import_interface_id = I.import_interface_id;
4536 End if;
4537
4538 -- End If;
4539
4540 -- createInterest
4541 --do_create_Interest(I, G_return_status);
4542 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4543 write_log(3, 'do_create_interest failed');
4544 RAISE FND_API.G_EXC_ERROR;
4545 End If;
4546
4547 -- do_create_LeadNoteAndContext
4548 If ((I.sales_lead_id is not null) AND (I.party_id is not null)
4549 AND ((I.lead_note is not null) OR
4550 (I.lead_note <> FND_API.G_MISS_CHAR)))
4551 then
4552 do_create_LeadNoteAndContext(I, G_return_status);
4553 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4554 write_log(3, 'do_create_LeadNoteAndContext failed');
4555 RAISE FND_API.G_EXC_ERROR;
4556 End If;
4557 End if;
4558
4559 G_LOCAL_ORG_CONTACT_ID := Null;
4560 G_SL_LINE_COUNT := 0;
4561
4562 --Bug 3680824: non resource user can import the lead for sales campaign
4563 -- in which case, user validation needs to be bypassed
4564 IF I.source_system = 'SALES_CAMPAIGN' THEN
4565 l_validation_level := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM;
4566 ELSE
4567 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
4568 END IF;
4569
4570 AS_SALES_LEAD_ENGINE_PVT.Lead_Process_After_Create (
4571 P_Api_Version_Number => 2.0,
4572 P_Init_Msg_List => FND_API.G_FALSE,
4573 P_Commit => FND_API.G_FALSE,
4574 P_Validation_Level => l_validation_level,
4575 P_Check_Access_Flag => FND_API.G_MISS_CHAR,
4576 P_Admin_Flag => FND_API.G_MISS_CHAR,
4577 P_Admin_Group_Id => FND_API.G_MISS_NUM,
4578 P_identity_salesforce_id => G_SL_SALESFORCE_ID,
4579 P_Salesgroup_id => l_group_id,
4580 P_Sales_Lead_Id => I.sales_lead_id,
4581 X_Return_Status => l_return_status,
4582 X_Msg_Count => l_msg_count,
4583 X_Msg_Data => l_msg_data
4584 );
4585
4586 -- bugfix# 2891236 , should check for l_return_status
4587 -- Bug 2893436, it shouldn't raise exception if return status is W
4588 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4589 write_log(3, 'Lead_Process_After_Create errors');
4590 raise FND_API.G_EXC_ERROR;
4591 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4592 write_log(3, 'Lead_Process_After_Create unexp errors');
4593 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4594 ELSE
4595 write_log(3, 'Lead_Process_After_Create ' || l_return_status);
4596 END IF;
4597
4598 SELECT to_char(sysdate,'yyyymmddhhmiss')
4599 INTO l_curr_time
4600 FROM dual;
4601 write_log(3, 'End Create - Process Lead time: '||l_curr_time);
4602
4603 --writing bak to as_import_interface
4604 I.load_status := G_LOAD_STATUS_SUCC;
4605 writeBak(I, G_return_status);
4606 IF ((G_return_status <> FND_API.G_RET_STS_SUCCESS) AND
4607 (G_return_status <> 'W'))
4608 THEN
4609 write_log(3, 'writeBak failed');
4610 RAISE FND_API.G_EXC_ERROR;
4611 END IF;
4612 l_batch_succ := l_batch_succ +1;
4613
4614 -- ffang 071701, bug 1888388, temporary solution
4615 -- the sales teams created by assign_sales_lead
4616 -- (update_sales_lead) should set freeze_flag to 'N'.
4617 -- This fix should be removed after sales lead api changed.
4618
4619 -- Bugfix# 2889261, Not to update the KEEP_FLAG
4620 --update as_accesses_all set freeze_flag='N'
4621 --where sales_lead_id = I.sales_lead_id;
4622 End if;
4623
4624 End If; -- Duplicate Lead Check
4625 commit;
4626
4627 Exception
4628
4629 when FND_API.G_EXC_ERROR Then
4630 rollback;
4631 l_batch_err := l_batch_err +1;
4632 l_error_type := 'EXP';
4633 write_errors(I,l_error_type, G_return_status );
4634 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4635 write_log(3, 'write_errors failed');
4636 else
4637 I.load_status := G_LOAD_STATUS_ERR;
4638 writeBak(I, G_return_status);
4639 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4640 write_log(3, 'writeBak failed');
4641 End if;
4642 End if;
4643 commit;
4644 when FND_API.G_EXC_UNEXPECTED_ERROR Then
4645 l_batch_unexp := l_batch_unexp +1;
4646 rollback;
4647 l_error_type := 'UNEXP';
4648 write_errors(I,l_error_type, G_return_status );
4649 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4650 write_log(3, 'write_errors failed');
4651 else
4652 I.load_status := G_LOAD_STATUS_UNEXP_ERR;
4653 writeBak(I, G_return_status);
4654 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4655 write_log(3, 'writeBak failed');
4656 End if;
4657 End if;
4658 commit;
4659 when others then
4660 l_batch_unexp := l_batch_unexp +1;
4661 rollback;
4662 l_error_type := 'OTHER';
4663 write_errors(I,l_error_type, G_return_status );
4664 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4665 write_log(3, 'write_errors failed');
4666 else
4667 I.load_status := G_LOAD_STATUS_UNEXP_ERR;
4668 writeBak(I, G_return_status);
4669 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4670 write_log(3, 'writeBak failed');
4671 End if;
4672 End if;
4673 commit;
4674 End;
4675 End Loop;
4676
4677 -- SOLIN, bug 4494009
4678 -- Set profile back to its original value
4679 fnd_profile.put('HZ_EXECUTE_API_CALLOUTS', l_hz_execute_api_callouts);
4680 -- SOLIN, end
4681
4682 write_log(2, 'Batch Size:'|| l_batch_size);
4683 write_log(1, 'Batch Size:'|| l_batch_size);
4684 write_log(2, 'Number of Records Successfully Imported:'|| l_batch_succ);
4685 write_log(1, 'Number of Records Successfully Imported:'|| l_batch_succ);
4686 write_log(2, 'Number of Records with Errors:'|| l_batch_err);
4687 write_log(1, 'Number of Records with Errors:'|| l_batch_err);
4688 write_log(2, 'Number of Records with unexpected Errors:'|| l_batch_unexp);
4689 write_log(1, 'Number of Records with unexpected Errors:'|| l_batch_unexp);
4690
4691 write_log(3, 'End Child Import Process time: '||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'));
4692
4693 Commit;
4694
4695 Exception
4696 when others then
4697 rollback;
4698 write_log(2, sqlerrm);
4699 write_log(1, sqlerrm);
4700 l_status := fnd_concurrent.set_completion_status('ERROR', sqlerrm);
4701
4702 end do_lead_import;
4703
4704
4705 ----------------------------------------------------------
4706 -- Name: main
4707 -- Scope: Public
4708 -- Sales Lead Import parallel logic implemented
4709 -- Main procedure called from Import Sales Lead conc prog
4710 ----------------------------------------------------------
4711 procedure main(
4712 errbuf varchar2,
4713 errcode varchar2,
4714 p_source_system in varchar2,
4715 --p_creation_date in date, -- bugfix : 2044447
4716 p_debug_msg_flag in varchar2 := 'N',--bugfix : 2047689
4717 p_batch_id in number,
4718 p_purge_error_flag in varchar2 := 'N',
4719 p_parent_request_id in number := NULL,
4720 p_child_request_id in number := NULL,
4721 p_resource_id in number := NULL, -- SOLIN, bug 4702335
4722 p_group_id in number := NULL -- SOLIN, bug 4702335
4723 ) IS
4724
4725 l_parameter_list wf_parameter_list_t;
4726 l_req_data VARCHAR2(10);
4727 l_req_data_counter NUMBER;
4728 l_batch_size NUMBER;
4729 l_request_id NUMBER;
4730 l_new_request_id NUMBER;
4731 l_total_children NUMBER;
4732 l_total_records NUMBER;
4733 l_interaction_threshold NUMBER;
4734
4735 l_wait_status BOOLEAN;
4736 x_phase VARCHAR2(30);
4737 x_status VARCHAR2(30);
4738 x_dev_phase VARCHAR2(30);
4739 x_dev_status VARCHAR2(30);
4740 x_message VARCHAR2(240);
4741
4742 TYPE request_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4743 l_request_id_tbl request_id_tbl;
4744
4745 -- SOLIN, bug 4556394, SQL tuning
4746 CURSOR c_get_schema_name(c_table_name VARCHAR2) IS
4747 SELECT owner
4748 FROM sys.all_tables
4749 WHERE table_name = c_table_name;
4750
4751 l_owner VARCHAR2(30);
4752 Begin
4753
4754 G_DEBUGFLAG := p_debug_msg_flag;
4755
4756 If p_child_request_id IS NOT NULL THEN
4757 write_log(3, 'Starting child process# '||p_child_request_id);
4758 do_lead_import(
4759 p_source_system,
4760 p_debug_msg_flag,
4761 p_parent_request_id,
4762 p_child_request_id,
4763 p_resource_id,
4764 p_group_id);
4765
4766 Elsif p_child_request_id is NULL THEN
4767
4768 write_log(3, 'Starting Main Lead Import Process time: '||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'));
4769
4770 /* Create parameter list for LeadImport events */
4771 l_parameter_list := WF_PARAMETER_LIST_T();
4772
4773 wf_event.AddParameterToList(p_name => 'P_SOURCE_SYSTEM',
4774 p_value => p_source_system,
4775 p_parameterlist => l_parameter_list);
4776
4777 wf_event.AddParameterToList(p_name => 'P_BATCH_ID',
4778 p_value => p_batch_id,
4779 p_parameterlist => l_parameter_list);
4780
4781 wf_event.AddParameterToList(p_name => 'P_DEBUG_MSG_FLAG',
4782 p_value => p_debug_msg_flag,
4783 p_parameterlist => l_parameter_list);
4784
4785 wf_event.AddParameterToList(p_name => 'P_PURGE_ERROR_FLAG',
4786 p_value => p_purge_error_flag,
4787 p_parameterlist => l_parameter_list);
4788
4789 /*** Raise LeadImport-PRE Event ***/
4790 write_log(1, 'Calling LeadImport-PRE Event');
4791 write_log(2, 'Calling LeadImport-PRE Event');
4792 write_log(3, 'Calling LeadImport-PRE Event');
4793
4794 Wf_Event.Raise
4795 ( p_event_name => 'oracle.apps.ams.leads.LeadsImportEvent.Pre',
4796 p_event_key => TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS'),
4797 p_parameters => l_parameter_list,
4798 p_send_date => sysdate
4799 );
4800 /*** End Event Raise ***/
4801
4802
4803 -- ffang 082301, user use parameter p_purge_error_flag to decide if
4804 -- purge AS_LEAD_IMPORT_ERRORS
4805 IF p_purge_error_flag = 'Y' THEN
4806 OPEN c_get_schema_name('AS_LEAD_IMPORT_ERRORS');
4807 FETCH c_get_schema_name INTO l_owner;
4808 CLOSE c_get_schema_name;
4809
4810 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_owner
4811 || '.AS_LEAD_IMPORT_ERRORS';
4812 -- delete from AS_LEAD_IMPORT_ERRORS;
4813 END IF;
4814
4815 --Get conc_request_id
4816 l_request_id := nvl(FND_GLOBAL.conc_request_id, -1);
4817
4818 --Get the batch size for each thread from profile
4819 l_batch_size := nvl(to_number(FND_PROFILE.value('AS_MIN_REC_PARALLEL_FOR_IMPORT')),0);
4820
4821 --Get interaction threshold
4822 l_interaction_threshold := nvl(FND_PROFILE.value('AS_INTERACTION_SCORE_THRESHOLD'),0);
4823
4824 --If profile is set to -ve value
4825 If l_batch_size < 0 then
4826 l_batch_size := 0;
4827 End if;
4828
4829 if p_batch_id > 0 then
4830 write_log(3, 'batch_id is found ');
4831 -- Update load_status to RUNNING
4832 UPDATE as_import_interface
4833 SET load_status = 'RUNNING', request_id = l_request_id,
4834 child_request_id = ceil(ROWNUM/decode(l_batch_size,0,ROWNUM,l_batch_size))
4835 WHERE batch_id = p_batch_id
4836 AND source_system = p_source_system
4837 AND load_status = 'NEW'
4838 AND decode(source_system,'INTERACTION',interaction_score,l_interaction_threshold) >= l_interaction_threshold;
4839 else
4840 write_log(3, 'batch_id is null ');
4841 UPDATE as_import_interface
4842 SET load_status = 'RUNNING', request_id = l_request_id,
4843 child_request_id = ceil(ROWNUM/decode(l_batch_size,0,ROWNUM,l_batch_size))
4844 WHERE source_system = p_source_system
4845 AND load_status = 'NEW'
4846 AND decode(source_system,'INTERACTION',interaction_score,l_interaction_threshold) >= l_interaction_threshold;
4847 end if;
4848
4849 --total records in batch
4850 l_total_records := SQL%ROWCOUNT;
4851 write_log(1, 'Total batch size: '||l_total_records);
4852 write_log(2, 'Total batch size: '||l_total_records);
4853 write_log(3, 'Total batch size: '||l_total_records);
4854
4855 COMMIT;
4856 write_log(3,'Updated load_status to RUNNING');
4857
4858 --handle condition if batch size for parallel import is set to null or zero.
4859 If l_batch_size <= 0 then
4860 l_batch_size := l_total_records;
4861 End if;
4862
4863
4864 If l_total_records > 0 then
4865 --Calculate number of child processes required
4866 l_total_children := ceil(l_total_records/l_batch_size);
4867
4868
4869 l_req_data := fnd_conc_global.request_data;
4870
4871 if (l_req_data is not null) then
4872 l_req_data_counter := to_number(l_req_data);
4873 l_req_data_counter := l_req_data_counter + 1;
4874 else
4875 l_req_data_counter := 1;
4876 end if;
4877
4878
4879 --Spawn child conc requests
4880 FOR child_idx IN 1..l_total_children LOOP
4881
4882 l_new_request_id := FND_REQUEST.SUBMIT_REQUEST (
4883 application => 'AS',
4884 program => 'ASXSLIMP',
4885 description => 'Import Sales Leads - Child #'||to_char(child_idx),
4886 --sub_request => TRUE,
4887 argument1 => p_source_system,
4888 argument2 => p_debug_msg_flag,
4889 argument3 => p_batch_id,
4890 argument4 => p_purge_error_flag,
4891 argument5 => l_request_id,
4892 argument6 => child_idx,
4893 argument7 => p_resource_id,
4894 argument8 => p_group_id
4895 );
4896
4897 IF l_new_request_id = 0 THEN
4898 write_log(1, 'Error during submission of child request #'||child_idx);
4899 write_log(2, 'Error during submission of child request #'||child_idx);
4900 write_log(3, 'Error during submission of child request #'||child_idx);
4901 END IF;
4902
4903 write_log(1, 'Spawned child# '||to_char(child_idx)||' request_id: '||to_char(l_new_request_id));
4904 write_log(2, 'Spawned child# '||to_char(child_idx)||' request_id: '||to_char(l_new_request_id));
4905 write_log(3, 'Spawned child# '||to_char(child_idx)||' request_id: '||to_char(l_new_request_id));
4906 l_request_id_tbl(child_idx) := l_new_request_id;
4907 END LOOP;
4908
4909 --Wait for children to finish
4910 --Bug# 3523221 changed api call to wait for children
4911
4912 commit;
4913 FOR child_idx IN 1 .. l_request_id_tbl.count LOOP
4914
4915 write_log(3, 'Waiting for child#'||to_char(child_idx));
4916
4917 l_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST (
4918 request_id => l_request_id_tbl(child_idx),
4919 phase => x_phase,
4920 status => x_status,
4921 dev_phase => x_dev_phase,
4922 dev_status => x_dev_status,
4923 message => x_message
4924 );
4925
4926 END LOOP;
4927
4928 Else -- l_total_records = 0
4929 write_log(3, 'Batch size: 0');
4930 l_total_children := 0;
4931 End if;
4932
4933
4934 /*** Raise LeadImport-POST Event ***/
4935 write_log(1, 'Calling LeadImport-POST Event');
4936 write_log(2, 'Calling LeadImport-POST Event');
4937 write_log(3, 'Calling LeadImport-POST Event');
4938
4939 Wf_Event.Raise
4940 ( p_event_name => 'oracle.apps.ams.leads.LeadsImportEvent.Post',
4941 p_event_key => TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS'),
4942 p_parameters => l_parameter_list,
4943 p_send_date => sysdate
4944 );
4945 /*** End Event Raise ***/
4946
4947 l_parameter_list.DELETE;
4948
4949 write_log(3, 'End Parent Import Process time: '||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'));
4950 write_log(3, 'Total '||l_total_records||' records processed.');
4951
4952 End if;
4953
4954 Commit;
4955
4956 Exception
4957 when others then
4958 rollback;
4959 write_log(2, sqlerrm);
4960 write_log(1, sqlerrm);
4961 l_status := fnd_concurrent.set_completion_status('ERROR', sqlerrm);
4962
4963 end main;
4964
4965 end as_import_sl_pvt;