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