[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
FROM DUAL;
SELECT B.VARIABLE_CODE, B.variable_type, TL.variable_name
FROM OKC_BUS_VARIABLES_TL TL, OKC_BUS_VARIABLES_B B
WHERE B.VARIABLE_INTENT = cp_intent
AND TL.LANGUAGE = cp_language
AND TL.VARIABLE_NAME = cp_extracted_var_name
AND DISABLED_YN <> 'Y'
AND B.VARIABLE_CODE = TL.VARIABLE_CODE;
SELECT VARIABLE_CODE, variable_type, variable_name
FROM OKC_VARIABLES_INTERFACE
WHERE batch_number = cp_batch_number
AND VARIABLE_INTENT = cp_intent
AND LANGUAGE = cp_language
AND VARIABLE_NAME = cp_extracted_var_name
AND DISABLED_YN <> 'Y'
AND nvl(process_status,'X') not in ('E');
if (p_calling_mode = 'CALLED_FROM_CREATE_UPDATE') then
-- DBMS_LOB.COPY(p_article_text, p_dest_clob, p_dest_clob_length, 1, 1);
SELECT OI2.ORG_INFORMATION3 SET_OF_BOOKS_ID
FROM HR_ORGANIZATION_INFORMATION OI1,
HR_ORGANIZATION_INFORMATION OI2,
HR_ALL_ORGANIZATION_UNITS OU
WHERE OI1.ORGANIZATION_ID = OU.ORGANIZATION_ID AND
OI2.ORGANIZATION_ID = OU.ORGANIZATION_ID AND
OI1.ORG_INFORMATION_CONTEXT = 'CLASS' AND
OI2.ORG_INFORMATION_CONTEXT = 'Operating Unit Information' AND
OI1.ORG_INFORMATION1 = 'OPERATING_UNIT'AND
OI1.ORGANIZATION_ID = cp_org_id
;
SELECT ARTICLE_NUMBER
FROM OKC_ARTICLES_ALL
WHERE ARTICLE_NUMBER = cp_article_number
AND ORG_ID = cp_org_id
AND ROWNUM < 2;
SELECT ORG_INFORMATION8 CLAUSE_SEQ
--nvl(ORG_INFORMATION8,'-99') CLAUSE_SEQ
FROM HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND ORGANIZATION_ID = cp_org_id
;
PROCEDURE delete_article_version(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_article_version_id IN NUMBER,
p_standard_yn IN VARCHAR2 := 'Y',
p_only_version IN VARCHAR2 := 'T',
p_adoption_type IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER := NULL
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_delete_article_version';
okc_debug.log('2200: Entered delete_article_version', 2);
SAVEPOINT g_delete_article_version_GRP;
OKC_ARTICLE_VERSIONS_PVT.Delete_Row(
x_return_status => x_return_status,
p_article_version_id => p_article_version_id,
p_object_version_number => p_object_version_number
);
DELETE FROM OKC_ARTICLE_VARIABLES
WHERE ARTICLE_VERSION_ID = p_article_version_id;
OKC_ADOPTIONS_GRP.DELETE_LOCAL_ADOPTION_DETAILS(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_only_local_version => p_only_version,
p_local_article_version_id => p_article_version_id,
p_local_org_id => G_CURRENT_ORG_ID
);
okc_debug.log('2300: Leaving delete_article_version', 2);
okc_debug.log('2400: Leaving delete_article_version: OKC_API.G_EXCEPTION_ERROR Exception', 2);
ROLLBACK TO g_delete_article_version_GRP;
okc_debug.log('2500: Leaving delete_article_version: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
ROLLBACK TO g_delete_article_version_GRP;
okc_debug.log('2600: Leaving delete_article_version because of EXCEPTION: '||sqlerrm, 2);
ROLLBACK TO g_delete_article_version_GRP;
END delete_article_version;
p_insert_by_reference IN VARCHAR2,
p_lock_text IN VARCHAR2,
p_global_yn IN VARCHAR2,
p_article_status IN VARCHAR2,
p_sav_release IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_std_article_version_id IN NUMBER,
p_display_name IN VARCHAR2,
p_translated_yn IN VARCHAR2,
p_article_description IN VARCHAR2,
p_date_approved IN DATE,
p_default_section IN VARCHAR2,
p_reference_source IN VARCHAR2,
p_reference_text IN VARCHAR2,
p_additional_instructions IN VARCHAR2,
p_variation_description IN VARCHAR2,
p_date_published IN DATE,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL
) IS
l_api_version CONSTANT NUMBER := 1;
p_insert_by_reference => p_insert_by_reference,
p_lock_text => p_lock_text,
p_global_yn => p_global_yn,
p_article_language => p_article_language,
p_article_status => l_article_status,
p_sav_release => p_sav_release,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_std_article_version_id => p_std_article_version_id,
p_display_name => p_display_name,
p_translated_yn => p_translated_yn,
p_article_description => p_article_description,
p_date_approved => p_date_approved,
p_default_section => p_default_section,
p_reference_source => p_reference_source,
p_reference_text => p_reference_text,
p_orig_system_reference_code => p_orig_system_reference_code,
p_orig_system_reference_id1 => p_orig_system_reference_id1,
p_orig_system_reference_id2 => p_orig_system_reference_id2,
p_additional_instructions => p_additional_instructions,
p_variation_description => p_variation_description,
p_date_published => p_date_published,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15
);
p_insert_by_reference IN VARCHAR2,
p_lock_text IN VARCHAR2,
p_global_yn IN VARCHAR2,
p_article_status IN VARCHAR2,
p_sav_release IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_std_article_version_id IN NUMBER,
p_display_name IN VARCHAR2,
p_translated_yn IN VARCHAR2,
p_article_description IN VARCHAR2,
p_date_approved IN DATE,
p_default_section IN VARCHAR2,
p_reference_source IN VARCHAR2,
p_reference_text IN VARCHAR2,
p_additional_instructions IN VARCHAR2,
p_variation_description IN VARCHAR2,
p_date_published IN DATE,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL,
p_v_orig_system_reference_code IN VARCHAR2,
p_v_orig_system_reference_id1 IN VARCHAR2,
p_v_orig_system_reference_id2 IN VARCHAR2,
p_global_article_version_id IN NUMBER := NULL,
x_article_version_id OUT NOCOPY NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
OKC_ARTICLES_ALL_PVT.Insert_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_article_title => p_article_title,
p_org_id => G_CURRENT_ORG_ID,
p_article_number => l_article_number,
p_standard_yn => p_standard_yn,
p_article_intent => p_article_intent,
p_article_language => p_article_language,
p_article_type => p_article_type,
p_orig_system_reference_code => p_orig_system_reference_code,
p_orig_system_reference_id1 => p_orig_system_reference_id1,
p_orig_system_reference_id2 => p_orig_system_reference_id2,
p_cz_transfer_status_flag => p_cz_transfer_status_flag,
x_article_number => x_article_number,
x_article_id => x_article_id
);
p_insert_by_reference => p_insert_by_reference,
p_lock_text => p_lock_text,
p_global_yn => p_global_yn,
p_article_language => p_article_language,
p_orig_system_reference_code => p_v_orig_system_reference_code,
p_orig_system_reference_id1 => p_v_orig_system_reference_id1,
p_orig_system_reference_id2 => p_v_orig_system_reference_id2,
p_article_status => p_article_status,
p_sav_release => p_sav_release,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_std_article_version_id => p_std_article_version_id,
p_display_name => p_display_name,
p_translated_yn => p_translated_yn,
p_article_description => p_article_description,
p_date_approved => p_date_approved,
p_default_section => p_default_section,
p_reference_source => p_reference_source,
p_reference_text => p_reference_text,
p_additional_instructions => p_additional_instructions,
p_variation_description => p_variation_description,
p_date_published => p_date_published,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
x_article_version_id => x_article_version_id
) ;
PROCEDURE update_article(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_article_id IN NUMBER,
p_article_title IN VARCHAR2,
p_article_number IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_article_intent IN VARCHAR2,
p_article_language IN VARCHAR2,
p_article_type IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2,
p_orig_system_reference_id1 IN VARCHAR2,
p_orig_system_reference_id2 IN VARCHAR2,
p_cz_transfer_status_flag IN VARCHAR2,
p_object_version_number IN NUMBER := NULL,
-- Article Version Attributes
p_article_version_id IN NUMBER,
p_article_text IN CLOB,
p_provision_yn IN VARCHAR2,
p_insert_by_reference IN VARCHAR2,
p_lock_text IN VARCHAR2,
p_global_yn IN VARCHAR2,
p_article_status IN VARCHAR2,
p_sav_release IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_std_article_version_id IN NUMBER,
p_display_name IN VARCHAR2,
p_translated_yn IN VARCHAR2,
p_article_description IN VARCHAR2,
p_date_approved IN DATE,
p_default_section IN VARCHAR2,
p_reference_source IN VARCHAR2,
p_reference_text IN VARCHAR2,
p_additional_instructions IN VARCHAR2,
p_variation_description IN VARCHAR2,
p_date_published IN DATE,
p_v_orig_system_reference_code IN VARCHAR2,
p_v_orig_system_reference_id1 IN VARCHAR2,
p_v_orig_system_reference_id2 IN VARCHAR2,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL,
p_v_object_version_number IN NUMBER := NULL
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_update_article';
okc_debug.log('1600: Entered update_article', 2);
SAVEPOINT g_update_article_GRP;
OKC_ARTICLES_ALL_PVT.Update_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
x_article_intent => l_article_intent,
p_article_id => p_article_id,
p_article_title => p_article_title,
p_org_id => G_CURRENT_ORG_ID,
p_article_number => p_article_number,
p_standard_yn => p_standard_yn,
p_article_intent => p_article_intent,
p_article_language => p_article_language,
p_article_type => p_article_type,
p_orig_system_reference_code => p_orig_system_reference_code,
p_orig_system_reference_id1 => p_orig_system_reference_id1,
p_orig_system_reference_id2 => p_orig_system_reference_id2,
p_cz_transfer_status_flag => p_cz_transfer_status_flag,
p_object_version_number => p_object_version_number
);
Update_Article_Version(
p_api_version => p_api_version ,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_commit => p_commit,
p_article_intent => l_article_intent,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_article_version_id => p_article_version_id,
p_article_id => p_article_id,
p_orig_system_reference_code => p_v_orig_system_reference_code,
p_orig_system_reference_id1 => p_v_orig_system_reference_id1,
p_orig_system_reference_id2 => p_v_orig_system_reference_id2,
p_article_text => p_article_text,
p_provision_yn => p_provision_yn,
p_insert_by_reference => p_insert_by_reference,
p_lock_text => p_lock_text,
p_global_yn => p_global_yn,
p_article_language => p_article_language,
p_article_status => p_article_status,
p_sav_release => p_sav_release,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_std_article_version_id => p_std_article_version_id,
p_display_name => p_display_name,
p_translated_yn => p_translated_yn,
p_article_description => p_article_description,
p_date_approved => p_date_approved,
p_default_section => p_default_section,
p_reference_source => p_reference_source,
p_reference_text => p_reference_text,
p_additional_instructions => p_additional_instructions,
p_variation_description => p_variation_description,
p_date_published => p_date_published,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_object_version_number => p_v_object_version_number
);
okc_debug.log('1800: Leaving update_article', 2);
okc_debug.log('1900: Leaving update_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
ROLLBACK TO g_update_article_GRP;
okc_debug.log('2000: Leaving update_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
ROLLBACK TO g_update_article_GRP;
okc_debug.log('2100: Leaving update_article because of EXCEPTION: '||sqlerrm, 2);
ROLLBACK TO g_update_article_GRP;
END update_article;
PROCEDURE delete_article(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_article_id IN NUMBER,
p_article_version_id IN NUMBER,
p_object_version_number IN NUMBER := NULL
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_delete_article';
SELECT article_status,standard_yn, adoption_type
FROM okc_article_versions av,okc_articles_all aa
WHERE aa.article_id = av.article_id
AND av.article_id = cp_article_id
AND av.article_version_id = cp_article_version_id;
SELECT 'F'
FROM OKC_ARTICLE_VERSIONS A
WHERE A.ARTICLE_ID = cp_article_id
AND A.ARTICLE_VERSION_ID <> cp_article_version_id
AND rownum < 2 ;
SELECT '1' from OKC_K_ARTICLES_B
WHERE sav_sae_id = cp_article_id
AND document_type = 'TEMPLATE';
okc_debug.log('2200: Entered delete_article', 2);
SAVEPOINT g_delete_article_GRP;
Okc_Debug.Log('2200: - Article Status is not Draft or Rejected,It cannot be deleted',2);
IF (OKC_XPRT_UTIL_PVT.ok_to_delete_clause(p_article_id) = 'N') THEN
IF (l_debug = 'Y') THEN
Okc_Debug.Log('2200: - Article Version is already Used in the Rules',2);
OKC_ARTICLES_ALL_PVT.Delete_Row(
x_return_status => x_return_status,
p_article_id => p_article_id,
p_object_version_number => NULL
);
DELETE FROM OKC_ARTICLE_RELATNS_ALL -- delete all relationships
WHERE source_article_id = p_article_id OR
target_article_id = p_article_id;
DELETE FROM OKC_FOLDER_CONTENTS -- delete all folder contents
WHERE member_id = p_article_id ;
OKC_ARTICLES_ALL_PVT.Delete_Row(
x_return_status => x_return_status,
p_article_id => p_article_id,
p_object_version_number => NULL
);
Delete_Article_Version(
p_api_version => p_api_version ,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_standard_yn => l_standard_yn,
p_adoption_type => l_adoption_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_article_version_id => p_article_version_id,
p_only_version => l_only_version,
p_object_version_number => p_object_version_number
);
okc_debug.log('2300: Leaving delete_article', 2);
okc_debug.log('2400: Leaving delete_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
ROLLBACK TO g_delete_article_GRP;
okc_debug.log('2500: Leaving delete_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
ROLLBACK TO g_delete_article_GRP;
okc_debug.log('2600: Leaving delete_article because of EXCEPTION: '||sqlerrm, 2);
ROLLBACK TO g_delete_article_GRP;
END delete_article;
p_insert_by_reference IN VARCHAR2,
p_lock_text IN VARCHAR2,
p_global_yn IN VARCHAR2,
p_article_language IN VARCHAR2,
p_article_status IN VARCHAR2,
p_sav_release IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_std_article_version_id IN NUMBER,
p_display_name IN VARCHAR2,
p_translated_yn IN VARCHAR2,
p_article_description IN VARCHAR2,
p_date_approved IN DATE,
p_default_section IN VARCHAR2,
p_reference_source IN VARCHAR2,
p_reference_text IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2 ,
p_orig_system_reference_id1 IN VARCHAR2 ,
p_orig_system_reference_id2 IN VARCHAR2 ,
p_additional_instructions IN VARCHAR2 ,
p_variation_description IN VARCHAR2,
p_date_published IN DATE,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL
) IS
l_api_version CONSTANT NUMBER := 1;
p_insert_by_reference => p_insert_by_reference,
p_lock_text => p_lock_text,
p_global_yn => p_global_yn,
p_article_language => p_article_language,
p_article_status => p_article_status,
p_sav_release => p_sav_release,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_std_article_version_id => p_std_article_version_id,
p_display_name => p_display_name,
p_translated_yn => p_translated_yn,
p_article_description => p_article_description,
p_date_approved => p_date_approved,
p_default_section => p_default_section,
p_reference_source => p_reference_source,
p_reference_text => p_reference_text,
p_orig_system_reference_code => p_orig_system_reference_code,
p_orig_system_reference_id1 => p_orig_system_reference_id1,
p_orig_system_reference_id2 => p_orig_system_reference_id2,
p_additional_instructions => p_additional_instructions,
p_variation_description => p_variation_description,
p_date_published => p_date_published,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_object_version_number => NULL
);
p_insert_by_reference IN VARCHAR2,
p_lock_text IN VARCHAR2,
p_global_yn IN VARCHAR2,
p_article_language IN VARCHAR2,
p_article_status IN VARCHAR2,
p_sav_release IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_std_article_version_id IN NUMBER,
p_display_name IN VARCHAR2,
p_translated_yn IN VARCHAR2,
p_article_description IN VARCHAR2,
p_date_approved IN DATE,
p_default_section IN VARCHAR2,
p_reference_source IN VARCHAR2,
p_reference_text IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2,
p_orig_system_reference_id1 IN VARCHAR2,
p_orig_system_reference_id2 IN VARCHAR2,
p_additional_instructions IN VARCHAR2,
p_variation_description IN VARCHAR2,
p_date_published IN DATE,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL,
x_article_version_id OUT NOCOPY NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
l_last_updated_by OKC_ARTICLE_VERSIONS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_ARTICLE_VERSIONS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_ARTICLE_VERSIONS.LAST_UPDATE_DATE%TYPE;
p_calling_mode => 'CALLED_FROM_CREATE_UPDATE',
p_replace_text => 'Y',
p_article_intent => p_article_intent,
p_language => USERENV('LANG'),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_variables_tbl => l_variable_code_tbl
) ;
OKC_ARTICLE_VERSIONS_PVT.Insert_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_article_id => p_article_id,
p_article_text => l_clob,
p_provision_yn => p_provision_yn,
p_insert_by_reference => p_insert_by_reference,
p_lock_text => p_lock_text,
p_global_yn => p_global_yn,
p_article_language => p_article_language,
p_article_status => l_article_status,
p_sav_release => p_sav_release,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_std_article_version_id => p_std_article_version_id,
p_display_name => p_display_name,
p_translated_yn => p_translated_yn,
p_article_description => p_article_description,
p_date_approved => p_date_approved,
p_default_section => p_default_section,
p_reference_source => p_reference_source,
p_reference_text => p_reference_text,
p_orig_system_reference_code => p_orig_system_reference_code,
p_orig_system_reference_id1 => p_orig_system_reference_id1,
p_orig_system_reference_id2 => p_orig_system_reference_id2,
p_additional_instructions => p_additional_instructions,
p_variation_description => p_variation_description,
p_date_published => p_date_published,
p_current_org_id => G_CURRENT_ORG_ID,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
x_earlier_adoption_type => l_earlier_adoption_type,
x_earlier_version_id => l_earlier_version_id,
x_article_version_id => x_article_version_id
);
INSERT INTO OKC_ARTICLE_VARIABLES
(
ARTICLE_VERSION_ID ,
VARIABLE_CODE ,
OBJECT_VERSION_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
x_article_version_id,
l_variable_code_tbl(i),
1.0,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id
);
UPDATE OKC_ARTICLE_VERSIONS
SET adoption_type = nvl(l_adoption_type, 'LOCAL')
WHERE article_version_id = x_article_version_id;
PROCEDURE update_article_variables (
p_article_version_id IN NUMBER,
p_variable_code_tbl IN variable_code_tbl_type,
p_do_dml IN VARCHAR2 := 'Y',
x_variables_to_insert_tbl OUT NOCOPY variable_code_tbl_type,
x_variables_to_delete_tbl OUT NOCOPY variable_code_tbl_type,
x_return_status OUT NOCOPY VARCHAR2) IS
i number := 0;
SELECT VARIABLE_CODE FROM OKC_ARTICLE_VARIABLES
WHERE ARTICLE_VERSION_ID = cp_article_version_id;
okc_debug.log('1750: Entered update_article_variables', 2);
x_variables_to_insert_tbl(k) := p_variable_code_tbl(i);
x_variables_to_delete_tbl(k) := l_existing_variables_tbl(j);
x_variables_to_delete_tbl := l_existing_variables_tbl;
IF x_variables_to_insert_tbl.COUNT > 0 Then
FORALL i in x_variables_to_insert_tbl.FIRST .. x_variables_to_insert_tbl.LAST
INSERT INTO OKC_ARTICLE_VARIABLES
(
ARTICLE_VERSION_ID ,
VARIABLE_CODE ,
OBJECT_VERSION_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
p_article_version_id,
x_variables_to_insert_tbl(i),
1.0,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id
);
IF x_variables_to_delete_tbl.COUNT > 0 Then
FORALL i in x_variables_to_delete_tbl.FIRST .. x_variables_to_delete_tbl.LAST
DELETE FROM OKC_ARTICLE_VARIABLES
WHERE VARIABLE_CODE = x_variables_to_delete_tbl(i)
AND ARTICLE_VERSION_ID = p_article_version_id;
Okc_Debug.Log('1750: Leaving Update Article variables successfully', 2);
okc_debug.log('1750: Leaving update_article_variables because of EXCEPTION: '||sqlerrm, 2);
PROCEDURE update_article_version(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_article_intent IN VARCHAR2,
p_article_version_id IN NUMBER,
p_article_id IN NUMBER,
p_article_text IN CLOB,
p_provision_yn IN VARCHAR2,
p_insert_by_reference IN VARCHAR2,
p_lock_text IN VARCHAR2,
p_global_yn IN VARCHAR2,
p_article_language IN VARCHAR2,
p_article_status IN VARCHAR2,
p_sav_release IN VARCHAR2,
p_start_date IN DATE,
p_end_date IN DATE,
p_std_article_version_id IN NUMBER,
p_display_name IN VARCHAR2,
p_translated_yn IN VARCHAR2,
p_article_description IN VARCHAR2,
p_date_approved IN DATE,
p_default_section IN VARCHAR2,
p_reference_source IN VARCHAR2,
p_reference_text IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2 := NULL,
p_orig_system_reference_id1 IN VARCHAR2 := NULL,
p_orig_system_reference_id2 IN VARCHAR2 := NULL,
p_additional_instructions IN VARCHAR2 := NULL,
p_variation_description IN VARCHAR2,
p_date_published IN DATE,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL,
p_object_version_number IN NUMBER := NULL
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_update_article_version';
l_variables_to_insert_tbl variable_code_tbl_type;
l_variables_to_delete_tbl variable_code_tbl_type;
okc_debug.log('1600: Entered update_article_version', 2);
SAVEPOINT g_update_article_version_GRP;
p_calling_mode => 'CALLED_FROM_CREATE_UPDATE',
p_replace_text => 'Y',
p_article_intent => p_article_intent,
p_language => USERENV('LANG'),
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_variables_tbl => l_variable_code_tbl
) ;
OKC_ARTICLE_VERSIONS_PVT.Update_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_article_version_id => p_article_version_id,
p_article_id => p_article_id,
p_article_version_number => NULL,
p_article_text => l_clob,
p_provision_yn => p_provision_yn,
p_insert_by_reference => p_insert_by_reference,
p_lock_text => p_lock_text,
p_global_yn => p_global_yn,
p_article_language => p_article_language,
p_article_status => l_article_status,
p_sav_release => p_sav_release,
p_start_date => p_start_date,
p_end_date => p_end_date,
p_std_article_version_id => p_std_article_version_id,
p_display_name => p_display_name,
p_translated_yn => p_translated_yn,
p_article_description => p_article_description,
p_date_approved => p_date_approved,
p_default_section => p_default_section,
p_reference_source => p_reference_source,
p_reference_text => p_reference_text,
p_orig_system_reference_code => p_orig_system_reference_code,
p_orig_system_reference_id1 => p_orig_system_reference_id1,
p_orig_system_reference_id2 => p_orig_system_reference_id2,
p_additional_instructions => p_additional_instructions,
p_variation_description => p_variation_description,
p_date_published => p_date_published,
p_current_org_id => G_CURRENT_ORG_ID,
p_attribute_category => p_attribute_category,
p_attribute1 => p_attribute1,
p_attribute2 => p_attribute2,
p_attribute3 => p_attribute3,
p_attribute4 => p_attribute4,
p_attribute5 => p_attribute5,
p_attribute6 => p_attribute6,
p_attribute7 => p_attribute7,
p_attribute8 => p_attribute8,
p_attribute9 => p_attribute9,
p_attribute10 => p_attribute10,
p_attribute11 => p_attribute11,
p_attribute12 => p_attribute12,
p_attribute13 => p_attribute13,
p_attribute14 => p_attribute14,
p_attribute15 => p_attribute15,
p_object_version_number => p_object_version_number,
x_article_status => l_article_status,
x_article_id => l_article_id,
x_earlier_version_id => l_earlier_version_id
);
update_article_variables (p_article_version_id => p_article_version_id,
p_variable_code_tbl => l_variable_code_tbl,
p_do_dml => 'Y',
x_variables_to_insert_tbl => l_variables_to_insert_tbl,
x_variables_to_delete_tbl => l_variables_to_delete_tbl,
x_return_status => x_return_status);
UPDATE OKC_ARTICLES_ALL
SET cz_transfer_status_flag = 'R'
WHERE ARTICLE_ID = l_article_id;
okc_debug.log('1800: Leaving update_article_version', 2);
okc_debug.log('1900: Leaving update_article_version: OKC_API.G_EXCEPTION_ERROR Exception', 2);
ROLLBACK TO g_update_article_version_GRP;
okc_debug.log('2000: Leaving update_article_version: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
ROLLBACK TO g_update_article_version_GRP;
okc_debug.log('2100: Leaving update_article_version because of EXCEPTION: '||sqlerrm, 2);
ROLLBACK TO g_update_article_version_GRP;
END update_article_version;
l_last_update_date OKC_ARTICLES_ALL.LAST_UPDATE_DATE%TYPE;
l_last_updated_by OKC_ARTICLES_ALL.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_ARTICLES_ALL.LAST_UPDATE_LOGIN%TYPE;
lv_insert_by_reference OKC_ARTICLE_VERSIONS.INSERT_BY_REFERENCE%TYPE;
lv_last_update_date OKC_ARTICLE_VERSIONS.LAST_UPDATE_DATE%TYPE;
lv_last_updated_by OKC_ARTICLE_VERSIONS.LAST_UPDATED_BY%TYPE;
lv_last_update_login OKC_ARTICLE_VERSIONS.LAST_UPDATE_LOGIN%TYPE;
SELECT aa.standard_yn,av.article_id
FROM OKC_ARTICLES_ALL aa,OKC_ARTICLE_VERSIONS av
WHERE aa.ARTICLE_ID = av.ARTICLE_ID
AND av.ARTICLE_VERSION_ID = cp_article_version_id;
SELECT
TARGET_ARTICLE_ID,
RELATIONSHIP_TYPE
FROM OKC_ARTICLE_RELATNS_ALL
WHERE source_article_id = cp_article_id
AND org_id = cp_org_id;
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login,
x_last_update_date => l_last_update_date
);
x_insert_by_reference => lv_insert_by_reference,
x_lock_text => lv_lock_text,
x_global_yn => lv_global_yn,
x_article_language => lv_article_language,
x_article_status => lv_article_status,
x_sav_release => lv_sav_release,
x_start_date => lv_start_date,
x_end_date => lv_end_date,
x_std_article_version_id => lv_std_article_version_id,
x_display_name => lv_display_name,
x_translated_yn => lv_translated_yn,
x_article_description => lv_article_description,
x_date_approved => lv_date_approved,
x_default_section => lv_default_section,
x_reference_source => lv_reference_source,
x_reference_text => lv_reference_text,
x_orig_system_reference_code => lv_orig_system_reference_code,
x_orig_system_reference_id1 => lv_orig_system_reference_id1,
x_orig_system_reference_id2 => lv_orig_system_reference_id2,
x_additional_instructions => lv_additional_instructions,
x_variation_description => lv_variation_description,
x_date_published => lv_date_published,
x_program_id => lv_program_id,
x_program_login_id => lv_program_login_id,
x_program_application_id => lv_program_application_id,
x_request_id => lv_request_id,
x_attribute_category => lv_attribute_category,
x_attribute1 => lv_attribute1,
x_attribute2 => lv_attribute2,
x_attribute3 => lv_attribute3,
x_attribute4 => lv_attribute4,
x_attribute5 => lv_attribute5,
x_attribute6 => lv_attribute6,
x_attribute7 => lv_attribute7,
x_attribute8 => lv_attribute8,
x_attribute9 => lv_attribute9,
x_attribute10 => lv_attribute10,
x_attribute11 => lv_attribute11,
x_attribute12 => lv_attribute12,
x_attribute13 => lv_attribute13,
x_attribute14 => lv_attribute14,
x_attribute15 => lv_attribute15,
x_object_version_number => lv_object_version_number,
x_created_by => lv_created_by,
x_creation_date => lv_creation_date,
x_last_updated_by => lv_last_updated_by,
x_last_update_login => lv_last_update_login,
x_last_update_date => lv_last_update_date
);
OKC_ARTICLES_ALL_PVT.Insert_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_article_title => nvl(p_new_article_title,l_article_title),
p_org_id => G_CURRENT_ORG_ID,
p_article_number => l_article_number,
p_standard_yn => p_create_standard_yn,
p_article_intent => l_article_intent,
p_article_language => l_article_language,
p_article_type => l_article_type,
p_orig_system_reference_code => l_orig_system_reference_code,
p_orig_system_reference_id1 => l_orig_system_reference_id1,
p_orig_system_reference_id2 => l_orig_system_reference_id2,
p_cz_transfer_status_flag => l_cz_transfer_status_flag,
p_attribute_category => l_attribute_category,
p_attribute1 => l_attribute1,
p_attribute2 => l_attribute2,
p_attribute3 => l_attribute3,
p_attribute4 => l_attribute4,
p_attribute5 => l_attribute5,
p_attribute6 => l_attribute6,
p_attribute7 => l_attribute7,
p_attribute8 => l_attribute8,
p_attribute9 => l_attribute9,
p_attribute10 => l_attribute10,
p_attribute11 => l_attribute11,
p_attribute12 => l_attribute12,
p_attribute13 => l_attribute13,
p_attribute14 => l_attribute14,
p_attribute15 => l_attribute15,
x_article_number => x_article_number,
x_article_id => x_article_id
);
OKC_ARTICLE_VERSIONS_PVT.Insert_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_article_id => x_article_id,
p_article_text => lv_article_text,
p_provision_yn => lv_provision_yn,
p_insert_by_reference => lv_insert_by_reference,
p_lock_text => lv_lock_text,
p_global_yn => lv_global_yn,
p_article_language => lv_article_language,
p_article_status => lv_article_status,
p_sav_release => lv_sav_release,
p_start_date => lv_start_date,
p_end_date => lv_end_date,
p_std_article_version_id => lv_std_article_version_id,
p_display_name => lv_display_name,
p_translated_yn => lv_translated_yn,
p_article_description => lv_article_description,
p_date_approved => NULL,
p_default_section => lv_default_section,
p_reference_source => lv_reference_source,
p_reference_text => lv_reference_text,
p_orig_system_reference_code => lv_orig_system_reference_code,
p_orig_system_reference_id1 => lv_orig_system_reference_id1,
p_orig_system_reference_id2 => lv_orig_system_reference_id2,
p_additional_instructions => lv_additional_instructions,
p_variation_description => lv_variation_description,
p_date_published => NULL,
p_current_org_id => G_CURRENT_ORG_ID,
p_attribute_category => lv_attribute_category,
p_attribute1 => lv_attribute1,
p_attribute2 => lv_attribute2,
p_attribute3 => lv_attribute3,
p_attribute4 => lv_attribute4,
p_attribute5 => lv_attribute5,
p_attribute6 => lv_attribute6,
p_attribute7 => lv_attribute7,
p_attribute8 => lv_attribute8,
p_attribute9 => lv_attribute9,
p_attribute10 => lv_attribute10,
p_attribute11 => lv_attribute11,
p_attribute12 => lv_attribute12,
p_attribute13 => lv_attribute13,
p_attribute14 => lv_attribute14,
p_attribute15 => lv_attribute15,
x_earlier_adoption_type => l_earlier_adoption_type,
x_earlier_version_id => l_earlier_version_id,
x_article_version_id => x_article_version_id
);
INSERT INTO OKC_ARTICLE_VARIABLES
(
ARTICLE_VERSION_ID ,
VARIABLE_CODE ,
OBJECT_VERSION_NUMBER ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT
x_article_version_id,
VARIABLE_CODE,
1.0,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id
FROM OKC_ARTICLE_VARIABLES
WHERE ARTICLE_VERSION_ID = p_article_version_id;
INSERT INTO OKC_ARTICLE_RELATNS_ALL
(
SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
ORG_ID,
RELATIONSHIP_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
VALUES
(
x_article_id,
l_target_article_id_tbl(i),
G_CURRENT_ORG_ID,
l_relationship_type_tbl(i),
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_login_Id,
sysdate
);
INSERT INTO OKC_ARTICLE_RELATNS_ALL
(
SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
ORG_ID,
RELATIONSHIP_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
VALUES
(
l_target_article_id_tbl(i),
x_article_id,
G_CURRENT_ORG_ID,
l_relationship_type_tbl(i),
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_login_Id,
sysdate
);
l_target_article_id_tbl.DELETE;
l_relationship_type_tbl.DELETE;
INSERT INTO OKC_FOLDER_CONTENTS
(
MEMBER_ID ,
FOLDER_ID ,
OBJECT_VERSION_NUMBER,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT
x_article_id,
folder_id,
1.0,
l_User_Id,
sysdate,
sysdate,
l_User_Id,
l_login_Id
FROM OKC_FOLDER_CONTENTS mem
WHERE MEMBER_ID = l_p_article_id
AND exists
(select 1 from okc_folders_all_b fold where
fold.org_id = G_CURRENT_ORG_ID
and fold.folder_id = mem.folder_id);
SELECT V2.article_id FROM OKC_ARTICLE_ADOPTIONS AA,
OKC_ARTICLE_VERSIONS V1 ,
OKC_ARTICLE_VERSIONS V2
WHERE V1.ARTICLE_VERSION_ID = AA.GLOBAL_ARTICLE_VERSION_ID
AND V2.ARTICLE_VERSION_ID = AA.LOCAL_ARTICLE_VERSION_ID
AND V1.ARTICLE_ID = cp_article_id
AND AA.LOCAL_ORG_ID = cp_local_org_id
AND AA.ADOPTION_TYPE = 'LOCALIZED'
AND V2.ARTICLE_STATUS = 'APPROVED'
AND NVL(V2.END_DATE, SYSDATE+1) > SYSDATE
AND rownum < 2
UNION ALL
SELECT V1.article_id FROM OKC_ARTICLE_ADOPTIONS AA,
OKC_ARTICLE_VERSIONS V1
WHERE V1.ARTICLE_VERSION_ID = AA.GLOBAL_ARTICLE_VERSION_ID
AND V1.ARTICLE_ID = cp_article_id
AND AA.LOCAL_ORG_ID = cp_local_org_id
AND AA.ADOPTION_TYPE = 'ADOPTED'
AND AA.ADOPTION_STATUS = 'APPROVED'
AND rownum < 2;
SELECT '1' FROM OKC_ARTICLE_RELATNS_ALL
WHERE source_article_id = cp_src_article_id
AND target_article_id = cp_tar_article_id
AND org_id = cp_local_org_id;
SELECT '1' FROM OKC_ARTICLE_ADOPTIONS AA1,
OKC_ARTICLE_VERSIONS V1,
OKC_ARTICLE_ADOPTIONS AA2,
OKC_ARTICLE_VERSIONS V2
WHERE V1.ARTICLE_VERSION_ID = AA1.GLOBAL_ARTICLE_VERSION_ID
AND V1.ARTICLE_ID = cp_src_article_id
AND AA1.LOCAL_ORG_ID = cp_local_org_id
AND AA1.ADOPTION_TYPE = 'ADOPTED'
AND V2.ARTICLE_VERSION_ID = AA2.GLOBAL_ARTICLE_VERSION_ID
AND V2.ARTICLE_ID = cp_tar_article_id
AND AA2.LOCAL_ORG_ID = AA1.LOCAL_ORG_ID
AND AA2.ADOPTION_TYPE = 'ADOPTED'
AND NOT EXISTS
(
SELECT '1' FROM OKC_ARTICLE_RELATNS_ALL
WHERE source_article_id = V1.ARTICLE_ID
AND target_article_id = V2.ARTICLE_ID
AND org_id = AA1.LOCAL_ORG_ID
);
SELECT ORGANIZATION_ID,
decode(nvl(ORG_INFORMATION1,'N'),'N','AVAILABLE','Y','ADOPTED') ADOPTION_TYPE ,
ORG_INFORMATION2
FROM HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND ORGANIZATION_ID <> G_GLOBAL_ORG_ID
AND NOT EXISTS
(
SELECT '1'
FROM OKC_ARTICLE_RELATNS_ALL R1
WHERE R1.SOURCE_ARTICLE_ID = cp_src_global_article_id AND
R1.TARGET_ARTICLE_ID = cp_tar_global_article_id AND
R1.RELATIONSHIP_TYPE = cp_relationship_type AND
R1.ORG_ID = ORGANIZATION_ID
);
SELECT '1'
FROM OKC_ARTICLES_ALL A, OKC_ARTICLES_ALL B
WHERE A.ARTICLE_ID = cp_src_global_article_id
AND B.ARTICLE_ID = cp_tar_global_article_id
AND EXISTS
(SELECT 1 FROM OKC_ARTICLE_VERSIONS V
WHERE V.ARTICLE_ID = B.ARTICLE_ID
AND V.GLOBAL_YN = 'Y'
AND V.ARTICLE_STATUS = 'APPROVED'
AND NVL(V.END_DATE,SYSDATE + 1) > SYSDATE
)
AND EXISTS
(SELECT 1 FROM OKC_ARTICLE_VERSIONS V1
WHERE V1.ARTICLE_ID = A.ARTICLE_ID
AND V1.GLOBAL_YN = 'Y'
AND V1.ARTICLE_STATUS = 'APPROVED'
AND NVL(V1.END_DATE,SYSDATE + 1) > SYSDATE
);
INSERT INTO OKC_ARTICLE_RELATNS_ALL
(
SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
ORG_ID,
RELATIONSHIP_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
SELECT
l_source_article_id_tbl(j),
l_target_article_id_tbl(j),
l_org_id_tbl(j),
p_relationship_type,
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_login_Id,
sysdate
FROM DUAL
WHERE l_source_article_id_tbl(j) IS NOT NULL;
INSERT INTO OKC_ARTICLE_RELATNS_ALL
(
SOURCE_ARTICLE_ID,
TARGET_ARTICLE_ID,
ORG_ID,
RELATIONSHIP_TYPE,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
)
SELECT
l_target_article_id_tbl(j),
l_source_article_id_tbl(j),
l_org_id_tbl(j),
p_relationship_type,
1.0,
l_User_Id,
sysdate,
l_User_Id,
l_Login_Id,
sysdate
FROM DUAL
WHERE l_source_article_id_tbl(j) IS NOT NULL;
l_target_article_id_tbl.DELETE;
l_source_article_id_tbl.DELETE;
l_org_id_tbl.DELETE;
l_adoption_type_tbl.DELETE;
l_notifier_tbl.DELETE;
l_last_updated_by OKC_ARTICLE_RELATNS_ALL.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_ARTICLE_RELATNS_ALL.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_ARTICLE_RELATNS_ALL.LAST_UPDATE_DATE%TYPE;
okc_debug.log('600: Entered insert_row', 2);
SAVEPOINT g_insert_row_GRP;
OKC_ARTICLE_RELATIONSHIPS_PVT.Insert_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_source_article_id => p_source_article_id,
p_target_article_id => p_target_article_id,
p_org_id => p_org_id,
p_relationship_type => p_relationship_type,
x_source_article_id => l_source_article_id,
x_target_article_id => l_target_article_id,
x_org_id => l_org_id
);
OKC_ARTICLE_RELATIONSHIPS_PVT.Insert_Row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_source_article_id => p_target_article_id,
p_target_article_id => p_source_article_id,
p_org_id => p_org_id,
p_relationship_type => p_relationship_type,
x_source_article_id => l_source_article_id,
x_target_article_id => l_target_article_id,
x_org_id => l_org_id
);
okc_debug.log('700: Leaving insert_row', 2);
okc_debug.log('800: Leaving insert_row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
ROLLBACK TO g_insert_row_GRP;
okc_debug.log('900: Leaving insert_row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
ROLLBACK TO g_insert_row_GRP;
okc_debug.log('1000: Leaving insert_row because of EXCEPTION: '||sqlerrm, 2);
ROLLBACK TO g_insert_row_GRP;
PROCEDURE DELETE_AUTO_ADOPTED_RELATIONS
(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_fetchsize IN NUMBER,
p_source_global_article_id IN NUMBER,
p_target_global_article_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
SELECT source_article_id, target_article_id, org_id
FROM OKC_ARTICLE_RELATNS_ALL REL
WHERE source_article_id = cp_source_global_article_id
AND target_article_id = cp_target_global_article_id;
( SELECT '1'
FROM HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION_CONTEXT = 'OKC_TERMS_LIBRARY_DETAILS'
AND ORGANIZATION_ID = rel.org_id
AND ORG_INFORMATION1 = 'Y');
DELETE FROM OKC_ARTICLE_RELATNS_ALL
WHERE source_article_id = l_source_article_id_tbl(j) AND
target_article_id = l_target_article_id_tbl(j) AND
org_id = l_org_id_tbl(j);
DELETE FROM OKC_ARTICLE_RELATNS_ALL
WHERE target_article_id = l_source_article_id_tbl(j) AND
source_article_id = l_target_article_id_tbl(j) AND
org_id = l_org_id_tbl(j);
l_org_id_tbl.DELETE;
l_source_article_id_tbl.DELETE;
l_target_article_id_tbl.DELETE;
END DELETE_AUTO_ADOPTED_RELATIONS;
PROCEDURE delete_article_relationship(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_source_article_id IN NUMBER,
p_target_article_id IN NUMBER,
p_org_id IN NUMBER,
p_object_version_number IN NUMBER := NULL
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_delete_row';
okc_debug.log('2200: Entered delete_row', 2);
SAVEPOINT g_delete_row_GRP;
OKC_ARTICLE_RELATIONSHIPS_PVT.Delete_Row(
x_return_status => x_return_status,
p_source_article_id => p_source_article_id,
p_target_article_id => p_target_article_id,
p_org_id => p_org_id,
p_object_version_number => p_object_version_number
);
OKC_ARTICLE_RELATIONSHIPS_PVT.Delete_Row(
x_return_status => x_return_status,
p_source_article_id => p_target_article_id,
p_target_article_id => p_source_article_id,
p_org_id => p_org_id,
p_object_version_number => p_object_version_number
);
DELETE FROM OKC_ARTICLE_RELATNS_ALL
WHERE SOURCE_ARTICLE_ID = p_source_article_id
AND TARGET_ARTICLE_ID = p_target_article_id;
DELETE FROM OKC_ARTICLE_RELATNS_ALL
WHERE SOURCE_ARTICLE_ID = p_target_article_id
AND TARGET_ARTICLE_ID = p_source_article_id;
DELETE_AUTO_ADOPTED_RELATIONS
(
x_return_status => x_return_status,
x_msg_count => x_msg_data,
x_msg_data => x_msg_data,
p_fetchsize => 100,
p_source_global_article_id => p_source_article_id,
p_target_global_article_id => p_target_article_id
) ;
okc_debug.log('2300: Leaving delete_row', 2);
okc_debug.log('2400: Leaving delete_Row: OKC_API.G_EXCEPTION_ERROR Exception', 2);
ROLLBACK TO g_delete_row_GRP;
okc_debug.log('2500: Leaving delete_Row: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
ROLLBACK TO g_delete_row_GRP;
okc_debug.log('2600: Leaving delete_Row because of EXCEPTION: '||sqlerrm, 2);
ROLLBACK TO g_delete_row_GRP;
END delete_article_relationship;
select
'Y'
from
okc_article_versions av
where
av.article_id = cp_article_id and
av.start_date > cp_start_date and
av.article_status in ( 'APPROVED', 'HOLD') and
rownum < 2;