[Home] [Help]
PACKAGE BODY: APPS.OE_CUSTOMER_INFO_PVT
Source
1 PACKAGE BODY OE_CUSTOMER_INFO_PVT AS
2 /* $Header: OEXVCUSB.pls 120.1 2009/02/27 08:30:38 smanian noship $ */
3
4 G_PKG_NAME VARCHAR2(100) := 'OE_CUSTOMER_INFO_PVT';
5 G_CREATED_BY_MODULE VARCHAR2(100) := 'ONT_OI_ADD_CUSTOMER';
6 G_EMAIL_REQUIRED VARCHAR2(1);
7 G_AUTO_PARTY_NUMBERING VARCHAR2(1);
8 G_AUTO_CUST_NUMBERING VARCHAR2(1);
9 G_AUTO_CONTACT_NUMBERING VARCHAR2(1);
10 G_AUTO_LOCATION_NUMBERING VARCHAR2(1);
11 G_AUTO_SITE_NUMBERING VARCHAR2(1);
12 G_ONT_ADD_CUSTOMER VARCHAR2(1);
13
14
15 /* This procedure reads important profile options and system parameter values and store them in global variables */
16 PROCEDURE Initialize_Global( x_return_status OUT NOCOPY Varchar2);
17
18
19 PROCEDURE Initialize_Global( x_return_status OUT NOCOPY Varchar2)
20 IS
21 l_sys_parm_rec ar_system_parameters_all%rowtype;
22 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
23 Begin
24
25 x_return_status := FND_API.G_RET_STS_SUCCESS;
26
27 IF l_debug_level > 0 THEN
28 oe_debug_pub.add( 'ENTERING PROCEDURE INITIALIZE_GLOBAL' ) ;
29 END IF;
30
31 l_Sys_Parm_Rec := OE_Sys_Parameters_Pvt.Get_AR_Sys_Params;
32
33 fnd_profile.get('HZ_GENERATE_PARTY_NUMBER',G_AUTO_PARTY_NUMBERING);
34 fnd_profile.get('HZ_GENERATE_PARTY_SITE_NUMBER',G_AUTO_SITE_NUMBERING);
35 fnd_profile.get('ONT_MANDATE_CUSTOMER_EMAIL',G_EMAIL_REQUIRED);
36 fnd_profile.get('HZ_GENERATE_CONTACT_NUMBER',G_AUTO_CONTACT_NUMBERING);
37 fnd_profile.get('ONT_ADD_CUSTOMER_OI',G_ONT_ADD_CUSTOMER);
38
39 G_AUTO_PARTY_NUMBERING := NVL(G_AUTO_PARTY_NUMBERING,'Y');
40 G_AUTO_CUST_NUMBERING := NVL(l_sys_parm_rec.GENERATE_CUSTOMER_NUMBER,'Y');
41 G_AUTO_LOCATION_NUMBERING := NVL(l_sys_parm_rec.AUTO_SITE_NUMBERING,'Y');
42 G_AUTO_SITE_NUMBERING := NVL(G_AUTO_SITE_NUMBERING, 'Y');
43 G_EMAIL_REQUIRED := NVL(G_EMAIL_REQUIRED,'Y');
44 G_AUTO_CONTACT_NUMBERING := NVL(G_AUTO_CONTACT_NUMBERING,'Y');
45 G_ONT_ADD_CUSTOMER := NVL(G_ONT_ADD_CUSTOMER,'Y');
46
47
48
49 IF l_debug_level > 0 THEN
50 oe_debug_pub.add('G_AUTO_PARTY_NUMBERING :'||G_AUTO_PARTY_NUMBERING);
51 oe_debug_pub.add('G_AUTO_CUST_NUMBERING :'||G_AUTO_CUST_NUMBERING);
52 oe_debug_pub.add('G_AUTO_LOCATION_NUMBERING :'||G_AUTO_LOCATION_NUMBERING);
53 oe_debug_pub.add('G_AUTO_SITE_NUMBERING :'||G_AUTO_SITE_NUMBERING);
54 oe_debug_pub.add('G_EMAIL_REQUIRED :'||G_EMAIL_REQUIRED);
55 oe_debug_pub.add('G_AUTO_CONTACT_NUMBERING :'||G_AUTO_CONTACT_NUMBERING);
56 oe_debug_pub.add('G_ONT_ADD_CUSTOMER :'||G_ONT_ADD_CUSTOMER);
57
58 IF OE_GLOBALS.G_UPDATE_ON_ID THEN
59 oe_debug_pub.add('OE_GLOBALS.G_UPDATE_ON_ID : TRUE');
60 ELSE
61 oe_debug_pub.add('OE_GLOBALS.G_UPDATE_ON_ID : FALSE');
62 END IF;
63
64 oe_debug_pub.add( 'EXITING PROCEDURE INITIALIZE_GLOBAL' ) ;
65
66 END IF;
67
68 Exception
69 WHEN OTHERS THEN
70
71 IF l_debug_level > 0 THEN
72 oe_debug_pub.add( 'PROBLEM IN CALL TO INITIALIZE_GLOBAL. ABORT PROCESSING'||SQLERRM ) ;
73 END IF;
74 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
75
76 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
77 THEN
78 OE_MSG_PUB.Add_Exc_Msg
79 ( G_PKG_NAME
80 , 'Initialize_Global'
81 );
82 END IF;
83
84 End Initialize_Global;
85
86
87 /* This procedure will be called from OE_HEADER_UTIL and OE_LINE_UTIL packages
88 to derive/create customer/address/contact related information
89 */
90
91 Procedure get_customer_info_ids
92 (
93 p_customer_info_tbl IN OUT NOCOPY OE_ORDER_PUB.CUSTOMER_INFO_TABLE_TYPE,
94 p_operation_code IN VARCHAR2,
95 p_sold_to_customer_ref IN VARCHAR2,
96 p_ship_to_customer_ref IN VARCHAR2,
97 p_bill_to_customer_ref IN VARCHAR2,
98 p_deliver_to_customer_ref IN VARCHAR2,
99
100 p_ship_to_address_ref IN VARCHAR2,
101 p_bill_to_address_ref IN VARCHAR2,
102 p_deliver_to_address_ref IN VARCHAR2,
103 p_sold_to_address_ref IN VARCHAR2,
104
105 p_sold_to_contact_ref IN VARCHAR2,
106 p_ship_to_contact_ref IN VARCHAR2,
107 p_bill_to_contact_ref IN VARCHAR2,
108 p_deliver_to_contact_ref IN VARCHAR2,
109
110 p_sold_to_customer_id IN NUMBER,
111 p_ship_to_customer_id IN NUMBER,
112 p_bill_to_customer_id IN NUMBER,
113 p_deliver_to_customer_id IN NUMBER,
114
115 p_ship_to_org_id IN NUMBER,
116 p_invoice_to_org_id IN NUMBER,
117 p_deliver_to_org_id IN NUMBER,
118 p_sold_to_site_use_id IN NUMBER,
119
120 p_sold_to_contact_id IN NUMBER,
121 p_ship_to_contact_id IN NUMBER,
122 p_invoice_to_contact_id IN NUMBER,
123 p_deliver_to_contact_id IN NUMBER,
124
125
126 x_sold_to_customer_id OUT NOCOPY NUMBER,
127 x_ship_to_customer_id OUT NOCOPY NUMBER,
128 x_bill_to_customer_id OUT NOCOPY NUMBER,
129 x_deliver_to_customer_id OUT NOCOPY NUMBER,
130
131 x_ship_to_org_id OUT NOCOPY NUMBER,
132 x_invoice_to_org_id OUT NOCOPY NUMBER,
133 x_deliver_to_org_id OUT NOCOPY NUMBER,
134 x_sold_to_site_use_id OUT NOCOPY NUMBER,
135
136 x_sold_to_contact_id OUT NOCOPY NUMBER,
137 x_ship_to_contact_id OUT NOCOPY NUMBER,
138 x_invoice_to_contact_id OUT NOCOPY NUMBER,
139 x_deliver_to_contact_id OUT NOCOPY NUMBER,
140
141
142 x_return_status OUT NOCOPY VARCHAR2,
143 x_msg_count OUT NOCOPY NUMBER,
144 x_msg_data OUT NOCOPY VARCHAR2
145 ) IS
146 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
147
148
149 l_sold_to_cust_found boolean := FALSE;
150 l_ship_to_cust_found boolean := FALSE;
151 l_bill_to_cust_found boolean := FALSE;
152 l_deliver_to_cust_found boolean := FALSE;
153
154 l_ship_addr_found boolean := FALSE;
155 l_bill_addr_found boolean := FALSE;
156 l_deliver_addr_found boolean := FALSE;
157 l_sold_addr_found boolean := FALSE;
158
159 l_sold_cont_found boolean := FALSE;
160 l_ship_cont_found boolean := FALSE;
161 l_bill_cont_found boolean := FALSE;
162 l_deliver_cont_found boolean := FALSE;
163
164
165 l_sold_to_customer_rec OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE;
166 l_ship_to_customer_rec OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE;
167 l_bill_to_customer_rec OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE;
168 l_deliver_to_customer_rec OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE;
169
170 l_sold_to_cust_index NUMBER;
171 l_ship_to_cust_index NUMBER;
172 l_bill_to_cust_index NUMBER;
173 l_deliver_to_cust_index NUMBER;
174
175 l_ship_addr_rec_index NUMBER;
176 l_bill_addr_rec_index NUMBER;
177 l_deliver_addr_rec_index NUMBER;
178 l_sold_addr_rec_index NUMBER;
179
180 l_sold_cont_rec_index NUMBER;
181 l_ship_cont_rec_index NUMBER;
182 l_bill_cont_rec_index NUMBER;
183 l_deliver_cont_rec_index NUMBER;
184
185
186 l_new_sold_to_org_id NUMBER;
187 l_new_ship_to_customer_id NUMBER;
188 l_new_bill_to_customer_id NUMBER;
189 l_new_deliver_to_cust_id NUMBER;
190
191 l_new_sold_to_party_id NUMBER;
192 l_new_ship_to_party_id NUMBER;
193 l_new_bill_to_party_id NUMBER;
194 l_new_deliver_to_party_id NUMBER;
195
196 l_customer_id NUMBER;
197 BEGIN
198
199 IF l_debug_level > 0 THEN
200 oe_debug_pub.add(' Entering OE_CUSTOMER_INFO_PVT.get_customer_info_ids'||p_customer_info_tbl.count);
201 END IF;
202
203
204 Initialize_Global(x_return_status);
205
206 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 RETURN;
209 END IF;
210
211
212 x_return_status := FND_API.G_RET_STS_SUCCESS;
213
214
215 x_sold_to_customer_id := p_sold_to_customer_id;
216 x_ship_to_customer_id := p_ship_to_customer_id;
217 x_bill_to_customer_id := p_bill_to_customer_id;
218 x_deliver_to_customer_id := p_deliver_to_customer_id;
219
220 x_ship_to_org_id := p_ship_to_org_id;
221 x_invoice_to_org_id := p_invoice_to_org_id;
222 x_deliver_to_org_id := p_deliver_to_org_id;
223
224 x_sold_to_contact_id := p_sold_to_contact_id;
225 x_ship_to_contact_id := p_ship_to_contact_id;
226 x_invoice_to_contact_id := p_invoice_to_contact_id;
227 x_deliver_to_contact_id := p_deliver_to_contact_id;
228
229
230 IF p_sold_to_customer_ref IS NOT NULL THEN
231
232 FOR i in 1..p_customer_info_tbl.last LOOP
233 IF p_customer_info_tbl.exists(i) THEN
234 IF p_customer_info_tbl(i).customer_info_type_code = 'CUSTOMER' THEN
235 IF p_customer_info_tbl(i).customer_info_ref = p_sold_to_customer_ref THEN
236
237 IF l_sold_to_cust_found = FALSE THEN
238 l_sold_to_cust_found := TRUE;
239 l_sold_to_cust_index := i;
240 l_sold_to_customer_rec := p_customer_info_tbl(i);
241 END IF;
242 END IF;
243 END IF;
244 END IF;
245 END LOOP;
246
247 END IF;
248
249
250 IF p_ship_to_customer_ref IS NOT NULL THEN
251
252 FOR i in 1..p_customer_info_tbl.last LOOP
253 IF p_customer_info_tbl.exists(i) THEN
254 IF p_customer_info_tbl(i).customer_info_type_code = 'CUSTOMER' THEN
255 IF p_customer_info_tbl(i).customer_info_ref = p_ship_to_customer_ref THEN
256 IF l_ship_to_cust_found = FALSE THEN
257 l_ship_to_cust_found := TRUE;
258 l_ship_to_cust_index := i;
259 l_ship_to_customer_rec := p_customer_info_tbl(i);
260 END IF;
261 END IF;
262 END IF;
263 END IF;
264 END LOOP;
265
266 END IF;
267
268 IF p_bill_to_customer_ref IS NOT NULL THEN
269
270 FOR i in 1..p_customer_info_tbl.last LOOP
271 IF p_customer_info_tbl.exists(i) THEN
272 IF p_customer_info_tbl(i).customer_info_type_code = 'CUSTOMER' THEN
273 IF p_customer_info_tbl(i).customer_info_ref = p_bill_to_customer_ref THEN
274 IF l_bill_to_cust_found = FALSE THEN
275 l_bill_to_cust_found := TRUE;
276 l_bill_to_cust_index := i;
277 l_bill_to_customer_rec := p_customer_info_tbl(i);
278 END IF;
279 END IF;
280 END IF;
281 END IF;
282 END LOOP;
283
284 END IF;
285
286 IF p_deliver_to_customer_ref IS NOT NULL THEN
287
288 FOR i in 1..p_customer_info_tbl.last LOOP
289 IF p_customer_info_tbl.exists(i) THEN
290 IF p_customer_info_tbl(i).customer_info_type_code = 'CUSTOMER' THEN
291 IF p_customer_info_tbl(i).customer_info_ref = p_deliver_to_customer_ref THEN
292 IF l_deliver_to_cust_found = FALSE THEN
293 l_deliver_to_cust_found := TRUE;
294 l_deliver_to_cust_index := i;
295 l_deliver_to_customer_rec := p_customer_info_tbl(i);
296 END IF;
297 END IF;
298 END IF;
299 END IF;
300 END LOOP;
301
302 END IF;
303
304 IF l_debug_level > 0 THEN
305
306 IF l_sold_to_cust_found THEN
307 oe_debug_pub.add('Sold to customer record passed :'||l_sold_to_cust_index);
308 END IF;
309
310 IF l_ship_to_cust_found THEN
311 oe_debug_pub.add('Ship to customer record passed :'||l_ship_to_cust_index);
312 END IF;
313
314 IF l_bill_to_cust_found THEN
315 oe_debug_pub.add('Bill to customer record passed :'||l_bill_to_cust_index);
316 END IF;
317
318 IF l_deliver_to_cust_found THEN
319 oe_debug_pub.add('Sold to customer record passed :'||l_deliver_to_cust_index);
320 END IF;
321
322 oe_debug_pub.add('Check for Permissions :'||G_ONT_ADD_CUSTOMER);
323 END IF;
324
325
326 IF G_ONT_ADD_CUSTOMER = 'N' THEN /* Nothing allowed */
327
328 fnd_message.set_name('ONT','ONT_OI_INL_SET_PARAMETER');
329 fnd_message.set_token('TYPE', 'Customers, Addresses or Contacts');
330 oe_msg_pub.add;
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 RETURN;
333
334 ELSIF G_ONT_ADD_CUSTOMER = 'P' THEN /* Address and contact can be added to an exisiting customer */
335
336 GOTO ADDRESS_CREATION;
337
338 ELSE /* Evering allowed */
339
340 IF l_debug_level > 0 THEN
341 oe_debug_pub.add('No restriction to create customer/address and contact.Continue...');
342 END IF;
343 END IF;
344
345 IF l_debug_level > 0 THEN
346 oe_debug_pub.add('Start Creating customers (SOLD_TO/SHIP_TO/BILL_TO/DELIVER_TO) if records are passed');
347 END IF;
348
349 /*
350 1.Check whether customer_id is sent
351 2.If customer_id is not sent check whether value is sent and try to derive id from value
352 3.If customer_id is sent then see if value is aslo sent and try to update the value on the id ig OE_GLOBALS.G_UPDATE_ON_ID is true
353 4.If customer_id could not be resolved till step 3,create the customer
354 5. Check for minimum required fields
355 6.Validate customer related fields
356 7. call TCA API to create the customer
357 */
358
359 IF l_sold_to_cust_found
360 THEN
361
362 IF l_debug_level > 0 THEN
363 oe_debug_pub.add('l_sold_to_customer_rec.customer_id :'||l_sold_to_customer_rec.customer_id);
364 END IF;
365
366 IF NVL(l_sold_to_customer_rec.customer_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
367
368 Check_Duplicate_Customer( p_customer_record => l_sold_to_customer_rec
369 , p_type => 'SOLD_TO'
370 , x_customer_id => l_new_sold_to_org_id
371 );
372
373 IF l_new_sold_to_org_id = FND_API.G_MISS_NUM THEN
374 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
375 ELSE
376 x_sold_to_customer_id := l_new_sold_to_org_id;
377 p_customer_info_tbl(l_sold_to_cust_index).new_account_id := l_new_sold_to_org_id;
378 END IF;
379
380 IF l_debug_level > 0 THEN
381 oe_debug_pub.add('l_new_sold_to_org_id:'||l_new_sold_to_org_id);
382 END IF;
383
384 ELSE
385 IF IS_BOTH_ID_VAL_PASSED( p_customer_rec => l_sold_to_customer_rec )
386 AND OE_GLOBALS.G_UPDATE_ON_ID
387 THEN
388
389 Update_Customer (p_customer_rec => l_sold_to_customer_rec
390 , x_return_status =>x_return_status
391 );
392 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
393
394 IF l_debug_level > 0 THEN
395 oe_debug_pub.add ('Updating Customer information failed,returning');
396 END IF;
397
398 RETURN;
399 END IF;
400 END IF;
401
402 l_new_sold_to_org_id := l_sold_to_customer_rec.customer_id;
403 x_sold_to_customer_id := l_sold_to_customer_rec.customer_id;
404 p_customer_info_tbl(l_sold_to_cust_index).new_account_id := l_sold_to_customer_rec.customer_id;
405
406 END IF;
407
408
409 IF NVL(l_new_sold_to_org_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
410
411 Check_Customer_Fields(l_sold_to_customer_rec,x_return_status);
412
413 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
414 IF l_debug_level > 0 THEN
415 oe_debug_pub.add ('Check_Customer_Fields failed,returning');
416 END IF;
417
418 RETURN;
419 END IF;
420
421 Validate_Customer_Fields(l_sold_to_customer_rec,x_return_status);
422
423 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
424 IF l_debug_level > 0 THEN
425 oe_debug_pub.add ('Validate_Customer_Fields failed,returning');
426 END IF;
427
428 RETURN;
429 END IF;
430 END IF;
431
432 IF NVL(l_new_sold_to_org_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
433 Create_account (
434 p_header_customer_info_rec => l_sold_to_customer_rec,
435 x_cust_account_id =>l_new_sold_to_org_id,
436 x_party_id =>l_new_sold_to_party_id,
437 x_return_status =>x_return_status,
438 x_msg_count =>x_msg_count,
439 x_msg_data => x_msg_data
440 );
441
442
443 IF l_debug_level > 0 THEN
444 oe_debug_pub.add(' Create_account : x_return_status :'||x_return_status);
445 oe_debug_pub.add(' Create_account : l_new_sold_to_party_id :'||l_new_sold_to_party_id);
446 oe_debug_pub.add(' Create_account : l_new_sold_to_org_id :'||l_new_sold_to_org_id);
447 oe_debug_pub.add(' Create_account : x_msg_count :'||x_msg_count);
448 oe_debug_pub.add(' Create_account : x_msg_data :'||x_msg_data);
449 END IF;
450
451 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
452 oe_msg_pub.transfer_msg_stack;
453 RETURN;
454 END IF;
455
456
457 END IF;
458
459 x_sold_to_customer_id := l_new_sold_to_org_id;
460 p_customer_info_tbl(l_sold_to_cust_index).new_account_id := l_new_sold_to_org_id;
461
462
463 END IF;
464
465 IF l_debug_level > 0 THEN
466 oe_debug_pub.add(' Step 1 :Completed ');
467 END IF;
468
469
470 /*
471 1.Check whether customer_id is sent
472 2.If customer_id is not sent check whether value is sent and try to derive id from value
473 3.If customer_id is sent then see if value is aslo sent and try to update the value on the id ig OE_GLOBALS.G_UPDATE_ON_ID is true
474 4.If customer_id could not be resolved till step 3,create the customer
475 5. Check for minimum required fields
476 6.Validate customer related fields
477 7. call TCA API to create the customer
478 */
479
480
481 IF l_ship_to_cust_found
482 THEN
483
484 IF l_debug_level > 0 THEN
485 oe_debug_pub.add('l_ship_to_customer_rec.customer_id :'||l_ship_to_customer_rec.customer_id);
486 END IF;
487
488 IF NVL(l_ship_to_customer_rec.customer_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
489 Check_Duplicate_Customer( p_customer_record => l_ship_to_customer_rec
490 , p_type => 'SHIP_TO'
491 , x_customer_id => l_new_ship_to_customer_id
492 );
493
494 IF l_new_ship_to_customer_id = FND_API.G_MISS_NUM THEN
495 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
496 ELSE
497 x_ship_to_customer_id := l_new_ship_to_customer_id;
498 p_customer_info_tbl(l_ship_to_cust_index).new_account_id := l_new_ship_to_customer_id;
499 END IF;
500
501
502 IF l_debug_level > 0 THEN
503 oe_debug_pub.add('l_new_ship_to_customer_id :'||l_new_ship_to_customer_id);
504 oe_debug_pub.add('l_new_ship_to_party_id :'||l_new_ship_to_party_id);
505 END IF;
506
507 ELSE
508 IF IS_BOTH_ID_VAL_PASSED( p_customer_rec => l_ship_to_customer_rec )
509 AND OE_GLOBALS.G_UPDATE_ON_ID
510 THEN
511 Update_Customer (p_customer_rec => l_ship_to_customer_rec
512 , x_return_status =>x_return_status
513 );
514 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
515 IF l_debug_level > 0 THEN
516 oe_debug_pub.add('Update_Customer failed ,returning');
517 END IF;
518 RETURN;
519 END IF;
520 END IF;
521
522 l_new_ship_to_customer_id := l_ship_to_customer_rec.customer_id;
523 x_ship_to_customer_id := l_ship_to_customer_rec.customer_id;
524 p_customer_info_tbl(l_ship_to_cust_index).new_account_id := l_ship_to_customer_rec.customer_id;
525 END IF;
526
527 IF NVL(l_new_ship_to_customer_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
528
529 Check_Customer_Fields(l_ship_to_customer_rec,x_return_status);
530
531 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
532 IF l_debug_level > 0 THEN
533 oe_debug_pub.add('Check_Customer_Fields failed ,returning');
534 END IF;
535
536 RETURN;
537 END IF;
538
539 Validate_Customer_Fields(l_ship_to_customer_rec,x_return_status);
540
541 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
542 IF l_debug_level > 0 THEN
543 oe_debug_pub.add('Validate_Customer_Fields failed ,returning');
544 END IF;
545
546 RETURN;
547 END IF;
548
549 END IF;
550
551 IF NVL(l_new_ship_to_customer_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
552 THEN
553 Create_account (
554 p_header_customer_info_rec => l_ship_to_customer_rec,
555 x_cust_account_id =>l_new_ship_to_customer_id,
556 x_party_id =>l_new_ship_to_party_id,
557 x_return_status =>x_return_status,
558 x_msg_count =>x_msg_count,
559 x_msg_data => x_msg_data
560 );
561
562 IF l_debug_level > 0 THEN
563 oe_debug_pub.add(' Create_account : x_return_status :'||x_return_status);
564 oe_debug_pub.add(' Create_account : l_new_ship_to_party_id :'||l_new_ship_to_party_id);
565 oe_debug_pub.add(' Create_account : l_new_ship_to_customer_id :'||l_new_ship_to_customer_id);
566 oe_debug_pub.add(' Create_account : x_msg_count :'||x_msg_count);
567 oe_debug_pub.add(' Create_account : x_msg_data :'||x_msg_data);
568 END IF;
569
570 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
571 oe_msg_pub.transfer_msg_stack;
572 RETURN;
573 END IF;
574 END IF;
575
576 x_ship_to_customer_id := l_new_ship_to_customer_id;
577 p_customer_info_tbl(l_ship_to_cust_index).new_account_id := l_new_ship_to_customer_id;
578 END IF;
579
580 IF l_debug_level > 0 THEN
581 oe_debug_pub.add(' Step 2 :Completed ');
582 END IF;
583
584
585 IF l_bill_to_cust_found
586 THEN
587
588 IF l_debug_level > 0 THEN
589 oe_debug_pub.add('l_bill_to_customer_rec.customer_id:'||l_bill_to_customer_rec.customer_id);
590 END IF;
591
592 IF NVL(l_bill_to_customer_rec.customer_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
593
594 Check_Duplicate_Customer( p_customer_record => l_bill_to_customer_rec
595 , p_type => 'BILL_TO'
596 , x_customer_id => l_new_bill_to_customer_id
597 );
598
599
600 IF l_new_bill_to_customer_id = FND_API.G_MISS_NUM THEN
601 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
602 ELSE
603 x_bill_to_customer_id := l_new_bill_to_customer_id;
604 p_customer_info_tbl(l_bill_to_cust_index).new_account_id := l_new_bill_to_customer_id;
605 END IF;
606
607 IF l_debug_level > 0 THEN
608 oe_debug_pub.add('l_new_bill_to_customer_id:'||l_new_bill_to_customer_id);
609 oe_debug_pub.add('l_new_bill_to_party_id:'||l_new_bill_to_party_id);
610 END IF;
611 ELSE
612 IF IS_BOTH_ID_VAL_PASSED( p_customer_rec => l_bill_to_customer_rec )
613 AND OE_GLOBALS.G_UPDATE_ON_ID
614 THEN
615
616 Update_Customer (p_customer_rec => l_bill_to_customer_rec
617 , x_return_status =>x_return_status
618 );
619
620 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
621 IF l_debug_level > 0 THEN
622 oe_debug_pub.add('Update_Customer failed ,returning');
623 END IF;
624 RETURN;
625 END IF;
626
627 END IF;
628
629 l_new_bill_to_customer_id := l_bill_to_customer_rec.customer_id;
630 x_bill_to_customer_id := l_bill_to_customer_rec.customer_id;
631 p_customer_info_tbl(l_bill_to_cust_index).new_account_id := l_bill_to_customer_rec.customer_id;
632
633 END IF;
634
635 IF NVL(l_new_bill_to_customer_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
636
637 Check_Customer_Fields(l_bill_to_customer_rec,x_return_status);
638
639 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
640 IF l_debug_level > 0 THEN
641 oe_debug_pub.add('Check_Customer_Fields failed ,returning');
642 END IF;
643
644 RETURN;
645 END IF;
646
647 Validate_Customer_Fields(l_bill_to_customer_rec,x_return_status);
648
649 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
650 IF l_debug_level > 0 THEN
651 oe_debug_pub.add('Validate_Customer_Fields failed ,returning');
652 END IF;
653
654 RETURN;
655 END IF;
656
657 END IF;
658
659 IF NVL(l_new_bill_to_customer_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
660 THEN
661 Create_account (
662 p_header_customer_info_rec => l_bill_to_customer_rec,
663 x_cust_account_id =>l_new_bill_to_customer_id,
664 x_party_id =>l_new_bill_to_party_id,
665 x_return_status =>x_return_status,
666 x_msg_count =>x_msg_count,
667 x_msg_data => x_msg_data
668 );
669
670 IF l_debug_level > 0 THEN
671 oe_debug_pub.add(' Create_account : x_return_status :'||x_return_status);
672 oe_debug_pub.add(' Create_account : l_new_bill_to_party_id :'||l_new_bill_to_party_id);
673 oe_debug_pub.add(' Create_account : l_new_bill_to_customer_id :'||l_new_bill_to_customer_id);
674 oe_debug_pub.add(' Create_account : x_msg_count :'||x_msg_count);
675 oe_debug_pub.add(' Create_account : x_msg_data :'||x_msg_data);
676 END IF;
677
678 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
679 oe_msg_pub.transfer_msg_stack;
680 RETURN;
681 END IF;
682
683 END IF;
684
685 x_bill_to_customer_id := l_new_bill_to_customer_id;
686 p_customer_info_tbl(l_bill_to_cust_index).new_account_id := l_new_bill_to_customer_id;
687
688 END IF;
689
690 IF l_debug_level > 0 THEN
691 oe_debug_pub.add(' Step 3 :Completed ');
692 END IF;
693
694
695 IF l_deliver_to_cust_found
696 THEN
697
698 IF l_debug_level > 0 THEN
699 oe_Debug_pub.add('l_deliver_to_customer_rec.customer_id:'||l_deliver_to_customer_rec.customer_id);
700 END IF;
701
702 IF NVL(l_deliver_to_customer_rec.customer_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
703
704 Check_Duplicate_Customer( p_customer_record => l_deliver_to_customer_rec
705 , p_type => 'DELIVER_TO'
706 , x_customer_id => l_new_deliver_to_cust_id
707 );
708
709
710 IF l_new_deliver_to_cust_id = FND_API.G_MISS_NUM THEN
711 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
712 ELSE
713 x_deliver_to_customer_id := l_new_deliver_to_cust_id;
714 p_customer_info_tbl(l_deliver_to_cust_index).new_account_id := l_new_deliver_to_cust_id;
715 END IF;
716
717 IF l_debug_level > 0 THEN
718 oe_Debug_pub.add('l_new_deliver_to_cust_id :'||l_new_deliver_to_cust_id);
719 oe_Debug_pub.add('l_new_deliver_to_party_id :'||l_new_deliver_to_party_id);
720 END IF;
721
722 ELSE
723 IF IS_BOTH_ID_VAL_PASSED( p_customer_rec => l_deliver_to_customer_rec )
724 AND OE_GLOBALS.G_UPDATE_ON_ID
725 THEN
726
727 Update_Customer (p_customer_rec => l_deliver_to_customer_rec
728 , x_return_status =>x_return_status
729 );
730 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
731 IF l_debug_level > 0 THEN
732 oe_debug_pub.add('Update_Customer failed ,returning');
733 END IF;
734
735 RETURN;
736 END IF;
737
738 END IF;
739
740 l_new_deliver_to_cust_id := l_deliver_to_customer_rec.customer_id;
741 x_deliver_to_customer_id := l_deliver_to_customer_rec.customer_id;
742 p_customer_info_tbl(l_deliver_to_cust_index).new_account_id := l_deliver_to_customer_rec.customer_id;
743
744 END IF;
745
746 IF NVL(l_new_deliver_to_cust_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
747
748 Check_Customer_Fields(l_deliver_to_customer_rec,x_return_status);
749
750 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
751 IF l_debug_level > 0 THEN
752 oe_debug_pub.add('Check_Customer_Fields failed ,returning');
753 END IF;
754
755 RETURN;
756 END IF;
757
758 Validate_Customer_Fields(l_deliver_to_customer_rec,x_return_status);
759
760 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
761 IF l_debug_level > 0 THEN
762 oe_debug_pub.add('Validate_Customer_Fields failed ,returning');
763 END IF;
764
765 RETURN;
766 END IF;
767
768
769 END IF;
770
771 IF NVL(l_new_deliver_to_cust_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
772 THEN
773 Create_account ( p_header_customer_info_rec => l_deliver_to_customer_rec,
774 x_cust_account_id =>l_new_deliver_to_cust_id,
775 x_party_id =>l_new_deliver_to_party_id ,
776 x_return_status =>x_return_status,
777 x_msg_count =>x_msg_count,
778 x_msg_data => x_msg_data
779 );
780
781
782 IF l_debug_level > 0 THEN
783 oe_debug_pub.add(' Create_account : x_return_status :'||x_return_status);
784 oe_debug_pub.add(' Create_account : l_new_deliver_to_party_id :'||l_new_bill_to_party_id);
785 oe_debug_pub.add(' Create_account : l_new_deliver_to_cust_id :'||l_new_bill_to_customer_id);
786 oe_debug_pub.add(' Create_account : x_msg_count :'||x_msg_count);
787 oe_debug_pub.add(' Create_account : x_msg_data :'||x_msg_data);
788 END IF;
789
790
791 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
792 oe_msg_pub.transfer_msg_stack;
793 RETURN;
794 END IF;
795
796 END IF;
797
798 x_deliver_to_customer_id := l_new_deliver_to_cust_id;
799 p_customer_info_tbl(l_deliver_to_cust_index).new_account_id := l_new_deliver_to_cust_id;
800 END IF;
801
802
803 --Create Relationship b/w customers
804 IF ( NVL(x_sold_to_customer_id,FND_API.G_MISS_NUM)<> FND_API.G_MISS_NUM ) THEN
805
806 Create_relationships ( p_sold_to_customer_id => x_sold_to_customer_id,
807 p_ship_to_customer_id => x_ship_to_customer_id,
808 p_bill_to_customer_id => x_bill_to_customer_id,
809 p_deliver_to_cust_id => x_deliver_to_customer_id,
810 x_return_status =>x_return_status ,
811 x_msg_count => x_msg_count,
812 x_msg_data =>x_msg_data);
813
814 IF l_debug_level > 0 THEN
815 oe_debug_pub.add('Create_relationships :x_return_status : '||x_return_status);
816 oe_debug_pub.add('Create_relationships :x_msg_data : '||x_msg_count);
817 oe_debug_pub.add('Create_relationships :x_msg_data : '||x_msg_data);
818 END IF;
819
820 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
821 oe_msg_pub.transfer_msg_stack;
822 RETURN;
823 END IF;
824
825 END IF;
826 --End of create_relationship
827
828 << ADDRESS_CREATION >>
829 --Address Creation starts for customers
830
831 IF p_ship_to_address_ref IS NOT NULL THEN
832
833 FOR i in 1..p_customer_info_tbl.last LOOP
834 IF p_customer_info_tbl.exists(i) THEN
835 IF p_customer_info_tbl(i).customer_info_type_code ='ADDRESS' THEN
836 IF p_customer_info_tbl(i).customer_info_ref = p_ship_to_address_ref THEN
837 IF p_customer_info_tbl(i).customer_id IS NULL THEN --Both Cust and address being created in this call
838
839 FOR j in 1..p_customer_info_tbl.last LOOP
840 IF p_customer_info_tbl.exists(j) THEN
841 IF p_customer_info_tbl(j).customer_info_ref = p_customer_info_tbl(i).parent_customer_info_ref
842 AND p_customer_info_tbl(j).customer_info_type_code = 'CUSTOMER' THEN
843 IF l_ship_addr_found = FALSE THEN
844 l_ship_addr_found := TRUE;
845 l_ship_addr_rec_index := i;
846 l_customer_id := p_customer_info_tbl(j).new_account_id;
847 END IF;
848 END IF;
849 END IF;
850 END LOOP;
851 ELSE --Address being added to existing customer
852
853 IF l_ship_addr_found = FALSE THEN
854 l_ship_addr_found := TRUE;
855 l_ship_addr_rec_index := i;
856 l_customer_id := p_customer_info_tbl(i).customer_id;
857 END IF;
858
859
860 END IF;
861
862 END IF;
863 END IF;
864 END IF;
865 END LOOP;
866 END IF;
867
868
869
870 IF l_ship_addr_found THEN
871
872 IF NOT IS_VALID_ID( p_customer_id => l_customer_id ) THEN
873 x_return_status := FND_API.G_RET_STS_ERROR;
874 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
875 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CUSTOMER_ID');
876 OE_MSG_PUB.Add;
877 RETURN;
878 END IF;
879
880
881 Create_Addresses (
882 p_customer_info_tbl => p_customer_info_tbl
883 , p_operation_code => p_operation_code
884 , p_customer_id => l_customer_id
885 , p_address_rec_index => l_ship_addr_rec_index
886 , p_address_usage => 'SHIP_TO'
887 , p_sold_to_customer_id => x_sold_to_customer_id
888 , p_ship_to_customer_id => x_ship_to_customer_id
889 , p_bill_to_customer_id => x_bill_to_customer_id
890 , p_deliver_to_customer_id =>x_deliver_to_customer_id
891 , x_ship_to_org_id => x_ship_to_org_id
892 , x_invoice_to_org_id => x_invoice_to_org_id
893 , x_deliver_to_org_id => x_deliver_to_org_id
894 , x_sold_to_site_use_id => x_sold_to_site_use_id
895 , x_return_status => x_return_status
896 , x_msg_count => x_msg_count
897 , x_msg_data => x_msg_data
898 ) ;
899
900 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
901 RETURN;
902 END IF;
903 END IF;
904
905
906 IF p_bill_to_address_ref IS NOT NULL THEN
907
908 FOR i in 1..p_customer_info_tbl.last LOOP
909 IF p_customer_info_tbl.exists(i) THEN
910 IF p_customer_info_tbl(i).customer_info_type_code ='ADDRESS' THEN
911 IF p_customer_info_tbl(i).customer_info_ref = p_bill_to_address_ref THEN
912 IF p_customer_info_tbl(i).customer_id IS NULL THEN --Both Cust and address being created in this call
913
914 FOR j in 1..p_customer_info_tbl.last LOOP
915 IF p_customer_info_tbl.exists(j) THEN
916 IF p_customer_info_tbl(j).customer_info_ref = p_customer_info_tbl(i).parent_customer_info_ref
917 AND p_customer_info_tbl(j).customer_info_type_code = 'CUSTOMER' THEN
918 IF l_bill_addr_found = FALSE THEN
919 l_bill_addr_found := TRUE;
920 l_bill_addr_rec_index := i;
921 l_customer_id := p_customer_info_tbl(j).new_account_id;
922 END IF;
923 END IF;
924 END IF;
925 END LOOP;
926
927 ELSE
928
929 IF l_bill_addr_found = FALSE THEN
930 l_bill_addr_found := TRUE;
931 l_bill_addr_rec_index := i;
932 l_customer_id := p_customer_info_tbl(i).customer_id;
933 END IF;
934
935
936 END IF;
937
938 END IF;
939 END IF;
940 END IF;
941 END LOOP;
942
943 END IF;
944
945
946 IF l_bill_addr_found THEN
947
948 IF NOT IS_VALID_ID( p_customer_id => l_customer_id ) THEN
949 x_return_status := FND_API.G_RET_STS_ERROR;
950 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
951 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CUSTOMER_ID');
952 OE_MSG_PUB.Add;
953 RETURN;
954 END IF;
955
956
957 Create_Addresses (
958 p_customer_info_tbl => p_customer_info_tbl
959 , p_operation_code => p_operation_code
960 , p_customer_id => l_customer_id
961 , p_address_rec_index => l_bill_addr_rec_index
962 , p_address_usage => 'BILL_TO'
963 , p_sold_to_customer_id => x_sold_to_customer_id
964 , p_ship_to_customer_id => x_ship_to_customer_id
965 , p_bill_to_customer_id => x_bill_to_customer_id
966 , p_deliver_to_customer_id => x_deliver_to_customer_id
967 , x_ship_to_org_id => x_ship_to_org_id
968 , x_invoice_to_org_id => x_invoice_to_org_id
969 , x_deliver_to_org_id => x_deliver_to_org_id
970 , x_sold_to_site_use_id => x_sold_to_site_use_id
971 , x_return_status => x_return_status
972 , x_msg_count => x_msg_count
973 , x_msg_data => x_msg_data
974 ) ;
975
976 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
977 RETURN;
978 END IF;
979
980 END IF;
981
982 IF p_deliver_to_address_ref IS NOT NULL THEN
983
984 FOR i in 1..p_customer_info_tbl.last LOOP
985 IF p_customer_info_tbl.exists(i) THEN
986 IF p_customer_info_tbl(i).customer_info_type_code ='ADDRESS' THEN
987 IF p_customer_info_tbl(i).customer_info_ref = p_deliver_to_address_ref THEN
988 IF p_customer_info_tbl(i).customer_id IS NULL THEN --Both Cust and address being created in this call
989
990 FOR j in 1..p_customer_info_tbl.last LOOP
991 IF p_customer_info_tbl.exists(j) THEN
992 IF p_customer_info_tbl(j).customer_info_ref = p_customer_info_tbl(i).parent_customer_info_ref
993 AND p_customer_info_tbl(j).customer_info_type_code = 'CUSTOMER' THEN
994 IF l_deliver_addr_found = FALSE THEN
995 l_deliver_addr_found := TRUE;
996 l_deliver_addr_rec_index := i;
997 l_customer_id := p_customer_info_tbl(j).new_account_id;
998 END IF;
999 END IF;
1000 END IF;
1001 END LOOP;
1002 ELSE
1003
1004 IF l_deliver_addr_found = FALSE THEN
1005 l_deliver_addr_found := TRUE;
1006 l_deliver_addr_rec_index := i;
1007 l_customer_id := p_customer_info_tbl(i).customer_id;
1008 END IF;
1009
1010
1011 END IF;
1012
1013 END IF;
1014 END IF;
1015 END IF;
1016 END LOOP;
1017
1018 END IF;
1019
1020
1021 IF l_deliver_addr_found THEN
1022
1023 IF NOT IS_VALID_ID( p_customer_id => l_customer_id ) THEN
1024 x_return_status := FND_API.G_RET_STS_ERROR;
1025 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
1026 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CUSTOMER_ID');
1027 OE_MSG_PUB.Add;
1028 RETURN;
1029 END IF;
1030
1031
1032 Create_Addresses (
1033 p_customer_info_tbl => p_customer_info_tbl
1034 , p_operation_code => p_operation_code
1035 , p_customer_id => l_customer_id
1036 , p_address_rec_index => l_deliver_addr_rec_index
1037 , p_address_usage => 'DELIVER_TO'
1038 , p_sold_to_customer_id => x_sold_to_customer_id
1039 , p_ship_to_customer_id => x_ship_to_customer_id
1040 , p_bill_to_customer_id => x_bill_to_customer_id
1041 , p_deliver_to_customer_id => x_deliver_to_customer_id
1042 , x_ship_to_org_id => x_ship_to_org_id
1043 , x_invoice_to_org_id => x_invoice_to_org_id
1044 , x_deliver_to_org_id => x_deliver_to_org_id
1045 , x_sold_to_site_use_id => x_sold_to_site_use_id
1046 , x_return_status => x_return_status
1047 , x_msg_count => x_msg_count
1048 , x_msg_data => x_msg_data
1049 ) ;
1050
1051 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1052 RETURN;
1053 END IF;
1054
1055 END IF;
1056
1057 IF p_sold_to_address_ref IS NOT NULL THEN
1058
1059 FOR i in 1..p_customer_info_tbl.last LOOP
1060 IF p_customer_info_tbl.exists(i) THEN
1061 IF p_customer_info_tbl(i).customer_info_type_code ='ADDRESS' THEN
1062 IF p_customer_info_tbl(i).customer_info_ref = p_sold_to_address_ref THEN
1063 IF p_customer_info_tbl(i).customer_id IS NULL THEN --Both Cust and address being created in this call
1064
1065 FOR j in 1..p_customer_info_tbl.last LOOP
1066 IF p_customer_info_tbl.exists(j) THEN
1067 IF p_customer_info_tbl(j).customer_info_ref = p_customer_info_tbl(i).parent_customer_info_ref
1068 AND p_customer_info_tbl(j).customer_info_type_code = 'CUSTOMER' THEN
1069 IF l_sold_addr_found = FALSE THEN
1070 l_sold_addr_found := TRUE;
1071 l_sold_addr_rec_index := i;
1072 l_customer_id := p_customer_info_tbl(j).new_account_id;
1073 END IF;
1074 END IF;
1075 END IF;
1076 END LOOP;
1077 ELSE
1078
1079 IF l_sold_addr_found = FALSE THEN
1080 l_sold_addr_found := TRUE;
1081 l_sold_addr_rec_index := i;
1082 l_customer_id := p_customer_info_tbl(i).customer_id;
1083 END IF;
1084
1085
1086 END IF;
1087
1088 END IF;
1089 END IF;
1090 END IF;
1091 END LOOP;
1092
1093 END IF;
1094
1095
1096 IF l_sold_addr_found THEN
1097
1098 IF NOT IS_VALID_ID( p_customer_id => l_customer_id ) THEN
1099 x_return_status := FND_API.G_RET_STS_ERROR;
1100 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
1101 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CUSTOMER_ID');
1102 OE_MSG_PUB.Add;
1103 RETURN;
1104 END IF;
1105
1106 Create_Addresses (
1107 p_customer_info_tbl => p_customer_info_tbl
1108 , p_operation_code => p_operation_code
1109 , p_customer_id => l_customer_id
1110 , p_address_rec_index => l_sold_addr_rec_index
1111 , p_address_usage => 'SOLD_TO'
1112 , p_sold_to_customer_id => x_sold_to_customer_id
1113 , p_ship_to_customer_id => x_ship_to_customer_id
1114 , p_bill_to_customer_id => x_bill_to_customer_id
1115 , p_deliver_to_customer_id => x_deliver_to_customer_id
1116 , x_ship_to_org_id => x_ship_to_org_id
1117 , x_invoice_to_org_id => x_invoice_to_org_id
1118 , x_deliver_to_org_id => x_deliver_to_org_id
1119 , x_sold_to_site_use_id => x_sold_to_site_use_id
1120 , x_return_status => x_return_status
1121 , x_msg_count => x_msg_count
1122 , x_msg_data => x_msg_data
1123 ) ;
1124
1125 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1126 RETURN;
1127 END IF;
1128
1129 END IF;
1130
1131
1132
1133
1134 --Contacts
1135
1136 IF p_sold_to_contact_ref IS NOT NULL THEN
1137
1138 FOR i in 1..p_customer_info_tbl.last LOOP
1139 IF p_customer_info_tbl.exists(i) THEN
1140 IF p_customer_info_tbl(i).customer_info_type_code ='CONTACT' THEN
1141 IF p_customer_info_tbl(i).customer_info_ref = p_sold_to_contact_ref THEN
1142 IF p_customer_info_tbl(i).customer_id IS NULL THEN --Both Cust and address being created in this call
1143
1144 FOR j in 1..p_customer_info_tbl.last LOOP
1145 IF p_customer_info_tbl.exists(j) THEN
1146 IF p_customer_info_tbl(j).customer_info_ref = p_customer_info_tbl(i).parent_customer_info_ref
1147 AND p_customer_info_tbl(j).customer_info_type_code = 'CUSTOMER' THEN
1148 IF l_sold_cont_found = FALSE THEN
1149 l_sold_cont_found := TRUE;
1150 l_sold_cont_rec_index := i;
1151 l_customer_id := p_customer_info_tbl(j).new_account_id;
1152 END IF;
1153 END IF;
1154 END IF;
1155 END LOOP;
1156 ELSE
1157
1158 IF l_sold_cont_found = FALSE THEN
1159 l_sold_cont_found := TRUE;
1160 l_sold_cont_rec_index := i;
1161 l_customer_id := p_customer_info_tbl(i).customer_id;
1162 END IF;
1163
1164
1165 END IF;
1166
1167 END IF;
1168 END IF;
1169 END IF;
1170 END LOOP;
1171
1172 END IF;
1173
1174
1175 IF l_sold_cont_found THEN
1176
1177 IF NOT IS_VALID_ID( p_customer_id => l_customer_id ) THEN
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
1180 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CUSTOMER_ID');
1181 OE_MSG_PUB.Add;
1182 RETURN;
1183 END IF;
1184
1185 Create_Contact ( p_customer_info_tbl => p_customer_info_tbl
1186 , p_operation_code => p_operation_code
1187 , p_customer_id => l_customer_id
1188 , p_customer_rec_index => l_sold_cont_rec_index
1189 , p_usage_code => 'SOLD_TO'
1190 , x_sold_to_contact_id => x_sold_to_contact_id
1191 , x_ship_to_contact_id => x_ship_to_contact_id
1192 , x_invoice_to_contact_id => x_invoice_to_contact_id
1193 , x_deliver_to_contact_id => x_deliver_to_contact_id
1194 , x_return_status => x_return_status
1195 , x_msg_count => x_msg_count
1196 , x_msg_data => x_msg_data
1197 );
1198
1199 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1200 oe_msg_pub.transfer_msg_stack;
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 RETURN;
1203 END IF;
1204
1205 END IF;
1206
1207 IF p_ship_to_contact_ref IS NOT NULL THEN
1208
1209 FOR i in 1..p_customer_info_tbl.last LOOP
1210 IF p_customer_info_tbl.exists(i) THEN
1211 IF p_customer_info_tbl(i).customer_info_type_code ='CONTACT' THEN
1212 IF p_customer_info_tbl(i).customer_info_ref = p_ship_to_contact_ref THEN
1213 IF p_customer_info_tbl(i).customer_id IS NULL THEN --Both Cust and address being created in this call
1214
1215 FOR j in 1..p_customer_info_tbl.last LOOP
1216 IF p_customer_info_tbl.exists(j) THEN
1217 IF p_customer_info_tbl(j).customer_info_ref = p_customer_info_tbl(i).parent_customer_info_ref
1218 AND p_customer_info_tbl(j).customer_info_type_code = 'CUSTOMER' THEN
1219 IF l_ship_cont_found = FALSE THEN
1220 l_ship_cont_found := TRUE;
1221 l_ship_cont_rec_index := i;
1222 l_customer_id := p_customer_info_tbl(j).new_account_id;
1223 END IF;
1224 END IF;
1225 END IF;
1226 END LOOP;
1227 ELSE
1228
1229 IF l_ship_cont_found = FALSE THEN
1230 l_ship_cont_found := TRUE;
1231 l_ship_cont_rec_index := i;
1232 l_customer_id := p_customer_info_tbl(i).customer_id;
1233 END IF;
1234
1235
1236 END IF;
1237
1238 END IF;
1239 END IF;
1240 END IF;
1241 END LOOP;
1242
1243 END IF;
1244
1245
1246 IF l_ship_cont_found THEN
1247
1248 IF NOT IS_VALID_ID( p_customer_id => l_customer_id ) THEN
1249 x_return_status := FND_API.G_RET_STS_ERROR;
1250 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
1251 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CUSTOMER_ID');
1252 OE_MSG_PUB.Add;
1253 RETURN;
1254 END IF;
1255
1256 Create_Contact ( p_customer_info_tbl => p_customer_info_tbl
1257 , p_operation_code => p_operation_code
1258 , p_customer_id => l_customer_id
1259 , p_customer_rec_index => l_ship_cont_rec_index
1260 , p_usage_code => 'SHIP_TO'
1261 , x_sold_to_contact_id => x_sold_to_contact_id
1262 , x_ship_to_contact_id => x_ship_to_contact_id
1263 , x_invoice_to_contact_id => x_invoice_to_contact_id
1264 , x_deliver_to_contact_id => x_deliver_to_contact_id
1265 , x_return_status => x_return_status
1266 , x_msg_count => x_msg_count
1267 , x_msg_data => x_msg_data
1268 );
1269
1270 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1271 oe_msg_pub.transfer_msg_stack;
1272 x_return_status := FND_API.G_RET_STS_ERROR;
1273 RETURN;
1274 END IF;
1275
1276 END IF;
1277
1278
1279
1280 IF p_bill_to_contact_ref IS NOT NULL THEN
1281
1282 FOR i in 1..p_customer_info_tbl.last LOOP
1283 IF p_customer_info_tbl.exists(i) THEN
1284 IF p_customer_info_tbl(i).customer_info_type_code ='CONTACT' THEN
1285 IF p_customer_info_tbl(i).customer_info_ref = p_bill_to_contact_ref THEN
1286 IF p_customer_info_tbl(i).customer_id IS NULL THEN --Both Cust and address being created in this call
1287
1288 FOR j in 1..p_customer_info_tbl.last LOOP
1289 IF p_customer_info_tbl.exists(j) THEN
1290 IF p_customer_info_tbl(j).customer_info_ref = p_customer_info_tbl(i).parent_customer_info_ref
1291 AND p_customer_info_tbl(j).customer_info_type_code = 'CUSTOMER' THEN
1292 IF l_bill_cont_found = FALSE THEN
1293 l_bill_cont_found := TRUE;
1294 l_bill_cont_rec_index := i;
1295 l_customer_id := p_customer_info_tbl(j).new_account_id;
1296 END IF;
1297 END IF;
1298 END IF;
1299 END LOOP;
1300 ELSE
1301
1302 IF l_bill_cont_found = FALSE THEN
1303 l_bill_cont_found := TRUE;
1304 l_bill_cont_rec_index := i;
1305 l_customer_id := p_customer_info_tbl(i).customer_id;
1306 END IF;
1307
1308
1309 END IF;
1310
1311 END IF;
1312 END IF;
1313 END IF;
1314 END LOOP;
1315
1316 END IF;
1317
1318
1319 IF l_bill_cont_found THEN
1320
1321 IF NOT IS_VALID_ID( p_customer_id => l_customer_id ) THEN
1322 x_return_status := FND_API.G_RET_STS_ERROR;
1323 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
1324 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CUSTOMER_ID');
1325 OE_MSG_PUB.Add;
1326 RETURN;
1327 END IF;
1328
1329 Create_Contact ( p_customer_info_tbl => p_customer_info_tbl
1330 , p_operation_code => p_operation_code
1331 , p_customer_id => l_customer_id
1332 , p_customer_rec_index => l_bill_cont_rec_index
1333 , p_usage_code => 'BILL_TO'
1334 , x_sold_to_contact_id => x_sold_to_contact_id
1335 , x_ship_to_contact_id => x_ship_to_contact_id
1336 , x_invoice_to_contact_id => x_invoice_to_contact_id
1337 , x_deliver_to_contact_id => x_deliver_to_contact_id
1338 , x_return_status => x_return_status
1339 , x_msg_count => x_msg_count
1340 , x_msg_data => x_msg_data
1341 );
1342
1343 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1344 oe_msg_pub.transfer_msg_stack;
1345 x_return_status := FND_API.G_RET_STS_ERROR;
1346 RETURN;
1347 END IF;
1348
1349 END IF;
1350
1351
1352 IF p_deliver_to_contact_ref IS NOT NULL THEN
1353
1354 FOR i in 1..p_customer_info_tbl.last LOOP
1355 IF p_customer_info_tbl.exists(i) THEN
1356 IF p_customer_info_tbl(i).customer_info_type_code ='CONTACT' THEN
1357 IF p_customer_info_tbl(i).customer_info_ref = p_deliver_to_contact_ref THEN
1358 IF p_customer_info_tbl(i).customer_id IS NULL THEN --Both Cust and address being created in this call
1359
1360 FOR j in 1..p_customer_info_tbl.last LOOP
1361 IF p_customer_info_tbl.exists(j) THEN
1362 IF p_customer_info_tbl(j).customer_info_ref = p_customer_info_tbl(i).parent_customer_info_ref
1363 AND p_customer_info_tbl(j).customer_info_type_code = 'CUSTOMER' THEN
1364 IF l_deliver_cont_found = FALSE THEN
1365 l_deliver_cont_found := TRUE;
1366 l_deliver_cont_rec_index := i;
1367 l_customer_id := p_customer_info_tbl(j).new_account_id;
1368 END IF;
1369 END IF;
1370 END IF;
1371 END LOOP;
1372 ELSE
1373
1374 IF l_deliver_cont_found = FALSE THEN
1375 l_deliver_cont_found := TRUE;
1376 l_deliver_cont_rec_index := i;
1377 l_customer_id := p_customer_info_tbl(i).customer_id;
1378 END IF;
1379
1380
1381 END IF;
1382
1383 END IF;
1384 END IF;
1385 END IF;
1386 END LOOP;
1387
1388 END IF;
1389
1390
1391 IF l_deliver_cont_found THEN
1392
1393 IF NOT IS_VALID_ID( p_customer_id => l_customer_id ) THEN
1394 x_return_status := FND_API.G_RET_STS_ERROR;
1395 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
1396 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CUSTOMER_ID');
1397 OE_MSG_PUB.Add;
1398 RETURN;
1399 END IF;
1400
1401 Create_Contact ( p_customer_info_tbl => p_customer_info_tbl
1402 , p_operation_code => p_operation_code
1403 , p_customer_id => l_customer_id
1404 , p_customer_rec_index => l_deliver_cont_rec_index
1405 , p_usage_code => 'DELIVER_TO'
1406 , x_sold_to_contact_id => x_sold_to_contact_id
1407 , x_ship_to_contact_id => x_ship_to_contact_id
1408 , x_invoice_to_contact_id => x_invoice_to_contact_id
1409 , x_deliver_to_contact_id => x_deliver_to_contact_id
1410 , x_return_status => x_return_status
1411 , x_msg_count => x_msg_count
1412 , x_msg_data => x_msg_data
1413 );
1414
1415 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1416 oe_msg_pub.transfer_msg_stack;
1417 x_return_status := FND_API.G_RET_STS_ERROR;
1418 RETURN;
1419 END IF;
1420
1421 END IF;
1422
1423
1424
1425 IF l_debug_level > 0 THEN
1426 oe_debug_pub.add(' Entering OE_CUSTOMER_INFO_PVT.get_customer_info_ids');
1427 END IF;
1428
1429
1430 EXCEPTION
1431
1432 WHEN OTHERS THEN
1433
1434 IF l_debug_level > 0 THEN
1435 oe_debug_pub.add('OE_CUSTOMER_INFO_PVT.get_customer_info_ids : Other Errors :'||SQLERRM);
1436 END IF;
1437
1438 x_return_status := FND_API.G_RET_STS_ERROR;
1439
1440 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1441 THEN
1442 OE_MSG_PUB.Add_Exc_Msg
1443 ( G_PKG_NAME
1444 , 'get_customer_info_ids'
1445 );
1446 END IF;
1447
1448 OE_MSG_PUB.Count_And_Get
1449 ( p_count => x_msg_count
1450 , p_data => x_msg_data
1451 );
1452
1453 End get_customer_info_ids;
1454
1455 /* This procedure will call the wraper procedure oe_oe_inline_address.create_account which
1456 inturn calls TCA apis to create a customer account. Email and phone contact points for the party will
1457 also be created if passed*/
1458
1459 Procedure Create_account (p_header_customer_info_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE,
1460 x_party_id OUT NOCOPY NUMBER,
1461 x_cust_account_id OUT NOCOPY NUMBER,
1462 x_return_status OUT NOCOPY VARCHAR2,
1463 x_msg_count OUT NOCOPY NUMBER,
1464 x_msg_data OUT NOCOPY VARCHAR2
1465 ) IS
1466 l_customer_info_rec OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE;
1467 l_party_id NUMBER := NULL;
1468 l_party_type VARCHAR2(100) := NULL;
1469 l_organization_name VARCHAR2(360) ;
1470 l_customer_first_name varchar2(100);
1471 l_customer_middle_name varchar2(100);
1472 l_customer_last_name varchar2(100);
1473 l_customer_name_adjunct varchar2(100);
1474 l_customer_name_suffix varchar2(100);
1475 l_party_number NUMBER;
1476 x_party_number varchar2(30);
1477 l_cust_account_number varchar2(30);
1478
1479 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1480
1481 BEGIN
1482
1483 IF l_debug_level > 0 THEN
1484 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Create_account');
1485 END IF;
1486
1487 x_return_status := FND_API.G_RET_STS_SUCCESS;
1488 l_customer_info_rec := p_header_customer_info_rec;
1489
1490 IF G_AUTO_PARTY_NUMBERING = 'Y' THEN
1491 l_party_number := NULL;
1492 ELSE
1493 l_party_number := l_customer_info_rec.party_number;
1494 END IF;
1495
1496 IF G_AUTO_CUST_NUMBERING = 'Y' THEN
1497 l_cust_account_number := NULL;
1498 ELSE
1499 l_cust_account_number := l_customer_info_rec.customer_number;
1500 END IF;
1501
1502
1503 IF l_customer_info_rec.customer_type = 'PERSON' THEN
1504 l_party_type := 'PERSON';
1505 l_customer_first_name := l_customer_info_rec.person_first_name;
1506 l_customer_middle_name := l_customer_info_rec.person_middle_name;
1507 l_customer_last_name := l_customer_info_rec.person_last_name;
1508 l_customer_name_adjunct := l_customer_info_rec.person_title;
1509 l_customer_name_suffix := l_customer_info_rec.person_name_suffix;
1510
1511 ELSE
1512 l_party_type := 'ORGANIZATION';
1513 l_organization_name := l_customer_info_rec.organization_name;
1514 END IF;
1515
1516 --account under an existing party
1517 IF l_customer_info_rec.party_id IS NOT NULL THEN
1518
1519 IF IS_VALID_ID ( p_party_id => l_customer_info_rec.party_id) THEN
1520 l_party_id := l_customer_info_rec.party_id;
1521 ELSE
1522 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
1523 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','PARTY_ID');
1524 OE_MSG_PUB.Add;
1525 x_return_status := FND_API.G_RET_STS_ERROR;
1526
1527 END IF;
1528 END IF;
1529
1530
1531 If l_debug_level >0 then
1532 oe_debug_pub.add('l_party_type :'||l_party_type);
1533 oe_debug_pub.add('l_party_number :'||l_party_number);
1534 oe_debug_pub.add('l_organization_name :'||l_organization_name);
1535 oe_debug_pub.add('l_customer_first_name :'||l_customer_first_name);
1536 oe_debug_pub.add('l_customer_middle_name :'||l_customer_middle_name);
1537 oe_debug_pub.add('l_customer_last_name :'||l_customer_last_name);
1538
1539 End If;
1540
1541
1542 oe_oe_inline_address.create_account(
1543 p_party_number=> l_party_number,
1544 p_organization_name=>l_organization_name,
1545 p_party_type=>l_party_type,
1546 p_party_id=>l_party_id,
1547 p_first_name=>l_customer_first_name,
1548 p_last_name=>l_customer_last_name,
1549 p_middle_name=>l_customer_middle_name,
1550 p_name_suffix=>l_customer_name_suffix,
1551 p_title=>l_customer_name_adjunct,
1552 p_email=> l_customer_info_rec.email_address,
1553 c_attribute_category=>l_customer_info_rec.attribute_category ,
1554 c_attribute1=>l_customer_info_rec.attribute1,
1555 c_attribute2=>l_customer_info_rec.attribute2,
1556 c_attribute3=>l_customer_info_rec.attribute3,
1557 c_attribute4=>l_customer_info_rec.attribute4,
1558 c_attribute5=>l_customer_info_rec.attribute5,
1559 c_attribute6=>l_customer_info_rec.attribute6,
1560 c_attribute7=>l_customer_info_rec.attribute8,
1561 c_attribute8=>l_customer_info_rec.attribute8,
1562 c_attribute9=>l_customer_info_rec.attribute9,
1563 c_attribute10=>l_customer_info_rec.attribute10,
1564 c_attribute11=>l_customer_info_rec.attribute11,
1565 c_attribute12=>l_customer_info_rec.attribute12,
1566 c_attribute13=>l_customer_info_rec.attribute13,
1567 c_attribute14=>l_customer_info_rec.attribute14,
1568 c_attribute15=>l_customer_info_rec.attribute15,
1569 c_attribute16=>l_customer_info_rec.attribute16,
1570 c_attribute17=>l_customer_info_rec.attribute17,
1571 c_attribute18=>l_customer_info_rec.attribute18,
1572 c_attribute19=>l_customer_info_rec.attribute19,
1573 c_attribute20=>l_customer_info_rec.attribute20,
1574 c_global_attribute_category=>l_customer_info_rec.global_attribute_category,
1575 c_global_attribute1=>l_customer_info_rec.global_attribute1,
1576 c_global_attribute2=>l_customer_info_rec.global_attribute2,
1577 c_global_attribute3=>l_customer_info_rec.global_attribute3,
1578 c_global_attribute4=>l_customer_info_rec.global_attribute4,
1579 c_global_attribute5=>l_customer_info_rec.global_attribute5,
1580 c_global_attribute6=>l_customer_info_rec.global_attribute6,
1581 c_global_attribute7=>l_customer_info_rec.global_attribute7,
1582 c_global_attribute8=>l_customer_info_rec.global_attribute8,
1583 c_global_attribute9=>l_customer_info_rec.global_attribute9,
1584 c_global_attribute10=>l_customer_info_rec.global_attribute11,
1585 c_global_attribute11=>l_customer_info_rec.global_attribute12,
1586 c_global_attribute12=>l_customer_info_rec.global_attribute12,
1587 c_global_attribute13=>l_customer_info_rec.global_attribute13,
1588 c_global_attribute14=>l_customer_info_rec.global_attribute14,
1589 c_global_attribute15=>l_customer_info_rec.global_attribute15,
1590 c_global_attribute16=>l_customer_info_rec.global_attribute16,
1591 c_global_attribute17=>l_customer_info_rec.global_attribute17,
1592 c_global_attribute18=>l_customer_info_rec.global_attribute18,
1593 c_global_attribute19=>l_customer_info_rec.global_attribute19,
1594 c_global_attribute20=>l_customer_info_rec.global_attribute20,
1595 p_alternate_name=>NULL,
1596 p_tax_reference=>NULL,
1597 p_taxpayer_id=>NULL,
1598 x_party_id=>x_party_id,
1599 x_party_number=>x_party_number,
1600 x_cust_Account_id=> x_cust_account_id,
1601 x_cust_account_number=>l_cust_account_number,--IN/OUT Check
1602 x_return_status=>x_return_status,
1603 x_msg_count=>x_msg_count,
1604 x_msg_data=>x_msg_data,
1605 in_Created_by_module=>G_CREATED_BY_MODULE ,
1606 p_orig_system => l_customer_info_rec.orig_system,
1607 p_orig_system_reference => l_customer_info_rec.orig_system_reference,
1608 p_account_description => l_customer_info_rec.account_description
1609 );
1610
1611 IF l_debug_level > 0 THEN
1612 oe_debug_pub.add('Create_account TCA CALL:x_return_status:'||x_return_status);
1613 oe_debug_pub.add('Create_account TCA CALL:x_msg_count:'||x_msg_count);
1614 oe_debug_pub.add('Create_account TCA CALL:x_msg_data:'||x_msg_data);
1615 oe_debug_pub.add('Create_account TCA CALL:x_cust_account_id:'||x_cust_account_id);
1616 oe_debug_pub.add('Create_account TCA CALL:x_cust_account_number:'||l_cust_account_number);
1617 END IF;
1618
1619 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1620 RETURN;
1621 END IF;
1622
1623 IF NVL(l_customer_info_rec.email_address,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR Then
1624
1625 oe_oe_inline_address.create_contact_point
1626 (
1627 in_contact_point_type => 'EMAIL',
1628 in_owner_table_id => x_party_id,
1629 in_email => l_customer_info_rec.email_address,
1630 in_phone_area_code => NULL,
1631 in_phone_number => NULL,
1632 in_phone_extension => NULL,
1633 in_phone_country_code => NULL,
1634 p_created_by_module => G_CREATED_BY_MODULE,
1635 p_orig_system => l_customer_info_rec.orig_system,
1636 p_orig_system_reference => l_customer_info_rec.orig_system_reference,
1637 x_return_status => x_return_status,
1638 x_msg_count => x_msg_count,
1639 x_msg_data => x_msg_data
1640 );
1641
1642 IF l_debug_level > 0 THEN
1643 oe_debug_pub.add('create_contact_point EMAIL TCA CALL:x_return_status:'||x_return_status);
1644 oe_debug_pub.add('create_contact_point EMAIL TCA CALL:x_msg_count:'||x_msg_count);
1645 oe_debug_pub.add('create_contact_point EMAIL TCA CALL:x_msg_data:'||x_msg_data);
1646 END IF;
1647
1648
1649 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1650 RETURN;
1651 END IF;
1652
1653
1654 END IF;
1655
1656 IF NVL(l_customer_info_rec.phone_number,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR Then
1657
1658 oe_oe_inline_address.create_contact_point
1659 (in_contact_point_type =>'PHONE',
1660 in_owner_table_id=>x_party_id,
1661 in_email=>NULL,
1662 in_phone_area_code =>l_customer_info_rec.phone_area_code,
1663 in_phone_number=>l_customer_info_rec.phone_number,
1664 in_phone_extension=>l_customer_info_rec.phone_extension,
1665 in_phone_country_code=>l_customer_info_rec.phone_country_code,
1666 x_return_status=>x_return_status,
1667 x_msg_count=>x_msg_count,
1668 x_msg_data=>x_msg_data
1669 );
1670
1671 IF l_debug_level > 0 THEN
1672 oe_debug_pub.add('create_contact_point PHONE TCA CALL:x_return_status:'||x_return_status);
1673 oe_debug_pub.add('create_contact_point PHONE TCA CALL:x_msg_count:'||x_msg_count);
1674 oe_debug_pub.add('create_contact_point PHONE TCA CALL:x_msg_data:'||x_msg_data);
1675 END IF;
1676
1677 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1678 RETURN;
1679 END IF;
1680
1681 END IF;
1682
1683 Exception
1684 WHEN OTHERS THEN
1685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1686
1687 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1688 THEN
1689 OE_MSG_PUB.Add_Exc_Msg
1690 ( G_PKG_NAME
1691 , 'Create_account'
1692 );
1693 END IF;
1694
1695 End Create_account;
1696
1697 /*
1698 This procedure creates relationship between the sold_to customer and other site customers if a
1699 relation ship does not exists already.Relation ships will be created only if the sysem paramer OM:Customer
1700 Relation Ship is set to Related Customer
1701 */
1702 Procedure Create_relationships (p_sold_to_customer_id IN NUMBER,
1703 p_ship_to_customer_id NUMBER DEFAULT NULL,
1704 p_bill_to_customer_id NUMBER DEFAULT NULL,
1705 p_deliver_to_cust_id NUMBER DEFAULT NULL,
1706 x_return_status OUT NOCOPY VARCHAR2,
1707 x_msg_count OUT NOCOPY NUMBER,
1708 x_msg_data OUT NOCOPY VARCHAR2
1709 ) IS
1710 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1711 l_rel_flag VARCHAR2(1);
1712 BEGIN
1713
1714 IF l_debug_level > 0 THEN
1715 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Create_relationships ');
1716 END IF;
1717
1718 x_return_status := FND_API.G_RET_STS_SUCCESS;
1719
1720 l_rel_flag := OE_Sys_Parameters.Value('CUSTOMER_RELATIONSHIPS_FLAG');
1721
1722 IF l_rel_flag <> 'Y' THEN
1723 IF l_debug_level > 0 THEN
1724 oe_debug_pub.add(' No need to create relationships.system param is not related customers ');
1725 END IF;
1726 RETURN;
1727 END IF;
1728
1729 IF NVL(p_sold_to_customer_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1730 AND NVL(p_ship_to_customer_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1731 AND p_sold_to_customer_id <> p_ship_to_customer_id
1732 AND NOT check_relation_exists ( p_sold_to_customer_id,p_ship_to_customer_id )
1733 THEN
1734 oe_oe_inline_address.create_cust_relationship( p_cust_acct_id => p_sold_to_customer_id
1735 ,p_related_cust_acct_id => p_ship_to_customer_id
1736 ,p_reciprocal_flag => 'Y'
1737 ,p_created_by_module => G_CREATED_BY_MODULE
1738 ,x_return_status => x_return_status
1739 ,x_msg_count => x_msg_count
1740 ,x_msg_data => x_msg_data);
1741
1742 IF l_debug_level > 0 THEN
1743 oe_debug_pub.add('Ship to customer relation creation');
1744 oe_debug_pub.add('Create_relationships TCA CALL:x_return_status:'||x_return_status);
1745 oe_debug_pub.add('Create_relationships TCA CALL:x_msg_count:'||x_msg_count);
1746 oe_debug_pub.add('Create_relationships TCA CALL:x_msg_data:'||x_msg_data);
1747 END IF;
1748
1749 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1750 RETURN;
1751 END IF;
1752
1753 END IF;
1754
1755 IF NVL(p_sold_to_customer_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1756 AND NVL(p_bill_to_customer_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1757 AND p_sold_to_customer_id <> p_bill_to_customer_id
1758 AND NOT check_relation_exists ( p_sold_to_customer_id,p_bill_to_customer_id )
1759 THEN
1760
1761 oe_oe_inline_address.create_cust_relationship( p_cust_acct_id => p_sold_to_customer_id
1762 ,p_related_cust_acct_id => p_bill_to_customer_id
1763 ,p_reciprocal_flag => 'Y'
1764 ,p_created_by_module => G_CREATED_BY_MODULE
1765 ,x_return_status => x_return_status
1766 ,x_msg_count => x_msg_count
1767 ,x_msg_data => x_msg_data);
1768
1769 IF l_debug_level > 0 THEN
1770 oe_debug_pub.add('Invoice to customer relation creation');
1771 oe_debug_pub.add('Create_relationships TCA CALL:x_return_status:'||x_return_status);
1772 oe_debug_pub.add('Create_relationships TCA CALL:x_msg_count:'||x_msg_count);
1773 oe_debug_pub.add('Create_relationships TCA CALL:x_msg_data:'||x_msg_data);
1774 END IF;
1775
1776 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1777 RETURN;
1778 END IF;
1779
1780 END IF;
1781
1782 IF NVL(p_sold_to_customer_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1783 AND NVL(p_deliver_to_cust_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1784 AND p_sold_to_customer_id <> p_deliver_to_cust_id
1785 AND NOT check_relation_exists ( p_sold_to_customer_id,p_deliver_to_cust_id )
1786 THEN
1787
1788
1789 oe_oe_inline_address.create_cust_relationship( p_cust_acct_id => p_sold_to_customer_id
1790 ,p_related_cust_acct_id => p_deliver_to_cust_id
1791 ,p_reciprocal_flag => 'Y'
1792 ,p_created_by_module => G_CREATED_BY_MODULE
1793 ,x_return_status => x_return_status
1794 ,x_msg_count => x_msg_count
1795 ,x_msg_data => x_msg_data);
1796 IF l_debug_level > 0 THEN
1797 oe_debug_pub.add('Deliver to customer relation creation');
1798 oe_debug_pub.add('Create_relationships TCA CALL:x_return_status:'||x_return_status);
1799 oe_debug_pub.add('Create_relationships TCA CALL:x_msg_count:'||x_msg_count);
1800 oe_debug_pub.add('Create_relationships TCA CALL:x_msg_data:'||x_msg_data);
1801 END IF;
1802
1803
1804 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1805 RETURN;
1806 END IF;
1807
1808 END IF;
1809
1810 IF l_debug_level > 0 THEN
1811 oe_debug_pub.add('Exiting OE_CUSTOMER_INFO_PVT.Create_relationships ');
1812 END IF;
1813
1814 Exception
1815 WHEN OTHERS THEN
1816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1817
1818 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1819 THEN
1820 OE_MSG_PUB.Add_Exc_Msg
1821 ( G_PKG_NAME
1822 , 'Create_relationships'
1823 );
1824 END IF;
1825
1826 IF l_debug_level > 0 THEN
1827 oe_debug_pub.add('Unexpected error in OE_CUSTOMER_INFO_PVT.Create_relationships '||SQLERRM);
1828 END IF;
1829
1830 End Create_relationships;
1831
1832
1833 /* This procedure will create a party site for the passed party_id and the location details*/
1834
1835 Procedure Create_Party_Site
1836 ( p_party_id IN NUMBER,
1837 p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE,
1838 x_location_id OUT NOCOPY NUMBER,
1839 x_party_site_id OUT NOCOPY NUMBER,
1840 x_return_status OUT NOCOPY VARCHAR2,
1841 x_msg_count OUT NOCOPY NUMBER,
1842 x_msg_data OUT NOCOPY VARCHAR2 )IS
1843
1844 l_location_rec HZ_LOCATION_V2PUB.location_rec_type;
1845 l_msg_count number;
1846 l_msg_data Varchar2(4000);
1847 l_return_status Varchar2(1);
1848 l_party_site_number VARCHAR2(360);
1849 x_party_site_number VARCHAR2 (360);
1850 --
1851 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1852 --
1853
1854 BEGIN
1855
1856 x_return_status := FND_API.G_RET_STS_SUCCESS;
1857
1858 IF l_debug_level > 0 THEN
1859 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Create_Party_Site');
1860 END IF;
1861
1862
1863 IF l_debug_level > 0 then
1864 oe_debug_pub.add('Step 1:Create Location');
1865 end if;
1866
1867 oe_oe_inline_address.Create_Location(
1868 p_country => p_address_rec.country,
1869 p_address1 => p_address_rec.address1,
1870 p_address2 => p_address_rec.address2,
1871 p_address3 => p_address_rec.address3,
1872 p_address4 => p_address_rec.address4,
1873 p_city => p_address_rec.city,
1874 p_postal_code => p_address_rec.postal_code,
1875 p_state => p_address_rec.state,
1876 p_province => p_address_rec.province,
1877 p_county => p_address_rec.county,
1878 p_address_style => p_address_rec.address_style ,
1879 p_address_line_phonetic =>p_address_rec.address_line_phonetic,
1880 p_created_by_module => G_CREATED_BY_MODULE,
1881 p_orig_system => p_address_rec.orig_system,
1882 p_orig_system_reference =>p_address_rec.orig_system_reference,
1883 x_location_id => x_location_id,
1884 c_Attribute_Category =>p_address_rec.Attribute_Category,
1885 c_Attribute1 => p_address_rec.Attribute1,
1886 c_Attribute2 => p_address_rec.Attribute2,
1887 c_Attribute3 => p_address_rec.Attribute3,
1888 c_Attribute4 => p_address_rec.Attribute4,
1889 c_Attribute5 => p_address_rec.Attribute5,
1890 c_Attribute6 => p_address_rec.Attribute6,
1891 c_Attribute7 => p_address_rec.Attribute7,
1892 c_Attribute8 => p_address_rec.Attribute8,
1893 c_Attribute9 => p_address_rec.Attribute9,
1894 c_Attribute10 => p_address_rec.Attribute10,
1895 c_Attribute11 => p_address_rec.Attribute11,
1896 c_Attribute12 => p_address_rec.Attribute12,
1897 c_Attribute13 => p_address_rec.Attribute13,
1898 c_Attribute14 => p_address_rec.Attribute14,
1899 c_Attribute15 => p_address_rec.Attribute15,
1900 c_Attribute16 => p_address_rec.Attribute16,
1901 c_Attribute17 => p_address_rec.Attribute17,
1902 c_Attribute18 => p_address_rec.Attribute18,
1903 c_Attribute19 => p_address_rec.Attribute19,
1904 c_Attribute20 => p_address_rec.Attribute20,
1905 c_global_Attribute_Category => p_address_rec.global_Attribute_Category,
1906 c_global_Attribute1 => p_address_rec.global_Attribute1,
1907 c_global_Attribute2 => p_address_rec.global_Attribute2,
1908 c_global_Attribute3 => p_address_rec.global_Attribute3,
1909 c_global_Attribute4 => p_address_rec.global_Attribute4,
1910 c_global_Attribute5 => p_address_rec.global_Attribute5,
1911 c_global_Attribute6 => p_address_rec.global_Attribute6,
1912 c_global_Attribute7 => p_address_rec.global_Attribute7,
1913 c_global_Attribute8 => p_address_rec.global_Attribute8,
1914 c_global_Attribute9 => p_address_rec.global_Attribute9,
1915 c_global_Attribute10 => p_address_rec.global_Attribute10,
1916 c_global_Attribute11 => p_address_rec.global_Attribute11,
1917 c_global_Attribute12 => p_address_rec.global_Attribute12,
1918 c_global_Attribute13 => p_address_rec.global_Attribute13,
1919 c_global_Attribute14 => p_address_rec.global_Attribute14,
1920 c_global_Attribute15 => p_address_rec.global_Attribute15,
1921 c_global_Attribute16 => p_address_rec.global_Attribute16,
1922 c_global_Attribute17 => p_address_rec.global_Attribute17,
1923 c_global_Attribute18 => p_address_rec.global_Attribute18,
1924 c_global_Attribute19 => p_address_rec.global_Attribute19,
1925 c_global_Attribute20 => p_address_rec.global_Attribute20,
1926 x_return_status => x_return_status,
1927 x_msg_count => x_msg_count,
1928 x_msg_data => x_msg_data);
1929
1930 IF l_debug_level > 0 THEN
1931 oe_debug_pub.add('End of Step 1:Create Location :x_return_status:'||x_return_status);
1932 oe_debug_pub.add('End of Step 1:Create Location :x_msg_count:'||x_msg_count);
1933 oe_debug_pub.add('End of Step 1:Create Location :x_msg_data:'||x_msg_data);
1934 oe_debug_pub.add('End of Step 1:Create Location :x_location_id:'||x_location_id);
1935 END IF;
1936
1937 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
1938 return;
1939 END IF;
1940
1941 IF l_debug_level > 0 THEN
1942 oe_debug_pub.add('Step 2:Create party site');
1943 END IF;
1944
1945 IF G_AUTO_SITE_NUMBERING = 'Y' THEN
1946 l_party_site_number := NULL;
1947 ELSE
1948 l_party_site_number := p_address_rec.site_number;
1949 END IF;
1950
1951 oe_oe_inline_address.Create_Party_Site
1952 (
1953 p_party_id => p_party_id,
1954 p_location_id => x_location_id,
1955 p_party_site_number => l_party_site_number,
1956 p_created_by_module => G_CREATED_BY_MODULE,
1957 x_party_site_id => x_party_site_id,
1958 x_party_site_number => x_party_site_number,
1959 x_return_status => x_return_status,
1960 x_msg_count => x_msg_count,
1961 x_msg_data => x_msg_data
1962 );
1963
1964 IF l_debug_level > 0 THEN
1965 oe_debug_pub.add('End of Step 2:Create party_site :x_return_status:'||x_return_status);
1966 oe_debug_pub.add('End of Step 2:Create party_site :x_msg_count:'||x_msg_count);
1967 oe_debug_pub.add('End of Step 2:Create party_site :x_msg_data:'||x_msg_data);
1968 oe_debug_pub.add('End of Step 2:Create party_site :x_party_site_id:'||x_party_site_id);
1969 END IF;
1970
1971
1972 IF l_debug_level > 0 THEN
1973 oe_debug_pub.add('Exiting OE_CUSTOMER_INFO_PVT.Create_Party_Site');
1974 END IF;
1975
1976
1977 EXCEPTION
1978 WHEN OTHERS THEN
1979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1980
1981 IF l_debug_level > 0 THEN
1982 oe_debug_pub.add('Unexpected error in OE_CUSTOMER_INFO_PVT.Create_Party_Site '||SQLERRM);
1983 END IF;
1984
1985 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1986 THEN
1987 OE_MSG_PUB.Add_Exc_Msg
1988 ( G_PKG_NAME
1989 , 'Create_Party_Site'
1990 );
1991 END IF;
1992
1993 END Create_Party_Site;
1994
1995 /* This procedure will create a account site for a customer taking party site as input */
1996
1997 Procedure Create_Cust_Account_Site
1998 ( p_party_site_id IN NUMBER,
1999 p_cust_account_id IN NUMBER,
2000 p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE,
2001 x_cust_account_site_id OUT NOCOPY NUMBER,
2002 x_return_status OUT NOCOPY VARCHAR2,
2003 x_msg_count OUT NOCOPY NUMBER,
2004 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2005
2006 l_location_rec HZ_LOCATION_V2PUB.location_rec_type;
2007 l_msg_count number;
2008 l_msg_data Varchar2(4000);
2009 l_return_status Varchar2(1);
2010 l_party_site_number VARCHAR2(360);
2011 x_party_site_number VARCHAR2 (360);
2012 --
2013 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2014 --
2015
2016 BEGIN
2017
2018 x_return_status := FND_API.G_RET_STS_SUCCESS;
2019
2020 IF l_debug_level > 0 THEN
2021 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Create_Cust_Account_Site');
2022 END IF;
2023
2024 IF l_debug_level > 0 THEN
2025 oe_debug_pub.add('Step 3:Create Account Site');
2026 END IF;
2027
2028 oe_oe_inline_address.Create_Account_Site
2029 (
2030 p_cust_account_id => p_cust_account_id,
2031 p_party_site_id => p_party_site_id,
2032 c_Attribute_Category => p_address_rec.attribute_category,
2033 c_Attribute1 => p_address_rec.attribute1,
2034 c_Attribute2 => p_address_rec.attribute2,
2035 c_Attribute3 => p_address_rec.attribute3,
2036 c_Attribute4 => p_address_rec.attribute4,
2037 c_Attribute5 => p_address_rec.attribute5,
2038 c_Attribute6 => p_address_rec.attribute6,
2039 c_Attribute7 => p_address_rec.attribute7,
2040 c_Attribute8 => p_address_rec.attribute8,
2041 c_Attribute9 => p_address_rec.attribute9,
2042 c_Attribute10 => p_address_rec.attribute10,
2043 c_Attribute11 => p_address_rec.attribute11,
2044 c_Attribute12 => p_address_rec.attribute12,
2045 c_Attribute13 => p_address_rec.attribute13,
2046 c_Attribute14 => p_address_rec.attribute14,
2047 c_Attribute15 => p_address_rec.attribute15,
2048 c_Attribute16 => p_address_rec.attribute16,
2049 c_Attribute17 => p_address_rec.attribute17,
2050 c_Attribute18 => p_address_rec.attribute18,
2051 c_Attribute19 => p_address_rec.attribute19,
2052 c_Attribute20 => p_address_rec.attribute20,
2053 x_customer_site_id => x_cust_account_site_id,
2054 x_return_status => x_return_status,
2055 x_msg_count => x_msg_count,
2056 x_msg_data => x_msg_data,
2057 in_created_by_module => G_CREATED_BY_MODULE
2058 );
2059 IF l_debug_level > 0 THEN
2060 oe_debug_pub.add('End of Step 3:Create Account_Site :x_return_status:'||x_return_status);
2061 oe_debug_pub.add('End of Step 3:Create Account_Site :x_msg_count:'||x_msg_count);
2062 oe_debug_pub.add('End of Step 3:Create Account_Site :x_msg_data:'||x_msg_data);
2063 oe_debug_pub.add('End of Step 3:Create Account_Site :x_cust_account_site_id:'||x_cust_account_site_id);
2064 END IF;
2065
2066 IF l_debug_level > 0 THEN
2067 oe_debug_pub.add('Exiting OE_CUSTOMER_INFO_PVT.Create_Cust_Account_Site');
2068 END IF;
2069
2070
2071 EXCEPTION
2072 WHEN OTHERS THEN
2073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2074
2075 IF l_debug_level > 0 THEN
2076 oe_debug_pub.add('Unexpected error in OE_CUSTOMER_INFO_PVT.Create_Cust_Account_Site '||SQLERRM);
2077 END IF;
2078
2079 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2080 THEN
2081 OE_MSG_PUB.Add_Exc_Msg
2082 ( G_PKG_NAME
2083 , 'Create_Cust_Account_Site'
2084 );
2085 END IF;
2086
2087 END Create_Cust_Account_Site;
2088
2089 /* Create account site usages */
2090
2091 PROCEDURE Create_Acct_Site_Uses
2092 ( p_cust_acct_site_id IN NUMBER,
2093 p_location_number IN VARCHAR2,
2094 p_site_use_code IN VARCHAR2,
2095 x_site_use_id OUT NOCOPY NUMBER,
2096 x_return_status OUT NOCOPY VARCHAR2,
2097 x_msg_count OUT NOCOPY NUMBER ,
2098 x_msg_data OUT NOCOPY VARCHAR2)
2099 IS
2100 l_location_number VARCHAR2(360);
2101 l_acct_site_uses HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
2102 l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
2103 p_org_id NUMBER := MO_GLOBAL.get_current_org_id;
2104 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2105
2106 BEGIN
2107 IF l_debug_level > 0 THEN
2108 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Create_Acct_Site_Uses');
2109 END IF;
2110
2111
2112 l_acct_site_uses.cust_acct_site_id := p_cust_acct_site_id;
2113 l_acct_site_uses.created_by_module := G_CREATED_BY_MODULE;
2114 l_acct_site_uses.application_id := 660;
2115 l_acct_site_uses.org_id := p_org_id;
2116 l_acct_site_uses.site_use_code := p_site_use_code;
2117
2118 IF p_location_number IS NOT NULL THEN
2119 l_acct_site_uses.location := p_location_number;
2120 END IF;
2121
2122
2123 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use
2124 (
2125 p_cust_site_use_rec => l_acct_site_uses,
2126 p_customer_profile_rec => l_cust_profile_rec,
2127 p_create_profile => FND_API.G_FALSE,
2128 x_return_status => x_return_status,
2129 x_msg_count => x_msg_count,
2130 x_msg_data => x_msg_data,
2131 x_site_use_id => x_site_use_id
2132 );
2133
2134 IF l_debug_level > 0 THEN
2135 oe_debug_pub.add('Create_Cust_Site_Use : x_return_status:'||x_return_status);
2136 oe_debug_pub.add('Create_Cust_Site_Use : x_msg_count:'||x_msg_count);
2137 oe_debug_pub.add('Create_Cust_Site_Use : x_msg_data:'||x_msg_data);
2138 oe_debug_pub.add('Create_Cust_Site_Use : x_site_use_id:'||x_site_use_id);
2139 END IF;
2140
2141
2142 IF l_debug_level > 0 THEN
2143 oe_debug_pub.add('Exiting OE_CUSTOMER_INFO_PVT.Create_Acct_Site_Uses');
2144 END IF;
2145
2146 EXCEPTION
2147 WHEN OTHERS THEN
2148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2149
2150 IF l_debug_level > 0 THEN
2151 oe_debug_pub.add('Unexpected error in OE_CUSTOMER_INFO_PVT.Create_Acct_Site_Uses '||SQLERRM);
2152 END IF;
2153
2154 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2155 THEN
2156 OE_MSG_PUB.Add_Exc_Msg
2157 ( G_PKG_NAME
2158 , 'Create_Acct_Site_Uses'
2159 );
2160 END IF;
2161
2162 END Create_Acct_Site_Uses;
2163
2164
2165 /* This procuedre will get all the party sites for the passed party and check whether the site being created
2166 is already there .If found it returns the existing party_site_id */
2167
2168 Procedure Check_Party_Site_Exists( p_party_id IN NUMBER,
2169 p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE,
2170 x_party_site_id OUT NOCOPY NUMBER
2171 ) IS
2172 CURSOR party_location
2173 IS
2174 SELECT party_site_id,location_id
2175 FROM hz_party_sites
2176 WHERE party_id = p_party_id;
2177
2178 l_location_rec HZ_LOCATION_V2PUB.location_rec_type;
2179 l_return_status VARCHAR2(1);
2180 l_msg_count NUMBER;
2181 l_msg_data VARCHAR2(4000);
2182 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2183 BEGIN
2184
2185 x_party_site_id := FND_API.G_MISS_NUM;
2186
2187 IF l_debug_level > 0 THEN
2188 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Check_Party_Site_Exists : p_party_id'||p_party_id);
2189 END IF;
2190
2191
2192 FOR i in party_location LOOP
2193
2194 IF l_debug_level > 0 THEN
2195 oe_debug_pub.add('Party_site_id :'||i.party_site_id);
2196 oe_debug_pub.add('Location_id :'||i.location_id);
2197 END IF;
2198
2199
2200 HZ_LOCATION_V2PUB.get_location_rec(
2201 p_init_msg_list => FND_API.G_TRUE,
2202 p_location_id => i.location_id,
2203 x_location_rec => l_location_rec,
2204 x_return_status => l_return_status,
2205 x_msg_count => l_msg_count,
2206 x_msg_data => l_msg_data
2207 );
2208
2209 IF l_debug_level > 0 THEN
2210 oe_debug_pub.add('l_return_status :'||l_return_status);
2211 oe_debug_pub.add('l_msg_data :'||l_msg_data);
2212 END IF;
2213
2214 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2215 x_party_site_id := FND_API.G_MISS_NUM;
2216 oe_msg_pub.transfer_msg_stack;
2217 RETURN;
2218 END IF;
2219
2220
2221 --Check for match
2222 IF NVL(l_location_rec.country,FND_API.G_MISS_CHAR) = NVL(p_address_rec.country,FND_API.G_MISS_CHAR)
2223 AND NVL(l_location_rec.state,FND_API.G_MISS_CHAR) = NVL(p_address_rec.state,FND_API.G_MISS_CHAR)
2224 AND NVL(l_location_rec.city,FND_API.G_MISS_CHAR) = NVL(p_address_rec.city,FND_API.G_MISS_CHAR)
2225 AND NVL(l_location_rec.county,FND_API.G_MISS_CHAR) = NVL(p_address_rec.county,FND_API.G_MISS_CHAR)
2226 AND NVL(l_location_rec.ADDRESS1,FND_API.G_MISS_CHAR) = NVL(p_address_rec.ADDRESS1,FND_API.G_MISS_CHAR)
2227 AND NVL(l_location_rec.ADDRESS2,FND_API.G_MISS_CHAR) = NVL(p_address_rec.ADDRESS2,FND_API.G_MISS_CHAR)
2228 AND NVL(l_location_rec.ADDRESS3,FND_API.G_MISS_CHAR) = NVL(p_address_rec.ADDRESS3,FND_API.G_MISS_CHAR)
2229 AND NVL(l_location_rec.ADDRESS4,FND_API.G_MISS_CHAR) = NVL(p_address_rec.ADDRESS4,FND_API.G_MISS_CHAR)
2230 THEN
2231
2232 IF l_debug_level > 0 THEN
2233 oe_debug_pub.add(' MATCH FOUND : Party Site Exists');
2234 oe_debug_pub.add(' x_party_site_id:'||i.party_site_id);
2235 oe_debug_pub.add(' Location_id:'||i.location_id);
2236 END IF;
2237
2238 x_party_site_id := i.party_site_id;
2239 RETURN;
2240 END IF;
2241
2242
2243 END LOOP;
2244
2245
2246 IF l_debug_level > 0 THEN
2247 oe_debug_pub.add(' No Match found :x_party_site_id :'||x_party_site_id);
2248 oe_debug_pub.add('Exiting OE_CUSTOMER_INFO_PVT.Check_Party_Site_Exists');
2249 END IF;
2250
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 IF l_debug_level > 0 THEN
2254 oe_debug_pub.add('Other errors in Check_Party_Site_Exists: '||SQLERRM);
2255 END IF;
2256
2257 x_party_site_id := FND_API.G_MISS_NUM;
2258
2259 END Check_Party_Site_Exists;
2260
2261 /*This procedure checks whether a party site has already got a account site */
2262
2263 Procedure Check_Cust_Site_Exists ( p_party_site_id IN NUMBER,
2264 p_cust_account_id IN NUMBER,
2265 x_cust_acct_site_id OUT NOCOPY NUMBER
2266 )IS
2267 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2268 BEGIN
2269
2270
2271 IF l_debug_level > 0 THEN
2272 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Check_Cust_Site_Exists');
2273 END IF;
2274
2275 x_cust_acct_site_id := FND_API.G_MISS_NUM;
2276
2277 SELECT cust_acct_site_id
2278 INTO x_cust_acct_site_id
2279 FROM hz_cust_acct_sites
2280 WHERE party_site_id = p_party_site_id
2281 AND cust_account_id = p_cust_account_id
2282 AND ROWNUM =1;
2283
2284
2285
2286 IF l_debug_level > 0 THEN
2287 oe_debug_pub.add('x_cust_acct_site_id :'||x_cust_acct_site_id);
2288 oe_debug_pub.add('Exiting OE_CUSTOMER_INFO_PVT.Check_Cust_Site_Exists');
2289 END IF;
2290
2291 EXCEPTION
2292 WHEN OTHERS THEN
2293 x_cust_acct_site_id := FND_API.G_MISS_NUM;
2294
2295 IF l_debug_level > 0 THEN
2296 oe_debug_pub.add('Other Errors :'||SQLERRM);
2297 END IF;
2298 END Check_Cust_Site_Exists;
2299
2300 /* This function returns the location_id for the given cust_acct_site_id */
2301 Function Get_Location_id( p_cust_acct_site_id IN NUMBER DEFAULT NULL)
2302 RETURN NUMBER IS
2303 l_location_id NUMBER;
2304 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2305 BEGIN
2306
2307 IF l_debug_level > 0 THEN
2308 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Get_Location_id');
2309 END IF;
2310
2311
2312 SELECT loc.location_id
2313 INTO l_location_id
2314 FROM hz_cust_acct_sites acct_site,
2315 hz_party_sites party_site,
2316 hz_locations loc
2317 WHERE acct_site.party_site_id = party_site.party_site_id
2318 AND loc.location_id = party_site.location_id
2319 AND acct_site.cust_acct_site_id = p_cust_acct_site_id ;
2320
2321
2322 IF l_debug_level > 0 THEN
2323 oe_debug_pub.add('l_location_id :'||l_location_id);
2324 END IF;
2325
2326 RETURN l_location_id;
2327
2328 EXCEPTION
2329 WHEN OTHERS THEN
2330 IF l_debug_level > 0 THEN
2331 oe_debug_pub.add(' OTHER ERROERS IN OE_CUSTOMER_INFO_PVT.Get_Location_id'|| SQLERRM);
2332 END IF;
2333
2334 RETURN NULL;
2335 END Get_Location_id;
2336
2337
2338 /* This procedure is called from Get_Customer_Info_ids to create customer sites and different usages */
2339 Procedure Create_Addresses (
2340 p_customer_info_tbl IN OUT NOCOPY OE_ORDER_PUB.CUSTOMER_INFO_TABLE_TYPE
2341 , p_operation_code IN VARCHAR2
2342 , p_sold_to_customer_id IN NUMBER
2343 , p_ship_to_customer_id IN NUMBER
2344 , p_bill_to_customer_id IN NUMBER
2345 , p_deliver_to_customer_id IN NUMBER
2346 , p_customer_id IN NUMBER
2347 , p_address_rec_index IN NUMBER
2348 , p_address_usage IN VARCHAR2
2349 , x_ship_to_org_id IN OUT NOCOPY NUMBER
2350 , x_invoice_to_org_id IN OUT NOCOPY NUMBER
2351 , x_deliver_to_org_id IN OUT NOCOPY NUMBER
2352 , x_sold_to_site_use_id IN OUT NOCOPY NUMBER
2353 , x_return_status OUT NOCOPY VARCHAR2
2354 , x_msg_count OUT NOCOPY NUMBER
2355 , x_msg_data OUT NOCOPY VARCHAR2
2356 ) IS
2357
2358
2359 l_addr_rec OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE;
2360
2361 l_ship_addr_rec_index NUMBER;
2362 l_bill_addr_rec_index NUMBER;
2363 l_deliver_addr_rec_index NUMBER;
2364
2365
2366 x_ship_account_site_id NUMBER;
2367 x_bill_account_site_id NUMBER;
2368 x_deliver_account_site_id NUMBER;
2369
2370
2371 x_location_id NUMBER;
2372 x_party_site_id NUMBER;
2373 x_party_site_number VARCHAR2(360);
2374 x_cust_account_site_id NUMBER;
2375 x_site_use_id NUMBER;
2376
2377 l_location_number VARCHAR2(360);
2378
2379 l_party_id NUMBER;
2380 x_customer_id NUMBER;
2381 l_customer_id NUMBER;
2382
2383 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2384 l_site_customer_id NUMBER;
2385 x_account_site_id NUMBER;
2386
2387 BEGIN
2388
2389 x_return_status := FND_API.G_RET_STS_SUCCESS;
2390
2391 IF l_debug_level > 0 THEN
2392 oe_debug_pub.add('Entering procedure OE_CUSTOMER_INFO_PVT.Create_Addresses');
2393 oe_debug_pub.add('p_address_usage:'||p_address_usage);
2394 END IF;
2395
2396 l_addr_rec := p_customer_info_tbl(p_address_rec_index);
2397
2398
2399 IF p_address_usage = 'SHIP_TO' THEN
2400 l_site_customer_id := p_ship_to_customer_id;
2401 ELSIF p_address_usage ='BILL_TO' THEN
2402 l_site_customer_id := p_bill_to_customer_id ;
2403 ELSIF p_address_usage = 'DELIVER_TO' THEN
2404 l_site_customer_id := p_deliver_to_customer_id;
2405 ELSE
2406 l_site_customer_id := p_customer_id;
2407 END IF;
2408
2409 IF NVL( l_addr_rec.site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
2410
2411
2412 Value_To_Id_Address ( p_address_record => l_addr_rec
2413 , p_sold_to_org_id => p_sold_to_customer_id
2414 , p_site_customer_id => l_site_customer_id
2415 , p_site_usage => p_address_usage
2416 , x_site_usage_id => x_site_use_id);
2417
2418 IF x_site_use_id = FND_API.G_MISS_NUM THEN
2419 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2420 ELSE
2421 IF p_address_usage = 'SHIP_TO' THEN
2422 x_ship_to_org_id := x_site_use_id;
2423 ELSIF p_address_usage ='BILL_TO' THEN
2424 x_invoice_to_org_id := x_site_use_id;
2425 ELSIF p_address_usage ='DELIVER_TO' THEN
2426 x_deliver_to_org_id := x_site_use_id;
2427 ELSE
2428 x_sold_to_site_use_id := x_site_use_id;
2429 END IF ;
2430
2431 END IF;
2432 ELSE
2433
2434 --Update Address logic to go here
2435 IF IS_BOTH_ID_VAL_PASSED( p_address_rec => l_addr_rec )
2436 AND OE_GLOBALS.G_UPDATE_ON_ID
2437 THEN
2438 Update_Location (p_address_rec => l_addr_rec
2439 , p_site_use_id => l_addr_rec.site_use_id
2440 , p_site_usage_code => p_address_usage
2441 , x_return_status =>x_return_status
2442 , x_msg_count => x_msg_count
2443 , x_msg_data => x_msg_data
2444 );
2445 END IF;
2446
2447 IF l_debug_level > 0 THEN
2448 oe_debug_pub.add('Update_Location :x_return_status'||x_return_status);
2449 oe_debug_pub.add('Update_Location :x_msg_count'||x_msg_count);
2450 oe_debug_pub.add('Update_Location :x_msg_data'||x_msg_data);
2451 END IF;
2452
2453 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2454 oe_msg_pub.transfer_msg_stack;
2455 RETURN;
2456 END IF;
2457
2458 x_site_use_id := l_addr_rec.site_use_id;
2459
2460 IF p_address_usage = 'SHIP_TO' THEN
2461 x_ship_to_org_id := x_site_use_id;
2462 ELSIF p_address_usage ='BILL_TO' THEN
2463 x_invoice_to_org_id := x_site_use_id;
2464 ELSIF p_address_usage ='DELIVER_TO' THEN
2465 x_deliver_to_org_id := x_site_use_id;
2466 ELSE
2467 x_sold_to_site_use_id := x_site_use_id;
2468 END IF ;
2469
2470
2471
2472 END IF;
2473
2474 IF G_AUTO_LOCATION_NUMBERING = 'Y' THEN
2475 l_location_number := NULL;
2476 ELSE
2477 l_location_number := l_addr_rec.location_number;
2478 END IF;
2479
2480 IF NVL(x_site_use_id ,FND_API.G_MISS_NUM ) = FND_API.G_MISS_NUM THEN
2481 Check_Duplicate_Address ( p_address_record => l_addr_rec
2482 , p_sold_to_org_id => p_sold_to_customer_id
2483 , p_site_customer_id => l_site_customer_id
2484 , p_site_usage => p_address_usage
2485 , x_site_usage_id => x_site_use_id);
2486
2487 IF x_site_use_id = FND_API.G_MISS_NUM THEN
2488 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2489 ELSE
2490 IF p_address_usage = 'SHIP_TO' THEN
2491 x_ship_to_org_id := x_site_use_id;
2492 ELSIF p_address_usage ='BILL_TO' THEN
2493 x_invoice_to_org_id := x_site_use_id;
2494 ELSIF p_address_usage ='DELIVER_TO' THEN
2495 x_deliver_to_org_id := x_site_use_id;
2496 ELSE
2497 x_sold_to_site_use_id := x_site_use_id;
2498 END IF ;
2499
2500 END IF;
2501 END IF;
2502
2503
2504 IF NVL(x_site_use_id ,FND_API.G_MISS_NUM ) = FND_API.G_MISS_NUM THEN
2505
2506 Check_Party_Site_Exists( p_party_id => Get_Party_ID(p_customer_id),
2507 p_address_rec =>l_addr_rec,
2508 x_party_site_id => x_party_site_id
2509 );
2510
2511 IF x_party_site_id <> FND_API.G_MISS_NUM THEN
2512
2513 Check_Cust_Site_Exists ( p_party_site_id =>x_party_site_id,
2514 p_cust_account_id =>p_customer_id,
2515 x_cust_acct_site_id =>x_account_site_id
2516 );
2517 END IF;
2518
2519
2520 IF x_account_site_id <> FND_API.G_MISS_NUM THEN
2521
2522 IF l_debug_level > 0 THEN
2523 oe_debug_pub.add('This site is aleary created ,so check create site usage alone');
2524 END IF;
2525
2526
2527 Check_site_usage_exists ( p_cust_acct_site_id => x_account_site_id
2528 ,p_site_usage => p_address_usage
2529 ,x_site_use_id => x_site_use_id);
2530
2531 IF x_site_use_id <> FND_API.G_MISS_NUM THEN
2532 IF l_debug_level > 0 THEN
2533 oe_debug_pub.add(p_address_usage||': Site Usage already exists ');
2534 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses:x_site_use_id'||x_site_use_id);
2535 END IF;
2536 ELSE
2537
2538 Create_Acct_Site_Uses(
2539 p_cust_acct_site_id => x_account_site_id,
2540 p_location_number => l_location_number,
2541 p_site_use_code => p_address_usage,
2542 x_site_use_id => x_site_use_id ,
2543 x_return_status => x_return_status,
2544 x_msg_count => x_msg_count,
2545 x_msg_data => x_msg_data );
2546
2547 END IF;
2548
2549 IF l_debug_level > 0 THEN
2550 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses:x_return_status:'||x_return_status);
2551 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses: x_msg_count :'||x_return_status);
2552 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses: x_msg_data'||x_msg_data);
2553 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses:x_site_use_id'||x_site_use_id);
2554 END IF;
2555
2556 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2557 oe_msg_pub.transfer_msg_stack;
2558 RETURN;
2559 END IF;
2560
2561 ELSE
2562 IF l_debug_level > 0 THEN
2563 oe_debug_pub.add('Create both site and site usage');
2564 END IF;
2565
2566
2567 IF x_party_site_id = FND_API.G_MISS_NUM THEN
2568 Create_Party_Site
2569 ( p_party_id => Get_Party_ID(p_customer_id),
2570 p_address_rec => l_addr_rec,
2571 x_location_id => x_location_id,
2572 x_party_site_id => x_party_site_id,
2573 x_return_status =>x_return_status,
2574 x_msg_count =>x_msg_count,
2575 x_msg_data =>x_msg_data
2576 );
2577
2578 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2579 oe_msg_pub.transfer_msg_stack;
2580 RETURN;
2581 END IF;
2582
2583 END IF;
2584
2585 IF l_debug_level > 0 THEN
2586 oe_debug_pub.add(p_address_usage||': Create_Party_Site:x_return_status:'||x_return_status);
2587 oe_debug_pub.add(p_address_usage||': Create_Party_Site: x_msg_count :'||x_msg_count);
2588 oe_debug_pub.add(p_address_usage||': Create_Party_Site: x_msg_data'||x_msg_data);
2589 oe_debug_pub.add(p_address_usage||': Create_Party_Site:x_party_site_id'||x_party_site_id);
2590 oe_debug_pub.add(p_address_usage||': Create_Party_Site:x_location_id'||x_location_id);
2591 END IF;
2592
2593
2594
2595 Create_Cust_Account_Site
2596 ( p_party_site_id =>x_party_site_id,
2597 p_cust_account_id =>p_customer_id,
2598 p_address_rec => l_addr_rec,
2599 x_cust_account_site_id =>x_account_site_id,
2600 x_return_status =>x_return_status,
2601 x_msg_count =>x_msg_count,
2602 x_msg_data =>x_msg_data
2603 );
2604
2605
2606 IF l_debug_level > 0 THEN
2607 oe_debug_pub.add(p_address_usage||': Create_Cust_Account_Site:x_return_status:'||x_return_status);
2608 oe_debug_pub.add(p_address_usage||': Create_Cust_Account_Site: x_msg_count :'||x_msg_count);
2609 oe_debug_pub.add(p_address_usage||': Create_Cust_Account_Site: x_msg_data'||x_msg_data);
2610 oe_debug_pub.add(p_address_usage||': Create_Cust_Account_Site:x_ship_account_site_id'||x_ship_account_site_id);
2611 END IF;
2612
2613 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2614 oe_msg_pub.transfer_msg_stack;
2615 RETURN;
2616 END IF;
2617
2618 Create_Acct_Site_Uses(
2619 p_cust_acct_site_id => x_account_site_id ,
2620 p_location_number => l_location_number,
2621 p_site_use_code => p_address_usage,
2622 x_site_use_id => x_site_use_id ,
2623 x_return_status => x_return_status,
2624 x_msg_count => x_msg_count,
2625 x_msg_data => x_msg_data );
2626
2627
2628 IF l_debug_level > 0 THEN
2629 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses:x_return_status:'||x_return_status);
2630 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses: x_msg_count :'||x_return_status);
2631 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses: x_msg_data'||x_msg_data);
2632 oe_debug_pub.add(p_address_usage||': Create_Acct_Site_Uses:x_site_use_id'||x_site_use_id);
2633 END IF;
2634
2635 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2636 oe_msg_pub.transfer_msg_stack;
2637 RETURN;
2638 END IF;
2639
2640
2641 END IF;
2642 IF p_address_usage = 'SHIP_TO' THEN
2643 x_ship_to_org_id := x_site_use_id;
2644 ELSIF p_address_usage ='BILL_TO' THEN
2645 x_invoice_to_org_id := x_site_use_id;
2646 ELSIF p_address_usage ='DELIVER_TO' THEN
2647 x_deliver_to_org_id := x_site_use_id;
2648 ELSE
2649 x_sold_to_site_use_id := x_site_use_id;
2650 END IF ;
2651 ELSE
2652 IF p_address_usage = 'SHIP_TO' THEN
2653 x_ship_to_org_id := x_site_use_id;
2654 ELSIF p_address_usage ='BILL_TO' THEN
2655 x_invoice_to_org_id := x_site_use_id;
2656 ELSIF p_address_usage ='DELIVER_TO' THEN
2657 x_deliver_to_org_id := x_site_use_id;
2658 ELSE
2659 x_sold_to_site_use_id := x_site_use_id;
2660 END IF ;
2661 END IF;
2662
2663
2664
2665 IF l_debug_level > 0 THEN
2666 oe_debug_pub.add('Exiting procedure OE_CUSTOMER_INFO_PVT.Create_Addresses');
2667 END IF;
2668
2669 EXCEPTION
2670 WHEN OTHERS THEN
2671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2672
2673 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2674 THEN
2675 OE_MSG_PUB.Add_Exc_Msg
2676 ( G_PKG_NAME
2677 , 'Create_Addresses'
2678 );
2679 END IF;
2680
2681 END Create_Addresses;
2682
2683
2684 Procedure Check_Customer_Fields (p_customer_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
2685 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
2686 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2687 BEGIN
2688
2689 x_return_status := FND_API.G_RET_STS_SUCCESS;
2690
2691 IF p_customer_record.customer_type IS NULL THEN
2692 fnd_message.set_name('ONT','ONT_PO_INL_REQD');
2693 fnd_message.set_token('API_NAME', 'CREATE_ACCOUNT');
2694 fnd_message.set_token('FIELD_REQD', 'CUSTOMER_TYPE');
2695 oe_msg_pub.add;
2696 x_return_status := FND_API.G_RET_STS_ERROR;
2697
2698 IF l_debug_level > 0 THEN
2699 oe_debug_pub.add( 'CUSTOMER TYPE REQUIRED BUT NOT ENTERED' ) ;
2700 END IF;
2701
2702
2703 END IF;
2704
2705 IF ( p_customer_record.customer_type = 'ORGANIZATION'
2706 AND p_customer_record.organization_name IS NULL
2707 )
2708 THEN
2709 fnd_message.set_name('ONT','ONT_PO_INL_REQD');
2710 fnd_message.set_token('API_NAME', 'CREATE_ACCOUNT');
2711 fnd_message.set_token('FIELD_REQD', 'ORGANIZATION_NAME');
2712 oe_msg_pub.add;
2713 x_return_status := FND_API.G_RET_STS_ERROR;
2714
2715 IF l_debug_level > 0 THEN
2716 oe_debug_pub.add( 'ORGANIZATION NAME REQUIRED BUT NOT ENTERED' ) ;
2717 END IF;
2718
2719
2720 END IF;
2721
2722
2723 IF ( p_customer_record.customer_type = 'PERSON'
2724 AND p_customer_record.person_first_name IS NULL
2725 AND p_customer_record.person_last_name IS NULL
2726 )
2727 THEN
2728 fnd_message.set_name('ONT','ONT_PO_INL_REQD');
2729 fnd_message.set_token('API_NAME', 'CREATE_ACCOUNT');
2730 fnd_message.set_token('FIELD_REQD', 'PERSON_FIRST_NAME');
2731 oe_msg_pub.add;
2732 x_return_status := FND_API.G_RET_STS_ERROR;
2733
2734 IF l_debug_level > 0 THEN
2735 oe_debug_pub.add( 'PERSON FIRST/LAST NAME REQUIRED BUT NOT ENTERED' ) ;
2736 END IF;
2737
2738
2739 END IF;
2740
2741
2742 IF G_EMAIL_REQUIRED = 'Y' and
2743 p_customer_record.email_address is NULL THEN
2744 fnd_message.set_name('ONT','ONT_PO_INL_REQD');
2745 fnd_message.set_token('API_NAME', 'CREATE_ACCOUNT');
2746 fnd_message.set_token('FIELD_REQD', 'EMAIL_ADDRESS');
2747 oe_msg_pub.add;
2748 x_return_status := FND_API.G_RET_STS_ERROR;
2749
2750 IF l_debug_level > 0 THEN
2751 oe_debug_pub.add( 'EMAIL REQUIRED BUT NOT ENTERED' ) ;
2752 END IF;
2753 END IF;
2754
2755 IF G_AUTO_PARTY_NUMBERING = 'N' and
2756 p_customer_record.party_number IS NULL Then
2757
2758 fnd_message.set_name('ONT','ONT_PO_INL_REQD');
2759 fnd_message.set_token('API_NAME', 'CREATE_ACCOUNT');
2760 fnd_message.set_token('FIELD_REQD', 'PARTY_NUMBER');
2761 oe_msg_pub.add;
2762 x_return_status := FND_API.G_RET_STS_ERROR;
2763
2764 IF l_debug_level > 0 THEN
2765 oe_debug_pub.add( 'PARTY NUMBER REQUIRED BUT NOT ENTERED' ) ;
2766 END IF;
2767
2768 End If;
2769
2770 IF G_AUTO_CUST_NUMBERING = 'N' and
2771 p_customer_record.customer_number IS NULL THEN
2772
2773 fnd_message.set_name('ONT','ONT_PO_INL_REQD');
2774 fnd_message.set_token('API_NAME', 'CREATE_ACCOUNT');
2775 fnd_message.set_token('FIELD_REQD', 'CUSTOMER_NUMBER');
2776 oe_msg_pub.add;
2777 x_return_status := FND_API.G_RET_STS_ERROR;
2778
2779 IF l_debug_level > 0 THEN
2780 oe_debug_pub.add( 'CUSTOMER NUMBER REQUIRED BUT NOT ENTERED' ) ;
2781 END IF;
2782
2783 End If;
2784
2785 EXCEPTION
2786 WHEN OTHERS THEN
2787 NULL;
2788 END Check_Customer_Fields;
2789
2790 Procedure Validate_Customer_Fields (p_customer_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
2791 ,x_return_status OUT NOCOPY VARCHAR2 )IS
2792 x_party_id NUMBER;
2793 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2794
2795 BEGIN
2796 x_return_status := FND_API.G_RET_STS_SUCCESS;
2797
2798 IF p_customer_record.customer_type NOT IN ('ORGANIZATION','PERSON') THEN
2799 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
2800 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','PARTY_ID');
2801 OE_MSG_PUB.Add;
2802 x_return_status := FND_API.G_RET_STS_ERROR;
2803 END IF;
2804
2805 EXCEPTION
2806 WHEN OTHERS THEN
2807 NULL;
2808 END Validate_Customer_Fields;
2809
2810
2811 Procedure Check_Duplicate_Customer ( p_customer_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
2812 , p_type IN VARCHAR2
2813 , x_customer_id OUT NOCOPY VARCHAR2
2814 ) IS
2815 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2816 BEGIN
2817
2818 IF l_debug_level >0 THEN
2819 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Check_Duplicate_Customer');
2820 END IF;
2821 x_customer_id := FND_API.G_MISS_NUM;
2822
2823 IF p_customer_record.customer_type = 'ORGANIZATION' THEN
2824 IF p_type = 'SOLD_TO' THEN
2825 x_customer_id := Oe_value_to_id.sold_to_org(
2826 p_sold_to_org => p_customer_record.organization_name,
2827 p_customer_number => p_customer_record.customer_number);
2828 ELSE
2829 x_customer_id := Oe_value_to_id.site_customer(
2830 p_site_customer => p_customer_record.organization_name
2831 , p_site_customer_number => p_customer_record.customer_number
2832 , p_type =>p_type );
2833
2834
2835 END IF;
2836 ELSIF p_customer_record.customer_type = 'PERSON' THEN
2837 IF p_type = 'SOLD_TO' THEN
2838 x_customer_id := oe_value_to_id.sold_to_org(
2839 p_sold_to_org => p_customer_record.person_first_name || ' ' ||
2840 p_customer_record.person_last_name,
2841 p_customer_number => p_customer_record.customer_number);
2842 ELSE
2843 x_customer_id := Oe_value_to_id.site_customer(
2844 p_site_customer => p_customer_record.person_first_name || ' ' ||
2845 p_customer_record.person_last_name
2846 , p_site_customer_number => p_customer_record.customer_number
2847 , p_type => p_type );
2848 END IF;
2849 END IF;
2850
2851
2852 IF l_debug_level >0 THEN
2853 oe_debug_pub.add('Exiting OE_CUSTOMER_INFO_PVT.Check_Duplicate_Customer :'||x_customer_id);
2854 END IF;
2855
2856 EXCEPTION
2857 WHEN OTHERS THEN
2858 IF l_debug_level >0 THEN
2859 oe_debug_pub.add('Other errors in OE_CUSTOMER_INFO_PVT.Check_Duplicate_Customer :'||SQLERRM);
2860 END IF;
2861
2862 END Check_Duplicate_Customer;
2863
2864
2865
2866
2867 Procedure Check_Address_Fields (p_address_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
2868 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
2869 BEGIN
2870 NULL;
2871 EXCEPTION
2872 WHEN OTHERS THEN
2873 NULL;
2874 END Check_Address_Fields;
2875
2876
2877 Procedure Validate_Address_Fields (p_address_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
2878 ,x_return_status OUT NOCOPY VARCHAR2 )IS
2879 BEGIN
2880
2881 NULL;
2882 EXCEPTION
2883 WHEN OTHERS THEN
2884 NULL;
2885 END Validate_Address_Fields;
2886
2887
2888 Procedure Check_Duplicate_Address ( p_address_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
2889 , p_sold_to_org_id IN NUMBER
2890 , p_site_customer_id IN NUMBER
2891 , p_site_usage IN VARCHAR2
2892 , x_site_usage_id OUT NOCOPY NUMBER
2893 )IS
2894 l_address_validation VARCHAR2(1);
2895 l_site_customer_id NUMBER;
2896 BEGIN
2897 l_address_validation := OE_Sys_Parameters.VALUE('OE_ADDR_VALID_OIMP');
2898
2899
2900 IF l_address_validation = 'S' THEN
2901 l_site_customer_id := p_sold_to_org_id;
2902 ELSIF l_address_validation ='R' THEN
2903 l_site_customer_id := p_site_customer_id;
2904 ELSE
2905 x_site_usage_id := NULL;
2906 RETURN;
2907 END IF;
2908
2909
2910
2911 IF p_site_usage = 'SHIP_TO' THEN
2912
2913 x_site_usage_id := Oe_Value_To_Id.Ship_To_Org(
2914 p_ship_to_address1 => p_address_record.address1,
2915 p_ship_to_address2 => p_address_record.address2,
2916 p_ship_to_address3 => p_address_record.address3,
2917 p_ship_to_address4 => p_address_record.address4,
2918 p_ship_to_location => p_address_record.location_number,
2919 p_ship_to_city => p_address_record.city,
2920 p_ship_to_state => p_address_record.state,
2921 p_ship_to_postal_code => p_address_record.postal_code,
2922 p_ship_to_country => p_address_record.country,
2923 p_ship_to_org => p_address_record.location_number,
2924 p_sold_to_org_id => p_sold_to_org_id,
2925 p_ship_to_customer_id =>l_site_customer_id);
2926
2927 ELSIF p_site_usage = 'BILL_TO' Then
2928
2929 x_site_usage_id := Oe_Value_To_Id.Invoice_To_Org(
2930 p_invoice_to_address1 => p_address_record.address1,
2931 p_invoice_to_address2 => p_address_record.address2,
2932 p_invoice_to_address3 => p_address_record.address3,
2933 p_invoice_to_address4 => p_address_record.address4,
2934 p_invoice_to_location => p_address_record.location_number,
2935 p_invoice_to_city => p_address_record.city,
2936 p_invoice_to_state => p_address_record.state,
2937 p_invoice_to_postal_code => p_address_record.postal_code,
2938 p_invoice_to_country => p_address_record.country,
2939 p_invoice_to_org => p_address_record.location_number,
2940 p_sold_to_org_id => p_sold_to_org_id,
2941 p_invoice_to_customer_id => l_site_customer_id);
2942 ELSIF p_site_usage = 'DELIVER_TO' THEN
2943 x_site_usage_id := Oe_Value_To_Id.Deliver_To_Org(
2944 p_deliver_to_address1 => p_address_record.address1,
2945 p_deliver_to_address2 => p_address_record.address2,
2946 p_deliver_to_address3 => p_address_record.address3,
2947 p_deliver_to_address4 => p_address_record.address4,
2948 p_deliver_to_location => p_address_record.location_number,
2949 p_deliver_to_city => p_address_record.city,
2950 p_deliver_to_state => p_address_record.state,
2951 p_deliver_to_postal_code => p_address_record.postal_code,
2952 p_deliver_to_country => p_address_record.country,
2953 p_deliver_to_org => p_address_record.location_number,
2954 p_sold_to_org_id => p_sold_to_org_id,
2955 p_deliver_to_customer_id => l_site_customer_id);
2956 ELSE
2957 x_site_usage_id := Oe_Value_To_Id.Customer_Location
2958 ( p_sold_to_location_address1 =>p_address_record.address1
2959 , p_sold_to_location_address2 =>p_address_record.address2
2960 , p_sold_to_location_address3 =>p_address_record.address3
2961 , p_sold_to_location_address4 =>p_address_record.address4
2962 , p_sold_to_location =>p_address_record.location_number
2963 , p_sold_to_location_city =>p_address_record.city
2964 , p_sold_to_location_state =>p_address_record.state
2965 , p_sold_to_location_postal_code =>p_address_record.postal_code
2966 , p_sold_to_location_country =>p_address_record.country
2967 , p_sold_to_org_id =>p_sold_to_org_id
2968 );
2969
2970
2971 END IF;
2972
2973
2974 EXCEPTION
2975 WHEN OTHERS THEN
2976 x_site_usage_id := FND_API.G_MISS_NUM;
2977 END Check_Duplicate_Address;
2978
2979 Procedure Value_To_Id_Address ( p_address_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
2980 , p_sold_to_org_id IN NUMBER
2981 , p_site_customer_id IN NUMBER
2982 , p_site_usage IN VARCHAR2
2983 , x_site_usage_id OUT NOCOPY NUMBER
2984 ) IS
2985 BEGIN
2986
2987 IF p_site_usage = 'SHIP_TO' THEN
2988
2989 x_site_usage_id := Oe_Value_To_Id.Ship_To_Org(
2990 p_ship_to_address1 => p_address_record.address1,
2991 p_ship_to_address2 => p_address_record.address2,
2992 p_ship_to_address3 => p_address_record.address3,
2993 p_ship_to_address4 => p_address_record.address4,
2994 p_ship_to_location => p_address_record.location_number,
2995 p_ship_to_city => p_address_record.city,
2996 p_ship_to_state => p_address_record.state,
2997 p_ship_to_postal_code => p_address_record.postal_code,
2998 p_ship_to_country => p_address_record.country,
2999 p_ship_to_org => p_address_record.location_number,
3000 p_sold_to_org_id => p_sold_to_org_id,
3001 p_ship_to_customer_id =>p_site_customer_id);
3002
3003 ELSIF p_site_usage = 'BILL_TO' Then
3004
3005 x_site_usage_id := Oe_Value_To_Id.Invoice_To_Org(
3006 p_invoice_to_address1 => p_address_record.address1,
3007 p_invoice_to_address2 => p_address_record.address2,
3008 p_invoice_to_address3 => p_address_record.address3,
3009 p_invoice_to_address4 => p_address_record.address4,
3010 p_invoice_to_location => p_address_record.location_number,
3011 p_invoice_to_city => p_address_record.city,
3012 p_invoice_to_state => p_address_record.state,
3013 p_invoice_to_postal_code => p_address_record.postal_code,
3014 p_invoice_to_country => p_address_record.country,
3015 p_invoice_to_org => p_address_record.location_number,
3016 p_sold_to_org_id => p_sold_to_org_id,
3017 p_invoice_to_customer_id => p_site_customer_id);
3018 ELSIF p_site_usage = 'DELIVER_TO' THEN
3019
3020 x_site_usage_id := Oe_Value_To_Id.Deliver_To_Org(
3021 p_deliver_to_address1 => p_address_record.address1,
3022 p_deliver_to_address2 => p_address_record.address2,
3023 p_deliver_to_address3 => p_address_record.address3,
3024 p_deliver_to_address4 => p_address_record.address4,
3025 p_deliver_to_location => p_address_record.location_number,
3026 p_deliver_to_city => p_address_record.city,
3027 p_deliver_to_state => p_address_record.state,
3028 p_deliver_to_postal_code => p_address_record.postal_code,
3029 p_deliver_to_country => p_address_record.country,
3030 p_deliver_to_org => p_address_record.location_number,
3031 p_sold_to_org_id => p_sold_to_org_id,
3032 p_deliver_to_customer_id => p_site_customer_id);
3033 ELSE
3034 x_site_usage_id := Oe_Value_To_Id.Customer_Location
3035 ( p_sold_to_location_address1 =>p_address_record.address1
3036 , p_sold_to_location_address2 =>p_address_record.address2
3037 , p_sold_to_location_address3 =>p_address_record.address3
3038 , p_sold_to_location_address4 =>p_address_record.address4
3039 , p_sold_to_location =>p_address_record.location_number
3040 , p_sold_to_location_city =>p_address_record.city
3041 , p_sold_to_location_state =>p_address_record.state
3042 , p_sold_to_location_postal_code =>p_address_record.postal_code
3043 , p_sold_to_location_country =>p_address_record.country
3044 , p_sold_to_org_id =>p_sold_to_org_id
3045 );
3046
3047 END IF;
3048
3049 EXCEPTION
3050 WHEN OTHERS THEN
3051 x_site_usage_id := FND_API.G_MISS_NUM;
3052 END Value_To_Id_Address;
3053
3054 Procedure Check_Contact_Fields (p_contact_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
3055 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
3056 BEGIN
3057
3058 x_return_status := FND_API.G_RET_STS_SUCCESS;
3059
3060 IF p_contact_record.person_first_name IS NULL
3061 AND p_contact_record.person_last_name IS NULL
3062
3063 THEN
3064 fnd_message.set_name('ONT','ONT_PO_INL_REQD');
3065 fnd_message.set_token('API_NAME', 'CREATE_CONTACT');
3066 fnd_message.set_token('FIELD_REQD', 'PERSON_FIRST_NAME');
3067 oe_msg_pub.add;
3068 x_return_status := FND_API.G_RET_STS_ERROR;
3069
3070 END IF;
3071
3072 EXCEPTION
3073 WHEN OTHERS THEN
3074 NULL;
3075 END Check_Contact_Fields;
3076
3077
3078 Procedure Validate_Contact_Fields (p_contact_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
3079 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
3080 BEGIN
3081
3082 x_return_status := FND_API.G_RET_STS_SUCCESS;
3083
3084 IF G_AUTO_CONTACT_NUMBERING = 'N' THEN
3085 IF p_contact_record.contact_number IS NULL THEN
3086 fnd_message.set_name('ONT','ONT_PO_INL_REQD');
3087 fnd_message.set_token('API_NAME', 'CREATE_CONTACT');
3088 fnd_message.set_token('FIELD_REQD', 'CONTACT_NUMBER');
3089 oe_msg_pub.add;
3090 x_return_status := FND_API.G_RET_STS_ERROR;
3091 END IF;
3092 END IF;
3093
3094 EXCEPTION
3095 WHEN OTHERS THEN
3096 NULL;
3097 END Validate_Contact_Fields;
3098
3099
3100 Function Get_Party_ID ( p_cust_acct_id IN NUMBER)
3101 RETURN NUMBER IS
3102 l_party_id NUMBER;
3103 BEGIN
3104
3105 select party_id
3106 into l_party_id
3107 from hz_cust_accounts
3108 where cust_account_id = p_cust_acct_id;
3109
3110 RETURN l_party_id;
3111
3112 EXCEPTION
3113 WHEN OTHERS THEN
3114 RETURN FND_API.G_MISS_NUM;
3115 END Get_Party_ID;
3116
3117 /* This procedure is used to update a address location.This will be called from create_address procedure if
3118 bot ID and Value are passed for address related fields and OE_GLOBALS.G_UPDATE_ON_ID is TRUE
3119 */
3120
3121 Procedure Update_Location ( p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
3122 , p_site_use_id IN NUMBER
3123 , p_site_usage_code IN VARCHAR2
3124 , x_return_status OUT NOCOPY VARCHAR2
3125 , x_msg_count OUT NOCOPY NUMBER
3126 , x_msg_data OUT NOCOPY VARCHAR2
3127 ) IS
3128 l_location_rec HZ_LOCATION_V2PUB.LOCATION_REC_TYPE;
3129 l_location_id NUMBER;
3130 x_ver_number NUMBER;
3131 BEGIN
3132
3133 x_return_status := FND_API.G_RET_STS_SUCCESS;
3134
3135 l_location_id := Get_Location_id ( p_site_usage_code => p_site_usage_code
3136 , p_site_use_id => p_site_use_id
3137 );
3138
3139 IF l_location_id <> FND_API.G_MISS_NUM THEN
3140 l_location_rec.location_id := l_location_id;
3141 l_location_rec.address1 := p_address_rec.address1;
3142 l_location_rec.address2 := p_address_rec.address2;
3143 l_location_rec.address3 := p_address_rec.address3;
3144 l_location_rec.address4 := p_address_rec.address4;
3145 l_location_rec.city := p_address_rec.city;
3146 l_location_rec.state := p_address_rec.state;
3147 l_location_rec.postal_code := p_address_rec.postal_code;
3148 l_location_rec.country := p_address_rec.country;
3149
3150 x_ver_number := Get_obj_version_number(p_location_id => l_location_id);
3151
3152 HZ_LOCATION_V2PUB.UPDATE_LOCATION (
3153 p_init_msg_list => FND_API.G_TRUE,
3154 p_location_rec => l_location_rec,
3155 p_object_version_number => x_ver_number,
3156 x_return_status =>x_return_status,
3157 x_msg_count =>x_msg_count,
3158 x_msg_data =>x_msg_data
3159 );
3160
3161 ELSE
3162 x_return_status := FND_API.G_RET_STS_ERROR;
3163 END IF;
3164
3165 EXCEPTION
3166 WHEN OTHERS THEN
3167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3168
3169 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3170 THEN
3171 OE_MSG_PUB.Add_Exc_Msg
3172 ( G_PKG_NAME
3173 , 'Update_Location'
3174 );
3175 END IF;
3176
3177 END Update_Location;
3178
3179 /* This function returns the current object version number
3180 which will be passed to the TCA APIS in case of updates*/
3181
3182 FUNCTION Get_obj_version_number( p_location_id IN NUMBER DEFAULT NULL
3183 ,p_cust_account_id IN NUMBER DEFAULT NULL
3184 ,p_party_id IN NUMBER DEFAULT NULL ) RETURN NUMBER
3185 IS
3186 l_version_num NUMBER;
3187 BEGIN
3188
3189 IF p_location_id IS NOT NULL THEN
3190 select object_version_number
3191 into l_version_num
3192 from hz_locations
3193 where location_id = p_location_id;
3194 END IF;
3195
3196 IF p_cust_account_id IS NOT NULL THEN
3197 SELECT OBJECT_VERSION_NUMBER
3198 into l_version_num
3199 FROM HZ_CUST_ACCOUNTS
3200 WHERE CUST_ACCOUNT_ID = p_cust_account_id;
3201 END IF;
3202
3203 IF p_party_id IS NOT NULL THEN
3204 SELECT OBJECT_VERSION_NUMBER
3205 into l_version_num
3206 FROM HZ_PARTIES
3207 WHERE party_id = p_party_id;
3208 END IF;
3209
3210 RETURN l_version_num;
3211 EXCEPTION
3212 WHEN OTHERS THEN
3213 return NULL;
3214 END Get_obj_version_number;
3215
3216 /* This function returns the location_id for the given site_use_id and usage_code */
3217
3218 Function Get_Location_id ( p_site_usage_code IN VARCHAR2 DEFAULT NULL
3219 , p_site_use_id IN NUMBER DEFAULT NULL ) RETURN NUMBER
3220 IS
3221 l_location_id NUMBER;
3222 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3223 BEGIN
3224
3225 SELECT loc.location_id
3226 INTO l_location_id
3227 FROM hz_cust_site_uses site_uses,
3228 hz_cust_acct_sites acct_site,
3229 hz_party_sites party_site,
3230 hz_locations loc
3231 WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
3232 AND acct_site.party_site_id = party_site.party_site_id
3233 AND loc.location_id = party_site.location_id
3234 AND site_uses.site_use_code = p_site_usage_code
3235 AND site_uses.site_use_id = p_site_use_id;
3236
3237 IF l_debug_level > 0 THEN
3238 oe_debug_pub.add('Get_Location_id :l_location_id :'||l_location_id );
3239 END IF;
3240
3241 return l_location_id;
3242
3243 EXCEPTION
3244 WHEN OTHERS THEN
3245 IF l_debug_level > 0 THEN
3246 oe_debug_pub.add('Unable to retrive the location_id for the site_use_id :'||p_site_use_id||'and site_usage_code :'||p_site_usage_code);
3247 END IF;
3248 FND_MESSAGE.set_name('ONT','OE_INVALID_ATTRIBUTE');
3249 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','SITE_USE_ID');
3250 OE_MSG_PUB.Add;
3251 return FND_API.G_MISS_NUM;
3252 END Get_Location_id;
3253
3254
3255 /* This procedure will be called from Create_Account procedure if both ID and Value is passed
3256 for customer fields and OE_GLOBALS.G_UPDATE_ON_ID is TRUE. This API can update Customer_name,
3257 Account_Description,Account_number,Email contact point,Phone contact point*/
3258
3259 Procedure Update_Customer ( p_customer_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
3260 , x_return_status OUT NOCOPY VARCHAR2 ) IS
3261 x_version_number NUMBER;
3262 l_cust_acct_rec HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE;
3263 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
3264 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
3265 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
3266
3267 x_msg_count NUMBER;
3268 x_msg_data VARCHAR2(4000);
3269 l_customer_id NUMBER;
3270 l_party_id NUMBER;
3271 l_party_type VARCHAR2(50);
3272 x_profile_id NUMBER;
3273 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3274
3275 BEGIN
3276
3277 x_return_status := FND_API.G_RET_STS_SUCCESS;
3278
3279 IF l_debug_level > 0 THEN
3280 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Update_Customer ');
3281 END IF;
3282
3283 IF NOT IS_VALID_ID ( p_customer_id => p_customer_rec.customer_id ) THEN
3284 x_return_status := FND_API.G_RET_STS_ERROR;
3285 RETURN;
3286 END IF;
3287
3288
3289 l_party_id := Get_Party_ID (p_customer_rec.customer_id);
3290 l_party_type := Get_Party_Type (l_party_id);
3291
3292
3293
3294 IF l_party_type = 'ORGANIZATION' THEN
3295 l_party_rec.party_id := l_party_id;
3296 l_organization_rec.party_rec := l_party_rec;
3297 l_organization_rec.organization_name := p_customer_rec.organization_name;
3298 x_version_number := Get_obj_version_number(p_party_id => l_party_id);
3299
3300 hz_party_v2pub.update_organization (
3301 p_init_msg_list => FND_API.G_TRUE,
3302 p_organization_rec => l_organization_rec,
3303 p_party_object_version_number => x_version_number,
3304 x_profile_id =>x_profile_id,
3305 x_return_status =>x_return_status,
3306 x_msg_count => x_msg_count,
3307 x_msg_data => x_msg_data );
3308
3309 ELSE
3310 l_party_rec.party_id := l_party_id;
3311 l_person_rec.person_first_name := p_customer_rec.person_first_name;
3312 l_person_rec.person_middle_name := p_customer_rec.person_middle_name;
3313 l_person_rec.person_last_name := p_customer_rec.person_last_name;
3314 l_person_rec.person_title := p_customer_rec.person_title;
3315 l_person_rec.person_name_suffix := p_customer_rec.person_name_suffix;
3316 l_person_rec.party_rec := l_party_rec;
3317 x_version_number := Get_obj_version_number(p_party_id => l_party_id);
3318
3319 hz_party_v2pub.update_person (
3320 p_init_msg_list => FND_API.G_TRUE,
3321 p_person_rec => l_person_rec,
3322 p_party_object_version_number => x_version_number,
3323 x_profile_id =>x_profile_id,
3324 x_return_status =>x_return_status,
3325 x_msg_count => x_msg_count,
3326 x_msg_data => x_msg_data );
3327
3328
3329 END IF;
3330
3331 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3332 oe_msg_pub.transfer_msg_stack;
3333 RETURN;
3334 END IF;
3335
3336
3337 IF p_customer_rec.email_address IS NOT NULL THEN
3338 oe_oe_inline_address.create_contact_point
3339 (
3340 in_contact_point_type => 'EMAIL',
3341 in_owner_table_id => l_party_id,
3342 in_email => p_customer_rec.email_address,
3343 in_phone_area_code => NULL,
3344 in_phone_number => NULL,
3345 in_phone_extension => NULL,
3346 in_phone_country_code => NULL,
3347 p_created_by_module => G_CREATED_BY_MODULE,
3348 p_orig_system => p_customer_rec.orig_system,
3349 p_orig_system_reference => p_customer_rec.orig_system_reference,
3350 x_return_status => x_return_status,
3351 x_msg_count => x_msg_count,
3352 x_msg_data => x_msg_data
3353 );
3354
3355 END IF;
3356
3357 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3358 oe_msg_pub.transfer_msg_stack;
3359 RETURN;
3360 END IF;
3361
3362 IF p_customer_rec.phone_number IS NOT NULL Then
3363
3364 oe_oe_inline_address.create_contact_point
3365 (in_contact_point_type =>'PHONE',
3366 in_owner_table_id=>l_party_id,
3367 in_email=>NULL,
3368 in_phone_area_code =>p_customer_rec.phone_area_code,
3369 in_phone_number=>p_customer_rec.phone_number,
3370 in_phone_extension=>p_customer_rec.phone_extension,
3371 in_phone_country_code=>p_customer_rec.phone_country_code,
3372 x_return_status=>x_return_status,
3373 x_msg_count=>x_msg_count,
3374 x_msg_data=>x_msg_data
3375 );
3376 END IF;
3377
3378 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3379 oe_msg_pub.transfer_msg_stack;
3380 RETURN;
3381 END IF;
3382
3383 IF p_customer_rec.customer_number IS NOT NULL
3384 OR p_customer_rec.account_description IS NOT NULL THEN
3385
3386 x_version_number := Get_obj_version_number(p_cust_account_id => p_customer_rec.customer_id);
3387 l_cust_acct_rec.account_number := p_customer_rec.customer_number;
3388 l_cust_acct_rec.account_name := p_customer_rec.account_description;
3389 l_cust_acct_rec.cust_account_id := p_customer_rec.customer_id;
3390
3391 HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT (
3392 p_init_msg_list => FND_API.G_TRUE,
3393 p_cust_account_rec => l_cust_acct_rec,
3394 p_object_version_number => x_version_number,
3395 x_return_status => x_return_status,
3396 x_msg_count =>x_msg_count,
3397 x_msg_data =>x_msg_data
3398 );
3399 END IF;
3400
3401
3402 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3403 oe_msg_pub.transfer_msg_stack;
3404 RETURN;
3405 END IF;
3406
3407 IF l_debug_level > 0 THEN
3408 oe_debug_pub.add('Update_Customer :x_return_status:'||x_return_status);
3409 oe_debug_pub.add('Update_Customer:x_msg_count:'||x_msg_count);
3410 oe_debug_pub.add('Update_Customer :x_msg_data:'||x_msg_data);
3411 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Update_Customer ');
3412 END IF;
3413
3414 EXCEPTION
3415 WHEN OTHERS THEN
3416 x_return_status := FND_API.G_RET_STS_ERROR;
3417 END Update_Customer;
3418
3419 /* This procedure is called from Get_Customer_Info_Ids procedure to create contacts */
3420
3421 Procedure Create_Contact ( p_customer_info_tbl IN OE_ORDER_PUB.CUSTOMER_INFO_TABLE_TYPE
3422 , p_operation_code IN VARCHAR2
3423 , p_customer_id IN NUMBER
3424 , p_customer_rec_index IN NUMBER
3425 , p_usage_code IN VARCHAR2
3426 , x_sold_to_contact_id IN OUT NOCOPY NUMBER
3427 , x_ship_to_contact_id IN OUT NOCOPY NUMBER
3428 , x_invoice_to_contact_id IN OUT NOCOPY NUMBER
3429 , x_deliver_to_contact_id IN OUT NOCOPY NUMBER
3430 , x_return_status OUT NOCOPY VARCHAR2
3431 , x_msg_count OUT NOCOPY NUMBER
3432 , x_msg_data OUT NOCOPY VARCHAR2
3433 ) IS
3434 l_contact_rec_index NUMBER;
3435 l_contact_rec OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE;
3436 x_contact_id NUMBER;
3437 out_cont_name VARCHAR2(1000);
3438 x_sold_to_cont_index NUMBER;
3439 x_ship_to_cont_index NUMBER;
3440 x_bill_to_cont_index NUMBER;
3441 x_deliver_to_cont_index NUMBER;
3442
3443 x_cust_account_id NUMBER;
3444 x_party_id NUMBER;
3445 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3446 x_customer_id NUMBER;
3447 l_customer_id NUMBER;
3448 l_site_use_id NUMBER;
3449 BEGIN
3450
3451 x_return_status := FND_API.G_RET_STS_SUCCESS;
3452
3453 IF l_debug_level > 0 THEN
3454 oe_debug_pub.add('Entering OE_CUSTMER_INFO_PVT.Create_Contact');
3455 END IF;
3456
3457 l_contact_rec := p_customer_info_tbl(p_customer_rec_index);
3458
3459
3460 IF NVL( l_contact_rec.contact_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
3461
3462 Value_To_Id_Contact ( p_contact_record => l_contact_rec
3463 , p_customer_id => p_customer_id
3464 , p_site_usage_code => p_usage_code
3465 , p_site_usage_id => l_site_use_id
3466 , x_contact_id => x_contact_id
3467 );
3468 IF x_contact_id = FND_API.G_MISS_NUM THEN
3469 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
3470 ELSE
3471 IF p_usage_code = 'SOLD_TO' THEN
3472 x_sold_to_contact_id := x_contact_id;
3473 ELSIF p_usage_code = 'SHIP_TO' THEN
3474 x_ship_to_contact_id := x_contact_id;
3475 ELSIF p_usage_code = 'BILL_TO' THEN
3476 x_invoice_to_contact_id := x_contact_id;
3477 ELSE
3478 x_deliver_to_contact_id := x_contact_id;
3479 END IF;
3480 END IF;
3481 ELSE
3482 --Update Contact logic to go here
3483 IF IS_BOTH_ID_VAL_PASSED( p_contact_rec => l_contact_rec )
3484 AND OE_GLOBALS.G_UPDATE_ON_ID
3485 THEN
3486 Update_Contact (p_contact_rec => l_contact_rec
3487 , x_return_status =>x_return_status
3488 , x_msg_count => x_msg_count
3489 , x_msg_data => x_msg_data
3490 );
3491 END IF;
3492
3493 IF l_debug_level > 0 THEN
3494 oe_debug_pub.add('Update_Location :x_return_status'||x_return_status);
3495 oe_debug_pub.add('Update_Location :x_msg_count'||x_msg_count);
3496 oe_debug_pub.add('Update_Location :x_msg_data'||x_msg_data);
3497 END IF;
3498
3499 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3500 oe_msg_pub.transfer_msg_stack;
3501 RETURN;
3502 END IF;
3503
3504
3505 IF p_usage_code = 'SOLD_TO' THEN
3506 x_sold_to_contact_id := l_contact_rec.contact_id;
3507 ELSIF p_usage_code = 'SHIP_TO' THEN
3508 x_ship_to_contact_id := l_contact_rec.contact_id;
3509 ELSIF p_usage_code = 'BILL_TO' THEN
3510 x_invoice_to_contact_id := l_contact_rec.contact_id;
3511 ELSE
3512 x_deliver_to_contact_id := l_contact_rec.contact_id;
3513 END IF;
3514
3515
3516 x_contact_id :=l_contact_rec.contact_id;
3517
3518 END IF;
3519
3520 IF NVL(x_contact_id , FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
3521
3522 Check_Contact_Fields (l_contact_rec,x_return_status);
3523
3524 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3525 RETURN;
3526 END IF;
3527
3528 Validate_Contact_Fields(l_contact_rec,x_return_status);
3529
3530 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3531 RETURN;
3532 END IF;
3533
3534 oe_oe_inline_address.create_contact(
3535 p_contact_last_name => l_contact_rec.person_last_name,
3536 p_contact_first_name => l_contact_rec.person_first_name,
3537 p_contact_title => l_contact_rec.person_title,
3538 p_email => l_contact_rec.email_address,
3539 p_area_code => l_contact_rec.phone_area_code,
3540 p_phone_number => l_contact_rec.phone_number,
3541 p_extension => l_contact_rec.phone_extension,
3542 p_acct_id => p_customer_id,
3543 p_party_id => Get_party_id(p_customer_id),
3544 in_phone_country_code => l_contact_rec.phone_country_code,
3545 p_created_by_module => G_CREATED_BY_MODULE,
3546 p_orig_system => l_contact_rec.orig_system,
3547 p_orig_system_reference => l_contact_rec.orig_system_reference,
3548 c_attribute_category =>l_contact_rec.attribute_category,
3549 c_attribute1=>l_contact_rec.attribute1,
3550 c_attribute2=>l_contact_rec.attribute2,
3551 c_attribute3=>l_contact_rec.attribute3,
3552 c_attribute4=>l_contact_rec.attribute4,
3553 c_attribute5=>l_contact_rec.attribute5,
3554 c_attribute6=>l_contact_rec.attribute6,
3555 c_attribute7=>l_contact_rec.attribute7,
3556 c_attribute8=>l_contact_rec.attribute8,
3557 c_attribute9=>l_contact_rec.attribute9,
3558 c_attribute10=>l_contact_rec.attribute10,
3559 c_attribute11=>l_contact_rec.attribute11,
3560 c_attribute12=>l_contact_rec.attribute12,
3561 c_attribute13=>l_contact_rec.attribute13,
3562 c_attribute14=>l_contact_rec.attribute14,
3563 c_attribute15=>l_contact_rec.attribute15,
3564 c_attribute16=>l_contact_rec.attribute16,
3565 c_attribute17=>l_contact_rec.attribute17,
3566 c_attribute18=>l_contact_rec.attribute18,
3567 c_attribute19=>l_contact_rec.attribute19,
3568 c_attribute20=>l_contact_rec.attribute20,
3569 c_attribute21=>l_contact_rec.attribute21,
3570 c_attribute22=>l_contact_rec.attribute22,
3571 c_attribute23=>l_contact_rec.attribute23,
3572 c_attribute24=>l_contact_rec.attribute24,
3573 c_attribute25=>l_contact_rec.attribute25,
3574 c2_attribute_category=>l_contact_rec.global_attribute_category,
3575 c2_attribute1=>l_contact_rec.global_attribute1,
3576 c2_attribute2=>l_contact_rec.global_attribute2,
3577 c2_attribute3=>l_contact_rec.global_attribute3,
3578 c2_attribute4=>l_contact_rec.global_attribute4,
3579 c2_attribute5=>l_contact_rec.global_attribute5,
3580 c2_attribute6=>l_contact_rec.global_attribute6,
3581 c2_attribute7=>l_contact_rec.global_attribute7,
3582 c2_attribute8=>l_contact_rec.global_attribute8,
3583 c2_attribute9=>l_contact_rec.global_attribute9,
3584 c2_attribute10=>l_contact_rec.global_attribute10,
3585 c2_attribute11=>l_contact_rec.global_attribute11,
3586 c2_attribute12=>l_contact_rec.global_attribute12,
3587 c2_attribute13=>l_contact_rec.global_attribute13,
3588 c2_attribute14=>l_contact_rec.global_attribute14,
3589 c2_attribute15=>l_contact_rec.global_attribute15,
3590 c2_attribute16=>l_contact_rec.global_attribute16,
3591 c2_attribute17=>l_contact_rec.global_attribute17,
3592 c2_attribute18=>l_contact_rec.global_attribute18,
3593 c2_attribute19=>l_contact_rec.global_attribute19,
3594 c2_attribute20=>l_contact_rec.global_attribute20,
3595 x_return_status =>x_return_status,
3596 x_msg_count =>x_msg_count,
3597 x_msg_data =>x_msg_data,
3598 x_contact_id =>x_contact_id,
3599 x_contact_name => out_cont_name );
3600 IF l_debug_level > 0 THEN
3601 oe_debug_pub.add('Create Contact x_return_status :'||x_return_status);
3602 oe_debug_pub.add('Create Contact x_msg_data:'||x_msg_data);
3603 oe_debug_pub.add('Create Contact out_cont_id :'||x_contact_id);
3604 oe_debug_pub.add('Create Contact out_cont_name :'||out_cont_name);
3605
3606 END IF;
3607
3608 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3609 oe_msg_pub.transfer_msg_stack;
3610 RETURN;
3611 END IF;
3612
3613 IF p_usage_code = 'SOLD_TO' THEN
3614 x_sold_to_contact_id := x_contact_id;
3615 ELSIF p_usage_code = 'SHIP_TO' THEN
3616 x_ship_to_contact_id := x_contact_id;
3617 ELSIF p_usage_code = 'BILL_TO' THEN
3618 x_invoice_to_contact_id := x_contact_id;
3619 ELSE
3620 x_deliver_to_contact_id := x_contact_id;
3621 END IF;
3622
3623
3624 END IF;
3625
3626
3627
3628 IF l_debug_level > 0 THEN
3629 oe_debug_pub.add('Exiting OE_CUSTMER_INFO_PVT.Create_Contact');
3630 END IF;
3631
3632 EXCEPTION
3633 WHEN OTHERS THEN
3634
3635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3636
3637 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3638 THEN
3639 OE_MSG_PUB.Add_Exc_Msg
3640 ( G_PKG_NAME
3641 , 'Create_Contact'
3642 );
3643 END IF;
3644
3645 IF l_debug_level > 0 THEN
3646 oe_debug_pub.add('Create_account Other Errors :'||SQLERRM);
3647 END IF;
3648
3649 END Create_Contact;
3650
3651
3652
3653 Function IS_BOTH_ID_VAL_PASSED ( p_customer_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE)
3654 RETURN BOOLEAN
3655 IS
3656 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3657 BEGIN
3658 IF NVL(p_customer_rec.customer_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
3659 IF NVL(p_customer_rec.organization_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3660 OR NVL(p_customer_rec.account_description,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3661 OR NVL(p_customer_rec.customer_number,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3662 OR NVL(p_customer_rec.person_first_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3663 OR NVL(p_customer_rec.person_middle_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3664 OR NVL(p_customer_rec.person_last_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3665 OR NVL(p_customer_rec.person_title,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3666 OR NVL(p_customer_rec.person_name_suffix,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3667 THEN
3668 IF l_debug_level > 0 THEN
3669 oe_debug_pub.add('Both ID and Value Passed for Customer record');
3670 END IF;
3671 RETURN TRUE;
3672 END IF;
3673 END IF;
3674
3675 RETURN FALSE;
3676 END IS_BOTH_ID_VAL_PASSED;
3677
3678
3679 Function IS_BOTH_ID_VAL_PASSED ( p_address_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE)
3680 RETURN BOOLEAN
3681 IS
3682 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3683 BEGIN
3684
3685 IF NVL(p_address_rec.site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
3686
3687 IF NVL(p_address_rec.country,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3688 OR NVL(p_address_rec.state,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3689 OR NVL(p_address_rec.city,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3690 OR NVL(p_address_rec.county,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3691 OR NVL(p_address_rec.postal_code,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3692 OR NVL(p_address_rec.ADDRESS1,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3693 OR NVL(p_address_rec.ADDRESS2,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3694 OR NVL(p_address_rec.ADDRESS3,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3695 OR NVL(p_address_rec.ADDRESS4,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3696 THEN
3697 IF l_debug_level > 0 THEN
3698 oe_debug_pub.add('Both ID and Value Passed for address record');
3699 END IF;
3700
3701 RETURN TRUE;
3702 END IF;
3703 END IF;
3704 RETURN FALSE;
3705 END IS_BOTH_ID_VAL_PASSED;
3706
3707
3708
3709 Function IS_BOTH_ID_VAL_PASSED ( p_contact_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE)
3710 RETURN BOOLEAN
3711 IS
3712 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3713 BEGIN
3714 IF NVL(p_contact_rec.contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
3715 IF NVL(p_contact_rec.person_first_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3716 OR NVL(p_contact_rec.person_middle_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3717 OR NVL(p_contact_rec.person_last_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3718 OR NVL(p_contact_rec.person_title,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3719 OR NVL(p_contact_rec.person_name_suffix,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
3720 THEN
3721 IF l_debug_level > 0 THEN
3722 oe_debug_pub.add('Both ID and Value Passed for Customer record');
3723 END IF;
3724 RETURN TRUE;
3725 END IF;
3726 END IF;
3727
3728 RETURN FALSE;
3729
3730 END IS_BOTH_ID_VAL_PASSED;
3731
3732 /* This function returns true if a relation ship already exists between the two customers */
3733 Function check_relation_exists ( p_customer_id IN NUMBER
3734 ,p_rel_customer_id NUMBER ) RETURN BOOLEAN
3735 IS
3736 l_exists VARCHAR2(1);
3737 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3738 BEGIN
3739
3740 SELECT 'Y' INTO l_exists
3741 FROM HZ_CUST_ACCT_RELATE
3742 WHERE CUST_ACCOUNT_ID = p_customer_id
3743 AND RELATED_CUST_ACCOUNT_ID = p_rel_customer_id
3744 AND STATUS='A';
3745
3746 IF l_exists = 'Y' THEN
3747 IF l_debug_level > 0 THEN
3748 oe_debug_pub.add('Relation ship already exists between these two customers');
3749 END IF;
3750
3751 RETURN TRUE;
3752 ELSE
3753 RETURN FALSE;
3754 END IF;
3755 EXCEPTION
3756 WHEN OTHERS THEN
3757 IF l_debug_level > 0 THEN
3758 oe_debug_pub.add('Relation ship does not exists .Create it');
3759 END IF;
3760
3761 RETURN FALSE;
3762
3763 END check_relation_exists;
3764
3765 FUNCTION IS_VALID_ID ( p_party_id IN NUMBER DEFAULT NULL ,
3766 p_customer_id IN NUMBER DEFAULT NULL ) RETURN BOOLEAN
3767 IS
3768 l_exists varchar2(1);
3769 BEGIN
3770
3771 IF NVL(p_customer_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
3772 RETURN ( oe_validate.customer(p_customer_id => p_customer_id ));
3773 END IF;
3774
3775 IF NVL(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
3776 select 'Y'
3777 into l_exists
3778 from HZ_PARTIES
3779 Where party_id = p_party_id;
3780
3781 RETURN TRUE;
3782 END IF;
3783
3784 RETURN FALSE;
3785 EXCEPTION
3786 WHEN OTHERS THEN
3787 RETURN FALSE;
3788 END IS_VALID_ID;
3789
3790
3791
3792 Procedure Value_To_Id_contact( p_contact_record IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
3793 , p_customer_id IN NUMBER
3794 , p_site_usage_code IN VARCHAR2
3795 , p_site_usage_id OUT NOCOPY NUMBER
3796 , x_contact_id OUT NOCOPY NUMBER
3797 )IS
3798 l_contact_name VARCHAR2(5000);
3799 BEGIN
3800 x_contact_id := FND_API.G_MISS_NUM;
3801
3802 Select p_contact_record.person_last_name
3803 || DECODE(p_contact_record.person_first_name, NULL, NULL, ', '
3804 || p_contact_record.PERSON_FIRST_NAME)
3805 || DECODE(p_contact_record.Person_Name_Suffix, NULL, NULL, ', '
3806 ||p_contact_record.Person_Name_Suffix)
3807 Into l_contact_name
3808 From Dual;
3809
3810 x_contact_id := Oe_Value_To_Id.Sold_To_Contact(
3811 p_sold_to_contact => l_contact_name,
3812 p_sold_to_org_id => p_customer_id);
3813
3814
3815
3816 EXCEPTION
3817 WHEN OTHERS THEN
3818 x_contact_id := FND_API.G_MISS_NUM;
3819
3820 END Value_To_Id_contact;
3821
3822 /*This function checks whether the passed site usage already exits for the passed account site */
3823 PROCEDURE Check_site_usage_exists ( p_cust_acct_site_id IN NUMBER
3824 ,p_site_usage IN VARCHAR2
3825 ,x_site_use_id OUT NOCOPY NUMBER)
3826 IS
3827 BEGIN
3828
3829 x_site_use_id := FND_API.G_MISS_NUM;
3830
3831 SELECT site_uses.site_use_id
3832 INTO x_site_use_id
3833 FROM hz_cust_acct_sites acct_site,
3834 hz_cust_site_uses site_uses,
3835 hz_party_sites party_site,
3836 hz_locations loc
3837 WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
3838 AND acct_site.party_site_id = party_site.party_site_id
3839 AND loc.location_id = party_site.location_id
3840 AND site_uses.site_use_code = p_site_usage
3841 AND acct_site.CUST_ACCT_SITE_ID = p_cust_acct_site_id
3842 AND site_uses.STATUS = 'A';
3843
3844
3845
3846 EXCEPTION
3847 WHEN OTHERS THEN
3848 x_site_use_id := FND_API.G_MISS_NUM;
3849 END Check_site_usage_exists;
3850
3851 FUNCTION Get_Party_Type ( p_party_id IN NUMBER )RETURN VARCHAR2
3852 IS
3853 l_party_type VARCHAR2(50);
3854 BEGIN
3855 select party_type
3856 into l_party_type
3857 from hz_parties
3858 where party_id = p_party_id;
3859
3860 RETURN l_party_type;
3861
3862 EXCEPTION
3863 WHEN OTHERS THEN
3864 RETURN NULL;
3865 END Get_Party_Type;
3866
3867 /* Updates the contact information like person name,title,email,phone .Called from create_contact procedure
3868 if both ID and value are passed in contact record and OE_GLOBALS.G_UPDATE_ON_ID is TRUE*/
3869
3870 PROCEDURE Update_Contact ( p_contact_rec IN OE_ORDER_PUB.CUSTOMER_INFO_REC_TYPE
3871 , x_return_status OUT NOCOPY VARCHAR2
3872 , x_msg_count OUT NOCOPY NUMBER
3873 , x_msg_data OUT NOCOPY VARCHAR2
3874 )
3875 IS
3876 l_party_id NUMBER;
3877 x_profile_id NUMBER;
3878 l_person_rec HZ_PARTY_V2PUB.PERSON_REC_TYPE;
3879 l_party_rec HZ_PARTY_V2PUB.PARTY_REC_TYPE;
3880 x_version_number NUMBER;
3881 l_rel_party_id NUMBER;
3882 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3883 BEGIN
3884
3885 IF l_debug_level > 0 THEN
3886 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Update_Contact ');
3887 END IF;
3888 begin
3889
3890 SELECT party.party_id,REL_PARTY.party_id
3891 into l_party_id , l_rel_party_id
3892 FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
3893 HZ_PARTIES PARTY,
3894 HZ_CUST_ACCOUNTS ACCT,
3895 HZ_RELATIONSHIPS REL,
3896 HZ_ORG_CONTACTS ORG_CONT,
3897 HZ_PARTIES REL_PARTY
3898 WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
3899 AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
3900 AND ORG_CONT.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
3901 AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
3902 AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
3903 AND REL.SUBJECT_ID = PARTY.PARTY_ID
3904 AND REL.PARTY_ID = REL_PARTY.PARTY_ID
3905 AND REL.OBJECT_ID = ACCT.PARTY_ID
3906 AND ACCT.CUST_ACCOUNT_ID = ACCT_ROLE.CUST_ACCOUNT_ID
3907 AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_rec.contact_id;
3908
3909 Exception
3910 WHEN OTHERS THEN
3911 fnd_message.set_name('ONT','OE_INVALID_ATTRIBUTE');
3912 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','CONTACT_ID');
3913 OE_MSG_PUB.Add;
3914 x_return_status := FND_API.G_RET_STS_ERROR;
3915 End;
3916
3917 l_party_rec.party_id := l_party_id;
3918 l_person_rec.party_rec := l_party_rec;
3919 l_person_rec.person_first_name := p_contact_rec.person_first_name;
3920 l_person_rec.person_middle_name := p_contact_rec.person_middle_name;
3921 l_person_rec.person_last_name := p_contact_rec.person_last_name;
3922 l_person_rec.person_title := p_contact_rec.person_title;
3923 l_person_rec.person_name_suffix := p_contact_rec.person_name_suffix;
3924 x_version_number := Get_obj_version_number(p_party_id => l_party_id);
3925
3926 hz_party_v2pub.update_person (
3927 p_init_msg_list => FND_API.G_TRUE,
3928 p_person_rec => l_person_rec,
3929 p_party_object_version_number => x_version_number,
3930 x_profile_id =>x_profile_id,
3931 x_return_status =>x_return_status,
3932 x_msg_count => x_msg_count,
3933 x_msg_data => x_msg_data );
3934
3935
3936
3937 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3938 oe_msg_pub.transfer_msg_stack;
3939 RETURN;
3940 END IF;
3941
3942
3943 IF p_contact_rec.email_address IS NOT NULL THEN
3944 oe_oe_inline_address.create_contact_point
3945 (
3946 in_contact_point_type => 'EMAIL',
3947 in_owner_table_id => l_rel_party_id,
3948 in_email => p_contact_rec.email_address,
3949 in_phone_area_code => NULL,
3950 in_phone_number => NULL,
3951 in_phone_extension => NULL,
3952 in_phone_country_code => NULL,
3953 p_created_by_module => G_CREATED_BY_MODULE,
3954 p_orig_system => p_contact_rec.orig_system,
3955 p_orig_system_reference => p_contact_rec.orig_system_reference,
3956 x_return_status => x_return_status,
3957 x_msg_count => x_msg_count,
3958 x_msg_data => x_msg_data
3959 );
3960
3961 END IF;
3962
3963 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3964 oe_msg_pub.transfer_msg_stack;
3965 RETURN;
3966 END IF;
3967
3968 IF p_contact_rec.phone_number IS NOT NULL Then
3969
3970 oe_oe_inline_address.create_contact_point
3971 (in_contact_point_type =>'PHONE',
3972 in_owner_table_id=>l_rel_party_id,
3973 in_email=>NULL,
3974 in_phone_area_code =>p_contact_rec.phone_area_code,
3975 in_phone_number=>p_contact_rec.phone_number,
3976 in_phone_extension=>p_contact_rec.phone_extension,
3977 in_phone_country_code=>p_contact_rec.phone_country_code,
3978 x_return_status=>x_return_status,
3979 x_msg_count=>x_msg_count,
3980 x_msg_data=>x_msg_data
3981 );
3982 END IF;
3983
3984 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3985 oe_msg_pub.transfer_msg_stack;
3986 RETURN;
3987 END IF;
3988
3989
3990
3991 IF l_debug_level > 0 THEN
3992 oe_debug_pub.add('Update_Contact :x_return_status:'||x_return_status);
3993 oe_debug_pub.add('Update_Contact :x_msg_count:'||x_msg_count);
3994 oe_debug_pub.add('Update_Contact :x_msg_data:'||x_msg_data);
3995 oe_debug_pub.add('Entering OE_CUSTOMER_INFO_PVT.Update_Contact ');
3996 END IF;
3997
3998 EXCEPTION
3999 WHEN OTHERS THEN
4000 x_return_status := FND_API.G_RET_STS_ERROR;
4001
4002 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4003 THEN
4004 OE_MSG_PUB.Add_Exc_Msg
4005 ( G_PKG_NAME
4006 , 'Update_Contact'
4007 );
4008 END IF;
4009
4010 IF l_debug_level > 0 THEN
4011 oe_debug_pub.add('Update_Contact Other Errors :'||SQLERRM);
4012 END IF;
4013
4014
4015 END Update_Contact;
4016
4017 END OE_CUSTOMER_INFO_PVT;