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