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