[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
ERR_INSERTING EXCEPTION;
select classification_id from iem_email_classifications
where message_id=l_message_id
order by score desc;
cursor c_item is select ib.item_id,ib.item_name,ib.last_update_date
from amv_c_chl_item_match cim,jtf_amv_items_vl ib
where cim.channel_category_id = l_cm_cat_id
and cim.channel_id is null
and cim.approval_status_type ='APPROVED'
and cim.table_name_code ='ITEM'
and cim.available_for_channel_date <= sysdate
and cim.item_id = ib.item_id
and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
and nvl(ib.expiration_date, sysdate) >= sysdate;
cursor c_intent is select a.intent,b.score from
iem_intents a,iem_email_classifications b
where b.message_id=l_message_id
and a.intent_id=b.classification_id;
select * into l_header_Rec
from iem_ms_base_headers
where message_id=l_message_id;
select user_name,kem_flag,email_address,account_type
into l_email_user_name,l_intent_flg,l_email_address,l_acct_type
from iem_mstemail_accounts
where email_account_id=l_post_rec.email_account_id;
l_tag_keyval.delete;
l_class_val_tbl.delete;
select name
into l_folder_name
from iem_route_classifications
where route_classification_id=l_rt_classification_id;
select PARTY_TYPE into l_party_type from HZ_PARTIES
where party_id = l_tag_custid;
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_interaction_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
select contact_party_id into l_cust_contact_id from jtf_ih_interactions
where interaction_id=l_interaction_id;
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_media_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
ELSE -- Rerouted Message Need to update the media item
l_media_rec.media_id:=l_post_rec.ih_media_item_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_media_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Update the Media Life Cycle for Mail Preprocessing
l_media_lc_rec.milcs_id:=l_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_mp_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_activity_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_rule_type=>'AUTODELETE',
p_keyvals_tbl=>l_class_val_tbl,
p_accountid=>l_post_rec.email_account_id,
x_result=>l_autoproc_result,
x_action=>l_action,
x_parameters=>l_param_rec_tbl,
x_return_status=>l_ret_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
l_logmessage:='Error While Calling rules Engine for Autodelete';
IF l_autoproc_result='T' THEN -- Delete the message
-- Create a New MLCS for AUTO_DELETE
l_media_lc_rec.media_id :=l_media_id ;
l_media_lc_rec.milcs_type_id := 42; --EMAIL_AUTO_DELETE
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Update the Media Life Cycle for Auto Delete
l_media_lc_rec.milcs_id:=l_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Add a Activity for AUTO-DELETE
l_activity_rec.start_date_time := SYSDATE;
l_activity_rec.action_id := 72; -- EMAIL AUTO _DELETED
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_activity_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
l_logmessage:='AUTODELETE:Error While Inserting Record in Proc Emails Table ';
l_logmessage:='Error in Auto Delete '||l_out_text;
select count(*) into l_autoack_count
from jtf_ih_media_item_lc_segs
where media_id=l_post_rec.ih_media_item_id
and MILCS_TYPE_ID=29;
l_param_rec_tbl.delete;
l_run_proc_tbl.delete;
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_activity_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Update the Media Life Cycle for Auto Resolve
l_media_lc_rec.milcs_id:=l_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
l_logmessage:='EXECPROC:Error While Inserting Record in Proc Emails Table ';
-- Populate l_email_doc_tbl for both autocreate and autoupdate SR
FOR l_param_index in l_param_rec_tbl.FIRST..l_param_rec_tbl.LAST LOOP
l_status_id:=to_number(l_param_rec_tbl(l_param_index).parameter1);
l_qual_tbl.delete;
if l_acct_type='I' then -- select the party id from profile
l_emp_flag:='Y';
Select person_id into l_contact_id
from per_workforce_current_x
Where upper(email_address)=upper(l_sender);
select party_type into l_party_type from hz_parties
where party_id=l_customer_id;
select object_id into l_party_id
from HZ_RELATIONSHIPS where party_id= l_customer_id and
(relationship_code='CONTACT_OF' or relationship_code='EMPLOYEE_OF')
and status='A';
select contact_point_id into l_contact_point_id
from hz_contact_points
where owner_table_name='HZ_PARTIES'
and owner_table_id=l_customer_id
and contact_point_type='EMAIL'
and upper(email_address)=upper(l_sender)
and contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A');
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
l_logmessage:='AUTOCREATESR:Error While Inserting Record in Proc Emails Table ';
-- Update the Media Life Cycle for Auto Create SR
l_media_lc_rec.milcs_id:=l_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_activity_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- select result reason outcome for activity
select wu.outcome_id, wu.result_id, wu.reason_id INTO
l_activity_rec.outcome_id, l_activity_rec.result_id, l_activity_rec.reason_id
from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
where aa.action_id =65
and aa.action_item_id =45
and aa.default_wrap_id = wu.wrap_id;
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_activity_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Update the mail Processing Life Cycles
l_media_lc_rec.milcs_id:=l_mp_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- update the itneraction with result reason outcome
select wu.outcome_id, wu.result_id, wu.reason_id INTO
l_interaction_rec.outcome_id, l_interaction_rec.result_id, l_interaction_rec.reason_id
from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
where aa.action_id =65
and aa.action_item_id =45
and aa.default_wrap_id = wu.wrap_id;
select contact_party_id into l_cust_contact_id from jtf_ih_interactions
where interaction_id=l_interaction_id;
p_action=>'CLOSE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
if (fnd_profile.value_specific('IEM_SR_NOT_UPDATED'))='REDIRECT'
then
l_redirect_flag:='Y';
ELSE -- This is a update service request
IF (l_status_id is not null) and (l_sr_id is not null) then
IEM_EMAIL_PROC_PVT.IEM_SRSTATUS_UPDATE(p_sr_id=>l_sr_id ,
p_status_id=>l_status_id,
p_email_rec=>l_post_rec,
x_status =>l_stat,
x_out_text=>l_out_text) ;
-- Add a Activity for AUTO-UPDATE OF SR
l_activity_rec.start_date_time := SYSDATE;
l_activity_rec.action_id := 75; -- Auto Update Of SR
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_activity_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
--Update Intearctionwith result reason outcome
-- Create a Media Life Cycle for Auto update of SR
l_media_lc_rec.media_id :=l_media_id ;
l_media_lc_rec.milcs_type_id := 40; --EMAIL_AUTO_UPDATED_SR
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Update the Media Life Cycle for Auto Update of SR
l_media_lc_rec.milcs_id:=l_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
l_logmessage:='AUTOUPDSR:Error While Inserting Record in Proc Emails Table ';
l_noti_flag:=FND_PROFILE.VALUE_SPECIFIC('IEM_SR_UPDATE_NOTI');
-- update the itneraction with result reason outcome
select wu.outcome_id, wu.result_id, wu.reason_id INTO
l_interaction_rec.outcome_id, l_interaction_rec.result_id, l_interaction_rec.reason_id
from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
where aa.action_id =75
and aa.action_item_id =45
and aa.default_wrap_id = wu.wrap_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
l_outbox_tbl.delete;
-- Update the mail Processing Life Cycles
l_media_lc_rec.milcs_id:=l_mp_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
if (fnd_profile.value_specific('IEM_SR_NOT_UPDATED'))='REDIRECT'
then
l_redirect_flag:='Y';
END IF; -- if l_stat='S' from SR update api
END IF; -- elsif l_Action in ('AUTOCREATESR','AUTOUPDATESR');
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- set for autocreate/auto update SR fails for employee type
if l_redirect_flag='N' then
iem_rules_engine_pub.auto_process_email(p_api_version_number=>1.0,
p_commit=>FND_API.G_FALSE,
p_rule_type=>'AUTOREDIRECT',
p_keyvals_tbl=>l_class_val_tbl,
p_accountid=>l_post_rec.email_account_id,
x_result=>l_autoproc_result,
x_action=>l_action,
x_parameters=>l_param_rec_tbl,
x_return_status=>l_ret_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
else -- As auto redirect flag is set for autoupdate/create SR
l_autoproc_result:='T';
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Update the Media Life Cycle for Auto RRRR
l_media_lc_rec.milcs_id:=l_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
l_outbox_tbl.delete;
l_qual_tbl.delete;
delete from iem_email_classifications where message_id=l_post_rec.message_id;
update iem_rt_preproc_emails
set email_account_id=l_redirect_id,
msg_status='REDIRECT',
ih_media_item_id=l_media_id
where message_id=l_post_rec.message_id;
l_outbox_tbl.delete;
update iem_rt_proc_emails
set resource_id=l_agentid
where message_id=l_post_rec.message_id;
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Update the Media Life Cycle for Auto Routing
if l_stat='S' then
l_media_lc_rec.milcs_id:=l_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- In case of autoroute update the interaction with resource id of the agent to which
-- the message is autorouted to
l_interaction_rec.interaction_id:=l_interaction_id;
JTF_IH_PUB.Update_Interaction( p_api_version => 1.1,
p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_interaction_rec => l_interaction_rec
);
raise ERR_INSERTING;
-- Update the Media Life Cycle for Mail processing
l_media_lc_rec.milcs_id:=l_mp_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
delete from iem_rt_preproc_emails
where message_id=l_post_rec.message_id;
select count(*) into l_kb_rank
from iem_doc_usage_stats
where kb_doc_id=v_item.item_id;
p_doc_last_modified_date=>v_item.last_update_date,
p_score =>l_kb_rank,
p_url =>' ',
p_kb_delete=>'N',
p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_CREATION_DATE =>SYSDATE,
p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_LAST_UPDATE_DATE =>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_return_status=>l_ret_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
-- Update the Media Life Cycle for Mail processing -- no need to call wrapup
l_media_lc_rec.milcs_id:=l_mp_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
update iem_rt_preproc_emails
set creation_date=sysdate
where message_id=l_post_rec.message_id;
l_logmessage:='AUTOREPLY:Error While Inserting Record in Proc Emails Table ';
update iem_rt_preproc_emails
set creation_date=sysdate
where message_id=l_post_rec.message_id;
delete from iem_rt_preproc_emails where message_id=l_post_rec.message_id;
delete from iem_rt_preproc_emails
where message_id=l_post_rec.message_id;
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
WHEN STOP_REDIRECT_PROCESSING THEN -- Here record can not be deleted from preproc_emails table
if g_statement_log then
l_Error_Message := 'stop Further Processing';
update iem_rt_preproc_emails
set creation_date=sysdate
where message_id=l_post_rec.message_id;
WHEN ERR_INSERTING THEN
if g_exception_log then
l_logmessage := 'Unable To insert Record in Post MDT '||sqlerrm;
update iem_rt_preproc_emails
set creation_date=sysdate
where message_id=l_post_rec.message_id;
update iem_rt_preproc_emails
set creation_date=sysdate
where message_id=l_post_rec.message_id;
for x in ( select message_id
from iem_rt_preproc_emails
order by priority,creation_date)
LOOP
BEGIN
select * into x_msg_rec from iem_rt_preproc_emails
where message_id=x.message_id FOR UPDATE NOWAIT;
-- Selecting Auto Ack. Subject which will be appended to original mail
BEGIN
select meaning||': '
into l_ack_sub
from fnd_lookups
where lookup_type='IEM_AUTO_ACKNOWLEDGE'
and lookup_code='SUBJECT';
IEM_OUTBOX_PROC_PUB.insertDocument(
p_api_version_number=>1.0 ,
p_outbox_item_id=>l_outbox_id,
p_document_source=>'MES' ,
p_document_id =>p_document_id ,
X_RETURN_STATUS=>l_ret_status,
X_MSG_COUNT=>l_msg_count,
X_MSG_DATA=>l_msg_data);
x_out_text:='Failed in insert document '||l_text_data;
select value into l_outval
from iem_comp_rt_stats
where type='WORKFLOW' AND param=to_char(p_message_id);
select classification_id into l_class
from iem_email_classifications
where message_id=p_message_id
and score = (select max(score) from iem_email_classifications
where message_id=p_message_id)
and rownum=1;
SELECT TO_CHAR(iem.IEM_MAILPREPROCWF_S1.nextval)
INTO l_itemkey
FROM dual;
PROCEDURE IEM_SRSTATUS_UPDATE(p_sr_id in number,
p_status_id in number,
p_email_rec in iem_rt_preproc_emails%rowtype,
x_status out NOCOPY varchar2,
x_out_text out NOCOPY varchar2) IS
l_service_request_rec CS_ServiceRequest_PUB.service_request_rec_type;
-- code for auto sr update
x_status:='S';
-- select object version number
BEGIN
open c1 for
'select object_version_number,status_flag from cs_incidents_all_b where incident_id=:sr_id' using p_sr_id;
x_out_Text:='Oracle Error for SR# '||p_sr_id|| 'While selecting object version number '||sqlerrm;
IF l_status_flag<>'C' THEN -- SR is not closed so we can update
l_status_id:=p_status_id;
IEM_ServiceRequest_PVT.Update_Status_Wrap
( p_api_version =>2.0,
p_init_msg_list =>fnd_api.g_true,
p_commit => fnd_api.g_false,
p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
p_user_id =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
x_return_status =>l_ret_status,
x_msg_count=>l_msg_count,
x_msg_data =>l_msg_data,
p_request_id =>p_sr_id,
p_object_version_number =>l_object_version_number,
p_status_id=>l_status_id,
p_status=>l_sr_status,
x_interaction_id=>l_interaction_id);
x_out_text:='SR update Status Api fails for SR# '||p_sr_id;
x_out_text:='Successfully Update the SR '||p_sr_id;
x_out_text:='SR '||p_sr_id||' is Closed Hence Not updated ';
p_action in varchar2, -- ADD/UPDATE/CLOSE
p_interaction_rec IN JTF_IH_PUB.interaction_rec_type,
p_activity_rec IN JTF_IH_PUB.activity_rec_type,
p_media_lc_rec IN JTF_IH_PUB.media_lc_rec_type,
p_media_rec IN JTF_IH_PUB.media_rec_type,
x_id OUT NOCOPY NUMBER,
x_status out NOCOPY varchar2,
x_out_text out NOCOPY varchar2) IS
l_media_id number;
elsif p_action='UPDATE' THEN
JTF_IH_PUB.Update_MediaItem( 1.0,
'T',
'F',
TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
l_ret_status,
l_msg_count,
l_msg_data,
p_media_rec);
elsif p_action='UPDATE' THEN
JTF_IH_PUB.Update_MediaLifeCycle( 1.0,
'T',
'F',
TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
l_ret_status,
l_msg_count,
l_msg_data,
p_media_lc_rec);
ELSIF p_action='UPDATE' THEN
JTF_IH_PUB.Update_Interaction( p_api_version => 1.1,
p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_interaction_rec => p_interaction_rec
);
select wu.outcome_id, wu.result_id, wu.reason_id INTO
l_activity_rec.outcome_id, l_activity_rec.result_id, l_activity_rec.reason_id
from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
where aa.action_id =l_activity_rec.action_id
and aa.action_item_id = l_activity_rec.action_item_id
and aa.default_wrap_id = wu.wrap_id;
-- Update the mail Processing Life Cycles
l_media_lc_rec.milcs_id:=p_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
select wu.outcome_id, wu.result_id, wu.reason_id into
l_interaction_rec.outcome_id,
l_interaction_rec.result_id,
l_interaction_rec.reason_id
from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
where aa.action_id =p_action_id
and aa.action_item_id = 45
and aa.default_wrap_id = wu.wrap_id;
p_action=>'CLOSE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_interaction_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
p_action=>'CLOSE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_interaction_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
select * into l_header_rec from iem_ms_base_headers
where message_id=p_post_rec.message_id;
IEM_OUTBOX_PROC_PUB.insertDocument(
p_api_version_number=>1.0 ,
p_outbox_item_id=>l_outbox_id,
p_document_source=>'MES' ,
p_document_id =>p_doc_tbl(i).doc_id ,
X_RETURN_STATUS=>l_ret_status,
X_MSG_COUNT=>l_msg_count,
X_MSG_DATA=>l_msg_data);
x_out_text:='Error Encountered While Calling Insert Document ';
SELECT fl.file_name
INTO l_file_name
FROM jtf_amv_items_tl b ,jtf_amv_attachments a ,fnd_lobs fl
WHERE b.item_id = a.attachment_used_by_id
and a.attachment_used_by='ITEM'
AND a.file_id = fl.file_id
AND b.item_id=p_doc_tbl(i).doc_id
AND b.language=USERENV('LANG')
and rownum=1;
IEM_OUTBOX_PROC_PUB.insertDocument(
p_api_version_number=>1.0 ,
p_outbox_item_id=>l_outbox_id,
p_document_source=>'MES' ,
p_document_id =>l_notification_id ,
X_RETURN_STATUS=>l_ret_status,
X_MSG_COUNT=>l_msg_count,
X_MSG_DATA=>l_msg_data);
x_out_text:='Error Encountered While Calling Insert Document ';
IEM_OUTBOX_PROC_PUB.insertDocument(
p_api_version_number=>1.0 ,
p_outbox_item_id=>l_outbox_id,
p_document_source=>'MES' ,
p_document_id =>p_fwd_doc_id ,
X_RETURN_STATUS=>l_ret_status,
X_MSG_COUNT=>l_msg_count,
X_MSG_DATA=>l_msg_data);
x_out_text:='Error Encountered While Calling Insert Document during autoforward';
cursor c1 is select lookup_code,meaning
from fnd_lookups
where enabled_flag = 'Y'
AND NVL(start_date_active, SYSDATE) <= SYSDATE
AND NVL(end_date_active,SYSDATE) >= SYSDATE
AND lookup_type ='IEM_MERGE_FIELDS'
ANd lookup_code like 'ACK%';
select from_name,reply_to_address
INTO l_from_name,l_reply_address
from IEM_MSTEMAIL_ACCOUNTS
where email_account_id=p_email_account_id;
x_qual_tbl.delete;
SELECT a.intent_id, a.keyword,a.weight
from iem_intent_dtls a,iem_account_intents b
WHERE b.email_account_id=l_email_account_id
AND a.intent_id=b.intent_id
AND QUERY_RESPONSE='Q'
and weight>0
order by 1;
SELECT keyword,weight FROM iem_intent_dtls
where intent_id=l_class_id
AND QUERY_RESPONSE='Q'
and weight>0
order by 2 desc;
select kem_flag,account_language
INTO l_theme_code,l_lang
FROM IEM_MSTEMAIL_ACCOUNTS
WHERE EMAIL_ACCOUNT_ID=l_email_account_id;
l_theme_buf.delete;
l_token_buf.delete;
FOR x in (select * from the(select cast(l_data as iem_class_tbl_typ)
from dual)a where score>0 and rownum<8 order by score desc)
LOOP
l_wtot:=l_wtot+x.score;
FOR x in (select * from the(select cast(l_data as iem_class_tbl_typ)
from dual)a where score>0 and rownum<8 order by score desc)
LOOP
x.score:=x.score*100/l_wtot;
select intent into g_topclass
from iem_intents
where intent_id=l_gclassid;
p_LAST_UPDATED_BY=>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ,
p_LAST_UPDATE_DATE=>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ,
x_return_Status=>l_status,
x_msg_count=>l_count,
x_msg_data=>l_msg_data);
cursor c_intent is select a.intent,b.score from
iem_intents a,iem_email_classifications b
where b.message_id=l_message_id
and a.intent_id=b.classification_id;
select classification_id from iem_email_classifications
where message_id=l_message_id
order by score desc;
cursor c_item is select ib.item_id,ib.item_name,ib.last_update_date
from amv_c_chl_item_match cim,jtf_amv_items_vl ib
where cim.channel_category_id = l_cm_cat_id
and cim.channel_id is null
and cim.approval_status_type ='APPROVED'
and cim.table_name_code ='ITEM'
and cim.available_for_channel_date <= sysdate
and cim.item_id = ib.item_id
and nvl(ib.effective_start_date, sysdate) <= sysdate + 1
and nvl(ib.expiration_date, sysdate) >= sysdate;
select * into l_mail_rec from iem_rt_proc_emails
where ih_media_item_id=p_media_id and msg_status='AUTOREPLY';
l_logmessage:='Error while selecting Message For re-processing '||sqlerrm;
select * into l_header_Rec
from iem_ms_base_headers
where message_id=l_mail_Rec.message_id;
select user_name,email_address
into l_email_user_name,l_email_address
from iem_mstemail_accounts
where email_account_id=l_mail_rec.email_account_id;
l_class_val_tbl.delete;
l_tag_keyval.delete;
select name
into l_folder_name
from iem_route_classifications
where route_classification_id=l_mail_Rec.rt_classification_id;
update iem_rt_proc_emails
set resource_id=0,
group_id=l_group_id,
customer_id=p_customer_id,
contact_id=p_contact_id,
relationship_id=p_relationship_id,
msg_status=null,
mail_proc_status='P',
category_map_id=l_cm_cat_id
where message_id=l_mail_rec.message_id;
p_action=>'ADD' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- Update the Media Life Cycle for Auto Routing
l_media_lc_rec.milcs_id:=l_milcs_id;
p_action=>'UPDATE' , -- ADD/UPDATE/CLOSE
p_interaction_rec=>l_interaction_rec,
p_activity_rec=>l_activity_rec ,
p_media_lc_rec=>l_media_lc_Rec ,
p_media_rec=>l_media_rec ,
x_id=>l_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
-- In case of autoroute update the interaction with resource id of the agent to which
-- the message is autorouted to
l_interaction_rec.interaction_id:=p_interaction_id;
JTF_IH_PUB.Update_Interaction( p_api_version => 1.1,
p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
x_return_status => l_ret_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_interaction_rec => l_interaction_rec
);
select count(*) into l_kb_rank
from iem_doc_usage_stats
where kb_doc_id=v_item.item_id;
p_doc_last_modified_date=>v_item.last_update_date,
p_score =>l_kb_rank,
p_url =>' ',
p_kb_delete=>'N',
p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_CREATION_DATE =>SYSDATE,
p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_LAST_UPDATE_DATE =>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_return_status=>l_ret_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
cursor c1 is select category_id from iem_account_categories
where email_account_id=l_email_account_id;
select keyword,weight from iem_intent_dtls where intent_id=l_classification_id
and query_response='R';
select document_id,KB_REPOSITORY_NAME,score,kb_result_id from iem_kb_results
where message_id=l_msg_id
order by 2,1,score asc;
select count(*) into l_cnt from iem_kb_results
where message_id=l_msg_id
and classification_id=l_classification_id
and email_account_id=l_email_account_id;
select classification_string into l_imt_string
from IEM_EMAIL_CLASSIFICATIONS
WHERE MESSAGE_ID=l_msg_id AND EMAIL_ACCOUNT_ID=l_email_account_id
and classification_id=l_classification_id;
p_updated_in_days => l_days,
p_check_login_user => FND_API.G_FALSE,
p_application_id => G_APP_ID,
p_area_array => l_area_array,
p_content_array => l_content_array,
p_param_array => l_param_array,
p_user_id => l_user_id,
p_category_id => l_category_id,
p_include_subcats => FND_API.G_FALSE,
p_external_contents => FND_API.G_TRUE,
p_rows_requested_tbl => l_rows_req,
p_start_row_pos_tbl => l_start_row,
p_get_total_cnt_flag => 'T',
x_rows_returned_tbl => l_rows_returned,
x_next_row_pos_tbl => l_next_row_pos,
x_total_row_cnt_tbl => l_total_row_cnt,
x_result_array => l_result_array);
p_doc_last_modified_date=>l_result_array(l_count).last_update_date,
p_score =>to_char(l_result_array(l_count).score),
p_url =>l_result_array(l_count).url_string,
p_kb_delete=>'N',
p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_CREATION_DATE =>SYSDATE,
p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_LAST_UPDATE_DATE =>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
p_updated_in_days => l_days,
p_check_login_user => FND_API.G_FALSE,
p_application_id => G_APP_ID,
p_area_array => l_area_array,
p_content_array => l_content_array,
p_param_array => l_param_array,
p_user_id => l_user_id,
p_category_id => l_category_id,
p_include_subcats => FND_API.G_FALSE,
p_external_contents => FND_API.G_TRUE,
p_rows_requested_tbl => l_rows_req,
p_start_row_pos_tbl => l_start_row,
p_get_total_cnt_flag => 'T',
x_rows_returned_tbl => l_rows_returned,
x_next_row_pos_tbl => l_next_row_pos,
x_total_row_cnt_tbl => l_total_row_cnt,
x_result_array => l_result_array);
p_doc_last_modified_date=>l_result_array(l_count).last_update_date,
p_score =>to_char(l_result_array(l_count).score),
p_url =>l_result_array(l_count).url_string,
p_kb_delete=>'N',
p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_CREATION_DATE =>SYSDATE,
p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_LAST_UPDATE_DATE =>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
p_updated_in_days => l_days,
p_check_login_user => FND_API.G_FALSE,
p_application_id => G_APP_ID,
p_area_array => l_area_array,
p_content_array => l_content_array,
p_param_array => l_param_array,
p_user_id => l_user_id,
p_category_id => l_category_id,
p_include_subcats => FND_API.G_TRUE,
p_external_contents => FND_API.G_TRUE,
p_rows_requested_tbl => l_rows_req,
p_start_row_pos_tbl => l_start_row,
p_get_total_cnt_flag => 'T',
x_rows_returned_tbl => l_rows_returned,
x_next_row_pos_tbl => l_next_row_pos,
x_total_row_cnt_tbl => l_total_row_cnt,
x_result_array => l_result_array);
p_doc_last_modified_date=>l_result_array(l_count).last_update_date,
p_score =>to_char(l_result_array(l_count).score),
p_url =>l_result_array(l_count).url_string,
p_kb_delete=>'N',
p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_CREATION_DATE =>SYSDATE,
p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_LAST_UPDATE_DATE =>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
p_updated_in_days => l_days,
p_check_login_user => FND_API.G_FALSE,
p_application_id => G_APP_ID,
p_area_array => l_area_array,
p_content_array => l_content_array,
p_param_array => l_param_array,
p_user_id => l_user_id,
p_category_id => l_category_id,
p_include_subcats => FND_API.G_FALSE,
p_external_contents => FND_API.G_TRUE,
p_rows_requested_tbl => l_rows_req,
p_start_row_pos_tbl => l_start_row,
p_get_total_cnt_flag => 'T',
x_rows_returned_tbl => l_rows_returned,
x_next_row_pos_tbl => l_next_row_pos,
x_total_row_cnt_tbl => l_total_row_cnt,
x_result_array => l_result_array);
p_doc_last_modified_date=>l_result_array(l_count).last_update_date,
p_score =>to_char(l_result_array(l_count).score),
p_url =>l_result_array(l_count).url_string,
p_kb_delete=>'N',
p_CREATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_CREATION_DATE =>SYSDATE,
p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_LAST_UPDATE_DATE =>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_return_status=>l_return_status,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data);
delete from iem_kb_results where message_id=l_msg_id and kb_result_id=v_doc.kb_result_id;