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