The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_updated_by NUMBER,
last_update_date OKC_K_ARTICLES_V.LAST_UPDATE_DATE%TYPE,
last_update_login NUMBER,
-- new 11i10 columns
-- defaulted during insert based on App_Id
document_type OKC_K_ARTICLES_B.DOCUMENT_TYPE%TYPE,
document_id NUMBER ,
source_flag VARCHAR2(1) ,
mandatory_yn OKC_K_ARTICLES_B.mandatory_yn%TYPE ,
scn_id NUMBER ,
label OKC_K_ARTICLES_B.label%TYPE ,
amendment_description OKC_K_ARTICLES_B.amendment_description%TYPE ,
amendment_operation_code OKC_K_ARTICLES_B.amendment_operation_code%TYPE ,
article_version_id NUMBER ,
change_nonstd_yn OKC_K_ARTICLES_B.change_nonstd_yn%TYPE ,
orig_system_reference_code OKC_K_ARTICLES_B.orig_system_reference_code%TYPE ,
orig_system_reference_id1 NUMBER ,
orig_system_reference_id2 NUMBER ,
display_sequence NUMBER ,
print_text_yn OKC_K_ARTICLES_B.print_text_yn%TYPE ,
summary_amend_operation_code OKC_K_ARTICLES_B.summary_amend_operation_code%TYPE ,
ref_article_id NUMBER ,
ref_article_version_id NUMBER,
b_sav_sae_id NUMBER,
last_amended_by OKC_K_ARTICLES_B.LAST_AMENDED_BY%TYPE,
last_amendment_date OKC_K_ARTICLES_B.LAST_AMENDMENT_DATE%TYPE
);
gi_catv_rec.last_updated_by := p_catv_rec.last_updated_by;
gi_catv_rec.last_update_date := p_catv_rec.last_update_date;
gi_catv_rec.last_update_login := p_catv_rec.last_update_login;
g_atnv_rec.last_updated_by := p_atnv_rec.last_updated_by;
g_atnv_rec.last_update_date := p_atnv_rec.last_update_date;
g_atnv_rec.last_update_login := p_atnv_rec.last_update_login;
SELECT
ID,
CHR_ID,
CLE_ID,
CAT_ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
SAV_SAE_ID,
SAV_SAV_RELEASE,
SBT_CODE,
DNZ_CHR_ID,
COMMENTS,
FULLTEXT_YN,
VARIATION_DESCRIPTION,
NAME,
TEXT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CAT_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okc_K_Articles_V
WHERE id = p_id;
SELECT
ID,
CHR_ID,
CLE_ID,
CAT_ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
SAV_SAE_ID,
SAV_SAV_RELEASE,
SBT_CODE,
DNZ_CHR_ID,
COMMENTS,
FULLTEXT_YN,
VARIATION_DESCRIPTION,
NAME,
TEXT,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CAT_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okc_K_Articles_HV
WHERE id = p_id AND major_version=p_major_version;
l_catv_rec.LAST_UPDATED_BY,
l_catv_rec.LAST_UPDATE_DATE,
l_catv_rec.LAST_UPDATE_LOGIN;
l_catv_rec.LAST_UPDATED_BY,
l_catv_rec.LAST_UPDATE_DATE,
l_catv_rec.LAST_UPDATE_LOGIN;
x_last_updated_by => x_catv_rec.last_updated_by,
x_last_update_login => x_catv_rec.last_update_login,
x_last_update_date => x_catv_rec.last_update_date,
x_last_amended_by => x_catv_rec.last_amended_by,
x_last_amendment_date => x_catv_rec.last_amendment_date
);
x_catv_rec.LAST_UPDATED_BY := p_catv_rec.LAST_UPDATED_BY;
x_catv_rec.LAST_UPDATE_DATE := p_catv_rec.LAST_UPDATE_DATE;
x_catv_rec.LAST_UPDATE_LOGIN := p_catv_rec.LAST_UPDATE_LOGIN;
x_catv_rec.LAST_UPDATED_BY := p_catv_rec.LAST_UPDATED_BY;
x_catv_rec.LAST_UPDATE_DATE := p_catv_rec.LAST_UPDATE_DATE;
x_catv_rec.LAST_UPDATE_LOGIN := p_catv_rec.LAST_UPDATE_LOGIN;
SELECT authoring_org_id
FROM OKC_K_HEADERS_B WHERE document_id=p_doc_id;
SELECT ID FROM OKC_SECTIONS_B
WHERE document_type = p_doc_type AND document_id = p_doc_id
AND scn_code = G_UNASSIGNED_SECTION_CODE;
SELECT TEMPLATE_ID FROM okc_template_usages_v
WHERE document_type = p_doc_type AND document_id = p_doc_id ;
SELECT TEMPLATE_ID FROM okc_terms_templates_all
WHERE template_name = l_tmpl_name and nvl(org_id,-99)=nvl(l_org_id,-99);
SELECT OKC_TERMS_TEMPLATES_ALL_S.NEXTVAL
INTO l_tmpl_id FROM DUAL;
INSERT INTO OKC_TERMS_TEMPLATES_ALL(
TEMPLATE_NAME,
TEMPLATE_ID,
WORKING_COPY_FLAG,
INTENT,
STATUS_CODE,
START_DATE,
GLOBAL_FLAG,
CONTRACT_EXPERT_ENABLED,
DESCRIPTION,
ORG_ID,
ORIG_SYSTEM_REFERENCE_CODE,
HIDE_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
VALUES (
l_tmpl_name,
l_tmpl_id,
'N',
Decode( p_doc_type,'OKC_BUY','B','OKE_BUY','B', 'S'),
'APPROVED',
to_date('01-01-1951','DD-MM-YYYY'),
'N',
'N',
'Dummy Template for 11.5.10 Upgrade',
l_org_id,
decode (p_doc_type,'OKE_SELL', 'OKC11510UPG:OKE', 'OKE_BUY', 'OKC11510UPG:OKE', 'OKC11510UPG'),
decode(p_doc_type,'OKS','N','Y'),
1,
Fnd_Global.User_Id,
trunc(sysdate),
Fnd_Global.User_Id,
Fnd_Global.Login_Id,
trunc(sysdate)
);
INSERT INTO OKC_ALLOWED_TMPL_USAGES(
ALLOWED_TMPL_USAGES_ID,
TEMPLATE_ID,
DOCUMENT_TYPE,
DEFAULT_YN,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
VALUES (
OKC_ALLOWED_TMPL_USAGES_S.NEXTVAL,
l_tmpl_id,
p_doc_type,
'N',
1,
Fnd_Global.User_Id,
trunc(sysdate),
Fnd_Global.User_Id,
Fnd_Global.Login_Id,
trunc(sysdate)
);
SELECT article_version_id
FROM okc_article_versions
WHERE article_id=p_art_id
AND (nvl(sav_release, To_Char(article_version_number))=p_ver_num
OR p_ver_num IS NULL AND SYSDATE BETWEEN Start_date AND nvl(end_date,sysdate)
)
;
SELECT t.sav_sae_id, t.article_version_id, a.standard_yn
FROM okc_terms_articles_v t, okc_articles_all a
WHERE t.id=p_id AND t.sav_sae_id=a.article_id;
SELECT intent FROM OKC_BUS_DOC_TYPES_B
WHERE DOCUMENT_TYPE=p_doc_type;
p_insert_by_reference => 'N',
p_lock_text => 'N',
p_global_yn => 'N',
p_article_status => NULL,
p_sav_release => p_catv_rec.sav_sav_release,
p_start_date => NULL,
p_end_date => NULL,
p_std_article_version_id => l_art_ver_id,
p_display_name => p_catv_rec.name,
p_translated_yn => 'N',
p_article_description => p_catv_rec.comments,
p_date_approved => NULL,
p_default_section => NULL,
p_reference_source => NULL,
p_reference_text => NULL,
p_additional_instructions => NULL,
p_variation_description => p_catv_rec.variation_description,
p_v_orig_system_reference_code => p_catv_rec.orig_system_reference_code,
p_v_orig_system_reference_id1 => NULL,
p_v_orig_system_reference_id2 => NULL,
p_global_article_version_id => NULL,
x_article_version_id => x_art_ver_id
);
okc_debug.log('11400: non-std article is to be updated.Version id is '||l_art_ver_id, 2);
OKC_ARTICLES_GRP.update_article(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_article_id => l_art_id,
p_article_title => p_catv_rec.name,
p_article_number => NULL,
p_standard_yn => NULL,
p_article_intent => NULL,
p_article_language => NULL,
p_article_type => p_catv_rec.sbt_code,
p_orig_system_reference_code => NULL,
p_orig_system_reference_id1 => NULL,
p_orig_system_reference_id2 => NULL,
p_cz_transfer_status_flag => NULL,
-- Article Version Attributes
p_article_version_id => l_art_ver_id,
p_article_text => p_catv_rec.text,
p_provision_yn => NULL,
p_insert_by_reference => NULL,
p_lock_text => NULL,
p_global_yn => NULL,
p_article_status => NULL,
p_sav_release => NULL,
p_start_date => NULL,
p_end_date => NULL,
p_std_article_version_id => NULL,
p_display_name => p_catv_rec.name,
p_translated_yn => NULL,
p_article_description => p_catv_rec.comments,
p_date_approved => NULL,
p_default_section => NULL,
p_reference_source => NULL,
p_reference_text => NULL,
p_additional_instructions => NULL,
p_variation_description => NULL,
p_v_orig_system_reference_code => NULL,
p_v_orig_system_reference_id1 => NULL,
p_v_orig_system_reference_id2 => NULL
);
okc_debug.log('300: non-std article updated.', 2);
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 = p_scn_id;
procedure update_k_article(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_catv_rec IN catv_rec_type,
x_catv_rec OUT NOCOPY catv_rec_type) is
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_ARTICLE';
okc_debug.log('1000: before update_article', 2);
OKC_K_ARTICLES_GRP.update_article(
p_api_version => p_api_version,
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 => gi_catv_rec.id,
p_sav_sae_id => gi_catv_rec.b_sav_sae_id,
p_document_type => gi_catv_rec.document_type,
p_document_id => gi_catv_rec.document_id,
p_source_flag => gi_catv_rec.source_flag,
p_mandatory_yn => gi_catv_rec.mandatory_yn,
p_scn_id => gi_catv_rec.scn_id,
p_label => gi_catv_rec.label,
p_amendment_description => gi_catv_rec.amendment_description,
p_article_version_id => gi_catv_rec.article_version_id,
p_change_nonstd_yn => gi_catv_rec.change_nonstd_yn,
p_orig_system_reference_code => gi_catv_rec.orig_system_reference_code,
p_orig_system_reference_id1 => gi_catv_rec.orig_system_reference_id1,
p_orig_system_reference_id2 => gi_catv_rec.orig_system_reference_id2,
p_display_sequence => gi_catv_rec.display_sequence,
p_attribute_category => gi_catv_rec.attribute_category,
p_attribute1 => gi_catv_rec.attribute1,
p_attribute2 => gi_catv_rec.attribute2,
p_attribute3 => gi_catv_rec.attribute3,
p_attribute4 => gi_catv_rec.attribute4,
p_attribute5 => gi_catv_rec.attribute5,
p_attribute6 => gi_catv_rec.attribute6,
p_attribute7 => gi_catv_rec.attribute7,
p_attribute8 => gi_catv_rec.attribute8,
p_attribute9 => gi_catv_rec.attribute9,
p_attribute10 => gi_catv_rec.attribute10,
p_attribute11 => gi_catv_rec.attribute11,
p_attribute12 => gi_catv_rec.attribute12,
p_attribute13 => gi_catv_rec.attribute13,
p_attribute14 => gi_catv_rec.attribute14,
p_attribute15 => gi_catv_rec.attribute15,
p_print_text_yn => gi_catv_rec.print_text_yn,
p_ref_article_id => gi_catv_rec.ref_article_id,
p_ref_article_version_id => gi_catv_rec.ref_article_version_id,
p_object_version_number => gi_catv_rec.object_version_number
);
end update_k_article;
procedure update_k_article(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_catv_tbl IN catv_tbl_type,
x_catv_tbl OUT NOCOPY catv_tbl_type) is
i NUMBER;
update_k_article(p_api_version=>p_api_version,
p_init_msg_list=>OKC_API.G_FALSE,
x_return_status=>l_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data,
p_catv_rec=> p_catv_tbl(i),
x_catv_rec=>x_catv_tbl(i));
end update_k_article;
procedure delete_k_article(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_catv_rec IN catv_rec_type) is
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_K_ARTICLE';
OKC_K_ARTICLES_GRP.delete_article(
p_api_version => p_api_version,
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_validation_string => NULL,
p_id => p_catv_rec.id,
p_object_version_number => p_catv_rec.object_version_number
);
end delete_k_article;
procedure delete_k_article(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_catv_tbl IN catv_tbl_type) is
i NUMBER;
delete_k_article(p_api_version=>p_api_version,
p_init_msg_list=>OKC_API.G_FALSE,
x_return_status=>l_return_status,
x_msg_count=>x_msg_count,
x_msg_data=>x_msg_data,
p_catv_rec=>p_catv_tbl(i));
end delete_k_article;
procedure delete_article_translation(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_atnv_rec IN atnv_rec_type) is
begin
OKC_API.init_msg_list(p_init_msg_list);
Okc_Debug.Log('400: Entered delete_article_translation - DUMMY procedure', 2);
end delete_article_translation;
procedure delete_article_translation(p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_atnv_tbl IN atnv_tbl_type) is
begin
OKC_API.init_msg_list(p_init_msg_list);
Okc_Debug.Log('400: Entered delete_article_translation - DUMMY procedure', 2);
end delete_article_translation;
select name
from OKC_STD_ARTICLES_V
where ID = p_sav_sae_id;