[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
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
, VAR.MR_VARIABLE_HTML
, VAR.MR_VARIABLE_XML
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_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
-- , VAR.MR_VARIABLE_HTML
-- , VAR.MR_VARIABLE_XML
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 document_type_class
FROM okc_bus_doc_types_b
WHERE document_type = c_doc_type;
SELECT Nvl(AMENDMENT_NUMBER,0) FROM PON_AUCTION_HEADERS_ALL
WHERE AUCTION_HEADER_ID = c_doc_id;
SELECT KART.ID CAT_ID,
VAR.VARIABLE_CODE,
BUSVAR.VARIABLE_TYPE,
BUSVAR.EXTERNAL_YN,
BUSVAR.VALUE_SET_ID,
Decode(Nvl(BUSVAR.mrv_flag,'N'), 'Y', NULL, 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 (Nvl(BUSVAR.mrv_flag,'N'), 'Y', NULL, 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,
Decode( Nvl(BUSVAR.mrv_flag,'N'), 'Y',
DECODE(p_keep_orig_ref,'Y',
get_orig_var_val_xml(
KART.ID,VAR.VARIABLE_CODE,
P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'HTML')
,(SELECT src.mr_variable_html FROM okc_k_art_variables src WHERE src.cat_id= KART.orig_system_reference_id1 AND src. variable_code = VAR.variable_code)
)
,to_clob(NULL)) mr_variable_html,
Decode(Nvl(BUSVAR.mrv_flag,'N'), 'Y', DECODE(p_keep_orig_ref,
'Y',get_orig_var_val_xml(
KART.ID,VAR.VARIABLE_CODE,
P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'XML')
,(SELECT src.mr_variable_xml FROM okc_k_art_variables src WHERE src.cat_id= KART.orig_system_reference_id1 AND src.variable_code = VAR.variable_code)
)
,to_clob(NULL)) mr_variable_xml,
Decode(Nvl(BUSVAR.mrv_flag,'N'), 'Y', DECODE(p_keep_orig_ref,
'Y', To_Number(get_orig_var_val(
KART.ID,VAR.VARIABLE_CODE,
P_SOURCE_DOC_ID,P_SOURCE_DOC_TYPE,'CAT_ID'))
,KART.orig_system_reference_id1
)
,NULL) SourceCatId
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,
VAR.mr_variable_html,
VAR.mr_variable_xml,
KART1.cat_id SourceCatId
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
AND ( p_retain_lock_terms_yn = 'N'
OR
( p_retain_lock_terms_yn = 'Y'
AND NOT EXISTS ( SELECT 'LOCKEXISTS'
FROM okc_k_entity_locks
WHERE entity_name='CLAUSE'
AND entity_pk1 = To_Char(KART.id)
AND lock_by_document_type=p_target_doc_type
AND lock_by_document_id=p_target_doc_id
)
)
);
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,
mr_variable_html,
mr_variable_xml,
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),
mrvariablehtml_tbl(i),
mrvariablexml_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,
MANDATORY_RWA,
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
AND ( p_retain_lock_terms_yn = 'N'
OR
( p_retain_lock_terms_yn = 'Y'
AND NOT EXISTS ( SELECT 'LOCKEXISTS'
FROM okc_k_entity_locks
WHERE entity_name='CLAUSE'
AND entity_pk1 = To_Char(KART.id)
AND lock_by_document_type=p_target_doc_type
AND lock_by_document_id=p_target_doc_id
)
)
);
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.MANDATORY_RWA,
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,
MANDATORY_RWA,
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;
SELECT Max(DISPLAY_SEQUENCE) FROM okc_k_articles_b
WHERE DOCUMENT_TYPE=P_TARGET_DOC_TYPE
AND DOCUMENT_ID=P_TARGET_DOC_ID;
SELECT tgt.id,tgtsec.id, lck.k_entity_lock_id
FROM okc_k_articles_b tgt, okc_k_entity_locks lck, okc_sections_b tgtsec
WHERE tgt.document_type= p_target_doc_type
AND tgt.document_id= p_target_doc_id
AND tgt.summary_amend_operation_code = 'ADDED'
AND lck.entity_name='DUMMYSEC'
AND lck.lock_by_document_type=p_target_doc_type
AND lck.lock_by_document_id= p_target_doc_id
AND tgt.scn_id = lck.lock_by_entity_id
AND tgtsec.document_type = p_target_doc_type
AND tgtsec.document_id = p_target_doc_id
AND lck.entity_pk1 = tgtsec.ORIG_SYSTEM_REFERENCE_ID1
;
SELECT tgtart.id,tgtsec.id
FROM okc_k_articles_b tgtart
,okc_k_articles_b srcart
,okc_sections_b tgtsec
WHERE 1 = 1
-- Target document clauses
AND tgtart.document_type= p_target_doc_type
AND tgtart.document_id= p_target_doc_id
AND tgtart.summary_amend_operation_code IN ('UPDATED','DELTED')
AND tgtart.orig_system_reference_id1 = srcart.id
AND srcart.document_type=p_source_doc_type
AND srcart.document_id= p_source_doc_id
AND tgtsec.document_type = p_target_doc_type
AND tgtsec.document_id = p_target_doc_id
AND srcart.scn_id=tgtsec.ORIG_SYSTEM_REFERENCE_ID1;
INSERT INTO OKC_K_ARTICLES_B(
ID,
SAV_SAE_ID,
DOCUMENT_TYPE,
DOCUMENT_ID,
CHR_ID,
DNZ_CHR_ID,
SOURCE_FLAG,
MANDATORY_YN,
MANDATORY_RWA,
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),
Mandatory_rwa_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));
UPDATE okc_k_articles_b
SET scn_id= tgt_scn_tbl(i)
WHERE id= tgt_cat_tbl(i);
UPDATE okc_k_entity_locks
SET lock_by_entity_id = tgt_scn_tbl(i)
WHERE k_entity_lock_id=tgt_scn_upd_lock_tbl(i);
UPDATE okc_k_articles_b
SET scn_id= tgt_scn_upd_tbl(i)
WHERE id= tgt_cat_upd_tbl(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
AND (( l_scn_drp_action = 'DROP_AMEND_SEC' AND
heading <> Nvl(fnd_profile.Value('OKC_AMENDMENT_SPECIFIC_SECTION'),'~!@#$%')
) OR
(
l_scn_drp_action = 'DROP_PROV_SEC' AND
OKC_CODE_HOOK.IS_NOT_PROVISIONAL_SECTION(heading) = FND_API.G_TRUE
) OR
l_scn_drp_action = 'DROP_NOTHING')
AND ( p_retain_lock_terms_yn = 'N'
OR
( p_retain_lock_terms_yn = 'Y'
AND NOT EXISTS ( SELECT 'LOCKEXISTS'
FROM okc_k_entity_locks
WHERE entity_name='SECTION'
AND entity_pk1 = To_Char(id)
AND lock_by_document_type=p_target_doc_type
AND lock_by_document_id=p_target_doc_id
)
)
/*OR
( p_retain_lock_terms_yn = 'Y'
AND NOT EXISTS ( SELECT 'LOCKEXISTS'
FROM okc_k_entity_locks lck,okc_sections_b sec
WHERE entity_name='DUMMYSEC'
AND entity_pk1 = To_Char(id)
AND lock_by_document_type=p_target_doc_type
AND lock_by_document_id=p_target_doc_id
AND sec.document_type = p_target_doc_type
AND sec.document_id = p_target_doc_id
)
)*/
);
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)
AND (( l_scn_drp_action = 'DROP_AMEND_SEC' AND
heading <> Nvl(fnd_profile.Value('OKC_AMENDMENT_SPECIFIC_SECTION'),'~!@#$%')
) OR
(
l_scn_drp_action = 'DROP_PROV_SEC' AND
OKC_CODE_HOOK.IS_NOT_PROVISIONAL_SECTION(heading) = FND_API.G_TRUE
) OR
l_scn_drp_action = 'DROP_NOTHING');
SELECT Max(section_sequence) FROM okc_sections_b
WHERE DOCUMENT_TYPE=P_TARGET_DOC_TYPE
AND DOCUMENT_ID=P_TARGET_DOC_ID;
SELECT 'Y'
FROM okc_sections_b
WHERE document_type = p_target_doc_type
AND document_id = p_target_doc_id
AND scn_code = p_scn_code;
SELECT tgtsec.id,tgtsec2.id
FROM okc_sections_b tgtsec
,okc_sections_b srcsec
,okc_sections_b tgtsec2
WHERE 1 = 1
AND tgtsec.document_type= p_target_doc_type
AND tgtsec.document_id= p_target_doc_id
AND tgtsec.summary_amend_operation_code IN ('UPDATED','DELTED')
AND tgtsec.scn_id IS NOT NULL
AND tgtsec.orig_system_reference_id1 = srcsec.id
AND srcsec.document_type= p_source_doc_type
AND srcsec.document_id= p_source_doc_id
AND tgtsec2.document_type=p_target_doc_type
AND tgtsec2.document_id=p_target_doc_id
AND tgtsec2.ORIG_SYSTEM_REFERENCE_ID1=srcsec.scn_id;
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);
UPDATE okc_k_articles_b
SET scn_id= tgt_scn_id_upd_tbl (i)
WHERE id= tgt_id_upd_tbl (i);
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;
SELECT object_version_number
FROM OKC_MLP_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE = p_doc_type AND DOCUMENT_ID = p_doc_id;
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_VAR_EXT_B WHERE cat_id IN ( SELECT kart.ID
FROM OKC_K_ARTICLES_B KART
, OKC_BUS_VARIABLES_B BUS_VAR
, OKC_K_ART_VARIABLES KVAR
WHERE kart.document_type=p_doc_type
AND kart.document_id = p_doc_id
AND kart.SOURCE_FLAG IS NOT NULL
AND KVAR.cat_id=kart.id
AND KVAR.variable_code=BUS_VAR.variable_code
AND BUS_VAR.MRV_FLAG='Y');
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 ');
/*kkolukul: clm changes- Delete entries from multiple templates table*/
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3900: Delete a record from okc_template_usages for the doc');
OKC_CLM_PKG.Delete_Usages_Row(
x_return_status => x_return_status,
p_document_type => p_doc_type,
p_document_id => p_doc_id,
p_object_version_number => l_objnum
);
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 * FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE=p_target_doc_type
AND DOCUMENT_ID=p_target_doc_id;
SELECT 'Y'
FROM cz_config_hdrs
WHERE config_hdr_id = c_config_header_id
AND config_rev_nbr = c_config_rev_nbr;
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 UPPER(FND_PROFILE.VALUE('OKC_USE_CONTRACTS_RULES_ENGINE')) INTO l_cntrcts_ruls_eng_exists FROM DUAL;
INSERT INTO okc_xprt_doc_ques_response(doc_question_response_id,doc_id,doc_type,question_id,response)
(SELECT okc_xprt_doc_ques_response_s.NEXTVAL,p_target_doc_id,p_target_doc_type,question_id,response
FROM okc_xprt_doc_ques_response WHERE doc_id = p_source_doc_id AND doc_type = p_source_doc_type );
OKC_TEMPLATE_USAGES_GRP.update_template_usages(
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_data => x_msg_data,
x_msg_count => x_msg_count,
p_document_type => p_target_doc_type,
p_document_id => p_target_doc_id,
p_template_id => l_usage_rec.template_id,
p_doc_numbering_scheme => l_usage_rec.doc_numbering_scheme,
p_document_number => p_document_number,
p_article_effective_date => p_article_effective_date,
p_config_header_id => l_usage_rec.config_header_id,
p_config_revision_number => l_usage_rec.config_revision_number,
p_valid_config_yn => l_usage_rec.valid_config_yn,
p_approval_abstract_text => l_approval_abstract_text,
p_contract_source_code => l_usage_rec.contract_source_code,
p_authoring_party_code => l_usage_rec.authoring_party_code,
p_source_change_allowed_flag => l_source_change_allowed_flag,
-- Additional fix for bug# 4116433.
p_autogen_deviations_flag => l_usage_rec.autogen_deviations_flag,
p_lock_terms_flag => l_usage_rec.lock_terms_flag,
p_enable_reporting_flag => l_usage_rec.enable_reporting_flag,
p_locked_by_user_id => l_usage_rec.locked_by_user_id,
-- Fix for defaulting Contract Admin
p_contract_admin_id => l_contract_admin_id,
p_legal_contact_id => l_legal_contact_id
);
SELECT decode(p_keep_version,'Y','N','Y') INTO l_get_from_library FROM DUAL;
new API created to insert to this table*/
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'910:Create template usage record in okc_mlp_template_usages');
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
CONTRACT_EXPERT_FINISH_FLAG = 'N',
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;
DELETE FROM okc_xprt_doc_ques_response
WHERE doc_id = p_target_doc_id
AND doc_type = p_target_doc_type;
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;
new API created to insert to this table*/
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'410:Create template usage record in okc_mlp_template_usages');
okc_clm_pkg.insert_usages_row(p_document_type => p_target_doc_type,
p_document_id => p_target_doc_id,
p_template_id => p_source_doc_id,
p_doc_numbering_scheme => l_tmpl_rec.tmpl_numbering_scheme,
p_document_number => p_document_number,
p_article_effective_date => p_article_effective_date,
p_config_header_id => Null,
p_config_revision_number => Null,
p_valid_config_yn => Null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
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,
VAR.MR_VARIABLE_HTML,
VAR.MR_VARIABLE_XML,
BUS_VAR.MRV_FLAG
FROM OKC_K_ART_VARIABLES_H VAR,
OKC_K_ARTICLES_B KART,
OKC_K_ARTICLES_BH KART1,
OKC_BUS_VARIABLES_B BUS_VAR
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,
mr_variable_html,
mr_variable_xml
)
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,
mr_variable_html_tbl(i),
mr_variable_xml_tbl(i));