[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
SELECT ID,SECTION_SEQUENCE DISPLAY_SEQ,'SECTION' obj_type
FROM OKC_SECTIONS_B
WHERE document_type = p_doc_type
AND document_id = p_doc_id
AND scn_id=b_scn_id
UNION ALL
SELECT ID,DISPLAY_SEQUENCE DISPLAY_SEQ,'ARTICLE' obj_type
FROM OKC_K_ARTICLES_B
WHERE document_type = p_doc_type
AND document_id = p_doc_id
AND scn_id=b_scn_id
ORDER BY 2;
SELECT SCN_ID FROM OKC_K_ARTICLES_B
WHERE ID=b_cat_id;
OKC_K_ARTICLES_GRP.update_article(
p_api_version =>1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => scn_child_tbl(k).id,
p_display_sequence => l_ref_sequence,
p_object_version_number => Null
);
OKC_TERMS_SECTIONS_GRP.update_section(
p_api_version =>1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => scn_child_tbl(k).id,
p_section_sequence => l_ref_sequence,
p_object_version_number =>Null
);
PROCEDURE update_article_variable(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_validation_level IN NUMBER ,
p_validate_commit IN VARCHAR2 ,
p_validation_string IN VARCHAR2,
p_commit IN VARCHAR2 ,
p_art_var_tbl IN art_var_tbl_type,
p_mode IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'update_article_variable';
SELECT DOCUMENT_TYPE,DOCUMENT_ID
FROM OKC_K_ARTICLES_B
WHERE id=b_cat_id;
SELECT object_version_number
FROM OKC_K_ARTICLES_B
WHERE ID=b_cat_id
AND AMENDMENT_OPERATION_CODE<>G_AMEND_CODE_UPDATED;
okc_debug.log('100: Entered update_article_variable', 2);
G_PKG_NAME, '100: Entered update_article_variable' );
SAVEPOINT g_update_article_variable_GRP;
OKC_K_ART_VARIABLES_PVT.update_row(
p_validation_level => 0,
x_return_status => x_return_status,
p_cat_id => p_art_var_tbl(i).cat_id,
p_variable_code => p_art_var_tbl(i).variable_code,
p_variable_type => p_art_var_tbl(i).variable_type,
p_external_yn => p_art_var_tbl(i).external_yn,
p_variable_value_id => p_art_var_tbl(i).variable_value_id,
p_variable_value => p_art_var_tbl(i).variable_value,
p_attribute_value_set_id => p_art_var_tbl(i).attribute_value_set_id,
p_object_version_number => p_art_var_tbl(i).object_version_number
);
OKC_K_ARTICLES_GRP.Update_article(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => 0,
p_mode => p_mode,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status,
p_id => p_art_var_tbl(i).cat_id,
p_object_version_number => l_dummy
);
okc_debug.log('200: Leaving update_article_variable', 2);
G_PKG_NAME, '200: Leaving update_article_variable' );
okc_debug.log('300: Leaving update_article_variable: OKC_API.G_EXCEPTION_ERROR Exception', 2);
G_PKG_NAME, '300: Leaving update_article_variable: OKC_API.G_EXCEPTION_ERROR Exception' );
ROLLBACK TO g_update_article_variable_GRP;
okc_debug.log('400: Leaving update_article_variable: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '400: Leaving update_article_variable: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
ROLLBACK TO g_update_article_variable_GRP;
okc_debug.log('500: Leaving update_article_variable because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '500: Leaving update_article_variable because of EXCEPTION: '||sqlerrm );
ROLLBACK TO g_update_article_variable_GRP;
END update_article_variable;
PROCEDURE update_structure(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_validation_level IN NUMBER ,
p_validate_commit IN VARCHAR2 ,
p_validation_string IN VARCHAR2,
p_commit IN VARCHAR2 ,
p_structure_tbl IN structure_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'update_structure';
SELECT DOCUMENT_TYPE,DOCUMENT_ID
FROM OKC_K_ARTICLES_B
WHERE id=b_id;
SELECT DOCUMENT_TYPE,DOCUMENT_ID
FROM OKC_SECTIONS_B
WHERE id=b_id;
okc_debug.log('100: Entered update_structure', 2);
G_PKG_NAME, '100: Entered update_structure' );
SAVEPOINT g_update_structure_GRP;
OKC_TERMS_SECTIONS_GRP.update_section(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_validation_level => 0,
x_return_status => x_return_status,
p_id => p_structure_tbl(i).id,
p_section_sequence => p_structure_tbl(i).display_sequence,
p_label => p_structure_tbl(i).label,
p_scn_id => p_structure_tbl(i).scn_id,
p_object_version_number => p_structure_tbl(i).object_version_number
);
OKC_K_ARTICLES_GRP.Update_article(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => 0,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_return_status => x_return_status,
p_id => p_structure_tbl(i).id,
p_mandatory_yn => p_structure_tbl(i).mandatory_yn,
p_scn_id => p_structure_tbl(i).scn_id,
p_label => p_structure_tbl(i).label,
p_display_sequence => p_structure_tbl(i).display_sequence,
p_object_version_number => p_structure_tbl(i).object_version_number
);
okc_debug.log('200: Leaving update_structure', 2);
G_PKG_NAME, '200: Leaving update_structure' );
okc_debug.log('300: Leaving update_structure: OKC_API.G_EXCEPTION_ERROR Exception', 2);
G_PKG_NAME, '300: Leaving update_structure: OKC_API.G_EXCEPTION_ERROR Exception' );
ROLLBACK TO g_update_structure_GRP;
okc_debug.log('400: Leaving update_structure: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '400: Leaving update_structure: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
ROLLBACK TO g_update_structure_GRP;
okc_debug.log('500: Leaving update_structure because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '500: Leaving update_structure because of EXCEPTION: '||sqlerrm );
ROLLBACK TO g_update_structure_GRP;
END update_structure;
l_insert_tbl article_id_tbl_type;
SELECT 'x' FROM
OKC_K_ARTICLES_B KART
WHERE document_type=p_doc_type
AND document_id=p_doc_id
AND source_flag='R'
AND orig_article_id = b_article_id
AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>'DELETED'
AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>'DELETED';
SELECT nvl(article_effective_date,sysdate)
FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id;
SELECT vers.article_id article_id,
vers.article_version_id article_version_id,
nvl(vers.default_section,'UNASSIGNED') scn_code,
nvl(PROVISION_YN,'N') provision_yn
FROM OKC_ARTICLE_VERSIONS VERS
WHERE vers.article_id = b_article_id
AND article_status in ('ON_HOLD','APPROVED')
AND b_article_effective_date >= vers.start_date
AND b_article_effective_date <= nvl(vers.end_date,b_article_effective_date+1);
SELECT article_id,
article_version_id,
nvl(vers.default_section,'UNASSIGNED') scn_code,
nvl(PROVISION_YN,'N') provision_yn
FROM OKC_ARTICLE_VERSIONS VERS
WHERE article_id= b_article_id
AND article_status in ('ON_HOLD','APPROVED')
AND start_date = (select max(start_date) FROM OKC_ARTICLE_VERSIONS
WHERE article_id= b_article_id
AND article_status in ('ON_HOLD','APPROVED') );
SELECT id FROM OKC_SECTIONS_B
WHERE document_type=p_doc_type
AND document_id =p_doc_id
AND scn_code = b_scn_code
AND nvl(AMENDMENT_OPERATION_CODE,'?')<>'DELETED'
AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>'DELETED'
AND rownum=1 ;
SELECT nvl(max(section_sequence),0)+10 FROM OKC_SECTIONS_B
WHERE document_type = p_doc_type
AND document_id = p_doc_id
AND NVL(scn_code,'XWY') <> 'UNASSIGNED'
AND scn_id IS NULL;
SELECT nvl(max(display_sequence),0)+10 FROM OKC_K_ARTICLES_B
WHERE document_type=p_doc_type
AND document_id =p_doc_id
AND SCN_ID = b_scn_id;
SELECT nvl(max(section_sequence),0)+10 FROM OKC_SECTIONS_B
WHERE document_type=p_doc_type
AND document_id =p_doc_id
AND SCN_ID = b_scn_id;
SELECT name,
nvl(PROVISION_ALLOWED_YN,'Y')
FROM OKC_BUS_DOC_TYPES_V
WHERE DOCUMENT_TYPE=p_doc_type;
SELECT NVL(t.xprt_clause_mandatory_flag,'N'),
NVL(t.xprt_scn_code,'UNASSIGNED')
FROM okc_template_usages u,
okc_terms_templates_all t
WHERE u.template_id = t.template_id
AND u.document_type = p_doc_type
AND u.document_id = p_doc_id ;
SELECT doc_numbering_scheme
FROM okc_template_usages
WHERE document_type = p_doc_type
AND document_id = p_doc_id;
SELECT org_id
FROM OKC_ARTICLES_ALL
WHERE article_id = b_article_id;
SELECT VERS.ARTICLE_ID article_id,
ADP.GLOBAL_ARTICLE_VERSION_ID article_version_id,
NVL(VERS.default_section,'UNASSIGNED') scn_code,
NVL(VERS.PROVISION_YN,'N') provision_yn
FROM OKC_ARTICLE_VERSIONS VERS,
OKC_ARTICLE_ADOPTIONS ADP
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
AND VERS.ARTICLE_ID = b_article_id
AND nvl(b_article_effective_date,sysdate) >= VERS.START_DATE
AND nvl(b_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(b_article_effective_date,sysdate) +1)
AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
AND ADP.ADOPTION_TYPE = 'ADOPTED'
AND ADP.LOCAL_ORG_ID = b_local_org_id
AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD') ;
SELECT VERS.ARTICLE_ID article_id,
ADP.GLOBAL_ARTICLE_VERSION_ID article_version_id,
NVL(VERS.default_section,'UNASSIGNED') scn_code,
NVL(VERS.PROVISION_YN,'N') provision_yn
FROM OKC_ARTICLE_VERSIONS VERS,
OKC_ARTICLE_ADOPTIONS ADP
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
AND VERS.ARTICLE_ID = b_article_id
AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
AND ADP.ADOPTION_TYPE = 'ADOPTED'
AND ADP.LOCAL_ORG_ID = b_local_org_id
AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
ORDER BY ADP.creation_date desc;
l_query1 := 'Select kart.id ,kart.object_version_number from okc_k_articles_b kart where document_type=:l_doc_type and document_id=:l_doc_id and source_flag=''R'' and orig_article_id not in (';
l_insert_tbl(k):= p_article_id_tbl(i);
l_query := 'Select kart.id ,kart.object_version_number from okc_k_articles_b kart where document_type=:l_doc_type and document_id=:l_doc_id and source_flag=''R'' ';
okc_debug.log('400: Going to delete Articles which came from expert last time but not present in expert this time');
G_PKG_NAME, '400: Going to delete Articles which came from expert last time but not present in expert this time' );
OKC_K_ARTICLES_GRP.delete_article(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_validate_commit => FND_API.G_FALSE,
p_validation_string => Null,
p_commit => FND_API.G_FALSE,
p_mode => p_mode,
p_id => l_article_id,
p_object_version_number => l_ovn,
p_mandatory_clause_delete => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
okc_debug.log('500: Articles Delete ',2);
okc_debug.log('600: Going to insert new Articles retunred by expert',2);
okc_debug.log('600: Inserting Articles : '||l_insert_tbl.COUNT,2);
G_PKG_NAME, '500: Articles Delete ' );
G_PKG_NAME, '600: Going to insert new Articles retunred by expert' );
G_PKG_NAME, '600: Inserting Articles : '||l_insert_tbl.COUNT );
IF l_insert_tbl.COUNT > 0 THEN
OPEN l_get_effective_date_csr;
FOR i IN l_insert_tbl.FIRST..l_insert_tbl.LAST LOOP
-- bug 4162255
-- check if Article is global or local
OPEN l_get_article_org_csr(l_insert_tbl(i));
OPEN l_get_active_article_csr(l_insert_tbl(i),l_article_effective_date);
OPEN l_get_max_article_csr(l_insert_tbl(i));
OPEN l_get_local_article_csr(b_article_id => l_insert_tbl(i),
b_local_org_id => l_current_org_id,
b_article_effective_date => l_article_effective_date );
OPEN l_get_max_local_article_csr(b_article_id => l_insert_tbl(i),
b_local_org_id => l_current_org_id );
END IF; -- l_insert_tbl.COUNT > 0
okc_debug.log('900: New Articles Inserted');
G_PKG_NAME, '900: New Articles Inserted' );
SELECT article_id , 'x' FROM OKC_ARTICLE_VERSIONS
WHERE article_version_id=b_article_version_id;
OKC_K_ARTICLES_GRP.update_article(
p_api_version =>1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_mode => p_mode,
p_id => p_article_tbl(i).cat_id,
p_sav_sae_id => l_article_id,
p_article_version_id => p_article_tbl(i).article_version_id,
p_object_version_number => p_article_tbl(i).ovn
);
l_not_deleted BOOLEAN;
SELECT nvl(max(SECTION_SEQUENCE),0) FROM OKC_SECTIONS_B
WHERE document_type=p_doc_type
AND document_id=p_doc_id
AND ( (b_scn_id is Null and scn_id is Null)
OR
(b_scn_id is Not Null and scn_id=b_scn_id)
);
SELECT nvl(max(display_SEQUENCE),0) FROM OKC_K_ARTICLES_B
WHERE document_type=p_doc_type
AND document_id=p_doc_id
and b_scn_id is not null
and scn_id=b_scn_id;
SELECT SCN_ID FROM OKC_K_ARTICLES_B WHERE Id=b_cat_id;
SELECT SCN_ID FROM OKC_SECTIONS_B WHERE Id=b_scn_id;
SELECT ID FROM OKC_SECTIONS_B
where id<>b_id
AND document_type=p_doc_type
AND document_id=p_doc_id
start with Id=b_id
connect by id=prior scn_id;
SELECT ID,SECTION_SEQUENCE DISPLAY_SEQ,'SECTION' obj_type
FROM OKC_SECTIONS_B
WHERE document_type = p_doc_type
AND document_id = p_doc_id
AND ( (b_scn_id is Null and scn_id is Null)
OR
(b_scn_id is Not Null and scn_id=b_scn_id)
)
UNION ALL
SELECT ID,DISPLAY_SEQUENCE DISPLAY_SEQ,'ARTICLE' obj_type
FROM OKC_K_ARTICLES_B
WHERE document_type = p_doc_type
AND document_id = p_doc_id
AND scn_id=b_scn_id
ORDER BY 2;
okc_debug.log('300: Wrong Selection of Item Location', 2);
G_PKG_NAME, '300: Wrong Selection of Item Location' );
okc_debug.log('400: Wrong Selection of Item Location', 2);
G_PKG_NAME, '400: Wrong Selection of Item Location' );
okc_debug.log('500: Wrong Selection ', 2);
G_PKG_NAME, '500: Wrong Selection ' );
p_msg_name => 'OKC_WRONG_SELECTION'
);
l_not_deleted := TRUE;
l_not_deleted := FALSE;
IF l_not_deleted THEN
IF p_organize_tbl(i).object_type='ARTICLE' THEN
/*IF (l_debug = 'Y') THEN
okc_debug.log('600: Updating Display Sequence of cat_id '||p_organize_tbl(i).id, 2);
OKC_K_ARTICLES_GRP.update_article(
p_api_version =>1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => p_organize_tbl(i).id,
p_scn_id => l_parent_scn_id,
p_display_sequence => l_ref_sequence,
p_object_version_number => Null
);
OKC_TERMS_SECTIONS_GRP.update_section(
p_api_version =>1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => p_organize_tbl(i).id,
p_scn_id => nvl(l_parent_scn_id,OKC_API.G_MISS_NUM),
p_section_sequence => l_ref_sequence,
p_object_version_number =>Null
);
OKC_K_ARTICLES_GRP.update_article(
p_api_version =>1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => scn_child_tbl(k).id,
p_display_sequence => l_ref_sequence,
p_object_version_number => Null
);
OKC_TERMS_SECTIONS_GRP.update_section(
p_api_version =>1,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_id => scn_child_tbl(k).id,
p_section_sequence => l_ref_sequence,
p_object_version_number =>Null
);
l_not_deleted BOOLEAN;
l_new_action OKC_REVIEW_UPLD_TERMS.ACTION%TYPE := 'UPDATED';
SELECT object_text,
action,
NVL(error_message_count,0) error_message_count,
NVL(warning_message_count,0) warning_message_count
FROM okc_review_upld_terms
WHERE review_upld_terms_id = b_review_upld_terms_id;
SELECT av.insert_by_reference,
akb.mandatory_yn,
av.lock_text
FROM okc_article_versions av, okc_review_upld_terms ar, okc_k_articles_b akb
WHERE av.article_version_id = ar.article_version_id
AND akb.id = ar.object_id
AND ar.review_upld_terms_id = b_review_upld_terms_id;
SELECT doc_numbering_scheme from okc_template_usages
WHERE document_type = p_doc_type
AND document_id = p_doc_id;
Process the records to update the first clause text with the clause text
of the rest of the clauses.
Check is made for clause not being IBR, lock_text, mandatory
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
*/
IF p_merge_review_tbl.COUNT>0 THEN
OPEN l_get_article_text_csr(p_merge_review_tbl(1).review_upld_terms_id);
G_PKG_NAME, '500: Wrong Selection ' );
p_msg_name => 'OKC_WRONG_SELECTION'
);
G_PKG_NAME, '500: Wrong Selection ' );
p_msg_name => 'OKC_WRONG_SELECTION');
Update the records being merged with ACTION=MERGED (Soft Delete)
***********************************************************************/
-- Added for Bug 5339759
UPDATE okc_review_upld_terms
SET
old_review_upld_terms_id = review_upld_terms_id
WHERE review_upld_terms_id = p_merge_review_tbl(i).review_upld_terms_id;
OKC_REVIEW_UPLD_TERMS_PVT.update_row(
x_return_status => x_return_status,
p_review_upld_terms_id => p_merge_review_tbl(i).review_upld_terms_id,
p_action => 'MERGED',
p_object_version_number => p_merge_review_tbl(i).object_version_number,
p_new_parent_id => G_MISS_NUM
);
UPDATE okc_review_var_values
SET
old_review_upld_terms_id = review_upld_terms_id,
review_upld_terms_id = p_merge_review_tbl(1).review_upld_terms_id
WHERE review_upld_terms_id = p_merge_review_tbl(i).review_upld_terms_id;
Update the OKC_REVIEW_UPLD_TERMS table for the first clause
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
-- Added for bug# 4897442
IF(l_new_action = 'ADDED') THEN
l_new_action := 'ADDED';
l_new_action := 'UPDATED';
OKC_REVIEW_UPLD_TERMS_PVT.update_row(
x_return_status => x_return_status,
p_review_upld_terms_id => p_merge_review_tbl(1).review_upld_terms_id,
p_action => l_new_action,
p_object_text => l_final_article_text,
p_object_version_number => NULL,
p_error_message_count => l_final_err_msg_count,
p_warning_message_count => l_final_wrn_msg_count );