[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_DELETED;
G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
PROCEDURE ALLOWED_TMPL_USAGES_Delete_Set(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_template_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'ALLOWED_TMPL_USAGES_Delete_Set';
SELECT allowed_tmpl_usages_id,object_version_number
FROM OKC_ALLOWED_TMPL_USAGES
WHERE TEMPLATE_ID = p_template_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered ALLOWED_TMPL_USAGES_Delete_Set');
OKC_ALLOWED_TMPL_USAGES_GRP.Delete_Allowed_Tmpl_Usages(
p_api_version => 1,
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_allowed_tmpl_usages_id => cr.allowed_tmpl_usages_id,
p_object_version_number => cr.object_version_number
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'400: Leaving ALLOWED_TMPL_USAGES_Delete_Set');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving ALLOWED_TMPL_USAGES_Delete_Set:FND_API.G_EXC_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving ALLOWED_TMPL_USAGES_Delete_Set:FND_API.G_EXC_UNEXPECTED_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'600: Leaving ALLOWED_TMPL_USAGES_Delete_Set because of EXCEPTION: '||sqlerrm);
END ALLOWED_TMPL_USAGES_Delete_Set;
PROCEDURE Update_Allowed_Tmpl_Usages_Id(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_old_template_id IN NUMBER,
p_new_template_id IN NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Allowed_Tmpl_Usages_Id';
SELECT allowed_tmpl_usages_id,object_version_number,document_type,default_yn
FROM OKC_ALLOWED_TMPL_USAGES
WHERE TEMPLATE_ID = p_old_template_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Entered Update_Allowed_Tmpl_Usages_Id');
OKC_ALLOWED_TMPL_USAGES_GRP.update_Allowed_Tmpl_Usages(
p_api_version => 1,
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_template_id => p_new_template_id,
p_document_type => cr.document_type,
p_default_yn => cr.default_yn,
p_allowed_tmpl_usages_id => cr.allowed_tmpl_usages_id,
p_object_version_number => cr.object_version_number
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1400: Update_Allowed_Tmpl_Usages_Id');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Update_Allowed_Tmpl_Usages_Id :FND_API.G_EXC_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'500: Leaving Update_Allowed_Tmpl_Usages_Id : FND_API.G_EXC_UNEXPECTED_ERROR');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1600: Leaving Update_Allowed_Tmpl_Usages_Id because of EXCEPTION: '||sqlerrm);
END Update_Allowed_Tmpl_Usages_Id;
PROCEDURE Delete_Doc (
x_return_status OUT NOCOPY VARCHAR2,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc';
SELECT STATUS_CODE FROM okc_terms_templates
WHERE TEMPLATE_ID = p_doc_id;
SELECT '!' FROM okc_terms_templates
WHERE parent_template_id = p_doc_id;
SELECT '!' FROM okc_template_usages_v
WHERE TEMPLATE_ID = p_doc_id AND ROWNUM=1;
SELECT TEMPLATE_ID, object_version_number,template_model_id
FROM okc_terms_templates
WHERE template_id=p_doc_id;
SELECT object_version_number
FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE = p_doc_type AND DOCUMENT_ID = p_doc_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: Entered Delete_Doc');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3100: Template Status is not valid to delete it');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3200: Template is already used - so can not be deleted');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3300: Delete each record from OKC_ALLOWED_TMPL_USAGES_V in a loop');
ALLOWED_TMPL_USAGES_Delete_Set(
x_return_status => x_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
p_template_id => p_doc_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3400: Delete each record from okc_terms_templates in a loop');
OKC_TERMS_TEMPLATES_PVT.Delete_Row(
x_return_status => x_return_status,
p_template_id => cr.template_id,
p_object_version_number => cr.object_version_number
);
OKC_XPRT_TMPL_RULE_ASSNS_PVT.delete_template_rule_assns(
p_api_version => 1,
p_init_msg_list => OKC_API.G_FALSE,
p_commit => OKC_API.G_FALSE,
p_template_id => cr.template_id,
x_return_status => x_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3500: Delete records from okc_k_art_varaibles for the doc');
OKC_K_ART_VARIABLES_PVT.delete_set(
x_return_status => x_return_status,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Delete records from okc_k_articles_v for the doc');
OKC_K_ARTICLES_PVT.delete_set(
x_return_status => x_return_status,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Delete records from okc_sections_v for the doc');
OKC_TERMS_SECTIONS_PVT.delete_set(
x_return_status => x_return_status,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id
);
OKC_REVIEW_UPLD_TERMS_PVT.delete_uploaded_terms(
p_api_version => l_api_version,
p_document_type => p_doc_type,
p_document_id => p_doc_id,
x_return_status => x_return_status,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3800: Delete a record from okc_template_usages for the doc');
OKC_TEMPLATE_USAGES_PVT.delete_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
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4000: Leaving Delete_Doc');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4100: Leaving Delete_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4200: Leaving Delete_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4300: Leaving Delete_Doc because of EXCEPTION: '||sqlerrm);
END Delete_Doc ;
PROCEDURE delete_doc_version (
x_return_status OUT NOCOPY VARCHAR2,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_version_number IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'delete_doc_version';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2900: Entered delete_doc_version');
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3500: Delete records from okc_k_art_varaibles_h for the doc');
x_return_status:=OKC_K_ART_VARIABLES_PVT.delete_version(
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_major_version => p_version_number
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3600: Delete records from okc_k_articles_bh for the doc');
x_return_status:=OKC_K_ARTICLES_PVT.delete_version(
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_major_version => p_version_number
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3700: Delete records from okc_sections_h for the doc');
x_return_status:=OKC_TERMS_SECTIONS_PVT.delete_version(
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_major_version => p_version_number
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'3800: Delete a record from okc_template_usages_h for the doc');
x_return_status:=OKC_TEMPLATE_USAGES_PVT.delete_version(
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_major_version => p_version_number
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4000: Leaving delete_doc_version');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4100: Leaving delete_doc_version : OKC_API.G_EXCEPTION_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4200: Leaving delete_doc_version : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'4300: Leaving delete_doc_version because of EXCEPTION: '||sqlerrm);
END Delete_Doc_version ;
SELECT distinct id, kart.object_version_number
FROM okc_k_articles_b kart, okc_k_art_variables var
WHERE document_type = p_doc_type
AND document_id = p_doc_id
AND var.cat_id = kart.id
AND kart.amendment_operation_code IS NULL
and var.variable_code = p_variable_code;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'4500: Update each record from okc_K_ARTICLES in a loop');
OKC_K_ARTICLES_GRP.Update_article(
p_api_version => 1,
p_init_msg_list => FND_API.G_FALSE ,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_mode => 'AMEND',
p_id => cr.id,
p_object_version_number => cr.object_version_number
);
2. Update the table OKC_TMPL_DRAFT_CLAUSES with the merged/parent template id.
*/
PROCEDURE Merge_Template_Working_Copy (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_template_id IN NUMBER
) IS
l_api_version CONSTANT NUMBER := 1;
SELECT parent_template_id
FROM okc_terms_templates_all
WHERE template_id=p_template_id;
SELECT template_id, document_type
FROM OKC_ALLOWED_TMPL_USAGES
WHERE TEMPLATE_ID = l_base_template_id;
SELECT id, object_version_number
FROM okc_k_articles_b
WHERE document_type=G_TMPL_DOC_TYPE
AND document_id = p_template_id;
SELECT id, object_version_number
FROM okc_sections_b
WHERE document_type=G_TMPL_DOC_TYPE
AND document_id = p_template_id;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5200: - Delete Base Template');
OKC_TERMS_TEMPLATES_PVT.Delete_Row(
x_return_status => x_return_status,
p_template_id => l_base_template_id,
p_object_version_number => NULL ,
p_delete_parent_yn => 'Y'
);
ALLOWED_TMPL_USAGES_Delete_Set(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_template_id => l_base_template_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5300: Delete records from okc_k_art_varaibles for the doc');
OKC_K_ART_VARIABLES_PVT.delete_set(
x_return_status => x_return_status,
p_doc_type => G_TMPL_DOC_TYPE,
p_doc_id => l_base_template_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5400: Delete records from okc_k_articles_v for the doc');
OKC_K_ARTICLES_PVT.delete_set(
x_return_status => x_return_status,
p_doc_type => G_TMPL_DOC_TYPE,
p_doc_id => l_base_template_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5500: Delete records from okc_sections_v for the doc');
OKC_TERMS_SECTIONS_PVT.delete_set(
x_return_status => x_return_status,
p_doc_type => G_TMPL_DOC_TYPE,
p_doc_id => l_base_template_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5600: Delete delevirable from the base template');
Okc_Deliverable_Process_Pvt.Delete_Deliverables(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_doc_type => G_TMPL_DOC_TYPE,
p_doc_id => l_base_template_id,
p_doc_version => -99,
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,'5700: Update Template Id of working template to old template');
OKC_TERMS_TEMPLATES_PVT.Update_Template_Id(
x_return_status => x_return_status,
p_old_template_id => p_template_id,
p_new_template_id => l_base_template_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5800: Update Template Id of working template to old template for the template Usages ');
Update_Allowed_Tmpl_Usages_Id(
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
p_old_template_id => p_template_id,
p_new_template_id => l_base_template_id
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'5900: Update Template Id of working template to old template for the sections');
OKC_TERMS_SECTIONS_PVT.Update_Row(
x_return_status => x_return_status,
p_id => cr.id,
p_document_id => l_base_template_id,
p_object_version_number => cr.object_version_number
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'6000: Update Template Id of working template to old template for the articles');
OKC_K_ARTICLES_PVT.Update_Row(
x_return_status => x_return_status,
p_id => cr.id,
p_document_id => l_base_template_id,
p_object_version_number => cr.object_version_number
);
OKC_DELIVERABLE_PROCESS_PVT.update_del_for_template_merge (
p_api_version => p_api_version ,
p_init_msg_list => p_init_msg_list,
x_msg_data => x_msg_data ,
x_msg_count => x_msg_count ,
x_return_status => x_return_status,
p_base_template_id => l_base_template_id,
p_working_template_id => p_template_id
);
UPDATE OKC_TMPL_DRAFT_CLAUSES
SET template_id = l_base_template_id
WHERE template_id = p_template_id;
SELECT distinct var.variable_code
FROM okc_k_articles_b kart, okc_k_art_variables var
WHERE kart.document_type=p_doc_type AND kart.document_id=p_doc_id
and var.cat_id=kart.id AND variable_type='S';
SELECT busvar.variable_code
FROM OKC_BUS_DOC_TYPES_B vo, OKC_BUS_VARIABLES_B busvar
WHERE vo.document_type=p_doc_type AND busvar.contract_expert_yn='Y'
AND busvar.variable_intent=vo.intent;
SELECT document_type_class
FROM okc_bus_doc_types_v
WHERE document_type=p_doc_type;
SELECT cat_id, object_version_number
FROM okc_k_art_variables
WHERE variable_code=p_variable_code
AND cat_id IN (SELECT id FROM okc_k_articles_b
WHERE document_type=p_doc_type AND document_id=p_doc_id);
SELECT id, object_version_number
FROM okc_k_articles_b a
WHERE document_type=p_doc_type AND document_id=p_doc_id
and EXISTS (SELECT '!' FROM okc_k_art_variables v
WHERE v.variable_code=p_variable_code AND v.cat_id = a.cat_id );
OKC_K_ART_VARIABLES_PVT.update_row(
x_return_status => x_return_status,
p_cat_id => cr.cat_id,
p_variable_code => p_variable_code,
p_variable_type => NULL,
p_external_yn => NULL,
p_variable_value_id => p_variable_value_id,
p_variable_value => p_variable_value,
p_attribute_value_set_id => NULL,
p_object_version_number => cr.object_version_number
);
OKC_K_ARTICLES_PVT.update_row(
x_return_status => x_return_status,
p_id => cr.id,
p_sav_sae_id => NULL,
p_document_type => NULL,
p_document_id => NULL,
p_source_flag => NULL,
p_mandatory_yn => NULL,
p_scn_id => NULL,
p_label => NULL,
p_amendment_description => NULL,
p_amendment_operation_code => G_AMEND_CODE_UPDATED,
p_article_version_id => NULL,
p_change_nonstd_yn => NULL,
p_orig_system_reference_code => NULL,
p_orig_system_reference_id1 => NULL,
p_orig_system_reference_id2 => NULL,
p_display_sequence => NULL,
p_print_text_yn => NULL,
p_object_version_number => cr.object_version_number
);
SELECT nvl(max(section_sequence),0)+10
FROM OKC_SECTIONS_B
WHERE DOCUMENT_TYPE= p_doc_type
AND DOCUMENT_ID = p_doc_id
AND SCN_ID IS NULL;
OKC_TERMS_SECTIONS_PVT.insert_row(
x_return_status => x_return_status,
p_id => NULL,
p_section_sequence => l_sequence,
p_label => NULL,
p_scn_id => NULL,
p_heading => l_meaning,
p_description => l_meaning,
p_document_type => p_doc_type,
p_document_id => p_doc_id,
p_scn_code => G_UNASSIGNED_SECTION_CODE,
p_amendment_description => NULL,
p_amendment_operation_code => NULL,
p_orig_system_reference_code => NULL,
p_orig_system_reference_id1 => NULL,
p_orig_system_reference_id2 => NULL,
p_print_yn => 'N',
x_id => x_scn_id
);
SELECT 'X' FROM OKC_BUS_DOC_TYPES_B
WHERE document_type=p_doc_type;
SELECT nvl(ver.display_name,art.article_title) name
FROM okc_articles_all art,
okc_article_versions ver
WHERE art.org_id = p_org_id
AND art.article_id = p_article_id
AND art.article_id = ver.article_id
AND ver.start_date <= nvl(p_eff_date,sysdate)
AND ver.start_date = (select max(start_date)
from okc_article_versions ver1
where ver1.article_id = ver.article_id
and ver1.start_date <= nvl(p_eff_date,sysdate)
and ver1.article_status = ver.article_status)
AND (ver.article_status = 'APPROVED' OR
not exists (select 1
from okc_article_versions ver2
where ver2.article_id = art.article_id
and ver2.start_date <= nvl(p_eff_date,sysdate)
and ver2.article_status = 'APPROVED'));
SELECT ver.article_version_id
FROM okc_articles_all art,
okc_article_versions ver
WHERE art.article_id = p_article_id
AND art.article_id = ver.article_id
AND ver.start_date <= nvl(p_eff_date,sysdate)
AND ver.start_date = (select max(start_date)
from okc_article_versions ver1
where ver1.article_id = ver.article_id
and ver1.start_date <= nvl(p_eff_date,sysdate)
and ver1.article_status = ver.article_status)
AND (ver.article_status = 'APPROVED' OR
not exists (select 1
from okc_article_versions ver2
where ver2.article_id = art.article_id
and ver2.start_date <= nvl(p_eff_date,sysdate)
and ver2.article_status = 'APPROVED'));
SELECT 1
FROM dual
WHERE exists (select 1
from OKC_ARTICLE_RELATNS_ALL
where org_id = p_org_id
and source_article_id = p_article_id
and relationship_type = 'ALTERNATE');
SELECT 1
FROM okc_terms_templates_all
WHERE template_id = p_template_id
AND working_copy_flag = 'Y'
UNION ALL
SELECT 1
FROM okc_allowed_tmpl_usages
WHERE template_id = p_template_id
UNION ALL
SELECT 1
FROM okc_k_articles_b
WHERE document_type = 'TEMPLATE'
AND document_id = p_template_id;
SELECT 1
FROM okc_article_relatns_all reln,
okc_article_versions ver
WHERE reln.source_article_id = p_article_id
AND reln.relationship_type = 'ALTERNATE'
AND reln.target_article_id = ver.article_id
AND NVL(p_eff_date,SYSDATE) BETWEEN ver.start_date AND NVL(ver.end_date, nvl(p_eff_date,SYSDATE))
AND ver.article_status = 'APPROVED'
AND reln.org_id = mo_global.get_current_org_id()
AND ( ver.provision_yn = 'N' OR
( p_document_type IN (select document_type
from okc_bus_doc_types_b
where provision_allowed_yn = 'Y'
)
)
);
SELECT 1
FROM okc_article_relatns_all reln,
okc_article_versions ver,
okc_articles_all art
WHERE reln.source_article_id = p_article_id
AND reln.relationship_type = 'ALTERNATE'
AND reln.target_article_id = art.article_id
AND art.article_id = ver.article_id
AND cp_effective_date BETWEEN ver.start_date AND NVL(ver.end_date, cp_effective_date)
AND ( ( p_document_type = 'TEMPLATE') OR ( ver.article_status IN ('APPROVED','ON_HOLD')) )
AND reln.org_id = p_org_id
AND ( (p_org_id = art.org_id
)
OR
( exists ( SELECT 1
FROM okc_article_ADOPTIONS ADP
WHERE adp.global_article_version_id = ver.article_version_id
AND adp.adoption_type = 'ADOPTED'
AND adp.local_org_id = p_org_id
AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')
)
)
)
AND ( ver.provision_yn = 'N' OR
( p_document_type
IN ( SELECT document_type
FROM okc_bus_doc_types_b
WHERE provision_allowed_yn = 'Y'
)
)
);
SELECT 1
FROM okc_k_articles_b kart,
okc_template_usages usg
WHERE usg.document_type = p_document_type
AND usg.document_id = p_document_id
AND usg.document_type = kart.document_type
AND usg.document_id = kart.document_id
AND NVL(usg.contract_source_code,'STRUCTURED') = 'STRUCTURED'
AND (kart.amendment_operation_code IS NOT NULL OR
kart.summary_amend_operation_code IS NOT NULL)
UNION ALL
SELECT 1
FROM okc_sections_b scn,
okc_template_usages usg
WHERE usg.document_type = p_document_type
AND usg.document_id = p_document_id
AND NVL(usg.contract_source_code,'STRUCTURED') = 'STRUCTURED'
AND usg.document_type = scn.document_type
AND usg.document_id = scn.document_id
AND (scn.amendment_operation_code IS NOT NULL OR
scn.summary_amend_operation_code IS NOT NULL)
UNION ALL
SELECT 1
FROM okc_contract_docs kdoc,
okc_template_usages usg
WHERE usg.document_type = p_document_type
AND usg.document_id = p_document_id
AND usg.document_type = kdoc.business_document_type
AND usg.document_id = kdoc.business_document_id
AND NVL(usg.contract_source_code,'STRUCTURED') = 'ATTACHED'
AND kdoc.primary_contract_doc_flag = 'Y'
AND kdoc.delete_flag = 'Y'
AND kdoc.effective_from_version = p_document_version
AND ((NVL(p_document_version, -99) = -99)
OR
(
exists (SELECT 1
FROM
OKC_TEMPLATE_USAGES_H usgH
WHERE
usgH.document_type = p_document_type
AND usgH.document_id = p_document_id
AND usgH.major_version < p_document_version
)
)
)
UNION ALL
SELECT 1
FROM okc_contract_docs kdoc,
okc_template_usages usg
WHERE usg.document_type = p_document_type
AND usg.document_id = p_document_id
AND usg.document_type = kdoc.business_document_type
AND usg.document_id = kdoc.business_document_id
AND NVL(usg.contract_source_code,'STRUCTURED') = 'ATTACHED'
AND kdoc.primary_contract_doc_flag = 'Y'
AND kdoc.business_document_type = kdoc.effective_from_type
AND kdoc.business_document_id = kdoc.effective_from_id
AND kdoc.business_document_version = kdoc.effective_from_version
AND ((kdoc.effective_from_version > 0 and
kdoc.effective_from_version = p_document_version)OR
kdoc.effective_from_version = -99)
AND ((NVL(p_document_version, -99) = -99)
OR
(
exists (SELECT 1
FROM
OKC_TEMPLATE_USAGES_H usgH
WHERE
usgH.document_type = p_document_type
AND usgH.document_id = p_document_id
AND usgH.major_version < p_document_version
)
)
)
;
IF p_existing_summary_code='ADDED' AND p_amend_operation_code ='DELETED' THEN
l_new_summary_code:=FND_API.G_MISS_CHAR; -- Summary should be set to NULL
ELSIF p_existing_summary_code='ADDED' AND p_amend_operation_code = 'UPDATED' THEN
l_new_summary_code:=p_existing_summary_code;
ELSIF p_existing_summary_code='DELETED' AND p_amend_operation_code = 'UPDATED' THEN
l_new_summary_code:=p_existing_summary_code;
ELSIF p_existing_summary_code='DELETED' AND p_amend_operation_code = 'ADDED' THEN
l_new_summary_code:=FND_API.G_MISS_CHAR;
SELECT article_version_number
FROM okc_article_versions
WHERE article_version_id = p_art_version_id;
SELECT heading
FROM okc_sections_b
WHERE id = p_scn_id;
SELECT a.article_title, a.article_number
FROM okc_articles_all a
WHERE a.article_id = p_article_id;
SELECT a.display_name
FROM okc_article_versions a
WHERE a.article_version_id = p_article_version_id;
select HEADING FROM OKC_SECTIONS_B WHERE ID = l_section_id ;
select HEADING FROM OKC_SECTIONS_B WHERE ID = (select scn_id from okc_k_articles_b where id = l_article_id) ;
select meaning from fnd_lookups where lookup_type = 'OKC_ARTICLE_SECTION' and lookup_code = (
select default_section from okc_article_versions where article_version_id = l_article_version_id);
select meaning from fnd_lookups where lookup_type = 'OKC_ARTICLE_SECTION' and
lookup_code = 'UNASSIGNED';
select meaning from fnd_lookups
where lookup_type = 'OKC_ARTICLE_SECTION'
and lookup_code = (select default_section from okc_article_versions
where article_version_id = l_article_version_id);
select meaning from fnd_lookups
where lookup_type = 'OKC_ARTICLE_SECTION'
and lookup_code = (select xprt_scn_code from okc_terms_templates_all
where template_id = l_template_id);
select meaning from fnd_lookups where lookup_type = 'OKC_ARTICLE_SECTION'
and lookup_code = 'UNASSIGNED';
select value.flex_value
from fnd_flex_values_vl value,
fnd_flex_value_sets val_set
where
value.FLEX_VALUE_SET_ID = val_set.FLEX_VALUE_SET_ID
and val_set.flex_value_set_id = l_value_set_id
and to_char(value.flex_value_id)= l_flex_value_id;
SELECT tu.article_effective_date
FROM okc_template_usages tu
WHERE tu.document_type = p_document_type
AND tu.document_id = p_document_id;
SELECT start_date, end_date
FROM OKC_TERMS_TEMPLATES_ALL
WHERE template_id=p_document_id;
SELECT article_version_id ,
article_version_number
FROM okc_article_versions
WHERE article_id= p_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)
AND p_document_type <> 'TEMPLATE'
UNION ALL
SELECT article_version_id ,
article_version_number
FROM okc_article_versions
WHERE article_id= p_article_id
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)
AND p_document_type = 'TEMPLATE'
;
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 = p_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')
AND p_document_type <> 'TEMPLATE'
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 = p_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')
AND p_document_type <> 'TEMPLATE'
UNION ALL
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 = p_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 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')
AND p_document_type = 'TEMPLATE'
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 = p_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 ADP.ADOPTION_TYPE = 'ADOPTED'
AND ADP.LOCAL_ORG_ID = b_local_org_id
AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
AND p_document_type = 'TEMPLATE'
;
SELECT org_id
FROM OKC_ARTICLES_ALL
WHERE article_id = p_article_id;
SELECT article_version_number
FROM okc_article_versions
WHERE article_version_id= b_article__version_id;
SELECT oracle_username
FROM fnd_oracle_userid
WHERE read_only_flag = 'U';
SELECT 1
FROM all_views
WHERE view_name like 'XDO_TEMPLATES_VL'
AND owner = pc_user;
l_sql_stmt := 'SELECT SUBSTR(TEMPLATE_NAME,1,255) FROM XDO_TEMPLATES_VL WHERE TEMPLATE_ID = :1';
SELECT t.org_id
FROM okc_terms_templates_all t,
okc_template_usages u
WHERE t.template_id = u.template_id
AND u.document_type = p_doc_type
AND u.document_id = p_doc_id ;
SELECT t.org_id
FROM okc_terms_templates_all t
WHERE t.template_id = p_doc_id ;
SELECT id
FROM okc_k_headers_b
WHERE document_id = p_document_id ;
If the mode is not 'VIEW', it updates the template usages record based on
business rules.
Where Used: In Authoring page: Structure page invokes this procedure
*************************************************************************************/
PROCEDURE get_template_details (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_document_type IN VARCHAR2,
p_document_id IN NUMBER,
p_mode in VARCHAR2,
p_eff_date IN DATE,
p_org_id IN NUMBER,
x_template_exists OUT NOCOPY VARCHAR2,
x_template_id OUT NOCOPY NUMBER,
x_template_name OUT NOCOPY VARCHAR2,
x_enable_expert_button OUT NOCOPY VARCHAR2,
x_template_org_id OUT NOCOPY NUMBER,
x_doc_numbering_scheme OUT NOCOPY VARCHAR2,
x_config_header_id OUT NOCOPY NUMBER,
x_config_revision_number OUT NOCOPY NUMBER,
x_valid_config_yn OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1;
l_update_date BOOLEAN := false;
l_update_date_with DATE := FND_API.G_MISS_DATE;
select a.template_id, b.template_name, a.article_effective_date,
a.doc_numbering_scheme,
a.config_header_id, a.config_revision_number,
a.valid_config_yn, b.org_id
from okc_template_usages a ,okc_terms_templates_all b
where a.template_id = b.template_id
and a.document_id = p_document_id and a.document_type = p_document_type;
select a.template_id, b.template_name
from
okc_allowed_tmpl_usages a, okc_terms_templates_all b
where a.template_id = b.template_id
and a.default_yn = 'Y'
and b.status_code = 'APPROVED'
and a.document_type = p_document_type
and b.org_id = p_org_id
and nvl(p_eff_date,trunc(sysdate)) between start_date and nvl(end_date, trunc(sysdate));
if(p_mode <> 'UPDATE') THEN
close l_get_template_details_csr;
l_update_date := false;
l_update_date := true;
l_update_date_with := p_eff_date;
l_update_date := true;
l_update_date_with := FND_API.G_MISS_DATE;
l_update_date := true;
l_update_date_with := p_eff_date;
if(l_update_date) then
UPDATE okc_template_usages
SET article_effective_date = l_update_date_with
WHERE document_type = p_document_type
AND document_id = p_document_id;
FUNCTION is_section_deleted(
p_scn_id IN NUMBER
) RETURN VARCHAR2 IS
/*
This function will be called from Update Section Page
Given a scn_id it will return the following:
'D' If the section is deleted i.e AMENDMENT_OPERATION_CODE or SUMMARY_AMEND_OPERATION_CODE is 'DELETED'
'E' If the scn_id does not exists which will result in Stale Data Error
'S' If the scn_id is NOT deleted and exists
*/
CURSOR csr_check_section IS
SELECT amendment_operation_code,summary_amend_operation_code
FROM okc_sections_b
WHERE id = p_scn_id;
l_api_name CONSTANT VARCHAR2(30) := 'is_section_deleted';
IF (NVL(l_amendment_operation_code,'x') = 'DELETED' OR
NVL(l_summary_amend_operation_code,'x') = 'DELETED') THEN
l_return := 'D';
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving is_section_deleted because of EXCEPTION: '||sqlerrm);
END is_section_deleted;
FUNCTION is_article_deleted(
p_cat_id IN NUMBER,
p_article_id IN NUMBER
) RETURN VARCHAR2 IS
/*
This function will be called from Update Article Page
Given a cat_id and sav_sae_id it will return the following:
'D' If the article is deleted i.e AMENDMENT_OPERATION_CODE or SUMMARY_AMEND_OPERATION_CODE is 'DELETED'
'E' If the sav_sae_id does not match the sav_sae_id in record
'S' If the article record is NOT deleted and exists
*/
CURSOR csr_check_article IS
SELECT amendment_operation_code,summary_amend_operation_code
FROM okc_k_articles_b
WHERE id = p_cat_id
AND sav_sae_id = p_article_id ;
l_api_name CONSTANT VARCHAR2(30) := 'is_article_deleted';
IF (NVL(l_amendment_operation_code,'x') = 'DELETED' OR
NVL(l_summary_amend_operation_code,'x') = 'DELETED') THEN
l_return := 'D';
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving is_article_deleted because of EXCEPTION: '||sqlerrm);
END is_article_deleted;
SELECT variable_code
FROM okc_k_art_variables
WHERE cat_id = p_cat_id
AND variable_type = 'D';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting p_num_days to 1 to prevent any current data from being deleted');
delete from OKC_QA_ERRORS_T qa
where creation_date <= sysdate - l_num_days;
CURSOR l_draft_selected_ver_csr IS
SELECT ARTV.article_version_id
FROM OKC_TMPL_DRAFT_CLAUSES TMPLC,
OKC_ARTICLE_VERSIONS ARTV
WHERE TMPLC.template_id = p_doc_id
AND TMPLC.article_id = p_article_id
AND TMPLC.selected_yn = 'Y'
AND ARTV.article_id = TMPLC.article_id
AND ARTV.article_version_id = TMPLC.article_version_id
AND ARTV.article_status in ('DRAFT', 'REJECTED')
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 = TMPLC.article_id);
SELECT ver.article_version_id
FROM okc_articles_all art,
okc_article_versions ver
WHERE art.article_id = p_article_id
AND art.article_id = ver.article_id
AND cp_effective_date BETWEEN ver.start_date AND NVL(ver.end_date,cp_effective_date+1)
-- begin change
-- Bug 4021182, we cannot include pending approval, on hold or expired clauses here
AND VER.article_status IN ('APPROVED', 'DRAFT', 'REJECTED')
AND NOT EXISTS (SELECT 1 from OKC_TMPL_DRAFT_CLAUSES TMPLC
WHERE TMPLC.template_id = p_doc_id
AND TMPLC.article_id = p_article_id
AND TMPLC.article_version_id = VER.article_version_id)
-- end change
ORDER BY ver.article_version_number DESC;
SELECT ver.article_version_id
FROM okc_articles_all art,
okc_article_versions ver
WHERE art.article_id = p_article_id
AND art.article_id = ver.article_id
-- Bugs 4018610, 4018467, the start date of draft clause can be
-- changed to a future date, making this cursor return nothing
-- The draft clause status can also change to pending approval
-- or an approved clause can be put on hold after including in the template
-- AND ver.start_date <= cp_effective_date
AND ver.start_date = (SELECT max(start_date)
FROM okc_article_versions ver1
WHERE ver1.article_id = ver.article_id
--AND ver1.start_date <= cp_effective_date
--AND ver1.article_status = 'APPROVED'
);
CURSOR l_pen_app_selected_ver_csr IS
SELECT ARTV.article_version_id
FROM OKC_TMPL_DRAFT_CLAUSES TMPLC,
OKC_ARTICLE_VERSIONS ARTV
WHERE TMPLC.template_id = p_doc_id
AND TMPLC.article_id = p_article_id
AND TMPLC.selected_yn = 'Y'
AND ARTV.article_id = TMPLC.article_id
AND ARTV.article_version_id = TMPLC.article_version_id
AND ARTV.article_status = 'PENDING_APPROVAL'
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 = TMPLC.article_id);
SELECT ver.article_version_id
FROM okc_articles_all art,
okc_article_versions ver
WHERE art.article_id = p_article_id
AND art.article_id = ver.article_id
AND ver.article_status IN ('APPROVED','EXPIRED','ON_HOLD')
AND cp_effective_date BETWEEN ver.start_date AND NVL(ver.end_date,cp_effective_date+1);
SELECT ver.article_version_id
FROM okc_articles_all art,
okc_article_versions ver
WHERE art.article_id = p_article_id
AND art.article_id = ver.article_id
AND ver.start_date <= cp_effective_date
AND ver.article_status IN ('APPROVED','EXPIRED','ON_HOLD')
AND ver.start_date = (SELECT max(start_date)
FROM okc_article_versions ver1
WHERE ver1.article_id = ver.article_id
AND ver1.start_date <= cp_effective_date
AND ver1.article_status IN ('APPROVED','EXPIRED','ON_HOLD'));
SELECT org_id,
mo_global.get_current_org_id() current_org_id
FROM OKC_ARTICLES_ALL
WHERE article_id = b_article_id;
SELECT ADP.GLOBAL_ARTICLE_VERSION_ID
FROM OKC_ARTICLE_ADOPTIONS ADP,
OKC_ARTICLE_VERSIONS VER
WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VER.article_version_id
AND VER.article_id = b_article_id
AND ADP.LOCAL_ORG_ID = b_current_org_id
--AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
AND ADP.ADOPTION_TYPE IN ('ADOPTED','AVAILABLE')
ORDER BY VER.article_version_number desc,
DECODE(ADP.adoption_status,'APPROVED','001','ON_HOLD','001','002') desc;
OPEN l_draft_selected_ver_csr;
FETCH l_draft_selected_ver_csr INTO l_article_version_id;
IF l_draft_selected_ver_csr%NOTFOUND THEN
l_stop := FALSE;
CLOSE l_draft_selected_ver_csr;
OPEN l_pen_app_selected_ver_csr;
FETCH l_pen_app_selected_ver_csr INTO l_article_version_id;
IF l_pen_app_selected_ver_csr%NOTFOUND THEN
l_stop := FALSE;
CLOSE l_pen_app_selected_ver_csr;
SELECT START_DATE, END_DATE
FROM OKC_TERMS_TEMPLATES_ALL
WHERE TEMPLATE_ID = cp_template_id;
SELECT
oav.article_version_id,
oav.article_id,
oav.start_date,
oav.end_date
FROM okc_article_Versions oav,
okc_articles_all oaa
WHERE oav.article_id = oaa.article_id
AND oaa.org_id = cp_org_id
AND oav.article_status IN ('DRAFT','REJECTED')
AND oav.start_date <= cp_effective_date
AND nvl(oav.end_date, nvl(cp_effective_date,sysdate) +1) >= nvl(cp_effective_date,sysdate)
AND oaa.article_id in
(SELECT clause_id from okc_xprt_clauses_v oxc
WHERE oxc.template_id = cp_template_id);
SELECT osb.heading, nvl(oka.label, '-98766554433.77'),osb.label section_label
FROM okc_sections_b osb,okc_k_articles_b oka
WHERE oka.document_id = cp_template_id
AND oka.sav_sae_id = cp_article_id
AND oka.scn_id = osb.id
AND rownum < 3;
SELECT oav.article_version_id,
oav.article_id
FROM okc_article_Versions oav,
okc_articles_all oaa
WHERE oav.article_id = oaa.article_id
AND oaa.org_id = cp_org_id
AND oav.article_status IN ('DRAFT','REJECTED')
AND oaa.standard_yn = 'Y'
AND oav.start_date <= cp_effective_date
AND nvl(oav.end_date, nvl(cp_effective_date,sysdate) +1) >= nvl(cp_effective_date,sysdate)
AND oaa.article_id in
(SELECT sav_sae_id from okc_k_articles_b oka
WHERE oka.document_id = cp_template_id
AND oka.document_type='TEMPLATE');
SELECT 'Y' from okc_Article_versions
WHERE article_id = cp_article_id
AND article_version_id <> cp_article_version_id
AND article_status = 'APPROVED'
AND start_date <= cp_template_effective_date
AND nvl(end_date, nvl(cp_template_effective_date,sysdate) +1) >= nvl(cp_template_effective_date,sysdate)
AND rownum < 2;
SELECT meaning
FROM fnd_lookups
WHERE lookup_code = 'UNASSIGNED' and lookup_type = 'OKC_ARTICLE_SECTION';
cursor selected_yn_csr( cp_template_id number,
cp_article_id number,
cp_article_version_id number) is
select selected_yn
from OKC_TMPL_DRAFT_CLAUSES
where template_id = cp_template_id
and article_id = cp_article_id
and article_version_id = cp_article_version_id;
TYPE selected_yn_tbl_type IS TABLE of OKC_TMPL_DRAFT_CLAUSES.SELECTED_YN%TYPE INDEX BY BINARY_INTEGER ;
selected_yn_tbl selected_yn_tbl_type;
selected_yn_tbl(i) := NULL;
open selected_yn_csr( p_template_id,
article_id_tbl(i),
article_version_id_tbl(i));
fetch selected_yn_csr into selected_yn_tbl(i);
if selected_yn_csr%NOTFOUND then
selected_yn_tbl(i) := 'Y';
close selected_yn_csr;
draft_articles_tbl.DELETE;
DELETE FROM OKC_TMPL_DRAFT_CLAUSES
WHERE template_id = p_template_id;
INSERT INTO OKC_TMPL_DRAFT_CLAUSES
(
TEMPLATE_ID,
ARTICLE_ID,
ARTICLE_VERSION_ID,
SECTION_NAME,
ARTICLE_LABEL,
MULTIPLE_SCNS_YN,
PREV_VAL_VERSION_YN,
SELECTED_YN,
WF_SEQ_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
p_template_id,
article_id_tbl(i),
article_version_id_tbl(i),
section_name_tbl(i),
decode(article_label_tbl(i),'-98766554433.77',NULL,article_label_tbl(i)),
multiple_scns_yn_tbl(i),
prev_val_version_yn_tbl(i),
selected_yn_tbl(i),
null,
1,
l_user_id,
sysdate,
sysdate,
l_user_id,
l_login_id);
SELECT 'Y'
FROM okc_terms_templates_all tmpl,
okc_k_articles_b kart,
okc_article_versions ver
WHERE tmpl.template_id = lc_tmpl_id
AND kart.document_id = tmpl.template_id
AND kart.document_type = 'TEMPLATE'
AND ver.article_id = kart.sav_sae_id
AND ver.global_yn = 'Y'
AND NOT EXISTS (SELECT 1
FROM okc_article_adoptions adp,
okc_article_versions ver1
WHERE adp.global_article_version_id = ver1.article_version_id
AND ver1.article_id = ver.article_id
AND adp.local_org_id = lc_org_id
AND adp.adoption_status IN ( 'APPROVED', 'ON_HOLD')
AND adp.adoption_type = 'ADOPTED');
PROCEDURE update_contract_admin(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_doc_ids_tbl IN doc_ids_tbl,
p_doc_types_tbl IN doc_types_tbl,
p_new_con_admin_user_ids_tbl IN new_con_admin_user_ids_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name VARCHAR2(30);
l_api_name := 'update_contract_admin';
'Entered OKC_TERMS_UTIL_PVT.update_contract_admin');
SAVEPOINT update_contract_admin;
UPDATE okc_template_usages
SET contract_admin_id = p_new_con_admin_user_ids_tbl(i)
WHERE document_id = p_doc_ids_tbl(i)
AND document_type = p_doc_types_tbl(i);
'Leaving OKC_TERMS_UTIL_PVT.update_contract_admin');
'Leaving OKC_TERMS_UTIL_PVT.update_contract_admin because of EXCEPTION: ' || sqlerrm);
ROLLBACK TO update_contract_admin;
END update_contract_admin;
SELECT resource_id,
resource_grp_id,
quote_number
FROM aso_quote_headers_all
WHERE quote_header_id = p_doc_id;
SELECT sre.user_id
FROM aso_quote_accesses sales_team,
jtf_rs_role_relations rr,
jtf_rs_roles_b rl,
jtf_rs_resource_extns sre
WHERE sales_team.quote_number = p_quote_number
AND rr.ROLE_ID = rl.ROLE_ID
AND NVL(rr.delete_flag,'N') <> 'Y'
AND rr.Role_resource_type = 'RS_INDIVIDUAL'
-- For Bug# 6343627 AND (rr.end_date_active IS NULL OR rr.end_date_active >= SYSDATE)
AND (rr.end_date_active IS NULL OR rr.end_date_active > SYSDATE)
AND (
(rl.role_type_code = 'CONTRACTS' AND rl.role_code = 'CONTRACTS_ADMIN')
OR
(rl.role_type_code = 'SALES' AND rl.role_code = 'CONTRACTS_ADMIN')
OR
(rl.role_code = 'CONTRACTS_ADMIN')
)
AND rr.role_resource_id = sales_team.resource_id
AND sre.resource_id = sales_team.resource_id;
SELECT sre.user_id
FROM jtf_rs_group_members srg,
jtf_rs_resource_extns sre,
jtf_rs_role_relations rr,
jtf_rs_roles_b rl
WHERE srg.group_id = p_sales_group_id
AND srg.resource_id = sre.resource_id
AND NVL(srg.delete_flag,'N') <> 'Y'
AND rr.ROLE_ID = rl.ROLE_ID
AND NVL(rr.delete_flag,'N') <> 'Y'
-- For Bug# 6343627 AND AND rr.Role_resource_type = 'RS_INDIVIDUAL'
AND rr.Role_resource_type = 'RS_GROUP_MEMBER'
-- For Bug# 6343627 AND (rr.end_date_active IS NULL OR rr.end_date_active >= SYSDATE)
AND (rr.end_date_active IS NULL OR rr.end_date_active > SYSDATE)
AND rl.role_type_code = 'CONTRACTS'
AND rl.role_code = 'CONTRACTS_ADMIN'
-- For Bug# 6343627 AND rr.role_resource_id = sre.resource_id;
SELECT related_group_id
FROM jtf_rs_grp_relations
WHERE group_id = p_sales_group_id
AND relation_type = 'PARENT_GROUP';
SELECT 'Y'
FROM okc_review_upld_terms rev
WHERE rev.document_type = p_document_type
AND rev.document_id = p_document_id
;
SELECT lock_terms_flag
FROM okc_template_usages usg
WHERE usg.document_type = p_document_type
AND usg.document_id = p_document_id
;
select adminppl.full_name
from fnd_user ctrtadm, PER_ALL_PEOPLE_F adminppl
where p_contract_admin_id = ctrtadm.user_id(+)
and ctrtadm.employee_id = adminppl.person_id(+)
and adminppl.effective_start_date = adminppl.start_date;
SELECT
contract_template_code
FROM po_document_types_all_b
WHERE org_id = p_org_id
AND document_type_code = l_doc_type
AND document_subtype= l_doc_sub_type;
SELECT
podoctypes.contract_template_code
FROM pon_auction_headers_all pah,
po_document_types_all_b podoctypes,
pon_auc_doctypes pac
WHERE auction_header_id = p_doc_id
AND pah.doctype_id = pac.doctype_id
AND pah.org_id = podoctypes.org_id
AND pac.document_type_code = podoctypes.document_type_code;
SELECT
xdb.template_code
FROM oe_blanket_headers_all oeb,
oe_transaction_types_all otl,
xdo_templates_b xdb
WHERE oeb.order_type_id = otl.transaction_type_id
AND oeb.header_id = p_doc_id
AND otl.layout_template_id = xdb.template_id;
SELECT
xdb.template_code
FROM oe_order_headers_all oeb,
oe_transaction_types_vl otl,
xdo_templates_b xdb
WHERE oeb.order_type_id = otl.transaction_type_id
AND oeb.header_id = p_doc_id
AND otl.layout_template_id = xdb.template_id;
SELECT
xdb.template_code
FROM
okc_terms_templates_all otta,
okc_template_usages_v otuv,
xdo_templates_b xdb
WHERE otuv.document_id = p_doc_id
AND otuv.document_type = p_doc_type
AND otuv.template_id = otta.template_id
AND otta.print_template_id = xdb.template_id;
select fnd_profile.value('ASO_DEFAULT_LAYOUT_TEMPLATE') into l_layout_template_code from dual;
OKC_TEMPLATE_USAGES_GRP.update_template_usages(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list ,
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_document_type => p_document_type,
p_document_id => p_document_id,
p_lock_terms_flag => 'Y',
p_locked_by_user_id => FND_GLOBAL.user_id);
OKC_TEMPLATE_USAGES_GRP.update_template_usages(
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list ,
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_document_type => p_document_type,
p_document_id => p_document_id,
p_lock_terms_flag => 'N');
l_sys_last_update_date date;
SELECT max(Nvl(LAST_UPDATE_DATE,CREATION_DATE))
FROM OKC_K_ARTICLES_B
WHERE DOCUMENT_TYPE=p_document_type
AND DOCUMENT_ID=p_document_id;
SELECT max(Nvl(LAST_UPDATE_DATE,CREATION_DATE))
FROM OKC_SECTIONS_B
WHERE DOCUMENT_TYPE=p_document_type
AND DOCUMENT_ID=p_document_id;
SELECT MAX(NVL(LAST_UPDATE_DATE,CREATION_DATE))
FROM OKC_K_ART_VARIABLES WHERE CAT_ID IN (
SELECT ID FROM OKC_K_ARTICLES_B
WHERE DOCUMENT_TYPE = p_document_type
AND DOCUMENT_ID = document_id);
SELECT MAX(LAST_UPDATE_DATE)
FROM okc_template_usages
WHERE document_type = p_document_type
AND document_id = p_document_id;
SELECT contract_source_code
FROM okc_template_usages
WHERE document_type = p_document_type
AND document_id = p_document_id;
FETCH l_get_max_usg_upd_date_csr INTO l_sys_last_update_date;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9350: l_terms_changed_date :'||l_sys_last_update_date);
FETCH l_get_max_usg_upd_date_csr INTO l_sys_last_update_date;
l_sys_last_update_date := nvl(l_sys_last_update_date,okc_api.g_miss_date);
l_sys_last_update_date := Greatest(l_article_change_date, l_section_change_date,l_variable_change_date,l_sys_last_update_date);
if(l_sys_last_update_date = OKC_API.G_MISS_DATE) THEN
l_sys_last_update_date := sysdate;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE,'9700: l_sys_last_update_date : '||l_sys_last_update_date);
return l_sys_last_update_date;
select ref_article_id from okc_k_articles_b
where id = p_id;
SELECT VB.variable_code,
KA.id,
KA.article_version_id
FROM okc_k_articles_b KA,
okc_k_art_variables KV,
okc_bus_variables_b VB
WHERE VB.variable_code = KV.variable_code
AND KA.id = KV.cat_id
AND VB.variable_source = 'P'
AND KA.document_type = p_document_type
AND KA.document_id = p_document_id
ORDER BY VB.variable_code;
DELETE FROM OKC_TERMS_UDV_WITH_PROCEDURE_T;
/* Insert data into the temp table */
IF l_variable_value IS NOT NULL THEN
INSERT INTO OKC_TERMS_UDV_WITH_PROCEDURE_T
(
VARIABLE_CODE,
VARIABLE_VALUE,
DOC_TYPE,
DOC_ID,
ARTICLE_VERSION_ID,
CAT_ID
)
VALUES
(
csr_udv_with_procs_rec.variable_code, -- VARIABLE_CODE
l_variable_value, -- VARIABLE_VALUE
p_document_type, -- DOCUMENT_TYPE
p_document_id, -- DOCUMENT_ID
csr_udv_with_procs_rec.article_version_id, -- ARTICLE_VERSION_ID
csr_udv_with_procs_rec.id -- CAT_ID
);
SELECT VB.procedure_name,
VB.value_set_id,
VT.variable_name
FROM okc_bus_variables_b VB,
okc_bus_variables_tl VT
WHERE VB.variable_code = VT.variable_code
AND VT.language = USERENV('LANG')
AND VB.variable_code = p_variable_code
AND VB.variable_source = 'P';
SELECT validation_type
FROM FND_FLEX_VALUE_SETS
WHERE flex_value_set_id = p_value_set_id;
SELECT application_table_name,
value_column_name,
id_column_name,
additional_where_clause
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = p_value_set_id;
SELECT status
FROM all_objects
WHERE object_name = SUBSTR(p_procedure_name,
INSTR(p_procedure_name,'.')+1,
(INSTR(p_procedure_name,'.',1,2) -
INSTR(p_procedure_name,'.') - 1))
AND object_type = 'PACKAGE'
AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
SELECT status
FROM all_objects
WHERE object_name = SUBSTR(p_procedure_name,
INSTR(p_procedure_name,'.')+1,
(INSTR(p_procedure_name,'.',1,2) -
INSTR(p_procedure_name,'.') - 1))
AND object_type = 'PACKAGE BODY'
AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1);
SELECT 'X'
FROM all_source
WHERE name = SUBSTR(p_procedure_name,
INSTR(p_procedure_name,'.')+1,
(INSTR(p_procedure_name,'.',1,2) -
INSTR(p_procedure_name,'.') - 1))
AND type = 'PACKAGE'
AND owner = SUBSTR(p_procedure_name,1,INSTR(p_procedure_name,'.')-1)
AND text LIKE '%' || SUBSTR(p_procedure_name,INSTR(p_procedure_name,'.',1,2)+1) || '%';
l_sql_stmt := 'SELECT '||l_name_col||
' FROM ('||
' SELECT '||l_name_col||' , '||l_id_col||
' FROM '||l_table_name||' ';