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