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