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