[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select nvl(provision_allowed_YN,'N')
INTO l_provision_allowed
From okc_bus_doc_types_b
Where document_type = p_doc_type;
Select SCN_ID,
SAV_SAE_ID ,
Display_Sequence,
Mandatory_YN ,
Label,
okc_terms_util_pvt.get_latest_tmpl_art_version_id(sav_sae_id,
sysdate),
id,
sav_sae_id orig_article_id
BULK COLLECT INTO
l_scn_id,
l_article_id,
l_display_sequence,
l_mandatory_flag,
l_label,
l_article_version_id,
l_art_seq_id,
l_orig_article_id
From
okc_k_articles_b
Where document_Type = 'TEMPLATE'
And document_Id = p_template_id;
Select SCN_ID,
SAV_SAE_ID ,
Display_Sequence,
Mandatory_YN ,
Label,
okc_terms_util_pvt.get_latest_tmpl_art_version_id(sav_sae_id,
sysdate),
id,
sav_sae_id orig_article_id
BULK COLLECT INTO
l_scn_id,
l_article_id,
l_display_sequence,
l_mandatory_flag,
l_label,
l_article_version_id,
l_art_seq_id,
l_orig_article_id
From
okc_k_articles_b oab
Where document_Type = 'TEMPLATE'
And document_Id = p_template_id
And Exists (Select 1 From okc_article_versions oav
Where oab.sav_sae_id = oav.article_id
And oav.provision_yn = 'N');
INSERT INTO okc_terms_deviations_temp (Scn_id,
article_id,
display_sequence,
mandatory_flag,
label,
article_version_id,
source_flag,
orig_article_id)
VALUES
(l_scn_id(i),
l_article_id(i),
l_display_sequence(i),
l_mandatory_flag(i),
l_label(i),
l_article_version_id(i),
'T',
l_orig_article_id(i));
'130: Inserted TEMPLATE data in Global Temp Table');
'240: Inserting the data in temp table as count > 0');
INSERT INTO okc_terms_deviations_temp (
article_id,
article_version_id,
source_flag)
VALUES
(l_expert_articles_tbl(i),
okc_terms_util_pvt.get_latest_tmpl_art_version_id(
l_expert_articles_tbl(i),
sysdate),
'R');
SELECT a.lookup_code deviation_category,
b.lookup_code deviation_code,
a.meaning deviation_category_meaning,
b.meaning deviation_code_meaning,
b.tag deviation_category_priority -- Changed a.tag to b.tag
FROM fnd_lookup_values a, fnd_lookup_values b
WHERE a.lookup_code = b.lookup_type
AND a.lookup_type = 'OKC_TERMS_DEVIATION_CATEGORIES'
AND a.enabled_flag = b.enabled_flag
AND a.language = b.language
AND b.language = USERENV('LANG')
AND b.enabled_flag = 'Y'
ORDER BY b.tag; -- Changed a.tag to b.tag
procedure Update_deviation_details(x_return_status OUT NOCOPY VARCHAR2,
p_sequence_id IN NUMBER)
is
l_api_name CONSTANT Varchar2(60):='UPDATE_DEVIATIONS_DETAILS';
'700: Entered UPDATE_DEVIATION_DETAILS');
Update okc_terms_deviations_t odt set
(odt.scn_label, odt.scn_sequence) = (select label, section_sequence from
okc_sections_b where id = odt.scn_id)
where sequence_id = p_sequence_id;
** update the scn_sequence for the section/clause which is currently
** on document i.e, deviations for the clauses existing on document.
** update the scn_sequence for the section/clause which is missing
** on document but is from template.
** update the scn_sequence for section/clause which is present on the
** template but is not existing on document.
** There is additional logic to handle sections within section, the
** order is achieved by the following:
** Ex: seq section
** 10 S1
** 20 |->S2 (child section)
** 20 S3 (here the parent section will be based
** on prev parent)
** 30 S4
** In order to get the correct section sequence the child section
** sequence generated as parent scn seq + child scn seq * 0.0001
** so in the case the final section sequence will be
** Ex: seq section
** 10 S1
** 10.002 |->S2 (child section)
** 20 S3 (here the parent section will be based
** on prev parent)
** 30 S4
*/
-- updates the scn_sequence for the caluses which are exisiting on document
Update okc_terms_deviations_t odt set
(odt.scn_label, odt.scn_sequence) =
(select osb1.label, decode(osb1.section_sequence,
osb2.section_sequence,osb1.section_sequence,
osb2.section_sequence+(1/10000)*osb1.section_sequence) -- Bug#4615605 replaced .0001 with 1/10000
from okc_sections_b osb1, okc_sections_b osb2
where nvl(osb1.scn_id,osb1.id) = osb2.id
and osb1.document_type = odt.document_type
and osb1.document_id = odt.document_id
and osb1.id = odt.scn_id)
where sequence_id = p_sequence_id;
update okc_terms_deviations_t odt set
(odt.scn_label, odt.scn_sequence) = (select osb1.label,
decode(osb1.section_sequence,
osb2.section_sequence,osb1.section_sequence,
osb2.section_sequence+(1/10000)*osb1.section_sequence)
from okc_sections_b osb1, okc_sections_b osb2, okc_sections_b osb3
where nvl(osb1.scn_id,osb1.id) = osb2.id
and osb1.document_type = odt.document_type
and osb1.document_id = odt.document_id
and osb3.id = odt.scn_id
and osb3.id = to_number(osb1.orig_system_reference_id1))
where sequence_id = p_sequence_id
and scn_sequence is null;
SELECT nvl(max(scn_sequence),0) INTO l_max_scn_seq
FROM okc_terms_deviations_t
WHERE sequence_id = p_sequence_id;
Update okc_terms_deviations_t odt set
(odt.scn_label, odt.scn_sequence) =
(select osb1.label, to_number(decode(osb1.section_sequence,
osb2.section_sequence,osb1.section_sequence,
osb2.section_sequence+(1/10000)*osb1.section_sequence))
+ l_max_scn_seq
from okc_sections_b osb1, okc_sections_b osb2
where nvl(osb1.scn_id,osb1.id) = osb2.id
and osb1.document_type = 'TEMPLATE'
and osb1.id = odt.scn_id)
where sequence_id = p_sequence_id
and scn_sequence is null;
'710: Updated Section Label');
Update okc_terms_deviations_t odt set
lock_text_mod_flag = (select lock_text from
okc_article_versions
where article_version_id = odt.ref_article_version_id)
where sequence_id = p_sequence_id
and deviation_code = 'MODIFIED_STD';
'720: Updated lock_text flag');
** Bug #4105040 Modified the update statement
** Added an extra condition on doc_article_version_id
*/
Update okc_terms_deviations_t odt set
compare_text_flag = 'Y'
where ref_article_version_id is NOT NULL
and doc_article_version_id <> ref_article_version_id
and deviation_code = 'ARTICLE_EXPIRED'
and sequence_id = p_sequence_id;
'710: Updated compare text flag for Expired Clauses');
FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'730: Leaving Update_Deviation_details : FND_API.G_EXC_UNEXPECTED_ERROR');
delete from okc_terms_deviations_t odt
where odt.scn_sequence+ ((1/10000) * odt.display_sequence) >
(select min(odt1.scn_sequence+ ((1/10000) * odt1.display_sequence))
from okc_terms_deviations_t odt1
where odt.deviation_category_priority=odt1.deviation_category_priority
and odt.doc_article_id = odt1.doc_article_id
and odt.sequence_id = odt1.sequence_id)
and odt.sequence_id = p_sequence_id;
SELECT OKC_TERMS_DEVIATIONS_S1.nextval INTO l_seq_id from DUAL;
SELECT
oab.id,
oab.SCN_ID,
okc_terms_util_pvt.get_section_label(scn_id),
oab.label,
oab.sav_sae_id,
oab.article_version_id,
oab.ref_article_id ,
oab.ref_article_version_id,
okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
oab.display_sequence,
oab.mandatory_YN,
oab.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_k_articles_b oab
WHERE document_type = p_doc_type
AND document_id = p_doc_id
AND NVL(summary_amend_operation_code,'NULL') <> 'DELETED'
AND EXISTS (SELECT 1 FROM okc_articles_all oka
WHERE oka.article_id = oab.sav_sae_id
AND standard_YN = 'N')
AND NOT EXISTS (SELECT 1 from okc_terms_deviations_temp odt
WHERE odt.article_id = oab.orig_article_id);
SELECT
oab.id,
oab.SCN_ID,
okc_terms_util_pvt.get_section_label(scn_id),
oab.label,
oab.sav_sae_id,
oab.article_version_id,
oab.ref_article_id ,
okc_terms_util_pvt.get_latest_tmpl_art_version_id(oab.ref_article_id, sysdate),
okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
oab.display_sequence,
oab.mandatory_YN,
oab.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_k_articles_b oab
WHERE ref_article_id is not null
AND ref_article_version_id is not null
AND document_id = p_doc_id
AND document_type = p_doc_type
AND nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
AND Exists ( Select 1 from okc_terms_deviations_temp odt where
oab.orig_article_id = odt.article_id ) ;
SELECT
otd.art_seq_id,
otd.SCN_ID,
okc_terms_util_pvt.get_section_name(otd.article_version_id, p_template_id),
otd.label,
otd.article_id,
otd.article_version_id,
otd.ref_article_id ,
otd.ref_article_version_id,
okc_terms_util_pvt.get_article_name(otd.article_id, otd.article_version_id),
otd.display_sequence,
otd.mandatory_flag,
otd.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_terms_deviations_temp otd
WHERE otd.source_flag = 'R'
AND x_include_exp = 'Y'
AND NOT EXISTS ( Select 1 from okc_k_articles_b oab
Where oab.document_type = p_doc_type
And oab.document_id = p_doc_id
And (oab.orig_article_id = otd.article_id
OR oab.sav_sae_id = otd.article_id)
And nvl(summary_amend_operation_code,'NULL') <> 'DELETED' );
SELECT
otd.art_seq_id,
otd.SCN_ID,
okc_terms_util_pvt.get_section_label(otd.scn_id),
otd.label,
otd.article_id,
otd.article_version_id,
otd.ref_article_id ,
otd.ref_article_version_id,
okc_terms_util_pvt.get_article_name(otd.article_id, otd.article_version_id),
otd.display_sequence,
otd.mandatory_flag,
otd.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_terms_deviations_temp otd
WHERE otd.source_flag = 'T'
AND otd.mandatory_flag = 'Y'
--Bug 4070733 Added condition to check for ammendement operation code
AND (NOT EXISTS ( Select 1 from okc_k_articles_b oab
Where oab.document_type = p_doc_type
And oab.document_id = p_doc_id
And (oab.orig_article_id = otd.article_id
--Bug 4077070
OR oab.sav_sae_id = otd.article_id )
And nvl(oab.summary_amend_operation_code,'NULL') <> 'DELETED'));
SELECT
otd.art_seq_id,
otd.SCN_ID,
okc_terms_util_pvt.get_section_label(otd.scn_id),
otd.label,
otd.article_id,
otd.article_version_id,
otd.ref_article_id ,
otd.ref_article_version_id,
okc_terms_util_pvt.get_article_name(otd.article_id, otd.article_version_id),
otd.display_sequence,
otd.mandatory_flag,
otd.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_terms_deviations_temp otd
WHERE otd.source_flag = 'T'
AND otd.mandatory_flag = 'N'
--Bug 4070733 Added condition to check for ammendement operation code
AND (NOT EXISTS ( Select 1 from okc_k_articles_b oab
Where oab.document_type = p_doc_type
And oab.document_id = p_doc_id
And (oab.orig_article_id = otd.article_id
--Bug 4077070
OR oab.sav_sae_id = otd.article_id )
AND nvl(oab.summary_amend_operation_code,'NULL') <> 'DELETED'));
SELECT
oab.id,
oab.SCN_ID,
okc_terms_util_pvt.get_section_label(scn_id),
oab.label,
oab.sav_sae_id,
oab.article_version_id,
oab.sav_sae_id, -- in case of expired clause,ref_art_id=art_id
okc_terms_util_pvt.get_latest_art_version_id(oab.sav_sae_id,p_doc_type,p_doc_id), --- Bug #4312185 [ Passing p_doc_type and p_doc_id, instead of 'TEMPLATE' and p_template_id ]
okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
oab.display_sequence,
oab.mandatory_YN,
oab.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_k_articles_b oab
WHERE document_id = p_doc_id
AND document_type = p_doc_type
AND nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
AND EXISTS (Select 1 from okc_article_versions oav
Where oav.article_id = oab.sav_sae_id
And oav.article_version_id = oab.article_version_id
And trunc(nvl(end_date, sysdate)) < trunc(sysdate));
SELECT
oab.id,
oab.SCN_ID,
okc_terms_util_pvt.get_section_label(scn_id),
oab.label,
oab.sav_sae_id,
oab.article_version_id,
oab.ref_article_id ,
oab.ref_article_version_id,
okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
oab.display_sequence,
oab.mandatory_YN,
oab.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_k_articles_b oab
WHERE document_id = p_doc_id
AND document_type = p_doc_type
and nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
AND EXISTS (Select 1 from okc_article_versions oav
Where oav.article_id = oab.sav_sae_id
And oav.article_version_id = oab.article_version_id
And oav.article_status = 'ON_HOLD')
AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
Where odt.doc_article_id = oab.sav_sae_id
And sequence_id = l_seq_id);
SELECT
oab.id,
oab.SCN_ID,
okc_terms_util_pvt.get_section_label(scn_id),
oab.label,
oab.sav_sae_id,
oab.article_version_id,
oab.ref_article_id ,
oab.ref_article_version_id,
okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
oab.display_sequence,
oab.mandatory_YN,
oab.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_k_articles_b oab
WHERE oab.source_flag = 'R'
AND document_id = p_doc_id
AND document_type = p_doc_type
AND nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
AND x_include_exp = 'Y'
AND NOT Exists ( Select 1 from okc_terms_deviations_temp odt
Where oab.orig_article_id = odt.article_id
And odt.source_flag = 'R')
AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
Where odt.doc_article_id = oab.sav_sae_id
And sequence_id = l_seq_id);
SELECT
oab.id,
oab.SCN_ID,
okc_terms_util_pvt.get_section_label(scn_id),
oab.label,
oab.sav_sae_id,
oab.article_version_id,
oab.ref_article_id ,
oab.ref_article_version_id,
okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
oab.display_sequence,
oab.mandatory_YN,
oab.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_k_articles_b oab
WHERE document_id = p_doc_id
AND document_type = p_doc_type
AND nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
AND EXISTS (Select 1 from okc_articles_all oaa
Where oaa.article_id = oab.sav_sae_id
And oaa.standard_yn = 'Y')
AND NOT EXISTS (Select 1 from okc_terms_deviations_temp
Where article_id = nvl(oab.orig_article_id,
oab.sav_sae_id))
-- Fix for bug# 4709359.
AND NOT EXISTS (Select 1 from okc_terms_deviations_temp
Where article_id = oab.sav_sae_id)
-- End of Fix for bug# 4709359.
AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
Where odt.doc_article_id = oab.sav_sae_id
And sequence_id = l_seq_id);
SELECT
oab.id,
oab.SCN_ID,
okc_terms_util_pvt.get_section_label(oab.scn_id),
oab.label,
oab.sav_sae_id,
oab.article_version_id,
odt.article_id ,
odt.article_version_id,
okc_terms_util_pvt.get_article_name(oab.sav_sae_id, oab.article_version_id),
oab.display_sequence,
oab.mandatory_YN,
oab.orig_article_id
BULK COLLECT INTO
l_art_seq_id,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id
FROM okc_k_articles_b oab, okc_terms_deviations_temp odt
WHERE oab.document_id = p_doc_id
AND oab.document_type = p_doc_type
AND oab.source_flag = odt.source_flag
AND nvl(oab.summary_amend_operation_code,'NULL') <> 'DELETED'
AND oab.ref_article_id is null
AND oab.ref_article_version_id is null
AND oab.orig_article_id = odt.article_id
AND EXISTS (select 1
from OKC_ARTICLE_RELATNS_ALL oar
where oar.source_article_id = odt.article_id -- currently on template
and oar.target_article_id = oab.sav_sae_id -- currently on document
and oar.relationship_type = 'ALTERNATE')
AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
Where odt.doc_article_id = oab.sav_sae_id
And sequence_id = l_seq_id);
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'430: Inserting Deviations in okc_terms_deviations_t ');
Insert_deviations(
x_return_status,
x_msg_data,
x_msg_count,
l_seq_id,
l_dev_cat.deviation_category,
l_dev_cat.deviation_code,
l_dev_cat.deviation_category_meaning,
l_dev_cat.deviation_code_meaning,
l_dev_cat.deviation_category_priority,
l_scn_id,
l_section_heading,
l_label,
l_doc_article_id,
l_doc_article_version_id,
l_ref_article_id,
l_ref_article_version_id,
l_article_title,
l_display_sequence,
l_mandatory_flag,
l_orig_article_id,
l_art_seq_id,
l_compare_flag,
p_doc_type,
p_doc_id);
/* Update the Section Details and Protected flag */
IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'440: Calling Update Deviation Details ');
Update_deviation_details(x_return_status, l_seq_id);
Procedure Insert_deviations(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_seq_id Number,
p_dev_category Varchar2,
p_dev_code Varchar2,
p_dev_category_meaning Varchar2,
p_dev_code_meaning Varchar2,
p_dev_category_priority Number,
p_scn_id scn_id_tbl,
p_section_heading section_heading_tbl,
p_label label_tbl,
p_doc_article_id article_id_tbl,
p_doc_article_version_id article_version_id_tbl,
p_ref_article_id ref_article_id_tbl,
p_ref_article_version_id ref_article_version_id_tbl,
p_article_title article_title_tbl,
p_display_sequence display_sequence_tbl,
p_mandatory_flag mandatory_flag_tbl,
p_orig_article_id orig_article_id_tbl,
p_art_seq_id art_seq_id_tbl,
p_compare_flag Varchar2,
p_doc_type Varchar2,
p_doc_id Number)
is
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_DEVIATIONS' ;
'500: Inserting Deviations in okc_terms_deviations_t ');
'510: Inserting Deviations in okc_terms_deviations_t ');
INSERT INTO okc_terms_deviations_t (
sequence_id,
deviation_category,
deviation_code,
deviation_category_meaning,
deviation_code_meaning,
deviation_category_priority,
scn_id,
section_heading,
label,
doc_article_id,
doc_article_version_id,
ref_article_id,
ref_article_version_id,
article_title,
display_sequence,
mandatory_flag,
compare_text_flag,
orig_article_id,
art_seq_id,
document_type,
document_id,
creation_date,
deviation_type)
VALUES (
p_seq_id,
p_dev_category,
p_dev_code,
p_dev_category_meaning,
p_dev_code_meaning,
p_dev_category_priority,
p_scn_id(i),
p_section_heading(i),
p_label(i),
p_doc_article_id(i),
p_doc_article_version_id(i),
p_ref_article_id(i),
p_ref_article_version_id(i),
p_article_title(i),
p_display_sequence(i),
p_mandatory_flag(i),
p_compare_flag,
p_orig_article_id(i),
p_art_seq_id(i),
p_doc_type,
p_doc_id,
sysdate,
'C'
);
'520: Inserted Deviations in okc_terms_deviations_t ');
'530: Leaving Insert_Deviations because of EXCEPTION: '||sqlerrm);
end Insert_deviations;
cursor del_csr is Select rowid from okc_terms_deviations_t
Where trunc(Creation_date) <= trunc(sysdate) - p_num_days
for update of doc_article_id nowait;
delete from okc_terms_deviations_t
where current of del_csr;
FND_FILE.PUT_LINE( FND_FILE.LOG,'Delete Records: '|| i);
select 'Y' 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 business_document_version = -99;