Group Team
PREREQUISITE
-------------------
Following are prerequisite before Assigning category to an Item.
1. Organization must be defined in Oracle.
2. Item must be defined.
3. Category set name and Category Combination Should be defined.
In Below example Item 'NewTestItem' is considered. Its inventory item id is 83456 and belong to 'Vision operation' Organization with Org id 106.
Category Set name 'Inventory' with category Set id '1' and Category 'NONE' with category id '5' is needs to be assigned to the Item.
Below Code can be used for Assigning the Category set and Category Combination for the item 'NewTestItem'.
Code is tested in Oracel11i (11.5.10).
DECLARE
X_RETURN_STATUS
VARCHAR2(1000);
X_ERRORCODE
NUMBER;
X_MSG_COUNT
NUMBER;
X_MSG_DATA
VARCHAR2(1000);
BEGIN
--Apps Initialization is available in another section. Use the below link to know in detail
Apps_Initialize;
INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT
( P_API_VERSION
=> 1.0,
P_INIT_MSG_LIST
=> FND_API.G_FALSE,
P_COMMIT
=> FND_API.G_FALSE,
X_RETURN_STATUS
=> X_RETURN_STATUS,
X_ERRORCODE
=> X_ERRORCODE,
X_MSG_COUNT
=> X_MSG_COUNT,
X_MSG_DATA
=> X_MSG_DATA,
P_CATEGORY_ID
=>
5,
P_CATEGORY_SET_ID
=>
1,
P_INVENTORY_ITEM_ID
=>
83456,
P_ORGANIZATION_ID
=>
106);
IF x_return_status =
fnd_api.g_ret_sts_success
THEN
COMMIT;
DBMS_OUTPUT.put_line ('
Item Category Assignment using API is Successful');
ELSE
BEGIN
IF (
fnd_msg_pub.count_msg > 1)
THEN
FOR k IN 1 ..
fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get (p_msg_index
=> k,
p_encoded
=> 'F',
p_data
=> x_msg_data,
p_msg_index_out
=> x_msg_index_out
);
DBMS_OUTPUT.PUT_LINE('
x_msg_data:= ' || x_msg_data);
IF x_msg_data
IS NOT NULL
THEN
l_error_message := l_error_message || '
-' || x_msg_data;
END IF;
END LOOP;
ELSE
--Only one error
fnd_msg_pub.get (p_msg_index
=> 1,
p_encoded
=> 'F',
p_data
=> x_msg_data,
p_msg_index_out
=> x_msg_index_out
);
l_error_message := x_msg_data;
END IF;
DBMS_OUTPUT.put_line ( '
Error encountered by the API is '
|| l_error_message
);
ROLLBACK;
EXCEPTION
WHEN OTHERS
THEN
l_error_message :=
SQLERRM;
DBMS_OUTPUT.put_line ( '
Error encountered by the API is '
|| l_error_message
);
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('
Error in Assigning Category to an Item and error is '||
SUBSTR(
SQLERRM,1,200));
END;
/
Group Team
Following are prerequistics before updating category to an Item.
1. Organization must be defined in Oracle.
2. Item must be defined.
3. Category set name and Category Combination is Unique.
4. Already Category set name and Category Combination Should be assigned to the Item. Already existing Item Category is to be replaced by New Category.
4. New Category must be defined and it must be the part of category set name defined.
Below Code is tested in Oracle11i (11.5.10).
DECLARE
v_item
VARCHAR2(100) := '
NewItem';
v_cat_set_name
VARCHAR2(100) := '
Inventory';
v_Update_category
VARCHAR2(50) := '
NewCategory';
v_organization_id
NUMBER := 101;
--Vision Operation is the Org Name
v_inv_item_id
NUMBER := 0;
l_error
VARCHAR2(1000):= NULL;
v_flag
NUMBER := 0;
v_category_id
NUMBER := 0;
v_cat_set_id
NUMBER := 0;
v_11i_cat_id
NUMBER := 0;
BEGIN
dbms_output.put_line('
------Checking whether Item exists in Oracle 11i----------------');
BEGIN
SELECT inventory_item_id
INTO v_inv_item_id
FROM mtl_system_items_b msi
WHERE msi.organization_id =
v_organization_id
AND msi.segment1 =
v_item;
EXCEPTION
WHEN OTHERS THEN
l_error := '
Item : '||v_item||'
-- not defined for the organization.';
dbms_output.put_line(
l_error);
v_flag := 1;
END;
dbms_output.put_line('
------Checking Category Set exists in Oracle 11i----------------');
BEGIN
SELECT category_set_id
INTO v_cat_set_id
FROM mtl_category_sets
WHERE category_set_name =
v_cat_set_name;
EXCEPTION
WHEN OTHERS THEN
l_error := v_cat_set_name||
' -- '||'
does not exist in Oracle 11i.';
dbms_output.put_line(
l_error);
v_flag := 1;
END;
dbms_output.put_line('
------Checking Category exists in Oracl 11i----------------');
BEGIN
SELECT category_id
INTO v_category_id
FROM mtl_categories mc,
mtl_category_sets mcs
WHERE mc.segment1 = v_Update_category -- Incase category is combination of different segment then needs to check for all segment values
AND mc.structure_id =mcs.structure_id
AND mcs.category_set_name=
v_cat_set_name;
EXCEPTION
WHEN OTHERS THEN
l_error := '
Category '||v_Update_category||'
does not exists in Oracle 11i.';
dbms_output.put_line(
l_error);
v_flag := 1;
END;
dbms_output.put_line('
------Checking whether any Category is assigned to the Item----------');
BEGIN
SELECT category_id
INTO v_11i_cat_id
FROM mtl_item_categories
WHERE category_set_id =
v_cat_set_id
AND organization_id =
v_organization_id
AND inventory_item_id =
v_inv_item_id;
EXCEPTION
WHEN OTHERS THEN
l_error := '
There is no Category Set already existing for the Item. Hence cannot update the new Category. Need to Create new Category Set and Category Combination for the Item';
dbms_output.put_line(
l_error);
v_flag := 1;
END;
IF v_flag = 0
THEN
INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,CATEGORY_SET_ID
,CATEGORY_ID
,OLD_CATEGORY_ID
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PROCESS_FLAG
,TRANSACTION_TYPE
,SET_PROCESS_ID
)
VALUES
( v_inv_item_id
,v_organization_id
,v_cat_set_id
,v_category_id
,v_11i_cat_id
,NUll
,NUll
,NUll
,NUll
,SYSDATE
,-1
,SYSDATE
,-1
,-1
,1 --Process_flag(always 1 - pending)
,'
UPDATE'
,1 --set_process_id
);
COMMIT;
END IF;
BEGIN
-- Calling Item Import Procedure to load data into base table. Click on the below Item_Import link to know about API used to Import Item
Item_Import;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('
Error in calling Import Program and error is '||
SUBSTR(
SQLERRM,1,200));
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('
Error in assinging new category to Item and error is '||
SUBSTR(
SQLERRM,1,200));
END;
/
Group Team
Prerequistics
-------------
Following needs to be defined before assigning Item to a Category
1. Organization must be defined.
2. Item must be defined.
3. Category set must exists.
4. Category must be defined and it must be the part of category set name defined.
Below Code is tested in Oracle 11i (11.5.10).
DECLARE
v_item
VARCHAR2(100) := '
NewItem';
v_cat_set_name
VARCHAR2(100) := '
Inventory';
v_category_segment1
VARCHAR2(50) := '
NONE';
v_organization_id
NUMBER :=
101;
--Vision Operation is the Org Name
v_inv_item_id
NUMBER := 0;
l_error
VARCHAR2(1000) := NULL;
v_flag
NUMBER := 0;
v_category_id
NUMBER := 0;
v_cat_set_id
NUMBER := 0;
BEGIN
dbms_output.put_line('
------Checking whether Item exists in Oracl 11i----------------');
BEGIN
SELECT inventory_item_id
INTO v_inv_item_id
FROM mtl_system_items_b msi
WHERE msi.organization_id =
v_organization_id
AND msi.segment1 =
v_item;
EXCEPTION
WHEN OTHERS THEN
l_error := '
Item : '||v_item||'
-- not defined for ITD organization.';
dbms_output.put_line(
l_error);
v_flag := 1;
END;
dbms_output.put_line('
------Checking Category Set exists in Oracle 11i----------------');
BEGIN
SELECT category_set_id
INTO v_cat_set_id
FROM mtl_category_sets
WHERE category_set_name =
v_cat_set_name;
EXCEPTION
WHEN OTHERS THEN
l_error := v_cat_set_name||
' -- '||'
does not exist in Oracle 11i.';
dbms_output.put_line(
l_error);
v_flag := 1;
END;
dbms_output.put_line('------Checking Category exists in Oracle 11i----------------');
BEGIN
SELECT category_id
INTO v_category_id
FROM mtl_categories mc,
mtl_category_sets mcs
WHERE mc.segment1 = v_category_segment1 -- Incase category is combination of different segment then needs to check for all segment values
AND mc.structure_id =mcs.structure_id
AND mcs.category_set_name=
v_cat_set_name;
EXCEPTION
WHEN OTHERS THEN
l_error := '
Category '||v_category_segment1||'
does not exists in Oracle 11i.';
d
bms_output.put_line(
l_error);
v_flag := 1;
END;
IF v_flag = 0
THEN
INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
( INVENTORY_ITEM_ID
,ORGANIZATION_ID
,CATEGORY_SET_ID
,CATEGORY_ID
,REQUEST_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PROCESS_FLAG
,TRANSACTION_TYPE
,SET_PROCESS_ID
)
VALUES
( v_inv_item_id
,v_organization_id
,v_cat_set_id
,v_category_id
,NUll
,NUll
,NUll
,NUll
,SYSDATE
,-1
,SYSDATE
,-1
,-1
,1 --Process_flag(always 1 - pending)
,'CREATE'
,1 --set_process_id
);
COMMIT;
END IF;
BEGIN
-- Calling Item Import Procedure to load data into base table. Click on the below Item_Import link to know about API used to Import Item
Item_Import;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('
Error in calling Import Program and error is '||SUBSTR(SQLERRM,1,200));
END;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('
Error in assinging new category to Item and error is '||
SUBSTR(SQLERRM,1,200));
END;
/
