[Home] [Help]
PACKAGE BODY: APPS.AS_IMPORT_SL_PVT
Source
4 --impView as_imp_sl_v%rowtype;
1 package body as_import_sl_pvt as
2 /* $Header: asxslimb.pls 120.10.12020000.3 2013/05/21 08:34:58 snsarava ship $ */
3
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),
101 l_msg_data,
98 nvl(FND_GLOBAL.Login_id, -1),
99 pI.import_interface_id,
100 nvl(pI.batch_id,-1),
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),
184 -- ffang 101601, bug 2053591, populate promotion_id / promotion_code
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,
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,
210 program_application_id = nvl(FND_GLOBAL.Prog_appl_id, -1),
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),
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;
336 p_dup_fax := 'N';
333 if c_chk_cont_pnt%FOUND then
334 p_dup_fax := 'Y';
335 else
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
477 -- procedure validate_primary_cp.
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
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
524 pHzpRec in OUT NOCOPY hz_party_v2pub.party_rec_type,
521 -- 8. AS_SALES_LEAD_CONTACTS
522 --------------------------------------------------------
523 procedure do_assign_flex (
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
555 BEGIN
552 and entity_name = pEntity;
553 l_index NUMBER := 1;
554
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;
646 -- pHzlRec.global_attribute19 := I.gattr_val_19;
643 -- pHzlRec.global_attribute16 := I.gattr_val_16;
644 -- pHzlRec.global_attribute17 := I.gattr_val_17;
645 -- pHzlRec.global_attribute18 := I.gattr_val_18;
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;
742 pHzocRec.attribute4 := I.attr_val_4;
739 pHzocRec.attribute1 := I.attr_val_1;
740 pHzocRec.attribute2 := I.attr_val_2;
741 pHzocRec.attribute3 := I.attr_val_3;
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;
772 -- pHzocRec.global_attribute9 := I.gattr_val_9;
769 -- pHzocRec.global_attribute6 := I.gattr_val_6;
770 -- pHzocRec.global_attribute7 := I.gattr_val_7;
771 -- pHzocRec.global_attribute8 := I.gattr_val_8;
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 --------------------------------------------------------
882 G_return_status OUT NOCOPY varchar2)
879 procedure do_create_person(
880 pI IN OUT NOCOPY leadImpType,
881 pType IN 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
1010 END IF;
1007 write_log (3, 'Person created: '||pI.party_id);
1008 Elsif (pType = 'CONTACT') then
1009 write_log (3, 'Contact created: '||pI.contact_party_id);
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');
1138 x_return_status => G_return_status,
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,
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,
1254 );
1251 x_msg_count => G_MESG_COUNT,
1252 x_msg_data => l_msg_data,
1253 x_contact_preference_id => l_res_id
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
1385
1382 write_log(3, 'Duplicate PHONE');
1383 END IF;
1384
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);
1516 -- fill up contact_point_rec_type
1513 --dbms_output.put_line('l_dup_fax:'||l_dup_fax);
1514 --dbms_output.put_line('l_dup_url:'||l_dup_url);
1515
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;
1638 --l_ph_rec.timezone_id := pCP.timezone_id;
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;
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
1759 l_location_rec.language := pI.language;
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;
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;
1888 l_org_rec.created_by_module := 'AML_LEAD_IMPORT';
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
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
2026 l_ps_use_rec.site_use_type := 'BILL_TO';
2023 -- to 'BILL_TO'
2024 IF (pI.site_use_type is null OR pI.site_use_type = FND_API.G_MISS_CHAR)
2025 THEN
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';
2147 l_dummy_rec1,
2144 l_org_con_rec.application_id := 530;
2145
2146 do_assign_flex (
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;
2248 THEN
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)
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);
2349 l_temp_promotion_code VARCHAR2(50);
2346 l_api_name CONSTANT VARCHAR2(30) := 'create_sales_lead';
2347 l_temp_promotion_id NUMBER;
2348 -- ffang 101601, bug 2053591
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
2475 IF c_get_promotion_id%NOTFOUND THEN
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;
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;
2589 l_sales_lead_rec.sales_methodology_id := pI.sales_methodology_id;
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;
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 :=
2695 G_SL_LINE_COUNT := 4;
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;
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,
2827 l_sales_lead_contact_tbl,
2824 -- l_dummy_rec6,
2825 l_dummy_rec7,
2826 l_dummy_tbl8,
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;
2948 if pI.party_type = 'PERSON' then
2945 end if;
2946
2947
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 --Code modified for bug13954536(11906160)
3049 -- SOLIN, bug 4227632, use 'PARTY' always
3050 --If pI.party_type = 'ORGANIZATION' then
3051 -- l_note_context_rec.NOTE_CONTEXT_TYPE := 'PARTY_ORGANIZATION';
3052 --else
3053 -- l_note_context_rec.NOTE_CONTEXT_TYPE := 'PARTY';
3054 --end if;
3055
3056 --Code modified for bug13954536(11906160) --start
3057 If pI.party_type = 'ORGANIZATION' then
3058 --Code added for bug11906160
3059 If nvl(fnd_profile.value('AS_NOTES_LEAD_CUSTOMER'),'N') = 'Y' then
3060
3061 l_note_context_rec.NOTE_CONTEXT_TYPE := 'PARTY_ORGANIZATION';
3062 l_note_context_rec.NOTE_CONTEXT_TYPE_ID := pI.party_id;
3063 end if;
3064 else
3065 If nvl(fnd_profile.value('AS_NOTES_LEAD_CONTACT'),'N') = 'Y' then
3066
3067 l_note_context_rec.NOTE_CONTEXT_TYPE := 'PARTY';
3068 l_note_context_rec.NOTE_CONTEXT_TYPE_ID := pI.contact_party_id;
3072
3069 end if;
3070 end if;
3071 --Code modified for bug13954536(11906160) --End
3073
3074 l_note_context_rec.NOTE_CONTEXT_TYPE_ID := pI.party_id;
3075 l_note_context_rec.LAST_UPDATE_DATE := SYSDATE;
3076 l_note_context_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3077 l_note_context_rec.CREATION_DATE := SYSDATE;
3078 l_note_context_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3079 l_note_context_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.USER_ID;
3080
3081 l_note_context_rec_tbl(2) := l_note_context_rec;
3082
3083 -- Call Jtf_notes_pub.create_note()
3084
3085 JTF_NOTES_PUB.Create_Note (
3086 p_parent_note_id => NULL
3087 , p_jtf_note_id => NULL
3088 , p_api_version => 1.0
3089 , p_init_msg_list => 'T'
3090 , p_commit => 'F'
3091 , p_validation_level => 100
3092 , x_return_status => G_return_status
3093 , x_msg_count => G_mesg_count
3094 , x_msg_data => l_msg_data
3095 , p_org_id => NULL
3096 , p_source_object_id => pI.sales_lead_id
3097 , p_source_object_code => 'LEAD'
3098 , p_notes => pI.lead_note
3099 , p_notes_detail => NULL --EMPTY_CLOB()
3100 , p_note_status => NULL
3101 , p_entered_by => FND_GLOBAL.USER_ID
3102 , p_entered_date => SYSDATE
3103 , x_jtf_note_id => l_jtf_note_id
3104 , p_last_update_date => SYSDATE
3105 , p_last_updated_by => FND_GLOBAL.USER_ID
3106 , p_creation_date => SYSDATE
3107 , p_created_by => FND_GLOBAL.USER_ID
3108 , p_last_update_login => FND_GLOBAL.USER_ID
3109 , p_attribute1 => NULL
3110 , p_attribute2 => NULL
3111 , p_attribute3 => NULL
3112 , p_attribute4 => NULL
3113 , p_attribute5 => NULL
3114 , p_attribute6 => NULL
3115 , p_attribute7 => NULL
3116 , p_attribute8 => NULL
3117 , p_attribute9 => NULL
3118 , p_attribute10 => NULL
3119 , p_attribute11 => NULL
3120 , p_attribute12 => NULL
3121 , p_attribute13 => NULL
3122 , p_attribute14 => NULL
3123 , p_attribute15 => NULL
3124 , p_context => NULL
3125 , p_note_type => NVL(pI.note_type,'AS_USER')
3126 , p_jtf_note_contexts_tab => l_note_context_rec_tbl
3127 );
3128
3129 write_log(3, 'do_create_LeadNoteAndContext:End - Note_id - '||to_char(l_jtf_note_id));
3130
3131 End do_create_LeadNoteAndContext;
3132
3133 ----------------------------------------------------------
3134 -- name: do_update_party
3135 -- scope: private
3136 -- calls
3137 ----------------------------------------------------------
3138 procedure do_update_party(
3139 pI IN OUT NOCOPY leadImpType,
3140 G_return_status OUT NOCOPY varchar2)
3141 IS
3142 l_org_rec HZ_PARTY_V2PUB.organization_rec_type;
3143 l_hz_partyNumber number;
3144 l_hz_profile number;
3145 l_msg_data VARCHAR2(2000);
3146 l_per_rec HZ_PARTY_V2PUB.person_rec_type;
3147 l_osysref varchar2(240) := Null;
3148
3149 Begin
3150
3151 -- l_org_rec.party_rec.party_id := pI.hz_partyId;
3152 -- l_per_rec.party_rec.party_id := pI.hz_partyId;
3153
3154 IF (pI.orig_system_reference is not null) or
3155 (pI.orig_system_reference <> FND_API.G_MISS_CHAR)
3156 THEN
3157 -- l_org_rec.party_rec.orig_system_reference:= pI.orig_system_reference;
3158 -- l_per_rec.party_rec.orig_system_reference:= pI.orig_system_reference;
3159 l_osysref := pI.orig_system_reference;
3160 else
3161 -- l_org_rec.party_rec.orig_system_reference := pI.import_interface_id;
3162 -- l_per_rec.party_rec.orig_system_reference := pI.import_interface_id;
3163 l_osysref := pI.import_interface_id;
3164 end if;
3165
3166 -- The following lines were commented as this is erroring out
3167 -- and not supported by hz_party_pub ARHPTYSB.pls 115.72
3168 -- Rashmi Goyal sent an email stating that this is a non updatable column.
3169 -- Until that gets fixed, it was decided by Sr Management that we update
3170 -- the table directly.
3171 update hz_parties
3172 set orig_system_reference = l_osysref
3173 where party_id = pI.party_id;
3174
3175 /* ***
3176 If (pI.party_type = 'ORGANIZATION') then
3177 hz_party_pub.update_organization (
3178 p_api_version => G_api_version,
3179 p_init_msg_list => FND_API.G_FALSE,
3180 p_commit => FND_API.G_FALSE,
3181 p_organization_rec => l_org_rec,
3182 p_party_last_update_date => pI.last_update_date,
3183 x_return_status => G_return_status,
3184 x_msg_count => G_MESG_COUNT,
3185 x_msg_data => l_msg_data,
3186 x_profile_id => l_hz_profile,
3187 p_validation_level => FND_API.G_VALID_LEVEL_FULL
3188 );
3189 elsif (pI.party_type ='PERSON') then
3190 hz_party_pub.update_person (
3191 p_api_version => G_api_version,
3192 p_init_msg_list => FND_API.G_FALSE,
3193 p_commit => FND_API.G_FALSE,
3194 p_person_rec => l_per_rec,
3195 p_party_last_update_date => pI.last_update_date,
3196 x_profile_id => l_hz_profile,
3197 x_return_status => G_return_status,
3198 x_msg_count => G_MESG_COUNT,
3199 x_msg_data => l_msg_data,
3200 p_validation_level => FND_API.G_VALID_LEVEL_FULL
3201 );
3202 else
3203 null;
3204 end if;
3205 *** */
3206 END do_update_party;
3210 -- Scope: Public
3207
3208 ----------------------------------------------------------
3209 -- Name: trans_custkey
3211 -- Select customer key from HZ_PARTIES when osysref matches
3212 ----------------------------------------------------------
3213 function trans_custkey (p_osysref IN Varchar2) Return Varchar2
3214 IS
3215 l_tmp hz_parties.customer_key%type;
3216 BEGIN
3217 Select customer_key into l_tmp
3218 from hz_parties hzp
3219 where hzp.orig_system_reference = p_osysref
3220 and hzp.status = 'A'
3221 and rownum < 2;
3222 Return l_tmp;
3223
3224 exception when others then
3225 Return Null;
3226 END trans_custkey;
3227
3228
3229 ----------------------------------------------------------
3230 -- Name: party_echeck
3231 -- Scope: Public
3232 -- Existence checking for parties in the same batch
3233 ----------------------------------------------------------
3234 procedure party_echeck(p_imp_id IN Number,
3235 p_party_id IN OUT NOCOPY Number,
3236 p_plupd_date IN OUT NOCOPY Date,
3237 p_psite_id IN OUT NOCOPY Number,
3238 p_loc_id IN OUT NOCOPY Number)
3239 IS
3240 Cursor exists_party (p_interface_id Number) IS
3241 select decode(p2.address_key,l.address_key,0,1)+
3242 decode(p2.country,l.country,0,2) match_rank,
3243 p2.party_id, p2.customer_key ,p2.address_key,
3244 p2.country, p2.identifying_address_flag,
3245 max(p2.party_id) mparty_id,
3246 max(p2.party_site_id) party_site_id,
3247 max(p2.location_id) location_id
3248 from (select s.customer_key, p.party_id,
3249 p.last_update_date, s.address_key, s.country,
3250 nvl(ps.identifying_address_flag, 'N')
3251 identifying_address_flag,
3252 ps.location_id, ps.party_site_id
3253 from as_import_interface s, hz_parties p, hz_party_sites ps
3254 where s.load_status = 'NEW'
3255 and s.import_interface_id = p_interface_id
3256 and p.customer_key (+) = s.customer_key
3257 and p.party_type (+) = s.party_type
3258 and p.status (+) = 'A'
3259 and ps.status (+) = 'A'
3260 and ps.party_id (+) = p.party_id) p2,
3261 hz_locations l
3262 where l.country (+) = p2.country
3263 and l.location_id (+) = p2.location_id
3264 group by decode(p2.address_key,l.address_key,0,1)+
3265 decode(p2.country,l.country,0,2),
3266 p2.party_id, p2.identifying_address_flag,
3267 p2.customer_key, p2.address_key, p2.country
3268 order by match_rank asc, identifying_address_flag desc,
3269 party_id desc;
3270
3271 l_country hz_locations.country%Type;
3272 l_akey hz_locations.address_key%Type;
3273 BEGIN
3274 p_plupd_date := Null;
3275 If (p_imp_id is Null) then
3276 return;
3277 End If;
3278 If not (p_party_id is not null and p_psite_id is not null
3279 and p_loc_id is not null)
3280 then
3281 For I in exists_party (p_imp_id) Loop
3282 If I.match_rank = 0 then
3283 if (p_party_id is null or (p_party_id is not null
3284 and p_party_id = I.mparty_id))
3285 then
3286 p_psite_id := nvl(p_psite_id, I.party_site_id);
3287 p_loc_id := nvl(p_loc_id, I.location_id);
3288 end if;
3289 End If;
3290 p_party_id := nvl(p_party_id, I.mparty_id);
3291 l_akey := I.address_key;
3292 l_country := I.country;
3293 Exit;
3294 End Loop;
3295 if (p_loc_id is null) then
3296 select max(location_id) into p_loc_id
3297 from hz_locations
3298 where address_key = l_akey and country = l_country;
3299 End if;
3300 End If;
3301 if (p_party_id is not null) then
3302 select last_update_date into p_plupd_date
3303 from hz_parties
3304 where party_id = p_party_id and rownum = 1;
3305 End If;
3306
3307 exception when others then
3308 Null;
3309 end party_echeck;
3310
3311 ----------------------------------------------------------
3312 -- Name: is_duplicate_lead
3313 -- Scope: Public
3314 -- Sales Lead existence checking
3315 ----------------------------------------------------------
3316 FUNCTION is_duplicate_lead (pI IN leadImpType) RETURN BOOLEAN
3317 IS
3318 l_call_user_hook BOOLEAN;
3319 l_duplicate_flag VARCHAR2(1);
3320 l_return_status VARCHAR2(1);
3321 l_msg_count NUMBER;
3322 l_msg_data VARCHAR2(2000);
3323 BEGIN
3324 -- SOLIN, add customer user hook
3325 -- USER HOOK standard : customer pre-processing section - mandatory
3326 -- l_call_user_hook := JTF_USR_HKS.Ok_to_execute('AS_IMPORT_SL_PVT','IS_DUPLICATE_LEAD','B','C');
3327
3328 IF G_CALL_USER_HOOK
3329 THEN
3330 write_log(3, 'Call user_hook is true');
3331 AS_IMPORT_SL_CUHK.Is_Duplicate_Lead_Pre(
3332 p_api_version_number => 2.0,
3333 p_init_msg_list => FND_API.G_FALSE,
3334 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3335 p_commit => FND_API.G_FALSE,
3336 p_import_interface_id => pI.import_interface_id,
3337 x_duplicate_flag => l_duplicate_flag,
3341
3338 x_return_status => l_return_status,
3339 x_msg_count => l_msg_count,
3340 x_msg_data => l_msg_data);
3342 write_log(3, 'x_duplicate_flag=' || l_duplicate_flag);
3343 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3344 RAISE FND_API.G_EXC_ERROR;
3345 END IF;
3346
3347 IF l_duplicate_flag = 'Y' THEN
3348 RETURN TRUE;
3349 ELSE
3350 RETURN FALSE;
3351 END IF;
3352 ELSE
3353 write_log(3, 'Call user_hook is false');
3354 END IF;
3355 -- end SOLIN
3356
3357 -- return FALSE for current implementation
3358 RETURN FALSE;
3359 END is_duplicate_lead;
3360
3361
3362 ----------------------------------------------------------
3363 -- Name: TCA_DQM_processing
3364 -- Scope: Private
3365 -- Using DQM check for existance of TCA entities
3366 -- If they do not exist in TCA repository then create new
3367 ----------------------------------------------------------
3368
3369 procedure TCA_DQM_processing (I IN OUT NOCOPY leadImpType, l_create_party OUT NOCOPY VARCHAR2, l_create_party_site OUT NOCOPY VARCHAR2,
3370 l_create_contact OUT NOCOPY VARCHAR2, -- l_create_contact_point OUT NOCOPY VARCHAR2,
3371 l_create_location OUT NOCOPY VARCHAR2, ld_phone OUT NOCOPY VARCHAR2,
3372 ld_fax OUT NOCOPY VARCHAR2, ld_email OUT NOCOPY VARCHAR2, ld_url OUT NOCOPY VARCHAR2)
3373 IS
3374
3375 -- Declare Variables for passing search criteria
3376
3377 -- Pass Party search criteria in this variable
3378 party_cond HZ_PARTY_SEARCH.PARTY_SEARCH_REC_TYPE;
3379 -- Pass Party Site search criteria in this variable
3380 party_site_cond HZ_PARTY_SEARCH.PARTY_SITE_LIST;
3381 -- Pass Contact search criteria in this variable
3382 contact_cond HZ_PARTY_SEARCH.CONTACT_LIST;
3383 -- Pass Contact Point search criteria in this variable
3384 contact_point_cond HZ_PARTY_SEARCH.CONTACT_POINT_LIST;
3385
3386 -- The Match Rule to use for the dup identification.
3387 l_rule_id NUMBER;
3388
3389 -- The Search Context ID returned by the API.
3390 l_search_context_id NUMBER;
3391
3392 -- Other OUT parameters returned by the API.
3393 l_return_status VARCHAR2(1);
3394 l_msg_count NUMBER;
3395 l_msg_data VARCHAR2(2000);
3396
3397 -- API also returns the number of matches.
3398 l_num_matches NUMBER;
3399
3400 -- Local variables
3401 l_org_contact_id NUMBER(15);
3402 l_party_id NUMBER(15);
3403 l_party_site_id NUMBER(15);
3404 l_contact_point_id NUMBER(15);
3405 l_creation_date DATE;
3406 l_score NUMBER;
3407
3408 l_dup_phone varchar2(1):= 'N';
3409 l_dup_email varchar2(1):= 'N';
3410 l_dup_fax varchar2(1):= 'N';
3411 l_dup_url varchar2(1):= 'N';
3412 l_contact_provided varchar2(1) := 'N';
3413
3414 l_orig_sys_party_found VARCHAR2(1);
3415 l_identifying_addr_flag VARCHAR2(1);
3416 l_index NUMBER(2);
3417
3418 -- SOLIN, BUG 3528579
3419 l_activate_flag VARCHAR2(1);
3420 l_status VARCHAR2(1);
3421 l_object_version_number NUMBER;
3422 l_profile_id NUMBER;
3423 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
3424 l_organization_rec HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
3425 -- SOLIN, BUG 3528579 end
3426
3427 --l_restrict_sql VARCHAR2(4000);
3428
3429 -- Cursor for getting matched party_id from hz_matched_parties_gt
3430 CURSOR C_matched_party(p_party_type VARCHAR2)
3431 IS
3432 SELECT HZMP.party_id, score, hzp.creation_date
3433 FROM HZ_MATCHED_PARTIES_GT hzmp, HZ_PARTIES hzp
3434 WHERE search_context_id = l_search_context_id
3435 AND hzp.party_id = hzmp.party_id
3436 AND hzp.party_type = p_party_type
3437 AND nvl(hzp.status,'A') = 'A' --bug# 3319259
3438 ORDER BY score desc, hzp.creation_date desc;
3439
3440 -- SOLIN, Bug 3528579
3441 -- Cursor for getting matched party_id, ignore party status
3442 CURSOR C_matched_party2(p_party_type VARCHAR2)
3443 IS
3444 SELECT HZMP.party_id, score, hzp.creation_date, hzp.status
3445 , hzp.object_version_number
3446 FROM HZ_MATCHED_PARTIES_GT hzmp, HZ_PARTIES hzp
3447 WHERE search_context_id = l_search_context_id
3448 AND hzp.party_id = hzmp.party_id
3449 AND hzp.party_type = p_party_type
3450 ORDER BY score desc, hzp.creation_date desc;
3451
3452 -- Cursor for getting matched party_site from hz_matched_party_sites_gt
3453 CURSOR C_matched_party_sites
3454 IS
3455 SELECT hzmps.party_id, hzmps.party_site_id, score , hzps.creation_date
3456 FROM hz_matched_party_sites_gt hzmps, hz_party_sites hzps
3457 WHERE search_context_id = l_search_context_id
3458 AND hzps.party_site_id = hzmps.party_site_id
3459 AND hzps.party_id = hzmps.party_id
3460 AND nvl(hzps.status,'A') = 'A' --bug# 3319259
3461 ORDER BY score desc, hzps.creation_date desc;
3462
3463 -- Cursor for getting matched contacts from hz_matched_contacts_gt
3464 CURSOR C_matched_contacts
3465 IS
3466 SELECT hzmc.party_id, hzmc.org_contact_id, score , hzoc.creation_date
3467 FROM hz_matched_contacts_gt hzmc, hz_org_contacts hzoc
3468 WHERE search_context_id = l_search_context_id
3469 AND hzmc.org_contact_id = hzoc.org_contact_id
3470 AND nvl(hzoc.status,'A') = 'A' --bug# 3319259
3471 ORDER BY score desc, hzoc.creation_date desc;
3472
3473 -- Cursor for getting matched contact_point from hz_matched_cpts_gt
3474 CURSOR C_matched_contact_points(cp_type VARCHAR2, p_plt VARCHAR2)
3475 IS
3479 AND hzmcp.contact_point_id = hzcp.contact_point_id
3476 SELECT hzmcp.party_id, hzmcp.contact_point_id, score , hzcp.creation_date
3477 FROM hz_matched_cpts_gt hzmcp, hz_contact_points hzcp
3478 WHERE search_context_id = l_search_context_id
3480 AND hzcp.contact_point_type = cp_type
3481 AND nvl(hzcp.phone_line_type,'xx') = nvl(p_plt,'xx')
3482 AND nvl(hzcp.status,'A') = 'A' --bug# 3319259
3483 ORDER BY score desc, hzcp.creation_date desc;
3484
3485 -- Cursor for getting contact_party_id and rel_party_id
3486 CURSOR C_get_contact_info
3487 IS
3488 SELECT decode(subject_type,'PERSON',subject_id, object_id) contact_party_id, party_id
3489 FROM hz_org_contacts hzoc, hz_relationships hzr
3490 WHERE hzoc.org_contact_id = l_org_contact_id
3491 AND hzr.relationship_id = hzoc.party_relationship_id
3492 and hzr.relationship_code = 'CONTACT_OF';
3493
3494 -- Cursor for Orig_system_reference dup check
3495 CURSOR c_check_orig_sys_ref(p_orig_system_ref varchar)
3496 IS
3497 SELECT party_id
3498 FROM hz_parties hzp
3499 WHERE hzp.orig_system_reference = p_orig_system_ref
3500 AND nvl(hzp.status,'A') = 'A';
3501 -- AND rownum < 2;
3502
3503 -- Cursor for getting party_site_id given the party_id
3504 CURSOR C_get_party_site_id(p_party_id number)
3505 IS
3506 SELECT party_site_id, nvl(identifying_address_flag,'N')
3507 FROM hz_party_sites
3508 WHERE party_id = p_party_id
3509 AND nvl(start_date_active,sysdate) <= sysdate
3510 AND nvl(end_date_active,sysdate) >= sysdate
3511 ORDER BY nvl(identifying_address_flag,'N') DESC;
3512
3513 -- Cursor for getting rel_party_id
3514 CURSOR C_get_rel_party_id(p_contact_party_id number, p_party_id number)
3515 IS
3516 SELECT party_id
3517 FROM hz_relationships hzr
3518 WHERE hzr.relationship_code in ('CONTACT_OF','EMPLOYEE_OF')
3519 AND subject_id in (p_contact_party_id, p_party_id)
3520 AND object_id in (p_contact_party_id, p_party_id)
3521 AND hzr.status = 'A'
3522 AND nvl(hzr.start_date,sysdate) <= sysdate
3523 AND nvl(hzr.end_date,sysdate) >= sysdate;
3524
3525 BEGIN
3526
3527 l_create_party := 'N';
3528 -- l_create_contact_point := 'N';
3529 l_create_party_site := 'N';
3530 l_create_location := 'N';
3531 l_create_contact := 'N';
3532
3533 l_dup_phone := 'N';
3534 l_dup_email := 'N';
3535 l_dup_fax := 'N';
3536 l_dup_url := 'N';
3537
3538
3539 -- Data Assignment to DQM datatypes
3540
3541 -- 1. Pass Party search criteria in party_cond
3542
3543 party_cond.party_type := I.party_type;
3544 IF I.party_type = 'ORGANIZATION' THEN
3545 party_cond.party_name := I.customer_name;
3546 ELSIF I.party_type = 'PERSON' THEN
3547 party_cond.party_name := I.first_name||' '||I.last_name;
3548 END IF;
3549 party_cond.party_all_names := party_cond.party_name;
3550
3551 party_cond.party_number := I.party_number;
3552 party_cond.duns_number_c := I.duns_number_c;
3553 party_cond.tax_reference := I.tax_reference;
3554 party_cond.person_name := I.first_name||' '||I.last_name;
3555 party_cond.person_first_name := I.first_name;
3556 party_cond.person_last_name := I.last_name;
3557 party_cond.person_initials := I.person_initials;
3558 party_cond.person_name := I.first_name||' '||I.last_name;
3559 party_cond.sic_code := I.sic_code;
3560 party_cond.sic_code_type := I.sic_code_type;
3561 party_cond.category_code := I.customer_category_code;
3562 party_cond.year_established := I.year_established;
3563 party_cond.employees_total := I.num_of_employees;
3564 party_cond.curr_fy_potential_revenue := I.potential_revenue_curr_fy;
3565 party_cond.next_fy_potential_revenue := I.potential_revenue_next_fy;
3566 party_cond.tax_reference := I.tax_reference;
3567 party_cond.tax_name := I.tax_name;
3568 party_cond.salutation := I.salutation;
3569 party_cond.organization_name_phonetic := I.organization_name_phonetic;
3570
3571 -- 2. Pass Party Site search criteria in party_site_cond
3572 party_site_cond(1).address1 := I.address1;
3573 party_site_cond(1).address2 := I.address2;
3574 party_site_cond(1).address3 := I.address3;
3575 party_site_cond(1).address4 := I.address4;
3576 party_site_cond(1).country := I.country;
3577 party_site_cond(1).city := I.city;
3578 party_site_cond(1).province := I.province;
3579 party_site_cond(1).postal_code := I.postal_code;
3580 -- SOLIN, bug 4633401
3581 -- add space between address?
3582 party_site_cond(1).address := I.address1 || ' ' || I.address2 || ' '
3583 || I.address3 || ' ' || I.address4;
3584 -- SOLIN, end
3585 party_site_cond(1).state := I.state;
3586 party_site_cond(1).county := I.county;
3587 party_site_cond(1).party_site_name := I.party_site_name;
3588 party_site_cond(1).party_site_number := I.party_site_number;
3589 --party_site_cond(1).floor := I.floor;
3590 --party_site_cond(1).house_number := I.house_number;
3591 --party_site_cond(1).po_box_number := I.po_box_number;
3592 party_site_cond(1).position := I.position;
3593 party_site_cond(1).postal_plus4_code := I.postal_plus4_code;
3594 --party_site_cond(1).street := I.street;
3595 --party_site_cond(1).street_suffix := I.street_suffix;
3596 --party_site_cond(1).street_number := I.street_number;
3597 --party_site_cond(1).suite := I.suite;
3598 party_site_cond(1).address_effective_date := I.address_effective_date;
3599 party_site_cond(1).mailstop := I.mailstop;
3600 party_site_cond(1).address_lines_phonetic := I.address_lines_phonetic;
3601
3605 contact_cond(1).person_name := I.first_name||' '||I.last_name;
3602 -- 3. Pass Contact search criteria in contact_cond
3603 contact_cond(1).contact_name := I.first_name||' '||I.last_name;
3604 contact_cond(1).contact_number := I.contact_number;
3606 contact_cond(1).person_first_name := I.first_name;
3607 contact_cond(1).person_last_name := I.last_name;
3608 contact_cond(1).person_initials := I.person_initials;
3609 contact_cond(1).job_title := I.job_title;
3610 contact_cond(1).job_title_code := I.job_title_code;
3611 contact_cond(1).mail_stop := I.mail_stop;
3612 contact_cond(1).content_source_type := I.content_source_type;
3613 contact_cond(1).person_first_name_phonetic := I.person_first_name_phonetic;
3614 contact_cond(1).person_last_name_phonetic := I.person_last_name_phonetic;
3615 contact_cond(1).person_name_suffix := I.person_name_suffix;
3616 contact_cond(1).person_previous_last_name := I.person_previous_last_name;
3617
3618 -- 4. Pass Contact Point search criteria in contact_point_cond
3619 l_index := 1;
3620 IF I.email_address IS NOT NULL THEN
3621 contact_point_cond(l_index).CONTACT_POINT_TYPE := 'EMAIL';
3622 contact_point_cond(l_index).EMAIL_ADDRESS := I.email_address;
3623 contact_point_cond(l_index).EMAIL_FORMAT := I.email_format;
3624 l_index := l_index + 1;
3625 END IF;
3626 IF I.phone_number IS NOT NULL THEN
3627 contact_point_cond(l_index).CONTACT_POINT_TYPE := 'PHONE';
3628 contact_point_cond(l_index).PHONE_NUMBER := I.phone_number;
3629 contact_point_cond(l_index).PHONE_LINE_TYPE := nvl(I.phone_type,'GEN');
3630 contact_point_cond(l_index).PHONE_AREA_CODE := I.area_code;
3631 contact_point_cond(l_index).PHONE_EXTENSION := I.extension;
3632 contact_point_cond(l_index).PHONE_COUNTRY_CODE := I.phone_country_code;
3633
3634 contact_point_cond(l_index).raw_phone_number:= I.phone_country_code||I.area_code||I.phone_number;
3635 contact_point_cond(l_index).flex_format_phone_number:= I.phone_country_code||I.area_code||I.phone_number;
3636
3637 l_index := l_index + 1;
3638 END IF;
3639 IF I.fax_number IS NOT NULL THEN
3640 contact_point_cond(l_index).CONTACT_POINT_TYPE := 'PHONE';
3641 contact_point_cond(l_index).PHONE_LINE_TYPE := 'FAX';
3642 contact_point_cond(l_index).PHONE_NUMBER := I.fax_number;
3643 contact_point_cond(l_index).PHONE_AREA_CODE := I.fax_area_code;
3644 contact_point_cond(l_index).PHONE_EXTENSION := I.fax_extension;
3645 contact_point_cond(l_index).PHONE_COUNTRY_CODE := I.fax_country_code;
3646
3647 --bmuthukr modified the following code to pass fax# details to fix bug 3748665
3648 --contact_point_cond(l_index).raw_phone_number:= I.phone_country_code||I.area_code||I.phone_number;
3649 --contact_point_cond(l_index).flex_format_phone_number:= I.phone_country_code||I.area_code||I.phone_number;
3650 contact_point_cond(l_index).raw_phone_number:= I.fax_country_code||I.fax_area_code||I.fax_number;
3651 contact_point_cond(l_index).flex_format_phone_number:= I.fax_country_code||I.fax_area_code||I.fax_number;
3652 --Ends changes..
3653
3654 l_index := l_index + 1;
3655 END IF;
3656 IF I.url IS NOT NULL THEN
3657 contact_point_cond(l_index).CONTACT_POINT_TYPE := 'WEB';
3658 contact_point_cond(l_index).WEB_TYPE := 'http';
3659 contact_point_cond(l_index).URL := I.url;
3660 END IF;
3661
3662 IF I.party_id IS NULL THEN --Skip party existance check if provided in import
3663
3664 --Orig System Regerence check
3665 l_orig_sys_party_found := 'N';
3666
3667 IF I.orig_system_reference IS NOT NULL THEN
3668
3669 OPEN c_check_orig_sys_ref(I.orig_system_reference);
3670 FETCH c_check_orig_sys_ref INTO l_party_id;
3671 IF c_check_orig_sys_ref%NOTFOUND THEN
3672 l_orig_sys_party_found := 'N';
3673 ELSE
3674 l_orig_sys_party_found := 'Y';
3675 END IF;
3676 CLOSE c_check_orig_sys_ref;
3677 /*
3678 l_restrict_sql := null;
3679 FOR osr IN c_check_orig_sys_ref(I.orig_system_reference) LOOP
3680 l_restrict_sql := l_restrict_sql || to_char(osr.party_id) ||',';
3681 END LOOP;
3682
3683 IF l_restrict_sql IS NULL THEN
3684 l_orig_sys_party_found := 'N';
3685 ELSE
3686 l_orig_sys_party_found := 'Y';
3687 l_restrict_sql := ' party_id in ('|| l_restrict_sql ||'0) ';
3688 END IF;
3689 */
3690 END IF;
3691
3692 l_party_id := null;
3693
3694 ----- Begin PARTY SEARCH -----
3695 -- Get rule_id from profile
3696 IF I.party_type = 'ORGANIZATION' THEN
3697 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_PARTY'));
3698 ELSIF I.party_type = 'PERSON' THEN
3699 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_PERSON'));
3700 ELSE
3701 --else bad party_type
3702 AS_UTILITY_PVT.Set_Message(
3703 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
3704 p_msg_name => 'AS_INVALID_PARTY_TYPE',
3705 p_token1 => 'VALUE',
3706 p_token1_value => I.party_type);
3707 write_log (3, 'Party_type is invalid');
3708 RAISE FND_API.G_EXC_ERROR;
3709 END IF;
3710
3711 write_log(3,'#1 :: Calling FIND_PARTIES with OSR found = '||l_orig_sys_party_found);
3712 write_log(3,'rule_id '||to_char(l_rule_id));
3713
3714 IF l_orig_sys_party_found = 'N' THEN
3715 -- Full search
3716 HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond, NULL,
3717 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3718 ELSE
3722 -- Restricted search by passing p_restrict_sql
3719 -- l_restrict_sql := ' party_id in (select party_id from hz_parties where orig_system_reference = '''||I.orig_system_reference||''')';
3720 write_log(3,'In Restrict sql');
3721
3723 HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond,
3724 '/* SELECTIVE */ party_id in (select party_id from hz_parties where ORIG_SYSTEM_REFERENCE = '''||I.orig_system_reference||''') ',
3725 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3726
3727 IF l_num_matches = 0 THEN
3728 -- Full search
3729 write_log(3,'performing full search');
3730 HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond, NULL,
3731 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3732 END IF;
3733 END IF;
3734
3735 IF l_return_status <> FND_API.g_ret_sts_success THEN
3736 RAISE FND_API.g_exc_error;
3737 END IF;
3738
3739 write_log(3,'After find_parties matches '||to_char(l_num_matches));
3740
3741 IF l_num_matches > 0 THEN
3742 -- A possible duplicate has been found.
3743 -- Get the party id the matched parties having highest score.
3744 -- SOLIN, Bug 3528579
3745 l_activate_flag := NVL(FND_PROFILE.value('AS_ACTIVATE_PARTIES_FROM_IMPORT'), 'N');
3746 IF l_activate_flag = 'N'
3747 THEN
3748 OPEN C_matched_party(I.party_type);
3749 FETCH C_matched_party INTO l_party_id, l_score, l_creation_date;
3750 CLOSE C_matched_party;
3751 ELSE
3752 OPEN C_matched_party2(I.party_type);
3753 FETCH C_matched_party2 INTO l_party_id, l_score, l_creation_date,
3754 l_status, l_object_version_number;
3755 CLOSE C_matched_party2;
3756
3757 -- activate the party if it's inactive
3758 IF l_status = 'I'
3759 THEN
3760 write_log(3, 'Activating party ' || l_party_id);
3761 IF I.party_type = 'ORGANIZATION'
3762 THEN
3763 l_organization_rec.party_rec.party_id := l_party_id;
3764 l_organization_rec.party_rec.status := 'A';
3765 HZ_PARTY_V2PUB.update_organization(
3766 p_init_msg_list => FND_API.G_FALSE,
3767 p_organization_rec => l_organization_rec,
3768 p_party_object_version_number => l_object_version_number,
3769 x_profile_id => l_profile_id,
3770 x_return_status => l_return_status,
3771 x_msg_count => l_MSG_COUNT,
3772 x_msg_data => l_msg_data
3773 );
3774 ELSIF I.party_type = 'PERSON'
3775 THEN
3776 l_person_rec.party_rec.party_id := l_party_id;
3777 l_person_rec.party_rec.status := 'A';
3778 HZ_PARTY_V2PUB.update_person(
3779 p_init_msg_list => FND_API.G_FALSE,
3780 p_person_rec => l_person_rec,
3781 p_party_object_version_number => l_object_version_number,
3782 x_profile_id => l_profile_id,
3783 x_return_status => l_return_status,
3784 x_msg_count => l_MSG_COUNT,
3785 x_msg_data => l_msg_data
3786 );
3787 END IF;
3788 write_log(3, 'l_return_status=' || l_return_status
3789 || ',l_msg_data=' || l_msg_data);
3790 END IF; -- l_status = 'I'
3791 END IF; -- l_activate_flag = 'N'
3792 write_log(3, 'Matched party - '||to_char(l_party_id)||' score '
3793 ||to_char(l_score)||' created '||to_char(l_creation_date)
3794 || 'status=' || l_status || ' activate?' || l_activate_flag);
3795 -- SOLIN, Bug 3528579 end
3796 I.party_id := l_party_id; --assign the matched party_id
3797 IF I.party_id IS NOT NULL THEN
3798 l_create_party := 'N';
3799 ELSE
3800 l_create_party := 'Y';
3801 END IF;
3802 ELSE
3803 write_log(3,'No party match found !');
3804 l_create_party := 'Y';
3805 END IF;
3806
3807 ----- End PARTY SEARCH -----
3808 ELSE -- party_id Provided
3809 l_create_party := 'N';
3810 write_log(3,'Skip party echeck - Party_id provided - '||to_char(I.party_id));
3811 END IF; --If party_id IS NULL
3812
3813 ----- Begin PARTY SITE SEARCH -----
3814
3815 IF I.party_id IS NOT NULL THEN -- do party_site search based on that party_id
3816 -- get party_site_id in case no address info is provided bug# 2760262
3817 IF I.party_site_id IS NULL AND I.address1 IS NULL THEN
3818 OPEN C_get_party_site_id(I.party_id);
3819 FETCH C_get_party_site_id INTO I.party_site_id, l_identifying_addr_flag;
3820 CLOSE C_get_party_site_id;
3821 END IF;
3822
3823 IF I.party_site_id IS NULL AND I.address1 IS NOT NULL THEN
3824 write_log(3,'#2 :: Calling GET_MATCHING_PARTY_SITES with party_id: '||to_char(I.party_id));
3825
3826 -- SOLIN, Bug 4942209
3827 -- create a new rule profile for DQM party site match
3828 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_PARTY_SITE'));
3829 HZ_PARTY_SEARCH.get_matching_party_sites ('T',l_rule_id, I.party_id, party_site_cond,
3833 RAISE FND_API.g_exc_error;
3830 contact_point_cond, l_search_context_id, l_return_status, l_msg_count, l_msg_data);
3831
3832 IF l_return_status <> FND_API.g_ret_sts_success THEN
3834 END IF;
3835
3836 OPEN C_matched_party_sites;
3837 FETCH C_matched_party_sites INTO l_party_id, l_party_site_id, l_score, l_creation_date;
3838 IF C_matched_party_sites%FOUND THEN
3839 write_log(3,'Matched party_site - '||to_char(l_party_site_id)|| ' score '||to_char(l_score)||' created '||to_char(l_creation_date));
3840 I.party_site_id := l_party_site_id;
3841 SELECT location_id INTO I.location_ID
3842 FROM hz_party_sites
3843 WHERE party_site_id = I.party_site_id;
3844 l_create_party_site := 'N';
3845 l_create_location := 'N';
3846 ELSE
3847 write_log(3,'No party_site match found !');
3848 l_create_party_site := 'Y';
3849 l_create_location := 'Y';
3850 END IF;
3851 CLOSE C_matched_party_sites;
3852
3853 ELSE -- IF party_site_id is not null
3854 IF I.location_id IS NULL AND I.party_site_id IS NOT NULL THEN
3855 SELECT location_id INTO I.location_ID
3856 FROM hz_party_sites
3857 WHERE party_site_id = I.party_site_id;
3858 END IF;
3859 l_create_party_site := 'N';
3860 l_create_location := 'N';
3861 write_log(3,'Skip party_site echeck - party_site_id provided- '||to_char(I.party_site_id));
3862 END IF;
3863 ELSE --if party_id is null then
3864 l_create_party_site := 'Y';
3865 l_create_location := 'Y';
3866 END IF;
3867
3868 ----- End PARTY SITE SEARCH -----
3869
3870 ----- Begin CONTACT SEARCH -----
3871
3872 IF party_cond.party_type = 'ORGANIZATION' THEN --1
3873 IF I.contact_party_id IS NULL THEN --2
3874 IF I.first_name IS NOT NULL AND I.last_name IS NOT NULL THEN --3
3875 IF I.party_id IS NOT NULL THEN --4
3876 write_log(3,'#3 :: Calling GET_MATCHING_CONTACTS with party_id: '||to_char(I.party_id));
3877
3878 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_CONTACT'));
3879 HZ_PARTY_SEARCH.get_matching_contacts('T',l_rule_id, I.party_id, contact_cond, contact_point_cond,
3880 l_search_context_id, l_return_status, l_msg_count, l_msg_data);
3881
3882 IF l_return_status <> FND_API.g_ret_sts_success THEN
3883 RAISE FND_API.g_exc_error;
3884 END IF;
3885
3886 OPEN C_matched_contacts;
3887 FETCH C_matched_contacts INTO l_party_id, l_org_contact_id, l_score, l_creation_date;
3888 IF C_matched_contacts%FOUND THEN
3889 OPEN C_get_contact_info;
3890 FETCH C_get_contact_info INTO I.contact_party_id, I.rel_party_id;
3891 CLOSE C_get_contact_info;
3892 IF I.party_id is null THEN
3893 I.rel_party_id := null; --create new relationship as reusing contact
3894 END IF;
3895 write_log(3,'Matched contact_party_id - '||to_char(I.contact_party_id)|| ' score '||to_char(l_score)||' created '||to_char(l_creation_date));
3896 l_create_contact := 'N';
3897 ELSE
3898 l_create_contact := 'Y';
3899 write_log(3,'No contact match found !');
3900 END IF;
3901 CLOSE C_matched_contacts;
3902 END IF; --4 party_id is not null
3903
3904 IF I.contact_party_id IS NULL THEN --4
3905 -- Blind search for matching person in TCA
3906 write_log(3, 'Contact blind search begin: using FIND_PARTIES (Person)');
3907
3908 --Reset values for person search
3909 party_cond.party_type := 'PERSON';
3910 party_cond.party_name := I.first_name||' '||I.last_name;
3911
3912 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_PERSON'));
3913 HZ_PARTY_SEARCH.find_parties ('T',l_rule_id, party_cond, party_site_cond, contact_cond , contact_point_cond, NULL,
3914 'N',l_search_context_id, l_num_matches, l_return_status, l_msg_count, l_msg_data);
3915
3916 IF l_num_matches > 0 THEN --match found
3917 OPEN C_matched_party('PERSON');
3918 FETCH C_matched_party INTO l_party_id, l_score, l_creation_date;
3919 CLOSE C_matched_party;
3920 write_log(3, 'Matched person - '||to_char(l_party_id)||' score '||to_char(l_score)||' created '||to_char(l_creation_date));
3921 I.contact_party_id := l_party_id; --assign the matched party_id to contact_party_id
3922 IF I.contact_party_id IS NOT NULL THEN
3923 l_create_contact := 'N';
3924 ELSE
3925 l_create_contact := 'Y';
3926 END IF;
3927 ELSE
3928 write_log(3,'No person match found !');
3929 l_create_contact := 'Y';
3930 END IF;
3931
3932 END IF; --if party_id is not null --4
3933
3934 ELSE --First name, Last name not provided --3
3935
3936 write_log(3, 'Contact Firstname, Lastname not provided');
3937 l_create_contact := 'N';
3938 END IF; --3
3939
3940 ELSE -- if I.contact_party_id is not null --2
3941 IF I.rel_party_id IS NULL and I.party_id IS NOT NULL THEN
3942 OPEN C_get_rel_party_id(I.contact_party_id, I.party_id);
3943 FETCH C_get_rel_party_id INTO I.rel_party_id;
3944 CLOSE C_get_rel_party_id;
3945 END IF;
3946 l_create_contact := 'N';
3947 l_contact_provided := 'Y';
3948 write_log(3,'Skip contact echeck- provided contact_party_id- '||to_char(I.contact_party_id));
3949 END IF; --2
3950 END IF; --1
3951
3952 ----- End CONTACT SEARCH -----
3953
3954 ----- Begin CONTACT POINT SEARCH -----
3955
3956 IF (I.party_type = 'ORGANIZATION' and I.rel_party_id is not null) or
3960 write_log(3,'#4 :: Calling GET_MATCHING_CONTACT_POINTS with party_id: '||to_char(I.party_id));
3957 (I.party_type <> 'ORGANIZATION' and I.party_id is not null) THEN
3958 IF l_contact_provided = 'N' AND I.first_name IS NOT NULL AND I.last_name IS NOT NULL THEN
3959 IF I.email_address IS NOT NULL or I.phone_number IS NOT NULL THEN
3961 l_rule_id := to_number(FND_PROFILE.value('AS_USE_DQM_RULE_CODE_CONTACT'));
3962 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);
3963
3964 IF l_return_status <> FND_API.g_ret_sts_success THEN
3965 RAISE FND_API.g_exc_error;
3966 END IF;
3967
3968 -- Check EMAIL
3969 IF I.email_address IS NOT NULL THEN
3970 OPEN C_matched_contact_points('EMAIL',null);
3971 FETCH C_matched_contact_points INTO l_party_id, l_contact_point_id, l_score, l_creation_date;
3972 IF C_matched_contact_points%FOUND THEN
3973 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));
3974 l_dup_email := 'Y';
3975 ELSE
3976 l_dup_email := 'N';
3977 write_log(3,'EMAIL NOT match found !');
3978 END IF;
3979 CLOSE C_matched_contact_points;
3980 END IF; --EMAIL
3981
3982 -- Check PHONE
3983 IF I.phone_number IS NOT NULL THEN
3984 OPEN C_matched_contact_points('PHONE','GEN');
3985 FETCH C_matched_contact_points INTO l_party_id, l_contact_point_id, l_score, l_creation_date;
3986 IF C_matched_contact_points%FOUND THEN
3987 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));
3988 I.phone_id := l_contact_point_id;
3989 l_dup_phone := 'Y';
3990 ELSE
3991 write_log(3,'PHONE NOT match found !');
3992 l_dup_phone := 'N';
3993 END IF;
3994 CLOSE C_matched_contact_points;
3995 END IF; --PHONE
3996
3997 -- Check FAX
3998 IF I.fax_number IS NOT NULL THEN
3999 OPEN C_matched_contact_points('PHONE','FAX');
4000 FETCH C_matched_contact_points INTO l_party_id, l_contact_point_id, l_score, l_creation_date;
4001 IF C_matched_contact_points%FOUND THEN
4002 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));
4003 l_dup_fax := 'Y';
4004 ELSE
4005 write_log(3,'FAX NOT match found !');
4006 l_dup_fax := 'N';
4007 END IF;
4008 CLOSE C_matched_contact_points;
4009 END IF; --FAX
4010
4011 -- Check URL
4012 IF I.url IS NOT NULL THEN
4013 OPEN C_matched_contact_points('WEB',null);
4014 FETCH C_matched_contact_points INTO l_party_id, l_contact_point_id, l_score, l_creation_date;
4015 IF C_matched_contact_points%FOUND THEN
4016 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));
4017 l_dup_url := 'Y';
4018 ELSE
4019 write_log(3,'URL NOT match found !');
4020 l_dup_url := 'N';
4021 END IF;
4022 CLOSE C_matched_contact_points;
4023 END IF; --URL
4024 END IF;
4025 END IF;
4026 END IF;
4027
4028 ld_phone := l_dup_phone;
4029 ld_email := l_dup_email;
4030 ld_fax := l_dup_fax;
4031 ld_url := l_dup_url;
4032
4033 ----- End CONTACT POINT SEARCH -----
4034
4035 END TCA_DQM_processing;
4036
4037
4038 ----------------------------------------------------------
4039 -- Name: do_lead_import
4040 -- Scope: Public
4041 -- Sales Lead Import logic implemented
4042 -- Calls all other provate procedures and functions
4043 ----------------------------------------------------------
4044 procedure do_lead_import(
4045 -- errbuf varchar2,
4046 -- errcode varchar2,
4047 p_source_system in varchar2,
4048 p_debug_msg_flag in varchar2 := 'N',
4049 p_parent_request_id in number,
4050 p_child_request_id in number,
4051 p_resource_id in number, -- SOLIN, bug 4702335
4052 p_group_id in number -- SOLIN, bug 4702335
4053 )
4054 IS
4055 l_hz_conpartyid number;
4056 l_batch_unexp number := 0;
4057 l_batch_err number := 0;
4058 l_batch_succ number := 0;
4059 l_batch_size number := 0;
4060 l_duplicate_lead varchar2(1):= 'U';
4061 l_source_system number := 0;
4062 l_party_id number;
4063 l_orig_sys_party_found varchar2(1) default NULL ;
4064 l_return_status VARCHAR2(1);
4065 x_return_status VARCHAR2(1);
4066 l_msg_count NUMBER;
4067 l_msg_data VARCHAR2(2000);
4068
4069 p_dup_phone varchar2(1):= 'N';
4070 p_dup_email varchar2(1):= 'N';
4071 p_dup_fax varchar2(1):= 'N';
4072 p_dup_url varchar2(1):= 'N';
4073
4074 l_lead_engines_out_rec AS_SALES_LEADS_PUB.LEAD_ENGINES_OUT_Rec_Type;
4075 l_error_type varchar2(100);
4076 l_group_id number;
4077
4078 -- Bugfix for concurrency control
4079 cursor c_main (l_parent_request_id number, l_child_request_id number) is
4080 select * from as_import_interface --as_imp_sl_v
4081 where request_id = l_parent_request_id
4082 and child_request_id = l_child_request_id
4086 CURSOR C_Get_Lead_Info(C_Sales_Lead_Id NUMBER) IS
4083 and load_status = 'RUNNING'
4084 and source_system = p_source_system;
4085
4087 SELECT SL.CUSTOMER_ID, SL.ADDRESS_ID, SL.ASSIGN_TO_SALESFORCE_ID,
4088 SL.ASSIGN_TO_PERSON_ID, SL.ASSIGN_SALES_GROUP_ID,
4089 SL.QUALIFIED_FLAG, SL.PARENT_PROJECT,
4090 SL.CHANNEL_CODE, SL.DECISION_TIMEFRAME_CODE, SL.BUDGET_AMOUNT,
4091 SL.BUDGET_STATUS_CODE, SL.SOURCE_PROMOTION_ID, SL.STATUS_CODE,
4092 SL.REJECT_REASON_CODE, SL.LEAD_RANK_ID,
4093 -- swkhanna 5/24/02
4094 SL.LEAD_DATE, SL.SOURCE_SYSTEM, SL.COUNTRY
4095 FROM AS_SALES_LEADS SL
4096 WHERE SL.SALES_LEAD_ID = C_Sales_Lead_Id;
4097
4098 CURSOR C_Get_SLAESFORCE(C_User_Id NUMBER) IS
4099 SELECT JS.RESOURCE_ID
4100 FROM JTF_RS_RESOURCE_EXTNS JS
4101 WHERE JS.USER_ID = C_User_Id;
4102
4103 -- Cursor to select currency_code is passed null
4104 CURSOR C_currency_code (C_Terr_Code VARCHAR2) IS
4105 SELECT DECODE (derive_type, --enh 3098798
4106 NULL, currency_code,
4107 derive_type, 'EUR'
4108 ) currency_code
4109 FROM fnd_currencies
4110 WHERE issuing_territory_code = C_Terr_Code
4111 and nvl(start_date_active, sysdate) <= sysdate
4112 and nvl(end_date_active, sysdate) >= sysdate
4113 and enabled_flag = 'Y';
4114
4115 -- Cursor for fetching rows from as_imp_cnt_pnt_interface
4116 CURSOR c_cnt_pnt (c_owner_type varchar2, c_import_interface_id number)
4117 IS
4118 SELECT *
4119 FROM AS_IMP_CNT_PNT_INTERFACE
4120 WHERE owner_type = c_owner_type
4121 AND import_interface_id = c_import_interface_id;
4122
4123 -- Find the sales group of the person being added
4124 -- bugfix # 2772260
4125 CURSOR c_get_group_id (c_resource_id NUMBER, c_rs_group_member VARCHAR2,
4126 c_sales VARCHAR2, c_telesales VARCHAR2,
4127 c_fieldsales VARCHAR2, c_prm VARCHAR2, c_y VARCHAR2)
4128 IS
4129 SELECT grp.group_id
4130 FROM JTF_RS_GROUP_MEMBERS mem,
4131 JTF_RS_ROLE_RELATIONS rrel,
4132 JTF_RS_ROLES_B role,
4133 JTF_RS_GROUP_USAGES u,
4134 JTF_RS_GROUPS_B grp
4135 WHERE mem.group_member_id = rrel.role_resource_id
4136 AND rrel.role_resource_type = c_rs_group_member --'RS_GROUP_MEMBER'
4137 AND rrel.role_id = role.role_id
4138 AND role.role_type_code in (c_sales, c_telesales, c_fieldsales, c_prm) --'SALES','TELESALES','FIELDSALES','PRM')
4139 AND mem.delete_flag <> c_y --'Y'
4140 AND rrel.delete_flag <> c_y --'Y'
4141 AND SYSDATE BETWEEN rrel.start_date_active AND
4142 NVL(rrel.end_date_active,SYSDATE)
4143 AND mem.resource_id = c_resource_id
4144 AND mem.group_id = u.group_id
4145 AND u.usage = c_sales --'SALES'
4146 AND mem.group_id = grp.group_id
4147 AND SYSDATE BETWEEN grp.start_date_active AND
4148 NVL(grp.end_date_active,SYSDATE)
4149 AND ROWNUM < 2;
4150
4151 l_currency_code VARCHAR2(15);
4152 l_isQualified VARCHAR2(1);
4153 l_sales_lead_rec as_sales_leads_pub.sales_lead_rec_type;
4154 l_sales_lead_log_id NUMBER;
4155 x_sales_team_flag VARCHAR2(1);
4156 l_curr_time VARCHAR2(15);
4157
4158 l_create_party VARCHAR2(1) := 'N';
4159 l_create_party_site VARCHAR2(1) := 'N';
4160 l_create_location VARCHAR2(1) := 'N';
4161 l_create_contact VARCHAR2(1) := 'N';
4162 l_dup_sales_lead_id NUMBER;
4163
4164 l_validation_level NUMBER;
4165 l_hz_execute_api_callouts VARCHAR2(240);
4166
4167 BEGIN
4168 G_DEBUGFLAG := p_debug_msg_flag;
4169
4170 write_log(3, 'Sales Lead Import Child #'||p_child_request_id||' started at '
4171 ||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'));
4172
4173 write_log(3, 'Getting the saleasforce_id for the user ...');
4174
4175 -- SOLIN, bug 4702335
4176 IF p_resource_id IS NOT NULL
4177 THEN
4178 G_SL_SALESFORCE_ID := p_resource_id;
4179 l_group_id := p_group_id;
4180 ELSE
4181 OPEN C_Get_SLAESFORCE(fnd_global.user_id);
4182 FETCH C_Get_SLAESFORCE INTO G_SL_SALESFORCE_ID;
4183 CLOSE C_Get_SLAESFORCE;
4184
4185 If (G_SL_SALESFORCE_ID is null) then
4186 G_SL_SALESFORCE_ID := fnd_profile.value('AS_DEFAULT_RESOURCE_ID');
4187 end if;
4188
4189 -- Find the sales group of the person being added
4190 -- bugfix # 2772260
4191 OPEN c_get_group_id (G_SL_SALESFORCE_ID, 'RS_GROUP_MEMBER', 'SALES',
4192 'TELESALES', 'FIELDSALES', 'PRM', 'Y');
4193 FETCH c_get_group_id INTO l_group_id;
4194 CLOSE c_get_group_id;
4195 end if;
4196 -- SOLIN, end
4197
4198 write_log(3, 'Salesforce_id for the logged in user is : ' || G_SL_SALESFORCE_ID);
4199 write_log(3, 'Slaes Group id : ' || l_group_id);
4200
4201 -- ajchatto 050602, check for SOURCE_SYSTEM
4202 -- bug# 2351782
4203 -- Bugfix# 2835357, check if the source system is valid or not once.
4204 SELECT count(*)
4205 INTO l_source_system
4206 FROM as_lookups
4207 WHERE lookup_type = 'SOURCE_SYSTEM'
4208 AND lookup_code = p_source_system;
4209
4210 -- SOLIN, bug 4494009
4211 l_hz_execute_api_callouts := fnd_profile.value('HZ_EXECUTE_API_CALLOUTS');
4212 write_log(3, 'Profile HZ_EXECUTE_API_CALLOUTS: '|| l_hz_execute_api_callouts);
4213 fnd_profile.put('HZ_EXECUTE_API_CALLOUTS', 'N');
4214 -- SOLIN, end
4215
4216 -- For each lead
4220 FND_MSG_PUB.Initialize;
4217 For I in c_main(p_parent_request_id, p_child_request_id)
4218 Loop
4219 l_batch_size := l_batch_size +1;
4221
4222 Begin
4223
4224 IF I.party_type IS NULL THEN
4225 I.party_type := 'ORGANIZATION';
4226 END IF;
4227
4228 -- resetting flag bug# 2574165
4229 l_orig_sys_party_found := NULL;
4230 l_duplicate_lead := 'U';
4231 l_dup_sales_lead_id := NULL;
4232 write_log(3, 'Processing import_interface_id: '||to_char(I.import_interface_id));
4233
4234 IF l_source_system < 1 THEN
4235 AS_UTILITY_PVT.Set_Message(
4236 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
4237 p_msg_name => 'API_INVALID_ID',
4238 p_token1 => 'COLUMN',
4239 p_token1_value => 'SOURCE_SYSTEM',
4240 p_token2 => 'VALUE',
4241 p_token2_value => I.SOURCE_SYSTEM );
4242 RAISE FND_API.G_EXC_ERROR;
4243 END IF;
4244
4245 -- Begin enh: Support for Currency : aanjaria 100402
4246 IF I.currency_code IS NULL AND I.budget_amount IS NOT NULL THEN
4247 -- SOLIN, Bug 4956232
4248 -- throw exception, not get currency_code for not null budget_amount
4249 AS_UTILITY_PVT.Set_Message(
4250 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
4251 p_msg_name => 'API_INVALID_ID',
4252 p_token1 => 'COLUMN',
4253 p_token1_value => 'CURRENCY_CODE',
4254 p_token2 => 'VALUE',
4255 p_token2_value => 'NULL' );
4256 write_log(3, 'Please enter currency_code when your budget_amount is entered.');
4257 raise FND_API.G_EXC_ERROR;
4258
4259 -- Get the currency of the customer country
4260 --OPEN C_currency_code(I.country);
4261 --FETCH C_currency_code INTO l_currency_code;
4262 --IF C_currency_code%NOTFOUND THEN
4263 -- Set default currency
4264 -- l_currency_code := fnd_profile.value('JTF_PROFILE_DEFAULT_CURRENCY');
4265 --END IF;
4266 --CLOSE C_currency_code;
4267 --I.currency_code := l_currency_code;
4268 END IF; --if currency_code is null
4269 write_log(3, 'Value of currency :'||I.currency_code);
4270 -- End enh: Support for Currency
4271
4272 -- Check profile for executing custom hook
4273 IF (fnd_profile.value ('AS_LEAD_IMP_EXEC_CUSTOM_CODE')='Y') Then
4274
4275 write_log(3, 'Before calling custom hook for party match');
4276 -- Call custom hook
4277 aml_find_party_match_pvt.main(I, --IN OUT param
4278 x_return_status --OUT param
4279 );
4280
4281 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
4282 write_log(3, 'aml_find_party_match failed');
4283 raise FND_API.G_EXC_ERROR;
4284 END IF;
4285
4286 write_log(3, 'Returned from custom hook with party_id: '||to_char(I.party_id));
4287
4288 END IF;
4289
4290 --End custom hook
4291
4292 --Start DQM entities processing
4293 --aanjaria 11.29.2002
4294 write_log(3, 'Start DQM - TCA Processing');
4295
4296 SELECT to_char(sysdate,'yyyymmddhhmiss')
4297 INTO l_curr_time
4298 FROM dual;
4299 write_log(3, 'Starting DQM - TCA time: '||l_curr_time);
4300
4301 TCA_DQM_processing (I, l_create_party, l_create_party_site, l_create_contact, -- l_create_contact_point,
4302 l_create_location, p_dup_phone, p_dup_fax, p_dup_email, p_dup_url );
4303 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);
4304
4305 -- Check for lead duplication before creating it !
4306 IF I.party_id IS NOT NULL AND I.source_system <> 'INTERACTION' THEN --bug 3601263 bypass dedupe for interaction
4307 deDupe_Check(pI => I, x_duplicate_lead => l_duplicate_lead, x_dup_sales_lead_id => l_dup_sales_lead_id );
4308 write_log(3,'back from dedupe checking'||l_duplicate_lead||'-'||to_char(l_dup_sales_lead_id));
4309 END IF;
4310
4311 IF l_duplicate_lead = 'D' THEN
4312 I.sales_lead_id := l_dup_sales_lead_id;
4313 write_log(3, 'Duplicate lead');
4314 I.load_status := 'DUPLICATE';
4315 writeBak(I, G_return_status);
4316 commit;
4317 IF ((G_return_status <> FND_API.G_RET_STS_SUCCESS) AND (G_return_status <> 'W')) THEN
4318 write_log(3, 'writeBak failed');
4319 RAISE FND_API.G_EXC_ERROR;
4320 END IF;
4321 END IF;
4322
4323 IF l_duplicate_lead = 'U' THEN
4324
4325 -- ffang 092601, for bug 2017445, lead existence checking should
4326 -- be check at the begining to prevent customer/address/contact creation.
4327 IF Is_duplicate_lead(I) THEN
4328 write_log(3, 'Duplicate lead');
4329 I.load_status := 'DUPLICATE';
4330 writeBak(I, G_return_status);
4331 IF ((G_return_status <> FND_API.G_RET_STS_SUCCESS) AND
4332 (G_return_status <> 'W'))
4333 THEN
4334 write_log(3, 'writeBak failed');
4335 RAISE FND_API.G_EXC_ERROR;
4336 END IF;
4337
4338 ELSE
4339 -- Create TCA entities
4340
4341 -- Create Location
4342 IF l_create_location = 'Y' or I.location_id IS NULL THEN
4343 -- ffang 100901, for bug 2042175, if address1 or country
4344 -- does not exist, skip create location
4345 IF (I.address1 IS NOT NULL AND I.country IS NOT NULL) THEN
4346 write_log(3, 'Creating location');
4347 do_create_location(I, G_return_status);
4351 RAISE FND_API.G_EXC_ERROR;
4348 -- If error raise exception
4349 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4350 write_log(3, 'Creating location failed');
4352 END IF;
4353 I.new_loc_flag := 1; -- new location flag set
4354 ELSE
4355 write_log (3, 'No add1/country-skip creating location');
4356 END IF;
4357 ELSE
4358 write_log (3, 'dup location:' || I.location_id);
4359 END IF;
4360
4361 -- Create Party
4362 IF l_create_party = 'Y' or I.party_id IS NULL THEN
4363 IF I.party_type = 'ORGANIZATION' THEN
4364 do_create_organization(I, G_return_status);
4365 -- If error raise exception
4366 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4367 write_log(3, 'Creating Organization failed');
4368 RAISE FND_API.G_EXC_ERROR;
4369 END IF;
4370 I.new_party_flag := 1; --new party flag set
4371 ELSIF I.party_type ='PERSON' THEN
4372 do_create_person(I, I.party_type, G_return_status);
4373 -- If error raise exception
4374 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4375 write_log(3, 'do_create_Person failed');
4376 RAISE FND_API.G_EXC_ERROR;
4377 END IF;
4378 I.new_party_flag := 1; --new party flag set
4379 ELSE
4380 -- ffang 101201, bug2050535, push error message
4381 AS_UTILITY_PVT.Set_Message(
4382 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
4383 p_msg_name => 'AS_INVALID_PARTY_TYPE',
4384 p_token1 => 'VALUE',
4385 p_token1_value => I.party_type);
4386 write_log (3, 'Party_type is invalid');
4387 -- end ffang 101201
4388 RAISE FND_API.G_EXC_ERROR;
4389 END IF; -- party_type condition end
4390 --2851215: orig_system_reference should not be updated
4391 /*
4392 ELSIF l_create_party = 'N' and I.party_id IS NOT NULL THEN
4393 IF I.party_type IS NOT NULL THEN
4394 write_log (3, 'dup party:' || I.party_id || '-' || I.party_type);
4395 do_update_party(I, G_return_status);
4396 -- If error raise exception
4397 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4398 write_log(3, 'do_update_party failed');
4399 RAISE FND_API.G_EXC_ERROR;
4400 END IF;
4401 END IF;
4402 */
4403 END IF; -- l_party_found condition end
4407 -- ffang 100901, for bug 2042175, if location is not
4404
4405 -- Create Party Site
4406 IF (l_create_party_site = 'Y' or I.party_site_id IS NULL) THEN
4408 -- created, skip create party site and party site use
4409 IF (I.location_id IS NOT NULL) THEN
4410 -- write_log(3, 'Creating party site');
4411 write_log(3, 'Creating party site for Organization');
4412 -- swkhanna 6/12/02 Bug 2404796
4413 -- do_create_ps_psu(I, I.party_id, 'ORG', G_return_status);
4414 do_create_ps_psu(I, I.party_id, I.party_type, G_return_status);
4415 -- If error raise exception
4416 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4417 write_log(3, 'Creating Party Site / Use failed');
4418 RAISE FND_API.G_EXC_ERROR;
4419 END IF;
4420 I.new_ps_flag := 1; --new party flag set
4421 ELSE
4422 write_log(3,'no location created-skip create PS');
4423 END IF;
4424 END IF;
4425
4426 -- Create contact points for 'PERSON'
4427 FOR cpp in c_cnt_pnt('PERSON', I.import_interface_id) LOOP
4428 write_log(3, 'Creating contact points for PERSON');
4429 do_create_contact_points(I, cpp, 'PERSON', G_return_status);
4430 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4431 write_log(3, 'createContactPoints for person failed');
4432 RAISE FND_API.G_EXC_ERROR;
4433 END IF;
4434 END LOOP;
4435
4436 -- Create contacts
4437 IF I.party_type = 'ORGANIZATION' THEN
4438 IF I.contact_party_id IS NULL or l_create_contact = 'Y' THEN
4439 -- ffang 100901, bug 2042181, if first name or last name
4440 -- does not exist, skip create contact
4441 IF (I.first_name IS NOT NULL and I.last_name IS NOT NULL) THEN
4442 write_log(3, 'Creating the Contact');
4443 do_create_person(I, 'CONTACT', G_return_status);
4444 -- If error raise exception
4445 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4449 I.new_con_flag := 1; -- new contact flag set
4446 write_log(3, 'do_create_Person-contact failed');
4447 RAISE FND_API.G_EXC_ERROR;
4448 END IF;
4450 ELSE
4451 write_log(3, 'no first/last name-skip create cnt');
4452 END IF;
4453 ELSE
4454 write_log(3, 'dup contact: ' || I.contact_party_id);
4455 END IF; --create_contact = 'Y'
4456
4457 -- Check and createOrgContact, Relationship and OrgContactRoles
4458 -- ffang 100901, for bug 2042181, if contact is not created,
4459 -- then don't create relationship
4460 IF (I.rel_party_id is NULL and I.contact_party_id is not NULL) THEN
4461 do_create_relationship(I, G_return_status);
4462 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4463 write_log(3, 'createRelationship failed');
4464 RAISE FND_API.G_EXC_ERROR;
4465 END IF;
4466 I.new_rel_flag := 1; -- new relationship flag set
4467 END IF;
4468 END IF; --if party_type = 'ORGANIZATION'
4469
4470 -- Create Contact Point
4471 IF (I.party_type = 'ORGANIZATION' and I.rel_party_id is not null) or
4472 (I.party_type <> 'ORGANIZATION' and I.party_id is not null) THEN
4473 write_log (3, 'Creating contact point (1)');
4474
4475 do_create_contact_points_old (I, p_dup_phone, p_dup_fax, p_dup_email, p_dup_url, G_return_status);
4476 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4477 write_log(3, 'createContactPoints for contact (1) failed');
4478 RAISE FND_API.G_EXC_ERROR;
4479 END IF;
4480
4481 -- SOLIN, bug 4637420
4482 -- create_contact_preferences should be called after
4483 -- contact point is created.
4484 do_contact_preference(I, G_return_status);
4485 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4486 write_log(3, 'createContactPreference failed');
4487 RAISE FND_API.G_EXC_ERROR;
4488 END IF;
4489 -- SOLIN, end bug 4637420
4490 ELSE
4491 write_log (3, ' Contact Point Not Created ');
4492 END IF;
4493
4494 -- create contact points (in as_imp_cnt_pnt_interface) for 'CONTACT'
4495 FOR cpc in c_cnt_pnt('CONTACT', I.import_interface_id) LOOP
4496 write_log (3, 'Creating contact point (2)');
4497 do_create_contact_points(I, cpc, 'CONTACT',G_return_status);
4498 IF G_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4499 write_log(3, 'createContactPoints for contact failed');
4500 RAISE FND_API.G_EXC_ERROR;
4501 END IF;
4502 END LOOP;
4503
4504 SELECT to_char(sysdate,'yyyymmddhhmiss')
4505 INTO l_curr_time
4506 FROM dual;
4507 write_log(3, 'End DQM - TCA time: '||l_curr_time);
4508
4509
4510 -- After all the contact points are created
4511 -- check if there are at least one contact point,
4512 -- If not, update one of the phone contact as primary
4513 -- Added by Ajoy
4514 -- Not currently used as HZ_CONTACT_POINT_PUB.create_contact_points
4515 -- takes care of setting the primary flag for PHONE, EMAIL, WEB etc.
4516
4517 -- validate_primary_cp (I, G_return_status);
4518
4519
4520 -- new call goes here.
4521
4522 -- createSalesLead
4523 -- ffang 080201, for bug 1852338, check if leads is a duplicate
4524 -- lead or not before creating lead.
4525 -- ffang 092601, for bug 2017445, lead existence checking should
4526 -- be check at the begining to prevent customer/address/contact
4527 -- creation.
4528 -- If not is_duplicate_lead(I) THEN
4529
4530 SELECT to_char(sysdate,'yyyymmddhhmiss')
4531 INTO l_curr_time
4532 FROM dual;
4533 write_log(3, 'Start Create - Process Lead time: '||l_curr_time);
4534
4535
4536 do_create_saleslead(I, G_return_status);
4537
4538 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4539 write_log(3, 'do_create_saleslead failed');
4540 RAISE FND_API.G_EXC_ERROR;
4541 End If;
4542
4543 -- Added by Ajoy, 08/21, bugfix# 2521850
4544 -- If the lead creation is successful, update the import record with sales_lead_id
4545 -- so that the marketing_score (lead_score) attribute can be used in rule engine
4546 If G_return_status = FND_API.G_RET_STS_SUCCESS Then
4547 /* --redundent update..after purge project this update is not needed
4548 UPDATE as_import_interface
4549 SET sales_lead_id = I.sales_lead_id
4550 WHERE import_interface_id = I.import_interface_id;
4551 */
4552 UPDATE aml_interaction_leads
4553 SET sales_lead_id = I.sales_lead_id
4554 WHERE import_interface_id = I.import_interface_id;
4555 End if;
4556
4557 -- End If;
4558
4559 -- createInterest
4560 --do_create_Interest(I, G_return_status);
4561 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4562 write_log(3, 'do_create_interest failed');
4563 RAISE FND_API.G_EXC_ERROR;
4564 End If;
4565
4566 -- do_create_LeadNoteAndContext
4570 then
4567 If ((I.sales_lead_id is not null) AND (I.party_id is not null)
4568 AND ((I.lead_note is not null) OR
4569 (I.lead_note <> FND_API.G_MISS_CHAR)))
4571 do_create_LeadNoteAndContext(I, G_return_status);
4572 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4573 write_log(3, 'do_create_LeadNoteAndContext failed');
4574 RAISE FND_API.G_EXC_ERROR;
4575 End If;
4576 End if;
4577
4578 G_LOCAL_ORG_CONTACT_ID := Null;
4579 G_SL_LINE_COUNT := 0;
4580
4581 --Bug 3680824: non resource user can import the lead for sales campaign
4582 -- in which case, user validation needs to be bypassed
4583 IF I.source_system = 'SALES_CAMPAIGN' THEN
4584 l_validation_level := AS_UTILITY_PUB.G_VALID_LEVEL_ITEM;
4585 ELSE
4586 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
4587 END IF;
4588
4589 AS_SALES_LEAD_ENGINE_PVT.Lead_Process_After_Create (
4590 P_Api_Version_Number => 2.0,
4591 P_Init_Msg_List => FND_API.G_FALSE,
4592 P_Commit => FND_API.G_FALSE,
4593 P_Validation_Level => l_validation_level,
4594 P_Check_Access_Flag => FND_API.G_MISS_CHAR,
4595 P_Admin_Flag => FND_API.G_MISS_CHAR,
4596 P_Admin_Group_Id => FND_API.G_MISS_NUM,
4597 P_identity_salesforce_id => G_SL_SALESFORCE_ID,
4598 P_Salesgroup_id => l_group_id,
4599 P_Sales_Lead_Id => I.sales_lead_id,
4600 X_Return_Status => l_return_status,
4601 X_Msg_Count => l_msg_count,
4602 X_Msg_Data => l_msg_data
4603 );
4604
4605 -- bugfix# 2891236 , should check for l_return_status
4606 -- Bug 2893436, it shouldn't raise exception if return status is W
4607 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4608 write_log(3, 'Lead_Process_After_Create errors');
4609 raise FND_API.G_EXC_ERROR;
4610 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4611 write_log(3, 'Lead_Process_After_Create unexp errors');
4612 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4613 ELSE
4614 write_log(3, 'Lead_Process_After_Create ' || l_return_status);
4615 END IF;
4616
4617 SELECT to_char(sysdate,'yyyymmddhhmiss')
4618 INTO l_curr_time
4619 FROM dual;
4620 write_log(3, 'End Create - Process Lead time: '||l_curr_time);
4621
4622 --writing bak to as_import_interface
4623 I.load_status := G_LOAD_STATUS_SUCC;
4624 writeBak(I, G_return_status);
4625 IF ((G_return_status <> FND_API.G_RET_STS_SUCCESS) AND
4626 (G_return_status <> 'W'))
4627 THEN
4628 write_log(3, 'writeBak failed');
4629 RAISE FND_API.G_EXC_ERROR;
4630 END IF;
4631 l_batch_succ := l_batch_succ +1;
4632
4633 -- ffang 071701, bug 1888388, temporary solution
4634 -- the sales teams created by assign_sales_lead
4635 -- (update_sales_lead) should set freeze_flag to 'N'.
4636 -- This fix should be removed after sales lead api changed.
4637
4638 -- Bugfix# 2889261, Not to update the KEEP_FLAG
4639 --update as_accesses_all set freeze_flag='N'
4640 --where sales_lead_id = I.sales_lead_id;
4641 End if;
4642
4643 End If; -- Duplicate Lead Check
4644 commit;
4645
4646 Exception
4647
4648 when FND_API.G_EXC_ERROR Then
4649 rollback;
4650 l_batch_err := l_batch_err +1;
4651 l_error_type := 'EXP';
4652 write_errors(I,l_error_type, G_return_status );
4653 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4654 write_log(3, 'write_errors failed');
4655 else
4656 I.load_status := G_LOAD_STATUS_ERR;
4657 writeBak(I, G_return_status);
4658 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4659 write_log(3, 'writeBak failed');
4660 End if;
4661 End if;
4662 commit;
4663 when FND_API.G_EXC_UNEXPECTED_ERROR Then
4664 l_batch_unexp := l_batch_unexp +1;
4665 rollback;
4666 l_error_type := 'UNEXP';
4667 write_errors(I,l_error_type, G_return_status );
4668 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4669 write_log(3, 'write_errors failed');
4670 else
4671 I.load_status := G_LOAD_STATUS_UNEXP_ERR;
4672 writeBak(I, G_return_status);
4673 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4674 write_log(3, 'writeBak failed');
4675 End if;
4676 End if;
4677 commit;
4678 when others then
4679 l_batch_unexp := l_batch_unexp +1;
4680 rollback;
4681 l_error_type := 'OTHER';
4682 write_errors(I,l_error_type, G_return_status );
4683 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4684 write_log(3, 'write_errors failed');
4688 If G_return_status <> FND_API.G_RET_STS_SUCCESS Then
4685 else
4686 I.load_status := G_LOAD_STATUS_UNEXP_ERR;
4687 writeBak(I, G_return_status);
4689 write_log(3, 'writeBak failed');
4690 End if;
4691 End if;
4692 commit;
4693 End;
4694 End Loop;
4695
4696 -- SOLIN, bug 4494009
4697 -- Set profile back to its original value
4698 fnd_profile.put('HZ_EXECUTE_API_CALLOUTS', l_hz_execute_api_callouts);
4699 -- SOLIN, end
4700
4701 write_log(2, 'Batch Size:'|| l_batch_size);
4702 write_log(1, 'Batch Size:'|| l_batch_size);
4703 write_log(2, 'Number of Records Successfully Imported:'|| l_batch_succ);
4704 write_log(1, 'Number of Records Successfully Imported:'|| l_batch_succ);
4705 write_log(2, 'Number of Records with Errors:'|| l_batch_err);
4706 write_log(1, 'Number of Records with Errors:'|| l_batch_err);
4707 write_log(2, 'Number of Records with unexpected Errors:'|| l_batch_unexp);
4708 write_log(1, 'Number of Records with unexpected Errors:'|| l_batch_unexp);
4709
4710 write_log(3, 'End Child Import Process time: '||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'));
4711
4712 Commit;
4713
4714 Exception
4715 when others then
4716 rollback;
4717 write_log(2, sqlerrm);
4718 write_log(1, sqlerrm);
4719 l_status := fnd_concurrent.set_completion_status('ERROR', sqlerrm);
4720
4721 end do_lead_import;
4722
4723
4724 ----------------------------------------------------------
4725 -- Name: main
4726 -- Scope: Public
4727 -- Sales Lead Import parallel logic implemented
4728 -- Main procedure called from Import Sales Lead conc prog
4729 ----------------------------------------------------------
4730 procedure main(
4731 errbuf varchar2,
4732 errcode varchar2,
4733 p_source_system in varchar2,
4734 --p_creation_date in date, -- bugfix : 2044447
4735 p_debug_msg_flag in varchar2 := 'N',--bugfix : 2047689
4736 p_batch_id in number,
4737 p_purge_error_flag in varchar2 := 'N',
4738 p_parent_request_id in number := NULL,
4739 p_child_request_id in number := NULL,
4740 p_resource_id in number := NULL, -- SOLIN, bug 4702335
4741 p_group_id in number := NULL -- SOLIN, bug 4702335
4742 ) IS
4743
4744 l_parameter_list wf_parameter_list_t;
4745 l_req_data VARCHAR2(10);
4746 l_req_data_counter NUMBER;
4747 l_batch_size NUMBER;
4748 l_request_id NUMBER;
4749 l_new_request_id NUMBER;
4750 l_total_children NUMBER;
4751 l_total_records NUMBER;
4752 l_interaction_threshold NUMBER;
4753
4754 l_wait_status BOOLEAN;
4755 x_phase VARCHAR2(30);
4756 x_status VARCHAR2(30);
4757 x_dev_phase VARCHAR2(30);
4758 x_dev_status VARCHAR2(30);
4759 x_message VARCHAR2(240);
4760
4761 TYPE request_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4762 l_request_id_tbl request_id_tbl;
4763
4764 -- SOLIN, bug 4556394, SQL tuning
4765 CURSOR c_get_schema_name(c_table_name VARCHAR2) IS
4766 SELECT owner
4767 FROM sys.all_tables
4768 WHERE table_name = c_table_name;
4769
4770 l_owner VARCHAR2(30);
4771 Begin
4772
4773 G_DEBUGFLAG := p_debug_msg_flag;
4774
4775 If p_child_request_id IS NOT NULL THEN
4776 write_log(3, 'Starting child process# '||p_child_request_id);
4777 do_lead_import(
4778 p_source_system,
4779 p_debug_msg_flag,
4780 p_parent_request_id,
4781 p_child_request_id,
4782 p_resource_id,
4783 p_group_id);
4784
4785 Elsif p_child_request_id is NULL THEN
4786
4787 write_log(3, 'Starting Main Lead Import Process time: '||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'));
4788
4789 /* Create parameter list for LeadImport events */
4790 l_parameter_list := WF_PARAMETER_LIST_T();
4791
4792 wf_event.AddParameterToList(p_name => 'P_SOURCE_SYSTEM',
4793 p_value => p_source_system,
4794 p_parameterlist => l_parameter_list);
4795
4796 wf_event.AddParameterToList(p_name => 'P_BATCH_ID',
4797 p_value => p_batch_id,
4798 p_parameterlist => l_parameter_list);
4799
4800 wf_event.AddParameterToList(p_name => 'P_DEBUG_MSG_FLAG',
4801 p_value => p_debug_msg_flag,
4802 p_parameterlist => l_parameter_list);
4803
4804 wf_event.AddParameterToList(p_name => 'P_PURGE_ERROR_FLAG',
4805 p_value => p_purge_error_flag,
4806 p_parameterlist => l_parameter_list);
4807
4808 /*** Raise LeadImport-PRE Event ***/
4809 write_log(1, 'Calling LeadImport-PRE Event');
4810 write_log(2, 'Calling LeadImport-PRE Event');
4811 write_log(3, 'Calling LeadImport-PRE Event');
4812
4813 Wf_Event.Raise
4814 ( p_event_name => 'oracle.apps.ams.leads.LeadsImportEvent.Pre',
4815 p_event_key => TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS'),
4816 p_parameters => l_parameter_list,
4817 p_send_date => sysdate
4818 );
4819 /*** End Event Raise ***/
4820
4821
4825 OPEN c_get_schema_name('AS_LEAD_IMPORT_ERRORS');
4822 -- ffang 082301, user use parameter p_purge_error_flag to decide if
4823 -- purge AS_LEAD_IMPORT_ERRORS
4824 IF p_purge_error_flag = 'Y' THEN
4826 FETCH c_get_schema_name INTO l_owner;
4827 CLOSE c_get_schema_name;
4828
4829 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_owner
4830 || '.AS_LEAD_IMPORT_ERRORS';
4831 -- delete from AS_LEAD_IMPORT_ERRORS;
4832 END IF;
4833
4834 --Get conc_request_id
4835 l_request_id := nvl(FND_GLOBAL.conc_request_id, -1);
4836
4837 --Get the batch size for each thread from profile
4838 l_batch_size := nvl(to_number(FND_PROFILE.value('AS_MIN_REC_PARALLEL_FOR_IMPORT')),0);
4839
4840 --Get interaction threshold
4841 l_interaction_threshold := nvl(FND_PROFILE.value('AS_INTERACTION_SCORE_THRESHOLD'),0);
4842
4843 --If profile is set to -ve value
4844 If l_batch_size < 0 then
4845 l_batch_size := 0;
4846 End if;
4847
4848 if p_batch_id > 0 then
4849 write_log(3, 'batch_id is found ');
4850 -- Update load_status to RUNNING
4851 UPDATE as_import_interface
4852 SET load_status = 'RUNNING', request_id = l_request_id,
4853 child_request_id = ceil(ROWNUM/decode(l_batch_size,0,ROWNUM,l_batch_size))
4854 WHERE batch_id = p_batch_id
4855 AND source_system = p_source_system
4856 AND load_status = 'NEW'
4857 AND decode(source_system,'INTERACTION',interaction_score,l_interaction_threshold) >= l_interaction_threshold;
4858 else
4859 write_log(3, 'batch_id is null ');
4860 UPDATE as_import_interface
4861 SET load_status = 'RUNNING', request_id = l_request_id,
4862 child_request_id = ceil(ROWNUM/decode(l_batch_size,0,ROWNUM,l_batch_size))
4863 WHERE source_system = p_source_system
4864 AND load_status = 'NEW'
4865 AND decode(source_system,'INTERACTION',interaction_score,l_interaction_threshold) >= l_interaction_threshold;
4866 end if;
4867
4868 --total records in batch
4869 l_total_records := SQL%ROWCOUNT;
4870 write_log(1, 'Total batch size: '||l_total_records);
4871 write_log(2, 'Total batch size: '||l_total_records);
4872 write_log(3, 'Total batch size: '||l_total_records);
4873
4874 COMMIT;
4875 write_log(3,'Updated load_status to RUNNING');
4876
4877 --handle condition if batch size for parallel import is set to null or zero.
4878 If l_batch_size <= 0 then
4879 l_batch_size := l_total_records;
4880 End if;
4881
4882
4883 If l_total_records > 0 then
4884 --Calculate number of child processes required
4885 l_total_children := ceil(l_total_records/l_batch_size);
4886
4887
4888 l_req_data := fnd_conc_global.request_data;
4889
4890 if (l_req_data is not null) then
4891 l_req_data_counter := to_number(l_req_data);
4892 l_req_data_counter := l_req_data_counter + 1;
4893 else
4894 l_req_data_counter := 1;
4895 end if;
4896
4897
4898 --Spawn child conc requests
4899 FOR child_idx IN 1..l_total_children LOOP
4900
4901 l_new_request_id := FND_REQUEST.SUBMIT_REQUEST (
4902 application => 'AS',
4903 program => 'ASXSLIMP',
4904 description => 'Import Sales Leads - Child #'||to_char(child_idx),
4905 --sub_request => TRUE,
4906 argument1 => p_source_system,
4907 argument2 => p_debug_msg_flag,
4908 argument3 => p_batch_id,
4909 argument4 => p_purge_error_flag,
4910 argument5 => l_request_id,
4911 argument6 => child_idx,
4912 argument7 => p_resource_id,
4913 argument8 => p_group_id
4914 );
4915
4916 IF l_new_request_id = 0 THEN
4917 write_log(1, 'Error during submission of child request #'||child_idx);
4918 write_log(2, 'Error during submission of child request #'||child_idx);
4919 write_log(3, 'Error during submission of child request #'||child_idx);
4920 END IF;
4921
4922 write_log(1, 'Spawned child# '||to_char(child_idx)||' request_id: '||to_char(l_new_request_id));
4923 write_log(2, 'Spawned child# '||to_char(child_idx)||' request_id: '||to_char(l_new_request_id));
4924 write_log(3, 'Spawned child# '||to_char(child_idx)||' request_id: '||to_char(l_new_request_id));
4925 l_request_id_tbl(child_idx) := l_new_request_id;
4926 END LOOP;
4927
4928 --Wait for children to finish
4929 --Bug# 3523221 changed api call to wait for children
4930
4931 commit;
4932 FOR child_idx IN 1 .. l_request_id_tbl.count LOOP
4933
4934 write_log(3, 'Waiting for child#'||to_char(child_idx));
4935
4936 l_wait_status := FND_CONCURRENT.WAIT_FOR_REQUEST (
4937 request_id => l_request_id_tbl(child_idx),
4938 phase => x_phase,
4939 status => x_status,
4940 dev_phase => x_dev_phase,
4941 dev_status => x_dev_status,
4942 message => x_message
4943 );
4944
4945 END LOOP;
4946
4947 Else -- l_total_records = 0
4948 write_log(3, 'Batch size: 0');
4949 l_total_children := 0;
4950 End if;
4951
4952
4953 /*** Raise LeadImport-POST Event ***/
4954 write_log(1, 'Calling LeadImport-POST Event');
4955 write_log(2, 'Calling LeadImport-POST Event');
4956 write_log(3, 'Calling LeadImport-POST Event');
4957
4958 Wf_Event.Raise
4962 p_send_date => sysdate
4959 ( p_event_name => 'oracle.apps.ams.leads.LeadsImportEvent.Post',
4960 p_event_key => TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS'),
4961 p_parameters => l_parameter_list,
4963 );
4964 /*** End Event Raise ***/
4965
4966 l_parameter_list.DELETE;
4967
4968 write_log(3, 'End Parent Import Process time: '||to_char(sysdate, 'DD-Mon-YYYY HH24:MI:SS'));
4969 write_log(3, 'Total '||l_total_records||' records processed.');
4970
4971 End if;
4972
4973 Commit;
4974
4975 Exception
4976 when others then
4977 rollback;
4978 write_log(2, sqlerrm);
4979 write_log(1, sqlerrm);
4980 l_status := fnd_concurrent.set_completion_status('ERROR', sqlerrm);
4981
4982 end main;
4983
4984 end as_import_sl_pvt;