[Home] [Help]
PACKAGE BODY: APPS.OE_OE_INLINE_ADDRESS
Source
1 PACKAGE BODY oe_oe_inline_address AS
2 /* $Header: OEXFINLB.pls 120.0.12010000.3 2008/12/31 06:24:01 smanian ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'oe_oe_inline_address';
7 G_CREATED_BY_MODULE CONSTANT VARCHAR2(30) := 'ONT_UI_ADD_CUSTOMER';
8
9
10
11 FUNCTION find_lookup_meaning(in_lookup_type in varchar2,
12 in_lookup_code in varchar2
13 ) return varchar2 IS
14
15 CURSOR c_meaning is
16 SELECT meaning
17 from ar_lookups
18 where lookup_type = in_lookup_type
19 and lookup_code = in_lookup_code;
20 l_meaning varchar2(200);
21
22 --
23 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
24 --
25 BEGIN
26
27 OPEN c_meaning;
28 FETCH c_meaning
29 INTO l_meaning;
30 if c_meaning%NOTFOUND then
31 CLOSE c_meaning;
32 return null;
33 elsif c_meaning%FOUND then
34 CLOSE c_meaning;
35 return l_meaning;
36 end if;
37
38 return null;
39
40 EXCEPTION
41 when others then
42 if c_meaning%ISOPEN then
43 close c_meaning;
44 end if;
45 return null;
46
47
48 END find_lookup_meaning;
49
50
51 PROCEDURE Create_contact
52 ( p_contact_last_name in varchar2,
53 p_contact_first_name in varchar2,
54 p_contact_title in varchar2,
55 p_email in varchar2,
56 p_area_code in varchar2,
57 p_phone_number in varchar2,
58 p_extension in varchar2,
59 p_acct_id in number,
60 p_party_id in number,
61 p_created_by_module IN VARCHAR2 DEFAULT NULL,
62 p_orig_system IN VARCHAR2 DEFAULT NULL, --ER7675548
63 p_orig_system_reference IN VARCHAR2 DEFAULT NULL, --ER7675548
64 x_return_status OUT NOCOPY VARCHAR2,
65
66 x_msg_count OUT NOCOPY NUMBER,
67
68 x_msg_data OUT NOCOPY VARCHAR2,
69
70 x_contact_id out nocopy number,
71
72 x_contact_name out nocopy varchar2,
73
74 c_Attribute_Category IN VARCHAR2,
75 c_Attribute1 IN VARCHAR2,
76 c_Attribute2 IN VARCHAR2,
77 c_Attribute3 IN VARCHAR2,
78 c_Attribute4 IN VARCHAR2,
79 c_Attribute5 IN VARCHAR2,
80 c_Attribute6 IN VARCHAR2,
81 c_Attribute7 IN VARCHAR2,
82 c_Attribute8 IN VARCHAR2,
83 c_Attribute9 IN VARCHAR2,
84 c_Attribute10 IN VARCHAR2,
85 c_Attribute11 IN VARCHAR2,
86 c_Attribute12 IN VARCHAR2,
87 c_Attribute13 IN VARCHAR2,
88 c_Attribute14 IN VARCHAR2,
89 c_Attribute15 IN VARCHAR2,
90 c_Attribute16 IN VARCHAR2,
91 c_Attribute17 IN VARCHAR2,
92 c_Attribute18 IN VARCHAR2,
93 c_Attribute19 IN VARCHAR2,
94 c_Attribute20 IN VARCHAR2,
95 c_Attribute21 IN VARCHAR2,
96 c_Attribute22 IN VARCHAR2,
97 c_Attribute23 IN VARCHAR2,
98 c_Attribute24 IN VARCHAR2,
99 c_Attribute25 IN VARCHAR2,
100 c2_Attribute_Category IN VARCHAR2,
101 c2_Attribute1 IN VARCHAR2,
102 c2_Attribute2 IN VARCHAR2,
103 c2_Attribute3 IN VARCHAR2,
104 c2_Attribute4 IN VARCHAR2,
105 c2_Attribute5 IN VARCHAR2,
106 c2_Attribute6 IN VARCHAR2,
107 c2_Attribute7 IN VARCHAR2,
108 c2_Attribute8 IN VARCHAR2,
109 c2_Attribute9 IN VARCHAR2,
110 c2_Attribute10 IN VARCHAR2,
111 c2_Attribute11 IN VARCHAR2,
112 c2_Attribute12 IN VARCHAR2,
113 c2_Attribute13 IN VARCHAR2,
114 c2_Attribute14 IN VARCHAR2,
115 c2_Attribute15 IN VARCHAR2,
116 c2_Attribute16 IN VARCHAR2,
117 c2_Attribute17 IN VARCHAR2,
118 c2_Attribute18 IN VARCHAR2,
119 c2_Attribute19 IN VARCHAR2,
120 c2_Attribute20 IN VARCHAR2,
121 in_phone_country_code in varchar2 default null
122 ) IS
123
124 l_person_rec hz_party_v2pub.person_rec_type;
125 l_party_rec hz_party_v2pub.party_rec_type;
126
127 x_party_id number;
128 x_party_number varchar2(50);
129 x_profile_id number;
130
131 x_rel_party_id number;
132 x_rel_party_number hz_parties.party_number%TYPE;
133 x_party_relationship_id number;
134
135 l_org_contact_rec hz_party_contact_v2pub.org_contact_rec_type;
136 x_org_contact_id number;
137
138 x_cust_account_role_id number;
139
140 l_cust_acct_roles_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
141
142 l_gen_party_number varchar2(1);
143 l_gen_contact_number varchar2(1);
144 --
145 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
146 --
147 BEGIN
148
149 l_person_rec.person_first_name:=p_contact_first_name;
150 l_person_rec.person_last_name:=p_contact_last_name;
151 l_person_rec.person_pre_name_adjunct:=p_contact_title;
152 l_person_rec.created_by_module := NVL(p_created_by_module,G_CREATED_BY_MODULE);
153
154 l_gen_party_number := nvl(fnd_profile.value('HZ_GENERATE_PARTY_NUMBER'),'Y');
155 --l_gen_contact_number := nvl(fnd_profile.value('AR_AUTOMATIC_CONTACT_NUMBERING'),'Y');
156 l_gen_contact_number := nvl(fnd_profile.value('HZ_GENERATE_CONTACT_NUMBER'),'Y');
157
158 -- if the party_number is not automatically generated then get it
159 -- from the sequence
160 if l_gen_party_number = 'N' then
161 select hz_party_number_s.nextval
162 into l_party_rec.party_number
163 from dual;
164 end if;
165
166 l_person_rec.party_rec := l_party_rec;
167
168 HZ_PARTY_V2PUB.Create_Person(
169 p_person_rec => l_person_rec,
170 x_party_id => x_party_id,
171 x_party_number => x_party_number,
172 x_profile_id => x_profile_id,
173 x_return_status => x_return_status,
174 x_msg_count => x_msg_count,
175 x_msg_data => x_msg_data
176 );
177
178 if x_return_status in ('E','U') then
179 return;
180 end if;
181
182
183 if l_gen_party_number = 'N' then
184 select hz_party_number_s.nextval
185 into l_org_contact_rec.party_rel_rec.party_rec.party_number
186 from dual;
187 end if;
188
189 l_org_contact_rec.party_rel_rec.subject_id := x_party_id;
190 l_org_contact_rec.party_rel_rec.object_id := p_party_id;
191 l_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
192 l_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
193 l_org_contact_rec.party_rel_rec.start_date := sysdate;
194 l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
195 l_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
196 l_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
197 l_org_contact_rec.party_rel_rec.created_by_module := NVL(p_created_by_module,G_CREATED_BY_MODULE);
198
199
200 Select party_type
201 Into l_org_contact_rec.party_rel_rec.object_type
202 From HZ_PARTIES
203 Where party_id = p_party_id;
204
205 if l_gen_contact_number = 'N' then
206
207 select hz_contact_numbers_s.nextval
208 into l_org_contact_rec.contact_number
209 from dual;
210 end if;
211
212 l_org_contact_rec.title:= p_contact_title;
213 l_org_contact_rec.created_by_module := G_CREATED_BY_MODULE;
214
215
216 HZ_PARTY_CONTACT_V2PUB.Create_Org_Contact (
217 p_org_contact_rec => l_org_contact_rec,
218 x_party_id => x_rel_party_id,
219 x_party_number => x_rel_party_number,
220 x_party_rel_id => x_party_relationship_id,
221 x_org_contact_id => x_org_contact_id,
222 x_return_status => x_return_status,
223 x_msg_count => x_msg_count,
224 x_msg_data => x_msg_data
225 );
226
227 if x_return_status in ('E','U') then
228 return;
229 end if;
230
231
232 if p_email is not null then
233
234 create_contact_point(
235 in_contact_point_type =>'EMAIL',
236 in_owner_table_id=>x_rel_party_id,
237 in_email=>p_email,
238 in_phone_area_code =>NULL,
239 in_phone_number=>NULL,
240 in_phone_extension=>NULL,
241 p_created_by_module => p_created_by_module,
242 p_orig_system => p_orig_system,
243 p_orig_system_reference => p_orig_system_reference,
244 x_return_status=>x_return_status,
245 x_msg_count=>x_msg_count,
246 x_msg_data=>x_msg_data
247 );
248 if x_return_status in ('E','U') then
249 return;
250 end if;
251
252 end if;
253
254
255 if p_area_code is not null or p_phone_number is not null then
256
257 create_contact_point(
258 in_contact_point_type =>'PHONE',
259 in_owner_table_id=>x_rel_party_id,
260 in_email=>NULL,
261 in_phone_area_code =>p_area_code,
262 in_phone_number=>p_phone_number,
263 in_phone_extension=>p_extension,
264 in_phone_country_Code=>in_phone_country_code,
265 p_created_by_module => p_created_by_module,
266 p_orig_system => p_orig_system,
267 p_orig_system_reference => p_orig_system_reference,
268 x_return_status=>x_return_status,
269 x_msg_count=>x_msg_count,
270 x_msg_data=>x_msg_data,
271 c_attribute_category=>c2_attribute_category,
272 c_attribute1=>c2_attribute1,
273 c_attribute2=>c2_attribute2,
274 c_attribute3=>c2_attribute3,
275 c_attribute4=>c2_attribute4,
276 c_attribute5=>c2_attribute5,
277 c_attribute6=>c2_attribute6,
278 c_attribute7=>c2_attribute7,
279 c_attribute8=>c2_attribute8,
280 c_attribute9=>c2_attribute9,
281 c_attribute10=>c2_attribute10,
282 c_attribute11=>c2_attribute11,
283 c_attribute12=>c2_attribute12,
284 c_attribute13=>c2_attribute13,
285 c_attribute14=>c2_attribute14,
286 c_attribute15=>c2_attribute15,
287 c_attribute16=>c2_attribute16,
288 c_attribute17=>c2_attribute17,
289 c_attribute18=>c2_attribute18,
290 c_attribute19=>c2_attribute19,
291 c_attribute20=>c2_attribute20
292 );
293 if x_return_status in ('E','U') then
294 return;
295 end if;
296
297 end if;
298
299
300
301 l_cust_acct_roles_rec.party_id := x_rel_party_id;
302 l_cust_acct_roles_rec.cust_account_id := p_acct_id;
303 l_cust_acct_roles_rec.role_type := 'CONTACT';
304 l_cust_acct_roles_rec.cust_acct_site_id := NULL;
305 l_cust_acct_roles_rec.attribute_category := c_attribute_category;
306 l_cust_acct_roles_rec.attribute1 := c_attribute1;
307 l_cust_acct_roles_rec.attribute2 := c_attribute2;
308 l_cust_acct_roles_rec.attribute3 := c_attribute3;
309 l_cust_acct_roles_rec.attribute4 := c_attribute4;
310 l_cust_acct_roles_rec.attribute5 := c_attribute5;
311 l_cust_acct_roles_rec.attribute6 := c_attribute6;
312 l_cust_acct_roles_rec.attribute7 := c_attribute7;
313 l_cust_acct_roles_rec.attribute8 := c_attribute8;
314 l_cust_acct_roles_rec.attribute9 := c_attribute9;
315 l_cust_acct_roles_rec.attribute10 := c_attribute10;
316 l_cust_acct_roles_rec.attribute11 := c_attribute11;
317 l_cust_acct_roles_rec.attribute12 := c_attribute12;
318 l_cust_acct_roles_rec.attribute13 := c_attribute13;
319 l_cust_acct_roles_rec.attribute14 := c_attribute14;
320 l_cust_acct_roles_rec.attribute15 := c_attribute15;
321 l_cust_acct_roles_rec.attribute16 := c_attribute16;
322 l_cust_acct_roles_rec.attribute17 := c_attribute17;
323 l_cust_acct_roles_rec.attribute18 := c_attribute18;
324 l_cust_acct_roles_rec.attribute19 := c_attribute19;
325 l_cust_acct_roles_rec.attribute20 := c_attribute20;
326 l_cust_acct_roles_rec.created_by_module := NVL(p_created_by_module,G_CREATED_BY_MODULE);
327 l_cust_acct_roles_rec.orig_system := p_orig_system; --ER7675548
328 l_cust_acct_roles_rec.orig_system_reference := p_orig_system_reference; --ER7675548
329
330
331 HZ_CUST_ACCOUNT_ROLE_V2PUB.Create_Cust_Account_Role(
332 p_cust_account_role_rec => l_cust_acct_roles_rec,
333 x_return_status => x_return_status,
334 x_msg_count => x_msg_count,
335 x_msg_data => x_msg_data,
336 x_cust_account_role_id => x_cust_account_role_id
337 );
338
339 if x_return_status in ('E','U') then
340 return;
341 end if;
342
343 select party_name
344 into x_contact_name
345 from hz_parties
346 where party_id = x_party_id;
347
348 x_contact_id := x_cust_account_role_id;
349
350
351 END create_contact;
352
353
354 PROCEDURE create_acct_contact
355 (
356 p_acct_id in number,
357 p_contact_party_id in number,
358 x_return_status OUT NOCOPY VARCHAR2,
359
360 x_msg_count OUT NOCOPY NUMBER,
361
362 x_msg_data OUT NOCOPY VARCHAR2,
363
364 x_contact_id out nocopy number,
365
366 c_Attribute_Category IN VARCHAR2,
367 c_Attribute1 IN VARCHAR2,
368 c_Attribute2 IN VARCHAR2,
369 c_Attribute3 IN VARCHAR2,
370 c_Attribute4 IN VARCHAR2,
371 c_Attribute5 IN VARCHAR2,
372 c_Attribute6 IN VARCHAR2,
373 c_Attribute7 IN VARCHAR2,
374 c_Attribute8 IN VARCHAR2,
375 c_Attribute9 IN VARCHAR2,
376 c_Attribute10 IN VARCHAR2,
377 c_Attribute11 IN VARCHAR2,
378 c_Attribute12 IN VARCHAR2,
379 c_Attribute13 IN VARCHAR2,
380 c_Attribute14 IN VARCHAR2,
381 c_Attribute15 IN VARCHAR2,
382 c_Attribute16 IN VARCHAR2,
383 c_Attribute17 IN VARCHAR2,
384 c_Attribute18 IN VARCHAR2,
385 c_Attribute19 IN VARCHAR2,
386 c_Attribute20 IN VARCHAR2,
387 c_Attribute21 IN VARCHAR2,
388 c_Attribute22 IN VARCHAR2,
389 c_Attribute23 IN VARCHAR2,
390 c_Attribute24 IN VARCHAR2,
391 c_Attribute25 IN VARCHAR2,
392 c2_Attribute_Category IN VARCHAR2,
393 c2_Attribute1 IN VARCHAR2,
394 c2_Attribute2 IN VARCHAR2,
395 c2_Attribute3 IN VARCHAR2,
396 c2_Attribute4 IN VARCHAR2,
397 c2_Attribute5 IN VARCHAR2,
398 c2_Attribute6 IN VARCHAR2,
399 c2_Attribute7 IN VARCHAR2,
400 c2_Attribute8 IN VARCHAR2,
401 c2_Attribute9 IN VARCHAR2,
402 c2_Attribute10 IN VARCHAR2,
403 c2_Attribute11 IN VARCHAR2,
404 c2_Attribute12 IN VARCHAR2,
405 c2_Attribute13 IN VARCHAR2,
406 c2_Attribute14 IN VARCHAR2,
407 c2_Attribute15 IN VARCHAR2,
408 c2_Attribute16 IN VARCHAR2,
409 c2_Attribute17 IN VARCHAR2,
410 c2_Attribute18 IN VARCHAR2,
411 c2_Attribute19 IN VARCHAR2,
412 c2_Attribute20 IN VARCHAR2,
413 in_created_by_module in varchar2 default null
414 ) IS
415
416 x_cust_account_role_id number;
417 l_cust_acct_roles_rec hz_cust_account_role_v2pub.cust_account_role_rec_type;
418
419 /*CURSOR c_email IS
420 SELECT email_address
421 FROM hz_contact_points
422 WHERE owner_table_id = p_contact_party_id
423 AND owner_table_name = 'HZ_PARTIES'
424 AND contact_point_type = 'EMAIL'
425 AND primary_flag = 'Y'
426 AND status = 'A'; */
427
428 CURSOR c_email IS
429 SELECT email_address
430 fROM hz_parties
431 where party_id = p_contact_party_id;
432
433 l_email hz_parties.email_address%TYPE;
434 l_create_email boolean := FALSE;
435
436 --
437 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
438 --
439 BEGIN
440
441
442 /*-- if email is given by the add customer form, then we check that
443 -- email is not the one found from the lov or there are no email address
444 -- previously defined
445 if p_email is not null then
446 OPEN c_email;
447 FETCH c_email
448 INTO l_email;
449 IF c_email%FOUND then
450
451 if l_email is not null and p_email != l_email then
452 l_create_email := FALSE;
453 end if;
454 ELSIF c_email%NOTFOUND then
455 l_create_email := TRUE;
456 END IF;
457 CLOSE c_email;
458
459 IF l_create_email then
460 create_contact_point(
461 in_contact_point_type =>'EMAIL',
462 in_owner_table_id=>p_contact_party_id,
463 in_email=>p_email,
464 in_phone_area_code =>NULL,
465 in_phone_number=>NULL,
466 in_phone_extension=>NULL,
467 x_return_status=>x_return_status,
468 x_msg_count=>x_msg_count,
469 x_msg_data=>x_msg_data
470 );
471 if x_return_status in ('E','U') then
472 return;
473 end if;
474 END IF;
475
476 end if;
477
478
479 if p_area_code is not nul or p_phone_number is not null then
480
481 create_contact_point(
482 in_contact_point_type =>'PHONE',
483 in_owner_table_id=>p_contact_party_id,
484 in_email=>NULL,
485 in_phone_area_code =>p_area_code,
486 in_phone_number=>p_phone_number,
487 in_phone_extension=>p_extension,
488 x_return_status=>x_return_status,
489 x_msg_count=>x_msg_count,
490 x_msg_data=>x_msg_data,
491 c_attribute_category=>c2_attribute_category,
492 c_attribute1=>c2_attribute1,
493 c_attribute2=>c2_attribute2,
494 c_attribute3=>c2_attribute3,
495 c_attribute4=>c2_attribute4,
496 c_attribute5=>c2_attribute5,
497 c_attribute6=>c2_attribute6,
498 c_attribute7=>c2_attribute7,
499 c_attribute8=>c2_attribute8,
500 c_attribute9=>c2_attribute9,
501 c_attribute10=>c2_attribute10,
502 c_attribute11=>c2_attribute11,
503 c_attribute12=>c2_attribute12,
504 c_attribute13=>c2_attribute13,
505 c_attribute14=>c2_attribute14,
506 c_attribute15=>c2_attribute15,
507 c_attribute16=>c2_attribute16,
508 c_attribute17=>c2_attribute17,
509 c_attribute18=>c2_attribute18,
510 c_attribute19=>c2_attribute19,
511 c_attribute20=>c2_attribute20
512 );
513 if x_return_status in ('E','U') then
514 return;
515 end if;
516
517 end if; */
518
519
520 l_cust_acct_roles_rec.party_id := p_contact_party_id;
521 l_cust_acct_roles_rec.cust_account_id := p_acct_id;
522 l_cust_acct_roles_rec.role_type := 'CONTACT';
523 l_cust_acct_roles_rec.cust_acct_site_id := NULL;
524
525 -- If created by module is sent from outside like Automatic Acct Creation
526 IF in_Created_by_module is not null then
527 l_cust_acct_roles_rec.created_by_module := in_created_by_module;
528
529 ELSE
530 l_cust_acct_roles_rec.created_by_module := G_CREATED_BY_MODULE;
531 END IF;
532
533 l_cust_acct_roles_rec.attribute_category := c_attribute_category;
534 l_cust_acct_roles_rec.attribute1 := c_attribute1;
535 l_cust_acct_roles_rec.attribute2 := c_attribute2;
536 l_cust_acct_roles_rec.attribute3 := c_attribute3;
537 l_cust_acct_roles_rec.attribute4 := c_attribute4;
538 l_cust_acct_roles_rec.attribute5 := c_attribute5;
539 l_cust_acct_roles_rec.attribute6 := c_attribute6;
540 l_cust_acct_roles_rec.attribute7 := c_attribute7;
541 l_cust_acct_roles_rec.attribute8 := c_attribute8;
542 l_cust_acct_roles_rec.attribute9 := c_attribute9;
543 l_cust_acct_roles_rec.attribute10 := c_attribute10;
544 l_cust_acct_roles_rec.attribute11 := c_attribute11;
545 l_cust_acct_roles_rec.attribute12 := c_attribute12;
546 l_cust_acct_roles_rec.attribute13 := c_attribute13;
547 l_cust_acct_roles_rec.attribute14 := c_attribute14;
548 l_cust_acct_roles_rec.attribute15 := c_attribute15;
549 l_cust_acct_roles_rec.attribute16 := c_attribute16;
550 l_cust_acct_roles_rec.attribute17 := c_attribute17;
551 l_cust_acct_roles_rec.attribute18 := c_attribute18;
552 l_cust_acct_roles_rec.attribute19 := c_attribute19;
553 l_cust_acct_roles_rec.attribute20 := c_attribute20;
554
555
556
557 HZ_CUST_ACCOUNT_ROLE_V2PUB.Create_Cust_Account_Role(
558 p_cust_account_role_rec => l_cust_acct_roles_rec,
559 x_return_status => x_return_status,
560 x_msg_count => x_msg_count,
561 x_msg_data => x_msg_data,
562 x_cust_account_role_id => x_cust_account_role_id
563 );
564
565 if x_return_status in ('E','U') then
566 return;
567 end if;
568
569 x_contact_id := x_cust_account_role_id;
570
571 EXCEPTION
572 when others then
573 if c_email%ISOPEN then
574 CLOSE c_email;
575 end if;
576
577 END create_acct_contact;
578
579
580
581
582 PROCEDURE create_contact_point(
583 in_contact_point_type in varchar2,
584 in_owner_table_id in number,
585 in_email in varchar2,
586 in_phone_area_code in varchar2,
587 in_phone_number in varchar2,
588 in_phone_extension in varchar2,
589 x_return_status OUT NOCOPY VARCHAR2,
590
591 x_msg_count OUT NOCOPY NUMBER,
592
593 x_msg_data OUT NOCOPY VARCHAR2,
594
595 c_Attribute_Category IN VARCHAR2 default null,
596 c_Attribute1 IN VARCHAR2 default null,
597 c_Attribute2 IN VARCHAR2 default null,
598 c_Attribute3 IN VARCHAR2 default null,
599 c_Attribute4 IN VARCHAR2 default null,
600 c_Attribute5 IN VARCHAR2 default null,
601 c_Attribute6 IN VARCHAR2 default null,
602 c_Attribute7 IN VARCHAR2 default null,
603 c_Attribute8 IN VARCHAR2 default null,
604 c_Attribute9 IN VARCHAR2 default null,
605 c_Attribute10 IN VARCHAR2 default null,
606 c_Attribute11 IN VARCHAR2 default null,
607 c_Attribute12 IN VARCHAR2 default null,
608 c_Attribute13 IN VARCHAR2 default null,
609 c_Attribute14 IN VARCHAR2 default null,
610 c_Attribute15 IN VARCHAR2 default null,
611 c_Attribute16 IN VARCHAR2 default null,
612 c_Attribute17 IN VARCHAR2 default null,
613 c_Attribute18 IN VARCHAR2 default null,
614 c_Attribute19 IN VARCHAR2 default null,
615 c_Attribute20 IN VARCHAR2 default null,
616 in_phone_country_code in varchar2 default null,
617 p_created_by_module IN VARCHAR2 DEFAULT NULL,
618 p_orig_system IN VARCHAR2 DEFAULT NULL, --ER7675548
619 p_orig_system_reference IN VARCHAR2 DEFAULT NULL --ER7675548
620 ) IS
621
622 x_contact_point_id number;
623
624 l_contact_points_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
625 l_email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type;
626 l_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
627
628 --
629 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
630 --
631 BEGIN
632
633 l_contact_points_rec.contact_point_type := in_contact_point_type;
634 l_contact_points_rec.status := 'A';
635 l_contact_points_rec.owner_table_name := 'HZ_PARTIES';
636 l_contact_points_rec.owner_table_id := in_owner_table_id;
637 l_contact_points_rec.primary_flag := 'Y';
638 l_contact_points_rec.created_by_module := NVL(p_created_by_module,G_CREATED_BY_MODULE);
639
640 l_contact_points_rec.orig_system := p_orig_system; --ER7675548
641 l_contact_points_rec.orig_system_reference := p_orig_system_reference; --ER7675548
642
643 select hz_contact_points_s.nextval
644 into l_contact_points_rec.contact_point_id
645 from dual;
646
647
648 IF in_contact_point_type = 'EMAIL' then
649 l_email_rec.email_address := in_email;
650
651 HZ_CONTACT_POINT_V2PUB.Create_Contact_Point(
652 p_contact_point_rec => l_contact_points_rec,
653 p_email_rec => l_email_rec,
654 x_return_status => x_return_status,
655 x_msg_count => x_msg_count,
656 x_msg_data => x_msg_data,
657 x_contact_point_id => x_contact_point_id
658 );
659
660 ELSIF in_contact_point_type = 'PHONE' then
661
662 l_phone_rec.phone_area_code := in_phone_area_code;
663 l_phone_rec.phone_number := in_phone_number;
664 l_phone_rec.phone_extension := in_phone_extension;
665 l_phone_rec.phone_country_code := in_phone_country_code;
666 l_phone_rec.phone_line_type := 'GEN';
667
668 l_contact_points_rec.attribute_category := c_attribute_category;
669 l_contact_points_rec.attribute1 := c_attribute1;
670 l_contact_points_rec.attribute2 := c_attribute2;
671 l_contact_points_rec.attribute3 := c_attribute3;
672 l_contact_points_rec.attribute4 := c_attribute4;
673 l_contact_points_rec.attribute5 := c_attribute5;
674 l_contact_points_rec.attribute6 := c_attribute6;
675 l_contact_points_rec.attribute7 := c_attribute7;
676 l_contact_points_rec.attribute8 := c_attribute8;
677 l_contact_points_rec.attribute9 := c_attribute9;
678 l_contact_points_rec.attribute10 := c_attribute10;
679 l_contact_points_rec.attribute11 := c_attribute11;
680 l_contact_points_rec.attribute12 := c_attribute12;
681 l_contact_points_rec.attribute13 := c_attribute13;
682 l_contact_points_rec.attribute14 := c_attribute14;
683 l_contact_points_rec.attribute15 := c_attribute15;
684 l_contact_points_rec.attribute16 := c_attribute16;
685 l_contact_points_rec.attribute17 := c_attribute17;
686 l_contact_points_rec.attribute18 := c_attribute18;
687 l_contact_points_rec.attribute19 := c_attribute19;
688 l_contact_points_rec.attribute20 := c_attribute20;
689
690 HZ_CONTACT_POINT_V2PUB.Create_Contact_Point(
691 p_contact_point_rec => l_contact_points_rec,
692 p_phone_rec => l_phone_rec,
693 x_return_status => x_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data,
696 x_contact_point_id => x_contact_point_id
697 );
698
699 END IF;
700
701
702 END create_contact_point;
703
704
705
706 PROCEDURE Create_Location
707 (
708 p_country IN Varchar2,
709 p_address1 IN Varchar2,
710 p_address2 IN Varchar2,
711 p_address3 IN Varchar2,
712 p_address4 IN Varchar2,
713 p_city IN Varchar2,
714 p_postal_code IN Varchar2,
715 p_state IN Varchar2,
716 p_province IN varchar2,
717 p_county IN Varchar2,
718 p_address_style IN Varchar2,
719 p_address_line_phonetic IN Varchar2,
720 p_created_by_module IN VARCHAR2 DEFAULT NULL,
721 p_orig_system IN VARCHAR2 DEFAULT NULL, --ER7675548
722 p_orig_system_reference IN VARCHAR2 DEFAULT NULL, --ER7675548
723 c_Attribute_Category IN VARCHAR2,
724 c_Attribute1 IN VARCHAR2,
725 c_Attribute2 IN VARCHAR2,
726 c_Attribute3 IN VARCHAR2,
727 c_Attribute4 IN VARCHAR2,
728 c_Attribute5 IN VARCHAR2,
729 c_Attribute6 IN VARCHAR2,
730 c_Attribute7 IN VARCHAR2,
731 c_Attribute8 IN VARCHAR2,
732 c_Attribute9 IN VARCHAR2,
733 c_Attribute10 IN VARCHAR2,
734 c_Attribute11 IN VARCHAR2,
735 c_Attribute12 IN VARCHAR2,
736 c_Attribute13 IN VARCHAR2,
737 c_Attribute14 IN VARCHAR2,
738 c_Attribute15 IN VARCHAR2,
739 c_Attribute16 IN VARCHAR2,
740 c_Attribute17 IN VARCHAR2,
741 c_Attribute18 IN VARCHAR2,
742 c_Attribute19 IN VARCHAR2,
743 c_Attribute20 IN VARCHAR2,
744 c_global_Attribute_Category IN VARCHAR2,
745 c_global_Attribute1 IN VARCHAR2,
746 c_global_Attribute2 IN VARCHAR2,
747 c_global_Attribute3 IN VARCHAR2,
748 c_global_Attribute4 IN VARCHAR2,
749 c_global_Attribute5 IN VARCHAR2,
750 c_global_Attribute6 IN VARCHAR2,
751 c_global_Attribute7 IN VARCHAR2,
752 c_global_Attribute8 IN VARCHAR2,
753 c_global_Attribute9 IN VARCHAR2,
754 c_global_Attribute10 IN VARCHAR2,
755 c_global_Attribute11 IN VARCHAR2,
756 c_global_Attribute12 IN VARCHAR2,
757 c_global_Attribute13 IN VARCHAR2,
758 c_global_Attribute14 IN VARCHAR2,
759 c_global_Attribute15 IN VARCHAR2,
760 c_global_Attribute16 IN VARCHAR2,
761 c_global_Attribute17 IN VARCHAR2,
762 c_global_Attribute18 IN VARCHAR2,
763 c_global_Attribute19 IN VARCHAR2,
764 c_global_Attribute20 IN VARCHAR2,
765 x_location_id OUT NOCOPY Number,
766
767 x_return_status OUT NOCOPY VARCHAR2,
768
769 x_msg_count OUT NOCOPY NUMBER,
770
771 x_msg_data OUT NOCOPY VARCHAR2
772
773 ) IS
774
775 l_location_rec HZ_LOCATION_V2PUB.location_rec_type;
776 l_msg_count number;
777 l_msg_data Varchar2(4000);
778 l_return_status Varchar2(1);
779
780 --
781 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
782 --
783 BEGIN
784
785 l_location_rec.country :=p_country;
786 l_location_rec.address1 :=p_address1;
787 l_location_rec.address2 :=p_address2;
788 l_location_rec.address3 :=p_address3;
789 l_location_rec.address4 :=p_address4;
790 l_location_rec.city :=p_city;
791 l_location_rec.state :=p_state;
792 l_location_rec.postal_code:=p_postal_code;
793 l_location_rec.province:=p_province;
794 l_location_rec.county:=p_county;
795 l_location_rec.address_style:=p_address_style;
796 l_location_rec.address_lines_phonetic:=p_address_line_phonetic; --Bug# 7575444
797 l_location_rec.attribute_category := c_attribute_category;
798 l_location_rec.attribute1 := c_attribute1;
799 l_location_rec.attribute2 := c_attribute2;
800 l_location_rec.attribute3 := c_attribute3;
801 l_location_rec.attribute4 := c_attribute4;
802 l_location_rec.attribute5 := c_attribute5;
803 l_location_rec.attribute6 := c_attribute6;
804 l_location_rec.attribute7 := c_attribute7;
805 l_location_rec.attribute8 := c_attribute8;
806 l_location_rec.attribute9 := c_attribute9;
807 l_location_rec.attribute10 := c_attribute10;
808 l_location_rec.attribute11 := c_attribute11;
809 l_location_rec.attribute12 := c_attribute12;
810 l_location_rec.attribute13 := c_attribute13;
811 l_location_rec.attribute14 := c_attribute14;
812 l_location_rec.attribute15 := c_attribute15;
813 l_location_rec.attribute16 := c_attribute16;
814 l_location_rec.attribute17 := c_attribute17;
815 l_location_rec.attribute18 := c_attribute18;
816 l_location_rec.attribute19 := c_attribute19;
817 l_location_rec.attribute20 := c_attribute20;
818
819 l_location_rec.created_by_module := NVL(p_created_by_module,G_CREATED_BY_MODULE); --ER7675548
820 l_location_rec.orig_system := p_orig_system; --ER7675548
821 l_location_rec.orig_system_reference := p_orig_system_reference ; --ER7675548
822
823 HZ_LOCATION_V2PUB.Create_Location(
824 p_init_msg_list => Null
825 ,p_location_rec => l_location_rec
826 ,x_return_status => l_return_status
827 ,x_msg_count => l_msg_count
828 ,x_msg_data => l_msg_data
829 ,x_location_id => x_location_id
830 );
831
832 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
834 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
835 RAISE FND_API.G_EXC_ERROR;
836 END IF;
837
838 x_return_status := FND_API.G_RET_STS_SUCCESS;
839
840 -- Get message count and data
841
842 OE_MSG_PUB.Count_And_Get
843 ( p_count => x_msg_count
844 , p_data => x_msg_data
845 );
846
847 oe_debug_pub.add('Exiting oe_oe_inline_address.create_location', 1);
848
849 EXCEPTION
850
851 WHEN FND_API.G_EXC_ERROR THEN
852
853
854 IF l_debug_level > 0 THEN
855 oe_debug_pub.add( 'INSIDE CREATE_LOCATION EXC ERROR' , 1 ) ;
856 END IF;
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 OE_MSG_PUB.Count_And_Get
859 ( p_count => x_msg_count
860 , p_data => x_msg_data
861 );
862
863 -- Get message count and data
864
865 OE_MSG_PUB.Count_And_Get
866 ( p_count => x_msg_count
867 , p_data => x_msg_data
868 );
869
870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
871
872
873 IF l_debug_level > 0 THEN
874 oe_debug_pub.add( 'INSIDE CREATE_LOCATION UNEXPECTED ERROR' , 1 ) ;
875 END IF;
876 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
877 OE_MSG_PUB.Count_And_Get
878 ( p_count => x_msg_count
879 , p_data => x_msg_data
880 );
881
882 -- Get message count and data
883
884 OE_MSG_PUB.Count_And_Get
885 ( p_count => x_msg_count
886 , p_data => x_msg_data
887 );
888
889 WHEN OTHERS THEN
890
891
892 IF l_debug_level > 0 THEN
893 oe_debug_pub.add( 'INSIDE CREATE_LOCATION WHEN OTHERS' , 1 ) ;
894 END IF;
895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
896 OE_MSG_PUB.Count_And_Get
897 ( p_count => x_msg_count
898 , p_data => x_msg_data
899 );
900
901 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
902 THEN
903 OE_MSG_PUB.Add_Exc_Msg
904 ( G_PKG_NAME
905 , 'Create_Location'
906 );
907 END IF;
908
909 -- Get message count and data
910
911 OE_MSG_PUB.Count_And_Get
912 ( p_count => x_msg_count
913 , p_data => x_msg_data
914 );
915
916 END Create_Location;
917
918
919 PROCEDURE Create_Party_Site
920 (
921 p_party_id IN Number,
922 p_location_id IN Number,
923 p_party_site_number IN VARCHAR2,
924 p_created_by_module IN VARCHAR2 DEFAULT NULL,
925 p_orig_system IN VARCHAR2 DEFAULT NULL, --ER7675548
926 p_orig_system_reference IN VARCHAR2 DEFAULT NULL, --ER7675548
927 x_party_site_id OUT NOCOPY NUMBER,
928
929 x_party_site_number OUT NOCOPY VARCHAR2,
930
931 x_return_status OUT NOCOPY VARCHAR2,
932
933 x_msg_count OUT NOCOPY NUMBER,
934
935 x_msg_data OUT NOCOPY VARCHAR2
936
937 )IS
938 l_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
939 tmp_var VARCHAR2(2000);
940 tmp_var1 VARCHAR2(2000);
941 x number;
942
943 --
944 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
945 --
946 BEGIN
947 IF l_debug_level > 0 THEN
948 oe_debug_pub.add( 'ENTERING OE_OE_FORM_HEADER.CREATE_PARTY_SITE' , 1 ) ;
949 END IF;
950 l_party_site_rec.party_id:= p_party_id;
951 l_party_site_rec.location_id:=p_location_id;
952 -- Party Site Number Should be sent only if auto-numbering set off . Add
953 -- Validation to check this
954 IF p_party_site_Number IS NOT NULL THEN
955 l_party_site_rec.party_site_number:=p_party_site_number;
956 END IF;
957
958 l_party_site_rec.created_by_module := NVL(p_created_by_module,G_CREATED_BY_MODULE); --ER7675548
959 l_party_site_rec.orig_system := p_orig_system; --ER7675548
960 l_party_site_rec.orig_system_reference := p_orig_system_reference; --ER7675548
961
962
963
964 HZ_PARTY_SITE_V2PUB.Create_Party_Site
965 (
966 p_party_site_rec => l_party_site_rec,
967 x_party_site_id => x_party_site_id,
968 x_party_site_number => x_party_site_number,
969 x_return_status => x_return_status,
970 x_msg_count => x_msg_count,
971 x_msg_data => x_msg_data
972 );
973
974 IF l_debug_level > 0 THEN
975 oe_debug_pub.add( 'AFTER CALL HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE'||X_RETURN_STATUS , 1 ) ;
976 END IF;
977
978 IF l_debug_level > 0 THEN
979 oe_debug_pub.add( 'AFTER CALL HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE MSG'||X_MSG_DATA , 1 ) ;
980 END IF;
981
982 IF x_msg_count = 1 THEN
983 --x_msg_data := x_msg_data || '**CREATE_PARTY_SITE**';
984 return;
985 ELSIF
986 x_msg_count > 1 THEN
987 FOR x IN 1..x_msg_count LOOP
988 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
989 tmp_var1 := tmp_var1 || ' ' || tmp_var;
990 END LOOP;
991 x_msg_data := tmp_var1;
992 --x_msg_data := x_msg_data || '**CREATE_PARTY_SITE**';
993 return;
994 END IF;
995
996 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
999 RAISE FND_API.G_EXC_ERROR;
1000 END IF;
1001
1002 x_return_status := FND_API.G_RET_STS_SUCCESS;
1003
1004 -- Get message count and data
1005
1006 OE_MSG_PUB.Count_And_Get
1007 ( p_count => x_msg_count
1008 , p_data => x_msg_data
1009 );
1010
1011
1012 EXCEPTION
1013
1014 WHEN FND_API.G_EXC_ERROR THEN
1015
1016
1017 x_return_status := FND_API.G_RET_STS_ERROR;
1018
1019 -- Get message count and data
1020
1021 OE_MSG_PUB.Count_And_Get
1022 ( p_count => x_msg_count
1023 , p_data => x_msg_data
1024 );
1025
1026 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1027
1028
1029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1030
1031 -- Get message count and data
1032
1033 OE_MSG_PUB.Count_And_Get
1034 ( p_count => x_msg_count
1035 , p_data => x_msg_data
1036 );
1037
1038 WHEN OTHERS THEN
1039
1040
1041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1042
1043 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1044 THEN
1045 OE_MSG_PUB.Add_Exc_Msg
1046 ( G_PKG_NAME
1047 , 'Create_Location'
1048 );
1049 END IF;
1050
1051 -- Get message count and data
1052
1053 OE_MSG_PUB.Count_And_Get
1054 ( p_count => x_msg_count
1055 , p_data => x_msg_data
1056 );
1057
1058 END Create_Party_Site;
1059
1060
1061 PROCEDURE Create_Account_Site
1062 (
1063 p_cust_account_id IN NUMBER,
1064 p_party_site_id IN NUMBER,
1065 p_orig_system IN VARCHAR2 DEFAULT NULL, --ER7675548
1066 p_orig_system_reference IN VARCHAR2 DEFAULT NULL, --ER7675548
1067 c_Attribute_Category IN VARCHAR2,
1068 c_Attribute1 IN VARCHAR2,
1069 c_Attribute2 IN VARCHAR2,
1070 c_Attribute3 IN VARCHAR2,
1071 c_Attribute4 IN VARCHAR2,
1072 c_Attribute5 IN VARCHAR2,
1073 c_Attribute6 IN VARCHAR2,
1074 c_Attribute7 IN VARCHAR2,
1075 c_Attribute8 IN VARCHAR2,
1076 c_Attribute9 IN VARCHAR2,
1077 c_Attribute10 IN VARCHAR2,
1078 c_Attribute11 IN VARCHAR2,
1079 c_Attribute12 IN VARCHAR2,
1080 c_Attribute13 IN VARCHAR2,
1081 c_Attribute14 IN VARCHAR2,
1082 c_Attribute15 IN VARCHAR2,
1083 c_Attribute16 IN VARCHAR2,
1084 c_Attribute17 IN VARCHAR2,
1085 c_Attribute18 IN VARCHAR2,
1086 c_Attribute19 IN VARCHAR2,
1087 c_Attribute20 IN VARCHAR2,
1088 x_customer_site_id OUT NOCOPY NUMBER,
1089
1090 x_return_status OUT NOCOPY VARCHAR2,
1091
1092 x_msg_count OUT NOCOPY NUMBER,
1093
1094 x_msg_data OUT NOCOPY VARCHAR2,
1095
1096 in_created_by_module in varchar2 default null
1097 ) IS
1098 l_account_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
1099 tmp_var VARCHAR2(2000);
1100 tmp_var1 VARCHAR2(2000);
1101 x number;
1102
1103 --
1104 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1105 --
1106 BEGIN
1107
1108 l_account_site_rec.party_site_id:=p_party_site_id;
1109 l_account_site_rec.cust_account_id:=p_cust_account_id;
1110
1111 l_account_site_rec.attribute_category := c_attribute_category;
1112 l_account_site_rec.attribute1 := c_attribute1;
1113 l_account_site_rec.attribute2 := c_attribute2;
1114 l_account_site_rec.attribute3 := c_attribute3;
1115 l_account_site_rec.attribute4 := c_attribute4;
1116 l_account_site_rec.attribute5 := c_attribute5;
1117 l_account_site_rec.attribute6 := c_attribute6;
1118 l_account_site_rec.attribute7 := c_attribute7;
1119 l_account_site_rec.attribute8 := c_attribute8;
1120 l_account_site_rec.attribute9 := c_attribute9;
1121 l_account_site_rec.attribute10 := c_attribute10;
1122 l_account_site_rec.attribute11 := c_attribute11;
1123 l_account_site_rec.attribute12 := c_attribute12;
1124 l_account_site_rec.attribute13 := c_attribute13;
1125 l_account_site_rec.attribute14 := c_attribute14;
1126 l_account_site_rec.attribute15 := c_attribute15;
1127 l_account_site_rec.attribute16 := c_attribute16;
1128 l_account_site_rec.attribute17 := c_attribute17;
1129 l_account_site_rec.attribute18 := c_attribute18;
1130 l_account_site_rec.attribute19 := c_attribute19;
1131 l_account_site_rec.attribute20 := c_attribute20;
1132
1133 -- If created by module is sent from outside like Automatic Acct Creation
1134 IF in_Created_by_module is not null then
1135 l_account_site_rec.created_by_module := in_created_by_module;
1136
1137 ELSE
1138 l_account_site_rec.created_by_module := G_CREATED_BY_MODULE;
1139 END IF;
1140
1141 l_account_site_rec.orig_system := p_orig_system; --ER7675548
1142 l_account_site_rec.orig_system_reference := p_orig_system_reference; --ER7675548
1143
1144 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Acct_Site
1145 (
1146 p_cust_acct_site_rec => l_account_site_rec,
1147 x_return_status => x_return_status,
1148 x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data,
1150 x_cust_acct_site_id => x_customer_site_id
1151 );
1152
1153 IF x_msg_count = 1 THEN
1154 --x_msg_data := x_msg_data || '**CREATE_PARTY_SITE**';
1155 return;
1156 ELSIF
1157 x_msg_count > 1 THEN
1158 FOR x IN 1..x_msg_count LOOP
1159 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1160 tmp_var1 := tmp_var1 || ' ' || tmp_var;
1161 END LOOP;
1162 x_msg_data := tmp_var1;
1163 --x_msg_data := x_msg_data || '**CREATE_PARTY_SITE**';
1164 return;
1165 END IF;
1166
1167 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1169 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1170 RAISE FND_API.G_EXC_ERROR;
1171 END IF;
1172
1173 x_return_status := FND_API.G_RET_STS_SUCCESS;
1174
1175 -- Get message count and data
1176
1177 OE_MSG_PUB.Count_And_Get
1178 ( p_count => x_msg_count
1179 , p_data => x_msg_data
1180 );
1181
1182
1183 EXCEPTION
1184
1185 WHEN FND_API.G_EXC_ERROR THEN
1186
1187
1188 x_return_status := FND_API.G_RET_STS_ERROR;
1189
1190 -- Get message count and data
1191
1192 OE_MSG_PUB.Count_And_Get
1193 ( p_count => x_msg_count
1194 , p_data => x_msg_data
1195 );
1196
1197 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1198
1199
1200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1201
1202 -- Get message count and data
1203
1204 OE_MSG_PUB.Count_And_Get
1205 ( p_count => x_msg_count
1206 , p_data => x_msg_data
1207 );
1208
1209 WHEN OTHERS THEN
1210
1211
1212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1213
1214 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1215 THEN
1216 OE_MSG_PUB.Add_Exc_Msg
1217 ( G_PKG_NAME
1218 , 'Create_Location'
1219 );
1220 END IF;
1221
1222 -- Get message count and data
1223
1224 OE_MSG_PUB.Count_And_Get
1225 ( p_count => x_msg_count
1226 , p_data => x_msg_data
1227 );
1228 END Create_Account_Site;
1229
1230
1231
1232
1233 PROCEDURE Create_Acct_Site_Uses
1234 (
1235 p_cust_acct_site_id IN NUMBER,
1236 p_location IN Varchar2,
1237 p_site_use_code IN Varchar2,
1238 x_site_use_id OUT NOCOPY NUMBER,
1239
1240 x_return_status OUT NOCOPY VARCHAR2,
1241
1242 x_msg_count OUT NOCOPY NUMBER,
1243
1244 x_msg_data OUT NOCOPY VARCHAR2,
1245
1246 c_Attribute_Category IN VARCHAR2,
1247 c_Attribute1 IN VARCHAR2,
1248 c_Attribute2 IN VARCHAR2,
1249 c_Attribute3 IN VARCHAR2,
1250 c_Attribute4 IN VARCHAR2,
1251 c_Attribute5 IN VARCHAR2,
1252 c_Attribute6 IN VARCHAR2,
1253 c_Attribute7 IN VARCHAR2,
1254 c_Attribute8 IN VARCHAR2,
1255 c_Attribute9 IN VARCHAR2,
1256 c_Attribute10 IN VARCHAR2,
1257 c_Attribute11 IN VARCHAR2,
1258 c_Attribute12 IN VARCHAR2,
1259 c_Attribute13 IN VARCHAR2,
1260 c_Attribute14 IN VARCHAR2,
1261 c_Attribute15 IN VARCHAR2,
1262 c_Attribute16 IN VARCHAR2,
1263 c_Attribute17 IN VARCHAR2,
1264 c_Attribute18 IN VARCHAR2,
1265 c_Attribute19 IN VARCHAR2,
1266 c_Attribute20 IN VARCHAR2,
1267 c_Attribute21 IN VARCHAR2,
1268 c_Attribute22 IN VARCHAR2,
1269 c_Attribute23 IN VARCHAR2,
1270 c_Attribute24 IN VARCHAR2,
1271 c_Attribute25 IN VARCHAR2,
1272 in_created_by_module in varchar2 default null,
1273 in_primary_flag in varchar2 default null
1274 ) IS
1275
1276 l_acct_site_uses HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
1277 l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
1278
1279 --
1280 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1281 --
1282 BEGIN
1283
1284 l_acct_site_uses.cust_acct_site_id := p_cust_acct_site_id;
1285 l_acct_site_uses.site_use_code := p_site_use_code;
1286 l_acct_site_uses.location := p_location;
1287 l_acct_site_uses.attribute_category := c_attribute_category;
1288 l_acct_site_uses.attribute1 := c_attribute1;
1289 l_acct_site_uses.attribute2 := c_attribute2;
1290 l_acct_site_uses.attribute3 := c_attribute3;
1291 l_acct_site_uses.attribute4 := c_attribute4;
1292 l_acct_site_uses.attribute5 := c_attribute5;
1293 l_acct_site_uses.attribute6 := c_attribute6;
1294 l_acct_site_uses.attribute7 := c_attribute7;
1295 l_acct_site_uses.attribute8 := c_attribute8;
1296 l_acct_site_uses.attribute9 := c_attribute9;
1297 l_acct_site_uses.attribute10 := c_attribute10;
1298 l_acct_site_uses.attribute11 := c_attribute11;
1299 l_acct_site_uses.attribute12 := c_attribute12;
1300 l_acct_site_uses.attribute13 := c_attribute13;
1301 l_acct_site_uses.attribute14 := c_attribute14;
1302 l_acct_site_uses.attribute15 := c_attribute15;
1303 l_acct_site_uses.attribute16 := c_attribute16;
1304 l_acct_site_uses.attribute17 := c_attribute17;
1305 l_acct_site_uses.attribute18 := c_attribute18;
1306 l_acct_site_uses.attribute19 := c_attribute19;
1307 l_acct_site_uses.attribute20 := c_attribute20;
1308 l_acct_site_uses.attribute21 := c_attribute21;
1309 l_acct_site_uses.attribute22 := c_attribute22;
1310 l_acct_site_uses.attribute23 := c_attribute23;
1311 l_acct_site_uses.attribute24 := c_attribute24;
1312 l_acct_site_uses.attribute25 := c_attribute25;
1313
1314 IF in_primary_flag IS NOT NULL THEN
1315 l_acct_site_uses.primary_flag := in_primary_flag;
1316 END IF;
1317
1318 -- If created by module is sent from outside like Automatic Acct Creation
1319 IF in_Created_by_module is not null then
1320 l_acct_site_uses.created_by_module := in_created_by_module;
1321
1322 ELSE
1323 l_acct_site_uses.created_by_module := G_CREATED_BY_MODULE;
1324 END IF;
1325
1326
1327 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use
1328 (
1329 p_cust_site_use_rec => l_acct_site_uses,
1330 p_customer_profile_rec => l_cust_profile_rec,
1331 p_create_profile => FND_API.G_FALSE,
1332 x_return_status => x_return_status,
1333 x_msg_count => x_msg_count,
1334 x_msg_data => x_msg_data,
1335 x_site_use_id => x_site_use_id
1336 );
1337
1338 END Create_Acct_Site_Uses;
1339
1340
1341
1342
1343 PROCEDURE Create_Account
1344 (
1345 p_party_number IN Varchar2,
1346 p_organization_name IN Varchar2,
1347 p_alternate_name IN Varchar2,
1348 p_tax_reference IN Varchar2,
1349 p_taxpayer_id IN Varchar2,
1350 p_party_id IN Number,
1351 p_first_name IN Varchar2,
1352 p_last_name IN Varchar2,
1353 p_middle_name IN Varchar2,
1354 p_name_suffix IN Varchar2,
1355 p_title IN Varchar2,
1356 p_party_type IN Varchar2,
1357 p_email IN Varchar2,
1358 c_Attribute_Category IN VARCHAR2,
1359 c_Attribute1 IN VARCHAR2,
1360 c_Attribute2 IN VARCHAR2,
1361 c_Attribute3 IN VARCHAR2,
1362 c_Attribute4 IN VARCHAR2,
1363 c_Attribute5 IN VARCHAR2,
1364 c_Attribute6 IN VARCHAR2,
1365 c_Attribute7 IN VARCHAR2,
1366 c_Attribute8 IN VARCHAR2,
1367 c_Attribute9 IN VARCHAR2,
1368 c_Attribute10 IN VARCHAR2,
1369 c_Attribute11 IN VARCHAR2,
1370 c_Attribute12 IN VARCHAR2,
1371 c_Attribute13 IN VARCHAR2,
1372 c_Attribute14 IN VARCHAR2,
1373 c_Attribute15 IN VARCHAR2,
1374 c_Attribute16 IN VARCHAR2,
1375 c_Attribute17 IN VARCHAR2,
1376 c_Attribute18 IN VARCHAR2,
1377 c_Attribute19 IN VARCHAR2,
1378 c_Attribute20 IN VARCHAR2,
1379 c_global_Attribute_Category IN VARCHAR2,
1380 c_global_Attribute1 IN VARCHAR2,
1381 c_global_Attribute2 IN VARCHAR2,
1382 c_global_Attribute3 IN VARCHAR2,
1383 c_global_Attribute4 IN VARCHAR2,
1384 c_global_Attribute5 IN VARCHAR2,
1385 c_global_Attribute6 IN VARCHAR2,
1386 c_global_Attribute7 IN VARCHAR2,
1387 c_global_Attribute8 IN VARCHAR2,
1388 c_global_Attribute9 IN VARCHAR2,
1389 c_global_Attribute10 IN VARCHAR2,
1390 c_global_Attribute11 IN VARCHAR2,
1391 c_global_Attribute12 IN VARCHAR2,
1392 c_global_Attribute13 IN VARCHAR2,
1393 c_global_Attribute14 IN VARCHAR2,
1394 c_global_Attribute15 IN VARCHAR2,
1395 c_global_Attribute16 IN VARCHAR2,
1396 c_global_Attribute17 IN VARCHAR2,
1397 c_global_Attribute18 IN VARCHAR2,
1398 c_global_Attribute19 IN VARCHAR2,
1399 c_global_Attribute20 IN VARCHAR2,
1400 x_party_id OUT NOCOPY Number,
1401
1402 x_party_number OUT NOCOPY Varchar2,
1403
1404 x_cust_Account_id OUT NOCOPY NUMBER,
1405
1406 x_cust_account_number IN OUT NOCOPY /* file.sql.39 change */ varchar2,
1407 x_return_status OUT NOCOPY VARCHAR2,
1408
1409 x_msg_count OUT NOCOPY NUMBER,
1410
1411 x_msg_data OUT NOCOPY VARCHAR2,
1412
1413 in_created_by_module in varchar2 default null,
1414 p_orig_system IN VARCHAR2 DEFAULT NULL, --ER7675548
1415 p_orig_system_reference IN VARCHAR2 DEFAULT NULL, --ER7675548
1416 p_account_description IN VARCHAR2 DEFAULT NULL --ER7675548
1417 ) IS
1418
1419 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
1420 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
1421 l_party_rec HZ_PARTY_V2PUB.party_rec_type;
1422 l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
1423 l_account_rec HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
1424
1425 x_profile_id NUMBER;
1426
1427 --
1428 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1429 --
1430 BEGIN
1431
1432 IF p_account_description IS NOT NULL THEN
1433 l_account_rec.account_name := p_account_description; --ER7675548
1434 END IF;
1435 l_account_rec.account_number := x_cust_account_number;
1436 l_account_rec.attribute_category := c_attribute_category;
1437 l_account_rec.attribute1 := c_attribute1;
1438 l_account_rec.attribute2 := c_attribute2;
1439 l_account_rec.attribute3 := c_attribute3;
1440 l_account_rec.attribute4 := c_attribute4;
1441 l_account_rec.attribute5 := c_attribute5;
1442 l_account_rec.attribute6 := c_attribute6;
1443 l_account_rec.attribute7 := c_attribute7;
1444 l_account_rec.attribute8 := c_attribute8;
1445 l_account_rec.attribute9 := c_attribute9;
1446 l_account_rec.attribute10 := c_attribute10;
1447 l_account_rec.attribute11 := c_attribute11;
1448 l_account_rec.attribute12 := c_attribute12;
1449 l_account_rec.attribute13 := c_attribute13;
1450 l_account_rec.attribute14 := c_attribute14;
1451 l_account_rec.attribute15 := c_attribute15;
1452 l_account_rec.attribute16 := c_attribute16;
1453 l_account_rec.attribute17 := c_attribute17;
1454 l_account_rec.attribute18 := c_attribute18;
1455 l_account_rec.attribute19 := c_attribute19;
1456 l_account_rec.attribute20 := c_attribute20;
1457
1458 l_account_rec.global_attribute_category := c_global_attribute_category;
1459 l_account_rec.global_attribute1 := c_global_attribute1;
1460 l_account_rec.global_attribute2 := c_global_attribute2;
1461 l_account_rec.global_attribute3 := c_global_attribute3;
1462 l_account_rec.global_attribute4 := c_global_attribute4;
1463 l_account_rec.global_attribute5 := c_global_attribute5;
1464 l_account_rec.global_attribute6 := c_global_attribute6;
1465 l_account_rec.global_attribute7 := c_global_attribute7;
1466 l_account_rec.global_attribute8 := c_global_attribute8;
1467 l_account_rec.global_attribute9 := c_global_attribute9;
1468 l_account_rec.global_attribute10 := c_global_attribute10;
1469 l_account_rec.global_attribute11 := c_global_attribute11;
1470 l_account_rec.global_attribute12 := c_global_attribute12;
1471 l_account_rec.global_attribute13 := c_global_attribute13;
1472 l_account_rec.global_attribute14 := c_global_attribute14;
1473 l_account_rec.global_attribute15 := c_global_attribute15;
1474 l_account_rec.global_attribute16 := c_global_attribute16;
1475 l_account_rec.global_attribute17 := c_global_attribute17;
1476 l_account_rec.global_attribute18 := c_global_attribute18;
1477 l_account_rec.global_attribute19 := c_global_attribute19;
1478 l_account_rec.global_attribute20 := c_global_attribute20;
1479
1480
1481 -- If created by module is sent from outside like Automatic Acct Creation
1482 IF in_Created_by_module is not null then
1483 l_account_rec.created_by_module := in_created_by_module;
1484
1485 ELSE
1486 l_account_rec.created_by_module := G_CREATED_BY_MODULE;
1487 END IF;
1488
1489 l_account_rec.orig_system := p_orig_system; --ER7675548
1490 l_account_rec.orig_system_reference := p_orig_system_reference; --ER7675548
1491
1492 if p_party_type = 'PERSON' then
1493
1494 l_person_rec.person_first_name:=p_first_name;
1495 l_person_rec.person_last_name:=p_last_name;
1496 l_person_rec.person_middle_name:=p_middle_name;
1497 l_person_rec.tax_reference:=p_tax_reference;
1498 l_person_rec.jgzz_fiscal_code:=p_taxpayer_id;
1499 l_person_rec.person_name_suffix:=p_name_suffix;
1500 IF l_debug_level > 0 THEN
1501 oe_debug_pub.add( 'CUSTOMER PERSON TITLE='||P_TITLE ) ;
1502 END IF;
1503 l_person_rec.person_pre_name_adjunct := p_title;
1504 l_party_rec.party_number := p_party_number;
1505
1506 If p_party_id is not null then
1507 l_party_rec.party_id := p_party_id;
1508 end if;
1509 l_person_rec.party_rec := l_party_rec;
1510
1511
1512 else
1513 l_organization_rec.organization_name:=p_organization_name;
1514 l_organization_rec.organization_name_phonetic:=p_alternate_name;
1515 l_organization_rec.tax_reference:=p_tax_reference;
1516 l_organization_rec.jgzz_fiscal_code:=p_taxpayer_id;
1517 l_party_rec.party_number := p_party_number;
1518
1519 if p_party_id is not null then
1520 l_party_rec.party_id := p_party_id;
1521 end if;
1522 l_organization_rec.party_rec := l_party_rec;
1523
1524 end if;
1525
1526 IF p_party_type = 'PERSON' then
1527
1528 HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Account
1529 (
1530 p_person_rec => l_person_rec,
1531 p_cust_account_rec => l_account_rec,
1532 p_customer_profile_rec => l_cust_profile_rec,
1533 x_party_id => x_party_id,
1534 x_party_number => x_party_number,
1535 x_cust_account_id => x_cust_account_id,
1536 x_account_number => x_cust_account_number,
1537 x_profile_id => x_profile_id,
1538 x_return_status => x_return_status,
1539 x_msg_count => x_msg_count,
1540 x_msg_data => x_msg_data
1541 );
1542
1543 ELSE
1544
1545 HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Account
1546 (
1547 p_organization_rec => l_organization_rec,
1548 p_cust_account_rec => l_account_rec,
1549 p_customer_profile_rec => l_cust_profile_rec,
1550 x_party_id => x_party_id,
1551 x_party_number => x_party_number,
1552 x_cust_account_id => x_cust_account_id,
1553 x_account_number => x_cust_account_number,
1554 x_profile_id => x_profile_id,
1555 x_return_status => x_return_status,
1556 x_msg_count => x_msg_count,
1557 x_msg_data => x_msg_data
1558 );
1559
1560 END IF;
1561
1562
1563 END Create_Account;
1564
1565
1566
1567
1568 PROCEDURE Create_Party_relationship(
1569 p_object_party_id IN Number,
1570 p_subject_party_id IN Number,
1571 p_reciprocal_flag IN Varchar2,
1572 x_party_relationship_id OUT NOCOPY Number,
1573
1574 x_return_status OUT NOCOPY VARCHAR2,
1575
1576 x_msg_count OUT NOCOPY NUMBER,
1577
1578 x_msg_data OUT NOCOPY VARCHAR2
1579
1580 ) IS
1581
1582 x_party_id number;
1583 x_party_number hz_parties.party_number%TYPE;
1584
1585 --
1586 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1587 --
1588 BEGIN
1589
1590 -- commmented out as it is not used anywhere
1591 NULL;
1592
1593 END create_party_relationship;
1594
1595
1596
1597
1598 PROCEDURE Create_Cust_relationship(
1599 p_cust_acct_id IN Number,
1600 p_related_cust_acct_id IN Number,
1601 p_reciprocal_flag IN Varchar2,
1602 p_created_by_module IN VARCHAR2 DEFAULT NULL,
1603 x_return_status OUT NOCOPY VARCHAR2,
1604
1605 x_msg_count OUT NOCOPY NUMBER,
1606
1607 x_msg_data OUT NOCOPY VARCHAR2
1608
1609 ) IS
1610
1611 l_cust_rel_rec HZ_CUST_ACCOUNT_V2PUB.cust_acct_relate_rec_type;
1612
1613 --
1614 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1615 --
1616 BEGIN
1617
1618 l_cust_rel_rec.cust_account_id := p_cust_acct_id;
1619 l_cust_rel_rec.related_cust_account_id := p_related_cust_acct_id;
1620 l_cust_rel_rec.relationship_type := 'ALL';
1621 l_cust_rel_rec.customer_reciprocal_flag := p_reciprocal_flag;
1622 l_cust_rel_rec.created_by_module := NVL(p_created_by_module,G_CREATED_BY_MODULE);
1623
1624
1625
1626 HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Acct_Relate
1627 (
1628 p_cust_acct_relate_rec => l_cust_rel_rec,
1629 x_return_status => x_return_status,
1630 x_msg_count => x_msg_count,
1631 x_msg_data => x_msg_data
1632 );
1633
1634 oe_debug_pub.add('status='||x_return_status||
1635 ' msg_count='||x_msg_count||
1636 ' msg_data='||x_msg_data||
1637 ' cust_acct_related_id='||l_cust_rel_rec.related_cust_account_id||
1638 ' cust_acct_id='||l_cust_rel_rec.cust_account_id||
1639 ' ship_to_flag='||l_cust_rel_rec.ship_to_flag||
1640 ' bill_to_flag='||l_cust_rel_rec.bill_to_flag
1641 );
1642
1643
1644 END create_cust_relationship;
1645
1646
1647
1648 PROCEDURE Commit_Changes IS
1649
1650 --
1651 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1652 --
1653 BEGIN
1654
1655 commit;
1656
1657 END commit_changes;
1658
1659
1660
1661 PROCEDURE Rollback_Changes IS
1662
1663 --
1664 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1665 --
1666 BEGIN
1667
1668 Rollback;
1669
1670 END rollback_changes;
1671
1672
1673
1674 PROCEDURE Create_Person
1675 (
1676 p_first_name IN NUMBER,
1677 p_party_number IN OUT NOCOPY /* file.sql.39 change */ Varchar2,
1678 x_party_id OUT NOCOPY NUMBER,
1679
1680 x_profile_id OUT NOCOPY NUMBER,
1681
1682 x_return_status OUT NOCOPY VARCHAR2,
1683
1684 x_msg_count OUT NOCOPY NUMBER,
1685
1686 x_msg_data OUT NOCOPY VARCHAR2
1687
1688 ) IS
1689
1690 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
1691
1692 --
1693 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1694 --
1695 BEGIN
1696
1697 l_person_rec.person_first_name:=p_first_name;
1698 if p_party_number is not null then
1699 --l_person_rec.party_rec.party_number := p_party_number;
1700 null;
1701 end if;
1702
1703 HZ_PARTY_V2PUB.Create_Person(
1704 p_person_rec => l_person_rec,
1705 x_party_id => x_party_id,
1706 x_party_number => p_party_number,
1707 x_profile_id => x_profile_id,
1708 x_return_status => x_return_status,
1709 x_msg_count => x_msg_count,
1710 x_msg_data => x_msg_data
1711 );
1712
1713 END Create_Person;
1714
1715
1716 PROCEDURE Create_Organization
1717 (
1718 p_organization_name IN NUMBER,
1719 p_party_number IN OUT NOCOPY /* file.sql.39 change */ Varchar2,
1720 x_party_id OUT NOCOPY NUMBER,
1721
1722 x_profile_id OUT NOCOPY NUMBER,
1723
1724 x_return_status OUT NOCOPY VARCHAR2,
1725
1726 x_msg_count OUT NOCOPY NUMBER,
1727
1728 x_msg_data OUT NOCOPY VARCHAR2
1729
1730 ) IS
1731 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
1732
1733 --
1734 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1735 --
1736 BEGIN
1737
1738 l_organization_rec.organization_name:=p_organization_name;
1739 if p_party_number is not null then
1740 --l_organization_rec.party_rec.party_number := p_party_number;
1741 null;
1742 end if;
1743
1744 END Create_Organization;
1745
1746
1747
1748 PROCEDURE Create_role_resp
1749 (
1750 p_cust_acct_role_id IN NUMBER,
1751 p_usage_type IN VARCHAR2,
1752 x_return_status OUT NOCOPY VARCHAR2,
1753
1754 x_msg_count OUT NOCOPY NUMBER,
1755
1756 x_msg_data OUT NOCOPY VARCHAR2,
1757
1758 x_responsibility_id OUT NOCOPY NUMBER
1759
1760 ) IS
1761
1762 l_role_resp_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.role_responsibility_rec_type;
1763
1764 --
1765 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1766 --
1767 BEGIN
1768
1769 l_role_resp_rec.cust_account_role_id := p_cust_acct_role_id;
1770 l_role_resp_rec.responsibility_type := p_usage_type;
1771 l_role_resp_rec.primary_flag := 'Y';
1772 l_role_resp_rec.created_by_module := G_CREATED_BY_MODULE;
1773
1774 HZ_CUST_ACCOUNT_ROLE_V2PUB.Create_Role_Responsibility(
1775 p_role_responsibility_rec => l_role_resp_rec,
1776 x_return_status => x_return_status,
1777 x_msg_count => x_msg_count,
1778 x_msg_data => x_msg_data,
1779 x_responsibility_id => x_responsibility_id
1780 );
1781 END create_role_resp;
1782
1783
1784
1785 PROCEDURE add_customer_startup(
1786 out_auto_site_numbering out NOCOPY /* file.sql.39 change */ varchar2,
1787 out_auto_location_numbering out NOCOPY /* file.sql.39 change */ varchar2,
1788 out_auto_cust_numbering out NOCOPY /* file.sql.39 change */ varchar2,
1789 out_email_required out NOCOPY /* file.sql.39 change */ varchar2,
1790 out_auto_party_numbering out NOCOPY /* file.sql.39 change */ varchar2,
1791 out_default_country_code out NOCOPY /* file.sql.39 change */ varchar2,
1792 out_default_country out NOCOPY /* file.sql.39 change */ varchar2,
1793 out_address_style out NOCOPY /* file.sql.39 change */ varchar2
1794 ) IS
1795
1796 sysparm ar_system_parameters%rowtype;
1797
1798 BEGIN
1799
1800
1801 BEGIN
1802 select *
1803 into sysparm
1804 from ar_system_parameters;
1805
1806 EXCEPTION
1807 WHEN NO_DATA_FOUND THEN
1808 NULL;
1809 WHEN TOO_MANY_ROWS THEN
1810 NULL;
1811 WHEN OTHERS THEN
1812 NULL;
1813
1814 END;
1815
1816
1817 out_auto_cust_numbering :=sysparm.generate_customer_number;
1818 --g_home_country_code := sysparm.default_country;
1819 fnd_profile.get('DEFAULT_COUNTRY',out_default_country_code);
1820 fnd_profile.get('ONT_MANDATE_CUSTOMER_EMAIL',out_email_required);
1821
1822 IF out_default_country_code is not null THEN
1823 select territory_short_name,address_style
1824 into out_default_country,out_address_style
1825 from fnd_territories_vl
1826 where territory_code = out_default_country_code;
1827 END IF;
1828
1829 fnd_profile.get('HZ_GENERATE_PARTY_NUMBER',out_auto_party_numbering);
1830 fnd_profile.get('HZ_GENERATE_PARTY_SITE_NUMBER',out_auto_site_numbering);
1831
1832 if out_auto_site_numbering is null then
1833 out_auto_site_numbering :='Y';
1834 end if;
1835
1836 -- for location numbering we use the system option site_numbering
1837 out_auto_location_numbering := nvl(sysparm.auto_site_numbering,'N');
1838
1839
1840 END add_customer_startup;
1841
1842
1843 PROCEDURE Add_Customer(
1844 in_cust_account_id in number,
1845 in_cust_type varchar2,
1846 in_party_Number in varchar2,
1847 in_cust_name in varchar2,
1848 in_cust_first_name in varchar2,
1849 in_cust_middle_name in varchar2,
1850 in_cust_last_name in varchar2,
1851 in_cust_title in varchar2,
1852 in_Cust_Number in varchar2,
1853 in_cust_email in varchar2,
1854 in_cust_country_code in varchar2,
1855 in_cust_phone_number in varchar2,
1856 in_cust_phone_ext in varchar2,
1857 in_addr_location in varchar2,
1858 in_addr_country_Code in varchar2,
1859 in_addr_line1 in varchar2,
1860 in_addr_line2 in varchar2,
1861 in_addr_line3 in varchar2,
1862 in_addr_city in varchar2,
1863 in_addr_state in varchar2,
1864 in_addr_zip in varchar2,
1865 in_ship_usage in varchar2,
1866 in_bill_usage in varchar2,
1867 in_deliver_usage in varchar2,
1868 in_sold_usage in varchar2,
1869 in_cont_first_name in varchar2,
1870 in_cont_last_name in varchar2,
1871 in_cont_title in varchar2,
1872 in_cont_email in varchar2,
1873 in_cont_country_Code in varchar2,
1874 in_cont_phone_number in varchar2,
1875 in_cont_phone_ext in varchar2,
1876 out_cust_name out nocopy varchar2,
1877 out_cust_number out nocopy varchar2,
1878 out_cust_id out nocopy number,
1879 out_party_number out nocopy varchar2,
1880 out_ship_to_site_use_id out NOCOPY /* file.sql.39 change */ varchar2,
1881 out_bill_to_site_use_id out NOCOPY /* file.sql.39 change */ varchar2,
1882 out_deliver_to_site_use_id out NOCOPY /* file.sql.39 change */ varchar2,
1883 out_sold_to_site_use_id out NOCOPY /* file.sql.39 change */ varchar2,
1884 out_ship_to_location out nocopy varchar2,
1885 out_bill_to_location out nocopy varchar2,
1886 out_deliver_to_location out nocopy varchar2,
1887 out_sold_to_location out nocopy varchar2,
1888 out_cont_id out nocopy number,
1889 out_cont_name out nocopy varchar2,
1890 x_return_status out NOCOPY /* file.sql.39 change */ varchar2,
1891 x_msg_data out NOCOPY /* file.sql.39 change */ varchar2,
1892 x_msg_count out NOCOPY /* file.sql.39 change */ number,
1893 in_county in varchar2,
1894 in_party_site_number in varchar2
1895 ) IS
1896
1897 x_party_id number;
1898 x_party_number varchar2(100);
1899 l_cust_number varchar2(100);
1900 x_location_id number;
1901 x_party_site_id number;
1902 x_party_site_number varchar2(100);
1903 x_customer_site_id number;
1904 x_cust_account_id number;
1905 x_site_use_id number;
1906
1907
1908 BEGIN
1909
1910 oe_msg_pub.initialize;
1911 savepoint add_customer;
1912 l_cust_number := in_cust_number;
1913
1914 IF in_cust_account_id is null then
1915
1916 oe_oe_inline_address.create_account(
1917 p_party_number=>null,
1918 p_organization_name=>in_cust_name,
1919 p_alternate_name=>null,
1920 p_tax_reference=>NULL,
1921 p_taxpayer_id=>NULL,
1922 p_party_id=>null,
1923 p_first_name=>in_cust_first_name,
1924 p_last_name=>in_cust_last_name,
1925 p_middle_name=>in_cust_middle_name,
1926 p_name_suffix=>null,
1927 p_title=>in_cust_title,
1928 p_party_type=>in_cust_type,
1929 p_email=>in_cust_email,
1930 c_attribute_category=>null,
1931 c_attribute1=>null,
1932 c_attribute2=>null,
1933 c_attribute3=>null,
1934 c_attribute4=>null,
1935 c_attribute5=>null,
1936 c_attribute6=>null,
1937 c_attribute7=>null,
1938 c_attribute8=>null,
1939 c_attribute9=>null,
1940 c_attribute10=>null,
1941 c_attribute11=>null,
1942 c_attribute12=>null,
1943 c_attribute13=>null,
1944 c_attribute14=>null,
1945 c_attribute15=>null,
1946 c_attribute16=>null,
1947 c_attribute17=>null,
1948 c_attribute18=>null,
1949 c_attribute19=>null,
1950 c_attribute20=>null,
1951 c_global_attribute_category=>null,
1952 c_global_attribute1=>null,
1953 c_global_attribute2=>null,
1954 c_global_attribute3=>null,
1955 c_global_attribute4=>null,
1956 c_global_attribute5=>null,
1957 c_global_attribute6=>null,
1958 c_global_attribute7=>null,
1959 c_global_attribute8=>null,
1960 c_global_attribute9=>null,
1961 c_global_attribute10=>null,
1962 c_global_attribute11=>null,
1963 c_global_attribute12=>null,
1964 c_global_attribute13=>null,
1965 c_global_attribute14=>null,
1966 c_global_attribute15=>null,
1967 c_global_attribute16=>null,
1968 c_global_attribute17=>null,
1969 c_global_attribute18=>null,
1970 c_global_attribute19=>null,
1971 c_global_attribute20=>null,
1972 x_party_id=>x_party_id,
1973 x_party_number=>x_party_number,
1974 x_cust_Account_id=>x_cust_account_id,
1975 x_cust_account_number=>l_cust_number,
1976 x_return_status=>x_return_status,
1977 x_msg_count=>x_msg_count,
1978 x_msg_data=>x_msg_data
1979 );
1980
1981
1982 out_cust_Number := l_cust_number;
1983 out_party_number := x_party_number;
1984 out_cust_id := x_cust_account_id;
1985
1986
1987 if x_return_status in ('E','U') then
1988
1989 /* OE_MSG_PUB.Count_And_Get
1990 ( p_count => x_msg_count
1991 , p_data => x_msg_data
1992 );
1993 */
1994
1995 rollback;
1996 return;
1997
1998 end if;
1999
2000
2001 select party_name
2002 into out_cust_name
2003 from hz_parties
2004 where party_id = x_party_id;
2005
2006 else
2007 out_cust_id := in_cust_account_id;
2008 x_cust_account_id := in_cust_account_id;
2009
2010 select party.party_id,
2011 party.party_name,
2012 acct.account_number,
2013 party.party_number
2014
2015 into x_party_id,
2016 out_cust_name,
2017 out_cust_number,
2018 out_party_number
2019 from hz_cust_accounts acct,
2020 hz_parties party
2021 where acct.cust_account_id = in_cust_account_id
2022 and party.party_id = acct.party_id;
2023
2024
2025 end if; -- if in_cust_account_id is null
2026
2027 if in_cust_email is not null then
2028
2029
2030 create_contact_point(
2031 in_contact_point_type =>'EMAIL',
2032 in_owner_table_id=>x_party_id,
2033 in_email=>in_cust_email,
2034 in_phone_area_code =>NULL,
2035 in_phone_number=>NULL,
2036 in_phone_extension=>NULL,
2037 x_return_status=>x_return_status,
2038 x_msg_count=>x_msg_count,
2039 x_msg_data=>x_msg_data
2040 );
2041 if x_return_status in ('E','U') then
2042
2043 rollback;
2044 return;
2045 end if;
2046
2047 end if;
2048
2049
2050 if in_cust_phone_number is not null then
2051
2052 create_contact_point(
2053 in_contact_point_type =>'PHONE',
2054 in_owner_table_id=>x_party_id,
2055 in_email=>NULL,
2056 in_phone_area_code =>null,
2057 in_phone_number=>in_cust_phone_number,
2058 in_phone_extension=>in_cust_phone_ext,
2059 in_phone_country_code=>in_cust_country_code,
2060 x_return_status=>x_return_status,
2061 x_msg_count=>x_msg_count,
2062 x_msg_data=>x_msg_data,
2063 c_attribute_category=>null,
2064 c_attribute1=>null,
2065 c_attribute2=>null,
2066 c_attribute3=>null,
2067 c_attribute4=>null,
2068 c_attribute5=>null,
2069 c_attribute6=>null,
2070 c_attribute7=>null,
2071 c_attribute8=>null,
2072 c_attribute9=>null,
2073 c_attribute10=>null,
2074 c_attribute11=>null,
2075 c_attribute12=>null,
2076 c_attribute13=>null,
2077 c_attribute14=>null,
2078 c_attribute15=>null,
2079 c_attribute16=>null,
2080 c_attribute17=>null,
2081 c_attribute18=>null,
2082 c_attribute19=>null,
2083 c_attribute20=>null
2084 );
2085
2086 if x_return_status in ('E','U') then
2087 rollback;
2088 return;
2089 end if;
2090
2091 end if;
2092
2093 IF in_addr_line1 is not null then
2094
2095 oe_oe_inline_address.Create_Location
2096 (
2097 p_country => in_addr_country_Code,
2098 p_address1=> in_addr_line1,
2099 p_address2 =>in_addr_line2,
2100 p_address3 =>in_addr_line3,
2101 p_address4 =>null,
2102 p_city =>in_addr_city,
2103 p_postal_code =>in_addr_zip,
2104 p_state =>in_addr_state,
2105 p_province =>null,
2106 p_county => in_county,
2107 p_address_style =>null,
2108 p_address_line_phonetic => Null,
2109 c_attribute_category=>null,
2110 c_attribute1=>null,
2111 c_attribute2=>null,
2112 c_attribute3=>null,
2113 c_attribute4=>null,
2114 c_attribute5=>null,
2115 c_attribute6=>null,
2116 c_attribute7=>null,
2117 c_attribute8=>null,
2118 c_attribute9=>null,
2119 c_attribute10=>null,
2120 c_attribute11=>null,
2121 c_attribute12=>null,
2122 c_attribute13=>null,
2123 c_attribute14=>null,
2124 c_attribute15=>null,
2125 c_attribute16=>null,
2126 c_attribute17=>null,
2127 c_attribute18=>null,
2128 c_attribute19=>null,
2129 c_attribute20=>null,
2130 c_global_attribute_category=>null,
2131 c_global_attribute1=>null,
2132 c_global_attribute2=>null,
2133 c_global_attribute3=>null,
2134 c_global_attribute4=>null,
2135 c_global_attribute5=>null,
2136 c_global_attribute6=>null,
2137 c_global_attribute7=>null,
2138 c_global_attribute8=>null,
2139 c_global_attribute9=>null,
2140 c_global_attribute10=>null,
2141 c_global_attribute11=>null,
2142 c_global_attribute12=>null,
2143 c_global_attribute13=>null,
2144 c_global_attribute14=>null,
2145 c_global_attribute15=>null,
2146 c_global_attribute16=>null,
2147 c_global_attribute17=>null,
2148 c_global_attribute18=>null,
2149 c_global_attribute19=>null,
2150 c_global_attribute20=>null,
2151 x_location_id => x_location_id,
2152 x_return_status=>x_return_status ,
2153 x_msg_count=>x_msg_count,
2154 x_msg_data=> x_msg_data
2155 ) ;
2156
2157
2158
2159 if x_return_status in ('E','U') then
2160 rollback;
2161 return;
2162 end if;
2163
2164
2165 --x_location_id := 1048;
2166
2167 oe_oe_inline_address.Create_Party_Site
2168 (
2169 p_party_id => x_party_id,
2170 p_location_id=>x_location_id ,
2171 p_party_site_number =>in_party_site_number,
2172 x_party_site_id =>x_party_site_id ,
2173 x_party_site_number =>x_party_site_number,
2174 x_return_status => x_return_status,
2175 x_msg_count => x_msg_count,
2176 x_msg_data => x_msg_data
2177 ) ;
2178 if x_return_status in ('E','U') then
2179 rollback;
2180 return;
2181 end if;
2182
2183 oe_oe_inline_address.Create_Account_Site
2184 (
2185 p_cust_account_id =>x_cust_account_id,
2186 p_party_site_id =>x_party_site_id,
2187 c_attribute_category=>null,
2188 c_attribute1=>null,
2189 c_attribute2=>null,
2190 c_attribute3=>null,
2191 c_attribute4=>null,
2192 c_attribute5=>null,
2193 c_attribute6=>null,
2194 c_attribute7=>null,
2195 c_attribute8=>null,
2196 c_attribute9=>null,
2197 c_attribute10=>null,
2198 c_attribute11=>null,
2199 c_attribute12=>null,
2200 c_attribute13=>null,
2201 c_attribute14=>null,
2202 c_attribute15=>null,
2203 c_attribute16=>null,
2204 c_attribute17=>null,
2205 c_attribute18=>null,
2206 c_attribute19=>null,
2207 c_attribute20=>null,
2208 x_customer_site_id =>x_customer_site_id ,
2209 x_return_status => x_return_status,
2210 x_msg_count => x_msg_count,
2211 x_msg_data => x_msg_data
2212 ) ;
2213
2214 if x_return_status in ('E','U') then
2215 rollback;
2216 return;
2217 end if;
2218
2219 -- if SHIPTO needs to be created
2220 if in_ship_usage = 'Y' then
2221
2222 oe_oe_inline_address.Create_Acct_Site_Uses
2223 (
2224 p_cust_acct_site_id =>x_customer_site_id,
2225 p_location =>in_addr_location,
2226 p_site_use_code =>'SHIP_TO',
2227 x_site_use_id =>x_site_use_id ,
2228 x_return_status => x_return_status,
2229 x_msg_count => x_msg_count,
2230 x_msg_data => x_msg_data,
2231 c_attribute_category=>null,
2232 c_attribute1=>null,
2233 c_attribute2=>null,
2234 c_attribute3=>null,
2235 c_attribute4=>null,
2236 c_attribute5=>null,
2237 c_attribute6=>null,
2238 c_attribute7=>null,
2239 c_attribute8=>null,
2240 c_attribute9=>null,
2241 c_attribute10=>null,
2242 c_attribute11=>null,
2243 c_attribute12=>null,
2244 c_attribute13=>null,
2245 c_attribute14=>null,
2246 c_attribute15=>null,
2247 c_attribute16=>null,
2248 c_attribute17=>null,
2249 c_attribute18=>null,
2250 c_attribute19=>null,
2251 c_attribute20=>null,
2252 c_attribute21=>null,
2253 c_attribute22=>null,
2254 c_attribute23=>null,
2255 c_attribute24=>null,
2256 c_attribute25=>null
2257 );
2258
2259
2260 if x_return_status in ('E','U') then
2261 return;
2262 end if;
2263
2264
2265 out_ship_to_site_use_id := x_site_use_id;
2266
2267 -- if location is system generated then we fetch the location number
2268 -- after creation
2269 if in_addr_location is null then
2270 select location
2271 into out_ship_to_location
2272 from hz_cust_site_uses_all
2273 where site_use_id = x_site_use_id;
2274 else
2275 out_ship_to_location := in_addr_location;
2276 end if;
2277
2278 end if; -- if shipto needs to be created
2279
2280
2281 -- if BILLTO needs to be created
2282 if in_bill_usage = 'Y' then
2283
2284 oe_oe_inline_address.Create_Acct_Site_Uses
2285 (
2286 p_cust_acct_site_id =>x_customer_site_id,
2287 p_location =>in_addr_location,
2288 p_site_use_code =>'BILL_TO',
2289 x_site_use_id =>x_site_use_id ,
2290 x_return_status => x_return_status,
2291 x_msg_count => x_msg_count,
2292 x_msg_data => x_msg_data,
2293 c_attribute_category=>null,
2294 c_attribute1=>null,
2295 c_attribute2=>null,
2296 c_attribute3=>null,
2297 c_attribute4=>null,
2298 c_attribute5=>null,
2299 c_attribute6=>null,
2300 c_attribute7=>null,
2301 c_attribute8=>null,
2302 c_attribute9=>null,
2303 c_attribute10=>null,
2304 c_attribute11=>null,
2305 c_attribute12=>null,
2306 c_attribute13=>null,
2307 c_attribute14=>null,
2308 c_attribute15=>null,
2309 c_attribute16=>null,
2310 c_attribute17=>null,
2311 c_attribute18=>null,
2312 c_attribute19=>null,
2313 c_attribute20=>null,
2314 c_attribute21=>null,
2315 c_attribute22=>null,
2316 c_attribute23=>null,
2317 c_attribute24=>null,
2318 c_attribute25=>null
2319 );
2320
2321
2322 if x_return_status in ('E','U') then
2323 return;
2324 end if;
2325
2326 out_bill_to_site_use_id := x_site_use_id;
2327
2328 -- if location is system generated then we fetch the location number
2329 -- after creation
2330
2331 if in_addr_location is null then
2332 select location
2333 into out_bill_to_location
2334 from hz_cust_site_uses_all
2335 where site_use_id = x_site_use_id;
2336 else
2337 out_bill_to_location := in_addr_location;
2338 end if;
2339 end if; -- if billto needs to be created
2340
2341
2342 -- if DELIVERTO needs to be created
2343 if in_deliver_usage = 'Y' then
2344
2345
2346 oe_oe_inline_address.Create_Acct_Site_Uses
2347 (
2348 p_cust_acct_site_id =>x_customer_site_id,
2349 p_location =>in_addr_location,
2350 p_site_use_code =>'DELIVER_TO',
2351 x_site_use_id =>x_site_use_id ,
2352 x_return_status => x_return_status,
2353 x_msg_count => x_msg_count,
2354 x_msg_data => x_msg_data,
2355 c_attribute_category=>null,
2356 c_attribute1=>null,
2357 c_attribute2=>null,
2358 c_attribute3=>null,
2359 c_attribute4=>null,
2360 c_attribute5=>null,
2361 c_attribute6=>null,
2362 c_attribute7=>null,
2363 c_attribute8=>null,
2364 c_attribute9=>null,
2365 c_attribute10=>null,
2366 c_attribute11=>null,
2367 c_attribute12=>null,
2368 c_attribute13=>null,
2369 c_attribute14=>null,
2370 c_attribute15=>null,
2371 c_attribute16=>null,
2372 c_attribute17=>null,
2373 c_attribute18=>null,
2374 c_attribute19=>null,
2375 c_attribute20=>null,
2376 c_attribute21=>null,
2377 c_attribute22=>null,
2378 c_attribute23=>null,
2379 c_attribute24=>null,
2380 c_attribute25=>null
2381 );
2382
2383
2384
2385 if x_return_status in ('E','U') then
2386 return;
2387 end if;
2388
2389 out_deliver_to_site_use_id := x_site_use_id;
2390
2391 -- if location is system generated then we fetch the location number
2392 -- after creation
2393 if in_addr_location is null then
2394 select location
2395 into out_deliver_to_location
2396 from hz_cust_site_uses_all
2397 where site_use_id = x_site_use_id;
2398
2399 else
2400 out_deliver_to_location := in_addr_location;
2401 end if;
2402 end if; -- if deliver_to needs to be created
2403
2404
2405 -- if SOLDTO needs to be created
2406 if in_sold_usage = 'Y' then
2407
2408 oe_oe_inline_address.Create_Acct_Site_Uses
2409 (
2410 p_cust_acct_site_id =>x_customer_site_id,
2411 p_location =>in_addr_location,
2412 p_site_use_code =>'SOLD_TO',
2413 x_site_use_id =>x_site_use_id ,
2414 x_return_status => x_return_status,
2415 x_msg_count => x_msg_count,
2416 x_msg_data => x_msg_data,
2417 c_attribute_category=>null,
2418 c_attribute1=>null,
2419 c_attribute2=>null,
2420 c_attribute3=>null,
2421 c_attribute4=>null,
2422 c_attribute5=>null,
2423 c_attribute6=>null,
2424 c_attribute7=>null,
2425 c_attribute8=>null,
2426 c_attribute9=>null,
2427 c_attribute10=>null,
2428 c_attribute11=>null,
2429 c_attribute12=>null,
2430 c_attribute13=>null,
2431 c_attribute14=>null,
2432 c_attribute15=>null,
2433 c_attribute16=>null,
2434 c_attribute17=>null,
2435 c_attribute18=>null,
2436 c_attribute19=>null,
2437 c_attribute20=>null,
2438 c_attribute21=>null,
2439 c_attribute22=>null,
2440 c_attribute23=>null,
2441 c_attribute24=>null,
2442 c_attribute25=>null
2443 );
2444
2445
2446 if x_return_status in ('E','U') then
2447 return;
2448 end if;
2449
2450 out_sold_to_site_use_id := x_site_use_id;
2451
2452
2453 -- if location is system generated then we fetch the location number
2454 -- after creation
2455 if in_addr_location is null then
2456 select location
2457 into out_sold_to_location
2458 from hz_cust_site_uses_all
2459 where site_use_id = x_site_use_id;
2460 else
2461 out_sold_to_location := in_addr_location;
2462 end if;
2463 end if; -- if soldto location needs to be created
2464
2465
2466
2467
2468 end if; -- if address1 is not null
2469
2470
2471 IF in_cont_last_name is not null then
2472
2473 oe_oe_inline_address.Create_contact
2474 (p_contact_last_name =>in_cont_last_name,
2475 p_contact_first_name =>in_cont_first_name,
2476 p_contact_title =>in_cont_title,
2477 p_email =>in_cont_email,
2478 p_area_code =>null,
2479 p_phone_number =>in_cont_phone_number,
2480 p_extension =>in_cont_phone_ext,
2481 p_acct_id =>x_cust_account_id,
2482 p_party_id =>x_party_id,
2483 x_return_status =>x_return_status,
2484 x_msg_count =>x_msg_count,
2485 x_msg_data =>x_msg_data,
2486 x_contact_id =>out_cont_id,
2487 x_contact_name =>out_cont_name,
2488 c_attribute_category=>null,
2489 c_attribute1=>null,
2490 c_attribute2=>null,
2491 c_attribute3=>null,
2492 c_attribute4=>null,
2493 c_attribute5=>null,
2494 c_attribute6=>null,
2495 c_attribute7=>null,
2496 c_attribute8=>null,
2497 c_attribute9=>null,
2498 c_attribute10=>null,
2499 c_attribute11=>null,
2500 c_attribute12=>null,
2501 c_attribute13=>null,
2502 c_attribute14=>null,
2503 c_attribute15=>null,
2504 c_attribute16=>null,
2505 c_attribute17=>null,
2506 c_attribute18=>null,
2507 c_attribute19=>null,
2508 c_attribute20=>null,
2509 c_attribute21=>null,
2510 c_attribute22=>null,
2511 c_attribute23=>null,
2512 c_attribute24=>null,
2513 c_attribute25=>null,
2514 c2_attribute_category=>null,
2515 c2_attribute1=>null,
2516 c2_attribute2=>null,
2517 c2_attribute3=>null,
2518 c2_attribute4=>null,
2519 c2_attribute5=>null,
2520 c2_attribute6=>null,
2521 c2_attribute7=>null,
2522 c2_attribute8=>null,
2523 c2_attribute9=>null,
2524 c2_attribute10=>null,
2525 c2_attribute11=>null,
2526 c2_attribute12=>null,
2527 c2_attribute13=>null,
2528 c2_attribute14=>null,
2529 c2_attribute15=>null,
2530 c2_attribute16=>null,
2531 c2_attribute17=>null,
2532 c2_attribute18=>null,
2533 c2_attribute19=>null,
2534 c2_attribute20=>null,
2535 in_phone_country_code=>in_cont_country_Code
2536 );
2537
2538
2539 if x_return_status in ('E','U') then
2540 return;
2541 end if;
2542
2543 -- this is done in order to match with the concatanation style of the
2544 -- view oe_contacts_v which is used in the sales order form
2545
2546 select in_cont_last_name||
2547 DECODE(in_cont_first_name,NULL,NULL,', '||in_cont_first_name)||
2548 DECODE(in_cont_title,NULL,NULL,' '||in_cont_title)
2549 into out_cont_Name
2550 from dual;
2551
2552 END IF; -- if contact information is passed
2553
2554 END Add_Customer;
2555
2556
2557 END oe_oe_inline_address;