[Home] [Help]
263: END IF;
264: l_valid := FALSE;
265: fnd_message.set_name('INV','INV_MISSING_REQUIRED_PARAMETER');
266: fnd_msg_pub.ADD;
267: RAISE fnd_api.g_EXC_ERROR;
268: END IF;
269: IF l_debug = 1 THEN
270: mdebug('Validate Params: Required params passed. ');
271: END IF;
278: mdebug('Validate Params: Same Parent and Category Set Id');
279: END IF;
280: fnd_message.set_name('INV','INV_SAME_CATEGORY_SETS');
281: fnd_msg_pub.ADD;
282: RAISE fnd_api.g_EXC_ERROR;
283: END IF;
284: IF l_debug = 1 THEN
285: mdebug('Validate Params: Parent and Child category ids are diff');
286: END IF;
301: fnd_message.set_name('INV','INV_INVALID_PARAMETER');
302: fnd_message.set_token('PARAM_NAME', 'CATEGORY_ID');
303: fnd_message.set_token('PROGRAM_NAME', G_PKG_NAME||'.'||p_calling_api);
304: fnd_msg_pub.ADD;
305: RAISE fnd_api.g_EXC_ERROR;
306: END IF;
307: IF l_debug = 1 THEN
308: mdebug('Validate Params: Category Id is valid for insert');
309: END IF;
322: mdebug('Validate Params: Record not available for update');
323: END IF;
324: fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_UPDATE');
325: fnd_msg_pub.ADD;
326: RAISE fnd_api.g_EXC_ERROR;
327: END IF;
328: IF l_debug = 1 THEN
329: mdebug('Validate Params: Record exists in mtl_category_set_valid_cats ');
330: END IF;
348: mdebug('Validate Params: You might create loops!! ');
349: END IF;
350: fnd_message.set_name('INV','INV_CATEGORY_LOOPS_ERR');
351: fnd_msg_pub.ADD;
352: RAISE fnd_api.g_EXC_ERROR;
353: END IF;
354: IF l_debug = 1 THEN
355: mdebug('Validate Params: No loops after updation ');
356: END IF;
371: mdebug('Validate Params: User wishes to create a leaf node ');
372: END IF;
373: -- fnd_message.set_name('INV','INV_MISSING_PARENT_CAT');
374: -- fnd_msg_pub.ADD;
375: -- RAISE fnd_api.g_EXC_ERROR;
376: ELSE
377: --
378: -- check whether the parent category id is valid
379: --
388: fnd_message.set_token('PARAM_NAME', 'PARENT_CATEGORY_ID');
389: fnd_message.set_token('PROGRAM_NAME',
390: G_PKG_NAME||'.'||p_calling_api);
391: fnd_msg_pub.ADD;
392: RAISE fnd_api.g_EXC_ERROR;
393: END IF;
394: IF l_debug = 1 THEN
395: mdebug('Validate Params: Parent category id is valid in mtl_categories_b ');
396: END IF;
528: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
529: BEGIN
530:
531: IF ( p_bool = TRUE ) THEN
532: RETURN fnd_api.g_TRUE ;
533: ELSIF ( p_bool = FALSE ) THEN
534: RETURN fnd_api.g_FALSE ;
535: ELSE
536: NULL;
530:
531: IF ( p_bool = TRUE ) THEN
532: RETURN fnd_api.g_TRUE ;
533: ELSIF ( p_bool = FALSE ) THEN
534: RETURN fnd_api.g_FALSE ;
535: ELSE
536: NULL;
537: END IF;
538:
695: IF (l_debug = 1) THEN
696: mdebug('ERR: No Product Categories structure');
697: END IF;
698: CLOSE get_structure_id;
699: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
700: END IF;
701: CLOSE get_structure_id;
702: /* Bug 5474569 End */
703:
724: fnd_msg_pub.ADD;
725: IF (l_debug = 1) THEN
726: mdebug('Flex Structure Information needed');
727: END IF;
728: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
729: END IF;
730:
731: IF (x_category_rec.structure_id = g_MISS_NUM OR
732: x_category_rec.structure_id IS NULL) AND
745: IF (l_debug = 1) THEN
746: mdebug('ERR: Invalid Flex Structure information provided');
747: END IF;
748: CLOSE get_structure_id;
749: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
750: END IF;
751: CLOSE get_structure_id;
752: END IF;
753:
762: IF (l_debug = 1) THEN
763: mdebug('Invalid Flex Structure information provided');
764: END IF;
765: CLOSE validate_structure_id;
766: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
767: END IF;
768: CLOSE validate_structure_id;
769: END IF;
770: --Bug: 2645153
774: if (l_product_str_id <> x_category_rec.structure_id and
775: nvl(x_category_rec.summary_flag, 'N' ) = 'Y' ) then
776: fnd_message.set_name('INV','INV_CAT_SUM_FLAG_ERR');
777: fnd_msg_pub.ADD;
778: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
779: end if;
780: /* Bug 5474569 End */
781:
782: IF x_category_rec.attribute_category = g_MISS_CHAR THEN
960: fnd_msg_pub.ADD;
961: IF (l_debug = 1) THEN
962: mdebug('Category Id needed for Update');
963: END IF;
964: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
965: END IF;
966:
967: IF (x_category_rec.structure_id = g_MISS_NUM OR
968: x_category_rec.structure_id IS NULL) AND
977: IF (l_debug = 1) THEN
978: mdebug('Invalid Flex Structure information provided');
979: END IF;
980: CLOSE get_category_structure_id;
981: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
982: END IF;
983: CLOSE get_category_structure_id;
984: /*
985: fnd_message.set_name('INV','INV_FLEX_STRUCTURE_REQ');
986: fnd_msg_pub.ADD;
987: IF (l_debug = 1) THEN
988: mdebug('Flex Structure Information needed');
989: END IF;
990: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
991: */
992: END IF;
993:
994: IF (x_category_rec.structure_id = g_MISS_NUM OR
1003: fnd_msg_pub.ADD;
1004: IF (l_debug = 1) THEN
1005: mdebug('Invalid Flex Structure information provided');
1006: END IF;
1007: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
1008: END IF;
1009: CLOSE get_structure_id;
1010: END IF;
1011:
1016: if (l_product_str_id <> x_category_rec.structure_id and
1017: nvl(x_category_rec.summary_flag, 'N' ) = 'Y' ) then
1018: fnd_message.set_name('INV','INV_CAT_SUM_FLAG_ERR');
1019: fnd_msg_pub.ADD;
1020: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
1021: end if;
1022: /* Bug 5474569 End */
1023:
1024: /* Before further processing we get the info. from Database */
1075: IF (l_debug = 1) THEN
1076: mdebug('Invalid Category Id provided');
1077: END IF;
1078: CLOSE category_rec_cursor;
1079: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
1080: END IF;
1081: CLOSE category_rec_cursor;
1082:
1083: IF x_category_rec.description = g_MISS_CHAR THEN
1295: IF (l_debug = 1) THEN
1296: mdebug('ValueSet Validation Error : '||l_trim_str);
1297: END IF;
1298:
1299: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1300: END IF;
1301: END ValueSet_Validate;
1302:
1303: ----------------------------------------------------------------------------
1358: fnd_msg_pub.ADD;
1359: IF (l_debug = 1) THEN
1360: mdebug('Delimiter is NULL...Error');
1361: END IF;
1362: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1363: END IF;
1364:
1365: --Start: 3893482
1366: l_indx := 1;
1472: fnd_msg_pub.ADD;
1473: IF (l_debug = 1) THEN
1474: mdebug('CCID already exists => '|| To_char(FND_FLEX_KEYVAL.combination_id));
1475: END IF;
1476: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477:
1478: ELSIF (p_operation = G_UPDATE) THEN
1479: IF (FND_FLEX_KEYVAL.combination_id <>
1480: p_category_rec.category_id) THEN
1483: fnd_msg_pub.ADD;
1484: IF (l_debug = 1) THEN
1485: mdebug( 'Code combination already used for another category');
1486: END IF;
1487: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1488: ELSE
1489: ValueSet_Validate(l_structure_id, l_concat_segs);
1490: IF (l_debug = 1) THEN
1491: mdebug('Updating CCID/Category_Id => '|| To_char(FND_FLEX_KEYVAL.combination_id));
1527: fnd_msg_pub.ADD;
1528: IF (l_debug = 1) THEN
1529: mdebug('Trying to update a non-existant ROW');
1530: END IF;
1531: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1532:
1533: ELSE -- neither insert nor update
1534: NULL;
1535: END IF;
1542: ----------------------------------------------------------------------------
1543: PROCEDURE Create_Category
1544: (
1545: p_api_version IN NUMBER ,
1546: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1547: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1548: x_return_status OUT NOCOPY VARCHAR2 ,
1549: x_errorcode OUT NOCOPY NUMBER,
1550: x_msg_count OUT NOCOPY NUMBER ,
1543: PROCEDURE Create_Category
1544: (
1545: p_api_version IN NUMBER ,
1546: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1547: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1548: x_return_status OUT NOCOPY VARCHAR2 ,
1549: x_errorcode OUT NOCOPY NUMBER,
1550: x_msg_count OUT NOCOPY NUMBER ,
1551: x_msg_data OUT NOCOPY VARCHAR2 ,
1589: -- Standard Start of API savepoint
1590: SAVEPOINT Create_Category_PUB;
1591:
1592: -- Check for call compatibility.
1593: IF NOT FND_API.Compatible_API_Call (l_api_version,
1594: p_api_version,
1595: l_api_name,
1596: G_PKG_NAME)
1597: THEN
1594: p_api_version,
1595: l_api_name,
1596: G_PKG_NAME)
1597: THEN
1598: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1599: END IF;
1600: -- Initialize API message list if necessary.
1601: -- Initialize message list if p_init_msg_list is set to TRUE.
1602: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1598: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1599: END IF;
1600: -- Initialize API message list if necessary.
1601: -- Initialize message list if p_init_msg_list is set to TRUE.
1602: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1603: FND_MSG_PUB.initialize;
1604: END IF;
1605:
1606: IF (l_debug = 1) THEN
1710: mdebug('Tracing....10');
1711: END IF;
1712:
1713: -- Standard check of p_commit.
1714: IF FND_API.To_Boolean( p_commit ) THEN
1715: COMMIT WORK;
1716: END IF;
1717:
1718: x_return_status := FND_API.G_RET_STS_SUCCESS;
1714: IF FND_API.To_Boolean( p_commit ) THEN
1715: COMMIT WORK;
1716: END IF;
1717:
1718: x_return_status := FND_API.G_RET_STS_SUCCESS;
1719: -- Standard call to get message count and if count is 1,
1720: -- get message info.
1721: -- The client will directly display the x_msg_data (which is already
1722: -- translated) if the x_msg_count = 1;
1730: ( p_count => x_msg_count,
1731: p_data => x_msg_data
1732: );
1733: EXCEPTION
1734: WHEN FND_API.G_EXC_ERROR THEN
1735: IF (l_debug = 1) THEN
1736: mdebug('Ending : Returning ERROR');
1737: END IF;
1738: ROLLBACK TO Create_Category_PUB;
1735: IF (l_debug = 1) THEN
1736: mdebug('Ending : Returning ERROR');
1737: END IF;
1738: ROLLBACK TO Create_Category_PUB;
1739: x_return_status := FND_API.G_RET_STS_ERROR;
1740: FND_MSG_PUB.Count_And_Get
1741: ( p_count => x_msg_count,
1742: p_data => x_msg_data
1743: );
1740: FND_MSG_PUB.Count_And_Get
1741: ( p_count => x_msg_count,
1742: p_data => x_msg_data
1743: );
1744: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1745: ROLLBACK TO Create_Category_PUB;
1746: IF (l_debug = 1) THEN
1747: mdebug('Ending : Returning UNEXPECTED ERROR');
1748: END IF;
1745: ROLLBACK TO Create_Category_PUB;
1746: IF (l_debug = 1) THEN
1747: mdebug('Ending : Returning UNEXPECTED ERROR');
1748: END IF;
1749: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1750: FND_MSG_PUB.Count_And_Get
1751: ( p_count => x_msg_count,
1752: p_data => x_msg_data
1753: );
1755: ROLLBACK TO Create_Category_PUB;
1756: IF (l_debug = 1) THEN
1757: mdebug('Ending : Returning UNEXPECTED ERROR');
1758: END IF;
1759: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1760: IF FND_MSG_PUB.Check_Msg_Level
1761: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1762: THEN
1763: FND_MSG_PUB.Add_Exc_Msg
1778: ----------------------------------------------------------------------------
1779: PROCEDURE Update_Category
1780: (
1781: p_api_version IN NUMBER ,
1782: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1783: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1784: x_return_status OUT NOCOPY VARCHAR2 ,
1785: x_errorcode OUT NOCOPY NUMBER,
1786: x_msg_count OUT NOCOPY NUMBER ,
1779: PROCEDURE Update_Category
1780: (
1781: p_api_version IN NUMBER ,
1782: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1783: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1784: x_return_status OUT NOCOPY VARCHAR2 ,
1785: x_errorcode OUT NOCOPY NUMBER,
1786: x_msg_count OUT NOCOPY NUMBER ,
1787: x_msg_data OUT NOCOPY VARCHAR2 ,
1818: -- Standard Start of API savepoint
1819: SAVEPOINT Update_Category_PUB;
1820:
1821:
1822: IF NOT FND_API.Compatible_API_Call (l_api_version,
1823: p_api_version ,
1824: l_api_name ,
1825: G_PKG_NAME)
1826: THEN
1823: p_api_version ,
1824: l_api_name ,
1825: G_PKG_NAME)
1826: THEN
1827: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828: END IF;
1829: -- Initialize API message list if necessary.
1830: -- Initialize message list if p_init_msg_list is set to TRUE.
1831: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1827: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828: END IF;
1829: -- Initialize API message list if necessary.
1830: -- Initialize message list if p_init_msg_list is set to TRUE.
1831: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1832: FND_MSG_PUB.initialize;
1833: END IF;
1834:
1835: IF (l_debug = 1) THEN
1923: mdebug('Update_Category:: Tracing....10');
1924: END IF;
1925:
1926: -- Standard check of p_commit.
1927: IF FND_API.To_Boolean( p_commit ) THEN
1928: COMMIT WORK;
1929: END IF;
1930:
1931: x_return_status := FND_API.G_RET_STS_SUCCESS;
1927: IF FND_API.To_Boolean( p_commit ) THEN
1928: COMMIT WORK;
1929: END IF;
1930:
1931: x_return_status := FND_API.G_RET_STS_SUCCESS;
1932: -- Standard call to get message count and if count is 1,
1933: -- get message info.
1934: -- The client will directly display the x_msg_data (which is already
1935: -- translated) if the x_msg_count = 1;
1940: ( p_count => x_msg_count,
1941: p_data => x_msg_data
1942: );
1943: EXCEPTION
1944: WHEN FND_API.G_EXC_ERROR THEN
1945: ROLLBACK TO Update_Category_PUB;
1946: x_return_status := FND_API.G_RET_STS_ERROR;
1947: FND_MSG_PUB.Count_And_Get
1948: ( p_count => x_msg_count,
1942: );
1943: EXCEPTION
1944: WHEN FND_API.G_EXC_ERROR THEN
1945: ROLLBACK TO Update_Category_PUB;
1946: x_return_status := FND_API.G_RET_STS_ERROR;
1947: FND_MSG_PUB.Count_And_Get
1948: ( p_count => x_msg_count,
1949: p_data => x_msg_data
1950: );
1947: FND_MSG_PUB.Count_And_Get
1948: ( p_count => x_msg_count,
1949: p_data => x_msg_data
1950: );
1951: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1952: ROLLBACK TO Update_Category_PUB;
1953: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1954: FND_MSG_PUB.Count_And_Get
1955: ( p_count => x_msg_count,
1949: p_data => x_msg_data
1950: );
1951: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1952: ROLLBACK TO Update_Category_PUB;
1953: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1954: FND_MSG_PUB.Count_And_Get
1955: ( p_count => x_msg_count,
1956: p_data => x_msg_data
1957: );
1956: p_data => x_msg_data
1957: );
1958: WHEN OTHERS THEN
1959: ROLLBACK TO Update_Category_PUB;
1960: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1961: IF FND_MSG_PUB.Check_Msg_Level
1962: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1963: THEN
1964: FND_MSG_PUB.Add_Exc_Msg
1981: ----------------------------------------------------------------------------
1982: PROCEDURE Update_Category_Description
1983: (
1984: p_api_version IN NUMBER ,
1985: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1986: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1987: x_return_status OUT NOCOPY VARCHAR2 ,
1988: x_errorcode OUT NOCOPY NUMBER,
1989: x_msg_count OUT NOCOPY NUMBER ,
1982: PROCEDURE Update_Category_Description
1983: (
1984: p_api_version IN NUMBER ,
1985: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1986: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1987: x_return_status OUT NOCOPY VARCHAR2 ,
1988: x_errorcode OUT NOCOPY NUMBER,
1989: x_msg_count OUT NOCOPY NUMBER ,
1990: x_msg_data OUT NOCOPY VARCHAR2 ,
2017: SAVEPOINT Update_Category_Desc_PUB;
2018:
2019:
2020: -- Check for call compatibility.
2021: IF NOT FND_API.Compatible_API_Call (l_api_version,
2022: p_api_version ,
2023: l_api_name ,
2024: G_PKG_NAME)
2025: THEN
2022: p_api_version ,
2023: l_api_name ,
2024: G_PKG_NAME)
2025: THEN
2026: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2027: END IF;
2028: -- Initialize API message list if necessary.
2029: -- Initialize message list if p_init_msg_list is set to TRUE.
2030: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2026: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2027: END IF;
2028: -- Initialize API message list if necessary.
2029: -- Initialize message list if p_init_msg_list is set to TRUE.
2030: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2031: FND_MSG_PUB.initialize;
2032: END IF;
2033:
2034: UPDATE mtl_categories_tl
2050: RAISE NO_DATA_FOUND;
2051: END IF;
2052:
2053: -- Standard check of p_commit.
2054: IF FND_API.To_Boolean( p_commit ) THEN
2055: COMMIT WORK;
2056: END IF;
2057:
2058: x_return_status := FND_API.G_RET_STS_SUCCESS;
2054: IF FND_API.To_Boolean( p_commit ) THEN
2055: COMMIT WORK;
2056: END IF;
2057:
2058: x_return_status := FND_API.G_RET_STS_SUCCESS;
2059: -- Standard call to get message count and if count is 1,
2060: -- get message info.
2061: -- The client will directly display the x_msg_data (which is already
2062: -- translated) if the x_msg_count = 1;
2068: p_data => x_msg_data
2069: );
2070:
2071: EXCEPTION
2072: WHEN FND_API.G_EXC_ERROR THEN
2073: ROLLBACK TO Update_Category_Desc_PUB;
2074: x_return_status := FND_API.G_RET_STS_ERROR;
2075: FND_MSG_PUB.Count_And_Get
2076: ( p_count => x_msg_count,
2070:
2071: EXCEPTION
2072: WHEN FND_API.G_EXC_ERROR THEN
2073: ROLLBACK TO Update_Category_Desc_PUB;
2074: x_return_status := FND_API.G_RET_STS_ERROR;
2075: FND_MSG_PUB.Count_And_Get
2076: ( p_count => x_msg_count,
2077: p_data => x_msg_data
2078: );
2075: FND_MSG_PUB.Count_And_Get
2076: ( p_count => x_msg_count,
2077: p_data => x_msg_data
2078: );
2079: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2080: ROLLBACK TO Update_Category_Desc_PUB;
2081: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2082: FND_MSG_PUB.Count_And_Get
2083: ( p_count => x_msg_count,
2077: p_data => x_msg_data
2078: );
2079: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2080: ROLLBACK TO Update_Category_Desc_PUB;
2081: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2082: FND_MSG_PUB.Count_And_Get
2083: ( p_count => x_msg_count,
2084: p_data => x_msg_data
2085: );
2084: p_data => x_msg_data
2085: );
2086: WHEN OTHERS THEN
2087: ROLLBACK TO Update_Category_Desc_PUB;
2088: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2089: IF FND_MSG_PUB.Check_Msg_Level
2090: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2091: THEN
2092: FND_MSG_PUB.Add_Exc_Msg
2110:
2111: PROCEDURE Delete_Category
2112: (
2113: p_api_version IN NUMBER ,
2114: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2115: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2116: x_return_status OUT NOCOPY VARCHAR2 ,
2117: x_errorcode OUT NOCOPY NUMBER,
2118: x_msg_count OUT NOCOPY NUMBER ,
2111: PROCEDURE Delete_Category
2112: (
2113: p_api_version IN NUMBER ,
2114: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2115: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2116: x_return_status OUT NOCOPY VARCHAR2 ,
2117: x_errorcode OUT NOCOPY NUMBER,
2118: x_msg_count OUT NOCOPY NUMBER ,
2119: x_msg_data OUT NOCOPY VARCHAR2 ,
2176: -- Standard Start of API savepoint
2177: SAVEPOINT Delete_Category_PUB;
2178:
2179: -- Check for call compatibility.
2180: IF NOT FND_API.Compatible_API_Call (l_api_version,
2181: p_api_version ,
2182: l_api_name ,
2183: G_PKG_NAME)
2184: THEN
2181: p_api_version ,
2182: l_api_name ,
2183: G_PKG_NAME)
2184: THEN
2185: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2186: END IF;
2187: -- Initialize API message list if necessary.
2188: -- Initialize message list if p_init_msg_list is set to TRUE.
2189: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2185: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2186: END IF;
2187: -- Initialize API message list if necessary.
2188: -- Initialize message list if p_init_msg_list is set to TRUE.
2189: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2190: FND_MSG_PUB.initialize;
2191: END IF;
2192:
2193: OPEN category_assignment_exists(p_category_id);
2203: fnd_msg_pub.ADD;
2204: IF (l_debug = 1) THEN
2205: mdebug('Cannot delete: Category part of a Category Assignment');
2206: END IF;
2207: RAISE FND_API.G_EXC_ERROR;
2208: END IF;
2209:
2210: OPEN default_category_exists(p_category_id);
2211: FETCH default_category_exists INTO l_default_category_exists;
2220: fnd_msg_pub.ADD;
2221: IF (l_debug = 1) THEN
2222: mdebug('Cannot delete: Category specified is a default category to one of the Category Sets.');
2223: END IF;
2224: RAISE FND_API.G_EXC_ERROR;
2225: END IF;
2226:
2227: OPEN valid_category_exists(p_category_id);
2228: FETCH valid_category_exists INTO l_valid_category_exists;
2237: fnd_msg_pub.ADD;
2238: IF (l_debug = 1) THEN
2239: mdebug('Cannot delete: Category specified is part of a valid category set');
2240: END IF;
2241: RAISE FND_API.G_EXC_ERROR;
2242: END IF;
2243:
2244: delete from mtl_categories_tl
2245: where category_id = p_category_id ;
2268: IF (l_debug = 1) THEN
2269: mdebug('Category deleted successfully: '||p_category_id);
2270: END IF;
2271: -- Standard check of p_commit.
2272: IF FND_API.To_Boolean( p_commit ) THEN
2273: COMMIT WORK;
2274: END IF;
2275:
2276: x_return_status := FND_API.G_RET_STS_SUCCESS;
2272: IF FND_API.To_Boolean( p_commit ) THEN
2273: COMMIT WORK;
2274: END IF;
2275:
2276: x_return_status := FND_API.G_RET_STS_SUCCESS;
2277: -- Standard call to get message count and if count is 1,
2278: -- get message info.
2279: -- The client will directly display the x_msg_data (which is already
2280: -- translated) if the x_msg_count = 1;
2285: ( p_count => x_msg_count,
2286: p_data => x_msg_data
2287: );
2288: EXCEPTION
2289: WHEN FND_API.G_EXC_ERROR THEN
2290: ROLLBACK TO Delete_Category_PUB;
2291: x_return_status := FND_API.G_RET_STS_ERROR;
2292: FND_MSG_PUB.Count_And_Get
2293: ( p_count => x_msg_count,
2287: );
2288: EXCEPTION
2289: WHEN FND_API.G_EXC_ERROR THEN
2290: ROLLBACK TO Delete_Category_PUB;
2291: x_return_status := FND_API.G_RET_STS_ERROR;
2292: FND_MSG_PUB.Count_And_Get
2293: ( p_count => x_msg_count,
2294: p_data => x_msg_data
2295: );
2292: FND_MSG_PUB.Count_And_Get
2293: ( p_count => x_msg_count,
2294: p_data => x_msg_data
2295: );
2296: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2297: ROLLBACK TO Delete_Category_PUB;
2298: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2299: FND_MSG_PUB.Count_And_Get
2300: ( p_count => x_msg_count,
2294: p_data => x_msg_data
2295: );
2296: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2297: ROLLBACK TO Delete_Category_PUB;
2298: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2299: FND_MSG_PUB.Count_And_Get
2300: ( p_count => x_msg_count,
2301: p_data => x_msg_data
2302: );
2301: p_data => x_msg_data
2302: );
2303: WHEN OTHERS THEN
2304: ROLLBACK TO Delete_Category_PUB;
2305: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2306: IF FND_MSG_PUB.Check_Msg_Level
2307: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2308: THEN
2309: FND_MSG_PUB.Add_Exc_Msg
2327: ----------------------------------------------------------------------------
2328: PROCEDURE Create_Category_Assignment
2329: (
2330: p_api_version IN NUMBER,
2331: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2332: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2333: x_return_status OUT NOCOPY VARCHAR2,
2334: x_errorcode OUT NOCOPY NUMBER,
2335: x_msg_count OUT NOCOPY NUMBER,
2328: PROCEDURE Create_Category_Assignment
2329: (
2330: p_api_version IN NUMBER,
2331: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2332: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2333: x_return_status OUT NOCOPY VARCHAR2,
2334: x_errorcode OUT NOCOPY NUMBER,
2335: x_msg_count OUT NOCOPY NUMBER,
2336: x_msg_data OUT NOCOPY VARCHAR2,
2364: -- Standard Start of API savepoint
2365: SAVEPOINT Create_Category_Assignment_PUB;
2366:
2367: -- Check for call compatibility.
2368: IF NOT FND_API.Compatible_API_Call (l_api_version,
2369: p_api_version ,
2370: l_api_name ,
2371: G_PKG_NAME)
2372: THEN
2369: p_api_version ,
2370: l_api_name ,
2371: G_PKG_NAME)
2372: THEN
2373: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2374: END IF;
2375: -- Initialize API message list if necessary.
2376: -- Initialize message list if p_init_msg_list is set to TRUE.
2377: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2373: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2374: END IF;
2375: -- Initialize API message list if necessary.
2376: -- Initialize message list if p_init_msg_list is set to TRUE.
2377: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2378: FND_MSG_PUB.initialize;
2379: END IF;
2380: INV_ITEM_MSG.set_Message_Mode('PLSQL');
2381:
2401: );
2402:
2403: --mdebug('Create_Category_Assignment: Done!!');
2404: -- Standard check of p_commit.
2405: IF FND_API.To_Boolean( p_commit ) THEN
2406: COMMIT WORK;
2407: END IF;
2408:
2409: INV_ITEM_MSG.Write_List;
2412: p_data => x_msg_data
2413: );
2414:
2415: EXCEPTION
2416: WHEN FND_API.G_EXC_ERROR THEN
2417: ROLLBACK TO Create_Category_Assignment_PUB;
2418: x_return_status := FND_API.G_RET_STS_ERROR;
2419: FND_MSG_PUB.Count_And_Get
2420: ( p_count => x_msg_count,
2414:
2415: EXCEPTION
2416: WHEN FND_API.G_EXC_ERROR THEN
2417: ROLLBACK TO Create_Category_Assignment_PUB;
2418: x_return_status := FND_API.G_RET_STS_ERROR;
2419: FND_MSG_PUB.Count_And_Get
2420: ( p_count => x_msg_count,
2421: p_data => x_msg_data
2422: );
2419: FND_MSG_PUB.Count_And_Get
2420: ( p_count => x_msg_count,
2421: p_data => x_msg_data
2422: );
2423: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2424: ROLLBACK TO Create_Category_Assignment_PUB;
2425: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2426: FND_MSG_PUB.Count_And_Get
2427: ( p_count => x_msg_count,
2421: p_data => x_msg_data
2422: );
2423: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2424: ROLLBACK TO Create_Category_Assignment_PUB;
2425: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2426: FND_MSG_PUB.Count_And_Get
2427: ( p_count => x_msg_count,
2428: p_data => x_msg_data
2429: );
2428: p_data => x_msg_data
2429: );
2430: WHEN OTHERS THEN
2431: ROLLBACK TO Create_Category_Assignment_PUB;
2432: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2433: IF FND_MSG_PUB.Check_Msg_Level
2434: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2435: THEN
2436: FND_MSG_PUB.Add_Exc_Msg
2451: ----------------------------------------------------------------------------
2452: PROCEDURE Delete_Category_Assignment
2453: (
2454: p_api_version IN NUMBER,
2455: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2456: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2457: x_return_status OUT NOCOPY VARCHAR2,
2458: x_errorcode OUT NOCOPY NUMBER,
2459: x_msg_count OUT NOCOPY NUMBER,
2452: PROCEDURE Delete_Category_Assignment
2453: (
2454: p_api_version IN NUMBER,
2455: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2456: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2457: x_return_status OUT NOCOPY VARCHAR2,
2458: x_errorcode OUT NOCOPY NUMBER,
2459: x_msg_count OUT NOCOPY NUMBER,
2460: x_msg_data OUT NOCOPY VARCHAR2,
2487: -- Standard Start of API savepoint
2488: SAVEPOINT Delete_Category_Assignment_PUB;
2489:
2490: -- Check for call compatibility.
2491: IF NOT FND_API.Compatible_API_Call (l_api_version,
2492: p_api_version ,
2493: l_api_name ,
2494: G_PKG_NAME)
2495: THEN
2492: p_api_version ,
2493: l_api_name ,
2494: G_PKG_NAME)
2495: THEN
2496: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2497: END IF;
2498: -- Initialize API message list if necessary.
2499: -- Initialize message list if p_init_msg_list is set to TRUE.
2500: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2496: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2497: END IF;
2498: -- Initialize API message list if necessary.
2499: -- Initialize message list if p_init_msg_list is set to TRUE.
2500: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2501: FND_MSG_PUB.initialize;
2502: END IF;
2503: --Added code for bug 2527058
2504: INV_ITEM_MSG.set_Message_Mode('PLSQL');
2545: mdebug('Delete_Category_Assignment: Done!!');
2546: END IF;
2547:
2548: -- Standard check of p_commit.
2549: IF FND_API.To_Boolean( p_commit ) THEN
2550: COMMIT WORK;
2551: END IF;
2552:
2553: -- x_return_status := FND_API.G_RET_STS_SUCCESS;
2549: IF FND_API.To_Boolean( p_commit ) THEN
2550: COMMIT WORK;
2551: END IF;
2552:
2553: -- x_return_status := FND_API.G_RET_STS_SUCCESS;
2554: INV_ITEM_MSG.Write_List;
2555: -- Standard call to get message count and if count is 1,
2556: -- get message info.
2557: -- The client will directly display the x_msg_data (which is already
2563: ( p_count => x_msg_count,
2564: p_data => x_msg_data
2565: );
2566: EXCEPTION
2567: WHEN FND_API.G_EXC_ERROR THEN
2568: ROLLBACK TO Delete_Category_Assignment_PUB;
2569: x_return_status := FND_API.G_RET_STS_ERROR;
2570: FND_MSG_PUB.Count_And_Get
2571: ( p_count => x_msg_count,
2565: );
2566: EXCEPTION
2567: WHEN FND_API.G_EXC_ERROR THEN
2568: ROLLBACK TO Delete_Category_Assignment_PUB;
2569: x_return_status := FND_API.G_RET_STS_ERROR;
2570: FND_MSG_PUB.Count_And_Get
2571: ( p_count => x_msg_count,
2572: p_data => x_msg_data
2573: );
2570: FND_MSG_PUB.Count_And_Get
2571: ( p_count => x_msg_count,
2572: p_data => x_msg_data
2573: );
2574: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2575: ROLLBACK TO Delete_Category_Assignment_PUB;
2576: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2577: FND_MSG_PUB.Count_And_Get
2578: ( p_count => x_msg_count,
2572: p_data => x_msg_data
2573: );
2574: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2575: ROLLBACK TO Delete_Category_Assignment_PUB;
2576: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2577: FND_MSG_PUB.Count_And_Get
2578: ( p_count => x_msg_count,
2579: p_data => x_msg_data
2580: );
2579: p_data => x_msg_data
2580: );
2581: WHEN OTHERS THEN
2582: ROLLBACK TO Delete_Category_Assignment_PUB;
2583: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2584: IF FND_MSG_PUB.Check_Msg_Level
2585: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2586: THEN
2587: FND_MSG_PUB.Add_Exc_Msg
2632: IF x_category_rec.supplier_enabled_flag NOT IN (g_YES,g_MISS_CHAR) THEN -- g_NO is modifed to g_YES for bug#6278190
2633: fnd_message.set_name('INV','INV_NOT_VALID_FLAG');
2634: fnd_message.set_token('COLUMN_NAME', 'SUPPLIER_ENABLED_FLAG');
2635: fnd_msg_pub.ADD;
2636: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2637: IF (l_debug = 1) THEN
2638: mdebug('Invalid supplier enabled flag information provided');
2639: END IF;
2640: END IF;
2645: IF x_category_rec.web_status NOT IN (g_YES,g_MISS_CHAR) THEN
2646: fnd_message.set_name('INV','INV_NOT_VALID_FLAG');
2647: fnd_message.set_token('COLUMN_NAME', 'WEB_STATUS');
2648: fnd_msg_pub.ADD;
2649: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2650: IF (l_debug = 1) THEN
2651: mdebug('Invalid web status flag information provided');
2652: END IF;
2653: END IF;
2663: IF (l_debug = 1) THEN
2664: mdebug('No Default purchasing category set provided');
2665: END IF;
2666: CLOSE get_po_structure_id;
2667: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2668: ELSE
2669: IF (l_po_structure_id <> x_category_rec.structure_id) THEN
2670: IF (x_category_rec.supplier_enabled_flag = g_NO) THEN
2671: fnd_message.set_name('INV','INV_SUP_ENABLED_PO_CAT_ONLY');
2680: IF (l_debug = 1) THEN
2681: mdebug('Only purchasing cat can be viewable by supplier');
2682: END IF;
2683: CLOSE get_po_structure_id;
2684: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2685: END IF;
2686: END IF;
2687: CLOSE get_po_structure_id;
2688: END IF; --if flag = 'Y'
2694: -- API to create a valid Category in Category Sets
2695: ----------------------------------------------------------------------------
2696: PROCEDURE Create_Valid_Category(
2697: p_api_version IN NUMBER,
2698: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2699: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2700: p_category_set_id IN NUMBER,
2701: p_category_id IN NUMBER,
2702: p_parent_category_id IN NUMBER,
2695: ----------------------------------------------------------------------------
2696: PROCEDURE Create_Valid_Category(
2697: p_api_version IN NUMBER,
2698: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2699: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2700: p_category_set_id IN NUMBER,
2701: p_category_id IN NUMBER,
2702: p_parent_category_id IN NUMBER,
2703: x_return_status OUT NOCOPY VARCHAR2,
2733: IF l_debug = 1 THEN
2734: mdebug('Create_Valid_Category: Tracing...1');
2735: END IF;
2736: -- Standard Start of API savepoint
2737: IF FND_API.To_Boolean( p_commit ) THEN
2738: SAVEPOINT Create_Valid_Category_PUB;
2739: END IF;
2740: -- Check for call compatibility.
2741: IF NOT FND_API.Compatible_API_Call (l_api_version,
2737: IF FND_API.To_Boolean( p_commit ) THEN
2738: SAVEPOINT Create_Valid_Category_PUB;
2739: END IF;
2740: -- Check for call compatibility.
2741: IF NOT FND_API.Compatible_API_Call (l_api_version,
2742: p_api_version,
2743: l_api_name,
2744: G_PKG_NAME) THEN
2745: IF l_debug = 1 THEN
2744: G_PKG_NAME) THEN
2745: IF l_debug = 1 THEN
2746: mdebug('Create_Valid_Category: Invalid API Call');
2747: END IF;
2748: RAISE FND_API.g_EXC_ERROR;
2749: END IF;
2750: -- Initialize API message list if necessary.
2751: -- Initialize message list if p_init_msg_list is set to TRUE.
2752: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2748: RAISE FND_API.g_EXC_ERROR;
2749: END IF;
2750: -- Initialize API message list if necessary.
2751: -- Initialize message list if p_init_msg_list is set to TRUE.
2752: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2753: FND_MSG_PUB.initialize;
2754: END IF;
2755: IF validate_category_set_params
2756: (p_validation_type => G_INSERT
2799: , SYSDATE
2800: );
2801: ELSE
2802: -- passed parameters are invalid
2803: RAISE FND_API.G_EXC_ERROR;
2804: END IF;
2805:
2806: -- Standard check of p_commit.
2807: IF FND_API.To_Boolean( p_commit ) THEN
2803: RAISE FND_API.G_EXC_ERROR;
2804: END IF;
2805:
2806: -- Standard check of p_commit.
2807: IF FND_API.To_Boolean( p_commit ) THEN
2808: COMMIT WORK;
2809: END IF;
2810:
2811: x_return_status := FND_API.G_RET_STS_SUCCESS;
2807: IF FND_API.To_Boolean( p_commit ) THEN
2808: COMMIT WORK;
2809: END IF;
2810:
2811: x_return_status := FND_API.G_RET_STS_SUCCESS;
2812: -- Standard call to get message count and if count is 1,
2813: -- get message info.
2814: -- The client will directly display the x_msg_data (which is already
2815: -- translated) if the x_msg_count = 1;
2821: p_data => x_msg_data
2822: );
2823:
2824: EXCEPTION
2825: WHEN FND_API.G_EXC_ERROR THEN
2826: IF l_debug = 1 THEN
2827: mdebug('Create_Valid_Category: Apps Exception raised');
2828: END IF;
2829: IF FND_API.To_Boolean( p_commit ) THEN
2825: WHEN FND_API.G_EXC_ERROR THEN
2826: IF l_debug = 1 THEN
2827: mdebug('Create_Valid_Category: Apps Exception raised');
2828: END IF;
2829: IF FND_API.To_Boolean( p_commit ) THEN
2830: ROLLBACK TO Create_Valid_Category_PUB;
2831: END IF;
2832: x_return_status := FND_API.G_RET_STS_ERROR;
2833: FND_MSG_PUB.Count_And_Get
2828: END IF;
2829: IF FND_API.To_Boolean( p_commit ) THEN
2830: ROLLBACK TO Create_Valid_Category_PUB;
2831: END IF;
2832: x_return_status := FND_API.G_RET_STS_ERROR;
2833: FND_MSG_PUB.Count_And_Get
2834: ( p_count => x_msg_count,
2835: p_data => x_msg_data
2836: );
2833: FND_MSG_PUB.Count_And_Get
2834: ( p_count => x_msg_count,
2835: p_data => x_msg_data
2836: );
2837: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2838: IF l_debug = 1 THEN
2839: mdebug('Create_Valid_Category: Apps Unexpected Error');
2840: END IF;
2841: IF FND_API.To_Boolean( p_commit ) THEN
2837: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2838: IF l_debug = 1 THEN
2839: mdebug('Create_Valid_Category: Apps Unexpected Error');
2840: END IF;
2841: IF FND_API.To_Boolean( p_commit ) THEN
2842: ROLLBACK TO Create_Valid_Category_PUB;
2843: END IF;
2844: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2845: FND_MSG_PUB.Count_And_Get
2840: END IF;
2841: IF FND_API.To_Boolean( p_commit ) THEN
2842: ROLLBACK TO Create_Valid_Category_PUB;
2843: END IF;
2844: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2845: FND_MSG_PUB.Count_And_Get
2846: ( p_count => x_msg_count,
2847: p_data => x_msg_data
2848: );
2849: WHEN OTHERS THEN
2850: IF l_debug = 1 THEN
2851: mdebug('Create_Valid_Category: Exception -- OTHERS ');
2852: END IF;
2853: IF FND_API.To_Boolean( p_commit ) THEN
2854: ROLLBACK TO Create_Valid_Category_PUB;
2855: END IF;
2856: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2857: IF FND_MSG_PUB.Check_Msg_Level
2852: END IF;
2853: IF FND_API.To_Boolean( p_commit ) THEN
2854: ROLLBACK TO Create_Valid_Category_PUB;
2855: END IF;
2856: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2857: IF FND_MSG_PUB.Check_Msg_Level
2858: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2859: THEN
2860: FND_MSG_PUB.Add_Exc_Msg
2874: -- API to update a valid Category
2875: ----------------------------------------------------------------------------
2876: PROCEDURE Update_Valid_Category(
2877: p_api_version IN NUMBER,
2878: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2879: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2880: p_category_set_id IN NUMBER,
2881: p_category_id IN NUMBER,
2882: p_parent_category_id IN NUMBER,
2875: ----------------------------------------------------------------------------
2876: PROCEDURE Update_Valid_Category(
2877: p_api_version IN NUMBER,
2878: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2879: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2880: p_category_set_id IN NUMBER,
2881: p_category_id IN NUMBER,
2882: p_parent_category_id IN NUMBER,
2883: x_return_status OUT NOCOPY VARCHAR2,
2909: IF l_debug = 1 THEN
2910: mdebug('Update_Valid_Category: Tracing...1');
2911: END IF;
2912: -- Standard Start of API savepoint
2913: IF FND_API.To_Boolean( p_commit ) THEN
2914: SAVEPOINT Update_Valid_Category_PUB;
2915: END IF;
2916: -- Check for call compatibility.
2917: IF NOT FND_API.Compatible_API_Call (l_api_version,
2913: IF FND_API.To_Boolean( p_commit ) THEN
2914: SAVEPOINT Update_Valid_Category_PUB;
2915: END IF;
2916: -- Check for call compatibility.
2917: IF NOT FND_API.Compatible_API_Call (l_api_version,
2918: p_api_version,
2919: l_api_name,
2920: G_PKG_NAME) THEN
2921: IF l_debug = 1 THEN
2920: G_PKG_NAME) THEN
2921: IF l_debug = 1 THEN
2922: mdebug('Update_Valid_Category: Invalid API call');
2923: END IF;
2924: RAISE FND_API.g_EXC_ERROR;
2925: END IF;
2926: -- Initialize API message list if necessary.
2927: -- Initialize message list if p_init_msg_list is set to TRUE.
2928: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2924: RAISE FND_API.g_EXC_ERROR;
2925: END IF;
2926: -- Initialize API message list if necessary.
2927: -- Initialize message list if p_init_msg_list is set to TRUE.
2928: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2929: FND_MSG_PUB.initialize;
2930: END IF;
2931:
2932: IF validate_category_set_params
2956: mdebug('Update_Valid_Category: Record not available for update');
2957: END IF;
2958: fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_UPDATE');
2959: fnd_msg_pub.ADD;
2960: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2961: END IF;
2962: ELSE
2963: -- passed parameters are invalid
2964: RAISE FND_API.G_EXC_ERROR;
2960: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2961: END IF;
2962: ELSE
2963: -- passed parameters are invalid
2964: RAISE FND_API.G_EXC_ERROR;
2965: END IF;
2966: -- Standard check of p_commit.
2967: IF FND_API.To_Boolean( p_commit ) THEN
2968: COMMIT WORK;
2963: -- passed parameters are invalid
2964: RAISE FND_API.G_EXC_ERROR;
2965: END IF;
2966: -- Standard check of p_commit.
2967: IF FND_API.To_Boolean( p_commit ) THEN
2968: COMMIT WORK;
2969: END IF;
2970:
2971: x_return_status := FND_API.G_RET_STS_SUCCESS;
2967: IF FND_API.To_Boolean( p_commit ) THEN
2968: COMMIT WORK;
2969: END IF;
2970:
2971: x_return_status := FND_API.G_RET_STS_SUCCESS;
2972: -- Standard call to get message count and if count is 1,
2973: -- get message info.
2974: -- The client will directly display the x_msg_data (which is already
2975: -- translated) if the x_msg_count = 1;
2981: p_data => x_msg_data
2982: );
2983:
2984: EXCEPTION
2985: WHEN FND_API.G_EXC_ERROR THEN
2986: IF l_debug = 1 THEN
2987: mdebug('Update_Valid_Category: Apps Exception raised');
2988: END IF;
2989: IF FND_API.To_Boolean( p_commit ) THEN
2985: WHEN FND_API.G_EXC_ERROR THEN
2986: IF l_debug = 1 THEN
2987: mdebug('Update_Valid_Category: Apps Exception raised');
2988: END IF;
2989: IF FND_API.To_Boolean( p_commit ) THEN
2990: ROLLBACK TO Update_Valid_Category_PUB;
2991: END IF;
2992: x_return_status := FND_API.G_RET_STS_ERROR;
2993: FND_MSG_PUB.Count_And_Get
2988: END IF;
2989: IF FND_API.To_Boolean( p_commit ) THEN
2990: ROLLBACK TO Update_Valid_Category_PUB;
2991: END IF;
2992: x_return_status := FND_API.G_RET_STS_ERROR;
2993: FND_MSG_PUB.Count_And_Get
2994: ( p_count => x_msg_count,
2995: p_data => x_msg_data
2996: );
2993: FND_MSG_PUB.Count_And_Get
2994: ( p_count => x_msg_count,
2995: p_data => x_msg_data
2996: );
2997: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2998: IF l_debug = 1 THEN
2999: mdebug('Update_Valid_Category: Apps Unexpected Error');
3000: END IF;
3001: IF FND_API.To_Boolean( p_commit ) THEN
2997: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2998: IF l_debug = 1 THEN
2999: mdebug('Update_Valid_Category: Apps Unexpected Error');
3000: END IF;
3001: IF FND_API.To_Boolean( p_commit ) THEN
3002: ROLLBACK TO Update_Valid_Category_PUB;
3003: END IF;
3004: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3005: FND_MSG_PUB.Count_And_Get
3000: END IF;
3001: IF FND_API.To_Boolean( p_commit ) THEN
3002: ROLLBACK TO Update_Valid_Category_PUB;
3003: END IF;
3004: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3005: FND_MSG_PUB.Count_And_Get
3006: ( p_count => x_msg_count,
3007: p_data => x_msg_data
3008: );
3009: WHEN OTHERS THEN
3010: IF l_debug = 1 THEN
3011: mdebug('Update_Valid_Category: Exception -- OTHERS ');
3012: END IF;
3013: IF FND_API.To_Boolean( p_commit ) THEN
3014: ROLLBACK TO Update_Valid_Category_PUB;
3015: END IF;
3016: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3017: IF FND_MSG_PUB.Check_Msg_Level
3012: END IF;
3013: IF FND_API.To_Boolean( p_commit ) THEN
3014: ROLLBACK TO Update_Valid_Category_PUB;
3015: END IF;
3016: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3017: IF FND_MSG_PUB.Check_Msg_Level
3018: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3019: THEN
3020: FND_MSG_PUB.Add_Exc_Msg
3034: -- API to Delete a valid Category
3035: ----------------------------------------------------------------------------
3036: PROCEDURE Delete_Valid_Category(
3037: p_api_version IN NUMBER,
3038: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3039: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3040: p_category_set_id IN NUMBER,
3041: p_category_id IN NUMBER,
3042: x_return_status OUT NOCOPY VARCHAR2,
3035: ----------------------------------------------------------------------------
3036: PROCEDURE Delete_Valid_Category(
3037: p_api_version IN NUMBER,
3038: p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3039: p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3040: p_category_set_id IN NUMBER,
3041: p_category_id IN NUMBER,
3042: x_return_status OUT NOCOPY VARCHAR2,
3043: x_errorcode OUT NOCOPY NUMBER,
3132: IF l_debug = 1 THEN
3133: mdebug('Delete_Valid_Category: Tracing...1');
3134: END IF;
3135: -- Standard Start of API savepoint
3136: IF FND_API.To_Boolean( p_commit ) THEN
3137: SAVEPOINT Delete_Valid_Category_PUB;
3138: END IF;
3139: -- Check for call compatibility.
3140: IF NOT FND_API.Compatible_API_Call (l_api_version,
3136: IF FND_API.To_Boolean( p_commit ) THEN
3137: SAVEPOINT Delete_Valid_Category_PUB;
3138: END IF;
3139: -- Check for call compatibility.
3140: IF NOT FND_API.Compatible_API_Call (l_api_version,
3141: p_api_version,
3142: l_api_name,
3143: G_PKG_NAME) THEN
3144: IF l_debug = 1 THEN
3143: G_PKG_NAME) THEN
3144: IF l_debug = 1 THEN
3145: mdebug('Delete_Valid_Category: Invalid API call');
3146: END IF;
3147: RAISE FND_API.G_EXC_ERROR;
3148: END IF;
3149: -- Initialize API message list if necessary.
3150: -- Initialize message list if p_init_msg_list is set to TRUE.
3151: IF FND_API.to_Boolean( p_init_msg_list ) THEN
3147: RAISE FND_API.G_EXC_ERROR;
3148: END IF;
3149: -- Initialize API message list if necessary.
3150: -- Initialize message list if p_init_msg_list is set to TRUE.
3151: IF FND_API.to_Boolean( p_init_msg_list ) THEN
3152: FND_MSG_PUB.initialize;
3153: END IF;
3154:
3155: IF (p_category_set_id IS NULL OR p_category_id IS NULL) THEN
3157: mdebug('Delete_Valid_Category: Mandatory parameters missing');
3158: END IF;
3159: fnd_message.set_name('INV','INV_MISSING_REQUIRED_PARAMETER');
3160: fnd_msg_pub.ADD;
3161: RAISE fnd_api.G_EXC_ERROR;
3162: END IF;
3163:
3164: IF NOT get_category_set_type(p_category_set_id => p_category_set_id
3165: ,p_category_id => p_category_id
3168: mdebug('Delete_Valid_Category: Record not available for deletion');
3169: END IF;
3170: fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_DELETE');
3171: fnd_msg_pub.ADD;
3172: RAISE fnd_api.g_EXC_ERROR;
3173: END IF;
3174:
3175: -- check if the user tries to delete default cateogy of the category set
3176: IF UPPER(l_hrchy_enabled) = 'Y' THEN
3203: END IF;
3204: fnd_message.set_name('INV','INV_DELETE_DEF_CAT_ERR');
3205: fnd_message.set_token('CATEGORY_NAME', l_description);
3206: fnd_msg_pub.ADD;
3207: RAISE fnd_api.G_EXC_ERROR;
3208: END IF;
3209:
3210: -- check if there are any items associated to the category / category set
3211: IF UPPER(l_hrchy_enabled) = 'Y' THEN
3260: END IF;
3261: fnd_message.set_name('INV','INV_CATEGORY_ITEMS_EXIST');
3262: fnd_message.set_token('CATEGORY_NAME', l_description);
3263: fnd_msg_pub.ADD;
3264: RAISE FND_API.G_EXC_ERROR;
3265: END IF;
3266:
3267: -- Standard check of p_commit.
3268: IF FND_API.To_Boolean( p_commit ) THEN
3264: RAISE FND_API.G_EXC_ERROR;
3265: END IF;
3266:
3267: -- Standard check of p_commit.
3268: IF FND_API.To_Boolean( p_commit ) THEN
3269: COMMIT WORK;
3270: END IF;
3271:
3272: x_return_status := FND_API.G_RET_STS_SUCCESS;
3268: IF FND_API.To_Boolean( p_commit ) THEN
3269: COMMIT WORK;
3270: END IF;
3271:
3272: x_return_status := FND_API.G_RET_STS_SUCCESS;
3273: -- Standard call to get message count and if count is 1,
3274: -- get message info.
3275: -- The client will directly display the x_msg_data (which is already
3276: -- translated) if the x_msg_count = 1;
3282: p_data => x_msg_data
3283: );
3284:
3285: EXCEPTION
3286: WHEN FND_API.G_EXC_ERROR THEN
3287: IF l_debug = 1 THEN
3288: mdebug('Delete_Valid_Category: Apps Exception raised');
3289: END IF;
3290: IF FND_API.To_Boolean( p_commit ) THEN
3286: WHEN FND_API.G_EXC_ERROR THEN
3287: IF l_debug = 1 THEN
3288: mdebug('Delete_Valid_Category: Apps Exception raised');
3289: END IF;
3290: IF FND_API.To_Boolean( p_commit ) THEN
3291: ROLLBACK TO Delete_Valid_Category_PUB;
3292: END IF;
3293: x_return_status := FND_API.G_RET_STS_ERROR;
3294: FND_MSG_PUB.Count_And_Get
3289: END IF;
3290: IF FND_API.To_Boolean( p_commit ) THEN
3291: ROLLBACK TO Delete_Valid_Category_PUB;
3292: END IF;
3293: x_return_status := FND_API.G_RET_STS_ERROR;
3294: FND_MSG_PUB.Count_And_Get
3295: ( p_count => x_msg_count,
3296: p_data => x_msg_data
3297: );
3294: FND_MSG_PUB.Count_And_Get
3295: ( p_count => x_msg_count,
3296: p_data => x_msg_data
3297: );
3298: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3299: IF l_debug = 1 THEN
3300: mdebug('Delete_Valid_Category: Apps Unexpected Error');
3301: END IF;
3302: IF FND_API.To_Boolean( p_commit ) THEN
3298: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3299: IF l_debug = 1 THEN
3300: mdebug('Delete_Valid_Category: Apps Unexpected Error');
3301: END IF;
3302: IF FND_API.To_Boolean( p_commit ) THEN
3303: ROLLBACK TO Delete_Valid_Category_PUB;
3304: END IF;
3305: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3306: FND_MSG_PUB.Count_And_Get
3301: END IF;
3302: IF FND_API.To_Boolean( p_commit ) THEN
3303: ROLLBACK TO Delete_Valid_Category_PUB;
3304: END IF;
3305: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3306: FND_MSG_PUB.Count_And_Get
3307: ( p_count => x_msg_count,
3308: p_data => x_msg_data
3309: );
3310: WHEN OTHERS THEN
3311: IF l_debug = 1 THEN
3312: mdebug('Delete_Valid_Category: Exception -- OTHERS ');
3313: END IF;
3314: IF FND_API.To_Boolean( p_commit ) THEN
3315: ROLLBACK TO Delete_Valid_Category_PUB;
3316: END IF;
3317: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3318: IF c_get_items_in_cat%ISOPEN THEN
3313: END IF;
3314: IF FND_API.To_Boolean( p_commit ) THEN
3315: ROLLBACK TO Delete_Valid_Category_PUB;
3316: END IF;
3317: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3318: IF c_get_items_in_cat%ISOPEN THEN
3319: CLOSE c_get_items_in_cat;
3320: END IF;
3321: IF FND_MSG_PUB.Check_Msg_Level
3372: ,p_class_code_name_value_pairs => NULL
3373: ,p_data_level_name_value_pairs => l_data_column_name_value_pairs
3374: ,p_attr_name_value_pairs => null
3375: ,p_mode => p_mode
3376: ,p_use_def_vals_on_insert => FND_API.G_TRUE
3377: ,x_return_status => x_return_status
3378: ,x_errorcode => x_errorcode
3379: ,x_msg_count => x_msg_count
3380: ,x_msg_data => x_msg_data );
3380: ,x_msg_data => x_msg_data );
3381: EXCEPTION
3382:
3383: WHEN OTHERS THEN
3384: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3385: x_msg_data := 'Executing - '||G_PKG_NAME||'.Process_Dml_On_Row '||SQLERRM;
3386:
3387: END Process_Dml_On_Row;
3388:
3433: -- Standard Start of API savepoint
3434: SAVEPOINT Update_Category_Assignment_PUB;
3435:
3436: -- Check for call compatibility.
3437: IF NOT FND_API.Compatible_API_Call (l_api_version,
3438: p_api_version ,
3439: l_api_name ,
3440: G_PKG_NAME)
3441: THEN
3438: p_api_version ,
3439: l_api_name ,
3440: G_PKG_NAME)
3441: THEN
3442: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3443: END IF;
3444: -- Initialize API message list if necessary.
3445: -- Initialize message list if p_init_msg_list is set to TRUE.
3446: IF FND_API.to_Boolean( p_init_msg_list ) THEN
3442: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3443: END IF;
3444: -- Initialize API message list if necessary.
3445: -- Initialize message list if p_init_msg_list is set to TRUE.
3446: IF FND_API.to_Boolean( p_init_msg_list ) THEN
3447: FND_MSG_PUB.initialize;
3448: END IF;
3449:
3450: INV_ITEM_MSG.set_Message_Mode('PLSQL');
3475: mdebug('Update_Category_Assignment: Done!!');
3476: END IF;
3477:
3478: -- Standard check of p_commit.
3479: IF FND_API.To_Boolean( p_commit ) THEN
3480: COMMIT WORK;
3481: END IF;
3482:
3483: --- Bug 6272365 Start
3480: COMMIT WORK;
3481: END IF;
3482:
3483: --- Bug 6272365 Start
3484: --- x_return_status := FND_API.G_RET_STS_SUCCESS;
3485: INV_ITEM_MSG.Write_List;
3486: --- Bug 6272365 End
3487: -- Standard call to get message count and if count is 1,
3488: -- get message info.
3495: ( p_count => x_msg_count,
3496: p_data => x_msg_data
3497: );
3498: EXCEPTION
3499: WHEN FND_API.G_EXC_ERROR THEN
3500: ROLLBACK TO Update_Category_Assignment_PUB;
3501: x_return_status := FND_API.G_RET_STS_ERROR;
3502: FND_MSG_PUB.Count_And_Get
3503: ( p_count => x_msg_count,
3497: );
3498: EXCEPTION
3499: WHEN FND_API.G_EXC_ERROR THEN
3500: ROLLBACK TO Update_Category_Assignment_PUB;
3501: x_return_status := FND_API.G_RET_STS_ERROR;
3502: FND_MSG_PUB.Count_And_Get
3503: ( p_count => x_msg_count,
3504: p_data => x_msg_data
3505: );
3502: FND_MSG_PUB.Count_And_Get
3503: ( p_count => x_msg_count,
3504: p_data => x_msg_data
3505: );
3506: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3507: ROLLBACK TO Update_Category_Assignment_PUB;
3508: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3509: FND_MSG_PUB.Count_And_Get
3510: ( p_count => x_msg_count,
3504: p_data => x_msg_data
3505: );
3506: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3507: ROLLBACK TO Update_Category_Assignment_PUB;
3508: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3509: FND_MSG_PUB.Count_And_Get
3510: ( p_count => x_msg_count,
3511: p_data => x_msg_data
3512: );
3511: p_data => x_msg_data
3512: );
3513: WHEN OTHERS THEN
3514: ROLLBACK TO Update_Category_Assignment_PUB;
3515: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3516: IF FND_MSG_PUB.Check_Msg_Level
3517: (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3518: THEN
3519: FND_MSG_PUB.Add_Exc_Msg