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