[Home] [Help]
PACKAGE BODY: APPS.OKL_ASR_PVT
Source
1 PACKAGE BODY Okl_Asr_Pvt AS
2 /* $Header: OKLSASRB.pls 115.6 2004/05/24 23:28:32 pjgomes noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE validate_id
6 ---------------------------------------------------------------------------
7 PROCEDURE validate_id (p_asrv_rec IN asrv_rec_type,
8 x_return_status OUT NOCOPY VARCHAR2) IS
9
10 BEGIN
11 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
12 --Check for Null
13 IF p_asrv_rec.id = Okl_Api.G_MISS_NUM OR
14 p_asrv_rec.id IS NULL
15 THEN
16
17 x_return_status := Okl_Api.G_RET_STS_ERROR;
18 --set error message in message stack
19 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
20 p_msg_name => G_REQUIRED_VALUE,
21 p_token1 => G_COL_NAME_TOKEN,
22 p_token1_value => 'id');
23 RAISE G_EXCEPTION_HALT_VALIDATION;
24
25 END IF;
26 END validate_id;
27
28 ---------------------------------------------------------------------------
29 -- PROCEDURE validate_object_version_number
30 ---------------------------------------------------------------------------
31 PROCEDURE validate_object_version_number (p_asrv_rec IN asrv_rec_type,
32 x_return_status OUT NOCOPY VARCHAR2) IS
33
34 BEGIN
35 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
36 --Check for Null
37 IF p_asrv_rec.object_version_number = Okl_Api.G_MISS_NUM OR
38 p_asrv_rec.object_version_number IS NULL
39 THEN
40
41 x_return_status := Okl_Api.G_RET_STS_ERROR;
42 --set error message in message stack
43 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
44 p_msg_name => G_REQUIRED_VALUE,
45 p_token1 => G_COL_NAME_TOKEN,
46 p_token1_value => 'object_version_number');
47 RAISE G_EXCEPTION_HALT_VALIDATION;
48
49 END IF;
50 END validate_object_version_number;
51
52 ---------------------------------------------------------------------------
53 -- PROCEDURE validate_ant_id
54 ---------------------------------------------------------------------------
55 PROCEDURE validate_ant_id (p_asrv_rec IN asrv_rec_type,
56 x_return_status OUT NOCOPY VARCHAR2) IS
57
58 l_dummy_var VARCHAR2(1) := '0';
59
60 CURSOR l_ant_id_csr IS
61 SELECT '1'
62 FROM OKL_ANSWER_SETS_B
63 WHERE id = p_asrv_rec.ant_id;
64
65 BEGIN
66 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
67
68 --Check for Null
69 IF p_asrv_rec.ant_id = Okl_Api.G_MISS_NUM OR
70 p_asrv_rec.ant_id IS NULL
71 THEN
72 x_return_status := Okl_Api.G_RET_STS_ERROR;
73 --set error message in message stack
74 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
75 p_msg_name => G_REQUIRED_VALUE,
76 p_token1 => G_COL_NAME_TOKEN,
77 p_token1_value => 'ant_id');
78 RAISE G_EXCEPTION_HALT_VALIDATION;
79 END IF;
80
81 -- Validate Foreign Key
82 OPEN l_ant_id_csr;
83 FETCH l_ant_id_csr INTO l_dummy_var;
84 CLOSE l_ant_id_csr;
85
86 IF (l_dummy_var <> '1') THEN
87 x_return_status := Okl_Api.G_RET_STS_ERROR;
88 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
89 p_msg_name => G_NO_PARENT_RECORD,
90 p_token1 => G_COL_NAME_TOKEN,
91 p_token1_value => 'ANT_ID_FOR',
92 p_token2 => G_CHILD_TABLE_TOKEN,
93 p_token2_value => G_VIEW,
94 p_token3 => G_PARENT_TABLE_TOKEN,
95 p_token3_value => 'OKL_ANSR_SET_CRTRIA_V');
96
97 RAISE G_EXCEPTION_HALT_VALIDATION;
98 END IF;
99 END validate_ant_id;
100
101 ---------------------------------------------------------------------------
102 -- PROCEDURE validate_qcn_id
103 ---------------------------------------------------------------------------
104 PROCEDURE validate_qcn_id (p_asrv_rec IN asrv_rec_type,
105 x_return_status OUT NOCOPY VARCHAR2) IS
106
107 l_dummy_var VARCHAR2(1) := '0';
108
109 CURSOR l_qcn_id_csr IS
110 SELECT '1'
111 FROM OKL_QUESTION_CRTRNS
112 WHERE id = p_asrv_rec.qcn_id;
113
114 BEGIN
115 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
116
117 --Check for Null
118 IF p_asrv_rec.qcn_id = Okl_Api.G_MISS_NUM OR
119 p_asrv_rec.qcn_id IS NULL
120 THEN
121 x_return_status := Okl_Api.G_RET_STS_ERROR;
122 --set error message in message stack
123 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
124 p_msg_name => G_REQUIRED_VALUE,
125 p_token1 => G_COL_NAME_TOKEN,
126 p_token1_value => 'qcn_id');
127 RAISE G_EXCEPTION_HALT_VALIDATION;
128 END IF;
129
130 -- Validate Foreign Key
131 OPEN l_qcn_id_csr;
132 FETCH l_qcn_id_csr INTO l_dummy_var;
133 CLOSE l_qcn_id_csr;
134
135 IF (l_dummy_var <> '1') THEN
136 x_return_status := Okl_Api.G_RET_STS_ERROR;
137 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
138 p_msg_name => G_NO_PARENT_RECORD,
139 p_token1 => G_COL_NAME_TOKEN,
140 p_token1_value => 'QCN_ID_FOR',
141 p_token2 => G_CHILD_TABLE_TOKEN,
142 p_token2_value => G_VIEW,
143 p_token3 => G_PARENT_TABLE_TOKEN,
144 p_token3_value => 'OKL_ANSR_SET_CRTRIA_V');
145
146 RAISE G_EXCEPTION_HALT_VALIDATION;
147 END IF;
148 END validate_qcn_id;
149
150 ---------------------------------------------------------------------------
151 -- PROCEDURE validate_sort_sequence
152 ---------------------------------------------------------------------------
153 PROCEDURE validate_sort_sequence (p_asrv_rec IN asrv_rec_type,
154 x_return_status OUT NOCOPY VARCHAR2) IS
155
156 BEGIN
157 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
158
159 --Check for Null
160 IF p_asrv_rec.sort_sequence = Okl_Api.G_MISS_NUM OR
161 p_asrv_rec.sort_sequence IS NULL
162 THEN
163
164 x_return_status := Okl_Api.G_RET_STS_ERROR;
165 --set error message in message stack
166 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
167 p_msg_name => G_REQUIRED_VALUE,
168 p_token1 => G_COL_NAME_TOKEN,
169 p_token1_value => 'sort_sequence');
170 RAISE G_EXCEPTION_HALT_VALIDATION;
171
172 END IF;
173 END validate_sort_sequence;
174
175 ---------------------------------------------------------------------------
176 -- FUNCTION get_seq_id
177 ---------------------------------------------------------------------------
178 FUNCTION get_seq_id RETURN NUMBER IS
179 BEGIN
180 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
181 END get_seq_id;
182
183 ---------------------------------------------------------------------------
184 -- PROCEDURE qc
185 ---------------------------------------------------------------------------
186 PROCEDURE qc IS
187 BEGIN
188 NULL;
189 END qc;
190
191 ---------------------------------------------------------------------------
192 -- PROCEDURE change_version
193 ---------------------------------------------------------------------------
194 PROCEDURE change_version IS
195 BEGIN
196 NULL;
197 END change_version;
198
199 ---------------------------------------------------------------------------
200 -- PROCEDURE api_copy
201 ---------------------------------------------------------------------------
202 PROCEDURE api_copy IS
203 BEGIN
204 NULL;
205 END api_copy;
206
207 ---------------------------------------------------------------------------
208 -- PROCEDURE add_language
209 ---------------------------------------------------------------------------
210 PROCEDURE add_language IS
211 BEGIN
212 DELETE FROM OKL_ANSR_SET_CRTRIA_TL T
213 WHERE NOT EXISTS (
214 SELECT NULL
215 FROM OKL_ANSR_SET_CRTRIA_B B --fixed bug 3321017 by kmotepal
216 WHERE B.ID = T.ID
217 );
218
219 UPDATE OKL_ANSR_SET_CRTRIA_TL T SET (
220 DESCRIPTION) = (SELECT
221 B.DESCRIPTION
222 FROM OKL_ANSR_SET_CRTRIA_TL B
223 WHERE B.ID = T.ID
224 AND B.LANGUAGE = T.SOURCE_LANG)
225 WHERE (
226 T.ID,
227 T.LANGUAGE)
228 IN (SELECT
229 SUBT.ID,
230 SUBT.LANGUAGE
231 FROM OKL_ANSR_SET_CRTRIA_TL SUBB, OKL_ANSR_SET_CRTRIA_TL SUBT
232 WHERE SUBB.ID = SUBT.ID
233 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
234 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
235 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
236 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
237 ));
238
239 INSERT INTO OKL_ANSR_SET_CRTRIA_TL (
240 ID,
241 LANGUAGE,
242 SOURCE_LANG,
243 SFWT_FLAG,
244 DESCRIPTION,
245 CREATED_BY,
246 CREATION_DATE,
247 LAST_UPDATED_BY,
248 LAST_UPDATE_DATE,
249 LAST_UPDATE_LOGIN)
250 SELECT
251 B.ID,
252 L.LANGUAGE_CODE,
253 B.SOURCE_LANG,
254 B.SFWT_FLAG,
255 B.DESCRIPTION,
256 B.CREATED_BY,
257 B.CREATION_DATE,
258 B.LAST_UPDATED_BY,
259 B.LAST_UPDATE_DATE,
260 B.LAST_UPDATE_LOGIN
261 FROM OKL_ANSR_SET_CRTRIA_TL B, FND_LANGUAGES L
262 WHERE L.INSTALLED_FLAG IN ('I', 'B')
263 AND B.LANGUAGE = USERENV('LANG')
264 AND NOT EXISTS(
265 SELECT NULL
266 FROM OKL_ANSR_SET_CRTRIA_TL T
267 WHERE T.ID = B.ID
268 AND T.LANGUAGE = L.LANGUAGE_CODE
269 );
270
271 END add_language;
272
273 ---------------------------------------------------------------------------
274 -- FUNCTION get_rec for: OKL_ANSR_SET_CRTRIA_B
275 ---------------------------------------------------------------------------
276 FUNCTION get_rec (
277 p_asr_rec IN asr_rec_type,
278 x_no_data_found OUT NOCOPY BOOLEAN
279 ) RETURN asr_rec_type IS
280 CURSOR asr_pk_csr (p_id IN NUMBER) IS
281 SELECT
282 ID,
283 ANT_ID,
284 QCN_ID,
285 SORT_SEQUENCE,
286 OBJECT_VERSION_NUMBER,
287 ATTRIBUTE_CATEGORY,
288 ATTRIBUTE1,
289 ATTRIBUTE2,
290 ATTRIBUTE3,
291 ATTRIBUTE4,
292 ATTRIBUTE5,
293 ATTRIBUTE6,
294 ATTRIBUTE7,
295 ATTRIBUTE8,
296 ATTRIBUTE9,
297 ATTRIBUTE10,
298 ATTRIBUTE11,
299 ATTRIBUTE12,
300 ATTRIBUTE13,
301 ATTRIBUTE14,
302 ATTRIBUTE15,
303 CREATED_BY,
304 CREATION_DATE,
305 LAST_UPDATED_BY,
306 LAST_UPDATE_DATE,
307 LAST_UPDATE_LOGIN
308 FROM Okl_Ansr_Set_Crtria_B
309 WHERE okl_ansr_set_crtria_b.id = p_id;
310 l_asr_pk asr_pk_csr%ROWTYPE;
311 l_asr_rec asr_rec_type;
312 BEGIN
313 x_no_data_found := TRUE;
314 -- Get current database values
315 OPEN asr_pk_csr (p_asr_rec.id);
316 FETCH asr_pk_csr INTO
317 l_asr_rec.ID,
318 l_asr_rec.ANT_ID,
319 l_asr_rec.QCN_ID,
320 l_asr_rec.SORT_SEQUENCE,
321 l_asr_rec.OBJECT_VERSION_NUMBER,
322 l_asr_rec.ATTRIBUTE_CATEGORY,
323 l_asr_rec.ATTRIBUTE1,
324 l_asr_rec.ATTRIBUTE2,
325 l_asr_rec.ATTRIBUTE3,
326 l_asr_rec.ATTRIBUTE4,
327 l_asr_rec.ATTRIBUTE5,
328 l_asr_rec.ATTRIBUTE6,
329 l_asr_rec.ATTRIBUTE7,
330 l_asr_rec.ATTRIBUTE8,
331 l_asr_rec.ATTRIBUTE9,
332 l_asr_rec.ATTRIBUTE10,
333 l_asr_rec.ATTRIBUTE11,
334 l_asr_rec.ATTRIBUTE12,
335 l_asr_rec.ATTRIBUTE13,
336 l_asr_rec.ATTRIBUTE14,
337 l_asr_rec.ATTRIBUTE15,
338 l_asr_rec.CREATED_BY,
339 l_asr_rec.CREATION_DATE,
340 l_asr_rec.LAST_UPDATED_BY,
341 l_asr_rec.LAST_UPDATE_DATE,
342 l_asr_rec.LAST_UPDATE_LOGIN;
343 x_no_data_found := asr_pk_csr%NOTFOUND;
344 CLOSE asr_pk_csr;
345 RETURN(l_asr_rec);
346 END get_rec;
347
348 FUNCTION get_rec (
349 p_asr_rec IN asr_rec_type
350 ) RETURN asr_rec_type IS
351 l_row_notfound BOOLEAN := TRUE;
352 BEGIN
353 RETURN(get_rec(p_asr_rec, l_row_notfound));
354 END get_rec;
355 ---------------------------------------------------------------------------
356 -- FUNCTION get_rec for: OKL_ANSR_SET_CRTRIA_TL
357 ---------------------------------------------------------------------------
358 FUNCTION get_rec (
359 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType,
360 x_no_data_found OUT NOCOPY BOOLEAN
361 ) RETURN OklAnsrSetCrtriaTlRecType IS
362 CURSOR okl_ansr_set_crtria_tl_pk_csr (p_id IN NUMBER,
363 p_language IN VARCHAR2) IS
364 SELECT
365 ID,
366 LANGUAGE,
367 SOURCE_LANG,
368 SFWT_FLAG,
369 DESCRIPTION,
370 CREATED_BY,
371 CREATION_DATE,
372 LAST_UPDATED_BY,
373 LAST_UPDATE_DATE,
374 LAST_UPDATE_LOGIN
375 FROM Okl_Ansr_Set_Crtria_Tl
376 WHERE okl_ansr_set_crtria_tl.id = p_id
377 AND okl_ansr_set_crtria_tl.LANGUAGE = p_language;
378 l_okl_ansr_set_crtria_tl_pk okl_ansr_set_crtria_tl_pk_csr%ROWTYPE;
379 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
380 BEGIN
381 x_no_data_found := TRUE;
382 -- Get current database values
383 OPEN okl_ansr_set_crtria_tl_pk_csr (p_okl_ansr_set_crtria_tl_rec.id,
384 p_okl_ansr_set_crtria_tl_rec.LANGUAGE);
385 FETCH okl_ansr_set_crtria_tl_pk_csr INTO
386 l_okl_ansr_set_crtria_tl_rec.ID,
387 l_okl_ansr_set_crtria_tl_rec.LANGUAGE,
388 l_okl_ansr_set_crtria_tl_rec.SOURCE_LANG,
389 l_okl_ansr_set_crtria_tl_rec.SFWT_FLAG,
390 l_okl_ansr_set_crtria_tl_rec.DESCRIPTION,
391 l_okl_ansr_set_crtria_tl_rec.CREATED_BY,
392 l_okl_ansr_set_crtria_tl_rec.CREATION_DATE,
393 l_okl_ansr_set_crtria_tl_rec.LAST_UPDATED_BY,
394 l_okl_ansr_set_crtria_tl_rec.LAST_UPDATE_DATE,
395 l_okl_ansr_set_crtria_tl_rec.LAST_UPDATE_LOGIN;
396 x_no_data_found := okl_ansr_set_crtria_tl_pk_csr%NOTFOUND;
397 CLOSE okl_ansr_set_crtria_tl_pk_csr;
398 RETURN(l_okl_ansr_set_crtria_tl_rec);
399 END get_rec;
400
401 FUNCTION get_rec (
402 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType
403 ) RETURN OklAnsrSetCrtriaTlRecType IS
404 l_row_notfound BOOLEAN := TRUE;
405 BEGIN
406 RETURN(get_rec(p_okl_ansr_set_crtria_tl_rec, l_row_notfound));
407 END get_rec;
408 ---------------------------------------------------------------------------
409 -- FUNCTION get_rec for: OKL_ANSR_SET_CRTRIA_V
410 ---------------------------------------------------------------------------
411 FUNCTION get_rec (
412 p_asrv_rec IN asrv_rec_type,
413 x_no_data_found OUT NOCOPY BOOLEAN
414 ) RETURN asrv_rec_type IS
415 CURSOR okl_asrv_pk_csr (p_id IN NUMBER) IS
416 SELECT
417 ID,
418 OBJECT_VERSION_NUMBER,
419 SFWT_FLAG,
420 ANT_ID,
421 QCN_ID,
422 SORT_SEQUENCE,
423 DESCRIPTION,
424 ATTRIBUTE_CATEGORY,
425 ATTRIBUTE1,
426 ATTRIBUTE2,
427 ATTRIBUTE3,
428 ATTRIBUTE4,
429 ATTRIBUTE5,
430 ATTRIBUTE6,
431 ATTRIBUTE7,
432 ATTRIBUTE8,
433 ATTRIBUTE9,
434 ATTRIBUTE10,
435 ATTRIBUTE11,
436 ATTRIBUTE12,
437 ATTRIBUTE13,
438 ATTRIBUTE14,
439 ATTRIBUTE15,
440 CREATED_BY,
441 CREATION_DATE,
442 LAST_UPDATED_BY,
443 LAST_UPDATE_DATE,
444 LAST_UPDATE_LOGIN
445 FROM Okl_Ansr_Set_Crtria_V
446 WHERE okl_ansr_set_crtria_v.id = p_id;
447 l_okl_asrv_pk okl_asrv_pk_csr%ROWTYPE;
448 l_asrv_rec asrv_rec_type;
449 BEGIN
450 x_no_data_found := TRUE;
451 -- Get current database values
452 OPEN okl_asrv_pk_csr (p_asrv_rec.id);
453 FETCH okl_asrv_pk_csr INTO
454 l_asrv_rec.ID,
455 l_asrv_rec.OBJECT_VERSION_NUMBER,
456 l_asrv_rec.SFWT_FLAG,
457 l_asrv_rec.ANT_ID,
458 l_asrv_rec.QCN_ID,
459 l_asrv_rec.SORT_SEQUENCE,
460 l_asrv_rec.DESCRIPTION,
461 l_asrv_rec.ATTRIBUTE_CATEGORY,
462 l_asrv_rec.ATTRIBUTE1,
463 l_asrv_rec.ATTRIBUTE2,
464 l_asrv_rec.ATTRIBUTE3,
465 l_asrv_rec.ATTRIBUTE4,
466 l_asrv_rec.ATTRIBUTE5,
467 l_asrv_rec.ATTRIBUTE6,
468 l_asrv_rec.ATTRIBUTE7,
469 l_asrv_rec.ATTRIBUTE8,
470 l_asrv_rec.ATTRIBUTE9,
471 l_asrv_rec.ATTRIBUTE10,
472 l_asrv_rec.ATTRIBUTE11,
473 l_asrv_rec.ATTRIBUTE12,
474 l_asrv_rec.ATTRIBUTE13,
475 l_asrv_rec.ATTRIBUTE14,
476 l_asrv_rec.ATTRIBUTE15,
477 l_asrv_rec.CREATED_BY,
478 l_asrv_rec.CREATION_DATE,
479 l_asrv_rec.LAST_UPDATED_BY,
480 l_asrv_rec.LAST_UPDATE_DATE,
481 l_asrv_rec.LAST_UPDATE_LOGIN;
482 x_no_data_found := okl_asrv_pk_csr%NOTFOUND;
483 CLOSE okl_asrv_pk_csr;
484 RETURN(l_asrv_rec);
485 END get_rec;
486
487 FUNCTION get_rec (
488 p_asrv_rec IN asrv_rec_type
489 ) RETURN asrv_rec_type IS
490 l_row_notfound BOOLEAN := TRUE;
491 BEGIN
492 RETURN(get_rec(p_asrv_rec, l_row_notfound));
493 END get_rec;
494
495 -----------------------------------------------------------
496 -- FUNCTION null_out_defaults for: OKL_ANSR_SET_CRTRIA_V --
497 -----------------------------------------------------------
498 FUNCTION null_out_defaults (
499 p_asrv_rec IN asrv_rec_type
500 ) RETURN asrv_rec_type IS
501 l_asrv_rec asrv_rec_type := p_asrv_rec;
502 BEGIN
503 IF (l_asrv_rec.object_version_number = Okl_Api.G_MISS_NUM) THEN
504 l_asrv_rec.object_version_number := NULL;
505 END IF;
506 IF (l_asrv_rec.sfwt_flag = okl_api.G_MISS_CHAR) THEN
507 l_asrv_rec.sfwt_flag := NULL;
508 END IF;
509 IF (l_asrv_rec.ant_id = okl_api.G_MISS_NUM) THEN
510 l_asrv_rec.ant_id := NULL;
511 END IF;
512 IF (l_asrv_rec.qcn_id = okl_api.G_MISS_NUM) THEN
513 l_asrv_rec.qcn_id := NULL;
514 END IF;
515 IF (l_asrv_rec.sort_sequence = okl_api.G_MISS_NUM) THEN
516 l_asrv_rec.sort_sequence := NULL;
517 END IF;
518 IF (l_asrv_rec.description = okl_api.G_MISS_CHAR) THEN
519 l_asrv_rec.description := NULL;
520 END IF;
521 IF (l_asrv_rec.attribute_category = okl_api.G_MISS_CHAR) THEN
522 l_asrv_rec.attribute_category := NULL;
523 END IF;
524 IF (l_asrv_rec.attribute1 = okl_api.G_MISS_CHAR) THEN
525 l_asrv_rec.attribute1 := NULL;
526 END IF;
527 IF (l_asrv_rec.attribute2 = okl_api.G_MISS_CHAR) THEN
528 l_asrv_rec.attribute2 := NULL;
529 END IF;
530 IF (l_asrv_rec.attribute3 = okl_api.G_MISS_CHAR) THEN
531 l_asrv_rec.attribute3 := NULL;
532 END IF;
533 IF (l_asrv_rec.attribute4 = okl_api.G_MISS_CHAR) THEN
534 l_asrv_rec.attribute4 := NULL;
535 END IF;
536 IF (l_asrv_rec.attribute5 = okl_api.G_MISS_CHAR) THEN
537 l_asrv_rec.attribute5 := NULL;
538 END IF;
539 IF (l_asrv_rec.attribute6 = okl_api.G_MISS_CHAR) THEN
540 l_asrv_rec.attribute6 := NULL;
541 END IF;
542 IF (l_asrv_rec.attribute7 = okl_api.G_MISS_CHAR) THEN
543 l_asrv_rec.attribute7 := NULL;
544 END IF;
545 IF (l_asrv_rec.attribute8 = okl_api.G_MISS_CHAR) THEN
546 l_asrv_rec.attribute8 := NULL;
547 END IF;
548 IF (l_asrv_rec.attribute9 = okl_api.G_MISS_CHAR) THEN
549 l_asrv_rec.attribute9 := NULL;
550 END IF;
551 IF (l_asrv_rec.attribute10 = okl_api.G_MISS_CHAR) THEN
552 l_asrv_rec.attribute10 := NULL;
553 END IF;
554 IF (l_asrv_rec.attribute11 = okl_api.G_MISS_CHAR) THEN
555 l_asrv_rec.attribute11 := NULL;
556 END IF;
557 IF (l_asrv_rec.attribute12 = okl_api.G_MISS_CHAR) THEN
558 l_asrv_rec.attribute12 := NULL;
559 END IF;
560 IF (l_asrv_rec.attribute13 = okl_api.G_MISS_CHAR) THEN
561 l_asrv_rec.attribute13 := NULL;
562 END IF;
563 IF (l_asrv_rec.attribute14 = okl_api.G_MISS_CHAR) THEN
564 l_asrv_rec.attribute14 := NULL;
565 END IF;
566 IF (l_asrv_rec.attribute15 = okl_api.G_MISS_CHAR) THEN
567 l_asrv_rec.attribute15 := NULL;
568 END IF;
569 IF (l_asrv_rec.created_by = okl_api.G_MISS_NUM) THEN
570 l_asrv_rec.created_by := NULL;
571 END IF;
572 IF (l_asrv_rec.creation_date = okl_api.G_MISS_DATE) THEN
573 l_asrv_rec.creation_date := NULL;
574 END IF;
575 IF (l_asrv_rec.last_updated_by = okl_api.G_MISS_NUM) THEN
576 l_asrv_rec.last_updated_by := NULL;
577 END IF;
578 IF (l_asrv_rec.last_update_date = okl_api.G_MISS_DATE) THEN
579 l_asrv_rec.last_update_date := NULL;
580 END IF;
581 IF (l_asrv_rec.last_update_login = okl_api.G_MISS_NUM) THEN
582 l_asrv_rec.last_update_login := NULL;
583 END IF;
584 RETURN(l_asrv_rec);
585 END null_out_defaults;
586 ---------------------------------------------------------------------------
587 -- PROCEDURE Validate_Attributes
588 ---------------------------------------------------------------------------
589 ---------------------------------------------------
590 -- Validate_Attributes for:OKL_ANSR_SET_CRTRIA_V --
591 ---------------------------------------------------
592 FUNCTION Validate_Attributes (
593 p_asrv_rec IN asrv_rec_type
594 ) RETURN VARCHAR2 IS
595 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
596 -- TAPI postgen 05/23/2001
597 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
598 -- End TAPI postgen 05/23/2001
599 BEGIN
600 -- TAPI postgen 05/23/2001
601 validate_id(p_asrv_rec, x_return_status);
602 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
603 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
604 l_return_status := x_return_status;
605 RAISE G_EXCEPTION_HALT_VALIDATION;
606 ELSE
607 l_return_status := x_return_status; -- record that there was an error
608 END IF;
609 END IF;
610
611 validate_object_version_number(p_asrv_rec, x_return_status);
612 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
613 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
614 l_return_status := x_return_status;
615 RAISE G_EXCEPTION_HALT_VALIDATION;
616 ELSE
617 l_return_status := x_return_status; -- record that there was an error
618 END IF;
619 END IF;
620
621 validate_ant_id(p_asrv_rec, x_return_status);
622 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
623 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
624 l_return_status := x_return_status;
625 RAISE G_EXCEPTION_HALT_VALIDATION;
626 ELSE
627 l_return_status := x_return_status; -- record that there was an error
628 END IF;
629 END IF;
630
631 validate_qcn_id(p_asrv_rec, x_return_status);
632 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
633 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
634 l_return_status := x_return_status;
635 RAISE G_EXCEPTION_HALT_VALIDATION;
636 ELSE
637 l_return_status := x_return_status; -- record that there was an error
638 END IF;
639 END IF;
640
641 validate_sort_sequence(p_asrv_rec, x_return_status);
642 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
643 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
644 l_return_status := x_return_status;
645 RAISE G_EXCEPTION_HALT_VALIDATION;
646 ELSE
647 l_return_status := x_return_status; -- record that there was an error
648 END IF;
649 END IF;
650 -- End TAPI postgen 05/23/2001
651
652 RETURN(l_return_status);
653 END Validate_Attributes;
654
655 ---------------------------------------------------------------------------
656 -- PROCEDURE Validate_Record
657 ---------------------------------------------------------------------------
658 -----------------------------------------------
659 -- Validate_Record for:OKL_ANSR_SET_CRTRIA_V --
660 -----------------------------------------------
661 FUNCTION Validate_Record (
662 p_asrv_rec IN asrv_rec_type
663 ) RETURN VARCHAR2 IS
664 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
665 BEGIN
666 RETURN (l_return_status);
667 END Validate_Record;
668
669 ---------------------------------------------------------------------------
670 -- PROCEDURE Migrate
671 ---------------------------------------------------------------------------
672 PROCEDURE migrate (
673 p_from IN asrv_rec_type,
674 p_to OUT NOCOPY asr_rec_type
675 ) IS
676 BEGIN
677 p_to.id := p_from.id;
678 p_to.ant_id := p_from.ant_id;
679 p_to.qcn_id := p_from.qcn_id;
680 p_to.sort_sequence := p_from.sort_sequence;
681 p_to.object_version_number := p_from.object_version_number;
682 p_to.attribute_category := p_from.attribute_category;
683 p_to.attribute1 := p_from.attribute1;
684 p_to.attribute2 := p_from.attribute2;
685 p_to.attribute3 := p_from.attribute3;
686 p_to.attribute4 := p_from.attribute4;
687 p_to.attribute5 := p_from.attribute5;
688 p_to.attribute6 := p_from.attribute6;
689 p_to.attribute7 := p_from.attribute7;
690 p_to.attribute8 := p_from.attribute8;
691 p_to.attribute9 := p_from.attribute9;
692 p_to.attribute10 := p_from.attribute10;
693 p_to.attribute11 := p_from.attribute11;
694 p_to.attribute12 := p_from.attribute12;
695 p_to.attribute13 := p_from.attribute13;
696 p_to.attribute14 := p_from.attribute14;
697 p_to.attribute15 := p_from.attribute15;
698 p_to.created_by := p_from.created_by;
699 p_to.creation_date := p_from.creation_date;
700 p_to.last_updated_by := p_from.last_updated_by;
701 p_to.last_update_date := p_from.last_update_date;
702 p_to.last_update_login := p_from.last_update_login;
703 END migrate;
704 PROCEDURE migrate (
705 p_from IN asr_rec_type,
706 p_to OUT NOCOPY asrv_rec_type
707 ) IS
708 BEGIN
709 p_to.id := p_from.id;
710 p_to.ant_id := p_from.ant_id;
711 p_to.qcn_id := p_from.qcn_id;
712 p_to.sort_sequence := p_from.sort_sequence;
713 p_to.object_version_number := p_from.object_version_number;
714 p_to.attribute_category := p_from.attribute_category;
715 p_to.attribute1 := p_from.attribute1;
716 p_to.attribute2 := p_from.attribute2;
717 p_to.attribute3 := p_from.attribute3;
718 p_to.attribute4 := p_from.attribute4;
719 p_to.attribute5 := p_from.attribute5;
720 p_to.attribute6 := p_from.attribute6;
721 p_to.attribute7 := p_from.attribute7;
722 p_to.attribute8 := p_from.attribute8;
723 p_to.attribute9 := p_from.attribute9;
724 p_to.attribute10 := p_from.attribute10;
725 p_to.attribute11 := p_from.attribute11;
726 p_to.attribute12 := p_from.attribute12;
727 p_to.attribute13 := p_from.attribute13;
728 p_to.attribute14 := p_from.attribute14;
729 p_to.attribute15 := p_from.attribute15;
730 p_to.created_by := p_from.created_by;
731 p_to.creation_date := p_from.creation_date;
732 p_to.last_updated_by := p_from.last_updated_by;
733 p_to.last_update_date := p_from.last_update_date;
734 p_to.last_update_login := p_from.last_update_login;
735 END migrate;
736 PROCEDURE migrate (
737 p_from IN asrv_rec_type,
738 p_to OUT NOCOPY OklAnsrSetCrtriaTlRecType
739 ) IS
740 BEGIN
741 p_to.id := p_from.id;
742 p_to.sfwt_flag := p_from.sfwt_flag;
743 p_to.description := p_from.description;
744 p_to.created_by := p_from.created_by;
745 p_to.creation_date := p_from.creation_date;
746 p_to.last_updated_by := p_from.last_updated_by;
747 p_to.last_update_date := p_from.last_update_date;
748 p_to.last_update_login := p_from.last_update_login;
749 END migrate;
750 PROCEDURE migrate (
751 p_from IN OklAnsrSetCrtriaTlRecType,
752 p_to OUT NOCOPY asrv_rec_type
753 ) IS
754 BEGIN
755 p_to.id := p_from.id;
756 p_to.sfwt_flag := p_from.sfwt_flag;
757 p_to.description := p_from.description;
758 p_to.created_by := p_from.created_by;
759 p_to.creation_date := p_from.creation_date;
760 p_to.last_updated_by := p_from.last_updated_by;
761 p_to.last_update_date := p_from.last_update_date;
762 p_to.last_update_login := p_from.last_update_login;
763 END migrate;
764
765 ---------------------------------------------------------------------------
766 -- PROCEDURE validate_row
767 ---------------------------------------------------------------------------
768 --------------------------------------------
769 -- validate_row for:OKL_ANSR_SET_CRTRIA_V --
770 --------------------------------------------
771 PROCEDURE validate_row(
772 p_api_version IN NUMBER,
773 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
774 x_return_status OUT NOCOPY VARCHAR2,
775 x_msg_count OUT NOCOPY NUMBER,
776 x_msg_data OUT NOCOPY VARCHAR2,
777 p_asrv_rec IN asrv_rec_type) IS
778
779 l_api_version CONSTANT NUMBER := 1;
780 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
781 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
782 l_asrv_rec asrv_rec_type := p_asrv_rec;
783 l_asr_rec asr_rec_type;
784 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
785 BEGIN
786 l_return_status := okl_api.START_ACTIVITY(l_api_name,
787 G_PKG_NAME,
788 p_init_msg_list,
789 l_api_version,
790 p_api_version,
791 '_PVT',
792 x_return_status);
793 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
794 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
795 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
796 RAISE okl_api.G_EXCEPTION_ERROR;
797 END IF;
798 --- Validate all non-missing attributes (Item Level Validation)
799 l_return_status := Validate_Attributes(l_asrv_rec);
800 --- If any errors happen abort API
801 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
802 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
803 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
804 RAISE okl_api.G_EXCEPTION_ERROR;
805 END IF;
806 l_return_status := Validate_Record(l_asrv_rec);
807 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
808 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
809 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
810 RAISE okl_api.G_EXCEPTION_ERROR;
811 END IF;
812 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
813 EXCEPTION
814 WHEN okl_api.G_EXCEPTION_ERROR THEN
815 x_return_status := okl_api.HANDLE_EXCEPTIONS
816 (
817 l_api_name,
818 G_PKG_NAME,
819 'okl_api.G_RET_STS_ERROR',
820 x_msg_count,
821 x_msg_data,
822 '_PVT'
823 );
824 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
825 x_return_status :=okl_api.HANDLE_EXCEPTIONS
826 (
827 l_api_name,
828 G_PKG_NAME,
829 'okl_api.G_RET_STS_UNEXP_ERROR',
830 x_msg_count,
831 x_msg_data,
832 '_PVT'
833 );
834 WHEN OTHERS THEN
835 x_return_status :=okl_api.HANDLE_EXCEPTIONS
836 (
837 l_api_name,
838 G_PKG_NAME,
839 'OTHERS',
840 x_msg_count,
841 x_msg_data,
842 '_PVT'
843 );
844 END validate_row;
845 ------------------------------------------
846 -- PL/SQL TBL validate_row for:ASRV_TBL --
847 ------------------------------------------
848 PROCEDURE validate_row(
849 p_api_version IN NUMBER,
850 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
851 x_return_status OUT NOCOPY VARCHAR2,
852 x_msg_count OUT NOCOPY NUMBER,
853 x_msg_data OUT NOCOPY VARCHAR2,
854 p_asrv_tbl IN asrv_tbl_type) IS
855
856 l_api_version CONSTANT NUMBER := 1;
857 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
858 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
859 i NUMBER := 0;
860 BEGIN
861 okl_api.init_msg_list(p_init_msg_list);
862 -- Make sure PL/SQL table has records in it before passing
863 IF (p_asrv_tbl.COUNT > 0) THEN
864 i := p_asrv_tbl.FIRST;
865 LOOP
866 validate_row (
867 p_api_version => p_api_version,
868 p_init_msg_list => okl_api.G_FALSE,
869 x_return_status => x_return_status,
870 x_msg_count => x_msg_count,
871 x_msg_data => x_msg_data,
872 p_asrv_rec => p_asrv_tbl(i));
873 EXIT WHEN (i = p_asrv_tbl.LAST);
874 i := p_asrv_tbl.NEXT(i);
875 END LOOP;
876 END IF;
877 EXCEPTION
878 WHEN okl_api.G_EXCEPTION_ERROR THEN
879 x_return_status := okl_api.HANDLE_EXCEPTIONS
880 (
881 l_api_name,
882 G_PKG_NAME,
883 'okl_api.G_RET_STS_ERROR',
884 x_msg_count,
885 x_msg_data,
886 '_PVT'
887 );
888 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
889 x_return_status :=okl_api.HANDLE_EXCEPTIONS
890 (
891 l_api_name,
892 G_PKG_NAME,
893 'okl_api.G_RET_STS_UNEXP_ERROR',
894 x_msg_count,
895 x_msg_data,
896 '_PVT'
897 );
898 WHEN OTHERS THEN
899 x_return_status :=okl_api.HANDLE_EXCEPTIONS
900 (
901 l_api_name,
902 G_PKG_NAME,
903 'OTHERS',
904 x_msg_count,
905 x_msg_data,
906 '_PVT'
907 );
908 END validate_row;
909
910 ---------------------------------------------------------------------------
911 -- PROCEDURE insert_row
912 ---------------------------------------------------------------------------
913 ------------------------------------------
914 -- insert_row for:OKL_ANSR_SET_CRTRIA_B --
915 ------------------------------------------
916 PROCEDURE insert_row(
917 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
918 x_return_status OUT NOCOPY VARCHAR2,
919 x_msg_count OUT NOCOPY NUMBER,
920 x_msg_data OUT NOCOPY VARCHAR2,
921 p_asr_rec IN asr_rec_type,
922 x_asr_rec OUT NOCOPY asr_rec_type) IS
923
924 l_api_version CONSTANT NUMBER := 1;
925 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
926 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
927 l_asr_rec asr_rec_type := p_asr_rec;
928 l_def_asr_rec asr_rec_type;
929 ----------------------------------------------
930 -- Set_Attributes for:OKL_ANSR_SET_CRTRIA_B --
931 ----------------------------------------------
932 FUNCTION Set_Attributes (
933 p_asr_rec IN asr_rec_type,
934 x_asr_rec OUT NOCOPY asr_rec_type
935 ) RETURN VARCHAR2 IS
936 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
937 BEGIN
938 x_asr_rec := p_asr_rec;
939 RETURN(l_return_status);
940 END Set_Attributes;
941 BEGIN
942 l_return_status := okl_api.START_ACTIVITY(l_api_name,
943 p_init_msg_list,
944 '_PVT',
945 x_return_status);
946 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
947 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
948 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
949 RAISE okl_api.G_EXCEPTION_ERROR;
950 END IF;
951 --- Setting item attributes
952 l_return_status := Set_Attributes(
953 p_asr_rec, -- IN
954 l_asr_rec); -- OUT
955 --- If any errors happen abort API
956 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
957 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
958 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
959 RAISE okl_api.G_EXCEPTION_ERROR;
960 END IF;
961 INSERT INTO OKL_ANSR_SET_CRTRIA_B(
962 id,
963 ant_id,
964 qcn_id,
965 sort_sequence,
966 object_version_number,
967 attribute_category,
968 attribute1,
969 attribute2,
970 attribute3,
971 attribute4,
972 attribute5,
973 attribute6,
974 attribute7,
975 attribute8,
976 attribute9,
977 attribute10,
978 attribute11,
979 attribute12,
980 attribute13,
981 attribute14,
982 attribute15,
983 created_by,
984 creation_date,
985 last_updated_by,
986 last_update_date,
987 last_update_login)
988 VALUES (
989 l_asr_rec.id,
990 l_asr_rec.ant_id,
991 l_asr_rec.qcn_id,
992 l_asr_rec.sort_sequence,
993 l_asr_rec.object_version_number,
994 l_asr_rec.attribute_category,
995 l_asr_rec.attribute1,
996 l_asr_rec.attribute2,
997 l_asr_rec.attribute3,
998 l_asr_rec.attribute4,
999 l_asr_rec.attribute5,
1000 l_asr_rec.attribute6,
1001 l_asr_rec.attribute7,
1002 l_asr_rec.attribute8,
1003 l_asr_rec.attribute9,
1004 l_asr_rec.attribute10,
1005 l_asr_rec.attribute11,
1006 l_asr_rec.attribute12,
1007 l_asr_rec.attribute13,
1008 l_asr_rec.attribute14,
1009 l_asr_rec.attribute15,
1010 l_asr_rec.created_by,
1011 l_asr_rec.creation_date,
1012 l_asr_rec.last_updated_by,
1013 l_asr_rec.last_update_date,
1014 l_asr_rec.last_update_login);
1015 -- Set OUT values
1016 x_asr_rec := l_asr_rec;
1017 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1018 EXCEPTION
1019 WHEN okl_api.G_EXCEPTION_ERROR THEN
1020 x_return_status := okl_api.HANDLE_EXCEPTIONS
1021 (
1022 l_api_name,
1023 G_PKG_NAME,
1024 'okl_api.G_RET_STS_ERROR',
1025 x_msg_count,
1026 x_msg_data,
1027 '_PVT'
1028 );
1029 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1030 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1031 (
1032 l_api_name,
1033 G_PKG_NAME,
1034 'okl_api.G_RET_STS_UNEXP_ERROR',
1035 x_msg_count,
1036 x_msg_data,
1037 '_PVT'
1038 );
1039 WHEN OTHERS THEN
1040 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1041 (
1042 l_api_name,
1043 G_PKG_NAME,
1044 'OTHERS',
1045 x_msg_count,
1046 x_msg_data,
1047 '_PVT'
1048 );
1049 END insert_row;
1050 -------------------------------------------
1051 -- insert_row for:OKL_ANSR_SET_CRTRIA_TL --
1052 -------------------------------------------
1053 PROCEDURE insert_row(
1054 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1055 x_return_status OUT NOCOPY VARCHAR2,
1056 x_msg_count OUT NOCOPY NUMBER,
1057 x_msg_data OUT NOCOPY VARCHAR2,
1058 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType,
1059 x_okl_ansr_set_crtria_tl_rec OUT NOCOPY OklAnsrSetCrtriaTlRecType) IS
1060
1061 l_api_version CONSTANT NUMBER := 1;
1062 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1063 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1064 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType := p_okl_ansr_set_crtria_tl_rec;
1065 ldefoklansrsetcrtriatlrec OklAnsrSetCrtriaTlRecType;
1066 CURSOR get_languages IS
1067 SELECT *
1068 FROM FND_LANGUAGES
1069 WHERE INSTALLED_FLAG IN ('I', 'B');
1070 -----------------------------------------------
1071 -- Set_Attributes for:OKL_ANSR_SET_CRTRIA_TL --
1072 -----------------------------------------------
1073 FUNCTION Set_Attributes (
1074 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType,
1075 x_okl_ansr_set_crtria_tl_rec OUT NOCOPY OklAnsrSetCrtriaTlRecType
1076 ) RETURN VARCHAR2 IS
1077 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1078 BEGIN
1079 x_okl_ansr_set_crtria_tl_rec := p_okl_ansr_set_crtria_tl_rec;
1080 x_okl_ansr_set_crtria_tl_rec.LANGUAGE := USERENV('LANG');
1081 x_okl_ansr_set_crtria_tl_rec.SOURCE_LANG := USERENV('LANG');
1082 RETURN(l_return_status);
1083 END Set_Attributes;
1084 BEGIN
1085 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1086 p_init_msg_list,
1087 '_PVT',
1088 x_return_status);
1089 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1090 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1091 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1092 RAISE okl_api.G_EXCEPTION_ERROR;
1093 END IF;
1094 --- Setting item attributes
1095 l_return_status := Set_Attributes(
1096 p_okl_ansr_set_crtria_tl_rec, -- IN
1097 l_okl_ansr_set_crtria_tl_rec); -- OUT
1098 --- If any errors happen abort API
1099 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1100 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1101 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1102 RAISE okl_api.G_EXCEPTION_ERROR;
1103 END IF;
1104 FOR l_lang_rec IN get_languages LOOP
1105 l_okl_ansr_set_crtria_tl_rec.LANGUAGE := l_lang_rec.language_code;
1106 INSERT INTO OKL_ANSR_SET_CRTRIA_TL(
1107 id,
1108 LANGUAGE,
1109 source_lang,
1110 sfwt_flag,
1111 description,
1112 created_by,
1113 creation_date,
1114 last_updated_by,
1115 last_update_date,
1116 last_update_login)
1117 VALUES (
1118 l_okl_ansr_set_crtria_tl_rec.id,
1119 l_okl_ansr_set_crtria_tl_rec.LANGUAGE,
1120 l_okl_ansr_set_crtria_tl_rec.source_lang,
1121 l_okl_ansr_set_crtria_tl_rec.sfwt_flag,
1122 l_okl_ansr_set_crtria_tl_rec.description,
1123 l_okl_ansr_set_crtria_tl_rec.created_by,
1124 l_okl_ansr_set_crtria_tl_rec.creation_date,
1125 l_okl_ansr_set_crtria_tl_rec.last_updated_by,
1126 l_okl_ansr_set_crtria_tl_rec.last_update_date,
1127 l_okl_ansr_set_crtria_tl_rec.last_update_login);
1128 END LOOP;
1129 -- Set OUT values
1130 x_okl_ansr_set_crtria_tl_rec := l_okl_ansr_set_crtria_tl_rec;
1131 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1132 EXCEPTION
1133 WHEN okl_api.G_EXCEPTION_ERROR THEN
1134 x_return_status := okl_api.HANDLE_EXCEPTIONS
1135 (
1136 l_api_name,
1137 G_PKG_NAME,
1138 'okl_api.G_RET_STS_ERROR',
1139 x_msg_count,
1140 x_msg_data,
1141 '_PVT'
1142 );
1143 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1144 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1145 (
1146 l_api_name,
1147 G_PKG_NAME,
1148 'okl_api.G_RET_STS_UNEXP_ERROR',
1149 x_msg_count,
1150 x_msg_data,
1151 '_PVT'
1152 );
1153 WHEN OTHERS THEN
1154 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1155 (
1156 l_api_name,
1157 G_PKG_NAME,
1158 'OTHERS',
1159 x_msg_count,
1160 x_msg_data,
1161 '_PVT'
1162 );
1163 END insert_row;
1164 ------------------------------------------
1165 -- insert_row for:OKL_ANSR_SET_CRTRIA_V --
1166 ------------------------------------------
1167 PROCEDURE insert_row(
1168 p_api_version IN NUMBER,
1169 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1170 x_return_status OUT NOCOPY VARCHAR2,
1171 x_msg_count OUT NOCOPY NUMBER,
1172 x_msg_data OUT NOCOPY VARCHAR2,
1173 p_asrv_rec IN asrv_rec_type,
1174 x_asrv_rec OUT NOCOPY asrv_rec_type) IS
1175
1176 l_api_version CONSTANT NUMBER := 1;
1177 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1178 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1179 l_asrv_rec asrv_rec_type;
1180 l_def_asrv_rec asrv_rec_type;
1181 l_asr_rec asr_rec_type;
1182 lx_asr_rec asr_rec_type;
1183 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
1184 lx_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
1185 -------------------------------
1186 -- FUNCTION fill_who_columns --
1187 -------------------------------
1188 FUNCTION fill_who_columns (
1189 p_asrv_rec IN asrv_rec_type
1190 ) RETURN asrv_rec_type IS
1191 l_asrv_rec asrv_rec_type := p_asrv_rec;
1192 BEGIN
1193 l_asrv_rec.CREATION_DATE := SYSDATE;
1194 l_asrv_rec.CREATED_BY := Fnd_Global.USER_ID;
1195 l_asrv_rec.LAST_UPDATE_DATE := SYSDATE;
1196 l_asrv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1197 l_asrv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1198 RETURN(l_asrv_rec);
1199 END fill_who_columns;
1200 ----------------------------------------------
1201 -- Set_Attributes for:OKL_ANSR_SET_CRTRIA_V --
1202 ----------------------------------------------
1203 FUNCTION Set_Attributes (
1204 p_asrv_rec IN asrv_rec_type,
1205 x_asrv_rec OUT NOCOPY asrv_rec_type
1206 ) RETURN VARCHAR2 IS
1207 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1208 BEGIN
1209 x_asrv_rec := p_asrv_rec;
1210 x_asrv_rec.OBJECT_VERSION_NUMBER := 1;
1211 x_asrv_rec.SFWT_FLAG := 'N';
1212 RETURN(l_return_status);
1213 END Set_Attributes;
1214 BEGIN
1215 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1216 G_PKG_NAME,
1217 p_init_msg_list,
1218 l_api_version,
1219 p_api_version,
1220 '_PVT',
1221 x_return_status);
1222 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1223 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1224 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1225 RAISE okl_api.G_EXCEPTION_ERROR;
1226 END IF;
1227 l_asrv_rec := null_out_defaults(p_asrv_rec);
1228 -- Set primary key value
1229 l_asrv_rec.ID := get_seq_id;
1230 --- Setting item attributes
1231 l_return_status := Set_Attributes(
1232 l_asrv_rec, -- IN
1233 l_def_asrv_rec); -- OUT
1234 --- If any errors happen abort API
1235 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1236 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1237 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1238 RAISE okl_api.G_EXCEPTION_ERROR;
1239 END IF;
1240 l_def_asrv_rec := fill_who_columns(l_def_asrv_rec);
1241 --- Validate all non-missing attributes (Item Level Validation)
1242 l_return_status := Validate_Attributes(l_def_asrv_rec);
1243 --- If any errors happen abort API
1244 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1245 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1246 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1247 RAISE okl_api.G_EXCEPTION_ERROR;
1248 END IF;
1249 l_return_status := Validate_Record(l_def_asrv_rec);
1250 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1251 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1252 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1253 RAISE okl_api.G_EXCEPTION_ERROR;
1254 END IF;
1255 --------------------------------------
1256 -- Move VIEW record to "Child" records
1257 --------------------------------------
1258 migrate(l_def_asrv_rec, l_asr_rec);
1259 migrate(l_def_asrv_rec, l_okl_ansr_set_crtria_tl_rec);
1260 --------------------------------------------
1261 -- Call the INSERT_ROW for each child record
1262 --------------------------------------------
1263 insert_row(
1264 p_init_msg_list,
1265 x_return_status,
1266 x_msg_count,
1267 x_msg_data,
1268 l_asr_rec,
1269 lx_asr_rec
1270 );
1271 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1272 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1273 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1274 RAISE okl_api.G_EXCEPTION_ERROR;
1275 END IF;
1276 migrate(lx_asr_rec, l_def_asrv_rec);
1277 insert_row(
1278 p_init_msg_list,
1279 x_return_status,
1280 x_msg_count,
1281 x_msg_data,
1282 l_okl_ansr_set_crtria_tl_rec,
1283 lx_okl_ansr_set_crtria_tl_rec
1284 );
1285 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1286 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1287 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1288 RAISE okl_api.G_EXCEPTION_ERROR;
1289 END IF;
1290 migrate(lx_okl_ansr_set_crtria_tl_rec, l_def_asrv_rec);
1291 -- Set OUT values
1292 x_asrv_rec := l_def_asrv_rec;
1293 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1294 EXCEPTION
1295 WHEN okl_api.G_EXCEPTION_ERROR THEN
1296 x_return_status := okl_api.HANDLE_EXCEPTIONS
1297 (
1298 l_api_name,
1299 G_PKG_NAME,
1300 'okl_api.G_RET_STS_ERROR',
1301 x_msg_count,
1302 x_msg_data,
1303 '_PVT'
1304 );
1305 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1306 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1307 (
1308 l_api_name,
1309 G_PKG_NAME,
1310 'okl_api.G_RET_STS_UNEXP_ERROR',
1311 x_msg_count,
1312 x_msg_data,
1313 '_PVT'
1314 );
1315 WHEN OTHERS THEN
1316 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1317 (
1318 l_api_name,
1319 G_PKG_NAME,
1320 'OTHERS',
1321 x_msg_count,
1322 x_msg_data,
1323 '_PVT'
1324 );
1325 END insert_row;
1326 ----------------------------------------
1327 -- PL/SQL TBL insert_row for:ASRV_TBL --
1328 ----------------------------------------
1329 PROCEDURE insert_row(
1330 p_api_version IN NUMBER,
1331 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1332 x_return_status OUT NOCOPY VARCHAR2,
1333 x_msg_count OUT NOCOPY NUMBER,
1334 x_msg_data OUT NOCOPY VARCHAR2,
1335 p_asrv_tbl IN asrv_tbl_type,
1336 x_asrv_tbl OUT NOCOPY asrv_tbl_type) IS
1337
1338 l_api_version CONSTANT NUMBER := 1;
1339 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1340 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1341 i NUMBER := 0;
1342 BEGIN
1343 okl_api.init_msg_list(p_init_msg_list);
1344 -- Make sure PL/SQL table has records in it before passing
1345 IF (p_asrv_tbl.COUNT > 0) THEN
1346 i := p_asrv_tbl.FIRST;
1347 LOOP
1348 insert_row (
1349 p_api_version => p_api_version,
1350 p_init_msg_list => okl_api.G_FALSE,
1351 x_return_status => x_return_status,
1352 x_msg_count => x_msg_count,
1353 x_msg_data => x_msg_data,
1354 p_asrv_rec => p_asrv_tbl(i),
1355 x_asrv_rec => x_asrv_tbl(i));
1356 EXIT WHEN (i = p_asrv_tbl.LAST);
1357 i := p_asrv_tbl.NEXT(i);
1358 END LOOP;
1359 END IF;
1360 EXCEPTION
1361 WHEN okl_api.G_EXCEPTION_ERROR THEN
1362 x_return_status := okl_api.HANDLE_EXCEPTIONS
1363 (
1364 l_api_name,
1365 G_PKG_NAME,
1366 'okl_api.G_RET_STS_ERROR',
1367 x_msg_count,
1368 x_msg_data,
1369 '_PVT'
1370 );
1371 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1372 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1373 (
1374 l_api_name,
1375 G_PKG_NAME,
1376 'okl_api.G_RET_STS_UNEXP_ERROR',
1377 x_msg_count,
1378 x_msg_data,
1379 '_PVT'
1380 );
1381 WHEN OTHERS THEN
1382 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1383 (
1384 l_api_name,
1385 G_PKG_NAME,
1386 'OTHERS',
1387 x_msg_count,
1388 x_msg_data,
1389 '_PVT'
1390 );
1391 END insert_row;
1392
1393 ---------------------------------------------------------------------------
1394 -- PROCEDURE lock_row
1395 ---------------------------------------------------------------------------
1396 ----------------------------------------
1397 -- lock_row for:OKL_ANSR_SET_CRTRIA_B --
1398 ----------------------------------------
1399 PROCEDURE lock_row(
1400 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 p_asr_rec IN asr_rec_type) IS
1405
1406 E_Resource_Busy EXCEPTION;
1407 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1408 CURSOR lock_csr (p_asr_rec IN asr_rec_type) IS
1409 SELECT OBJECT_VERSION_NUMBER
1410 FROM OKL_ANSR_SET_CRTRIA_B
1411 WHERE ID = p_asr_rec.id
1412 AND OBJECT_VERSION_NUMBER = p_asr_rec.object_version_number
1413 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1414
1415 CURSOR lchk_csr (p_asr_rec IN asr_rec_type) IS
1416 SELECT OBJECT_VERSION_NUMBER
1417 FROM OKL_ANSR_SET_CRTRIA_B
1418 WHERE ID = p_asr_rec.id;
1419 l_api_version CONSTANT NUMBER := 1;
1420 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1421 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1422 l_object_version_number OKL_ANSR_SET_CRTRIA_B.OBJECT_VERSION_NUMBER%TYPE;
1423 lc_object_version_number OKL_ANSR_SET_CRTRIA_B.OBJECT_VERSION_NUMBER%TYPE;
1424 l_row_notfound BOOLEAN := FALSE;
1425 lc_row_notfound BOOLEAN := FALSE;
1426 BEGIN
1427 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1428 p_init_msg_list,
1429 '_PVT',
1430 x_return_status);
1431 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1432 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1433 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1434 RAISE okl_api.G_EXCEPTION_ERROR;
1435 END IF;
1436 BEGIN
1437 OPEN lock_csr(p_asr_rec);
1438 FETCH lock_csr INTO l_object_version_number;
1439 l_row_notfound := lock_csr%NOTFOUND;
1440 CLOSE lock_csr;
1441 EXCEPTION
1442 WHEN E_Resource_Busy THEN
1443 IF (lock_csr%ISOPEN) THEN
1444 CLOSE lock_csr;
1445 END IF;
1446 okl_api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1447 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
1448 END;
1449
1450 IF ( l_row_notfound ) THEN
1451 OPEN lchk_csr(p_asr_rec);
1452 FETCH lchk_csr INTO lc_object_version_number;
1453 lc_row_notfound := lchk_csr%NOTFOUND;
1454 CLOSE lchk_csr;
1455 END IF;
1456 IF (lc_row_notfound) THEN
1457 okl_api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1458 RAISE okl_api.G_EXCEPTION_ERROR;
1459 ELSIF lc_object_version_number > p_asr_rec.object_version_number THEN
1460 okl_api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1461 RAISE okl_api.G_EXCEPTION_ERROR;
1462 ELSIF lc_object_version_number <> p_asr_rec.object_version_number THEN
1463 okl_api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1464 RAISE okl_api.G_EXCEPTION_ERROR;
1465 ELSIF lc_object_version_number = -1 THEN
1466 okl_api.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1467 RAISE okl_api.G_EXCEPTION_ERROR;
1468 END IF;
1469 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1470 EXCEPTION
1471 WHEN okl_api.G_EXCEPTION_ERROR THEN
1472 x_return_status := okl_api.HANDLE_EXCEPTIONS
1473 (
1474 l_api_name,
1475 G_PKG_NAME,
1476 'okl_api.G_RET_STS_ERROR',
1477 x_msg_count,
1478 x_msg_data,
1479 '_PVT'
1480 );
1481 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1482 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1483 (
1484 l_api_name,
1485 G_PKG_NAME,
1486 'okl_api.G_RET_STS_UNEXP_ERROR',
1487 x_msg_count,
1488 x_msg_data,
1489 '_PVT'
1490 );
1491 WHEN OTHERS THEN
1492 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1493 (
1494 l_api_name,
1495 G_PKG_NAME,
1496 'OTHERS',
1497 x_msg_count,
1498 x_msg_data,
1499 '_PVT'
1500 );
1501 END lock_row;
1502 -----------------------------------------
1503 -- lock_row for:OKL_ANSR_SET_CRTRIA_TL --
1504 -----------------------------------------
1505 PROCEDURE lock_row(
1506 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1507 x_return_status OUT NOCOPY VARCHAR2,
1508 x_msg_count OUT NOCOPY NUMBER,
1509 x_msg_data OUT NOCOPY VARCHAR2,
1510 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType) IS
1511
1512 E_Resource_Busy EXCEPTION;
1513 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1514 CURSOR lock_csr (p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType) IS
1515 SELECT *
1516 FROM OKL_ANSR_SET_CRTRIA_TL
1517 WHERE ID = p_okl_ansr_set_crtria_tl_rec.id
1518 FOR UPDATE NOWAIT;
1519
1520 l_api_version CONSTANT NUMBER := 1;
1521 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1522 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1523 l_lock_var lock_csr%ROWTYPE;
1524 l_row_notfound BOOLEAN := FALSE;
1525 lc_row_notfound BOOLEAN := FALSE;
1526 BEGIN
1527 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1528 p_init_msg_list,
1529 '_PVT',
1530 x_return_status);
1531 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1532 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1533 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1534 RAISE okl_api.G_EXCEPTION_ERROR;
1535 END IF;
1536 BEGIN
1537 OPEN lock_csr(p_okl_ansr_set_crtria_tl_rec);
1538 FETCH lock_csr INTO l_lock_var;
1539 l_row_notfound := lock_csr%NOTFOUND;
1540 CLOSE lock_csr;
1541 EXCEPTION
1542 WHEN E_Resource_Busy THEN
1543 IF (lock_csr%ISOPEN) THEN
1544 CLOSE lock_csr;
1545 END IF;
1546 okl_api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1547 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
1548 END;
1549
1550 IF ( l_row_notfound ) THEN
1551 okl_api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1552 RAISE okl_api.G_EXCEPTION_ERROR;
1553 END IF;
1554 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1555 EXCEPTION
1556 WHEN okl_api.G_EXCEPTION_ERROR THEN
1557 x_return_status := okl_api.HANDLE_EXCEPTIONS
1558 (
1559 l_api_name,
1560 G_PKG_NAME,
1561 'okl_api.G_RET_STS_ERROR',
1562 x_msg_count,
1563 x_msg_data,
1564 '_PVT'
1565 );
1566 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1567 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1568 (
1569 l_api_name,
1570 G_PKG_NAME,
1571 'okl_api.G_RET_STS_UNEXP_ERROR',
1572 x_msg_count,
1573 x_msg_data,
1574 '_PVT'
1575 );
1576 WHEN OTHERS THEN
1577 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1578 (
1579 l_api_name,
1580 G_PKG_NAME,
1581 'OTHERS',
1582 x_msg_count,
1583 x_msg_data,
1584 '_PVT'
1585 );
1586 END lock_row;
1587 ----------------------------------------
1588 -- lock_row for:OKL_ANSR_SET_CRTRIA_V --
1589 ----------------------------------------
1590 PROCEDURE lock_row(
1591 p_api_version IN NUMBER,
1592 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1593 x_return_status OUT NOCOPY VARCHAR2,
1594 x_msg_count OUT NOCOPY NUMBER,
1595 x_msg_data OUT NOCOPY VARCHAR2,
1596 p_asrv_rec IN asrv_rec_type) IS
1597
1598 l_api_version CONSTANT NUMBER := 1;
1599 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1600 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1601 l_asr_rec asr_rec_type;
1602 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
1603 BEGIN
1604 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1605 G_PKG_NAME,
1606 p_init_msg_list,
1607 l_api_version,
1608 p_api_version,
1609 '_PVT',
1610 x_return_status);
1611 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1612 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1613 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1614 RAISE okl_api.G_EXCEPTION_ERROR;
1615 END IF;
1616 --------------------------------------
1617 -- Move VIEW record to "Child" records
1618 --------------------------------------
1619 migrate(p_asrv_rec, l_asr_rec);
1620 migrate(p_asrv_rec, l_okl_ansr_set_crtria_tl_rec);
1621 --------------------------------------------
1622 -- Call the LOCK_ROW for each child record
1623 --------------------------------------------
1624 lock_row(
1625 p_init_msg_list,
1626 x_return_status,
1627 x_msg_count,
1628 x_msg_data,
1629 l_asr_rec
1630 );
1631 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1632 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1633 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1634 RAISE okl_api.G_EXCEPTION_ERROR;
1635 END IF;
1636 lock_row(
1637 p_init_msg_list,
1638 x_return_status,
1639 x_msg_count,
1640 x_msg_data,
1641 l_okl_ansr_set_crtria_tl_rec
1642 );
1643 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1644 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1645 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1646 RAISE okl_api.G_EXCEPTION_ERROR;
1647 END IF;
1648 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1649 EXCEPTION
1650 WHEN okl_api.G_EXCEPTION_ERROR THEN
1651 x_return_status := okl_api.HANDLE_EXCEPTIONS
1652 (
1653 l_api_name,
1654 G_PKG_NAME,
1655 'okl_api.G_RET_STS_ERROR',
1656 x_msg_count,
1657 x_msg_data,
1658 '_PVT'
1659 );
1660 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1661 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1662 (
1663 l_api_name,
1664 G_PKG_NAME,
1665 'okl_api.G_RET_STS_UNEXP_ERROR',
1666 x_msg_count,
1667 x_msg_data,
1668 '_PVT'
1669 );
1670 WHEN OTHERS THEN
1671 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1672 (
1673 l_api_name,
1674 G_PKG_NAME,
1675 'OTHERS',
1676 x_msg_count,
1677 x_msg_data,
1678 '_PVT'
1679 );
1680 END lock_row;
1681 --------------------------------------
1682 -- PL/SQL TBL lock_row for:ASRV_TBL --
1683 --------------------------------------
1684 PROCEDURE lock_row(
1685 p_api_version IN NUMBER,
1686 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1687 x_return_status OUT NOCOPY VARCHAR2,
1688 x_msg_count OUT NOCOPY NUMBER,
1689 x_msg_data OUT NOCOPY VARCHAR2,
1690 p_asrv_tbl IN asrv_tbl_type) IS
1691
1692 l_api_version CONSTANT NUMBER := 1;
1693 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1694 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1695 i NUMBER := 0;
1696 BEGIN
1697 okl_api.init_msg_list(p_init_msg_list);
1698 -- Make sure PL/SQL table has records in it before passing
1699 IF (p_asrv_tbl.COUNT > 0) THEN
1700 i := p_asrv_tbl.FIRST;
1701 LOOP
1702 lock_row (
1703 p_api_version => p_api_version,
1704 p_init_msg_list => okl_api.G_FALSE,
1705 x_return_status => x_return_status,
1706 x_msg_count => x_msg_count,
1707 x_msg_data => x_msg_data,
1708 p_asrv_rec => p_asrv_tbl(i));
1709 EXIT WHEN (i = p_asrv_tbl.LAST);
1710 i := p_asrv_tbl.NEXT(i);
1711 END LOOP;
1712 END IF;
1713 EXCEPTION
1714 WHEN okl_api.G_EXCEPTION_ERROR THEN
1715 x_return_status := okl_api.HANDLE_EXCEPTIONS
1716 (
1717 l_api_name,
1718 G_PKG_NAME,
1719 'okl_api.G_RET_STS_ERROR',
1720 x_msg_count,
1721 x_msg_data,
1722 '_PVT'
1723 );
1724 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1725 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1726 (
1727 l_api_name,
1728 G_PKG_NAME,
1729 'okl_api.G_RET_STS_UNEXP_ERROR',
1730 x_msg_count,
1731 x_msg_data,
1732 '_PVT'
1733 );
1734 WHEN OTHERS THEN
1735 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1736 (
1737 l_api_name,
1738 G_PKG_NAME,
1739 'OTHERS',
1740 x_msg_count,
1741 x_msg_data,
1742 '_PVT'
1743 );
1744 END lock_row;
1745
1746 ---------------------------------------------------------------------------
1747 -- PROCEDURE update_row
1748 ---------------------------------------------------------------------------
1749 ------------------------------------------
1750 -- update_row for:OKL_ANSR_SET_CRTRIA_B --
1751 ------------------------------------------
1752 PROCEDURE update_row(
1753 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1754 x_return_status OUT NOCOPY VARCHAR2,
1755 x_msg_count OUT NOCOPY NUMBER,
1756 x_msg_data OUT NOCOPY VARCHAR2,
1757 p_asr_rec IN asr_rec_type,
1758 x_asr_rec OUT NOCOPY asr_rec_type) IS
1759
1760 l_api_version CONSTANT NUMBER := 1;
1761 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1762 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1763 l_asr_rec asr_rec_type := p_asr_rec;
1764 l_def_asr_rec asr_rec_type;
1765 l_row_notfound BOOLEAN := TRUE;
1766 ----------------------------------
1767 -- FUNCTION populate_new_record --
1768 ----------------------------------
1769 FUNCTION populate_new_record (
1770 p_asr_rec IN asr_rec_type,
1771 x_asr_rec OUT NOCOPY asr_rec_type
1772 ) RETURN VARCHAR2 IS
1773 l_asr_rec asr_rec_type;
1774 l_row_notfound BOOLEAN := TRUE;
1775 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1776 BEGIN
1777 x_asr_rec := p_asr_rec;
1778 -- Get current database values
1779 l_asr_rec := get_rec(p_asr_rec, l_row_notfound);
1780 IF (l_row_notfound) THEN
1781 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
1782 END IF;
1783 IF (x_asr_rec.id = okl_api.G_MISS_NUM)
1784 THEN
1785 x_asr_rec.id := l_asr_rec.id;
1786 END IF;
1787 IF (x_asr_rec.ant_id = okl_api.G_MISS_NUM)
1788 THEN
1789 x_asr_rec.ant_id := l_asr_rec.ant_id;
1790 END IF;
1791 IF (x_asr_rec.qcn_id = okl_api.G_MISS_NUM)
1792 THEN
1793 x_asr_rec.qcn_id := l_asr_rec.qcn_id;
1794 END IF;
1795 IF (x_asr_rec.sort_sequence = okl_api.G_MISS_NUM)
1796 THEN
1797 x_asr_rec.sort_sequence := l_asr_rec.sort_sequence;
1798 END IF;
1799 IF (x_asr_rec.object_version_number = okl_api.G_MISS_NUM)
1800 THEN
1801 x_asr_rec.object_version_number := l_asr_rec.object_version_number;
1802 END IF;
1803 IF (x_asr_rec.attribute_category = okl_api.G_MISS_CHAR)
1804 THEN
1805 x_asr_rec.attribute_category := l_asr_rec.attribute_category;
1806 END IF;
1807 IF (x_asr_rec.attribute1 = okl_api.G_MISS_CHAR)
1808 THEN
1809 x_asr_rec.attribute1 := l_asr_rec.attribute1;
1810 END IF;
1811 IF (x_asr_rec.attribute2 = okl_api.G_MISS_CHAR)
1812 THEN
1813 x_asr_rec.attribute2 := l_asr_rec.attribute2;
1814 END IF;
1815 IF (x_asr_rec.attribute3 = okl_api.G_MISS_CHAR)
1816 THEN
1817 x_asr_rec.attribute3 := l_asr_rec.attribute3;
1818 END IF;
1819 IF (x_asr_rec.attribute4 = okl_api.G_MISS_CHAR)
1820 THEN
1821 x_asr_rec.attribute4 := l_asr_rec.attribute4;
1822 END IF;
1823 IF (x_asr_rec.attribute5 = okl_api.G_MISS_CHAR)
1824 THEN
1825 x_asr_rec.attribute5 := l_asr_rec.attribute5;
1826 END IF;
1827 IF (x_asr_rec.attribute6 = okl_api.G_MISS_CHAR)
1828 THEN
1829 x_asr_rec.attribute6 := l_asr_rec.attribute6;
1830 END IF;
1831 IF (x_asr_rec.attribute7 = okl_api.G_MISS_CHAR)
1832 THEN
1833 x_asr_rec.attribute7 := l_asr_rec.attribute7;
1834 END IF;
1835 IF (x_asr_rec.attribute8 = okl_api.G_MISS_CHAR)
1836 THEN
1837 x_asr_rec.attribute8 := l_asr_rec.attribute8;
1838 END IF;
1839 IF (x_asr_rec.attribute9 = okl_api.G_MISS_CHAR)
1840 THEN
1841 x_asr_rec.attribute9 := l_asr_rec.attribute9;
1842 END IF;
1843 IF (x_asr_rec.attribute10 = okl_api.G_MISS_CHAR)
1844 THEN
1845 x_asr_rec.attribute10 := l_asr_rec.attribute10;
1846 END IF;
1847 IF (x_asr_rec.attribute11 = okl_api.G_MISS_CHAR)
1848 THEN
1849 x_asr_rec.attribute11 := l_asr_rec.attribute11;
1850 END IF;
1851 IF (x_asr_rec.attribute12 = okl_api.G_MISS_CHAR)
1852 THEN
1853 x_asr_rec.attribute12 := l_asr_rec.attribute12;
1854 END IF;
1855 IF (x_asr_rec.attribute13 = okl_api.G_MISS_CHAR)
1856 THEN
1857 x_asr_rec.attribute13 := l_asr_rec.attribute13;
1858 END IF;
1859 IF (x_asr_rec.attribute14 = okl_api.G_MISS_CHAR)
1860 THEN
1861 x_asr_rec.attribute14 := l_asr_rec.attribute14;
1862 END IF;
1863 IF (x_asr_rec.attribute15 = okl_api.G_MISS_CHAR)
1864 THEN
1865 x_asr_rec.attribute15 := l_asr_rec.attribute15;
1866 END IF;
1867 IF (x_asr_rec.created_by = okl_api.G_MISS_NUM)
1868 THEN
1869 x_asr_rec.created_by := l_asr_rec.created_by;
1870 END IF;
1871 IF (x_asr_rec.creation_date = okl_api.G_MISS_DATE)
1872 THEN
1873 x_asr_rec.creation_date := l_asr_rec.creation_date;
1874 END IF;
1875 IF (x_asr_rec.last_updated_by = okl_api.G_MISS_NUM)
1876 THEN
1877 x_asr_rec.last_updated_by := l_asr_rec.last_updated_by;
1878 END IF;
1879 IF (x_asr_rec.last_update_date = okl_api.G_MISS_DATE)
1880 THEN
1881 x_asr_rec.last_update_date := l_asr_rec.last_update_date;
1882 END IF;
1883 IF (x_asr_rec.last_update_login = okl_api.G_MISS_NUM)
1884 THEN
1885 x_asr_rec.last_update_login := l_asr_rec.last_update_login;
1886 END IF;
1887 RETURN(l_return_status);
1888 END populate_new_record;
1889 ----------------------------------------------
1890 -- Set_Attributes for:OKL_ANSR_SET_CRTRIA_B --
1891 ----------------------------------------------
1892 FUNCTION Set_Attributes (
1893 p_asr_rec IN asr_rec_type,
1894 x_asr_rec OUT NOCOPY asr_rec_type
1895 ) RETURN VARCHAR2 IS
1896 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1897 BEGIN
1898 x_asr_rec := p_asr_rec;
1899 RETURN(l_return_status);
1900 END Set_Attributes;
1901 BEGIN
1902 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1903 p_init_msg_list,
1904 '_PVT',
1905 x_return_status);
1906 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1907 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1908 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1909 RAISE okl_api.G_EXCEPTION_ERROR;
1910 END IF;
1911 --- Setting item attributes
1912 l_return_status := Set_Attributes(
1913 p_asr_rec, -- IN
1914 l_asr_rec); -- OUT
1915 --- If any errors happen abort API
1916 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1917 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1918 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1919 RAISE okl_api.G_EXCEPTION_ERROR;
1920 END IF;
1921 l_return_status := populate_new_record(l_asr_rec, l_def_asr_rec);
1922 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1923 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1924 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1925 RAISE okl_api.G_EXCEPTION_ERROR;
1926 END IF;
1927 UPDATE OKL_ANSR_SET_CRTRIA_B
1928 SET ANT_ID = l_def_asr_rec.ant_id,
1929 QCN_ID = l_def_asr_rec.qcn_id,
1930 SORT_SEQUENCE = l_def_asr_rec.sort_sequence,
1931 OBJECT_VERSION_NUMBER = l_def_asr_rec.object_version_number,
1932 ATTRIBUTE_CATEGORY = l_def_asr_rec.attribute_category,
1933 ATTRIBUTE1 = l_def_asr_rec.attribute1,
1934 ATTRIBUTE2 = l_def_asr_rec.attribute2,
1935 ATTRIBUTE3 = l_def_asr_rec.attribute3,
1936 ATTRIBUTE4 = l_def_asr_rec.attribute4,
1937 ATTRIBUTE5 = l_def_asr_rec.attribute5,
1938 ATTRIBUTE6 = l_def_asr_rec.attribute6,
1939 ATTRIBUTE7 = l_def_asr_rec.attribute7,
1940 ATTRIBUTE8 = l_def_asr_rec.attribute8,
1941 ATTRIBUTE9 = l_def_asr_rec.attribute9,
1942 ATTRIBUTE10 = l_def_asr_rec.attribute10,
1943 ATTRIBUTE11 = l_def_asr_rec.attribute11,
1944 ATTRIBUTE12 = l_def_asr_rec.attribute12,
1945 ATTRIBUTE13 = l_def_asr_rec.attribute13,
1946 ATTRIBUTE14 = l_def_asr_rec.attribute14,
1947 ATTRIBUTE15 = l_def_asr_rec.attribute15,
1948 CREATED_BY = l_def_asr_rec.created_by,
1949 CREATION_DATE = l_def_asr_rec.creation_date,
1950 LAST_UPDATED_BY = l_def_asr_rec.last_updated_by,
1951 LAST_UPDATE_DATE = l_def_asr_rec.last_update_date,
1952 LAST_UPDATE_LOGIN = l_def_asr_rec.last_update_login
1953 WHERE ID = l_def_asr_rec.id;
1954
1955 x_asr_rec := l_def_asr_rec;
1956 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1957 EXCEPTION
1958 WHEN okl_api.G_EXCEPTION_ERROR THEN
1959 x_return_status := okl_api.HANDLE_EXCEPTIONS
1960 (
1961 l_api_name,
1962 G_PKG_NAME,
1963 'okl_api.G_RET_STS_ERROR',
1964 x_msg_count,
1965 x_msg_data,
1966 '_PVT'
1967 );
1968 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1969 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1970 (
1971 l_api_name,
1972 G_PKG_NAME,
1973 'okl_api.G_RET_STS_UNEXP_ERROR',
1974 x_msg_count,
1975 x_msg_data,
1976 '_PVT'
1977 );
1978 WHEN OTHERS THEN
1979 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1980 (
1981 l_api_name,
1982 G_PKG_NAME,
1983 'OTHERS',
1984 x_msg_count,
1985 x_msg_data,
1986 '_PVT'
1987 );
1988 END update_row;
1989 -------------------------------------------
1990 -- update_row for:OKL_ANSR_SET_CRTRIA_TL --
1991 -------------------------------------------
1992 PROCEDURE update_row(
1993 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1994 x_return_status OUT NOCOPY VARCHAR2,
1995 x_msg_count OUT NOCOPY NUMBER,
1996 x_msg_data OUT NOCOPY VARCHAR2,
1997 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType,
1998 x_okl_ansr_set_crtria_tl_rec OUT NOCOPY OklAnsrSetCrtriaTlRecType) IS
1999
2000 l_api_version CONSTANT NUMBER := 1;
2001 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2002 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2003 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType := p_okl_ansr_set_crtria_tl_rec;
2004 ldefoklansrsetcrtriatlrec OklAnsrSetCrtriaTlRecType;
2005 l_row_notfound BOOLEAN := TRUE;
2006 ----------------------------------
2007 -- FUNCTION populate_new_record --
2008 ----------------------------------
2009 FUNCTION populate_new_record (
2010 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType,
2011 x_okl_ansr_set_crtria_tl_rec OUT NOCOPY OklAnsrSetCrtriaTlRecType
2012 ) RETURN VARCHAR2 IS
2013 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
2014 l_row_notfound BOOLEAN := TRUE;
2015 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2016 BEGIN
2017 x_okl_ansr_set_crtria_tl_rec := p_okl_ansr_set_crtria_tl_rec;
2018 -- Get current database values
2019 l_okl_ansr_set_crtria_tl_rec := get_rec(p_okl_ansr_set_crtria_tl_rec, l_row_notfound);
2020 IF (l_row_notfound) THEN
2021 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
2022 END IF;
2023 IF (x_okl_ansr_set_crtria_tl_rec.id = okl_api.G_MISS_NUM)
2024 THEN
2025 x_okl_ansr_set_crtria_tl_rec.id := l_okl_ansr_set_crtria_tl_rec.id;
2026 END IF;
2027 IF (x_okl_ansr_set_crtria_tl_rec.LANGUAGE = okl_api.G_MISS_CHAR)
2028 THEN
2029 x_okl_ansr_set_crtria_tl_rec.LANGUAGE := l_okl_ansr_set_crtria_tl_rec.LANGUAGE;
2030 END IF;
2031 IF (x_okl_ansr_set_crtria_tl_rec.source_lang = okl_api.G_MISS_CHAR)
2032 THEN
2033 x_okl_ansr_set_crtria_tl_rec.source_lang := l_okl_ansr_set_crtria_tl_rec.source_lang;
2034 END IF;
2035 IF (x_okl_ansr_set_crtria_tl_rec.sfwt_flag = okl_api.G_MISS_CHAR)
2036 THEN
2037 x_okl_ansr_set_crtria_tl_rec.sfwt_flag := l_okl_ansr_set_crtria_tl_rec.sfwt_flag;
2038 END IF;
2039 IF (x_okl_ansr_set_crtria_tl_rec.description = okl_api.G_MISS_CHAR)
2040 THEN
2041 x_okl_ansr_set_crtria_tl_rec.description := l_okl_ansr_set_crtria_tl_rec.description;
2042 END IF;
2043 IF (x_okl_ansr_set_crtria_tl_rec.created_by = okl_api.G_MISS_NUM)
2044 THEN
2045 x_okl_ansr_set_crtria_tl_rec.created_by := l_okl_ansr_set_crtria_tl_rec.created_by;
2046 END IF;
2047 IF (x_okl_ansr_set_crtria_tl_rec.creation_date = okl_api.G_MISS_DATE)
2048 THEN
2049 x_okl_ansr_set_crtria_tl_rec.creation_date := l_okl_ansr_set_crtria_tl_rec.creation_date;
2050 END IF;
2051 IF (x_okl_ansr_set_crtria_tl_rec.last_updated_by = okl_api.G_MISS_NUM)
2052 THEN
2053 x_okl_ansr_set_crtria_tl_rec.last_updated_by := l_okl_ansr_set_crtria_tl_rec.last_updated_by;
2054 END IF;
2055 IF (x_okl_ansr_set_crtria_tl_rec.last_update_date = okl_api.G_MISS_DATE)
2056 THEN
2057 x_okl_ansr_set_crtria_tl_rec.last_update_date := l_okl_ansr_set_crtria_tl_rec.last_update_date;
2058 END IF;
2059 IF (x_okl_ansr_set_crtria_tl_rec.last_update_login = okl_api.G_MISS_NUM)
2060 THEN
2061 x_okl_ansr_set_crtria_tl_rec.last_update_login := l_okl_ansr_set_crtria_tl_rec.last_update_login;
2062 END IF;
2063 RETURN(l_return_status);
2064 END populate_new_record;
2065 -----------------------------------------------
2066 -- Set_Attributes for:OKL_ANSR_SET_CRTRIA_TL --
2067 -----------------------------------------------
2068 FUNCTION Set_Attributes (
2069 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType,
2070 x_okl_ansr_set_crtria_tl_rec OUT NOCOPY OklAnsrSetCrtriaTlRecType
2071 ) RETURN VARCHAR2 IS
2072 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2073 BEGIN
2074 x_okl_ansr_set_crtria_tl_rec := p_okl_ansr_set_crtria_tl_rec;
2075 x_okl_ansr_set_crtria_tl_rec.LANGUAGE := USERENV('LANG');
2076 x_okl_ansr_set_crtria_tl_rec.SOURCE_LANG := USERENV('LANG');
2077 RETURN(l_return_status);
2078 END Set_Attributes;
2079 BEGIN
2080 l_return_status := okl_api.START_ACTIVITY(l_api_name,
2081 p_init_msg_list,
2082 '_PVT',
2083 x_return_status);
2084 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2085 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2086 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2087 RAISE okl_api.G_EXCEPTION_ERROR;
2088 END IF;
2089 --- Setting item attributes
2090 l_return_status := Set_Attributes(
2091 p_okl_ansr_set_crtria_tl_rec, -- IN
2092 l_okl_ansr_set_crtria_tl_rec); -- OUT
2093 --- If any errors happen abort API
2094 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2095 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2096 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2097 RAISE okl_api.G_EXCEPTION_ERROR;
2098 END IF;
2099 l_return_status := populate_new_record(l_okl_ansr_set_crtria_tl_rec, ldefoklansrsetcrtriatlrec);
2100 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2101 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2102 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2103 RAISE okl_api.G_EXCEPTION_ERROR;
2104 END IF;
2105 UPDATE OKL_ANSR_SET_CRTRIA_TL
2106 SET DESCRIPTION = ldefoklansrsetcrtriatlrec.description,
2107 CREATED_BY = ldefoklansrsetcrtriatlrec.created_by,
2108 CREATION_DATE = ldefoklansrsetcrtriatlrec.creation_date,
2109 LAST_UPDATED_BY = ldefoklansrsetcrtriatlrec.last_updated_by,
2110 LAST_UPDATE_DATE = ldefoklansrsetcrtriatlrec.last_update_date,
2111 LAST_UPDATE_LOGIN = ldefoklansrsetcrtriatlrec.last_update_login
2112 WHERE ID = ldefoklansrsetcrtriatlrec.id
2113 --AND SOURCE_LANG = USERENV('LANG');
2114 AND USERENV('LANG') in (SOURCE_LANG, LANGUAGE);
2115
2116 UPDATE OKL_ANSR_SET_CRTRIA_TL
2117 SET SFWT_FLAG = 'Y'
2118 WHERE ID = ldefoklansrsetcrtriatlrec.id
2119 AND SOURCE_LANG <> USERENV('LANG');
2120
2121 x_okl_ansr_set_crtria_tl_rec := ldefoklansrsetcrtriatlrec;
2122 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2123 EXCEPTION
2124 WHEN okl_api.G_EXCEPTION_ERROR THEN
2125 x_return_status := okl_api.HANDLE_EXCEPTIONS
2126 (
2127 l_api_name,
2128 G_PKG_NAME,
2129 'okl_api.G_RET_STS_ERROR',
2130 x_msg_count,
2131 x_msg_data,
2132 '_PVT'
2133 );
2134 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2135 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2136 (
2137 l_api_name,
2138 G_PKG_NAME,
2139 'okl_api.G_RET_STS_UNEXP_ERROR',
2140 x_msg_count,
2141 x_msg_data,
2142 '_PVT'
2143 );
2144 WHEN OTHERS THEN
2145 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2146 (
2147 l_api_name,
2148 G_PKG_NAME,
2149 'OTHERS',
2150 x_msg_count,
2151 x_msg_data,
2152 '_PVT'
2153 );
2154 END update_row;
2155 ------------------------------------------
2156 -- update_row for:OKL_ANSR_SET_CRTRIA_V --
2157 ------------------------------------------
2158 PROCEDURE update_row(
2159 p_api_version IN NUMBER,
2160 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2161 x_return_status OUT NOCOPY VARCHAR2,
2162 x_msg_count OUT NOCOPY NUMBER,
2163 x_msg_data OUT NOCOPY VARCHAR2,
2164 p_asrv_rec IN asrv_rec_type,
2165 x_asrv_rec OUT NOCOPY asrv_rec_type) IS
2166
2167 l_api_version CONSTANT NUMBER := 1;
2168 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2169 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2170 l_asrv_rec asrv_rec_type := p_asrv_rec;
2171 l_def_asrv_rec asrv_rec_type;
2172 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
2173 lx_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
2174 l_asr_rec asr_rec_type;
2175 lx_asr_rec asr_rec_type;
2176 -------------------------------
2177 -- FUNCTION fill_who_columns --
2178 -------------------------------
2179 FUNCTION fill_who_columns (
2180 p_asrv_rec IN asrv_rec_type
2181 ) RETURN asrv_rec_type IS
2182 l_asrv_rec asrv_rec_type := p_asrv_rec;
2183 BEGIN
2184 l_asrv_rec.LAST_UPDATE_DATE := SYSDATE;
2185 l_asrv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
2186 l_asrv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2187 RETURN(l_asrv_rec);
2188 END fill_who_columns;
2189 ----------------------------------
2190 -- FUNCTION populate_new_record --
2191 ----------------------------------
2192 FUNCTION populate_new_record (
2193 p_asrv_rec IN asrv_rec_type,
2194 x_asrv_rec OUT NOCOPY asrv_rec_type
2195 ) RETURN VARCHAR2 IS
2196 l_asrv_rec asrv_rec_type;
2197 l_row_notfound BOOLEAN := TRUE;
2198 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2199 BEGIN
2200 x_asrv_rec := p_asrv_rec;
2201 -- Get current database values
2202 l_asrv_rec := get_rec(p_asrv_rec, l_row_notfound);
2203 IF (l_row_notfound) THEN
2204 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
2205 END IF;
2206 IF (x_asrv_rec.id = okl_api.G_MISS_NUM)
2207 THEN
2208 x_asrv_rec.id := l_asrv_rec.id;
2209 END IF;
2210 IF (x_asrv_rec.object_version_number = okl_api.G_MISS_NUM)
2211 THEN
2212 x_asrv_rec.object_version_number := l_asrv_rec.object_version_number;
2213 END IF;
2214 IF (x_asrv_rec.sfwt_flag = okl_api.G_MISS_CHAR)
2215 THEN
2216 x_asrv_rec.sfwt_flag := l_asrv_rec.sfwt_flag;
2217 END IF;
2218 IF (x_asrv_rec.ant_id = okl_api.G_MISS_NUM)
2219 THEN
2220 x_asrv_rec.ant_id := l_asrv_rec.ant_id;
2221 END IF;
2222 IF (x_asrv_rec.qcn_id = okl_api.G_MISS_NUM)
2223 THEN
2224 x_asrv_rec.qcn_id := l_asrv_rec.qcn_id;
2225 END IF;
2226 IF (x_asrv_rec.sort_sequence = okl_api.G_MISS_NUM)
2227 THEN
2228 x_asrv_rec.sort_sequence := l_asrv_rec.sort_sequence;
2229 END IF;
2230 IF (x_asrv_rec.description = okl_api.G_MISS_CHAR)
2231 THEN
2232 x_asrv_rec.description := l_asrv_rec.description;
2233 END IF;
2234 IF (x_asrv_rec.attribute_category = okl_api.G_MISS_CHAR)
2235 THEN
2236 x_asrv_rec.attribute_category := l_asrv_rec.attribute_category;
2237 END IF;
2238 IF (x_asrv_rec.attribute1 = okl_api.G_MISS_CHAR)
2239 THEN
2240 x_asrv_rec.attribute1 := l_asrv_rec.attribute1;
2241 END IF;
2242 IF (x_asrv_rec.attribute2 = okl_api.G_MISS_CHAR)
2243 THEN
2244 x_asrv_rec.attribute2 := l_asrv_rec.attribute2;
2245 END IF;
2246 IF (x_asrv_rec.attribute3 = okl_api.G_MISS_CHAR)
2247 THEN
2248 x_asrv_rec.attribute3 := l_asrv_rec.attribute3;
2249 END IF;
2250 IF (x_asrv_rec.attribute4 = okl_api.G_MISS_CHAR)
2251 THEN
2252 x_asrv_rec.attribute4 := l_asrv_rec.attribute4;
2253 END IF;
2254 IF (x_asrv_rec.attribute5 = okl_api.G_MISS_CHAR)
2255 THEN
2256 x_asrv_rec.attribute5 := l_asrv_rec.attribute5;
2257 END IF;
2258 IF (x_asrv_rec.attribute6 = okl_api.G_MISS_CHAR)
2259 THEN
2260 x_asrv_rec.attribute6 := l_asrv_rec.attribute6;
2261 END IF;
2262 IF (x_asrv_rec.attribute7 = okl_api.G_MISS_CHAR)
2263 THEN
2264 x_asrv_rec.attribute7 := l_asrv_rec.attribute7;
2265 END IF;
2266 IF (x_asrv_rec.attribute8 = okl_api.G_MISS_CHAR)
2267 THEN
2268 x_asrv_rec.attribute8 := l_asrv_rec.attribute8;
2269 END IF;
2270 IF (x_asrv_rec.attribute9 = okl_api.G_MISS_CHAR)
2271 THEN
2272 x_asrv_rec.attribute9 := l_asrv_rec.attribute9;
2273 END IF;
2274 IF (x_asrv_rec.attribute10 = okl_api.G_MISS_CHAR)
2275 THEN
2276 x_asrv_rec.attribute10 := l_asrv_rec.attribute10;
2277 END IF;
2278 IF (x_asrv_rec.attribute11 = okl_api.G_MISS_CHAR)
2279 THEN
2280 x_asrv_rec.attribute11 := l_asrv_rec.attribute11;
2281 END IF;
2282 IF (x_asrv_rec.attribute12 = okl_api.G_MISS_CHAR)
2283 THEN
2284 x_asrv_rec.attribute12 := l_asrv_rec.attribute12;
2285 END IF;
2286 IF (x_asrv_rec.attribute13 = okl_api.G_MISS_CHAR)
2287 THEN
2288 x_asrv_rec.attribute13 := l_asrv_rec.attribute13;
2289 END IF;
2290 IF (x_asrv_rec.attribute14 = okl_api.G_MISS_CHAR)
2291 THEN
2292 x_asrv_rec.attribute14 := l_asrv_rec.attribute14;
2293 END IF;
2294 IF (x_asrv_rec.attribute15 = okl_api.G_MISS_CHAR)
2295 THEN
2296 x_asrv_rec.attribute15 := l_asrv_rec.attribute15;
2297 END IF;
2298 IF (x_asrv_rec.created_by = okl_api.G_MISS_NUM)
2299 THEN
2300 x_asrv_rec.created_by := l_asrv_rec.created_by;
2301 END IF;
2302 IF (x_asrv_rec.creation_date = okl_api.G_MISS_DATE)
2303 THEN
2304 x_asrv_rec.creation_date := l_asrv_rec.creation_date;
2305 END IF;
2306 IF (x_asrv_rec.last_updated_by = okl_api.G_MISS_NUM)
2307 THEN
2308 x_asrv_rec.last_updated_by := l_asrv_rec.last_updated_by;
2309 END IF;
2310 IF (x_asrv_rec.last_update_date = okl_api.G_MISS_DATE)
2311 THEN
2312 x_asrv_rec.last_update_date := l_asrv_rec.last_update_date;
2313 END IF;
2314 IF (x_asrv_rec.last_update_login = okl_api.G_MISS_NUM)
2315 THEN
2316 x_asrv_rec.last_update_login := l_asrv_rec.last_update_login;
2317 END IF;
2318 RETURN(l_return_status);
2319 END populate_new_record;
2320 ----------------------------------------------
2321 -- Set_Attributes for:OKL_ANSR_SET_CRTRIA_V --
2322 ----------------------------------------------
2323 FUNCTION Set_Attributes (
2324 p_asrv_rec IN asrv_rec_type,
2325 x_asrv_rec OUT NOCOPY asrv_rec_type
2326 ) RETURN VARCHAR2 IS
2327 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2328 BEGIN
2329 x_asrv_rec := p_asrv_rec;
2330 x_asrv_rec.OBJECT_VERSION_NUMBER := NVL(x_asrv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2331 RETURN(l_return_status);
2332 END Set_Attributes;
2333 BEGIN
2334 l_return_status := okl_api.START_ACTIVITY(l_api_name,
2335 G_PKG_NAME,
2336 p_init_msg_list,
2337 l_api_version,
2338 p_api_version,
2339 '_PVT',
2340 x_return_status);
2341 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2342 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2343 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2344 RAISE okl_api.G_EXCEPTION_ERROR;
2345 END IF;
2346 --- Setting item attributes
2347 l_return_status := Set_Attributes(
2348 p_asrv_rec, -- IN
2349 l_asrv_rec); -- OUT
2350 --- If any errors happen abort API
2351 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2352 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2353 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2354 RAISE okl_api.G_EXCEPTION_ERROR;
2355 END IF;
2356 l_return_status := populate_new_record(l_asrv_rec, l_def_asrv_rec);
2357 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2358 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2359 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2360 RAISE okl_api.G_EXCEPTION_ERROR;
2361 END IF;
2362 l_def_asrv_rec := fill_who_columns(l_def_asrv_rec);
2363 --- Validate all non-missing attributes (Item Level Validation)
2364 l_return_status := Validate_Attributes(l_def_asrv_rec);
2365 --- If any errors happen abort API
2366 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2367 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2368 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2369 RAISE okl_api.G_EXCEPTION_ERROR;
2370 END IF;
2371 l_return_status := Validate_Record(l_def_asrv_rec);
2372 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2373 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2374 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2375 RAISE okl_api.G_EXCEPTION_ERROR;
2376 END IF;
2377
2378 --------------------------------------
2379 -- Move VIEW record to "Child" records
2380 --------------------------------------
2381 migrate(l_def_asrv_rec, l_okl_ansr_set_crtria_tl_rec);
2382 migrate(l_def_asrv_rec, l_asr_rec);
2383 --------------------------------------------
2384 -- Call the UPDATE_ROW for each child record
2385 --------------------------------------------
2386 update_row(
2387 p_init_msg_list,
2388 x_return_status,
2389 x_msg_count,
2390 x_msg_data,
2391 l_okl_ansr_set_crtria_tl_rec,
2392 lx_okl_ansr_set_crtria_tl_rec
2393 );
2394 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2395 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2396 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
2397 RAISE okl_api.G_EXCEPTION_ERROR;
2398 END IF;
2399 migrate(lx_okl_ansr_set_crtria_tl_rec, l_def_asrv_rec);
2400 update_row(
2401 p_init_msg_list,
2402 x_return_status,
2403 x_msg_count,
2404 x_msg_data,
2405 l_asr_rec,
2406 lx_asr_rec
2407 );
2408 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2409 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2410 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
2411 RAISE okl_api.G_EXCEPTION_ERROR;
2412 END IF;
2413 migrate(lx_asr_rec, l_def_asrv_rec);
2414 x_asrv_rec := l_def_asrv_rec;
2415 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2416 EXCEPTION
2417 WHEN okl_api.G_EXCEPTION_ERROR THEN
2418 x_return_status := okl_api.HANDLE_EXCEPTIONS
2419 (
2420 l_api_name,
2421 G_PKG_NAME,
2422 'okl_api.G_RET_STS_ERROR',
2423 x_msg_count,
2424 x_msg_data,
2425 '_PVT'
2426 );
2427 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2428 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2429 (
2430 l_api_name,
2431 G_PKG_NAME,
2432 'okl_api.G_RET_STS_UNEXP_ERROR',
2433 x_msg_count,
2434 x_msg_data,
2435 '_PVT'
2436 );
2437 WHEN OTHERS THEN
2438 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2439 (
2440 l_api_name,
2441 G_PKG_NAME,
2442 'OTHERS',
2443 x_msg_count,
2444 x_msg_data,
2445 '_PVT'
2446 );
2447 END update_row;
2448 ----------------------------------------
2449 -- PL/SQL TBL update_row for:ASRV_TBL --
2450 ----------------------------------------
2451 PROCEDURE update_row(
2452 p_api_version IN NUMBER,
2453 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2454 x_return_status OUT NOCOPY VARCHAR2,
2455 x_msg_count OUT NOCOPY NUMBER,
2456 x_msg_data OUT NOCOPY VARCHAR2,
2457 p_asrv_tbl IN asrv_tbl_type,
2458 x_asrv_tbl OUT NOCOPY asrv_tbl_type) IS
2459
2460 l_api_version CONSTANT NUMBER := 1;
2461 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2462 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2463 i NUMBER := 0;
2464 BEGIN
2465 okl_api.init_msg_list(p_init_msg_list);
2466 -- Make sure PL/SQL table has records in it before passing
2467 IF (p_asrv_tbl.COUNT > 0) THEN
2468 i := p_asrv_tbl.FIRST;
2469 LOOP
2470 update_row (
2471 p_api_version => p_api_version,
2472 p_init_msg_list => okl_api.G_FALSE,
2473 x_return_status => x_return_status,
2474 x_msg_count => x_msg_count,
2475 x_msg_data => x_msg_data,
2476 p_asrv_rec => p_asrv_tbl(i),
2477 x_asrv_rec => x_asrv_tbl(i));
2478 EXIT WHEN (i = p_asrv_tbl.LAST);
2479 i := p_asrv_tbl.NEXT(i);
2480 END LOOP;
2481 END IF;
2482 EXCEPTION
2483 WHEN okl_api.G_EXCEPTION_ERROR THEN
2484 x_return_status := okl_api.HANDLE_EXCEPTIONS
2485 (
2486 l_api_name,
2487 G_PKG_NAME,
2488 'okl_api.G_RET_STS_ERROR',
2489 x_msg_count,
2490 x_msg_data,
2491 '_PVT'
2492 );
2493 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2494 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2495 (
2496 l_api_name,
2497 G_PKG_NAME,
2498 'okl_api.G_RET_STS_UNEXP_ERROR',
2499 x_msg_count,
2500 x_msg_data,
2501 '_PVT'
2502 );
2503 WHEN OTHERS THEN
2504 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2505 (
2506 l_api_name,
2507 G_PKG_NAME,
2508 'OTHERS',
2509 x_msg_count,
2510 x_msg_data,
2511 '_PVT'
2512 );
2513 END update_row;
2514
2515 ---------------------------------------------------------------------------
2516 -- PROCEDURE delete_row
2517 ---------------------------------------------------------------------------
2518 ------------------------------------------
2519 -- delete_row for:OKL_ANSR_SET_CRTRIA_B --
2520 ------------------------------------------
2521 PROCEDURE delete_row(
2522 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2523 x_return_status OUT NOCOPY VARCHAR2,
2524 x_msg_count OUT NOCOPY NUMBER,
2525 x_msg_data OUT NOCOPY VARCHAR2,
2526 p_asr_rec IN asr_rec_type) IS
2527
2528 l_api_version CONSTANT NUMBER := 1;
2529 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2530 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2531 l_asr_rec asr_rec_type:= p_asr_rec;
2532 l_row_notfound BOOLEAN := TRUE;
2533 BEGIN
2534 l_return_status := okl_api.START_ACTIVITY(l_api_name,
2535 p_init_msg_list,
2536 '_PVT',
2537 x_return_status);
2538 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2539 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2540 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2541 RAISE okl_api.G_EXCEPTION_ERROR;
2542 END IF;
2543 DELETE FROM OKL_ANSR_SET_CRTRIA_B
2544 WHERE ID = l_asr_rec.id;
2545
2546 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2547 EXCEPTION
2548 WHEN okl_api.G_EXCEPTION_ERROR THEN
2549 x_return_status := okl_api.HANDLE_EXCEPTIONS
2550 (
2551 l_api_name,
2552 G_PKG_NAME,
2553 'okl_api.G_RET_STS_ERROR',
2554 x_msg_count,
2555 x_msg_data,
2556 '_PVT'
2557 );
2558 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2559 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2560 (
2561 l_api_name,
2562 G_PKG_NAME,
2563 'okl_api.G_RET_STS_UNEXP_ERROR',
2564 x_msg_count,
2565 x_msg_data,
2566 '_PVT'
2567 );
2568 WHEN OTHERS THEN
2569 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2570 (
2571 l_api_name,
2572 G_PKG_NAME,
2573 'OTHERS',
2574 x_msg_count,
2575 x_msg_data,
2576 '_PVT'
2577 );
2578 END delete_row;
2579 -------------------------------------------
2580 -- delete_row for:OKL_ANSR_SET_CRTRIA_TL --
2581 -------------------------------------------
2582 PROCEDURE delete_row(
2583 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2584 x_return_status OUT NOCOPY VARCHAR2,
2585 x_msg_count OUT NOCOPY NUMBER,
2586 x_msg_data OUT NOCOPY VARCHAR2,
2587 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType) IS
2588
2589 l_api_version CONSTANT NUMBER := 1;
2590 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
2591 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2592 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType:= p_okl_ansr_set_crtria_tl_rec;
2593 l_row_notfound BOOLEAN := TRUE;
2594 -----------------------------------------------
2595 -- Set_Attributes for:OKL_ANSR_SET_CRTRIA_TL --
2596 -----------------------------------------------
2597 FUNCTION Set_Attributes (
2598 p_okl_ansr_set_crtria_tl_rec IN OklAnsrSetCrtriaTlRecType,
2599 x_okl_ansr_set_crtria_tl_rec OUT NOCOPY OklAnsrSetCrtriaTlRecType
2600 ) RETURN VARCHAR2 IS
2601 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2602 BEGIN
2603 x_okl_ansr_set_crtria_tl_rec := p_okl_ansr_set_crtria_tl_rec;
2604 x_okl_ansr_set_crtria_tl_rec.LANGUAGE := USERENV('LANG');
2605 RETURN(l_return_status);
2606 END Set_Attributes;
2607 BEGIN
2608 l_return_status := okl_api.START_ACTIVITY(l_api_name,
2609 p_init_msg_list,
2610 '_PVT',
2611 x_return_status);
2612 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2613 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2614 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2615 RAISE okl_api.G_EXCEPTION_ERROR;
2616 END IF;
2617 --- Setting item attributes
2618 l_return_status := Set_Attributes(
2619 p_okl_ansr_set_crtria_tl_rec, -- IN
2620 l_okl_ansr_set_crtria_tl_rec); -- OUT
2621 --- If any errors happen abort API
2622 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2623 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2624 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2625 RAISE okl_api.G_EXCEPTION_ERROR;
2626 END IF;
2627 DELETE FROM OKL_ANSR_SET_CRTRIA_TL
2628 WHERE ID = l_okl_ansr_set_crtria_tl_rec.id;
2629
2630 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2631 EXCEPTION
2632 WHEN okl_api.G_EXCEPTION_ERROR THEN
2633 x_return_status := okl_api.HANDLE_EXCEPTIONS
2634 (
2635 l_api_name,
2636 G_PKG_NAME,
2637 'okl_api.G_RET_STS_ERROR',
2638 x_msg_count,
2639 x_msg_data,
2640 '_PVT'
2641 );
2642 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2643 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2644 (
2645 l_api_name,
2646 G_PKG_NAME,
2647 'okl_api.G_RET_STS_UNEXP_ERROR',
2648 x_msg_count,
2649 x_msg_data,
2650 '_PVT'
2651 );
2652 WHEN OTHERS THEN
2653 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2654 (
2655 l_api_name,
2656 G_PKG_NAME,
2657 'OTHERS',
2658 x_msg_count,
2659 x_msg_data,
2660 '_PVT'
2661 );
2662 END delete_row;
2663 ------------------------------------------
2664 -- delete_row for:OKL_ANSR_SET_CRTRIA_V --
2665 ------------------------------------------
2666 PROCEDURE delete_row(
2667 p_api_version IN NUMBER,
2668 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2669 x_return_status OUT NOCOPY VARCHAR2,
2670 x_msg_count OUT NOCOPY NUMBER,
2671 x_msg_data OUT NOCOPY VARCHAR2,
2672 p_asrv_rec IN asrv_rec_type) IS
2673
2674 l_api_version CONSTANT NUMBER := 1;
2675 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2676 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2677 l_asrv_rec asrv_rec_type := p_asrv_rec;
2678 l_okl_ansr_set_crtria_tl_rec OklAnsrSetCrtriaTlRecType;
2679 l_asr_rec asr_rec_type;
2680 BEGIN
2681 l_return_status := okl_api.START_ACTIVITY(l_api_name,
2682 G_PKG_NAME,
2683 p_init_msg_list,
2684 l_api_version,
2685 p_api_version,
2686 '_PVT',
2687 x_return_status);
2688 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2689 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2690 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2691 RAISE okl_api.G_EXCEPTION_ERROR;
2692 END IF;
2693 --------------------------------------
2694 -- Move VIEW record to "Child" records
2695 --------------------------------------
2696 migrate(l_asrv_rec, l_okl_ansr_set_crtria_tl_rec);
2697 migrate(l_asrv_rec, l_asr_rec);
2698 --------------------------------------------
2699 -- Call the DELETE_ROW for each child record
2700 --------------------------------------------
2701 delete_row(
2702 p_init_msg_list,
2703 x_return_status,
2704 x_msg_count,
2705 x_msg_data,
2706 l_okl_ansr_set_crtria_tl_rec
2707 );
2708 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2709 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2710 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
2711 RAISE okl_api.G_EXCEPTION_ERROR;
2712 END IF;
2713 delete_row(
2714 p_init_msg_list,
2715 x_return_status,
2716 x_msg_count,
2717 x_msg_data,
2718 l_asr_rec
2719 );
2720 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2721 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2722 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
2723 RAISE okl_api.G_EXCEPTION_ERROR;
2724 END IF;
2725 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2726 EXCEPTION
2727 WHEN okl_api.G_EXCEPTION_ERROR THEN
2728 x_return_status := okl_api.HANDLE_EXCEPTIONS
2729 (
2730 l_api_name,
2731 G_PKG_NAME,
2732 'okl_api.G_RET_STS_ERROR',
2733 x_msg_count,
2734 x_msg_data,
2735 '_PVT'
2736 );
2737 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2738 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2739 (
2740 l_api_name,
2741 G_PKG_NAME,
2742 'okl_api.G_RET_STS_UNEXP_ERROR',
2743 x_msg_count,
2744 x_msg_data,
2745 '_PVT'
2746 );
2747 WHEN OTHERS THEN
2748 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2749 (
2750 l_api_name,
2751 G_PKG_NAME,
2752 'OTHERS',
2753 x_msg_count,
2754 x_msg_data,
2755 '_PVT'
2756 );
2757 END delete_row;
2758 ----------------------------------------
2759 -- PL/SQL TBL delete_row for:ASRV_TBL --
2760 ----------------------------------------
2761 PROCEDURE delete_row(
2762 p_api_version IN NUMBER,
2763 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2764 x_return_status OUT NOCOPY VARCHAR2,
2765 x_msg_count OUT NOCOPY NUMBER,
2766 x_msg_data OUT NOCOPY VARCHAR2,
2767 p_asrv_tbl IN asrv_tbl_type) IS
2768
2769 l_api_version CONSTANT NUMBER := 1;
2770 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2771 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2772 i NUMBER := 0;
2773 BEGIN
2774 okl_api.init_msg_list(p_init_msg_list);
2775 -- Make sure PL/SQL table has records in it before passing
2776 IF (p_asrv_tbl.COUNT > 0) THEN
2777 i := p_asrv_tbl.FIRST;
2778 LOOP
2779 delete_row (
2780 p_api_version => p_api_version,
2781 p_init_msg_list => okl_api.G_FALSE,
2782 x_return_status => x_return_status,
2783 x_msg_count => x_msg_count,
2784 x_msg_data => x_msg_data,
2785 p_asrv_rec => p_asrv_tbl(i));
2786 EXIT WHEN (i = p_asrv_tbl.LAST);
2787 i := p_asrv_tbl.NEXT(i);
2788 END LOOP;
2789 END IF;
2790 EXCEPTION
2791 WHEN okl_api.G_EXCEPTION_ERROR THEN
2792 x_return_status := okl_api.HANDLE_EXCEPTIONS
2793 (
2794 l_api_name,
2795 G_PKG_NAME,
2796 'okl_api.G_RET_STS_ERROR',
2797 x_msg_count,
2798 x_msg_data,
2799 '_PVT'
2800 );
2801 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2802 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2803 (
2804 l_api_name,
2805 G_PKG_NAME,
2806 'okl_api.G_RET_STS_UNEXP_ERROR',
2807 x_msg_count,
2808 x_msg_data,
2809 '_PVT'
2810 );
2811 WHEN OTHERS THEN
2812 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2813 (
2814 l_api_name,
2815 G_PKG_NAME,
2816 'OTHERS',
2817 x_msg_count,
2818 x_msg_data,
2819 '_PVT'
2820 );
2821 END delete_row;
2822 END Okl_Asr_Pvt;