[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
order by b.score desc;
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;
select value into l_thread_id
from IEM_ENCRYPTED_TAG_DTLS
where key ='IEMNTHREADID' and encrypted_id = substr(l_encrypted_id,1,5);
select agent_id into l_agentid from
IEM_ENCRYPTED_TAGS where encrypted_id = substr(l_encrypted_id,1,5);
l_logmessage:=l_logmessage || '-error while selecting '||sqlerrm;
fix for bug 13797286 . Don't insert into IEM_THREAD_DTLS if the message is redirected or rerouted.
*/
IF l_thread_id IS NOT NULL AND nvl(l_post_rec.msg_status,'NEW') not in
('REDIRECT','REROUTE') THEN
if g_statement_log then
l_logmessage:='start insertion to IEM_THREAD_DTLS';
insert into IEM_THREAD_DTLS(
THREAD_ID,
MESSAGE_ID,
MESSAGE_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values(l_thread_id, l_post_rec.message_id,'I',l_agentid,sysdate, l_agentid,sysdate,l_agentid);
Select person_id into l_contact_id
from per_workforce_current_x
Where upper(email_address)=upper(l_sender);
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;
l_logmessage:='Before calling update IEM_EMAIL_PROC_PVT.IEM_PROC_IH media';
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 );
l_logmessage:='After calling IEM_EMAIL_PROC_PVT.IEM_PROC_IH media update: status : '||l_stat || 'text : '|| l_out_text || 'l_media_id : ' ||l_media_id;
l_logmessage:='Before calling update IEM_EMAIL_PROC_PVT.IEM_PROC_IH MLCS add';
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:='After calling update IEM_EMAIL_PROC_PVT.IEM_PROC_IH MLCS add :status : '||l_stat ||' text : '||l_out_text||' l_milcs_id :'||l_milcs_id;
-- 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 );
l_logmessage:='After calling update IEM_EMAIL_PROC_PVT.IEM_PROC_IH MLCS add :status : '||l_stat ||' text : '||l_out_text||' l_milcs_id :'||l_milcs_id;
l_logmessage:='Before calling update IEM_EMAIL_PROC_PVT.IEM_PROC_IH MLCS update';
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 );
l_logmessage:='After calling update IEM_EMAIL_PROC_PVT.IEM_PROC_IH MLCS update :status : '||l_stat ||' text : '||l_out_text||' l_milcs_id :'||l_milcs_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 );
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;
l_logmessage:='Calling update MLCS IEM_EMAIL_PROC_PVT.IEM_PROC_IH for milcs id 31 ';
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:='After update MLCS IEM_EMAIL_PROC_PVT.IEM_PROC_IH for milcs id 31 staus : '||l_stat||' text : '||l_out_text||' l_milcs_id : '||l_milcs_id;
l_logmessage:='Calling IEM_RT_PROC_EMAILS_PVT.create_item after update mlcs 31';
l_logmessage:='After update MLCS IEM_EMAIL_PROC_PVT.IEM_PROC_IH for milcs id 31 staus : '||l_stat||' text : '||l_out_text||' l_milcs_id : '||l_milcs_id;
l_logmessage:='EXECPROC:Error While Inserting Record in Proc Emails Table ';
-- Add a Activity for AUTO-UPDATE OF SR if the incoming email contain reference to SR
if l_sr_id is not null then
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 );
l_logmessage:='Calling IEM_EMAIL_PROC_PVT.IEM_WRAPUP after update mlcs 31';
l_logmessage:='End of IEM_EMAIL_PROC_PVT.IEM_WRAPUP after update mlcs 31 status : '||l_stat ||' data: '||l_out_text;
-- 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
-- Added NVL condition for bug 11796985
l_status_id:=to_number(NVL(l_param_rec_tbl(l_param_index).parameter1,'0'));
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');
l_logmessage:='calling IEM_EMAIL_PROC_PVT.IEM_PROC_IH add activity for auto update 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_milcs_id,
x_status=>l_stat ,
x_out_text=>l_out_text );
l_logmessage:='after IEM_EMAIL_PROC_PVT.IEM_PROC_IH add activity for auto update sr : status : '||l_stat||' text :'||l_out_text||' act id : '||l_activity_id;
l_logmessage:='calling IEM_RT_PROC_EMAILS_PVT.create_item after auto update sr';
l_logmessage:='after IEM_RT_PROC_EMAILS_PVT.create_item after auto update sr : status :'||l_ret_status ||' data : '||l_msg_data;
l_logmessage:='AUTOCREATESR:Error While Inserting Record in Proc Emails Table '||'sqlerrm:'||sqlerrm;
-- Update the Media Life Cycle for Auto Create SR
l_media_lc_rec.milcs_id:=l_milcs_id;
l_logmessage:='update interaction for auto update sr';
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:='END IEM_EMAIL_PROC_PVT.IEM_PROC_IH update interaction for auto update sr : status : '||l_stat||' text :'||l_out_text|| ' inter id : '||l_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 );
-- 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;
select incident_number into l_activity_rec.doc_source_object_name
from cs_incidents_all_b where incident_id=l_sr_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 );
l_logmessage:='calling iem_email_proc_pvt.IEM_AUTOREPLY for auot update sr';
l_logmessage:='END iem_email_proc_pvt.IEM_AUTOREPLY for auot update sr : status : '||l_stat ||' data : '||l_out_text;
l_logmessage:='calling IEM_EMAIL_PROC_PVT.IEM_WRAPUP for resolve on auto update sr';
l_logmessage:='calling IEM_EMAIL_PROC_PVT.IEM_WRAPUP for resolve on auto update sr status : '||l_stat||' data :'||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
-- If l_status id is 0 which means user do not want to update status
-- so do not call the update status API
If l_status_id=0 then
l_stat:='S';
l_logmessage:='calling IEM_EMAIL_PROC_PVT.IEM_SRSTATUS_UPDATE for auto update sr';
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) ;
l_logmessage:='after IEM_EMAIL_PROC_PVT.IEM_SRSTATUS_UPDATE for auto update l_out_textsr : status : '||l_stat||' 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
l_logmessage:='calling IEM_EMAIL_PROC_PVT.IEM_PROC_IH update MLCS for auto ack';
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 );
l_logmessage:='END IEM_EMAIL_PROC_PVT.IEM_PROC_IH update MLCS for auto ack : status : '||l_stat||' txt : '||l_out_text||' mlcs id : '||l_milcs_id;
-- 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;
-- Fix for bug 14010115 ,update ih_interaction_id also with latest interaction_id in case new interaction is created.
-- else, row will be updated with old interaction id, in case of auto-redirect rule.
update iem_rt_preproc_emails
set email_account_id=l_redirect_id,
ih_interaction_id=l_interaction_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;
l_logmessage:='calling IEM_EMAIL_PROC_PVT.IEM_PROC_IH update MLCS for email inbound auto route ';
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:='end IEM_EMAIL_PROC_PVT.IEM_PROC_IH update MLCS for email inbound auto route status : '||l_stat||' text : '||l_out_text||' id : '||l_milcs_id;
-- 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;
l_logmessage:='calling JTF_IH_PUB.Update_Interaction ';
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
);
l_logmessage:='end JTF_IH_PUB.Update_Interaction status'||l_ret_status || ' txt : '||l_msg_data;
raise ERR_INSERTING;
-- Update the Media Life Cycle for Mail processing
l_media_lc_rec.milcs_id:=l_mp_milcs_id;
l_logmessage:='calling IEM_EMAIL_PROC_PVT.IEM_PROC_IH update MLCS after JTF call ';
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:='end IEM_EMAIL_PROC_PVT.IEM_PROC_IH update MLCS after JTF call : status : '||l_stat||' txt : '||l_out_text||' id : '||l_milcs_id;
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;
l_logmessage:=' Calling iem_email_proc_pvt.IEM_PROC_IH update MLCS in STOP_AUTO_PROCESS';
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:=' End iem_email_proc_pvt.IEM_PROC_IH update MLCS in STOP_AUTO_PROCESS :status : '||l_stat||' txt :'||l_out_text||' id : '||l_milcs_id;
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 );
l_logmessage:='call to IEM_EMAIL_PROC_PVT.IEM_PROC_IH update MLCS in else statement of stop_processing';
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;
Select parser_id, start_tag, end_tag, column_name, rank
from iem_parser_dtls
where parser_id = l_parser_id
and UPPER(column_name) IN ('ACCOUNT_NUMBER','CUSTOMER_NUMBER','CUSTOMER_NAME',
'CUSTOMER_PHONE','CUSTOMER_EMAIL','INSTANCE_NUMBER',
'INSTANCE_SERIAL_NUMBER','INCIDENT_SITE_NUMBER','CONTACT_NUMBER',
'CONTACT_NAME','CONTACT_PHONE','CONTACT_EMAIL')
order by rank asc;
Select parser_id, start_tag, end_tag, column_name, rank
from iem_parser_dtls
where parser_id = l_parser_id
and UPPER(column_name) IN ('ADDRESSEE', 'EXTERNAL_REFERENCE', 'INCIDENT_ADDRESS1', 'INCIDENT_ADDRESS2',
'INCIDENT_ADDRESS3', 'INCIDENT_ADDRESS4', 'INCIDENT_CITY', 'INCIDENT_COUNTRY',
'INCIDENT_COUNTY', 'INCIDENT_POSTAL_CODE', 'INCIDENT_PROVINCE', 'INCIDENT_STATE',
'PROBLEM_CODE', 'SERVICE_REQUEST_TYPE', 'SITE_NAME', 'URGENCY', 'INVENTORY_ITEM_NAME')
order by rank asc;
SELECT party_type
FROM hz_parties a
WHERE a.party_id = g_customer_id
AND a.status = 'A'
AND a.party_type IN ('ORGANIZATION','PERSON');
FOR contact_rec in (Select start_tag, end_tag, column_name, rank
from iem_parser_dtls
where parser_id = p_parser_id
and UPPER(column_name) IN ('CONTACT_PHONE','CONTACT_EMAIL')
order by rank asc)
LOOP
IF (contact_rec.column_name = 'CONTACT_PHONE') THEN
BEGIN
l_contact_phone:= get_tag_data(contact_rec.start_tag, contact_rec.end_tag, p_message_id);
Select a.person_id, b.phone_id, 'PHONE','EMPLOYEE'
into g_contact_party_id,g_contact_point_id, g_contact_point_type, g_contact_party_type
from per_workforce_x a,per_phones b
where a.person_id = b.parent_id
and REGEXP_REPLACE(b.phone_number,'([[:punct:]|[:space:]]*)') = l_contact_phone;
Select a.person_id, 'EMAIL','EMPLOYEE'
into g_contact_party_id, g_contact_point_type , g_contact_party_type
from per_workforce_current_x a
where UPPER(a.email_Address) = upper(l_contact_email);
select party_type into g_contact_party_type
from hz_parties
where party_id = p_contact_id
and rownum = 1;
select cust_account_id into l_cust_account_id
from hz_cust_accounts
where party_id =g_customer_id;
SELECT distinct --c.concatenated_segments,
--cs.category_set_name,
--cs.description cat_set_description,
--t.description cat_description,
c.category_id into l_category_Id
-- ic.category_set_id --Category_Set_Id
FROM mtl_category_set_valid_cats ic,
mtl_categories_kfv c,
mtl_category_sets_vl cs,
mtl_item_categories itc,
mtl_categories_tl t
WHERE ic.category_id = c.category_id
AND t.category_id (+) = c.category_id
AND t.language (+) = userenv('LANG')
AND ic.category_set_id = cs.category_set_id
AND ic.category_set_id = fnd_profile.value('CS_SR_DEFAULT_CATEGORY_SET')
AND itc.inventory_item_id = l_inventory_item_id
AND itc.category_id = c.category_id
AND itc.category_set_id = ic.category_set_id
AND itc.organization_id = cs_std.Get_Item_Valdn_Orgzn_Id
AND sysdate <= NVL(c.disable_date,sysdate);
l_logmessage:='INSERTED REQUEST ID : ' || l_sr_create_out_rec.request_id ;
l_logmessage:='INSERTED REQUEST NUM : ' || l_sr_create_out_rec.request_number;
select party_id into x_customer_id
from hz_parties where party_number = p_customer_number;
select party_id into x_customer_id
from hz_parties where UPPER(party_name) = UPPER(p_customer_name);
select party_id into x_customer_id
from hz_cust_accounts
where account_number=p_account_number;
Select owner_table_id into x_customer_id
From hz_contact_points
where owner_table_name='HZ_PARTIES'
and contact_point_type = 'PHONE'
and phone_number =p_customer_phone;
Select owner_table_id into x_customer_id
from hz_contact_points a, hz_parties b
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type='PHONE'
and a.status='A'
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and a.owner_table_id = b.party_id
and b.party_type in ('PERSON', 'ORGANIZATION')
and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_customer_phone,'([[:punct:]|[:space:]]*)');
Select owner_table_id into x_customer_id
From hz_contact_points
where owner_table_name='HZ_PARTIES'
and contact_point_type = 'EMAIL'
and upper(email_address) =upper(p_customer_email);
Select a.owner_table_id into x_customer_id
from hz_contact_points a, hz_parties b
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type = 'EMAIL'
and a.status='A'
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and a.owner_table_id = b.party_id
and b.party_type in ('PERSON', 'ORGANIZATION')
and upper(a.email_address) =upper(p_customer_email);
select distinct owner_party_id
into x_customer_id
from csi_item_instances
where instance_number = p_instance_number;
select distinct owner_party_id
into x_customer_id
from csi_item_instances
where serial_number = p_instance_serial_number;
select party_id into x_customer_id
from hz_party_sites
where party_site_number = p_incident_site_number;
Select distinct b.subject_id
into g_customer_id
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.status='A'
and a.primary_flag = 'Y'
and a.owner_table_id = b.party_id
and directional_flag = 'B'
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.object_id = c.party_id
and c.party_number = p_contact_number;
select upper(party_type) into l_party_type
from hz_parties
where party_number = p_contact_number;
select distinct a.subject_id
into g_customer_id
from hz_relationships a, hz_parties b
where a.party_id = b.party_id
and directional_flag = 'B'
and b.party_number = p_contact_number;
select distinct a.subject_id
into g_customer_id
from hz_relationships a, hz_parties b
where a.object_id = b.party_id
and directional_flag = 'B'
and b.party_number = p_contact_number;
select party_id into g_customer_id
from hz_parties
where party_number = p_contact_number;
select distinct a.subject_id
into g_customer_id
from hz_relationships a, hz_parties b
where a.object_id = b.party_id
and directional_flag = 'B'
and upper(b.party_name) = upper(p_contact_name);
select distinct b.object_id into g_customer_id
from hz_parties a, hz_party_relationships b, hz_contact_points c
where c.phone_number = p_contact_phone
where and reverse(c.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
and a.party_id = b.subject_id
and c.owner_table_id = b.party_id
and c.contact_point_type = 'PHONE'
and c.owner_table_name = 'HZ_PARTIES';
Select distinct b.subject_id into g_customer_id
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type='PHONE'
and a.status='A'
and a.primary_flag = 'Y'
and a.owner_table_id = b.party_id
and directional_flag = 'B'
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.object_id = c.party_id
and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)');
select distinct b.object_id into g_customer_id
from hz_parties a, hz_party_relationships b, hz_contact_points c
where upper(c.email_address) =upper(p_contact_email)
and a.party_id = b.subject_id
and c.owner_table_id = b.party_id
and c.contact_point_type = 'EMAIL'
and c.owner_table_name = 'HZ_PARTIES';
Select distinct b.subject_id into g_customer_id
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type='EMAIL'
and a.status='A'
and a.primary_flag = 'Y'
and a.owner_table_id = b.party_id
and directional_flag = 'B'
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.object_id = c.party_id
and upper(a.email_address) =upper(p_contact_email);
Select cust_account_id into x_cust_account_id
from hz_cust_accounts
where party_id=g_customer_id
and account_number=p_account_number
and status = 'A';
Select cust_account_id into x_cust_account_id
from hz_cust_accounts
where account_number=p_account_number
and status = 'A';
Select contact_point_id into x_customer_phone_id
From hz_contact_points
where contact_point_type = 'PHONE'
--and phone_number = p_customer_phone
and reverse(transposed_phone_number)=REGEXP_REPLACE(p_customer_phone,'([[:punct:]|[:space:]]*)')
and owner_table_id = g_customer_id;
Select contact_point_id into x_customer_phone_id
From hz_contact_points
where owner_table_name='HZ_PARTIES'
and contact_point_type = 'PHONE'
and reverse(transposed_phone_number)=REGEXP_REPLACE(p_customer_phone,'([[:punct:]|[:space:]]*)');
Select contact_point_id into x_customer_email_id
From hz_contact_points
where contact_point_type = 'EMAIL'
and upper(email_address) =upper(p_customer_email)
and owner_table_id = g_customer_id;
Select contact_point_id into x_customer_email_id
From hz_contact_points
where owner_table_name='HZ_PARTIES'
and contact_point_type = 'EMAIL'
and upper(email_address) =upper(p_customer_email);
select instance_id,inventory_item_id, inv_master_organization_id
into x_customer_product_id, x_inventory_item_id, x_inventory_org_id
from csi_item_instances
where instance_number = p_instance_number
and owner_party_account_id = p_cust_account_id
and owner_party_id = g_customer_id;
select instance_id,inventory_item_id, inv_master_organization_id
into x_customer_product_id, x_inventory_item_id, x_inventory_org_id
from csi_item_instances
where instance_number = p_instance_number
and owner_party_id = g_customer_id;
select instance_id,inventory_item_id, inv_master_organization_id
into x_customer_product_id, x_inventory_item_id, x_inventory_org_id
from csi_item_instances
where instance_number = p_instance_number
and owner_party_account_id = p_cust_account_id;
select instance_id,inventory_item_id, inv_master_organization_id
into x_customer_product_id, x_inventory_item_id, x_inventory_org_id
from csi_item_instances
where instance_number = p_instance_number;
select instance_id,inventory_item_id,inv_master_organization_id
into x_customer_product_id, x_inventory_item_id, x_inventory_org_id
from csi_item_instances
where serial_number = p_instance_serial_number
and owner_party_account_id = p_cust_account_id
and owner_party_id = g_customer_id;
select instance_id,inventory_item_id,inv_master_organization_id
into x_customer_product_id, x_inventory_item_id, x_inventory_org_id
from csi_item_instances
where serial_number = p_instance_serial_number
and owner_party_id = g_customer_id;
select instance_id,inventory_item_id, inv_master_organization_id
into x_customer_product_id, x_inventory_item_id, x_inventory_org_id
from csi_item_instances
where serial_number = p_instance_serial_number
and owner_party_account_id = p_cust_account_id;
select instance_id,inventory_item_id, inv_master_organization_id
into x_customer_product_id, x_inventory_item_id, x_inventory_org_id
from csi_item_instances
where serial_number = p_instance_serial_number;
select party_site_id into x_incident_location_id
from hz_party_sites
where party_site_number = p_incident_site_number
and party_id = g_customer_id;
select party_site_id into x_incident_location_id
from hz_party_sites
where party_site_number = p_incident_site_number;
select upper(party_type) into l_party_type
from hz_parties
where party_number = p_contact_number;
FOR contact_rec in (Select column_name, rank
from iem_parser_dtls
where parser_id = p_parser_id
and UPPER(column_name) IN ('CONTACT_PHONE','CONTACT_EMAIL')
order by rank asc)
LOOP
--only go throug the processing if the value has not been found
--in the last itiration there fore checking the x_contact_party_id value
--we are also looking for contact_phone and contact_email because its very rare to get a
--single hit from just from the contact_number.
IF x_contact_party_id IS NULL THEN
IF ((contact_rec.column_name = 'CONTACT_PHONE') AND (p_contact_phone is not null)) THEN
BEGIN
IF (l_party_type = 'PERSON') THEN
l_logmessage:='contact_number->contact_phone->type:person processing';
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type='PHONE'
and a.status='A'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
--and c.party_number =p_contact_number
and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
and b.subject_id = g_customer_id;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type='PHONE'
and a.status='A'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
--and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
and c.party_number =p_contact_number
and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
and b.subject_id = g_customer_id;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type = 'EMAIL'
and a.status='A'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
--and c.party_number =p_contact_number
and upper(c.email_address) = upper(p_contact_email)
and b.subject_id = g_customer_id;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type = 'EMAIL'
and a.status='A'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
--and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
and c.party_number =p_contact_number
and upper(c.email_address) = upper(p_contact_email)
and b.subject_id = g_customer_id;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.status='A'
and a.primary_flag = 'Y'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
-- and c.party_number =p_contact_number
and b.subject_id = g_customer_id
and rownum = 1;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.status='A'
and a.primary_flag = 'Y'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
--and b.object_id in (select party_id from hz_parties where party_number = p_contact_number)
and c.party_number =p_contact_number
and b.subject_id = g_customer_id
and rownum = 1;
FOR contact_rec in (Select column_name, rank
from iem_parser_dtls
where parser_id = p_parser_id
and UPPER(column_name) IN ('CONTACT_PHONE','CONTACT_EMAIL')
order by rank asc)
LOOP
--only go throug the processing if the value has not been found
--in the last itiration there fore checking the x_contact_party_id value
IF (x_contact_party_id) IS NULL THEN
IF (contact_rec.column_name = 'CONTACT_PHONE') THEN
BEGIN
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type='PHONE'
and a.status='A'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))
--and upper(c.party_name) =upper(p_contact_name)
and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
and b.subject_id = g_customer_id;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type = 'EMAIL'
and a.status='A'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
-- and upper(c.party_name) =upper(p_contact_name)
and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))
and upper(c.email_address) = upper(p_contact_email)
and b.subject_id = g_customer_id;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.status='A'
and a.primary_flag = 'Y'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
and b.object_id in (select party_id from hz_parties where upper(party_name) =upper(p_contact_name))
--and upper(c.party_name) =upper(p_contact_name)
and b.subject_id = g_customer_id;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type='PHONE'
and a.status='A'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
and reverse(a.transposed_phone_number)=REGEXP_REPLACE(p_contact_phone,'([[:punct:]|[:space:]]*)')
and b.subject_id = g_customer_id;
Select c.party_id, c.party_type, a.contact_point_id, a.contact_point_type
into x_contact_party_id, x_contact_type, x_contact_point_id, x_contact_point_type
from hz_contact_points a,hz_relationships b, hz_parties c
where a.owner_table_name='HZ_PARTIES'
and a.contact_point_type='EMAIL'
and a.status='A'
and a.owner_table_id = b.party_id
and a.contact_point_id not in (select contact_level_table_id from HZ_CONTACT_PREFERENCES
where contact_level_table='HZ_CONTACT_POINTS' and status='A')
and b.party_id = c.party_id
and upper(a.email_address)=upper(p_contact_email)
and b.subject_id = g_customer_id;
select DISTINCT inventory_item_id, organization_id
into x_inventory_item_id, x_inventory_org_id
from mtl_system_items_b
where organization_id=FND_PROFILE.value('CS_INV_VALIDATION_ORG') and
upper(segment1)= upper(p_inventory_item_name);
select count(problem_code) into l_code_count
from cs_sr_prob_code_mapping_detail
where problem_code = p_problem_code;
select party_site_id into x_party_site_id
from hz_party_sites
where upper(party_site_name) = upper(p_site_name)
and party_id = g_customer_id;
select count(external_reference) into l_ext_ref_count
from csi_item_instances
where upper(external_reference) = upper(p_ext_ref);
select cust_account_id into x_account_id
from hz_cust_accounts
where party_id = p_customer_id
AND status = 'A';
SELECT s.party_site_id into x_bill_to_site_id
FROM hz_party_sites s,
hz_parties p,
hz_locations l,
hz_party_site_uses u
WHERE p.party_id = p_customer_id
AND p.party_id = s.party_id
AND s.status = 'A'
AND s.location_id = l.location_id
AND s.party_site_id = u.party_site_id
AND u.site_use_type = 'BILL_TO'
AND u.primary_per_type = 'Y'
AND u.status = 'A';
SELECT s.party_site_id into x_ship_to_site_id
FROM hz_party_sites s,
hz_parties p,
hz_locations l,
hz_party_site_uses u
WHERE p.party_id = p_customer_id
AND p.party_id = s.party_id
AND s.status = 'A'
AND s.location_id = l.location_id
AND s.party_site_id = u.party_site_id
AND u.site_use_type = 'SHIP_TO'
AND u.primary_per_type = 'Y'
AND u.status = 'A';
select upper(value) email
from IEM_MS_MSGBODYS
where message_id = l_message_id
order by order_id asc;