[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL FROM DUAL;
x_last_updated_by OUT NOCOPY NUMBER,
x_last_update_login OUT NOCOPY NUMBER,
x_last_update_date OUT NOCOPY DATE
) RETURN VARCHAR2 IS
CURSOR OKC_REVIEW_TERMS_PK_CSR (cp_REVIEW_UPLD_TERMS_id IN NUMBER) IS
SELECT
DOCUMENT_ID,
DOCUMENT_TYPE,
OBJECT_ID,
OBJECT_TYPE,
OBJECT_TITLE,
OBJECT_TEXT,
PARENT_OBJECT_TYPE,
PARENT_ID,
ARTICLE_ID,
ARTICLE_VERSION_ID,
LABEL,
DISPLAY_SEQ,
ACTION,
ERROR_MESSAGE_COUNT,
WARNING_MESSAGE_COUNT,
OBJECT_VERSION_NUMBER,
NEW_PARENT_ID,
UPLOAD_LEVEL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE
FROM OKC_REVIEW_UPLD_TERMS t
WHERE t.REVIEW_UPLD_TERMS_ID = cp_REVIEW_UPLD_TERMS_id;
x_last_updated_by,
x_last_update_login,
x_last_update_date;
l_last_updated_by OKC_REVIEW_UPLD_TERMS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_DATE%TYPE;
x_last_updated_by => l_last_updated_by,
x_last_update_login => l_last_update_login,
x_last_update_date => l_last_update_date
);
SELECT '!'
FROM ??unknown_table??
WHERE ??REVIEW_UPLD_TERMS_ID?? = p_REVIEW_UPLD_TERMS_id;
SELECT '!'
FROM ??unknown_table??
WHERE ??DOCUMENT_ID?? = p_document_id;
SELECT '!'
FROM ??unknown_table??
WHERE ??OBJECT_ID?? = p_object_id;
SELECT '!'
FROM ??unknown_table??
WHERE ??PARENT_ID?? = p_parent_id;
SELECT '!'
FROM ??unknown_table??
WHERE ??ARTICLE_ID?? = p_article_id;
SELECT '!'
FROM ??unknown_table??
WHERE ??ARTICLE_VERSION_ID?? = p_article_version_id;
l_last_updated_by OKC_REVIEW_UPLD_TERMS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_DATE%TYPE;
FUNCTION Insert_Row(
p_REVIEW_UPLD_TERMS_id IN NUMBER,
p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_object_id IN NUMBER,
p_object_type IN VARCHAR2,
p_object_title IN CLOB,
p_object_text IN CLOB,
p_parent_object_type IN VARCHAR2,
p_parent_id IN NUMBER,
p_article_id IN NUMBER,
p_article_version_id IN NUMBER,
p_label IN VARCHAR2,
p_display_seq IN NUMBER,
p_action IN VARCHAR2,
p_error_message_count IN NUMBER,
p_warning_message_count IN NUMBER,
p_new_parent_id IN NUMBER,
p_upload_level IN NUMBER,
p_object_version_number IN NUMBER,
p_created_by IN NUMBER,
p_creation_date IN DATE,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_last_update_date IN DATE
) RETURN VARCHAR2 IS
BEGIN
IF (l_debug = 'Y') THEN
Okc_Debug.Log('3600: Entered Insert_Row function', 2);
INSERT INTO OKC_REVIEW_UPLD_TERMS(
REVIEW_UPLD_TERMS_ID,
DOCUMENT_ID,
DOCUMENT_TYPE,
OBJECT_ID,
OBJECT_TYPE,
OBJECT_TITLE,
OBJECT_TEXT,
PARENT_OBJECT_TYPE,
PARENT_ID,
ARTICLE_ID,
ARTICLE_VERSION_ID,
LABEL,
DISPLAY_SEQ,
ACTION,
ERROR_MESSAGE_COUNT,
WARNING_MESSAGE_COUNT,
OBJECT_VERSION_NUMBER,
NEW_PARENT_ID,
UPLOAD_LEVEL,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
VALUES (
p_REVIEW_UPLD_TERMS_id,
p_document_id,
p_document_type,
p_object_id,
p_object_type,
p_object_title,
p_object_text,
p_parent_object_type,
p_parent_id,
p_article_id,
p_article_version_id,
p_label,
p_display_seq,
p_action,
p_error_message_count,
p_warning_message_count,
p_object_version_number,
p_new_parent_id,
p_upload_level,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_login,
p_last_update_date);
Okc_Debug.Log('3700: Leaving Insert_Row', 2);
Okc_Debug.Log('3800: Leaving Insert_Row:OTHERS Exception', 2);
END Insert_Row;
PROCEDURE Insert_Row(
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
p_REVIEW_UPLD_TERMS_id IN NUMBER,
p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_object_id IN NUMBER,
p_object_type IN VARCHAR2,
p_object_title IN CLOB,
p_object_text IN CLOB,
p_parent_object_type IN VARCHAR2,
p_parent_id IN NUMBER,
p_article_id IN NUMBER,
p_article_version_id IN NUMBER,
p_label IN VARCHAR2,
p_display_seq IN NUMBER,
p_action IN VARCHAR2,
p_error_message_count IN NUMBER,
p_warning_message_count IN NUMBER,
p_new_parent_id IN NUMBER,
p_upload_level IN NUMBER,
x_REVIEW_UPLD_TERMS_id OUT NOCOPY NUMBER
) IS
l_object_version_number OKC_REVIEW_UPLD_TERMS.OBJECT_VERSION_NUMBER%TYPE;
l_last_updated_by OKC_REVIEW_UPLD_TERMS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_DATE%TYPE;
Okc_Debug.Log('4200: Entered Insert_Row', 2);
l_last_update_date := l_creation_date;
l_last_updated_by := l_created_by;
l_last_update_login := Fnd_Global.Login_Id;
Okc_Debug.Log('4300: Call the internal Insert_Row for Base Table', 2);
x_return_status := Insert_Row(
p_REVIEW_UPLD_TERMS_id => x_REVIEW_UPLD_TERMS_id,
p_document_id => p_document_id,
p_document_type => p_document_type,
p_object_id => p_object_id,
p_object_type => p_object_type,
p_object_title => p_object_title,
p_object_text => p_object_text,
p_parent_object_type => p_parent_object_type,
p_parent_id => p_parent_id,
p_article_id => p_article_id,
p_article_version_id => p_article_version_id,
p_label => p_label,
p_display_seq => p_display_seq,
p_action => p_action,
p_error_message_count => p_error_message_count,
p_warning_message_count => p_warning_message_count,
p_object_version_number => l_object_version_number,
p_new_parent_id => p_new_parent_id,
p_upload_level => p_upload_level,
p_created_by => l_created_by,
p_creation_date => l_creation_date,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_last_update_date => l_last_update_date
);
Okc_Debug.Log('4500: Leaving Insert_Row', 2);
Okc_Debug.Log('4600: Leaving Insert_Row:FND_API.G_EXC_ERROR Exception', 2);
Okc_Debug.Log('4700: Leaving Insert_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
Okc_Debug.Log('4800: Leaving Insert_Row because of EXCEPTION: '||sqlerrm, 2);
END Insert_Row;
SELECT object_version_number
FROM OKC_REVIEW_UPLD_TERMS
WHERE REVIEW_UPLD_TERMS_ID = cp_REVIEW_UPLD_TERMS_id
AND (object_version_number = cp_object_version_number OR cp_object_version_number IS NULL)
FOR UPDATE OF object_version_number NOWAIT;
SELECT object_version_number
FROM OKC_REVIEW_UPLD_TERMS
WHERE REVIEW_UPLD_TERMS_ID = cp_REVIEW_UPLD_TERMS_id;
Okc_Api.Set_Message(G_APP_NAME,G_RECORD_DELETED);
Okc_Api.Set_Message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
FUNCTION Update_Row(
p_REVIEW_UPLD_TERMS_id IN NUMBER,
p_document_id IN NUMBER,
p_document_type IN VARCHAR2,
p_object_id IN NUMBER,
p_object_type IN VARCHAR2,
p_object_title IN CLOB,
p_object_text IN CLOB,
p_parent_object_type IN VARCHAR2,
p_parent_id IN NUMBER,
p_article_id IN NUMBER,
p_article_version_id IN NUMBER,
p_label IN VARCHAR2,
p_display_seq IN NUMBER,
p_action IN VARCHAR2,
p_error_message_count IN NUMBER,
p_warning_message_count IN NUMBER,
p_new_parent_id IN NUMBER,
p_upload_level IN NUMBER,
p_object_version_number IN NUMBER,
p_last_updated_by IN NUMBER,
p_last_update_login IN NUMBER,
p_last_update_date IN DATE
) RETURN VARCHAR2 IS
BEGIN
IF (l_debug = 'Y') THEN
Okc_Debug.Log('6400: Entered Update_Row', 2);
UPDATE OKC_REVIEW_UPLD_TERMS
SET DOCUMENT_ID = p_document_id,
DOCUMENT_TYPE = p_document_type,
OBJECT_ID = p_object_id,
OBJECT_TYPE = p_object_type,
OBJECT_TITLE = p_object_title,
OBJECT_TEXT = p_object_text,
PARENT_OBJECT_TYPE = p_parent_object_type,
PARENT_ID = p_parent_id,
ARTICLE_ID = p_article_id,
ARTICLE_VERSION_ID = p_article_version_id,
LABEL = p_label,
DISPLAY_SEQ = p_display_seq,
ACTION = p_action,
ERROR_MESSAGE_COUNT = p_error_message_count,
WARNING_MESSAGE_COUNT = p_warning_message_count,
OBJECT_VERSION_NUMBER = p_object_version_number,
NEW_PARENT_ID = p_new_parent_id,
UPLOAD_LEVEL = p_upload_level,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_LOGIN = p_last_update_login,
LAST_UPDATE_DATE = p_last_update_date
WHERE REVIEW_UPLD_TERMS_ID = p_REVIEW_UPLD_TERMS_id;
Okc_Debug.Log('6500: Leaving Update_Row', 2);
Okc_Debug.Log('6600: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
END Update_Row;
PROCEDURE Update_Row(
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
p_REVIEW_UPLD_TERMS_id IN NUMBER,
p_document_id IN NUMBER := NULL,
p_document_type IN VARCHAR2 := NULL,
p_object_id IN NUMBER := NULL,
p_object_type IN VARCHAR2 := NULL,
p_object_title IN CLOB := NULL,
p_object_text IN CLOB := NULL,
p_parent_object_type IN VARCHAR2 := NULL,
p_parent_id IN NUMBER := NULL,
p_article_id IN NUMBER := NULL,
p_article_version_id IN NUMBER := NULL,
p_label IN VARCHAR2 := NULL,
p_display_seq IN NUMBER := NULL,
p_action IN VARCHAR2 := NULL,
p_error_message_count IN NUMBER := NULL,
p_warning_message_count IN NUMBER := NULL,
p_new_parent_id IN NUMBER := NULL,
p_upload_level IN NUMBER := NULL,
p_object_version_number IN NUMBER
) IS
l_document_id OKC_REVIEW_UPLD_TERMS.DOCUMENT_ID%TYPE;
l_last_updated_by OKC_REVIEW_UPLD_TERMS.LAST_UPDATED_BY%TYPE;
l_last_update_login OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_LOGIN%TYPE;
l_last_update_date OKC_REVIEW_UPLD_TERMS.LAST_UPDATE_DATE%TYPE;
Okc_Debug.Log('7000: Entered Update_Row', 2);
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
x_return_status := Update_Row(
p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id,
p_document_id => l_document_id,
p_document_type => l_document_type,
p_object_id => l_object_id,
p_object_type => l_object_type,
p_object_title => l_object_title,
p_object_text => l_object_text,
p_parent_object_type => l_parent_object_type,
p_parent_id => l_parent_id,
p_article_id => l_article_id,
p_article_version_id => l_article_version_id,
p_label => l_label,
p_display_seq => l_display_seq,
p_action => l_action,
p_error_message_count => l_error_message_count,
p_warning_message_count => l_warning_message_count,
p_object_version_number => l_object_version_number,
p_new_parent_id => l_new_parent_id,
p_upload_level => l_upload_level,
p_last_updated_by => l_last_updated_by,
p_last_update_login => l_last_update_login,
p_last_update_date => l_last_update_date
);
Okc_Debug.Log('7800: Leaving Update_Row', 2);
Okc_Debug.Log('7900: Leaving Update_Row:FND_API.G_EXC_ERROR Exception', 2);
Okc_Debug.Log('8000: Leaving Update_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
Okc_Debug.Log('8100: Leaving Update_Row because of EXCEPTION: '||sqlerrm, 2);
END Update_Row;
FUNCTION Delete_Row(
p_REVIEW_UPLD_TERMS_id IN NUMBER
) RETURN VARCHAR2 IS
BEGIN
IF (l_debug = 'Y') THEN
Okc_Debug.Log('8200: Entered Delete_Row', 2);
DELETE FROM OKC_REVIEW_UPLD_TERMS
WHERE REVIEW_UPLD_TERMS_ID = p_REVIEW_UPLD_TERMS_ID;
Okc_Debug.Log('8300: Leaving Delete_Row', 2);
Okc_Debug.Log('8400: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
END Delete_Row;
PROCEDURE Delete_Row(
x_return_status OUT NOCOPY VARCHAR2,
p_REVIEW_UPLD_TERMS_id IN NUMBER,
p_object_version_number IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'B_Delete_Row';
Okc_Debug.Log('8800: Entered Delete_Row', 2);
x_return_status := Delete_Row( p_REVIEW_UPLD_TERMS_id => p_REVIEW_UPLD_TERMS_id );
Okc_Debug.Log('9300: Leaving Delete_Row', 2);
Okc_Debug.Log('9400: Leaving Delete_Row:FND_API.G_EXC_ERROR Exception', 2);
Okc_Debug.Log('9500: Leaving Delete_Row:FND_API.G_EXC_UNEXPECTED_ERROR Exception', 2);
Okc_Debug.Log('9600: Leaving Delete_Row because of EXCEPTION: '||sqlerrm, 2);
END Delete_Row;
SELECT review_upld_terms_id,
level upload_level,
object_id,
object_type,
object_text,
parent_object_type,
parent_id,
article_id,
article_version_id,
object_version_number,
label,
display_seq,
action,
non_standard_flag,
mandatory_flag,
lock_text,
new_parent_id,
object_title,
old_review_upld_terms_id
FROM okc_review_upld_terms
WHERE document_id = p_document_id
AND document_type = p_document_type
CONNECT BY PRIOR review_upld_terms_id = new_parent_id
START WITH new_parent_id is null
ORDER SIBLINGS BY review_upld_terms_id;
SELECT review_upld_terms_id,
level upload_level,
object_id,
object_type,
object_text,
parent_object_type,
parent_id,
article_id,
article_version_id,
object_version_number,
label,
display_seq,
action,
non_standard_flag,
mandatory_flag,
lock_text,
new_parent_id,
object_title,
old_review_upld_terms_id --,
--OKC_WORD_DOWNLOAD_UPLOAD.UPLOAD_POST_PROCESSOR(p_document_id,p_document_type,object_id)
--OKC_WORD_DOWNLOAD_UPLOAD.GET_LATEST_WMLBLOB(p_document_id,p_document_type, object_id)
FROM okc_review_upld_terms
WHERE document_id = p_document_id
AND document_type = p_document_type
CONNECT BY PRIOR review_upld_terms_id = new_parent_id
START WITH new_parent_id is null
ORDER SIBLINGS BY review_upld_terms_id;
select doc_numbering_scheme from okc_template_usages
where document_type = p_document_type and
document_id = p_document_id;
SELECT 'Y'
from okc_article_versions av, okc_review_upld_terms ar
where av.article_version_id = ar.article_version_id
and av.insert_by_reference = 'Y'
and ar.review_upld_terms_id= p_review_upld_terms_id;
SELECT 'Y'
from okc_k_articles_b akb, okc_review_upld_terms ar
where akb.id = ar.object_id
and akb.mandatory_yn = 'Y'
and ar.review_upld_terms_id= p_review_upld_terms_id;
SELECT 'Y'
from okc_article_versions av, okc_review_upld_terms ar
where av.article_version_id = ar.article_version_id
and av.lock_text = 'Y'
and ar.review_upld_terms_id= p_review_upld_terms_id;
SELECT
aa.article_type
from okc_articles_all aa, okc_review_upld_terms rev
where
rev.article_id = aa.article_id
and rev.review_upld_terms_id = p_review_upld_terms_id;
UPDATE okc_word_sync_t
SET cat_id = NULL
WHERE doc_id = p_document_id
AND doc_type = p_document_type
AND action = 'ADDEDASSIGNED';
OKC_WORD_DOWNLOAD_UPLOAD.INSERT_WML_TEXT(l_article_version_id,obj_texts_wml(i));
ELSIF actions(i) = 'UPDATED' THEN
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'11900: Action is UPDATED');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12000: B4 call OKC_TERMS_SECTIONS_GRP.update_section');
OKC_TERMS_SECTIONS_GRP.update_section(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_validate_commit => p_validate_commit,
p_validation_string => p_validation_string,
p_commit => p_commit,
p_mode => p_mode,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => obj_ids(i),
p_section_sequence => l_display_sequence,
p_label => NULL,
p_scn_id => l_ref_id,
p_heading => substr(to_char(obj_titles(i)),1,80),
p_description => NULL,
p_scn_code => FND_API.G_MISS_CHAR,
p_object_version_number => NULL);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12100: After OKC_TERMS_SECTIONS_GRP.update_section');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12400: B4 call OKC_K_NON_STD_ART_GRP.update_non_std_article');
OKC_K_NON_STD_ART_GRP.update_non_std_article(
p_api_version => p_api_version,
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,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_article_title => substr(to_char(obj_titles(i)),1,80),
p_article_type => l_article_type,
-- Article Version Attributes
p_article_text => obj_texts(i),
p_provision_yn => 'N',
p_article_description => NULL,
p_display_name => substr(to_char(obj_titles(i)),1,450),
-- K Article Attributes
p_doc_type => p_document_type,
p_doc_id => p_document_id,
p_cat_id => obj_ids(i),
p_amendment_description => NULL,
p_print_text_yn => NULL,
x_cat_id => l_cat_id,
x_article_version_id => l_article_version_id ) ;
OKC_WORD_DOWNLOAD_UPLOAD.INSERT_WML_TEXT(l_article_version_id,obj_texts_wml(i));
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'12500: After OKC_K_NON_STD_ART_GRP.update_non_std_article');
UPDATE okc_k_articles_b
SET scn_id = l_ref_id
WHERE id = obj_ids(i);
OKC_WORD_DOWNLOAD_UPLOAD.INSERT_WML_TEXT(l_article_version_id,obj_texts_wml(i));
ELSIF (actions(i) = 'DELETED' OR actions(i) = 'MERGED') THEN
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'13300: Action='||actions(i));
UPDATE okc_sections_b scn
SET scn.last_updated_by = fnd_global.user_id,
scn.last_update_date = sysdate,
scn.amendment_operation_code = 'DELETED',
scn.summary_amend_operation_code =
okc_terms_util_pvt.get_summary_amend_code(
scn.summary_amend_operation_code,
scn.amendment_operation_code,
'DELETED'),
scn.last_amended_by = fnd_global.user_id,
scn.last_amendment_date = sysdate
WHERE scn.id = obj_ids(i);
DELETE FROM okc_sections_b
WHERE id = obj_ids(i);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14000: Before invoking delete on okc_k_articles_b');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14100: B4 delete k_art mode=AMEND, objectID='||obj_ids(i));
UPDATE okc_k_articles_b kart
SET kart.last_updated_by = fnd_global.user_id,
kart.last_update_date = sysdate,
kart.amendment_operation_code = 'DELETED',
kart.summary_amend_operation_code =
okc_terms_util_pvt.get_summary_amend_code(
kart.summary_amend_operation_code,
kart.amendment_operation_code,
'DELETED'),
kart.last_amended_by = fnd_global.user_id,
kart.last_amendment_date = sysdate
WHERE kart.id = obj_ids(i);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14200: after delete k_art, objectID='||obj_ids(i));
DELETE FROM okc_k_articles_b
WHERE id = obj_ids(i);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14300: After delete on okc_k_articles_b');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14400: Before Bulk update of okc_sections_b');
UPDATE okc_sections_b
SET scn_id = pobj_ids(i),
section_sequence = disp_seqs(i)
WHERE id = obj_ids(i)
AND obj_types(i) = 'SECTION';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14500: Before Bulk update of okc_k_articles_b');
UPDATE okc_k_articles_b
SET scn_id = pobj_ids(i),
display_sequence = disp_seqs(i)
WHERE id = obj_ids(i)
AND obj_types(i) = 'ARTICLE';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14600: Before delete on Review Tables');
OKC_TEMPLATE_USAGES_GRP.update_template_usages(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list ,
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_document_type => p_document_type,
p_document_id => p_document_id,
p_lock_terms_flag => 'N');
delete from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
document_id = p_document_id;
delete from okc_review_upld_header where document_type = p_document_type and
document_id = p_document_id;
delete from okc_review_messages where REVIEW_UPLD_TERMS_ID
in (select REVIEW_UPLD_TERMS_ID from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
document_id = p_document_id);
delete from OKC_REVIEW_VAR_VALUES where REVIEW_UPLD_TERMS_ID
in (select REVIEW_UPLD_TERMS_ID from okc_REVIEW_UPLD_TERMS where document_type = p_document_type and
document_id = p_document_id);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'14700: After delete on Review Tables');
DELETE FROM okc_word_sync_t WHERE doc_id=p_document_id AND doc_type=p_document_type;
OKC_REVIEW_UPLD_TERMS_PVT.delete_uploaded_terms(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list ,
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_document_type => p_document_type,
p_document_id => p_document_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete_uploaded_terms');
DELETE FROM okc_word_sync_t WHERE doc_id=p_document_id AND doc_type=p_document_type;
PROCEDURE Delete_Uploaded_Terms (
p_api_version IN NUMBER,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_document_type IN VARCHAR2,
p_document_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Uploaded_Terms';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entering Delete_Uploaded_Terms');
SAVEPOINT g_delete_uploaded_terms;
DELETE
FROM OKC_REVIEW_MESSAGES m
WHERE m.REVIEW_UPLD_TERMS_id IN
(SELECT REVIEW_UPLD_TERMS_id
FROM okc_REVIEW_UPLD_TERMS
WHERE document_id = p_document_id
AND document_type = p_document_type);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'200: After Delete Review Messages');
DELETE
FROM OKC_REVIEW_VAR_VALUES v
WHERE v.REVIEW_UPLD_TERMS_id IN
(SELECT REVIEW_UPLD_TERMS_id
FROM okc_REVIEW_UPLD_TERMS
WHERE document_id = p_document_id
AND document_type = p_document_type);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'300: After delete review variables');
DELETE
FROM OKC_REVIEW_UPLD_TERMS
WHERE document_id = p_document_id
AND document_type = p_document_type;
DELETE
FROM OKC_REVIEW_UPLD_HEADER
WHERE document_id = p_document_id
AND document_type = p_document_type;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete review terms');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Delete_Uploaded_Terms: OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO g_delete_uploaded_terms;
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving Delete_Uploaded_terms: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO g_delete_uploaded_terms;
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'700: Leaving delete_uploaded_Terms because of EXCEPTION: '||sqlerrm);
ROLLBACK TO g_delete_uploaded_terms;
END delete_uploaded_terms;
select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS
where document_type = p_document_type and document_id = p_document_id
and object_id = p_document_id and object_type = p_document_type;
select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
new_parent_id from okc_REVIEW_UPLD_TERMS
where document_type = p_document_type
and document_id = p_document_id
and action = 'DELETED'
and new_parent_id is null
order by object_type ;
select 'Y'
from OKC_REVIEW_UPLD_TERMS
where document_type = p_document_type
and document_id = p_document_id
and ( (object_type = 'ARTICLE'
and new_parent_id IS NULL)
OR (object_type = 'ARTICLE'
and new_parent_id = (select review_upld_terms_id
from okc_review_upld_terms
where document_type = p_document_type
and document_id = p_document_id
and object_id = p_document_id
and object_type = p_document_type))
OR (object_type = 'SECTION' and new_parent_id IS NULL)
);
select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
new_parent_id from okc_REVIEW_UPLD_TERMS
where document_type = p_document_type and document_id = p_document_id
and object_type = 'ARTICLE' and new_parent_id IS NULL and article_id is null and article_version_id is null;
select REVIEW_UPLD_TERMS_id, object_id, object_type, parent_id, parent_object_type,
new_parent_id
from okc_REVIEW_UPLD_TERMS
where document_type = p_document_type
and document_id = p_document_id
and ((object_type = 'ARTICLE'
and new_parent_id IS NULL
and article_id is not null
and article_version_id is not null)
OR (object_type = 'SECTION' and new_parent_id IS NULL));
select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS
where document_type = p_document_type and document_id = p_document_id
and object_type = 'SECTION' and to_char(object_title) = Okc_Util.Decode_Lookup('OKC_ARTICLE_SECTION',G_UNASSIGNED_SECTION_CODE);
select rev_var.variable_value_id, rev_var.REVIEW_UPLD_TERMS_id, rev_var.variable_name,
rev_var.language, rev_var.variable_code, rev_var.variable_type, rev_var.attribute_value_set_id,
rev_var.variable_value_id
from OKC_REVIEW_VAR_VALUES rev_var, okc_REVIEW_UPLD_TERMS rev
where rev_var.REVIEW_UPLD_TERMS_id = rev.REVIEW_UPLD_TERMS_id
and rev.document_type = p_document_type and rev.document_id = p_document_id;
select art_var.cat_id, art_var.variable_code, art_var.variable_type, art_var.external_yn,
art_var.attribute_value_set_id,art_var.variable_value_id, art_var.variable_value
from okc_k_art_variables art_var, okc_k_articles_b kart
where art_var.cat_id = kart.id
and kart.document_type = p_document_type and kart.document_id = p_document_id;
SELECT rev_var.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,rev_var.variable_code variable_code,
rev_var.variable_name variable_name,
kart_var.variable_value kart_variable_value, rev_var.variable_value rev_variable_value,
kart_var.variable_type, rev_var.variable_value_id
FROM OKC_REVIEW_VAR_VALUES rev_var, okc_k_art_variables kart_var,
OKC_REVIEW_UPLD_TERMS rev, okc_article_versions av
WHERE rev.object_id = kart_var.cat_id
AND rev.REVIEW_UPLD_TERMS_id = rev_var.REVIEW_UPLD_TERMS_id
AND kart_var.variable_code = rev_var.variable_code
AND av.article_version_id = rev.article_version_id
AND ((kart_var.variable_type='U' and kart_var.variable_value is null
and (kart_var.variable_value is null
and (rev_var.variable_value is not null and rev_var.variable_value <> '_________' )
and exists(select 'x' from okc_bus_variables_tl bustl where bustl.variable_code = rev_var.variable_code)) OR
(kart_var.variable_value is not null and rev_var.variable_value <> kart_var.variable_value))
OR (kart_var.variable_type<>'U' and exists
(select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
and var_doc.doc_Type = p_document_type))
)
and rev.document_type = p_document_type and rev.document_id = p_document_id
and nvl(rev.action,'NOCHANGE') not in ('DELETED','MERGED')
and nvl(av.insert_by_reference,'N') <> 'Y';
select rev.review_upld_terms_id, kart_var.variable_code, variable_name, language, description
from okc_k_art_variables kart_var, okc_review_upld_terms rev, okc_bus_variables_tl bustl, okc_article_versions av
where rev.object_id = kart_var.cat_id
and rev.document_type = p_document_type and rev.document_id = p_document_id
and rev.article_version_id = av.article_version_id
and kart_var.variable_code not in (select variable_code from okc_review_Var_values rev_var
where rev_var.review_upld_terms_id = rev.review_upld_terms_id)
and rev.action not in ('DELETED','MERGED')
and bustl.variable_code = kart_var.variable_code
and language = userenv('LANG')
and nvl(av.insert_by_reference,'N') <> 'Y';
select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
decode(rev_var.variable_value,
NULL, 'N',
'Y') modified
from okc_review_upld_Terms rev, okc_review_var_values rev_var, okc_article_versions av
where rev.document_type = p_document_type and rev.document_id = p_document_id
and rev_var.review_upld_terms_id = rev.review_upld_terms_id
and av.article_version_id = rev.article_version_id
and not exists(
select 'x' from okc_k_art_variables kart_var, okc_review_upld_Terms rev_upld where
rev_upld.review_upld_Terms_id = rev_var.review_upld_terms_id
and rev_upld.object_id = kart_var.cat_id
and rev_var.variable_code = kart_var.variable_code
and (
(rev_var.variable_type = 'U'
and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
and busb.variable_intent = p_intent)
)
OR
(rev_var.variable_type <> 'U'
and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
and var_doc.doc_type = p_document_type)
)
)
)
and rev.action not in ('DELETED','MERGED')
and nvl(av.insert_by_reference,'N') <> 'Y';
select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type
from okc_review_upld_terms rev, okc_review_var_values rev_var
where document_type = p_document_type and rev.document_id = p_document_id
and rev_var.review_upld_terms_id = rev.review_upld_terms_id
and ((rev_var.variable_type in ('S','D') and not exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
and var_doc.doc_type = p_document_type))
OR (rev_var.variable_type = 'U' and not exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
and busb.variable_intent = p_intent))
)
and rev.action not in ('DELETED','MERGED') ;
select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
decode(rev_var.variable_value,
NULL, 'N', 'Y') modified
from okc_review_upld_Terms rev, okc_review_var_values rev_var
where rev.document_type = p_document_type and rev.document_id = p_document_id
and rev_var.review_upld_terms_id = rev.review_upld_terms_id
and (
(rev_var.variable_type = 'U'
and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
and busb.variable_intent = p_intent)
)
OR
(rev_var.variable_type <> 'U'
and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
and var_doc.doc_type = p_document_type)
)
)
and rev.action = 'ADDED' ;
select rev.review_upld_terms_id, rev_var.variable_code, rev_var.variable_type,
decode(rev_var.variable_value,
NULL, 'N', 'Y') modified
from okc_review_upld_terms rev, okc_review_var_values rev_var, okc_article_versions av
where rev.document_type = p_document_type and rev.document_id = p_document_id
and rev_var.review_upld_terms_id = rev.review_upld_terms_id
and av.article_version_id = rev.article_version_id
and (
(rev_var.variable_type = 'U'
and exists (select 'x' from okc_bus_variables_b busb where busb.variable_code = rev_var.variable_code
and busb.variable_intent = p_intent)
)
OR
(rev_var.variable_type <> 'U'
and exists (select 'x' from okc_variable_doc_types var_doc where var_doc.variable_code = rev_var.variable_code
and var_doc.doc_type = p_document_type)
)
)
and nvl(av.insert_by_reference,'N') = 'Y';
SELECT rev.object_title, rev.REVIEW_UPLD_TERMS_id, rev.object_type
from okc_REVIEW_UPLD_TERMS rev
where document_type = p_document_type and
document_id = p_document_id and
object_title is null
and object_type IN ('ARTICLE', 'SECTION');
select doc_numbering_scheme from okc_template_usages
where document_type = p_document_type and
document_id = p_document_id;
SELECT 'Y', ACTION, review_upld_terms_id, object_title
from okc_article_versions av, okc_review_upld_terms ar
where av.article_version_id = ar.article_version_id
and av.insert_by_reference = 'Y'
and ar.document_type = p_document_type
and ar.document_id = p_document_id
and ar.object_type = 'ARTICLE'
and ar.action = 'UPDATED'
and p_user_access <> 'SUPER_USER';
SELECT 'Y', ACTION, review_upld_terms_id, object_title
from okc_k_articles_b akb, okc_review_upld_terms ar
where akb.id = ar.object_id
and akb.mandatory_yn = 'Y'
and ar.document_type = p_document_type
and ar.document_id = p_document_id
and ar.object_type = 'ARTICLE'
and ar.action = 'DELETED'
and p_user_access <> 'SUPER_USER';
SELECT 'Y', ACTION, review_upld_terms_id, object_title
from okc_article_versions av, okc_review_upld_terms ar
where av.article_version_id = ar.article_version_id
and av.lock_text = 'Y'
and ar.document_type = p_document_type
and ar.document_id = p_document_id
and ar.object_type = 'ARTICLE'
and ar.action = 'UPDATED'
and p_user_access <> 'SUPER_USER';
CURSOR update_err_warn_csr is
select
count(*) err_warn_count,
rev_msg.review_upld_terms_id,
rev_msg.error_severity
from
okc_review_messages rev_msg ,
okc_review_upld_terms rev_trm
where
rev_msg.review_upld_terms_id = rev_trm.review_upld_terms_id
and rev_trm.document_type = p_document_type
and rev_trm.document_id = p_document_id
group by rev_msg.review_upld_terms_id, rev_msg.error_severity;
cursor get_updated_articles_csr is
select review_upld_terms_id,
object_title,
regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ltrim(ut.object_text),'&NBSP;| ',' '),
and ut.action = 'UPDATED'
and ut.document_type = p_document_type
and ut.document_id = p_document_id;
select review_upld_terms_id,
object_title, object_type
from okc_review_upld_terms rev
where rev.document_type = p_document_type
and rev.document_id = p_document_id
and rev.object_type in ('SECTION','ARTICLE')
and rev.action in ('ADDED','UPDATED')
and ((object_type = 'SECTION' and length(to_char(object_title)) > 80) OR (object_type = 'ARTICLE' and length(to_char(object_title)) > 450)) ;
select review_upld_terms_id, object_type, object_id, object_title, action, display_seq, new_parent_id
from okc_review_upld_Terms
where document_type = p_document_type
and document_id = p_document_id
and nvl(action,'XXX') <> 'DELETED'
start with new_parent_id is null
connect by prior review_upld_terms_id = new_parent_id
order siblings by review_upld_terms_id;
select review_upld_terms_id
from okc_review_upld_Terms
where document_type = p_document_type
and document_id = p_document_id ;
cursor deleted_terms_csr is
select new_parent_id,
display_seq
from okc_review_upld_Terms
where document_type = p_document_type
and document_id = p_document_id
and action = 'DELETED';
select intent from okc_bus_doc_types_b
where document_type = p_document_type;
INSERT into okc_REVIEW_UPLD_TERMS(
REVIEW_UPLD_TERMS_ID,
DOCUMENT_ID,
DOCUMENT_TYPE,
OBJECT_ID,
OBJECT_TYPE,
OBJECT_TITLE,
OBJECT_TEXT,
PARENT_OBJECT_TYPE,
PARENT_ID,
ARTICLE_ID,
ARTICLE_VERSION_ID,
OBJECT_VERSION_NUMBER,
LABEL,
DISPLAY_SEQ,
ACTION,
ERROR_MESSAGE_COUNT,
WARNING_MESSAGE_COUNT,
NEW_PARENT_ID,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(
SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
P_DOCUMENT_ID,
P_DOCUMENT_TYPE,
p_document_id,
P_DOCUMENT_TYPE,
okc_terms_util_pvt.get_message('OKC','OKC_TERMS_CONTRACT_TERMS'),
null,
null,
null,
null,
null,
1,
null,
null,
null,
null,
null,
null,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE from dual);
update okc_REVIEW_UPLD_TERMS
set new_parent_id = (select REVIEW_UPLD_TERMS_id from okc_REVIEW_UPLD_TERMS where
document_type = p_document_type and document_id = p_document_id
and object_type = p_document_type and object_id = p_document_id)
where document_type = p_document_type and document_id = p_document_id
and object_type = 'SECTION' and new_parent_id = p_document_id;
update okc_REVIEW_UPLD_TERMS rev
set rev.article_id = (select sav_sae_id from okc_k_articles_b
where document_type = p_document_type
and document_id = p_document_id
and id = rev.object_id)
,rev.article_version_id = (select article_version_id from okc_k_articles_b
where document_type = p_document_type
and document_id = p_document_id
and id = rev.object_id)
where document_type = p_document_type
and document_id = p_document_id
and object_type = 'ARTICLE'
and object_id is not null
and article_id is null
and article_version_id is null
and exists (select 1 from okc_k_articles_b kart
where document_type = p_document_type
and document_id = p_document_id
and id = rev.object_id);
update okc_Review_upld_terms rev_terms
set action = 'ADDED',
article_id = null,
article_version_id = null,
object_id = null
where rev_terms.review_upld_terms_id > (select min(review_upld_terms_id) from okc_Review_upld_terms
where object_id = rev_terms.object_id
and object_type = 'ARTICLE'
and document_Type = p_document_type and document_id = p_document_id
)
and document_Type = p_document_type and document_id = p_document_id
and object_type = 'ARTICLE' ;
update okc_Review_upld_terms rev_terms
set action = 'ADDED',
article_id = null,
article_version_id = null,
object_id = null
where rev_terms.review_upld_terms_id > (select min(review_upld_terms_id) from okc_Review_upld_terms
where object_id = rev_terms.object_id
and object_type = 'SECTION'
and document_Type = p_document_type and document_id = p_document_id)
and document_Type = p_document_type and document_id = p_document_id
and object_type = 'SECTION';
UPDATE OKC_REVIEW_UPLD_TERMS
SET ACTION='ADDED'
WHERE OBJECT_ID IS NULL
AND DOCUMENT_ID = P_DOCUMENT_ID
AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
AND ACTION IS NULL
AND object_type IN ('ARTICLE','SECTION');
UPDATE OKC_REVIEW_UPLD_TERMS UT
SET ACTION='ADDED'
WHERE ACTION IS NULL
AND DOCUMENT_ID = P_DOCUMENT_ID
AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
AND NEW_PARENT_ID IS NOT NULL
AND ((OBJECT_TYPE = 'ARTICLE'
AND ( NOT EXISTS (SELECT 1
FROM OKC_K_ARTICLES_B A
WHERE A.ID = UT.OBJECT_ID
AND A.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
AND A.DOCUMENT_ID = UT.DOCUMENT_ID) OR
EXISTS (SELECT 1
FROM OKC_K_ARTICLES_B A1
WHERE A1.ID = UT.OBJECT_ID
AND A1.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
AND A1.DOCUMENT_ID = UT.DOCUMENT_ID
AND NVL(A1.AMENDMENT_OPERATION_CODE,'ZZZ') = 'DELETED') )
) OR
(OBJECT_TYPE = 'SECTION'
AND (NOT EXISTS (SELECT 1
FROM OKC_SECTIONS_B S
WHERE S.ID = UT.OBJECT_ID
AND S.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
AND S.DOCUMENT_ID = UT.DOCUMENT_ID )
OR
EXISTS (SELECT 1
FROM OKC_SECTIONS_B S1
WHERE S1.ID = UT.OBJECT_ID
AND S1.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
AND S1.DOCUMENT_ID = UT.DOCUMENT_ID
AND NVL(S1.AMENDMENT_OPERATION_CODE,'ZZZ') = 'DELETED' ))
));
INSERT INTO OKC_REVIEW_UPLD_TERMS(
REVIEW_UPLD_TERMS_ID,
DOCUMENT_ID,
DOCUMENT_TYPE,
OBJECT_ID,
OBJECT_TYPE,
OBJECT_TITLE,
OBJECT_TEXT,
PARENT_OBJECT_TYPE,
PARENT_ID,
ARTICLE_ID,
ARTICLE_VERSION_ID,
OBJECT_VERSION_NUMBER,
LABEL,
DISPLAY_SEQ,
ACTION,
ERROR_MESSAGE_COUNT,
WARNING_MESSAGE_COUNT,
NEW_PARENT_ID,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
KART.DOCUMENT_ID,
KART.DOCUMENT_TYPE,
KART.ID,
'ARTICLE',
nvl(VER.DISPLAY_NAME,ART.ARTICLE_TITLE),
-- Fix for bug# 5223552. Fix for inserting Clause/Reference Text based on IBR flag in review tbl based for 'Deleted' Clause
(SELECT to_clob(ver.reference_text) FROM dual WHERE Nvl(ver.insert_by_reference,'N') = 'Y'
UNION ALL
SELECT VER.ARTICLE_TEXT FROM dual WHERE Nvl(ver.insert_by_reference,'N') = 'N'
),
'SECTION',
KART.SCN_ID,
KART.SAV_SAE_ID,
KART.ARTICLE_VERSION_ID,
1,
KART.LABEL,
KART.DISPLAY_SEQUENCE,
'DELETED',
NULL,
NULL,
(SELECT REVIEW_UPLD_TERMS_ID
FROM OKC_REVIEW_UPLD_TERMS PARENT
WHERE PARENT.OBJECT_ID = KART.SCN_ID
AND PARENT.DOCUMENT_TYPE = p_document_type
AND PARENT.DOCUMENT_ID = p_document_id),
-- KART.SCN_ID,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE
FROM OKC_K_ARTICLES_B KART,
OKC_ARTICLES_ALL ART,
OKC_ARTICLE_VERSIONS VER
WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
AND ART.ARTICLE_ID = VER.ARTICLE_ID
AND KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
AND KART.DOCUMENT_ID = P_DOCUMENT_ID
AND KART.DOCUMENT_TYPE = P_DOCUMENT_TYPE
AND NOT EXISTS (SELECT 1
FROM OKC_REVIEW_UPLD_TERMS UT
WHERE UT.OBJECT_ID = KART.ID
AND UT.OBJECT_TYPE = 'ARTICLE'
AND UT.DOCUMENT_ID = P_DOCUMENT_ID
AND UT.DOCUMENT_TYPE = P_DOCUMENT_TYPE));
INSERT INTO OKC_REVIEW_UPLD_TERMS(
REVIEW_UPLD_TERMS_ID,
DOCUMENT_ID,
DOCUMENT_TYPE,
OBJECT_ID,
OBJECT_TYPE,
OBJECT_TITLE,
OBJECT_TEXT,
PARENT_OBJECT_TYPE,
PARENT_ID,
ARTICLE_ID,
ARTICLE_VERSION_ID,
OBJECT_VERSION_NUMBER,
LABEL,
DISPLAY_SEQ,
ACTION,
ERROR_MESSAGE_COUNT,
WARNING_MESSAGE_COUNT,
NEW_PARENT_ID,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(SELECT OKC_REVIEW_UPLD_TERMS_S1.NEXTVAL,
SCN.DOCUMENT_ID,
SCN.DOCUMENT_TYPE,
SCN.ID,
'SECTION',
SCN.HEADING,
NULL,
DECODE(SCN.SCN_ID,NULL,p_document_type,'SECTION'),
SCN.SCN_ID,
NULL,
NULL,
1,
SCN.LABEL,
SCN.SECTION_SEQUENCE,
'DELETED',
NULL,
NULL,
-- SCN.SCN_ID,
DECODE(SCN.SCN_ID, NULL,l_rev_id_for_doc,
p_document_id, l_rev_id_for_doc,
(SELECT REVIEW_UPLD_TERMS_ID
FROM OKC_REVIEW_UPLD_TERMS PARENT
WHERE PARENT.OBJECT_ID = SCN.SCN_ID
AND PARENT.DOCUMENT_TYPE = p_document_type
AND PARENT.DOCUMENT_ID = p_document_id)
),
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE
FROM OKC_SECTIONS_B SCN
WHERE SCN.DOCUMENT_ID = P_DOCUMENT_ID
AND SCN.DOCUMENT_TYPE = P_DOCUMENT_TYPE
AND NOT EXISTS (SELECT 1
FROM OKC_REVIEW_UPLD_TERMS UT
WHERE UT.OBJECT_ID = SCN.ID
AND UT.DOCUMENT_TYPE = p_document_type
AND UT.DOCUMENT_ID = p_document_id
AND UT.OBJECT_TYPE = 'SECTION'));
UPDATE OKC_REVIEW_UPLD_TERMS REV
SET NEW_PARENT_ID = (SELECT REVIEW_UPLD_TERMS_ID
FROM OKC_REVIEW_UPLD_TERMS PARENT
WHERE PARENT.OBJECT_ID = REV.PARENT_ID
AND REV.REVIEW_UPLD_TERMS_id = del_csr.REVIEW_UPLD_TERMS_id
AND PARENT.DOCUMENT_TYPE = p_document_type
AND PARENT.document_id = p_document_id
)
WHERE
REV.REVIEW_UPLD_TERMS_id = del_csr.REVIEW_UPLD_TERMS_id;
UPDATE OKC_REVIEW_UPLD_TERMS UT
SET ACTION='UPDATED',
DISPLAY_SEQ = (select KART.display_sequence
FROM OKC_K_ARTICLES_B KART
WHERE KART.ID = UT.OBJECT_ID)
WHERE ACTION IS NULL
AND DOCUMENT_ID = P_DOCUMENT_ID
AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
AND OBJECT_TYPE = 'ARTICLE'
AND EXISTS
(
SELECT 1
FROM OKC_K_ARTICLES_B KART,
OKC_ARTICLES_ALL ART,
OKC_ARTICLE_VERSIONS VER
WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
AND ART.ARTICLE_ID = VER.ARTICLE_ID
AND KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
AND KART.ID = UT.OBJECT_ID
AND KART.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
AND KART.DOCUMENT_ID = UT.DOCUMENT_ID
AND (
(NVL(VER.DISPLAY_NAME,ART.ARTICLE_TITLE) <> to_char(UT.OBJECT_TITLE)) OR
(EXISTS (SELECT 1 FROM OKC_WORD_SYNC_T WHERE DOC_ID = P_DOCUMENT_ID AND DOC_TYPE = P_DOCUMENT_TYPE AND CAT_ID = UT.OBJECT_ID AND action = 'UPDATEDASSIGNED'))));
UPDATE OKC_REVIEW_UPLD_TERMS UT
SET ACTION='UPDATED',
DISPLAY_SEQ = (select KART.display_sequence
FROM OKC_K_ARTICLES_B KART
WHERE KART.ID = UT.OBJECT_ID)
WHERE ACTION IS NULL
AND DOCUMENT_ID = P_DOCUMENT_ID
AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
AND OBJECT_TYPE = 'ARTICLE'
AND EXISTS
(
SELECT 1
FROM OKC_K_ARTICLES_B KART,
OKC_ARTICLES_ALL ART,
OKC_ARTICLE_VERSIONS VER
WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
AND ART.ARTICLE_ID = VER.ARTICLE_ID
AND KART.ARTICLE_VERSION_ID = VER.ARTICLE_VERSION_ID
AND KART.ID = UT.OBJECT_ID
AND KART.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
AND KART.DOCUMENT_ID = UT.DOCUMENT_ID
AND (
(NVL(VER.DISPLAY_NAME,ART.ARTICLE_TITLE) <> to_char(UT.OBJECT_TITLE)) OR
(NVL(ver.insert_by_reference,'N') = 'Y' AND
NVL(dbms_lob.compare(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ut.object_text,'&NBSP;| ',' '),
(NVL(ver.insert_by_reference,'N') <> 'Y' AND
NVL(dbms_lob.compare(regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(ut.object_text,'&NBSP;| ',' '),
UPDATE OKC_REVIEW_UPLD_TERMS UT
SET ACTION='UPDATED',
DISPLAY_SEQ = (SELECT SECTION_SEQUENCE FROM OKC_SECTIONS_B SCN
WHERE SCN.ID = UT.OBJECT_ID
AND to_char(UT.OBJECT_TITLE) <> SCN.HEADING)
WHERE ACTION IS NULL
AND DOCUMENT_ID = P_DOCUMENT_ID
AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
AND OBJECT_TYPE = 'SECTION'
AND EXISTS (SELECT 1
FROM OKC_SECTIONS_B SCN
WHERE SCN.ID = UT.OBJECT_ID
AND SCN.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
AND SCN.DOCUMENT_ID = UT.DOCUMENT_ID
AND to_char(UT.OBJECT_TITLE) <> SCN.HEADING);
UPDATE OKC_REVIEW_UPLD_TERMS UT
SET NON_STANDARD_FLAG = 'Y'
WHERE DOCUMENT_ID = P_DOCUMENT_ID
AND DOCUMENT_TYPE = P_DOCUMENT_TYPE
AND OBJECT_TYPE = 'ARTICLE'
AND EXISTS (SELECT 1
FROM OKC_K_ARTICLES_B KART,
OKC_ARTICLES_ALL ART
WHERE KART.SAV_SAE_ID = ART.ARTICLE_ID
AND KART.ID = UT.OBJECT_ID
AND KART.DOCUMENT_TYPE = UT.DOCUMENT_TYPE
AND KART.DOCUMENT_ID = UT.DOCUMENT_ID
AND ART.STANDARD_YN = 'N');
update okc_REVIEW_UPLD_TERMS
set new_parent_id = l_unassigned_scn_id, action = 'ADDED'
where REVIEW_UPLD_TERMS_id = clause_no_parent_csr.REVIEW_UPLD_TERMS_id;
update OKC_REVIEW_UPLD_TERMS
set new_parent_id = l_unassigned_scn_id
where REVIEW_UPLD_TERMS_id =clauses_moved_no_section_csr.REVIEW_UPLD_TERMS_id;
update OKC_REVIEW_VAR_VALUES
set changed = 'Y'
where REVIEW_UPLD_TERMS_id = var_csr.REVIEW_UPLD_TERMS_id;
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number,
variable_code
)
(select okc_review_messages_s1.nextval review_messages_id,
var_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity,l_message_name ,1 object_version_number, var_csr.variable_code from dual
where not exists (select 1 from okc_review_messages where review_upld_terms_id = var_csr.review_upld_terms_id
and variable_code = var_csr.variable_code and message_name = l_message_name));
-- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
update okc_review_upld_terms
set action='UPDATED'
where review_upld_terms_id = valid_var_added_csr.review_upld_terms_id and action is null;
update OKC_REVIEW_VAR_VALUES
set changed = 'A'
where REVIEW_UPLD_TERMS_id = valid_var_added_csr.REVIEW_UPLD_TERMS_id;
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number,
variable_code
)
(select okc_review_messages_s1.nextval review_messages_id,
valid_var_added_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity, l_message_name ,1 object_version_number, valid_var_added_csr.variable_code from dual
where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_var_added_csr.review_upld_terms_id
and variable_code = valid_var_added_csr.variable_code and message_name = l_message_name));
update OKC_REVIEW_VAR_VALUES
set changed = 'A'
where REVIEW_UPLD_TERMS_id = valid_var_new_clause_csr.REVIEW_UPLD_TERMS_id;
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number,
variable_code
)
(select okc_review_messages_s1.nextval review_messages_id,
valid_var_new_clause_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity, l_message_name ,1 object_version_number, valid_var_new_clause_csr.variable_code from dual
where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_var_new_clause_csr.review_upld_terms_id
and variable_code = valid_var_new_clause_csr.variable_code and message_name = l_message_name));
update OKC_REVIEW_VAR_VALUES
set changed = 'A'
where REVIEW_UPLD_TERMS_id = valid_new_var_ibr_csr.REVIEW_UPLD_TERMS_id;
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number,
variable_code
)
(select okc_review_messages_s1.nextval review_messages_id,
valid_new_var_ibr_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity, l_message_name ,1 object_version_number, valid_new_var_ibr_csr.variable_code from dual
where not exists (select 1 from okc_review_messages where review_upld_terms_id = valid_new_var_ibr_csr.review_upld_terms_id
and variable_code = valid_new_var_ibr_csr.variable_code and message_name = l_message_name));
-- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
update okc_review_upld_terms
set action='UPDATED'
where review_upld_terms_id = removed_var_csr.review_upld_terms_id and action is null;
insert into okc_review_var_values(
review_var_values_id,
review_upld_terms_id,
variable_name,
language,
variable_code,
object_version_number,
changed)
(select okc_review_var_values_s1.nextval,
removed_var_csr.review_upld_terms_id,
removed_var_csr.variable_name,
removed_var_csr.language,
removed_var_csr.variable_code,
1,
'D' from dual);
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number,
variable_code
)
(select okc_review_messages_s1.nextval review_messages_id,
removed_var_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity,'OKC_REVIEW_VAR_REMOVED' message_name ,1 object_version_number, removed_var_csr.variable_code from dual
where not exists (select 1 from okc_review_messages where review_upld_terms_id = removed_var_csr.review_upld_terms_id
and variable_code = removed_var_csr.variable_code and message_name = 'OKC_REVIEW_VAR_REMOVED'));
-- Fix for bug# 5229387. Clauses should be marked as 'UPDATED' only if action is null
update okc_review_upld_terms
set action='UPDATED'
where review_upld_terms_id = invalid_var_added_csr.review_upld_terms_id and action is null;
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number,
variable_code
)
(select okc_review_messages_s1.nextval review_messages_id,
invalid_var_added_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity, l_message_name ,1 object_version_number, invalid_var_added_csr.variable_code from dual
where not exists (select 1 from okc_review_messages where review_upld_terms_id = invalid_var_added_csr.review_upld_terms_id
and variable_code = invalid_var_added_csr.variable_code and message_name = l_message_name));
delete from okc_review_var_values revvar
where revvar.rowid > (select min(rowid) from okc_review_var_values
where review_upld_terms_id = revvar.review_upld_terms_id
and revvar.variable_code = variable_code)
and exists(select 1 from okc_Review_upld_terms rev
where revvar.review_upld_terms_id = rev.review_upld_terms_id
and rev.document_type = p_document_type
and rev.document_id = p_document_id);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5000: Begin: Logging the ones that were determined as UPDATED');
for upd_csr in get_updated_articles_csr loop
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5010: for review_upld_terms_id=' || upd_csr.review_upld_terms_id );
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: End: Logging the ones that were determined as UPDATED');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: After delete review terms');
update okc_REVIEW_UPLD_TERMS
set object_title = l_clause_title || l_clause_counter
where REVIEW_UPLD_TERMS_id = emp_csr.REVIEW_UPLD_TERMS_id;
update okc_REVIEW_UPLD_TERMS
set object_title = l_section_title || l_section_counter
where REVIEW_UPLD_TERMS_id = emp_csr.REVIEW_UPLD_TERMS_id;
update okc_review_upld_terms rev
set action = 'DELETED',
object_title = (select nvl(ver.display_name,art. article_title)
from OKC_K_ARTICLES_B kart, OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
where kart.id = rev.object_id
and kart.sav_sae_id = art.article_id
and kart.article_version_id = ver.article_version_id
and art.article_id = ver.article_id)
,object_text = (select ver.article_text from OKC_K_ARTICLES_B kart, OKC_ARTICLE_VERSIONS VER
where kart.id = rev.object_id
and kart.article_Version_id = ver.article_version_id)
where document_type = p_document_type
and document_id = p_document_id
and object_type = 'ARTICLE'
and action = 'UPDATED'
and (DBMS_LOB.getlength(object_text)=0 OR object_text is null);
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number
)
(select okc_review_messages_s1.nextval review_messages_id,
ibr_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity,'OKC_ARTICLE_UPDT_IBR' message_name ,1 object_version_number from dual);
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number
)
(select okc_review_messages_s1.nextval review_messages_id,
mandatory_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity,'OKC_ARTICLE_IS_MANDATORY' message_name ,1 object_version_number from dual);
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number
)
(select okc_review_messages_s1.nextval review_messages_id,
lock_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity,'OKC_ARTICLE_UPDT_LOCK' message_name ,1 object_version_number from dual);
insert into okc_review_messages (
review_messages_id,
REVIEW_UPLD_TERMS_id,
error_severity,
message_name,
object_version_number
)
(select okc_review_messages_s1.nextval review_messages_id,
title_csr.REVIEW_UPLD_TERMS_id REVIEW_UPLD_TERMS_id,
'W' error_severity, l_message_name,1 object_version_number from dual);
for upd_csr in update_err_warn_csr loop
if (upd_csr.error_severity = 'E') then
update okc_review_upld_terms
set error_message_count = upd_csr.err_warn_count
where review_upld_terms_id = upd_csr.review_upld_terms_id;
update okc_review_upld_terms
set warning_message_count = upd_csr.err_warn_count
where review_upld_terms_id = upd_csr.review_upld_terms_id;
OKC_TEMPLATE_USAGES_GRP.update_template_usages(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list ,
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_document_type => p_document_type,
p_document_id => p_document_id,
p_lock_terms_flag => 'Y',
p_locked_by_user_id => FND_GLOBAL.user_id);
curr_disp_seqs.DELETE;
del_new_parents.DELETE;
del_disp_seqs.DELETE;
OPEN deleted_terms_csr;
FETCH deleted_terms_csr BULK COLLECT INTO
del_new_parents,del_disp_seqs;
CLOSE deleted_terms_csr;
UPDATE okc_review_upld_terms
SET display_seq = l_sequence
WHERE review_upld_terms_id = disp_rec.review_upld_terms_id;
for upd_csr in update_err_warn_csr loop
if (upd_csr.error_severity = 'E') then
update okc_review_upld_terms
set error_message_count = upd_csr.err_warn_count
where review_upld_terms_id = upd_csr.review_upld_terms_id;
update okc_review_upld_terms
set warning_message_count = upd_csr.err_warn_count
where review_upld_terms_id = upd_csr.review_upld_terms_id;
SELECT nvl(max(display_seq),0)+10
FROM OKC_REVIEW_UPLD_TERMS
WHERE DOCUMENT_TYPE= p_document_type
AND DOCUMENT_ID = p_document_id
AND PARENT_ID IS NULL or new_parent_id = p_document_id;
INSERT INTO OKC_REVIEW_UPLD_TERMS(
REVIEW_UPLD_TERMS_ID,
DOCUMENT_ID,
DOCUMENT_TYPE,
OBJECT_ID,
OBJECT_TYPE,
OBJECT_TITLE,
OBJECT_TEXT,
PARENT_OBJECT_TYPE,
PARENT_ID,
ARTICLE_ID,
ARTICLE_VERSION_ID,
OBJECT_VERSION_NUMBER,
LABEL,
DISPLAY_SEQ,
ACTION,
ERROR_MESSAGE_COUNT,
WARNING_MESSAGE_COUNT,
NEW_PARENT_ID,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
(SELECT l_scn_id,
P_DOCUMENT_ID,
P_DOCUMENT_TYPE,
NULL,
'SECTION',
l_meaning,
NULL,
p_document_type,
p_document_id,
NULL,
NULL,
1,
NULL,
l_sequence,
'ADDED',
NULL,
NULL,
p_new_parent_id,
FND_GLOBAL.LOGIN_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE
FROM dual);