[Home] [Help]
PACKAGE BODY: APPS.OKL_QQH_PVT
Source
1 PACKAGE BODY OKL_QQH_PVT AS
2 /* $Header: OKLSQQHB.pls 120.2.12010000.4 2008/11/17 10:58:39 kkorrapo ship $ */
3
4 -------------------------
5 -- PROCEDURE add_language
6 -------------------------
7 PROCEDURE add_language IS
8
9 BEGIN
10
11 DELETE FROM OKL_QUICK_QUOTES_TL T
12 WHERE NOT EXISTS (SELECT NULL FROM OKL_QUICK_QUOTES_ALL_B B WHERE B.ID =T.ID);
13
14 UPDATE OKL_QUICK_QUOTES_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_QUOTES_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_QUOTES_TL SUBB,
32 OKL_QUICK_QUOTES_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_QUOTES_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_QUOTES_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_QUOTES_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_qqhv_rec IN qqhv_rec_type) RETURN qqhv_rec_type IS
88
89 l_qqhv_rec qqhv_rec_type;
90
91 BEGIN
92
93 l_qqhv_rec := p_qqhv_rec;
94
95 -- Not applicable to ID and OBJECT_VERSION_NUMBER
96
97 IF l_qqhv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
98 l_qqhv_rec.attribute_category := NULL;
99 END IF;
100 IF l_qqhv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
101 l_qqhv_rec.attribute1 := NULL;
102 END IF;
103 IF l_qqhv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
104 l_qqhv_rec.attribute2 := NULL;
105 END IF;
106 IF l_qqhv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
107 l_qqhv_rec.attribute3 := NULL;
108 END IF;
109 IF l_qqhv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
110 l_qqhv_rec.attribute4 := NULL;
111 END IF;
112 IF l_qqhv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
113 l_qqhv_rec.attribute5 := NULL;
114 END IF;
115 IF l_qqhv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
116 l_qqhv_rec.attribute6 := NULL;
117 END IF;
118 IF l_qqhv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
119 l_qqhv_rec.attribute7 := NULL;
120 END IF;
121 IF l_qqhv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
122 l_qqhv_rec.attribute8 := NULL;
123 END IF;
124 IF l_qqhv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
125 l_qqhv_rec.attribute9 := NULL;
126 END IF;
127 IF l_qqhv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
128 l_qqhv_rec.attribute10 := NULL;
129 END IF;
130 IF l_qqhv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
131 l_qqhv_rec.attribute11 := NULL;
132 END IF;
133 IF l_qqhv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
134 l_qqhv_rec.attribute12 := NULL;
135 END IF;
136 IF l_qqhv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
137 l_qqhv_rec.attribute13 := NULL;
138 END IF;
139 IF l_qqhv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
140 l_qqhv_rec.attribute14 := NULL;
141 END IF;
142 IF l_qqhv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
143 l_qqhv_rec.attribute15 := NULL;
144 END IF;
145 IF l_qqhv_rec.reference_number = FND_API.G_MISS_CHAR THEN
146 l_qqhv_rec.reference_number := NULL;
147 END IF;
148 IF l_qqhv_rec.expected_start_date = FND_API.G_MISS_DATE THEN
149 l_qqhv_rec.expected_start_date := NULL;
150 END IF;
151 IF l_qqhv_rec.org_id = FND_API.G_MISS_NUM THEN
152 l_qqhv_rec.org_id := NULL;
153 END IF;
154 IF l_qqhv_rec.inv_org_id = FND_API.G_MISS_NUM THEN
155 l_qqhv_rec.inv_org_id := NULL;
156 END IF;
157 IF l_qqhv_rec.currency_code = FND_API.G_MISS_CHAR THEN
158 l_qqhv_rec.currency_code := NULL;
159 END IF;
160 IF l_qqhv_rec.term = FND_API.G_MISS_NUM THEN
161 l_qqhv_rec.term := NULL;
162 END IF;
163 IF l_qqhv_rec.end_of_term_option_id = FND_API.G_MISS_NUM THEN
164 l_qqhv_rec.end_of_term_option_id := NULL;
165 END IF;
166 IF l_qqhv_rec.pricing_method = FND_API.G_MISS_CHAR THEN
167 l_qqhv_rec.pricing_method := NULL;
168 END IF;
169 IF l_qqhv_rec.lease_opportunity_id = FND_API.G_MISS_NUM THEN
170 l_qqhv_rec.lease_opportunity_id := NULL;
171 END IF;
172 IF l_qqhv_rec.originating_vendor_id = FND_API.G_MISS_NUM THEN
173 l_qqhv_rec.originating_vendor_id := NULL;
174 END IF;
175 IF l_qqhv_rec.program_agreement_id = FND_API.G_MISS_NUM THEN
176 l_qqhv_rec.program_agreement_id := NULL;
177 END IF;
178 IF l_qqhv_rec.sales_rep_id = FND_API.G_MISS_NUM THEN
179 l_qqhv_rec.sales_rep_id := NULL;
180 END IF;
181 IF l_qqhv_rec.sales_territory_id = FND_API.G_MISS_NUM THEN
182 l_qqhv_rec.sales_territory_id := NULL;
183 END IF;
184 IF l_qqhv_rec.structured_pricing = FND_API.G_MISS_CHAR THEN
185 l_qqhv_rec.structured_pricing := NULL;
186 END IF;
187 IF l_qqhv_rec.line_level_pricing = FND_API.G_MISS_CHAR THEN
188 l_qqhv_rec.line_level_pricing := NULL;
189 END IF;
190 IF l_qqhv_rec.rate_template_id = FND_API.G_MISS_NUM THEN
191 l_qqhv_rec.rate_template_id := NULL;
192 END IF;
193 IF l_qqhv_rec.rate_card_id = FND_API.G_MISS_NUM THEN
194 l_qqhv_rec.rate_card_id := NULL;
195 END IF;
196 IF l_qqhv_rec.lease_rate_factor = FND_API.G_MISS_NUM THEN
197 l_qqhv_rec.lease_rate_factor := NULL;
198 END IF;
199 IF l_qqhv_rec.target_rate_type = FND_API.G_MISS_CHAR THEN
200 l_qqhv_rec.target_rate_type := NULL;
201 END IF;
202 IF l_qqhv_rec.target_rate = FND_API.G_MISS_NUM THEN
203 l_qqhv_rec.target_rate := NULL;
204 END IF;
205 IF l_qqhv_rec.target_amount = FND_API.G_MISS_NUM THEN
206 l_qqhv_rec.target_amount := NULL;
207 END IF;
208 IF l_qqhv_rec.target_frequency = FND_API.G_MISS_CHAR THEN
209 l_qqhv_rec.target_frequency := NULL;
210 END IF;
211 IF l_qqhv_rec.target_arrears = FND_API.G_MISS_CHAR THEN
212 l_qqhv_rec.target_arrears := NULL;
213 END IF;
214 IF l_qqhv_rec.target_periods = FND_API.G_MISS_NUM THEN
215 l_qqhv_rec.target_periods := NULL;
216 END IF;
217 IF l_qqhv_rec.iir = FND_API.G_MISS_NUM THEN
218 l_qqhv_rec.iir := NULL;
219 END IF;
220 IF l_qqhv_rec.sub_iir = FND_API.G_MISS_NUM THEN
221 l_qqhv_rec.sub_iir := NULL;
222 END IF;
223 IF l_qqhv_rec.booking_yield = FND_API.G_MISS_NUM THEN
224 l_qqhv_rec.booking_yield := NULL;
225 END IF;
226 IF l_qqhv_rec.sub_booking_yield = FND_API.G_MISS_NUM THEN
227 l_qqhv_rec.sub_booking_yield := NULL;
228 END IF;
229 IF l_qqhv_rec.pirr = FND_API.G_MISS_NUM THEN
230 l_qqhv_rec.pirr := NULL;
231 END IF;
232 IF l_qqhv_rec.sub_pirr = FND_API.G_MISS_NUM THEN
233 l_qqhv_rec.sub_pirr := NULL;
234 END IF;
235 IF l_qqhv_rec.airr = FND_API.G_MISS_NUM THEN
236 l_qqhv_rec.airr := NULL;
237 END IF;
238 IF l_qqhv_rec.sub_airr = FND_API.G_MISS_NUM THEN
239 l_qqhv_rec.sub_airr := NULL;
240 END IF;
241
242 RETURN l_qqhv_rec;
243
244 END null_out_defaults;
245
246
247 -------------------
248 -- FUNCTION get_rec
249 -------------------
250 FUNCTION get_rec (p_id IN NUMBER
251 ,x_return_status OUT NOCOPY VARCHAR2) RETURN qqhv_rec_type IS
252
253 l_qqhv_rec qqhv_rec_type;
254 l_prog_name VARCHAR2(61);
255
256 BEGIN
257
258 l_prog_name := G_PKG_NAME||'.get_rec';
259
260 SELECT
261 id
262 ,object_version_number
263 ,attribute_category
264 ,attribute1
265 ,attribute2
266 ,attribute3
267 ,attribute4
268 ,attribute5
269 ,attribute6
270 ,attribute7
271 ,attribute8
272 ,attribute9
273 ,attribute10
274 ,attribute11
275 ,attribute12
276 ,attribute13
277 ,attribute14
278 ,attribute15
279 ,reference_number
280 ,expected_start_date
281 ,org_id
282 ,inv_org_id
283 ,currency_code
284 ,term
285 ,end_of_term_option_id
286 ,pricing_method
287 ,lease_opportunity_id
288 ,originating_vendor_id
289 ,program_agreement_id
290 ,sales_rep_id
291 ,sales_territory_id
292 ,structured_pricing
293 ,line_level_pricing
294 ,rate_template_id
295 ,rate_card_id
296 ,lease_rate_factor
297 ,target_rate_type
298 ,target_rate
299 ,target_amount
300 ,target_frequency
301 ,target_arrears
302 ,target_periods
303 ,iir
304 ,sub_iir
305 ,booking_yield
306 ,sub_booking_yield
307 ,pirr
308 ,sub_pirr
309 ,airr
310 ,sub_airr
311 -- abhsaxen - added - start
312 ,sts_code
313 -- abhsaxen - added - end
314 INTO
315 l_qqhv_rec.id
316 ,l_qqhv_rec.object_version_number
317 ,l_qqhv_rec.attribute_category
318 ,l_qqhv_rec.attribute1
319 ,l_qqhv_rec.attribute2
320 ,l_qqhv_rec.attribute3
321 ,l_qqhv_rec.attribute4
322 ,l_qqhv_rec.attribute5
323 ,l_qqhv_rec.attribute6
324 ,l_qqhv_rec.attribute7
325 ,l_qqhv_rec.attribute8
326 ,l_qqhv_rec.attribute9
327 ,l_qqhv_rec.attribute10
328 ,l_qqhv_rec.attribute11
329 ,l_qqhv_rec.attribute12
330 ,l_qqhv_rec.attribute13
331 ,l_qqhv_rec.attribute14
332 ,l_qqhv_rec.attribute15
333 ,l_qqhv_rec.reference_number
334 ,l_qqhv_rec.expected_start_date
335 ,l_qqhv_rec.org_id
336 ,l_qqhv_rec.inv_org_id
337 ,l_qqhv_rec.currency_code
338 ,l_qqhv_rec.term
339 ,l_qqhv_rec.end_of_term_option_id
340 ,l_qqhv_rec.pricing_method
341 ,l_qqhv_rec.lease_opportunity_id
342 ,l_qqhv_rec.originating_vendor_id
343 ,l_qqhv_rec.program_agreement_id
344 ,l_qqhv_rec.sales_rep_id
345 ,l_qqhv_rec.sales_territory_id
346 ,l_qqhv_rec.structured_pricing
347 ,l_qqhv_rec.line_level_pricing
348 ,l_qqhv_rec.rate_template_id
349 ,l_qqhv_rec.rate_card_id
350 ,l_qqhv_rec.lease_rate_factor
351 ,l_qqhv_rec.target_rate_type
352 ,l_qqhv_rec.target_rate
353 ,l_qqhv_rec.target_amount
354 ,l_qqhv_rec.target_frequency
355 ,l_qqhv_rec.target_arrears
356 ,l_qqhv_rec.target_periods
357 ,l_qqhv_rec.iir
358 ,l_qqhv_rec.sub_iir
359 ,l_qqhv_rec.booking_yield
360 ,l_qqhv_rec.sub_booking_yield
361 ,l_qqhv_rec.pirr
362 ,l_qqhv_rec.sub_pirr
363 ,l_qqhv_rec.airr
364 ,l_qqhv_rec.sub_airr
365 -- abhsaxen - added - start
366 ,l_qqhv_rec.sts_code
367 -- abhsaxen - added - end
368 FROM OKL_QUICK_QUOTES_V
369 WHERE id = p_id;
370
371 x_return_status := G_RET_STS_SUCCESS;
372 RETURN l_qqhv_rec;
373
374 EXCEPTION
375
376 WHEN OTHERS THEN
377
378 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
379 p_msg_name => G_DB_ERROR,
380 p_token1 => G_PROG_NAME_TOKEN,
381 p_token1_value => l_prog_name,
382 p_token2 => G_SQLCODE_TOKEN,
383 p_token2_value => sqlcode,
384 p_token3 => G_SQLERRM_TOKEN,
385 p_token3_value => sqlerrm);
386
387 x_return_status := G_RET_STS_UNEXP_ERROR;
388
389 END get_rec;
390
391
392 ------------------------
393 -- PROCEDURE validate_id
394 ------------------------
398 OKL_API.set_message(p_app_name => G_APP_NAME,
395 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
396 BEGIN
397 IF p_id IS NULL THEN
399 p_msg_name => G_COL_ERROR,
400 p_token1 => G_COL_NAME_TOKEN,
401 p_token1_value => 'id',
402 p_token2 => G_PKG_NAME_TOKEN,
403 p_token2_value => G_PKG_NAME);
404 RAISE OKL_API.G_EXCEPTION_ERROR;
405 END IF;
406 x_return_status := G_RET_STS_SUCCESS;
407 END validate_id;
408
409
410 -------------------------------------------
411 -- PROCEDURE validate_object_version_number
412 -------------------------------------------
413 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
414 BEGIN
415 IF p_object_version_number IS NULL THEN
416 OKL_API.set_message(p_app_name => G_APP_NAME,
417 p_msg_name => G_COL_ERROR,
418 p_token1 => G_COL_NAME_TOKEN,
419 p_token1_value => 'object_version_number',
420 p_token2 => G_PKG_NAME_TOKEN,
421 p_token2_value => G_PKG_NAME);
422 RAISE OKL_API.G_EXCEPTION_ERROR;
423 END IF;
424 x_return_status := G_RET_STS_SUCCESS;
425 END validate_object_version_number;
426
427 -------------------------------------------
428 -- Function validate_sts_Code
429 -------------------------------------------
430
431 PROCEDURE validate_sts_code(x_return_status out NOCOPY varchar2,p_sts_code IN VARCHAR2) IS
432 l_api_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'validate_sts_code';
433 BEGIN
434 x_return_status := OKL_API.G_RET_STS_SUCCESS;
435 -- Column is mandatory
436 IF (p_sts_code is null) THEN
437 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
438 p_msg_name => G_COL_ERROR,
439 p_token1 => g_col_name_token,
440 p_token1_value => 'sts_code',
441 p_token2 => G_PKG_NAME_TOKEN,
442 p_token2_value => G_PKG_NAME);
443 -- notify caller of an error
444 RAISE OKL_API.G_EXCEPTION_ERROR;
445 END IF;
446 -- Lookup Code Validation
447 x_return_status := OKL_UTIL.check_lookup_code(
448 p_lookup_type => 'OKL_QQ_STATUS',
449 p_lookup_code => p_sts_code);
450
451 IF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
452 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
453 p_msg_name => G_COL_ERROR,
454 p_token1 => g_col_name_token,
455 p_token1_value => 'sts_code',
456 p_token2 => G_PKG_NAME_TOKEN,
457 p_token2_value => G_PKG_NAME);
458 -- notify caller of an error
459 raise OKL_API.G_EXCEPTION_ERROR;
460 ELSIF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
461 -- notify caller of an error
462 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
463 END IF;
464 EXCEPTION
465 WHEN OKL_API.G_EXCEPTION_ERROR THEN
466 x_return_status := G_RET_STS_ERROR;
467 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
468 x_return_status := G_RET_STS_UNEXP_ERROR;
469 WHEN OTHERS THEN
470 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
471 p_msg_name => G_DB_ERROR,
472 p_token1 => G_PROG_NAME_TOKEN,
473 p_token1_value => l_api_name,
474 p_token2 => 'SQLCODE',
475 p_token2_value => sqlcode,
476 p_token3 => 'SQLERRM',
477 p_token3_value => sqlerrm);
478 x_return_status := G_RET_STS_UNEXP_ERROR;
479 END validate_sts_code;
480
481 --Bug 7022258-Added by kkorrapo
482 FUNCTION validate_unique(p_qqhv_rec_type IN qqhv_rec_type) RETURN VARCHAR2 IS
483
484 CURSOR chk_uniquness IS
485 SELECT 'x'
486 FROM okl_quick_quotes_b
487 WHERE reference_number = p_qqhv_rec_type.reference_number
488 AND id <> NVL(p_qqhv_rec_type.id, -9999);
489
490 l_dummy_var VARCHAR2(1);
491 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
492 l_api_name CONSTANT VARCHAR2(61) := g_pkg_name || '.' || 'validate_header';
493 l_msg_count NUMBER;
494 l_msg_data VARCHAR2(2000);
495
496 CURSOR c_get_prefix IS
497 SELECT QCKQTE_SEQ_PREFIX_TXT
498 FROM okl_system_params;
499 l_prefix VARCHAR2(30);
500
501 BEGIN
502
503 OPEN chk_uniquness; -- QQ Reference Number should be unique
504 FETCH chk_uniquness INTO l_dummy_var;
505 CLOSE chk_uniquness; -- if l_dummy_var is 'x' then Ref Num already exists
506
507 IF (l_dummy_var = 'x') THEN
508 okl_api.set_message(p_app_name => g_app_name
509 ,p_msg_name => 'OKL_DUPLICATE_CURE_REQUEST'
513 END IF;
510 ,p_token1 => 'COL_NAME'
511 ,p_token1_value => p_qqhv_rec_type.reference_number);
512 RETURN okl_api.g_ret_sts_error;
514
515 --get prefix
516 OPEN c_get_prefix;
517 FETCH c_get_prefix INTO l_prefix;
518 CLOSE c_get_prefix;
519
520 IF l_prefix IS NOT NULL THEN
521 IF INSTR(p_qqhv_rec_type.reference_number,l_prefix) <> 1 THEN
522 okl_api.set_message(p_app_name => g_app_name
523 ,p_msg_name => 'OKL_NO_PREFIX'
524 ,p_token1 => 'COL_NAME'
525 ,p_token1_value => p_qqhv_rec_type.reference_number
526 ,p_token2 => 'PREFIX'
527 ,p_token2_value => l_prefix);
528 RETURN okl_api.g_ret_sts_error;
529 END IF;
530 END IF;
531
532 RETURN x_return_status;
533
534
535 END validate_unique;
536 --Bug 7022258--Addition end
537
538 --------------------------------------
539 -- PROCEDURE validate_reference_number
540 --------------------------------------
541 PROCEDURE validate_reference_number (x_return_status OUT NOCOPY VARCHAR2, p_reference_number IN VARCHAR2) IS
542 BEGIN
543 IF p_reference_number IS NULL THEN
544 OKL_API.set_message(p_app_name => G_APP_NAME,
545 p_msg_name => G_COL_ERROR,
546 p_token1 => G_COL_NAME_TOKEN,
547 p_token1_value => 'reference_number',
548 p_token2 => G_PKG_NAME_TOKEN,
549 p_token2_value => G_PKG_NAME);
550 RAISE OKL_API.G_EXCEPTION_ERROR;
551 END IF;
552 x_return_status := G_RET_STS_SUCCESS;
553 END validate_reference_number;
554
555
556 -----------------------------------------
557 -- PROCEDURE validate_expected_start_date
558 -----------------------------------------
559 PROCEDURE validate_expected_start_date (x_return_status OUT NOCOPY VARCHAR2, p_expected_start_date IN DATE) IS
560 BEGIN
561 IF p_expected_start_date IS NULL THEN
562 OKL_API.set_message(p_app_name => G_APP_NAME,
563 p_msg_name => G_COL_ERROR,
564 p_token1 => G_COL_NAME_TOKEN,
565 p_token1_value => 'expected_start_date',
566 p_token2 => G_PKG_NAME_TOKEN,
567 p_token2_value => G_PKG_NAME);
568 RAISE OKL_API.G_EXCEPTION_ERROR;
569 END IF;
570 x_return_status := G_RET_STS_SUCCESS;
571 END validate_expected_start_date;
572
573
574 ----------------------------
575 -- PROCEDURE validate_org_id
576 ----------------------------
577 PROCEDURE validate_org_id (x_return_status OUT NOCOPY VARCHAR2, p_org_id IN NUMBER) IS
578 BEGIN
579 IF p_org_id IS NULL THEN
580 OKL_API.set_message(p_app_name => G_APP_NAME,
581 p_msg_name => G_COL_ERROR,
582 p_token1 => G_COL_NAME_TOKEN,
583 p_token1_value => 'org_id',
584 p_token2 => G_PKG_NAME_TOKEN,
585 p_token2_value => G_PKG_NAME);
586 RAISE OKL_API.G_EXCEPTION_ERROR;
587 END IF;
588 x_return_status := G_RET_STS_SUCCESS;
589 END validate_org_id;
590
591
592 --------------------------------
593 -- PROCEDURE validate_inv_org_id
594 --------------------------------
595 PROCEDURE validate_inv_org_id (x_return_status OUT NOCOPY VARCHAR2, p_inv_org_id IN NUMBER) IS
596 BEGIN
597 IF p_inv_org_id IS NULL THEN
598 OKL_API.set_message(p_app_name => G_APP_NAME,
599 p_msg_name => G_COL_ERROR,
600 p_token1 => G_COL_NAME_TOKEN,
601 p_token1_value => 'inv_org_id',
602 p_token2 => G_PKG_NAME_TOKEN,
603 p_token2_value => G_PKG_NAME);
604 RAISE OKL_API.G_EXCEPTION_ERROR;
605 END IF;
606 x_return_status := G_RET_STS_SUCCESS;
607 END validate_inv_org_id;
608
609
610 -----------------------------------------
611 -- PROCEDURE validate_currency_code
612 -----------------------------------------
613 PROCEDURE validate_currency_code (x_return_status OUT NOCOPY VARCHAR2, p_currency_code IN VARCHAR2) IS
614 BEGIN
615 IF p_currency_code IS NULL THEN
616 OKL_API.set_message(p_app_name => G_APP_NAME,
617 p_msg_name => G_COL_ERROR,
618 p_token1 => G_COL_NAME_TOKEN,
619 p_token1_value => 'currency_code',
620 p_token2 => G_PKG_NAME_TOKEN,
621 p_token2_value => G_PKG_NAME);
622 RAISE OKL_API.G_EXCEPTION_ERROR;
623 END IF;
624 x_return_status := G_RET_STS_SUCCESS;
625 END validate_currency_code;
626
627
628 -----------------------------------------
629 -- PROCEDURE validate_term
630 -----------------------------------------
634 OKL_API.set_message(p_app_name => G_APP_NAME,
631 PROCEDURE validate_term (x_return_status OUT NOCOPY VARCHAR2, p_term IN NUMBER) IS
632 BEGIN
633 IF p_term IS NULL THEN
635 p_msg_name => G_COL_ERROR,
636 p_token1 => G_COL_NAME_TOKEN,
637 p_token1_value => 'term',
638 p_token2 => G_PKG_NAME_TOKEN,
639 p_token2_value => G_PKG_NAME);
640 RAISE OKL_API.G_EXCEPTION_ERROR;
641 END IF;
642 x_return_status := G_RET_STS_SUCCESS;
643 END validate_term;
644
645
646 -----------------------------------------
647 -- PROCEDURE validate_end_of_term_option_id
648 -----------------------------------------
649 PROCEDURE validate_end_of_term_option_id (x_return_status OUT NOCOPY VARCHAR2, p_end_of_term_option_id IN NUMBER) IS
650 BEGIN
651 IF p_end_of_term_option_id IS NULL THEN
652 OKL_API.set_message(p_app_name => G_APP_NAME,
653 p_msg_name => G_COL_ERROR,
654 p_token1 => G_COL_NAME_TOKEN,
655 p_token1_value => 'end_of_term_option_id',
656 p_token2 => G_PKG_NAME_TOKEN,
657 p_token2_value => G_PKG_NAME);
658 RAISE OKL_API.G_EXCEPTION_ERROR;
659 END IF;
660 x_return_status := G_RET_STS_SUCCESS;
661 END validate_end_of_term_option_id;
662
663
664 -----------------------------------------
665 -- PROCEDURE validate_pricing_method
666 -----------------------------------------
667 PROCEDURE validate_pricing_method (x_return_status OUT NOCOPY VARCHAR2, p_pricing_method IN VARCHAR2) IS
668 BEGIN
669 IF p_pricing_method IS NULL THEN
670 OKL_API.set_message(p_app_name => G_APP_NAME,
671 p_msg_name => G_COL_ERROR,
672 p_token1 => G_COL_NAME_TOKEN,
673 p_token1_value => 'pricing_method',
674 p_token2 => G_PKG_NAME_TOKEN,
675 p_token2_value => G_PKG_NAME);
676 RAISE OKL_API.G_EXCEPTION_ERROR;
677 END IF;
678 x_return_status := G_RET_STS_SUCCESS;
679 END validate_pricing_method;
680
681
682 -------------------------------
683 -- FUNCTION validate_attributes
684 -------------------------------
685 FUNCTION validate_attributes (p_qqhv_rec IN qqhv_rec_type) RETURN VARCHAR2 IS
686
687 l_return_status VARCHAR2(1);
688
689 BEGIN
690
691 validate_id (l_return_status, p_qqhv_rec.id);
692 validate_object_version_number (l_return_status, p_qqhv_rec.object_version_number);
693 validate_reference_number (l_return_status, p_qqhv_rec.reference_number);
694 validate_expected_start_date (l_return_status, p_qqhv_rec.expected_start_date);
695 validate_org_id (l_return_status, p_qqhv_rec.org_id);
696 validate_inv_org_id (l_return_status, p_qqhv_rec.inv_org_id);
697 validate_currency_code (l_return_status, p_qqhv_rec.currency_code);
698 validate_term (l_return_status, p_qqhv_rec.term);
699 validate_end_of_term_option_id (l_return_status, p_qqhv_rec.end_of_term_option_id);
700 validate_pricing_method (l_return_status, p_qqhv_rec.pricing_method);
701 validate_sts_code(l_return_status,p_qqhv_rec.sts_code);
702 RETURN l_return_status;
703
704 END validate_attributes;
705
706 ----------------------------
707 -- PROCEDURE validate_record
708 ----------------------------
709 FUNCTION validate_record (p_qqhv_rec IN qqhv_rec_type) RETURN VARCHAR2 IS
710 l_return_status VARCHAR2(1);
711 BEGIN
712 RETURN G_RET_STS_SUCCESS;
713 END validate_record;
714
715
716 -----------------------------
717 -- PROECDURE migrate (V -> B)
718 -----------------------------
719 PROCEDURE migrate (p_from IN qqhv_rec_type, p_to IN OUT NOCOPY qqh_rec_type) IS
720
721 BEGIN
722
723 p_to.id := p_from.id;
724 p_to.object_version_number := p_from.object_version_number;
725 p_to.attribute_category := p_from.attribute_category;
726 p_to.attribute1 := p_from.attribute1;
727 p_to.attribute2 := p_from.attribute2;
728 p_to.attribute3 := p_from.attribute3;
729 p_to.attribute4 := p_from.attribute4;
730 p_to.attribute5 := p_from.attribute5;
731 p_to.attribute6 := p_from.attribute6;
732 p_to.attribute7 := p_from.attribute7;
733 p_to.attribute8 := p_from.attribute8;
734 p_to.attribute9 := p_from.attribute9;
735 p_to.attribute10 := p_from.attribute10;
736 p_to.attribute11 := p_from.attribute11;
737 p_to.attribute12 := p_from.attribute12;
738 p_to.attribute13 := p_from.attribute13;
739 p_to.attribute14 := p_from.attribute14;
740 p_to.attribute15 := p_from.attribute15;
741 p_to.reference_number := p_from.reference_number;
742 p_to.expected_start_date := p_from.expected_start_date;
746 p_to.term := p_from.term;
743 p_to.org_id := p_from.org_id;
744 p_to.inv_org_id := p_from.inv_org_id;
745 p_to.currency_code := p_from.currency_code;
747 p_to.end_of_term_option_id := p_from.end_of_term_option_id;
748 p_to.pricing_method := p_from.pricing_method;
749 p_to.lease_opportunity_id := p_from.lease_opportunity_id;
750 p_to.originating_vendor_id := p_from.originating_vendor_id;
751 p_to.program_agreement_id := p_from.program_agreement_id;
752 p_to.sales_rep_id := p_from.sales_rep_id;
753 p_to.sales_territory_id := p_from.sales_territory_id;
754 p_to.structured_pricing := p_from.structured_pricing;
755 p_to.line_level_pricing := p_from.line_level_pricing;
756 p_to.rate_template_id := p_from.rate_template_id;
757 p_to.rate_card_id := p_from.rate_card_id;
758 p_to.lease_rate_factor := p_from.lease_rate_factor;
759 p_to.target_rate_type := p_from.target_rate_type;
760 p_to.target_rate := p_from.target_rate;
761 p_to.target_amount := p_from.target_amount;
762 p_to.target_frequency := p_from.target_frequency;
763 p_to.target_arrears := p_from.target_arrears;
764 p_to.target_periods := p_from.target_periods;
765 p_to.iir := p_from.iir;
766 p_to.sub_iir := p_from.sub_iir;
767 p_to.booking_yield := p_from.booking_yield;
768 p_to.sub_booking_yield := p_from.sub_booking_yield;
769 p_to.pirr := p_from.pirr;
770 p_to.sub_pirr := p_from.sub_pirr;
771 p_to.airr := p_from.airr;
772 p_to.sub_airr := p_from.sub_airr;
773 --abhsaxen - added - start
774 p_to.sts_code := p_from.sts_code;
775 --abhsaxen - added - end
776
777 END migrate;
778
779
780 -----------------------------
781 -- PROCEDURE migrate (V -> TL)
782 -----------------------------
783 PROCEDURE migrate (p_from IN qqhv_rec_type, p_to IN OUT NOCOPY qqhtl_rec_type) IS
784 BEGIN
785 p_to.id := p_from.id;
786 p_to.short_description := p_from.short_description;
787 p_to.description := p_from.description;
788 p_to.comments := p_from.comments;
789 END migrate;
790
791
792 ---------------------------
793 -- PROCEDURE insert_row (B)
794 ---------------------------
795 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_qqh_rec IN qqh_rec_type) IS
796
797 l_prog_name VARCHAR2(61);
798
799 BEGIN
800
801 l_prog_name := G_PKG_NAME||'.insert_row (B)';
802
803 INSERT INTO okl_quick_quotes_b (
804 id
805 ,object_version_number
806 ,attribute_category
807 ,attribute1
808 ,attribute2
809 ,attribute3
810 ,attribute4
811 ,attribute5
812 ,attribute6
813 ,attribute7
814 ,attribute8
815 ,attribute9
816 ,attribute10
817 ,attribute11
818 ,attribute12
819 ,attribute13
820 ,attribute14
821 ,attribute15
822 ,created_by
823 ,creation_date
824 ,last_updated_by
825 ,last_update_date
826 ,last_update_login
827 ,reference_number
828 ,expected_start_date
829 ,org_id
830 ,inv_org_id
831 ,currency_code
832 ,term
833 ,end_of_term_option_id
834 ,pricing_method
835 ,lease_opportunity_id
836 ,originating_vendor_id
837 ,program_agreement_id
838 ,sales_rep_id
839 ,sales_territory_id
840 ,structured_pricing
841 ,line_level_pricing
842 ,rate_template_id
843 ,rate_card_id
844 ,lease_rate_factor
845 ,target_rate_type
846 ,target_rate
847 ,target_amount
848 ,target_frequency
849 ,target_arrears
850 ,target_periods
851 ,iir
852 ,sub_iir
853 ,booking_yield
854 ,sub_booking_yield
855 ,pirr
856 ,sub_pirr
857 ,airr
858 ,sub_airr
859 -- abhsaxen - added - start
860 ,sts_code
861 -- abhsaxen - added - end
862 )
863 VALUES
864 (
865 p_qqh_rec.id
866 ,p_qqh_rec.object_version_number
867 ,p_qqh_rec.attribute_category
868 ,p_qqh_rec.attribute1
869 ,p_qqh_rec.attribute2
870 ,p_qqh_rec.attribute3
871 ,p_qqh_rec.attribute4
872 ,p_qqh_rec.attribute5
873 ,p_qqh_rec.attribute6
874 ,p_qqh_rec.attribute7
875 ,p_qqh_rec.attribute8
876 ,p_qqh_rec.attribute9
877 ,p_qqh_rec.attribute10
878 ,p_qqh_rec.attribute11
879 ,p_qqh_rec.attribute12
880 ,p_qqh_rec.attribute13
881 ,p_qqh_rec.attribute14
882 ,p_qqh_rec.attribute15
883 ,G_USER_ID
884 ,SYSDATE
888 ,p_qqh_rec.reference_number
885 ,G_USER_ID
886 ,SYSDATE
887 ,G_LOGIN_ID
889 ,p_qqh_rec.expected_start_date
890 ,p_qqh_rec.org_id
891 ,p_qqh_rec.inv_org_id
892 ,p_qqh_rec.currency_code
893 ,p_qqh_rec.term
894 ,p_qqh_rec.end_of_term_option_id
895 ,p_qqh_rec.pricing_method
896 ,p_qqh_rec.lease_opportunity_id
897 ,p_qqh_rec.originating_vendor_id
898 ,p_qqh_rec.program_agreement_id
899 ,p_qqh_rec.sales_rep_id
900 ,p_qqh_rec.sales_territory_id
901 ,p_qqh_rec.structured_pricing
902 ,p_qqh_rec.line_level_pricing
903 ,p_qqh_rec.rate_template_id
904 ,p_qqh_rec.rate_card_id
905 ,p_qqh_rec.lease_rate_factor
906 ,p_qqh_rec.target_rate_type
907 ,p_qqh_rec.target_rate
908 ,p_qqh_rec.target_amount
909 ,p_qqh_rec.target_frequency
910 ,p_qqh_rec.target_arrears
911 ,p_qqh_rec.target_periods
912 ,p_qqh_rec.iir
913 ,p_qqh_rec.sub_iir
914 ,p_qqh_rec.booking_yield
915 ,p_qqh_rec.sub_booking_yield
916 ,p_qqh_rec.pirr
917 ,p_qqh_rec.sub_pirr
918 ,p_qqh_rec.airr
919 ,p_qqh_rec.sub_airr
920 -- abhsaxen - added - start
921 ,p_qqh_rec.sts_code
922 -- abhsaxen - added - end
923
924 );
925
926 x_return_status := G_RET_STS_SUCCESS;
927
928 EXCEPTION
929
930 WHEN OTHERS THEN
931
932 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
933 p_msg_name => G_DB_ERROR,
934 p_token1 => G_PROG_NAME_TOKEN,
935 p_token1_value => l_prog_name,
936 p_token2 => G_SQLCODE_TOKEN,
937 p_token2_value => sqlcode,
938 p_token3 => G_SQLERRM_TOKEN,
939 p_token3_value => sqlerrm);
940
941 x_return_status := G_RET_STS_UNEXP_ERROR;
942
943 END insert_row;
944
945
946 ----------------------------
947 -- PROCEDURE insert_row (TL)
948 ----------------------------
949 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_qqhtl_rec IN qqhtl_rec_type) IS
950
951 CURSOR get_languages IS
952 SELECT language_code
953 FROM FND_LANGUAGES
954 WHERE INSTALLED_FLAG IN ('I', 'B');
955
956 l_sfwt_flag VARCHAR2(1);
957
958 l_prog_name VARCHAR2(61);
959
960 BEGIN
961
962 l_prog_name := G_PKG_NAME||'.insert_row (TL)';
963
964 FOR l_lang_rec IN get_languages LOOP
965
966 IF l_lang_rec.language_code = USERENV('LANG') THEN
967 l_sfwt_flag := 'N';
968 ELSE
969 l_sfwt_flag := 'Y';
970 END IF;
971
972 INSERT INTO OKL_QUICK_QUOTES_TL (
973 id
974 ,language
975 ,source_lang
976 ,sfwt_flag
977 ,created_by
978 ,creation_date
979 ,last_updated_by
980 ,last_update_date
981 ,last_update_login
982 ,short_description
983 ,description
984 ,comments)
985 VALUES (
986 p_qqhtl_rec.id
987 ,l_lang_rec.language_code
988 ,USERENV('LANG')
989 ,l_sfwt_flag
990 ,G_USER_ID
991 ,SYSDATE
992 ,G_USER_ID
993 ,SYSDATE
994 ,G_LOGIN_ID
995 ,p_qqhtl_rec.short_description
996 ,p_qqhtl_rec.description
997 ,p_qqhtl_rec.comments);
998
999 END LOOP;
1000
1001 x_return_status := G_RET_STS_SUCCESS;
1002
1003 EXCEPTION
1004
1005 WHEN OTHERS THEN
1006
1007 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1008 p_msg_name => G_DB_ERROR,
1009 p_token1 => G_PROG_NAME_TOKEN,
1010 p_token1_value => l_prog_name,
1011 p_token2 => G_SQLCODE_TOKEN,
1012 p_token2_value => sqlcode,
1013 p_token3 => G_SQLERRM_TOKEN,
1014 p_token3_value => sqlerrm);
1015
1016 x_return_status := G_RET_STS_UNEXP_ERROR;
1017
1018 END insert_row;
1019
1020
1021 ---------------------------
1022 -- PROCEDURE insert_row (V)
1023 ---------------------------
1024 PROCEDURE insert_row (
1025 x_return_status OUT NOCOPY VARCHAR2,
1026 p_qqhv_rec IN qqhv_rec_type,
1027 x_qqhv_rec OUT NOCOPY qqhv_rec_type) IS
1028
1029 l_return_status VARCHAR2(1);
1030
1031 l_qqhv_rec qqhv_rec_type;
1032 l_qqh_rec qqh_rec_type;
1033 l_qqhtl_rec qqhtl_rec_type;
1034
1035 l_prog_name VARCHAR2(61);
1036
1037 BEGIN
1038
1039 l_prog_name := G_PKG_NAME||'.insert_row (V)';
1040
1041 l_qqhv_rec := null_out_defaults (p_qqhv_rec);
1042
1046
1043 SELECT okl_qqh_seq.nextval INTO l_qqhv_rec.ID FROM DUAL;
1044
1045 l_qqhv_rec.OBJECT_VERSION_NUMBER := 1;
1047 l_return_status := validate_attributes(l_qqhv_rec);
1048
1049 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1050 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1051 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1052 RAISE OKL_API.G_EXCEPTION_ERROR;
1053 END IF;
1054 --Bug 7022258-Added by kkorrapo
1055 IF (okl_util.validate_seq_num('OKL_QQH_REF_SEQ','OKL_QUICK_QUOTES_B','REFERENCE_NUMBER',l_qqhv_rec.reference_number) = 'N') THEN
1056 RAISE okl_api.g_exception_error;
1057 END IF;
1058 --Bug 7022258--Addition end
1059
1060 l_return_status := validate_record(l_qqhv_rec);
1061
1062 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1063 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1064 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1065 RAISE OKL_API.G_EXCEPTION_ERROR;
1066 END IF;
1067
1068 migrate (l_qqhv_rec, l_qqh_rec);
1069 migrate (l_qqhv_rec, l_qqhtl_rec);
1070
1071 insert_row (x_return_status => l_return_status, p_qqh_rec => l_qqh_rec);
1072
1073 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1074 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1075 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1076 RAISE OKL_API.G_EXCEPTION_ERROR;
1077 END IF;
1078
1079 insert_row (x_return_status => l_return_status, p_qqhtl_rec => l_qqhtl_rec);
1080
1081 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1082 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1083 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1084 RAISE OKL_API.G_EXCEPTION_ERROR;
1085 END IF;
1086
1087 x_qqhv_rec := l_qqhv_rec;
1088 x_return_status := l_return_status;
1089
1090 EXCEPTION
1091
1092 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1093
1094 x_return_status := G_RET_STS_ERROR;
1095
1096 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1097
1098 x_return_status := G_RET_STS_UNEXP_ERROR;
1099
1100 WHEN OTHERS THEN
1101
1102 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1103 p_msg_name => G_DB_ERROR,
1104 p_token1 => G_PROG_NAME_TOKEN,
1105 p_token1_value => l_prog_name,
1106 p_token2 => G_SQLCODE_TOKEN,
1107 p_token2_value => sqlcode,
1108 p_token3 => G_SQLERRM_TOKEN,
1109 p_token3_value => sqlerrm);
1110
1111 x_return_status := G_RET_STS_UNEXP_ERROR;
1112
1113 END insert_row;
1114
1115
1116 -----------------------------
1117 -- PROCEDURE insert_row (REC)
1118 -----------------------------
1119 PROCEDURE insert_row(
1120 p_api_version IN NUMBER,
1121 p_init_msg_list IN VARCHAR2,
1122 x_return_status OUT NOCOPY VARCHAR2,
1123 x_msg_count OUT NOCOPY NUMBER,
1124 x_msg_data OUT NOCOPY VARCHAR2,
1125 p_qqhv_rec IN qqhv_rec_type,
1126 x_qqhv_rec OUT NOCOPY qqhv_rec_type) IS
1127
1128 l_return_status VARCHAR2(1);
1129
1130 l_prog_name VARCHAR2(61);
1131
1132 BEGIN
1133
1134 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
1135
1136 IF p_init_msg_list = G_TRUE THEN
1137 FND_MSG_PUB.initialize;
1138 END IF;
1139
1140 insert_row (x_return_status => l_return_status,
1141 p_qqhv_rec => p_qqhv_rec,
1142 x_qqhv_rec => x_qqhv_rec);
1143
1144 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1145 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1146 ELSIF l_return_status = G_RET_STS_ERROR THEN
1147 RAISE OKL_API.G_EXCEPTION_ERROR;
1148 END IF;
1149
1150 x_return_status := l_return_status;
1151
1152 EXCEPTION
1153
1154 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1155
1156 x_return_status := G_RET_STS_ERROR;
1157
1158 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1159
1160 x_return_status := G_RET_STS_UNEXP_ERROR;
1161
1162 WHEN OTHERS THEN
1163
1164 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1165 p_msg_name => G_DB_ERROR,
1166 p_token1 => G_PROG_NAME_TOKEN,
1167 p_token1_value => l_prog_name,
1168 p_token2 => G_SQLCODE_TOKEN,
1169 p_token2_value => sqlcode,
1170 p_token3 => G_SQLERRM_TOKEN,
1171 p_token3_value => sqlerrm);
1172
1173 x_return_status := G_RET_STS_UNEXP_ERROR;
1174
1175 END insert_row;
1176
1177
1178 -----------------------------
1179 -- PROCEDURE insert_row (TBL)
1180 -----------------------------
1181 PROCEDURE insert_row(
1182 p_api_version IN NUMBER,
1183 p_init_msg_list IN VARCHAR2,
1184 x_return_status OUT NOCOPY VARCHAR2,
1188 x_qqhv_tbl OUT NOCOPY qqhv_tbl_type) IS
1185 x_msg_count OUT NOCOPY NUMBER,
1186 x_msg_data OUT NOCOPY VARCHAR2,
1187 p_qqhv_tbl IN qqhv_tbl_type,
1189
1190 l_return_status VARCHAR2(1);
1191 i BINARY_INTEGER;
1192
1193 l_prog_name VARCHAR2(61);
1194
1195 BEGIN
1196
1197 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
1198
1199 IF p_init_msg_list = G_TRUE THEN
1200 FND_MSG_PUB.initialize;
1201 END IF;
1202
1203 IF (p_qqhv_tbl.COUNT > 0) THEN
1204 i := p_qqhv_tbl.FIRST;
1205 LOOP
1206 IF p_qqhv_tbl.EXISTS(i) THEN
1207
1208 insert_row (x_return_status => l_return_status,
1209 p_qqhv_rec => p_qqhv_tbl(i),
1210 x_qqhv_rec => x_qqhv_tbl(i));
1211
1212 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1213 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1214 ELSIF l_return_status = G_RET_STS_ERROR THEN
1215 RAISE OKL_API.G_EXCEPTION_ERROR;
1216 END IF;
1217
1218 EXIT WHEN (i = p_qqhv_tbl.LAST);
1219 i := p_qqhv_tbl.NEXT(i);
1220
1221 END IF;
1222
1223 END LOOP;
1224
1225 ELSE
1226
1227 l_return_status := G_RET_STS_SUCCESS;
1228
1229 END IF;
1230
1231 x_return_status := l_return_status;
1232
1233 EXCEPTION
1234
1235 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1236
1237 x_return_status := G_RET_STS_ERROR;
1238
1239 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1240
1241 x_return_status := G_RET_STS_UNEXP_ERROR;
1242
1243 WHEN OTHERS THEN
1244
1245 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1246 p_msg_name => G_DB_ERROR,
1247 p_token1 => G_PROG_NAME_TOKEN,
1248 p_token1_value => l_prog_name,
1249 p_token2 => G_SQLCODE_TOKEN,
1250 p_token2_value => sqlcode,
1251 p_token3 => G_SQLERRM_TOKEN,
1252 p_token3_value => sqlerrm);
1253
1254 x_return_status := G_RET_STS_UNEXP_ERROR;
1255
1256 END insert_row;
1257
1258
1259 ---------------------
1260 -- PROCEDURE lock_row
1261 ---------------------
1262 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_qqh_rec IN qqh_rec_type) IS
1263
1264 E_Resource_Busy EXCEPTION;
1265
1266 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
1267
1268 CURSOR lock_csr IS
1269 SELECT OBJECT_VERSION_NUMBER
1270 FROM OKL_QUICK_QUOTES_B
1271 WHERE ID = p_qqh_rec.id
1272 AND OBJECT_VERSION_NUMBER = p_qqh_rec.object_version_number
1273 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1274
1275 CURSOR lchk_csr IS
1276 SELECT OBJECT_VERSION_NUMBER
1277 FROM OKL_QUICK_QUOTES_B
1278 WHERE ID = p_qqh_rec.id;
1279
1280 l_object_version_number NUMBER;
1281 lc_object_version_number NUMBER;
1282
1283 l_prog_name VARCHAR2(61);
1284
1285 BEGIN
1286
1287 l_prog_name := G_PKG_NAME||'.lock_row';
1288
1289 BEGIN
1290 OPEN lock_csr;
1291 FETCH lock_csr INTO l_object_version_number;
1292 CLOSE lock_csr;
1293
1294 EXCEPTION
1295 WHEN E_Resource_Busy THEN
1296
1297 IF (lock_csr%ISOPEN) THEN
1298 CLOSE lock_csr;
1299 END IF;
1300 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1301 p_msg_name => G_OVN_ERROR2,
1302 p_token1 => G_PROG_NAME_TOKEN,
1303 p_token1_value => l_prog_name);
1304 RAISE OKL_API.G_EXCEPTION_ERROR;
1305 END;
1306
1307 IF l_object_version_number IS NULL THEN
1308
1309 OPEN lchk_csr;
1310 FETCH lchk_csr INTO lc_object_version_number;
1311 CLOSE lchk_csr;
1312
1313 IF lc_object_version_number IS NULL THEN
1314
1315 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1316 p_msg_name => G_OVN_ERROR3,
1317 p_token1 => G_PROG_NAME_TOKEN,
1318 p_token1_value => l_prog_name);
1319
1320 ELSIF lc_object_version_number <> p_qqh_rec.object_version_number THEN
1321
1322 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1323 p_msg_name => G_OVN_ERROR,
1324 p_token1 => G_PROG_NAME_TOKEN,
1325 p_token1_value => l_prog_name);
1326
1327 END IF;
1328
1329 RAISE OKL_API.G_EXCEPTION_ERROR;
1330
1331 END IF;
1332
1333 x_return_status := G_RET_STS_SUCCESS;
1334
1335 EXCEPTION
1336
1337 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1338
1342
1339 x_return_status := G_RET_STS_ERROR;
1340
1341 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1343 x_return_status := G_RET_STS_UNEXP_ERROR;
1344
1345 WHEN OTHERS THEN
1346
1347 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1348 p_msg_name => G_DB_ERROR,
1349 p_token1 => G_PROG_NAME_TOKEN,
1350 p_token1_value => l_prog_name,
1351 p_token2 => G_SQLCODE_TOKEN,
1352 p_token2_value => sqlcode,
1353 p_token3 => G_SQLERRM_TOKEN,
1354 p_token3_value => sqlerrm);
1355
1356 x_return_status := G_RET_STS_UNEXP_ERROR;
1357
1358 END lock_row;
1359
1360
1361 ---------------------------
1362 -- PROCEDURE update_row (B)
1363 ---------------------------
1364 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_qqh_rec IN qqh_rec_type) IS
1365
1366 l_return_status VARCHAR2(1);
1367
1368 l_prog_name VARCHAR2(61);
1369
1370 BEGIN
1371
1372 l_prog_name := G_PKG_NAME||'.update_row (B)';
1373
1374 lock_row (x_return_status => l_return_status, p_qqh_rec => p_qqh_rec);
1375
1376 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1377 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1378 ELSIF l_return_status = G_RET_STS_ERROR THEN
1379 RAISE OKL_API.G_EXCEPTION_ERROR;
1380 END IF;
1381
1382 UPDATE okl_quick_quotes_b
1383 SET
1384 object_version_number = p_qqh_rec.object_version_number+1
1385 ,attribute_category = p_qqh_rec.attribute_category
1386 ,attribute1 = p_qqh_rec.attribute1
1387 ,attribute2 = p_qqh_rec.attribute2
1388 ,attribute3 = p_qqh_rec.attribute3
1389 ,attribute4 = p_qqh_rec.attribute4
1390 ,attribute5 = p_qqh_rec.attribute5
1391 ,attribute6 = p_qqh_rec.attribute6
1392 ,attribute7 = p_qqh_rec.attribute7
1393 ,attribute8 = p_qqh_rec.attribute8
1394 ,attribute9 = p_qqh_rec.attribute9
1395 ,attribute10 = p_qqh_rec.attribute10
1396 ,attribute11 = p_qqh_rec.attribute11
1397 ,attribute12 = p_qqh_rec.attribute12
1398 ,attribute13 = p_qqh_rec.attribute13
1399 ,attribute14 = p_qqh_rec.attribute14
1400 ,attribute15 = p_qqh_rec.attribute15
1401 ,reference_number = p_qqh_rec.reference_number
1402 ,expected_start_date = p_qqh_rec.expected_start_date
1403 ,org_id = p_qqh_rec.org_id
1404 ,inv_org_id = p_qqh_rec.inv_org_id
1405 ,currency_code = p_qqh_rec.currency_code
1406 ,term = p_qqh_rec.term
1407 ,end_of_term_option_id = p_qqh_rec.end_of_term_option_id
1408 ,pricing_method = p_qqh_rec.pricing_method
1409 ,lease_opportunity_id = p_qqh_rec.lease_opportunity_id
1410 ,originating_vendor_id = p_qqh_rec.originating_vendor_id
1411 ,program_agreement_id = p_qqh_rec.program_agreement_id
1412 ,sales_rep_id = p_qqh_rec.sales_rep_id
1413 ,sales_territory_id = p_qqh_rec.sales_territory_id
1414 ,structured_pricing = p_qqh_rec.structured_pricing
1415 ,line_level_pricing = p_qqh_rec.line_level_pricing
1416 ,rate_template_id = p_qqh_rec.rate_template_id
1417 ,rate_card_id = p_qqh_rec.rate_card_id
1418 ,lease_rate_factor = p_qqh_rec.lease_rate_factor
1419 ,target_rate_type = p_qqh_rec.target_rate_type
1420 ,target_rate = p_qqh_rec.target_rate
1421 ,target_amount = p_qqh_rec.target_amount
1422 ,target_frequency = p_qqh_rec.target_frequency
1423 ,target_arrears = p_qqh_rec.target_arrears
1424 ,target_periods = p_qqh_rec.target_periods
1425 ,iir = p_qqh_rec.iir
1426 ,sub_iir = p_qqh_rec.sub_iir
1427 ,booking_yield = p_qqh_rec.booking_yield
1428 ,sub_booking_yield = p_qqh_rec.sub_booking_yield
1429 ,pirr = p_qqh_rec.pirr
1430 ,sub_pirr = p_qqh_rec.sub_pirr
1431 ,airr = p_qqh_rec.airr
1432 ,sub_airr = p_qqh_rec.sub_airr
1433 -- abhsaxen - added - start
1434 ,sts_code = p_qqh_rec.sts_code
1435 -- abhsaxen - added - end
1436 WHERE id = p_qqh_rec.id;
1437
1438 x_return_status := l_return_status;
1439
1440 EXCEPTION
1441
1442 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1443 x_return_status := G_RET_STS_ERROR;
1444
1445 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1446 x_return_status := G_RET_STS_UNEXP_ERROR;
1447
1448 WHEN OTHERS THEN
1449 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1450 p_msg_name => G_DB_ERROR,
1451 p_token1 => G_PROG_NAME_TOKEN,
1452 p_token1_value => l_prog_name,
1453 p_token2 => G_SQLCODE_TOKEN,
1454 p_token2_value => sqlcode,
1455 p_token3 => G_SQLERRM_TOKEN,
1456 p_token3_value => sqlerrm);
1457
1458 x_return_status := G_RET_STS_UNEXP_ERROR;
1459
1460 END update_row;
1461
1462
1463 ----------------------------
1464 -- PROCEDURE update_row (TL)
1465 ----------------------------
1466 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_qqhtl_rec IN qqhtl_rec_type) IS
1467
1468 l_prog_name VARCHAR2(61);
1469
1470 BEGIN
1471
1472 l_prog_name := G_PKG_NAME||'.update_row (TL)';
1476 source_lang = USERENV('LANG')
1473
1474 UPDATE OKL_QUICK_QUOTES_TL
1475 SET
1477 ,sfwt_flag = 'Y'
1478 ,last_updated_by = G_USER_ID
1479 ,last_update_date = SYSDATE
1480 ,last_update_login = G_LOGIN_ID
1481 ,short_description = p_qqhtl_rec.short_description
1482 ,description = p_qqhtl_rec.description
1483 ,comments = p_qqhtl_rec.comments
1484 WHERE ID = p_qqhtl_rec.id;
1485
1486 UPDATE OKL_QUICK_QUOTES_TL
1487 SET SFWT_FLAG = 'N'
1488 WHERE ID = p_qqhtl_rec.id
1489 AND SOURCE_LANG = LANGUAGE;
1490
1491 x_return_status := G_RET_STS_SUCCESS;
1492
1493 EXCEPTION
1494
1495 WHEN OTHERS THEN
1496
1497 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1498 p_msg_name => G_DB_ERROR,
1499 p_token1 => G_PROG_NAME_TOKEN,
1500 p_token1_value => l_prog_name,
1501 p_token2 => G_SQLCODE_TOKEN,
1502 p_token2_value => sqlcode,
1503 p_token3 => G_SQLERRM_TOKEN,
1504 p_token3_value => sqlerrm);
1505
1506 x_return_status := G_RET_STS_UNEXP_ERROR;
1507
1508 END update_row;
1509
1510
1511 ---------------------------
1512 -- PROCEDURE update_row (V)
1513 ---------------------------
1514 PROCEDURE update_row (
1515 x_return_status OUT NOCOPY VARCHAR2,
1516 p_qqhv_rec IN qqhv_rec_type,
1517 x_qqhv_rec OUT NOCOPY qqhv_rec_type) IS
1518
1519 l_prog_name VARCHAR2(61);
1520
1521 l_return_status VARCHAR2(1);
1522 l_qqhv_rec qqhv_rec_type;
1523 l_qqh_rec qqh_rec_type;
1524 l_qqhtl_rec qqhtl_rec_type;
1525
1526 ----------------------
1527 -- populate_new_record
1528 ----------------------
1529 FUNCTION populate_new_record (p_qqhv_rec IN qqhv_rec_type,
1530 x_qqhv_rec OUT NOCOPY qqhv_rec_type) RETURN VARCHAR2 IS
1531
1532 l_prog_name VARCHAR2(61);
1533 l_return_status VARCHAR2(1);
1534 l_db_qqhv_rec qqhv_rec_type;
1535
1536 BEGIN
1537
1538 l_prog_name := G_PKG_NAME||'.populate_new_record';
1539
1540 x_qqhv_rec := p_qqhv_rec;
1541 l_db_qqhv_rec := get_rec (p_qqhv_rec.id, l_return_status);
1542
1543 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1544 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1545 ELSIF l_return_status = G_RET_STS_ERROR THEN
1546 RAISE OKL_API.G_EXCEPTION_ERROR;
1547 END IF;
1548
1549 -- smadhava - Modified - G_MISS compliance - Start
1550 IF x_qqhv_rec.attribute_category IS NULL THEN
1551 x_qqhv_rec.attribute_category := l_db_qqhv_rec.attribute_category;
1552 ELSIF x_qqhv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1553 x_qqhv_rec.attribute_category := null;
1554 END IF;
1555
1556 IF x_qqhv_rec.attribute1 IS NULL THEN
1557 x_qqhv_rec.attribute1 := l_db_qqhv_rec.attribute1;
1558 ELSIF x_qqhv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1559 x_qqhv_rec.attribute1 := null;
1560 END IF;
1561
1562 IF x_qqhv_rec.attribute2 IS NULL THEN
1563 x_qqhv_rec.attribute2 := l_db_qqhv_rec.attribute2;
1564 ELSIF x_qqhv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1565 x_qqhv_rec.attribute2 := null;
1566 END IF;
1567
1568 IF x_qqhv_rec.attribute3 IS NULL THEN
1569 x_qqhv_rec.attribute3 := l_db_qqhv_rec.attribute3;
1570 ELSIF x_qqhv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1571 x_qqhv_rec.attribute3 := null;
1572 END IF;
1573
1574 IF x_qqhv_rec.attribute4 IS NULL THEN
1575 x_qqhv_rec.attribute4 := l_db_qqhv_rec.attribute4;
1576 ELSIF x_qqhv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1577 x_qqhv_rec.attribute4 := null;
1578 END IF;
1579
1580 IF x_qqhv_rec.attribute5 IS NULL THEN
1581 x_qqhv_rec.attribute5 := l_db_qqhv_rec.attribute5;
1582 ELSIF x_qqhv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1583 x_qqhv_rec.attribute5 := null;
1584 END IF;
1585
1586 IF x_qqhv_rec.attribute6 IS NULL THEN
1587 x_qqhv_rec.attribute6 := l_db_qqhv_rec.attribute6;
1588 ELSIF x_qqhv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1589 x_qqhv_rec.attribute6 := null;
1590 END IF;
1591
1592 IF x_qqhv_rec.attribute7 IS NULL THEN
1593 x_qqhv_rec.attribute7 := l_db_qqhv_rec.attribute7;
1594 ELSIF x_qqhv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1595 x_qqhv_rec.attribute7 := null;
1596 END IF;
1597
1598 IF x_qqhv_rec.attribute8 IS NULL THEN
1599 x_qqhv_rec.attribute8 := l_db_qqhv_rec.attribute8;
1600 ELSIF x_qqhv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1601 x_qqhv_rec.attribute8 := null;
1602 END IF;
1603
1604 IF x_qqhv_rec.attribute9 IS NULL THEN
1605 x_qqhv_rec.attribute9 := l_db_qqhv_rec.attribute9;
1606 ELSIF x_qqhv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1607 x_qqhv_rec.attribute9 := null;
1608 END IF;
1609
1610 IF x_qqhv_rec.attribute10 IS NULL THEN
1614 END IF;
1611 x_qqhv_rec.attribute10 := l_db_qqhv_rec.attribute10;
1612 ELSIF x_qqhv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1613 x_qqhv_rec.attribute10 := null;
1615
1616 IF x_qqhv_rec.attribute11 IS NULL THEN
1617 x_qqhv_rec.attribute11 := l_db_qqhv_rec.attribute11;
1618 ELSIF x_qqhv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1619 x_qqhv_rec.attribute11 := null;
1620 END IF;
1621
1622 IF x_qqhv_rec.attribute12 IS NULL THEN
1623 x_qqhv_rec.attribute12 := l_db_qqhv_rec.attribute12;
1624 ELSIF x_qqhv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1625 x_qqhv_rec.attribute12 := null;
1626 END IF;
1627
1628 IF x_qqhv_rec.attribute13 IS NULL THEN
1629 x_qqhv_rec.attribute13 := l_db_qqhv_rec.attribute13;
1630 ELSIF x_qqhv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1631 x_qqhv_rec.attribute13 := null;
1632 END IF;
1633
1634 IF x_qqhv_rec.attribute14 IS NULL THEN
1635 x_qqhv_rec.attribute14 := l_db_qqhv_rec.attribute14;
1636 ELSIF x_qqhv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1637 x_qqhv_rec.attribute14 := null;
1638 END IF;
1639
1640 IF x_qqhv_rec.attribute15 IS NULL THEN
1641 x_qqhv_rec.attribute15 := l_db_qqhv_rec.attribute15;
1642 ELSIF x_qqhv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1643 x_qqhv_rec.attribute15 := null;
1644 END IF;
1645
1646 IF x_qqhv_rec.reference_number IS NULL THEN
1647 x_qqhv_rec.reference_number := l_db_qqhv_rec.reference_number;
1648 ELSIF x_qqhv_rec.reference_number = FND_API.G_MISS_CHAR THEN
1649 x_qqhv_rec.reference_number := null;
1650 END IF;
1651
1652 IF x_qqhv_rec.expected_start_date IS NULL THEN
1653 x_qqhv_rec.expected_start_date := l_db_qqhv_rec.expected_start_date;
1654 ELSIF x_qqhv_rec.expected_start_date = FND_API.G_MISS_DATE THEN
1655 x_qqhv_rec.expected_start_date := null;
1656 END IF;
1657
1658 IF x_qqhv_rec.org_id IS NULL THEN
1659 x_qqhv_rec.org_id := l_db_qqhv_rec.org_id;
1660 ELSIF x_qqhv_rec.org_id = FND_API.G_MISS_NUM THEN
1661 x_qqhv_rec.org_id := null;
1662 END IF;
1663
1664 IF x_qqhv_rec.inv_org_id IS NULL THEN
1665 x_qqhv_rec.inv_org_id := l_db_qqhv_rec.inv_org_id;
1666 ELSIF x_qqhv_rec.inv_org_id = FND_API.G_MISS_NUM THEN
1667 x_qqhv_rec.inv_org_id := null;
1668 END IF;
1669
1670 IF x_qqhv_rec.currency_code IS NULL THEN
1671 x_qqhv_rec.currency_code := l_db_qqhv_rec.currency_code;
1672 ELSIF x_qqhv_rec.currency_code = FND_API.G_MISS_CHAR THEN
1673 x_qqhv_rec.currency_code := null;
1674 END IF;
1675
1676 IF x_qqhv_rec.term IS NULL THEN
1677 x_qqhv_rec.term := l_db_qqhv_rec.term;
1678 ELSIF x_qqhv_rec.term = FND_API.G_MISS_NUM THEN
1679 x_qqhv_rec.term := null;
1680 END IF;
1681
1682 IF x_qqhv_rec.end_of_term_option_id IS NULL THEN
1683 x_qqhv_rec.end_of_term_option_id := l_db_qqhv_rec.end_of_term_option_id;
1684 ELSIF x_qqhv_rec.end_of_term_option_id = FND_API.G_MISS_NUM THEN
1685 x_qqhv_rec.end_of_term_option_id := null;
1686 END IF;
1687
1688 IF x_qqhv_rec.pricing_method IS NULL THEN
1689 x_qqhv_rec.pricing_method := l_db_qqhv_rec.pricing_method;
1690 ELSIF x_qqhv_rec.pricing_method = FND_API.G_MISS_CHAR THEN
1691 x_qqhv_rec.pricing_method := null;
1692 END IF;
1693
1694 IF x_qqhv_rec.lease_opportunity_id IS NULL THEN
1695 x_qqhv_rec.lease_opportunity_id := l_db_qqhv_rec.lease_opportunity_id;
1696 ELSIF x_qqhv_rec.lease_opportunity_id = FND_API.G_MISS_NUM THEN
1697 x_qqhv_rec.lease_opportunity_id := null;
1698 END IF;
1699
1700 IF x_qqhv_rec.originating_vendor_id IS NULL THEN
1701 x_qqhv_rec.originating_vendor_id := l_db_qqhv_rec.originating_vendor_id;
1702 ELSIF x_qqhv_rec.originating_vendor_id = FND_API.G_MISS_NUM THEN
1703 x_qqhv_rec.originating_vendor_id := null;
1704 END IF;
1705
1706 IF x_qqhv_rec.program_agreement_id IS NULL THEN
1707 x_qqhv_rec.program_agreement_id := l_db_qqhv_rec.program_agreement_id;
1708 ELSIF x_qqhv_rec.program_agreement_id = FND_API.G_MISS_NUM THEN
1709 x_qqhv_rec.program_agreement_id := null;
1710 END IF;
1711
1712 IF x_qqhv_rec.sales_rep_id IS NULL THEN
1713 x_qqhv_rec.sales_rep_id := l_db_qqhv_rec.sales_rep_id;
1714 ELSIF x_qqhv_rec.sales_rep_id = FND_API.G_MISS_NUM THEN
1715 x_qqhv_rec.sales_rep_id := null;
1716 END IF;
1717
1718 IF x_qqhv_rec.sales_territory_id IS NULL THEN
1719 x_qqhv_rec.sales_territory_id := l_db_qqhv_rec.sales_territory_id;
1720 ELSIF x_qqhv_rec.sales_territory_id = FND_API.G_MISS_NUM THEN
1721 x_qqhv_rec.sales_territory_id := null;
1722 END IF;
1723
1724 IF x_qqhv_rec.structured_pricing IS NULL THEN
1725 x_qqhv_rec.structured_pricing := l_db_qqhv_rec.structured_pricing;
1726 ELSIF x_qqhv_rec.structured_pricing = FND_API.G_MISS_CHAR THEN
1727 x_qqhv_rec.structured_pricing := null;
1728 END IF;
1729
1730 IF x_qqhv_rec.line_level_pricing IS NULL THEN
1731 x_qqhv_rec.line_level_pricing := l_db_qqhv_rec.line_level_pricing;
1732 ELSIF x_qqhv_rec.line_level_pricing = FND_API.G_MISS_CHAR THEN
1733 x_qqhv_rec.line_level_pricing := null;
1734 END IF;
1735
1739 x_qqhv_rec.rate_template_id := null;
1736 IF x_qqhv_rec.rate_template_id IS NULL THEN
1737 x_qqhv_rec.rate_template_id := l_db_qqhv_rec.rate_template_id;
1738 ELSIF x_qqhv_rec.rate_template_id = FND_API.G_MISS_NUM THEN
1740 END IF;
1741
1742 IF x_qqhv_rec.rate_card_id IS NULL THEN
1743 x_qqhv_rec.rate_card_id := l_db_qqhv_rec.rate_card_id;
1744 ELSIF x_qqhv_rec.rate_card_id = FND_API.G_MISS_NUM THEN
1745 x_qqhv_rec.rate_card_id := null;
1746 END IF;
1747
1748 IF x_qqhv_rec.lease_rate_factor IS NULL THEN
1749 x_qqhv_rec.lease_rate_factor := l_db_qqhv_rec.lease_rate_factor;
1750 ELSIF x_qqhv_rec.lease_rate_factor = FND_API.G_MISS_NUM THEN
1751 x_qqhv_rec.lease_rate_factor := null;
1752 END IF;
1753
1754 IF x_qqhv_rec.target_rate_type IS NULL THEN
1755 x_qqhv_rec.target_rate_type := l_db_qqhv_rec.target_rate_type;
1756 ELSIF x_qqhv_rec.target_rate_type = FND_API.G_MISS_CHAR THEN
1757 x_qqhv_rec.target_rate_type := null;
1758 END IF;
1759
1760 IF x_qqhv_rec.target_rate IS NULL THEN
1761 x_qqhv_rec.target_rate := l_db_qqhv_rec.target_rate;
1762 ELSIF x_qqhv_rec.target_rate = FND_API.G_MISS_NUM THEN
1763 x_qqhv_rec.target_rate := null;
1764 END IF;
1765
1766 IF x_qqhv_rec.target_amount IS NULL THEN
1767 x_qqhv_rec.target_amount := l_db_qqhv_rec.target_amount;
1768 ELSIF x_qqhv_rec.target_amount = FND_API.G_MISS_NUM THEN
1769 x_qqhv_rec.target_amount := null;
1770 END IF;
1771
1772 IF x_qqhv_rec.target_frequency IS NULL THEN
1773 x_qqhv_rec.target_frequency := l_db_qqhv_rec.target_frequency;
1774 ELSIF x_qqhv_rec.target_frequency = FND_API.G_MISS_CHAR THEN
1775 x_qqhv_rec.target_frequency := null;
1776 END IF;
1777
1778 IF x_qqhv_rec.target_arrears IS NULL THEN
1779 x_qqhv_rec.target_arrears := l_db_qqhv_rec.target_arrears;
1780 ELSIF x_qqhv_rec.target_arrears = FND_API.G_MISS_CHAR THEN
1781 x_qqhv_rec.target_arrears := null;
1782 END IF;
1783
1784 IF x_qqhv_rec.target_periods IS NULL THEN
1785 x_qqhv_rec.target_periods := l_db_qqhv_rec.target_periods;
1786 ELSIF x_qqhv_rec.target_periods = FND_API.G_MISS_NUM THEN
1787 x_qqhv_rec.target_periods := null;
1788 END IF;
1789
1790 IF x_qqhv_rec.iir IS NULL THEN
1791 x_qqhv_rec.iir := l_db_qqhv_rec.iir;
1792 ELSIF x_qqhv_rec.iir = FND_API.G_MISS_NUM THEN
1793 x_qqhv_rec.iir := null;
1794 END IF;
1795
1796 IF x_qqhv_rec.sub_iir IS NULL THEN
1797 x_qqhv_rec.sub_iir := l_db_qqhv_rec.sub_iir;
1798 ELSIF x_qqhv_rec.sub_iir = FND_API.G_MISS_NUM THEN
1799 x_qqhv_rec.sub_iir := null;
1800 END IF;
1801
1802 IF x_qqhv_rec.booking_yield IS NULL THEN
1803 x_qqhv_rec.booking_yield := l_db_qqhv_rec.booking_yield;
1804 ELSIF x_qqhv_rec.booking_yield = FND_API.G_MISS_NUM THEN
1805 x_qqhv_rec.booking_yield := null;
1806 END IF;
1807
1808 IF x_qqhv_rec.sub_booking_yield IS NULL THEN
1809 x_qqhv_rec.sub_booking_yield := l_db_qqhv_rec.sub_booking_yield;
1810 ELSIF x_qqhv_rec.sub_booking_yield = FND_API.G_MISS_NUM THEN
1811 x_qqhv_rec.sub_booking_yield := null;
1812 END IF;
1813
1814 IF x_qqhv_rec.pirr IS NULL THEN
1815 x_qqhv_rec.pirr := l_db_qqhv_rec.pirr;
1816 ELSIF x_qqhv_rec.pirr = FND_API.G_MISS_NUM THEN
1817 x_qqhv_rec.pirr := null;
1818 END IF;
1819
1820 IF x_qqhv_rec.sub_pirr IS NULL THEN
1821 x_qqhv_rec.sub_pirr := l_db_qqhv_rec.sub_pirr;
1822 ELSIF x_qqhv_rec.sub_pirr = FND_API.G_MISS_NUM THEN
1823 x_qqhv_rec.sub_pirr := null;
1824 END IF;
1825
1826 IF x_qqhv_rec.airr IS NULL THEN
1827 x_qqhv_rec.airr := l_db_qqhv_rec.airr;
1828 ELSIF x_qqhv_rec.airr = FND_API.G_MISS_NUM THEN
1829 x_qqhv_rec.airr := null;
1830 END IF;
1831
1832 IF x_qqhv_rec.sub_airr IS NULL THEN
1833 x_qqhv_rec.sub_airr := l_db_qqhv_rec.sub_airr;
1834 ELSIF x_qqhv_rec.sub_airr = FND_API.G_MISS_NUM THEN
1835 x_qqhv_rec.sub_airr := null;
1836 END IF;
1837
1838 IF x_qqhv_rec.short_description IS NULL THEN
1839 x_qqhv_rec.short_description := l_db_qqhv_rec.short_description;
1843
1840 ELSIF x_qqhv_rec.short_description = FND_API.G_MISS_CHAR THEN
1841 x_qqhv_rec.short_description := null;
1842 END IF;
1844 IF x_qqhv_rec.description IS NULL THEN
1845 x_qqhv_rec.description := l_db_qqhv_rec.description;
1846 ELSIF x_qqhv_rec.description = FND_API.G_MISS_CHAR THEN
1847 x_qqhv_rec.description := null;
1848 END IF;
1849
1850 IF x_qqhv_rec.comments IS NULL THEN
1851 x_qqhv_rec.comments := l_db_qqhv_rec.comments;
1852 ELSIF x_qqhv_rec.comments = FND_API.G_MISS_CHAR THEN
1853 x_qqhv_rec.comments := null;
1854 END IF;
1855
1856 IF x_qqhv_rec.sts_code IS NULL THEN
1857 x_qqhv_rec.sts_code := l_db_qqhv_rec.sts_code;
1858 ELSIF x_qqhv_rec.sts_code = FND_API.G_MISS_CHAR THEN
1859 x_qqhv_rec.sts_code := null;
1860 END IF;
1861 -- smadhava - Modified - G_MISS compliance - End
1862
1863 RETURN l_return_status;
1864
1865 EXCEPTION
1866
1867 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1868
1869 x_return_status := G_RET_STS_ERROR;
1870
1871 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1872
1873 x_return_status := G_RET_STS_UNEXP_ERROR;
1874
1875 WHEN OTHERS THEN
1876
1877 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1878 p_msg_name => G_DB_ERROR,
1879 p_token1 => G_PROG_NAME_TOKEN,
1880 p_token1_value => l_prog_name,
1881 p_token2 => G_SQLCODE_TOKEN,
1882 p_token2_value => sqlcode,
1883 p_token3 => G_SQLERRM_TOKEN,
1884 p_token3_value => sqlerrm);
1885
1886 x_return_status := G_RET_STS_UNEXP_ERROR;
1887
1888 END populate_new_record;
1889
1890 BEGIN
1891
1892 l_prog_name := G_PKG_NAME||'.update_row (V)';
1893
1894 l_return_status := populate_new_record (p_qqhv_rec, l_qqhv_rec);
1895
1896 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1897 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1898 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1899 RAISE OKL_API.G_EXCEPTION_ERROR;
1900 END IF;
1901
1902 l_return_status := validate_attributes (l_qqhv_rec);
1903
1904 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1905 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1906 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1907 RAISE OKL_API.G_EXCEPTION_ERROR;
1908 END IF;
1909
1910 l_return_status := validate_record (l_qqhv_rec);
1911
1912 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1913 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1914 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1915 RAISE OKL_API.G_EXCEPTION_ERROR;
1916 END IF;
1917
1918 --Bug 7022258-Added by kkorrapo
1919 l_return_status := validate_unique (l_qqhv_rec);
1920
1921 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1922 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1923 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1924 RAISE OKL_API.G_EXCEPTION_ERROR;
1925 END IF;
1926 --Bug 7022258--Addition end
1927
1928 migrate (l_qqhv_rec, l_qqh_rec);
1929 migrate (l_qqhv_rec, l_qqhtl_rec);
1930
1931 update_row (x_return_status => l_return_status, p_qqh_rec => l_qqh_rec);
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 update_row (x_return_status => l_return_status, p_qqhtl_rec => l_qqhtl_rec);
1940
1941 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1942 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1943 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1944 RAISE OKL_API.G_EXCEPTION_ERROR;
1945 END IF;
1946
1947 x_return_status := l_return_status;
1948 x_qqhv_rec := l_qqhv_rec;
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_prog_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 update_row;
1974
1975
1976 -----------------------------
1977 -- PROCEDURE update_row (REC)
1978 -----------------------------
1979 PROCEDURE update_row(
1980 p_api_version IN NUMBER,
1981 p_init_msg_list IN VARCHAR2,
1985 p_qqhv_rec IN qqhv_rec_type,
1982 x_return_status OUT NOCOPY VARCHAR2,
1983 x_msg_count OUT NOCOPY NUMBER,
1984 x_msg_data OUT NOCOPY VARCHAR2,
1986 x_qqhv_rec OUT NOCOPY qqhv_rec_type) IS
1987
1988 l_return_status VARCHAR2(1);
1989
1990 l_prog_name VARCHAR2(61);
1991
1992 BEGIN
1993
1994 l_prog_name := G_PKG_NAME||'.update_row (REC)';
1995
1996 IF p_init_msg_list = G_TRUE THEN
1997 FND_MSG_PUB.initialize;
1998 END IF;
1999
2000 update_row (x_return_status => l_return_status,
2001 p_qqhv_rec => p_qqhv_rec,
2002 x_qqhv_rec => x_qqhv_rec);
2003
2004 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2005 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2006 ELSIF l_return_status = G_RET_STS_ERROR THEN
2007 RAISE OKL_API.G_EXCEPTION_ERROR;
2008 END IF;
2009
2010 -- viselvar added
2011 x_qqhv_rec.object_version_number:=x_qqhv_rec.object_version_number+1;
2012 x_return_status := l_return_status;
2013
2014 EXCEPTION
2015
2016 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2017
2018 x_return_status := G_RET_STS_ERROR;
2019
2020 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2021
2022 x_return_status := G_RET_STS_UNEXP_ERROR;
2023
2024 WHEN OTHERS THEN
2025
2026 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2027 p_msg_name => G_DB_ERROR,
2028 p_token1 => G_PROG_NAME_TOKEN,
2029 p_token1_value => l_prog_name,
2030 p_token2 => G_SQLCODE_TOKEN,
2031 p_token2_value => sqlcode,
2032 p_token3 => G_SQLERRM_TOKEN,
2033 p_token3_value => sqlerrm);
2034
2035 x_return_status := G_RET_STS_UNEXP_ERROR;
2036
2037 END update_row;
2038
2039
2040 -----------------------------
2041 -- PROCEDURE update_row (TBL)
2042 -----------------------------
2043 PROCEDURE update_row(
2044 p_api_version IN NUMBER,
2045 p_init_msg_list IN VARCHAR2,
2046 x_return_status OUT NOCOPY VARCHAR2,
2047 x_msg_count OUT NOCOPY NUMBER,
2048 x_msg_data OUT NOCOPY VARCHAR2,
2049 p_qqhv_tbl IN qqhv_tbl_type,
2050 x_qqhv_tbl OUT NOCOPY qqhv_tbl_type) IS
2051
2052 l_return_status VARCHAR2(1);
2053 i BINARY_INTEGER;
2054 l_prog_name VARCHAR2(61);
2055
2056 BEGIN
2057
2058 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
2059
2060 IF p_init_msg_list = G_TRUE THEN
2061 FND_MSG_PUB.initialize;
2062 END IF;
2063
2064 x_qqhv_tbl := p_qqhv_tbl;
2065
2066 IF (p_qqhv_tbl.COUNT > 0) THEN
2067
2068 i := p_qqhv_tbl.FIRST;
2069
2070 LOOP
2071
2072 IF p_qqhv_tbl.EXISTS(i) THEN
2073 update_row (x_return_status => l_return_status,
2074 p_qqhv_rec => p_qqhv_tbl(i),
2075 x_qqhv_rec => x_qqhv_tbl(i));
2076
2077 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2078 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2079 ELSIF l_return_status = G_RET_STS_ERROR THEN
2080 RAISE OKL_API.G_EXCEPTION_ERROR;
2081 END IF;
2082
2083 EXIT WHEN (i = p_qqhv_tbl.LAST);
2084 i := p_qqhv_tbl.NEXT(i);
2085
2086 END IF;
2087
2088 END LOOP;
2089
2090 ELSE
2091
2092 l_return_status := G_RET_STS_SUCCESS;
2093
2094 END IF;
2095
2096 x_return_status := l_return_status;
2097
2098 EXCEPTION
2099
2100 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2101
2102 x_return_status := G_RET_STS_ERROR;
2103
2104 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2105
2106 x_return_status := G_RET_STS_UNEXP_ERROR;
2107
2108 WHEN OTHERS THEN
2109
2110 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2111 p_msg_name => G_DB_ERROR,
2112 p_token1 => G_PROG_NAME_TOKEN,
2113 p_token1_value => l_prog_name,
2114 p_token2 => G_SQLCODE_TOKEN,
2115 p_token2_value => sqlcode,
2116 p_token3 => G_SQLERRM_TOKEN,
2117 p_token3_value => sqlerrm);
2118
2119 x_return_status := G_RET_STS_UNEXP_ERROR;
2120
2121 END update_row;
2122
2123
2124 -----------------
2125 -- delete_row (V)
2126 -----------------
2127 PROCEDURE delete_row(
2128 x_return_status OUT NOCOPY VARCHAR2,
2129 p_id IN NUMBER) IS
2130
2131 l_prog_name VARCHAR2(61);
2132
2133 BEGIN
2134
2135 l_prog_name := G_PKG_NAME||'.delete_row (V)';
2136
2137 DELETE FROM OKL_QUICK_QUOTES_B WHERE id = p_id;
2138 DELETE FROM OKL_QUICK_QUOTES_TL WHERE id = p_id;
2139
2140 x_return_status := G_RET_STS_SUCCESS;
2141
2142 EXCEPTION
2143
2144 WHEN OTHERS THEN
2145
2146 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2147 p_msg_name => G_DB_ERROR,
2148 p_token1 => G_PROG_NAME_TOKEN,
2149 p_token1_value => l_prog_name,
2150 p_token2 => G_SQLCODE_TOKEN,
2151 p_token2_value => sqlcode,
2152 p_token3 => G_SQLERRM_TOKEN,
2153 p_token3_value => sqlerrm);
2154
2155 x_return_status := G_RET_STS_UNEXP_ERROR;
2156
2157 END delete_row;
2158
2159
2160 -----------------------------
2161 -- PROCEDURE delete_row (REC)
2162 -----------------------------
2163 PROCEDURE delete_row(
2164 p_api_version IN NUMBER,
2165 p_init_msg_list IN VARCHAR2,
2166 x_return_status OUT NOCOPY VARCHAR2,
2167 x_msg_count OUT NOCOPY NUMBER,
2168 x_msg_data OUT NOCOPY VARCHAR2,
2169 p_qqhv_rec IN qqhv_rec_type) IS
2170
2171 l_return_status VARCHAR2(1);
2172
2173 l_prog_name VARCHAR2(61);
2174
2175 BEGIN
2176
2177 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
2178
2179 IF p_init_msg_list = G_TRUE THEN
2180 FND_MSG_PUB.initialize;
2181 END IF;
2182
2183 delete_row (x_return_status => l_return_status,
2184 p_id => p_qqhv_rec.id);
2185
2186 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2187 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2188 ELSIF l_return_status = G_RET_STS_ERROR THEN
2189 RAISE OKL_API.G_EXCEPTION_ERROR;
2190 END IF;
2191
2192 x_return_status := l_return_status;
2193
2194 EXCEPTION
2195
2196 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2197
2198 x_return_status := G_RET_STS_ERROR;
2199
2200 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2201
2202 x_return_status := G_RET_STS_UNEXP_ERROR;
2203
2204 WHEN OTHERS THEN
2205
2206 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2207 p_msg_name => G_DB_ERROR,
2208 p_token1 => G_PROG_NAME_TOKEN,
2209 p_token1_value => l_prog_name,
2210 p_token2 => G_SQLCODE_TOKEN,
2211 p_token2_value => sqlcode,
2212 p_token3 => G_SQLERRM_TOKEN,
2213 p_token3_value => sqlerrm);
2214
2215 x_return_status := G_RET_STS_UNEXP_ERROR;
2216
2217 END delete_row;
2218
2219
2220 -------------------
2221 -- delete_row (TBL)
2222 -------------------
2223 PROCEDURE delete_row(
2224 p_api_version IN NUMBER,
2225 p_init_msg_list IN VARCHAR2,
2226 x_return_status OUT NOCOPY VARCHAR2,
2227 x_msg_count OUT NOCOPY NUMBER,
2228 x_msg_data OUT NOCOPY VARCHAR2,
2229 p_qqhv_tbl IN qqhv_tbl_type) IS
2230
2231 l_return_status VARCHAR2(1);
2232 i BINARY_INTEGER;
2233
2234 l_prog_name VARCHAR2(61);
2235
2236 BEGIN
2237
2238 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
2239
2240 IF p_init_msg_list = G_TRUE THEN
2241 FND_MSG_PUB.initialize;
2242 END IF;
2243
2244 IF (p_qqhv_tbl.COUNT > 0) THEN
2245
2246 i := p_qqhv_tbl.FIRST;
2247
2248 LOOP
2249
2250 IF p_qqhv_tbl.EXISTS(i) THEN
2251
2252 delete_row (x_return_status => l_return_status,
2253 p_id => p_qqhv_tbl(i).id);
2254
2255 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
2256 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2257 ELSIF l_return_status = G_RET_STS_ERROR THEN
2258 RAISE OKL_API.G_EXCEPTION_ERROR;
2259 END IF;
2260
2261 EXIT WHEN (i = p_qqhv_tbl.LAST);
2262 i := p_qqhv_tbl.NEXT(i);
2263
2264 END IF;
2265
2266 END LOOP;
2267
2268 ELSE
2269
2270 l_return_status := G_RET_STS_SUCCESS;
2271
2272 END IF;
2273
2274 x_return_status := l_return_status;
2275
2276 EXCEPTION
2277
2278 WHEN OTHERS THEN
2279
2280 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
2281 p_msg_name => G_DB_ERROR,
2282 p_token1 => G_PROG_NAME_TOKEN,
2283 p_token1_value => l_prog_name,
2284 p_token2 => G_SQLCODE_TOKEN,
2285 p_token2_value => sqlcode,
2286 p_token3 => G_SQLERRM_TOKEN,
2287 p_token3_value => sqlerrm);
2288
2289 x_return_status := G_RET_STS_UNEXP_ERROR;
2290
2291 END delete_row;
2292
2293
2294 END OKL_QQH_PVT;