[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_AMEND_CODE_DELETED CONSTANT VARCHAR2(30) := 'DELETED';
PROCEDURE Delete_Doc (
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_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_string IN VARCHAR2 := NULL,
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER
,p_retain_lock_terms_yn IN VARCHAR2 := 'N'
,p_retain_lock_xprt_yn IN VARCHAR2 := 'N'
,p_retain_lock_deliverables_yn IN VARCHAR2 := 'N'
-- For backward compatability
,p_retain_deliverables_yn IN VARCHAR2 := 'N'
-- For backward compatability
,P_RELEASE_LOCKS_YN IN VARCHAR2 := 'N'
, p_retain_exhibits IN VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Doc';
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'100: Entered Delete_Doc');
SAVEPOINT g_Delete_Doc;
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1000: Calling Ptivate API to Delete the doc');
OKC_TERMS_UTIL_PVT.Delete_Doc(
x_return_status => x_return_status,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id
,p_retain_lock_terms_yn => p_retain_lock_terms_yn
,p_retain_lock_xprt_yn => p_retain_lock_xprt_yn
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1100: Delete delevirable for the doc');
OKC_DELIVERABLE_PROCESS_PVT.Delete_Deliverables(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_doc_type => p_doc_type,
p_doc_id => p_doc_id,
p_doc_version => -99,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status
, p_retain_lock_deliverables_yn => p_retain_lock_deliverables_yn
, p_retain_exhibits => p_retain_exhibits
);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1200: Delete attachments for the doc');
OKC_CONTRACT_DOCS_GRP.Delete_Ver_Attachments(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
p_commit => FND_API.G_FALSE,
p_business_document_type => p_doc_type,
p_business_document_id => p_doc_id,
p_business_document_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,'1300: Leaving Delete_Doc');
ROLLBACK TO g_Delete_Doc;
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1400: Leaving Delete_Doc : OKC_API.G_EXCEPTION_ERROR Exception');
ROLLBACK TO g_Delete_Doc;
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1500: Leaving Delete_Doc : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
ROLLBACK TO g_Delete_Doc;
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'1600: Leaving Delete_Doc because of EXCEPTION: '||sqlerrm);
END Delete_Doc ;
DELETE FROM OKC_K_ART_VAR_EXT_TL
WHERE cat_id IN
( SELECT KART.ID FROM OKC_K_ARTICLES_B KART, OKC_K_ART_VARIABLES ARTVAR, OKC_BUS_VARIABLES_B BUSVAR
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i)
AND BUSVAR.mrv_flag='Y'
AND ARTVAR.VARIABLE_CODE=BUSVAR.VARIABLE_CODE
AND ARTVAR.CAT_ID=KART.ID);
DELETE FROM OKC_K_ART_VAR_EXT_B
WHERE cat_id IN
( SELECT KART.ID FROM OKC_K_ARTICLES_B KART, OKC_K_ART_VARIABLES ARTVAR, OKC_BUS_VARIABLES_B BUSVAR
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i)
AND BUSVAR.mrv_flag='Y'
AND ARTVAR.VARIABLE_CODE=BUSVAR.VARIABLE_CODE
AND ARTVAR.CAT_ID=KART.ID);
DELETE FROM OKC_K_ART_VARIABLES
WHERE cat_id IN
( SELECT ID FROM OKC_K_ARTICLES_B
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i));
DELETE FROM OKC_K_ART_VAR_EXT_TLH
WHERE cat_id IN
( SELECT KART.ID FROM OKC_K_ARTICLES_BH KART, OKC_K_ART_VARIABLES_H ARTVAR, OKC_BUS_VARIABLES_B BUSVAR
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i)
AND BUSVAR.mrv_flag='Y'
AND ARTVAR.VARIABLE_CODE=BUSVAR.VARIABLE_CODE
AND ARTVAR.CAT_ID=KART.ID);
DELETE FROM OKC_K_ART_VAR_EXT_BH
WHERE cat_id IN
( SELECT KART.ID FROM OKC_K_ARTICLES_BH KART, OKC_K_ART_VARIABLES_H ARTVAR, OKC_BUS_VARIABLES_B BUSVAR
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i)
AND BUSVAR.mrv_flag='Y'
AND ARTVAR.VARIABLE_CODE=BUSVAR.VARIABLE_CODE
AND ARTVAR.CAT_ID=KART.ID);
DELETE FROM OKC_K_ART_VARIABLES_H
WHERE cat_id IN
( SELECT ID FROM OKC_K_ARTICLES_BH
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i));
DELETE FROM OKC_K_ARTICLES_B
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i);
DELETE FROM OKC_K_ARTICLES_BH
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i);
DELETE FROM OKC_SECTIONS_B
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i);
DELETE FROM OKC_SECTIONS_BH
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i);
DELETE FROM OKC_TEMPLATE_USAGES
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i);
DELETE FROM OKC_TEMPLATE_USAGES_H
WHERE document_type=l_doc_type_tbl(i)
and document_id=l_doc_id_tbl(i);
DELETE FROM OKC_XPRT_DOC_QUES_RESPONSE
WHERE doc_type=l_doc_type_tbl(i)
and doc_id=l_doc_id_tbl(i);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Delete attachments for the doc');
OKC_CONTRACT_DOCS_GRP.Delete_doc_Attachments(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level=> FND_API.G_VALID_LEVEL_FULL,
p_commit => FND_API.G_FALSE,
p_business_document_type => p_doc_tbl(i).doc_type,
p_business_document_id => p_doc_tbl(i).doc_id,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count,
x_return_status => x_return_status
);
select distinct var.variable_code
from okc_k_art_variables var, okc_k_articles_b kart
where kart.document_type = p_doc_type
and kart.document_id = p_doc_id
and kart.amendment_Operation_code is null
and kart.id=var.cat_id
and var.variable_type='S';
SELECT document_type_class
FROM okc_bus_doc_types_b
WHERE document_type=p_doc_type;
SELECT a.standard_yn
FROM okc_k_articles_b kart, okc_articles_all a
WHERE kart.document_type=p_doc_type
AND kart.document_id=p_doc_id
AND nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
AND nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED
AND a.article_id = kart.sav_sae_id
ORDER BY Decode(a.standard_yn,'N',1) ASC ;
11-OCT-2004 pnayani updated Is_Document_Updatable FUNCTION
Added logic to check for REPOSITORY class documents
14-MAR-2005 andixit Updated Repository logic.
*/
FUNCTION Is_Document_Updatable(
p_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
p_validation_string IN VARCHAR2
) RETURN VARCHAR2 IS -- 'T' - updatable, 'F'- nonupdatable, 'E' - doesn't exist
l_api_version CONSTANT NUMBER := 1;
SELECT Decode(STATUS_CODE,'PENDING_APPROVAL',G_FALSE,'EXPIRED',G_FALSE,
Decode(NVL(HIDE_YN,'N'), 'N',
Decode(c.cnt,0,
Decode( NVL(t.ORG_ID,p_org_id), p_org_id,G_TRUE, G_FALSE)
,G_FALSE)
,G_FALSE)
) upd
FROM okc_terms_templates_all t,
(SELECT Count(*) cnt FROM OKC_TERMS_TEMPLATES_ALL i WHERE i.PARENT_TEMPLATE_ID = p_doc_id) c
WHERE template_id = p_doc_id;
SELECT document_type_class
FROM okc_bus_doc_types_b
WHERE document_type = p_doc_type;
SELECT G_TRUE
FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
UNION ALL
SELECT G_TRUE
FROM dual
WHERE NOT EXISTS (SELECT 'Y' FROM OKC_TEMPLATE_USAGES
WHERE DOCUMENT_TYPE = p_doc_type
AND DOCUMENT_ID = p_doc_id
)
AND EXISTS ( SELECT 'Y' FROM okc_deliverables
WHERE business_document_type=p_doc_type
AND business_DOCUMENT_ID=p_doc_id
);
SELECT 'Y'
FROM okc_k_articles_b a
WHERE ( document_type=p_doc_type AND document_id=p_doc_id AND source_flag
IS NULL )
OR (
document_type='TEMPLATE' AND document_id IN
(SELECT template_id FROM okc_template_usages
WHERE document_type=p_doc_type AND document_id=p_doc_id )
AND NOT EXISTS
(SELECT 'x' from okc_k_articles_b b
WHERE b.document_type=p_doc_type AND b.document_id=p_doc_id
AND ( b.sav_sae_id=a.sav_sae_id /*or b.ref_article_id=a.sav_sae_id*/) )
)
;
SELECT 'Y'
FROM okc_template_usages_v tu,
okc_terms_templates_all t
WHERE tu.document_type = p_doc_type AND tu.document_id = p_doc_id
AND t.template_id = tu.template_id AND t.status_code = 'APPROVED'
AND nvl(t.end_date,sysdate+1) <= sysdate;
SELECT a.standard_yn
FROM okc_k_articles_b kart, okc_articles_all a
WHERE kart.document_type=p_doc_type
AND kart.document_id=p_doc_id
AND kart.summary_amend_operation_code IS NOT NULL
AND a.article_id = kart.sav_sae_id
ORDER BY Decode(a.standard_yn,'N',1) ASC ;
SELECT t.template_id, t.template_name
FROM okc_template_usages_v tu, okc_terms_templates_all t
WHERE tu.document_type = p_doc_type AND tu.document_id = p_doc_id
AND t.template_id = tu.template_id;
SELECT
decode(application_id,515,'OKS',510,decode(buy_or_sell,'S','OKC_SELL','B','OKC_BUY','OKC_SELL'),871,'OKO',777,decode(buy_or_sell,'S','OKE_SELL','B','OKE_BUY','OKE_SELL'),540,'OKL',Null)
FROM okc_k_headers_b
WHERE id=p_chr_id;
SELECT
decode(application_id,515,'OKS',510,decode(buy_or_sell,'S','OKC_SELL','B','OKC_BUY','OKC_SELL'),871,'OKO',777,decode(buy_or_sell,'S','OKE_SELL','B','OKE_BUY','OKE_SELL'),540,'OKL',Null),
document_id
FROM okc_k_headers_b
WHERE id=p_chr_id;
PROCEDURE Get_Last_Update_Date (
p_api_version IN NUMBER,
p_init_msg_list 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_doc_type IN VARCHAR2,
p_doc_id IN NUMBER,
x_deliverable_changed_date OUT NOCOPY DATE,
x_terms_changed_date OUT NOCOPY DATE
) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'Get_Last_Update_Date';
SELECT max(Nvl(LAST_AMENDMENT_DATE,CREATION_DATE))
FROM OKC_K_ARTICLES_B
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id;
SELECT max(Nvl(LAST_AMENDMENT_DATE,CREATION_DATE))
FROM OKC_SECTIONS_B
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id;
SELECT max(Nvl(LAST_AMENDMENT_DATE,CREATION_DATE))
FROM OKC_K_ARTICLES_BH
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id;
SELECT max(Nvl(LAST_AMENDMENT_DATE,CREATION_DATE))
FROM OKC_SECTIONS_BH
WHERE DOCUMENT_TYPE=p_doc_type
AND DOCUMENT_ID=p_doc_id;
SELECT contract_source_code
FROM okc_template_usages
WHERE document_type = p_doc_type
AND document_id = p_doc_id;
SELECT MAX(last_update_date)
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,'8800: Entered get_last_update_date');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10100: Leaving get_last_update_date : OKC_API.G_EXCEPTION_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10200: Leaving get_last_update_date : OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'10300: Leaving get_last_update_date because of EXCEPTION: '||sqlerrm);
END get_last_update_date;
SELECT document_type_class
FROM okc_bus_doc_types_b
WHERE document_type=p_doc_type;
SELECT status_code,nvl(end_date,sysdate+1) end_date
FROM okc_terms_templates_all
WHERE template_id = p_doc_id;
SELECT '!'
FROM okc_k_articles_b kart
WHERE kart.document_type=p_doc_type
AND kart.document_id=p_doc_id
AND kart.source_flag IS NULL
AND nvl(kart.amendment_operation_code,'?')<>G_AMEND_CODE_DELETED
AND nvl(kart.summary_amend_operation_code,'?')<>G_AMEND_CODE_DELETED;
SELECT template_name
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
SELECT document_type_class
FROM okc_bus_doc_types_b
WHERE document_type=p_doc_type;
SELECT document_type_class
FROM okc_bus_doc_types_b
WHERE document_type=p_doc_type;
SELECT template_name,
intent,
status_code,
start_date,
end_date ,
instruction_text ,
description ,
global_flag ,
contract_expert_enabled,
org_id
FROM okc_terms_templates_all
where template_id=b_template_id;
SELECT t.template_id,
t.template_name,
t.instruction_text ,
t.description
FROM okc_template_usages_v tu, okc_terms_templates_all t
WHERE tu.document_type = p_doc_type AND tu.document_id = p_doc_id
AND t.template_id = tu.template_id;
FUNCTION enable_update(
p_object_type IN VARCHAR2,
p_document_type IN VARCHAR2,
p_standard_yn IN VARCHAR2
) RETURN VARCHAR2 IS
l_api_name CONSTANT VARCHAR2(30) := 'enable_update';
RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
RETURN 'OkcTermsStructDtlsUpdateDisabled';
RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving enable_update because of EXCEPTION: '||sqlerrm);
END enable_update;
FUNCTION enable_delete(
p_object_type IN VARCHAR2,
p_mandatory_yn IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_document_type IN VARCHAR2
) RETURN VARCHAR2 IS
l_api_name CONSTANT VARCHAR2(30) := 'enable_delete';
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving enable_delete because of EXCEPTION: '||sqlerrm);
END enable_delete;
SELECT 'Y'
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;
SELECT tu.document_id,
tu.authoring_party_code,
tu.contract_source_code,
tu.template_id,
t.template_name,
t.description,
party.alternate_name authoring_party,
src.meaning contract_source
FROM OKC_TEMPLATE_USAGES tu,
OKC_TERMS_TEMPLATES_ALL t,
okc_resp_parties_vl party,
okc_bus_doc_types_b doc,
fnd_lookups src
WHERE t.template_id(+) = tu.template_id
AND tu.authoring_party_code = party.resp_party_code
AND tu.document_type = doc.document_type
AND doc.document_type_class = party.document_type_class
AND NVL(doc.intent,'zzz') = NVL(party.intent,'zzz')
AND src.lookup_type = 'OKC_CONTRACT_TERMS_SOURCES'
AND src.lookup_code = tu.contract_source_code
AND tu.document_type = p_document_type
AND tu.document_id = p_document_id;
SELECT tu.document_id,
tu.authoring_party_code,
tu.contract_source_code,
tu.template_id,
t.template_name,
t.description,
t.instruction_text,
party.alternate_name authoring_party,
src.meaning contract_source
FROM OKC_TEMPLATE_USAGES tu,
OKC_TERMS_TEMPLATES_ALL t,
okc_resp_parties_vl party,
okc_bus_doc_types_b doc,
fnd_lookups src
WHERE t.template_id(+) = tu.template_id
AND tu.authoring_party_code = party.resp_party_code
AND tu.document_type = doc.document_type
AND doc.document_type_class = party.document_type_class
AND NVL(doc.intent,'zzz') = NVL(party.intent,'zzz')
AND src.lookup_type = 'OKC_CONTRACT_TERMS_SOURCES'
AND src.lookup_code = tu.contract_source_code
AND tu.document_type = p_document_type
AND tu.document_id = p_document_id;
SELECT tl.media_id ,
docs.generated_flag
FROM OKC_CONTRACT_DOCS docs, FND_ATTACHED_DOCUMENTS fnd,FND_DOCUMENTS_TL tl
WHERE docs.primary_contract_doc_flag = 'Y'
AND docs.business_document_version=-99
AND docs.business_document_type = p_document_type
AND docs.business_document_id = p_document_id
AND docs.attached_document_id = fnd.attached_document_id
AND fnd.document_id = tl.document_id
AND tl.language = USERENV('LANG');
SELECT tu.document_id,
tu.authoring_party_code,
tu.contract_source_code,
tu.template_id,
t.template_name,
t.description,
t.instruction_text,
party.alternate_name authoring_party,
src.meaning contract_source
FROM OKC_TEMPLATE_USAGES_H tu,
OKC_TERMS_TEMPLATES_ALL t,
okc_resp_parties_vl party,
okc_bus_doc_types_b doc,
fnd_lookups src
WHERE t.template_id(+) = tu.template_id
AND tu.authoring_party_code = party.resp_party_code
AND tu.document_type = doc.document_type
AND doc.document_type_class = party.document_type_class
AND NVL(doc.intent,'zzz') = NVL(party.intent,'zzz')
AND src.lookup_type = 'OKC_CONTRACT_TERMS_SOURCES'
AND src.lookup_code = tu.contract_source_code
AND tu.document_type = p_document_type
AND tu.document_id = p_document_id
AND tu.major_version = p_document_version;
SELECT tl.media_id ,
docs.generated_flag
FROM OKC_CONTRACT_DOCS docs, FND_ATTACHED_DOCUMENTS fnd,FND_DOCUMENTS_TL tl
WHERE docs.primary_contract_doc_flag = 'Y'
AND docs.business_document_version = p_document_version
AND docs.business_document_type = p_document_type
AND docs.business_document_id = p_document_id
AND docs.attached_document_id = fnd.attached_document_id
AND fnd.document_id = tl.document_id
AND tl.language = USERENV('LANG');
SELECT authoring_party_code
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;
SELECT 'Y'
FROM OKC_TERMS_TEMPLATES_ALL tmpl,
OKC_ALLOWED_TMPL_USAGES usg,
OKC_BUS_DOC_TYPES_B doc
WHERE tmpl.template_id = p_template_id
AND doc.document_type = p_doc_type
AND doc.intent = tmpl.intent
AND usg.template_id = tmpl.template_id
AND usg.document_type = p_doc_type
AND tmpl.status_code = 'APPROVED'
AND tmpl.org_id = p_org_id
AND p_valid_date between tmpl.start_date and nvl(tmpl.end_date,p_valid_date);
SELECT tmpl.template_name,
tmpl.description,
okc_util.decode_lookup('OKC_CONTRACT_TERMS_SOURCES','STRUCTURED'),
party.alternate_name
FROM okc_terms_templates_all tmpl,
okc_resp_parties_vl party,
okc_bus_doc_types_b doc
WHERE tmpl.template_id = p_template_id
and party.document_type_class = doc.document_type_class
and party.intent = doc.intent
and doc.document_type= p_document_type
and party.resp_party_code = 'INTERNAL_ORG';
SELECT tmpl.template_id,
tmpl.template_name,
tmpl.description
FROM OKC_TERMS_TEMPLATES_ALL tmpl,
OKC_ALLOWED_TMPL_USAGES usg,
OKC_BUS_DOC_TYPES_B doc
WHERE doc.document_type = p_document_type
AND doc.intent = tmpl.intent
AND usg.template_id = tmpl.template_id
AND usg.document_type = p_document_type
AND usg.document_type = doc.document_type
AND usg.default_yn = 'Y'
AND tmpl.status_code = 'APPROVED'
AND tmpl.org_id = p_org_id
AND p_valid_date between tmpl.start_date and nvl(tmpl.end_date,p_valid_date);
SELECT usg.autogen_deviations_flag,types.application_id
FROM OKC_TEMPLATE_USAGES usg, OKC_BUS_DOC_TYPES_B types
WHERE usg.document_type = p_document_type
AND usg.document_id = p_document_id
AND types.document_type = usg.document_type;
select media_id from okc_contract_docs_details_vl
where business_document_id = p_document_id
and business_document_type = p_document_type
and category_code = 'OKC_REPO_APPROVAL_ABSTRACT'
and datatype_id = 6;
SELECT 'Y'
FROM FND_LOOKUP_VALUES
WHERE lookup_code = 'REVIEW_DEV_REP'
AND lookup_type = 'OKC_TERMS_AUTH_ACTIONS_VIEW';
SELECT contract_source_code
FROM okc_template_usages
WHERE document_id = p_doc_id
AND document_type = p_doc_type;
SELECT 'Y'
FROM okc_contract_docs
WHERE business_document_id = p_doc_id
AND business_document_type = p_doc_type
AND business_document_version = -99
AND effective_from_id = business_document_id
AND effective_from_type = business_document_type
AND effective_from_version = business_document_version
AND primary_contract_doc_flag = 'Y'
UNION ALL
SELECT 'Y'
FROM okc_contract_docs
WHERE business_document_id = p_doc_id
AND business_document_type = p_doc_type
AND business_document_version = -99
AND delete_flag = 'Y'
AND primary_contract_doc_flag = 'Y';
SELECT translated_from_tmpl_id, language, org_id, parent_template_id
FROM okc_terms_templates_all
WHERE template_id = p_template_id;
SELECT template_id
FROM okc_terms_templates_all
WHERE language = p_language
AND org_id = l_org_id
AND template_id = l_tmpl_id
UNION ALL
SELECT template_id
FROM okc_terms_templates_all
WHERE language = p_language
AND org_id = l_org_id
AND translated_from_tmpl_id = l_tmpl_id;
SELECT orig_system_reference_code, orig_system_reference_id1, document_type,document_id
From okc_k_articles_b
Where id= cp_kart_sec_id ;
SELECT orig_system_reference_code, orig_system_reference_id1, document_type,document_id
From okc_sections_b
Where id= cp_kart_sec_id ;
SELECT 'Y'
FROM okc_k_entity_locks
WHERE entity_name= cp_entity_name
AND entity_pk1= cp_entity_pk1
AND lock_by_document_type <> cp_lock_by_doc_type
AND lock_by_document_id <> cp_lock_by_doc_id;
FUNCTION enable_update(
p_object_type IN VARCHAR2,
p_document_type IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_kart_sec_id in NUMBER
) RETURN VARCHAR2
IS
l_lock_exists VARCHAR2(1);
RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
RETURN 'OkcTermsStructDtlsUpdateDisabled';
RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
RETURN 'OkcTermsStructDtlsUpdateEnabled' ;
RETURN 'OkcTermsStructDtlsUpdateDisabled' ;
END enable_update;
FUNCTION enable_delete(
p_object_type IN VARCHAR2,
p_mandatory_yn IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_document_type IN VARCHAR2 := NULL,
p_kart_sec_id in number
) RETURN VARCHAR2
IS
CURSOR cur_art_details
IS
SELECT orig_system_reference_code, orig_system_reference_id1
From okc_k_articles_b
Where id= p_kart_sec_id ;
RETURN enable_delete(
p_object_type ,
p_mandatory_yn ,
p_standard_yn ,
p_document_type );
END enable_delete;
FUNCTION enable_delete(
p_object_type IN VARCHAR2,
p_mandatory_yn IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_document_type IN VARCHAR2 := NULL ,
p_kart_sec_id in NUMBER,
p_lockingEnabledYn IN VARCHAR2
) RETURN VARCHAR2
IS
BEGIN
RETURN enable_delete(
p_object_type ,
p_mandatory_yn ,
p_standard_yn ,
p_document_type );
END enable_delete;
FUNCTION enable_update(
p_object_type IN VARCHAR2,
p_document_type IN VARCHAR2,
p_standard_yn IN VARCHAR2,
p_kart_sec_id in NUMBER,
p_lockingEnabledYn IN VARCHAR2
) RETURN VARCHAR2
IS
BEGIN
RETURN enable_update(
p_object_type ,
p_document_type ,
p_standard_yn
);
END enable_update;
SELECT 'Y'
FROM okc_deliverables
WHERE business_document_type = p_document_type
AND business_document_id = p_document_id
AND business_document_version=Nvl(p_doc_version,-99);