The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cln_collaboration_msg_id_s.nextval INTO l_event_key FROM dual ;
cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
(
p_api_version => 1.0,
p_commit => FND_API.g_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_cln_id => p_cin_unique_id,
p_disposition_code => l_apc_disposition,
p_disposition_date => sysdate,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
cln_debug_pub.Add('-- UPDATE_EVENT_DISPOSITION returned --',1);
cln_ch_collaboration_pkg.update_collaboration(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
p_coll_id => p_cin_unique_id,
p_msg_text => l_fnd_msg,
p_xmlg_msg_id => NULL,
p_xmlg_transaction_type => 'M4U',
p_xmlg_transaction_subtype => 'RESP_CIC',
p_xmlg_int_transaction_type => 'M4U',
p_xmlg_int_transaction_subtype => 'RESP_CIC',
p_doc_dir => 'IN',
p_doc_type => 'M4U_RESP_CIC',
p_disposition => l_disposition,
p_doc_status => l_doc_status,
p_coll_status => l_coll_status,
p_xmlg_document_id => NULL,
p_xmlg_internal_control_number => p_xmlg_internal_control_no,
p_tr_partner_type => m4u_ucc_utils.c_party_type,
p_tr_partner_id => m4u_ucc_utils.g_party_id,
p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
p_attribute2 => p_target_market,
p_attribute5 => p_supp_gln,
p_attribute6 => p_retailer_gln,
p_attribute8 => p_ucc_doc_unique_id,
p_attribute10 => p_cic_unique_id,
p_attribute11 => p_cic_state,
p_doc_no => p_gtin,
p_dattribute1 => sysdate,
p_rosettanet_check_required => false,
x_dtl_coll_id => x_collab_detail_id );
cln_debug_pub.add('collaboration history updated for cln_id - ' || p_cin_unique_id,1);
x_msg_data := x_msg_data || ' ; EGO API update - ' || l_msg_data ;
cln_debug_pub.add('collaboration history messages updated for cln_dtl_id - ' || p_collab_detail_id,1);
x_msg_data := x_msg_data || ' Collaboration Add Messages update - ' || l_msg_data ;
cln_debug_pub.Add(' last_updated_by -- '|| 1,1);
cln_debug_pub.Add(' last_updated_date -- '|| sysdate ,1);
cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
(
p_api_version => 1.0,
p_commit => FND_API.g_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_cln_id => p_rcir_command_unique_id,
p_disposition_code => l_apc_disposition,
p_disposition_date => sysdate,
x_return_status => x_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
cln_debug_pub.Add('-- UPDATE_EVENT_DISPOSITION returned --',1);
cln_ch_collaboration_pkg.update_collaboration(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
p_coll_id => p_rcir_command_unique_id,
p_msg_text => l_fnd_msg,
p_xmlg_msg_id => NULL,
p_xmlg_transaction_type => 'M4U',
p_xmlg_transaction_subtype => 'RESP_BATCH',
p_xmlg_int_transaction_type => 'M4U',
p_xmlg_int_transaction_subtype => 'RESP_BATCH',
p_doc_dir => 'IN',
p_doc_type => 'M4U_RESP_BATCH',
p_disposition => l_disposition,
p_doc_status => l_doc_status,
p_coll_status => l_coll_status,
p_xmlg_document_id => NULL,
p_xmlg_internal_control_number => p_xmlg_internal_control_no,
p_tr_partner_type => m4u_ucc_utils.c_party_type,
p_tr_partner_id => m4u_ucc_utils.g_party_id,
p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
p_attribute2 => p_target_market,
p_attribute5 => p_supp_gln,
p_attribute8 => p_ucc_doc_unique_id,
p_attribute9 => p_rcir_command_validation_key,
p_dattribute1 => sysdate,
p_attribute11 => p_rcir_command_success,
p_doc_no => p_gtin,
p_rosettanet_check_required => false,
x_dtl_coll_id => x_collab_detail_id );
cln_debug_pub.Add('Update collaboration returned');
x_msg_data := x_msg_data || ' ; EGO API update - ' || l_msg_data ;
SELECT owner_role
INTO l_owner_role
FROM cln_coll_hist_hdr
WHERE attribute12 = l_batch_id AND rownum < 2
AND xmlg_transaction_type = 'M4U';
SELECT collaboration_type
INTO l_coll_type
FROM cln_coll_hist_hdr
WHERE collaboration_id = p_wlq_message_id;
cln_ch_collaboration_pkg.update_collaboration(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
p_coll_id => p_wlq_message_id,
p_msg_text => l_fnd_msg,
p_xmlg_msg_id => NULL,
p_xmlg_transaction_type => 'M4U',
p_xmlg_transaction_subtype => p_wlq_response_type,
p_xmlg_int_transaction_type => 'M4U',
p_xmlg_int_transaction_subtype => p_wlq_response_type,
p_doc_dir => 'IN',
p_doc_type => l_doc_type,
p_disposition => l_disposition,
p_doc_status => l_doc_status,
p_coll_status => l_coll_status,
p_xmlg_document_id => NULL,
p_xmlg_internal_control_number => p_xmlg_internal_control_no,
p_tr_partner_type => m4u_ucc_utils.c_party_type,
p_tr_partner_id => m4u_ucc_utils.g_party_id,
p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
p_attribute8 => p_ucc_doc_unique_id,
p_dattribute1 => sysdate,
p_attribute11 => p_wlq_success_flag,
p_rosettanet_check_required => false,
x_dtl_coll_id => x_collab_detail_id );
cln_debug_pub.add('collaboration history updated for CLN_uniqueid - ' || p_wlq_message_id,1);
SELECT collaboration_type
INTO l_coll_type
FROM cln_coll_hist_hdr
WHERE collaboration_id = p_command_unique_id;
cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
(
p_api_version => 1.0,
p_commit => FND_API.g_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_cln_id => p_command_unique_id,
p_disposition_code => l_apc_disposition,
p_disposition_date => sysdate,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
cln_debug_pub.add('ego_uccnet_events disposition updated');
CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION
(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
p_coll_id => p_command_unique_id,
p_msg_text => l_fnd_msg,
p_xmlg_msg_id => NULL,
p_xmlg_int_transaction_type => 'M4U',
p_xmlg_int_transaction_subtype => 'RESP_ACK',
p_xmlg_transaction_type => 'M4U',
p_xmlg_transaction_subtype => 'RESP_ACK',
p_doc_dir => 'IN',
p_doc_type => 'M4U_RESP_ACK',
p_disposition => l_disposition,
p_doc_status => l_doc_status,
p_coll_status => l_coll_status,
p_tr_partner_type => m4u_ucc_utils.c_party_type,
p_tr_partner_id => m4u_ucc_utils.g_party_id,
p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
p_rosettanet_check_required => FALSE,
x_dtl_coll_id => x_collab_detail_id,
p_xmlg_internal_control_number => p_xmlg_internal_control_no,
p_doc_creation_date => sysdate,
p_attribute2 => p_target_market,
p_attribute5 => p_supp_gln,
p_attribute8 => p_ucc_doc_unique_id,
p_attribute9 => p_command_validation_key,
p_attribute11 => p_command_success,
p_dattribute1 => sysdate,
p_doc_no => p_gtin
);
cln_debug_pub.add('collaboration history updated for cln_id - ' || p_command_unique_id,1);
SELECT collaboration_id
FROM cln_coll_hist_hdr
WHERE attribute12 = p_batch_id
AND xmlg_transaction_type = 'M4U';
cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
(
p_api_version => 1.0,
p_commit => FND_API.g_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_cln_id => rec_cln_id.collaboration_id,
p_disposition_code => l_apc_disposition,
p_disposition_date => sysdate,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
cln_debug_pub.Add('-- UPDATE_EVENT_DISPOSITION returned --',1);
CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION
(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
p_coll_id => rec_cln_id.collaboration_id ,
p_msg_text => l_fnd_msg,
p_xmlg_msg_id => NULL,
p_xmlg_int_transaction_type => 'M4U',
p_xmlg_int_transaction_subtype => 'RESP_ACK',
p_xmlg_transaction_type => 'M4U',
p_xmlg_transaction_subtype => 'RESP_ACK',
p_doc_dir => 'IN',
p_doc_type => 'M4U_RESP_ACK',
p_disposition => l_disposition,
p_doc_status => l_doc_status,
p_coll_status => l_coll_status,
p_tr_partner_type => m4u_ucc_utils.c_party_type,
p_tr_partner_id => m4u_ucc_utils.g_party_id,
p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
p_rosettanet_check_required => FALSE,
x_dtl_coll_id => x_collab_detail_id,
p_xmlg_internal_control_number => p_xmlg_internal_control_no,
p_doc_creation_date => sysdate,
p_attribute8 => p_ucc_doc_unique_id,
p_attribute10 => p_command_unique_id,
p_attribute11 => 'FALSE'
);
cln_debug_pub.add('collaboration history updated for cln_id - ' || rec_cln_id.collaboration_id,1);
SELECT owner_role
INTO l_owner_role
FROM cln_coll_hist_hdr
WHERE attribute12 = p_command_unique_id AND rownum < 2
AND xmlg_transaction_type = 'M4U';
x_msg_data := 'Successfully updated all RCIR-Batch collaborations on RCIR Batch response';
cln_debug_pub.Add('-- Calling API EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION --',1);
EGO_UCCNET_EVENTS_PUB.UPDATE_EVENT_DISPOSITION
(
p_api_version => 1.0,
p_commit => FND_API.g_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_cln_id => p_command_unique_id,
p_disposition_code => l_apc_disposition,
p_disposition_date => sysdate,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
cln_debug_pub.Add('-- UPDATE_EVENT_DISPOSITION returned --',1);
CLN_CH_COLLABORATION_PKG.UPDATE_COLLABORATION
(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
p_coll_id => p_command_unique_id ,
p_msg_text => l_fnd_msg,
p_xmlg_msg_id => NULL,
p_xmlg_int_transaction_type => 'M4U',
p_xmlg_int_transaction_subtype => 'RESP_ACK',
p_xmlg_transaction_type => 'M4U',
p_xmlg_transaction_subtype => 'RESP_ACK',
p_doc_dir => 'IN',
p_doc_type => 'M4U_RESP_ACK',
p_disposition => l_disposition,
p_doc_status => l_doc_status,
p_coll_status => l_coll_status,
p_tr_partner_type => m4u_ucc_utils.c_party_type,
p_tr_partner_id => m4u_ucc_utils.g_party_id,
p_tr_partner_site => m4u_ucc_utils.g_party_site_id,
p_rosettanet_check_required => FALSE,
x_dtl_coll_id => x_collab_detail_id,
p_xmlg_internal_control_number => p_xmlg_internal_control_no,
p_doc_creation_date => sysdate,
p_attribute8 => p_ucc_doc_unique_id,
p_attribute9 => p_command_validation_key,
p_attribute11 => p_command_success
);
cln_debug_pub.add('collaboration history updated for cln_id - ' || p_command_unique_id,1);