[Home] [Help]
PACKAGE BODY: APPS.OKC_XPRT_QUESTION_PVT
Source
1 PACKAGE BODY okc_xprt_question_pvt AS
2 /*$Header: OKCVXIQB.pls 120.1.12020000.2 2012/07/16 16:20:05 harchand ship $*/
3
4 l_debug VARCHAR2 (1)
5 := NVL (fnd_profile.VALUE ('AFLOG_ENABLED'), 'N');
6 ---------------------------------------------------------------------------
7 -- GLOBAL MESSAGE CONSTANTS
8 ---------------------------------------------------------------------------
9 g_fnd_app CONSTANT VARCHAR2 (200) := okc_api.g_fnd_app;
10 g_invalid_value CONSTANT VARCHAR2 (200) := okc_api.g_invalid_value;
11 g_col_name_token CONSTANT VARCHAR2 (200)
12 := okc_api.g_col_name_token;
13 ---------------------------------------------------------------------------
14 -- GLOBAL VARIABLES
15 ---------------------------------------------------------------------------
16 g_pkg_name CONSTANT VARCHAR2 (200) := 'OKC_XPRT_QUESTION_PVT';
17 g_app_name CONSTANT VARCHAR2 (3) := okc_api.g_app_name;
18 ------------------------------------------------------------------------------
19 -- GLOBAL CONSTANTS
20 ------------------------------------------------------------------------------
21 g_false CONSTANT VARCHAR2 (1) := fnd_api.g_false;
22 g_true CONSTANT VARCHAR2 (1) := fnd_api.g_true;
23 g_ret_sts_success CONSTANT VARCHAR2 (1) := fnd_api.g_ret_sts_success;
24 g_ret_sts_error CONSTANT VARCHAR2 (1) := fnd_api.g_ret_sts_error;
25 g_ret_sts_unexp_error CONSTANT VARCHAR2 (1)
26 := fnd_api.g_ret_sts_unexp_error;
27 g_unexpected_error CONSTANT VARCHAR2 (200) := 'OKC_UNEXPECTED_ERROR';
28 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'ERROR_MESSAGE';
29 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'ERROR_CODE';
30 l_OKC_I_NOT_NULL VARCHAR2 (30) := 'OKC_I_NOT_NULL';
31 --l_okc_xprt_imp_qc_not_null VARCHAR2 (30)
32 -- := 'OKC_XPRT_IMP_QC_NOT_NULL';
33 l_okc_i_invalid_value VARCHAR2 (30)
34 := 'OKC_I_INVALID_VALUE';
35 l_field VARCHAR2 (30) := 'FIELD';
36 g_exc_error EXCEPTION;
37
38 PROCEDURE validate_question_name (
39 p_question_name IN VARCHAR2,
40 --p_question_intent IN VARCHAR2,
41 p_question_type IN VARCHAR2,
42 p_lang IN VARCHAR2,
43 p_question_id IN NUMBER DEFAULT NULL,
44 x_return_status OUT NOCOPY VARCHAR2
45 )
46 IS
47 CURSOR cur_question_exists
48 IS
49 SELECT 'X'
50 FROM okc_xprt_questions_b b, okc_xprt_questions_tl t
51 WHERE b.question_id = t.question_id
52 AND b.question_type = t.question_type
53 -- AND b.question_intent = p_question_intent
54 AND t.question_type = p_question_type
55 AND Upper(t.question_name) = Upper(p_question_name)
56 AND t.question_id <> Nvl(p_question_id,-1)
57 AND t.LANGUAGE = p_lang;
58
59 l_validate_flag VARCHAR2 (1);
60 BEGIN
61 OPEN cur_question_exists;
62
63 FETCH cur_question_exists
64 INTO l_validate_flag;
65
66 CLOSE cur_question_exists;
67
68 IF NVL (l_validate_flag, 'Y') = 'X'
69 THEN
70 x_return_status := g_ret_sts_error;
71
72 IF p_question_type = 'Q'
73 THEN
74 x_return_status := g_ret_sts_error;
75 okc_api.set_message (p_app_name => g_app_name,
76 p_msg_name => 'OKC_XPRT_DUP_QST_NAME'
77 );
78 RETURN;
79 ELSE
80 x_return_status := g_ret_sts_error;
81 okc_api.set_message (p_app_name => g_app_name,
82 p_msg_name => 'OKC_XPRT_DUP_CON_NAME'
83 );
84 RETURN;
85 END IF;
86 END IF;
87
88 x_return_status := g_ret_sts_success;
89 EXCEPTION
90 WHEN OTHERS
91 THEN
92 x_return_status := g_ret_sts_unexp_error;
93 okc_api.set_message (p_app_name => g_app_name,
94 p_msg_name => g_unexpected_error,
95 p_token1 => g_sqlcode_token,
96 p_token1_value => SQLCODE,
97 p_token2 => g_sqlerrm_token,
98 p_token2_value => SQLERRM
99 );
100 END validate_question_name;
101
102 PROCEDURE isQuestionPromptExists
103 (p_question_prompt IN VARCHAR2,
104 p_question_intent IN VARCHAR2,
105 p_lang IN VARCHAR2,
106 p_question_id IN NUMBER DEFAULT NULL,
107 x_return_status OUT NOCOPY VARCHAR2
108 )
109 IS
110 CURSOR cur_question_exists
111 IS
112 SELECT 'X'
113 FROM okc_xprt_questions_b b, okc_xprt_questions_tl t
114 WHERE b.question_id = t.question_id
115 AND b.question_type = t.question_type
116 AND b.question_intent = p_question_intent
117 AND t.question_type = 'Q'
118 AND Upper(t.prompt) = Upper(p_question_prompt)
119 AND t.question_id <> Nvl(p_question_id,-1)
120 AND t.LANGUAGE = p_lang;
121
122 l_validate_flag VARCHAR2 (1);
123 BEGIN
124 OPEN cur_question_exists;
125
126 FETCH cur_question_exists
127 INTO l_validate_flag;
128
129 CLOSE cur_question_exists;
130
131 IF NVL (l_validate_flag, 'Y') = 'X'
132 THEN
133 x_return_status := g_ret_sts_error;
134
135 okc_api.set_message (p_app_name => g_app_name,
136 p_msg_name => 'OKC_XPRT_DUP_QST_PROMPT'
137 );
138 RETURN;
139 END IF;
140
141 x_return_status := g_ret_sts_success;
142 EXCEPTION
143 WHEN OTHERS
144 THEN
145 x_return_status := g_ret_sts_unexp_error;
146 okc_api.set_message (p_app_name => g_app_name,
147 p_msg_name => g_unexpected_error,
148 p_token1 => g_sqlcode_token,
149 p_token1_value => SQLCODE,
150 p_token2 => g_sqlerrm_token,
151 p_token2_value => SQLERRM
152 );
153 END isQuestionPromptExists;
154
155
156 PROCEDURE default_row (
157 p_xprt_question_rec IN OUT NOCOPY xprt_qn_const_rec_type,
158 x_returns_status OUT NOCOPY VARCHAR2
159 )
160 IS
161 BEGIN
162
163 IF p_xprt_question_rec.QN_CONST_id = okc_api.g_miss_num
164 THEN
165 p_xprt_question_rec.QN_CONST_id := NULL;
166 END IF;
167
168
169 IF p_xprt_question_rec.QN_CONST_type = okc_api.g_miss_char
170 THEN
171 p_xprt_question_rec.QN_CONST_type := NULL;
172 END IF;
173
174 IF p_xprt_question_rec.QN_CONST_intent = okc_api.g_miss_char
175 THEN
176 p_xprt_question_rec.QN_CONST_intent := NULL;
177 END IF;
178
179 IF (p_xprt_question_rec.disabled_flag = okc_api.g_miss_char)
180 THEN
181 p_xprt_question_rec.disabled_flag := 'N';
182 END IF;
183
184
185 IF p_xprt_question_rec.question_datatype = okc_api.g_miss_char
186 THEN
187 p_xprt_question_rec.question_datatype := NULL;
188 END IF;
189
190 IF p_xprt_question_rec.QN_CONST_type = 'C' THEN
191 p_xprt_question_rec.question_datatype := 'N';
192 p_xprt_question_rec.value_set_name := NULL;
193 p_xprt_question_rec.prompt := NULL;
194 END IF;
195
196 -- When question_datatype is Yes or No ('B') then default 'value set' to OKC_XPRT_YES_NO
197 IF p_xprt_question_rec.question_datatype = 'B'
198 THEN
199 p_xprt_question_rec.value_set_name := 'OKC_XPRT_YES_NO';
200 -- When question_datatype is Numeric ('N') then default 'value set' to null
201 ELSIF p_xprt_question_rec.question_datatype = 'N'
202 THEN
203 p_xprt_question_rec.value_set_name := NULL;
204 END IF;
205
206 IF p_xprt_question_rec.value_set_name = okc_api.g_miss_char THEN
207 p_xprt_question_rec.value_set_name := NULL;
208 END IF;
209
210
211 IF p_xprt_question_rec.default_value = okc_api.g_miss_num
212 OR p_xprt_question_rec.QN_CONST_type = 'Q'
213 THEN
214 p_xprt_question_rec.default_value := NULL;
215 END IF;
216
217 IF p_xprt_question_rec.minimum_value = okc_api.g_miss_num
218
219 THEN
220 p_xprt_question_rec.minimum_value := NULL;
221 END IF;
222
223 IF p_xprt_question_rec.maximum_value = okc_api.g_miss_num
224
225 THEN
226 p_xprt_question_rec.maximum_value := NULL;
227 END IF;
228
229
230 IF (p_xprt_question_rec.question_sync_flag = okc_api.g_miss_char)
231 THEN
232 IF (p_xprt_question_rec.qn_const_type = 'Q')
233 THEN
234 p_xprt_question_rec.question_sync_flag := 'Y';
235 ELSE
236 p_xprt_question_rec.question_sync_flag := 'N';
237 END IF;
238 END IF;
239
240 IF (p_xprt_question_rec.object_version_number = okc_api.g_miss_num)
241 THEN
242 p_xprt_question_rec.object_version_number := 1;
243 END IF;
244
245 -- Default who columns
246 IF p_xprt_question_rec.created_by = okc_api.g_miss_num
247 THEN
248 p_xprt_question_rec.created_by := fnd_global.user_id;
249 END IF;
250
251 IF p_xprt_question_rec.creation_date = okc_api.g_miss_date
252 THEN
253 p_xprt_question_rec.creation_date := SYSDATE;
254 END IF;
255
256 -- Default Who Coulmns
257 p_xprt_question_rec.last_update_date := SYSDATE;
258 p_xprt_question_rec.last_updated_by := fnd_global.user_id;
259 p_xprt_question_rec.last_update_login := fnd_global.login_id;
260
261
262 IF fnd_global.conc_request_id = -1
263 THEN
264 p_xprt_question_rec.request_id := NULL;
265 ELSE
266 p_xprt_question_rec.request_id := fnd_global.conc_request_id;
267 END IF;
268
269 IF fnd_global.conc_program_id = -1
270 THEN
271 p_xprt_question_rec.program_id := NULL;
272 p_xprt_question_rec.program_update_date := NULL;
273 ELSE
274 p_xprt_question_rec.program_id := fnd_global.conc_program_id;
275 -- Directly initializing it to the sysdate
276 p_xprt_question_rec.program_update_date := SYSDATE;
277 END IF;
278
279 IF fnd_global.prog_appl_id = -1
280 THEN
281 p_xprt_question_rec.program_application_id := NULL;
282 ELSE
283 p_xprt_question_rec.program_application_id :=
284 fnd_global.prog_appl_id;
285 END IF;
286
287 IF (p_xprt_question_rec.source_lang = okc_api.g_miss_char)
288 THEN
289 p_xprt_question_rec.source_lang := USERENV ('LANG');
290 END IF;
291
292 IF p_xprt_question_rec.QN_CONST_name = okc_api.g_miss_char
293 THEN
294 p_xprt_question_rec.QN_CONST_name := NULL;
295 END IF;
296
297 IF p_xprt_question_rec.lang = okc_api.g_miss_char
298 THEN
299 p_xprt_question_rec.lang := NULL;
300 END IF;
301
302 IF p_xprt_question_rec.source_lang = okc_api.g_miss_char
303 THEN
304 p_xprt_question_rec.source_lang := NULL;
305 END IF;
306
307 IF p_xprt_question_rec.description = okc_api.g_miss_char
308 THEN
309 p_xprt_question_rec.description := NULL;
310 END IF;
311
312 IF p_xprt_question_rec.prompt = okc_api.g_miss_char
313 THEN
314 p_xprt_question_rec.prompt := NULL;
315 END IF;
316
317
318 x_returns_status := g_ret_sts_success;
319 EXCEPTION
320 WHEN OTHERS
321 THEN
322 x_returns_status := g_ret_sts_unexp_error;
323 okc_api.set_message (p_app_name => g_app_name,
324 p_msg_name => g_unexpected_error,
325 p_token1 => g_sqlcode_token,
326 p_token1_value => SQLCODE,
327 p_token2 => g_sqlerrm_token,
328 p_token2_value => SQLERRM
329 );
330 END default_row;
331
332 PROCEDURE validate_row (
333 p_xprt_question_rec IN xprt_qn_const_rec_type,
334 p_val_qn_name IN VARCHAR2 DEFAULT 'Y',
335 x_return_status OUT NOCOPY VARCHAR2
336 )
337 IS
338 x_error_count NUMBER := 0;
339 l_validate_flag VARCHAR2 (1);
340 l_qn_type_lookup_type VARCHAR2 (30) := 'OKC_XPRT_QUESTION_TYPE';
341 l_qn_datatype_lookup_type VARCHAR2 (30)
342 := 'OKC_XPRT_QUESTION_DATATYPE';
343 l_return_status VARCHAR2 (1);
344
345 --x_error_message VARCHAR2(2000);
346 CURSOR cur_val_question_lookup (
347 p_lookup_type IN VARCHAR2,
348 p_lookup_code IN VARCHAR2
349 )
350 IS
351 SELECT 'X'
352 FROM fnd_lookup_values
353 WHERE lookup_type = p_lookup_type
354 --AND langauge = UserEnv('Lang') P_lang
355 AND lookup_code = p_lookup_code
356 AND enabled_flag = 'Y'
357 AND SYSDATE >= start_date_active
358 AND (end_date_active IS NULL OR SYSDATE <= end_date_active);
359
360 CURSOR cur_validate_valueset (p_value_set_name IN VARCHAR2)
361 IS
362 SELECT 'X'
363 FROM fnd_flex_value_sets
364 WHERE flex_value_set_name = p_value_set_name;
365 BEGIN
366 x_return_status := g_ret_sts_success;
367
368 -- Assuming Defaulting has been done prior to the validation
369 -- VALIDATION FOR FIELDS THAT ARE COMMON FOR BOTH QUESTIONS/CONSTANTS
370 -- Question Type/Question Intent/Disabled Flag/question_datatype/question_sync_flag/object_version_number
371 -- Question Name/Langauge/
372
373 /**
374 * This indicates the question type. FK to FND lookup OKC_XPRT_QUESTION_TYPE.
375 * Possible values are Q and C. Q: Question, C: Constants
376 **/
377 IF p_xprt_question_rec.QN_CONST_type IS NULL
378 THEN
379 x_error_count := x_error_count + 1;
380 okc_api.set_message (p_app_name => g_app_name,
381 p_msg_name => l_okc_i_not_null,
382 p_token1 => l_field,
383 p_token1_value => 'QUESTION_TYPE'
384 );
385 ELSE
386 OPEN cur_val_question_lookup (l_qn_type_lookup_type,
387 p_xprt_question_rec.QN_CONST_type
388 );
389
390 FETCH cur_val_question_lookup
391 INTO l_validate_flag;
392
393 CLOSE cur_val_question_lookup;
394
395 IF NVL (l_validate_flag, 'Y') <> 'X'
396 THEN
397 x_error_count := x_error_count + 1;
398 okc_api.set_message (p_app_name => g_app_name,
399 p_msg_name => l_okc_i_invalid_value,
400 p_token1 => l_field,
401 p_token1_value => 'QUESTION_TYPE'
402 );
403 END IF;
404 END IF;
405
406 /**
407 * Intent of Question or Constant. B: Buy, S: Sell.
408 **/
409 IF p_xprt_question_rec.QN_CONST_intent IS NULL
410 THEN
411 x_error_count := x_error_count + 1;
412 okc_api.set_message (p_app_name => g_app_name,
413 p_msg_name => l_OKC_I_NOT_NULL,
414 p_token1 => l_field,
415 p_token1_value => 'INTENT'
416 );
417 ELSE
418 IF p_xprt_question_rec.QN_CONST_intent NOT IN ('B', 'S')
419 THEN
420 x_error_count := x_error_count + 1;
421 okc_api.set_message (p_app_name => g_app_name,
422 p_msg_name => l_okc_i_invalid_value,
423 p_token1 => l_field,
424 p_token1_value => 'INTENT'
425 );
426 END IF;
427 END IF;
428
429 IF p_xprt_question_rec.disabled_flag IS NULL
430 THEN
431 x_error_count := x_error_count + 1;
432 okc_api.set_message (p_app_name => g_app_name,
433 p_msg_name => l_okc_i_not_null,
434 p_token1 => l_field,
435 p_token1_value => 'DISABLED_FLAG'
436 );
437 END IF;
438
439 IF p_xprt_question_rec.question_sync_flag IS NULL
440 THEN
441 x_error_count := x_error_count + 1;
442 okc_api.set_message (p_app_name => g_app_name,
443 p_msg_name => l_okc_i_not_null,
444 p_token1 => l_field,
445 p_token1_value => 'QUESTION_SYNC_FLAG'
446 );
447 END IF;
448
449 IF p_xprt_question_rec.object_version_number IS NULL
450 THEN
451 x_error_count := x_error_count + 1;
452 okc_api.set_message (p_app_name => g_app_name,
453 p_msg_name => l_okc_i_not_null,
454 p_token1 => l_field,
455 p_token1_value => 'OBJECT_VERSION_NUMBER'
456 );
457 END IF;
458
459 IF p_xprt_question_rec.question_datatype IS NULL
460 THEN
461 x_error_count := x_error_count + 1;
462 okc_api.set_message (p_app_name => g_app_name,
463 p_msg_name => l_okc_i_not_null,
464 p_token1 => l_field,
465 p_token1_value => 'RESPONSE_TYPE'
466 );
467 END IF;
468
469 IF p_xprt_question_rec.QN_CONST_name IS NULL
470 THEN
471 x_error_count := x_error_count + 1;
472 okc_api.set_message (p_app_name => g_app_name,
473 p_msg_name => l_OKC_I_NOT_NULL,
474 p_token1 => l_field,
475 p_token1_value => 'NAME'
476 );
477 END IF;
478
479 IF p_xprt_question_rec.source_lang IS NULL
480 THEN
481 x_error_count := x_error_count + 1;
482 okc_api.set_message (p_app_name => g_app_name,
483 p_msg_name => l_okc_i_not_null,
484 p_token1 => l_field,
485 p_token1_value => 'SOURCE_LANG'
486 );
487 END IF;
488
489 -- A Question/Constant name must be unique
490 IF p_xprt_question_rec.QN_CONST_name IS NOT NULL
491 --AND p_xprt_question_rec.question_intent IS NOT NULL
492 AND p_xprt_question_rec.QN_CONST_type IS NOT NULL
493 AND p_xprt_question_rec.source_lang IS NOT NULL
494 AND p_val_qn_name = 'Y'
495 THEN
496 validate_question_name
497 (p_question_name => p_xprt_question_rec.QN_CONST_name,
498 --p_question_intent => p_xprt_question_rec.question_intent,
499 p_question_type => p_xprt_question_rec.QN_CONST_type,
500 p_lang => p_xprt_question_rec.source_lang,
501 p_question_id => p_xprt_question_rec.QN_CONST_id,
502 x_return_status => l_return_status
503 );
504
505 IF (l_return_status <> g_ret_sts_success)
506 THEN
507 x_error_count := x_error_count + 1;
508 END IF;
509 END IF;
510
511 -- Question Related Validations
512 IF (p_xprt_question_rec.QN_CONST_type = 'Q')
513 THEN
514 -- Check Question Prompt
515 IF p_xprt_question_rec.prompt IS NULL
516 THEN
517 x_error_count := x_error_count + 1;
518 okc_api.set_message (p_app_name => g_app_name,
519 p_msg_name => l_okc_i_not_null,
520 p_token1 => l_field,
521 p_token1_value => 'QUESTION_PROMPT'
522 );
523 ELSE
524 l_return_status := NULL;
525 IF p_xprt_question_rec.prompt IS NOT NULL
526 AND p_xprt_question_rec.QN_CONST_intent IS NOT NULL
527 AND p_xprt_question_rec.source_lang IS NOT NULL
528 THEN
529
530 isQuestionPromptExists(p_question_prompt => p_xprt_question_rec.prompt,
531 p_question_intent => p_xprt_question_rec.QN_CONST_intent,
532 p_lang => p_xprt_question_rec.source_lang,
533 p_question_id => p_xprt_question_rec.QN_CONST_id,
534 x_return_status => l_return_status );
535
536 IF (l_return_status <> g_ret_sts_success)
537 THEN
538 x_error_count := x_error_count + 1;
539 END IF;
540
541 END IF;
542 END IF;
543
544 l_validate_flag := NULL;
545 -- Check Question Response Type
546 IF p_xprt_question_rec.question_datatype IS NOT NULL
547 THEN
548 OPEN cur_val_question_lookup
549 (l_qn_datatype_lookup_type,
550 p_xprt_question_rec.question_datatype
551 );
552
553 FETCH cur_val_question_lookup
554 INTO l_validate_flag;
555
556 CLOSE cur_val_question_lookup;
557
558 IF NVL (l_validate_flag, 'Y') <> 'X'
559 THEN
560 x_error_count := x_error_count + 1;
561 okc_api.set_message (p_app_name => g_app_name,
562 p_msg_name => l_okc_i_invalid_value,
563 p_token1 => l_field,
564 p_token1_value => 'RESPONSE_TYPE'
565 );
566 -- IF Response Type is List of Values validate Valueset.
567 ELSIF p_xprt_question_rec.question_datatype = 'L'
568 THEN
569 IF p_xprt_question_rec.value_set_name IS NULL
570 THEN
571 x_error_count := x_error_count + 1;
572 okc_api.set_message (p_app_name => g_app_name,
573 p_msg_name => l_okc_i_not_null,
574 p_token1 => l_field,
575 p_token1_value => 'VALUE_SET_NAME'
576 );
577 ELSE
578 l_validate_flag := NULL;
579 OPEN cur_validate_valueset
580 (p_xprt_question_rec.value_set_name);
581
582 FETCH cur_validate_valueset
583 INTO l_validate_flag;
584
585 CLOSE cur_validate_valueset;
586
587 IF NVL (l_validate_flag, 'Y') <> 'X'
588 THEN
589 x_error_count := x_error_count + 1;
590 okc_api.set_message
591 (p_app_name => g_app_name,
592 p_msg_name => l_okc_i_invalid_value,
593 p_token1 => l_field,
594 p_token1_value => 'VALUE_SET_NAME'
595 );
596 END IF;
597 END IF;
598 END IF; -- Question Data Type is valid
599 END IF; -- Check Question Response Type
600 -- Constants Related Validations
601 ELSIF (p_xprt_question_rec.QN_CONST_type = 'C')
602 THEN
603 IF p_xprt_question_rec.DEFAULT_VALUE IS NULL
604 THEN
605 x_error_count := x_error_count + 1;
606 okc_api.set_message (p_app_name => g_app_name,
607 p_msg_name => l_okc_i_not_null,
608 p_token1 => l_field,
609 p_token1_value => 'DEFAULT_VALUE'
610 );
611 END IF;
612 END IF;
613
614 IF (x_error_count > 0)
615 THEN
616 x_return_status := g_ret_sts_error;
617 END IF;
618 EXCEPTION
619 WHEN OTHERS
620 THEN
621 x_return_status := g_ret_sts_unexp_error;
622 okc_api.set_message (p_app_name => g_app_name,
623 p_msg_name => g_unexpected_error,
624 p_token1 => g_sqlcode_token,
625 p_token1_value => SQLCODE,
626 p_token2 => g_sqlerrm_token,
627 p_token2_value => SQLERRM
628 );
629 END validate_row;
630
631 PROCEDURE insert_row (
632 p_question_id IN OUT NOCOPY NUMBER,
633 p_question_type IN VARCHAR2,
634 p_question_intent IN VARCHAR2,
635 p_disabled_flag IN VARCHAR2,
636 p_question_datatype IN VARCHAR2,
637 p_value_set_name IN VARCHAR2,
638 p_default_value IN NUMBER,
639 p_minimum_value IN NUMBER,
640 p_maximum_value IN NUMBER,
641 p_question_sync_flag IN VARCHAR2,
642 p_object_version_number IN NUMBER,
643 p_created_by IN NUMBER,
644 p_creation_date IN DATE,
645 p_last_updated_by IN NUMBER,
646 p_last_update_date IN DATE,
647 p_last_update_login IN NUMBER,
648 p_program_id IN NUMBER,
649 p_request_id IN NUMBER,
650 p_program_application_id IN NUMBER,
651 p_program_update_date IN DATE,
652 p_tl_question_type IN VARCHAR2,
653 p_source_lang IN VARCHAR2,
654 p_question_name IN VARCHAR2,
655 p_description IN VARCHAR2,
656 p_prompt IN VARCHAR2,
657 x_return_status OUT NOCOPY VARCHAR2
658 )
659 IS
660 BEGIN
661
662 x_return_status := g_ret_sts_success;
663
664 IF p_question_id IS NULL
665 THEN
666 SELECT OKC_XPRT_QUESTIONS_B_S.NEXTVAL
667 INTO p_question_id
668 FROM DUAL;
669 END IF;
670
671 INSERT INTO okc_xprt_questions_b
672 (question_id, question_type, question_intent,
673 disabled_flag, question_datatype, value_set_name,
674 DEFAULT_VALUE, minimum_value, maximum_value,
675 question_sync_flag, object_version_number,
676 created_by, creation_date, last_updated_by,
677 last_update_date, last_update_login, program_id,
678 request_id, program_application_id,
679 program_update_date
680 )
681 VALUES (p_question_id, p_question_type, p_question_intent,
682 p_disabled_flag, p_question_datatype, p_value_set_name,
683 p_default_value, p_minimum_value, p_maximum_value,
684 p_question_sync_flag, p_object_version_number,
685 p_created_by, p_creation_date, p_last_updated_by,
686 p_last_update_date, p_last_update_login, p_program_id,
687 p_request_id, p_program_application_id,
688 p_program_update_date
689 )
690 -- returning question_id into p_question_id
691 ;
692
693 INSERT INTO okc_xprt_questions_tl
694 (question_id, question_name, question_type, LANGUAGE,
695 source_lang, description, prompt, created_by,
696 creation_date, last_updated_by, last_update_date,
697 last_update_login)
698 SELECT p_question_id, p_question_name, p_question_type,
699 l.language_code, p_source_lang, p_description, p_prompt,
700 p_created_by, p_creation_date, p_last_updated_by,
701 p_last_update_date, p_last_update_login
702 FROM fnd_languages l
703 WHERE l.installed_flag IN ('I', 'B')
704 AND NOT EXISTS (
705 SELECT NULL
706 FROM okc_xprt_questions_tl t
707 WHERE t.question_id = p_question_id
708 AND t.LANGUAGE = l.language_code);
709 EXCEPTION
710 WHEN OTHERS
711 THEN
712 x_return_status := g_ret_sts_unexp_error;
713 okc_api.set_message (p_app_name => g_app_name,
714 p_msg_name => g_unexpected_error,
715 p_token1 => g_sqlcode_token,
716 p_token1_value => SQLCODE,
717 p_token2 => g_sqlerrm_token,
718 p_token2_value => SQLERRM
719 );
720 END insert_row;
721
722 PROCEDURE read_message (
723 p_entity IN VARCHAR2,
724 p_process IN VARCHAR2,
725 p_action IN VARCHAR2,
726 x_message IN OUT NOCOPY VARCHAR2
727 )
728 IS
729 l_message VARCHAR2 (2000);
730 l_entity VARCHAR2(30);
731
732 BEGIN
733
734
735
736
737
738 FOR i IN 1 .. fnd_msg_pub.count_msg
739 LOOP
740 l_message := fnd_msg_pub.get (i, p_encoded => fnd_api.g_false);
741
742
743 IF LENGTH (l_message) + LENGTH (Nvl(x_message,' ')) <= 2500
744 THEN
745
746 x_message := x_message || l_message;
747
748 ELSE
749 EXIT;
750 END IF;
751 END LOOP;
752
753
754
755 IF Nvl(p_entity,'Q') = 'Q' THEN
756 l_entity := 'QUESTION';
757 ELSE
758 l_entity := 'CONSTANT';
759 END IF;
760
761 fnd_msg_pub.initialize;
762
763 IF p_action = 'CREATE' THEN
764 okc_api.set_message(p_app_name => g_app_name,
765 p_msg_name => 'OKC_I_CREATION_ENTITY_FAIL',
766 p_token1 => 'ENTITY',
767 p_token1_value => l_entity
768 );
769 ELSIF p_action ='UPDATE' THEN
770 okc_api.set_message(p_app_name => g_app_name,
771 p_msg_name => 'OKC_I_UPDATE_ENTITY_FAIL',
772 p_token1 => 'ENTITY',
773 p_token1_value => l_entity
774 );
775 END IF;
776
777 okc_api.set_message(p_app_name => g_app_name,
778 p_msg_name => 'OKC_I_ERROR_PROCEDURE',
779 p_token1 => 'PROCEDURE',
780 p_token1_value => p_process
781 );
782
783 l_message := fnd_msg_pub.get (1, p_encoded => fnd_api.g_false);
784 l_message := l_message || fnd_msg_pub.get (2, p_encoded => fnd_api.g_false);
785
786 x_message := x_message ||' '||l_message;
787
788
789
790 EXCEPTION
791 WHEN OTHERS
792 THEN
793 NULL;
794 END read_message;
795
796 PROCEDURE create_question (
797 p_xprt_question_rec IN OUT NOCOPY xprt_qn_const_rec_type,
798 p_commit IN VARCHAR2 := FND_API.G_FALSE,
799 x_return_status OUT NOCOPY VARCHAR2,
800 x_msg_data OUT NOCOPY VARCHAR2
801 )
802 IS
803 l_return_status VARCHAR2 (1);
804 l_return_status2 VARCHAR2 (1);
805
806 BEGIN
807 /**
808 * Default Row
809 * Validate Row
810 * Insert Row
811 **/
812 SAVEPOINT create_question_sp;
813 x_return_status := g_ret_sts_success;
814
815
816
817
818 fnd_msg_pub.initialize;
819 default_row (p_xprt_question_rec => p_xprt_question_rec,
820 x_returns_status => l_return_status
821 );
822
823
824 IF l_return_status <> g_ret_sts_success
825 THEN
826
827 x_return_status := l_return_status;
828 -- x_msg_count := FND_MSG_PUB.Count_Msg;
829 read_message( p_xprt_question_rec.qn_const_type
830 ,'DEFAULT_ROW'
831 , 'CREATE'
832 , x_msg_data);
833
834 fnd_msg_pub.initialize;
835 ROLLBACK TO create_question_sp;
836 RETURN;
837 END IF;
838
839
840 l_return_status := NULL;
841 fnd_msg_pub.initialize;
842 validate_row (p_xprt_question_rec => p_xprt_question_rec,
843 x_return_status => l_return_status
844 );
845
846
847
848 IF l_return_status <> g_ret_sts_success
849 THEN
850
851 x_return_status := l_return_status;
852 -- x_msg_count := FND_MSG_PUB.Count_Msg;
853 read_message (p_xprt_question_rec.qn_const_type
854 ,'VALIDATE_ROW', 'CREATE',x_msg_data);
855 fnd_msg_pub.initialize;
856 ROLLBACK TO create_question_sp;
857 RETURN;
858 END IF;
859
860 l_return_status := '';
861 fnd_msg_pub.initialize;
862
863 insert_row
864 (p_question_id => p_xprt_question_rec.QN_CONST_id,
865 p_question_type => p_xprt_question_rec.QN_CONST_type,
866 p_question_intent => p_xprt_question_rec.QN_CONST_intent,
867 p_disabled_flag => p_xprt_question_rec.disabled_flag,
868 p_question_datatype => p_xprt_question_rec.question_datatype,
869 p_value_set_name => p_xprt_question_rec.value_set_name,
870 p_default_value => p_xprt_question_rec.DEFAULT_VALUE,
871 p_minimum_value => p_xprt_question_rec.minimum_value,
872 p_maximum_value => p_xprt_question_rec.maximum_value,
873 p_question_sync_flag => p_xprt_question_rec.question_sync_flag,
874 p_object_version_number => p_xprt_question_rec.object_version_number,
875 p_created_by => p_xprt_question_rec.created_by,
876 p_creation_date => p_xprt_question_rec.creation_date,
877 p_last_updated_by => p_xprt_question_rec.last_updated_by,
878 p_last_update_date => p_xprt_question_rec.last_update_date,
879 p_last_update_login => p_xprt_question_rec.last_update_login,
880 p_program_id => p_xprt_question_rec.program_id,
881 p_request_id => p_xprt_question_rec.request_id,
882 p_program_application_id => p_xprt_question_rec.program_application_id,
883 p_program_update_date => p_xprt_question_rec.program_update_date,
884 p_tl_question_type => p_xprt_question_rec.qn_const_type,
885 p_source_lang => p_xprt_question_rec.source_lang,
886 p_question_name => p_xprt_question_rec.QN_CONST_name,
887 p_description => p_xprt_question_rec.description,
888 p_prompt => p_xprt_question_rec.prompt,
889 x_return_status => l_return_status
890 );
891
892 IF l_return_status <> g_ret_sts_success
893 THEN
894
895 x_return_status := l_return_status;
896 read_message (p_xprt_question_rec.qn_const_type, 'INSERT_ROW', 'CREATE', x_msg_data);
897 fnd_msg_pub.initialize;
898 ROLLBACK TO create_question_sp;
899 RETURN;
900 END IF;
901
902 IF FND_API.To_Boolean( p_commit ) THEN
903 COMMIT ;
904 END IF;
905
906 EXCEPTION
907 WHEN OTHERS
908 THEN
909 x_return_status := g_ret_sts_unexp_error;
910 okc_api.set_message (p_app_name => g_app_name,
911 p_msg_name => g_unexpected_error,
912 p_token1 => g_sqlcode_token,
913 p_token1_value => SQLCODE,
914 p_token2 => g_sqlerrm_token,
915 p_token2_value => SQLERRM
916 );
917 END create_question;
918
919 PROCEDURE update_question(p_xprt_update_question_rec IN OUT NOCOPY xprt_qn_const_rec_type,
920 p_commit IN VARCHAR2 := FND_API.G_FALSE,
921 x_return_status OUT NOCOPY VARCHAR2,
922 x_msg_data OUT NOCOPY VARCHAR2) IS
923
924 l_update_flag VARCHAR2(1);
925
926 l_question_type OKC_XPRT_QUESTIONS_B.question_type%type;
927 l_question_intent OKC_XPRT_QUESTIONS_B.Question_intent%TYPE;
928 l_disabled_flag OKC_XPRT_QUESTIONS_B.disabled_flag%TYPE;
929 l_question_datatype OKC_XPRT_QUESTIONS_B.question_datatype%TYPE;
930 l_value_set_name OKC_XPRT_QUESTIONS_B.value_set_name%TYPE;
931 l_object_version_number NUMBER;
932
933 l_question_name OKC_XPRT_QUESTIONS_TL.question_name%TYPE;
934 l_source_lang OKC_XPRT_QUESTIONS_TL.source_lang%TYPE;
935 l_lang OKC_XPRT_QUESTIONS_TL.language%TYPE;
936 l_description OKC_XPRT_QUESTIONS_TL.description%TYPE;
937 l_prompt OKC_XPRT_QUESTIONS_TL.prompt%TYPE;
938
939 l_default_value OKC_XPRT_QUESTIONS_B.default_value%TYPE;
940
941 l_val_question_name_flag VARCHAR2(1) := 'N';
942 l_return_status varchar2(1);
943 l_error_count NUMBER := 0;
944
945 l_ret_status VARCHAR2(1);
946 l_entity VARCHAR2(30);
947
948 BEGIN
949
950
951
952 x_return_status := G_RET_STS_SUCCESS;
953 --x_msg_count := 0;
954 x_msg_data := NULL;
955 -- Update QuestionSyncFlag to 'Y' for any changes
956 -- Increment Object Version Number
957 -- Update Last Updated By
958 -- Update Last Update Date
959 -- Update Last Update Login
960
961 -- Assuming question id is passed for the update rec
962 -- If it is not passed need to derive it from the
963 FND_MSG_PUB.initialize;
964 BEGIN
965 SELECT question_type,
966 question_intent,
967 disabled_flag,
968 question_datatype,
969 value_set_name,
970 default_value,
971 object_version_number
972 INTO l_question_type,
973 l_question_intent,
974 l_disabled_flag,
975 l_question_datatype,
976 l_value_set_name,
977 l_default_value,
978 l_object_version_number
979 FROM okc_xprt_questions_b
980 WHERE question_id = p_xprt_update_question_rec.QN_CONST_id
981 AND question_type = p_xprt_update_question_rec.QN_CONST_type;
982
983
984
985
986
987 SELECT question_name, language, source_lang, description, prompt
988 INTO l_question_name, l_lang, l_source_lang, l_description, l_prompt
989 FROM okc_xprt_questions_tl
990 WHERE question_id = p_xprt_update_question_rec.qn_const_id
991 AND language =
992 Decode(p_xprt_update_question_rec.lang,OKC_API.G_MISS_CHAR, UserEnv('lang'),
993 NULL,UserEnv('lang')
994 ,p_xprt_update_question_rec.lang)
995 AND question_type = p_xprt_update_question_rec.qn_const_type
996 ;
997
998 EXCEPTION
999 WHEN No_Data_Found THEN
1000 x_return_status := g_ret_sts_error;
1001 okc_api.set_message
1002 (p_app_name => g_app_name,
1003 p_msg_name => l_okc_i_invalid_value,
1004 p_token1 => l_field,
1005 p_token1_value => 'QUESTION_ID'
1006 );
1007 x_msg_data := fnd_msg_pub.get (1, p_encoded => fnd_api.g_false);
1008 FND_MSG_PUB.initialize;
1009 RETURN;
1010
1011 WHEN OTHERS THEN
1012 x_return_status := g_ret_sts_unexp_error;
1013 -- x_msg_count := 1;
1014 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1015 p_msg_name => G_UNEXPECTED_ERROR,
1016 p_token1 => G_SQLCODE_TOKEN,
1017 p_token1_value => sqlcode,
1018 p_token2 => G_SQLERRM_TOKEN,
1019 p_token2_value => sqlerrm);
1020 x_msg_data := fnd_msg_pub.get (1, p_encoded => fnd_api.g_false);
1021 FND_MSG_PUB.initialize;
1022 RETURN;
1023 END;
1024
1025 IF
1026 ( p_xprt_update_question_rec.qn_const_type <> OKC_API.G_MISS_CHAR
1027 AND p_xprt_update_question_rec.qn_const_type <> l_question_type)
1028 OR p_xprt_update_question_rec.qn_const_type IS NULL
1029 THEN
1030 x_return_status := G_RET_STS_ERROR;
1031 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1032 p_msg_name => 'OKC_I_NO_CHANGE',
1033 p_token1 => 'FIELD',
1034 p_token1_value => 'QUESTION_TYPE' );
1035 x_msg_data := fnd_msg_pub.get (1, p_encoded => fnd_api.g_false);
1036 FND_MSG_PUB.initialize;
1037 RETURN;
1038 END IF;
1039
1040
1041
1042 SAVEPOINT update_question_sp;
1043 -- Validate the attributes
1044 l_return_status := '';
1045 FND_MSG_PUB.initialize;
1046
1047 l_return_status := G_RET_STS_SUCCESS;
1048 l_update_flag := okc_xprt_util_pvt.Ok_To_Delete_Question(p_question_id => p_xprt_update_question_rec.qn_const_id);
1049
1050 IF l_question_type= 'Q' THEN
1051 l_entity := 'QUESTION';
1052 ELSE
1053 l_entity := 'CONSTANT';
1054 END IF;
1055
1056 IF Nvl(l_update_flag,'Y') = 'N' THEN
1057 -- Question/Constant Used in a Rule
1058
1059 IF p_xprt_update_question_rec.qn_const_intent IS NULL
1060 OR
1061 (p_xprt_update_question_rec.qn_const_intent <> OKC_API.G_MISS_CHAR
1062 AND
1063 p_xprt_update_question_rec.qn_const_intent <> l_question_intent
1064 )
1065 THEN
1066 l_return_status := G_RET_STS_ERROR;
1067 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1068 p_msg_name => 'OKC_I_QC_USED_IN_RULE',
1069 p_token1 => 'ENTITY' ,
1070 p_token1_value => l_entity,
1071 p_token2 => 'FIELD',
1072 p_token2_value => 'QUESTION_INTENT');
1073 END IF;
1074
1075 IF p_xprt_update_question_rec.qn_const_name IS NULL
1076 OR
1077 (p_xprt_update_question_rec.qn_const_name <> OKC_API.G_MISS_CHAR AND
1078 p_xprt_update_question_rec.qn_const_name <> l_question_name) THEN
1079 l_return_status := G_RET_STS_ERROR;
1080 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1081 p_msg_name => 'OKC_I_QC_USED_IN_RULE',
1082 p_token1 => 'ENTITY' ,
1083 p_token1_value => l_entity,
1084 p_token2 => 'FIELD',
1085 p_token2_value => 'QUESTION_NAME');
1086 END IF;
1087
1088 -- Check whether user wants to update fields that are not allowed to update
1089 IF (l_question_type = 'Q') THEN
1090 -- Question Record
1091 -- VALIDATE Response Type/ Value Set
1092 IF p_xprt_update_question_rec.question_datatype IS NULL
1093 OR
1094 (p_xprt_update_question_rec.question_datatype <>
1095 OKC_API.G_MISS_CHAR AND p_xprt_update_question_rec.question_datatype <>
1096 l_question_datatype)
1097
1098 THEN
1099 l_return_status := G_RET_STS_ERROR;
1100 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1101 p_msg_name => 'OKC_I_QC_USED_IN_RULE',
1102 p_token1 => 'ENTITY' ,
1103 p_token1_value => l_entity,
1104 p_token2 => 'FIELD',
1105 p_token2_value => 'QUESTION_DATATYPE');
1106
1107 END IF;
1108
1109 IF p_xprt_update_question_rec.value_set_name IS NULL OR
1110 (p_xprt_update_question_rec.value_set_name <>
1111 OKC_API.G_MISS_CHAR AND
1112 p_xprt_update_question_rec.value_set_name <> l_value_set_name) THEN
1113 l_return_status := G_RET_STS_ERROR;
1114 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1115 p_msg_name => 'OKC_I_QC_USED_IN_RULE',
1116 p_token1 => 'ENTITY' ,
1117 p_token1_value => l_entity,
1118 p_token2 => 'FIELD',
1119 p_token2_value => 'VALUE_SET_NAME');
1120
1121 END IF;
1122 END IF;
1123 END IF;
1124
1125 IF l_return_status <> G_RET_STS_SUCCESS THEN
1126 x_return_status := G_RET_STS_ERROR;
1127 read_message(p_xprt_update_question_rec.qn_const_type,'UPDATE_QUESTION', 'UPDATE', x_msg_data);
1128 FND_MSG_PUB.initialize;
1129 ROLLBACK TO update_question_sp;
1130 return;
1131 END IF;
1132
1133
1134
1135 -- Record whether intent or question/constant name is changed
1136 -- If changed need to validate for duplicates
1137 IF p_xprt_update_question_rec.qn_const_name IS NULL
1138 OR
1139 (p_xprt_update_question_rec.qn_const_name <> OKC_API.G_MISS_CHAR
1140 AND
1141 p_xprt_update_question_rec.qn_const_name <> l_question_name) THEN
1142 l_val_question_name_flag := 'Y';
1143 END IF;
1144
1145
1146
1147 -- Default the attributes
1148 p_xprt_update_question_rec.last_updated_by := fnd_global.user_id;
1149 p_xprt_update_question_rec.last_update_date := SYSDATE;
1150 p_xprt_update_question_rec.last_update_login := fnd_global.login_id;
1151
1152 IF p_xprt_update_question_rec.qn_const_type = OKC_API.G_MISS_CHAR THEN
1153 p_xprt_update_question_rec.qn_const_type := l_question_type;
1154 END IF;
1155
1156 IF p_xprt_update_question_rec.qn_const_type = 'Q' THEN
1157 p_xprt_update_question_rec.question_sync_flag := 'Y';
1158 ELSE
1159 p_xprt_update_question_rec.question_sync_flag := 'N';
1160 END IF;
1161
1162 IF p_xprt_update_question_rec.qn_const_intent = OKC_API.G_MISS_CHAR THEN
1163 p_xprt_update_question_rec.qn_const_intent := l_question_intent;
1164 END IF;
1165
1166 IF p_xprt_update_question_rec.disabled_flag = OKC_API.G_MISS_CHAR THEN
1167 p_xprt_update_question_rec.disabled_flag := l_disabled_flag;
1168 END IF;
1169
1170 IF p_xprt_update_question_rec.qn_const_type = 'C' THEN
1171 p_xprt_update_question_rec.question_datatype := 'N';
1172 END IF;
1173
1174
1175 IF p_xprt_update_question_rec.question_datatype = OKC_API.G_MISS_CHAR THEN
1176 p_xprt_update_question_rec.question_datatype := l_question_datatype;
1177 ELSE
1178 IF p_xprt_update_question_rec.question_datatype = 'B' THEN
1179 p_xprt_update_question_rec.value_set_name := 'OKC_XPRT_YES_NO';
1180 ELSIF p_xprt_update_question_rec.question_datatype = 'N' THEN
1181 p_xprt_update_question_rec.value_set_name := NULL;
1182 END IF;
1183 END IF;
1184
1185 IF p_xprt_update_question_rec.value_set_name = OKC_API.G_MISS_CHAR THEN
1186 p_xprt_update_question_rec.value_set_name := l_value_set_name;
1187 END IF;
1188
1189 IF p_xprt_update_question_rec.default_value = OKC_API.G_MISS_NUM THEN
1190 p_xprt_update_question_rec.default_value := l_default_value;
1191 END IF;
1192
1193 IF p_xprt_update_question_rec.qn_const_name = OKC_API.G_MISS_CHAR THEN
1194 p_xprt_update_question_rec.qn_const_name := l_question_name;
1195 END IF;
1196
1197 IF p_xprt_update_question_rec.lang = OKC_API.G_MISS_CHAR THEN
1198 p_xprt_update_question_rec.lang := l_lang;
1199 END IF;
1200
1201 IF p_xprt_update_question_rec.source_lang = OKC_API.G_MISS_CHAR THEN
1202 p_xprt_update_question_rec.source_lang := l_source_lang;
1203 END IF;
1204
1205
1206 IF p_xprt_update_question_rec.description = OKC_API.G_MISS_CHAR THEN
1207 p_xprt_update_question_rec.description := l_description;
1208 END IF;
1209
1210 IF p_xprt_update_question_rec.prompt = OKC_API.G_MISS_CHAR THEN
1211 p_xprt_update_question_rec.prompt := l_prompt;
1212 END IF;
1213
1214
1215
1216 validate_row(p_xprt_question_rec => p_xprt_update_question_rec,
1217 p_val_qn_name => l_val_question_name_flag,
1218 x_return_status => l_ret_status);
1219
1220 IF l_ret_status <> G_RET_STS_SUCCESS
1221 THEN
1222 x_return_status := G_RET_STS_ERROR;
1223 read_message(p_xprt_update_question_rec.qn_const_type,'VALIDATE_ROW','UPDATE', x_msg_data);
1224 FND_MSG_PUB.initialize;
1225 ROLLBACK TO update_question_sp;
1226 RETURN;
1227 END IF;
1228
1229
1230
1231 UPDATE okc_xprt_questions_b
1232
1233 SET question_intent = p_xprt_update_question_rec.qn_const_intent,
1234 disabled_flag = p_xprt_update_question_rec.disabled_flag,
1235 question_datatype = p_xprt_update_question_rec.question_datatype,
1236 value_set_name = p_xprt_update_question_rec.value_set_name,
1237 default_value = p_xprt_update_question_rec.default_value,
1238 question_sync_flag = p_xprt_update_question_rec.question_sync_flag,
1239 object_version_number = l_object_version_number + 1,
1240 last_updated_by = p_xprt_update_question_rec.last_updated_by,
1241 last_update_date = p_xprt_update_question_rec.last_update_date,
1242 last_update_login = p_xprt_update_question_rec.last_update_login
1243
1244 WHERE question_id = p_xprt_update_question_rec.qn_const_id;
1245
1246 UPDATE okc_xprt_questions_tl
1247 SET question_name = p_xprt_update_question_rec.qn_const_name,
1248 description = p_xprt_update_question_rec.description,
1249 prompt = p_xprt_update_question_rec.prompt,
1250 last_updated_by = p_xprt_update_question_rec.last_updated_by,
1251 last_update_date = p_xprt_update_question_rec.last_update_date,
1252 last_update_login = p_xprt_update_question_rec.last_update_login
1253 WHERE question_id = p_xprt_update_question_rec.qn_const_id
1254 AND LANGUAGE = p_xprt_update_question_rec.lang;
1255
1256 x_return_status := G_RET_STS_SUCCESS;
1257
1258 IF FND_API.To_Boolean( p_commit ) THEN
1259 COMMIT ;
1260 END IF;
1261
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 x_return_status := g_ret_sts_unexp_error;
1265
1266 Okc_Api.Set_Message(p_app_name => G_APP_NAME,
1267 p_msg_name => G_UNEXPECTED_ERROR,
1268 p_token1 => G_SQLCODE_TOKEN,
1269 p_token1_value => sqlcode,
1270 p_token2 => G_SQLERRM_TOKEN,
1271 p_token2_value => sqlerrm);
1272
1273 x_msg_data := fnd_msg_pub.get (1, p_encoded => fnd_api.g_false);
1274 ROLLBACK TO update_question_sp;
1275 END update_question;
1276 END okc_xprt_question_pvt;