Friday 5 January 2018

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;

5 comments:

12345 said...

Good Blog , Thanks for sharing this informative article.
Oracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad

Rainbow Training Institute said...

Thank you for sharing such a nice and really very helpful article

Oracle Fusion HCM Online Training

Anonymous said...

Good blog, Thanks for sharing this informative article. It would be helpful to all if you write a full article.
Oracle Fusion HCM Online Training"

shaik shah said...

Goodblog, thanks for sharing this informative article. It would be helpful to all .your way of explaining in this good in this article.
Oracle Fusion HCM Online Training

Goutham Raj said...

Good Article, Alots of information provided, Thanks for sharing this information.
Spark Scala Training

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *