[Home] [Help]
PACKAGE BODY: APPS.OKL_QUL_PVT
Source
1 PACKAGE BODY OKL_QUL_PVT AS
2 /* $Header: OKLSQULB.pls 120.1 2005/08/31 23:33:14 rravikir noship $ */
3
4 -----------------------------
5 -- FUNCTION null_out_defaults
6 -----------------------------
7 FUNCTION null_out_defaults (p_qulv_rec IN qulv_rec_type) RETURN qulv_rec_type IS
8
9 l_qulv_rec qulv_rec_type;
10
11 BEGIN
12
13 l_qulv_rec := p_qulv_rec;
14
15 -- Not applicable to ID and OBJECT_VERSION_NUMBER
16
17 IF l_qulv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
18 l_qulv_rec.attribute_category := NULL;
19 END IF;
20 IF l_qulv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
21 l_qulv_rec.attribute1 := NULL;
22 END IF;
23 IF l_qulv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
24 l_qulv_rec.attribute2 := NULL;
25 END IF;
26 IF l_qulv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
27 l_qulv_rec.attribute3 := NULL;
28 END IF;
29 IF l_qulv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
30 l_qulv_rec.attribute4 := NULL;
31 END IF;
32 IF l_qulv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
33 l_qulv_rec.attribute5 := NULL;
34 END IF;
35 IF l_qulv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
36 l_qulv_rec.attribute6 := NULL;
37 END IF;
38 IF l_qulv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
39 l_qulv_rec.attribute7 := NULL;
40 END IF;
41 IF l_qulv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
42 l_qulv_rec.attribute8 := NULL;
43 END IF;
44 IF l_qulv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
45 l_qulv_rec.attribute9 := NULL;
46 END IF;
47 IF l_qulv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
48 l_qulv_rec.attribute10 := NULL;
49 END IF;
50 IF l_qulv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
51 l_qulv_rec.attribute11 := NULL;
52 END IF;
53 IF l_qulv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
54 l_qulv_rec.attribute12 := NULL;
55 END IF;
56 IF l_qulv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
57 l_qulv_rec.attribute13 := NULL;
58 END IF;
59 IF l_qulv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
60 l_qulv_rec.attribute14 := NULL;
61 END IF;
62 IF l_qulv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
63 l_qulv_rec.attribute15 := NULL;
64 END IF;
65 IF l_qulv_rec.subpool_trx_id = FND_API.G_MISS_NUM THEN
66 l_qulv_rec.subpool_trx_id := NULL;
67 END IF;
68 IF l_qulv_rec.source_type_code = FND_API.G_MISS_CHAR THEN
69 l_qulv_rec.source_type_code := NULL;
70 END IF;
71 IF l_qulv_rec.source_object_id = FND_API.G_MISS_NUM THEN
72 l_qulv_rec.source_object_id := NULL;
73 END IF;
74 IF l_qulv_rec.asset_number = FND_API.G_MISS_CHAR THEN
75 l_qulv_rec.asset_number := NULL;
76 END IF;
77 IF l_qulv_rec.asset_start_date = FND_API.G_MISS_DATE THEN
78 l_qulv_rec.asset_start_date := NULL;
79 END IF;
80 IF l_qulv_rec.subsidy_pool_id = FND_API.G_MISS_NUM THEN
81 l_qulv_rec.subsidy_pool_id := NULL;
82 END IF;
83 IF l_qulv_rec.subsidy_pool_amount = FND_API.G_MISS_NUM THEN
84 l_qulv_rec.subsidy_pool_amount := NULL;
85 END IF;
86 IF l_qulv_rec.subsidy_pool_currency_code = FND_API.G_MISS_CHAR THEN
87 l_qulv_rec.subsidy_pool_currency_code := NULL;
88 END IF;
89 IF l_qulv_rec.subsidy_id = FND_API.G_MISS_NUM THEN
90 l_qulv_rec.subsidy_id := NULL;
91 END IF;
92 IF l_qulv_rec.subsidy_amount = FND_API.G_MISS_NUM THEN
93 l_qulv_rec.subsidy_amount := NULL;
94 END IF;
95 IF l_qulv_rec.subsidy_currency_code = FND_API.G_MISS_CHAR THEN
96 l_qulv_rec.subsidy_currency_code := NULL;
97 END IF;
98 IF l_qulv_rec.vendor_id = FND_API.G_MISS_NUM THEN
99 l_qulv_rec.vendor_id := NULL;
100 END IF;
101 IF l_qulv_rec.conversion_rate = FND_API.G_MISS_NUM THEN
102 l_qulv_rec.conversion_rate := NULL;
103 END IF;
104
105 RETURN l_qulv_rec;
106
107 END null_out_defaults;
108
109
110 -------------------
111 -- FUNCTION get_rec
112 -------------------
113 FUNCTION get_rec (p_id IN NUMBER
114 ,x_return_status OUT NOCOPY VARCHAR2) RETURN qulv_rec_type IS
115
116 l_qulv_rec qulv_rec_type;
117 l_prog_name VARCHAR2(61);
118
119 BEGIN
120
121 l_prog_name := G_PKG_NAME||'.get_rec';
122
123 SELECT
124 id
125 ,object_version_number
126 ,attribute_category
127 ,attribute1
128 ,attribute2
129 ,attribute3
130 ,attribute4
131 ,attribute5
132 ,attribute6
133 ,attribute7
134 ,attribute8
135 ,attribute9
136 ,attribute10
137 ,attribute11
138 ,attribute12
139 ,attribute13
140 ,attribute14
141 ,attribute15
142 ,subpool_trx_id
143 ,source_type_code
144 ,source_object_id
145 ,asset_number
146 ,asset_start_date
147 ,subsidy_pool_id
148 ,subsidy_pool_amount
149 ,subsidy_pool_currency_code
150 ,subsidy_id
151 ,subsidy_amount
152 ,subsidy_currency_code
153 ,vendor_id
154 ,conversion_rate
155 INTO
156 l_qulv_rec.id
157 ,l_qulv_rec.object_version_number
158 ,l_qulv_rec.attribute_category
159 ,l_qulv_rec.attribute1
160 ,l_qulv_rec.attribute2
161 ,l_qulv_rec.attribute3
162 ,l_qulv_rec.attribute4
163 ,l_qulv_rec.attribute5
164 ,l_qulv_rec.attribute6
165 ,l_qulv_rec.attribute7
166 ,l_qulv_rec.attribute8
167 ,l_qulv_rec.attribute9
168 ,l_qulv_rec.attribute10
169 ,l_qulv_rec.attribute11
170 ,l_qulv_rec.attribute12
171 ,l_qulv_rec.attribute13
172 ,l_qulv_rec.attribute14
173 ,l_qulv_rec.attribute15
174 ,l_qulv_rec.subpool_trx_id
175 ,l_qulv_rec.source_type_code
176 ,l_qulv_rec.source_object_id
177 ,l_qulv_rec.asset_number
178 ,l_qulv_rec.asset_start_date
179 ,l_qulv_rec.subsidy_pool_id
180 ,l_qulv_rec.subsidy_pool_amount
181 ,l_qulv_rec.subsidy_pool_currency_code
182 ,l_qulv_rec.subsidy_id
183 ,l_qulv_rec.subsidy_amount
184 ,l_qulv_rec.subsidy_currency_code
185 ,l_qulv_rec.vendor_id
186 ,l_qulv_rec.conversion_rate
187 FROM okl_quote_subpool_usage_v
188 WHERE id = p_id;
189
190 x_return_status := G_RET_STS_SUCCESS;
191 RETURN l_qulv_rec;
192
193 EXCEPTION
194
195 WHEN OTHERS THEN
196
197 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
198 p_msg_name => G_DB_ERROR,
199 p_token1 => G_PROG_NAME_TOKEN,
200 p_token1_value => l_prog_name,
201 p_token2 => G_SQLCODE_TOKEN,
202 p_token2_value => sqlcode,
203 p_token3 => G_SQLERRM_TOKEN,
204 p_token3_value => sqlerrm);
205
206 x_return_status := G_RET_STS_UNEXP_ERROR;
207
208 END get_rec;
209
210
211 ------------------------
212 -- PROCEDURE validate_id
213 ------------------------
214 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
215 BEGIN
216 IF p_id IS NULL THEN
217 OKL_API.set_message(p_app_name => G_APP_NAME,
218 p_msg_name => G_COL_ERROR,
219 p_token1 => G_COL_NAME_TOKEN,
220 p_token1_value => 'id',
221 p_token2 => G_PKG_NAME_TOKEN,
222 p_token2_value => G_PKG_NAME);
223 RAISE OKL_API.G_EXCEPTION_ERROR;
224 END IF;
225 x_return_status := G_RET_STS_SUCCESS;
226 END validate_id;
227
228
229 -------------------------------------------
230 -- PROCEDURE validate_object_version_number
231 -------------------------------------------
232 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
233 BEGIN
234 IF p_object_version_number IS NULL THEN
235 OKL_API.set_message(p_app_name => G_APP_NAME,
236 p_msg_name => G_COL_ERROR,
237 p_token1 => G_COL_NAME_TOKEN,
238 p_token1_value => 'object_version_number',
239 p_token2 => G_PKG_NAME_TOKEN,
240 p_token2_value => G_PKG_NAME);
241 RAISE OKL_API.G_EXCEPTION_ERROR;
242 END IF;
243 x_return_status := G_RET_STS_SUCCESS;
244 END validate_object_version_number;
245
246
247 -------------------------------
248 -- FUNCTION validate_attributes
249 -------------------------------
250 FUNCTION validate_attributes (p_qulv_rec IN qulv_rec_type) RETURN VARCHAR2 IS
251
252 l_return_status VARCHAR2(1);
253
254 BEGIN
255
256 validate_id (l_return_status, p_qulv_rec.id);
257 validate_object_version_number (l_return_status, p_qulv_rec.object_version_number);
258
259 RETURN l_return_status;
260
261 END validate_attributes;
262
263 ----------------------------
264 -- PROCEDURE validate_record
265 ----------------------------
266 FUNCTION validate_record (p_qulv_rec IN qulv_rec_type) RETURN VARCHAR2 IS
267 l_return_status VARCHAR2(1);
268 BEGIN
269 RETURN G_RET_STS_SUCCESS;
270 END validate_record;
271
272
273 -----------------------------
274 -- PROECDURE migrate (V -> B)
275 -----------------------------
276 PROCEDURE migrate (p_from IN qulv_rec_type, p_to IN OUT NOCOPY qul_rec_type) IS
277
278 BEGIN
279
280 p_to.id := p_from.id;
281 p_to.object_version_number := p_from.object_version_number;
282 p_to.attribute_category := p_from.attribute_category;
283 p_to.attribute1 := p_from.attribute1;
284 p_to.attribute2 := p_from.attribute2;
285 p_to.attribute3 := p_from.attribute3;
286 p_to.attribute4 := p_from.attribute4;
287 p_to.attribute5 := p_from.attribute5;
288 p_to.attribute6 := p_from.attribute6;
289 p_to.attribute7 := p_from.attribute7;
290 p_to.attribute8 := p_from.attribute8;
291 p_to.attribute9 := p_from.attribute9;
292 p_to.attribute10 := p_from.attribute10;
293 p_to.attribute11 := p_from.attribute11;
294 p_to.attribute12 := p_from.attribute12;
295 p_to.attribute13 := p_from.attribute13;
296 p_to.attribute14 := p_from.attribute14;
297 p_to.attribute15 := p_from.attribute15;
298 p_to.subpool_trx_id := p_from.subpool_trx_id;
299 p_to.source_type_code := p_from.source_type_code;
300 p_to.source_object_id := p_from.source_object_id;
301 p_to.asset_number := p_from.asset_number;
302 p_to.asset_start_date := p_from.asset_start_date;
303 p_to.subsidy_pool_id := p_from.subsidy_pool_id;
304 p_to.subsidy_pool_amount := p_from.subsidy_pool_amount;
305 p_to.subsidy_pool_currency_code := p_from.subsidy_pool_currency_code;
306 p_to.subsidy_id := p_from.subsidy_id;
307 p_to.subsidy_amount := p_from.subsidy_amount;
308 p_to.subsidy_currency_code := p_from.subsidy_currency_code;
309 p_to.vendor_id := p_from.vendor_id;
310 p_to.conversion_rate := p_from.conversion_rate;
311
312 END migrate;
313
314
315 ---------------------------
316 -- PROCEDURE insert_row (B)
317 ---------------------------
318 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_qul_rec IN qul_rec_type) IS
319
320 l_prog_name VARCHAR2(61);
321
322 BEGIN
323
324 l_prog_name := G_PKG_NAME||'.insert_row (B)';
325
326 INSERT INTO okl_quote_subpool_usage (
327 id
328 ,object_version_number
329 ,attribute_category
330 ,attribute1
331 ,attribute2
332 ,attribute3
333 ,attribute4
334 ,attribute5
335 ,attribute6
336 ,attribute7
337 ,attribute8
338 ,attribute9
339 ,attribute10
340 ,attribute11
341 ,attribute12
342 ,attribute13
343 ,attribute14
344 ,attribute15
345 ,created_by
346 ,creation_date
347 ,last_updated_by
348 ,last_update_date
349 ,last_update_login
350 ,subpool_trx_id
351 ,source_type_code
352 ,source_object_id
353 ,asset_number
354 ,asset_start_date
355 ,subsidy_pool_id
356 ,subsidy_pool_amount
357 ,subsidy_pool_currency_code
358 ,subsidy_id
359 ,subsidy_amount
360 ,subsidy_currency_code
361 ,vendor_id
362 ,conversion_rate
363 )
364 VALUES
365 (
366 p_qul_rec.id
367 ,p_qul_rec.object_version_number
368 ,p_qul_rec.attribute_category
369 ,p_qul_rec.attribute1
370 ,p_qul_rec.attribute2
371 ,p_qul_rec.attribute3
372 ,p_qul_rec.attribute4
373 ,p_qul_rec.attribute5
374 ,p_qul_rec.attribute6
375 ,p_qul_rec.attribute7
376 ,p_qul_rec.attribute8
377 ,p_qul_rec.attribute9
378 ,p_qul_rec.attribute10
379 ,p_qul_rec.attribute11
380 ,p_qul_rec.attribute12
381 ,p_qul_rec.attribute13
382 ,p_qul_rec.attribute14
383 ,p_qul_rec.attribute15
384 ,G_USER_ID
385 ,SYSDATE
386 ,G_USER_ID
387 ,SYSDATE
388 ,G_LOGIN_ID
389 ,p_qul_rec.subpool_trx_id
390 ,p_qul_rec.source_type_code
391 ,p_qul_rec.source_object_id
392 ,p_qul_rec.asset_number
393 ,p_qul_rec.asset_start_date
394 ,p_qul_rec.subsidy_pool_id
395 ,p_qul_rec.subsidy_pool_amount
396 ,p_qul_rec.subsidy_pool_currency_code
397 ,p_qul_rec.subsidy_id
398 ,p_qul_rec.subsidy_amount
399 ,p_qul_rec.subsidy_currency_code
400 ,p_qul_rec.vendor_id
401 ,p_qul_rec.conversion_rate
402 );
403
404 x_return_status := G_RET_STS_SUCCESS;
405
406 EXCEPTION
407
408 WHEN OTHERS THEN
409
410 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
411 p_msg_name => G_DB_ERROR,
412 p_token1 => G_PROG_NAME_TOKEN,
413 p_token1_value => l_prog_name,
414 p_token2 => G_SQLCODE_TOKEN,
415 p_token2_value => sqlcode,
416 p_token3 => G_SQLERRM_TOKEN,
417 p_token3_value => sqlerrm);
418
419 x_return_status := G_RET_STS_UNEXP_ERROR;
420
421 END insert_row;
422
423
424 ---------------------------
425 -- PROCEDURE insert_row (V)
426 ---------------------------
427 PROCEDURE insert_row (
428 x_return_status OUT NOCOPY VARCHAR2,
429 p_qulv_rec IN qulv_rec_type,
430 x_qulv_rec OUT NOCOPY qulv_rec_type) IS
431
432 l_return_status VARCHAR2(1);
433
434 l_qulv_rec qulv_rec_type;
435 l_qul_rec qul_rec_type;
436
437 l_prog_name VARCHAR2(61);
438
439 BEGIN
440
441 l_prog_name := G_PKG_NAME||'.insert_row (V)';
442
443 l_qulv_rec := null_out_defaults (p_qulv_rec);
444
445 SELECT okl_qul_seq.nextval INTO l_qulv_rec.ID FROM DUAL;
446
447 l_qulv_rec.OBJECT_VERSION_NUMBER := 1;
448
449 l_return_status := validate_attributes(l_qulv_rec);
450
451 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
453 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
454 RAISE OKL_API.G_EXCEPTION_ERROR;
455 END IF;
456
457 l_return_status := validate_record(l_qulv_rec);
458
459 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
460 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
461 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
462 RAISE OKL_API.G_EXCEPTION_ERROR;
463 END IF;
464
465 migrate (l_qulv_rec, l_qul_rec);
466
467 insert_row (x_return_status => l_return_status, p_qul_rec => l_qul_rec);
468
469 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
470 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
471 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
472 RAISE OKL_API.G_EXCEPTION_ERROR;
473 END IF;
474
475 x_qulv_rec := l_qulv_rec;
476 x_return_status := l_return_status;
477
478 EXCEPTION
479
480 WHEN OKL_API.G_EXCEPTION_ERROR THEN
481
482 x_return_status := G_RET_STS_ERROR;
483
484 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
485
486 x_return_status := G_RET_STS_UNEXP_ERROR;
487
488 WHEN OTHERS THEN
489
490 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
491 p_msg_name => G_DB_ERROR,
492 p_token1 => G_PROG_NAME_TOKEN,
493 p_token1_value => l_prog_name,
494 p_token2 => G_SQLCODE_TOKEN,
495 p_token2_value => sqlcode,
496 p_token3 => G_SQLERRM_TOKEN,
497 p_token3_value => sqlerrm);
498
499 x_return_status := G_RET_STS_UNEXP_ERROR;
500
501 END insert_row;
502
503
504 -----------------------------
505 -- PROCEDURE insert_row (REC)
506 -----------------------------
507 PROCEDURE insert_row(
508 p_api_version IN NUMBER,
509 p_init_msg_list IN VARCHAR2,
510 x_return_status OUT NOCOPY VARCHAR2,
511 x_msg_count OUT NOCOPY NUMBER,
512 x_msg_data OUT NOCOPY VARCHAR2,
513 p_qulv_rec IN qulv_rec_type,
514 x_qulv_rec OUT NOCOPY qulv_rec_type) IS
515
516 l_return_status VARCHAR2(1);
517
518 l_prog_name VARCHAR2(61);
519
520 BEGIN
521
522 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
523
524 IF p_init_msg_list = G_TRUE THEN
525 FND_MSG_PUB.initialize;
526 END IF;
527
528 insert_row (x_return_status => l_return_status,
529 p_qulv_rec => p_qulv_rec,
530 x_qulv_rec => x_qulv_rec);
531
532 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
533 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
534 ELSIF l_return_status = G_RET_STS_ERROR THEN
535 RAISE OKL_API.G_EXCEPTION_ERROR;
536 END IF;
537
538 x_return_status := l_return_status;
539
540 EXCEPTION
541
542 WHEN OKL_API.G_EXCEPTION_ERROR THEN
543
544 x_return_status := G_RET_STS_ERROR;
545
546 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
547
548 x_return_status := G_RET_STS_UNEXP_ERROR;
549
550 WHEN OTHERS THEN
551
552 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
553 p_msg_name => G_DB_ERROR,
554 p_token1 => G_PROG_NAME_TOKEN,
555 p_token1_value => l_prog_name,
556 p_token2 => G_SQLCODE_TOKEN,
557 p_token2_value => sqlcode,
558 p_token3 => G_SQLERRM_TOKEN,
559 p_token3_value => sqlerrm);
560
561 x_return_status := G_RET_STS_UNEXP_ERROR;
562
563 END insert_row;
564
565
566 -----------------------------
567 -- PROCEDURE insert_row (TBL)
568 -----------------------------
569 PROCEDURE insert_row(
570 p_api_version IN NUMBER,
571 p_init_msg_list IN VARCHAR2,
572 x_return_status OUT NOCOPY VARCHAR2,
573 x_msg_count OUT NOCOPY NUMBER,
574 x_msg_data OUT NOCOPY VARCHAR2,
575 p_qulv_tbl IN qulv_tbl_type,
576 x_qulv_tbl OUT NOCOPY qulv_tbl_type) IS
577
578 l_return_status VARCHAR2(1);
579 i BINARY_INTEGER;
580
581 l_prog_name VARCHAR2(61);
582
583 BEGIN
584
585 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
586
587 IF p_init_msg_list = G_TRUE THEN
588 FND_MSG_PUB.initialize;
589 END IF;
590
591 IF (p_qulv_tbl.COUNT > 0) THEN
592 i := p_qulv_tbl.FIRST;
593 LOOP
594 IF p_qulv_tbl.EXISTS(i) THEN
595
596 insert_row (x_return_status => l_return_status,
597 p_qulv_rec => p_qulv_tbl(i),
598 x_qulv_rec => x_qulv_tbl(i));
599
600 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
601 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
602 ELSIF l_return_status = G_RET_STS_ERROR THEN
603 RAISE OKL_API.G_EXCEPTION_ERROR;
604 END IF;
605
606 EXIT WHEN (i = p_qulv_tbl.LAST);
607 i := p_qulv_tbl.NEXT(i);
608
609 END IF;
610
611 END LOOP;
612
613 ELSE
614
615 l_return_status := G_RET_STS_SUCCESS;
616
617 END IF;
618
619 x_return_status := l_return_status;
620
621 EXCEPTION
622
623 WHEN OKL_API.G_EXCEPTION_ERROR THEN
624
625 x_return_status := G_RET_STS_ERROR;
626
627 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
628
629 x_return_status := G_RET_STS_UNEXP_ERROR;
630
631 WHEN OTHERS THEN
632
633 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
634 p_msg_name => G_DB_ERROR,
635 p_token1 => G_PROG_NAME_TOKEN,
636 p_token1_value => l_prog_name,
637 p_token2 => G_SQLCODE_TOKEN,
638 p_token2_value => sqlcode,
639 p_token3 => G_SQLERRM_TOKEN,
640 p_token3_value => sqlerrm);
641
642 x_return_status := G_RET_STS_UNEXP_ERROR;
643
644 END insert_row;
645
646
647 ---------------------
648 -- PROCEDURE lock_row
649 ---------------------
650 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_qul_rec IN qul_rec_type) IS
651
652 E_Resource_Busy EXCEPTION;
653
654 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
655
656 CURSOR lock_csr IS
657 SELECT OBJECT_VERSION_NUMBER
658 FROM OKL_QUOTE_SUBPOOL_USAGE
659 WHERE ID = p_qul_rec.id
660 AND OBJECT_VERSION_NUMBER = p_qul_rec.object_version_number
661 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
662
663 CURSOR lchk_csr IS
664 SELECT OBJECT_VERSION_NUMBER
665 FROM OKL_QUOTE_SUBPOOL_USAGE
666 WHERE ID = p_qul_rec.id;
667
668 l_object_version_number NUMBER;
669 lc_object_version_number NUMBER;
670
671 l_prog_name VARCHAR2(61);
672
673 BEGIN
674
675 l_prog_name := G_PKG_NAME||'.lock_row';
676
677 BEGIN
678 OPEN lock_csr;
679 FETCH lock_csr INTO l_object_version_number;
680 CLOSE lock_csr;
681 EXCEPTION
682 WHEN E_Resource_Busy THEN
683
684 IF (lock_csr%ISOPEN) THEN
685 CLOSE lock_csr;
686 END IF;
687 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
688 p_msg_name => G_OVN_ERROR2,
689 p_token1 => G_PROG_NAME_TOKEN,
690 p_token1_value => l_prog_name);
691 RAISE OKL_API.G_EXCEPTION_ERROR;
692 END;
693
694 IF l_object_version_number IS NULL THEN
695
696 OPEN lchk_csr;
697 FETCH lchk_csr INTO lc_object_version_number;
698 CLOSE lchk_csr;
699
700 IF lc_object_version_number IS NULL THEN
701
702 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
703 p_msg_name => G_OVN_ERROR3,
704 p_token1 => G_PROG_NAME_TOKEN,
705 p_token1_value => l_prog_name);
706
707 ELSIF lc_object_version_number <> p_qul_rec.object_version_number THEN
708
709 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
710 p_msg_name => G_OVN_ERROR,
711 p_token1 => G_PROG_NAME_TOKEN,
712 p_token1_value => l_prog_name);
713
714 END IF;
715
716 RAISE OKL_API.G_EXCEPTION_ERROR;
717
718 END IF;
719
720 x_return_status := G_RET_STS_SUCCESS;
721
722 EXCEPTION
723
724 WHEN OKL_API.G_EXCEPTION_ERROR THEN
725
726 x_return_status := G_RET_STS_ERROR;
727
728 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
729
730 x_return_status := G_RET_STS_UNEXP_ERROR;
731
732 WHEN OTHERS THEN
733
734 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
735 p_msg_name => G_DB_ERROR,
736 p_token1 => G_PROG_NAME_TOKEN,
737 p_token1_value => l_prog_name,
738 p_token2 => G_SQLCODE_TOKEN,
739 p_token2_value => sqlcode,
740 p_token3 => G_SQLERRM_TOKEN,
741 p_token3_value => sqlerrm);
742
743 x_return_status := G_RET_STS_UNEXP_ERROR;
744
745 END lock_row;
746
747
748 ---------------------------
749 -- PROCEDURE update_row (B)
750 ---------------------------
751 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_qul_rec IN qul_rec_type) IS
752
753 l_return_status VARCHAR2(1);
754
755 l_prog_name VARCHAR2(61);
756
757 BEGIN
758
759 l_prog_name := G_PKG_NAME||'.update_row (B)';
760
761 lock_row (x_return_status => l_return_status, p_qul_rec => p_qul_rec);
762
763 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
764 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
765 ELSIF l_return_status = G_RET_STS_ERROR THEN
766 RAISE OKL_API.G_EXCEPTION_ERROR;
767 END IF;
768
769 UPDATE okl_quote_subpool_usage
770 SET
771 object_version_number = p_qul_rec.object_version_number+1
772 ,attribute_category = p_qul_rec.attribute_category
773 ,attribute1 = p_qul_rec.attribute1
774 ,attribute2 = p_qul_rec.attribute2
775 ,attribute3 = p_qul_rec.attribute3
776 ,attribute4 = p_qul_rec.attribute4
777 ,attribute5 = p_qul_rec.attribute5
778 ,attribute6 = p_qul_rec.attribute6
779 ,attribute7 = p_qul_rec.attribute7
780 ,attribute8 = p_qul_rec.attribute8
781 ,attribute9 = p_qul_rec.attribute9
782 ,attribute10 = p_qul_rec.attribute10
783 ,attribute11 = p_qul_rec.attribute11
784 ,attribute12 = p_qul_rec.attribute12
785 ,attribute13 = p_qul_rec.attribute13
786 ,attribute14 = p_qul_rec.attribute14
787 ,attribute15 = p_qul_rec.attribute15
788 ,subpool_trx_id = p_qul_rec.subpool_trx_id
789 ,source_type_code = p_qul_rec.source_type_code
790 ,source_object_id = p_qul_rec.source_object_id
791 ,asset_number = p_qul_rec.asset_number
792 ,asset_start_date = p_qul_rec.asset_start_date
793 ,subsidy_pool_id = p_qul_rec.subsidy_pool_id
794 ,subsidy_pool_amount = p_qul_rec.subsidy_pool_amount
795 ,subsidy_pool_currency_code = p_qul_rec.subsidy_pool_currency_code
796 ,subsidy_id = p_qul_rec.subsidy_id
797 ,subsidy_amount = p_qul_rec.subsidy_amount
798 ,subsidy_currency_code = p_qul_rec.subsidy_currency_code
799 ,vendor_id = p_qul_rec.vendor_id
800 ,conversion_rate = p_qul_rec.conversion_rate
801 WHERE id = p_qul_rec.id;
802
803 x_return_status := l_return_status;
804
805 EXCEPTION
806
807 WHEN OKL_API.G_EXCEPTION_ERROR THEN
808 x_return_status := G_RET_STS_ERROR;
809
810 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
811 x_return_status := G_RET_STS_UNEXP_ERROR;
812
813 WHEN OTHERS THEN
814 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
815 p_msg_name => G_DB_ERROR,
816 p_token1 => G_PROG_NAME_TOKEN,
817 p_token1_value => l_prog_name,
818 p_token2 => G_SQLCODE_TOKEN,
819 p_token2_value => sqlcode,
820 p_token3 => G_SQLERRM_TOKEN,
821 p_token3_value => sqlerrm);
822
823 x_return_status := G_RET_STS_UNEXP_ERROR;
824
825 END update_row;
826
827
828 ---------------------------
829 -- PROCEDURE update_row (V)
830 ---------------------------
831 PROCEDURE update_row (
832 x_return_status OUT NOCOPY VARCHAR2,
833 p_qulv_rec IN qulv_rec_type,
834 x_qulv_rec OUT NOCOPY qulv_rec_type) IS
835
836 l_prog_name VARCHAR2(61);
837
838 l_return_status VARCHAR2(1);
839 l_qulv_rec qulv_rec_type;
840 l_qul_rec qul_rec_type;
841
842 ----------------------
843 -- populate_new_record
844 ----------------------
845 FUNCTION populate_new_record (p_qulv_rec IN qulv_rec_type,
846 x_qulv_rec OUT NOCOPY qulv_rec_type) RETURN VARCHAR2 IS
847
848 l_prog_name VARCHAR2(61);
849 l_return_status VARCHAR2(1);
850 l_db_qulv_rec qulv_rec_type;
851
852 BEGIN
853
854 l_prog_name := G_PKG_NAME||'.populate_new_record';
855
856 x_qulv_rec := p_qulv_rec;
857 l_db_qulv_rec := get_rec (p_qulv_rec.id, l_return_status);
858
859 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
860 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
861 ELSIF l_return_status = G_RET_STS_ERROR THEN
862 RAISE OKL_API.G_EXCEPTION_ERROR;
863 END IF;
864
865 IF x_qulv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
866 x_qulv_rec.attribute_category := l_db_qulv_rec.attribute_category;
867 END IF;
868 IF x_qulv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
869 x_qulv_rec.attribute1 := l_db_qulv_rec.attribute1;
870 END IF;
871 IF x_qulv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
872 x_qulv_rec.attribute2 := l_db_qulv_rec.attribute2;
873 END IF;
874 IF x_qulv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
875 x_qulv_rec.attribute3 := l_db_qulv_rec.attribute3;
876 END IF;
877 IF x_qulv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
878 x_qulv_rec.attribute4 := l_db_qulv_rec.attribute4;
879 END IF;
880 IF x_qulv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
881 x_qulv_rec.attribute5 := l_db_qulv_rec.attribute5;
882 END IF;
883 IF x_qulv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
884 x_qulv_rec.attribute6 := l_db_qulv_rec.attribute6;
885 END IF;
886 IF x_qulv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
887 x_qulv_rec.attribute7 := l_db_qulv_rec.attribute7;
888 END IF;
889 IF x_qulv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
890 x_qulv_rec.attribute8 := l_db_qulv_rec.attribute8;
891 END IF;
892 IF x_qulv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
893 x_qulv_rec.attribute9 := l_db_qulv_rec.attribute9;
894 END IF;
895 IF x_qulv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
896 x_qulv_rec.attribute10 := l_db_qulv_rec.attribute10;
897 END IF;
898 IF x_qulv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
899 x_qulv_rec.attribute11 := l_db_qulv_rec.attribute11;
900 END IF;
901 IF x_qulv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
902 x_qulv_rec.attribute12 := l_db_qulv_rec.attribute12;
903 END IF;
904 IF x_qulv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
905 x_qulv_rec.attribute13 := l_db_qulv_rec.attribute13;
906 END IF;
907 IF x_qulv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
908 x_qulv_rec.attribute14 := l_db_qulv_rec.attribute14;
909 END IF;
910 IF x_qulv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
911 x_qulv_rec.attribute15 := l_db_qulv_rec.attribute15;
912 END IF;
913 IF x_qulv_rec.subpool_trx_id = FND_API.G_MISS_NUM THEN
914 x_qulv_rec.subpool_trx_id := l_db_qulv_rec.subpool_trx_id;
915 END IF;
916 IF x_qulv_rec.source_type_code = FND_API.G_MISS_CHAR THEN
917 x_qulv_rec.source_type_code := l_db_qulv_rec.source_type_code;
918 END IF;
919 IF x_qulv_rec.source_object_id = FND_API.G_MISS_NUM THEN
920 x_qulv_rec.source_object_id := l_db_qulv_rec.source_object_id;
921 END IF;
922 IF x_qulv_rec.asset_number = FND_API.G_MISS_CHAR THEN
923 x_qulv_rec.asset_number := l_db_qulv_rec.asset_number;
924 END IF;
925 IF x_qulv_rec.asset_start_date = FND_API.G_MISS_DATE THEN
926 x_qulv_rec.asset_start_date := l_db_qulv_rec.asset_start_date;
927 END IF;
928 IF x_qulv_rec.subsidy_pool_id = FND_API.G_MISS_NUM THEN
929 x_qulv_rec.subsidy_pool_id := l_db_qulv_rec.subsidy_pool_id;
930 END IF;
931 IF x_qulv_rec.subsidy_pool_amount = FND_API.G_MISS_NUM THEN
932 x_qulv_rec.subsidy_pool_amount := l_db_qulv_rec.subsidy_pool_amount;
933 END IF;
934 IF x_qulv_rec.subsidy_pool_currency_code = FND_API.G_MISS_CHAR THEN
935 x_qulv_rec.subsidy_pool_currency_code := l_db_qulv_rec.subsidy_pool_currency_code;
936 END IF;
937 IF x_qulv_rec.subsidy_id = FND_API.G_MISS_NUM THEN
938 x_qulv_rec.subsidy_id := l_db_qulv_rec.subsidy_id;
939 END IF;
940 IF x_qulv_rec.subsidy_amount = FND_API.G_MISS_NUM THEN
941 x_qulv_rec.subsidy_amount := l_db_qulv_rec.subsidy_amount;
942 END IF;
943 IF x_qulv_rec.subsidy_currency_code = FND_API.G_MISS_CHAR THEN
944 x_qulv_rec.subsidy_currency_code := l_db_qulv_rec.subsidy_currency_code;
945 END IF;
946 IF x_qulv_rec.vendor_id = FND_API.G_MISS_NUM THEN
947 x_qulv_rec.vendor_id := l_db_qulv_rec.vendor_id;
948 END IF;
949 IF x_qulv_rec.conversion_rate = FND_API.G_MISS_NUM THEN
950 x_qulv_rec.conversion_rate := l_db_qulv_rec.conversion_rate;
951 END IF;
952
953 RETURN l_return_status;
954
955 EXCEPTION
956
957 WHEN OKL_API.G_EXCEPTION_ERROR THEN
958
959 x_return_status := G_RET_STS_ERROR;
960
961 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
962
963 x_return_status := G_RET_STS_UNEXP_ERROR;
964
965 WHEN OTHERS THEN
966
967 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
968 p_msg_name => G_DB_ERROR,
969 p_token1 => G_PROG_NAME_TOKEN,
970 p_token1_value => l_prog_name,
971 p_token2 => G_SQLCODE_TOKEN,
972 p_token2_value => sqlcode,
973 p_token3 => G_SQLERRM_TOKEN,
974 p_token3_value => sqlerrm);
975
976 x_return_status := G_RET_STS_UNEXP_ERROR;
977
978 END populate_new_record;
979
980 BEGIN
981
982 l_prog_name := G_PKG_NAME||'.update_row (V)';
983
984 l_return_status := populate_new_record (p_qulv_rec, l_qulv_rec);
985
986 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
987 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
988 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
989 RAISE OKL_API.G_EXCEPTION_ERROR;
990 END IF;
991
992 l_return_status := validate_attributes (l_qulv_rec);
993
994 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
995 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
996 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
997 RAISE OKL_API.G_EXCEPTION_ERROR;
998 END IF;
999
1000 l_return_status := validate_record (l_qulv_rec);
1001
1002 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1003 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1004 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1005 RAISE OKL_API.G_EXCEPTION_ERROR;
1006 END IF;
1007
1008 migrate (l_qulv_rec, l_qul_rec);
1009
1010 update_row (x_return_status => l_return_status, p_qul_rec => l_qul_rec);
1011
1012 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1013 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1014 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1015 RAISE OKL_API.G_EXCEPTION_ERROR;
1016 END IF;
1017
1018 x_return_status := l_return_status;
1019 x_qulv_rec := l_qulv_rec;
1020
1021 EXCEPTION
1022
1023 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1024
1025 x_return_status := G_RET_STS_ERROR;
1026
1027 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1028
1029 x_return_status := G_RET_STS_UNEXP_ERROR;
1030
1031 WHEN OTHERS THEN
1032
1033 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1034 p_msg_name => G_DB_ERROR,
1035 p_token1 => G_PROG_NAME_TOKEN,
1036 p_token1_value => l_prog_name,
1037 p_token2 => G_SQLCODE_TOKEN,
1038 p_token2_value => sqlcode,
1039 p_token3 => G_SQLERRM_TOKEN,
1040 p_token3_value => sqlerrm);
1041
1042 x_return_status := G_RET_STS_UNEXP_ERROR;
1043
1044 END update_row;
1045
1046
1047 -----------------------------
1048 -- PROCEDURE update_row (REC)
1049 -----------------------------
1050 PROCEDURE update_row(
1051 p_api_version IN NUMBER,
1052 p_init_msg_list IN VARCHAR2,
1053 x_return_status OUT NOCOPY VARCHAR2,
1054 x_msg_count OUT NOCOPY NUMBER,
1055 x_msg_data OUT NOCOPY VARCHAR2,
1056 p_qulv_rec IN qulv_rec_type,
1057 x_qulv_rec OUT NOCOPY qulv_rec_type) IS
1058
1059 l_return_status VARCHAR2(1);
1060
1061 l_prog_name VARCHAR2(61);
1062
1063 BEGIN
1064
1065 l_prog_name := G_PKG_NAME||'.update_row (REC)';
1066
1067 IF p_init_msg_list = G_TRUE THEN
1068 FND_MSG_PUB.initialize;
1069 END IF;
1070
1071 update_row (x_return_status => l_return_status,
1072 p_qulv_rec => p_qulv_rec,
1073 x_qulv_rec => x_qulv_rec);
1074
1075 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1076 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1077 ELSIF l_return_status = G_RET_STS_ERROR THEN
1078 RAISE OKL_API.G_EXCEPTION_ERROR;
1079 END IF;
1080
1081 x_return_status := l_return_status;
1082
1083 EXCEPTION
1084
1085 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1086
1087 x_return_status := G_RET_STS_ERROR;
1088
1089 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1090
1091 x_return_status := G_RET_STS_UNEXP_ERROR;
1092
1093 WHEN OTHERS THEN
1094
1095 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1096 p_msg_name => G_DB_ERROR,
1097 p_token1 => G_PROG_NAME_TOKEN,
1098 p_token1_value => l_prog_name,
1099 p_token2 => G_SQLCODE_TOKEN,
1100 p_token2_value => sqlcode,
1101 p_token3 => G_SQLERRM_TOKEN,
1102 p_token3_value => sqlerrm);
1103
1104 x_return_status := G_RET_STS_UNEXP_ERROR;
1105
1106 END update_row;
1107
1108
1109 -----------------------------
1110 -- PROCEDURE update_row (TBL)
1111 -----------------------------
1112 PROCEDURE update_row(
1113 p_api_version IN NUMBER,
1114 p_init_msg_list IN VARCHAR2,
1115 x_return_status OUT NOCOPY VARCHAR2,
1116 x_msg_count OUT NOCOPY NUMBER,
1117 x_msg_data OUT NOCOPY VARCHAR2,
1118 p_qulv_tbl IN qulv_tbl_type,
1119 x_qulv_tbl OUT NOCOPY qulv_tbl_type) IS
1120
1121 l_return_status VARCHAR2(1);
1122 i BINARY_INTEGER;
1123 l_prog_name VARCHAR2(61);
1124
1125 BEGIN
1126
1127 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
1128
1129 IF p_init_msg_list = G_TRUE THEN
1130 FND_MSG_PUB.initialize;
1131 END IF;
1132
1133 x_qulv_tbl := p_qulv_tbl;
1134
1135 IF (p_qulv_tbl.COUNT > 0) THEN
1136
1137 i := p_qulv_tbl.FIRST;
1138
1139 LOOP
1140
1141 IF p_qulv_tbl.EXISTS(i) THEN
1142 update_row (x_return_status => l_return_status,
1143 p_qulv_rec => p_qulv_tbl(i),
1144 x_qulv_rec => x_qulv_tbl(i));
1145
1146 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1147 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1148 ELSIF l_return_status = G_RET_STS_ERROR THEN
1149 RAISE OKL_API.G_EXCEPTION_ERROR;
1150 END IF;
1151
1152 EXIT WHEN (i = p_qulv_tbl.LAST);
1153 i := p_qulv_tbl.NEXT(i);
1154
1155 END IF;
1156
1157 END LOOP;
1158
1159 ELSE
1160
1161 l_return_status := G_RET_STS_SUCCESS;
1162
1163 END IF;
1164
1165 x_return_status := l_return_status;
1166
1167 EXCEPTION
1168
1169 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1170
1171 x_return_status := G_RET_STS_ERROR;
1172
1173 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1174
1175 x_return_status := G_RET_STS_UNEXP_ERROR;
1176
1177 WHEN OTHERS THEN
1178
1179 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1180 p_msg_name => G_DB_ERROR,
1181 p_token1 => G_PROG_NAME_TOKEN,
1182 p_token1_value => l_prog_name,
1183 p_token2 => G_SQLCODE_TOKEN,
1184 p_token2_value => sqlcode,
1185 p_token3 => G_SQLERRM_TOKEN,
1186 p_token3_value => sqlerrm);
1187
1188 x_return_status := G_RET_STS_UNEXP_ERROR;
1189
1190 END update_row;
1191
1192
1193 -----------------
1194 -- delete_row (V)
1195 -----------------
1196 PROCEDURE delete_row(
1197 x_return_status OUT NOCOPY VARCHAR2,
1198 p_id IN NUMBER) IS
1199
1200 l_prog_name VARCHAR2(61);
1201
1202 BEGIN
1203
1204 l_prog_name := G_PKG_NAME||'.delete_row (V)';
1205
1206 DELETE FROM OKL_QUOTE_SUBPOOL_USAGE WHERE id = p_id;
1207
1208 x_return_status := G_RET_STS_SUCCESS;
1209
1210 EXCEPTION
1211
1212 WHEN OTHERS THEN
1213
1214 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1215 p_msg_name => G_DB_ERROR,
1216 p_token1 => G_PROG_NAME_TOKEN,
1217 p_token1_value => l_prog_name,
1218 p_token2 => G_SQLCODE_TOKEN,
1219 p_token2_value => sqlcode,
1220 p_token3 => G_SQLERRM_TOKEN,
1221 p_token3_value => sqlerrm);
1222
1223 x_return_status := G_RET_STS_UNEXP_ERROR;
1224
1225 END delete_row;
1226
1227
1228 -----------------------------
1229 -- PROCEDURE delete_row (REC)
1230 -----------------------------
1231 PROCEDURE delete_row(
1232 p_api_version IN NUMBER,
1233 p_init_msg_list IN VARCHAR2,
1234 x_return_status OUT NOCOPY VARCHAR2,
1235 x_msg_count OUT NOCOPY NUMBER,
1236 x_msg_data OUT NOCOPY VARCHAR2,
1237 p_qulv_rec IN qulv_rec_type) IS
1238
1239 l_return_status VARCHAR2(1);
1240
1241 l_prog_name VARCHAR2(61);
1242
1243 BEGIN
1244
1245 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
1246
1247 IF p_init_msg_list = G_TRUE THEN
1248 FND_MSG_PUB.initialize;
1249 END IF;
1250
1251 delete_row (x_return_status => l_return_status,
1252 p_id => p_qulv_rec.id);
1253
1254 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1255 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1256 ELSIF l_return_status = G_RET_STS_ERROR THEN
1257 RAISE OKL_API.G_EXCEPTION_ERROR;
1258 END IF;
1259
1260 x_return_status := l_return_status;
1261
1262 EXCEPTION
1263
1264 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1265
1266 x_return_status := G_RET_STS_ERROR;
1267
1268 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1269
1270 x_return_status := G_RET_STS_UNEXP_ERROR;
1271
1272 WHEN OTHERS THEN
1273
1274 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1275 p_msg_name => G_DB_ERROR,
1276 p_token1 => G_PROG_NAME_TOKEN,
1277 p_token1_value => l_prog_name,
1278 p_token2 => G_SQLCODE_TOKEN,
1279 p_token2_value => sqlcode,
1280 p_token3 => G_SQLERRM_TOKEN,
1281 p_token3_value => sqlerrm);
1282
1283 x_return_status := G_RET_STS_UNEXP_ERROR;
1284
1285 END delete_row;
1286
1287
1288 -------------------
1289 -- delete_row (TBL)
1290 -------------------
1291 PROCEDURE delete_row(
1292 p_api_version IN NUMBER,
1293 p_init_msg_list IN VARCHAR2,
1294 x_return_status OUT NOCOPY VARCHAR2,
1295 x_msg_count OUT NOCOPY NUMBER,
1296 x_msg_data OUT NOCOPY VARCHAR2,
1297 p_qulv_tbl IN qulv_tbl_type) IS
1298
1299 l_return_status VARCHAR2(1);
1300 i BINARY_INTEGER;
1301
1302 l_prog_name VARCHAR2(61);
1303
1304 BEGIN
1305
1306 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
1307
1308 IF p_init_msg_list = G_TRUE THEN
1309 FND_MSG_PUB.initialize;
1310 END IF;
1311
1312 IF (p_qulv_tbl.COUNT > 0) THEN
1313
1314 i := p_qulv_tbl.FIRST;
1315
1316 LOOP
1317
1318 IF p_qulv_tbl.EXISTS(i) THEN
1319
1320 delete_row (x_return_status => l_return_status,
1321 p_id => p_qulv_tbl(i).id);
1322
1323 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1324 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1325 ELSIF l_return_status = G_RET_STS_ERROR THEN
1326 RAISE OKL_API.G_EXCEPTION_ERROR;
1327 END IF;
1328
1329 EXIT WHEN (i = p_qulv_tbl.LAST);
1330 i := p_qulv_tbl.NEXT(i);
1331
1332 END IF;
1333
1334 END LOOP;
1335
1336 ELSE
1337
1338 l_return_status := G_RET_STS_SUCCESS;
1339
1340 END IF;
1341
1342 x_return_status := l_return_status;
1343
1344 EXCEPTION
1345
1346 WHEN OTHERS THEN
1347
1348 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1349 p_msg_name => G_DB_ERROR,
1350 p_token1 => G_PROG_NAME_TOKEN,
1351 p_token1_value => l_prog_name,
1352 p_token2 => G_SQLCODE_TOKEN,
1353 p_token2_value => sqlcode,
1354 p_token3 => G_SQLERRM_TOKEN,
1355 p_token3_value => sqlerrm);
1356
1357 x_return_status := G_RET_STS_UNEXP_ERROR;
1358
1359 END delete_row;
1360
1361
1362 END OKL_QUL_PVT;