1 PACKAGE BODY OE_Create_account_info AS
2 /* $Header: OEXCACTB.pls 120.9 2006/07/21 14:11:32 mbhoumik noship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CREATE_ACCOUNT_INFO';
6 G_CREATED_BY_MODULE VARCHAR2(150);
7 G_account_created_or_found VARCHAR2(50) := 'FOUND'; /*cc project */
8 G_fetch_primary_party_sites boolean :=FALSE; /*cc project */
9
10 -- Start of Comments
11 -- API name Crate_Account_Layer
12 -- Type Private
13 -- Function Automatic creation of Account Layer from Party Layer
14 --
15 -- Pre-reqs
16 --
17 -- Parameters
18 --
19 -- Notes
20 --
21 -- End of Comments
22
23
24 PROCEDURE Create_Account_Layer(
25 p_control_rec IN Control_Rec_Type := G_MISS_CONTROL_REC
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_count OUT NOCOPY NUMBER
28 ,x_msg_data OUT NOCOPY VARCHAR2
29 ,p_party_customer_rec IN OUT NOCOPY /* file.sql.39 change */ Party_customer_rec
30 ,p_site_tbl IN OUT NOCOPY /* file.sql.39 change */ site_tbl_type
31 ,p_account_tbl OUT NOCOPY account_tbl
32 ,p_contact_tbl out NOCOPY contact_tbl
33 ) IS
34
35 p_allow_account_creation boolean :=FALSE;
36 p_allow_contact_creation boolean := FALSE;
37 p_allow_site_creation boolean := FALSE;
38 l_multiple_account boolean :=FALSE;
39 l_add_customer varchar2(30);
40 l_cust_account_role_id number;
41 l_org_contact_id number := null;
42 l_cust_account_id number := null;
43 l_party_id number := null;
44
45 --list of all related customers
46 l_related_customer_tab account_tbl;
47 p2_contact_tbl contact_tbl;
48 l2_cust_account_id number := null;
49 l3_cust_account_id number := null;
50 l2_party_id number := null;
51 l2_org_contact_id number := null;
52 l_account_tbl account_tbl;
53 l2_party_number varchar2(20):=NULL;
54 matched_cust number :=NULL;
55 i number;
56
57 found_relationship BOOLEAN;
58
59 l_status varchar2(10);
60
61
62 CURSOR c_check_account(in_cust_account_id in number) IS
63 SELECT status,party_id
64 FROM hz_cust_accounts
65 WHERE cust_account_id = in_cust_account_id;
66
67 CURSOR c_get_cust_account_id(in_cust_account_number in varchar2) IS
68 SELECT cust_account_id,party_id,status
69 FROM hz_cust_accounts
70 WHERE account_number=in_cust_account_number
71 AND status='A';
72
73 CURSOR c_cust_account_id(in_cust_account_id in number) IS
74 SELECT party_id
75 FROM hz_cust_accounts
76 WHERE cust_account_id=in_cust_account_id
77 AND status='A';
78
79 CURSOR c_related_cust_account_id (in_cust_account_id in number) IS
80 SELECT cust_account_id
81 FROM hz_cust_acct_relate
82 WHERE related_cust_account_id=in_cust_account_id
83 AND status='A';
84
85 l_contact_status varchar2(10) := 'XXX';
86 x_msg_data_contact varchar2(4000);
87 x_msg_count_contact number := null;
88 l_site_failed boolean := FALSE;
89 l_party_site_use_id number := null;
90 lcustomer_relations varchar2(1) := 'N';
91 l_rc_matched boolean := FALSE;
92 l_site_use_code varchar2(10); --added for bug 4240715
93 --
94 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
95 --
96 BEGIN
97 SAVEPOINT CREATE_ACCOUNT_LAYER;
98
99 x_return_status := FND_API.G_RET_STS_SUCCESS;
100
101 -- Checking to see if the OM message stack needs to be initialized
102 IF p_control_rec.p_init_msg_list THEN
103 oe_msg_pub.initialize;
104 END IF;
105
106 IF l_debug_level > 0 THEN
107 oe_debug_pub.add( ' ==== Entering Create Account Layer ====');
108 oe_debug_pub.add(' rec acct_id = '||p_party_customer_rec.p_cust_account_id||
109 ' rec acct_nbr = '||p_party_customer_rec.p_cust_account_number);
110 oe_debug_pub.add(' rec party_id ='||p_party_customer_rec.p_party_id|| ' rec party_nbr = '||
111 p_party_customer_rec.p_party_number);
112 oe_debug_pub.add( ' rec org_contact_id = '||p_party_customer_rec.p_org_contact_id||
113 ' rec role_id = '||p_party_customer_rec.p_cust_account_role_id ) ;
114 END IF;
115
116 IF l_debug_level > 0 THEN
117 oe_debug_pub.add( 'allow account creation = '||P_CONTROL_REC.P_ALLOW_ACCOUNT_CREATION ) ;
118 END IF;
119
120 IF p_control_rec.p_allow_account_creation IS NOT NULL THEN
121
122 IF p_control_rec.p_allow_account_creation = 'ALL' then
123 p_allow_account_creation := TRUE;
124 p_allow_contact_creation := TRUE;
125 p_allow_site_creation := TRUE;
126
127 ELSIF p_control_rec.p_allow_account_creation = 'SITE_AND_CONTACT' then
128 p_allow_account_creation := FALSE;
129 p_allow_contact_creation := TRUE;
130 p_allow_site_creation := TRUE;
131
132 ELSIF p_control_rec.p_allow_account_creation = 'NONE' then
133 p_allow_account_creation := FALSE;
134 p_allow_contact_creation := FALSE;
135 p_allow_site_creation := FALSE;
136
137 -- any other values will be considered 'CHECK'
138 ELSE
139 fnd_profile.get('ONT_AUTOMATIC_ACCOUNT_CREATION',l_add_customer);
140 IF l_debug_level > 0 THEN
141 oe_debug_pub.add( 'atuomatic account creation profile = '||L_ADD_CUSTOMER ) ;
142 END IF;
143 IF l_add_customer = 'Y' then
144 p_allow_account_creation := TRUE;
145 p_allow_contact_creation := TRUE;
146 p_allow_site_creation := TRUE;
147
148 ELSIF l_add_customer='P' then
149 p_allow_account_creation := FALSE;
150 p_allow_contact_creation := TRUE;
151 p_allow_site_creation := TRUE;
152 ELSE
153 p_allow_account_creation := FALSE;
154 p_allow_contact_creation := FALSE;
155 p_allow_site_creation := FALSE;
156 END IF;
157
158 END IF; -- checking the creation access profile
159
160 ELSE -- if it is null
161 fnd_profile.get('ONT_AUTOMATIC_ACCOUNT_CREATION',l_add_customer);
162 IF l_debug_level > 0 THEN
163 oe_debug_pub.add( 'atuomatic account creation profile = '||L_ADD_CUSTOMER ) ;
164 END IF;
165 IF l_add_customer = 'Y' then
166 p_allow_account_creation := TRUE;
167 p_allow_contact_creation := TRUE;
168 p_allow_site_creation := TRUE;
169
170 ELSIF l_add_customer='P' then
171 p_allow_account_creation := FALSE;
172 p_allow_contact_creation := TRUE;
173 p_allow_site_creation := TRUE;
174 ELSE
175 p_allow_account_creation := FALSE;
176 p_allow_contact_creation := FALSE;
177 p_allow_site_creation := FALSE;
178 END IF;
179
180 END IF; -- if permission is not null
181
182 /*cc project. Value to id is not required for Telesales and Teleservice Integrations. The
183 newly introduced flag p_control_rec.p_do_value_to_id it having a default value of TRUE. By default
184 Value to id will be done for all the calls. But when calling from Teleservice and Telesales Integrations
185 we will setting it as false so that value to id is ignored for these integrations.
186 */
187
188 IF (p_control_rec.p_do_value_to_id ) THEN
189 IF l_debug_level > 0 THEN
190 oe_debug_pub.add('valud to id is required');
191 END IF;
192 Value_to_id(
193 p_party_customer_rec => p_party_customer_rec
194 ,p_site_tbl => p_site_tbl
195 ,p_permission => l_add_customer
196 ,x_return_status => x_return_status
197 ,x_msg_count => x_msg_count
198 ,x_msg_data => x_msg_data);
199
200 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
201 OE_MSG_PUB.Count_And_Get
202 ( p_count => x_msg_count
203 , p_data => x_msg_data
204 );
205 IF l_debug_level > 0 THEN
206 oe_debug_pub.add( 'returning with error after value_to_id' ) ;
207 END IF;
208 return;
209 END IF;
210 ELSE
211 IF l_debug_level > 0 THEN
212 oe_debug_pub.add('valud to id is not required');
213 END IF;
214 END IF;
215 /*cc project*/
216
217 IF l_debug_level > 0 THEN
218 oe_debug_pub.add( '===> entering create account layer');
219 oe_debug_pub.add(' rec acct_id = '||p_party_customer_rec.p_cust_account_id||
220 ' rec acct_nbr = '||p_party_customer_rec.p_cust_account_number);
221 oe_debug_pub.add(' rec party_id = '||p_party_customer_rec.p_party_id||
222 ' rec party_nbr = '|| p_party_customer_rec.p_party_number);
223 oe_debug_pub.add( ' rec org_contact_id = '||p_party_customer_rec.p_org_contact_id||
224 ' rec role_id = '||p_party_customer_rec.p_cust_account_role_id ) ;
225 END IF;
226
227 -- Checking for minimum required information
228 IF p_party_customer_rec.p_party_id IS NULL AND
229 p_party_customer_rec.p_cust_account_id IS NULL AND
230 p_party_customer_rec.p_cust_account_number IS NULL AND
231 p_party_customer_rec.p_party_number IS NULL then
232
233 --p_party_customer_rec.p_org_contact_id IS NULL AND
234 --p_party_customer_rec.p_cust_account_role_id IS NULL THEN
235
236 x_return_status := FND_API.G_RET_STS_ERROR;
237 IF l_debug_level > 0 THEN
238 oe_debug_pub.add( 'Returning with error as '|| ' required information is not sent' ) ;
239 END IF;
240 return;
241 END IF;
242
243 IF p_control_rec.p_created_by_module IS NULL THEN
244 x_return_status := FND_API.G_RET_STS_ERROR;
245 IF l_debug_level > 0 THEN
246 oe_debug_pub.add( 'returning with error as '|| ' created by module is not sent' ) ;
247 END IF;
248 return;
249
250 ELSE
251 G_CREATED_BY_MODULE := p_control_rec.p_created_by_module;
252 END IF;
253
254
255
256 IF p_Control_rec.p_process_customer THEN
257 IF l_debug_level > 0 THEN
258 oe_debug_pub.add( ' customer processing required' ) ;
259 end IF;
260 ELSE
261 IF l_debug_level > 0 THEN
262 oe_debug_pub.add( ' customer processing not required' ) ;
263 end if;
264 end if;
265
266 if p_control_rec.p_process_contact then
267 if l_debug_level > 0 then
268 oe_debug_pub.add( ' contact processing required' ) ;
269 end if;
270 else
271 if l_debug_level > 0 then
272 oe_debug_pub.add( ' contact processing not required' ) ;
273 END IF;
274 END IF;
275
276 l_cust_account_id := p_party_customer_rec.p_cust_account_id;
277 l_party_id := p_party_customer_rec.p_party_id;
278
279 -- we do not call check account if any account information is passed
280 -- if both account and party information is passed then we ignore the
281 -- party information
282
283 oe_debug_pub.add('p_party_customer_rec.p_cust_account_id = '||p_party_customer_rec.p_cust_account_id );
284 oe_debug_pub.add('p_party_customer_rec.p_cust_account_number = '||p_party_customer_rec.p_cust_account_number );
285 oe_debug_pub.add('p_party_customer_rec.p_party_id = '||p_party_customer_rec.p_party_id );
286 oe_debug_pub.add('p_party_customer_rec.p_party_number = '||p_party_customer_rec.p_party_number );
287
288
289 IF ((p_party_customer_rec.p_cust_account_id is null) AND
290 (p_party_customer_rec.p_party_id is not null or
291 p_party_customer_rec.p_party_number is not null) AND
292 p_control_rec.p_process_customer) then
293
294 IF l_debug_level > 0 THEN
295 oe_debug_pub.add( '=== calling check_and_create_account...' ) ;
296 END IF;
297
298 /*cc project, Initializing the G_account_created_or_found before calling
299 check_and_create_account.Everytime Check_and_create_account is called it will be
300 resetting the value to CREATED in case account is created*/
301
302 G_account_created_or_found :='FOUND';
303
304 Check_and_Create_Account(
305 p_party_id=>p_party_customer_rec.p_party_id
306 ,p_party_number=>p_party_customer_rec.p_party_number
307 ,p_allow_account_creation=>p_allow_account_creation
308 ,p_multiple_account_is_error=>p_control_rec.p_multiple_account_is_error
309 ,p_account_tbl=>p_account_tbl
310 ,p_out_org_contact_id=>l_org_contact_id
311 ,p_out_cust_account_role_id=>l_cust_account_role_id
312 ,x_return_status=>x_return_status
313 ,x_msg_count=>x_msg_count
314 ,x_msg_data=>x_msg_data
315 ,p_site_tbl_count=>p_site_tbl.COUNT
316 ,p_return_if_only_party=>p_control_rec.p_return_if_only_party
317 );
318 /*cc project assigning the value of G_account_created_or_found to p_party_customer_rec */
319 p_party_customer_rec.p_account_created_or_found :=G_account_created_or_found;
320
321 IF l_debug_level > 0 THEN
322 oe_debug_pub.add( 'Account Found/Created:'||p_party_customer_rec.p_account_created_or_found) ;
323 END IF;
324 /*cc project*/
325
326 IF l_debug_level > 0 THEN
327 oe_debug_pub.add( '=== ...done calling check_and_create_account' ) ;
328 END IF;
329
330 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
331 OE_MSG_PUB.Count_And_Get
332 ( p_count => x_msg_count
333 , p_data => x_msg_data
334 );
335 IF l_debug_level > 0 THEN
336 oe_debug_pub.add( 'returning with error' ) ;
337 END IF;
338 return;
339
340 END IF;
341
342
343 IF p_account_tbl.COUNT > 0 then
344 FOR i in p_account_tbl.FIRST..p_account_tbl.LAST
345 LOOP
346 IF l_debug_level > 0 THEN
347 oe_debug_pub.add( ' ACCT_ID='||P_ACCOUNT_TBL ( I ) ) ;
348 END IF;
349 END LOOP;
350 END IF;
351
352 --If multiple Accounts then return the account table
353 IF p_account_tbl.COUNT > 1 then
354 IF l_debug_level > 0 THEN
355 oe_debug_pub.add( 'returning with multiple accounts' ) ;
356 end IF;
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358 l_multiple_account := TRUE;
359 return;
360 ELSIF p_account_tbl.COUNT = 0 then
361 IF l_debug_level > 0 THEN
362 oe_debug_pub.add( 'no accounts found returning' ) ;
363 end IF;
364 /*cc project, We need to return the status success, if account creation is not allowed so that
365 Add customer will be shown */
366 IF ((p_control_rec.p_return_if_only_party AND p_site_tbl.COUNT = 0) OR ( G_CREATED_BY_MODULE = 'ONT_TELESERVICE_INTEGRATION' AND NOT p_allow_account_creation))THEN
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368 ELSE
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 END IF;
371
372 return;
373 ELSIF p_account_tbl.COUNT = 1 then
374 x_return_status := FND_API.G_RET_STS_SUCCESS;
375 l_cust_account_id := p_account_tbl(1);
376 END IF;
377 ELSE
378 IF l_debug_level > 0 THEN
379 oe_debug_pub.add( ' account does not need to be checked' ) ;
380 END IF;
381
382 END IF; -- if account needs to be checked
383
387
384 -- Checking for Account Information
385 IF p_party_customer_rec.p_cust_account_id is not null AND
386 p_control_rec.p_process_customer THEN
388 OPEN c_check_account(p_party_customer_rec.p_cust_account_id);
389 FETCH c_check_account
390 INTO l_status,
391 l_party_id;
392
393 IF c_check_account%FOUND THEN
394 IF l_status <>'A' then
395 x_return_status := FND_API.G_RET_STS_ERROR;
396 IF l_debug_level > 0 THEN
397 oe_debug_pub.add( 'account is not active' ) ;
398 END IF;
399 FND_MESSAGE.Set_Name('ONT','ONT_AAC_INACTIVE_ACCOUNT');
400 OE_MSG_PUB.ADD;
401 CLOSE c_check_account;
402 OE_MSG_PUB.Count_And_Get
403 ( p_count => x_msg_count
404 , p_data => x_msg_data
405 );
406 return;
407 END IF;
408
409 oe_debug_pub.add( 'overwriting p_account_tbl: ' || p_account_tbl.COUNT ) ;
410
411 p_account_tbl(1) := p_party_customer_rec.p_cust_account_id;
412 p_party_customer_rec.p_party_id := l_party_id;
413
414 ELSIF c_check_account%NOTFOUND THEN
415 x_return_status := FND_API.G_RET_STS_ERROR;
416 IF l_debug_level > 0 THEN
417 oe_debug_pub.add( 'invalid account.no such account_id ' ) ;
418 END IF;
419 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
420 OE_MSG_PUB.ADD;
421 CLOSE c_check_account;
422 OE_MSG_PUB.Count_And_Get
423 ( p_count => x_msg_count
424 , p_data => x_msg_data
425 );
426 return;
427
428 END IF;
429
430
431 -- if Account Number was passed then we ge the cust account id
432 ELSIF p_party_customer_rec.p_cust_account_number is not null AND
433 p_control_rec.p_process_customer THEN
434
435 OPEN c_get_cust_account_id(p_party_customer_rec.p_cust_account_Number);
436 FETCH c_get_cust_account_id
437 INTO l_cust_account_id,
438 l_party_id,
439 l_status;
440
441 IF c_get_cust_account_id%NOTFOUND THEN
442 IF l_debug_level > 0 THEN
443 oe_debug_pub.add( 'invalid customer account number:'||p_party_customer_rec.p_cust_account_Number ) ;
444 END IF;
445 x_return_status := FND_API.G_RET_STS_ERROR;
446 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
447 OE_MSG_PUB.ADD;
448 CLOSE c_get_cust_account_id;
449 OE_MSG_PUB.Count_And_Get
450 ( p_count => x_msg_count
451 , p_data => x_msg_data
452 );
453 return;
454 ELSIF c_get_cust_account_id%FOUND THEN
455 IF l_status <> 'A' then
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 IF l_debug_level > 0 THEN
458 oe_debug_pub.add( 'account for account number is not active' ) ;
459 END IF;
460 FND_MESSAGE.Set_Name('ONT','ONT_AAC_INACTIVE_ACCOUNT');
461 OE_MSG_PUB.ADD;
462 CLOSE c_get_cust_account_id;
463 OE_MSG_PUB.Count_And_Get
464 ( p_count => x_msg_count
465 , p_data => x_msg_data
466 );
467 return;
468 END IF;
469
470 p_party_customer_rec.p_party_id := l_party_id;
471 END IF;
472
473 END IF; -- if account information is not null
474
475
476 -- getting party_id for the account_record
477 IF l_party_id is null AND
478 p_control_rec.p_process_customer then
479 OPEN c_cust_account_id(l_cust_account_id);
480 FETCH c_cust_account_id
481 INTO l_party_id;
482 IF c_cust_account_id%NOTFOUND THEN
483 IF l_debug_level > 0 THEN
484 oe_debug_pub.add( 'INVALID CUSTOMER ACCOUNT ID' ) ;
485 END IF;
486 CLOSE c_cust_account_id;
487 OE_MSG_PUB.Count_And_Get
488 ( p_count => x_msg_count
489 , p_data => x_msg_data
490 );
491 return;
492 END IF;
493 END IF;
494
495
496 IF l_debug_level > 0 THEN
497 oe_debug_pub.add(' status after acct = '||x_return_status||' l_cust_account_id = '|| l_cust_account_id);
498 oe_debug_pub.add(' rec cust_id = '||p_party_customer_rec.p_cust_account_id|| ' l_party_id = '||l_party_id||
499 ' acct tbl count = '||p_account_tbl.count ) ;
500 END IF;
501
502
503 /* cc project. At this point sold_to_customer is either created or found.
504 For the contact center integration we need to return back the control to
505 give higher precedence for defaulting of related customer and account sites
506 in case sold_to_customer is found.
507 */
508 IF (p_control_rec.p_return_if_customer_found = TRUE AND p_party_customer_rec.p_account_created_or_found ='FOUND') THEN
509 IF l_debug_level > 0 THEN
510 oe_debug_pub.add('account was found.p_return_if_customer_found is true, so returning ');
511 END IF;
512 return;
513 ELSE
514 IF l_debug_level > 0 THEN
515 oe_debug_pub.add('either accunt was created or p_return_if_customer_found is false, So continue processing');
519 END IF;
516 oe_debug_pub.add('p_account_created_or_found:'||p_party_customer_rec.p_account_created_or_found);
517 END IF;
518
520 /* cc project */
521
522
523 /* at this point: created the sold_to customer
524 now start creating the related customers
525 */
526 lcustomer_relations := OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
527 fnd_profile.get('ONT_AUTOMATIC_ACCOUNT_CREATION',l_add_customer);
528
529 oe_debug_pub.add('related customer profile: '||lcustomer_relations);
530 oe_debug_pub.add('add customer profile: '||l_add_customer);
531
532 IF l_debug_level > 0 THEN
533 oe_debug_pub.add( 'Starting related customer(RC) support' ) ;
534 END IF;
535
536 /* p_site_tbl exists {*/
537 IF p_site_tbl.COUNT > 0 THEN
538
539 /* loop through p_site_tbl {*/
540
541 --{added for bug 4240715
542 /*added to handle conditon where no sold to information passed and other customer(ship/bill/delvier)being passed */
543
544 IF p_party_customer_rec.p_party_id IS NULL AND -- end customer enhancement
545 p_party_customer_rec.p_cust_account_id IS NULL AND
546 p_party_customer_rec.p_cust_account_number IS NULL AND
547 p_party_customer_rec.p_party_number IS NULL and
548 p_site_tbl(i).p_site_use_code <>'END_CUST' then
549
550 x_return_status := FND_API.G_RET_STS_ERROR;
551 IF l_debug_level > 0 THEN
552 oe_debug_pub.add( 'Returning with error as '|| ' required information is not sent' ) ;
553 END IF;
554
555 return;
556 End if;
557 --bug 4240715}
558
559 FOR i IN p_site_tbl.FIRST..p_site_tbl.LAST LOOP
560 IF l_debug_level > 0 THEN
561 oe_debug_pub.add ('AAC:VTI: ============ SITE CUSTOMER '||i||' of '||p_site_tbl.LAST||' ================ ');
562 oe_debug_pub.add( 'AAC:RC: processing site level customer#'||i);
563 END IF;
564
565 l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
566 l2_party_id := p_site_tbl(i).p_party_id;
567
568
569 if (p_site_tbl(i).p_process_site = false) then
570
571 IF l_debug_level > 0 THEN
572 oe_debug_pub.add( 'AAC:RC: customer#'||i||' does not require account creation, skipping');
573 END IF;
574 goto skip_loop;
575 end if;
576
577 IF (p_site_tbl(i).p_cust_account_id is null) THEN
578 /* account creation needed {*/
579
580 IF l_debug_level > 0 THEN
581 oe_debug_pub.add('AAC:RC: account creation needed: party_id:'|| l2_party_id);
582 oe_debug_pub.add( '=== calling check_and_create_account...' ) ;
583 END IF;
584 -- create account if needed (profile willing ofcourse)
585 Check_and_Create_Account(
586 p_party_id=>l2_party_id
587 ,p_party_number=>l2_party_number
588 ,p_allow_account_creation=>p_allow_account_creation
589 ,p_multiple_account_is_error=>p_control_rec.p_multiple_account_is_error
590 ,p_account_tbl=>l_account_tbl
591 ,p_out_org_contact_id=>l_org_contact_id
592 ,p_out_cust_account_role_id=>l_cust_account_role_id
593 ,x_return_status=>x_return_status
594 ,x_msg_count=>x_msg_count
595 ,x_msg_data=>x_msg_data
596 ,p_site_tbl_count=>p_site_tbl.COUNT
597 ,p_return_if_only_party=>p_control_rec.p_return_if_only_party
598 );
599
600 IF l_debug_level > 0 THEN
601 oe_debug_pub.add( '=== ...done calling check_and_create_account' ) ;
602 END IF;
603
604 -- error checking
605 --If multiple Accounts then return the account table
606 IF l_account_tbl.COUNT > 1 then
607 IF l_debug_level > 0 THEN
608 oe_debug_pub.add( 'AAC:RC: returning with multiple accounts' ) ;
609 END IF;
610 x_return_status := FND_API.G_RET_STS_SUCCESS;
611 --l_multiple_account := TRUE;
612 P_account_tbl:=l_account_tbl;
613 return;
614 ELSIF l_account_tbl.COUNT = 0 then
615 IF l_debug_level > 0 THEN
616 oe_debug_pub.add( 'AAC:RC: no accounts found returning' ) ;
617 END IF;
618 IF p_control_rec.p_return_if_only_party AND p_site_tbl.COUNT = 0 THEN
619 x_return_status := FND_API.G_RET_STS_SUCCESS;
620 ELSE
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 END IF;
623 x_return_status := FND_API.G_RET_STS_SUCCESS;
624 return;
625 ELSIF l_account_tbl.COUNT = 1 then
626 x_return_status := FND_API.G_RET_STS_SUCCESS;
627 l2_cust_account_id := l_account_tbl(1);
628
629 -- populate cust_account_id back to site_tbl
630 IF l_debug_level > 0 THEN
631 oe_debug_pub.add('AAC:RC: account created: cust_account_id:'|| l2_cust_account_id);
632 END IF;
633 p_site_tbl(i).p_cust_account_id:=l2_cust_account_id;
634 END IF; -- account_tbl count
635
636 END IF; /* account creation ends} */
637
638 IF ((p_site_tbl(i).p_cust_account_id is not null)
639 and (p_site_tbl(i).p_site_use_code <> 'END_CUST')) THEN -- end customer changes(bug 4240715)
640
641 IF l_debug_level > 0 THEN
645 oe_debug_pub.add( 'party_customer_rec account_id = '||p_party_customer_rec.p_cust_account_id);
642 oe_debug_pub.add( 'AAC:RC: account exists, checking for relationship' ) ;
643 oe_debug_pub.add( 'l_cust_account_id = '||l_cust_account_id);
644 oe_debug_pub.add( 'site_tbl account_id = '||p_site_tbl(i).p_cust_account_id);
646 END IF;
647
648 found_relationship := FALSE;
649 IF l_cust_account_id=p_site_tbl(i).p_cust_account_id
650 --or p_site_tbl(i).p_cust_account_id is null
651 THEN
652 oe_debug_pub.add('AAC:RC: Customers are same');
653 l_rc_matched := TRUE;
654 else
655 oe_debug_pub.add('AAC:RC: Customers are different');
656 l_rc_matched := FALSE;
657 end if;
658
659 IF (lcustomer_relations = 'N')
660 then
661 oe_debug_pub.add('AAC:RC: Relationship is N');
662
663 if (l_rc_matched = FALSE ) then
664 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
665 FND_MESSAGE.Set_Token('TEXT',' Customer Relationship is not allowed. SoldTo and Site Level Customer should be same');
666 OE_MSG_PUB.ADD;
667 oe_debug_pub.add('AAC:RC: ERROR: Customers are different');
668 x_return_status := FND_API.G_RET_STS_ERROR;
669 return;
670 END IF;
671
672 elsif (lcustomer_relations = 'Y')
673 then
674 oe_debug_pub.add('AAC:RC: Relationship is Y');
675
676 IF (l_rc_matched = FALSE)
677 then
678 oe_debug_pub.add('AAC:RC: Customers are different. Create relationship');
679 begin
680 select cust_account_id
681 into matched_cust
682 from hz_cust_acct_relate
683 where related_cust_account_id=l_cust_account_id
684 and cust_account_id=p_site_tbl(i).p_cust_account_id;
685
686 found_relationship := TRUE;
687 IF l_debug_level > 0 THEN
688 oe_debug_pub.add( 'AAC:RC: relationship found' ) ;
689 END IF;
690
691 EXCEPTION
692 WHEN NO_DATA_FOUND THEN
693 IF l_debug_level > 0 THEN
694 oe_debug_pub.add( 'AAC:RC: no relationship' ) ;
695 END IF;
696 found_relationship := FALSE;
697
698 END; -- begin
699
700 if(found_relationship = FALSE) then
701
702 if (l_add_customer = 'Y')
703 then
704 oe_debug_pub.add('AAC:RC: l_add_customer is Y');
705 oe_debug_pub.add('AAC:RC: creating relationship');
706 oe_oe_inline_address.create_cust_relationship(
707 p_cust_acct_id => p_site_tbl(i).p_cust_account_id
708 ,p_related_cust_acct_id => l_cust_account_id
709 ,p_reciprocal_flag => 'Y'
710 ,x_return_status => x_return_status
711 ,x_msg_count => x_msg_count
712 ,x_msg_data => x_msg_data);
713
714 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
715 oe_debug_pub.add('Creating relationship failed');
716
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
719 FND_MESSAGE.Set_Token('TEXT',' Customer Relationship creation failed. ', FALSE);
720 OE_MSG_PUB.ADD;
721 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count , p_data => x_msg_data );
722 return;
723 END IF; -- if failure
724 oe_debug_pub.add('AAC:RC: relationship created');
725 else -- l_add_customer = 'N'
726 x_return_status := FND_API.G_RET_STS_ERROR;
727 IF l_debug_level > 0 THEN
728 oe_debug_pub.add('AAC:RC: ERROR: l_add_customer is N' );
729 END IF;
730 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
731 FND_MESSAGE.Set_Token('TEXT',' Customer Relationship not defined. No permission to create relationship. ', FALSE);
732 OE_MSG_PUB.ADD;
733 OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count , p_data => x_msg_data );
734 return;
735 end if; -- l_add_customer end
736 end if; -- found_relationship
737 else -- l_rc_matched = TRUE
738 oe_debug_pub.add('AAC:RC: Customers are same. continue');
739 end if; --l_rc_matched
740
741
742 elsif (lcustomer_relations = 'A')
743 then
744 oe_debug_pub.add('AAC:RC: Relationship is A. Continue');
745 end if;
746
747 END IF; -- if p_site_tbl account_id is not null
748
749 <<skip_loop>>
750 null;
751
752 END LOOP; /* looping through p_site_tbl} */
753
754 END IF;/* we have p_site_tbl} */
755
756 IF l_debug_level > 0 THEN
757 oe_debug_pub.add( 'AAC: ...done related customer' ) ;
758 END IF;
759 /* done related customer here */
760
761 -- if the check_customer did not fail or did not return multiple accts
762 -- then call check contact
763
764 IF x_return_status <> FND_API.G_RET_STS_ERROR AND
765 ( p_account_tbl.COUNT = 1 OR
766 p_party_customer_rec.p_cust_account_id IS NOT NULL) AND
767 p_Control_rec.p_process_contact
768 THEN
769 IF l_debug_level > 0 THEN
770 oe_debug_pub.add( 'checking and creating contact' ) ;
771 END IF;
772 -- if contact was found in check account because of party relationship
776 IF l_debug_level > 0 THEN
773 -- then use that contact
774 IF l_cust_account_role_id is null AND
775 l_org_contact_id is null then
777 oe_debug_pub.add( 'taking contact informaton from record' ) ;
778 END IF;
779 l_cust_account_role_id :=p_party_customer_rec.p_cust_account_role_id;
780 l_org_contact_id :=p_party_customer_rec.p_org_contact_id;
781 END IF;
782
783 IF l_cust_account_role_id is not null OR
784 l_org_contact_id is not null then
785
786 -- check_and_Create_Contact should also handle validation against related
787 -- customers
788 l3_cust_account_id := l_cust_account_id;
789
790 -- handle the scenario if no acct or party information is passed
791 Check_and_Create_Contact(
792 p_party_id=>l_party_id
793 ,p_cust_account_id=>l3_cust_account_id
794 ,p_org_contact_id=>l_org_Contact_id
795 ,P_site_use_code=>null
796 ,p_allow_contact_creation=>p_allow_contact_creation
797 ,p_create_responsibility=>FALSE
798 ,p_cust_account_role_id=>l_cust_account_role_id
799 ,p_cust_account_site_id=>null
800 ,p_assign_contact_to_site=>FALSE
801 ,p_multiple_account_is_error=>p_control_rec.p_multiple_account_is_error
802 ,p_multiple_contact_is_error=>p_control_rec.p_multiple_contact_is_error
803 ,p_contact_tbl=>p_contact_tbl
804 ,p_multiple_account=>l_multiple_account
805 ,x_return_status=>x_return_status
806 ,x_msg_count=>x_msg_count
807 ,x_msg_data=>x_msg_data
808 );
809
810
811 IF l_debug_level > 0 THEN
812 oe_debug_pub.add( 'count contact table = '||P_CONTACT_TBL.COUNT ) ;
813 END IF;
814 IF p_Contact_tbl.COUNT > 0 THEN
815 FOR i in p_contact_tbl.FIRST..p_contact_tbl.LAST
816 LOOP
817 IF l_debug_level > 0 THEN
818 oe_debug_pub.add( 'contact_id = '||P_CONTACT_TBL ( I ) ) ;
819 END IF;
820 END LOOP;
821 END IF;
822
823 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
824 IF l_debug_level > 0 THEN
825 oe_debug_pub.add( 'contact with error' ) ;
826 END IF;
827 l_contact_status := FND_API.G_RET_STS_ERROR;
828 x_msg_data_contact := x_msg_data;
829 x_msg_count_contact := x_msg_count;
830 --OE_MSG_PUB.Count_And_Get
831 --( p_count => x_msg_count
832 --, p_data => x_msg_data
833 --);
834 --return;
835 END IF;
836
837
838 --If multiple Contact then return the contact table
839 IF p_contact_tbl.COUNT > 1 then
840 IF l_debug_level > 0 THEN
841 oe_debug_pub.add( 'returning with multiple contacts' ) ;
842 end IF;
843
844 if p_control_rec.p_multiple_contact_is_error then
845 IF l_debug_level > 0 THEN
846 oe_debug_pub.add( 'erroring with multiple contacts' ) ;
847 END IF;
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 else
850 x_return_status := FND_API.G_RET_STS_SUCCESS;
851 end if;
852 return;
853 ELSIF p_contact_tbl.COUNT = 0 then
854 IF l_debug_level > 0 THEN
855 oe_debug_pub.add( 'no contacts found error' ) ;
856 END IF;
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 ELSIF p_contact_tbl.COUNT = 1 then
859 null;
860 END IF;
861
862 ELSE
863 IF l_debug_level > 0 THEN
864 oe_debug_pub.add( 'not calling check contact' ) ;
865 END IF;
866
867 END IF; -- if contact data is passed
868
869 ELSE -- if status is not success
870
871 IF l_debug_level > 0 THEN
872 oe_debug_pub.add( '2 not calling check contact' ) ;
873 END IF;
874
875 END IF; -- if status was successful
876
877
878 IF p_control_rec.p_continue_processing_on_error then
879 IF l_debug_level > 0 THEN
880 oe_debug_pub.add( 'continue processing on error' ) ;
881 end if;
882 else
883 if l_debug_level > 0 then
884 oe_debug_pub.add( 'do not continue processing on error' ) ;
885 END IF;
886 END IF;
887
888 -- If status is not error and there are records in the site table
889 -- OR we shall continue even if error
890 IF (p_control_rec.p_continue_processing_on_error AND p_site_tbl.COUNT >0 ) OR
891 (x_return_status <> FND_API.G_RET_STS_ERROR AND p_site_tbl.COUNT > 0 ) THEN
892
893 -- loop through p_site_tbl for all contacts
894
895 FOR i IN p_site_tbl.FIRST..p_site_tbl.LAST LOOP
896
897 IF l_debug_level > 0 THEN
898 oe_debug_pub.add ('AAC:VTI: ============ SITE CONTACT '||i||' of '||p_site_tbl.LAST||' =============== ');
899 oe_debug_pub.add( 'AAC: processing site#'||i||' for contacts');
900 END IF;
901
902 l2_cust_account_id := l_cust_account_id;
903 l2_party_id := l_party_id;
904 l2_org_contact_id := l_org_contact_id;
905
906 if (nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
907 OR nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM )
908 then
912 oe_debug_pub.add('site level party_id: '||l2_party_id);
909 l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
910 l2_party_id := p_site_tbl(i).p_party_id;
911 oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
913 end if;
914
915 oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
916 oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
917 if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
918 = FND_API.G_MISS_NUM) THEN
919 l_cust_account_role_id:=null;
920 else
921 l_cust_account_role_id:=p_site_tbl(i).p_cust_account_role_id;
922 end if;
923 oe_debug_pub.add( 'AAC: After Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
924
925
926 if nvl(p_site_tbl(i).p_org_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
927 l2_org_contact_id := p_site_tbl(i).p_org_contact_id;
928 end if;
929
930 --{added for bug 4240715
931 if p_site_tbl(i).p_site_use_code ='END_CUST' then /* added check for end customer */
932 l_site_use_code := 'SOLD';
933 if nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM then
934 l_cust_account_role_id:=null;
935 else
936 l_cust_account_role_id :=p_site_tbl(i).p_cust_account_role_id;
937 end if;
938 else
939 l_site_use_code := p_site_tbl(i).p_site_use_code;
940 end if;
941 --bug 4240715}
942
943 Check_and_Create_Contact(
944 p_party_id=>l2_party_id
945 ,p_cust_account_id=>l2_cust_account_id
946 ,p_org_contact_id=>l2_org_Contact_id
947 ,P_site_use_code=>l_site_use_code --modified for bug 4240715
948 ,p_allow_contact_creation=>p_allow_contact_creation
949 ,p_create_responsibility=>FALSE
950 ,p_cust_account_role_id=>l_cust_account_role_id
951 ,p_cust_account_site_id=>null
952 ,p_assign_contact_to_site=>FALSE
953 ,p_multiple_account_is_error=>p_control_rec.p_multiple_account_is_error
954 ,p_multiple_contact_is_error=>p_control_rec.p_multiple_contact_is_error
955 ,p_contact_tbl=>p2_contact_tbl
956 ,p_multiple_account=>l_multiple_account
957 ,x_return_status=>x_return_status
958 ,x_msg_count=>x_msg_count
959 ,x_msg_data=>x_msg_data
960 );
961
962 IF l_debug_level > 0 THEN
963 oe_debug_pub.add( 'AAC: ..done calling check_and_create_contacts ' ) ;
964 oe_debug_pub.add( 'count contact table = '||P2_CONTACT_TBL.COUNT ) ;
965 END IF;
966
967 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
968 IF l_debug_level > 0 THEN
969 oe_debug_pub.add( 'contact with error' ) ;
970 END IF;
971
972 l_contact_status := FND_API.G_RET_STS_ERROR;
973 x_msg_data_contact := x_msg_data;
974 x_msg_count_contact := x_msg_count;
975 END IF;
976
977
978 --If multiple Contact then return the contact table
979 IF p2_contact_tbl.COUNT > 1 then
980 if p_control_rec.p_multiple_contact_is_error then
981 IF l_debug_level > 0 THEN
982 oe_debug_pub.add( 'erroring with multiple contacts' ) ;
983 END IF;
984 x_return_status := FND_API.G_RET_STS_ERROR;
985 else
986 x_return_status := FND_API.G_RET_STS_SUCCESS;
987 end if;
988
989 ELSIF p2_contact_tbl.COUNT = 0 then
990 IF l_debug_level > 0 THEN
991 oe_debug_pub.add( 'no contacts found error' ) ;
992 END IF;
993 x_return_status := FND_API.G_RET_STS_ERROR;
994 ELSIF p2_contact_tbl.COUNT = 1 then
995 p_site_tbl(i).p_cust_account_role_id := p2_contact_tbl(1);
996 END IF;
997 end loop;
998
999 IF l_debug_level > 0 THEN
1000 oe_debug_pub.add( 'XX calling check_and_create_sites... ' ) ;
1001 END IF;
1002
1006 sites will execute. If it is true then we will ignore the addresses passed to us and instead
1003 -- check_and_Create_Sites should also valdiate against related
1004 -- customers now. and create their sites if ncessary
1005 /*cc project, based on the g_fech_parimary_party_sites value the logic for check_and_create
1007 fetch and primary party sites and related account sites. If primary party sites are found and no corresponding
1008 account sites are there then we will create the account sites.
1009 If there is no primary party sites are found then we will use the party sites that are passed to
1010 fetch/create the account sites
1011
1012 The above logic is only for the Contact Center Integration. For other integrations and
1013 existing logic should work
1014 */
1015
1016 G_fetch_primary_party_sites :=p_control_rec.p_fetch_primary_party_sites;
1017
1018 IF l_debug_level > 0 THEN
1019 IF (G_fetch_primary_party_sites) THEN
1020 oe_debug_pub.add('G_fetch_primary_party_sites :TRUE');
1021 ELSE
1022 oe_debug_pub.add('G_fetch_primary_party_sites :FALSE');
1023 END IF;
1024 END IF;
1025
1026 /*cc project */
1027 Check_and_Create_Sites(
1028 p_party_id=>l_party_id
1029 ,p_cust_account_id=>l_cust_account_id
1030 ,p_site_tbl=>p_site_tbl
1031 ,p_allow_site_creation=>p_allow_site_creation
1032 ,p_continue_on_error=>p_control_rec.p_continue_processing_on_error
1033 ,x_return_status=>x_return_status
1034 ,x_msg_data=>x_msg_data
1035 ,x_msg_count=>x_msg_count
1036 );
1037
1038 IF l_debug_level > 0 THEN
1039 oe_debug_pub.add( 'XX ...done calling check_and_create_sites ' ) ;
1040 END IF;
1041
1042 ELSE
1043 IF l_debug_level > 0 THEN
1044 oe_debug_pub.add( 'not calling check_and_create_sites X ' ) ;
1045 END IF;
1046
1047 END IF; -- if site table exists
1048
1049 -- we may not have to check the success status as data may need to be
1050 -- commited even if some txn failed.
1051 -- however in telesales we will never commit so it is fine
1052 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1053 IF p_control_rec.p_commit THEN
1054 IF l_debug_level > 0 THEN
1055 oe_debug_pub.add( 'commiting the txn' ) ;
1056 end if;
1057 commit;
1058
1059 end if;
1060 else
1061
1062 if l_debug_level > 0 then
1063 oe_debug_pub.add( 'not commiting the txn' ) ;
1064 END IF;
1065 l_site_failed := TRUE;
1066 OE_MSG_PUB.Count_And_Get
1067 ( p_count => x_msg_count
1068 , p_data => x_msg_data
1069 );
1070
1071
1072 END IF;
1073
1074 -- THere might be failure in contact processing
1075 -- to show that message we send the status as error
1076 -- since in the client messages are displayed if only status is error
1077 IF l_debug_level > 0 THEN
1078 oe_debug_pub.add( ' checking contact failure status = '||l_contact_status ) ;
1079 END IF;
1080 IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1081 IF l_debug_level > 0 THEN
1082 oe_debug_pub.add( ' returning error for contact' ) ;
1083 END IF;
1084 x_return_status := FND_API.G_RET_STS_ERROR;
1085
1086 -- If site has failed then count and get above will catch that
1087 -- else to find the error message for contact we do this
1088 IF NOT l_site_failed then
1089 IF l_debug_level > 0 THEN
1090 oe_debug_pub.add( ' msg from contact only' ) ;
1091 END IF;
1092 x_msg_data := x_msg_data_contact;
1093 x_msg_count := x_msg_count_contact;
1094 END IF;
1095
1096 END IF;
1097 oe_debug_pub.add( 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
1098
1099 EXCEPTION
1100
1101 WHEN FND_API.G_EXC_ERROR THEN
1102
1103 IF c_check_account%ISOPEN THEN
1104 CLOSE c_check_account;
1105 END IF;
1106 IF c_get_cust_account_id%ISOPEN THEN
1107 CLOSE c_get_cust_account_id;
1108 END IF;
1109 IF c_cust_account_id%ISOPEN THEN
1110 CLOSE c_cust_account_id;
1111 END IF;
1112
1113 x_return_status := FND_API.G_RET_STS_ERROR;
1114
1115 -- Get message count and data
1116
1117 OE_MSG_PUB.Count_And_Get
1118 ( p_count => x_msg_count
1119 , p_data => x_msg_data
1120 );
1121
1122 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1123
1124
1125 IF c_get_cust_account_id%ISOPEN THEN
1126 CLOSE c_get_cust_account_id;
1127 END IF;
1128 IF c_cust_account_id%ISOPEN THEN
1129 CLOSE c_cust_account_id;
1130 END IF;
1131
1132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1133
1134 -- Get message count and data
1135
1136 OE_MSG_PUB.Count_And_Get
1137 ( p_count => x_msg_count
1138 , p_data => x_msg_data
1139 );
1140
1141 WHEN OTHERS THEN
1142
1143
1144 IF c_get_cust_account_id%ISOPEN THEN
1145 CLOSE c_get_cust_account_id;
1146 END IF;
1147 IF c_cust_account_id%ISOPEN THEN
1151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1148 CLOSE c_cust_account_id;
1149 END IF;
1150
1152 IF l_debug_level > 0 THEN
1153 oe_debug_pub.add( 'CREATE_ACCOUNT_LAYER WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
1154 END IF;
1155
1156 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1157 THEN
1158 OE_MSG_PUB.Add_Exc_Msg
1159 ( G_PKG_NAME
1160 , 'create_account_layer'
1161 );
1162 END IF;
1163
1164 -- Get message count and data
1165
1166 OE_MSG_PUB.Count_And_Get
1167 ( p_count => x_msg_count
1168 , p_data => x_msg_data
1169 );
1170
1171 END create_account_layer;
1172
1173
1174
1175 PROCEDURE Check_and_Create_Sites(
1176 p_party_id in number
1177 ,p_cust_account_id in number
1178 ,p_site_tbl in out NOCOPY /* file.sql.39 change */ site_tbl_Type
1179 ,p_allow_site_creation in boolean
1180 ,p_continue_on_error in boolean
1181 -- this expects either party_site_use_id or site_use_code and party_site_id
1182 ,x_return_status out NOCOPY varchar2
1183 ,x_msg_data out NOCOPY varchar2
1184 ,x_msg_count out NOCOPY varchar2
1185 ) IS
1186
1187 l_site_use_code varchar2(20);
1188 l_party_site_id number := null;
1189 l_party_id number := null;
1190 l_primary_per_type varchar2(10);
1191 l_cust_acct_site_id number;
1192 l_site_use_id number := null;
1193 l_site_use_primary_flag varchar2(10);
1194 l_send_primary varchar2(10);
1195 l_end_customer_passed varchar2(2); --added for bug 4240715
1196 l_status varchar2(10);
1197 l_primary_site_use varchar2(10);
1198 lx_party_site_use_id number := null; --added for bug 4240715
1199
1200 /* cc project */
1201 l_party_site_use_id number;
1202 l_party_site_id_cc number;
1203 /* cc project */
1204
1205
1206 CURSOR c_party_site_use(in_party_site_use_id in number) IS
1207 SELECT site_use.site_use_type,site_use.party_site_id,site_use.primary_per_type
1208 FROM hz_party_site_uses site_use,
1209 hz_party_sites site
1210 WHERE party_site_use_id = in_party_site_use_id
1211 AND site.party_site_id = site_use.party_site_id
1212 AND site.status = 'A'
1213 AND site_use.status='A';
1214
1215 CURSOR c_party_site(in_party_site_id in number) IS
1216 SELECT party_id,status
1217 FROM hz_party_sites
1218 WHERE party_site_id = in_party_site_id;
1219
1220
1221 CURSOR c_acct_site(in_cust_account_id in number
1222 ,in_party_site_id in number
1223 ,in_site_use_code in varchar2) IS
1224 SELECT s.cust_acct_site_id,u.site_use_id,u.primary_flag
1225 FROM hz_cust_acct_sites s,
1226 hz_cust_site_uses_all u
1227 WHERE s.cust_account_id = in_cust_account_id
1228 AND s.party_site_id = in_party_site_id
1229 AND s.status(+) = 'A'
1230 AND u.cust_acct_site_id(+) = s.cust_acct_site_id
1231 AND u.site_use_code(+) = in_site_use_code
1232 AND u.status(+) = 'A';
1233
1234 CURSOR c_site_use(in_site_use_id in number,
1235 in_site_use_code in varchar2) IS
1236 SELECT uses.cust_acct_site_id,
1237 uses.status,
1238 uses.primary_flag,
1239 cust_site.cust_account_id
1240 FROM hz_cust_site_uses uses, hz_cust_acct_sites_all cust_site
1241 WHERE site_use_id = in_site_Use_id
1242 AND site_use_code = in_site_use_code
1243 AND cust_site.cust_acct_site_id = uses.cust_acct_site_id;
1247 FROM hz_cust_acct_sites_all a,
1244
1245 CURSOR C_get_cust_from_site_use_id(l_site_use_Id NUMBER) IS
1246 SELECT a.cust_account_id
1248 hz_cust_site_uses b
1249 WHERE b.site_use_id=l_site_use_id
1250 and a.cust_acct_site_id=b.cust_acct_site_id
1251 and b.status = 'A';
1252
1253 /*cc project*/
1254 CURSOR c_get_party_sites(in_party_id in number) IS
1255 SELECT party_site_id
1256 FROM hz_party_sites
1257 where party_id = in_party_id
1258 and status='A';
1259
1260
1261 CURSOR c_prim_party_site_use(in_party_site_id in number,in_site_use_type in varchar2) IS
1262 SELECT party_site_use_id
1263 FROM hz_party_site_uses
1264 where party_site_id=in_party_site_id and
1265 site_use_type=in_site_use_type and
1266 primary_per_type='Y'
1267 and status='A';
1268
1269
1270 /*cc project*/
1271 --{added for bug 4240715
1272 CURSOR c_endcust_party_site_use(in_party_site_id in number,in_site_use_type in varchar2) IS
1273 SELECT site_use.party_site_use_id
1274 FROM hz_party_site_uses site_use,
1275 hz_party_sites site
1276 WHERE party_site_use_id = site_use.party_site_use_id
1277 AND site.party_site_id = in_party_site_id
1278 AND site_use.site_use_type =in_site_use_type
1279 AND site.status = 'A'
1280 AND site_use.status='A';
1281
1282 --bug 4240715}
1283
1284
1285 l_return_status varchar2(1);
1286 px_cust_account_id number := null;
1287 l_cust_account_id number := null;
1288 --l_site_party_site_use_id number := null;
1289 px_party_id number := null;
1290
1291 --
1292 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
1293 --
1294 BEGIN
1295
1296 px_party_id := p_party_id;
1297 px_cust_account_id := p_cust_account_id;
1298
1299 --l_debug_level:=1;
1300 x_return_status := FND_API.G_RET_STS_SUCCESS;
1301 l_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 IF l_debug_level > 0 THEN
1304 oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1305 oe_debug_pub.add(' party_id = '||p_party_id);
1306 oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1307 END IF;
1308
1309 IF p_cust_account_id is null then
1310 x_return_status := FND_API.G_RET_STS_ERROR;
1311 IF l_debug_level > 0 THEN
1312 oe_debug_pub.add( ' ERROR: cust account id must be sent to check sites' ) ;
1313 END IF;
1314
1315 END IF;
1316
1317
1318 FOR i in p_site_tbl.FIRST..p_site_tbl.LAST
1319 LOOP
1320
1321 l_party_site_id := null;
1322 l_site_use_code := null;
1323 l_party_id := null;
1324 l_status := null;
1325 l_site_use_code := null;
1326 l_party_id := null;
1327 l_primary_per_type := null;
1328 l_cust_acct_site_id := null;
1329 l_site_use_id := null;
1330 l_site_use_primary_flag := null;
1331 l_send_primary := null;
1332 l_primary_site_use := null;
1333 l_end_customer_passed :='N'; --added for bug 4240715
1334
1335 x_return_status := FND_API.G_RET_STS_SUCCESS;
1336 IF l_debug_level > 0 THEN
1337 oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1338 oe_debug_pub.add(' site table rec #'||i);
1339 oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1340 oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1341 oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1342 END IF;
1343
1344 if (p_site_tbl(i).p_process_site = FALSE) then
1345 IF (l_debug_level > 0) THEN
1346 oe_debug_pub.add( ' p_process_site is NULL, skipping site processing..');
1347 end if;
1348 goto skip_site;
1349 end if;
1350
1351 --{added for bug 4240715
1352 if(p_site_tbl(i).p_site_use_code ='END_CUST') then -- end_customer enhancement
1353 l_end_customer_passed :='Y';
1354 end if;
1355 --bug 4240715}
1356
1357 if nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM OR
1358 nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1359 then
1360 px_cust_account_id := p_site_tbl(i).p_cust_account_id;
1361 px_party_id := p_site_tbl(i).p_party_id;
1362 end if;
1363
1364
1365 IF l_debug_level > 0 THEN
1366 oe_debug_pub.add( ' site level customer: party_id:'||px_party_id ) ;
1367 oe_debug_pub.add( ' site level customer: account_id:'||px_cust_account_id ) ;
1368 END IF;
1369
1370
1371 if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1372 and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1373 and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374 and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375 and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379 and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376 and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377 and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378 and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380 and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381 and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382 and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383 and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1384 and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1385 then
1386 --nothing to do! return!
1387 IF l_debug_level > 0 THEN
1388 oe_debug_pub.add ('AAC: create_sites: no data passed in, likely a contact search; returning');
1389 END IF;
1390 return;
1391 end if;
1392
1393
1394 IF p_site_tbl(i).p_site_use_code IS NOT NULL THEN
1395 l_site_use_code := p_site_tbl(i).p_site_use_code;
1396 ELSE
1397 l_return_status := FND_API.G_RET_STS_ERROR;
1398 IF l_debug_level > 0 THEN
1399 oe_debug_pub.add( 'site use not specified' ) ;
1400 END IF;
1401 FND_MESSAGE.Set_Name('ONT','ONT_AAC_ERROR');
1402
1403 FND_MESSAGE.Set_Token('TEXT','Usage of Account Site should be specified', FALSE);
1404 OE_MSG_PUB.ADD;
1405 IF p_continue_on_error THEN
1406 null;
1407 ELSE
1408 OE_MSG_PUB.Count_And_Get
1409 ( p_count => x_msg_count
1410 , p_data => x_msg_data
1411 );
1412 return;
1413 END IF;
1414 END IF; -- if site_use_code is not null
1415
1416 -- If site_use_id is passed then we validate this for the account
1417 IF p_site_tbl(i).p_site_use_id IS NOT NULL THEN
1418
1419 IF l_debug_level > 0 THEN
1420 oe_debug_pub.add( ' checking site_use_id' ) ;
1421 END IF;
1422
1423 --{added for bug 4240715
1424 -- added for end customer /* check done based on the order SOLD_TO,SHIP_TO,BILL_TO,DELIVER_TO */
1425 if l_site_use_code ='END_CUST' then
1426
1427 OPEN c_site_use(p_site_tbl(i).p_site_use_id,'SOLD_TO');
1428 FETCH c_site_use
1429 INTO l_cust_acct_site_id,
1430 l_status,
1431 l_primary_site_use,
1432 l_cust_account_id;
1433
1434 if c_site_use%FOUND then
1435 oe_debug_pub.add('Checking for end customer of type SOLD_TO');
1436 l_site_use_code :='SOLD_TO';
1437 l_site_use_id := p_site_tbl(i).p_site_use_id;
1438 px_cust_account_id := p_cust_account_id;
1439 px_party_id := p_party_id;
1440 if l_status <> 'A' then
1441 l_return_status := fnd_api.g_ret_sts_error;
1442 IF l_debug_level > 0 THEN
1443 oe_debug_pub.add( 'account site use is inactive' ) ;
1444 END IF;
1445 end if;
1446 /* IF l_cust_account_id <> px_cust_account_id then
1447 x_return_status := FND_API.G_RET_STS_ERROR;
1448 l_return_status := FND_API.G_RET_STS_ERROR;
1449 IF l_debug_level > 0 THEN
1450 oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1451 END IF;
1452 End if; */
1453 goto end_customer_found;
1454 Else -- not sold to
1455 close c_site_use;
1456 end if; -- sold to check;
1457
1458 OPEN c_site_use(p_site_tbl(i).p_site_use_id,'SHIP_TO');
1459 FETCH c_site_use
1460 INTO l_cust_acct_site_id,
1461 l_status,
1462 l_primary_site_use,
1463 l_cust_account_id;
1464
1465 if c_site_use%FOUND then
1466 oe_debug_pub.add('Checking for end customer of type SHIP_TO');
1467 l_site_use_code :='SHIP_TO';
1468 l_site_use_id := p_site_tbl(i).p_site_use_id;
1469 px_cust_account_id := p_cust_account_id;
1470 px_party_id := p_party_id;
1471 if l_status <> 'A' then
1472 l_return_status := fnd_api.g_ret_sts_error;
1473 IF l_debug_level > 0 THEN
1474 oe_debug_pub.add( 'account site use is inactive' ) ;
1475 END IF;
1476 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1477 OE_MSG_PUB.ADD;
1478 end if;
1479 /* IF l_cust_account_id <> px_cust_account_id then
1480 x_return_status := FND_API.G_RET_STS_ERROR;
1481 l_return_status := FND_API.G_RET_STS_ERROR;
1482 IF l_debug_level > 0 THEN
1483 oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1484 END IF;
1485 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1486 OE_MSG_PUB.ADD;
1487 End if; */
1488 goto end_customer_found;
1489 Else
1490 close c_site_use;
1491 end if; -- ship to customer check;
1492
1493 OPEN c_site_use(p_site_tbl(i).p_site_use_id,'BILL_TO');
1494 FETCH c_site_use
1495 INTO l_cust_acct_site_id,
1496 l_status,
1497 l_primary_site_use,
1498 l_cust_account_id;
1499
1500 if c_site_use%FOUND then
1501 oe_debug_pub.add('Checking for end customer of type BILL_TO');
1502 l_site_use_code :='BILL_TO';
1503 l_site_use_id := p_site_tbl(i).p_site_use_id;
1504 px_cust_account_id := p_cust_account_id;
1508 IF l_debug_level > 0 THEN
1505 px_party_id := p_party_id;
1506 if l_status <> 'A' then
1507 l_return_status := fnd_api.g_ret_sts_error;
1509 oe_debug_pub.add( 'account site use is inactive' ) ;
1510 END IF;
1511 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
1512 OE_MSG_PUB.ADD;
1513 end if;
1514 /* IF l_cust_account_id <> px_cust_account_id then
1515 x_return_status := FND_API.G_RET_STS_ERROR;
1516 l_return_status := FND_API.G_RET_STS_ERROR;
1517 IF l_debug_level > 0 THEN
1518 oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1519 END IF;
1520 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_ACCOUNT');
1521 OE_MSG_PUB.ADD;
1522 End if;*/
1523 goto end_customer_found;
1524 Else -- not invoice to
1525 close c_site_use;
1526 end if;
1527
1528 OPEN c_site_use(p_site_tbl(i).p_site_use_id,'DELIVER_TO');
1529 FETCH c_site_use
1530 INTO l_cust_acct_site_id,
1531 l_status,
1532 l_primary_site_use,
1533 l_cust_account_id;
1534
1535 if c_site_use%FOUND then
1536 oe_debug_pub.add('Checking for end customer of type DELIVER_TO');
1537 l_site_use_code :='DELIVER_TO';
1538 l_site_use_id := p_site_tbl(i).p_site_use_id;
1539 px_cust_account_id := p_cust_account_id;
1540 px_party_id := p_party_id;
1541 if l_status <> 'A' then
1542 l_return_status := fnd_api.g_ret_sts_error;
1543 IF l_debug_level > 0 THEN
1544 oe_debug_pub.add( 'account site use is inactive' ) ;
1545 END IF;
1546 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
1547 OE_MSG_PUB.ADD;
1548 end if;
1549 /* IF l_cust_account_id <> px_cust_account_id then
1550 x_return_status := FND_API.G_RET_STS_ERROR;
1551 l_return_status := FND_API.G_RET_STS_ERROR;
1552 IF l_debug_level > 0 THEN
1553 oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1554 END IF;
1555 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_ACCOUNT');
1556 OE_MSG_PUB.ADD;
1557 End if; */
1558 goto end_customer_found;
1559 else
1560 close c_site_use;
1561 end if; -- deliver account;
1562 --bug 4240715}
1563 ELSE -- not End customer
1564
1565 OPEN c_site_use(p_site_tbl(i).p_site_use_id,l_site_use_code);
1566 FETCH c_site_use
1567 INTO l_cust_acct_site_id,
1568 l_status,
1569 l_primary_site_use,
1570 l_cust_account_id;
1571
1572 l_site_use_id := p_site_tbl(i).p_site_use_id;
1573 px_cust_account_id := p_cust_account_id;
1574 px_party_id := p_party_id
1575 ;
1576
1577 IF c_site_use%FOUND THEN
1578
1579 IF l_debug_level > 0 THEN
1580 oe_debug_pub.add('found site_use_id acct_site_id = '||l_cust_acct_site_id|| ' status='||l_status);
1581 oe_debug_pub.add(' primary = '||l_primary_site_use|| ' cust_account_id = '||l_cust_account_id ) ;
1582 end if;
1583
1584 if l_status <> 'A' then
1585 l_return_status := fnd_api.g_ret_sts_error;
1586 IF l_debug_level > 0 THEN
1587 oe_debug_pub.add( 'account site use is inactive' ) ;
1588 END IF;
1589
1590 IF l_site_use_code = 'SHIP_TO' then
1591 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1592 ELSIF l_site_use_code = 'BILL_TO' then
1593 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
1594 ELSIF l_site_use_code = 'DELIVER_TO' then
1595 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
1596 END IF;
1597
1598 OE_MSG_PUB.ADD;
1599 END IF;
1600
1601 oe_debug_pub.add( 'px_accoun_id:'||px_cust_account_id||'l_accoun_id:'||l_cust_account_id ) ;
1602 /* IF l_cust_account_id <> px_cust_account_id then
1603 x_return_status := FND_API.G_RET_STS_ERROR;
1604 l_return_status := FND_API.G_RET_STS_ERROR;
1605 IF l_debug_level > 0 THEN
1606 oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1607 END IF;
1608
1609 IF l_site_use_code = 'SHIP_TO' then
1610 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1611 ELSIF l_site_use_code = 'BILL_TO' then
1612 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_ACCOUNT');
1613 ELSIF l_site_use_code = 'DELIVER_TO' then
1614 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_ACCOUNT');
1615 END IF;
1616
1617 OE_MSG_PUB.ADD;
1618 END IF;
1619 */
1620
1621 p_site_tbl(i).p_cust_acct_site_id := l_cust_acct_site_id;
1622
1623 ELSIF c_site_use%NOTFOUND THEN
1624 x_return_status := FND_API.G_RET_STS_ERROR;
1625 l_return_status := FND_API.G_RET_STS_ERROR;
1626 IF l_debug_level > 0 THEN
1627 oe_debug_pub.add( 'INVALID SITE_USE_ID ' ) ;
1628 END IF;
1629
1630 IF l_site_use_code = 'SHIP_TO' then
1631 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_SHIPTO');
1632 ELSIF l_site_use_code = 'BILL_TO' then
1633 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_BILLTO');
1634 ELSIF l_site_use_code = 'DELIVER_TO' then
1635 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_DELIVERTO');
1636 END IF;
1637
1638 OE_MSG_PUB.ADD;
1639
1640 END IF; -- if cursor found
1641 CLOSE c_site_use;
1642 END IF; -- site_ use_code (bug 4240715)
1643 ELSE -- if site_use_id is null
1644
1648 /*cc project, based on the g_fech_parimary_party_sites value the logic for check_and_create
1645 IF l_debug_level > 0 THEN
1646 oe_debug_pub.add( ' site_use_id not passed' ) ;
1647 END IF;
1649 sites will execute. If it is true then we will ignore the addresses passed to us and instead
1650 fetch and primary party sites and related account sites. If primary party sites are found and no corresponding
1651 account sites are there then we will create the account sites.
1652 If there is no primary party sites are found then we will use the party sites that are passed to
1653 fetch/create the account sites
1654
1655 The above logic is only for the Contact Center Integration. For other integrations and
1656 existing logic should work
1657 */
1658 IF(G_fetch_primary_party_sites) THEN
1659
1660 /*fetching all the party sites of the given party_id */
1661
1662 FOR l_party_site_id_rec IN c_get_party_sites(p_site_tbl(i).p_party_id) LOOP
1663
1664 l_party_site_id_cc :=l_party_site_id_rec.party_site_id;
1665
1666 IF l_debug_level > 0 THEN
1667 oe_debug_pub.add('cc Party site id :'|| l_party_site_id_cc);
1668 oe_debug_pub.add('searching whether party site usage is primary or not');
1669 END IF;
1670
1671 OPEN c_prim_party_site_use(l_party_site_id_cc,p_site_tbl(i).p_site_use_code);
1672 FETCH c_prim_party_site_use
1673 into l_party_site_use_id;
1674
1675
1676 IF c_prim_party_site_use%NOTFOUND THEN
1677 IF l_debug_level > 0 THEN
1678 oe_debug_pub.add('no primary party site use of type :'||p_site_tbl(i).p_site_use_code||' of party_site_id:'||l_party_site_id_cc);
1679 END IF;
1680 ELSIF c_prim_party_site_use%FOUND THEN
1681 p_site_tbl(i).p_party_site_use_id :=l_party_site_use_id;
1682 l_party_site_id :=l_party_site_id_cc;
1683 IF l_debug_level > 0 THEN
1684 oe_debug_pub.add(' primary party site use of type :'||p_site_tbl(i).p_site_use_code||' of party_site_id:'||l_party_site_id_cc||' is found,party_site_use_id is'||l_party_site_use_id);
1685 oe_debug_pub.add('exiting from loop to search primary party site uses');
1686 END IF;
1687 Close c_prim_party_site_use;
1688 exit;
1689
1690 END IF;
1691
1692 IF c_prim_party_site_use%ISOPEN then
1693 Close c_prim_party_site_use;
1694 END IF;
1695
1696 END LOOP;
1697
1698 /* By now we should have got the l_party_site_id populated in case a active primary party site use is found.
1699 If we have not got it yet then we should execute the party_site_id passed to us
1700 */
1701 IF(l_party_site_id IS NULL) Then
1702
1703 /*party_site_use_id will never be passed under contact center integration.
1704 we have to use site_use_code and party_site_id
1705 */
1706 -- if party_site_id is sent
1707 IF l_party_site_id is null AND
1708 x_return_status <> FND_API.G_RET_STS_ERROR then
1709 oe_debug_pub.add( 'l_party_site_id is null and p_site_tbl(i).p_party_site_id is '||p_site_tbl(i).p_party_site_id);
1710 IF p_site_tbl(i).p_party_site_id is not null then
1711 IF l_debug_level > 0 THEN
1712 oe_debug_pub.add( 'checking for party_site_id ' ) ;
1713 END IF;
1714
1715 l_party_site_id := p_site_tbl(i).p_party_site_id;
1716
1720 l_status;
1717 OPEN c_party_site(l_party_site_id);
1718 FETCH c_party_site
1719 INTO l_party_id,
1721
1722 IF c_party_site%NOTFOUND THEN
1723 l_return_status := FND_API.G_RET_STS_ERROR;
1724 x_return_status := FND_API.G_RET_STS_ERROR;
1725 IF l_debug_level > 0 THEN
1726 oe_debug_pub.add( 'no such party site id' ) ;
1727 END IF;
1728 CLOSE c_party_site;
1729
1730 IF l_site_use_code = 'SHIP_TO' then
1731 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_SHIPTO');
1732 ELSIF l_site_use_code = 'BILL_TO' then
1733 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_BILLTO');
1734 ELSIF l_site_use_code = 'DELIVER_TO' then
1735 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_DELIVERTO');
1736 END IF;
1737
1738 OE_MSG_PUB.ADD;
1739 IF p_continue_on_error THEN
1740 null;
1741 ELSE
1742 OE_MSG_PUB.Count_And_Get
1743 ( p_count => x_msg_count
1744 , p_data => x_msg_data
1745 );
1746 return;
1747 END IF;
1748
1749 ELSIF c_party_site%FOUND THEN
1750
1751 IF l_debug_level > 0 THEN
1752 oe_debug_pub.add( 'found for party_site_id ' ) ;
1753 END IF;
1754
1755 IF l_status <> 'A' THEN
1756
1757 x_return_status := FND_API.G_RET_STS_ERROR;
1758 l_return_status := FND_API.G_RET_STS_ERROR;
1759
1760 IF l_site_use_code = 'SHIP_TO' then
1761 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1762 ELSIF l_site_use_code = 'BILL_TO' then
1763 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
1764 ELSIF l_site_use_code = 'DELIVER_TO' then
1765 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
1766 END IF;
1767
1768 OE_MSG_PUB.ADD;
1769 IF l_debug_level > 0 THEN
1770 oe_debug_pub.add( 'party site is not active' ) ;
1771 END IF;
1772 CLOSE c_party_site;
1773 IF p_continue_on_error THEN
1774 null;
1775 ELSE
1776 OE_MSG_PUB.Count_And_Get
1777 ( p_count => x_msg_count
1778 , p_data => x_msg_data
1779 );
1780 return;
1781 END IF;
1782
1783 END IF; -- if status is not active
1784
1785 oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1786
1787 IF px_party_id <> l_party_id THEN
1788 x_return_status := FND_API.G_RET_STS_ERROR;
1789 l_return_status := FND_API.G_RET_STS_ERROR;
1790
1791 IF l_site_use_code = 'SHIP_TO' then
1792 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1793 ELSIF l_site_use_code = 'BILL_TO' then
1794 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_ACCOUNT');
1795 ELSIF l_site_use_code = 'DELIVER_TO' then
1796 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_ACCOUNT');
1797 END IF;
1798
1799 OE_MSG_PUB.ADD;
1800 IF l_debug_level > 0 THEN
1801 oe_debug_pub.add( 'party site does not belong to the party' ) ;
1802 END IF;
1803
1804 CLOSE c_party_site;
1805 IF p_continue_on_error THEN
1806 null;
1807 ELSE
1808 OE_MSG_PUB.Count_And_Get
1809 ( p_count => x_msg_count
1810 , p_data => x_msg_data
1811 );
1812 return;
1813 END IF;
1814 END IF; -- if party_id does not match
1815
1816 IF c_party_site%ISOPEN THEN
1817 CLOSE c_party_site;
1818 END IF;
1819
1820 END IF; -- if party_site_id found
1821
1822 ELSE -- if party_site_id is null
1823 oe_debug_pub.add( 'Yes.. Party_Site_id is not null');
1824 if l_end_customer_passed ='N' then -- if its not end customer(4240715)
1825 x_return_status := FND_API.G_RET_STS_ERROR;
1826 l_return_status := FND_API.G_RET_STS_ERROR;
1827 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1828 FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
1829 OE_MSG_PUB.ADD;
1830 IF l_debug_level > 0 THEN
1831 oe_debug_pub.add( 'no site information is sent ' ) ;
1832 END IF;
1833 IF p_continue_on_error THEN
1834 null;
1835 ELSE
1836 OE_MSG_PUB.Count_And_Get
1837 ( p_count => x_msg_count
1838 , p_data => x_msg_data
1839 );
1840 return;
1841 END IF;
1842 end if; -- as long as its not end customer(4240715)
1843 END IF; -- if party_site_id not null
1844 END IF; -- if l_party_site_id is null
1845 ELSE
1846 oe_debug_pub.add('Have got the primary_party_site_use_id');
1847 END IF;
1848
1849
1853
1850 ELSE --G_fetch_primary_party_sites =FALSE
1851 -- existing logic
1852
1854 -- Determining the Site Use Code
1855 -- Either party_site_use_id should be passed or
1856 -- party_site_id and site_use_code should be passed
1857 IF p_site_tbl(i).p_party_site_use_id IS NOT NULL THEN
1858
1859 IF l_debug_level > 0 THEN
1860 oe_debug_pub.add( 'checking for party_site_use_id ='|| P_SITE_TBL ( I ) .P_PARTY_SITE_USE_ID ) ;
1861 END IF;
1862 OPEN c_party_site_use(p_site_tbl(i).p_party_site_use_id);
1863 FETCH c_party_site_use
1864 INTO l_site_use_code,
1865 l_party_site_id,
1866 l_primary_per_type;
1867
1868 IF c_party_site_use%NOTFOUND THEN
1869 l_return_status := FND_API.G_RET_STS_ERROR;
1870 x_return_status := FND_API.G_RET_STS_ERROR;
1871 IF l_debug_level > 0 THEN
1872 oe_debug_pub.add( 'not a valid party site use id' ) ;
1873 END IF;
1874
1875 IF l_site_use_code = 'SHIP_TO' then
1876 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_SHIPTO');
1877 ELSIF l_site_use_code = 'BILL_TO' then
1878 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_BILLTO');
1879 ELSIF l_site_use_code = 'DELIVER_TO' then
1880 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_DELIVERTO');
1881 END IF;
1882
1883 OE_MSG_PUB.ADD;
1884 CLOSE c_party_site_use;
1885
1886 IF p_continue_on_error THEN
1887 null;
1888 ELSE
1889 OE_MSG_PUB.Count_And_Get
1890 ( p_count => x_msg_count
1891 , p_data => x_msg_data
1892 );
1893 return;
1894 END IF;
1895 ELSIF c_party_site_use%FOUND THEN
1896 p_site_tbl(i).p_party_site_id := l_party_site_id;
1897 END IF;
1898
1899 IF l_debug_level > 0 THEN
1900 oe_debug_pub.add( ' site_use_code = '||L_SITE_USE_CODE ) ;
1901 END IF;
1902 -- site use code has to be passed in the control record
1903 -- because we can make a party site as a account site of bill,ship,deliver
1904 -- even if the party site use is STMNTS or others
1905 --p_site_tbl(i).p_site_use_code := l_site_use_code;
1906
1907 IF c_party_site_use%ISOPEN THEN
1908 CLOSE c_party_site_use;
1909 END IF;
1910
1911 END IF; -- if party_site_use_id is not null
1912
1913 -- if party_site_id is sent
1914 IF l_party_site_id is null AND
1915 x_return_status <> FND_API.G_RET_STS_ERROR then
1916 IF p_site_tbl(i).p_party_site_id is not null then
1917 IF l_debug_level > 0 THEN
1918 oe_debug_pub.add( 'checking for party_site_id ' ) ;
1919 END IF;
1920 l_party_site_id := p_site_tbl(i).p_party_site_id;
1921
1922 OPEN c_party_site(l_party_site_id);
1923 FETCH c_party_site
1924 INTO l_party_id,
1925 l_status;
1926 IF c_party_site%NOTFOUND THEN
1927 l_return_status := FND_API.G_RET_STS_ERROR;
1928 x_return_status := FND_API.G_RET_STS_ERROR;
1929 IF l_debug_level > 0 THEN
1930 oe_debug_pub.add( 'no such party site id' ) ;
1931 END IF;
1932 CLOSE c_party_site;
1933
1934 IF l_site_use_code = 'SHIP_TO' then
1935 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_SHIPTO');
1936 ELSIF l_site_use_code = 'BILL_TO' then
1937 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_BILLTO');
1938 ELSIF l_site_use_code = 'DELIVER_TO' then
1939 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_DELIVERTO');
1940 END IF;
1941
1942 OE_MSG_PUB.ADD;
1943 IF p_continue_on_error THEN
1944 null;
1945 ELSE
1946 OE_MSG_PUB.Count_And_Get
1947 ( p_count => x_msg_count
1948 , p_data => x_msg_data
1949 );
1950 return;
1951 END IF;
1952 ELSIF c_party_site%FOUND THEN
1953
1954 IF l_debug_level > 0 THEN
1955 oe_debug_pub.add( 'found for party_site_id ' ) ;
1956 END IF;
1957 IF l_status <> 'A' THEN
1958
1959 x_return_status := FND_API.G_RET_STS_ERROR;
1960 l_return_status := FND_API.G_RET_STS_ERROR;
1961
1962 IF l_site_use_code = 'SHIP_TO' then
1963 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1964 ELSIF l_site_use_code = 'BILL_TO' then
1965 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
1966 ELSIF l_site_use_code = 'DELIVER_TO' then
1967 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
1968 END IF;
1969
1970 OE_MSG_PUB.ADD;
1971 IF l_debug_level > 0 THEN
1972 oe_debug_pub.add( 'party site is not active' ) ;
1973 END IF;
1974 CLOSE c_party_site;
1975 IF p_continue_on_error THEN
1976 null;
1977 ELSE
1978 OE_MSG_PUB.Count_And_Get
1979 ( p_count => x_msg_count
1980 , p_data => x_msg_data
1981 );
1982 return;
1983 END IF;
1984
1985 END IF; -- if status is not active
1986
1987 oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1988
1989 IF px_party_id <> l_party_id THEN
1990 x_return_status := FND_API.G_RET_STS_ERROR;
1991 l_return_status := FND_API.G_RET_STS_ERROR;
1992
1993 IF l_site_use_code = 'SHIP_TO' then
1997 ELSIF l_site_use_code = 'DELIVER_TO' then
1994 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1995 ELSIF l_site_use_code = 'BILL_TO' then
1996 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_ACCOUNT');
1998 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_ACCOUNT');
1999 END IF;
2000
2001 OE_MSG_PUB.ADD;
2002 IF l_debug_level > 0 THEN
2003 oe_debug_pub.add( 'party site does not belong to the party' ) ;
2004 END IF;
2005 CLOSE c_party_site;
2006 IF p_continue_on_error THEN
2007 null;
2008 ELSE
2009 OE_MSG_PUB.Count_And_Get
2010 ( p_count => x_msg_count
2011 , p_data => x_msg_data
2012 );
2013 return;
2014 END IF;
2015 END IF; -- if party_id does not match
2016
2017 IF c_party_site%ISOPEN THEN
2018 CLOSE c_party_site;
2019 END IF;
2020
2021 END IF; -- if party_site_id found
2022
2023 ELSE -- if party_site_id is null
2024 oe_debug_pub.add( 'Yes.. Party_Site_id is not null.. Second');
2025 if l_end_customer_passed ='N' then -- if its not end customer(bug 4240715)
2026 x_return_status := FND_API.G_RET_STS_ERROR;
2027 l_return_status := FND_API.G_RET_STS_ERROR;
2028 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
2029 FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
2030 OE_MSG_PUB.ADD;
2031 IF l_debug_level > 0 THEN
2032 oe_debug_pub.add( 'no site information is sent ' ) ;
2033 END IF;
2034 IF p_continue_on_error THEN
2035 null;
2036 ELSE
2037 OE_MSG_PUB.Count_And_Get
2038 ( p_count => x_msg_count
2039 , p_data => x_msg_data
2040 );
2041 return;
2042 END IF;
2043 End If; --if its not end customer
2044 END IF; -- if party_site_id not null
2045 END IF; -- if l_party_site_id is null
2046
2047 IF l_debug_level > 0 THEN
2048 oe_debug_pub.add( ' l_party_site_id ='||l_party_site_id|| ' l_site_use_code ='||L_SITE_USE_CODE ) ;
2049 END IF;
2050
2051
2052 END IF; --G_fetch_primary_party_sites
2053
2054 --{added for bug 4240715
2055 --------------------------
2056
2057 -- added for end customer /* check done based on the order SOLD_TO,SHIP_TO,BILL_TO,DELIVER_TO */
2058 if l_site_use_code ='END_CUST' then
2059 OPEN c_endcust_party_site_use(l_party_site_id,'SOLD_TO');
2060 FETCH c_endcust_party_site_use
2061 INTO lx_party_site_use_id;
2062
2063 if c_endcust_party_site_use%FOUND then
2064 oe_debug_pub.add('Checking for end customer of type SOLD_TO');
2065 l_site_use_code :='SOLD_TO';
2066 goto end_customer_site_use_found;
2067 Else -- not sold to
2068 close c_endcust_party_site_use;
2069 end if; -- sold to check;
2070
2071 OPEN c_endcust_party_site_use(l_party_site_id,'SHIP_TO');
2072 FETCH c_endcust_party_site_use
2073 INTO lx_party_site_use_id;
2074
2075 if c_endcust_party_site_use%FOUND then
2076 oe_debug_pub.add('Checking for end customer of type SHIP_TO');
2077 l_site_use_code :='SHIP_TO';
2078 goto end_customer_site_use_found;
2079 Else -- not sold to
2080 close c_endcust_party_site_use;
2081 end if; -- sold to check;
2082 OPEN c_endcust_party_site_use(l_party_site_id,'BILL_TO');
2083 FETCH c_endcust_party_site_use
2084 INTO lx_party_site_use_id;
2085
2086 if c_endcust_party_site_use%FOUND then
2087 oe_debug_pub.add('Checking for end customer of type BILL_TO');
2088 l_site_use_code :='BILL_TO';
2089 goto end_customer_site_use_found;
2090 Else -- not sold to
2091 close c_endcust_party_site_use;
2092 end if; -- sold to check;
2093 OPEN c_endcust_party_site_use(l_party_site_id,'DELIVER_TO');
2094 FETCH c_endcust_party_site_use
2095 INTO lx_party_site_use_id;
2096
2097 if c_endcust_party_site_use%FOUND then
2098 oe_debug_pub.add('Checking for end customer of type DELIVER_TO');
2099 l_site_use_code :='DELIVER_TO';
2100 goto end_customer_site_use_found;
2101 Else -- not sold to
2102 close c_endcust_party_site_use;
2103 end if; -- sold to check;
2104 oe_debug_pub.add('Site use code selected for end customer is'||l_site_use_code);
2105 END IF;
2106 --------------------------------
2107
2108 <<end_customer_site_use_found>>
2109
2110
2111 IF x_return_status = FND_API.G_RET_STS_SUCCESS and l_site_use_code <>'END_CUST' THEN
2112
2113
2114 IF l_debug_level > 0 THEN
2115 oe_debug_pub.add( ' continuing processing' ) ;
2116 end if;
2117 -- fetch the account_site_id
2118 IF p_site_tbl(i).p_create_primary_acct_site_use then
2119 l_send_primary := 'Y';
2120 ELSE
2121 l_send_primary := 'N';
2122 END IF;
2123
2124 OPEN c_acct_site(px_cust_account_id,l_party_site_id,l_site_use_code);
2125 FETCH c_acct_site
2126 INTO l_cust_acct_site_id,
2127 l_site_use_id,
2128 l_site_use_primary_flag;
2129 IF c_acct_site%FOUND THEN
2130
2131 IF l_debug_level > 0 THEN
2132 oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2133 END IF;
2134 IF l_site_use_id IS NULL THEN
2135 IF l_debug_level > 0 THEN
2139
2136 oe_debug_pub.add( 'make a call to create account site use' ) ;
2137 END IF;
2138 -- make a call to create account site use
2140 IF p_allow_site_creation then
2141 oe_oe_inline_address.Create_Acct_Site_Uses
2142 (
2143 p_cust_acct_site_id =>l_cust_acct_site_id,
2144 p_location =>null,
2145 p_site_use_code =>l_site_use_code,
2146 x_site_use_id =>l_site_use_id ,
2147 x_return_status => x_return_status,
2148 x_msg_count => x_msg_count,
2149 x_msg_data => x_msg_data,
2150 c_attribute_category=>null,
2151 c_attribute1=>null,
2152 c_attribute2=>null,
2153 c_attribute3=>null,
2154 c_attribute4=>null,
2155 c_attribute5=>null,
2156 c_attribute6=>null,
2157 c_attribute7=>null,
2158 c_attribute8=>null,
2159 c_attribute9=>null,
2160 c_attribute10=>null,
2161 c_attribute11=>null,
2162 c_attribute12=>null,
2163 c_attribute13=>null,
2164 c_attribute14=>null,
2165 c_attribute15=>null,
2166 c_attribute16=>null,
2167 c_attribute17=>null,
2168 c_attribute18=>null,
2169 c_attribute19=>null,
2170 c_attribute20=>null,
2171 c_attribute21=>null,
2172 c_attribute22=>null,
2173 c_attribute23=>null,
2174 c_attribute24=>null,
2175 c_attribute25=>null,
2176 in_created_by_module=>G_CREATED_BY_MODULE,
2177 in_primary_flag =>l_send_primary
2178 );
2179 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2180 CLOSE c_acct_site;
2181 IF l_debug_level > 0 THEN
2182 oe_debug_pub.add( ' account site use creation failed' ) ;
2183 END IF;
2184 IF l_debug_level > 0 THEN
2185 oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2186 END IF;
2187
2188 x_return_status := FND_API.G_RET_STS_ERROR;
2189 l_return_status := FND_API.G_RET_STS_ERROR;
2190
2191 IF x_msg_count = 1 then
2192
2193 IF l_site_use_code = 'SHIP_TO' then
2194 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2195 ELSIF l_site_use_code = 'BILL_TO' then
2196 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_SITE_CREATION');
2197 ELSIF l_site_use_code = 'DELIVER_TO' then
2198 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_SITE_CREATION');
2199 END IF;
2200
2201 FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2202 OE_MSG_PUB.ADD;
2203 ELSE
2204 oe_msg_pub.transfer_msg_stack;
2205 END IF;
2206
2207 IF p_continue_on_error THEN
2208 null;
2209 ELSE
2210 return;
2211 END IF;
2212 END IF;
2213 ELSE
2214 x_return_status := FND_API.G_RET_STS_ERROR;
2215 l_return_status := FND_API.G_RET_STS_ERROR;
2216 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2217 OE_MSG_PUB.ADD;
2218 IF l_debug_level > 0 THEN
2219 oe_debug_pub.add( ' not authorized to create site' ) ;
2220 END IF;
2221 CLOSE c_acct_site;
2222 IF p_continue_on_error THEN
2223 null;
2224 ELSE
2225 return;
2226 END IF;
2227 END IF; -- if permission to create sites
2228
2229 END IF; -- if site_use_id is null;
2230 ELSIF c_acct_site%NOTFOUND THEN
2231
2232 -- make a call to create acct site and account site use
2233 IF l_debug_level > 0 THEN
2234 oe_debug_pub.add( 'make a call to create acct site and use' ) ;
2235 END IF;
2236
2237 IF p_allow_site_creation THEN
2238
2239 IF l_debug_level > 0 THEN
2240 oe_debug_pub.add( ' creating account site' ) ;
2241 end IF;
2242 oe_oe_inline_address.Create_Account_Site
2243 (
2244 p_cust_account_id =>px_cust_account_id,
2245 p_party_site_id =>l_party_site_id,
2246 c_attribute_category=>null,
2247 c_attribute1=>null,
2248 c_attribute2=>null,
2249 c_attribute3=>null,
2250 c_attribute4=>null,
2251 c_attribute5=>null,
2252 c_attribute6=>null,
2253 c_attribute7=>null,
2254 c_attribute8=>null,
2255 c_attribute9=>null,
2256 c_attribute10=>null,
2257 c_attribute11=>null,
2258 c_attribute12=>null,
2259 c_attribute13=>null,
2260 c_attribute14=>null,
2261 c_attribute15=>null,
2262 c_attribute16=>null,
2263 c_attribute17=>null,
2264 c_attribute18=>null,
2265 c_attribute19=>null,
2266 c_attribute20=>null,
2267 x_customer_site_id =>l_cust_acct_site_id ,
2268 x_return_status => x_return_status,
2269 x_msg_count => x_msg_count,
2270 x_msg_data => x_msg_data,
2271 in_created_by_module=>G_CREATED_BY_MODULE
2272 ) ;
2273 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2274 CLOSE c_acct_site;
2275 l_return_status := FND_API.G_RET_STS_ERROR;
2276 IF l_debug_level > 0 THEN
2277 oe_debug_pub.add( ' account site creation failed' ) ;
2278 oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2279 END IF;
2280 IF x_msg_count = 1 then
2281
2285 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_SITE_CREATION');
2282 IF l_site_use_code = 'SHIP_TO' then
2283 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2284 ELSIF l_site_use_code = 'BILL_TO' then
2286 ELSIF l_site_use_code = 'DELIVER_TO' then
2287 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_SITE_CREATION');
2288 END IF;
2289
2290 FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2291 OE_MSG_PUB.ADD;
2292 ELSE
2293 oe_msg_pub.transfer_msg_stack;
2294 END IF;
2295 IF p_continue_on_error THEN
2296 null;
2297 ELSE
2298 return;
2299 END IF;
2300
2301 ELSE
2302
2303 IF l_debug_level > 0 THEN
2304 oe_debug_pub.add( ' acct_site_id created = '||L_CUST_ACCT_SITE_ID ) ;
2305 END IF;
2306 oe_oe_inline_address.Create_Acct_Site_Uses
2307 (
2308 p_cust_acct_site_id =>l_cust_acct_site_id,
2309 p_location =>null,
2310 p_site_use_code =>l_site_use_code,
2311 x_site_use_id =>l_site_use_id ,
2312 x_return_status => x_return_status,
2313 x_msg_count => x_msg_count,
2314 x_msg_data => x_msg_data,
2315 c_attribute_category=>null,
2316 c_attribute1=>null,
2317 c_attribute2=>null,
2318 c_attribute3=>null,
2319 c_attribute4=>null,
2320 c_attribute5=>null,
2321 c_attribute6=>null,
2322 c_attribute7=>null,
2323 c_attribute8=>null,
2324 c_attribute9=>null,
2325 c_attribute10=>null,
2326 c_attribute11=>null,
2327 c_attribute12=>null,
2328 c_attribute13=>null,
2329 c_attribute14=>null,
2330 c_attribute15=>null,
2331 c_attribute16=>null,
2332 c_attribute17=>null,
2333 c_attribute18=>null,
2334 c_attribute19=>null,
2335 c_attribute20=>null,
2336 c_attribute21=>null,
2337 c_attribute22=>null,
2338 c_attribute23=>null,
2339 c_attribute24=>null,
2340 c_attribute25=>null,
2341 in_created_by_module=>G_CREATED_BY_MODULE,
2342 in_primary_flag =>l_send_primary
2343 );
2344
2345 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2346 l_return_status := FND_API.G_RET_STS_ERROR;
2347 CLOSE c_acct_site;
2348 IF l_debug_level > 0 THEN
2349 oe_debug_pub.add( ' account site use creation failed' ) ;
2350 oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2351 END IF;
2352 IF x_msg_count = 1 then
2353 IF l_debug_level > 0 THEN
2354 oe_debug_pub.add( ' adding to message stack' ) ;
2355 END IF;
2356
2357 IF l_site_use_code = 'SHIP_TO' then
2358 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2359 ELSIF l_site_use_code = 'BILL_TO' then
2360 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_SITE_CREATION');
2361 ELSIF l_site_use_code = 'DELIVER_TO' then
2362 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_SITE_CREATION');
2363 END IF;
2364
2365 FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2366 OE_MSG_PUB.ADD;
2367 ELSE
2368 IF l_debug_level > 0 THEN
2369 oe_debug_pub.add( ' transferring to message stack' ) ;
2370 END IF;
2371 oe_msg_pub.transfer_msg_stack;
2372 END IF;
2373 IF p_continue_on_error THEN
2374 null;
2375 ELSE
2376 return;
2377 END IF;
2378
2379 END IF;
2380
2381 END IF; -- if acct site creation succeeded
2382
2383 ELSE
2384
2385 x_return_status := FND_API.G_RET_STS_ERROR;
2386 l_return_status := FND_API.G_RET_STS_ERROR;
2387 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2388 OE_MSG_PUB.ADD;
2389 IF l_debug_level > 0 THEN
2390 oe_debug_pub.add( '2 not allowed to create site' ) ;
2391 END IF;
2392 CLOSE c_acct_site;
2393 IF p_continue_on_error THEN
2394 null;
2395 ELSE
2396 return;
2397 END IF;
2398
2399 END IF; -- if allow site creation
2400
2401 END IF; -- if cursor found
2402
2403 IF c_acct_site%ISOPEN THEN
2404 CLOSE c_acct_site;
2405 END IF;
2406
2407 END IF; -- if status is success
2408
2409 IF l_debug_level > 0 THEN
2410 oe_debug_pub.add( ' assinging site table site_id = '||l_site_use_id|| ' acct site_id = '||L_CUST_ACCT_SITE_ID ) ;
2411 END IF;
2412
2413
2414 -- END IF; -- if site_use_id is passed
2415 --**
2416
2417 --bug 4240715}
2418
2419
2420
2421 /*cc project*/
2422
2423 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2424
2425 IF l_debug_level > 0 THEN
2426 oe_debug_pub.add( ' continuing processing' ) ;
2427 end if;
2428 -- fetch the account_site_id
2429 IF p_site_tbl(i).p_create_primary_acct_site_use then
2430 l_send_primary := 'Y';
2431 ELSE
2432 l_send_primary := 'N';
2433 END IF;
2434
2438 l_site_use_id,
2435 OPEN c_acct_site(px_cust_account_id,l_party_site_id,l_site_use_code);
2436 FETCH c_acct_site
2437 INTO l_cust_acct_site_id,
2439 l_site_use_primary_flag;
2440 IF c_acct_site%FOUND THEN
2441
2442 IF l_debug_level > 0 THEN
2443 oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2444 END IF;
2445 IF l_site_use_id IS NULL THEN
2446 IF l_debug_level > 0 THEN
2447 oe_debug_pub.add( 'make a call to create account site use' ) ;
2448 END IF;
2449 -- make a call to create account site use
2450
2451 IF p_allow_site_creation then
2452 oe_oe_inline_address.Create_Acct_Site_Uses
2453 (
2454 p_cust_acct_site_id =>l_cust_acct_site_id,
2455 p_location =>null,
2456 p_site_use_code =>l_site_use_code,
2457 x_site_use_id =>l_site_use_id ,
2458 x_return_status => x_return_status,
2459 x_msg_count => x_msg_count,
2460 x_msg_data => x_msg_data,
2461 c_attribute_category=>null,
2462 c_attribute1=>null,
2463 c_attribute2=>null,
2464 c_attribute3=>null,
2465 c_attribute4=>null,
2466 c_attribute5=>null,
2467 c_attribute6=>null,
2468 c_attribute7=>null,
2469 c_attribute8=>null,
2470 c_attribute9=>null,
2471 c_attribute10=>null,
2472 c_attribute11=>null,
2473 c_attribute12=>null,
2474 c_attribute13=>null,
2475 c_attribute14=>null,
2476 c_attribute15=>null,
2477 c_attribute16=>null,
2478 c_attribute17=>null,
2479 c_attribute18=>null,
2480 c_attribute19=>null,
2481 c_attribute20=>null,
2482 c_attribute21=>null,
2483 c_attribute22=>null,
2484 c_attribute23=>null,
2485 c_attribute24=>null,
2486 c_attribute25=>null,
2487 in_created_by_module=>G_CREATED_BY_MODULE,
2488 in_primary_flag =>l_send_primary
2489 );
2490 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2491 CLOSE c_acct_site;
2492 IF l_debug_level > 0 THEN
2493 oe_debug_pub.add( ' account site use creation failed' ) ;
2494 END IF;
2495 IF l_debug_level > 0 THEN
2496 oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2497 END IF;
2498
2499 x_return_status := FND_API.G_RET_STS_ERROR;
2500 l_return_status := FND_API.G_RET_STS_ERROR;
2501
2502 IF x_msg_count = 1 then
2503
2504 IF l_site_use_code = 'SHIP_TO' then
2505 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2506 ELSIF l_site_use_code = 'BILL_TO' then
2507 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_SITE_CREATION');
2508 ELSIF l_site_use_code = 'DELIVER_TO' then
2509 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVER_SITE_CREATION');
2510 END IF;
2511
2512 FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2513 OE_MSG_PUB.ADD;
2514 ELSE
2515 oe_msg_pub.transfer_msg_stack;
2516 END IF;
2517
2518 IF p_continue_on_error THEN
2519 null;
2520 ELSE
2521 return;
2522 END IF;
2523 END IF;
2524 ELSE
2525 x_return_status := FND_API.G_RET_STS_ERROR;
2526 l_return_status := FND_API.G_RET_STS_ERROR;
2527 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2528 OE_MSG_PUB.ADD;
2529 IF l_debug_level > 0 THEN
2530 oe_debug_pub.add( ' not authorized to create site' ) ;
2531 END IF;
2532 CLOSE c_acct_site;
2533 IF p_continue_on_error THEN
2534 null;
2535 ELSE
2536 return;
2537 END IF;
2538 END IF; -- if permission to create sites
2539
2540 END IF; -- if site_use_id is null;
2541 ELSIF c_acct_site%NOTFOUND THEN
2542
2543 -- make a call to create acct site and account site use
2544 IF l_debug_level > 0 THEN
2545 oe_debug_pub.add( 'make a call to create acct site and use' ) ;
2546 END IF;
2547
2548 IF p_allow_site_creation THEN
2549
2550 IF l_debug_level > 0 THEN
2551 oe_debug_pub.add( ' creating account site' ) ;
2552 end IF;
2553 oe_oe_inline_address.Create_Account_Site
2554 (
2555 p_cust_account_id =>px_cust_account_id,
2556 p_party_site_id =>l_party_site_id,
2557 c_attribute_category=>null,
2558 c_attribute1=>null,
2559 c_attribute2=>null,
2560 c_attribute3=>null,
2561 c_attribute4=>null,
2562 c_attribute5=>null,
2563 c_attribute6=>null,
2564 c_attribute7=>null,
2565 c_attribute8=>null,
2566 c_attribute9=>null,
2567 c_attribute10=>null,
2568 c_attribute11=>null,
2569 c_attribute12=>null,
2570 c_attribute13=>null,
2571 c_attribute14=>null,
2572 c_attribute15=>null,
2576 c_attribute19=>null,
2573 c_attribute16=>null,
2574 c_attribute17=>null,
2575 c_attribute18=>null,
2577 c_attribute20=>null,
2578 x_customer_site_id =>l_cust_acct_site_id ,
2579 x_return_status => x_return_status,
2580 x_msg_count => x_msg_count,
2581 x_msg_data => x_msg_data,
2582 in_created_by_module=>G_CREATED_BY_MODULE
2583 ) ;
2584 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2585 CLOSE c_acct_site;
2586 l_return_status := FND_API.G_RET_STS_ERROR;
2587 IF l_debug_level > 0 THEN
2588 oe_debug_pub.add( ' account site creation failed' ) ;
2589 oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2590 END IF;
2591 IF x_msg_count = 1 then
2592
2593 IF l_site_use_code = 'SHIP_TO' then
2594 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2595 ELSIF l_site_use_code = 'BILL_TO' then
2596 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_SITE_CREATION');
2597 ELSIF l_site_use_code = 'DELIVER_TO' then
2598 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVER_SITE_CREATION');
2599 END IF;
2600
2601 FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2602 OE_MSG_PUB.ADD;
2603 ELSE
2604 oe_msg_pub.transfer_msg_stack;
2605 END IF;
2606 IF p_continue_on_error THEN
2607 null;
2608 ELSE
2609 return;
2610 END IF;
2611
2612 ELSE
2613
2614 IF l_debug_level > 0 THEN
2615 oe_debug_pub.add( ' acct_site_id created = '||L_CUST_ACCT_SITE_ID ) ;
2616 END IF;
2617 oe_oe_inline_address.Create_Acct_Site_Uses
2618 (
2619 p_cust_acct_site_id =>l_cust_acct_site_id,
2620 p_location =>null,
2621 p_site_use_code =>l_site_use_code,
2622 x_site_use_id =>l_site_use_id ,
2623 x_return_status => x_return_status,
2624 x_msg_count => x_msg_count,
2625 x_msg_data => x_msg_data,
2626 c_attribute_category=>null,
2627 c_attribute1=>null,
2628 c_attribute2=>null,
2629 c_attribute3=>null,
2630 c_attribute4=>null,
2631 c_attribute5=>null,
2632 c_attribute6=>null,
2633 c_attribute7=>null,
2634 c_attribute8=>null,
2635 c_attribute9=>null,
2636 c_attribute10=>null,
2637 c_attribute11=>null,
2638 c_attribute12=>null,
2639 c_attribute13=>null,
2640 c_attribute14=>null,
2641 c_attribute15=>null,
2642 c_attribute16=>null,
2643 c_attribute17=>null,
2644 c_attribute18=>null,
2645 c_attribute19=>null,
2646 c_attribute20=>null,
2647 c_attribute21=>null,
2648 c_attribute22=>null,
2649 c_attribute23=>null,
2650 c_attribute24=>null,
2651 c_attribute25=>null,
2652 in_created_by_module=>G_CREATED_BY_MODULE,
2653 in_primary_flag =>l_send_primary
2654 );
2655
2656 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2657 l_return_status := FND_API.G_RET_STS_ERROR;
2658 CLOSE c_acct_site;
2659 IF l_debug_level > 0 THEN
2660 oe_debug_pub.add( ' account site use creation failed' ) ;
2661 oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2662 END IF;
2663 IF x_msg_count = 1 then
2664 IF l_debug_level > 0 THEN
2665 oe_debug_pub.add( ' adding to message stack' ) ;
2666 END IF;
2667
2668 IF l_site_use_code = 'SHIP_TO' then
2669 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2670 ELSIF l_site_use_code = 'BILL_TO' then
2671 FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_SITE_CREATION');
2672 ELSIF l_site_use_code = 'DELIVER_TO' then
2673 FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVER_SITE_CREATION');
2674 END IF;
2675
2676 FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2677 OE_MSG_PUB.ADD;
2678 ELSE
2679 IF l_debug_level > 0 THEN
2680 oe_debug_pub.add( ' transferring to message stack' ) ;
2681 END IF;
2682 oe_msg_pub.transfer_msg_stack;
2683 END IF;
2684 IF p_continue_on_error THEN
2685 null;
2686 ELSE
2687 return;
2688 END IF;
2689
2690 END IF;
2691
2692 END IF; -- if acct site creation succeeded
2693
2694 ELSE
2695
2696 x_return_status := FND_API.G_RET_STS_ERROR;
2700 IF l_debug_level > 0 THEN
2697 l_return_status := FND_API.G_RET_STS_ERROR;
2698 FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2699 OE_MSG_PUB.ADD;
2701 oe_debug_pub.add( '2 not allowed to create site' ) ;
2702 END IF;
2703 CLOSE c_acct_site;
2704 IF p_continue_on_error THEN
2705 null;
2706 ELSE
2707 return;
2708 END IF;
2709
2710 END IF; -- if allow site creation
2711
2712 END IF; -- if cursor found
2713
2714 IF c_acct_site%ISOPEN THEN
2715 CLOSE c_acct_site;
2716 END IF;
2717
2718 END IF; -- if status is success
2719
2720 IF l_debug_level > 0 THEN
2721 oe_debug_pub.add( ' assinging site table site_id = '||l_site_use_id|| ' acct site_id = '||L_CUST_ACCT_SITE_ID ) ;
2722 END IF;
2723 END IF; -- if site_use_id is passed
2724
2725 <<end_customer_found>> --added for bug 4240715
2726 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2727
2728
2729
2730 IF l_debug_level > 0 THEN
2731 oe_debug_pub.add( ' site not success so copying null' ) ;
2732 END IF;
2733 p_site_tbl(i).p_site_use_id := null;
2734 p_site_tbl(i).p_cust_acct_site_id := null;
2735 ELSE
2736
2737 OPEN C_get_cust_from_site_use_id(l_site_use_id);
2738 FETCH C_get_cust_from_site_use_id
2739 INTO p_site_tbl(i).p_cust_account_id;
2740
2741 IF C_get_cust_from_site_use_id%FOUND THEN
2742
2743 IF l_debug_level > 0 THEN
2744 oe_debug_pub.add( ' account site found = '||p_site_tbl(i).p_cust_account_id ) ;
2745 END IF;
2746 END IF;
2747 close C_get_cust_from_site_use_id;
2748
2749 IF l_debug_level > 0 THEN
2750 oe_debug_pub.add( ' site success so copying actual' ) ;
2751 END IF;
2752
2753 p_site_tbl(i).p_site_use_id := l_site_use_id;
2754 p_site_tbl(i).p_cust_acct_site_id := l_cust_acct_site_id;
2755
2756 END IF;
2757
2758 <<skip_site>>
2759 null;
2760
2761 END LOOP;
2762
2763
2764 x_return_status := l_return_status;
2765
2766 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2767 IF l_debug_level > 0 THEN
2768 oe_debug_pub.add( ' doing count and get for site' ) ;
2769 END IF;
2770 OE_MSG_PUB.Count_And_Get
2771 ( p_count => x_msg_count
2772 , p_data => x_msg_data
2773 );
2774 END IF;
2775 IF l_debug_level > 0 THEN
2776 oe_debug_pub.add( ' at end of site msg = '||x_msg_data|| ' count = '||x_MSG_COUNT ) ;
2777 END IF;
2778
2779 EXCEPTION
2780
2781 WHEN FND_API.G_EXC_ERROR THEN
2782
2783 IF c_party_site_use%ISOPEN THEN
2784 CLOSE c_party_site_use;
2785 END IF;
2786 IF c_party_site%ISOPEN THEN
2787 CLOSE c_party_site;
2788 END IF;
2789 IF c_acct_site%ISOPEN THEN
2790 CLOSE c_acct_site;
2791 END IF;
2792 x_return_status := FND_API.G_RET_STS_ERROR;
2793
2794 -- Get message count and data
2795
2796 OE_MSG_PUB.Count_And_Get
2797 ( p_count => x_msg_count
2798 , p_data => x_msg_data
2799 );
2800
2801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2802
2803
2804 IF c_party_site_use%ISOPEN THEN
2805 CLOSE c_party_site_use;
2806 END IF;
2807 IF c_party_site%ISOPEN THEN
2808 CLOSE c_party_site;
2809 END IF;
2810 IF c_acct_site%ISOPEN THEN
2811 CLOSE c_acct_site;
2812 END IF;
2813 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2814
2815 -- Get message count and data
2816
2817 OE_MSG_PUB.Count_And_Get
2818 ( p_count => x_msg_count
2819 , p_data => x_msg_data
2820 );
2821
2822 WHEN OTHERS THEN
2823
2824
2825 IF c_party_site_use%ISOPEN THEN
2826 CLOSE c_party_site_use;
2827 END IF;
2828 IF c_party_site%ISOPEN THEN
2829 CLOSE c_party_site;
2830 END IF;
2831 IF c_acct_site%ISOPEN THEN
2832 CLOSE c_acct_site;
2833 END IF;
2834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2835 IF l_debug_level > 0 THEN
2836 oe_debug_pub.add( 'check_and_create_site when other code = '|| sqlcode||' message = '||sqlerrm ) ;
2837 END IF;
2838
2839 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2840 THEN
2841 OE_MSG_PUB.Add_Exc_Msg
2842 ( G_PKG_NAME
2843 , 'Check_and_create_sites'
2844 );
2845 END IF;
2846
2847 -- Get message count and data
2848
2849 OE_MSG_PUB.Count_And_Get
2850 ( p_count => x_msg_count
2851 , p_data => x_msg_data
2852 );
2853
2854
2855 END Check_and_Create_Sites;
2859
2856
2857
2858
2860 PROCEDURE find_contact(
2861 in_party_id in number
2862 ,out_org_contact_id out NOCOPY number
2863 ,out_cust_account_role_id out NOCOPY number
2864 ) IS
2865
2866 --
2867 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
2868 --
2869 BEGIN
2870
2871 null;
2872
2873 END find_contact;
2874
2875
2876 PROCEDURE Check_and_Create_Contact(
2877 p_party_id in number -- used to see if org_contact belongs to this party
2878 ,p_cust_account_id in number --acct from ct is in account table
2879 ,p_org_contact_id in number
2880 ,p_site_use_code in varchar2
2881 ,p_allow_contact_creation in boolean
2882 ,p_create_responsibility in boolean
2883 ,p_cust_account_role_id in out NOCOPY /* file.sql.39 change */ number
2884 ,p_cust_account_site_id in number
2885 ,p_assign_contact_to_site in boolean
2886 ,p_multiple_account_is_error in boolean
2887 ,p_multiple_contact_is_error in boolean
2888 ,p_contact_tbl out NOCOPY contact_tbl
2889 ,p_multiple_account out NOCOPY boolean
2890 ,x_return_status OUT NOCOPY VARCHAR2
2891 ,x_msg_count OUT NOCOPY NUMBER
2892 ,x_msg_data OUT NOCOPY VARCHAR2
2893 ) IS
2894
2895
2896 CURSOR c_get_cust_id(in_cust_account_role_id in number) IS
2897 SELECT cust_account_id
2898 FROM hz_cust_account_roles
2899 where cust_account_role_id = in_cust_account_role_id;
2900
2901 CURSOR c_cust_role_site( in_cust_account_role_id in number) IS
2902 SELECT status,
2903 role_type,
2904 cust_account_id,
2905 cust_acct_site_id
2906 FROM hz_cust_account_roles
2907 where cust_account_role_id = in_cust_account_role_id;
2908
2909 CURSOR c_cust_account_role( in_cust_account_role_id in number) IS
2910 SELECT cust_acct_site_id,
2911 status,
2912 role_type,
2913 cust_account_id
2914 FROM hz_cust_account_roles
2915 where cust_account_role_id = in_cust_account_role_id;
2916
2917 CURSOR c_org_contact(in_org_contact_id in number,
2918 in_cust_account_id in number) IS
2919 SELECT role.cust_account_role_id,
2920 role.cust_acct_site_id
2921 FROM hz_org_contacts org,
2922 hz_relationships rel,
2923 hz_cust_accounts acct,
2924 hz_cust_account_roles role
2925 WHERE org.org_Contact_id = in_org_contact_id
2926 AND org.party_relationship_id = rel.relationship_id
2927 AND rel.object_id=acct.party_id
2928 AND rel.subject_table_name='HZ_PARTIES'
2929 AND rel.object_table_name='HZ_PARTIES'
2930 AND acct.cust_account_id = in_cust_account_id
2931 and role.cust_account_id = acct.cust_account_id
2932 and role.role_type ='CONTACT'
2933 and role.party_id = rel.party_id
2934 and acct.status = 'A'
2935 and role.status = 'A'
2936 and rel.status = 'A';
2937
2938
2939 CURSOR C_get_cust_id_from_party_id(l_Party_Id NUMBER) IS
2940 SELECT cust_account_id,account_number
2941 FROM hz_cust_accounts
2942 WHERE party_id = l_Party_Id
2943 and status = 'A';
2944
2945 CURSOR c_check_org_contact(in_party_id in number,in_org_contact_id in number) IS
2946 SELECT rel.party_id
2947 FROM hz_org_contacts org,
2948 hz_relationships rel
2949 WHERE org.org_contact_id = in_org_contact_id
2950 AND rel.status = 'A'
2951 AND rel.relationship_id = org.party_relationship_id
2952 AND (rel.object_id=in_party_id OR rel.subject_id=in_party_id)
2953 AND rel.subject_table_name='HZ_PARTIES'
2954 AND rel.object_table_name='HZ_PARTIES';
2955 --AND org.status='A'; -- bug 3810361, TCA USAGE GUIDE SAYS THIS IS NOT SUPPORTED IN V2 API, We should instead look at hz_relationships.status
2956
2957 l_party_id NUMBER := null;
2958 l_org_contact_id number := null;
2959 l_rel_party_id varchar2(100);
2960 l_status varchar2(100);
2961 l_cust_role_site_id number := null;
2962 l_role_type varchar2(100);
2963 l_cust_account_id number := null;
2964 l_account_number varchar2(30);
2965 l_cust_account_role_id number := null;
2966 l_cust_acct_site_id number := null;
2967 l_multiple_contact boolean := FALSE;
2968 l_return_status varchar2(10);
2969 l_msg_data varchar2(4000);
2970 l_msg_count number;
2971 l_message varchar2(4000);
2972 px_party_id number := null;
2973 px_cust_account_id number := null;
2974
2975 --
2976 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
2977 l_indent varchar2(5) := ' ';
2978 --
2979 BEGIN
2980 l_debug_level:=1;
2981 IF l_debug_level > 0 THEN
2982 oe_debug_pub.add( 'entering check_and_create_contact ');
2983 oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2984 oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2985 oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2986 p_cust_account_site_id ) ;
2987 END IF;
2988
2989 IF l_debug_level > 0 THEN
2990 if p_multiple_contact_is_error then
2991 oe_debug_pub.add(l_indent|| 'multiple contact is error' ) ;
2992 else
2993 oe_debug_pub.add( l_indent|| 'multiple contact is not error' ) ;
2997 oe_debug_pub.add( l_indent|| 'assign_contact_to_site ' ) ;
2994 end if;
2995
2996 if p_assign_contact_to_site then
2998 else
2999 oe_debug_pub.add(l_indent|| 'not assign_contact_to_site ' ) ;
3000 end if;
3001
3002 if p_create_responsibility then
3003 oe_debug_pub.add(l_indent|| 'create responsibility ' ) ;
3004 else
3005 oe_debug_pub.add(l_indent|| 'not create responsibility ' ) ;
3006 end if;
3007
3008 if p_allow_contact_creation then
3009 oe_debug_pub.add( l_indent|| 'allow_contact_creation ' ) ;
3010 else
3011 oe_debug_pub.add( l_indent|| 'not allow_contact_creation ' ) ;
3012 END IF;
3013 END IF;
3014
3015
3016 x_return_status := FND_API.G_RET_STS_SUCCESS;
3017 p_contact_tbl.DELETE;
3018 p_multiple_account := FALSE;
3019
3020
3021 -- atleast some kind of account information is needed
3022 IF p_cust_account_id is null AND
3023 p_cust_account_role_id is null then
3024 x_return_status := FND_API.G_RET_STS_ERROR;
3025 IF l_debug_level > 0 THEN
3026 oe_debug_pub.add(l_indent|| ' not a valid call to check contact' ) ;
3027 END IF;
3028 return;
3029
3030 END IF;
3031
3032 -- getting the cust account id
3033 IF p_cust_account_id is null AND
3034 p_cust_account_role_id is not null then
3035
3036 IF l_debug_level > 0 THEN
3037 oe_debug_pub.add( l_indent||' getting account from contact information' ) ;
3038 end IF;
3039 OPEN c_get_cust_id(p_cust_account_role_id);
3040 FETCH c_get_cust_id
3041 INTO l_cust_account_id;
3042
3043 IF c_get_cust_id%NOTFOUND THEN
3044 x_return_status := FND_API.G_RET_STS_ERROR;
3045 IF l_debug_level > 0 THEN
3046 oe_debug_pub.add( l_indent|| 'not a valid cust account role id' ) ;
3047 END IF;
3048 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3049 OE_MSG_PUB.ADD;
3050
3051 CLOSE c_get_cust_id;
3052 OE_MSG_PUB.Count_And_Get
3053 ( p_count => x_msg_count
3054 , p_data => x_msg_data
3055 );
3056
3057 return;
3058
3059 END IF;
3060
3061 CLOSE c_get_cust_id;
3062 END IF;
3063
3064 -- we get cust_account_id from role_id if found above
3065 IF l_cust_account_id is null then
3066 l_cust_account_id := p_cust_account_id;
3067 END IF;
3068
3069 IF l_debug_level > 0 THEN
3070 oe_debug_pub.add( l_indent|| 'cust_account_id = '||L_CUST_ACCOUNT_ID ) ;
3071 END IF;
3072
3073 p_contact_tbl.DELETE;
3074 IF l_cust_account_id is not null then
3075
3076 IF p_cust_account_role_id is not null then
3077
3078 -- if we need to assign the contact to a site, then first we check
3079 -- if it is already attached to that site
3080 IF p_assign_contact_to_site and p_cust_account_site_id is not null then
3081 IF l_debug_level > 0 THEN
3082 oe_debug_pub.add(l_indent|| 'need to assign contact to site' ) ;
3083 END IF;
3084 OPEN c_cust_role_site( p_cust_account_role_id);
3085 FETCH c_cust_role_site
3086 INTO l_status,
3087 l_role_type,
3088 l_cust_account_id,
3089 l_cust_role_site_id;
3090
3091
3092 IF c_cust_role_site%NOTFOUND THEN
3093 IF l_debug_level > 0 THEN
3094 oe_debug_pub.add( l_indent|| 'site contact not found' ) ;
3095 END IF;
3096 CLOSE c_cust_role_site;
3097
3098 IF p_create_responsibility then
3099 --create acct contact and attach site and pass the responsibility
3100 IF l_debug_level > 0 THEN
3101 oe_debug_pub.add( l_indent|| ' 1 not supported currently' ) ;
3102 end IF;
3103 ELSE
3104 --create acct contact and attach site and DO NOT pass responsibility
3105 IF l_debug_level > 0 THEN
3106 oe_debug_pub.add( l_indent|| ' 2 not supported currently' ) ;
3107 END IF;
3108 END IF;
3109
3110 ELSIF c_cust_role_site%FOUND THEN
3111
3112 IF l_debug_level > 0 THEN
3113 oe_debug_pub.add(l_indent|| 'site contact found' ) ;
3114 END IF;
3115 IF l_status <> 'A' then
3116 IF l_debug_level > 0 THEN
3117 oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3118 END IF;
3119 CLOSE c_cust_role_site;
3120 x_return_status := FND_API.G_RET_STS_ERROR;
3121 return;
3122
3123 END IF;
3124
3125 IF l_role_type <> 'CONTACT' then
3126 IF l_debug_level > 0 THEN
3127 oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3128 END IF;
3129 CLOSE c_cust_role_site;
3130 x_return_status := FND_API.G_RET_STS_ERROR;
3131 return;
3132
3133 END IF;
3134
3135 IF l_cust_account_id <> p_cust_account_id then
3136 IF l_debug_level > 0 THEN
3137 oe_debug_pub.add(l_indent|| 'acct role does not belong to this acct' ) ;
3138 END IF;
3142 END IF;
3139 CLOSE c_cust_role_site;
3140 x_return_status := FND_API.G_RET_STS_ERROR;
3141 return;
3143
3144
3145 IF l_cust_role_site_id <> p_cust_account_site_id then
3146 IF l_debug_level > 0 THEN
3147 oe_debug_pub.add( l_indent|| 'Cust account role for site is not valid' ) ;
3148 END IF;
3149 CLOSE c_cust_role_site;
3150 x_return_status := FND_API.G_RET_STS_ERROR;
3151 return;
3152
3153 END IF;
3154
3155
3156 IF l_debug_level > 0 THEN
3157 oe_debug_pub.add(l_indent|| 'found the contact attached to account site' ) ;
3158 END IF;
3159 CLOSE c_cust_role_site;
3160 p_contact_tbl(1) :=p_cust_account_role_id;
3161 return;
3162
3163 END IF;
3164 CLOSE c_cust_role_site;
3165
3166 ELSE -- if the contact is not to be assigned to a site
3167
3168 OPEN c_cust_account_role(p_cust_account_role_id);
3169 FETCH c_cust_account_role
3170 INTO l_cust_role_site_id,
3171 l_status,
3172 l_role_type,
3173 l_cust_account_id;
3174
3175 IF c_cust_account_role%NOTFOUND then
3176
3177 CLOSE c_cust_account_role;
3178
3179 x_return_status := FND_API.G_RET_STS_ERROR;
3180 IF l_debug_level > 0 THEN
3181 oe_debug_pub.add(l_indent|| '2 contact_id not found' ) ;
3182 END IF;
3183 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3184 OE_MSG_PUB.ADD;
3185 OE_MSG_PUB.Count_And_Get
3186 ( p_count => x_msg_count
3187 , p_data => x_msg_data
3188 );
3189 return;
3190
3191 ELSIF c_cust_account_role%FOUND then
3192
3193 IF l_status <> 'A' then
3194 IF l_debug_level > 0 THEN
3195 oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3196 END IF;
3197 CLOSE c_cust_account_role;
3198 x_return_status := FND_API.G_RET_STS_ERROR;
3199 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INACTIVE_CONTACT');
3200 OE_MSG_PUB.ADD;
3201 OE_MSG_PUB.Count_And_Get
3202 ( p_count => x_msg_count
3203 , p_data => x_msg_data
3204 );
3205 return;
3206
3207 END IF;
3208
3209 IF l_role_type <> 'CONTACT' then
3210 IF l_debug_level > 0 THEN
3211 oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3212 END IF;
3213 CLOSE c_cust_account_role;
3214 x_return_status := FND_API.G_RET_STS_ERROR;
3215 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ROLE_CONTACT');
3216 OE_MSG_PUB.ADD;
3217 OE_MSG_PUB.Count_And_Get
3218 ( p_count => x_msg_count
3219 , p_data => x_msg_data
3220 );
3221 return;
3222
3223 END IF;
3224
3225
3226 IF l_cust_account_id <> p_cust_account_id then
3227 IF l_debug_level > 0 THEN
3228 oe_debug_pub.add( l_indent|| 'Account role does not belong to this acct' ) ;
3229 END IF;
3230 CLOSE c_cust_account_role;
3231 x_return_status := FND_API.G_RET_STS_ERROR;
3232 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_ACCOUNT');
3233 OE_MSG_PUB.ADD;
3234 OE_MSG_PUB.Count_And_Get
3235 ( p_count => x_msg_count
3236 , p_data => x_msg_data
3237 );
3238 return;
3239 END IF;
3240
3241 IF p_create_responsibility then
3242 --check if resp exists. if not then create one
3243 IF l_debug_level > 0 THEN
3244 oe_debug_pub.add( l_indent|| ' 1 not supported currently' ) ;
3245 end if;
3246 END IF;
3247
3248 IF l_debug_level > 0 THEN
3249 oe_debug_pub.add( l_indent|| 'found contact attached to account returning' ) ;
3250 END IF;
3251 CLOSE c_cust_account_role;
3252 p_contact_tbl(1) :=p_cust_account_role_id;
3253 return;
3254 END IF; -- if cust_account_role found
3255
3256 CLOSE c_cust_account_role;
3257
3258 END IF; -- if contact is to be assigned to the site
3259
3260
3261 ELSIF p_org_Contact_id is not null then -- if acct contact not passed
3262
3263 -- check to see if the incoming party_id is atleast the object or subject
3264 OPEN c_check_org_contact(p_party_id,p_org_contact_id);
3265 FETCH c_check_org_contact
3266 INTO l_rel_party_id;
3267
3268 IF c_check_org_contact%NOTFOUND then
3269 IF l_debug_level > 0 THEN
3270 oe_debug_pub.add(l_indent|| 'org contact_id does not belong to sent party or '|| ' contact may be inactive' ) ;
3271 END IF;
3272 x_return_status := FND_API.G_RET_STS_ERROR;
3276 OE_MSG_PUB.Count_And_Get
3273 CLOSE c_check_org_contact;
3274 FND_MESSAGE.Set_Name('ONT','ONT_AAC_CONTACT_ACCOUNT');
3275 OE_MSG_PUB.ADD;
3277 ( p_count => x_msg_count
3278 , p_data => x_msg_data
3279 );
3280 IF l_debug_level > 0 THEN
3281 oe_debug_pub.add(l_indent|| ' contact error msg = '||x_msg_data|| '; count = '||x_MSG_COUNT ) ;
3282 END IF;
3283
3284 return;
3285 END IF;
3286 CLOSE c_check_org_contact;
3287
3288 IF l_debug_level > 0 THEN
3289 oe_debug_pub.add(l_indent|| 'checking for value of org_contact_id' ) ;
3290 END IF;
3291 -- derive customer account
3292 OPEN c_org_contact(p_org_Contact_id,
3293 l_cust_account_id );
3294 LOOP
3295 --oe_debug_pub.add('Inside get_cust_role_id loop');
3296 FETCH c_org_contact
3297 INTO l_cust_account_role_id,
3298 l_cust_acct_site_id;
3299 EXIT WHEN C_org_contact%NOTFOUND;
3300
3301 IF l_debug_level > 0 THEN
3302 oe_debug_pub.add(l_indent|| 'acct_role_id = '||l_cust_account_role_id|| ' cust_acct_site_id = '||L_CUST_ACCT_SITE_ID ) ;
3303 END IF;
3304
3305 p_contact_tbl(p_contact_tbl.COUNT + 1):= l_cust_account_role_id;
3306
3307 END LOOP;
3308
3309 CLOSE c_org_contact;
3310
3311
3312 -- IF we are not assigning this contact to a particular site
3313 -- or a particular responsibility then multiple records is an error
3314 -- Else we try to match multiple records for that specific type
3315 IF NOT p_assign_contact_to_site AND
3316 NOT p_create_responsibility then
3317
3318 IF p_multiple_contact_is_error THEN
3319
3320 IF p_contact_tbl.COUNT > 1 then
3321 IF l_debug_level > 0 THEN
3322 oe_debug_pub.add(l_indent|| 'error >1 contact for org_contact_id' ) ;
3323 END IF;
3324 x_return_status := FND_API.G_RET_STS_ERROR;
3325 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3326 FND_MESSAGE.Set_Token('TEXT',' Multiple Contacts ', FALSE);
3327 OE_MSG_PUB.ADD;
3328 OE_MSG_PUB.Count_And_Get
3329 ( p_count => x_msg_count
3330 , p_data => x_msg_data
3331 );
3332 return;
3333 END IF;
3334 END IF; -- if multiple contact is error
3335
3336
3337 IF p_contact_tbl.COUNT = 1 then
3338 IF l_debug_level > 0 THEN
3339 oe_debug_pub.add( l_indent|| 'RETURNING WITH ONE CONTACT' ) ;
3340 END IF;
3341 x_return_status := FND_API.G_RET_STS_SUCCESS;
3342 return;
3343 ELSIF p_contact_tbl.COUNT = 0 then
3344 IF l_debug_level > 0 THEN
3345 oe_debug_pub.add( l_indent|| 'no contact for account found. creating '|| ' acct_id = '||p_cust_account_id|| ' rel_party_id = '||L_REL_PARTY_ID ) ;
3346 END IF;
3347
3348 IF p_allow_contact_creation THEN
3349
3350 oe_oe_inline_address.Create_acct_contact
3351 (
3352 p_acct_id=>p_cust_account_id,
3353 p_contact_party_id=>l_rel_party_id,
3354 x_return_status=>l_return_status,
3355 x_msg_count=>l_msg_count,
3356 x_msg_data=>l_msg_data,
3357 x_contact_id=>p_contact_tbl(1),
3358 c_attribute_category=>null,
3359 c_attribute1=>null,
3360 c_attribute2=>null,
3361 c_attribute3=>null,
3362 c_attribute4=>null,
3363 c_attribute5=>null,
3364 c_attribute6=>null,
3365 c_attribute7=>null,
3366 c_attribute8=>null,
3367 c_attribute9=>null,
3368 c_attribute10=>null,
3369 c_attribute11=>null,
3370 c_attribute12=>null,
3371 c_attribute13=>null,
3372 c_attribute14=>null,
3373 c_attribute15=>null,
3374 c_attribute16=>null,
3375 c_attribute17=>null,
3376 c_attribute18=>null,
3377 c_attribute19=>null,
3378 c_attribute20=>null,
3379 c_attribute21=>null,
3380 c_attribute22=>null,
3381 c_attribute23=>null,
3382 c_attribute24=>null,
3383 c_attribute25=>null,
3384 c2_attribute_category=>null,
3385 c2_attribute1=>null,
3386 c2_attribute2=>null,
3387 c2_attribute3=>null,
3388 c2_attribute4=>null,
3389 c2_attribute5=>null,
3390 c2_attribute6=>null,
3391 c2_attribute7=>null,
3392 c2_attribute8=>null,
3393 c2_attribute9=>null,
3394 c2_attribute10=>null,
3395 c2_attribute11=>null,
3396 c2_attribute12=>null,
3397 c2_attribute13=>null,
3398 c2_attribute14=>null,
3399 c2_attribute15=>null,
3400 c2_attribute16=>null,
3401 c2_attribute17=>null,
3405 in_Created_by_module=>G_CREATED_BY_MODULE
3402 c2_attribute18=>null,
3403 c2_attribute19=>null,
3404 c2_attribute20=>null,
3406 );
3407
3408 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3409
3410 x_return_status := l_return_status;
3411 x_msg_data := l_msg_data;
3412 x_msg_count := l_msg_count;
3413 IF l_debug_level > 0 THEN
3414 oe_debug_pub.add(l_indent|| ' contact creation failed' ) ;
3415 oe_debug_pub.add( l_indent|| ' error = '||X_MSG_DATA ) ;
3416 END IF;
3417
3418 IF x_msg_count = 1 then
3419 FND_MESSAGE.Set_Name('ONT','ONT_AAC_CONTACT_CREATION');
3420 FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
3421 OE_MSG_PUB.ADD;
3422 ELSE
3423 oe_msg_pub.transfer_msg_stack;
3424 END IF;
3425
3426 OE_MSG_PUB.Count_And_Get
3427 ( p_count => x_msg_count
3428 , p_data => x_msg_data
3429 );
3430 return;
3431 ELSE
3432 x_return_status := FND_API.G_RET_STS_SUCCESS;
3433 IF l_debug_level > 0 THEN
3434 oe_debug_pub.add( l_indent|| ' contact creation succeeded '|| ' cust acct_role_id = '||P_CONTACT_TBL ( 1 ) ) ;
3435 END IF;
3436
3437 END IF;
3438 ELSE
3439 x_return_status := FND_API.G_RET_STS_ERROR;
3440 IF l_debug_level > 0 THEN
3441 oe_debug_pub.add( l_indent|| 'not allowed to create contact' ) ;
3442 end IF;
3443 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_PERMISSION');
3444 OE_MSG_PUB.ADD;
3445 OE_MSG_PUB.Count_And_Get
3446 ( p_count => x_msg_count
3447 , p_data => x_msg_data
3448 );
3449 return;
3450
3451 END IF; -- if permission to create contact
3452
3453 END IF; -- contact table count
3454
3455
3456 ELSIF (p_assign_contact_to_site AND
3457 p_cust_account_site_id is not null) OR
3458 (p_create_responsibility AND p_site_use_code is not null) then
3459
3460 -- if zero then create the account contact appropiately
3461 -- take care of account site and responsibility
3462 IF p_contact_tbl.COUNT = 0 then
3463 IF l_debug_level > 0 THEN
3464 oe_debug_pub.add( l_indent|| 'equal to ZERO' ) ;
3465 END IF;
3466 x_return_status := FND_API.G_RET_STS_SUCCESS;
3467 return;
3468
3469 -- if one or more then check the account contact appropiately
3470 -- take care of account site and responsibility
3471 -- and create if not present
3472 ELSIF p_Contact_tbl.COUNT >0 then
3473 IF l_debug_level > 0 THEN
3474 oe_debug_pub.add( l_indent|| 'one or more than one' ) ;
3475 END IF;
3476 x_return_status := FND_API.G_RET_STS_SUCCESS;
3477 return;
3478 END IF;
3479
3480 END IF; -- if not to be assigned
3481
3482 IF l_debug_level > 0 THEN
3483 oe_debug_pub.add(l_indent|| 'cust_acct_role_id = '||L_CUST_ACCOUNT_ROLE_ID ) ;
3484 END IF;
3485
3486 ELSE
3487 IF l_debug_level > 0 THEN
3488 oe_debug_pub.add(l_indent|| 'error no contact information is passed' ) ;
3489 END IF;
3490 return;
3491 END IF; -- if p_cust_account_role_id is not null
3492
3493 ELSE -- if cust_account_id is not passed
3494 IF l_debug_level > 0 THEN
3495 oe_debug_pub.add(l_indent|| 'this procedure expects the account_id to be passed' ) ;
3496 END IF;
3497 return;
3498
3499 END IF; -- if cust_account_id is not null
3500
3501
3502 EXCEPTION
3503
3504 WHEN FND_API.G_EXC_ERROR THEN
3505
3506 x_return_status := FND_API.G_RET_STS_ERROR;
3507
3508 -- Get message count and data
3509
3510 OE_MSG_PUB.Count_And_Get
3511 ( p_count => x_msg_count
3512 , p_data => x_msg_data
3513 );
3514
3515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3516
3517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3518
3519 -- Get message count and data
3520
3521 OE_MSG_PUB.Count_And_Get
3522 ( p_count => x_msg_count
3523 , p_data => x_msg_data
3524 );
3525
3526 WHEN OTHERS THEN
3527
3528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3529 IF l_debug_level > 0 THEN
3530 oe_debug_pub.add( 'CHECK_CONTACT WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
3531 END IF;
3532
3533 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3534 THEN
3535 OE_MSG_PUB.Add_Exc_Msg
3536 ( G_PKG_NAME
3540
3537 , 'check_and_create_contact'
3538 );
3539 END IF;
3541 -- Get message count and data
3542
3543 OE_MSG_PUB.Count_And_Get
3544 ( p_count => x_msg_count
3545 , p_data => x_msg_data
3546 );
3547
3548
3549 END check_and_create_Contact;
3550
3551
3552
3553 PROCEDURE Check_and_Create_Account(
3554 p_party_id in number
3555 ,p_party_number in varchar2
3556 ,p_allow_account_creation in boolean
3557 ,p_multiple_account_is_error in boolean
3558 ,p_account_tbl out NOCOPY account_tbl
3559 ,p_out_org_contact_id out NOCOPY number
3560 ,p_out_cust_account_role_id out NOCOPY number
3561 ,x_return_status OUT NOCOPY VARCHAR2
3562 ,x_msg_count OUT NOCOPY NUMBER
3563 ,x_msg_data OUT NOCOPY VARCHAR2
3564 ,p_site_tbl_count IN number
3565 ,p_return_if_only_party in boolean
3566 ) IS
3567
3568 CURSOR C_get_cust_id_from_party_id(l_Party_Id NUMBER) IS
3569 SELECT cust_account_id,
3570 account_number
3571 FROM hz_cust_accounts
3572 WHERE party_id = l_Party_Id
3573 and status = 'A';
3574
3575 CURSOR party_rec(l_party_id in number) IS
3576 select party_type
3577 from hz_parties
3578 where party_id = l_party_id;
3579
3580 CURSOR party_number_rec IS
3581 select party_id,party_type
3582 from hz_parties
3583 where party_id = p_party_number;
3584
3585
3586 l_cust_account_id NUMBER := NULL;
3587 l_return_status VARCHAR2(1);
3588 l_party_type VARCHAR2(30);
3589 l_object_party_id NUMBER;
3590 l_party_id NUMBER;
3591 l_message varchar2(300);
3592 l_account_number varchar2(30);
3593 p_multiple_account boolean := FALSE;
3594 l_org_contact_Id number;
3595 l_cust_account_role_id number;
3596 l_msg_count Number;
3597 l_msg_data Varchar2(4000);
3598 x_party_id number;
3599 x_party_number varchar2(30);
3600 x_cust_Account_id number;
3601 x_cust_account_number varchar2(30);
3602 l_found boolean := FALSE;
3603
3604 --
3605 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
3606 l_indent varchar2(5) := ' ';
3607 i number := 0;
3608
3609 --
3610 BEGIN
3611
3612 x_return_status := FND_API.G_RET_STS_SUCCESS;
3613 p_account_tbl.DELETE;
3614
3615 IF l_debug_level > 0 THEN
3616 oe_debug_pub.add(l_indent||'party_id = '||p_party_id|| '; party_number = '||p_party_number);
3617 oe_debug_pub.add(l_indent||'site tbl count = '||p_site_tbl_count ) ;
3618 END IF;
3619
3620 if p_allow_account_creation then
3621 IF l_debug_level > 0 THEN
3622 oe_debug_pub.add( l_indent|| ' allowed account creation' ) ;
3623 END IF;
3624 end if;
3625
3626 if p_multiple_account_is_error then
3627 IF l_debug_level > 0 THEN
3628 oe_debug_pub.add(l_indent|| ' multiple account is error' ) ;
3629 END IF;
3630 end if;
3631
3632 if p_return_if_only_party then
3633 IF l_debug_level > 0 THEN
3634 oe_debug_pub.add(l_indent|| ' return_if_only_party' ) ;
3635 END IF;
3636 end if;
3637
3638
3639 -- if both party_id and party_number information is not provided then
3640 -- then we raise an error
3641 IF p_party_id is not null or p_party_number is not null then
3642
3643 -- we will ignore party_number if party_id is passed
3644 IF p_party_id is null and p_party_number is not null then
3645 OPEN party_number_rec;
3646 FETCH party_number_rec
3647 INTO l_party_id,
3648 l_party_type;
3649
3650 IF party_number_rec%NOTFOUND THEN
3651 x_return_status := FND_API.G_RET_STS_ERROR;
3652 IF l_debug_level > 0 THEN
3653 oe_debug_pub.add(l_indent|| 'no such party found for party_number' ) ;
3654 END IF;
3655 CLOSE party_number_rec;
3656 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_PARTY');
3657 OE_MSG_PUB.ADD;
3658 return;
3659 END IF;
3660
3661 CLOSE party_number_rec;
3662 ELSE
3663 l_party_id := p_party_id;
3664 OPEN party_rec(l_party_id);
3665 FETCH party_rec
3666 INTO l_party_type;
3667
3668 IF party_rec%NOTFOUND THEN
3669 x_return_status := FND_API.G_RET_STS_ERROR;
3670 IF l_debug_level > 0 THEN
3671 oe_debug_pub.add(l_indent|| 'no such party found for party_id' ) ;
3672 END IF;
3673 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_PARTY');
3674 OE_MSG_PUB.ADD;
3675 CLOSE party_rec;
3676 return;
3677 END IF;
3678
3679 CLOSE party_rec;
3680
3681 END IF;
3682
3683 IF l_debug_level > 0 THEN
3684 oe_debug_pub.add(l_indent|| 'party type for sold_to = '|| L_PARTY_TYPE ) ;
3685 END IF;
3686 IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
3687
3688 IF l_debug_level > 0 THEN
3692 OPEN C_get_cust_id_from_party_id(l_Party_Id);
3689 oe_debug_pub.add(l_indent|| 'party type = '||l_party_type|| '; party_id = '||l_PARTY_ID ) ;
3690 END IF;
3691 -- derive customer account
3693 LOOP
3694 IF l_debug_level > 0 THEN
3695 i := i+1;
3696 oe_debug_pub.add ('AAC:VTI: ===GET_CUST_LOOP_'||i||'===== ');
3697 oe_debug_pub.add(l_indent|| 'inside get_cust_id loop :X#'||i) ;
3698 END IF;
3699
3700 FETCH C_get_cust_id_from_party_id
3701 INTO l_cust_account_id,
3702 l_account_number;
3703 EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
3704
3705 IF l_debug_level > 0 THEN
3706 oe_debug_pub.add( l_indent|| 'acct_id = '||l_cust_account_id|| '; account number = '||l_ACCOUNT_NUMBER ) ;
3707 END IF;
3708
3709 IF l_debug_level > 0 THEN
3710 oe_debug_pub.add( l_indent|| 'row count get_cust_id = '|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
3711 END IF;
3712
3713 IF p_multiple_account_is_error THEN
3714 IF C_get_cust_id_from_party_id%ROWCOUNT > 1 then
3715 IF l_debug_level > 0 THEN
3716 oe_debug_pub.add( l_indent|| 'more than one account for party id' ) ;
3717 END IF;
3718 x_return_status := FND_API.G_RET_STS_ERROR;
3719 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3720 FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
3721 OE_MSG_PUB.ADD;
3722 EXIT;
3723 END IF;
3724 end if;
3725 IF l_debug_level > 0 THEN
3726 oe_debug_pub.add(l_indent|| 'adding to account tbl id = '||L_CUST_ACCOUNT_ID ) ;
3727 END IF;
3728 p_account_tbl(p_account_tbl.COUNT + 1):= l_cust_account_id;
3729
3730
3731 END LOOP;
3732
3733 -- if multiple accounts are detected then do not proceed further as
3734 -- contacts and sites needs to be created once an account is selected
3735 IF p_account_tbl.COUNT > 1 then
3736 IF l_debug_level > 0 THEN
3737 oe_debug_pub.add( l_indent|| 'multiple accounts found' ) ;
3738 end IF;
3739 p_multiple_account := TRUE;
3740 END IF;
3741
3742 CLOSE C_get_cust_id_from_party_id;
3743
3744 IF l_debug_level > 0 THEN
3745 oe_debug_pub.add( l_indent||'cust acct id for sold_to = '|| L_CUST_ACCOUNT_ID ) ;
3746 END IF;
3747
3748
3749 ELSE -- if not person or organization
3750
3751 x_return_status := FND_API.G_RET_STS_ERROR;
3752 IF l_debug_level > 0 THEN
3753 oe_debug_pub.add(l_indent|| ' invalid party type' ) ;
3754 end IF;
3755 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_PARTY');
3756 OE_MSG_PUB.ADD;
3757
3758 END IF; -- party_type
3759
3760 IF p_account_tbl.COUNT > 0 then
3761 FOR i in p_account_tbl.FIRST..p_account_tbl.LAST
3762 LOOP
3763 IF l_debug_level > 0 THEN
3764 oe_debug_pub.add( l_indent|| 'acct_id = '||P_ACCOUNT_TBL ( I ) ) ;
3765 END IF;
3766 END LOOP;
3767 END IF;
3768
3769 IF l_debug_level > 0 THEN
3770 oe_debug_pub.add(l_indent|| 'before checking to create cust account' ) ;
3771 END IF;
3772 -- create customer account
3773 IF x_return_status <> FND_API.G_RET_STS_ERROR AND
3774 NOT p_multiple_account AND
3775 l_cust_account_id IS NULL THEN
3776
3777 IF p_allow_account_creation THEN
3778
3779 IF p_site_tbl_count = 0 AND p_return_if_only_party THEN
3780
3781 -- We will not process even the Contact information
3782 -- as we will not have account information
3783 -- Even if party level contact is passed then
3784 -- user should select it in the Add Customer form
3785 IF l_debug_level > 0 THEN
3786 oe_debug_pub.add( l_indent|| ' going to call add customer' ) ;
3787 END IF;
3788 x_return_status := FND_API.G_RET_STS_SUCCESS;
3789 return;
3790
3791 ELSE
3792 IF l_party_id is not NULL THEN
3793 IF l_debug_level > 0 THEN
3794 oe_debug_pub.add( l_indent|| 'creating cust account...' ) ;
3795 END IF;
3796
3797 oe_oe_inline_address.create_account(
3798 p_party_number=>null,
3799 p_organization_name=>null,
3800 p_alternate_name=>null,
3801 p_tax_reference=>NULL,
3802 p_taxpayer_id=>NULL,
3803 p_party_id=>l_party_id,
3804 p_first_name=>null,
3805 p_last_name=>null,
3806 p_middle_name=>null,
3807 p_name_suffix=>null,
3808 p_title=>null,
3809 p_party_type=>l_party_type,
3810 p_email=>null,
3811 c_attribute_category=>null,
3812 c_attribute1=>null,
3813 c_attribute2=>null,
3814 c_attribute3=>null,
3815 c_attribute4=>null,
3816 c_attribute5=>null,
3817 c_attribute6=>null,
3818 c_attribute7=>null,
3822 c_attribute11=>null,
3819 c_attribute8=>null,
3820 c_attribute9=>null,
3821 c_attribute10=>null,
3823 c_attribute12=>null,
3824 c_attribute13=>null,
3825 c_attribute14=>null,
3826 c_attribute15=>null,
3827 c_attribute16=>null,
3828 c_attribute17=>null,
3829 c_attribute18=>null,
3830 c_attribute19=>null,
3831 c_attribute20=>null,
3832 c_global_attribute_category=>null,
3833 c_global_attribute1=>null,
3834 c_global_attribute2=>null,
3835 c_global_attribute3=>null,
3836 c_global_attribute4=>null,
3837 c_global_attribute5=>null,
3838 c_global_attribute6=>null,
3839 c_global_attribute7=>null,
3840 c_global_attribute8=>null,
3841 c_global_attribute9=>null,
3842 c_global_attribute10=>null,
3843 c_global_attribute11=>null,
3844 c_global_attribute12=>null,
3845 c_global_attribute13=>null,
3846 c_global_attribute14=>null,
3847 c_global_attribute15=>null,
3848 c_global_attribute16=>null,
3849 c_global_attribute17=>null,
3850 c_global_attribute18=>null,
3851 c_global_attribute19=>null,
3852 c_global_attribute20=>null,
3853 x_party_id=>x_party_id,
3854 x_party_number=>x_party_number,
3855 x_cust_Account_id=>x_cust_account_id,
3856 x_cust_account_number=>x_cust_account_number,
3857 x_return_status=>x_return_status,
3858 x_msg_count=>l_msg_count,
3859 x_msg_data=>l_msg_data,
3860 in_Created_by_module=>G_CREATED_BY_MODULE
3861 );
3862
3863 IF l_debug_level > 0 THEN
3864 oe_debug_pub.add( l_indent|| ' create account status='||x_RETURN_STATUS|| '; x_party_id = '|| X_PARTY_ID);
3865 oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3866 oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3867 END IF;
3868
3869 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3870 IF l_debug_level > 0 THEN
3871 oe_debug_pub.add( l_indent|| ' account creation failed' ) ;
3872 END IF;
3873 IF x_msg_count = 1 then
3874 FND_MESSAGE.Set_Name('ONT','ONT_AAC_ACCOUNT_CREATION');
3875 FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
3876 OE_MSG_PUB.ADD;
3877 ELSE
3878 oe_msg_pub.transfer_msg_stack;
3879 END IF;
3880
3881 END IF;
3882 p_account_tbl(1):= x_cust_account_id;
3883 /*cc project assigning the value CREATED TO variable G_account_created_or_found. We need
3884 to do the site creation process in case account is created and not search for the sites*/
3885 IF l_debug_level > 0 THEN
3886 oe_debug_pub.add('cc account created');
3887 END IF;
3888
3889 G_account_created_or_found :='CREATED';
3890
3891 /*cc project*/
3892 END IF; -- end party if
3893 END IF; -- If p_return_if_only_party
3894
3895 ELSE -- profile is N raise error
3896 /*cc project ,For Contact Center Integration
3897 If there is no permission to create the account , but party information is passed
3898 then we have to open the add Customer Form to the user. So returning
3899 status as success instead of error.*/
3900
3901 IF G_CREATED_BY_MODULE <> 'ONT_TELESERVICE_INTEGRATION' THEN
3902 x_return_status := FND_API.G_RET_STS_ERROR;
3903 IF l_debug_level > 0 THEN
3904 oe_debug_pub.add(l_indent|| 'not allowed to create account' ) ;
3905 END IF;
3906 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
3907 OE_MSG_PUB.ADD;
3908 ELSE
3909 IF l_debug_level > 0 THEN
3910 oe_debug_pub.add('Contact Center Integration, no permission to create account');
3911 END IF;
3912 END IF;
3913 END IF; -- end profile condition
3914
3915 ELSE
3916 IF l_debug_level > 0 THEN
3917 oe_debug_pub.add(l_indent|| ' account does not need to be created' ) ;
3918 END IF;
3919 END IF; -- checking to see if acconts needs to be created
3920
3921 --oe_debug_pub.add('p_out_cust_account_id = '|| p_account_tbl(1));
3922
3923 ELSE
3924 --x_return_status := FND_API.G_RET_STS_ERROR;
3925 FND_MESSAGE.Set_Name('ONT', 'ONT_AVAIL_GENERIC');
3926 IF l_debug_level > 0 THEN
3927 oe_debug_pub.add(l_indent|| 'party_id or party number is not passed' ) ;
3928 END IF;
3929 l_message := 'Party_id or Party Number is not passed';
3930 FND_MESSAGE.Set_Token('TEXT',l_message, FALSE);
3931 OE_MSG_PUB.ADD;
3932
3933 END IF; -- if party_id or party_number is not null
3934
3935
3936 IF l_debug_level > 0 THEN
3940 IF l_debug_level > 0 THEN
3937 oe_debug_pub.add(l_indent|| 'create account at the end' ) ;
3938 END IF;
3939 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3941 oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3942 END IF;
3943 oe_msg_pub.count_and_get(p_encoded=>fnd_api.G_TRUE,
3944 p_count => x_msg_count,
3945 p_data=>x_msg_data
3946 );
3947 IF l_debug_level > 0 THEN
3948 oe_debug_pub.add( l_indent|| 'count = '||x_msg_count||'; msg = '||X_MSG_DATA ) ;
3949 END IF;
3950 END IF;
3951
3952 IF l_debug_level > 0 THEN
3953 oe_debug_pub.add( l_indent|| 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
3954 oe_debug_pub.add( l_indent|| 'exiting check_and_create_account: ' || X_RETURN_STATUS ) ;
3955 END IF;
3956
3957 EXCEPTION
3958
3959 WHEN FND_API.G_EXC_ERROR THEN
3960
3961 IF C_get_cust_id_from_party_id%ISOPEN THEN
3962 CLOSE c_get_cust_id_from_party_id;
3963 END IF;
3964 IF party_rec%ISOPEN THEN
3965 CLOSE party_rec;
3966 END IF;
3967 IF party_number_rec%ISOPEN THEN
3968 CLOSE party_number_rec;
3969 END IF;
3970
3971 x_return_status := FND_API.G_RET_STS_ERROR;
3972
3973 -- Get message count and data
3974
3975 OE_MSG_PUB.Count_And_Get
3976 ( p_count => x_msg_count
3977 , p_data => x_msg_data
3978 );
3979
3980 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3981
3982
3983 IF C_get_cust_id_from_party_id%ISOPEN THEN
3984 CLOSE c_get_cust_id_from_party_id;
3985 END IF;
3986 IF party_rec%ISOPEN THEN
3987 CLOSE party_rec;
3988 END IF;
3989 IF party_number_rec%ISOPEN THEN
3990 CLOSE party_number_rec;
3991 END IF;
3992
3993 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3994
3995 -- Get message count and data
3996
3997 OE_MSG_PUB.Count_And_Get
3998 ( p_count => x_msg_count
3999 , p_data => x_msg_data
4000 );
4001
4002 WHEN OTHERS THEN
4003
4004
4005 IF C_get_cust_id_from_party_id%ISOPEN THEN
4006 CLOSE c_get_cust_id_from_party_id;
4007 END IF;
4008 IF party_rec%ISOPEN THEN
4009 CLOSE party_rec;
4010 END IF;
4011 IF party_number_rec%ISOPEN THEN
4012 CLOSE party_number_rec;
4013 END IF;
4014
4015 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4016 IF l_debug_level > 0 THEN
4017 oe_debug_pub.add( 'CHECK_ACCOUNT WHEN OTHERS EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
4018 END IF;
4019
4020 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4021 THEN
4022 OE_MSG_PUB.Add_Exc_Msg
4023 ( G_PKG_NAME
4024 , 'check_and_create_account'
4025 );
4026 END IF;
4027
4028 -- Get message count and data
4029
4030 OE_MSG_PUB.Count_And_Get
4031 ( p_count => x_msg_count
4032 , p_data => x_msg_data
4033 );
4034 END check_and_create_account;
4035
4036 PROCEDURE set_debug_on IS
4037
4038 l_file_val varchar2(2000);
4039
4040 --
4041 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4042 --
4043 BEGIN
4044
4045 oe_debug_pub.debug_on;
4046 oe_debug_pub.initialize;
4047 l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
4048
4049 END set_debug_on;
4050
4051
4052
4053 PROCEDURE if_multiple_accounts(
4054 p_party_id in number
4055 ,p_party_number varchar2
4056 ,p_account_Tbl out NOCOPY account_tbl
4057 ,x_return_status out NOCOPY varchar2
4058 ,x_msg_data out NOCOPY varchar2
4059 ,x_msg_count out NOCOPY number
4060 ) IS
4061
4062 CURSOR C_get_cust_id_from_party_id(l_Party_Id NUMBER) IS
4063 SELECT cust_account_id,
4064 account_number
4065 FROM hz_cust_accounts
4066 WHERE party_id = l_Party_Id
4067 and status = 'A';
4068
4069 CURSOR party_rec(l_party_id in number) IS
4070 select party_type
4071 from hz_parties
4072 where party_id = l_party_id;
4073
4074 CURSOR party_number_rec IS
4075 select party_id,party_type
4076 from hz_parties
4077 where party_id = p_party_number;
4078
4079 l_party_type VARCHAR2(30);
4080 l_party_id NUMBER;
4081 l_cust_account_id number;
4082 l_account_number varchar2(30);
4083
4084 --
4085 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4086 --
4087 BEGIN
4088
4089 x_return_status := FND_API.G_RET_STS_SUCCESS;
4090 p_account_tbl.DELETE;
4091
4095 -- if both party_id and party_number information is not provided then
4092 IF l_debug_level > 0 THEN
4093 oe_debug_pub.add( ' PARTY_ID='||P_PARTY_ID|| ' PARTY_NUMBER ='||P_PARTY_NUMBER ) ;
4094 END IF;
4096 -- then we raise an error
4097 IF p_party_id is not null or p_party_number is not null then
4098
4099 -- we will ignore party_number if party_id is passed
4100 IF p_party_id is null and p_party_number is not null then
4101 OPEN party_number_rec;
4102 FETCH party_number_rec
4103 INTO l_party_id,
4104 l_party_type;
4105
4106 IF party_number_rec%NOTFOUND THEN
4107 IF l_debug_level > 0 THEN
4108 oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_NUMBER' ) ;
4109 END IF;
4110 x_return_status := FND_API.G_RET_STS_ERROR;
4111 FND_MESSAGE.Set_Name('ONT','ONT_AACC_NO_ACCOUNT');
4112 OE_MSG_PUB.ADD;
4113 CLOSE party_number_rec;
4114 return;
4115 END IF;
4116
4117 CLOSE party_number_rec;
4118 ELSE
4119 l_party_id := p_party_id;
4120 OPEN party_rec(l_party_id);
4121 FETCH party_rec
4122 INTO l_party_type;
4123
4124 IF party_rec%NOTFOUND THEN
4125 IF l_debug_level > 0 THEN
4126 oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_ID' ) ;
4127 END IF;
4128 x_return_status := FND_API.G_RET_STS_ERROR;
4129 FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
4130 OE_MSG_PUB.ADD;
4131 CLOSE party_rec;
4132 return;
4133 END IF;
4134
4135 CLOSE party_rec;
4136
4137 END IF; -- if party_number is not null
4138
4139 IF l_debug_level > 0 THEN
4140 oe_debug_pub.add( 'PARTY TYPE FOR SOLD_TO = '|| L_PARTY_TYPE ) ;
4141 END IF;
4142 IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
4143
4144 IF l_debug_level > 0 THEN
4145 oe_debug_pub.add( 'PARTY TYPE='||L_PARTY_TYPE|| ' PARTY_ID='||L_PARTY_ID ) ;
4146 END IF;
4147 -- derive customer account
4148 OPEN C_get_cust_id_from_party_id(l_Party_Id);
4149 LOOP
4150 IF l_debug_level > 0 THEN
4151 oe_debug_pub.add( 'INSIDE GET_CUST_ID LOOP' ) ;
4152 END IF;
4153 FETCH C_get_cust_id_from_party_id
4154 INTO l_cust_account_id,
4155 l_account_number;
4156 EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
4157
4158 IF l_debug_level > 0 THEN
4159 oe_debug_pub.add( 'ACCT_ID='||L_CUST_ACCOUNT_ID|| ' ACCOUNT NUMBER='||L_ACCOUNT_NUMBER ) ;
4160 END IF;
4161
4162 IF l_debug_level > 0 THEN
4163 oe_debug_pub.add( 'ROW COUNT GET_CUST_ID='|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
4164 END IF;
4165
4166 oe_debug_pub.add('X1:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4167
4168 IF l_debug_level > 0 THEN
4169 oe_debug_pub.add( 'ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4170 END IF;
4171
4172 oe_debug_pub.add('X2:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4173
4174 p_account_tbl(p_account_tbl.COUNT + 1):= l_cust_account_id;
4175
4176 oe_debug_pub.add('X3:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4177
4178 END LOOP;
4179 ELSE
4180 IF l_debug_level > 0 THEN
4181 oe_debug_pub.add( 'INVALID PARTY TYPE' ) ;
4182 END IF;
4183 x_return_status := FND_API.G_RET_STS_ERROR;
4184 FND_MESSAGE.Set_Name('ONT','ONT_AAC_INVALID_PARTY');
4185 OE_MSG_PUB.ADD;
4186 return;
4187 END IF;
4188
4189 ELSE
4190 IF l_debug_level > 0 THEN
4191 oe_debug_pub.add( 'NO PARTY INFORMATION SENT' ) ;
4192 END IF;
4193 x_return_status := FND_API.G_RET_STS_ERROR;
4194 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4195 FND_MESSAGE.Set_Token('TEXT','No Customer Information ', FALSE);
4196 OE_MSG_PUB.ADD;
4197 return;
4198 END IF;
4199
4200 END if_multiple_accounts;
4201
4202
4203 -- Value_to_id for Automatic Account Creation :
4204 -- Try to lookup id-s for values passed in for customer, contact and sites
4205 -- Conservatively checking if the id columns are also passed.
4206 -- p_permission = "Y" -- Allow everything,
4207 -- "P" --allow contact and address only
4208 -- "N" -- nothing is allowed.
4209
4210 PROCEDURE Value_to_id(
4211 p_party_customer_rec IN OUT NOCOPY Party_customer_rec
4212 ,p_site_tbl IN OUT NOCOPY site_tbl_type
4213 ,p_permission IN varchar2
4214 ,x_return_status OUT NOCOPY VARCHAR2
4215 ,x_msg_count OUT NOCOPY NUMBER
4216 ,x_msg_data OUT NOCOPY VARCHAR2)
4217 IS
4218 -- local variables here
4219 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4220 l_dummy1 number := null;
4221 l_dummy2 number := null;
4222 l_dummy3 number := null;
4223 l_dummy4 boolean := false;
4224
4225 BEGIN
4226
4227 x_return_status := FND_API.G_RET_STS_SUCCESS;
4228
4229 IF l_debug_level > 0 THEN
4230 oe_debug_pub.add ('AAC:VTI: starting Value-To-Id');
4234
4231 oe_debug_pub.add ('AAC:VTI: looking for header level stuff');
4232 END IF;
4233
4235 -- if both party_id and account_id are missing, call find_sold_to_id
4236 if (nvl(p_party_customer_rec.p_party_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4237 OR nvl(p_party_customer_rec.p_cust_account_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4238 then
4239
4240 -- calling find_sold_to_id
4241 find_sold_to_id(
4242 p_party_id => p_party_customer_rec.p_party_id
4243 ,p_cust_account_id => p_party_customer_rec.p_cust_account_id
4244 ,p_party_name => p_party_customer_rec.p_party_name
4245 ,p_cust_account_number => p_party_customer_rec.p_cust_account_number
4246 ,p_party_number => p_party_customer_rec.p_party_number
4247 ,p_party_site_id => l_dummy1
4248 ,p_party_site_use_id => l_dummy2
4249 ,p_site_use_id => l_dummy3
4250 ,p_party_site_use_code => 'SOLD_TO' --bug 4240715
4251 ,p_permission => p_permission
4252 ,p_process_site => l_dummy4
4253 ,x_return_status => x_return_status
4254 );
4255
4256 IF x_return_status = FND_API.G_RET_STS_ERROR then
4257 -- not found a party_id/cust_account_id
4258 -- error message already logged inside find_sold_to_id
4259 IF l_debug_level > 0 THEN
4260 oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for header');
4261 END IF;
4262 -- exit if account_id cannot be found
4263 return;
4264 end if; -- x_return_status
4265
4266 end if;
4267
4268 IF l_debug_level > 0 THEN
4269 oe_debug_pub.add ('AAC:VTI: looking for sold_to contact');
4270 END IF;
4271
4272 -- if both org_contact_id and account_role_id are missing, call find_contact_id
4273 if (nvl(p_party_customer_rec.p_org_contact_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4274 and nvl(p_party_customer_rec.p_cust_account_role_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4275 then
4276 find_contact_id( p_contact_id => p_party_customer_rec.p_org_contact_id
4277 ,p_cust_contact_id => p_party_customer_rec.p_cust_account_role_id
4278 ,p_contact_name => p_party_customer_rec.p_contact_name
4279 ,p_permission => p_permission
4280 ,p_sold_to_org_id => p_party_customer_rec.p_cust_account_id
4281 ,p_site_use_id => p_party_customer_rec.p_cust_account_id
4282 ,p_party_id => p_party_customer_rec.p_party_id
4283 ,p_site_use_code => 'SOLD_TO'
4284 ,x_return_status => x_return_status
4285 );
4286
4287 end if;
4288
4289 x_return_status := FND_API.G_RET_STS_SUCCESS;
4290
4291 IF p_site_tbl.COUNT <> 0 then
4292 for i in p_site_tbl.FIRST..p_site_tbl.LAST loop
4293 -- do value-to-id for each site record
4294
4295 IF l_debug_level > 0 THEN
4296 oe_debug_pub.add ('AAC:VTI: ============ VTI SITE RECORD '||i||' of '||p_site_tbl.LAST||' =============== ');
4297 oe_debug_pub.add ('AAC:VTI: looking for site party_id line record #'||i);
4298 END IF;
4299
4300
4301 -- do party v-t-i
4302 find_sold_to_id(
4303 p_party_id => p_site_tbl(i).p_party_id
4304 ,p_cust_account_id => p_site_tbl(i).p_cust_account_id--site_customer_id
4305 ,p_party_name => p_site_tbl(i).p_party_name
4306 ,p_cust_account_number => p_site_tbl(i).p_cust_account_number
4307 ,p_party_number => p_site_tbl(i).p_party_number
4308 ,p_site_use_id => p_site_tbl(i).p_site_use_id
4309 ,p_party_site_id => p_site_tbl(i).p_party_site_id
4310 ,p_party_site_use_id => p_site_tbl(i).p_party_site_use_id
4311 ,p_party_site_use_code => p_site_tbl(1).p_site_use_code
4312 ,p_permission => p_permission
4313 ,p_process_site => p_site_tbl(i).p_process_site
4314 ,x_return_status => x_return_status
4315 );
4316
4317 IF x_return_status = FND_API.G_RET_STS_ERROR then
4318 -- not found a party_id/cust_account_id
4319 -- error message already logged inside find_sold_to_id
4320 IF l_debug_level > 0 THEN
4321 oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for line#'||i);
4322 END IF;
4323 -- exit if account_id cannot be found
4324 return;
4325 end if; -- x_return_status
4326
4327 IF l_debug_level > 0 THEN
4328 oe_debug_pub.add ('AAC:VTI: looking for site contact');
4329 END IF;
4330
4331 oe_debug_pub.add('P1:'||p_site_tbl(i).p_site_use_id);
4332 -- do contact v-t-i
4333 find_contact_id(
4334 p_contact_id => p_site_tbl(i).p_org_contact_id
4335 ,p_cust_contact_id => p_site_tbl(i).p_cust_account_role_id
4336 ,p_contact_name => p_site_tbl(i).p_contact_name
4337 ,p_permission => p_permission
4338 ,p_sold_to_org_id => p_site_tbl(i).p_cust_account_id
4339 ,p_site_use_id => p_party_customer_rec.p_cust_account_id
4340 ,p_party_id => p_site_tbl(i).p_party_id
4341 ,p_site_use_code => p_site_tbl(i).p_site_use_code --bug 4240715
4342 ,x_return_status => x_return_status
4343 );
4344
4345 x_return_status := FND_API.G_RET_STS_SUCCESS;
4346 -- do site v-t-i
4347 oe_debug_pub.add('P2:'||p_site_tbl(i).p_site_use_id);
4348 find_site_id(
4349 p_site_use_id => p_site_tbl(i).p_party_site_use_id
4350 ,p_site_id => p_site_tbl(i).p_party_site_id
4351 ,p_account_site_use_id => p_site_tbl(i).p_site_use_id
4352 ,p_site_use_code => p_site_tbl(i).p_site_use_code
4353 ,p_site_address1 => p_site_tbl(i).p_site_address1
4354 ,p_site_address2 => p_site_tbl(i).p_site_address2
4355 ,p_site_address3 => p_site_tbl(i).p_site_address3
4356 ,p_site_address4 => p_site_tbl(i).p_site_address4
4357 ,p_site_org => p_site_tbl(i).p_site_org
4358 ,p_site_city => p_site_tbl(i).p_site_city
4359 ,p_site_state => p_site_tbl(i).p_site_state
4360 ,p_site_postal_code => p_site_tbl(i).p_site_postal_code
4361 ,p_site_country => p_site_tbl(i).p_site_country
4362 ,p_site_customer_id => p_site_tbl(i).p_cust_account_id
4363 ,p_sold_to_org_id => p_party_customer_rec.p_cust_account_id
4364 ,p_party_id => p_site_tbl(i).p_party_id
4365 ,p_sold_to_party_id => p_party_customer_rec.p_party_id
4366 ,p_permission => p_permission
4367 ,x_return_status => x_return_status
4368 ,x_msg_count => x_msg_count
4369 ,x_msg_data => x_msg_data
4370 );
4371 oe_debug_pub.add('P3:'||p_site_tbl(i).p_site_use_id);
4372 IF x_return_status = FND_API.G_RET_STS_ERROR then
4373 -- not found a party_id/cust_account_id
4374 -- error message already logged inside find_sold_to_id
4375 IF l_debug_level > 0 THEN
4376 oe_debug_pub.add ('AAC:VTI:site_use_id account/party not found for line#'||1);
4377 END IF;
4378 -- exit if account_id cannot be found
4379 return;
4380 end if; -- x_return_status
4381
4382 end loop;
4383 end if;
4384
4385 IF l_debug_level > 0 THEN
4386 oe_debug_pub.add ('AAC:VTI: ending Value-To-Id');
4387 END IF;
4388
4389 END value_to_id;
4390
4391 PROCEDURE find_sold_to_id(
4392 p_party_id IN OUT NOCOPY number
4393 ,p_cust_account_id in out NOCOPY number
4394 ,p_party_name IN varchar2
4395 ,p_cust_account_number IN varchar2
4396 ,p_party_number in varchar2
4397 ,p_permission in varchar2
4398 ,p_site_use_id IN OUT NOCOPY number
4399 ,p_party_site_id IN OUT NOCOPY number
4400 ,p_party_site_use_id IN OUT NOCOPY number
4401 ,p_party_site_use_code IN varchar2 DEFAULT NULL --bug 4240715
4402 ,p_process_site IN OUT NOCOPY boolean
4403 ,x_return_status OUT NOCOPY VARCHAR2
4404 )
4405 IS
4406 -- local variables here
4407 cursor c_get_account_id(l_party_id number) is
4408 select cust_account_id
4412 and status='A';
4409 --into p_cust_account_id -- commented for bug 3449269
4410 from hz_cust_accounts
4411 where party_id=l_party_id
4413 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4414
4415 BEGIN
4416
4417 x_return_status := FND_API.G_RET_STS_SUCCESS;
4418
4419 IF l_debug_level > 0 THEN
4420 oe_debug_pub.add ('AAC:VTI: FSTI: starting find_sold_to_id');
4421 END IF;
4422
4423 -- check if party_id and cust_account_id are not null, return if so
4424 if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4425 and nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4426 THEN
4427 IF l_debug_level > 0 THEN
4428 oe_debug_pub.add ('AAC:FSTI: party_id and account_id are not null, nothing to do: returning');
4429 END IF;
4430 RETURN;
4431 ELSE
4432 IF l_debug_level > 0 THEN
4433 oe_debug_pub.add ('AAC:FSTI: party_name: '||p_party_name||'; cust_account_number: '||p_cust_account_number);
4434 oe_debug_pub.add ('AAC:FSTI: party_id: '||p_party_id||'; cust_account_id: '||p_cust_account_id);
4435 END IF;
4436 END IF;
4437
4438 if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4439 and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4440 and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441 and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442 and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4443 and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444 and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4445 and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4446 then
4447 --nothing to do! return!
4448 IF l_debug_level > 0 THEN
4449 oe_debug_pub.add ('AAC:FSTI: no data passed in, seting process_site to false, returning');
4450 END IF;
4451 p_process_site:=false;
4452 return;
4453 end if;
4454
4455 oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4456 oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4457 oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4458 oe_debug_pub.add('p_party_name :'||p_party_name );
4459 oe_debug_pub.add('p_party_number :'||p_party_number );
4460 oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4461 oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4462 oe_debug_pub.add('p_party_id :'||p_party_id);
4463
4464 begin
4465 -- check if site_use_id is passed
4466 if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4467 nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM ) then
4468
4469 IF l_debug_level > 0 THEN
4470 oe_debug_pub.add ('AAC:FSTI: site_use_id:'||p_site_use_id||' is not null, cust_account_id is null: getting cust_account_id');
4471 END IF;
4472
4473 select s.cust_account_id
4474 into p_cust_account_id
4475 from hz_cust_acct_sites_all s,
4476 hz_cust_site_uses u
4477 where s.cust_acct_site_id=u.cust_acct_site_id
4478 and u.site_use_id=p_site_use_id;
4479
4480 end if;
4481
4482 -- check if cust_account_id is not null, return if so
4483 if nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM and
4484 nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
4485
4486 IF l_debug_level > 0 THEN
4487 oe_debug_pub.add ('AAC:FSTI: cust_account_id:'||p_cust_account_id||' is not null, party_id is null: getting party_id');
4488 END IF;
4489
4490 select party_id
4491 into p_party_id
4492 from hz_cust_accounts
4493 where cust_account_id=p_cust_account_id;
4494
4495 RETURN;
4496 END IF;
4497
4498 EXCEPTION
4499
4500 WHEN NO_DATA_FOUND THEN
4501
4502 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
4503 THEN
4504 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_ACCOUNT');
4505 OE_MSG_PUB.ADD;
4506 END IF;
4507
4508 IF l_debug_level > 4 THEN
4509 oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4510 END IF;
4511
4512 x_return_status := FND_API.G_RET_STS_ERROR;
4513 return;
4514
4515 WHEN OTHERS THEN
4516
4517 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4518 --THEN
4519 OE_MSG_PUB.Add_Exc_Msg(
4520 G_PKG_NAME
4521 ,'find_sold_to_id'
4522 );
4523 --END IF;
4524 IF l_debug_level > 4 THEN
4525 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4526 END IF;
4527 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4528
4529 end;
4530
4531
4532 -- look for cust_account_id using party_name
4533 p_cust_account_id := sold_to_org(
4534 p_sold_to_org => p_party_name
4535 ,p_customer_number => p_cust_account_number
4536 ,p_site_use_code => p_party_site_use_code
4537 );
4538
4539 -- if account found, return
4540 IF nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4541 THEN
4542 p_cust_account_id := NULL;
4543
4544 else
4545 -- found a sold_to_org_id/cust_account_id
4546 IF l_debug_level > 0 THEN
4547 oe_debug_pub.add ('AAC:FSTI: found sold_to_org_id/cust_account_id:'||p_cust_account_id);
4548 END IF;
4549
4550 -- also find the party_id for this account_id
4551 if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4552 begin
4553 select party_id
4554 into p_party_id
4555 from hz_cust_accounts
4559 oe_debug_pub.add ('AAC:FSTI: also found party_id:'||p_party_id||', returning');
4556 where cust_account_id = p_cust_account_id;
4557
4558 IF l_debug_level > 0 THEN
4560 END IF;
4561 EXCEPTION
4562
4563 WHEN NO_DATA_FOUND THEN
4564
4565 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
4566 THEN
4567 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_ACCOUNT');
4568 OE_MSG_PUB.ADD;
4569 END IF;
4570
4571 IF l_debug_level > 4 THEN
4572 oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4573 END IF;
4574
4575 x_return_status := FND_API.G_RET_STS_ERROR;
4576 return;
4577
4578 WHEN OTHERS THEN
4579
4580 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4581 --THEN
4582 OE_MSG_PUB.Add_Exc_Msg(
4583 G_PKG_NAME
4584 ,'find_sold_to_id'
4585 );
4586 --END IF;
4587 IF l_debug_level > 4 THEN
4588 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4589 END IF;
4590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4591
4592 end;
4593 end if;
4594 return;
4595 end if;
4596
4597 if (nvl(p_party_number,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4598 and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4599 then
4600 IF l_debug_level > 0 THEN
4601 oe_debug_pub.add ('AAC:FSTI: party_number:'|| p_party_number||' is not null, picking up party_id from it');
4602 END IF;
4603
4604 begin
4605 select party_id
4606 into p_party_id
4607 from hz_parties
4608 where party_number=p_party_number;
4609
4610 EXCEPTION
4611
4612 WHEN NO_DATA_FOUND THEN
4613
4614 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
4615 THEN
4616 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_PARTY');
4617 OE_MSG_PUB.ADD;
4618 END IF;
4619
4620 IF l_debug_level > 4 THEN
4621 oe_debug_pub.add ('AAC:VTI: invalid party_number ');
4622 END IF;
4623
4624 x_return_status := FND_API.G_RET_STS_ERROR;
4625 p_party_id := null;
4626 return;
4627
4628 WHEN OTHERS THEN
4629
4630 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4631 --THEN
4632 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4633 --END IF;
4634 IF l_debug_level > 4 THEN
4635 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4636 END IF;
4637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4638
4639 end;
4640 end if;
4641
4642
4643 if (nvl(p_party_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4644 and nvl(p_party_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4645 then
4646 IF l_debug_level > 0 THEN
4647 oe_debug_pub.add ('AAC:FSTI: party_site_use_id:'|| p_party_site_use_id||' is not null, picking up party_site_id from it');
4648 END IF;
4649 begin
4650 select party_site_id
4651 into p_party_site_id
4652 from hz_party_site_uses
4653 where party_site_use_id=p_party_site_use_id;
4654
4655 EXCEPTION
4656
4657 WHEN NO_DATA_FOUND THEN
4658
4659 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
4660 THEN
4661 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_PARTY');
4662 OE_MSG_PUB.ADD;
4663 END IF;
4664
4665 IF l_debug_level > 4 THEN
4666 oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
4667 END IF;
4668
4669 x_return_status := FND_API.G_RET_STS_ERROR;
4670 p_party_site_id := null;
4671 return;
4672
4673 WHEN OTHERS THEN
4674
4675 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4676 --THEN
4677 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4678 --END IF;
4679 IF l_debug_level > 4 THEN
4680 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4681 END IF;
4682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4683
4684 end;
4685 end if;
4686
4687 if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4688 and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4689 then
4690 IF l_debug_level > 0 THEN
4691 oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up party_id from it');
4692 END IF;
4693 begin
4694 select party_id
4695 into p_party_id
4696 from hz_party_sites
4697 where party_site_id=p_party_site_id;
4698
4699 EXCEPTION
4700
4701 WHEN NO_DATA_FOUND THEN
4702
4703 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
4704 THEN
4705 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_PARTY');
4706 OE_MSG_PUB.ADD;
4707 END IF;
4708
4709 IF l_debug_level > 4 THEN
4710 oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
4711 END IF;
4712
4713 x_return_status := FND_API.G_RET_STS_ERROR;
4714 return;
4715
4716 WHEN OTHERS THEN
4717
4718 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4719 --THEN
4720 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4721 --END IF;
4722 IF l_debug_level > 4 THEN
4723 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4724 END IF;
4725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4726
4727 end;
4731 if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4728 end if;
4729
4730 /*
4732 and nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4733 then
4734 IF l_debug_level > 0 THEN
4735 oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust site_use_id from it');
4736 END IF;
4737 begin
4738 select u.site_use_id
4739 into p_site_use_id
4740 from hz_cust_acct_sites s,hz_cust_site_uses_all u
4741 where s.party_site_id=p_party_site_id
4742 and s.cust_acct_site_id= u.cust_acct_site_id;
4743
4744 EXCEPTION
4745
4746 WHEN NO_DATA_FOUND THEN
4747
4748 IF l_debug_level > 4 THEN
4749 oe_debug_pub.add ('AAC:VTI: party_site_id: no site_use_id ');
4750 END IF;
4751 p_site_use_id := null;
4752
4753 WHEN TOO_MANY_ROWS THEN
4754 IF l_debug_level > 4 THEN
4755 oe_debug_pub.add ('AAC:VTI: party_site_id: multiple sites found ');
4756 END IF;
4757 p_site_use_id := null;
4758
4759 WHEN OTHERS THEN
4760
4761 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4762 --THEN
4763 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4764 --END IF;
4765 IF l_debug_level > 4 THEN
4766 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4767 END IF;
4768 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4769
4770 end;
4771 end if;*/
4772
4773 /*
4774 if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4775 and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4776 then
4777 IF l_debug_level > 0 THEN
4778 oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust_account_id from it via account_sites');
4779 END IF;
4780 begin
4781 select distinct cust_account_id
4782 into p_cust_account_id
4783 from hz_cust_acct_sites cs
4784 where party_site_id=p_party_site_id;
4785
4786 EXCEPTION
4787
4788 WHEN NO_DATA_FOUND THEN
4789
4790 IF l_debug_level > 4 THEN
4791 oe_debug_pub.add ('AAC:VTI: party_site_id: no cust_account_id via site_use_id');
4792 END IF;
4793 p_cust_account_id := null;
4794
4795 WHEN TOO_MANY_ROWS THEN
4796 IF l_debug_level > 4 THEN
4797 oe_debug_pub.add ('AAC:VTI: party_site_id: multiple accounts ');
4798 END IF;
4799
4800 p_cust_account_id := null;
4801 x_return_status := FND_API.G_RET_STS_ERROR;
4802 p_cust_account_id := null;
4803 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4804 FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4805 OE_MSG_PUB.ADD;
4806
4807 return;
4808
4809 WHEN OTHERS THEN
4810
4811 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4812 --THEN
4813 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4814 --END IF;
4815 IF l_debug_level > 4 THEN
4816 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4817 END IF;
4818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4819
4820 end;
4821 end if;*/
4822
4823
4824 /*
4825 if (nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4826 and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4827 then
4828 IF l_debug_level > 0 THEN
4829 oe_debug_pub.add ('AAC:FSTI: site_id:'|| p_site_id||' is not null, picking up cust_account_id from it');
4830 END IF;
4831 begin
4832 select distinct cust_account_id
4833 into p_cust_account_id
4834 from hz_cust_acct_sites
4835 where cust_acct_site_id=p_site_id;
4836
4837 EXCEPTION
4838
4839 WHEN NO_DATA_FOUND THEN
4840
4841 IF l_debug_level > 4 THEN
4842 oe_debug_pub.add ('AAC:VTI: site_id: no cust_account_id via site_id');
4843 END IF;
4844 p_cust_account_id := null;
4845
4846 WHEN TOO_MANY_ROWS THEN
4847 IF l_debug_level > 4 THEN
4848 oe_debug_pub.add ('AAC:VTI: site_id: multiple accounts, returning with error ');
4849 END IF;
4850
4851 x_return_status := FND_API.G_RET_STS_ERROR;
4852 p_cust_account_id := null;
4853 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4854 FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4855 OE_MSG_PUB.ADD;
4856
4857 return;
4858
4859 WHEN OTHERS THEN
4860
4861 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4862 --THEN
4863 OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4864 --END IF;
4865 IF l_debug_level > 4 THEN
4866 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4867 END IF;
4868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4869
4870 end;
4871 end if;
4872 */
4873 -- check if party_id is not null
4874 -- We have a party_id, then look for account_id using it
4875 -- multiple matches is an error
4876 -- single/zero match is ok
4877
4878 if nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4879 and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4880 IF l_debug_level > 0 THEN
4881 oe_debug_pub.add ('AAC:FSTI: party_id:'|| p_party_id||' is not null, picking up account_id from it');
4882 END IF;
4883
4884 open c_get_account_id(p_party_id);
4885
4886 loop
4887 fetch c_get_account_id
4888 into p_cust_account_id;
4889 EXIT WHEN c_get_account_id%NOTFOUND;
4890
4891 if c_get_account_id%ROWCOUNT > 1 then
4895 END IF;
4892
4893 IF l_debug_level > 0 THEN
4894 oe_debug_pub.add ('AAC:FSTI: ERROR: multiple accounts found');
4896
4897 --x_return_status := FND_API.G_RET_STS_ERROR;
4898 p_cust_account_id := null;
4899 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4900 FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4901 OE_MSG_PUB.ADD;
4902 close c_get_account_id;
4903
4904 return;
4905 end if;
4906 end loop;
4907
4908 if (c_get_account_id%ROWCOUNT = 1)
4909 then
4910 IF l_debug_level > 0 THEN
4911 oe_debug_pub.add ('AAC:FSTI: one account found:'||p_cust_account_id);
4912 END IF;
4913 end if;
4914
4915 if (c_get_account_id%ROWCOUNT = 0)
4916 then
4917 IF l_debug_level > 0 THEN
4918 oe_debug_pub.add ('AAC:FSTI: no account found');
4919 END IF;
4920 p_cust_account_id := NULL;
4921 end if;
4922
4923 close c_get_account_id;
4924 return;
4925 end if;
4926
4927 -- no account found, and party_id is null/missing
4928 -- try to find name in party_layer
4929 -- check if we have permissions to create accounts ("Y")
4930 -- looking for party_id is futile if we cannot create an account
4931
4932 IF p_permission <> 'Y' THEN
4933 -- reset cust_account_id, set error condition
4934 -- and return
4935 IF l_debug_level > 0 THEN
4936 oe_debug_pub.add ('AAC:FSTI: no permission to create account: returning');
4937 END IF;
4938 FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
4939 OE_MSG_PUB.ADD;
4940 p_cust_account_id := NULL;
4941 x_return_status := FND_API.G_RET_STS_ERROR;
4942 return;
4943 END IF;
4944
4945 -- at this point:
4946 -- no matching cust_account_id found,
4947 -- we have permission to create account,
4948 -- and going to search for name in party layer
4949 IF l_debug_level > 0 THEN
4950 oe_debug_pub.add ('AAC:FSTI: have permission to create account: trying to find party_id');
4951 END IF;
4952
4953 p_party_id := get_party_id(
4954 p_party_name => p_party_name,
4955 p_party_number => p_party_number,
4956 p_party_site_use_code => p_party_site_use_code
4957 );
4958
4959
4960 IF nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4961 THEN
4962 -- didn't find a party_id either
4963 -- since we *cannot* create a party, error out
4964 IF l_debug_level > 0 THEN
4965 oe_debug_pub.add ('AAC:FSTI: cannot find party_id, returning with error');
4966 END IF;
4967 p_party_id := NULL;
4968 x_return_status := FND_API.G_RET_STS_ERROR;
4969 return;
4970 END IF;
4971
4972 IF l_debug_level > 0 THEN
4973 oe_debug_pub.add ('AAC:FSTI: found party_id:'||p_party_id);
4974 END IF;
4975
4976
4977 IF l_debug_level > 0 THEN
4978 oe_debug_pub.add ('AAC:VTI: ending find_sold_to_id');
4979 END IF;
4980
4981 return;
4982
4983 EXCEPTION
4984
4985 WHEN NO_DATA_FOUND THEN
4986
4987 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
4988 THEN
4989
4990 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
4991 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','find_sold_to_id');
4992 OE_MSG_PUB.Add;
4993
4994 END IF;
4995 IF l_debug_level > 4 THEN
4996 oe_debug_pub.add ('AAC:VTI: invalid p_cust_account_id in find_sold_to_id'); -- got a party_name
4997 END IF;
4998
4999 x_return_status := FND_API.G_RET_STS_ERROR;
5000 return;
5001
5002 WHEN OTHERS THEN
5003
5004 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5005 THEN
5006 OE_MSG_PUB.Add_Exc_Msg(
5007 G_PKG_NAME
5008 ,'find_Sold_To_Org_id'
5009 );
5010 END IF;
5011 IF l_debug_level > 4 THEN
5012 oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id'); -- got a party_name
5013 END IF;
5014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5015
5016 END find_sold_to_id;
5017
5018 PROCEDURE find_contact_id(
5019 p_contact_id IN OUT NOCOPY number
5020 ,p_cust_contact_id IN OUT NOCOPY number
5021 ,p_contact_name IN varchar2
5022 ,p_permission in varchar2
5023 ,p_sold_to_org_id in number
5024 ,p_site_use_id in number
5025 ,p_party_id in number
5026 ,p_site_use_code in varchar2 default null
5027 ,x_return_status OUT NOCOPY varchar2
5028 )
5029 IS
5030 -- local variables here
5031 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
5032
5033 BEGIN
5034
5035 x_return_status := FND_API.G_RET_STS_SUCCESS;
5036
5037 IF l_debug_level > 0 THEN
5038 oe_debug_pub.add ('AAC:VTI: starting find_contact_id...');
5039 END IF;
5040
5041 -- check if cust_contact_id is not null, return if so
5042 if nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5043 IF l_debug_level > 0 THEN
5044 oe_debug_pub.add ('AAC:FCI: cust_contact_id is not null, nothing to do: warn; returning}');
5045 END IF;
5046
5047 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5048 THEN
5049 fnd_message.set_name('ONT','OE_BOTH_VAL_AND_ID_EXIST');
5050 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_contact');
5051 OE_MSG_PUB.Add;
5052 END IF;
5053
5054 RETURN;
5055 END IF;
5056
5057 if nvl(p_sold_to_org_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5058 IF l_debug_level > 0 THEN
5062 return;
5059 oe_debug_pub.add ('AAC:FCI: sold_to_org_id is null; returning');
5060 END IF;
5061
5063 end if;
5064
5065
5066 IF l_debug_level > 0 THEN
5067 oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5068 oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5069 oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5070 oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5071 END IF;
5072
5073 -- look for cust_contact_id using contact_name
5074
5075
5076 if nvl(p_contact_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
5077 and nvl(p_cust_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5078 IF l_debug_level > 0 THEN
5079 oe_debug_pub.add ('AAC:FCI: contact_name is not null,trying to find account contact_id');
5080 END IF;
5081 if p_site_use_code <> 'END_CUST' then
5082 p_cust_contact_id := OE_Value_To_Id.sold_to_contact(
5083 p_sold_to_contact => p_contact_name
5084 ,p_sold_to_org_id => p_sold_to_org_id
5085 );
5086 else
5087 p_cust_contact_id := OE_Value_To_Id.end_Customer_contact(
5088 p_end_customer_contact => p_contact_name
5089 ,p_end_customer_id => p_sold_to_org_id
5090 );
5091 end if;
5092 -- if contact found, return
5093 IF nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5094 THEN
5095 -- found a cust_contact_id
5096 IF l_debug_level > 0 THEN
5097 oe_debug_pub.add ('AAC:FCI: found cust_contact_id:'||p_cust_contact_id||', returning}');
5098 END IF;
5099 return;
5100 else
5101 p_cust_contact_id := null;
5102 end if;
5103 end if;
5104
5105
5106 -- check if contact_id is not null, return if so
5107 if nvl(p_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5108 IF l_debug_level > 0 THEN
5109 oe_debug_pub.add ('AAC:FSTI: org_contact_id is not null');
5110 END IF;
5111
5112 RETURN;
5113 END IF;
5114
5115
5116 -- at this point:
5117 -- no matching cust_contact_id found,
5118 -- we have permission to create contact,
5119 -- and going to search for name in party layer
5120 IF l_debug_level > 0 THEN
5121 oe_debug_pub.add ('AAC:FCI: no account contact_id');
5122 oe_debug_pub.add ('AAC:FCI: have permission to create account contact: finding party contact_id');
5123 END IF;
5124
5125 IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5126 THEN
5127 p_contact_id := get_party_contact_id(
5128 p_contact_name => p_contact_name
5129 ,p_party_id => p_party_id
5130 ,p_sold_to_org_id => p_sold_to_org_id
5131 );
5132
5133
5134 IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5135 THEN
5136 -- didn't find a party level contact_id either
5137 -- since we *cannot* create a party, error out
5138 IF l_debug_level > 0 THEN
5139 oe_debug_pub.add ('AAC:FCI: cannot find contact_id, returning with error}');
5140 END IF;
5141 p_contact_id := NULL;
5142 x_return_status := FND_API.G_RET_STS_ERROR;
5143 return;
5144 END IF;
5145 end if;
5146 -- found a contact_id
5147
5148 IF l_debug_level > 0 THEN
5149 oe_debug_pub.add ('AAC:FCI: found contact_id:'||p_contact_id);
5150 oe_debug_pub.add ('AAC:VTI: ...done find_contact_id');
5151 END IF;
5152
5153 return;
5154
5155 END find_contact_id;
5156
5157 procedure find_site_id(
5158 p_site_use_id IN OUT NOCOPY number
5159 ,p_site_id IN OUT NOCOPY number
5160 ,p_account_site_use_id in out NOCOPY number
5161 ,p_site_use_code in varchar2
5162 ,p_site_address1 in VARCHAR2
5163 ,p_site_address2 in VARCHAR2
5164 ,p_site_address3 in VARCHAR2
5165 ,p_site_address4 in VARCHAR2
5166 ,p_site_org in VARCHAR2
5167 ,p_site_city in VARCHAR2
5168 ,p_site_state in VARCHAR2
5169 ,p_site_postal_code in VARCHAR2
5170 ,p_site_country in VARCHAR2
5171 ,p_site_customer_id in number
5172 ,p_sold_to_org_id in number
5173 ,p_sold_to_party_id in number
5174 ,p_party_id IN out nocopy number
5175 ,p_permission in varchar2
5176 ,x_return_status OUT NOCOPY VARCHAR2
5177 ,x_msg_data out NOCOPY varchar2
5178 ,x_msg_count out NOCOPY varchar2
5179 )
5180 IS
5181 -- local variables here
5182 l_debug_level NUMBER := oe_debug_pub.g_debug_level;
5183 l_cust_account_id number := null;
5184
5185 BEGIN
5186
5187 x_return_status := FND_API.G_RET_STS_SUCCESS;
5188
5189 IF l_debug_level > 0 THEN
5190 oe_debug_pub.add ('AAC:VTI: FSI: starting find_site_id{');
5191 END IF;
5192
5193 if (nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5194 then
5195 IF l_debug_level > 0 THEN
5196 oe_debug_pub.add ('AAC:FSI: party_id null; returning');
5197 END IF;
5198
5199 return;
5200 end if;
5201
5202 if (nvl(p_party_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5203 OR nvl(p_site_customer_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
5204 then
5205 l_cust_account_id := p_site_customer_id;
5206 else
5207 l_cust_account_id := p_sold_to_org_id;
5208 end if;
5209
5210 -- check if site_address1 is null
5211 IF l_debug_level > 0 THEN
5212 if nvl(p_site_address1,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
5216 end if;
5213 oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5214 else
5215 oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5217 oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5218 END IF;
5219
5220 -- check if cust_site_use_id is not null, return if so
5221 if nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5222 IF l_debug_level > 0 THEN
5223 oe_debug_pub.add ('AAC:FSI: account_site_use_id is not null; returning');
5224 END IF;
5225
5226 RETURN;
5227 END IF;
5228
5229 -- check if site_use_code is null, return with error if so
5230 if nvl(p_site_use_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR then
5231 IF l_debug_level > 0 THEN
5232 oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5233 oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5234 end if;
5235 x_return_status := FND_API.G_RET_STS_ERROR;
5236 return;
5237 end if;
5238
5239 if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5240 and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5241 and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242 --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243 and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244 and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245 and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246 and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247 and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248 and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249 and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250 and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251 and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252 and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5253 --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5254 --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255 and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5256 then
5257 --nothing to do! return!
5258 IF l_debug_level > 0 THEN
5259 oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5260 END IF;
5261 return;
5262 end if;
5263
5264 IF l_debug_level > 0 THEN
5265 oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5266 oe_debug_pub.add('p_site_id :'|| p_site_id );
5267 oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5268 oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269 oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270 oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271 oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272 oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273 oe_debug_pub.add('p_site_org :'|| p_site_org );
5274 oe_debug_pub.add('p_site_city :'|| p_site_city );
5275 oe_debug_pub.add('p_site_state :'|| p_site_state );
5276 oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5277 oe_debug_pub.add('p_site_country :'|| p_site_country );
5278 oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5279 oe_debug_pub.add('p_party_id :'|| p_party_id );
5280 end if;
5281
5282 if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5283 and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5284 IF l_debug_level > 0 THEN
5285 oe_debug_pub.add ('AAC:FSI: party_site_use_id:'||p_site_use_id||' is not null; using it to get party_site_id');
5286 END IF;
5287 begin
5288
5289 select party_site_id
5290 into p_site_id
5291 from hz_party_site_uses
5292 where party_site_use_id=p_site_use_id
5293 and status='A';
5294
5295 EXCEPTION
5296
5297 WHEN NO_DATA_FOUND THEN
5298
5299 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
5300 THEN
5301 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
5302 FND_MESSAGE.Set_Token('TEXT','Not a Valid party site use ', FALSE);
5303 OE_MSG_PUB.ADD;
5304 END IF;
5305
5306 IF l_debug_level > 4 THEN
5307 oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
5308 END IF;
5309
5310 x_return_status := FND_API.G_RET_STS_ERROR;
5311 return;
5312
5313 WHEN OTHERS THEN
5314
5315 --IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5316 --THEN
5317 OE_MSG_PUB.Add_Exc_Msg(
5318 G_PKG_NAME
5319 ,'find_site_id'
5320 );
5321 --END IF;
5322 IF l_debug_level > 4 THEN
5323 oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5324 END IF;
5325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5326
5327 end;
5328
5329 end if;
5330
5331 if nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5332 and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5333 IF l_debug_level > 0 THEN
5334 oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5335 END IF;
5336 begin
5337 select s.party_id
5338 into p_party_id
5339 from hz_party_sites s
5340 where s.party_site_id=p_site_id
5344 oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5341 and s.status='A';
5342
5343 IF l_debug_level > 0 THEN
5345 END IF;
5346
5347
5348 EXCEPTION
5349
5350 WHEN NO_DATA_FOUND THEN
5351
5352 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
5353 THEN
5354 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
5355 FND_MESSAGE.Set_Token('TEXT','Not a Valid party site ', FALSE);
5356 OE_MSG_PUB.ADD;
5357
5358 END IF;
5359 IF l_debug_level > 4 THEN
5360 oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5361 END IF;
5362 x_return_status := FND_API.G_RET_STS_ERROR;
5363 return;
5364
5365 WHEN OTHERS THEN
5366
5367 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5368 THEN
5369 OE_MSG_PUB.Add_Exc_Msg(
5370 G_PKG_NAME
5371 ,'find_site_id'
5372 );
5373 END IF;
5374 IF l_debug_level > 4 THEN
5375 oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5376 END IF;
5377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5378
5379 end;
5380 end if;
5381
5382 if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5383 and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5384 and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385 and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386 and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387 and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388 and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389 and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390 and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391 then
5392 --nothing to do! return!
5393 IF l_debug_level > 0 THEN
5394 oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5395 END IF;
5396 return;
5397 end if;
5398
5399 -- look for cust_site_id using site_name
5400 IF l_debug_level > 0 THEN
5401 oe_debug_pub.add ('AAC:FSI: trying to find account site_use_id');
5402 END IF;
5403
5404 /*
5405 if p_site_use_code='SHIP_TO'
5406 THEN
5407
5408 p_account_site_use_id := OE_Value_To_Id.ship_to_org(
5409 p_ship_to_address1 => p_site_address1
5410 ,p_ship_to_address2 => p_site_address2
5411 ,p_ship_to_address3 => p_site_address3
5412 ,p_ship_to_address4 => p_site_address4
5413 ,p_ship_to_location => p_site_org
5414 ,p_ship_to_org => p_site_org
5415 ,p_sold_to_org_id => l_cust_account_id
5416 ,p_ship_to_city => p_site_city
5417 ,p_ship_to_state => p_site_state
5418 ,p_ship_to_postal_code => p_site_postal_code
5419 ,p_ship_to_country => p_site_country
5420 ,p_ship_to_customer_id => p_site_customer_id );
5421
5422
5423 elsif p_site_use_code='BILL_TO'
5424 THEN
5425
5426 p_account_site_use_id := OE_Value_To_Id.invoice_to_org(
5427 p_invoice_to_address1 => p_site_address1
5428 ,p_invoice_to_address2 => p_site_address2
5429 ,p_invoice_to_address3 => p_site_address3
5430 ,p_invoice_to_address4 => p_site_address4
5431 ,p_invoice_to_location => p_site_org
5432 ,p_invoice_to_org => p_site_org
5433 ,p_sold_to_org_id => l_cust_account_id
5434 ,p_invoice_to_city => p_site_city
5435 ,p_invoice_to_state => p_site_state
5436 ,p_invoice_to_postal_code => p_site_postal_code
5437 ,p_invoice_to_country => p_site_country
5438 ,p_invoice_to_customer_id => p_site_customer_id );
5439
5440
5441 elsif p_site_use_code='DELIVER_TO'
5442 THEN
5443 p_account_site_use_id := OE_Value_To_Id.deliver_to_org(
5444 p_deliver_to_address1 => p_site_address1
5445 ,p_deliver_to_address2 => p_site_address2
5446 ,p_deliver_to_address3 => p_site_address3
5447 ,p_deliver_to_address4 => p_site_address4
5448 ,p_deliver_to_location => p_site_org
5449 ,p_deliver_to_org => p_site_org
5450 ,p_sold_to_org_id => l_cust_account_id
5451 ,p_deliver_to_city => p_site_city
5452 ,p_deliver_to_state => p_site_state
5453 ,p_deliver_to_postal_code => p_site_postal_code
5454 ,p_deliver_to_country => p_site_country
5455 ,p_deliver_to_customer_id => p_site_customer_id );
5456
5457 else
5458 -- ERROR!
5459 IF l_debug_level > 0 THEN
5460 oe_debug_pub.add ('AAC:FSI: site_use_code is invalid:'||p_site_use_code);
5461 END IF;
5462 return;
5463 end if;
5464
5465 -- if site found, return
5466 IF nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5467 THEN
5468 -- found a a cust_site_id
5469 IF l_debug_level > 0 THEN
5470 oe_debug_pub.add ('AAC:FSI: found cust_site_use_id:'||p_account_site_use_id||', returning');
5471 oe_debug_pub.add ('AAC:VTI: ending find_site_id');
5472 END IF;
5473
5474
5475 if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5476 IF l_debug_level > 0 THEN
5477 oe_debug_pub.add ('AAC:FSI: cust_site_use_id:'||p_account_site_use_id||' is not null; using it to get party_id');
5478 END IF;
5479 begin
5483 hz_cust_acct_sites s,
5480 select a.party_id
5481 into p_party_id
5482 from hz_cust_accounts a,
5484 hz_cust_site_uses_all u
5485 where u.site_use_id=p_account_site_use_id
5486 and u.cust_acct_site_id=s.cust_acct_site_id
5487 and s.cust_account_id=a.cust_account_id;
5488
5489 IF l_debug_level > 0 THEN
5490 oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5491 END IF;
5492
5493
5494 EXCEPTION
5495
5496 WHEN NO_DATA_FOUND THEN
5497
5498 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
5499 THEN
5500 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
5501 FND_MESSAGE.Set_Token('TEXT','Not a Valid account site use', FALSE);
5502 OE_MSG_PUB.ADD;
5503
5504 END IF;
5505 IF l_debug_level > 4 THEN
5506 oe_debug_pub.add ('AAC:VTI: invalid cust_site_use_id ');
5507 END IF;
5508 x_return_status := FND_API.G_RET_STS_ERROR;
5509 return;
5510
5511 end;
5512 end if;
5513
5514 return;
5515 else
5516 p_account_site_use_id := NULL; --convert from G_MISS_NUM
5517 END IF;
5518 */
5519 -- no account site found, try to find name in party_layer
5520 -- check if we have permissions to create sites (should be "Y" or "P")
5521 -- looking for party level site_id is futile if we cannot create a site
5522
5523 -- at this point:
5524 -- no matching cust_site_id found,
5525 -- we have permission to create site,
5526 -- and going to search for name in party layer
5527 IF l_debug_level > 0 THEN
5528 oe_debug_pub.add ('AAC:FSI: no account site_use_id');
5529 oe_debug_pub.add ('AAC:FSI: finding party site_use_id');
5530 END IF;
5531
5532 if (nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) then
5533 p_site_id := get_party_site_id(
5534 p_site_address1 => p_site_address1
5535 ,p_site_address2 => p_site_address2
5536 ,p_site_address3 => p_site_address3
5537 ,p_site_address4 => p_site_address4
5538 ,p_site_location => p_site_org
5539 ,p_site_org => p_site_org
5540 ,p_sold_to_party_id => p_sold_to_party_id
5541 ,p_site_city => p_site_city
5542 ,p_site_state => p_site_state
5543 ,p_site_postal_code => p_site_postal_code
5544 ,p_site_country => p_site_country
5545 ,p_site_customer_id => p_site_customer_id
5546 ,p_site_use_code => p_site_use_code
5547 ,p_party_id => p_party_id
5548 );
5549
5550 IF nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5551 THEN
5552 -- didn't find a party level site_id either
5553 -- since we *cannot* create a party, error out
5554 IF l_debug_level > 0 THEN
5555 oe_debug_pub.add ('AAC:FSI: cannot find party_site_id, returning with error');
5556 END IF;
5557 p_site_id := NULL;
5558 --x_return_status := FND_API.G_RET_STS_ERROR;
5559 return;
5560 ELSE -- we did find a party_site_id, get party_id from it
5561 if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5562 IF l_debug_level > 0 THEN
5563 oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5564 END IF;
5565 begin
5566 select s.party_id
5567 into p_party_id
5568 from hz_party_sites s
5569 where s.party_site_id=p_site_id
5570 and s.status='A';
5571
5572 IF l_debug_level > 0 THEN
5573 oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5574 END IF;
5575
5576
5577 EXCEPTION
5578
5579 WHEN NO_DATA_FOUND THEN
5580
5581 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
5582 THEN
5583 FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
5584 FND_MESSAGE.Set_Token('TEXT','Not a Valid party site ', FALSE);
5585 OE_MSG_PUB.ADD;
5586
5587 END IF;
5588 IF l_debug_level > 4 THEN
5589 oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5590 END IF;
5591 x_return_status := FND_API.G_RET_STS_ERROR;
5592 return;
5593
5594 WHEN OTHERS THEN
5595
5596 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5597 THEN
5598 OE_MSG_PUB.Add_Exc_Msg(
5599 G_PKG_NAME
5600 ,'find_site_id'
5601 );
5602 END IF;
5603 IF l_debug_level > 4 THEN
5604 oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5605 END IF;
5606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5607
5608 end;
5609 end if;
5610 end if;
5611 end if;
5612
5613 -- found a site_id
5614
5615 IF l_debug_level > 0 THEN
5616 oe_debug_pub.add ('AAC:FSI: party site_use_id:'||p_site_use_id);
5617 oe_debug_pub.add ('AAC:FSI: account site_use_id:'||p_account_site_use_id);
5618 END IF;
5619
5620 return;
5621
5622 END find_site_id;
5623
5624 /* helper functions */
5625
5626 FUNCTION get_party_id(
5627 p_party_name in varchar2
5628 ,p_party_number in varchar2
5629 ,p_party_site_use_code in varchar2
5630 ) return number
5631 IS
5632 l_id NUMBER;
5633 l_debug_level CONSTANT NUMBER := 5;--oe_debug_pub.g_debug_level;
5634 BEGIN
5635 IF l_debug_level > 4 THEN
5636 oe_debug_pub.add ('AAC:VTI: starting get_party_id{');
5637 END IF;
5638
5639 -- did they actually pass some values?
5640 IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5644 oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5641 AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5642 THEN
5643 IF l_debug_level > 4 THEN
5645 oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5646 END IF;
5647 RETURN NULL;
5648 END IF;
5649
5650 IF (nvl(p_party_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char)
5651 THEN
5652 -- got a party_number
5653 IF l_debug_level > 4 THEN
5654 oe_debug_pub.add ('AAC:VTI:GPI party number: '||p_party_number);
5655 END IF;
5656
5657 SELECT party_id
5658 INTO l_id
5659 FROM hz_parties party
5660 WHERE party.party_number = p_party_number
5661 and status='A';
5662
5663 ELSE
5664 IF l_debug_level > 4 THEN
5665 oe_debug_pub.add ('AAC:VTI:GPI name: '||p_party_name); -- got a party_name
5666 END IF;
5667
5668 SELECT party.party_id
5669 INTO l_id
5670 FROM HZ_PARTIES Party
5671 WHERE party.party_name = p_party_name
5672 and status='A';
5673
5674 END IF;
5675
5676 IF l_debug_level > 4 THEN
5677 oe_debug_pub.add ('AAC:VTI:GPI: party_id: '||l_id);
5678 oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5679 END IF;
5680
5681 return l_id;
5682 EXCEPTION
5683
5684 WHEN NO_DATA_FOUND THEN
5685
5686 IF (OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR))
5687 THEN
5688
5689 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
5690 --{bug 4240715
5691 if p_party_site_use_code is NULL or p_party_site_use_code = 'SOLD_TO' then
5692 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_org_id');
5693 elsif p_party_site_use_code ='SHIP_TO' then
5694 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_to_org_id');
5695 elsif p_party_site_use_code ='BILL_TO' then
5696 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','bill_to_org_id');
5697 elsif p_party_site_use_code ='DELIVER_TO' then
5698 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','deliver_to_org_id');
5699 elsif p_party_site_use_code ='END_CUST' then
5700 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_id');
5701 end if;
5702 --bug 4240715}
5703 OE_MSG_PUB.Add;
5704
5705 END IF;
5706 IF l_debug_level > 4 THEN
5707 oe_debug_pub.add ('AAC:VTI: no data in Get_party_id'); -- got a party_name
5708 END IF;
5709 RETURN FND_API.G_MISS_NUM;
5710
5711 WHEN OTHERS THEN
5712
5713 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5714 THEN
5715 OE_MSG_PUB.Add_Exc_Msg(
5716 G_PKG_NAME
5717 ,'get_party_id' --modified for bug 4590205
5718 );
5719 END IF;
5720 IF l_debug_level > 4 THEN
5721 oe_debug_pub.add ('AAC:VTI: unexpected error in Get_party_id'); -- got a party_name
5722 END IF;
5723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5724
5725 END get_party_id;
5726
5727 FUNCTION get_party_contact_id(
5728 p_contact_name in varchar2
5729 ,p_party_id in number
5730 ,p_sold_to_org_id in number
5731 ) return number
5732 IS
5733
5734 CURSOR c_org_contact_id(in_org_contact_name in varchar2,
5735 in_party_id number)
5736 IS
5737 SELECT org_contact.org_contact_id
5738 FROM hz_parties party,
5739 hz_relationships rel,
5740 hz_org_contacts org_contact,
5741 ar_lookups arl
5742 WHERE rel.object_id=in_party_id
5743 AND rel.relationship_id=org_contact.party_relationship_id
5744 AND rel.party_id=rel.subject_id
5745 AND rel.directional_flag='Y'
5746 AND party.party_type='PERSON'
5747 AND party.person_last_name || decode(party.person_first_name, null, null, ', '||
5748 party.person_first_name) || decode(arl.meaning, null, null, ' '||arl.meaning) = in_org_contact_name
5749 AND arl.lookup_code(+)=org_contact.title
5750 AND arl.lookup_type(+)='CONTACT_TITLE';
5751
5752
5753 l_id NUMBER;
5754 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5755 BEGIN
5756
5757 IF l_debug_level > 4 THEN
5758 oe_debug_pub.add ('AAC:VTI: starting get_party_contact_id...');
5759 END IF;
5760
5761 IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5762 THEN
5763 IF l_debug_level > 4 THEN
5764 oe_debug_pub.add ('AAC:VTI: no contact_name');
5765 oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5766 END IF;
5767 RETURN NULL;
5768 END IF;
5769
5770 OPEN c_org_contact_id(p_contact_name,p_party_id);
5771 FETCH c_org_contact_id
5772 INTO l_id;
5773
5774 IF c_org_contact_id%FOUND then
5775 CLOSE c_org_contact_id;
5776 IF l_debug_level > 4 THEN
5777 oe_debug_pub.add ('AAC:VTI: org_contact_id:'||l_id);
5778 oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5779 end if;
5780 return l_id;
5781 end if;
5782
5783 IF l_debug_level > 4 THEN
5784 oe_debug_pub.add ('AAC:VTI: GCI org_contact_id not found');
5785 end if;
5786
5787 CLOSE c_org_contact_id;
5788
5789 IF l_debug_level > 4 THEN
5790 oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5791 END IF;
5792
5793 return NULL;
5794
5795 END get_party_contact_id;
5796
5797
5798
5799 FUNCTION get_party_site_id(
5800 p_site_address1 IN VARCHAR2
5801 ,p_site_address2 IN VARCHAR2
5802 ,p_site_address3 IN VARCHAR2
5803 ,p_site_address4 IN VARCHAR2
5804 ,p_site_location IN VARCHAR2
5805 ,p_site_org IN VARCHAR2
5809 ,p_site_postal_code IN VARCHAR2
5806 ,p_sold_to_party_id IN number
5807 ,p_site_city IN VARCHAR2
5808 ,p_site_state IN VARCHAR2
5810 ,p_site_country IN VARCHAR2
5811 ,p_site_customer_id IN VARCHAR2
5812 ,p_site_use_code IN VARCHAR2
5813 ,p_party_id IN number
5814 ) return number
5815 IS
5816
5817 l_id NUMBER;
5818 lcustomer_relations varchar2(1);
5819
5820
5821 CURSOR c_party_site_id(in_sold_to_party_id number) IS
5822 SELECT site.party_site_id
5823 FROM hz_locations loc,
5824 hz_party_sites site
5825 WHERE site.location_id=loc.location_id
5826 and site.party_id=in_sold_to_party_id
5827 and loc.address1 = p_site_address1
5828 and nvl( loc.address2, fnd_api.g_miss_char) =
5829 nvl( p_site_address2, fnd_api.g_miss_char)
5830 and nvl( loc.address3, fnd_api.g_miss_char) =
5831 nvl( p_site_address3, fnd_api.g_miss_char)
5832 and nvl( loc.address4, fnd_api.g_miss_char) =
5833 nvl( p_site_address4, fnd_api.g_miss_char)
5834 and nvl( loc.city, fnd_api.g_miss_char) =
5835 nvl( p_site_city, fnd_api.g_miss_char)
5836 and nvl( loc.state, fnd_api.g_miss_char) =
5837 nvl( p_site_state, fnd_api.g_miss_char)
5838 and nvl( loc.postal_code, fnd_api.g_miss_char) =
5839 nvl( p_site_postal_code, fnd_api.g_miss_char)
5840 and nvl( loc.country, fnd_api.g_miss_char) =
5841 nvl( p_site_country, fnd_api.g_miss_char);
5842
5843
5844 /*
5845 cursor C1(in_sold_to_party_id number) IS
5846 SELECT site.party_site_id
5847 FROM HZ_PARTY_SITES SITE,
5848 HZ_LOCATIONS LOC
5849 WHERE site.location_id=loc.location_id
5850 and site.status='A'
5851 and loc.ADDRESS1 = p_site_address1
5852 AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5853 nvl( p_site_address2, fnd_api.g_miss_char)
5854 AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5855 nvl( p_site_address3, fnd_api.g_miss_char)
5856 AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857 nvl( p_site_address4, fnd_api.g_miss_char)
5858 AND nvl( loc.city, fnd_api.g_miss_char) =
5859 nvl( p_site_city, fnd_api.g_miss_char)
5860 AND nvl( loc.state, fnd_api.g_miss_char) =
5861 nvl( p_site_state, fnd_api.g_miss_char)
5862 AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5863 nvl( p_site_postal_code, fnd_api.g_miss_char)
5864 AND nvl( loc.country, fnd_api.g_miss_char) =
5865 nvl( p_site_country, fnd_api.g_miss_char)
5866 AND site.status = 'A'
5867 AND site.party_id in(
5868 SELECT in_sold_to_party_id FROM DUAL
5869 UNION
5870 SELECT object_ID
5871 FROM HZ_relationships rel
5872 WHERE rel.subject_id= in_sold_to_party_id
5873 and rel.status='A');
5874 */
5875 CURSOR c2 IS
5876 SELECT site.party_site_id
5877 FROM hz_locations loc,
5878 hz_party_sites site
5879 WHERE site.location_id=loc.location_id
5880 and site.status='A'
5881 and loc.address1 = p_site_address1
5882 and nvl( loc.address2, fnd_api.g_miss_char) =
5883 nvl( p_site_address2, fnd_api.g_miss_char)
5884 and nvl( loc.address3, fnd_api.g_miss_char) =
5885 nvl( p_site_address3, fnd_api.g_miss_char)
5886 and nvl( loc.address4, fnd_api.g_miss_char) =
5887 nvl( p_site_address4, fnd_api.g_miss_char)
5888 and nvl( loc.city, fnd_api.g_miss_char) =
5889 nvl( p_site_city, fnd_api.g_miss_char)
5890 and nvl( loc.state, fnd_api.g_miss_char) =
5891 nvl( p_site_state, fnd_api.g_miss_char)
5892 and nvl( loc.postal_code, fnd_api.g_miss_char) =
5893 nvl( p_site_postal_code, fnd_api.g_miss_char)
5894 and nvl( loc.country, fnd_api.g_miss_char) =
5895 nvl( p_site_country, fnd_api.g_miss_char);
5896
5897 l_site_party_id number;
5898 l_sold_to_party_id number;
5899 l_dummy number;
5900
5901 --
5902 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5903 --
5904 BEGIN
5905
5906 IF l_debug_level > 0 THEN
5907 oe_debug_pub.add( 'AAC: site_address1:'||P_SITE_ADDRESS1);
5908 oe_debug_pub.add(' address4:'||p_site_address4);
5909 oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5910 END IF;
5911
5912 IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5913 AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5914 AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915 AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916 AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5917 THEN
5918 IF l_debug_level > 0 THEN
5919 oe_debug_pub.add( 'AAC: all incoming data missing,returning');
5920 end if;
5921 RETURN NULL;
5922 END IF;
5923
5924 if (nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) then
5925 IF l_debug_level > 0 THEN
5926 oe_debug_pub.add( 'AAC: incoming party_id is '||p_party_id ) ;
5927 END IF;
5928
5929 OPEN c_party_site_id(p_party_id);
5930 FETCH c_party_site_id
5931 INTO l_id;
5932
5933 IF c_party_site_id%FOUND then
5934 CLOSE c_party_site_id;
5935 IF l_debug_level > 0 THEN
5936 oe_debug_pub.add( 'AAC: found party_site_id is '||l_id ) ;
5937 END IF;
5938 return l_id;
5939
5940 ELSE
5941 IF l_debug_level > 0 THEN
5942 oe_debug_pub.add( 'AAC: not found party_site_id in 1st try; trying SQL2' ) ;
5943 END IF;
5944
5945 SELECT site.party_site_id
5946 INTO l_id
5947 FROM hz_locations loc,
5948 hz_party_sites site
5949 WHERE loc.ADDRESS1 = p_site_address1
5950 AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5951 nvl( p_site_address2, fnd_api.g_miss_char)
5955 DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5952 AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5953 nvl( p_site_address3, fnd_api.g_miss_char)
5954 AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5956 DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5957 DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
5958 nvl( p_site_address4, fnd_api.g_miss_char)
5959 AND site.status = 'A'
5960 AND site.party_id = p_party_id
5961 and site.location_id=loc.location_id;
5962
5963 END IF;
5964 CLOSE c_party_site_id;
5965
5966 IF l_debug_level > 0 THEN
5967 oe_debug_pub.add( 'AAC: found party_site_id is '||l_id );
5968 END IF;
5969
5970 RETURN l_id;
5971
5972 ELSE --p_party_id is null...
5973
5974 IF l_debug_level > 0 THEN
5975 oe_debug_pub.add( 'AAC: party_id is null' ) ;
5976 END IF;
5977
5978 OPEN C2;
5979
5980 FETCH C2
5981 INTO l_id;
5982
5983 IF C2%FOUND then
5984 CLOSE C2 ;
5985 IF l_debug_level > 0 THEN
5986 oe_debug_pub.add( 'AAC: found site_use_id = '||L_ID ) ;
5987 END IF;
5988 return l_id;
5989
5990 ELSE
5991 IF l_debug_level > 0 THEN
5992 oe_debug_pub.add( 'AAC: not found party_site_id in 1st try; trying SQL2' ) ;
5993 END IF;
5994
5995 SELECT site.party_site_id
5996 INTO l_id
5997 FROM hz_locations loc,
5998 hz_party_sites site
5999 WHERE loc.ADDRESS1 = p_site_address1
6000 AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6001 nvl( p_site_address2, fnd_api.g_miss_char)
6002 AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6003 nvl( p_site_address3, fnd_api.g_miss_char)
6004 AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6005 DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
6006 DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
6007 DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
6008 nvl( p_site_address4, fnd_api.g_miss_char)
6009 AND site.status = 'A'
6010 and site.location_id=loc.location_id;
6011
6012 IF l_debug_level > 0 THEN
6013 oe_debug_pub.add( ' found site_use_id = '||L_ID ) ;
6014 END IF;
6015
6016 END IF;
6017 CLOSE C2;
6018
6019 RETURN l_id;
6020 END IF;
6021
6022
6023
6024 EXCEPTION
6025
6026 WHEN NO_DATA_FOUND THEN
6027
6028 IF (c_party_site_id%ISOPEN) then
6029 CLOSE c_party_site_id;
6030 END IF;
6031
6032
6033 IF C2%ISOPEN then
6034 CLOSE C2;
6035 END IF;
6036
6037 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
6038 THEN
6039
6040 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
6041 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','get_party_site_id');
6042 OE_MSG_PUB.Add;
6043
6044 END IF;
6045
6046 RETURN NULL;
6047
6048 WHEN OTHERS THEN
6049
6050 IF c_party_site_id%ISOPEN then
6051 CLOSE c_party_site_id;
6052 END IF;
6053
6054 IF C2%ISOPEN then
6055 CLOSE C2;
6056 END IF;
6057
6058 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6059 THEN
6060 OE_MSG_PUB.Add_Exc_Msg
6061 ( G_PKG_NAME
6062 , 'get_party_site_id'
6063 );
6064 END IF;
6065
6066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6067
6068 END get_party_site_id;
6069
6070
6071
6072 FUNCTION Sold_To_Org
6073 ( p_sold_to_org IN VARCHAR2
6074 , p_customer_number IN VARCHAR2
6075 , p_site_use_code IN VARCHAR2
6076 ) RETURN NUMBER
6077 IS
6078 l_id NUMBER;
6079 --
6080 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6081 --
6082 BEGIN
6083
6084 IF nvl(p_sold_to_org,fnd_api.g_miss_char) = fnd_api.g_miss_char
6085 AND nvl(p_customer_number,fnd_api.g_miss_char) = fnd_api.g_miss_char
6086 THEN
6087 RETURN NULL;
6088 END IF;
6089
6090
6091 IF nvl(p_customer_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
6092
6093 SELECT ORGANIZATION_ID
6094 INTO l_id
6095 FROM OE_SOLD_TO_ORGS_V
6096 WHERE CUSTOMER_NUMBER = p_customer_number;
6097
6098 ELSE
6099 Select Cust_Acct.Cust_account_id
6100 into l_id
6101 from HZ_CUST_ACCOUNTS Cust_Acct,
6102 HZ_PARTIES Party
6103 where Cust_Acct.Party_id = Party.party_id
6104 and cust_acct.status='A'
6105 and Party.Party_name = p_sold_to_org;
6106
6107 END IF;
6108
6109 RETURN l_id;
6110
6111 EXCEPTION
6112
6113 WHEN NO_DATA_FOUND THEN
6114
6115 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
6116 THEN
6117
6118 fnd_message.set_name('ONT','OE_VALUE_TO_ID_ERROR');
6119 --{bug 4240715
6120 if p_site_use_code is NULL or p_site_use_code = 'SOLD_TO' then
6121 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_org_id');
6122 elsif p_site_use_code ='SHIP_TO' then
6123 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','ship_to_org_id');
6124 elsif p_site_use_code ='BILL_TO' then
6125 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','bill_to_org_id');
6126 elsif p_site_use_code ='DELIVER_TO' then
6127 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','deliver_to_org_id');
6128 elsif p_site_use_code ='END_CUST' then
6129 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','end_customer_id');
6130 end if;
6131 -- FND_MESSAGE.SET_TOKEN('ATTRIBUTE','sold_to_org_id');
6132 --bug 4240715}
6133 OE_MSG_PUB.Add;
6134
6135 END IF;
6136
6137 RETURN FND_API.G_MISS_NUM;
6138
6139 WHEN OTHERS THEN
6140
6141 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6142 THEN
6143 OE_MSG_PUB.Add_Exc_Msg
6144 ( G_PKG_NAME
6145 , 'Sold_To_Org'
6146 );
6147 END IF;
6148
6149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6150
6151 END Sold_To_Org;
6152
6153 PROCEDURE does_Cust_Exist(p_cust_id IN NUMBER, --(5255840)
6154 x_found OUT NOCOPY VARCHAR2)
6155 IS
6156
6157 pragma autonomous_transaction;
6158
6159 BEGIN
6160 select 'Y' into x_found from HZ_CUST_ACCOUNTS where cust_account_id= p_cust_id;
6161 EXCEPTION
6162 WHEN NO_DATA_FOUND THEN
6163 x_found:='N';
6164
6165 WHEN OTHERS THEN
6166 oe_debug_pub.add('Yes... Error in Autonomous Block:'||SQLERRM);
6167 x_found:='E';
6168 END;
6169
6170 --------------------------------------------------------------
6171 FUNCTION CUST_EXISTS(cust_id number) return Boolean IS --(5255840)
6172 --------------------------------------------------------------------
6173 l_temp varchar2(2);
6174 BEGIN
6175
6176 does_Cust_Exist(p_cust_id => cust_id,
6177 x_found => l_temp);
6178 oe_debug_pub.add('Yes.. does_Cust_Exist:'||l_temp);
6179 IF l_temp='Y' THEN
6180 return TRUE;
6181 ELSIF l_temp='N' THEN
6182 oe_debug_pub.add('Yes.. Committing');
6183 COMMIT;
6184 return TRUE;
6185 ELSE
6186 return FALSE;
6187 END IF;
6188
6189 EXCEPTION WHEN OTHERS THEN
6190 oe_debug_pub.add('Yes... Error in CUST_EXISTS:'||SQLERRM);
6191 return FALSE;
6192 END Cust_Exists;
6193
6194 END oe_create_account_info;
[Home] [Help]