[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')
710: ,x_return_status => x_return_status
711: ,x_msg_count => x_msg_count
712: ,x_msg_data => x_msg_data);
713:
714: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
715: oe_debug_pub.add('Creating relationship failed');
716:
717: x_return_status := FND_API.G_RET_STS_ERROR;
718: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
713:
714: IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
715: oe_debug_pub.add('Creating relationship failed');
716:
717: x_return_status := FND_API.G_RET_STS_ERROR;
718: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
719: FND_MESSAGE.Set_Token('TEXT',' Customer Relationship creation failed. ', FALSE);
720: OE_MSG_PUB.ADD;
721: OE_MSG_PUB.Count_And_Get ( p_count => x_msg_count , p_data => x_msg_data );
722: return;
723: END IF; -- if failure
724: oe_debug_pub.add('AAC:RC: relationship created');
725: else -- l_add_customer = 'N'
726: x_return_status := FND_API.G_RET_STS_ERROR;
727: IF l_debug_level > 0 THEN
728: oe_debug_pub.add('AAC:RC: ERROR: l_add_customer is N' );
729: END IF;
730: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
760:
761: -- if the check_customer did not fail or did not return multiple accts
762: -- then call check contact
763:
764: IF x_return_status <> FND_API.G_RET_STS_ERROR AND
765: ( p_account_tbl.COUNT = 1 OR
766: p_party_customer_rec.p_cust_account_id IS NOT NULL) AND
767: p_Control_rec.p_process_contact
768: THEN
819: END IF;
820: END LOOP;
821: END IF;
822:
823: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
824: IF l_debug_level > 0 THEN
825: oe_debug_pub.add( 'contact with error' ) ;
826: END IF;
827: l_contact_status := FND_API.G_RET_STS_ERROR;
823: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
824: IF l_debug_level > 0 THEN
825: oe_debug_pub.add( 'contact with error' ) ;
826: END IF;
827: l_contact_status := FND_API.G_RET_STS_ERROR;
828: x_msg_data_contact := x_msg_data;
829: x_msg_count_contact := x_msg_count;
830: --OE_MSG_PUB.Count_And_Get
831: --( p_count => x_msg_count
844: if p_control_rec.p_multiple_contact_is_error then
845: IF l_debug_level > 0 THEN
846: oe_debug_pub.add( 'erroring with multiple contacts' ) ;
847: END IF;
848: x_return_status := FND_API.G_RET_STS_ERROR;
849: else
850: x_return_status := FND_API.G_RET_STS_SUCCESS;
851: end if;
852: return;
846: oe_debug_pub.add( 'erroring with multiple contacts' ) ;
847: END IF;
848: x_return_status := FND_API.G_RET_STS_ERROR;
849: else
850: x_return_status := FND_API.G_RET_STS_SUCCESS;
851: end if;
852: return;
853: ELSIF p_contact_tbl.COUNT = 0 then
854: IF l_debug_level > 0 THEN
853: ELSIF p_contact_tbl.COUNT = 0 then
854: IF l_debug_level > 0 THEN
855: oe_debug_pub.add( 'no contacts found error' ) ;
856: END IF;
857: x_return_status := FND_API.G_RET_STS_ERROR;
858: ELSIF p_contact_tbl.COUNT = 1 then
859: null;
860: END IF;
861:
887:
888: -- If status is not error and there are records in the site table
889: -- OR we shall continue even if error
890: IF (p_control_rec.p_continue_processing_on_error AND p_site_tbl.COUNT >0 ) OR
891: (x_return_status <> FND_API.G_RET_STS_ERROR AND p_site_tbl.COUNT > 0 ) THEN
892:
893: -- loop through p_site_tbl for all contacts
894:
895: FOR i IN p_site_tbl.FIRST..p_site_tbl.LAST LOOP
902: l2_cust_account_id := l_cust_account_id;
903: l2_party_id := l_party_id;
904: l2_org_contact_id := l_org_contact_id;
905:
906: if (nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
907: OR nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM )
908: then
909: l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
910: l2_party_id := p_site_tbl(i).p_party_id;
903: l2_party_id := l_party_id;
904: l2_org_contact_id := l_org_contact_id;
905:
906: if (nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
907: OR nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM )
908: then
909: l2_cust_account_id := p_site_tbl(i).p_cust_account_id;
910: l2_party_id := p_site_tbl(i).p_party_id;
911: oe_debug_pub.add('site level cust_account_id: '||l2_cust_account_id);
913: end if;
914:
915: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
916: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
917: if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
918: = FND_API.G_MISS_NUM) THEN
919: l_cust_account_role_id:=null;
920: else
921: l_cust_account_role_id:=p_site_tbl(i).p_cust_account_role_id;
914:
915: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||p_site_tbl(i).p_cust_account_role_id);
916: oe_debug_pub.add( 'AAC:Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
917: if (nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)
918: = FND_API.G_MISS_NUM) THEN
919: l_cust_account_role_id:=null;
920: else
921: l_cust_account_role_id:=p_site_tbl(i).p_cust_account_role_id;
922: end if;
922: end if;
923: oe_debug_pub.add( 'AAC: After Site Contact-Cust_Acct_Role_id'||l_cust_account_role_id);
924:
925:
926: if nvl(p_site_tbl(i).p_org_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM then
927: l2_org_contact_id := p_site_tbl(i).p_org_contact_id;
928: end if;
929:
930: --{added for bug 4240715
929:
930: --{added for bug 4240715
931: if p_site_tbl(i).p_site_use_code ='END_CUST' then /* added check for end customer */
932: l_site_use_code := 'SOLD';
933: if nvl(p_site_tbl(i).p_cust_account_role_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM then
934: l_cust_account_role_id:=null;
935: else
936: l_cust_account_role_id :=p_site_tbl(i).p_cust_account_role_id;
937: end if;
963: oe_debug_pub.add( 'AAC: ..done calling check_and_create_contacts ' ) ;
964: oe_debug_pub.add( 'count contact table = '||P2_CONTACT_TBL.COUNT ) ;
965: END IF;
966:
967: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
968: IF l_debug_level > 0 THEN
969: oe_debug_pub.add( 'contact with error' ) ;
970: END IF;
971:
968: IF l_debug_level > 0 THEN
969: oe_debug_pub.add( 'contact with error' ) ;
970: END IF;
971:
972: l_contact_status := FND_API.G_RET_STS_ERROR;
973: x_msg_data_contact := x_msg_data;
974: x_msg_count_contact := x_msg_count;
975: END IF;
976:
980: if p_control_rec.p_multiple_contact_is_error then
981: IF l_debug_level > 0 THEN
982: oe_debug_pub.add( 'erroring with multiple contacts' ) ;
983: END IF;
984: x_return_status := FND_API.G_RET_STS_ERROR;
985: else
986: x_return_status := FND_API.G_RET_STS_SUCCESS;
987: end if;
988:
982: oe_debug_pub.add( 'erroring with multiple contacts' ) ;
983: END IF;
984: x_return_status := FND_API.G_RET_STS_ERROR;
985: else
986: x_return_status := FND_API.G_RET_STS_SUCCESS;
987: end if;
988:
989: ELSIF p2_contact_tbl.COUNT = 0 then
990: IF l_debug_level > 0 THEN
989: ELSIF p2_contact_tbl.COUNT = 0 then
990: IF l_debug_level > 0 THEN
991: oe_debug_pub.add( 'no contacts found error' ) ;
992: END IF;
993: x_return_status := FND_API.G_RET_STS_ERROR;
994: ELSIF p2_contact_tbl.COUNT = 1 then
995: p_site_tbl(i).p_cust_account_role_id := p2_contact_tbl(1);
996: END IF;
997: end loop;
1048:
1049: -- we may not have to check the success status as data may need to be
1050: -- commited even if some txn failed.
1051: -- however in telesales we will never commit so it is fine
1052: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1053: IF p_control_rec.p_commit THEN
1054: IF l_debug_level > 0 THEN
1055: oe_debug_pub.add( 'commiting the txn' ) ;
1056: end if;
1076: -- since in the client messages are displayed if only status is error
1077: IF l_debug_level > 0 THEN
1078: oe_debug_pub.add( ' checking contact failure status = '||l_contact_status ) ;
1079: END IF;
1080: IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1081: IF l_debug_level > 0 THEN
1082: oe_debug_pub.add( ' returning error for contact' ) ;
1083: END IF;
1084: x_return_status := FND_API.G_RET_STS_ERROR;
1080: IF l_contact_status = FND_API.G_RET_STS_ERROR THEN
1081: IF l_debug_level > 0 THEN
1082: oe_debug_pub.add( ' returning error for contact' ) ;
1083: END IF;
1084: x_return_status := FND_API.G_RET_STS_ERROR;
1085:
1086: -- If site has failed then count and get above will catch that
1087: -- else to find the error message for contact we do this
1088: IF NOT l_site_failed then
1097: oe_debug_pub.add( 'p_account_tbl: ' || p_account_tbl.COUNT ) ;
1098:
1099: EXCEPTION
1100:
1101: WHEN FND_API.G_EXC_ERROR THEN
1102:
1103: IF c_check_account%ISOPEN THEN
1104: CLOSE c_check_account;
1105: END IF;
1109: IF c_cust_account_id%ISOPEN THEN
1110: CLOSE c_cust_account_id;
1111: END IF;
1112:
1113: x_return_status := FND_API.G_RET_STS_ERROR;
1114:
1115: -- Get message count and data
1116:
1117: OE_MSG_PUB.Count_And_Get
1118: ( p_count => x_msg_count
1119: , p_data => x_msg_data
1120: );
1121:
1122: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1123:
1124:
1125: IF c_get_cust_account_id%ISOPEN THEN
1126: CLOSE c_get_cust_account_id;
1128: IF c_cust_account_id%ISOPEN THEN
1129: CLOSE c_cust_account_id;
1130: END IF;
1131:
1132: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1133:
1134: -- Get message count and data
1135:
1136: OE_MSG_PUB.Count_And_Get
1147: IF c_cust_account_id%ISOPEN THEN
1148: CLOSE c_cust_account_id;
1149: END IF;
1150:
1151: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1152: IF l_debug_level > 0 THEN
1153: oe_debug_pub.add( 'CREATE_ACCOUNT_LAYER WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
1154: END IF;
1155:
1296: px_party_id := p_party_id;
1297: px_cust_account_id := p_cust_account_id;
1298:
1299: --l_debug_level:=1;
1300: x_return_status := FND_API.G_RET_STS_SUCCESS;
1301: l_return_status := FND_API.G_RET_STS_SUCCESS;
1302:
1303: IF l_debug_level > 0 THEN
1304: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1297: px_cust_account_id := p_cust_account_id;
1298:
1299: --l_debug_level:=1;
1300: x_return_status := FND_API.G_RET_STS_SUCCESS;
1301: l_return_status := FND_API.G_RET_STS_SUCCESS;
1302:
1303: IF l_debug_level > 0 THEN
1304: oe_debug_pub.add(' cust_id = '||p_cust_account_id);
1305: oe_debug_pub.add(' party_id = '||p_party_id);
1306: oe_debug_pub.add(' site tbl count = '||p_SITE_TBL.COUNT ) ;
1307: END IF;
1308:
1309: IF p_cust_account_id is null then
1310: x_return_status := FND_API.G_RET_STS_ERROR;
1311: IF l_debug_level > 0 THEN
1312: oe_debug_pub.add( ' ERROR: cust account id must be sent to check sites' ) ;
1313: END IF;
1314:
1331: l_send_primary := null;
1332: l_primary_site_use := null;
1333: l_end_customer_passed :='N'; --added for bug 4240715
1334:
1335: x_return_status := FND_API.G_RET_STS_SUCCESS;
1336: IF l_debug_level > 0 THEN
1337: oe_debug_pub.add ('AAC:VTI: ========= SITE TABLE RECORD '||i||' of '||p_site_tbl.LAST||' ================== ');
1338: oe_debug_pub.add(' site table rec #'||i);
1339: oe_debug_pub.add(' party_site_use_id = '|| p_site_tbl(i).p_party_site_use_id);
1353: l_end_customer_passed :='Y';
1354: end if;
1355: --bug 4240715}
1356:
1357: if nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM OR
1358: nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1359: then
1360: px_cust_account_id := p_site_tbl(i).p_cust_account_id;
1361: px_party_id := p_site_tbl(i).p_party_id;
1354: end if;
1355: --bug 4240715}
1356:
1357: if nvl(p_site_tbl(i).p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM OR
1358: nvl(p_site_tbl(i).p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
1359: then
1360: px_cust_account_id := p_site_tbl(i).p_cust_account_id;
1361: px_party_id := p_site_tbl(i).p_party_id;
1362: end if;
1367: oe_debug_pub.add( ' site level customer: account_id:'||px_cust_account_id ) ;
1368: END IF;
1369:
1370:
1371: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1372: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1373: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1368: END IF;
1369:
1370:
1371: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1372: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1373: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1369:
1370:
1371: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1372: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1373: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1370:
1371: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1372: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1373: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1371: if ( nvl(p_site_tbl(i).p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1372: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1373: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1372: and nvl(p_site_tbl(i).p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1373: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1373: and nvl(p_site_tbl(i).p_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1374: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1374: and nvl(p_site_tbl(i).p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1375: and nvl(p_site_tbl(i).p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1376: and nvl(p_site_tbl(i).p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1384: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1377: and nvl(p_site_tbl(i).p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1384: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1385: then
1378: and nvl(p_site_tbl(i).p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1384: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1385: then
1386: --nothing to do! return!
1379: and nvl(p_site_tbl(i).p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1384: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1385: then
1386: --nothing to do! return!
1387: IF l_debug_level > 0 THEN
1380: and nvl(p_site_tbl(i).p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1381: and nvl(p_site_tbl(i).p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1382: and nvl(p_site_tbl(i).p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
1383: and nvl(p_site_tbl(i).p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
1384: and nvl(p_site_tbl(i).p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
1385: then
1386: --nothing to do! return!
1387: IF l_debug_level > 0 THEN
1388: oe_debug_pub.add ('AAC: create_sites: no data passed in, likely a contact search; returning');
1393:
1394: IF p_site_tbl(i).p_site_use_code IS NOT NULL THEN
1395: l_site_use_code := p_site_tbl(i).p_site_use_code;
1396: ELSE
1397: l_return_status := FND_API.G_RET_STS_ERROR;
1398: IF l_debug_level > 0 THEN
1399: oe_debug_pub.add( 'site use not specified' ) ;
1400: END IF;
1401: FND_MESSAGE.Set_Name('ONT','ONT_AAC_ERROR');
1437: l_site_use_id := p_site_tbl(i).p_site_use_id;
1438: px_cust_account_id := p_cust_account_id;
1439: px_party_id := p_party_id;
1440: if l_status <> 'A' then
1441: l_return_status := fnd_api.g_ret_sts_error;
1442: IF l_debug_level > 0 THEN
1443: oe_debug_pub.add( 'account site use is inactive' ) ;
1444: END IF;
1445: end if;
1443: oe_debug_pub.add( 'account site use is inactive' ) ;
1444: END IF;
1445: end if;
1446: /* IF l_cust_account_id <> px_cust_account_id then
1447: x_return_status := FND_API.G_RET_STS_ERROR;
1448: l_return_status := FND_API.G_RET_STS_ERROR;
1449: IF l_debug_level > 0 THEN
1450: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1451: END IF;
1444: END IF;
1445: end if;
1446: /* IF l_cust_account_id <> px_cust_account_id then
1447: x_return_status := FND_API.G_RET_STS_ERROR;
1448: l_return_status := FND_API.G_RET_STS_ERROR;
1449: IF l_debug_level > 0 THEN
1450: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1451: END IF;
1452: End if; */
1468: l_site_use_id := p_site_tbl(i).p_site_use_id;
1469: px_cust_account_id := p_cust_account_id;
1470: px_party_id := p_party_id;
1471: if l_status <> 'A' then
1472: l_return_status := fnd_api.g_ret_sts_error;
1473: IF l_debug_level > 0 THEN
1474: oe_debug_pub.add( 'account site use is inactive' ) ;
1475: END IF;
1476: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1476: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1477: OE_MSG_PUB.ADD;
1478: end if;
1479: /* IF l_cust_account_id <> px_cust_account_id then
1480: x_return_status := FND_API.G_RET_STS_ERROR;
1481: l_return_status := FND_API.G_RET_STS_ERROR;
1482: IF l_debug_level > 0 THEN
1483: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1484: END IF;
1477: OE_MSG_PUB.ADD;
1478: end if;
1479: /* IF l_cust_account_id <> px_cust_account_id then
1480: x_return_status := FND_API.G_RET_STS_ERROR;
1481: l_return_status := FND_API.G_RET_STS_ERROR;
1482: IF l_debug_level > 0 THEN
1483: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1484: END IF;
1485: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1503: l_site_use_id := p_site_tbl(i).p_site_use_id;
1504: px_cust_account_id := p_cust_account_id;
1505: px_party_id := p_party_id;
1506: if l_status <> 'A' then
1507: l_return_status := fnd_api.g_ret_sts_error;
1508: IF l_debug_level > 0 THEN
1509: oe_debug_pub.add( 'account site use is inactive' ) ;
1510: END IF;
1511: FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
1511: FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_INACTIVE');
1512: OE_MSG_PUB.ADD;
1513: end if;
1514: /* IF l_cust_account_id <> px_cust_account_id then
1515: x_return_status := FND_API.G_RET_STS_ERROR;
1516: l_return_status := FND_API.G_RET_STS_ERROR;
1517: IF l_debug_level > 0 THEN
1518: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1519: END IF;
1512: OE_MSG_PUB.ADD;
1513: end if;
1514: /* IF l_cust_account_id <> px_cust_account_id then
1515: x_return_status := FND_API.G_RET_STS_ERROR;
1516: l_return_status := FND_API.G_RET_STS_ERROR;
1517: IF l_debug_level > 0 THEN
1518: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1519: END IF;
1520: FND_MESSAGE.Set_Name('ONT','ONT_AAC_BILLTO_ACCOUNT');
1538: l_site_use_id := p_site_tbl(i).p_site_use_id;
1539: px_cust_account_id := p_cust_account_id;
1540: px_party_id := p_party_id;
1541: if l_status <> 'A' then
1542: l_return_status := fnd_api.g_ret_sts_error;
1543: IF l_debug_level > 0 THEN
1544: oe_debug_pub.add( 'account site use is inactive' ) ;
1545: END IF;
1546: FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
1546: FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_INACTIVE');
1547: OE_MSG_PUB.ADD;
1548: end if;
1549: /* IF l_cust_account_id <> px_cust_account_id then
1550: x_return_status := FND_API.G_RET_STS_ERROR;
1551: l_return_status := FND_API.G_RET_STS_ERROR;
1552: IF l_debug_level > 0 THEN
1553: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1554: END IF;
1547: OE_MSG_PUB.ADD;
1548: end if;
1549: /* IF l_cust_account_id <> px_cust_account_id then
1550: x_return_status := FND_API.G_RET_STS_ERROR;
1551: l_return_status := FND_API.G_RET_STS_ERROR;
1552: IF l_debug_level > 0 THEN
1553: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1554: END IF;
1555: FND_MESSAGE.Set_Name('ONT','ONT_AAC_DELIVERTO_ACCOUNT');
1581: oe_debug_pub.add(' primary = '||l_primary_site_use|| ' cust_account_id = '||l_cust_account_id ) ;
1582: end if;
1583:
1584: if l_status <> 'A' then
1585: l_return_status := fnd_api.g_ret_sts_error;
1586: IF l_debug_level > 0 THEN
1587: oe_debug_pub.add( 'account site use is inactive' ) ;
1588: END IF;
1589:
1599: END IF;
1600:
1601: oe_debug_pub.add( 'px_accoun_id:'||px_cust_account_id||'l_accoun_id:'||l_cust_account_id ) ;
1602: /* IF l_cust_account_id <> px_cust_account_id then
1603: x_return_status := FND_API.G_RET_STS_ERROR;
1604: l_return_status := FND_API.G_RET_STS_ERROR;
1605: IF l_debug_level > 0 THEN
1606: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1607: END IF;
1600:
1601: oe_debug_pub.add( 'px_accoun_id:'||px_cust_account_id||'l_accoun_id:'||l_cust_account_id ) ;
1602: /* IF l_cust_account_id <> px_cust_account_id then
1603: x_return_status := FND_API.G_RET_STS_ERROR;
1604: l_return_status := FND_API.G_RET_STS_ERROR;
1605: IF l_debug_level > 0 THEN
1606: oe_debug_pub.add( 'site does not belong to this account, or site account' ) ;
1607: END IF;
1608:
1620:
1621: p_site_tbl(i).p_cust_acct_site_id := l_cust_acct_site_id;
1622:
1623: ELSIF c_site_use%NOTFOUND THEN
1624: x_return_status := FND_API.G_RET_STS_ERROR;
1625: l_return_status := FND_API.G_RET_STS_ERROR;
1626: IF l_debug_level > 0 THEN
1627: oe_debug_pub.add( 'INVALID SITE_USE_ID ' ) ;
1628: END IF;
1621: p_site_tbl(i).p_cust_acct_site_id := l_cust_acct_site_id;
1622:
1623: ELSIF c_site_use%NOTFOUND THEN
1624: x_return_status := FND_API.G_RET_STS_ERROR;
1625: l_return_status := FND_API.G_RET_STS_ERROR;
1626: IF l_debug_level > 0 THEN
1627: oe_debug_pub.add( 'INVALID SITE_USE_ID ' ) ;
1628: END IF;
1629:
1704: we have to use site_use_code and party_site_id
1705: */
1706: -- if party_site_id is sent
1707: IF l_party_site_id is null AND
1708: x_return_status <> FND_API.G_RET_STS_ERROR then
1709: oe_debug_pub.add( 'l_party_site_id is null and p_site_tbl(i).p_party_site_id is '||p_site_tbl(i).p_party_site_id);
1710: IF p_site_tbl(i).p_party_site_id is not null then
1711: IF l_debug_level > 0 THEN
1712: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1719: INTO l_party_id,
1720: l_status;
1721:
1722: IF c_party_site%NOTFOUND THEN
1723: l_return_status := FND_API.G_RET_STS_ERROR;
1724: x_return_status := FND_API.G_RET_STS_ERROR;
1725: IF l_debug_level > 0 THEN
1726: oe_debug_pub.add( 'no such party site id' ) ;
1727: END IF;
1720: l_status;
1721:
1722: IF c_party_site%NOTFOUND THEN
1723: l_return_status := FND_API.G_RET_STS_ERROR;
1724: x_return_status := FND_API.G_RET_STS_ERROR;
1725: IF l_debug_level > 0 THEN
1726: oe_debug_pub.add( 'no such party site id' ) ;
1727: END IF;
1728: CLOSE c_party_site;
1753: END IF;
1754:
1755: IF l_status <> 'A' THEN
1756:
1757: x_return_status := FND_API.G_RET_STS_ERROR;
1758: l_return_status := FND_API.G_RET_STS_ERROR;
1759:
1760: IF l_site_use_code = 'SHIP_TO' then
1761: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1754:
1755: IF l_status <> 'A' THEN
1756:
1757: x_return_status := FND_API.G_RET_STS_ERROR;
1758: l_return_status := FND_API.G_RET_STS_ERROR;
1759:
1760: IF l_site_use_code = 'SHIP_TO' then
1761: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1762: ELSIF l_site_use_code = 'BILL_TO' then
1784:
1785: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1786:
1787: IF px_party_id <> l_party_id THEN
1788: x_return_status := FND_API.G_RET_STS_ERROR;
1789: l_return_status := FND_API.G_RET_STS_ERROR;
1790:
1791: IF l_site_use_code = 'SHIP_TO' then
1792: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1785: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1786:
1787: IF px_party_id <> l_party_id THEN
1788: x_return_status := FND_API.G_RET_STS_ERROR;
1789: l_return_status := FND_API.G_RET_STS_ERROR;
1790:
1791: IF l_site_use_code = 'SHIP_TO' then
1792: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1793: ELSIF l_site_use_code = 'BILL_TO' then
1821:
1822: ELSE -- if party_site_id is null
1823: oe_debug_pub.add( 'Yes.. Party_Site_id is not null');
1824: if l_end_customer_passed ='N' then -- if its not end customer(4240715)
1825: x_return_status := FND_API.G_RET_STS_ERROR;
1826: l_return_status := FND_API.G_RET_STS_ERROR;
1827: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1828: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
1829: OE_MSG_PUB.ADD;
1822: ELSE -- if party_site_id is null
1823: oe_debug_pub.add( 'Yes.. Party_Site_id is not null');
1824: if l_end_customer_passed ='N' then -- if its not end customer(4240715)
1825: x_return_status := FND_API.G_RET_STS_ERROR;
1826: l_return_status := FND_API.G_RET_STS_ERROR;
1827: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
1828: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
1829: OE_MSG_PUB.ADD;
1830: IF l_debug_level > 0 THEN
1865: l_party_site_id,
1866: l_primary_per_type;
1867:
1868: IF c_party_site_use%NOTFOUND THEN
1869: l_return_status := FND_API.G_RET_STS_ERROR;
1870: x_return_status := FND_API.G_RET_STS_ERROR;
1871: IF l_debug_level > 0 THEN
1872: oe_debug_pub.add( 'not a valid party site use id' ) ;
1873: END IF;
1866: l_primary_per_type;
1867:
1868: IF c_party_site_use%NOTFOUND THEN
1869: l_return_status := FND_API.G_RET_STS_ERROR;
1870: x_return_status := FND_API.G_RET_STS_ERROR;
1871: IF l_debug_level > 0 THEN
1872: oe_debug_pub.add( 'not a valid party site use id' ) ;
1873: END IF;
1874:
1911: END IF; -- if party_site_use_id is not null
1912:
1913: -- if party_site_id is sent
1914: IF l_party_site_id is null AND
1915: x_return_status <> FND_API.G_RET_STS_ERROR then
1916: IF p_site_tbl(i).p_party_site_id is not null then
1917: IF l_debug_level > 0 THEN
1918: oe_debug_pub.add( 'checking for party_site_id ' ) ;
1919: END IF;
1923: FETCH c_party_site
1924: INTO l_party_id,
1925: l_status;
1926: IF c_party_site%NOTFOUND THEN
1927: l_return_status := FND_API.G_RET_STS_ERROR;
1928: x_return_status := FND_API.G_RET_STS_ERROR;
1929: IF l_debug_level > 0 THEN
1930: oe_debug_pub.add( 'no such party site id' ) ;
1931: END IF;
1924: INTO l_party_id,
1925: l_status;
1926: IF c_party_site%NOTFOUND THEN
1927: l_return_status := FND_API.G_RET_STS_ERROR;
1928: x_return_status := FND_API.G_RET_STS_ERROR;
1929: IF l_debug_level > 0 THEN
1930: oe_debug_pub.add( 'no such party site id' ) ;
1931: END IF;
1932: CLOSE c_party_site;
1955: oe_debug_pub.add( 'found for party_site_id ' ) ;
1956: END IF;
1957: IF l_status <> 'A' THEN
1958:
1959: x_return_status := FND_API.G_RET_STS_ERROR;
1960: l_return_status := FND_API.G_RET_STS_ERROR;
1961:
1962: IF l_site_use_code = 'SHIP_TO' then
1963: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1956: END IF;
1957: IF l_status <> 'A' THEN
1958:
1959: x_return_status := FND_API.G_RET_STS_ERROR;
1960: l_return_status := FND_API.G_RET_STS_ERROR;
1961:
1962: IF l_site_use_code = 'SHIP_TO' then
1963: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_INACTIVE');
1964: ELSIF l_site_use_code = 'BILL_TO' then
1986:
1987: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1988:
1989: IF px_party_id <> l_party_id THEN
1990: x_return_status := FND_API.G_RET_STS_ERROR;
1991: l_return_status := FND_API.G_RET_STS_ERROR;
1992:
1993: IF l_site_use_code = 'SHIP_TO' then
1994: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1987: oe_debug_pub.add('px: '||px_party_id||' l_party:'||l_party_id);
1988:
1989: IF px_party_id <> l_party_id THEN
1990: x_return_status := FND_API.G_RET_STS_ERROR;
1991: l_return_status := FND_API.G_RET_STS_ERROR;
1992:
1993: IF l_site_use_code = 'SHIP_TO' then
1994: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SHIPTO_ACCOUNT');
1995: ELSIF l_site_use_code = 'BILL_TO' then
2022:
2023: ELSE -- if party_site_id is null
2024: oe_debug_pub.add( 'Yes.. Party_Site_id is not null.. Second');
2025: if l_end_customer_passed ='N' then -- if its not end customer(bug 4240715)
2026: x_return_status := FND_API.G_RET_STS_ERROR;
2027: l_return_status := FND_API.G_RET_STS_ERROR;
2028: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
2029: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
2030: OE_MSG_PUB.ADD;
2023: ELSE -- if party_site_id is null
2024: oe_debug_pub.add( 'Yes.. Party_Site_id is not null.. Second');
2025: if l_end_customer_passed ='N' then -- if its not end customer(bug 4240715)
2026: x_return_status := FND_API.G_RET_STS_ERROR;
2027: l_return_status := FND_API.G_RET_STS_ERROR;
2028: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
2029: FND_MESSAGE.Set_Token('TEXT','Not a Valid Account Site ', FALSE);
2030: OE_MSG_PUB.ADD;
2031: IF l_debug_level > 0 THEN
2107:
2108: <
2109:
2110:
2111: IF x_return_status = FND_API.G_RET_STS_SUCCESS and l_site_use_code <>'END_CUST' THEN
2112:
2113:
2114: IF l_debug_level > 0 THEN
2115: oe_debug_pub.add( ' continuing processing' ) ;
2175: c_attribute25=>null,
2176: in_created_by_module=>G_CREATED_BY_MODULE,
2177: in_primary_flag =>l_send_primary
2178: );
2179: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2180: CLOSE c_acct_site;
2181: IF l_debug_level > 0 THEN
2182: oe_debug_pub.add( ' account site use creation failed' ) ;
2183: END IF;
2184: IF l_debug_level > 0 THEN
2185: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2186: END IF;
2187:
2188: x_return_status := FND_API.G_RET_STS_ERROR;
2189: l_return_status := FND_API.G_RET_STS_ERROR;
2190:
2191: IF x_msg_count = 1 then
2192:
2185: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2186: END IF;
2187:
2188: x_return_status := FND_API.G_RET_STS_ERROR;
2189: l_return_status := FND_API.G_RET_STS_ERROR;
2190:
2191: IF x_msg_count = 1 then
2192:
2193: IF l_site_use_code = 'SHIP_TO' then
2210: return;
2211: END IF;
2212: END IF;
2213: ELSE
2214: x_return_status := FND_API.G_RET_STS_ERROR;
2215: l_return_status := FND_API.G_RET_STS_ERROR;
2216: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2217: OE_MSG_PUB.ADD;
2218: IF l_debug_level > 0 THEN
2211: END IF;
2212: END IF;
2213: ELSE
2214: x_return_status := FND_API.G_RET_STS_ERROR;
2215: l_return_status := FND_API.G_RET_STS_ERROR;
2216: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2217: OE_MSG_PUB.ADD;
2218: IF l_debug_level > 0 THEN
2219: oe_debug_pub.add( ' not authorized to create site' ) ;
2269: x_msg_count => x_msg_count,
2270: x_msg_data => x_msg_data,
2271: in_created_by_module=>G_CREATED_BY_MODULE
2272: ) ;
2273: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2274: CLOSE c_acct_site;
2275: l_return_status := FND_API.G_RET_STS_ERROR;
2276: IF l_debug_level > 0 THEN
2277: oe_debug_pub.add( ' account site creation failed' ) ;
2271: in_created_by_module=>G_CREATED_BY_MODULE
2272: ) ;
2273: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2274: CLOSE c_acct_site;
2275: l_return_status := FND_API.G_RET_STS_ERROR;
2276: IF l_debug_level > 0 THEN
2277: oe_debug_pub.add( ' account site creation failed' ) ;
2278: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2279: END IF;
2341: in_created_by_module=>G_CREATED_BY_MODULE,
2342: in_primary_flag =>l_send_primary
2343: );
2344:
2345: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2346: l_return_status := FND_API.G_RET_STS_ERROR;
2347: CLOSE c_acct_site;
2348: IF l_debug_level > 0 THEN
2349: oe_debug_pub.add( ' account site use creation failed' ) ;
2342: in_primary_flag =>l_send_primary
2343: );
2344:
2345: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2346: l_return_status := FND_API.G_RET_STS_ERROR;
2347: CLOSE c_acct_site;
2348: IF l_debug_level > 0 THEN
2349: oe_debug_pub.add( ' account site use creation failed' ) ;
2350: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2381: END IF; -- if acct site creation succeeded
2382:
2383: ELSE
2384:
2385: x_return_status := FND_API.G_RET_STS_ERROR;
2386: l_return_status := FND_API.G_RET_STS_ERROR;
2387: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2388: OE_MSG_PUB.ADD;
2389: IF l_debug_level > 0 THEN
2382:
2383: ELSE
2384:
2385: x_return_status := FND_API.G_RET_STS_ERROR;
2386: l_return_status := FND_API.G_RET_STS_ERROR;
2387: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2388: OE_MSG_PUB.ADD;
2389: IF l_debug_level > 0 THEN
2390: oe_debug_pub.add( '2 not allowed to create site' ) ;
2419:
2420:
2421: /*cc project*/
2422:
2423: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2424:
2425: IF l_debug_level > 0 THEN
2426: oe_debug_pub.add( ' continuing processing' ) ;
2427: end if;
2486: c_attribute25=>null,
2487: in_created_by_module=>G_CREATED_BY_MODULE,
2488: in_primary_flag =>l_send_primary
2489: );
2490: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2491: CLOSE c_acct_site;
2492: IF l_debug_level > 0 THEN
2493: oe_debug_pub.add( ' account site use creation failed' ) ;
2494: END IF;
2495: IF l_debug_level > 0 THEN
2496: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2497: END IF;
2498:
2499: x_return_status := FND_API.G_RET_STS_ERROR;
2500: l_return_status := FND_API.G_RET_STS_ERROR;
2501:
2502: IF x_msg_count = 1 then
2503:
2496: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2497: END IF;
2498:
2499: x_return_status := FND_API.G_RET_STS_ERROR;
2500: l_return_status := FND_API.G_RET_STS_ERROR;
2501:
2502: IF x_msg_count = 1 then
2503:
2504: IF l_site_use_code = 'SHIP_TO' then
2521: return;
2522: END IF;
2523: END IF;
2524: ELSE
2525: x_return_status := FND_API.G_RET_STS_ERROR;
2526: l_return_status := FND_API.G_RET_STS_ERROR;
2527: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2528: OE_MSG_PUB.ADD;
2529: IF l_debug_level > 0 THEN
2522: END IF;
2523: END IF;
2524: ELSE
2525: x_return_status := FND_API.G_RET_STS_ERROR;
2526: l_return_status := FND_API.G_RET_STS_ERROR;
2527: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2528: OE_MSG_PUB.ADD;
2529: IF l_debug_level > 0 THEN
2530: oe_debug_pub.add( ' not authorized to create site' ) ;
2580: x_msg_count => x_msg_count,
2581: x_msg_data => x_msg_data,
2582: in_created_by_module=>G_CREATED_BY_MODULE
2583: ) ;
2584: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2585: CLOSE c_acct_site;
2586: l_return_status := FND_API.G_RET_STS_ERROR;
2587: IF l_debug_level > 0 THEN
2588: oe_debug_pub.add( ' account site creation failed' ) ;
2582: in_created_by_module=>G_CREATED_BY_MODULE
2583: ) ;
2584: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2585: CLOSE c_acct_site;
2586: l_return_status := FND_API.G_RET_STS_ERROR;
2587: IF l_debug_level > 0 THEN
2588: oe_debug_pub.add( ' account site creation failed' ) ;
2589: oe_debug_pub.add( ' error = '||X_MSG_DATA||' count = '||X_MSG_COUNT ) ;
2590: END IF;
2652: in_created_by_module=>G_CREATED_BY_MODULE,
2653: in_primary_flag =>l_send_primary
2654: );
2655:
2656: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2657: l_return_status := FND_API.G_RET_STS_ERROR;
2658: CLOSE c_acct_site;
2659: IF l_debug_level > 0 THEN
2660: oe_debug_pub.add( ' account site use creation failed' ) ;
2653: in_primary_flag =>l_send_primary
2654: );
2655:
2656: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2657: l_return_status := FND_API.G_RET_STS_ERROR;
2658: CLOSE c_acct_site;
2659: IF l_debug_level > 0 THEN
2660: oe_debug_pub.add( ' account site use creation failed' ) ;
2661: oe_debug_pub.add( 'error = '||x_msg_data||' count = '||x_MSG_COUNT ) ;
2692: END IF; -- if acct site creation succeeded
2693:
2694: ELSE
2695:
2696: x_return_status := FND_API.G_RET_STS_ERROR;
2697: l_return_status := FND_API.G_RET_STS_ERROR;
2698: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2699: OE_MSG_PUB.ADD;
2700: IF l_debug_level > 0 THEN
2693:
2694: ELSE
2695:
2696: x_return_status := FND_API.G_RET_STS_ERROR;
2697: l_return_status := FND_API.G_RET_STS_ERROR;
2698: FND_MESSAGE.Set_Name('ONT','ONT_AAC_SITE_PERMISSION');
2699: OE_MSG_PUB.ADD;
2700: IF l_debug_level > 0 THEN
2701: oe_debug_pub.add( '2 not allowed to create site' ) ;
2722: END IF;
2723: END IF; -- if site_use_id is passed
2724:
2725: <
2726: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2727:
2728:
2729:
2730: IF l_debug_level > 0 THEN
2762:
2763:
2764: x_return_status := l_return_status;
2765:
2766: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2767: IF l_debug_level > 0 THEN
2768: oe_debug_pub.add( ' doing count and get for site' ) ;
2769: END IF;
2770: OE_MSG_PUB.Count_And_Get
2777: END IF;
2778:
2779: EXCEPTION
2780:
2781: WHEN FND_API.G_EXC_ERROR THEN
2782:
2783: IF c_party_site_use%ISOPEN THEN
2784: CLOSE c_party_site_use;
2785: END IF;
2788: END IF;
2789: IF c_acct_site%ISOPEN THEN
2790: CLOSE c_acct_site;
2791: END IF;
2792: x_return_status := FND_API.G_RET_STS_ERROR;
2793:
2794: -- Get message count and data
2795:
2796: OE_MSG_PUB.Count_And_Get
2797: ( p_count => x_msg_count
2798: , p_data => x_msg_data
2799: );
2800:
2801: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2802:
2803:
2804: IF c_party_site_use%ISOPEN THEN
2805: CLOSE c_party_site_use;
2809: END IF;
2810: IF c_acct_site%ISOPEN THEN
2811: CLOSE c_acct_site;
2812: END IF;
2813: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2814:
2815: -- Get message count and data
2816:
2817: OE_MSG_PUB.Count_And_Get
2830: END IF;
2831: IF c_acct_site%ISOPEN THEN
2832: CLOSE c_acct_site;
2833: END IF;
2834: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2835: IF l_debug_level > 0 THEN
2836: oe_debug_pub.add( 'check_and_create_site when other code = '|| sqlcode||' message = '||sqlerrm ) ;
2837: END IF;
2838:
3012: END IF;
3013: END IF;
3014:
3015:
3016: x_return_status := FND_API.G_RET_STS_SUCCESS;
3017: p_contact_tbl.DELETE;
3018: p_multiple_account := FALSE;
3019:
3020:
3020:
3021: -- atleast some kind of account information is needed
3022: IF p_cust_account_id is null AND
3023: p_cust_account_role_id is null then
3024: x_return_status := FND_API.G_RET_STS_ERROR;
3025: IF l_debug_level > 0 THEN
3026: oe_debug_pub.add(l_indent|| ' not a valid call to check contact' ) ;
3027: END IF;
3028: return;
3040: FETCH c_get_cust_id
3041: INTO l_cust_account_id;
3042:
3043: IF c_get_cust_id%NOTFOUND THEN
3044: x_return_status := FND_API.G_RET_STS_ERROR;
3045: IF l_debug_level > 0 THEN
3046: oe_debug_pub.add( l_indent|| 'not a valid cust account role id' ) ;
3047: END IF;
3048: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3116: IF l_debug_level > 0 THEN
3117: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3118: END IF;
3119: CLOSE c_cust_role_site;
3120: x_return_status := FND_API.G_RET_STS_ERROR;
3121: return;
3122:
3123: END IF;
3124:
3126: IF l_debug_level > 0 THEN
3127: oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3128: END IF;
3129: CLOSE c_cust_role_site;
3130: x_return_status := FND_API.G_RET_STS_ERROR;
3131: return;
3132:
3133: END IF;
3134:
3136: IF l_debug_level > 0 THEN
3137: oe_debug_pub.add(l_indent|| 'acct role does not belong to this acct' ) ;
3138: END IF;
3139: CLOSE c_cust_role_site;
3140: x_return_status := FND_API.G_RET_STS_ERROR;
3141: return;
3142: END IF;
3143:
3144:
3146: IF l_debug_level > 0 THEN
3147: oe_debug_pub.add( l_indent|| 'Cust account role for site is not valid' ) ;
3148: END IF;
3149: CLOSE c_cust_role_site;
3150: x_return_status := FND_API.G_RET_STS_ERROR;
3151: return;
3152:
3153: END IF;
3154:
3175: IF c_cust_account_role%NOTFOUND then
3176:
3177: CLOSE c_cust_account_role;
3178:
3179: x_return_status := FND_API.G_RET_STS_ERROR;
3180: IF l_debug_level > 0 THEN
3181: oe_debug_pub.add(l_indent|| '2 contact_id not found' ) ;
3182: END IF;
3183: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_NO_CONTACT');
3194: IF l_debug_level > 0 THEN
3195: oe_debug_pub.add( l_indent|| 'cust account role is not active' ) ;
3196: END IF;
3197: CLOSE c_cust_account_role;
3198: x_return_status := FND_API.G_RET_STS_ERROR;
3199: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INACTIVE_CONTACT');
3200: OE_MSG_PUB.ADD;
3201: OE_MSG_PUB.Count_And_Get
3202: ( p_count => x_msg_count
3210: IF l_debug_level > 0 THEN
3211: oe_debug_pub.add( l_indent|| 'cust account role type is not valid' ) ;
3212: END IF;
3213: CLOSE c_cust_account_role;
3214: x_return_status := FND_API.G_RET_STS_ERROR;
3215: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ROLE_CONTACT');
3216: OE_MSG_PUB.ADD;
3217: OE_MSG_PUB.Count_And_Get
3218: ( p_count => x_msg_count
3227: IF l_debug_level > 0 THEN
3228: oe_debug_pub.add( l_indent|| 'Account role does not belong to this acct' ) ;
3229: END IF;
3230: CLOSE c_cust_account_role;
3231: x_return_status := FND_API.G_RET_STS_ERROR;
3232: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_ACCOUNT');
3233: OE_MSG_PUB.ADD;
3234: OE_MSG_PUB.Count_And_Get
3235: ( p_count => x_msg_count
3268: IF c_check_org_contact%NOTFOUND then
3269: IF l_debug_level > 0 THEN
3270: oe_debug_pub.add(l_indent|| 'org contact_id does not belong to sent party or '|| ' contact may be inactive' ) ;
3271: END IF;
3272: x_return_status := FND_API.G_RET_STS_ERROR;
3273: CLOSE c_check_org_contact;
3274: FND_MESSAGE.Set_Name('ONT','ONT_AAC_CONTACT_ACCOUNT');
3275: OE_MSG_PUB.ADD;
3276: OE_MSG_PUB.Count_And_Get
3320: IF p_contact_tbl.COUNT > 1 then
3321: IF l_debug_level > 0 THEN
3322: oe_debug_pub.add(l_indent|| 'error >1 contact for org_contact_id' ) ;
3323: END IF;
3324: x_return_status := FND_API.G_RET_STS_ERROR;
3325: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3326: FND_MESSAGE.Set_Token('TEXT',' Multiple Contacts ', FALSE);
3327: OE_MSG_PUB.ADD;
3328: OE_MSG_PUB.Count_And_Get
3337: IF p_contact_tbl.COUNT = 1 then
3338: IF l_debug_level > 0 THEN
3339: oe_debug_pub.add( l_indent|| 'RETURNING WITH ONE CONTACT' ) ;
3340: END IF;
3341: x_return_status := FND_API.G_RET_STS_SUCCESS;
3342: return;
3343: ELSIF p_contact_tbl.COUNT = 0 then
3344: IF l_debug_level > 0 THEN
3345: oe_debug_pub.add( l_indent|| 'no contact for account found. creating '|| ' acct_id = '||p_cust_account_id|| ' rel_party_id = '||L_REL_PARTY_ID ) ;
3404: c2_attribute20=>null,
3405: in_Created_by_module=>G_CREATED_BY_MODULE
3406: );
3407:
3408: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3409:
3410: x_return_status := l_return_status;
3411: x_msg_data := l_msg_data;
3412: x_msg_count := l_msg_count;
3428: , p_data => x_msg_data
3429: );
3430: return;
3431: ELSE
3432: x_return_status := FND_API.G_RET_STS_SUCCESS;
3433: IF l_debug_level > 0 THEN
3434: oe_debug_pub.add( l_indent|| ' contact creation succeeded '|| ' cust acct_role_id = '||P_CONTACT_TBL ( 1 ) ) ;
3435: END IF;
3436:
3435: END IF;
3436:
3437: END IF;
3438: ELSE
3439: x_return_status := FND_API.G_RET_STS_ERROR;
3440: IF l_debug_level > 0 THEN
3441: oe_debug_pub.add( l_indent|| 'not allowed to create contact' ) ;
3442: end IF;
3443: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_CONTACT_PERMISSION');
3462: IF p_contact_tbl.COUNT = 0 then
3463: IF l_debug_level > 0 THEN
3464: oe_debug_pub.add( l_indent|| 'equal to ZERO' ) ;
3465: END IF;
3466: x_return_status := FND_API.G_RET_STS_SUCCESS;
3467: return;
3468:
3469: -- if one or more then check the account contact appropiately
3470: -- take care of account site and responsibility
3472: ELSIF p_Contact_tbl.COUNT >0 then
3473: IF l_debug_level > 0 THEN
3474: oe_debug_pub.add( l_indent|| 'one or more than one' ) ;
3475: END IF;
3476: x_return_status := FND_API.G_RET_STS_SUCCESS;
3477: return;
3478: END IF;
3479:
3480: END IF; -- if not to be assigned
3500:
3501:
3502: EXCEPTION
3503:
3504: WHEN FND_API.G_EXC_ERROR THEN
3505:
3506: x_return_status := FND_API.G_RET_STS_ERROR;
3507:
3508: -- Get message count and data
3502: EXCEPTION
3503:
3504: WHEN FND_API.G_EXC_ERROR THEN
3505:
3506: x_return_status := FND_API.G_RET_STS_ERROR;
3507:
3508: -- Get message count and data
3509:
3510: OE_MSG_PUB.Count_And_Get
3511: ( p_count => x_msg_count
3512: , p_data => x_msg_data
3513: );
3514:
3515: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3516:
3517: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3518:
3519: -- Get message count and data
3513: );
3514:
3515: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3516:
3517: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3518:
3519: -- Get message count and data
3520:
3521: OE_MSG_PUB.Count_And_Get
3524: );
3525:
3526: WHEN OTHERS THEN
3527:
3528: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3529: IF l_debug_level > 0 THEN
3530: oe_debug_pub.add( 'CHECK_CONTACT WHEN OTHER EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
3531: END IF;
3532:
3608:
3609: --
3610: BEGIN
3611:
3612: x_return_status := FND_API.G_RET_STS_SUCCESS;
3613: p_account_tbl.DELETE;
3614:
3615: IF l_debug_level > 0 THEN
3616: oe_debug_pub.add(l_indent||'party_id = '||p_party_id|| '; party_number = '||p_party_number);
3647: INTO l_party_id,
3648: l_party_type;
3649:
3650: IF party_number_rec%NOTFOUND THEN
3651: x_return_status := FND_API.G_RET_STS_ERROR;
3652: IF l_debug_level > 0 THEN
3653: oe_debug_pub.add(l_indent|| 'no such party found for party_number' ) ;
3654: END IF;
3655: CLOSE party_number_rec;
3665: FETCH party_rec
3666: INTO l_party_type;
3667:
3668: IF party_rec%NOTFOUND THEN
3669: x_return_status := FND_API.G_RET_STS_ERROR;
3670: IF l_debug_level > 0 THEN
3671: oe_debug_pub.add(l_indent|| 'no such party found for party_id' ) ;
3672: END IF;
3673: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_PARTY');
3714: IF C_get_cust_id_from_party_id%ROWCOUNT > 1 then
3715: IF l_debug_level > 0 THEN
3716: oe_debug_pub.add( l_indent|| 'more than one account for party id' ) ;
3717: END IF;
3718: x_return_status := FND_API.G_RET_STS_ERROR;
3719: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
3720: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
3721: OE_MSG_PUB.ADD;
3722: EXIT;
3747:
3748:
3749: ELSE -- if not person or organization
3750:
3751: x_return_status := FND_API.G_RET_STS_ERROR;
3752: IF l_debug_level > 0 THEN
3753: oe_debug_pub.add(l_indent|| ' invalid party type' ) ;
3754: end IF;
3755: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_INVALID_PARTY');
3769: IF l_debug_level > 0 THEN
3770: oe_debug_pub.add(l_indent|| 'before checking to create cust account' ) ;
3771: END IF;
3772: -- create customer account
3773: IF x_return_status <> FND_API.G_RET_STS_ERROR AND
3774: NOT p_multiple_account AND
3775: l_cust_account_id IS NULL THEN
3776:
3777: IF p_allow_account_creation THEN
3784: -- user should select it in the Add Customer form
3785: IF l_debug_level > 0 THEN
3786: oe_debug_pub.add( l_indent|| ' going to call add customer' ) ;
3787: END IF;
3788: x_return_status := FND_API.G_RET_STS_SUCCESS;
3789: return;
3790:
3791: ELSE
3792: IF l_party_id is not NULL THEN
3865: oe_debug_pub.add(l_indent||' x_cust_id = '||X_CUST_ACCOUNT_ID|| '; x_cust_nbr = '||X_CUST_ACCOUNT_NUMBER);
3866: oe_debug_pub.add(l_indent||' l_msg_count ='|| L_MSG_COUNT|| '; l_msg_data = '||L_MSG_DATA ) ;
3867: END IF;
3868:
3869: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3870: IF l_debug_level > 0 THEN
3871: oe_debug_pub.add( l_indent|| ' account creation failed' ) ;
3872: END IF;
3873: IF x_msg_count = 1 then
3898: then we have to open the add Customer Form to the user. So returning
3899: status as success instead of error.*/
3900:
3901: IF G_CREATED_BY_MODULE <> 'ONT_TELESERVICE_INTEGRATION' THEN
3902: x_return_status := FND_API.G_RET_STS_ERROR;
3903: IF l_debug_level > 0 THEN
3904: oe_debug_pub.add(l_indent|| 'not allowed to create account' ) ;
3905: END IF;
3906: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
3920:
3921: --oe_debug_pub.add('p_out_cust_account_id = '|| p_account_tbl(1));
3922:
3923: ELSE
3924: --x_return_status := FND_API.G_RET_STS_ERROR;
3925: FND_MESSAGE.Set_Name('ONT', 'ONT_AVAIL_GENERIC');
3926: IF l_debug_level > 0 THEN
3927: oe_debug_pub.add(l_indent|| 'party_id or party number is not passed' ) ;
3928: END IF;
3935:
3936: IF l_debug_level > 0 THEN
3937: oe_debug_pub.add(l_indent|| 'create account at the end' ) ;
3938: END IF;
3939: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3940: IF l_debug_level > 0 THEN
3941: oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3942: END IF;
3943: oe_msg_pub.count_and_get(p_encoded=>fnd_api.G_TRUE,
3939: IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
3940: IF l_debug_level > 0 THEN
3941: oe_debug_pub.add(l_indent|| 'doing count_and_get' ) ;
3942: END IF;
3943: oe_msg_pub.count_and_get(p_encoded=>fnd_api.G_TRUE,
3944: p_count => x_msg_count,
3945: p_data=>x_msg_data
3946: );
3947: IF l_debug_level > 0 THEN
3955: END IF;
3956:
3957: EXCEPTION
3958:
3959: WHEN FND_API.G_EXC_ERROR THEN
3960:
3961: IF C_get_cust_id_from_party_id%ISOPEN THEN
3962: CLOSE c_get_cust_id_from_party_id;
3963: END IF;
3967: IF party_number_rec%ISOPEN THEN
3968: CLOSE party_number_rec;
3969: END IF;
3970:
3971: x_return_status := FND_API.G_RET_STS_ERROR;
3972:
3973: -- Get message count and data
3974:
3975: OE_MSG_PUB.Count_And_Get
3976: ( p_count => x_msg_count
3977: , p_data => x_msg_data
3978: );
3979:
3980: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3981:
3982:
3983: IF C_get_cust_id_from_party_id%ISOPEN THEN
3984: CLOSE c_get_cust_id_from_party_id;
3989: IF party_number_rec%ISOPEN THEN
3990: CLOSE party_number_rec;
3991: END IF;
3992:
3993: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3994:
3995: -- Get message count and data
3996:
3997: OE_MSG_PUB.Count_And_Get
4011: IF party_number_rec%ISOPEN THEN
4012: CLOSE party_number_rec;
4013: END IF;
4014:
4015: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4016: IF l_debug_level > 0 THEN
4017: oe_debug_pub.add( 'CHECK_ACCOUNT WHEN OTHERS EXCEPTION CODE='|| SQLCODE||' MESSAGE='||SQLERRM ) ;
4018: END IF;
4019:
4085: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4086: --
4087: BEGIN
4088:
4089: x_return_status := FND_API.G_RET_STS_SUCCESS;
4090: p_account_tbl.DELETE;
4091:
4092: IF l_debug_level > 0 THEN
4093: oe_debug_pub.add( ' PARTY_ID='||P_PARTY_ID|| ' PARTY_NUMBER ='||P_PARTY_NUMBER ) ;
4106: IF party_number_rec%NOTFOUND THEN
4107: IF l_debug_level > 0 THEN
4108: oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_NUMBER' ) ;
4109: END IF;
4110: x_return_status := FND_API.G_RET_STS_ERROR;
4111: FND_MESSAGE.Set_Name('ONT','ONT_AACC_NO_ACCOUNT');
4112: OE_MSG_PUB.ADD;
4113: CLOSE party_number_rec;
4114: return;
4124: IF party_rec%NOTFOUND THEN
4125: IF l_debug_level > 0 THEN
4126: oe_debug_pub.add( 'NO SUCH PARTY FOUND FOR PARTY_ID' ) ;
4127: END IF;
4128: x_return_status := FND_API.G_RET_STS_ERROR;
4129: FND_MESSAGE.Set_Name('ONT','ONT_AAC_NO_ACCOUNT');
4130: OE_MSG_PUB.ADD;
4131: CLOSE party_rec;
4132: return;
4179: ELSE
4180: IF l_debug_level > 0 THEN
4181: oe_debug_pub.add( 'INVALID PARTY TYPE' ) ;
4182: END IF;
4183: x_return_status := FND_API.G_RET_STS_ERROR;
4184: FND_MESSAGE.Set_Name('ONT','ONT_AAC_INVALID_PARTY');
4185: OE_MSG_PUB.ADD;
4186: return;
4187: END IF;
4189: ELSE
4190: IF l_debug_level > 0 THEN
4191: oe_debug_pub.add( 'NO PARTY INFORMATION SENT' ) ;
4192: END IF;
4193: x_return_status := FND_API.G_RET_STS_ERROR;
4194: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4195: FND_MESSAGE.Set_Token('TEXT','No Customer Information ', FALSE);
4196: OE_MSG_PUB.ADD;
4197: return;
4223: l_dummy4 boolean := false;
4224:
4225: BEGIN
4226:
4227: x_return_status := FND_API.G_RET_STS_SUCCESS;
4228:
4229: IF l_debug_level > 0 THEN
4230: oe_debug_pub.add ('AAC:VTI: starting Value-To-Id');
4231: oe_debug_pub.add ('AAC:VTI: looking for header level stuff');
4232: END IF;
4233:
4234:
4235: -- if both party_id and account_id are missing, call find_sold_to_id
4236: if (nvl(p_party_customer_rec.p_party_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4237: OR nvl(p_party_customer_rec.p_cust_account_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4238: then
4239:
4240: -- calling find_sold_to_id
4233:
4234:
4235: -- if both party_id and account_id are missing, call find_sold_to_id
4236: if (nvl(p_party_customer_rec.p_party_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4237: OR nvl(p_party_customer_rec.p_cust_account_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4238: then
4239:
4240: -- calling find_sold_to_id
4241: find_sold_to_id(
4252: ,p_process_site => l_dummy4
4253: ,x_return_status => x_return_status
4254: );
4255:
4256: IF x_return_status = FND_API.G_RET_STS_ERROR then
4257: -- not found a party_id/cust_account_id
4258: -- error message already logged inside find_sold_to_id
4259: IF l_debug_level > 0 THEN
4260: oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for header');
4269: oe_debug_pub.add ('AAC:VTI: looking for sold_to contact');
4270: END IF;
4271:
4272: -- if both org_contact_id and account_role_id are missing, call find_contact_id
4273: if (nvl(p_party_customer_rec.p_org_contact_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4274: and nvl(p_party_customer_rec.p_cust_account_role_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4275: then
4276: find_contact_id( p_contact_id => p_party_customer_rec.p_org_contact_id
4277: ,p_cust_contact_id => p_party_customer_rec.p_cust_account_role_id
4270: END IF;
4271:
4272: -- if both org_contact_id and account_role_id are missing, call find_contact_id
4273: if (nvl(p_party_customer_rec.p_org_contact_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM
4274: and nvl(p_party_customer_rec.p_cust_account_role_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM)
4275: then
4276: find_contact_id( p_contact_id => p_party_customer_rec.p_org_contact_id
4277: ,p_cust_contact_id => p_party_customer_rec.p_cust_account_role_id
4278: ,p_contact_name => p_party_customer_rec.p_contact_name
4285: );
4286:
4287: end if;
4288:
4289: x_return_status := FND_API.G_RET_STS_SUCCESS;
4290:
4291: IF p_site_tbl.COUNT <> 0 then
4292: for i in p_site_tbl.FIRST..p_site_tbl.LAST loop
4293: -- do value-to-id for each site record
4313: ,p_process_site => p_site_tbl(i).p_process_site
4314: ,x_return_status => x_return_status
4315: );
4316:
4317: IF x_return_status = FND_API.G_RET_STS_ERROR then
4318: -- not found a party_id/cust_account_id
4319: -- error message already logged inside find_sold_to_id
4320: IF l_debug_level > 0 THEN
4321: oe_debug_pub.add ('AAC:VTI:sold_to_id account/party not found for line#'||i);
4341: ,p_site_use_code => p_site_tbl(i).p_site_use_code --bug 4240715
4342: ,x_return_status => x_return_status
4343: );
4344:
4345: x_return_status := FND_API.G_RET_STS_SUCCESS;
4346: -- do site v-t-i
4347: oe_debug_pub.add('P2:'||p_site_tbl(i).p_site_use_id);
4348: find_site_id(
4349: p_site_use_id => p_site_tbl(i).p_party_site_use_id
4368: ,x_msg_count => x_msg_count
4369: ,x_msg_data => x_msg_data
4370: );
4371: oe_debug_pub.add('P3:'||p_site_tbl(i).p_site_use_id);
4372: IF x_return_status = FND_API.G_RET_STS_ERROR then
4373: -- not found a party_id/cust_account_id
4374: -- error message already logged inside find_sold_to_id
4375: IF l_debug_level > 0 THEN
4376: oe_debug_pub.add ('AAC:VTI:site_use_id account/party not found for line#'||1);
4413: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
4414:
4415: BEGIN
4416:
4417: x_return_status := FND_API.G_RET_STS_SUCCESS;
4418:
4419: IF l_debug_level > 0 THEN
4420: oe_debug_pub.add ('AAC:VTI: FSTI: starting find_sold_to_id');
4421: END IF;
4420: oe_debug_pub.add ('AAC:VTI: FSTI: starting find_sold_to_id');
4421: END IF;
4422:
4423: -- check if party_id and cust_account_id are not null, return if so
4424: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4425: and nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4426: THEN
4427: IF l_debug_level > 0 THEN
4428: oe_debug_pub.add ('AAC:FSTI: party_id and account_id are not null, nothing to do: returning');
4421: END IF;
4422:
4423: -- check if party_id and cust_account_id are not null, return if so
4424: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4425: and nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4426: THEN
4427: IF l_debug_level > 0 THEN
4428: oe_debug_pub.add ('AAC:FSTI: party_id and account_id are not null, nothing to do: returning');
4429: END IF;
4434: oe_debug_pub.add ('AAC:FSTI: party_id: '||p_party_id||'; cust_account_id: '||p_cust_account_id);
4435: END IF;
4436: END IF;
4437:
4438: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4439: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4440: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4435: END IF;
4436: END IF;
4437:
4438: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4439: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4440: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4443: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4436: END IF;
4437:
4438: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4439: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4440: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4443: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4437:
4438: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4439: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4440: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4443: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4445: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4438: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4439: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4440: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4443: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4445: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4446: then
4439: and nvl(p_party_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4440: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4443: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4445: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4446: then
4447: --nothing to do! return!
4440: and nvl(p_party_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4441: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4443: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4445: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4446: then
4447: --nothing to do! return!
4448: IF l_debug_level > 0 THEN
4441: and nvl(p_party_name ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4442: and nvl(p_party_number ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4443: and nvl(p_cust_account_number,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
4444: and nvl(p_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4445: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4446: then
4447: --nothing to do! return!
4448: IF l_debug_level > 0 THEN
4449: oe_debug_pub.add ('AAC:FSTI: no data passed in, seting process_site to false, returning');
4462: oe_debug_pub.add('p_party_id :'||p_party_id);
4463:
4464: begin
4465: -- check if site_use_id is passed
4466: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4467: nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM ) then
4468:
4469: IF l_debug_level > 0 THEN
4470: oe_debug_pub.add ('AAC:FSTI: site_use_id:'||p_site_use_id||' is not null, cust_account_id is null: getting cust_account_id');
4463:
4464: begin
4465: -- check if site_use_id is passed
4466: if (nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM and
4467: nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM ) then
4468:
4469: IF l_debug_level > 0 THEN
4470: oe_debug_pub.add ('AAC:FSTI: site_use_id:'||p_site_use_id||' is not null, cust_account_id is null: getting cust_account_id');
4471: END IF;
4479:
4480: end if;
4481:
4482: -- check if cust_account_id is not null, return if so
4483: if nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM and
4484: nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
4485:
4486: IF l_debug_level > 0 THEN
4487: oe_debug_pub.add ('AAC:FSTI: cust_account_id:'||p_cust_account_id||' is not null, party_id is null: getting party_id');
4480: end if;
4481:
4482: -- check if cust_account_id is not null, return if so
4483: if nvl(p_cust_account_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM and
4484: nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
4485:
4486: IF l_debug_level > 0 THEN
4487: oe_debug_pub.add ('AAC:FSTI: cust_account_id:'||p_cust_account_id||' is not null, party_id is null: getting party_id');
4488: END IF;
4508: IF l_debug_level > 4 THEN
4509: oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4510: END IF;
4511:
4512: x_return_status := FND_API.G_RET_STS_ERROR;
4513: return;
4514:
4515: WHEN OTHERS THEN
4516:
4523: --END IF;
4524: IF l_debug_level > 4 THEN
4525: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4526: END IF;
4527: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4528:
4529: end;
4530:
4531:
4536: ,p_site_use_code => p_party_site_use_code
4537: );
4538:
4539: -- if account found, return
4540: IF nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4541: THEN
4542: p_cust_account_id := NULL;
4543:
4544: else
4547: oe_debug_pub.add ('AAC:FSTI: found sold_to_org_id/cust_account_id:'||p_cust_account_id);
4548: END IF;
4549:
4550: -- also find the party_id for this account_id
4551: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4552: begin
4553: select party_id
4554: into p_party_id
4555: from hz_cust_accounts
4571: IF l_debug_level > 4 THEN
4572: oe_debug_pub.add ('AAC:VTI: invalid cust_account_id ');
4573: END IF;
4574:
4575: x_return_status := FND_API.G_RET_STS_ERROR;
4576: return;
4577:
4578: WHEN OTHERS THEN
4579:
4586: --END IF;
4587: IF l_debug_level > 4 THEN
4588: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4589: END IF;
4590: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4591:
4592: end;
4593: end if;
4594: return;
4593: end if;
4594: return;
4595: end if;
4596:
4597: if (nvl(p_party_number,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4598: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4599: then
4600: IF l_debug_level > 0 THEN
4601: oe_debug_pub.add ('AAC:FSTI: party_number:'|| p_party_number||' is not null, picking up party_id from it');
4594: return;
4595: end if;
4596:
4597: if (nvl(p_party_number,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4598: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4599: then
4600: IF l_debug_level > 0 THEN
4601: oe_debug_pub.add ('AAC:FSTI: party_number:'|| p_party_number||' is not null, picking up party_id from it');
4602: END IF;
4620: IF l_debug_level > 4 THEN
4621: oe_debug_pub.add ('AAC:VTI: invalid party_number ');
4622: END IF;
4623:
4624: x_return_status := FND_API.G_RET_STS_ERROR;
4625: p_party_id := null;
4626: return;
4627:
4628: WHEN OTHERS THEN
4633: --END IF;
4634: IF l_debug_level > 4 THEN
4635: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4636: END IF;
4637: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4638:
4639: end;
4640: end if;
4641:
4639: end;
4640: end if;
4641:
4642:
4643: if (nvl(p_party_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4644: and nvl(p_party_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4645: then
4646: IF l_debug_level > 0 THEN
4647: oe_debug_pub.add ('AAC:FSTI: party_site_use_id:'|| p_party_site_use_id||' is not null, picking up party_site_id from it');
4640: end if;
4641:
4642:
4643: if (nvl(p_party_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4644: and nvl(p_party_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4645: then
4646: IF l_debug_level > 0 THEN
4647: oe_debug_pub.add ('AAC:FSTI: party_site_use_id:'|| p_party_site_use_id||' is not null, picking up party_site_id from it');
4648: END IF;
4665: IF l_debug_level > 4 THEN
4666: oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
4667: END IF;
4668:
4669: x_return_status := FND_API.G_RET_STS_ERROR;
4670: p_party_site_id := null;
4671: return;
4672:
4673: WHEN OTHERS THEN
4678: --END IF;
4679: IF l_debug_level > 4 THEN
4680: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4681: END IF;
4682: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4683:
4684: end;
4685: end if;
4686:
4683:
4684: end;
4685: end if;
4686:
4687: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4688: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4689: then
4690: IF l_debug_level > 0 THEN
4691: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up party_id from it');
4684: end;
4685: end if;
4686:
4687: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4688: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4689: then
4690: IF l_debug_level > 0 THEN
4691: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up party_id from it');
4692: END IF;
4709: IF l_debug_level > 4 THEN
4710: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
4711: END IF;
4712:
4713: x_return_status := FND_API.G_RET_STS_ERROR;
4714: return;
4715:
4716: WHEN OTHERS THEN
4717:
4721: --END IF;
4722: IF l_debug_level > 4 THEN
4723: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4724: END IF;
4725: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4726:
4727: end;
4728: end if;
4729:
4727: end;
4728: end if;
4729:
4730: /*
4731: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4732: and nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4733: then
4734: IF l_debug_level > 0 THEN
4735: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust site_use_id from it');
4728: end if;
4729:
4730: /*
4731: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4732: and nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
4733: then
4734: IF l_debug_level > 0 THEN
4735: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust site_use_id from it');
4736: END IF;
4764: --END IF;
4765: IF l_debug_level > 4 THEN
4766: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4767: END IF;
4768: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4769:
4770: end;
4771: end if;*/
4772:
4770: end;
4771: end if;*/
4772:
4773: /*
4774: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4775: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4776: then
4777: IF l_debug_level > 0 THEN
4778: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust_account_id from it via account_sites');
4771: end if;*/
4772:
4773: /*
4774: if (nvl(p_party_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4775: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4776: then
4777: IF l_debug_level > 0 THEN
4778: oe_debug_pub.add ('AAC:FSTI: party_site_id:'|| p_party_site_id||' is not null, picking up cust_account_id from it via account_sites');
4779: END IF;
4797: oe_debug_pub.add ('AAC:VTI: party_site_id: multiple accounts ');
4798: END IF;
4799:
4800: p_cust_account_id := null;
4801: x_return_status := FND_API.G_RET_STS_ERROR;
4802: p_cust_account_id := null;
4803: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4804: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4805: OE_MSG_PUB.ADD;
4814: --END IF;
4815: IF l_debug_level > 4 THEN
4816: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4817: END IF;
4818: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4819:
4820: end;
4821: end if;*/
4822:
4821: end if;*/
4822:
4823:
4824: /*
4825: if (nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4826: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4827: then
4828: IF l_debug_level > 0 THEN
4829: oe_debug_pub.add ('AAC:FSTI: site_id:'|| p_site_id||' is not null, picking up cust_account_id from it');
4822:
4823:
4824: /*
4825: if (nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
4826: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4827: then
4828: IF l_debug_level > 0 THEN
4829: oe_debug_pub.add ('AAC:FSTI: site_id:'|| p_site_id||' is not null, picking up cust_account_id from it');
4830: END IF;
4847: IF l_debug_level > 4 THEN
4848: oe_debug_pub.add ('AAC:VTI: site_id: multiple accounts, returning with error ');
4849: END IF;
4850:
4851: x_return_status := FND_API.G_RET_STS_ERROR;
4852: p_cust_account_id := null;
4853: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4854: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4855: OE_MSG_PUB.ADD;
4864: --END IF;
4865: IF l_debug_level > 4 THEN
4866: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id');
4867: END IF;
4868: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4869:
4870: end;
4871: end if;
4872: */
4874: -- We have a party_id, then look for account_id using it
4875: -- multiple matches is an error
4876: -- single/zero match is ok
4877:
4878: if nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4879: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4880: IF l_debug_level > 0 THEN
4881: oe_debug_pub.add ('AAC:FSTI: party_id:'|| p_party_id||' is not null, picking up account_id from it');
4882: END IF;
4875: -- multiple matches is an error
4876: -- single/zero match is ok
4877:
4878: if nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
4879: and nvl(p_cust_account_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
4880: IF l_debug_level > 0 THEN
4881: oe_debug_pub.add ('AAC:FSTI: party_id:'|| p_party_id||' is not null, picking up account_id from it');
4882: END IF;
4883:
4893: IF l_debug_level > 0 THEN
4894: oe_debug_pub.add ('AAC:FSTI: ERROR: multiple accounts found');
4895: END IF;
4896:
4897: --x_return_status := FND_API.G_RET_STS_ERROR;
4898: p_cust_account_id := null;
4899: FND_MESSAGE.Set_Name('ONT','ONT_AVAIL_GENERIC');
4900: FND_MESSAGE.Set_Token('TEXT','Multiple Accounts Exist', FALSE);
4901: OE_MSG_PUB.ADD;
4937: END IF;
4938: FND_MESSAGE.Set_Name('ONT', 'ONT_AAC_ACCOUNT_PERMISSION');
4939: OE_MSG_PUB.ADD;
4940: p_cust_account_id := NULL;
4941: x_return_status := FND_API.G_RET_STS_ERROR;
4942: return;
4943: END IF;
4944:
4945: -- at this point:
4956: p_party_site_use_code => p_party_site_use_code
4957: );
4958:
4959:
4960: IF nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
4961: THEN
4962: -- didn't find a party_id either
4963: -- since we *cannot* create a party, error out
4964: IF l_debug_level > 0 THEN
4964: IF l_debug_level > 0 THEN
4965: oe_debug_pub.add ('AAC:FSTI: cannot find party_id, returning with error');
4966: END IF;
4967: p_party_id := NULL;
4968: x_return_status := FND_API.G_RET_STS_ERROR;
4969: return;
4970: END IF;
4971:
4972: IF l_debug_level > 0 THEN
4995: IF l_debug_level > 4 THEN
4996: oe_debug_pub.add ('AAC:VTI: invalid p_cust_account_id in find_sold_to_id'); -- got a party_name
4997: END IF;
4998:
4999: x_return_status := FND_API.G_RET_STS_ERROR;
5000: return;
5001:
5002: WHEN OTHERS THEN
5003:
5010: END IF;
5011: IF l_debug_level > 4 THEN
5012: oe_debug_pub.add ('AAC:VTI: unexpected error in find_sold_to_id'); -- got a party_name
5013: END IF;
5014: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5015:
5016: END find_sold_to_id;
5017:
5018: PROCEDURE find_contact_id(
5031: l_debug_level NUMBER := oe_debug_pub.g_debug_level;
5032:
5033: BEGIN
5034:
5035: x_return_status := FND_API.G_RET_STS_SUCCESS;
5036:
5037: IF l_debug_level > 0 THEN
5038: oe_debug_pub.add ('AAC:VTI: starting find_contact_id...');
5039: END IF;
5038: oe_debug_pub.add ('AAC:VTI: starting find_contact_id...');
5039: END IF;
5040:
5041: -- check if cust_contact_id is not null, return if so
5042: if nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5043: IF l_debug_level > 0 THEN
5044: oe_debug_pub.add ('AAC:FCI: cust_contact_id is not null, nothing to do: warn; returning}');
5045: END IF;
5046:
5053:
5054: RETURN;
5055: END IF;
5056:
5057: if nvl(p_sold_to_org_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5058: IF l_debug_level > 0 THEN
5059: oe_debug_pub.add ('AAC:FCI: sold_to_org_id is null; returning');
5060: END IF;
5061:
5072:
5073: -- look for cust_contact_id using contact_name
5074:
5075:
5076: if nvl(p_contact_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
5077: and nvl(p_cust_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5078: IF l_debug_level > 0 THEN
5079: oe_debug_pub.add ('AAC:FCI: contact_name is not null,trying to find account contact_id');
5080: END IF;
5073: -- look for cust_contact_id using contact_name
5074:
5075:
5076: if nvl(p_contact_name,FND_API.G_MISS_CHAR) <> FND_API.G_MISS_CHAR
5077: and nvl(p_cust_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
5078: IF l_debug_level > 0 THEN
5079: oe_debug_pub.add ('AAC:FCI: contact_name is not null,trying to find account contact_id');
5080: END IF;
5081: if p_site_use_code <> 'END_CUST' then
5089: ,p_end_customer_id => p_sold_to_org_id
5090: );
5091: end if;
5092: -- if contact found, return
5093: IF nvl(p_cust_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5094: THEN
5095: -- found a cust_contact_id
5096: IF l_debug_level > 0 THEN
5097: oe_debug_pub.add ('AAC:FCI: found cust_contact_id:'||p_cust_contact_id||', returning}');
5103: end if;
5104:
5105:
5106: -- check if contact_id is not null, return if so
5107: if nvl(p_contact_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5108: IF l_debug_level > 0 THEN
5109: oe_debug_pub.add ('AAC:FSTI: org_contact_id is not null');
5110: END IF;
5111:
5121: oe_debug_pub.add ('AAC:FCI: no account contact_id');
5122: oe_debug_pub.add ('AAC:FCI: have permission to create account contact: finding party contact_id');
5123: END IF;
5124:
5125: IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5126: THEN
5127: p_contact_id := get_party_contact_id(
5128: p_contact_name => p_contact_name
5129: ,p_party_id => p_party_id
5130: ,p_sold_to_org_id => p_sold_to_org_id
5131: );
5132:
5133:
5134: IF nvl(p_contact_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5135: THEN
5136: -- didn't find a party level contact_id either
5137: -- since we *cannot* create a party, error out
5138: IF l_debug_level > 0 THEN
5138: IF l_debug_level > 0 THEN
5139: oe_debug_pub.add ('AAC:FCI: cannot find contact_id, returning with error}');
5140: END IF;
5141: p_contact_id := NULL;
5142: x_return_status := FND_API.G_RET_STS_ERROR;
5143: return;
5144: END IF;
5145: end if;
5146: -- found a contact_id
5183: l_cust_account_id number := null;
5184:
5185: BEGIN
5186:
5187: x_return_status := FND_API.G_RET_STS_SUCCESS;
5188:
5189: IF l_debug_level > 0 THEN
5190: oe_debug_pub.add ('AAC:VTI: FSI: starting find_site_id{');
5191: END IF;
5189: IF l_debug_level > 0 THEN
5190: oe_debug_pub.add ('AAC:VTI: FSI: starting find_site_id{');
5191: END IF;
5192:
5193: if (nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5194: then
5195: IF l_debug_level > 0 THEN
5196: oe_debug_pub.add ('AAC:FSI: party_id null; returning');
5197: END IF;
5198:
5199: return;
5200: end if;
5201:
5202: if (nvl(p_party_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5203: OR nvl(p_site_customer_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
5204: then
5205: l_cust_account_id := p_site_customer_id;
5206: else
5199: return;
5200: end if;
5201:
5202: if (nvl(p_party_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5203: OR nvl(p_site_customer_id ,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM)
5204: then
5205: l_cust_account_id := p_site_customer_id;
5206: else
5207: l_cust_account_id := p_sold_to_org_id;
5208: end if;
5209:
5210: -- check if site_address1 is null
5211: IF l_debug_level > 0 THEN
5212: if nvl(p_site_address1,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
5213: oe_debug_pub.add ('AAC:FSI: warning: site_address1 is null');
5214: else
5215: oe_debug_pub.add ('AAC:FSI: site_address1 : '||p_site_address1);
5216: end if;
5217: oe_debug_pub.add ( 'AAC:FSI: cust_account_id is '|| l_cust_account_id);
5218: END IF;
5219:
5220: -- check if cust_site_use_id is not null, return if so
5221: if nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
5222: IF l_debug_level > 0 THEN
5223: oe_debug_pub.add ('AAC:FSI: account_site_use_id is not null; returning');
5224: END IF;
5225:
5226: RETURN;
5227: END IF;
5228:
5229: -- check if site_use_code is null, return with error if so
5230: if nvl(p_site_use_code, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR then
5231: IF l_debug_level > 0 THEN
5232: oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5233: oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5234: end if;
5231: IF l_debug_level > 0 THEN
5232: oe_debug_pub.add ('AAC:FSI: site_use_code is null: error; returning');
5233: oe_debug_pub.add ('AAC:VTI: ending find_site_id}');
5234: end if;
5235: x_return_status := FND_API.G_RET_STS_ERROR;
5236: return;
5237: end if;
5238:
5239: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5235: x_return_status := FND_API.G_RET_STS_ERROR;
5236: return;
5237: end if;
5238:
5239: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5240: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5241: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5236: return;
5237: end if;
5238:
5239: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5240: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5241: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5237: end if;
5238:
5239: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5240: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5241: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5238:
5239: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5240: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5241: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5239: if (nvl(p_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5240: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5241: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5240: and nvl(p_site_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5241: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5241: and nvl(p_account_site_use_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5242: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5242: --and nvl(p_site_use_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5243: and nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5244: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5245: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5253: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5246: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5253: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5254: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5247: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5253: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5254: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5248: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5253: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5254: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5256: then
5249: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5253: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5254: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5256: then
5257: --nothing to do! return!
5250: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5253: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5254: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5256: then
5257: --nothing to do! return!
5258: IF l_debug_level > 0 THEN
5251: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5252: and nvl(p_site_customer_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5253: --and nvl(l_cust_account_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5254: --and nvl(p_sold_to_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5255: and nvl(p_party_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM)
5256: then
5257: --nothing to do! return!
5258: IF l_debug_level > 0 THEN
5259: oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5278: oe_debug_pub.add('p_site_customer_id :'|| p_site_customer_id );
5279: oe_debug_pub.add('p_party_id :'|| p_party_id );
5280: end if;
5281:
5282: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5283: and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5284: IF l_debug_level > 0 THEN
5285: oe_debug_pub.add ('AAC:FSI: party_site_use_id:'||p_site_use_id||' is not null; using it to get party_site_id');
5286: END IF;
5279: oe_debug_pub.add('p_party_id :'|| p_party_id );
5280: end if;
5281:
5282: if nvl(p_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5283: and nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5284: IF l_debug_level > 0 THEN
5285: oe_debug_pub.add ('AAC:FSI: party_site_use_id:'||p_site_use_id||' is not null; using it to get party_site_id');
5286: END IF;
5287: begin
5306: IF l_debug_level > 4 THEN
5307: oe_debug_pub.add ('AAC:VTI: invalid party_site_use_id ');
5308: END IF;
5309:
5310: x_return_status := FND_API.G_RET_STS_ERROR;
5311: return;
5312:
5313: WHEN OTHERS THEN
5314:
5321: --END IF;
5322: IF l_debug_level > 4 THEN
5323: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5324: END IF;
5325: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5326:
5327: end;
5328:
5329: end if;
5327: end;
5328:
5329: end if;
5330:
5331: if nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5332: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5333: IF l_debug_level > 0 THEN
5334: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5335: END IF;
5328:
5329: end if;
5330:
5331: if nvl(p_site_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5332: and nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5333: IF l_debug_level > 0 THEN
5334: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5335: END IF;
5336: begin
5358: END IF;
5359: IF l_debug_level > 4 THEN
5360: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5361: END IF;
5362: x_return_status := FND_API.G_RET_STS_ERROR;
5363: return;
5364:
5365: WHEN OTHERS THEN
5366:
5373: END IF;
5374: IF l_debug_level > 4 THEN
5375: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5376: END IF;
5377: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5378:
5379: end;
5380: end if;
5381:
5378:
5379: end;
5380: end if;
5381:
5382: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5383: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5384: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5379: end;
5380: end if;
5381:
5382: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5383: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5384: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5380: end if;
5381:
5382: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5383: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5384: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5381:
5382: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5383: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5384: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5382: if nvl(p_site_address1 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5383: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5384: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5383: and nvl(p_site_address2 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5384: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: then
5384: and nvl(p_site_address3 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5385: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: then
5392: --nothing to do! return!
5385: and nvl(p_site_address4 ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: then
5392: --nothing to do! return!
5393: IF l_debug_level > 0 THEN
5386: and nvl(p_site_org ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5387: and nvl(p_site_city ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5388: and nvl(p_site_state ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5389: and nvl(p_site_postal_code ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5390: and nvl(p_site_country ,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5391: then
5392: --nothing to do! return!
5393: IF l_debug_level > 0 THEN
5394: oe_debug_pub.add ('AAC:FSI: no data passed in, returning');
5462: return;
5463: end if;
5464:
5465: -- if site found, return
5466: IF nvl(p_account_site_use_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM
5467: THEN
5468: -- found a a cust_site_id
5469: IF l_debug_level > 0 THEN
5470: oe_debug_pub.add ('AAC:FSI: found cust_site_use_id:'||p_account_site_use_id||', returning');
5471: oe_debug_pub.add ('AAC:VTI: ending find_site_id');
5472: END IF;
5473:
5474:
5475: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5476: IF l_debug_level > 0 THEN
5477: oe_debug_pub.add ('AAC:FSI: cust_site_use_id:'||p_account_site_use_id||' is not null; using it to get party_id');
5478: END IF;
5479: begin
5504: END IF;
5505: IF l_debug_level > 4 THEN
5506: oe_debug_pub.add ('AAC:VTI: invalid cust_site_use_id ');
5507: END IF;
5508: x_return_status := FND_API.G_RET_STS_ERROR;
5509: return;
5510:
5511: end;
5512: end if;
5528: oe_debug_pub.add ('AAC:FSI: no account site_use_id');
5529: oe_debug_pub.add ('AAC:FSI: finding party site_use_id');
5530: END IF;
5531:
5532: if (nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM) then
5533: p_site_id := get_party_site_id(
5534: p_site_address1 => p_site_address1
5535: ,p_site_address2 => p_site_address2
5536: ,p_site_address3 => p_site_address3
5546: ,p_site_use_code => p_site_use_code
5547: ,p_party_id => p_party_id
5548: );
5549:
5550: IF nvl(p_site_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
5551: THEN
5552: -- didn't find a party level site_id either
5553: -- since we *cannot* create a party, error out
5554: IF l_debug_level > 0 THEN
5554: IF l_debug_level > 0 THEN
5555: oe_debug_pub.add ('AAC:FSI: cannot find party_site_id, returning with error');
5556: END IF;
5557: p_site_id := NULL;
5558: --x_return_status := FND_API.G_RET_STS_ERROR;
5559: return;
5560: ELSE -- we did find a party_site_id, get party_id from it
5561: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5562: IF l_debug_level > 0 THEN
5557: p_site_id := NULL;
5558: --x_return_status := FND_API.G_RET_STS_ERROR;
5559: return;
5560: ELSE -- we did find a party_site_id, get party_id from it
5561: if nvl(p_party_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM then
5562: IF l_debug_level > 0 THEN
5563: oe_debug_pub.add ('AAC:FSI: party_site_id:'||p_site_id||' is not null; using it to get party_id');
5564: END IF;
5565: begin
5587: END IF;
5588: IF l_debug_level > 4 THEN
5589: oe_debug_pub.add ('AAC:VTI: invalid party_site_id ');
5590: END IF;
5591: x_return_status := FND_API.G_RET_STS_ERROR;
5592: return;
5593:
5594: WHEN OTHERS THEN
5595:
5602: END IF;
5603: IF l_debug_level > 4 THEN
5604: oe_debug_pub.add ('AAC:VTI: unexpected error in find_site_id');
5605: END IF;
5606: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5607:
5608: end;
5609: end if;
5610: end if;
5636: oe_debug_pub.add ('AAC:VTI: starting get_party_id{');
5637: END IF;
5638:
5639: -- did they actually pass some values?
5640: IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5641: AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5642: THEN
5643: IF l_debug_level > 4 THEN
5644: oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5637: END IF;
5638:
5639: -- did they actually pass some values?
5640: IF (nvl(p_party_name,fnd_api.g_miss_char) = fnd_api.g_miss_char
5641: AND nvl(p_party_number,fnd_api.g_miss_char) = fnd_api.g_miss_char)
5642: THEN
5643: IF l_debug_level > 4 THEN
5644: oe_debug_pub.add ('AAC: null values passed: name'||p_party_name||' number:'||p_party_number);
5645: oe_debug_pub.add ('AAC:VTI: ending get_party_id}');
5646: END IF;
5647: RETURN NULL;
5648: END IF;
5649:
5650: IF (nvl(p_party_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char)
5651: THEN
5652: -- got a party_number
5653: IF l_debug_level > 4 THEN
5654: oe_debug_pub.add ('AAC:VTI:GPI party number: '||p_party_number);
5705: END IF;
5706: IF l_debug_level > 4 THEN
5707: oe_debug_pub.add ('AAC:VTI: no data in Get_party_id'); -- got a party_name
5708: END IF;
5709: RETURN FND_API.G_MISS_NUM;
5710:
5711: WHEN OTHERS THEN
5712:
5713: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5719: END IF;
5720: IF l_debug_level > 4 THEN
5721: oe_debug_pub.add ('AAC:VTI: unexpected error in Get_party_id'); -- got a party_name
5722: END IF;
5723: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5724:
5725: END get_party_id;
5726:
5727: FUNCTION get_party_contact_id(
5757: IF l_debug_level > 4 THEN
5758: oe_debug_pub.add ('AAC:VTI: starting get_party_contact_id...');
5759: END IF;
5760:
5761: IF nvl(p_contact_name,FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR
5762: THEN
5763: IF l_debug_level > 4 THEN
5764: oe_debug_pub.add ('AAC:VTI: no contact_name');
5765: oe_debug_pub.add ('AAC:VTI: ...done get_party_contact_id');
5824: hz_party_sites site
5825: WHERE site.location_id=loc.location_id
5826: and site.party_id=in_sold_to_party_id
5827: and loc.address1 = p_site_address1
5828: and nvl( loc.address2, fnd_api.g_miss_char) =
5829: nvl( p_site_address2, fnd_api.g_miss_char)
5830: and nvl( loc.address3, fnd_api.g_miss_char) =
5831: nvl( p_site_address3, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5825: WHERE site.location_id=loc.location_id
5826: and site.party_id=in_sold_to_party_id
5827: and loc.address1 = p_site_address1
5828: and nvl( loc.address2, fnd_api.g_miss_char) =
5829: nvl( p_site_address2, fnd_api.g_miss_char)
5830: and nvl( loc.address3, fnd_api.g_miss_char) =
5831: nvl( p_site_address3, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5826: and site.party_id=in_sold_to_party_id
5827: and loc.address1 = p_site_address1
5828: and nvl( loc.address2, fnd_api.g_miss_char) =
5829: nvl( p_site_address2, fnd_api.g_miss_char)
5830: and nvl( loc.address3, fnd_api.g_miss_char) =
5831: nvl( p_site_address3, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5827: and loc.address1 = p_site_address1
5828: and nvl( loc.address2, fnd_api.g_miss_char) =
5829: nvl( p_site_address2, fnd_api.g_miss_char)
5830: and nvl( loc.address3, fnd_api.g_miss_char) =
5831: nvl( p_site_address3, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5835: nvl( p_site_city, fnd_api.g_miss_char)
5828: and nvl( loc.address2, fnd_api.g_miss_char) =
5829: nvl( p_site_address2, fnd_api.g_miss_char)
5830: and nvl( loc.address3, fnd_api.g_miss_char) =
5831: nvl( p_site_address3, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5835: nvl( p_site_city, fnd_api.g_miss_char)
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5829: nvl( p_site_address2, fnd_api.g_miss_char)
5830: and nvl( loc.address3, fnd_api.g_miss_char) =
5831: nvl( p_site_address3, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5835: nvl( p_site_city, fnd_api.g_miss_char)
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5837: nvl( p_site_state, fnd_api.g_miss_char)
5830: and nvl( loc.address3, fnd_api.g_miss_char) =
5831: nvl( p_site_address3, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5835: nvl( p_site_city, fnd_api.g_miss_char)
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5837: nvl( p_site_state, fnd_api.g_miss_char)
5838: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5831: nvl( p_site_address3, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5835: nvl( p_site_city, fnd_api.g_miss_char)
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5837: nvl( p_site_state, fnd_api.g_miss_char)
5838: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5839: nvl( p_site_postal_code, fnd_api.g_miss_char)
5832: and nvl( loc.address4, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5835: nvl( p_site_city, fnd_api.g_miss_char)
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5837: nvl( p_site_state, fnd_api.g_miss_char)
5838: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5839: nvl( p_site_postal_code, fnd_api.g_miss_char)
5840: and nvl( loc.country, fnd_api.g_miss_char) =
5833: nvl( p_site_address4, fnd_api.g_miss_char)
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5835: nvl( p_site_city, fnd_api.g_miss_char)
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5837: nvl( p_site_state, fnd_api.g_miss_char)
5838: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5839: nvl( p_site_postal_code, fnd_api.g_miss_char)
5840: and nvl( loc.country, fnd_api.g_miss_char) =
5841: nvl( p_site_country, fnd_api.g_miss_char);
5834: and nvl( loc.city, fnd_api.g_miss_char) =
5835: nvl( p_site_city, fnd_api.g_miss_char)
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5837: nvl( p_site_state, fnd_api.g_miss_char)
5838: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5839: nvl( p_site_postal_code, fnd_api.g_miss_char)
5840: and nvl( loc.country, fnd_api.g_miss_char) =
5841: nvl( p_site_country, fnd_api.g_miss_char);
5842:
5835: nvl( p_site_city, fnd_api.g_miss_char)
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5837: nvl( p_site_state, fnd_api.g_miss_char)
5838: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5839: nvl( p_site_postal_code, fnd_api.g_miss_char)
5840: and nvl( loc.country, fnd_api.g_miss_char) =
5841: nvl( p_site_country, fnd_api.g_miss_char);
5842:
5843:
5836: and nvl( loc.state, fnd_api.g_miss_char) =
5837: nvl( p_site_state, fnd_api.g_miss_char)
5838: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5839: nvl( p_site_postal_code, fnd_api.g_miss_char)
5840: and nvl( loc.country, fnd_api.g_miss_char) =
5841: nvl( p_site_country, fnd_api.g_miss_char);
5842:
5843:
5844: /*
5837: nvl( p_site_state, fnd_api.g_miss_char)
5838: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5839: nvl( p_site_postal_code, fnd_api.g_miss_char)
5840: and nvl( loc.country, fnd_api.g_miss_char) =
5841: nvl( p_site_country, fnd_api.g_miss_char);
5842:
5843:
5844: /*
5845: cursor C1(in_sold_to_party_id number) IS
5848: HZ_LOCATIONS LOC
5849: WHERE site.location_id=loc.location_id
5850: and site.status='A'
5851: and loc.ADDRESS1 = p_site_address1
5852: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5853: nvl( p_site_address2, fnd_api.g_miss_char)
5854: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5855: nvl( p_site_address3, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5849: WHERE site.location_id=loc.location_id
5850: and site.status='A'
5851: and loc.ADDRESS1 = p_site_address1
5852: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5853: nvl( p_site_address2, fnd_api.g_miss_char)
5854: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5855: nvl( p_site_address3, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5850: and site.status='A'
5851: and loc.ADDRESS1 = p_site_address1
5852: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5853: nvl( p_site_address2, fnd_api.g_miss_char)
5854: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5855: nvl( p_site_address3, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5851: and loc.ADDRESS1 = p_site_address1
5852: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5853: nvl( p_site_address2, fnd_api.g_miss_char)
5854: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5855: nvl( p_site_address3, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5859: nvl( p_site_city, fnd_api.g_miss_char)
5852: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5853: nvl( p_site_address2, fnd_api.g_miss_char)
5854: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5855: nvl( p_site_address3, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5859: nvl( p_site_city, fnd_api.g_miss_char)
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5853: nvl( p_site_address2, fnd_api.g_miss_char)
5854: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5855: nvl( p_site_address3, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5859: nvl( p_site_city, fnd_api.g_miss_char)
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5861: nvl( p_site_state, fnd_api.g_miss_char)
5854: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5855: nvl( p_site_address3, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5859: nvl( p_site_city, fnd_api.g_miss_char)
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5861: nvl( p_site_state, fnd_api.g_miss_char)
5862: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5855: nvl( p_site_address3, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5859: nvl( p_site_city, fnd_api.g_miss_char)
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5861: nvl( p_site_state, fnd_api.g_miss_char)
5862: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5863: nvl( p_site_postal_code, fnd_api.g_miss_char)
5856: AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5859: nvl( p_site_city, fnd_api.g_miss_char)
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5861: nvl( p_site_state, fnd_api.g_miss_char)
5862: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5863: nvl( p_site_postal_code, fnd_api.g_miss_char)
5864: AND nvl( loc.country, fnd_api.g_miss_char) =
5857: nvl( p_site_address4, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5859: nvl( p_site_city, fnd_api.g_miss_char)
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5861: nvl( p_site_state, fnd_api.g_miss_char)
5862: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5863: nvl( p_site_postal_code, fnd_api.g_miss_char)
5864: AND nvl( loc.country, fnd_api.g_miss_char) =
5865: nvl( p_site_country, fnd_api.g_miss_char)
5858: AND nvl( loc.city, fnd_api.g_miss_char) =
5859: nvl( p_site_city, fnd_api.g_miss_char)
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5861: nvl( p_site_state, fnd_api.g_miss_char)
5862: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5863: nvl( p_site_postal_code, fnd_api.g_miss_char)
5864: AND nvl( loc.country, fnd_api.g_miss_char) =
5865: nvl( p_site_country, fnd_api.g_miss_char)
5866: AND site.status = 'A'
5859: nvl( p_site_city, fnd_api.g_miss_char)
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5861: nvl( p_site_state, fnd_api.g_miss_char)
5862: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5863: nvl( p_site_postal_code, fnd_api.g_miss_char)
5864: AND nvl( loc.country, fnd_api.g_miss_char) =
5865: nvl( p_site_country, fnd_api.g_miss_char)
5866: AND site.status = 'A'
5867: AND site.party_id in(
5860: AND nvl( loc.state, fnd_api.g_miss_char) =
5861: nvl( p_site_state, fnd_api.g_miss_char)
5862: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5863: nvl( p_site_postal_code, fnd_api.g_miss_char)
5864: AND nvl( loc.country, fnd_api.g_miss_char) =
5865: nvl( p_site_country, fnd_api.g_miss_char)
5866: AND site.status = 'A'
5867: AND site.party_id in(
5868: SELECT in_sold_to_party_id FROM DUAL
5861: nvl( p_site_state, fnd_api.g_miss_char)
5862: AND nvl( loc.postal_code, fnd_api.g_miss_char) =
5863: nvl( p_site_postal_code, fnd_api.g_miss_char)
5864: AND nvl( loc.country, fnd_api.g_miss_char) =
5865: nvl( p_site_country, fnd_api.g_miss_char)
5866: AND site.status = 'A'
5867: AND site.party_id in(
5868: SELECT in_sold_to_party_id FROM DUAL
5869: UNION
5878: hz_party_sites site
5879: WHERE site.location_id=loc.location_id
5880: and site.status='A'
5881: and loc.address1 = p_site_address1
5882: and nvl( loc.address2, fnd_api.g_miss_char) =
5883: nvl( p_site_address2, fnd_api.g_miss_char)
5884: and nvl( loc.address3, fnd_api.g_miss_char) =
5885: nvl( p_site_address3, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5879: WHERE site.location_id=loc.location_id
5880: and site.status='A'
5881: and loc.address1 = p_site_address1
5882: and nvl( loc.address2, fnd_api.g_miss_char) =
5883: nvl( p_site_address2, fnd_api.g_miss_char)
5884: and nvl( loc.address3, fnd_api.g_miss_char) =
5885: nvl( p_site_address3, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5880: and site.status='A'
5881: and loc.address1 = p_site_address1
5882: and nvl( loc.address2, fnd_api.g_miss_char) =
5883: nvl( p_site_address2, fnd_api.g_miss_char)
5884: and nvl( loc.address3, fnd_api.g_miss_char) =
5885: nvl( p_site_address3, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5881: and loc.address1 = p_site_address1
5882: and nvl( loc.address2, fnd_api.g_miss_char) =
5883: nvl( p_site_address2, fnd_api.g_miss_char)
5884: and nvl( loc.address3, fnd_api.g_miss_char) =
5885: nvl( p_site_address3, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5889: nvl( p_site_city, fnd_api.g_miss_char)
5882: and nvl( loc.address2, fnd_api.g_miss_char) =
5883: nvl( p_site_address2, fnd_api.g_miss_char)
5884: and nvl( loc.address3, fnd_api.g_miss_char) =
5885: nvl( p_site_address3, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5889: nvl( p_site_city, fnd_api.g_miss_char)
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5883: nvl( p_site_address2, fnd_api.g_miss_char)
5884: and nvl( loc.address3, fnd_api.g_miss_char) =
5885: nvl( p_site_address3, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5889: nvl( p_site_city, fnd_api.g_miss_char)
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5891: nvl( p_site_state, fnd_api.g_miss_char)
5884: and nvl( loc.address3, fnd_api.g_miss_char) =
5885: nvl( p_site_address3, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5889: nvl( p_site_city, fnd_api.g_miss_char)
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5891: nvl( p_site_state, fnd_api.g_miss_char)
5892: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5885: nvl( p_site_address3, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5889: nvl( p_site_city, fnd_api.g_miss_char)
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5891: nvl( p_site_state, fnd_api.g_miss_char)
5892: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5893: nvl( p_site_postal_code, fnd_api.g_miss_char)
5886: and nvl( loc.address4, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5889: nvl( p_site_city, fnd_api.g_miss_char)
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5891: nvl( p_site_state, fnd_api.g_miss_char)
5892: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5893: nvl( p_site_postal_code, fnd_api.g_miss_char)
5894: and nvl( loc.country, fnd_api.g_miss_char) =
5887: nvl( p_site_address4, fnd_api.g_miss_char)
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5889: nvl( p_site_city, fnd_api.g_miss_char)
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5891: nvl( p_site_state, fnd_api.g_miss_char)
5892: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5893: nvl( p_site_postal_code, fnd_api.g_miss_char)
5894: and nvl( loc.country, fnd_api.g_miss_char) =
5895: nvl( p_site_country, fnd_api.g_miss_char);
5888: and nvl( loc.city, fnd_api.g_miss_char) =
5889: nvl( p_site_city, fnd_api.g_miss_char)
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5891: nvl( p_site_state, fnd_api.g_miss_char)
5892: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5893: nvl( p_site_postal_code, fnd_api.g_miss_char)
5894: and nvl( loc.country, fnd_api.g_miss_char) =
5895: nvl( p_site_country, fnd_api.g_miss_char);
5896:
5889: nvl( p_site_city, fnd_api.g_miss_char)
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5891: nvl( p_site_state, fnd_api.g_miss_char)
5892: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5893: nvl( p_site_postal_code, fnd_api.g_miss_char)
5894: and nvl( loc.country, fnd_api.g_miss_char) =
5895: nvl( p_site_country, fnd_api.g_miss_char);
5896:
5897: l_site_party_id number;
5890: and nvl( loc.state, fnd_api.g_miss_char) =
5891: nvl( p_site_state, fnd_api.g_miss_char)
5892: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5893: nvl( p_site_postal_code, fnd_api.g_miss_char)
5894: and nvl( loc.country, fnd_api.g_miss_char) =
5895: nvl( p_site_country, fnd_api.g_miss_char);
5896:
5897: l_site_party_id number;
5898: l_sold_to_party_id number;
5891: nvl( p_site_state, fnd_api.g_miss_char)
5892: and nvl( loc.postal_code, fnd_api.g_miss_char) =
5893: nvl( p_site_postal_code, fnd_api.g_miss_char)
5894: and nvl( loc.country, fnd_api.g_miss_char) =
5895: nvl( p_site_country, fnd_api.g_miss_char);
5896:
5897: l_site_party_id number;
5898: l_sold_to_party_id number;
5899: l_dummy number;
5908: oe_debug_pub.add(' address4:'||p_site_address4);
5909: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5910: END IF;
5911:
5912: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5913: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5914: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5909: oe_debug_pub.add( ' party_id:'||p_party_id ) ;
5910: END IF;
5911:
5912: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5913: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5914: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5917: THEN
5910: END IF;
5911:
5912: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5913: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5914: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5917: THEN
5918: IF l_debug_level > 0 THEN
5911:
5912: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5913: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5914: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5917: THEN
5918: IF l_debug_level > 0 THEN
5919: oe_debug_pub.add( 'AAC: all incoming data missing,returning');
5912: IF (nvl( p_site_address1,fnd_api.g_miss_char) = fnd_api.g_miss_char
5913: AND nvl(p_site_address2,fnd_api.g_miss_char) = fnd_api.g_miss_char
5914: AND nvl( p_site_address3,fnd_api.g_miss_char) = fnd_api.g_miss_char
5915: AND nvl( p_site_address4,fnd_api.g_miss_char) = fnd_api.g_miss_char
5916: AND nvl( p_sold_to_party_id,fnd_api.g_miss_num) = fnd_api.g_miss_num)
5917: THEN
5918: IF l_debug_level > 0 THEN
5919: oe_debug_pub.add( 'AAC: all incoming data missing,returning');
5920: end if;
5920: end if;
5921: RETURN NULL;
5922: END IF;
5923:
5924: if (nvl(p_party_id,FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM) then
5925: IF l_debug_level > 0 THEN
5926: oe_debug_pub.add( 'AAC: incoming party_id is '||p_party_id ) ;
5927: END IF;
5928:
5946: INTO l_id
5947: FROM hz_locations loc,
5948: hz_party_sites site
5949: WHERE loc.ADDRESS1 = p_site_address1
5950: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5951: nvl( p_site_address2, fnd_api.g_miss_char)
5952: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5953: nvl( p_site_address3, fnd_api.g_miss_char)
5954: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5947: FROM hz_locations loc,
5948: hz_party_sites site
5949: WHERE loc.ADDRESS1 = p_site_address1
5950: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5951: nvl( p_site_address2, fnd_api.g_miss_char)
5952: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5953: nvl( p_site_address3, fnd_api.g_miss_char)
5954: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5955: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5948: hz_party_sites site
5949: WHERE loc.ADDRESS1 = p_site_address1
5950: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5951: nvl( p_site_address2, fnd_api.g_miss_char)
5952: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5953: nvl( p_site_address3, fnd_api.g_miss_char)
5954: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5955: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5956: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5949: WHERE loc.ADDRESS1 = p_site_address1
5950: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
5951: nvl( p_site_address2, fnd_api.g_miss_char)
5952: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
5953: nvl( p_site_address3, fnd_api.g_miss_char)
5954: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5955: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5956: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5957: DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
5954: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5955: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5956: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5957: DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
5958: nvl( p_site_address4, fnd_api.g_miss_char)
5959: AND site.status = 'A'
5960: AND site.party_id = p_party_id
5961: and site.location_id=loc.location_id;
5962:
5996: INTO l_id
5997: FROM hz_locations loc,
5998: hz_party_sites site
5999: WHERE loc.ADDRESS1 = p_site_address1
6000: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6001: nvl( p_site_address2, fnd_api.g_miss_char)
6002: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6003: nvl( p_site_address3, fnd_api.g_miss_char)
6004: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
5997: FROM hz_locations loc,
5998: hz_party_sites site
5999: WHERE loc.ADDRESS1 = p_site_address1
6000: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6001: nvl( p_site_address2, fnd_api.g_miss_char)
6002: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6003: nvl( p_site_address3, fnd_api.g_miss_char)
6004: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6005: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
5998: hz_party_sites site
5999: WHERE loc.ADDRESS1 = p_site_address1
6000: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6001: nvl( p_site_address2, fnd_api.g_miss_char)
6002: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6003: nvl( p_site_address3, fnd_api.g_miss_char)
6004: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6005: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
6006: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
5999: WHERE loc.ADDRESS1 = p_site_address1
6000: AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
6001: nvl( p_site_address2, fnd_api.g_miss_char)
6002: AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
6003: nvl( p_site_address3, fnd_api.g_miss_char)
6004: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6005: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
6006: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
6007: DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
6004: AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
6005: DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
6006: DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
6007: DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
6008: nvl( p_site_address4, fnd_api.g_miss_char)
6009: AND site.status = 'A'
6010: and site.location_id=loc.location_id;
6011:
6012: IF l_debug_level > 0 THEN
6062: , 'get_party_site_id'
6063: );
6064: END IF;
6065:
6066: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6067:
6068: END get_party_site_id;
6069:
6070:
6080: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
6081: --
6082: BEGIN
6083:
6084: IF nvl(p_sold_to_org,fnd_api.g_miss_char) = fnd_api.g_miss_char
6085: AND nvl(p_customer_number,fnd_api.g_miss_char) = fnd_api.g_miss_char
6086: THEN
6087: RETURN NULL;
6088: END IF;
6081: --
6082: BEGIN
6083:
6084: IF nvl(p_sold_to_org,fnd_api.g_miss_char) = fnd_api.g_miss_char
6085: AND nvl(p_customer_number,fnd_api.g_miss_char) = fnd_api.g_miss_char
6086: THEN
6087: RETURN NULL;
6088: END IF;
6089:
6087: RETURN NULL;
6088: END IF;
6089:
6090:
6091: IF nvl(p_customer_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
6092:
6093: SELECT ORGANIZATION_ID
6094: INTO l_id
6095: FROM OE_SOLD_TO_ORGS_V
6133: OE_MSG_PUB.Add;
6134:
6135: END IF;
6136:
6137: RETURN FND_API.G_MISS_NUM;
6138:
6139: WHEN OTHERS THEN
6140:
6141: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6145: , 'Sold_To_Org'
6146: );
6147: END IF;
6148:
6149: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6150:
6151: END Sold_To_Org;
6152:
6153: PROCEDURE does_Cust_Exist(p_cust_id IN NUMBER, --(5255840)