Category Sets

This program will import one category set to another category set.
After importing run the import category set assignment program.



declare
cursor c1 is
select cat.INVENTORY_ITEM_ID,itm.segment1,itm.description
from mtl_item_categories_v cat, mtl_system_items_b itm
where cat.CATEGORY_ID=33219
and cat.INVENTORY_ITEM_ID=itm.INVENTORY_ITEM_ID
;
lc_cat_set_id number;

begin

select CATEGORY_SET_ID
into lc_cat_set_id
from MTL_CATEGORY_SETS_V
where CATEGORY_SET_NAME='Price Group';

for c1_rec in c1
loop
insert into MTL_ITEM_CATEGORIES_INTERFACE
(SET_PROCESS_ID,
INVENTORY_ITEM_ID,
CATEGORY_SET_ID,
LAST_UPDATE_DATE,
CREATION_DATE,
ORGANIZATION_ID,
CATEGORY_SET_NAME,
CATEGORY_NAME)
values
(1
,c1_rec.inventory_item_id
,lc_cat_set_id --1100000171
,sysdate
,sysdate
,85
,'Price Group'
,'NPR'
);

end loop;

end;

Change Status of WSH Exceptions

This code will change the status from OPEN to CLOSED in WSH_EXCEPTIONS table.
API: WSH_EXCEPTIONS_PUB.Exception_Action

declare
lc_wsh_exceptions_rec WSH_EXCEPTIONS_PUB.XC_ACTION_REC_TYPE;
lx_msg_count NUMBER;
lx_msg_data VARCHAR2 (2000);
lx_return_status VARCHAR2 (200);
l_message VARCHAR2 (2000);
l_msg_index_out NUMBER;

cursor exceptions_cur IS
select *
from apps.wsh_exceptions
where exception_name = 'WSH_CHANGE_SCHED_DATE'
and status='OPEN'
;

begin

for exceptions_rec in exceptions_cur
loop
lc_wsh_exceptions_rec.exception_id:=exceptions_rec.EXCEPTION_ID;
lc_wsh_exceptions_rec.new_status:='CLOSED';


WSH_EXCEPTIONS_PUB.Exception_Action(
p_api_version =>1.0,
p_init_msg_list =>NULL,
p_validation_level =>NULL,
p_commit =>fnd_api.g_true,
x_msg_count =>lx_msg_count,
x_msg_data =>lx_msg_data,
x_return_status =>lx_return_status,
p_exception_rec =>lc_wsh_exceptions_rec,
p_action =>'CHANGE_STATUS'
);
dbms_output.put_line('lx_return_status='||lx_return_status);

for k in 1 .. lx_msg_count
loop
fnd_msg_pub.get(
p_msg_index => k,
p_encoded => 'F',
p_data => l_message,
p_msg_index_out=>l_msg_index_out
);
dbms_output.put_line('The Error Message '
|| k || ' is: ' || l_message);

end loop;

end loop;

end;