The following lines contain the word 'select', 'insert', 'update' or 'delete':
update ecx_tp_headers
set party_id = p_to_fk_id,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login
where party_id = p_from_fk_id
and party_type in ('C', 'E', 'CARRIER');
update ecx_doclogs
set partyid = to_char(p_to_fk_id)
where partyid = to_char(p_from_fk_id)
and party_type in ('C', 'E', 'CARRIER');
update ecx_outbound_logs
set party_id = to_char(p_to_fk_id)
where party_id = to_char(p_from_fk_id)
and party_type in ('C', 'E', 'CARRIER');
select count(1), max(tp_header_id)--though there will be a single tp_header_id for
into l_exists, v_target_header_id--a party_site_id, max is used to retrieve tp_header_id
from ecx_tp_headers --in the same query if it exists.
where party_site_id =p_to_fk_id
and party_type in ('C', 'E', 'CARRIER') ;
select count(1)
into l_exists
from ecx_tp_headers
where party_site_id =p_from_fk_id
and party_type in ('C', 'E', 'CARRIER') ;
select etd.ext_process_id,
etd.source_tp_location_code bulk collect
into v_tp_details_target_tab
from ecx_tp_headers eth,
ecx_tp_details etd
where eth.tp_header_id= etd.tp_header_id
and eth.party_site_id =p_to_fk_id;
select etd.ext_process_id,
etd.source_tp_location_code bulk collect
into v_tp_details_source_tab
from ecx_tp_headers eth,
ecx_tp_details etd
where eth.tp_header_id= etd.tp_header_id
and eth.party_site_id =p_from_fk_id;
update ecx_tp_details
set tp_header_id = v_target_header_id
where tp_header_id in
(select tp_header_id
from ecx_tp_headers
where party_site_id = p_from_fk_id
and party_type in ('C', 'E', 'CARRIER')
);
delete
from ecx_tp_headers
where party_site_id = p_from_fk_id
and party_type in ('C', 'E', 'CARRIER');
update ecx_tp_headers
set party_site_id = p_to_fk_id,
last_updated_by = hz_utility_pub.user_id,
last_update_login = hz_utility_pub.last_update_login
where party_site_id = p_from_fk_id
and party_type in ('C', 'E', 'CARRIER');
update ecx_doclogs
set party_site_id = to_char(p_to_fk_id)
where party_site_id = to_char(p_from_fk_id)
and party_type in ('C', 'E', 'CARRIER');
update ecx_outbound_logs
set party_site_id = to_char(p_to_fk_id)
where party_site_id = to_char(p_from_fk_id)
and party_type in ('C', 'E', 'CARRIER');