Item category interface in oracle apps r12

Item category interface in oracle apps r12

In this post , We will discuss about Item category interface 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 replace Item category interface in oracle apps r12.

 

 

Sample code of Using API instead of Item category interface 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_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;