[Home] [Help]
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:
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);
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||
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;
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
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:
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;
159:
160: ELSE -- if it is null
161: fnd_profile.get('ONT_AUTOMATIC_ACCOUNT_CREATION',l_add_customer);
162: IF l_debug_level > 0 THEN
163: oe_debug_pub.add( 'atuomatic account creation profile = '||L_ADD_CUSTOMER ) ;
164: END IF;
165: IF l_add_customer = 'Y' then
166: p_allow_account_creation := TRUE;
167: p_allow_contact_creation := TRUE;
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
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
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:
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);
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||
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;
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
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:
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
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' ) ;
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
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' ) ;
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;
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:
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:
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
282:
283: oe_debug_pub.add('p_party_customer_rec.p_cust_account_id = '||p_party_customer_rec.p_cust_account_id );
284: oe_debug_pub.add('p_party_customer_rec.p_cust_account_number = '||p_party_customer_rec.p_cust_account_number );
285: oe_debug_pub.add('p_party_customer_rec.p_party_id = '||p_party_customer_rec.p_party_id );
286: oe_debug_pub.add('p_party_customer_rec.p_party_number = '||p_party_customer_rec.p_party_number );
287:
288:
289: IF ((p_party_customer_rec.p_cust_account_id is null) AND
290: (p_party_customer_rec.p_party_id is not null or
291: p_party_customer_rec.p_party_number is not null) AND
292: p_control_rec.p_process_customer) then
293:
294: IF l_debug_level > 0 THEN
295: oe_debug_pub.add( '=== calling check_and_create_account...' ) ;
296: END IF;
297:
298: /*cc project, Initializing the G_account_created_or_found before calling
299: check_and_create_account.Everytime Check_and_create_account is called it will be
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
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;
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:
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;
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
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:
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;
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:
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;
439: l_status;
440:
441: IF c_get_cust_account_id%NOTFOUND THEN
442: IF l_debug_level > 0 THEN
443: oe_debug_pub.add( 'invalid customer account number:'||p_party_customer_rec.p_cust_account_Number ) ;
444: END IF;
445: x_return_status := FND_API.G_RET_STS_ERROR;
446: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
447: OE_MSG_PUB.ADD;
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;
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
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:
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:
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
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;
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 */
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' ) ;
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;
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
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;
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;
568:
569: if (p_site_tbl(i).p_process_site = false) then
570:
571: IF l_debug_level > 0 THEN
572: oe_debug_pub.add( 'AAC:RC: customer#'||i||' does not require account creation, skipping');
573: END IF;
574: goto skip_loop;
575: end if;
576:
577: IF (p_site_tbl(i).p_cust_account_id is null) THEN
578: /* account creation needed {*/
579:
580: IF l_debug_level > 0 THEN
581: oe_debug_pub.add('AAC:RC: account creation needed: party_id:'|| l2_party_id);
582: oe_debug_pub.add( '=== calling check_and_create_account...' ) ;
583: END IF;
584: -- create account if needed (profile willing ofcourse)
585: Check_and_Create_Account(
578: /* account creation needed {*/
579:
580: IF l_debug_level > 0 THEN
581: oe_debug_pub.add('AAC:RC: account creation needed: party_id:'|| l2_party_id);
582: oe_debug_pub.add( '=== calling check_and_create_account...' ) ;
583: END IF;
584: -- create account if needed (profile willing ofcourse)
585: Check_and_Create_Account(
586: p_party_id=>l2_party_id
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
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;
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
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:
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;
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:
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;
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
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;
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')
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');
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:
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');
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
684: and cust_account_id=p_site_tbl(i).p_cust_account_id;
685:
686: found_relationship := TRUE;
687: IF l_debug_level > 0 THEN
688: oe_debug_pub.add( 'AAC:RC: relationship found' ) ;
689: END IF;
690:
691: EXCEPTION
692: WHEN NO_DATA_FOUND THEN
690:
691: EXCEPTION
692: WHEN NO_DATA_FOUND THEN
693: IF l_debug_level > 0 THEN
694: oe_debug_pub.add( 'AAC:RC: no relationship' ) ;
695: END IF;
696: found_relationship := FALSE;
697:
698: END; -- begin
700: if(found_relationship = FALSE) then
701:
702: if (l_add_customer = 'Y')
703: then
704: oe_debug_pub.add('AAC:RC: l_add_customer is Y');
705: oe_debug_pub.add('AAC:RC: creating relationship');
706: oe_oe_inline_address.create_cust_relationship(
707: p_cust_acct_id => p_site_tbl(i).p_cust_account_id
708: ,p_related_cust_acct_id => l_cust_account_id
701:
702: if (l_add_customer = 'Y')
703: then
704: oe_debug_pub.add('AAC:RC: l_add_customer is Y');
705: oe_debug_pub.add('AAC:RC: creating relationship');
706: oe_oe_inline_address.create_cust_relationship(
707: p_cust_acct_id => p_site_tbl(i).p_cust_account_id
708: ,p_related_cust_acct_id => l_cust_account_id
709: ,p_reciprocal_flag => 'Y'
711: ,x_msg_count => x_msg_count
712: ,x_msg_data => x_msg_data);
713:
714: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
715: oe_debug_pub.add('Creating relationship failed');
716:
717: x_return_status := FND_API.G_RET_STS_ERROR;
718: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
719: FND_MESSAGE.Set_Token('TEXT',' Customer Relationship creation failed. ', FALSE);
720: OE_MSG_PUB.ADD;
721: OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count , p_data => x_msg_data );
722: return;
723: END IF; -- if failure
724: oe_debug_pub.add('AAC:RC: relationship created');
725: else -- l_add_customer = 'N'
726: x_return_status := FND_API.G_RET_STS_ERROR;
727: IF l_debug_level > 0 THEN
728: oe_debug_pub.add('AAC:RC: ERROR: l_add_customer is N' );
724: oe_debug_pub.add('AAC:RC: relationship created');
725: else -- l_add_customer = 'N'
726: x_return_status := FND_API.G_RET_STS_ERROR;
727: IF l_debug_level > 0 THEN
728: oe_debug_pub.add('AAC:RC: ERROR: l_add_customer is N' );
729: END IF;
730: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
731: FND_MESSAGE.Set_Token('TEXT',' Customer Relationship not defined. No permission to create relationship. ', FALSE);
732: OE_MSG_PUB.ADD;
734: return;
735: end if; -- l_add_customer end
736: end if; -- found_relationship
737: else -- l_rc_matched = TRUE
738: oe_debug_pub.add('AAC:RC: Customers are same. continue');
739: end if; --l_rc_matched
740:
741:
742: elsif (lcustomer_relations = 'A')
740:
741:
742: elsif (lcustomer_relations = 'A')
743: then
744: oe_debug_pub.add('AAC:RC: Relationship is A. Continue');
745: end if;
746:
747: END IF; -- if p_site_tbl account_id is not null
748:
753:
754: END IF;/* we have p_site_tbl} */
755:
756: IF l_debug_level > 0 THEN
757: oe_debug_pub.add( 'AAC: ...done related customer' ) ;
758: END IF;
759: /* done related customer here */
760:
761: -- if the check_customer did not fail or did not return multiple accts
766: p_party_customer_rec.p_cust_account_id IS NOT NULL) AND
767: p_Control_rec.p_process_contact
768: THEN
769: IF l_debug_level > 0 THEN
770: oe_debug_pub.add( 'checking and creating contact' ) ;
771: END IF;
772: -- if contact was found in check account because of party relationship
773: -- then use that contact
774: IF l_cust_account_role_id is null AND
773: -- then use that contact
774: IF l_cust_account_role_id is null AND
775: l_org_contact_id is null then
776: IF l_debug_level > 0 THEN
777: oe_debug_pub.add( 'taking contact informaton from record' ) ;
778: END IF;
779: l_cust_account_role_id :=p_party_customer_rec.p_cust_account_role_id;
780: l_org_contact_id :=p_party_customer_rec.p_org_contact_id;
781: END IF;
808: );
809:
810:
811: IF l_debug_level > 0 THEN
812: oe_debug_pub.add( 'count contact table = '||P_CONTACT_TBL.COUNT ) ;
813: END IF;
814: IF p_Contact_tbl.COUNT > 0 THEN
815: FOR i in p_contact_tbl.FIRST..p_contact_tbl.LAST
816: LOOP
814: IF p_Contact_tbl.COUNT > 0 THEN
815: FOR i in p_contact_tbl.FIRST..p_contact_tbl.LAST
816: LOOP
817: IF l_debug_level > 0 THEN
818: oe_debug_pub.add( 'contact_id = '||P_CONTACT_TBL ( I ) ) ;
819: END IF;
820: END LOOP;
821: END IF;
822:
821: END IF;
822:
823: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
824: IF l_debug_level > 0 THEN
825: oe_debug_pub.add( 'contact with error' ) ;
826: END IF;
827: l_contact_status := FND_API.G_RET_STS_ERROR;
828: x_msg_data_contact := x_msg_data;
829: x_msg_count_contact := x_msg_count;
837:
838: --If multiple Contact then return the contact table
839: IF p_contact_tbl.COUNT > 1 then
840: IF l_debug_level > 0 THEN
841: oe_debug_pub.add( 'returning with multiple contacts' ) ;
842: end IF;
843:
844: if p_control_rec.p_multiple_contact_is_error then
845: IF l_debug_level > 0 THEN
842: end IF;
843:
844: if p_control_rec.p_multiple_contact_is_error then
845: IF l_debug_level > 0 THEN
846: oe_debug_pub.add( 'erroring with multiple contacts' ) ;
847: END IF;
848: x_return_status := FND_API.G_RET_STS_ERROR;
849: else
850: x_return_status := FND_API.G_RET_STS_SUCCESS;
851: end if;
852: return;
853: ELSIF p_contact_tbl.COUNT = 0 then
854: IF l_debug_level > 0 THEN
855: oe_debug_pub.add( 'no contacts found error' ) ;
856: END IF;
857: x_return_status := FND_API.G_RET_STS_ERROR;
858: ELSIF p_contact_tbl.COUNT = 1 then
859: null;
860: END IF;
861:
862: ELSE
863: IF l_debug_level > 0 THEN
864: oe_debug_pub.add( 'not calling check contact' ) ;
865: END IF;
866:
867: END IF; -- if contact data is passed
868:
868:
869: ELSE -- if status is not success
870:
871: IF l_debug_level > 0 THEN
872: oe_debug_pub.add( '2 not calling check contact' ) ;
873: END IF;
874:
875: END IF; -- if status was successful
876:
876:
877:
878: IF p_control_rec.p_continue_processing_on_error then
879: IF l_debug_level > 0 THEN
880: oe_debug_pub.add( 'continue processing on error' ) ;
881: end if;
882: else
883: if l_debug_level > 0 then
884: oe_debug_pub.add( 'do not continue processing on error' ) ;
880: oe_debug_pub.add( 'continue processing on error' ) ;
881: end if;
882: else
883: if l_debug_level > 0 then
884: oe_debug_pub.add( 'do not continue processing on error' ) ;
885: END IF;
886: END IF;
887:
888: -- If status is not error and there are records in the site table
894:
895: FOR i IN p_site_tbl.FIRST..p_site_tbl.LAST LOOP
896:
897: IF l_debug_level > 0 THEN
898: oe_debug_pub.add ('AAC:VTI: ============ SITE CONTACT '||i||' of '||p_site_tbl.LAST||' =============== ');
899: oe_debug_pub.add( 'AAC: processing site#'||i||' for contacts');
900: END IF;
901:
902: l2_cust_account_id := l_cust_account_id;
895: FOR i IN p_site_tbl.FIRST..p_site_tbl.LAST LOOP
896:
897: IF l_debug_level > 0 THEN
898: oe_debug_pub.add ('AAC:VTI: ============ SITE CONTACT '||i||' of '||p_site_tbl.LAST||' =============== ');
899: oe_debug_pub.add( 'AAC: processing site#'||i||' for contacts');
900: END IF;
901:
902: l2_cust_account_id := l_cust_account_id;
903: l2_party_id := l_party_id;
907: OR nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM )
908: then
909: l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
910: l2_party_id := p_site_tbl(i).p_party_id;
911: oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
912: oe_debug_pub.add('site level party_id: '||l2_party_id);
913: end if;
914:
915: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
908: then
909: l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
910: l2_party_id := p_site_tbl(i).p_party_id;
911: oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
912: oe_debug_pub.add('site level party_id: '||l2_party_id);
913: end if;
914:
915: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
916: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
911: oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
912: oe_debug_pub.add('site level party_id: '||l2_party_id);
913: end if;
914:
915: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
916: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
917: if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
918: = FND_API.G_MISS_NUM) THEN
919: l_cust_account_role_id:=null;
912: oe_debug_pub.add('site level party_id: '||l2_party_id);
913: end if;
914:
915: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
916: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
917: if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
918: = FND_API.G_MISS_NUM) THEN
919: l_cust_account_role_id:=null;
920: else
919: l_cust_account_role_id:=null;
920: else
921: l_cust_account_role_id:=p_site_tbl(i).p_cust_account_role_id;
922: end if;
923: oe_debug_pub.add( 'AAC: After Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
924:
925:
926: if nvl(p_site_tbl(i).p_org_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
927: l2_org_contact_id := p_site_tbl(i).p_org_contact_id;
959: ,x_msg_data=>x_msg_data
960: );
961:
962: IF l_debug_level > 0 THEN
963: oe_debug_pub.add( 'AAC: ..done calling check_and_create_contacts ' ) ;
964: oe_debug_pub.add( 'count contact table = '||P2_CONTACT_TBL.COUNT ) ;
965: END IF;
966:
967: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
960: );
961:
962: IF l_debug_level > 0 THEN
963: oe_debug_pub.add( 'AAC: ..done calling check_and_create_contacts ' ) ;
964: oe_debug_pub.add( 'count contact table = '||P2_CONTACT_TBL.COUNT ) ;
965: END IF;
966:
967: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
968: IF l_debug_level > 0 THEN
965: END IF;
966:
967: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
968: IF l_debug_level > 0 THEN
969: oe_debug_pub.add( 'contact with error' ) ;
970: END IF;
971:
972: l_contact_status := FND_API.G_RET_STS_ERROR;
973: x_msg_data_contact := x_msg_data;
978: --If multiple Contact then return the contact table
979: IF p2_contact_tbl.COUNT > 1 then
980: if p_control_rec.p_multiple_contact_is_error then
981: IF l_debug_level > 0 THEN
982: oe_debug_pub.add( 'erroring with multiple contacts' ) ;
983: END IF;
984: x_return_status := FND_API.G_RET_STS_ERROR;
985: else
986: x_return_status := FND_API.G_RET_STS_SUCCESS;
987: end if;
988:
989: ELSIF p2_contact_tbl.COUNT = 0 then
990: IF l_debug_level > 0 THEN
991: oe_debug_pub.add( 'no contacts found error' ) ;
992: END IF;
993: x_return_status := FND_API.G_RET_STS_ERROR;
994: ELSIF p2_contact_tbl.COUNT = 1 then
995: p_site_tbl(i).p_cust_account_role_id := p2_contact_tbl(1);
996: END IF;
997: end loop;
998:
999: IF l_debug_level > 0 THEN
1000: oe_debug_pub.add( 'XX calling check_and_create_sites... ' ) ;
1001: END IF;
1002:
1003: -- check_and_Create_Sites should also valdiate against related
1004: -- customers now. and create their sites if ncessary
1016: G_fetch_primary_party_sites :=p_control_rec.p_fetch_primary_party_sites;
1017:
1018: IF l_debug_level > 0 THEN
1019: IF (G_fetch_primary_party_sites) THEN
1020: oe_debug_pub.add('G_fetch_primary_party_sites :TRUE');
1021: ELSE
1022: oe_debug_pub.add('G_fetch_primary_party_sites :FALSE');
1023: END IF;
1024: END IF;
1018: IF l_debug_level > 0 THEN
1019: IF (G_fetch_primary_party_sites) THEN
1020: oe_debug_pub.add('G_fetch_primary_party_sites :TRUE');
1021: ELSE
1022: oe_debug_pub.add('G_fetch_primary_party_sites :FALSE');
1023: END IF;
1024: END IF;
1025:
1026: /*cc project */
1035: ,x_msg_count=>x_msg_count
1036: );
1037:
1038: IF l_debug_level > 0 THEN
1039: oe_debug_pub.add( 'XX ...done calling check_and_create_sites ' ) ;
1040: END IF;
1041:
1042: ELSE
1043: IF l_debug_level > 0 THEN
1040: END IF;
1041:
1042: ELSE
1043: IF l_debug_level > 0 THEN
1044: oe_debug_pub.add( 'not calling check_and_create_sites X ' ) ;
1045: END IF;
1046:
1047: END IF; -- if site table exists
1048:
1051: -- however in telesales we will never commit so it is fine
1052: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1053: IF p_control_rec.p_commit THEN
1054: IF l_debug_level > 0 THEN
1055: oe_debug_pub.add( 'commiting the txn' ) ;
1056: end if;
1057: commit;
1058:
1059: end if;
1059: end if;
1060: else
1061:
1062: if l_debug_level > 0 then
1063: oe_debug_pub.add( 'not commiting the txn' ) ;
1064: END IF;
1065: l_site_failed := TRUE;
1066: OE_MSG_PUB.Count_And_Get
1067: ( p_count => x_msg_count
1074: -- THere might be failure in contact processing
1075: -- to show that message we send the status as error
1076: -- since in the client messages are displayed if only status is error
1077: IF l_debug_level > 0 THEN
1078: oe_debug_pub.add( ' checking contact failure status = '||l_contact_status ) ;
1079: END IF;
1080: IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1081: IF l_debug_level > 0 THEN
1082: oe_debug_pub.add( ' returning error for contact' ) ;
1078: oe_debug_pub.add( ' checking contact failure status = '||l_contact_status ) ;
1079: END IF;
1080: IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1081: IF l_debug_level > 0 THEN
1082: oe_debug_pub.add( ' returning error for contact' ) ;
1083: END IF;
1084: x_return_status := FND_API.G_RET_STS_ERROR;
1085:
1086: -- If site has failed then count and get above will catch that
1086: -- If site has failed then count and get above will catch that
1087: -- else to find the error message for contact we do this
1088: IF NOT l_site_failed then
1089: IF l_debug_level > 0 THEN
1090: oe_debug_pub.add( ' msg from contact only' ) ;
1091: END IF;
1092: x_msg_data := x_msg_data_contact;
1093: x_msg_count := x_msg_count_contact;
1094: END IF;
1093: x_msg_count := x_msg_count_contact;
1094: END IF;
1095:
1096: END IF;
1097: oe_debug_pub.add( 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
1098:
1099: EXCEPTION
1100:
1101: WHEN FND_API.G_EXC_ERROR THEN
1149: END IF;
1150:
1151: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1152: IF l_debug_level > 0 THEN
1153: oe_debug_pub.add( 'CREATE_ACCOUNT_LAYER WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
1154: END IF;
1155:
1156: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1157: THEN
1288: --l_site_party_site_use_id number := null;
1289: px_party_id number := null;
1290:
1291: --
1292: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
1293: --
1294: BEGIN
1295:
1296: px_party_id := p_party_id;
1300: x_return_status := FND_API.G_RET_STS_SUCCESS;
1301: l_return_status := FND_API.G_RET_STS_SUCCESS;
1302:
1303: IF l_debug_level > 0 THEN
1304: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1305: oe_debug_pub.add(' party_id = '||p_party_id);
1306: oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1307: END IF;
1308:
1301: l_return_status := FND_API.G_RET_STS_SUCCESS;
1302:
1303: IF l_debug_level > 0 THEN
1304: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1305: oe_debug_pub.add(' party_id = '||p_party_id);
1306: oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1307: END IF;
1308:
1309: IF p_cust_account_id is null then
1302:
1303: IF l_debug_level > 0 THEN
1304: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1305: oe_debug_pub.add(' party_id = '||p_party_id);
1306: oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1307: END IF;
1308:
1309: IF p_cust_account_id is null then
1310: x_return_status := FND_API.G_RET_STS_ERROR;
1308:
1309: IF p_cust_account_id is null then
1310: x_return_status := FND_API.G_RET_STS_ERROR;
1311: IF l_debug_level > 0 THEN
1312: oe_debug_pub.add( ' ERROR: cust account id must be sent to check sites' ) ;
1313: END IF;
1314:
1315: END IF;
1316:
1333: l_end_customer_passed :='N'; --added for bug 4240715
1334:
1335: x_return_status := FND_API.G_RET_STS_SUCCESS;
1336: IF l_debug_level > 0 THEN
1337: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1338: oe_debug_pub.add(' site table rec #'||i);
1339: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1340: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1341: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1334:
1335: x_return_status := FND_API.G_RET_STS_SUCCESS;
1336: IF l_debug_level > 0 THEN
1337: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1338: oe_debug_pub.add(' site table rec #'||i);
1339: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1340: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1341: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1342: END IF;
1335: x_return_status := FND_API.G_RET_STS_SUCCESS;
1336: IF l_debug_level > 0 THEN
1337: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1338: oe_debug_pub.add(' site table rec #'||i);
1339: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1340: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1341: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1342: END IF;
1343:
1336: IF l_debug_level > 0 THEN
1337: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1338: oe_debug_pub.add(' site table rec #'||i);
1339: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1340: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1341: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1342: END IF;
1343:
1344: if (p_site_tbl(i).p_process_site = FALSE) then
1337: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1338: oe_debug_pub.add(' site table rec #'||i);
1339: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1340: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1341: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1342: END IF;
1343:
1344: if (p_site_tbl(i).p_process_site = FALSE) then
1345: IF (l_debug_level > 0) THEN
1342: END IF;
1343:
1344: if (p_site_tbl(i).p_process_site = FALSE) then
1345: IF (l_debug_level > 0) THEN
1346: oe_debug_pub.add( ' p_process_site is NULL, skipping site processing..');
1347: end if;
1348: goto skip_site;
1349: end if;
1350:
1362: end if;
1363:
1364:
1365: IF l_debug_level > 0 THEN
1366: oe_debug_pub.add( ' site level customer: party_id:'||px_party_id ) ;
1367: oe_debug_pub.add( ' site level customer: account_id:'||px_cust_account_id ) ;
1368: END IF;
1369:
1370:
1363:
1364:
1365: IF l_debug_level > 0 THEN
1366: oe_debug_pub.add( ' site level customer: party_id:'||px_party_id ) ;
1367: oe_debug_pub.add( ' site level customer: account_id:'||px_cust_account_id ) ;
1368: END IF;
1369:
1370:
1371: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1384: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1385: then
1386: --nothing to do! return!
1387: IF l_debug_level > 0 THEN
1388: oe_debug_pub.add ('AAC: create_sites: no data passed in, likely a contact search; returning');
1389: END IF;
1390: return;
1391: end if;
1392:
1395: l_site_use_code := p_site_tbl(i).p_site_use_code;
1396: ELSE
1397: l_return_status := FND_API.G_RET_STS_ERROR;
1398: IF l_debug_level > 0 THEN
1399: oe_debug_pub.add( 'site use not specified' ) ;
1400: END IF;
1401: FND_MESSAGE.Set_Name('ONT','ONT_AAC_ERROR');
1402:
1403: FND_MESSAGE.Set_Token('TEXT','Usage of Account Site should be specified', FALSE);
1416: -- If site_use_id is passed then we validate this for the account
1417: IF p_site_tbl(i).p_site_use_id IS NOT NULL THEN
1418:
1419: IF l_debug_level > 0 THEN
1420: oe_debug_pub.add( ' checking site_use_id' ) ;
1421: END IF;
1422:
1423: --{added for bug 4240715
1424: -- added for end customer /* check done based on the order SOLD_TO,SHIP_TO,BILL_TO,DELIVER_TO */
1431: l_primary_site_use,
1432: l_cust_account_id;
1433:
1434: if c_site_use%FOUND then
1435: oe_debug_pub.add('Checking for end customer of type SOLD_TO');
1436: l_site_use_code :='SOLD_TO';
1437: l_site_use_id := p_site_tbl(i).p_site_use_id;
1438: px_cust_account_id := p_cust_account_id;
1439: px_party_id := p_party_id;
1439: px_party_id := p_party_id;
1440: if l_status <> 'A' then
1441: l_return_status := fnd_api.g_ret_sts_error;
1442: IF l_debug_level > 0 THEN
1443: oe_debug_pub.add( 'account site use is inactive' ) ;
1444: END IF;
1445: end if;
1446: /* IF l_cust_account_id <> px_cust_account_id then
1447: x_return_status := FND_API.G_RET_STS_ERROR;
1446: /* IF l_cust_account_id <> px_cust_account_id then
1447: x_return_status := FND_API.G_RET_STS_ERROR;
1448: l_return_status := FND_API.G_RET_STS_ERROR;
1449: IF l_debug_level > 0 THEN
1450: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1451: END IF;
1452: End if; */
1453: goto end_customer_found;
1454: Else -- not sold to
1462: l_primary_site_use,
1463: l_cust_account_id;
1464:
1465: if c_site_use%FOUND then
1466: oe_debug_pub.add('Checking for end customer of type SHIP_TO');
1467: l_site_use_code :='SHIP_TO';
1468: l_site_use_id := p_site_tbl(i).p_site_use_id;
1469: px_cust_account_id := p_cust_account_id;
1470: px_party_id := p_party_id;
1470: px_party_id := p_party_id;
1471: if l_status <> 'A' then
1472: l_return_status := fnd_api.g_ret_sts_error;
1473: IF l_debug_level > 0 THEN
1474: oe_debug_pub.add( 'account site use is inactive' ) ;
1475: END IF;
1476: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1477: OE_MSG_PUB.ADD;
1478: end if;
1479: /* IF l_cust_account_id <> px_cust_account_id then
1480: x_return_status := FND_API.G_RET_STS_ERROR;
1481: l_return_status := FND_API.G_RET_STS_ERROR;
1482: IF l_debug_level > 0 THEN
1483: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1484: END IF;
1485: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1486: OE_MSG_PUB.ADD;
1487: End if; */
1497: l_primary_site_use,
1498: l_cust_account_id;
1499:
1500: if c_site_use%FOUND then
1501: oe_debug_pub.add('Checking for end customer of type BILL_TO');
1502: l_site_use_code :='BILL_TO';
1503: l_site_use_id := p_site_tbl(i).p_site_use_id;
1504: px_cust_account_id := p_cust_account_id;
1505: px_party_id := p_party_id;
1505: px_party_id := p_party_id;
1506: if l_status <> 'A' then
1507: l_return_status := fnd_api.g_ret_sts_error;
1508: IF l_debug_level > 0 THEN
1509: oe_debug_pub.add( 'account site use is inactive' ) ;
1510: END IF;
1511: FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
1512: OE_MSG_PUB.ADD;
1513: end if;
1514: /* IF l_cust_account_id <> px_cust_account_id then
1515: x_return_status := FND_API.G_RET_STS_ERROR;
1516: l_return_status := FND_API.G_RET_STS_ERROR;
1517: IF l_debug_level > 0 THEN
1518: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1519: END IF;
1520: FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_ACCOUNT');
1521: OE_MSG_PUB.ADD;
1522: End if;*/
1532: l_primary_site_use,
1533: l_cust_account_id;
1534:
1535: if c_site_use%FOUND then
1536: oe_debug_pub.add('Checking for end customer of type DELIVER_TO');
1537: l_site_use_code :='DELIVER_TO';
1538: l_site_use_id := p_site_tbl(i).p_site_use_id;
1539: px_cust_account_id := p_cust_account_id;
1540: px_party_id := p_party_id;
1540: px_party_id := p_party_id;
1541: if l_status <> 'A' then
1542: l_return_status := fnd_api.g_ret_sts_error;
1543: IF l_debug_level > 0 THEN
1544: oe_debug_pub.add( 'account site use is inactive' ) ;
1545: END IF;
1546: FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
1547: OE_MSG_PUB.ADD;
1548: end if;
1549: /* IF l_cust_account_id <> px_cust_account_id then
1550: x_return_status := FND_API.G_RET_STS_ERROR;
1551: l_return_status := FND_API.G_RET_STS_ERROR;
1552: IF l_debug_level > 0 THEN
1553: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1554: END IF;
1555: FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_ACCOUNT');
1556: OE_MSG_PUB.ADD;
1557: End if; */
1576:
1577: IF c_site_use%FOUND THEN
1578:
1579: IF l_debug_level > 0 THEN
1580: oe_debug_pub.add('found site_use_id acct_site_id = '||l_cust_acct_site_id|| ' status='||l_status);
1581: oe_debug_pub.add(' primary = '||l_primary_site_use|| ' cust_account_id = '||l_cust_account_id ) ;
1582: end if;
1583:
1584: if l_status <> 'A' then
1577: IF c_site_use%FOUND THEN
1578:
1579: IF l_debug_level > 0 THEN
1580: oe_debug_pub.add('found site_use_id acct_site_id = '||l_cust_acct_site_id|| ' status='||l_status);
1581: oe_debug_pub.add(' primary = '||l_primary_site_use|| ' cust_account_id = '||l_cust_account_id ) ;
1582: end if;
1583:
1584: if l_status <> 'A' then
1585: l_return_status := fnd_api.g_ret_sts_error;
1583:
1584: if l_status <> 'A' then
1585: l_return_status := fnd_api.g_ret_sts_error;
1586: IF l_debug_level > 0 THEN
1587: oe_debug_pub.add( 'account site use is inactive' ) ;
1588: END IF;
1589:
1590: IF l_site_use_code = 'SHIP_TO' then
1591: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1597:
1598: OE_MSG_PUB.ADD;
1599: END IF;
1600:
1601: oe_debug_pub.add( 'px_accoun_id:'||px_cust_account_id||'l_accoun_id:'||l_cust_account_id ) ;
1602: /* IF l_cust_account_id <> px_cust_account_id then
1603: x_return_status := FND_API.G_RET_STS_ERROR;
1604: l_return_status := FND_API.G_RET_STS_ERROR;
1605: IF l_debug_level > 0 THEN
1602: /* IF l_cust_account_id <> px_cust_account_id then
1603: x_return_status := FND_API.G_RET_STS_ERROR;
1604: l_return_status := FND_API.G_RET_STS_ERROR;
1605: IF l_debug_level > 0 THEN
1606: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1607: END IF;
1608:
1609: IF l_site_use_code = 'SHIP_TO' then
1610: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1623: ELSIF c_site_use%NOTFOUND THEN
1624: x_return_status := FND_API.G_RET_STS_ERROR;
1625: l_return_status := FND_API.G_RET_STS_ERROR;
1626: IF l_debug_level > 0 THEN
1627: oe_debug_pub.add( 'INVALID SITE_USE_ID ' ) ;
1628: END IF;
1629:
1630: IF l_site_use_code = 'SHIP_TO' then
1631: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_SHIPTO');
1642: END IF; -- site_ use_code (bug 4240715)
1643: ELSE -- if site_use_id is null
1644:
1645: IF l_debug_level > 0 THEN
1646: oe_debug_pub.add( ' site_use_id not passed' ) ;
1647: END IF;
1648: /*cc project, based on the g_fech_parimary_party_sites value the logic for check_and_create
1649: sites will execute. If it is true then we will ignore the addresses passed to us and instead
1650: fetch and primary party sites and related account sites. If primary party sites are found and no corresponding
1663:
1664: l_party_site_id_cc :=l_party_site_id_rec.party_site_id;
1665:
1666: IF l_debug_level > 0 THEN
1667: oe_debug_pub.add('cc Party site id :'|| l_party_site_id_cc);
1668: oe_debug_pub.add('searching whether party site usage is primary or not');
1669: END IF;
1670:
1671: OPEN c_prim_party_site_use(l_party_site_id_cc,p_site_tbl(i).p_site_use_code);
1664: l_party_site_id_cc :=l_party_site_id_rec.party_site_id;
1665:
1666: IF l_debug_level > 0 THEN
1667: oe_debug_pub.add('cc Party site id :'|| l_party_site_id_cc);
1668: oe_debug_pub.add('searching whether party site usage is primary or not');
1669: END IF;
1670:
1671: OPEN c_prim_party_site_use(l_party_site_id_cc,p_site_tbl(i).p_site_use_code);
1672: FETCH c_prim_party_site_use
1674:
1675:
1676: IF c_prim_party_site_use%NOTFOUND THEN
1677: IF l_debug_level > 0 THEN
1678: oe_debug_pub.add('no primary party site use of type :'||p_site_tbl(i).p_site_use_code||' of party_site_id:'||l_party_site_id_cc);
1679: END IF;
1680: ELSIF c_prim_party_site_use%FOUND THEN
1681: p_site_tbl(i).p_party_site_use_id :=l_party_site_use_id;
1682: l_party_site_id :=l_party_site_id_cc;
1680: ELSIF c_prim_party_site_use%FOUND THEN
1681: p_site_tbl(i).p_party_site_use_id :=l_party_site_use_id;
1682: l_party_site_id :=l_party_site_id_cc;
1683: IF l_debug_level > 0 THEN
1684: oe_debug_pub.add(' primary party site use of type :'||p_site_tbl(i).p_site_use_code||' of party_site_id:'||l_party_site_id_cc||' is found,party_site_use_id is'||l_party_site_use_id);
1685: oe_debug_pub.add('exiting from loop to search primary party site uses');
1686: END IF;
1687: Close c_prim_party_site_use;
1688: exit;
1681: p_site_tbl(i).p_party_site_use_id :=l_party_site_use_id;
1682: l_party_site_id :=l_party_site_id_cc;
1683: IF l_debug_level > 0 THEN
1684: oe_debug_pub.add(' primary party site use of type :'||p_site_tbl(i).p_site_use_code||' of party_site_id:'||l_party_site_id_cc||' is found,party_site_use_id is'||l_party_site_use_id);
1685: oe_debug_pub.add('exiting from loop to search primary party site uses');
1686: END IF;
1687: Close c_prim_party_site_use;
1688: exit;
1689:
1705: */
1706: -- if party_site_id is sent
1707: IF l_party_site_id is null AND
1708: x_return_status <> FND_API.G_RET_STS_ERROR then
1709: oe_debug_pub.add( 'l_party_site_id is null and p_site_tbl(i).p_party_site_id is '||p_site_tbl(i).p_party_site_id);
1710: IF p_site_tbl(i).p_party_site_id is not null then
1711: IF l_debug_level > 0 THEN
1712: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1713: END IF;
1708: x_return_status <> FND_API.G_RET_STS_ERROR then
1709: oe_debug_pub.add( 'l_party_site_id is null and p_site_tbl(i).p_party_site_id is '||p_site_tbl(i).p_party_site_id);
1710: IF p_site_tbl(i).p_party_site_id is not null then
1711: IF l_debug_level > 0 THEN
1712: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1713: END IF;
1714:
1715: l_party_site_id := p_site_tbl(i).p_party_site_id;
1716:
1722: IF c_party_site%NOTFOUND THEN
1723: l_return_status := FND_API.G_RET_STS_ERROR;
1724: x_return_status := FND_API.G_RET_STS_ERROR;
1725: IF l_debug_level > 0 THEN
1726: oe_debug_pub.add( 'no such party site id' ) ;
1727: END IF;
1728: CLOSE c_party_site;
1729:
1730: IF l_site_use_code = 'SHIP_TO' then
1748:
1749: ELSIF c_party_site%FOUND THEN
1750:
1751: IF l_debug_level > 0 THEN
1752: oe_debug_pub.add( 'found for party_site_id ' ) ;
1753: END IF;
1754:
1755: IF l_status <> 'A' THEN
1756:
1766: END IF;
1767:
1768: OE_MSG_PUB.ADD;
1769: IF l_debug_level > 0 THEN
1770: oe_debug_pub.add( 'party site is not active' ) ;
1771: END IF;
1772: CLOSE c_party_site;
1773: IF p_continue_on_error THEN
1774: null;
1781: END IF;
1782:
1783: END IF; -- if status is not active
1784:
1785: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1786:
1787: IF px_party_id <> l_party_id THEN
1788: x_return_status := FND_API.G_RET_STS_ERROR;
1789: l_return_status := FND_API.G_RET_STS_ERROR;
1797: END IF;
1798:
1799: OE_MSG_PUB.ADD;
1800: IF l_debug_level > 0 THEN
1801: oe_debug_pub.add( 'party site does not belong to the party' ) ;
1802: END IF;
1803:
1804: CLOSE c_party_site;
1805: IF p_continue_on_error THEN
1819:
1820: END IF; -- if party_site_id found
1821:
1822: ELSE -- if party_site_id is null
1823: oe_debug_pub.add( 'Yes.. Party_Site_id is not null');
1824: if l_end_customer_passed ='N' then -- if its not end customer(4240715)
1825: x_return_status := FND_API.G_RET_STS_ERROR;
1826: l_return_status := FND_API.G_RET_STS_ERROR;
1827: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1827: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1828: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
1829: OE_MSG_PUB.ADD;
1830: IF l_debug_level > 0 THEN
1831: oe_debug_pub.add( 'no site information is sent ' ) ;
1832: END IF;
1833: IF p_continue_on_error THEN
1834: null;
1835: ELSE
1842: end if; -- as long as its not end customer(4240715)
1843: END IF; -- if party_site_id not null
1844: END IF; -- if l_party_site_id is null
1845: ELSE
1846: oe_debug_pub.add('Have got the primary_party_site_use_id');
1847: END IF;
1848:
1849:
1850: ELSE --G_fetch_primary_party_sites =FALSE
1856: -- party_site_id and site_use_code should be passed
1857: IF p_site_tbl(i).p_party_site_use_id IS NOT NULL THEN
1858:
1859: IF l_debug_level > 0 THEN
1860: oe_debug_pub.add( 'checking for party_site_use_id ='|| P_SITE_TBL ( I ) .P_PARTY_SITE_USE_ID ) ;
1861: END IF;
1862: OPEN c_party_site_use(p_site_tbl(i).p_party_site_use_id);
1863: FETCH c_party_site_use
1864: INTO l_site_use_code,
1868: IF c_party_site_use%NOTFOUND THEN
1869: l_return_status := FND_API.G_RET_STS_ERROR;
1870: x_return_status := FND_API.G_RET_STS_ERROR;
1871: IF l_debug_level > 0 THEN
1872: oe_debug_pub.add( 'not a valid party site use id' ) ;
1873: END IF;
1874:
1875: IF l_site_use_code = 'SHIP_TO' then
1876: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_SHIPTO');
1896: p_site_tbl(i).p_party_site_id := l_party_site_id;
1897: END IF;
1898:
1899: IF l_debug_level > 0 THEN
1900: oe_debug_pub.add( ' site_use_code = '||L_SITE_USE_CODE ) ;
1901: END IF;
1902: -- site use code has to be passed in the control record
1903: -- because we can make a party site as a account site of bill,ship,deliver
1904: -- even if the party site use is STMNTS or others
1914: IF l_party_site_id is null AND
1915: x_return_status <> FND_API.G_RET_STS_ERROR then
1916: IF p_site_tbl(i).p_party_site_id is not null then
1917: IF l_debug_level > 0 THEN
1918: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1919: END IF;
1920: l_party_site_id := p_site_tbl(i).p_party_site_id;
1921:
1922: OPEN c_party_site(l_party_site_id);
1926: IF c_party_site%NOTFOUND THEN
1927: l_return_status := FND_API.G_RET_STS_ERROR;
1928: x_return_status := FND_API.G_RET_STS_ERROR;
1929: IF l_debug_level > 0 THEN
1930: oe_debug_pub.add( 'no such party site id' ) ;
1931: END IF;
1932: CLOSE c_party_site;
1933:
1934: IF l_site_use_code = 'SHIP_TO' then
1951: END IF;
1952: ELSIF c_party_site%FOUND THEN
1953:
1954: IF l_debug_level > 0 THEN
1955: oe_debug_pub.add( 'found for party_site_id ' ) ;
1956: END IF;
1957: IF l_status <> 'A' THEN
1958:
1959: x_return_status := FND_API.G_RET_STS_ERROR;
1968: END IF;
1969:
1970: OE_MSG_PUB.ADD;
1971: IF l_debug_level > 0 THEN
1972: oe_debug_pub.add( 'party site is not active' ) ;
1973: END IF;
1974: CLOSE c_party_site;
1975: IF p_continue_on_error THEN
1976: null;
1983: END IF;
1984:
1985: END IF; -- if status is not active
1986:
1987: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1988:
1989: IF px_party_id <> l_party_id THEN
1990: x_return_status := FND_API.G_RET_STS_ERROR;
1991: l_return_status := FND_API.G_RET_STS_ERROR;
1999: END IF;
2000:
2001: OE_MSG_PUB.ADD;
2002: IF l_debug_level > 0 THEN
2003: oe_debug_pub.add( 'party site does not belong to the party' ) ;
2004: END IF;
2005: CLOSE c_party_site;
2006: IF p_continue_on_error THEN
2007: null;
2020:
2021: END IF; -- if party_site_id found
2022:
2023: ELSE -- if party_site_id is null
2024: oe_debug_pub.add( 'Yes.. Party_Site_id is not null.. Second');
2025: if l_end_customer_passed ='N' then -- if its not end customer(bug 4240715)
2026: x_return_status := FND_API.G_RET_STS_ERROR;
2027: l_return_status := FND_API.G_RET_STS_ERROR;
2028: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
2028: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
2029: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
2030: OE_MSG_PUB.ADD;
2031: IF l_debug_level > 0 THEN
2032: oe_debug_pub.add( 'no site information is sent ' ) ;
2033: END IF;
2034: IF p_continue_on_error THEN
2035: null;
2036: ELSE
2044: END IF; -- if party_site_id not null
2045: END IF; -- if l_party_site_id is null
2046:
2047: IF l_debug_level > 0 THEN
2048: oe_debug_pub.add( ' l_party_site_id ='||l_party_site_id|| ' l_site_use_code ='||L_SITE_USE_CODE ) ;
2049: END IF;
2050:
2051:
2052: END IF; --G_fetch_primary_party_sites
2060: FETCH c_endcust_party_site_use
2061: INTO lx_party_site_use_id;
2062:
2063: if c_endcust_party_site_use%FOUND then
2064: oe_debug_pub.add('Checking for end customer of type SOLD_TO');
2065: l_site_use_code :='SOLD_TO';
2066: goto end_customer_site_use_found;
2067: Else -- not sold to
2068: close c_endcust_party_site_use;
2072: FETCH c_endcust_party_site_use
2073: INTO lx_party_site_use_id;
2074:
2075: if c_endcust_party_site_use%FOUND then
2076: oe_debug_pub.add('Checking for end customer of type SHIP_TO');
2077: l_site_use_code :='SHIP_TO';
2078: goto end_customer_site_use_found;
2079: Else -- not sold to
2080: close c_endcust_party_site_use;
2083: FETCH c_endcust_party_site_use
2084: INTO lx_party_site_use_id;
2085:
2086: if c_endcust_party_site_use%FOUND then
2087: oe_debug_pub.add('Checking for end customer of type BILL_TO');
2088: l_site_use_code :='BILL_TO';
2089: goto end_customer_site_use_found;
2090: Else -- not sold to
2091: close c_endcust_party_site_use;
2094: FETCH c_endcust_party_site_use
2095: INTO lx_party_site_use_id;
2096:
2097: if c_endcust_party_site_use%FOUND then
2098: oe_debug_pub.add('Checking for end customer of type DELIVER_TO');
2099: l_site_use_code :='DELIVER_TO';
2100: goto end_customer_site_use_found;
2101: Else -- not sold to
2102: close c_endcust_party_site_use;
2100: goto end_customer_site_use_found;
2101: Else -- not sold to
2102: close c_endcust_party_site_use;
2103: end if; -- sold to check;
2104: oe_debug_pub.add('Site use code selected for end customer is'||l_site_use_code);
2105: END IF;
2106: --------------------------------
2107:
2108: <
2111: IF x_return_status = FND_API.G_RET_STS_SUCCESS and l_site_use_code <>'END_CUST' THEN
2112:
2113:
2114: IF l_debug_level > 0 THEN
2115: oe_debug_pub.add( ' continuing processing' ) ;
2116: end if;
2117: -- fetch the account_site_id
2118: IF p_site_tbl(i).p_create_primary_acct_site_use then
2119: l_send_primary := 'Y';
2128: l_site_use_primary_flag;
2129: IF c_acct_site%FOUND THEN
2130:
2131: IF l_debug_level > 0 THEN
2132: oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2133: END IF;
2134: IF l_site_use_id IS NULL THEN
2135: IF l_debug_level > 0 THEN
2136: oe_debug_pub.add( 'make a call to create account site use' ) ;
2132: oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2133: END IF;
2134: IF l_site_use_id IS NULL THEN
2135: IF l_debug_level > 0 THEN
2136: oe_debug_pub.add( 'make a call to create account site use' ) ;
2137: END IF;
2138: -- make a call to create account site use
2139:
2140: IF p_allow_site_creation then
2178: );
2179: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2180: CLOSE c_acct_site;
2181: IF l_debug_level > 0 THEN
2182: oe_debug_pub.add( ' account site use creation failed' ) ;
2183: END IF;
2184: IF l_debug_level > 0 THEN
2185: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2186: END IF;
2181: IF l_debug_level > 0 THEN
2182: oe_debug_pub.add( ' account site use creation failed' ) ;
2183: END IF;
2184: IF l_debug_level > 0 THEN
2185: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2186: END IF;
2187:
2188: x_return_status := FND_API.G_RET_STS_ERROR;
2189: l_return_status := FND_API.G_RET_STS_ERROR;
2215: l_return_status := FND_API.G_RET_STS_ERROR;
2216: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2217: OE_MSG_PUB.ADD;
2218: IF l_debug_level > 0 THEN
2219: oe_debug_pub.add( ' not authorized to create site' ) ;
2220: END IF;
2221: CLOSE c_acct_site;
2222: IF p_continue_on_error THEN
2223: null;
2230: ELSIF c_acct_site%NOTFOUND THEN
2231:
2232: -- make a call to create acct site and account site use
2233: IF l_debug_level > 0 THEN
2234: oe_debug_pub.add( 'make a call to create acct site and use' ) ;
2235: END IF;
2236:
2237: IF p_allow_site_creation THEN
2238:
2236:
2237: IF p_allow_site_creation THEN
2238:
2239: IF l_debug_level > 0 THEN
2240: oe_debug_pub.add( ' creating account site' ) ;
2241: end IF;
2242: oe_oe_inline_address.Create_Account_Site
2243: (
2244: p_cust_account_id =>px_cust_account_id,
2273: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2274: CLOSE c_acct_site;
2275: l_return_status := FND_API.G_RET_STS_ERROR;
2276: IF l_debug_level > 0 THEN
2277: oe_debug_pub.add( ' account site creation failed' ) ;
2278: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2279: END IF;
2280: IF x_msg_count = 1 then
2281:
2274: CLOSE c_acct_site;
2275: l_return_status := FND_API.G_RET_STS_ERROR;
2276: IF l_debug_level > 0 THEN
2277: oe_debug_pub.add( ' account site creation failed' ) ;
2278: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2279: END IF;
2280: IF x_msg_count = 1 then
2281:
2282: IF l_site_use_code = 'SHIP_TO' then
2300:
2301: ELSE
2302:
2303: IF l_debug_level > 0 THEN
2304: oe_debug_pub.add( ' acct_site_id created = '||L_CUST_ACCT_SITE_ID ) ;
2305: END IF;
2306: oe_oe_inline_address.Create_Acct_Site_Uses
2307: (
2308: p_cust_acct_site_id =>l_cust_acct_site_id,
2345: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2346: l_return_status := FND_API.G_RET_STS_ERROR;
2347: CLOSE c_acct_site;
2348: IF l_debug_level > 0 THEN
2349: oe_debug_pub.add( ' account site use creation failed' ) ;
2350: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2351: END IF;
2352: IF x_msg_count = 1 then
2353: IF l_debug_level > 0 THEN
2346: l_return_status := FND_API.G_RET_STS_ERROR;
2347: CLOSE c_acct_site;
2348: IF l_debug_level > 0 THEN
2349: oe_debug_pub.add( ' account site use creation failed' ) ;
2350: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2351: END IF;
2352: IF x_msg_count = 1 then
2353: IF l_debug_level > 0 THEN
2354: oe_debug_pub.add( ' adding to message stack' ) ;
2350: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2351: END IF;
2352: IF x_msg_count = 1 then
2353: IF l_debug_level > 0 THEN
2354: oe_debug_pub.add( ' adding to message stack' ) ;
2355: END IF;
2356:
2357: IF l_site_use_code = 'SHIP_TO' then
2358: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2365: FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2366: OE_MSG_PUB.ADD;
2367: ELSE
2368: IF l_debug_level > 0 THEN
2369: oe_debug_pub.add( ' transferring to message stack' ) ;
2370: END IF;
2371: oe_msg_pub.transfer_msg_stack;
2372: END IF;
2373: IF p_continue_on_error THEN
2386: l_return_status := FND_API.G_RET_STS_ERROR;
2387: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2388: OE_MSG_PUB.ADD;
2389: IF l_debug_level > 0 THEN
2390: oe_debug_pub.add( '2 not allowed to create site' ) ;
2391: END IF;
2392: CLOSE c_acct_site;
2393: IF p_continue_on_error THEN
2394: null;
2406:
2407: END IF; -- if status is success
2408:
2409: IF l_debug_level > 0 THEN
2410: oe_debug_pub.add( ' assinging site table site_id = '||l_site_use_id|| ' acct site_id = '||L_CUST_ACCT_SITE_ID ) ;
2411: END IF;
2412:
2413:
2414: -- END IF; -- if site_use_id is passed
2422:
2423: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2424:
2425: IF l_debug_level > 0 THEN
2426: oe_debug_pub.add( ' continuing processing' ) ;
2427: end if;
2428: -- fetch the account_site_id
2429: IF p_site_tbl(i).p_create_primary_acct_site_use then
2430: l_send_primary := 'Y';
2439: l_site_use_primary_flag;
2440: IF c_acct_site%FOUND THEN
2441:
2442: IF l_debug_level > 0 THEN
2443: oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2444: END IF;
2445: IF l_site_use_id IS NULL THEN
2446: IF l_debug_level > 0 THEN
2447: oe_debug_pub.add( 'make a call to create account site use' ) ;
2443: oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2444: END IF;
2445: IF l_site_use_id IS NULL THEN
2446: IF l_debug_level > 0 THEN
2447: oe_debug_pub.add( 'make a call to create account site use' ) ;
2448: END IF;
2449: -- make a call to create account site use
2450:
2451: IF p_allow_site_creation then
2489: );
2490: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2491: CLOSE c_acct_site;
2492: IF l_debug_level > 0 THEN
2493: oe_debug_pub.add( ' account site use creation failed' ) ;
2494: END IF;
2495: IF l_debug_level > 0 THEN
2496: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2497: END IF;
2492: IF l_debug_level > 0 THEN
2493: oe_debug_pub.add( ' account site use creation failed' ) ;
2494: END IF;
2495: IF l_debug_level > 0 THEN
2496: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2497: END IF;
2498:
2499: x_return_status := FND_API.G_RET_STS_ERROR;
2500: l_return_status := FND_API.G_RET_STS_ERROR;
2526: l_return_status := FND_API.G_RET_STS_ERROR;
2527: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2528: OE_MSG_PUB.ADD;
2529: IF l_debug_level > 0 THEN
2530: oe_debug_pub.add( ' not authorized to create site' ) ;
2531: END IF;
2532: CLOSE c_acct_site;
2533: IF p_continue_on_error THEN
2534: null;
2541: ELSIF c_acct_site%NOTFOUND THEN
2542:
2543: -- make a call to create acct site and account site use
2544: IF l_debug_level > 0 THEN
2545: oe_debug_pub.add( 'make a call to create acct site and use' ) ;
2546: END IF;
2547:
2548: IF p_allow_site_creation THEN
2549:
2547:
2548: IF p_allow_site_creation THEN
2549:
2550: IF l_debug_level > 0 THEN
2551: oe_debug_pub.add( ' creating account site' ) ;
2552: end IF;
2553: oe_oe_inline_address.Create_Account_Site
2554: (
2555: p_cust_account_id =>px_cust_account_id,
2584: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2585: CLOSE c_acct_site;
2586: l_return_status := FND_API.G_RET_STS_ERROR;
2587: IF l_debug_level > 0 THEN
2588: oe_debug_pub.add( ' account site creation failed' ) ;
2589: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2590: END IF;
2591: IF x_msg_count = 1 then
2592:
2585: CLOSE c_acct_site;
2586: l_return_status := FND_API.G_RET_STS_ERROR;
2587: IF l_debug_level > 0 THEN
2588: oe_debug_pub.add( ' account site creation failed' ) ;
2589: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2590: END IF;
2591: IF x_msg_count = 1 then
2592:
2593: IF l_site_use_code = 'SHIP_TO' then
2611:
2612: ELSE
2613:
2614: IF l_debug_level > 0 THEN
2615: oe_debug_pub.add( ' acct_site_id created = '||L_CUST_ACCT_SITE_ID ) ;
2616: END IF;
2617: oe_oe_inline_address.Create_Acct_Site_Uses
2618: (
2619: p_cust_acct_site_id =>l_cust_acct_site_id,
2656: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2657: l_return_status := FND_API.G_RET_STS_ERROR;
2658: CLOSE c_acct_site;
2659: IF l_debug_level > 0 THEN
2660: oe_debug_pub.add( ' account site use creation failed' ) ;
2661: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2662: END IF;
2663: IF x_msg_count = 1 then
2664: IF l_debug_level > 0 THEN
2657: l_return_status := FND_API.G_RET_STS_ERROR;
2658: CLOSE c_acct_site;
2659: IF l_debug_level > 0 THEN
2660: oe_debug_pub.add( ' account site use creation failed' ) ;
2661: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2662: END IF;
2663: IF x_msg_count = 1 then
2664: IF l_debug_level > 0 THEN
2665: oe_debug_pub.add( ' adding to message stack' ) ;
2661: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2662: END IF;
2663: IF x_msg_count = 1 then
2664: IF l_debug_level > 0 THEN
2665: oe_debug_pub.add( ' adding to message stack' ) ;
2666: END IF;
2667:
2668: IF l_site_use_code = 'SHIP_TO' then
2669: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2676: FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2677: OE_MSG_PUB.ADD;
2678: ELSE
2679: IF l_debug_level > 0 THEN
2680: oe_debug_pub.add( ' transferring to message stack' ) ;
2681: END IF;
2682: oe_msg_pub.transfer_msg_stack;
2683: END IF;
2684: IF p_continue_on_error THEN
2697: l_return_status := FND_API.G_RET_STS_ERROR;
2698: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2699: OE_MSG_PUB.ADD;
2700: IF l_debug_level > 0 THEN
2701: oe_debug_pub.add( '2 not allowed to create site' ) ;
2702: END IF;
2703: CLOSE c_acct_site;
2704: IF p_continue_on_error THEN
2705: null;
2717:
2718: END IF; -- if status is success
2719:
2720: IF l_debug_level > 0 THEN
2721: oe_debug_pub.add( ' assinging site table site_id = '||l_site_use_id|| ' acct site_id = '||L_CUST_ACCT_SITE_ID ) ;
2722: END IF;
2723: END IF; -- if site_use_id is passed
2724:
2725: <
2727:
2728:
2729:
2730: IF l_debug_level > 0 THEN
2731: oe_debug_pub.add( ' site not success so copying null' ) ;
2732: END IF;
2733: p_site_tbl(i).p_site_use_id := null;
2734: p_site_tbl(i).p_cust_acct_site_id := null;
2735: ELSE
2740:
2741: IF C_get_cust_from_site_use_id%FOUND THEN
2742:
2743: IF l_debug_level > 0 THEN
2744: oe_debug_pub.add( ' account site found = '||p_site_tbl(i).p_cust_account_id ) ;
2745: END IF;
2746: END IF;
2747: close C_get_cust_from_site_use_id;
2748:
2746: END IF;
2747: close C_get_cust_from_site_use_id;
2748:
2749: IF l_debug_level > 0 THEN
2750: oe_debug_pub.add( ' site success so copying actual' ) ;
2751: END IF;
2752:
2753: p_site_tbl(i).p_site_use_id := l_site_use_id;
2754: p_site_tbl(i).p_cust_acct_site_id := l_cust_acct_site_id;
2764: x_return_status := l_return_status;
2765:
2766: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2767: IF l_debug_level > 0 THEN
2768: oe_debug_pub.add( ' doing count and get for site' ) ;
2769: END IF;
2770: OE_MSG_PUB.Count_And_Get
2771: ( p_count => x_msg_count
2772: , p_data => x_msg_data
2772: , p_data => x_msg_data
2773: );
2774: END IF;
2775: IF l_debug_level > 0 THEN
2776: oe_debug_pub.add( ' at end of site msg = '||x_msg_data|| ' count = '||x_MSG_COUNT ) ;
2777: END IF;
2778:
2779: EXCEPTION
2780:
2832: CLOSE c_acct_site;
2833: END IF;
2834: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2835: IF l_debug_level > 0 THEN
2836: oe_debug_pub.add( 'check_and_create_site when other code = '|| sqlcode||' message = '||sqlerrm ) ;
2837: END IF;
2838:
2839: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2840: THEN
2863: ,out_cust_account_role_id out NOCOPY number
2864: ) IS
2865:
2866: --
2867: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
2868: --
2869: BEGIN
2870:
2871: null;
2972: px_party_id number := null;
2973: px_cust_account_id number := null;
2974:
2975: --
2976: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
2977: l_indent varchar2(5) := ' ';
2978: --
2979: BEGIN
2980: l_debug_level:=1;
2978: --
2979: BEGIN
2980: l_debug_level:=1;
2981: IF l_debug_level > 0 THEN
2982: oe_debug_pub.add( 'entering check_and_create_contact ');
2983: oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2984: oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2985: oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2986: p_cust_account_site_id ) ;
2979: BEGIN
2980: l_debug_level:=1;
2981: IF l_debug_level > 0 THEN
2982: oe_debug_pub.add( 'entering check_and_create_contact ');
2983: oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2984: oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2985: oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2986: p_cust_account_site_id ) ;
2987: END IF;
2980: l_debug_level:=1;
2981: IF l_debug_level > 0 THEN
2982: oe_debug_pub.add( 'entering check_and_create_contact ');
2983: oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2984: oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2985: oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2986: p_cust_account_site_id ) ;
2987: END IF;
2988:
2981: IF l_debug_level > 0 THEN
2982: oe_debug_pub.add( 'entering check_and_create_contact ');
2983: oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2984: oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2985: oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2986: p_cust_account_site_id ) ;
2987: END IF;
2988:
2989: IF l_debug_level > 0 THEN
2987: END IF;
2988:
2989: IF l_debug_level > 0 THEN
2990: if p_multiple_contact_is_error then
2991: oe_debug_pub.add(l_indent|| 'multiple contact is error' ) ;
2992: else
2993: oe_debug_pub.add( l_indent|| 'multiple contact is not error' ) ;
2994: end if;
2995:
2989: IF l_debug_level > 0 THEN
2990: if p_multiple_contact_is_error then
2991: oe_debug_pub.add(l_indent|| 'multiple contact is error' ) ;
2992: else
2993: oe_debug_pub.add( l_indent|| 'multiple contact is not error' ) ;
2994: end if;
2995:
2996: if p_assign_contact_to_site then
2997: oe_debug_pub.add( l_indent|| 'assign_contact_to_site ' ) ;
2993: oe_debug_pub.add( l_indent|| 'multiple contact is not error' ) ;
2994: end if;
2995:
2996: if p_assign_contact_to_site then
2997: oe_debug_pub.add( l_indent|| 'assign_contact_to_site ' ) ;
2998: else
2999: oe_debug_pub.add(l_indent|| 'not assign_contact_to_site ' ) ;
3000: end if;
3001:
2995:
2996: if p_assign_contact_to_site then
2997: oe_debug_pub.add( l_indent|| 'assign_contact_to_site ' ) ;
2998: else
2999: oe_debug_pub.add(l_indent|| 'not assign_contact_to_site ' ) ;
3000: end if;
3001:
3002: if p_create_responsibility then
3003: oe_debug_pub.add(l_indent|| 'create responsibility ' ) ;
2999: oe_debug_pub.add(l_indent|| 'not assign_contact_to_site ' ) ;
3000: end if;
3001:
3002: if p_create_responsibility then
3003: oe_debug_pub.add(l_indent|| 'create responsibility ' ) ;
3004: else
3005: oe_debug_pub.add(l_indent|| 'not create responsibility ' ) ;
3006: end if;
3007:
3001:
3002: if p_create_responsibility then
3003: oe_debug_pub.add(l_indent|| 'create responsibility ' ) ;
3004: else
3005: oe_debug_pub.add(l_indent|| 'not create responsibility ' ) ;
3006: end if;
3007:
3008: if p_allow_contact_creation then
3009: oe_debug_pub.add( l_indent|| 'allow_contact_creation ' ) ;
3005: oe_debug_pub.add(l_indent|| 'not create responsibility ' ) ;
3006: end if;
3007:
3008: if p_allow_contact_creation then
3009: oe_debug_pub.add( l_indent|| 'allow_contact_creation ' ) ;
3010: else
3011: oe_debug_pub.add( l_indent|| 'not allow_contact_creation ' ) ;
3012: END IF;
3013: END IF;
3007:
3008: if p_allow_contact_creation then
3009: oe_debug_pub.add( l_indent|| 'allow_contact_creation ' ) ;
3010: else
3011: oe_debug_pub.add( l_indent|| 'not allow_contact_creation ' ) ;
3012: END IF;
3013: END IF;
3014:
3015:
3022: IF p_cust_account_id is null AND
3023: p_cust_account_role_id is null then
3024: x_return_status := FND_API.G_RET_STS_ERROR;
3025: IF l_debug_level > 0 THEN
3026: oe_debug_pub.add(l_indent|| ' not a valid call to check contact' ) ;
3027: END IF;
3028: return;
3029:
3030: END IF;
3033: IF p_cust_account_id is null AND
3034: p_cust_account_role_id is not null then
3035:
3036: IF l_debug_level > 0 THEN
3037: oe_debug_pub.add( l_indent||' getting account from contact information' ) ;
3038: end IF;
3039: OPEN c_get_cust_id(p_cust_account_role_id);
3040: FETCH c_get_cust_id
3041: INTO l_cust_account_id;
3042:
3043: IF c_get_cust_id%NOTFOUND THEN
3044: x_return_status := FND_API.G_RET_STS_ERROR;
3045: IF l_debug_level > 0 THEN
3046: oe_debug_pub.add( l_indent|| 'not a valid cust account role id' ) ;
3047: END IF;
3048: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3049: OE_MSG_PUB.ADD;
3050:
3066: l_cust_account_id := p_cust_account_id;
3067: END IF;
3068:
3069: IF l_debug_level > 0 THEN
3070: oe_debug_pub.add( l_indent|| 'cust_account_id = '||L_CUST_ACCOUNT_ID ) ;
3071: END IF;
3072:
3073: p_contact_tbl.DELETE;
3074: IF l_cust_account_id is not null then
3078: -- if we need to assign the contact to a site, then first we check
3079: -- if it is already attached to that site
3080: IF p_assign_contact_to_site and p_cust_account_site_id is not null then
3081: IF l_debug_level > 0 THEN
3082: oe_debug_pub.add(l_indent|| 'need to assign contact to site' ) ;
3083: END IF;
3084: OPEN c_cust_role_site( p_cust_account_role_id);
3085: FETCH c_cust_role_site
3086: INTO l_status,
3090:
3091:
3092: IF c_cust_role_site%NOTFOUND THEN
3093: IF l_debug_level > 0 THEN
3094: oe_debug_pub.add( l_indent|| 'site contact not found' ) ;
3095: END IF;
3096: CLOSE c_cust_role_site;
3097:
3098: IF p_create_responsibility then
3097:
3098: IF p_create_responsibility then
3099: --create acct contact and attach site and pass the responsibility
3100: IF l_debug_level > 0 THEN
3101: oe_debug_pub.add( l_indent|| ' 1 not supported currently' ) ;
3102: end IF;
3103: ELSE
3104: --create acct contact and attach site and DO NOT pass responsibility
3105: IF l_debug_level > 0 THEN
3102: end IF;
3103: ELSE
3104: --create acct contact and attach site and DO NOT pass responsibility
3105: IF l_debug_level > 0 THEN
3106: oe_debug_pub.add( l_indent|| ' 2 not supported currently' ) ;
3107: END IF;
3108: END IF;
3109:
3110: ELSIF c_cust_role_site%FOUND THEN
3109:
3110: ELSIF c_cust_role_site%FOUND THEN
3111:
3112: IF l_debug_level > 0 THEN
3113: oe_debug_pub.add(l_indent|| 'site contact found' ) ;
3114: END IF;
3115: IF l_status <> 'A' then
3116: IF l_debug_level > 0 THEN
3117: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3113: oe_debug_pub.add(l_indent|| 'site contact found' ) ;
3114: END IF;
3115: IF l_status <> 'A' then
3116: IF l_debug_level > 0 THEN
3117: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3118: END IF;
3119: CLOSE c_cust_role_site;
3120: x_return_status := FND_API.G_RET_STS_ERROR;
3121: return;
3123: END IF;
3124:
3125: IF l_role_type <> 'CONTACT' then
3126: IF l_debug_level > 0 THEN
3127: oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3128: END IF;
3129: CLOSE c_cust_role_site;
3130: x_return_status := FND_API.G_RET_STS_ERROR;
3131: return;
3133: END IF;
3134:
3135: IF l_cust_account_id <> p_cust_account_id then
3136: IF l_debug_level > 0 THEN
3137: oe_debug_pub.add(l_indent|| 'acct role does not belong to this acct' ) ;
3138: END IF;
3139: CLOSE c_cust_role_site;
3140: x_return_status := FND_API.G_RET_STS_ERROR;
3141: return;
3143:
3144:
3145: IF l_cust_role_site_id <> p_cust_account_site_id then
3146: IF l_debug_level > 0 THEN
3147: oe_debug_pub.add( l_indent|| 'Cust account role for site is not valid' ) ;
3148: END IF;
3149: CLOSE c_cust_role_site;
3150: x_return_status := FND_API.G_RET_STS_ERROR;
3151: return;
3153: END IF;
3154:
3155:
3156: IF l_debug_level > 0 THEN
3157: oe_debug_pub.add(l_indent|| 'found the contact attached to account site' ) ;
3158: END IF;
3159: CLOSE c_cust_role_site;
3160: p_contact_tbl(1) :=p_cust_account_role_id;
3161: return;
3177: CLOSE c_cust_account_role;
3178:
3179: x_return_status := FND_API.G_RET_STS_ERROR;
3180: IF l_debug_level > 0 THEN
3181: oe_debug_pub.add(l_indent|| '2 contact_id not found' ) ;
3182: END IF;
3183: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3184: OE_MSG_PUB.ADD;
3185: OE_MSG_PUB.Count_And_Get
3191: ELSIF c_cust_account_role%FOUND then
3192:
3193: IF l_status <> 'A' then
3194: IF l_debug_level > 0 THEN
3195: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3196: END IF;
3197: CLOSE c_cust_account_role;
3198: x_return_status := FND_API.G_RET_STS_ERROR;
3199: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INACTIVE_CONTACT');
3207: END IF;
3208:
3209: IF l_role_type <> 'CONTACT' then
3210: IF l_debug_level > 0 THEN
3211: oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3212: END IF;
3213: CLOSE c_cust_account_role;
3214: x_return_status := FND_API.G_RET_STS_ERROR;
3215: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ROLE_CONTACT');
3224:
3225:
3226: IF l_cust_account_id <> p_cust_account_id then
3227: IF l_debug_level > 0 THEN
3228: oe_debug_pub.add( l_indent|| 'Account role does not belong to this acct' ) ;
3229: END IF;
3230: CLOSE c_cust_account_role;
3231: x_return_status := FND_API.G_RET_STS_ERROR;
3232: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_ACCOUNT');
3240:
3241: IF p_create_responsibility then
3242: --check if resp exists. if not then create one
3243: IF l_debug_level > 0 THEN
3244: oe_debug_pub.add( l_indent|| ' 1 not supported currently' ) ;
3245: end if;
3246: END IF;
3247:
3248: IF l_debug_level > 0 THEN
3245: end if;
3246: END IF;
3247:
3248: IF l_debug_level > 0 THEN
3249: oe_debug_pub.add( l_indent|| 'found contact attached to account returning' ) ;
3250: END IF;
3251: CLOSE c_cust_account_role;
3252: p_contact_tbl(1) :=p_cust_account_role_id;
3253: return;
3266: INTO l_rel_party_id;
3267:
3268: IF c_check_org_contact%NOTFOUND then
3269: IF l_debug_level > 0 THEN
3270: oe_debug_pub.add(l_indent|| 'org contact_id does not belong to sent party or '|| ' contact may be inactive' ) ;
3271: END IF;
3272: x_return_status := FND_API.G_RET_STS_ERROR;
3273: CLOSE c_check_org_contact;
3274: FND_MESSAGE.Set_Name('ONT','ONT_AAC_CONTACT_ACCOUNT');
3277: ( p_count => x_msg_count
3278: , p_data => x_msg_data
3279: );
3280: IF l_debug_level > 0 THEN
3281: oe_debug_pub.add(l_indent|| ' contact error msg = '||x_msg_data|| '; count = '||x_MSG_COUNT ) ;
3282: END IF;
3283:
3284: return;
3285: END IF;
3285: END IF;
3286: CLOSE c_check_org_contact;
3287:
3288: IF l_debug_level > 0 THEN
3289: oe_debug_pub.add(l_indent|| 'checking for value of org_contact_id' ) ;
3290: END IF;
3291: -- derive customer account
3292: OPEN c_org_contact(p_org_Contact_id,
3293: l_cust_account_id );
3291: -- derive customer account
3292: OPEN c_org_contact(p_org_Contact_id,
3293: l_cust_account_id );
3294: LOOP
3295: --oe_debug_pub.add('Inside get_cust_role_id loop');
3296: FETCH c_org_contact
3297: INTO l_cust_account_role_id,
3298: l_cust_acct_site_id;
3299: EXIT WHEN C_org_contact%NOTFOUND;
3298: l_cust_acct_site_id;
3299: EXIT WHEN C_org_contact%NOTFOUND;
3300:
3301: IF l_debug_level > 0 THEN
3302: oe_debug_pub.add(l_indent|| 'acct_role_id = '||l_cust_account_role_id|| ' cust_acct_site_id = '||L_CUST_ACCT_SITE_ID ) ;
3303: END IF;
3304:
3305: p_contact_tbl(p_contact_tbl.COUNT + 1):= l_cust_account_role_id;
3306:
3318: IF p_multiple_contact_is_error THEN
3319:
3320: IF p_contact_tbl.COUNT > 1 then
3321: IF l_debug_level > 0 THEN
3322: oe_debug_pub.add(l_indent|| 'error >1 contact for org_contact_id' ) ;
3323: END IF;
3324: x_return_status := FND_API.G_RET_STS_ERROR;
3325: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3326: FND_MESSAGE.Set_Token('TEXT',' Multiple Contacts ', FALSE);
3335:
3336:
3337: IF p_contact_tbl.COUNT = 1 then
3338: IF l_debug_level > 0 THEN
3339: oe_debug_pub.add( l_indent|| 'RETURNING WITH ONE CONTACT' ) ;
3340: END IF;
3341: x_return_status := FND_API.G_RET_STS_SUCCESS;
3342: return;
3343: ELSIF p_contact_tbl.COUNT = 0 then
3341: x_return_status := FND_API.G_RET_STS_SUCCESS;
3342: return;
3343: ELSIF p_contact_tbl.COUNT = 0 then
3344: IF l_debug_level > 0 THEN
3345: oe_debug_pub.add( l_indent|| 'no contact for account found. creating '|| ' acct_id = '||p_cust_account_id|| ' rel_party_id = '||L_REL_PARTY_ID ) ;
3346: END IF;
3347:
3348: IF p_allow_contact_creation THEN
3349:
3410: x_return_status := l_return_status;
3411: x_msg_data := l_msg_data;
3412: x_msg_count := l_msg_count;
3413: IF l_debug_level > 0 THEN
3414: oe_debug_pub.add(l_indent|| ' contact creation failed' ) ;
3415: oe_debug_pub.add( l_indent|| ' error = '||X_MSG_DATA ) ;
3416: END IF;
3417:
3418: IF x_msg_count = 1 then
3411: x_msg_data := l_msg_data;
3412: x_msg_count := l_msg_count;
3413: IF l_debug_level > 0 THEN
3414: oe_debug_pub.add(l_indent|| ' contact creation failed' ) ;
3415: oe_debug_pub.add( l_indent|| ' error = '||X_MSG_DATA ) ;
3416: END IF;
3417:
3418: IF x_msg_count = 1 then
3419: FND_MESSAGE.Set_Name('ONT','ONT_AAC_CONTACT_CREATION');
3430: return;
3431: ELSE
3432: x_return_status := FND_API.G_RET_STS_SUCCESS;
3433: IF l_debug_level > 0 THEN
3434: oe_debug_pub.add( l_indent|| ' contact creation succeeded '|| ' cust acct_role_id = '||P_CONTACT_TBL ( 1 ) ) ;
3435: END IF;
3436:
3437: END IF;
3438: ELSE
3437: END IF;
3438: ELSE
3439: x_return_status := FND_API.G_RET_STS_ERROR;
3440: IF l_debug_level > 0 THEN
3441: oe_debug_pub.add( l_indent|| 'not allowed to create contact' ) ;
3442: end IF;
3443: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_PERMISSION');
3444: OE_MSG_PUB.ADD;
3445: OE_MSG_PUB.Count_And_Get
3460: -- if zero then create the account contact appropiately
3461: -- take care of account site and responsibility
3462: IF p_contact_tbl.COUNT = 0 then
3463: IF l_debug_level > 0 THEN
3464: oe_debug_pub.add( l_indent|| 'equal to ZERO' ) ;
3465: END IF;
3466: x_return_status := FND_API.G_RET_STS_SUCCESS;
3467: return;
3468:
3470: -- take care of account site and responsibility
3471: -- and create if not present
3472: ELSIF p_Contact_tbl.COUNT >0 then
3473: IF l_debug_level > 0 THEN
3474: oe_debug_pub.add( l_indent|| 'one or more than one' ) ;
3475: END IF;
3476: x_return_status := FND_API.G_RET_STS_SUCCESS;
3477: return;
3478: END IF;
3479:
3480: END IF; -- if not to be assigned
3481:
3482: IF l_debug_level > 0 THEN
3483: oe_debug_pub.add(l_indent|| 'cust_acct_role_id = '||L_CUST_ACCOUNT_ROLE_ID ) ;
3484: END IF;
3485:
3486: ELSE
3487: IF l_debug_level > 0 THEN
3484: END IF;
3485:
3486: ELSE
3487: IF l_debug_level > 0 THEN
3488: oe_debug_pub.add(l_indent|| 'error no contact information is passed' ) ;
3489: END IF;
3490: return;
3491: END IF; -- if p_cust_account_role_id is not null
3492:
3491: END IF; -- if p_cust_account_role_id is not null
3492:
3493: ELSE -- if cust_account_id is not passed
3494: IF l_debug_level > 0 THEN
3495: oe_debug_pub.add(l_indent|| 'this procedure expects the account_id to be passed' ) ;
3496: END IF;
3497: return;
3498:
3499: END IF; -- if cust_account_id is not null
3526: WHEN OTHERS THEN
3527:
3528: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3529: IF l_debug_level > 0 THEN
3530: oe_debug_pub.add( 'CHECK_CONTACT WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
3531: END IF;
3532:
3533: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3534: THEN
3601: x_cust_account_number varchar2(30);
3602: l_found boolean := FALSE;
3603:
3604: --
3605: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
3606: l_indent varchar2(5) := ' ';
3607: i number := 0;
3608:
3609: --
3612: x_return_status := FND_API.G_RET_STS_SUCCESS;
3613: p_account_tbl.DELETE;
3614:
3615: IF l_debug_level > 0 THEN
3616: oe_debug_pub.add(l_indent||'party_id = '||p_party_id|| '; party_number = '||p_party_number);
3617: oe_debug_pub.add(l_indent||'site tbl count = '||p_site_tbl_count ) ;
3618: END IF;
3619:
3620: if p_allow_account_creation then
3613: p_account_tbl.DELETE;
3614:
3615: IF l_debug_level > 0 THEN
3616: oe_debug_pub.add(l_indent||'party_id = '||p_party_id|| '; party_number = '||p_party_number);
3617: oe_debug_pub.add(l_indent||'site tbl count = '||p_site_tbl_count ) ;
3618: END IF;
3619:
3620: if p_allow_account_creation then
3621: IF l_debug_level > 0 THEN
3618: END IF;
3619:
3620: if p_allow_account_creation then
3621: IF l_debug_level > 0 THEN
3622: oe_debug_pub.add( l_indent|| ' allowed account creation' ) ;
3623: END IF;
3624: end if;
3625:
3626: if p_multiple_account_is_error then
3624: end if;
3625:
3626: if p_multiple_account_is_error then
3627: IF l_debug_level > 0 THEN
3628: oe_debug_pub.add(l_indent|| ' multiple account is error' ) ;
3629: END IF;
3630: end if;
3631:
3632: if p_return_if_only_party then
3630: end if;
3631:
3632: if p_return_if_only_party then
3633: IF l_debug_level > 0 THEN
3634: oe_debug_pub.add(l_indent|| ' return_if_only_party' ) ;
3635: END IF;
3636: end if;
3637:
3638:
3649:
3650: IF party_number_rec%NOTFOUND THEN
3651: x_return_status := FND_API.G_RET_STS_ERROR;
3652: IF l_debug_level > 0 THEN
3653: oe_debug_pub.add(l_indent|| 'no such party found for party_number' ) ;
3654: END IF;
3655: CLOSE party_number_rec;
3656: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_PARTY');
3657: OE_MSG_PUB.ADD;
3667:
3668: IF party_rec%NOTFOUND THEN
3669: x_return_status := FND_API.G_RET_STS_ERROR;
3670: IF l_debug_level > 0 THEN
3671: oe_debug_pub.add(l_indent|| 'no such party found for party_id' ) ;
3672: END IF;
3673: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_PARTY');
3674: OE_MSG_PUB.ADD;
3675: CLOSE party_rec;
3680:
3681: END IF;
3682:
3683: IF l_debug_level > 0 THEN
3684: oe_debug_pub.add(l_indent|| 'party type for sold_to = '|| L_PARTY_TYPE ) ;
3685: END IF;
3686: IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
3687:
3688: IF l_debug_level > 0 THEN
3685: END IF;
3686: IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
3687:
3688: IF l_debug_level > 0 THEN
3689: oe_debug_pub.add(l_indent|| 'party type = '||l_party_type|| '; party_id = '||l_PARTY_ID ) ;
3690: END IF;
3691: -- derive customer account
3692: OPEN C_get_cust_id_from_party_id(l_Party_Id);
3693: LOOP
3692: OPEN C_get_cust_id_from_party_id(l_Party_Id);
3693: LOOP
3694: IF l_debug_level > 0 THEN
3695: i := i+1;
3696: oe_debug_pub.add ('AAC:VTI: ===GET_CUST_LOOP_'||i||'===== ');
3697: oe_debug_pub.add(l_indent|| 'inside get_cust_id loop :X#'||i) ;
3698: END IF;
3699:
3700: FETCH C_get_cust_id_from_party_id
3693: LOOP
3694: IF l_debug_level > 0 THEN
3695: i := i+1;
3696: oe_debug_pub.add ('AAC:VTI: ===GET_CUST_LOOP_'||i||'===== ');
3697: oe_debug_pub.add(l_indent|| 'inside get_cust_id loop :X#'||i) ;
3698: END IF;
3699:
3700: FETCH C_get_cust_id_from_party_id
3701: INTO l_cust_account_id,
3702: l_account_number;
3703: EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
3704:
3705: IF l_debug_level > 0 THEN
3706: oe_debug_pub.add( l_indent|| 'acct_id = '||l_cust_account_id|| '; account number = '||l_ACCOUNT_NUMBER ) ;
3707: END IF;
3708:
3709: IF l_debug_level > 0 THEN
3710: oe_debug_pub.add( l_indent|| 'row count get_cust_id = '|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
3706: oe_debug_pub.add( l_indent|| 'acct_id = '||l_cust_account_id|| '; account number = '||l_ACCOUNT_NUMBER ) ;
3707: END IF;
3708:
3709: IF l_debug_level > 0 THEN
3710: oe_debug_pub.add( l_indent|| 'row count get_cust_id = '|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
3711: END IF;
3712:
3713: IF p_multiple_account_is_error THEN
3714: IF C_get_cust_id_from_party_id%ROWCOUNT > 1 then
3712:
3713: IF p_multiple_account_is_error THEN
3714: IF C_get_cust_id_from_party_id%ROWCOUNT > 1 then
3715: IF l_debug_level > 0 THEN
3716: oe_debug_pub.add( l_indent|| 'more than one account for party id' ) ;
3717: END IF;
3718: x_return_status := FND_API.G_RET_STS_ERROR;
3719: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3720: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
3722: EXIT;
3723: END IF;
3724: end if;
3725: IF l_debug_level > 0 THEN
3726: oe_debug_pub.add(l_indent|| 'adding to account tbl id = '||L_CUST_ACCOUNT_ID ) ;
3727: END IF;
3728: p_account_tbl(p_account_tbl.COUNT + 1):= l_cust_account_id;
3729:
3730:
3733: -- if multiple accounts are detected then do not proceed further as
3734: -- contacts and sites needs to be created once an account is selected
3735: IF p_account_tbl.COUNT > 1 then
3736: IF l_debug_level > 0 THEN
3737: oe_debug_pub.add( l_indent|| 'multiple accounts found' ) ;
3738: end IF;
3739: p_multiple_account := TRUE;
3740: END IF;
3741:
3741:
3742: CLOSE C_get_cust_id_from_party_id;
3743:
3744: IF l_debug_level > 0 THEN
3745: oe_debug_pub.add( l_indent||'cust acct id for sold_to = '|| L_CUST_ACCOUNT_ID ) ;
3746: END IF;
3747:
3748:
3749: ELSE -- if not person or organization
3749: ELSE -- if not person or organization
3750:
3751: x_return_status := FND_API.G_RET_STS_ERROR;
3752: IF l_debug_level > 0 THEN
3753: oe_debug_pub.add(l_indent|| ' invalid party type' ) ;
3754: end IF;
3755: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_PARTY');
3756: OE_MSG_PUB.ADD;
3757:
3760: IF p_account_tbl.COUNT > 0 then
3761: FOR i in p_account_tbl.FIRST..p_account_tbl.LAST
3762: LOOP
3763: IF l_debug_level > 0 THEN
3764: oe_debug_pub.add( l_indent|| 'acct_id = '||P_ACCOUNT_TBL ( I ) ) ;
3765: END IF;
3766: END LOOP;
3767: END IF;
3768:
3766: END LOOP;
3767: END IF;
3768:
3769: IF l_debug_level > 0 THEN
3770: oe_debug_pub.add(l_indent|| 'before checking to create cust account' ) ;
3771: END IF;
3772: -- create customer account
3773: IF x_return_status <> FND_API.G_RET_STS_ERROR AND
3774: NOT p_multiple_account AND
3782: -- as we will not have account information
3783: -- Even if party level contact is passed then
3784: -- user should select it in the Add Customer form
3785: IF l_debug_level > 0 THEN
3786: oe_debug_pub.add( l_indent|| ' going to call add customer' ) ;
3787: END IF;
3788: x_return_status := FND_API.G_RET_STS_SUCCESS;
3789: return;
3790:
3790:
3791: ELSE
3792: IF l_party_id is not NULL THEN
3793: IF l_debug_level > 0 THEN
3794: oe_debug_pub.add( l_indent|| 'creating cust account...' ) ;
3795: END IF;
3796:
3797: oe_oe_inline_address.create_account(
3798: p_party_number=>null,
3860: in_Created_by_module=>G_CREATED_BY_MODULE
3861: );
3862:
3863: IF l_debug_level > 0 THEN
3864: oe_debug_pub.add( l_indent|| ' create account status='||x_RETURN_STATUS|| '; x_party_id = '|| X_PARTY_ID);
3865: oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3866: oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3867: END IF;
3868:
3861: );
3862:
3863: IF l_debug_level > 0 THEN
3864: oe_debug_pub.add( l_indent|| ' create account status='||x_RETURN_STATUS|| '; x_party_id = '|| X_PARTY_ID);
3865: oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3866: oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3867: END IF;
3868:
3869: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3862:
3863: IF l_debug_level > 0 THEN
3864: oe_debug_pub.add( l_indent|| ' create account status='||x_RETURN_STATUS|| '; x_party_id = '|| X_PARTY_ID);
3865: oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3866: oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3867: END IF;
3868:
3869: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3870: IF l_debug_level > 0 THEN
3867: END IF;
3868:
3869: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3870: IF l_debug_level > 0 THEN
3871: oe_debug_pub.add( l_indent|| ' account creation failed' ) ;
3872: END IF;
3873: IF x_msg_count = 1 then
3874: FND_MESSAGE.Set_Name('ONT','ONT_AAC_ACCOUNT_CREATION');
3875: FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
3882: p_account_tbl(1):= x_cust_account_id;
3883: /*cc project assigning the value CREATED TO variable G_account_created_or_found. We need
3884: to do the site creation process in case account is created and not search for the sites*/
3885: IF l_debug_level > 0 THEN
3886: oe_debug_pub.add('cc account created');
3887: END IF;
3888:
3889: G_account_created_or_found :='CREATED';
3890:
3900:
3901: IF G_CREATED_BY_MODULE <> 'ONT_TELESERVICE_INTEGRATION' THEN
3902: x_return_status := FND_API.G_RET_STS_ERROR;
3903: IF l_debug_level > 0 THEN
3904: oe_debug_pub.add(l_indent|| 'not allowed to create account' ) ;
3905: END IF;
3906: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
3907: OE_MSG_PUB.ADD;
3908: ELSE
3906: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
3907: OE_MSG_PUB.ADD;
3908: ELSE
3909: IF l_debug_level > 0 THEN
3910: oe_debug_pub.add('Contact Center Integration, no permission to create account');
3911: END IF;
3912: END IF;
3913: END IF; -- end profile condition
3914:
3913: END IF; -- end profile condition
3914:
3915: ELSE
3916: IF l_debug_level > 0 THEN
3917: oe_debug_pub.add(l_indent|| ' account does not need to be created' ) ;
3918: END IF;
3919: END IF; -- checking to see if acconts needs to be created
3920:
3921: --oe_debug_pub.add('p_out_cust_account_id = '|| p_account_tbl(1));
3917: oe_debug_pub.add(l_indent|| ' account does not need to be created' ) ;
3918: END IF;
3919: END IF; -- checking to see if acconts needs to be created
3920:
3921: --oe_debug_pub.add('p_out_cust_account_id = '|| p_account_tbl(1));
3922:
3923: ELSE
3924: --x_return_status := FND_API.G_RET_STS_ERROR;
3925: FND_MESSAGE.Set_Name('ONT', 'ONT_AVAIL_GENERIC');
3923: ELSE
3924: --x_return_status := FND_API.G_RET_STS_ERROR;
3925: FND_MESSAGE.Set_Name('ONT', 'ONT_AVAIL_GENERIC');
3926: IF l_debug_level > 0 THEN
3927: oe_debug_pub.add(l_indent|| 'party_id or party number is not passed' ) ;
3928: END IF;
3929: l_message := 'Party_id or Party Number is not passed';
3930: FND_MESSAGE.Set_Token('TEXT',l_message, FALSE);
3931: OE_MSG_PUB.ADD;
3933: END IF; -- if party_id or party_number is not null
3934:
3935:
3936: IF l_debug_level > 0 THEN
3937: oe_debug_pub.add(l_indent|| 'create account at the end' ) ;
3938: END IF;
3939: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3940: IF l_debug_level > 0 THEN
3941: oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3937: oe_debug_pub.add(l_indent|| 'create account at the end' ) ;
3938: END IF;
3939: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3940: IF l_debug_level > 0 THEN
3941: oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3942: END IF;
3943: oe_msg_pub.count_and_get(p_encoded=>fnd_api.G_TRUE,
3944: p_count => x_msg_count,
3945: p_data=>x_msg_data
3944: p_count => x_msg_count,
3945: p_data=>x_msg_data
3946: );
3947: IF l_debug_level > 0 THEN
3948: oe_debug_pub.add( l_indent|| 'count = '||x_msg_count||'; msg = '||X_MSG_DATA ) ;
3949: END IF;
3950: END IF;
3951:
3952: IF l_debug_level > 0 THEN
3949: END IF;
3950: END IF;
3951:
3952: IF l_debug_level > 0 THEN
3953: oe_debug_pub.add( l_indent|| 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
3954: oe_debug_pub.add( l_indent|| 'exiting check_and_create_account: ' || X_RETURN_STATUS ) ;
3955: END IF;
3956:
3957: EXCEPTION
3950: END IF;
3951:
3952: IF l_debug_level > 0 THEN
3953: oe_debug_pub.add( l_indent|| 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
3954: oe_debug_pub.add( l_indent|| 'exiting check_and_create_account: ' || X_RETURN_STATUS ) ;
3955: END IF;
3956:
3957: EXCEPTION
3958:
4013: END IF;
4014:
4015: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4016: IF l_debug_level > 0 THEN
4017: oe_debug_pub.add( 'CHECK_ACCOUNT WHEN OTHERS EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
4018: END IF;
4019:
4020: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4021: THEN
4037:
4038: l_file_val varchar2(2000);
4039:
4040: --
4041: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4042: --
4043: BEGIN
4044:
4045: oe_debug_pub.debug_on;
4041: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4042: --
4043: BEGIN
4044:
4045: oe_debug_pub.debug_on;
4046: oe_debug_pub.initialize;
4047: l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
4048:
4049: END set_debug_on;
4042: --
4043: BEGIN
4044:
4045: oe_debug_pub.debug_on;
4046: oe_debug_pub.initialize;
4047: l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
4048:
4049: END set_debug_on;
4050:
4043: BEGIN
4044:
4045: oe_debug_pub.debug_on;
4046: oe_debug_pub.initialize;
4047: l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
4048:
4049: END set_debug_on;
4050:
4051:
4081: l_cust_account_id number;
4082: l_account_number varchar2(30);
4083:
4084: --
4085: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4086: --
4087: BEGIN
4088:
4089: x_return_status := FND_API.G_RET_STS_SUCCESS;
4089: x_return_status := FND_API.G_RET_STS_SUCCESS;
4090: p_account_tbl.DELETE;
4091:
4092: IF l_debug_level > 0 THEN
4093: oe_debug_pub.add( ' PARTY_ID='||P_PARTY_ID|| ' PARTY_NUMBER ='||P_PARTY_NUMBER ) ;
4094: END IF;
4095: -- if both party_id and party_number information is not provided then
4096: -- then we raise an error
4097: IF p_party_id is not null or p_party_number is not null then
4104: l_party_type;
4105:
4106: IF party_number_rec%NOTFOUND THEN
4107: IF l_debug_level > 0 THEN
4108: oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_NUMBER' ) ;
4109: END IF;
4110: x_return_status := FND_API.G_RET_STS_ERROR;
4111: FND_MESSAGE.Set_Name('ONT','ONT_AACC_NO_ACCOUNT');
4112: OE_MSG_PUB.ADD;
4122: INTO l_party_type;
4123:
4124: IF party_rec%NOTFOUND THEN
4125: IF l_debug_level > 0 THEN
4126: oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_ID' ) ;
4127: END IF;
4128: x_return_status := FND_API.G_RET_STS_ERROR;
4129: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
4130: OE_MSG_PUB.ADD;
4136:
4137: END IF; -- if party_number is not null
4138:
4139: IF l_debug_level > 0 THEN
4140: oe_debug_pub.add( 'PARTY TYPE FOR SOLD_TO = '|| L_PARTY_TYPE ) ;
4141: END IF;
4142: IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
4143:
4144: IF l_debug_level > 0 THEN
4141: END IF;
4142: IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
4143:
4144: IF l_debug_level > 0 THEN
4145: oe_debug_pub.add( 'PARTY TYPE='||L_PARTY_TYPE|| ' PARTY_ID='||L_PARTY_ID ) ;
4146: END IF;
4147: -- derive customer account
4148: OPEN C_get_cust_id_from_party_id(l_Party_Id);
4149: LOOP
4147: -- derive customer account
4148: OPEN C_get_cust_id_from_party_id(l_Party_Id);
4149: LOOP
4150: IF l_debug_level > 0 THEN
4151: oe_debug_pub.add( 'INSIDE GET_CUST_ID LOOP' ) ;
4152: END IF;
4153: FETCH C_get_cust_id_from_party_id
4154: INTO l_cust_account_id,
4155: l_account_number;
4155: l_account_number;
4156: EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
4157:
4158: IF l_debug_level > 0 THEN
4159: oe_debug_pub.add( 'ACCT_ID='||L_CUST_ACCOUNT_ID|| ' ACCOUNT NUMBER='||L_ACCOUNT_NUMBER ) ;
4160: END IF;
4161:
4162: IF l_debug_level > 0 THEN
4163: oe_debug_pub.add( 'ROW COUNT GET_CUST_ID='|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
4159: oe_debug_pub.add( 'ACCT_ID='||L_CUST_ACCOUNT_ID|| ' ACCOUNT NUMBER='||L_ACCOUNT_NUMBER ) ;
4160: END IF;
4161:
4162: IF l_debug_level > 0 THEN
4163: oe_debug_pub.add( 'ROW COUNT GET_CUST_ID='|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
4164: END IF;
4165:
4166: oe_debug_pub.add('X1:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4167:
4162: IF l_debug_level > 0 THEN
4163: oe_debug_pub.add( 'ROW COUNT GET_CUST_ID='|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
4164: END IF;
4165:
4166: oe_debug_pub.add('X1:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4167:
4168: IF l_debug_level > 0 THEN
4169: oe_debug_pub.add( 'ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4170: END IF;
4165:
4166: oe_debug_pub.add('X1:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4167:
4168: IF l_debug_level > 0 THEN
4169: oe_debug_pub.add( 'ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4170: END IF;
4171:
4172: oe_debug_pub.add('X2:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4173:
4168: IF l_debug_level > 0 THEN
4169: oe_debug_pub.add( 'ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4170: END IF;
4171:
4172: oe_debug_pub.add('X2:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4173:
4174: p_account_tbl(p_account_tbl.COUNT + 1):= l_cust_account_id;
4175:
4176: oe_debug_pub.add('X3:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4172: oe_debug_pub.add('X2:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4173:
4174: p_account_tbl(p_account_tbl.COUNT + 1):= l_cust_account_id;
4175:
4176: oe_debug_pub.add('X3:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4177:
4178: END LOOP;
4179: ELSE
4180: IF l_debug_level > 0 THEN
4177:
4178: END LOOP;
4179: ELSE
4180: IF l_debug_level > 0 THEN
4181: oe_debug_pub.add( 'INVALID PARTY TYPE' ) ;
4182: END IF;
4183: x_return_status := FND_API.G_RET_STS_ERROR;
4184: FND_MESSAGE.Set_Name('ONT','ONT_AAC_INVALID_PARTY');
4185: OE_MSG_PUB.ADD;
4187: END IF;
4188:
4189: ELSE
4190: IF l_debug_level > 0 THEN
4191: oe_debug_pub.add( 'NO PARTY INFORMATION SENT' ) ;
4192: END IF;
4193: x_return_status := FND_API.G_RET_STS_ERROR;
4194: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4195: FND_MESSAGE.Set_Token('TEXT','No Customer Information ', FALSE);
4215: ,x_msg_count OUT NOCOPY NUMBER
4216: ,x_msg_data OUT NOCOPY VARCHAR2)
4217: IS
4218: -- local variables here
4219: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4220: l_dummy1 number := null;
4221: l_dummy2 number := null;
4222: l_dummy3 number := null;
4223: l_dummy4 boolean := false;
4226:
4227: x_return_status := FND_API.G_RET_STS_SUCCESS;
4228:
4229: IF l_debug_level > 0 THEN
4230: oe_debug_pub.add ('AAC:VTI: starting Value-To-Id');
4231: oe_debug_pub.add ('AAC:VTI: looking for header level stuff');
4232: END IF;
4233:
4234:
4227: x_return_status := FND_API.G_RET_STS_SUCCESS;
4228:
4229: IF l_debug_level > 0 THEN
4230: oe_debug_pub.add ('AAC:VTI: starting Value-To-Id');
4231: oe_debug_pub.add ('AAC:VTI: looking for header level stuff');
4232: END IF;
4233:
4234:
4235: -- if both party_id and account_id are missing, call find_sold_to_id
4256: IF x_return_status = FND_API.G_RET_STS_ERROR then
4257: -- not found a party_id/cust_account_id
4258: -- error message already logged inside find_sold_to_id
4259: IF l_debug_level > 0 THEN
4260: oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for header');
4261: END IF;
4262: -- exit if account_id cannot be found
4263: return;
4264: end if; -- x_return_status
4265:
4266: end if;
4267:
4268: IF l_debug_level > 0 THEN
4269: oe_debug_pub.add ('AAC:VTI: looking for sold_to contact');
4270: END IF;
4271:
4272: -- if both org_contact_id and account_role_id are missing, call find_contact_id
4273: if (nvl(p_party_customer_rec.p_org_contact_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4292: for i in p_site_tbl.FIRST..p_site_tbl.LAST loop
4293: -- do value-to-id for each site record
4294:
4295: IF l_debug_level > 0 THEN
4296: oe_debug_pub.add ('AAC:VTI: ============ VTI SITE RECORD '||i||' of '||p_site_tbl.LAST||' =============== ');
4297: oe_debug_pub.add ('AAC:VTI: looking for site party_id line record #'||i);
4298: END IF;
4299:
4300:
4293: -- do value-to-id for each site record
4294:
4295: IF l_debug_level > 0 THEN
4296: oe_debug_pub.add ('AAC:VTI: ============ VTI SITE RECORD '||i||' of '||p_site_tbl.LAST||' =============== ');
4297: oe_debug_pub.add ('AAC:VTI: looking for site party_id line record #'||i);
4298: END IF;
4299:
4300:
4301: -- do party v-t-i
4317: IF x_return_status = FND_API.G_RET_STS_ERROR then
4318: -- not found a party_id/cust_account_id
4319: -- error message already logged inside find_sold_to_id
4320: IF l_debug_level > 0 THEN
4321: oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for line#'||i);
4322: END IF;
4323: -- exit if account_id cannot be found
4324: return;
4325: end if; -- x_return_status
4324: return;
4325: end if; -- x_return_status
4326:
4327: IF l_debug_level > 0 THEN
4328: oe_debug_pub.add ('AAC:VTI: looking for site contact');
4329: END IF;
4330:
4331: oe_debug_pub.add('P1:'||p_site_tbl(i).p_site_use_id);
4332: -- do contact v-t-i
4327: IF l_debug_level > 0 THEN
4328: oe_debug_pub.add ('AAC:VTI: looking for site contact');
4329: END IF;
4330:
4331: oe_debug_pub.add('P1:'||p_site_tbl(i).p_site_use_id);
4332: -- do contact v-t-i
4333: find_contact_id(
4334: p_contact_id => p_site_tbl(i).p_org_contact_id
4335: ,p_cust_contact_id => p_site_tbl(i).p_cust_account_role_id
4343: );
4344:
4345: x_return_status := FND_API.G_RET_STS_SUCCESS;
4346: -- do site v-t-i
4347: oe_debug_pub.add('P2:'||p_site_tbl(i).p_site_use_id);
4348: find_site_id(
4349: p_site_use_id => p_site_tbl(i).p_party_site_use_id
4350: ,p_site_id => p_site_tbl(i).p_party_site_id
4351: ,p_account_site_use_id => p_site_tbl(i).p_site_use_id
4367: ,x_return_status => x_return_status
4368: ,x_msg_count => x_msg_count
4369: ,x_msg_data => x_msg_data
4370: );
4371: oe_debug_pub.add('P3:'||p_site_tbl(i).p_site_use_id);
4372: IF x_return_status = FND_API.G_RET_STS_ERROR then
4373: -- not found a party_id/cust_account_id
4374: -- error message already logged inside find_sold_to_id
4375: IF l_debug_level > 0 THEN
4372: IF x_return_status = FND_API.G_RET_STS_ERROR then
4373: -- not found a party_id/cust_account_id
4374: -- error message already logged inside find_sold_to_id
4375: IF l_debug_level > 0 THEN
4376: oe_debug_pub.add ('AAC:VTI:site_use_id account/party not found for line#'||1);
4377: END IF;
4378: -- exit if account_id cannot be found
4379: return;
4380: end if; -- x_return_status
4382: end loop;
4383: end if;
4384:
4385: IF l_debug_level > 0 THEN
4386: oe_debug_pub.add ('AAC:VTI: ending Value-To-Id');
4387: END IF;
4388:
4389: END value_to_id;
4390:
4409: --into p_cust_account_id -- commented for bug 3449269
4410: from hz_cust_accounts
4411: where party_id=l_party_id
4412: and status='A';
4413: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4414:
4415: BEGIN
4416:
4417: x_return_status := FND_API.G_RET_STS_SUCCESS;
4416:
4417: x_return_status := FND_API.G_RET_STS_SUCCESS;
4418:
4419: IF l_debug_level > 0 THEN
4420: oe_debug_pub.add ('AAC:VTI: FSTI: starting find_sold_to_id');
4421: END IF;
4422:
4423: -- check if party_id and cust_account_id are not null, return if so
4424: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4424: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4425: and nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4426: THEN
4427: IF l_debug_level > 0 THEN
4428: oe_debug_pub.add ('AAC:FSTI: party_id and account_id are not null, nothing to do: returning');
4429: END IF;
4430: RETURN;
4431: ELSE
4432: IF l_debug_level > 0 THEN
4429: END IF;
4430: RETURN;
4431: ELSE
4432: IF l_debug_level > 0 THEN
4433: oe_debug_pub.add ('AAC:FSTI: party_name: '||p_party_name||'; cust_account_number: '||p_cust_account_number);
4434: oe_debug_pub.add ('AAC:FSTI: party_id: '||p_party_id||'; cust_account_id: '||p_cust_account_id);
4435: END IF;
4436: END IF;
4437:
4430: RETURN;
4431: ELSE
4432: IF l_debug_level > 0 THEN
4433: oe_debug_pub.add ('AAC:FSTI: party_name: '||p_party_name||'; cust_account_number: '||p_cust_account_number);
4434: oe_debug_pub.add ('AAC:FSTI: party_id: '||p_party_id||'; cust_account_id: '||p_cust_account_id);
4435: END IF;
4436: END IF;
4437:
4438: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4445: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4446: then
4447: --nothing to do! return!
4448: IF l_debug_level > 0 THEN
4449: oe_debug_pub.add ('AAC:FSTI: no data passed in, seting process_site to false, returning');
4450: END IF;
4451: p_process_site:=false;
4452: return;
4453: end if;
4451: p_process_site:=false;
4452: return;
4453: end if;
4454:
4455: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4456: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4457: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4458: oe_debug_pub.add('p_party_name :'||p_party_name );
4459: oe_debug_pub.add('p_party_number :'||p_party_number );
4452: return;
4453: end if;
4454:
4455: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4456: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4457: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4458: oe_debug_pub.add('p_party_name :'||p_party_name );
4459: oe_debug_pub.add('p_party_number :'||p_party_number );
4460: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4453: end if;
4454:
4455: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4456: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4457: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4458: oe_debug_pub.add('p_party_name :'||p_party_name );
4459: oe_debug_pub.add('p_party_number :'||p_party_number );
4460: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4461: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4454:
4455: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4456: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4457: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4458: oe_debug_pub.add('p_party_name :'||p_party_name );
4459: oe_debug_pub.add('p_party_number :'||p_party_number );
4460: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4461: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4462: oe_debug_pub.add('p_party_id :'||p_party_id);
4455: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4456: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4457: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4458: oe_debug_pub.add('p_party_name :'||p_party_name );
4459: oe_debug_pub.add('p_party_number :'||p_party_number );
4460: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4461: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4462: oe_debug_pub.add('p_party_id :'||p_party_id);
4463:
4456: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4457: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4458: oe_debug_pub.add('p_party_name :'||p_party_name );
4459: oe_debug_pub.add('p_party_number :'||p_party_number );
4460: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4461: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4462: oe_debug_pub.add('p_party_id :'||p_party_id);
4463:
4464: begin
4457: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4458: oe_debug_pub.add('p_party_name :'||p_party_name );
4459: oe_debug_pub.add('p_party_number :'||p_party_number );
4460: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4461: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4462: oe_debug_pub.add('p_party_id :'||p_party_id);
4463:
4464: begin
4465: -- check if site_use_id is passed
4458: oe_debug_pub.add('p_party_name :'||p_party_name );
4459: oe_debug_pub.add('p_party_number :'||p_party_number );
4460: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4461: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4462: oe_debug_pub.add('p_party_id :'||p_party_id);
4463:
4464: begin
4465: -- check if site_use_id is passed
4466: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4466: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4467: nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM ) then
4468:
4469: IF l_debug_level > 0 THEN
4470: oe_debug_pub.add ('AAC:FSTI: site_use_id:'||p_site_use_id||' is not null, cust_account_id is null: getting cust_account_id');
4471: END IF;
4472:
4473: select s.cust_account_id
4474: into p_cust_account_id
4483: if nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM and
4484: nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
4485:
4486: IF l_debug_level > 0 THEN
4487: oe_debug_pub.add ('AAC:FSTI: cust_account_id:'||p_cust_account_id||' is not null, party_id is null: getting party_id');
4488: END IF;
4489:
4490: select party_id
4491: into p_party_id
4505: OE_MSG_PUB.ADD;
4506: END IF;
4507:
4508: IF l_debug_level > 4 THEN
4509: oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4510: END IF;
4511:
4512: x_return_status := FND_API.G_RET_STS_ERROR;
4513: return;
4521: ,'find_sold_to_id'
4522: );
4523: --END IF;
4524: IF l_debug_level > 4 THEN
4525: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4526: END IF;
4527: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4528:
4529: end;
4543:
4544: else
4545: -- found a sold_to_org_id/cust_account_id
4546: IF l_debug_level > 0 THEN
4547: oe_debug_pub.add ('AAC:FSTI: found sold_to_org_id/cust_account_id:'||p_cust_account_id);
4548: END IF;
4549:
4550: -- also find the party_id for this account_id
4551: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4555: from hz_cust_accounts
4556: where cust_account_id = p_cust_account_id;
4557:
4558: IF l_debug_level > 0 THEN
4559: oe_debug_pub.add ('AAC:FSTI: also found party_id:'||p_party_id||', returning');
4560: END IF;
4561: EXCEPTION
4562:
4563: WHEN NO_DATA_FOUND THEN
4568: OE_MSG_PUB.ADD;
4569: END IF;
4570:
4571: IF l_debug_level > 4 THEN
4572: oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4573: END IF;
4574:
4575: x_return_status := FND_API.G_RET_STS_ERROR;
4576: return;
4584: ,'find_sold_to_id'
4585: );
4586: --END IF;
4587: IF l_debug_level > 4 THEN
4588: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4589: END IF;
4590: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4591:
4592: end;
4597: if (nvl(p_party_number,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4598: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4599: then
4600: IF l_debug_level > 0 THEN
4601: oe_debug_pub.add ('AAC:FSTI: party_number:'|| p_party_number||' is not null, picking up party_id from it');
4602: END IF;
4603:
4604: begin
4605: select party_id
4617: OE_MSG_PUB.ADD;
4618: END IF;
4619:
4620: IF l_debug_level > 4 THEN
4621: oe_debug_pub.add ('AAC:VTI: invalid party_number ');
4622: END IF;
4623:
4624: x_return_status := FND_API.G_RET_STS_ERROR;
4625: p_party_id := null;
4631: --THEN
4632: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4633: --END IF;
4634: IF l_debug_level > 4 THEN
4635: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4636: END IF;
4637: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4638:
4639: end;
4643: if (nvl(p_party_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4644: and nvl(p_party_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4645: then
4646: IF l_debug_level > 0 THEN
4647: oe_debug_pub.add ('AAC:FSTI: party_site_use_id:'|| p_party_site_use_id||' is not null, picking up party_site_id from it');
4648: END IF;
4649: begin
4650: select party_site_id
4651: into p_party_site_id
4662: OE_MSG_PUB.ADD;
4663: END IF;
4664:
4665: IF l_debug_level > 4 THEN
4666: oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
4667: END IF;
4668:
4669: x_return_status := FND_API.G_RET_STS_ERROR;
4670: p_party_site_id := null;
4676: --THEN
4677: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4678: --END IF;
4679: IF l_debug_level > 4 THEN
4680: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4681: END IF;
4682: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4683:
4684: end;
4687: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4688: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4689: then
4690: IF l_debug_level > 0 THEN
4691: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up party_id from it');
4692: END IF;
4693: begin
4694: select party_id
4695: into p_party_id
4706: OE_MSG_PUB.ADD;
4707: END IF;
4708:
4709: IF l_debug_level > 4 THEN
4710: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
4711: END IF;
4712:
4713: x_return_status := FND_API.G_RET_STS_ERROR;
4714: return;
4719: --THEN
4720: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4721: --END IF;
4722: IF l_debug_level > 4 THEN
4723: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4724: END IF;
4725: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4726:
4727: end;
4731: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4732: and nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4733: then
4734: IF l_debug_level > 0 THEN
4735: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust site_use_id from it');
4736: END IF;
4737: begin
4738: select u.site_use_id
4739: into p_site_use_id
4745:
4746: WHEN NO_DATA_FOUND THEN
4747:
4748: IF l_debug_level > 4 THEN
4749: oe_debug_pub.add ('AAC:VTI: party_site_id: no site_use_id ');
4750: END IF;
4751: p_site_use_id := null;
4752:
4753: WHEN TOO_MANY_ROWS THEN
4751: p_site_use_id := null;
4752:
4753: WHEN TOO_MANY_ROWS THEN
4754: IF l_debug_level > 4 THEN
4755: oe_debug_pub.add ('AAC:VTI: party_site_id: multiple sites found ');
4756: END IF;
4757: p_site_use_id := null;
4758:
4759: WHEN OTHERS THEN
4762: --THEN
4763: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4764: --END IF;
4765: IF l_debug_level > 4 THEN
4766: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4767: END IF;
4768: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4769:
4770: end;
4774: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4775: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4776: then
4777: IF l_debug_level > 0 THEN
4778: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust_account_id from it via account_sites');
4779: END IF;
4780: begin
4781: select distinct cust_account_id
4782: into p_cust_account_id
4787:
4788: WHEN NO_DATA_FOUND THEN
4789:
4790: IF l_debug_level > 4 THEN
4791: oe_debug_pub.add ('AAC:VTI: party_site_id: no cust_account_id via site_use_id');
4792: END IF;
4793: p_cust_account_id := null;
4794:
4795: WHEN TOO_MANY_ROWS THEN
4793: p_cust_account_id := null;
4794:
4795: WHEN TOO_MANY_ROWS THEN
4796: IF l_debug_level > 4 THEN
4797: oe_debug_pub.add ('AAC:VTI: party_site_id: multiple accounts ');
4798: END IF;
4799:
4800: p_cust_account_id := null;
4801: x_return_status := FND_API.G_RET_STS_ERROR;
4812: --THEN
4813: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4814: --END IF;
4815: IF l_debug_level > 4 THEN
4816: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4817: END IF;
4818: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4819:
4820: end;
4825: if (nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4826: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4827: then
4828: IF l_debug_level > 0 THEN
4829: oe_debug_pub.add ('AAC:FSTI: site_id:'|| p_site_id||' is not null, picking up cust_account_id from it');
4830: END IF;
4831: begin
4832: select distinct cust_account_id
4833: into p_cust_account_id
4838:
4839: WHEN NO_DATA_FOUND THEN
4840:
4841: IF l_debug_level > 4 THEN
4842: oe_debug_pub.add ('AAC:VTI: site_id: no cust_account_id via site_id');
4843: END IF;
4844: p_cust_account_id := null;
4845:
4846: WHEN TOO_MANY_ROWS THEN
4844: p_cust_account_id := null;
4845:
4846: WHEN TOO_MANY_ROWS THEN
4847: IF l_debug_level > 4 THEN
4848: oe_debug_pub.add ('AAC:VTI: site_id: multiple accounts, returning with error ');
4849: END IF;
4850:
4851: x_return_status := FND_API.G_RET_STS_ERROR;
4852: p_cust_account_id := null;
4862: --THEN
4863: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4864: --END IF;
4865: IF l_debug_level > 4 THEN
4866: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4867: END IF;
4868: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4869:
4870: end;
4877:
4878: if nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4879: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4880: IF l_debug_level > 0 THEN
4881: oe_debug_pub.add ('AAC:FSTI: party_id:'|| p_party_id||' is not null, picking up account_id from it');
4882: END IF;
4883:
4884: open c_get_account_id(p_party_id);
4885:
4890:
4891: if c_get_account_id%ROWCOUNT > 1 then
4892:
4893: IF l_debug_level > 0 THEN
4894: oe_debug_pub.add ('AAC:FSTI: ERROR: multiple accounts found');
4895: END IF;
4896:
4897: --x_return_status := FND_API.G_RET_STS_ERROR;
4898: p_cust_account_id := null;
4907:
4908: if (c_get_account_id%ROWCOUNT = 1)
4909: then
4910: IF l_debug_level > 0 THEN
4911: oe_debug_pub.add ('AAC:FSTI: one account found:'||p_cust_account_id);
4912: END IF;
4913: end if;
4914:
4915: if (c_get_account_id%ROWCOUNT = 0)
4914:
4915: if (c_get_account_id%ROWCOUNT = 0)
4916: then
4917: IF l_debug_level > 0 THEN
4918: oe_debug_pub.add ('AAC:FSTI: no account found');
4919: END IF;
4920: p_cust_account_id := NULL;
4921: end if;
4922:
4932: IF p_permission <> 'Y' THEN
4933: -- reset cust_account_id, set error condition
4934: -- and return
4935: IF l_debug_level > 0 THEN
4936: oe_debug_pub.add ('AAC:FSTI: no permission to create account: returning');
4937: END IF;
4938: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
4939: OE_MSG_PUB.ADD;
4940: p_cust_account_id := NULL;
4946: -- no matching cust_account_id found,
4947: -- we have permission to create account,
4948: -- and going to search for name in party layer
4949: IF l_debug_level > 0 THEN
4950: oe_debug_pub.add ('AAC:FSTI: have permission to create account: trying to find party_id');
4951: END IF;
4952:
4953: p_party_id := get_party_id(
4954: p_party_name => p_party_name,
4961: THEN
4962: -- didn't find a party_id either
4963: -- since we *cannot* create a party, error out
4964: IF l_debug_level > 0 THEN
4965: oe_debug_pub.add ('AAC:FSTI: cannot find party_id, returning with error');
4966: END IF;
4967: p_party_id := NULL;
4968: x_return_status := FND_API.G_RET_STS_ERROR;
4969: return;
4969: return;
4970: END IF;
4971:
4972: IF l_debug_level > 0 THEN
4973: oe_debug_pub.add ('AAC:FSTI: found party_id:'||p_party_id);
4974: END IF;
4975:
4976:
4977: IF l_debug_level > 0 THEN
4974: END IF;
4975:
4976:
4977: IF l_debug_level > 0 THEN
4978: oe_debug_pub.add ('AAC:VTI: ending find_sold_to_id');
4979: END IF;
4980:
4981: return;
4982:
4992: OE_MSG_PUB.Add;
4993:
4994: END IF;
4995: IF l_debug_level > 4 THEN
4996: oe_debug_pub.add ('AAC:VTI: invalid p_cust_account_id in find_sold_to_id'); -- got a party_name
4997: END IF;
4998:
4999: x_return_status := FND_API.G_RET_STS_ERROR;
5000: return;
5008: ,'find_Sold_To_Org_id'
5009: );
5010: END IF;
5011: IF l_debug_level > 4 THEN
5012: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id'); -- got a party_name
5013: END IF;
5014: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5015:
5016: END find_sold_to_id;
5027: ,x_return_status OUT NOCOPY varchar2
5028: )
5029: IS
5030: -- local variables here
5031: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
5032:
5033: BEGIN
5034:
5035: x_return_status := FND_API.G_RET_STS_SUCCESS;
5034:
5035: x_return_status := FND_API.G_RET_STS_SUCCESS;
5036:
5037: IF l_debug_level > 0 THEN
5038: oe_debug_pub.add ('AAC:VTI: starting find_contact_id...');
5039: END IF;
5040:
5041: -- check if cust_contact_id is not null, return if so
5042: if nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5040:
5041: -- check if cust_contact_id is not null, return if so
5042: if nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5043: IF l_debug_level > 0 THEN
5044: oe_debug_pub.add ('AAC:FCI: cust_contact_id is not null, nothing to do: warn; returning}');
5045: END IF;
5046:
5047: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5048: THEN
5055: END IF;
5056:
5057: if nvl(p_sold_to_org_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5058: IF l_debug_level > 0 THEN
5059: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is null; returning');
5060: END IF;
5061:
5062: return;
5063: end if;
5063: end if;
5064:
5065:
5066: IF l_debug_level > 0 THEN
5067: oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5068: oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5069: oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5070: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5071: END IF;
5064:
5065:
5066: IF l_debug_level > 0 THEN
5067: oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5068: oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5069: oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5070: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5071: END IF;
5072:
5065:
5066: IF l_debug_level > 0 THEN
5067: oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5068: oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5069: oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5070: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5071: END IF;
5072:
5073: -- look for cust_contact_id using contact_name
5066: IF l_debug_level > 0 THEN
5067: oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5068: oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5069: oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5070: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5071: END IF;
5072:
5073: -- look for cust_contact_id using contact_name
5074:
5075:
5076: if nvl(p_contact_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
5077: and nvl(p_cust_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5078: IF l_debug_level > 0 THEN
5079: oe_debug_pub.add ('AAC:FCI: contact_name is not null,trying to find account contact_id');
5080: END IF;
5081: if p_site_use_code <> 'END_CUST' then
5082: p_cust_contact_id := OE_Value_To_Id.sold_to_contact(
5083: p_sold_to_contact => p_contact_name
5093: IF nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5094: THEN
5095: -- found a cust_contact_id
5096: IF l_debug_level > 0 THEN
5097: oe_debug_pub.add ('AAC:FCI: found cust_contact_id:'||p_cust_contact_id||', returning}');
5098: END IF;
5099: return;
5100: else
5101: p_cust_contact_id := null;
5105:
5106: -- check if contact_id is not null, return if so
5107: if nvl(p_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5108: IF l_debug_level > 0 THEN
5109: oe_debug_pub.add ('AAC:FSTI: org_contact_id is not null');
5110: END IF;
5111:
5112: RETURN;
5113: END IF;
5117: -- no matching cust_contact_id found,
5118: -- we have permission to create contact,
5119: -- and going to search for name in party layer
5120: IF l_debug_level > 0 THEN
5121: oe_debug_pub.add ('AAC:FCI: no account contact_id');
5122: oe_debug_pub.add ('AAC:FCI: have permission to create account contact: finding party contact_id');
5123: END IF;
5124:
5125: IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5118: -- we have permission to create contact,
5119: -- and going to search for name in party layer
5120: IF l_debug_level > 0 THEN
5121: oe_debug_pub.add ('AAC:FCI: no account contact_id');
5122: oe_debug_pub.add ('AAC:FCI: have permission to create account contact: finding party contact_id');
5123: END IF;
5124:
5125: IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5126: THEN
5135: THEN
5136: -- didn't find a party level contact_id either
5137: -- since we *cannot* create a party, error out
5138: IF l_debug_level > 0 THEN
5139: oe_debug_pub.add ('AAC:FCI: cannot find contact_id, returning with error}');
5140: END IF;
5141: p_contact_id := NULL;
5142: x_return_status := FND_API.G_RET_STS_ERROR;
5143: return;
5145: end if;
5146: -- found a contact_id
5147:
5148: IF l_debug_level > 0 THEN
5149: oe_debug_pub.add ('AAC:FCI: found contact_id:'||p_contact_id);
5150: oe_debug_pub.add ('AAC:VTI: ...done find_contact_id');
5151: END IF;
5152:
5153: return;
5146: -- found a contact_id
5147:
5148: IF l_debug_level > 0 THEN
5149: oe_debug_pub.add ('AAC:FCI: found contact_id:'||p_contact_id);
5150: oe_debug_pub.add ('AAC:VTI: ...done find_contact_id');
5151: END IF;
5152:
5153: return;
5154:
5178: ,x_msg_count out NOCOPY varchar2
5179: )
5180: IS
5181: -- local variables here
5182: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
5183: l_cust_account_id number := null;
5184:
5185: BEGIN
5186:
5186:
5187: x_return_status := FND_API.G_RET_STS_SUCCESS;
5188:
5189: IF l_debug_level > 0 THEN
5190: oe_debug_pub.add ('AAC:VTI: FSI: starting find_site_id{');
5191: END IF;
5192:
5193: if (nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5194: then
5192:
5193: if (nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5194: then
5195: IF l_debug_level > 0 THEN
5196: oe_debug_pub.add ('AAC:FSI: party_id null; returning');
5197: END IF;
5198:
5199: return;
5200: end if;
5209:
5210: -- check if site_address1 is null
5211: IF l_debug_level > 0 THEN
5212: if nvl(p_site_address1,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
5213: oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5214: else
5215: oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5216: end if;
5217: oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5211: IF l_debug_level > 0 THEN
5212: if nvl(p_site_address1,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
5213: oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5214: else
5215: oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5216: end if;
5217: oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5218: END IF;
5219:
5213: oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5214: else
5215: oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5216: end if;
5217: oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5218: END IF;
5219:
5220: -- check if cust_site_use_id is not null, return if so
5221: if nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5219:
5220: -- check if cust_site_use_id is not null, return if so
5221: if nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5222: IF l_debug_level > 0 THEN
5223: oe_debug_pub.add ('AAC:FSI: account_site_use_id is not null; returning');
5224: END IF;
5225:
5226: RETURN;
5227: END IF;
5228:
5229: -- check if site_use_code is null, return with error if so
5230: if nvl(p_site_use_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR then
5231: IF l_debug_level > 0 THEN
5232: oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5233: oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5234: end if;
5235: x_return_status := FND_API.G_RET_STS_ERROR;
5236: return;
5229: -- check if site_use_code is null, return with error if so
5230: if nvl(p_site_use_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR then
5231: IF l_debug_level > 0 THEN
5232: oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5233: oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5234: end if;
5235: x_return_status := FND_API.G_RET_STS_ERROR;
5236: return;
5237: end if;
5255: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5256: then
5257: --nothing to do! return!
5258: IF l_debug_level > 0 THEN
5259: oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5260: END IF;
5261: return;
5262: end if;
5263:
5261: return;
5262: end if;
5263:
5264: IF l_debug_level > 0 THEN
5265: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5266: oe_debug_pub.add('p_site_id :'|| p_site_id );
5267: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5268: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5262: end if;
5263:
5264: IF l_debug_level > 0 THEN
5265: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5266: oe_debug_pub.add('p_site_id :'|| p_site_id );
5267: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5268: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5263:
5264: IF l_debug_level > 0 THEN
5265: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5266: oe_debug_pub.add('p_site_id :'|| p_site_id );
5267: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5268: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5264: IF l_debug_level > 0 THEN
5265: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5266: oe_debug_pub.add('p_site_id :'|| p_site_id );
5267: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5268: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5265: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5266: oe_debug_pub.add('p_site_id :'|| p_site_id );
5267: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5268: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5266: oe_debug_pub.add('p_site_id :'|| p_site_id );
5267: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5268: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5267: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5268: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5268: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5276: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5269: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5276: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5277: oe_debug_pub.add('p_site_country :'|| p_site_country );
5270: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5276: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5277: oe_debug_pub.add('p_site_country :'|| p_site_country );
5278: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5271: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5276: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5277: oe_debug_pub.add('p_site_country :'|| p_site_country );
5278: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5279: oe_debug_pub.add('p_party_id :'|| p_party_id );
5272: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5276: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5277: oe_debug_pub.add('p_site_country :'|| p_site_country );
5278: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5279: oe_debug_pub.add('p_party_id :'|| p_party_id );
5280: end if;
5273: oe_debug_pub.add('p_site_org :'|| p_site_org );
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5276: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5277: oe_debug_pub.add('p_site_country :'|| p_site_country );
5278: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5279: oe_debug_pub.add('p_party_id :'|| p_party_id );
5280: end if;
5281:
5274: oe_debug_pub.add('p_site_city :'|| p_site_city );
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5276: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5277: oe_debug_pub.add('p_site_country :'|| p_site_country );
5278: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5279: oe_debug_pub.add('p_party_id :'|| p_party_id );
5280: end if;
5281:
5282: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5275: oe_debug_pub.add('p_site_state :'|| p_site_state );
5276: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5277: oe_debug_pub.add('p_site_country :'|| p_site_country );
5278: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5279: oe_debug_pub.add('p_party_id :'|| p_party_id );
5280: end if;
5281:
5282: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5283: and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5281:
5282: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5283: and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5284: IF l_debug_level > 0 THEN
5285: oe_debug_pub.add ('AAC:FSI: party_site_use_id:'||p_site_use_id||' is not null; using it to get party_site_id');
5286: END IF;
5287: begin
5288:
5289: select party_site_id
5303: OE_MSG_PUB.ADD;
5304: END IF;
5305:
5306: IF l_debug_level > 4 THEN
5307: oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
5308: END IF;
5309:
5310: x_return_status := FND_API.G_RET_STS_ERROR;
5311: return;
5319: ,'find_site_id'
5320: );
5321: --END IF;
5322: IF l_debug_level > 4 THEN
5323: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5324: END IF;
5325: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5326:
5327: end;
5330:
5331: if nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5332: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5333: IF l_debug_level > 0 THEN
5334: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5335: END IF;
5336: begin
5337: select s.party_id
5338: into p_party_id
5340: where s.party_site_id=p_site_id
5341: and s.status='A';
5342:
5343: IF l_debug_level > 0 THEN
5344: oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5345: END IF;
5346:
5347:
5348: EXCEPTION
5356: OE_MSG_PUB.ADD;
5357:
5358: END IF;
5359: IF l_debug_level > 4 THEN
5360: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5361: END IF;
5362: x_return_status := FND_API.G_RET_STS_ERROR;
5363: return;
5364:
5371: ,'find_site_id'
5372: );
5373: END IF;
5374: IF l_debug_level > 4 THEN
5375: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5376: END IF;
5377: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5378:
5379: end;
5390: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: then
5392: --nothing to do! return!
5393: IF l_debug_level > 0 THEN
5394: oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5395: END IF;
5396: return;
5397: end if;
5398:
5397: end if;
5398:
5399: -- look for cust_site_id using site_name
5400: IF l_debug_level > 0 THEN
5401: oe_debug_pub.add ('AAC:FSI: trying to find account site_use_id');
5402: END IF;
5403:
5404: /*
5405: if p_site_use_code='SHIP_TO'
5456:
5457: else
5458: -- ERROR!
5459: IF l_debug_level > 0 THEN
5460: oe_debug_pub.add ('AAC:FSI: site_use_code is invalid:'||p_site_use_code);
5461: END IF;
5462: return;
5463: end if;
5464:
5466: IF nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5467: THEN
5468: -- found a a cust_site_id
5469: IF l_debug_level > 0 THEN
5470: oe_debug_pub.add ('AAC:FSI: found cust_site_use_id:'||p_account_site_use_id||', returning');
5471: oe_debug_pub.add ('AAC:VTI: ending find_site_id');
5472: END IF;
5473:
5474:
5467: THEN
5468: -- found a a cust_site_id
5469: IF l_debug_level > 0 THEN
5470: oe_debug_pub.add ('AAC:FSI: found cust_site_use_id:'||p_account_site_use_id||', returning');
5471: oe_debug_pub.add ('AAC:VTI: ending find_site_id');
5472: END IF;
5473:
5474:
5475: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5473:
5474:
5475: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5476: IF l_debug_level > 0 THEN
5477: oe_debug_pub.add ('AAC:FSI: cust_site_use_id:'||p_account_site_use_id||' is not null; using it to get party_id');
5478: END IF;
5479: begin
5480: select a.party_id
5481: into p_party_id
5486: and u.cust_acct_site_id=s.cust_acct_site_id
5487: and s.cust_account_id=a.cust_account_id;
5488:
5489: IF l_debug_level > 0 THEN
5490: oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5491: END IF;
5492:
5493:
5494: EXCEPTION
5502: OE_MSG_PUB.ADD;
5503:
5504: END IF;
5505: IF l_debug_level > 4 THEN
5506: oe_debug_pub.add ('AAC:VTI: invalid cust_site_use_id ');
5507: END IF;
5508: x_return_status := FND_API.G_RET_STS_ERROR;
5509: return;
5510:
5524: -- no matching cust_site_id found,
5525: -- we have permission to create site,
5526: -- and going to search for name in party layer
5527: IF l_debug_level > 0 THEN
5528: oe_debug_pub.add ('AAC:FSI: no account site_use_id');
5529: oe_debug_pub.add ('AAC:FSI: finding party site_use_id');
5530: END IF;
5531:
5532: if (nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) then
5525: -- we have permission to create site,
5526: -- and going to search for name in party layer
5527: IF l_debug_level > 0 THEN
5528: oe_debug_pub.add ('AAC:FSI: no account site_use_id');
5529: oe_debug_pub.add ('AAC:FSI: finding party site_use_id');
5530: END IF;
5531:
5532: if (nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) then
5533: p_site_id := get_party_site_id(
5551: THEN
5552: -- didn't find a party level site_id either
5553: -- since we *cannot* create a party, error out
5554: IF l_debug_level > 0 THEN
5555: oe_debug_pub.add ('AAC:FSI: cannot find party_site_id, returning with error');
5556: END IF;
5557: p_site_id := NULL;
5558: --x_return_status := FND_API.G_RET_STS_ERROR;
5559: return;
5559: return;
5560: ELSE -- we did find a party_site_id, get party_id from it
5561: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5562: IF l_debug_level > 0 THEN
5563: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5564: END IF;
5565: begin
5566: select s.party_id
5567: into p_party_id
5569: where s.party_site_id=p_site_id
5570: and s.status='A';
5571:
5572: IF l_debug_level > 0 THEN
5573: oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5574: END IF;
5575:
5576:
5577: EXCEPTION
5585: OE_MSG_PUB.ADD;
5586:
5587: END IF;
5588: IF l_debug_level > 4 THEN
5589: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5590: END IF;
5591: x_return_status := FND_API.G_RET_STS_ERROR;
5592: return;
5593:
5600: ,'find_site_id'
5601: );
5602: END IF;
5603: IF l_debug_level > 4 THEN
5604: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5605: END IF;
5606: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5607:
5608: end;
5612:
5613: -- found a site_id
5614:
5615: IF l_debug_level > 0 THEN
5616: oe_debug_pub.add ('AAC:FSI: party site_use_id:'||p_site_use_id);
5617: oe_debug_pub.add ('AAC:FSI: account site_use_id:'||p_account_site_use_id);
5618: END IF;
5619:
5620: return;
5613: -- found a site_id
5614:
5615: IF l_debug_level > 0 THEN
5616: oe_debug_pub.add ('AAC:FSI: party site_use_id:'||p_site_use_id);
5617: oe_debug_pub.add ('AAC:FSI: account site_use_id:'||p_account_site_use_id);
5618: END IF;
5619:
5620: return;
5621:
5629: ,p_party_site_use_code in varchar2
5630: ) return number
5631: IS
5632: l_id NUMBER;
5633: l_debug_level CONSTANT NUMBER := 5;--oe_debug_pub.g_debug_level;
5634: BEGIN
5635: IF l_debug_level > 4 THEN
5636: oe_debug_pub.add ('AAC:VTI: starting get_party_id{');
5637: END IF;
5632: l_id NUMBER;
5633: l_debug_level CONSTANT NUMBER := 5;--oe_debug_pub.g_debug_level;
5634: BEGIN
5635: IF l_debug_level > 4 THEN
5636: oe_debug_pub.add ('AAC:VTI: starting get_party_id{');
5637: END IF;
5638:
5639: -- did they actually pass some values?
5640: IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5640: IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5641: AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5642: THEN
5643: IF l_debug_level > 4 THEN
5644: oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5645: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5646: END IF;
5647: RETURN NULL;
5648: END IF;
5641: AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5642: THEN
5643: IF l_debug_level > 4 THEN
5644: oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5645: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5646: END IF;
5647: RETURN NULL;
5648: END IF;
5649:
5650: IF (nvl(p_party_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char)
5651: THEN
5652: -- got a party_number
5653: IF l_debug_level > 4 THEN
5654: oe_debug_pub.add ('AAC:VTI:GPI party number: '||p_party_number);
5655: END IF;
5656:
5657: SELECT party_id
5658: INTO l_id
5661: and status='A';
5662:
5663: ELSE
5664: IF l_debug_level > 4 THEN
5665: oe_debug_pub.add ('AAC:VTI:GPI name: '||p_party_name); -- got a party_name
5666: END IF;
5667:
5668: SELECT party.party_id
5669: INTO l_id
5673:
5674: END IF;
5675:
5676: IF l_debug_level > 4 THEN
5677: oe_debug_pub.add ('AAC:VTI:GPI: party_id: '||l_id);
5678: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5679: END IF;
5680:
5681: return l_id;
5674: END IF;
5675:
5676: IF l_debug_level > 4 THEN
5677: oe_debug_pub.add ('AAC:VTI:GPI: party_id: '||l_id);
5678: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5679: END IF;
5680:
5681: return l_id;
5682: EXCEPTION
5703: OE_MSG_PUB.Add;
5704:
5705: END IF;
5706: IF l_debug_level > 4 THEN
5707: oe_debug_pub.add ('AAC:VTI: no data in Get_party_id'); -- got a party_name
5708: END IF;
5709: RETURN FND_API.G_MISS_NUM;
5710:
5711: WHEN OTHERS THEN
5717: ,'get_party_id' --modified for bug 4590205
5718: );
5719: END IF;
5720: IF l_debug_level > 4 THEN
5721: oe_debug_pub.add ('AAC:VTI: unexpected error in Get_party_id'); -- got a party_name
5722: END IF;
5723: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5724:
5725: END get_party_id;
5750: AND arl.lookup_type(+)='CONTACT_TITLE';
5751:
5752:
5753: l_id NUMBER;
5754: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5755: BEGIN
5756:
5757: IF l_debug_level > 4 THEN
5758: oe_debug_pub.add ('AAC:VTI: starting get_party_contact_id...');
5754: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5755: BEGIN
5756:
5757: IF l_debug_level > 4 THEN
5758: oe_debug_pub.add ('AAC:VTI: starting get_party_contact_id...');
5759: END IF;
5760:
5761: IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5762: THEN
5760:
5761: IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5762: THEN
5763: IF l_debug_level > 4 THEN
5764: oe_debug_pub.add ('AAC:VTI: no contact_name');
5765: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5766: END IF;
5767: RETURN NULL;
5768: END IF;
5761: IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5762: THEN
5763: IF l_debug_level > 4 THEN
5764: oe_debug_pub.add ('AAC:VTI: no contact_name');
5765: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5766: END IF;
5767: RETURN NULL;
5768: END IF;
5769:
5773:
5774: IF c_org_contact_id%FOUND then
5775: CLOSE c_org_contact_id;
5776: IF l_debug_level > 4 THEN
5777: oe_debug_pub.add ('AAC:VTI: org_contact_id:'||l_id);
5778: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5779: end if;
5780: return l_id;
5781: end if;
5774: IF c_org_contact_id%FOUND then
5775: CLOSE c_org_contact_id;
5776: IF l_debug_level > 4 THEN
5777: oe_debug_pub.add ('AAC:VTI: org_contact_id:'||l_id);
5778: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5779: end if;
5780: return l_id;
5781: end if;
5782:
5780: return l_id;
5781: end if;
5782:
5783: IF l_debug_level > 4 THEN
5784: oe_debug_pub.add ('AAC:VTI: GCI org_contact_id not found');
5785: end if;
5786:
5787: CLOSE c_org_contact_id;
5788:
5786:
5787: CLOSE c_org_contact_id;
5788:
5789: IF l_debug_level > 4 THEN
5790: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5791: END IF;
5792:
5793: return NULL;
5794:
5898: l_sold_to_party_id number;
5899: l_dummy number;
5900:
5901: --
5902: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5903: --
5904: BEGIN
5905:
5906: IF l_debug_level > 0 THEN
5903: --
5904: BEGIN
5905:
5906: IF l_debug_level > 0 THEN
5907: oe_debug_pub.add( 'AAC: site_address1:'||P_SITE_ADDRESS1);
5908: oe_debug_pub.add(' address4:'||p_site_address4);
5909: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5910: END IF;
5911:
5904: BEGIN
5905:
5906: IF l_debug_level > 0 THEN
5907: oe_debug_pub.add( 'AAC: site_address1:'||P_SITE_ADDRESS1);
5908: oe_debug_pub.add(' address4:'||p_site_address4);
5909: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5910: END IF;
5911:
5912: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5905:
5906: IF l_debug_level > 0 THEN
5907: oe_debug_pub.add( 'AAC: site_address1:'||P_SITE_ADDRESS1);
5908: oe_debug_pub.add(' address4:'||p_site_address4);
5909: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5910: END IF;
5911:
5912: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5913: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5917: THEN
5918: IF l_debug_level > 0 THEN
5919: oe_debug_pub.add( 'AAC: all incoming data missing,returning');
5920: end if;
5921: RETURN NULL;
5922: END IF;
5923:
5922: END IF;
5923:
5924: if (nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) then
5925: IF l_debug_level > 0 THEN
5926: oe_debug_pub.add( 'AAC: incoming party_id is '||p_party_id ) ;
5927: END IF;
5928:
5929: OPEN c_party_site_id(p_party_id);
5930: FETCH c_party_site_id
5932:
5933: IF c_party_site_id%FOUND then
5934: CLOSE c_party_site_id;
5935: IF l_debug_level > 0 THEN
5936: oe_debug_pub.add( 'AAC: found party_site_id is '||l_id ) ;
5937: END IF;
5938: return l_id;
5939:
5940: ELSE
5938: return l_id;
5939:
5940: ELSE
5941: IF l_debug_level > 0 THEN
5942: oe_debug_pub.add( 'AAC: not found party_site_id in 1st try; trying SQL2' ) ;
5943: END IF;
5944:
5945: SELECT site.party_site_id
5946: INTO l_id
5963: END IF;
5964: CLOSE c_party_site_id;
5965:
5966: IF l_debug_level > 0 THEN
5967: oe_debug_pub.add( 'AAC: found party_site_id is '||l_id );
5968: END IF;
5969:
5970: RETURN l_id;
5971:
5971:
5972: ELSE --p_party_id is null...
5973:
5974: IF l_debug_level > 0 THEN
5975: oe_debug_pub.add( 'AAC: party_id is null' ) ;
5976: END IF;
5977:
5978: OPEN C2;
5979:
5982:
5983: IF C2%FOUND then
5984: CLOSE C2 ;
5985: IF l_debug_level > 0 THEN
5986: oe_debug_pub.add( 'AAC: found site_use_id = '||L_ID ) ;
5987: END IF;
5988: return l_id;
5989:
5990: ELSE
5988: return l_id;
5989:
5990: ELSE
5991: IF l_debug_level > 0 THEN
5992: oe_debug_pub.add( 'AAC: not found party_site_id in 1st try; trying SQL2' ) ;
5993: END IF;
5994:
5995: SELECT site.party_site_id
5996: INTO l_id
6009: AND site.status = 'A'
6010: and site.location_id=loc.location_id;
6011:
6012: IF l_debug_level > 0 THEN
6013: oe_debug_pub.add( ' found site_use_id = '||L_ID ) ;
6014: END IF;
6015:
6016: END IF;
6017: CLOSE C2;
6076: ) RETURN NUMBER
6077: IS
6078: l_id NUMBER;
6079: --
6080: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6081: --
6082: BEGIN
6083:
6084: IF nvl(p_sold_to_org,fnd_api.g_miss_char) = fnd_api.g_miss_char
6162: WHEN NO_DATA_FOUND THEN
6163: x_found:='N';
6164:
6165: WHEN OTHERS THEN
6166: oe_debug_pub.add('Yes... Error in Autonomous Block:'||SQLERRM);
6167: x_found:='E';
6168: END;
6169:
6170: --------------------------------------------------------------
6174: BEGIN
6175:
6176: does_Cust_Exist(p_cust_id => cust_id,
6177: x_found => l_temp);
6178: oe_debug_pub.add('Yes.. does_Cust_Exist:'||l_temp);
6179: IF l_temp='Y' THEN
6180: return TRUE;
6181: ELSIF l_temp='N' THEN
6182: oe_debug_pub.add('Yes.. Committing');
6178: oe_debug_pub.add('Yes.. does_Cust_Exist:'||l_temp);
6179: IF l_temp='Y' THEN
6180: return TRUE;
6181: ELSIF l_temp='N' THEN
6182: oe_debug_pub.add('Yes.. Committing');
6183: COMMIT;
6184: return TRUE;
6185: ELSE
6186: return FALSE;
6186: return FALSE;
6187: END IF;
6188:
6189: EXCEPTION WHEN OTHERS THEN
6190: oe_debug_pub.add('Yes... Error in CUST_EXISTS:'||SQLERRM);
6191: return FALSE;
6192: END Cust_Exists;
6193:
6194: END oe_create_account_info;