item category conversion in oracle apps r12

item category conversion in oracle apps r12

In this post , We will discuss about Item category conversion in oracle apps r12. Oracle has provided two options to assign items categories to the Inventory item in oracle apps. One options is two use the Standard Oracle interface and second option is to use the api to assign the item category to the inventory item in oracle apps r12. API is the more easy and fast process to assign item categories as compared to the Oracle interface. Here below I am sharing the sample code to using api to item category conversion in oracle apps r12

Sample code of Using item category conversion in oracle apps r12

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_STGwhere 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_vwhere SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5=i.INV_SEGMENTSand 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_bwhere segment1=i.item_codeAND 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_NAMEand 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_categorieswhere CATEGORY_SET_ID=v_CATEGORY_SET_IDand INVENTORY_ITEM_ID=v_inventory_item_idAND 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_STGSET 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_STGSET 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_STGSET 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_STGSET 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_STGSET ERROR_MESSAGE= V_ERROR_MESSAGE,

PROCESSED_FLAG='N'
where rowid=i.rowid;
end if;
end loop;

end;
　
 end;