Oracle API to assign item categories to inventory items

Oracle API to assign item categories to inventory items

In this PLSQL code we have to assign Inventory Item categories to Inventory Items. 

API Used:  

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT

INV_ITEM_CATEGORY_PUB.Create_Category_Assignment

 

1 2 3 4 5 6 7 8 9 10 11 12

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_STG -- custom staging table 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_STG -- custom staging table 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_STG -- custom staging table 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_STG -- custom staging table 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_STG -- custom staging table 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_STG -- custom staging table SET ERROR_MESSAGE= V_ERROR_MESSAGE, PROCESSED_FLAG='N' where rowid=i.rowid; end if; end loop; end; end;