[Home] [Help]
136:
137: If (l_article_id.count > 0) then
138:
139: FORALL i IN l_scn_id.FIRST..l_scn_id.LAST
140: INSERT INTO okc_terms_deviations_temp (Scn_id,
141: article_id,
142: display_sequence,
143: mandatory_flag,
144: label,
258: '240: Inserting the data in temp table as count > 0');
259: END IF;
260:
261: FORALL i IN l_expert_articles_tbl.FIRST..l_expert_articles_tbl.LAST
262: INSERT INTO okc_terms_deviations_temp (
263: article_id,
264: article_version_id,
265: source_flag)
266: VALUES
369: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
370: '700: Entered UPDATE_DEVIATION_DETAILS');
371: END IF;
372: /*
373: Update okc_terms_deviations_t odt set
374: (odt.scn_label, odt.scn_sequence) = (select label, section_sequence from
375: okc_sections_b where id = odt.scn_id)
376: where sequence_id = p_sequence_id;
377: */
405: */
406:
407: -- updates the scn_sequence for the caluses which are exisiting on document
408:
409: Update okc_terms_deviations_t odt set
410: (odt.scn_label, odt.scn_sequence) =
411: (select osb1.label, decode(osb1.section_sequence,
412: osb2.section_sequence,osb1.section_sequence,
413: osb2.section_sequence+(1/10000)*osb1.section_sequence) -- Bug#4615605 replaced .0001 with 1/10000
421:
422: -- updates the scn_sequence for sections which are copied from template, but
423: -- the clauses are missing on the document.
424:
425: update okc_terms_deviations_t odt set
426: (odt.scn_label, odt.scn_sequence) = (select osb1.label,
427: decode(osb1.section_sequence,
428: osb2.section_sequence,osb1.section_sequence,
429: osb2.section_sequence+(1/10000)*osb1.section_sequence)
439: -- Now get the max scn_sequence which can be used to update the template
440: -- scn_sequence
441:
442: SELECT nvl(max(scn_sequence),0) INTO l_max_scn_seq
443: FROM okc_terms_deviations_t
444: WHERE sequence_id = p_sequence_id;
445:
446: -- updates the scn_sequence for the clauses which are on template but
447: -- not on the document
444: WHERE sequence_id = p_sequence_id;
445:
446: -- updates the scn_sequence for the clauses which are on template but
447: -- not on the document
448: Update okc_terms_deviations_t odt set
449: (odt.scn_label, odt.scn_sequence) =
450: (select osb1.label, to_number(decode(osb1.section_sequence,
451: osb2.section_sequence,osb1.section_sequence,
452: osb2.section_sequence+(1/10000)*osb1.section_sequence))
462: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
463: '710: Updated Section Label');
464: END IF;
465:
466: Update okc_terms_deviations_t odt set
467: lock_text_mod_flag = (select lock_text from
468: okc_article_versions
469: where article_version_id = odt.ref_article_version_id)
470: where sequence_id = p_sequence_id
480: ** Bug #4105040 Modified the update statement
481: ** Added an extra condition on doc_article_version_id
482: */
483:
484: Update okc_terms_deviations_t odt set
485: compare_text_flag = 'Y'
486: where ref_article_version_id is NOT NULL
487: and doc_article_version_id <> ref_article_version_id
488: and deviation_code = 'ARTICLE_EXPIRED'
528: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
529: '800: Entered REMOVE_DUPLICATE_DEVIATIONS');
530: END IF;
531:
532: delete from okc_terms_deviations_t odt
533: where odt.scn_sequence+ ((1/10000) * odt.display_sequence) >
534: (select min(odt1.scn_sequence+ ((1/10000) * odt1.display_sequence))
535: from okc_terms_deviations_t odt1
536: where odt.deviation_category_priority=odt1.deviation_category_priority
531:
532: delete from okc_terms_deviations_t odt
533: where odt.scn_sequence+ ((1/10000) * odt.display_sequence) >
534: (select min(odt1.scn_sequence+ ((1/10000) * odt1.display_sequence))
535: from okc_terms_deviations_t odt1
536: where odt.deviation_category_priority=odt1.deviation_category_priority
537: and odt.doc_article_id = odt1.doc_article_id
538: and odt.sequence_id = odt1.sequence_id)
539: and odt.sequence_id = p_sequence_id;
709: AND NVL(summary_amend_operation_code,'NULL') <> 'DELETED'
710: AND EXISTS (SELECT 1 FROM okc_articles_all oka
711: WHERE oka.article_id = oab.sav_sae_id
712: AND standard_YN = 'N')
713: AND NOT EXISTS (SELECT 1 from okc_terms_deviations_temp odt
714: WHERE odt.article_id = oab.orig_article_id);
715:
716:
717:
760: AND ref_article_version_id is not null
761: AND document_id = p_doc_id
762: AND document_type = p_doc_type
763: AND nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
764: AND Exists ( Select 1 from okc_terms_deviations_temp odt where
765: oab.orig_article_id = odt.article_id ) ;
766: l_compare_flag := 'Y';
767:
768: elsif l_dev_cat.deviation_code = 'MISSING_EXPERT_ARTICLE' then
802: l_article_title,
803: l_display_sequence,
804: l_mandatory_flag,
805: l_orig_article_id
806: FROM okc_terms_deviations_temp otd
807: WHERE otd.source_flag = 'R'
808: AND x_include_exp = 'Y'
809: AND NOT EXISTS ( Select 1 from okc_k_articles_b oab
810: Where oab.document_type = p_doc_type
854: l_article_title,
855: l_display_sequence,
856: l_mandatory_flag,
857: l_orig_article_id
858: FROM okc_terms_deviations_temp otd
859: WHERE otd.source_flag = 'T'
860: AND otd.mandatory_flag = 'Y'
861: --Bug 4070733 Added condition to check for ammendement operation code
862: AND (NOT EXISTS ( Select 1 from okc_k_articles_b oab
907: l_article_title,
908: l_display_sequence,
909: l_mandatory_flag,
910: l_orig_article_id
911: FROM okc_terms_deviations_temp otd
912: WHERE otd.source_flag = 'T'
913: AND otd.mandatory_flag = 'N'
914: --Bug 4070733 Added condition to check for ammendement operation code
915: AND (NOT EXISTS ( Select 1 from okc_k_articles_b oab
1019: AND EXISTS (Select 1 from okc_article_versions oav
1020: Where oav.article_id = oab.sav_sae_id
1021: And oav.article_version_id = oab.article_version_id
1022: And oav.article_status = 'ON_HOLD')
1023: AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
1024: Where odt.doc_article_id = oab.sav_sae_id
1025: And sequence_id = l_seq_id);
1026:
1027: l_compare_flag := 'N';
1068: AND document_id = p_doc_id
1069: AND document_type = p_doc_type
1070: AND nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
1071: AND x_include_exp = 'Y'
1072: AND NOT Exists ( Select 1 from okc_terms_deviations_temp odt
1073: Where oab.orig_article_id = odt.article_id
1074: And odt.source_flag = 'R')
1075: AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
1076: Where odt.doc_article_id = oab.sav_sae_id
1071: AND x_include_exp = 'Y'
1072: AND NOT Exists ( Select 1 from okc_terms_deviations_temp odt
1073: Where oab.orig_article_id = odt.article_id
1074: And odt.source_flag = 'R')
1075: AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
1076: Where odt.doc_article_id = oab.sav_sae_id
1077: And sequence_id = l_seq_id);
1078: l_compare_flag := 'N';
1079:
1122: AND nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
1123: AND EXISTS (Select 1 from okc_articles_all oaa
1124: Where oaa.article_id = oab.sav_sae_id
1125: And oaa.standard_yn = 'Y')
1126: AND NOT EXISTS (Select 1 from okc_terms_deviations_temp
1127: Where article_id = nvl(oab.orig_article_id,
1128: oab.sav_sae_id))
1129: -- Fix for bug# 4709359.
1130: AND NOT EXISTS (Select 1 from okc_terms_deviations_temp
1126: AND NOT EXISTS (Select 1 from okc_terms_deviations_temp
1127: Where article_id = nvl(oab.orig_article_id,
1128: oab.sav_sae_id))
1129: -- Fix for bug# 4709359.
1130: AND NOT EXISTS (Select 1 from okc_terms_deviations_temp
1131: Where article_id = oab.sav_sae_id)
1132: -- End of Fix for bug# 4709359.
1133: AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
1134: Where odt.doc_article_id = oab.sav_sae_id
1129: -- Fix for bug# 4709359.
1130: AND NOT EXISTS (Select 1 from okc_terms_deviations_temp
1131: Where article_id = oab.sav_sae_id)
1132: -- End of Fix for bug# 4709359.
1133: AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
1134: Where odt.doc_article_id = oab.sav_sae_id
1135: And sequence_id = l_seq_id);
1136:
1137: l_compare_flag := 'N';
1171: l_article_title,
1172: l_display_sequence,
1173: l_mandatory_flag,
1174: l_orig_article_id
1175: FROM okc_k_articles_b oab, okc_terms_deviations_temp odt
1176: WHERE oab.document_id = p_doc_id
1177: AND oab.document_type = p_doc_type
1178: AND oab.source_flag = odt.source_flag
1179: AND nvl(oab.summary_amend_operation_code,'NULL') <> 'DELETED'
1184: from OKC_ARTICLE_RELATNS_ALL oar
1185: where oar.source_article_id = odt.article_id -- currently on template
1186: and oar.target_article_id = oab.sav_sae_id -- currently on document
1187: and oar.relationship_type = 'ALTERNATE')
1188: AND NOT EXISTS (Select 1 from okc_terms_deviations_t odt
1189: Where odt.doc_article_id = oab.sav_sae_id
1190: And sequence_id = l_seq_id);
1191:
1192: l_compare_flag := 'Y';
1192: l_compare_flag := 'Y';
1193: end if;
1194:
1195: -- The deviations data has been collected into respective PL/SQL
1196: -- tables and then inserted into okc_terms_deviations_t table.
1197: -- Insert_deviatiosn procedure is called for this.
1198:
1199: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1200: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'430: Inserting Deviations in okc_terms_deviations_t ');
1196: -- tables and then inserted into okc_terms_deviations_t table.
1197: -- Insert_deviatiosn procedure is called for this.
1198:
1199: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1200: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'430: Inserting Deviations in okc_terms_deviations_t ');
1201: END IF;
1202:
1203: Insert_deviations(
1204: x_return_status,
1295: FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1296: end Generate_Terms_Deviations;
1297:
1298: -- this procedure will insert data into
1299: -- okc_terms_deviations_t table
1300:
1301: Procedure Insert_deviations(
1302: x_return_status OUT NOCOPY VARCHAR2,
1303: x_msg_data OUT NOCOPY VARCHAR2,
1329: begin
1330:
1331: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1332: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1333: '500: Inserting Deviations in okc_terms_deviations_t ');
1334: END IF;
1335:
1336: if p_doc_article_id.count > 0 then
1337:
1336: if p_doc_article_id.count > 0 then
1337:
1338: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1339: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1340: '510: Inserting Deviations in okc_terms_deviations_t ');
1341: END IF;
1342:
1343: FORALL i in p_doc_article_id.FIRST.. p_doc_article_id.LAST
1344: INSERT INTO okc_terms_deviations_t (
1340: '510: Inserting Deviations in okc_terms_deviations_t ');
1341: END IF;
1342:
1343: FORALL i in p_doc_article_id.FIRST.. p_doc_article_id.LAST
1344: INSERT INTO okc_terms_deviations_t (
1345: sequence_id,
1346: deviation_category,
1347: deviation_code,
1348: deviation_category_meaning,
1393: end if;
1394:
1395: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1396: FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1397: '520: Inserted Deviations in okc_terms_deviations_t ');
1398: END IF;
1399:
1400: x_return_status := G_RET_STS_SUCCESS;
1401:
1422: l_api_name CONSTANT VARCHAR2(30) := 'Purge_deviations_Data';
1423: l_return_status varchar2(1);
1424: i NUMBER;
1425:
1426: cursor del_csr is Select rowid from okc_terms_deviations_t
1427: Where trunc(Creation_date) <= trunc(sysdate) - p_num_days
1428: for update of doc_article_id nowait;
1429: begin
1430: SAVEPOINT purge_deviations_data;
1436:
1437: -- for each of the records selected in the above cursor
1438: -- issue a delete to purge the data
1439: For i in del_csr loop
1440: delete from okc_terms_deviations_t
1441: where current of del_csr;
1442: end loop;
1443: FND_FILE.PUT_LINE( FND_FILE.LOG,'Delete Records: '|| i);
1444: