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