1 PACKAGE BODY OE_INLINE_CUSTOMER_PUB AS
2 /* $Header: OEXPINLB.pls 120.8.12020000.2 2013/03/23 13:50:56 sujithku 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 -- Commenting Attribute passing to Party as per Bug 16423322
2091 /*l_party_site_rec.attribute_category := address_info_rec.attribute_category;
2092 l_party_site_rec.attribute1 := address_info_rec.attribute1;
2093 l_party_site_rec.attribute2 := address_info_rec.attribute2;
2094 l_party_site_rec.attribute3 := address_info_rec.attribute3;
2095 l_party_site_rec.attribute4 := address_info_rec.attribute4;
2096 l_party_site_rec.attribute5 := address_info_rec.attribute5;
2097 l_party_site_rec.attribute6 := address_info_rec.attribute6;
2098 l_party_site_rec.attribute7 := address_info_rec.attribute7;
2099 l_party_site_rec.attribute8 := address_info_rec.attribute8;
2100 l_party_site_rec.attribute9 := address_info_rec.attribute9;
2101 l_party_site_rec.attribute10 := address_info_rec.attribute10;
2102 l_party_site_rec.attribute11 := address_info_rec.attribute11;
2103 l_party_site_rec.attribute12 := address_info_rec.attribute12;
2104 l_party_site_rec.attribute13 := address_info_rec.attribute13;
2105 l_party_site_rec.attribute14 := address_info_rec.attribute14;
2106 l_party_site_rec.attribute15 := address_info_rec.attribute15;
2107 l_party_site_rec.attribute16 := address_info_rec.attribute16;
2108 l_party_site_rec.attribute17 := address_info_rec.attribute17;
2109 l_party_site_rec.attribute18 := address_info_rec.attribute18;
2110 l_party_site_rec.attribute19 := address_info_rec.attribute19;
2111 l_party_site_rec.attribute20 := address_info_rec.attribute20;*/
2112 l_party_site_rec.created_by_module := G_CREATED_BY_MODULE;
2113 l_party_site_rec.application_id := 660;
2114
2115 HZ_PARTY_SITE_V2PUB.Create_Party_Site
2116 (
2117 p_party_site_rec => l_party_site_rec,
2118 x_party_site_id => l_party_site_id,
2119 x_party_site_number => l_party_site_number,
2120 x_return_status => l_return_status,
2121 x_msg_count => l_msg_count,
2122 x_msg_data => l_msg_data
2123 );
2124 IF l_debug_level > 0 THEN
2125 oe_debug_pub.add( 'AFTER HZ CREATE_PARTY_SITE API' ) ;
2126 END IF;
2127 IF l_debug_level > 0 THEN
2128 oe_debug_pub.add( 'PARTY_SITE_ID = '||L_PARTY_SITE_ID ) ;
2129 END IF;
2130 IF l_debug_level > 0 THEN
2131 oe_debug_pub.add( 'PARTY_SITE_NUMBER = '||L_PARTY_SITE_NUMBER ) ;
2132 END IF;
2133
2134 -- Let us check the status of the call to hz api
2135 IF l_debug_level > 0 THEN
2136 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2137 END IF;
2138 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2139 IF l_debug_level > 0 THEN
2140 oe_debug_pub.add( 'HZ CREATE_PARTY_SITE API ERROR ' ) ;
2141 END IF;
2142 IF l_debug_level > 0 THEN
2143 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2144 END IF;
2145 IF l_debug_level > 0 THEN
2146 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2147 END IF;
2148 x_return_status := l_return_status;
2149 IF l_debug_level > 0 THEN
2150 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2151 END IF;
2152 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2153 oe_msg_pub.transfer_msg_stack;
2154 fnd_msg_pub.delete_msg;
2155 return;
2156 End If;
2157
2158 IF l_debug_level > 0 THEN
2159 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCOUNT_SITE API' ) ;
2160 END IF;
2161
2162 l_account_site_rec.party_site_id := l_party_site_id;
2163 l_account_site_rec.cust_account_id := l_customer_info_id;
2164 l_account_site_rec.attribute_category := address_info_rec.attribute_category;
2165 l_account_site_rec.attribute1 := address_info_rec.attribute1;
2166 l_account_site_rec.attribute2 := address_info_rec.attribute2;
2167 l_account_site_rec.attribute3 := address_info_rec.attribute3;
2168 l_account_site_rec.attribute4 := address_info_rec.attribute4;
2169 l_account_site_rec.attribute5 := address_info_rec.attribute5;
2170 l_account_site_rec.attribute6 := address_info_rec.attribute6;
2171 l_account_site_rec.attribute7 := address_info_rec.attribute7;
2172 l_account_site_rec.attribute8 := address_info_rec.attribute8;
2173 l_account_site_rec.attribute9 := address_info_rec.attribute9;
2174 l_account_site_rec.attribute10 := address_info_rec.attribute10;
2175 l_account_site_rec.attribute11 := address_info_rec.attribute11;
2176 l_account_site_rec.attribute12 := address_info_rec.attribute12;
2177 l_account_site_rec.attribute13 := address_info_rec.attribute13;
2178 l_account_site_rec.attribute14 := address_info_rec.attribute14;
2179 l_account_site_rec.attribute15 := address_info_rec.attribute15;
2180 l_account_site_rec.attribute16 := address_info_rec.attribute16;
2181 l_account_site_rec.attribute17 := address_info_rec.attribute17;
2182 l_account_site_rec.attribute18 := address_info_rec.attribute18;
2183 l_account_site_rec.attribute19 := address_info_rec.attribute19;
2184 l_account_site_rec.attribute20 := address_info_rec.attribute20;
2185 l_account_site_rec.global_attribute_category := address_info_rec.global_attribute_category;
2186 l_account_site_rec.global_attribute1 := address_info_rec.global_attribute1;
2187 l_account_site_rec.global_attribute2 := address_info_rec.global_attribute2;
2188 l_account_site_rec.global_attribute3 := address_info_rec.global_attribute3;
2189 l_account_site_rec.global_attribute4 := address_info_rec.global_attribute4;
2190 l_account_site_rec.global_attribute5 := address_info_rec.global_attribute5;
2191 l_account_site_rec.global_attribute6 := address_info_rec.global_attribute6;
2192 l_account_site_rec.global_attribute7 := address_info_rec.global_attribute7;
2193 l_account_site_rec.global_attribute8 := address_info_rec.global_attribute8;
2194 l_account_site_rec.global_attribute9 := address_info_rec.global_attribute9;
2195 l_account_site_rec.global_attribute10 := address_info_rec.global_attribute10;
2196 l_account_site_rec.global_attribute11 := address_info_rec.global_attribute11;
2197 l_account_site_rec.global_attribute12 := address_info_rec.global_attribute12;
2198 l_account_site_rec.global_attribute13 := address_info_rec.global_attribute13;
2199 l_account_site_rec.global_attribute14 := address_info_rec.global_attribute14;
2200 l_account_site_rec.global_attribute15 := address_info_rec.global_attribute15;
2201 l_account_site_rec.global_attribute16 := address_info_rec.global_attribute16;
2202 l_account_site_rec.global_attribute17 := address_info_rec.global_attribute17;
2203 l_account_site_rec.global_attribute18 := address_info_rec.global_attribute18;
2204 l_account_site_rec.global_attribute19 := address_info_rec.global_attribute19;
2205 l_account_site_rec.global_attribute20 := address_info_rec.global_attribute20;
2206 l_account_site_rec.created_by_module := G_CREATED_BY_MODULE;
2207 l_account_site_rec.application_id := 660;
2208 l_account_site_rec.org_id := p_org_id;
2209
2210 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Acct_Site
2211 (
2212 p_cust_acct_site_rec => l_account_site_rec,
2213 x_return_status => l_return_status,
2214 x_msg_count => l_msg_count,
2215 x_msg_data => l_msg_data,
2216 x_cust_acct_site_id => l_customer_site_id
2217 );
2218 IF l_debug_level > 0 THEN
2219 oe_debug_pub.add( 'AFTER HZ CREATE_ACCOUNT_SITE API' ) ;
2220 END IF;
2221 IF l_debug_level > 0 THEN
2222 oe_debug_pub.add( 'CUSTOMER_SITE_ID = '||L_CUSTOMER_SITE_ID ) ;
2223 END IF;
2224
2225 -- Let us check the status of the call to hz api
2226 IF l_debug_level > 0 THEN
2227 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2228 END IF;
2229 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2230 IF l_debug_level > 0 THEN
2231 oe_debug_pub.add( 'HZ CREATE_ACCOUNT_SITE API ERROR ' ) ;
2232 END IF;
2233 IF l_debug_level > 0 THEN
2234 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2235 END IF;
2236 IF l_debug_level > 0 THEN
2237 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2238 END IF;
2239 x_return_status := l_return_status;
2240 IF l_debug_level > 0 THEN
2241 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2242 END IF;
2243 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2244 oe_msg_pub.transfer_msg_stack;
2245 fnd_msg_pub.delete_msg;
2246 return;
2247 End If;
2248
2249 IF l_debug_level > 0 THEN
2250 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCOUNT_SITE_USES API' ) ;
2251 END IF;
2252
2253 l_acct_site_uses.cust_acct_site_id := l_customer_site_id;
2254 l_acct_site_uses.location := l_location_number;
2255 l_acct_site_uses.created_by_module := G_CREATED_BY_MODULE;
2256 l_acct_site_uses.application_id := 660;
2257 l_acct_site_uses.org_id := p_org_id;
2258
2259 IF address_info_rec.is_ship_to_address = 'Y' THEN
2260 l_acct_site_uses.site_use_code := 'SHIP_TO';
2261 IF l_debug_level > 0 THEN
2262 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCT_SITE_USES FOR SHIP_TO' ) ;
2263 END IF;
2264 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use
2265 (
2266 p_cust_site_use_rec => l_acct_site_uses,
2267 p_customer_profile_rec => l_cust_profile_rec,
2268 p_create_profile => FND_API.G_FALSE,
2269 x_return_status => l_return_status,
2270 x_msg_count => l_msg_count,
2271 x_msg_data => l_msg_data,
2272 x_site_use_id => l_site_use_id_ship
2273 );
2274 -- Let us check the status of the call to hz api
2275 IF l_debug_level > 0 THEN
2276 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2277 END IF;
2278 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2279 IF l_debug_level > 0 THEN
2280 oe_debug_pub.add( 'HZ CREATE_SITE_USAGE API ERROR ' ) ;
2281 END IF;
2282 IF l_debug_level > 0 THEN
2283 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2284 END IF;
2285 IF l_debug_level > 0 THEN
2286 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2287 END IF;
2288 x_return_status := l_return_status;
2289 IF l_debug_level > 0 THEN
2290 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2291 END IF;
2292 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2293 oe_msg_pub.transfer_msg_stack;
2294 fnd_msg_pub.delete_msg;
2295 return;
2296 End If;
2297
2298 IF l_debug_level > 0 THEN
2299 oe_debug_pub.add( 'AFTER HZ CREATE_ACCT_SITE_USES FOR SHIP_TO' ) ;
2300 END IF;
2301 IF l_debug_level > 0 THEN
2302 oe_debug_pub.add( 'SITE_USE_ID_SHIP = '||L_SITE_USE_ID_SHIP ) ;
2303 END IF;
2304 IF l_debug_level > 0 THEN
2305 oe_debug_pub.add( 'L_RETURN_STATUS = '||L_RETURN_STATUS ) ;
2306 END IF;
2307 If p_type_of_address = 'SHIP_TO' Then
2308 x_usage_site_id := l_site_use_id_ship;
2309 End if;
2310
2311 Update_Address_id( type_of_address => 'SHIP_TO',
2312 usage_site_id => l_site_use_id_ship,
2313 row_id => address_info_rec.rowid );
2314
2315 END IF;
2316
2317 IF address_info_rec.is_bill_to_address = 'Y' THEN
2318 l_acct_site_uses.site_use_code := 'BILL_TO';
2319 IF l_debug_level > 0 THEN
2320 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCT_SITE_USES FOR BILL_TO' ) ;
2321 END IF;
2322 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use
2323 (
2324 p_cust_site_use_rec => l_acct_site_uses,
2325 p_customer_profile_rec => l_cust_profile_rec,
2326 p_create_profile => FND_API.G_FALSE,
2327 x_return_status => l_return_status,
2328 x_msg_count => l_msg_count,
2329 x_msg_data => l_msg_data,
2330 x_site_use_id => l_site_use_id_bill
2331 );
2332 -- Let us check the status of the call to hz api
2333 IF l_debug_level > 0 THEN
2334 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2335 END IF;
2336 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2337 IF l_debug_level > 0 THEN
2338 oe_debug_pub.add( 'HZ CREATE_SITE_USAGE API ERROR ' ) ;
2339 END IF;
2340 IF l_debug_level > 0 THEN
2341 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2342 END IF;
2343 IF l_debug_level > 0 THEN
2344 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2345 END IF;
2346 x_return_status := l_return_status;
2347 IF l_debug_level > 0 THEN
2348 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2349 END IF;
2350 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2351 oe_msg_pub.transfer_msg_stack;
2352 fnd_msg_pub.delete_msg;
2353 return;
2354 End If;
2355
2356 IF l_debug_level > 0 THEN
2357 oe_debug_pub.add( 'AFTER HZ CREATE_ACCT_SITE_USES FOR BILL_TO' ) ;
2358 END IF;
2359 IF l_debug_level > 0 THEN
2360 oe_debug_pub.add( 'SITE_USE_ID_BILL = '||L_SITE_USE_ID_BILL ) ;
2361 END IF;
2362 If p_type_of_address = 'BILL_TO' Then
2363 x_usage_site_id := l_site_use_id_bill;
2364 End if;
2365
2366 Update_Address_id( type_of_address => 'BILL_TO',
2367 usage_site_id => l_site_use_id_bill,
2368 row_id => address_info_rec.rowid );
2369 END IF;
2370
2371 IF address_info_rec.is_deliver_to_address = 'Y' THEN
2372 l_acct_site_uses.site_use_code := 'DELIVER_TO';
2373 IF l_debug_level > 0 THEN
2374 oe_debug_pub.add( 'BEFORE HZ CREATE_ACCT_SITE_USES FOR DELIVER_TO ' ) ;
2375 END IF;
2376 HZ_CUST_ACCOUNT_SITE_V2PUB.Create_Cust_Site_Use
2377 (
2378 p_cust_site_use_rec => l_acct_site_uses,
2379 p_customer_profile_rec => l_cust_profile_rec,
2380 p_create_profile => FND_API.G_FALSE,
2381 x_return_status => l_return_status,
2382 x_msg_count => l_msg_count,
2383 x_msg_data => l_msg_data,
2384 x_site_use_id => l_site_use_id_deliver
2385 );
2386 -- Let us check the status of the call to hz api
2387 IF l_debug_level > 0 THEN
2388 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2389 END IF;
2390 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2391 IF l_debug_level > 0 THEN
2392 oe_debug_pub.add( 'HZ CREATE_SITE_USAGE API ERROR ' ) ;
2393 END IF;
2394 IF l_debug_level > 0 THEN
2395 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2396 END IF;
2397 IF l_debug_level > 0 THEN
2398 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2399 END IF;
2400 IF l_debug_level > 0 THEN
2401 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2402 END IF;
2403 x_return_status := l_return_status;
2404 IF l_debug_level > 0 THEN
2405 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2406 END IF;
2407 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2408 oe_msg_pub.transfer_msg_stack;
2409 fnd_msg_pub.delete_msg;
2410 return;
2411 End If;
2412
2413 IF l_debug_level > 0 THEN
2414 oe_debug_pub.add( 'AFTER HZ CREATE_ACCT_SITE_USES FOR DELIVER_TO' ) ;
2415 END IF;
2416 IF l_debug_level > 0 THEN
2417 oe_debug_pub.add( 'SITE_USE_ID_DELIVER = '||L_SITE_USE_ID_DELIVER ) ;
2418 END IF;
2419 If p_type_of_address = 'DELIVER_TO' Then
2420 x_usage_site_id := l_site_use_id_deliver;
2421 End if;
2422 Update_Address_id( type_of_address => 'DELIVER_TO',
2423 usage_site_id => l_site_use_id_deliver,
2424 row_id => address_info_rec.rowid );
2425
2426 END IF;
2427
2428 EXCEPTION
2429 When Others Then
2430 IF l_debug_level > 0 THEN
2431 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_ADDRESS. ABORT PROCESSING' ) ;
2432 END IF;
2433 IF l_debug_level > 0 THEN
2434 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
2435 END IF;
2436 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
2437 fnd_message.set_token('API_NAME', 'Create_Address');
2438 oe_msg_pub.add;
2439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2440 Update_Error_Flag(p_rowid => address_info_rec.rowid);
2441 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
2442 IF l_debug_level > 0 THEN
2443 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2444 END IF;
2445
2446 END;
2447 -- End of Begin after loop
2448 END LOOP;
2449 -- End of For loop
2450 If l_no_record_exists Then
2451 IF l_debug_level > 0 THEN
2452 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED REF , PLEASE CHECK DATA' ) ;
2453 END IF;
2454 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
2455 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2456 oe_msg_pub.add;
2457 x_return_status := FND_API.G_RET_STS_ERROR;
2458 IF l_debug_level > 0 THEN
2459 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2460 END IF;
2461 End If;
2462
2463 IF l_debug_level > 0 THEN
2464 oe_debug_pub.add( 'EXITING PROCEDURE CREATE ADDRESS' ) ;
2465 END IF;
2466 Exception
2467 When Others Then
2468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2469 IF l_debug_level > 0 THEN
2470 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_ADDRESS. ABORT PROCESSING' ) ;
2471 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
2472 END IF;
2473 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
2474 fnd_message.set_token('API_NAME', 'Create_Address');
2475 oe_msg_pub.add;
2476 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
2477 IF l_debug_level > 0 THEN
2478 oe_debug_pub.add( 'EXITING IN CREATE_ADDRESS PROCEDURE WITH ERROR' ) ;
2479 END IF;
2480
2481 End Create_Address;
2482 -- End procedure Create Address }
2483
2484
2485 -- { Start procedure Create Contact
2486 PROCEDURE Create_Contact(
2487 p_customer_info_ref IN Varchar2,
2488 p_cust_account_id IN OUT NOCOPY /* file.sql.39 change */ Number,
2489 p_cust_account_number IN OUT NOCOPY /* file.sql.39 change */ Varchar2,
2490 p_type_of_contact IN Varchar2,
2491 p_orig_sys_document_ref IN Varchar2,
2492 p_orig_sys_line_ref IN Varchar2,
2493 p_order_source_id IN Number,
2494 x_contact_id OUT NOCOPY /* file.sql.39 change */ Number,
2495 x_contact_name OUT NOCOPY /* file.sql.39 change */ Varchar2,
2496 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
2497 )
2498 IS
2499 l_person_rec HZ_PARTY_V2PUB.person_rec_type;
2500 l_party_rec HZ_PARTY_V2PUB.party_rec_type;
2501
2502 l_contact_party_id Number;
2503 l_contact_party_number Varchar2(50);
2504 l_customer_party_id Number;
2505 l_customer_party_number Varchar2(50);
2506 l_profile_id Number;
2507
2508 l_return_status Varchar2(1);
2509
2510 -- l_party_rel_rec HZ_RELATIONSHIP_V2PUB.relationship_rec_type;
2511 x_rel_party_id Number;
2512 x_rel_party_number hz_parties.party_number%TYPE;
2513 x_party_relationship_id Number;
2514
2515 l_org_contact_rec HZ_PARTY_CONTACT_V2PUB.org_contact_rec_type;
2516 x_org_contact_id Number;
2517
2518 x_cust_account_role_id Number;
2519 l_cust_acct_roles_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
2520
2521 l_gen_contact_number Varchar2(1);
2522
2523 l_customer_info_id Number;
2524 l_customer_info_number Varchar2(30);
2525 l_customer_info_ref Varchar2(50) := p_customer_info_ref;
2526 l_type_of_contact Varchar2(10) := p_type_of_contact;
2527 l_usage_site_id Number;
2528 l_ship_to_org_id Number;
2529 l_bill_to_org_id Number;
2530 l_deliver_to_org_id Number;
2531 l_ret_contact_id Number;
2532 l_existing_value Varchar2(1) := 'N';
2533
2534 l_msg_data Varchar2(2000);
2535 l_msg_count Number;
2536 l_contact_name Varchar2(2000);
2537 l_sold_to_contact Number;
2538 l_no_record_exists BOOLEAN := TRUE;
2539
2540 -- Following cursor will fetch the data for the passed ref and type --
2541 -- information. For the contact creation. --
2542 -- {
2543 Cursor l_contact_info_cur Is
2544 Select person_first_name,
2545 person_last_name,
2546 person_middle_name,
2547 person_name_suffix,
2548 person_title,
2549 email_address,
2550 phone_area_code,
2551 phone_number,
2552 phone_extension,
2553 current_customer_number,
2554 current_customer_id,
2555 parent_customer_ref,
2556 new_contact_id,
2557 new_party_id,
2558 is_ship_to_address,
2559 is_bill_to_address,
2560 is_deliver_to_address,
2561 rowid,
2562 phone_country_code
2563 from oe_customer_info_iface_all
2564 where customer_info_ref = l_customer_info_ref
2565 and customer_info_type_code = 'CONTACT';
2566
2567 -- End of Cursor definition for l_contact_info_cur }
2568
2569 --
2570 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2571 --
2572 Begin
2573 IF l_debug_level > 0 THEN
2574 oe_debug_pub.add( 'ENTERING PROCEDURE CREATE CONTACT' ) ;
2575 END IF;
2576
2577 x_return_status := FND_API.G_RET_STS_SUCCESS;
2578
2579 OE_MSG_PUB.set_msg_context(
2580 p_entity_code => 'OI_INL_ADDCUST'
2581 ,p_entity_ref => null
2582 ,p_entity_id => null
2583 ,p_header_id => null
2584 ,p_line_id => null
2585 -- ,p_batch_request_id => p_x_header_rec.request_id
2586 ,p_order_source_id => p_order_source_id
2587 ,p_orig_sys_document_ref => p_orig_sys_document_ref
2588 ,p_change_sequence => null
2589 ,p_orig_sys_document_line_ref => p_orig_sys_line_ref
2590 ,p_orig_sys_shipment_ref => p_customer_info_ref
2591 ,p_source_document_type_id => null
2592 ,p_source_document_id => null
2593 ,p_source_document_line_id => null
2594 ,p_attribute_code => null
2595 ,p_constraint_id => null
2596 );
2597
2598 -- { Start Contact Loop
2599 For contact_rec In l_contact_info_cur Loop
2600 -- { Start of Begin for Loop
2601 Begin
2602 l_no_record_exists := FALSE;
2603 -- Check if the Data is already used to create the New
2604 -- Customer then return that value and exit out of the
2605 -- process
2606
2607 IF l_debug_level > 0 THEN
2608 oe_debug_pub.add( 'INSIDE LOOP FOR THE CUSTOMER_INFO_CUR' ) ;
2609 END IF;
2610
2611 If contact_rec.New_Contact_Id Is Not Null And
2612 l_type_of_contact = 'SOLD_TO' Then
2613 IF l_debug_level > 0 THEN
2614 oe_debug_pub.add( 'NEW CONTACT ID IS THERE , RETURING THE EXISTING VAL' ) ;
2615 END IF;
2616 x_contact_id := contact_rec.New_Contact_id;
2617 IF l_debug_level > 0 THEN
2618 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH CURRENT VAL' ) ;
2619 END IF;
2620 RETURN;
2621 End If;
2622
2623 -- {Start Check if the contact is being created for the site then
2624 --then if that contact is alreary for that site then return the
2625 --contact_id otherwise need to make contact for new site too.
2626 If contact_rec.New_Contact_Id Is Not Null and
2627 l_type_of_contact Is Not Null Then
2628 If (l_type_of_contact = 'SHIP_TO' and
2629 contact_rec.is_ship_to_address = 'Y') Or
2630 (l_type_of_contact = 'BILL_TO' and
2631 contact_rec.is_bill_to_address = 'Y') Or
2632 (l_type_of_contact = 'DELIVER_TO' and
2633 contact_rec.is_deliver_to_address = 'Y') Then
2634 IF l_debug_level > 0 THEN
2635 oe_debug_pub.add( 'NEW CONTACT ID IS THERE FOR SITE ' || L_TYPE_OF_CONTACT || ' , RETURING THE EXISTING VAL' ) ;
2636 END IF;
2637 x_contact_id := contact_rec.New_Contact_id;
2638 IF l_debug_level > 0 THEN
2639 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH CURRENT VAL' ) ;
2640 END IF;
2641 RETURN;
2642 End If;
2643 End If;
2644 -- End of the site level contact check}
2645
2646 -- {Start of the checking of existing customer or not
2647 IF l_debug_level > 0 THEN
2648 oe_debug_pub.add( 'LET US CHECK IF THIS IS FOR EXISTING CUSTOMER' ) ;
2649 END IF;
2650
2651 If (contact_rec.current_customer_id IS NULL) And
2652 (contact_rec.current_customer_number) IS NULL Then
2653 --{Start of if parent rec is passed or not
2654 If contact_rec.parent_customer_ref is Null Then
2655 IF l_debug_level > 0 THEN
2656 oe_debug_pub.add( 'NO PARENT CUSTOMER REF POPULATED. CHECK THE DATA' ) ;
2657 END IF;
2658 x_return_status := FND_API.G_RET_STS_ERROR;
2659 fnd_message.set_name('ONT','ONT_OI_INL_NO_PARENT_INFO');
2660 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2661 oe_msg_pub.add;
2662 return;
2663 End If;
2664 -- End of if parent rec is passed or not}
2665
2666 IF l_debug_level > 0 THEN
2667 oe_debug_pub.add( 'BEFORE CALLING CREATE ACCOUNT PROCEDURE' ) ;
2668 END IF;
2669 -- call Create_Account api
2670 Create_Account( p_customer_info_ref => contact_rec.parent_customer_ref,
2671 p_orig_sys_document_ref => p_orig_sys_document_ref,
2672 p_orig_sys_line_ref => p_orig_sys_line_ref,
2673 p_order_source_id => p_order_source_id,
2674 x_cust_account_id => l_customer_info_id,
2675 x_cust_account_number => l_customer_info_number,
2676 x_cust_party_id => l_customer_party_id,
2677 x_existing_value => l_existing_value,
2678 x_return_status => l_return_status
2679 );
2680 IF l_debug_level > 0 THEN
2681 oe_debug_pub.add( 'AFTER CALLING CREATE ACCOUNT PROCEDURE' ) ;
2682 END IF;
2683
2684 -- Check for the return status of the api
2685 -- to return the proper information to the called program.
2686 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2687 IF l_debug_level > 0 THEN
2688 oe_debug_pub.add( 'RETURN STATUS IS NOT SUCCESS , AFTER CREATE ACC. FOR CONTACT' ) ;
2689 END IF;
2690 x_return_status := l_return_status;
2691 IF l_debug_level > 0 THEN
2692 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH ERROR' ) ;
2693 END IF;
2694 Return;
2695 End if;
2696 -- End of the checking of the Parent Info and creation}
2697
2698 Else
2699 IF l_debug_level > 0 THEN
2700 oe_debug_pub.add( 'FOR EXISTING CUSTOMER' ) ;
2701 END IF;
2702 If contact_rec.current_customer_id IS NOT NULL Then
2703 l_customer_info_id := contact_rec.Current_Customer_id;
2704 Begin
2705 Select party_id
2706 Into l_customer_party_id
2707 From hz_cust_accounts
2708 Where cust_account_id = l_customer_info_id;
2709 Exception
2710 When No_Data_Found Then
2711 IF l_debug_level > 0 THEN
2712 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED EXISTING CUSTOMER , PLEASE CHECK DATA' ) ;
2713 END IF;
2714 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
2715 fnd_message.set_token('REFERENCE', contact_rec.Current_Customer_id);
2716 oe_msg_pub.add;
2717 x_return_status := FND_API.G_RET_STS_ERROR;
2718 Return;
2719 End;
2720 Else
2721 --Validate customer
2722 Validate_Customer_Number(p_customer_number => contact_rec.Current_Customer_Number,
2723 x_party_id => l_customer_party_id,
2724 x_account_id => l_customer_info_id,
2725 x_return_status => l_return_status
2726 );
2727 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2728 x_return_status := l_return_status;
2729 Return;
2730 End If;
2731 End If;
2732 End If;
2733 -- End of the checking of existing customer or not}
2734
2735 -- { Start of duplicate check for contact
2736 -- concatenate to get name of contact
2737 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)
2738 Into l_contact_name
2739 From Dual;
2740
2741 -- { Start of If for duplicate contact check
2742 If l_type_of_contact = 'SOLD_TO' Then
2743 l_sold_to_contact := Oe_Value_To_Id.Sold_To_Contact(
2744 p_sold_to_contact => l_contact_name,
2745 p_sold_to_org_id => l_customer_info_id);
2746 If l_sold_to_contact <> FND_API.G_MISS_NUM Then
2747 -- Raise Error and Abort Processing
2748 IF l_debug_level > 0 THEN
2749 oe_debug_pub.add( 'TRYING TO ENTER THE SOLD_TO CONTACT WHICH ALREADY EXISTS' ) ;
2750 END IF;
2751 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
2752 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2753 oe_msg_pub.add;
2754 x_return_status := FND_API.G_RET_STS_ERROR;
2755 Update_Error_Flag(p_rowid => contact_rec.rowid);
2756 Return;
2757 Else
2758 -- As the data is not duplicate but the call to oe_value_to_id
2759 -- has entered one error message in stack(necessary evil!)
2760 -- What to do => here is solution delete it
2761 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2762 End if; -- duplicate sold_to contact check
2763 Elsif l_type_of_contact = 'SHIP_TO' Then
2764 l_sold_to_contact := Oe_Value_To_Id.Ship_To_Contact(
2765 p_ship_to_contact => l_contact_name,
2766 p_ship_to_org_id => l_usage_site_id);
2767 If l_sold_to_contact <> FND_API.G_MISS_NUM Then
2768 -- Raise Error and Abort Processing
2769 IF l_debug_level > 0 THEN
2770 oe_debug_pub.add( 'TRYING TO ENTER THE SHIP_TO CONTACT WHICH ALREADY EXISTS' ) ;
2771 END IF;
2772 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
2773 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2774 oe_msg_pub.add;
2775 x_return_status := FND_API.G_RET_STS_ERROR;
2776 Update_Error_Flag(p_rowid => contact_rec.rowid);
2777 Return;
2778 Else
2779 -- As the data is not duplicate but the call to oe_value_to_id
2780 -- has entered one error message in stack(necessary evil!)
2781 -- What to do => here is solution delete it
2782 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2783 End if; -- duplicate ship_to contact check
2784 Elsif l_type_of_contact = 'BILL_TO' Then
2785 l_sold_to_contact := Oe_Value_To_Id.Invoice_To_Contact(
2786 p_invoice_to_contact => l_contact_name,
2787 p_invoice_to_org_id => l_usage_site_id);
2788 If l_sold_to_contact <> FND_API.G_MISS_NUM Then
2789 -- Raise Error and Abort Processing
2790 IF l_debug_level > 0 THEN
2791 oe_debug_pub.add( 'TRYING TO ENTER THE BILL_TO CONTACT WHICH ALREADY EXISTS' ) ;
2792 END IF;
2793 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
2794 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2795 oe_msg_pub.add;
2796 x_return_status := FND_API.G_RET_STS_ERROR;
2797 Update_Error_Flag(p_rowid => contact_rec.rowid);
2798 Return;
2799 Else
2800 -- As the data is not duplicate but the call to oe_value_to_id
2801 -- has entered one error message in stack(necessary evil!)
2802 -- What to do => here is solution delete it
2803 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2804 End if; -- duplicate bill_to contact check
2805 Elsif l_type_of_contact = 'DELIVER_TO' Then
2806 l_sold_to_contact := Oe_Value_To_Id.Deliver_To_Contact(
2807 p_deliver_to_contact => l_contact_name,
2808 p_deliver_to_org_id => l_usage_site_id);
2809 If l_sold_to_contact <> FND_API.G_MISS_NUM Then
2810 -- Raise Error and Abort Processing
2811 IF l_debug_level > 0 THEN
2812 oe_debug_pub.add( 'TRYING TO ENTER THE DELIVER_TO CONTACT WHICH ALREADY EXISTS' ) ;
2813 END IF;
2814 fnd_message.set_name('ONT','ONT_OI_INL_DUPLICATE');
2815 fnd_message.set_token('REFERENCE', p_customer_info_ref);
2816 oe_msg_pub.add;
2817 x_return_status := FND_API.G_RET_STS_ERROR;
2818 Update_Error_Flag(p_rowid => contact_rec.rowid);
2819 Return;
2820 Else
2821 -- As the data is not duplicate but the call to oe_value_to_id
2822 -- has entered one error message in stack(necessary evil!)
2823 -- What to do => here is solution delete it
2824 oe_msg_pub.delete_msg(oe_msg_pub.g_msg_count);
2825 End if; -- duplicate deliver_to contact check
2826 End if;
2827 -- End of If for duplicate contact check}
2828
2829
2830
2831 -- { Start Let us now check to see that we are here just to create
2832 -- a new site level contact for the exiting contact or the contact
2833 -- itself is not there and we have to go thru the whole process
2834 If contact_rec.New_Contact_Id Is Not Null Then
2835 Check_and_Create_Contact(p_contact_party_id => contact_rec.new_party_id,
2836 p_cust_acct_id => l_customer_info_id,
2837 p_usage_type => l_type_of_contact,
2838 x_contact_id => l_ret_contact_id,
2839 x_return_status => l_return_status
2840 );
2841 If contact_rec.New_Contact_Id = l_ret_contact_id Then
2842 IF l_debug_level > 0 THEN
2843 oe_debug_pub.add( 'GENERATED CONTACT ID IS => ' || L_RET_CONTACT_ID ) ;
2844 END IF;
2845 x_contact_id := contact_rec.New_Contact_id;
2846 IF l_debug_level > 0 THEN
2847 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH CURRENT VAL' ) ;
2848 END IF;
2849 RETURN;
2850 Else
2851 IF l_debug_level > 0 THEN
2852 oe_debug_pub.add( 'THERE IS SOME PROBLEM , PLEASE INVESTIGATE.' ) ;
2853 END IF;
2854 End If;
2855 End If;
2856
2857 -- End of the check }
2858
2859
2860 l_person_rec.person_first_name := contact_rec.person_first_name;
2861 l_person_rec.person_last_name := contact_rec.person_last_name;
2862 l_person_rec.person_pre_name_adjunct := contact_rec.person_title;
2863 l_person_rec.created_by_module := G_CREATED_BY_MODULE;
2864 l_person_rec.application_id := 660;
2865
2866 If G_AUTO_PARTY_NUMBERING = 'N' Then
2867 IF l_debug_level > 0 THEN
2868 oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM PARTY SEQUENCE' ) ;
2869 END IF;
2870 Select hz_party_number_s.nextval
2871 Into l_party_rec.party_number
2872 From Dual;
2873 IF l_debug_level > 0 THEN
2874 oe_debug_pub.add( 'AFTER SELECTING NEW PARTY SEQUENCE ' || L_PARTY_REC.PARTY_NUMBER ) ;
2875 END IF;
2876 End If; -- If G_AUTO_PARTY_NUMBERING
2877
2878 l_person_rec.party_rec := l_party_rec;
2879
2880 -- { Start Before Calling hz api to create person for contact
2881 HZ_PARTY_V2PUB.Create_Person(
2882 p_person_rec => l_person_rec,
2883 x_party_id => l_contact_party_id,
2884 x_party_number => l_contact_party_number,
2885 x_profile_id => l_profile_id,
2886 x_return_status => l_return_status,
2887 x_msg_count => l_msg_count,
2888 x_msg_data => l_msg_data
2889 );
2890
2891 -- End Call hz api to create contact person }
2892 -- Let us check the status of the call to hz api
2893 IF l_debug_level > 0 THEN
2894 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2895 END IF;
2896 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2897 IF l_debug_level > 0 THEN
2898 oe_debug_pub.add( 'HZ CREATE_PERSON API ERROR ' ) ;
2899 END IF;
2900 IF l_debug_level > 0 THEN
2901 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2902 END IF;
2903 IF l_debug_level > 0 THEN
2904 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2905 END IF;
2906 x_return_status := l_return_status;
2907 IF l_debug_level > 0 THEN
2908 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
2909 END IF;
2910 Update_Error_Flag(p_rowid => contact_rec.rowid);
2911 oe_msg_pub.transfer_msg_stack;
2912 fnd_msg_pub.delete_msg;
2913 return;
2914 End If;
2915
2916 IF l_debug_level > 0 THEN
2917 oe_debug_pub.add( 'NEW PARTY ID FOR CONTACT =>' || L_CONTACT_PARTY_ID ) ;
2918 END IF;
2919 IF l_debug_level > 0 THEN
2920 oe_debug_pub.add( 'NEW PARTY NUMBER FOR CONTACT =>' || L_CONTACT_PARTY_NUMBER ) ;
2921 END IF;
2922
2923 If G_AUTO_PARTY_NUMBERING = 'N' Then
2924 IF l_debug_level > 0 THEN
2925 oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM PARTY SEQUENCE FOR ORG' ) ;
2926 END IF;
2927 Select hz_party_number_s.nextval
2928 Into l_org_contact_rec.party_rel_rec.party_rec.party_number
2929 From Dual;
2930 IF l_debug_level > 0 THEN
2931 oe_debug_pub.add( 'AFTER SELECTING NEW PARTY SEQUENCE ' || L_ORG_CONTACT_REC.PARTY_REL_REC.PARTY_REC.PARTY_NUMBER ) ;
2932 END IF;
2933 End If; -- If G_AUTO_PARTY_NUMBERING
2934
2935 l_org_contact_rec.party_rel_rec.subject_id := l_contact_party_id;
2936 l_org_contact_rec.party_rel_rec.object_id := l_customer_party_id;
2937 l_org_contact_rec.party_rel_rec.relationship_type := 'CONTACT';
2938 l_org_contact_rec.party_rel_rec.relationship_code := 'CONTACT_OF';
2939 l_org_contact_rec.party_rel_rec.start_date := sysdate;
2940 l_org_contact_rec.party_rel_rec.subject_table_name := 'HZ_PARTIES';
2941 l_org_contact_rec.party_rel_rec.object_table_name := 'HZ_PARTIES';
2942 l_org_contact_rec.party_rel_rec.created_by_module := G_CREATED_BY_MODULE;
2943 l_org_contact_rec.party_rel_rec.application_id := 660;
2944 l_org_contact_rec.party_rel_rec.subject_type := 'PERSON';
2945 Select party_type
2946 Into l_org_contact_rec.party_rel_rec.object_type
2947 From HZ_PARTIES
2948 Where party_id = l_customer_party_id;
2949
2950 IF l_debug_level > 0 THEN
2951 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 ) ;
2952 END IF;
2953 If G_AUTO_CONTACT_NUMBERING = 'N' Then
2954 IF l_debug_level > 0 THEN
2955 oe_debug_pub.add( 'BEFORE SELECTING NEXTVAL FROM CONTACT SEQ' ) ;
2956 END IF;
2957 Select hz_contact_numbers_s.nextval
2958 Into l_org_contact_rec.contact_number
2959 From Dual;
2960 IF l_debug_level > 0 THEN
2961 oe_debug_pub.add( 'AFTER SELECTING NEW CONTACT SEQUENCE ' || L_ORG_CONTACT_REC.CONTACT_NUMBER ) ;
2962 END IF;
2963 End If; -- If G_AUTO_CONTACT_NUMBERING
2964
2965
2966 l_org_contact_rec.title := contact_rec.person_title;
2967 l_org_contact_rec.created_by_module := G_CREATED_BY_MODULE;
2968 l_org_contact_rec.application_id := 660;
2969
2970 -- { Start Before Calling hz api to create org contact
2971
2972 HZ_PARTY_CONTACT_V2PUB.Create_Org_Contact (
2973 p_org_contact_rec => l_org_contact_rec,
2974 x_party_id => x_rel_party_id,
2975 x_party_number => x_rel_party_number,
2976 x_party_rel_id => x_party_relationship_id,
2977 x_org_contact_id => x_org_contact_id,
2978 x_return_status => l_return_status,
2979 x_msg_count => l_msg_count,
2980 x_msg_data => l_msg_data
2981 );
2982 -- End Call hz api to create org contact }
2983 -- Let us check the status of the call to hz api
2984 IF l_debug_level > 0 THEN
2985 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
2986 END IF;
2987 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
2988 IF l_debug_level > 0 THEN
2989 oe_debug_pub.add( 'HZ CREATE_ORG_CONTACT API ERROR ' ) ;
2990 END IF;
2991 IF l_debug_level > 0 THEN
2992 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
2993 END IF;
2994 IF l_debug_level > 0 THEN
2995 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
2996 END IF;
2997 x_return_status := l_return_status;
2998 IF l_debug_level > 0 THEN
2999 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3000 END IF;
3001 Update_Error_Flag(p_rowid => contact_rec.rowid);
3002 oe_msg_pub.transfer_msg_stack;
3003 fnd_msg_pub.delete_msg;
3004 return;
3005 End If;
3006
3007 IF l_debug_level > 0 THEN
3008 oe_debug_pub.add( 'NEW REL PARTY ID FOR CONTACT =>' || X_REL_PARTY_ID ) ;
3009 END IF;
3010 IF l_debug_level > 0 THEN
3011 oe_debug_pub.add( 'NEW REL PARTY NUMBER CONTCT =>' || X_REL_PARTY_NUMBER ) ;
3012 END IF;
3013 IF l_debug_level > 0 THEN
3014 oe_debug_pub.add( 'NEW ORG CONTCT ID =>' || X_ORG_CONTACT_ID ) ;
3015 END IF;
3016
3017 -- { Let us create the Contact's contact point EMAIL and PHONE
3018 If contact_rec.email_address is Not Null Then
3019 Create_Contact_Point(
3020 p_contact_point_type => 'EMAIL',
3021 p_owner_table_id => x_rel_party_id,
3022 p_email => contact_rec.email_address,
3023 p_phone_area_code => NULL,
3024 p_phone_number => NULL,
3025 p_phone_extension => NULL,
3026 p_phone_country_code => NULL,
3027 x_return_status => l_return_status,
3028 x_msg_count => l_msg_count,
3029 x_msg_data => l_msg_data
3030 );
3031 -- Let us check the status of the call to hz api
3032 IF l_debug_level > 0 THEN
3033 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
3034 END IF;
3035 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
3036 IF l_debug_level > 0 THEN
3037 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM CONTACT EML '|| L_MSG_DATA ) ;
3038 END IF;
3039 x_return_status := l_return_status;
3040 IF l_debug_level > 0 THEN
3041 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH ERROR' ) ;
3042 END IF;
3043 return;
3044 Else
3045 IF l_debug_level > 0 THEN
3046 oe_debug_pub.add( 'NEW EMAIL => ' || CONTACT_REC.EMAIL_ADDRESS ) ;
3047 END IF;
3048 End if;
3049
3050 End If;
3051 -- End for create contact point EMAIL}
3052
3053 -- { Start for create contact point PHONE
3054 If contact_rec.phone_number is Not Null Then
3055 Create_Contact_Point(
3056 p_contact_point_type => 'PHONE',
3057 p_owner_table_id => x_rel_party_id,
3058 p_email => NULL,
3059 p_phone_area_code => contact_rec.phone_area_code,
3060 p_phone_number => contact_rec.phone_number,
3061 p_phone_extension => contact_rec.phone_extension,
3062 p_phone_country_code => contact_rec.phone_country_code,
3063 x_return_status => l_return_status,
3064 x_msg_count => l_msg_count,
3065 x_msg_data => l_msg_data
3066 );
3067 -- Let us check the status of the call to hz api
3068 IF l_debug_level > 0 THEN
3069 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
3070 END IF;
3071 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
3072 IF l_debug_level > 0 THEN
3073 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM CONTACT PH '|| L_MSG_DATA ) ;
3074 END IF;
3075 x_return_status := l_return_status;
3076 IF l_debug_level > 0 THEN
3077 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT WITH ERROR' ) ;
3078 END IF;
3079 return;
3080 Else
3081 IF l_debug_level > 0 THEN
3082 oe_debug_pub.add( 'NEW PHONE => ' || CONTACT_REC.PHONE_NUMBER ) ;
3083 END IF;
3084 End if;
3085
3086 End If;
3087 -- End for create contact point PHONE}
3088
3089 -- { Start create CONTACT role for the new contact
3090
3091 l_cust_acct_roles_rec.party_id := x_rel_party_id;
3092 l_cust_acct_roles_rec.cust_account_id := l_customer_info_id;
3093 l_cust_acct_roles_rec.role_type := 'CONTACT';
3094 -- l_cust_acct_roles_rec.begin_date := sysdate;
3095 l_cust_acct_roles_rec.cust_acct_site_id := NULL;
3096 l_cust_acct_roles_rec.created_by_module := G_CREATED_BY_MODULE;
3097 l_cust_acct_roles_rec.application_id := 660;
3098
3099 HZ_CUST_ACCOUNT_ROLE_V2PUB.Create_Cust_Account_Role(
3100 p_cust_account_role_rec => l_cust_acct_roles_rec,
3101 x_return_status => l_return_status,
3102 x_msg_count => l_msg_count,
3103 x_msg_data => l_msg_data,
3104 x_cust_account_role_id => x_cust_account_role_id
3105 );
3106
3107 IF l_debug_level > 0 THEN
3108 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
3109 END IF;
3110 -- Let us check the status of the call to hz api
3111 IF l_debug_level > 0 THEN
3112 oe_debug_pub.add( 'RETURN STATS ' || L_RETURN_STATUS ) ;
3113 END IF;
3114 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
3115 IF l_debug_level > 0 THEN
3116 oe_debug_pub.add( 'HZ CREATE_CUST_ACCT_ROLES API ERROR ' ) ;
3117 END IF;
3118 IF l_debug_level > 0 THEN
3119 oe_debug_pub.add( 'RETURN ERROR MESSAGE COUNT FROM HZ ' || OE_MSG_PUB.GET ( P_MSG_INDEX => L_MSG_COUNT ) ) ;
3120 END IF;
3121 IF l_debug_level > 0 THEN
3122 oe_debug_pub.add( 'RETURN ERROR MESSAGE FROM HZ ' || L_MSG_DATA ) ;
3123 END IF;
3124 x_return_status := l_return_status;
3125 IF l_debug_level > 0 THEN
3126 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3127 END IF;
3128 Update_Error_Flag(p_rowid => contact_rec.rowid);
3129 oe_msg_pub.transfer_msg_stack;
3130 fnd_msg_pub.delete_msg;
3131 return;
3132 End If;
3133 IF l_debug_level > 0 THEN
3134 oe_debug_pub.add( 'NEW CUST ACC. ROLE => ' || X_CUST_ACCOUNT_ROLE_ID ) ;
3135 END IF;
3136 x_contact_id := x_cust_account_role_id;
3137
3138 If contact_rec.New_Contact_Id Is Null and
3139 l_type_of_contact Is Not Null Then
3140 Check_and_Create_Contact(p_contact_party_id => x_rel_party_id,
3141 p_cust_acct_id => l_customer_info_id,
3142 p_usage_type => l_type_of_contact,
3143 x_contact_id => l_ret_contact_id,
3144 x_return_status => l_return_status
3145 );
3146 IF l_debug_level > 0 THEN
3147 oe_debug_pub.add( 'NEW CONTACT ID IS THERE FOR SITE ' || L_TYPE_OF_CONTACT || ' , RETURING THE EXISTING VAL' ) ;
3148 END IF;
3149 x_contact_id := l_ret_Contact_id;
3150 End If;
3151
3152 -- Let us select the Name to Pass back to calling api
3153 Select party_name
3154 Into x_contact_name
3155 From hz_parties
3156 Where party_id = l_contact_party_id;
3157 -- End of select to get name
3158
3159 -- Now Update the table with the new values
3160
3161 Update oe_customer_info_iface_all
3162 Set New_Contact_Id = x_contact_id,
3163 New_Party_Id = x_rel_party_id,
3164 is_ship_to_address =
3165 decode(l_type_of_contact,'SHIP_TO','Y',is_ship_to_address),
3166 is_bill_to_address =
3167 decode(l_type_of_contact,'BILL_TO','Y',is_bill_to_address),
3168 is_deliver_to_address =
3169 decode(l_type_of_contact,'DELIVER_TO','Y',is_deliver_to_address)
3170 Where rowid = contact_rec.rowid;
3171
3172 -- x_contact_id := l_contact_party_id;
3173 IF l_debug_level > 0 THEN
3174 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT ' ) ;
3175 END IF;
3176 -- End CONTACT role for the new contact}
3177
3178 Exception
3179 When Others Then
3180 IF l_debug_level > 0 THEN
3181 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_CONTACT. ABORT PROCESSING' ) ;
3182 END IF;
3183 IF l_debug_level > 0 THEN
3184 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3185 END IF;
3186 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
3187 fnd_message.set_token('API_NAME', 'Create_Contact');
3188 oe_msg_pub.add;
3189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3190 Update_Error_Flag(p_rowid => contact_rec.rowid);
3191 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
3192 IF l_debug_level > 0 THEN
3193 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3194 END IF;
3195 End;
3196 -- End of Begin after Loop }
3197
3198 End Loop;
3199 -- End Contact Loop }
3200
3201 If l_no_record_exists Then
3202 IF l_debug_level > 0 THEN
3203 oe_debug_pub.add( 'NO RECORD FOUND FOR THE PASSED REF , PLEASE CHECK DATA' ) ;
3204 END IF;
3205 fnd_message.set_name('ONT','ONT_OI_INL_NO_DATA');
3206 fnd_message.set_token('REFERENCE', p_customer_info_ref);
3207 oe_msg_pub.add;
3208 x_return_status := FND_API.G_RET_STS_ERROR;
3209 IF l_debug_level > 0 THEN
3210 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3211 END IF;
3212 End If;
3213
3214 IF l_debug_level > 0 THEN
3215 oe_debug_pub.add( 'EXITING PROCEDURE CREATE CONTACT' ) ;
3216 END IF;
3217 Exception
3218 When Others Then
3219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3220 IF l_debug_level > 0 THEN
3221 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_CONTACT. ABORT PROCESSING' ) ;
3222 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3223 END IF;
3224 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
3225 fnd_message.set_token('API_NAME', 'Create_Contact');
3226 oe_msg_pub.add;
3227 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
3228 IF l_debug_level > 0 THEN
3229 oe_debug_pub.add( 'EXITING IN CREATE_CONTACT PROCEDURE WITH ERROR' ) ;
3230 END IF;
3231 End Create_Contact;
3232 -- End procedure Create Contact }
3233
3234
3235 -- { Start of procedure Create_Customer_Info
3236
3237 PROCEDURE Create_Customer_Info(
3238 p_customer_info_ref IN Varchar2,
3239 p_customer_info_type_code IN Varchar2,
3240 p_usage IN Varchar2,
3241 p_orig_sys_document_ref IN Varchar2,
3242 p_orig_sys_line_ref IN Varchar2,
3243 p_order_source_id IN Number,
3244 p_org_id IN Number,
3245 x_customer_info_id OUT NOCOPY /* file.sql.39 change */ Number,
3246 x_customer_info_number OUT NOCOPY /* file.sql.39 change */ Varchar2,
3247 x_return_status OUT NOCOPY /* file.sql.39 change */ Varchar2
3248 )
3249 Is
3250 l_customer_info_ref Varchar2(50) := p_customer_info_ref;
3251 l_customer_info_id Number;
3252 l_customer_info_number Varchar2(30);
3253 l_customer_party_id Number;
3254 l_contact_id Number;
3255 l_contact_name Varchar2(360);
3256 l_type_of_contact Varchar2(10) := p_usage;
3257 l_type_of_address Varchar2(10) := p_usage;
3258 l_return_status Varchar2(1) := FND_API.G_RET_STS_SUCCESS;
3259 l_usage_site_id Number;
3260 l_ship_to_org_id Number;
3261 l_bill_to_org_id Number;
3262 l_deliver_to_org_id Number;
3263 l_existing_value Varchar2(1) := 'N';
3264 l_orig_sys_line_ref Varchar2(50) := p_orig_sys_line_ref;
3265
3266 -- Following cursor will fetch the data for the passed ref and type --
3267 -- information. For the address creation. --
3268 -- {
3269 Cursor l_address_info_cur Is
3270 Select country,
3271 address1,
3272 address2,
3273 address3,
3274 address4,
3275 city,
3276 postal_code,
3277 state,
3278 province,
3279 county,
3280 is_ship_to_address,
3281 is_bill_to_address,
3282 is_deliver_to_address,
3283 attribute_category,
3284 attribute1,
3285 attribute2,
3286 attribute3,
3287 attribute4,
3288 attribute5,
3289 attribute6,
3290 attribute7,
3291 attribute8,
3292 attribute9,
3293 attribute10,
3294 attribute11,
3295 attribute12,
3296 attribute13,
3297 attribute14,
3298 attribute15,
3299 attribute16,
3300 attribute17,
3301 attribute18,
3302 attribute19,
3303 attribute20,
3304 global_attribute_category,
3305 global_attribute1,
3306 global_attribute2,
3307 global_attribute3,
3308 global_attribute4,
3309 global_attribute5,
3310 global_attribute6,
3311 global_attribute7,
3312 global_attribute8,
3313 global_attribute9,
3314 global_attribute10,
3315 global_attribute11,
3316 global_attribute12,
3317 global_attribute13,
3318 global_attribute14,
3319 global_attribute15,
3320 global_attribute16,
3321 global_attribute17,
3322 global_attribute18,
3323 global_attribute19,
3324 global_attribute20
3325 from oe_customer_info_iface_all
3326 where customer_info_ref = l_customer_info_ref
3327 and customer_info_type_code = 'ADDRESS';
3328
3329 -- End of Cursor definition for l_address_info_cur }
3330
3331 --
3332 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3333 --
3334 Begin
3335
3336 x_return_status := FND_API.G_RET_STS_SUCCESS;
3337 -- Initialize the system paramter and profile option used
3338 -- later in processing
3339 If G_INITIALIZED = FND_API.G_FALSE Then
3340 Initialize_Global(l_return_status);
3341 End If;
3342
3343 -- Check for the type of entry need to be created based on the
3344 -- paramenter passed p_customer_info_type_code 'ACCOUNT', 'CONTACT' or
3345 -- 'ADDRESS'
3346 -- Depending on that call the respective api/processing...
3347
3348 -- Null p_orig_sys_line_ref if add ct info is called for Header
3349 If l_orig_sys_line_ref = FND_API.G_MISS_CHAR Then
3350 l_orig_sys_line_ref := Null;
3351 End If;
3352
3353 -- { Start of if for p_customer_info_type_code
3354 if p_customer_info_type_code = 'ACCOUNT' then
3355 IF l_debug_level > 0 THEN
3356 oe_debug_pub.add( 'BEFORE CALLING CREATE ACCOUNT PROCEDURE' ) ;
3357 END IF;
3358 -- call Create_Account api
3359 Create_Account( p_customer_info_ref => p_customer_info_ref,
3360 p_orig_sys_document_ref => p_orig_sys_document_ref,
3361 p_orig_sys_line_ref => l_orig_sys_line_ref,
3362 p_order_source_id => p_order_source_id,
3363 x_cust_account_id => l_customer_info_id,
3364 x_cust_account_number => l_customer_info_number,
3365 x_cust_party_id => l_customer_party_id,
3366 x_existing_value => l_existing_value,
3367 x_return_status => l_return_status
3368 );
3369
3370 IF l_debug_level > 0 THEN
3371 oe_debug_pub.add( 'AFTER CALLING CREATE ACCOUNT PROCEDURE' ) ;
3372 END IF;
3373
3374 -- Check for the return status of the api
3375 -- to return the proper information to the called program.
3376 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3377 IF l_debug_level > 0 THEN
3378 oe_debug_pub.add( 'RETURN STATUS IS NOT SUCCESS , AFTER CREATE ACC.' ) ;
3379 END IF;
3380 x_return_status := l_return_status;
3381 else
3382 IF l_debug_level > 0 THEN
3383 oe_debug_pub.add( 'RETURN STATUS IS SUCCESS , AFTER CREATE ACCOUNT' ) ;
3384 END IF;
3385 x_customer_info_id := l_customer_info_id;
3386 x_customer_info_number := l_customer_info_number;
3387 x_return_status := l_return_status;
3388 end if;
3389
3390 elsif p_customer_info_type_code = 'CONTACT' then
3391 -- call Create_Contact api
3392 Create_Contact(
3393 p_customer_info_ref => p_customer_info_ref,
3394 p_cust_account_id => l_customer_info_id,
3395 p_cust_account_number => l_customer_info_number,
3396 p_type_of_contact => l_type_of_contact,
3397 p_orig_sys_document_ref => p_orig_sys_document_ref,
3398 p_orig_sys_line_ref => l_orig_sys_line_ref,
3399 p_order_source_id => p_order_source_id,
3400 x_contact_id => l_contact_id,
3401 x_contact_name => l_contact_name,
3402 x_return_status => l_return_status
3403 );
3404
3405 -- Check for the return status of the api
3406 -- to return the proper information to the called program.
3407 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
3408 IF l_debug_level > 0 THEN
3409 oe_debug_pub.add( 'RETURN STATUS IS NOT SUCCESS , AFTER CREATE CONT.' ) ;
3410 END IF;
3411 x_return_status := l_return_status;
3412 else
3413 IF l_debug_level > 0 THEN
3414 oe_debug_pub.add( 'RETURN STATUS IS SUCCESS , AFTER CREATE ACCOUNT' ) ;
3415 END IF;
3416 x_customer_info_id := l_contact_id;
3417 x_return_status := l_return_status;
3418 end if;
3419 elsif p_customer_info_type_code = 'ADDRESS' then
3420 -- call Create_Address api
3421 Create_Address(p_customer_info_ref => p_customer_info_ref,
3422 p_type_of_address => l_type_of_address,
3423 p_orig_sys_document_ref => p_orig_sys_document_ref,
3424 p_orig_sys_line_ref => l_orig_sys_line_ref,
3425 p_order_source_id => p_order_source_id,
3426 p_org_id => p_org_id,
3427 x_usage_site_id => l_usage_site_id,
3428 x_return_status => l_return_status
3429 );
3430 -- Create_Address;
3431 -- Check for the return status of the api
3432 -- to return the proper information to the called program.
3433 If l_return_status <> FND_API.G_RET_STS_SUCCESS Then
3434 IF l_debug_level > 0 THEN
3435 oe_debug_pub.add( 'RETURN STATUS IS NOT SUCCESS , AFTER CREATE ADDRESS.' ) ;
3436 END IF;
3437 x_return_status := l_return_status;
3438 Else
3439 IF l_debug_level > 0 THEN
3440 oe_debug_pub.add( 'RETURN STATUS IS SUCCESS , AFTER CREATE ADDRESS' ) ;
3441 END IF;
3442 -- { Start If
3443 If l_type_of_address = 'SHIP_TO' Then
3444 x_customer_info_id := l_usage_site_id;
3445 Elsif l_type_of_address = 'BILL_TO' Then
3446 x_customer_info_id := l_usage_site_id;
3447 Elsif l_type_of_address = 'DELIVER_TO' Then
3448 x_customer_info_id := l_usage_site_id;
3449 End If;
3450 -- End If}
3451 x_return_status := l_return_status;
3452 End If;
3453 else
3454 NULL;
3455 -- Wrong type of information passed
3456 -- set the error message stack with the error message and
3457 -- return error to calling api.
3458 end if;
3459 -- End of if for p_customer_info_type_code }
3460 Exception
3461 When Others Then
3462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3463 IF l_debug_level > 0 THEN
3464 oe_debug_pub.add( 'PROBLEM IN CALL TO CREATE_CUSTOMER_INFO. ABORT PROCESSING' ) ;
3465 END IF;
3466 IF l_debug_level > 0 THEN
3467 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3468 END IF;
3469 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
3470 fnd_message.set_token('API_NAME', 'Create_Customer_Info');
3471 oe_msg_pub.add;
3472 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
3473 IF l_debug_level > 0 THEN
3474 oe_debug_pub.add( 'EXITING IN CREATE_CUSTOMER_INFO PROCEDURE WITH ERROR' ) ;
3475 END IF;
3476
3477 End Create_Customer_Info;
3478
3479 -- End of procedure Create_Customer_Info }
3480
3481 Procedure Delete_Customer_Info(
3482 p_header_customer_rec In OE_ORDER_IMPORT_SPECIFIC_PVT.Customer_Rec_Type,
3483 p_line_customer_tbl In OE_ORDER_IMPORT_SPECIFIC_PVT.Customer_Tbl_Type)
3484 Is
3485
3486 --
3487 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3488 --
3489 Begin
3490
3491 IF l_debug_level > 0 THEN
3492 oe_debug_pub.add( 'INSIDE DELETE_CUSTOMER_INFO' ) ;
3493 END IF;
3494
3495 If p_header_customer_rec.Orig_Sys_Customer_Ref IS NOT NULL Then
3496 Delete
3497 From oe_customer_info_iface_all a
3498 Where customer_info_ref = p_header_customer_rec.Orig_Sys_Customer_Ref
3499 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3500 From Oe_Headers_Iface_All b
3501 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3502 End If;
3503
3504 If p_header_customer_rec.Sold_To_Contact_Ref IS NOT NULL Then
3505
3506 Delete
3507 From oe_customer_info_iface_all a
3508 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3509 where customer_info_ref = p_header_customer_rec.Sold_To_Contact_Ref
3510 and customer_info_type_code = 'CONTACT')
3511 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3512 From Oe_Headers_Iface_All b
3513 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3514
3515 Delete
3516 From oe_customer_info_iface_all a
3517 Where customer_info_ref = p_header_customer_rec.Sold_To_Contact_Ref
3518 And customer_info_ref Not In (Select Sold_To_Contact_Ref
3519 From Oe_Headers_Iface_All b
3520 Where b.Sold_To_Contact_Ref = a.customer_info_ref);
3521 End If;
3522
3523 If p_header_customer_rec.Orig_Ship_Address_Ref IS NOT NULL Then
3524
3525
3526 Delete
3527 From oe_customer_info_iface_all a
3528 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3529 where customer_info_ref = p_header_customer_rec.Orig_Ship_Address_Ref
3530 and customer_info_type_code = 'ADDRESS' )
3531 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3532 From Oe_Headers_Iface_All b
3533 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3534
3535 Delete
3536 From oe_customer_info_iface_all a
3537 Where customer_info_ref = p_header_customer_rec.Orig_Ship_Address_Ref
3538 And customer_info_ref Not In ((Select Orig_Ship_Address_Ref
3539 From Oe_Headers_Iface_All b
3540 Where b.Orig_Ship_Address_Ref = a.customer_info_ref)
3541 UNION ALL
3542 (Select Orig_Ship_Address_Ref
3543 From Oe_Lines_Iface_All c
3544 Where c.Orig_Ship_Address_Ref = a.customer_info_ref));
3545 End If;
3546
3547 If p_header_customer_rec.Orig_Bill_Address_Ref IS NOT NULL Then
3548
3549
3550 Delete
3551 From oe_customer_info_iface_all a
3552 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3553 where customer_info_ref = p_header_customer_rec.Orig_Bill_Address_Ref
3554 and customer_info_type_code = 'ADDRESS')
3555 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3556 From Oe_Headers_Iface_All b
3557 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3558
3559
3560
3561 Delete
3562 From oe_customer_info_iface_all a
3563 Where customer_info_ref = p_header_customer_rec.Orig_Bill_Address_Ref
3564 And customer_info_ref Not In ((Select Orig_Bill_Address_Ref
3565 From Oe_Headers_Iface_All b
3566 Where b.Orig_Bill_Address_Ref = a.customer_info_ref)
3567 UNION ALL
3568 (Select Orig_Bill_Address_Ref
3569 From Oe_Lines_Iface_All c
3570 Where c.Orig_Bill_Address_Ref = a.customer_info_ref));
3571 End If;
3572
3573 If p_header_customer_rec.Orig_Deliver_Address_Ref IS NOT NULL Then
3574
3575
3576 Delete
3577 From oe_customer_info_iface_all a
3578 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3579 where customer_info_ref = p_header_customer_rec.Orig_Deliver_Address_Ref
3580 and customer_info_type_code = 'ADDRESS')
3581 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3582 From Oe_Headers_Iface_All b
3583 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3584
3585 Delete
3586 From oe_customer_info_iface_all a
3587 Where customer_info_ref = p_header_customer_rec.Orig_Deliver_Address_Ref
3588 And customer_info_ref Not In ((Select Orig_Deliver_Address_Ref
3589 From Oe_Headers_Iface_All b
3590 Where b.Orig_Deliver_Address_Ref = a.customer_info_ref)
3591 UNION ALL
3592 (Select Orig_Deliver_Address_Ref
3593 From Oe_Lines_Iface_All c
3594 Where c.Orig_Deliver_Address_Ref = a.customer_info_ref));
3595 End If;
3596
3597 If p_header_customer_rec.Ship_To_Contact_Ref IS NOT NULL Then
3598
3599 Delete
3600 From oe_customer_info_iface_all a
3601 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3602 where customer_info_ref = p_header_customer_rec.Ship_To_Contact_Ref
3603 and customer_info_type_code = 'CONTACT')
3604 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3605 From Oe_Headers_Iface_All b
3606 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3607
3608
3609
3610 Delete
3611 From oe_customer_info_iface_all a
3612 Where customer_info_ref = p_header_customer_rec.Ship_To_Contact_Ref
3613 And customer_info_ref Not In ((Select Ship_To_Contact_Ref
3614 From Oe_Headers_Iface_All b
3615 Where b.Ship_To_Contact_Ref = a.customer_info_ref)
3616 UNION ALL
3617 (Select Ship_To_Contact_Ref
3618 From Oe_Lines_Iface_All c
3619 Where c.Ship_To_Contact_Ref = a.customer_info_ref));
3620 End If;
3621
3622 If p_header_customer_rec.Bill_To_Contact_Ref IS NOT NULL Then
3623
3624 Delete
3625 From oe_customer_info_iface_all a
3626 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3627 where customer_info_ref = p_header_customer_rec.Bill_To_Contact_Ref
3628 and customer_info_type_code = 'CONTACT')
3629 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3630 From Oe_Headers_Iface_All b
3631 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3632
3633
3634 Delete
3635 From oe_customer_info_iface_all a
3636 Where customer_info_ref = p_header_customer_rec.Bill_To_Contact_Ref
3637 And customer_info_ref Not In ((Select Bill_To_Contact_Ref
3638 From Oe_Headers_Iface_All b
3639 Where b.Bill_To_Contact_Ref = a.customer_info_ref)
3640 UNION ALL
3641 (Select Bill_To_Contact_Ref
3642 From Oe_Lines_Iface_All c
3643 Where c.Bill_To_Contact_Ref = a.customer_info_ref));
3644 End If;
3645
3646 If p_header_customer_rec.Deliver_To_Contact_Ref IS NOT NULL Then
3647
3648
3649 Delete
3650 From oe_customer_info_iface_all a
3651 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3652 where customer_info_ref = p_header_customer_rec.Deliver_To_Contact_Ref
3653 and customer_info_type_code = 'CONTACT')
3654 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3655 From Oe_Headers_Iface_All b
3656 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3657
3658
3659 Delete
3660 From oe_customer_info_iface_all a
3661 Where customer_info_ref = p_header_customer_rec.Deliver_To_Contact_Ref
3662 And customer_info_ref Not In ((Select Deliver_To_Contact_Ref
3663 From Oe_Headers_Iface_All b
3664 Where b.Deliver_To_Contact_Ref = a.customer_info_ref)
3665 UNION ALL
3666 (Select Deliver_To_Contact_Ref
3667 From Oe_Lines_Iface_All c
3668 Where c.Deliver_To_Contact_Ref = a.customer_info_ref));
3669 End If;
3670
3671 For i In 1..p_line_customer_tbl.COUNT Loop
3672 If p_line_customer_tbl(i).Orig_Ship_Address_Ref IS NOT NULL Then
3673
3674 Delete
3675 From oe_customer_info_iface_all a
3676 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3677 where customer_info_ref = p_line_customer_tbl(i).Orig_Ship_Address_Ref
3678 and customer_info_type_code = 'ADDRESS' )
3679 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3680 From Oe_Headers_Iface_All b
3681 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3682
3683
3684 Delete
3685 From oe_customer_info_iface_all a
3686 Where customer_info_ref = p_line_customer_tbl(i).Orig_Ship_Address_Ref
3687 And customer_info_ref Not In ((Select Orig_Ship_Address_Ref
3688 From Oe_Lines_Iface_All b
3689 Where b.Orig_Ship_Address_Ref = a.customer_info_ref)
3690 UNION ALL
3691 (Select Orig_Ship_Address_Ref
3692 From Oe_Headers_Iface_All c
3693 Where c.Orig_Ship_Address_Ref = a.customer_info_ref));
3694 End If;
3695
3696 If p_line_customer_tbl(i).Orig_Bill_Address_Ref IS NOT NULL Then
3697
3698 Delete
3699 From oe_customer_info_iface_all a
3700 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3701 where customer_info_ref = p_line_customer_tbl(i).Orig_Bill_Address_Ref
3702 and customer_info_type_code = 'ADDRESS' )
3703 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3704 From Oe_Headers_Iface_All b
3705 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3706
3707
3708 Delete
3709 From oe_customer_info_iface_all a
3710 Where customer_info_ref = p_line_customer_tbl(i).Orig_Bill_Address_Ref
3711 And customer_info_ref Not In ((Select Orig_Bill_Address_Ref
3712 From Oe_Lines_Iface_All b
3713 Where b.Orig_Bill_Address_Ref = a.customer_info_ref)
3714 UNION ALL
3715 (Select Orig_Bill_Address_Ref
3716 From Oe_Headers_Iface_All c
3717 Where c.Orig_Bill_Address_Ref = a.customer_info_ref));
3718 End If;
3719
3720 If p_line_customer_tbl(i).Orig_Deliver_Address_Ref IS NOT NULL Then
3721
3722 Delete
3723 From oe_customer_info_iface_all a
3724 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3725 where customer_info_ref = p_line_customer_tbl(i).Orig_Deliver_Address_Ref
3726 and customer_info_type_code = 'ADDRESS')
3727 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3728 From Oe_Headers_Iface_All b
3729 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3730
3731
3732 Delete
3733 From oe_customer_info_iface_all a
3734 Where customer_info_ref = p_line_customer_tbl(i).Orig_Deliver_Address_Ref
3735 And customer_info_ref Not In ((Select Orig_Deliver_Address_Ref
3736 From Oe_Lines_Iface_All b
3737 Where b.Orig_Deliver_Address_Ref = a.customer_info_ref)
3738 UNION ALL
3739 (Select Orig_Deliver_Address_Ref
3740 From Oe_Headers_Iface_All c
3741 Where c.Orig_Deliver_Address_Ref = a.customer_info_ref));
3742 End If;
3743
3744 If p_line_customer_tbl(i).Ship_To_Contact_Ref IS NOT NULL Then
3745
3746
3747 Delete
3748 From oe_customer_info_iface_all a
3749 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3750 where customer_info_ref = p_line_customer_tbl(i).Ship_To_Contact_Ref
3751 and customer_info_type_code = 'CONTACT')
3752 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3753 From Oe_Headers_Iface_All b
3754 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3755
3756
3757 Delete
3758 From oe_customer_info_iface_all a
3759 Where customer_info_ref = p_line_customer_tbl(i).Ship_To_Contact_Ref
3760 And customer_info_ref Not In ((Select Ship_To_Contact_Ref
3761 From Oe_Lines_Iface_All b
3762 Where b.Ship_To_Contact_Ref = a.customer_info_ref)
3763 UNION ALL
3764 (Select Ship_To_Contact_Ref
3765 From Oe_Headers_Iface_All c
3766 Where c.Ship_To_Contact_Ref = a.customer_info_ref));
3767 End If;
3768
3769 If p_line_customer_tbl(i).Bill_To_Contact_Ref IS NOT NULL Then
3770
3771
3772 Delete
3773 From oe_customer_info_iface_all a
3774 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3775 where customer_info_ref = p_line_customer_tbl(i).Bill_To_Contact_Ref
3776 and customer_info_type_code = 'CONTACT')
3777 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3778 From Oe_Headers_Iface_All b
3779 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3780
3781
3782 Delete
3783 From oe_customer_info_iface_all a
3784 Where customer_info_ref = p_line_customer_tbl(i).Bill_To_Contact_Ref
3785 And customer_info_ref Not In ((Select Bill_To_Contact_Ref
3786 From Oe_Lines_Iface_All b
3787 Where b.Bill_To_Contact_Ref = a.customer_info_ref)
3788 UNION ALL
3789 (Select Bill_To_Contact_Ref
3790 From Oe_Headers_Iface_All c
3791 Where c.Bill_To_Contact_Ref = a.customer_info_ref));
3792 End If;
3793
3794 If p_line_customer_tbl(i).Deliver_To_Contact_Ref IS NOT NULL Then
3795
3796 Delete
3797 From oe_customer_info_iface_all a
3798 Where customer_info_ref = (Select Parent_Customer_Ref from oe_customer_info_iface_all
3799 where customer_info_ref = p_line_customer_tbl(i).Deliver_To_Contact_Ref
3800 and customer_info_type_code = 'CONTACT')
3801 And customer_info_ref Not In (Select Orig_Sys_Customer_Ref
3802 From Oe_Headers_Iface_All b
3803 Where b.Orig_Sys_Customer_Ref = a.customer_info_ref);
3804
3805
3806
3807 Delete
3808 From oe_customer_info_iface_all a
3809 Where customer_info_ref = p_line_customer_tbl(i).Deliver_To_Contact_Ref
3810 And customer_info_ref Not In ((Select Deliver_To_Contact_Ref
3811 From Oe_Lines_Iface_All b
3812 Where b.Deliver_To_Contact_Ref = a.customer_info_ref)
3813 UNION ALL
3814 (Select Deliver_To_Contact_Ref
3815 From Oe_Headers_Iface_All c
3816 Where c.Deliver_To_Contact_Ref = a.customer_info_ref));
3817 End If;
3818
3819 End Loop;
3820 Exception
3821 When Others Then
3822 IF l_debug_level > 0 THEN
3823 oe_debug_pub.add( 'PROBLEM IN CALL TO DELETE_CUSTOMER_INFO. ABORT PROCESSING' ) ;
3824 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
3825 END IF;
3826 fnd_message.set_name('ONT','ONT_OI_INL_API_FAILED');
3827 fnd_message.set_token('API_NAME', 'Delete_Customer_Info');
3828 oe_msg_pub.add;
3829 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Unexpected error occured: ' || sqlerrm);
3830 IF l_debug_level > 0 THEN
3831 oe_debug_pub.add( 'EXITING IN DELETE_CUSTOMER_INFO PROCEDURE WITH ERROR' ) ;
3832 END IF;
3833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3834 End Delete_Customer_Info;
3835
3836 END OE_INLINE_CUSTOMER_PUB;
[Home] [Help]