[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_updated_by OUT NOCOPY Varchar2,
x_last_update_date OUT NOCOPY Varchar2
) IS
Begin
x_return_status := ECX_UTIL_API.G_NO_ERROR;
Select
TP_HEADER_ID,
COMPANY_ADMIN_EMAIL,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE
into
x_tp_header_id,
x_company_admin_email,
x_last_updated_by,
x_last_update_date,
x_created_by,
x_creation_date
from
ECX_TP_HEADERS
where party_type = p_party_type
and party_id = p_party_id
and party_site_id = p_party_site_id;
select ecx_tp_headers_s.nextval
from dual;
insert into ECX_TP_HEADERS(
TP_HEADER_ID,
PARTY_TYPE,
PARTY_ID,
PARTY_SITE_ID,
COMPANY_ADMIN_EMAIL,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATED_BY,
CREATION_DATE)
values (
x_tp_header_id,
p_party_type,
p_party_id,
p_party_site_id,
p_company_admin_email,
0,
sysdate,
0,
sysdate
) ;
Procedure update_trading_partner(
x_return_status OUT NOCOPY Pls_integer,
x_msg OUT NOCOPY Varchar2,
p_tp_header_id IN Pls_integer,
p_company_admin_email IN Varchar2
) IS
l_ret_code pls_integer := ECX_UTIL_API.G_NO_ERROR;
Update ECX_TP_HEADERS set
COMPANY_ADMIN_EMAIL = p_company_admin_email,
LAST_UPDATED_BY = 0,
LAST_UPDATE_DATE = sysdate
where tp_header_id = p_tp_header_id;
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_tp_headers',
'p_param_name', 'Trading partner header ID',
'p_param_id', p_tp_header_id);
select party_type,
party_id,
party_site_id
into l_party_type,
l_party_id,
l_party_site_id
from ecx_tp_headers
where tp_header_id = p_tp_header_id;
p_mod_type => 'UPDATE',
p_tp_header_id => p_tp_header_id,
p_party_type => l_party_type,
p_party_id => l_party_id,
p_party_site_id => l_party_site_id,
p_company_email_addr => p_company_admin_email);
Procedure delete_trading_partner(
x_return_status OUT NOCOPY Pls_integer,
x_msg OUT NOCOPY Varchar2,
p_tp_header_id IN Pls_integer
) IS
l_xref_dtl_id ecx_xref_dtl.xref_dtl_id%type;
select xref_dtl_id from ecx_xref_dtl
where tp_header_id = p_tp_header_id;
delete from ecx_tp_details
where tp_header_id = p_tp_header_id;
delete from ecx_xref_dtl
where tp_header_id = p_tp_header_id;
delete from ecx_xref_dtl_tl
where xref_dtl_id = l_xref_dtl_id;
select party_type,
party_id,
party_site_id,
company_admin_email
into l_party_type,
l_party_id,
l_party_site_id,
l_company_admin_email
from ecx_tp_headers
where tp_header_id = p_tp_header_id;
delete from ecx_tp_headers
where tp_header_id = p_tp_header_id;
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
'p_table', 'ecx_tp_headers',
'p_param_name', 'Trading partner header ID',
'p_param_id', p_tp_header_id);
p_mod_type => 'DELETE',
p_tp_header_id => p_tp_header_id,
p_party_type => l_party_type,
p_party_id => l_party_id,
p_party_site_id => l_party_site_id,
p_company_email_addr => l_company_admin_email);
x_last_updated_by OUT NOCOPY Varchar2,
x_last_update_date OUT NOCOPY Varchar2,
p_source_tp_location_code IN Varchar2
) IS
i_hub_id NUMBER;
select
TP_DETAIL_ID,
MAP_CODE,
CONNECTION_TYPE,
HUB_USER_ID,
HUB_ID,
PROTOCOL_TYPE,
PROTOCOL_ADDRESS,
USERNAME,
PASSWORD ,
ROUTING_ID,
SOURCE_TP_LOCATION_CODE,
EXTERNAL_TP_LOCATION_CODE,
CONFIRMATION,
etd.CREATED_BY,
etd.LAST_UPDATED_BY,
etd.CREATION_DATE,
etd.LAST_UPDATE_DATE
Into
x_tp_detail_id,
x_map_code,
x_connection_type,
x_hub_user_id,
i_hub_id,
x_protocol_type,
x_protocol_address,
x_username,
x_password,
x_routing_id,
x_source_tp_location_code,
x_external_tp_location_code,
x_confirmation,
x_created_by ,
x_last_updated_by ,
x_creation_date,
x_last_update_date
from ecx_tp_details etd,
ecx_mappings em
where etd.tp_header_id = p_tp_header_id
and etd.ext_process_id = p_ext_process_id
and em.map_id = etd.map_id
and (p_source_tp_location_code is null
or etd.source_tp_location_code=p_source_tp_location_code);
select protocol_type,protocol_address
into x_protocol_type,x_protocol_address
from ecx_hubs
where hub_id=i_hub_id;
select username,password
into x_username,x_password
from ecx_hub_users
where hub_user_id=x_hub_user_id ;
x_last_updated_by OUT NOCOPY Varchar2,
x_last_update_date OUT NOCOPY Varchar2,
p_ext_type IN Varchar2 ,
p_ext_subtype IN Varchar2 ,
p_source_tp_location_code IN Varchar2
) IS
x_ext_process_id number := 0;
x_last_updated_by => x_last_updated_by,
x_last_update_date => x_last_update_date,
x_return_status => x_return_status,
x_msg => x_msg);
select
etd.TP_HEADER_ID,
etd.TP_DETAIL_ID,
em.MAP_CODE,
etd.CONNECTION_TYPE,
etd.HUB_USER_ID,
etd.PROTOCOL_TYPE,
etd.PROTOCOL_ADDRESS,
etd.USERNAME,
etd.PASSWORD,
etd.ROUTING_ID,
etd.SOURCE_TP_LOCATION_CODE,
etd.EXTERNAL_TP_LOCATION_CODE,
etd.CONFIRMATION,
etd.CREATED_BY,
etd.LAST_UPDATED_BY,
etd.CREATION_DATE,
etd.LAST_UPDATE_DATE
Into
x_tp_header_id,
x_tp_detail_id,
x_map_code,
x_connection_type,
x_hub_user_id,
x_protocol_type,
x_protocol_address,
x_username,
x_password,
x_routing_id,
x_source_tp_location_code,
x_external_tp_location_code,
x_confirmation,
x_created_by,
x_last_updated_by,
x_creation_date,
x_last_update_date
from ecx_tp_details etd,
ecx_tp_headers eth,
ecx_mappings em
where eth.party_id = p_party_id
and eth.party_site_id = p_party_site_id
and eth.party_type = p_party_type
and etd.ext_process_id = x_ext_process_id
and em.map_id = etd.map_id
and (p_source_tp_location_code is null
or etd.source_tp_location_code=p_source_tp_location_code);
select name
from ecx_hubs
where hub_id = i_hub_id;
select hub_entity_code
from ecx_hub_users
where hub_user_id = p_hub_user_id;
select 1 from ecx_tp_headers
where tp_header_id = p_tp_header_id;
select 1,direction from ecx_ext_processes
where ext_process_id = p_ext_process_id;
select map_id from ecx_mappings
where map_code = p_map_code;
select 1 from ecx_hub_users
where hub_user_id = p_hub_user_id;
select 1 from ecx_tp_details
where tp_detail_id = p_routing_id;
Select 1 from ecx_tp_details tp,ecx_ext_processes ep
where tp.ext_process_id=ep.ext_process_id
And ep.ext_type = p_ext_type_in
And ep.ext_subtype = p_ext_subtype_in
And ep.standard_id = p_standard_id_in
And tp.source_tp_Location_code= p_source_tp_location_code_in
And ep.direction = p_direction_in ;
select 1 from ecx_tp_details
where tp_header_id = p_tp_header_id_in
and ext_process_id in ( select ext.ext_process_id
from ecx_ext_processes ext,
ecx_transactions tran
where ext.direction = 'OUT'
and ext.transaction_id
= tran.transaction_id
and tran.transaction_type
= p_transaction_type_in
and tran.transaction_subtype
= p_transaction_subtype_in );
select ext_type,ext_subtype,standard_id,direction
from ecx_ext_processes
where ext_process_id=p_ext_process_id_in;
select transaction_type,transaction_subtype
from ecx_transactions et,ecx_ext_processes eep
where eep.ext_process_id = p_ext_process_id_in
and et.transaction_id = eep.transaction_id;
select 1,hub_id from ecx_hubs
where name=p_connection_type and
protocol_type= p_protocol_type;
select ecx_tp_details_s.nextval
from dual;
Insert into ECX_TP_DETAILS(
TP_HEADER_ID,
TP_DETAIL_ID,
MAP_ID,
EXT_PROCESS_ID,
CONNECTION_TYPE,
HUB_USER_ID,
HUB_ID,
PROTOCOL_TYPE,
PROTOCOL_ADDRESS,
USERNAME,
PASSWORD,
ROUTING_ID,
SOURCE_TP_LOCATION_CODE,
EXTERNAL_TP_LOCATION_CODE,
CONFIRMATION,
CREATED_BY,
LAST_UPDATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE
)
values (
p_tp_header_id,
x_tp_detail_id,
i_map_id,
p_ext_process_id,
l_connection_type,
null,
null,
p_protocol_type,
p_protocol_address,
p_username,
encrypt_password, -- CHECK THIS!
null,
p_source_tp_location_code,
p_external_tp_location_code,
l_confirmation,
0,
0,
sysdate,
sysdate
);
Insert into ECX_TP_DETAILS(
TP_HEADER_ID,
TP_DETAIL_ID,
MAP_ID,
EXT_PROCESS_ID,
CONNECTION_TYPE,
HUB_USER_ID,
HUB_ID,
PROTOCOL_TYPE,
PROTOCOL_ADDRESS,
USERNAME,
PASSWORD,
ROUTING_ID,
EXTERNAL_TP_LOCATION_CODE,
CONFIRMATION,
CREATED_BY,
LAST_UPDATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE
)
values (
p_tp_header_id,
x_tp_detail_id,
i_map_id,
p_ext_process_id,
p_connection_type,
l_hub_user_id,
i_hub_id,
null,
null,
null,
null,
null,
p_external_tp_location_code,
l_confirmation,
0,
0,
sysdate,
sysdate
);
Insert into ECX_TP_DETAILS(
TP_HEADER_ID,
TP_DETAIL_ID,
MAP_ID,
EXT_PROCESS_ID,
CONNECTION_TYPE,
HUB_USER_ID,
HUB_ID,
PROTOCOL_TYPE,
PROTOCOL_ADDRESS,
USERNAME,
PASSWORD,
ROUTING_ID,
SOURCE_TP_LOCATION_CODE,
EXTERNAL_TP_LOCATION_CODE,
CONFIRMATION,
CREATED_BY,
LAST_UPDATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE
)
values (
p_tp_header_id,
x_tp_detail_id,
i_map_id,
p_ext_process_id,
null,
null,
null,
null,
null,
null,
null,
p_routing_id,
p_source_tp_location_code,
p_external_tp_location_code,
l_confirmation,
0,
0,
sysdate,
sysdate
);
i_last_updated_by varchar2(10);
i_last_update_date varchar2(25);
x_last_updated_by => i_last_updated_by,
x_last_update_date => i_last_update_date,
x_return_status =>x_return_status,
x_msg =>x_msg
);
x_last_updated_by => i_last_updated_by,
x_last_update_date => i_last_update_date,
x_return_status => x_return_status,
x_msg => x_msg);
Procedure update_tp_detail(
x_return_status Out NOCOPY pls_integer,
x_msg Out NOCOPY Varchar2,
p_tp_detail_id In pls_integer,
p_map_code In Varchar2,
p_ext_process_id In pls_integer,
p_connection_type In Varchar2,
p_hub_user_id In pls_integer,
p_protocol_type In Varchar2,
p_protocol_address In Varchar2,
p_username In Varchar2,
p_password In Varchar2,
p_routing_id In pls_integer,
p_source_tp_location_code In Varchar2,
p_external_tp_location_code In Varchar2,
p_confirmation In pls_integer ,
p_passupd_flag IN varchar2
) Is
l_confirmation ecx_tp_details.confirmation%type;
select name
from ecx_hubs
where hub_id = i_hub_id;
select hub_entity_code
from ecx_hub_users
where hub_user_id = p_hub_user_id;
select map_id from ecx_mappings
where map_code = p_map_code;
select direction from ecx_ext_processes
where ext_process_id = p_ext_process_id;
select 1 from ecx_tp_details
where tp_detail_id = p_tp_detail_id;
select 1 from ecx_hub_users
where hub_user_id = p_hub_user_id;
select 1 from ecx_tp_details
where tp_detail_id = p_routing_id;
Select tp_detail_id from ecx_tp_details tp,ecx_ext_processes ep
where tp.ext_process_id=ep.ext_process_id
And ep.ext_type = p_ext_type_in
And ep.ext_subtype = p_ext_subtype_in
And ep.standard_id = p_standard_id_in
And tp.source_tp_Location_code= p_source_tp_location_code_in
And tp.tp_detail_id <> p_tp_detail_id_in
And ep.direction = p_direction_in ;
select 1 from ecx_tp_details
where tp_header_id = p_tp_header_id_in
and tp_detail_id <> p_tp_detail_id_in
and ext_process_id in ( select ext.ext_process_id
from ecx_ext_processes ext,
ecx_transactions tran
where ext.direction = 'OUT'
and ext.transaction_id
= tran.transaction_id
and tran.transaction_type
= p_transaction_type_in
and tran.transaction_subtype
= p_transaction_subtype_in );
select ext_type,ext_subtype,standard_id,direction
from ecx_ext_processes
where ext_process_id=p_ext_process_id_in;
select transaction_type,transaction_subtype
from ecx_transactions et,ecx_ext_processes eep
where eep.ext_process_id = p_ext_process_id_in
and et.transaction_id = eep.transaction_id;
select tp_header_id
from ecx_tp_details
where tp_detail_id=p_tp_detail_id_in;
select 1,hub_id from ecx_hubs
where name=p_connection_type and
protocol_type= p_protocol_type;
Update ECX_TP_DETAILS set
MAP_ID = i_map_id,
EXT_PROCESS_ID = p_ext_process_id,
CONNECTION_TYPE = l_connection_type,
HUB_USER_ID = null,
HUB_ID = null,
PROTOCOL_TYPE = p_protocol_type,
PROTOCOL_ADDRESS = p_protocol_address,
USERNAME = p_username,
PASSWORD = encrypt_password,
ROUTING_ID = null,
SOURCE_TP_LOCATION_CODE = p_source_tp_location_code,
EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
CONFIRMATION = l_confirmation,
LAST_UPDATED_BY = 0,
LAST_UPDATE_DATE = sysdate
where tp_detail_id = p_tp_detail_id;
Update ECX_TP_DETAILS set
MAP_ID = i_map_id,
EXT_PROCESS_ID = p_ext_process_id,
CONNECTION_TYPE = l_connection_type,
HUB_USER_ID = null,
HUB_ID = null,
PROTOCOL_TYPE = p_protocol_type,
PROTOCOL_ADDRESS = p_protocol_address,
ROUTING_ID = null,
SOURCE_TP_LOCATION_CODE = p_source_tp_location_code,
EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
CONFIRMATION = l_confirmation,
LAST_UPDATED_BY = 0,
LAST_UPDATE_DATE = sysdate
where tp_detail_id = p_tp_detail_id;
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_tp_details',
'p_param_name', 'Trading partner detail ID',
'p_param_id', p_tp_detail_id);
Update ECX_TP_DETAILS set
MAP_ID = i_map_id,
EXT_PROCESS_ID = p_ext_process_id,
CONNECTION_TYPE = p_connection_type,
HUB_USER_ID = p_hub_user_id,
HUB_ID = i_hub_id,
PROTOCOL_TYPE = null,
PROTOCOL_ADDRESS = null,
USERNAME = null,
PASSWORD = null,
ROUTING_ID = null,
EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
CONFIRMATION = l_confirmation,
LAST_UPDATED_BY = 0,
LAST_UPDATE_DATE = sysdate
where tp_detail_id = p_tp_detail_id;
Update ECX_TP_DETAILS set
MAP_ID = i_map_id,
EXT_PROCESS_ID = p_ext_process_id,
CONNECTION_TYPE = p_connection_type,
HUB_USER_ID = p_hub_user_id,
HUB_ID = i_hub_id,
PROTOCOL_TYPE = null,
PROTOCOL_ADDRESS = null,
ROUTING_ID = null,
EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
CONFIRMATION = l_confirmation,
LAST_UPDATED_BY = 0,
LAST_UPDATE_DATE = sysdate
where tp_detail_id = p_tp_detail_id;
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_tp_details',
'p_param_name', 'Trading partner detail ID',
'p_param_id', p_tp_detail_id);
Update ECX_TP_DETAILS set
MAP_ID = i_map_id,
EXT_PROCESS_ID = p_ext_process_id,
CONNECTION_TYPE = null,
HUB_USER_ID = null,
HUB_ID = null,
PROTOCOL_TYPE = null,
PROTOCOL_ADDRESS = null,
USERNAME = null,
PASSWORD = null,
ROUTING_ID = p_routing_id,
SOURCE_TP_LOCATION_CODE = p_source_tp_location_code,
EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
CONFIRMATION = l_confirmation,
LAST_UPDATED_BY = 0,
LAST_UPDATE_DATE = sysdate
where tp_detail_id = p_tp_detail_id;
Update ECX_TP_DETAILS set
MAP_ID = i_map_id,
EXT_PROCESS_ID = p_ext_process_id,
CONNECTION_TYPE = null,
HUB_USER_ID = null,
HUB_ID = null,
PROTOCOL_TYPE = null,
PROTOCOL_ADDRESS = null,
ROUTING_ID = p_routing_id,
SOURCE_TP_LOCATION_CODE = p_source_tp_location_code,
EXTERNAL_TP_LOCATION_CODE = p_external_tp_location_code,
CONFIRMATION = l_confirmation,
LAST_UPDATED_BY = 0,
LAST_UPDATE_DATE = sysdate
where tp_detail_id = p_tp_detail_id;
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_UPDATED',
'p_table', 'ecx_tp_details',
'p_param_name', 'Trading partner detail ID',
'p_param_id', p_tp_detail_id);
Procedure delete_tp_detail( x_return_status Out NOCOPY pls_integer,
x_msg Out NOCOPY Varchar2,
p_tp_detail_id In pls_integer ) Is
begin
x_return_status := ECX_UTIL_API.G_NO_ERROR;
delete from ecx_tp_details
where tp_detail_id = p_tp_detail_id;
x_msg := ecx_debug.getTranslatedMessage('ECX_NO_ROWS_DELETED',
'p_table', 'ecx_tp_details',
'p_param_name', 'Trading partner detail ID',
'p_param_id', p_tp_detail_id);
if(p_mod_type='DELETE') then
wf_local_synch.propagate_role(
p_orig_system => org_name,
p_orig_system_id =>party_or_site_id ,
p_attributes => p_params,
p_expiration_date => sysdate
);
select LOCATION_CODE from hr_locations where LOCATION_ID=v_party_id;
select ADDRESS_LINE_1||ADDRESS_LINE_2||ADDRESS_LINE_3 ||town_or_city||country||postal_code from hr_locations
where location_id =v_party_id ;
select BANK_NAME from CE_BANK_BRANCHES_V where BRANCH_PARTY_ID=v_party_id;
select address_line1||' '||address_line2||' '||address_line3||' '||CITY||' '||ZIP from CE_BANK_BRANCHES_V where BRANCH_PARTY_ID=v_party_id;
select p.vendor_name from PO_VENDORS p where p.vendor_ID =v_party_id ;
select p1.ADDRESS_LINE1||' '||p1.ADDRESS_LINE2||' '||p1.ADDRESS_LINE3||' '||p1.CITY||p1.ZIP from PO_VENDOR_SITES_ALL p1
where p1.VENDOR_SITE_ID =v_party_site_id and p1.VENDOR_ID=v_party_id;
select PARTY_NAME from hz_parties where party_id=v_party_id;
select ADDRESS1 ||ADDRESS2 || ADDRESS3 || ADDRESS4 ||CITY ||POSTAL_CODE ||STATE ||PROVINCE || COUNTY||COUNTRY from hz_locations where location_id =(select location_id from hz_party_sites where party_id=v_party_id and party_site_id=v_party_site_id);
select decode(party_type,'C','HZ_PARTIES','EXCHANGE','HZ_PARTIES','CARRIER','HZ_PARTIES','S','PO_VENDORS','I','HR_LOCATIONS','B','CE_BANK_BRANCHES_V') from ecx_tp_headers where party_id =v_party_id ;
select decode(party_type,'C','HZ_PARTY_SITES','EXCHANGE','HZ_PARTY_SITES','CARRIER','HZ_PARTY_SITES','S','PO_VENDOR_SITES_ALL','I','HR_LOCATIONS_SITES','B','CE_BANK_BRANCHES_SITE') from ecx_tp_headers where party_id =v_party_id ;
select decode(p_party_type,'C','HZ_PARTY_SITES','EXCHANGE','HZ_PARTY_SITES','CARRIER','HZ_PARTY_SITES','S','PO_VENDOR_SITES','I','HR_LOCATIONS_SITES','B','CE_BANK_BRANCHES_SITE') from dual;
select decode(p_party_type,'C','HZ_PARTIES','EXCHANGE','HZ_PARTIES','CARRIER','HZ_PARTIES','S','PO_VENDORS','I','HR_LOCATIONS','B','CE_BANK_BRANCHES_V') from dual;
if(p_mod_type='DELETE') then
open orig_role;