[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_INSERT CONSTANT NUMBER := 1;
G_UPDATE CONSTANT NUMBER := 2;
SELECT hierarchy_enabled INTO l_hierarchy_enabled
FROM mtl_category_sets_b
WHERE category_set_id = p_category_set_id;
SELECT cat.category_id
FROM mtl_categories_b cat, mtl_category_sets_b cat_set
WHERE cat_set.category_set_id = cp_cat_set_id
AND cat_set.structure_id = cat.structure_id
AND cat.category_id = cp_category_id
AND ((cat.enabled_flag = 'Y'
-- do not display today's records
AND TRUNC(NVL(cat.disable_date,SYSDATE+1)) > TRUNC(SYSDATE)
)
OR NVL(g_eni_upgarde_flag,'N') = 'Y' --Added for ENI 11.5.10 Upgrade
)
;
SELECT category_id
INTO l_category_id
FROM mtl_category_set_valid_cats
WHERE category_id = p_category_id
AND category_set_id = p_category_set_id;
SELECT csv.category_id, cs.hierarchy_enabled
INTO l_category_id, x_hrchy_enabled
FROM mtl_category_set_valid_cats csv
,mtl_category_sets_b cs
WHERE csv.category_id = p_category_id
AND csv.category_set_id = p_category_set_id
AND cs.category_set_id = csv.category_set_id;
SELECT category_id
FROM mtl_category_set_valid_cats
WHERE category_set_id = cp_category_set_id
CONNECT BY prior category_id = parent_category_id
START WITH parent_category_id = cp_parent_category_id;
SELECT default_category_id
FROM mtl_category_sets_b cat_sets
WHERE cat_sets.category_set_id = cp_category_set_id
AND cat_sets.default_category_id = cp_category_id;
SELECT category_id
FROM mtl_item_categories
WHERE category_id = cp_category_id
AND category_set_id = cp_category_set_id
AND rownum = 1;
IF p_validation_type = G_INSERT THEN
IF l_debug = 1 THEN
mdebug('Validate Params: check for Insert ');
mdebug('Validate Params: Category Id is valid for insert');
ELSIF p_validation_type = G_UPDATE THEN
IF l_debug = 1 THEN
mdebug('Validate Params: check for Update ');
mdebug('Validate Params: Record not available for update');
fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_UPDATE');
SELECT id_flex_num
FROM fnd_id_flex_structures
WHERE application_id = G_INVENTORY_APP_ID
AND id_flex_code = G_CAT_FLEX_CODE
AND id_flex_structure_code = p_structure_code
AND enabled_flag = 'Y';
SELECT structure_id
FROM mtl_categories_b
WHERE category_id = p_category_id;
SELECT 'x'
FROM fnd_id_flex_structures
WHERE application_id = G_INVENTORY_APP_ID
AND id_flex_code = G_CAT_FLEX_CODE
AND id_flex_num = p_structure_id
AND enabled_flag = 'Y';
SELECT
--category_id,
--structure_id,
description,
attribute_category,
summary_flag,
enabled_flag,
start_date_active,
end_date_active,
disable_date,
web_status,--Bug: 2430879
supplier_enabled_flag,--Bug: 2645153
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
--last_update_date,
--last_updated_by,
--creation_date,
--created_by,
--last_update_login
FROM mtl_categories_vl
WHERE category_id = p_category_id;
IF (p_operation = G_INSERT) THEN
/* The following code is not needed.
IF (x_category_rec.category_id = g_MISS_NUM OR
x_category_rec.category_id IS NOT NULL) THEN
x_category_rec.category_id := NULL;
mdebug('Ignoring the Category Id value for Insert');
END IF; --IF (p_operation = G_INSERT) THEN
IF (p_operation = G_UPDATE) THEN
IF (x_category_rec.category_id = g_MISS_NUM OR
x_category_rec.category_id IS NULL) THEN
fnd_message.set_name('INV','INV_NO_CATEGORY');
mdebug('Category Id needed for Update');
END IF; --IF (p_operation = G_UPDATE) THEN
( NOT l_success AND p_operation = G_UPDATE AND -- added for 13850442
(INSTR(FND_FLEX_KEYVAL.error_message,'has been disabled.')> 0 OR
INSTR(FND_FLEX_KEYVAL.error_message,'has expired.')> 0 OR
INSTR(FND_FLEX_KEYVAL.error_message,'This combination is disabled')>0))) THEN
NULL;
SELECT count(segment_num)
FROM fnd_id_flex_segments
WHERE application_id = G_INVENTORY_APP_ID
AND id_flex_code = G_CAT_FLEX_CODE
AND id_flex_num = p_structure_id
AND (enabled_flag = 'Y' OR NVL(g_eni_upgarde_flag,'N') = 'Y');-- Added for 11.5.10 ENI Upgrade
SELECT application_column_name,rownum
FROM fnd_id_flex_segments
WHERE application_id = 401
AND id_flex_code = 'MCAT'
AND id_flex_num = cp_flex_num
AND enabled_flag = 'Y'
ORDER BY segment_num ASC;
AND (p_operation = G_UPDATE))) THEN
IF (p_operation = G_INSERT) THEN
fnd_message.set_name('INV','INV_NEW_ENT');
ELSIF (p_operation = G_UPDATE) THEN
IF (FND_FLEX_KEYVAL.combination_id <>
p_category_rec.category_id) THEN
fnd_message.set_name('INV','INV_NEW_ENT');
ELSE -- neither insert nor update
NULL;
IF (p_operation = G_INSERT) THEN
ValueSet_Validate(l_structure_id, l_concat_segs, p_operation); -- added p_operation for 13850442
Calling procedure will take care of inserting record.
Since the COMBINATION_ID is Category_Id, just verifying if the
comb. exists through fnd_flex_keyval.validate_segs(FIND_COMB..)
call and inserting directly in database through Table Handler
would be enough. The folllowing could be used as alternative.
l_success := fnd_flex_keyval.validate_segs(
operation => 'CREATE_COMBINATION',
appl_short_name => G_INVENTORY_APP_SHORT_NAME,
key_flex_code => G_CAT_FLEX_CODE,
structure_number => l_structure_id,
concat_segments => l_concat_segs
);
ELSIF (p_operation = G_UPDATE) THEN
fnd_message.set_name('INV','INV_VALID_CAT');
mdebug('Trying to update a non-existant ROW');
ELSE -- neither insert nor update
NULL;
SELECT mtl_categories_s.nextval
FROM dual;
Preprocess_Category_Rec(G_INSERT, p_category_rec, l_category_rec) ;
Flex_Validate(G_INSERT, l_category_rec);
MTL_CATEGORIES_PKG.Insert_Row(
X_ROWID => l_row_id, -- OUT variable
X_CATEGORY_ID => l_category_id, -- gen from seq.
X_DESCRIPTION => l_category_rec.description,
X_STRUCTURE_ID => l_category_rec.structure_id,
X_DISABLE_DATE => l_category_rec.disable_date,
X_WEB_STATUS => l_category_rec.web_status,--Bug: 2430879
X_SUPPLIER_ENABLED_FLAG => l_category_rec.supplier_enabled_flag,--Bug: 2645153
X_SEGMENT1 => l_category_rec.segment1 ,
X_SEGMENT2 => l_category_rec.segment2 ,
X_SEGMENT3 => l_category_rec.segment3 ,
X_SEGMENT4 => l_category_rec.segment4 ,
X_SEGMENT5 => l_category_rec.segment5 ,
X_SEGMENT6 => l_category_rec.segment6 ,
X_SEGMENT7 => l_category_rec.segment7 ,
X_SEGMENT8 => l_category_rec.segment8 ,
X_SEGMENT9 => l_category_rec.segment9 ,
X_SEGMENT10 => l_category_rec.segment10 ,
X_SEGMENT11 => l_category_rec.segment11 ,
X_SEGMENT12 => l_category_rec.segment12 ,
X_SEGMENT13 => l_category_rec.segment13 ,
X_SEGMENT14 => l_category_rec.segment14 ,
X_SEGMENT15 => l_category_rec.segment15 ,
X_SEGMENT16 => l_category_rec.segment16 ,
X_SEGMENT17 => l_category_rec.segment17 ,
X_SEGMENT18 => l_category_rec.segment18 ,
X_SEGMENT19 => l_category_rec.segment19 ,
X_SEGMENT20 => l_category_rec.segment20 ,
X_SUMMARY_FLAG => l_category_rec.summary_flag,
X_ENABLED_FLAG => l_category_rec.enabled_flag,
X_START_DATE_ACTIVE => l_category_rec.start_date_active,
X_END_DATE_ACTIVE => l_category_rec.end_date_active,
X_ATTRIBUTE_CATEGORY => l_category_rec.attribute_category,
X_ATTRIBUTE1 => l_category_rec.attribute1 ,
X_ATTRIBUTE2 => l_category_rec.attribute2 ,
X_ATTRIBUTE3 => l_category_rec.attribute3 ,
X_ATTRIBUTE4 => l_category_rec.attribute4 ,
X_ATTRIBUTE5 => l_category_rec.attribute5 ,
X_ATTRIBUTE6 => l_category_rec.attribute6 ,
X_ATTRIBUTE7 => l_category_rec.attribute7 ,
X_ATTRIBUTE8 => l_category_rec.attribute8 ,
X_ATTRIBUTE9 => l_category_rec.attribute9 ,
X_ATTRIBUTE10 => l_category_rec.attribute10,
X_ATTRIBUTE11 => l_category_rec.attribute11,
X_ATTRIBUTE12 => l_category_rec.attribute12,
X_ATTRIBUTE13 => l_category_rec.attribute13,
X_ATTRIBUTE14 => l_category_rec.attribute14,
X_ATTRIBUTE15 => l_category_rec.attribute15,
X_LAST_UPDATE_DATE => l_sys_date,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_CREATION_DATE => l_sys_date,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
PROCEDURE Update_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,
x_return_status OUT NOCOPY VARCHAR2 ,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
)
IS
-- Start OF comments
-- API name : Update_Category
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Update a category.
--
-- Version: Current Version 0.1
-- Previous Version : None
-- Notes : Stub Version
--
-- END OF comments
l_api_name CONSTANT VARCHAR2(30) := 'Update_Category';
SAVEPOINT Update_Category_PUB;
Preprocess_Category_Rec(G_UPDATE, p_category_rec, l_category_rec) ;
Flex_Validate(G_UPDATE, l_category_rec);
MTL_CATEGORIES_PKG.Update_Row(
X_CATEGORY_ID => l_category_rec.category_id,
X_DESCRIPTION => l_category_rec.description,
X_STRUCTURE_ID => l_category_rec.structure_id,
X_DISABLE_DATE => l_category_rec.disable_date,
X_WEB_STATUS => l_category_rec.web_status,--Bug: 2430879
X_SUPPLIER_ENABLED_FLAG => l_category_rec.supplier_enabled_flag,--Bug: 2645153
X_SEGMENT1 => l_category_rec.segment1 ,
X_SEGMENT2 => l_category_rec.segment2 ,
X_SEGMENT3 => l_category_rec.segment3 ,
X_SEGMENT4 => l_category_rec.segment4 ,
X_SEGMENT5 => l_category_rec.segment5 ,
X_SEGMENT6 => l_category_rec.segment6 ,
X_SEGMENT7 => l_category_rec.segment7 ,
X_SEGMENT8 => l_category_rec.segment8 ,
X_SEGMENT9 => l_category_rec.segment9 ,
X_SEGMENT10 => l_category_rec.segment10 ,
X_SEGMENT11 => l_category_rec.segment11 ,
X_SEGMENT12 => l_category_rec.segment12 ,
X_SEGMENT13 => l_category_rec.segment13 ,
X_SEGMENT14 => l_category_rec.segment14 ,
X_SEGMENT15 => l_category_rec.segment15 ,
X_SEGMENT16 => l_category_rec.segment16 ,
X_SEGMENT17 => l_category_rec.segment17 ,
X_SEGMENT18 => l_category_rec.segment18 ,
X_SEGMENT19 => l_category_rec.segment19 ,
X_SEGMENT20 => l_category_rec.segment20 ,
X_SUMMARY_FLAG => l_category_rec.summary_flag,
X_ENABLED_FLAG => l_category_rec.enabled_flag,
X_START_DATE_ACTIVE => l_category_rec.start_date_active,
X_END_DATE_ACTIVE => l_category_rec.end_date_active,
X_ATTRIBUTE_CATEGORY => l_category_rec.attribute_category,
X_ATTRIBUTE1 => l_category_rec.attribute1 ,
X_ATTRIBUTE2 => l_category_rec.attribute2 ,
X_ATTRIBUTE3 => l_category_rec.attribute3 ,
X_ATTRIBUTE4 => l_category_rec.attribute4 ,
X_ATTRIBUTE5 => l_category_rec.attribute5 ,
X_ATTRIBUTE6 => l_category_rec.attribute6 ,
X_ATTRIBUTE7 => l_category_rec.attribute7 ,
X_ATTRIBUTE8 => l_category_rec.attribute8 ,
X_ATTRIBUTE9 => l_category_rec.attribute9 ,
X_ATTRIBUTE10 => l_category_rec.attribute10,
X_ATTRIBUTE11 => l_category_rec.attribute11,
X_ATTRIBUTE12 => l_category_rec.attribute12,
X_ATTRIBUTE13 => l_category_rec.attribute13,
X_ATTRIBUTE14 => l_category_rec.attribute14,
X_ATTRIBUTE15 => l_category_rec.attribute15,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
mdebug('Updated Category: '||To_char(l_category_rec.category_id));
mdebug('Update_Category:: Tracing....10');
ROLLBACK TO Update_Category_PUB;
ROLLBACK TO Update_Category_PUB;
ROLLBACK TO Update_Category_PUB;
END Update_Category;
PROCEDURE Update_Category_Description
(
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,
x_return_status OUT NOCOPY VARCHAR2 ,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_category_id IN NUMBER,
p_description IN VARCHAR2
-- deleted as this can be picked up from the environment.
--p_language IN VARCHAR2
)
IS
-- Start OF comments
-- API name : Update_Category_Description
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Update a category description in the specified language.
--
-- Version: Current Version 0.1
-- Previous Version : None
-- Notes : Stub Version
-- END OF comments
l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Description';
SAVEPOINT Update_Category_Desc_PUB;
UPDATE mtl_categories_tl
SET
description = p_description,
last_update_date = Sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
source_lang = userenv('LANG')
WHERE category_id = p_category_id
AND userenv('LANG') IN (language, source_lang) ;
mdebug('Trying to Update a non-existant Category.');
ROLLBACK TO Update_Category_Desc_PUB;
ROLLBACK TO Update_Category_Desc_PUB;
ROLLBACK TO Update_Category_Desc_PUB;
END Update_Category_Description;
PROCEDURE Delete_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,
x_return_status OUT NOCOPY VARCHAR2 ,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_category_id IN NUMBER
)
IS
-- Start OF comments
-- API name : Delete_Category
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Delete a category.
--
-- Version: Current Version 0.1
-- Previous Version : None
-- Notes : Stub Version
--
-- END OF comments
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category';
SELECT 'x'
FROM dual
WHERE exists
( SELECT category_id
FROM mtl_item_categories
WHERE category_id = p_category_id
);
SELECT 'x'
FROM dual
WHERE exists
( SELECT default_category_id
FROM mtl_category_sets_b
WHERE default_category_id = p_category_id
);
SELECT 'x'
FROM dual
WHERE exists
( SELECT category_id
FROM mtl_category_set_valid_cats
WHERE category_id = p_category_id
);
SAVEPOINT Delete_Category_PUB;
mdebug('Can Delete: Category not part of any Category Assignment');
mdebug('Cannot delete: Category part of a Category Assignment');
mdebug('Can Delete: Category not a default category');
mdebug('Cannot delete: Category specified is a default category to one of the Category Sets.');
mdebug('Can Delete: Category not part of a Valid category set');
mdebug('Cannot delete: Category specified is part of a valid category set');
delete from mtl_categories_tl
where category_id = p_category_id ;
mdebug('Trying to delete non-existant Category Id from MTL_CATEGORIES_TL.');
delete from mtl_categories_b
where category_id = p_category_id ;
mdebug('Trying to delete non-existant Category Id from MTL_CATEGORIES_B.');
mdebug('Category deleted successfully: '||p_category_id);
ROLLBACK TO Delete_Category_PUB;
ROLLBACK TO Delete_Category_PUB;
ROLLBACK TO Delete_Category_PUB;
END Delete_Category;
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,
x_return_status OUT NOCOPY VARCHAR2,
x_errorcode OUT NOCOPY NUMBER,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_category_id IN NUMBER,
p_category_set_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER
)
IS
-- Start OF comments
-- API name : Delete_Category_Assignment
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Delete an item category assignment.
--
-- Version: Current Version 0.1
-- Previous Version : None
-- Notes : Stub Version
--
-- END OF comments
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category_Assignment';
SAVEPOINT Delete_Category_Assignment_PUB;
INV_ITEM_CATEGORY_PVT.Delete_Category_Assignment
(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_category_set_id => p_category_set_id
, p_category_id => p_category_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
mdebug('Delete_Category_Assignment: Tracing...1');
DELETE FROM mtl_item_categories
WHERE category_set_id = p_category_set_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND category_id = p_category_id;
mdebug('Delete_Category_Assignment: Done!!');
,p_dml_type => 'DELETE'
,p_inventory_item_id => p_inventory_item_id
,p_organization_id => p_organization_id
,p_category_set_id => p_category_set_id
,p_category_id => p_category_id
,p_old_category_id => null --add by geguo.
);
ROLLBACK TO Delete_Category_Assignment_PUB;
ROLLBACK TO Delete_Category_Assignment_PUB;
ROLLBACK TO Delete_Category_Assignment_PUB;
END Delete_Category_Assignment;
SELECT STRUCTURE_ID
FROM MTL_CATEGORY_SETS MCS,
MTL_DEFAULT_CATEGORY_SETS MDCS
WHERE FUNCTIONAL_AREA_ID = 2
AND MCS.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID;
l_login_id mtl_category_set_valid_cats.last_update_login%TYPE;
(p_validation_type => G_INSERT
,p_category_set_id => p_category_set_id
,p_category_id => p_category_id
,p_parent_category_id => p_parent_category_id
,p_calling_api => l_api_name
) THEN
IF l_debug = 1 THEN
mdebug('Create_Valid_Category: Inserting data into category sets ');
INSERT INTO mtl_category_set_valid_cats
( category_set_id
, category_id
, parent_category_id
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
)
VALUES
( p_category_set_id
, p_category_id
, p_parent_category_id
, l_user_id
, SYSDATE
, l_user_id
, SYSDATE
, l_login_id
, l_request_id
, l_prog_appl_id
, l_program_id
, SYSDATE
);
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 : Public
-- Pre-reqs : None
-- FUNCTION : Update record in mtl_category_set_valid_cats.
--
-- Version: Current Version 1.0
-- Previous Version : None
-- Notes : Stub Version
--
-- END OF comments
l_api_name CONSTANT VARCHAR2(30) := 'Update_Valid_Category';
l_login_id mtl_category_set_valid_cats.last_update_login%TYPE;
mdebug('Update_Valid_Category: Tracing...1');
SAVEPOINT Update_Valid_Category_PUB;
mdebug('Update_Valid_Category: Invalid API call');
(p_validation_type => G_UPDATE
,p_category_set_id => p_category_set_id
,p_category_id => p_category_id
,p_parent_category_id => p_parent_category_id
,p_calling_api => l_api_name
) THEN
l_user_id := fnd_global.user_id;
mdebug('Update_Valid_Category: About to update the category record');
UPDATE mtl_category_set_valid_cats
SET parent_category_id = p_parent_category_id
,last_updated_by = l_user_id
,last_update_date = SYSDATE
,last_update_login = l_login_id
WHERE category_set_id = p_category_set_id
AND category_id = p_category_id;
mdebug('Update_Valid_Category: Record not available for update');
fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_UPDATE');
mdebug('Update_Valid_Category: Apps Exception raised');
ROLLBACK TO Update_Valid_Category_PUB;
mdebug('Update_Valid_Category: Apps Unexpected Error');
ROLLBACK TO Update_Valid_Category_PUB;
mdebug('Update_Valid_Category: Exception -- OTHERS ');
ROLLBACK TO Update_Valid_Category_PUB;
END Update_Valid_Category;
PROCEDURE Delete_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,
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 : Delete_Valid_Category
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Delete the record from mtl_category_set_valid_cats.
--
-- Version: Current Version 1.0
-- Previous Version : None
-- Notes : Stub Version
--
-- END OF comments
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Valid_Category';
SELECT description
FROM mtl_categories_vl
WHERE category_id = cp_category_id;
SELECT category_id
FROM mtl_item_categories item_cat
WHERE item_cat.category_id = cp_category_id
AND item_cat.category_set_id = cp_category_set_id
AND rownum = 1;
SELECT valid_cats.category_id
FROM mtl_category_set_valid_cats valid_cats
WHERE EXISTS
(SELECT 'X'
FROM mtl_item_categories item_cat
WHERE item_cat.category_id = valid_cats.category_id
AND item_cat.category_set_id = cp_category_set_id
)
CONNECT BY PRIOR
valid_cats.category_id = valid_cats.parent_category_id
AND valid_cats.category_set_id = cp_category_set_id
START WITH
valid_cats.category_id = cp_category_id
AND category_set_id = cp_category_set_id
AND rownum = 1;
SELECT cat_sets.default_category_id
FROM mtl_category_sets_b cat_sets
WHERE cat_sets.category_set_id = p_category_set_id
AND cat_sets.default_category_id = p_category_id
AND NVL(cat_sets.validate_flag,'N') = 'Y';
SELECT cat_sets.default_category_id
FROM mtl_category_sets_b cat_sets
WHERE cat_sets.category_set_id = p_category_set_id
AND EXISTS
(SELECT 'X'
FROM mtl_category_set_valid_cats check_cats
WHERE check_cats.category_id = cat_sets.default_category_id
CONNECT BY PRIOR
check_cats.category_id = check_cats.parent_category_id
AND check_cats.category_set_id = cp_category_set_id
START WITH
check_cats.category_id = cp_category_id
AND check_cats.category_set_id = cp_category_set_id
)
AND NVL(cat_sets.validate_flag,'N') = 'Y';
mdebug('Delete_Valid_Category: Tracing...1');
SAVEPOINT Delete_Valid_Category_PUB;
mdebug('Delete_Valid_Category: Invalid API call');
mdebug('Delete_Valid_Category: Mandatory parameters missing');
mdebug('Delete_Valid_Category: Record not available for deletion');
fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_DELETE');
mdebug('Delete_Valid_Category: Cannot delete default category');
fnd_message.set_name('INV','INV_DELETE_DEF_CAT_ERR');
mdebug('Delete_Valid_Category: No items associated! Delete now');
DELETE mtl_category_set_valid_cats delete_cats
WHERE category_set_id = p_category_set_id
AND EXISTS
(SELECT 'X'
FROM mtl_category_set_valid_cats
WHERE category_id = delete_cats.category_id
CONNECT BY PRIOR category_id = parent_category_id
AND category_set_id = p_category_set_id
START WITH category_id = p_category_id
AND category_set_id = p_category_set_id
);
DELETE mtl_category_set_valid_cats delete_cats
WHERE category_set_id = p_category_set_id
AND category_id = p_category_id;
mdebug('Delete_Valid_Category: Items ASSOCIATED!! ');
mdebug('Delete_Valid_Category: Apps Exception raised');
ROLLBACK TO Delete_Valid_Category_PUB;
mdebug('Delete_Valid_Category: Apps Unexpected Error');
ROLLBACK TO Delete_Valid_Category_PUB;
mdebug('Delete_Valid_Category: Exception -- OTHERS ');
ROLLBACK TO Delete_Valid_Category_PUB;
END Delete_Valid_Category;
,p_use_def_vals_on_insert => FND_API.G_TRUE
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
PROCEDURE Update_Category_Assignment
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_category_id IN NUMBER,
p_old_category_id IN NUMBER,
p_category_set_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_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 : Delete_Category_Assignment
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Delete an item category assignment.
--
-- Version: Current Version 0.1
-- Previous Version : None
-- Notes : Stub Version
--
-- END OF comments
l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Assignment';
SAVEPOINT Update_Category_Assignment_PUB;
INV_ITEM_CATEGORY_PVT.Update_Category_Assignment
(
p_api_version => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_inventory_item_id => p_inventory_item_id
, p_organization_id => p_organization_id
, p_category_set_id => p_category_set_id
, p_category_id => p_category_id
, p_old_category_id => p_old_category_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
mdebug('Update_Category_Assignment: Done!!');
,p_dml_type => 'UPDATE'
,p_inventory_item_id => p_inventory_item_id
,p_organization_id => p_organization_id
,p_category_set_id => p_category_set_id
,p_category_id => p_category_id
,p_old_category_id => p_old_category_id
);
ROLLBACK TO Update_Category_Assignment_PUB;
ROLLBACK TO Update_Category_Assignment_PUB;
ROLLBACK TO Update_Category_Assignment_PUB;
END Update_Category_Assignment;
SELECT count(segment_num)
FROM fnd_id_flex_segments
WHERE application_id = G_INVENTORY_APP_ID
AND id_flex_code = G_CAT_FLEX_CODE
AND id_flex_num = p_structure_id
AND (enabled_flag = 'Y' OR NVL(g_eni_upgarde_flag,'N') = 'Y');-- Added for 11.5.10 ENI Upgrade
SELECT application_column_name,rownum
FROM fnd_id_flex_segments
WHERE application_id = 401
AND id_flex_code = 'MCAT'
AND id_flex_num = cp_flex_num
AND enabled_flag = 'Y'
ORDER BY segment_num ASC;
Preprocess_Category_Rec(G_INSERT, p_category_rec, l_category_rec) ;
FND_MESSAGE.Set_Name('FND','FLEX-NO DYNAMIC INSERTS');