Oracle API to Assign Inventory item Categories to Inventroy items

In this post , I will share the PLSQL code which uses oracle standard Oracle API to assign Inventory Item categories to Inventory Items.

But before this Your Item Categories should be created in System.


　




declare
  V_COUNT NUMBER;

V_ERROR_FLAG VARCHAR2(40);

V_ERROR_MESSAGE VARCHAR2(4000);

v_inventory_item_id number;

v_CATEGORY_SET_ID number;

V_CATEGORY_ID number;

v_old_CATEGORY_ID number;

cursor c is

SELECT ROWID,item_code,CATEGORY_SET_NAME,SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5 INV_SEGMENTS,PROCESSED_FLAG FROM XXINV_CATEGRY_ASSIGN_STG

where PROCESSED_FLAG='N';




BEGIN
  
for i in c loop

V_ERROR_MESSAGE:=null;

V_ERROR_FLAG:='N';

V_CATEGORY_ID:=NULL;

v_inventory_item_id:=NULL;

v_CATEGORY_SET_ID:=NULL;

v_old_CATEGORY_ID:=NULL;




BEGIN
  


　
  select CATEGORY_ID INTO V_CATEGORY_ID from mtl_categories_v

where SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5=i.INV_SEGMENTS

and STRUCTURE_NAME=i.CATEGORY_SET_NAME;




  DBMS_OUTPUT.put_line('V_CATEGORY_ID'||V_CATEGORY_ID);




 EXCEPTION WHEN OTHERS THEN

V_ERROR_FLAG:='Y';

V_ERROR_MESSAGE :='ERROR IN ITEM CATEGORY';




END;
  



begin
  
select inventory_item_id into v_inventory_item_id from mtl_system_items_b

where segment1=i.item_code

AND ORGANIZATION_ID=P_ORG_ID;

EXCEPTION WHEN OTHERS THEN

V_ERROR_FLAG:='Y';

V_ERROR_MESSAGE :=V_ERROR_MESSAGE;




END;
  


　
 begin

select CATEGORY_SET_ID into v_CATEGORY_SET_ID from mtl_category_sets_tl

where CATEGORY_SET_NAME=i.CATEGORY_SET_NAME

and rownum=1;




  DBMS_OUTPUT.put_line('v_CATEGORY_SET_ID'||v_CATEGORY_SET_ID);




  EXCEPTION WHEN OTHERS THEN

V_ERROR_FLAG:='Y';

V_ERROR_MESSAGE :=V_ERROR_MESSAGE||'CATEGORY_SET_NAME not existing in oracle';




END;
  



begin
  
select CATEGORY_ID into v_old_CATEGORY_ID from mtl_item_categories

where CATEGORY_SET_ID=v_CATEGORY_SET_ID

and INVENTORY_ITEM_ID=v_inventory_item_id

AND ORGANIZATION_ID=P_ORG_ID;

DBMS_OUTPUT.put_line('v_old_CATEGORY_ID'||v_old_CATEGORY_ID);




 EXCEPTION WHEN OTHERS THEN

v_old_CATEGORY_ID:=null;




END;
  
if V_ERROR_FLAG='N' THEN

if v_old_CATEGORY_ID is not null then

DBMS_OUTPUT.put_line('IN PROG');




DECLARE
  
v_return_status VARCHAR2(1) := NULL;

v_msg_count NUMBER := 0;

v_msg_data VARCHAR2(2000);




  v_errorcode VARCHAR2(1000);

v1_category_id NUMBER;

v1_old_category_id NUMBER;

v1_category_set_id NUMBER;

v1_inventory_item_id NUMBER;

v_organization_id NUMBER;

v_context VARCHAR2(2);




begin
  




--- context done ------------
   v1_old_category_id := v_old_CATEGORY_ID;

v1_category_id := V_CATEGORY_ID;

v1_category_set_id := v_CATEGORY_SET_ID;

v1_inventory_item_id := v_inventory_item_id;

v_organization_id := p_org_id;

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT

( p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_commit => FND_API.G_FALSE,

x_return_status => v_return_status,

x_errorcode => v_errorcode,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data,

p_old_category_id => v1_old_category_id,

p_category_id => v1_category_id,

p_category_set_id => v1_category_set_id,

p_inventory_item_id => v1_inventory_item_id,

p_organization_id => v_organization_id);

IF v_return_status = fnd_api.g_ret_sts_success THEN

UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE=null,

PROCESSED_FLAG='Y'

where rowid=i.rowid;

COMMIT;

DBMS_OUTPUT.put_line ('Updation of category assigment is Sucessfull : '||v_category_id);

ELSE DBMS_OUTPUT.put_line ('Updation of category assigment failed:'||v_msg_data);

ROLLBACK;

UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE='Updation of category assigment failed:'||v_msg_data,

PROCESSED_FLAG='N'

where rowid=i.rowid;

COMMIT;

FOR i IN 1 .. v_msg_count LOOP

v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

dbms_output.put_line( i|| ') '|| v_msg_data);

END LOOP;

END IF;

END;



　
  else




  DECLARE

v_return_status VARCHAR2(1) := NULL;

v_msg_count NUMBER := 0;

v_msg_data VARCHAR2(2000);




  v_errorcode VARCHAR2(1000);

v1_category_id NUMBER;

v1_old_category_id NUMBER;

v1_category_set_id NUMBER;

v1_inventory_item_id NUMBER;

v_organization_id NUMBER;

v_context VARCHAR2(2);




begin
  




--- context done ------------
   v1_category_id := V_CATEGORY_ID;

v1_category_set_id := v_CATEGORY_SET_ID;

v1_inventory_item_id := v_inventory_item_id;

v_organization_id := p_org_id;

INV_ITEM_CATEGORY_PUB.Create_Category_Assignment

( p_api_version => 1.0,

p_init_msg_list => FND_API.G_TRUE,

p_commit => FND_API.G_FALSE,

x_return_status => v_return_status,

x_errorcode => v_errorcode,

x_msg_count => v_msg_count,

x_msg_data => v_msg_data,

p_category_id => v1_category_id,

p_category_set_id => v1_category_set_id,

p_inventory_item_id => v1_inventory_item_id,

p_organization_id => v_organization_id);

IF v_return_status = fnd_api.g_ret_sts_success THEN

UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE=null,

PROCESSED_FLAG='Y'

where rowid=i.rowid;

COMMIT;

DBMS_OUTPUT.put_line ('New category assigment is Sucessfull : '||v_category_id);

ELSE DBMS_OUTPUT.put_line ('New category assigment failed:'||v_msg_data);

ROLLBACK;

UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE='New category assigment failed:'||v_msg_data,

PROCESSED_FLAG='N'

where rowid=i.rowid;

COMMIT;

FOR i IN 1 .. v_msg_count LOOP

v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

dbms_output.put_line( i|| ') '|| v_msg_data);

END LOOP;

END IF;

END;




  end if;




  else




  UPDATE XXINV_CATEGRY_ASSIGN_STG

SET ERROR_MESSAGE= V_ERROR_MESSAGE,

PROCESSED_FLAG='N'

where rowid=i.rowid;

end if;

end loop;




end;
  


　

　

　

　
 end;