The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_msg_data := 'Collaboration History is successfully updated with the error message';
SELECT header.tp_header_id , header.party_id, header.party_site_id,
header.party_type, doclogs.internal_control_number, doclogs.attribute5,
transaction.transaction_type, transaction.transaction_subtype,
doclogs.document_number, doclogs.protocol_type, estd.standard_code
INTO l_np_tp_id, l_party_id, l_tp_location_code,
l_party_type, l_int_con_no, l_app_ref_id,
l_xmlg_tran_type, l_xmlg_tran_subtype,
l_xmlg_doc_id, l_sender_component, l_message_standard
FROM ecx_doclogs doclogs, ecx_tp_details details, ecx_tp_headers header,
ecx_ext_processes processes, ecx_transactions transaction, ecx_standards estd
WHERE doclogs.msgid = HEXTORAW(p_msg_id) and
doclogs.party_site_id = source_tp_location_code and
doclogs.transaction_type = ext_type and
doclogs.transaction_subtype = ext_subtype and
header.tp_header_id = details.tp_header_id and
details.ext_process_id = processes.ext_process_id and
processes.transaction_id = transaction.transaction_id and
estd.standard_id = processes.standard_id and
rownum < 2;
SELECT internal_control_number, attribute5, transaction_type,
transaction_subtype, document_number, protocol_type, message_standard
INTO l_int_con_no, l_app_ref_id, l_xmlg_tran_type,
l_xmlg_tran_subtype, l_xmlg_doc_id, l_sender_component, l_message_standard
FROM ecx_doclogs
WHERE msgid = HEXTORAW(p_msg_id);
SELECT DECODE(NVL(l_xmlg_tran_type, 'NULL'), 'NULL', '', l_xmlg_tran_type || ':') ||
DECODE(NVL(l_xmlg_tran_subtype, 'NULL'), 'NULL', '', l_xmlg_tran_subtype || ':') ||
DECODE(NVL(l_xmlg_doc_id, 'NULL'), 'NULL', '', l_xmlg_doc_id)
INTO l_doc_no
FROM DUAL;
SELECT collaboration_id
INTO l_coll_id
FROM CLN_COLL_HIST_HDR
WHERE APPLICATION_REFERENCE_ID = l_app_ref_id;
SELECT collaboration_id
INTO l_coll_id
FROM CLN_COLL_HIST_HDR
WHERE xmlg_msg_id = p_msg_id;
SELECT processing_message INTO l_error_msg FROM ecx_in_process_v
WHERE msgid = HEXTORAW(l_msg_id);
SELECT TRANSACTION_TYPE, TRANSACTION_SUBTYPE, DIRECTION, ATTRIBUTE5, message_standard
INTO l_txn_type, l_txn_subtype, l_direction, l_app_ref_id, l_message_standard
FROM ecx_doclogs
WHERE msgid = HEXTORAW(l_msg_id);
SELECT collaboration_id
INTO l_coll_id
FROM CLN_COLL_HIST_HDR
WHERE APPLICATION_REFERENCE_ID = l_app_ref_id;
CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION(
x_return_status => l_return_code,
x_msg_data => l_return_msg,
p_msg_text => l_msg,
p_coll_pt => 'XML_GATEWAY',
p_doc_status => 'SUCCESS',
p_coll_id => l_coll_id,
-- p_xmlg_internal_control_number => NULL,
p_xmlg_msg_id => l_msg_id,
p_rosettanet_check_required => FALSE,
x_dtl_coll_id => l_dtl_coll_id);