[Home] [Help]
PACKAGE BODY: APPS.OKL_QQL_PVT
Source
1 PACKAGE BODY OKL_QQL_PVT AS
2 /* $Header: OKLSQQLB.pls 120.1 2005/12/22 12:26:23 smadhava noship $ */
3
4 -------------------------
5 -- PROCEDURE add_language
6 -------------------------
7 PROCEDURE add_language IS
8
9 BEGIN
10
11 DELETE FROM OKL_QUICK_QUOTE_LINES_TL T
12 WHERE NOT EXISTS (SELECT NULL FROM OKL_QUICK_QUOTE_LINES_B B WHERE B.ID =T.ID);
13
14 UPDATE OKL_QUICK_QUOTE_LINES_TL T
15 SET (SHORT_DESCRIPTION,
16 DESCRIPTION,
17 COMMENTS) =
18 (SELECT
19 B.SHORT_DESCRIPTION,
20 B.DESCRIPTION,
21 B.COMMENTS
22 FROM
23 OKL_QUICK_QUOTE_LINES_TL B
24 WHERE
25 B.ID = T.ID
26 AND B.LANGUAGE = T.SOURCE_LANG)
27 WHERE (T.ID, T.LANGUAGE) IN (SELECT
28 SUBT.ID,
29 SUBT.LANGUAGE
30 FROM
31 OKL_QUICK_QUOTE_LINES_TL SUBB,
32 OKL_QUICK_QUOTE_LINES_TL SUBT
33 WHERE
34 SUBB.ID = SUBT.ID
35 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
36 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
37 OR (SUBB.DESCRIPTION <> SUBT.DESCRIPTION)
38 OR (SUBB.COMMENTS <> SUBT.COMMENTS)
39 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
40 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
41 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
42 )
43 );
44
45 INSERT INTO OKL_QUICK_QUOTE_LINES_TL (
46 ID,
47 LANGUAGE,
48 SOURCE_LANG,
49 SFWT_FLAG,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN,
55 SHORT_DESCRIPTION,
56 DESCRIPTION,
57 COMMENTS)
58 SELECT
59 B.ID,
60 L.LANGUAGE_CODE,
61 B.SOURCE_LANG,
62 B.SFWT_FLAG,
63 B.CREATED_BY,
64 B.CREATION_DATE,
65 B.LAST_UPDATED_BY,
66 B.LAST_UPDATE_DATE,
67 B.LAST_UPDATE_LOGIN,
68 B.SHORT_DESCRIPTION,
69 B.DESCRIPTION,
70 B.COMMENTS
71 FROM OKL_QUICK_QUOTE_LINES_TL B, FND_LANGUAGES L
72 WHERE L.INSTALLED_FLAG IN ('I', 'B')
73 AND B.LANGUAGE = USERENV('LANG')
74 AND NOT EXISTS (
75 SELECT NULL
76 FROM OKL_QUICK_QUOTE_LINES_TL T
77 WHERE T.ID = B.ID
78 AND T.LANGUAGE = L.LANGUAGE_CODE
79 );
80
81 END add_language;
82
83
84 -----------------------------
85 -- FUNCTION null_out_defaults
86 -----------------------------
87 FUNCTION null_out_defaults (p_qqlv_rec IN qqlv_rec_type) RETURN qqlv_rec_type IS
88
89 l_qqlv_rec qqlv_rec_type;
90
91 BEGIN
92
93 l_qqlv_rec := p_qqlv_rec;
94
95 -- Not applicable to ID and OBJECT_VERSION_NUMBER
96
97 IF l_qqlv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
98 l_qqlv_rec.attribute_category := NULL;
99 END IF;
100 IF l_qqlv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
101 l_qqlv_rec.attribute1 := NULL;
102 END IF;
103 IF l_qqlv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
104 l_qqlv_rec.attribute2 := NULL;
105 END IF;
106 IF l_qqlv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
107 l_qqlv_rec.attribute3 := NULL;
108 END IF;
109 IF l_qqlv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
110 l_qqlv_rec.attribute4 := NULL;
111 END IF;
112 IF l_qqlv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
113 l_qqlv_rec.attribute5 := NULL;
114 END IF;
115 IF l_qqlv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
116 l_qqlv_rec.attribute6 := NULL;
117 END IF;
118 IF l_qqlv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
119 l_qqlv_rec.attribute7 := NULL;
120 END IF;
121 IF l_qqlv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
122 l_qqlv_rec.attribute8 := NULL;
123 END IF;
124 IF l_qqlv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
125 l_qqlv_rec.attribute9 := NULL;
126 END IF;
127 IF l_qqlv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
128 l_qqlv_rec.attribute10 := NULL;
129 END IF;
130 IF l_qqlv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
131 l_qqlv_rec.attribute11 := NULL;
132 END IF;
133 IF l_qqlv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
134 l_qqlv_rec.attribute12 := NULL;
135 END IF;
136 IF l_qqlv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
137 l_qqlv_rec.attribute13 := NULL;
138 END IF;
139 IF l_qqlv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
140 l_qqlv_rec.attribute14 := NULL;
141 END IF;
142 IF l_qqlv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
143 l_qqlv_rec.attribute15 := NULL;
144 END IF;
145 IF l_qqlv_rec.quick_quote_id = FND_API.G_MISS_NUM THEN
146 l_qqlv_rec.quick_quote_id := NULL;
147 END IF;
148 IF l_qqlv_rec.type = FND_API.G_MISS_CHAR THEN
149 l_qqlv_rec.type := NULL;
150 END IF;
151 IF l_qqlv_rec.basis = FND_API.G_MISS_CHAR THEN
152 l_qqlv_rec.basis := NULL;
153 END IF;
154 IF l_qqlv_rec.value = FND_API.G_MISS_NUM THEN
155 l_qqlv_rec.value := NULL;
156 END IF;
157 IF l_qqlv_rec.end_of_term_value_default = FND_API.G_MISS_NUM THEN
158 l_qqlv_rec.end_of_term_value_default := NULL;
159 END IF;
160 IF l_qqlv_rec.end_of_term_value = FND_API.G_MISS_NUM THEN
161 l_qqlv_rec.end_of_term_value := NULL;
162 END IF;
163 IF l_qqlv_rec.percentage_of_total_cost = FND_API.G_MISS_NUM THEN
164 l_qqlv_rec.percentage_of_total_cost := NULL;
165 END IF;
166 IF l_qqlv_rec.item_category_id = FND_API.G_MISS_NUM THEN
167 l_qqlv_rec.item_category_id := NULL;
168 END IF;
169 IF l_qqlv_rec.item_category_set_id = FND_API.G_MISS_NUM THEN
170 l_qqlv_rec.item_category_set_id := NULL;
171 END IF;
172 IF l_qqlv_rec.lease_rate_factor = FND_API.G_MISS_NUM THEN
173 l_qqlv_rec.lease_rate_factor := NULL;
174 END IF;
175 IF l_qqlv_rec.short_description = FND_API.G_MISS_CHAR THEN
176 l_qqlv_rec.short_description := NULL;
177 END IF;
178 IF l_qqlv_rec.description = FND_API.G_MISS_CHAR THEN
179 l_qqlv_rec.description := NULL;
180 END IF;
181 IF l_qqlv_rec.comments = FND_API.G_MISS_CHAR THEN
182 l_qqlv_rec.comments := NULL;
183 END IF;
184
185 RETURN l_qqlv_rec;
186
187 END null_out_defaults;
188
189
190 -------------------
191 -- FUNCTION get_rec
192 -------------------
193 FUNCTION get_rec (p_id IN NUMBER
194 ,x_return_status OUT NOCOPY VARCHAR2) RETURN qqlv_rec_type IS
195
196 l_qqlv_rec qqlv_rec_type;
197 l_prog_name VARCHAR2(61);
198
199 BEGIN
200
201 l_prog_name := G_PKG_NAME||'.get_rec';
202
203 SELECT
204 id
205 ,object_version_number
206 ,attribute_category
207 ,attribute1
208 ,attribute2
209 ,attribute3
210 ,attribute4
211 ,attribute5
212 ,attribute6
213 ,attribute7
214 ,attribute8
215 ,attribute9
216 ,attribute10
217 ,attribute11
218 ,attribute12
219 ,attribute13
220 ,attribute14
221 ,attribute15
222 ,quick_quote_id
223 ,type
224 ,basis
225 ,value
226 ,end_of_term_value_default
227 ,end_of_term_value
228 ,percentage_of_total_cost
229 ,item_category_id
230 ,item_category_set_id
231 ,lease_rate_factor
232 ,short_description
233 ,description
234 ,comments
235 INTO
236 l_qqlv_rec.id
237 ,l_qqlv_rec.object_version_number
238 ,l_qqlv_rec.attribute_category
239 ,l_qqlv_rec.attribute1
240 ,l_qqlv_rec.attribute2
241 ,l_qqlv_rec.attribute3
242 ,l_qqlv_rec.attribute4
243 ,l_qqlv_rec.attribute5
244 ,l_qqlv_rec.attribute6
245 ,l_qqlv_rec.attribute7
246 ,l_qqlv_rec.attribute8
247 ,l_qqlv_rec.attribute9
248 ,l_qqlv_rec.attribute10
249 ,l_qqlv_rec.attribute11
250 ,l_qqlv_rec.attribute12
251 ,l_qqlv_rec.attribute13
252 ,l_qqlv_rec.attribute14
253 ,l_qqlv_rec.attribute15
254 ,l_qqlv_rec.quick_quote_id
255 ,l_qqlv_rec.type
256 ,l_qqlv_rec.basis
257 ,l_qqlv_rec.value
258 ,l_qqlv_rec.end_of_term_value_default
259 ,l_qqlv_rec.end_of_term_value
260 ,l_qqlv_rec.percentage_of_total_cost
261 ,l_qqlv_rec.item_category_id
262 ,l_qqlv_rec.item_category_set_id
263 ,l_qqlv_rec.lease_rate_factor
264 ,l_qqlv_rec.short_description
265 ,l_qqlv_rec.description
266 ,l_qqlv_rec.comments
267 FROM OKL_QUICK_QUOTE_LINES_V
268 WHERE id = p_id;
269
270 x_return_status := G_RET_STS_SUCCESS;
271 RETURN l_qqlv_rec;
272
273 EXCEPTION
274
275 WHEN OTHERS THEN
276
277 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
278 p_msg_name => G_DB_ERROR,
279 p_token1 => G_PROG_NAME_TOKEN,
280 p_token1_value => l_prog_name,
281 p_token2 => G_SQLCODE_TOKEN,
282 p_token2_value => sqlcode,
283 p_token3 => G_SQLERRM_TOKEN,
284 p_token3_value => sqlerrm);
285
286 x_return_status := G_RET_STS_UNEXP_ERROR;
287
288 END get_rec;
289
290
291 ------------------------
292 -- PROCEDURE validate_id
293 ------------------------
294 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
295 BEGIN
296 IF p_id IS NULL THEN
297 OKL_API.set_message(p_app_name => G_APP_NAME,
298 p_msg_name => G_COL_ERROR,
299 p_token1 => G_COL_NAME_TOKEN,
300 p_token1_value => 'id',
301 p_token2 => G_PKG_NAME_TOKEN,
302 p_token2_value => G_PKG_NAME);
303 RAISE OKL_API.G_EXCEPTION_ERROR;
304 END IF;
305 x_return_status := G_RET_STS_SUCCESS;
306 END validate_id;
307
308
309 -------------------------------------------
310 -- PROCEDURE validate_object_version_number
311 -------------------------------------------
312 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
313 BEGIN
314 IF p_object_version_number IS NULL THEN
315 OKL_API.set_message(p_app_name => G_APP_NAME,
316 p_msg_name => G_COL_ERROR,
317 p_token1 => G_COL_NAME_TOKEN,
318 p_token1_value => 'object_version_number',
319 p_token2 => G_PKG_NAME_TOKEN,
320 p_token2_value => G_PKG_NAME);
321 RAISE OKL_API.G_EXCEPTION_ERROR;
322 END IF;
323 x_return_status := G_RET_STS_SUCCESS;
324 END validate_object_version_number;
325
326
327 ------------------------
328 -- PROCEDURE validate_quick_quote_id
329 ------------------------
330 PROCEDURE validate_quick_quote_id (x_return_status OUT NOCOPY VARCHAR2, p_quick_quote_id IN NUMBER) IS
331 BEGIN
332 IF p_quick_quote_id IS NULL THEN
333 OKL_API.set_message(p_app_name => G_APP_NAME,
334 p_msg_name => G_COL_ERROR,
335 p_token1 => G_COL_NAME_TOKEN,
336 p_token1_value => 'quick_quote_id',
337 p_token2 => G_PKG_NAME_TOKEN,
338 p_token2_value => G_PKG_NAME);
339 RAISE OKL_API.G_EXCEPTION_ERROR;
340 END IF;
341 x_return_status := G_RET_STS_SUCCESS;
342 END validate_quick_quote_id;
343
344
345 --------------------------------------
346 -- PROCEDURE validate_type
347 --------------------------------------
348 PROCEDURE validate_type (x_return_status OUT NOCOPY VARCHAR2, p_type IN VARCHAR2) IS
349 BEGIN
350 IF p_type IS NULL THEN
351 OKL_API.set_message(p_app_name => G_APP_NAME,
352 p_msg_name => G_COL_ERROR,
353 p_token1 => G_COL_NAME_TOKEN,
354 p_token1_value => 'type',
355 p_token2 => G_PKG_NAME_TOKEN,
356 p_token2_value => G_PKG_NAME);
357 RAISE OKL_API.G_EXCEPTION_ERROR;
358 END IF;
359 x_return_status := G_RET_STS_SUCCESS;
360 END validate_type;
361
362
363 -------------------------------
364 -- FUNCTION validate_attributes
365 -------------------------------
366 FUNCTION validate_attributes (p_qqlv_rec IN qqlv_rec_type) RETURN VARCHAR2 IS
367
368 l_return_status VARCHAR2(1);
369
370 BEGIN
371
372 validate_id (l_return_status, p_qqlv_rec.id);
373 validate_object_version_number (l_return_status, p_qqlv_rec.object_version_number);
374 validate_quick_quote_id (l_return_status, p_qqlv_rec.quick_quote_id);
375 validate_type (l_return_status, p_qqlv_rec.type);
376
377
378 RETURN l_return_status;
379
380 END validate_attributes;
381
382 ----------------------------
383 -- PROCEDURE validate_record
384 ----------------------------
385 FUNCTION validate_record (p_qqlv_rec IN qqlv_rec_type) RETURN VARCHAR2 IS
386 l_return_status VARCHAR2(1);
387 BEGIN
388 RETURN G_RET_STS_SUCCESS;
389 END validate_record;
390
391
392 -----------------------------
393 -- PROECDURE migrate (V -> B)
394 -----------------------------
395 PROCEDURE migrate (p_from IN qqlv_rec_type, p_to IN OUT NOCOPY qql_rec_type) IS
396
397 BEGIN
398
399 p_to.id := p_from.id;
400 p_to.object_version_number := p_from.object_version_number;
401 p_to.attribute_category := p_from.attribute_category;
402 p_to.attribute1 := p_from.attribute1;
403 p_to.attribute2 := p_from.attribute2;
404 p_to.attribute3 := p_from.attribute3;
405 p_to.attribute4 := p_from.attribute4;
406 p_to.attribute5 := p_from.attribute5;
407 p_to.attribute6 := p_from.attribute6;
408 p_to.attribute7 := p_from.attribute7;
409 p_to.attribute8 := p_from.attribute8;
410 p_to.attribute9 := p_from.attribute9;
411 p_to.attribute10 := p_from.attribute10;
412 p_to.attribute11 := p_from.attribute11;
413 p_to.attribute12 := p_from.attribute12;
414 p_to.attribute13 := p_from.attribute13;
415 p_to.attribute14 := p_from.attribute14;
416 p_to.attribute15 := p_from.attribute15;
417 p_to.quick_quote_id := p_from.quick_quote_id;
418 p_to.type := p_from.type;
419 p_to.basis := p_from.basis;
420 p_to.value := p_from.value;
421 p_to.end_of_term_value_default := p_from.end_of_term_value_default;
422 p_to.end_of_term_value := p_from.end_of_term_value;
423 p_to.percentage_of_total_cost := p_from.percentage_of_total_cost;
424 p_to.item_category_id := p_from.item_category_id;
425 p_to.item_category_set_id := p_from.item_category_set_id;
426 p_to.lease_rate_factor := p_from.lease_rate_factor;
427
428 END migrate;
429
430
431 -----------------------------
432 -- PROCEDURE migrate (V -> TL)
433 -----------------------------
434 PROCEDURE migrate (p_from IN qqlv_rec_type, p_to IN OUT NOCOPY qqltl_rec_type) IS
435 BEGIN
436 p_to.id := p_from.id;
437 p_to.short_description := p_from.short_description;
438 p_to.description := p_from.description;
439 p_to.comments := p_from.comments;
440 END migrate;
441
442
443 ---------------------------
444 -- PROCEDURE insert_row (B)
445 ---------------------------
446 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_qql_rec IN qql_rec_type) IS
447
448 l_prog_name VARCHAR2(61);
449
450 BEGIN
451
452 l_prog_name := G_PKG_NAME||'.insert_row (B)';
453
454 INSERT INTO okl_quick_quote_lines_b (
455 id
456 ,object_version_number
457 ,attribute_category
458 ,attribute1
459 ,attribute2
460 ,attribute3
461 ,attribute4
462 ,attribute5
463 ,attribute6
464 ,attribute7
465 ,attribute8
466 ,attribute9
467 ,attribute10
468 ,attribute11
469 ,attribute12
470 ,attribute13
471 ,attribute14
472 ,attribute15
473 ,created_by
474 ,creation_date
475 ,last_updated_by
476 ,last_update_date
477 ,last_update_login
478 ,quick_quote_id
479 ,type
480 ,basis
481 ,value
482 ,end_of_term_value_default
483 ,end_of_term_value
484 ,percentage_of_total_cost
485 ,item_category_id
486 ,item_category_set_id
487 ,lease_rate_factor
488 )
489 VALUES
490 (
491 p_qql_rec.id
492 ,p_qql_rec.object_version_number
493 ,p_qql_rec.attribute_category
494 ,p_qql_rec.attribute1
495 ,p_qql_rec.attribute2
496 ,p_qql_rec.attribute3
497 ,p_qql_rec.attribute4
498 ,p_qql_rec.attribute5
499 ,p_qql_rec.attribute6
500 ,p_qql_rec.attribute7
501 ,p_qql_rec.attribute8
502 ,p_qql_rec.attribute9
503 ,p_qql_rec.attribute10
504 ,p_qql_rec.attribute11
505 ,p_qql_rec.attribute12
506 ,p_qql_rec.attribute13
507 ,p_qql_rec.attribute14
508 ,p_qql_rec.attribute15
509 ,G_USER_ID
510 ,SYSDATE
511 ,G_USER_ID
512 ,SYSDATE
513 ,G_LOGIN_ID
514 ,p_qql_rec.quick_quote_id
515 ,p_qql_rec.type
516 ,p_qql_rec.basis
517 ,p_qql_rec.value
518 ,p_qql_rec.end_of_term_value_default
519 ,p_qql_rec.end_of_term_value
520 ,p_qql_rec.percentage_of_total_cost
521 ,p_qql_rec.item_category_id
522 ,p_qql_rec.item_category_set_id
523 ,p_qql_rec.lease_rate_factor
524 );
525
526 x_return_status := G_RET_STS_SUCCESS;
527
528 EXCEPTION
529
530 WHEN OTHERS THEN
531
532 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
533 p_msg_name => G_DB_ERROR,
534 p_token1 => G_PROG_NAME_TOKEN,
535 p_token1_value => l_prog_name,
536 p_token2 => G_SQLCODE_TOKEN,
537 p_token2_value => sqlcode,
538 p_token3 => G_SQLERRM_TOKEN,
539 p_token3_value => sqlerrm);
540
541 x_return_status := G_RET_STS_UNEXP_ERROR;
542
543 END insert_row;
544
545
546 ----------------------------
547 -- PROCEDURE insert_row (TL)
548 ----------------------------
549 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_qqltl_rec IN qqltl_rec_type) IS
550
551 CURSOR get_languages IS
552 SELECT language_code
553 FROM FND_LANGUAGES
554 WHERE INSTALLED_FLAG IN ('I', 'B');
555
556 l_sfwt_flag VARCHAR2(1);
557
558 l_prog_name VARCHAR2(61);
559
560 BEGIN
561
562 l_prog_name := G_PKG_NAME||'.insert_row (TL)';
563
564 FOR l_lang_rec IN get_languages LOOP
565
566 IF l_lang_rec.language_code = USERENV('LANG') THEN
567 l_sfwt_flag := 'N';
568 ELSE
569 l_sfwt_flag := 'Y';
570 END IF;
571
572 INSERT INTO OKL_QUICK_QUOTE_LINES_TL (
573 id
574 ,language
575 ,source_lang
576 ,sfwt_flag
577 ,created_by
578 ,creation_date
579 ,last_updated_by
580 ,last_update_date
581 ,last_update_login
582 ,short_description
583 ,description
584 ,comments)
585 VALUES (
586 p_qqltl_rec.id
587 ,l_lang_rec.language_code
588 ,USERENV('LANG')
589 ,l_sfwt_flag
590 ,G_USER_ID
591 ,SYSDATE
592 ,G_USER_ID
593 ,SYSDATE
594 ,G_LOGIN_ID
595 ,p_qqltl_rec.short_description
596 ,p_qqltl_rec.description
597 ,p_qqltl_rec.comments);
598
599 END LOOP;
600
601 x_return_status := G_RET_STS_SUCCESS;
602
603 EXCEPTION
604
605 WHEN OTHERS THEN
606
607 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
608 p_msg_name => G_DB_ERROR,
609 p_token1 => G_PROG_NAME_TOKEN,
610 p_token1_value => l_prog_name,
611 p_token2 => G_SQLCODE_TOKEN,
612 p_token2_value => sqlcode,
613 p_token3 => G_SQLERRM_TOKEN,
614 p_token3_value => sqlerrm);
615
616 x_return_status := G_RET_STS_UNEXP_ERROR;
617
618 END insert_row;
619
620
621 ---------------------------
622 -- PROCEDURE insert_row (V)
623 ---------------------------
624 PROCEDURE insert_row (
625 x_return_status OUT NOCOPY VARCHAR2,
626 p_qqlv_rec IN qqlv_rec_type,
627 x_qqlv_rec OUT NOCOPY qqlv_rec_type) IS
628
629 l_return_status VARCHAR2(1);
630
631 l_qqlv_rec qqlv_rec_type;
632 l_qql_rec qql_rec_type;
633 l_qqltl_rec qqltl_rec_type;
634
635 l_prog_name VARCHAR2(61);
636
637 BEGIN
638
639 l_prog_name := G_PKG_NAME||'.insert_row (V)';
640
641 l_qqlv_rec := null_out_defaults (p_qqlv_rec);
642
643 SELECT okl_qql_seq.nextval INTO l_qqlv_rec.ID FROM DUAL;
644
645 l_qqlv_rec.OBJECT_VERSION_NUMBER := 1;
646
647 l_return_status := validate_attributes(l_qqlv_rec);
648
649 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
650 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
651 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
652 RAISE OKL_API.G_EXCEPTION_ERROR;
653 END IF;
654
655 l_return_status := validate_record(l_qqlv_rec);
656
657 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
658 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
659 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
660 RAISE OKL_API.G_EXCEPTION_ERROR;
661 END IF;
662
663 migrate (l_qqlv_rec, l_qql_rec);
664 migrate (l_qqlv_rec, l_qqltl_rec);
665
666 insert_row (x_return_status => l_return_status, p_qql_rec => l_qql_rec);
667
668 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
669 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
670 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
671 RAISE OKL_API.G_EXCEPTION_ERROR;
672 END IF;
673
674 insert_row (x_return_status => l_return_status, p_qqltl_rec => l_qqltl_rec);
675
676 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
677 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
678 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
679 RAISE OKL_API.G_EXCEPTION_ERROR;
680 END IF;
681
682 x_qqlv_rec := l_qqlv_rec;
683 x_return_status := l_return_status;
684
685 EXCEPTION
686
687 WHEN OKL_API.G_EXCEPTION_ERROR THEN
688
689 x_return_status := G_RET_STS_ERROR;
690
691 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
692
693 x_return_status := G_RET_STS_UNEXP_ERROR;
694
695 WHEN OTHERS THEN
696
697 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
698 p_msg_name => G_DB_ERROR,
699 p_token1 => G_PROG_NAME_TOKEN,
700 p_token1_value => l_prog_name,
701 p_token2 => G_SQLCODE_TOKEN,
702 p_token2_value => sqlcode,
703 p_token3 => G_SQLERRM_TOKEN,
704 p_token3_value => sqlerrm);
705
706 x_return_status := G_RET_STS_UNEXP_ERROR;
707
708 END insert_row;
709
710
711 -----------------------------
712 -- PROCEDURE insert_row (REC)
713 -----------------------------
714 PROCEDURE insert_row(
715 p_api_version IN NUMBER,
716 p_init_msg_list IN VARCHAR2,
717 x_return_status OUT NOCOPY VARCHAR2,
718 x_msg_count OUT NOCOPY NUMBER,
719 x_msg_data OUT NOCOPY VARCHAR2,
720 p_qqlv_rec IN qqlv_rec_type,
721 x_qqlv_rec OUT NOCOPY qqlv_rec_type) IS
722
723 l_return_status VARCHAR2(1);
724
725 l_prog_name VARCHAR2(61);
726
727 BEGIN
728
729 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
730
731 IF p_init_msg_list = G_TRUE THEN
732 FND_MSG_PUB.initialize;
733 END IF;
734
735 insert_row (x_return_status => l_return_status,
736 p_qqlv_rec => p_qqlv_rec,
737 x_qqlv_rec => x_qqlv_rec);
738
739 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
740 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
741 ELSIF l_return_status = G_RET_STS_ERROR THEN
742 RAISE OKL_API.G_EXCEPTION_ERROR;
743 END IF;
744
745 x_return_status := l_return_status;
746
747 EXCEPTION
748
749 WHEN OKL_API.G_EXCEPTION_ERROR THEN
750
751 x_return_status := G_RET_STS_ERROR;
752
753 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
754
755 x_return_status := G_RET_STS_UNEXP_ERROR;
756
757 WHEN OTHERS THEN
758
759 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
760 p_msg_name => G_DB_ERROR,
761 p_token1 => G_PROG_NAME_TOKEN,
762 p_token1_value => l_prog_name,
763 p_token2 => G_SQLCODE_TOKEN,
764 p_token2_value => sqlcode,
765 p_token3 => G_SQLERRM_TOKEN,
766 p_token3_value => sqlerrm);
767
768 x_return_status := G_RET_STS_UNEXP_ERROR;
769
770 END insert_row;
771
772
773 -----------------------------
774 -- PROCEDURE insert_row (TBL)
775 -----------------------------
776 PROCEDURE insert_row(
777 p_api_version IN NUMBER,
778 p_init_msg_list IN VARCHAR2,
779 x_return_status OUT NOCOPY VARCHAR2,
780 x_msg_count OUT NOCOPY NUMBER,
781 x_msg_data OUT NOCOPY VARCHAR2,
782 p_qqlv_tbl IN qqlv_tbl_type,
783 x_qqlv_tbl OUT NOCOPY qqlv_tbl_type) IS
784
785 l_return_status VARCHAR2(1);
786 i BINARY_INTEGER;
787
788 l_prog_name VARCHAR2(61);
789
790 BEGIN
791
792 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
793
794 IF p_init_msg_list = G_TRUE THEN
795 FND_MSG_PUB.initialize;
796 END IF;
797
798 IF (p_qqlv_tbl.COUNT > 0) THEN
799 i := p_qqlv_tbl.FIRST;
800 LOOP
801 IF p_qqlv_tbl.EXISTS(i) THEN
802
803 insert_row (x_return_status => l_return_status,
804 p_qqlv_rec => p_qqlv_tbl(i),
805 x_qqlv_rec => x_qqlv_tbl(i));
806
807 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
808 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
809 ELSIF l_return_status = G_RET_STS_ERROR THEN
810 RAISE OKL_API.G_EXCEPTION_ERROR;
811 END IF;
812
813 EXIT WHEN (i = p_qqlv_tbl.LAST);
814 i := p_qqlv_tbl.NEXT(i);
815
816 END IF;
817
818 END LOOP;
819
820 ELSE
821
822 l_return_status := G_RET_STS_SUCCESS;
823
824 END IF;
825
826 x_return_status := l_return_status;
827
828 EXCEPTION
829
830 WHEN OKL_API.G_EXCEPTION_ERROR THEN
831
832 x_return_status := G_RET_STS_ERROR;
833
834 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
835
836 x_return_status := G_RET_STS_UNEXP_ERROR;
837
838 WHEN OTHERS THEN
839
840 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
841 p_msg_name => G_DB_ERROR,
842 p_token1 => G_PROG_NAME_TOKEN,
843 p_token1_value => l_prog_name,
844 p_token2 => G_SQLCODE_TOKEN,
845 p_token2_value => sqlcode,
846 p_token3 => G_SQLERRM_TOKEN,
847 p_token3_value => sqlerrm);
848
849 x_return_status := G_RET_STS_UNEXP_ERROR;
850
851 END insert_row;
852
853
854 ---------------------
855 -- PROCEDURE lock_row
856 ---------------------
857 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_qql_rec IN qql_rec_type) IS
858
859 E_Resource_Busy EXCEPTION;
860
861 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
862
863 CURSOR lock_csr IS
864 SELECT OBJECT_VERSION_NUMBER
865 FROM OKL_QUICK_QUOTE_LINES_B
866 WHERE ID = p_qql_rec.id
867 AND OBJECT_VERSION_NUMBER = p_qql_rec.object_version_number
868 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
869
870 CURSOR lchk_csr IS
871 SELECT OBJECT_VERSION_NUMBER
872 FROM OKL_QUICK_QUOTE_LINES_B
873 WHERE ID = p_qql_rec.id;
874
875 l_object_version_number NUMBER;
876 lc_object_version_number NUMBER;
877
878 l_prog_name VARCHAR2(61);
879
880 BEGIN
881
882 l_prog_name := G_PKG_NAME||'.lock_row';
883
884 BEGIN
885 OPEN lock_csr;
886 FETCH lock_csr INTO l_object_version_number;
887 CLOSE lock_csr;
888 EXCEPTION
889 WHEN E_Resource_Busy THEN
890
891 IF (lock_csr%ISOPEN) THEN
892 CLOSE lock_csr;
893 END IF;
894 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
895 p_msg_name => G_OVN_ERROR2,
896 p_token1 => G_PROG_NAME_TOKEN,
897 p_token1_value => l_prog_name);
898 RAISE OKL_API.G_EXCEPTION_ERROR;
899 END;
900
901 IF l_object_version_number IS NULL THEN
902
903 OPEN lchk_csr;
904 FETCH lchk_csr INTO lc_object_version_number;
905 CLOSE lchk_csr;
906
907 IF lc_object_version_number IS NULL THEN
908
909 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
910 p_msg_name => G_OVN_ERROR3,
911 p_token1 => G_PROG_NAME_TOKEN,
912 p_token1_value => l_prog_name);
913
914 ELSIF lc_object_version_number <> p_qql_rec.object_version_number THEN
915
916 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
917 p_msg_name => G_OVN_ERROR,
918 p_token1 => G_PROG_NAME_TOKEN,
919 p_token1_value => l_prog_name);
920
921 END IF;
922
923 RAISE OKL_API.G_EXCEPTION_ERROR;
924
925 END IF;
926
927 x_return_status := G_RET_STS_SUCCESS;
928
929 EXCEPTION
930
931 WHEN OKL_API.G_EXCEPTION_ERROR THEN
932
933 x_return_status := G_RET_STS_ERROR;
934
935 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
936
937 x_return_status := G_RET_STS_UNEXP_ERROR;
938
939 WHEN OTHERS THEN
940
941 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
942 p_msg_name => G_DB_ERROR,
943 p_token1 => G_PROG_NAME_TOKEN,
944 p_token1_value => l_prog_name,
945 p_token2 => G_SQLCODE_TOKEN,
946 p_token2_value => sqlcode,
947 p_token3 => G_SQLERRM_TOKEN,
948 p_token3_value => sqlerrm);
949
950 x_return_status := G_RET_STS_UNEXP_ERROR;
951
952 END lock_row;
953
954
955 ---------------------------
956 -- PROCEDURE update_row (B)
957 ---------------------------
958 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_qql_rec IN qql_rec_type) IS
959
960 l_return_status VARCHAR2(1);
961
962 l_prog_name VARCHAR2(61);
963
964 BEGIN
965
966 l_prog_name := G_PKG_NAME||'.update_row (B)';
967
968 lock_row (x_return_status => l_return_status, p_qql_rec => p_qql_rec);
969
970 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
971 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
972 ELSIF l_return_status = G_RET_STS_ERROR THEN
973 RAISE OKL_API.G_EXCEPTION_ERROR;
974 END IF;
975
976 UPDATE okl_quick_quote_lines_b
977 SET
978 object_version_number = p_qql_rec.object_version_number+1
979 ,attribute_category = p_qql_rec.attribute_category
980 ,attribute1 = p_qql_rec.attribute1
981 ,attribute2 = p_qql_rec.attribute2
982 ,attribute3 = p_qql_rec.attribute3
983 ,attribute4 = p_qql_rec.attribute4
984 ,attribute5 = p_qql_rec.attribute5
985 ,attribute6 = p_qql_rec.attribute6
986 ,attribute7 = p_qql_rec.attribute7
987 ,attribute8 = p_qql_rec.attribute8
988 ,attribute9 = p_qql_rec.attribute9
989 ,attribute10 = p_qql_rec.attribute10
990 ,attribute11 = p_qql_rec.attribute11
991 ,attribute12 = p_qql_rec.attribute12
992 ,attribute13 = p_qql_rec.attribute13
993 ,attribute14 = p_qql_rec.attribute14
994 ,attribute15 = p_qql_rec.attribute15
995 ,quick_quote_id = p_qql_rec.quick_quote_id
996 ,type = p_qql_rec.type
997 ,basis = p_qql_rec.basis
998 ,value = p_qql_rec.value
999 ,end_of_term_value_default = p_qql_rec.end_of_term_value_default
1000 ,end_of_term_value = p_qql_rec.end_of_term_value
1001 ,percentage_of_total_cost = p_qql_rec.percentage_of_total_cost
1002 ,item_category_id = p_qql_rec.item_category_id
1003 ,item_category_set_id = p_qql_rec.item_category_set_id
1004 ,lease_rate_factor = p_qql_rec.lease_rate_factor
1005 WHERE id = p_qql_rec.id;
1006
1007 x_return_status := l_return_status;
1008
1009 EXCEPTION
1010
1011 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1012 x_return_status := G_RET_STS_ERROR;
1013
1014 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1015 x_return_status := G_RET_STS_UNEXP_ERROR;
1016
1017 WHEN OTHERS THEN
1018 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1019 p_msg_name => G_DB_ERROR,
1020 p_token1 => G_PROG_NAME_TOKEN,
1021 p_token1_value => l_prog_name,
1022 p_token2 => G_SQLCODE_TOKEN,
1023 p_token2_value => sqlcode,
1024 p_token3 => G_SQLERRM_TOKEN,
1025 p_token3_value => sqlerrm);
1026
1027 x_return_status := G_RET_STS_UNEXP_ERROR;
1028
1029 END update_row;
1030
1031
1032 ----------------------------
1033 -- PROCEDURE update_row (TL)
1034 ----------------------------
1035 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_qqltl_rec IN qqltl_rec_type) IS
1036
1037 l_prog_name VARCHAR2(61);
1038
1039 BEGIN
1040
1041 l_prog_name := G_PKG_NAME||'.update_row (TL)';
1042
1043 UPDATE OKL_QUICK_QUOTE_LINES_TL
1044 SET
1045 source_lang = USERENV('LANG')
1046 ,sfwt_flag = 'Y'
1047 ,last_updated_by = G_USER_ID
1048 ,last_update_date = SYSDATE
1049 ,last_update_login = G_LOGIN_ID
1050 ,short_description = p_qqltl_rec.short_description
1051 ,description = p_qqltl_rec.description
1052 ,comments = p_qqltl_rec.comments
1053 WHERE ID = p_qqltl_rec.id;
1054
1055 UPDATE OKL_QUICK_QUOTE_LINES_TL
1056 SET SFWT_FLAG = 'N'
1057 WHERE ID = p_qqltl_rec.id
1058 AND SOURCE_LANG = LANGUAGE;
1059
1060 x_return_status := G_RET_STS_SUCCESS;
1061
1062 EXCEPTION
1063
1064 WHEN OTHERS THEN
1065
1066 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1067 p_msg_name => G_DB_ERROR,
1068 p_token1 => G_PROG_NAME_TOKEN,
1069 p_token1_value => l_prog_name,
1070 p_token2 => G_SQLCODE_TOKEN,
1071 p_token2_value => sqlcode,
1072 p_token3 => G_SQLERRM_TOKEN,
1073 p_token3_value => sqlerrm);
1074
1075 x_return_status := G_RET_STS_UNEXP_ERROR;
1076
1077 END update_row;
1078
1079
1080 ---------------------------
1081 -- PROCEDURE update_row (V)
1082 ---------------------------
1083 PROCEDURE update_row (
1084 x_return_status OUT NOCOPY VARCHAR2,
1085 p_qqlv_rec IN qqlv_rec_type,
1086 x_qqlv_rec OUT NOCOPY qqlv_rec_type) IS
1087
1088 l_prog_name VARCHAR2(61);
1089
1090 l_return_status VARCHAR2(1);
1091 l_qqlv_rec qqlv_rec_type;
1092 l_qql_rec qql_rec_type;
1093 l_qqltl_rec qqltl_rec_type;
1094
1095 ----------------------
1096 -- populate_new_record
1097 ----------------------
1098 FUNCTION populate_new_record (p_qqlv_rec IN qqlv_rec_type,
1099 x_qqlv_rec OUT NOCOPY qqlv_rec_type) RETURN VARCHAR2 IS
1100
1101 l_prog_name VARCHAR2(61);
1102 l_return_status VARCHAR2(1);
1103 l_db_qqlv_rec qqlv_rec_type;
1104
1105 BEGIN
1106
1107 l_prog_name := G_PKG_NAME||'.populate_new_record';
1108
1109 x_qqlv_rec := p_qqlv_rec;
1110 l_db_qqlv_rec := get_rec (p_qqlv_rec.id, l_return_status);
1111
1112 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1113 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1114 ELSIF l_return_status = G_RET_STS_ERROR THEN
1115 RAISE OKL_API.G_EXCEPTION_ERROR;
1116 END IF;
1117
1118 -- smadhava - modified - G_MISS compliance - Start
1119 IF x_qqlv_rec.attribute_category IS NULL THEN
1120 x_qqlv_rec.attribute_category := l_db_qqlv_rec.attribute_category;
1121 ELSIF x_qqlv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1122 x_qqlv_rec.attribute_category := null;
1123 END IF;
1124
1125 IF x_qqlv_rec.attribute1 IS NULL THEN
1126 x_qqlv_rec.attribute1 := l_db_qqlv_rec.attribute1;
1127 ELSIF x_qqlv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1128 x_qqlv_rec.attribute1 := null;
1129 END IF;
1130
1131 IF x_qqlv_rec.attribute2 IS NULL THEN
1132 x_qqlv_rec.attribute2 := l_db_qqlv_rec.attribute2;
1133 ELSIF x_qqlv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1134 x_qqlv_rec.attribute2 := null;
1135 END IF;
1136
1137 IF x_qqlv_rec.attribute3 IS NULL THEN
1138 x_qqlv_rec.attribute3 := l_db_qqlv_rec.attribute3;
1139 ELSIF x_qqlv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1140 x_qqlv_rec.attribute3 := null;
1141 END IF;
1142
1143 IF x_qqlv_rec.attribute4 IS NULL THEN
1144 x_qqlv_rec.attribute4 := l_db_qqlv_rec.attribute4;
1145 ELSIF x_qqlv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1146 x_qqlv_rec.attribute4 := null;
1147 END IF;
1148
1149 IF x_qqlv_rec.attribute5 IS NULL THEN
1150 x_qqlv_rec.attribute5 := l_db_qqlv_rec.attribute5;
1151 ELSIF x_qqlv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1152 x_qqlv_rec.attribute5 := null;
1153 END IF;
1154
1155 IF x_qqlv_rec.attribute6 IS NULL THEN
1156 x_qqlv_rec.attribute6 := l_db_qqlv_rec.attribute6;
1157 ELSIF x_qqlv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1158 x_qqlv_rec.attribute6 := null;
1159 END IF;
1160
1161 IF x_qqlv_rec.attribute7 IS NULL THEN
1162 x_qqlv_rec.attribute7 := l_db_qqlv_rec.attribute7;
1163 ELSIF x_qqlv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1164 x_qqlv_rec.attribute7 := null;
1165 END IF;
1166
1167 IF x_qqlv_rec.attribute8 IS NULL THEN
1168 x_qqlv_rec.attribute8 := l_db_qqlv_rec.attribute8;
1169 ELSIF x_qqlv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1170 x_qqlv_rec.attribute8 := null;
1171 END IF;
1172
1173 IF x_qqlv_rec.attribute9 IS NULL THEN
1174 x_qqlv_rec.attribute9 := l_db_qqlv_rec.attribute9;
1175 ELSIF x_qqlv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1176 x_qqlv_rec.attribute9 := null;
1177 END IF;
1178
1179 IF x_qqlv_rec.attribute10 IS NULL THEN
1180 x_qqlv_rec.attribute10 := l_db_qqlv_rec.attribute10;
1181 ELSIF x_qqlv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1182 x_qqlv_rec.attribute10 := null;
1183 END IF;
1184
1185 IF x_qqlv_rec.attribute11 IS NULL THEN
1186 x_qqlv_rec.attribute11 := l_db_qqlv_rec.attribute11;
1187 ELSIF x_qqlv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1188 x_qqlv_rec.attribute11 := null;
1189 END IF;
1190
1191 IF x_qqlv_rec.attribute12 IS NULL THEN
1192 x_qqlv_rec.attribute12 := l_db_qqlv_rec.attribute12;
1193 ELSIF x_qqlv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1194 x_qqlv_rec.attribute12 := null;
1195 END IF;
1196
1197 IF x_qqlv_rec.attribute13 IS NULL THEN
1198 x_qqlv_rec.attribute13 := l_db_qqlv_rec.attribute13;
1199 ELSIF x_qqlv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1200 x_qqlv_rec.attribute13 := null;
1201 END IF;
1202
1203 IF x_qqlv_rec.attribute14 IS NULL THEN
1204 x_qqlv_rec.attribute14 := l_db_qqlv_rec.attribute14;
1205 ELSIF x_qqlv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1206 x_qqlv_rec.attribute14 := null;
1207 END IF;
1208
1209 IF x_qqlv_rec.attribute15 IS NULL THEN
1210 x_qqlv_rec.attribute15 := l_db_qqlv_rec.attribute15;
1211 ELSIF x_qqlv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1212 x_qqlv_rec.attribute15 := null;
1213 END IF;
1214
1215 IF x_qqlv_rec.quick_quote_id IS NULL THEN
1216 x_qqlv_rec.quick_quote_id := l_db_qqlv_rec.quick_quote_id;
1217 ELSIF x_qqlv_rec.quick_quote_id = FND_API.G_MISS_NUM THEN
1218 x_qqlv_rec.quick_quote_id := null;
1219 END IF;
1220
1221 IF x_qqlv_rec.type IS NULL THEN
1222 x_qqlv_rec.type := l_db_qqlv_rec.type;
1223 ELSIF x_qqlv_rec.type = FND_API.G_MISS_CHAR THEN
1224 x_qqlv_rec.type := null;
1225 END IF;
1226
1227 IF x_qqlv_rec.basis IS NULL THEN
1228 x_qqlv_rec.basis := l_db_qqlv_rec.basis;
1229 ELSIF x_qqlv_rec.basis = FND_API.G_MISS_CHAR THEN
1230 x_qqlv_rec.basis := null;
1231 END IF;
1232
1233 IF x_qqlv_rec.value IS NULL THEN
1234 x_qqlv_rec.value := l_db_qqlv_rec.value;
1235 ELSIF x_qqlv_rec.value = FND_API.G_MISS_NUM THEN
1236 x_qqlv_rec.value := null;
1237 END IF;
1238
1239 IF x_qqlv_rec.end_of_term_value_default IS NULL THEN
1240 x_qqlv_rec.end_of_term_value_default := l_db_qqlv_rec.end_of_term_value_default;
1241 ELSIF x_qqlv_rec.end_of_term_value_default = FND_API.G_MISS_NUM THEN
1242 x_qqlv_rec.end_of_term_value_default := null;
1243 END IF;
1244
1245 IF x_qqlv_rec.end_of_term_value IS NULL THEN
1246 x_qqlv_rec.end_of_term_value := l_db_qqlv_rec.end_of_term_value;
1247 ELSIF x_qqlv_rec.end_of_term_value = FND_API.G_MISS_NUM THEN
1248 x_qqlv_rec.end_of_term_value := null;
1249 END IF;
1250
1251 IF x_qqlv_rec.percentage_of_total_cost IS NULL THEN
1252 x_qqlv_rec.percentage_of_total_cost := l_db_qqlv_rec.percentage_of_total_cost;
1253 ELSIF x_qqlv_rec.percentage_of_total_cost = FND_API.G_MISS_NUM THEN
1254 x_qqlv_rec.percentage_of_total_cost := null;
1255 END IF;
1256
1257 IF x_qqlv_rec.item_category_id IS NULL THEN
1258 x_qqlv_rec.item_category_id := l_db_qqlv_rec.item_category_id;
1259 ELSIF x_qqlv_rec.item_category_id = FND_API.G_MISS_NUM THEN
1260 x_qqlv_rec.item_category_id := null;
1261 END IF;
1262
1263 IF x_qqlv_rec.item_category_set_id IS NULL THEN
1264 x_qqlv_rec.item_category_set_id := l_db_qqlv_rec.item_category_set_id;
1265 ELSIF x_qqlv_rec.item_category_set_id = FND_API.G_MISS_NUM THEN
1266 x_qqlv_rec.item_category_set_id := null;
1267 END IF;
1268
1269 IF x_qqlv_rec.lease_rate_factor IS NULL THEN
1270 x_qqlv_rec.lease_rate_factor := l_db_qqlv_rec.lease_rate_factor;
1271 ELSIF x_qqlv_rec.lease_rate_factor = FND_API.G_MISS_NUM THEN
1272 x_qqlv_rec.lease_rate_factor := null;
1273 END IF;
1274
1275 IF x_qqlv_rec.short_description IS NULL THEN
1276 x_qqlv_rec.short_description := l_db_qqlv_rec.short_description;
1277 ELSIF x_qqlv_rec.short_description = FND_API.G_MISS_CHAR THEN
1278 x_qqlv_rec.short_description := null;
1279 END IF;
1280
1281 IF x_qqlv_rec.description IS NULL THEN
1282 x_qqlv_rec.description := l_db_qqlv_rec.description;
1283 ELSIF x_qqlv_rec.description = FND_API.G_MISS_CHAR THEN
1284 x_qqlv_rec.description := null;
1285 END IF;
1286
1287 IF x_qqlv_rec.comments IS NULL THEN
1288 x_qqlv_rec.comments := l_db_qqlv_rec.comments;
1289 ELSIF x_qqlv_rec.comments = FND_API.G_MISS_CHAR THEN
1290 x_qqlv_rec.comments := null;
1291 END IF;
1292 -- smadhava - modified - G_MISS compliance - End
1293
1294 RETURN l_return_status;
1295
1296 EXCEPTION
1297
1298 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1299
1300 x_return_status := G_RET_STS_ERROR;
1301
1302 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1303
1304 x_return_status := G_RET_STS_UNEXP_ERROR;
1305
1306 WHEN OTHERS THEN
1307
1308 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1309 p_msg_name => G_DB_ERROR,
1310 p_token1 => G_PROG_NAME_TOKEN,
1311 p_token1_value => l_prog_name,
1312 p_token2 => G_SQLCODE_TOKEN,
1313 p_token2_value => sqlcode,
1314 p_token3 => G_SQLERRM_TOKEN,
1315 p_token3_value => sqlerrm);
1316
1317 x_return_status := G_RET_STS_UNEXP_ERROR;
1318
1319 END populate_new_record;
1320
1321 BEGIN
1322
1323 l_prog_name := G_PKG_NAME||'.update_row (V)';
1324
1325 l_return_status := populate_new_record (p_qqlv_rec, l_qqlv_rec);
1326
1327 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1328 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1329 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1330 RAISE OKL_API.G_EXCEPTION_ERROR;
1331 END IF;
1332
1333 l_return_status := validate_attributes (l_qqlv_rec);
1334
1335 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1336 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1337 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1338 RAISE OKL_API.G_EXCEPTION_ERROR;
1339 END IF;
1340
1341 l_return_status := validate_record (l_qqlv_rec);
1342
1343 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1344 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1345 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1346 RAISE OKL_API.G_EXCEPTION_ERROR;
1347 END IF;
1348
1349 migrate (l_qqlv_rec, l_qql_rec);
1350 migrate (l_qqlv_rec, l_qqltl_rec);
1351
1352 update_row (x_return_status => l_return_status, p_qql_rec => l_qql_rec);
1353
1354 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1355 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1356 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1357 RAISE OKL_API.G_EXCEPTION_ERROR;
1358 END IF;
1359
1360 update_row (x_return_status => l_return_status, p_qqltl_rec => l_qqltl_rec);
1361
1362 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1363 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1364 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1365 RAISE OKL_API.G_EXCEPTION_ERROR;
1366 END IF;
1367
1368 x_return_status := l_return_status;
1369 x_qqlv_rec := l_qqlv_rec;
1370
1371 EXCEPTION
1372
1373 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1374
1375 x_return_status := G_RET_STS_ERROR;
1376
1377 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1378
1379 x_return_status := G_RET_STS_UNEXP_ERROR;
1380
1381 WHEN OTHERS THEN
1382
1383 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1384 p_msg_name => G_DB_ERROR,
1385 p_token1 => G_PROG_NAME_TOKEN,
1386 p_token1_value => l_prog_name,
1387 p_token2 => G_SQLCODE_TOKEN,
1388 p_token2_value => sqlcode,
1389 p_token3 => G_SQLERRM_TOKEN,
1390 p_token3_value => sqlerrm);
1391
1392 x_return_status := G_RET_STS_UNEXP_ERROR;
1393
1394 END update_row;
1395
1396
1397 -----------------------------
1398 -- PROCEDURE update_row (REC)
1399 -----------------------------
1400 PROCEDURE update_row(
1401 p_api_version IN NUMBER,
1402 p_init_msg_list IN VARCHAR2,
1403 x_return_status OUT NOCOPY VARCHAR2,
1404 x_msg_count OUT NOCOPY NUMBER,
1405 x_msg_data OUT NOCOPY VARCHAR2,
1406 p_qqlv_rec IN qqlv_rec_type,
1407 x_qqlv_rec OUT NOCOPY qqlv_rec_type) IS
1408
1409 l_return_status VARCHAR2(1);
1410
1411 l_prog_name VARCHAR2(61);
1412
1413 BEGIN
1414
1415 l_prog_name := G_PKG_NAME||'.update_row (REC)';
1416
1417 IF p_init_msg_list = G_TRUE THEN
1418 FND_MSG_PUB.initialize;
1419 END IF;
1420
1421 update_row (x_return_status => l_return_status,
1422 p_qqlv_rec => p_qqlv_rec,
1423 x_qqlv_rec => x_qqlv_rec);
1424
1425 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1426 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1427 ELSIF l_return_status = G_RET_STS_ERROR THEN
1428 RAISE OKL_API.G_EXCEPTION_ERROR;
1429 END IF;
1430
1431 --viselvar added
1432 x_qqlv_rec.object_version_number:=x_qqlv_rec.object_version_number+1;
1433 x_return_status := l_return_status;
1434
1435 EXCEPTION
1436
1437 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1438
1439 x_return_status := G_RET_STS_ERROR;
1440
1441 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1442
1443 x_return_status := G_RET_STS_UNEXP_ERROR;
1444
1445 WHEN OTHERS THEN
1446
1447 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1448 p_msg_name => G_DB_ERROR,
1449 p_token1 => G_PROG_NAME_TOKEN,
1450 p_token1_value => l_prog_name,
1451 p_token2 => G_SQLCODE_TOKEN,
1452 p_token2_value => sqlcode,
1453 p_token3 => G_SQLERRM_TOKEN,
1454 p_token3_value => sqlerrm);
1455
1456 x_return_status := G_RET_STS_UNEXP_ERROR;
1457
1458 END update_row;
1459
1460
1461 -----------------------------
1462 -- PROCEDURE update_row (TBL)
1463 -----------------------------
1464 PROCEDURE update_row(
1465 p_api_version IN NUMBER,
1466 p_init_msg_list IN VARCHAR2,
1467 x_return_status OUT NOCOPY VARCHAR2,
1468 x_msg_count OUT NOCOPY NUMBER,
1469 x_msg_data OUT NOCOPY VARCHAR2,
1470 p_qqlv_tbl IN qqlv_tbl_type,
1471 x_qqlv_tbl OUT NOCOPY qqlv_tbl_type) IS
1472
1473 l_return_status VARCHAR2(1);
1474 i BINARY_INTEGER;
1475 l_prog_name VARCHAR2(61);
1476
1477 BEGIN
1478
1479 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
1480
1481 IF p_init_msg_list = G_TRUE THEN
1482 FND_MSG_PUB.initialize;
1483 END IF;
1484
1485 x_qqlv_tbl := p_qqlv_tbl;
1486
1487 IF (p_qqlv_tbl.COUNT > 0) THEN
1488
1489 i := p_qqlv_tbl.FIRST;
1490
1491 LOOP
1492
1493 IF p_qqlv_tbl.EXISTS(i) THEN
1494 update_row (p_api_version => p_api_version,
1495 p_init_msg_list => p_init_msg_list,
1496 x_return_status => l_return_status,
1497 x_msg_count => x_msg_count,
1498 x_msg_data => x_msg_data,
1499 p_qqlv_rec => p_qqlv_tbl(i),
1500 x_qqlv_rec => x_qqlv_tbl(i));
1501
1502 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1503 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1504 ELSIF l_return_status = G_RET_STS_ERROR THEN
1505 RAISE OKL_API.G_EXCEPTION_ERROR;
1506 END IF;
1507
1508 EXIT WHEN (i = p_qqlv_tbl.LAST);
1509 i := p_qqlv_tbl.NEXT(i);
1510
1511 END IF;
1512
1513 END LOOP;
1514
1515 ELSE
1516
1517 l_return_status := G_RET_STS_SUCCESS;
1518
1519 END IF;
1520
1521 x_return_status := l_return_status;
1522
1523 EXCEPTION
1524
1525 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1526
1527 x_return_status := G_RET_STS_ERROR;
1528
1529 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1530
1531 x_return_status := G_RET_STS_UNEXP_ERROR;
1532
1533 WHEN OTHERS THEN
1534
1535 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1536 p_msg_name => G_DB_ERROR,
1537 p_token1 => G_PROG_NAME_TOKEN,
1538 p_token1_value => l_prog_name,
1539 p_token2 => G_SQLCODE_TOKEN,
1540 p_token2_value => sqlcode,
1541 p_token3 => G_SQLERRM_TOKEN,
1542 p_token3_value => sqlerrm);
1543
1544 x_return_status := G_RET_STS_UNEXP_ERROR;
1545
1546 END update_row;
1547
1548
1549 -----------------
1550 -- delete_row (V)
1551 -----------------
1552 PROCEDURE delete_row(
1553 x_return_status OUT NOCOPY VARCHAR2,
1554 p_id IN NUMBER) IS
1555
1556 l_prog_name VARCHAR2(61);
1557
1558 BEGIN
1559
1560 l_prog_name := G_PKG_NAME||'.delete_row (V)';
1561
1562 DELETE FROM OKL_QUICK_QUOTE_LINES_B WHERE id = p_id;
1563 DELETE FROM OKL_QUICK_QUOTE_LINES_TL WHERE id = p_id;
1564
1565 x_return_status := G_RET_STS_SUCCESS;
1566
1567 EXCEPTION
1568
1569 WHEN OTHERS THEN
1570
1571 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1572 p_msg_name => G_DB_ERROR,
1573 p_token1 => G_PROG_NAME_TOKEN,
1574 p_token1_value => l_prog_name,
1575 p_token2 => G_SQLCODE_TOKEN,
1576 p_token2_value => sqlcode,
1577 p_token3 => G_SQLERRM_TOKEN,
1578 p_token3_value => sqlerrm);
1579
1580 x_return_status := G_RET_STS_UNEXP_ERROR;
1581
1582 END delete_row;
1583
1584
1585 -----------------------------
1586 -- PROCEDURE delete_row (REC)
1587 -----------------------------
1588 PROCEDURE delete_row(
1589 p_api_version IN NUMBER,
1590 p_init_msg_list IN VARCHAR2,
1591 x_return_status OUT NOCOPY VARCHAR2,
1592 x_msg_count OUT NOCOPY NUMBER,
1593 x_msg_data OUT NOCOPY VARCHAR2,
1594 p_qqlv_rec IN qqlv_rec_type) IS
1595
1596 l_return_status VARCHAR2(1);
1597
1598 l_prog_name VARCHAR2(61);
1599
1600 BEGIN
1601
1602 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
1603
1604 IF p_init_msg_list = G_TRUE THEN
1605 FND_MSG_PUB.initialize;
1606 END IF;
1607
1608 delete_row (x_return_status => l_return_status,
1609 p_id => p_qqlv_rec.id);
1610
1611 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1612 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1613 ELSIF l_return_status = G_RET_STS_ERROR THEN
1614 RAISE OKL_API.G_EXCEPTION_ERROR;
1615 END IF;
1616
1617 x_return_status := l_return_status;
1618
1619 EXCEPTION
1620
1621 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1622
1623 x_return_status := G_RET_STS_ERROR;
1624
1625 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1626
1627 x_return_status := G_RET_STS_UNEXP_ERROR;
1628
1629 WHEN OTHERS THEN
1630
1631 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1632 p_msg_name => G_DB_ERROR,
1633 p_token1 => G_PROG_NAME_TOKEN,
1634 p_token1_value => l_prog_name,
1635 p_token2 => G_SQLCODE_TOKEN,
1636 p_token2_value => sqlcode,
1637 p_token3 => G_SQLERRM_TOKEN,
1638 p_token3_value => sqlerrm);
1639
1640 x_return_status := G_RET_STS_UNEXP_ERROR;
1641
1642 END delete_row;
1643
1644
1645 -------------------
1646 -- delete_row (TBL)
1647 -------------------
1648 PROCEDURE delete_row(
1649 p_api_version IN NUMBER,
1650 p_init_msg_list IN VARCHAR2,
1651 x_return_status OUT NOCOPY VARCHAR2,
1652 x_msg_count OUT NOCOPY NUMBER,
1653 x_msg_data OUT NOCOPY VARCHAR2,
1654 p_qqlv_tbl IN qqlv_tbl_type) IS
1655
1656 l_return_status VARCHAR2(1);
1657 i BINARY_INTEGER;
1658
1659 l_prog_name VARCHAR2(61);
1660
1661 BEGIN
1662
1663 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
1664
1665 IF p_init_msg_list = G_TRUE THEN
1666 FND_MSG_PUB.initialize;
1667 END IF;
1668
1669 IF (p_qqlv_tbl.COUNT > 0) THEN
1670
1671 i := p_qqlv_tbl.FIRST;
1672
1673 LOOP
1674
1675 IF p_qqlv_tbl.EXISTS(i) THEN
1676
1677 delete_row (x_return_status => l_return_status,
1678 p_id => p_qqlv_tbl(i).id);
1679
1680 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1681 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1682 ELSIF l_return_status = G_RET_STS_ERROR THEN
1683 RAISE OKL_API.G_EXCEPTION_ERROR;
1684 END IF;
1685
1686 EXIT WHEN (i = p_qqlv_tbl.LAST);
1687 i := p_qqlv_tbl.NEXT(i);
1688
1689 END IF;
1690
1691 END LOOP;
1692
1693 ELSE
1694
1695 l_return_status := G_RET_STS_SUCCESS;
1696
1697 END IF;
1698
1699 x_return_status := l_return_status;
1700
1701 EXCEPTION
1702
1703 WHEN OTHERS THEN
1704
1705 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1706 p_msg_name => G_DB_ERROR,
1707 p_token1 => G_PROG_NAME_TOKEN,
1708 p_token1_value => l_prog_name,
1709 p_token2 => G_SQLCODE_TOKEN,
1710 p_token2_value => sqlcode,
1711 p_token3 => G_SQLERRM_TOKEN,
1712 p_token3_value => sqlerrm);
1713
1714 x_return_status := G_RET_STS_UNEXP_ERROR;
1715
1716 END delete_row;
1717
1718
1719 END OKL_QQL_PVT;