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