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