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