[Home] [Help]
95: --
96: BEGIN
97: SAVEPOINT CREATE_ACCOUNT_LAYER;
98:
99: x_return_status := FND_API.G_RET_STS_SUCCESS;
100:
101: -- Checking to see if the OM message stack needs to be initialized
102: IF p_control_rec.p_init_msg_list THEN
103: oe_msg_pub.initialize;
196: ,x_return_status => x_return_status
197: ,x_msg_count => x_msg_count
198: ,x_msg_data => x_msg_data);
199:
200: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
201: OE_MSG_PUB.Count_And_Get
202: ( p_count => x_msg_count
203: , p_data => x_msg_data
204: );
232:
233: --p_party_customer_rec.p_org_contact_id IS NULL AND
234: --p_party_customer_rec.p_cust_account_role_id IS NULL THEN
235:
236: x_return_status := FND_API.G_RET_STS_ERROR;
237: IF l_debug_level > 0 THEN
238: oe_debug_pub.add( 'Returning with error as '|| ' required information is not sent' ) ;
239: END IF;
240: return;
240: return;
241: END IF;
242:
243: IF p_control_rec.p_created_by_module IS NULL THEN
244: x_return_status := FND_API.G_RET_STS_ERROR;
245: IF l_debug_level > 0 THEN
246: oe_debug_pub.add( 'returning with error as '|| ' created by module is not sent' ) ;
247: END IF;
248: return;
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: );
353: IF p_account_tbl.COUNT > 1 then
354: IF l_debug_level > 0 THEN
355: oe_debug_pub.add( 'returning with multiple accounts' ) ;
356: end IF;
357: x_return_status := FND_API.G_RET_STS_SUCCESS;
358: l_multiple_account := TRUE;
359: return;
360: ELSIF p_account_tbl.COUNT = 0 then
361: IF l_debug_level > 0 THEN
363: end IF;
364: /*cc project, We need to return the status success, if account creation is not allowed so that
365: Add customer will be shown */
366: IF ((p_control_rec.p_return_if_only_party AND p_site_tbl.COUNT = 0) OR ( G_CREATED_BY_MODULE = 'ONT_TELESERVICE_INTEGRATION' AND NOT p_allow_account_creation))THEN
367: x_return_status := FND_API.G_RET_STS_SUCCESS;
368: ELSE
369: x_return_status := FND_API.G_RET_STS_ERROR;
370: END IF;
371:
365: Add customer will be shown */
366: IF ((p_control_rec.p_return_if_only_party AND p_site_tbl.COUNT = 0) OR ( G_CREATED_BY_MODULE = 'ONT_TELESERVICE_INTEGRATION' AND NOT p_allow_account_creation))THEN
367: x_return_status := FND_API.G_RET_STS_SUCCESS;
368: ELSE
369: x_return_status := FND_API.G_RET_STS_ERROR;
370: END IF;
371:
372: return;
373: ELSIF p_account_tbl.COUNT = 1 then
370: END IF;
371:
372: return;
373: ELSIF p_account_tbl.COUNT = 1 then
374: x_return_status := FND_API.G_RET_STS_SUCCESS;
375: l_cust_account_id := p_account_tbl(1);
376: END IF;
377: ELSE
378: IF l_debug_level > 0 THEN
391: l_party_id;
392:
393: IF c_check_account%FOUND THEN
394: IF l_status <>'A' then
395: x_return_status := FND_API.G_RET_STS_ERROR;
396: IF l_debug_level > 0 THEN
397: oe_debug_pub.add( 'account is not active' ) ;
398: END IF;
399: FND_MESSAGE.Set_Name('ONT','ONT_AAC_INACTIVE_ACCOUNT');
411: p_account_tbl(1) := p_party_customer_rec.p_cust_account_id;
412: p_party_customer_rec.p_party_id := l_party_id;
413:
414: ELSIF c_check_account%NOTFOUND THEN
415: x_return_status := FND_API.G_RET_STS_ERROR;
416: IF l_debug_level > 0 THEN
417: oe_debug_pub.add( 'invalid account.no such account_id ' ) ;
418: END IF;
419: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
441: IF c_get_cust_account_id%NOTFOUND THEN
442: IF l_debug_level > 0 THEN
443: oe_debug_pub.add( 'invalid customer account number:'||p_party_customer_rec.p_cust_account_Number ) ;
444: END IF;
445: x_return_status := FND_API.G_RET_STS_ERROR;
446: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
447: OE_MSG_PUB.ADD;
448: CLOSE c_get_cust_account_id;
449: OE_MSG_PUB.Count_And_Get
452: );
453: return;
454: ELSIF c_get_cust_account_id%FOUND THEN
455: IF l_status <> 'A' then
456: x_return_status := FND_API.G_RET_STS_ERROR;
457: IF l_debug_level > 0 THEN
458: oe_debug_pub.add( 'account for account number is not active' ) ;
459: END IF;
460: FND_MESSAGE.Set_Name('ONT','ONT_AAC_INACTIVE_ACCOUNT');
546: p_party_customer_rec.p_cust_account_number IS NULL AND
547: p_party_customer_rec.p_party_number IS NULL and
548: p_site_tbl(i).p_site_use_code <>'END_CUST' then
549:
550: x_return_status := FND_API.G_RET_STS_ERROR;
551: IF l_debug_level > 0 THEN
552: oe_debug_pub.add( 'Returning with error as '|| ' required information is not sent' ) ;
553: END IF;
554:
606: IF l_account_tbl.COUNT > 1 then
607: IF l_debug_level > 0 THEN
608: oe_debug_pub.add( 'AAC:RC: returning with multiple accounts' ) ;
609: END IF;
610: x_return_status := FND_API.G_RET_STS_SUCCESS;
611: --l_multiple_account := TRUE;
612: P_account_tbl:=l_account_tbl;
613: return;
614: ELSIF l_account_tbl.COUNT = 0 then
615: IF l_debug_level > 0 THEN
616: oe_debug_pub.add( 'AAC:RC: no accounts found returning' ) ;
617: END IF;
618: IF p_control_rec.p_return_if_only_party AND p_site_tbl.COUNT = 0 THEN
619: x_return_status := FND_API.G_RET_STS_SUCCESS;
620: ELSE
621: x_return_status := FND_API.G_RET_STS_ERROR;
622: END IF;
623: x_return_status := FND_API.G_RET_STS_SUCCESS;
617: END IF;
618: IF p_control_rec.p_return_if_only_party AND p_site_tbl.COUNT = 0 THEN
619: x_return_status := FND_API.G_RET_STS_SUCCESS;
620: ELSE
621: x_return_status := FND_API.G_RET_STS_ERROR;
622: END IF;
623: x_return_status := FND_API.G_RET_STS_SUCCESS;
624: return;
625: ELSIF l_account_tbl.COUNT = 1 then
619: x_return_status := FND_API.G_RET_STS_SUCCESS;
620: ELSE
621: x_return_status := FND_API.G_RET_STS_ERROR;
622: END IF;
623: x_return_status := FND_API.G_RET_STS_SUCCESS;
624: return;
625: ELSIF l_account_tbl.COUNT = 1 then
626: x_return_status := FND_API.G_RET_STS_SUCCESS;
627: l2_cust_account_id := l_account_tbl(1);
622: END IF;
623: x_return_status := FND_API.G_RET_STS_SUCCESS;
624: return;
625: ELSIF l_account_tbl.COUNT = 1 then
626: x_return_status := FND_API.G_RET_STS_SUCCESS;
627: l2_cust_account_id := l_account_tbl(1);
628:
629: -- populate cust_account_id back to site_tbl
630: IF l_debug_level > 0 THEN
664: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
665: FND_MESSAGE.Set_Token('TEXT',' Customer Relationship is not allowed. SoldTo and Site Level Customer should be same');
666: OE_MSG_PUB.ADD;
667: oe_debug_pub.add('AAC:RC: ERROR: Customers are different');
668: x_return_status := FND_API.G_RET_STS_ERROR;
669: return;
670: END IF;
671:
672: elsif (lcustomer_relations = 'Y')
712: ,x_return_status => x_return_status
713: ,x_msg_count => x_msg_count
714: ,x_msg_data => x_msg_data);
715:
716: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
717: oe_debug_pub.add('Creating relationship failed');
718:
719: x_return_status := FND_API.G_RET_STS_ERROR;
720: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
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' );
731: END IF;
732: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
762:
763: -- if the check_customer did not fail or did not return multiple accts
764: -- then call check contact
765:
766: IF x_return_status <> FND_API.G_RET_STS_ERROR AND
767: ( p_account_tbl.COUNT = 1 OR
768: p_party_customer_rec.p_cust_account_id IS NOT NULL) AND
769: p_Control_rec.p_process_contact
770: THEN
821: END IF;
822: END LOOP;
823: END IF;
824:
825: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
826: IF l_debug_level > 0 THEN
827: oe_debug_pub.add( 'contact with error' ) ;
828: END IF;
829: l_contact_status := FND_API.G_RET_STS_ERROR;
825: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
826: IF l_debug_level > 0 THEN
827: oe_debug_pub.add( 'contact with error' ) ;
828: END IF;
829: l_contact_status := FND_API.G_RET_STS_ERROR;
830: x_msg_data_contact := x_msg_data;
831: x_msg_count_contact := x_msg_count;
832: --OE_MSG_PUB.Count_And_Get
833: --( p_count => x_msg_count
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;
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
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:
889:
890: -- If status is not error and there are records in the site table
891: -- OR we shall continue even if error
892: IF (p_control_rec.p_continue_processing_on_error AND p_site_tbl.COUNT >0 ) OR
893: (x_return_status <> FND_API.G_RET_STS_ERROR AND p_site_tbl.COUNT > 0 ) THEN
894:
895: -- loop through p_site_tbl for all contacts
896:
897: FOR i IN p_site_tbl.FIRST..p_site_tbl.LAST LOOP
904: l2_cust_account_id := l_cust_account_id;
905: l2_party_id := l_party_id;
906: l2_org_contact_id := l_org_contact_id;
907:
908: if (nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
909: OR nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM )
910: then
911: l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
912: l2_party_id := p_site_tbl(i).p_party_id;
905: l2_party_id := l_party_id;
906: l2_org_contact_id := l_org_contact_id;
907:
908: if (nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
909: OR nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM )
910: then
911: l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
912: l2_party_id := p_site_tbl(i).p_party_id;
913: oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
915: end if;
916:
917: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
918: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
919: if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
920: = FND_API.G_MISS_NUM) THEN
921: l_cust_account_role_id:=null;
922: else
923: l_cust_account_role_id:=p_site_tbl(i).p_cust_account_role_id;
916:
917: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
918: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
919: if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
920: = FND_API.G_MISS_NUM) THEN
921: l_cust_account_role_id:=null;
922: else
923: l_cust_account_role_id:=p_site_tbl(i).p_cust_account_role_id;
924: end if;
924: end if;
925: oe_debug_pub.add( 'AAC: After Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
926:
927:
928: if nvl(p_site_tbl(i).p_org_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
929: l2_org_contact_id := p_site_tbl(i).p_org_contact_id;
930: end if;
931:
932: --{added for bug 4240715
931:
932: --{added for bug 4240715
933: if p_site_tbl(i).p_site_use_code ='END_CUST' then /* added check for end customer */
934: l_site_use_code := 'SOLD';
935: if nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM then
936: l_cust_account_role_id:=null;
937: else
938: l_cust_account_role_id :=p_site_tbl(i).p_cust_account_role_id;
939: end if;
965: oe_debug_pub.add( 'AAC: ..done calling check_and_create_contacts ' ) ;
966: oe_debug_pub.add( 'count contact table = '||P2_CONTACT_TBL.COUNT ) ;
967: END IF;
968:
969: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
970: IF l_debug_level > 0 THEN
971: oe_debug_pub.add( 'contact with error' ) ;
972: END IF;
973:
970: IF l_debug_level > 0 THEN
971: oe_debug_pub.add( 'contact with error' ) ;
972: END IF;
973:
974: l_contact_status := FND_API.G_RET_STS_ERROR;
975: x_msg_data_contact := x_msg_data;
976: x_msg_count_contact := x_msg_count;
977: END IF;
978:
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:
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
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;
1050:
1051: -- we may not have to check the success status as data may need to be
1052: -- commited even if some txn failed.
1053: -- however in telesales we will never commit so it is fine
1054: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1055: IF p_control_rec.p_commit THEN
1056: IF l_debug_level > 0 THEN
1057: oe_debug_pub.add( 'commiting the txn' ) ;
1058: end if;
1078: -- since in the client messages are displayed if only status is error
1079: IF l_debug_level > 0 THEN
1080: oe_debug_pub.add( ' checking contact failure status = '||l_contact_status ) ;
1081: END IF;
1082: IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1083: IF l_debug_level > 0 THEN
1084: oe_debug_pub.add( ' returning error for contact' ) ;
1085: END IF;
1086: x_return_status := FND_API.G_RET_STS_ERROR;
1082: IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1083: IF l_debug_level > 0 THEN
1084: oe_debug_pub.add( ' returning error for contact' ) ;
1085: END IF;
1086: x_return_status := FND_API.G_RET_STS_ERROR;
1087:
1088: -- If site has failed then count and get above will catch that
1089: -- else to find the error message for contact we do this
1090: IF NOT l_site_failed then
1099: oe_debug_pub.add( 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
1100:
1101: EXCEPTION
1102:
1103: WHEN FND_API.G_EXC_ERROR THEN
1104:
1105: IF c_check_account%ISOPEN THEN
1106: CLOSE c_check_account;
1107: END IF;
1111: IF c_cust_account_id%ISOPEN THEN
1112: CLOSE c_cust_account_id;
1113: END IF;
1114:
1115: x_return_status := FND_API.G_RET_STS_ERROR;
1116:
1117: -- Get message count and data
1118:
1119: OE_MSG_PUB.Count_And_Get
1120: ( p_count => x_msg_count
1121: , p_data => x_msg_data
1122: );
1123:
1124: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1125:
1126:
1127: IF c_get_cust_account_id%ISOPEN THEN
1128: CLOSE c_get_cust_account_id;
1130: IF c_cust_account_id%ISOPEN THEN
1131: CLOSE c_cust_account_id;
1132: END IF;
1133:
1134: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1135:
1136: -- Get message count and data
1137:
1138: OE_MSG_PUB.Count_And_Get
1149: IF c_cust_account_id%ISOPEN THEN
1150: CLOSE c_cust_account_id;
1151: END IF;
1152:
1153: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1154: IF l_debug_level > 0 THEN
1155: oe_debug_pub.add( 'CREATE_ACCOUNT_LAYER WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
1156: END IF;
1157:
1298: px_party_id := p_party_id;
1299: px_cust_account_id := p_cust_account_id;
1300:
1301: --l_debug_level:=1;
1302: x_return_status := FND_API.G_RET_STS_SUCCESS;
1303: l_return_status := FND_API.G_RET_STS_SUCCESS;
1304:
1305: IF l_debug_level > 0 THEN
1306: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1299: px_cust_account_id := p_cust_account_id;
1300:
1301: --l_debug_level:=1;
1302: x_return_status := FND_API.G_RET_STS_SUCCESS;
1303: l_return_status := FND_API.G_RET_STS_SUCCESS;
1304:
1305: IF l_debug_level > 0 THEN
1306: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1307: oe_debug_pub.add(' party_id = '||p_party_id);
1308: oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1309: END IF;
1310:
1311: IF p_cust_account_id is null then
1312: x_return_status := FND_API.G_RET_STS_ERROR;
1313: IF l_debug_level > 0 THEN
1314: oe_debug_pub.add( ' ERROR: cust account id must be sent to check sites' ) ;
1315: END IF;
1316:
1333: l_send_primary := null;
1334: l_primary_site_use := null;
1335: l_end_customer_passed :='N'; --added for bug 4240715
1336:
1337: x_return_status := FND_API.G_RET_STS_SUCCESS;
1338: IF l_debug_level > 0 THEN
1339: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1340: oe_debug_pub.add(' site table rec #'||i);
1341: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1355: l_end_customer_passed :='Y';
1356: end if;
1357: --bug 4240715}
1358:
1359: if nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM OR
1360: nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1361: then
1362: px_cust_account_id := p_site_tbl(i).p_cust_account_id;
1363: px_party_id := p_site_tbl(i).p_party_id;
1356: end if;
1357: --bug 4240715}
1358:
1359: if nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM OR
1360: nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1361: then
1362: px_cust_account_id := p_site_tbl(i).p_cust_account_id;
1363: px_party_id := p_site_tbl(i).p_party_id;
1364: end if;
1369: oe_debug_pub.add( ' site level customer: account_id:'||px_cust_account_id ) ;
1370: END IF;
1371:
1372:
1373: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1375: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1376: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1370: END IF;
1371:
1372:
1373: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1375: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1376: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1371:
1372:
1373: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1375: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1376: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1372:
1373: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1375: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1376: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1373: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1375: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1376: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1374: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1375: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1376: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1376: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1384: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1384: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1385: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1378: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1384: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1385: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1386: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1379: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1384: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1385: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1386: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1387: then
1380: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1384: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1385: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1386: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1387: then
1388: --nothing to do! return!
1381: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1384: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1385: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1386: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1387: then
1388: --nothing to do! return!
1389: IF l_debug_level > 0 THEN
1382: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1384: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1385: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1386: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1387: then
1388: --nothing to do! return!
1389: IF l_debug_level > 0 THEN
1390: oe_debug_pub.add ('AAC: create_sites: no data passed in, likely a contact search; returning');
1395:
1396: IF p_site_tbl(i).p_site_use_code IS NOT NULL THEN
1397: l_site_use_code := p_site_tbl(i).p_site_use_code;
1398: ELSE
1399: l_return_status := FND_API.G_RET_STS_ERROR;
1400: IF l_debug_level > 0 THEN
1401: oe_debug_pub.add( 'site use not specified' ) ;
1402: END IF;
1403: FND_MESSAGE.Set_Name('ONT','ONT_AAC_ERROR');
1439: l_site_use_id := p_site_tbl(i).p_site_use_id;
1440: px_cust_account_id := p_cust_account_id;
1441: px_party_id := p_party_id;
1442: if l_status <> 'A' then
1443: l_return_status := fnd_api.g_ret_sts_error;
1444: IF l_debug_level > 0 THEN
1445: oe_debug_pub.add( 'account site use is inactive' ) ;
1446: END IF;
1447: end if;
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;
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;
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;
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; */
1470: l_site_use_id := p_site_tbl(i).p_site_use_id;
1471: px_cust_account_id := p_cust_account_id;
1472: px_party_id := p_party_id;
1473: if l_status <> 'A' then
1474: l_return_status := fnd_api.g_ret_sts_error;
1475: IF l_debug_level > 0 THEN
1476: oe_debug_pub.add( 'account site use is inactive' ) ;
1477: END IF;
1478: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
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;
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');
1505: l_site_use_id := p_site_tbl(i).p_site_use_id;
1506: px_cust_account_id := p_cust_account_id;
1507: px_party_id := p_party_id;
1508: if l_status <> 'A' then
1509: l_return_status := fnd_api.g_ret_sts_error;
1510: IF l_debug_level > 0 THEN
1511: oe_debug_pub.add( 'account site use is inactive' ) ;
1512: END IF;
1513: FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
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;
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');
1540: l_site_use_id := p_site_tbl(i).p_site_use_id;
1541: px_cust_account_id := p_cust_account_id;
1542: px_party_id := p_party_id;
1543: if l_status <> 'A' then
1544: l_return_status := fnd_api.g_ret_sts_error;
1545: IF l_debug_level > 0 THEN
1546: oe_debug_pub.add( 'account site use is inactive' ) ;
1547: END IF;
1548: FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
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;
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');
1583: oe_debug_pub.add(' primary = '||l_primary_site_use|| ' cust_account_id = '||l_cust_account_id ) ;
1584: end if;
1585:
1586: if l_status <> 'A' then
1587: l_return_status := fnd_api.g_ret_sts_error;
1588: IF l_debug_level > 0 THEN
1589: oe_debug_pub.add( 'account site use is inactive' ) ;
1590: END IF;
1591:
1601: END IF;
1602:
1603: oe_debug_pub.add( 'px_accoun_id:'||px_cust_account_id||'l_accoun_id:'||l_cust_account_id ) ;
1604: /* IF l_cust_account_id <> px_cust_account_id then
1605: x_return_status := FND_API.G_RET_STS_ERROR;
1606: l_return_status := FND_API.G_RET_STS_ERROR;
1607: IF l_debug_level > 0 THEN
1608: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1609: END IF;
1602:
1603: oe_debug_pub.add( 'px_accoun_id:'||px_cust_account_id||'l_accoun_id:'||l_cust_account_id ) ;
1604: /* IF l_cust_account_id <> px_cust_account_id then
1605: x_return_status := FND_API.G_RET_STS_ERROR;
1606: l_return_status := FND_API.G_RET_STS_ERROR;
1607: IF l_debug_level > 0 THEN
1608: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1609: END IF;
1610:
1622:
1623: p_site_tbl(i).p_cust_acct_site_id := l_cust_acct_site_id;
1624:
1625: ELSIF c_site_use%NOTFOUND THEN
1626: x_return_status := FND_API.G_RET_STS_ERROR;
1627: l_return_status := FND_API.G_RET_STS_ERROR;
1628: IF l_debug_level > 0 THEN
1629: oe_debug_pub.add( 'INVALID SITE_USE_ID ' ) ;
1630: END IF;
1623: p_site_tbl(i).p_cust_acct_site_id := l_cust_acct_site_id;
1624:
1625: ELSIF c_site_use%NOTFOUND THEN
1626: x_return_status := FND_API.G_RET_STS_ERROR;
1627: l_return_status := FND_API.G_RET_STS_ERROR;
1628: IF l_debug_level > 0 THEN
1629: oe_debug_pub.add( 'INVALID SITE_USE_ID ' ) ;
1630: END IF;
1631:
1706: we have to use site_use_code and party_site_id
1707: */
1708: -- if party_site_id is sent
1709: IF l_party_site_id is null AND
1710: x_return_status <> FND_API.G_RET_STS_ERROR then
1711: oe_debug_pub.add( 'l_party_site_id is null and p_site_tbl(i).p_party_site_id is '||p_site_tbl(i).p_party_site_id);
1712: IF p_site_tbl(i).p_party_site_id is not null then
1713: IF l_debug_level > 0 THEN
1714: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1721: INTO l_party_id,
1722: l_status;
1723:
1724: IF c_party_site%NOTFOUND THEN
1725: l_return_status := FND_API.G_RET_STS_ERROR;
1726: x_return_status := FND_API.G_RET_STS_ERROR;
1727: IF l_debug_level > 0 THEN
1728: oe_debug_pub.add( 'no such party site id' ) ;
1729: END IF;
1722: l_status;
1723:
1724: IF c_party_site%NOTFOUND THEN
1725: l_return_status := FND_API.G_RET_STS_ERROR;
1726: x_return_status := FND_API.G_RET_STS_ERROR;
1727: IF l_debug_level > 0 THEN
1728: oe_debug_pub.add( 'no such party site id' ) ;
1729: END IF;
1730: CLOSE c_party_site;
1755: END IF;
1756:
1757: IF l_status <> 'A' THEN
1758:
1759: x_return_status := FND_API.G_RET_STS_ERROR;
1760: l_return_status := FND_API.G_RET_STS_ERROR;
1761:
1762: IF l_site_use_code = 'SHIP_TO' then
1763: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1756:
1757: IF l_status <> 'A' THEN
1758:
1759: x_return_status := FND_API.G_RET_STS_ERROR;
1760: l_return_status := FND_API.G_RET_STS_ERROR;
1761:
1762: IF l_site_use_code = 'SHIP_TO' then
1763: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1764: ELSIF l_site_use_code = 'BILL_TO' then
1786:
1787: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1788:
1789: IF px_party_id <> l_party_id THEN
1790: x_return_status := FND_API.G_RET_STS_ERROR;
1791: l_return_status := FND_API.G_RET_STS_ERROR;
1792:
1793: IF l_site_use_code = 'SHIP_TO' then
1794: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1787: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1788:
1789: IF px_party_id <> l_party_id THEN
1790: x_return_status := FND_API.G_RET_STS_ERROR;
1791: l_return_status := FND_API.G_RET_STS_ERROR;
1792:
1793: IF l_site_use_code = 'SHIP_TO' then
1794: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1795: ELSIF l_site_use_code = 'BILL_TO' then
1823:
1824: ELSE -- if party_site_id is null
1825: oe_debug_pub.add( 'Yes.. Party_Site_id is not null');
1826: if l_end_customer_passed ='N' then -- if its not end customer(4240715)
1827: x_return_status := FND_API.G_RET_STS_ERROR;
1828: l_return_status := FND_API.G_RET_STS_ERROR;
1829: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1830: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
1831: OE_MSG_PUB.ADD;
1824: ELSE -- if party_site_id is null
1825: oe_debug_pub.add( 'Yes.. Party_Site_id is not null');
1826: if l_end_customer_passed ='N' then -- if its not end customer(4240715)
1827: x_return_status := FND_API.G_RET_STS_ERROR;
1828: l_return_status := FND_API.G_RET_STS_ERROR;
1829: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1830: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
1831: OE_MSG_PUB.ADD;
1832: IF l_debug_level > 0 THEN
1867: l_party_site_id,
1868: l_primary_per_type;
1869:
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;
1868: l_primary_per_type;
1869:
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:
1913: END IF; -- if party_site_use_id is not null
1914:
1915: -- if party_site_id is sent
1916: IF l_party_site_id is null AND
1917: x_return_status <> FND_API.G_RET_STS_ERROR then
1918: IF p_site_tbl(i).p_party_site_id is not null then
1919: IF l_debug_level > 0 THEN
1920: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1921: END IF;
1925: FETCH c_party_site
1926: INTO l_party_id,
1927: l_status;
1928: IF c_party_site%NOTFOUND THEN
1929: l_return_status := FND_API.G_RET_STS_ERROR;
1930: x_return_status := FND_API.G_RET_STS_ERROR;
1931: IF l_debug_level > 0 THEN
1932: oe_debug_pub.add( 'no such party site id' ) ;
1933: END IF;
1926: INTO l_party_id,
1927: l_status;
1928: IF c_party_site%NOTFOUND THEN
1929: l_return_status := FND_API.G_RET_STS_ERROR;
1930: x_return_status := FND_API.G_RET_STS_ERROR;
1931: IF l_debug_level > 0 THEN
1932: oe_debug_pub.add( 'no such party site id' ) ;
1933: END IF;
1934: CLOSE c_party_site;
1957: oe_debug_pub.add( 'found for party_site_id ' ) ;
1958: END IF;
1959: IF l_status <> 'A' THEN
1960:
1961: x_return_status := FND_API.G_RET_STS_ERROR;
1962: l_return_status := FND_API.G_RET_STS_ERROR;
1963:
1964: IF l_site_use_code = 'SHIP_TO' then
1965: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1958: END IF;
1959: IF l_status <> 'A' THEN
1960:
1961: x_return_status := FND_API.G_RET_STS_ERROR;
1962: l_return_status := FND_API.G_RET_STS_ERROR;
1963:
1964: IF l_site_use_code = 'SHIP_TO' then
1965: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1966: ELSIF l_site_use_code = 'BILL_TO' then
1988:
1989: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1990:
1991: IF px_party_id <> l_party_id THEN
1992: x_return_status := FND_API.G_RET_STS_ERROR;
1993: l_return_status := FND_API.G_RET_STS_ERROR;
1994:
1995: IF l_site_use_code = 'SHIP_TO' then
1996: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1989: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1990:
1991: IF px_party_id <> l_party_id THEN
1992: x_return_status := FND_API.G_RET_STS_ERROR;
1993: l_return_status := FND_API.G_RET_STS_ERROR;
1994:
1995: IF l_site_use_code = 'SHIP_TO' then
1996: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1997: ELSIF l_site_use_code = 'BILL_TO' then
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');
2031: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
2032: OE_MSG_PUB.ADD;
2025: ELSE -- if party_site_id is null
2026: oe_debug_pub.add( 'Yes.. Party_Site_id is not null.. Second');
2027: if l_end_customer_passed ='N' then -- if its not end customer(bug 4240715)
2028: x_return_status := FND_API.G_RET_STS_ERROR;
2029: l_return_status := FND_API.G_RET_STS_ERROR;
2030: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
2031: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
2032: OE_MSG_PUB.ADD;
2033: IF l_debug_level > 0 THEN
2109:
2110: <
2111:
2112:
2113: IF x_return_status = FND_API.G_RET_STS_SUCCESS and l_site_use_code <>'END_CUST' THEN
2114:
2115:
2116: IF l_debug_level > 0 THEN
2117: oe_debug_pub.add( ' continuing processing' ) ;
2177: c_attribute25=>null,
2178: in_created_by_module=>G_CREATED_BY_MODULE,
2179: in_primary_flag =>l_send_primary
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;
2189:
2190: x_return_status := FND_API.G_RET_STS_ERROR;
2191: l_return_status := FND_API.G_RET_STS_ERROR;
2192:
2193: IF x_msg_count = 1 then
2194:
2187: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2188: END IF;
2189:
2190: x_return_status := FND_API.G_RET_STS_ERROR;
2191: l_return_status := FND_API.G_RET_STS_ERROR;
2192:
2193: IF x_msg_count = 1 then
2194:
2195: IF l_site_use_code = 'SHIP_TO' then
2212: return;
2213: END IF;
2214: END IF;
2215: ELSE
2216: x_return_status := FND_API.G_RET_STS_ERROR;
2217: l_return_status := FND_API.G_RET_STS_ERROR;
2218: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2219: OE_MSG_PUB.ADD;
2220: IF l_debug_level > 0 THEN
2213: END IF;
2214: END IF;
2215: ELSE
2216: x_return_status := FND_API.G_RET_STS_ERROR;
2217: l_return_status := FND_API.G_RET_STS_ERROR;
2218: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2219: OE_MSG_PUB.ADD;
2220: IF l_debug_level > 0 THEN
2221: oe_debug_pub.add( ' not authorized to create site' ) ;
2271: x_msg_count => x_msg_count,
2272: x_msg_data => x_msg_data,
2273: in_created_by_module=>G_CREATED_BY_MODULE
2274: ) ;
2275: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2276: CLOSE c_acct_site;
2277: l_return_status := FND_API.G_RET_STS_ERROR;
2278: IF l_debug_level > 0 THEN
2279: oe_debug_pub.add( ' account site creation failed' ) ;
2273: in_created_by_module=>G_CREATED_BY_MODULE
2274: ) ;
2275: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2276: CLOSE c_acct_site;
2277: l_return_status := FND_API.G_RET_STS_ERROR;
2278: IF l_debug_level > 0 THEN
2279: oe_debug_pub.add( ' account site creation failed' ) ;
2280: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2281: END IF;
2343: in_created_by_module=>G_CREATED_BY_MODULE,
2344: in_primary_flag =>l_send_primary
2345: );
2346:
2347: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2348: l_return_status := FND_API.G_RET_STS_ERROR;
2349: CLOSE c_acct_site;
2350: IF l_debug_level > 0 THEN
2351: oe_debug_pub.add( ' account site use creation failed' ) ;
2344: in_primary_flag =>l_send_primary
2345: );
2346:
2347: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2348: l_return_status := FND_API.G_RET_STS_ERROR;
2349: CLOSE c_acct_site;
2350: IF l_debug_level > 0 THEN
2351: oe_debug_pub.add( ' account site use creation failed' ) ;
2352: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2383: END IF; -- if acct site creation succeeded
2384:
2385: ELSE
2386:
2387: x_return_status := FND_API.G_RET_STS_ERROR;
2388: l_return_status := FND_API.G_RET_STS_ERROR;
2389: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2390: OE_MSG_PUB.ADD;
2391: IF l_debug_level > 0 THEN
2384:
2385: ELSE
2386:
2387: x_return_status := FND_API.G_RET_STS_ERROR;
2388: l_return_status := FND_API.G_RET_STS_ERROR;
2389: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2390: OE_MSG_PUB.ADD;
2391: IF l_debug_level > 0 THEN
2392: oe_debug_pub.add( '2 not allowed to create site' ) ;
2421:
2422:
2423: /*cc project*/
2424:
2425: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2426:
2427: IF l_debug_level > 0 THEN
2428: oe_debug_pub.add( ' continuing processing' ) ;
2429: end if;
2488: c_attribute25=>null,
2489: in_created_by_module=>G_CREATED_BY_MODULE,
2490: in_primary_flag =>l_send_primary
2491: );
2492: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2493: CLOSE c_acct_site;
2494: IF l_debug_level > 0 THEN
2495: oe_debug_pub.add( ' account site use creation failed' ) ;
2496: END IF;
2497: IF l_debug_level > 0 THEN
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;
2503:
2504: IF x_msg_count = 1 then
2505:
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;
2503:
2504: IF x_msg_count = 1 then
2505:
2506: IF l_site_use_code = 'SHIP_TO' then
2523: return;
2524: END IF;
2525: END IF;
2526: ELSE
2527: x_return_status := FND_API.G_RET_STS_ERROR;
2528: l_return_status := FND_API.G_RET_STS_ERROR;
2529: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2530: OE_MSG_PUB.ADD;
2531: IF l_debug_level > 0 THEN
2524: END IF;
2525: END IF;
2526: ELSE
2527: x_return_status := FND_API.G_RET_STS_ERROR;
2528: l_return_status := FND_API.G_RET_STS_ERROR;
2529: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2530: OE_MSG_PUB.ADD;
2531: IF l_debug_level > 0 THEN
2532: oe_debug_pub.add( ' not authorized to create site' ) ;
2582: x_msg_count => x_msg_count,
2583: x_msg_data => x_msg_data,
2584: in_created_by_module=>G_CREATED_BY_MODULE
2585: ) ;
2586: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2587: CLOSE c_acct_site;
2588: l_return_status := FND_API.G_RET_STS_ERROR;
2589: IF l_debug_level > 0 THEN
2590: oe_debug_pub.add( ' account site creation failed' ) ;
2584: in_created_by_module=>G_CREATED_BY_MODULE
2585: ) ;
2586: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2587: CLOSE c_acct_site;
2588: l_return_status := FND_API.G_RET_STS_ERROR;
2589: IF l_debug_level > 0 THEN
2590: oe_debug_pub.add( ' account site creation failed' ) ;
2591: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2592: END IF;
2654: in_created_by_module=>G_CREATED_BY_MODULE,
2655: in_primary_flag =>l_send_primary
2656: );
2657:
2658: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2659: l_return_status := FND_API.G_RET_STS_ERROR;
2660: CLOSE c_acct_site;
2661: IF l_debug_level > 0 THEN
2662: oe_debug_pub.add( ' account site use creation failed' ) ;
2655: in_primary_flag =>l_send_primary
2656: );
2657:
2658: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2659: l_return_status := FND_API.G_RET_STS_ERROR;
2660: CLOSE c_acct_site;
2661: IF l_debug_level > 0 THEN
2662: oe_debug_pub.add( ' account site use creation failed' ) ;
2663: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2694: END IF; -- if acct site creation succeeded
2695:
2696: ELSE
2697:
2698: x_return_status := FND_API.G_RET_STS_ERROR;
2699: l_return_status := FND_API.G_RET_STS_ERROR;
2700: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2701: OE_MSG_PUB.ADD;
2702: IF l_debug_level > 0 THEN
2695:
2696: ELSE
2697:
2698: x_return_status := FND_API.G_RET_STS_ERROR;
2699: l_return_status := FND_API.G_RET_STS_ERROR;
2700: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2701: OE_MSG_PUB.ADD;
2702: IF l_debug_level > 0 THEN
2703: oe_debug_pub.add( '2 not allowed to create site' ) ;
2724: END IF;
2725: END IF; -- if site_use_id is passed
2726:
2727: <
2728: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2729:
2730:
2731:
2732: IF l_debug_level > 0 THEN
2764:
2765:
2766: x_return_status := l_return_status;
2767:
2768: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2769: IF l_debug_level > 0 THEN
2770: oe_debug_pub.add( ' doing count and get for site' ) ;
2771: END IF;
2772: OE_MSG_PUB.Count_And_Get
2779: END IF;
2780:
2781: EXCEPTION
2782:
2783: WHEN FND_API.G_EXC_ERROR THEN
2784:
2785: IF c_party_site_use%ISOPEN THEN
2786: CLOSE c_party_site_use;
2787: END IF;
2790: END IF;
2791: IF c_acct_site%ISOPEN THEN
2792: CLOSE c_acct_site;
2793: END IF;
2794: x_return_status := FND_API.G_RET_STS_ERROR;
2795:
2796: -- Get message count and data
2797:
2798: OE_MSG_PUB.Count_And_Get
2799: ( p_count => x_msg_count
2800: , p_data => x_msg_data
2801: );
2802:
2803: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2804:
2805:
2806: IF c_party_site_use%ISOPEN THEN
2807: CLOSE c_party_site_use;
2811: END IF;
2812: IF c_acct_site%ISOPEN THEN
2813: CLOSE c_acct_site;
2814: END IF;
2815: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2816:
2817: -- Get message count and data
2818:
2819: OE_MSG_PUB.Count_And_Get
2832: END IF;
2833: IF c_acct_site%ISOPEN THEN
2834: CLOSE c_acct_site;
2835: END IF;
2836: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2837: IF l_debug_level > 0 THEN
2838: oe_debug_pub.add( 'check_and_create_site when other code = '|| sqlcode||' message = '||sqlerrm ) ;
2839: END IF;
2840:
3014: END IF;
3015: END IF;
3016:
3017:
3018: x_return_status := FND_API.G_RET_STS_SUCCESS;
3019: p_contact_tbl.DELETE;
3020: p_multiple_account := FALSE;
3021:
3022:
3022:
3023: -- atleast some kind of account information is needed
3024: IF p_cust_account_id is null AND
3025: p_cust_account_role_id is null then
3026: x_return_status := FND_API.G_RET_STS_ERROR;
3027: IF l_debug_level > 0 THEN
3028: oe_debug_pub.add(l_indent|| ' not a valid call to check contact' ) ;
3029: END IF;
3030: return;
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');
3118: IF l_debug_level > 0 THEN
3119: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3120: END IF;
3121: CLOSE c_cust_role_site;
3122: x_return_status := FND_API.G_RET_STS_ERROR;
3123: return;
3124:
3125: END IF;
3126:
3128: IF l_debug_level > 0 THEN
3129: oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3130: END IF;
3131: CLOSE c_cust_role_site;
3132: x_return_status := FND_API.G_RET_STS_ERROR;
3133: return;
3134:
3135: END IF;
3136:
3138: IF l_debug_level > 0 THEN
3139: oe_debug_pub.add(l_indent|| 'acct role does not belong to this acct' ) ;
3140: END IF;
3141: CLOSE c_cust_role_site;
3142: x_return_status := FND_API.G_RET_STS_ERROR;
3143: return;
3144: END IF;
3145:
3146:
3148: IF l_debug_level > 0 THEN
3149: oe_debug_pub.add( l_indent|| 'Cust account role for site is not valid' ) ;
3150: END IF;
3151: CLOSE c_cust_role_site;
3152: x_return_status := FND_API.G_RET_STS_ERROR;
3153: return;
3154:
3155: END IF;
3156:
3177: IF c_cust_account_role%NOTFOUND then
3178:
3179: CLOSE c_cust_account_role;
3180:
3181: x_return_status := FND_API.G_RET_STS_ERROR;
3182: IF l_debug_level > 0 THEN
3183: oe_debug_pub.add(l_indent|| '2 contact_id not found' ) ;
3184: END IF;
3185: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3196: IF l_debug_level > 0 THEN
3197: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3198: END IF;
3199: CLOSE c_cust_account_role;
3200: x_return_status := FND_API.G_RET_STS_ERROR;
3201: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INACTIVE_CONTACT');
3202: OE_MSG_PUB.ADD;
3203: OE_MSG_PUB.Count_And_Get
3204: ( p_count => x_msg_count
3212: IF l_debug_level > 0 THEN
3213: oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3214: END IF;
3215: CLOSE c_cust_account_role;
3216: x_return_status := FND_API.G_RET_STS_ERROR;
3217: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ROLE_CONTACT');
3218: OE_MSG_PUB.ADD;
3219: OE_MSG_PUB.Count_And_Get
3220: ( p_count => x_msg_count
3229: IF l_debug_level > 0 THEN
3230: oe_debug_pub.add( l_indent|| 'Account role does not belong to this acct' ) ;
3231: END IF;
3232: CLOSE c_cust_account_role;
3233: x_return_status := FND_API.G_RET_STS_ERROR;
3234: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_ACCOUNT');
3235: OE_MSG_PUB.ADD;
3236: OE_MSG_PUB.Count_And_Get
3237: ( p_count => x_msg_count
3270: IF c_check_org_contact%NOTFOUND then
3271: IF l_debug_level > 0 THEN
3272: oe_debug_pub.add(l_indent|| 'org contact_id does not belong to sent party or '|| ' contact may be inactive' ) ;
3273: END IF;
3274: x_return_status := FND_API.G_RET_STS_ERROR;
3275: CLOSE c_check_org_contact;
3276: FND_MESSAGE.Set_Name('ONT','ONT_AAC_CONTACT_ACCOUNT');
3277: OE_MSG_PUB.ADD;
3278: OE_MSG_PUB.Count_And_Get
3322: IF p_contact_tbl.COUNT > 1 then
3323: IF l_debug_level > 0 THEN
3324: oe_debug_pub.add(l_indent|| 'error >1 contact for org_contact_id' ) ;
3325: END IF;
3326: x_return_status := FND_API.G_RET_STS_ERROR;
3327: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3328: FND_MESSAGE.Set_Token('TEXT',' Multiple Contacts ', FALSE);
3329: OE_MSG_PUB.ADD;
3330: OE_MSG_PUB.Count_And_Get
3339: IF p_contact_tbl.COUNT = 1 then
3340: IF l_debug_level > 0 THEN
3341: oe_debug_pub.add( l_indent|| 'RETURNING WITH ONE CONTACT' ) ;
3342: END IF;
3343: x_return_status := FND_API.G_RET_STS_SUCCESS;
3344: return;
3345: ELSIF p_contact_tbl.COUNT = 0 then
3346: IF l_debug_level > 0 THEN
3347: oe_debug_pub.add( l_indent|| 'no contact for account found. creating '|| ' acct_id = '||p_cust_account_id|| ' rel_party_id = '||L_REL_PARTY_ID ) ;
3406: c2_attribute20=>null,
3407: in_Created_by_module=>G_CREATED_BY_MODULE
3408: );
3409:
3410: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3411:
3412: x_return_status := l_return_status;
3413: x_msg_data := l_msg_data;
3414: x_msg_count := l_msg_count;
3430: , p_data => x_msg_data
3431: );
3432: return;
3433: ELSE
3434: x_return_status := FND_API.G_RET_STS_SUCCESS;
3435: IF l_debug_level > 0 THEN
3436: oe_debug_pub.add( l_indent|| ' contact creation succeeded '|| ' cust acct_role_id = '||P_CONTACT_TBL ( 1 ) ) ;
3437: END IF;
3438:
3437: END IF;
3438:
3439: END IF;
3440: ELSE
3441: x_return_status := FND_API.G_RET_STS_ERROR;
3442: IF l_debug_level > 0 THEN
3443: oe_debug_pub.add( l_indent|| 'not allowed to create contact' ) ;
3444: end IF;
3445: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_PERMISSION');
3464: IF p_contact_tbl.COUNT = 0 then
3465: IF l_debug_level > 0 THEN
3466: oe_debug_pub.add( l_indent|| 'equal to ZERO' ) ;
3467: END IF;
3468: x_return_status := FND_API.G_RET_STS_SUCCESS;
3469: return;
3470:
3471: -- if one or more then check the account contact appropiately
3472: -- take care of account site and responsibility
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
3502:
3503:
3504: EXCEPTION
3505:
3506: WHEN FND_API.G_EXC_ERROR THEN
3507:
3508: x_return_status := FND_API.G_RET_STS_ERROR;
3509:
3510: -- Get message count and data
3504: EXCEPTION
3505:
3506: WHEN FND_API.G_EXC_ERROR THEN
3507:
3508: x_return_status := FND_API.G_RET_STS_ERROR;
3509:
3510: -- Get message count and data
3511:
3512: OE_MSG_PUB.Count_And_Get
3513: ( p_count => x_msg_count
3514: , p_data => x_msg_data
3515: );
3516:
3517: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3518:
3519: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3520:
3521: -- Get message count and data
3515: );
3516:
3517: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3518:
3519: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3520:
3521: -- Get message count and data
3522:
3523: OE_MSG_PUB.Count_And_Get
3526: );
3527:
3528: WHEN OTHERS THEN
3529:
3530: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3531: IF l_debug_level > 0 THEN
3532: oe_debug_pub.add( 'CHECK_CONTACT WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
3533: END IF;
3534:
3610:
3611: --
3612: BEGIN
3613:
3614: x_return_status := FND_API.G_RET_STS_SUCCESS;
3615: p_account_tbl.DELETE;
3616:
3617: IF l_debug_level > 0 THEN
3618: oe_debug_pub.add(l_indent||'party_id = '||p_party_id|| '; party_number = '||p_party_number);
3649: INTO l_party_id,
3650: l_party_type;
3651:
3652: IF party_number_rec%NOTFOUND THEN
3653: x_return_status := FND_API.G_RET_STS_ERROR;
3654: IF l_debug_level > 0 THEN
3655: oe_debug_pub.add(l_indent|| 'no such party found for party_number' ) ;
3656: END IF;
3657: CLOSE party_number_rec;
3667: FETCH party_rec
3668: INTO l_party_type;
3669:
3670: IF party_rec%NOTFOUND THEN
3671: x_return_status := FND_API.G_RET_STS_ERROR;
3672: IF l_debug_level > 0 THEN
3673: oe_debug_pub.add(l_indent|| 'no such party found for party_id' ) ;
3674: END IF;
3675: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_PARTY');
3716: IF C_get_cust_id_from_party_id%ROWCOUNT > 1 then
3717: IF l_debug_level > 0 THEN
3718: oe_debug_pub.add( l_indent|| 'more than one account for party id' ) ;
3719: END IF;
3720: x_return_status := FND_API.G_RET_STS_ERROR;
3721: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3722: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
3723: OE_MSG_PUB.ADD;
3724: EXIT;
3749:
3750:
3751: ELSE -- if not person or organization
3752:
3753: x_return_status := FND_API.G_RET_STS_ERROR;
3754: IF l_debug_level > 0 THEN
3755: oe_debug_pub.add(l_indent|| ' invalid party type' ) ;
3756: end IF;
3757: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_PARTY');
3771: IF l_debug_level > 0 THEN
3772: oe_debug_pub.add(l_indent|| 'before checking to create cust account' ) ;
3773: END IF;
3774: -- create customer account
3775: IF x_return_status <> FND_API.G_RET_STS_ERROR AND
3776: NOT p_multiple_account AND
3777: l_cust_account_id IS NULL THEN
3778:
3779: IF p_allow_account_creation THEN
3786: -- user should select it in the Add Customer form
3787: IF l_debug_level > 0 THEN
3788: oe_debug_pub.add( l_indent|| ' going to call add customer' ) ;
3789: END IF;
3790: x_return_status := FND_API.G_RET_STS_SUCCESS;
3791: return;
3792:
3793: ELSE
3794: IF l_party_id is not NULL THEN
3867: oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3868: oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3869: END IF;
3870:
3871: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3872: IF l_debug_level > 0 THEN
3873: oe_debug_pub.add( l_indent|| ' account creation failed' ) ;
3874: END IF;
3875: IF x_msg_count = 1 then
3900: then we have to open the add Customer Form to the user. So returning
3901: status as success instead of error.*/
3902:
3903: IF G_CREATED_BY_MODULE <> 'ONT_TELESERVICE_INTEGRATION' THEN
3904: x_return_status := FND_API.G_RET_STS_ERROR;
3905: IF l_debug_level > 0 THEN
3906: oe_debug_pub.add(l_indent|| 'not allowed to create account' ) ;
3907: END IF;
3908: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
3922:
3923: --oe_debug_pub.add('p_out_cust_account_id = '|| p_account_tbl(1));
3924:
3925: ELSE
3926: --x_return_status := FND_API.G_RET_STS_ERROR;
3927: FND_MESSAGE.Set_Name('ONT', 'ONT_AVAIL_GENERIC');
3928: IF l_debug_level > 0 THEN
3929: oe_debug_pub.add(l_indent|| 'party_id or party number is not passed' ) ;
3930: END IF;
3937:
3938: IF l_debug_level > 0 THEN
3939: oe_debug_pub.add(l_indent|| 'create account at the end' ) ;
3940: END IF;
3941: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3942: IF l_debug_level > 0 THEN
3943: oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3944: END IF;
3945: oe_msg_pub.count_and_get(p_encoded=>fnd_api.G_TRUE,
3941: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3942: IF l_debug_level > 0 THEN
3943: oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3944: END IF;
3945: oe_msg_pub.count_and_get(p_encoded=>fnd_api.G_TRUE,
3946: p_count => x_msg_count,
3947: p_data=>x_msg_data
3948: );
3949: IF l_debug_level > 0 THEN
3957: END IF;
3958:
3959: EXCEPTION
3960:
3961: WHEN FND_API.G_EXC_ERROR THEN
3962:
3963: IF C_get_cust_id_from_party_id%ISOPEN THEN
3964: CLOSE c_get_cust_id_from_party_id;
3965: END IF;
3969: IF party_number_rec%ISOPEN THEN
3970: CLOSE party_number_rec;
3971: END IF;
3972:
3973: x_return_status := FND_API.G_RET_STS_ERROR;
3974:
3975: -- Get message count and data
3976:
3977: OE_MSG_PUB.Count_And_Get
3978: ( p_count => x_msg_count
3979: , p_data => x_msg_data
3980: );
3981:
3982: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3983:
3984:
3985: IF C_get_cust_id_from_party_id%ISOPEN THEN
3986: CLOSE c_get_cust_id_from_party_id;
3991: IF party_number_rec%ISOPEN THEN
3992: CLOSE party_number_rec;
3993: END IF;
3994:
3995: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3996:
3997: -- Get message count and data
3998:
3999: OE_MSG_PUB.Count_And_Get
4013: IF party_number_rec%ISOPEN THEN
4014: CLOSE party_number_rec;
4015: END IF;
4016:
4017: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4018: IF l_debug_level > 0 THEN
4019: oe_debug_pub.add( 'CHECK_ACCOUNT WHEN OTHERS EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
4020: END IF;
4021:
4087: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4088: --
4089: BEGIN
4090:
4091: x_return_status := FND_API.G_RET_STS_SUCCESS;
4092: p_account_tbl.DELETE;
4093:
4094: IF l_debug_level > 0 THEN
4095: oe_debug_pub.add( ' PARTY_ID='||P_PARTY_ID|| ' PARTY_NUMBER ='||P_PARTY_NUMBER ) ;
4108: IF party_number_rec%NOTFOUND THEN
4109: IF l_debug_level > 0 THEN
4110: oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_NUMBER' ) ;
4111: END IF;
4112: x_return_status := FND_API.G_RET_STS_ERROR;
4113: FND_MESSAGE.Set_Name('ONT','ONT_AACC_NO_ACCOUNT');
4114: OE_MSG_PUB.ADD;
4115: CLOSE party_number_rec;
4116: return;
4126: IF party_rec%NOTFOUND THEN
4127: IF l_debug_level > 0 THEN
4128: oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_ID' ) ;
4129: END IF;
4130: x_return_status := FND_API.G_RET_STS_ERROR;
4131: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
4132: OE_MSG_PUB.ADD;
4133: CLOSE party_rec;
4134: return;
4181: ELSE
4182: IF l_debug_level > 0 THEN
4183: oe_debug_pub.add( 'INVALID PARTY TYPE' ) ;
4184: END IF;
4185: x_return_status := FND_API.G_RET_STS_ERROR;
4186: FND_MESSAGE.Set_Name('ONT','ONT_AAC_INVALID_PARTY');
4187: OE_MSG_PUB.ADD;
4188: return;
4189: END IF;
4191: ELSE
4192: IF l_debug_level > 0 THEN
4193: oe_debug_pub.add( 'NO PARTY INFORMATION SENT' ) ;
4194: END IF;
4195: x_return_status := FND_API.G_RET_STS_ERROR;
4196: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4197: FND_MESSAGE.Set_Token('TEXT','No Customer Information ', FALSE);
4198: OE_MSG_PUB.ADD;
4199: return;
4225: l_dummy4 boolean := false;
4226:
4227: BEGIN
4228:
4229: x_return_status := FND_API.G_RET_STS_SUCCESS;
4230:
4231: IF l_debug_level > 0 THEN
4232: oe_debug_pub.add ('AAC:VTI: starting Value-To-Id');
4233: oe_debug_pub.add ('AAC:VTI: looking for header level stuff');
4234: END IF;
4235:
4236:
4237: -- if both party_id and account_id are missing, call find_sold_to_id
4238: if (nvl(p_party_customer_rec.p_party_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4239: OR nvl(p_party_customer_rec.p_cust_account_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4240: then
4241:
4242: -- calling find_sold_to_id
4235:
4236:
4237: -- if both party_id and account_id are missing, call find_sold_to_id
4238: if (nvl(p_party_customer_rec.p_party_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4239: OR nvl(p_party_customer_rec.p_cust_account_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4240: then
4241:
4242: -- calling find_sold_to_id
4243: find_sold_to_id(
4254: ,p_process_site => l_dummy4
4255: ,x_return_status => x_return_status
4256: );
4257:
4258: IF x_return_status = FND_API.G_RET_STS_ERROR then
4259: -- not found a party_id/cust_account_id
4260: -- error message already logged inside find_sold_to_id
4261: IF l_debug_level > 0 THEN
4262: oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for header');
4271: oe_debug_pub.add ('AAC:VTI: looking for sold_to contact');
4272: END IF;
4273:
4274: -- if both org_contact_id and account_role_id are missing, call find_contact_id
4275: if (nvl(p_party_customer_rec.p_org_contact_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4276: and nvl(p_party_customer_rec.p_cust_account_role_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4277: then
4278: find_contact_id( p_contact_id => p_party_customer_rec.p_org_contact_id
4279: ,p_cust_contact_id => p_party_customer_rec.p_cust_account_role_id
4272: END IF;
4273:
4274: -- if both org_contact_id and account_role_id are missing, call find_contact_id
4275: if (nvl(p_party_customer_rec.p_org_contact_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4276: and nvl(p_party_customer_rec.p_cust_account_role_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4277: then
4278: find_contact_id( p_contact_id => p_party_customer_rec.p_org_contact_id
4279: ,p_cust_contact_id => p_party_customer_rec.p_cust_account_role_id
4280: ,p_contact_name => p_party_customer_rec.p_contact_name
4287: );
4288:
4289: end if;
4290:
4291: x_return_status := FND_API.G_RET_STS_SUCCESS;
4292:
4293: IF p_site_tbl.COUNT <> 0 then
4294: for i in p_site_tbl.FIRST..p_site_tbl.LAST loop
4295: -- do value-to-id for each site record
4315: ,p_process_site => p_site_tbl(i).p_process_site
4316: ,x_return_status => x_return_status
4317: );
4318:
4319: IF x_return_status = FND_API.G_RET_STS_ERROR then
4320: -- not found a party_id/cust_account_id
4321: -- error message already logged inside find_sold_to_id
4322: IF l_debug_level > 0 THEN
4323: oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for line#'||i);
4343: ,p_site_use_code => p_site_tbl(i).p_site_use_code --bug 4240715
4344: ,x_return_status => x_return_status
4345: );
4346:
4347: x_return_status := FND_API.G_RET_STS_SUCCESS;
4348: -- do site v-t-i
4349: oe_debug_pub.add('P2:'||p_site_tbl(i).p_site_use_id);
4350: find_site_id(
4351: p_site_use_id => p_site_tbl(i).p_party_site_use_id
4370: ,x_msg_count => x_msg_count
4371: ,x_msg_data => x_msg_data
4372: );
4373: oe_debug_pub.add('P3:'||p_site_tbl(i).p_site_use_id);
4374: IF x_return_status = FND_API.G_RET_STS_ERROR then
4375: -- not found a party_id/cust_account_id
4376: -- error message already logged inside find_sold_to_id
4377: IF l_debug_level > 0 THEN
4378: oe_debug_pub.add ('AAC:VTI:site_use_id account/party not found for line#'||1);
4415: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4416:
4417: BEGIN
4418:
4419: x_return_status := FND_API.G_RET_STS_SUCCESS;
4420:
4421: IF l_debug_level > 0 THEN
4422: oe_debug_pub.add ('AAC:VTI: FSTI: starting find_sold_to_id');
4423: END IF;
4422: oe_debug_pub.add ('AAC:VTI: FSTI: starting find_sold_to_id');
4423: END IF;
4424:
4425: -- check if party_id and cust_account_id are not null, return if so
4426: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4427: and nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4428: THEN
4429: IF l_debug_level > 0 THEN
4430: oe_debug_pub.add ('AAC:FSTI: party_id and account_id are not null, nothing to do: returning');
4423: END IF;
4424:
4425: -- check if party_id and cust_account_id are not null, return if so
4426: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4427: and nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4428: THEN
4429: IF l_debug_level > 0 THEN
4430: oe_debug_pub.add ('AAC:FSTI: party_id and account_id are not null, nothing to do: returning');
4431: END IF;
4436: oe_debug_pub.add ('AAC:FSTI: party_id: '||p_party_id||'; cust_account_id: '||p_cust_account_id);
4437: END IF;
4438: END IF;
4439:
4440: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4442: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4443: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4437: END IF;
4438: END IF;
4439:
4440: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4442: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4443: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4445: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4438: END IF;
4439:
4440: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4442: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4443: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4445: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4446: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4439:
4440: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4442: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4443: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4445: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4446: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4447: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4440: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4442: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4443: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4445: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4446: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4447: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4448: then
4441: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4442: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4443: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4445: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4446: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4447: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4448: then
4449: --nothing to do! return!
4442: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4443: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4445: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4446: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4447: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4448: then
4449: --nothing to do! return!
4450: IF l_debug_level > 0 THEN
4443: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4445: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4446: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4447: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4448: then
4449: --nothing to do! return!
4450: IF l_debug_level > 0 THEN
4451: oe_debug_pub.add ('AAC:FSTI: no data passed in, seting process_site to false, returning');
4464: oe_debug_pub.add('p_party_id :'||p_party_id);
4465:
4466: begin
4467: -- check if site_use_id is passed
4468: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4469: nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM ) then
4470:
4471: IF l_debug_level > 0 THEN
4472: oe_debug_pub.add ('AAC:FSTI: site_use_id:'||p_site_use_id||' is not null, cust_account_id is null: getting cust_account_id');
4465:
4466: begin
4467: -- check if site_use_id is passed
4468: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4469: nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM ) then
4470:
4471: IF l_debug_level > 0 THEN
4472: oe_debug_pub.add ('AAC:FSTI: site_use_id:'||p_site_use_id||' is not null, cust_account_id is null: getting cust_account_id');
4473: END IF;
4481:
4482: end if;
4483:
4484: -- check if cust_account_id is not null, return if so
4485: if nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM and
4486: nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
4487:
4488: IF l_debug_level > 0 THEN
4489: oe_debug_pub.add ('AAC:FSTI: cust_account_id:'||p_cust_account_id||' is not null, party_id is null: getting party_id');
4482: end if;
4483:
4484: -- check if cust_account_id is not null, return if so
4485: if nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM and
4486: nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
4487:
4488: IF l_debug_level > 0 THEN
4489: oe_debug_pub.add ('AAC:FSTI: cust_account_id:'||p_cust_account_id||' is not null, party_id is null: getting party_id');
4490: END IF;
4510: IF l_debug_level > 4 THEN
4511: oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4512: END IF;
4513:
4514: x_return_status := FND_API.G_RET_STS_ERROR;
4515: return;
4516:
4517: WHEN OTHERS THEN
4518:
4525: --END IF;
4526: IF l_debug_level > 4 THEN
4527: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4528: END IF;
4529: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4530:
4531: end;
4532:
4533:
4538: ,p_site_use_code => p_party_site_use_code
4539: );
4540:
4541: -- if account found, return
4542: IF nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4543: THEN
4544: p_cust_account_id := NULL;
4545:
4546: else
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
4554: begin
4555: select party_id
4556: into p_party_id
4557: from hz_cust_accounts
4573: IF l_debug_level > 4 THEN
4574: oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4575: END IF;
4576:
4577: x_return_status := FND_API.G_RET_STS_ERROR;
4578: return;
4579:
4580: WHEN OTHERS THEN
4581:
4588: --END IF;
4589: IF l_debug_level > 4 THEN
4590: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4591: END IF;
4592: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4593:
4594: end;
4595: end if;
4596: return;
4595: end if;
4596: return;
4597: end if;
4598:
4599: if (nvl(p_party_number,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4600: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4601: then
4602: IF l_debug_level > 0 THEN
4603: oe_debug_pub.add ('AAC:FSTI: party_number:'|| p_party_number||' is not null, picking up party_id from it');
4596: return;
4597: end if;
4598:
4599: if (nvl(p_party_number,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4600: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4601: then
4602: IF l_debug_level > 0 THEN
4603: oe_debug_pub.add ('AAC:FSTI: party_number:'|| p_party_number||' is not null, picking up party_id from it');
4604: END IF;
4622: IF l_debug_level > 4 THEN
4623: oe_debug_pub.add ('AAC:VTI: invalid party_number ');
4624: END IF;
4625:
4626: x_return_status := FND_API.G_RET_STS_ERROR;
4627: p_party_id := null;
4628: return;
4629:
4630: WHEN OTHERS THEN
4635: --END IF;
4636: IF l_debug_level > 4 THEN
4637: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4638: END IF;
4639: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4640:
4641: end;
4642: end if;
4643:
4641: end;
4642: end if;
4643:
4644:
4645: if (nvl(p_party_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4646: and nvl(p_party_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4647: then
4648: IF l_debug_level > 0 THEN
4649: oe_debug_pub.add ('AAC:FSTI: party_site_use_id:'|| p_party_site_use_id||' is not null, picking up party_site_id from it');
4642: end if;
4643:
4644:
4645: if (nvl(p_party_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4646: and nvl(p_party_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4647: then
4648: IF l_debug_level > 0 THEN
4649: oe_debug_pub.add ('AAC:FSTI: party_site_use_id:'|| p_party_site_use_id||' is not null, picking up party_site_id from it');
4650: END IF;
4667: IF l_debug_level > 4 THEN
4668: oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
4669: END IF;
4670:
4671: x_return_status := FND_API.G_RET_STS_ERROR;
4672: p_party_site_id := null;
4673: return;
4674:
4675: WHEN OTHERS THEN
4680: --END IF;
4681: IF l_debug_level > 4 THEN
4682: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4683: END IF;
4684: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4685:
4686: end;
4687: end if;
4688:
4685:
4686: end;
4687: end if;
4688:
4689: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4690: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4691: then
4692: IF l_debug_level > 0 THEN
4693: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up party_id from it');
4686: end;
4687: end if;
4688:
4689: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4690: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4691: then
4692: IF l_debug_level > 0 THEN
4693: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up party_id from it');
4694: END IF;
4711: IF l_debug_level > 4 THEN
4712: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
4713: END IF;
4714:
4715: x_return_status := FND_API.G_RET_STS_ERROR;
4716: return;
4717:
4718: WHEN OTHERS THEN
4719:
4723: --END IF;
4724: IF l_debug_level > 4 THEN
4725: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4726: END IF;
4727: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4728:
4729: end;
4730: end if;
4731:
4729: end;
4730: end if;
4731:
4732: /*
4733: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4734: and nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4735: then
4736: IF l_debug_level > 0 THEN
4737: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust site_use_id from it');
4730: end if;
4731:
4732: /*
4733: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4734: and nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4735: then
4736: IF l_debug_level > 0 THEN
4737: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust site_use_id from it');
4738: END IF;
4766: --END IF;
4767: IF l_debug_level > 4 THEN
4768: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4769: END IF;
4770: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4771:
4772: end;
4773: end if;*/
4774:
4772: end;
4773: end if;*/
4774:
4775: /*
4776: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4777: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4778: then
4779: IF l_debug_level > 0 THEN
4780: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust_account_id from it via account_sites');
4773: end if;*/
4774:
4775: /*
4776: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4777: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4778: then
4779: IF l_debug_level > 0 THEN
4780: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust_account_id from it via account_sites');
4781: END IF;
4799: oe_debug_pub.add ('AAC:VTI: party_site_id: multiple accounts ');
4800: END IF;
4801:
4802: p_cust_account_id := null;
4803: x_return_status := FND_API.G_RET_STS_ERROR;
4804: p_cust_account_id := null;
4805: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4806: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4807: OE_MSG_PUB.ADD;
4816: --END IF;
4817: IF l_debug_level > 4 THEN
4818: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4819: END IF;
4820: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4821:
4822: end;
4823: end if;*/
4824:
4823: end if;*/
4824:
4825:
4826: /*
4827: if (nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4828: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4829: then
4830: IF l_debug_level > 0 THEN
4831: oe_debug_pub.add ('AAC:FSTI: site_id:'|| p_site_id||' is not null, picking up cust_account_id from it');
4824:
4825:
4826: /*
4827: if (nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4828: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4829: then
4830: IF l_debug_level > 0 THEN
4831: oe_debug_pub.add ('AAC:FSTI: site_id:'|| p_site_id||' is not null, picking up cust_account_id from it');
4832: END IF;
4849: IF l_debug_level > 4 THEN
4850: oe_debug_pub.add ('AAC:VTI: site_id: multiple accounts, returning with error ');
4851: END IF;
4852:
4853: x_return_status := FND_API.G_RET_STS_ERROR;
4854: p_cust_account_id := null;
4855: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4856: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4857: OE_MSG_PUB.ADD;
4866: --END IF;
4867: IF l_debug_level > 4 THEN
4868: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4869: END IF;
4870: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4871:
4872: end;
4873: end if;
4874: */
4876: -- We have a party_id, then look for account_id using it
4877: -- multiple matches is an error
4878: -- single/zero match is ok
4879:
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;
4877: -- multiple matches is an error
4878: -- single/zero match is ok
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:
4895: IF l_debug_level > 0 THEN
4896: oe_debug_pub.add ('AAC:FSTI: ERROR: multiple accounts found');
4897: END IF;
4898:
4899: --x_return_status := FND_API.G_RET_STS_ERROR;
4900: p_cust_account_id := null;
4901: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4902: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4903: OE_MSG_PUB.ADD;
4939: END IF;
4940: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
4941: OE_MSG_PUB.ADD;
4942: p_cust_account_id := NULL;
4943: x_return_status := FND_API.G_RET_STS_ERROR;
4944: return;
4945: END IF;
4946:
4947: -- at this point:
4958: p_party_site_use_code => p_party_site_use_code
4959: );
4960:
4961:
4962: IF nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4963: THEN
4964: -- didn't find a party_id either
4965: -- since we *cannot* create a party, error out
4966: IF l_debug_level > 0 THEN
4966: IF l_debug_level > 0 THEN
4967: oe_debug_pub.add ('AAC:FSTI: cannot find party_id, returning with error');
4968: END IF;
4969: p_party_id := NULL;
4970: x_return_status := FND_API.G_RET_STS_ERROR;
4971: return;
4972: END IF;
4973:
4974: IF l_debug_level > 0 THEN
4997: IF l_debug_level > 4 THEN
4998: oe_debug_pub.add ('AAC:VTI: invalid p_cust_account_id in find_sold_to_id'); -- got a party_name
4999: END IF;
5000:
5001: x_return_status := FND_API.G_RET_STS_ERROR;
5002: return;
5003:
5004: WHEN OTHERS THEN
5005:
5012: END IF;
5013: IF l_debug_level > 4 THEN
5014: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id'); -- got a party_name
5015: END IF;
5016: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5017:
5018: END find_sold_to_id;
5019:
5020: PROCEDURE find_contact_id(
5033: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
5034:
5035: BEGIN
5036:
5037: x_return_status := FND_API.G_RET_STS_SUCCESS;
5038:
5039: IF l_debug_level > 0 THEN
5040: oe_debug_pub.add ('AAC:VTI: starting find_contact_id...');
5041: END IF;
5040: oe_debug_pub.add ('AAC:VTI: starting find_contact_id...');
5041: END IF;
5042:
5043: -- check if cust_contact_id is not null, return if so
5044: if nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5045: IF l_debug_level > 0 THEN
5046: oe_debug_pub.add ('AAC:FCI: cust_contact_id is not null, nothing to do: warn; returning}');
5047: END IF;
5048:
5055:
5056: RETURN;
5057: END IF;
5058:
5059: if nvl(p_sold_to_org_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5060: IF l_debug_level > 0 THEN
5061: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is null; returning');
5062: END IF;
5063:
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;
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
5091: ,p_end_customer_id => p_sold_to_org_id
5092: );
5093: end if;
5094: -- if contact found, return
5095: IF nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5096: THEN
5097: -- found a cust_contact_id
5098: IF l_debug_level > 0 THEN
5099: oe_debug_pub.add ('AAC:FCI: found cust_contact_id:'||p_cust_contact_id||', returning}');
5105: end if;
5106:
5107:
5108: -- check if contact_id is not null, return if so
5109: if nvl(p_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5110: IF l_debug_level > 0 THEN
5111: oe_debug_pub.add ('AAC:FSTI: org_contact_id is not null');
5112: END IF;
5113:
5123: oe_debug_pub.add ('AAC:FCI: no account contact_id');
5124: oe_debug_pub.add ('AAC:FCI: have permission to create account contact: finding party contact_id');
5125: END IF;
5126:
5127: IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5128: THEN
5129: p_contact_id := get_party_contact_id(
5130: p_contact_name => p_contact_name
5131: ,p_party_id => p_party_id
5132: ,p_sold_to_org_id => p_sold_to_org_id
5133: );
5134:
5135:
5136: IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5137: THEN
5138: -- didn't find a party level contact_id either
5139: -- since we *cannot* create a party, error out
5140: IF l_debug_level > 0 THEN
5140: IF l_debug_level > 0 THEN
5141: oe_debug_pub.add ('AAC:FCI: cannot find contact_id, returning with error}');
5142: END IF;
5143: p_contact_id := NULL;
5144: x_return_status := FND_API.G_RET_STS_ERROR;
5145: return;
5146: END IF;
5147: end if;
5148: -- found a contact_id
5185: l_cust_account_id number := null;
5186:
5187: BEGIN
5188:
5189: x_return_status := FND_API.G_RET_STS_SUCCESS;
5190:
5191: IF l_debug_level > 0 THEN
5192: oe_debug_pub.add ('AAC:VTI: FSI: starting find_site_id{');
5193: END IF;
5191: IF l_debug_level > 0 THEN
5192: oe_debug_pub.add ('AAC:VTI: FSI: starting find_site_id{');
5193: END IF;
5194:
5195: if (nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5196: then
5197: IF l_debug_level > 0 THEN
5198: oe_debug_pub.add ('AAC:FSI: party_id null; returning');
5199: END IF;
5200:
5201: return;
5202: end if;
5203:
5204: if (nvl(p_party_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5205: OR nvl(p_site_customer_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
5206: then
5207: l_cust_account_id := p_site_customer_id;
5208: else
5201: return;
5202: end if;
5203:
5204: if (nvl(p_party_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5205: OR nvl(p_site_customer_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
5206: then
5207: l_cust_account_id := p_site_customer_id;
5208: else
5209: l_cust_account_id := p_sold_to_org_id;
5210: end if;
5211:
5212: -- check if site_address1 is null
5213: IF l_debug_level > 0 THEN
5214: if nvl(p_site_address1,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
5215: oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5216: else
5217: oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5218: end if;
5219: oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5220: END IF;
5221:
5222: -- check if cust_site_use_id is not null, return if so
5223: if nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5224: IF l_debug_level > 0 THEN
5225: oe_debug_pub.add ('AAC:FSI: account_site_use_id is not null; returning');
5226: END IF;
5227:
5228: RETURN;
5229: END IF;
5230:
5231: -- check if site_use_code is null, return with error if so
5232: if nvl(p_site_use_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR then
5233: IF l_debug_level > 0 THEN
5234: oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5235: oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5236: end if;
5233: IF l_debug_level > 0 THEN
5234: oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5235: oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5236: end if;
5237: x_return_status := FND_API.G_RET_STS_ERROR;
5238: return;
5239: end if;
5240:
5241: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5237: x_return_status := FND_API.G_RET_STS_ERROR;
5238: return;
5239: end if;
5240:
5241: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5243: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5244: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5238: return;
5239: end if;
5240:
5241: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5243: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5244: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5239: end if;
5240:
5241: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5243: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5244: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5240:
5241: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5243: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5244: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5241: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5243: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5244: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5242: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5243: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5244: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5244: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5254: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5247: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5254: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5248: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5254: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5256: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5249: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5254: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5256: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5257: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5250: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5254: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5256: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5257: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5258: then
5251: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5254: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5256: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5257: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5258: then
5259: --nothing to do! return!
5252: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5254: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5256: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5257: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5258: then
5259: --nothing to do! return!
5260: IF l_debug_level > 0 THEN
5253: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5254: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5256: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5257: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5258: then
5259: --nothing to do! return!
5260: IF l_debug_level > 0 THEN
5261: oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5280: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5281: oe_debug_pub.add('p_party_id :'|| p_party_id );
5282: end if;
5283:
5284: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5285: and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5286: IF l_debug_level > 0 THEN
5287: oe_debug_pub.add ('AAC:FSI: party_site_use_id:'||p_site_use_id||' is not null; using it to get party_site_id');
5288: END IF;
5281: oe_debug_pub.add('p_party_id :'|| p_party_id );
5282: end if;
5283:
5284: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5285: and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5286: IF l_debug_level > 0 THEN
5287: oe_debug_pub.add ('AAC:FSI: party_site_use_id:'||p_site_use_id||' is not null; using it to get party_site_id');
5288: END IF;
5289: begin
5308: IF l_debug_level > 4 THEN
5309: oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
5310: END IF;
5311:
5312: x_return_status := FND_API.G_RET_STS_ERROR;
5313: return;
5314:
5315: WHEN OTHERS THEN
5316:
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;
5330:
5331: end if;
5329: end;
5330:
5331: end if;
5332:
5333: if nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5334: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5335: IF l_debug_level > 0 THEN
5336: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5337: END IF;
5330:
5331: end if;
5332:
5333: if nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5334: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5335: IF l_debug_level > 0 THEN
5336: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5337: END IF;
5338: begin
5360: END IF;
5361: IF l_debug_level > 4 THEN
5362: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5363: END IF;
5364: x_return_status := FND_API.G_RET_STS_ERROR;
5365: return;
5366:
5367: WHEN OTHERS THEN
5368:
5375: END IF;
5376: IF l_debug_level > 4 THEN
5377: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5378: END IF;
5379: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5380:
5381: end;
5382: end if;
5383:
5380:
5381: end;
5382: end if;
5383:
5384: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5381: end;
5382: end if;
5383:
5384: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5382: end if;
5383:
5384: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5383:
5384: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5384: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5392: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5392: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5393: then
5386: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5392: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5393: then
5394: --nothing to do! return!
5387: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5392: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5393: then
5394: --nothing to do! return!
5395: IF l_debug_level > 0 THEN
5388: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5392: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5393: then
5394: --nothing to do! return!
5395: IF l_debug_level > 0 THEN
5396: oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5464: return;
5465: end if;
5466:
5467: -- if site found, return
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:
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
5506: END IF;
5507: IF l_debug_level > 4 THEN
5508: oe_debug_pub.add ('AAC:VTI: invalid cust_site_use_id ');
5509: END IF;
5510: x_return_status := FND_API.G_RET_STS_ERROR;
5511: return;
5512:
5513: end;
5514: end if;
5530: oe_debug_pub.add ('AAC:FSI: no account site_use_id');
5531: oe_debug_pub.add ('AAC:FSI: finding party site_use_id');
5532: END IF;
5533:
5534: if (nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) then
5535: p_site_id := get_party_site_id(
5536: p_site_address1 => p_site_address1
5537: ,p_site_address2 => p_site_address2
5538: ,p_site_address3 => p_site_address3
5548: ,p_site_use_code => p_site_use_code
5549: ,p_party_id => p_party_id
5550: );
5551:
5552: IF nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5553: THEN
5554: -- didn't find a party level site_id either
5555: -- since we *cannot* create a party, error out
5556: IF l_debug_level > 0 THEN
5556: IF l_debug_level > 0 THEN
5557: oe_debug_pub.add ('AAC:FSI: cannot find party_site_id, returning with error');
5558: END IF;
5559: p_site_id := NULL;
5560: --x_return_status := FND_API.G_RET_STS_ERROR;
5561: return;
5562: ELSE -- we did find a party_site_id, get party_id from it
5563: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5564: IF l_debug_level > 0 THEN
5559: p_site_id := NULL;
5560: --x_return_status := FND_API.G_RET_STS_ERROR;
5561: return;
5562: ELSE -- we did find a party_site_id, get party_id from it
5563: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5564: IF l_debug_level > 0 THEN
5565: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5566: END IF;
5567: begin
5589: END IF;
5590: IF l_debug_level > 4 THEN
5591: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5592: END IF;
5593: x_return_status := FND_API.G_RET_STS_ERROR;
5594: return;
5595:
5596: WHEN OTHERS THEN
5597:
5604: END IF;
5605: IF l_debug_level > 4 THEN
5606: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5607: END IF;
5608: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5609:
5610: end;
5611: end if;
5612: end if;
5638: oe_debug_pub.add ('AAC:VTI: starting get_party_id{');
5639: END IF;
5640:
5641: -- did they actually pass some values?
5642: IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5643: AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5644: THEN
5645: IF l_debug_level > 4 THEN
5646: oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5639: END IF;
5640:
5641: -- did they actually pass some values?
5642: IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5643: AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5644: THEN
5645: IF l_debug_level > 4 THEN
5646: oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5647: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5648: END IF;
5649: RETURN NULL;
5650: END IF;
5651:
5652: IF (nvl(p_party_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char)
5653: THEN
5654: -- got a party_number
5655: IF l_debug_level > 4 THEN
5656: oe_debug_pub.add ('AAC:VTI:GPI party number: '||p_party_number);
5707: END IF;
5708: IF l_debug_level > 4 THEN
5709: oe_debug_pub.add ('AAC:VTI: no data in Get_party_id'); -- got a party_name
5710: END IF;
5711: RETURN FND_API.G_MISS_NUM;
5712:
5713: WHEN OTHERS THEN
5714:
5715: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5721: END IF;
5722: IF l_debug_level > 4 THEN
5723: oe_debug_pub.add ('AAC:VTI: unexpected error in Get_party_id'); -- got a party_name
5724: END IF;
5725: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5726:
5727: END get_party_id;
5728:
5729: FUNCTION get_party_contact_id(
5759: IF l_debug_level > 4 THEN
5760: oe_debug_pub.add ('AAC:VTI: starting get_party_contact_id...');
5761: END IF;
5762:
5763: IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5764: THEN
5765: IF l_debug_level > 4 THEN
5766: oe_debug_pub.add ('AAC:VTI: no contact_name');
5767: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5826: hz_party_sites site
5827: WHERE site.location_id=loc.location_id
5828: and site.party_id=in_sold_to_party_id
5829: and loc.address1 = p_site_address1
5830: and nvl( loc.address2, fnd_api.g_miss_char) =
5831: nvl( p_site_address2, fnd_api.g_miss_char)
5832: and nvl( loc.address3, fnd_api.g_miss_char) =
5833: nvl( p_site_address3, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5827: WHERE site.location_id=loc.location_id
5828: and site.party_id=in_sold_to_party_id
5829: and loc.address1 = p_site_address1
5830: and nvl( loc.address2, fnd_api.g_miss_char) =
5831: nvl( p_site_address2, fnd_api.g_miss_char)
5832: and nvl( loc.address3, fnd_api.g_miss_char) =
5833: nvl( p_site_address3, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5828: and site.party_id=in_sold_to_party_id
5829: and loc.address1 = p_site_address1
5830: and nvl( loc.address2, fnd_api.g_miss_char) =
5831: nvl( p_site_address2, fnd_api.g_miss_char)
5832: and nvl( loc.address3, fnd_api.g_miss_char) =
5833: nvl( p_site_address3, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5829: and loc.address1 = p_site_address1
5830: and nvl( loc.address2, fnd_api.g_miss_char) =
5831: nvl( p_site_address2, fnd_api.g_miss_char)
5832: and nvl( loc.address3, fnd_api.g_miss_char) =
5833: nvl( p_site_address3, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5837: nvl( p_site_city, fnd_api.g_miss_char)
5830: and nvl( loc.address2, fnd_api.g_miss_char) =
5831: nvl( p_site_address2, fnd_api.g_miss_char)
5832: and nvl( loc.address3, fnd_api.g_miss_char) =
5833: nvl( p_site_address3, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5837: nvl( p_site_city, fnd_api.g_miss_char)
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5831: nvl( p_site_address2, fnd_api.g_miss_char)
5832: and nvl( loc.address3, fnd_api.g_miss_char) =
5833: nvl( p_site_address3, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5837: nvl( p_site_city, fnd_api.g_miss_char)
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5839: nvl( p_site_state, fnd_api.g_miss_char)
5832: and nvl( loc.address3, fnd_api.g_miss_char) =
5833: nvl( p_site_address3, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5837: nvl( p_site_city, fnd_api.g_miss_char)
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5839: nvl( p_site_state, fnd_api.g_miss_char)
5840: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5833: nvl( p_site_address3, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5837: nvl( p_site_city, fnd_api.g_miss_char)
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5839: nvl( p_site_state, fnd_api.g_miss_char)
5840: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5841: nvl( p_site_postal_code, fnd_api.g_miss_char)
5834: and nvl( loc.address4, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5837: nvl( p_site_city, fnd_api.g_miss_char)
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5839: nvl( p_site_state, fnd_api.g_miss_char)
5840: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5841: nvl( p_site_postal_code, fnd_api.g_miss_char)
5842: and nvl( loc.country, fnd_api.g_miss_char) =
5835: nvl( p_site_address4, fnd_api.g_miss_char)
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5837: nvl( p_site_city, fnd_api.g_miss_char)
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5839: nvl( p_site_state, fnd_api.g_miss_char)
5840: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5841: nvl( p_site_postal_code, fnd_api.g_miss_char)
5842: and nvl( loc.country, fnd_api.g_miss_char) =
5843: nvl( p_site_country, fnd_api.g_miss_char);
5836: and nvl( loc.city, fnd_api.g_miss_char) =
5837: nvl( p_site_city, fnd_api.g_miss_char)
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5839: nvl( p_site_state, fnd_api.g_miss_char)
5840: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5841: nvl( p_site_postal_code, fnd_api.g_miss_char)
5842: and nvl( loc.country, fnd_api.g_miss_char) =
5843: nvl( p_site_country, fnd_api.g_miss_char);
5844:
5837: nvl( p_site_city, fnd_api.g_miss_char)
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5839: nvl( p_site_state, fnd_api.g_miss_char)
5840: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5841: nvl( p_site_postal_code, fnd_api.g_miss_char)
5842: and nvl( loc.country, fnd_api.g_miss_char) =
5843: nvl( p_site_country, fnd_api.g_miss_char);
5844:
5845:
5838: and nvl( loc.state, fnd_api.g_miss_char) =
5839: nvl( p_site_state, fnd_api.g_miss_char)
5840: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5841: nvl( p_site_postal_code, fnd_api.g_miss_char)
5842: and nvl( loc.country, fnd_api.g_miss_char) =
5843: nvl( p_site_country, fnd_api.g_miss_char);
5844:
5845:
5846: /*
5839: nvl( p_site_state, fnd_api.g_miss_char)
5840: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5841: nvl( p_site_postal_code, fnd_api.g_miss_char)
5842: and nvl( loc.country, fnd_api.g_miss_char) =
5843: nvl( p_site_country, fnd_api.g_miss_char);
5844:
5845:
5846: /*
5847: cursor C1(in_sold_to_party_id number) IS
5850: HZ_LOCATIONS LOC
5851: WHERE site.location_id=loc.location_id
5852: and site.status='A'
5853: and loc.ADDRESS1 = p_site_address1
5854: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5855: nvl( p_site_address2, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5857: nvl( p_site_address3, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5851: WHERE site.location_id=loc.location_id
5852: and site.status='A'
5853: and loc.ADDRESS1 = p_site_address1
5854: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5855: nvl( p_site_address2, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5857: nvl( p_site_address3, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5852: and site.status='A'
5853: and loc.ADDRESS1 = p_site_address1
5854: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5855: nvl( p_site_address2, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5857: nvl( p_site_address3, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5853: and loc.ADDRESS1 = p_site_address1
5854: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5855: nvl( p_site_address2, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5857: nvl( p_site_address3, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5861: nvl( p_site_city, fnd_api.g_miss_char)
5854: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5855: nvl( p_site_address2, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5857: nvl( p_site_address3, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5861: nvl( p_site_city, fnd_api.g_miss_char)
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5855: nvl( p_site_address2, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5857: nvl( p_site_address3, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5861: nvl( p_site_city, fnd_api.g_miss_char)
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5863: nvl( p_site_state, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5857: nvl( p_site_address3, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5861: nvl( p_site_city, fnd_api.g_miss_char)
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5863: nvl( p_site_state, fnd_api.g_miss_char)
5864: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5857: nvl( p_site_address3, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5861: nvl( p_site_city, fnd_api.g_miss_char)
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5863: nvl( p_site_state, fnd_api.g_miss_char)
5864: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5865: nvl( p_site_postal_code, fnd_api.g_miss_char)
5858: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5861: nvl( p_site_city, fnd_api.g_miss_char)
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5863: nvl( p_site_state, fnd_api.g_miss_char)
5864: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5865: nvl( p_site_postal_code, fnd_api.g_miss_char)
5866: AND nvl( loc.country, fnd_api.g_miss_char) =
5859: nvl( p_site_address4, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5861: nvl( p_site_city, fnd_api.g_miss_char)
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5863: nvl( p_site_state, fnd_api.g_miss_char)
5864: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5865: nvl( p_site_postal_code, fnd_api.g_miss_char)
5866: AND nvl( loc.country, fnd_api.g_miss_char) =
5867: nvl( p_site_country, fnd_api.g_miss_char)
5860: AND nvl( loc.city, fnd_api.g_miss_char) =
5861: nvl( p_site_city, fnd_api.g_miss_char)
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5863: nvl( p_site_state, fnd_api.g_miss_char)
5864: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5865: nvl( p_site_postal_code, fnd_api.g_miss_char)
5866: AND nvl( loc.country, fnd_api.g_miss_char) =
5867: nvl( p_site_country, fnd_api.g_miss_char)
5868: AND site.status = 'A'
5861: nvl( p_site_city, fnd_api.g_miss_char)
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5863: nvl( p_site_state, fnd_api.g_miss_char)
5864: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5865: nvl( p_site_postal_code, fnd_api.g_miss_char)
5866: AND nvl( loc.country, fnd_api.g_miss_char) =
5867: nvl( p_site_country, fnd_api.g_miss_char)
5868: AND site.status = 'A'
5869: AND site.party_id in(
5862: AND nvl( loc.state, fnd_api.g_miss_char) =
5863: nvl( p_site_state, fnd_api.g_miss_char)
5864: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5865: nvl( p_site_postal_code, fnd_api.g_miss_char)
5866: AND nvl( loc.country, fnd_api.g_miss_char) =
5867: nvl( p_site_country, fnd_api.g_miss_char)
5868: AND site.status = 'A'
5869: AND site.party_id in(
5870: SELECT in_sold_to_party_id FROM DUAL
5863: nvl( p_site_state, fnd_api.g_miss_char)
5864: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5865: nvl( p_site_postal_code, fnd_api.g_miss_char)
5866: AND nvl( loc.country, fnd_api.g_miss_char) =
5867: nvl( p_site_country, fnd_api.g_miss_char)
5868: AND site.status = 'A'
5869: AND site.party_id in(
5870: SELECT in_sold_to_party_id FROM DUAL
5871: UNION
5880: hz_party_sites site
5881: WHERE site.location_id=loc.location_id
5882: and site.status='A'
5883: and loc.address1 = p_site_address1
5884: and nvl( loc.address2, fnd_api.g_miss_char) =
5885: nvl( p_site_address2, fnd_api.g_miss_char)
5886: and nvl( loc.address3, fnd_api.g_miss_char) =
5887: nvl( p_site_address3, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5881: WHERE site.location_id=loc.location_id
5882: and site.status='A'
5883: and loc.address1 = p_site_address1
5884: and nvl( loc.address2, fnd_api.g_miss_char) =
5885: nvl( p_site_address2, fnd_api.g_miss_char)
5886: and nvl( loc.address3, fnd_api.g_miss_char) =
5887: nvl( p_site_address3, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5882: and site.status='A'
5883: and loc.address1 = p_site_address1
5884: and nvl( loc.address2, fnd_api.g_miss_char) =
5885: nvl( p_site_address2, fnd_api.g_miss_char)
5886: and nvl( loc.address3, fnd_api.g_miss_char) =
5887: nvl( p_site_address3, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5883: and loc.address1 = p_site_address1
5884: and nvl( loc.address2, fnd_api.g_miss_char) =
5885: nvl( p_site_address2, fnd_api.g_miss_char)
5886: and nvl( loc.address3, fnd_api.g_miss_char) =
5887: nvl( p_site_address3, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5891: nvl( p_site_city, fnd_api.g_miss_char)
5884: and nvl( loc.address2, fnd_api.g_miss_char) =
5885: nvl( p_site_address2, fnd_api.g_miss_char)
5886: and nvl( loc.address3, fnd_api.g_miss_char) =
5887: nvl( p_site_address3, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5891: nvl( p_site_city, fnd_api.g_miss_char)
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5885: nvl( p_site_address2, fnd_api.g_miss_char)
5886: and nvl( loc.address3, fnd_api.g_miss_char) =
5887: nvl( p_site_address3, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5891: nvl( p_site_city, fnd_api.g_miss_char)
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5893: nvl( p_site_state, fnd_api.g_miss_char)
5886: and nvl( loc.address3, fnd_api.g_miss_char) =
5887: nvl( p_site_address3, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5891: nvl( p_site_city, fnd_api.g_miss_char)
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5893: nvl( p_site_state, fnd_api.g_miss_char)
5894: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5887: nvl( p_site_address3, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5891: nvl( p_site_city, fnd_api.g_miss_char)
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5893: nvl( p_site_state, fnd_api.g_miss_char)
5894: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5895: nvl( p_site_postal_code, fnd_api.g_miss_char)
5888: and nvl( loc.address4, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5891: nvl( p_site_city, fnd_api.g_miss_char)
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5893: nvl( p_site_state, fnd_api.g_miss_char)
5894: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5895: nvl( p_site_postal_code, fnd_api.g_miss_char)
5896: and nvl( loc.country, fnd_api.g_miss_char) =
5889: nvl( p_site_address4, fnd_api.g_miss_char)
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5891: nvl( p_site_city, fnd_api.g_miss_char)
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5893: nvl( p_site_state, fnd_api.g_miss_char)
5894: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5895: nvl( p_site_postal_code, fnd_api.g_miss_char)
5896: and nvl( loc.country, fnd_api.g_miss_char) =
5897: nvl( p_site_country, fnd_api.g_miss_char);
5890: and nvl( loc.city, fnd_api.g_miss_char) =
5891: nvl( p_site_city, fnd_api.g_miss_char)
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5893: nvl( p_site_state, fnd_api.g_miss_char)
5894: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5895: nvl( p_site_postal_code, fnd_api.g_miss_char)
5896: and nvl( loc.country, fnd_api.g_miss_char) =
5897: nvl( p_site_country, fnd_api.g_miss_char);
5898:
5891: nvl( p_site_city, fnd_api.g_miss_char)
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5893: nvl( p_site_state, fnd_api.g_miss_char)
5894: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5895: nvl( p_site_postal_code, fnd_api.g_miss_char)
5896: and nvl( loc.country, fnd_api.g_miss_char) =
5897: nvl( p_site_country, fnd_api.g_miss_char);
5898:
5899: l_site_party_id number;
5892: and nvl( loc.state, fnd_api.g_miss_char) =
5893: nvl( p_site_state, fnd_api.g_miss_char)
5894: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5895: nvl( p_site_postal_code, fnd_api.g_miss_char)
5896: and nvl( loc.country, fnd_api.g_miss_char) =
5897: nvl( p_site_country, fnd_api.g_miss_char);
5898:
5899: l_site_party_id number;
5900: l_sold_to_party_id number;
5893: nvl( p_site_state, fnd_api.g_miss_char)
5894: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5895: nvl( p_site_postal_code, fnd_api.g_miss_char)
5896: and nvl( loc.country, fnd_api.g_miss_char) =
5897: nvl( p_site_country, fnd_api.g_miss_char);
5898:
5899: l_site_party_id number;
5900: l_sold_to_party_id number;
5901: l_dummy number;
5910: oe_debug_pub.add(' address4:'||p_site_address4);
5911: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5912: END IF;
5913:
5914: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5917: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5918: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5911: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5912: END IF;
5913:
5914: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5917: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5918: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5919: THEN
5912: END IF;
5913:
5914: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5917: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5918: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5919: THEN
5920: IF l_debug_level > 0 THEN
5913:
5914: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5917: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5918: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5919: THEN
5920: IF l_debug_level > 0 THEN
5921: oe_debug_pub.add( 'AAC: all incoming data missing,returning');
5914: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5917: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5918: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5919: THEN
5920: IF l_debug_level > 0 THEN
5921: oe_debug_pub.add( 'AAC: all incoming data missing,returning');
5922: end if;
5922: end if;
5923: RETURN NULL;
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:
5948: INTO l_id
5949: FROM hz_locations loc,
5950: hz_party_sites site
5951: WHERE loc.ADDRESS1 = p_site_address1
5952: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5953: nvl( p_site_address2, fnd_api.g_miss_char)
5954: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5955: nvl( p_site_address3, fnd_api.g_miss_char)
5956: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5949: FROM hz_locations loc,
5950: hz_party_sites site
5951: WHERE loc.ADDRESS1 = p_site_address1
5952: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5953: nvl( p_site_address2, fnd_api.g_miss_char)
5954: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5955: nvl( p_site_address3, fnd_api.g_miss_char)
5956: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5957: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5950: hz_party_sites site
5951: WHERE loc.ADDRESS1 = p_site_address1
5952: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5953: nvl( p_site_address2, fnd_api.g_miss_char)
5954: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5955: nvl( p_site_address3, fnd_api.g_miss_char)
5956: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5957: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5958: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5951: WHERE loc.ADDRESS1 = p_site_address1
5952: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5953: nvl( p_site_address2, fnd_api.g_miss_char)
5954: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5955: nvl( p_site_address3, fnd_api.g_miss_char)
5956: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5957: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5958: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5959: DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
5956: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5957: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5958: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5959: DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
5960: nvl( p_site_address4, fnd_api.g_miss_char)
5961: AND site.status = 'A'
5962: AND site.party_id = p_party_id
5963: and site.location_id=loc.location_id;
5964:
5998: INTO l_id
5999: FROM hz_locations loc,
6000: hz_party_sites site
6001: WHERE loc.ADDRESS1 = p_site_address1
6002: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6003: nvl( p_site_address2, fnd_api.g_miss_char)
6004: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6005: nvl( p_site_address3, fnd_api.g_miss_char)
6006: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5999: FROM hz_locations loc,
6000: hz_party_sites site
6001: WHERE loc.ADDRESS1 = p_site_address1
6002: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6003: nvl( p_site_address2, fnd_api.g_miss_char)
6004: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6005: nvl( p_site_address3, fnd_api.g_miss_char)
6006: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6007: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
6000: hz_party_sites site
6001: WHERE loc.ADDRESS1 = p_site_address1
6002: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6003: nvl( p_site_address2, fnd_api.g_miss_char)
6004: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6005: nvl( p_site_address3, fnd_api.g_miss_char)
6006: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6007: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
6008: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
6001: WHERE loc.ADDRESS1 = p_site_address1
6002: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6003: nvl( p_site_address2, fnd_api.g_miss_char)
6004: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6005: nvl( p_site_address3, fnd_api.g_miss_char)
6006: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6007: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
6008: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
6009: DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
6006: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6007: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
6008: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
6009: DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
6010: nvl( p_site_address4, fnd_api.g_miss_char)
6011: AND site.status = 'A'
6012: and site.location_id=loc.location_id;
6013:
6014: IF l_debug_level > 0 THEN
6064: , 'get_party_site_id'
6065: );
6066: END IF;
6067:
6068: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6069:
6070: END get_party_site_id;
6071:
6072:
6082: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6083: --
6084: BEGIN
6085:
6086: IF nvl(p_sold_to_org,fnd_api.g_miss_char) = fnd_api.g_miss_char
6087: AND nvl(p_customer_number,fnd_api.g_miss_char) = fnd_api.g_miss_char
6088: THEN
6089: RETURN NULL;
6090: END IF;
6083: --
6084: BEGIN
6085:
6086: IF nvl(p_sold_to_org,fnd_api.g_miss_char) = fnd_api.g_miss_char
6087: AND nvl(p_customer_number,fnd_api.g_miss_char) = fnd_api.g_miss_char
6088: THEN
6089: RETURN NULL;
6090: END IF;
6091:
6089: RETURN NULL;
6090: END IF;
6091:
6092:
6093: IF nvl(p_customer_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
6094:
6095: SELECT ORGANIZATION_ID
6096: INTO l_id
6097: FROM OE_SOLD_TO_ORGS_V
6135: OE_MSG_PUB.Add;
6136:
6137: END IF;
6138:
6139: RETURN FND_API.G_MISS_NUM;
6140:
6141: WHEN OTHERS THEN
6142:
6143: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6147: , 'Sold_To_Org'
6148: );
6149: END IF;
6150:
6151: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6152:
6153: END Sold_To_Org;
6154:
6155: PROCEDURE does_Cust_Exist(p_cust_id IN NUMBER, --(5255840)