Item Categories Created Syntax

 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;