The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
SELECT intent FROM OKC_BUS_DOC_TYPES_B
WHERE DOCUMENT_TYPE=P_DOC_TYPE;
SELECT ver.article_id,ver.article_version_id, art.article_number
FROM OKC_ARTICLE_VERSIONS ver, okc_articles_all art
WHERE ver.ARTICLE_VERSION_ID=p_std_article_version_id
and art.article_id = ver.article_id;
p_insert_by_reference => 'N',
p_lock_text => 'N',
p_global_yn =>'N',
p_article_status => NULL,
p_sav_release => NULL,
p_start_date => NULL,
p_end_date => NULL,
p_std_article_version_id => p_std_article_version_id,
p_display_name => p_display_name,
p_translated_yn => 'N',
p_article_description => p_article_description,
p_date_approved => NULL,
p_default_section => NULL,
p_reference_source => NULL,
p_reference_text => NULL,
p_additional_instructions => NULL,
p_variation_description => NULL,
p_v_orig_system_reference_code => NULL,
p_v_orig_system_reference_id1 => NULL,
p_v_orig_system_reference_id2 => NULL,
p_global_article_version_id => NULL,
p_edited_in_word => p_edited_in_word,
p_article_text_in_word => p_article_text_in_word,
x_article_version_id => x_article_version_id
);
OKC_K_ARTICLES_GRP.update_article(
p_api_version =>1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_validate_commit => FND_API.G_FALSE,
p_validation_string => NULL,
p_commit => FND_API.G_FALSE,
p_mode => p_mode,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => p_cat_id,
p_sav_sae_id => lx_article_id,
p_amendment_description => p_amendment_description,
p_print_text_yn => p_print_text_yn,
p_article_version_id => x_article_version_id,
p_ref_article_id => l_ref_article_id ,
p_ref_article_version_id=> l_ref_article_version_id,
p_change_nonstd_yn => 'N',
p_object_version_number => NULL,
p_lock_terms_yn => p_lock_terms_yn
);
Procedure update_non_std_article(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_validate_commit IN VARCHAR2,
p_validation_string IN VARCHAR2,
p_commit IN VARCHAR2,
p_mode IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_article_title IN VARCHAR2,
p_article_type IN VARCHAR2,
-- Article Version Attributes
p_article_text IN CLOB,
p_provision_yn IN VARCHAR2,
p_display_name IN VARCHAR2,
p_article_description IN VARCHAR2,
p_edited_in_word IN VARCHAR2 DEFAULT 'N',
p_article_text_in_word IN BLOB DEFAULT NULL,
-- K Article Attributes
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_cat_id IN NUMBER,
p_amendment_description IN VARCHAR2,
p_print_text_yn IN VARCHAR2,
x_cat_id OUT NOCOPY NUMBER,
x_article_version_id OUT NOCOPY NUMBER,
p_lock_terms_yn IN VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_update_non_std_article';
SELECT sav_sae_id,
article_version_id,
ref_article_version_id,
change_nonstd_yn,
object_version_number
FROM OKC_K_ARTICLES_B
WHERE id=p_cat_id;
okc_debug.log('100: Entered update_non_std_article', 2);
G_PKG_NAME, '100: Entered update_non_std_article' );
SAVEPOINT g_update_non_std_article_GRP;
OKC_ARTICLES_GRP.update_article(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_article_id => l_article_id,
p_article_title => p_article_title,
p_article_number => NULL,
p_standard_yn => NULL,
p_article_intent => NULL,
p_article_language => NULL,
p_article_type => p_article_type,
p_orig_system_reference_code => NULL,
p_orig_system_reference_id1 => NULL,
p_orig_system_reference_id2 => NULL,
p_cz_transfer_status_flag => NULL,
p_object_version_number => NULL,
-- Article Version Attributes
p_article_version_id => x_article_version_id,
p_article_text => p_article_text,
p_provision_yn => p_provision_yn,
p_insert_by_reference => NULL,
p_lock_text => NULL,
p_global_yn => NULL,
p_article_status => NULL,
p_sav_release => NULL,
p_start_date => NULL,
p_end_date => NULL,
p_std_article_version_id => NULL,
p_display_name => p_display_name,
p_translated_yn => NULL,
p_article_description => p_article_description,
p_date_approved => NULL,
p_default_section => NULL,
p_reference_source => NULL,
p_reference_text => NULL,
p_additional_instructions => NULL,
p_variation_description => NULL,
p_v_orig_system_reference_code => NULL,
p_v_orig_system_reference_id1 => NULL,
p_v_orig_system_reference_id2 => NULL,
p_v_object_version_number => NULL,
p_edited_in_word => p_edited_in_word,
p_article_text_in_word => p_article_text_in_word
);
OKC_K_ARTICLES_GRP.update_article(
p_api_version =>1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_validate_commit => FND_API.G_FALSE,
p_validation_string => NULL,
p_commit => FND_API.G_FALSE,
p_mode => p_mode,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => p_cat_id,
p_sav_sae_id => l_article_id,
p_amendment_description => p_amendment_description,
p_print_text_yn =>p_print_text_yn,
p_article_version_id => x_article_version_id,
p_object_version_number => l_ovn,
p_lock_terms_yn => p_lock_terms_yn
);
okc_debug.log('900: Leavingupdate_non_std_article', 2);
G_PKG_NAME, '900: Leavingupdate_non_std_article' );
okc_debug.log('300: Leaving update_non_std_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
G_PKG_NAME, '300: Leaving update_non_std_article: OKC_API.G_EXCEPTION_ERROR Exception' );
ROLLBACK TO g_update_non_std_article_GRP;
okc_debug.log('400: Leavingupdate_non_std_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '400: Leavingupdate_non_std_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
ROLLBACK TO g_update_non_std_article_GRP;
okc_debug.log('500: Leaving update_non_std_article because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '500: Leaving update_non_std_article because of EXCEPTION: '||sqlerrm );
ROLLBACK TO g_update_non_std_article_GRP;
END update_non_std_article;
SELECT sav_sae_id, scn_id , orig_article_id, display_sequence, object_Version_number,
ref_article_id
FROM OKC_K_ARTICLES_B
WHERE id=p_k_art_id ;
cursor l_get_delete_var IS
select variable_code, object_Version_number from okc_k_art_variables
where variable_code not in (
select artvar.variable_code
from okc_k_art_variables artvar, okc_article_variables ar
where ar.variable_code = artvar.variable_code
and artvar.cat_id = p_k_art_id
and ar.article_version_id = x_article_version_id)
and cat_id = p_k_art_id;
cursor l_get_insert_var IS
select var.variable_code, bus.variable_type,bus.external_yn,bus.value_set_id
from okc_article_variables var, okc_bus_variables_b bus
where var.variable_code not in (
select artvar.variable_code
from okc_k_art_variables artvar, okc_article_variables ar
where ar.variable_code = artvar.variable_code
and artvar.cat_id = p_k_art_id
and ar.article_version_id = x_article_version_id
)
and var.article_version_id = x_article_version_id
and var.variable_code = bus.variable_code;
okc_debug.log('400: Before invoking OKC_K_ARTICLES_GRP.delete_article record for p_id = ' || p_k_art_id, 2);
G_PKG_NAME, '400: Before invoking OKC_K_ARTICLES_GRP.delete_article record for p_id = ' || p_k_art_id );
OKC_K_ARTICLES_GRP.delete_article(
p_api_version =>1,
p_init_msg_list => FND_API.G_FALSE,
p_validate_commit => FND_API.G_FALSE,
p_validation_string => NULL,
p_commit => FND_API.G_FALSE,
p_mode => p_mode, -- Other value 'AMEND'
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => p_k_art_id,
p_object_version_number => l_ovn); */
okc_debug.log('500: After invoking OKC_K_ARTICLES_GRP.delete_article record x_return_status = ' || x_return_status, 2);
G_PKG_NAME, '500: After invoking OKC_K_ARTICLES_GRP.delete_article record x_return_status = ' || x_return_status );
/* update okc_k_articles_b
set sav_sae_id = l_orig_article_id,
article_version_id = x_article_version_id
where id = p_k_art_id; */
OKC_K_ARTICLES_GRP.update_article(
p_api_version => 1.0,
p_init_msg_list => p_init_msg_list ,
p_validate_commit => p_validate_commit,
p_validation_string => p_validation_string,
p_commit => p_commit,
p_mode => p_mode, -- Other value 'AMEND'
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => p_k_art_id,
p_sav_sae_id => l_orig_article_id,
p_article_version_id => x_article_version_id,
p_object_version_number => NULL,
p_ref_article_id => G_MISS_NUM,
p_ref_article_version_id => G_MISS_NUM,
p_lock_terms_yn => p_lock_terms_yn);
Delete those variables that are not in the Standard Article Version
to which we are reverting to
*/
for del_var_csr in l_get_delete_var loop
OKC_K_ART_VARIABLES_PVT.Delete_Row(
x_return_status => x_return_status,
p_cat_id => p_k_art_id,
p_variable_code => del_var_csr.variable_code,
p_object_version_number => del_var_csr.object_version_number);
Insert those variables that are not in the Current Clause, but are
available in the Standard Article Version to which we are reverting to
*/
for ins_var_csr in l_get_insert_var loop
OKC_K_ART_VARIABLES_PVT.insert_row(
x_return_status => x_return_status,
p_cat_id => p_k_art_id,
p_variable_code => ins_var_csr.variable_code,
p_variable_type => ins_var_csr.variable_type,
p_external_yn => ins_var_csr.external_yn,
p_attribute_value_set_id => ins_var_csr.value_set_id,
p_variable_value_id => l_variable_value_id,
p_variable_value => l_variable_value,
x_cat_id => l_x_cat_id,
x_variable_code => l_x_variable_code);