[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
OKC_TERMS_SECTIONS_PVT.insert_row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_id => p_id,
p_section_sequence => p_section_sequence,
p_label => p_label,
p_scn_id => p_scn_id,
p_heading => p_heading,
p_description => p_description,
p_document_type => p_document_type,
p_document_id => p_document_id,
p_scn_code => p_scn_code,
p_amendment_description => l_amendment_description,
p_amendment_operation_code => l_amendment_operation_code,
p_summary_amend_operation_code => l_summary_amend_operation_code,
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_print_yn => p_print_yn,
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_id => x_id
);
PROCEDURE update_section(
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_mode IN VARCHAR2 , --'AMEND' or 'NORMAL'
p_id IN NUMBER,
p_section_sequence IN NUMBER,
p_label IN VARCHAR2,
p_scn_id IN NUMBER,
p_heading IN VARCHAR2,
p_description IN VARCHAR2,
p_document_type IN VARCHAR2,
p_document_id IN NUMBER,
p_scn_code IN VARCHAR2,
p_amendment_description IN VARCHAR2,
p_orig_system_reference_code IN VARCHAR2,
p_orig_system_reference_id1 IN NUMBER,
p_orig_system_reference_id2 IN NUMBER,
p_print_yn IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_object_version_number IN NUMBER,
p_lock_terms_yn IN VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_update_section';
SELECT DOCUMENT_ID,DOCUMENT_TYPE,orig_system_reference_id1 FROM OKC_SECTIONS_B
WHERE ID=P_ID;
SELECT SUMMARY_AMEND_OPERATION_CODE ,amendment_operation_code
FROM OKC_SECTIONS_B
WHERE ID=P_ID;
okc_debug.log('1600: Entered update_section', 2);
G_PKG_NAME, '1600: Entered update_section' );
SAVEPOINT g_update_section_GRP;
l_amendment_operation_code := nvl(l_existing_operation_code,G_AMEND_CODE_UPDATED);
p_amend_operation_code=>G_AMEND_CODE_UPDATED);
AND l_amendment_operation_code = G_AMEND_CODE_UPDATED
AND p_lock_terms_yn = 'Y'
AND l_orig_system_reference_id1 IS NOT null
)
THEN
okc_k_entity_locks_grp.lock_entity
( p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE,
p_entity_name => okc_k_entity_locks_grp.G_SECTION_ENTITY,
p_entity_pk1 => To_Char(l_orig_system_reference_id1),
P_LOCK_BY_ENTITY_ID => p_id,
p_LOCK_BY_DOCUMENT_TYPE => l_document_type,
p_LOCK_BY_DOCUMENT_ID => l_document_id,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA
);
OKC_TERMS_SECTIONS_PVT.update_row(
p_validation_level => p_validation_level,
x_return_status => x_return_status,
p_id => p_id,
p_section_sequence => p_section_sequence,
p_label => p_label,
p_scn_id => p_scn_id,
p_heading => p_heading,
p_description => p_description,
p_document_type => p_document_type,
p_document_id => p_document_id,
p_scn_code => p_scn_code,
p_amendment_description => l_amendment_description,
p_amendment_operation_code => l_amendment_operation_code,
p_summary_amend_operation_code => l_summary_amend_operation_code,
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_print_yn => p_print_yn,
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
);
okc_debug.log('1800: Leaving update_section', 2);
G_PKG_NAME, '1800: Leaving update_section' );
okc_debug.log('1900: Leaving update_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
G_PKG_NAME, '1900: Leaving update_section: OKC_API.G_EXCEPTION_ERROR Exception' );
ROLLBACK TO g_update_section_GRP;
okc_debug.log('2000: Leaving update_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '2000: Leaving update_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
ROLLBACK TO g_update_section_GRP;
okc_debug.log('2100: Leaving update_section because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '2100: Leaving update_section because of EXCEPTION: '||sqlerrm );
ROLLBACK TO g_update_section_GRP;
END update_section;
SELECT id,
'SECTION' object_type,
object_version_number,
section_sequence display_sequence,
scn_id
FROM okc_sections_b
WHERE document_type=p_document_type
AND document_id = p_document_id
AND ( (b_scn_id IS NOT NULL AND scn_id=b_scn_id) OR
(b_scn_id IS NULL AND scn_id IS NULL)
)
UNION ALL
SELECT id,
'ARTICLE' object_type,
object_version_number,
display_sequence,
scn_id
FROM okc_k_articles_b
WHERE document_type=p_document_type
AND document_id = p_document_id
AND ( (b_scn_id IS NOT NULL AND scn_id=b_scn_id) OR
(b_scn_id IS NULL AND scn_id IS NULL)
)
ORDER BY display_sequence;
select scn_id from okc_sections_b
where document_type=p_document_type
AND document_id = p_document_id
AND id=p_ref_scn_id;
SELECT nvl(max(SECTION_SEQUENCE),0) FROM OKC_SECTIONS_B
WHERE document_type=p_document_type
AND document_id=p_document_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_document_type
AND document_id=p_document_id
and scn_id =b_scn_id;
SELECT id,object_version_number
FROM OKC_K_ARTICLES_B
WHERE SCN_ID=b_scn_id
AND display_sequence >= b_ref_sequence
Order by display_sequence;
scn_tbl.delete;
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_mode => 'NORMAL',
p_id => scn_tbl(k).id,
p_section_sequence => l_sequence,
p_object_version_number => scn_tbl(k).object_version_number
);
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 => 'NORMAL',
p_id => scn_tbl(k).id,
p_display_sequence => l_sequence,
p_object_version_number => scn_tbl(k).object_version_number
);
PROCEDURE delete_section(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_validate_commit IN VARCHAR2,
p_validation_string IN VARCHAR2,
p_commit IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_mode IN VARCHAR2,
p_super_user_yn IN VARCHAR2,
p_amendment_description IN VARCHAR2,
p_id IN NUMBER,
p_object_version_number IN NUMBER,
p_lock_terms_yn IN VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'g_delete_section';
l_delete_rec BOOLEAN :=FALSE;
SELECT DOCUMENT_ID,DOCUMENT_TYPE,orig_system_reference_id1 FROM OKC_SECTIONS_B
WHERE ID=P_ID;
SELECT ID,OBJECT_VERSION_NUMBER FROM OKC_SECTIONS_B
WHERE SCN_ID=b_scn_id;
SELECT ID,OBJECT_VERSION_NUMBER FROM OKC_K_ARTICLES_B
WHERE SCN_ID=b_scn_id;
SELECT SUMMARY_AMEND_OPERATION_CODE,AMENDMENT_OPERATION_CODE FROM OKC_SECTIONS_B
WHERE ID=P_ID;
okc_debug.log('2200: Entered delete_section', 2);
G_PKG_NAME, '2200: Entered delete_section' );
SAVEPOINT g_delete_section_GRP;
okc_debug.log('2220: Calling Delete API to delete Section '|| l_scn_id , 2);
G_PKG_NAME, '2220: Calling Delete API to delete Section '|| l_scn_id );
OKC_TERMS_SECTIONS_GRP.delete_section(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
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_mode => p_mode,
p_id => l_scn_id,
p_amendment_description => NULL,
p_object_version_number => l_ovn
);
okc_debug.log('2230: Section '|| l_scn_id||' deleted' , 2);
G_PKG_NAME, '2230: Section '|| l_scn_id||' deleted' );
okc_debug.log('2230: Going to delete Articles of section '|| p_id , 2);
G_PKG_NAME, '2230: Going to delete Articles of section '|| p_id );
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,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_mode => p_mode,
p_super_user_yn => l_super_user_yn,
p_id => l_article_rec.id,
p_amendment_description => NULL,
p_object_version_number => l_article_rec.object_version_number,
p_lock_terms_yn => p_lock_terms_yn );
okc_debug.log('2240: Articles of section '|| p_id||' deleted' , 2);
G_PKG_NAME, '2240: Articles of section '|| p_id||' deleted' );
p_amend_operation_code=>G_AMEND_CODE_DELETED);
OKC_TERMS_SECTIONS_PVT.update_row(
x_return_status => x_return_status,
p_id => p_id,
p_amendment_description => p_amendment_description,
p_amendment_operation_code => G_AMEND_CODE_DELETED,
p_summary_amend_operation_code => l_summary_amend_operation_code,
p_object_version_number => p_object_version_number
);
l_delete_rec := TRUE;
IF p_mode<>'AMEND' or l_delete_rec THEN
-- Following Code will delete the section
OKC_TERMS_SECTIONS_PVT.delete_row(
x_return_status => x_return_status,
p_id => p_id,
p_object_version_number => p_object_version_number
);
okc_debug.log('2300: Leaving delete_section', 2);
G_PKG_NAME, '2300: Leaving delete_section' );
okc_debug.log('2400: Leaving delete_section: OKC_API.G_EXCEPTION_ERROR Exception', 2);
G_PKG_NAME, '2400: Leaving delete_section: OKC_API.G_EXCEPTION_ERROR Exception' );
ROLLBACK TO g_delete_section_GRP;
okc_debug.log('2500: Leaving delete_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
G_PKG_NAME, '2500: Leaving delete_section: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
ROLLBACK TO g_delete_section_GRP;
okc_debug.log('2600: Leaving delete_section because of EXCEPTION: '||sqlerrm, 2);
G_PKG_NAME, '2600: Leaving delete_section because of EXCEPTION: '||sqlerrm );
ROLLBACK TO g_delete_section_GRP;
END delete_section;