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