Group Team
PREREQUISITE
-----------------
Following are prerequisite before updating category for an Item.
1. Organization must be defined in Oracle.
2. Item must be defined.
3. Category set name and Category Combination Should already exists for the Item.
4. New Category that needs to be assigned to the Item Should be the part of already existing category set name.
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 already assigned to the Item.
New Category 'TestCategory' with category id 1198 needs to be Updated to the Item.
New Category is part of the Category Set 'Inventory'.
Below Code can be used for Updating the Category set and Category Combination for the item 'NewTestItem'.
Code is tested in 11.5.10
DECLARE
X_RETURN_STATUS VARCHAR2(1000);
X_ERRORCODE NUMBER;
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(1000);
l_error_message
VARCHAR2 (1000) :=
NULL;
x_msg_index_out
NUMBER;
BEGIN
--Apps Initialization is available in another section. Use the below link to know in detail
Apps_Initialize;
INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT
( P_API_VERSION
=> 1.0,
P_INIT_MSG_LIST
=> FND_API.G_FALSE,
P_COMMIT
=> FND_API.G_FALSE,
P_CATEGORY_ID
=>
5,
P_OLD_CATEGORY_ID
=>
1198,
P_CATEGORY_SET_ID
=>
1,
P_INVENTORY_ITEM_ID
=>
83456,
P_ORGANIZATION_ID
=>
106,
X_RETURN_STATUS
=> X_RETURN_STATUS,
X_ERRORCODE
=> X_ERRORCODE,
X_MSG_COUNT
=> X_MSG_COUNT,
X_MSG_DATA
=> X_MSG_DATA
);
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
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;
/
Group Team
We can use the below code to Submit the Item Import Program using API.
Code is tested in 11i.
CREATE OR REPLACE PROCEDURE Item_import
IS
v_phase
VARCHAR2(240);
v_status
VARCHAR2(240);
v_request_phase
VARCHAR2(240);
v_request_status
VARCHAR2(240);
v_finished
BOOLEAN;
v_message
VARCHAR2(240);
l_request_id
NUMBER;
BEGIN
--We need to apps initialize before calling the import program
--Apps Initialize is explained in another section. Check below link to know in detail
Apps Initialize;
l_request_id :=
Fnd_Request.submit_request (
application => '
INV',
program => '
INCOIN',
description => NULL,
start_time =>
SYSDATE,
sub_request =>
FALSE,
argument1 => 1, --Mode to run this request(Insert new cost information only)
argument2 => 1, --Group ID option (All)
argument3 => 1, -- Group ID Dummy
argument4 => 1,
argument5 => 1,
argument6 => <
Group ID>, -- Group ID passed in Interface table
argument7 => <1
or 2> -- 1 for Create and 2 for Update
);
COMMIT;
IF ( l_request_id = 0 )
THEN
dbms_output.put_line( '
Submission of Import failed ');
END IF;
-- Wait for request to run the import Program to Finish
v_finished :=
fnd_concurrent.wait_for_request (request_id => l_request_id,
interval => 60,
max_wait => 0,
phase => v_phase,
status => v_status,
dev_phase => v_request_phase,
dev_status => v_request_status,
message => v_message);
dbms_output.put_line('
Request Phase : '|| v_request_phase );
dbms_output.put_line('
Request Status : ' || v_request_status );
--Testing end status
IF (
UPPER(v_request_status) = '
NORMAL')
THEN
dbms_output.put_line( '
Submission of Item Import is Success ');
ELSE
dbms_output.put_line( '
Submission of Item Import failed ');
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line( '
Submission of Import failed ');
END ;
/
Group Team
Procedure apps_initialize can be used to apps initialize from backend.
It is tested in 11i.
CREATE OR REPLACE PROCEDURE apps_initialize
IS
v_responsibility_name
VARCHAR2 (100) :=
'Payables';
v_user_name
VARCHAR2 (100) :=
'Oracle';
v_application_id
NUMBER := 0;
v_responsibility_id
NUMBER := 0;
v_user_id
NUMBER := 0;
v_flag
NUMBER := 0;
BEGIN
--Getting Responsbility and Application Id
BEGIN
SELECT responsibility_id,
application_id
INTO v_responsibility_id,
v_application_id
FROM fnd_responsibility_tl
WHERE responsibility_name = v_responsibility_name
AND LANGUAGE = USERENV ('LANG');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
(
'Error in getting Responsibility information and error is '
||
SUBSTR (SQLERRM, 1, 200)
);
v_flag := 1;
END;
----Getting User Id
BEGIN
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE user_name = v_user_name;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
(
'Error in getting User information and error is '
||
SUBSTR (SQLERRM, 1, 200)
);
v_flag := 1;
END;
IF v_flag = 0
THEN
fnd_global.apps_initialize (v_user_id,
v_responsibility_id,
v_application_id
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
(
'Error in procedure apps initialize and error is '
||
SUBSTR (SQLERRM, 1, 200)
);
END;
/
