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