Friday, June 17, 2011

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;