[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT payload
INTO l_clob
FROM m4u_dmd_payloads
WHERE payload_id = l_payload_id;
FUNCTION update_collab
(
p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t
) RETURN VARCHAR2
AS
l_params wf_parameter_list_t;
m4u_dmd_utils.log('Entering m4u_cln_pkg.update_collab',6);
SELECT *
INTO l_m4u_doc_rec
FROM m4u_dmd_documents
WHERE doc_id = l_doc_id;
SELECT *
INTO l_m4u_msg_rec
FROM m4u_dmd_messages
WHERE msg_id = l_m4u_doc_rec.msg_id;
SELECT collaboration_id
INTO l_cln_id
FROM cln_coll_hist_hdr
WHERE application_reference_id = l_cln_ref_id
AND collaboration_type = l_cln_type
AND rownum < 2;
m4u_dmd_utils.log('Call cln.update_collaboration',6);
cln_ch_collaboration_pkg.update_collaboration
(
x_return_status => l_ret_sts,
x_msg_data => l_ret_msg,
p_coll_id => l_cln_id,
p_app_id => m4u_dmd_utils.c_app_id,
p_ref_id => l_cln_ref_id,
p_rel_no => NULL,
p_doc_no => l_cln_doc_id,
p_doc_rev_no => NULL,
p_xmlg_transaction_type => l_xmlg_type,
p_xmlg_transaction_subtype => l_xmlg_styp,
p_xmlg_document_id => l_xmlg_doc_id,
p_resend_flag => NULL,
p_resend_count => NULL,
p_disposition => l_cln_disp,
p_coll_status => l_cln_sts,
p_doc_type => l_cln_doc_type,
p_doc_dir => l_payload_dir,
p_coll_pt => l_cln_pt,
p_org_ref => NULL,
p_doc_status => l_cln_doc_sts,
p_notification_id => NULL,
p_msg_text => l_cln_msg,
p_tr_partner_type => m4u_cln_pkg.c_party_type,
p_tr_partner_id => m4u_cln_pkg.g_party_id,
p_tr_partner_site => m4u_cln_pkg.g_party_site_id,
p_sender_component => NULL,
p_rosettanet_check_required => false,
p_xmlg_msg_id => null,
p_attribute1 => l_m4u_doc_rec.msg_id,
p_attribute2 => l_m4u_doc_rec.doc_id,
p_attribute3 => l_m4u_doc_rec.ref_doc_id,
p_attribute4 => l_m4u_doc_rec.orig_doc_id,
p_attribute5 => l_m4u_doc_rec.type,
p_attribute6 => l_m4u_doc_rec.action,
p_attribute7 => l_m4u_doc_rec.top_gtin,
p_attribute8 => l_m4u_doc_rec.info_provider_gln,
p_attribute9 => l_m4u_doc_rec.data_recepient_gln,
p_attribute10 => l_m4u_doc_rec.target_market_country,
p_attribute11 => l_m4u_doc_rec.target_market_sub_div,
p_attribute12 => l_m4u_doc_rec.parameter1,
p_attribute13 => l_m4u_doc_rec.parameter2,
p_attribute14 => l_m4u_doc_rec.parameter3,
p_attribute15 => l_m4u_doc_rec.lparameter1,
p_dattribute1 => l_m4u_doc_rec.doc_timestamp,
x_dtl_coll_id => l_cln_dtl_id,
p_xml_event_key => null
);
m4u_dmd_utils.log('Exiting m4u_cln_pkg.update_collab - Success' ,6);
m4u_dmd_utils.log('m4u_cln_pkg.update_collab',6);
END update_collab;
PROCEDURE update_tp_detail
(
p_tp_hdr_id IN NUMBER,
p_direction IN VARCHAR2,
x_ret_sts OUT NOCOPY VARCHAR2,
x_ret_msg OUT NOCOPY VARCHAR2
)
IS
l_ret_msg VARCHAR2(4000);
m4u_dmd_utils.log('Entering m4u_cln_pkg.add_or_update_tp_detail',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 = p_direction
AND txn.transaction_type = l_txn_type
AND txn.transaction_subtype = l_txn_subtype
AND tph.tp_header_id = p_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 = p_direction
AND txn.party_type = l_party_type
AND txn.transaction_type = l_txn_type
AND txn.transaction_subtype = l_txn_subtype;
m4u_dmd_utils.log('Exiting add_or_update_tp_detail - Failure',6);
m4u_dmd_utils.log('Call ecx_tp_api.update_tp_detail',1);
l_progress := 'Call ecx_tp_api.update_tp_detail';
ecx_tp_api.update_tp_detail
(
x_return_status => l_ret_sts,
x_msg => l_ret_msg,
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 => null,
p_protocol_type => l_protocol,
p_protocol_address => l_protocol_addr,
p_username => l_user,
p_password => l_passwd,
p_routing_id => null,
p_source_tp_location_code => l_src_loc,
p_external_tp_location_code => null,
p_confirmation => l_doc_conf,
p_passupd_flag => 'Y'
);
m4u_dmd_utils.log('Exiting add_or_update_tp_detail - Failure',6);
m4u_dmd_utils.log('Exiting add_or_update_tp_detail - Success',2);
END update_tp_detail;
SELECT location_id, object_version_number
INTO l_location_id, l_obj_ver_num
FROM hr_locations_all
WHERE location_code = l_location_code
AND ROWNUM < 2;
m4u_dmd_utils.log('Call HR_LOCATION_API.update_location', 2);
l_progress := 'Call HR_LOCATION_API.update_location';
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
);
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;
m4u_dmd_utils.log('Call ecx_tp_api.update_trading_partner', 2);
l_progress := 'Call ecx_tp_api.update_trading_partner';
ecx_tp_api.update_trading_partner
(
x_return_status => l_ret_sts,
x_msg => l_ret_msg,
p_tp_header_id => l_tp_hdr_id,
p_company_admin_email => m4u_cln_pkg.c_tp_email
);
l_progress := 'Call update_tp_detail - OUT';
update_tp_detail(l_tp_hdr_id,m4u_dmd_utils.c_dir_out,l_ret_sts,l_ret_msg);
l_progress := 'Call update_tp_detail - IN';
update_tp_detail(l_tp_hdr_id,m4u_dmd_utils.c_dir_in ,l_ret_sts,l_ret_msg);
SELECT to_char(e.party_id), to_char(e.party_site_id)
INTO g_party_id, g_party_site_id
FROM hr_locations_all h,
ecx_tp_headers e
WHERE h.location_id = e.party_id
AND e.party_type = m4u_cln_pkg.c_party_type
AND h.location_code = m4u_cln_pkg.c_party_site;