[Home] [Help]
PACKAGE BODY: APPS.OKL_ICP_PVT
Source
1 PACKAGE BODY OKL_ICP_PVT AS
2 /* $Header: OKLSICPB.pls 120.3 2005/07/08 07:06:45 smadhava noship $ */
3
4 ----------
5 -- get_rec
6 ----------
7 FUNCTION get_rec (p_id IN NUMBER,
8 x_return_status OUT NOCOPY VARCHAR2) RETURN icpv_rec_type IS
9
10 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'get_rec';
11
12 l_icpv_rec icpv_rec_type;
13
14 BEGIN
15
16 /* smadhava - Pricing Enhancements - Modified - Start */
17 SELECT
18 ID,
19 OBJECT_VERSION_NUMBER,
20 CAT_ID1,
21 CAT_ID2,
22 TERM_IN_MONTHS,
23 RESIDUAL_VALUE_PERCENT,
24 ITEM_RESIDUAL_ID,
25 STS_CODE,
26 VERSION_NUMBER,
27 START_DATE,
28 END_DATE,
29 CREATED_BY,
30 CREATION_DATE,
31 LAST_UPDATED_BY,
32 LAST_UPDATE_DATE,
33 LAST_UPDATE_LOGIN,
34 ATTRIBUTE_CATEGORY,
35 ATTRIBUTE1,
36 ATTRIBUTE2,
37 ATTRIBUTE3,
38 ATTRIBUTE4,
39 ATTRIBUTE5,
40 ATTRIBUTE6,
41 ATTRIBUTE7,
42 ATTRIBUTE8,
43 ATTRIBUTE9,
44 ATTRIBUTE10,
45 ATTRIBUTE11,
46 ATTRIBUTE12,
47 ATTRIBUTE13,
48 ATTRIBUTE14,
49 ATTRIBUTE15
50 INTO
51 l_icpv_rec.id,
52 l_icpv_rec.object_version_number,
53 l_icpv_rec.cat_id1,
54 l_icpv_rec.cat_id2,
55 l_icpv_rec.term_in_months,
56 l_icpv_rec.residual_value_percent,
57 l_icpv_rec.item_residual_id,
58 l_icpv_rec.sts_code,
59 l_icpv_rec.version_number,
60 l_icpv_rec.start_date,
61 l_icpv_rec.end_date,
62 l_icpv_rec.created_by,
63 l_icpv_rec.creation_date,
64 l_icpv_rec.last_updated_by,
65 l_icpv_rec.last_update_date,
66 l_icpv_rec.last_update_login,
67 l_icpv_rec.attribute_category,
68 l_icpv_rec.attribute1,
69 l_icpv_rec.attribute2,
70 l_icpv_rec.attribute3,
71 l_icpv_rec.attribute4,
72 l_icpv_rec.attribute5,
73 l_icpv_rec.attribute6,
74 l_icpv_rec.attribute7,
75 l_icpv_rec.attribute8,
76 l_icpv_rec.attribute9,
77 l_icpv_rec.attribute10,
78 l_icpv_rec.attribute11,
79 l_icpv_rec.attribute12,
80 l_icpv_rec.attribute13,
81 l_icpv_rec.attribute14,
82 l_icpv_rec.attribute15
83 FROM okl_itm_cat_rv_prcs_v icpv
84 WHERE icpv.id = p_id;
85 /* smadhava - Pricing Enhancements - Modified - Start */
86 x_return_status := G_RET_STS_SUCCESS;
87 RETURN l_icpv_rec;
88
89 EXCEPTION
90
91 WHEN OKL_API.G_EXCEPTION_ERROR THEN
92
93 x_return_status := G_RET_STS_ERROR;
94
95 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
96
97 x_return_status := G_RET_STS_UNEXP_ERROR;
98
99 WHEN OTHERS THEN
100
101 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
102 p_msg_name => G_DB_ERROR,
103 p_token1 => G_PROG_NAME_TOKEN,
104 p_token1_value => l_api_name,
105 p_token2 => G_SQLCODE_TOKEN,
106 p_token2_value => sqlcode,
107 p_token3 => G_SQLERRM_TOKEN,
108 p_token3_value => sqlerrm);
109
110 x_return_status := G_RET_STS_UNEXP_ERROR;
111
112 END get_rec;
113
114
115 ---------------------------------------------------------------------------
116 -- FUNCTION null_out_defaults for: OKL_LS_RT_FCTR_ENTS_V
117 ---------------------------------------------------------------------------
118 FUNCTION null_out_defaults (p_icpv_rec IN icpv_rec_type) RETURN icpv_rec_type IS
119 l_icpv_rec icpv_rec_type := p_icpv_rec;
120 BEGIN
121 IF (l_icpv_rec.id = G_MISS_NUM ) THEN
122 l_icpv_rec.id := NULL;
123 END IF;
124 IF (l_icpv_rec.object_version_number = G_MISS_NUM ) THEN
125 l_icpv_rec.object_version_number := NULL;
126 END IF;
127 IF (l_icpv_rec.cat_id1 = G_MISS_NUM ) THEN
128 l_icpv_rec.cat_id1 := NULL;
129 END IF;
130 /* smadhava - Pricing Enhancements - Modified - Start */
131 IF (l_icpv_rec.cat_id2 = G_MISS_NUM ) THEN
132 l_icpv_rec.cat_id2 := NULL;
133 END IF;
134 /* smadhava - Pricing Enhancements - Modified - End */
135 IF (l_icpv_rec.term_in_months = G_MISS_NUM ) THEN
136 l_icpv_rec.term_in_months := NULL;
137 END IF;
138 IF (l_icpv_rec.residual_value_percent = G_MISS_NUM ) THEN
139 l_icpv_rec.residual_value_percent := NULL;
140 END IF;
141 /* smadhava - Pricing Enhancements - Added - Start */
142 IF (l_icpv_rec.item_residual_id = G_MISS_NUM ) THEN
143 l_icpv_rec.item_residual_id := NULL;
144 END IF;
145 IF (l_icpv_rec.sts_code = G_MISS_CHAR ) THEN
146 l_icpv_rec.sts_code := NULL;
147 END IF;
148 IF (l_icpv_rec.version_number = G_MISS_CHAR ) THEN
149 l_icpv_rec.version_number := NULL;
150 END IF;
151 /* smadhava - Pricing Enhancements - Added - End */
152 IF (l_icpv_rec.start_date = G_MISS_DATE ) THEN
153 l_icpv_rec.start_date := NULL;
154 END IF;
155 IF (l_icpv_rec.end_date = G_MISS_DATE ) THEN
156 l_icpv_rec.end_date := NULL;
157 END IF;
158 IF (l_icpv_rec.created_by = G_MISS_NUM ) THEN
159 l_icpv_rec.created_by := NULL;
160 END IF;
161 IF (l_icpv_rec.creation_date = G_MISS_DATE ) THEN
162 l_icpv_rec.creation_date := NULL;
163 END IF;
164 IF (l_icpv_rec.last_updated_by = G_MISS_NUM ) THEN
165 l_icpv_rec.last_updated_by := NULL;
166 END IF;
167 IF (l_icpv_rec.last_update_date = G_MISS_DATE ) THEN
168 l_icpv_rec.last_update_date := NULL;
169 END IF;
170 IF (l_icpv_rec.last_update_login = G_MISS_NUM ) THEN
171 l_icpv_rec.last_update_login := NULL;
172 END IF;
173 IF (l_icpv_rec.attribute_category = G_MISS_CHAR ) THEN
174 l_icpv_rec.attribute_category := NULL;
175 END IF;
176 IF (l_icpv_rec.attribute1 = G_MISS_CHAR ) THEN
177 l_icpv_rec.attribute1 := NULL;
178 END IF;
179 IF (l_icpv_rec.attribute2 = G_MISS_CHAR ) THEN
180 l_icpv_rec.attribute2 := NULL;
181 END IF;
182 IF (l_icpv_rec.attribute3 = G_MISS_CHAR ) THEN
183 l_icpv_rec.attribute3 := NULL;
184 END IF;
185 IF (l_icpv_rec.attribute4 = G_MISS_CHAR ) THEN
186 l_icpv_rec.attribute4 := NULL;
187 END IF;
188 IF (l_icpv_rec.attribute5 = G_MISS_CHAR ) THEN
189 l_icpv_rec.attribute5 := NULL;
190 END IF;
191 IF (l_icpv_rec.attribute6 = G_MISS_CHAR ) THEN
192 l_icpv_rec.attribute6 := NULL;
193 END IF;
194 IF (l_icpv_rec.attribute7 = G_MISS_CHAR ) THEN
195 l_icpv_rec.attribute7 := NULL;
196 END IF;
197 IF (l_icpv_rec.attribute8 = G_MISS_CHAR ) THEN
198 l_icpv_rec.attribute8 := NULL;
199 END IF;
200 IF (l_icpv_rec.attribute9 = G_MISS_CHAR ) THEN
201 l_icpv_rec.attribute9 := NULL;
202 END IF;
203 IF (l_icpv_rec.attribute10 = G_MISS_CHAR ) THEN
204 l_icpv_rec.attribute10 := NULL;
205 END IF;
206 IF (l_icpv_rec.attribute11 = G_MISS_CHAR ) THEN
207 l_icpv_rec.attribute11 := NULL;
208 END IF;
209 IF (l_icpv_rec.attribute12 = G_MISS_CHAR ) THEN
210 l_icpv_rec.attribute12 := NULL;
211 END IF;
212 IF (l_icpv_rec.attribute13 = G_MISS_CHAR ) THEN
213 l_icpv_rec.attribute13 := NULL;
214 END IF;
215 IF (l_icpv_rec.attribute14 = G_MISS_CHAR ) THEN
216 l_icpv_rec.attribute14 := NULL;
217 END IF;
218 IF (l_icpv_rec.attribute15 = G_MISS_CHAR ) THEN
219 l_icpv_rec.attribute15 := NULL;
220 END IF;
221 RETURN(l_icpv_rec);
222 END null_out_defaults;
223
224
225 ---------------------------------
226 -- PROCEDURE validate_id
227 ---------------------------------
228 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2,
229 p_id IN NUMBER) IS
230
231 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_id';
232
233 BEGIN
234
235 IF p_id IS NULL THEN
236
237 OKL_API.set_message(p_app_name => G_APP_NAME,
238 p_msg_name => G_REQUIRED_VALUE,
239 p_token1 => G_COL_NAME_TOKEN,
240 p_token1_value => 'id');
241
242 RAISE OKL_API.G_EXCEPTION_ERROR;
243
244 END IF;
245
246 x_return_status := G_RET_STS_SUCCESS;
247
248 EXCEPTION
249
250 WHEN OKL_API.G_EXCEPTION_ERROR THEN
251
252 x_return_status := G_RET_STS_ERROR;
253
254 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
255
256 x_return_status := G_RET_STS_UNEXP_ERROR;
257
258 WHEN OTHERS THEN
259
260 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
261 p_msg_name => G_DB_ERROR,
262 p_token1 => G_PROG_NAME_TOKEN,
263 p_token1_value => l_api_name,
264 p_token2 => G_SQLCODE_TOKEN,
265 p_token2_value => sqlcode,
266 p_token3 => G_SQLERRM_TOKEN,
267 p_token3_value => sqlerrm);
268
269 x_return_status := G_RET_STS_UNEXP_ERROR;
270
271 END validate_id;
272
273
274 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2,
275 p_object_version_number IN NUMBER) IS
276
277 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_object_version_number';
278
279 BEGIN
280
281 IF (p_object_version_number IS NULL) OR (p_object_version_number = G_MISS_NUM) THEN
282
283 OKL_API.set_message(p_app_name => G_APP_NAME,
284 p_msg_name => G_REQUIRED_VALUE,
285 p_token1 => G_COL_NAME_TOKEN,
286 p_token1_value => 'object_version_number');
287
288 RAISE OKL_API.G_EXCEPTION_ERROR;
289
290 END IF;
291
292 x_return_status := G_RET_STS_SUCCESS;
293
294 EXCEPTION
295
296 WHEN OKL_API.G_EXCEPTION_ERROR THEN
297
298 x_return_status := G_RET_STS_ERROR;
299
300 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
301
302 x_return_status := G_RET_STS_UNEXP_ERROR;
303
304 WHEN OTHERS THEN
305
306 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
307 p_msg_name => G_DB_ERROR,
308 p_token1 => G_PROG_NAME_TOKEN,
309 p_token1_value => l_api_name,
310 p_token2 => G_SQLCODE_TOKEN,
311 p_token2_value => sqlcode,
312 p_token3 => G_SQLERRM_TOKEN,
313 p_token3_value => sqlerrm);
314
315 x_return_status := G_RET_STS_UNEXP_ERROR;
316
317 END validate_object_version_number;
318
319
320 ---------------------------------
321 -- PROCEDURE validate_cat_id1
322 ---------------------------------
323 PROCEDURE validate_cat_id1 (x_return_status OUT NOCOPY VARCHAR2,
324 p_cat_id1 IN NUMBER) IS
325
326 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_cat_id1';
327
328 BEGIN
329
330 IF p_cat_id1 IS NULL THEN
331
332 OKL_API.set_message(p_app_name => G_APP_NAME,
333 p_msg_name => G_REQUIRED_VALUE,
334 p_token1 => G_COL_NAME_TOKEN,
335 p_token1_value => 'cat_id1');
336
337 RAISE OKL_API.G_EXCEPTION_ERROR;
338
339 END IF;
340
341 x_return_status := G_RET_STS_SUCCESS;
342
343 EXCEPTION
344
345 WHEN OKL_API.G_EXCEPTION_ERROR THEN
346
347 x_return_status := G_RET_STS_ERROR;
348
349 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
350
351 x_return_status := G_RET_STS_UNEXP_ERROR;
352
353 WHEN OTHERS THEN
354
355 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
356 p_msg_name => G_DB_ERROR,
357 p_token1 => G_PROG_NAME_TOKEN,
358 p_token1_value => l_api_name,
359 p_token2 => G_SQLCODE_TOKEN,
360 p_token2_value => sqlcode,
361 p_token3 => G_SQLERRM_TOKEN,
362 p_token3_value => sqlerrm);
363
364 x_return_status := G_RET_STS_UNEXP_ERROR;
365
366 END validate_cat_id1;
367
368
369 ---------------------------------
370 -- PROCEDURE validate_cat_id2
371 ---------------------------------
372 PROCEDURE validate_cat_id2 (x_return_status OUT NOCOPY VARCHAR2,
373 p_cat_id2 IN NUMBER) IS
374
375 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_cat_id2';
376
377 BEGIN
378
379 IF p_cat_id2 IS NULL THEN
380
381 OKL_API.set_message(p_app_name => G_APP_NAME,
382 p_msg_name => G_REQUIRED_VALUE,
383 p_token1 => G_COL_NAME_TOKEN,
384 p_token1_value => 'cat_id2');
385
386 RAISE OKL_API.G_EXCEPTION_ERROR;
387
388 END IF;
389
390 x_return_status := G_RET_STS_SUCCESS;
391
392 EXCEPTION
393
394 WHEN OKL_API.G_EXCEPTION_ERROR THEN
395
396 x_return_status := G_RET_STS_ERROR;
397
398 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
399
400 x_return_status := G_RET_STS_UNEXP_ERROR;
401
402 WHEN OTHERS THEN
403
404 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
405 p_msg_name => G_DB_ERROR,
406 p_token1 => G_PROG_NAME_TOKEN,
407 p_token1_value => l_api_name,
408 p_token2 => G_SQLCODE_TOKEN,
409 p_token2_value => sqlcode,
410 p_token3 => G_SQLERRM_TOKEN,
411 p_token3_value => sqlerrm);
412
413 x_return_status := G_RET_STS_UNEXP_ERROR;
414
415 END validate_cat_id2;
416
417
418 ---------------------------------
419 -- PROCEDURE validate_term_in_months
420 ---------------------------------
421 PROCEDURE validate_term_in_months (x_return_status OUT NOCOPY VARCHAR2,
422 p_term_in_months IN NUMBER) IS
423
424 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_term_in_months';
425
426 BEGIN
427
428 IF p_term_in_months IS NULL THEN
429
430 OKL_API.set_message(p_app_name => G_APP_NAME,
431 p_msg_name => G_REQUIRED_VALUE,
432 p_token1 => G_COL_NAME_TOKEN,
433 p_token1_value => 'term_in_months');
434
435 RAISE OKL_API.G_EXCEPTION_ERROR;
436
437 END IF;
438
439 IF p_term_in_months <= 0 THEN
440
441 OKL_API.set_message(OKL_API.G_APP_NAME, 'OKL_INVALID_TERM');
445
442 RAISE OKL_API.G_EXCEPTION_ERROR;
443
444 END IF;
446 IF TRUNC(p_term_in_months) <> p_term_in_months THEN
447
448 OKL_API.set_message(OKL_API.G_APP_NAME, 'OKL_INVALID_TERM2');
449 RAISE OKL_API.G_EXCEPTION_ERROR;
450
451 END IF;
452
453 x_return_status := G_RET_STS_SUCCESS;
454
455 EXCEPTION
456
457 WHEN OKL_API.G_EXCEPTION_ERROR THEN
458
459 x_return_status := G_RET_STS_ERROR;
460
461 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
462
463 x_return_status := G_RET_STS_UNEXP_ERROR;
464
465 WHEN OTHERS THEN
466
467 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
468 p_msg_name => G_DB_ERROR,
469 p_token1 => G_PROG_NAME_TOKEN,
470 p_token1_value => l_api_name,
471 p_token2 => G_SQLCODE_TOKEN,
472 p_token2_value => sqlcode,
473 p_token3 => G_SQLERRM_TOKEN,
474 p_token3_value => sqlerrm);
475
476 x_return_status := G_RET_STS_UNEXP_ERROR;
477
478 END validate_term_in_months;
479
480
481 ---------------------------------
482 -- PROCEDURE validate_rv_percent
483 ---------------------------------
484 PROCEDURE validate_rv_percent (x_return_status OUT NOCOPY VARCHAR2,
485 p_residual_value_percent IN NUMBER) IS
486
487 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_rv_percent';
488
489 BEGIN
490
491 IF p_residual_value_percent IS NULL THEN
492
493 OKL_API.set_message(p_app_name => G_APP_NAME,
494 p_msg_name => G_REQUIRED_VALUE,
495 p_token1 => G_COL_NAME_TOKEN,
496 p_token1_value => 'residual_value_percent');
497
498 RAISE OKL_API.G_EXCEPTION_ERROR;
499
500 END IF;
501
502 IF p_residual_value_percent < 0 OR p_residual_value_percent >= 100 THEN
503
504 OKL_API.set_message(OKL_API.G_APP_NAME, 'OKL_INVALID_RV');
505 RAISE OKL_API.G_EXCEPTION_ERROR;
506
507 END IF;
508
509 x_return_status := G_RET_STS_SUCCESS;
510
511 EXCEPTION
512
513 WHEN OKL_API.G_EXCEPTION_ERROR THEN
514
515 x_return_status := G_RET_STS_ERROR;
516
517 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
518
519 x_return_status := G_RET_STS_UNEXP_ERROR;
520
521 WHEN OTHERS THEN
522
523 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
524 p_msg_name => G_DB_ERROR,
525 p_token1 => G_PROG_NAME_TOKEN,
526 p_token1_value => l_api_name,
527 p_token2 => G_SQLCODE_TOKEN,
528 p_token2_value => sqlcode,
529 p_token3 => G_SQLERRM_TOKEN,
530 p_token3_value => sqlerrm);
531
532 x_return_status := G_RET_STS_UNEXP_ERROR;
533
534 END validate_rv_percent;
535 /* smadhava - Pricing Enhancements - Added - Start */
536 ---------------------------------
537 -- PROCEDURE validate_item_residual_id
538 ---------------------------------
539 PROCEDURE validate_item_residual_id (x_return_status OUT NOCOPY VARCHAR2,
540 p_item_residual_id IN NUMBER) IS
541
542 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_item_residual_id';
543
544 BEGIN
545
546 IF p_item_residual_id IS NULL THEN
547
548 OKL_API.set_message(p_app_name => G_APP_NAME,
549 p_msg_name => G_REQUIRED_VALUE,
550 p_token1 => G_COL_NAME_TOKEN,
551 p_token1_value => 'item_residual_id');
552
553 RAISE OKL_API.G_EXCEPTION_ERROR;
554
555 END IF;
556
557
558 x_return_status := G_RET_STS_SUCCESS;
559
560 EXCEPTION
561
562 WHEN OKL_API.G_EXCEPTION_ERROR THEN
563
564 x_return_status := G_RET_STS_ERROR;
565
566 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
567
568 x_return_status := G_RET_STS_UNEXP_ERROR;
569
570 WHEN OTHERS THEN
571
572 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
573 p_msg_name => G_DB_ERROR,
574 p_token1 => G_PROG_NAME_TOKEN,
575 p_token1_value => l_api_name,
576 p_token2 => G_SQLCODE_TOKEN,
577 p_token2_value => sqlcode,
578 p_token3 => G_SQLERRM_TOKEN,
579 p_token3_value => sqlerrm);
580
581 x_return_status := G_RET_STS_UNEXP_ERROR;
582
583 END validate_item_residual_id;
584
585 ---------------------------------
586 -- PROCEDURE validate_sts_code
587 ---------------------------------
591 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_sts_code';
588 PROCEDURE validate_sts_code (x_return_status OUT NOCOPY VARCHAR2,
589 p_sts_code IN VARCHAR2) IS
590
592 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
593 BEGIN
594
595 IF p_sts_code IS NULL THEN
596 OKL_API.set_message(p_app_name => G_APP_NAME,
597 p_msg_name => G_REQUIRED_VALUE,
598 p_token1 => G_COL_NAME_TOKEN,
599 p_token1_value => 'sts_code');
600 RAISE OKL_API.G_EXCEPTION_ERROR;
601 END IF;
602 l_return_status := OKL_UTIL.check_lookup_code(
603 p_lookup_type => 'OKL_PRC_STATUS',
604 p_lookup_code => p_sts_code);
605 IF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
606 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
607 p_msg_name => g_invalid_value,
608 p_token1 => g_col_name_token,
609 p_token1_value => 'sts_code');
610 -- notify caller of an error
611 raise OKL_API.G_EXCEPTION_ERROR;
612 ELSIF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
613 -- notify caller of an error
614 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
615 END IF;
616
617 x_return_status := G_RET_STS_SUCCESS;
618
619 EXCEPTION
620
621 WHEN OKL_API.G_EXCEPTION_ERROR THEN
622
623 x_return_status := G_RET_STS_ERROR;
624
625 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
626
627 x_return_status := G_RET_STS_UNEXP_ERROR;
628
629 WHEN OTHERS THEN
630
631 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
632 p_msg_name => G_DB_ERROR,
633 p_token1 => G_PROG_NAME_TOKEN,
634 p_token1_value => l_api_name,
635 p_token2 => G_SQLCODE_TOKEN,
636 p_token2_value => sqlcode,
637 p_token3 => G_SQLERRM_TOKEN,
638 p_token3_value => sqlerrm);
639
640 x_return_status := G_RET_STS_UNEXP_ERROR;
641
642 END validate_sts_code;
643
644 ---------------------------------
645 -- PROCEDURE validate_version_number
646 ---------------------------------
647 PROCEDURE validate_version_number (x_return_status OUT NOCOPY VARCHAR2,
648 p_version_number IN VARCHAR2) IS
649
650 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_version_number';
651
652 BEGIN
653
654 IF p_version_number IS NULL THEN
655
656 OKL_API.set_message(p_app_name => G_APP_NAME,
657 p_msg_name => G_REQUIRED_VALUE,
658 p_token1 => G_COL_NAME_TOKEN,
659 p_token1_value => 'version_number');
660
661 RAISE OKL_API.G_EXCEPTION_ERROR;
662
663 END IF;
664
665
666 x_return_status := G_RET_STS_SUCCESS;
667
668 EXCEPTION
669
670 WHEN OKL_API.G_EXCEPTION_ERROR THEN
671
672 x_return_status := G_RET_STS_ERROR;
673
674 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
675
676 x_return_status := G_RET_STS_UNEXP_ERROR;
677
678 WHEN OTHERS THEN
679
680 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
681 p_msg_name => G_DB_ERROR,
682 p_token1 => G_PROG_NAME_TOKEN,
683 p_token1_value => l_api_name,
684 p_token2 => G_SQLCODE_TOKEN,
685 p_token2_value => sqlcode,
686 p_token3 => G_SQLERRM_TOKEN,
687 p_token3_value => sqlerrm);
688
689 x_return_status := G_RET_STS_UNEXP_ERROR;
690
691 END validate_version_number;
692 /* smadhava - Pricing Enhancements - Added - End */
693 ----------------------
694 -- validate_attributes
695 ----------------------
696 FUNCTION Validate_Attributes (p_icpv_rec IN icpv_rec_type) RETURN VARCHAR2 IS
697
698 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_attributes';
699 l_return_status VARCHAR2(1);
700
701 BEGIN
702
703 -- ***
704 -- id
705 -- ***
706
707 validate_id(l_return_status, p_icpv_rec.id);
708
709 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
710 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
711 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
712 RAISE OKL_API.G_EXCEPTION_ERROR;
713 END IF;
714
715 -- ***
716 -- object_version_number
717 -- ***
718
719 validate_object_version_number (l_return_status, p_icpv_rec.object_version_number);
720
721 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
722 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
723 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
724 RAISE OKL_API.G_EXCEPTION_ERROR;
725 END IF;
729 as these are henceforth deprecated
726 /* smadhava - Pricing Enhancements - Modified - Start */
727 /*
728 Commenting the validations for cat_id1, cat_id2, term_in_months and residual_value_percent
730 */
731
732
733 -- ***
734 -- cat_id1
735 -- ***
736 /*
737 validate_cat_id1(l_return_status, p_icpv_rec.cat_id1);
738 IF l_return_status = G_RET_STS_ERROR THEN
739 RAISE OKL_API.G_EXCEPTION_ERROR;
740 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
741 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
742 END IF;
743 */
744 -- ***
745 -- cat_id2
746 -- ***
747 /*
748 validate_cat_id2(l_return_status, p_icpv_rec.cat_id2);
749 IF l_return_status = G_RET_STS_ERROR THEN
750 RAISE OKL_API.G_EXCEPTION_ERROR;
751 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
752 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
753 END IF;
754 */
755 -- ***
756 -- term_in_months
757 -- ***
758 /*
759 validate_term_in_months(l_return_status, p_icpv_rec.term_in_months);
760
761 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
762 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
763 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
764 RAISE OKL_API.G_EXCEPTION_ERROR;
765 END IF;
766 */
767 -- ***
768 -- residual_value_percent
769 -- ***
770 /*
771 validate_rv_percent (l_return_status, p_icpv_rec.residual_value_percent);
772
773 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
774 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
775 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
776 RAISE OKL_API.G_EXCEPTION_ERROR;
777 END IF;
778 */
779 /* smadhava - Pricing Enhancements - Modified - End */
780
781 /* smadhava - Pricing Enhancements - Added - Start */
782 -- ***
783 -- item_residual_id
784 -- ***
785
786 validate_item_residual_id(l_return_status, p_icpv_rec.item_residual_id);
787
788 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
789 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
790 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
791 RAISE OKL_API.G_EXCEPTION_ERROR;
792 END IF;
793
794 -- ***
795 -- sts_code
796 -- ***
797
798 validate_sts_code(l_return_status, p_icpv_rec.sts_code);
799
800 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
801 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
802 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
803 RAISE OKL_API.G_EXCEPTION_ERROR;
804 END IF;
805
806 -- ***
807 -- version_number
808 -- ***
809 validate_version_number(l_return_status, p_icpv_rec.version_number);
810
811 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
812 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
813 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
814 RAISE OKL_API.G_EXCEPTION_ERROR;
815 END IF;
816 /* smadhava - Pricing Enhancements - Added - End */
817
818 RETURN G_RET_STS_SUCCESS;
819
820 EXCEPTION
821
822 WHEN OKL_API.G_EXCEPTION_ERROR THEN
823
824 RETURN G_RET_STS_ERROR;
825
826 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
827
828 RETURN G_RET_STS_UNEXP_ERROR;
829
830 WHEN OTHERS THEN
831
832 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
833 p_msg_name => G_DB_ERROR,
834 p_token1 => G_PROG_NAME_TOKEN,
835 p_token1_value => l_api_name,
836 p_token2 => G_SQLCODE_TOKEN,
837 p_token2_value => sqlcode,
838 p_token3 => G_SQLERRM_TOKEN,
839 p_token3_value => sqlerrm);
840
841 RETURN G_RET_STS_UNEXP_ERROR;
842
843 END validate_attributes;
844
845
846 ------------------
847 -- validate_record
848 ------------------
849 FUNCTION validate_record (p_icpv_rec IN icpv_rec_type) RETURN VARCHAR2 IS
850
851 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_record';
852
853 /* smadhava - Pricing Enhancements - Modified - Start */
854 /*
855 Commenting to remove the existing validations. The existing validations
856 check for overlap of effective from and effective to dates for a particular
857 term which is not relevant for this release. This is because the term and
858 values are stored in a separate table.
859 */
860 /*
861 CURSOR c_uk_recs IS
862 SELECT start_date,
863 end_date
864 FROM okl_itm_cat_rv_prcs
865 WHERE cat_id1 = p_icpv_rec.cat_id1
866 AND cat_id2 = p_icpv_rec.cat_id2
867 AND term_in_months = p_icpv_rec.term_in_months
868 AND id <> p_icpv_rec.id
869 AND start_date IS NOT NULL
870 ORDER BY start_date;
871
872 l_item_desc VARCHAR2(240);
873 */
874 /* smadhava - Pricing Enhancements - Modified - End */
875 BEGIN
879 -- IF p_icpv_rec.end_date IS NOT NULL AND p_icpv_rec.start_date IS NULL THEN
876 /* smadhava - Pricing Enhancements - Added - Start */
877
878 /* The if condition is modified to check only the effective from date */
880 IF p_icpv_rec.start_date IS NULL THEN
881 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
882 p_msg_name => 'OKL_REQUIRED_EFFECTIVE_FROM');
883
884 RAISE OKL_API.G_EXCEPTION_ERROR;
885
886 END IF;
887
888 IF p_icpv_rec.end_date IS NOT NULL AND p_icpv_rec.end_date <> G_MISS_DATE AND p_icpv_rec.end_date < p_icpv_rec.start_date THEN
889
890 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
891 p_msg_name => 'OKL_INVALID_EFFECTIVE_TO');
892
893 RAISE OKL_API.G_EXCEPTION_ERROR;
894
895 END IF;
896 /* smadhava - Pricing Enhancements - Added - End */
897
898 /* smadhava - Pricing Enhancements - Modified - Start */
899 /*
900 --------------------------
901 -- check for date overlaps
902 --------------------------
903 IF p_icpv_rec.start_date IS NOT NULL THEN
904
905 SELECT description
906 INTO l_item_desc
907 FROM mtl_system_items_tl
908 WHERE inventory_item_id = p_icpv_rec.cat_id1
909 AND organization_id = p_icpv_rec.cat_id2
910 AND language = USERENV('LANG');
911
912 FOR l_uk_rec IN c_uk_recs LOOP
913
914
915 -- Open ended record
916 IF l_uk_rec.end_date IS NULL THEN
917
918 IF (p_icpv_rec.end_date >= l_uk_rec.start_date) OR (p_icpv_rec.end_date IS NULL) THEN
919
920 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
921 p_msg_name => 'OKL_ITMRV_EXISTS',
922 p_token1 => 'ITEM_DESC',
923 p_token1_value => l_item_desc,
924 p_token2 => 'TERM',
925 p_token2_value => p_icpv_rec.term_in_months);
926
927 RAISE OKL_API.G_EXCEPTION_ERROR;
928
929 END IF;
930
931 END IF;
932
933 -- Finite record
934 IF l_uk_rec.end_date IS NOT NULL THEN
935
936 IF p_icpv_rec.end_date BETWEEN l_uk_rec.start_date AND l_uk_rec.end_date THEN
937
938 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
939 p_msg_name => 'OKL_ITMRV_EXISTS',
940 p_token1 => 'ITEM_DESC',
941 p_token1_value => l_item_desc,
942 p_token2 => 'TERM',
943 p_token2_value => p_icpv_rec.term_in_months);
944
945 RAISE OKL_API.G_EXCEPTION_ERROR;
946
947 END IF;
948
949 IF p_icpv_rec.start_date <= l_uk_rec.end_date AND p_icpv_rec.end_date IS NULL THEN
950
951 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
952 p_msg_name => 'OKL_ITMRV_EXISTS',
953 p_token1 => 'ITEM_DESC',
954 p_token1_value => l_item_desc,
955 p_token2 => 'TERM',
956 p_token2_value => p_icpv_rec.term_in_months);
957
958 RAISE OKL_API.G_EXCEPTION_ERROR;
959
960 END IF;
961
962 END IF;
963
964 END LOOP;
965
966 END IF;
967 */
968 /* smadhava - Pricing Enhancements - Modified - End */
969
970 RETURN G_RET_STS_SUCCESS;
971
972 EXCEPTION
973
974 WHEN OKL_API.G_EXCEPTION_ERROR THEN
975
976 RETURN G_RET_STS_ERROR;
977
978 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
979
980 RETURN G_RET_STS_UNEXP_ERROR;
981
982 WHEN OTHERS THEN
983
984 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
985 p_msg_name => G_DB_ERROR,
986 p_token1 => G_PROG_NAME_TOKEN,
987 p_token1_value => l_api_name,
988 p_token2 => G_SQLCODE_TOKEN,
989 p_token2_value => sqlcode,
990 p_token3 => G_SQLERRM_TOKEN,
991 p_token3_value => sqlerrm);
992
993 RETURN G_RET_STS_UNEXP_ERROR;
994
995 END validate_record;
996
997
998 ---------------------
999 -- validate_row (REC)
1000 ---------------------
1001 PROCEDURE validate_row(
1002 p_api_version IN NUMBER,
1003 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1004 x_return_status OUT NOCOPY VARCHAR2,
1005 x_msg_count OUT NOCOPY NUMBER,
1006 x_msg_data OUT NOCOPY VARCHAR2,
1007 p_icpv_rec IN icpv_rec_type) IS
1008
1009 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_row (REC)';
1010
1011 l_return_status VARCHAR2(1);
1012
1013 BEGIN
1014
1015 l_return_status := validate_attributes(p_icpv_rec);
1016
1020 RAISE OKL_API.G_EXCEPTION_ERROR;
1017 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1018 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1019 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1021 END IF;
1022
1023 l_return_status := validate_record(p_icpv_rec);
1024
1025 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1026 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1027 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1028 RAISE OKL_API.G_EXCEPTION_ERROR;
1029 END IF;
1030
1031 EXCEPTION
1032
1033 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1034
1035 x_return_status := G_RET_STS_ERROR;
1036
1037 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1038
1039 x_return_status := G_RET_STS_UNEXP_ERROR;
1040
1041 WHEN OTHERS THEN
1042
1043 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1044 p_msg_name => G_DB_ERROR,
1045 p_token1 => G_PROG_NAME_TOKEN,
1046 p_token1_value => l_api_name,
1047 p_token2 => G_SQLCODE_TOKEN,
1048 p_token2_value => sqlcode,
1049 p_token3 => G_SQLERRM_TOKEN,
1050 p_token3_value => sqlerrm);
1051
1052 x_return_status := G_RET_STS_UNEXP_ERROR;
1053
1054 END validate_row;
1055
1056 ---------------------
1057 -- validate_row (TBL)
1058 ---------------------
1059 PROCEDURE validate_row(
1060 p_api_version IN NUMBER,
1061 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1062 x_return_status OUT NOCOPY VARCHAR2,
1063 x_msg_count OUT NOCOPY NUMBER,
1064 x_msg_data OUT NOCOPY VARCHAR2,
1065 p_icpv_tbl IN icpv_tbl_type) IS
1066
1067 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_row (TBL)';
1068
1069 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1070 i BINARY_INTEGER;
1071
1072 BEGIN
1073
1074 IF (p_icpv_tbl.COUNT > 0) THEN
1075
1076 i := p_icpv_tbl.FIRST;
1077
1078 LOOP
1079
1080 IF p_icpv_tbl.EXISTS(i) THEN
1081
1082 validate_row (p_api_version => G_API_VERSION,
1083 p_init_msg_list => G_FALSE,
1084 x_return_status => l_return_status,
1085 x_msg_count => x_msg_count,
1086 x_msg_data => x_msg_data,
1087 p_icpv_rec => p_icpv_tbl(i));
1088
1089 IF l_return_status = G_RET_STS_ERROR THEN
1090 RAISE OKL_API.G_EXCEPTION_ERROR;
1091 ELSIF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1092 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1093 END IF;
1094
1095 EXIT WHEN i = p_icpv_tbl.LAST;
1096 i := p_icpv_tbl.NEXT(i);
1097
1098 END IF;
1099
1100 END LOOP;
1101
1102 END IF;
1103
1104 x_return_status := l_return_status;
1105
1106 EXCEPTION
1107
1108 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1109
1110 x_return_status := G_RET_STS_ERROR;
1111
1112 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1113
1114 x_return_status := G_RET_STS_UNEXP_ERROR;
1115
1116 WHEN OTHERS THEN
1117
1118 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1119 p_msg_name => G_DB_ERROR,
1120 p_token1 => G_PROG_NAME_TOKEN,
1121 p_token1_value => l_api_name,
1122 p_token2 => G_SQLCODE_TOKEN,
1123 p_token2_value => sqlcode,
1124 p_token3 => G_SQLERRM_TOKEN,
1125 p_token3_value => sqlerrm);
1126
1127 x_return_status := G_RET_STS_UNEXP_ERROR;
1128
1129 END validate_row;
1130
1131
1132 -------------------
1133 -- insert_row (REC)
1134 -------------------
1135 PROCEDURE insert_row(
1136 p_api_version IN NUMBER,
1137 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1138 x_return_status OUT NOCOPY VARCHAR2,
1139 x_msg_count OUT NOCOPY NUMBER,
1140 x_msg_data OUT NOCOPY VARCHAR2,
1141 p_icpv_rec IN icpv_rec_type,
1142 x_icpv_rec OUT NOCOPY icpv_rec_type) IS
1143
1144 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'insert_row (REC)';
1145
1146 l_return_status VARCHAR2(1);
1147 l_icpv_rec icpv_rec_type;
1148
1149 BEGIN
1150
1151 l_icpv_rec := null_out_defaults(p_icpv_rec);
1152
1153 l_icpv_rec.ID := okc_p_util.raw_to_number(sys_guid());
1154 l_icpv_rec.OBJECT_VERSION_NUMBER := 1;
1155 l_icpv_rec.CREATION_DATE := SYSDATE;
1156 l_icpv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1157 l_icpv_rec.LAST_UPDATE_DATE := SYSDATE;
1161 l_return_status := validate_attributes(l_icpv_rec);
1158 l_icpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1159 l_icpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1160
1162 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1163 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1164 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1165 RAISE OKL_API.G_EXCEPTION_ERROR;
1166 END IF;
1167 l_return_status := validate_record(l_icpv_rec);
1168
1169 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1170 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1171 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1172 RAISE OKL_API.G_EXCEPTION_ERROR;
1173 END IF;
1174 /* smadhava - Pricing Enhancements - Modified- Start */
1175 INSERT INTO OKL_ITM_CAT_RV_PRCS(
1176 id,
1177 object_version_number,
1178 cat_id1,
1179 cat_id2,
1180 term_in_months,
1181 residual_value_percent,
1182 item_residual_id,
1183 sts_code,
1184 version_number,
1185 start_date,
1186 end_date,
1187 created_by,
1188 creation_date,
1189 last_updated_by,
1190 last_update_date,
1191 last_update_login,
1192 attribute_category,
1193 attribute1,
1194 attribute2,
1195 attribute3,
1196 attribute4,
1197 attribute5,
1198 attribute6,
1199 attribute7,
1200 attribute8,
1201 attribute9,
1202 attribute10,
1203 attribute11,
1204 attribute12,
1205 attribute13,
1206 attribute14,
1207 attribute15)
1208 VALUES (
1209 l_icpv_rec.id,
1210 l_icpv_rec.object_version_number,
1211 l_icpv_rec.cat_id1,
1212 l_icpv_rec.cat_id2,
1213 l_icpv_rec.term_in_months,
1214 l_icpv_rec.residual_value_percent,
1215 l_icpv_rec.item_residual_id,
1216 l_icpv_rec.sts_code,
1217 l_icpv_rec.version_number,
1218 l_icpv_rec.start_date,
1219 l_icpv_rec.end_date,
1220 l_icpv_rec.created_by,
1221 l_icpv_rec.creation_date,
1222 l_icpv_rec.last_updated_by,
1223 l_icpv_rec.last_update_date,
1224 l_icpv_rec.last_update_login,
1225 l_icpv_rec.attribute_category,
1226 l_icpv_rec.attribute1,
1227 l_icpv_rec.attribute2,
1228 l_icpv_rec.attribute3,
1229 l_icpv_rec.attribute4,
1230 l_icpv_rec.attribute5,
1231 l_icpv_rec.attribute6,
1232 l_icpv_rec.attribute7,
1233 l_icpv_rec.attribute8,
1234 l_icpv_rec.attribute9,
1235 l_icpv_rec.attribute10,
1236 l_icpv_rec.attribute11,
1237 l_icpv_rec.attribute12,
1238 l_icpv_rec.attribute13,
1239 l_icpv_rec.attribute14,
1240 l_icpv_rec.attribute15);
1241 /* smadhava - Pricing Enhancements - Added - End */
1242 x_icpv_rec := l_icpv_rec;
1243 x_return_status := l_return_status;
1244 EXCEPTION
1245
1246 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1247
1248 x_return_status := G_RET_STS_ERROR;
1249
1250 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1251
1252 x_return_status := G_RET_STS_UNEXP_ERROR;
1253
1254 WHEN OTHERS THEN
1255
1256 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1257 p_msg_name => G_DB_ERROR,
1258 p_token1 => G_PROG_NAME_TOKEN,
1259 p_token1_value => l_api_name,
1260 p_token2 => G_SQLCODE_TOKEN,
1261 p_token2_value => sqlcode,
1262 p_token3 => G_SQLERRM_TOKEN,
1263 p_token3_value => sqlerrm);
1264
1265 x_return_status := G_RET_STS_UNEXP_ERROR;
1266
1267 END insert_row;
1268
1269
1270 -------------------
1271 -- insert_row (TBL)
1272 -------------------
1273 PROCEDURE insert_row(
1274 p_api_version IN NUMBER,
1275 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1276 x_return_status OUT NOCOPY VARCHAR2,
1277 x_msg_count OUT NOCOPY NUMBER,
1278 x_msg_data OUT NOCOPY VARCHAR2,
1279 p_icpv_tbl IN icpv_tbl_type,
1280 x_icpv_tbl OUT NOCOPY icpv_tbl_type) IS
1281
1282 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'insert_row (TBL)';
1283 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1284 i BINARY_INTEGER;
1285
1286 BEGIN
1287
1288 IF (p_icpv_tbl.COUNT > 0) THEN
1289 i := p_icpv_tbl.FIRST;
1290 LOOP
1291 IF p_icpv_tbl.EXISTS(i) THEN
1292
1293 insert_row (p_api_version => G_API_VERSION,
1294 p_init_msg_list => G_FALSE,
1295 x_return_status => l_return_status,
1296 x_msg_count => x_msg_count,
1297 x_msg_data => x_msg_data,
1298 p_icpv_rec => p_icpv_tbl(i),
1299 x_icpv_rec => x_icpv_tbl(i));
1300
1304 RAISE OKL_API.G_EXCEPTION_ERROR;
1301 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1302 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1303 ELSIF l_return_status = G_RET_STS_ERROR THEN
1305 END IF;
1306
1307 EXIT WHEN (i = p_icpv_tbl.LAST);
1308 i := p_icpv_tbl.NEXT(i);
1309
1310 END IF;
1311
1312 END LOOP;
1313
1314 END IF;
1315
1316 x_return_status := G_RET_STS_SUCCESS;
1317
1318 EXCEPTION
1319
1320 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1321
1322 x_return_status := G_RET_STS_ERROR;
1323
1324 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1325
1326 x_return_status := G_RET_STS_UNEXP_ERROR;
1327
1328 WHEN OTHERS THEN
1329
1330 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1331 p_msg_name => G_DB_ERROR,
1332 p_token1 => G_PROG_NAME_TOKEN,
1333 p_token1_value => l_api_name,
1334 p_token2 => G_SQLCODE_TOKEN,
1335 p_token2_value => sqlcode,
1336 p_token3 => G_SQLERRM_TOKEN,
1337 p_token3_value => sqlerrm);
1338
1339 x_return_status := G_RET_STS_UNEXP_ERROR;
1340
1341 END insert_row;
1342
1343
1344 -----------------
1345 -- lock_row (REC)
1346 -----------------
1347 PROCEDURE lock_row(
1348 p_api_version IN NUMBER,
1349 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1350 x_return_status OUT NOCOPY VARCHAR2,
1351 x_msg_count OUT NOCOPY NUMBER,
1352 x_msg_data OUT NOCOPY VARCHAR2,
1353 p_icpv_rec IN icpv_rec_type) IS
1354
1355 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'lock_row (REC)';
1356
1357 E_Resource_Busy EXCEPTION;
1358
1359 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1360
1361 CURSOR lock_csr (p_icpv_rec IN icpv_rec_type) IS
1362 SELECT OBJECT_VERSION_NUMBER
1363 FROM OKL_ITM_CAT_RV_PRCS
1364 WHERE ID = p_icpv_rec.id
1365 AND OBJECT_VERSION_NUMBER = p_icpv_rec.object_version_number
1366 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1367
1368 CURSOR lchk_csr (p_icpv_rec IN icpv_rec_type) IS
1369 SELECT OBJECT_VERSION_NUMBER
1370 FROM OKL_ITM_CAT_RV_PRCS
1371 WHERE ID = p_icpv_rec.id;
1372
1373 l_return_status VARCHAR2(1):= OKL_API.G_RET_STS_SUCCESS;
1374 l_object_version_number OKL_ITM_CAT_RV_PRCS.OBJECT_VERSION_NUMBER%TYPE;
1375 lc_object_version_number OKL_ITM_CAT_RV_PRCS.OBJECT_VERSION_NUMBER%TYPE;
1376 l_row_notfound BOOLEAN := FALSE;
1377 lc_row_notfound BOOLEAN := FALSE;
1378
1379 BEGIN
1380
1381 BEGIN
1382 OPEN lock_csr(p_icpv_rec);
1383 FETCH lock_csr INTO l_object_version_number;
1384 l_row_notfound := lock_csr%NOTFOUND;
1385 CLOSE lock_csr;
1386 EXCEPTION
1387 WHEN E_Resource_Busy THEN
1388 IF (lock_csr%ISOPEN) THEN
1389 CLOSE lock_csr;
1390 END IF;
1391 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1392 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1393 END;
1394
1395 IF ( l_row_notfound ) THEN
1396 OPEN lchk_csr(p_icpv_rec);
1397 FETCH lchk_csr INTO lc_object_version_number;
1398 lc_row_notfound := lchk_csr%NOTFOUND;
1399 CLOSE lchk_csr;
1400 END IF;
1401 IF (lc_row_notfound) THEN
1402 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1403 RAISE OKC_API.G_EXCEPTION_ERROR;
1404 ELSIF lc_object_version_number > p_icpv_rec.object_version_number THEN
1405 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1406 RAISE OKC_API.G_EXCEPTION_ERROR;
1407 ELSIF lc_object_version_number <> p_icpv_rec.object_version_number THEN
1408 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1409 RAISE OKC_API.G_EXCEPTION_ERROR;
1410 ELSIF lc_object_version_number = -1 THEN
1411 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1412 RAISE OKC_API.G_EXCEPTION_ERROR;
1413 END IF;
1414
1415 x_return_status := l_return_status;
1416
1417 EXCEPTION
1418
1419 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1420
1421 x_return_status := G_RET_STS_ERROR;
1422
1423 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1424
1425 x_return_status := G_RET_STS_UNEXP_ERROR;
1426
1427 WHEN OTHERS THEN
1428
1429 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1430 p_msg_name => G_DB_ERROR,
1431 p_token1 => G_PROG_NAME_TOKEN,
1432 p_token1_value => l_api_name,
1433 p_token2 => G_SQLCODE_TOKEN,
1434 p_token2_value => sqlcode,
1435 p_token3 => G_SQLERRM_TOKEN,
1436 p_token3_value => sqlerrm);
1437
1438 x_return_status := G_RET_STS_UNEXP_ERROR;
1439
1440 END lock_row;
1441
1442
1443 -----------------
1444 -- lock_row (TBL)
1448 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1445 -----------------
1446 PROCEDURE lock_row(
1447 p_api_version IN NUMBER,
1449 x_return_status OUT NOCOPY VARCHAR2,
1450 x_msg_count OUT NOCOPY NUMBER,
1451 x_msg_data OUT NOCOPY VARCHAR2,
1452 p_icpv_tbl IN icpv_tbl_type) IS
1453
1454 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'lock_row (TBL)';
1455 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1456 i BINARY_INTEGER;
1457
1458 BEGIN
1459
1460 IF (p_icpv_tbl.COUNT > 0) THEN
1461
1462 i := p_icpv_tbl.FIRST;
1463
1464 LOOP
1465
1466 IF p_icpv_tbl.EXISTS(i) THEN
1467
1468 lock_row (p_api_version => G_API_VERSION,
1469 p_init_msg_list => G_FALSE,
1470 x_return_status => l_return_status,
1471 x_msg_count => x_msg_count,
1472 x_msg_data => x_msg_data,
1473 p_icpv_rec => p_icpv_tbl(i));
1474
1475 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1476 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1477 ELSIF l_return_status = G_RET_STS_ERROR THEN
1478 RAISE OKL_API.G_EXCEPTION_ERROR;
1479 END IF;
1480
1481 EXIT WHEN (i = p_icpv_tbl.LAST);
1482 i := p_icpv_tbl.NEXT(i);
1483
1484 END IF;
1485
1486 END LOOP;
1487
1488 END IF;
1489
1490 x_return_status := l_return_status;
1491
1492 EXCEPTION
1493
1494 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1495
1496 x_return_status := G_RET_STS_ERROR;
1497
1498 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1499
1500 x_return_status := G_RET_STS_UNEXP_ERROR;
1501
1502 WHEN OTHERS THEN
1503
1504 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1505 p_msg_name => G_DB_ERROR,
1506 p_token1 => G_PROG_NAME_TOKEN,
1507 p_token1_value => l_api_name,
1508 p_token2 => G_SQLCODE_TOKEN,
1509 p_token2_value => sqlcode,
1510 p_token3 => G_SQLERRM_TOKEN,
1511 p_token3_value => sqlerrm);
1512
1513 x_return_status := G_RET_STS_UNEXP_ERROR;
1514
1515 END lock_row;
1516
1517
1518 ------------------------------------------
1519 -- update_row for:OKL_ITM_CAT_RV_PRCS_V --
1520 ------------------------------------------
1521 PROCEDURE update_row(
1522 p_api_version IN NUMBER,
1523 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1524 x_return_status OUT NOCOPY VARCHAR2,
1525 x_msg_count OUT NOCOPY NUMBER,
1526 x_msg_data OUT NOCOPY VARCHAR2,
1527 p_icpv_rec IN icpv_rec_type,
1528 x_icpv_rec OUT NOCOPY icpv_rec_type) IS
1529
1530 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'update_row (_V)';
1531 l_return_status VARCHAR2(1);
1532 l_icpv_rec icpv_rec_type := p_icpv_rec;
1533
1534 /* smadhava - Pricing Enhancements - Modified - Start */
1535 -- Modifications for G_MISS changes
1536 ----------------------
1537 -- populate_new_record
1538 ----------------------
1539 FUNCTION populate_new_record (p_icpv_rec IN icpv_rec_type,
1540 x_icpv_rec OUT NOCOPY icpv_rec_type) RETURN VARCHAR2 IS
1541
1542 l_return_status VARCHAR2(1);
1543 l_db_icpv_rec icpv_rec_type;
1544
1545 BEGIN
1546
1547 x_icpv_rec := p_icpv_rec;
1548
1549 l_db_icpv_rec := get_rec(p_icpv_rec.id, l_return_status);
1550
1551 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1552 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1553 ELSIF l_return_status = G_RET_STS_ERROR THEN
1554 RAISE OKL_API.G_EXCEPTION_ERROR;
1555 END IF;
1556
1557 -- Do NOT default the following 4 standard attributes from the DB
1558 -- object_version_number
1559 -- last_update_date
1560 -- last_update_by
1561 -- last_update_login
1562 IF (x_icpv_rec.id IS NULL) THEN
1563 x_icpv_rec.id := l_db_icpv_rec.id;
1564 END IF;
1565 IF (x_icpv_rec.cat_id1 IS NULL) THEN
1566 x_icpv_rec.cat_id1 := l_db_icpv_rec.cat_id1;
1567 END IF;
1568 IF (x_icpv_rec.cat_id2 IS NULL) THEN
1569 x_icpv_rec.cat_id2 := l_db_icpv_rec.cat_id2;
1570 END IF;
1571 IF (x_icpv_rec.term_in_months IS NULL) THEN
1572 x_icpv_rec.term_in_months := l_db_icpv_rec.term_in_months;
1573 END IF;
1574 IF (x_icpv_rec.residual_value_percent IS NULL) THEN
1575 x_icpv_rec.residual_value_percent := l_db_icpv_rec.residual_value_percent;
1576 END IF;
1577 IF (x_icpv_rec.item_residual_id IS NULL) THEN
1578 x_icpv_rec.item_residual_id := l_db_icpv_rec.item_residual_id;
1579 END IF;
1583 IF (x_icpv_rec.version_number IS NULL) THEN
1580 IF (x_icpv_rec.sts_code IS NULL) THEN
1581 x_icpv_rec.sts_code := l_db_icpv_rec.sts_code;
1582 END IF;
1584 x_icpv_rec.version_number := l_db_icpv_rec.version_number;
1585 END IF;
1586 IF (x_icpv_rec.start_date IS NULL) THEN
1587 x_icpv_rec.start_date := l_db_icpv_rec.start_date;
1588 END IF;
1589 IF (x_icpv_rec.end_date IS NULL) THEN
1590 x_icpv_rec.end_date := l_db_icpv_rec.end_date;
1591 END IF;
1592 IF (x_icpv_rec.created_by IS NULL) THEN
1593 x_icpv_rec.created_by := l_db_icpv_rec.created_by;
1594 END IF;
1595 IF (x_icpv_rec.creation_date IS NULL) THEN
1596 x_icpv_rec.creation_date := l_db_icpv_rec.creation_date;
1597 END IF;
1598 IF (x_icpv_rec.attribute_category IS NULL) THEN
1599 x_icpv_rec.attribute_category := l_db_icpv_rec.attribute_category;
1600 END IF;
1601 IF (x_icpv_rec.attribute1 IS NULL) THEN
1602 x_icpv_rec.attribute1 := l_db_icpv_rec.attribute1;
1603 END IF;
1604 IF (x_icpv_rec.attribute2 IS NULL) THEN
1605 x_icpv_rec.attribute2 := l_db_icpv_rec.attribute2;
1606 END IF;
1607 IF (x_icpv_rec.attribute3 IS NULL) THEN
1608 x_icpv_rec.attribute3 := l_db_icpv_rec.attribute3;
1609 END IF;
1610 IF (x_icpv_rec.attribute4 IS NULL) THEN
1611 x_icpv_rec.attribute4 := l_db_icpv_rec.attribute4;
1612 END IF;
1613 IF (x_icpv_rec.attribute5 IS NULL) THEN
1614 x_icpv_rec.attribute5 := l_db_icpv_rec.attribute5;
1615 END IF;
1616 IF (x_icpv_rec.attribute6 IS NULL) THEN
1617 x_icpv_rec.attribute6 := l_db_icpv_rec.attribute6;
1618 END IF;
1619 IF (x_icpv_rec.attribute7 IS NULL) THEN
1620 x_icpv_rec.attribute7 := l_db_icpv_rec.attribute7;
1621 END IF;
1622 IF (x_icpv_rec.attribute8 IS NULL) THEN
1623 x_icpv_rec.attribute8 := l_db_icpv_rec.attribute8;
1624 END IF;
1625 IF (x_icpv_rec.attribute9 IS NULL) THEN
1626 x_icpv_rec.attribute9 := l_db_icpv_rec.attribute9;
1627 END IF;
1628 IF (x_icpv_rec.attribute10 IS NULL) THEN
1629 x_icpv_rec.attribute10 := l_db_icpv_rec.attribute10;
1630 END IF;
1631 IF (x_icpv_rec.attribute11 IS NULL) THEN
1632 x_icpv_rec.attribute11 := l_db_icpv_rec.attribute11;
1633 END IF;
1634 IF (x_icpv_rec.attribute12 IS NULL) THEN
1635 x_icpv_rec.attribute12 := l_db_icpv_rec.attribute12;
1636 END IF;
1637 IF (x_icpv_rec.attribute13 IS NULL) THEN
1638 x_icpv_rec.attribute13 := l_db_icpv_rec.attribute13;
1639 END IF;
1640 IF (x_icpv_rec.attribute14 IS NULL) THEN
1641 x_icpv_rec.attribute14 := l_db_icpv_rec.attribute14;
1642 END IF;
1643 IF (x_icpv_rec.attribute15 IS NULL) THEN
1644 x_icpv_rec.attribute15 := l_db_icpv_rec.attribute15;
1645 END IF;
1646
1647 RETURN l_return_status;
1648
1649 EXCEPTION
1650
1651 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1652
1653 x_return_status := G_RET_STS_ERROR;
1654
1655 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1656
1657 x_return_status := G_RET_STS_UNEXP_ERROR;
1658
1659 WHEN OTHERS THEN
1660
1661 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1662 p_msg_name => G_DB_ERROR,
1663 p_token1 => G_PROG_NAME_TOKEN,
1664 p_token1_value => l_api_name,
1665 p_token2 => G_SQLCODE_TOKEN,
1666 p_token2_value => sqlcode,
1667 p_token3 => G_SQLERRM_TOKEN,
1668 p_token3_value => sqlerrm);
1669
1670 x_return_status := G_RET_STS_UNEXP_ERROR;
1671
1672 END populate_new_record;
1673 /* smadhava - Pricing Enhancements - Modified - End */
1674 BEGIN
1675 l_return_status := populate_new_record(p_icpv_rec, l_icpv_rec);
1676
1677 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1678 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1679 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1680 RAISE OKL_API.G_EXCEPTION_ERROR;
1681 END IF;
1682
1683 l_icpv_rec := null_out_defaults(l_icpv_rec);
1684
1685 l_icpv_rec.LAST_UPDATE_DATE := SYSDATE;
1686 l_icpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1687 l_icpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1688
1689 l_return_status := validate_attributes(l_icpv_rec);
1690
1691 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1692 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1693 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1694 RAISE OKL_API.G_EXCEPTION_ERROR;
1695 END IF;
1696
1697 l_return_status := validate_record(l_icpv_rec);
1698
1699 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1700 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1701 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1702 RAISE OKL_API.G_EXCEPTION_ERROR;
1703 END IF;
1704
1705 lock_row(p_api_version => G_API_VERSION,
1706 p_init_msg_list => G_FALSE,
1707 x_return_status => l_return_status,
1708 x_msg_count => x_msg_count,
1712 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1709 x_msg_data => x_msg_data,
1710 p_icpv_rec => l_icpv_rec);
1711
1713 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1714 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1715 RAISE OKL_API.G_EXCEPTION_ERROR;
1716 END IF;
1717 /* smadhava - Pricing Enhancements - Modified - Start */
1718 UPDATE OKL_ITM_CAT_RV_PRCS
1719 SET OBJECT_VERSION_NUMBER = l_icpv_rec.object_version_number+1,
1720 CAT_ID1 = l_icpv_rec.cat_id1,
1721 CAT_ID2 = l_icpv_rec.cat_id2,
1722 TERM_IN_MONTHS = l_icpv_rec.term_in_months,
1723 RESIDUAL_VALUE_PERCENT = l_icpv_rec.residual_value_percent,
1724 ITEM_RESIDUAL_ID = l_icpv_rec.item_residual_id,
1725 STS_CODE = l_icpv_rec.sts_code,
1726 VERSION_NUMBER = l_icpv_rec.version_number,
1727 START_DATE = l_icpv_rec.start_date,
1728 END_DATE = l_icpv_rec.end_date,
1729 CREATED_BY = l_icpv_rec.created_by,
1730 CREATION_DATE = l_icpv_rec.creation_date,
1731 LAST_UPDATED_BY = l_icpv_rec.last_updated_by,
1732 LAST_UPDATE_DATE = l_icpv_rec.last_update_date,
1733 LAST_UPDATE_LOGIN = l_icpv_rec.last_update_login,
1734 ATTRIBUTE_CATEGORY = l_icpv_rec.attribute_category,
1735 ATTRIBUTE1 = l_icpv_rec.attribute1,
1736 ATTRIBUTE2 = l_icpv_rec.attribute2,
1737 ATTRIBUTE3 = l_icpv_rec.attribute3,
1738 ATTRIBUTE4 = l_icpv_rec.attribute4,
1739 ATTRIBUTE5 = l_icpv_rec.attribute5,
1740 ATTRIBUTE6 = l_icpv_rec.attribute6,
1741 ATTRIBUTE7 = l_icpv_rec.attribute7,
1742 ATTRIBUTE8 = l_icpv_rec.attribute8,
1743 ATTRIBUTE9 = l_icpv_rec.attribute9,
1744 ATTRIBUTE10 = l_icpv_rec.attribute10,
1745 ATTRIBUTE11 = l_icpv_rec.attribute11,
1746 ATTRIBUTE12 = l_icpv_rec.attribute12,
1747 ATTRIBUTE13 = l_icpv_rec.attribute13,
1748 ATTRIBUTE14 = l_icpv_rec.attribute14,
1749 ATTRIBUTE15 = l_icpv_rec.attribute15
1750 WHERE ID = l_icpv_rec.id;
1751 /* smadhava - Pricing Enhancements - Modified - End */
1752 x_icpv_rec := l_icpv_rec;
1753 x_return_status := l_return_status;
1754
1755 EXCEPTION
1756
1757 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1758
1759 x_return_status := G_RET_STS_ERROR;
1760
1761 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1762
1763 x_return_status := G_RET_STS_UNEXP_ERROR;
1764
1765 WHEN OTHERS THEN
1766
1767 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1768 p_msg_name => G_DB_ERROR,
1769 p_token1 => G_PROG_NAME_TOKEN,
1770 p_token1_value => l_api_name,
1771 p_token2 => G_SQLCODE_TOKEN,
1772 p_token2_value => sqlcode,
1773 p_token3 => G_SQLERRM_TOKEN,
1774 p_token3_value => sqlerrm);
1775
1776 x_return_status := G_RET_STS_UNEXP_ERROR;
1777
1778 END update_row;
1779
1780
1781 -------------------
1782 -- update_row (TBL)
1783 -------------------
1784 PROCEDURE update_row(
1785 p_api_version IN NUMBER,
1786 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1787 x_return_status OUT NOCOPY VARCHAR2,
1788 x_msg_count OUT NOCOPY NUMBER,
1789 x_msg_data OUT NOCOPY VARCHAR2,
1790 p_icpv_tbl IN icpv_tbl_type,
1791 x_icpv_tbl OUT NOCOPY icpv_tbl_type) IS
1792
1793 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'update_row (TBL)';
1794 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1795 i BINARY_INTEGER;
1796
1797 BEGIN
1798
1799 IF (p_icpv_tbl.COUNT > 0) THEN
1800
1801 i := p_icpv_tbl.FIRST;
1802
1803 LOOP
1804
1805 IF p_icpv_tbl.EXISTS(i) THEN
1806 update_row (p_api_version => G_API_VERSION,
1807 p_init_msg_list => G_FALSE,
1808 x_return_status => l_return_status,
1809 x_msg_count => x_msg_count,
1810 x_msg_data => x_msg_data,
1811 p_icpv_rec => p_icpv_tbl(i),
1812 x_icpv_rec => x_icpv_tbl(i));
1813
1814 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1815 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1816 ELSIF l_return_status = G_RET_STS_ERROR THEN
1817 RAISE OKL_API.G_EXCEPTION_ERROR;
1818 END IF;
1819
1820 EXIT WHEN (i = p_icpv_tbl.LAST);
1821 i := p_icpv_tbl.NEXT(i);
1822
1823 END IF;
1824
1825 END LOOP;
1826
1827 END IF;
1828
1829 x_return_status := l_return_status;
1830
1831 EXCEPTION
1832
1833 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1834
1835 x_return_status := G_RET_STS_ERROR;
1836
1837 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1838
1839 x_return_status := G_RET_STS_UNEXP_ERROR;
1843 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1840
1841 WHEN OTHERS THEN
1842
1844 p_msg_name => G_DB_ERROR,
1845 p_token1 => G_PROG_NAME_TOKEN,
1846 p_token1_value => l_api_name,
1847 p_token2 => G_SQLCODE_TOKEN,
1848 p_token2_value => sqlcode,
1849 p_token3 => G_SQLERRM_TOKEN,
1850 p_token3_value => sqlerrm);
1851
1852 x_return_status := G_RET_STS_UNEXP_ERROR;
1853
1854 END update_row;
1855
1856
1857 -------------------
1858 -- delete_row (REC)
1859 -------------------
1860 PROCEDURE delete_row(
1861 p_api_version IN NUMBER,
1862 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1863 x_return_status OUT NOCOPY VARCHAR2,
1864 x_msg_count OUT NOCOPY NUMBER,
1865 x_msg_data OUT NOCOPY VARCHAR2,
1866 p_icpv_rec IN icpv_rec_type) IS
1867
1868 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'delete_row (REC)';
1869
1870 BEGIN
1871
1872 DELETE FROM OKL_ITM_CAT_RV_PRCS WHERE id = p_icpv_rec.id;
1873
1874 x_return_status := G_RET_STS_SUCCESS;
1875
1876 EXCEPTION
1877
1878 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1879
1880 x_return_status := G_RET_STS_ERROR;
1881
1882 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1883
1884 x_return_status := G_RET_STS_UNEXP_ERROR;
1885
1886 WHEN OTHERS THEN
1887
1888 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1889 p_msg_name => G_DB_ERROR,
1890 p_token1 => G_PROG_NAME_TOKEN,
1891 p_token1_value => l_api_name,
1892 p_token2 => G_SQLCODE_TOKEN,
1893 p_token2_value => sqlcode,
1894 p_token3 => G_SQLERRM_TOKEN,
1895 p_token3_value => sqlerrm);
1896
1897 x_return_status := G_RET_STS_UNEXP_ERROR;
1898
1899 END delete_row;
1900
1901 -------------------
1902 -- delete_row (TBL)
1903 -------------------
1904 PROCEDURE delete_row(
1905 p_api_version IN NUMBER,
1906 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1907 x_return_status OUT NOCOPY VARCHAR2,
1908 x_msg_count OUT NOCOPY NUMBER,
1909 x_msg_data OUT NOCOPY VARCHAR2,
1910 p_icpv_tbl IN icpv_tbl_type) IS
1911
1912 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'delete_row (TBL)';
1913 l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
1914 i BINARY_INTEGER;
1915
1916 BEGIN
1917
1918 IF (p_icpv_tbl.COUNT > 0) THEN
1919
1920 i := p_icpv_tbl.FIRST;
1921
1922 LOOP
1923
1924 IF p_icpv_tbl.EXISTS(i) THEN
1925
1926 delete_row (p_api_version => G_API_VERSION,
1927 p_init_msg_list => G_FALSE,
1928 x_return_status => l_return_status,
1929 x_msg_count => x_msg_count,
1930 x_msg_data => x_msg_data,
1931 p_icpv_rec => p_icpv_tbl(i));
1932
1933 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1934 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1935 ELSIF l_return_status = G_RET_STS_ERROR THEN
1936 RAISE OKL_API.G_EXCEPTION_ERROR;
1937 END IF;
1938
1939 EXIT WHEN (i = p_icpv_tbl.LAST);
1940 i := p_icpv_tbl.NEXT(i);
1941
1942 END IF;
1943
1944 END LOOP;
1945
1946 END IF;
1947
1948 x_return_status := l_return_status;
1949
1950 EXCEPTION
1951
1952 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1953
1954 x_return_status := G_RET_STS_ERROR;
1955
1956 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1957
1958 x_return_status := G_RET_STS_UNEXP_ERROR;
1959
1960 WHEN OTHERS THEN
1961
1962 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1963 p_msg_name => G_DB_ERROR,
1964 p_token1 => G_PROG_NAME_TOKEN,
1965 p_token1_value => l_api_name,
1966 p_token2 => G_SQLCODE_TOKEN,
1967 p_token2_value => sqlcode,
1968 p_token3 => G_SQLERRM_TOKEN,
1969 p_token3_value => sqlerrm);
1970
1971 x_return_status := G_RET_STS_UNEXP_ERROR;
1972
1973 END delete_row;
1974
1975 END OKL_icp_PVT;