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