[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_DEVIATIONS_PVT
Source
1 PACKAGE BODY OKC_TERMS_DEVIATIONS_PVT AS
2 /* $Header: OKCVTDRB.pls 120.4.12000000.3 2007/08/01 11:58:32 ndoddi ship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
8 ---------------------------------------------------------------------------
9 -- GLOBAL CONSTANTS
10 ---------------------------------------------------------------------------
11 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_DEVIATIONS_PVT';
12 G_MODULE CONSTANT VARCHAR2(200) := 'okc.plsql.'||G_PKG_NAME||'.';
13 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
14 G_UNASSIGNED_SECTION_CODE CONSTANT VARCHAR2(30) := 'UNASSIGNED';
15 ------------------------------------------------------------------------------
16 -- GLOBAL CONSTANTS
17 ------------------------------------------------------------------------------
18 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
19 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
20
21 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
22 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
23 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
24
25 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
26 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
27 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
28 ------------------------------------------------------------------------------
29 -- GLOBAL EXCEPTIONS
30 ------------------------------------------------------------------------------
31 E_Resource_Busy EXCEPTION;
32 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
33
34 /*
35 -- PROCEDURE Populate_Template_Articles
36 -- To be used to delete populate the global temp table with articles on
37 -- the current version of the template.
38 */
39 PROCEDURE Populate_Template_Articles (
40 x_return_status OUT NOCOPY VARCHAR2,
41 p_template_id IN NUMBER,
42 p_doc_type IN VARCHAR2
43 ) is
44 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_TEMPLATE_ARTICLES';
45 l_scn_id scn_id_tbl;
46 l_article_id article_id_tbl;
47 l_display_sequence display_sequence_tbl;
48 l_mandatory_flag mandatory_flag_tbl;
49 l_label label_tbl;
50 l_article_version_id article_version_id_tbl;
51 l_art_seq_id art_seq_id_tbl;
52 l_orig_article_id orig_article_id_tbl;
53
54 l_provision_allowed varchar2(1);
55 Begin
56
57 -- check if the document allows provisions, if not then the
58 -- provisions should not be copied into the table
59 --
60 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
61 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
62 '100: Entered POPULATE_TEMPLATE_ARTICLES');
63 END IF;
64
65 Select nvl(provision_allowed_YN,'N')
66 INTO l_provision_allowed
67 From okc_bus_doc_types_b
68 Where document_type = p_doc_type;
69
70
71 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
72 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
73 '110: Provision Allowed : '||l_provision_allowed);
74 END IF;
75
76
77 If (l_provision_allowed = 'Y') then
78
79 Select SCN_ID,
80 SAV_SAE_ID ,
81 Display_Sequence,
82 Mandatory_YN ,
83 Label,
84 okc_terms_util_pvt.get_latest_tmpl_art_version_id(sav_sae_id,
85 sysdate),
86 id,
87 sav_sae_id orig_article_id
88 BULK COLLECT INTO
89 l_scn_id,
90 l_article_id,
91 l_display_sequence,
92 l_mandatory_flag,
93 l_label,
94 l_article_version_id,
95 l_art_seq_id,
96 l_orig_article_id
97 From
98 okc_k_articles_b
99 Where document_Type = 'TEMPLATE'
100 And document_Id = p_template_id;
101
102 else -- provision_allowed = N
103
104 Select SCN_ID,
105 SAV_SAE_ID ,
106 Display_Sequence,
107 Mandatory_YN ,
108 Label,
109 okc_terms_util_pvt.get_latest_tmpl_art_version_id(sav_sae_id,
110 sysdate),
111 id,
112 sav_sae_id orig_article_id
113 BULK COLLECT INTO
114 l_scn_id,
115 l_article_id,
116 l_display_sequence,
117 l_mandatory_flag,
118 l_label,
119 l_article_version_id,
120 l_art_seq_id,
121 l_orig_article_id
122 From
123 okc_k_articles_b oab
124 Where document_Type = 'TEMPLATE'
125 And document_Id = p_template_id
126 And Exists (Select 1 From okc_article_versions oav
127 Where oab.sav_sae_id = oav.article_id
128 And oav.provision_yn = 'N');
129
130 end if; -- l_provision_allowed
131
132 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
133 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
134 '120: After DEVIATION CATEGORY Bulk Collect');
135 END IF;
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,
145 article_version_id,
146 source_flag,
147 orig_article_id)
148 VALUES
149 (l_scn_id(i),
150 l_article_id(i),
151 l_display_sequence(i),
152 l_mandatory_flag(i),
153 l_label(i),
154 l_article_version_id(i),
155 'T',
156 l_orig_article_id(i));
157
158 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
159 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
160 '130: Inserted TEMPLATE data in Global Temp Table');
161 END IF;
162 end if;
163
164 x_return_status := G_RET_STS_SUCCESS;
165
166 Exception
167
168 When OTHERS then
169
170 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
171 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_api_name,
172 '140: Leaving Populate_Teamplate_articles because of EXCEPTION: '||sqlerrm);
173 END IF;
174
175 x_return_status := G_RET_STS_UNEXP_ERROR;
176 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
177 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
178 END IF;
179
180 End Populate_Template_Articles;
181
182
183
184 /*
185 -- PROCEDURE Populate_Expert_Articles
186 -- To be used to delete populate the global temp table with articles on
187 -- the current version of the Expert.
188 -- Bug #4044354, removed the reference to l_document_type and
189 -- l_document_id which were not getting initialized and causing
190 -- expert BV to fail.
191 */
192 PROCEDURE Populate_Expert_Articles (
193 x_return_status OUT NOCOPY VARCHAR2,
194 p_document_type VARCHAR2,
195 p_document_id NUMBER,
196 p_include_exp OUT NOCOPY VARCHAR2,
197 p_seq_id NUMBER)
198 is
199 l_api_name CONSTANT VARCHAR2(30):='POPULATE_EXPERT_ARTICLES';
200 l_api_version NUMBER;
201 l_init_msg_list VARCHAR2(1);
202 l_bv_mode VARCHAR2(3) ;
203 l_qa_result_tbl OKC_TERMS_QA_GRP.qa_result_tbl_type;
204 l_expert_articles_tbl OKC_XPRT_UTIL_PVT.expert_articles_tbl_type;
205 l_return_status VARCHAR2(1);
206 l_msg_count NUMBER;
207 l_msg_data VARCHAR2(2000);
208
209 begin
210
211 l_init_msg_list := OKC_API.G_FALSE;
212 l_bv_mode := 'DEV';
213 l_api_version := 1;
214 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
215 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
216 '200: Entered POPULATE_EXPERT_ARTICLES');
217 END IF;
218
219
220 OKC_XPRT_UTIL_PVT.contract_expert_bv (
221 l_api_version,
222 l_init_msg_list,
223 p_document_id,
224 p_document_type,
225 l_bv_mode,
226 p_seq_id,
227 l_qa_result_tbl,
228 l_expert_articles_tbl,
229 l_return_status,
230 l_msg_count,
231 l_msg_data );
232
233 if (l_return_status = G_RET_STS_UNEXP_ERROR) then
234 p_include_exp := 'N';
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
236 end if;
237
238 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
239 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
240 '210: Return value of CONTRACT EXPRT BV is : '|| l_return_status);
241 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
242 '220: Value of p_include_exp is : '|| p_include_exp);
243 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
244 '230: The L_qa_results_tbl count is : '|| l_qa_result_tbl.count);
245 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
246 '230: The L_expert_articles_tbl count is : '|| l_expert_articles_tbl.count);
247
248 END IF;
249
250
251 if (l_return_status = G_RET_STS_SUCCESS) then
252 p_include_exp := 'Y';
253
254 if (l_expert_articles_tbl.count > 0) then
255
256 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
257 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
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
267 (l_expert_articles_tbl(i),
268 okc_terms_util_pvt.get_latest_tmpl_art_version_id(
269 l_expert_articles_tbl(i),
270 sysdate),
271 'R');
272
273 end if;
274 end if;
275
276 x_return_status := l_return_status;
277
278 Exception
279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
280 x_return_status := G_RET_STS_UNEXP_ERROR;
281 When OTHERS then
282
283 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
284 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, l_api_name,
285 '230: Leaving Populate_Expert_articles because of EXCEPTION: '||sqlerrm);
286 END IF;
287
288 x_return_status := G_RET_STS_UNEXP_ERROR;
289 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
290 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
291 END IF;
292 end Populate_Expert_Articles;
293
294 /*
295 -- PROCEDURE Generate_Terms_Deviations:
296 -- This API will be used to generate deviations
297 */
298 PROCEDURE Generate_Terms_Deviations (
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_data OUT NOCOPY VARCHAR2,
301 x_msg_count OUT NOCOPY NUMBER,
302
303 p_doc_type IN VARCHAR2,
304 p_doc_id IN NUMBER,
305 p_template_id IN NUMBER,
306 p_run_id OUT NOCOPY NUMBER
307 ) is
308
309
310 /* Bug #4105248
311 ** Made changes in the Cursor 'dev_cat' to fetch deviation
312 ** code priority. Modified a.tag to b.tag.
313 */
314
315 Cursor dev_cat is
316 SELECT a.lookup_code deviation_category,
317 b.lookup_code deviation_code,
318 a.meaning deviation_category_meaning,
319 b.meaning deviation_code_meaning,
320 b.tag deviation_category_priority -- Changed a.tag to b.tag
321 FROM fnd_lookup_values a, fnd_lookup_values b
322 WHERE a.lookup_code = b.lookup_type
323 AND a.lookup_type = 'OKC_TERMS_DEVIATION_CATEGORIES'
324 AND a.enabled_flag = b.enabled_flag
325 AND a.language = b.language
326 AND b.language = USERENV('LANG')
327 AND b.enabled_flag = 'Y'
328 ORDER BY b.tag; -- Changed a.tag to b.tag
329
330 l_dev_cat dev_cat%rowtype;
331 l_seq_id number;
332 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_TERMS_DEVIATIONS';
333
334
335
336 l_dev_category dev_category_tbl;
337 l_dev_code dev_code_tbl;
338 l_dev_category_meaning dev_category_meaning_tbl;
339 l_dev_code_meaning dev_code_tbl;
340 l_scn_id scn_id_tbl;
341 l_section_heading section_heading_tbl;
342 l_label label_tbl;
343 l_doc_article_id article_id_tbl;
344 l_doc_article_version_id article_version_id_tbl;
345 l_ref_article_id ref_article_id_tbl;
346 l_ref_article_version_id ref_article_version_id_tbl;
347 l_article_title article_title_tbl;
348 l_display_sequence display_sequence_tbl;
349 l_mandatory_flag mandatory_flag_tbl;
350 l_orig_article_id orig_article_id_tbl;
351 l_art_seq_id art_seq_id_tbl;
352 l_contract_source varchar2(30);
353 l_compare_flag varchar2(1) ;
354 l_xprt_enabled varchar2(1);
355 x_include_exp varchar2(1) ;
356 l_init_msg_list varchar2(1);
357 l_api_version number;
358
359
360 procedure Update_deviation_details(x_return_status OUT NOCOPY VARCHAR2,
361 p_sequence_id IN NUMBER)
362 is
363 l_api_name CONSTANT Varchar2(60):='UPDATE_DEVIATIONS_DETAILS';
364 l_max_scn_seq Number;
365
366 begin
367
368 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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 */
378
379 /* bug #4057194
380 ** The following is the logic for updating the section label and section
381 ** sequence
382 ** update the scn_sequence for the section/clause which is currently
383 ** on document i.e, deviations for the clauses existing on document.
384 ** update the scn_sequence for the section/clause which is missing
385 ** on document but is from template.
386 ** update the scn_sequence for section/clause which is present on the
387 ** template but is not existing on document.
388 ** There is additional logic to handle sections within section, the
389 ** order is achieved by the following:
390 ** Ex: seq section
391 ** 10 S1
392 ** 20 |->S2 (child section)
393 ** 20 S3 (here the parent section will be based
394 ** on prev parent)
395 ** 30 S4
396 ** In order to get the correct section sequence the child section
397 ** sequence generated as parent scn seq + child scn seq * 0.0001
398 ** so in the case the final section sequence will be
399 ** Ex: seq section
400 ** 10 S1
401 ** 10.002 |->S2 (child section)
402 ** 20 S3 (here the parent section will be based
403 ** on prev parent)
404 ** 30 S4
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
414 from okc_sections_b osb1, okc_sections_b osb2
415 where nvl(osb1.scn_id,osb1.id) = osb2.id
416 and osb1.document_type = odt.document_type
417 and osb1.document_id = odt.document_id
418 and osb1.id = odt.scn_id)
419 where sequence_id = p_sequence_id;
420
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)
430 from okc_sections_b osb1, okc_sections_b osb2, okc_sections_b osb3
431 where nvl(osb1.scn_id,osb1.id) = osb2.id
432 and osb1.document_type = odt.document_type
433 and osb1.document_id = odt.document_id
434 and osb3.id = odt.scn_id
435 and osb3.id = to_number(osb1.orig_system_reference_id1))
436 where sequence_id = p_sequence_id
437 and scn_sequence is null;
438
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
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))
453 + l_max_scn_seq
454 from okc_sections_b osb1, okc_sections_b osb2
455 where nvl(osb1.scn_id,osb1.id) = osb2.id
456 and osb1.document_type = 'TEMPLATE'
457 and osb1.id = odt.scn_id)
458 where sequence_id = p_sequence_id
459 and scn_sequence is null;
460
461 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
471 and deviation_code = 'MODIFIED_STD';
472
473 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
474 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
475 '720: Updated lock_text flag');
476 END IF;
477
478
479 /*
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'
489 and sequence_id = p_sequence_id;
490
491
492 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
493 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
494 '710: Updated compare text flag for Expired Clauses');
495 END IF;
496
497 x_return_status := G_RET_STS_SUCCESS;
498
499 EXCEPTION
500 When OTHERS then
501
502 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
503 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'730: Leaving Update_Deviation_details : FND_API.G_EXC_UNEXPECTED_ERROR');
504 END IF;
505 x_return_status := G_RET_STS_UNEXP_ERROR;
506 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
507 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
508 END IF;
509 end;
510
511 /*
512 ** Bug #4087224
513 ** If the same clause is existing multiple times
514 ** in same or different sections and having same deviation, then this was
515 ** getting reported multiple times, this was because the clauses are
516 ** fetched based on deviations. Removing the duplicate deviations in
517 ** this procedure.
518 */
519
520 procedure remove_duplicate_deviations (x_return_status OUT NOCOPY VARCHAR2,
521 p_sequence_id IN NUMBER)
522 is
523 l_api_name CONSTANT Varchar2(60):='REMOVE_DUPLICATE_DEVIATIONS';
524
525 begin
526
527 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
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
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;
540
541 x_return_status := G_RET_STS_SUCCESS;
542
543 EXCEPTION
544 When OTHERS then
545
546 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'810: Leaving remove_duplicate_deviations : FND_API.G_EXC_UNEXPECTED_ERROR');
548 END IF;
549 x_return_status := G_RET_STS_UNEXP_ERROR;
550 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
551 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
552 END IF;
553
554 end;
555
556
557
558 Begin
559
560 l_compare_flag := 'N';
561 l_xprt_enabled := 'N';
562 x_include_exp := 'N';
563 l_init_msg_list := FND_API.G_FALSE;
564 l_api_version := 1;
565
566 FND_MSG_PUB.initialize;
567 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
568 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
569 '300: Entered GENERATE_DEVIATIONS');
570 END IF;
571 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
572 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
573 '305: Calling populate template articles');
574 END IF;
575
576 populate_template_articles(x_return_status, p_template_id, p_doc_type);
577
578 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
579 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
580 '310: return status for populate_template_articles is : '|| x_return_status);
581 END IF;
582
583 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
585 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
586 RAISE FND_API.G_EXC_ERROR ;
587 END IF;
588
589 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
590 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
591 '312: Checking If expert is enabled on document');
592 END IF;
593
594 /*
595 ** Bug #4115488, Added code to check if the document is CE enabled
596 ** using the same logic used for displaying the 'Use Contract Expert'
597 ** button on the document. This will take care of the scenarios where
598 ** the template is enabled/disabled on revisions.
599 */
600
601 OKC_XPRT_UTIL_PVT.enable_expert_button
602 (
603 l_api_version,
604 l_init_msg_list,
605 p_template_id,
606 p_doc_id,
607 p_doc_type,
608 l_xprt_enabled, -- FND_API.G_FALSE or G_TRUE
609 x_return_status,
610 x_msg_count,
611 x_msg_data
612 );
613
614 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
615 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
616 '314: XPRT Enabled on Template: '||l_xprt_enabled);
617 END IF;
618
619 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
621 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
622 RAISE FND_API.G_EXC_ERROR ;
623 END IF;
624
625 SELECT OKC_TERMS_DEVIATIONS_S1.nextval INTO l_seq_id from DUAL;
626
627
628 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
629 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
630 '316: Generated Sequence Number is l_seq_id: '|| l_seq_id);
631 END IF;
632
633 -- Bug #4115488
634
635 if (l_xprt_enabled = FND_API.G_TRUE) then
636
637 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
638 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
639 '318: Calling populate_expert_articles');
640 END IF;
641
642 Populate_Expert_Articles( x_return_status,
643 p_doc_type,
644 p_doc_id,
645 x_include_exp,
646 l_seq_id); --Policy Deviations Change:Passing sequence Id
647
648 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
649 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
650 '320: return status for populate_expert_articles is : '|| x_return_status);
651 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
652 '320: value of x_include_exp is: '|| x_include_exp);
653 END IF;
654
655 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
657 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
658 RAISE FND_API.G_EXC_ERROR ;
659 END IF;
660
661 end if; -- end of xprt_enabled
662
663 open dev_cat;
664 loop
665 Fetch dev_cat into l_dev_cat;
666 exit when dev_cat%NOTFOUND;
667
668 If l_dev_cat.deviation_code = 'ADDED_NON_STD' then
669 /*
670 ** considers all the non-std clauses currently
671 ** existing on the document but no originated
672 ** from either Template or Expert
673 ** Bug 4044354 Replaced ref_article_id with orig_article_id
674 */
675 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
676 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
677 '330: Generating deviations for Added Non Std Clause');
678 END IF;
679
680 SELECT
681 oab.id,
682 oab.SCN_ID,
683 okc_terms_util_pvt.get_section_label(scn_id),
684 oab.label,
685 oab.sav_sae_id,
686 oab.article_version_id,
687 oab.ref_article_id ,
688 oab.ref_article_version_id,
689 okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
690 oab.display_sequence,
691 oab.mandatory_YN,
692 oab.orig_article_id
693 BULK COLLECT INTO
694 l_art_seq_id,
695 l_scn_id,
696 l_section_heading,
697 l_label,
698 l_doc_article_id,
699 l_doc_article_version_id,
700 l_ref_article_id,
701 l_ref_article_version_id,
702 l_article_title,
703 l_display_sequence,
704 l_mandatory_flag,
705 l_orig_article_id
706 FROM okc_k_articles_b oab
707 WHERE document_type = p_doc_type
708 AND document_id = p_doc_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
718 l_compare_flag := 'N';
719
720 elsif l_dev_cat.deviation_code = 'MODIFIED_STD' then
721 /*
722 ** considers clauses which are modified to non-std
723 ** and originated from Tempalte or Expert (i.e,
724 ** source_flag is NOT NULL
725 ** Bug 4044354 Replaced ref_article_id with orig_article_id
726 */
727 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
728 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
729 '340: Generating Deviations deviations for Modified Std Clause ');
730 END IF;
731
732 SELECT
733 oab.id,
734 oab.SCN_ID,
735 okc_terms_util_pvt.get_section_label(scn_id),
736 oab.label,
737 oab.sav_sae_id,
738 oab.article_version_id,
739 oab.ref_article_id ,
740 okc_terms_util_pvt.get_latest_tmpl_art_version_id(oab.ref_article_id, sysdate),
741 okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
742 oab.display_sequence,
743 oab.mandatory_YN,
744 oab.orig_article_id
745 BULK COLLECT INTO
746 l_art_seq_id,
747 l_scn_id,
748 l_section_heading,
749 l_label,
750 l_doc_article_id,
751 l_doc_article_version_id,
752 l_ref_article_id,
753 l_ref_article_version_id,
754 l_article_title,
755 l_display_sequence,
756 l_mandatory_flag,
757 l_orig_article_id
758 FROM okc_k_articles_b oab
759 WHERE ref_article_id is not null
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
769 /*
770 ** will consider the clauses suggested by expert but are
771 ** missing on the document. Also, considers the clauses
772 ** suggested by new rules, if any.
773 ** Bug #4044354 Replaced ref_article_id with orig_article_id
774 */
775 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
776 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
777 '350: Generating Deviations for Missing Exp Clause ');
778 END IF;
779
780 SELECT
781 otd.art_seq_id,
782 otd.SCN_ID,
783 okc_terms_util_pvt.get_section_name(otd.article_version_id, p_template_id),
784 otd.label,
785 otd.article_id,
786 otd.article_version_id,
787 otd.ref_article_id ,
788 otd.ref_article_version_id,
789 okc_terms_util_pvt.get_article_name(otd.article_id, otd.article_version_id),
790 otd.display_sequence,
791 otd.mandatory_flag,
792 otd.orig_article_id
793 BULK COLLECT INTO
794 l_art_seq_id,
795 l_scn_id,
796 l_section_heading,
797 l_label,
798 l_doc_article_id,
799 l_doc_article_version_id,
800 l_ref_article_id,
801 l_ref_article_version_id,
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
811 And oab.document_id = p_doc_id
812 And (oab.orig_article_id = otd.article_id
813 OR oab.sav_sae_id = otd.article_id)
814 And nvl(summary_amend_operation_code,'NULL') <> 'DELETED' );
815
816 l_compare_flag := 'N';
817
818 elsif l_dev_cat.deviation_code = 'MISSING_MANDATORY' then
819
820 /*
821 ** will consider only std. mandatory clauses missing from
822 ** the document(which are NOTmodfied)
823 ** Also, considers any new mandatory clause added to the
824 ** template in the latest version
825 */
826 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
827 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
828 '360: Generating Deviations for Missing Mandatory Clause ');
829 END IF;
830
831
832 SELECT
833 otd.art_seq_id,
834 otd.SCN_ID,
835 okc_terms_util_pvt.get_section_label(otd.scn_id),
836 otd.label,
837 otd.article_id,
838 otd.article_version_id,
839 otd.ref_article_id ,
840 otd.ref_article_version_id,
841 okc_terms_util_pvt.get_article_name(otd.article_id, otd.article_version_id),
842 otd.display_sequence,
843 otd.mandatory_flag,
844 otd.orig_article_id
845 BULK COLLECT INTO
846 l_art_seq_id,
847 l_scn_id,
848 l_section_heading,
849 l_label,
850 l_doc_article_id,
851 l_doc_article_version_id,
852 l_ref_article_id,
853 l_ref_article_version_id,
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
863 Where oab.document_type = p_doc_type
864 And oab.document_id = p_doc_id
865 And (oab.orig_article_id = otd.article_id
866 --Bug 4077070
867 OR oab.sav_sae_id = otd.article_id )
868 And nvl(oab.summary_amend_operation_code,'NULL') <> 'DELETED'));
869
870 l_compare_flag := 'N';
871
872 elsif l_dev_cat.deviation_code = 'MISSING_OPTIONAL_ARTICLE' then
873 /*
874 ** considers the standard clauses which were removed
875 ** from the document but are existing on the template
876 ** Also, considers any new clauses added to the template
877 ** after it has been instantiated on the document (i.e,
878 ** in the latest version of the template
879 */
880 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
881 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
882 '370: Generating Deviations for Missing Optional Clause ');
883 END IF;
884
885 SELECT
886 otd.art_seq_id,
887 otd.SCN_ID,
888 okc_terms_util_pvt.get_section_label(otd.scn_id),
889 otd.label,
890 otd.article_id,
891 otd.article_version_id,
892 otd.ref_article_id ,
893 otd.ref_article_version_id,
894 okc_terms_util_pvt.get_article_name(otd.article_id, otd.article_version_id),
895 otd.display_sequence,
896 otd.mandatory_flag,
897 otd.orig_article_id
898 BULK COLLECT INTO
899 l_art_seq_id,
900 l_scn_id,
901 l_section_heading,
902 l_label,
903 l_doc_article_id,
904 l_doc_article_version_id,
905 l_ref_article_id,
906 l_ref_article_version_id,
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
916 Where oab.document_type = p_doc_type
917 And oab.document_id = p_doc_id
918 And (oab.orig_article_id = otd.article_id
919 --Bug 4077070
920 OR oab.sav_sae_id = otd.article_id )
921 AND nvl(oab.summary_amend_operation_code,'NULL') <> 'DELETED'));
922
923 l_compare_flag := 'N';
924
925 elsif l_dev_cat.deviation_code = 'ARTICLE_EXPIRED' then
926
927 /*
928 ** This will be irrespecitve of Source (Template, Expert or
929 ** directly added to the document), if the article is present
930 ** on the document and is Expired, it will be reported
931 ** This check is made against all the clauses on the document
932 ** Get_latest_version)id will return either a latest version or
933 ** null
934 */
935
936 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
937 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
938 '380: Generating Deviations for Clause Expired ');
939 END IF;
940 SELECT
941 oab.id,
942 oab.SCN_ID,
943 okc_terms_util_pvt.get_section_label(scn_id),
944 oab.label,
945 oab.sav_sae_id,
946 oab.article_version_id,
947 oab.sav_sae_id, -- in case of expired clause,ref_art_id=art_id
948 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 ]
949 okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
950 oab.display_sequence,
951 oab.mandatory_YN,
952 oab.orig_article_id
953 BULK COLLECT INTO
954 l_art_seq_id,
955 l_scn_id,
956 l_section_heading,
957 l_label,
958 l_doc_article_id,
959 l_doc_article_version_id,
960 l_ref_article_id,
961 l_ref_article_version_id,
962 l_article_title,
963 l_display_sequence,
964 l_mandatory_flag,
965 l_orig_article_id
966 FROM okc_k_articles_b oab
967 WHERE document_id = p_doc_id
968 AND document_type = p_doc_type
969 AND nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
970 AND EXISTS (Select 1 from okc_article_versions oav
971 Where oav.article_id = oab.sav_sae_id
972 And oav.article_version_id = oab.article_version_id
973 And trunc(nvl(end_date, sysdate)) < trunc(sysdate));
974 l_compare_flag := 'N';
975
976 elsif l_dev_cat.deviation_code = 'ARTICLE_ON_HOLD' then
977
978 /*
979 ** This will be irrespecitve of Source (Template, Expert or
980 ** directly added to the document), if the article is present
981 ** on the document and is on-hold, it will be reported.
982 ** This check is made against all the clauses on the document
983 */
984 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
985 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
986 '390: Generating Deviations for Clause on hold ');
987 END IF;
988
989 SELECT
990 oab.id,
991 oab.SCN_ID,
992 okc_terms_util_pvt.get_section_label(scn_id),
993 oab.label,
994 oab.sav_sae_id,
995 oab.article_version_id,
996 oab.ref_article_id ,
997 oab.ref_article_version_id,
998 okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
999 oab.display_sequence,
1000 oab.mandatory_YN,
1001 oab.orig_article_id
1002 BULK COLLECT INTO
1003 l_art_seq_id,
1004 l_scn_id,
1005 l_section_heading,
1006 l_label,
1007 l_doc_article_id,
1008 l_doc_article_version_id,
1009 l_ref_article_id,
1010 l_ref_article_version_id,
1011 l_article_title,
1012 l_display_sequence,
1013 l_mandatory_flag,
1014 l_orig_article_id
1015 FROM okc_k_articles_b oab
1016 WHERE document_id = p_doc_id
1017 AND document_type = p_doc_type
1018 and nvl(summary_amend_operation_code,'NULL') <> 'DELETED'
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';
1028
1029 elsif l_dev_cat.deviation_code = 'EXPERT_ARTICLE_NOT_REQUIRED' then
1030 /*
1031 ** will consider the clauses that were previously suggested
1032 ** by Expert but are not in the current run
1033 ** Bug #4044354 Replaced ref_article_id with orig_article_id
1034 */
1035 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1036 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1037 '400: Generating Deviations for Exp recommending to remove ');
1038 END IF;
1039
1040 SELECT
1041 oab.id,
1042 oab.SCN_ID,
1043 okc_terms_util_pvt.get_section_label(scn_id),
1044 oab.label,
1045 oab.sav_sae_id,
1046 oab.article_version_id,
1047 oab.ref_article_id ,
1048 oab.ref_article_version_id,
1049 okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
1050 oab.display_sequence,
1051 oab.mandatory_YN,
1052 oab.orig_article_id
1053 BULK COLLECT INTO
1054 l_art_seq_id,
1055 l_scn_id,
1056 l_section_heading,
1057 l_label,
1058 l_doc_article_id,
1059 l_doc_article_version_id,
1060 l_ref_article_id,
1061 l_ref_article_version_id,
1062 l_article_title,
1063 l_display_sequence,
1064 l_mandatory_flag,
1065 l_orig_article_id
1066 FROM okc_k_articles_b oab
1067 WHERE oab.source_flag = 'R'
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
1077 And sequence_id = l_seq_id);
1078 l_compare_flag := 'N';
1079
1080 elsif l_dev_cat.deviation_code = 'ADDED_STD_ARTICLE' then
1081
1082 /*
1083 ** will consider clauses with source_flag as null and the clause
1084 ** is std. OR the clause is missing on the template.
1085 ** will not consider Expert related clauses
1086 */
1087
1088 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1089 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,
1090 '410: Generating Deviation for Added Std Clause ');
1091 END IF;
1092
1093 SELECT
1094 oab.id,
1095 oab.SCN_ID,
1096 okc_terms_util_pvt.get_section_label(scn_id),
1097 oab.label,
1098 oab.sav_sae_id,
1099 oab.article_version_id,
1100 oab.ref_article_id ,
1101 oab.ref_article_version_id,
1102 okc_terms_util_pvt.get_article_name(sav_sae_id, article_version_id),
1103 oab.display_sequence,
1104 oab.mandatory_YN,
1105 oab.orig_article_id
1106 BULK COLLECT INTO
1107 l_art_seq_id,
1108 l_scn_id,
1109 l_section_heading,
1110 l_label,
1111 l_doc_article_id,
1112 l_doc_article_version_id,
1113 l_ref_article_id,
1114 l_ref_article_version_id,
1115 l_article_title,
1116 l_display_sequence,
1117 l_mandatory_flag,
1118 l_orig_article_id
1119 FROM okc_k_articles_b oab
1120 WHERE document_id = p_doc_id
1121 AND document_type = p_doc_type
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
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';
1138
1139 elsif l_dev_cat.deviation_code = 'REPLACED_ALT' then
1140
1141 /*
1142 ** considers the clauses suggested by template or expert
1143 ** and have replaced with available alternate clauses.
1144 */
1145 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1146 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'420: Generating Deviations for Replace Clause with Alternate ');
1147 END IF;
1148
1149 SELECT
1150 oab.id,
1151 oab.SCN_ID,
1152 okc_terms_util_pvt.get_section_label(oab.scn_id),
1153 oab.label,
1154 oab.sav_sae_id,
1155 oab.article_version_id,
1156 odt.article_id ,
1157 odt.article_version_id,
1158 okc_terms_util_pvt.get_article_name(oab.sav_sae_id, oab.article_version_id),
1159 oab.display_sequence,
1160 oab.mandatory_YN,
1161 oab.orig_article_id
1162 BULK COLLECT INTO
1163 l_art_seq_id,
1164 l_scn_id,
1165 l_section_heading,
1166 l_label,
1167 l_doc_article_id,
1168 l_doc_article_version_id,
1169 l_ref_article_id,
1170 l_ref_article_version_id,
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'
1180 AND oab.ref_article_id is null
1181 AND oab.ref_article_version_id is null
1182 AND oab.orig_article_id = odt.article_id
1183 AND EXISTS (select 1
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';
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 ');
1201 END IF;
1202
1203 Insert_deviations(
1204 x_return_status,
1205 x_msg_data,
1206 x_msg_count,
1207
1208 l_seq_id,
1209 l_dev_cat.deviation_category,
1210 l_dev_cat.deviation_code,
1211 l_dev_cat.deviation_category_meaning,
1212 l_dev_cat.deviation_code_meaning,
1213 l_dev_cat.deviation_category_priority,
1214 l_scn_id,
1215 l_section_heading,
1216 l_label,
1217 l_doc_article_id,
1218 l_doc_article_version_id,
1219 l_ref_article_id,
1220 l_ref_article_version_id,
1221 l_article_title,
1222 l_display_sequence,
1223 l_mandatory_flag,
1224 l_orig_article_id,
1225 l_art_seq_id,
1226 l_compare_flag,
1227 p_doc_type,
1228 p_doc_id);
1229
1230 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1232 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1233 RAISE FND_API.G_EXC_ERROR ;
1234 END IF;
1235
1236 end loop;
1237
1238 close dev_cat;
1239
1240 /* Update the Section Details and Protected flag */
1241 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1242 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'440: Calling Update Deviation Details ');
1243 END IF;
1244
1245 Update_deviation_details(x_return_status, l_seq_id);
1246 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1248 End if;
1249
1250 Remove_duplicate_deviations(x_return_status, l_seq_id);
1251 If (x_return_status = G_RET_STS_UNEXP_ERROR) then
1252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1253 End if;
1254
1255 p_run_id := l_seq_id;
1256
1257 x_return_status := G_RET_STS_SUCCESS;
1258 commit;
1259
1260 EXCEPTION
1261 WHEN FND_API.G_EXC_ERROR THEN
1262 IF (dev_cat%ISOPEN) THEN
1263 CLOSE dev_cat;
1264 END IF;
1265
1266 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1267 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'100: Leaving Generate_deviations :FND_API.G_EXC_ERROR');
1268 END IF;
1269 x_return_status := G_RET_STS_ERROR ;
1270
1271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1272 IF (dev_cat%ISOPEN) THEN
1273 CLOSE dev_cat;
1274 END IF;
1275
1276 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1277 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'200: Leaving Generate_Deviations : FND_API.G_EXC_UNEXPECTED_ERROR');
1278 END IF;
1279 x_return_status := G_RET_STS_UNEXP_ERROR ;
1280 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1281
1282 WHEN OTHERS THEN
1283 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1284 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'300: Leaving Generate_Deviations because of EXCEPTION: '||sqlerrm);
1285 END IF;
1286
1287 IF (dev_cat%ISOPEN) THEN
1288 CLOSE dev_cat;
1289 END IF;
1290
1291 x_return_status := G_RET_STS_UNEXP_ERROR ;
1292 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1293 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1294 END IF;
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,
1304 x_msg_count OUT NOCOPY NUMBER,
1305
1306 p_seq_id Number,
1307 p_dev_category Varchar2,
1308 p_dev_code Varchar2,
1309 p_dev_category_meaning Varchar2,
1310 p_dev_code_meaning Varchar2,
1311 p_dev_category_priority Number,
1312 p_scn_id scn_id_tbl,
1313 p_section_heading section_heading_tbl,
1314 p_label label_tbl,
1315 p_doc_article_id article_id_tbl,
1316 p_doc_article_version_id article_version_id_tbl,
1317 p_ref_article_id ref_article_id_tbl,
1318 p_ref_article_version_id ref_article_version_id_tbl,
1319 p_article_title article_title_tbl,
1320 p_display_sequence display_sequence_tbl,
1321 p_mandatory_flag mandatory_flag_tbl,
1322 p_orig_article_id orig_article_id_tbl,
1323 p_art_seq_id art_seq_id_tbl,
1324 p_compare_flag Varchar2,
1325 p_doc_type Varchar2,
1326 p_doc_id Number)
1327 is
1328 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_DEVIATIONS' ;
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
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 (
1345 sequence_id,
1346 deviation_category,
1347 deviation_code,
1348 deviation_category_meaning,
1349 deviation_code_meaning,
1350 deviation_category_priority,
1351 scn_id,
1352 section_heading,
1353 label,
1354 doc_article_id,
1355 doc_article_version_id,
1356 ref_article_id,
1357 ref_article_version_id,
1358 article_title,
1359 display_sequence,
1360 mandatory_flag,
1361 compare_text_flag,
1362 orig_article_id,
1363 art_seq_id,
1364 document_type,
1365 document_id,
1366 creation_date,
1367 deviation_type)
1368 VALUES (
1369 p_seq_id,
1370 p_dev_category,
1371 p_dev_code,
1372 p_dev_category_meaning,
1373 p_dev_code_meaning,
1374 p_dev_category_priority,
1375 p_scn_id(i),
1376 p_section_heading(i),
1377 p_label(i),
1378 p_doc_article_id(i),
1379 p_doc_article_version_id(i),
1380 p_ref_article_id(i),
1381 p_ref_article_version_id(i),
1382 p_article_title(i),
1383 p_display_sequence(i),
1384 p_mandatory_flag(i),
1385 p_compare_flag,
1386 p_orig_article_id(i),
1387 p_art_seq_id(i),
1388 p_doc_type,
1389 p_doc_id,
1390 sysdate,
1391 'C'
1392 );
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
1402 EXCEPTION
1403 WHEN OTHERS Then
1404
1405 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1406 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,
1407 '530: Leaving Insert_Deviations because of EXCEPTION: '||sqlerrm);
1408 END IF;
1409 x_return_status := G_RET_STS_UNEXP_ERROR;
1410 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1411 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1412 END IF;
1413 end Insert_deviations;
1414
1415
1416 Procedure Purge_Deviations_Data(
1417 errbuf OUT NOCOPY VARCHAR2,
1418 retcode OUT NOCOPY VARCHAR2,
1419 p_num_days IN NUMBER )
1420
1421 is
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;
1431 l_return_status := G_RET_STS_SUCCESS;
1432 FND_MSG_PUB.initialize;
1433
1434 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_num_days: '||p_num_days);
1435 FND_FILE.PUT_LINE( FND_FILE.LOG,'Entered into Purge Deviations');
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
1445 retcode := 0;
1446
1447 FND_FILE.PUT_LINE( FND_FILE.LOG,'Return Code of the Program: '|| retcode);
1448
1449 EXCEPTION
1450
1451 WHEN E_RESOURCE_BUSY then
1452 l_return_status := G_RET_STS_ERROR;
1453 retcode := 1;
1454 ROLLBACK TO purge_deviations_data;
1455 FND_FILE.PUT_LINE( FND_FILE.LOG,'Return Code of the Program: '|| retcode);
1456 FND_FILE.PUT_LINE(FND_FILE.LOG,'Resource busy exception');
1457 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1458
1459 WHEN OTHERS Then
1460 l_return_status := G_RET_STS_UNEXP_ERROR;
1461 errbuf := substr(sqlerrm,1,200);
1462 retcode := 2;
1463 FND_FILE.PUT_LINE( FND_FILE.LOG,'Return Code of the Program: '|| retcode);
1464 ROLLBACK TO purge_deviations_data;
1465
1466 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1467 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED ,l_api_name,'660: Other exception');
1468 END IF;
1469 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception Others: '|| sqlerrm);
1470 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1471 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1472 END IF;
1473
1474
1475 end purge_deviations_data;
1476
1477 -- Returns 'Y' if deviations report document has been generated and attached
1478 -- to the business document. 'N' if not.
1479
1480 FUNCTION has_deviation_report(
1481 p_document_type IN VARCHAR2,
1482 p_document_id IN NUMBER
1483 ) RETURN VARCHAR2 IS
1484 l_api_name CONSTANT VARCHAR2(30) := 'has_deviation_report';
1485 CURSOR doc_details_csr IS
1486 select 'Y' from okc_contract_docs_details_vl
1487 where business_document_id = p_document_id
1488 and business_document_type = p_document_type
1489 and category_code = 'OKC_REPO_APPROVAL_ABSTRACT'
1490 and business_document_version = -99;
1491
1492 l_result VARCHAR2(1);
1493
1494 BEGIN
1495 l_result := '?';
1496
1497 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1498 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'1800: Entering has_deviation_report ');
1499 END IF;
1500
1501 OPEN doc_details_csr ;
1502 FETCH doc_details_csr into l_result;
1503 CLOSE doc_details_csr ;
1504
1505 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1506 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE, G_MODULE||l_api_name,'2000: Result has_deviation_report : ['||l_result||']');
1507 END IF;
1508
1509 IF l_result = 'Y' THEN
1510 RETURN 'Y';
1511 ELSE
1512 RETURN 'N';
1513 END IF;
1514
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 IF ( FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1518 FND_LOG.STRING( FND_LOG.LEVEL_EXCEPTION, G_MODULE||l_api_name,'2000: Leaving has_deviation_report of EXCEPTION: '||sqlerrm);
1519 END IF;
1520 RETURN 'E';
1521 END has_deviation_report;
1522
1523 end OKC_TERMS_DEVIATIONS_PVT; -- package