The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
SELECT NVL(standard_yn,'N') standard_flag,
kart.article_version_id,
kart.ORIG_SYSTEM_REFERENCE_ID1
FROM okc_articles_all lib,
okc_k_articles_b kart
WHERE lib.article_id = kart.sav_sae_id
AND kart.id= p_cat_id;
SELECT article_version_id
FROM okc_k_articles_b
WHERE id = p_cat_id;
SELECT VAR.VARIABLE_VALUE_ID
FROM OKC_K_ART_VARIABLES VAR
WHERE VAR.CAT_ID = p_cat_id
AND VAR.VARIABLE_CODE = p_variable_code;
SELECT kart1.id
FROM okc_articles_all lib,
okc_k_articles_b kart,
okc_k_articles_b kart1
WHERE lib.article_id = kart.sav_sae_id
AND kart.id= p_cat_id
AND kart1.document_id = p_source_doc_id
AND kart1.document_type = p_source_doc_type
AND kart1.orig_system_reference_id1 = kart.orig_system_reference_id1;
SELECT var.variable_value,
var.variable_value_id
FROM okc_k_art_variables var
WHERE var.cat_id = p_cat_id
AND var.variable_code = p_variable_code;
SELECT NVL(standard_yn,'N') standard_flag,
kart.article_version_id,
kart.ORIG_SYSTEM_REFERENCE_ID1
FROM okc_articles_all lib,
okc_k_articles_b kart
WHERE lib.article_id = kart.sav_sae_id
AND kart.id= p_cat_id;
SELECT article_version_id
FROM okc_k_articles_b
WHERE id = p_cat_id;
SELECT VAR.VARIABLE_VALUE
FROM OKC_K_ART_VARIABLES VAR
WHERE VAR.CAT_ID = p_cat_id
AND VAR.VARIABLE_CODE = p_variable_code;
SELECT KART.ID CAT_ID,
VAR.VARIABLE_CODE,
BUSVAR.VARIABLE_TYPE,
BUSVAR.EXTERNAL_YN,
BUSVAR.VALUE_SET_ID,
DECODE(p_keep_orig_ref,'Y',get_orig_var_val(
KART.ID,VAR.VARIABLE_CODE,
P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'VALUE'),
get_variable_value(KART.ID,VAR.VARIABLE_CODE)) VARIABLE_VALUE,
DECODE(p_keep_orig_ref,'Y',get_orig_var_val(
KART.ID,VAR.VARIABLE_CODE,
P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'ID'),
get_variable_value_id(KART.ID,VAR.VARIABLE_CODE)) VARIABLE_VALUE_ID,
'N' OVERRIDE_GLOBAL_YN
FROM OKC_ARTICLE_VARIABLES VAR,
OKC_K_ARTICLES_B KART,
OKC_BUS_VARIABLES_B BUSVAR
WHERE KART.ARTICLE_VERSION_ID=VAR.ARTICLE_VERSION_ID
AND KART.DOCUMENT_TYPE=p_target_doc_type
AND KART.DOCUMENT_ID=p_target_doc_id
AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE
AND not exists ( select 'x' from okc_k_art_variables where cat_id=kart.id);
SELECT KART.ID CAT_ID,
VAR.VARIABLE_CODE,
BUSVAR.VARIABLE_TYPE,
BUSVAR.EXTERNAL_YN,
BUSVAR.VALUE_SET_ID,
VAR.VARIABLE_VALUE,
VAR.VARIABLE_VALUE_ID,
VAR.OVERRIDE_GLOBAL_YN
FROM OKC_K_ART_VARIABLES VAR,
OKC_K_ARTICLES_B KART,
OKC_K_ARTICLES_B KART1,
OKC_BUS_VARIABLES_B BUSVAR
WHERE KART.DOCUMENT_TYPE=p_target_doc_type
AND KART.DOCUMENT_ID=p_target_doc_id
AND KART1.DOCUMENT_TYPE=p_source_doc_type
AND KART1.DOCUMENT_ID=p_source_doc_id
AND KART.ORIG_SYSTEM_REFERENCE_CODE=G_COPY
AND ((KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ID AND P_KEEP_ORIG_REF = 'N') OR
(KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ORIG_SYSTEM_REFERENCE_ID1 AND P_KEEP_ORIG_REF = 'Y'))
AND KART1.ID=VAR.CAT_ID
AND BUSVAR.VARIABLE_CODE=VAR.VARIABLE_CODE ;
INSERT INTO OKC_K_ART_VARIABLES(cat_id,
variable_code,
variable_type,
external_yn,
attribute_value_set_id,
variable_value,
variable_value_id,
override_global_yn,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (cat_tbl(i),
var_tbl(i),
var_type_tbl(i),
external_yn_tbl(i),
value_set_id_tbl(i),
var_value_tbl(i),
var_value_id_tbl(i),
override_global_yn_tbl(i),
1,
sysdate,
Fnd_Global.User_Id,
sysdate,
Fnd_Global.User_Id,
Fnd_Global.Login_Id);
SELECT nvl(PROVISION_ALLOWED_YN,'Y') FROM OKC_BUS_DOC_TYPES_B
WHERE DOCUMENT_TYPE=p_target_doc_type;
SELECT STANDARD_YN,ARTICLE_TITLE FROM OKC_ARTICLES_ALL
WHERE article_id=b_article_id;
SELECT global_flag FROM OKC_TERMS_TEMPLATES_ALL
WHERE template_id=p_source_doc_id;
SELECT org_id FROM OKC_TERMS_TEMPLATES_ALL
WHERE template_id=p_target_doc_id;
SELECT article_version_id FROM OKC_ARTICLE_VERSIONS
WHERE article_id= b_article_id
AND article_status in ('ON_HOLD','APPROVED')
AND nvl(p_article_effective_date,sysdate) >= Start_date
AND nvl(p_article_effective_date,sysdate) <= nvl(end_date, nvl(p_article_effective_date,sysdate) +1);
SELECT article_version_id FROM OKC_ARTICLE_VERSIONS
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 VERS2.ARTICLE_ID,VERS2.ARTICLE_VERSION_ID
FROM OKC_ARTICLE_VERSIONS VERS1,OKC_ARTICLE_VERSIONS VERS2
WHERE VERS1.ARTICLE_VERSION_ID=b_version_id
AND VERS2.ARTICLE_VERSION_ID=VERS1.STD_ARTICLE_VERSION_ID;
SELECT
SAV_SAE_ID,
KART.ATTRIBUTE_CATEGORY,
KART.ATTRIBUTE1,
KART.ATTRIBUTE2,
KART.ATTRIBUTE3,
KART.ATTRIBUTE4,
KART.ATTRIBUTE5,
KART.ATTRIBUTE6,
KART.ATTRIBUTE7,
KART.ATTRIBUTE8,
KART.ATTRIBUTE9,
KART.ATTRIBUTE10,
KART.ATTRIBUTE11,
KART.ATTRIBUTE12,
KART.ATTRIBUTE13,
KART.ATTRIBUTE14,
KART.ATTRIBUTE15,
SOURCE_FLAG,
ARTICLE_VERSION_ID,
CHANGE_NONSTD_YN,
SCN.ID SCN_ID,
DECODE(P_KEEP_ORIG_REF,'Y',KART.ORIG_SYSTEM_REFERENCE_ID1,KART.ID) ORIG_SYSTEM_REFERENCE_ID1,
MANDATORY_YN,
KART.LABEL,
DISPLAY_SEQUENCE,
ref_article_id,
ref_article_version_id,
DECODE(p_source_doc_type,G_TEMPLATE_DOC_TYPE,sav_sae_id,orig_article_id) orig_article_id
FROM OKC_K_ARTICLES_B KART,
OKC_SECTIONS_B SCN
WHERE KART.DOCUMENT_TYPE=p_source_doc_type
AND KART.DOCUMENT_ID=p_source_doc_id
AND SCN.DOCUMENT_TYPE = p_target_doc_type
AND SCN.DOCUMENT_ID =p_target_doc_id
AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
AND SCN.ORIG_SYSTEM_REFERENCE_ID1=KART.SCN_ID
AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED;
SELECT
KART.SAV_SAE_ID,
KART.ATTRIBUTE_CATEGORY,
KART.ATTRIBUTE1,
KART.ATTRIBUTE2,
KART.ATTRIBUTE3,
KART.ATTRIBUTE4,
KART.ATTRIBUTE5,
KART.ATTRIBUTE6,
KART.ATTRIBUTE7,
KART.ATTRIBUTE8,
KART.ATTRIBUTE9,
KART.ATTRIBUTE10,
KART.ATTRIBUTE11,
KART.ATTRIBUTE12,
KART.ATTRIBUTE13,
KART.ATTRIBUTE14,
KART.ATTRIBUTE15,
KART.SOURCE_FLAG,
KART.ARTICLE_VERSION_ID,
KART.CHANGE_NONSTD_YN,
SCN.ID SCN_ID,
KART.ORIG_SYSTEM_REFERENCE_ID1 ORIG_SYSTEM_REFERENCE_ID1,
KART.MANDATORY_YN,
KART.LABEL,
KART.DISPLAY_SEQUENCE,
KART.ref_article_id,
KART.ref_article_version_id,
DECODE(p_source_doc_type,G_TEMPLATE_DOC_TYPE,KART.sav_sae_id,KART.orig_article_id) orig_article_id
FROM OKC_K_ARTICLES_B KART,
OKC_SECTIONS_B SCN,
OKC_SECTIONS_B SCN1,
OKC_K_ARTICLES_B KART1
WHERE KART.DOCUMENT_TYPE= p_source_doc_type
AND KART.DOCUMENT_ID= p_source_doc_id
AND SCN.DOCUMENT_TYPE = p_target_doc_type
AND SCN.DOCUMENT_ID = p_target_doc_id
AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
AND SCN1.DOCUMENT_TYPE = p_source_doc_type
AND SCN1.DOCUMENT_ID = p_source_doc_id
AND SCN1.ORIG_SYSTEM_REFERENCE_ID1 = SCN.ORIG_SYSTEM_REFERENCE_ID1
AND KART1.ORIG_SYSTEM_REFERENCE_ID1 = KART.ORIG_SYSTEM_REFERENCE_ID1
AND KART1.SCN_ID = SCN1.ID
AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED;
SELECT
SAV_SAE_ID,
KART.ATTRIBUTE_CATEGORY,
KART.ATTRIBUTE1,
KART.ATTRIBUTE2,
KART.ATTRIBUTE3,
KART.ATTRIBUTE4,
KART.ATTRIBUTE5,
KART.ATTRIBUTE6,
KART.ATTRIBUTE7,
KART.ATTRIBUTE8,
KART.ATTRIBUTE9,
KART.ATTRIBUTE10,
KART.ATTRIBUTE11,
KART.ATTRIBUTE12,
KART.ATTRIBUTE13,
KART.ATTRIBUTE14,
KART.ATTRIBUTE15,
SOURCE_FLAG,
ARTICLE_VERSION_ID,
CHANGE_NONSTD_YN,
SCN.ID SCN_ID,
DECODE(P_KEEP_ORIG_REF,'Y',KART.ORIG_SYSTEM_REFERENCE_ID1,KART.ID) ORIG_SYSTEM_REFERENCE_ID1,
MANDATORY_YN,
KART.LABEL,
DISPLAY_SEQUENCE,
ref_article_id,
ref_article_version_id,
orig_article_id
FROM OKC_K_ARTICLES_BH KART,
OKC_SECTIONS_B SCN
WHERE KART.DOCUMENT_TYPE=p_source_doc_type
AND KART.DOCUMENT_ID=p_source_doc_id
AND KART.MAJOR_VERSION = nvl(p_source_version_number,OKC_API.G_MISS_NUM)
AND SCN.DOCUMENT_TYPE = p_target_doc_type
AND SCN.DOCUMENT_ID =p_target_doc_id
AND SCN.ORIG_SYSTEM_REFERENCE_CODE =G_COPY
AND SCN.ORIG_SYSTEM_REFERENCE_ID1=KART.SCN_ID;
SELECT ADP.LOCAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
ADP.ADOPTION_TYPE,
VERS1.ARTICLE_ID
FROM OKC_ARTICLE_VERSIONS VERS,
OKC_ARTICLE_ADOPTIONS ADP,
OKC_ARTICLE_VERSIONS VERS1
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
AND VERS.ARTICLE_ID = b_article_id
AND nvl(p_article_effective_date,sysdate) >= VERS.START_DATE
AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_article_effective_date,sysdate) +1)
AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
AND VERS1.ARTICLE_VERSION_ID =ADP.LOCAL_ARTICLE_VERSION_ID
AND ADP.ADOPTION_TYPE = 'LOCALIZED'
AND ADP.LOCAL_ORG_ID = b_local_org_id
AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
UNION ALL
SELECT ADP.GLOBAL_ARTICLE_VERSION_ID LOCAL_ARTICLE_VERSION_ID,
ADP.ADOPTION_TYPE,
VERS.ARTICLE_ID
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(p_article_effective_date,sysdate) >= VERS.START_DATE
AND nvl(p_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(p_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 nvl(PROVISION_YN,'N') provision_yn
FROM OKC_ARTICLE_VERSIONS
WHERE ARTICLE_VERSION_ID=b_article_version_id;
SELECT org_id
FROM OKC_ARTICLES_ALL
WHERE article_id = b_article_id;
SELECT DECODE(ADP.LOCAL_ARTICLE_VERSION_ID,NULL,ADP.GLOBAL_ARTICLE_VERSION_ID,ADP.LOCAL_ARTICLE_VERSION_ID),
ADP.ADOPTION_TYPE
FROM OKC_ARTICLE_ADOPTIONS ADP
WHERE ADP.LOCAL_ORG_ID = b_article_org_id
AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
AND ADP.GLOBAL_ARTICLE_VERSION_ID IN (SELECT ARTICLE_VERSION_ID
FROM OKC_ARTICLE_VERSIONS
WHERE article_id = b_article_id)
ORDER BY ADP.creation_date desc;
SELECT article_id
FROM okc_article_versions
WHERE article_version_id = b_article_version_id;
INSERT INTO OKC_K_ARTICLES_B(
ID,
SAV_SAE_ID,
DOCUMENT_TYPE,
DOCUMENT_ID,
CHR_ID,
DNZ_CHR_ID,
SOURCE_FLAG,
MANDATORY_YN,
SCN_ID,
LABEL,
AMENDMENT_DESCRIPTION,
AMENDMENT_OPERATION_CODE,
ARTICLE_VERSION_ID,
CHANGE_NONSTD_YN,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
DISPLAY_SEQUENCE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRINT_TEXT_YN,
REF_ARTICLE_ID,
REF_ARTICLE_VERSION_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
ORIG_ARTICLE_ID)
VALUES(OKC_K_ARTICLES_B_S.nextval,
sav_sae_tbl1(i),
p_target_doc_type,
p_target_doc_id,
decode(p_target_doc_type,'OKC_BUY',p_target_doc_id,'OKC_SELL',p_target_doc_id, 'OKO',p_target_doc_id,'OKS',p_target_doc_id,'OKE_BUY',p_target_doc_id, 'OKE_SELL',p_target_doc_id, 'OKL',p_target_doc_id,NULL),
decode(p_target_doc_type,'OKC_BUY',p_target_doc_id,'OKC_SELL',p_target_doc_id, 'OKO',p_target_doc_id,'OKS',p_target_doc_id,'OKE_BUY',p_target_doc_id, 'OKE_SELL',p_target_doc_id, 'OKL',p_target_doc_id,NULL),
Source_flag_tbl1(i),
Mandatory_yn_tbl1(i),
Scn_id_tbl1(i),
Label_tbl1(i),
Null,
Null,
decode(p_target_doc_type, OKC_TERMS_UTIL_GRP.G_TMPL_DOC_TYPE,NULL,Article_Version_tbl1(i)),
Change_nonstd_yn_tbl1(i),
G_COPY,
Orig_System_Reference_id1_tbl1(i),
Null,
Display_sequence_tbl1(i),
Attribute_category_tbl1(i),
Attribute1_tbl1(i),
Attribute2_tbl1(i),
Attribute3_tbl1(i),
Attribute4_tbl1(i),
Attribute5_tbl1(i),
Attribute6_tbl1(i),
Attribute7_tbl1(i),
Attribute8_tbl1(i),
Attribute9_tbl1(i),
Attribute10_tbl1(i),
Attribute11_tbl1(i),
Attribute12_tbl1(i),
Attribute13_tbl1(i),
Attribute14_tbl1(i),
Attribute15_tbl1(i),
Null,
ref_article_id_tbl(i),
ref_article_version_id_tbl(i),
1,
Fnd_Global.User_Id,
sysdate,
Fnd_Global.User_Id,
Fnd_Global.Login_Id,
sysdate,
orig_article_id_tbl1(i));
SELECT OKC_SECTIONS_B_S.NEXTVAL,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SCN_ID SCN_ID,
DECODE(P_KEEP_ORIG_REF,'Y',ORIG_SYSTEM_REFERENCE_ID1,ID) ORIG_SYSTEM_REFERENCE_ID1,
SECTION_SEQUENCE,
LABEL,
SCN_CODE,
HEADING,
PRINT_YN ,
DESCRIPTION
FROM OKC_SECTIONS_B
WHERE DOCUMENT_TYPE=P_SOURCE_DOC_TYPE
AND DOCUMENT_ID=P_SOURCE_DOC_ID
AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED;
SELECT OKC_SECTIONS_B_S.NEXTVAL,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
SCN_ID SCN_ID,
DECODE(P_KEEP_ORIG_REF, 'Y', ORIG_SYSTEM_REFERENCE_ID1,ID) ORIG_SYSTEM_REFERENCE_ID1,
SECTION_SEQUENCE,
LABEL,
SCN_CODE,
HEADING,
PRINT_YN ,
DESCRIPTION
FROM OKC_SECTIONS_BH
WHERE DOCUMENT_TYPE=P_SOURCE_DOC_TYPE
AND DOCUMENT_ID=P_SOURCE_DOC_ID
AND MAJOR_VERSION = nvl(p_source_version_number,OKC_API.G_MISS_NUM);
INSERT INTO OKC_SECTIONS_B(
ID,
DOCUMENT_TYPE,
DOCUMENT_ID,
CHR_ID,
SCN_ID,
LABEL,
AMENDMENT_DESCRIPTION,
AMENDMENT_OPERATION_CODE,
ORIG_SYSTEM_REFERENCE_CODE,
ORIG_SYSTEM_REFERENCE_ID1,
ORIG_SYSTEM_REFERENCE_ID2,
SECTION_SEQUENCE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PRINT_YN,
HEADING,
SCN_CODE,
DESCRIPTION,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE)
VALUES(id_tbl(i),
p_target_doc_type,
p_target_doc_id,
decode(p_target_doc_type,'OKC_BUY',p_target_doc_id, 'OKC_SELL',p_target_doc_id, 'OKO',p_target_doc_id,'OKS',p_target_doc_id,'OKE_BUY',p_target_doc_id, 'OKE_SELL',p_target_doc_id, 'OKL',p_target_doc_id,NULL),
Scn_id_tbl(i),
Label_tbl(i),
Null,
Null,
G_COPY,
Orig_System_Reference_id1_tbl(i),
Null,
section_sequence_tbl(i),
Attribute_category_tbl(i),
Attribute1_tbl(i),
Attribute2_tbl(i),
Attribute3_tbl(i),
Attribute4_tbl(i),
Attribute5_tbl(i),
Attribute6_tbl(i),
Attribute7_tbl(i),
Attribute8_tbl(i),
Attribute9_tbl(i),
Attribute10_tbl(i),
Attribute11_tbl(i),
Attribute12_tbl(i),
Attribute13_tbl(i),
Attribute14_tbl(i),
Attribute15_tbl(i),
print_yn_tbl(i),
heading_tbl(i),
scn_code_tbl(i),
description_tbl(i),
1,
Fnd_Global.User_Id,
sysdate,
Fnd_Global.User_Id,
Fnd_Global.Login_Id,
sysdate);
SELECT count(*)
FROM OKC_K_ARTICLES_B
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
AND SOURCE_FLAG IS NULL;
SELECT id
FROM OKC_SECTIONS_B
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND SCN_CODE = 'UNASSIGNED';
SELECT ROWID FROM OKC_K_ARTICLES_B
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
AND SOURCE_FLAG IS NULL FOR UPDATE NOWAIT;
SELECT ROWID FROM OKC_K_ART_VARIABLES
WHERE CAT_ID IN
(SELECT ID FROM OKC_K_ARTICLES_B
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id
AND SOURCE_FLAG IS NOT NULL) FOR UPDATE NOWAIT;
SELECT ROWID FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id
AND SOURCE_FLAG IS NOT NULL FOR UPDATE NOWAIT;
SELECT ROWID FROM OKC_SECTIONS_B
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id
AND id <> b_scn_id FOR UPDATE NOWAIT;
UPDATE OKC_K_ARTICLES_B
SET SCN_ID = l_unassigned_scn_id,
LAST_UPDATED_BY = FND_GLOBAl.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAl.LOGIN_ID,
LAST_UPDATE_DATE = sysdate
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
AND SOURCE_FLAG IS NULL;
DELETE FROM OKC_K_ART_VARIABLES WHERE CAT_ID IN
(SELECT ID FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id
AND SOURCE_FLAG IS NOT NULL);
DELETE FROM OKC_K_ARTICLES_B WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id
AND SOURCE_FLAG IS NOT NULL;
DELETE FROM OKC_SECTIONS_B WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id
AND id <> l_unassigned_scn_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: Calling OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables ');
OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables (
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE,
p_doc_id => p_doc_id,
p_doc_type => p_doc_type,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'180: After Call to OKC_DELIVERABLE_PROCESS_PVT.delete_template_deliverables ');
SELECT '!' FROM OKC_ALLOWED_TMPL_USAGES
WHERE template_id=p_source_doc_id
AND document_type = p_target_doc_type;
SELECT * FROM OKC_TERMS_TEMPLATES_ALL
WHERE template_id=p_source_doc_id;
SELECT name FROM OKC_BUS_DOC_TYPES_V
WHERE document_type = p_target_doc_type;
SELECT '!' FROM OKC_TEMPLATE_USAGES
WHERE document_type = p_target_doc_type
AND document_id = p_target_doc_id;
SELECT ROWID FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE=p_target_doc_type
AND DOCUMENT_ID=p_target_doc_id
FOR UPDATE NOWAIT;
SELECT * FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE=p_source_doc_type
AND DOCUMENT_ID=p_source_doc_id;
SELECT 'Y' FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE=p_target_doc_type
AND DOCUMENT_ID=p_target_doc_id;
SELECT * from okc_allowed_tmpl_usages where
template_id=b_source_doc_id;
select OKC_ALLOWED_TMPL_USAGES_S.NEXTVAL into l_tmpl_usage_id from dual;
SELECT decode(p_keep_version,'Y','N','Y') INTO l_get_from_library FROM DUAL;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400:Update the existing OKC_TEMPLATE_USAGES record with new template id : '||p_source_doc_id);
UPDATE OKC_TEMPLATE_USAGES
SET TEMPLATE_ID = p_source_doc_id,
DOC_NUMBERING_SCHEME = l_tmpl_rec.tmpl_numbering_scheme,
ARTICLE_EFFECTIVE_DATE = p_article_effective_date, -- To Check and confirm with PMs
CONFIG_HEADER_ID = NULL,
CONFIG_REVISION_NUMBER = NULL,
VALID_CONFIG_YN = NULL,
LAST_UPDATED_BY = FND_GLOBAl.USER_ID,
LAST_UPDATE_LOGIN = FND_GLOBAl.LOGIN_ID,
LAST_UPDATE_DATE = sysdate,
CONTRACT_SOURCE_CODE = G_STRUCT_CONTRACT_SOURCE,
AUTHORING_PARTY_CODE = G_INTERNAL_PARTY_CODE,
CONTRACT_ADMIN_ID = p_contract_admin_id,
LEGAL_CONTACT_ID = p_legal_contact_id
WHERE document_type = p_target_doc_type
AND document_id = p_target_doc_id;
SELECT * FROM OKC_TEMPLATE_USAGES_H
WHERE DOCUMENT_TYPE=p_source_doc_type
AND DOCUMENT_ID=p_source_doc_id
AND MAJOR_VERSION=p_source_version_number;
SELECT 'Y' FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE=p_target_doc_type
AND DOCUMENT_ID=p_target_doc_id;
SELECT KART.ID CAT_ID,
VAR.VARIABLE_CODE,
VAR.VARIABLE_TYPE,
VAR.EXTERNAL_YN,
VAR.ATTRIBUTE_VALUE_SET_ID,
VAR.VARIABLE_VALUE,
VAR.VARIABLE_VALUE_ID,
VAR.OVERRIDE_GLOBAL_YN
FROM OKC_K_ART_VARIABLES_H VAR,
OKC_K_ARTICLES_B KART,
OKC_K_ARTICLES_BH KART1
WHERE KART.ORIG_SYSTEM_REFERENCE_ID1=KART1.ID
AND VAR.CAT_ID=KART1.ID
AND KART.DOCUMENT_TYPE=p_target_doc_type
AND KART.DOCUMENT_ID=p_target_doc_id
AND KART1.DOCUMENT_TYPE=p_source_doc_type
AND KART1.DOCUMENT_ID=p_source_doc_id
AND KART1.MAJOR_VERSION = p_source_version_number
AND KART.ORIG_SYSTEM_REFERENCE_CODE=G_COPY
AND VAR.MAJOR_VERSION = p_source_version_number;
INSERT INTO OKC_K_ART_VARIABLES(cat_id,
variable_code,
variable_type,
external_yn,
attribute_value_set_id,
variable_value,
variable_value_id,
override_global_yn,
object_version_number,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
VALUES (cat_tbl(i),
var_tbl(i),
var_type_tbl(i),
external_yn_tbl(i),
value_set_id_tbl(i),
var_value_tbl(i),
var_value_id_tbl(i),
override_global_yn_tbl(i),
1,
sysdate,
Fnd_Global.User_Id,
sysdate,
Fnd_Global.User_Id,
Fnd_Global.Login_Id);