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