The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE add_or_update_tp_detail(
x_errbuf OUT NOCOPY VARCHAR2,
x_retcode OUT NOCOPY VARCHAR2,
p_txn_subtype VARCHAR2,
p_map VARCHAR2,
p_direction VARCHAR2,
p_tp_hdr_id NUMBER
)
IS
l_tp_dtl_id NUMBER;
cln_debug_pub.Add('----- ENTERING add_or_update_tp_detail API ------', 2);
SELECT tp_detail_id
INTO l_tp_dtl_id
FROM
ecx_tp_details tpd,
ecx_tp_headers tph,
ecx_ext_processes extp,
ecx_transactions txn,
ecx_standards svl
WHERE 1=1
AND tph.tp_header_id = tpd.tp_header_id
AND tpd.ext_process_id = extp.ext_process_id
AND extp.transaction_id = txn.transaction_id
AND extp.standard_id = svl.standard_id
AND svl.standard_code = l_standard_code
AND extp.ext_type = l_ext_type
AND extp.ext_subtype = l_ext_subtype
AND extp.direction = l_direction
AND txn.transaction_type = l_transaction_type
AND txn.transaction_subtype = l_transaction_subtype
AND tph.tp_header_id = l_tp_hdr_id;
SELECT extp.ext_process_id
INTO l_ext_process_id
FROM ecx_ext_processes extp,
ecx_transactions txn,
ecx_standards svl
WHERE 1=1
AND extp.transaction_id = txn.transaction_id
AND extp.standard_id = svl.standard_id
AND svl.standard_code = l_standard_code
AND extp.ext_type = l_ext_type
AND extp.ext_subtype = l_ext_subtype
AND extp.direction = l_direction
AND txn.party_type = l_party_type
AND txn.transaction_type = l_transaction_type
AND txn.transaction_subtype = l_transaction_subtype;
cln_debug_pub.Add('---- ecx_tp_api.update_trading_partner returns Normal----', 1);
cln_debug_pub.Add('---- Call ecx_tp_api.update_tp_detail ----', 1);
ecx_tp_api.update_tp_detail(
x_return_status => l_retcode,
x_msg => l_retmesg,
p_tp_detail_id => l_tp_dtl_id,
p_map_code => l_map,
p_ext_process_id => l_ext_process_id,
p_connection_type => l_conn_type,
p_hub_user_id => l_hub_user_id,
p_protocol_type => l_protocol,
p_protocol_address => l_protocol_addr,
p_username => l_user,
p_password => l_passwd,
p_routing_id => l_routing_id,
p_source_tp_location_code => l_src_loc,
p_external_tp_location_code => l_ext_loc,
p_confirmation => l_doc_conf,
p_passupd_flag => 'Y'
);
FND_MESSAGE.SET_TOKEN('PARAM1','update');
cln_debug_pub.Add('---- ecx_tp_api.update_trading_partner returns Normal----', 1);
cln_debug_pub.Add('Exiting add_or_update_tp_detail normally');
cln_debug_pub.Add('==========ERROR :EXTING add_or_update_tp_detail API ===========', 2);
cln_debug_pub.Add('==========ERROR :EXTING add_or_update_tp_detail API ===========', 2);
SELECT location_id, object_version_number
INTO l_location_id, l_obj_ver_num
FROM hr_locations_all
WHERE location_code = l_location_code;
SELECT location_id, object_version_number
INTO l_location_id, l_obj_ver_num
FROM hr_locations
WHERE location_code = l_location_code
AND ROWNUM < 2;
cln_debug_pub.Add('---- Update HR Location Values in DB ----', 1);
cln_debug_pub.Add('---- Call HR_LOCATION_API.update_location ----', 1);
FND_MESSAGE.SET_TOKEN('PARAM','Update');
HR_LOCATION_API.update_location(
p_effective_date => sysdate,
p_language_code => userenv('LANG'),
p_location_code => l_location_code,
p_description => l_description,
p_address_line_1 => l_addr_line_1,
p_country => l_country,
p_style => l_style,
p_location_id => l_location_id,
p_object_version_number => l_obj_ver_num );
l_msg_data := 'HR Location updated with success';
cln_debug_pub.Add('---- HR_LOCATION_API.update_location returns Normal----', 1);
SELECT tp_header_id
INTO l_tp_hdr_id
FROM ecx_tp_headers
WHERE party_type = 'I'
AND party_id = l_location_id
AND party_site_id = l_location_id;
cln_debug_pub.Add('---- Update ECX TP Header Values in DB ----', 1);
cln_debug_pub.Add('---- Call ecx_tp_api.update_trading_partner ----', 1);
ecx_tp_api.update_trading_partner(
x_return_status => l_retcode,
x_msg => l_retmesg,
p_tp_header_id => l_tp_hdr_id,
p_company_admin_email => '[email protected]'
);
FND_MESSAGE.SET_TOKEN('PARAM','update');
cln_debug_pub.Add('---- ecx_tp_api.update_trading_partner returns Normal----', 1);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'CIN','m4u_230_cin_out','OUT',l_tp_hdr_id);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'GBREGQRY','m4u_230_gbreg_qry_out','OUT',l_tp_hdr_id);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'PARTYQRY','m4u_230_party_qry_out','OUT',l_tp_hdr_id);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RCIR','m4u_230_rcir_out','OUT',l_tp_hdr_id);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'WLQ','m4u_230_wlq_out','OUT',l_tp_hdr_id);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_ACK','m4u_230_resp_ack_in','IN',l_tp_hdr_id);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_BATCH','m4u_230_resp_batch_in','IN',l_tp_hdr_id);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_CIC','m4u_230_resp_cic_in','IN',l_tp_hdr_id);
M4U_SETUP_PACKAGE.add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_GTIN','m4u_230_resp_gbregqry_in','IN',l_tp_hdr_id);
add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_PARTY','m4u_230_resp_partyqry_in','IN',l_tp_hdr_id);
add_or_update_tp_detail(l_retmesg,l_retcode,'RESP_RFCIN','m4u_230_resp_rfcin_in','IN',l_tp_hdr_id);