85: );
86:
87:
88: procedure store_related_duns(
89: p_commit IN VARCHAR2:= FND_API.G_FALSE,
90: p_group_id IN NUMBER,
91: x_return_status IN OUT NOCOPY VARCHAR2
92: );
93:
993: l_return_value := fnd_installation.get_app_info(
994: 'AR', l_status, l_industry, l_ar_schema);
995:
996: IF NOT l_return_value THEN
997: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998: END IF;
999:
1000: l_return_value := fnd_installation.get_app_info(
1001: 'FND', l_status, l_industry, l_aol_schema);
1000: l_return_value := fnd_installation.get_app_info(
1001: 'FND', l_status, l_industry, l_aol_schema);
1002:
1003: IF NOT l_return_value THEN
1004: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1005: END IF;
1006:
1007: system.ad_apps_private.get_apps_schema_name(
1008: 1, l_aol_schema, l_apps_schema, l_apps_mls_schema);
1211: +===========================================================================*/
1212:
1213: procedure map(
1214: p_api_version IN NUMBER,
1215: p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1216: p_commit IN VARCHAR2:= FND_API.G_FALSE,
1217: p_group_id IN NUMBER := NULL,
1218: x_return_status OUT NOCOPY VARCHAR2,
1219: x_msg_count OUT NOCOPY NUMBER,
1212:
1213: procedure map(
1214: p_api_version IN NUMBER,
1215: p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1216: p_commit IN VARCHAR2:= FND_API.G_FALSE,
1217: p_group_id IN NUMBER := NULL,
1218: x_return_status OUT NOCOPY VARCHAR2,
1219: x_msg_count OUT NOCOPY NUMBER,
1220: x_msg_data OUT NOCOPY VARCHAR2,
1218: x_return_status OUT NOCOPY VARCHAR2,
1219: x_msg_count OUT NOCOPY NUMBER,
1220: x_msg_data OUT NOCOPY VARCHAR2,
1221: p_inactivate_flag IN VARCHAR2, --4227564 := 'Y',
1222: p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL
1223:
1224: ) IS
1225: l_api_name CONSTANT VARCHAR2(30) := 'map';
1226: l_api_version CONSTANT NUMBER := 1.0;
1227:
1228: CURSOR c1 IS
1229: SELECT * FROM
1230: HZ_PARTY_INTERFACE
1231: WHERE NVL(group_id, FND_API.G_MISS_NUM) = NVL(p_group_id, FND_API.G_MISS_NUM)
1232: AND status = 'N';
1233:
1234: l_interface_rec HZ_PARTY_INTERFACE%ROWTYPE;
1235: l_organization_rec HZ_PARTY_V2PUB.organization_rec_type;
1275: do_add_policy_function;
1276:
1277: l_result := FND_PROFILE.SAVE('HZ_DNB_POLICY_EXIST', 'Y', 'SITE');
1278: IF NOT l_result THEN
1279: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1280: END IF;
1281:
1282: --Cache profile in current session
1283: l_policy_exist := FND_PROFILE.VALUE('HZ_DNB_POLICY_EXIST');
1300:
1301: arp_util.debug('HZ_MAP_PARTY_PUB.MAP (+) ');
1302:
1303: --Standard call to check for call compatibility.
1304: IF NOT FND_API.Compatible_API_Call(
1305: l_api_version,
1306: p_api_version,
1307: l_api_name,
1308: G_PKG_NAME)
1306: p_api_version,
1307: l_api_name,
1308: G_PKG_NAME)
1309: THEN
1310: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1311: END IF;
1312:
1313: --Initialize message list if p_init_msg_list is set to TRUE.
1314: IF FND_API.to_Boolean(p_init_msg_list) THEN
1310: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1311: END IF;
1312:
1313: --Initialize message list if p_init_msg_list is set to TRUE.
1314: IF FND_API.to_Boolean(p_init_msg_list) THEN
1315: FND_MSG_PUB.initialize;
1316: END IF;
1317:
1318: --Initialize API return status to success.
1315: FND_MSG_PUB.initialize;
1316: END IF;
1317:
1318: --Initialize API return status to success.
1319: x_return_status := FND_API.G_RET_STS_SUCCESS;
1320:
1321: --
1322: --Process records one by one.
1323: --
1365: x_msg_count,
1366: x_msg_data
1367: );
1368:
1369: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1370: RAISE FND_API.G_EXC_ERROR;
1371: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1372: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1373: END IF;
1366: x_msg_data
1367: );
1368:
1369: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1370: RAISE FND_API.G_EXC_ERROR;
1371: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1372: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1373: END IF;
1374: --
1367: );
1368:
1369: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1370: RAISE FND_API.G_EXC_ERROR;
1371: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1372: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1373: END IF;
1374: --
1375: --Create a row in hz_dnb_request_log if request_log_id is NULL
1368:
1369: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1370: RAISE FND_API.G_EXC_ERROR;
1371: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1372: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1373: END IF;
1374: --
1375: --Create a row in hz_dnb_request_log if request_log_id is NULL
1376: --
1412: do_map(
1413: l_interface_rec,
1414: x_return_status
1415: );
1416: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1417: RAISE FND_API.G_EXC_ERROR;
1418: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1419: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1420: END IF;
1413: l_interface_rec,
1414: x_return_status
1415: );
1416: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1417: RAISE FND_API.G_EXC_ERROR;
1418: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1419: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1420: END IF;
1421:
1414: x_return_status
1415: );
1416: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1417: RAISE FND_API.G_EXC_ERROR;
1418: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1419: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1420: END IF;
1421:
1422: IF l_final_party_id IS NULL THEN
1415: );
1416: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1417: RAISE FND_API.G_EXC_ERROR;
1418: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1419: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1420: END IF;
1421:
1422: IF l_final_party_id IS NULL THEN
1423: l_final_party_id := l_interface_rec.party_id;
1472: l_interface_rec,
1473: x_return_status
1474: );
1475:
1476: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1477: RAISE FND_API.G_EXC_ERROR;
1478: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1479: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1480: END IF;
1473: x_return_status
1474: );
1475:
1476: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1477: RAISE FND_API.G_EXC_ERROR;
1478: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1479: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1480: END IF;
1481:
1474: );
1475:
1476: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1477: RAISE FND_API.G_EXC_ERROR;
1478: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1479: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1480: END IF;
1481:
1482: arp_util.debug('HZ_MAP_PARTY_PUB.MAP: create DNB and USER_ENTERED data for HQ (-)');
1475:
1476: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1477: RAISE FND_API.G_EXC_ERROR;
1478: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1479: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1480: END IF;
1481:
1482: arp_util.debug('HZ_MAP_PARTY_PUB.MAP: create DNB and USER_ENTERED data for HQ (-)');
1483:
1579: x_msg_count,
1580: x_msg_data
1581: );
1582:
1583: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1584: RAISE FND_API.G_EXC_ERROR;
1585: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1587: END IF;
1580: x_msg_data
1581: );
1582:
1583: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1584: RAISE FND_API.G_EXC_ERROR;
1585: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1587: END IF;
1588:
1581: );
1582:
1583: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1584: RAISE FND_API.G_EXC_ERROR;
1585: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1587: END IF;
1588:
1589: --Update HQ's DNB info.
1582:
1583: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1584: RAISE FND_API.G_EXC_ERROR;
1585: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1587: END IF;
1588:
1589: --Update HQ's DNB info.
1590: l_interface_rec.party_id := l_displayed_duns_party_id;
1597: l_interface_rec,
1598: x_return_status
1599: );
1600:
1601: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1602: RAISE FND_API.G_EXC_ERROR;
1603: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1604: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1605: END IF;
1598: x_return_status
1599: );
1600:
1601: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1602: RAISE FND_API.G_EXC_ERROR;
1603: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1604: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1605: END IF;
1606:
1599: );
1600:
1601: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1602: RAISE FND_API.G_EXC_ERROR;
1603: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1604: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1605: END IF;
1606:
1607: l_interface_rec.orig_system_reference := l_orig_system_reference;
1600:
1601: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1602: RAISE FND_API.G_EXC_ERROR;
1603: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1604: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1605: END IF;
1606:
1607: l_interface_rec.orig_system_reference := l_orig_system_reference;
1608:
1631: l_organization_profile_id,
1632: x_return_status
1633: );
1634:
1635: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1636: RAISE FND_API.G_EXC_ERROR;
1637: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1639: END IF;
1632: x_return_status
1633: );
1634:
1635: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1636: RAISE FND_API.G_EXC_ERROR;
1637: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1639: END IF;
1640:
1633: );
1634:
1635: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1636: RAISE FND_API.G_EXC_ERROR;
1637: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1639: END IF;
1640:
1641: l_final_party_id := l_organization_rec.party_rec.party_id;
1634:
1635: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1636: RAISE FND_API.G_EXC_ERROR;
1637: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1638: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1639: END IF;
1640:
1641: l_final_party_id := l_organization_rec.party_rec.party_id;
1642:
1642:
1643: --bug 4287144
1644: IF l_original_party_id is not null THEN
1645: check_new_duns(l_organization_rec,x_return_status);
1646: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1647: return;
1648: END IF;
1649: END IF;
1650:
1670: l_party_rel_rec,
1671: x_return_status
1672: );
1673:
1674: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1675: RAISE FND_API.G_EXC_ERROR;
1676: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1677: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1678: END IF;
1671: x_return_status
1672: );
1673:
1674: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1675: RAISE FND_API.G_EXC_ERROR;
1676: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1677: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1678: END IF;
1679:
1672: );
1673:
1674: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1675: RAISE FND_API.G_EXC_ERROR;
1676: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1677: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1678: END IF;
1679:
1680: END IF;
1673:
1674: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1675: RAISE FND_API.G_EXC_ERROR;
1676: ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1677: RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1678: END IF;
1679:
1680: END IF;
1681:
1710: do_update_request_log(
1711: l_interface_rec.request_log_id,
1712: l_interface_rec.party_id,
1713: 'S1' );
1714: IF FND_API.to_Boolean(p_commit) THEN
1715: commit;
1716: END IF;
1717:
1718: ELSE
1730:
1731: END IF;
1732:
1733: EXCEPTION
1734: WHEN FND_API.G_EXC_ERROR THEN
1735:
1736: ROLLBACK TO map_pub;
1737: x_return_status := FND_API.G_RET_STS_ERROR;
1738:
1733: EXCEPTION
1734: WHEN FND_API.G_EXC_ERROR THEN
1735:
1736: ROLLBACK TO map_pub;
1737: x_return_status := FND_API.G_RET_STS_ERROR;
1738:
1739: arp_util.debug('HZ_MAP_PARTY_PUB.MAP: FND_API.G_EXC_ERROR');
1740:
1741: do_update_request_log(
1735:
1736: ROLLBACK TO map_pub;
1737: x_return_status := FND_API.G_RET_STS_ERROR;
1738:
1739: arp_util.debug('HZ_MAP_PARTY_PUB.MAP: FND_API.G_EXC_ERROR');
1740:
1741: do_update_request_log(
1742: l_interface_rec.request_log_id,
1743: l_original_party_id,
1747: p_status => 'E1',
1748: p_party_interface_id => l_interface_rec.party_interface_id);
1749:
1750: FND_MSG_PUB.Count_And_Get(
1751: p_encoded => FND_API.G_FALSE,
1752: p_count => x_msg_count,
1753: p_data => x_msg_data);
1754:
1755: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1751: p_encoded => FND_API.G_FALSE,
1752: p_count => x_msg_count,
1753: p_data => x_msg_data);
1754:
1755: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1756:
1757: ROLLBACK TO map_pub;
1758: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1759:
1754:
1755: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1756:
1757: ROLLBACK TO map_pub;
1758: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1759:
1760: arp_util.debug('HZ_MAP_PARTY_PUB.MAP: G_EXC_UNEXPECTED_ERROR');
1761:
1762: do_update_request_log(
1768: p_status => 'E1',
1769: p_party_interface_id => l_interface_rec.party_interface_id);
1770:
1771: FND_MSG_PUB.Count_And_Get(
1772: p_encoded => FND_API.G_FALSE,
1773: p_count => x_msg_count,
1774: p_data => x_msg_data);
1775:
1776: WHEN OTHERS THEN
1775:
1776: WHEN OTHERS THEN
1777:
1778: ROLLBACK TO map_pub;
1779: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1780:
1781: arp_util.debug('HZ_MAP_PARTY_PUB.MAP: OTHERS EXCEPTION');
1782:
1783: FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1793: p_status => 'E1',
1794: p_party_interface_id => l_interface_rec.party_interface_id);
1795:
1796: FND_MSG_PUB.Count_And_Get(
1797: p_encoded => FND_API.G_FALSE,
1798: p_count => x_msg_count,
1799: p_data => x_msg_data);
1800:
1801: END;
1822: END IF;
1823:
1824: --Standard call to get message count and if count is 1, get message info.
1825: FND_MSG_PUB.Count_And_Get(
1826: p_encoded => FND_API.G_FALSE,
1827: p_count => x_msg_count,
1828: p_data => x_msg_data);
1829: arp_util.debug('HZ_MAP_PARTY_PUB.MAP (-)');
1830:
1943: x_msg_count => x_msg_count,
1944: x_msg_data => x_msg_data
1945: );
1946:
1947: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1948: RETURN;
1949: END IF;
1950: -- END IF;
1951: END IF;
1950: -- END IF;
1951: END IF;
1952: CLOSE c_check_party_mapping;
1953: EXCEPTION
1954: WHEN FND_API.G_EXC_ERROR THEN
1955: x_return_status := FND_API.G_RET_STS_ERROR;
1956: RETURN;
1957: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1958: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1951: END IF;
1952: CLOSE c_check_party_mapping;
1953: EXCEPTION
1954: WHEN FND_API.G_EXC_ERROR THEN
1955: x_return_status := FND_API.G_RET_STS_ERROR;
1956: RETURN;
1957: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1958: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1959: RETURN;
1953: EXCEPTION
1954: WHEN FND_API.G_EXC_ERROR THEN
1955: x_return_status := FND_API.G_RET_STS_ERROR;
1956: RETURN;
1957: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1958: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1959: RETURN;
1960: END;
1961:
1954: WHEN FND_API.G_EXC_ERROR THEN
1955: x_return_status := FND_API.G_RET_STS_ERROR;
1956: RETURN;
1957: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1958: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1959: RETURN;
1960: END;
1961:
1962:
1987: x_msg_count => x_msg_count,
1988: x_msg_data => x_msg_data
1989: );
1990:
1991: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1992: RETURN;
1993: END IF;
1994:
1995: -- END IF;
1995: -- END IF;
1996: END LOOP;
1997: CLOSE c_check_party_site_mapping;
1998: EXCEPTION
1999: WHEN FND_API.G_EXC_ERROR THEN
2000: x_return_status := FND_API.G_RET_STS_ERROR;
2001: RETURN;
2002: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2003: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1996: END LOOP;
1997: CLOSE c_check_party_site_mapping;
1998: EXCEPTION
1999: WHEN FND_API.G_EXC_ERROR THEN
2000: x_return_status := FND_API.G_RET_STS_ERROR;
2001: RETURN;
2002: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2003: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2004: RETURN;
1998: EXCEPTION
1999: WHEN FND_API.G_EXC_ERROR THEN
2000: x_return_status := FND_API.G_RET_STS_ERROR;
2001: RETURN;
2002: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2003: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2004: RETURN;
2005: END;
2006:
1999: WHEN FND_API.G_EXC_ERROR THEN
2000: x_return_status := FND_API.G_RET_STS_ERROR;
2001: RETURN;
2002: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2003: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2004: RETURN;
2005: END;
2006:
2007: --Inactivate HZ_CONTACT_POINTS records
2030: x_msg_count => x_msg_count,
2031: x_msg_data => x_msg_data
2032: );
2033:
2034: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2035: RETURN;
2036: END IF;
2037: -- END IF;
2038: END LOOP;
2037: -- END IF;
2038: END LOOP;
2039: CLOSE c_check_contact_point_mapping;
2040: EXCEPTION
2041: WHEN FND_API.G_EXC_ERROR THEN
2042: x_return_status := FND_API.G_RET_STS_ERROR;
2043: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2044: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2045: END;
2038: END LOOP;
2039: CLOSE c_check_contact_point_mapping;
2040: EXCEPTION
2041: WHEN FND_API.G_EXC_ERROR THEN
2042: x_return_status := FND_API.G_RET_STS_ERROR;
2043: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2044: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2045: END;
2046:
2039: CLOSE c_check_contact_point_mapping;
2040: EXCEPTION
2041: WHEN FND_API.G_EXC_ERROR THEN
2042: x_return_status := FND_API.G_RET_STS_ERROR;
2043: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2044: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2045: END;
2046:
2047: EXCEPTION
2040: EXCEPTION
2041: WHEN FND_API.G_EXC_ERROR THEN
2042: x_return_status := FND_API.G_RET_STS_ERROR;
2043: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2044: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2045: END;
2046:
2047: EXCEPTION
2048: WHEN FND_API.G_EXC_ERROR THEN
2044: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2045: END;
2046:
2047: EXCEPTION
2048: WHEN FND_API.G_EXC_ERROR THEN
2049: x_return_status := FND_API.G_RET_STS_ERROR;
2050: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2051: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052: END check_mosr_mapping;
2045: END;
2046:
2047: EXCEPTION
2048: WHEN FND_API.G_EXC_ERROR THEN
2049: x_return_status := FND_API.G_RET_STS_ERROR;
2050: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2051: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052: END check_mosr_mapping;
2053:
2046:
2047: EXCEPTION
2048: WHEN FND_API.G_EXC_ERROR THEN
2049: x_return_status := FND_API.G_RET_STS_ERROR;
2050: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2051: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052: END check_mosr_mapping;
2053:
2054: -- Bug 3223595 : Added new procedure check_new_duns
2047: EXCEPTION
2048: WHEN FND_API.G_EXC_ERROR THEN
2049: x_return_status := FND_API.G_RET_STS_ERROR;
2050: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2051: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052: END check_mosr_mapping;
2053:
2054: -- Bug 3223595 : Added new procedure check_new_duns
2055:
2135: AND hosr.orig_system_reference not like p_duns_number_c || '%';
2136:
2137: BEGIN
2138: --bug 4287144
2139: x_return_status := FND_API.G_RET_STS_SUCCESS;
2140:
2141: --Inactivate HZ_PARTIES records
2142: BEGIN
2143: p_duns_number_c := p_organization_rec.duns_number_c;
2160: l_orig_sys_reference_rec.created_by_module := 'TCA_DNB_MAPPING';
2161: l_orig_sys_reference_rec.old_orig_system_reference := l_orig_system_reference_p;
2162:
2163: hz_orig_system_ref_pub.create_orig_system_reference(
2164: FND_API.G_FALSE,
2165: l_orig_sys_reference_rec,
2166: x_return_status,
2167: l_msg_count,
2168: l_msg_data);
2166: x_return_status,
2167: l_msg_count,
2168: l_msg_data);
2169:
2170: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2171: RETURN;
2172: END IF;
2173:
2174: --Inactivate HZ_PARTY_SITES records
2192: l_orig_sys_reference_rec.created_by_module := 'TCA_DNB_MAPPING';
2193:
2194: l_orig_sys_reference_rec.old_orig_system_reference := l_orig_system_reference;
2195: hz_orig_system_ref_pub.create_orig_system_reference(
2196: FND_API.G_FALSE,
2197: l_orig_sys_reference_rec,
2198: x_return_status,
2199: l_msg_count,
2200: l_msg_data);
2198: x_return_status,
2199: l_msg_count,
2200: l_msg_data);
2201:
2202: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2203: RETURN;
2204: END IF;
2205: END LOOP;
2206: CLOSE c_check_party_site_mapping;
2204: END IF;
2205: END LOOP;
2206: CLOSE c_check_party_site_mapping;
2207: EXCEPTION
2208: WHEN FND_API.G_EXC_ERROR THEN
2209: x_return_status := FND_API.G_RET_STS_ERROR;
2210: RETURN;
2211: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2212: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2205: END LOOP;
2206: CLOSE c_check_party_site_mapping;
2207: EXCEPTION
2208: WHEN FND_API.G_EXC_ERROR THEN
2209: x_return_status := FND_API.G_RET_STS_ERROR;
2210: RETURN;
2211: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2212: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2213: RETURN;
2207: EXCEPTION
2208: WHEN FND_API.G_EXC_ERROR THEN
2209: x_return_status := FND_API.G_RET_STS_ERROR;
2210: RETURN;
2211: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2212: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2213: RETURN;
2214: END;
2215:
2208: WHEN FND_API.G_EXC_ERROR THEN
2209: x_return_status := FND_API.G_RET_STS_ERROR;
2210: RETURN;
2211: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2212: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2213: RETURN;
2214: END;
2215:
2216: --Inactivate HZ_CONTACT_POINTS records
2232: l_orig_sys_reference_rec.status := 'A';
2233: l_orig_sys_reference_rec.created_by_module := 'TCA_DNB_MAPPING';
2234: l_orig_sys_reference_rec.old_orig_system_reference := l_orig_system_reference;
2235: hz_orig_system_ref_pub.create_orig_system_reference(
2236: FND_API.G_FALSE,
2237: l_orig_sys_reference_rec,
2238: x_return_status,
2239: l_msg_count,
2240: l_msg_data);
2238: x_return_status,
2239: l_msg_count,
2240: l_msg_data);
2241:
2242: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2243: RETURN;
2244: END IF;
2245: END LOOP;
2246: CLOSE c_check_contact_point_mapping;
2244: END IF;
2245: END LOOP;
2246: CLOSE c_check_contact_point_mapping;
2247: EXCEPTION
2248: WHEN FND_API.G_EXC_ERROR THEN
2249: x_return_status := FND_API.G_RET_STS_ERROR;
2250: RETURN;
2251: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2252: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2245: END LOOP;
2246: CLOSE c_check_contact_point_mapping;
2247: EXCEPTION
2248: WHEN FND_API.G_EXC_ERROR THEN
2249: x_return_status := FND_API.G_RET_STS_ERROR;
2250: RETURN;
2251: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2252: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2253: RETURN;
2247: EXCEPTION
2248: WHEN FND_API.G_EXC_ERROR THEN
2249: x_return_status := FND_API.G_RET_STS_ERROR;
2250: RETURN;
2251: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2252: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2253: RETURN;
2254: END;
2255: END IF;
2248: WHEN FND_API.G_EXC_ERROR THEN
2249: x_return_status := FND_API.G_RET_STS_ERROR;
2250: RETURN;
2251: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2252: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2253: RETURN;
2254: END;
2255: END IF;
2256: CLOSE c_check_party_mapping;
2254: END;
2255: END IF;
2256: CLOSE c_check_party_mapping;
2257: EXCEPTION
2258: WHEN FND_API.G_EXC_ERROR THEN
2259: x_return_status := FND_API.G_RET_STS_ERROR;
2260: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2261: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2262: END;
2255: END IF;
2256: CLOSE c_check_party_mapping;
2257: EXCEPTION
2258: WHEN FND_API.G_EXC_ERROR THEN
2259: x_return_status := FND_API.G_RET_STS_ERROR;
2260: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2261: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2262: END;
2263:
2256: CLOSE c_check_party_mapping;
2257: EXCEPTION
2258: WHEN FND_API.G_EXC_ERROR THEN
2259: x_return_status := FND_API.G_RET_STS_ERROR;
2260: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2261: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2262: END;
2263:
2264:
2257: EXCEPTION
2258: WHEN FND_API.G_EXC_ERROR THEN
2259: x_return_status := FND_API.G_RET_STS_ERROR;
2260: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2261: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2262: END;
2263:
2264:
2265: EXCEPTION
2262: END;
2263:
2264:
2265: EXCEPTION
2266: WHEN FND_API.G_EXC_ERROR THEN
2267: x_return_status := FND_API.G_RET_STS_ERROR;
2268: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2269: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2270: end check_new_duns;
2263:
2264:
2265: EXCEPTION
2266: WHEN FND_API.G_EXC_ERROR THEN
2267: x_return_status := FND_API.G_RET_STS_ERROR;
2268: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2269: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2270: end check_new_duns;
2271:
2264:
2265: EXCEPTION
2266: WHEN FND_API.G_EXC_ERROR THEN
2267: x_return_status := FND_API.G_RET_STS_ERROR;
2268: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2269: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2270: end check_new_duns;
2271:
2272:
2265: EXCEPTION
2266: WHEN FND_API.G_EXC_ERROR THEN
2267: x_return_status := FND_API.G_RET_STS_ERROR;
2268: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2269: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2270: end check_new_duns;
2271:
2272:
2273: /*===========================================================================+
2341: store_org(l_organization_rec,
2342: l_organization_profile_id,
2343: x_return_status);
2344:
2345: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2346: RETURN;
2347: END IF;
2348:
2349: --
2362: -- START OF CODE
2363:
2364: if(l_is_new_party = FALSE) THEN
2365: check_new_duns(l_organization_rec, x_return_status);
2366: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2367: return;
2368: end if;
2369: end if;
2370:
2394: l_orig_sys_reference_rec.created_by_module := 'TCA_DNB_MAPPING';
2395:
2396:
2397: hz_orig_system_ref_pub.create_orig_system_reference(
2398: FND_API.G_FALSE,
2399: l_orig_sys_reference_rec,
2400: x_return_status,
2401: l_msg_count,
2402: l_msg_data);
2399: l_orig_sys_reference_rec,
2400: x_return_status,
2401: l_msg_count,
2402: l_msg_data);
2403: if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2404: RETURN;
2405: end if;
2406: END;
2407: end if;
2411: -- p_is_new_party => l_is_new_party,
2412: x_return_status => x_return_status
2413: );
2414:
2415: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2416: RETURN;
2417: END IF;
2418:
2419:
2449: p_interface_rec.party_id,
2450: l_is_new_party,
2451: x_return_status);
2452:
2453: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2454: RETURN;
2455: END IF;
2456: -- Bug 3070330 : Comment the code to store location for USER_ENTERED
2457: /*
2464: store_location( l_location_rec,
2465: p_interface_rec.party_id,
2466: x_return_status);
2467:
2468: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2469: RETURN;
2470: END IF;
2471: END IF;
2472: */
2501: l_contact_points_rec,
2502: l_phone_rec,
2503: x_return_status);
2504:
2505: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2506: RETURN;
2507: END IF;
2508: END IF;
2509:
2526: l_contact_points_rec,
2527: l_phone_rec,
2528: x_return_status);
2529:
2530: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2531: RETURN;
2532: END IF;
2533:
2534: END IF;
2552: store_credit_ratings(
2553: l_credit_ratings_rec,
2554: x_return_status);
2555:
2556: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2557: RETURN;
2558: END IF;
2559:
2560: END IF; -- END IF p_interface_rec.GDP_NAME <> 'BIR'
2581: l_fin_rep_rec,
2582: p_interface_rec,
2583: x_return_status);
2584:
2585: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2586: RETURN;
2587: END IF;
2588: END IF;
2589:
2619: l_fin_rep_rec,
2620: p_interface_rec,
2621: x_return_status);
2622:
2623: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2624: RETURN;
2625: END IF;
2626: END IF;
2627:
2640: l_fin_rep_rec,
2641: p_interface_rec,
2642: x_return_status);
2643:
2644: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2645: RETURN;
2646: END IF;
2647: END IF;
2648:
2661: l_fin_rep_rec,
2662: p_interface_rec,
2663: x_return_status);
2664:
2665: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2666: RETURN;
2667: END IF;
2668: END IF;
2669:
2724:
2725: END IF;
2726: */
2727: l_organization_rec.party_rec.party_id := p_interface_rec.party_id;
2728: IF l_organization_rec.displayed_duns_party_id = FND_API.G_MISS_NUM OR
2729: l_organization_rec.displayed_duns_party_id = NULL THEN
2730: l_organization_rec.displayed_duns_party_id := l_organization_rec.party_rec.party_id;
2731: END IF;
2732: l_organization_rec.DUNS_NUMBER_C := lpad(to_char(p_interface_rec.DUNS_NUMBER),9,'0');
2878: ) IS
2879: BEGIN
2880:
2881: l_location_rec.ADDRESS1 := NVL(p_interface_rec.ADDRESS1, 'Not provided by DNB');
2882: l_location_rec.ADDRESS2 := NVL(p_interface_rec.ADDRESS2, FND_API.G_MISS_CHAR);
2883: l_location_rec.ADDRESS3 := NVL(p_interface_rec.ADDRESS3, FND_API.G_MISS_CHAR);
2884: l_location_rec.ADDRESS4 := NVL(p_interface_rec.ADDRESS4, FND_API.G_MISS_CHAR);
2885: l_location_rec.CITY := NVL(p_interface_rec.CITY, FND_API.G_MISS_CHAR);
2886: l_location_rec.STATE := NVL(p_interface_rec.STATE, FND_API.G_MISS_CHAR);
2879: BEGIN
2880:
2881: l_location_rec.ADDRESS1 := NVL(p_interface_rec.ADDRESS1, 'Not provided by DNB');
2882: l_location_rec.ADDRESS2 := NVL(p_interface_rec.ADDRESS2, FND_API.G_MISS_CHAR);
2883: l_location_rec.ADDRESS3 := NVL(p_interface_rec.ADDRESS3, FND_API.G_MISS_CHAR);
2884: l_location_rec.ADDRESS4 := NVL(p_interface_rec.ADDRESS4, FND_API.G_MISS_CHAR);
2885: l_location_rec.CITY := NVL(p_interface_rec.CITY, FND_API.G_MISS_CHAR);
2886: l_location_rec.STATE := NVL(p_interface_rec.STATE, FND_API.G_MISS_CHAR);
2887: l_location_rec.COUNTY := NVL(p_interface_rec.COUNTY, FND_API.G_MISS_CHAR);
2880:
2881: l_location_rec.ADDRESS1 := NVL(p_interface_rec.ADDRESS1, 'Not provided by DNB');
2882: l_location_rec.ADDRESS2 := NVL(p_interface_rec.ADDRESS2, FND_API.G_MISS_CHAR);
2883: l_location_rec.ADDRESS3 := NVL(p_interface_rec.ADDRESS3, FND_API.G_MISS_CHAR);
2884: l_location_rec.ADDRESS4 := NVL(p_interface_rec.ADDRESS4, FND_API.G_MISS_CHAR);
2885: l_location_rec.CITY := NVL(p_interface_rec.CITY, FND_API.G_MISS_CHAR);
2886: l_location_rec.STATE := NVL(p_interface_rec.STATE, FND_API.G_MISS_CHAR);
2887: l_location_rec.COUNTY := NVL(p_interface_rec.COUNTY, FND_API.G_MISS_CHAR);
2888: l_location_rec.POSTAL_CODE := NVL(p_interface_rec.POSTAL_CODE, FND_API.G_MISS_CHAR);
2881: l_location_rec.ADDRESS1 := NVL(p_interface_rec.ADDRESS1, 'Not provided by DNB');
2882: l_location_rec.ADDRESS2 := NVL(p_interface_rec.ADDRESS2, FND_API.G_MISS_CHAR);
2883: l_location_rec.ADDRESS3 := NVL(p_interface_rec.ADDRESS3, FND_API.G_MISS_CHAR);
2884: l_location_rec.ADDRESS4 := NVL(p_interface_rec.ADDRESS4, FND_API.G_MISS_CHAR);
2885: l_location_rec.CITY := NVL(p_interface_rec.CITY, FND_API.G_MISS_CHAR);
2886: l_location_rec.STATE := NVL(p_interface_rec.STATE, FND_API.G_MISS_CHAR);
2887: l_location_rec.COUNTY := NVL(p_interface_rec.COUNTY, FND_API.G_MISS_CHAR);
2888: l_location_rec.POSTAL_CODE := NVL(p_interface_rec.POSTAL_CODE, FND_API.G_MISS_CHAR);
2889:
2882: l_location_rec.ADDRESS2 := NVL(p_interface_rec.ADDRESS2, FND_API.G_MISS_CHAR);
2883: l_location_rec.ADDRESS3 := NVL(p_interface_rec.ADDRESS3, FND_API.G_MISS_CHAR);
2884: l_location_rec.ADDRESS4 := NVL(p_interface_rec.ADDRESS4, FND_API.G_MISS_CHAR);
2885: l_location_rec.CITY := NVL(p_interface_rec.CITY, FND_API.G_MISS_CHAR);
2886: l_location_rec.STATE := NVL(p_interface_rec.STATE, FND_API.G_MISS_CHAR);
2887: l_location_rec.COUNTY := NVL(p_interface_rec.COUNTY, FND_API.G_MISS_CHAR);
2888: l_location_rec.POSTAL_CODE := NVL(p_interface_rec.POSTAL_CODE, FND_API.G_MISS_CHAR);
2889:
2890: --Bug 1736056: Default country to 'US' if it is NULL. Convert
2883: l_location_rec.ADDRESS3 := NVL(p_interface_rec.ADDRESS3, FND_API.G_MISS_CHAR);
2884: l_location_rec.ADDRESS4 := NVL(p_interface_rec.ADDRESS4, FND_API.G_MISS_CHAR);
2885: l_location_rec.CITY := NVL(p_interface_rec.CITY, FND_API.G_MISS_CHAR);
2886: l_location_rec.STATE := NVL(p_interface_rec.STATE, FND_API.G_MISS_CHAR);
2887: l_location_rec.COUNTY := NVL(p_interface_rec.COUNTY, FND_API.G_MISS_CHAR);
2888: l_location_rec.POSTAL_CODE := NVL(p_interface_rec.POSTAL_CODE, FND_API.G_MISS_CHAR);
2889:
2890: --Bug 1736056: Default country to 'US' if it is NULL. Convert
2891: --lower case to upper case.
2884: l_location_rec.ADDRESS4 := NVL(p_interface_rec.ADDRESS4, FND_API.G_MISS_CHAR);
2885: l_location_rec.CITY := NVL(p_interface_rec.CITY, FND_API.G_MISS_CHAR);
2886: l_location_rec.STATE := NVL(p_interface_rec.STATE, FND_API.G_MISS_CHAR);
2887: l_location_rec.COUNTY := NVL(p_interface_rec.COUNTY, FND_API.G_MISS_CHAR);
2888: l_location_rec.POSTAL_CODE := NVL(p_interface_rec.POSTAL_CODE, FND_API.G_MISS_CHAR);
2889:
2890: --Bug 1736056: Default country to 'US' if it is NULL. Convert
2891: --lower case to upper case.
2892: l_location_rec.COUNTRY := NVL(UPPER(p_interface_rec.COUNTRY), G_DEFAULT_COUNTRY_CODE);
2890: --Bug 1736056: Default country to 'US' if it is NULL. Convert
2891: --lower case to upper case.
2892: l_location_rec.COUNTRY := NVL(UPPER(p_interface_rec.COUNTRY), G_DEFAULT_COUNTRY_CODE);
2893:
2894: l_location_rec.PROVINCE := NVL(p_interface_rec.PROVINCE, FND_API.G_MISS_CHAR);
2895: l_location_rec.orig_system_reference := p_interface_rec.orig_system_reference;
2896: l_location_rec.actual_content_source := p_interface_rec.content_source_type;
2897: l_location_rec.created_by_module := 'TCA_DNB_MAPPING';
2898:
3120: | p_interface_rec.INCOME_STMT_START_DATE))), sysdate);
3121: |
3122: */
3123: IF p_interface_rec.INCOME_STMT_START_DATE IS NULL OR
3124: p_interface_rec.INCOME_STMT_START_DATE = FND_API.G_MISS_DATE OR
3125: p_interface_rec.INCOME_STMT_END_DATE IS NULL OR
3126: p_interface_rec.INCOME_STMT_END_DATE = FND_API.G_MISS_DATE
3127: THEN
3128: l_fin_rep_rec.REPORT_START_DATE := NULL;
3122: */
3123: IF p_interface_rec.INCOME_STMT_START_DATE IS NULL OR
3124: p_interface_rec.INCOME_STMT_START_DATE = FND_API.G_MISS_DATE OR
3125: p_interface_rec.INCOME_STMT_END_DATE IS NULL OR
3126: p_interface_rec.INCOME_STMT_END_DATE = FND_API.G_MISS_DATE
3127: THEN
3128: l_fin_rep_rec.REPORT_START_DATE := NULL;
3129: l_fin_rep_rec.REPORT_END_DATE := NULL;
3130: l_fin_rep_rec.ISSUED_PERIOD := NVL(
3173: /*Bug 3456205*/
3174: /* Commenting out changes made in initial fix for 3456205.
3175:
3176: IF l_fin_rep_rec.REPORT_START_DATE is NULL
3177: OR l_fin_rep_rec.REPORT_START_DATE = FND_API.G_MISS_DATE
3178: THEN
3179: l_fin_rep_rec.REPORT_END_DATE := NULL;
3180: l_fin_rep_rec.ISSUED_PERIOD := 'Not Provided By DNB';
3181:
3179: l_fin_rep_rec.REPORT_END_DATE := NULL;
3180: l_fin_rep_rec.ISSUED_PERIOD := 'Not Provided By DNB';
3181:
3182: ELSIF l_fin_rep_rec.REPORT_END_DATE is NULL
3183: OR l_fin_rep_rec.REPORT_END_DATE = FND_API.G_MISS_DATE
3184: THEN
3185: l_fin_rep_rec.REPORT_START_DATE := NULL;
3186: l_fin_rep_rec.ISSUED_PERIOD := 'Not Provided By DNB';
3187:
3351: END IF;
3352:
3353: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_ORG: party does not exist: created party with party_id =' || to_char(p_organization_rec.party_rec.party_id));
3354:
3355: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3356: RETURN;
3357: END IF;
3358:
3359: IF p_organization_rec.displayed_duns_party_id IS NULL THEN
3475: --bug 4169070
3476: --content_source_type = p_code_assignment_rec.content_source_type AND
3477: actual_content_source = p_code_assignment_rec.actual_content_source AND
3478: class_code not in (
3479: /*nvl(SUBSTRB(replace(p_interface_rec.sic_code1, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3480: nvl(SUBSTRB(replace(p_interface_rec.sic_code2, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3481: nvl(SUBSTRB(replace(p_interface_rec.sic_code3, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3482: nvl(SUBSTRB(replace(p_interface_rec.sic_code4, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3483: nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3476: --content_source_type = p_code_assignment_rec.content_source_type AND
3477: actual_content_source = p_code_assignment_rec.actual_content_source AND
3478: class_code not in (
3479: /*nvl(SUBSTRB(replace(p_interface_rec.sic_code1, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3480: nvl(SUBSTRB(replace(p_interface_rec.sic_code2, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3481: nvl(SUBSTRB(replace(p_interface_rec.sic_code3, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3482: nvl(SUBSTRB(replace(p_interface_rec.sic_code4, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3483: nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3484: nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
3477: actual_content_source = p_code_assignment_rec.actual_content_source AND
3478: class_code not in (
3479: /*nvl(SUBSTRB(replace(p_interface_rec.sic_code1, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3480: nvl(SUBSTRB(replace(p_interface_rec.sic_code2, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3481: nvl(SUBSTRB(replace(p_interface_rec.sic_code3, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3482: nvl(SUBSTRB(replace(p_interface_rec.sic_code4, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3483: nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3484: nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
3485: */
3478: class_code not in (
3479: /*nvl(SUBSTRB(replace(p_interface_rec.sic_code1, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3480: nvl(SUBSTRB(replace(p_interface_rec.sic_code2, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3481: nvl(SUBSTRB(replace(p_interface_rec.sic_code3, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3482: nvl(SUBSTRB(replace(p_interface_rec.sic_code4, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3483: nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3484: nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
3485: */
3486: NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
3479: /*nvl(SUBSTRB(replace(p_interface_rec.sic_code1, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3480: nvl(SUBSTRB(replace(p_interface_rec.sic_code2, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3481: nvl(SUBSTRB(replace(p_interface_rec.sic_code3, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3482: nvl(SUBSTRB(replace(p_interface_rec.sic_code4, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3483: nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3484: nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
3485: */
3486: NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
3487: NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
3480: nvl(SUBSTRB(replace(p_interface_rec.sic_code2, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3481: nvl(SUBSTRB(replace(p_interface_rec.sic_code3, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3482: nvl(SUBSTRB(replace(p_interface_rec.sic_code4, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3483: nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3484: nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
3485: */
3486: NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
3487: NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
3488: NVL(p_interface_rec.sic_code3,FND_API.G_MISS_CHAR),
3482: nvl(SUBSTRB(replace(p_interface_rec.sic_code4, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3483: nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3484: nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
3485: */
3486: NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
3487: NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
3488: NVL(p_interface_rec.sic_code3,FND_API.G_MISS_CHAR),
3489: NVL(p_interface_rec.sic_code4,FND_API.G_MISS_CHAR),
3490: NVL(p_interface_rec.sic_code5,FND_API.G_MISS_CHAR),
3483: nvl(SUBSTRB(replace(p_interface_rec.sic_code5, ' ', ''), 1, 4),FND_API.G_MISS_CHAR),
3484: nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
3485: */
3486: NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
3487: NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
3488: NVL(p_interface_rec.sic_code3,FND_API.G_MISS_CHAR),
3489: NVL(p_interface_rec.sic_code4,FND_API.G_MISS_CHAR),
3490: NVL(p_interface_rec.sic_code5,FND_API.G_MISS_CHAR),
3491: NVL(p_interface_rec.sic_code6,FND_API.G_MISS_CHAR)
3484: nvl(SUBSTRB(replace(p_interface_rec.sic_code6, ' ', ''), 1, 4),FND_API.G_MISS_CHAR)
3485: */
3486: NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
3487: NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
3488: NVL(p_interface_rec.sic_code3,FND_API.G_MISS_CHAR),
3489: NVL(p_interface_rec.sic_code4,FND_API.G_MISS_CHAR),
3490: NVL(p_interface_rec.sic_code5,FND_API.G_MISS_CHAR),
3491: NVL(p_interface_rec.sic_code6,FND_API.G_MISS_CHAR)
3492: )
3485: */
3486: NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
3487: NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
3488: NVL(p_interface_rec.sic_code3,FND_API.G_MISS_CHAR),
3489: NVL(p_interface_rec.sic_code4,FND_API.G_MISS_CHAR),
3490: NVL(p_interface_rec.sic_code5,FND_API.G_MISS_CHAR),
3491: NVL(p_interface_rec.sic_code6,FND_API.G_MISS_CHAR)
3492: )
3493: AND
3486: NVL(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR),
3487: NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
3488: NVL(p_interface_rec.sic_code3,FND_API.G_MISS_CHAR),
3489: NVL(p_interface_rec.sic_code4,FND_API.G_MISS_CHAR),
3490: NVL(p_interface_rec.sic_code5,FND_API.G_MISS_CHAR),
3491: NVL(p_interface_rec.sic_code6,FND_API.G_MISS_CHAR)
3492: )
3493: AND
3494: -- (END_DATE_ACTIVE IS NULL OR (END_DATE_ACTIVE IS not NULL and trunc(END_DATE_ACTIVE) >= trunc(SYSDATE)));
3487: NVL(p_interface_rec.sic_code2,FND_API.G_MISS_CHAR),
3488: NVL(p_interface_rec.sic_code3,FND_API.G_MISS_CHAR),
3489: NVL(p_interface_rec.sic_code4,FND_API.G_MISS_CHAR),
3490: NVL(p_interface_rec.sic_code5,FND_API.G_MISS_CHAR),
3491: NVL(p_interface_rec.sic_code6,FND_API.G_MISS_CHAR)
3492: )
3493: AND
3494: -- (END_DATE_ACTIVE IS NULL OR (END_DATE_ACTIVE IS not NULL and trunc(END_DATE_ACTIVE) >= trunc(SYSDATE)));
3495: (END_DATE_ACTIVE IS NULL OR (END_DATE_ACTIVE IS not NULL and END_DATE_ACTIVE >= SYSDATE));
3561: class_category = p_code_assignment_rec.class_category AND
3562: --bug 4169070
3563: --content_source_type = p_code_assignment_rec.content_source_type AND
3564: actual_content_source = p_code_assignment_rec.actual_content_source AND
3565: class_code <> nvl(p_interface_rec.sic_code1,FND_API.G_MISS_CHAR) AND
3566: primary_flag = 'Y' AND
3567: (END_DATE_ACTIVE IS NULL OR (END_DATE_ACTIVE IS not NULL and
3568: END_DATE_ACTIVE >= SYSDATE));
3569: --trunc(END_DATE_ACTIVE) >= trunc(SYSDATE))); --bug 4287144
3615: store_classification (
3616: p_code_assignment_rec => p_code_assignment_rec,
3617: x_return_status => x_return_status
3618: );
3619: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3620: RETURN;
3621: END IF;
3622:
3623: /*
3628: store_classification (
3629: p_code_assignment_rec => p_code_assignment_rec,
3630: x_return_status => x_return_status
3631: );
3632: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3633: RETURN;
3634: END IF;
3635:
3636: END IF;
3653: store_classification (
3654: p_code_assignment_rec => p_code_assignment_rec,
3655: x_return_status => x_return_status
3656: );
3657: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3658: RETURN;
3659: END IF;
3660: /*
3661: IF p_is_new_party AND isMixNMatchEnabled = 'N' THEN
3665: store_classification (
3666: p_code_assignment_rec => p_code_assignment_rec,
3667: x_return_status => x_return_status
3668: );
3669: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3670: RETURN;
3671: END IF;
3672: END IF;
3673: */
3690: store_classification (
3691: p_code_assignment_rec => p_code_assignment_rec,
3692: x_return_status => x_return_status
3693: );
3694: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3695: RETURN;
3696: END IF;
3697: /*
3698: IF p_is_new_party AND isMixNMatchEnabled = 'N' THEN
3701: store_classification (
3702: p_code_assignment_rec => p_code_assignment_rec,
3703: x_return_status => x_return_status
3704: );
3705: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3706: RETURN;
3707: END IF;
3708: END IF;
3709: */
3726: store_classification (
3727: p_code_assignment_rec => p_code_assignment_rec,
3728: x_return_status => x_return_status
3729: );
3730: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3731: RETURN;
3732: END IF;
3733: /*
3734: IF p_is_new_party AND isMixNMatchEnabled = 'N' THEN
3737: store_classification (
3738: p_code_assignment_rec => p_code_assignment_rec,
3739: x_return_status => x_return_status
3740: );
3741: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3742: RETURN;
3743: END IF;
3744: END IF;
3745: */
3763: store_classification (
3764: p_code_assignment_rec => p_code_assignment_rec,
3765: x_return_status => x_return_status
3766: );
3767: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3768: RETURN;
3769: END IF;
3770: /*
3771: IF p_is_new_party AND isMixNMatchEnabled = 'N' THEN
3774: store_classification (
3775: p_code_assignment_rec => p_code_assignment_rec,
3776: x_return_status => x_return_status
3777: );
3778: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3779: RETURN;
3780: END IF;
3781: END IF;
3782: */
3802: store_classification (
3803: p_code_assignment_rec => p_code_assignment_rec,
3804: x_return_status => x_return_status
3805: );
3806: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3807: RETURN;
3808: END IF;
3809: /*
3810: IF p_is_new_party AND isMixNMatchEnabled = 'N' THEN
3813: store_classification (
3814: p_code_assignment_rec => p_code_assignment_rec,
3815: x_return_status => x_return_status
3816: );
3817: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3818: RETURN;
3819: END IF;
3820: END IF;
3821: */
3934: x_msg_count => l_msg_count,
3935: x_msg_data => l_msg_data
3936: );
3937:
3938: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3939: RETURN;
3940: END IF;
3941: l_create := TRUE;
3942: END IF;
4005: l_msg_count NUMBER;
4006: l_msg_data VARCHAR2(2000);
4007: l_object_version_number NUMBER;
4008: l_create BOOLEAN := FALSE;
4009: l_valid_tax_location VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
4010: l_party_site_id NUMBER;
4011:
4012: -- Bug 2882305 : Local variable to store address1
4013:
4056: 'U',
4057: l_valid_tax_location );
4058: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_LOCATION: l_valid_tax_location =' || l_valid_tax_location);
4059:
4060: IF l_valid_tax_location <> fnd_api.g_ret_sts_error THEN
4061: -- if the existing location record has same taxable components, update this record
4062:
4063: /* SELECT ps.location_id, loc.object_version_number
4064: INTO p_location_rec.location_id, l_object_version_number
4097: x_return_status,
4098: l_msg_count,
4099: l_msg_data);
4100:
4101: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4102: RETURN;
4103: END IF;
4104: --4227564
4105: BEGIN
4129: l_orig_sys_reference_rec.status := 'A';
4130: l_orig_sys_reference_rec.created_by_module := 'TCA_DNB_MAPPING';
4131:
4132: hz_orig_system_ref_pub.create_orig_system_reference(
4133: FND_API.G_FALSE,
4134: l_orig_sys_reference_rec,
4135: x_return_status,
4136: l_msg_count,
4137: l_msg_data);
4134: l_orig_sys_reference_rec,
4135: x_return_status,
4136: l_msg_count,
4137: l_msg_data);
4138: if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4139: RETURN;
4140: end if;
4141: END;
4142:
4202: );
4203: IF l_location_id IS NOT NULL THEN
4204: p_location_rec.location_id := l_location_id;
4205: END IF;
4206: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4207: RETURN;
4208: END IF;
4209: ELSE
4210: BEGIN
4213: --select by UPPER(ADDRESS1) to prevent dulicate.
4214: SELECT location_id INTO p_location_rec.location_id
4215: FROM hz_locations
4216: WHERE UPPER(ADDRESS1) = UPPER(p_location_rec.ADDRESS1) AND
4217: UPPER(NVL(ADDRESS2, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS2, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS2)) AND
4218: UPPER(NVL(ADDRESS3, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS3, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS3)) AND
4219: UPPER(NVL(ADDRESS4, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS4, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS4)) AND
4220: UPPER(NVL(CITY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.CITY, NULL, FND_API.G_MISS_CHAR, p_location_rec.CITY)) AND
4221: UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
4214: SELECT location_id INTO p_location_rec.location_id
4215: FROM hz_locations
4216: WHERE UPPER(ADDRESS1) = UPPER(p_location_rec.ADDRESS1) AND
4217: UPPER(NVL(ADDRESS2, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS2, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS2)) AND
4218: UPPER(NVL(ADDRESS3, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS3, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS3)) AND
4219: UPPER(NVL(ADDRESS4, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS4, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS4)) AND
4220: UPPER(NVL(CITY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.CITY, NULL, FND_API.G_MISS_CHAR, p_location_rec.CITY)) AND
4221: UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
4222: UPPER(NVL(COUNTY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.COUNTY, NULL, FND_API.G_MISS_CHAR, p_location_rec.COUNTY)) AND
4215: FROM hz_locations
4216: WHERE UPPER(ADDRESS1) = UPPER(p_location_rec.ADDRESS1) AND
4217: UPPER(NVL(ADDRESS2, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS2, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS2)) AND
4218: UPPER(NVL(ADDRESS3, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS3, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS3)) AND
4219: UPPER(NVL(ADDRESS4, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS4, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS4)) AND
4220: UPPER(NVL(CITY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.CITY, NULL, FND_API.G_MISS_CHAR, p_location_rec.CITY)) AND
4221: UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
4222: UPPER(NVL(COUNTY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.COUNTY, NULL, FND_API.G_MISS_CHAR, p_location_rec.COUNTY)) AND
4223: UPPER(NVL(POSTAL_CODE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.POSTAL_CODE, NULL, FND_API.G_MISS_CHAR, p_location_rec.POSTAL_CODE)) AND
4216: WHERE UPPER(ADDRESS1) = UPPER(p_location_rec.ADDRESS1) AND
4217: UPPER(NVL(ADDRESS2, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS2, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS2)) AND
4218: UPPER(NVL(ADDRESS3, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS3, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS3)) AND
4219: UPPER(NVL(ADDRESS4, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS4, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS4)) AND
4220: UPPER(NVL(CITY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.CITY, NULL, FND_API.G_MISS_CHAR, p_location_rec.CITY)) AND
4221: UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
4222: UPPER(NVL(COUNTY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.COUNTY, NULL, FND_API.G_MISS_CHAR, p_location_rec.COUNTY)) AND
4223: UPPER(NVL(POSTAL_CODE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.POSTAL_CODE, NULL, FND_API.G_MISS_CHAR, p_location_rec.POSTAL_CODE)) AND
4224: UPPER(COUNTRY) = UPPER(p_location_rec.COUNTRY) AND
4217: UPPER(NVL(ADDRESS2, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS2, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS2)) AND
4218: UPPER(NVL(ADDRESS3, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS3, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS3)) AND
4219: UPPER(NVL(ADDRESS4, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS4, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS4)) AND
4220: UPPER(NVL(CITY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.CITY, NULL, FND_API.G_MISS_CHAR, p_location_rec.CITY)) AND
4221: UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
4222: UPPER(NVL(COUNTY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.COUNTY, NULL, FND_API.G_MISS_CHAR, p_location_rec.COUNTY)) AND
4223: UPPER(NVL(POSTAL_CODE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.POSTAL_CODE, NULL, FND_API.G_MISS_CHAR, p_location_rec.POSTAL_CODE)) AND
4224: UPPER(COUNTRY) = UPPER(p_location_rec.COUNTRY) AND
4225: UPPER(NVL(PROVINCE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.PROVINCE, NULL, FND_API.G_MISS_CHAR, p_location_rec.PROVINCE)) AND
4218: UPPER(NVL(ADDRESS3, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS3, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS3)) AND
4219: UPPER(NVL(ADDRESS4, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS4, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS4)) AND
4220: UPPER(NVL(CITY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.CITY, NULL, FND_API.G_MISS_CHAR, p_location_rec.CITY)) AND
4221: UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
4222: UPPER(NVL(COUNTY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.COUNTY, NULL, FND_API.G_MISS_CHAR, p_location_rec.COUNTY)) AND
4223: UPPER(NVL(POSTAL_CODE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.POSTAL_CODE, NULL, FND_API.G_MISS_CHAR, p_location_rec.POSTAL_CODE)) AND
4224: UPPER(COUNTRY) = UPPER(p_location_rec.COUNTRY) AND
4225: UPPER(NVL(PROVINCE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.PROVINCE, NULL, FND_API.G_MISS_CHAR, p_location_rec.PROVINCE)) AND
4226: actual_content_source = p_location_rec.actual_content_source AND
4219: UPPER(NVL(ADDRESS4, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.ADDRESS4, NULL, FND_API.G_MISS_CHAR, p_location_rec.ADDRESS4)) AND
4220: UPPER(NVL(CITY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.CITY, NULL, FND_API.G_MISS_CHAR, p_location_rec.CITY)) AND
4221: UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
4222: UPPER(NVL(COUNTY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.COUNTY, NULL, FND_API.G_MISS_CHAR, p_location_rec.COUNTY)) AND
4223: UPPER(NVL(POSTAL_CODE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.POSTAL_CODE, NULL, FND_API.G_MISS_CHAR, p_location_rec.POSTAL_CODE)) AND
4224: UPPER(COUNTRY) = UPPER(p_location_rec.COUNTRY) AND
4225: UPPER(NVL(PROVINCE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.PROVINCE, NULL, FND_API.G_MISS_CHAR, p_location_rec.PROVINCE)) AND
4226: actual_content_source = p_location_rec.actual_content_source AND
4227: rownum =1;
4221: UPPER(NVL(STATE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.STATE, NULL, FND_API.G_MISS_CHAR, p_location_rec.STATE)) AND
4222: UPPER(NVL(COUNTY, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.COUNTY, NULL, FND_API.G_MISS_CHAR, p_location_rec.COUNTY)) AND
4223: UPPER(NVL(POSTAL_CODE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.POSTAL_CODE, NULL, FND_API.G_MISS_CHAR, p_location_rec.POSTAL_CODE)) AND
4224: UPPER(COUNTRY) = UPPER(p_location_rec.COUNTRY) AND
4225: UPPER(NVL(PROVINCE, FND_API.G_MISS_CHAR)) = UPPER(decode(p_location_rec.PROVINCE, NULL, FND_API.G_MISS_CHAR, p_location_rec.PROVINCE)) AND
4226: actual_content_source = p_location_rec.actual_content_source AND
4227: rownum =1;
4228:
4229: EXCEPTION WHEN NO_DATA_FOUND THEN
4252: END IF;
4253:
4254: arp_util.debug('HZ_MAP_PARTY_PUB.DO_STORE_LOCATION: created location with location_id '|| to_char(p_location_rec.location_id));
4255:
4256: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4257: RETURN;
4258: END IF;
4259:
4260: END;
4517: x_return_status,
4518: l_msg_count,
4519: l_msg_data);
4520:
4521: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4522: RETURN;
4523: END IF;
4524: --4227564
4525: BEGIN
4546: l_orig_sys_reference_rec.created_by_module := 'TCA_DNB_MAPPING';
4547:
4548:
4549: hz_orig_system_ref_pub.create_orig_system_reference(
4550: FND_API.G_FALSE,
4551: l_orig_sys_reference_rec,
4552: x_return_status,
4553: l_msg_count,
4554: l_msg_data);
4551: l_orig_sys_reference_rec,
4552: x_return_status,
4553: l_msg_count,
4554: l_msg_data);
4555: if x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4556: RETURN;
4557: end if;
4558: END;
4559: END IF;
4616: x_return_status,
4617: l_msg_count,
4618: l_msg_data);
4619:
4620: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4621: RETURN;
4622: END IF;
4623:
4624: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_CONTACT_POINT: created contact point, contact_point_id = ' || l_contact_point_id);
4723: l_msg_count,
4724: l_msg_data);
4725: */
4726:
4727: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4728: RETURN;
4729: END IF;
4730:
4731: ELSE
4842: -- For relationships, V1 API is used, so pass G_MISS values when
4843: -- we want to retain the previous values.
4844:
4845: /* Bug 4507494 : Pass NULL for non updatable columns
4846: p_fin_rep_rec.DATE_REPORT_ISSUED := FND_API.G_MISS_DATE;
4847: p_fin_rep_rec.REPORT_START_DATE := FND_API.G_MISS_DATE;
4848: p_fin_rep_rec.REPORT_END_DATE := FND_API.G_MISS_DATE;
4849: p_fin_rep_rec.ISSUED_PERIOD := FND_API.G_MISS_CHAR;
4850: */
4843: -- we want to retain the previous values.
4844:
4845: /* Bug 4507494 : Pass NULL for non updatable columns
4846: p_fin_rep_rec.DATE_REPORT_ISSUED := FND_API.G_MISS_DATE;
4847: p_fin_rep_rec.REPORT_START_DATE := FND_API.G_MISS_DATE;
4848: p_fin_rep_rec.REPORT_END_DATE := FND_API.G_MISS_DATE;
4849: p_fin_rep_rec.ISSUED_PERIOD := FND_API.G_MISS_CHAR;
4850: */
4851: p_fin_rep_rec.DATE_REPORT_ISSUED := NULL;
4844:
4845: /* Bug 4507494 : Pass NULL for non updatable columns
4846: p_fin_rep_rec.DATE_REPORT_ISSUED := FND_API.G_MISS_DATE;
4847: p_fin_rep_rec.REPORT_START_DATE := FND_API.G_MISS_DATE;
4848: p_fin_rep_rec.REPORT_END_DATE := FND_API.G_MISS_DATE;
4849: p_fin_rep_rec.ISSUED_PERIOD := FND_API.G_MISS_CHAR;
4850: */
4851: p_fin_rep_rec.DATE_REPORT_ISSUED := NULL;
4852: p_fin_rep_rec.REPORT_START_DATE := NULL;
4845: /* Bug 4507494 : Pass NULL for non updatable columns
4846: p_fin_rep_rec.DATE_REPORT_ISSUED := FND_API.G_MISS_DATE;
4847: p_fin_rep_rec.REPORT_START_DATE := FND_API.G_MISS_DATE;
4848: p_fin_rep_rec.REPORT_END_DATE := FND_API.G_MISS_DATE;
4849: p_fin_rep_rec.ISSUED_PERIOD := FND_API.G_MISS_CHAR;
4850: */
4851: p_fin_rep_rec.DATE_REPORT_ISSUED := NULL;
4852: p_fin_rep_rec.REPORT_START_DATE := NULL;
4853: p_fin_rep_rec.REPORT_END_DATE := NULL;
4880: l_msg_count,
4881: l_msg_data);
4882: */
4883:
4884: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4885: RETURN;
4886: END IF;
4887: END IF;
4888: EXCEPTION WHEN NO_DATA_FOUND THEN
5084: p_new_fin_report,
5085: l_fin_num_tab,
5086: x_return_status);
5087:
5088: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5089: RETURN;
5090: END IF;
5091:
5092: END IF;
5099: p_new_fin_report,
5100: l_fin_num_tab,
5101: x_return_status);
5102:
5103: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5104: RETURN;
5105: END IF;
5106:
5107: END IF;
5114: p_new_fin_report,
5115: l_fin_num_tab,
5116: x_return_status);
5117:
5118: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5119: RETURN;
5120: END IF;
5121: END IF;
5122:
5128: p_new_fin_report,
5129: l_fin_num_tab,
5130: x_return_status);
5131:
5132: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5133: RETURN;
5134: END IF;
5135: END IF;
5136:
5142: p_new_fin_report,
5143: l_fin_num_tab,
5144: x_return_status);
5145:
5146: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5147: RETURN;
5148: END IF;
5149: END IF;
5150:
5156: p_new_fin_report,
5157: l_fin_num_tab,
5158: x_return_status);
5159:
5160: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5161: RETURN;
5162: END IF;
5163: END IF;
5164:
5170: p_new_fin_report,
5171: l_fin_num_tab,
5172: x_return_status);
5173:
5174: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5175: RETURN;
5176: END IF;
5177: END IF;
5178:
5184: p_new_fin_report,
5185: l_fin_num_tab,
5186: x_return_status);
5187:
5188: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5189: RETURN;
5190: END IF;
5191: END IF;
5192:
5198: p_new_fin_report,
5199: l_fin_num_tab,
5200: x_return_status);
5201:
5202: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5203: RETURN;
5204: END IF;
5205: END IF;
5206:
5212: p_new_fin_report,
5213: l_fin_num_tab,
5214: x_return_status);
5215:
5216: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5217: RETURN;
5218: END IF;
5219: END IF;
5220:
5226: p_new_fin_report,
5227: l_fin_num_tab,
5228: x_return_status);
5229:
5230: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5231: RETURN;
5232: END IF;
5233: END IF;
5234:
5240: p_new_fin_report,
5241: l_fin_num_tab,
5242: x_return_status);
5243:
5244: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5245: RETURN;
5246: END IF;
5247: END IF;
5248:
5254: p_new_fin_report,
5255: l_fin_num_tab,
5256: x_return_status);
5257:
5258: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5259: RETURN;
5260: END IF;
5261: END IF;
5262:
5268: p_new_fin_report,
5269: l_fin_num_tab,
5270: x_return_status);
5271:
5272: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5273: RETURN;
5274: END IF;
5275: END IF;
5276:
5282: p_new_fin_report,
5283: l_fin_num_tab,
5284: x_return_status);
5285:
5286: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5287: RETURN;
5288: END IF;
5289: END IF;
5290:
5296: p_new_fin_report,
5297: l_fin_num_tab,
5298: x_return_status);
5299:
5300: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5301: RETURN;
5302: END IF;
5303: END IF;
5304:
5311: p_new_fin_report,
5312: l_fin_num_tab,
5313: x_return_status);
5314:
5315: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5316: RETURN;
5317: END IF;
5318: END IF;
5319:
5326: p_new_fin_report,
5327: l_fin_num_tab,
5328: x_return_status);
5329:
5330: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5331: RETURN;
5332: END IF;
5333: END IF;
5334:
5340: p_new_fin_report,
5341: l_fin_num_tab,
5342: x_return_status);
5343:
5344: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5345: RETURN;
5346: END IF;
5347: END IF;
5348:
5370: p_new_fin_report,
5371: l_fin_num_tab,
5372: x_return_status);
5373:
5374: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5375: RETURN;
5376: END IF;
5377: END IF;
5378:
5402: p_new_fin_report,
5403: l_fin_num_tab,
5404: x_return_status);
5405:
5406: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5407: RETURN;
5408: END IF;
5409: END IF;
5410:
5416: p_new_fin_report,
5417: l_fin_num_tab,
5418: x_return_status);
5419:
5420: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5421: RETURN;
5422: END IF;
5423: END IF;
5424:
5430: p_new_fin_report,
5431: l_fin_num_tab,
5432: x_return_status);
5433:
5434: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5435: RETURN;
5436: END IF;
5437: END IF;
5438:
5444: p_new_fin_report,
5445: l_fin_num_tab,
5446: x_return_status);
5447:
5448: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5449: RETURN;
5450: END IF;
5451: END IF;
5452:
5458: p_new_fin_report,
5459: l_fin_num_tab,
5460: x_return_status);
5461:
5462: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5463: RETURN;
5464: END IF;
5465: END IF;
5466:
5472: p_new_fin_report,
5473: l_fin_num_tab,
5474: x_return_status);
5475:
5476: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5477: RETURN;
5478: END IF;
5479: END IF;
5480:
5486: p_new_fin_report,
5487: l_fin_num_tab,
5488: x_return_status);
5489:
5490: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5491: RETURN;
5492: END IF;
5493: END IF;
5494:
5635: l_msg_count,
5636: l_msg_data,
5637: l_financial_number_id);
5638: */
5639: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
5640: RETURN;
5641: END IF;
5642:
5643: --else some fin num may be existing. check in db to see if fin num exists. If it does, update it, else create it.
5747: | If previous status was W1, set it to W2.
5748: +===========================================================================*/
5749:
5750: procedure store_related_duns(
5751: p_commit IN VARCHAR2:= FND_API.G_FALSE,
5752: p_group_id IN NUMBER,
5753: x_return_status IN OUT NOCOPY VARCHAR2
5754: ) IS
5755: CURSOR c1 IS
5754: ) IS
5755: CURSOR c1 IS
5756: SELECT * FROM
5757: HZ_PARTY_INTERFACE
5758: WHERE NVL(group_id, FND_API.G_MISS_NUM) = NVL(p_group_id, FND_API.G_MISS_NUM)
5759: AND status IN ('P1','W1'); -- Bug 3107162
5760:
5761: l_interface_rec HZ_PARTY_INTERFACE%ROWTYPE;
5762: l_related_duns_rec related_duns_rec_type;
5792: --Set Savepoint
5793: SAVEPOINT store_related_duns_pub;
5794:
5795: --Initialize API return status to success.
5796: x_return_status := FND_API.G_RET_STS_SUCCESS;
5797:
5798: --VJN :: Initialize dnb hierarchy variables
5799:
5800: conform_party_id := null;
5850: --If DUNS=Parent DUNS (DNB should be sending the Parent data if the company is the final Parent.),
5851: --do_store_related_duns will not be called.
5852: --
5853:
5854: IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
5855: l_interface_rec.PARENT_DUNS_NUMBER IS NOT NULL THEN
5856:
5857: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_RELATED_DUNS: PARENT');
5858:
5877: --
5878: --DOM_ULT
5879: --
5880:
5881: IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
5882: l_interface_rec.DOM_ULT_DUNS_NUMBER IS NOT NULL THEN
5883:
5884: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_RELATED_DUNS: DOM_ULT');
5885:
5903: --
5904: --GLB_ULT
5905: --
5906:
5907: IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
5908: l_interface_rec.GLB_ULT_DUNS_NUMBER IS NOT NULL THEN
5909:
5910: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_RELATED_DUNS: GLB_ULT');
5911:
5932: -- THE PROFILE OPTION 'HZ_DNB_HIER_STOP_CONFORM' IS INTRODUCED FOR DEBUGGING PURPOSES.
5933: -- IF THIS OPTION DOES NOT EXIST, THE MAPPING PROGRAM WILL ALWAYS CONFORM A PURCHASED PARTY
5934: -- IF THIS OPTION EXISTS, THEN CONFORMATION WILL BE AS PER THE FLAGS MENTIONED ABOVE.
5935:
5936: IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
5937: nvl(fnd_profile.value('HZ_DNB_HIER_STOP_CONFORM'),'N') = 'N'
5938: THEN
5939: IF fnd_profile.value('HZ_GENERATE_PARTY_NUMBER') = 'N' THEN
5940: fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'Y');
5956: --
5957: --Check return status
5958: --
5959:
5960: IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
5961: IF l_interface_rec.status = 'W1' THEN -- Bug 3107162
5962: UPDATE hz_party_interface
5963: SET status = 'W2'
5964: WHERE party_interface_id = l_interface_rec.party_interface_id;
5976: 'S'
5977: );
5978:
5979: arp_util.debug('HZ_MAP_PARTY_PUB.STORE_RELATED_DUNS: update request log status to S');
5980: IF FND_API.to_Boolean(p_commit) THEN
5981: commit;
5982: END IF;
5983:
5984: ELSE
5999:
6000:
6001:
6002: EXCEPTION
6003: WHEN FND_API.G_EXC_ERROR THEN
6004: ROLLBACK TO store_related_duns_pub;
6005: x_return_status := FND_API.G_RET_STS_ERROR;
6006:
6007: do_update_request_log(
6001:
6002: EXCEPTION
6003: WHEN FND_API.G_EXC_ERROR THEN
6004: ROLLBACK TO store_related_duns_pub;
6005: x_return_status := FND_API.G_RET_STS_ERROR;
6006:
6007: do_update_request_log(
6008: l_interface_rec.request_log_id,
6009: l_interface_rec.party_id,
6013: store_error(
6014: p_status => 'E2',
6015: p_party_interface_id => l_interface_rec.party_interface_id);
6016:
6017: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6018: ROLLBACK TO store_related_duns_pub;
6019: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6020:
6021: do_update_request_log(
6015: p_party_interface_id => l_interface_rec.party_interface_id);
6016:
6017: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6018: ROLLBACK TO store_related_duns_pub;
6019: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6020:
6021: do_update_request_log(
6022: l_interface_rec.request_log_id,
6023: l_interface_rec.party_id,
6029: p_party_interface_id => l_interface_rec.party_interface_id);
6030:
6031: WHEN OTHERS THEN
6032: ROLLBACK TO store_related_duns_pub;
6033: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6034:
6035: FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
6036: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
6037: FND_MSG_PUB.ADD;
6223: IF l_profile = 'N' THEN
6224: fnd_profile.put('HZ_GENERATE_PARTY_NUMBER', 'N');
6225: END IF;
6226:
6227: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6228: RETURN;
6229: END IF;
6230:
6231: arp_util.debug('HZ_MAP_PARTY_PUB.DO_STORE_RELATED_DUNS: party does not exist: created party with party_id ' || to_char(l_organization_rec.party_rec.party_id));
6250:
6251: -- VJN ::: POPULATE PASSED IN OUT VARIABLE
6252: x_conform_party_id := l_party_rel_rec.subject_id ;
6253:
6254: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6255: RETURN;
6256: END IF;
6257:
6258: l_contact_points_rec.owner_table_id := l_organization_rec.party_rec.party_id;
6266: l_phone_rec,
6267: x_return_status);
6268: END IF;
6269:
6270: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6271: RETURN;
6272: END IF;
6273:
6274: --Bug 3550989 : Pass orig_system and orig_system_reference to create_org API
6299: x_return_status);
6300: -- VJN ::: POPULATE PASSED IN OUT VARIABLE
6301: x_conform_party_id := l_party_rel_rec.subject_id ;
6302:
6303: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6304: RETURN;
6305: END IF;
6306:
6307: l_contact_points_rec.owner_table_id := l_party_id;
6315: l_phone_rec,
6316: x_return_status);
6317: END IF;
6318:
6319: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6320: RETURN;
6321: END IF;
6322: -- Bug 3417357 : Pass p_create_new=FALSE when party is updated
6323: -- Bug 4534494: need to pass OSR
6327: l_party_id,
6328: FALSE,
6329: x_return_status);
6330:
6331: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6332: RETURN;
6333: END IF;
6334:
6335:
6492: x_return_status,
6493: l_msg_count,
6494: l_msg_data);
6495:
6496: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6497: RETURN;
6498: END IF;
6499: END LOOP;
6500: CLOSE c_old_parent_hq;
6567: x_return_status,
6568: l_msg_count,
6569: l_msg_data);
6570:
6571: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
6572: RETURN;
6573: END IF;
6574: l_create_rel := TRUE;
6575: END IF;
6660: FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
6661: -- SELECT hz_party_interface_errors_s.nextval INTO l_interface_error_id from dual;
6662:
6663: l_message_text := FND_MSG_PUB.Get(
6664: p_encoded => FND_API.G_FALSE );
6665:
6666: INSERT INTO hz_party_interface_errors (
6667: interface_error_id,
6668: party_interface_id,
6741:
6742: l_x_return_status VARCHAR2(1);
6743: l_x_msg_count NUMBER;
6744: l_x_msg_data VARCHAR2(2000);
6745: l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
6746:
6747: l_count NUMBER;
6748: l_msg_data VARCHAR2(2000);
6749: BEGIN
6755: 'Concurrent program ARHMAPSB - Map DNB data(+)' );
6756:
6757: map(
6758: 1,
6759: FND_API.G_TRUE,
6760: FND_API.G_TRUE,
6761: l_group_id,
6762: l_x_return_status,
6763: l_x_msg_count,
6756:
6757: map(
6758: 1,
6759: FND_API.G_TRUE,
6760: FND_API.G_TRUE,
6761: l_group_id,
6762: l_x_return_status,
6763: l_x_msg_count,
6764: l_x_msg_data,
6776:
6777: --write error message into log file
6778: FOR l_count IN 1..l_x_msg_count LOOP
6779: l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,
6780: FND_API.G_FALSE);
6781: FND_FILE.PUT_LINE( FND_FILE.LOG,
6782: TO_CHAR(l_count) || ' : ' || l_msg_data );
6783: END LOOP;
6784: -- END IF;
6869: END IF;
6870: CLOSE c_check_lookups;
6871: EXCEPTION
6872:
6873: WHEN FND_API.G_EXC_ERROR THEN
6874:
6875: ROLLBACK TO create_lookup;
6876: x_return_status := FND_API.G_RET_STS_ERROR;
6877: FND_MSG_PUB.Count_And_Get(
6872:
6873: WHEN FND_API.G_EXC_ERROR THEN
6874:
6875: ROLLBACK TO create_lookup;
6876: x_return_status := FND_API.G_RET_STS_ERROR;
6877: FND_MSG_PUB.Count_And_Get(
6878: p_encoded => FND_API.G_FALSE,
6879: p_count => x_msg_count,
6880: p_data => x_msg_data);
6874:
6875: ROLLBACK TO create_lookup;
6876: x_return_status := FND_API.G_RET_STS_ERROR;
6877: FND_MSG_PUB.Count_And_Get(
6878: p_encoded => FND_API.G_FALSE,
6879: p_count => x_msg_count,
6880: p_data => x_msg_data);
6881:
6882: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6878: p_encoded => FND_API.G_FALSE,
6879: p_count => x_msg_count,
6880: p_data => x_msg_data);
6881:
6882: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6883:
6884: ROLLBACK TO create_lookup ;
6885: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6886: FND_MSG_PUB.Count_And_Get(
6881:
6882: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6883:
6884: ROLLBACK TO create_lookup ;
6885: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6886: FND_MSG_PUB.Count_And_Get(
6887: p_encoded => FND_API.G_FALSE,
6888: p_count => x_msg_count,
6889: p_data => x_msg_data);
6883:
6884: ROLLBACK TO create_lookup ;
6885: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6886: FND_MSG_PUB.Count_And_Get(
6887: p_encoded => FND_API.G_FALSE,
6888: p_count => x_msg_count,
6889: p_data => x_msg_data);
6890:
6891: WHEN OTHERS THEN
6890:
6891: WHEN OTHERS THEN
6892:
6893: ROLLBACK TO create_lookup;
6894: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6895: FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
6896: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
6897: FND_MSG_PUB.ADD;
6898: FND_MSG_PUB.Count_And_Get(
6895: FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
6896: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
6897: FND_MSG_PUB.ADD;
6898: FND_MSG_PUB.Count_And_Get(
6899: p_encoded => FND_API.G_FALSE,
6900: p_count => x_msg_count,
6901: p_data => x_msg_data);
6902:
6903: END create_lookup;
7238: END IF;
7239:
7240: EXCEPTION
7241: WHEN OTHERS THEN
7242: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
7243: FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
7244: FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
7245: FND_MSG_PUB.ADD;
7246: END create_dynamic_lookups;