[Home] [Help]
PACKAGE BODY: APPS.OKC_NUMBER_SCHEME_GRP
Source
1 PACKAGE BODY OKC_NUMBER_SCHEME_GRP AS
2 /* $Header: OKCGNSMB.pls 120.8 2006/10/05 23:30:51 ssivarap noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL VARIABLES
8 ---------------------------------------------------------------------------
9 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_NUMBER_SCHEME_GRP';
10
11 ------------------------------------------------------------------------------
12 -- GLOBAL CONSTANTS
13 ------------------------------------------------------------------------------
14
15 G_RET_STS_SUCCESS CONSTANT varchar2(1) := FND_API.G_RET_STS_SUCCESS;
16 G_RET_STS_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_ERROR;
17 G_RET_STS_UNEXP_ERROR CONSTANT varchar2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
18
19 G_UNEXPECTED_ERROR CONSTANT varchar2(200) := 'OKC_UNEXPECTED_ERROR';
20 G_SQLERRM_TOKEN CONSTANT varchar2(200) := 'ERROR_MESSAGE';
21 G_SQLCODE_TOKEN CONSTANT varchar2(200) := 'ERROR_CODE';
22
23 G_NUMBER_SEQUENCE CONSTANT VARCHAR2(30) :='NUMBER_SEQUENCE';
24 G_LOWERCASE_ENG_ALPHABETS CONSTANT VARCHAR2(30) :='LOWERCASE_ENG_ALPHABETS';
25 G_UPPERCASE_ENG_ALPHABETS CONSTANT VARCHAR2(30) :='UPPERCASE_ENG_ALPHABETS';
26
27 G_UPPERCASE_ROMAN_NUMBER CONSTANT VARCHAR2(30) :='UPPERCASE_ROMAN_NUMBER';
28 G_LOWERCASE_ROMAN_NUMBER CONSTANT VARCHAR2(30) :='LOWERCASE_ROMAN_NUMBER';
29
30 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
31 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
32 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
33
34 TYPE structure_rec_type IS RECORD (
35 ID NUMBER,
36 Type VARCHAR2(30),
37 label VARCHAR2(100)
38 );
39
40 TYPE structure_tbl_type IS TABLE OF structure_rec_type INDEX BY BINARY_INTEGER;
41
42 TYPE review_rec_type IS RECORD (
43 review_upld_terms_id NUMBER,
44 object_id NUMBER,
45 Type VARCHAR2(30),
46 label VARCHAR2(100)
47 );
48
49 TYPE review_tbl_type IS TABLE OF review_rec_type INDEX BY BINARY_INTEGER;
50
51 l_structure_tbl structure_tbl_type;
52 l_review_tbl review_tbl_type;
53
54 l_lvl1_seq_code VARCHAR2(30) := NULL;
55 l_lvl2_seq_code VARCHAR2(30) := NULL;
56 l_lvl3_seq_code VARCHAR2(30) := NULL;
57 l_lvl4_seq_code VARCHAR2(30) := NULL;
58 l_lvl5_seq_code VARCHAR2(30) := NULL;
59
60 l_lvl1_sequence NUMBER :=0;
61 l_lvl2_sequence NUMBER :=0;
62 l_lvl3_sequence NUMBER :=0;
63 l_lvl4_sequence NUMBER :=0;
64 l_lvl5_sequence NUMBER :=0;
65
66 l_lvl1_concat_yn VARCHAR2(1) := NULL;
67 l_lvl2_concat_yn VARCHAR2(1) := NULL;
68 l_lvl3_concat_yn VARCHAR2(1) := NULL;
69 l_lvl4_concat_yn VARCHAR2(1) := NULL;
70 l_lvl5_concat_yn VARCHAR2(1) := NULL;
71
72 --Bug 3663038 Used %type in declaration
73 l_lvl1_end_char OKC_NUMBER_SCHEME_DTLS.END_CHARACTER%TYPE := NULL;
74 l_lvl2_end_char OKC_NUMBER_SCHEME_DTLS.END_CHARACTER%TYPE := NULL;
75 l_lvl3_end_char OKC_NUMBER_SCHEME_DTLS.END_CHARACTER%TYPE := NULL;
76 l_lvl4_end_char OKC_NUMBER_SCHEME_DTLS.END_CHARACTER%TYPE := NULL;
77 l_lvl5_end_char OKC_NUMBER_SCHEME_DTLS.END_CHARACTER%TYPE := NULL;
78
79 l_no_of_levels NUMBER := 0;
80
81 l_number_article_yn VARCHAR2(1) :='Y';
82
83 /*
84 API to conver numbers into excel like Cell numbers. Like 1 ==>A
85 27 ==> AA
86 28 ==> AB
87 52 ==> AZ
88 54 ==> BB
89 */
90
91 FUNCTION GETALPHABET(seq_number number,type varchar2) return varchar2 as
92 l_floor number;
93 l_mod number;
94 l_out varchar2(10);
95 begin
96 l_floor := floor(seq_number/26);
97 l_mod := mod(seq_number,26);
98
99 if l_floor > 0 and not(l_floor=1 and l_mod = 0) then
100
101 Select GETALPHABET(Decode( l_mod,0,l_floor-1,l_floor),type) into l_out from dual;
102
103 end if;
104
105 If l_mod=0 then
106 SELECT l_out||fnd_global.local_chr(26+decode(type,'L',96,'U',64))
107 into l_out from dual;
108 return l_out;
109 else
110 SELECT l_out||fnd_global.local_chr(l_mod+decode(type,'L',96,'U',64))
111 into l_out from dual;
112 return l_out;
113 end if;
114 end;
115
116 FUNCTION convert_to_roman(p_number IN INT) RETURN VARCHAR2 IS
117
118 out_roman varchar2(30);
119 left_over number;
120 thousand number;
121 five_hundred number;
122 hundred number;
123 fifty number;
124 tenth number;
125 fifth number;
126
127 begin
128 /*IF (l_debug = 'Y') THEN
129 okc_debug.log('100: Inside convert_to_roman', 2);
130 okc_debug.log('100: p_number : '||p_number, 2);
131 END IF;*/
132
133 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
134 FND_LOG.STRING(G_PROC_LEVEL,
135 G_PKG_NAME, '100: Inside convert_to_roman' );
136 FND_LOG.STRING(G_PROC_LEVEL,
137 G_PKG_NAME, '100: p_number : '||p_number );
138 END IF;
139
140 If p_number > 3999 then
141 OKC_API.SET_MESSAGE('OKC','OKC_CANT_CONVERT_ROMAN');
142 RAISE FND_API.G_EXC_ERROR ;
143
144 end if;
145
146 thousand := floor(p_number/1000);
147 left_over := mod(p_number,1000);
148 five_hundred :=floor(left_over/500);
149 left_over := mod(left_over,500);
150 hundred := floor(left_over/100);
151 left_over := mod(left_over,100);
152 fifty := floor(left_over/50);
153 left_over := mod(left_over,50);
154 tenth := floor(left_over/10);
155 left_over := mod(left_over,10);
156 fifth := floor(left_over/5);
157 left_over := mod(left_over,5);
158
159
160 if left_over < 4 then
161 if left_over > 0 then
162 for i in 1..left_over loop
163 out_roman := 'I'||out_roman;
164 end loop;
165 end if;
166
167 if fifth > 0 then
168 out_roman := 'V'||out_roman;
169 end if;
170
171 elsif left_over = 4 then
172
173 if fifth > 0 then
174 out_roman := 'IX';
175 else
176 out_roman := 'IV';
177 end if;
178 end if;
179
180 if tenth < 4 then
181 If tenth > 0 then
182 for i in 1..tenth loop
183 out_roman := 'X'||out_roman;
184 end loop;
185 end if;
186 if fifty > 0 then
187 out_roman := 'L'||out_roman;
188 end if;
189 elsif tenth = 4 then
190 if fifty > 0 then
191 out_roman := 'XC'||out_roman;
192 else
193 out_roman := 'XL'||out_roman;
194 end if;
195 end if;
196
197 if hundred < 4 then
198 If hundred > 0 then
199 for i in 1..hundred loop
200 out_roman := 'C'||out_roman;
201 end loop;
202 end if;
203 if five_hundred > 0 then
204 out_roman := 'D'||out_roman;
205 end if;
206 elsif hundred = 4 then
207 if five_hundred > 0 then
208 out_roman := 'CM'||out_roman;
209 else
210 out_roman := 'CD'||out_roman;
211 end if;
212 end if;
213 If thousand < 4 then
214 If thousand > 0 then
215 for i in 1..thousand loop
216 out_roman := 'M'||out_roman;
217 end loop;
218 end if;
219 end if;
220
221 /*IF (l_debug = 'Y') THEN
222 okc_debug.log('1000: Leaving convert_to_roman', 2);
223 okc_debug.log('1000: out_roman : '||out_roman, 2);
224 END IF;*/
225
226 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
227 FND_LOG.STRING(G_PROC_LEVEL,
228 G_PKG_NAME, '1000: Leaving convert_to_roman' );
229 FND_LOG.STRING(G_PROC_LEVEL,
230 G_PKG_NAME, '1000: out_roman : '||out_roman );
231 END IF;
232
233 return out_roman;
234
235 EXCEPTION
236 WHEN FND_API.G_EXC_ERROR THEN
237 /*IF (l_debug = 'Y') THEN
238 okc_debug.log('300: Leaving convert_to_roman: OKC_API.G_EXCEPTION_ERROR Exception', 2);
239 END IF;*/
240
241 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
242 FND_LOG.STRING(G_EXCP_LEVEL,
243 G_PKG_NAME, '300: Leaving convert_to_roman: OKC_API.G_EXCEPTION_ERROR Exception' );
244 END IF;
245
246 raise;
247
248
249 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
250 /*IF (l_debug = 'Y') THEN
251 okc_debug.log('400: Leaving convert_to_roman: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
252 END IF;*/
253
254 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
255 FND_LOG.STRING(G_EXCP_LEVEL,
256 G_PKG_NAME, '400: Leaving convert_to_roman: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
257 END IF;
258
259 raise;
260
261
262 WHEN OTHERS THEN
263 /*IF (l_debug = 'Y') THEN
264 okc_debug.log('500: Leaving convert_to_roman because of EXCEPTION: '||sqlerrm, 2);
265 END IF;*/
266
267 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
268 FND_LOG.STRING(G_EXCP_LEVEL,
269 G_PKG_NAME, '500: Leaving convert_to_roman because of EXCEPTION: '||sqlerrm );
270 END IF;
271
272 raise;
273
274 END convert_to_roman;
275
276 FUNCTION get_numbering_seq(p_level NUMBER) return VARCHAR2 IS
277
278 l_number VARCHAR2(30);
279 l_lvl_sequence NUMBER;
280 l_vl_seq_code VARCHAR2(30);
281 BEGIN
282 /*IF (l_debug = 'Y') THEN
283 okc_debug.log('100: Entering get_numbering_seq', 2);
284 okc_debug.log('100: p_level : '||p_level, 2);
285 END IF;*/
286
287 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
288 FND_LOG.STRING(G_PROC_LEVEL,
289 G_PKG_NAME, '100: Entering get_numbering_seq' );
290 FND_LOG.STRING(G_PROC_LEVEL,
291 G_PKG_NAME, '100: p_level : '||p_level );
292 END IF;
293
294 Select decode(p_level,1,l_lvl1_seq_code,2,l_lvl2_seq_code,3,l_lvl3_seq_code,4,l_lvl4_seq_code,5,l_lvl5_seq_code,NULL)
295 INTO l_vl_seq_code from dual;
296
297 /*IF (l_debug = 'Y') THEN
298 okc_debug.log('150: l_vl_seq_code : '||l_vl_seq_code, 2);
299 END IF;*/
300
301 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
302 FND_LOG.STRING(G_PROC_LEVEL,
303 G_PKG_NAME, '150: l_vl_seq_code : '||l_vl_seq_code );
304 END IF;
305
306 IF p_level = 1 THEN
307 l_lvl1_sequence := l_lvl1_sequence+1;
308 l_lvl_sequence := l_lvl1_sequence;
309 ELSIF p_level = 2 THEN
310 l_lvl2_sequence := l_lvl2_sequence+1;
311 l_lvl_sequence := l_lvl2_sequence;
312 ELSIF p_level = 3 THEN
313 l_lvl3_sequence := l_lvl3_sequence+1;
314 l_lvl_sequence := l_lvl3_sequence;
315
316 ELSIF p_level = 4 THEN
317 l_lvl4_sequence := l_lvl4_sequence+1;
318 l_lvl_sequence := l_lvl4_sequence;
319
320 ELSIF p_level = 5 THEN
321 l_lvl5_sequence := l_lvl5_sequence+1;
322 l_lvl_sequence := l_lvl5_sequence;
323 ELSE
324 l_lvl_sequence := NULL;
325 END IF;
326
327 /*IF (l_debug = 'Y') THEN
328 okc_debug.log('180: l_lvl_sequence : '||l_lvl_sequence, 2);
329 END IF;*/
330
331 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
332 FND_LOG.STRING(G_PROC_LEVEL,
333 G_PKG_NAME, '180: l_lvl_sequence : '||l_lvl_sequence );
334 END IF;
335
336 IF l_vl_seq_code = G_NUMBER_SEQUENCE THEN
337 l_number := l_lvl_sequence;
338 ELSIF l_vl_seq_code = G_UPPERCASE_ROMAN_NUMBER THEN
339 l_number := upper(convert_to_roman(l_lvl_sequence));
340 ELSIF l_vl_seq_code = G_LOWERCASE_ROMAN_NUMBER THEN
341 l_number := lower(convert_to_roman(l_lvl_sequence));
342 ELSIF l_vl_seq_code = G_LOWERCASE_ENG_ALPHABETS THEN
343 l_number := getalphabet(l_lvl_sequence,'L');
344 ELSIF l_vl_seq_code = G_UPPERCASE_ENG_ALPHABETS THEN
345 l_number := getalphabet(l_lvl_sequence,'U');
346 ELSE
347 l_number := NULL;
348 END IF;
349
350
351 /*IF (l_debug = 'Y') THEN
352 okc_debug.log('300: Leaving get_numbering_seq ', 2);
353 okc_debug.log('300: l_number : '||l_number, 2);
354 END IF;*/
355
356 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
357 FND_LOG.STRING(G_PROC_LEVEL,
358 G_PKG_NAME, '300: Leaving get_numbering_seq ' );
359 FND_LOG.STRING(G_PROC_LEVEL,
360 G_PKG_NAME, '300: l_number : '||l_number );
361 END IF;
362
363 return l_number;
364
365 EXCEPTION
366
367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
368 /*IF (l_debug = 'Y') THEN
369 okc_debug.log('400: Leaving get_numbering_seq: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
370 END IF;*/
371
372 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
373 FND_LOG.STRING(G_EXCP_LEVEL,
374 G_PKG_NAME, '400: Leaving get_numbering_seq: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
375 END IF;
376
377 raise;
378
379 WHEN OTHERS THEN
380 /*IF (l_debug = 'Y') THEN
381 okc_debug.log('500: Leaving get_numbering_seq because of EXCEPTION: '||sqlerrm, 2);
382 END IF;*/
383
384 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
385 FND_LOG.STRING(G_EXCP_LEVEL,
386 G_PKG_NAME, '500: Leaving get_numbering_seq because of EXCEPTION: '||sqlerrm );
387 END IF;
388
389 raise;
390 END get_numbering_seq;
391
392
393 procedure section_numbering(p_doc_type varchar2,p_doc_id NUMBER,p_level NUMBER,p_parent_label VARCHAR2,p_scn_id NUMBER) IS
394
395 cursor l_get_child_csr IS
396 SELECT ID,'SECTION' TYPE,section_sequence display_sequence from okc_sections_b
397 where scn_id=p_scn_id
398 and document_type=p_doc_type
399 and document_id = p_doc_id
400 AND nvl(amendment_operation_code,'?') <> 'DELETED'
401 AND nvl(summary_amend_operation_code,'?') <> 'DELETED'
402 UNION
403 SELECT ID,'ARTICLE' TYPE,display_sequence display_sequence from okc_k_ARTICLES_b
404 where scn_id=p_scn_id
405 and document_type=p_doc_type
406 and document_id = p_doc_id
407 AND nvl(amendment_operation_code,'?') <> 'DELETED'
408 AND nvl(summary_amend_operation_code,'?') <> 'DELETED'
409 AND l_number_article_yn = 'Y'
410 Order by 3;
411
412 l_concat_yn VARCHAR2(30) := 'N';
413 l_end_char VARCHAR2(30) := NULL;
414 l_label Varchar2(30) := NULL;
415 l_number Varchar2(30) := NULL;
416 l_next_parent_number Varchar2(30) := NULL;
417 i NUMBER;
418 BEGIN
419
420 /*IF (l_debug = 'Y') THEN
421 okc_debug.log('100: Entering section_numbering ', 2);
422 okc_debug.log('100: Parameters ', 2);
423 okc_debug.log('100: p_doc_type : '||p_doc_type, 2);
424 okc_debug.log('100: p_doc_id : '||p_doc_id, 2);
425 okc_debug.log('100: p_level : '||p_level, 2);
426 okc_debug.log('100: p_parent_label : '||p_parent_label, 2);
427 okc_debug.log('100: p_scn_id : '||p_scn_id, 2);
428 END IF;*/
429
430 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
431 FND_LOG.STRING(G_PROC_LEVEL,
432 G_PKG_NAME, '100: Entering section_numbering ' );
433 FND_LOG.STRING(G_PROC_LEVEL,
434 G_PKG_NAME, '100: Parameters ' );
435 FND_LOG.STRING(G_PROC_LEVEL,
436 G_PKG_NAME, '100: p_doc_type : '||p_doc_type );
437 FND_LOG.STRING(G_PROC_LEVEL,
438 G_PKG_NAME, '100: p_doc_id : '||p_doc_id );
439 FND_LOG.STRING(G_PROC_LEVEL,
440 G_PKG_NAME, '100: p_level : '||p_level );
441 FND_LOG.STRING(G_PROC_LEVEL,
442 G_PKG_NAME, '100: p_parent_label : '||p_parent_label );
443 FND_LOG.STRING(G_PROC_LEVEL,
444 G_PKG_NAME, '100: p_scn_id : '||p_scn_id );
445 END IF;
446
447 select decode(p_level,1,NULL,2,l_lvl1_concat_yn,3,l_lvl2_concat_yn,4,l_lvl3_concat_yn,5,l_lvl4_concat_yn,'N'),
448 decode(p_level,1,l_lvl1_end_char ,2,l_lvl2_end_char ,3,l_lvl3_end_char ,4,l_lvl4_end_char ,5,l_lvl5_end_char ,NULL)
449 into l_concat_yn ,l_end_char from dual;
450
451 /*IF (l_debug = 'Y') THEN
452 okc_debug.log('110: l_concat_yn : '||l_concat_yn, 2);
453 okc_debug.log('110: l_end_char : '||l_end_char, 2);
454 END IF;*/
455
456 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
457 FND_LOG.STRING(G_PROC_LEVEL,
458 G_PKG_NAME, '110: l_concat_yn : '||l_concat_yn );
459 FND_LOG.STRING(G_PROC_LEVEL,
460 G_PKG_NAME, '110: l_end_char : '||l_end_char );
461 END IF;
462
463 FOR cr in l_get_child_csr LOOP
464 l_number := get_numbering_seq(p_level);
465 IF l_concat_yn='Y' THEN
466 l_label := p_parent_label||'.'||l_number||l_end_char;
467 l_next_parent_number := p_parent_label||'.'||l_number;
468 ElSE
469 l_label := l_number||l_end_char;
470 l_next_parent_number := l_number;
471 END IF;
472
473 i := l_structure_tbl.count+1;
474 l_structure_tbl(i).id := cr.id;
475 l_structure_tbl(i).type := cr.type;
476 l_structure_tbl(i).label := l_label;
477
478 /*IF (l_debug = 'Y') THEN
479 okc_debug.log('120: i : '||i, 2);
480 okc_debug.log('120: l_structure_tbl(i).id : '||l_structure_tbl(i).id, 2);
481 okc_debug.log('120: l_structure_tbl(i).type : '||l_structure_tbl(i).type, 2);
482 okc_debug.log('120: l_structure_tbl(i).label : '||l_structure_tbl(i).label, 2);
483 END IF;*/
484
485 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
486 FND_LOG.STRING(G_PROC_LEVEL,
487 G_PKG_NAME, '120: i : '||i );
488 FND_LOG.STRING(G_PROC_LEVEL,
489 G_PKG_NAME, '120: l_structure_tbl(i).id : '||l_structure_tbl(i).id );
490 FND_LOG.STRING(G_PROC_LEVEL,
491 G_PKG_NAME, '120: l_structure_tbl(i).type : '||l_structure_tbl(i).type );
492 FND_LOG.STRING(G_PROC_LEVEL,
493 G_PKG_NAME, '120: l_structure_tbl(i).label : '||l_structure_tbl(i).label );
494 END IF;
495
496 IF cr.type='SECTION' THEN
497 IF p_level = 1 THEN
498 l_lvl2_sequence := 0;
499 ELSIF p_level = 2 THEN
500 l_lvl3_sequence := 0;
501 ELSIF p_level = 3 THEN
502 l_lvl4_sequence := 0;
503 ELSIF p_level = 4 THEN
504 l_lvl5_sequence := 0;
505 ELSE
506 NULL;
507 END IF;
508
509 IF l_no_of_levels > p_level then
510 section_numbering(p_doc_type=>p_doc_type, p_doc_id => p_doc_id,p_level => p_level + 1,p_parent_label => l_next_parent_number,p_scn_id => cr.id) ;
511 END IF;
512 END IF;
513 END LOOP;
514
515
516 /*IF (l_debug = 'Y') THEN
517 okc_debug.log('300: Leaving section_numbering ', 2);
518 END IF;*/
519
520 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
521 FND_LOG.STRING(G_PROC_LEVEL,
522 G_PKG_NAME, '300: Leaving section_numbering ' );
523 END IF;
524
525 EXCEPTION
526
527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
528 /*IF (l_debug = 'Y') THEN
529 okc_debug.log('400: Leaving section_numbering: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
530 END IF;*/
531
532 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
533 FND_LOG.STRING(G_EXCP_LEVEL,
534 G_PKG_NAME, '400: Leaving section_numbering: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
535 END IF;
536
537 raise;
538
539 WHEN OTHERS THEN
540 /*IF (l_debug = 'Y') THEN
541 okc_debug.log('500: Leaving section_numbering because of EXCEPTION: '||sqlerrm, 2);
542 END IF;*/
543
544 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
545 FND_LOG.STRING(G_EXCP_LEVEL,
546 G_PKG_NAME, '500: Leaving section_numbering because of EXCEPTION: '||sqlerrm );
547 END IF;
548
549 raise;
550 END section_numbering;
551
552 PROCEDURE generate_preview(
553 p_api_version IN NUMBER,
554 p_init_msg_list IN VARCHAR2 ,
555
556 x_return_status OUT NOCOPY VARCHAR2,
557 x_msg_count OUT NOCOPY NUMBER,
558 x_msg_data OUT NOCOPY VARCHAR2,
559 x_out_string OUT NOCOPY VARCHAR2,
560 p_update_db IN VARCHAR2 ,
561
562 p_num_scheme_id IN NUMBER
563 ) IS
564 Cursor l_num_sch_dtl_crs IS
565 SELECT decode(Num_Sequence_code, G_NUMBER_SEQUENCE,'1',G_LOWERCASE_ENG_ALPHABETS,'a',G_UPPERCASE_ENG_ALPHABETS,'A',G_LOWERCASE_ROMAN_NUMBER,'i',G_UPPERCASE_ROMAN_NUMBER,'I') label1,
566 decode(Num_Sequence_code, G_NUMBER_SEQUENCE,'2',G_LOWERCASE_ENG_ALPHABETS,'b',G_UPPERCASE_ENG_ALPHABETS,'B',G_LOWERCASE_ROMAN_NUMBER,'ii',G_UPPERCASE_ROMAN_NUMBER,'II')label2,
567 concatenation_yn,
568 sequence_level,
569 End_character
570 FROM OKC_NUMBER_SCHEME_DTLS
571 WHERE Num_scheme_Id = p_num_scheme_id
572 order by Sequence_Level;
573
574 l_out Varchar2(2000) :=NULL;
575 l_string Varchar2(2000) :=NULL;
576 l_concat_yn Varchar2(1);
577 l_label varchar2(30);
578 l_api_name Varchar2(30) :='generate_preview';
579 l_api_version NUMBER :=1;
580 k NUMBER :=0;
581 BEGIN
582 /*IF (l_debug = 'Y') THEN
583 okc_debug.log('100: Entered generate_preview', 2);
584 okc_debug.log('100: p_num_scheme_id : '||p_num_scheme_id, 2);
585 END IF;*/
586
587 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
588 FND_LOG.STRING(G_PROC_LEVEL,
589 G_PKG_NAME, '100: Entered generate_preview' );
590 FND_LOG.STRING(G_PROC_LEVEL,
591 G_PKG_NAME, '100: p_num_scheme_id : '||p_num_scheme_id );
592 END IF;
593
594 -- Standard Start of API savepoint
595 SAVEPOINT g_generate_preview;
596
597 -- Standard call to check for call compatibility.
598 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600 END IF;
601 -- Initialize message list if p_init_msg_list is set to TRUE.
602 IF FND_API.to_Boolean( p_init_msg_list ) THEN
603 FND_MSG_PUB.initialize;
604 END IF;
605 -- Initialize API return status to success
606 x_return_status := G_RET_STS_SUCCESS;
607
608 FOR i in 1..2 LOOP
609 l_concat_yn := 'N';
610 l_label := NULL;
611 l_string := NULL;
612 k :=0;
613 FOR cr IN l_num_sch_dtl_crs LOOP
614 k:=k+1;
615
616 SELECT
617 decode(l_concat_yn,'Y',l_label||'.'||decode(k,1,decode(i,1,cr.label1,2,cr.label2,cr.label1),cr.label1),decode(k,1,decode(i,1,cr.label1,2,cr.label2,cr.label1),cr.label1))
618 INTO l_label FROM DUAL;
619
620 l_string :=l_label ||cr.end_character||' '||ltrim(rtrim(okc_util.decode_lookup('OKC_NUMBER_LEVEL',cr.sequence_level)));
621
622 FOR i in 1..cr.sequence_level LOOP
623 l_string := ' '||l_string;
624 END LOOP;
625
626 l_string := l_string ||fnd_global.newline;
627 l_concat_yn := cr.concatenation_yn;
628 l_out := l_out||l_string;
629 END LOOP;
630 END LOOP;
631 x_out_string := l_out;
632
633 IF p_update_db=FND_API.G_TRUE THEN
634
635 Update OKC_NUMBER_SCHEMES_B SET
636 num_scheme_preview = x_out_string,
637 object_version_number = object_version_number+1,
638 creation_date = sysdate,
639 created_by = Fnd_Global.User_Id,
640 last_update_date = sysdate,
641 last_updated_by = Fnd_Global.User_Id,
642 last_update_login = Fnd_Global.Login_Id
643 WHERE num_scheme_id = p_num_scheme_id;
644 Commit;
645
646 END IF;
647
648 /*IF (l_debug = 'Y') THEN
649 okc_debug.log('300: Leaving generate_preview ', 2);
650 END IF;*/
651
652 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
653 FND_LOG.STRING(G_PROC_LEVEL,
654 G_PKG_NAME, '300: Leaving generate_preview' );
655 END IF;
656
657 EXCEPTION
658 WHEN FND_API.G_EXC_ERROR THEN
659 /*IF (l_debug = 'Y') THEN
660 okc_debug.log('2400: Leaving generate_preview: OKC_API.G_EXCEPTION_ERROR Exception', 2);
661 END IF;*/
662
663 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
664 FND_LOG.STRING(G_EXCP_LEVEL,
665 G_PKG_NAME, '2400: Leaving generate_preview: OKC_API.G_EXCEPTION_ERROR Exception' );
666 END IF;
667
668 Rollback to g_generate_preview;
669 x_return_status := G_RET_STS_ERROR ;
670 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
671
672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
673 /*IF (l_debug = 'Y') THEN
674 okc_debug.log('2400: Leaving generate_preview: OKC_API.G_EXCEPTION_ERROR Exception', 2);
675 END IF;*/
676
677 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
678 FND_LOG.STRING(G_EXCP_LEVEL,
679 G_PKG_NAME, '2400: Leaving generate_preview: OKC_API.G_EXCEPTION_ERROR Exception' );
680 END IF;
681
682 Rollback to g_generate_preview;
683 x_return_status := G_RET_STS_UNEXP_ERROR ;
684 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
685
686 WHEN OTHERS THEN
687 /*IF (l_debug = 'Y') THEN
688 okc_debug.log('2400: Leaving generate_preview: OKC_API.G_EXCEPTION_ERROR Exception', 2);
689 END IF;*/
690
691 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
692 FND_LOG.STRING(G_EXCP_LEVEL,
693 G_PKG_NAME, '2400: Leaving generate_preview: OKC_API.G_EXCEPTION_ERROR Exception' );
694 END IF;
695
696 Rollback to g_generate_preview;
697 x_return_status := G_RET_STS_UNEXP_ERROR ;
698 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
699 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
700 END IF;
701 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
702
703
704 END generate_preview;
705
706 PROCEDURE apply_numbering_scheme(
707 p_api_version IN NUMBER,
708 p_init_msg_list IN VARCHAR2 ,
709
710 x_return_status OUT NOCOPY VARCHAR2,
711 x_msg_count OUT NOCOPY NUMBER,
712 x_msg_data OUT NOCOPY VARCHAR2,
713 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
714 p_validation_string IN VARCHAR2,
715 p_commit IN VARCHAR2 := FND_API.G_FALSE,
716 p_doc_type IN VARCHAR2,
717 p_doc_id IN NUMBER,
718 p_num_scheme_id IN NUMBER
719 ) IS
720
721 l_api_version NUMBER :=1;
722 l_api_name VARCHAR2(30) := 'apply_numbering_scheme';
723
724 cursor l_get_child_csr IS
725 SELECT ID,section_sequence from okc_sections_b
726 where document_type = p_doc_type
727 AND document_id = p_doc_id
728 AND SCN_ID IS NULL
729 AND nvl(amendment_operation_code,'?') <> 'DELETED'
730 AND nvl(summary_amend_operation_code,'?') <> 'DELETED'
731 Order by section_sequence;
732
733 cursor l_get_num_scheme IS
734 SELECT number_article_yn from OKC_NUMBER_SCHEMES_B
735 where num_scheme_id=p_num_scheme_id;
736
737 cursor l_get_num_scheme_dtl IS
738 SELECT num_sequence_code,sequence_level,concatenation_yn,end_character from OKC_NUMBER_SCHEME_DTLS
739 where num_scheme_id=p_num_scheme_id;
740
741 cursor l_get_usage_rec IS
742 SELECT template_id,object_version_number from OKC_template_usages
743 where document_type=p_doc_type and document_id=p_doc_id;
744
745 CURSOR l_get_art_csr IS
746 SELECT id,object_version_number
747 FROM okc_k_articles_b
748 WHERE document_type=p_doc_type
749 AND document_id=p_doc_id
750 AND nvl(amendment_operation_code,'?') <> 'DELETED'
751 AND nvl(summary_amend_operation_code,'?') <> 'DELETED'
752 AND label is NOT NULL;
753
754 l_label Varchar2(30) := NULL;
755 l_number Varchar2(30) := NULL;
756 i NUMBER;
757
758 CURSOR l_get_dtl_count IS
759 SELECT COUNT(*)
760 FROM OKC_NUMBER_SCHEME_DTLS
761 WHERE num_scheme_id=p_num_scheme_id;
762
763 l_dtl_count NUMBER;
764
765
766 BEGIN
767
768 /*IF (l_debug = 'Y') THEN
769 okc_debug.log('100: Entered apply_numbering_scheme', 2);
770 okc_debug.log('100: Parameter List ', 2);
771 okc_debug.log('100: p_api_version : '||p_api_version, 2);
772 okc_debug.log('100: p_init_msg_list : '||p_init_msg_list, 2);
773 okc_debug.log('100: p_commit : '||p_commit, 2);
774 okc_debug.log('100: p_validate_commit : '||p_validate_commit, 2);
775 okc_debug.log('100: p_validation_string : '||p_validation_string , 2);
776 okc_debug.log('100: p_doc_type : '||p_doc_type, 2);
777 okc_debug.log('100: p_doc_id : '||p_doc_id, 2);
778 okc_debug.log('100: p_num_scheme_id : '||p_num_scheme_id, 2);
779 END IF;*/
780
781 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
782 FND_LOG.STRING(G_PROC_LEVEL,
783 G_PKG_NAME, '100: Entered apply_numbering_scheme' );
784 FND_LOG.STRING(G_PROC_LEVEL,
785 G_PKG_NAME, '100: Parameter List ' );
786 FND_LOG.STRING(G_PROC_LEVEL,
787 G_PKG_NAME, '100: p_api_version : '||p_api_version );
788 FND_LOG.STRING(G_PROC_LEVEL,
789 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
790 FND_LOG.STRING(G_PROC_LEVEL,
791 G_PKG_NAME, '100: p_commit : '||p_commit );
792 FND_LOG.STRING(G_PROC_LEVEL,
793 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
794 FND_LOG.STRING(G_PROC_LEVEL,
795 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
796 FND_LOG.STRING(G_PROC_LEVEL,
797 G_PKG_NAME, '100: p_doc_type : '||p_doc_type );
798 FND_LOG.STRING(G_PROC_LEVEL,
799 G_PKG_NAME, '100: p_doc_id : '||p_doc_id );
800 FND_LOG.STRING(G_PROC_LEVEL,
801 G_PKG_NAME, '100: p_num_scheme_id : '||p_num_scheme_id );
802 END IF;
803
804 -- Initialize the global Structure Table bug 3200243
805 l_structure_tbl.DELETE;
806
807 -- Standard Start of API savepoint
808 SAVEPOINT g_apply_numbering_scheme;
809 -- Standard call to check for call compatibility.
810 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
812 END IF;
813 -- Initialize message list if p_init_msg_list is set to TRUE.
814 IF FND_API.to_Boolean( p_init_msg_list ) THEN
815 FND_MSG_PUB.initialize;
816 END IF;
817
818 IF FND_API.To_Boolean( p_validate_commit ) THEN
819
820 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
821 p_api_version => l_api_version,
822 p_init_msg_list => FND_API.G_FALSE,
823 p_doc_type => p_doc_type,
824 p_doc_id => p_doc_id,
825 p_validation_string => p_validation_string,
826 x_return_status => x_return_status,
827 x_msg_data => x_msg_data,
828 x_msg_count => x_msg_count) ) THEN
829
830 /*IF (l_debug = 'Y') THEN
831 okc_debug.log('110: Issue with document header Record.Cannot commit', 2);
832 END IF;*/
833
834 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
835 FND_LOG.STRING(G_PROC_LEVEL,
836 G_PKG_NAME, '110: Issue with document header Record.Cannot commit' );
837 END IF;
838 RAISE FND_API.G_EXC_ERROR ;
839 END IF;
840 END IF;
841
842 -- Initialize API return status to success
843 x_return_status := FND_API.G_RET_STS_SUCCESS;
844
845 -- if Count of Numbering Scheme Detail is 0 , then this is the no numbering scheme
846 -- in this case we need to remove the label from sections and articles table
847 OPEN l_get_dtl_count;
848 FETCH l_get_dtl_count INTO l_dtl_count;
849 CLOSE l_get_dtl_count;
850
851 l_no_of_levels := l_dtl_count;
852
853 /*IF (l_debug = 'Y') THEN
854 okc_debug.log('110: Numbering Scheme Detail Count : '||l_dtl_count,2);
855 END IF;*/
856
857 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
858 FND_LOG.STRING(G_PROC_LEVEL,
859 G_PKG_NAME, '110: Numbering Scheme Detail Count : '||l_dtl_count );
860 END IF;
861
862 --IF l_dtl_count = 0 THEN
863 -- do bulk update on sections and articles records
864 -- update okc_sections_b
865 UPDATE okc_sections_b
866 SET label = NULL
867 WHERE document_type = p_doc_type
868 AND document_id = p_doc_id ;
869
870 -- update okc_k_articles_b
871 UPDATE okc_k_articles_b
872 SET label = NULL
873 WHERE document_type = p_doc_type
874 AND document_id = p_doc_id ;
875
876 --ELSE
877 IF NVL(l_dtl_count,0) > 0 THEN
878 -- dtl count > 0
879
880 OPEN l_get_num_scheme ;
881 FETCH l_get_num_scheme INTO l_number_article_yn ;
882 IF l_get_num_scheme%NOTFOUND THEN
883 raise FND_API.G_EXC_UNEXPECTED_ERROR;
884 END IF;
885 CLOSE l_get_num_scheme ;
886
887 /*IF (l_debug = 'Y') THEN
888 okc_debug.log('120: l_number_article_yn : '||l_number_article_yn, 2);
889 END IF;*/
890
891 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
892 FND_LOG.STRING(G_PROC_LEVEL,
893 G_PKG_NAME, '120: l_number_article_yn : '||l_number_article_yn );
894 END IF;
895
896 FOR CR in l_get_num_scheme_dtl LOOP
897 IF cr.sequence_level=1 THEN
898 l_lvl1_seq_code := cr.num_sequence_code;
899 l_lvl1_concat_yn := cr.concatenation_yn;
900 l_lvl1_end_char := cr.end_character;
901 ELSIF cr.sequence_level=2 THEN
902 l_lvl2_seq_code := cr.num_sequence_code;
903 l_lvl2_concat_yn := cr.concatenation_yn;
904 l_lvl2_end_char := cr.end_character;
905 ELSIF cr.sequence_level=3 THEN
906 l_lvl3_seq_code := cr.num_sequence_code;
907 l_lvl3_concat_yn := cr.concatenation_yn;
908 l_lvl3_end_char := cr.end_character;
909 ELSIF cr.sequence_level=4 THEN
910 l_lvl4_seq_code := cr.num_sequence_code;
911 l_lvl4_concat_yn := cr.concatenation_yn;
912 l_lvl4_end_char := cr.end_character;
913 ELSIF cr.sequence_level=5 THEN
914 l_lvl5_seq_code := cr.num_sequence_code;
915 l_lvl5_concat_yn := cr.concatenation_yn;
916 l_lvl5_end_char := cr.end_character;
917 END IF;
918
919 END LOOP;
920
921 /*IF (l_debug = 'Y') THEN
922 okc_debug.log('130: Sequence Level 1 ', 2);
923 okc_debug.log('130: l_lvl1_seq_code : '||l_lvl1_seq_code, 2);
924 okc_debug.log('130: l_lvl1_concat_yn : '||l_lvl1_concat_yn, 2);
925 okc_debug.log('130: l_lvl1_end_char : '||l_lvl1_end_char, 2);
926 okc_debug.log('130: Sequence Level 2 ', 2);
927 okc_debug.log('130: l_lvl2_seq_code : '||l_lvl2_seq_code, 2);
928 okc_debug.log('130: l_lvl2_concat_yn : '||l_lvl2_concat_yn, 2);
929 okc_debug.log('130: l_lvl2_end_char : '||l_lvl2_end_char, 2);
930 okc_debug.log('130: Sequence Level 3 ', 2);
931 okc_debug.log('130: l_lvl3_seq_code : '||l_lvl3_seq_code, 2);
932 okc_debug.log('130: l_lvl3_concat_yn : '||l_lvl3_concat_yn, 2);
933 okc_debug.log('130: l_lvl3_end_char : '||l_lvl3_end_char, 2);
934 okc_debug.log('130: Sequence Level 4 ', 2);
935 okc_debug.log('130: l_lvl4_seq_code : '||l_lvl4_seq_code, 2);
936 okc_debug.log('130: l_lvl4_concat_yn : '||l_lvl4_concat_yn, 2);
937 okc_debug.log('130: l_lvl4_end_char : '||l_lvl4_end_char, 2);
938 okc_debug.log('130: Sequence Level 5 ', 2);
939 okc_debug.log('130: l_lvl5_seq_code : '||l_lvl5_seq_code, 2);
940 okc_debug.log('130: l_lvl5_concat_yn : '||l_lvl5_concat_yn, 2);
941 okc_debug.log('130: l_lvl5_end_char : '||l_lvl5_end_char, 2);
942 END IF;*/
943
944
945 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
946 FND_LOG.STRING(G_PROC_LEVEL,
947 G_PKG_NAME, '130: Sequence Level 1 ');
948 FND_LOG.STRING(G_PROC_LEVEL,
949 G_PKG_NAME, '130: l_lvl1_seq_code : '||l_lvl1_seq_code);
950 FND_LOG.STRING(G_PROC_LEVEL,
951 G_PKG_NAME, '130: l_lvl1_concat_yn : '||l_lvl1_concat_yn);
952 FND_LOG.STRING(G_PROC_LEVEL,
953 G_PKG_NAME, '130: l_lvl1_end_char : '||l_lvl1_end_char);
954 FND_LOG.STRING(G_PROC_LEVEL,
955 G_PKG_NAME, '130: Sequence Level 2 ');
956 FND_LOG.STRING(G_PROC_LEVEL,
957 G_PKG_NAME, '130: l_lvl2_seq_code : '||l_lvl2_seq_code);
958 FND_LOG.STRING(G_PROC_LEVEL,
959 G_PKG_NAME, '130: l_lvl2_concat_yn : '||l_lvl2_concat_yn);
960 FND_LOG.STRING(G_PROC_LEVEL,
961 G_PKG_NAME, '130: l_lvl2_end_char : '||l_lvl2_end_char);
962 FND_LOG.STRING(G_PROC_LEVEL,
963 G_PKG_NAME, '130: Sequence Level 3 ');
964 FND_LOG.STRING(G_PROC_LEVEL,
965 G_PKG_NAME, '130: l_lvl3_seq_code : '||l_lvl3_seq_code);
966 FND_LOG.STRING(G_PROC_LEVEL,
967 G_PKG_NAME, '130: l_lvl3_concat_yn : '||l_lvl3_concat_yn);
968 FND_LOG.STRING(G_PROC_LEVEL,
969 G_PKG_NAME, '130: l_lvl3_end_char : '||l_lvl3_end_char);
970 FND_LOG.STRING(G_PROC_LEVEL,
971 G_PKG_NAME, '130: Sequence Level 4 ');
972 FND_LOG.STRING(G_PROC_LEVEL,
973 G_PKG_NAME, '130: l_lvl4_seq_code : '||l_lvl4_seq_code);
974 FND_LOG.STRING(G_PROC_LEVEL,
975 G_PKG_NAME, '130: l_lvl4_concat_yn : '||l_lvl4_concat_yn);
976 FND_LOG.STRING(G_PROC_LEVEL,
977 G_PKG_NAME, '130: l_lvl4_end_char : '||l_lvl4_end_char);
978 FND_LOG.STRING(G_PROC_LEVEL,
979 G_PKG_NAME, '130: Sequence Level 5 ');
980 FND_LOG.STRING(G_PROC_LEVEL,
981 G_PKG_NAME, '130: l_lvl5_seq_code : '||l_lvl5_seq_code);
982 FND_LOG.STRING(G_PROC_LEVEL,
983 G_PKG_NAME, '130: l_lvl5_concat_yn : '||l_lvl5_concat_yn);
984 FND_LOG.STRING(G_PROC_LEVEL,
985 G_PKG_NAME, '130: l_lvl5_end_char : '||l_lvl5_end_char);
986 END IF;
987
988 l_lvl1_sequence :=0;
989 l_lvl2_sequence :=0;
990 l_lvl3_sequence :=0;
991 l_lvl4_sequence :=0;
992 l_lvl5_sequence :=0;
993
994 FOR cr in l_get_child_csr LOOP
995
996 l_number := get_numbering_seq(p_level => 1);
997
998 /*IF (l_debug = 'Y') THEN
999 okc_debug.log('140: l_number : '||l_number, 2);
1000 END IF;*/
1001
1002 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1003 FND_LOG.STRING(G_PROC_LEVEL,
1004 G_PKG_NAME, '140: l_number : '||l_number );
1005 END IF;
1006
1007 l_label := l_number||l_lvl1_end_char;
1008
1009 /*IF (l_debug = 'Y') THEN
1010 okc_debug.log('150: l_label : '||l_label, 2);
1011 END IF;*/
1012
1013 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1014 FND_LOG.STRING(G_PROC_LEVEL,
1015 G_PKG_NAME, '150: l_label : '||l_label );
1016 END IF;
1017
1018 i := l_structure_tbl.count+1;
1019 l_structure_tbl(i).id := cr.id;
1020 l_structure_tbl(i).type := 'SECTION';
1021 l_structure_tbl(i).label := l_label;
1022
1023 l_lvl2_sequence := 0;
1024
1025 IF l_no_of_levels > 1 THEN
1026 section_numbering(p_doc_type=>p_doc_type, p_doc_id => p_doc_id,p_level => 2,p_parent_label => l_number,p_scn_id => cr.id) ;
1027 END IF;
1028
1029 END LOOP;
1030
1031 /*IF (l_debug = 'Y') THEN
1032 okc_debug.log('160: Count of l_structure_tbl : '||l_structure_tbl.count, 2);
1033 END IF;*/
1034
1035 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1036 FND_LOG.STRING(G_PROC_LEVEL,
1037 G_PKG_NAME, '160: Count of l_structure_tbl : '||l_structure_tbl.count );
1038 END IF;
1039
1040 IF l_structure_tbl.count > 0 THEN
1041 For k in l_structure_tbl.FIRST..l_structure_tbl.LAST LOOP
1042 IF l_structure_tbl(k).type ='SECTION' THEN
1043
1044 /*IF (l_debug = 'Y') THEN
1045 okc_debug.log('170: Calling OKC_TERMS_SECTIONS_GRP.update_section', 2);
1046 okc_debug.log('170: l_structure_tbl(k).id : '||l_structure_tbl(k).id, 2);
1047 okc_debug.log('170: l_structure_tbl(k).label : '||l_structure_tbl(k).label, 2);
1048 END IF;*/
1049
1050 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1051 FND_LOG.STRING(G_PROC_LEVEL,
1052 G_PKG_NAME, '170: Calling OKC_TERMS_SECTIONS_GRP.update_section' );
1053 FND_LOG.STRING(G_PROC_LEVEL,
1054 G_PKG_NAME, '170: l_structure_tbl(k).id : '||l_structure_tbl(k).id );
1055 FND_LOG.STRING(G_PROC_LEVEL,
1056 G_PKG_NAME, '170: l_structure_tbl(k).label : '||l_structure_tbl(k).label );
1057 END IF;
1058
1059 OKC_TERMS_SECTIONS_GRP.update_section(
1060 p_api_version => 1,
1061 p_init_msg_list => FND_API.G_FALSE,
1062 p_mode => 'NORMAL',
1063 x_return_status => x_return_status,
1064 x_msg_count => x_msg_count,
1065 x_msg_data => x_msg_data,
1066 p_id => l_structure_tbl(k).id,
1067 p_label => l_structure_tbl(k).label,
1068 p_object_version_number => NULL
1069 );
1070
1071 /*IF (l_debug = 'Y') THEN
1072 okc_debug.log('170: After Calling OKC_TERMS_SECTIONS_GRP.update_section x_return_status : '||x_return_status, 2);
1073 END IF;*/
1074
1075 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1076 FND_LOG.STRING(G_PROC_LEVEL,
1077 G_PKG_NAME, '170: After Calling OKC_TERMS_SECTIONS_GRP.update_section x_return_status : '||x_return_status );
1078 END IF;
1079
1080 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1082 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1083 RAISE FND_API.G_EXC_ERROR ;
1084 END IF;
1085 ELSIF l_structure_tbl(k).type ='ARTICLE' THEN
1086
1087 /*IF (l_debug = 'Y') THEN
1088 okc_debug.log('180: Calling OKC_K_ARTICLES_GRP.update_article', 2);
1089 okc_debug.log('180: l_structure_tbl(k).id : '||l_structure_tbl(k).id, 2);
1090 okc_debug.log('180: l_structure_tbl(k).label : '||l_structure_tbl(k).label, 2);
1091 END IF;*/
1092
1093 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1094 FND_LOG.STRING(G_PROC_LEVEL,
1095 G_PKG_NAME, '180: Calling OKC_K_ARTICLES_GRP.update_article');
1096 FND_LOG.STRING(G_PROC_LEVEL,
1097 G_PKG_NAME, '180: l_structure_tbl(k).id : '||l_structure_tbl(k).id);
1098 FND_LOG.STRING(G_PROC_LEVEL,
1099 G_PKG_NAME, '180: l_structure_tbl(k).label : '||l_structure_tbl(k).label);
1100 END IF;
1101
1102 OKC_K_ARTICLES_GRP.update_article(
1103 p_api_version => 1,
1104 p_init_msg_list => FND_API.G_FALSE,
1105 p_mode => 'NORMAL',
1106 x_return_status => x_return_status,
1107 x_msg_count => x_msg_count,
1108 x_msg_data => x_msg_data,
1109 p_id => l_structure_tbl(k).id,
1110 p_label => l_structure_tbl(k).label,
1111 p_object_version_number => NULL
1112 );
1113 /*IF (l_debug = 'Y') THEN
1114 okc_debug.log('180: After Calling OKC_TERMS_SECTIONS_GRP.update_section x_return_status : '||x_return_status, 2);
1115 END IF;*/
1116
1117 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1118 FND_LOG.STRING(G_PROC_LEVEL,
1119 G_PKG_NAME, '180: After Calling OKC_TERMS_SECTIONS_GRP.update_section x_return_status : '||x_return_status );
1120 END IF;
1121
1122 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1123 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1124 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1125 RAISE FND_API.G_EXC_ERROR ;
1126 END IF;
1127 END IF;
1128 END LOOP;
1129 END IF; -- l_structure_tbl.count > 0
1130
1131 /******* Not needed as all the labels are being updated in the begining itself
1132 IF l_number_article_yn <>'Y' THEN
1133 -- if any articles are already numbered, make label to null
1134 FOR cr in l_get_art_csr LOOP
1135 OKC_K_ARTICLES_GRP.update_article(
1136 p_api_version => 1,
1137 p_init_msg_list => FND_API.G_FALSE,
1138 p_mode => 'NORMAL',
1139 x_return_status => x_return_status,
1140 x_msg_count => x_msg_count,
1141 x_msg_data => x_msg_data,
1142 p_id => cr.id,
1143 p_label => OKC_API.G_MISS_CHAR,
1144 p_object_version_number => cr.object_version_number
1145 );
1146
1147 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1149 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1150 RAISE FND_API.G_EXC_ERROR ;
1151 END IF;
1152
1153 END LOOP;
1154 END IF; -- l_number_article_yn <>'Y'
1155 ***********/
1156
1157 END IF; -- detail count is 0 i.e no numbering scheme
1158
1159
1160
1161
1162
1163 IF p_doc_type='TEMPLATE' THEN
1164
1165 /*IF (l_debug = 'Y') THEN
1166 okc_debug.log('300: p_doc_type = TEMPLATE', 2);
1167 okc_debug.log('300: OKC_TERMS_TEMPLATES_GRP.update_template', 2);
1168 END IF;*/
1169
1170 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1171 FND_LOG.STRING(G_PROC_LEVEL,
1172 G_PKG_NAME, '300: p_doc_type = TEMPLATE' );
1173 FND_LOG.STRING(G_PROC_LEVEL,
1174 G_PKG_NAME, '300: OKC_TERMS_TEMPLATES_GRP.update_template' );
1175 END IF;
1176
1177 OKC_TERMS_TEMPLATES_GRP.update_template(
1178 p_api_version => 1,
1179 p_init_msg_list => FND_API.G_FALSE,
1180 x_return_status => x_return_status,
1181 x_msg_count => x_msg_count,
1182 x_msg_data => x_msg_data,
1183 p_template_name => NULL,
1184 p_template_id => p_doc_id,
1185 p_working_copy_flag =>NULL,
1186 p_intent =>NULL,
1187 p_status_code =>NULL,
1188 p_start_date =>NULL,
1189 p_end_date =>NULL,
1190 p_global_flag =>NULL,
1191 p_parent_template_id =>NULL,
1192 p_print_template_id =>NULL,
1193 p_contract_expert_enabled =>NULL,
1194 p_template_model_id =>NULL,
1195 p_instruction_text =>NULL,
1196 p_tmpl_numbering_scheme =>p_num_scheme_id,
1197 p_description =>NULL,
1198 p_org_id => NULL,
1199 p_object_version_number => NULL
1200 );
1201
1202 /*IF (l_debug = 'Y') THEN
1203 okc_debug.log('300: After Call to OKC_TERMS_TEMPLATES_GRP.update_template x_return_status : '||x_return_status, 2);
1204 END IF;*/
1205
1206 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1207 FND_LOG.STRING(G_PROC_LEVEL,
1208 G_PKG_NAME, '300: After Call to OKC_TERMS_TEMPLATES_GRP.update_template x_return_status : '||x_return_status );
1209 END IF;
1210
1211 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1213 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1214 RAISE FND_API.G_EXC_ERROR ;
1215 END IF;
1216
1217
1218 ELSE
1219 FOR cr in l_get_usage_rec LOOP
1220
1221 -- Updating usage rec with numbering scheme
1222 /*IF (l_debug = 'Y') THEN
1223 okc_debug.log('400: OKC_TEMPLATE_USAGES_GRP.update_template_usages', 2);
1224 END IF;*/
1225
1226 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1227 FND_LOG.STRING(G_PROC_LEVEL,
1228 G_PKG_NAME, '400: OKC_TEMPLATE_USAGES_GRP.update_template_usages' );
1229 END IF;
1230
1231 OKC_TEMPLATE_USAGES_GRP.update_template_usages(
1232 p_api_version =>1,
1233 p_init_msg_list => FND_API.G_FALSE,
1234 x_return_status => x_return_status,
1235 x_msg_count => x_msg_count,
1236 x_msg_data => x_msg_data,
1237 p_document_type => p_doc_type,
1238 p_document_id => p_doc_id,
1239 p_template_id => cr.template_id,
1240 p_doc_numbering_scheme=>p_num_scheme_id ,
1241 p_document_number =>NULL,
1242 p_article_effective_date => NULL,
1243 p_config_header_id =>NULL,
1244 p_config_revision_number =>NULL,
1245 p_valid_config_yn =>NULL,
1246 p_object_version_number =>cr.object_version_number
1247 );
1248
1249 /*IF (l_debug = 'Y') THEN
1250 okc_debug.log('400: After Call to OKC_TEMPLATE_USAGES_GRP.update_template_usages x_return_status : '||x_return_status, 2);
1251 END IF;*/
1252
1253 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1254 FND_LOG.STRING(G_PROC_LEVEL,
1255 G_PKG_NAME, '400: After Call to OKC_TEMPLATE_USAGES_GRP.update_template_usages x_return_status : '||x_return_status );
1256 END IF;
1257
1258 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1260 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1261 RAISE FND_API.G_EXC_ERROR ;
1262 END IF;
1263 EXIT;
1264 END LOOP;
1265 END IF;
1266
1267 -- Standard check of p_commit
1268 IF FND_API.To_Boolean( p_commit ) THEN
1269 COMMIT WORK;
1270 END IF;
1271 -- Standard call to get message count and if count is 1, get message info.
1272 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1273
1274 /*IF (l_debug = 'Y') THEN
1275 okc_debug.log('2000: Leaving apply_numbering_scheme', 2);
1276 END IF;*/
1277
1278 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1279 FND_LOG.STRING(G_PROC_LEVEL,
1280 G_PKG_NAME, '2000: Leaving apply_numbering_scheme' );
1281 END IF;
1282 EXCEPTION
1283 WHEN FND_API.G_EXC_ERROR THEN
1284 /*IF (l_debug = 'Y') THEN
1285 okc_debug.log('2400: Leaving apply_numbering_scheme: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1286 END IF;*/
1287
1288 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1289 FND_LOG.STRING(G_EXCP_LEVEL,
1290 G_PKG_NAME, '2400: Leaving apply_numbering_scheme: OKC_API.G_EXCEPTION_ERROR Exception' );
1291 END IF;
1292
1293 ROLLBACK TO g_apply_numbering_scheme;
1294 x_return_status := G_RET_STS_ERROR ;
1295 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1296
1297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1298 /*IF (l_debug = 'Y') THEN
1299 okc_debug.log('2400: Leaving apply_numbering_scheme: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1300 END IF;*/
1301
1302 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1303 FND_LOG.STRING(G_EXCP_LEVEL,
1304 G_PKG_NAME, '2400: Leaving apply_numbering_scheme: OKC_API.G_EXCEPTION_ERROR Exception' );
1305 END IF;
1306
1307 ROLLBACK TO g_apply_numbering_scheme;
1308 x_return_status := G_RET_STS_UNEXP_ERROR ;
1309 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1310
1311 WHEN OTHERS THEN
1312 /*IF (l_debug = 'Y') THEN
1313 okc_debug.log('2400: Leaving apply_numbering_scheme: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1314 END IF;*/
1315
1316 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1317 FND_LOG.STRING(G_EXCP_LEVEL,
1318 G_PKG_NAME, '2400: Leaving apply_numbering_scheme: OKC_API.G_EXCEPTION_ERROR Exception' );
1319 END IF;
1320
1321 ROLLBACK TO g_apply_numbering_scheme;
1322 x_return_status := G_RET_STS_UNEXP_ERROR ;
1323 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1324 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1325 END IF;
1326 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1327
1328 END apply_numbering_scheme;
1329
1330
1331 FUNCTION Ok_To_Delete(
1332 p_num_scheme_id IN NUMBER
1333 ) RETURN VARCHAR2 IS
1334 CURSOR used_in_tmpl_crs IS
1335 SELECT 'N' from OKC_TERMS_TEMPLATES_ALL
1336 WHERE TMPL_NUMBERING_SCHEME=p_num_scheme_id;
1337 CURSOR used_in_doc_crs IS -- it's required index on DOC_NUMBERING_SCHEME column
1338 SELECT 'N' from OKC_TEMPLATE_USAGES_V
1339 WHERE DOC_NUMBERING_SCHEME=p_num_scheme_id;
1340 l_ret VARCHAR2(1) := 'Y';
1341 BEGIN
1342 OPEN used_in_tmpl_crs;
1343 FETCH used_in_tmpl_crs INTO l_ret;
1344 CLOSE used_in_tmpl_crs;
1345 IF l_ret='Y' THEN
1346 OPEN used_in_doc_crs;
1347 FETCH used_in_doc_crs INTO l_ret;
1348 CLOSE used_in_doc_crs;
1349 END IF;
1350 RETURN l_ret;
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 IF used_in_tmpl_crs%ISOPEN THEN
1354 CLOSE used_in_tmpl_crs;
1355 END IF;
1356 IF used_in_doc_crs%ISOPEN THEN
1357 CLOSE used_in_doc_crs;
1358 END IF;
1359 RETURN NULL;
1360 END Ok_To_Delete;
1361
1362 procedure review_section_numbering(
1363 p_doc_type varchar2,
1364 p_doc_id NUMBER,
1365 p_level NUMBER,
1366 p_parent_label VARCHAR2,
1367 p_review_upld_terms_id NUMBER) IS
1368
1369 cursor l_get_child_csr IS
1370 SELECT review_upld_terms_id,
1371 object_type TYPE,
1372 display_seq,
1373 object_id
1374 from okc_review_upld_terms
1375 where new_parent_id=p_review_upld_terms_id
1376 and document_type=p_doc_type
1377 and document_id = p_doc_id
1378 and (object_type = 'SECTION'
1379 OR (object_type = 'ARTICLE'
1380 and l_number_article_yn = 'Y'))
1381 /***********
1382 UNION
1383 SELECT review_upld_terms_id,'ARTICLE' TYPE,display_seq, object_id from okc_review_upld_terms
1384 where new_parent_id=p_review_upld_terms_id
1385 and document_type=p_doc_type
1386 and document_id = p_doc_id
1387 and object_type = 'ARTICLE'
1388 *********/
1389 Order by 3;
1390
1391 l_concat_yn VARCHAR2(30) := 'N';
1392 l_end_char VARCHAR2(30) := NULL;
1393 l_label Varchar2(30) := NULL;
1394 l_number Varchar2(30) := NULL;
1395 l_next_parent_number Varchar2(30) := NULL;
1396 i NUMBER;
1397 BEGIN
1398
1399 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1400 FND_LOG.STRING(G_PROC_LEVEL,
1401 G_PKG_NAME, '100: Entering review_section_numbering ');
1402 FND_LOG.STRING(G_PROC_LEVEL,
1403 G_PKG_NAME, '100: Parameters ');
1404 FND_LOG.STRING(G_PROC_LEVEL,
1405 G_PKG_NAME, '100: p_doc_type : '||p_doc_type);
1406 FND_LOG.STRING(G_PROC_LEVEL,
1407 G_PKG_NAME, '100: p_doc_id : '||p_doc_id);
1408 FND_LOG.STRING(G_PROC_LEVEL,
1409 G_PKG_NAME, '100: p_level : '||p_level);
1410 FND_LOG.STRING(G_PROC_LEVEL,
1411 G_PKG_NAME, '100: p_parent_label : '||p_parent_label);
1412 FND_LOG.STRING(G_PROC_LEVEL,
1413 G_PKG_NAME, '100: review_upld_terms_id : '||p_review_upld_terms_id);
1414 END IF;
1415
1416 select decode(p_level,1,NULL,2,l_lvl1_concat_yn,3,l_lvl2_concat_yn,4,l_lvl3_concat_yn,5,l_lvl4_concat_yn,'N'),
1417 decode(p_level,1,l_lvl1_end_char ,2,l_lvl2_end_char ,3,l_lvl3_end_char ,4,l_lvl4_end_char ,5,l_lvl5_end_char ,NULL)
1418 into l_concat_yn ,l_end_char from dual;
1419
1420 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1421 FND_LOG.STRING(G_PROC_LEVEL,
1422 G_PKG_NAME, '110: l_concat_yn : '||l_concat_yn );
1423 FND_LOG.STRING(G_PROC_LEVEL,
1424 G_PKG_NAME, '110: l_end_char : '||l_end_char );
1425 END IF;
1426
1427 FOR cr in l_get_child_csr LOOP
1428 l_number := get_numbering_seq(p_level);
1429 IF l_concat_yn='Y' THEN
1430 l_label := p_parent_label||'.'||l_number||l_end_char;
1431 l_next_parent_number := p_parent_label||'.'||l_number;
1432 ElSE
1433 l_label := l_number||l_end_char;
1434 l_next_parent_number := l_number;
1435 END IF;
1436
1437 i := l_review_tbl.count+1;
1438 l_review_tbl(i).review_upld_terms_id := cr.review_upld_terms_id;
1439 l_review_tbl(i).type := cr.type;
1440 l_review_tbl(i).label := l_label;
1441
1442 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1443 FND_LOG.STRING(G_PROC_LEVEL,
1444 G_PKG_NAME, '120: i : '||i);
1445 FND_LOG.STRING(G_PROC_LEVEL,
1446 G_PKG_NAME, '120: l_review_tbl(i).id : '||l_review_tbl(i).review_upld_terms_id);
1447 FND_LOG.STRING(G_PROC_LEVEL,
1448 G_PKG_NAME, '120: l_review_tbl(i).type : '||l_review_tbl(i).type);
1449 FND_LOG.STRING(G_PROC_LEVEL,
1450 G_PKG_NAME, '120: l_review_tbl(i).label : '||l_review_tbl(i).label);
1451 END IF;
1452
1453 IF cr.type='SECTION' THEN
1454 IF p_level = 1 THEN
1455 l_lvl2_sequence := 0;
1456 ELSIF p_level = 2 THEN
1457 l_lvl3_sequence := 0;
1458 ELSIF p_level = 3 THEN
1459 l_lvl4_sequence := 0;
1460 ELSIF p_level = 4 THEN
1461 l_lvl5_sequence := 0;
1462 ELSE
1463 NULL;
1464 END IF;
1465
1466 IF l_no_of_levels > p_level then
1467 review_section_numbering(p_doc_type=>p_doc_type,
1468 p_doc_id => p_doc_id,
1469 p_level => p_level + 1,
1470 p_parent_label => l_next_parent_number,
1471 p_review_upld_terms_id => cr.review_upld_terms_id) ;
1472 END IF;
1473 END IF;
1474 END LOOP;
1475
1476 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1477 FND_LOG.STRING(G_PROC_LEVEL,
1478 G_PKG_NAME, '300: Leaving review_section_numbering ' );
1479 END IF;
1480
1481 EXCEPTION
1482
1483 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1484
1485 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1486 FND_LOG.STRING(G_EXCP_LEVEL,
1487 G_PKG_NAME, '400: Leaving review_section_numbering: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception');
1488 END IF;
1489
1490 raise;
1491
1492 WHEN OTHERS THEN
1493
1494 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1495 FND_LOG.STRING(G_EXCP_LEVEL,
1496 G_PKG_NAME, '500: Leaving review_section_numbering because of EXCEPTION: '||sqlerrm);
1497 END IF;
1498
1499 raise;
1500 END review_section_numbering;
1501
1502
1503 PROCEDURE apply_num_scheme_4_Review(
1504 p_api_version IN NUMBER,
1505 p_init_msg_list IN VARCHAR2 ,
1506
1507 x_return_status OUT NOCOPY VARCHAR2,
1508 x_msg_count OUT NOCOPY NUMBER,
1509 x_msg_data OUT NOCOPY VARCHAR2,
1510 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
1511 p_validation_string IN VARCHAR2,
1512 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1513 p_doc_type IN VARCHAR2,
1514 p_doc_id IN NUMBER,
1515 p_num_scheme_id IN NUMBER
1516 ) IS
1517
1518 l_api_version NUMBER :=1;
1519 l_api_name VARCHAR2(30) := 'apply_num_scheme_4_Review';
1520
1521 cursor l_get_child_csr IS
1522 SELECT review_upld_terms_id,display_seq, object_id
1523 from okc_review_upld_terms
1524 where document_type = p_doc_type
1525 AND document_id = p_doc_id
1526 AND object_type = 'SECTION'
1527 --and (action <> 'DELETED' OR action is null)
1528 AND new_parent_id = (select review_upld_terms_id
1529 from okc_review_upld_terms rt
1530 where rt.document_type = p_doc_type
1531 and rt.document_id = p_doc_id
1532 and rt.object_type = p_doc_type
1533 and rt.object_id = p_doc_id)
1534 Order by display_seq;
1535
1536 cursor l_get_num_scheme IS
1537 SELECT number_article_yn from OKC_NUMBER_SCHEMES_B
1538 where num_scheme_id=p_num_scheme_id;
1539
1540 cursor l_get_num_scheme_dtl IS
1541 SELECT num_sequence_code,sequence_level,concatenation_yn,end_character from OKC_NUMBER_SCHEME_DTLS
1542 where num_scheme_id=p_num_scheme_id;
1543
1544
1545
1546 CURSOR l_get_art_csr IS
1547 SELECT review_upld_terms_id,object_version_number, object_id
1548 FROM okc_review_upld_terms
1549 WHERE document_type=p_doc_type
1550 AND document_id=p_doc_id
1551 AND object_type = 'ARTICLE'
1552 and action <> 'DELETED';
1553
1554 l_label Varchar2(30) := NULL;
1555 l_number Varchar2(30) := NULL;
1556 i NUMBER;
1557
1558 CURSOR l_get_dtl_count IS
1559 SELECT COUNT(*)
1560 FROM OKC_NUMBER_SCHEME_DTLS
1561 WHERE num_scheme_id=p_num_scheme_id;
1562
1563 l_dtl_count NUMBER;
1564
1565
1566 BEGIN
1567
1568 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1569 FND_LOG.STRING(G_PROC_LEVEL,
1570 G_PKG_NAME, '100: Entered apply_num_scheme_4_Review');
1571 FND_LOG.STRING(G_PROC_LEVEL,
1572 G_PKG_NAME, '100: Parameter List ');
1573 FND_LOG.STRING(G_PROC_LEVEL,
1574 G_PKG_NAME, '100: p_api_version : '||p_api_version);
1575 FND_LOG.STRING(G_PROC_LEVEL,
1576 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list);
1577 FND_LOG.STRING(G_PROC_LEVEL,
1578 G_PKG_NAME, '100: p_commit : '||p_commit);
1579 FND_LOG.STRING(G_PROC_LEVEL,
1580 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit);
1581 FND_LOG.STRING(G_PROC_LEVEL,
1582 G_PKG_NAME, '100: p_validation_string : '||p_validation_string);
1583 FND_LOG.STRING(G_PROC_LEVEL,
1584 G_PKG_NAME, '100: p_doc_type : '||p_doc_type);
1585 FND_LOG.STRING(G_PROC_LEVEL,
1586 G_PKG_NAME, '100: p_doc_id : '||p_doc_id);
1587 FND_LOG.STRING(G_PROC_LEVEL,
1588 G_PKG_NAME, '100: p_num_scheme_id : '||p_num_scheme_id);
1589 END IF;
1590
1591 -- Initialize the global Structure Table
1592 l_review_tbl.DELETE;
1593
1594 -- Standard Start of API savepoint
1595 SAVEPOINT g_apply_num_scheme_4_Review;
1596 -- Standard call to check for call compatibility.
1597 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1599 END IF;
1600 -- Initialize message list if p_init_msg_list is set to TRUE.
1601 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1602 FND_MSG_PUB.initialize;
1603 END IF;
1604
1605 IF FND_API.To_Boolean( p_validate_commit ) THEN
1606
1607 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
1608 p_api_version => l_api_version,
1609 p_init_msg_list => FND_API.G_FALSE,
1610 p_doc_type => p_doc_type,
1611 p_doc_id => p_doc_id,
1612 p_validation_string => p_validation_string,
1613 x_return_status => x_return_status,
1614 x_msg_data => x_msg_data,
1615 x_msg_count => x_msg_count) ) THEN
1616
1617 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1618 FND_LOG.STRING(G_PROC_LEVEL,
1619 G_PKG_NAME, '110: Issue with document header Record.Cannot commit' );
1620 END IF;
1621 RAISE FND_API.G_EXC_ERROR ;
1622 END IF;
1623 END IF;
1624
1625 -- Initialize API return status to success
1626 x_return_status := FND_API.G_RET_STS_SUCCESS;
1627
1628 -- if Count of Numbering Scheme Detail is 0 , then this is the no numbering scheme
1629 -- in this case we need to remove the label from sections and articles table
1630 OPEN l_get_dtl_count;
1631 FETCH l_get_dtl_count INTO l_dtl_count;
1632 CLOSE l_get_dtl_count;
1633
1634 l_no_of_levels := l_dtl_count;
1635
1636 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1637 FND_LOG.STRING(G_PROC_LEVEL,
1638 G_PKG_NAME, '110: Numbering Scheme Detail Count : '||l_dtl_count );
1639 END IF;
1640
1641 UPDATE okc_review_upld_terms
1642 SET label = NULL
1643 WHERE document_type = p_doc_type
1644 AND document_id = p_doc_id ;
1645
1646 IF NVL(l_dtl_count,0) > 0 THEN
1647 -- dtl count > 0
1648
1649 OPEN l_get_num_scheme ;
1650 FETCH l_get_num_scheme INTO l_number_article_yn ;
1651 IF l_get_num_scheme%NOTFOUND THEN
1652 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1653 END IF;
1654 CLOSE l_get_num_scheme ;
1655
1656
1657 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1658 FND_LOG.STRING(G_PROC_LEVEL,
1659 G_PKG_NAME, '120: l_number_article_yn : '||l_number_article_yn );
1660 END IF;
1661
1662 FOR CR in l_get_num_scheme_dtl LOOP
1663 IF cr.sequence_level=1 THEN
1664 l_lvl1_seq_code := cr.num_sequence_code;
1665 l_lvl1_concat_yn := cr.concatenation_yn;
1666 l_lvl1_end_char := cr.end_character;
1667 ELSIF cr.sequence_level=2 THEN
1668 l_lvl2_seq_code := cr.num_sequence_code;
1669 l_lvl2_concat_yn := cr.concatenation_yn;
1670 l_lvl2_end_char := cr.end_character;
1671 ELSIF cr.sequence_level=3 THEN
1672 l_lvl3_seq_code := cr.num_sequence_code;
1673 l_lvl3_concat_yn := cr.concatenation_yn;
1674 l_lvl3_end_char := cr.end_character;
1675 ELSIF cr.sequence_level=4 THEN
1676 l_lvl4_seq_code := cr.num_sequence_code;
1677 l_lvl4_concat_yn := cr.concatenation_yn;
1678 l_lvl4_end_char := cr.end_character;
1679 ELSIF cr.sequence_level=5 THEN
1680 l_lvl5_seq_code := cr.num_sequence_code;
1681 l_lvl5_concat_yn := cr.concatenation_yn;
1682 l_lvl5_end_char := cr.end_character;
1683 END IF;
1684 END LOOP;
1685
1686
1687 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1688 FND_LOG.STRING(G_PROC_LEVEL,
1689 G_PKG_NAME, '130: Sequence Level 1 ');
1690 FND_LOG.STRING(G_PROC_LEVEL,
1691 G_PKG_NAME, '130: l_lvl1_seq_code : '||l_lvl1_seq_code);
1692 FND_LOG.STRING(G_PROC_LEVEL,
1693 G_PKG_NAME, '130: l_lvl1_concat_yn : '||l_lvl1_concat_yn);
1694 FND_LOG.STRING(G_PROC_LEVEL,
1695 G_PKG_NAME, '130: l_lvl1_end_char : '||l_lvl1_end_char);
1696 FND_LOG.STRING(G_PROC_LEVEL,
1697 G_PKG_NAME, '130: Sequence Level 2 ');
1698 FND_LOG.STRING(G_PROC_LEVEL,
1699 G_PKG_NAME, '130: l_lvl2_seq_code : '||l_lvl2_seq_code);
1700 FND_LOG.STRING(G_PROC_LEVEL,
1701 G_PKG_NAME, '130: l_lvl2_concat_yn : '||l_lvl2_concat_yn);
1702 FND_LOG.STRING(G_PROC_LEVEL,
1703 G_PKG_NAME, '130: l_lvl2_end_char : '||l_lvl2_end_char);
1704 FND_LOG.STRING(G_PROC_LEVEL,
1705 G_PKG_NAME, '130: Sequence Level 3 ');
1706 FND_LOG.STRING(G_PROC_LEVEL,
1707 G_PKG_NAME, '130: l_lvl3_seq_code : '||l_lvl3_seq_code);
1708 FND_LOG.STRING(G_PROC_LEVEL,
1709 G_PKG_NAME, '130: l_lvl3_concat_yn : '||l_lvl3_concat_yn);
1710 FND_LOG.STRING(G_PROC_LEVEL,
1711 G_PKG_NAME, '130: l_lvl3_end_char : '||l_lvl3_end_char);
1712 FND_LOG.STRING(G_PROC_LEVEL,
1713 G_PKG_NAME, '130: Sequence Level 4 ');
1714 FND_LOG.STRING(G_PROC_LEVEL,
1715 G_PKG_NAME, '130: l_lvl4_seq_code : '||l_lvl4_seq_code);
1716 FND_LOG.STRING(G_PROC_LEVEL,
1717 G_PKG_NAME, '130: l_lvl4_concat_yn : '||l_lvl4_concat_yn);
1718 FND_LOG.STRING(G_PROC_LEVEL,
1719 G_PKG_NAME, '130: l_lvl4_end_char : '||l_lvl4_end_char);
1720 FND_LOG.STRING(G_PROC_LEVEL,
1721 G_PKG_NAME, '130: Sequence Level 5 ');
1722 FND_LOG.STRING(G_PROC_LEVEL,
1723 G_PKG_NAME, '130: l_lvl5_seq_code : '||l_lvl5_seq_code);
1724 FND_LOG.STRING(G_PROC_LEVEL,
1725 G_PKG_NAME, '130: l_lvl5_concat_yn : '||l_lvl5_concat_yn);
1726 FND_LOG.STRING(G_PROC_LEVEL,
1727 G_PKG_NAME, '130: l_lvl5_end_char : '||l_lvl5_end_char);
1728 END IF;
1729
1730 l_lvl1_sequence :=0;
1731 l_lvl2_sequence :=0;
1732 l_lvl3_sequence :=0;
1733 l_lvl4_sequence :=0;
1734 l_lvl5_sequence :=0;
1735
1736 FOR cr in l_get_child_csr LOOP
1737
1738 l_number := get_numbering_seq(p_level => 1);
1739
1740
1741 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1742 FND_LOG.STRING(G_PROC_LEVEL,
1743 G_PKG_NAME, '140: l_number : '||l_number );
1744 END IF;
1745
1746 l_label := l_number||l_lvl1_end_char;
1747
1748
1749 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1750 FND_LOG.STRING(G_PROC_LEVEL,
1751 G_PKG_NAME, '150: l_label : '||l_label );
1752 END IF;
1753
1754 i := l_review_tbl.count+1;
1755 l_review_tbl(i).review_upld_terms_id := cr.review_upld_terms_id;
1756 l_review_tbl(i).object_id := cr.object_id;
1757 l_review_tbl(i).type := 'SECTION';
1758 l_review_tbl(i).label := l_label;
1759
1760 l_lvl2_sequence := 0;
1761
1762 IF l_no_of_levels > 1 THEN
1763 review_section_numbering(p_doc_type=>p_doc_type,
1764 p_doc_id => p_doc_id,
1765 p_level => 2,
1766 p_parent_label => l_number,
1767 p_review_upld_terms_id => cr.review_upld_terms_id) ;
1768 END IF;
1769
1770 END LOOP;
1771
1772 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1773 FND_LOG.STRING(G_PROC_LEVEL,
1774 G_PKG_NAME, '160: Count of l_review_tbl : '||l_review_tbl.count );
1775 END IF;
1776
1777
1778 IF l_review_tbl.count > 0 THEN
1779 For k in l_review_tbl.FIRST..l_review_tbl.LAST LOOP
1780
1781 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1782 FND_LOG.STRING(G_PROC_LEVEL,
1783 G_PKG_NAME, '170: Calling OKC_REVIEW_UPLD_TERMS_PVT.update_row');
1784 FND_LOG.STRING(G_PROC_LEVEL,
1785 G_PKG_NAME, '170: l_review_tbl(k).id : '||l_review_tbl(k).review_upld_terms_id);
1786 FND_LOG.STRING(G_PROC_LEVEL,
1787 G_PKG_NAME, '170: l_review_tbl(k).label : '||l_review_tbl(k).label);
1788 END IF;
1789
1790 OKC_REVIEW_UPLD_TERMS_PVT.update_row(
1791 x_return_status => x_return_status,
1792 p_review_upld_terms_id => l_review_tbl(k).review_upld_terms_id,
1793 p_label => l_review_tbl(k).label,
1794 p_object_version_number => NULL);
1795
1796 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1797 FND_LOG.STRING(G_PROC_LEVEL,
1798 G_PKG_NAME, '170: After Calling OKC_REVIEW_UPLD_TERMS_PVT.update_row x_return_status : '||x_return_status );
1799 END IF;
1800
1801 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1802 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1803 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1804 RAISE FND_API.G_EXC_ERROR ;
1805 END IF;
1806 END LOOP;
1807 END IF; -- l_review_tbl.count > 0
1808 END IF; -- detail count is 0 i.e no numbering scheme
1809
1810 -- Standard check of p_commit
1811 IF FND_API.To_Boolean( p_commit ) THEN
1812 COMMIT WORK;
1813 END IF;
1814 -- Standard call to get message count and if count is 1, get message info.
1815 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1816
1817
1818 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1819 FND_LOG.STRING(G_PROC_LEVEL,
1820 G_PKG_NAME, '2000: Leaving apply_num_scheme_4_Review' );
1821 END IF;
1822
1823 EXCEPTION
1824 WHEN FND_API.G_EXC_ERROR THEN
1825
1826 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1827 FND_LOG.STRING(G_EXCP_LEVEL,
1828 G_PKG_NAME, '2400: Leaving apply_num_scheme_4_Review: OKC_API.G_EXCEPTION_ERROR Exception' );
1829 END IF;
1830
1831 ROLLBACK TO g_apply_num_scheme_4_Review;
1832 x_return_status := G_RET_STS_ERROR ;
1833 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1834
1835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1836
1837 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1838 FND_LOG.STRING(G_EXCP_LEVEL,
1839 G_PKG_NAME, '2400: Leaving apply_num_scheme_4_Review: OKC_API.G_EXCEPTION_ERROR Exception' );
1840 END IF;
1841
1842 ROLLBACK TO g_apply_num_scheme_4_Review;
1843 x_return_status := G_RET_STS_UNEXP_ERROR ;
1844 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1845
1846 WHEN OTHERS THEN
1847 IF (l_debug = 'Y') THEN
1848 okc_debug.log('2400: Leaving apply_num_scheme_4_Review: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1849 END IF;
1850
1851 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1852 FND_LOG.STRING(G_EXCP_LEVEL,
1853 G_PKG_NAME, '2400: Leaving apply_num_scheme_4_Review: OKC_API.G_EXCEPTION_ERROR Exception' );
1854 END IF;
1855
1856 ROLLBACK TO g_apply_num_scheme_4_Review;
1857 x_return_status := G_RET_STS_UNEXP_ERROR ;
1858 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1859 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1860 END IF;
1861 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1862
1863 END apply_num_scheme_4_Review;
1864
1865
1866 END OKC_NUMBER_SCHEME_GRP;