1 PACKAGE BODY OE_INLINE_CUSTOMER_PUB AS
2 /* $Header: OEXPINLB.pls 120.7.12010000.2 2008/11/14 11:57:23 ckasera ship $ */
3
4 -- { Start of Global Variable used in Api
5
6 G_INITIALIZED VARCHAR2(1) := FND_API.G_FALSE;
7 G_EMAIL_REQUIRED VARCHAR2(1) := 'N';
8 G_AUTO_PARTY_NUMBERING VARCHAR2(1) := 'N';
9 G_AUTO_CUST_NUMBERING VARCHAR2(1) := 'N';
10 G_AUTO_CONTACT_NUMBERING VARCHAR2(1) := 'N';
11 G_AUTO_LOCATION_NUMBERING VARCHAR2(1) := 'N';
12 G_AUTO_SITE_NUMBERING VARCHAR2(1) := 'N';
13
14 G_CREATED_BY_MODULE CONSTANT VARCHAR2(30) := 'ONT_OI_ADD_CUSTOMER';
15 -- End of Global Variable used in Api}
16
17 -- { Start of procedure Initialize_Global
18 PROCEDURE Initialize_Global( x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2)
19 IS
20 l_sysparm_rec ar_system_parameters%rowtype;
21 l_sys_parm_rec ar_system_parameters_all%rowtype;
22 --
23 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
24 --
25 Begin
26
27 IF l_debug_level > 0 THEN
28 oe_debug_pub.add( 'ENTERING PROCEDURE INITIALIZE_GLOBAL' ) ;
29 END IF;
30 x_return_status := FND_API.G_RET_STS_SUCCESS;
31
32 -- { Start of the ar_system_parameters select and assignment
33
34 IF oe_code_control.code_release_level < '110510' THEN
35 Select *
36 Into l_sysparm_rec
37 From ar_system_parameters;
38 ELSE
39 l_Sys_Parm_Rec := OE_Sys_Parameters_Pvt.Get_AR_Sys_Params;
40 END IF;
41
42
43 IF l_debug_level > 0 THEN
44 oe_debug_pub.add( 'AFTER SELECT FROM AR_SYSTEM_PARAMETES' ) ;
45 END IF;
46
47 IF oe_code_control.code_release_level < '110510' THEN
48 G_AUTO_CUST_NUMBERING := nvl(l_sysparm_rec.generate_customer_number,'Y');
49 G_AUTO_LOCATION_NUMBERING := nvl(l_sysparm_rec.auto_site_numbering,'Y');
50 ELSE
51 G_AUTO_CUST_NUMBERING := nvl(l_sys_parm_rec.generate_customer_number,'Y');
52 G_AUTO_LOCATION_NUMBERING := nvl(l_sys_parm_rec.auto_site_numbering,'Y');
53 END IF;
54
55 -- End of the ar_system_parameters select and assignment}
56
57 -- { Start for global values from profile
58 fnd_profile.get('ONT_MANDATE_CUSTOMER_EMAIL',G_EMAIL_REQUIRED);
59 fnd_profile.get('HZ_GENERATE_PARTY_NUMBER',G_AUTO_PARTY_NUMBERING);
60 fnd_profile.get('HZ_GENERATE_CONTACT_NUMBER',G_AUTO_CONTACT_NUMBERING);
61 fnd_profile.get('HZ_GENERATE_PARTY_SITE_NUMBER',G_AUTO_SITE_NUMBERING);
62
63 G_EMAIL_REQUIRED := nvl(G_EMAIL_REQUIRED,'Y');
64 G_AUTO_PARTY_NUMBERING := nvl(G_AUTO_PARTY_NUMBERING,'Y');
65 G_AUTO_CONTACT_NUMBERING := nvl(G_AUTO_CONTACT_NUMBERING,'Y');
66 G_AUTO_SITE_NUMBERING := nvl(G_AUTO_SITE_NUMBERING, 'Y');
67
68 -- End for global values from profile }
69 G_INITIALIZED := FND_API.G_TRUE;
70 IF l_debug_level > 0 THEN
71 oe_debug_pub.add( 'EXITING PROCEDURE INITIALIZE_GLOBAL' ) ;
72 END IF;
73 Exception
74 When Others Then
75 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
76 IF l_debug_level > 0 THEN
77 oe_debug_pub.add( 'PROBLEM IN CALL TO INITIALIZE_GLOBAL. ABORT PROCESSING' ) ;
78 END IF;
79 IF l_debug_level > 0 THEN
80 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
81 END IF;
82 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
83 fnd_message.set_token('API_NAME', 'Initialize_Global');
84 oe_msg_pub.add;
85 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
86 IF l_debug_level > 0 THEN
87 oe_debug_pub.add( 'EXITING PROCEDURE INITIALIZE_GLOBAL' ) ;
88 END IF;
89 End Initialize_Global;
90 -- End of procedure Initialize_Global}
91
92 -- { Start of procedure Validate Party Number
93 -- This will be to validate if the party_number passed
94 -- is already used or not. If used that that is an error
95 -- report it.
96 PROCEDURE Validate_Party_Number( p_party_number IN Varchar2,
97 p_party_type IN Varchar2,
98 x_party_id OUT NOCOPY /* file.sql.39 change */ Number,
99 x_party_name OUT NOCOPY /* file.sql.39 change */ Varchar2,
100 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2)
101 IS
102 --
103 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
104 --
105 Begin
106
107 IF l_debug_level > 0 THEN
108 oe_debug_pub.add( 'ENTERNING VALIDATE PARTY NUMBER API' ) ;
109 END IF;
110 Select party_id,
111 party_name
112 Into x_party_id,
113 x_party_name
114 From hz_parties
115 Where party_number = p_party_number
116 And party_type = p_party_type;
117
118 IF l_debug_level > 0 THEN
119 oe_debug_pub.add( 'AFTER SELECT OF PARTY INFO.' ) ;
120 END IF;
121 x_return_status := FND_API.G_RET_STS_SUCCESS;
122
123 IF l_debug_level > 0 THEN
124 oe_debug_pub.add( 'EXITING VALIDATE PARTY NUMBER API WITH PARTY ID' ) ;
125 END IF;
126 Exception
127 When No_Data_Found Then
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129 IF l_debug_level > 0 THEN
130 oe_debug_pub.add( 'EXITING VALIDATE PARTY NUMBER API WITHOUT PARTY ID' ) ;
131 END IF;
132 When Others Then
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 IF l_debug_level > 0 THEN
135 oe_debug_pub.add( 'PROBLEM IN CALL TO VALIDATE PARTY. ABORT PROCESSING' ) ;
136 END IF;
137 IF l_debug_level > 0 THEN
138 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
139 END IF;
140 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
141 fnd_message.set_token('API_NAME', 'Validate_Party_Number');
142 oe_msg_pub.add;
143 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add( 'EXITING VALIDATE PARTY NUMBER API WITHOUT PARTY ID' ) ;
146 END IF;
147 End Validate_Party_Number;
148 -- End of procedure Validate_Party_Number}
149
150 -- { Start of update Error Flag for the error Record
151 PROCEDURE Update_Error_Flag(
152 p_rowid IN Rowid)
153 IS
154 PRAGMA AUTONOMOUS_TRANSACTION;
155
156 --
157 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
158 --
159
160 BEGIN
161
162 IF l_debug_level > 0 THEN
163 oe_debug_pub.add( 'ENTERING PROCEDURE UPDATE_ERROR_FLAG' ) ;
164 END IF;
165
166 Update Oe_Customer_Info_Iface_All
167 Set Error_Flag = 'Y'
168 Where rowid = p_rowid;
169 Commit;
170
171 IF l_debug_level > 0 THEN
172 oe_debug_pub.add( 'EXITING PROCEDURE UPDATE_ERROR_FLAG' ) ;
173 END IF;
174 Exception
175
176 when others then
177 IF l_debug_level > 0 THEN
178 OE_DEBUG_PUB.add ('Update Error Flag: Unexpected Error : '||sqlerrm);
179 END IF;
180
181 END Update_Error_Flag;
182 -- End of Update Error Flag }
183
184
185 PROCEDURE Update_Address_id(type_of_address IN VARCHAR2,
186 usage_site_id IN NUMBER,
187 row_id IN rowid )
188 IS
189 Pragma AUTONOMOUS_TRANSACTION;
190 l_address_id_ship NUMBER;
191 l_address_id_bill NUMBER;
192 l_address_id_deliver NUMBER;
193 l_dummy VARCHAR2(2);
194
195 --
196 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
197 --
198
199 BEGIN
200 IF l_debug_level > 0 THEN
201
202 oe_debug_pub.add (' Entering Update_Address_id');
203 oe_debug_pub.add (' locking table for: '||type_of_address);
204 oe_debug_pub.add (' p_usage_site_id :'|| usage_site_id);
205 oe_debug_pub.add (' Row Id :'||row_id );
206
207 END IF;
208
209 SELECT new_address_id_ship,new_address_id_bill,new_address_id_Deliver
210 into l_address_id_ship, l_address_id_bill, l_address_id_deliver
211 FROM oe_customer_info_iface_all
212 WHERE rowid = row_id
213 FOR UPDATE NOWAIT;
214
215 If type_of_address = 'SHIP_TO' AND
216 l_address_id_ship is NULL Then
217
218 UPDATE oe_customer_info_iface_all
219 SET new_address_id_ship = usage_site_id
220 WHERE rowid = row_id;
221
222 Elsif type_of_address = 'BILL_TO' AND
223 l_address_id_bill is NULL Then
224
225 UPDATE oe_customer_info_iface_all
226 SET new_address_id_bill = usage_site_id
227 WHERE rowid = row_id;
228
229 Elsif type_of_address = 'DELIVER_TO' AND
230 l_address_id_deliver is NULL Then
231 UPDATE oe_customer_info_iface_all
232 SET new_address_id_Deliver = usage_site_id
233 WHERE rowid = row_id;
234 End if;
235 Commit;
236
237 IF l_debug_level > 0 THEN
238 oe_debug_pub.add (' Type of Address : '|| type_of_address);
239 oe_debug_pub.add ('Update_Address_id: ' || usage_site_id);
240 END IF;
241
242 EXCEPTION
243 when no_data_found then
244 null;
245
246 when others then
247 IF l_debug_level > 0 THEN
248 OE_DEBUG_PUB.add ('Update_address_id: Unexpected Error : '||sqlerrm);
249 END IF;
250
251 END Update_Address_Id;
252
253 -- { Start of Create Cust Relationship
254 -- This api will be called from Order Import Specific api for
255 -- creating the relationship between the sold to customer and
256 -- other type(ship/deliver/invoice) customer is being created
257 -- thru the Add customer process
258 PROCEDURE Create_Cust_Relationship(
259 p_cust_acct_id IN Number,
260 p_related_cust_acct_id IN Number,
261 p_reciprocal_flag IN Varchar2 Default 'N',
262 p_org_id IN Number,
263 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2,
264 x_msg_count OUT NOCOPY /* file.sql.39 change */ Number,
265 x_msg_data OUT NOCOPY /* file.sql.39 change */ Varchar2
266 )
267 IS
268
269 l_cust_rel_rec HZ_CUST_ACCOUNT_V2PUB.cust_acct_relate_rec_type;
270 l_return_status Varchar2(1);
271 l_need_cust_rel Varchar2(1);
272
273 --
274 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
275 --
276 BEGIN
277
278 IF l_debug_level > 0 THEN
279 oe_debug_pub.add( 'ENTERING PROCEDURE CREATE_CUST_RELATIONSHIP' ) ;
280 END IF;
281
282 l_need_cust_rel := OE_Sys_Parameters.Value('CUSTOMER_RELATIONSHIPS_FLAG');
283
284 If l_need_cust_rel In ('N', 'A') Then
285 IF l_debug_level > 0 THEN
286 oe_debug_pub.add( 'NO NEED TO CREATE RELATIONSHIP' ) ;
287 END IF;
288 IF l_debug_level > 0 THEN
289 oe_debug_pub.add( 'EXITING PROCEDURE CREATE_CUST_RELATIONSHIP' ) ;
290 END IF;
291 Return;
292 End If;
293 l_cust_rel_rec.cust_account_id := p_cust_acct_id;
294 l_cust_rel_rec.related_cust_account_id := p_related_cust_acct_id;
295 l_cust_rel_rec.relationship_type := 'ALL';
296 l_cust_rel_rec.customer_reciprocal_flag := p_reciprocal_flag;
297 l_cust_rel_rec.created_by_module := G_CREATED_BY_MODULE;
298 l_cust_rel_rec.application_id := 660;
299 l_cust_rel_rec.org_id := p_org_id;
300
301 IF l_debug_level > 0 THEN
302 oe_debug_pub.add( 'CUST_ACCOUNT_ID:'||P_CUST_ACCT_ID||':RELATED_CUST_ACCOUNT_ID:'||P_RELATED_CUST_ACCT_ID ) ;
303 END IF;
304 HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Acct_Relate
305 (
306 p_cust_acct_relate_rec => l_cust_rel_rec,
307 x_return_status => l_return_status,
308 x_msg_count => x_msg_count,
309 x_msg_data => x_msg_data
310 );
311
312 IF l_debug_level > 0 THEN
313 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
314 END IF;
315 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
316 IF l_debug_level > 0 THEN
317 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => X_MSG_COUNT ) ) ;
318 END IF;
319 IF l_debug_level > 0 THEN
320 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM ACC RELATE '|| X_MSG_DATA ) ;
321 END IF;
322 x_return_status := l_return_status;
323 oe_msg_pub.transfer_msg_stack;
324 fnd_msg_pub.delete_msg;
325 IF l_debug_level > 0 THEN
326 oe_debug_pub.add( 'EXITING PROCEDURE CREATE_CUST_RELATIONSHIP' ) ;
327 END IF;
328 return;
329 Else
330 IF l_debug_level > 0 THEN
331 oe_debug_pub.add( 'NEW CUSTOMER RELATIONSHIP IS CREATED' ) ;
332 END IF;
333 End if;
334
335 IF l_debug_level > 0 THEN
336 oe_debug_pub.add( 'EXITING PROCEDURE CREATE_CUST_RELATIONSHIP' ) ;
337 END IF;
338 EXCEPTION
339 When Others Then
340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
341 IF l_debug_level > 0 THEN
342 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_CUST_RELATIONSHIP. ABORT PROCESSING' ) ;
343 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
344 END IF;
345 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
346 fnd_message.set_token('API_NAME', 'Create_Cust_Relationship');
347 oe_msg_pub.add;
348 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
349 IF l_debug_level > 0 THEN
350 oe_debug_pub.add( 'EXITING CREATE_CUST_RELATIONSHIP API WITH ERROR' ) ;
351 END IF;
352 END create_cust_relationship;
353 -- End of Create Cust Relationship}
354
355 -- {Start of procedure Check and Create Contact
356 PROCEDURE Check_and_Create_Contact(p_contact_party_id IN Number,
357 p_cust_acct_id IN Number,
358 p_usage_type IN Varchar2,
359 x_contact_id OUT NOCOPY /* file.sql.39 change */ Number,
360 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
361 ) IS
362
363 -- if role found at site level then we create an acct level role
364 -- also
365 Cursor c_cust_acct_role IS
366 Select cust_account_role_id
367 From hz_cust_account_roles
368 Where party_id = p_contact_party_id
369 And cust_account_id = p_cust_acct_id
370 And role_type = 'CONTACT'
371 And status = 'A'
372 And cust_acct_site_id is null;
373
374 CURSOR c_usage_type(l_role_id in number) IS
375 SELECT responsibility_type
376 FROM hz_role_responsibility
377 WHERE cust_account_role_id = l_role_id;
378 --AND responsibility_type = in_usage_type;
379
380 l_cust_account_role_id Number;
381 l_select Number;
382 l_msg_count Number;
383 l_msg_data Varchar2(4000);
384 l_responsibility_id Number;
385 l_create_role Varchar2(10) := 'UNKNOWN';
386 l_role_resp_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.role_responsibility_rec_type;
387 l_return_status Varchar2(1);
388
389 --
390 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
391 --
392 BEGIN
393
394 IF l_debug_level > 0 THEN
395 oe_debug_pub.add( 'ENTERING CHECK AND CREATE CONTACT API' ) ;
396 END IF;
397 -- checking to see if there is cust_account_role for the party contact
398 Open c_cust_acct_role;
399 Fetch c_cust_acct_role
400 Into l_cust_account_role_id;
401
402 -- {Start of the If of NOTFOUND
403 If c_cust_acct_role%NOTFOUND then
404 x_contact_id := null;
405 Elsif c_cust_acct_role%FOUND then
406
407 IF l_debug_level > 0 THEN
408 oe_debug_pub.add( 'A CUST ACCT ROLE USAGE TYPE = '|| P_USAGE_TYPE ) ;
409 END IF;
410
411 --if cust_acct_role is found then we check the usage_type of this
412 --role
413 -- {Start of the loop for c_usage_type
414 FOR c_record in c_usage_type(l_cust_account_role_id)
415 LOOP
416 IF l_debug_level > 0 THEN
417 oe_debug_pub.add( 'LOOP FOR ROLE TYPE' ) ;
418 END IF;
419 If c_record.responsibility_type = p_usage_type then
420 IF l_debug_level > 0 THEN
421 oe_debug_pub.add( 'ROLE TYPE FOUND' ) ;
422 END IF;
423 l_create_role := 'FOUND';
424 Else
425 IF l_debug_level > 0 THEN
426 oe_debug_pub.add( 'ROLE TYPE NOT FOUND' ) ;
427 END IF;
428 if l_create_role <> 'FOUND' then
429 l_create_role := 'NOTFOUND';
430 end if;
431
432 End If;
433 END LOOP;
434 -- End of the loop for c_usage_type}
435
436 IF l_debug_level > 0 THEN
437 oe_debug_pub.add( 'IF THE TYPE OF ROLE IS NOTFOUND THEN WE WILL' ) ;
438 END IF;
439 IF l_debug_level > 0 THEN
440 oe_debug_pub.add( 'CREATE NEW ROLE DEPENDING ON THE TYPE OF USAGE' ) ;
441 END IF;
442 IF l_debug_level > 0 THEN
443 oe_debug_pub.add( 'PASSED. ELSE IF IT IS REMAIN UNKNOWN OR FOUND' ) ;
444 END IF;
445 IF l_debug_level > 0 THEN
446 oe_debug_pub.add( 'IN THAT CASE RETURN THE EXISITING ID I.E.' ) ;
447 END IF;
448 IF l_debug_level > 0 THEN
449 oe_debug_pub.add( 'L_CUST_ACCOUNT_ROLE_ID =>' || L_CUST_ACCOUNT_ROLE_ID ) ;
450 END IF;
451
452 IF l_debug_level > 0 THEN
453 oe_debug_pub.add( 'L_CREATE_ROLE => '||L_CREATE_ROLE ) ;
454 END IF;
455 -- { Start of the l_create_role
456 If l_create_role = 'NOTFOUND' then
457 -- role usage type is only created if the user comes from the
458 -- site levels.Its not created for account level contacts
459 -- { Start of the usage If
460 If p_usage_type in ('SHIP_TO','BILL_TO','DELIVER_TO') then
461 l_role_resp_rec.cust_account_role_id := l_cust_account_role_id;
462 l_role_resp_rec.responsibility_type := p_usage_type;
463 l_role_resp_rec.primary_flag := 'Y';
464 l_role_resp_rec.created_by_module := G_CREATED_BY_MODULE;
465 l_role_resp_rec.application_id := 660;
466
467 IF l_debug_level > 0 THEN
468 oe_debug_pub.add( 'CREATING ROLE RESP. CALLING HZ_API' ) ;
469 END IF;
470 -- {Start Call hz api to create role resp
471 HZ_CUST_ACCOUNT_ROLE_V2PUB.Create_Role_Responsibility(
472 p_role_responsibility_rec => l_role_resp_rec,
473 x_return_status => l_return_status,
474 x_msg_count => l_msg_count,
475 x_msg_data => l_msg_data,
476 x_responsibility_id => l_responsibility_id
477 );
478 -- End Call hz api to create role resp }
479
480 -- Let us check the status of the call to hz api
481 IF l_debug_level > 0 THEN
482 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
483 END IF;
484 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
485 IF l_debug_level > 0 THEN
486 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
487 END IF;
488 IF l_debug_level > 0 THEN
489 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
490 END IF;
491 x_return_status := l_return_status;
492 oe_msg_pub.transfer_msg_stack;
493 fnd_msg_pub.delete_msg;
494 IF l_debug_level > 0 THEN
495 oe_debug_pub.add( 'EXITING CREATE ROLE RESPONSIBILITY API WITH ERROR' ) ;
496 END IF;
497 return;
498 Else
499 IF l_debug_level > 0 THEN
500 oe_debug_pub.add( 'NEW RESPO. ID FOR SITE => '|| L_RESPONSIBILITY_ID ) ;
501 END IF;
502 End If;
503 -- End if of Let us check the status of the call to hz api
504 End If;
505 -- End of the usage If}
506
507 End If;
508 -- End of the l_create_role}
509 x_contact_id := l_cust_account_role_id;
510
511 End If;
512 -- End of the If of NOTFOUND}
513
514 Close c_cust_acct_role;
515 --oe_debug_pub.add('out contact_id ='||out_contact_id);
516 IF l_debug_level > 0 THEN
517 oe_debug_pub.add( 'EXISING CHECK AND CREATE CONTACT API' ) ;
518 END IF;
519
520 EXCEPTION
521 WHEN others then
522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
523 If c_cust_acct_role%ISOPEN then
524 CLOSE c_cust_acct_role;
525 End If;
526 If c_usage_type%ISOPEN then
527 CLOSE c_usage_type;
528 End If;
529 IF l_debug_level > 0 THEN
530 oe_debug_pub.add( 'PROBLEM IN CALL TO CHECK_AND_CREATE_CONTACT. ABORT PROCESSING' ) ;
531 END IF;
532 IF l_debug_level > 0 THEN
533 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
534 END IF;
535 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
536 fnd_message.set_token('API_NAME', 'Check_and_Create_Contact');
537 oe_msg_pub.add;
538 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
539 IF l_debug_level > 0 THEN
540 oe_debug_pub.add( 'EXITING CHECK AND CREATE AND CREATE CONTACT API' ) ;
541 END IF;
542
543 END Check_and_Create_Contact;
544 -- End of procedure Check and Create Contact}
545
546
547 -- { Start of procedure Create Contact Point(Phone and Email)
548 -- This will be used to create the contact points for
549 -- customer as well as contact
550 PROCEDURE Create_Contact_Point(
551 p_contact_point_type IN Varchar2,
552 p_owner_table_id IN Number,
553 p_email IN Varchar2,
554 p_phone_area_code IN Varchar2,
555 p_phone_number IN Varchar2,
556 p_phone_extension IN Varchar2,
557 p_phone_country_code IN Varchar2,
558 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2,
559 x_msg_count OUT NOCOPY /* file.sql.39 change */ Number,
560 x_msg_data OUT NOCOPY /* file.sql.39 change */ Varchar2
561 )
562 IS
563 l_contact_point_id Number;
564 l_contact_points_rec HZ_CONTACT_POINT_V2PUB.contact_point_rec_type;
565 l_email_rec HZ_CONTACT_POINT_V2PUB.email_rec_type;
566 l_phone_rec HZ_CONTACT_POINT_V2PUB.phone_rec_type;
567 l_return_status Varchar2(1);
568 --
569 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
570 --
571 BEGIN
572
573 IF l_debug_level > 0 THEN
574 oe_debug_pub.add( 'ENTERING CREATE CONTACT POINT API' ) ;
575 END IF;
576
577 x_return_status := FND_API.G_RET_STS_SUCCESS;
578 l_contact_points_rec.contact_point_type := p_contact_point_type;
579 l_contact_points_rec.status := 'A';
580 l_contact_points_rec.owner_table_name := 'HZ_PARTIES';
581 l_contact_points_rec.owner_table_id := p_owner_table_id;
582 l_contact_points_rec.primary_flag := 'Y';
583 l_contact_points_rec.created_by_module := G_CREATED_BY_MODULE;
584 l_contact_points_rec.application_id := 660;
585
586 -- Select the nextval from sequence for contact point
587 Select hz_contact_points_s.nextval
588 Into l_contact_points_rec.contact_point_id
589 From dual;
590
591 If p_contact_point_type = 'EMAIL' Then
592
593 l_email_rec.email_address := p_email;
594
595 -- { Start Call hz api to create contact point
596 HZ_CONTACT_POINT_V2PUB.Create_Contact_Point(
597 p_contact_point_rec => l_contact_points_rec,
598 p_email_rec => l_email_rec,
599 x_return_status => l_return_status,
600 x_msg_count => x_msg_count,
601 x_msg_data => x_msg_data,
602 x_contact_point_id => l_contact_point_id
603 );
604 -- End Call hz api to create contact point }
605
606 -- Let us check the status of the call to hz api
607 IF l_debug_level > 0 THEN
608 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
609 END IF;
610 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
611 IF l_debug_level > 0 THEN
612 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => X_MSG_COUNT ) ) ;
613 END IF;
614 IF l_debug_level > 0 THEN
615 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || X_MSG_DATA ) ;
616 END IF;
617 x_return_status := l_return_status;
618 oe_msg_pub.transfer_msg_stack;
619 fnd_msg_pub.delete_msg;
620 IF l_debug_level > 0 THEN
621 oe_debug_pub.add( 'EXITING CREATE CONTACT POINT API WITH ERROR' ) ;
622 END IF;
623 return;
624 Else
625 IF l_debug_level > 0 THEN
626 oe_debug_pub.add( 'NEW CONTACT ID FOR EMAIL => '|| L_CONTACT_POINT_ID ) ;
627 END IF;
628 End if;
629 Elsif p_contact_point_type = 'PHONE' Then
630
631 l_phone_rec.phone_area_code := p_phone_area_code;
632 l_phone_rec.phone_number := p_phone_number;
633 l_phone_rec.phone_extension := p_phone_extension;
634 l_phone_rec.phone_line_type := 'GEN';
635 l_phone_rec.phone_country_code := p_phone_country_code;
636
637 -- { Start Call hz api to create contact point
638 HZ_CONTACT_POINT_V2PUB.Create_Contact_Point(
639 p_contact_point_rec => l_contact_points_rec,
640 p_phone_rec => l_phone_rec,
641 x_return_status => l_return_status,
642 x_msg_count => x_msg_count,
643 x_msg_data => x_msg_data,
644 x_contact_point_id => l_contact_point_id
645 );
646 -- End Call hz api to create contact point }
647
648 -- Let us check the status of the call to hz api
649 IF l_debug_level > 0 THEN
650 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
651 END IF;
652 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
653 IF l_debug_level > 0 THEN
654 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => X_MSG_COUNT ) ) ;
655 END IF;
656 IF l_debug_level > 0 THEN
657 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || X_MSG_DATA ) ;
658 END IF;
659 x_return_status := l_return_status;
660 oe_msg_pub.transfer_msg_stack;
661 fnd_msg_pub.delete_msg;
662 IF l_debug_level > 0 THEN
663 oe_debug_pub.add( 'EXITING CREATE CONTACT POINT API WITH ERROR' ) ;
664 END IF;
665 return;
666 Else
667 IF l_debug_level > 0 THEN
668 oe_debug_pub.add( 'NEW CONTACT ID FOR PHONE =>' || L_CONTACT_POINT_ID ) ;
669 END IF;
670 End if;
671
672 END IF;
673 Exception
674 When Others Then
675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676 IF l_debug_level > 0 THEN
677 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE CONTACT PT. ABORT PROCESSING' ) ;
678 END IF;
679 IF l_debug_level > 0 THEN
680 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
681 END IF;
682 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
683 fnd_message.set_token('API_NAME', 'Create_Contact_Point');
684 oe_msg_pub.add;
685 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
686 IF l_debug_level > 0 THEN
687 oe_debug_pub.add( 'EXITING CREATE CONTACT POINT API WITH ERROR' ) ;
688 END IF;
689
690 END create_contact_point;
691 -- End of procedure Create Contact Point(Phone and Email) }
692
693 -- { Start of procedure Create_Account
694 PROCEDURE Create_Account(
695 p_customer_info_ref IN Varchar2,
696 p_orig_sys_document_ref IN Varchar2,
697 p_orig_sys_line_ref IN Varchar2,
698 p_order_source_id IN Number,
699 x_cust_account_id IN OUT NOCOPY /* file.sql.39 change */ Number,
700 x_cust_account_number IN OUT NOCOPY /* file.sql.39 change */ Varchar2,
701 x_cust_party_id OUT NOCOPY /* file.sql.39 change */ Number,
702 x_existing_value OUT NOCOPY /* file.sql.39 change */ Varchar2,
703 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
704 )
705 IS
706
707 l_customer_info_ref Varchar2(50) := p_customer_info_ref;
708 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
709 l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
710 l_party_rec HZ_PARTY_V2PUB.party_rec_type;
711 l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
712 l_account_rec HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
713 l_party_id Number;
714 l_party_number varchar2(30);
715 l_party_name Varchar2(360);
716 l_msg_data Varchar2(2000);
717 l_msg_count Number;
718 l_return_status Varchar2(1);
719 x_profile_id Number;
720 l_duplicate_account Number;
721 l_no_record_exists BOOLEAN := TRUE;
722
723 -- Following cursor will fetch the data for the passed ref and type --
724 -- information. For the account creation. --
725 -- {
726 Cursor l_customer_info_cur Is
727 Select party_number,
728 organization_name,
729 person_first_name,
730 person_last_name,
731 person_middle_name,
732 person_name_suffix,
733 person_title,
734 customer_type,
735 email_address,
736 phone_area_code,
737 phone_number,
738 phone_extension,
739 new_account_id,
740 new_account_number,
741 new_party_id,
742 customer_number,
743 attribute_category,
744 attribute1,
745 attribute2,
746 attribute3,
747 attribute4,
748 attribute5,
749 attribute6,
750 attribute7,
751 attribute8,
752 attribute9,
753 attribute10,
754 attribute11,
755 attribute12,
756 attribute13,
757 attribute14,
758 attribute15,
759 attribute16,
760 attribute17,
761 attribute18,
762 attribute19,
763 attribute20,
764 global_attribute_category,
765 global_attribute1,
766 global_attribute2,
767 global_attribute3,
768 global_attribute4,
769 global_attribute5,
770 global_attribute6,
771 global_attribute7,
772 global_attribute8,
773 global_attribute9,
774 global_attribute10,
775 global_attribute11,
776 global_attribute12,
777 global_attribute13,
778 global_attribute14,
779 global_attribute15,
780 global_attribute16,
781 global_attribute17,
782 global_attribute18,
783 global_attribute19,
784 global_attribute20,
785 rowid,
786 phone_country_code
787 from oe_customer_info_iface_all
788 where customer_info_ref = l_customer_info_ref
789 and customer_info_type_code = 'ACCOUNT';
790
791 -- End of Cursor definition for l_customer_info_cur }
792 --
793 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
794 --
795 Begin
796
797 IF l_debug_level > 0 THEN
798 oe_debug_pub.add( 'ENTERING IN CREATE_ACCOUNT PROCEDURE' ) ;
799 END IF;
800 x_return_status := FND_API.G_RET_STS_SUCCESS;
801 x_existing_value := 'N';
802
803 --{ If to check whether Add Customer privilege is set
804 If OE_ORDER_IMPORT_SPECIFIC_PVT.G_ONT_ADD_CUSTOMER = 'Y' Then
805 IF l_debug_level > 0 THEN
806 oe_debug_pub.add( 'ADD CUSTOMER PRIVILEGE IS THERE' ) ;
807 END IF;
808 OE_MSG_PUB.set_msg_context(
809 p_entity_code => 'OI_INL_ADDCUST'
810 ,p_entity_ref => null
811 ,p_entity_id => null
812 ,p_header_id => null
813 ,p_line_id => null
814 -- ,p_batch_request_id => p_x_header_rec.request_id
815 ,p_order_source_id => p_order_source_id
816 ,p_orig_sys_document_ref => p_orig_sys_document_ref
817 ,p_change_sequence => null
818 ,p_orig_sys_document_line_ref => p_orig_sys_line_ref
819 ,p_orig_sys_shipment_ref => p_customer_info_ref
820 ,p_source_document_type_id => null
821 ,p_source_document_id => null
822 ,p_source_document_line_id => null
823 ,p_attribute_code => null
824 ,p_constraint_id => null
825 );
826
827 -- { Start Customer Loop
828 For customer_rec In l_customer_info_cur Loop
829 -- { Start of Begin for Loop
830 Begin
831 l_no_record_exists := FALSE;
832 -- Check if the Data is already used to create the New
833 -- Customer then return that value and exit out of the
834 -- process
835
836 IF l_debug_level > 0 THEN
837 oe_debug_pub.add( 'INSIDE LOOP FOR THE CUSTOMER_INFO_CUR' ) ;
838 END IF;
839
840 If customer_rec.New_Account_Id Is Not Null Then
841 IF l_debug_level > 0 THEN
842 oe_debug_pub.add( 'NEW ACCOUNT ID IS THERE , RETURING THE EXISTING VAL' ) ;
843 END IF;
844 x_cust_account_id := customer_rec.New_Account_id;
845 x_cust_account_number := customer_rec.New_Account_number;
846 x_cust_party_id := customer_rec.New_Party_Id;
847 x_existing_value := 'Y';
848 IF l_debug_level > 0 THEN
849 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH CURRENT VAL' ) ;
850 END IF;
851 return;
852 End If;
853
854 -- { Start Check for the Duplidate Account information
855 If customer_rec.customer_type = 'ORGANIZATION' Then
856 l_duplicate_account := Oe_value_to_id.sold_to_org(
857 p_sold_to_org => customer_rec.organization_name,
858 p_customer_number => customer_rec.customer_number);
859 Elsif customer_rec.customer_type = 'PERSON' Then
860 l_duplicate_account := oe_value_to_id.sold_to_org(
861 p_sold_to_org => customer_rec.person_first_name || ' ' ||
862 customer_rec.person_last_name,
863 p_customer_number => customer_rec.customer_number);
864 End If;
865 If l_duplicate_account <> FND_API.G_MISS_NUM Then
866 -- Raise Error and Abort Processing
867 IF l_debug_level > 0 THEN
868 oe_debug_pub.add( 'TRYING TO ENTER THE ACCOUNT WHICH ALREADY EXISTS' ) ;
869 END IF;
870 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
871 fnd_message.set_token('TYPE', 'ACCOUNT');
872 fnd_message.set_token('REFERENCE', p_customer_info_ref);
873 oe_msg_pub.add;
874 x_return_status := FND_API.G_RET_STS_ERROR;
875 Update_Error_Flag(p_rowid => customer_rec.rowid);
876 Return;
877 Else
878 -- As the data is not duplicate but the call to oe_value_to_id
879 -- has entered one error message in stack(necessary evil!)
880 -- What to do => here is solution delete it
881 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
882 End if; -- Duplicate account check
883 -- End Check for the Duplicate Information }
884
885 -- { Start Check for the Required Information
886 If G_EMAIL_REQUIRED = 'Y' and
887 customer_rec.email_address is Null Then
888 -- Raise Error and Abort Processing
889 IF l_debug_level > 0 THEN
890 oe_debug_pub.add( 'EMAIL REQUIRED BUT NOT ENTERED' ) ;
891 END IF;
892 fnd_message.set_name('ONT','ONT_OI_INL_REQD');
893 fnd_message.set_token('API_NAME', 'Create_Account');
894 fnd_message.set_token('FIELD_REQD', 'EMAIL_ADDRESS');
895 oe_msg_pub.add;
896 x_return_status := FND_API.G_RET_STS_ERROR;
897 Update_Error_Flag(p_rowid => customer_rec.rowid);
898 End If; -- If G_EMAIL_REQUIRED
899
900 If G_AUTO_PARTY_NUMBERING = 'N' and
901 customer_rec.party_number is Null Then
902 -- Raise Error and Abort Processing
903 IF l_debug_level > 0 THEN
904 oe_debug_pub.add( 'PARTY NUMBER REQUIRED BUT NOT ENTERED' ) ;
905 END IF;
906 fnd_message.set_name('ONT','ONT_OI_INL_REQD');
907 fnd_message.set_token('API_NAME', 'Create_Account');
908 fnd_message.set_token('FIELD_REQD', 'PARTY_NUMBER');
909 oe_msg_pub.add;
910 x_return_status := FND_API.G_RET_STS_ERROR;
911 Update_Error_Flag(p_rowid => customer_rec.rowid);
912 End If; -- If G_AUTO_PARTY_NUMBERING
913
914 If G_AUTO_CUST_NUMBERING = 'N' and
915 customer_rec.customer_number is Null Then
916 -- Raise Error and Abort Processing
917 IF l_debug_level > 0 THEN
918 oe_debug_pub.add( 'CUSTOMER NUMBER REQUIRED BUT NOT ENTERED' ) ;
919 END IF;
920 fnd_message.set_name('ONT','ONT_OI_INL_REQD');
921 fnd_message.set_token('API_NAME', 'Create_Account');
922 fnd_message.set_token('FIELD_REQD', 'CUSTOMER_NUMBER');
923 oe_msg_pub.add;
924 x_return_status := FND_API.G_RET_STS_ERROR;
925 Update_Error_Flag(p_rowid => customer_rec.rowid);
926 End If; -- If G_AUTO_CUST_NUMBERING
927 -- End Check for the Required Information }
928
929 -- { Start Check to see if the Party Number is passed
930 -- if yes then see if it is new or already exists
931
932 If customer_rec.party_number is Not Null Then
933
934 Validate_Party_Number(
935 p_party_number => customer_rec.party_number,
936 p_party_type => customer_rec.customer_type,
937 x_party_id => l_party_id,
938 x_party_name => l_party_name,
939 x_return_status => l_return_status);
940 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
941 x_return_status := l_return_status;
942 IF l_debug_level > 0 THEN
943 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
944 END IF;
945 return;
946 End If;
947 End If;
948
949 -- End Check to see if the Party Number is passed}
950
951 IF l_debug_level > 0 THEN
952 oe_debug_pub.add( 'PARTY NAME => ' || L_PARTY_NAME ) ;
953 END IF;
954 -- { Start if
955 If customer_rec.person_first_name is not Null And
956 customer_rec.organization_name is not Null Then
957
958 IF l_debug_level > 0 THEN
959 oe_debug_pub.add( 'BOTH PERSON AND ORGANIZATION INFORMATION CAN NOT BE POPULATED TOGETHER , POPULATE WHAT YOU ARE CREATING' ) ;
960 END IF;
961 fnd_message.set_name('ONT','ONT_OI_INL_BOTH_PARTY_CUST');
962 oe_msg_pub.add;
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 IF l_debug_level > 0 THEN
965 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
966 END IF;
967 Update_Error_Flag(p_rowid => customer_rec.rowid);
968 Return;
969 End if;
970 -- End If }
971
972 -- Assign values to l_account_rec which will be passed to hz
973 -- api to create account
974
975 IF l_debug_level > 0 THEN
976 oe_debug_pub.add( 'START ASSIGNING THE COLUMNS TO THE HZ RECORD STRUCTURE' ) ;
977 END IF;
978
979 l_account_rec.attribute_category := customer_rec.attribute_category;
980 l_account_rec.attribute1 := customer_rec.attribute1;
981 l_account_rec.attribute2 := customer_rec.attribute2;
982 l_account_rec.attribute3 := customer_rec.attribute3;
983 l_account_rec.attribute4 := customer_rec.attribute4;
984 l_account_rec.attribute5 := customer_rec.attribute5;
985 l_account_rec.attribute6 := customer_rec.attribute6;
986 l_account_rec.attribute7 := customer_rec.attribute7;
987 l_account_rec.attribute8 := customer_rec.attribute8;
988 l_account_rec.attribute9 := customer_rec.attribute9;
989 l_account_rec.attribute10 := customer_rec.attribute10;
990 l_account_rec.attribute11 := customer_rec.attribute11;
991 l_account_rec.attribute12 := customer_rec.attribute12;
992 l_account_rec.attribute13 := customer_rec.attribute13;
993 l_account_rec.attribute14 := customer_rec.attribute14;
994 l_account_rec.attribute15 := customer_rec.attribute15;
995 l_account_rec.attribute16 := customer_rec.attribute16;
996 l_account_rec.attribute17 := customer_rec.attribute17;
997 l_account_rec.attribute18 := customer_rec.attribute18;
998 l_account_rec.attribute19 := customer_rec.attribute19;
999 l_account_rec.attribute20 := customer_rec.attribute20;
1000
1001 l_account_rec.global_attribute_category :=
1002 customer_rec.global_attribute_category;
1003 l_account_rec.global_attribute1 := customer_rec.global_attribute1;
1004 l_account_rec.global_attribute2 := customer_rec.global_attribute2;
1005 l_account_rec.global_attribute3 := customer_rec.global_attribute3;
1006 l_account_rec.global_attribute4 := customer_rec.global_attribute4;
1007 l_account_rec.global_attribute5 := customer_rec.global_attribute5;
1008 l_account_rec.global_attribute6 := customer_rec.global_attribute6;
1009 l_account_rec.global_attribute7 := customer_rec.global_attribute7;
1010 l_account_rec.global_attribute8 := customer_rec.global_attribute8;
1011 l_account_rec.global_attribute9 := customer_rec.global_attribute9;
1012 l_account_rec.global_attribute10 := customer_rec.global_attribute10;
1013 l_account_rec.global_attribute11 := customer_rec.global_attribute11;
1014 l_account_rec.global_attribute12 := customer_rec.global_attribute12;
1015 l_account_rec.global_attribute13 := customer_rec.global_attribute13;
1016 l_account_rec.global_attribute14 := customer_rec.global_attribute14;
1017 l_account_rec.global_attribute15 := customer_rec.global_attribute15;
1018 l_account_rec.global_attribute16 := customer_rec.global_attribute16;
1019 l_account_rec.global_attribute17 := customer_rec.global_attribute17;
1020 l_account_rec.global_attribute18 := customer_rec.global_attribute18;
1021 l_account_rec.global_attribute19 := customer_rec.global_attribute19;
1022 l_account_rec.global_attribute20 := customer_rec.global_attribute20;
1023
1024 -- This information will have the customer_number is the auto
1025 -- generation of the account number is set to 'N'.
1026 -- ar_system_parameters.generate_customer_number is the column
1027 -- to check for the value
1028 -- If the value is 'N' and interface table do not have value
1029 -- for the New_Account_Number then the call to hz api will return
1030 -- error that the value need to be passed and that will be displayed
1031 -- in log file and as well as error form.
1032 l_account_rec.account_number := customer_rec.new_account_number;
1033 l_account_rec.created_by_module := G_CREATED_BY_MODULE;
1034 l_account_rec.application_id := 660;
1035
1036 IF l_debug_level > 0 THEN
1037 oe_debug_pub.add( 'END ASSIGNING THE COLUMNS TO THE HZ ACCOUNT STRUCTURE' ) ;
1038 END IF;
1039
1040 -- { Start of the If for the customer_type condition
1041 -- Type is PERSON
1042 If customer_rec.customer_type = 'PERSON' Then
1043
1044 IF l_debug_level > 0 THEN
1045 oe_debug_pub.add( 'INSIDE CUSTOMER_TYPE PERSON' ) ;
1046 END IF;
1047 l_person_rec.person_first_name := customer_rec.person_first_name;
1048 l_person_rec.person_last_name := customer_rec.person_last_name;
1049 l_person_rec.person_middle_name := customer_rec.person_middle_name;
1050 l_person_rec.person_name_suffix := customer_rec.person_name_suffix;
1051 l_person_rec.person_pre_name_adjunct := customer_rec.person_title;
1052 l_party_rec.party_number := customer_rec.party_number;
1053 If customer_rec.party_number Is Not Null And
1054 l_party_id is Not Null Then
1055 l_party_rec.party_id := l_party_id;
1056 End If;
1057
1058 l_person_rec.party_rec := l_party_rec;
1059
1060 IF l_debug_level > 0 THEN
1061 oe_debug_pub.add( 'BEFORE CALL TO CREATE_ACCOUNT FOR TYPE PERSON' ) ;
1062 END IF;
1063 -- { Start Call hz api to create customer account
1064 HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Account
1065 (
1066 p_person_rec => l_person_rec,
1067 p_cust_account_rec => l_account_rec,
1068 p_customer_profile_rec => l_cust_profile_rec,
1069 x_party_id => x_cust_party_id,
1070 x_party_number => l_party_number,
1071 x_cust_account_id => x_cust_account_id,
1072 x_account_number => x_cust_account_number,
1073 x_profile_id => x_profile_id,
1074 x_return_status => l_return_status,
1075 x_msg_count => l_msg_count,
1076 x_msg_data => l_msg_data
1077 );
1078 -- End Call hz api to create customer account}
1079
1080 -- Let us check the status of the call to hz api
1081 IF l_debug_level > 0 THEN
1082 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
1083 END IF;
1084 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1085 IF l_debug_level > 0 THEN
1086 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
1087 END IF;
1088 IF l_debug_level > 0 THEN
1089 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
1090 END IF;
1091 x_return_status := l_return_status;
1092 IF l_debug_level > 0 THEN
1093 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1094 END IF;
1095 Update_Error_Flag(p_rowid => customer_rec.rowid);
1096 oe_msg_pub.transfer_msg_stack;
1097 fnd_msg_pub.delete_msg;
1098 return;
1099 Else
1100 IF l_debug_level > 0 THEN
1101 oe_debug_pub.add( 'NEW CUSTOMER NUMBER => ' || X_CUST_ACCOUNT_NUMBER ) ;
1102 END IF;
1103 IF l_debug_level > 0 THEN
1104 oe_debug_pub.add( 'NEW CUSTOMER ID => ' || X_CUST_ACCOUNT_ID ) ;
1105 END IF;
1106 IF l_debug_level > 0 THEN
1107 oe_debug_pub.add( 'NEW PARTY ID => ' || X_CUST_PARTY_ID ) ;
1108 END IF;
1109
1110 Update oe_customer_info_iface_all
1111 Set New_Party_Id = x_cust_party_id,
1112 New_Party_Number = l_party_number,
1113 New_Account_Id = x_cust_account_id,
1114 New_Account_Number = x_cust_account_Number
1115 Where rowid = customer_rec.rowid;
1116
1117 End if;
1118
1119 -- Now we have our account created, so we are set to create the
1120 -- contact point information for that account, i.e., phone and
1121 -- email inforation.
1122 -- { Start for create contact point EMAIL
1123 If customer_rec.email_address is Not Null Then
1124 Create_Contact_Point(
1125 p_contact_point_type => 'EMAIL',
1126 p_owner_table_id => x_cust_party_id,
1127 p_email => customer_rec.email_address,
1128 p_phone_area_code => NULL,
1129 p_phone_number => NULL,
1130 p_phone_extension => NULL,
1131 p_phone_country_code => NULL,
1132 x_return_status => l_return_status,
1133 x_msg_count => l_msg_count,
1134 x_msg_data => l_msg_data
1135 );
1136 -- Let us check the status of the call to hz api
1137 IF l_debug_level > 0 THEN
1138 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
1139 END IF;
1140 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1141 IF l_debug_level > 0 THEN
1142 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM CONTACT EML '|| L_MSG_DATA ) ;
1143 END IF;
1144 x_return_status := l_return_status;
1145 IF l_debug_level > 0 THEN
1146 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1147 END IF;
1148 -- Update_Error_Flag(p_rowid => customer_rec.rowid);
1149 return;
1150 Else
1151 IF l_debug_level > 0 THEN
1152 oe_debug_pub.add( 'NEW EMAIL => ' || CUSTOMER_REC.EMAIL_ADDRESS ) ;
1153 END IF;
1154 End if;
1155
1156 End If;
1157 -- End for create contact point EMAIL}
1158
1159 -- { Start for create contact point PHONE
1160 If customer_rec.phone_number is Not Null Then
1161 Create_Contact_Point(
1162 p_contact_point_type => 'PHONE',
1163 p_owner_table_id => x_cust_party_id,
1164 p_email => NULL,
1165 p_phone_area_code => customer_rec.phone_area_code,
1166 p_phone_number => customer_rec.phone_number,
1167 p_phone_extension => customer_rec.phone_extension,
1168 p_phone_country_code => customer_rec.phone_country_code,
1169 x_return_status => l_return_status,
1170 x_msg_count => l_msg_count,
1171 x_msg_data => l_msg_data
1172 );
1173 -- Let us check the status of the call to hz api
1174 IF l_debug_level > 0 THEN
1175 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
1176 END IF;
1177 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1178 IF l_debug_level > 0 THEN
1179 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM CONTACT PH '|| L_MSG_DATA ) ;
1180 END IF;
1181 x_return_status := l_return_status;
1182 IF l_debug_level > 0 THEN
1183 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1184 END IF;
1185 -- Update_Error_Flag(p_rowid => customer_rec.rowid);
1186 return;
1187 Else
1188 IF l_debug_level > 0 THEN
1189 oe_debug_pub.add( 'NEW PHONE => ' || CUSTOMER_REC.PHONE_NUMBER ) ;
1190 END IF;
1191 End if;
1192
1193 End If;
1194 -- End for create contact point PHONE}
1195
1196 -- Type is ORGANIZATION
1197 Elsif customer_rec.customer_type = 'ORGANIZATION' Then
1198
1199 IF l_debug_level > 0 THEN
1200 oe_debug_pub.add( 'INSIDE CUSTOMER_TYPE ORGANIZATION' ) ;
1201 END IF;
1202 l_organization_rec.organization_name := customer_rec.organization_name;
1203 -- ??? l_organization_rec.organization_name_phonetic := p_alternate_name;
1204 -- ??? l_organization_rec.tax_reference:=p_tax_reference;
1205 -- ??? l_organization_rec.jgzz_fiscal_code:=p_taxpayer_id;
1206 l_party_rec.party_number := customer_rec.party_number;
1207
1208 If customer_rec.party_number Is Not Null And
1209 l_party_id is Not Null Then
1210 l_party_rec.party_id := l_party_id;
1211 End If;
1212 l_organization_rec.party_rec := l_party_rec;
1213
1214 IF l_debug_level > 0 THEN
1215 oe_debug_pub.add( 'BEFORE CALL TO CREATE_ACCOUNT FOR TYPE ORGANIZATION' ) ;
1216 END IF;
1217 -- { Start Call hz api to create customer account type Organization
1218 HZ_CUST_ACCOUNT_V2PUB.Create_Cust_Account
1219 (
1220 p_organization_rec => l_organization_rec,
1221 p_cust_account_rec => l_account_rec,
1222 p_customer_profile_rec => l_cust_profile_rec,
1223 x_party_id => x_cust_party_id,
1224 x_party_number => l_party_number,
1225 x_cust_account_id => x_cust_account_id,
1226 x_account_number => x_cust_account_number,
1227 x_profile_id => x_profile_id,
1228 x_return_status => l_return_status,
1229 x_msg_count => l_msg_count,
1230 x_msg_data => l_msg_data
1231 );
1232 -- End Call hz api to create customer account type Organization}
1233
1234 -- Let us check the status of the call to hz api
1235 IF l_debug_level > 0 THEN
1236 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
1237 END IF;
1238 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1239 IF l_debug_level > 0 THEN
1240 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
1241 END IF;
1242 IF l_debug_level > 0 THEN
1243 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
1244 END IF;
1245 x_return_status := l_return_status;
1246 IF l_debug_level > 0 THEN
1247 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1248 END IF;
1249 Update_Error_Flag(p_rowid => customer_rec.rowid);
1250 oe_msg_pub.transfer_msg_stack;
1251 fnd_msg_pub.delete_msg;
1252 return;
1253 Else
1254 IF l_debug_level > 0 THEN
1255 oe_debug_pub.add( 'NEW CUSTOMER NUMBER => ' || X_CUST_ACCOUNT_NUMBER ) ;
1256 END IF;
1257 IF l_debug_level > 0 THEN
1258 oe_debug_pub.add( 'NEW CUSTOMER ID => ' || X_CUST_ACCOUNT_ID ) ;
1259 END IF;
1260 IF l_debug_level > 0 THEN
1261 oe_debug_pub.add( 'NEW PARTY ID => ' || X_CUST_PARTY_ID ) ;
1262 END IF;
1263
1264 Update oe_customer_info_iface_all
1265 Set New_Party_Id = x_cust_party_id,
1266 New_Party_Number = l_party_number,
1267 New_Account_Id = x_cust_account_id,
1268 New_Account_Number = x_cust_account_Number
1269 Where rowid = customer_rec.rowid;
1270
1271 End if;
1272
1273 -- Now we have our account created, so we are set to create the
1274 -- contact point information for that account, i.e., phone and
1275 -- email inforation.
1276 -- { Start for create contact point EMAIL
1277 If customer_rec.email_address is Not Null Then
1278 Create_Contact_Point(
1279 p_contact_point_type => 'EMAIL',
1280 p_owner_table_id => x_cust_party_id,
1281 p_email => customer_rec.email_address,
1282 p_phone_area_code => NULL,
1283 p_phone_number => NULL,
1284 p_phone_extension => NULL,
1285 p_phone_country_code => NULL,
1286 x_return_status => l_return_status,
1287 x_msg_count => l_msg_count,
1288 x_msg_data => l_msg_data
1289 );
1290 -- Let us check the status of the call to hz api
1291 IF l_debug_level > 0 THEN
1292 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
1293 END IF;
1294 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1295 IF l_debug_level > 0 THEN
1296 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM CONTACT EML '|| L_MSG_DATA ) ;
1297 END IF;
1298 x_return_status := l_return_status;
1299 IF l_debug_level > 0 THEN
1300 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1301 END IF;
1302 -- Update_Error_Flag(p_rowid => customer_rec.rowid);
1303 return;
1304 Else
1305 IF l_debug_level > 0 THEN
1306 oe_debug_pub.add( 'NEW EMAIL => ' || CUSTOMER_REC.EMAIL_ADDRESS ) ;
1307 END IF;
1308 End if;
1309
1310 End If;
1311 -- End for create contact point EMAIL}
1312
1313 -- { Start for create contact point PHONE
1314 If customer_rec.phone_number is Not Null Then
1315 Create_Contact_Point(
1316 p_contact_point_type => 'PHONE',
1317 p_owner_table_id => x_cust_party_id,
1318 p_email => NULL,
1319 p_phone_area_code => customer_rec.phone_area_code,
1320 p_phone_number => customer_rec.phone_number,
1321 p_phone_extension => customer_rec.phone_extension,
1322 p_phone_country_code => customer_rec.phone_country_code,
1323 x_return_status => l_return_status,
1324 x_msg_count => l_msg_count,
1325 x_msg_data => l_msg_data
1326 );
1327 -- Let us check the status of the call to hz api
1328 IF l_debug_level > 0 THEN
1329 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
1330 END IF;
1331 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1332 IF l_debug_level > 0 THEN
1333 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM CONTACT PH '|| L_MSG_DATA ) ;
1334 END IF;
1335 x_return_status := l_return_status;
1336 IF l_debug_level > 0 THEN
1337 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1338 END IF;
1339 -- Update_Error_Flag(p_rowid => customer_rec.rowid);
1340 return;
1341 Else
1342 IF l_debug_level > 0 THEN
1343 oe_debug_pub.add( 'NEW PHONE => ' || CUSTOMER_REC.PHONE_NUMBER ) ;
1344 END IF;
1345 End if;
1346
1347 End If;
1348 -- End for create contact point PHONE}
1349
1350 Else
1351 -- Wrong Type is passed Error out
1352 IF l_debug_level > 0 THEN
1353 oe_debug_pub.add( 'WRONG TYPE OF CUSTOMER INFORMATION PASSED.' ) ;
1354 END IF;
1355 x_return_status := FND_API.G_RET_STS_ERROR;
1356 fnd_message.set_name('ONT','ONT_OI_INL_INV_CUST_TYPE');
1357 oe_msg_pub.add;
1358 IF l_debug_level > 0 THEN
1359 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1360 END IF;
1361 Update_Error_Flag(p_rowid => customer_rec.rowid);
1362 Return;
1363 End If;
1364 IF l_debug_level > 0 THEN
1365 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE ' ) ;
1366 END IF;
1367 -- End of the If for the customer_type condition}
1368 Exception
1369 When Others Then
1370 IF l_debug_level > 0 THEN
1371 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_ACCOUNT. ABORT PROCESSING' ) ;
1372 END IF;
1373 IF l_debug_level > 0 THEN
1374 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
1375 END IF;
1376 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
1377 fnd_message.set_token('API_NAME', 'Create_Account');
1378 oe_msg_pub.add;
1379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1380 Update_Error_Flag(p_rowid => customer_rec.rowid);
1381 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
1382 IF l_debug_level > 0 THEN
1383 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1384 END IF;
1385 End;
1386 -- End of Begin after Loop }
1387 End Loop;
1388 -- End Customer Loop }
1389 If l_no_record_exists Then
1390 IF l_debug_level > 0 THEN
1391 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED REF , PLEASE CHECK DATA' ) ;
1392 END IF;
1393 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
1394 fnd_message.set_token('REFERENCE', p_customer_info_ref);
1395 oe_msg_pub.add;
1396 x_return_status := FND_API.G_RET_STS_ERROR;
1397 IF l_debug_level > 0 THEN
1398 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1399 END IF;
1400 End If;
1401
1402 Else
1403 IF l_debug_level > 0 THEN
1404 oe_debug_pub.add( 'ADD CUSTOMER PRIVILEGE IS NOT THERE' ) ;
1405 END IF;
1406 fnd_message.set_name('ONT','ONT_OI_INL_SET_PARAMETER');
1407 fnd_message.set_token('TYPE', 'Customers');
1408 oe_msg_pub.add;
1409 x_return_status := FND_API.G_RET_STS_ERROR;
1410 End If;
1411 -- End If to check whether Add Customer privilege is set}
1412 Exception
1413 When Others Then
1414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1415 IF l_debug_level > 0 THEN
1416 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_ACCOUNT. ABORT PROCESSING' ) ;
1417 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
1418 END IF;
1419 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
1420 fnd_message.set_token('API_NAME', 'Create_Account');
1421 oe_msg_pub.add;
1422 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
1423 IF l_debug_level > 0 THEN
1424 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1425 END IF;
1426 End Create_Account;
1427 -- End of procedure Create_Account}
1428
1429
1430 -- { Start procedure validate_customer_number
1431 Procedure Validate_Customer_Number(p_customer_number In VARCHAR2,
1432 x_party_id Out NOCOPY /* file.sql.39 change */ NUMBER,
1433 x_account_id Out NOCOPY /* file.sql.39 change */ NUMBER,
1434 x_return_status Out NOCOPY /* file.sql.39 change */ VARCHAR2
1435 )
1436 Is
1437 --
1438 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1439 --
1440 Begin
1441
1442 IF l_debug_level > 0 THEN
1443 oe_debug_pub.add( 'ENTERNING VALIDATE_CUSTOMER_NUMBER API' ) ;
1444 END IF;
1445 Select party_id, cust_account_id
1446 Into x_party_id, x_account_id
1447 From hz_cust_accounts
1448 Where account_number = p_customer_number;
1449 x_return_status := FND_API.G_RET_STS_SUCCESS;
1450
1451 IF l_debug_level > 0 THEN
1452 oe_debug_pub.add( 'EXITING VALIDATE_CUSTOMER_NUMBER API' ) ;
1453 END IF;
1454 Exception
1455 When NO_DATA_FOUND Then
1456 x_return_status := FND_API.G_RET_STS_ERROR;
1457 IF l_debug_level > 0 THEN
1458 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED EXISTING CUSTOMER , PLEASE CHECK DATA' ) ;
1459 END IF;
1460 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
1461 fnd_message.set_token('REFERENCE', p_customer_number);
1462 oe_msg_pub.add;
1463 IF l_debug_level > 0 THEN
1464 oe_debug_pub.add( 'EXITING IN CREATE_ACCOUNT PROCEDURE WITH ERROR' ) ;
1465 END IF;
1466 End validate_customer_number;
1467
1468
1469 -- { Start procedure Create Address
1470 PROCEDURE Create_Address(p_customer_info_ref IN Varchar2,
1471 p_type_of_address IN Varchar2,
1472 p_orig_sys_document_ref IN Varchar2,
1473 p_orig_sys_line_ref IN Varchar2,
1474 p_order_source_id IN Number,
1475 p_org_id IN Number,
1476 x_usage_site_id OUT NOCOPY /* file.sql.39 change */ Number,
1477 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
1478 )
1479 IS
1480
1481 l_customer_info_ref Varchar2(50) := p_customer_info_ref;
1482 CURSOR address_info_cur IS
1483 Select parent_customer_ref,
1484 current_customer_number,
1485 current_customer_id,
1486 country,
1487 address1,
1488 address2,
1489 address3,
1490 address4,
1491 city,
1492 postal_code,
1493 state,
1494 province,
1495 county,
1496 is_ship_to_address,
1497 is_bill_to_address,
1498 is_deliver_to_address,
1499 new_address_id_ship,
1500 new_address_id_bill,
1501 new_address_id_deliver,
1502 location_number,
1503 site_number,
1504 attribute_category,
1505 attribute1,
1506 attribute2,
1507 attribute3,
1508 attribute4,
1509 attribute5,
1510 attribute6,
1511 attribute7,
1512 attribute8,
1513 attribute9,
1514 attribute10,
1515 attribute11,
1516 attribute12,
1517 attribute13,
1518 attribute14,
1519 attribute15,
1520 attribute16,
1521 attribute17,
1522 attribute18,
1523 attribute19,
1524 attribute20,
1525 global_attribute_category,
1526 global_attribute1,
1527 global_attribute2,
1528 global_attribute3,
1529 global_attribute4,
1530 global_attribute5,
1531 global_attribute6,
1532 global_attribute7,
1533 global_attribute8,
1534 global_attribute9,
1535 global_attribute10,
1536 global_attribute11,
1537 global_attribute12,
1538 global_attribute13,
1539 global_attribute14,
1540 global_attribute15,
1541 global_attribute16,
1542 global_attribute17,
1543 global_attribute18,
1544 global_attribute19,
1545 global_attribute20,
1546 rowid
1547 From oe_customer_info_iface_all
1548 WHERE customer_info_ref = l_customer_info_ref
1549 AND customer_info_type_code = 'ADDRESS';
1550
1551 l_customer_info_id Number;
1552 l_customer_info_number Varchar2(30);
1553 l_customer_party_id Number;
1554 l_return_status Varchar2(1);
1555 l_location_rec HZ_LOCATION_V2PUB.location_rec_type;
1556 l_msg_count Number;
1557 l_msg_data Varchar2(4000);
1558 l_location_id Number;
1559 l_party_site_rec HZ_PARTY_SITE_V2PUB.party_site_rec_type;
1560 --g_auto_site_numbering Varchar2(1);
1561 --g_auto_location_numbering Varchar2(1);
1562 l_party_site_id Number;
1563 l_party_site_number Varchar2(80);
1564 l_account_site_rec HZ_CUST_ACCOUNT_SITE_V2PUB.cust_acct_site_rec_type;
1565 l_customer_site_id Number;
1566 l_acct_site_uses HZ_CUST_ACCOUNT_SITE_V2PUB.cust_site_use_rec_type;
1567 l_cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
1568 l_site_use_id_ship Number;
1569 l_site_use_id_bill Number;
1570 l_site_use_id_deliver Number;
1571 l_location_number Varchar2(40);
1572 l_address_style Varchar2(40);
1573 l_site_number Varchar2(80);
1574 l_existing_value Varchar2(1) := 'N';
1575 l_no_record_exists BOOLEAN := TRUE;
1576 l_ship_to_org Varchar2(240) := 'Dummy';
1577 l_duplicate_address Number;
1578 l_site_id_exists Varchar2(1);
1579 l_val_addr Varchar2(1):= 'Y'; ---bug 7299729
1580 l_temp_Usage_to_cust_id NUMBER DEFAULT NULL; --bug 7299729
1581 --
1582 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1583 --
1584 Begin
1585 IF l_debug_level > 0 THEN
1586 oe_debug_pub.add( 'ENTERING PROCEDURE CREATE ADDRESS' ) ;
1587 END IF;
1588
1589 x_return_status := FND_API.G_RET_STS_SUCCESS;
1590
1591 OE_MSG_PUB.set_msg_context(
1592 p_entity_code => 'OI_INL_ADDCUST'
1593 ,p_entity_ref => null
1594 ,p_entity_id => null
1595 ,p_header_id => null
1596 ,p_line_id => null
1597 -- ,p_batch_request_id => p_x_header_rec.request_id
1598 ,p_order_source_id => p_order_source_id
1599 ,p_orig_sys_document_ref => p_orig_sys_document_ref
1600 ,p_change_sequence => null
1601 ,p_orig_sys_document_line_ref => p_orig_sys_line_ref
1602 ,p_orig_sys_shipment_ref => p_customer_info_ref
1603 ,p_source_document_type_id => null
1604 ,p_source_document_id => null
1605 ,p_source_document_line_id => null
1606 ,p_attribute_code => null
1607 ,p_constraint_id => null
1608 );
1609
1610 IF l_debug_level > 0 THEN
1611 oe_debug_pub.add( 'TYPE_OF_USAGE => ' || P_TYPE_OF_ADDRESS ) ;
1612 END IF;
1613 FOR address_info_rec IN address_info_cur LOOP
1614 BEGIN
1615 l_no_record_exists := FALSE;
1616 If p_type_of_address = 'SHIP_TO' and
1617 nvl(address_info_rec.is_ship_to_address, 'N') <> 'Y' Then
1618 IF l_debug_level > 0 THEN
1619 oe_debug_pub.add( 'USAGE IS NOT SET FOR SHIP_TO. PLEASE CORRECT DATA' ) ;
1620 END IF;
1621 fnd_message.set_name('ONT','ONT_OI_INL_NO_USAGE_SET');
1622 fnd_message.set_token('USAGE', 'SHIP_TO');
1623 fnd_message.set_token('REFERENCE', p_customer_info_ref);
1624 oe_msg_pub.add;
1625 x_return_status := FND_API.G_RET_STS_ERROR;
1626 Return;
1627 Elsif p_type_of_address = 'BILL_TO' and
1628 nvl(address_info_rec.is_bill_to_address, 'N') <> 'Y' Then
1629 IF l_debug_level > 0 THEN
1630 oe_debug_pub.add( 'USAGE IS NOT SET FOR BILL_TO. PLEASE CORRECT DATA' ) ;
1631 END IF;
1632 fnd_message.set_name('ONT','ONT_OI_INL_NO_USAGE_SET');
1633 fnd_message.set_token('USAGE', 'BILL_TO');
1634 fnd_message.set_token('REFERENCE', p_customer_info_ref);
1635 oe_msg_pub.add;
1636 x_return_status := FND_API.G_RET_STS_ERROR;
1637 Return;
1638 Elsif p_type_of_address = 'DELIVER_TO' and
1639 nvl(address_info_rec.is_deliver_to_address, 'N') <> 'Y' Then
1640 IF l_debug_level > 0 THEN
1641 oe_debug_pub.add( 'USAGE IS NOT SET FOR DELIVER_TO. PLEASE CORRECT DATA' ) ;
1642 END IF;
1643 fnd_message.set_name('ONT','ONT_OI_INL_NO_USAGE_SET');
1644 fnd_message.set_token('USAGE', 'DELIVER_TO');
1645 fnd_message.set_token('REFERENCE', p_customer_info_ref);
1646 oe_msg_pub.add;
1647 x_return_status := FND_API.G_RET_STS_ERROR;
1648 Return;
1649 End If;
1650
1651 If address_info_rec.is_ship_to_address = 'Y' AND
1652 address_info_rec.new_address_id_ship IS NOT NULL AND
1653 p_type_of_address = 'SHIP_TO' Then
1654
1655 begin --bug#5566811
1656 select 'Y' into l_site_id_exists
1657 from hz_cust_site_uses
1658 where site_use_id=address_info_rec.new_address_id_ship
1659 and status='A';
1660
1661 if l_site_id_exists='Y' then
1662 x_usage_site_id := address_info_rec.new_address_id_ship;
1663 x_return_status := FND_API.G_RET_STS_SUCCESS;
1664 IF l_debug_level > 0 THEN
1665 oe_debug_pub.add( 'RETURNING EXISING SHIP_TO ID=> '|| X_USAGE_SITE_ID ) ;
1666 END IF;
1667 RETURN;
1668
1669 end if;
1670 exception
1671 when no_data_found then
1672 x_usage_site_id:=null;
1673 end;
1674
1675
1676 Elsif address_info_rec.is_bill_to_address = 'Y' AND
1677 address_info_rec.new_address_id_bill IS NOT NULL And
1678 p_type_of_address = 'BILL_TO' Then
1679
1680 begin --bug#5566811
1681 select 'Y' into l_site_id_exists
1682 from hz_cust_site_uses
1683 where site_use_id=address_info_rec.new_address_id_bill
1684 and status='A';
1685
1686 if l_site_id_exists='Y' then
1687 x_usage_site_id := address_info_rec.new_address_id_bill;
1688 x_return_status := FND_API.G_RET_STS_SUCCESS;
1689 IF l_debug_level > 0 THEN
1690 oe_debug_pub.add( 'RETURNING EXISING BILL_TO ID=> '|| X_USAGE_SITE_ID ) ;
1691 END IF;
1692 RETURN;
1693
1694 end if;
1695 exception
1696 when no_data_found then
1697 x_usage_site_id:=null;
1698 end;
1699
1700
1701 Elsif address_info_rec.is_deliver_to_address = 'Y' AND
1702 address_info_rec.new_address_id_deliver IS NOT NULL And
1703 p_type_of_address = 'DELIVER_TO' Then
1704
1705 begin --bug#5566811
1706 select 'Y' into l_site_id_exists
1707 from hz_cust_site_uses
1708 where site_use_id=address_info_rec.new_address_id_deliver
1709 and status='A';
1710
1711 if l_site_id_exists='Y' then
1712 x_usage_site_id := address_info_rec.new_address_id_deliver;
1713 x_return_status := FND_API.G_RET_STS_SUCCESS;
1714 IF l_debug_level > 0 THEN
1715 oe_debug_pub.add( 'RETURNING EXISING DELIVER_TO ID=> '|| X_USAGE_SITE_ID ) ;
1716 END IF;
1717 RETURN;
1718
1719 end if;
1720 exception
1721 when no_data_found then
1722 x_usage_site_id:=null;
1723 end;
1724
1725
1726 END IF;
1727
1728
1729 -- {Start of OR
1730 If (address_info_rec.Current_Customer_Number IS NULL) And
1731 (address_info_rec.Current_Customer_Id IS NULL) Then
1732
1733 -- { Start of If for parent_customer_ref Null
1734 If address_info_rec.parent_customer_ref IS NULL
1735 Then
1736 IF l_debug_level > 0 THEN
1737 oe_debug_pub.add( 'PARENT_CUSTOMER_INFO_REF IS NULL' ) ;
1738 END IF;
1739 fnd_message.set_name('ONT','ONT_OI_INL_NO_PARENT_REF');
1740 fnd_message.set_token('REFERENCE', p_customer_info_ref);
1741 oe_msg_pub.add;
1742 -- call msg routine
1743 x_return_status := FND_API.G_RET_STS_ERROR;
1744 Return;
1745 Else
1746 IF l_debug_level > 0 THEN
1747 oe_debug_pub.add( 'BEFORE CALLING CREATE ACCOUNT PROCEDURE' ) ;
1748 END IF;
1749 -- call Create_Account api
1750 create_account(p_customer_info_ref => address_info_rec.parent_customer_ref,
1751 p_orig_sys_document_ref => p_orig_sys_document_ref,
1752 p_orig_sys_line_ref => p_orig_sys_line_ref,
1753 p_order_source_id => p_order_source_id,
1754 x_cust_account_id => l_customer_info_id,
1755 x_cust_account_number => l_customer_info_number,
1756 x_cust_party_id => l_customer_party_id,
1757 x_existing_value => l_existing_value,
1758 x_return_status => l_return_status
1759 );
1760 IF l_debug_level > 0 THEN
1761 oe_debug_pub.add( 'AFTER CALLING CREATE ACCOUNT PROCEDURE' ) ;
1762 END IF;
1763 IF l_debug_level > 0 THEN
1764 oe_debug_pub.add( 'CUST_ACCOUNT_ID = '||L_CUSTOMER_INFO_ID ) ;
1765 END IF;
1766 IF l_debug_level > 0 THEN
1767 oe_debug_pub.add( 'CUST_PARTY_ID = '||L_CUSTOMER_PARTY_ID ) ;
1768 END IF;
1769
1770 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1771 IF l_debug_level > 0 THEN
1772 oe_debug_pub.add( 'RETURN STATUS AFTER CREATE_ACCOUNT IS ERROR' ) ;
1773 END IF;
1774 x_return_status := l_return_status;
1775 Return;
1776 End If;
1777
1778 -- { Start of G_SOLD_TO_CUST
1779 If G_SOLD_TO_CUST is Not Null And
1780 l_existing_value <> 'Y' Then
1781 Create_Cust_Relationship(
1782 p_cust_acct_id => l_customer_info_id,
1783 p_related_cust_acct_id => G_SOLD_TO_CUST,
1784 p_org_id => p_org_id,
1785 x_return_status => l_return_status,
1786 x_msg_count => l_msg_count,
1787 x_msg_data => l_msg_data);
1788
1789 IF l_debug_level > 0 THEN
1790 oe_debug_pub.add( 'AFTER CREATE_CUST_RELATIONSHIP' ) ;
1791 END IF;
1792
1793 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1794 IF l_debug_level > 0 THEN
1795 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
1796 END IF;
1797 IF l_debug_level > 0 THEN
1798 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
1799 END IF;
1800 Update_Error_Flag(p_rowid => address_info_rec.rowid);
1801 x_return_status := l_return_status;
1802 Return;
1803 End if;
1804 End If;
1805 -- End of G_SOLD_TO_CUST}
1806
1807 End If;
1808 -- End of If for parent_customer_ref Null}
1809 Else
1810 -- {Start of Current_Customer Is Not Null
1811 If address_info_rec.Current_Customer_id IS NOT NULL Then
1812 l_customer_info_id := address_info_rec.Current_Customer_id;
1813 Begin
1814 Select party_id
1815 Into l_customer_party_id
1816 From hz_cust_accounts
1817 Where cust_account_id = l_customer_info_id;
1818 Exception
1819 When No_Data_Found Then
1820 IF l_debug_level > 0 THEN
1821 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED EXISTING CUSTOMER , PLEASE CHECK DATA' ) ;
1822 END IF;
1823 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
1824 fnd_message.set_token('REFERENCE', address_info_rec.Current_Customer_id);
1825 oe_msg_pub.add;
1826 x_return_status := FND_API.G_RET_STS_ERROR;
1827 Return;
1828 End;
1829 Else
1830 --Validate customer
1831 Validate_Customer_Number(p_customer_number => address_info_rec.Current_Customer_Number,
1832 x_party_id => l_customer_party_id,
1833 x_account_id => l_customer_info_id,
1834 x_return_status => l_return_status
1835 );
1836 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
1837 x_return_status := l_return_status;
1838 Return;
1839 End If;
1840
1841 End If;
1842 -- End of Current_Customer Is Not Null}
1843
1844 End If;
1845 -- End of OR}
1846 l_val_addr :=OE_Sys_Parameters.VALUE('OE_ADDR_VALID_OIMP'); --bug 7299729
1847 IF l_debug_level > 0 THEN
1848 oe_debug_pub.add( 'Customer validation for OI '||l_val_addr ) ;
1849 END IF;
1850
1851
1852 IF NOT (l_val_addr = 'N' ) THEN ---skip duplicate check bug 7299729
1853 IF (l_val_addr = 'S') THEN ---for single customer check bug 7299729
1854 l_temp_Usage_to_cust_id := l_customer_info_id ;
1855
1856 END IF ;
1857
1858 -- {Start of If for duplicate checking
1859 If p_type_of_address = 'SHIP_TO' Then
1860 l_duplicate_address := Oe_Value_To_Id.Ship_To_Org(
1861 p_ship_to_address1 => address_info_rec.address1,
1862 p_ship_to_address2 => address_info_rec.address2,
1863 p_ship_to_address3 => address_info_rec.address3,
1864 p_ship_to_address4 => address_info_rec.address4,
1865 p_ship_to_location => address_info_rec.location_number,
1866 p_ship_to_org => l_ship_to_org,
1867 p_sold_to_org_id => l_customer_info_id,
1868 p_ship_to_city => address_info_rec.city,
1869 p_ship_to_state => address_info_rec.state,
1870 p_ship_to_postal_code => address_info_rec.postal_code,
1871 p_ship_to_country => address_info_rec.country,
1872 p_ship_to_customer_id =>l_temp_usage_to_cust_id --bug 7299729
1873 );
1874 If l_duplicate_address <> FND_API.G_MISS_NUM Then
1875 -- Raise Error and Abort Processing
1876 IF l_debug_level > 0 THEN
1877 oe_debug_pub.add( 'TRYING TO ENTER THE SHIP TO WHICH ALREADY EXISTS' ) ;
1878 END IF;
1879 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
1880 fnd_message.set_token('TYPE', 'SHIP_TO ADDRESS');
1881 fnd_message.set_token('REFERENCE', p_customer_info_ref);
1882 oe_msg_pub.add;
1883 x_return_status := FND_API.G_RET_STS_ERROR;
1884 Update_Error_Flag(p_rowid => address_info_rec.rowid);
1885 Return;
1886 Else
1887 -- As the data is not duplicate but the call to oe_value_to_id
1888 -- has entered one error message in stack(necessary evil!)
1889 -- What to do => here is solution delete it
1890 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
1891 End if; -- duplicate ship_to_org check
1892 Elsif p_type_of_address = 'BILL_TO' Then
1893 l_duplicate_address := Oe_Value_To_Id.Invoice_To_Org(
1894 p_invoice_to_address1 => address_info_rec.address1,
1895 p_invoice_to_address2 => address_info_rec.address2,
1896 p_invoice_to_address3 => address_info_rec.address3,
1897 p_invoice_to_address4 => address_info_rec.address4,
1898 p_invoice_to_location => address_info_rec.location_number,
1899 p_invoice_to_org => l_ship_to_org,
1900 p_sold_to_org_id => l_customer_info_id,
1901 p_invoice_to_city => address_info_rec.city,
1902 p_invoice_to_state => address_info_rec.state,
1903 p_invoice_to_postal_code => address_info_rec.postal_code,
1904 p_invoice_to_country => address_info_rec.country,
1905 p_invoice_to_customer_id =>l_temp_usage_to_cust_id --bug 7299729
1906 );
1907 If l_duplicate_address <> FND_API.G_MISS_NUM Then
1908 -- Raise Error and Abort Processing
1909 IF l_debug_level > 0 THEN
1910 oe_debug_pub.add( 'TRYING TO ENTER THE BILL TO WHICH ALREADY EXISTS' ) ;
1911 END IF;
1912 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
1913 fnd_message.set_token('TYPE', 'BILL_TO ADDRESS');
1914 fnd_message.set_token('REFERENCE', p_customer_info_ref);
1915 oe_msg_pub.add;
1916 x_return_status := FND_API.G_RET_STS_ERROR;
1917 Update_Error_Flag(p_rowid => address_info_rec.rowid);
1918 Return;
1919 Else
1920 -- As the data is not duplicate but the call to oe_value_to_id
1921 -- has entered one error message in stack(necessary evil!)
1922 -- What to do => here is solution delete it
1923 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
1924 End if; -- duplicate bill_to_org check
1925 Elsif p_type_of_address = 'DELIVER_TO' Then
1926 l_duplicate_address := Oe_Value_To_Id.Deliver_To_Org(
1927 p_deliver_to_address1 => address_info_rec.address1,
1928 p_deliver_to_address2 => address_info_rec.address2,
1929 p_deliver_to_address3 => address_info_rec.address3,
1930 p_deliver_to_address4 => address_info_rec.address4,
1931 p_deliver_to_location => address_info_rec.location_number,
1932 p_deliver_to_org => l_ship_to_org,
1933 p_sold_to_org_id => l_customer_info_id,
1934 p_deliver_to_city => address_info_rec.city,
1935 p_deliver_to_state => address_info_rec.state,
1936 p_deliver_to_postal_code => address_info_rec.postal_code,
1937 p_deliver_to_country => address_info_rec.country,
1938 p_deliver_to_customer_id =>l_temp_usage_to_cust_id --bug 7299729
1939 );
1940 If l_duplicate_address <> FND_API.G_MISS_NUM Then
1941 -- Raise Error and Abort Processing
1942 IF l_debug_level > 0 THEN
1943 oe_debug_pub.add( 'TRYING TO ENTER THE DELIVER TO WHICH ALREADY EXISTS' ) ;
1944 END IF;
1945 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
1946 fnd_message.set_token('TYPE', 'DELIVER_TO ADDRESS');
1947 fnd_message.set_token('REFERENCE', p_customer_info_ref);
1948 oe_msg_pub.add;
1949 x_return_status := FND_API.G_RET_STS_ERROR;
1950 Update_Error_Flag(p_rowid => address_info_rec.rowid);
1951 Return;
1952 Else
1953 -- As the data is not duplicate but the call to oe_value_to_id
1954 -- has entered one error message in stack(necessary evil!)
1955 -- What to do => here is solution delete it
1956 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
1957 End if; -- duplicate deliver_to_org check
1958 End If;
1959 -- End Of If For duplicate checking}
1960 END IF; ----end of skip duplicate check --bug 7299729
1961
1962 --check if site number is passed, else error
1963 IF l_debug_level > 0 THEN
1964 oe_debug_pub.add( 'AUTO_SITE_NUMBERING = '|| G_AUTO_SITE_NUMBERING ) ;
1965 END IF;
1966 IF l_debug_level > 0 THEN
1967 oe_debug_pub.add( 'AUTO_LOCATION_NUMBERING = '|| G_AUTO_LOCATION_NUMBERING ) ;
1968 END IF;
1969
1970 IF nvl(G_AUTO_SITE_NUMBERING, 'Y') = 'N' AND
1971 address_info_rec.site_number IS NULL
1972 THEN
1973 fnd_message.set_name('ONT','ONT_OI_INL_REQD');
1974 fnd_message.set_token('API_NAME', 'Create_Address');
1975 fnd_message.set_token('FIELD_REQD', 'SITE_NUMBER');
1976 oe_msg_pub.add;
1977 x_return_status := FND_API.G_RET_STS_ERROR;
1978 IF l_debug_level > 0 THEN
1979 oe_debug_pub.add( 'SPECIFY SITE NUMBER , PROFILE NO AUTO' ) ;
1980 END IF;
1981 RETURN;
1982 ELSIF nvl(G_AUTO_SITE_NUMBERING, 'Y') = 'N' AND
1983 address_info_rec.site_number IS NOT NULL
1984 THEN
1985 l_site_number := address_info_rec.site_number;
1986 END IF;
1987
1988 IF nvl(g_auto_location_numbering, 'Y') = 'N' AND
1989 address_info_rec.location_number IS NULL
1990 THEN
1991 fnd_message.set_name('ONT','ONT_OI_INL_REQD');
1992 fnd_message.set_token('API_NAME', 'Create_Address');
1993 fnd_message.set_token('FIELD_REQD', 'LOCATION_NUMBER');
1994 oe_msg_pub.add;
1995 x_return_status := FND_API.G_RET_STS_ERROR;
1996 IF l_debug_level > 0 THEN
1997 oe_debug_pub.add( 'SPECIFY LOCATION NUMBER' ) ;
1998 END IF;
1999 RETURN;
2000 ELSIF nvl(g_auto_location_numbering, 'Y') = 'N' AND
2001 address_info_rec.location_number IS NOT NULL
2002 THEN
2003 l_location_number := address_info_rec.location_number;
2004 END IF;
2005
2006 IF l_debug_level > 0 THEN
2007 oe_debug_pub.add( 'CALL CREATE_LOCATION API' ) ;
2008 END IF;
2009
2010 l_location_rec.country := address_info_rec.country;
2011 l_location_rec.address1 := address_info_rec.address1;
2012 l_location_rec.address2 := address_info_rec.address2;
2013 l_location_rec.address3 := address_info_rec.address3;
2014 l_location_rec.address4 := address_info_rec.address4;
2015 l_location_rec.city := address_info_rec.city;
2016 l_location_rec.state := address_info_rec.state;
2017 l_location_rec.postal_code:= address_info_rec.postal_code;
2018 l_location_rec.province:= address_info_rec.province;
2019 l_location_rec.county:= address_info_rec.county;
2020 l_location_rec.address_style:= l_address_style;
2021 l_location_rec.attribute_category := address_info_rec.attribute_category;
2022 l_location_rec.attribute1 := address_info_rec.attribute1;
2023 l_location_rec.attribute2 := address_info_rec.attribute2;
2024 l_location_rec.attribute3 := address_info_rec.attribute3;
2025 l_location_rec.attribute4 := address_info_rec.attribute4;
2026 l_location_rec.attribute5 := address_info_rec.attribute5;
2027 l_location_rec.attribute6 := address_info_rec.attribute6;
2028 l_location_rec.attribute7 := address_info_rec.attribute7;
2029 l_location_rec.attribute8 := address_info_rec.attribute8;
2030 l_location_rec.attribute9 := address_info_rec.attribute9;
2031 l_location_rec.attribute10 := address_info_rec.attribute10;
2032 l_location_rec.attribute11 := address_info_rec.attribute11;
2033 l_location_rec.attribute12 := address_info_rec.attribute12;
2034 l_location_rec.attribute13 := address_info_rec.attribute13;
2035 l_location_rec.attribute14 := address_info_rec.attribute14;
2036 l_location_rec.attribute15 := address_info_rec.attribute15;
2037 l_location_rec.attribute16 := address_info_rec.attribute16;
2038 l_location_rec.attribute17 := address_info_rec.attribute17;
2039 l_location_rec.attribute18 := address_info_rec.attribute18;
2040 l_location_rec.attribute19 := address_info_rec.attribute19;
2041 l_location_rec.attribute20 := address_info_rec.attribute20;
2042 l_location_rec.created_by_module := G_CREATED_BY_MODULE;
2043 l_location_rec.application_id := 660;
2044 HZ_LOCATION_V2PUB.Create_Location(
2045 p_init_msg_list => Null
2046 ,p_location_rec => l_location_rec
2047 ,x_return_status => l_return_status
2048 ,x_msg_count => l_msg_count
2049 ,x_msg_data => l_msg_data
2050 ,x_location_id => l_location_id
2051 );
2052 IF l_debug_level > 0 THEN
2053 oe_debug_pub.add( 'AFTER HZ CREATE_LOCATION' ) ;
2054 END IF;
2055 IF l_debug_level > 0 THEN
2056 oe_debug_pub.add( 'LOCATION ID = '||L_LOCATION_ID ) ;
2057 END IF;
2058
2059 -- Let us check the status of the call to hz api
2060 IF l_debug_level > 0 THEN
2061 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2062 END IF;
2063 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2064 IF l_debug_level > 0 THEN
2065 oe_debug_pub.add( 'HZ CREATE_LOCATION API ERROR ' ) ;
2066 END IF;
2067 IF l_debug_level > 0 THEN
2068 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2069 END IF;
2070 IF l_debug_level > 0 THEN
2071 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2072 END IF;
2073 x_return_status := l_return_status;
2074 IF l_debug_level > 0 THEN
2075 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2076 END IF;
2077 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2078 oe_msg_pub.transfer_msg_stack;
2079 fnd_msg_pub.delete_msg;
2080 return;
2081 End If;
2082
2083 IF l_debug_level > 0 THEN
2084 oe_debug_pub.add( 'CALL CREATE_PARTY_SITE API' ) ;
2085 END IF;
2086
2087 l_party_site_rec.party_id:= l_customer_party_id;
2088 l_party_site_rec.location_id := l_location_id;
2089 l_party_site_rec.party_site_number := l_site_number;
2090 l_party_site_rec.attribute_category := address_info_rec.attribute_category;
2091 l_party_site_rec.attribute1 := address_info_rec.attribute1;
2092 l_party_site_rec.attribute2 := address_info_rec.attribute2;
2093 l_party_site_rec.attribute3 := address_info_rec.attribute3;
2094 l_party_site_rec.attribute4 := address_info_rec.attribute4;
2095 l_party_site_rec.attribute5 := address_info_rec.attribute5;
2096 l_party_site_rec.attribute6 := address_info_rec.attribute6;
2097 l_party_site_rec.attribute7 := address_info_rec.attribute7;
2098 l_party_site_rec.attribute8 := address_info_rec.attribute8;
2099 l_party_site_rec.attribute9 := address_info_rec.attribute9;
2100 l_party_site_rec.attribute10 := address_info_rec.attribute10;
2101 l_party_site_rec.attribute11 := address_info_rec.attribute11;
2102 l_party_site_rec.attribute12 := address_info_rec.attribute12;
2103 l_party_site_rec.attribute13 := address_info_rec.attribute13;
2104 l_party_site_rec.attribute14 := address_info_rec.attribute14;
2105 l_party_site_rec.attribute15 := address_info_rec.attribute15;
2106 l_party_site_rec.attribute16 := address_info_rec.attribute16;
2107 l_party_site_rec.attribute17 := address_info_rec.attribute17;
2108 l_party_site_rec.attribute18 := address_info_rec.attribute18;
2109 l_party_site_rec.attribute19 := address_info_rec.attribute19;
2110 l_party_site_rec.attribute20 := address_info_rec.attribute20;
2111 l_party_site_rec.created_by_module := G_CREATED_BY_MODULE;
2112 l_party_site_rec.application_id := 660;
2113
2114 HZ_PARTY_SITE_V2PUB.Create_Party_Site
2115 (
2116 p_party_site_rec => l_party_site_rec,
2117 x_party_site_id => l_party_site_id,
2118 x_party_site_number => l_party_site_number,
2119 x_return_status => l_return_status,
2120 x_msg_count => l_msg_count,
2121 x_msg_data => l_msg_data
2122 );
2123 IF l_debug_level > 0 THEN
2124 oe_debug_pub.add( 'AFTER HZ CREATE_PARTY_SITE API' ) ;
2125 END IF;
2126 IF l_debug_level > 0 THEN
2127 oe_debug_pub.add( 'PARTY_SITE_ID = '||L_PARTY_SITE_ID ) ;
2128 END IF;
2129 IF l_debug_level > 0 THEN
2130 oe_debug_pub.add( 'PARTY_SITE_NUMBER = '||L_PARTY_SITE_NUMBER ) ;
2131 END IF;
2132
2133 -- Let us check the status of the call to hz api
2134 IF l_debug_level > 0 THEN
2135 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2136 END IF;
2137 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2138 IF l_debug_level > 0 THEN
2139 oe_debug_pub.add( 'HZ CREATE_PARTY_SITE API ERROR ' ) ;
2140 END IF;
2141 IF l_debug_level > 0 THEN
2142 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2143 END IF;
2144 IF l_debug_level > 0 THEN
2145 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2146 END IF;
2147 x_return_status := l_return_status;
2148 IF l_debug_level > 0 THEN
2149 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2150 END IF;
2151 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2152 oe_msg_pub.transfer_msg_stack;
2153 fnd_msg_pub.delete_msg;
2154 return;
2155 End If;
2156
2157 IF l_debug_level > 0 THEN
2158 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCOUNT_SITE API' ) ;
2159 END IF;
2160
2161 l_account_site_rec.party_site_id := l_party_site_id;
2162 l_account_site_rec.cust_account_id := l_customer_info_id;
2163 l_account_site_rec.attribute_category := address_info_rec.attribute_category;
2164 l_account_site_rec.attribute1 := address_info_rec.attribute1;
2165 l_account_site_rec.attribute2 := address_info_rec.attribute2;
2166 l_account_site_rec.attribute3 := address_info_rec.attribute3;
2167 l_account_site_rec.attribute4 := address_info_rec.attribute4;
2168 l_account_site_rec.attribute5 := address_info_rec.attribute5;
2169 l_account_site_rec.attribute6 := address_info_rec.attribute6;
2170 l_account_site_rec.attribute7 := address_info_rec.attribute7;
2171 l_account_site_rec.attribute8 := address_info_rec.attribute8;
2172 l_account_site_rec.attribute9 := address_info_rec.attribute9;
2173 l_account_site_rec.attribute10 := address_info_rec.attribute10;
2174 l_account_site_rec.attribute11 := address_info_rec.attribute11;
2175 l_account_site_rec.attribute12 := address_info_rec.attribute12;
2176 l_account_site_rec.attribute13 := address_info_rec.attribute13;
2177 l_account_site_rec.attribute14 := address_info_rec.attribute14;
2178 l_account_site_rec.attribute15 := address_info_rec.attribute15;
2179 l_account_site_rec.attribute16 := address_info_rec.attribute16;
2180 l_account_site_rec.attribute17 := address_info_rec.attribute17;
2181 l_account_site_rec.attribute18 := address_info_rec.attribute18;
2182 l_account_site_rec.attribute19 := address_info_rec.attribute19;
2183 l_account_site_rec.attribute20 := address_info_rec.attribute20;
2184 l_account_site_rec.global_attribute_category := address_info_rec.global_attribute_category;
2185 l_account_site_rec.global_attribute1 := address_info_rec.global_attribute1;
2186 l_account_site_rec.global_attribute2 := address_info_rec.global_attribute2;
2187 l_account_site_rec.global_attribute3 := address_info_rec.global_attribute3;
2188 l_account_site_rec.global_attribute4 := address_info_rec.global_attribute4;
2189 l_account_site_rec.global_attribute5 := address_info_rec.global_attribute5;
2190 l_account_site_rec.global_attribute6 := address_info_rec.global_attribute6;
2191 l_account_site_rec.global_attribute7 := address_info_rec.global_attribute7;
2192 l_account_site_rec.global_attribute8 := address_info_rec.global_attribute8;
2193 l_account_site_rec.global_attribute9 := address_info_rec.global_attribute9;
2194 l_account_site_rec.global_attribute10 := address_info_rec.global_attribute10;
2195 l_account_site_rec.global_attribute11 := address_info_rec.global_attribute11;
2196 l_account_site_rec.global_attribute12 := address_info_rec.global_attribute12;
2197 l_account_site_rec.global_attribute13 := address_info_rec.global_attribute13;
2198 l_account_site_rec.global_attribute14 := address_info_rec.global_attribute14;
2199 l_account_site_rec.global_attribute15 := address_info_rec.global_attribute15;
2200 l_account_site_rec.global_attribute16 := address_info_rec.global_attribute16;
2201 l_account_site_rec.global_attribute17 := address_info_rec.global_attribute17;
2202 l_account_site_rec.global_attribute18 := address_info_rec.global_attribute18;
2203 l_account_site_rec.global_attribute19 := address_info_rec.global_attribute19;
2204 l_account_site_rec.global_attribute20 := address_info_rec.global_attribute20;
2205 l_account_site_rec.created_by_module := G_CREATED_BY_MODULE;
2206 l_account_site_rec.application_id := 660;
2207 l_account_site_rec.org_id := p_org_id;
2208
2209 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Acct_Site
2210 (
2211 p_cust_acct_site_rec => l_account_site_rec,
2212 x_return_status => l_return_status,
2213 x_msg_count => l_msg_count,
2214 x_msg_data => l_msg_data,
2215 x_cust_acct_site_id => l_customer_site_id
2216 );
2217 IF l_debug_level > 0 THEN
2218 oe_debug_pub.add( 'AFTER HZ CREATE_ACCOUNT_SITE API' ) ;
2219 END IF;
2220 IF l_debug_level > 0 THEN
2221 oe_debug_pub.add( 'CUSTOMER_SITE_ID = '||L_CUSTOMER_SITE_ID ) ;
2222 END IF;
2223
2224 -- Let us check the status of the call to hz api
2225 IF l_debug_level > 0 THEN
2226 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2227 END IF;
2228 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2229 IF l_debug_level > 0 THEN
2230 oe_debug_pub.add( 'HZ CREATE_ACCOUNT_SITE API ERROR ' ) ;
2231 END IF;
2232 IF l_debug_level > 0 THEN
2233 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2234 END IF;
2235 IF l_debug_level > 0 THEN
2236 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2237 END IF;
2238 x_return_status := l_return_status;
2239 IF l_debug_level > 0 THEN
2240 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2241 END IF;
2242 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2243 oe_msg_pub.transfer_msg_stack;
2244 fnd_msg_pub.delete_msg;
2245 return;
2246 End If;
2247
2248 IF l_debug_level > 0 THEN
2249 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCOUNT_SITE_USES API' ) ;
2250 END IF;
2251
2252 l_acct_site_uses.cust_acct_site_id := l_customer_site_id;
2253 l_acct_site_uses.location := l_location_number;
2254 l_acct_site_uses.created_by_module := G_CREATED_BY_MODULE;
2255 l_acct_site_uses.application_id := 660;
2256 l_acct_site_uses.org_id := p_org_id;
2257
2258 IF address_info_rec.is_ship_to_address = 'Y' THEN
2259 l_acct_site_uses.site_use_code := 'SHIP_TO';
2260 IF l_debug_level > 0 THEN
2261 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCT_SITE_USES FOR SHIP_TO' ) ;
2262 END IF;
2263 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use
2264 (
2265 p_cust_site_use_rec => l_acct_site_uses,
2266 p_customer_profile_rec => l_cust_profile_rec,
2267 p_create_profile => FND_API.G_FALSE,
2268 x_return_status => l_return_status,
2269 x_msg_count => l_msg_count,
2270 x_msg_data => l_msg_data,
2271 x_site_use_id => l_site_use_id_ship
2272 );
2273 -- Let us check the status of the call to hz api
2274 IF l_debug_level > 0 THEN
2275 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2276 END IF;
2277 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2278 IF l_debug_level > 0 THEN
2279 oe_debug_pub.add( 'HZ CREATE_SITE_USAGE API ERROR ' ) ;
2280 END IF;
2281 IF l_debug_level > 0 THEN
2282 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2283 END IF;
2284 IF l_debug_level > 0 THEN
2285 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2286 END IF;
2287 x_return_status := l_return_status;
2288 IF l_debug_level > 0 THEN
2289 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2290 END IF;
2291 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2292 oe_msg_pub.transfer_msg_stack;
2293 fnd_msg_pub.delete_msg;
2294 return;
2295 End If;
2296
2297 IF l_debug_level > 0 THEN
2298 oe_debug_pub.add( 'AFTER HZ CREATE_ACCT_SITE_USES FOR SHIP_TO' ) ;
2299 END IF;
2300 IF l_debug_level > 0 THEN
2301 oe_debug_pub.add( 'SITE_USE_ID_SHIP = '||L_SITE_USE_ID_SHIP ) ;
2302 END IF;
2303 IF l_debug_level > 0 THEN
2304 oe_debug_pub.add( 'L_RETURN_STATUS = '||L_RETURN_STATUS ) ;
2305 END IF;
2306 If p_type_of_address = 'SHIP_TO' Then
2307 x_usage_site_id := l_site_use_id_ship;
2308 End if;
2309
2310 Update_Address_id( type_of_address => 'SHIP_TO',
2311 usage_site_id => l_site_use_id_ship,
2312 row_id => address_info_rec.rowid );
2313
2314 END IF;
2315
2316 IF address_info_rec.is_bill_to_address = 'Y' THEN
2317 l_acct_site_uses.site_use_code := 'BILL_TO';
2318 IF l_debug_level > 0 THEN
2319 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCT_SITE_USES FOR BILL_TO' ) ;
2320 END IF;
2321 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use
2322 (
2323 p_cust_site_use_rec => l_acct_site_uses,
2324 p_customer_profile_rec => l_cust_profile_rec,
2325 p_create_profile => FND_API.G_FALSE,
2326 x_return_status => l_return_status,
2327 x_msg_count => l_msg_count,
2328 x_msg_data => l_msg_data,
2329 x_site_use_id => l_site_use_id_bill
2330 );
2331 -- Let us check the status of the call to hz api
2332 IF l_debug_level > 0 THEN
2333 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2334 END IF;
2335 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2336 IF l_debug_level > 0 THEN
2337 oe_debug_pub.add( 'HZ CREATE_SITE_USAGE API ERROR ' ) ;
2338 END IF;
2339 IF l_debug_level > 0 THEN
2340 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2341 END IF;
2342 IF l_debug_level > 0 THEN
2343 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2344 END IF;
2345 x_return_status := l_return_status;
2346 IF l_debug_level > 0 THEN
2347 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2348 END IF;
2349 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2350 oe_msg_pub.transfer_msg_stack;
2351 fnd_msg_pub.delete_msg;
2352 return;
2353 End If;
2354
2355 IF l_debug_level > 0 THEN
2356 oe_debug_pub.add( 'AFTER HZ CREATE_ACCT_SITE_USES FOR BILL_TO' ) ;
2357 END IF;
2358 IF l_debug_level > 0 THEN
2359 oe_debug_pub.add( 'SITE_USE_ID_BILL = '||L_SITE_USE_ID_BILL ) ;
2360 END IF;
2361 If p_type_of_address = 'BILL_TO' Then
2362 x_usage_site_id := l_site_use_id_bill;
2363 End if;
2364
2365 Update_Address_id( type_of_address => 'BILL_TO',
2366 usage_site_id => l_site_use_id_bill,
2367 row_id => address_info_rec.rowid );
2368 END IF;
2369
2370 IF address_info_rec.is_deliver_to_address = 'Y' THEN
2371 l_acct_site_uses.site_use_code := 'DELIVER_TO';
2372 IF l_debug_level > 0 THEN
2373 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCT_SITE_USES FOR DELIVER_TO ' ) ;
2374 END IF;
2375 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use
2376 (
2377 p_cust_site_use_rec => l_acct_site_uses,
2378 p_customer_profile_rec => l_cust_profile_rec,
2379 p_create_profile => FND_API.G_FALSE,
2380 x_return_status => l_return_status,
2381 x_msg_count => l_msg_count,
2382 x_msg_data => l_msg_data,
2383 x_site_use_id => l_site_use_id_deliver
2384 );
2385 -- Let us check the status of the call to hz api
2386 IF l_debug_level > 0 THEN
2387 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2388 END IF;
2389 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2390 IF l_debug_level > 0 THEN
2391 oe_debug_pub.add( 'HZ CREATE_SITE_USAGE API ERROR ' ) ;
2392 END IF;
2393 IF l_debug_level > 0 THEN
2394 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2395 END IF;
2396 IF l_debug_level > 0 THEN
2397 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2398 END IF;
2399 IF l_debug_level > 0 THEN
2400 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2401 END IF;
2402 x_return_status := l_return_status;
2403 IF l_debug_level > 0 THEN
2404 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2405 END IF;
2406 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2407 oe_msg_pub.transfer_msg_stack;
2408 fnd_msg_pub.delete_msg;
2409 return;
2410 End If;
2411
2412 IF l_debug_level > 0 THEN
2413 oe_debug_pub.add( 'AFTER HZ CREATE_ACCT_SITE_USES FOR DELIVER_TO' ) ;
2414 END IF;
2415 IF l_debug_level > 0 THEN
2416 oe_debug_pub.add( 'SITE_USE_ID_DELIVER = '||L_SITE_USE_ID_DELIVER ) ;
2417 END IF;
2418 If p_type_of_address = 'DELIVER_TO' Then
2419 x_usage_site_id := l_site_use_id_deliver;
2420 End if;
2421 Update_Address_id( type_of_address => 'DELIVER_TO',
2422 usage_site_id => l_site_use_id_deliver,
2423 row_id => address_info_rec.rowid );
2424
2425 END IF;
2426
2427 EXCEPTION
2428 When Others Then
2429 IF l_debug_level > 0 THEN
2430 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_ADDRESS. ABORT PROCESSING' ) ;
2431 END IF;
2432 IF l_debug_level > 0 THEN
2433 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
2434 END IF;
2435 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
2436 fnd_message.set_token('API_NAME', 'Create_Address');
2437 oe_msg_pub.add;
2438 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2439 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2440 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
2441 IF l_debug_level > 0 THEN
2442 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2443 END IF;
2444
2445 END;
2446 -- End of Begin after loop
2447 END LOOP;
2448 -- End of For loop
2449 If l_no_record_exists Then
2450 IF l_debug_level > 0 THEN
2451 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED REF , PLEASE CHECK DATA' ) ;
2452 END IF;
2453 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
2454 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2455 oe_msg_pub.add;
2456 x_return_status := FND_API.G_RET_STS_ERROR;
2457 IF l_debug_level > 0 THEN
2458 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2459 END IF;
2460 End If;
2461
2462 IF l_debug_level > 0 THEN
2463 oe_debug_pub.add( 'EXITING PROCEDURE CREATE ADDRESS' ) ;
2464 END IF;
2465 Exception
2466 When Others Then
2467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2468 IF l_debug_level > 0 THEN
2469 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_ADDRESS. ABORT PROCESSING' ) ;
2470 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
2471 END IF;
2472 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
2473 fnd_message.set_token('API_NAME', 'Create_Address');
2474 oe_msg_pub.add;
2475 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
2476 IF l_debug_level > 0 THEN
2477 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2478 END IF;
2479
2480 End Create_Address;
2481 -- End procedure Create Address }
2482
2483
2484 -- { Start procedure Create Contact
2485 PROCEDURE Create_Contact(
2486 p_customer_info_ref IN Varchar2,
2487 p_cust_account_id IN OUT NOCOPY /* file.sql.39 change */ Number,
2488 p_cust_account_number IN OUT NOCOPY /* file.sql.39 change */ Varchar2,
2489 p_type_of_contact IN Varchar2,
2490 p_orig_sys_document_ref IN Varchar2,
2491 p_orig_sys_line_ref IN Varchar2,
2492 p_order_source_id IN Number,
2493 x_contact_id OUT NOCOPY /* file.sql.39 change */ Number,
2494 x_contact_name OUT NOCOPY /* file.sql.39 change */ Varchar2,
2495 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
2496 )
2497 IS
2498 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
2499 l_party_rec HZ_PARTY_V2PUB.party_rec_type;
2500
2501 l_contact_party_id Number;
2502 l_contact_party_number Varchar2(50);
2503 l_customer_party_id Number;
2504 l_customer_party_number Varchar2(50);
2505 l_profile_id Number;
2506
2507 l_return_status Varchar2(1);
2508
2509 -- l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
2510 x_rel_party_id Number;
2511 x_rel_party_number hz_parties.party_number%TYPE;
2512 x_party_relationship_id Number;
2513
2514 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
2515 x_org_contact_id Number;
2516
2517 x_cust_account_role_id Number;
2518 l_cust_acct_roles_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
2519
2520 l_gen_contact_number Varchar2(1);
2521
2522 l_customer_info_id Number;
2523 l_customer_info_number Varchar2(30);
2524 l_customer_info_ref Varchar2(50) := p_customer_info_ref;
2525 l_type_of_contact Varchar2(10) := p_type_of_contact;
2526 l_usage_site_id Number;
2527 l_ship_to_org_id Number;
2528 l_bill_to_org_id Number;
2529 l_deliver_to_org_id Number;
2530 l_ret_contact_id Number;
2531 l_existing_value Varchar2(1) := 'N';
2532
2533 l_msg_data Varchar2(2000);
2534 l_msg_count Number;
2535 l_contact_name Varchar2(2000);
2536 l_sold_to_contact Number;
2537 l_no_record_exists BOOLEAN := TRUE;
2538
2539 -- Following cursor will fetch the data for the passed ref and type --
2540 -- information. For the contact creation. --
2541 -- {
2542 Cursor l_contact_info_cur Is
2543 Select person_first_name,
2544 person_last_name,
2545 person_middle_name,
2546 person_name_suffix,
2547 person_title,
2548 email_address,
2549 phone_area_code,
2550 phone_number,
2551 phone_extension,
2552 current_customer_number,
2553 current_customer_id,
2554 parent_customer_ref,
2555 new_contact_id,
2556 new_party_id,
2557 is_ship_to_address,
2558 is_bill_to_address,
2559 is_deliver_to_address,
2560 rowid,
2561 phone_country_code
2562 from oe_customer_info_iface_all
2563 where customer_info_ref = l_customer_info_ref
2564 and customer_info_type_code = 'CONTACT';
2565
2566 -- End of Cursor definition for l_contact_info_cur }
2567
2568 --
2569 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2570 --
2571 Begin
2572 IF l_debug_level > 0 THEN
2573 oe_debug_pub.add( 'ENTERING PROCEDURE CREATE CONTACT' ) ;
2574 END IF;
2575
2576 x_return_status := FND_API.G_RET_STS_SUCCESS;
2577
2578 OE_MSG_PUB.set_msg_context(
2579 p_entity_code => 'OI_INL_ADDCUST'
2580 ,p_entity_ref => null
2581 ,p_entity_id => null
2582 ,p_header_id => null
2583 ,p_line_id => null
2584 -- ,p_batch_request_id => p_x_header_rec.request_id
2585 ,p_order_source_id => p_order_source_id
2586 ,p_orig_sys_document_ref => p_orig_sys_document_ref
2587 ,p_change_sequence => null
2588 ,p_orig_sys_document_line_ref => p_orig_sys_line_ref
2589 ,p_orig_sys_shipment_ref => p_customer_info_ref
2590 ,p_source_document_type_id => null
2591 ,p_source_document_id => null
2592 ,p_source_document_line_id => null
2593 ,p_attribute_code => null
2594 ,p_constraint_id => null
2595 );
2596
2597 -- { Start Contact Loop
2598 For contact_rec In l_contact_info_cur Loop
2599 -- { Start of Begin for Loop
2600 Begin
2601 l_no_record_exists := FALSE;
2602 -- Check if the Data is already used to create the New
2603 -- Customer then return that value and exit out of the
2604 -- process
2605
2606 IF l_debug_level > 0 THEN
2607 oe_debug_pub.add( 'INSIDE LOOP FOR THE CUSTOMER_INFO_CUR' ) ;
2608 END IF;
2609
2610 If contact_rec.New_Contact_Id Is Not Null And
2611 l_type_of_contact = 'SOLD_TO' Then
2612 IF l_debug_level > 0 THEN
2613 oe_debug_pub.add( 'NEW CONTACT ID IS THERE , RETURING THE EXISTING VAL' ) ;
2614 END IF;
2615 x_contact_id := contact_rec.New_Contact_id;
2616 IF l_debug_level > 0 THEN
2617 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH CURRENT VAL' ) ;
2618 END IF;
2619 RETURN;
2620 End If;
2621
2622 -- {Start Check if the contact is being created for the site then
2623 --then if that contact is alreary for that site then return the
2624 --contact_id otherwise need to make contact for new site too.
2625 If contact_rec.New_Contact_Id Is Not Null and
2626 l_type_of_contact Is Not Null Then
2627 If (l_type_of_contact = 'SHIP_TO' and
2628 contact_rec.is_ship_to_address = 'Y') Or
2629 (l_type_of_contact = 'BILL_TO' and
2630 contact_rec.is_bill_to_address = 'Y') Or
2631 (l_type_of_contact = 'DELIVER_TO' and
2632 contact_rec.is_deliver_to_address = 'Y') Then
2633 IF l_debug_level > 0 THEN
2634 oe_debug_pub.add( 'NEW CONTACT ID IS THERE FOR SITE ' || L_TYPE_OF_CONTACT || ' , RETURING THE EXISTING VAL' ) ;
2635 END IF;
2636 x_contact_id := contact_rec.New_Contact_id;
2637 IF l_debug_level > 0 THEN
2638 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH CURRENT VAL' ) ;
2639 END IF;
2640 RETURN;
2641 End If;
2642 End If;
2643 -- End of the site level contact check}
2644
2645 -- {Start of the checking of existing customer or not
2646 IF l_debug_level > 0 THEN
2647 oe_debug_pub.add( 'LET US CHECK IF THIS IS FOR EXISTING CUSTOMER' ) ;
2648 END IF;
2649
2650 If (contact_rec.current_customer_id IS NULL) And
2651 (contact_rec.current_customer_number) IS NULL Then
2652 --{Start of if parent rec is passed or not
2653 If contact_rec.parent_customer_ref is Null Then
2654 IF l_debug_level > 0 THEN
2655 oe_debug_pub.add( 'NO PARENT CUSTOMER REF POPULATED. CHECK THE DATA' ) ;
2656 END IF;
2657 x_return_status := FND_API.G_RET_STS_ERROR;
2658 fnd_message.set_name('ONT','ONT_OI_INL_NO_PARENT_INFO');
2659 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2660 oe_msg_pub.add;
2661 return;
2662 End If;
2663 -- End of if parent rec is passed or not}
2664
2665 IF l_debug_level > 0 THEN
2666 oe_debug_pub.add( 'BEFORE CALLING CREATE ACCOUNT PROCEDURE' ) ;
2667 END IF;
2668 -- call Create_Account api
2669 Create_Account( p_customer_info_ref => contact_rec.parent_customer_ref,
2670 p_orig_sys_document_ref => p_orig_sys_document_ref,
2671 p_orig_sys_line_ref => p_orig_sys_line_ref,
2672 p_order_source_id => p_order_source_id,
2673 x_cust_account_id => l_customer_info_id,
2674 x_cust_account_number => l_customer_info_number,
2675 x_cust_party_id => l_customer_party_id,
2676 x_existing_value => l_existing_value,
2677 x_return_status => l_return_status
2678 );
2679 IF l_debug_level > 0 THEN
2680 oe_debug_pub.add( 'AFTER CALLING CREATE ACCOUNT PROCEDURE' ) ;
2681 END IF;
2682
2683 -- Check for the return status of the api
2684 -- to return the proper information to the called program.
2685 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2686 IF l_debug_level > 0 THEN
2687 oe_debug_pub.add( 'RETURN STATUS IS NOT SUCCESS , AFTER CREATE ACC. FOR CONTACT' ) ;
2688 END IF;
2689 x_return_status := l_return_status;
2690 IF l_debug_level > 0 THEN
2691 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH ERROR' ) ;
2692 END IF;
2693 Return;
2694 End if;
2695 -- End of the checking of the Parent Info and creation}
2696
2697 Else
2698 IF l_debug_level > 0 THEN
2699 oe_debug_pub.add( 'FOR EXISTING CUSTOMER' ) ;
2700 END IF;
2701 If contact_rec.current_customer_id IS NOT NULL Then
2702 l_customer_info_id := contact_rec.Current_Customer_id;
2703 Begin
2704 Select party_id
2705 Into l_customer_party_id
2706 From hz_cust_accounts
2707 Where cust_account_id = l_customer_info_id;
2708 Exception
2709 When No_Data_Found Then
2710 IF l_debug_level > 0 THEN
2711 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED EXISTING CUSTOMER , PLEASE CHECK DATA' ) ;
2712 END IF;
2713 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
2714 fnd_message.set_token('REFERENCE', contact_rec.Current_Customer_id);
2715 oe_msg_pub.add;
2716 x_return_status := FND_API.G_RET_STS_ERROR;
2717 Return;
2718 End;
2719 Else
2720 --Validate customer
2721 Validate_Customer_Number(p_customer_number => contact_rec.Current_Customer_Number,
2722 x_party_id => l_customer_party_id,
2723 x_account_id => l_customer_info_id,
2724 x_return_status => l_return_status
2725 );
2726 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2727 x_return_status := l_return_status;
2728 Return;
2729 End If;
2730 End If;
2731 End If;
2732 -- End of the checking of existing customer or not}
2733
2734 -- { Start of duplicate check for contact
2735 -- concatenate to get name of contact
2736 Select contact_rec.person_last_name || DECODE(contact_rec.person_first_name, NULL, NULL, ', '|| contact_rec.PERSON_FIRST_NAME) || DECODE(contact_rec.Person_Name_Suffix, NULL, NULL, ', '||contact_rec.Person_Name_Suffix)
2737 Into l_contact_name
2738 From Dual;
2739
2740 -- { Start of If for duplicate contact check
2741 If l_type_of_contact = 'SOLD_TO' Then
2742 l_sold_to_contact := Oe_Value_To_Id.Sold_To_Contact(
2743 p_sold_to_contact => l_contact_name,
2744 p_sold_to_org_id => l_customer_info_id);
2745 If l_sold_to_contact <> FND_API.G_MISS_NUM Then
2746 -- Raise Error and Abort Processing
2747 IF l_debug_level > 0 THEN
2748 oe_debug_pub.add( 'TRYING TO ENTER THE SOLD_TO CONTACT WHICH ALREADY EXISTS' ) ;
2749 END IF;
2750 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
2751 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2752 oe_msg_pub.add;
2753 x_return_status := FND_API.G_RET_STS_ERROR;
2754 Update_Error_Flag(p_rowid => contact_rec.rowid);
2755 Return;
2756 Else
2757 -- As the data is not duplicate but the call to oe_value_to_id
2758 -- has entered one error message in stack(necessary evil!)
2759 -- What to do => here is solution delete it
2760 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2761 End if; -- duplicate sold_to contact check
2762 Elsif l_type_of_contact = 'SHIP_TO' Then
2763 l_sold_to_contact := Oe_Value_To_Id.Ship_To_Contact(
2764 p_ship_to_contact => l_contact_name,
2765 p_ship_to_org_id => l_usage_site_id);
2766 If l_sold_to_contact <> FND_API.G_MISS_NUM Then
2767 -- Raise Error and Abort Processing
2768 IF l_debug_level > 0 THEN
2769 oe_debug_pub.add( 'TRYING TO ENTER THE SHIP_TO CONTACT WHICH ALREADY EXISTS' ) ;
2770 END IF;
2771 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
2772 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2773 oe_msg_pub.add;
2774 x_return_status := FND_API.G_RET_STS_ERROR;
2775 Update_Error_Flag(p_rowid => contact_rec.rowid);
2776 Return;
2777 Else
2778 -- As the data is not duplicate but the call to oe_value_to_id
2779 -- has entered one error message in stack(necessary evil!)
2780 -- What to do => here is solution delete it
2781 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2782 End if; -- duplicate ship_to contact check
2783 Elsif l_type_of_contact = 'BILL_TO' Then
2784 l_sold_to_contact := Oe_Value_To_Id.Invoice_To_Contact(
2785 p_invoice_to_contact => l_contact_name,
2786 p_invoice_to_org_id => l_usage_site_id);
2787 If l_sold_to_contact <> FND_API.G_MISS_NUM Then
2788 -- Raise Error and Abort Processing
2789 IF l_debug_level > 0 THEN
2790 oe_debug_pub.add( 'TRYING TO ENTER THE BILL_TO CONTACT WHICH ALREADY EXISTS' ) ;
2791 END IF;
2792 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
2793 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2794 oe_msg_pub.add;
2795 x_return_status := FND_API.G_RET_STS_ERROR;
2796 Update_Error_Flag(p_rowid => contact_rec.rowid);
2797 Return;
2798 Else
2799 -- As the data is not duplicate but the call to oe_value_to_id
2800 -- has entered one error message in stack(necessary evil!)
2801 -- What to do => here is solution delete it
2802 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2803 End if; -- duplicate bill_to contact check
2804 Elsif l_type_of_contact = 'DELIVER_TO' Then
2805 l_sold_to_contact := Oe_Value_To_Id.Deliver_To_Contact(
2806 p_deliver_to_contact => l_contact_name,
2807 p_deliver_to_org_id => l_usage_site_id);
2808 If l_sold_to_contact <> FND_API.G_MISS_NUM Then
2809 -- Raise Error and Abort Processing
2810 IF l_debug_level > 0 THEN
2811 oe_debug_pub.add( 'TRYING TO ENTER THE DELIVER_TO CONTACT WHICH ALREADY EXISTS' ) ;
2812 END IF;
2813 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
2814 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2815 oe_msg_pub.add;
2816 x_return_status := FND_API.G_RET_STS_ERROR;
2817 Update_Error_Flag(p_rowid => contact_rec.rowid);
2818 Return;
2819 Else
2820 -- As the data is not duplicate but the call to oe_value_to_id
2821 -- has entered one error message in stack(necessary evil!)
2822 -- What to do => here is solution delete it
2823 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2824 End if; -- duplicate deliver_to contact check
2825 End if;
2826 -- End of If for duplicate contact check}
2827
2828
2829
2830 -- { Start Let us now check to see that we are here just to create
2831 -- a new site level contact for the exiting contact or the contact
2832 -- itself is not there and we have to go thru the whole process
2833 If contact_rec.New_Contact_Id Is Not Null Then
2834 Check_and_Create_Contact(p_contact_party_id => contact_rec.new_party_id,
2835 p_cust_acct_id => l_customer_info_id,
2836 p_usage_type => l_type_of_contact,
2837 x_contact_id => l_ret_contact_id,
2838 x_return_status => l_return_status
2839 );
2840 If contact_rec.New_Contact_Id = l_ret_contact_id Then
2841 IF l_debug_level > 0 THEN
2842 oe_debug_pub.add( 'GENERATED CONTACT ID IS => ' || L_RET_CONTACT_ID ) ;
2843 END IF;
2844 x_contact_id := contact_rec.New_Contact_id;
2845 IF l_debug_level > 0 THEN
2846 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH CURRENT VAL' ) ;
2847 END IF;
2848 RETURN;
2849 Else
2850 IF l_debug_level > 0 THEN
2851 oe_debug_pub.add( 'THERE IS SOME PROBLEM , PLEASE INVESTIGATE.' ) ;
2852 END IF;
2853 End If;
2854 End If;
2855
2856 -- End of the check }
2857
2858
2859 l_person_rec.person_first_name := contact_rec.person_first_name;
2860 l_person_rec.person_last_name := contact_rec.person_last_name;
2861 l_person_rec.person_pre_name_adjunct := contact_rec.person_title;
2862 l_person_rec.created_by_module := G_CREATED_BY_MODULE;
2863 l_person_rec.application_id := 660;
2864
2865 If G_AUTO_PARTY_NUMBERING = 'N' Then
2866 IF l_debug_level > 0 THEN
2867 oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM PARTY SEQUENCE' ) ;
2868 END IF;
2869 Select hz_party_number_s.nextval
2870 Into l_party_rec.party_number
2871 From Dual;
2872 IF l_debug_level > 0 THEN
2873 oe_debug_pub.add( 'AFTER SELECTING NEW PARTY SEQUENCE ' || L_PARTY_REC.PARTY_NUMBER ) ;
2874 END IF;
2875 End If; -- If G_AUTO_PARTY_NUMBERING
2876
2877 l_person_rec.party_rec := l_party_rec;
2878
2879 -- { Start Before Calling hz api to create person for contact
2880 HZ_PARTY_V2PUB.Create_Person(
2881 p_person_rec => l_person_rec,
2882 x_party_id => l_contact_party_id,
2883 x_party_number => l_contact_party_number,
2884 x_profile_id => l_profile_id,
2885 x_return_status => l_return_status,
2886 x_msg_count => l_msg_count,
2887 x_msg_data => l_msg_data
2888 );
2889
2890 -- End Call hz api to create contact person }
2891 -- Let us check the status of the call to hz api
2892 IF l_debug_level > 0 THEN
2893 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2894 END IF;
2895 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2896 IF l_debug_level > 0 THEN
2897 oe_debug_pub.add( 'HZ CREATE_PERSON API ERROR ' ) ;
2898 END IF;
2899 IF l_debug_level > 0 THEN
2900 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2901 END IF;
2902 IF l_debug_level > 0 THEN
2903 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2904 END IF;
2905 x_return_status := l_return_status;
2906 IF l_debug_level > 0 THEN
2907 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
2908 END IF;
2909 Update_Error_Flag(p_rowid => contact_rec.rowid);
2910 oe_msg_pub.transfer_msg_stack;
2911 fnd_msg_pub.delete_msg;
2912 return;
2913 End If;
2914
2915 IF l_debug_level > 0 THEN
2916 oe_debug_pub.add( 'NEW PARTY ID FOR CONTACT =>' || L_CONTACT_PARTY_ID ) ;
2917 END IF;
2918 IF l_debug_level > 0 THEN
2919 oe_debug_pub.add( 'NEW PARTY NUMBER FOR CONTACT =>' || L_CONTACT_PARTY_NUMBER ) ;
2920 END IF;
2921
2922 If G_AUTO_PARTY_NUMBERING = 'N' Then
2923 IF l_debug_level > 0 THEN
2924 oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM PARTY SEQUENCE FOR ORG' ) ;
2925 END IF;
2926 Select hz_party_number_s.nextval
2927 Into l_org_contact_rec.party_rel_rec.party_rec.party_number
2928 From Dual;
2929 IF l_debug_level > 0 THEN
2930 oe_debug_pub.add( 'AFTER SELECTING NEW PARTY SEQUENCE ' || L_ORG_CONTACT_REC.PARTY_REL_REC.PARTY_REC.PARTY_NUMBER ) ;
2931 END IF;
2932 End If; -- If G_AUTO_PARTY_NUMBERING
2933
2934 l_org_contact_rec.party_rel_rec.subject_id := l_contact_party_id;
2935 l_org_contact_rec.party_rel_rec.object_id := l_customer_party_id;
2936 l_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
2937 l_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
2938 l_org_contact_rec.party_rel_rec.start_date := sysdate;
2939 l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
2940 l_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
2941 l_org_contact_rec.party_rel_rec.created_by_module := G_CREATED_BY_MODULE;
2942 l_org_contact_rec.party_rel_rec.application_id := 660;
2943 l_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
2944 Select party_type
2945 Into l_org_contact_rec.party_rel_rec.object_type
2946 From HZ_PARTIES
2947 Where party_id = l_customer_party_id;
2948
2949 IF l_debug_level > 0 THEN
2950 oe_debug_pub.add( 'SUBJECT_ID:'||L_CONTACT_PARTY_ID||':OBJECT_ID:'||L_CUSTOMER_PARTY_ID||':OBJECT_TYPE'||L_ORG_CONTACT_REC.PARTY_REL_REC.OBJECT_TYPE ) ;
2951 END IF;
2952 If G_AUTO_CONTACT_NUMBERING = 'N' Then
2953 IF l_debug_level > 0 THEN
2954 oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM CONTACT SEQ' ) ;
2955 END IF;
2956 Select hz_contact_numbers_s.nextval
2957 Into l_org_contact_rec.contact_number
2958 From Dual;
2959 IF l_debug_level > 0 THEN
2960 oe_debug_pub.add( 'AFTER SELECTING NEW CONTACT SEQUENCE ' || L_ORG_CONTACT_REC.CONTACT_NUMBER ) ;
2961 END IF;
2962 End If; -- If G_AUTO_CONTACT_NUMBERING
2963
2964
2965 l_org_contact_rec.title := contact_rec.person_title;
2966 l_org_contact_rec.created_by_module := G_CREATED_BY_MODULE;
2967 l_org_contact_rec.application_id := 660;
2968
2969 -- { Start Before Calling hz api to create org contact
2970
2971 HZ_PARTY_CONTACT_V2PUB.Create_Org_Contact (
2972 p_org_contact_rec => l_org_contact_rec,
2973 x_party_id => x_rel_party_id,
2974 x_party_number => x_rel_party_number,
2975 x_party_rel_id => x_party_relationship_id,
2976 x_org_contact_id => x_org_contact_id,
2977 x_return_status => l_return_status,
2978 x_msg_count => l_msg_count,
2979 x_msg_data => l_msg_data
2980 );
2981 -- End Call hz api to create org contact }
2982 -- Let us check the status of the call to hz api
2983 IF l_debug_level > 0 THEN
2984 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2985 END IF;
2986 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2987 IF l_debug_level > 0 THEN
2988 oe_debug_pub.add( 'HZ CREATE_ORG_CONTACT API ERROR ' ) ;
2989 END IF;
2990 IF l_debug_level > 0 THEN
2991 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2992 END IF;
2993 IF l_debug_level > 0 THEN
2994 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2995 END IF;
2996 x_return_status := l_return_status;
2997 IF l_debug_level > 0 THEN
2998 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
2999 END IF;
3000 Update_Error_Flag(p_rowid => contact_rec.rowid);
3001 oe_msg_pub.transfer_msg_stack;
3002 fnd_msg_pub.delete_msg;
3003 return;
3004 End If;
3005
3006 IF l_debug_level > 0 THEN
3007 oe_debug_pub.add( 'NEW REL PARTY ID FOR CONTACT =>' || X_REL_PARTY_ID ) ;
3008 END IF;
3009 IF l_debug_level > 0 THEN
3010 oe_debug_pub.add( 'NEW REL PARTY NUMBER CONTCT =>' || X_REL_PARTY_NUMBER ) ;
3011 END IF;
3012 IF l_debug_level > 0 THEN
3013 oe_debug_pub.add( 'NEW ORG CONTCT ID =>' || X_ORG_CONTACT_ID ) ;
3014 END IF;
3015
3016 -- { Let us create the Contact's contact point EMAIL and PHONE
3017 If contact_rec.email_address is Not Null Then
3018 Create_Contact_Point(
3019 p_contact_point_type => 'EMAIL',
3020 p_owner_table_id => x_rel_party_id,
3021 p_email => contact_rec.email_address,
3022 p_phone_area_code => NULL,
3023 p_phone_number => NULL,
3024 p_phone_extension => NULL,
3025 p_phone_country_code => NULL,
3026 x_return_status => l_return_status,
3027 x_msg_count => l_msg_count,
3028 x_msg_data => l_msg_data
3029 );
3030 -- Let us check the status of the call to hz api
3031 IF l_debug_level > 0 THEN
3032 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
3033 END IF;
3034 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
3035 IF l_debug_level > 0 THEN
3036 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM CONTACT EML '|| L_MSG_DATA ) ;
3037 END IF;
3038 x_return_status := l_return_status;
3039 IF l_debug_level > 0 THEN
3040 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH ERROR' ) ;
3041 END IF;
3042 return;
3043 Else
3044 IF l_debug_level > 0 THEN
3045 oe_debug_pub.add( 'NEW EMAIL => ' || CONTACT_REC.EMAIL_ADDRESS ) ;
3046 END IF;
3047 End if;
3048
3049 End If;
3050 -- End for create contact point EMAIL}
3051
3052 -- { Start for create contact point PHONE
3053 If contact_rec.phone_number is Not Null Then
3054 Create_Contact_Point(
3055 p_contact_point_type => 'PHONE',
3056 p_owner_table_id => x_rel_party_id,
3057 p_email => NULL,
3058 p_phone_area_code => contact_rec.phone_area_code,
3059 p_phone_number => contact_rec.phone_number,
3060 p_phone_extension => contact_rec.phone_extension,
3061 p_phone_country_code => contact_rec.phone_country_code,
3062 x_return_status => l_return_status,
3063 x_msg_count => l_msg_count,
3064 x_msg_data => l_msg_data
3065 );
3066 -- Let us check the status of the call to hz api
3067 IF l_debug_level > 0 THEN
3068 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
3069 END IF;
3070 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
3071 IF l_debug_level > 0 THEN
3072 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM CONTACT PH '|| L_MSG_DATA ) ;
3073 END IF;
3074 x_return_status := l_return_status;
3075 IF l_debug_level > 0 THEN
3076 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH ERROR' ) ;
3077 END IF;
3078 return;
3079 Else
3080 IF l_debug_level > 0 THEN
3081 oe_debug_pub.add( 'NEW PHONE => ' || CONTACT_REC.PHONE_NUMBER ) ;
3082 END IF;
3083 End if;
3084
3085 End If;
3086 -- End for create contact point PHONE}
3087
3088 -- { Start create CONTACT role for the new contact
3089
3090 l_cust_acct_roles_rec.party_id := x_rel_party_id;
3091 l_cust_acct_roles_rec.cust_account_id := l_customer_info_id;
3092 l_cust_acct_roles_rec.role_type := 'CONTACT';
3093 -- l_cust_acct_roles_rec.begin_date := sysdate;
3094 l_cust_acct_roles_rec.cust_acct_site_id := NULL;
3095 l_cust_acct_roles_rec.created_by_module := G_CREATED_BY_MODULE;
3096 l_cust_acct_roles_rec.application_id := 660;
3097
3098 HZ_CUST_ACCOUNT_ROLE_V2PUB.Create_Cust_Account_Role(
3099 p_cust_account_role_rec => l_cust_acct_roles_rec,
3100 x_return_status => l_return_status,
3101 x_msg_count => l_msg_count,
3102 x_msg_data => l_msg_data,
3103 x_cust_account_role_id => x_cust_account_role_id
3104 );
3105
3106 IF l_debug_level > 0 THEN
3107 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
3108 END IF;
3109 -- Let us check the status of the call to hz api
3110 IF l_debug_level > 0 THEN
3111 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
3112 END IF;
3113 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
3114 IF l_debug_level > 0 THEN
3115 oe_debug_pub.add( 'HZ CREATE_CUST_ACCT_ROLES API ERROR ' ) ;
3116 END IF;
3117 IF l_debug_level > 0 THEN
3118 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
3119 END IF;
3120 IF l_debug_level > 0 THEN
3121 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
3122 END IF;
3123 x_return_status := l_return_status;
3124 IF l_debug_level > 0 THEN
3125 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3126 END IF;
3127 Update_Error_Flag(p_rowid => contact_rec.rowid);
3128 oe_msg_pub.transfer_msg_stack;
3129 fnd_msg_pub.delete_msg;
3130 return;
3131 End If;
3132 IF l_debug_level > 0 THEN
3133 oe_debug_pub.add( 'NEW CUST ACC. ROLE => ' || X_CUST_ACCOUNT_ROLE_ID ) ;
3134 END IF;
3135 x_contact_id := x_cust_account_role_id;
3136
3137 If contact_rec.New_Contact_Id Is Null and
3138 l_type_of_contact Is Not Null Then
3139 Check_and_Create_Contact(p_contact_party_id => x_rel_party_id,
3140 p_cust_acct_id => l_customer_info_id,
3141 p_usage_type => l_type_of_contact,
3142 x_contact_id => l_ret_contact_id,
3143 x_return_status => l_return_status
3144 );
3145 IF l_debug_level > 0 THEN
3146 oe_debug_pub.add( 'NEW CONTACT ID IS THERE FOR SITE ' || L_TYPE_OF_CONTACT || ' , RETURING THE EXISTING VAL' ) ;
3147 END IF;
3148 x_contact_id := l_ret_Contact_id;
3149 End If;
3150
3151 -- Let us select the Name to Pass back to calling api
3152 Select party_name
3153 Into x_contact_name
3154 From hz_parties
3155 Where party_id = l_contact_party_id;
3156 -- End of select to get name
3157
3158 -- Now Update the table with the new values
3159
3160 Update oe_customer_info_iface_all
3161 Set New_Contact_Id = x_contact_id,
3162 New_Party_Id = x_rel_party_id,
3163 is_ship_to_address =
3164 decode(l_type_of_contact,'SHIP_TO','Y',is_ship_to_address),
3165 is_bill_to_address =
3166 decode(l_type_of_contact,'BILL_TO','Y',is_bill_to_address),
3167 is_deliver_to_address =
3168 decode(l_type_of_contact,'DELIVER_TO','Y',is_deliver_to_address)
3169 Where rowid = contact_rec.rowid;
3170
3171 -- x_contact_id := l_contact_party_id;
3172 IF l_debug_level > 0 THEN
3173 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT ' ) ;
3174 END IF;
3175 -- End CONTACT role for the new contact}
3176
3177 Exception
3178 When Others Then
3179 IF l_debug_level > 0 THEN
3180 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_CONTACT. ABORT PROCESSING' ) ;
3181 END IF;
3182 IF l_debug_level > 0 THEN
3183 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3184 END IF;
3185 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
3186 fnd_message.set_token('API_NAME', 'Create_Contact');
3187 oe_msg_pub.add;
3188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3189 Update_Error_Flag(p_rowid => contact_rec.rowid);
3190 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
3191 IF l_debug_level > 0 THEN
3192 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3193 END IF;
3194 End;
3195 -- End of Begin after Loop }
3196
3197 End Loop;
3198 -- End Contact Loop }
3199
3200 If l_no_record_exists Then
3201 IF l_debug_level > 0 THEN
3202 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED REF , PLEASE CHECK DATA' ) ;
3203 END IF;
3204 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
3205 fnd_message.set_token('REFERENCE', p_customer_info_ref);
3206 oe_msg_pub.add;
3207 x_return_status := FND_API.G_RET_STS_ERROR;
3208 IF l_debug_level > 0 THEN
3209 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3210 END IF;
3211 End If;
3212
3213 IF l_debug_level > 0 THEN
3214 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT' ) ;
3215 END IF;
3216 Exception
3217 When Others Then
3218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3219 IF l_debug_level > 0 THEN
3220 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_CONTACT. ABORT PROCESSING' ) ;
3221 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3222 END IF;
3223 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
3224 fnd_message.set_token('API_NAME', 'Create_Contact');
3225 oe_msg_pub.add;
3226 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
3227 IF l_debug_level > 0 THEN
3228 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3229 END IF;
3230 End Create_Contact;
3231 -- End procedure Create Contact }
3232
3233
3234 -- { Start of procedure Create_Customer_Info
3235
3236 PROCEDURE Create_Customer_Info(
3237 p_customer_info_ref IN Varchar2,
3238 p_customer_info_type_code IN Varchar2,
3239 p_usage IN Varchar2,
3240 p_orig_sys_document_ref IN Varchar2,
3241 p_orig_sys_line_ref IN Varchar2,
3242 p_order_source_id IN Number,
3243 p_org_id IN Number,
3244 x_customer_info_id OUT NOCOPY /* file.sql.39 change */ Number,
3245 x_customer_info_number OUT NOCOPY /* file.sql.39 change */ Varchar2,
3246 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
3247 )
3248 Is
3249 l_customer_info_ref Varchar2(50) := p_customer_info_ref;
3250 l_customer_info_id Number;
3251 l_customer_info_number Varchar2(30);
3252 l_customer_party_id Number;
3253 l_contact_id Number;
3254 l_contact_name Varchar2(360);
3255 l_type_of_contact Varchar2(10) := p_usage;
3256 l_type_of_address Varchar2(10) := p_usage;
3257 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
3258 l_usage_site_id Number;
3259 l_ship_to_org_id Number;
3260 l_bill_to_org_id Number;
3261 l_deliver_to_org_id Number;
3262 l_existing_value Varchar2(1) := 'N';
3263 l_orig_sys_line_ref Varchar2(50) := p_orig_sys_line_ref;
3264
3265 -- Following cursor will fetch the data for the passed ref and type --
3266 -- information. For the address creation. --
3267 -- {
3268 Cursor l_address_info_cur Is
3269 Select country,
3270 address1,
3271 address2,
3272 address3,
3273 address4,
3274 city,
3275 postal_code,
3276 state,
3277 province,
3278 county,
3279 is_ship_to_address,
3280 is_bill_to_address,
3281 is_deliver_to_address,
3282 attribute_category,
3283 attribute1,
3284 attribute2,
3285 attribute3,
3286 attribute4,
3287 attribute5,
3288 attribute6,
3289 attribute7,
3290 attribute8,
3291 attribute9,
3292 attribute10,
3293 attribute11,
3294 attribute12,
3295 attribute13,
3296 attribute14,
3297 attribute15,
3298 attribute16,
3299 attribute17,
3300 attribute18,
3301 attribute19,
3302 attribute20,
3303 global_attribute_category,
3304 global_attribute1,
3305 global_attribute2,
3306 global_attribute3,
3307 global_attribute4,
3308 global_attribute5,
3309 global_attribute6,
3310 global_attribute7,
3311 global_attribute8,
3312 global_attribute9,
3313 global_attribute10,
3314 global_attribute11,
3315 global_attribute12,
3316 global_attribute13,
3317 global_attribute14,
3318 global_attribute15,
3319 global_attribute16,
3320 global_attribute17,
3321 global_attribute18,
3322 global_attribute19,
3323 global_attribute20
3324 from oe_customer_info_iface_all
3325 where customer_info_ref = l_customer_info_ref
3326 and customer_info_type_code = 'ADDRESS';
3327
3328 -- End of Cursor definition for l_address_info_cur }
3329
3330 --
3331 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3332 --
3333 Begin
3334
3335 x_return_status := FND_API.G_RET_STS_SUCCESS;
3336 -- Initialize the system paramter and profile option used
3337 -- later in processing
3338 If G_INITIALIZED = FND_API.G_FALSE Then
3339 Initialize_Global(l_return_status);
3340 End If;
3341
3342 -- Check for the type of entry need to be created based on the
3343 -- paramenter passed p_customer_info_type_code 'ACCOUNT', 'CONTACT' or
3344 -- 'ADDRESS'
3345 -- Depending on that call the respective api/processing...
3346
3347 -- Null p_orig_sys_line_ref if add ct info is called for Header
3348 If l_orig_sys_line_ref = FND_API.G_MISS_CHAR Then
3349 l_orig_sys_line_ref := Null;
3350 End If;
3351
3352 -- { Start of if for p_customer_info_type_code
3353 if p_customer_info_type_code = 'ACCOUNT' then
3354 IF l_debug_level > 0 THEN
3355 oe_debug_pub.add( 'BEFORE CALLING CREATE ACCOUNT PROCEDURE' ) ;
3356 END IF;
3357 -- call Create_Account api
3358 Create_Account( p_customer_info_ref => p_customer_info_ref,
3359 p_orig_sys_document_ref => p_orig_sys_document_ref,
3360 p_orig_sys_line_ref => l_orig_sys_line_ref,
3361 p_order_source_id => p_order_source_id,
3362 x_cust_account_id => l_customer_info_id,
3363 x_cust_account_number => l_customer_info_number,
3364 x_cust_party_id => l_customer_party_id,
3365 x_existing_value => l_existing_value,
3366 x_return_status => l_return_status
3367 );
3368
3369 IF l_debug_level > 0 THEN
3370 oe_debug_pub.add( 'AFTER CALLING CREATE ACCOUNT PROCEDURE' ) ;
3371 END IF;
3372
3373 -- Check for the return status of the api
3374 -- to return the proper information to the called program.
3375 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3376 IF l_debug_level > 0 THEN
3377 oe_debug_pub.add( 'RETURN STATUS IS NOT SUCCESS , AFTER CREATE ACC.' ) ;
3378 END IF;
3379 x_return_status := l_return_status;
3380 else
3381 IF l_debug_level > 0 THEN
3382 oe_debug_pub.add( 'RETURN STATUS IS SUCCESS , AFTER CREATE ACCOUNT' ) ;
3383 END IF;
3384 x_customer_info_id := l_customer_info_id;
3385 x_customer_info_number := l_customer_info_number;
3386 x_return_status := l_return_status;
3387 end if;
3388
3389 elsif p_customer_info_type_code = 'CONTACT' then
3390 -- call Create_Contact api
3391 Create_Contact(
3392 p_customer_info_ref => p_customer_info_ref,
3393 p_cust_account_id => l_customer_info_id,
3394 p_cust_account_number => l_customer_info_number,
3395 p_type_of_contact => l_type_of_contact,
3396 p_orig_sys_document_ref => p_orig_sys_document_ref,
3397 p_orig_sys_line_ref => l_orig_sys_line_ref,
3398 p_order_source_id => p_order_source_id,
3399 x_contact_id => l_contact_id,
3400 x_contact_name => l_contact_name,
3401 x_return_status => l_return_status
3402 );
3403
3404 -- Check for the return status of the api
3405 -- to return the proper information to the called program.
3406 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3407 IF l_debug_level > 0 THEN
3408 oe_debug_pub.add( 'RETURN STATUS IS NOT SUCCESS , AFTER CREATE CONT.' ) ;
3409 END IF;
3410 x_return_status := l_return_status;
3411 else
3412 IF l_debug_level > 0 THEN
3413 oe_debug_pub.add( 'RETURN STATUS IS SUCCESS , AFTER CREATE ACCOUNT' ) ;
3414 END IF;
3415 x_customer_info_id := l_contact_id;
3416 x_return_status := l_return_status;
3417 end if;
3418 elsif p_customer_info_type_code = 'ADDRESS' then
3419 -- call Create_Address api
3420 Create_Address(p_customer_info_ref => p_customer_info_ref,
3421 p_type_of_address => l_type_of_address,
3422 p_orig_sys_document_ref => p_orig_sys_document_ref,
3423 p_orig_sys_line_ref => l_orig_sys_line_ref,
3424 p_order_source_id => p_order_source_id,
3425 p_org_id => p_org_id,
3426 x_usage_site_id => l_usage_site_id,
3427 x_return_status => l_return_status
3428 );
3429 -- Create_Address;
3430 -- Check for the return status of the api
3431 -- to return the proper information to the called program.
3432 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
3433 IF l_debug_level > 0 THEN
3434 oe_debug_pub.add( 'RETURN STATUS IS NOT SUCCESS , AFTER CREATE ADDRESS.' ) ;
3435 END IF;
3436 x_return_status := l_return_status;
3437 Else
3438 IF l_debug_level > 0 THEN
3439 oe_debug_pub.add( 'RETURN STATUS IS SUCCESS , AFTER CREATE ADDRESS' ) ;
3440 END IF;
3441 -- { Start If
3442 If l_type_of_address = 'SHIP_TO' Then
3443 x_customer_info_id := l_usage_site_id;
3444 Elsif l_type_of_address = 'BILL_TO' Then
3445 x_customer_info_id := l_usage_site_id;
3446 Elsif l_type_of_address = 'DELIVER_TO' Then
3447 x_customer_info_id := l_usage_site_id;
3448 End If;
3449 -- End If}
3450 x_return_status := l_return_status;
3451 End If;
3452 else
3453 NULL;
3454 -- Wrong type of information passed
3455 -- set the error message stack with the error message and
3456 -- return error to calling api.
3457 end if;
3458 -- End of if for p_customer_info_type_code }
3459 Exception
3460 When Others Then
3461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3462 IF l_debug_level > 0 THEN
3463 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_CUSTOMER_INFO. ABORT PROCESSING' ) ;
3464 END IF;
3465 IF l_debug_level > 0 THEN
3466 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3467 END IF;
3468 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
3469 fnd_message.set_token('API_NAME', 'Create_Customer_Info');
3470 oe_msg_pub.add;
3471 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
3472 IF l_debug_level > 0 THEN
3473 oe_debug_pub.add( 'EXITING IN CREATE_CUSTOMER_INFO PROCEDURE WITH ERROR' ) ;
3474 END IF;
3475
3476 End Create_Customer_Info;
3477
3478 -- End of procedure Create_Customer_Info }
3479
3480 Procedure Delete_Customer_Info(
3481 p_header_customer_rec In OE_ORDER_IMPORT_SPECIFIC_PVT.Customer_Rec_Type,
3482 p_line_customer_tbl In OE_ORDER_IMPORT_SPECIFIC_PVT.Customer_Tbl_Type)
3483 Is
3484
3485 --
3486 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3487 --
3488 Begin
3489
3490 IF l_debug_level > 0 THEN
3491 oe_debug_pub.add( 'INSIDE DELETE_CUSTOMER_INFO' ) ;
3492 END IF;
3493
3494 If p_header_customer_rec.Orig_Sys_Customer_Ref IS NOT NULL Then
3495 Delete
3496 From oe_customer_info_iface_all a
3497 Where customer_info_ref = p_header_customer_rec.Orig_Sys_Customer_Ref
3498 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3499 From Oe_Headers_Iface_All b
3500 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3501 End If;
3502
3503 If p_header_customer_rec.Sold_To_Contact_Ref IS NOT NULL Then
3504
3505 Delete
3506 From oe_customer_info_iface_all a
3507 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3508 where customer_info_ref = p_header_customer_rec.Sold_To_Contact_Ref
3509 and customer_info_type_code = 'CONTACT')
3510 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3511 From Oe_Headers_Iface_All b
3512 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3513
3514 Delete
3515 From oe_customer_info_iface_all a
3516 Where customer_info_ref = p_header_customer_rec.Sold_To_Contact_Ref
3517 And customer_info_ref Not In (Select Sold_To_Contact_Ref
3518 From Oe_Headers_Iface_All b
3519 Where b.Sold_To_Contact_Ref = a.customer_info_ref);
3520 End If;
3521
3522 If p_header_customer_rec.Orig_Ship_Address_Ref IS NOT NULL Then
3523
3524
3525 Delete
3526 From oe_customer_info_iface_all a
3527 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3528 where customer_info_ref = p_header_customer_rec.Orig_Ship_Address_Ref
3529 and customer_info_type_code = 'ADDRESS' )
3530 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3531 From Oe_Headers_Iface_All b
3532 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3533
3534 Delete
3535 From oe_customer_info_iface_all a
3536 Where customer_info_ref = p_header_customer_rec.Orig_Ship_Address_Ref
3537 And customer_info_ref Not In ((Select Orig_Ship_Address_Ref
3538 From Oe_Headers_Iface_All b
3539 Where b.Orig_Ship_Address_Ref = a.customer_info_ref)
3540 UNION ALL
3541 (Select Orig_Ship_Address_Ref
3542 From Oe_Lines_Iface_All c
3543 Where c.Orig_Ship_Address_Ref = a.customer_info_ref));
3544 End If;
3545
3546 If p_header_customer_rec.Orig_Bill_Address_Ref IS NOT NULL Then
3547
3548
3549 Delete
3550 From oe_customer_info_iface_all a
3551 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3552 where customer_info_ref = p_header_customer_rec.Orig_Bill_Address_Ref
3553 and customer_info_type_code = 'ADDRESS')
3554 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3555 From Oe_Headers_Iface_All b
3556 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3557
3558
3559
3560 Delete
3561 From oe_customer_info_iface_all a
3562 Where customer_info_ref = p_header_customer_rec.Orig_Bill_Address_Ref
3563 And customer_info_ref Not In ((Select Orig_Bill_Address_Ref
3564 From Oe_Headers_Iface_All b
3565 Where b.Orig_Bill_Address_Ref = a.customer_info_ref)
3566 UNION ALL
3567 (Select Orig_Bill_Address_Ref
3568 From Oe_Lines_Iface_All c
3569 Where c.Orig_Bill_Address_Ref = a.customer_info_ref));
3570 End If;
3571
3572 If p_header_customer_rec.Orig_Deliver_Address_Ref IS NOT NULL Then
3573
3574
3575 Delete
3576 From oe_customer_info_iface_all a
3577 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3578 where customer_info_ref = p_header_customer_rec.Orig_Deliver_Address_Ref
3579 and customer_info_type_code = 'ADDRESS')
3580 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3581 From Oe_Headers_Iface_All b
3582 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3583
3584 Delete
3585 From oe_customer_info_iface_all a
3586 Where customer_info_ref = p_header_customer_rec.Orig_Deliver_Address_Ref
3587 And customer_info_ref Not In ((Select Orig_Deliver_Address_Ref
3588 From Oe_Headers_Iface_All b
3589 Where b.Orig_Deliver_Address_Ref = a.customer_info_ref)
3590 UNION ALL
3591 (Select Orig_Deliver_Address_Ref
3592 From Oe_Lines_Iface_All c
3593 Where c.Orig_Deliver_Address_Ref = a.customer_info_ref));
3594 End If;
3595
3596 If p_header_customer_rec.Ship_To_Contact_Ref IS NOT NULL Then
3597
3598 Delete
3599 From oe_customer_info_iface_all a
3600 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3601 where customer_info_ref = p_header_customer_rec.Ship_To_Contact_Ref
3602 and customer_info_type_code = 'CONTACT')
3603 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3604 From Oe_Headers_Iface_All b
3605 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3606
3607
3608
3609 Delete
3610 From oe_customer_info_iface_all a
3611 Where customer_info_ref = p_header_customer_rec.Ship_To_Contact_Ref
3612 And customer_info_ref Not In ((Select Ship_To_Contact_Ref
3613 From Oe_Headers_Iface_All b
3614 Where b.Ship_To_Contact_Ref = a.customer_info_ref)
3615 UNION ALL
3616 (Select Ship_To_Contact_Ref
3617 From Oe_Lines_Iface_All c
3618 Where c.Ship_To_Contact_Ref = a.customer_info_ref));
3619 End If;
3620
3621 If p_header_customer_rec.Bill_To_Contact_Ref IS NOT NULL Then
3622
3623 Delete
3624 From oe_customer_info_iface_all a
3625 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3626 where customer_info_ref = p_header_customer_rec.Bill_To_Contact_Ref
3627 and customer_info_type_code = 'CONTACT')
3628 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3629 From Oe_Headers_Iface_All b
3630 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3631
3632
3633 Delete
3634 From oe_customer_info_iface_all a
3635 Where customer_info_ref = p_header_customer_rec.Bill_To_Contact_Ref
3636 And customer_info_ref Not In ((Select Bill_To_Contact_Ref
3637 From Oe_Headers_Iface_All b
3638 Where b.Bill_To_Contact_Ref = a.customer_info_ref)
3639 UNION ALL
3640 (Select Bill_To_Contact_Ref
3641 From Oe_Lines_Iface_All c
3642 Where c.Bill_To_Contact_Ref = a.customer_info_ref));
3643 End If;
3644
3645 If p_header_customer_rec.Deliver_To_Contact_Ref IS NOT NULL Then
3646
3647
3648 Delete
3649 From oe_customer_info_iface_all a
3650 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3651 where customer_info_ref = p_header_customer_rec.Deliver_To_Contact_Ref
3652 and customer_info_type_code = 'CONTACT')
3653 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3654 From Oe_Headers_Iface_All b
3655 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3656
3657
3658 Delete
3659 From oe_customer_info_iface_all a
3660 Where customer_info_ref = p_header_customer_rec.Deliver_To_Contact_Ref
3661 And customer_info_ref Not In ((Select Deliver_To_Contact_Ref
3662 From Oe_Headers_Iface_All b
3663 Where b.Deliver_To_Contact_Ref = a.customer_info_ref)
3664 UNION ALL
3665 (Select Deliver_To_Contact_Ref
3666 From Oe_Lines_Iface_All c
3667 Where c.Deliver_To_Contact_Ref = a.customer_info_ref));
3668 End If;
3669
3670 For i In 1..p_line_customer_tbl.COUNT Loop
3671 If p_line_customer_tbl(i).Orig_Ship_Address_Ref IS NOT NULL Then
3672
3673 Delete
3674 From oe_customer_info_iface_all a
3675 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3676 where customer_info_ref = p_line_customer_tbl(i).Orig_Ship_Address_Ref
3677 and customer_info_type_code = 'ADDRESS' )
3678 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3679 From Oe_Headers_Iface_All b
3680 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3681
3682
3683 Delete
3684 From oe_customer_info_iface_all a
3685 Where customer_info_ref = p_line_customer_tbl(i).Orig_Ship_Address_Ref
3686 And customer_info_ref Not In ((Select Orig_Ship_Address_Ref
3687 From Oe_Lines_Iface_All b
3688 Where b.Orig_Ship_Address_Ref = a.customer_info_ref)
3689 UNION ALL
3690 (Select Orig_Ship_Address_Ref
3691 From Oe_Headers_Iface_All c
3692 Where c.Orig_Ship_Address_Ref = a.customer_info_ref));
3693 End If;
3694
3695 If p_line_customer_tbl(i).Orig_Bill_Address_Ref IS NOT NULL Then
3696
3697 Delete
3698 From oe_customer_info_iface_all a
3699 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3700 where customer_info_ref = p_line_customer_tbl(i).Orig_Bill_Address_Ref
3701 and customer_info_type_code = 'ADDRESS' )
3702 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3703 From Oe_Headers_Iface_All b
3704 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3705
3706
3707 Delete
3708 From oe_customer_info_iface_all a
3709 Where customer_info_ref = p_line_customer_tbl(i).Orig_Bill_Address_Ref
3710 And customer_info_ref Not In ((Select Orig_Bill_Address_Ref
3711 From Oe_Lines_Iface_All b
3712 Where b.Orig_Bill_Address_Ref = a.customer_info_ref)
3713 UNION ALL
3714 (Select Orig_Bill_Address_Ref
3715 From Oe_Headers_Iface_All c
3716 Where c.Orig_Bill_Address_Ref = a.customer_info_ref));
3717 End If;
3718
3719 If p_line_customer_tbl(i).Orig_Deliver_Address_Ref IS NOT NULL Then
3720
3721 Delete
3722 From oe_customer_info_iface_all a
3723 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3724 where customer_info_ref = p_line_customer_tbl(i).Orig_Deliver_Address_Ref
3725 and customer_info_type_code = 'ADDRESS')
3726 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3727 From Oe_Headers_Iface_All b
3728 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3729
3730
3731 Delete
3732 From oe_customer_info_iface_all a
3733 Where customer_info_ref = p_line_customer_tbl(i).Orig_Deliver_Address_Ref
3734 And customer_info_ref Not In ((Select Orig_Deliver_Address_Ref
3735 From Oe_Lines_Iface_All b
3736 Where b.Orig_Deliver_Address_Ref = a.customer_info_ref)
3737 UNION ALL
3738 (Select Orig_Deliver_Address_Ref
3739 From Oe_Headers_Iface_All c
3740 Where c.Orig_Deliver_Address_Ref = a.customer_info_ref));
3741 End If;
3742
3743 If p_line_customer_tbl(i).Ship_To_Contact_Ref IS NOT NULL Then
3744
3745
3746 Delete
3747 From oe_customer_info_iface_all a
3748 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3749 where customer_info_ref = p_line_customer_tbl(i).Ship_To_Contact_Ref
3750 and customer_info_type_code = 'CONTACT')
3751 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3752 From Oe_Headers_Iface_All b
3753 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3754
3755
3756 Delete
3757 From oe_customer_info_iface_all a
3758 Where customer_info_ref = p_line_customer_tbl(i).Ship_To_Contact_Ref
3759 And customer_info_ref Not In ((Select Ship_To_Contact_Ref
3760 From Oe_Lines_Iface_All b
3761 Where b.Ship_To_Contact_Ref = a.customer_info_ref)
3762 UNION ALL
3763 (Select Ship_To_Contact_Ref
3764 From Oe_Headers_Iface_All c
3765 Where c.Ship_To_Contact_Ref = a.customer_info_ref));
3766 End If;
3767
3768 If p_line_customer_tbl(i).Bill_To_Contact_Ref IS NOT NULL Then
3769
3770
3771 Delete
3772 From oe_customer_info_iface_all a
3773 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3774 where customer_info_ref = p_line_customer_tbl(i).Bill_To_Contact_Ref
3775 and customer_info_type_code = 'CONTACT')
3776 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3777 From Oe_Headers_Iface_All b
3778 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3779
3780
3781 Delete
3782 From oe_customer_info_iface_all a
3783 Where customer_info_ref = p_line_customer_tbl(i).Bill_To_Contact_Ref
3784 And customer_info_ref Not In ((Select Bill_To_Contact_Ref
3785 From Oe_Lines_Iface_All b
3786 Where b.Bill_To_Contact_Ref = a.customer_info_ref)
3787 UNION ALL
3788 (Select Bill_To_Contact_Ref
3789 From Oe_Headers_Iface_All c
3790 Where c.Bill_To_Contact_Ref = a.customer_info_ref));
3791 End If;
3792
3793 If p_line_customer_tbl(i).Deliver_To_Contact_Ref IS NOT NULL Then
3794
3795 Delete
3796 From oe_customer_info_iface_all a
3797 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3798 where customer_info_ref = p_line_customer_tbl(i).Deliver_To_Contact_Ref
3799 and customer_info_type_code = 'CONTACT')
3800 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3801 From Oe_Headers_Iface_All b
3802 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3803
3804
3805
3806 Delete
3807 From oe_customer_info_iface_all a
3808 Where customer_info_ref = p_line_customer_tbl(i).Deliver_To_Contact_Ref
3809 And customer_info_ref Not In ((Select Deliver_To_Contact_Ref
3810 From Oe_Lines_Iface_All b
3811 Where b.Deliver_To_Contact_Ref = a.customer_info_ref)
3812 UNION ALL
3813 (Select Deliver_To_Contact_Ref
3814 From Oe_Headers_Iface_All c
3815 Where c.Deliver_To_Contact_Ref = a.customer_info_ref));
3816 End If;
3817
3818 End Loop;
3819 Exception
3820 When Others Then
3821 IF l_debug_level > 0 THEN
3822 oe_debug_pub.add( 'PROBLEM IN CALL TO DELETE_CUSTOMER_INFO. ABORT PROCESSING' ) ;
3823 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3824 END IF;
3825 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
3826 fnd_message.set_token('API_NAME', 'Delete_Customer_Info');
3827 oe_msg_pub.add;
3828 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
3829 IF l_debug_level > 0 THEN
3830 oe_debug_pub.add( 'EXITING IN DELETE_CUSTOMER_INFO PROCEDURE WITH ERROR' ) ;
3831 END IF;
3832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3833 End Delete_Customer_Info;
3834
3835 END OE_INLINE_CUSTOMER_PUB;
[Home] [Help]