The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_INSERT CONSTANT VARCHAR2(3) := 'INS';
c_UPDATE CONSTANT VARCHAR2(3) := 'UPD';
c_DELETE CONSTANT VARCHAR2(3) := 'DEL';
SELECT 'x',request_id, approval_status --2879647
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT structure_id, validate_flag, mult_item_cat_assign_flag,
control_level
,hierarchy_enabled--Bug: 2996160
FROM mtl_category_sets_b
WHERE category_set_id = p_category_set_id;
SELECT structure_id
FROM mtl_categories_b
WHERE category_id = p_category_id
AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE;--Bug: 2996160
SELECT 'x'
FROM mtl_category_set_valid_cats
WHERE category_set_id = p_category_set_id
AND category_id = p_category_id;
SELECT COUNT( category_id ), COUNT( DECODE(category_id, p_category_id,1, NULL) )
FROM mtl_item_categories
WHERE
inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND category_set_id = p_category_set_id;
SELECT COUNT(*) INTO l_default_cats
FROM MTL_DEFAULT_CATEGORY_SETS
WHERE CATEGORY_SET_ID = p_category_set_id;
SELECT MASTER_ORGANIZATION_ID
INTO p_master_org_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
, p_transaction_id => p_transaction_id
);
DELETE FROM mtl_item_categories
WHERE inventory_item_id = p_inventory_item_id
AND category_set_id = p_category_set_id;
DELETE FROM mtl_item_categories
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND category_set_id = p_category_set_id;
INV_ITEM_MSG.Debug(Mctx, 'begin INSERT INTO mtl_item_categories');
INSERT INTO mtl_item_categories
(
inventory_item_id
, organization_id
, category_set_id
, category_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id --4105867
)
SELECT
p_inventory_item_id
, p.organization_id
, p_category_set_id
, p_category_id
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, FND_GLOBAL.conc_request_id
FROM mtl_parameters p , mtl_system_items_b i
WHERE p.master_organization_id = p_master_org_id
AND i.inventory_item_id = p_inventory_item_id
AND i.organization_id = p.organization_id
AND not exists
(SELECT 'x'
FROM mtl_item_categories
whERE inventory_item_id = p_inventory_item_id
AND organization_id = p.organization_id
AND category_set_id = p_category_set_id
AND category_id = p_category_id);
INSERT INTO mtl_item_categories
(
inventory_item_id
, organization_id
, category_set_id
, category_id
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id --4105867
)
VALUES
(
p_inventory_item_id
, p_organization_id
, p_category_set_id
, p_category_id
, SYSDATE
, FND_GLOBAL.user_id
, SYSDATE
, FND_GLOBAL.user_id
, FND_GLOBAL.login_id
, FND_GLOBAL.conc_request_id
);
INV_ITEM_MSG.Debug(Mctx, 'end INSERT INTO mtl_item_categories');
PROCEDURE Delete_Category_Assignment
(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_FALSE
, p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_category_set_id IN NUMBER
, p_category_id IN NUMBER
, p_transaction_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category_Assignment';
SELECT control_level,default_category_id--Bug:2527058
FROM mtl_category_sets_b
WHERE category_set_id = p_category_set_id;
SELECT structure_id
FROM mtl_categories_b
WHERE category_id = p_category_id;
SELECT category_set_id
FROM mtl_default_category_sets
WHERE functional_area_id = cp_functional_area;
SELECT gdsn_outbound_enabled_flag
FROM mtl_system_items_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
SAVEPOINT Delete_Category_Assignment_PVT;
SELECT count(category_id)
INTO cnt_cat
FROM mtl_item_categories
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id
AND CATEGORY_SET_ID = p_category_set_id;
SELECT MASTER_ORGANIZATION_ID
INTO p_master_org_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
, p_transaction_id => p_transaction_id
);
DELETE /*+ INDEX(MIC MTL_ITEM_CATEGORIES_U1) */
FROM mtl_item_categories MIC
WHERE category_set_id = p_category_set_id
AND category_id = p_category_id
AND inventory_item_id = p_inventory_item_id
AND organization_id =
(SELECT organization_id
FROM mtl_parameters p
WHERE p.master_organization_id = p_master_org_id
AND p.organization_id = mic.organization_id);
DELETE FROM mtl_item_categories
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND category_set_id = p_category_set_id
AND category_id = p_category_id;
INV_ITEM_MSG.Debug(Mctx, 'after DELETE FROM mtl_item_categories');
ROLLBACK TO Delete_Category_Assignment_PVT;
ROLLBACK TO Delete_Category_Assignment_PVT;
ROLLBACK TO Delete_Category_Assignment_PVT;
END Delete_Category_Assignment;
SELECT 'x'
FROM mtl_Category_set_valid_cats VC
WHERE VC.category_set_id = p_category_set_id
AND VC.category_id = p_category_id
AND NOT EXISTS
(SELECT NULL FROM mtl_Category_set_valid_cats
WHERE parent_category_id = VC.category_id
AND category_set_id = p_category_set_id);
SELECT 'x'
FROM mtl_Category_set_valid_cats
WHERE category_set_id = p_category_set_id
AND parent_category_id = p_category_id ;
PROCEDURE Update_Valid_Category(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
p_category_set_id IN NUMBER,
p_category_id IN NUMBER,
p_parent_category_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
-- Start OF comments
-- API name : Update_Valid_Category
-- TYPE : Private and USed by ENI Upgrade program alone
-- Pre-reqs : 11.5.10 level
-- FUNCTION : Create a category.
-- This sets the PUB API package level variable
-- and calls the corresponding PUB API procedure.
-- This will not do validations for ENABLED_FLAG and DISABLE_DATE
-- END OF comments
BEGIN
INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'Y';
INV_ITEM_CATEGORY_PUB.Update_Valid_Category
(
p_api_version => p_api_version ,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit ,
p_category_set_id => p_category_set_id ,
p_category_id => p_category_id ,
p_parent_category_id => p_parent_category_id,
x_return_status => x_return_status,
x_errorcode => x_errorcode ,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data
);
END Update_Valid_Category;
PROCEDURE Update_Category_Assignment
(
p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2
, p_commit IN VARCHAR2
, p_inventory_item_id IN NUMBER
, p_organization_id IN NUMBER
, p_category_set_id IN NUMBER
, p_category_id IN NUMBER
, p_old_category_id IN NUMBER
, p_transaction_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Assignment';
SELECT structure_id,
validate_flag,
mult_item_cat_assign_flag,
control_level,
hierarchy_enabled
FROM mtl_category_sets_b
WHERE category_set_id = p_category_set_id;
SELECT 'x'
FROM mtl_category_set_valid_cats
WHERE category_set_id = p_category_set_id
AND category_id = p_category_id;
SELECT structure_id
FROM mtl_categories_b
WHERE category_id = p_category_id
AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE; /*Bug no: 5946409 Checking whether the category is disabled */
SAVEPOINT Update_Category_Assignment_PVT;
SELECT Count(1)
INTO l_reccount
FROM mtl_item_categories
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND category_set_id = p_category_set_id
AND category_id = p_old_category_id;
INV_ITEM_MSG.Debug(Mctx, 'Select Master Org from Mtl_Parameters');
SELECT MASTER_ORGANIZATION_ID
INTO p_master_org_id
FROM mtl_parameters
WHERE organization_id = p_organization_id;
( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
, p_transaction_id => p_transaction_id
);
SELECT Count(1)
INTO l_reccount
FROM mtl_item_categories
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND category_set_id = p_category_set_id
AND category_id = p_category_id;
UPDATE /*+ INDEX(MIC MTL_ITEM_CATEGORIES_U1) */
Mtl_Item_Categories MIC
SET Category_Id = p_category_id
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
,request_id = FND_GLOBAL.conc_request_id -- 4105867
WHERE category_set_id = p_category_set_id
AND category_id = p_old_category_id
AND inventory_item_id = p_inventory_item_id
AND organization_id =(SELECT organization_id
FROM mtl_parameters p
WHERE p.master_organization_id = p_master_org_id
AND p.organization_id = mic.organization_id);
--Category assignment is not getting updated in eni_oltp_item_star table
--when user update category assignment through
--INV_ITEM_CATEGORY_PUB.Update_Category_Assignment
INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_inventory_item_id => p_inventory_item_id
,p_organization_id => p_organization_id
,p_category_set_id => p_category_set_id
,p_old_category_id => p_old_category_id
,p_new_category_id => p_category_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
UPDATE Mtl_Item_Categories
SET Category_Id = p_category_id
,last_update_date = SYSDATE
,last_updated_by = FND_GLOBAL.user_id
,last_update_login = FND_GLOBAL.login_id
,request_id = FND_GLOBAL.conc_request_id --4105867
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND category_set_id = p_category_set_id
AND category_id = p_old_category_id;
--Category assignment is not getting updated in eni_oltp_item_star table
--when user update category assignment through
--INV_ITEM_CATEGORY_PUB.Update_Category_Assignment
INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,p_inventory_item_id => p_inventory_item_id
,p_organization_id => p_organization_id
,p_category_set_id => p_category_set_id
,p_old_category_id => p_old_category_id
,p_new_category_id => p_category_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
INV_ITEM_MSG.Debug(Mctx, 'after update FROM mtl_item_categories');
ROLLBACK TO Update_Category_Assignment_PVT;
ROLLBACK TO Update_Category_Assignment_PVT;
ROLLBACK TO Update_Category_Assignment_PVT;
ROLLBACK TO Update_Category_Assignment_PVT;
END Update_Category_Assignment;