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