[Home] [Help]
PACKAGE BODY: APPS.OKC_TERMS_MULTIREC_GRP
Source
1 PACKAGE BODY OKC_TERMS_MULTIREC_GRP AS
2 /* $Header: OKCGMULB.pls 120.19 2011/12/09 13:33:24 serukull ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
10 ---------------------------------------------------------------------------
11 -- GLOBAL VARIABLES
12 ---------------------------------------------------------------------------
13 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_TERMS_MULTIREC_GRP';
14 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
15
16 ------------------------------------------------------------------------------
17 -- GLOBAL CONSTANTS
18 ------------------------------------------------------------------------------
19 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
20 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
21 G_MISS_NUM CONSTANT NUMBER := FND_API.G_MISS_NUM;
22 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
23 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
24 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
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 G_AMEND_CODE_ADDED CONSTANT VARCHAR2(30) := 'ADDED';
29 G_AMEND_CODE_UPDATED CONSTANT VARCHAR2(30) := 'UPDATED';
30
31 G_DBG_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
32 G_PROC_LEVEL NUMBER := FND_LOG.LEVEL_PROCEDURE;
33 G_EXCP_LEVEL NUMBER := FND_LOG.LEVEL_EXCEPTION;
34
35 TYPE scn_child_rec_type IS RECORD (
36 id OKC_K_ARTICLES_B.id%type,
37 display_sequence OKC_K_ARTICLES_B.display_sequence%type,
38 OBJECT_TYPE VARCHAR2(30)
39 );
40
41 TYPE scn_child_tbl_type IS TABLE OF scn_child_rec_type INDEX BY BINARY_INTEGER;
42
43 PROCEDURE create_article(
44 p_api_version IN NUMBER,
45 p_init_msg_list IN VARCHAR2,
46 p_mode IN VARCHAR2,
47 p_validation_level IN NUMBER,
48 p_validate_commit IN VARCHAR2,
49 p_validation_string IN VARCHAR2,
50 p_commit IN VARCHAR2 ,
51 p_ref_type IN VARCHAR2 := 'SECTION', -- 'ARTICLE' or 'SECTION'
52 p_ref_id IN NUMBER, -- Id of okc_sections_b or okc_articles_b depending upon ref type
53 p_doc_type IN VARCHAR2,
54 p_doc_id IN NUMBER,
55
56 p_kart_tbl IN kart_tbl_type,
57 x_kart_tbl OUT NOCOPY kart_tbl_type,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2
61 ) IS
62
63 l_api_version CONSTANT NUMBER := 1;
64 l_api_name CONSTANT VARCHAR2(30) := 'create_article';
65 l_id NUMBER;
66 l_scn_id NUMBER;
67 i NUMBER := 0;
68 l_ref_count NUMBER := 0;
69 l_ref_sequence NUMBER := 0;
70 l_amendment_description OKC_K_ARTICLES_B.amendment_description%TYPE;
71 l_print_text_yn OKC_K_ARTICLES_B.amendment_description%TYPE;
72
73
74 CURSOR l_get_scn_child_csr(b_scn_id NUMBER) IS
75 SELECT ID,SECTION_SEQUENCE DISPLAY_SEQ,'SECTION' obj_type
76 FROM OKC_SECTIONS_B
77 WHERE document_type = p_doc_type
78 AND document_id = p_doc_id
79 AND scn_id=b_scn_id
80 UNION ALL
81 SELECT ID,DISPLAY_SEQUENCE DISPLAY_SEQ,'ARTICLE' obj_type
82 FROM OKC_K_ARTICLES_B
83 WHERE document_type = p_doc_type
84 AND document_id = p_doc_id
85 AND scn_id=b_scn_id
86 ORDER BY 2;
87
88 CURSOR l_get_scn_csr(b_cat_id NUMBER) IS
89 SELECT SCN_ID FROM OKC_K_ARTICLES_B
90 WHERE ID=b_cat_id;
91
92 scn_child_rec l_get_scn_child_csr%ROWTYPE;
93 scn_child_tbl scn_child_tbl_type;
94
95 BEGIN
96
97 /*IF (l_debug = 'Y') THEN
98 okc_debug.log('100: Entered create_article', 2);
99 okc_debug.log('100: Parameter List ', 2);
100 okc_debug.log('100: p_api_version : '||p_api_version, 2);
101 okc_debug.log('100: p_init_msg_list : '||p_init_msg_list, 2);
102 okc_debug.log('100: p_mode : '||p_mode, 2);
103 okc_debug.log('100: p_validation_level : '||p_validation_level, 2);
104 okc_debug.log('100: p_validate_commit : '||p_validate_commit, 2);
105 okc_debug.log('100: p_validation_string : '||p_validation_string, 2);
106 okc_debug.log('100: p_commit : '||p_commit, 2);
107 okc_debug.log('100: p_ref_type : '||p_ref_type, 2);
108 okc_debug.log('100: p_ref_id : '||p_ref_id, 2);
109 okc_debug.log('100: p_doc_type : '||p_doc_type, 2);
110 okc_debug.log('100: p_doc_id : '||p_doc_id, 2);
111 END IF;*/
112
113 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
114 FND_LOG.STRING(G_PROC_LEVEL,
115 G_PKG_NAME, '100: Entered create_article' );
116 FND_LOG.STRING(G_PROC_LEVEL,
117 G_PKG_NAME, '100: Parameter List ' );
118 FND_LOG.STRING(G_PROC_LEVEL,
119 G_PKG_NAME, '100: p_api_version : '||p_api_version );
120 FND_LOG.STRING(G_PROC_LEVEL,
121 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
122 FND_LOG.STRING(G_PROC_LEVEL,
123 G_PKG_NAME, '100: p_mode : '||p_mode );
124 FND_LOG.STRING(G_PROC_LEVEL,
125 G_PKG_NAME, '100: p_validation_level : '||p_validation_level );
126 FND_LOG.STRING(G_PROC_LEVEL,
127 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
128 FND_LOG.STRING(G_PROC_LEVEL,
129 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
130 FND_LOG.STRING(G_PROC_LEVEL,
131 G_PKG_NAME, '100: p_commit : '||p_commit );
132 FND_LOG.STRING(G_PROC_LEVEL,
133 G_PKG_NAME, '100: p_ref_type : '||p_ref_type );
134 FND_LOG.STRING(G_PROC_LEVEL,
135 G_PKG_NAME, '100: p_ref_id : '||p_ref_id );
136 FND_LOG.STRING(G_PROC_LEVEL,
137 G_PKG_NAME, '100: p_doc_type : '||p_doc_type );
138 FND_LOG.STRING(G_PROC_LEVEL,
139 G_PKG_NAME, '100: p_doc_id : '||p_doc_id );
140 END IF;
141
142 -- Standard Start of API savepoint
143 SAVEPOINT g_create_article_GRP;
144 -- Standard call to check for call compatibility.
145 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147 END IF;
148 -- Initialize message list if p_init_msg_list is set to TRUE.
149 IF FND_API.to_Boolean( p_init_msg_list ) THEN
150 FND_MSG_PUB.initialize;
151 END IF;
152
153 IF p_kart_tbl.COUNT >0
154 AND FND_API.To_Boolean( p_validate_commit ) THEN
155
156 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
157 p_api_version => l_api_version,
158 p_init_msg_list => FND_API.G_FALSE,
159 p_doc_type => p_doc_type,
160 p_doc_id => p_doc_id,
161 p_validation_string => p_validation_string,
162 x_return_status => x_return_status,
163 x_msg_data => x_msg_data,
164 x_msg_count => x_msg_count) ) THEN
165
166 /*IF (l_debug = 'Y') THEN
167 okc_debug.log('110: Issue with document header Record.Cannot commit', 2);
168 END IF;*/
169
170 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
171 FND_LOG.STRING(G_PROC_LEVEL,
172 G_PKG_NAME, '110: Issue with document header Record.Cannot commit' );
173 END IF;
174 RAISE FND_API.G_EXC_ERROR ;
175 END IF;
176 END IF;
177
178 -- Initialize API return status to success
179 x_return_status := FND_API.G_RET_STS_SUCCESS;
180
181 IF p_kart_tbl.COUNT > 0 THEN
182
183 IF p_ref_type='ARTICLE' THEN
184
185 OPEN l_get_scn_csr(p_ref_id);
186 FETCH l_get_scn_csr INTO l_scn_id;
187 CLOSE l_get_scn_csr;
188
189 ELSIF p_ref_type='SECTION' THEN
190 l_scn_id := p_ref_id;
191 ELSE
192 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
193
194 END IF;
195
196 OPEN l_get_scn_child_csr(l_scn_id);
197 LOOP
198 FETCH l_get_scn_child_csr INTO scn_child_rec;
199 EXIT WHEN l_get_scn_child_csr%NOTFOUND;
200 i := i+1;
201 scn_child_tbl(i).id := scn_child_rec.id;
202 scn_child_tbl(i).display_sequence := scn_child_rec.display_seq;
203 scn_child_tbl(i).object_type := scn_child_rec.obj_type;
204
205 IF p_ref_type='ARTICLE' AND scn_child_tbl(i).object_type='ARTICLE' AND scn_child_tbl(i).id = p_ref_id THEN
206 l_ref_count := i;
207 END IF;
208
209 END LOOP;
210 CLOSE l_get_scn_child_csr;
211
212
213 IF p_ref_type ='SECTION' THEN
214 l_ref_count := i;
215 END IF;
216
217 IF i=0 THEN
218 l_ref_sequence := 0;
219 ELSE
220 l_ref_sequence := scn_child_tbl(l_ref_count).display_sequence;
221 END IF;
222
223 FOR i IN p_kart_tbl.FIRST..p_kart_tbl.LAST LOOP
224
225 /*IF (l_debug = 'Y') THEN
226 okc_debug.log('110: Creating Article No '||p_kart_tbl(i).sav_sae_id, 2);
227 END IF;*/
228
229 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
230 FND_LOG.STRING(G_PROC_LEVEL,
231 G_PKG_NAME, '110: Creating Article No '||p_kart_tbl(i).sav_sae_id );
232 END IF;
233
234 l_ref_sequence := l_ref_sequence + 10;
235
236 IF p_mode='AMEND' THEN
237 l_amendment_description := p_kart_tbl(i).amendment_description;
238 l_print_text_yn := p_kart_tbl(i).print_text_yn;
239 ELSE
240 l_amendment_description := NULL;
241 l_print_text_yn := 'N';
242 END IF;
243
244 OKC_K_ARTICLES_GRP.create_article(
245 p_api_version => 1,
246 p_init_msg_list => FND_API.G_FALSE,
247 p_validation_level => 0,
248 p_mode => p_mode,
249 x_return_status => x_return_status,
250 x_msg_count => x_msg_count,
251 x_msg_data => x_msg_data,
252 p_id => NULL,
253 p_sav_sae_id => p_kart_tbl(i).sav_sae_id,
254 p_document_type => p_doc_type,
255 p_document_id => p_doc_id,
256 p_scn_id => l_scn_id,
257 p_article_version_id => p_kart_tbl(i).article_version_id,
258 p_display_sequence => l_ref_sequence,
259 p_amendment_description => l_amendment_description,
260 p_print_text_yn => l_print_text_yn,
261 p_ref_article_version_id=> p_kart_tbl(i).ref_article_version_id,
262 p_ref_article_id => p_kart_tbl(i).ref_article_id,
263 x_id => l_id
264 );
265
266 --------------------------------------------
267 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
269 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
270 RAISE FND_API.G_EXC_ERROR ;
271 END IF;
272 --------------------------------------------
273 x_kart_tbl(i).id := l_id;
274 END LOOP;
275 END IF;
276
277 IF scn_child_tbl.COUNT > 0 THEN
278 FOR k IN scn_child_tbl.FIRST..scn_child_tbl.LAST LOOP
279 IF k > l_ref_count THEN
280
281 l_ref_sequence := l_ref_sequence + 10;
282 IF scn_child_tbl(k).object_type='ARTICLE' THEN
283
284 /*IF (l_debug = 'Y') THEN
285 okc_debug.log('120: Updating Display Sequence of cat_id '||scn_child_tbl(k).id, 2);
286 END IF;*/
287
288 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
289 FND_LOG.STRING(G_PROC_LEVEL,
290 G_PKG_NAME, '120: Updating Display Sequence of cat_id '||scn_child_tbl(k).id );
291 END IF;
292
293 OKC_K_ARTICLES_GRP.update_article(
294 p_api_version =>1,
295 p_init_msg_list => OKC_API.G_FALSE,
296 x_return_status => x_return_status,
297 x_msg_count => x_msg_count,
298 x_msg_data => x_msg_data,
299 p_id => scn_child_tbl(k).id,
300 p_display_sequence => l_ref_sequence,
301 p_object_version_number => Null
302 );
303 --------------------------------------------
304 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
306 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
307 RAISE FND_API.G_EXC_ERROR ;
308 END IF;
309 --------------------------------------------
310 ELSIF scn_child_tbl(k).object_type='SECTION' THEN
311
312 /*IF (l_debug = 'Y') THEN
313 okc_debug.log('1000: Updating Display Sequence of scn_id '||scn_child_tbl(k).id, 2);
314 END IF;*/
315
316 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
317 FND_LOG.STRING(G_PROC_LEVEL,
318 G_PKG_NAME, '1000: Updating Display Sequence of scn_id '||scn_child_tbl(k).id );
319 END IF;
320
321 OKC_TERMS_SECTIONS_GRP.update_section(
322 p_api_version =>1,
323 p_init_msg_list => OKC_API.G_FALSE,
324 x_return_status => x_return_status,
325 x_msg_count => x_msg_count,
326 x_msg_data => x_msg_data,
327 p_id => scn_child_tbl(k).id,
328 p_section_sequence => l_ref_sequence,
329 p_object_version_number =>Null
330 );
331 --------------------------------------------
332 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
334 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
335 RAISE FND_API.G_EXC_ERROR ;
336 END IF;
337
338 END IF; -- IF scn_child_tbl(k).object_type='ARTICLE' THEN
339 END IF;
340 END LOOP;
341 END IF;
342
343 -- Standard check of p_commit
344 IF FND_API.To_Boolean( p_commit ) THEN
345 COMMIT WORK;
346 END IF;
347
348 -- Standard call to get message count and if count is 1, get message info.
349 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
350
351 /*IF (l_debug = 'Y') THEN
352 okc_debug.log('200: Leaving create_article', 2);
353 END IF;*/
354
355 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
356 FND_LOG.STRING(G_PROC_LEVEL,
357 G_PKG_NAME, '200: Leaving create_article' );
358 END IF;
359
360 EXCEPTION
361 WHEN FND_API.G_EXC_ERROR THEN
362 /*IF (l_debug = 'Y') THEN
363 okc_debug.log('300: Leaving create_article: OKC_API.G_EXCEPTION_ERROR Exception', 2);
364 END IF;*/
365
366 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
367 FND_LOG.STRING(G_EXCP_LEVEL,
368 G_PKG_NAME, '300: Leaving create_article: OKC_API.G_EXCEPTION_ERROR Exception' );
369 END IF;
370
371 IF l_get_scn_child_csr%ISOPEN THEN
372 CLOSE l_get_scn_child_csr;
373 END IF;
374
375 IF l_get_scn_csr%ISOPEN THEN
376 CLOSE l_get_scn_csr;
377 END IF;
378
379 ROLLBACK TO g_create_article_GRP;
380 x_return_status := G_RET_STS_ERROR ;
381 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
382
383 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
384 /*IF (l_debug = 'Y') THEN
385 okc_debug.log('400: Leaving create_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
386 END IF;*/
387
388 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
389 FND_LOG.STRING(G_EXCP_LEVEL,
390 G_PKG_NAME, '400: Leaving create_article: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
391 END IF;
392
393 IF l_get_scn_child_csr%ISOPEN THEN
394 CLOSE l_get_scn_child_csr;
395 END IF;
396
397 IF l_get_scn_csr%ISOPEN THEN
398 CLOSE l_get_scn_csr;
399 END IF;
400
401 ROLLBACK TO g_create_article_GRP;
402 x_return_status := G_RET_STS_UNEXP_ERROR ;
403 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
404
405 WHEN OTHERS THEN
406 /*IF (l_debug = 'Y') THEN
407 okc_debug.log('500: Leaving create_article because of EXCEPTION: '||sqlerrm, 2);
408 END IF;*/
409
410 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
411 FND_LOG.STRING(G_EXCP_LEVEL,
412 G_PKG_NAME, '500: Leaving create_article because of EXCEPTION: '||sqlerrm );
413 END IF;
414
415 IF l_get_scn_child_csr%ISOPEN THEN
416 CLOSE l_get_scn_child_csr;
417 END IF;
418
419 IF l_get_scn_csr%ISOPEN THEN
420 CLOSE l_get_scn_csr;
421 END IF;
422
423 ROLLBACK TO g_create_article_GRP;
424 x_return_status := G_RET_STS_UNEXP_ERROR ;
425 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
426 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
427 END IF;
428 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
429
430 END create_article;
431
432 PROCEDURE update_article_variable(
433 p_api_version IN NUMBER,
434 p_init_msg_list IN VARCHAR2 ,
435 p_validation_level IN NUMBER ,
436 p_validate_commit IN VARCHAR2 ,
437 p_validation_string IN VARCHAR2,
438 p_commit IN VARCHAR2 ,
439 p_art_var_tbl IN art_var_tbl_type,
440 p_mode IN VARCHAR2 ,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_msg_count OUT NOCOPY NUMBER,
443 x_msg_data OUT NOCOPY VARCHAR2,
444 p_lock_terms_yn IN VARCHAR2
445 ) IS
446
447 l_api_version CONSTANT NUMBER := 1;
448 l_api_name CONSTANT VARCHAR2(30) := 'update_article_variable';
449 l_doc_type VARCHAR2(30);
450 l_doc_id NUMBER;
451 l_dummy NUMBER := FND_API.G_MISS_NUM;
452
453 CURSOR l_doc_csr(b_cat_id NUMBER) IS
454 SELECT DOCUMENT_TYPE,DOCUMENT_ID
455 FROM OKC_K_ARTICLES_B
456 WHERE id=b_cat_id;
457
458 CURSOR l_amend_art_csr(b_cat_id NUMBER) IS
459 SELECT object_version_number
460 FROM OKC_K_ARTICLES_B
461 WHERE ID=b_cat_id
462 AND AMENDMENT_OPERATION_CODE<>G_AMEND_CODE_UPDATED;
463
464 BEGIN
465
466 /*IF (l_debug = 'Y') THEN
467 okc_debug.log('100: Entered update_article_variable', 2);
468 okc_debug.log('100: Parameter List ', 2);
469 okc_debug.log('100: p_api_version : '||p_api_version, 2);
470 okc_debug.log('100: p_init_msg_list : '||p_init_msg_list, 2);
471 okc_debug.log('100: p_mode : '||p_mode, 2);
472 okc_debug.log('100: p_validation_level : '||p_validation_level, 2);
473 okc_debug.log('100: p_validate_commit : '||p_validate_commit, 2);
474 okc_debug.log('100: p_validation_string : '||p_validation_string, 2);
475 okc_debug.log('100: p_commit : '||p_commit, 2);
476 END IF;*/
477
478 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
479 FND_LOG.STRING(G_PROC_LEVEL,
480 G_PKG_NAME, '100: Entered update_article_variable' );
481 FND_LOG.STRING(G_PROC_LEVEL,
482 G_PKG_NAME, '100: Parameter List ' );
483 FND_LOG.STRING(G_PROC_LEVEL,
484 G_PKG_NAME, '100: p_api_version : '||p_api_version );
485 FND_LOG.STRING(G_PROC_LEVEL,
486 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
487 FND_LOG.STRING(G_PROC_LEVEL,
488 G_PKG_NAME, '100: p_mode : '||p_mode );
489 FND_LOG.STRING(G_PROC_LEVEL,
490 G_PKG_NAME, '100: p_validation_level : '||p_validation_level );
491 FND_LOG.STRING(G_PROC_LEVEL,
492 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
493 FND_LOG.STRING(G_PROC_LEVEL,
494 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
495 FND_LOG.STRING(G_PROC_LEVEL,
496 G_PKG_NAME, '100: p_commit : '||p_commit );
497 END IF;
498
499 -- Standard Start of API savepoint
500 SAVEPOINT g_update_article_variable_GRP;
501 -- Standard call to check for call compatibility.
502 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
503 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
504 END IF;
505 -- Initialize message list if p_init_msg_list is set to TRUE.
506 IF FND_API.to_Boolean( p_init_msg_list ) THEN
507 FND_MSG_PUB.initialize;
508 END IF;
509
510 IF p_art_var_tbl.FIRST IS NOT NULL
511 AND FND_API.To_Boolean( p_validate_commit ) THEN
512
513 OPEN l_doc_csr(p_art_var_tbl(1).cat_id);
514 FETCH l_doc_csr INTO l_doc_type,l_doc_id;
515 CLOSE l_doc_csr;
516 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
517 p_api_version => l_api_version,
518 p_init_msg_list => FND_API.G_FALSE,
519 p_doc_type => l_doc_type,
520 p_doc_id => l_doc_id,
521 p_validation_string => p_validation_string,
522 x_return_status => x_return_status,
523 x_msg_data => x_msg_data,
524 x_msg_count => x_msg_count) ) THEN
525
526 /*IF (l_debug = 'Y') THEN
527 okc_debug.log('110: Issue with document header Record.Cannot commit', 2);
528 END IF;*/
529
530 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
531 FND_LOG.STRING(G_PROC_LEVEL,
532 G_PKG_NAME, '110: Issue with document header Record.Cannot commit' );
533 END IF;
534 RAISE FND_API.G_EXC_ERROR ;
535 END IF;
536 END IF;
537
538 -- Initialize API return status to success
539 x_return_status := FND_API.G_RET_STS_SUCCESS;
540
541 IF p_art_var_tbl.FIRST IS NOT NULL THEN
542 FOR i IN p_art_var_tbl.FIRST..p_art_var_tbl.LAST LOOP
543 /*IF (l_debug = 'Y') THEN
544 okc_debug.log('115: Updating variable '||p_art_var_tbl(i).variable_code, 2);
545 END IF;*/
546
547 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
548 FND_LOG.STRING(G_PROC_LEVEL,
549 G_PKG_NAME, '115: Updating variable '||p_art_var_tbl(i).variable_code );
550 END IF;
551
552 OKC_K_ART_VARIABLES_PVT.update_row(
553 p_validation_level => 0,
554 x_return_status => x_return_status,
555 p_cat_id => p_art_var_tbl(i).cat_id,
556 p_variable_code => p_art_var_tbl(i).variable_code,
557 p_variable_type => p_art_var_tbl(i).variable_type,
558 p_external_yn => p_art_var_tbl(i).external_yn,
559 p_variable_value_id => p_art_var_tbl(i).variable_value_id,
560 p_variable_value => p_art_var_tbl(i).variable_value,
561 p_attribute_value_set_id => p_art_var_tbl(i).attribute_value_set_id,
562 p_object_version_number => p_art_var_tbl(i).object_version_number
563 );
564
565 --------------------------------------------
566 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
567 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
568 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
569 RAISE FND_API.G_EXC_ERROR ;
570 END IF;
571 --------------------------------------------
572 IF p_mode ='AMEND' THEN
573 l_dummy := FND_API.G_MISS_NUM;
574 OPEN l_amend_art_csr(p_art_var_tbl(i).cat_id);
575 FETCH l_amend_art_csr into l_dummy;
576 CLOSE l_amend_art_csr;
577 IF l_dummy <> FND_API.G_MISS_NUM THEN
578
579 /*IF (l_debug = 'Y') THEN
580 okc_debug.log('125: Updating Amend Operation Code for cat_id '||p_art_var_tbl(i).cat_id, 2);
581 END IF;*/
582
583 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
584 FND_LOG.STRING(G_PROC_LEVEL,
585 G_PKG_NAME, '125: Updating Amend Operation Code for cat_id '||p_art_var_tbl(i).cat_id );
586 END IF;
587 OKC_K_ARTICLES_GRP.Update_article(
588 p_api_version => 1,
589 p_init_msg_list => FND_API.G_FALSE,
590 p_validation_level => 0,
591 p_mode => p_mode,
592 x_msg_count => x_msg_count,
593 x_msg_data => x_msg_data,
594 x_return_status => x_return_status,
595 p_id => p_art_var_tbl(i).cat_id,
596 p_object_version_number => l_dummy,
597 p_lock_terms_yn => p_lock_terms_yn
598 );
599
600 --------------------------------------------
601 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
603 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
604 RAISE FND_API.G_EXC_ERROR ;
605 END IF;
606 --------------------------------------------
607 END IF;
608 END IF;
609 END LOOP;
610 END IF;
611
612 -- Standard check of p_commit
613 IF FND_API.To_Boolean( p_commit ) THEN
614 COMMIT WORK;
615 END IF;
616
617 -- Standard call to get message count and if count is 1, get message info.
618 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
619
620 /*IF (l_debug = 'Y') THEN
621 okc_debug.log('200: Leaving update_article_variable', 2);
622 END IF;*/
623
624 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
625 FND_LOG.STRING(G_PROC_LEVEL,
626 G_PKG_NAME, '200: Leaving update_article_variable' );
627 END IF;
628
629 EXCEPTION
630 WHEN FND_API.G_EXC_ERROR THEN
631 /*IF (l_debug = 'Y') THEN
632 okc_debug.log('300: Leaving update_article_variable: OKC_API.G_EXCEPTION_ERROR Exception', 2);
633 END IF;*/
634
635 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
636 FND_LOG.STRING(G_EXCP_LEVEL,
637 G_PKG_NAME, '300: Leaving update_article_variable: OKC_API.G_EXCEPTION_ERROR Exception' );
638 END IF;
639
640 IF l_doc_csr%ISOPEN THEN
641 CLOSE l_doc_csr;
642 END IF;
643
644 ROLLBACK TO g_update_article_variable_GRP;
645 x_return_status := G_RET_STS_ERROR ;
646 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
647
648 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
649 /*IF (l_debug = 'Y') THEN
650 okc_debug.log('400: Leaving update_article_variable: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
651 END IF;*/
652
653 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
654 FND_LOG.STRING(G_EXCP_LEVEL,
655 G_PKG_NAME, '400: Leaving update_article_variable: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
656 END IF;
657
658 IF l_doc_csr%ISOPEN THEN
659 CLOSE l_doc_csr;
660 END IF;
661
662 ROLLBACK TO g_update_article_variable_GRP;
663 x_return_status := G_RET_STS_UNEXP_ERROR ;
664 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
665
666 WHEN OTHERS THEN
667 /*IF (l_debug = 'Y') THEN
668 okc_debug.log('500: Leaving update_article_variable because of EXCEPTION: '||sqlerrm, 2);
669 END IF;*/
670
671 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
672 FND_LOG.STRING(G_EXCP_LEVEL,
673 G_PKG_NAME, '500: Leaving update_article_variable because of EXCEPTION: '||sqlerrm );
674 END IF;
675
676 IF l_doc_csr%ISOPEN THEN
677 CLOSE l_doc_csr;
678 END IF;
679
680 ROLLBACK TO g_update_article_variable_GRP;
681 x_return_status := G_RET_STS_UNEXP_ERROR ;
682 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
683 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
684 END IF;
685 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
686
687 END update_article_variable;
688
689 PROCEDURE update_structure(
690 p_api_version IN NUMBER,
691 p_init_msg_list IN VARCHAR2 ,
692 p_validation_level IN NUMBER ,
693 p_validate_commit IN VARCHAR2 ,
694 p_validation_string IN VARCHAR2,
695 p_commit IN VARCHAR2 ,
696 p_structure_tbl IN structure_tbl_type,
697 x_return_status OUT NOCOPY VARCHAR2,
698 x_msg_count OUT NOCOPY NUMBER,
699 x_msg_data OUT NOCOPY VARCHAR2
700 ) IS
701
702 l_api_version CONSTANT NUMBER := 1;
703 l_api_name CONSTANT VARCHAR2(30) := 'update_structure';
704 l_doc_type VARCHAR2(30);
705 l_doc_id NUMBER;
706
707 CURSOR l_doc_art_csr(b_id NUMBER) IS
708 SELECT DOCUMENT_TYPE,DOCUMENT_ID
709 FROM OKC_K_ARTICLES_B
710 WHERE id=b_id;
711
712 CURSOR l_doc_scn_csr(b_id NUMBER) IS
713 SELECT DOCUMENT_TYPE,DOCUMENT_ID
714 FROM OKC_SECTIONS_B
715 WHERE id=b_id;
716
717 BEGIN
718
719 /*IF (l_debug = 'Y') THEN
720 okc_debug.log('100: Entered update_structure', 2);
721 okc_debug.log('100: Parameter List ', 2);
722 okc_debug.log('100: p_api_version : '||p_api_version, 2);
723 okc_debug.log('100: p_init_msg_list : '||p_init_msg_list, 2);
724 okc_debug.log('100: p_validation_level : '||p_validation_level, 2);
725 okc_debug.log('100: p_validate_commit : '||p_validate_commit, 2);
726 okc_debug.log('100: p_validation_string : '||p_validation_string, 2);
727 okc_debug.log('100: p_commit : '||p_commit, 2);
728 END IF;*/
729
730 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
731 FND_LOG.STRING(G_PROC_LEVEL,
732 G_PKG_NAME, '100: Entered update_structure' );
733 FND_LOG.STRING(G_PROC_LEVEL,
734 G_PKG_NAME, '100: Parameter List ' );
735 FND_LOG.STRING(G_PROC_LEVEL,
736 G_PKG_NAME, '100: p_api_version : '||p_api_version );
737 FND_LOG.STRING(G_PROC_LEVEL,
738 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
739 FND_LOG.STRING(G_PROC_LEVEL,
740 G_PKG_NAME, '100: p_validation_level : '||p_validation_level );
741 FND_LOG.STRING(G_PROC_LEVEL,
742 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
743 FND_LOG.STRING(G_PROC_LEVEL,
744 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
745 FND_LOG.STRING(G_PROC_LEVEL,
746 G_PKG_NAME, '100: p_commit : '||p_commit );
747 END IF;
748
749 -- Standard Start of API savepoint
750 SAVEPOINT g_update_structure_GRP;
751 -- Standard call to check for call compatibility.
752 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
754 END IF;
755
756 -- Initialize message list if p_init_msg_list is set to TRUE.
757 IF FND_API.to_Boolean( p_init_msg_list ) THEN
758 FND_MSG_PUB.initialize;
759 END IF;
760
761 -- Initialize API return status to success
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763
764 IF p_structure_tbl.FIRST IS NOT NULL
765 AND FND_API.To_Boolean( p_validate_commit ) THEN
766
767 IF p_structure_tbl(1).type ='ARTICLE' THEN
768 OPEN l_doc_art_csr(p_structure_tbl(1).id);
769 FETCH l_doc_art_csr INTO l_doc_type,l_doc_id;
770 CLOSE l_doc_art_csr;
771 END IF;
772
773 IF p_structure_tbl(1).type ='SECTION' THEN
774 OPEN l_doc_scn_csr(p_structure_tbl(1).id);
775 FETCH l_doc_scn_csr INTO l_doc_type,l_doc_id;
776 CLOSE l_doc_scn_csr;
777 END IF;
778
779 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
780 p_api_version => l_api_version,
781 p_init_msg_list => FND_API.G_FALSE,
782 p_doc_type => l_doc_type,
783 p_doc_id => l_doc_id,
784 x_return_status => x_return_status,
785 x_msg_data => x_msg_data,
786 x_msg_count => x_msg_count) ) THEN
787
788 /*IF (l_debug = 'Y') THEN
789 okc_debug.log('110: Issue with document header Record.Cannot commit', 2);
790 END IF;*/
791
792 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
793 FND_LOG.STRING(G_PROC_LEVEL,
794 G_PKG_NAME, '110: Issue with document header Record.Cannot commit' );
795 END IF;
796 RAISE FND_API.G_EXC_ERROR ;
797 END IF;
798 END IF;
799
800
801 IF p_structure_tbl.FIRST IS NOT NULL THEN
802
803 FOR i IN p_structure_tbl.FIRST..p_structure_tbl.LAST LOOP
804
805 IF p_structure_tbl(i).type = 'SECTION' THEN
806
807 OKC_TERMS_SECTIONS_GRP.update_section(
808 p_api_version => 1,
809 p_init_msg_list => FND_API.G_FALSE,
810 x_msg_count => x_msg_count,
811 x_msg_data => x_msg_data,
812 p_validation_level => 0,
813 x_return_status => x_return_status,
814 p_id => p_structure_tbl(i).id,
815 p_section_sequence => p_structure_tbl(i).display_sequence,
816 p_label => p_structure_tbl(i).label,
817 p_scn_id => p_structure_tbl(i).scn_id,
818 p_object_version_number => p_structure_tbl(i).object_version_number
819 );
820
821 --------------------------------------------
822 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
824 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
825 RAISE FND_API.G_EXC_ERROR ;
826 END IF;
827 --------------------------------------------
828
829 END IF;
830
831 IF p_structure_tbl(i).type = 'ARTICLE' THEN
832
833 OKC_K_ARTICLES_GRP.Update_article(
834 p_api_version => 1,
835 p_init_msg_list => FND_API.G_FALSE,
836 p_validation_level => 0,
837 x_msg_count => x_msg_count,
838 x_msg_data => x_msg_data,
839 x_return_status => x_return_status,
840 p_id => p_structure_tbl(i).id,
841 p_mandatory_yn => p_structure_tbl(i).mandatory_yn,
842 p_scn_id => p_structure_tbl(i).scn_id,
843 p_label => p_structure_tbl(i).label,
844 p_display_sequence => p_structure_tbl(i).display_sequence,
845 p_object_version_number => p_structure_tbl(i).object_version_number
846 );
847
848 --------------------------------------------
849 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
850 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
851 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
852 RAISE FND_API.G_EXC_ERROR ;
853 END IF;
854 --------------------------------------------
855 END IF;
856 END LOOP;
857
858 END IF;
859 -- Standard check of p_commit
860 IF FND_API.To_Boolean( p_commit ) THEN
861 COMMIT WORK;
862 END IF;
863
864 -- Standard call to get message count and if count is 1, get message info.
865 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
866
867 /*IF (l_debug = 'Y') THEN
868 okc_debug.log('200: Leaving update_structure', 2);
869 END IF;*/
870
871 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
872 FND_LOG.STRING(G_PROC_LEVEL,
873 G_PKG_NAME, '200: Leaving update_structure' );
874 END IF;
875
876 EXCEPTION
877 WHEN FND_API.G_EXC_ERROR THEN
878 /*IF (l_debug = 'Y') THEN
879 okc_debug.log('300: Leaving update_structure: OKC_API.G_EXCEPTION_ERROR Exception', 2);
880 END IF;*/
881
882 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
883 FND_LOG.STRING(G_EXCP_LEVEL,
884 G_PKG_NAME, '300: Leaving update_structure: OKC_API.G_EXCEPTION_ERROR Exception' );
885 END IF;
886
887 IF l_doc_art_csr%ISOPEN THEN
888 CLOSE l_doc_art_csr;
889 END IF;
890
891 IF l_doc_scn_csr%ISOPEN THEN
892 CLOSE l_doc_scn_csr;
893 END IF;
894
895 ROLLBACK TO g_update_structure_GRP;
896 x_return_status := G_RET_STS_ERROR ;
897 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
898
899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
900 /*IF (l_debug = 'Y') THEN
901 okc_debug.log('400: Leaving update_structure: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
902 END IF;*/
903
904 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
905 FND_LOG.STRING(G_EXCP_LEVEL,
906 G_PKG_NAME, '400: Leaving update_structure: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
907 END IF;
908
909 IF l_doc_art_csr%ISOPEN THEN
910 CLOSE l_doc_art_csr;
911 END IF;
912
913 IF l_doc_scn_csr%ISOPEN THEN
914 CLOSE l_doc_scn_csr;
915 END IF;
916
917 ROLLBACK TO g_update_structure_GRP;
918 x_return_status := G_RET_STS_UNEXP_ERROR ;
919 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
920
921 WHEN OTHERS THEN
922 /*IF (l_debug = 'Y') THEN
923 okc_debug.log('500: Leaving update_structure because of EXCEPTION: '||sqlerrm, 2);
924 END IF;*/
925
926 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
927 FND_LOG.STRING(G_EXCP_LEVEL,
928 G_PKG_NAME, '500: Leaving update_structure because of EXCEPTION: '||sqlerrm );
929 END IF;
930
931 IF l_doc_art_csr%ISOPEN THEN
932 CLOSE l_doc_art_csr;
933 END IF;
934
935 IF l_doc_scn_csr%ISOPEN THEN
936 CLOSE l_doc_scn_csr;
937 END IF;
938
939 ROLLBACK TO g_update_structure_GRP;
940 x_return_status := G_RET_STS_UNEXP_ERROR ;
941 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
942 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
943 END IF;
944 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
945
946 END update_structure;
947
948 PROCEDURE sync_doc_with_expert(
949 p_api_version IN NUMBER,
950 p_init_msg_list IN VARCHAR2,
951 p_validate_commit IN VARCHAR2,
952 p_validation_string IN VARCHAR2,
953 p_commit IN VARCHAR2,
954 p_doc_type IN VARCHAR2,
955 p_doc_id IN NUMBER,
956 p_article_id_tbl IN article_id_tbl_type,
957 p_mode IN VARCHAR2 ,
958 x_articles_dropped OUT NOCOPY NUMBER,
959 x_return_status OUT NOCOPY VARCHAR2,
960 x_msg_count OUT NOCOPY NUMBER,
961 x_msg_data OUT NOCOPY VARCHAR2,
962 p_lock_terms_yn IN VARCHAR2
963 ) IS
964
965 l_api_version CONSTANT NUMBER := 1;
966 l_api_name CONSTANT VARCHAR2(30) := 'sync_doc_with_expert';
967 l_insert_tbl article_id_tbl_type;
968 l_dummy_var VARCHAR2(1);
969 l_article_id NUMBER;
970 l_ovn NUMBER;
971 k NUMBER := 0;
972 TYPE csrType IS REF CURSOR;
973 l_check_existing_csr csrType;
974 l_query VARCHAR2(3900);
975 l_query1 VARCHAR2(3900);
976 l_query2 VARCHAR2(3900);
977 l_article_effective_date DATE;
978 l_scn_seq NUMBER;
979 l_scn_id OKC_SECTIONS_B.ID%TYPE;
980 l_art_id OKC_K_ARTICLES_B.ID%TYPE;
981 l_art_seq NUMBER;
982 l_articles_dropped NUMBER :=0;
983 l_max_sec_seq NUMBER;
984
985 CURSOR l_check_presence_csr( b_article_id NUMBER) IS
986 SELECT 'x' FROM
987 OKC_K_ARTICLES_B KART
988 WHERE document_type=p_doc_type
989 AND document_id=p_doc_id
990 AND source_flag='R'
991 AND orig_article_id = b_article_id
992 AND nvl(KART.AMENDMENT_OPERATION_CODE,'?')<>'DELETED'
993 AND nvl(KART.SUMMARY_AMEND_OPERATION_CODE,'?')<>'DELETED';
994
995 CURSOR l_get_effective_date_csr IS
996 SELECT nvl(article_effective_date,sysdate)
997 FROM OKC_TEMPLATE_USAGES
998 WHERE DOCUMENT_TYPE = p_doc_type
999 AND DOCUMENT_ID = p_doc_id;
1000
1001 CURSOR l_get_active_article_csr(b_article_id NUMBER,b_article_effective_date DATE) IS
1002 SELECT vers.article_id article_id,
1003 vers.article_version_id article_version_id,
1004 nvl(vers.default_section,'UNASSIGNED') scn_code,
1005 nvl(PROVISION_YN,'N') provision_yn
1006 FROM OKC_ARTICLE_VERSIONS VERS
1007 WHERE vers.article_id = b_article_id
1008 AND article_status in ('ON_HOLD','APPROVED')
1009 AND b_article_effective_date >= vers.start_date
1010 AND b_article_effective_date <= nvl(vers.end_date,b_article_effective_date+1);
1011
1012 CURSOR l_get_max_article_csr(b_article_id NUMBER) IS
1013 SELECT article_id,
1014 article_version_id,
1015 nvl(vers.default_section,'UNASSIGNED') scn_code,
1016 nvl(PROVISION_YN,'N') provision_yn
1017 FROM OKC_ARTICLE_VERSIONS VERS
1018 WHERE article_id= b_article_id
1019 AND article_status in ('ON_HOLD','APPROVED')
1020 AND start_date = (select max(start_date) FROM OKC_ARTICLE_VERSIONS
1021 WHERE article_id= b_article_id
1022 AND article_status in ('ON_HOLD','APPROVED') );
1023
1024 CURSOR l_get_scn_csr(b_scn_code VARCHAR2) IS
1025 SELECT id FROM OKC_SECTIONS_B
1026 WHERE document_type=p_doc_type
1027 AND document_id =p_doc_id
1028 AND scn_code = b_scn_code
1029 AND nvl(AMENDMENT_OPERATION_CODE,'?')<>'DELETED'
1030 AND nvl(SUMMARY_AMEND_OPERATION_CODE,'?')<>'DELETED'
1031 AND rownum=1 ;
1032
1033 CURSOR l_get_section_seq_csr IS
1034 SELECT nvl(max(section_sequence),0)+10 FROM OKC_SECTIONS_B
1035 WHERE document_type = p_doc_type
1036 AND document_id = p_doc_id
1037 AND NVL(scn_code,'XWY') <> 'UNASSIGNED'
1038 AND scn_id IS NULL;
1039
1040 CURSOR l_get_art_seq_csr(b_scn_id NUMBER) IS
1041 SELECT nvl(max(display_sequence),0)+10 FROM OKC_K_ARTICLES_B
1042 WHERE document_type=p_doc_type
1043 AND document_id =p_doc_id
1044 AND SCN_ID = b_scn_id;
1045
1046 --Bug#5160892 added below cursor
1047 CURSOR l_get_sec_seq_csr(b_scn_id NUMBER) IS
1048 SELECT nvl(max(section_sequence),0)+10 FROM OKC_SECTIONS_B
1049 WHERE document_type=p_doc_type
1050 AND document_id =p_doc_id
1051 AND SCN_ID = b_scn_id;
1052
1053 CURSOR l_get_prov_csr IS
1054 SELECT name,
1055 nvl(PROVISION_ALLOWED_YN,'Y')
1056 FROM OKC_BUS_DOC_TYPES_V
1057 WHERE DOCUMENT_TYPE=p_doc_type;
1058
1059 CURSOR csr_mandatory_flag IS
1060 SELECT NVL(t.xprt_clause_mandatory_flag,'N'),
1061 NVL(t.xprt_scn_code,'UNASSIGNED')
1062 FROM okc_template_usages u,
1063 okc_terms_templates_all t
1064 WHERE u.template_id = t.template_id
1065 AND u.document_type = p_doc_type
1066 AND u.document_id = p_doc_id ;
1067 --CLM changes start
1068 CURSOR art_def_scn_csr(p_article_id NUMBER,p_article_version_id NUMBER) IS
1069 SELECT 'x' FROM okc_art_var_sections
1070 WHERE article_id = p_article_id
1071 AND article_version_id = p_article_version_id
1072 AND ROWNUM=1;
1073 l_art_var_exists VARCHAR2(1) := 'N';
1074 --CLM changes end
1075
1076
1077 l_xprt_clause_mandatory_flag okc_terms_templates_all.xprt_clause_mandatory_flag%TYPE;
1078 l_xprt_scn_code okc_terms_templates_all.xprt_scn_code%TYPE;
1079
1080 l_article_rec l_get_active_article_csr%ROWTYPE;
1081 l_max_article_rec l_get_max_article_csr%ROWTYPE;
1082 l_prov_allowed VARCHAR2(1) ;
1083 l_art_title VARCHAR2(450);
1084 l_bus_doc_name VARCHAR2(450);
1085 l_num_scheme_id NUMBER:=0;
1086 l_msg_data VARCHAR2(4000);
1087 l_msg_count NUMBER;
1088 l_renumber_flag VARCHAR2(1) :='N';
1089
1090 CURSOR l_get_num_scheme_id IS
1091 SELECT doc_numbering_scheme
1092 FROM okc_template_usages
1093 WHERE document_type = p_doc_type
1094 AND document_id = p_doc_id;
1095
1096 l_article_org_id NUMBER;
1097 l_current_org_id VARCHAR2(100);
1098
1099 CURSOR l_get_article_org_csr(b_article_id NUMBER) IS
1100 SELECT org_id
1101 FROM OKC_ARTICLES_ALL
1102 WHERE article_id = b_article_id;
1103
1104 CURSOR l_get_local_article_csr(b_article_id IN NUMBER,
1105 b_local_org_id IN NUMBER,
1106 b_article_effective_date IN DATE) IS
1107 SELECT VERS.ARTICLE_ID article_id,
1108 ADP.GLOBAL_ARTICLE_VERSION_ID article_version_id,
1109 NVL(VERS.default_section,'UNASSIGNED') scn_code,
1110 NVL(VERS.PROVISION_YN,'N') provision_yn
1111 FROM OKC_ARTICLE_VERSIONS VERS,
1112 OKC_ARTICLE_ADOPTIONS ADP
1113 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
1114 AND VERS.ARTICLE_ID = b_article_id
1115 AND nvl(b_article_effective_date,sysdate) >= VERS.START_DATE
1116 AND nvl(b_article_effective_date,sysdate) <= nvl(VERS.end_date, nvl(b_article_effective_date,sysdate) +1)
1117 AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
1118 AND ADP.ADOPTION_TYPE = 'ADOPTED'
1119 AND ADP.LOCAL_ORG_ID = b_local_org_id
1120 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD') ;
1121
1122 CURSOR l_get_max_local_article_csr(b_article_id IN NUMBER,
1123 b_local_org_id IN NUMBER) IS
1124 SELECT VERS.ARTICLE_ID article_id,
1125 ADP.GLOBAL_ARTICLE_VERSION_ID article_version_id,
1126 NVL(VERS.default_section,'UNASSIGNED') scn_code,
1127 NVL(VERS.PROVISION_YN,'N') provision_yn
1128 FROM OKC_ARTICLE_VERSIONS VERS,
1129 OKC_ARTICLE_ADOPTIONS ADP
1130 WHERE ADP.GLOBAL_ARTICLE_VERSION_ID = VERS.ARTICLE_VERSION_ID
1131 AND VERS.ARTICLE_ID = b_article_id
1132 AND VERS.ARTICLE_STATUS IN ('ON_HOLD','APPROVED')
1133 AND ADP.ADOPTION_TYPE = 'ADOPTED'
1134 AND ADP.LOCAL_ORG_ID = b_local_org_id
1135 AND ADP.adoption_status IN ( 'APPROVED', 'ON_HOLD')
1136 ORDER BY ADP.creation_date desc;
1137
1138 l_local_article_rec l_get_local_article_csr%ROWTYPE;
1139 l_max_local_article_rec l_get_max_local_article_csr%ROWTYPE;
1140
1141 CURSOR get_articles_order_art_num is
1142 SELECT id,scn_id
1143 FROM okc_k_articles_b,okc_articles_all
1144 WHERE document_type=p_doc_type AND
1145 document_id=p_doc_id AND
1146 sav_sae_id=article_id
1147 ORDER BY scn_id,article_number;
1148
1149 l_disp_seq NUMBER := 10;
1150 l_section_id NUMBER := NULL;
1151 l_order_by_column VARCHAR2(80);
1152 l_hook_used NUMBER;
1153
1154 BEGIN
1155
1156 /*IF (l_debug = 'Y') THEN
1157 okc_debug.log('100: Entered sync_doc_with_expert', 2);
1158 okc_debug.log('100: Parameter List ', 2);
1159 okc_debug.log('100: p_api_version : '||p_api_version, 2);
1160 okc_debug.log('100: p_init_msg_list : '||p_init_msg_list, 2);
1161 okc_debug.log('100: p_validate_commit : '||p_validate_commit, 2);
1162 okc_debug.log('100: p_validation_string : '||p_validation_string, 2);
1163 okc_debug.log('100: p_commit : '||p_commit, 2);
1164 okc_debug.log('100: p_doc_type : '||p_doc_type, 2);
1165 okc_debug.log('100: p_doc_id : '||p_doc_id, 2);
1166 END IF;*/
1167
1168 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1169 FND_LOG.STRING(G_PROC_LEVEL,
1170 G_PKG_NAME, '100: Entered sync_doc_with_expert' );
1171 FND_LOG.STRING(G_PROC_LEVEL,
1172 G_PKG_NAME, '100: Parameter List ' );
1173 FND_LOG.STRING(G_PROC_LEVEL,
1174 G_PKG_NAME, '100: p_api_version : '||p_api_version );
1175 FND_LOG.STRING(G_PROC_LEVEL,
1176 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
1177 FND_LOG.STRING(G_PROC_LEVEL,
1178 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
1179 FND_LOG.STRING(G_PROC_LEVEL,
1180 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
1181 FND_LOG.STRING(G_PROC_LEVEL,
1182 G_PKG_NAME, '100: p_commit : '||p_commit );
1183 FND_LOG.STRING(G_PROC_LEVEL,
1184 G_PKG_NAME, '100: p_doc_type : '||p_doc_type );
1185 FND_LOG.STRING(G_PROC_LEVEL,
1186 G_PKG_NAME, '100: p_doc_id : '||p_doc_id );
1187 END IF;
1188
1189 -- Standard Start of API savepoint
1190 SAVEPOINT g_sync_doc_with_expert_GRP;
1191 -- Standard call to check for call compatibility.
1192 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
1193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1194 END IF;
1195 -- Initialize message list if p_init_msg_list is set to TRUE.
1196 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1197 FND_MSG_PUB.initialize;
1198 END IF;
1199
1200 -- Initialize API return status to success
1201 x_return_status := FND_API.G_RET_STS_SUCCESS;
1202
1203 -- Begin: Added code for bug 5406515
1204 l_current_org_id := OKC_TERMS_UTIL_PVT.get_current_org_id(p_doc_type, p_doc_id);
1205 MO_GLOBAL.set_policy_context('S',l_current_org_id);
1206 -- End: Added code for bug 5406515
1207
1208 IF FND_API.To_Boolean( p_validate_commit )
1209 AND NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
1210 p_api_version => l_api_version,
1211 p_init_msg_list => FND_API.G_FALSE,
1212 p_doc_type => p_doc_type,
1213 p_doc_id => p_doc_id,
1214 p_validation_string => p_validation_string,
1215 x_return_status => x_return_status,
1216 x_msg_data => x_msg_data,
1217 x_msg_count => x_msg_count) ) THEN
1218
1219 /*IF (l_debug = 'Y') THEN
1220 okc_debug.log('200: Issue with document header Record.Cannot commit', 2);
1221 END IF;*/
1222
1223 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1224 FND_LOG.STRING(G_PROC_LEVEL,
1225 G_PKG_NAME, '200: Issue with document header Record.Cannot commit' );
1226 END IF;
1227 RAISE FND_API.G_EXC_ERROR ;
1228 END IF;
1229
1230 OPEN l_get_prov_csr;
1231 FETCH l_get_prov_csr into l_bus_doc_name, l_prov_allowed;
1232 CLOSE l_get_prov_csr;
1233
1234 -- Check if the expert clauses would be mandatory or optional
1235 OPEN csr_mandatory_flag;
1236 FETCH csr_mandatory_flag INTO l_xprt_clause_mandatory_flag,l_xprt_scn_code;
1237 CLOSE csr_mandatory_flag;
1238
1239
1240 /*IF (l_debug = 'Y') THEN
1241 okc_Debug.Log('100: l_prov_allowed : '||l_prov_allowed,2);
1242 okc_Debug.Log('100: l_xprt_clause_mandatory_flag : '||l_xprt_clause_mandatory_flag,2);
1243 END IF;*/
1244
1245 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1246 FND_LOG.STRING(G_PROC_LEVEL,
1247 G_PKG_NAME, '100: l_prov_allowed : '||l_prov_allowed );
1248 FND_LOG.STRING(G_PROC_LEVEL,
1249 G_PKG_NAME, '100: l_xprt_clause_mandatory_flag : '||l_xprt_clause_mandatory_flag );
1250 END IF;
1251
1252 l_query1 := 'Select kart.id ,kart.object_version_number from okc_k_articles_b kart where document_type=:l_doc_type and document_id=:l_doc_id and source_flag=''R'' and orig_article_id not in (';
1253
1254 k := 0;
1255
1256 /*IF (l_debug = 'Y') THEN
1257 okc_debug.log('200: Article Count from Expert : '||p_article_id_tbl.COUNT,2);
1258 END IF;*/
1259
1260 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1261 FND_LOG.STRING(G_PROC_LEVEL,
1262 G_PKG_NAME, '200: Article Count from Expert : '||p_article_id_tbl.COUNT );
1263 END IF;
1264
1265 IF p_article_id_tbl.COUNT > 0 THEN
1266 FOR i IN p_article_id_tbl.FIRST..p_article_id_tbl.LAST LOOP
1267
1268 -- Finding out article which was returned by Expert but
1269 -- is not present in document
1270
1271 OPEN l_check_presence_csr(p_article_id_tbl(i));
1272 FETCH l_check_presence_csr INTO l_dummy_var;
1273 IF l_check_presence_csr%NOTFOUND THEN
1274 l_insert_tbl(k):= p_article_id_tbl(i);
1275 k := k +1;
1276 END IF;
1277 CLOSE l_check_presence_csr;
1278
1279 IF i=p_article_id_tbl.FIRST THEN
1280 l_query1 := l_query1||p_article_id_tbl(i);
1281 ELSE
1282 l_query1 := l_query1||','||p_article_id_tbl(i);
1283 END IF;
1284
1285 END LOOP;
1286 l_query := l_query1||')';
1287 ELSE
1288 l_query := 'Select kart.id ,kart.object_version_number from okc_k_articles_b kart where document_type=:l_doc_type and document_id=:l_doc_id and source_flag=''R'' ';
1289
1290 END IF;
1291
1292 /*IF (l_debug = 'Y') THEN
1293 okc_debug.log('300: Query Is '||l_query);
1294 END IF;*/
1295
1296 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1297 FND_LOG.STRING(G_PROC_LEVEL,
1298 G_PKG_NAME, '300: Query Is '||l_query );
1299 END IF;
1300
1301 -- Find out article which
1302 /*IF (l_debug = 'Y') THEN
1303 okc_debug.log('400: Going to delete Articles which came from expert last time but not present in expert this time');
1304 END IF;*/
1305
1306 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1307 FND_LOG.STRING(G_PROC_LEVEL,
1308 G_PKG_NAME, '400: Going to delete Articles which came from expert last time but not present in expert this time' );
1309 END IF;
1310
1311 OPEN l_check_existing_csr FOR l_query USING p_doc_type,p_doc_id;
1312
1313 LOOP
1314
1315 FETCH l_check_existing_csr INTO l_article_id,l_ovn;
1316 EXIT WHEN l_check_existing_csr%NOTFOUND;
1317 -- since we are deleting articles from expert, renumber
1318 l_renumber_flag :='Y';
1319
1320 OKC_K_ARTICLES_GRP.delete_article(
1321 p_api_version => l_api_version,
1322 p_init_msg_list => FND_API.G_FALSE,
1323 p_validate_commit => FND_API.G_FALSE,
1324 p_validation_string => Null,
1325 p_commit => FND_API.G_FALSE,
1326 p_mode => p_mode,
1327 p_id => l_article_id,
1328 p_object_version_number => l_ovn,
1329 p_mandatory_clause_delete => 'Y',
1330 x_return_status => x_return_status,
1331 x_msg_count => x_msg_count,
1332 x_msg_data => x_msg_data,
1333 p_lock_terms_yn => p_lock_terms_yn
1334 );
1335 --------------------------------------------
1336 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1338 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1339 RAISE FND_API.G_EXC_ERROR ;
1340 END IF;
1341 --------------------------------------------
1342
1343 END LOOP;
1344
1345 CLOSE l_check_existing_csr;
1346
1347 /*IF (l_debug = 'Y') THEN
1348 okc_debug.log('500: Articles Delete ',2);
1349 okc_debug.log('600: Going to insert new Articles retunred by expert',2);
1350 okc_debug.log('600: Inserting Articles : '||l_insert_tbl.COUNT,2);
1351 END IF;*/
1352
1353 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1354 FND_LOG.STRING(G_PROC_LEVEL,
1355 G_PKG_NAME, '500: Articles Delete ' );
1356 FND_LOG.STRING(G_PROC_LEVEL,
1357 G_PKG_NAME, '600: Going to insert new Articles retunred by expert' );
1358 FND_LOG.STRING(G_PROC_LEVEL,
1359 G_PKG_NAME, '600: Inserting Articles : '||l_insert_tbl.COUNT );
1360 END IF;
1361
1362 IF l_insert_tbl.COUNT > 0 THEN
1363
1364 OPEN l_get_effective_date_csr;
1365 FETCH l_get_effective_date_csr INTO l_article_effective_date;
1366
1367 IF l_get_effective_date_csr%NOTFOUND THEN
1368 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1369 END IF;
1370 CLOSE l_get_effective_date_csr;
1371
1372 FOR i IN l_insert_tbl.FIRST..l_insert_tbl.LAST LOOP
1373
1374 -- bug 4162255
1375 -- check if Article is global or local
1376 OPEN l_get_article_org_csr(l_insert_tbl(i));
1377 FETCH l_get_article_org_csr INTO l_article_org_id;
1378 CLOSE l_get_article_org_csr;
1379
1380 -- current Org Id
1381 -- fnd_profile.get('ORG_ID',l_current_org_id);
1382 l_current_org_id := OKC_TERMS_UTIL_PVT.get_current_org_id(p_doc_type, p_doc_id);
1383
1384 IF nvl(l_current_org_id,'?') = l_article_org_id THEN
1385
1386 -- Find out latest active version of article to be inserted
1387 OPEN l_get_active_article_csr(l_insert_tbl(i),l_article_effective_date);
1388 FETCH l_get_active_article_csr INTO l_article_rec;
1389
1390 IF l_get_active_article_csr%NOTFOUND THEN
1391
1392 -- If no active version found then find out last active version.
1393
1394 OPEN l_get_max_article_csr(l_insert_tbl(i));
1395 FETCH l_get_max_article_csr INTO l_max_article_rec;
1396
1397 IF l_get_max_article_csr%NOTFOUND THEN
1398 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1399 END IF;
1400
1401 l_article_rec.article_id := l_max_article_rec.article_id;
1402 l_article_rec.article_version_id := l_max_article_rec.article_version_id;
1403 l_article_rec.scn_code := l_max_article_rec.scn_code;
1404 l_article_rec.provision_yn := l_max_article_rec.provision_yn;
1405 CLOSE l_get_max_article_csr;
1406
1407 END IF;
1408
1409 CLOSE l_get_active_article_csr;
1410
1411 ELSE
1412 -- Current Org Id and Article Org ID are different
1413 -- This is a ADOPTED Article
1414 OPEN l_get_local_article_csr(b_article_id => l_insert_tbl(i),
1415 b_local_org_id => l_current_org_id,
1416 b_article_effective_date => l_article_effective_date );
1417 FETCH l_get_local_article_csr INTO l_local_article_rec;
1418
1419 IF l_get_local_article_csr%NOTFOUND THEN
1420 -- get the Max Version of Approved Adopted Article
1421 OPEN l_get_max_local_article_csr(b_article_id => l_insert_tbl(i),
1422 b_local_org_id => l_current_org_id );
1423 FETCH l_get_max_local_article_csr INTO l_max_local_article_rec;
1424
1425 IF l_get_max_local_article_csr%NOTFOUND THEN
1426 CLOSE l_get_max_local_article_csr;
1427 CLOSE l_get_local_article_csr;
1428 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1429 END IF;
1430
1431 l_article_rec.article_id := l_max_local_article_rec.article_id;
1432 l_article_rec.article_version_id := l_max_local_article_rec.article_version_id;
1433 l_article_rec.scn_code := l_max_local_article_rec.scn_code;
1434 l_article_rec.provision_yn := l_max_local_article_rec.provision_yn;
1435
1436
1437 CLOSE l_get_max_local_article_csr;
1438 ELSE
1439 -- local article version found
1440 l_article_rec.article_id := l_local_article_rec.article_id;
1441 l_article_rec.article_version_id := l_local_article_rec.article_version_id;
1442 l_article_rec.scn_code := l_local_article_rec.scn_code;
1443 l_article_rec.provision_yn := l_local_article_rec.provision_yn;
1444 END IF;
1445
1446 CLOSE l_get_local_article_csr;
1447 END IF; -- nvl(l_current_org_id,'?') = l_article_org_id
1448
1449 -- check if the current article version is Provision and if provision
1450 -- is not allowed on the document, drop this article
1451 IF l_article_rec.provision_yn='Y' and l_prov_allowed='N' THEN
1452 l_articles_dropped := l_articles_dropped + 1;
1453 /*
1454 We are unable to display these messages on fnd message stack on structure pg
1455 So we will decide the number of articles dropped for now
1456 l_art_title := okc_terms_util_pvt.get_article_name
1457 (l_article_rec.article_id,
1458 l_article_rec.article_version_id);
1459 okc_Api.Set_Message(p_app_name => G_APP_NAME,
1460 p_msg_name => 'OKC_PROV_ART_DROPPED',
1461 p_token1 => 'ARTICLE_TITLE',
1462 p_token1_value => l_art_title,
1463 p_token2 => 'DOCUMENT_TYPE',
1464 p_token2_value => l_bus_doc_name);
1465 */
1466
1467 ELSE
1468 -- article is not provison or provision is allowed on document
1469 -- since we are inserting one or more articles, renumber
1470 -- we will renumber ONLY if article is Not added to unassigned section
1471 /*
1472 Added new column xprt_scn_code to okc_terms_templates_all
1473 If the article does not have a default scn_code in library then
1474 we will put the article in xprt_scn_code
1475 We always have to run renumber
1476 */
1477 -- IF NVL(l_article_rec.scn_code,'UNASSIGNED') <> 'UNASSIGNED' THEN
1478 --CLM changes start
1479 OPEN art_def_scn_csr(l_article_rec.article_id,l_article_rec.article_version_id);
1480 FETCH art_def_scn_csr INTO l_art_var_exists;
1481 CLOSE art_def_scn_csr;
1482
1483 IF (l_art_var_exists = 'x') THEN
1484 OKC_CLM_PKG.get_default_scn_code (
1485 p_api_version => p_api_version,
1486 p_init_msg_list => p_init_msg_list,
1487 p_article_id => l_article_rec.article_id,
1488 p_article_version_id => l_article_rec.article_version_id,
1489 p_doc_id => p_doc_id,
1490 p_doc_type => p_doc_type,
1491 x_default_scn_code => l_article_rec.scn_code,
1492 x_return_status => x_return_status
1493 ) ;
1494
1495 END IF;
1496 --CLM changes end
1497
1498 IF NVL(l_article_rec.scn_code,'UNASSIGNED') = 'UNASSIGNED' THEN
1499 l_article_rec.scn_code := l_xprt_scn_code;
1500 -- l_renumber_flag := 'Y';
1501 END IF;
1502
1503 -- As the expert articles will always have a section, always run renumber
1504 -- if expert returns clauses
1505 l_renumber_flag := 'Y';
1506
1507 --Find OUT IF section under which this article needs to be created exists
1508
1509 OPEN l_get_scn_csr(l_article_rec.scn_code);
1510 FETCH l_get_scn_csr INTO l_scn_id;
1511
1512 IF l_get_scn_csr%NOTFOUND THEN
1513
1514 -- Section doesnt exists.It needs to be created
1515
1516 OPEN l_get_section_seq_csr;
1517 FETCH l_get_section_seq_csr INTO l_scn_seq;
1518 CLOSE l_get_section_seq_csr;
1519
1520 /*IF (l_debug = 'Y') THEN
1521 okc_debug.log('700: Going to create section '|| l_article_rec.scn_code);
1522 END IF;*/
1523
1524 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1525 FND_LOG.STRING(G_PROC_LEVEL,
1526 G_PKG_NAME, '700: Going to create section '|| l_article_rec.scn_code );
1527 END IF;
1528
1529 OKC_TERMS_SECTIONS_GRP.create_section(
1530 p_api_version => l_api_version,
1531 p_init_msg_list => FND_API.G_FALSE,
1532 p_commit => FND_API.G_FALSE,
1533 x_return_status => x_return_status,
1534 x_msg_count => x_msg_count,
1535 x_msg_data => x_msg_data,
1536 p_id => NULL,
1537 p_section_sequence => l_scn_seq,
1538 p_scn_id => NULL,
1539 p_heading => OKC_UTIL.decode_lookup('OKC_ARTICLE_SECTION',l_article_rec.scn_code),
1540 p_description => OKC_UTIL.decode_lookup('OKC_ARTICLE_SECTION',l_article_rec.scn_code),
1541 p_document_type => p_doc_type,
1542 p_document_id => p_doc_id,
1543 p_scn_code => l_article_rec.scn_code,
1544 p_mode => p_mode,
1545 x_id => l_scn_id
1546 );
1547 --------------------------------------------
1548 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1550 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1551 RAISE FND_API.G_EXC_ERROR ;
1552 END IF;
1553 --------------------------------------------
1554 /*IF (l_debug = 'Y') THEN
1555 okc_debug.log('800: section created');
1556 END IF;*/
1557
1558 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1559 FND_LOG.STRING(G_PROC_LEVEL,
1560 G_PKG_NAME, '800: section created' );
1561 END IF;
1562
1563 END IF;
1564 CLOSE l_get_scn_csr;
1565
1566 -- Creating the article
1567 OPEN l_get_art_seq_csr(l_scn_id);
1568 FETCH l_get_art_seq_csr INTO l_art_seq;
1569 CLOSE l_get_art_seq_csr;
1570
1571 --START:Bug#5160892 added below code
1572 OPEN l_get_sec_seq_csr(l_scn_id);
1573 FETCH l_get_sec_seq_csr INTO l_max_sec_seq;
1574 CLOSE l_get_sec_seq_csr;
1575
1576 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1577 FND_LOG.STRING(G_PROC_LEVEL,
1578 G_PKG_NAME, '801: l_art_seq = '||l_art_seq);
1579 FND_LOG.STRING(G_PROC_LEVEL,
1580 G_PKG_NAME, '801: l_max_sec_seq = '||l_max_sec_seq);
1581 END IF;
1582
1583 IF(l_max_sec_seq > l_art_seq) then
1584 l_art_seq := l_max_sec_seq;
1585 END IF;
1586 --END:Bug#5160892
1587
1588 OKC_K_ARTICLES_GRP.create_article(
1589 p_api_version => l_api_version,
1590 p_init_msg_list => FND_API.G_FALSE,
1591 p_commit => FND_API.G_FALSE,
1592 x_return_status => x_return_status,
1593 x_msg_count => x_msg_count,
1594 x_msg_data => x_msg_data,
1595 p_id => NULL,
1596 p_sav_sae_id => l_article_rec.article_id,
1597 p_document_type => p_doc_type,
1598 p_document_id => p_doc_id,
1599 p_source_flag => 'R',
1600 p_mandatory_yn => l_xprt_clause_mandatory_flag,
1601 p_scn_id => l_scn_id,
1602 p_article_version_id => l_article_rec.article_version_id,
1603 p_display_sequence => l_art_seq,
1604 p_mode => p_mode,
1605 x_id => l_art_id
1606 );
1607 --------------------------------------------
1608 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1609 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1610 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1611 RAISE FND_API.G_EXC_ERROR ;
1612 END IF;
1613 --------------------------------------------
1614
1615 END IF; -- l_article_rec.provision_yn='Y' and l_prov_allowed='N'
1616
1617 END LOOP;
1618
1619 END IF; -- l_insert_tbl.COUNT > 0
1620
1621 /*IF (l_debug = 'Y') THEN
1622 okc_debug.log('900: New Articles Inserted');
1623 END IF;*/
1624
1625 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1626 FND_LOG.STRING(G_PROC_LEVEL,
1627 G_PKG_NAME, '900: New Articles Inserted' );
1628 END IF;
1629
1630
1631 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1632 FND_LOG.STRING(G_PROC_LEVEL,
1633 G_PKG_NAME, '950:Calling Code Hook to check if ordering by clause number has to be done');
1634 END IF;
1635
1636 OKC_CODE_HOOK.GET_XPRT_CLAUSE_ORDER(
1637 x_return_status => x_return_status,
1638 x_msg_count => l_msg_count,
1639 x_msg_data => l_msg_data,
1640 x_order_by_column => l_order_by_column,
1641 x_hook_used => l_hook_used
1642 );
1643
1644 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1645 FND_LOG.STRING(G_PROC_LEVEL,
1646 G_PKG_NAME, '975:Update the Clause Display Sequence if the code hook is being used');
1647 END IF;
1648
1649 IF l_hook_used = 1 AND l_order_by_column='CLAUSE_NUMBER' THEN
1650
1651 FOR articles_order_art_num IN get_articles_order_art_num
1652 LOOP
1653 IF articles_order_art_num.scn_id = l_section_id THEN
1654 l_disp_seq := l_disp_seq + 10;
1655 ELSE
1656 l_disp_seq := 10;
1657 l_section_id:=articles_order_art_num.scn_id;
1658 END IF;
1659 OKC_K_ARTICLES_GRP.update_article(
1660 p_api_version =>1,
1661 p_init_msg_list => OKC_API.G_FALSE,
1662 x_return_status => x_return_status,
1663 x_msg_count => l_msg_count,
1664 x_msg_data => l_msg_data,
1665 p_id => articles_order_art_num.id,
1666 p_display_sequence => l_disp_seq,
1667 p_object_version_number => Null
1668 );
1669 END LOOP;
1670 --------------------------------------------
1671 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
1672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1673 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
1674 RAISE FND_API.G_EXC_ERROR ;
1675 END IF;
1676 -------------------------------------------
1677
1678 END IF; -- l_hook_used = 1
1679
1680 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1681 FND_LOG.STRING(G_PROC_LEVEL,
1682 G_PKG_NAME, '980:Calling Code Hook to sync Rwa and Mandatory flags');
1683 END IF;
1684
1685 IF p_article_id_tbl.Count > 0 THEN
1686 OKC_CODE_HOOK.sync_rwa_with_document
1687 (p_api_version => 1,
1688 p_init_msg_list => FND_API.G_FALSE,
1689 p_doc_type => p_doc_type,
1690 p_doc_id => p_doc_id,
1691 p_article_id_tbl => p_article_id_tbl,
1692 x_return_status => x_return_status,
1693 x_msg_count => l_msg_count,
1694 x_msg_data => l_msg_data
1695 );
1696 END IF;
1697
1698 -- call renumber automatically if any articles have been added or deleted
1699 IF NVL(l_renumber_flag,'N') = 'Y' THEN
1700
1701 OPEN l_get_num_scheme_id;
1702 FETCH l_get_num_scheme_id INTO l_num_scheme_id;
1703 CLOSE l_get_num_scheme_id;
1704
1705 /*IF (l_debug = 'Y') THEN
1706 okc_debug.log('150: l_num_scheme_id : '||l_num_scheme_id, 2);
1707 END IF;*/
1708
1709 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1710 FND_LOG.STRING(G_PROC_LEVEL,
1711 G_PKG_NAME, '150: l_num_scheme_id : '||l_num_scheme_id );
1712 END IF;
1713
1714 IF NVL(l_num_scheme_id,0) <> 0 THEN
1715
1716 /*IF (l_debug = 'Y') THEN
1717 okc_debug.log('150: Calling apply_numbering_scheme for num_scheme_id : '||l_num_scheme_id, 2);
1718 END IF;*/
1719
1720 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1721 FND_LOG.STRING(G_PROC_LEVEL,
1722 G_PKG_NAME, '150: Calling apply_numbering_scheme for num_scheme_id : '||l_num_scheme_id );
1723 END IF;
1724
1725 OKC_NUMBER_SCHEME_GRP.apply_numbering_scheme(
1726 p_api_version => p_api_version,
1727 p_init_msg_list => FND_API.G_FALSE,
1728 x_return_status => x_return_status,
1729 x_msg_count => l_msg_count,
1730 x_msg_data => l_msg_data,
1731 p_validate_commit => p_validate_commit,
1732 p_validation_string => p_validation_string,
1733 p_commit => FND_API.G_FALSE,
1734 p_doc_type => p_doc_type,
1735 p_doc_id => p_doc_id,
1736 p_num_scheme_id => l_num_scheme_id
1737 );
1738
1739 /*IF (l_debug = 'Y') THEN
1740 okc_debug.log('150: After Calling apply_numbering_scheme ', 2);
1741 okc_debug.log('150: x_return_status : '||x_return_status, 2);
1742 okc_debug.log('150: x_msg_count : '||l_msg_count, 2);
1743 okc_debug.log('150: x_msg_data : '||l_msg_data, 2);
1744 END IF;*/
1745
1746 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1747 FND_LOG.STRING(G_PROC_LEVEL,
1748 G_PKG_NAME, '150: After Calling apply_numbering_scheme' );
1749 FND_LOG.STRING(G_PROC_LEVEL,
1750 G_PKG_NAME, '150: x_return_status : '||x_return_status );
1751 FND_LOG.STRING(G_PROC_LEVEL,
1752 G_PKG_NAME, '150: x_msg_count : '||l_msg_count );
1753 FND_LOG.STRING(G_PROC_LEVEL,
1754 G_PKG_NAME, '150: x_msg_data : '||l_msg_data );
1755 END IF;
1756
1757 END IF; --l_num_scheme_id is not 0
1758 END IF; -- call renumber automatically
1759
1760
1761 -- set the OUT parameter to indicate the number of provisional articles dropped
1762 x_articles_dropped := l_articles_dropped;
1763
1764 -- Standard check of p_commit
1765 IF FND_API.To_Boolean( p_commit ) THEN
1766 COMMIT WORK;
1767 END IF;
1768
1769 -- Standard call to get message count and if count is 1, get message info.
1770 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1771
1772 /*IF (l_debug = 'Y') THEN
1773 okc_debug.log('200: Leaving sync_doc_with_expert', 2);
1774 END IF;*/
1775
1776 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
1777 FND_LOG.STRING(G_PROC_LEVEL,
1778 G_PKG_NAME, '200: Leaving sync_doc_with_expert' );
1779 END IF;
1780
1781 EXCEPTION
1782 WHEN FND_API.G_EXC_ERROR THEN
1783 /*IF (l_debug = 'Y') THEN
1784 okc_debug.log('300: Leaving sync_doc_with_expert: OKC_API.G_EXCEPTION_ERROR Exception', 2);
1785 END IF;*/
1786
1787 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1788 FND_LOG.STRING(G_EXCP_LEVEL,
1789 G_PKG_NAME, '300: Leaving sync_doc_with_expert: OKC_API.G_EXCEPTION_ERROR Exception' );
1790 END IF;
1791
1792 IF l_get_max_local_article_csr%ISOPEN THEN
1793 CLOSE l_get_max_local_article_csr;
1794 END IF;
1795
1796 IF l_get_local_article_csr%ISOPEN THEN
1797 CLOSE l_get_local_article_csr;
1798 END IF;
1799
1800 IF l_get_max_article_csr%ISOPEN THEN
1801 CLOSE l_get_max_article_csr;
1802 END IF;
1803
1804 IF l_get_active_article_csr%ISOPEN THEN
1805 CLOSE l_get_active_article_csr;
1806 END IF;
1807
1808 IF l_get_effective_date_csr%ISOPEN THEN
1809 CLOSE l_get_effective_date_csr;
1810 END IF;
1811
1812 IF l_get_active_article_csr%ISOPEN THEN
1813 CLOSE l_get_active_article_csr;
1814 END IF;
1815
1816 IF l_check_presence_csr%ISOPEN THEN
1817 CLOSE l_check_presence_csr;
1818 END IF;
1819
1820 IF l_get_max_article_csr%ISOPEN THEN
1821 CLOSE l_get_max_article_csr;
1822 END IF;
1823
1824 IF l_get_max_article_csr%ISOPEN THEN
1825 CLOSE l_get_max_article_csr;
1826 END IF;
1827
1828 IF l_get_section_seq_csr%ISOPEN THEN
1829 CLOSE l_get_section_seq_csr;
1830 END IF;
1831
1832 IF l_get_art_seq_csr%ISOPEN THEN
1833 CLOSE l_get_art_seq_csr;
1834 END IF;
1835
1836 ROLLBACK TO g_sync_doc_with_expert_GRP;
1837 x_return_status := G_RET_STS_ERROR ;
1838 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1839
1840 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1841 /*IF (l_debug = 'Y') THEN
1842 okc_debug.log('400: Leaving sync_doc_with_expert: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1843 END IF;*/
1844
1845 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1846 FND_LOG.STRING(G_EXCP_LEVEL,
1847 G_PKG_NAME, '400: Leaving sync_doc_with_expert: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
1848 END IF;
1849
1850 IF l_get_max_local_article_csr%ISOPEN THEN
1851 CLOSE l_get_max_local_article_csr;
1852 END IF;
1853
1854 IF l_get_local_article_csr%ISOPEN THEN
1855 CLOSE l_get_local_article_csr;
1856 END IF;
1857
1858 IF l_get_max_article_csr%ISOPEN THEN
1859 CLOSE l_get_max_article_csr;
1860 END IF;
1861
1862 IF l_get_active_article_csr%ISOPEN THEN
1863 CLOSE l_get_active_article_csr;
1864 END IF;
1865
1866 IF l_get_effective_date_csr%ISOPEN THEN
1867 CLOSE l_get_effective_date_csr;
1868 END IF;
1869
1870 IF l_get_active_article_csr%ISOPEN THEN
1871 CLOSE l_get_active_article_csr;
1872 END IF;
1873
1874 IF l_check_presence_csr%ISOPEN THEN
1875 CLOSE l_check_presence_csr;
1876 END IF;
1877
1878 IF l_get_max_article_csr%ISOPEN THEN
1879 CLOSE l_get_max_article_csr;
1880 END IF;
1881
1882 IF l_get_max_article_csr%ISOPEN THEN
1883 CLOSE l_get_max_article_csr;
1884 END IF;
1885
1886 IF l_get_section_seq_csr%ISOPEN THEN
1887 CLOSE l_get_section_seq_csr;
1888 END IF;
1889
1890 IF l_get_art_seq_csr%ISOPEN THEN
1891 CLOSE l_get_art_seq_csr;
1892 END IF;
1893
1894 ROLLBACK TO g_sync_doc_with_expert_GRP;
1895 x_return_status := G_RET_STS_UNEXP_ERROR ;
1896 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1897
1898 WHEN OTHERS THEN
1899 /*IF (l_debug = 'Y') THEN
1900 okc_debug.log('500: Leaving sync_doc_with_expert because of EXCEPTION: '||sqlerrm, 2);
1901 END IF;*/
1902
1903 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
1904 FND_LOG.STRING(G_EXCP_LEVEL,
1905 G_PKG_NAME, '500: Leaving sync_doc_with_expert because of EXCEPTION: '||sqlerrm );
1906 END IF;
1907
1908 IF l_get_max_local_article_csr%ISOPEN THEN
1909 CLOSE l_get_max_local_article_csr;
1910 END IF;
1911
1912 IF l_get_local_article_csr%ISOPEN THEN
1913 CLOSE l_get_local_article_csr;
1914 END IF;
1915
1916 IF l_get_max_article_csr%ISOPEN THEN
1917 CLOSE l_get_max_article_csr;
1918 END IF;
1919
1920 IF l_get_active_article_csr%ISOPEN THEN
1921 CLOSE l_get_active_article_csr;
1922 END IF;
1923
1924 IF l_get_effective_date_csr%ISOPEN THEN
1925 CLOSE l_get_effective_date_csr;
1926 END IF;
1927
1928 IF l_get_active_article_csr%ISOPEN THEN
1929 CLOSE l_get_active_article_csr;
1930 END IF;
1931
1932 IF l_check_presence_csr%ISOPEN THEN
1933 CLOSE l_check_presence_csr;
1934 END IF;
1935
1936 IF l_get_max_article_csr%ISOPEN THEN
1937 CLOSE l_get_max_article_csr;
1938 END IF;
1939
1940 IF l_get_max_article_csr%ISOPEN THEN
1941 CLOSE l_get_max_article_csr;
1942 END IF;
1943
1944 IF l_get_section_seq_csr%ISOPEN THEN
1945 CLOSE l_get_section_seq_csr;
1946 END IF;
1947
1948 IF l_get_art_seq_csr%ISOPEN THEN
1949 CLOSE l_get_art_seq_csr;
1950 END IF;
1951
1952 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1953 p_msg_name => G_UNEXPECTED_ERROR,
1954 p_token1 => G_SQLCODE_TOKEN,
1955 p_token1_value => sqlcode,
1956 p_token2 => G_SQLERRM_TOKEN,
1957 p_token2_value => sqlerrm);
1958
1959 ROLLBACK TO g_sync_doc_with_expert_GRP;
1960
1961 x_return_status := G_RET_STS_UNEXP_ERROR ;
1962
1963 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1964 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1965 END IF;
1966 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
1967
1968 END sync_doc_with_expert;
1969
1970 PROCEDURE refresh_articles(
1971 p_api_version IN NUMBER,
1972 p_init_msg_list IN VARCHAR2 ,
1973 p_validate_commit IN VARCHAR2,
1974 p_validation_string IN VARCHAR2,
1975 p_commit IN VARCHAR2 ,
1976 p_mode IN VARCHAR2 ,
1977 p_doc_type IN VARCHAR2,
1978 p_doc_id IN NUMBER,
1979 p_article_tbl IN article_tbl_type,
1980 x_return_status OUT NOCOPY VARCHAR2,
1981 x_msg_count OUT NOCOPY NUMBER,
1982 x_msg_data OUT NOCOPY VARCHAR2,
1983 p_lock_terms_yn IN VARCHAR2
1984 )
1985 IS
1986
1987 l_api_version CONSTANT NUMBER := 1;
1988 l_api_name CONSTANT VARCHAR2(30) := 'refresh_articles';
1989 l_dummy VARCHAR2(1) := '?';
1990
1991 l_article_id NUMBER;
1992
1993 CURSOR l_check_version(b_article_version_id NUMBER) IS
1994 SELECT article_id , 'x' FROM OKC_ARTICLE_VERSIONS
1995 WHERE article_version_id=b_article_version_id;
1996
1997 BEGIN
1998
1999 /*IF (l_debug = 'Y') THEN
2000 okc_debug.log('100: Entered refresh_articles', 2);
2001 okc_debug.log('100: Parameter List ', 2);
2002 okc_debug.log('100: p_api_version : '||p_api_version, 2);
2003 okc_debug.log('100: p_init_msg_list : '||p_init_msg_list, 2);
2004 okc_debug.log('100: p_mode : '||p_mode, 2);
2005 okc_debug.log('100: p_validate_commit : '||p_validate_commit, 2);
2006 okc_debug.log('100: p_validation_string : '||p_validation_string, 2);
2007 okc_debug.log('100: p_commit : '||p_commit, 2);
2008 okc_debug.log('100: p_doc_type : '||p_doc_type, 2);
2009 okc_debug.log('100: p_doc_id : '||p_doc_id, 2);
2010 END IF;*/
2011
2012 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2013 FND_LOG.STRING(G_PROC_LEVEL,
2014 G_PKG_NAME, '100: Entered refresh_articles' );
2015 FND_LOG.STRING(G_PROC_LEVEL,
2016 G_PKG_NAME, '100: Parameter List ' );
2017 FND_LOG.STRING(G_PROC_LEVEL,
2018 G_PKG_NAME, '100: p_api_version : '||p_api_version );
2019 FND_LOG.STRING(G_PROC_LEVEL,
2020 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
2021 FND_LOG.STRING(G_PROC_LEVEL,
2022 G_PKG_NAME, '100: p_mode : '||p_mode );
2023 FND_LOG.STRING(G_PROC_LEVEL,
2024 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
2025 FND_LOG.STRING(G_PROC_LEVEL,
2026 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
2027 FND_LOG.STRING(G_PROC_LEVEL,
2028 G_PKG_NAME, '100: p_commit : '||p_commit );
2029 FND_LOG.STRING(G_PROC_LEVEL,
2030 G_PKG_NAME, '100: p_doc_type : '||p_doc_type );
2031 FND_LOG.STRING(G_PROC_LEVEL,
2032 G_PKG_NAME, '100: p_doc_id : '||p_doc_id );
2033 END IF;
2034
2035 -- Standard Start of API savepoint
2036 SAVEPOINT g_refresh_articles_GRP;
2037 -- Standard call to check for call compatibility.
2038
2039 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2040 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2041 END IF;
2042 -- Initialize message list if p_init_msg_list is set to TRUE.
2043 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2044 FND_MSG_PUB.initialize;
2045 END IF;
2046
2047 -- Initialize API return status to success
2048 x_return_status := FND_API.G_RET_STS_SUCCESS;
2049
2050 IF FND_API.To_Boolean( p_validate_commit )
2051 AND NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
2052 p_api_version => l_api_version,
2053 p_init_msg_list => FND_API.G_FALSE,
2054 p_doc_type => p_doc_type,
2055 p_doc_id => p_doc_id,
2056 p_validation_string => p_validation_string,
2057 x_return_status => x_return_status,
2058 x_msg_data => x_msg_data,
2059 x_msg_count => x_msg_count) ) THEN
2060
2061 /*IF (l_debug = 'Y') THEN
2062 okc_debug.log('200: Issue with document header Record.Cannot commit', 2);
2063 END IF;*/
2064
2065 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2066 FND_LOG.STRING(G_PROC_LEVEL,
2067 G_PKG_NAME, '200: Issue with document header Record.Cannot commit' );
2068 END IF;
2069 RAISE FND_API.G_EXC_ERROR ;
2070 END IF;
2071
2072 FOR i IN p_article_tbl.FIRST..p_article_tbl.LAST LOOP
2073
2074 l_dummy := '?';
2075 OPEN l_check_version(p_article_tbl(i).article_version_id);
2076 FETCH l_check_version INTO l_article_id,l_dummy;
2077 CLOSE l_check_version;
2078
2079 IF l_dummy <>'?' THEN
2080 OKC_K_ARTICLES_GRP.update_article(
2081 p_api_version =>1,
2082 p_init_msg_list => OKC_API.G_FALSE,
2083 x_return_status => x_return_status,
2084 x_msg_count => x_msg_count,
2085 x_msg_data => x_msg_data,
2086 p_mode => p_mode,
2087 p_id => p_article_tbl(i).cat_id,
2088 p_sav_sae_id => l_article_id,
2089 p_article_version_id => p_article_tbl(i).article_version_id,
2090 p_object_version_number => p_article_tbl(i).ovn,
2091 p_lock_terms_yn => p_lock_terms_yn
2092 );
2093 --------------------------------------------
2094 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2095 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2096 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2097 RAISE FND_API.G_EXC_ERROR ;
2098 END IF;
2099 --------------------------------------------
2100 END IF;
2101
2102 END LOOP;
2103
2104 -- Standard check of p_commit
2105 IF FND_API.To_Boolean( p_commit ) THEN
2106 COMMIT WORK;
2107 END IF;
2108
2109 -- Standard call to get message count and if count is 1, get message info.
2110 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2111
2112 /*IF (l_debug = 'Y') THEN
2113 okc_debug.log('200: Leaving refresh_articles', 2);
2114 END IF;*/
2115
2116 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2117 FND_LOG.STRING(G_PROC_LEVEL,
2118 G_PKG_NAME, '200: Leaving refresh_articles' );
2119 END IF;
2120
2121 EXCEPTION
2122 WHEN FND_API.G_EXC_ERROR THEN
2123 /*IF (l_debug = 'Y') THEN
2124 okc_debug.log('300: Leaving refresh_articles: OKC_API.G_EXCEPTION_ERROR Exception', 2);
2125 END IF;*/
2126
2127 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2128 FND_LOG.STRING(G_EXCP_LEVEL,
2129 G_PKG_NAME, '300: Leaving refresh_articles: OKC_API.G_EXCEPTION_ERROR Exception' );
2130 END IF;
2131
2132 IF l_check_version%ISOPEN THEN
2133 close l_check_version;
2134 END IF;
2135
2136 ROLLBACK TO g_refresh_articles_GRP;
2137 x_return_status := G_RET_STS_ERROR ;
2138 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2139
2140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2141 /*IF (l_debug = 'Y') THEN
2142 okc_debug.log('400: Leaving refresh_articles: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2143 END IF;*/
2144
2145 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2146 FND_LOG.STRING(G_EXCP_LEVEL,
2147 G_PKG_NAME, '400: Leaving refresh_articles: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
2148 END IF;
2149
2150 IF l_check_version%ISOPEN THEN
2151 close l_check_version;
2152 END IF;
2153
2154 ROLLBACK TO g_refresh_articles_GRP;
2155 x_return_status := G_RET_STS_UNEXP_ERROR ;
2156 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2157
2158 WHEN OTHERS THEN
2159 /*IF (l_debug = 'Y') THEN
2160 okc_debug.log('500: Leaving refresh_articles because of EXCEPTION: '||sqlerrm, 2);
2161 END IF;*/
2162
2163 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2164 FND_LOG.STRING(G_EXCP_LEVEL,
2165 G_PKG_NAME, '500: Leaving refresh_articles because of EXCEPTION: '||sqlerrm );
2166 END IF;
2167
2168 IF l_check_version%ISOPEN THEN
2169 close l_check_version;
2170 END IF;
2171
2172
2173 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2174 p_msg_name => G_UNEXPECTED_ERROR,
2175 p_token1 => G_SQLCODE_TOKEN,
2176 p_token1_value => sqlcode,
2177 p_token2 => G_SQLERRM_TOKEN,
2178 p_token2_value => sqlerrm);
2179
2180 ROLLBACK TO g_refresh_articles_GRP;
2181
2182 x_return_status := G_RET_STS_UNEXP_ERROR ;
2183
2184 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2185 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2186 END IF;
2187 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2188
2189 END refresh_articles;
2190
2191 PROCEDURE organize_layout(
2192 p_api_version IN NUMBER,
2193 p_init_msg_list IN VARCHAR2 ,
2194 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2195 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
2196 p_validation_string IN VARCHAR2,
2197 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2198 p_organize_tbl IN ORGANIZE_TBL_TYPE,
2199 p_ref_point IN VARCHAR2 := 'A', -- Possible values
2200 -- 'A'=After,'B'=Before,'S' = Subsection
2201 p_doc_type IN VARCHAR2,
2202 p_doc_id IN NUMBER,
2203 p_to_object_type IN VARCHAR2,
2204 p_to_object_id IN NUMBER,
2205 x_return_status OUT NOCOPY VARCHAR2,
2206 x_msg_count OUT NOCOPY NUMBER,
2207 x_msg_data OUT NOCOPY VARCHAR2
2208 ) IS
2209
2210 l_api_version CONSTANT NUMBER := 1;
2211 l_api_name CONSTANT VARCHAR2(30) := 'organize_layout';
2212 l_organize_tbl ORGANIZE_TBL_TYPE;
2213 l_scn_id NUMBER;
2214 l_parent_scn_id NUMBER;
2215 l_ref_is_set BOOLEAN :=FALSE;
2216 l_not_deleted BOOLEAN;
2217 l_ref_sequence NUMBER;
2218 l_ref_sequence1 NUMBER;
2219 j NUMBER := 0;
2220 l_ref_count NUMBER;
2221 l_dont_move Boolean;
2222
2223 TYPE del_list_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2224 l_del_list_tbl del_list_tbl_type;
2225
2226 scn_child_tbl scn_child_tbl_type;
2227
2228 CURSOR l_get_max_seq_csr(b_scn_id NUMBER) is
2229 SELECT nvl(max(SECTION_SEQUENCE),0) FROM OKC_SECTIONS_B
2230 WHERE document_type=p_doc_type
2231 AND document_id=p_doc_id
2232 AND ( (b_scn_id is Null and scn_id is Null)
2233 OR
2234 (b_scn_id is Not Null and scn_id=b_scn_id)
2235 );
2236
2237
2238 CURSOR l_get_max_art_seq_csr(b_scn_id NUMBER) is
2239 SELECT nvl(max(display_SEQUENCE),0) FROM OKC_K_ARTICLES_B
2240 WHERE document_type=p_doc_type
2241 AND document_id=p_doc_id
2242 and b_scn_id is not null
2243 and scn_id=b_scn_id;
2244
2245 CURSOR l_get_scn_csr(b_cat_id NUMBER) IS
2246 SELECT SCN_ID FROM OKC_K_ARTICLES_B WHERE Id=b_cat_id;
2247
2248 CURSOR l_get_parent_scn_csr(b_scn_id NUMBER) IS
2249 SELECT SCN_ID FROM OKC_SECTIONS_B WHERE Id=b_scn_id;
2250
2251 CURSOR l_get_parents_csr(b_id NUMBER) IS
2252 SELECT ID FROM OKC_SECTIONS_B
2253 where id<>b_id
2254 AND document_type=p_doc_type
2255 AND document_id=p_doc_id
2256 start with Id=b_id
2257 connect by id=prior scn_id;
2258
2259
2260 CURSOR l_get_scn_child(b_scn_id NUMBER) IS
2261 SELECT ID,SECTION_SEQUENCE DISPLAY_SEQ,'SECTION' obj_type
2262 FROM OKC_SECTIONS_B
2263 WHERE document_type = p_doc_type
2264 AND document_id = p_doc_id
2265 AND ( (b_scn_id is Null and scn_id is Null)
2266 OR
2267 (b_scn_id is Not Null and scn_id=b_scn_id)
2268 )
2269 UNION ALL
2270 SELECT ID,DISPLAY_SEQUENCE DISPLAY_SEQ,'ARTICLE' obj_type
2271 FROM OKC_K_ARTICLES_B
2272 WHERE document_type = p_doc_type
2273 AND document_id = p_doc_id
2274 AND scn_id=b_scn_id
2275 ORDER BY 2;
2276
2277 BEGIN
2278
2279 /*IF (l_debug = 'Y') THEN
2280 okc_debug.log('100: Entered organize_layout', 2);
2281 okc_debug.log('100: Parameter List ', 2);
2282 okc_debug.log('100: p_api_version : '||p_api_version, 2);
2283 okc_debug.log('100: p_init_msg_list : '||p_init_msg_list, 2);
2284 okc_debug.log('100: p_validate_commit : '||p_validate_commit, 2);
2285 okc_debug.log('100: p_validation_string : '||p_validation_string, 2);
2286 okc_debug.log('100: p_commit : '||p_commit, 2);
2287 okc_debug.log('100: p_ref_point : '||p_ref_point, 2);
2288 okc_debug.log('100: p_doc_type : '||p_doc_type, 2);
2289 okc_debug.log('100: p_doc_id : '||p_doc_id, 2);
2290 okc_debug.log('100: p_to_object_type : '||p_to_object_type, 2);
2291 okc_debug.log('100: p_to_object_id : '||p_to_object_id, 2);
2292 END IF;*/
2293
2294 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2295 FND_LOG.STRING(G_PROC_LEVEL,
2296 G_PKG_NAME, '100: Entered organize_layout' );
2297 FND_LOG.STRING(G_PROC_LEVEL,
2298 G_PKG_NAME, '100: Parameter List ' );
2299 FND_LOG.STRING(G_PROC_LEVEL,
2300 G_PKG_NAME, '100: p_api_version : '||p_api_version );
2301 FND_LOG.STRING(G_PROC_LEVEL,
2302 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
2303 FND_LOG.STRING(G_PROC_LEVEL,
2304 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
2305 FND_LOG.STRING(G_PROC_LEVEL,
2306 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
2307 FND_LOG.STRING(G_PROC_LEVEL,
2308 G_PKG_NAME, '100: p_commit : '||p_commit );
2309 FND_LOG.STRING(G_PROC_LEVEL,
2310 G_PKG_NAME, '100: p_ref_point : '||p_ref_point );
2311 FND_LOG.STRING(G_PROC_LEVEL,
2312 G_PKG_NAME, '100: p_doc_type : '||p_doc_type );
2313 FND_LOG.STRING(G_PROC_LEVEL,
2314 G_PKG_NAME, '100: p_doc_id : '||p_doc_id );
2315 FND_LOG.STRING(G_PROC_LEVEL,
2316 G_PKG_NAME, '100: p_to_object_type : '||p_to_object_type );
2317 FND_LOG.STRING(G_PROC_LEVEL,
2318 G_PKG_NAME, '100: p_to_object_id : '||p_to_object_id );
2319 END IF;
2320
2321 -- Standard Start of API savepoint
2322 SAVEPOINT g_organize_layout_GRP;
2323 -- Standard call to check for call compatibility.
2324
2325 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2327 END IF;
2328 -- Initialize message list if p_init_msg_list is set to TRUE.
2329 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2330 FND_MSG_PUB.initialize;
2331 END IF;
2332
2333 -- Initialize API return status to success
2334 x_return_status := FND_API.G_RET_STS_SUCCESS;
2335
2336 IF FND_API.To_Boolean( p_validate_commit )
2337 AND NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
2338 p_api_version => l_api_version,
2339 p_init_msg_list => FND_API.G_FALSE,
2340 p_doc_type => p_doc_type,
2341 p_doc_id => p_doc_id,
2342 p_validation_string => p_validation_string,
2343 x_return_status => x_return_status,
2344 x_msg_data => x_msg_data,
2345 x_msg_count => x_msg_count) ) THEN
2346
2347 /*IF (l_debug = 'Y') THEN
2348 okc_debug.log('200: Issue with document header Record.Cannot commit', 2);
2349 END IF;*/
2350
2351 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2352 FND_LOG.STRING(G_PROC_LEVEL,
2353 G_PKG_NAME, '200: Issue with document header Record.Cannot commit' );
2354 END IF;
2355 RAISE FND_API.G_EXC_ERROR ;
2356 END IF;
2357
2358 IF p_ref_point NOT IN ('A','B','S') THEN
2359 /*IF (l_debug = 'Y') THEN
2360 okc_debug.log('800: Error: Ref point should be either A,B or S', 2);
2361 END IF;*/
2362
2363 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2364 FND_LOG.STRING(G_PROC_LEVEL,
2365 G_PKG_NAME, '800: Error: Ref point should be either A,B or S' );
2366 END IF;
2367
2368 Okc_Api.Set_Message( G_FND_APP, 'OKC_WRONG_REF_POINT');
2369 RAISE FND_API.G_EXC_ERROR ;
2370
2371 END IF;
2372
2373 IF p_to_object_type = p_doc_type AND p_ref_point <>'S' THEN
2374 /*IF (l_debug = 'Y') THEN
2375 okc_debug.log('300: Wrong Selection of Item Location', 2);
2376 END IF;*/
2377
2378 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2379 FND_LOG.STRING(G_PROC_LEVEL,
2380 G_PKG_NAME, '300: Wrong Selection of Item Location' );
2381 END IF;
2382
2383 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2384 p_msg_name => 'OKC_WRONG_ITEM_LOCATION'
2385 );
2386
2387 RAISE FND_API.G_EXC_ERROR ;
2388 END IF;
2389
2390 IF p_to_object_type = 'ARTICLE' AND p_ref_point ='S' THEN
2391 /*IF (l_debug = 'Y') THEN
2392 okc_debug.log('400: Wrong Selection of Item Location', 2);
2393 END IF;*/
2394
2395 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2396 FND_LOG.STRING(G_PROC_LEVEL,
2397 G_PKG_NAME, '400: Wrong Selection of Item Location' );
2398 END IF;
2399
2400 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2401 p_msg_name => 'OKC_WRONG_ITEM_LOCATION1'
2402 );
2403
2404 RAISE FND_API.G_EXC_ERROR ;
2405 END IF;
2406
2407 l_organize_tbl := p_organize_tbl;
2408
2409 IF p_organize_tbl.COUNT>0 THEN
2410 FOR i IN p_organize_tbl.FIRST..p_organize_tbl.LAST LOOP
2411 IF p_organize_tbl(i).object_type NOT in ('SECTION','ARTICLE') THEN
2412 /*IF (l_debug = 'Y') THEN
2413 okc_debug.log('500: Wrong Selection ', 2);
2414 END IF;*/
2415
2416 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2417 FND_LOG.STRING(G_PROC_LEVEL,
2418 G_PKG_NAME, '500: Wrong Selection ' );
2419 END IF;
2420
2421 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2422 p_msg_name => 'OKC_WRONG_SELECTION'
2423 );
2424 RAISE FND_API.G_EXC_ERROR ;
2425 END IF;
2426 -- Finding out those records whose parents are already in Table.
2427 -- These records will be deleted.If parents are moved there is no need to move children.
2428 IF p_organize_tbl(i).object_type='ARTICLE' THEN
2429 OPEN l_get_scn_csr(p_organize_tbl(i).id);
2430 FETCH l_get_scn_csr INTO l_scn_id;
2431 CLOSE l_get_scn_csr;
2432 IF l_organize_tbl.COUNT >0 THEN
2433 FOR k in l_organize_tbl.FIRST..l_organize_tbl.LAST LOOP
2434 IF l_organize_tbl(k).object_type='SECTION' and l_organize_tbl(k).id= l_scn_id THEN
2435 l_del_list_tbl(l_del_list_tbl.count+1) := i;
2436 END IF;
2437 END LOOP;
2438 END IF;
2439 ELSE
2440 l_scn_id := p_organize_tbl(i).id;
2441 END IF;
2442
2443 FOR cr in l_get_parents_csr(l_scn_id) LOOP
2444 IF l_organize_tbl.COUNT >0 THEN
2445 FOR k in l_organize_tbl.FIRST..l_organize_tbl.LAST LOOP
2446 IF l_organize_tbl(k).object_type='SECTION' and l_organize_tbl(k).id= cr.id THEN
2447 l_del_list_tbl(l_del_list_tbl.count+1) := i;
2448 END IF;
2449 END LOOP;
2450 END IF;
2451 END LOOP;
2452
2453 END LOOP;
2454 END IF;
2455
2456
2457 IF p_to_object_type ='ARTICLE' THEN
2458 OPEN l_get_scn_csr(p_to_object_id);
2459 FETCH l_get_scn_csr INTO l_parent_scn_id;
2460 CLOSE l_get_scn_csr;
2461
2462 ELSIF p_to_object_type ='SECTION' THEN
2463 OPEN l_get_parent_scn_csr(p_to_object_id);
2464 FETCH l_get_parent_scn_csr INTO l_parent_scn_id;
2465 CLOSE l_get_parent_scn_csr;
2466
2467 ELSIF p_to_object_type = p_doc_type THEN
2468
2469 -- Case of Adding a section at TOP Level.
2470
2471 OPEN l_get_max_seq_csr(Null);
2472 FETCH l_get_max_seq_csr INTO l_ref_sequence;
2473 CLOSE l_get_max_seq_csr;
2474 l_ref_is_set := TRUE;
2475 l_parent_scn_id := NULL;
2476
2477 ELSE
2478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2479 END IF;
2480
2481
2482 IF NOT l_ref_is_set THEN
2483
2484 IF p_ref_point IN ('A','B') THEN
2485 FOR cr IN l_get_scn_child(l_parent_scn_id) LOOP
2486
2487 j := j+1;
2488 scn_child_tbl(j).id := cr.id;
2489 scn_child_tbl(j).display_sequence := cr.display_seq;
2490 scn_child_tbl(j).object_type := cr.obj_type;
2491
2492 IF scn_child_tbl(j).object_type=p_to_object_type AND scn_child_tbl(j).id = p_to_object_id THEN
2493 l_ref_count := j;
2494 END IF;
2495
2496 END LOOP;
2497
2498 IF p_ref_point ='B' THEN
2499 l_ref_count := l_ref_count -1 ;
2500
2501 IF l_ref_count = 0 THEN
2502 l_ref_sequence := 0;
2503 ELSIF l_ref_count > 0 THEN
2504 l_ref_sequence := scn_child_tbl(l_ref_count).display_sequence;
2505 ELSE
2506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2507 END IF;
2508 ELSIF p_ref_point ='A' THEN
2509 IF l_ref_count > 0 THEN
2510 l_ref_sequence := scn_child_tbl(l_ref_count).display_sequence;
2511 ELSE
2512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2513 END IF;
2514 ELSE
2515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2516 END IF;
2517 l_ref_is_set := TRUE;
2518
2519 ELSIF p_ref_point='S' THEN
2520 IF p_to_object_type ='SECTION' THEN
2521
2522 l_parent_scn_id := p_to_object_id;
2523 OPEN l_get_max_seq_csr(l_parent_scn_id );
2524 FETCH l_get_max_seq_csr INTO l_ref_sequence;
2525 CLOSE l_get_max_seq_csr;
2526
2527 OPEN l_get_max_art_seq_csr(l_parent_scn_id );
2528 FETCH l_get_max_art_seq_csr INTO l_ref_sequence1;
2529 CLOSE l_get_max_art_seq_csr;
2530
2531 IF l_ref_sequence1>l_ref_sequence THEN
2532 l_ref_sequence :=l_ref_sequence1;
2533 END IF;
2534 l_ref_is_set := TRUE;
2535
2536 ELSE
2537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2538 END IF;
2539 ELSE
2540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2541 END IF;
2542
2543 END IF;
2544
2545 IF p_organize_tbl.COUNT>0 THEN
2546 FOR i IN p_organize_tbl.FIRST..p_organize_tbl.LAST LOOP
2547 l_not_deleted := TRUE;
2548 IF l_del_list_tbl.COUNT > 0 THEN
2549 FOR k IN l_del_list_tbl.FIRST..l_del_list_tbl.LAST LOOP
2550 IF i = l_del_list_tbl(k) THEN
2551 l_not_deleted := FALSE;
2552 END IF;
2553 END LOOP;
2554 END IF;
2555
2556 IF l_not_deleted THEN
2557 IF p_organize_tbl(i).object_type='ARTICLE' THEN
2558
2559 /*IF (l_debug = 'Y') THEN
2560 okc_debug.log('600: Updating Display Sequence of cat_id '||p_organize_tbl(i).id, 2);
2561 END IF;*/
2562
2563 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2564 FND_LOG.STRING(G_PROC_LEVEL,
2565 G_PKG_NAME, '600: Updating Display Sequence of cat_id '||p_organize_tbl(i).id );
2566 END IF;
2567
2568 IF l_parent_scn_id IS NULL THEN
2569 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2570 p_msg_name => 'OKC_ART_MOVE_TO_DOC'
2571 );
2572 RAISE FND_API.G_EXC_ERROR ;
2573 END IF;
2574 l_ref_sequence := l_ref_sequence+10;
2575
2576 OKC_K_ARTICLES_GRP.update_article(
2577 p_api_version =>1,
2578 p_init_msg_list => OKC_API.G_FALSE,
2579 x_return_status => x_return_status,
2580 x_msg_count => x_msg_count,
2581 x_msg_data => x_msg_data,
2582 p_id => p_organize_tbl(i).id,
2583 p_scn_id => l_parent_scn_id,
2584 p_display_sequence => l_ref_sequence,
2585 p_object_version_number => Null
2586 );
2587 --------------------------------------------
2588 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2590 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2591 RAISE FND_API.G_EXC_ERROR ;
2592 END IF;
2593 --------------------------------------------
2594 ELSIF p_organize_tbl(i).object_type='SECTION' THEN
2595
2596 /*IF (l_debug = 'Y') THEN
2597 okc_debug.log('700: Updating Display Sequence of scn_id '||p_organize_tbl(i).id, 2);
2598 END IF;*/
2599
2600 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2601 FND_LOG.STRING(G_PROC_LEVEL,
2602 G_PKG_NAME, '700: Updating Display Sequence of scn_id '||p_organize_tbl(i).id );
2603 END IF;
2604
2605 l_ref_sequence := l_ref_sequence+10;
2606 OKC_TERMS_SECTIONS_GRP.update_section(
2607 p_api_version =>1,
2608 p_init_msg_list => OKC_API.G_FALSE,
2609 x_return_status => x_return_status,
2610 x_msg_count => x_msg_count,
2611 x_msg_data => x_msg_data,
2612 p_id => p_organize_tbl(i).id,
2613 p_scn_id => nvl(l_parent_scn_id,OKC_API.G_MISS_NUM),
2614 p_section_sequence => l_ref_sequence,
2615 p_object_version_number =>Null
2616 );
2617 --------------------------------------------
2618 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2620 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2621 RAISE FND_API.G_EXC_ERROR ;
2622 END IF;
2623
2624 END IF; -- IF p_organize_tbl(i).object_type='ARTICLE' THEN
2625
2626 END IF;
2627
2628 END LOOP;
2629 END IF;
2630
2631
2632 IF scn_child_tbl.COUNT > 0 THEN
2633 FOR k IN scn_child_tbl.FIRST..scn_child_tbl.LAST LOOP
2634 IF k > l_ref_count THEN
2635
2636 l_dont_move := FALSE;
2637 For m IN p_organize_tbl.FIRST..p_organize_tbl.last LOOP
2638 IF scn_child_tbl(k).id=p_organize_tbl(m).id and scn_child_tbl(k).object_type=p_organize_tbl(m).object_type THEN
2639 l_dont_move := TRUE;
2640 exit;
2641 END IF;
2642 END LOOP;
2643 IF not l_dont_move THEN
2644
2645 l_ref_sequence := l_ref_sequence + 10;
2646 IF scn_child_tbl(k).object_type='ARTICLE' THEN
2647
2648 /*IF (l_debug = 'Y') THEN
2649 okc_debug.log('800: Updating Display Sequence of cat_id '||scn_child_tbl(k).id, 2);
2650 END IF;*/
2651
2652 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2653 FND_LOG.STRING(G_PROC_LEVEL,
2654 G_PKG_NAME, '800: Updating Display Sequence of cat_id '||scn_child_tbl(k).id );
2655 END IF;
2656
2657 OKC_K_ARTICLES_GRP.update_article(
2658 p_api_version =>1,
2659 p_init_msg_list => OKC_API.G_FALSE,
2660 x_return_status => x_return_status,
2661 x_msg_count => x_msg_count,
2662 x_msg_data => x_msg_data,
2663 p_id => scn_child_tbl(k).id,
2664 p_display_sequence => l_ref_sequence,
2665 p_object_version_number => Null
2666 );
2667 --------------------------------------------
2668 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2670 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2671 RAISE FND_API.G_EXC_ERROR ;
2672 END IF;
2673 --------------------------------------------
2674 ELSIF scn_child_tbl(k).object_type='SECTION' THEN
2675
2676 /*IF (l_debug = 'Y') THEN
2677 okc_debug.log('900: Updating Display Sequence of scn_id '||scn_child_tbl(k).id, 2);
2678 END IF;*/
2679
2680 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2681 FND_LOG.STRING(G_PROC_LEVEL,
2682 G_PKG_NAME, '900: Updating Display Sequence of scn_id '||scn_child_tbl(k).id );
2683 END IF;
2684
2685 OKC_TERMS_SECTIONS_GRP.update_section(
2686 p_api_version =>1,
2687 p_init_msg_list => OKC_API.G_FALSE,
2688 x_return_status => x_return_status,
2689 x_msg_count => x_msg_count,
2690 x_msg_data => x_msg_data,
2691 p_id => scn_child_tbl(k).id,
2692 p_section_sequence => l_ref_sequence,
2693 p_object_version_number =>Null
2694 );
2695 --------------------------------------------
2696 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
2697 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
2698 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
2699 RAISE FND_API.G_EXC_ERROR ;
2700 END IF;
2701
2702 END IF; -- IF scn_child_tbl(k).object_type='ARTICLE' THEN
2703 END IF;
2704 END IF;
2705 END LOOP;
2706 END IF;
2707
2708 -- Standard check of p_commit
2709 IF FND_API.To_Boolean( p_commit ) THEN
2710 COMMIT WORK;
2711 END IF;
2712
2713 -- Standard call to get message count and if count is 1, get message info.
2714 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2715
2716 /*IF (l_debug = 'Y') THEN
2717 okc_debug.log('1000: Leaving organize_layout', 2);
2718 END IF;*/
2719
2720 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2721 FND_LOG.STRING(G_PROC_LEVEL,
2722 G_PKG_NAME, '1000: Leaving organize_layout' );
2723 END IF;
2724
2725 EXCEPTION
2726 WHEN FND_API.G_EXC_ERROR THEN
2727 /*IF (l_debug = 'Y') THEN
2728 okc_debug.log('1100: Leaving organize_layout: OKC_API.G_EXCEPTION_ERROR Exception', 2);
2729 END IF;*/
2730
2731 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2732 FND_LOG.STRING(G_EXCP_LEVEL,
2733 G_PKG_NAME, '1100: Leaving organize_layout: OKC_API.G_EXCEPTION_ERROR Exception' );
2734 END IF;
2735
2736 IF l_get_scn_child%ISOPEN THEN
2737 CLOSE l_get_scn_child;
2738 END IF;
2739
2740 IF l_get_parents_csr%ISOPEN THEN
2741 CLOSE l_get_scn_child;
2742 END IF;
2743
2744 IF l_get_parent_scn_csr%ISOPEN THEN
2745 CLOSE l_get_scn_child;
2746 END IF;
2747
2748 IF l_get_scn_csr%ISOPEN THEN
2749 CLOSE l_get_scn_child;
2750 END IF;
2751
2752 IF l_get_max_seq_csr%ISOPEN THEN
2753 CLOSE l_get_scn_child;
2754 ROLLBACK TO g_organize_layout_GRP;
2755 END IF;
2756 ROLLBACK TO g_organize_layout_GRP;
2757 x_return_status := G_RET_STS_ERROR ;
2758 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2759
2760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2761 /*IF (l_debug = 'Y') THEN
2762 okc_debug.log('1200: Leaving organize_layout: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2763 END IF;*/
2764
2765 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2766 FND_LOG.STRING(G_EXCP_LEVEL,
2767 G_PKG_NAME, '1200: Leaving organize_layout: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
2768 END IF;
2769
2770 IF l_get_scn_child%ISOPEN THEN
2771 CLOSE l_get_scn_child;
2772 END IF;
2773
2774 IF l_get_parents_csr%ISOPEN THEN
2775 CLOSE l_get_scn_child;
2776 END IF;
2777
2778 IF l_get_parent_scn_csr%ISOPEN THEN
2779 CLOSE l_get_scn_child;
2780 END IF;
2781
2782 IF l_get_scn_csr%ISOPEN THEN
2783 CLOSE l_get_scn_child;
2784 END IF;
2785
2786 IF l_get_max_seq_csr%ISOPEN THEN
2787 CLOSE l_get_scn_child;
2788 ROLLBACK TO g_organize_layout_GRP;
2789 END IF;
2790
2791 x_return_status := G_RET_STS_UNEXP_ERROR ;
2792 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2793
2794 WHEN OTHERS THEN
2795 /*IF (l_debug = 'Y') THEN
2796 okc_debug.log('1300: Leaving organize_layout because of EXCEPTION: '||sqlerrm, 2);
2797 END IF;*/
2798
2799 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
2800 FND_LOG.STRING(G_EXCP_LEVEL,
2801 G_PKG_NAME, '1300: Leaving organize_layout because of EXCEPTION: '||sqlerrm );
2802 END IF;
2803
2804 IF l_get_scn_child%ISOPEN THEN
2805 CLOSE l_get_scn_child;
2806 END IF;
2807
2808 IF l_get_parents_csr%ISOPEN THEN
2809 CLOSE l_get_scn_child;
2810 END IF;
2811
2812 IF l_get_parent_scn_csr%ISOPEN THEN
2813 CLOSE l_get_scn_child;
2814 END IF;
2815
2816 IF l_get_scn_csr%ISOPEN THEN
2817 CLOSE l_get_scn_child;
2818 END IF;
2819
2820 IF l_get_max_seq_csr%ISOPEN THEN
2821 CLOSE l_get_scn_child;
2822 END IF;
2823
2824 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
2825 p_msg_name => G_UNEXPECTED_ERROR,
2826 p_token1 => G_SQLCODE_TOKEN,
2827 p_token1_value => sqlcode,
2828 p_token2 => G_SQLERRM_TOKEN,
2829 p_token2_value => sqlerrm);
2830
2831 ROLLBACK TO g_organize_layout_GRP;
2832
2833 x_return_status := G_RET_STS_UNEXP_ERROR ;
2834
2835 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2836 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
2837 END IF;
2838 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
2839 END organize_layout;
2840
2841
2842
2843
2844 PROCEDURE merge_review_clauses(
2845 p_api_version IN NUMBER,
2846 p_init_msg_list IN VARCHAR2 ,
2847 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2848 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
2849 p_validation_string IN VARCHAR2,
2850 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2851 p_merge_review_tbl IN MERGE_REVIEW_TBL_TYPE,
2852 p_doc_type IN VARCHAR2,
2853 p_doc_id IN NUMBER,
2854 x_return_status OUT NOCOPY VARCHAR2,
2855 x_msg_count OUT NOCOPY NUMBER,
2856 x_msg_data OUT NOCOPY VARCHAR2
2857 ) IS
2858
2859 l_api_version CONSTANT NUMBER := 1;
2860 l_api_name CONSTANT VARCHAR2(30) := 'merge_review_clauses';
2861 l_merge_review_tbl MERGE_REVIEW_TBL_TYPE;
2862 l_scn_id NUMBER;
2863 l_parent_scn_id NUMBER;
2864 l_ref_is_set BOOLEAN :=FALSE;
2865 l_not_deleted BOOLEAN;
2866 l_ref_sequence NUMBER;
2867 l_ref_sequence1 NUMBER;
2868 j NUMBER := 0;
2869 l_ref_count NUMBER;
2870 l_dont_move Boolean;
2871 l_article_ibr VARCHAR2(1);
2872 l_article_mandatory VARCHAR2(1);
2873 l_article_text_locked VARCHAR2(1);
2874 l_final_article_text OKC_REVIEW_UPLD_TERMS.OBJECT_TEXT%TYPE;
2875 l_temp_article_text OKC_REVIEW_UPLD_TERMS.OBJECT_TEXT%TYPE;
2876 l_doc_num_scheme NUMBER;
2877 l_user_access VARCHAR2(30);
2878 l_new_action OKC_REVIEW_UPLD_TERMS.ACTION%TYPE := 'UPDATED';
2879 l_temp_action OKC_REVIEW_UPLD_TERMS.ACTION%TYPE;
2880 l_final_err_msg_count OKC_REVIEW_UPLD_TERMS.ERROR_MESSAGE_COUNT%TYPE := 0;
2881 l_temp_err_msg_count OKC_REVIEW_UPLD_TERMS.ERROR_MESSAGE_COUNT%TYPE := 0;
2882 l_final_wrn_msg_count OKC_REVIEW_UPLD_TERMS.WARNING_MESSAGE_COUNT%TYPE := 0;
2883 l_temp_wrn_msg_count OKC_REVIEW_UPLD_TERMS.WARNING_MESSAGE_COUNT%TYPE := 0;
2884
2885 CURSOR l_get_article_text_csr(b_review_upld_terms_id NUMBER) is
2886 SELECT object_text,
2887 action,
2888 NVL(error_message_count,0) error_message_count,
2889 NVL(warning_message_count,0) warning_message_count
2890 FROM okc_review_upld_terms
2891 WHERE review_upld_terms_id = b_review_upld_terms_id;
2892
2893 --As part of fix for bug# 4779506, added this cursor replacing the previously used cursors
2894 CURSOR article_properties(b_review_upld_terms_id NUMBER) is
2895 SELECT av.insert_by_reference,
2896 akb.mandatory_yn,
2897 av.lock_text
2898 FROM okc_article_versions av, okc_review_upld_terms ar, okc_k_articles_b akb
2899 WHERE av.article_version_id = ar.article_version_id
2900 AND akb.id = ar.object_id
2901 AND ar.review_upld_terms_id = b_review_upld_terms_id;
2902
2903 CURSOR current_num_scheme is
2904 SELECT doc_numbering_scheme from okc_template_usages
2905 WHERE document_type = p_doc_type
2906 AND document_id = p_doc_id;
2907
2908
2909 BEGIN
2910
2911 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2912 FND_LOG.STRING(G_PROC_LEVEL,
2913 G_PKG_NAME, '100: Entered merge_review_clauses' );
2914 FND_LOG.STRING(G_PROC_LEVEL,
2915 G_PKG_NAME, '100: Parameter List ' );
2916 FND_LOG.STRING(G_PROC_LEVEL,
2917 G_PKG_NAME, '100: p_api_version : '||p_api_version );
2918 FND_LOG.STRING(G_PROC_LEVEL,
2919 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
2920 FND_LOG.STRING(G_PROC_LEVEL,
2921 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
2922 FND_LOG.STRING(G_PROC_LEVEL,
2923 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
2924 FND_LOG.STRING(G_PROC_LEVEL,
2925 G_PKG_NAME, '100: p_commit : '||p_commit );
2926 FND_LOG.STRING(G_PROC_LEVEL,
2927 G_PKG_NAME, '100: p_doc_type : '||p_doc_type );
2928 FND_LOG.STRING(G_PROC_LEVEL,
2929 G_PKG_NAME, '100: p_doc_id : '||p_doc_id );
2930 END IF;
2931
2932 -- Standard Start of API savepoint
2933 SAVEPOINT g_merge_review_clauses_GRP;
2934 -- Standard call to check for call compatibility.
2935
2936 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2937 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2938 END IF;
2939 -- Initialize message list if p_init_msg_list is set to TRUE.
2940 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2941 FND_MSG_PUB.initialize;
2942 END IF;
2943
2944 -- Initialize API return status to success
2945 x_return_status := FND_API.G_RET_STS_SUCCESS;
2946
2947 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_STD','N') THEN
2948 l_user_access := 'STD_AUTHOR';
2949 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_NON_STD','N') THEN
2950 l_user_access := 'NON_STD_AUTHOR';
2951 IF FND_FUNCTION.TEST('OKC_TERMS_AUTHOR_SUPERUSER','N') THEN
2952 l_user_access := 'SUPER_USER';
2953 END IF;
2954 END IF;
2955 ELSE
2956 l_user_access := 'NO_ACCESS';
2957 END IF;
2958
2959 IF FND_API.To_Boolean( p_validate_commit )
2960 AND NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
2961 p_api_version => l_api_version,
2962 p_init_msg_list => FND_API.G_FALSE,
2963 p_doc_type => p_doc_type,
2964 p_doc_id => p_doc_id,
2965 p_validation_string => p_validation_string,
2966 x_return_status => x_return_status,
2967 x_msg_data => x_msg_data,
2968 x_msg_count => x_msg_count) ) THEN
2969
2970
2971 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
2972 FND_LOG.STRING(G_PROC_LEVEL,
2973 G_PKG_NAME, '200: Issue with document header Record.Cannot commit' );
2974 END IF;
2975 RAISE FND_API.G_EXC_ERROR ;
2976 END IF;
2977
2978 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
2979 Process the records to update the first clause text with the clause text
2980 of the rest of the clauses.
2981 Check is made for clause not being IBR, lock_text, mandatory
2982 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
2983 */
2984 IF p_merge_review_tbl.COUNT>0 THEN
2985 OPEN l_get_article_text_csr(p_merge_review_tbl(1).review_upld_terms_id);
2986 FETCH l_get_article_text_csr INTO
2987 l_final_article_text,
2988 l_new_action,
2989 l_final_err_msg_count,
2990 l_final_wrn_msg_count;
2991 CLOSE l_get_article_text_csr;
2992
2993
2994 l_article_ibr := 'N';
2995 l_article_mandatory := 'N';
2996 l_article_text_locked := 'N';
2997 IF p_merge_review_tbl(1).object_type <> 'ARTICLE' THEN
2998
2999 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3000 FND_LOG.STRING(G_PROC_LEVEL,
3001 G_PKG_NAME, '500: Wrong Selection ' );
3002 END IF;
3003 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3004 p_msg_name => 'OKC_WRONG_SELECTION'
3005 );
3006 RAISE FND_API.G_EXC_ERROR ;
3007 END IF;
3008
3009 OPEN article_properties(p_merge_review_tbl(1).review_upld_terms_id);
3010 FETCH article_properties INTO
3011 l_article_ibr,
3012 l_article_mandatory,
3013 l_article_text_locked;
3014 CLOSE article_properties;
3015
3016 IF l_article_ibr = 'Y' and (l_user_access <> 'SUPER_USER') THEN
3017
3018 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3019 FND_LOG.STRING(G_PROC_LEVEL,
3020 G_PKG_NAME, '500: Article is IBR enabled ' );
3021 END IF;
3022 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3023 p_msg_name => 'OKC_ARTICLE_IS_IBR'
3024 );
3025 RAISE FND_API.G_EXC_ERROR ;
3026 END IF;
3027
3028 IF l_article_text_locked = 'Y' AND (l_user_access <> 'SUPER_USER') THEN
3029
3030 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3031 FND_LOG.STRING(G_PROC_LEVEL,
3032 G_PKG_NAME, '500: Article is Text Locked ' );
3033 END IF;
3034 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3035 p_msg_name => 'OKC_ARTICLE_IS_LOCK_TEXT');
3036 RAISE FND_API.G_EXC_ERROR ;
3037 END IF;
3038
3039 FOR i IN 2 ..p_merge_review_tbl.LAST LOOP
3040 l_article_ibr := 'N';
3041 l_article_mandatory := 'N';
3042 l_article_text_locked := 'N';
3043 IF p_merge_review_tbl(i).object_type <> 'ARTICLE' THEN
3044
3045 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3046 FND_LOG.STRING(G_PROC_LEVEL,
3047 G_PKG_NAME, '500: Wrong Selection ' );
3048 END IF;
3049 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3050 p_msg_name => 'OKC_WRONG_SELECTION');
3051 RAISE FND_API.G_EXC_ERROR ;
3052 END IF;
3053 OPEN article_properties(p_merge_review_tbl(i).review_upld_terms_id);
3054 FETCH article_properties into l_article_ibr, l_article_mandatory, l_article_text_locked;
3055 CLOSE article_properties;
3056
3057 -- Fix for bug# 4779506. Changed check to NOT and changed parameter for fnd_function.test
3058 IF l_article_mandatory = 'Y' AND (l_user_access <> 'SUPER_USER') THEN
3059
3060 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3061 FND_LOG.STRING(G_PROC_LEVEL,
3062 G_PKG_NAME, '500: Article is Mandatory ' );
3063 END IF;
3064 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3065 p_msg_name => 'OKC_REVIEW_MERGE_MANDATORY'
3066 );
3067 RAISE FND_API.G_EXC_ERROR ;
3068 END IF;
3069
3070 IF p_merge_review_tbl(i).object_type='ARTICLE' THEN
3071 OPEN l_get_article_text_csr(p_merge_review_tbl(i).review_upld_terms_id);
3072 FETCH l_get_article_text_csr INTO
3073 l_temp_article_text,
3074 l_temp_action,
3075 l_temp_err_msg_count,
3076 l_temp_wrn_msg_count;
3077 CLOSE l_get_article_text_csr;
3078 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3079 FND_LOG.STRING(G_PROC_LEVEL,
3080 G_PKG_NAME, '500: l_temp_action : '||l_temp_action );
3081 FND_LOG.STRING(G_PROC_LEVEL,
3082 G_PKG_NAME, '500: l_temp_err_msg_count : '||l_temp_err_msg_count);
3083 FND_LOG.STRING(G_PROC_LEVEL,
3084 G_PKG_NAME, '500: l_temp_wrn_msg_count : '||l_temp_wrn_msg_count);
3085 END IF;
3086 l_final_article_text := l_final_article_text || l_temp_article_text;
3087 l_final_err_msg_count := l_final_err_msg_count + l_temp_err_msg_count;
3088 l_final_wrn_msg_count := l_final_wrn_msg_count + l_temp_wrn_msg_count;
3089
3090 END IF;
3091
3092 /**********************************************************************
3093 Update the records being merged with ACTION=MERGED (Soft Delete)
3094 ***********************************************************************/
3095 -- Added for Bug 5339759
3096 UPDATE okc_review_upld_terms
3097 SET
3098 old_review_upld_terms_id = review_upld_terms_id
3099 WHERE review_upld_terms_id = p_merge_review_tbl(i).review_upld_terms_id;
3100
3101 OKC_REVIEW_UPLD_TERMS_PVT.update_row(
3102 x_return_status => x_return_status,
3103 p_review_upld_terms_id => p_merge_review_tbl(i).review_upld_terms_id,
3104 p_action => 'MERGED',
3105 p_object_version_number => p_merge_review_tbl(i).object_version_number,
3106 p_new_parent_id => G_MISS_NUM
3107 );
3108 ----------------------------------------------------------------------
3109 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3111 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3112 RAISE FND_API.G_EXC_ERROR ;
3113 END IF;
3114 ----------------------------------------------------------------------
3115
3116 -- Added for Bug 5339759
3117 UPDATE okc_review_var_values
3118 SET
3119 old_review_upld_terms_id = review_upld_terms_id,
3120 review_upld_terms_id = p_merge_review_tbl(1).review_upld_terms_id
3121 WHERE review_upld_terms_id = p_merge_review_tbl(i).review_upld_terms_id;
3122 END LOOP;
3123
3124 /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
3125 Update the OKC_REVIEW_UPLD_TERMS table for the first clause
3126 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
3127 -- Added for bug# 4897442
3128
3129 IF(l_new_action = 'ADDED') THEN
3130 l_new_action := 'ADDED';
3131 ELSE
3132 l_new_action := 'UPDATED';
3133 END IF;
3134
3135
3136 IF l_final_err_msg_count <= 0 THEN
3137 l_final_err_msg_count := G_MISS_NUM;
3138 END IF;
3139 IF l_final_wrn_msg_count <= 0 THEN
3140 l_final_wrn_msg_count := G_MISS_NUM;
3141 END IF;
3142
3143 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3144 FND_LOG.STRING(G_PROC_LEVEL,
3145 G_PKG_NAME, '500: l_new_action : '||l_new_action );
3146 FND_LOG.STRING(G_PROC_LEVEL,
3147 G_PKG_NAME, '500: l_final_err_msg_count : '||l_final_err_msg_count);
3148 FND_LOG.STRING(G_PROC_LEVEL,
3149 G_PKG_NAME, '500: l_final_wrn_msg_count : '||l_final_wrn_msg_count);
3150 END IF;
3151
3152 OKC_REVIEW_UPLD_TERMS_PVT.update_row(
3153 x_return_status => x_return_status,
3154 p_review_upld_terms_id => p_merge_review_tbl(1).review_upld_terms_id,
3155 p_action => l_new_action,
3156 p_object_text => l_final_article_text,
3157 p_object_version_number => NULL,
3158 p_error_message_count => l_final_err_msg_count,
3159 p_warning_message_count => l_final_wrn_msg_count );
3160 ----------------------------------------------------------------------
3161 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3163 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3164 RAISE FND_API.G_EXC_ERROR ;
3165 END IF;
3166 ----------------------------------------------------------------------
3167
3168
3169 OPEN current_num_scheme;
3170 FETCH current_num_scheme into l_doc_num_scheme;
3171 CLOSE current_num_scheme;
3172
3173 IF (l_doc_num_scheme is NOT NULL) then
3174 OKC_NUMBER_SCHEME_GRP.apply_num_scheme_4_review(
3175 p_api_version => p_api_version,
3176 p_init_msg_list => p_init_msg_list,
3177 x_return_status => x_return_status,
3178 x_msg_count => x_msg_count,
3179 x_msg_data => x_msg_data,
3180 p_commit => p_commit,
3181 p_validation_string => p_validation_string,
3182 p_doc_type => p_doc_type,
3183 p_doc_id => p_doc_id,
3184 p_num_scheme_id => l_doc_num_scheme);
3185 END IF;
3186
3187 END IF; -- IF p_merge_review_tbl.COUNT>0
3188
3189 -- Standard check of p_commit
3190 IF FND_API.To_Boolean( p_commit ) THEN
3191 COMMIT WORK;
3192 END IF;
3193
3194 -- Standard call to get message count and if count is 1, get message info.
3195 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3196
3197
3198 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3199 FND_LOG.STRING(G_PROC_LEVEL,
3200 G_PKG_NAME, '1000: Leaving merge_review_clauses' );
3201 END IF;
3202
3203 EXCEPTION
3204 WHEN FND_API.G_EXC_ERROR THEN
3205
3206 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
3207 FND_LOG.STRING(G_EXCP_LEVEL,
3208 G_PKG_NAME, '1100: Leaving merge_review_clauses: OKC_API.G_EXCEPTION_ERROR Exception' );
3209 END IF;
3210
3211 IF article_properties%ISOPEN THEN
3212 CLOSE article_properties;
3213 END IF;
3214
3215
3216
3217 ROLLBACK TO g_merge_review_clauses_GRP;
3218 x_return_status := G_RET_STS_ERROR ;
3219 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3220
3221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3222
3223 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
3224 FND_LOG.STRING(G_EXCP_LEVEL,
3225 G_PKG_NAME, '1200: Leaving organize_layout: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception' );
3226 END IF;
3227
3228 IF article_properties%ISOPEN THEN
3229 CLOSE article_properties;
3230 END IF;
3231
3232
3233 ROLLBACK TO g_merge_review_clauses_GRP;
3234
3235 x_return_status := G_RET_STS_UNEXP_ERROR ;
3236 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3237
3238 WHEN OTHERS THEN
3239 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
3240 FND_LOG.STRING(G_EXCP_LEVEL,
3241 G_PKG_NAME, '1300: Leaving organize_layout because of EXCEPTION: '||sqlerrm );
3242 END IF;
3243
3244 IF article_properties%ISOPEN THEN
3245 CLOSE article_properties;
3246 END IF;
3247
3248 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3249 p_msg_name => G_UNEXPECTED_ERROR,
3250 p_token1 => G_SQLCODE_TOKEN,
3251 p_token1_value => sqlcode,
3252 p_token2 => G_SQLERRM_TOKEN,
3253 p_token2_value => sqlerrm);
3254
3255 ROLLBACK TO g_merge_review_clauses_GRP;
3256
3257 x_return_status := G_RET_STS_UNEXP_ERROR ;
3258
3259 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3260 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3261 END IF;
3262 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3263 END merge_review_clauses;
3264
3265
3266 PROCEDURE sort_clauses(
3267 p_api_version IN NUMBER,
3268 p_init_msg_list IN VARCHAR2 ,
3269 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3270 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
3271 p_validation_string IN VARCHAR2,
3272 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3273 p_doc_type IN VARCHAR2,
3274 p_doc_id IN NUMBER,
3275 x_return_status OUT NOCOPY VARCHAR2,
3276 x_msg_count OUT NOCOPY NUMBER,
3277 x_msg_data OUT NOCOPY VARCHAR2
3278 ) IS
3279
3280 l_api_version CONSTANT NUMBER := 1;
3281 l_api_name CONSTANT VARCHAR2(30) := 'sort_clauses';
3282
3283 CURSOR l_get_num_scheme_id IS
3284 SELECT doc_numbering_scheme
3285 FROM okc_template_usages
3286 WHERE document_type = p_doc_type
3287 AND document_id = p_doc_id;
3288
3289
3290 l_disp_seq NUMBER := 10;
3291 l_section_id NUMBER := NULL;
3292 l_renumber_flag VARCHAR2(1) :='Y';
3293 l_num_scheme_id NUMBER:=0;
3294 cont_art_tbl OKC_CODE_HOOK.cont_art_sort_tbl;
3295
3296
3297
3298 BEGIN
3299
3300 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3301 FND_LOG.STRING(G_PROC_LEVEL,
3302 G_PKG_NAME, '100: Entered sort_clauses' );
3303 FND_LOG.STRING(G_PROC_LEVEL,
3304 G_PKG_NAME, '100: Parameter List ' );
3305 FND_LOG.STRING(G_PROC_LEVEL,
3306 G_PKG_NAME, '100: p_api_version : '||p_api_version );
3307 FND_LOG.STRING(G_PROC_LEVEL,
3308 G_PKG_NAME, '100: p_init_msg_list : '||p_init_msg_list );
3309 FND_LOG.STRING(G_PROC_LEVEL,
3310 G_PKG_NAME, '100: p_validate_commit : '||p_validate_commit );
3311 FND_LOG.STRING(G_PROC_LEVEL,
3312 G_PKG_NAME, '100: p_validation_string : '||p_validation_string );
3313 FND_LOG.STRING(G_PROC_LEVEL,
3314 G_PKG_NAME, '100: p_commit : '||p_commit );
3315 FND_LOG.STRING(G_PROC_LEVEL,
3316 G_PKG_NAME, '100: p_doc_type : '||p_doc_type );
3317 FND_LOG.STRING(G_PROC_LEVEL,
3318 G_PKG_NAME, '100: p_doc_id : '||p_doc_id );
3319 END IF;
3320
3321 -- Standard Start of API savepoint
3322 SAVEPOINT g_sort_clauses_GRP;
3323 -- Standard call to check for call compatibility.
3324
3325 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3327 END IF;
3328 -- Initialize message list if p_init_msg_list is set to TRUE.
3329 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3330 FND_MSG_PUB.initialize;
3331 END IF;
3332
3333 -- Initialize API return status to success
3334 x_return_status := FND_API.G_RET_STS_SUCCESS;
3335
3336 IF FND_API.To_Boolean( p_validate_commit )
3337 AND NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
3338 p_api_version => l_api_version,
3339 p_init_msg_list => FND_API.G_FALSE,
3340 p_doc_type => p_doc_type,
3341 p_doc_id => p_doc_id,
3342 p_validation_string => p_validation_string,
3343 x_return_status => x_return_status,
3344 x_msg_data => x_msg_data,
3345 x_msg_count => x_msg_count) ) THEN
3346
3347 /*IF (l_debug = 'Y') THEN
3348 okc_debug.log('200: Issue with document header Record.Cannot commit', 2);
3349 END IF;*/
3350
3351 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3352 FND_LOG.STRING(G_PROC_LEVEL,
3353 G_PKG_NAME, '200: Issue with document header Record.Cannot commit' );
3354 END IF;
3355 RAISE FND_API.G_EXC_ERROR ;
3356 END IF;
3357
3358 OKC_CODE_HOOK.sort_clauses(p_doc_type => p_doc_type,
3359 p_doc_id => p_doc_id,
3360 x_return_status => x_return_status,
3361 x_msg_count => x_msg_count,
3362 x_msg_data => x_msg_data,
3363 x_cont_art_tbl => cont_art_tbl);
3364
3365
3366
3367 FOR i IN cont_art_tbl.first..cont_art_tbl.last
3368 LOOP
3369 IF cont_art_tbl(i).scn_id = l_section_id THEN
3370 l_disp_seq := l_disp_seq + 10;
3371 ELSE
3372 l_disp_seq := 10;
3373 l_section_id:=cont_art_tbl(i).scn_id;
3374 END IF;
3375 OKC_K_ARTICLES_GRP.update_article(
3376 p_api_version =>1,
3377 p_init_msg_list => OKC_API.G_FALSE,
3378 x_return_status => x_return_status,
3379 x_msg_count => x_msg_count,
3380 x_msg_data => x_msg_data,
3381 p_id => cont_art_tbl(i).id,
3382 p_display_sequence => l_disp_seq,
3383 p_object_version_number => Null
3384 );
3385 END LOOP;
3386 --------------------------------------------
3387 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
3388 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3389 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
3390 RAISE FND_API.G_EXC_ERROR ;
3391 END IF;
3392 -------------------------------------------
3393
3394
3395 IF NVL(l_renumber_flag,'N') = 'Y' THEN
3396
3397 OPEN l_get_num_scheme_id;
3398 FETCH l_get_num_scheme_id INTO l_num_scheme_id;
3399 CLOSE l_get_num_scheme_id;
3400
3401 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3402 FND_LOG.STRING(G_PROC_LEVEL,
3403 G_PKG_NAME, '150: l_num_scheme_id : '||l_num_scheme_id );
3404 END IF;
3405
3406 IF NVL(l_num_scheme_id,0) <> 0 THEN
3407
3408
3409 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3410 FND_LOG.STRING(G_PROC_LEVEL,
3411 G_PKG_NAME, '150: Calling apply_numbering_scheme for num_scheme_id : '||l_num_scheme_id );
3412 END IF;
3413
3414 OKC_NUMBER_SCHEME_GRP.apply_numbering_scheme(
3415 p_api_version => p_api_version,
3416 p_init_msg_list => FND_API.G_FALSE,
3417 x_return_status => x_return_status,
3418 x_msg_count => x_msg_count,
3419 x_msg_data => x_msg_data,
3420 p_validate_commit => p_validate_commit,
3421 p_validation_string => p_validation_string,
3422 p_commit => FND_API.G_FALSE,
3423 p_doc_type => p_doc_type,
3424 p_doc_id => p_doc_id,
3425 p_num_scheme_id => l_num_scheme_id
3426 );
3427
3428
3429 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3430 FND_LOG.STRING(G_PROC_LEVEL,
3431 G_PKG_NAME, '150: After Calling apply_numbering_scheme' );
3432 FND_LOG.STRING(G_PROC_LEVEL,
3433 G_PKG_NAME, '150: x_return_status : '||x_return_status );
3434 FND_LOG.STRING(G_PROC_LEVEL,
3435 G_PKG_NAME, '150: x_msg_count : '||x_msg_count );
3436 FND_LOG.STRING(G_PROC_LEVEL,
3437 G_PKG_NAME, '150: x_msg_data : '||x_msg_data );
3438 END IF;
3439
3440 END IF; --l_num_scheme_id is not 0
3441 END IF; -- call renumber automatically
3442
3443
3444 IF FND_API.To_Boolean( p_commit ) THEN
3445 COMMIT WORK;
3446 END IF;
3447
3448 IF ( G_PROC_LEVEL >= G_DBG_LEVEL ) THEN
3449 FND_LOG.STRING(G_PROC_LEVEL,
3450 G_PKG_NAME, '1000: Leaving sort_clauses' );
3451 END IF;
3452
3453 EXCEPTION
3454 WHEN OTHERS THEN
3455
3456 IF ( G_EXCP_LEVEL >= G_DBG_LEVEL ) THEN
3457 FND_LOG.STRING(G_EXCP_LEVEL,
3458 G_PKG_NAME, '1300: Leaving sort_clauses because of EXCEPTION: '||sqlerrm );
3459 END IF;
3460 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
3461 p_msg_name => G_UNEXPECTED_ERROR,
3462 p_token1 => G_SQLCODE_TOKEN,
3463 p_token1_value => sqlcode,
3464 p_token2 => G_SQLERRM_TOKEN,
3465 p_token2_value => sqlerrm);
3466
3467 ROLLBACK TO g_sort_clauses_GRP;
3468
3469 x_return_status := G_RET_STS_UNEXP_ERROR ;
3470
3471 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
3472 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
3473 END IF;
3474 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
3475
3476 END sort_clauses;
3477
3478
3479
3480 END OKC_TERMS_MULTIREC_GRP;