[Home] [Help]
PACKAGE BODY: APPS.OKL_AWR_PVT
Source
1 PACKAGE BODY Okl_Awr_Pvt AS
2 /* $Header: OKLSAWRB.pls 115.3 2002/05/10 08:32:26 pkm ship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE validate_id
6 ---------------------------------------------------------------------------
7 PROCEDURE validate_id (p_awrv_rec IN awrv_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_awrv_rec.id = Okl_Api.G_MISS_NUM OR
14 p_awrv_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_awrv_rec IN awrv_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_awrv_rec.object_version_number = Okl_Api.G_MISS_NUM OR
38 p_awrv_rec.object_version_number IS NULL
39 THEN
40
41 x_return_status := Okl_Api.G_RET_STS_ERROR;
42 --set error message in message stack
43 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
44 p_msg_name => G_REQUIRED_VALUE,
45 p_token1 => G_COL_NAME_TOKEN,
46 p_token1_value => 'object_version_number');
47 RAISE G_EXCEPTION_HALT_VALIDATION;
48
49 END IF;
50 END validate_object_version_number;
51
52 ---------------------------------------------------------------------------
53 -- PROCEDURE validate_ant_id
54 ---------------------------------------------------------------------------
55 PROCEDURE validate_ant_id (p_awrv_rec IN awrv_rec_type,
56 x_return_status OUT NOCOPY VARCHAR2) IS
57
58 l_dummy_var VARCHAR2(1) := '0';
59
60 CURSOR l_ant_id_csr IS
61 SELECT '1'
62 FROM OKL_ANSWER_SETS_B
63 WHERE id = p_awrv_rec.ant_id;
64
65 BEGIN
66 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
67
68 --Check for Null
69 IF p_awrv_rec.ant_id = Okl_Api.G_MISS_NUM OR
70 p_awrv_rec.ant_id IS NULL
71 THEN
72 x_return_status := Okl_Api.G_RET_STS_ERROR;
73 --set error message in message stack
74 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
75 p_msg_name => G_REQUIRED_VALUE,
76 p_token1 => G_COL_NAME_TOKEN,
77 p_token1_value => 'ant_id');
78 RAISE G_EXCEPTION_HALT_VALIDATION;
79 END IF;
80
81 -- Validate Foreign Key
82 OPEN l_ant_id_csr;
83 FETCH l_ant_id_csr INTO l_dummy_var;
84 CLOSE l_ant_id_csr;
85
86 IF (l_dummy_var <> '1') THEN
87 x_return_status := Okl_Api.G_RET_STS_ERROR;
88 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
89 p_msg_name => G_NO_PARENT_RECORD,
90 p_token1 => G_COL_NAME_TOKEN,
91 p_token1_value => 'ANT_ID_FOR',
92 p_token2 => G_CHILD_TABLE_TOKEN,
93 p_token2_value => G_VIEW,
94 p_token3 => G_PARENT_TABLE_TOKEN,
95 p_token3_value => 'OKL_ANSWERS_V');
96
97 RAISE G_EXCEPTION_HALT_VALIDATION;
98 END IF;
99 END validate_ant_id;
100
101 ---------------------------------------------------------------------------
102 -- PROCEDURE validate_rul_id
103 ---------------------------------------------------------------------------
104 PROCEDURE validate_rul_id (p_awrv_rec IN awrv_rec_type,
105 x_return_status OUT NOCOPY VARCHAR2) IS
106
107 l_dummy_var VARCHAR2(1) := '0';
108
109 CURSOR l_rul_id_csr IS
110 SELECT '1'
111 FROM OKC_RULES_B
112 WHERE id = p_awrv_rec.rul_id;
113
114 BEGIN
115 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
116
117 --Check for Null
118 IF p_awrv_rec.rul_id = Okl_Api.G_MISS_NUM OR
119 p_awrv_rec.rul_id IS NULL
120 THEN
121 x_return_status := Okl_Api.G_RET_STS_ERROR;
122 --set error message in message stack
123 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
124 p_msg_name => G_REQUIRED_VALUE,
125 p_token1 => G_COL_NAME_TOKEN,
126 p_token1_value => 'rul_id');
127 RAISE G_EXCEPTION_HALT_VALIDATION;
128 END IF;
129
130 -- Validate Foreign Key
131 OPEN l_rul_id_csr;
132 FETCH l_rul_id_csr INTO l_dummy_var;
133 CLOSE l_rul_id_csr;
134
135 IF (l_dummy_var <> '1') THEN
136 x_return_status := Okl_Api.G_RET_STS_ERROR;
137 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
138 p_msg_name => G_NO_PARENT_RECORD,
139 p_token1 => G_COL_NAME_TOKEN,
140 p_token1_value => 'RUL_ID_FOR',
141 p_token2 => G_CHILD_TABLE_TOKEN,
142 p_token2_value => G_VIEW,
143 p_token3 => G_PARENT_TABLE_TOKEN,
144 p_token3_value => 'OKL_ANSWERS_V');
145
146 RAISE G_EXCEPTION_HALT_VALIDATION;
147 END IF;
148 END validate_rul_id;
149
150 ---------------------------------------------------------------------------
151 -- PROCEDURE validate_sort_sequence
152 ---------------------------------------------------------------------------
153 PROCEDURE validate_sort_sequence (p_awrv_rec IN awrv_rec_type,
154 x_return_status OUT NOCOPY VARCHAR2) IS
155
156 BEGIN
157 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
158 --Check for Null
159 IF p_awrv_rec.sort_sequence = Okl_Api.G_MISS_NUM OR
160 p_awrv_rec.sort_sequence IS NULL
161 THEN
162
163 x_return_status := Okl_Api.G_RET_STS_ERROR;
164 --set error message in message stack
165 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
166 p_msg_name => G_REQUIRED_VALUE,
170
167 p_token1 => G_COL_NAME_TOKEN,
168 p_token1_value => 'object_version_number');
169 RAISE G_EXCEPTION_HALT_VALIDATION;
171 END IF;
172 END validate_sort_sequence;
173
174 ---------------------------------------------------------------------------
175 -- FUNCTION get_seq_id
176 ---------------------------------------------------------------------------
177 FUNCTION get_seq_id RETURN NUMBER IS
178 BEGIN
179 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
180 END get_seq_id;
181
182 ---------------------------------------------------------------------------
183 -- PROCEDURE qc
184 ---------------------------------------------------------------------------
185 PROCEDURE qc IS
186 BEGIN
187 NULL;
188 END qc;
189
190 ---------------------------------------------------------------------------
191 -- PROCEDURE change_version
192 ---------------------------------------------------------------------------
193 PROCEDURE change_version IS
194 BEGIN
195 NULL;
196 END change_version;
197
198 ---------------------------------------------------------------------------
199 -- PROCEDURE api_copy
200 ---------------------------------------------------------------------------
201 PROCEDURE api_copy IS
202 BEGIN
203 NULL;
204 END api_copy;
205
206 ---------------------------------------------------------------------------
207 -- FUNCTION get_rec for: OKL_ANSWERS
208 ---------------------------------------------------------------------------
209 FUNCTION get_rec (
210 p_awr_rec IN awr_rec_type,
211 x_no_data_found OUT NOCOPY BOOLEAN
212 ) RETURN awr_rec_type IS
213 CURSOR awr_pk_csr (p_id IN NUMBER) IS
214 SELECT
215 ID,
216 ANT_ID,
217 RUL_ID,
218 SORT_SEQUENCE,
219 OBJECT_VERSION_NUMBER,
220 ATTRIBUTE_CATEGORY,
221 ATTRIBUTE1,
222 ATTRIBUTE2,
223 ATTRIBUTE3,
224 ATTRIBUTE4,
225 ATTRIBUTE5,
226 ATTRIBUTE6,
227 ATTRIBUTE7,
228 ATTRIBUTE8,
229 ATTRIBUTE9,
230 ATTRIBUTE10,
231 ATTRIBUTE11,
232 ATTRIBUTE12,
233 ATTRIBUTE13,
234 ATTRIBUTE14,
235 ATTRIBUTE15,
236 CREATED_BY,
237 CREATION_DATE,
238 LAST_UPDATED_BY,
239 LAST_UPDATE_DATE,
240 LAST_UPDATE_LOGIN
241 FROM Okl_Answers
242 WHERE okl_answers.id = p_id;
243 l_awr_pk awr_pk_csr%ROWTYPE;
244 l_awr_rec awr_rec_type;
245 BEGIN
246 x_no_data_found := TRUE;
247 -- Get current database values
248 OPEN awr_pk_csr (p_awr_rec.id);
249 FETCH awr_pk_csr INTO
250 l_awr_rec.ID,
251 l_awr_rec.ANT_ID,
252 l_awr_rec.RUL_ID,
253 l_awr_rec.SORT_SEQUENCE,
254 l_awr_rec.OBJECT_VERSION_NUMBER,
255 l_awr_rec.ATTRIBUTE_CATEGORY,
256 l_awr_rec.ATTRIBUTE1,
257 l_awr_rec.ATTRIBUTE2,
258 l_awr_rec.ATTRIBUTE3,
259 l_awr_rec.ATTRIBUTE4,
260 l_awr_rec.ATTRIBUTE5,
261 l_awr_rec.ATTRIBUTE6,
262 l_awr_rec.ATTRIBUTE7,
263 l_awr_rec.ATTRIBUTE8,
264 l_awr_rec.ATTRIBUTE9,
265 l_awr_rec.ATTRIBUTE10,
266 l_awr_rec.ATTRIBUTE11,
267 l_awr_rec.ATTRIBUTE12,
268 l_awr_rec.ATTRIBUTE13,
269 l_awr_rec.ATTRIBUTE14,
270 l_awr_rec.ATTRIBUTE15,
271 l_awr_rec.CREATED_BY,
272 l_awr_rec.CREATION_DATE,
273 l_awr_rec.LAST_UPDATED_BY,
274 l_awr_rec.LAST_UPDATE_DATE,
275 l_awr_rec.LAST_UPDATE_LOGIN;
276 x_no_data_found := awr_pk_csr%NOTFOUND;
277 CLOSE awr_pk_csr;
278 RETURN(l_awr_rec);
279 END get_rec;
280
281 FUNCTION get_rec (
282 p_awr_rec IN awr_rec_type
283 ) RETURN awr_rec_type IS
284 l_row_notfound BOOLEAN := TRUE;
285 BEGIN
286 RETURN(get_rec(p_awr_rec, l_row_notfound));
287 END get_rec;
288 ---------------------------------------------------------------------------
289 -- FUNCTION get_rec for: OKL_ANSWERS_V
290 ---------------------------------------------------------------------------
291 FUNCTION get_rec (
292 p_awrv_rec IN awrv_rec_type,
293 x_no_data_found OUT NOCOPY BOOLEAN
294 ) RETURN awrv_rec_type IS
295 CURSOR okl_awrv_pk_csr (p_id IN NUMBER) IS
296 SELECT
297 ID,
298 OBJECT_VERSION_NUMBER,
299 ANT_ID,
303 ATTRIBUTE1,
300 RUL_ID,
301 SORT_SEQUENCE,
302 ATTRIBUTE_CATEGORY,
304 ATTRIBUTE2,
305 ATTRIBUTE3,
306 ATTRIBUTE4,
307 ATTRIBUTE5,
308 ATTRIBUTE6,
309 ATTRIBUTE7,
310 ATTRIBUTE8,
311 ATTRIBUTE9,
312 ATTRIBUTE10,
313 ATTRIBUTE11,
314 ATTRIBUTE12,
315 ATTRIBUTE13,
316 ATTRIBUTE14,
317 ATTRIBUTE15,
318 CREATED_BY,
319 CREATION_DATE,
320 LAST_UPDATED_BY,
321 LAST_UPDATE_DATE,
322 LAST_UPDATE_LOGIN
323 FROM Okl_Answers_V
324 WHERE okl_answers_v.id = p_id;
325 l_okl_awrv_pk okl_awrv_pk_csr%ROWTYPE;
326 l_awrv_rec awrv_rec_type;
327 BEGIN
328 x_no_data_found := TRUE;
329 -- Get current database values
330 OPEN okl_awrv_pk_csr (p_awrv_rec.id);
331 FETCH okl_awrv_pk_csr INTO
332 l_awrv_rec.ID,
333 l_awrv_rec.OBJECT_VERSION_NUMBER,
334 l_awrv_rec.ANT_ID,
335 l_awrv_rec.RUL_ID,
336 l_awrv_rec.SORT_SEQUENCE,
337 l_awrv_rec.ATTRIBUTE_CATEGORY,
338 l_awrv_rec.ATTRIBUTE1,
339 l_awrv_rec.ATTRIBUTE2,
340 l_awrv_rec.ATTRIBUTE3,
341 l_awrv_rec.ATTRIBUTE4,
342 l_awrv_rec.ATTRIBUTE5,
343 l_awrv_rec.ATTRIBUTE6,
344 l_awrv_rec.ATTRIBUTE7,
345 l_awrv_rec.ATTRIBUTE8,
346 l_awrv_rec.ATTRIBUTE9,
347 l_awrv_rec.ATTRIBUTE10,
348 l_awrv_rec.ATTRIBUTE11,
349 l_awrv_rec.ATTRIBUTE12,
350 l_awrv_rec.ATTRIBUTE13,
351 l_awrv_rec.ATTRIBUTE14,
352 l_awrv_rec.ATTRIBUTE15,
353 l_awrv_rec.CREATED_BY,
354 l_awrv_rec.CREATION_DATE,
355 l_awrv_rec.LAST_UPDATED_BY,
356 l_awrv_rec.LAST_UPDATE_DATE,
357 l_awrv_rec.LAST_UPDATE_LOGIN;
358 x_no_data_found := okl_awrv_pk_csr%NOTFOUND;
359 CLOSE okl_awrv_pk_csr;
360 RETURN(l_awrv_rec);
361 END get_rec;
362
363 FUNCTION get_rec (
364 p_awrv_rec IN awrv_rec_type
365 ) RETURN awrv_rec_type IS
366 l_row_notfound BOOLEAN := TRUE;
367 BEGIN
368 RETURN(get_rec(p_awrv_rec, l_row_notfound));
369 END get_rec;
370
371 ---------------------------------------------------
372 -- FUNCTION null_out_defaults for: OKL_ANSWERS_V --
373 ---------------------------------------------------
374 FUNCTION null_out_defaults (
375 p_awrv_rec IN awrv_rec_type
376 ) RETURN awrv_rec_type IS
377 l_awrv_rec awrv_rec_type := p_awrv_rec;
378 BEGIN
379 IF (l_awrv_rec.object_version_number = okl_api.G_MISS_NUM) THEN
380 l_awrv_rec.object_version_number := NULL;
381 END IF;
382 IF (l_awrv_rec.ant_id = okl_api.G_MISS_NUM) THEN
383 l_awrv_rec.ant_id := NULL;
384 END IF;
385 IF (l_awrv_rec.rul_id = okl_api.G_MISS_NUM) THEN
386 l_awrv_rec.rul_id := NULL;
387 END IF;
388 IF (l_awrv_rec.sort_sequence = okl_api.G_MISS_NUM) THEN
389 l_awrv_rec.sort_sequence := NULL;
390 END IF;
391 IF (l_awrv_rec.attribute_category = okl_api.G_MISS_CHAR) THEN
392 l_awrv_rec.attribute_category := NULL;
393 END IF;
394 IF (l_awrv_rec.attribute1 = okl_api.G_MISS_CHAR) THEN
395 l_awrv_rec.attribute1 := NULL;
396 END IF;
397 IF (l_awrv_rec.attribute2 = okl_api.G_MISS_CHAR) THEN
398 l_awrv_rec.attribute2 := NULL;
399 END IF;
400 IF (l_awrv_rec.attribute3 = okl_api.G_MISS_CHAR) THEN
401 l_awrv_rec.attribute3 := NULL;
402 END IF;
403 IF (l_awrv_rec.attribute4 = okl_api.G_MISS_CHAR) THEN
404 l_awrv_rec.attribute4 := NULL;
405 END IF;
406 IF (l_awrv_rec.attribute5 = okl_api.G_MISS_CHAR) THEN
407 l_awrv_rec.attribute5 := NULL;
408 END IF;
409 IF (l_awrv_rec.attribute6 = okl_api.G_MISS_CHAR) THEN
410 l_awrv_rec.attribute6 := NULL;
411 END IF;
412 IF (l_awrv_rec.attribute7 = okl_api.G_MISS_CHAR) THEN
413 l_awrv_rec.attribute7 := NULL;
414 END IF;
415 IF (l_awrv_rec.attribute8 = okl_api.G_MISS_CHAR) THEN
416 l_awrv_rec.attribute8 := NULL;
417 END IF;
418 IF (l_awrv_rec.attribute9 = okl_api.G_MISS_CHAR) THEN
419 l_awrv_rec.attribute9 := NULL;
420 END IF;
421 IF (l_awrv_rec.attribute10 = okl_api.G_MISS_CHAR) THEN
422 l_awrv_rec.attribute10 := NULL;
423 END IF;
424 IF (l_awrv_rec.attribute11 = okl_api.G_MISS_CHAR) THEN
425 l_awrv_rec.attribute11 := NULL;
426 END IF;
427 IF (l_awrv_rec.attribute12 = okl_api.G_MISS_CHAR) THEN
428 l_awrv_rec.attribute12 := NULL;
429 END IF;
433 IF (l_awrv_rec.attribute14 = okl_api.G_MISS_CHAR) THEN
430 IF (l_awrv_rec.attribute13 = okl_api.G_MISS_CHAR) THEN
431 l_awrv_rec.attribute13 := NULL;
432 END IF;
434 l_awrv_rec.attribute14 := NULL;
435 END IF;
436 IF (l_awrv_rec.attribute15 = okl_api.G_MISS_CHAR) THEN
437 l_awrv_rec.attribute15 := NULL;
438 END IF;
439 IF (l_awrv_rec.created_by = okl_api.G_MISS_NUM) THEN
440 l_awrv_rec.created_by := NULL;
441 END IF;
442 IF (l_awrv_rec.creation_date = okl_api.G_MISS_DATE) THEN
443 l_awrv_rec.creation_date := NULL;
444 END IF;
445 IF (l_awrv_rec.last_updated_by = okl_api.G_MISS_NUM) THEN
446 l_awrv_rec.last_updated_by := NULL;
447 END IF;
448 IF (l_awrv_rec.last_update_date = okl_api.G_MISS_DATE) THEN
449 l_awrv_rec.last_update_date := NULL;
450 END IF;
451 IF (l_awrv_rec.last_update_login = okl_api.G_MISS_NUM) THEN
452 l_awrv_rec.last_update_login := NULL;
453 END IF;
454 RETURN(l_awrv_rec);
455 END null_out_defaults;
456 ---------------------------------------------------------------------------
457 -- PROCEDURE Validate_Attributes
458 ---------------------------------------------------------------------------
459 -------------------------------------------
460 -- Validate_Attributes for:OKL_ANSWERS_V --
461 -------------------------------------------
462 FUNCTION Validate_Attributes (
463 p_awrv_rec IN awrv_rec_type
464 ) RETURN VARCHAR2 IS
465 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
466 -- TAPI postgen 05/23/2001
467 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
468 -- End TAPI postgen 05/23/2001
469 BEGIN
470 -- TAPI postgen 05/23/2001
471 validate_id(p_awrv_rec, x_return_status);
472 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
473 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
474 l_return_status := x_return_status;
475 RAISE G_EXCEPTION_HALT_VALIDATION;
476 ELSE
477 l_return_status := x_return_status; -- record that there was an error
478 END IF;
479 END IF;
480
481 validate_object_version_number(p_awrv_rec, x_return_status);
482 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
483 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
484 l_return_status := x_return_status;
485 RAISE G_EXCEPTION_HALT_VALIDATION;
486 ELSE
487 l_return_status := x_return_status; -- record that there was an error
488 END IF;
489 END IF;
490
491 validate_ant_id(p_awrv_rec, x_return_status);
492 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
493 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
494 l_return_status := x_return_status;
495 RAISE G_EXCEPTION_HALT_VALIDATION;
496 ELSE
497 l_return_status := x_return_status; -- record that there was an error
498 END IF;
502 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
499 END IF;
500
501 validate_rul_id(p_awrv_rec, x_return_status);
503 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
504 l_return_status := x_return_status;
505 RAISE G_EXCEPTION_HALT_VALIDATION;
506 ELSE
507 l_return_status := x_return_status; -- record that there was an error
508 END IF;
509 END IF;
510
511 validate_sort_sequence(p_awrv_rec, x_return_status);
512 IF (x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
513 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
514 l_return_status := x_return_status;
515 RAISE G_EXCEPTION_HALT_VALIDATION;
516 ELSE
517 l_return_status := x_return_status; -- record that there was an error
518 END IF;
519 END IF;
520 -- End TAPI postgen 05/23/2001
521
522 RETURN(l_return_status);
523 END Validate_Attributes;
524
525 ---------------------------------------------------------------------------
526 -- PROCEDURE Validate_Record
527 ---------------------------------------------------------------------------
528 ---------------------------------------
529 -- Validate_Record for:OKL_ANSWERS_V --
530 ---------------------------------------
531 FUNCTION Validate_Record (
532 p_awrv_rec IN awrv_rec_type
533 ) RETURN VARCHAR2 IS
534 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
535 BEGIN
536 RETURN (l_return_status);
537 END Validate_Record;
538
539 ---------------------------------------------------------------------------
540 -- PROCEDURE Migrate
541 ---------------------------------------------------------------------------
542 PROCEDURE migrate (
543 p_from IN awrv_rec_type,
544 p_to OUT NOCOPY awr_rec_type
545 ) IS
546 BEGIN
547 p_to.id := p_from.id;
548 p_to.ant_id := p_from.ant_id;
549 p_to.rul_id := p_from.rul_id;
550 p_to.sort_sequence := p_from.sort_sequence;
551 p_to.object_version_number := p_from.object_version_number;
552 p_to.attribute_category := p_from.attribute_category;
553 p_to.attribute1 := p_from.attribute1;
554 p_to.attribute2 := p_from.attribute2;
555 p_to.attribute3 := p_from.attribute3;
556 p_to.attribute4 := p_from.attribute4;
557 p_to.attribute5 := p_from.attribute5;
558 p_to.attribute6 := p_from.attribute6;
559 p_to.attribute7 := p_from.attribute7;
560 p_to.attribute8 := p_from.attribute8;
561 p_to.attribute9 := p_from.attribute9;
562 p_to.attribute10 := p_from.attribute10;
563 p_to.attribute11 := p_from.attribute11;
564 p_to.attribute12 := p_from.attribute12;
565 p_to.attribute13 := p_from.attribute13;
566 p_to.attribute14 := p_from.attribute14;
567 p_to.attribute15 := p_from.attribute15;
568 p_to.created_by := p_from.created_by;
569 p_to.creation_date := p_from.creation_date;
570 p_to.last_updated_by := p_from.last_updated_by;
571 p_to.last_update_date := p_from.last_update_date;
572 p_to.last_update_login := p_from.last_update_login;
573 END migrate;
574 PROCEDURE migrate (
578 BEGIN
575 p_from IN awr_rec_type,
576 p_to OUT NOCOPY awrv_rec_type
577 ) IS
579 p_to.id := p_from.id;
580 p_to.ant_id := p_from.ant_id;
581 p_to.rul_id := p_from.rul_id;
582 p_to.sort_sequence := p_from.sort_sequence;
583 p_to.object_version_number := p_from.object_version_number;
584 p_to.attribute_category := p_from.attribute_category;
585 p_to.attribute1 := p_from.attribute1;
586 p_to.attribute2 := p_from.attribute2;
587 p_to.attribute3 := p_from.attribute3;
588 p_to.attribute4 := p_from.attribute4;
589 p_to.attribute5 := p_from.attribute5;
590 p_to.attribute6 := p_from.attribute6;
591 p_to.attribute7 := p_from.attribute7;
592 p_to.attribute8 := p_from.attribute8;
593 p_to.attribute9 := p_from.attribute9;
594 p_to.attribute10 := p_from.attribute10;
595 p_to.attribute11 := p_from.attribute11;
596 p_to.attribute12 := p_from.attribute12;
597 p_to.attribute13 := p_from.attribute13;
598 p_to.attribute14 := p_from.attribute14;
599 p_to.attribute15 := p_from.attribute15;
600 p_to.created_by := p_from.created_by;
601 p_to.creation_date := p_from.creation_date;
602 p_to.last_updated_by := p_from.last_updated_by;
603 p_to.last_update_date := p_from.last_update_date;
604 p_to.last_update_login := p_from.last_update_login;
605 END migrate;
606
607 ---------------------------------------------------------------------------
608 -- PROCEDURE validate_row
609 ---------------------------------------------------------------------------
610 ------------------------------------
611 -- validate_row for:OKL_ANSWERS_V --
612 ------------------------------------
613 PROCEDURE validate_row(
614 p_api_version IN NUMBER,
615 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
616 x_return_status OUT NOCOPY VARCHAR2,
617 x_msg_count OUT NOCOPY NUMBER,
618 x_msg_data OUT NOCOPY VARCHAR2,
619 p_awrv_rec IN awrv_rec_type) IS
620
621 l_api_version CONSTANT NUMBER := 1;
622 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
623 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
624 l_awrv_rec awrv_rec_type := p_awrv_rec;
625 l_awr_rec awr_rec_type;
626 BEGIN
627 l_return_status := okl_api.START_ACTIVITY(l_api_name,
628 G_PKG_NAME,
629 p_init_msg_list,
630 l_api_version,
631 p_api_version,
632 '_PVT',
633 x_return_status);
634 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
635 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
636 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
637 RAISE okl_api.G_EXCEPTION_ERROR;
641 --- If any errors happen abort API
638 END IF;
639 --- Validate all non-missing attributes (Item Level Validation)
640 l_return_status := Validate_Attributes(l_awrv_rec);
642 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
643 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
644 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
645 RAISE okl_api.G_EXCEPTION_ERROR;
646 END IF;
647 l_return_status := Validate_Record(l_awrv_rec);
648 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
649 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
650 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
651 RAISE okl_api.G_EXCEPTION_ERROR;
652 END IF;
653 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
654 EXCEPTION
655 WHEN okl_api.G_EXCEPTION_ERROR THEN
656 x_return_status := okl_api.HANDLE_EXCEPTIONS
657 (
658 l_api_name,
659 G_PKG_NAME,
660 'okl_api.G_RET_STS_ERROR',
661 x_msg_count,
662 x_msg_data,
663 '_PVT'
664 );
665 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
666 x_return_status :=okl_api.HANDLE_EXCEPTIONS
667 (
668 l_api_name,
669 G_PKG_NAME,
670 'okl_api.G_RET_STS_UNEXP_ERROR',
671 x_msg_count,
672 x_msg_data,
673 '_PVT'
674 );
675 WHEN OTHERS THEN
676 x_return_status :=okl_api.HANDLE_EXCEPTIONS
677 (
678 l_api_name,
679 G_PKG_NAME,
680 'OTHERS',
681 x_msg_count,
682 x_msg_data,
683 '_PVT'
684 );
685 END validate_row;
686 ------------------------------------------
687 -- PL/SQL TBL validate_row for:AWRV_TBL --
688 ------------------------------------------
689 PROCEDURE validate_row(
690 p_api_version IN NUMBER,
691 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
692 x_return_status OUT NOCOPY VARCHAR2,
693 x_msg_count OUT NOCOPY NUMBER,
694 x_msg_data OUT NOCOPY VARCHAR2,
695 p_awrv_tbl IN awrv_tbl_type) IS
696
697 l_api_version CONSTANT NUMBER := 1;
698 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
699 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
700 i NUMBER := 0;
701 BEGIN
702 okl_api.init_msg_list(p_init_msg_list);
703 -- Make sure PL/SQL table has records in it before passing
704 IF (p_awrv_tbl.COUNT > 0) THEN
705 i := p_awrv_tbl.FIRST;
706 LOOP
707 validate_row (
708 p_api_version => p_api_version,
709 p_init_msg_list => okl_api.G_FALSE,
710 x_return_status => x_return_status,
711 x_msg_count => x_msg_count,
712 x_msg_data => x_msg_data,
713 p_awrv_rec => p_awrv_tbl(i));
714 EXIT WHEN (i = p_awrv_tbl.LAST);
715 i := p_awrv_tbl.NEXT(i);
716 END LOOP;
717 END IF;
718 EXCEPTION
719 WHEN okl_api.G_EXCEPTION_ERROR THEN
720 x_return_status := okl_api.HANDLE_EXCEPTIONS
721 (
722 l_api_name,
723 G_PKG_NAME,
724 'okl_api.G_RET_STS_ERROR',
725 x_msg_count,
726 x_msg_data,
727 '_PVT'
728 );
729 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
730 x_return_status :=okl_api.HANDLE_EXCEPTIONS
731 (
732 l_api_name,
733 G_PKG_NAME,
734 'okl_api.G_RET_STS_UNEXP_ERROR',
735 x_msg_count,
736 x_msg_data,
737 '_PVT'
738 );
739 WHEN OTHERS THEN
740 x_return_status :=okl_api.HANDLE_EXCEPTIONS
741 (
742 l_api_name,
743 G_PKG_NAME,
744 'OTHERS',
745 x_msg_count,
746 x_msg_data,
747 '_PVT'
748 );
749 END validate_row;
750
751 ---------------------------------------------------------------------------
752 -- PROCEDURE insert_row
756 --------------------------------
753 ---------------------------------------------------------------------------
754 --------------------------------
755 -- insert_row for:OKL_ANSWERS --
757 PROCEDURE insert_row(
758 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
759 x_return_status OUT NOCOPY VARCHAR2,
760 x_msg_count OUT NOCOPY NUMBER,
761 x_msg_data OUT NOCOPY VARCHAR2,
762 p_awr_rec IN awr_rec_type,
763 x_awr_rec OUT NOCOPY awr_rec_type) IS
764
765 l_api_version CONSTANT NUMBER := 1;
766 l_api_name CONSTANT VARCHAR2(30) := 'ANSWERS_insert_row';
767 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
768 l_awr_rec awr_rec_type := p_awr_rec;
769 l_def_awr_rec awr_rec_type;
770 ------------------------------------
771 -- Set_Attributes for:OKL_ANSWERS --
772 ------------------------------------
773 FUNCTION Set_Attributes (
774 p_awr_rec IN awr_rec_type,
775 x_awr_rec OUT NOCOPY awr_rec_type
776 ) RETURN VARCHAR2 IS
777 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
778 BEGIN
779 x_awr_rec := p_awr_rec;
780 RETURN(l_return_status);
781 END Set_Attributes;
782 BEGIN
783 l_return_status := okl_api.START_ACTIVITY(l_api_name,
784 p_init_msg_list,
785 '_PVT',
786 x_return_status);
787 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
788 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
789 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
790 RAISE okl_api.G_EXCEPTION_ERROR;
791 END IF;
792 --- Setting item attributes
793 l_return_status := Set_Attributes(
794 p_awr_rec, -- IN
795 l_awr_rec); -- OUT
796 --- If any errors happen abort API
797 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
798 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
799 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
800 RAISE okl_api.G_EXCEPTION_ERROR;
801 END IF;
802 INSERT INTO OKL_ANSWERS(
803 id,
804 ant_id,
805 rul_id,
806 sort_sequence,
807 object_version_number,
808 attribute_category,
809 attribute1,
810 attribute2,
811 attribute3,
812 attribute4,
813 attribute5,
814 attribute6,
815 attribute7,
816 attribute8,
817 attribute9,
818 attribute10,
819 attribute11,
820 attribute12,
821 attribute13,
822 attribute14,
823 attribute15,
824 created_by,
825 creation_date,
826 last_updated_by,
827 last_update_date,
828 last_update_login)
829 VALUES (
830 l_awr_rec.id,
831 l_awr_rec.ant_id,
832 l_awr_rec.rul_id,
833 l_awr_rec.sort_sequence,
834 l_awr_rec.object_version_number,
835 l_awr_rec.attribute_category,
836 l_awr_rec.attribute1,
837 l_awr_rec.attribute2,
838 l_awr_rec.attribute3,
839 l_awr_rec.attribute4,
840 l_awr_rec.attribute5,
841 l_awr_rec.attribute6,
842 l_awr_rec.attribute7,
843 l_awr_rec.attribute8,
844 l_awr_rec.attribute9,
845 l_awr_rec.attribute10,
846 l_awr_rec.attribute11,
847 l_awr_rec.attribute12,
848 l_awr_rec.attribute13,
849 l_awr_rec.attribute14,
850 l_awr_rec.attribute15,
851 l_awr_rec.created_by,
852 l_awr_rec.creation_date,
853 l_awr_rec.last_updated_by,
854 l_awr_rec.last_update_date,
855 l_awr_rec.last_update_login);
856 -- Set OUT values
857 x_awr_rec := l_awr_rec;
858 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
859 EXCEPTION
860 WHEN okl_api.G_EXCEPTION_ERROR THEN
861 x_return_status := okl_api.HANDLE_EXCEPTIONS
862 (
863 l_api_name,
864 G_PKG_NAME,
865 'okl_api.G_RET_STS_ERROR',
866 x_msg_count,
867 x_msg_data,
868 '_PVT'
869 );
870 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
871 x_return_status :=okl_api.HANDLE_EXCEPTIONS
872 (
873 l_api_name,
874 G_PKG_NAME,
875 'okl_api.G_RET_STS_UNEXP_ERROR',
876 x_msg_count,
877 x_msg_data,
878 '_PVT'
879 );
880 WHEN OTHERS THEN
881 x_return_status :=okl_api.HANDLE_EXCEPTIONS
882 (
883 l_api_name,
884 G_PKG_NAME,
885 'OTHERS',
886 x_msg_count,
887 x_msg_data,
888 '_PVT'
889 );
890 END insert_row;
891 ----------------------------------
892 -- insert_row for:OKL_ANSWERS_V --
893 ----------------------------------
894 PROCEDURE insert_row(
895 p_api_version IN NUMBER,
896 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
897 x_return_status OUT NOCOPY VARCHAR2,
898 x_msg_count OUT NOCOPY NUMBER,
902
899 x_msg_data OUT NOCOPY VARCHAR2,
900 p_awrv_rec IN awrv_rec_type,
901 x_awrv_rec OUT NOCOPY awrv_rec_type) IS
903 l_api_version CONSTANT NUMBER := 1;
904 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
905 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
906 l_awrv_rec awrv_rec_type;
907 l_def_awrv_rec awrv_rec_type;
908 l_awr_rec awr_rec_type;
909 lx_awr_rec awr_rec_type;
910 -------------------------------
911 -- FUNCTION fill_who_columns --
912 -------------------------------
913 FUNCTION fill_who_columns (
914 p_awrv_rec IN awrv_rec_type
915 ) RETURN awrv_rec_type IS
916 l_awrv_rec awrv_rec_type := p_awrv_rec;
917 BEGIN
918 l_awrv_rec.CREATION_DATE := SYSDATE;
919 l_awrv_rec.CREATED_BY := Fnd_Global.USER_ID;
920 l_awrv_rec.LAST_UPDATE_DATE := SYSDATE;
921 l_awrv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
922 l_awrv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
923 RETURN(l_awrv_rec);
924 END fill_who_columns;
925 --------------------------------------
926 -- Set_Attributes for:OKL_ANSWERS_V --
927 --------------------------------------
928 FUNCTION Set_Attributes (
929 p_awrv_rec IN awrv_rec_type,
930 x_awrv_rec OUT NOCOPY awrv_rec_type
931 ) RETURN VARCHAR2 IS
932 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
933 BEGIN
934 x_awrv_rec := p_awrv_rec;
935 x_awrv_rec.OBJECT_VERSION_NUMBER := 1;
936 RETURN(l_return_status);
937 END Set_Attributes;
938 BEGIN
939 l_return_status := okl_api.START_ACTIVITY(l_api_name,
940 G_PKG_NAME,
941 p_init_msg_list,
942 l_api_version,
943 p_api_version,
944 '_PVT',
945 x_return_status);
946 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
947 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
948 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
949 RAISE okl_api.G_EXCEPTION_ERROR;
950 END IF;
951 l_awrv_rec := null_out_defaults(p_awrv_rec);
952 -- Set primary key value
953 l_awrv_rec.ID := get_seq_id;
954 --- Setting item attributes
955 l_return_status := Set_Attributes(
956 l_awrv_rec, -- IN
957 l_def_awrv_rec); -- OUT
958 --- If any errors happen abort API
959 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
960 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
961 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
962 RAISE okl_api.G_EXCEPTION_ERROR;
963 END IF;
964 l_def_awrv_rec := fill_who_columns(l_def_awrv_rec);
965 --- Validate all non-missing attributes (Item Level Validation)
966 l_return_status := Validate_Attributes(l_def_awrv_rec);
967 --- If any errors happen abort API
968 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
969 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
970 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
971 RAISE okl_api.G_EXCEPTION_ERROR;
972 END IF;
973 l_return_status := Validate_Record(l_def_awrv_rec);
974 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
975 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
976 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
977 RAISE okl_api.G_EXCEPTION_ERROR;
978 END IF;
979 --------------------------------------
980 -- Move VIEW record to "Child" records
981 --------------------------------------
982 migrate(l_def_awrv_rec, l_awr_rec);
983 --------------------------------------------
984 -- Call the INSERT_ROW for each child record
985 --------------------------------------------
986 insert_row(
987 p_init_msg_list,
988 x_return_status,
989 x_msg_count,
990 x_msg_data,
991 l_awr_rec,
992 lx_awr_rec
993 );
994 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
995 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
996 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
997 RAISE okl_api.G_EXCEPTION_ERROR;
998 END IF;
999 migrate(lx_awr_rec, l_def_awrv_rec);
1000 -- Set OUT values
1001 x_awrv_rec := l_def_awrv_rec;
1002 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1003 EXCEPTION
1004 WHEN okl_api.G_EXCEPTION_ERROR THEN
1005 x_return_status := okl_api.HANDLE_EXCEPTIONS
1006 (
1007 l_api_name,
1008 G_PKG_NAME,
1009 'okl_api.G_RET_STS_ERROR',
1010 x_msg_count,
1011 x_msg_data,
1012 '_PVT'
1013 );
1014 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1015 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1016 (
1017 l_api_name,
1018 G_PKG_NAME,
1019 'okl_api.G_RET_STS_UNEXP_ERROR',
1020 x_msg_count,
1021 x_msg_data,
1022 '_PVT'
1023 );
1027 l_api_name,
1024 WHEN OTHERS THEN
1025 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1026 (
1028 G_PKG_NAME,
1029 'OTHERS',
1030 x_msg_count,
1031 x_msg_data,
1032 '_PVT'
1033 );
1034 END insert_row;
1035 ----------------------------------------
1036 -- PL/SQL TBL insert_row for:AWRV_TBL --
1037 ----------------------------------------
1038 PROCEDURE insert_row(
1039 p_api_version IN NUMBER,
1040 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1041 x_return_status OUT NOCOPY VARCHAR2,
1042 x_msg_count OUT NOCOPY NUMBER,
1043 x_msg_data OUT NOCOPY VARCHAR2,
1044 p_awrv_tbl IN awrv_tbl_type,
1045 x_awrv_tbl OUT NOCOPY awrv_tbl_type) IS
1046
1047 l_api_version CONSTANT NUMBER := 1;
1048 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1049 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1050 i NUMBER := 0;
1051 BEGIN
1052 okl_api.init_msg_list(p_init_msg_list);
1053 -- Make sure PL/SQL table has records in it before passing
1054 IF (p_awrv_tbl.COUNT > 0) THEN
1055 i := p_awrv_tbl.FIRST;
1056 LOOP
1057 insert_row (
1058 p_api_version => p_api_version,
1059 p_init_msg_list => okl_api.G_FALSE,
1060 x_return_status => x_return_status,
1061 x_msg_count => x_msg_count,
1062 x_msg_data => x_msg_data,
1063 p_awrv_rec => p_awrv_tbl(i),
1064 x_awrv_rec => x_awrv_tbl(i));
1065 EXIT WHEN (i = p_awrv_tbl.LAST);
1066 i := p_awrv_tbl.NEXT(i);
1067 END LOOP;
1068 END IF;
1069 EXCEPTION
1070 WHEN okl_api.G_EXCEPTION_ERROR THEN
1071 x_return_status := okl_api.HANDLE_EXCEPTIONS
1072 (
1073 l_api_name,
1074 G_PKG_NAME,
1075 'okl_api.G_RET_STS_ERROR',
1076 x_msg_count,
1077 x_msg_data,
1078 '_PVT'
1079 );
1080 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1081 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1082 (
1083 l_api_name,
1084 G_PKG_NAME,
1085 'okl_api.G_RET_STS_UNEXP_ERROR',
1086 x_msg_count,
1087 x_msg_data,
1088 '_PVT'
1089 );
1090 WHEN OTHERS THEN
1091 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1092 (
1093 l_api_name,
1094 G_PKG_NAME,
1095 'OTHERS',
1096 x_msg_count,
1097 x_msg_data,
1098 '_PVT'
1099 );
1100 END insert_row;
1101
1102 ---------------------------------------------------------------------------
1103 -- PROCEDURE lock_row
1104 ---------------------------------------------------------------------------
1105 ------------------------------
1106 -- lock_row for:OKL_ANSWERS --
1107 ------------------------------
1108 PROCEDURE lock_row(
1109 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1110 x_return_status OUT NOCOPY VARCHAR2,
1111 x_msg_count OUT NOCOPY NUMBER,
1112 x_msg_data OUT NOCOPY VARCHAR2,
1113 p_awr_rec IN awr_rec_type) IS
1114
1115 E_Resource_Busy EXCEPTION;
1116 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1117 CURSOR lock_csr (p_awr_rec IN awr_rec_type) IS
1118 SELECT OBJECT_VERSION_NUMBER
1119 FROM OKL_ANSWERS
1120 WHERE ID = p_awr_rec.id
1121 AND OBJECT_VERSION_NUMBER = p_awr_rec.object_version_number
1122 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1123
1124 CURSOR lchk_csr (p_awr_rec IN awr_rec_type) IS
1125 SELECT OBJECT_VERSION_NUMBER
1126 FROM OKL_ANSWERS
1127 WHERE ID = p_awr_rec.id;
1128 l_api_version CONSTANT NUMBER := 1;
1129 l_api_name CONSTANT VARCHAR2(30) := 'ANSWERS_lock_row';
1133 l_row_notfound BOOLEAN := FALSE;
1130 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1131 l_object_version_number OKL_ANSWERS.OBJECT_VERSION_NUMBER%TYPE;
1132 lc_object_version_number OKL_ANSWERS.OBJECT_VERSION_NUMBER%TYPE;
1134 lc_row_notfound BOOLEAN := FALSE;
1135 BEGIN
1136 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1137 p_init_msg_list,
1138 '_PVT',
1139 x_return_status);
1140 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1141 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1142 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1143 RAISE okl_api.G_EXCEPTION_ERROR;
1144 END IF;
1145 BEGIN
1146 OPEN lock_csr(p_awr_rec);
1147 FETCH lock_csr INTO l_object_version_number;
1148 l_row_notfound := lock_csr%NOTFOUND;
1149 CLOSE lock_csr;
1150 EXCEPTION
1151 WHEN E_Resource_Busy THEN
1152 IF (lock_csr%ISOPEN) THEN
1153 CLOSE lock_csr;
1154 END IF;
1155 okl_api.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1156 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
1157 END;
1158
1159 IF ( l_row_notfound ) THEN
1160 OPEN lchk_csr(p_awr_rec);
1161 FETCH lchk_csr INTO lc_object_version_number;
1162 lc_row_notfound := lchk_csr%NOTFOUND;
1163 CLOSE lchk_csr;
1164 END IF;
1165 IF (lc_row_notfound) THEN
1166 okl_api.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1167 RAISE okl_api.G_EXCEPTION_ERROR;
1168 ELSIF lc_object_version_number > p_awr_rec.object_version_number THEN
1169 okl_api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1170 RAISE okl_api.G_EXCEPTION_ERROR;
1171 ELSIF lc_object_version_number <> p_awr_rec.object_version_number THEN
1172 okl_api.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1173 RAISE okl_api.G_EXCEPTION_ERROR;
1174 ELSIF lc_object_version_number = -1 THEN
1175 okl_api.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1176 RAISE okl_api.G_EXCEPTION_ERROR;
1177 END IF;
1178 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1179 EXCEPTION
1180 WHEN okl_api.G_EXCEPTION_ERROR THEN
1181 x_return_status := okl_api.HANDLE_EXCEPTIONS
1182 (
1183 l_api_name,
1184 G_PKG_NAME,
1185 'okl_api.G_RET_STS_ERROR',
1186 x_msg_count,
1187 x_msg_data,
1188 '_PVT'
1189 );
1190 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1191 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1192 (
1193 l_api_name,
1194 G_PKG_NAME,
1195 'okl_api.G_RET_STS_UNEXP_ERROR',
1196 x_msg_count,
1197 x_msg_data,
1198 '_PVT'
1199 );
1200 WHEN OTHERS THEN
1201 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1202 (
1203 l_api_name,
1204 G_PKG_NAME,
1205 'OTHERS',
1206 x_msg_count,
1207 x_msg_data,
1208 '_PVT'
1209 );
1210 END lock_row;
1211 --------------------------------
1212 -- lock_row for:OKL_ANSWERS_V --
1213 --------------------------------
1214 PROCEDURE lock_row(
1215 p_api_version IN NUMBER,
1216 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1217 x_return_status OUT NOCOPY VARCHAR2,
1218 x_msg_count OUT NOCOPY NUMBER,
1219 x_msg_data OUT NOCOPY VARCHAR2,
1220 p_awrv_rec IN awrv_rec_type) IS
1221
1222 l_api_version CONSTANT NUMBER := 1;
1223 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1224 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1225 l_awr_rec awr_rec_type;
1226 BEGIN
1227 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1228 G_PKG_NAME,
1229 p_init_msg_list,
1230 l_api_version,
1231 p_api_version,
1232 '_PVT',
1233 x_return_status);
1234 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1235 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1236 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1237 RAISE okl_api.G_EXCEPTION_ERROR;
1238 END IF;
1239 --------------------------------------
1240 -- Move VIEW record to "Child" records
1241 --------------------------------------
1242 migrate(p_awrv_rec, l_awr_rec);
1243 --------------------------------------------
1244 -- Call the LOCK_ROW for each child record
1245 --------------------------------------------
1246 lock_row(
1247 p_init_msg_list,
1248 x_return_status,
1249 x_msg_count,
1250 x_msg_data,
1251 l_awr_rec
1252 );
1253 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1254 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1255 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1256 RAISE okl_api.G_EXCEPTION_ERROR;
1257 END IF;
1258 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1259 EXCEPTION
1260 WHEN okl_api.G_EXCEPTION_ERROR THEN
1261 x_return_status := okl_api.HANDLE_EXCEPTIONS
1262 (
1263 l_api_name,
1264 G_PKG_NAME,
1265 'okl_api.G_RET_STS_ERROR',
1266 x_msg_count,
1267 x_msg_data,
1268 '_PVT'
1269 );
1270 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1271 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1272 (
1273 l_api_name,
1274 G_PKG_NAME,
1275 'okl_api.G_RET_STS_UNEXP_ERROR',
1276 x_msg_count,
1277 x_msg_data,
1278 '_PVT'
1279 );
1280 WHEN OTHERS THEN
1281 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1282 (
1283 l_api_name,
1284 G_PKG_NAME,
1285 'OTHERS',
1286 x_msg_count,
1287 x_msg_data,
1288 '_PVT'
1289 );
1290 END lock_row;
1291 --------------------------------------
1292 -- PL/SQL TBL lock_row for:AWRV_TBL --
1293 --------------------------------------
1294 PROCEDURE lock_row(
1295 p_api_version IN NUMBER,
1296 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1297 x_return_status OUT NOCOPY VARCHAR2,
1298 x_msg_count OUT NOCOPY NUMBER,
1299 x_msg_data OUT NOCOPY VARCHAR2,
1300 p_awrv_tbl IN awrv_tbl_type) IS
1301
1302 l_api_version CONSTANT NUMBER := 1;
1303 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1304 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1305 i NUMBER := 0;
1306 BEGIN
1310 i := p_awrv_tbl.FIRST;
1307 okl_api.init_msg_list(p_init_msg_list);
1308 -- Make sure PL/SQL table has records in it before passing
1309 IF (p_awrv_tbl.COUNT > 0) THEN
1311 LOOP
1312 lock_row (
1313 p_api_version => p_api_version,
1314 p_init_msg_list => okl_api.G_FALSE,
1315 x_return_status => x_return_status,
1316 x_msg_count => x_msg_count,
1317 x_msg_data => x_msg_data,
1318 p_awrv_rec => p_awrv_tbl(i));
1319 EXIT WHEN (i = p_awrv_tbl.LAST);
1320 i := p_awrv_tbl.NEXT(i);
1321 END LOOP;
1322 END IF;
1323 EXCEPTION
1324 WHEN okl_api.G_EXCEPTION_ERROR THEN
1325 x_return_status := okl_api.HANDLE_EXCEPTIONS
1326 (
1327 l_api_name,
1328 G_PKG_NAME,
1329 'okl_api.G_RET_STS_ERROR',
1330 x_msg_count,
1331 x_msg_data,
1332 '_PVT'
1333 );
1334 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1335 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1336 (
1337 l_api_name,
1338 G_PKG_NAME,
1339 'okl_api.G_RET_STS_UNEXP_ERROR',
1340 x_msg_count,
1341 x_msg_data,
1342 '_PVT'
1343 );
1344 WHEN OTHERS THEN
1345 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1346 (
1347 l_api_name,
1348 G_PKG_NAME,
1349 'OTHERS',
1353 );
1350 x_msg_count,
1351 x_msg_data,
1352 '_PVT'
1354 END lock_row;
1355
1356 ---------------------------------------------------------------------------
1357 -- PROCEDURE update_row
1358 ---------------------------------------------------------------------------
1359 --------------------------------
1360 -- update_row for:OKL_ANSWERS --
1361 --------------------------------
1362 PROCEDURE update_row(
1363 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1364 x_return_status OUT NOCOPY VARCHAR2,
1365 x_msg_count OUT NOCOPY NUMBER,
1366 x_msg_data OUT NOCOPY VARCHAR2,
1367 p_awr_rec IN awr_rec_type,
1368 x_awr_rec OUT NOCOPY awr_rec_type) IS
1369
1370 l_api_version CONSTANT NUMBER := 1;
1371 l_api_name CONSTANT VARCHAR2(30) := 'ANSWERS_update_row';
1372 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1373 l_awr_rec awr_rec_type := p_awr_rec;
1374 l_def_awr_rec awr_rec_type;
1375 l_row_notfound BOOLEAN := TRUE;
1376 ----------------------------------
1377 -- FUNCTION populate_new_record --
1378 ----------------------------------
1379 FUNCTION populate_new_record (
1380 p_awr_rec IN awr_rec_type,
1381 x_awr_rec OUT NOCOPY awr_rec_type
1382 ) RETURN VARCHAR2 IS
1383 l_awr_rec awr_rec_type;
1384 l_row_notfound BOOLEAN := TRUE;
1385 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1386 BEGIN
1387 x_awr_rec := p_awr_rec;
1388 -- Get current database values
1389 l_awr_rec := get_rec(p_awr_rec, l_row_notfound);
1390 IF (l_row_notfound) THEN
1391 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
1392 END IF;
1393 IF (x_awr_rec.id = okl_api.G_MISS_NUM)
1394 THEN
1395 x_awr_rec.id := l_awr_rec.id;
1396 END IF;
1397 IF (x_awr_rec.ant_id = okl_api.G_MISS_NUM)
1398 THEN
1399 x_awr_rec.ant_id := l_awr_rec.ant_id;
1400 END IF;
1401 IF (x_awr_rec.rul_id = okl_api.G_MISS_NUM)
1402 THEN
1403 x_awr_rec.rul_id := l_awr_rec.rul_id;
1404 END IF;
1405 IF (x_awr_rec.sort_sequence = okl_api.G_MISS_NUM)
1406 THEN
1407 x_awr_rec.sort_sequence := l_awr_rec.sort_sequence;
1408 END IF;
1409 IF (x_awr_rec.object_version_number = okl_api.G_MISS_NUM)
1410 THEN
1411 x_awr_rec.object_version_number := l_awr_rec.object_version_number;
1412 END IF;
1413 IF (x_awr_rec.attribute_category = okl_api.G_MISS_CHAR)
1414 THEN
1415 x_awr_rec.attribute_category := l_awr_rec.attribute_category;
1416 END IF;
1417 IF (x_awr_rec.attribute1 = okl_api.G_MISS_CHAR)
1418 THEN
1419 x_awr_rec.attribute1 := l_awr_rec.attribute1;
1420 END IF;
1421 IF (x_awr_rec.attribute2 = okl_api.G_MISS_CHAR)
1422 THEN
1423 x_awr_rec.attribute2 := l_awr_rec.attribute2;
1424 END IF;
1425 IF (x_awr_rec.attribute3 = okl_api.G_MISS_CHAR)
1426 THEN
1427 x_awr_rec.attribute3 := l_awr_rec.attribute3;
1428 END IF;
1429 IF (x_awr_rec.attribute4 = okl_api.G_MISS_CHAR)
1430 THEN
1431 x_awr_rec.attribute4 := l_awr_rec.attribute4;
1432 END IF;
1433 IF (x_awr_rec.attribute5 = okl_api.G_MISS_CHAR)
1434 THEN
1435 x_awr_rec.attribute5 := l_awr_rec.attribute5;
1436 END IF;
1437 IF (x_awr_rec.attribute6 = okl_api.G_MISS_CHAR)
1438 THEN
1439 x_awr_rec.attribute6 := l_awr_rec.attribute6;
1440 END IF;
1441 IF (x_awr_rec.attribute7 = okl_api.G_MISS_CHAR)
1442 THEN
1443 x_awr_rec.attribute7 := l_awr_rec.attribute7;
1444 END IF;
1445 IF (x_awr_rec.attribute8 = okl_api.G_MISS_CHAR)
1446 THEN
1447 x_awr_rec.attribute8 := l_awr_rec.attribute8;
1448 END IF;
1449 IF (x_awr_rec.attribute9 = okl_api.G_MISS_CHAR)
1450 THEN
1451 x_awr_rec.attribute9 := l_awr_rec.attribute9;
1452 END IF;
1453 IF (x_awr_rec.attribute10 = okl_api.G_MISS_CHAR)
1454 THEN
1455 x_awr_rec.attribute10 := l_awr_rec.attribute10;
1456 END IF;
1457 IF (x_awr_rec.attribute11 = okl_api.G_MISS_CHAR)
1458 THEN
1459 x_awr_rec.attribute11 := l_awr_rec.attribute11;
1460 END IF;
1461 IF (x_awr_rec.attribute12 = okl_api.G_MISS_CHAR)
1462 THEN
1463 x_awr_rec.attribute12 := l_awr_rec.attribute12;
1464 END IF;
1465 IF (x_awr_rec.attribute13 = okl_api.G_MISS_CHAR)
1466 THEN
1467 x_awr_rec.attribute13 := l_awr_rec.attribute13;
1468 END IF;
1469 IF (x_awr_rec.attribute14 = okl_api.G_MISS_CHAR)
1470 THEN
1471 x_awr_rec.attribute14 := l_awr_rec.attribute14;
1472 END IF;
1473 IF (x_awr_rec.attribute15 = okl_api.G_MISS_CHAR)
1474 THEN
1475 x_awr_rec.attribute15 := l_awr_rec.attribute15;
1476 END IF;
1477 IF (x_awr_rec.created_by = okl_api.G_MISS_NUM)
1478 THEN
1479 x_awr_rec.created_by := l_awr_rec.created_by;
1480 END IF;
1481 IF (x_awr_rec.creation_date = okl_api.G_MISS_DATE)
1482 THEN
1483 x_awr_rec.creation_date := l_awr_rec.creation_date;
1484 END IF;
1485 IF (x_awr_rec.last_updated_by = okl_api.G_MISS_NUM)
1486 THEN
1490 THEN
1487 x_awr_rec.last_updated_by := l_awr_rec.last_updated_by;
1488 END IF;
1489 IF (x_awr_rec.last_update_date = okl_api.G_MISS_DATE)
1491 x_awr_rec.last_update_date := l_awr_rec.last_update_date;
1492 END IF;
1493 IF (x_awr_rec.last_update_login = okl_api.G_MISS_NUM)
1494 THEN
1495 x_awr_rec.last_update_login := l_awr_rec.last_update_login;
1496 END IF;
1497 RETURN(l_return_status);
1498 END populate_new_record;
1499 ------------------------------------
1500 -- Set_Attributes for:OKL_ANSWERS --
1501 ------------------------------------
1502 FUNCTION Set_Attributes (
1503 p_awr_rec IN awr_rec_type,
1504 x_awr_rec OUT NOCOPY awr_rec_type
1505 ) RETURN VARCHAR2 IS
1506 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1507 BEGIN
1508 x_awr_rec := p_awr_rec;
1509 RETURN(l_return_status);
1510 END Set_Attributes;
1511 BEGIN
1512 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1513 p_init_msg_list,
1514 '_PVT',
1515 x_return_status);
1516 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1517 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1518 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1519 RAISE okl_api.G_EXCEPTION_ERROR;
1520 END IF;
1521 --- Setting item attributes
1522 l_return_status := Set_Attributes(
1523 p_awr_rec, -- IN
1524 l_awr_rec); -- OUT
1525 --- If any errors happen abort API
1526 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1527 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1528 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1529 RAISE okl_api.G_EXCEPTION_ERROR;
1530 END IF;
1531 l_return_status := populate_new_record(l_awr_rec, l_def_awr_rec);
1532 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1533 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1534 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1535 RAISE okl_api.G_EXCEPTION_ERROR;
1536 END IF;
1537 UPDATE OKL_ANSWERS
1538 SET ANT_ID = l_def_awr_rec.ant_id,
1539 RUL_ID = l_def_awr_rec.rul_id,
1540 SORT_SEQUENCE = l_def_awr_rec.sort_sequence,
1541 OBJECT_VERSION_NUMBER = l_def_awr_rec.object_version_number,
1542 ATTRIBUTE_CATEGORY = l_def_awr_rec.attribute_category,
1543 ATTRIBUTE1 = l_def_awr_rec.attribute1,
1544 ATTRIBUTE2 = l_def_awr_rec.attribute2,
1545 ATTRIBUTE3 = l_def_awr_rec.attribute3,
1546 ATTRIBUTE4 = l_def_awr_rec.attribute4,
1547 ATTRIBUTE5 = l_def_awr_rec.attribute5,
1548 ATTRIBUTE6 = l_def_awr_rec.attribute6,
1549 ATTRIBUTE7 = l_def_awr_rec.attribute7,
1550 ATTRIBUTE8 = l_def_awr_rec.attribute8,
1551 ATTRIBUTE9 = l_def_awr_rec.attribute9,
1552 ATTRIBUTE10 = l_def_awr_rec.attribute10,
1553 ATTRIBUTE11 = l_def_awr_rec.attribute11,
1554 ATTRIBUTE12 = l_def_awr_rec.attribute12,
1555 ATTRIBUTE13 = l_def_awr_rec.attribute13,
1556 ATTRIBUTE14 = l_def_awr_rec.attribute14,
1557 ATTRIBUTE15 = l_def_awr_rec.attribute15,
1558 CREATED_BY = l_def_awr_rec.created_by,
1559 CREATION_DATE = l_def_awr_rec.creation_date,
1560 LAST_UPDATED_BY = l_def_awr_rec.last_updated_by,
1561 LAST_UPDATE_DATE = l_def_awr_rec.last_update_date,
1562 LAST_UPDATE_LOGIN = l_def_awr_rec.last_update_login
1563 WHERE ID = l_def_awr_rec.id;
1564
1565 x_awr_rec := l_def_awr_rec;
1566 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1567 EXCEPTION
1568 WHEN okl_api.G_EXCEPTION_ERROR THEN
1569 x_return_status := okl_api.HANDLE_EXCEPTIONS
1570 (
1571 l_api_name,
1572 G_PKG_NAME,
1573 'okl_api.G_RET_STS_ERROR',
1574 x_msg_count,
1575 x_msg_data,
1576 '_PVT'
1577 );
1578 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1579 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1580 (
1581 l_api_name,
1582 G_PKG_NAME,
1583 'okl_api.G_RET_STS_UNEXP_ERROR',
1584 x_msg_count,
1585 x_msg_data,
1586 '_PVT'
1587 );
1588 WHEN OTHERS THEN
1589 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1590 (
1591 l_api_name,
1592 G_PKG_NAME,
1593 'OTHERS',
1594 x_msg_count,
1595 x_msg_data,
1596 '_PVT'
1597 );
1598 END update_row;
1599 ----------------------------------
1600 -- update_row for:OKL_ANSWERS_V --
1601 ----------------------------------
1602 PROCEDURE update_row(
1603 p_api_version IN NUMBER,
1604 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1605 x_return_status OUT NOCOPY VARCHAR2,
1606 x_msg_count OUT NOCOPY NUMBER,
1607 x_msg_data OUT NOCOPY VARCHAR2,
1608 p_awrv_rec IN awrv_rec_type,
1609 x_awrv_rec OUT NOCOPY awrv_rec_type) IS
1610
1611 l_api_version CONSTANT NUMBER := 1;
1612 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1613 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1614 l_awrv_rec awrv_rec_type := p_awrv_rec;
1615 l_def_awrv_rec awrv_rec_type;
1616 l_awr_rec awr_rec_type;
1617 lx_awr_rec awr_rec_type;
1618 -------------------------------
1619 -- FUNCTION fill_who_columns --
1620 -------------------------------
1621 FUNCTION fill_who_columns (
1622 p_awrv_rec IN awrv_rec_type
1623 ) RETURN awrv_rec_type IS
1624 l_awrv_rec awrv_rec_type := p_awrv_rec;
1625 BEGIN
1626 l_awrv_rec.LAST_UPDATE_DATE := SYSDATE;
1627 l_awrv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1628 l_awrv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1629 RETURN(l_awrv_rec);
1630 END fill_who_columns;
1631 ----------------------------------
1632 -- FUNCTION populate_new_record --
1633 ----------------------------------
1634 FUNCTION populate_new_record (
1635 p_awrv_rec IN awrv_rec_type,
1636 x_awrv_rec OUT NOCOPY awrv_rec_type
1637 ) RETURN VARCHAR2 IS
1638 l_awrv_rec awrv_rec_type;
1639 l_row_notfound BOOLEAN := TRUE;
1640 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1641 BEGIN
1642 x_awrv_rec := p_awrv_rec;
1643 -- Get current database values
1644 l_awrv_rec := get_rec(p_awrv_rec, l_row_notfound);
1645 IF (l_row_notfound) THEN
1646 l_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
1647 END IF;
1648 IF (x_awrv_rec.id = okl_api.G_MISS_NUM)
1649 THEN
1650 x_awrv_rec.id := l_awrv_rec.id;
1651 END IF;
1652 IF (x_awrv_rec.object_version_number = okl_api.G_MISS_NUM)
1653 THEN
1654 x_awrv_rec.object_version_number := l_awrv_rec.object_version_number;
1655 END IF;
1656 IF (x_awrv_rec.ant_id = okl_api.G_MISS_NUM)
1657 THEN
1658 x_awrv_rec.ant_id := l_awrv_rec.ant_id;
1659 END IF;
1660 IF (x_awrv_rec.rul_id = okl_api.G_MISS_NUM)
1661 THEN
1662 x_awrv_rec.rul_id := l_awrv_rec.rul_id;
1663 END IF;
1664 IF (x_awrv_rec.sort_sequence = okl_api.G_MISS_NUM)
1665 THEN
1666 x_awrv_rec.sort_sequence := l_awrv_rec.sort_sequence;
1667 END IF;
1668 IF (x_awrv_rec.attribute_category = okl_api.G_MISS_CHAR)
1669 THEN
1670 x_awrv_rec.attribute_category := l_awrv_rec.attribute_category;
1671 END IF;
1672 IF (x_awrv_rec.attribute1 = okl_api.G_MISS_CHAR)
1673 THEN
1674 x_awrv_rec.attribute1 := l_awrv_rec.attribute1;
1675 END IF;
1676 IF (x_awrv_rec.attribute2 = okl_api.G_MISS_CHAR)
1677 THEN
1678 x_awrv_rec.attribute2 := l_awrv_rec.attribute2;
1679 END IF;
1680 IF (x_awrv_rec.attribute3 = okl_api.G_MISS_CHAR)
1681 THEN
1682 x_awrv_rec.attribute3 := l_awrv_rec.attribute3;
1683 END IF;
1684 IF (x_awrv_rec.attribute4 = okl_api.G_MISS_CHAR)
1685 THEN
1686 x_awrv_rec.attribute4 := l_awrv_rec.attribute4;
1687 END IF;
1688 IF (x_awrv_rec.attribute5 = okl_api.G_MISS_CHAR)
1689 THEN
1690 x_awrv_rec.attribute5 := l_awrv_rec.attribute5;
1691 END IF;
1692 IF (x_awrv_rec.attribute6 = okl_api.G_MISS_CHAR)
1693 THEN
1694 x_awrv_rec.attribute6 := l_awrv_rec.attribute6;
1695 END IF;
1696 IF (x_awrv_rec.attribute7 = okl_api.G_MISS_CHAR)
1697 THEN
1698 x_awrv_rec.attribute7 := l_awrv_rec.attribute7;
1699 END IF;
1700 IF (x_awrv_rec.attribute8 = okl_api.G_MISS_CHAR)
1701 THEN
1702 x_awrv_rec.attribute8 := l_awrv_rec.attribute8;
1703 END IF;
1704 IF (x_awrv_rec.attribute9 = okl_api.G_MISS_CHAR)
1705 THEN
1706 x_awrv_rec.attribute9 := l_awrv_rec.attribute9;
1707 END IF;
1708 IF (x_awrv_rec.attribute10 = okl_api.G_MISS_CHAR)
1709 THEN
1710 x_awrv_rec.attribute10 := l_awrv_rec.attribute10;
1711 END IF;
1712 IF (x_awrv_rec.attribute11 = okl_api.G_MISS_CHAR)
1713 THEN
1714 x_awrv_rec.attribute11 := l_awrv_rec.attribute11;
1715 END IF;
1716 IF (x_awrv_rec.attribute12 = okl_api.G_MISS_CHAR)
1717 THEN
1718 x_awrv_rec.attribute12 := l_awrv_rec.attribute12;
1719 END IF;
1720 IF (x_awrv_rec.attribute13 = okl_api.G_MISS_CHAR)
1721 THEN
1722 x_awrv_rec.attribute13 := l_awrv_rec.attribute13;
1723 END IF;
1724 IF (x_awrv_rec.attribute14 = okl_api.G_MISS_CHAR)
1725 THEN
1726 x_awrv_rec.attribute14 := l_awrv_rec.attribute14;
1727 END IF;
1728 IF (x_awrv_rec.attribute15 = okl_api.G_MISS_CHAR)
1729 THEN
1730 x_awrv_rec.attribute15 := l_awrv_rec.attribute15;
1731 END IF;
1732 IF (x_awrv_rec.created_by = okl_api.G_MISS_NUM)
1733 THEN
1734 x_awrv_rec.created_by := l_awrv_rec.created_by;
1735 END IF;
1736 IF (x_awrv_rec.creation_date = okl_api.G_MISS_DATE)
1737 THEN
1738 x_awrv_rec.creation_date := l_awrv_rec.creation_date;
1739 END IF;
1740 IF (x_awrv_rec.last_updated_by = okl_api.G_MISS_NUM)
1741 THEN
1742 x_awrv_rec.last_updated_by := l_awrv_rec.last_updated_by;
1743 END IF;
1744 IF (x_awrv_rec.last_update_date = okl_api.G_MISS_DATE)
1745 THEN
1746 x_awrv_rec.last_update_date := l_awrv_rec.last_update_date;
1747 END IF;
1748 IF (x_awrv_rec.last_update_login = okl_api.G_MISS_NUM)
1749 THEN
1750 x_awrv_rec.last_update_login := l_awrv_rec.last_update_login;
1751 END IF;
1752 RETURN(l_return_status);
1753 END populate_new_record;
1754 --------------------------------------
1755 -- Set_Attributes for:OKL_ANSWERS_V --
1756 --------------------------------------
1757 FUNCTION Set_Attributes (
1758 p_awrv_rec IN awrv_rec_type,
1759 x_awrv_rec OUT NOCOPY awrv_rec_type
1760 ) RETURN VARCHAR2 IS
1761 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1762 BEGIN
1763 x_awrv_rec := p_awrv_rec;
1764 x_awrv_rec.OBJECT_VERSION_NUMBER := NVL(x_awrv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1765 RETURN(l_return_status);
1766 END Set_Attributes;
1767 BEGIN
1768 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1769 G_PKG_NAME,
1770 p_init_msg_list,
1771 l_api_version,
1772 p_api_version,
1773 '_PVT',
1774 x_return_status);
1775 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1776 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1777 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1778 RAISE okl_api.G_EXCEPTION_ERROR;
1779 END IF;
1780 --- Setting item attributes
1781 l_return_status := Set_Attributes(
1782 p_awrv_rec, -- IN
1783 l_awrv_rec); -- OUT
1784 --- If any errors happen abort API
1785 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1786 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1787 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1788 RAISE okl_api.G_EXCEPTION_ERROR;
1789 END IF;
1790 l_return_status := populate_new_record(l_awrv_rec, l_def_awrv_rec);
1791 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1792 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1793 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1794 RAISE okl_api.G_EXCEPTION_ERROR;
1795 END IF;
1796 l_def_awrv_rec := fill_who_columns(l_def_awrv_rec);
1797 --- Validate all non-missing attributes (Item Level Validation)
1798 l_return_status := Validate_Attributes(l_def_awrv_rec);
1799 --- If any errors happen abort API
1800 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1801 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1802 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1803 RAISE okl_api.G_EXCEPTION_ERROR;
1804 END IF;
1805 l_return_status := Validate_Record(l_def_awrv_rec);
1806 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1807 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1808 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1809 RAISE okl_api.G_EXCEPTION_ERROR;
1810 END IF;
1811
1815 migrate(l_def_awrv_rec, l_awr_rec);
1812 --------------------------------------
1813 -- Move VIEW record to "Child" records
1814 --------------------------------------
1816 --------------------------------------------
1817 -- Call the UPDATE_ROW for each child record
1818 --------------------------------------------
1819 update_row(
1820 p_init_msg_list,
1821 x_return_status,
1822 x_msg_count,
1823 x_msg_data,
1824 l_awr_rec,
1825 lx_awr_rec
1826 );
1827 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1828 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1829 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
1830 RAISE okl_api.G_EXCEPTION_ERROR;
1831 END IF;
1832 migrate(lx_awr_rec, l_def_awrv_rec);
1833 x_awrv_rec := l_def_awrv_rec;
1834 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1835 EXCEPTION
1836 WHEN okl_api.G_EXCEPTION_ERROR THEN
1837 x_return_status := okl_api.HANDLE_EXCEPTIONS
1838 (
1839 l_api_name,
1840 G_PKG_NAME,
1841 'okl_api.G_RET_STS_ERROR',
1842 x_msg_count,
1843 x_msg_data,
1844 '_PVT'
1845 );
1846 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1847 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1848 (
1849 l_api_name,
1850 G_PKG_NAME,
1851 'okl_api.G_RET_STS_UNEXP_ERROR',
1852 x_msg_count,
1853 x_msg_data,
1854 '_PVT'
1855 );
1856 WHEN OTHERS THEN
1857 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1858 (
1859 l_api_name,
1860 G_PKG_NAME,
1861 'OTHERS',
1862 x_msg_count,
1863 x_msg_data,
1864 '_PVT'
1865 );
1866 END update_row;
1867 ----------------------------------------
1868 -- PL/SQL TBL update_row for:AWRV_TBL --
1869 ----------------------------------------
1870 PROCEDURE update_row(
1871 p_api_version IN NUMBER,
1872 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1873 x_return_status OUT NOCOPY VARCHAR2,
1874 x_msg_count OUT NOCOPY NUMBER,
1875 x_msg_data OUT NOCOPY VARCHAR2,
1876 p_awrv_tbl IN awrv_tbl_type,
1877 x_awrv_tbl OUT NOCOPY awrv_tbl_type) IS
1878
1879 l_api_version CONSTANT NUMBER := 1;
1880 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1881 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1882 i NUMBER := 0;
1883 BEGIN
1884 okl_api.init_msg_list(p_init_msg_list);
1885 -- Make sure PL/SQL table has records in it before passing
1886 IF (p_awrv_tbl.COUNT > 0) THEN
1887 i := p_awrv_tbl.FIRST;
1888 LOOP
1889 update_row (
1890 p_api_version => p_api_version,
1891 p_init_msg_list => okl_api.G_FALSE,
1892 x_return_status => x_return_status,
1893 x_msg_count => x_msg_count,
1894 x_msg_data => x_msg_data,
1895 p_awrv_rec => p_awrv_tbl(i),
1896 x_awrv_rec => x_awrv_tbl(i));
1897 EXIT WHEN (i = p_awrv_tbl.LAST);
1898 i := p_awrv_tbl.NEXT(i);
1899 END LOOP;
1900 END IF;
1901 EXCEPTION
1902 WHEN okl_api.G_EXCEPTION_ERROR THEN
1903 x_return_status := okl_api.HANDLE_EXCEPTIONS
1904 (
1905 l_api_name,
1906 G_PKG_NAME,
1907 'okl_api.G_RET_STS_ERROR',
1908 x_msg_count,
1909 x_msg_data,
1910 '_PVT'
1911 );
1912 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1913 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1914 (
1915 l_api_name,
1916 G_PKG_NAME,
1917 'okl_api.G_RET_STS_UNEXP_ERROR',
1918 x_msg_count,
1919 x_msg_data,
1920 '_PVT'
1921 );
1922 WHEN OTHERS THEN
1923 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1924 (
1925 l_api_name,
1926 G_PKG_NAME,
1927 'OTHERS',
1928 x_msg_count,
1929 x_msg_data,
1930 '_PVT'
1931 );
1932 END update_row;
1933
1934 ---------------------------------------------------------------------------
1935 -- PROCEDURE delete_row
1936 ---------------------------------------------------------------------------
1937 --------------------------------
1938 -- delete_row for:OKL_ANSWERS --
1939 --------------------------------
1940 PROCEDURE delete_row(
1941 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
1942 x_return_status OUT NOCOPY VARCHAR2,
1943 x_msg_count OUT NOCOPY NUMBER,
1944 x_msg_data OUT NOCOPY VARCHAR2,
1945 p_awr_rec IN awr_rec_type) IS
1946
1947 l_api_version CONSTANT NUMBER := 1;
1948 l_api_name CONSTANT VARCHAR2(30) := 'ANSWERS_delete_row';
1949 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
1950 l_awr_rec awr_rec_type:= p_awr_rec;
1951 l_row_notfound BOOLEAN := TRUE;
1952 BEGIN
1953 l_return_status := okl_api.START_ACTIVITY(l_api_name,
1954 p_init_msg_list,
1955 '_PVT',
1956 x_return_status);
1957 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
1961 END IF;
1958 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
1959 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
1960 RAISE okl_api.G_EXCEPTION_ERROR;
1962 DELETE FROM OKL_ANSWERS
1963 WHERE ID = l_awr_rec.id;
1964
1965 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
1966 EXCEPTION
1967 WHEN okl_api.G_EXCEPTION_ERROR THEN
1968 x_return_status := okl_api.HANDLE_EXCEPTIONS
1969 (
1970 l_api_name,
1971 G_PKG_NAME,
1972 'okl_api.G_RET_STS_ERROR',
1973 x_msg_count,
1974 x_msg_data,
1975 '_PVT'
1976 );
1977 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1978 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1979 (
1980 l_api_name,
1981 G_PKG_NAME,
1982 'okl_api.G_RET_STS_UNEXP_ERROR',
1983 x_msg_count,
1984 x_msg_data,
1985 '_PVT'
1986 );
1987 WHEN OTHERS THEN
1988 x_return_status :=okl_api.HANDLE_EXCEPTIONS
1989 (
1990 l_api_name,
1991 G_PKG_NAME,
1992 'OTHERS',
1993 x_msg_count,
1994 x_msg_data,
1995 '_PVT'
1996 );
1997 END delete_row;
1998 ----------------------------------
1999 -- delete_row for:OKL_ANSWERS_V --
2000 ----------------------------------
2001 PROCEDURE delete_row(
2002 p_api_version IN NUMBER,
2003 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2004 x_return_status OUT NOCOPY VARCHAR2,
2005 x_msg_count OUT NOCOPY NUMBER,
2006 x_msg_data OUT NOCOPY VARCHAR2,
2007 p_awrv_rec IN awrv_rec_type) IS
2008
2009 l_api_version CONSTANT NUMBER := 1;
2010 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2011 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2012 l_awrv_rec awrv_rec_type := p_awrv_rec;
2013 l_awr_rec awr_rec_type;
2014 BEGIN
2015 l_return_status := okl_api.START_ACTIVITY(l_api_name,
2016 G_PKG_NAME,
2017 p_init_msg_list,
2018 l_api_version,
2019 p_api_version,
2020 '_PVT',
2021 x_return_status);
2022 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2023 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2024 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
2025 RAISE okl_api.G_EXCEPTION_ERROR;
2026 END IF;
2027 --------------------------------------
2028 -- Move VIEW record to "Child" records
2029 --------------------------------------
2030 migrate(l_awrv_rec, l_awr_rec);
2031 --------------------------------------------
2032 -- Call the DELETE_ROW for each child record
2033 --------------------------------------------
2034 delete_row(
2035 p_init_msg_list,
2036 x_return_status,
2037 x_msg_count,
2038 x_msg_data,
2039 l_awr_rec
2040 );
2041 IF (x_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
2042 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
2043 ELSIF (x_return_status = okl_api.G_RET_STS_ERROR) THEN
2044 RAISE okl_api.G_EXCEPTION_ERROR;
2045 END IF;
2046 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2047 EXCEPTION
2048 WHEN okl_api.G_EXCEPTION_ERROR THEN
2049 x_return_status := okl_api.HANDLE_EXCEPTIONS
2050 (
2051 l_api_name,
2052 G_PKG_NAME,
2053 'okl_api.G_RET_STS_ERROR',
2054 x_msg_count,
2055 x_msg_data,
2056 '_PVT'
2057 );
2058 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2059 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2060 (
2061 l_api_name,
2062 G_PKG_NAME,
2063 'okl_api.G_RET_STS_UNEXP_ERROR',
2064 x_msg_count,
2065 x_msg_data,
2066 '_PVT'
2067 );
2068 WHEN OTHERS THEN
2069 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2070 (
2071 l_api_name,
2072 G_PKG_NAME,
2073 'OTHERS',
2074 x_msg_count,
2075 x_msg_data,
2076 '_PVT'
2077 );
2078 END delete_row;
2079 ----------------------------------------
2080 -- PL/SQL TBL delete_row for:AWRV_TBL --
2081 ----------------------------------------
2082 PROCEDURE delete_row(
2083 p_api_version IN NUMBER,
2084 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
2085 x_return_status OUT NOCOPY VARCHAR2,
2086 x_msg_count OUT NOCOPY NUMBER,
2087 x_msg_data OUT NOCOPY VARCHAR2,
2088 p_awrv_tbl IN awrv_tbl_type) IS
2089
2090 l_api_version CONSTANT NUMBER := 1;
2091 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2092 l_return_status VARCHAR2(1) := okl_api.G_RET_STS_SUCCESS;
2093 i NUMBER := 0;
2094 BEGIN
2095 okl_api.init_msg_list(p_init_msg_list);
2096 -- Make sure PL/SQL table has records in it before passing
2097 IF (p_awrv_tbl.COUNT > 0) THEN
2098 i := p_awrv_tbl.FIRST;
2099 LOOP
2100 delete_row (
2101 p_api_version => p_api_version,
2102 p_init_msg_list => okl_api.G_FALSE,
2103 x_return_status => x_return_status,
2104 x_msg_count => x_msg_count,
2105 x_msg_data => x_msg_data,
2106 p_awrv_rec => p_awrv_tbl(i));
2107 EXIT WHEN (i = p_awrv_tbl.LAST);
2108 i := p_awrv_tbl.NEXT(i);
2109 END LOOP;
2110 END IF;
2111 EXCEPTION
2112 WHEN okl_api.G_EXCEPTION_ERROR THEN
2113 x_return_status := okl_api.HANDLE_EXCEPTIONS
2114 (
2115 l_api_name,
2116 G_PKG_NAME,
2117 'okl_api.G_RET_STS_ERROR',
2118 x_msg_count,
2119 x_msg_data,
2120 '_PVT'
2121 );
2122 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2123 x_return_status :=okl_api.HANDLE_EXCEPTIONS
2124 (
2125 l_api_name,
2126 G_PKG_NAME,
2127 'okl_api.G_RET_STS_UNEXP_ERROR',
2128 x_msg_count,
2129 x_msg_data,
2130 '_PVT'
2131 );
2132 WHEN OTHERS THEN
2133 x_return_status :=Okl_Api.HANDLE_EXCEPTIONS
2134 (
2135 l_api_name,
2136 G_PKG_NAME,
2137 'OTHERS',
2138 x_msg_count,
2139 x_msg_data,
2140 '_PVT'
2141 );
2142 END delete_row;
2143 END Okl_Awr_Pvt;