[Home] [Help]
PACKAGE BODY: APPS.OKC_K_ART_VARIABLES_GRP
Source
1 PACKAGE BODY OKC_K_ART_VARIABLES_GRP AS
2 /* $Header: OKCGVARB.pls 120.0 2005/05/25 18:15:47 appldev noship $ */
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4
5 ---------------------------------------------------------------------------
6 -- GLOBAL MESSAGE CONSTANTS
7 ---------------------------------------------------------------------------
8 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
9 ---------------------------------------------------------------------------
10 -- GLOBAL VARIABLES
11 ---------------------------------------------------------------------------
12 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_K_ART_VARIABLES__GRP';
13 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
14
15 ------------------------------------------------------------------------------
16 -- GLOBAL CONSTANTS
17 ------------------------------------------------------------------------------
18 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
19 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
20 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
21 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
22 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
23 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_UNEXPECTED_ERROR';
24 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
25 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
26
27
28 Procedure update_article_var_values(
29 p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2 ,
31 p_validate_commit IN VARCHAR2,
32 p_validation_string IN VARCHAR2,
33 p_commit IN VARCHAR2,
34 p_mode IN VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2,
38
39 p_doc_type IN VARCHAR2,
40 p_doc_id IN NUMBER,
41 p_cat_id IN NUMBER,
42 p_amendment_description IN VARCHAR2,
43 p_print_text_yn IN VARCHAR2,
44 p_variable_code IN VARCHAR2,
45 p_variable_value_id IN VARCHAR2,
46 p_variable_value IN VARCHAR2
47
48 ) IS
49
50 l_api_version CONSTANT NUMBER := 1;
51 l_api_name CONSTANT VARCHAR2(30) := 'g_update_article_var_values';
52 l_ovn NUMBER;
53
54 Cursor l_get_art_var_csr IS
55 SELECT object_version_number
56 FROM OKC_K_ART_VARIABLES
57 WHERE cat_id=p_cat_id
58 AND variable_code=p_variable_code;
59
60 BEGIN
61 IF (l_debug = 'Y') THEN
62 okc_debug.log('100: Entered update_article_var_values', 2);
63 END IF;
64
65 -- Standard Start of API savepoint
66 SAVEPOINT g_upd_article_var_values_GRP;
67 -- Standard call to check for call compatibility.
68 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71 -- Initialize message list if p_init_msg_list is set to TRUE.
72 IF FND_API.to_Boolean( p_init_msg_list ) THEN
73 FND_MSG_PUB.initialize;
74 END IF;
75
76 IF FND_API.To_Boolean( p_validate_commit ) THEN
77
78 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
79 p_api_version => l_api_version,
80 p_init_msg_list => FND_API.G_FALSE,
81 p_doc_type => p_doc_type,
82 p_doc_id => p_doc_id,
83 p_validation_string => p_validation_string,
84 x_return_status => x_return_status,
85 x_msg_data => x_msg_data,
86 x_msg_count => x_msg_count) ) THEN
87
88 IF (l_debug = 'Y') THEN
89 okc_debug.log('110: Issue with document header Record.Cannot commit', 2);
90 END IF;
91 RAISE FND_API.G_EXC_ERROR ;
92 END IF;
93 END IF;
94
95 -- Initialize API return status to success
96 x_return_status := FND_API.G_RET_STS_SUCCESS;
97
98 IF (l_debug = 'Y') THEN
99 okc_debug.log('400: Updating k article record', 2);
100 END IF;
101
102 IF p_mode='AMEND' THEN
103
104 OKC_K_ARTICLES_GRP.update_article(
105 p_api_version =>1,
106 p_init_msg_list => FND_API.G_FALSE,
107 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
108 p_validate_commit => FND_API.G_FALSE,
109 p_validation_string => NULL,
110 p_commit => FND_API.G_FALSE,
111 p_mode => p_mode,
112 x_return_status => x_return_status,
113 x_msg_count => x_msg_count,
114 x_msg_data => x_msg_data,
115 p_id => p_cat_id,
116 p_amendment_description => p_amendment_description,
117 p_print_text_yn =>p_print_text_yn,
118 p_object_version_number => NULL
119 );
120 --------------------------------------------
121 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
122 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
123 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
124 RAISE FND_API.G_EXC_ERROR ;
125 END IF;
126 --------------------------------------------
127
128 END IF;
129
130 -- Updating variable values
131
132 OPEN l_get_art_var_csr;
133 FETCH l_get_art_var_csr INTO l_ovn;
134 IF l_get_art_var_csr%FOUND THEN
135
136 IF (l_debug = 'Y') THEN
137 okc_debug.log('700: updating article variable record ',2);
138 END IF;
139
140 OKC_K_ART_VARIABLES_PVT.update_row(
141 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
142 x_return_status => x_return_status,
143 p_cat_id => p_cat_id,
144 p_variable_code => p_variable_code,
145 p_variable_value_id => p_variable_value_id,
146 p_variable_value => p_variable_value,
147 p_object_version_number=>l_ovn
148 );
149 --------------------------------------------
150 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
151 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
152 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
153 RAISE FND_API.G_EXC_ERROR ;
154 END IF;
155 --------------------------------------------
156 IF (l_debug = 'Y') THEN
157 okc_debug.log('800: updated article variable record ', 2);
158 END IF;
159 END IF;
160 CLOSE l_get_art_var_csr;
161
162 -- Standard check of p_commit
163 IF FND_API.To_Boolean( p_commit ) THEN
164 COMMIT WORK;
165 END IF;
166
167 -- Standard call to get message count and if count is 1, get message info.
168 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
169
170 IF (l_debug = 'Y') THEN
171 okc_debug.log('900: Leaving update_article_var_values', 2);
172 END IF;
173
174
175 EXCEPTION
176 WHEN FND_API.G_EXC_ERROR THEN
177 IF (l_debug = 'Y') THEN
178 okc_debug.log('300: Leaving update_article_var_values: OKC_API.G_EXCEPTION_ERROR Exception', 2);
179 END IF;
180
181 IF l_get_art_var_csr%ISOPEN THEN
182 CLOSE l_get_art_var_csr;
183 END IF;
184
185 ROLLBACK TO g_upd_article_var_values_GRP;
186 x_return_status := G_RET_STS_ERROR ;
187 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
188
189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
190 IF (l_debug = 'Y') THEN
191 okc_debug.log('400: Leavingupdate_article_var_values: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
192 END IF;
193
194 IF l_get_art_var_csr%ISOPEN THEN
195 CLOSE l_get_art_var_csr;
196 END IF;
197
198
199 ROLLBACK TO g_upd_article_var_values_GRP;
200 x_return_status := G_RET_STS_UNEXP_ERROR ;
201 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
202
203 WHEN OTHERS THEN
204 IF (l_debug = 'Y') THEN
205 okc_debug.log('500: Leaving update_article_var_values because of EXCEPTION: '||sqlerrm, 2);
206 END IF;
207
208 IF l_get_art_var_csr%ISOPEN THEN
209 CLOSE l_get_art_var_csr;
210 END IF;
211
212
213 ROLLBACK TO g_upd_article_var_values_GRP;
214 x_return_status := G_RET_STS_UNEXP_ERROR ;
215 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
216 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
217 END IF;
218
219 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
220 END update_article_var_values;
221
222
223 Procedure update_global_var_values(
224 p_api_version IN NUMBER,
225 p_init_msg_list IN VARCHAR2 ,
226 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
227 p_validation_string IN VARCHAR2,
228 p_commit IN VARCHAR2 := FND_API.G_FALSE,
229 p_mode IN VARCHAR2 :='NORMAL', -- Other value 'AMEND'
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233 p_doc_type IN VARCHAR2,
234 p_doc_id IN NUMBER,
235 p_variable_code IN VARCHAR2,
236 p_global_variable_value_id IN VARCHAR2,
237 p_global_variable_value IN VARCHAR2
238 ) IS
239 l_api_version CONSTANT NUMBER := 1;
240 l_api_name CONSTANT VARCHAR2(30) := 'g_update_global_var_values';
241 l_ovn NUMBER;
242
243 Cursor l_get_cat_id_csr IS
244 SELECT DISTINCT k.id
245 FROM okc_k_art_variables v,
246 okc_k_articles_b k
247 WHERE k.id = v.cat_id
248 AND NVL(v.override_global_yn,'N') <> 'Y'
249 AND v.variable_code = p_variable_code
250 AND k.document_type = p_doc_type
251 AND k.document_id = p_doc_id;
252
253 BEGIN
254
255 IF (l_debug = 'Y') THEN
256 okc_debug.log('100: Entered update_global_var_values', 2);
257 END IF;
258
259 -- Standard Start of API savepoint
260 SAVEPOINT g_update_global_var_values_GRP;
261 -- Standard call to check for call compatibility.
262 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
264 END IF;
265 -- Initialize message list if p_init_msg_list is set to TRUE.
266 IF FND_API.to_Boolean( p_init_msg_list ) THEN
267 FND_MSG_PUB.initialize;
268 END IF;
269
270 IF FND_API.To_Boolean( p_validate_commit ) THEN
271
272 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
273 p_api_version => l_api_version,
274 p_init_msg_list => FND_API.G_FALSE,
275 p_doc_type => p_doc_type,
276 p_doc_id => p_doc_id,
277 p_validation_string => p_validation_string,
278 x_return_status => x_return_status,
279 x_msg_data => x_msg_data,
280 x_msg_count => x_msg_count) ) THEN
281
282 IF (l_debug = 'Y') THEN
283 okc_debug.log('110: Issue with document header Record.Cannot commit', 2);
284 END IF;
285 RAISE FND_API.G_EXC_ERROR ;
286 END IF;
287 END IF;
288
289
290 -- Initialize API return status to success
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292
293 -- Updating variable values
294
295 UPDATE okc_k_art_variables
296 SET variable_value = p_global_variable_value,
297 variable_value_id = p_global_variable_value_id,
298 global_variable_value = p_global_variable_value,
299 global_variable_value_id = p_global_variable_value_id,
300 object_version_number = object_version_number + 1
301 WHERE variable_code = p_variable_code
302 AND NVL(override_global_yn,'N') <> 'Y'
303 AND cat_id IN
304 (SELECT id FROM okc_k_articles_b WHERE document_type = p_doc_type AND document_id = p_doc_id);
305
306 -- Update the global value for all records with override_global_yn = Y
307
308 UPDATE okc_k_art_variables
309 SET global_variable_value = p_global_variable_value,
310 global_variable_value_id = p_global_variable_value_id,
311 object_version_number = object_version_number + 1
312 WHERE variable_code = p_variable_code
313 AND NVL(override_global_yn,'N') ='Y'
314 AND cat_id IN
315 (SELECT id FROM okc_k_articles_b WHERE document_type = p_doc_type AND document_id = p_doc_id);
316
317
318 IF (l_debug = 'Y') THEN
319 okc_debug.log('400: Updating k article record', 2);
320 END IF;
321
322 -- for Mode = AMEND mark articles as amended
323 IF p_mode='AMEND' THEN
324
325 FOR l_cat_rec IN l_get_cat_id_csr LOOP
326
327 OKC_K_ARTICLES_GRP.update_article(
328 p_api_version =>1,
329 p_init_msg_list => FND_API.G_FALSE,
330 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
331 p_validate_commit => FND_API.G_FALSE,
332 p_validation_string => NULL,
333 p_commit => FND_API.G_FALSE,
334 p_mode => p_mode,
335 x_return_status => x_return_status,
336 x_msg_count => x_msg_count,
337 x_msg_data => x_msg_data,
338 p_id => l_cat_rec.id,
339 p_amendment_description => NULL,
340 p_print_text_yn =>NULL,
341 p_object_version_number => NULL
342 );
343
344 --------------------------------------------
345 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
346 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
347 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
348 RAISE FND_API.G_EXC_ERROR ;
349 END IF;
350 --------------------------------------------
351
352 END LOOP;
353 END IF; -- mode = AMEND
354
355 -- Standard check of p_commit
356 IF FND_API.To_Boolean( p_commit ) THEN
357 COMMIT WORK;
358 END IF;
359
360 -- Standard call to get message count and if count is 1, get message info.
361 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
362
363 IF (l_debug = 'Y') THEN
364 okc_debug.log('900: Leaving update_global_var_values', 2);
365 END IF;
366
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 IF (l_debug = 'Y') THEN
370 okc_debug.log('300: Leaving update_article_var_values: OKC_API.G_EXCEPTION_ERROR Exception', 2);
371 END IF;
372
373 IF l_get_cat_id_csr%ISOPEN THEN
374 CLOSE l_get_cat_id_csr;
375 END IF;
376
377 ROLLBACK TO g_upd_article_var_values_GRP;
378 x_return_status := G_RET_STS_ERROR ;
379 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
380
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382 IF (l_debug = 'Y') THEN
383 okc_debug.log('400: Leaving update_article_var_values: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2
384 );
385 END IF;
386
387 IF l_get_cat_id_csr%ISOPEN THEN
388 CLOSE l_get_cat_id_csr;
389 END IF;
390
391
392 ROLLBACK TO g_upd_article_var_values_GRP;
393 x_return_status := G_RET_STS_UNEXP_ERROR ;
394 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
395
396 WHEN OTHERS THEN
397 IF (l_debug = 'Y') THEN
398 okc_debug.log('500: Leaving update_article_var_values because of EXCEPTION: '||sqlerrm, 2);
399 END IF;
400
401 IF l_get_cat_id_csr%ISOPEN THEN
402 CLOSE l_get_cat_id_csr;
403 END IF;
404
405
406 ROLLBACK TO g_upd_article_var_values_GRP;
407 x_return_status := G_RET_STS_UNEXP_ERROR ;
408 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
409 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
410 END IF;
411
412 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
413 END update_global_var_values;
414
415
416 Procedure update_local_var_values(
417 p_api_version IN NUMBER,
418 p_init_msg_list IN VARCHAR2 ,
419 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
420 p_validation_string IN VARCHAR2,
421 p_commit IN VARCHAR2 := FND_API.G_FALSE,
422 p_mode IN VARCHAR2 :='NORMAL', -- Other value 'AMEND'
423 x_return_status OUT NOCOPY VARCHAR2,
424 x_msg_count OUT NOCOPY NUMBER,
425 x_msg_data OUT NOCOPY VARCHAR2,
426 p_doc_type IN VARCHAR2,
427 p_doc_id IN NUMBER,
428 p_cat_id IN NUMBER,
429 p_amendment_description IN VARCHAR2 := NULL,
430 p_print_text_yn IN VARCHAR2 := NULL,
431 p_variable_code IN VARCHAR2,
432 p_variable_value_id IN VARCHAR2,
433 p_variable_value IN VARCHAR2,
434 p_override_global_yn IN VARCHAR2) IS
435
436 l_api_version CONSTANT NUMBER := 1;
437 l_api_name CONSTANT VARCHAR2(30) := 'g_update_local_var_values';
438
439 BEGIN
440
441 IF (l_debug = 'Y') THEN
442 okc_debug.log('100: Entered update_local_var_values', 2);
443 END IF;
444
445 -- Standard Start of API savepoint
446 SAVEPOINT g_update_local_var_values_GRP;
447 -- Standard call to check for call compatibility.
448 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451 -- Initialize message list if p_init_msg_list is set to TRUE.
452 IF FND_API.to_Boolean( p_init_msg_list ) THEN
453 FND_MSG_PUB.initialize;
454 END IF;
455
456 IF FND_API.To_Boolean( p_validate_commit ) THEN
457
458 IF NOT FND_API.To_Boolean(OKC_TERMS_UTIL_GRP.ok_to_commit (
459 p_api_version => l_api_version,
460 p_init_msg_list => FND_API.G_FALSE,
461 p_doc_type => p_doc_type,
462 p_doc_id => p_doc_id,
463 p_validation_string => p_validation_string,
464 x_return_status => x_return_status,
465 x_msg_data => x_msg_data,
466 x_msg_count => x_msg_count) ) THEN
467
468 IF (l_debug = 'Y') THEN
469 okc_debug.log('110: Issue with document header Record.Cannot commit', 2);
470 END IF;
471 RAISE FND_API.G_EXC_ERROR ;
472 END IF;
473 END IF;
474
475
476 -- Initialize API return status to success
477 x_return_status := FND_API.G_RET_STS_SUCCESS;
478
479 IF (l_debug = 'Y') THEN
480 okc_debug.log('400: Updating k article record', 2);
481 END IF;
482
483 -- for Mode = AMEND mark articles as amended
484 IF p_mode='AMEND' THEN
485
486 OKC_K_ARTICLES_GRP.update_article(
487 p_api_version =>1,
488 p_init_msg_list => FND_API.G_FALSE,
489 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
490 p_validate_commit => FND_API.G_FALSE,
491 p_validation_string => NULL,
492 p_commit => FND_API.G_FALSE,
493 p_mode => p_mode,
494 x_return_status => x_return_status,
495 x_msg_count => x_msg_count,
496 x_msg_data => x_msg_data,
497 p_id => p_cat_id,
498 p_amendment_description => NULL,
499 p_print_text_yn =>NULL,
500 p_object_version_number => NULL
501 );
502 --------------------------------------------
503 IF (x_return_status = G_RET_STS_UNEXP_ERROR) THEN
504 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
505 ELSIF (x_return_status = G_RET_STS_ERROR) THEN
506 RAISE FND_API.G_EXC_ERROR ;
507 END IF;
508 --------------------------------------------
509
510 END IF; -- mode = AMEND
511
512
513
514 -- Updating variable values
515
516 IF NVL(p_override_global_yn,'N') = 'N' THEN
517 -- override local with global values
518 UPDATE okc_k_art_variables
519 SET variable_value = NVL(global_variable_value,p_variable_value),
520 variable_value_id = NVL(global_variable_value_id,p_variable_value_id),
521 override_global_yn = 'N',
522 object_version_number = object_version_number + 1
523 WHERE variable_code = p_variable_code
524 AND cat_id = p_cat_id ;
525
526 ELSE
527 -- override global with local values
528 UPDATE okc_k_art_variables
529 SET variable_value = p_variable_value,
530 variable_value_id = p_variable_value_id,
531 override_global_yn = 'Y',
532 object_version_number = object_version_number + 1
533 WHERE variable_code = p_variable_code
534 AND cat_id = p_cat_id ;
535 END IF;
536
537
538 -- Standard check of p_commit
539 IF FND_API.To_Boolean( p_commit ) THEN
540 COMMIT WORK;
541 END IF;
542
543 -- Standard call to get message count and if count is 1, get message info.
544 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
545
546 IF (l_debug = 'Y') THEN
547 okc_debug.log('900: Leaving update_local_var_values', 2);
548 END IF;
549
550 EXCEPTION
551 WHEN FND_API.G_EXC_ERROR THEN
552 IF (l_debug = 'Y') THEN
553 okc_debug.log('300: Leaving update_article_var_values: OKC_API.G_EXCEPTION_ERROR Exception', 2);
554 END IF;
555
556 ROLLBACK TO g_upd_article_var_values_GRP;
557 x_return_status := G_RET_STS_ERROR ;
558 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
559
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
561 IF (l_debug = 'Y') THEN
562 okc_debug.log('400: Leaving update_article_var_values: OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2
563 );
564 END IF;
565
566
567 ROLLBACK TO g_upd_article_var_values_GRP;
568 x_return_status := G_RET_STS_UNEXP_ERROR ;
569 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
570
571 WHEN OTHERS THEN
572 IF (l_debug = 'Y') THEN
573 okc_debug.log('500: Leaving update_article_var_values because of EXCEPTION: '||sqlerrm, 2);
574 END IF;
575
576
577 ROLLBACK TO g_upd_article_var_values_GRP;
578 x_return_status := G_RET_STS_UNEXP_ERROR ;
579 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
580 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
581 END IF;
582
583 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
584 END update_local_var_values;
585
586 Procedure update_response_var_values(
587 p_api_version IN NUMBER,
588 p_init_msg_list IN VARCHAR2 ,
589 p_validate_commit IN VARCHAR2 := FND_API.G_FALSE,
590 p_validation_string IN VARCHAR2,
591 p_commit IN VARCHAR2 := FND_API.G_FALSE,
592 p_mode IN VARCHAR2 :='NORMAL', -- Other value 'AMEND'
593 x_return_status OUT NOCOPY VARCHAR2,
594 x_msg_count OUT NOCOPY NUMBER,
595 x_msg_data OUT NOCOPY VARCHAR2,
596 p_doc_type IN VARCHAR2,
597 p_doc_id IN NUMBER,
598 p_old_cat_id IN NUMBER,
599 p_amendment_description IN VARCHAR2 := NULL,
600 p_print_text_yn IN VARCHAR2 := NULL,
601 p_variable_code IN VARCHAR2,
602 p_variable_value_id IN VARCHAR2,
603 p_variable_value IN VARCHAR2,
604 p_override_global_yn IN VARCHAR2) IS
605
606 l_api_version CONSTANT NUMBER := 1;
607 l_api_name CONSTANT VARCHAR2(30) := 'g_update_response_var_values';
608 l_cat_id NUMBER;
609
610 CURSOR csr_new_cat_id IS
611 SELECT id
612 FROM okc_k_articles_b
613 WHERE orig_system_reference_code = 'COPY'
614 AND orig_system_reference_id1 = p_old_cat_id
615 AND document_type = p_doc_type
616 AND document_id = p_doc_id;
617
618
619
620 BEGIN
621
622 IF (l_debug = 'Y') THEN
623 okc_debug.log('100: Entered update_response_var_values', 2);
624 END IF;
625
626 OPEN csr_new_cat_id;
627 FETCH csr_new_cat_id INTO l_cat_id;
628 CLOSE csr_new_cat_id;
629
630 IF (l_debug = 'Y') THEN
631 okc_debug.log('200: l_cat_id : '||l_cat_id, 2);
632 END IF;
633
634 -- now call update_local_var_values
635 update_local_var_values
636 (
637 p_api_version => p_api_version,
638 p_init_msg_list => p_init_msg_list,
639 p_validate_commit => p_validate_commit,
640 p_validation_string => p_validation_string,
641 p_commit => p_commit,
642 p_mode => p_mode,
643 x_return_status => x_return_status,
644 x_msg_count => x_msg_count,
645 x_msg_data => x_msg_data,
646 p_doc_type => p_doc_type,
647 p_doc_id => p_doc_id,
648 p_cat_id => l_cat_id,
649 p_amendment_description => p_amendment_description,
650 p_print_text_yn => p_print_text_yn,
651 p_variable_code => p_variable_code,
652 p_variable_value_id => p_variable_value_id,
653 p_variable_value => p_variable_value,
654 p_override_global_yn => p_override_global_yn
655 );
656
657 IF (l_debug = 'Y') THEN
658 okc_debug.log('900: Leaving update_response_var_values', 2);
659 END IF;
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 IF (l_debug = 'Y') THEN
664 okc_debug.log('500: Leaving update_response_var_values because of EXCEPTION: '||sqlerrm, 2);
665 END IF;
666 x_return_status := G_RET_STS_UNEXP_ERROR ;
667 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
668 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
669 END IF;
670 FND_MSG_PUB.Count_And_Get(p_encoded=>'F', p_count => x_msg_count, p_data => x_msg_data );
671 END update_response_var_values;
672
673
674
675 END OKC_K_ART_VARIABLES_GRP;