[Home] [Help]
PACKAGE BODY: APPS.OKL_QUICK_QUOTES_PVT
Source
1 PACKAGE BODY OKL_QUICK_QUOTES_PVT AS
2 /* $Header: OKLRQQHB.pls 120.37.12010000.3 2008/11/13 13:33:45 kkorrapo ship $ */
3 -- subtype records used during pricing
4 SUBTYPE pricing_results_tbl_type IS OKL_PRICING_UTILS_PVT.pricing_results_tbl_type;
5 SUBTYPE yields_rec IS OKL_PRICING_UTILS_PVT.yields_rec;
6 SUBTYPE so_cash_flows_rec_type IS OKL_PRICING_UTILS_PVT.so_cash_flows_rec_type;
7 SUBTYPE so_cash_flow_details_tbl_type IS OKL_PRICING_UTILS_PVT.so_cash_flow_details_tbl_type;
8 SUBTYPE qa_results_tbl_type IS OKL_SALES_QUOTE_QA_PVT.qa_results_tbl_type;
9
10 user_exception exception;
11
12 FUNCTION validate_header(p_qqhv_rec_type IN qqhv_rec_type) RETURN VARCHAR2 IS
13
14 CURSOR chk_uniquness IS
15 SELECT 'x'
16 FROM okl_quick_quotes_b
17 WHERE reference_number = p_qqhv_rec_type.reference_number
18 AND id <> NVL(p_qqhv_rec_type.id, -9999);
19
20 l_dummy_var VARCHAR2(1);
21 x_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
22 l_api_name CONSTANT VARCHAR2(61) := g_pkg_name || '.' || 'validate_header';
23 l_msg_count NUMBER;
24 l_msg_data VARCHAR2(2000);
25
26 BEGIN
27
28 OPEN chk_uniquness; -- QQ Reference Number should be unique
29 FETCH chk_uniquness INTO l_dummy_var;
30 CLOSE chk_uniquness; -- if l_dummy_var is 'x' then Ref Num already exists
31
32 IF (l_dummy_var = 'x') THEN
33 okl_api.set_message(p_app_name => g_app_name
34 ,p_msg_name => 'OKL_DUPLICATE_CURE_REQUEST'
35 ,p_token1 => 'COL_NAME'
36 ,p_token1_value => p_qqhv_rec_type.reference_number);
37 RAISE okl_api.g_exception_error;
38 END IF;
39
40 RETURN x_return_status;
41
42 EXCEPTION
43 WHEN okl_api.g_exception_error THEN
44 RETURN okl_api.g_ret_sts_error;
45 WHEN OTHERS THEN
46 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
47 ,p_pkg_name => g_pkg_name
48 ,p_exc_name => 'OTHERS'
49 ,x_msg_count => l_msg_count
50 ,x_msg_data => l_msg_data
51 ,p_api_type => g_api_type);
52 RETURN x_return_status;
53 END validate_header;
54
55 PROCEDURE create_qqh(p_api_version IN NUMBER
56 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
57 ,x_return_status OUT NOCOPY VARCHAR2
58 ,x_msg_count OUT NOCOPY NUMBER
59 ,x_msg_data OUT NOCOPY VARCHAR2
60 ,p_qqhv_rec_type IN qqhv_rec_type
61 ,x_qqhv_rec_type OUT NOCOPY qqhv_rec_type ) IS
62
63 lp_qqhv_rec_type qqhv_rec_type;
64 lx_qqhv_rec_type qqhv_rec_type;
65 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.CREATE_QQH';
66 l_debug_enabled VARCHAR2(10);
67 is_debug_procedure_on BOOLEAN;
68 is_debug_statement_on BOOLEAN;
69 l_api_name CONSTANT VARCHAR2(30) := 'create_qqh';
70 l_api_version CONSTANT NUMBER := 1.0;
71 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
72
73 BEGIN
74 l_debug_enabled := nvl(okl_debug_pub.check_log_enabled,'N');
75 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
76 ,fnd_log.level_procedure);
77
78 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
79 okl_debug_pub.log_debug(fnd_log.level_procedure
80 ,l_module
81 ,'begin debug OKLRQQHB.pls call create_qqh');
82 END IF;
83 -- check for logging on STATEMENT level
84 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
85 ,fnd_log.level_statement);
86
87 -- call START_ACTIVITY to create savepoint, check compatibility
88 -- and initialize message list
89
90 l_return_status := okl_api.start_activity(p_api_name => l_api_name
91 ,p_pkg_name => g_pkg_name
92 ,p_init_msg_list => p_init_msg_list
93 ,l_api_version => l_api_version
94 ,p_api_version => p_api_version
95 ,p_api_type => g_api_type
96 ,x_return_status => x_return_status);
97
98 lp_qqhv_rec_type := p_qqhv_rec_type;
99
100 l_return_status := validate_header(lp_qqhv_rec_type); --validate header
101 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
102 RAISE okl_api.g_exception_unexpected_error;
103 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
104 RAISE okl_api.g_exception_error;
105 END IF;
106
107 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN -- write to log
108 okl_debug_pub.log_debug(fnd_log.level_statement
109 ,l_module
110 ,'Function okl_quick_quotes_pvt.validate_header returned with status ' ||
111 l_return_status);
112 END IF; -- end of l_debug_enabled ='Y'
113
114 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
115 RAISE okl_api.g_exception_unexpected_error;
116 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
117 RAISE okl_api.g_exception_error;
118 END IF;
119
120 okl_qqh_pvt.insert_row(p_api_version
121 ,okl_api.g_false
122 ,l_return_status
123 ,x_msg_count
124 ,x_msg_data
125 ,lp_qqhv_rec_type
126 ,lx_qqhv_rec_type); -- write to log
127
128 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
129 okl_debug_pub.log_debug(fnd_log.level_statement
130 ,l_module
131 ,'okl_qqh_pvt.insert_row returned with status ' ||
132 l_return_status ||
133 ' x_msg_data ' ||
134 x_msg_data);
135 END IF; -- end of l_debug_enabled ='Y'
136
137 IF (l_return_status = okl_api.g_ret_sts_error) THEN
138 RAISE okl_api.g_exception_error;
139 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
140 RAISE okl_api.g_exception_unexpected_error;
141 END IF;
142
143 x_qqhv_rec_type := lx_qqhv_rec_type;
144
145 x_return_status := l_return_status;
146
147 okl_api.end_activity(x_msg_count => x_msg_count
148 ,x_msg_data => x_msg_data);
149
150 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
151 okl_debug_pub.log_debug(fnd_log.level_procedure
152 ,l_module
153 ,'end debug OKLRQQHB.pls call create_qqh');
154 END IF;
155
156 EXCEPTION
157 WHEN okl_api.g_exception_error THEN
158 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
159 ,p_pkg_name => g_pkg_name
160 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
161 ,x_msg_count => x_msg_count
162 ,x_msg_data => x_msg_data
163 ,p_api_type => g_api_type);
164 WHEN okl_api.g_exception_unexpected_error THEN
165 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
166 ,p_pkg_name => g_pkg_name
167 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
168 ,x_msg_count => x_msg_count
169 ,x_msg_data => x_msg_data
170 ,p_api_type => g_api_type);
171 WHEN OTHERS THEN
172 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
173 ,p_pkg_name => g_pkg_name
174 ,p_exc_name => 'OTHERS'
175 ,x_msg_count => x_msg_count
176 ,x_msg_data => x_msg_data
177 ,p_api_type => g_api_type);
178 END create_qqh;
179
180 PROCEDURE create_qql(p_api_version IN NUMBER
181 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
182 ,x_return_status OUT NOCOPY VARCHAR2
183 ,x_msg_count OUT NOCOPY NUMBER
184 ,x_msg_data OUT NOCOPY VARCHAR2
185 ,p_qqlv_tbl_type IN qqlv_tbl_type
186 ,x_qqlv_tbl_type OUT NOCOPY qqlv_tbl_type) IS
187
188 lp_qqlv_tbl_type qqlv_tbl_type;
189 lx_qqlv_tbl_type qqlv_tbl_type;
190 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.CREATE_QQL';
191 l_debug_enabled VARCHAR2(10);
192 is_debug_procedure_on BOOLEAN;
193 is_debug_statement_on BOOLEAN;
194 l_api_name CONSTANT VARCHAR2(30) := 'create_qql';
195 l_api_version CONSTANT NUMBER := 1.0;
196 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
197
198 BEGIN
199 l_debug_enabled := nvl(okl_debug_pub.check_log_enabled,'N');
200 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
201 ,fnd_log.level_procedure);
202
203 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
204 okl_debug_pub.log_debug(fnd_log.level_procedure
205 ,l_module
206 ,'begin debug OKLRQQHB.pls call create_qql');
207 END IF;
208 -- check for logging on STATEMENT level
209 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
210 ,fnd_log.level_statement);
211
212 -- call START_ACTIVITY to create savepoint, check compatibility
213 -- and initialize message list
214
215 l_return_status := okl_api.start_activity(p_api_name => l_api_name
216 ,p_pkg_name => g_pkg_name
217 ,p_init_msg_list => p_init_msg_list
218 ,l_api_version => l_api_version
219 ,p_api_version => p_api_version
220 ,p_api_type => g_api_type
221 ,x_return_status => x_return_status); -- check if activity started successfully
222
223 lp_qqlv_tbl_type := p_qqlv_tbl_type;
224
225 okl_qql_pvt.insert_row(p_api_version
226 ,okl_api.g_false
227 ,l_return_status
228 ,x_msg_count
229 ,x_msg_data
230 ,lp_qqlv_tbl_type
231 ,lx_qqlv_tbl_type); -- write to log
232
233 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
234 okl_debug_pub.log_debug(fnd_log.level_statement
235 ,l_module
236 ,'okl_qql_pvt.insert_row returned with status ' ||
237 l_return_status ||
238 ' x_msg_data ' ||
239 x_msg_data);
240
241 END IF; -- end of l_debug_enabled ='Y'
242
243 IF (l_return_status = okl_api.g_ret_sts_error) THEN
244 RAISE okl_api.g_exception_error;
245 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
246 RAISE okl_api.g_exception_unexpected_error;
247 END IF;
248
249 --Assign value to OUT variables
250 x_qqlv_tbl_type := lx_qqlv_tbl_type;
251
252 x_return_status := l_return_status;
253 okl_api.end_activity(x_msg_count => x_msg_count
254 ,x_msg_data => x_msg_data);
255
256 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
257 okl_debug_pub.log_debug(fnd_log.level_procedure
258 ,l_module
259 ,'end debug OKLRQQHB.pls call create_qql');
260 END IF;
261
262 EXCEPTION
263 WHEN okl_api.g_exception_error THEN
264 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
265 ,p_pkg_name => g_pkg_name
266 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
267 ,x_msg_count => x_msg_count
268 ,x_msg_data => x_msg_data
269 ,p_api_type => g_api_type);
270 WHEN okl_api.g_exception_unexpected_error THEN
271 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
272 ,p_pkg_name => g_pkg_name
273 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
274 ,x_msg_count => x_msg_count
275 ,x_msg_data => x_msg_data
276 ,p_api_type => g_api_type);
277 WHEN OTHERS THEN
278 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
279 ,p_pkg_name => g_pkg_name
280 ,p_exc_name => 'OTHERS'
281 ,x_msg_count => x_msg_count
282 ,x_msg_data => x_msg_data
283 ,p_api_type => g_api_type);
284 END create_qql;
285
286 PROCEDURE create_quick_qte(p_api_version IN NUMBER
287 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
288 ,x_return_status OUT NOCOPY VARCHAR2
289 ,x_msg_count OUT NOCOPY NUMBER
290 ,x_msg_data OUT NOCOPY VARCHAR2
291 ,p_qqhv_rec_type IN qqhv_rec_type
292 ,x_qqhv_rec_type OUT NOCOPY qqhv_rec_type
293 ,p_qqlv_tbl_type IN qqlv_tbl_type
294 ,x_qqlv_tbl_type OUT NOCOPY qqlv_tbl_type) IS
295
296 lp_qqhv_rec_type qqhv_rec_type;
297 lx_qqhv_rec_type qqhv_rec_type;
298 lp_qqlv_tbl_type qqlv_tbl_type;
299 lx_qqlv_tbl_type qqlv_tbl_type;
300 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.CREATE_QUICK_QTE';
301 l_debug_enabled VARCHAR2(10);
302 is_debug_procedure_on BOOLEAN;
303 is_debug_statement_on BOOLEAN;
304 l_api_name CONSTANT VARCHAR2(30) := 'create_quick_qte';
305 l_api_version CONSTANT NUMBER := 1.0;
306 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
307 i NUMBER;
308
309 BEGIN
310 l_debug_enabled := nvl(okl_debug_pub.check_log_enabled,'N');
311 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
312 ,fnd_log.level_procedure);
313
314 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
315 okl_debug_pub.log_debug(fnd_log.level_procedure
316 ,l_module
317 ,'begin debug OKLRQQHB.pls call create_quick_qte');
318 END IF;
319 -- check for logging on STATEMENT level
320 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
321 ,fnd_log.level_statement);
322
323 -- call START_ACTIVITY to create savepoint, check compatibility
324 -- and initialize message list
325
326 l_return_status := okl_api.start_activity(p_api_name => l_api_name
327 ,p_pkg_name => g_pkg_name
328 ,p_init_msg_list => p_init_msg_list
329 ,l_api_version => l_api_version
330 ,p_api_version => p_api_version
331 ,p_api_type => g_api_type
332 ,x_return_status => x_return_status); -- check if activity started successfully
333
334 lp_qqhv_rec_type := p_qqhv_rec_type;
335 lp_qqlv_tbl_type := p_qqlv_tbl_type;
336
337 okl_quick_quotes_pvt.create_qqh(
338 p_api_version => p_api_version
339 ,p_init_msg_list => okl_api.g_false
340 ,x_return_status => l_return_status
341 ,x_msg_count => x_msg_count
342 ,x_msg_data => x_msg_data
343 ,p_qqhv_rec_type => lp_qqhv_rec_type
344 ,x_qqhv_rec_type => lx_qqhv_rec_type);
345
346
347 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
348 okl_debug_pub.log_debug(fnd_log.level_statement
349 ,l_module
350 ,'okl_quick_quotes_pvt.create_qqh returned with status ' ||
351 l_return_status ||
352 ' x_msg_data ' ||
353 x_msg_data);
354 END IF; -- end of l_debug_enabled ='Y'
355
356 IF (l_return_status = okl_api.g_ret_sts_error) THEN
357 RAISE okl_api.g_exception_error;
358 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
359 RAISE okl_api.g_exception_unexpected_error;
360 END IF;
361 -- populate the foreign key
362 FOR i in lp_qqlv_tbl_type.first..lp_qqlv_tbl_type.last LOOP
363 lp_qqlv_tbl_type(i).quick_quote_id:=lx_qqhv_rec_type.id;
364 END LOOP;
365
366 okl_quick_quotes_pvt.create_qql(
367 p_api_version => p_api_version
368 ,p_init_msg_list => okl_api.g_false
369 ,x_return_status => l_return_status
370 ,x_msg_count => x_msg_count
371 ,x_msg_data => x_msg_data
372 ,p_qqlv_tbl_type => lp_qqlv_tbl_type
373 ,x_qqlv_tbl_type => lx_qqlv_tbl_type);
374
375 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
376 okl_debug_pub.log_debug(fnd_log.level_statement
377 ,l_module
378 ,'okl_quick_quotes_pvt.create_qql returned with status ' ||
379 l_return_status ||
380 ' x_msg_data ' ||
381 x_msg_data);
382 END IF; -- end of l_debug_enabled ='Y'
383
384 IF (l_return_status = okl_api.g_ret_sts_error) THEN
385 RAISE okl_api.g_exception_error;
386 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
387 RAISE okl_api.g_exception_unexpected_error;
388 END IF;
389
390 x_qqhv_rec_type := lx_qqhv_rec_type;
391 x_qqlv_tbl_type := lx_qqlv_tbl_type;
392 x_return_status := l_return_status;
393 okl_api.end_activity(x_msg_count => x_msg_count
394 ,x_msg_data => x_msg_data);
395
396 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
397 okl_debug_pub.log_debug(fnd_log.level_procedure
398 ,l_module
399 ,'end debug OKLRQQHB.pls call create_qq');
400 END IF;
401
402 EXCEPTION
403 WHEN okl_api.g_exception_error THEN
404 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
405 ,p_pkg_name => g_pkg_name
406 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
407 ,x_msg_count => x_msg_count
408 ,x_msg_data => x_msg_data
409 ,p_api_type => g_api_type);
410 WHEN okl_api.g_exception_unexpected_error THEN
411 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
412 ,p_pkg_name => g_pkg_name
413 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
414 ,x_msg_count => x_msg_count
415 ,x_msg_data => x_msg_data
416 ,p_api_type => g_api_type);
417 WHEN OTHERS THEN
418 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
419 ,p_pkg_name => g_pkg_name
420 ,p_exc_name => 'OTHERS'
421 ,x_msg_count => x_msg_count
422 ,x_msg_data => x_msg_data
423 ,p_api_type => g_api_type);
424 END create_quick_qte;
425
426 PROCEDURE update_qqh (p_api_version IN NUMBER
427 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
428 ,x_return_status OUT NOCOPY VARCHAR2
429 ,x_msg_count OUT NOCOPY NUMBER
430 ,x_msg_data OUT NOCOPY VARCHAR2
431 ,p_qqhv_rec_type IN qqhv_rec_type
432 ,x_qqhv_rec_type OUT NOCOPY qqhv_rec_type) IS
433 lp_qqhv_rec_type qqhv_rec_type;
434 lx_qqhv_rec_type qqhv_rec_type;
435 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.UPDATE_QQH';
436 l_debug_enabled VARCHAR2(10);
437 is_debug_procedure_on BOOLEAN;
438 is_debug_statement_on BOOLEAN;
439 l_api_name CONSTANT VARCHAR2(30) := 'update_qqh';
440 l_api_version CONSTANT NUMBER := 1.0;
441 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
442
443 BEGIN
444 l_debug_enabled := NVL(okl_debug_pub.check_log_enabled,'N');
445 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
446 ,fnd_log.level_procedure);
447
448 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
449 okl_debug_pub.log_debug(fnd_log.level_procedure
450 ,l_module
451 ,'begin debug OKLRQQHB.pls call update_qqh');
452 END IF; -- check for logging at STATEMENT level
453 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
454 ,fnd_log.level_statement);
455
456 -- call START_ACTIVITY to create savepoint, check compatibility
457 -- and initialize message list
458
459 l_return_status := okl_api.start_activity(p_api_name => l_api_name
460 ,p_pkg_name => g_pkg_name
461 ,p_init_msg_list => p_init_msg_list
462 ,l_api_version => l_api_version
463 ,p_api_version => p_api_version
464 ,p_api_type => g_api_type
465 ,x_return_status => x_return_status); -- check if activity started successfully
466
467 lp_qqhv_rec_type := p_qqhv_rec_type;
468
469 -- schodava Bug # 4923350
470 -- Nulling out Rate Template/Card depending on the
471 -- structured pricing value
472 IF (lp_qqhv_rec_type.structured_pricing = 'Y') THEN
473 -- For Standard Rate Template, nullify LRF
474 IF lp_qqhv_rec_type.rate_template_id IS NOT NULL THEN
475 lp_qqhv_rec_type.lease_rate_factor := fnd_api.g_miss_num;
476 END IF;
477 lp_qqhv_rec_type.rate_template_id := fnd_api.g_miss_num;
478 lp_qqhv_rec_type.rate_card_id := fnd_api.g_miss_num;
479 ELSIF (lp_qqhv_rec_type.structured_pricing = 'N') THEN
480 lp_qqhv_rec_type.lease_rate_factor := fnd_api.g_miss_num;
481 -- For Standard Rate Template, nullify Rate Card Id and vice versa
482 IF lp_qqhv_rec_type.rate_template_id IS NOT NULL THEN
483 lp_qqhv_rec_type.rate_card_id := fnd_api.g_miss_num;
484 ELSE
485 lp_qqhv_rec_type.rate_template_id := fnd_api.g_miss_num;
486 END IF;
487 END IF;
488
489 okl_qqh_pvt.update_row(p_api_version
490 ,okl_api.g_false
491 ,l_return_status
492 ,x_msg_count
493 ,x_msg_data
494 ,lp_qqhv_rec_type
495 ,lx_qqhv_rec_type); -- write to log
496
497 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
498 okl_debug_pub.log_debug(fnd_log.level_statement
499 ,l_module
500 ,'okl_qqh_pvt.update_row returned with status ' ||
501 l_return_status ||
502 ' x_msg_data ' ||
503 x_msg_data);
504 END IF; -- end of l_debug_enabled ='Y'
505
506 IF (l_return_status = okl_api.g_ret_sts_error) THEN
507 RAISE okl_api.g_exception_error;
508 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
509 RAISE okl_api.g_exception_unexpected_error;
510 END IF;
511
512 x_qqhv_rec_type := lx_qqhv_rec_type;
513
514 x_return_status := l_return_status;
515 okl_api.end_activity(x_msg_count => x_msg_count
516 ,x_msg_data => x_msg_data);
517
518 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
519 okl_debug_pub.log_debug(fnd_log.level_procedure
520 ,l_module
521 ,'end debug OKLRQQHB.pls call update_qqh');
522 END IF;
523
524 EXCEPTION
525 WHEN okl_api.g_exception_error THEN
526 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
527 ,p_pkg_name => g_pkg_name
528 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
529 ,x_msg_count => x_msg_count
530 ,x_msg_data => x_msg_data
531 ,p_api_type => g_api_type);
532 WHEN okl_api.g_exception_unexpected_error THEN
533 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
534 ,p_pkg_name => g_pkg_name
535 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
536 ,x_msg_count => x_msg_count
537 ,x_msg_data => x_msg_data
538 ,p_api_type => g_api_type);
539 WHEN OTHERS THEN
540 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
541 ,p_pkg_name => g_pkg_name
542 ,p_exc_name => 'OTHERS'
543 ,x_msg_count => x_msg_count
544 ,x_msg_data => x_msg_data
545 ,p_api_type => g_api_type);
546 END update_qqh;
547 --start abhsaxen 22-Dec-2005
548 ------------------------------------------------------------------------------
549 -- Start of Comments
550 -- Created By : abhsaxen
551 -- Procedure Name : cancel_quick_quote
552 -- Description : Procedure functions to apply change in status of
553 -- quick quote from active to cancel
554 -- Dependencies :
555 -- Parameters :
556 -- Version : 1.0
557 -- End of Comments
558 -----------------------------------------------------------------------------
559
560 procedure cancel_quick_quote(p_api_version IN NUMBER
561 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
562 ,x_return_status OUT NOCOPY VARCHAR2
563 ,x_msg_count OUT NOCOPY NUMBER
564 ,x_msg_data OUT NOCOPY VARCHAR2
565 ,p_qqhv_rec_type IN qqhv_rec_type
566 ,x_qqhv_rec_type OUT NOCOPY qqhv_rec_type) IS
567 lp_qqhv_rec_type qqhv_rec_type;
568 lx_qqhv_rec_type qqhv_rec_type;
569 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.CANCEL_QUICK_QUOTE';
570 QQ_CANCELLED CONSTANT okl_quick_quotes_b.sts_code%TYPE := 'CANCELLED';
571 l_debug_enabled VARCHAR2(10);
572 is_debug_procedure_on BOOLEAN;
573 is_debug_statement_on BOOLEAN;
574 l_api_name CONSTANT VARCHAR2(30) := 'cancel_quick_quote';
575 l_api_version CONSTANT NUMBER := 1.0;
576 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
577
578 BEGIN
579 l_debug_enabled := NVL(okl_debug_pub.check_log_enabled,'N');
580 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
581 ,fnd_log.level_procedure);
582
583 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
584 okl_debug_pub.log_debug(fnd_log.level_procedure
585 ,l_module
586 ,'begin debug OKLRQQHB.pls call cancel_quick_quote');
587 END IF; -- check for logging at STATEMENT level
588 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
589 ,fnd_log.level_statement);
590
591 -- call START_ACTIVITY to create savepoint, check compatibility
592 -- and initialize message list
593
594 l_return_status := okl_api.start_activity(p_api_name => l_api_name
595 ,p_pkg_name => g_pkg_name
596 ,p_init_msg_list => p_init_msg_list
597 ,l_api_version => l_api_version
598 ,p_api_version => p_api_version
599 ,p_api_type => g_api_type
600 ,x_return_status => x_return_status); -- check if activity started successfully
601
602 lp_qqhv_rec_type := p_qqhv_rec_type;
603 lp_qqhv_rec_type.sts_code := QQ_CANCELLED;
604 okl_qqh_pvt.update_row(p_api_version
605 ,okl_api.g_false
606 ,l_return_status
607 ,x_msg_count
608 ,x_msg_data
609 ,lp_qqhv_rec_type
610 ,lx_qqhv_rec_type); -- write to log
611
612 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
613 okl_debug_pub.log_debug(fnd_log.level_statement
614 ,l_module
615 ,'okl_qqh_pvt.update_row returned with status ' ||
616 l_return_status ||
617 ' x_msg_data ' ||
618 x_msg_data);
619 END IF; -- end of l_debug_enabled ='Y'
620
621 IF (l_return_status = okl_api.g_ret_sts_error) THEN
622 RAISE okl_api.g_exception_error;
623 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
624 RAISE okl_api.g_exception_unexpected_error;
625 END IF;
626
627 x_qqhv_rec_type := lx_qqhv_rec_type;
628
629 x_return_status := l_return_status;
630 okl_api.end_activity(x_msg_count => x_msg_count
631 ,x_msg_data => x_msg_data);
632
633 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
634 okl_debug_pub.log_debug(fnd_log.level_procedure
635 ,l_module
636 ,'end debug OKLRQQHB.pls call cancel_quick_quote');
637 END IF;
638
639 EXCEPTION
640 WHEN okl_api.g_exception_error THEN
641 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
642 ,p_pkg_name => g_pkg_name
643 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
644 ,x_msg_count => x_msg_count
645 ,x_msg_data => x_msg_data
646 ,p_api_type => g_api_type);
647 WHEN okl_api.g_exception_unexpected_error THEN
648 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
649 ,p_pkg_name => g_pkg_name
650 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
651 ,x_msg_count => x_msg_count
652 ,x_msg_data => x_msg_data
653 ,p_api_type => g_api_type);
654 WHEN OTHERS THEN
655 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
656 ,p_pkg_name => g_pkg_name
657 ,p_exc_name => 'OTHERS'
658 ,x_msg_count => x_msg_count
659 ,x_msg_data => x_msg_data
660 ,p_api_type => g_api_type);
661 END cancel_quick_quote;
662
663
664 --end abhsaxen 22-Dec-2005
665
666
667
668 PROCEDURE update_qql (p_api_version IN NUMBER
669 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
670 ,x_return_status OUT NOCOPY VARCHAR2
671 ,x_msg_count OUT NOCOPY NUMBER
672 ,x_msg_data OUT NOCOPY VARCHAR2
673 ,p_qqlv_tbl_type IN qqlv_tbl_type
674 ,x_qqlv_tbl_type OUT NOCOPY qqlv_tbl_type) IS
675
676 lp_qqlv_tbl_type qqlv_tbl_type := p_qqlv_tbl_type; --dkagrawa assigned the in value
677 lx_qqlv_tbl_type qqlv_tbl_type;
678 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.UPDATE_QQL';
679 l_debug_enabled VARCHAR2(10);
680 is_debug_procedure_on BOOLEAN;
681 is_debug_statement_on BOOLEAN;
682 l_api_name CONSTANT VARCHAR2(30) := 'update_qql';
683 l_api_version CONSTANT NUMBER := 1.0;
684 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
685
686 BEGIN
687 l_debug_enabled := NVL(okl_debug_pub.check_log_enabled,'N');
688 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
689 ,fnd_log.level_procedure);
690
691 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
692 okl_debug_pub.log_debug(fnd_log.level_procedure
693 ,l_module
694 ,'begin debug OKLRQQHB.pls call update_qql');
695 END IF; -- check for logging at STATEMENT level
696 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
697 ,fnd_log.level_statement);
698
699 -- call START_ACTIVITY to create savepoint, check compatibility
700 -- and initialize message list
701
702 l_return_status := okl_api.start_activity(p_api_name => l_api_name
703 ,p_pkg_name => g_pkg_name
704 ,p_init_msg_list => p_init_msg_list
705 ,l_api_version => l_api_version
706 ,p_api_version => p_api_version
707 ,p_api_type => g_api_type
708 ,x_return_status => x_return_status); -- check if activity started successfully
709
710 IF lp_qqlv_tbl_type.COUNT > 0 THEN
711
712 okl_qql_pvt.update_row(p_api_version
713 ,okl_api.g_false
714 ,l_return_status
715 ,x_msg_count
716 ,x_msg_data
717 ,lp_qqlv_tbl_type
718 ,lx_qqlv_tbl_type); -- write to log
719 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
720 okl_debug_pub.log_debug(fnd_log.level_statement
721 ,l_module
722 ,'okl_qql_pvt.update_row returned with status ' ||
723 l_return_status ||
724 ' x_msg_data ' ||
725 x_msg_data);
726 END IF; -- end of l_debug_enabled ='Y'
727 IF (l_return_status = okl_api.g_ret_sts_error) THEN
728 RAISE okl_api.g_exception_error;
729 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
730 RAISE okl_api.g_exception_unexpected_error;
731 END IF;
732
733 END IF;
734
735 --Assign value to OUT variables
736
737 x_qqlv_tbl_type := lx_qqlv_tbl_type;
738 x_return_status := l_return_status;
739 okl_api.end_activity(x_msg_count => x_msg_count
740 ,x_msg_data => x_msg_data);
741
742 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
743 okl_debug_pub.log_debug(fnd_log.level_procedure
744 ,l_module
745 ,'end debug OKLRQQHB.pls call update_qql');
746 END IF;
747
748 EXCEPTION
749 WHEN okl_api.g_exception_error THEN
750 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
751 ,p_pkg_name => g_pkg_name
752 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
753 ,x_msg_count => x_msg_count
754 ,x_msg_data => x_msg_data
755 ,p_api_type => g_api_type);
756 WHEN okl_api.g_exception_unexpected_error THEN
757 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
758 ,p_pkg_name => g_pkg_name
759 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
760 ,x_msg_count => x_msg_count
761 ,x_msg_data => x_msg_data
762 ,p_api_type => g_api_type);
763 WHEN OTHERS THEN
764 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
765 ,p_pkg_name => g_pkg_name
766 ,p_exc_name => 'OTHERS'
767 ,x_msg_count => x_msg_count
768 ,x_msg_data => x_msg_data
769 ,p_api_type => g_api_type);
770 END update_qql;
771
772 PROCEDURE update_quick_qte(p_api_version IN NUMBER
773 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
774 ,x_return_status OUT NOCOPY VARCHAR2
775 ,x_msg_count OUT NOCOPY NUMBER
776 ,x_msg_data OUT NOCOPY VARCHAR2
777 ,p_qqhv_rec_type IN qqhv_rec_type
778 ,x_qqhv_rec_type OUT NOCOPY qqhv_rec_type
779 ,p_qqlv_tbl_type IN qqlv_tbl_type
780 ,x_qqlv_tbl_type OUT NOCOPY qqlv_tbl_type) IS
781
782 lp_qqhv_rec_type qqhv_rec_type;
783 lx_qqhv_rec_type qqhv_rec_type;
784 lp_qqlv_tbl_type qqlv_tbl_type;
785 lx_qqlv_tbl_type qqlv_tbl_type;
786 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.UPDATE_QUICK_QTE';
787 l_debug_enabled VARCHAR2(10);
788 is_debug_procedure_on BOOLEAN;
789 is_debug_statement_on BOOLEAN;
790 l_api_name CONSTANT VARCHAR2(30) := 'update_quick_qte';
791 l_api_version CONSTANT NUMBER := 1.0;
792 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
793
794 BEGIN
795 l_debug_enabled := NVL(okl_debug_pub.check_log_enabled,'N');
796 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
797 ,fnd_log.level_procedure);
798
799 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
800 okl_debug_pub.log_debug(fnd_log.level_procedure
801 ,l_module
802 ,'begin debug OKLRQQHB.pls call update_quick_qte');
803 END IF; -- check for logging at STATEMENT level
804 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
805 ,fnd_log.level_statement);
806
807 -- call START_ACTIVITY to create savepoint, check compatibility
808 -- and initialize message list
809
810 l_return_status := okl_api.start_activity(p_api_name => l_api_name
811 ,p_pkg_name => g_pkg_name
812 ,p_init_msg_list => p_init_msg_list
813 ,l_api_version => l_api_version
814 ,p_api_version => p_api_version
815 ,p_api_type => g_api_type
816 ,x_return_status => x_return_status); -- check if activity started successfully
817
818 lp_qqhv_rec_type := p_qqhv_rec_type;
819 lp_qqlv_tbl_type := p_qqlv_tbl_type;
820
821 okl_quick_quotes_pvt.update_qqh(
822 p_api_version => p_api_version
823 ,p_init_msg_list => okl_api.g_false
824 ,x_return_status => l_return_status
825 ,x_msg_count => x_msg_count
826 ,x_msg_data => x_msg_data
827 ,p_qqhv_rec_type => lp_qqhv_rec_type
828 ,x_qqhv_rec_type => lx_qqhv_rec_type
829 );
830
831 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
832 okl_debug_pub.log_debug(fnd_log.level_statement
833 ,l_module
834 ,'okl_quick_quotes_pvt.update_qqh returned with status ' ||
835 l_return_status ||
836 ' x_msg_data ' ||
837 x_msg_data);
838 END IF; -- end of l_debug_enabled ='Y'
839
840 IF (l_return_status = okl_api.g_ret_sts_error) THEN
841 RAISE okl_api.g_exception_error;
842 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
843 RAISE okl_api.g_exception_unexpected_error;
844 END IF;
845
846 okl_quick_quotes_pvt.update_qql(
847 p_api_version => p_api_version
848 ,p_init_msg_list => okl_api.g_false
849 ,x_return_status => l_return_status
850 ,x_msg_count => x_msg_count
851 ,x_msg_data => x_msg_data
852 ,p_qqlv_tbl_type => lp_qqlv_tbl_type
853 ,x_qqlv_tbl_type => lx_qqlv_tbl_type
854 );
855
856 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
857 okl_debug_pub.log_debug(fnd_log.level_statement
858 ,l_module
859 ,'okl_quick_quotes_pvt.update_qql returned with status ' ||
860 l_return_status ||
861 ' x_msg_data ' ||
862 x_msg_data);
863 END IF; -- end of l_debug_enabled ='Y'
864
865 IF (l_return_status = okl_api.g_ret_sts_error) THEN
866 RAISE okl_api.g_exception_error;
867 ELSIF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
868 RAISE okl_api.g_exception_unexpected_error;
869 END IF;
870
871 x_qqhv_rec_type := lx_qqhv_rec_type;
872 x_qqlv_tbl_type := lx_qqlv_tbl_type;
873 x_return_status := l_return_status;
874 okl_api.end_activity(x_msg_count => x_msg_count
875 ,x_msg_data => x_msg_data);
876
877 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
878 okl_debug_pub.log_debug(fnd_log.level_procedure
879 ,l_module
880 ,'end debug OKLRQQHB.pls call update_quick_qte');
881 END IF;
882
883 EXCEPTION
884 WHEN okl_api.g_exception_error THEN
885 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
886 ,p_pkg_name => g_pkg_name
887 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
888 ,x_msg_count => x_msg_count
889 ,x_msg_data => x_msg_data
890 ,p_api_type => g_api_type);
891 WHEN okl_api.g_exception_unexpected_error THEN
892 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
893 ,p_pkg_name => g_pkg_name
894 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
895 ,x_msg_count => x_msg_count
896 ,x_msg_data => x_msg_data
897 ,p_api_type => g_api_type);
898 WHEN OTHERS THEN
899 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
900 ,p_pkg_name => g_pkg_name
901 ,p_exc_name => 'OTHERS'
902 ,x_msg_count => x_msg_count
903 ,x_msg_data => x_msg_data
904 ,p_api_type => g_api_type);
905 END update_quick_qte;
906
907 PROCEDURE delete_qql(p_api_version IN NUMBER
908 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
909 ,x_return_status OUT NOCOPY VARCHAR2
910 ,x_msg_count OUT NOCOPY NUMBER
911 ,x_msg_data OUT NOCOPY VARCHAR2
912 ,p_qqlv_rec_type IN qqlv_rec_type) IS
913
914 l_api_name CONSTANT VARCHAR2(30) := 'delete_qql';
915 l_api_version CONSTANT NUMBER := 1.0;
916 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
917 l_qqlv_rec qqlv_rec_type := p_qqlv_rec_type;
918 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.DELETE_QQL';
919 l_debug_enabled VARCHAR2(10);
920 is_debug_procedure_on BOOLEAN;
921 is_debug_statement_on BOOLEAN;
922
923 BEGIN
924 l_debug_enabled := NVL(okl_debug_pub.check_log_enabled,'N');
925 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
926 ,fnd_log.level_procedure);
927
928 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
929 okl_debug_pub.log_debug(fnd_log.level_procedure
930 ,l_module
931 ,'begin debug OKLRQQHB.pls call delete_qql');
932 END IF; -- check for logging at STATEMENT level
933 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
934 ,fnd_log.level_statement);
935
936 -- call START_ACTIVITY to create savepoint, check compatibility
937 -- and initialize message list
938
939 l_return_status := okl_api.start_activity(p_api_name => l_api_name
940 ,p_pkg_name => g_pkg_name
941 ,p_init_msg_list => p_init_msg_list
942 ,l_api_version => l_api_version
943 ,p_api_version => p_api_version
944 ,p_api_type => g_api_type
945 ,x_return_status => x_return_status); -- check if activity started successfully
946
947 okl_qql_pvt.delete_row(
948 p_api_version => p_api_version,
949 p_init_msg_list => p_init_msg_list,
950 x_return_status => l_return_status,
951 x_msg_count => x_msg_count,
952 x_msg_data => x_msg_data,
953 p_qqlv_rec => l_qqlv_rec);
954
955 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
956 okl_debug_pub.log_debug(fnd_log.level_statement
957 ,l_module
958 ,'okl_qql_pvt.delete_row returned with status ' ||
959 l_return_status ||
960 ' x_msg_data ' ||
961 x_msg_data);
962 END IF; -- end of l_debug_enabled ='Y'
963
964 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
965 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
966 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
967 RAISE OKL_API.G_EXCEPTION_ERROR;
968 END IF;
969
970 okl_api.end_activity(x_msg_count => x_msg_count
971 ,x_msg_data => x_msg_data);
972
973 x_return_status := l_return_status;
974 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
975 okl_debug_pub.log_debug(fnd_log.level_procedure
976 ,l_module
977 ,'end debug OKLRQQHB.pls call delete_qql');
978 END IF;
979
980 EXCEPTION
981 WHEN okl_api.g_exception_error THEN
982 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
983 ,p_pkg_name => g_pkg_name
984 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
985 ,x_msg_count => x_msg_count
986 ,x_msg_data => x_msg_data
987 ,p_api_type => g_api_type);
988 WHEN okl_api.g_exception_unexpected_error THEN
989 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
990 ,p_pkg_name => g_pkg_name
991 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
992 ,x_msg_count => x_msg_count
993 ,x_msg_data => x_msg_data
994 ,p_api_type => g_api_type);
995 WHEN OTHERS THEN
996 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
997 ,p_pkg_name => g_pkg_name
998 ,p_exc_name => 'OTHERS'
999 ,x_msg_count => x_msg_count
1000 ,x_msg_data => x_msg_data
1001 ,p_api_type => g_api_type);
1002 END delete_qql;
1003
1004 PROCEDURE delete_qql(p_api_version IN NUMBER
1005 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.g_false
1006 ,x_return_status OUT NOCOPY VARCHAR2
1007 ,x_msg_count OUT NOCOPY NUMBER
1008 ,x_msg_data OUT NOCOPY VARCHAR2
1009 ,p_qqlv_tbl_type IN qqlv_tbl_type) IS
1010
1011 l_api_name CONSTANT VARCHAR2(30) := 'delete_qql';
1012 l_api_version CONSTANT NUMBER := 1.0;
1013 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1014 l_qqlv_tbl qqlv_tbl_type := p_qqlv_tbl_type;
1015 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.DELETE_QQL';
1016 l_debug_enabled VARCHAR2(10);
1017 is_debug_procedure_on BOOLEAN;
1018 is_debug_statement_on BOOLEAN;
1019
1020 BEGIN
1021 l_debug_enabled := NVL(okl_debug_pub.check_log_enabled,'N');
1022 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1023 ,fnd_log.level_procedure);
1024
1025 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1026 okl_debug_pub.log_debug(fnd_log.level_procedure
1027 ,l_module
1028 ,'begin debug OKLRQQHB.pls call delete_qql');
1029 END IF; -- check for logging at STATEMENT level
1030 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1031 ,fnd_log.level_statement);
1032
1033 -- call START_ACTIVITY to create savepoint, check compatibility
1034 -- and initialize message list
1035
1036 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1037 ,p_pkg_name => g_pkg_name
1038 ,p_init_msg_list => p_init_msg_list
1039 ,l_api_version => l_api_version
1040 ,p_api_version => p_api_version
1041 ,p_api_type => g_api_type
1042 ,x_return_status => x_return_status); -- check if activity started successfully
1043
1044 okl_qql_pvt.delete_row(
1045 p_api_version => p_api_version,
1046 p_init_msg_list => p_init_msg_list,
1047 x_return_status => l_return_status,
1048 x_msg_count => x_msg_count,
1049 x_msg_data => x_msg_data,
1050 p_qqlv_tbl => l_qqlv_tbl);
1051
1052 IF (l_debug_enabled = 'Y' AND is_debug_statement_on) THEN
1053 okl_debug_pub.log_debug(fnd_log.level_statement
1054 ,l_module
1055 ,'okl_qql_pvt.delete_row returned with status ' ||
1056 l_return_status ||
1057 ' x_msg_data ' ||
1058 x_msg_data);
1059 END IF; -- end of l_debug_enabled ='Y'
1060
1061 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1062 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1063 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1064 RAISE OKL_API.G_EXCEPTION_ERROR;
1065 END IF;
1066
1067 okl_api.end_activity(x_msg_count => x_msg_count
1068 ,x_msg_data => x_msg_data);
1069
1070 x_return_status := l_return_status;
1071 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1072 okl_debug_pub.log_debug(fnd_log.level_procedure
1073 ,l_module
1074 ,'end debug OKLRQQHB.pls call delete_qql');
1075 END IF;
1076
1077 EXCEPTION
1078 WHEN okl_api.g_exception_error THEN
1079 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1080 ,p_pkg_name => g_pkg_name
1081 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
1082 ,x_msg_count => x_msg_count
1083 ,x_msg_data => x_msg_data
1084 ,p_api_type => g_api_type);
1085 WHEN okl_api.g_exception_unexpected_error THEN
1086 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1087 ,p_pkg_name => g_pkg_name
1088 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
1089 ,x_msg_count => x_msg_count
1090 ,x_msg_data => x_msg_data
1091 ,p_api_type => g_api_type);
1092 WHEN OTHERS THEN
1093 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
1094 ,p_pkg_name => g_pkg_name
1095 ,p_exc_name => 'OTHERS'
1096 ,x_msg_count => x_msg_count
1097 ,x_msg_data => x_msg_data
1098 ,p_api_type => g_api_type);
1099 END delete_qql;
1100
1101 --method to create or update the quick quote
1102 PROCEDURE handle_quick_quote(p_api_version IN number
1103 ,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
1104 ,x_return_status OUT NOCOPY varchar2
1105 ,x_msg_count OUT NOCOPY number
1106 ,x_msg_data OUT NOCOPY varchar2
1107 ,p_qqhv_rec_type IN qqhv_rec_type
1108 ,p_qqlv_tbl_type IN qqlv_tbl_type
1109 ,p_cfh_rec_type IN cashflow_hdr_rec
1110 ,p_cfl_tbl_type IN cashflow_level_tbl
1111 ,p_commit IN varchar2
1112 ,create_yn IN varchar2
1113 ,x_payment_rec OUT NOCOPY payment_rec_type
1114 ,x_rent_payments_tbl OUT NOCOPY rent_payments_tbl
1115 ,x_fee_payments_tbl OUT NOCOPY fee_service_payments_tbl
1116 ,x_item_tbl OUT NOCOPY item_order_estimate_tbl
1117 ,x_qqhv_rec_type OUT NOCOPY qqhv_rec_type
1118 ,x_qqlv_tbl_type OUT NOCOPY qqlv_tbl_type
1119 ) IS
1120
1121 l_api_name CONSTANT VARCHAR2(30) := 'handle_quick_quote';
1122 QQ_ACTIVE CONSTANT okl_quick_quotes_b.sts_code%TYPE :='ACTIVE';
1123 l_api_version CONSTANT NUMBER := 1.0;
1124 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
1125
1126 l_qqhv_rec_type qqhv_rec_type := p_qqhv_rec_type;
1127 l_qqlv_tbl_type qqlv_tbl_type := p_qqlv_tbl_type;
1128 l_cfh_rec_type cashflow_hdr_rec := p_cfh_rec_type;
1129 l_cfl_tbl_type cashflow_level_tbl := p_cfl_tbl_type;
1130 l_cfh_other_rec cashflow_hdr_rec ;
1131 l_cfl_other_tbl cashflow_level_tbl ;
1132 lx_qqhv_rec_type qqhv_rec_type;
1133 lx_qqlv_tbl_type qqlv_tbl_type;
1134 lxx_qqlv_tbl_type qqlv_tbl_type;
1135 lp_qqlv_rec qqlv_rec_type;
1136 lx_qqlv_rec qqlv_rec_type;
1137
1138 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.handle_quick_quote';
1139 l_debug_enabled VARCHAR2(10);
1140 is_debug_procedure_on BOOLEAN;
1141 is_debug_statement_on BOOLEAN;
1142 l_item_name VARCHAR2(240);
1143 l_description VARCHAR2(240);
1144 l_so_details so_cash_flow_details_tbl_type;
1145
1146 l_update_cfh_rec cashflow_hdr_rec;
1147 l_update_cfl_tbl cashflow_level_tbl;
1148 l_cf_update_flag VARCHAR2(1):='N';
1149 count_num NUMBER;
1150
1151 i NUMBER;
1152 j NUMBER :=1;
1153 k NUMBER;
1154 l_rate NUMBER;
1155 l_frequency VARCHAR2(1);
1156 line_number NUMBER;
1157 no_of_lines NUMBER;
1158 l_rate_factor NUMBER;
1159 l_total_asset_cost NUMBER:=0;
1160 l_total_rent_payment NUMBER:=0;
1161 rate_card_num NUMBER:=1;
1162 l_months_factor NUMBER;
1163 l_precision NUMBER;
1164 yield_prec NUMBER :=4;
1165 l_oty_code VARCHAR2(100);
1166 l_count NUMBER;
1167 l_line_no NUMBER;
1168 l_cost NUMBER;
1169
1170 l_yields_rec yields_rec;
1171 sub_yields_rec yields_rec;
1172 pricing_results_table pricing_results_tbl_type;
1173 x_qa_result_tbl qa_results_tbl_type;
1174 x_qa_result VARCHAR2(10);
1175
1176 -- cursor to get the frequency from SRT
1177 CURSOR get_srt(template_id IN NUMBER) IS
1178 SELECT hdr.frequency_code, ver.srt_rate
1179 FROM okl_fe_std_rt_tmp_all_b hdr, okl_fe_Std_rt_tmp_vers ver
1180 WHERE ver.std_rate_tmpl_ver_id = template_id AND
1181 hdr.std_rate_tmpl_id = ver.std_rate_tmpl_id;
1182
1183 -- cursor to get the frequency from LRS
1184 CURSOR get_lrs(rate_card_id IN NUMBER) IS
1185 SELECT hdr.frq_code, ver.arrears_yn
1186 FROM okl_ls_rt_fctr_sets_v hdr,
1187 okl_fe_rate_set_versions_v ver
1188 WHERE ver.rate_set_id = hdr.id
1189 AND ver.rate_set_version_id = rate_card_id;
1190
1191 -- cursor to get the category name, descriotion for a category
1192 CURSOR get_cat_name(cp_category_id IN NUMBER) IS
1193 SELECT category_concat_segs item_name,
1194 description description
1195 FROM mtl_categories_v
1196 WHERE category_id = cp_category_id;
1197
1198 -- cursor to fetch the cashflow line details
1199 CURSOR get_cashflow_dtls(p_qqh_id IN NUMBER) IS
1200 SELECT levels.id LEVEL_ID,
1201 levels.amount AMOUNT,
1202 levels.number_of_periods NUMBER_OF_PERIODS,
1203 levels.stub_days STUB_DAYS,
1204 levels.stub_amount STUB_AMOUNT,
1205 levels.start_date START_DATE,
1206 levels.object_version_number ovn,
1207 ql.basis BASIS,
1208 flow.cft_code CFT_CODE,
1209 obj.id OBJ_ID,
1210 flow.id FLOW_ID,
1211 ql.id LINE_ID,
1212 levels.rate,
1213 ql.value VALUE
1214 FROM okl_cash_flow_objects obj, okl_cash_flows flow, okl_cash_flow_levels levels,
1215 okl_quick_quotes_b qh, okl_quick_quote_lines_b ql
1216 where obj.id= flow.cfo_id
1217 AND flow.id= levels.caf_id
1218 AND qh.id= ql.quick_quote_id
1219 AND obj.source_table='OKL_QUICK_QUOTE_LINES_B'
1220 AND obj.source_id= ql.id
1221 AND (ql.basis='ASSET_COST' or ql.basis='RENT')
1222 AND qh.id=p_qqh_id;
1223
1224 --dkagrawa strat
1225 CURSOR get_precision_csr(p_currency_code VARCHAR2)
1226 IS
1227 SELECT NVL(cur.precision,0) precision
1228 FROM fnd_currencies cur
1229 WHERE cur.currency_code = p_currency_code;
1230 --dkagrawa end
1231
1232
1233 CURSOR get_oty_code(qql_id IN NUMBER) IS
1234 SELECT oty_code FROM okl_cash_flow_objects
1235 WHERE source_table= 'OKL_QUICK_QUOTE_LINES_B' AND source_id= qql_id;
1236 -- cursor to fetch the quick quote line ids for a particualr quick quote header
1237 CURSOR get_qq_lines(qqh_id IN NUMBER) IS
1238 SELECT id FROM okl_quick_quote_lines_b where quick_quote_id=qqh_id;
1239
1240 -- cursor to fetch the fees and services
1241 /* sosharma 08-Feb-2008 bug 6692055
1242 Modified the cursor query get_line_dtls in OKL_QUICK_QUOTES_PVT.handle_quick_quote, so
1243 that the composite index would be used.
1244 Start Changes
1245 */
1246 /*
1247 CURSOR get_line_dtls(p_qqh_id IN NUMBER) IS
1248 SELECT levels.amount AMOUNT,
1249 levels.number_of_periods NUMBER_OF_PERIODS,
1250 levels.start_date START_DATE,
1251 ql.TYPE TYPE
1252 FROM okl_cash_flow_objects obj, okl_cash_flows flow, okl_cash_flow_levels levels,
1253 okl_quick_quotes_b qh, okl_quick_quote_lines_b ql
1254 where obj.id= flow.cfo_id
1255 AND flow.id= levels.caf_id
1256 AND qh.id= ql.quick_quote_id
1257 AND obj.source_table='OKL_QUICK_QUOTE_LINES_B'
1258 AND obj.source_id= ql.id
1259 AND ql.type IN ('FEE_EXPENSE','FEE_PAYMENT','INSURANCE','TAX','SERVICE')
1260 AND qh.id=p_qqh_id;
1261 */
1262 CURSOR get_line_dtls(p_qqh_id IN NUMBER) IS
1263 SELECT LEVELS.AMOUNT AMOUNT, LEVELS.NUMBER_OF_PERIODS NUMBER_OF_PERIODS,
1264 LEVELS.START_DATE START_DATE, QL.TYPE TYPE
1265 FROM
1266 OKL_QUICK_QUOTES_B QH,
1267 OKL_QUICK_QUOTE_LINES_B QL,
1268 OKL_CASH_FLOW_OBJECTS OBJ,
1269 OKL_CASH_FLOWS FLOW,
1270 OKL_CASH_FLOW_LEVELS LEVELS,
1271 FND_LOOKUPS LKP
1272 WHERE OBJ.ID= FLOW.CFO_ID
1273 AND FLOW.ID= LEVELS.CAF_ID
1274 AND QH.ID= QL.QUICK_QUOTE_ID
1275 AND LKP.LOOKUP_TYPE = 'OKL_CF_OBJECT_TYPE'
1276 /* The following 3 obj columns are part of the composite index CFO_COMP_I */
1277 AND OBJ.OTY_CODE = LKP.LOOKUP_CODE
1278 AND OBJ.SOURCE_TABLE='OKL_QUICK_QUOTE_LINES_B'
1279 AND OBJ.SOURCE_ID= QL.ID
1280 AND QL.TYPE IN ('FEE_EXPENSE','FEE_PAYMENT','INSURANCE','TAX','SERVICE')
1281 AND QH.ID=p_qqh_id;
1282 /* sosharma end changes */
1283
1284
1285 BEGIN
1286 l_debug_enabled := NVL(okl_debug_pub.check_log_enabled,'N');
1287 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
1288 ,fnd_log.level_procedure);
1289
1290 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
1291 okl_debug_pub.log_debug(fnd_log.level_procedure
1292 ,l_module
1293 ,'begin debug OKLRQQHB.pls call handle_quick_quote');
1294 END IF;
1295 -- check for logging at STATEMENT level
1296 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
1297 ,fnd_log.level_statement);
1298
1299 -- call START_ACTIVITY to create savepoint, check compatibility
1300 -- and initialize message list
1301
1302 l_return_status := okl_api.start_activity(p_api_name => l_api_name
1303 ,p_pkg_name => g_pkg_name
1304 ,p_init_msg_list => p_init_msg_list
1305 ,l_api_version => l_api_version
1306 ,p_api_version => p_api_version
1307 ,p_api_type => g_api_type
1308 ,x_return_status => x_return_status); -- check if activity started successfully
1309
1310 -- establish a savepoint to rollback when p_commit is N
1311 DBMS_TRANSACTION.SAVEPOINT('quick_quote_savepoint');
1312
1313 -- calculate the total asset cost except for solve for financed amount
1314 FOR i in l_qqlv_tbl_type.FIRST..l_qqlv_tbl_type.LAST LOOP
1315 IF (l_qqhv_rec_type.pricing_method<>'SF' and l_qqlv_tbl_type(i).TYPE='ITEM_CATEGORY' ) THEN
1316 l_total_asset_cost:=l_total_asset_cost+ l_qqlv_tbl_type(i).VALUE;
1317 END IF;
1318 END LOOP;
1319
1320 -- calculate the total rent payment except solve for rent payment
1321 IF (l_cfl_tbl_type.COUNT >0) THEN
1322 FOR i in l_cfl_tbl_type.FIRST..l_cfl_tbl_type.LAST LOOP
1323 IF (l_qqhv_rec_type.pricing_method<>'SP' AND l_qqhv_rec_type.pricing_method<>'RC' AND l_qqhv_rec_type.pricing_method<>'TR') THEN
1324 -- Added check for stub amount to be added to the rent payment.
1325 IF (l_cfl_tbl_type(i).periodic_amount IS NOT NULL AND l_cfl_tbl_type(i).periods IS NOT NULL)THEN
1326 l_total_rent_payment:=l_total_rent_payment + l_cfl_tbl_type(i).periodic_amount*l_cfl_tbl_type(i).periods;
1327 ELSIF (l_cfl_tbl_type(i).stub_amount IS NOT NULL AND l_cfl_tbl_type(i).stub_days IS NOT NULL) THEN
1328 l_total_rent_payment:=l_total_rent_payment + l_cfl_tbl_type(i).stub_amount;
1329 END IF;
1330 END IF;
1331 END LOOP;
1332 --Fix Bug # 5184245 ssdeshpa start
1333 ELSIF(l_qqhv_rec_type.rate_template_id IS NOT NULL) THEN
1334 l_total_rent_payment:= l_qqhv_rec_type.term * nvl(l_qqhv_rec_type.target_amount,0);
1335 END IF;
1336 --Fix Bug # 5184245 ssdeshpa end
1337 IF l_qqhv_rec_type.target_frequency IS NOT NULL
1338 THEN
1339 -- getting the value of the frequency for the correspoding code
1340 l_months_factor := okl_stream_generator_pvt.get_months_factor(
1341 p_frequency => l_qqhv_rec_type.target_frequency,
1342 x_return_status => x_return_status);
1343 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1344 RAISE okl_api.g_exception_unexpected_error;
1345 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1346 RAISE okl_api.g_exception_error;
1347 END IF;
1348 END IF;
1349 -- Pricing Method:rate card and pricing option : Structured pricing
1350 IF (l_qqhv_rec_type.pricing_method='RC' AND l_qqhv_rec_type.structured_pricing= 'Y') THEN
1351 l_rate_factor:=l_qqhv_rec_type.lease_rate_factor;
1352 -- line level pricing
1353 IF(l_qqhv_rec_type.line_level_pricing='Y') THEN
1354 FOR i in l_qqlv_tbl_type.FIRST.. l_qqlv_tbl_type.LAST LOOP
1355 IF (l_qqlv_tbl_type(i).type= 'ITEM_CATEGORY' and l_qqlv_tbl_type(i).lease_rate_factor is null) THEN
1356 l_qqlv_tbl_type(i).lease_rate_factor := l_rate_factor;
1357 END IF;
1358 END LOOP;
1359 -- line level pricing disabled
1360 ELSE
1361 FOR i in l_qqlv_tbl_type.FIRST.. l_qqlv_tbl_type.LAST LOOP
1362 IF (l_qqlv_tbl_type(i).type= 'ITEM_CATEGORY' ) THEN
1363 l_qqlv_tbl_type(i).lease_rate_factor := l_rate_factor;
1364 END IF;
1365 END LOOP;
1366 END IF;
1367 END IF;
1368
1369 -- populating the target period in the case of Pricing Method 'Target Rate'
1370 IF (l_qqhv_rec_type.pricing_method='TR'or l_qqhv_rec_type.pricing_method='RC' or
1371 l_qqhv_rec_type.rate_template_id is not null) THEN
1372 IF (l_qqhv_rec_type.rate_card_id is not null) THEN
1373 -- lease rate set
1374 OPEN get_lrs(l_qqhv_rec_type.rate_card_id);
1375 FETCH get_lrs INTO l_frequency, l_qqhv_rec_type.TARGET_ARREARS;
1376 CLOSE get_lrs;
1377
1378 l_months_factor := okl_stream_generator_pvt.get_months_factor(
1379 p_frequency => l_frequency,
1380 x_return_status => x_return_status);
1381 l_qqhv_rec_type.target_periods := l_qqhv_rec_type.term/l_months_factor;
1382
1383 ELSIF (l_qqhv_rec_type.rate_template_id is not null) THEN
1384 -- for an SRT , populate the target periods column
1385 OPEN get_srt(l_qqhv_rec_type.rate_template_id);
1386 FETCH get_srt INTO l_frequency, l_rate;
1387 CLOSE get_srt;
1388
1389 l_months_factor := okl_stream_generator_pvt.get_months_factor(
1390 p_frequency => l_frequency,
1391 x_return_status => x_return_status);
1392
1393 l_qqhv_rec_type.target_periods := l_qqhv_rec_type.term/l_months_factor;
1394 ELSE
1395 l_qqhv_rec_type.target_periods := l_qqhv_rec_type.term/l_months_factor;
1396 -- setting the value of l_frequency
1397 l_frequency:=l_qqhv_rec_type.target_frequency;
1398
1399 END IF;
1400 END IF;
1401
1402 --dkagrawa strat
1403 IF l_qqhv_rec_type.currency_code IS NOT NULL
1404 THEN
1405 OPEN get_precision_csr(l_qqhv_rec_type.currency_code);
1406 FETCH get_precision_csr INTO l_precision;
1407 CLOSE get_precision_csr;
1408 END IF;
1409 --dkagrawa end
1410
1411 IF (create_yn = 'Y') THEN
1412 -- create the quick quote
1413 -- Start added abhsaxen 24-Dec-2005
1414 l_qqhv_rec_type.sts_code := QQ_ACTIVE;
1415 -- end added abhsaxen 24-Dec-2005
1416 create_quick_qte(l_api_version,
1417 p_init_msg_list,
1418 x_return_status,
1419 x_msg_count,
1420 x_msg_data,
1421 l_qqhv_rec_type,
1422 lx_qqhv_rec_type,
1423 l_qqlv_tbl_type,
1424 lx_qqlv_tbl_type);
1425
1426 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1427 RAISE okl_api.g_exception_unexpected_error;
1428 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1429 RAISE okl_api.g_exception_error;
1430 END IF;
1431
1432 ELSIF (create_yn ='N') THEN
1433 -- update the quick quote
1434 --dkagrawa removed the call to update_quick_qte and added following statements
1435 --because in case of update you need to get the lock on underlying tables
1436 --update_quick_qte call is there in the end, so it will update in db
1437 -- viselvar deleting the quick quotes and creating again
1438 -- viselvar making the call to update of the quick quotes
1439 -- update the quick quote
1440
1441 update_qqh(l_api_version,
1442 p_init_msg_list,
1443 x_return_status,
1444 x_msg_count,
1445 x_msg_data,
1446 l_qqhv_rec_type,
1447 lx_qqhv_rec_type);
1448
1449 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1450 RAISE okl_api.g_exception_unexpected_error;
1451 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1452 RAISE okl_api.g_exception_error;
1453 END IF;
1454
1455 l_line_no :=1;
1456 -- delete the existing rows of quick quote lines for the header
1457 FOR quick_quote_line_id IN get_qq_lines(lx_qqhv_rec_type.id) LOOP
1458 lxx_qqlv_tbl_type(l_line_no).id := quick_quote_line_id.id;
1459 l_line_no := l_line_no +1;
1460 END LOOP;
1461
1462 okl_qql_pvt.delete_row(p_api_version,
1463 p_init_msg_list,
1464 x_return_status,
1465 x_msg_count,
1466 x_msg_data,
1467 lxx_qqlv_tbl_type);
1468
1469 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1470 RAISE okl_api.g_exception_unexpected_error;
1471 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1472 RAISE okl_api.g_exception_error;
1473 END IF;
1474
1475
1476 -- if there is a new quick quote line that is added, insert it separately
1477 FOR l_count IN l_qqlv_tbl_type.first .. l_qqlv_tbl_type.last LOOP
1478 -- IF (l_qqlv_tbl_type(l_count).id is null) THEN
1479 l_qqlv_tbl_type(l_count).quick_quote_id:=lx_qqhv_rec_type.id;
1480 -- insert the record
1481 okl_qql_pvt.insert_row(p_api_version
1482 ,okl_api.g_false
1483 ,x_return_status
1484 ,x_msg_count
1485 ,x_msg_data
1486 ,l_qqlv_tbl_type(l_count)
1487 ,lx_qqlv_tbl_type(l_count));
1488
1489 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1490 RAISE okl_api.g_exception_unexpected_error;
1491 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1492 RAISE okl_api.g_exception_error;
1493 END IF;
1494 END LOOP;
1495 -- call to cash flow delete API to delete the cash flows
1496 -- delete the cash flows for the quote type
1497 FOR i in l_qqlv_tbl_type.FIRST.. l_qqlv_tbl_type.LAST LOOP
1498 OPEN get_oty_code(l_qqlv_tbl_type(i).id);
1499 FETCH get_oty_code INTO l_oty_code;
1500 CLOSE get_oty_code;
1501
1502 IF (l_oty_code IS NOT NULL) THEN
1503
1504 OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows (
1505 l_api_version
1506 ,p_init_msg_list
1507 ,OKL_API.G_FALSE
1508 ,l_oty_code --dkagrawa changed source table name to soucrce object code
1509 ,l_qqlv_tbl_type(i).id
1510 ,x_return_status
1511 ,x_msg_count
1512 ,x_msg_data
1513 );
1514 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1515 RAISE okl_api.g_exception_unexpected_error;
1516 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1517 RAISE okl_api.g_exception_error;
1518 END IF;
1519
1520 END IF;
1521 END LOOP;
1522
1523 -- delete cash flows for the header
1524 OKL_LEASE_QUOTE_CASHFLOW_PVT.delete_cashflows (
1525 l_api_version
1526 ,p_init_msg_list
1527 ,OKL_API.G_FALSE
1528 ,'QUICK_QUOTE' --dkagrawa changed source table name to soucrce object code
1529 ,l_qqhv_rec_type.id
1530 ,x_return_status
1531 ,x_msg_count
1532 ,x_msg_data
1533 );
1534
1535 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1536 RAISE okl_api.g_exception_unexpected_error;
1537 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1538 RAISE okl_api.g_exception_error;
1539 END IF;
1540 -- delete the quick quote lines
1541 -- viselvar updated. Instead of deletion and creating the quick quotes,
1542 -- updation of the quick quotes is done
1543 /*delete_qql(l_api_version,
1544 p_init_msg_list,
1545 x_return_status,
1546 x_msg_count,
1547 x_msg_data,
1548 l_qqlv_tbl_type);
1549
1550 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1551 RAISE okl_api.g_exception_unexpected_error;
1552 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1553 RAISE okl_api.g_exception_error;
1554 END IF;
1555
1556 -- delete the quick quote header
1557 okl_qqh_pvt.delete_row(l_api_version,
1558 p_init_msg_list,
1559 x_return_status,
1560 x_msg_count,
1561 x_msg_data,
1562 l_qqhv_rec_type);
1563
1564
1565 -- create the quick quote
1566 create_quick_qte(l_api_version,
1567 p_init_msg_list,
1568 x_return_status,
1569 x_msg_count,
1570 x_msg_data,
1571 l_qqhv_rec_type,
1572 lx_qqhv_rec_type,
1573 l_qqlv_tbl_type,
1574 lx_qqlv_tbl_type);*/
1575 END IF;
1576
1577 -- create cash flows for the rent stream
1578 IF (l_qqhv_rec_type.rate_template_id is not null) THEN
1579 -- standard rate template
1580
1581 -- populate cash flow header record
1582 l_cfh_rec_type.type_code :='INFLOW';
1583 l_cfh_rec_type.stream_type_id := null;
1584 l_cfh_rec_type.arrears_flag := l_qqhv_rec_type.TARGET_ARREARS;
1585 l_cfh_rec_type.parent_object_code := 'QUICK_QUOTE';
1586 l_cfh_rec_type.parent_object_id := lx_qqhv_rec_type.id;
1587 l_cfh_rec_type.frequency_code:= l_frequency;
1588 l_cfh_rec_type.quote_type_code:='QQ';
1589 l_cfh_rec_type.quote_id:=lx_qqhv_rec_type.id;
1590 -- populate cash flow levels
1591 l_cfl_tbl_type(1).periods :=l_qqhv_rec_type.target_periods;
1592 l_cfl_tbl_type(1).periodic_amount :=l_qqhv_rec_type.TARGET_AMOUNT;
1593 l_cfl_tbl_type(1).record_mode := 'CREATE';
1594
1595 --populate the out record
1596 x_rent_payments_tbl(1).rate := l_rate;
1597 x_rent_payments_tbl(1).periods := l_cfl_tbl_type(1).periods;
1598 -- For Solve for Payment pricing method, the target amount will be null
1599 x_rent_payments_tbl(1).periodic_amount := round(l_qqhv_rec_type.TARGET_AMOUNT,l_precision);
1600 x_rent_payments_tbl(1).start_date :=l_qqhv_rec_type.expected_start_date;
1601 ELSIF (l_qqhv_rec_type.structured_pricing='Y' and l_qqhv_rec_type.pricing_method<>'RC') THEN
1602 l_frequency:= p_cfh_rec_type.frequency_code;
1603 l_cfh_rec_type.quote_type_code:='QQ';
1604 l_cfh_rec_type.quote_id:=lx_qqhv_rec_type.id;
1605 l_cfh_rec_type.parent_object_id:=lx_qqhv_rec_type.id;
1606 --populate the out record
1607 FOR k in l_cfl_tbl_type.FIRST..l_cfl_tbl_type.LAST LOOP
1608 x_rent_payments_tbl(k).rate :=l_cfl_tbl_type(k).rate ;
1609 x_rent_payments_tbl(k).periods := l_cfl_tbl_type(k).periods;
1610 x_rent_payments_tbl(k).periodic_amount := round(l_cfl_tbl_type(k).periodic_amount,l_precision);
1611 x_rent_payments_tbl(k).stub_amt := round(l_cfl_tbl_type(k).stub_amount,l_precision);
1612 x_rent_payments_tbl(k).stub_days :=l_cfl_tbl_type(k).stub_days;
1613 x_rent_payments_tbl(k).start_date :=l_cfl_tbl_type(k).start_date;
1614 END LOOP;
1615 END IF;
1616 -- create the cahflows for the rent stream other than the rate card method
1617 if (l_qqhv_rec_type.pricing_method <>'RC' and l_qqhv_rec_type.pricing_method <>'TR') THEN
1618 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow(l_api_version,
1619 p_init_msg_list,
1620 OKL_API.G_FALSE,
1621 l_cfh_rec_type,
1622 l_cfl_tbl_type,
1623 x_return_status,
1624 x_msg_count,
1625 x_msg_data
1626 );
1627
1628 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1629 RAISE okl_api.g_exception_unexpected_error;
1630 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1631 RAISE okl_api.g_exception_error;
1632 END IF;
1633
1634 end if;
1635 -- create the cash flows necessary for the quick quote
1636 FOR i IN lx_qqlv_tbl_type.FIRST.. lx_qqlv_tbl_type.LAST LOOP
1637 -- set the parameters common for FEE_EXPENSE, FEE_PAYMENT, TAX, INSURANCE,SERVICE
1638 l_cfh_other_rec.arrears_flag := l_qqhv_rec_type.TARGET_ARREARS;
1639 l_cfh_other_rec.frequency_code:= l_frequency;
1640 l_cfh_other_rec.parent_object_id := lx_qqlv_tbl_type(i).id;
1641 l_cfh_other_rec.stream_type_id := null;
1642
1643
1644 -- the payment frequency
1645 IF l_qqhv_rec_type.target_frequency IS NOT NULL THEN
1646 l_cfl_other_tbl(1).periods :=l_qqhv_rec_type.TERM/l_months_factor;
1647 ELSE
1648 l_cfl_other_tbl(1).periods := l_qqhv_rec_type.TARGET_PERIODS;
1649 END IF;
1650
1651 if (lx_qqlv_tbl_type(i).basis = 'FIXED') THEN
1652 l_cfl_other_tbl(1).periodic_amount :=l_qqlv_tbl_type(i).VALUE;
1653 ELSIF(lx_qqlv_tbl_type(i).basis = 'ASSET_COST') THEN
1654 l_cfl_other_tbl(1).periodic_amount :=l_qqlv_tbl_type(i).VALUE *l_total_asset_cost*.01 ;
1655 -- viselvar added for Bug 5106098
1656 l_cfl_other_tbl(1).periods :=1;
1657 ELSE
1658 l_cfl_other_tbl(1).periodic_amount :=l_qqlv_tbl_type(i).VALUE *l_total_rent_payment*.01/l_cfl_other_tbl(1).periods;
1659 END IF;
1660
1661 -- set the amount as zero
1662 IF ((l_qqhv_rec_type.pricing_method ='SP' OR l_qqhv_rec_type.pricing_method ='RC'
1663 OR(l_qqhv_rec_type.pricing_method ='TR')) AND (lx_qqlv_tbl_type(i).basis ='RENT')) THEN
1664 l_cfl_other_tbl(1).periodic_amount :=0;
1665 -- update of the cashflow is required if basis is percentage and the amount is not available
1666 l_cf_update_flag := 'Y';
1667 END IF;
1668 IF (l_qqhv_rec_type.pricing_method ='SF' AND lx_qqlv_tbl_type(i).basis ='ASSET_COST') THEN
1669 l_cfl_other_tbl(1).periodic_amount :=0;
1670 -- update of the cashflow is required if basis is percentage and the amount is not available
1671 l_cf_update_flag := 'Y';
1672 END IF;
1673
1674 l_cfh_other_rec.quote_id:=lx_qqhv_rec_type.id;
1675 l_cfh_other_rec.quote_type_code:='QQ';
1676 l_cfl_other_tbl(1).record_mode := 'CREATE';
1677 -- fee expennse
1678 IF (l_qqlv_tbl_type(i).TYPE = 'FEE_EXPENSE' ) THEN
1679 l_cfh_other_rec.type_code:='OUTFLOW';
1680 l_cfh_other_rec.parent_object_code := 'QUICK_QUOTE_FEE';
1681 -- fee payment
1682 ELSIF (l_qqlv_tbl_type(i).TYPE = 'FEE_PAYMENT' ) THEN
1683 l_cfh_other_rec.type_code:='INFLOW';
1684 l_cfh_other_rec.parent_object_code := 'QUICK_QUOTE_FEE';
1685 -- insurance
1686 ELSIF (l_qqlv_tbl_type(i).TYPE = 'INSURANCE' ) THEN
1687 l_cfh_other_rec.type_code:='INFLOW';
1688 l_cfh_other_rec.parent_object_code := 'QUICK_QUOTE_INSURANCE';
1689 -- Tax
1690 ELSIF (l_qqlv_tbl_type(i).TYPE = 'TAX' ) THEN
1691 l_cfh_other_rec.type_code:='INFLOW';
1692 l_cfh_other_rec.parent_object_code := 'QUICK_QUOTE_TAX';
1693 -- Service
1694 ELSIF (l_qqlv_tbl_type(i).TYPE = 'SERVICE' ) THEN
1695 l_cfh_other_rec.type_code:='INFLOW';
1696 l_cfh_other_rec.parent_object_code := 'QUICK_QUOTE_SERVICE';
1697 END IF;
1698
1699 IF (l_qqlv_tbl_type(i).TYPE = 'FEE_EXPENSE' or l_qqlv_tbl_type(i).TYPE = 'FEE_PAYMENT' or
1700 l_qqlv_tbl_type(i).TYPE = 'INSURANCE' or l_qqlv_tbl_type(i).TYPE = 'TAX' or
1701 l_qqlv_tbl_type(i).TYPE = 'SERVICE') THEN
1702
1703 -- create the cahflows for the correspoding streams
1704 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow(l_api_version,
1705 p_init_msg_list,
1706 OKL_API.G_FALSE,
1707 l_cfh_other_rec,
1708 l_cfl_other_tbl,
1709 x_return_status,
1710 x_msg_count,
1711 x_msg_data);
1712
1713 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1714 RAISE okl_api.g_exception_unexpected_error;
1715 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1716 RAISE okl_api.g_exception_error;
1717 END IF;
1718
1719 END IF;
1720 END LOOP;
1721 -- get the number of lines in the quick quote lines table +1 for usage in solve for subsidy
1722 no_of_lines :=l_qqlv_tbl_type.LAST +1;
1723 -- call the API for pricing the quote
1724
1725 /*make a call to the validation API to validate Quick Quotes before pricing
1726 */
1727 okl_sales_quote_qa_pvt.run_qa_checker(
1728 p_api_version => l_api_version,
1729 p_init_msg_list=> p_init_msg_list,
1730 p_object_type => 'QUICKQUOTE',
1731 p_object_id => lx_qqhv_rec_type.id,
1732 x_return_status=> x_return_status,
1733 x_msg_count => x_msg_count,
1734 x_msg_data => x_msg_data,
1735 x_qa_result => x_qa_result,
1736 x_qa_result_tbl => x_qa_result_tbl
1737 );
1738
1739 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1740 RAISE okl_api.g_exception_unexpected_error;
1741 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1742 RAISE okl_api.g_exception_error;
1743 END IF;
1744
1745 -- viselvar added
1746 IF (x_qa_result= 'E') THEN
1747 count_num:=0;
1748 FOR num_of_row IN x_qa_result_tbl.first .. x_qa_result_tbl.last LOOP
1749 IF (x_qa_result_tbl(num_of_row).result_code = 'ERROR' ) THEN
1750 count_num:= count_num +1;
1751 okl_api.set_message(p_app_name => G_APP_NAME,
1752 p_msg_name => x_qa_result_tbl(num_of_row).message_code );
1753 END IF;
1754 if (count_num = 3) then
1755 exit;
1756 end if;
1757 END LOOP;
1758 -- raise the exception
1759 RAISE okl_api.g_exception_error;
1760 END IF;
1761
1762 okl_pricing_utils_pvt.price_quick_quote(
1763 l_api_version,
1764 p_init_msg_list,
1765 x_return_status,
1766 x_msg_count,
1767 x_msg_data,
1768 lx_qqhv_rec_type.id,
1769 l_yields_rec,
1770 sub_yields_rec,
1771 pricing_results_table
1772 );
1773
1774 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1775 RAISE okl_api.g_exception_unexpected_error;
1776 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1777 RAISE okl_api.g_exception_error;
1778 END IF;
1779
1780 FOR i IN pricing_results_table.FIRST ..pricing_results_table.LAST LOOP
1781 IF (pricing_results_table(i).LINE_TYPE='FREE_FORM1') THEN
1782 -- populating the output record
1783
1784 line_number := i;
1785 x_payment_rec.subsidy_amount := round(pricing_results_table(i).subsidy,l_precision);
1786 x_payment_rec.financed_amount := round(pricing_results_table(i).financed_amount);
1787 -- viselvar modified for bug 5124375
1788 -- - nvl(pricing_results_table(i).subsidy,0)
1789 -- - nvl(pricing_results_table(i).down_payment,0)
1790 -- - nvl(pricing_results_table(i).trade_in,0),l_precision);
1791 x_payment_rec.arrears_yn:=l_qqhv_rec_type.TARGET_ARREARS;
1792 x_payment_rec.frequency_code := l_frequency;
1793 x_payment_rec.pre_tax_irr := round(l_yields_rec.pre_tax_irr*100,yield_prec);
1794 x_payment_rec.after_tax_irr := round(l_yields_rec.after_tax_irr*100,yield_prec);
1795 x_payment_rec.book_yield := round(l_yields_rec.bk_yield*100,yield_prec);
1796 x_payment_rec.iir := round(l_yields_rec.iir*100,yield_prec);
1797 x_payment_rec.sub_pre_tax_irr:= round(sub_yields_rec.pre_tax_irr*100,yield_prec);
1798 x_payment_rec.sub_after_tax_irr:= round(sub_yields_rec.after_tax_irr*100,yield_prec);
1799 x_payment_rec.sub_book_yield:= round(sub_yields_rec.bk_yield*100,yield_prec);
1800 x_payment_rec.sub_iir := round(sub_yields_rec.iir*100,yield_prec);
1801 -- solve for yields
1802 -- irrespective of the pricing method populating the yields in the quick quote header table
1803 lx_qqhv_rec_type.iir:= round(l_yields_rec.iir*100,yield_prec);
1804 lx_qqhv_rec_type.booking_yield:= round(l_yields_rec.bk_yield*100,yield_prec);
1805 lx_qqhv_rec_type.pirr:= round(l_yields_rec.pre_tax_irr*100,yield_prec);
1806 lx_qqhv_rec_type.airr:= round(l_yields_rec.after_tax_irr*100,yield_prec);
1807 lx_qqhv_rec_type.sub_iir:= round(sub_yields_rec.iir*100,yield_prec);
1808 lx_qqhv_rec_type.sub_booking_yield:= round(sub_yields_rec.bk_yield*100,yield_prec);
1809 lx_qqhv_rec_type.sub_pirr:= round(sub_yields_rec.pre_tax_irr*100,yield_prec);
1810 lx_qqhv_rec_type.sub_airr:= round(sub_yields_rec.after_tax_irr*100,yield_prec);
1811 -- solve for subsidy
1812 IF (p_qqhv_rec_type.pricing_method = 'SS') THEN
1813 -- viselvar added an explicit create for subsidy lines
1814 lp_qqlv_rec.TYPE:='SUBSIDY';
1815 lp_qqlv_rec.BASIS:='FIXED';
1816 lp_qqlv_rec.VALUE:=round(pricing_results_table(i).subsidy, l_precision);
1817 lp_qqlv_rec.quick_quote_id:=lx_qqhv_rec_type.id;
1818
1819 okl_qql_pvt.insert_row(p_api_version
1820 ,okl_api.g_false
1821 ,x_return_status
1822 ,x_msg_count
1823 ,x_msg_data
1824 ,lp_qqlv_rec
1825 ,lx_qqlv_rec);
1826
1827 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1828 RAISE okl_api.g_exception_unexpected_error;
1829 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1830 RAISE okl_api.g_exception_error;
1831 END IF;
1832 END IF;
1833 -- rate card pricing method
1834 IF (p_qqhv_rec_type.pricing_method = 'RC') THEN
1835 OPEN get_cat_name(pricing_results_table(i).item_category_id);
1836 FETCH get_cat_name INTO l_item_name, l_description;
1837 CLOSE get_cat_name;
1838
1839 FOR n in lx_qqlv_tbl_type.first .. lx_qqlv_tbl_type.last LOOP
1840 IF (lx_qqlv_tbl_type(n).id = pricing_results_table(i).line_id) THEN
1841 l_cost :=lx_qqlv_tbl_type(n).value;
1842 exit;
1843 END IF;
1844 END LOOP;
1845 l_so_details:=pricing_results_table(i).cash_flow_level_tbl;
1846 x_item_tbl(rate_card_num).item_category:= l_item_name;
1847 x_item_tbl(rate_card_num).description:= l_description;
1848 x_item_tbl(rate_card_num).cost:= l_cost;
1849 x_item_tbl(rate_card_num).rate_factor :=l_so_details(1).rate;
1850 x_item_tbl(rate_card_num).periods:=l_so_details(1).number_of_periods;
1851 x_item_tbl(rate_card_num).periodic_amt:=round(l_so_details(1).amount,l_precision);
1852 x_item_tbl(rate_card_num).start_date:=l_so_details(1).start_date;
1853 -- insert into the cash flow table
1854 -- populate cash flow header record
1855 l_cfh_rec_type.type_code:='INFLOW';
1856 l_cfh_rec_type.stream_type_id := null;
1857 l_cfh_rec_type.arrears_flag := lx_qqhv_rec_type.TARGET_ARREARS;
1858 -- as of now passing it as QUICK_QUOTE..has to be passed as QUICK_QUOTE_ASSET
1859 l_cfh_rec_type.parent_object_code := 'QUICK_QUOTE_ASSET';
1860 l_cfh_rec_type.parent_object_id := pricing_results_table(i).line_id;
1861 IF (l_frequency is not null) THEN
1862 l_cfh_rec_type.frequency_code:= l_frequency;
1863 ELSE
1864 l_cfh_rec_type.frequency_code:=lx_qqhv_rec_type.TARGET_FREQUENCY;
1865 END IF;
1866 l_cfh_rec_type.quote_id:=lx_qqhv_rec_type.id;
1867 l_cfh_rec_type.quote_type_code:='QQ';
1868
1869 --populate cash flow lines
1870 l_cfl_tbl_type(1).periods :=l_so_details(1).number_of_periods;
1871 l_cfl_tbl_type(1).periodic_amount :=l_so_details(1).amount;
1872 l_cfl_tbl_type(1).start_date:=l_so_details(1).start_date;
1873 l_cfl_tbl_type(1).record_mode := 'CREATE';
1874
1875 -- total rent payment
1876 --Fix Bug # 5184214 ssdeshpa start
1877 l_total_rent_payment := l_total_rent_payment + l_so_details(1).amount*l_so_details(1).number_of_periods;
1878 --Fix Bug # 5184214 ssdeshpa end
1879
1880 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow(l_api_version,
1881 p_init_msg_list,
1882 OKL_API.G_FALSE,
1883 l_cfh_rec_type,
1884 l_cfl_tbl_type,
1885 x_return_status,
1886 x_msg_count,
1887 x_msg_data
1888 );
1889
1890 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1891 RAISE okl_api.g_exception_unexpected_error;
1892 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1893 RAISE okl_api.g_exception_error;
1894 END IF;
1895
1896 rate_card_num:= rate_card_num + 1;
1897 END IF;
1898 END IF;
1899 END LOOP;
1900
1901 --viselvar fixed Bug 5045505
1902 --when Standard Rate Template is picked
1903 IF (l_qqhv_rec_type.rate_template_id is not null) THEN
1904
1905 l_cfl_tbl_type(1).rate:=pricing_results_table(1).cash_flow_level_tbl(1).rate;
1906 x_rent_payments_tbl(1).rate:=l_cfl_tbl_type(1).rate;
1907
1908 END IF;
1909
1910 -- solve for financed amount
1911 IF (p_qqhv_rec_type.pricing_method = 'SF') THEN
1912 FOR i in l_qqlv_tbl_type.FIRST.. l_qqlv_tbl_type.LAST LOOP
1913 IF (lx_qqlv_tbl_type(i).TYPE= 'ITEM_CATEGORY') THEN
1914 -- Added multiplication factor of 0.01, as value should be amount, not percentage.
1915 lx_qqlv_tbl_type(i).VALUE :=round(lx_qqlv_tbl_type(i).percentage_of_total_cost*pricing_results_table(line_number).financed_amount*0.01,l_precision);
1916 OPEN get_cat_name(lx_qqlv_tbl_type(i).item_category_id);
1917 FETCH get_cat_name INTO l_item_name, l_description;
1918 CLOSE get_cat_name;
1919 x_item_tbl(i).Item_Category:= l_item_name;
1920 x_item_tbl(i).description:= l_description;
1921 x_item_tbl(i).cost:= lx_qqlv_tbl_type(i).VALUE;
1922 x_item_tbl(i).purchase_option_value := round(lx_qqlv_tbl_type(i).end_of_term_value,l_precision);
1923 END IF;
1924 END LOOP;
1925 END IF;
1926 -- solve for payments
1927 IF (p_qqhv_rec_type.pricing_method = 'SP') THEN
1928 l_so_details:=pricing_results_table(line_number).cash_flow_level_tbl;
1929 FOR k in l_cfl_tbl_type.first .. l_cfl_tbl_type.last LOOP
1930 l_cfl_tbl_type(k).start_date:= l_so_details(k).start_date;
1931 -- calculate the total payment amount
1932 l_total_rent_payment := l_total_rent_payment + l_so_details(k).amount*l_so_details(k).number_of_periods;
1933 x_rent_payments_tbl(k).start_date :=l_cfl_tbl_type(k).start_date;
1934 -- Bug 5085836 viselvar added
1935 IF (l_so_details(k).stub_days is not null) THEN
1936 l_cfl_tbl_type(k).stub_amount:= l_so_details(k).amount;
1937 x_rent_payments_tbl(k).stub_amt := round(l_so_details(k).amount,l_precision);
1938 ELSE
1939 l_cfl_tbl_type(k).periodic_amount:= l_so_details(k).amount;
1940 x_rent_payments_tbl(k).periodic_amount := round(l_cfl_tbl_type(k).periodic_amount,l_precision);
1941 END IF;
1942 END LOOP;
1943 END IF;
1944 -- solve for target rate
1945 IF (p_qqhv_rec_type.pricing_method = 'TR') THEN
1946 l_so_details:=pricing_results_table(line_number).cash_flow_level_tbl;
1947 -- populate cash flow header record
1948 l_cfh_rec_type.type_code:='INFLOW';
1949 l_cfh_rec_type.stream_type_id := null;
1950 l_cfh_rec_type.arrears_flag := l_qqhv_rec_type.TARGET_ARREARS;
1951 l_cfh_rec_type.parent_object_code := 'QUICK_QUOTE';
1952 l_cfh_rec_type.parent_object_id := lx_qqhv_rec_type.id;
1953 l_cfh_rec_type.frequency_code:= l_frequency;
1954 l_cfh_rec_type.quote_id:=lx_qqhv_rec_type.id;
1955 l_cfh_rec_type.quote_type_code:= 'QQ';
1956
1957 -- populate cash flow levels
1958 l_cfl_tbl_type(1).periods :=l_so_details(1).number_of_periods;
1959 l_cfl_tbl_type(1).periodic_amount :=l_so_details(1).amount;
1960 l_cfl_tbl_type(1).rate :=lx_qqhv_rec_type.target_rate;
1961 l_cfl_tbl_type(1).start_date:=l_so_details(1).start_date;
1962 l_cfl_tbl_type(1).record_mode:='CREATE';
1963
1964 -- populate the target amount in the quote header
1965 lx_qqhv_rec_type.target_amount :=l_so_details(1).amount;
1966 -- total rent payment
1967 l_total_rent_payment := l_so_details(1).amount*l_so_details(1).number_of_periods;
1968
1969 -- populate the out record
1970 x_rent_payments_tbl(1).rate := lx_qqhv_rec_type.target_rate;
1971 x_rent_payments_tbl(1).periods := l_so_details(1).number_of_periods;
1972 x_rent_payments_tbl(1).periodic_amount := round(l_so_details(1).amount,l_precision);
1973 x_rent_payments_tbl(1).start_date :=l_so_details(1).start_date;
1974
1975 OKL_LEASE_QUOTE_CASHFLOW_PVT.create_cashflow(l_api_version,
1976 p_init_msg_list,
1977 OKL_API.G_FALSE,
1978 l_cfh_rec_type,
1979 l_cfl_tbl_type,
1980 x_return_status,
1981 x_msg_count,
1982 x_msg_data
1983 );
1984
1985 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
1986 RAISE okl_api.g_exception_unexpected_error;
1987 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
1988 RAISE okl_api.g_exception_error;
1989 END IF;
1990
1991 END IF;
1992
1993 -- viselvar added for Bug 5085836
1994 IF(p_qqhv_rec_type.pricing_method = 'SF' OR p_qqhv_rec_type.pricing_method = 'SY'
1995 OR p_qqhv_rec_type.pricing_method = 'SS') THEN
1996 l_so_details:=pricing_results_table(line_number).cash_flow_level_tbl;
1997 FOR i in l_so_details.first..l_so_details.last LOOP
1998 -- set the rate and the start date
1999 x_rent_payments_tbl(i).start_date :=l_so_details(i).start_date;
2000 END LOOP;
2001 END IF;
2002
2003 IF (l_cf_update_flag='Y') THEN
2004 FOR cashflow_dtl_rec IN get_cashflow_dtls(lx_qqhv_rec_type.id) LOOP
2005 IF (cashflow_dtl_rec.cft_code = 'OUTFLOW_SCHEDULE') THEN
2006 l_update_cfh_rec.TYPE_code :='OUTFLOW';
2007 ELSE
2008 l_update_cfh_rec.TYPE_code :='INFLOW';
2009 END IF;
2010 l_update_cfh_rec.arrears_flag:=l_qqhv_rec_type.TARGET_ARREARS;
2011 l_update_cfh_rec.frequency_code:= l_frequency;
2012 -- l_update_cfh_rec.parent_object_code:= ;
2013 l_update_cfh_rec.parent_object_id:=cashflow_dtl_rec.line_id;
2014 l_update_cfh_rec.quote_type_code:= 'QQ';
2015 l_update_cfh_rec.quote_id :=lx_qqhv_rec_type.id;
2016 l_update_cfh_rec.cashflow_header_id:=cashflow_dtl_rec.flow_id;
2017 l_update_cfh_rec.cashflow_object_id:=cashflow_dtl_rec.obj_id;
2018 l_update_cfh_rec.cashflow_header_ovn:=1;
2019
2020 l_update_cfl_tbl(1).cashflow_level_id:=cashflow_dtl_rec.level_id;
2021 l_update_cfl_tbl(1).start_date:=cashflow_dtl_rec.start_date;
2022 l_update_cfl_tbl(1).rate:=cashflow_dtl_rec.rate;
2023 l_update_cfl_tbl(1).stub_amount:=cashflow_dtl_rec.stub_amount;
2024 l_update_cfl_tbl(1).stub_days:=cashflow_dtl_rec.stub_days;
2025 l_update_cfl_tbl(1).periods:=cashflow_dtl_rec.number_of_periods;
2026 if (cashflow_dtl_rec.basis='ASSET_COST') THEN
2027 l_update_cfl_tbl(1).periodic_amount:=cashflow_dtl_rec.value * .01
2028 *pricing_results_table(line_number).financed_amount;
2029 else
2030 l_update_cfl_tbl(1).periodic_amount:=cashflow_dtl_rec.value * .01*l_total_rent_payment
2031 /cashflow_dtl_rec.number_of_periods;
2032 end if;
2033 l_update_cfl_tbl(1).cashflow_level_ovn:=1;
2034 l_update_cfl_tbl(1).record_mode:='UPDATE';
2035
2036 -- updating the cash flows for fees and services for basis other than fixed
2037
2038 OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow(l_api_version,
2039 p_init_msg_list,
2040 OKL_API.G_FALSE,
2041 l_update_cfh_rec,
2042 l_update_cfl_tbl,
2043 x_return_status,
2044 x_msg_count,
2045 x_msg_data
2046 );
2047
2048 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2049 RAISE okl_api.g_exception_unexpected_error;
2050 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2051 RAISE okl_api.g_exception_error;
2052 END IF;
2053
2054 END LOOP;
2055 END IF;
2056
2057 j:=1;
2058 FOR fee_payment_rec IN get_line_dtls(lx_qqhv_rec_type.id) LOOP
2059 -- populate the out record
2060 x_fee_payments_tbl(j).payment_type := fee_payment_rec.TYPE ;
2061 x_fee_payments_tbl(j).periods := fee_payment_rec.number_of_periods;
2062 x_fee_payments_tbl(j).periodic_amt := round(fee_payment_rec.amount,l_precision);
2063 x_fee_payments_tbl(j).start_date := fee_payment_rec.start_date;
2064 j:=j+1;
2065 END LOOP;
2066 -- set the out parameters
2067 x_qqhv_rec_type := lx_qqhv_rec_type;
2068 x_qqlv_tbl_type := lx_qqlv_tbl_type;
2069 IF (p_commit = 'N') THEN
2070 -- rollback the transaction
2071 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('quick_quote_savepoint');
2072 ELSIF (p_commit='Y') THEN
2073 -- update the data in the lease quote tables
2074 update_quick_qte(l_api_version,
2075 p_init_msg_list,
2076 x_return_status,
2077 x_msg_count,
2078 x_msg_data,
2079 lx_qqhv_rec_type,
2080 x_qqhv_rec_type,
2081 lx_qqlv_tbl_type,
2082 x_qqlv_tbl_type);
2083
2084 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2085 RAISE okl_api.g_exception_unexpected_error;
2086 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2087 RAISE okl_api.g_exception_error;
2088 END IF;
2089
2090 -- create the data in the cash flow tables if not already created
2091 l_cfh_rec_type.cashflow_header_ovn:=1;
2092 for i in l_cfl_tbl_type.first..l_cfl_tbl_type.last loop
2093 l_cfl_tbl_type(i).cashflow_level_ovn:=1;
2094 end loop;
2095
2096 IF (p_qqhv_rec_type.pricing_method <> 'SY' and p_qqhv_rec_type.pricing_method <> 'TR' and p_qqhv_rec_type.pricing_method<>'RC'
2097 and p_qqhv_rec_type.pricing_method <> 'SS') THEN
2098 FOR t IN l_cfl_tbl_type.FIRST .. l_cfl_tbl_type.LAST
2099 LOOP
2100 l_cfl_tbl_type(t).record_mode := 'UPDATE';
2101 END LOOP;
2102 OKL_LEASE_QUOTE_CASHFLOW_PVT.update_cashflow(l_api_version,
2103 p_init_msg_list,
2104 OKL_API.G_FALSE,
2105 l_cfh_rec_type,
2106 l_cfl_tbl_type,
2107 x_return_status,
2108 x_msg_count,
2109 x_msg_data
2110 );
2111
2112 IF (x_return_status = okl_api.g_ret_sts_unexp_error) THEN
2113 RAISE okl_api.g_exception_unexpected_error;
2114 ELSIF (x_return_status = okl_api.g_ret_sts_error) THEN
2115 RAISE okl_api.g_exception_error;
2116 END IF;
2117
2118 END IF;
2119 -- commit the transaction
2120 COMMIT;
2121 END IF;
2122
2123 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2124 okl_debug_pub.log_debug(fnd_log.level_procedure
2125 ,l_module
2126 ,'end debug OKLRQQHB.pls call handle_quick_quote');
2127 END IF;
2128 EXCEPTION
2129 WHEN okl_api.g_exception_error THEN
2130 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2131 ,p_pkg_name => g_pkg_name
2132 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2133 ,x_msg_count => x_msg_count
2134 ,x_msg_data => x_msg_data
2135 ,p_api_type => g_api_type);
2136 WHEN okl_api.g_exception_unexpected_error THEN
2137 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2138 ,p_pkg_name => g_pkg_name
2139 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2140 ,x_msg_count => x_msg_count
2141 ,x_msg_data => x_msg_data
2142 ,p_api_type => g_api_type);
2143 WHEN OTHERS THEN
2144 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2145 ,p_pkg_name => g_pkg_name
2146 ,p_exc_name => 'OTHERS'
2147 ,x_msg_count => x_msg_count
2148 ,x_msg_data => x_msg_data
2149 ,p_api_type => g_api_type);
2150
2151 END handle_quick_quote;
2152
2153 --------------------------------
2154 -- PROCEDURE get_quick_quote
2155 --------------------------------
2156 PROCEDURE get_quick_quote(p_quote_id IN okl_quick_quotes_b.id%type,
2157 x_qqhv_rec OUT NOCOPY qqhv_rec_type,
2158 x_qqlv_tbl OUT NOCOPY qqlv_tbl_type,
2159 x_return_status OUT NOCOPY VARCHAR2)
2160 IS
2161
2162 CURSOR c_qqh(p_id IN okl_quick_quotes_b.id%TYPE) IS
2163 SELECT id,
2164 object_version_number,
2165 attribute_category,
2166 attribute1,
2167 attribute2,
2168 attribute3,
2169 attribute4,
2170 attribute5,
2171 attribute6,
2172 attribute7,
2173 attribute8,
2174 attribute9,
2175 attribute10,
2176 attribute11,
2177 attribute12,
2178 attribute13,
2179 attribute14,
2180 attribute15,
2181 reference_number,
2182 expected_start_date,
2183 org_id,
2184 inv_org_id,
2185 currency_code,
2186 term,
2187 end_of_term_option_id,
2188 pricing_method,
2189 lease_opportunity_id,
2190 originating_vendor_id,
2191 program_agreement_id,
2192 sales_rep_id,
2193 sales_territory_id,
2194 structured_pricing,
2195 line_level_pricing,
2196 rate_template_id,
2197 rate_card_id,
2198 lease_rate_factor,
2199 target_rate_type,
2200 target_rate,
2201 target_amount,
2202 target_frequency,
2203 target_arrears,
2204 target_periods,
2205 iir,
2206 sub_iir,
2207 booking_yield,
2208 sub_booking_yield,
2209 pirr,
2210 sub_pirr,
2211 airr,
2212 sub_airr,
2213 sts_code,
2214 short_description,
2215 description,
2216 comments
2217 FROM okl_quick_quotes_v
2218 WHERE id = p_id;
2219
2220 CURSOR c_qql(p_id IN okl_quick_quote_lines_b.quick_quote_id%TYPE) IS
2221 SELECT id,
2222 object_version_number,
2223 attribute_category,
2224 attribute1,
2225 attribute2,
2226 attribute3,
2227 attribute4,
2228 attribute5,
2229 attribute6,
2230 attribute7,
2231 attribute8,
2232 attribute9,
2233 attribute10,
2234 attribute11,
2235 attribute12,
2236 attribute13,
2237 attribute14,
2238 attribute15,
2239 quick_quote_id,
2240 type,
2241 basis,
2242 value,
2243 end_of_term_value_default,
2244 end_of_term_value,
2245 percentage_of_total_cost,
2246 item_category_id,
2247 item_category_set_id,
2248 lease_rate_factor,
2249 short_description,
2250 description,
2251 comments
2252 FROM okl_quick_quote_lines_v
2253 WHERE quick_quote_id = p_id;
2254
2255 i pls_integer := 1;
2256 BEGIN
2257
2258 x_return_status := okl_api.g_ret_sts_success;
2259
2260 OPEN c_qqh(p_id => p_quote_id);
2261 FETCH c_qqh INTO
2262 x_qqhv_rec.id,
2263 x_qqhv_rec.object_version_number,
2264 x_qqhv_rec.attribute_category,
2265 x_qqhv_rec.attribute1,
2266 x_qqhv_rec.attribute2,
2267 x_qqhv_rec.attribute3,
2268 x_qqhv_rec.attribute4,
2269 x_qqhv_rec.attribute5,
2270 x_qqhv_rec.attribute6,
2271 x_qqhv_rec.attribute7,
2272 x_qqhv_rec.attribute8,
2273 x_qqhv_rec.attribute9,
2274 x_qqhv_rec.attribute10,
2275 x_qqhv_rec.attribute11,
2276 x_qqhv_rec.attribute12,
2277 x_qqhv_rec.attribute13,
2278 x_qqhv_rec.attribute14,
2279 x_qqhv_rec.attribute15,
2280 x_qqhv_rec.reference_number,
2281 x_qqhv_rec.expected_start_date,
2282 x_qqhv_rec.org_id,
2283 x_qqhv_rec.inv_org_id,
2284 x_qqhv_rec.currency_code,
2285 x_qqhv_rec.term,
2286 x_qqhv_rec.end_of_term_option_id,
2287 x_qqhv_rec.pricing_method,
2288 x_qqhv_rec.lease_opportunity_id,
2289 x_qqhv_rec.originating_vendor_id,
2290 x_qqhv_rec.program_agreement_id,
2291 x_qqhv_rec.sales_rep_id,
2292 x_qqhv_rec.sales_territory_id,
2293 x_qqhv_rec.structured_pricing,
2294 x_qqhv_rec.line_level_pricing,
2295 x_qqhv_rec.rate_template_id,
2296 x_qqhv_rec.rate_card_id,
2297 x_qqhv_rec.lease_rate_factor,
2298 x_qqhv_rec.target_rate_type,
2299 x_qqhv_rec.target_rate,
2300 x_qqhv_rec.target_amount,
2301 x_qqhv_rec.target_frequency,
2302 x_qqhv_rec.target_arrears,
2303 x_qqhv_rec.target_periods,
2304 x_qqhv_rec.iir,
2305 x_qqhv_rec.sub_iir,
2306 x_qqhv_rec.booking_yield,
2307 x_qqhv_rec.sub_booking_yield,
2308 x_qqhv_rec.pirr,
2309 x_qqhv_rec.sub_pirr,
2310 x_qqhv_rec.airr,
2311 x_qqhv_rec.sub_airr,
2312 x_qqhv_rec.sts_code,
2313 x_qqhv_rec.short_description,
2314 x_qqhv_rec.description,
2315 x_qqhv_rec.comments;
2316 --viselvar modified code
2317
2318 --Bug 7022258-Changed by kkorrapo
2319 --SELECT OKL_QQH_REF_SEQ.nextval INTO x_qqhv_rec.reference_number FROM DUAL;
2320 x_qqhv_rec.reference_number := okl_util.get_next_seq_num('OKL_QQH_REF_SEQ','OKL_QUICK_QUOTES_B','REFERENCE_NUMBER');
2321 --Bug 7022258--Change end
2322
2323 IF c_qqh%NOTFOUND THEN
2324 x_return_status := okl_api.g_ret_sts_error;
2325 END IF;
2326 CLOSE c_qqh;
2327
2328 FOR l_qql IN c_qql(p_id => p_quote_id) LOOP
2329 x_qqlv_tbl(i).id := l_qql.id;
2330 x_qqlv_tbl(i).object_version_number := l_qql.object_version_number;
2331 x_qqlv_tbl(i).attribute_category :=l_qql.attribute_category;
2332 x_qqlv_tbl(i).attribute1 := l_qql.attribute1;
2333 x_qqlv_tbl(i).attribute2 := l_qql.attribute2;
2334 x_qqlv_tbl(i).attribute3 := l_qql.attribute3;
2335 x_qqlv_tbl(i).attribute4 := l_qql.attribute4;
2336 x_qqlv_tbl(i).attribute5 := l_qql.attribute5;
2337 x_qqlv_tbl(i).attribute6 := l_qql.attribute6;
2338 x_qqlv_tbl(i).attribute7 := l_qql.attribute7;
2339 x_qqlv_tbl(i).attribute8 := l_qql.attribute8;
2340 x_qqlv_tbl(i).attribute9 := l_qql.attribute9;
2341 x_qqlv_tbl(i).attribute10 := l_qql.attribute10;
2342 x_qqlv_tbl(i).attribute11 := l_qql.attribute11;
2343 x_qqlv_tbl(i).attribute12 := l_qql.attribute12;
2344 x_qqlv_tbl(i).attribute13 := l_qql.attribute13;
2345 x_qqlv_tbl(i).attribute14 := l_qql.attribute14;
2346 x_qqlv_tbl(i).attribute15 := l_qql.attribute15;
2347 x_qqlv_tbl(i).quick_quote_id := l_qql.quick_quote_id;
2348 x_qqlv_tbl(i).type := l_qql.type;
2349 x_qqlv_tbl(i).basis := l_qql.basis;
2350 x_qqlv_tbl(i).value := l_qql.value;
2351 x_qqlv_tbl(i).end_of_term_value_default := l_qql.end_of_term_value_default;
2352 x_qqlv_tbl(i).end_of_term_value := l_qql.end_of_term_value;
2353 x_qqlv_tbl(i).percentage_of_total_cost := l_qql.percentage_of_total_cost;
2354 x_qqlv_tbl(i).item_category_id := l_qql.item_category_id;
2355 x_qqlv_tbl(i).item_category_set_id := l_qql.item_category_set_id;
2356 x_qqlv_tbl(i).lease_rate_factor := l_qql.lease_rate_factor;
2357 x_qqlv_tbl(i).short_description := l_qql.short_description;
2358 x_qqlv_tbl(i).description := l_qql.description;
2359 x_qqlv_tbl(i).comments := l_qql.comments;
2360
2361 i := i+1;
2362 END LOOP;
2363
2364 END get_quick_quote;
2365 ------------------------------------------------------------------------------
2366 -- PROCEDURE duplicate_quick_qte
2367 ------------------------------------------------------------------------------
2368 -- Start of comments
2369 --
2370 -- Procedure Name : duplicate_quick_qte
2371 -- Description : This procedure is a wrapper that duplicates estimates of a
2372 -- particular lease opportunity
2373 -- Parameters :
2374 -- Version : 1.0
2375 -- History : 15-feb-2006 viselvar created
2376 --
2377 -- End of comments
2378
2379 PROCEDURE duplicate_estimate ( p_api_version IN NUMBER,
2380 p_init_msg_list IN VARCHAR2,
2381 source_lopp_id IN NUMBER,
2382 target_lopp_id IN NUMBER,
2383 x_return_status OUT NOCOPY VARCHAR2,
2384 x_msg_count OUT NOCOPY NUMBER,
2385 x_msg_data OUT NOCOPY VARCHAR2) IS
2386
2387 l_quote_rec qqhv_rec_type;
2388 l_quote_lines_tbl qqlv_tbl_type;
2389 x_quick_qte_rec qqhv_rec_type;
2390 x_quick_qte_lines_tbl qqlv_tbl_type;
2391
2392 l_module CONSTANT fnd_log_messages.module%TYPE := 'okl.plsql.OKL_QUICK_QUOTES_PVT.DUPLICATE_QUICK_QTE';
2393 l_debug_enabled VARCHAR2(10);
2394 is_debug_procedure_on BOOLEAN;
2395 is_debug_statement_on BOOLEAN;
2396 l_api_name CONSTANT VARCHAR2(30) := 'duplicate_quick_qte';
2397 l_api_version CONSTANT NUMBER := 1.0;
2398 l_return_status VARCHAR2(1) := okl_api.g_ret_sts_success;
2399
2400 -- cursor to get the estimates of a particular lease opportunity
2401 CURSOR get_estimates(l_lopp_id IN NUMBER) IS
2402 SELECT ID FROM OKL_QUICK_QUOTES_B
2403 WHERE LEASE_OPPORTUNITY_ID = l_lopp_id;
2404
2405 BEGIN
2406
2407 l_debug_enabled := nvl(okl_debug_pub.check_log_enabled,'N');
2408 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module
2409 ,fnd_log.level_procedure);
2410
2411 IF (l_debug_enabled = 'Y' AND is_debug_procedure_on) THEN
2412 okl_debug_pub.log_debug(fnd_log.level_procedure
2413 ,l_module
2414 ,'begin debug OKLRQQHB.pls call duplicate_quick_qte');
2415 END IF;
2416 -- check for logging on STATEMENT level
2417 is_debug_statement_on := okl_debug_pub.check_log_on(l_module
2418 ,fnd_log.level_statement);
2419
2420 -- call START_ACTIVITY to create savepoint, check compatibility
2421 -- and initialize message list
2422
2423 l_return_status := okl_api.start_activity(p_api_name => l_api_name
2424 ,p_pkg_name => g_pkg_name
2425 ,p_init_msg_list => p_init_msg_list
2426 ,l_api_version => l_api_version
2427 ,p_api_version => p_api_version
2428 ,p_api_type => g_api_type
2429 ,x_return_status => x_return_status);
2430
2431 FOR quote_id IN get_estimates(source_lopp_id) LOOP
2432
2433 get_quick_quote ( p_quote_id => quote_id.id,
2434 x_qqhv_rec => l_quote_rec,
2435 x_qqlv_tbl => l_quote_lines_tbl,
2436 x_return_status => l_return_status );
2437
2438 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2439 RAISE okl_api.g_exception_unexpected_error;
2440 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2441 RAISE okl_api.g_exception_error;
2442 END IF;
2443
2444 -- set the target lease opportunity id
2445 l_quote_rec.lease_opportunity_id:=target_lopp_id;
2446
2447 create_quick_qte (p_api_version => p_api_version,
2448 p_init_msg_list => p_init_msg_list,
2449 x_return_status => l_return_status,
2450 x_msg_count => x_msg_count,
2451 x_msg_data => x_msg_data,
2452 p_qqhv_rec_type => l_quote_rec,
2453 x_qqhv_rec_type => x_quick_qte_rec,
2454 p_qqlv_tbl_type => l_quote_lines_tbl,
2455 x_qqlv_tbl_type => x_quick_qte_lines_tbl);
2456
2457 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
2458 RAISE okl_api.g_exception_unexpected_error;
2459 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
2460 RAISE okl_api.g_exception_error;
2461 END IF;
2462
2463 END LOOP;
2464
2465 x_return_status := okl_api.g_ret_sts_success;
2466
2467 EXCEPTION
2468
2469 WHEN okl_api.g_exception_error THEN
2470 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2471 ,p_pkg_name => g_pkg_name
2472 ,p_exc_name => 'OKL_API.G_RET_STS_ERROR'
2473 ,x_msg_count => x_msg_count
2474 ,x_msg_data => x_msg_data
2475 ,p_api_type => g_api_type);
2476 WHEN okl_api.g_exception_unexpected_error THEN
2477 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2478 ,p_pkg_name => g_pkg_name
2479 ,p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR'
2480 ,x_msg_count => x_msg_count
2481 ,x_msg_data => x_msg_data
2482 ,p_api_type => g_api_type);
2483 WHEN OTHERS THEN
2484 x_return_status := okl_api.handle_exceptions(p_api_name => l_api_name
2485 ,p_pkg_name => g_pkg_name
2486 ,p_exc_name => 'OTHERS'
2487 ,x_msg_count => x_msg_count
2488 ,x_msg_data => x_msg_data
2489 ,p_api_type => g_api_type);
2490
2491 END duplicate_estimate;
2492
2493
2494
2495 END okl_quick_quotes_pvt;