[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
686: and rownum = 1; --added for bug# 14113693
687:
688: found_relationship := TRUE;
689: IF l_debug_level > 0 THEN
690: oe_debug_pub.add( 'AAC:RC: relationship found' ) ;
691: END IF;
692:
693: EXCEPTION
694: WHEN NO_DATA_FOUND THEN
692:
693: EXCEPTION
694: WHEN NO_DATA_FOUND THEN
695: IF l_debug_level > 0 THEN
696: oe_debug_pub.add( 'AAC:RC: no relationship' ) ;
697: END IF;
698: found_relationship := FALSE;
699:
700: END; -- begin
702: if(found_relationship = FALSE) then
703:
704: if (l_add_customer = 'Y')
705: then
706: oe_debug_pub.add('AAC:RC: l_add_customer is Y');
707: oe_debug_pub.add('AAC:RC: creating relationship');
708: oe_oe_inline_address.create_cust_relationship(
709: p_cust_acct_id => p_site_tbl(i).p_cust_account_id
710: ,p_related_cust_acct_id => l_cust_account_id
703:
704: if (l_add_customer = 'Y')
705: then
706: oe_debug_pub.add('AAC:RC: l_add_customer is Y');
707: oe_debug_pub.add('AAC:RC: creating relationship');
708: oe_oe_inline_address.create_cust_relationship(
709: p_cust_acct_id => p_site_tbl(i).p_cust_account_id
710: ,p_related_cust_acct_id => l_cust_account_id
711: ,p_reciprocal_flag => 'Y'
713: ,x_msg_count => x_msg_count
714: ,x_msg_data => x_msg_data);
715:
716: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
717: oe_debug_pub.add('Creating relationship failed');
718:
719: x_return_status := FND_API.G_RET_STS_ERROR;
720: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
721: FND_MESSAGE.Set_Token('TEXT',' Customer Relationship creation failed. ', FALSE);
722: OE_MSG_PUB.ADD;
723: OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count , p_data => x_msg_data );
724: return;
725: END IF; -- if failure
726: oe_debug_pub.add('AAC:RC: relationship created');
727: else -- l_add_customer = 'N'
728: x_return_status := FND_API.G_RET_STS_ERROR;
729: IF l_debug_level > 0 THEN
730: oe_debug_pub.add('AAC:RC: ERROR: l_add_customer is N' );
726: oe_debug_pub.add('AAC:RC: relationship created');
727: else -- l_add_customer = 'N'
728: x_return_status := FND_API.G_RET_STS_ERROR;
729: IF l_debug_level > 0 THEN
730: oe_debug_pub.add('AAC:RC: ERROR: l_add_customer is N' );
731: END IF;
732: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
733: FND_MESSAGE.Set_Token('TEXT',' Customer Relationship not defined. No permission to create relationship. ', FALSE);
734: OE_MSG_PUB.ADD;
736: return;
737: end if; -- l_add_customer end
738: end if; -- found_relationship
739: else -- l_rc_matched = TRUE
740: oe_debug_pub.add('AAC:RC: Customers are same. continue');
741: end if; --l_rc_matched
742:
743:
744: elsif (lcustomer_relations = 'A')
742:
743:
744: elsif (lcustomer_relations = 'A')
745: then
746: oe_debug_pub.add('AAC:RC: Relationship is A. Continue');
747: end if;
748:
749: END IF; -- if p_site_tbl account_id is not null
750:
755:
756: END IF;/* we have p_site_tbl} */
757:
758: IF l_debug_level > 0 THEN
759: oe_debug_pub.add( 'AAC: ...done related customer' ) ;
760: END IF;
761: /* done related customer here */
762:
763: -- if the check_customer did not fail or did not return multiple accts
768: p_party_customer_rec.p_cust_account_id IS NOT NULL) AND
769: p_Control_rec.p_process_contact
770: THEN
771: IF l_debug_level > 0 THEN
772: oe_debug_pub.add( 'checking and creating contact' ) ;
773: END IF;
774: -- if contact was found in check account because of party relationship
775: -- then use that contact
776: IF l_cust_account_role_id is null AND
775: -- then use that contact
776: IF l_cust_account_role_id is null AND
777: l_org_contact_id is null then
778: IF l_debug_level > 0 THEN
779: oe_debug_pub.add( 'taking contact informaton from record' ) ;
780: END IF;
781: l_cust_account_role_id :=p_party_customer_rec.p_cust_account_role_id;
782: l_org_contact_id :=p_party_customer_rec.p_org_contact_id;
783: END IF;
810: );
811:
812:
813: IF l_debug_level > 0 THEN
814: oe_debug_pub.add( 'count contact table = '||P_CONTACT_TBL.COUNT ) ;
815: END IF;
816: IF p_Contact_tbl.COUNT > 0 THEN
817: FOR i in p_contact_tbl.FIRST..p_contact_tbl.LAST
818: LOOP
816: IF p_Contact_tbl.COUNT > 0 THEN
817: FOR i in p_contact_tbl.FIRST..p_contact_tbl.LAST
818: LOOP
819: IF l_debug_level > 0 THEN
820: oe_debug_pub.add( 'contact_id = '||P_CONTACT_TBL ( I ) ) ;
821: END IF;
822: END LOOP;
823: END IF;
824:
823: END IF;
824:
825: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
826: IF l_debug_level > 0 THEN
827: oe_debug_pub.add( 'contact with error' ) ;
828: END IF;
829: l_contact_status := FND_API.G_RET_STS_ERROR;
830: x_msg_data_contact := x_msg_data;
831: x_msg_count_contact := x_msg_count;
839:
840: --If multiple Contact then return the contact table
841: IF p_contact_tbl.COUNT > 1 then
842: IF l_debug_level > 0 THEN
843: oe_debug_pub.add( 'returning with multiple contacts' ) ;
844: end IF;
845:
846: if p_control_rec.p_multiple_contact_is_error then
847: IF l_debug_level > 0 THEN
844: end IF;
845:
846: if p_control_rec.p_multiple_contact_is_error then
847: IF l_debug_level > 0 THEN
848: oe_debug_pub.add( 'erroring with multiple contacts' ) ;
849: END IF;
850: x_return_status := FND_API.G_RET_STS_ERROR;
851: else
852: x_return_status := FND_API.G_RET_STS_SUCCESS;
853: end if;
854: return;
855: ELSIF p_contact_tbl.COUNT = 0 then
856: IF l_debug_level > 0 THEN
857: oe_debug_pub.add( 'no contacts found error' ) ;
858: END IF;
859: x_return_status := FND_API.G_RET_STS_ERROR;
860: ELSIF p_contact_tbl.COUNT = 1 then
861: null;
862: END IF;
863:
864: ELSE
865: IF l_debug_level > 0 THEN
866: oe_debug_pub.add( 'not calling check contact' ) ;
867: END IF;
868:
869: END IF; -- if contact data is passed
870:
870:
871: ELSE -- if status is not success
872:
873: IF l_debug_level > 0 THEN
874: oe_debug_pub.add( '2 not calling check contact' ) ;
875: END IF;
876:
877: END IF; -- if status was successful
878:
878:
879:
880: IF p_control_rec.p_continue_processing_on_error then
881: IF l_debug_level > 0 THEN
882: oe_debug_pub.add( 'continue processing on error' ) ;
883: end if;
884: else
885: if l_debug_level > 0 then
886: oe_debug_pub.add( 'do not continue processing on error' ) ;
882: oe_debug_pub.add( 'continue processing on error' ) ;
883: end if;
884: else
885: if l_debug_level > 0 then
886: oe_debug_pub.add( 'do not continue processing on error' ) ;
887: END IF;
888: END IF;
889:
890: -- If status is not error and there are records in the site table
896:
897: FOR i IN p_site_tbl.FIRST..p_site_tbl.LAST LOOP
898:
899: IF l_debug_level > 0 THEN
900: oe_debug_pub.add ('AAC:VTI: ============ SITE CONTACT '||i||' of '||p_site_tbl.LAST||' =============== ');
901: oe_debug_pub.add( 'AAC: processing site#'||i||' for contacts');
902: END IF;
903:
904: l2_cust_account_id := l_cust_account_id;
897: FOR i IN p_site_tbl.FIRST..p_site_tbl.LAST LOOP
898:
899: IF l_debug_level > 0 THEN
900: oe_debug_pub.add ('AAC:VTI: ============ SITE CONTACT '||i||' of '||p_site_tbl.LAST||' =============== ');
901: oe_debug_pub.add( 'AAC: processing site#'||i||' for contacts');
902: END IF;
903:
904: l2_cust_account_id := l_cust_account_id;
905: l2_party_id := l_party_id;
909: OR nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM )
910: then
911: l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
912: l2_party_id := p_site_tbl(i).p_party_id;
913: oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
914: oe_debug_pub.add('site level party_id: '||l2_party_id);
915: end if;
916:
917: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
910: then
911: l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
912: l2_party_id := p_site_tbl(i).p_party_id;
913: oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
914: oe_debug_pub.add('site level party_id: '||l2_party_id);
915: end if;
916:
917: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
918: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
913: oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
914: oe_debug_pub.add('site level party_id: '||l2_party_id);
915: end if;
916:
917: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
918: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
919: if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
920: = FND_API.G_MISS_NUM) THEN
921: l_cust_account_role_id:=null;
914: oe_debug_pub.add('site level party_id: '||l2_party_id);
915: end if;
916:
917: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
918: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
919: if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
920: = FND_API.G_MISS_NUM) THEN
921: l_cust_account_role_id:=null;
922: else
921: l_cust_account_role_id:=null;
922: else
923: l_cust_account_role_id:=p_site_tbl(i).p_cust_account_role_id;
924: end if;
925: oe_debug_pub.add( 'AAC: After Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
926:
927:
928: if nvl(p_site_tbl(i).p_org_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
929: l2_org_contact_id := p_site_tbl(i).p_org_contact_id;
961: ,x_msg_data=>x_msg_data
962: );
963:
964: IF l_debug_level > 0 THEN
965: oe_debug_pub.add( 'AAC: ..done calling check_and_create_contacts ' ) ;
966: oe_debug_pub.add( 'count contact table = '||P2_CONTACT_TBL.COUNT ) ;
967: END IF;
968:
969: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
962: );
963:
964: IF l_debug_level > 0 THEN
965: oe_debug_pub.add( 'AAC: ..done calling check_and_create_contacts ' ) ;
966: oe_debug_pub.add( 'count contact table = '||P2_CONTACT_TBL.COUNT ) ;
967: END IF;
968:
969: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
970: IF l_debug_level > 0 THEN
967: END IF;
968:
969: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
970: IF l_debug_level > 0 THEN
971: oe_debug_pub.add( 'contact with error' ) ;
972: END IF;
973:
974: l_contact_status := FND_API.G_RET_STS_ERROR;
975: x_msg_data_contact := x_msg_data;
980: --If multiple Contact then return the contact table
981: IF p2_contact_tbl.COUNT > 1 then
982: if p_control_rec.p_multiple_contact_is_error then
983: IF l_debug_level > 0 THEN
984: oe_debug_pub.add( 'erroring with multiple contacts' ) ;
985: END IF;
986: x_return_status := FND_API.G_RET_STS_ERROR;
987: else
988: x_return_status := FND_API.G_RET_STS_SUCCESS;
989: end if;
990:
991: ELSIF p2_contact_tbl.COUNT = 0 then
992: IF l_debug_level > 0 THEN
993: oe_debug_pub.add( 'no contacts found error' ) ;
994: END IF;
995: x_return_status := FND_API.G_RET_STS_ERROR;
996: ELSIF p2_contact_tbl.COUNT = 1 then
997: p_site_tbl(i).p_cust_account_role_id := p2_contact_tbl(1);
998: END IF;
999: end loop;
1000:
1001: IF l_debug_level > 0 THEN
1002: oe_debug_pub.add( 'XX calling check_and_create_sites... ' ) ;
1003: END IF;
1004:
1005: -- check_and_Create_Sites should also valdiate against related
1006: -- customers now. and create their sites if ncessary
1018: G_fetch_primary_party_sites :=p_control_rec.p_fetch_primary_party_sites;
1019:
1020: IF l_debug_level > 0 THEN
1021: IF (G_fetch_primary_party_sites) THEN
1022: oe_debug_pub.add('G_fetch_primary_party_sites :TRUE');
1023: ELSE
1024: oe_debug_pub.add('G_fetch_primary_party_sites :FALSE');
1025: END IF;
1026: END IF;
1020: IF l_debug_level > 0 THEN
1021: IF (G_fetch_primary_party_sites) THEN
1022: oe_debug_pub.add('G_fetch_primary_party_sites :TRUE');
1023: ELSE
1024: oe_debug_pub.add('G_fetch_primary_party_sites :FALSE');
1025: END IF;
1026: END IF;
1027:
1028: /*cc project */
1037: ,x_msg_count=>x_msg_count
1038: );
1039:
1040: IF l_debug_level > 0 THEN
1041: oe_debug_pub.add( 'XX ...done calling check_and_create_sites ' ) ;
1042: END IF;
1043:
1044: ELSE
1045: IF l_debug_level > 0 THEN
1042: END IF;
1043:
1044: ELSE
1045: IF l_debug_level > 0 THEN
1046: oe_debug_pub.add( 'not calling check_and_create_sites X ' ) ;
1047: END IF;
1048:
1049: END IF; -- if site table exists
1050:
1053: -- however in telesales we will never commit so it is fine
1054: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1055: IF p_control_rec.p_commit THEN
1056: IF l_debug_level > 0 THEN
1057: oe_debug_pub.add( 'commiting the txn' ) ;
1058: end if;
1059: commit;
1060:
1061: end if;
1061: end if;
1062: else
1063:
1064: if l_debug_level > 0 then
1065: oe_debug_pub.add( 'not commiting the txn' ) ;
1066: END IF;
1067: l_site_failed := TRUE;
1068: OE_MSG_PUB.Count_And_Get
1069: ( p_count => x_msg_count
1076: -- THere might be failure in contact processing
1077: -- to show that message we send the status as error
1078: -- since in the client messages are displayed if only status is error
1079: IF l_debug_level > 0 THEN
1080: oe_debug_pub.add( ' checking contact failure status = '||l_contact_status ) ;
1081: END IF;
1082: IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1083: IF l_debug_level > 0 THEN
1084: oe_debug_pub.add( ' returning error for contact' ) ;
1080: oe_debug_pub.add( ' checking contact failure status = '||l_contact_status ) ;
1081: END IF;
1082: IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1083: IF l_debug_level > 0 THEN
1084: oe_debug_pub.add( ' returning error for contact' ) ;
1085: END IF;
1086: x_return_status := FND_API.G_RET_STS_ERROR;
1087:
1088: -- If site has failed then count and get above will catch that
1088: -- If site has failed then count and get above will catch that
1089: -- else to find the error message for contact we do this
1090: IF NOT l_site_failed then
1091: IF l_debug_level > 0 THEN
1092: oe_debug_pub.add( ' msg from contact only' ) ;
1093: END IF;
1094: x_msg_data := x_msg_data_contact;
1095: x_msg_count := x_msg_count_contact;
1096: END IF;
1095: x_msg_count := x_msg_count_contact;
1096: END IF;
1097:
1098: END IF;
1099: oe_debug_pub.add( 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
1100:
1101: EXCEPTION
1102:
1103: WHEN FND_API.G_EXC_ERROR THEN
1151: END IF;
1152:
1153: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1154: IF l_debug_level > 0 THEN
1155: oe_debug_pub.add( 'CREATE_ACCOUNT_LAYER WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
1156: END IF;
1157:
1158: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1159: THEN
1290: --l_site_party_site_use_id number := null;
1291: px_party_id number := null;
1292:
1293: --
1294: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
1295: --
1296: BEGIN
1297:
1298: px_party_id := p_party_id;
1302: x_return_status := FND_API.G_RET_STS_SUCCESS;
1303: l_return_status := FND_API.G_RET_STS_SUCCESS;
1304:
1305: IF l_debug_level > 0 THEN
1306: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1307: oe_debug_pub.add(' party_id = '||p_party_id);
1308: oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1309: END IF;
1310:
1303: l_return_status := FND_API.G_RET_STS_SUCCESS;
1304:
1305: IF l_debug_level > 0 THEN
1306: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1307: oe_debug_pub.add(' party_id = '||p_party_id);
1308: oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1309: END IF;
1310:
1311: IF p_cust_account_id is null then
1304:
1305: IF l_debug_level > 0 THEN
1306: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1307: oe_debug_pub.add(' party_id = '||p_party_id);
1308: oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1309: END IF;
1310:
1311: IF p_cust_account_id is null then
1312: x_return_status := FND_API.G_RET_STS_ERROR;
1310:
1311: IF p_cust_account_id is null then
1312: x_return_status := FND_API.G_RET_STS_ERROR;
1313: IF l_debug_level > 0 THEN
1314: oe_debug_pub.add( ' ERROR: cust account id must be sent to check sites' ) ;
1315: END IF;
1316:
1317: END IF;
1318:
1335: l_end_customer_passed :='N'; --added for bug 4240715
1336:
1337: x_return_status := FND_API.G_RET_STS_SUCCESS;
1338: IF l_debug_level > 0 THEN
1339: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1340: oe_debug_pub.add(' site table rec #'||i);
1341: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1342: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1343: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1336:
1337: x_return_status := FND_API.G_RET_STS_SUCCESS;
1338: IF l_debug_level > 0 THEN
1339: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1340: oe_debug_pub.add(' site table rec #'||i);
1341: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1342: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1343: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1344: END IF;
1337: x_return_status := FND_API.G_RET_STS_SUCCESS;
1338: IF l_debug_level > 0 THEN
1339: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1340: oe_debug_pub.add(' site table rec #'||i);
1341: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1342: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1343: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1344: END IF;
1345:
1338: IF l_debug_level > 0 THEN
1339: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1340: oe_debug_pub.add(' site table rec #'||i);
1341: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1342: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1343: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1344: END IF;
1345:
1346: if (p_site_tbl(i).p_process_site = FALSE) then
1339: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1340: oe_debug_pub.add(' site table rec #'||i);
1341: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1342: oe_debug_pub.add(' party_site_id = '|| p_site_tbl(i).p_party_site_id|| ' site_use_code = '||p_site_tbl(i).p_site_use_code);
1343: oe_debug_pub.add(' site_use_id ='||p_site_tbl(i).p_site_use_id ) ;
1344: END IF;
1345:
1346: if (p_site_tbl(i).p_process_site = FALSE) then
1347: IF (l_debug_level > 0) THEN
1344: END IF;
1345:
1346: if (p_site_tbl(i).p_process_site = FALSE) then
1347: IF (l_debug_level > 0) THEN
1348: oe_debug_pub.add( ' p_process_site is NULL, skipping site processing..');
1349: end if;
1350: goto skip_site;
1351: end if;
1352:
1364: end if;
1365:
1366:
1367: IF l_debug_level > 0 THEN
1368: oe_debug_pub.add( ' site level customer: party_id:'||px_party_id ) ;
1369: oe_debug_pub.add( ' site level customer: account_id:'||px_cust_account_id ) ;
1370: END IF;
1371:
1372:
1365:
1366:
1367: IF l_debug_level > 0 THEN
1368: oe_debug_pub.add( ' site level customer: party_id:'||px_party_id ) ;
1369: oe_debug_pub.add( ' site level customer: account_id:'||px_cust_account_id ) ;
1370: END IF;
1371:
1372:
1373: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1386: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1387: then
1388: --nothing to do! return!
1389: IF l_debug_level > 0 THEN
1390: oe_debug_pub.add ('AAC: create_sites: no data passed in, likely a contact search; returning');
1391: END IF;
1392: return;
1393: end if;
1394:
1397: l_site_use_code := p_site_tbl(i).p_site_use_code;
1398: ELSE
1399: l_return_status := FND_API.G_RET_STS_ERROR;
1400: IF l_debug_level > 0 THEN
1401: oe_debug_pub.add( 'site use not specified' ) ;
1402: END IF;
1403: FND_MESSAGE.Set_Name('ONT','ONT_AAC_ERROR');
1404:
1405: FND_MESSAGE.Set_Token('TEXT','Usage of Account Site should be specified', FALSE);
1418: -- If site_use_id is passed then we validate this for the account
1419: IF p_site_tbl(i).p_site_use_id IS NOT NULL THEN
1420:
1421: IF l_debug_level > 0 THEN
1422: oe_debug_pub.add( ' checking site_use_id' ) ;
1423: END IF;
1424:
1425: --{added for bug 4240715
1426: -- added for end customer /* check done based on the order SOLD_TO,SHIP_TO,BILL_TO,DELIVER_TO */
1433: l_primary_site_use,
1434: l_cust_account_id;
1435:
1436: if c_site_use%FOUND then
1437: oe_debug_pub.add('Checking for end customer of type SOLD_TO');
1438: l_site_use_code :='SOLD_TO';
1439: l_site_use_id := p_site_tbl(i).p_site_use_id;
1440: px_cust_account_id := p_cust_account_id;
1441: px_party_id := p_party_id;
1441: px_party_id := p_party_id;
1442: if l_status <> 'A' then
1443: l_return_status := fnd_api.g_ret_sts_error;
1444: IF l_debug_level > 0 THEN
1445: oe_debug_pub.add( 'account site use is inactive' ) ;
1446: END IF;
1447: end if;
1448: /* IF l_cust_account_id <> px_cust_account_id then
1449: x_return_status := FND_API.G_RET_STS_ERROR;
1448: /* IF l_cust_account_id <> px_cust_account_id then
1449: x_return_status := FND_API.G_RET_STS_ERROR;
1450: l_return_status := FND_API.G_RET_STS_ERROR;
1451: IF l_debug_level > 0 THEN
1452: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1453: END IF;
1454: End if; */
1455: goto end_customer_found;
1456: Else -- not sold to
1464: l_primary_site_use,
1465: l_cust_account_id;
1466:
1467: if c_site_use%FOUND then
1468: oe_debug_pub.add('Checking for end customer of type SHIP_TO');
1469: l_site_use_code :='SHIP_TO';
1470: l_site_use_id := p_site_tbl(i).p_site_use_id;
1471: px_cust_account_id := p_cust_account_id;
1472: px_party_id := p_party_id;
1472: px_party_id := p_party_id;
1473: if l_status <> 'A' then
1474: l_return_status := fnd_api.g_ret_sts_error;
1475: IF l_debug_level > 0 THEN
1476: oe_debug_pub.add( 'account site use is inactive' ) ;
1477: END IF;
1478: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1479: OE_MSG_PUB.ADD;
1480: end if;
1481: /* IF l_cust_account_id <> px_cust_account_id then
1482: x_return_status := FND_API.G_RET_STS_ERROR;
1483: l_return_status := FND_API.G_RET_STS_ERROR;
1484: IF l_debug_level > 0 THEN
1485: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1486: END IF;
1487: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1488: OE_MSG_PUB.ADD;
1489: End if; */
1499: l_primary_site_use,
1500: l_cust_account_id;
1501:
1502: if c_site_use%FOUND then
1503: oe_debug_pub.add('Checking for end customer of type BILL_TO');
1504: l_site_use_code :='BILL_TO';
1505: l_site_use_id := p_site_tbl(i).p_site_use_id;
1506: px_cust_account_id := p_cust_account_id;
1507: px_party_id := p_party_id;
1507: px_party_id := p_party_id;
1508: if l_status <> 'A' then
1509: l_return_status := fnd_api.g_ret_sts_error;
1510: IF l_debug_level > 0 THEN
1511: oe_debug_pub.add( 'account site use is inactive' ) ;
1512: END IF;
1513: FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
1514: OE_MSG_PUB.ADD;
1515: end if;
1516: /* IF l_cust_account_id <> px_cust_account_id then
1517: x_return_status := FND_API.G_RET_STS_ERROR;
1518: l_return_status := FND_API.G_RET_STS_ERROR;
1519: IF l_debug_level > 0 THEN
1520: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1521: END IF;
1522: FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_ACCOUNT');
1523: OE_MSG_PUB.ADD;
1524: End if;*/
1534: l_primary_site_use,
1535: l_cust_account_id;
1536:
1537: if c_site_use%FOUND then
1538: oe_debug_pub.add('Checking for end customer of type DELIVER_TO');
1539: l_site_use_code :='DELIVER_TO';
1540: l_site_use_id := p_site_tbl(i).p_site_use_id;
1541: px_cust_account_id := p_cust_account_id;
1542: px_party_id := p_party_id;
1542: px_party_id := p_party_id;
1543: if l_status <> 'A' then
1544: l_return_status := fnd_api.g_ret_sts_error;
1545: IF l_debug_level > 0 THEN
1546: oe_debug_pub.add( 'account site use is inactive' ) ;
1547: END IF;
1548: FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
1549: OE_MSG_PUB.ADD;
1550: end if;
1551: /* IF l_cust_account_id <> px_cust_account_id then
1552: x_return_status := FND_API.G_RET_STS_ERROR;
1553: l_return_status := FND_API.G_RET_STS_ERROR;
1554: IF l_debug_level > 0 THEN
1555: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1556: END IF;
1557: FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_ACCOUNT');
1558: OE_MSG_PUB.ADD;
1559: End if; */
1578:
1579: IF c_site_use%FOUND THEN
1580:
1581: IF l_debug_level > 0 THEN
1582: oe_debug_pub.add('found site_use_id acct_site_id = '||l_cust_acct_site_id|| ' status='||l_status);
1583: oe_debug_pub.add(' primary = '||l_primary_site_use|| ' cust_account_id = '||l_cust_account_id ) ;
1584: end if;
1585:
1586: if l_status <> 'A' then
1579: IF c_site_use%FOUND THEN
1580:
1581: IF l_debug_level > 0 THEN
1582: oe_debug_pub.add('found site_use_id acct_site_id = '||l_cust_acct_site_id|| ' status='||l_status);
1583: oe_debug_pub.add(' primary = '||l_primary_site_use|| ' cust_account_id = '||l_cust_account_id ) ;
1584: end if;
1585:
1586: if l_status <> 'A' then
1587: l_return_status := fnd_api.g_ret_sts_error;
1585:
1586: if l_status <> 'A' then
1587: l_return_status := fnd_api.g_ret_sts_error;
1588: IF l_debug_level > 0 THEN
1589: oe_debug_pub.add( 'account site use is inactive' ) ;
1590: END IF;
1591:
1592: IF l_site_use_code = 'SHIP_TO' then
1593: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1599:
1600: OE_MSG_PUB.ADD;
1601: END IF;
1602:
1603: oe_debug_pub.add( 'px_accoun_id:'||px_cust_account_id||'l_accoun_id:'||l_cust_account_id ) ;
1604: /* IF l_cust_account_id <> px_cust_account_id then
1605: x_return_status := FND_API.G_RET_STS_ERROR;
1606: l_return_status := FND_API.G_RET_STS_ERROR;
1607: IF l_debug_level > 0 THEN
1604: /* IF l_cust_account_id <> px_cust_account_id then
1605: x_return_status := FND_API.G_RET_STS_ERROR;
1606: l_return_status := FND_API.G_RET_STS_ERROR;
1607: IF l_debug_level > 0 THEN
1608: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1609: END IF;
1610:
1611: IF l_site_use_code = 'SHIP_TO' then
1612: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1625: ELSIF c_site_use%NOTFOUND THEN
1626: x_return_status := FND_API.G_RET_STS_ERROR;
1627: l_return_status := FND_API.G_RET_STS_ERROR;
1628: IF l_debug_level > 0 THEN
1629: oe_debug_pub.add( 'INVALID SITE_USE_ID ' ) ;
1630: END IF;
1631:
1632: IF l_site_use_code = 'SHIP_TO' then
1633: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_SHIPTO');
1644: END IF; -- site_ use_code (bug 4240715)
1645: ELSE -- if site_use_id is null
1646:
1647: IF l_debug_level > 0 THEN
1648: oe_debug_pub.add( ' site_use_id not passed' ) ;
1649: END IF;
1650: /*cc project, based on the g_fech_parimary_party_sites value the logic for check_and_create
1651: sites will execute. If it is true then we will ignore the addresses passed to us and instead
1652: fetch and primary party sites and related account sites. If primary party sites are found and no corresponding
1665:
1666: l_party_site_id_cc :=l_party_site_id_rec.party_site_id;
1667:
1668: IF l_debug_level > 0 THEN
1669: oe_debug_pub.add('cc Party site id :'|| l_party_site_id_cc);
1670: oe_debug_pub.add('searching whether party site usage is primary or not');
1671: END IF;
1672:
1673: OPEN c_prim_party_site_use(l_party_site_id_cc,p_site_tbl(i).p_site_use_code);
1666: l_party_site_id_cc :=l_party_site_id_rec.party_site_id;
1667:
1668: IF l_debug_level > 0 THEN
1669: oe_debug_pub.add('cc Party site id :'|| l_party_site_id_cc);
1670: oe_debug_pub.add('searching whether party site usage is primary or not');
1671: END IF;
1672:
1673: OPEN c_prim_party_site_use(l_party_site_id_cc,p_site_tbl(i).p_site_use_code);
1674: FETCH c_prim_party_site_use
1676:
1677:
1678: IF c_prim_party_site_use%NOTFOUND THEN
1679: IF l_debug_level > 0 THEN
1680: oe_debug_pub.add('no primary party site use of type :'||p_site_tbl(i).p_site_use_code||' of party_site_id:'||l_party_site_id_cc);
1681: END IF;
1682: ELSIF c_prim_party_site_use%FOUND THEN
1683: p_site_tbl(i).p_party_site_use_id :=l_party_site_use_id;
1684: l_party_site_id :=l_party_site_id_cc;
1682: ELSIF c_prim_party_site_use%FOUND THEN
1683: p_site_tbl(i).p_party_site_use_id :=l_party_site_use_id;
1684: l_party_site_id :=l_party_site_id_cc;
1685: IF l_debug_level > 0 THEN
1686: oe_debug_pub.add(' primary party site use of type :'||p_site_tbl(i).p_site_use_code||' of party_site_id:'||l_party_site_id_cc||' is found,party_site_use_id is'||l_party_site_use_id);
1687: oe_debug_pub.add('exiting from loop to search primary party site uses');
1688: END IF;
1689: Close c_prim_party_site_use;
1690: exit;
1683: p_site_tbl(i).p_party_site_use_id :=l_party_site_use_id;
1684: l_party_site_id :=l_party_site_id_cc;
1685: IF l_debug_level > 0 THEN
1686: oe_debug_pub.add(' primary party site use of type :'||p_site_tbl(i).p_site_use_code||' of party_site_id:'||l_party_site_id_cc||' is found,party_site_use_id is'||l_party_site_use_id);
1687: oe_debug_pub.add('exiting from loop to search primary party site uses');
1688: END IF;
1689: Close c_prim_party_site_use;
1690: exit;
1691:
1707: */
1708: -- if party_site_id is sent
1709: IF l_party_site_id is null AND
1710: x_return_status <> FND_API.G_RET_STS_ERROR then
1711: oe_debug_pub.add( 'l_party_site_id is null and p_site_tbl(i).p_party_site_id is '||p_site_tbl(i).p_party_site_id);
1712: IF p_site_tbl(i).p_party_site_id is not null then
1713: IF l_debug_level > 0 THEN
1714: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1715: END IF;
1710: x_return_status <> FND_API.G_RET_STS_ERROR then
1711: oe_debug_pub.add( 'l_party_site_id is null and p_site_tbl(i).p_party_site_id is '||p_site_tbl(i).p_party_site_id);
1712: IF p_site_tbl(i).p_party_site_id is not null then
1713: IF l_debug_level > 0 THEN
1714: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1715: END IF;
1716:
1717: l_party_site_id := p_site_tbl(i).p_party_site_id;
1718:
1724: IF c_party_site%NOTFOUND THEN
1725: l_return_status := FND_API.G_RET_STS_ERROR;
1726: x_return_status := FND_API.G_RET_STS_ERROR;
1727: IF l_debug_level > 0 THEN
1728: oe_debug_pub.add( 'no such party site id' ) ;
1729: END IF;
1730: CLOSE c_party_site;
1731:
1732: IF l_site_use_code = 'SHIP_TO' then
1750:
1751: ELSIF c_party_site%FOUND THEN
1752:
1753: IF l_debug_level > 0 THEN
1754: oe_debug_pub.add( 'found for party_site_id ' ) ;
1755: END IF;
1756:
1757: IF l_status <> 'A' THEN
1758:
1768: END IF;
1769:
1770: OE_MSG_PUB.ADD;
1771: IF l_debug_level > 0 THEN
1772: oe_debug_pub.add( 'party site is not active' ) ;
1773: END IF;
1774: CLOSE c_party_site;
1775: IF p_continue_on_error THEN
1776: null;
1783: END IF;
1784:
1785: END IF; -- if status is not active
1786:
1787: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1788:
1789: IF px_party_id <> l_party_id THEN
1790: x_return_status := FND_API.G_RET_STS_ERROR;
1791: l_return_status := FND_API.G_RET_STS_ERROR;
1799: END IF;
1800:
1801: OE_MSG_PUB.ADD;
1802: IF l_debug_level > 0 THEN
1803: oe_debug_pub.add( 'party site does not belong to the party' ) ;
1804: END IF;
1805:
1806: CLOSE c_party_site;
1807: IF p_continue_on_error THEN
1821:
1822: END IF; -- if party_site_id found
1823:
1824: ELSE -- if party_site_id is null
1825: oe_debug_pub.add( 'Yes.. Party_Site_id is not null');
1826: if l_end_customer_passed ='N' then -- if its not end customer(4240715)
1827: x_return_status := FND_API.G_RET_STS_ERROR;
1828: l_return_status := FND_API.G_RET_STS_ERROR;
1829: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1829: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1830: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
1831: OE_MSG_PUB.ADD;
1832: IF l_debug_level > 0 THEN
1833: oe_debug_pub.add( 'no site information is sent ' ) ;
1834: END IF;
1835: IF p_continue_on_error THEN
1836: null;
1837: ELSE
1844: end if; -- as long as its not end customer(4240715)
1845: END IF; -- if party_site_id not null
1846: END IF; -- if l_party_site_id is null
1847: ELSE
1848: oe_debug_pub.add('Have got the primary_party_site_use_id');
1849: END IF;
1850:
1851:
1852: ELSE --G_fetch_primary_party_sites =FALSE
1858: -- party_site_id and site_use_code should be passed
1859: IF p_site_tbl(i).p_party_site_use_id IS NOT NULL THEN
1860:
1861: IF l_debug_level > 0 THEN
1862: oe_debug_pub.add( 'checking for party_site_use_id ='|| P_SITE_TBL ( I ) .P_PARTY_SITE_USE_ID ) ;
1863: END IF;
1864: OPEN c_party_site_use(p_site_tbl(i).p_party_site_use_id);
1865: FETCH c_party_site_use
1866: INTO l_site_use_code,
1870: IF c_party_site_use%NOTFOUND THEN
1871: l_return_status := FND_API.G_RET_STS_ERROR;
1872: x_return_status := FND_API.G_RET_STS_ERROR;
1873: IF l_debug_level > 0 THEN
1874: oe_debug_pub.add( 'not a valid party site use id' ) ;
1875: END IF;
1876:
1877: IF l_site_use_code = 'SHIP_TO' then
1878: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_SHIPTO');
1898: p_site_tbl(i).p_party_site_id := l_party_site_id;
1899: END IF;
1900:
1901: IF l_debug_level > 0 THEN
1902: oe_debug_pub.add( ' site_use_code = '||L_SITE_USE_CODE ) ;
1903: END IF;
1904: -- site use code has to be passed in the control record
1905: -- because we can make a party site as a account site of bill,ship,deliver
1906: -- even if the party site use is STMNTS or others
1916: IF l_party_site_id is null AND
1917: x_return_status <> FND_API.G_RET_STS_ERROR then
1918: IF p_site_tbl(i).p_party_site_id is not null then
1919: IF l_debug_level > 0 THEN
1920: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1921: END IF;
1922: l_party_site_id := p_site_tbl(i).p_party_site_id;
1923:
1924: OPEN c_party_site(l_party_site_id);
1928: IF c_party_site%NOTFOUND THEN
1929: l_return_status := FND_API.G_RET_STS_ERROR;
1930: x_return_status := FND_API.G_RET_STS_ERROR;
1931: IF l_debug_level > 0 THEN
1932: oe_debug_pub.add( 'no such party site id' ) ;
1933: END IF;
1934: CLOSE c_party_site;
1935:
1936: IF l_site_use_code = 'SHIP_TO' then
1953: END IF;
1954: ELSIF c_party_site%FOUND THEN
1955:
1956: IF l_debug_level > 0 THEN
1957: oe_debug_pub.add( 'found for party_site_id ' ) ;
1958: END IF;
1959: IF l_status <> 'A' THEN
1960:
1961: x_return_status := FND_API.G_RET_STS_ERROR;
1970: END IF;
1971:
1972: OE_MSG_PUB.ADD;
1973: IF l_debug_level > 0 THEN
1974: oe_debug_pub.add( 'party site is not active' ) ;
1975: END IF;
1976: CLOSE c_party_site;
1977: IF p_continue_on_error THEN
1978: null;
1985: END IF;
1986:
1987: END IF; -- if status is not active
1988:
1989: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1990:
1991: IF px_party_id <> l_party_id THEN
1992: x_return_status := FND_API.G_RET_STS_ERROR;
1993: l_return_status := FND_API.G_RET_STS_ERROR;
2001: END IF;
2002:
2003: OE_MSG_PUB.ADD;
2004: IF l_debug_level > 0 THEN
2005: oe_debug_pub.add( 'party site does not belong to the party' ) ;
2006: END IF;
2007: CLOSE c_party_site;
2008: IF p_continue_on_error THEN
2009: null;
2022:
2023: END IF; -- if party_site_id found
2024:
2025: ELSE -- if party_site_id is null
2026: oe_debug_pub.add( 'Yes.. Party_Site_id is not null.. Second');
2027: if l_end_customer_passed ='N' then -- if its not end customer(bug 4240715)
2028: x_return_status := FND_API.G_RET_STS_ERROR;
2029: l_return_status := FND_API.G_RET_STS_ERROR;
2030: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
2030: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
2031: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
2032: OE_MSG_PUB.ADD;
2033: IF l_debug_level > 0 THEN
2034: oe_debug_pub.add( 'no site information is sent ' ) ;
2035: END IF;
2036: IF p_continue_on_error THEN
2037: null;
2038: ELSE
2046: END IF; -- if party_site_id not null
2047: END IF; -- if l_party_site_id is null
2048:
2049: IF l_debug_level > 0 THEN
2050: oe_debug_pub.add( ' l_party_site_id ='||l_party_site_id|| ' l_site_use_code ='||L_SITE_USE_CODE ) ;
2051: END IF;
2052:
2053:
2054: END IF; --G_fetch_primary_party_sites
2062: FETCH c_endcust_party_site_use
2063: INTO lx_party_site_use_id;
2064:
2065: if c_endcust_party_site_use%FOUND then
2066: oe_debug_pub.add('Checking for end customer of type SOLD_TO');
2067: l_site_use_code :='SOLD_TO';
2068: goto end_customer_site_use_found;
2069: Else -- not sold to
2070: close c_endcust_party_site_use;
2074: FETCH c_endcust_party_site_use
2075: INTO lx_party_site_use_id;
2076:
2077: if c_endcust_party_site_use%FOUND then
2078: oe_debug_pub.add('Checking for end customer of type SHIP_TO');
2079: l_site_use_code :='SHIP_TO';
2080: goto end_customer_site_use_found;
2081: Else -- not sold to
2082: close c_endcust_party_site_use;
2085: FETCH c_endcust_party_site_use
2086: INTO lx_party_site_use_id;
2087:
2088: if c_endcust_party_site_use%FOUND then
2089: oe_debug_pub.add('Checking for end customer of type BILL_TO');
2090: l_site_use_code :='BILL_TO';
2091: goto end_customer_site_use_found;
2092: Else -- not sold to
2093: close c_endcust_party_site_use;
2096: FETCH c_endcust_party_site_use
2097: INTO lx_party_site_use_id;
2098:
2099: if c_endcust_party_site_use%FOUND then
2100: oe_debug_pub.add('Checking for end customer of type DELIVER_TO');
2101: l_site_use_code :='DELIVER_TO';
2102: goto end_customer_site_use_found;
2103: Else -- not sold to
2104: close c_endcust_party_site_use;
2102: goto end_customer_site_use_found;
2103: Else -- not sold to
2104: close c_endcust_party_site_use;
2105: end if; -- sold to check;
2106: oe_debug_pub.add('Site use code selected for end customer is'||l_site_use_code);
2107: END IF;
2108: --------------------------------
2109:
2110: <
2113: IF x_return_status = FND_API.G_RET_STS_SUCCESS and l_site_use_code <>'END_CUST' THEN
2114:
2115:
2116: IF l_debug_level > 0 THEN
2117: oe_debug_pub.add( ' continuing processing' ) ;
2118: end if;
2119: -- fetch the account_site_id
2120: IF p_site_tbl(i).p_create_primary_acct_site_use then
2121: l_send_primary := 'Y';
2130: l_site_use_primary_flag;
2131: IF c_acct_site%FOUND THEN
2132:
2133: IF l_debug_level > 0 THEN
2134: oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2135: END IF;
2136: IF l_site_use_id IS NULL THEN
2137: IF l_debug_level > 0 THEN
2138: oe_debug_pub.add( 'make a call to create account site use' ) ;
2134: oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2135: END IF;
2136: IF l_site_use_id IS NULL THEN
2137: IF l_debug_level > 0 THEN
2138: oe_debug_pub.add( 'make a call to create account site use' ) ;
2139: END IF;
2140: -- make a call to create account site use
2141:
2142: IF p_allow_site_creation then
2180: );
2181: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2182: CLOSE c_acct_site;
2183: IF l_debug_level > 0 THEN
2184: oe_debug_pub.add( ' account site use creation failed' ) ;
2185: END IF;
2186: IF l_debug_level > 0 THEN
2187: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2188: END IF;
2183: IF l_debug_level > 0 THEN
2184: oe_debug_pub.add( ' account site use creation failed' ) ;
2185: END IF;
2186: IF l_debug_level > 0 THEN
2187: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2188: END IF;
2189:
2190: x_return_status := FND_API.G_RET_STS_ERROR;
2191: l_return_status := FND_API.G_RET_STS_ERROR;
2217: l_return_status := FND_API.G_RET_STS_ERROR;
2218: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2219: OE_MSG_PUB.ADD;
2220: IF l_debug_level > 0 THEN
2221: oe_debug_pub.add( ' not authorized to create site' ) ;
2222: END IF;
2223: CLOSE c_acct_site;
2224: IF p_continue_on_error THEN
2225: null;
2232: ELSIF c_acct_site%NOTFOUND THEN
2233:
2234: -- make a call to create acct site and account site use
2235: IF l_debug_level > 0 THEN
2236: oe_debug_pub.add( 'make a call to create acct site and use' ) ;
2237: END IF;
2238:
2239: IF p_allow_site_creation THEN
2240:
2238:
2239: IF p_allow_site_creation THEN
2240:
2241: IF l_debug_level > 0 THEN
2242: oe_debug_pub.add( ' creating account site' ) ;
2243: end IF;
2244: oe_oe_inline_address.Create_Account_Site
2245: (
2246: p_cust_account_id =>px_cust_account_id,
2275: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2276: CLOSE c_acct_site;
2277: l_return_status := FND_API.G_RET_STS_ERROR;
2278: IF l_debug_level > 0 THEN
2279: oe_debug_pub.add( ' account site creation failed' ) ;
2280: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2281: END IF;
2282: IF x_msg_count = 1 then
2283:
2276: CLOSE c_acct_site;
2277: l_return_status := FND_API.G_RET_STS_ERROR;
2278: IF l_debug_level > 0 THEN
2279: oe_debug_pub.add( ' account site creation failed' ) ;
2280: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2281: END IF;
2282: IF x_msg_count = 1 then
2283:
2284: IF l_site_use_code = 'SHIP_TO' then
2302:
2303: ELSE
2304:
2305: IF l_debug_level > 0 THEN
2306: oe_debug_pub.add( ' acct_site_id created = '||L_CUST_ACCT_SITE_ID ) ;
2307: END IF;
2308: oe_oe_inline_address.Create_Acct_Site_Uses
2309: (
2310: p_cust_acct_site_id =>l_cust_acct_site_id,
2347: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2348: l_return_status := FND_API.G_RET_STS_ERROR;
2349: CLOSE c_acct_site;
2350: IF l_debug_level > 0 THEN
2351: oe_debug_pub.add( ' account site use creation failed' ) ;
2352: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2353: END IF;
2354: IF x_msg_count = 1 then
2355: IF l_debug_level > 0 THEN
2348: l_return_status := FND_API.G_RET_STS_ERROR;
2349: CLOSE c_acct_site;
2350: IF l_debug_level > 0 THEN
2351: oe_debug_pub.add( ' account site use creation failed' ) ;
2352: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2353: END IF;
2354: IF x_msg_count = 1 then
2355: IF l_debug_level > 0 THEN
2356: oe_debug_pub.add( ' adding to message stack' ) ;
2352: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2353: END IF;
2354: IF x_msg_count = 1 then
2355: IF l_debug_level > 0 THEN
2356: oe_debug_pub.add( ' adding to message stack' ) ;
2357: END IF;
2358:
2359: IF l_site_use_code = 'SHIP_TO' then
2360: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2367: FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2368: OE_MSG_PUB.ADD;
2369: ELSE
2370: IF l_debug_level > 0 THEN
2371: oe_debug_pub.add( ' transferring to message stack' ) ;
2372: END IF;
2373: oe_msg_pub.transfer_msg_stack;
2374: END IF;
2375: IF p_continue_on_error THEN
2388: l_return_status := FND_API.G_RET_STS_ERROR;
2389: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2390: OE_MSG_PUB.ADD;
2391: IF l_debug_level > 0 THEN
2392: oe_debug_pub.add( '2 not allowed to create site' ) ;
2393: END IF;
2394: CLOSE c_acct_site;
2395: IF p_continue_on_error THEN
2396: null;
2408:
2409: END IF; -- if status is success
2410:
2411: IF l_debug_level > 0 THEN
2412: oe_debug_pub.add( ' assinging site table site_id = '||l_site_use_id|| ' acct site_id = '||L_CUST_ACCT_SITE_ID ) ;
2413: END IF;
2414:
2415:
2416: -- END IF; -- if site_use_id is passed
2424:
2425: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2426:
2427: IF l_debug_level > 0 THEN
2428: oe_debug_pub.add( ' continuing processing' ) ;
2429: end if;
2430: -- fetch the account_site_id
2431: IF p_site_tbl(i).p_create_primary_acct_site_use then
2432: l_send_primary := 'Y';
2441: l_site_use_primary_flag;
2442: IF c_acct_site%FOUND THEN
2443:
2444: IF l_debug_level > 0 THEN
2445: oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2446: END IF;
2447: IF l_site_use_id IS NULL THEN
2448: IF l_debug_level > 0 THEN
2449: oe_debug_pub.add( 'make a call to create account site use' ) ;
2445: oe_debug_pub.add( ' account site found = '||L_SITE_USE_ID ) ;
2446: END IF;
2447: IF l_site_use_id IS NULL THEN
2448: IF l_debug_level > 0 THEN
2449: oe_debug_pub.add( 'make a call to create account site use' ) ;
2450: END IF;
2451: -- make a call to create account site use
2452:
2453: IF p_allow_site_creation then
2491: );
2492: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2493: CLOSE c_acct_site;
2494: IF l_debug_level > 0 THEN
2495: oe_debug_pub.add( ' account site use creation failed' ) ;
2496: END IF;
2497: IF l_debug_level > 0 THEN
2498: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2499: END IF;
2494: IF l_debug_level > 0 THEN
2495: oe_debug_pub.add( ' account site use creation failed' ) ;
2496: END IF;
2497: IF l_debug_level > 0 THEN
2498: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2499: END IF;
2500:
2501: x_return_status := FND_API.G_RET_STS_ERROR;
2502: l_return_status := FND_API.G_RET_STS_ERROR;
2528: l_return_status := FND_API.G_RET_STS_ERROR;
2529: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2530: OE_MSG_PUB.ADD;
2531: IF l_debug_level > 0 THEN
2532: oe_debug_pub.add( ' not authorized to create site' ) ;
2533: END IF;
2534: CLOSE c_acct_site;
2535: IF p_continue_on_error THEN
2536: null;
2543: ELSIF c_acct_site%NOTFOUND THEN
2544:
2545: -- make a call to create acct site and account site use
2546: IF l_debug_level > 0 THEN
2547: oe_debug_pub.add( 'make a call to create acct site and use' ) ;
2548: END IF;
2549:
2550: IF p_allow_site_creation THEN
2551:
2549:
2550: IF p_allow_site_creation THEN
2551:
2552: IF l_debug_level > 0 THEN
2553: oe_debug_pub.add( ' creating account site' ) ;
2554: end IF;
2555: oe_oe_inline_address.Create_Account_Site
2556: (
2557: p_cust_account_id =>px_cust_account_id,
2586: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2587: CLOSE c_acct_site;
2588: l_return_status := FND_API.G_RET_STS_ERROR;
2589: IF l_debug_level > 0 THEN
2590: oe_debug_pub.add( ' account site creation failed' ) ;
2591: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2592: END IF;
2593: IF x_msg_count = 1 then
2594:
2587: CLOSE c_acct_site;
2588: l_return_status := FND_API.G_RET_STS_ERROR;
2589: IF l_debug_level > 0 THEN
2590: oe_debug_pub.add( ' account site creation failed' ) ;
2591: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2592: END IF;
2593: IF x_msg_count = 1 then
2594:
2595: IF l_site_use_code = 'SHIP_TO' then
2613:
2614: ELSE
2615:
2616: IF l_debug_level > 0 THEN
2617: oe_debug_pub.add( ' acct_site_id created = '||L_CUST_ACCT_SITE_ID ) ;
2618: END IF;
2619: oe_oe_inline_address.Create_Acct_Site_Uses
2620: (
2621: p_cust_acct_site_id =>l_cust_acct_site_id,
2658: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2659: l_return_status := FND_API.G_RET_STS_ERROR;
2660: CLOSE c_acct_site;
2661: IF l_debug_level > 0 THEN
2662: oe_debug_pub.add( ' account site use creation failed' ) ;
2663: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2664: END IF;
2665: IF x_msg_count = 1 then
2666: IF l_debug_level > 0 THEN
2659: l_return_status := FND_API.G_RET_STS_ERROR;
2660: CLOSE c_acct_site;
2661: IF l_debug_level > 0 THEN
2662: oe_debug_pub.add( ' account site use creation failed' ) ;
2663: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2664: END IF;
2665: IF x_msg_count = 1 then
2666: IF l_debug_level > 0 THEN
2667: oe_debug_pub.add( ' adding to message stack' ) ;
2663: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2664: END IF;
2665: IF x_msg_count = 1 then
2666: IF l_debug_level > 0 THEN
2667: oe_debug_pub.add( ' adding to message stack' ) ;
2668: END IF;
2669:
2670: IF l_site_use_code = 'SHIP_TO' then
2671: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_SITE_CREATION');
2678: FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
2679: OE_MSG_PUB.ADD;
2680: ELSE
2681: IF l_debug_level > 0 THEN
2682: oe_debug_pub.add( ' transferring to message stack' ) ;
2683: END IF;
2684: oe_msg_pub.transfer_msg_stack;
2685: END IF;
2686: IF p_continue_on_error THEN
2699: l_return_status := FND_API.G_RET_STS_ERROR;
2700: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2701: OE_MSG_PUB.ADD;
2702: IF l_debug_level > 0 THEN
2703: oe_debug_pub.add( '2 not allowed to create site' ) ;
2704: END IF;
2705: CLOSE c_acct_site;
2706: IF p_continue_on_error THEN
2707: null;
2719:
2720: END IF; -- if status is success
2721:
2722: IF l_debug_level > 0 THEN
2723: oe_debug_pub.add( ' assinging site table site_id = '||l_site_use_id|| ' acct site_id = '||L_CUST_ACCT_SITE_ID ) ;
2724: END IF;
2725: END IF; -- if site_use_id is passed
2726:
2727: <
2729:
2730:
2731:
2732: IF l_debug_level > 0 THEN
2733: oe_debug_pub.add( ' site not success so copying null' ) ;
2734: END IF;
2735: p_site_tbl(i).p_site_use_id := null;
2736: p_site_tbl(i).p_cust_acct_site_id := null;
2737: ELSE
2742:
2743: IF C_get_cust_from_site_use_id%FOUND THEN
2744:
2745: IF l_debug_level > 0 THEN
2746: oe_debug_pub.add( ' account site found = '||p_site_tbl(i).p_cust_account_id ) ;
2747: END IF;
2748: END IF;
2749: close C_get_cust_from_site_use_id;
2750:
2748: END IF;
2749: close C_get_cust_from_site_use_id;
2750:
2751: IF l_debug_level > 0 THEN
2752: oe_debug_pub.add( ' site success so copying actual' ) ;
2753: END IF;
2754:
2755: p_site_tbl(i).p_site_use_id := l_site_use_id;
2756: p_site_tbl(i).p_cust_acct_site_id := l_cust_acct_site_id;
2766: x_return_status := l_return_status;
2767:
2768: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2769: IF l_debug_level > 0 THEN
2770: oe_debug_pub.add( ' doing count and get for site' ) ;
2771: END IF;
2772: OE_MSG_PUB.Count_And_Get
2773: ( p_count => x_msg_count
2774: , p_data => x_msg_data
2774: , p_data => x_msg_data
2775: );
2776: END IF;
2777: IF l_debug_level > 0 THEN
2778: oe_debug_pub.add( ' at end of site msg = '||x_msg_data|| ' count = '||x_MSG_COUNT ) ;
2779: END IF;
2780:
2781: EXCEPTION
2782:
2834: CLOSE c_acct_site;
2835: END IF;
2836: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2837: IF l_debug_level > 0 THEN
2838: oe_debug_pub.add( 'check_and_create_site when other code = '|| sqlcode||' message = '||sqlerrm ) ;
2839: END IF;
2840:
2841: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2842: THEN
2865: ,out_cust_account_role_id out NOCOPY number
2866: ) IS
2867:
2868: --
2869: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
2870: --
2871: BEGIN
2872:
2873: null;
2974: px_party_id number := null;
2975: px_cust_account_id number := null;
2976:
2977: --
2978: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
2979: l_indent varchar2(5) := ' ';
2980: --
2981: BEGIN
2982: l_debug_level:=1;
2980: --
2981: BEGIN
2982: l_debug_level:=1;
2983: IF l_debug_level > 0 THEN
2984: oe_debug_pub.add( 'entering check_and_create_contact ');
2985: oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2986: oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2987: oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2988: p_cust_account_site_id ) ;
2981: BEGIN
2982: l_debug_level:=1;
2983: IF l_debug_level > 0 THEN
2984: oe_debug_pub.add( 'entering check_and_create_contact ');
2985: oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2986: oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2987: oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2988: p_cust_account_site_id ) ;
2989: END IF;
2982: l_debug_level:=1;
2983: IF l_debug_level > 0 THEN
2984: oe_debug_pub.add( 'entering check_and_create_contact ');
2985: oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2986: oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2987: oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2988: p_cust_account_site_id ) ;
2989: END IF;
2990:
2983: IF l_debug_level > 0 THEN
2984: oe_debug_pub.add( 'entering check_and_create_contact ');
2985: oe_debug_pub.add(l_indent||' party_id = '||p_party_id||' cust_id = '||p_cust_account_id);
2986: oe_debug_pub.add(l_indent||' org_contact_id ='||p_org_contact_id|| ' p_site_use_code = '||p_site_use_code);
2987: oe_debug_pub.add(l_indent||' p_acct_role_id ='|| p_cust_account_role_id|| ' p_acct_account_site_id = '||
2988: p_cust_account_site_id ) ;
2989: END IF;
2990:
2991: IF l_debug_level > 0 THEN
2989: END IF;
2990:
2991: IF l_debug_level > 0 THEN
2992: if p_multiple_contact_is_error then
2993: oe_debug_pub.add(l_indent|| 'multiple contact is error' ) ;
2994: else
2995: oe_debug_pub.add( l_indent|| 'multiple contact is not error' ) ;
2996: end if;
2997:
2991: IF l_debug_level > 0 THEN
2992: if p_multiple_contact_is_error then
2993: oe_debug_pub.add(l_indent|| 'multiple contact is error' ) ;
2994: else
2995: oe_debug_pub.add( l_indent|| 'multiple contact is not error' ) ;
2996: end if;
2997:
2998: if p_assign_contact_to_site then
2999: oe_debug_pub.add( l_indent|| 'assign_contact_to_site ' ) ;
2995: oe_debug_pub.add( l_indent|| 'multiple contact is not error' ) ;
2996: end if;
2997:
2998: if p_assign_contact_to_site then
2999: oe_debug_pub.add( l_indent|| 'assign_contact_to_site ' ) ;
3000: else
3001: oe_debug_pub.add(l_indent|| 'not assign_contact_to_site ' ) ;
3002: end if;
3003:
2997:
2998: if p_assign_contact_to_site then
2999: oe_debug_pub.add( l_indent|| 'assign_contact_to_site ' ) ;
3000: else
3001: oe_debug_pub.add(l_indent|| 'not assign_contact_to_site ' ) ;
3002: end if;
3003:
3004: if p_create_responsibility then
3005: oe_debug_pub.add(l_indent|| 'create responsibility ' ) ;
3001: oe_debug_pub.add(l_indent|| 'not assign_contact_to_site ' ) ;
3002: end if;
3003:
3004: if p_create_responsibility then
3005: oe_debug_pub.add(l_indent|| 'create responsibility ' ) ;
3006: else
3007: oe_debug_pub.add(l_indent|| 'not create responsibility ' ) ;
3008: end if;
3009:
3003:
3004: if p_create_responsibility then
3005: oe_debug_pub.add(l_indent|| 'create responsibility ' ) ;
3006: else
3007: oe_debug_pub.add(l_indent|| 'not create responsibility ' ) ;
3008: end if;
3009:
3010: if p_allow_contact_creation then
3011: oe_debug_pub.add( l_indent|| 'allow_contact_creation ' ) ;
3007: oe_debug_pub.add(l_indent|| 'not create responsibility ' ) ;
3008: end if;
3009:
3010: if p_allow_contact_creation then
3011: oe_debug_pub.add( l_indent|| 'allow_contact_creation ' ) ;
3012: else
3013: oe_debug_pub.add( l_indent|| 'not allow_contact_creation ' ) ;
3014: END IF;
3015: END IF;
3009:
3010: if p_allow_contact_creation then
3011: oe_debug_pub.add( l_indent|| 'allow_contact_creation ' ) ;
3012: else
3013: oe_debug_pub.add( l_indent|| 'not allow_contact_creation ' ) ;
3014: END IF;
3015: END IF;
3016:
3017:
3024: IF p_cust_account_id is null AND
3025: p_cust_account_role_id is null then
3026: x_return_status := FND_API.G_RET_STS_ERROR;
3027: IF l_debug_level > 0 THEN
3028: oe_debug_pub.add(l_indent|| ' not a valid call to check contact' ) ;
3029: END IF;
3030: return;
3031:
3032: END IF;
3035: IF p_cust_account_id is null AND
3036: p_cust_account_role_id is not null then
3037:
3038: IF l_debug_level > 0 THEN
3039: oe_debug_pub.add( l_indent||' getting account from contact information' ) ;
3040: end IF;
3041: OPEN c_get_cust_id(p_cust_account_role_id);
3042: FETCH c_get_cust_id
3043: INTO l_cust_account_id;
3044:
3045: IF c_get_cust_id%NOTFOUND THEN
3046: x_return_status := FND_API.G_RET_STS_ERROR;
3047: IF l_debug_level > 0 THEN
3048: oe_debug_pub.add( l_indent|| 'not a valid cust account role id' ) ;
3049: END IF;
3050: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3051: OE_MSG_PUB.ADD;
3052:
3068: l_cust_account_id := p_cust_account_id;
3069: END IF;
3070:
3071: IF l_debug_level > 0 THEN
3072: oe_debug_pub.add( l_indent|| 'cust_account_id = '||L_CUST_ACCOUNT_ID ) ;
3073: END IF;
3074:
3075: p_contact_tbl.DELETE;
3076: IF l_cust_account_id is not null then
3080: -- if we need to assign the contact to a site, then first we check
3081: -- if it is already attached to that site
3082: IF p_assign_contact_to_site and p_cust_account_site_id is not null then
3083: IF l_debug_level > 0 THEN
3084: oe_debug_pub.add(l_indent|| 'need to assign contact to site' ) ;
3085: END IF;
3086: OPEN c_cust_role_site( p_cust_account_role_id);
3087: FETCH c_cust_role_site
3088: INTO l_status,
3092:
3093:
3094: IF c_cust_role_site%NOTFOUND THEN
3095: IF l_debug_level > 0 THEN
3096: oe_debug_pub.add( l_indent|| 'site contact not found' ) ;
3097: END IF;
3098: CLOSE c_cust_role_site;
3099:
3100: IF p_create_responsibility then
3099:
3100: IF p_create_responsibility then
3101: --create acct contact and attach site and pass the responsibility
3102: IF l_debug_level > 0 THEN
3103: oe_debug_pub.add( l_indent|| ' 1 not supported currently' ) ;
3104: end IF;
3105: ELSE
3106: --create acct contact and attach site and DO NOT pass responsibility
3107: IF l_debug_level > 0 THEN
3104: end IF;
3105: ELSE
3106: --create acct contact and attach site and DO NOT pass responsibility
3107: IF l_debug_level > 0 THEN
3108: oe_debug_pub.add( l_indent|| ' 2 not supported currently' ) ;
3109: END IF;
3110: END IF;
3111:
3112: ELSIF c_cust_role_site%FOUND THEN
3111:
3112: ELSIF c_cust_role_site%FOUND THEN
3113:
3114: IF l_debug_level > 0 THEN
3115: oe_debug_pub.add(l_indent|| 'site contact found' ) ;
3116: END IF;
3117: IF l_status <> 'A' then
3118: IF l_debug_level > 0 THEN
3119: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3115: oe_debug_pub.add(l_indent|| 'site contact found' ) ;
3116: END IF;
3117: IF l_status <> 'A' then
3118: IF l_debug_level > 0 THEN
3119: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3120: END IF;
3121: CLOSE c_cust_role_site;
3122: x_return_status := FND_API.G_RET_STS_ERROR;
3123: return;
3125: END IF;
3126:
3127: IF l_role_type <> 'CONTACT' then
3128: IF l_debug_level > 0 THEN
3129: oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3130: END IF;
3131: CLOSE c_cust_role_site;
3132: x_return_status := FND_API.G_RET_STS_ERROR;
3133: return;
3135: END IF;
3136:
3137: IF l_cust_account_id <> p_cust_account_id then
3138: IF l_debug_level > 0 THEN
3139: oe_debug_pub.add(l_indent|| 'acct role does not belong to this acct' ) ;
3140: END IF;
3141: CLOSE c_cust_role_site;
3142: x_return_status := FND_API.G_RET_STS_ERROR;
3143: return;
3145:
3146:
3147: IF l_cust_role_site_id <> p_cust_account_site_id then
3148: IF l_debug_level > 0 THEN
3149: oe_debug_pub.add( l_indent|| 'Cust account role for site is not valid' ) ;
3150: END IF;
3151: CLOSE c_cust_role_site;
3152: x_return_status := FND_API.G_RET_STS_ERROR;
3153: return;
3155: END IF;
3156:
3157:
3158: IF l_debug_level > 0 THEN
3159: oe_debug_pub.add(l_indent|| 'found the contact attached to account site' ) ;
3160: END IF;
3161: CLOSE c_cust_role_site;
3162: p_contact_tbl(1) :=p_cust_account_role_id;
3163: return;
3179: CLOSE c_cust_account_role;
3180:
3181: x_return_status := FND_API.G_RET_STS_ERROR;
3182: IF l_debug_level > 0 THEN
3183: oe_debug_pub.add(l_indent|| '2 contact_id not found' ) ;
3184: END IF;
3185: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3186: OE_MSG_PUB.ADD;
3187: OE_MSG_PUB.Count_And_Get
3193: ELSIF c_cust_account_role%FOUND then
3194:
3195: IF l_status <> 'A' then
3196: IF l_debug_level > 0 THEN
3197: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3198: END IF;
3199: CLOSE c_cust_account_role;
3200: x_return_status := FND_API.G_RET_STS_ERROR;
3201: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INACTIVE_CONTACT');
3209: END IF;
3210:
3211: IF l_role_type <> 'CONTACT' then
3212: IF l_debug_level > 0 THEN
3213: oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3214: END IF;
3215: CLOSE c_cust_account_role;
3216: x_return_status := FND_API.G_RET_STS_ERROR;
3217: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ROLE_CONTACT');
3226:
3227:
3228: IF l_cust_account_id <> p_cust_account_id then
3229: IF l_debug_level > 0 THEN
3230: oe_debug_pub.add( l_indent|| 'Account role does not belong to this acct' ) ;
3231: END IF;
3232: CLOSE c_cust_account_role;
3233: x_return_status := FND_API.G_RET_STS_ERROR;
3234: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_ACCOUNT');
3242:
3243: IF p_create_responsibility then
3244: --check if resp exists. if not then create one
3245: IF l_debug_level > 0 THEN
3246: oe_debug_pub.add( l_indent|| ' 1 not supported currently' ) ;
3247: end if;
3248: END IF;
3249:
3250: IF l_debug_level > 0 THEN
3247: end if;
3248: END IF;
3249:
3250: IF l_debug_level > 0 THEN
3251: oe_debug_pub.add( l_indent|| 'found contact attached to account returning' ) ;
3252: END IF;
3253: CLOSE c_cust_account_role;
3254: p_contact_tbl(1) :=p_cust_account_role_id;
3255: return;
3268: INTO l_rel_party_id;
3269:
3270: IF c_check_org_contact%NOTFOUND then
3271: IF l_debug_level > 0 THEN
3272: oe_debug_pub.add(l_indent|| 'org contact_id does not belong to sent party or '|| ' contact may be inactive' ) ;
3273: END IF;
3274: x_return_status := FND_API.G_RET_STS_ERROR;
3275: CLOSE c_check_org_contact;
3276: FND_MESSAGE.Set_Name('ONT','ONT_AAC_CONTACT_ACCOUNT');
3279: ( p_count => x_msg_count
3280: , p_data => x_msg_data
3281: );
3282: IF l_debug_level > 0 THEN
3283: oe_debug_pub.add(l_indent|| ' contact error msg = '||x_msg_data|| '; count = '||x_MSG_COUNT ) ;
3284: END IF;
3285:
3286: return;
3287: END IF;
3287: END IF;
3288: CLOSE c_check_org_contact;
3289:
3290: IF l_debug_level > 0 THEN
3291: oe_debug_pub.add(l_indent|| 'checking for value of org_contact_id' ) ;
3292: END IF;
3293: -- derive customer account
3294: OPEN c_org_contact(p_org_Contact_id,
3295: l_cust_account_id );
3293: -- derive customer account
3294: OPEN c_org_contact(p_org_Contact_id,
3295: l_cust_account_id );
3296: LOOP
3297: --oe_debug_pub.add('Inside get_cust_role_id loop');
3298: FETCH c_org_contact
3299: INTO l_cust_account_role_id,
3300: l_cust_acct_site_id;
3301: EXIT WHEN C_org_contact%NOTFOUND;
3300: l_cust_acct_site_id;
3301: EXIT WHEN C_org_contact%NOTFOUND;
3302:
3303: IF l_debug_level > 0 THEN
3304: oe_debug_pub.add(l_indent|| 'acct_role_id = '||l_cust_account_role_id|| ' cust_acct_site_id = '||L_CUST_ACCT_SITE_ID ) ;
3305: END IF;
3306:
3307: p_contact_tbl(p_contact_tbl.COUNT + 1):= l_cust_account_role_id;
3308:
3320: IF p_multiple_contact_is_error THEN
3321:
3322: IF p_contact_tbl.COUNT > 1 then
3323: IF l_debug_level > 0 THEN
3324: oe_debug_pub.add(l_indent|| 'error >1 contact for org_contact_id' ) ;
3325: END IF;
3326: x_return_status := FND_API.G_RET_STS_ERROR;
3327: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3328: FND_MESSAGE.Set_Token('TEXT',' Multiple Contacts ', FALSE);
3337:
3338:
3339: IF p_contact_tbl.COUNT = 1 then
3340: IF l_debug_level > 0 THEN
3341: oe_debug_pub.add( l_indent|| 'RETURNING WITH ONE CONTACT' ) ;
3342: END IF;
3343: x_return_status := FND_API.G_RET_STS_SUCCESS;
3344: return;
3345: ELSIF p_contact_tbl.COUNT = 0 then
3343: x_return_status := FND_API.G_RET_STS_SUCCESS;
3344: return;
3345: ELSIF p_contact_tbl.COUNT = 0 then
3346: IF l_debug_level > 0 THEN
3347: oe_debug_pub.add( l_indent|| 'no contact for account found. creating '|| ' acct_id = '||p_cust_account_id|| ' rel_party_id = '||L_REL_PARTY_ID ) ;
3348: END IF;
3349:
3350: IF p_allow_contact_creation THEN
3351:
3412: x_return_status := l_return_status;
3413: x_msg_data := l_msg_data;
3414: x_msg_count := l_msg_count;
3415: IF l_debug_level > 0 THEN
3416: oe_debug_pub.add(l_indent|| ' contact creation failed' ) ;
3417: oe_debug_pub.add( l_indent|| ' error = '||X_MSG_DATA ) ;
3418: END IF;
3419:
3420: IF x_msg_count = 1 then
3413: x_msg_data := l_msg_data;
3414: x_msg_count := l_msg_count;
3415: IF l_debug_level > 0 THEN
3416: oe_debug_pub.add(l_indent|| ' contact creation failed' ) ;
3417: oe_debug_pub.add( l_indent|| ' error = '||X_MSG_DATA ) ;
3418: END IF;
3419:
3420: IF x_msg_count = 1 then
3421: FND_MESSAGE.Set_Name('ONT','ONT_AAC_CONTACT_CREATION');
3432: return;
3433: ELSE
3434: x_return_status := FND_API.G_RET_STS_SUCCESS;
3435: IF l_debug_level > 0 THEN
3436: oe_debug_pub.add( l_indent|| ' contact creation succeeded '|| ' cust acct_role_id = '||P_CONTACT_TBL ( 1 ) ) ;
3437: END IF;
3438:
3439: END IF;
3440: ELSE
3439: END IF;
3440: ELSE
3441: x_return_status := FND_API.G_RET_STS_ERROR;
3442: IF l_debug_level > 0 THEN
3443: oe_debug_pub.add( l_indent|| 'not allowed to create contact' ) ;
3444: end IF;
3445: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_PERMISSION');
3446: OE_MSG_PUB.ADD;
3447: OE_MSG_PUB.Count_And_Get
3462: -- if zero then create the account contact appropiately
3463: -- take care of account site and responsibility
3464: IF p_contact_tbl.COUNT = 0 then
3465: IF l_debug_level > 0 THEN
3466: oe_debug_pub.add( l_indent|| 'equal to ZERO' ) ;
3467: END IF;
3468: x_return_status := FND_API.G_RET_STS_SUCCESS;
3469: return;
3470:
3472: -- take care of account site and responsibility
3473: -- and create if not present
3474: ELSIF p_Contact_tbl.COUNT >0 then
3475: IF l_debug_level > 0 THEN
3476: oe_debug_pub.add( l_indent|| 'one or more than one' ) ;
3477: END IF;
3478: x_return_status := FND_API.G_RET_STS_SUCCESS;
3479: return;
3480: END IF;
3481:
3482: END IF; -- if not to be assigned
3483:
3484: IF l_debug_level > 0 THEN
3485: oe_debug_pub.add(l_indent|| 'cust_acct_role_id = '||L_CUST_ACCOUNT_ROLE_ID ) ;
3486: END IF;
3487:
3488: ELSE
3489: IF l_debug_level > 0 THEN
3486: END IF;
3487:
3488: ELSE
3489: IF l_debug_level > 0 THEN
3490: oe_debug_pub.add(l_indent|| 'error no contact information is passed' ) ;
3491: END IF;
3492: return;
3493: END IF; -- if p_cust_account_role_id is not null
3494:
3493: END IF; -- if p_cust_account_role_id is not null
3494:
3495: ELSE -- if cust_account_id is not passed
3496: IF l_debug_level > 0 THEN
3497: oe_debug_pub.add(l_indent|| 'this procedure expects the account_id to be passed' ) ;
3498: END IF;
3499: return;
3500:
3501: END IF; -- if cust_account_id is not null
3528: WHEN OTHERS THEN
3529:
3530: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3531: IF l_debug_level > 0 THEN
3532: oe_debug_pub.add( 'CHECK_CONTACT WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
3533: END IF;
3534:
3535: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3536: THEN
3603: x_cust_account_number varchar2(30);
3604: l_found boolean := FALSE;
3605:
3606: --
3607: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
3608: l_indent varchar2(5) := ' ';
3609: i number := 0;
3610:
3611: --
3614: x_return_status := FND_API.G_RET_STS_SUCCESS;
3615: p_account_tbl.DELETE;
3616:
3617: IF l_debug_level > 0 THEN
3618: oe_debug_pub.add(l_indent||'party_id = '||p_party_id|| '; party_number = '||p_party_number);
3619: oe_debug_pub.add(l_indent||'site tbl count = '||p_site_tbl_count ) ;
3620: END IF;
3621:
3622: if p_allow_account_creation then
3615: p_account_tbl.DELETE;
3616:
3617: IF l_debug_level > 0 THEN
3618: oe_debug_pub.add(l_indent||'party_id = '||p_party_id|| '; party_number = '||p_party_number);
3619: oe_debug_pub.add(l_indent||'site tbl count = '||p_site_tbl_count ) ;
3620: END IF;
3621:
3622: if p_allow_account_creation then
3623: IF l_debug_level > 0 THEN
3620: END IF;
3621:
3622: if p_allow_account_creation then
3623: IF l_debug_level > 0 THEN
3624: oe_debug_pub.add( l_indent|| ' allowed account creation' ) ;
3625: END IF;
3626: end if;
3627:
3628: if p_multiple_account_is_error then
3626: end if;
3627:
3628: if p_multiple_account_is_error then
3629: IF l_debug_level > 0 THEN
3630: oe_debug_pub.add(l_indent|| ' multiple account is error' ) ;
3631: END IF;
3632: end if;
3633:
3634: if p_return_if_only_party then
3632: end if;
3633:
3634: if p_return_if_only_party then
3635: IF l_debug_level > 0 THEN
3636: oe_debug_pub.add(l_indent|| ' return_if_only_party' ) ;
3637: END IF;
3638: end if;
3639:
3640:
3651:
3652: IF party_number_rec%NOTFOUND THEN
3653: x_return_status := FND_API.G_RET_STS_ERROR;
3654: IF l_debug_level > 0 THEN
3655: oe_debug_pub.add(l_indent|| 'no such party found for party_number' ) ;
3656: END IF;
3657: CLOSE party_number_rec;
3658: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_PARTY');
3659: OE_MSG_PUB.ADD;
3669:
3670: IF party_rec%NOTFOUND THEN
3671: x_return_status := FND_API.G_RET_STS_ERROR;
3672: IF l_debug_level > 0 THEN
3673: oe_debug_pub.add(l_indent|| 'no such party found for party_id' ) ;
3674: END IF;
3675: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_PARTY');
3676: OE_MSG_PUB.ADD;
3677: CLOSE party_rec;
3682:
3683: END IF;
3684:
3685: IF l_debug_level > 0 THEN
3686: oe_debug_pub.add(l_indent|| 'party type for sold_to = '|| L_PARTY_TYPE ) ;
3687: END IF;
3688: IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
3689:
3690: IF l_debug_level > 0 THEN
3687: END IF;
3688: IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
3689:
3690: IF l_debug_level > 0 THEN
3691: oe_debug_pub.add(l_indent|| 'party type = '||l_party_type|| '; party_id = '||l_PARTY_ID ) ;
3692: END IF;
3693: -- derive customer account
3694: OPEN C_get_cust_id_from_party_id(l_Party_Id);
3695: LOOP
3694: OPEN C_get_cust_id_from_party_id(l_Party_Id);
3695: LOOP
3696: IF l_debug_level > 0 THEN
3697: i := i+1;
3698: oe_debug_pub.add ('AAC:VTI: ===GET_CUST_LOOP_'||i||'===== ');
3699: oe_debug_pub.add(l_indent|| 'inside get_cust_id loop :X#'||i) ;
3700: END IF;
3701:
3702: FETCH C_get_cust_id_from_party_id
3695: LOOP
3696: IF l_debug_level > 0 THEN
3697: i := i+1;
3698: oe_debug_pub.add ('AAC:VTI: ===GET_CUST_LOOP_'||i||'===== ');
3699: oe_debug_pub.add(l_indent|| 'inside get_cust_id loop :X#'||i) ;
3700: END IF;
3701:
3702: FETCH C_get_cust_id_from_party_id
3703: INTO l_cust_account_id,
3704: l_account_number;
3705: EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
3706:
3707: IF l_debug_level > 0 THEN
3708: oe_debug_pub.add( l_indent|| 'acct_id = '||l_cust_account_id|| '; account number = '||l_ACCOUNT_NUMBER ) ;
3709: END IF;
3710:
3711: IF l_debug_level > 0 THEN
3712: oe_debug_pub.add( l_indent|| 'row count get_cust_id = '|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
3708: oe_debug_pub.add( l_indent|| 'acct_id = '||l_cust_account_id|| '; account number = '||l_ACCOUNT_NUMBER ) ;
3709: END IF;
3710:
3711: IF l_debug_level > 0 THEN
3712: oe_debug_pub.add( l_indent|| 'row count get_cust_id = '|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
3713: END IF;
3714:
3715: IF p_multiple_account_is_error THEN
3716: IF C_get_cust_id_from_party_id%ROWCOUNT > 1 then
3714:
3715: IF p_multiple_account_is_error THEN
3716: IF C_get_cust_id_from_party_id%ROWCOUNT > 1 then
3717: IF l_debug_level > 0 THEN
3718: oe_debug_pub.add( l_indent|| 'more than one account for party id' ) ;
3719: END IF;
3720: x_return_status := FND_API.G_RET_STS_ERROR;
3721: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3722: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
3724: EXIT;
3725: END IF;
3726: end if;
3727: IF l_debug_level > 0 THEN
3728: oe_debug_pub.add(l_indent|| 'adding to account tbl id = '||L_CUST_ACCOUNT_ID ) ;
3729: END IF;
3730: p_account_tbl(p_account_tbl.COUNT + 1):= l_cust_account_id;
3731:
3732:
3735: -- if multiple accounts are detected then do not proceed further as
3736: -- contacts and sites needs to be created once an account is selected
3737: IF p_account_tbl.COUNT > 1 then
3738: IF l_debug_level > 0 THEN
3739: oe_debug_pub.add( l_indent|| 'multiple accounts found' ) ;
3740: end IF;
3741: p_multiple_account := TRUE;
3742: END IF;
3743:
3743:
3744: CLOSE C_get_cust_id_from_party_id;
3745:
3746: IF l_debug_level > 0 THEN
3747: oe_debug_pub.add( l_indent||'cust acct id for sold_to = '|| L_CUST_ACCOUNT_ID ) ;
3748: END IF;
3749:
3750:
3751: ELSE -- if not person or organization
3751: ELSE -- if not person or organization
3752:
3753: x_return_status := FND_API.G_RET_STS_ERROR;
3754: IF l_debug_level > 0 THEN
3755: oe_debug_pub.add(l_indent|| ' invalid party type' ) ;
3756: end IF;
3757: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_PARTY');
3758: OE_MSG_PUB.ADD;
3759:
3762: IF p_account_tbl.COUNT > 0 then
3763: FOR i in p_account_tbl.FIRST..p_account_tbl.LAST
3764: LOOP
3765: IF l_debug_level > 0 THEN
3766: oe_debug_pub.add( l_indent|| 'acct_id = '||P_ACCOUNT_TBL ( I ) ) ;
3767: END IF;
3768: END LOOP;
3769: END IF;
3770:
3768: END LOOP;
3769: END IF;
3770:
3771: IF l_debug_level > 0 THEN
3772: oe_debug_pub.add(l_indent|| 'before checking to create cust account' ) ;
3773: END IF;
3774: -- create customer account
3775: IF x_return_status <> FND_API.G_RET_STS_ERROR AND
3776: NOT p_multiple_account AND
3784: -- as we will not have account information
3785: -- Even if party level contact is passed then
3786: -- user should select it in the Add Customer form
3787: IF l_debug_level > 0 THEN
3788: oe_debug_pub.add( l_indent|| ' going to call add customer' ) ;
3789: END IF;
3790: x_return_status := FND_API.G_RET_STS_SUCCESS;
3791: return;
3792:
3792:
3793: ELSE
3794: IF l_party_id is not NULL THEN
3795: IF l_debug_level > 0 THEN
3796: oe_debug_pub.add( l_indent|| 'creating cust account...' ) ;
3797: END IF;
3798:
3799: oe_oe_inline_address.create_account(
3800: p_party_number=>null,
3862: in_Created_by_module=>G_CREATED_BY_MODULE
3863: );
3864:
3865: IF l_debug_level > 0 THEN
3866: oe_debug_pub.add( l_indent|| ' create account status='||x_RETURN_STATUS|| '; x_party_id = '|| X_PARTY_ID);
3867: oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3868: oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3869: END IF;
3870:
3863: );
3864:
3865: IF l_debug_level > 0 THEN
3866: oe_debug_pub.add( l_indent|| ' create account status='||x_RETURN_STATUS|| '; x_party_id = '|| X_PARTY_ID);
3867: oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3868: oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3869: END IF;
3870:
3871: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3864:
3865: IF l_debug_level > 0 THEN
3866: oe_debug_pub.add( l_indent|| ' create account status='||x_RETURN_STATUS|| '; x_party_id = '|| X_PARTY_ID);
3867: oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3868: oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3869: END IF;
3870:
3871: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3872: IF l_debug_level > 0 THEN
3869: END IF;
3870:
3871: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3872: IF l_debug_level > 0 THEN
3873: oe_debug_pub.add( l_indent|| ' account creation failed' ) ;
3874: END IF;
3875: IF x_msg_count = 1 then
3876: FND_MESSAGE.Set_Name('ONT','ONT_AAC_ACCOUNT_CREATION');
3877: FND_MESSAGE.Set_Token('TCA_MESSAGE',x_msg_data, FALSE);
3884: p_account_tbl(1):= x_cust_account_id;
3885: /*cc project assigning the value CREATED TO variable G_account_created_or_found. We need
3886: to do the site creation process in case account is created and not search for the sites*/
3887: IF l_debug_level > 0 THEN
3888: oe_debug_pub.add('cc account created');
3889: END IF;
3890:
3891: G_account_created_or_found :='CREATED';
3892:
3902:
3903: IF G_CREATED_BY_MODULE <> 'ONT_TELESERVICE_INTEGRATION' THEN
3904: x_return_status := FND_API.G_RET_STS_ERROR;
3905: IF l_debug_level > 0 THEN
3906: oe_debug_pub.add(l_indent|| 'not allowed to create account' ) ;
3907: END IF;
3908: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
3909: OE_MSG_PUB.ADD;
3910: ELSE
3908: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
3909: OE_MSG_PUB.ADD;
3910: ELSE
3911: IF l_debug_level > 0 THEN
3912: oe_debug_pub.add('Contact Center Integration, no permission to create account');
3913: END IF;
3914: END IF;
3915: END IF; -- end profile condition
3916:
3915: END IF; -- end profile condition
3916:
3917: ELSE
3918: IF l_debug_level > 0 THEN
3919: oe_debug_pub.add(l_indent|| ' account does not need to be created' ) ;
3920: END IF;
3921: END IF; -- checking to see if acconts needs to be created
3922:
3923: --oe_debug_pub.add('p_out_cust_account_id = '|| p_account_tbl(1));
3919: oe_debug_pub.add(l_indent|| ' account does not need to be created' ) ;
3920: END IF;
3921: END IF; -- checking to see if acconts needs to be created
3922:
3923: --oe_debug_pub.add('p_out_cust_account_id = '|| p_account_tbl(1));
3924:
3925: ELSE
3926: --x_return_status := FND_API.G_RET_STS_ERROR;
3927: FND_MESSAGE.Set_Name('ONT', 'ONT_AVAIL_GENERIC');
3925: ELSE
3926: --x_return_status := FND_API.G_RET_STS_ERROR;
3927: FND_MESSAGE.Set_Name('ONT', 'ONT_AVAIL_GENERIC');
3928: IF l_debug_level > 0 THEN
3929: oe_debug_pub.add(l_indent|| 'party_id or party number is not passed' ) ;
3930: END IF;
3931: l_message := 'Party_id or Party Number is not passed';
3932: FND_MESSAGE.Set_Token('TEXT',l_message, FALSE);
3933: OE_MSG_PUB.ADD;
3935: END IF; -- if party_id or party_number is not null
3936:
3937:
3938: IF l_debug_level > 0 THEN
3939: oe_debug_pub.add(l_indent|| 'create account at the end' ) ;
3940: END IF;
3941: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3942: IF l_debug_level > 0 THEN
3943: oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3939: oe_debug_pub.add(l_indent|| 'create account at the end' ) ;
3940: END IF;
3941: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3942: IF l_debug_level > 0 THEN
3943: oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3944: END IF;
3945: oe_msg_pub.count_and_get(p_encoded=>fnd_api.G_TRUE,
3946: p_count => x_msg_count,
3947: p_data=>x_msg_data
3946: p_count => x_msg_count,
3947: p_data=>x_msg_data
3948: );
3949: IF l_debug_level > 0 THEN
3950: oe_debug_pub.add( l_indent|| 'count = '||x_msg_count||'; msg = '||X_MSG_DATA ) ;
3951: END IF;
3952: END IF;
3953:
3954: IF l_debug_level > 0 THEN
3951: END IF;
3952: END IF;
3953:
3954: IF l_debug_level > 0 THEN
3955: oe_debug_pub.add( l_indent|| 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
3956: oe_debug_pub.add( l_indent|| 'exiting check_and_create_account: ' || X_RETURN_STATUS ) ;
3957: END IF;
3958:
3959: EXCEPTION
3952: END IF;
3953:
3954: IF l_debug_level > 0 THEN
3955: oe_debug_pub.add( l_indent|| 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
3956: oe_debug_pub.add( l_indent|| 'exiting check_and_create_account: ' || X_RETURN_STATUS ) ;
3957: END IF;
3958:
3959: EXCEPTION
3960:
4015: END IF;
4016:
4017: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4018: IF l_debug_level > 0 THEN
4019: oe_debug_pub.add( 'CHECK_ACCOUNT WHEN OTHERS EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
4020: END IF;
4021:
4022: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4023: THEN
4039:
4040: l_file_val varchar2(2000);
4041:
4042: --
4043: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4044: --
4045: BEGIN
4046:
4047: oe_debug_pub.debug_on;
4043: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4044: --
4045: BEGIN
4046:
4047: oe_debug_pub.debug_on;
4048: oe_debug_pub.initialize;
4049: l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
4050:
4051: END set_debug_on;
4044: --
4045: BEGIN
4046:
4047: oe_debug_pub.debug_on;
4048: oe_debug_pub.initialize;
4049: l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
4050:
4051: END set_debug_on;
4052:
4045: BEGIN
4046:
4047: oe_debug_pub.debug_on;
4048: oe_debug_pub.initialize;
4049: l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
4050:
4051: END set_debug_on;
4052:
4053:
4083: l_cust_account_id number;
4084: l_account_number varchar2(30);
4085:
4086: --
4087: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4088: --
4089: BEGIN
4090:
4091: x_return_status := FND_API.G_RET_STS_SUCCESS;
4091: x_return_status := FND_API.G_RET_STS_SUCCESS;
4092: p_account_tbl.DELETE;
4093:
4094: IF l_debug_level > 0 THEN
4095: oe_debug_pub.add( ' PARTY_ID='||P_PARTY_ID|| ' PARTY_NUMBER ='||P_PARTY_NUMBER ) ;
4096: END IF;
4097: -- if both party_id and party_number information is not provided then
4098: -- then we raise an error
4099: IF p_party_id is not null or p_party_number is not null then
4106: l_party_type;
4107:
4108: IF party_number_rec%NOTFOUND THEN
4109: IF l_debug_level > 0 THEN
4110: oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_NUMBER' ) ;
4111: END IF;
4112: x_return_status := FND_API.G_RET_STS_ERROR;
4113: FND_MESSAGE.Set_Name('ONT','ONT_AACC_NO_ACCOUNT');
4114: OE_MSG_PUB.ADD;
4124: INTO l_party_type;
4125:
4126: IF party_rec%NOTFOUND THEN
4127: IF l_debug_level > 0 THEN
4128: oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_ID' ) ;
4129: END IF;
4130: x_return_status := FND_API.G_RET_STS_ERROR;
4131: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
4132: OE_MSG_PUB.ADD;
4138:
4139: END IF; -- if party_number is not null
4140:
4141: IF l_debug_level > 0 THEN
4142: oe_debug_pub.add( 'PARTY TYPE FOR SOLD_TO = '|| L_PARTY_TYPE ) ;
4143: END IF;
4144: IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
4145:
4146: IF l_debug_level > 0 THEN
4143: END IF;
4144: IF l_party_type = 'PERSON' OR l_party_type ='ORGANIZATION' THEN
4145:
4146: IF l_debug_level > 0 THEN
4147: oe_debug_pub.add( 'PARTY TYPE='||L_PARTY_TYPE|| ' PARTY_ID='||L_PARTY_ID ) ;
4148: END IF;
4149: -- derive customer account
4150: OPEN C_get_cust_id_from_party_id(l_Party_Id);
4151: LOOP
4149: -- derive customer account
4150: OPEN C_get_cust_id_from_party_id(l_Party_Id);
4151: LOOP
4152: IF l_debug_level > 0 THEN
4153: oe_debug_pub.add( 'INSIDE GET_CUST_ID LOOP' ) ;
4154: END IF;
4155: FETCH C_get_cust_id_from_party_id
4156: INTO l_cust_account_id,
4157: l_account_number;
4157: l_account_number;
4158: EXIT WHEN C_get_cust_id_from_party_id%NOTFOUND;
4159:
4160: IF l_debug_level > 0 THEN
4161: oe_debug_pub.add( 'ACCT_ID='||L_CUST_ACCOUNT_ID|| ' ACCOUNT NUMBER='||L_ACCOUNT_NUMBER ) ;
4162: END IF;
4163:
4164: IF l_debug_level > 0 THEN
4165: oe_debug_pub.add( 'ROW COUNT GET_CUST_ID='|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
4161: oe_debug_pub.add( 'ACCT_ID='||L_CUST_ACCOUNT_ID|| ' ACCOUNT NUMBER='||L_ACCOUNT_NUMBER ) ;
4162: END IF;
4163:
4164: IF l_debug_level > 0 THEN
4165: oe_debug_pub.add( 'ROW COUNT GET_CUST_ID='|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
4166: END IF;
4167:
4168: oe_debug_pub.add('X1:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4169:
4164: IF l_debug_level > 0 THEN
4165: oe_debug_pub.add( 'ROW COUNT GET_CUST_ID='|| C_GET_CUST_ID_FROM_PARTY_ID%ROWCOUNT ) ;
4166: END IF;
4167:
4168: oe_debug_pub.add('X1:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4169:
4170: IF l_debug_level > 0 THEN
4171: oe_debug_pub.add( 'ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4172: END IF;
4167:
4168: oe_debug_pub.add('X1:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4169:
4170: IF l_debug_level > 0 THEN
4171: oe_debug_pub.add( 'ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4172: END IF;
4173:
4174: oe_debug_pub.add('X2:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4175:
4170: IF l_debug_level > 0 THEN
4171: oe_debug_pub.add( 'ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4172: END IF;
4173:
4174: oe_debug_pub.add('X2:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4175:
4176: p_account_tbl(p_account_tbl.COUNT + 1):= l_cust_account_id;
4177:
4178: oe_debug_pub.add('X3:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4174: oe_debug_pub.add('X2:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4175:
4176: p_account_tbl(p_account_tbl.COUNT + 1):= l_cust_account_id;
4177:
4178: oe_debug_pub.add('X3:ADDING TO ACCOUNT TBL ID='||L_CUST_ACCOUNT_ID ) ;
4179:
4180: END LOOP;
4181: ELSE
4182: IF l_debug_level > 0 THEN
4179:
4180: END LOOP;
4181: ELSE
4182: IF l_debug_level > 0 THEN
4183: oe_debug_pub.add( 'INVALID PARTY TYPE' ) ;
4184: END IF;
4185: x_return_status := FND_API.G_RET_STS_ERROR;
4186: FND_MESSAGE.Set_Name('ONT','ONT_AAC_INVALID_PARTY');
4187: OE_MSG_PUB.ADD;
4189: END IF;
4190:
4191: ELSE
4192: IF l_debug_level > 0 THEN
4193: oe_debug_pub.add( 'NO PARTY INFORMATION SENT' ) ;
4194: END IF;
4195: x_return_status := FND_API.G_RET_STS_ERROR;
4196: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4197: FND_MESSAGE.Set_Token('TEXT','No Customer Information ', FALSE);
4217: ,x_msg_count OUT NOCOPY NUMBER
4218: ,x_msg_data OUT NOCOPY VARCHAR2)
4219: IS
4220: -- local variables here
4221: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4222: l_dummy1 number := null;
4223: l_dummy2 number := null;
4224: l_dummy3 number := null;
4225: l_dummy4 boolean := false;
4228:
4229: x_return_status := FND_API.G_RET_STS_SUCCESS;
4230:
4231: IF l_debug_level > 0 THEN
4232: oe_debug_pub.add ('AAC:VTI: starting Value-To-Id');
4233: oe_debug_pub.add ('AAC:VTI: looking for header level stuff');
4234: END IF;
4235:
4236:
4229: x_return_status := FND_API.G_RET_STS_SUCCESS;
4230:
4231: IF l_debug_level > 0 THEN
4232: oe_debug_pub.add ('AAC:VTI: starting Value-To-Id');
4233: oe_debug_pub.add ('AAC:VTI: looking for header level stuff');
4234: END IF;
4235:
4236:
4237: -- if both party_id and account_id are missing, call find_sold_to_id
4258: IF x_return_status = FND_API.G_RET_STS_ERROR then
4259: -- not found a party_id/cust_account_id
4260: -- error message already logged inside find_sold_to_id
4261: IF l_debug_level > 0 THEN
4262: oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for header');
4263: END IF;
4264: -- exit if account_id cannot be found
4265: return;
4266: end if; -- x_return_status
4267:
4268: end if;
4269:
4270: IF l_debug_level > 0 THEN
4271: oe_debug_pub.add ('AAC:VTI: looking for sold_to contact');
4272: END IF;
4273:
4274: -- if both org_contact_id and account_role_id are missing, call find_contact_id
4275: if (nvl(p_party_customer_rec.p_org_contact_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4294: for i in p_site_tbl.FIRST..p_site_tbl.LAST loop
4295: -- do value-to-id for each site record
4296:
4297: IF l_debug_level > 0 THEN
4298: oe_debug_pub.add ('AAC:VTI: ============ VTI SITE RECORD '||i||' of '||p_site_tbl.LAST||' =============== ');
4299: oe_debug_pub.add ('AAC:VTI: looking for site party_id line record #'||i);
4300: END IF;
4301:
4302:
4295: -- do value-to-id for each site record
4296:
4297: IF l_debug_level > 0 THEN
4298: oe_debug_pub.add ('AAC:VTI: ============ VTI SITE RECORD '||i||' of '||p_site_tbl.LAST||' =============== ');
4299: oe_debug_pub.add ('AAC:VTI: looking for site party_id line record #'||i);
4300: END IF;
4301:
4302:
4303: -- do party v-t-i
4319: IF x_return_status = FND_API.G_RET_STS_ERROR then
4320: -- not found a party_id/cust_account_id
4321: -- error message already logged inside find_sold_to_id
4322: IF l_debug_level > 0 THEN
4323: oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for line#'||i);
4324: END IF;
4325: -- exit if account_id cannot be found
4326: return;
4327: end if; -- x_return_status
4326: return;
4327: end if; -- x_return_status
4328:
4329: IF l_debug_level > 0 THEN
4330: oe_debug_pub.add ('AAC:VTI: looking for site contact');
4331: END IF;
4332:
4333: oe_debug_pub.add('P1:'||p_site_tbl(i).p_site_use_id);
4334: -- do contact v-t-i
4329: IF l_debug_level > 0 THEN
4330: oe_debug_pub.add ('AAC:VTI: looking for site contact');
4331: END IF;
4332:
4333: oe_debug_pub.add('P1:'||p_site_tbl(i).p_site_use_id);
4334: -- do contact v-t-i
4335: find_contact_id(
4336: p_contact_id => p_site_tbl(i).p_org_contact_id
4337: ,p_cust_contact_id => p_site_tbl(i).p_cust_account_role_id
4345: );
4346:
4347: x_return_status := FND_API.G_RET_STS_SUCCESS;
4348: -- do site v-t-i
4349: oe_debug_pub.add('P2:'||p_site_tbl(i).p_site_use_id);
4350: find_site_id(
4351: p_site_use_id => p_site_tbl(i).p_party_site_use_id
4352: ,p_site_id => p_site_tbl(i).p_party_site_id
4353: ,p_account_site_use_id => p_site_tbl(i).p_site_use_id
4369: ,x_return_status => x_return_status
4370: ,x_msg_count => x_msg_count
4371: ,x_msg_data => x_msg_data
4372: );
4373: oe_debug_pub.add('P3:'||p_site_tbl(i).p_site_use_id);
4374: IF x_return_status = FND_API.G_RET_STS_ERROR then
4375: -- not found a party_id/cust_account_id
4376: -- error message already logged inside find_sold_to_id
4377: IF l_debug_level > 0 THEN
4374: IF x_return_status = FND_API.G_RET_STS_ERROR then
4375: -- not found a party_id/cust_account_id
4376: -- error message already logged inside find_sold_to_id
4377: IF l_debug_level > 0 THEN
4378: oe_debug_pub.add ('AAC:VTI:site_use_id account/party not found for line#'||1);
4379: END IF;
4380: -- exit if account_id cannot be found
4381: return;
4382: end if; -- x_return_status
4384: end loop;
4385: end if;
4386:
4387: IF l_debug_level > 0 THEN
4388: oe_debug_pub.add ('AAC:VTI: ending Value-To-Id');
4389: END IF;
4390:
4391: END value_to_id;
4392:
4411: --into p_cust_account_id -- commented for bug 3449269
4412: from hz_cust_accounts
4413: where party_id=l_party_id
4414: and status='A';
4415: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4416:
4417: BEGIN
4418:
4419: x_return_status := FND_API.G_RET_STS_SUCCESS;
4418:
4419: x_return_status := FND_API.G_RET_STS_SUCCESS;
4420:
4421: IF l_debug_level > 0 THEN
4422: oe_debug_pub.add ('AAC:VTI: FSTI: starting find_sold_to_id');
4423: END IF;
4424:
4425: -- check if party_id and cust_account_id are not null, return if so
4426: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4426: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4427: and nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4428: THEN
4429: IF l_debug_level > 0 THEN
4430: oe_debug_pub.add ('AAC:FSTI: party_id and account_id are not null, nothing to do: returning');
4431: END IF;
4432: RETURN;
4433: ELSE
4434: IF l_debug_level > 0 THEN
4431: END IF;
4432: RETURN;
4433: ELSE
4434: IF l_debug_level > 0 THEN
4435: oe_debug_pub.add ('AAC:FSTI: party_name: '||p_party_name||'; cust_account_number: '||p_cust_account_number);
4436: oe_debug_pub.add ('AAC:FSTI: party_id: '||p_party_id||'; cust_account_id: '||p_cust_account_id);
4437: END IF;
4438: END IF;
4439:
4432: RETURN;
4433: ELSE
4434: IF l_debug_level > 0 THEN
4435: oe_debug_pub.add ('AAC:FSTI: party_name: '||p_party_name||'; cust_account_number: '||p_cust_account_number);
4436: oe_debug_pub.add ('AAC:FSTI: party_id: '||p_party_id||'; cust_account_id: '||p_cust_account_id);
4437: END IF;
4438: END IF;
4439:
4440: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4447: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4448: then
4449: --nothing to do! return!
4450: IF l_debug_level > 0 THEN
4451: oe_debug_pub.add ('AAC:FSTI: no data passed in, seting process_site to false, returning');
4452: END IF;
4453: p_process_site:=false;
4454: return;
4455: end if;
4453: p_process_site:=false;
4454: return;
4455: end if;
4456:
4457: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4458: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4459: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4460: oe_debug_pub.add('p_party_name :'||p_party_name );
4461: oe_debug_pub.add('p_party_number :'||p_party_number );
4454: return;
4455: end if;
4456:
4457: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4458: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4459: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4460: oe_debug_pub.add('p_party_name :'||p_party_name );
4461: oe_debug_pub.add('p_party_number :'||p_party_number );
4462: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4455: end if;
4456:
4457: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4458: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4459: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4460: oe_debug_pub.add('p_party_name :'||p_party_name );
4461: oe_debug_pub.add('p_party_number :'||p_party_number );
4462: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4463: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4456:
4457: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4458: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4459: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4460: oe_debug_pub.add('p_party_name :'||p_party_name );
4461: oe_debug_pub.add('p_party_number :'||p_party_number );
4462: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4463: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4464: oe_debug_pub.add('p_party_id :'||p_party_id);
4457: oe_debug_pub.add('p_site_use_id :'||p_site_use_id);
4458: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4459: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4460: oe_debug_pub.add('p_party_name :'||p_party_name );
4461: oe_debug_pub.add('p_party_number :'||p_party_number );
4462: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4463: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4464: oe_debug_pub.add('p_party_id :'||p_party_id);
4465:
4458: oe_debug_pub.add('p_party_site_id :'||p_party_site_id );
4459: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4460: oe_debug_pub.add('p_party_name :'||p_party_name );
4461: oe_debug_pub.add('p_party_number :'||p_party_number );
4462: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4463: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4464: oe_debug_pub.add('p_party_id :'||p_party_id);
4465:
4466: begin
4459: oe_debug_pub.add('p_party_site_use_id :'||p_party_site_use_id );
4460: oe_debug_pub.add('p_party_name :'||p_party_name );
4461: oe_debug_pub.add('p_party_number :'||p_party_number );
4462: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4463: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4464: oe_debug_pub.add('p_party_id :'||p_party_id);
4465:
4466: begin
4467: -- check if site_use_id is passed
4460: oe_debug_pub.add('p_party_name :'||p_party_name );
4461: oe_debug_pub.add('p_party_number :'||p_party_number );
4462: oe_debug_pub.add('p_cust_account_number :'||p_cust_account_number );
4463: oe_debug_pub.add('p_cust_account_id :'||p_cust_account_id );
4464: oe_debug_pub.add('p_party_id :'||p_party_id);
4465:
4466: begin
4467: -- check if site_use_id is passed
4468: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4468: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4469: nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM ) then
4470:
4471: IF l_debug_level > 0 THEN
4472: oe_debug_pub.add ('AAC:FSTI: site_use_id:'||p_site_use_id||' is not null, cust_account_id is null: getting cust_account_id');
4473: END IF;
4474:
4475: select s.cust_account_id
4476: into p_cust_account_id
4485: if nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM and
4486: nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
4487:
4488: IF l_debug_level > 0 THEN
4489: oe_debug_pub.add ('AAC:FSTI: cust_account_id:'||p_cust_account_id||' is not null, party_id is null: getting party_id');
4490: END IF;
4491:
4492: select party_id
4493: into p_party_id
4507: OE_MSG_PUB.ADD;
4508: END IF;
4509:
4510: IF l_debug_level > 4 THEN
4511: oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4512: END IF;
4513:
4514: x_return_status := FND_API.G_RET_STS_ERROR;
4515: return;
4523: ,'find_sold_to_id'
4524: );
4525: --END IF;
4526: IF l_debug_level > 4 THEN
4527: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4528: END IF;
4529: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4530:
4531: end;
4545:
4546: else
4547: -- found a sold_to_org_id/cust_account_id
4548: IF l_debug_level > 0 THEN
4549: oe_debug_pub.add ('AAC:FSTI: found sold_to_org_id/cust_account_id:'||p_cust_account_id);
4550: END IF;
4551:
4552: -- also find the party_id for this account_id
4553: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4557: from hz_cust_accounts
4558: where cust_account_id = p_cust_account_id;
4559:
4560: IF l_debug_level > 0 THEN
4561: oe_debug_pub.add ('AAC:FSTI: also found party_id:'||p_party_id||', returning');
4562: END IF;
4563: EXCEPTION
4564:
4565: WHEN NO_DATA_FOUND THEN
4570: OE_MSG_PUB.ADD;
4571: END IF;
4572:
4573: IF l_debug_level > 4 THEN
4574: oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4575: END IF;
4576:
4577: x_return_status := FND_API.G_RET_STS_ERROR;
4578: return;
4586: ,'find_sold_to_id'
4587: );
4588: --END IF;
4589: IF l_debug_level > 4 THEN
4590: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4591: END IF;
4592: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4593:
4594: end;
4599: if (nvl(p_party_number,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4600: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4601: then
4602: IF l_debug_level > 0 THEN
4603: oe_debug_pub.add ('AAC:FSTI: party_number:'|| p_party_number||' is not null, picking up party_id from it');
4604: END IF;
4605:
4606: begin
4607: select party_id
4619: OE_MSG_PUB.ADD;
4620: END IF;
4621:
4622: IF l_debug_level > 4 THEN
4623: oe_debug_pub.add ('AAC:VTI: invalid party_number ');
4624: END IF;
4625:
4626: x_return_status := FND_API.G_RET_STS_ERROR;
4627: p_party_id := null;
4633: --THEN
4634: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4635: --END IF;
4636: IF l_debug_level > 4 THEN
4637: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4638: END IF;
4639: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4640:
4641: end;
4645: if (nvl(p_party_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4646: and nvl(p_party_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4647: then
4648: IF l_debug_level > 0 THEN
4649: oe_debug_pub.add ('AAC:FSTI: party_site_use_id:'|| p_party_site_use_id||' is not null, picking up party_site_id from it');
4650: END IF;
4651: begin
4652: select party_site_id
4653: into p_party_site_id
4664: OE_MSG_PUB.ADD;
4665: END IF;
4666:
4667: IF l_debug_level > 4 THEN
4668: oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
4669: END IF;
4670:
4671: x_return_status := FND_API.G_RET_STS_ERROR;
4672: p_party_site_id := null;
4678: --THEN
4679: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4680: --END IF;
4681: IF l_debug_level > 4 THEN
4682: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4683: END IF;
4684: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4685:
4686: end;
4689: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4690: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4691: then
4692: IF l_debug_level > 0 THEN
4693: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up party_id from it');
4694: END IF;
4695: begin
4696: select party_id
4697: into p_party_id
4708: OE_MSG_PUB.ADD;
4709: END IF;
4710:
4711: IF l_debug_level > 4 THEN
4712: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
4713: END IF;
4714:
4715: x_return_status := FND_API.G_RET_STS_ERROR;
4716: return;
4721: --THEN
4722: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_sold_to_id' ); --modified for bug 4590205
4723: --END IF;
4724: IF l_debug_level > 4 THEN
4725: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4726: END IF;
4727: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4728:
4729: end;
4733: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4734: and nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4735: then
4736: IF l_debug_level > 0 THEN
4737: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust site_use_id from it');
4738: END IF;
4739: begin
4740: select u.site_use_id
4741: into p_site_use_id
4747:
4748: WHEN NO_DATA_FOUND THEN
4749:
4750: IF l_debug_level > 4 THEN
4751: oe_debug_pub.add ('AAC:VTI: party_site_id: no site_use_id ');
4752: END IF;
4753: p_site_use_id := null;
4754:
4755: WHEN TOO_MANY_ROWS THEN
4753: p_site_use_id := null;
4754:
4755: WHEN TOO_MANY_ROWS THEN
4756: IF l_debug_level > 4 THEN
4757: oe_debug_pub.add ('AAC:VTI: party_site_id: multiple sites found ');
4758: END IF;
4759: p_site_use_id := null;
4760:
4761: WHEN OTHERS THEN
4764: --THEN
4765: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4766: --END IF;
4767: IF l_debug_level > 4 THEN
4768: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4769: END IF;
4770: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4771:
4772: end;
4776: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4777: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4778: then
4779: IF l_debug_level > 0 THEN
4780: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust_account_id from it via account_sites');
4781: END IF;
4782: begin
4783: select distinct cust_account_id
4784: into p_cust_account_id
4789:
4790: WHEN NO_DATA_FOUND THEN
4791:
4792: IF l_debug_level > 4 THEN
4793: oe_debug_pub.add ('AAC:VTI: party_site_id: no cust_account_id via site_use_id');
4794: END IF;
4795: p_cust_account_id := null;
4796:
4797: WHEN TOO_MANY_ROWS THEN
4795: p_cust_account_id := null;
4796:
4797: WHEN TOO_MANY_ROWS THEN
4798: IF l_debug_level > 4 THEN
4799: oe_debug_pub.add ('AAC:VTI: party_site_id: multiple accounts ');
4800: END IF;
4801:
4802: p_cust_account_id := null;
4803: x_return_status := FND_API.G_RET_STS_ERROR;
4814: --THEN
4815: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4816: --END IF;
4817: IF l_debug_level > 4 THEN
4818: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4819: END IF;
4820: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4821:
4822: end;
4827: if (nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4828: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4829: then
4830: IF l_debug_level > 0 THEN
4831: oe_debug_pub.add ('AAC:FSTI: site_id:'|| p_site_id||' is not null, picking up cust_account_id from it');
4832: END IF;
4833: begin
4834: select distinct cust_account_id
4835: into p_cust_account_id
4840:
4841: WHEN NO_DATA_FOUND THEN
4842:
4843: IF l_debug_level > 4 THEN
4844: oe_debug_pub.add ('AAC:VTI: site_id: no cust_account_id via site_id');
4845: END IF;
4846: p_cust_account_id := null;
4847:
4848: WHEN TOO_MANY_ROWS THEN
4846: p_cust_account_id := null;
4847:
4848: WHEN TOO_MANY_ROWS THEN
4849: IF l_debug_level > 4 THEN
4850: oe_debug_pub.add ('AAC:VTI: site_id: multiple accounts, returning with error ');
4851: END IF;
4852:
4853: x_return_status := FND_API.G_RET_STS_ERROR;
4854: p_cust_account_id := null;
4864: --THEN
4865: OE_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,'find_site_id' );
4866: --END IF;
4867: IF l_debug_level > 4 THEN
4868: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4869: END IF;
4870: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4871:
4872: end;
4879:
4880: if nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4881: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4882: IF l_debug_level > 0 THEN
4883: oe_debug_pub.add ('AAC:FSTI: party_id:'|| p_party_id||' is not null, picking up account_id from it');
4884: END IF;
4885:
4886: open c_get_account_id(p_party_id);
4887:
4892:
4893: if c_get_account_id%ROWCOUNT > 1 then
4894:
4895: IF l_debug_level > 0 THEN
4896: oe_debug_pub.add ('AAC:FSTI: ERROR: multiple accounts found');
4897: END IF;
4898:
4899: --x_return_status := FND_API.G_RET_STS_ERROR;
4900: p_cust_account_id := null;
4909:
4910: if (c_get_account_id%ROWCOUNT = 1)
4911: then
4912: IF l_debug_level > 0 THEN
4913: oe_debug_pub.add ('AAC:FSTI: one account found:'||p_cust_account_id);
4914: END IF;
4915: end if;
4916:
4917: if (c_get_account_id%ROWCOUNT = 0)
4916:
4917: if (c_get_account_id%ROWCOUNT = 0)
4918: then
4919: IF l_debug_level > 0 THEN
4920: oe_debug_pub.add ('AAC:FSTI: no account found');
4921: END IF;
4922: p_cust_account_id := NULL;
4923: end if;
4924:
4934: IF p_permission <> 'Y' THEN
4935: -- reset cust_account_id, set error condition
4936: -- and return
4937: IF l_debug_level > 0 THEN
4938: oe_debug_pub.add ('AAC:FSTI: no permission to create account: returning');
4939: END IF;
4940: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
4941: OE_MSG_PUB.ADD;
4942: p_cust_account_id := NULL;
4948: -- no matching cust_account_id found,
4949: -- we have permission to create account,
4950: -- and going to search for name in party layer
4951: IF l_debug_level > 0 THEN
4952: oe_debug_pub.add ('AAC:FSTI: have permission to create account: trying to find party_id');
4953: END IF;
4954:
4955: p_party_id := get_party_id(
4956: p_party_name => p_party_name,
4963: THEN
4964: -- didn't find a party_id either
4965: -- since we *cannot* create a party, error out
4966: IF l_debug_level > 0 THEN
4967: oe_debug_pub.add ('AAC:FSTI: cannot find party_id, returning with error');
4968: END IF;
4969: p_party_id := NULL;
4970: x_return_status := FND_API.G_RET_STS_ERROR;
4971: return;
4971: return;
4972: END IF;
4973:
4974: IF l_debug_level > 0 THEN
4975: oe_debug_pub.add ('AAC:FSTI: found party_id:'||p_party_id);
4976: END IF;
4977:
4978:
4979: IF l_debug_level > 0 THEN
4976: END IF;
4977:
4978:
4979: IF l_debug_level > 0 THEN
4980: oe_debug_pub.add ('AAC:VTI: ending find_sold_to_id');
4981: END IF;
4982:
4983: return;
4984:
4994: OE_MSG_PUB.Add;
4995:
4996: END IF;
4997: IF l_debug_level > 4 THEN
4998: oe_debug_pub.add ('AAC:VTI: invalid p_cust_account_id in find_sold_to_id'); -- got a party_name
4999: END IF;
5000:
5001: x_return_status := FND_API.G_RET_STS_ERROR;
5002: return;
5010: ,'find_Sold_To_Org_id'
5011: );
5012: END IF;
5013: IF l_debug_level > 4 THEN
5014: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id'); -- got a party_name
5015: END IF;
5016: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5017:
5018: END find_sold_to_id;
5029: ,x_return_status OUT NOCOPY varchar2
5030: )
5031: IS
5032: -- local variables here
5033: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
5034:
5035: BEGIN
5036:
5037: x_return_status := FND_API.G_RET_STS_SUCCESS;
5036:
5037: x_return_status := FND_API.G_RET_STS_SUCCESS;
5038:
5039: IF l_debug_level > 0 THEN
5040: oe_debug_pub.add ('AAC:VTI: starting find_contact_id...');
5041: END IF;
5042:
5043: -- check if cust_contact_id is not null, return if so
5044: if nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5042:
5043: -- check if cust_contact_id is not null, return if so
5044: if nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5045: IF l_debug_level > 0 THEN
5046: oe_debug_pub.add ('AAC:FCI: cust_contact_id is not null, nothing to do: warn; returning}');
5047: END IF;
5048:
5049: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
5050: THEN
5057: END IF;
5058:
5059: if nvl(p_sold_to_org_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5060: IF l_debug_level > 0 THEN
5061: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is null; returning');
5062: END IF;
5063:
5064: return;
5065: end if;
5065: end if;
5066:
5067:
5068: IF l_debug_level > 0 THEN
5069: oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5070: oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5071: oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5072: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5073: END IF;
5066:
5067:
5068: IF l_debug_level > 0 THEN
5069: oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5070: oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5071: oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5072: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5073: END IF;
5074:
5067:
5068: IF l_debug_level > 0 THEN
5069: oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5070: oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5071: oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5072: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5073: END IF;
5074:
5075: -- look for cust_contact_id using contact_name
5068: IF l_debug_level > 0 THEN
5069: oe_debug_pub.add ('AAC:FCI: contact_name is '||p_contact_name);
5070: oe_debug_pub.add ('AAC:FCI: org_contact_id is '||p_contact_id);
5071: oe_debug_pub.add ('AAC:FCI: party_id is '||p_party_id);
5072: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is '|| p_sold_to_org_id);
5073: END IF;
5074:
5075: -- look for cust_contact_id using contact_name
5076:
5077:
5078: if nvl(p_contact_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
5079: and nvl(p_cust_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5080: IF l_debug_level > 0 THEN
5081: oe_debug_pub.add ('AAC:FCI: contact_name is not null,trying to find account contact_id');
5082: END IF;
5083: if p_site_use_code <> 'END_CUST' then
5084: p_cust_contact_id := OE_Value_To_Id.sold_to_contact(
5085: p_sold_to_contact => p_contact_name
5095: IF nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5096: THEN
5097: -- found a cust_contact_id
5098: IF l_debug_level > 0 THEN
5099: oe_debug_pub.add ('AAC:FCI: found cust_contact_id:'||p_cust_contact_id||', returning}');
5100: END IF;
5101: return;
5102: else
5103: p_cust_contact_id := null;
5107:
5108: -- check if contact_id is not null, return if so
5109: if nvl(p_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5110: IF l_debug_level > 0 THEN
5111: oe_debug_pub.add ('AAC:FSTI: org_contact_id is not null');
5112: END IF;
5113:
5114: RETURN;
5115: END IF;
5119: -- no matching cust_contact_id found,
5120: -- we have permission to create contact,
5121: -- and going to search for name in party layer
5122: IF l_debug_level > 0 THEN
5123: oe_debug_pub.add ('AAC:FCI: no account contact_id');
5124: oe_debug_pub.add ('AAC:FCI: have permission to create account contact: finding party contact_id');
5125: END IF;
5126:
5127: IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5120: -- we have permission to create contact,
5121: -- and going to search for name in party layer
5122: IF l_debug_level > 0 THEN
5123: oe_debug_pub.add ('AAC:FCI: no account contact_id');
5124: oe_debug_pub.add ('AAC:FCI: have permission to create account contact: finding party contact_id');
5125: END IF;
5126:
5127: IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5128: THEN
5137: THEN
5138: -- didn't find a party level contact_id either
5139: -- since we *cannot* create a party, error out
5140: IF l_debug_level > 0 THEN
5141: oe_debug_pub.add ('AAC:FCI: cannot find contact_id, returning with error}');
5142: END IF;
5143: p_contact_id := NULL;
5144: x_return_status := FND_API.G_RET_STS_ERROR;
5145: return;
5147: end if;
5148: -- found a contact_id
5149:
5150: IF l_debug_level > 0 THEN
5151: oe_debug_pub.add ('AAC:FCI: found contact_id:'||p_contact_id);
5152: oe_debug_pub.add ('AAC:VTI: ...done find_contact_id');
5153: END IF;
5154:
5155: return;
5148: -- found a contact_id
5149:
5150: IF l_debug_level > 0 THEN
5151: oe_debug_pub.add ('AAC:FCI: found contact_id:'||p_contact_id);
5152: oe_debug_pub.add ('AAC:VTI: ...done find_contact_id');
5153: END IF;
5154:
5155: return;
5156:
5180: ,x_msg_count out NOCOPY varchar2
5181: )
5182: IS
5183: -- local variables here
5184: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
5185: l_cust_account_id number := null;
5186:
5187: BEGIN
5188:
5188:
5189: x_return_status := FND_API.G_RET_STS_SUCCESS;
5190:
5191: IF l_debug_level > 0 THEN
5192: oe_debug_pub.add ('AAC:VTI: FSI: starting find_site_id{');
5193: END IF;
5194:
5195: if (nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5196: then
5194:
5195: if (nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5196: then
5197: IF l_debug_level > 0 THEN
5198: oe_debug_pub.add ('AAC:FSI: party_id null; returning');
5199: END IF;
5200:
5201: return;
5202: end if;
5211:
5212: -- check if site_address1 is null
5213: IF l_debug_level > 0 THEN
5214: if nvl(p_site_address1,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
5215: oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5216: else
5217: oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5218: end if;
5219: oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5213: IF l_debug_level > 0 THEN
5214: if nvl(p_site_address1,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
5215: oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5216: else
5217: oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5218: end if;
5219: oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5220: END IF;
5221:
5215: oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5216: else
5217: oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5218: end if;
5219: oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5220: END IF;
5221:
5222: -- check if cust_site_use_id is not null, return if so
5223: if nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5221:
5222: -- check if cust_site_use_id is not null, return if so
5223: if nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5224: IF l_debug_level > 0 THEN
5225: oe_debug_pub.add ('AAC:FSI: account_site_use_id is not null; returning');
5226: END IF;
5227:
5228: RETURN;
5229: END IF;
5230:
5231: -- check if site_use_code is null, return with error if so
5232: if nvl(p_site_use_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR then
5233: IF l_debug_level > 0 THEN
5234: oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5235: oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5236: end if;
5237: x_return_status := FND_API.G_RET_STS_ERROR;
5238: return;
5231: -- check if site_use_code is null, return with error if so
5232: if nvl(p_site_use_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR then
5233: IF l_debug_level > 0 THEN
5234: oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5235: oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5236: end if;
5237: x_return_status := FND_API.G_RET_STS_ERROR;
5238: return;
5239: end if;
5257: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5258: then
5259: --nothing to do! return!
5260: IF l_debug_level > 0 THEN
5261: oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5262: END IF;
5263: return;
5264: end if;
5265:
5263: return;
5264: end if;
5265:
5266: IF l_debug_level > 0 THEN
5267: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5268: oe_debug_pub.add('p_site_id :'|| p_site_id );
5269: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5270: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5264: end if;
5265:
5266: IF l_debug_level > 0 THEN
5267: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5268: oe_debug_pub.add('p_site_id :'|| p_site_id );
5269: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5270: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5265:
5266: IF l_debug_level > 0 THEN
5267: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5268: oe_debug_pub.add('p_site_id :'|| p_site_id );
5269: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5270: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5266: IF l_debug_level > 0 THEN
5267: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5268: oe_debug_pub.add('p_site_id :'|| p_site_id );
5269: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5270: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5267: oe_debug_pub.add('p_site_use_id :'|| p_site_use_id );
5268: oe_debug_pub.add('p_site_id :'|| p_site_id );
5269: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5270: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5268: oe_debug_pub.add('p_site_id :'|| p_site_id );
5269: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5270: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5269: oe_debug_pub.add('p_account_site_use_id :'|| p_account_site_use_id );
5270: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5270: oe_debug_pub.add('p_site_use_code :'|| p_site_use_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5278: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5271: oe_debug_pub.add('p_site_address1 :'|| p_site_address1 );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5278: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5279: oe_debug_pub.add('p_site_country :'|| p_site_country );
5272: oe_debug_pub.add('p_site_address2 :'|| p_site_address2 );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5278: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5279: oe_debug_pub.add('p_site_country :'|| p_site_country );
5280: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5273: oe_debug_pub.add('p_site_address3 :'|| p_site_address3 );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5278: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5279: oe_debug_pub.add('p_site_country :'|| p_site_country );
5280: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5281: oe_debug_pub.add('p_party_id :'|| p_party_id );
5274: oe_debug_pub.add('p_site_address4 :'|| p_site_address4 );
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5278: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5279: oe_debug_pub.add('p_site_country :'|| p_site_country );
5280: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5281: oe_debug_pub.add('p_party_id :'|| p_party_id );
5282: end if;
5275: oe_debug_pub.add('p_site_org :'|| p_site_org );
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5278: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5279: oe_debug_pub.add('p_site_country :'|| p_site_country );
5280: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5281: oe_debug_pub.add('p_party_id :'|| p_party_id );
5282: end if;
5283:
5276: oe_debug_pub.add('p_site_city :'|| p_site_city );
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5278: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5279: oe_debug_pub.add('p_site_country :'|| p_site_country );
5280: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5281: oe_debug_pub.add('p_party_id :'|| p_party_id );
5282: end if;
5283:
5284: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5277: oe_debug_pub.add('p_site_state :'|| p_site_state );
5278: oe_debug_pub.add('p_site_postal_code :'|| p_site_postal_code );
5279: oe_debug_pub.add('p_site_country :'|| p_site_country );
5280: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5281: oe_debug_pub.add('p_party_id :'|| p_party_id );
5282: end if;
5283:
5284: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5285: and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5283:
5284: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5285: and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5286: IF l_debug_level > 0 THEN
5287: oe_debug_pub.add ('AAC:FSI: party_site_use_id:'||p_site_use_id||' is not null; using it to get party_site_id');
5288: END IF;
5289: begin
5290:
5291: select party_site_id
5305: OE_MSG_PUB.ADD;
5306: END IF;
5307:
5308: IF l_debug_level > 4 THEN
5309: oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
5310: END IF;
5311:
5312: x_return_status := FND_API.G_RET_STS_ERROR;
5313: return;
5321: ,'find_site_id'
5322: );
5323: --END IF;
5324: IF l_debug_level > 4 THEN
5325: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5326: END IF;
5327: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5328:
5329: end;
5332:
5333: if nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5334: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5335: IF l_debug_level > 0 THEN
5336: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5337: END IF;
5338: begin
5339: select s.party_id
5340: into p_party_id
5342: where s.party_site_id=p_site_id
5343: and s.status='A';
5344:
5345: IF l_debug_level > 0 THEN
5346: oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5347: END IF;
5348:
5349:
5350: EXCEPTION
5358: OE_MSG_PUB.ADD;
5359:
5360: END IF;
5361: IF l_debug_level > 4 THEN
5362: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5363: END IF;
5364: x_return_status := FND_API.G_RET_STS_ERROR;
5365: return;
5366:
5373: ,'find_site_id'
5374: );
5375: END IF;
5376: IF l_debug_level > 4 THEN
5377: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5378: END IF;
5379: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5380:
5381: end;
5392: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5393: then
5394: --nothing to do! return!
5395: IF l_debug_level > 0 THEN
5396: oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5397: END IF;
5398: return;
5399: end if;
5400:
5399: end if;
5400:
5401: -- look for cust_site_id using site_name
5402: IF l_debug_level > 0 THEN
5403: oe_debug_pub.add ('AAC:FSI: trying to find account site_use_id');
5404: END IF;
5405:
5406: /*
5407: if p_site_use_code='SHIP_TO'
5458:
5459: else
5460: -- ERROR!
5461: IF l_debug_level > 0 THEN
5462: oe_debug_pub.add ('AAC:FSI: site_use_code is invalid:'||p_site_use_code);
5463: END IF;
5464: return;
5465: end if;
5466:
5468: IF nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5469: THEN
5470: -- found a a cust_site_id
5471: IF l_debug_level > 0 THEN
5472: oe_debug_pub.add ('AAC:FSI: found cust_site_use_id:'||p_account_site_use_id||', returning');
5473: oe_debug_pub.add ('AAC:VTI: ending find_site_id');
5474: END IF;
5475:
5476:
5469: THEN
5470: -- found a a cust_site_id
5471: IF l_debug_level > 0 THEN
5472: oe_debug_pub.add ('AAC:FSI: found cust_site_use_id:'||p_account_site_use_id||', returning');
5473: oe_debug_pub.add ('AAC:VTI: ending find_site_id');
5474: END IF;
5475:
5476:
5477: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5475:
5476:
5477: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5478: IF l_debug_level > 0 THEN
5479: oe_debug_pub.add ('AAC:FSI: cust_site_use_id:'||p_account_site_use_id||' is not null; using it to get party_id');
5480: END IF;
5481: begin
5482: select a.party_id
5483: into p_party_id
5488: and u.cust_acct_site_id=s.cust_acct_site_id
5489: and s.cust_account_id=a.cust_account_id;
5490:
5491: IF l_debug_level > 0 THEN
5492: oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5493: END IF;
5494:
5495:
5496: EXCEPTION
5504: OE_MSG_PUB.ADD;
5505:
5506: END IF;
5507: IF l_debug_level > 4 THEN
5508: oe_debug_pub.add ('AAC:VTI: invalid cust_site_use_id ');
5509: END IF;
5510: x_return_status := FND_API.G_RET_STS_ERROR;
5511: return;
5512:
5526: -- no matching cust_site_id found,
5527: -- we have permission to create site,
5528: -- and going to search for name in party layer
5529: IF l_debug_level > 0 THEN
5530: oe_debug_pub.add ('AAC:FSI: no account site_use_id');
5531: oe_debug_pub.add ('AAC:FSI: finding party site_use_id');
5532: END IF;
5533:
5534: if (nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) then
5527: -- we have permission to create site,
5528: -- and going to search for name in party layer
5529: IF l_debug_level > 0 THEN
5530: oe_debug_pub.add ('AAC:FSI: no account site_use_id');
5531: oe_debug_pub.add ('AAC:FSI: finding party site_use_id');
5532: END IF;
5533:
5534: if (nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) then
5535: p_site_id := get_party_site_id(
5553: THEN
5554: -- didn't find a party level site_id either
5555: -- since we *cannot* create a party, error out
5556: IF l_debug_level > 0 THEN
5557: oe_debug_pub.add ('AAC:FSI: cannot find party_site_id, returning with error');
5558: END IF;
5559: p_site_id := NULL;
5560: --x_return_status := FND_API.G_RET_STS_ERROR;
5561: return;
5561: return;
5562: ELSE -- we did find a party_site_id, get party_id from it
5563: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5564: IF l_debug_level > 0 THEN
5565: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5566: END IF;
5567: begin
5568: select s.party_id
5569: into p_party_id
5571: where s.party_site_id=p_site_id
5572: and s.status='A';
5573:
5574: IF l_debug_level > 0 THEN
5575: oe_debug_pub.add ('AAC:FSI: got party_id:'||p_party_id);
5576: END IF;
5577:
5578:
5579: EXCEPTION
5587: OE_MSG_PUB.ADD;
5588:
5589: END IF;
5590: IF l_debug_level > 4 THEN
5591: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5592: END IF;
5593: x_return_status := FND_API.G_RET_STS_ERROR;
5594: return;
5595:
5602: ,'find_site_id'
5603: );
5604: END IF;
5605: IF l_debug_level > 4 THEN
5606: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5607: END IF;
5608: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5609:
5610: end;
5614:
5615: -- found a site_id
5616:
5617: IF l_debug_level > 0 THEN
5618: oe_debug_pub.add ('AAC:FSI: party site_use_id:'||p_site_use_id);
5619: oe_debug_pub.add ('AAC:FSI: account site_use_id:'||p_account_site_use_id);
5620: END IF;
5621:
5622: return;
5615: -- found a site_id
5616:
5617: IF l_debug_level > 0 THEN
5618: oe_debug_pub.add ('AAC:FSI: party site_use_id:'||p_site_use_id);
5619: oe_debug_pub.add ('AAC:FSI: account site_use_id:'||p_account_site_use_id);
5620: END IF;
5621:
5622: return;
5623:
5631: ,p_party_site_use_code in varchar2
5632: ) return number
5633: IS
5634: l_id NUMBER;
5635: l_debug_level CONSTANT NUMBER := 5;--oe_debug_pub.g_debug_level;
5636: BEGIN
5637: IF l_debug_level > 4 THEN
5638: oe_debug_pub.add ('AAC:VTI: starting get_party_id{');
5639: END IF;
5634: l_id NUMBER;
5635: l_debug_level CONSTANT NUMBER := 5;--oe_debug_pub.g_debug_level;
5636: BEGIN
5637: IF l_debug_level > 4 THEN
5638: oe_debug_pub.add ('AAC:VTI: starting get_party_id{');
5639: END IF;
5640:
5641: -- did they actually pass some values?
5642: IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5642: IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5643: AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5644: THEN
5645: IF l_debug_level > 4 THEN
5646: oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5647: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5648: END IF;
5649: RETURN NULL;
5650: END IF;
5643: AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5644: THEN
5645: IF l_debug_level > 4 THEN
5646: oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5647: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5648: END IF;
5649: RETURN NULL;
5650: END IF;
5651:
5652: IF (nvl(p_party_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char)
5653: THEN
5654: -- got a party_number
5655: IF l_debug_level > 4 THEN
5656: oe_debug_pub.add ('AAC:VTI:GPI party number: '||p_party_number);
5657: END IF;
5658:
5659: SELECT party_id
5660: INTO l_id
5663: and status='A';
5664:
5665: ELSE
5666: IF l_debug_level > 4 THEN
5667: oe_debug_pub.add ('AAC:VTI:GPI name: '||p_party_name); -- got a party_name
5668: END IF;
5669:
5670: SELECT party.party_id
5671: INTO l_id
5675:
5676: END IF;
5677:
5678: IF l_debug_level > 4 THEN
5679: oe_debug_pub.add ('AAC:VTI:GPI: party_id: '||l_id);
5680: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5681: END IF;
5682:
5683: return l_id;
5676: END IF;
5677:
5678: IF l_debug_level > 4 THEN
5679: oe_debug_pub.add ('AAC:VTI:GPI: party_id: '||l_id);
5680: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5681: END IF;
5682:
5683: return l_id;
5684: EXCEPTION
5705: OE_MSG_PUB.Add;
5706:
5707: END IF;
5708: IF l_debug_level > 4 THEN
5709: oe_debug_pub.add ('AAC:VTI: no data in Get_party_id'); -- got a party_name
5710: END IF;
5711: RETURN FND_API.G_MISS_NUM;
5712:
5713: WHEN OTHERS THEN
5719: ,'get_party_id' --modified for bug 4590205
5720: );
5721: END IF;
5722: IF l_debug_level > 4 THEN
5723: oe_debug_pub.add ('AAC:VTI: unexpected error in Get_party_id'); -- got a party_name
5724: END IF;
5725: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5726:
5727: END get_party_id;
5752: AND arl.lookup_type(+)='CONTACT_TITLE';
5753:
5754:
5755: l_id NUMBER;
5756: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5757: BEGIN
5758:
5759: IF l_debug_level > 4 THEN
5760: oe_debug_pub.add ('AAC:VTI: starting get_party_contact_id...');
5756: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5757: BEGIN
5758:
5759: IF l_debug_level > 4 THEN
5760: oe_debug_pub.add ('AAC:VTI: starting get_party_contact_id...');
5761: END IF;
5762:
5763: IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5764: THEN
5762:
5763: IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5764: THEN
5765: IF l_debug_level > 4 THEN
5766: oe_debug_pub.add ('AAC:VTI: no contact_name');
5767: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5768: END IF;
5769: RETURN NULL;
5770: END IF;
5763: IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5764: THEN
5765: IF l_debug_level > 4 THEN
5766: oe_debug_pub.add ('AAC:VTI: no contact_name');
5767: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5768: END IF;
5769: RETURN NULL;
5770: END IF;
5771:
5775:
5776: IF c_org_contact_id%FOUND then
5777: CLOSE c_org_contact_id;
5778: IF l_debug_level > 4 THEN
5779: oe_debug_pub.add ('AAC:VTI: org_contact_id:'||l_id);
5780: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5781: end if;
5782: return l_id;
5783: end if;
5776: IF c_org_contact_id%FOUND then
5777: CLOSE c_org_contact_id;
5778: IF l_debug_level > 4 THEN
5779: oe_debug_pub.add ('AAC:VTI: org_contact_id:'||l_id);
5780: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5781: end if;
5782: return l_id;
5783: end if;
5784:
5782: return l_id;
5783: end if;
5784:
5785: IF l_debug_level > 4 THEN
5786: oe_debug_pub.add ('AAC:VTI: GCI org_contact_id not found');
5787: end if;
5788:
5789: CLOSE c_org_contact_id;
5790:
5788:
5789: CLOSE c_org_contact_id;
5790:
5791: IF l_debug_level > 4 THEN
5792: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5793: END IF;
5794:
5795: return NULL;
5796:
5900: l_sold_to_party_id number;
5901: l_dummy number;
5902:
5903: --
5904: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5905: --
5906: BEGIN
5907:
5908: IF l_debug_level > 0 THEN
5905: --
5906: BEGIN
5907:
5908: IF l_debug_level > 0 THEN
5909: oe_debug_pub.add( 'AAC: site_address1:'||P_SITE_ADDRESS1);
5910: oe_debug_pub.add(' address4:'||p_site_address4);
5911: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5912: END IF;
5913:
5906: BEGIN
5907:
5908: IF l_debug_level > 0 THEN
5909: oe_debug_pub.add( 'AAC: site_address1:'||P_SITE_ADDRESS1);
5910: oe_debug_pub.add(' address4:'||p_site_address4);
5911: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5912: END IF;
5913:
5914: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5907:
5908: IF l_debug_level > 0 THEN
5909: oe_debug_pub.add( 'AAC: site_address1:'||P_SITE_ADDRESS1);
5910: oe_debug_pub.add(' address4:'||p_site_address4);
5911: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5912: END IF;
5913:
5914: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5917: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5918: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5919: THEN
5920: IF l_debug_level > 0 THEN
5921: oe_debug_pub.add( 'AAC: all incoming data missing,returning');
5922: end if;
5923: RETURN NULL;
5924: END IF;
5925:
5924: END IF;
5925:
5926: if (nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) then
5927: IF l_debug_level > 0 THEN
5928: oe_debug_pub.add( 'AAC: incoming party_id is '||p_party_id ) ;
5929: END IF;
5930:
5931: OPEN c_party_site_id(p_party_id);
5932: FETCH c_party_site_id
5934:
5935: IF c_party_site_id%FOUND then
5936: CLOSE c_party_site_id;
5937: IF l_debug_level > 0 THEN
5938: oe_debug_pub.add( 'AAC: found party_site_id is '||l_id ) ;
5939: END IF;
5940: return l_id;
5941:
5942: ELSE
5940: return l_id;
5941:
5942: ELSE
5943: IF l_debug_level > 0 THEN
5944: oe_debug_pub.add( 'AAC: not found party_site_id in 1st try; trying SQL2' ) ;
5945: END IF;
5946:
5947: SELECT site.party_site_id
5948: INTO l_id
5965: END IF;
5966: CLOSE c_party_site_id;
5967:
5968: IF l_debug_level > 0 THEN
5969: oe_debug_pub.add( 'AAC: found party_site_id is '||l_id );
5970: END IF;
5971:
5972: RETURN l_id;
5973:
5973:
5974: ELSE --p_party_id is null...
5975:
5976: IF l_debug_level > 0 THEN
5977: oe_debug_pub.add( 'AAC: party_id is null' ) ;
5978: END IF;
5979:
5980: OPEN C2;
5981:
5984:
5985: IF C2%FOUND then
5986: CLOSE C2 ;
5987: IF l_debug_level > 0 THEN
5988: oe_debug_pub.add( 'AAC: found site_use_id = '||L_ID ) ;
5989: END IF;
5990: return l_id;
5991:
5992: ELSE
5990: return l_id;
5991:
5992: ELSE
5993: IF l_debug_level > 0 THEN
5994: oe_debug_pub.add( 'AAC: not found party_site_id in 1st try; trying SQL2' ) ;
5995: END IF;
5996:
5997: SELECT site.party_site_id
5998: INTO l_id
6011: AND site.status = 'A'
6012: and site.location_id=loc.location_id;
6013:
6014: IF l_debug_level > 0 THEN
6015: oe_debug_pub.add( ' found site_use_id = '||L_ID ) ;
6016: END IF;
6017:
6018: END IF;
6019: CLOSE C2;
6078: ) RETURN NUMBER
6079: IS
6080: l_id NUMBER;
6081: --
6082: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6083: --
6084: BEGIN
6085:
6086: IF nvl(p_sold_to_org,fnd_api.g_miss_char) = fnd_api.g_miss_char
6164: WHEN NO_DATA_FOUND THEN
6165: x_found:='N';
6166:
6167: WHEN OTHERS THEN
6168: oe_debug_pub.add('Yes... Error in Autonomous Block:'||SQLERRM);
6169: x_found:='E';
6170: END;
6171:
6172: --------------------------------------------------------------
6176: BEGIN
6177:
6178: does_Cust_Exist(p_cust_id => cust_id,
6179: x_found => l_temp);
6180: oe_debug_pub.add('Yes.. does_Cust_Exist:'||l_temp);
6181: IF l_temp='Y' THEN
6182: return TRUE;
6183: ELSIF l_temp='N' THEN
6184: oe_debug_pub.add('Yes.. Committing');
6180: oe_debug_pub.add('Yes.. does_Cust_Exist:'||l_temp);
6181: IF l_temp='Y' THEN
6182: return TRUE;
6183: ELSIF l_temp='N' THEN
6184: oe_debug_pub.add('Yes.. Committing');
6185: COMMIT;
6186: return TRUE;
6187: ELSE
6188: return FALSE;
6188: return FALSE;
6189: END IF;
6190:
6191: EXCEPTION WHEN OTHERS THEN
6192: oe_debug_pub.add('Yes... Error in CUST_EXISTS:'||SQLERRM);
6193: return FALSE;
6194: END Cust_Exists;
6195:
6196: END oe_create_account_info;