[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
SELECT OKC_QA_ERRORS_T_S.NEXTVAL FROM DUAL;
FUNCTION insert_row(
p_document_type IN VARCHAR2,
p_document_id IN NUMBER,
p_sequence_id IN NUMBER,
p_error_record_type IN VARCHAR2,
p_title IN VARCHAR2,
p_error_severity IN VARCHAR2,
p_qa_code IN VARCHAR2,
p_message_name IN VARCHAR2,
p_problem_short_desc IN VARCHAR2,
p_problem_details_short IN VARCHAR2,
p_problem_details IN VARCHAR2,
p_Sgestion IN VARCHAR2,
p_article_id IN NUMBER,
p_deliverable_id IN NUMBER,
p_section_name IN VARCHAR2,
p_reference_column1 IN VARCHAR2,
p_reference_column2 IN VARCHAR2,
p_reference_column3 IN VARCHAR2,
p_reference_column4 IN VARCHAR2,
p_reference_column5 IN VARCHAR2,
p_creation_date IN DATE,
p_error_record_type_name IN VARCHAR2,
p_error_severity_name IN VARCHAR2 )RETURN VARCHAR2
IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'700: Entered Insert_Row function');
INSERT INTO OKC_QA_ERRORS_T
(
DOCUMENT_TYPE,
DOCUMENT_ID,
SEQUENCE_ID,
ERROR_RECORD_TYPE,
TITLE,
ERROR_SEVERITY,
QA_CODE,
MESSAGE_NAME,
PROBLEM_SHORT_DESC,
PROBLEM_DETAILS_SHORT,
PROBLEM_DETAILS,
SUGGESTION,
ARTICLE_ID,
DELIVERABLE_ID,
SECTION_NAME,
REFERENCE_COLUMN1,
REFERENCE_COLUMN2,
REFERENCE_COLUMN3,
REFERENCE_COLUMN4,
REFERENCE_COLUMN5,
CREATION_DATE,
ERROR_RECORD_TYPE_NAME,
ERROR_SEVERITY_NAME
)
VALUES
(
p_document_type,
p_document_id,
p_sequence_id,
p_error_record_type,
p_title,
p_error_severity,
p_qa_code,
p_message_name,
p_problem_short_desc,
p_problem_details_short,
p_problem_details,
p_Sgestion,
p_article_id,
p_deliverable_id,
p_section_name,
p_reference_column1,
p_reference_column2,
p_reference_column3,
p_reference_column4,
p_reference_column5,
p_creation_date,
p_error_record_type_name,
p_error_severity_name
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'800: Leaving Insert_Row');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'900: Leaving Insert_Row:OTHERS Exception');
END insert_row;
x_return_status := insert_row(
p_sequence_id => x_sequence_id,
p_document_type => p_qa_result_tbl(i).document_type,
p_document_id => p_qa_result_tbl(i).document_id,
p_error_record_type => p_qa_result_tbl(i).error_record_type,
p_title => p_qa_result_tbl(i).title,
p_error_severity => p_qa_result_tbl(i).error_severity,
p_qa_code => p_qa_result_tbl(i).qa_code,
p_message_name => p_qa_result_tbl(i).message_name,
p_problem_short_desc => p_qa_result_tbl(i).problem_short_desc,
p_problem_details_short => p_qa_result_tbl(i).problem_details_short,
p_problem_details => p_qa_result_tbl(i).problem_details,
p_Sgestion => p_qa_result_tbl(i).suggestion,
p_article_id => p_qa_result_tbl(i).article_id,
p_deliverable_id => p_qa_result_tbl(i).deliverable_id,
p_section_name => p_qa_result_tbl(i).section_name,
p_reference_column1 => p_qa_result_tbl(i).reference_column1,
p_reference_column2 => p_qa_result_tbl(i).reference_column2,
p_reference_column3 => p_qa_result_tbl(i).reference_column3,
p_reference_column4 => p_qa_result_tbl(i).reference_column4,
p_reference_column5 => p_qa_result_tbl(i).reference_column5,
p_creation_date => p_qa_result_tbl(i).creation_date,
p_error_record_type_name => p_qa_result_tbl(i).error_record_type_name,
p_error_severity_name => p_qa_result_tbl(i).error_severity_name);
SELECT kart1.id source_cat_id,
rel.SOURCE_ARTICLE_ID source_article_id,
kart1.SCN_ID scn_id,
kart1.label source_label,
Kart2.ID target_cat_id,
rel.TARGET_ARTICLE_ID target_article_id,
kart2.label target_label,
kart1.AMENDMENT_OPERATION_CODE amendment_operation_code,
rel.RELATIONSHIP_TYPE relationship_type
FROM OKC_K_ARTICLES_B kart1,
OKC_ARTICLE_RELATNS_ALL rel,
OKC_K_ARTICLES_B kart2
WHERE kart1.document_type = p_doc_type
AND kart1.document_id=p_doc_id
AND kart1.sav_sae_id=rel.source_article_id
AND kart2.document_type = p_doc_type
AND kart2.document_id=p_doc_id
AND kart2.sav_sae_id=rel.target_article_id
AND rel.org_id = c_current_org_id
AND rel.relationship_type in (G_INCOMPATIBLE ,G_ALTERNATE)
AND nvl(kart2.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
AND nvl(kart2.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
AND nvl(kart1.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
AND nvl(kart1.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
SELECT
Rule.clause_id source_article_id,
Rule.rule_id rule_id,
Rule.rule_name rule_name,
Kart.sav_sae_id target_article_id,
Rel.relationship_type relationship_type
FROM OKC_XPRT_CLAUSES_V rule,
OKC_K_ARTICLES_B kart,
OKC_ARTICLE_RELATNS_ALL rel
WHERE rule.template_id = p_doc_id
AND kart.document_type = p_doc_type
AND kart.document_id = p_doc_id
AND rule.rule_id =rel.source_article_id
AND kart.sav_sae_id = rel.target_article_id
AND rel.org_id = c_current_org_id
AND rel.relationship_type in (G_INCOMPATIBLE ,G_ALTERNATE) ;
SELECT
decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa,
fnd.meaning qa_name,
nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag
FROM FND_LOOKUPS FND, OKC_DOC_QA_LISTS QA
WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
AND QA.QA_CODE(+) = FND.LOOKUP_CODE
AND Fnd.LOOKUP_TYPE=G_QA_LOOKUP
AND fnd.lookup_code = p_qa_code;
SELECT nvl(qa.severity_flag,G_QA_STS_ERROR) severity_flag
FROM OKC_DOC_QA_LISTS QA
WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
AND QA.QA_CODE(+) = G_OKC_CHECK_LOCK_CONTRACT;
SELECT
decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa,
fnd.meaning qa_name,
nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag
FROM FND_LOOKUPS FND, OKC_DOC_QA_LISTS QA
WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
AND QA.QA_CODE(+) = FND.LOOKUP_CODE
AND Fnd.LOOKUP_TYPE=G_QA_LOOKUP
AND fnd.lookup_code = p_qa_code;
SELECT nvl(qa.severity_flag,G_QA_STS_ERROR) severity_flag
FROM OKC_DOC_QA_LISTS QA
WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
AND QA.QA_CODE(+) = G_OKC_CHECK_CONTRACT_ADMIN;
SELECT 'Y' FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE = P_DOC_TYPE
AND DOCUMENT_ID = P_DOC_ID
AND CONTRACT_ADMIN_ID IS NOT NULL;
SELECT 'Y' FROM okc_rep_contracts_all
WHERE CONTRACT_TYPE = P_DOC_TYPE
AND CONTRACT_ID = P_DOC_ID
AND OWNER_ID IS NOT NULL;
SELECT 'Y' FROM OKC_TEMPLATE_USAGES USG, fnd_user ctrtadm, PER_ALL_PEOPLE_F adminppl
WHERE USG.DOCUMENT_TYPE = P_DOC_TYPE
AND USG.DOCUMENT_ID = P_DOC_ID
AND USG.CONTRACT_ADMIN_ID IS NOT NULL
AND USG.contract_admin_id = ctrtadm.user_id
AND ctrtadm.employee_id = adminppl.person_id
and rownum < 2;
SELECT 'Y' FROM OKC_TEMPLATE_USAGES USG, fnd_user ctrtadm, PER_ALL_PEOPLE_F adminppl
WHERE USG.DOCUMENT_TYPE = P_DOC_TYPE
AND USG.DOCUMENT_ID = P_DOC_ID
AND USG.CONTRACT_ADMIN_ID IS NOT NULL
AND USG.contract_admin_id = ctrtadm.user_id
AND ctrtadm.employee_id = adminppl.person_id
and adminppl.effective_start_date = adminppl.start_date;
SELECT
Rule.clause_id xprt_article_id,
Rule.rule_id rule_id,
Rule.rule_name rule_name
FROM OKC_XPRT_CLAUSES_V rule,
OKC_K_ARTICLES_B kart
WHERE rule.template_id = p_doc_id
AND kart.document_type = p_doc_type
AND kart.document_id = p_doc_id
AND rule.clause_id =kart.sav_sae_id;
and nvl(l_article_tbl(i).amendment_operation_code,'?')<>G_AMEND_CODE_DELETED THEN
--Bug 4128923 l_error_count := 0;
IF nvl(l_article_tbl(k).amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
AND Nvl(l_article_tbl(k).std_art_id,l_article_tbl(k).article_id)
=Nvl(l_article_tbl(i).std_art_id,l_article_tbl(i).article_id) THEN
IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1600: Dupicate found - i:'||i||', k:'||k);
SELECT kart.id id,
kart.sav_sae_id article_id,
kart.article_version_id article_version_id,
kart.amendment_operation_code amendment_operation_code,
kart.scn_id scn_id,
var.variable_code variable_code,
busvar.variable_name variable_name,
busdoc.name doc_type
FROM okc_k_articles_b kart,
okc_k_art_variables var,
okc_bus_variables_vl busvar,
okc_bus_doc_types_v busdoc
WHERE kart.document_type=p_doc_type
and kart.document_id=p_doc_id
and var.cat_id=kart.id
and var.variable_type IN ('S','D')
and var.variable_code=busvar.variable_code
and busdoc.document_type = kart.document_type
and not exists (SELECT 'x' from OKC_VARIABLE_DOC_TYPES vo
WHERE var.variable_code=vo.variable_code
and doc_type=p_doc_type)
and p_doc_type<>G_TMPL_DOC_TYPE
and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
UNION ALL
SELECT kart.id id,
kart.sav_sae_id article_id,
vers.article_version_id article_version_id,
kart.amendment_operation_code amendment_operation_code,
kart.scn_id scn_id,
var.variable_code variable_code,
busvar.variable_name variable_name,
busdoc.name doc_type
FROM okc_k_articles_b kart,
okc_article_versions vers,
okc_allowed_tmpl_usages allwd ,
okc_article_variables var,
okc_bus_variables_vl busvar ,
okc_bus_doc_types_v busdoc
WHERE kart.document_type=G_TMPL_DOC_TYPE
and kart.document_id=p_doc_id
and allwd.template_id=kart.document_id
and kart.sav_sae_id=vers.article_id
and vers.article_status='APPROVED'
AND vers.start_date = (SELECT max(start_date)
FROM OKC_ARTICLE_VERSIONS
WHERE article_id= kart.sav_sae_id
AND article_status='APPROVED')
and vers.article_version_id=var.article_version_id
and busvar.variable_code=var.variable_code
and busvar.variable_type IN ('S','D')
and busdoc.document_type = allwd.document_type
and not exists (SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
WHERE var.variable_code=vo.variable_code
and doc_type=allwd.document_type)
and p_doc_type=G_TMPL_DOC_TYPE
and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
2. for TEMPLATE doc types, get selected draft versions also
*/
--Repository Enhancement 12.1 (For Validate Action)
p_rep_doc_type varchar2(30);
SELECT kart.id id,
kart.sav_sae_id article_id,
kart.article_version_id article_version_id,
kart.amendment_operation_code amendment_operation_code,
kart.scn_id scn_id,
var.variable_code variable_code,
busvar.variable_name variable_name,
busdoc.name doc_type
FROM okc_k_articles_b kart,
okc_k_art_variables var,
okc_bus_variables_vl busvar,
okc_bus_doc_types_v busdoc
WHERE kart.document_type=p_doc_type
and kart.document_id=p_doc_id
and var.cat_id=kart.id
and var.variable_type IN ('S','D')
and var.variable_code=busvar.variable_code
--Repository Enhancement 12.1 (For Validate Action)
and busdoc.document_type = kart.document_type
-- and busdoc.document_type = p_rep_doc_type
and not exists (SELECT 'x' from OKC_VARIABLE_DOC_TYPES vo
WHERE var.variable_code=vo.variable_code
--Repository Enhancement 12.1 (For Validate Action)
-- and doc_type=p_doc_type)
and doc_type= p_rep_doc_type)
and p_doc_type<>G_TMPL_DOC_TYPE
and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
UNION ALL
-- change to get draft/rejected clause versions also
SELECT kart.id id,
kart.sav_sae_id article_id,
vers.article_version_id article_version_id,
kart.amendment_operation_code amendment_operation_code,
kart.scn_id scn_id,
var.variable_code variable_code,
busvar.variable_name variable_name,
busdoc.name doc_type
FROM okc_k_articles_b kart,
okc_article_versions vers,
okc_allowed_tmpl_usages allwd ,
okc_article_variables var,
okc_bus_variables_vl busvar ,
okc_bus_doc_types_v busdoc
WHERE kart.document_type=G_TMPL_DOC_TYPE
and kart.document_id=p_doc_id
and allwd.template_id=kart.document_id
and kart.sav_sae_id=vers.article_id
-- new logic for determining clause version
and vers.article_version_id = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
kart.sav_sae_id,
g_start_date,
g_end_date,
g_status_code,
p_doc_type,
p_doc_id)
/* existing logic of determining clause version
and vers.article_status='APPROVED'
AND vers.start_date = (select max(start_date)
FROM OKC_ARTICLE_VERSIONS
WHERE article_id= kart.sav_sae_id
AND article_status='APPROVED')
*/
and vers.article_version_id=var.article_version_id
and busvar.variable_code=var.variable_code
and busvar.variable_type IN ('S','D')
and busdoc.document_type = allwd.document_type
and not exists ((SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
WHERE var.variable_code=vo.variable_code
and doc_type=allwd.document_type)
UNION ALL
(SELECT 'x' FROM OKC_VARIABLE_DOC_TYPES vo
WHERE var.variable_code=vo.variable_code
and doc_type like '%REPOSITORY%'))
and p_doc_type=G_TMPL_DOC_TYPE
and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
SELECT intent
FROM okc_bus_doc_types_b
WHERE document_type = p_doc_type;
SELECT
Rule.clause_id xprt_article_id,
Rule.rule_id rule_id,
Rule.rule_name rule_name,
var.variable_code variable_code,
busvar.variable_name variable_name,
busdoc.name doc_type
FROM okc_xprt_clauses_v rule,
okc_allowed_tmpl_usages allwd ,
okc_article_versions vers,
okc_article_variables var,
okc_bus_variables_vl busvar ,
okc_bus_doc_types_v busdoc
WHERE rule.template_id = p_doc_id
and allwd.template_id=p_doc_id
and vers.article_id = rule.clause_id
and vers.article_version_id =
OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
rule.clause_id,
g_start_date,
g_end_date,
g_status_code,
p_doc_type,
p_doc_id)
and var.article_version_id = vers.article_version_id
and busvar.variable_code = var.variable_code
and busvar.variable_type IN ('S','D')
and busdoc.document_type = allwd.document_type
and not exists (select 'x' from OKC_VARIABLE_DOC_TYPES vo
where vo.variable_code=var.variable_code
and vo.doc_type=allwd.document_type) ;
SELECT VB.variable_code,
KA.id,
KA.sav_sae_id article_id,
KA.scn_id,
VT.variable_name
FROM okc_k_articles_b KA,
okc_k_art_variables KV,
okc_bus_variables_b VB,
okc_bus_variables_tl VT
WHERE VB.variable_code = KV.variable_code
AND KA.id = KV.cat_id
AND VB.variable_code = VT.variable_code
AND VB.variable_source = 'P'
AND KA.document_type = p_doc_type
AND KA.document_id = p_doc_id
AND language = USERENV('LANG')
ORDER BY VB.variable_code;
SELECT kart.id id,
kart.sav_sae_id article_id,
kart.amendment_operation_code amendment_operation_code,
kart.scn_id scn_id,
var.variable_code variable_code,
busvar.variable_name variable_name,
var.variable_value variable_value,
var.variable_type,
var.external_yn,
var.variable_value_id,
busvar.mrv_flag,
var.mr_variable_html,
var.mr_variable_xml
FROM okc_k_articles_b kart,
okc_k_art_variables var,
okc_bus_variables_vl busvar
WHERE kart.document_type=p_doc_type
and kart.document_id=p_doc_id
and var.cat_id=kart.id
and busvar.variable_code=var.variable_code
and nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
and nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
and ( (var.variable_type = 'U' AND busvar.variable_source = 'M') OR exists (( SELECT 'x' FROM okc_variable_doc_types vo
WHERE vo.variable_code = var.variable_code
AND vo.doc_type = p_doc_type
)UNION all ( SELECT 'x' FROM okc_variable_doc_types vo
WHERE vo.variable_code = var.variable_code
AND vo.doc_type LIKE '%REPOSITORY%'))) ;
SELECT
kart.id id,
kart.sav_sae_id article_id,
kart.scn_id scn_id
FROM OKC_K_ARTICLES_B KART,
OKC_SECTIONS_B SCN
WHERE kart.document_type=p_doc_type
AND kart.document_id =p_doc_id
AND scn.id = kart.scn_id
AND scn.scn_code = G_UNASSIGNED_SECTION_CODE
AND nvl(scn.amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
AND nvl(kart.amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
AND nvl(kart.summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED;
SELECT
id id,
scn_id scn_id
FROM OKC_SECTIONS_B SCN
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 not exists ( SELECT 'x' FROM OKC_K_ARTICLES_B WHERE scn_id=scn.id
AND nvl(amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
AND nvl(summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED)
AND not exists ( SELECT 'x' FROM OKC_SECTIONS_B SCN1 WHERE SCN1.scn_id = scn.id
AND nvl(amendment_operation_code,'?') <> G_AMEND_CODE_DELETED
AND nvl(summary_amend_operation_code,'?') <> G_AMEND_CODE_DELETED);
SELECT NVL(disable_amend_yn,'N')
FROM OKC_BUS_DOC_TYPES_B
WHERE document_type = p_doc_type;
SELECT NVL(disable_amend_yn,'N')
FROM OKC_BUS_DOC_TYPES_B
WHERE document_type = p_doc_type;
SELECT status_code,end_date,template_name
FROM
OKC_TERMS_TEMPLATES_ALL TMPL,
OKC_TEMPLATE_USAGES USG
WHERE USG.DOCUMENT_TYPE = p_doc_type
AND USG.DOCUMENT_ID = p_doc_id
AND TMPL.TEMPLATE_ID = USG.TEMPLATE_ID;
SELECT 'Y'
FROM OKC_ALLOWED_TMPL_USAGES
WHERE TEMPLATE_ID = (SELECT TEMPLATE_ID
FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_ID = p_doc_id
AND DOCUMENT_TYPE = p_doc_type)
AND DOCUMENT_TYPE = p_doc_type;
SELECT name
FROM okc_bus_doc_types_tl
WHERE document_type = p_doc_type
AND LANGUAGE = userenv('LANG');
SELECT START_DATE,END_DATE
FROM OKC_TERMS_TEMPLATES_ALL
WHERE TEMPLATE_ID=p_doc_id;
SELECT kart.id,
kart.sav_sae_id article_id,
kart.scn_id scn_id,
kart.amendment_operation_code amendment_operation_code,
kart.amendment_description amendment_description
FROM OKC_K_ARTICLES_B KART
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID =p_doc_id
AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
WHERE VERS.ARTICLE_ID=KART.SAV_SAE_ID
AND VERS.ARTICLE_STATUS='APPROVED'
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)
);
SELECT kart.id,
kart.sav_sae_id article_id,
kart.scn_id scn_id,
kart.amendment_operation_code amendment_operation_code,
kart.amendment_description amendment_description
FROM OKC_K_ARTICLES_B KART,
okc_terms_templates_all tmpl,
okc_articles_all art
WHERE kart.document_id = tmpl.template_id
AND kart.sav_sae_id = art.article_id
AND DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID =p_doc_id
AND
(
( art.org_id <> tmpl.org_id
AND NOT EXISTS ( SELECT 'X'
FROM OKC_ARTICLE_ADOPTIONS ADP,
OKC_ARTICLE_VERSIONS VER
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
AND VER.article_id = KART.SAV_SAE_ID
AND ADP.LOCAL_ORG_ID = tmpl.org_id
AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
AND ADP.ADOPTION_TYPE = 'ADOPTED'
AND VER.ARTICLE_STATUS='APPROVED'
AND nvl(p_article_effective_date,sysdate) >= VER.START_DATE
AND nvl(p_article_effective_date,sysdate)
<= nvl(VER.end_date, nvl(p_article_effective_date,sysdate) +1)
)
) OR
( art.org_id = tmpl.org_id
AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
WHERE VERS.ARTICLE_ID=KART.SAV_SAE_ID
-- modified to include DRAFT and REJECTED statuses also
--AND VERS.ARTICLE_STATUS='APPROVED'
AND VERS.ARTICLE_STATUS in ('APPROVED', 'DRAFT' , 'REJECTED')
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)
)
)
);
SELECT
Rule.clause_id xprt_article_id,
Rule.rule_id rule_id,
Rule.rule_name rule_name
FROM okc_xprt_clauses_v rule,
okc_terms_templates_all tmpl,
okc_articles_all art
WHERE rule.template_id = tmpl.template_id
AND tmpl.template_id = p_doc_id
AND art.article_id = rule.clause_id
AND
(
( art.org_id <> tmpl.org_id
AND NOT EXISTS ( SELECT 'X'
FROM OKC_ARTICLE_ADOPTIONS ADP,
OKC_ARTICLE_VERSIONS VER
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
AND VER.article_id = rule.clause_id
AND ADP.LOCAL_ORG_ID = tmpl.org_id
AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
AND ADP.ADOPTION_TYPE = 'ADOPTED'
AND VER.ARTICLE_STATUS='APPROVED'
AND nvl(p_article_effective_date,sysdate) >= VER.START_DATE
AND nvl(p_article_effective_date,sysdate) <= nvl(VER.end_date,
nvl(p_article_effective_date,sysdate) +1)
)
)
OR
( art.org_id = tmpl.org_id
AND NOT EXISTS ( SELECT 'X' FROM OKC_ARTICLE_VERSIONS VERS
WHERE VERS.ARTICLE_ID=rule.clause_id
AND VERS.ARTICLE_STATUS in ('APPROVED', 'DRAFT' , 'REJECTED')
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)
)
)
);
SELECT kart.id,
kart.sav_sae_id article_id,
kart.article_version_id article_version_id,
kart.scn_id scn_id,
vers.start_date start_date,
kart.amendment_operation_code amendment_operation_code,
kart.amendment_description amendment_description
FROM OKC_K_ARTICLES_B KART,
OKC_ARTICLE_VERSIONS VERS,
OKC_ARTICLES_ALL ART
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID =p_doc_id
AND VERS.ARTICLE_VERSION_ID=KART.ARTICLE_VERSION_ID
AND ART.ARTICLE_ID = KART.SAV_SAE_ID
AND ART.STANDARD_YN='Y'
AND nvl(AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND ( ARTICLE_STATUS<>'APPROVED'
OR ( ARTICLE_STATUS='APPROVED' AND
nvl(END_DATE,b_effective_date+1)< b_effective_date
)
);
SELECT distinct KART.ID ID,
KART.SAV_SAE_ID ARTICLE_ID,
KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
KART.SCN_ID SCN_ID,
KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION
FROM OKC_K_ARTICLES_B KART,
OKC_ARTICLE_VERSIONS VERS,
OKC_ARTICLE_VERSIONS VERS1,
OKC_TEMPLATE_USAGES USG,
OKC_TERMS_TEMPLATES_ALL TMPL
WHERE KART.DOCUMENT_TYPE=p_doc_type
AND KART.DOCUMENT_ID =p_doc_id
AND KART.DOCUMENT_TYPE= USG.DOCUMENT_TYPE
AND KART.DOCUMENT_ID = USG.DOCUMENT_ID
AND USG.TEMPLATE_ID = TMPL.TEMPLATE_ID
AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>G_AMEND_CODE_DELETED
AND KART.ARTICLE_VERSION_ID = VERS1.ARTICLE_VERSION_ID
AND VERS.START_DATE > VERS1.START_DATE
AND trunc(NVL(USG.ARTICLE_EFFECTIVE_DATE,SYSDATE)) BETWEEN trunc(VERS.START_DATE) AND NVL(VERS.END_DATE,SYSDATE)
AND VERS.ARTICLE_STATUS = 'APPROVED'
AND (EXISTS
(SELECT 1
FROM OKC_ARTICLE_ADOPTIONS ADP
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
AND ADP.ADOPTION_TYPE = 'ADOPTED'
AND ADP.ADOPTION_STATUS = 'APPROVED'
AND ADP.LOCAL_ORG_ID = TMPL.ORG_ID)
OR
NOT EXISTS
(SELECT 1
FROM OKC_ARTICLE_ADOPTIONS ADP
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS1.ARTICLE_VERSION_ID
AND ADP.ADOPTION_TYPE = 'ADOPTED'
AND ADP.LOCAL_ORG_ID = TMPL.ORG_ID)
) ;
SELECT template_name, print_template_id
FROM okc_terms_templates_all
WHERE template_id = p_doc_id ;
SELECT parent.template_id, parent.template_name, trans.template_name
FROM okc_terms_templates_all parent, okc_terms_templates_all trans
WHERE parent.template_id = trans.translated_from_tmpl_id
AND trans.template_id = p_doc_id
AND exists (SELECT 1
FROM okc_terms_templates_all revision
WHERE parent.template_id = revision.parent_template_id);
SELECT parent.template_id, parent.template_name, trans.template_name
FROM okc_terms_templates_all parent, okc_terms_templates_all trans
WHERE ( trunc(sysdate) >= nvl(trunc(parent.end_date),sysdate+1)
OR parent.status_code = 'ON_HOLD' )
AND parent.template_id = trans.translated_from_tmpl_id
AND trans.template_id = p_doc_id;
SELECT DRA.article_id, DRA.article_version_id,
--NVL(VER.display_name, ART.article_title) title, DRA.section_name section
NVL(VER.display_name, ART.article_title) title,NVL(DRA.section_name, '*') section
FROM OKC_TMPL_DRAFT_CLAUSES DRA,
OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
WHERE DRA.template_id = p_doc_id and
DRA.selected_yn = 'Y' and
DRA.article_id = ART.article_id and
VER.article_version_id = DRA.article_version_id
-- additional check to ensure that we are not checking some
-- orphaned records in the OKC_TMPL_DRAFT_CLAUSES table
AND EXISTS (SELECT '1' FROM OKC_K_ARTICLES_B KART WHERE
KART.document_type = p_doc_type AND
KART.document_id = p_doc_id AND
KART.sav_sae_id = DRA.article_id);
SELECT DRA.article_id article_id, DRA.article_version_id,
--NVL(VER.display_name, ART.article_title) title, DRA.section_name section
NVL(VER.display_name, ART.article_title) title,NVL(DRA.section_name, '*') section
FROM OKC_TMPL_DRAFT_CLAUSES DRA,
OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
WHERE DRA.template_id = p_doc_id and
DRA.selected_yn = 'Y' and
DRA.article_id = ART.article_id and
VER.article_version_id = DRA.article_version_id
-- additional check to ensure that we are not checking some
-- orphaned records in the OKC_TMPL_DRAFT_CLAUSES table
AND EXISTS (SELECT '1' FROM OKC_K_ARTICLES_B KART WHERE
KART.document_type = p_doc_type AND
KART.document_id = p_doc_id AND
KART.sav_sae_id = DRA.article_id)
AND VER.article_status='REJECTED';
SELECT fnd.lookup_code qa_code,
fnd.meaning qa_name,
nvl(qa.severity_flag,G_QA_STS_WARNING) severity_flag ,
decode(fnd.enabled_flag,'N','N','Y',decode(qa.enable_qa_yn,'N','N','Y'),'Y') perform_qa
FROM FND_LOOKUPS FND, OKC_DOC_QA_LISTS QA
WHERE QA.DOCUMENT_TYPE(+)=p_doc_type
AND QA.QA_CODE(+) = FND.LOOKUP_CODE
AND Fnd.LOOKUP_TYPE=G_QA_LOOKUP;
SELECT KART.ID ID,
KART.SAV_SAE_ID ARTICLE_ID,
KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
KART.SCN_ID SCN_ID,
OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,KART.ARTICLE_VERSION_ID) TITLE,
Decode(ART.standard_yn,'N',KART.ref_article_id,NULL) STD_ART_ID
FROM OKC_K_ARTICLES_B KART,
OKC_ARTICLES_ALL ART,
OKC_ARTICLE_VERSIONS VERS
WHERE KART.DOCUMENT_TYPE = p_doc_type
AND KART.DOCUMENT_ID = p_doc_id
AND KART.ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
AND KART.SAV_SAE_ID = ART.ARTICLE_ID;
SELECT KART.ID ID,
KART.SAV_SAE_ID ARTICLE_ID,
STD.ARTICLE_ID STD_ART_ID,
KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
KART.SCN_ID SCN_ID,
OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,VERS.ARTICLE_VERSION_ID) TITLE
FROM OKC_K_ARTICLES_B KART, OKC_ARTICLE_VERSIONS VERS, OKC_ARTICLE_VERSIONS STD
WHERE KART.DOCUMENT_TYPE = p_doc_type
AND KART.DOCUMENT_ID = p_doc_id
AND STD.ARTICLE_VERSION_ID(+) = VERS.STD_ARTICLE_VERSION_ID
AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
AND nvl(VERS.START_DATE,sysdate) = ( SELECT nvl(MAX(START_DATE),sysdate)
FROM OKC_ARTICLE_VERSIONS
WHERE ARTICLE_ID=VERS.ARTICLE_ID);
SELECT KART.ID ID,
KART.SAV_SAE_ID ARTICLE_ID,
STD.ARTICLE_ID STD_ART_ID,
KART.ARTICLE_VERSION_ID ARTICLE_VERSION_ID,
KART.AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
KART.AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
KART.SCN_ID SCN_ID,
OKC_TERMS_UTIL_PVT.get_article_name(KART.SAV_SAE_ID ,VERS.ARTICLE_VERSION_ID) TITLE
FROM OKC_K_ARTICLES_B KART, OKC_ARTICLE_VERSIONS VERS, OKC_ARTICLE_VERSIONS STD
WHERE KART.DOCUMENT_TYPE = p_doc_type
AND KART.DOCUMENT_ID = p_doc_id
AND STD.ARTICLE_VERSION_ID(+) = VERS.STD_ARTICLE_VERSION_ID
AND KART.SAV_SAE_ID = VERS.ARTICLE_ID
AND VERS.ARTICLE_VERSION_ID = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
KART.sav_sae_id,
g_start_date,
g_end_date,
g_status_code,
p_doc_type,
p_doc_id
);
SELECT XPRT.clause_id article_id,
ver.article_version_id article_version_id,
Nvl(ver.display_name, art.article_title) title,
xprt.rule_id rule_id,
xprt.rule_name rule_name
FROM OKC_XPRT_CLAUSES_V XPRT,
OKC_ARTICLES_ALL ART, OKC_ARTICLE_VERSIONS VER
WHERE XPRT.template_id = p_doc_id
AND ART.article_id = XPRT.clause_id
AND VER.article_id = ART.article_id
AND VER.article_version_id = OKC_TERMS_UTIL_PVT.get_latest_tmpl_art_version_id(
ART.article_id,
g_start_date,
g_end_date,
g_status_code,
p_doc_type,
p_doc_id
);
SELECT ID ID,
AMENDMENT_OPERATION_CODE AMENDMENT_OPERATION_CODE,
AMENDMENT_DESCRIPTION AMENDMENT_DESCRIPTION,
SCN_ID SCN_ID,
SCN_CODE SCN_CODE,
DECODE(LABEL,NULL,HEADING,
okc_terms_util_pvt.get_message('OKC',
'OKC_TERMS_LABEL_AND_NAME',
'LABEL', LABEL,
'NAME', HEADING)) HEADING
FROM OKC_SECTIONS_B
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id;
SELECT nvl(ARTICLE_EFFECTIVE_DATE ,sysdate)
FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id;
SELECT start_date, end_date
FROM okc_terms_templates_all
WHERE template_id = p_doc_id;
SELECT start_date, end_date,
nvl(contract_expert_enabled, 'N'), nvl(status_code, 'DRAFT'),
template_name,org_id
FROM okc_terms_templates_all
WHERE template_id = p_doc_id;
x_qa_result_tbl.DELETE;
l_article_tbl.DELETE;
l_section_tbl.DELETE;
l_qa_detail_tbl.DELETE;
l_xprt_article_tbl.DELETE;