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