[Home] [Help]
PACKAGE BODY: APPS.OKS_REPRICE_PVT
Source
1 PACKAGE BODY oks_reprice_pvt AS
2 /* $Header: OKSRRPRB.pls 120.35.12020000.3 2012/12/13 14:07:36 spingali ship $*/
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7 g_module CONSTANT VARCHAR2 (250)
8 := 'oks.plsql.pricing.' ||
9 g_pkg_name ||
10 '.';
11
12 -- 08/05/2005 -- JVORUGAN R12 Partial Period Project
13 -- This procedure will update Price UOM at line and subline levels when the
14 -- Renewal Pricing type is Price List and Period Type and Period Start are not null
15 -- for the contract.
16 PROCEDURE update_price_uom (
17 p_chr_id IN NUMBER,
18 p_price_uom IN VARCHAR2, -- Bug 5139658
19 x_return_status OUT NOCOPY VARCHAR2
20 ) IS
21 l_api_name CONSTANT VARCHAR2 (30)
22 := 'update_price_uom';
23 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
24 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
25 l_k_det_rec oks_qp_int_pvt.k_details_rec;
26 l_return_status VARCHAR2 (1)
27 := okc_api.g_ret_sts_success;
28 l_msg_count NUMBER;
29 l_msg_data VARCHAR2 (2000);
30 l_price_uom oks_k_headers_b.price_uom%TYPE;
31 invalid_uom_exception EXCEPTION;
32
33 CURSOR cur_get_lines IS
34 SELECT oks.ID,
35 oks.cle_id,
36 oks.object_version_number -- bug 4638902
37 FROM okc_k_lines_b okc,
38 oks_k_lines_b oks
39 WHERE okc.dnz_chr_id = p_chr_id
40 AND okc.lse_id IN (1, 7, 9, 25, 19)
41 AND okc.ID = oks.cle_id;
42
43 CURSOR cur_hdr_uom IS
44 SELECT price_uom
45 FROM oks_k_headers_b
46 WHERE chr_id = p_chr_id;
47 BEGIN
48 -- start debug log
49 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
50 fnd_log.STRING (fnd_log.level_procedure,
51 g_module ||
52 l_api_name,
53 '100: Entered ' ||
54 g_pkg_name ||
55 '.' ||
56 l_api_name
57 );
58 END IF;
59
60 -- Bug 5139658
61
62 /*
63 OPEN cur_hdr_uom;
64
65 FETCH cur_hdr_uom
66 INTO l_price_uom;
67
68 IF cur_hdr_uom%NOTFOUND THEN
69 CLOSE cur_hdr_uom;
70
71 RAISE invalid_uom_exception;
72 END IF;
73
74 CLOSE cur_hdr_uom;
75
76 */
77
78 FOR cur_rec IN cur_get_lines LOOP
79 l_kln_rec_in.ID := cur_rec.ID;
80 l_kln_rec_in.cle_id := cur_rec.cle_id;
81 l_kln_rec_in.dnz_chr_id := p_chr_id;
82
83 l_kln_rec_in.price_uom := p_price_uom; -- Bug 5139658
84 -- bug 4638902
85 l_kln_rec_in.object_version_number := cur_rec.object_version_number;
86 -- bug 4638902
87 oks_contract_line_pub.update_line (p_api_version => 1.0,
88 p_init_msg_list => 'T',
89 x_return_status => l_return_status,
90 x_msg_count => l_msg_count,
91 x_msg_data => l_msg_data,
92 p_klnv_rec => l_kln_rec_in,
93 x_klnv_rec => l_kln_rec_out,
94 p_validate_yn => 'N'
95 );
96
97 IF l_return_status <> okc_api.g_ret_sts_success THEN
98 RAISE g_error;
99 END IF;
100 END LOOP;
101
102 x_return_status := l_return_status;
103
104 -- end debug log
105 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
106 fnd_log.STRING (fnd_log.level_procedure,
107 g_module ||
108 l_api_name,
109 '1000: Leaving ' ||
110 g_pkg_name ||
111 '.' ||
112 l_api_name
113 );
114 END IF;
115 EXCEPTION
116 WHEN invalid_uom_exception THEN
117 -- end debug log
118 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
119 fnd_log.STRING (fnd_log.level_procedure,
120 g_module ||
121 l_api_name,
122 '9500: Leaving ' ||
123 g_pkg_name ||
124 '.' ||
125 l_api_name
126 );
127 END IF;
128
129 x_return_status := g_ret_sts_unexp_error;
130 OKC_API.SET_MESSAGE(p_app_name => 'OKS',
131 p_msg_name => 'OKS_INVD_UOM_CODE',
132 p_token1 => 'OKS_API_NAME',
133 p_token1_value => 'oks_reprice_pvt.update_price_uom',
134 p_token2 => 'UOM_CODE',
135 p_token2_value => p_price_uom);
136 WHEN OTHERS THEN
137 -- end debug log
138 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
139 fnd_log.STRING (fnd_log.level_procedure,
140 g_module ||
141 l_api_name,
142 '4000: Leaving ' ||
143 g_pkg_name ||
144 '.' ||
145 l_api_name
146 );
147 END IF;
148
149 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
150 x_return_status := g_ret_sts_unexp_error;
151 okc_api.set_message (p_app_name => g_app_name_oks,
152 p_msg_name => g_unexpected_error,
153 p_token1 => g_sqlcode_token,
154 p_token1_value => SQLCODE,
155 p_token2 => g_sqlerrm_token,
156 p_token2_value => SQLERRM
157 );
158 END update_price_uom;
159
160 FUNCTION do_prorating (
161 p_old_start_date IN DATE,
162 p_old_end_date IN DATE,
163 p_new_start_date IN DATE,
164 p_new_end_date IN DATE,
165 p_amount IN NUMBER
166 )
167 RETURN NUMBER IS
168 l_api_name CONSTANT VARCHAR2 (30) := 'do_prorating';
169 l_old_duration NUMBER;
170 l_new_duration NUMBER;
171 l_prorated_amount NUMBER;
172 l_duration NUMBER;
173 l_period VARCHAR2 (10);
174 l_return_status VARCHAR2 (1);
175 l_old_start_date DATE;
176 l_old_end_date DATE;
177 l_new_start_date DATE;
178 l_new_end_date DATE;
179
180 CURSOR l_time_csr (
181 l_uom_code VARCHAR2
182 ) IS
183 SELECT tce_code
184 FROM okc_time_code_units_v
185 WHERE uom_code = l_uom_code
186 AND quantity = 1;
187
188 CURSOR l_time_csr_noqty (
189 l_uom_code VARCHAR2
190 ) IS
191 SELECT tce_code
192 FROM okc_time_code_units_v
193 WHERE uom_code = l_uom_code;
194
195 l_old_tce_code VARCHAR2 (25);
196 l_new_tce_code VARCHAR2 (25);
197 BEGIN
198 -- start debug log
199 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
200 fnd_log.STRING (fnd_log.level_procedure,
201 g_module ||
202 l_api_name,
203 '100: Entered ' ||
204 g_pkg_name ||
205 '.' ||
206 l_api_name
207 );
208 END IF;
209
210 --------- Truncation of start date and end date added for bug # 2660399 ------
211 l_old_start_date := TRUNC (p_old_start_date);
212 l_old_end_date := TRUNC (p_old_end_date);
213 l_new_start_date := TRUNC (p_new_start_date);
214 l_new_end_date := TRUNC (p_new_end_date);
215 --------------- End truncation ------------------------------------------
216 -- bug 4919612 SKEKKAR
217 -- okc_time_util_pub.get_duration (p_start_date => l_old_start_date,
218 okc_time_util_pvt.get_pricing_duration (p_start_date => l_old_start_date,
219 p_end_date => l_old_end_date,
220 x_duration => l_old_duration, -- bug 4895901
221 x_timeunit => l_old_tce_code, -- bug 5230438
222 x_return_status => l_return_status
223 );
224
225 /* bug 5230438
226 OPEN l_time_csr (l_period);
227 FETCH l_time_csr
228 INTO l_old_tce_code;
229 CLOSE l_time_csr;
230 */
231
232 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
233 fnd_log.STRING (fnd_log.level_statement,
234 g_module ||
235 l_api_name,
236 '100:Old Tce Code: ' ||
237 l_old_tce_code
238 );
239 END IF;
240
241 ------------errorout_ad('Old Tce Code: ' || l_old_tce_Code);
242 -- bug 4919612 SKEKKAR
243 -- okc_time_util_pub.get_duration (p_start_date => l_new_start_date,
244 okc_time_util_pvt.get_pricing_duration (p_start_date => l_new_start_date,
245 p_end_date => l_new_end_date,
246 x_duration => l_new_duration, -- bug 4895901
247 x_timeunit => l_new_tce_code, --bug 5230438
248 x_return_status => l_return_status
249 );
250
251 /* bug 5230438
252 OPEN l_time_csr (l_period);
253 FETCH l_time_csr
254 INTO l_new_tce_code;
255 IF l_time_csr%NOTFOUND THEN
256 OPEN l_time_csr_noqty (l_period);
257 FETCH l_time_csr_noqty
258 INTO l_new_tce_code;
259 CLOSE l_time_csr_noqty;
260 END IF;
261 CLOSE l_time_csr;
262 */
263
264 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
265 fnd_log.STRING (fnd_log.level_statement,
266 g_module ||
267 l_api_name,
268 '101:New Tce Code: ' ||
269 l_new_tce_code
270 );
271 END IF;
272
273 ------------errorout_ad('New Tce Code: ' || l_new_tce_Code);
274 IF l_old_tce_code = 'YEAR'
275 AND l_new_tce_code = 'YEAR' THEN
276 -- bug 4895901 skekkar
277 l_old_duration := l_old_duration * 12;
278 l_new_duration := l_new_duration * 12;
279 /*
280 l_old_duration :=
281 TRUNC (MONTHS_BETWEEN (l_old_end_date +
282 1, l_old_start_date));
283 l_new_duration :=
284 TRUNC (MONTHS_BETWEEN (l_new_end_date +
285 1, l_new_start_date));
286 */
287 ELSIF l_old_tce_code = 'MONTH'
288 AND l_new_tce_code = 'MONTH' THEN
289 -- bug 4895901 skekkar
290 null;
291 /*
292 l_old_duration :=
293 TRUNC (MONTHS_BETWEEN (l_old_end_date +
294 1, l_old_start_date));
295 l_new_duration :=
296 TRUNC (MONTHS_BETWEEN (l_new_end_date +
297 1, l_new_start_date));
298 */
299 ELSIF l_old_tce_code = 'MONTH'
300 AND l_new_tce_code = 'YEAR' THEN
301 -- bug 4895901 skekkar
302 l_new_duration := l_new_duration * 12;
303 /*
304 l_old_duration :=
305 TRUNC (MONTHS_BETWEEN (l_old_end_date +
306 1, l_old_start_date));
307 l_new_duration :=
308 TRUNC (MONTHS_BETWEEN (l_new_end_date +
309 1, l_new_start_date));
310 */
311 ELSIF l_old_tce_code = 'YEAR'
312 AND l_new_tce_code = 'MONTH' THEN
313 -- bug 4895901 skekkar
314 l_old_duration := l_old_duration * 12;
315 /*
316 l_old_duration :=
317 TRUNC (MONTHS_BETWEEN (l_old_end_date +
318 1, l_old_start_date));
319 l_new_duration :=
320 TRUNC (MONTHS_BETWEEN (l_new_end_date +
321 1, l_new_start_date));
322 */
323 ELSE
324 l_old_duration := l_old_end_date -
325 l_old_start_date +
326 1;
327 l_new_duration := l_new_end_date -
328 l_new_start_date +
329 1;
330 END IF;
331
332 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
333 fnd_log.STRING (fnd_log.level_statement,
334 g_module ||
335 l_api_name,
336 '102:Amount: ' ||
337 p_amount
338 );
339 fnd_log.STRING (fnd_log.level_statement,
340 g_module ||
341 l_api_name,
342 '103:Old Duration: ' ||
343 l_old_duration
344 );
345 fnd_log.STRING (fnd_log.level_statement,
346 g_module ||
347 l_api_name,
348 '104:New Duration: ' ||
349 l_new_duration
350 );
351 END IF;
352
353 ------------errorout_ad('Amount: ' || p_amount);
354 ------------errorout_ad('Old Duration: ' || l_old_duration);
355 ------------errorout_ad('New Duration: ' || l_new_duration);
356 l_prorated_amount :=
357 (l_new_duration *
358 NVL (p_amount, 0)) /
359 NVL (l_old_duration, 1);
360
361 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
362 fnd_log.STRING (fnd_log.level_statement,
363 g_module ||
364 l_api_name,
365 '105:Prorated Amount' ||
366 l_prorated_amount
367 );
368 END IF;
369
370 ------------errorout_ad('Prorated Amount' || l_prorated_amount);
371
372 -- end debug log
373 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
374 fnd_log.STRING (fnd_log.level_procedure,
375 g_module ||
376 l_api_name,
377 '1000: Leaving ' ||
378 g_pkg_name ||
379 '.' ||
380 l_api_name
381 );
382 END IF;
383
384 RETURN (l_prorated_amount);
385 END do_prorating;
386
387 -- Added by JVORUGAN
388 -- In Do_Prorating_pp Proration is done based on new partial period logic
389 FUNCTION do_prorating_pp (
390 p_id IN NUMBER DEFAULT NULL,
391 p_lse_id IN NUMBER DEFAULT NULL,
392 p_unit_price IN NUMBER DEFAULT NULL,
393 p_old_start_date IN DATE,
394 p_old_end_date IN DATE,
395 p_new_start_date IN DATE,
396 p_new_end_date IN DATE,
397 p_amount IN NUMBER,
398 p_period_type IN VARCHAR2,
399 p_period_start IN VARCHAR2
400 )
401 RETURN NUMBER IS
402 l_api_name CONSTANT VARCHAR2 (30)
403 := 'do_prorating_pp';
404
405 CURSOR l_oks_elements (p_line_id NUMBER) IS
406 SELECT itm.number_of_items,kln.toplvl_uom_code,lin.date_terminated
407 FROM oks_k_lines_b kln,
408 okc_k_items itm,
409 okc_k_lines_b lin
410 WHERE kln.cle_id = p_line_id
411 and itm.cle_id = kln.cle_id
412 and lin.id = p_line_id;
413
414 Cursor check_sub_instance IS
415 select 'Y'
416 from okc_k_items itm,
417 oks_subscr_header_b sub
418 where itm.cle_id = p_id
419 and sub.instance_id = itm.object1_id1;
420
421 CURSOR get_orig_system_id1 IS
422 SELECT orig_system_id1
423 FROM okc_k_lines_b
424 WHERE ID = p_id;
425
426 l_oks_elements_rec l_oks_elements%ROWTYPE;
427 l_old_duration NUMBER;
428 l_new_duration NUMBER;
429 l_prorated_amount NUMBER;
430 l_period VARCHAR2 (30);
431 l_return_status VARCHAR2 (1);
432 l_old_start_date DATE;
433 l_old_end_date DATE;
434 l_new_start_date DATE;
435 l_new_end_date DATE;
436 l_quantity NUMBER;
437 l_unit_price NUMBER;
438 l_sub_instance_check VARCHAR2(1);
439 l_orig_system_id NUMBER;
440
441 BEGIN
442 -- start debug log
443 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
444 fnd_log.STRING (fnd_log.level_procedure,
445 g_module ||
446 l_api_name,
447 '100: Entered ' ||
448 g_pkg_name ||
449 '.' ||
450 l_api_name
451 );
452 END IF;
453
454 --------- Truncation of start date and end date ------
455 l_old_start_date := TRUNC (p_old_start_date);
456 l_old_end_date := TRUNC (p_old_end_date);
457 l_new_start_date := TRUNC (p_new_start_date);
458 l_new_end_date := TRUNC (p_new_end_date);
459 --------------- End truncation -----------------------
460 okc_time_util_pub.get_duration (p_start_date => l_old_start_date,
461 p_end_date => l_old_end_date,
462 x_duration => l_old_duration,
463 x_timeunit => l_period,
464 x_return_status => l_return_status
465 );
466
467 l_sub_instance_check := NULL;
468 --subscription instance can only be attached as service product/Exntended warranty product
469 IF p_lse_id in (9,25) THEN
470 Open check_sub_instance;
471 Fetch check_sub_instance into l_sub_instance_check;
472 Close check_sub_instance;
473 END IF;
474
475 IF p_lse_id in (7,9,25) AND (nvl(l_sub_instance_check,'X') <> 'Y') THEN
476 open l_oks_elements(p_id);
477 fetch l_oks_elements into l_oks_elements_rec;
478 close l_oks_elements;
479 okc_time_util_pub.get_duration (p_start_date => l_new_start_date,
480 p_end_date => l_new_end_date,
481 x_duration => l_new_duration,
482 x_timeunit => l_period,
483 x_return_status => l_return_status
484 );
485 l_new_duration :=
486 oks_time_measures_pub.get_quantity (p_start_date => l_new_start_date,
487 p_end_date => l_new_end_date,
488 ----Fix for bug#5623498 added duration based uom in the nvl condition
489 p_source_uom => nvl(l_oks_elements_rec.toplvl_uom_code,l_period),
490 p_period_type => p_period_type,
491 p_period_start => p_period_start
492 );
493 -- IF p_lse_id in (7,9) THEN
494 -- l_unit_price := p_unit_price;
495 -- ELSE
496 l_quantity := OKS_TIME_MEASURES_PUB.get_quantity
497 (p_start_date => l_old_start_date,
498 p_end_date => nvl(l_oks_elements_rec.date_terminated-1,l_old_end_date),
499 ----Fix for bug#5623498 added duration based uom in the nvl condition
500 p_source_uom => nvl(l_oks_elements_rec.toplvl_uom_code,l_period),
501 p_period_type => p_period_type,
502 p_period_start => p_period_start);
503 l_unit_price := p_amount / l_quantity;
504 -- END IF;
505 l_prorated_amount :=(l_unit_price)*l_new_duration; --(l_unit_price)*l_oks_elements_rec.number_of_items*l_new_duration; --Bug 5337890
506 ELSIF p_lse_id =46 THEN
507 Open get_orig_system_id1;
508 Fetch get_orig_system_id1 into l_orig_system_id;
509 Close get_orig_system_id1;
510 oks_subscription_pub.get_subs_qty
511 (p_cle_id => l_orig_system_id,
512 x_return_status => l_return_status,
513 x_quantity => l_quantity,
514 x_uom_code => l_period
515 );
516 l_unit_price := p_amount / l_quantity;
517 oks_subscription_pub.get_subs_qty
518 (p_cle_id => p_id,
519 x_return_status => l_return_status,
520 x_quantity => l_quantity,
521 x_uom_code => l_period
522 );
523 l_prorated_amount := l_unit_price*l_quantity;
524 ELSE
525
526 l_new_duration :=
527 oks_time_measures_pub.get_quantity (p_start_date => l_new_start_date,
528 p_end_date => l_new_end_date,
529 p_source_uom => l_period,
530 p_period_type => p_period_type,
531 p_period_start => p_period_start
532 );
533 l_prorated_amount :=
534 (l_new_duration *
535 NVL (p_amount, 0)) /
536 NVL (l_old_duration, 1);
537 END IF;
538 -- end debug log
539 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
540 fnd_log.STRING (fnd_log.level_procedure,
541 g_module ||
542 l_api_name,
543 '1000: Leaving ' ||
544 g_pkg_name ||
545 '.' ||
546 l_api_name
547 );
548 END IF;
549
550 RETURN (l_prorated_amount);
551 END do_prorating_pp;
552
553 PROCEDURE get_oldcontractline_dates (
554 p_cle_id IN NUMBER,
555 p_opn_code IN VARCHAR2,
556 x_old_start_date OUT NOCOPY DATE,
557 x_old_end_date OUT NOCOPY DATE
558 ) IS
559 l_api_name CONSTANT VARCHAR2 (30)
560 := 'get_oldcontractline_dates';
561
562 CURSOR l_olddates_csr IS
563 SELECT start_date,
564 end_date
565 FROM okc_k_lines_b
566 WHERE ID =
567 (SELECT ool.object_cle_id
568 FROM okc_operation_lines ool,
569 okc_operation_instances oie,
570 okc_class_operations cop
571 WHERE cop.ID = oie.cop_id
572 AND cop.opn_code = p_opn_code
573 AND oie.ID = ool.oie_id
574 AND ool.subject_cle_id = p_cle_id
575 AND ROWNUM < 2);
576 BEGIN
577 -- start debug log
578 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
579 fnd_log.STRING (fnd_log.level_procedure,
580 g_module ||
581 l_api_name,
582 '100: Entered ' ||
583 g_pkg_name ||
584 '.' ||
585 l_api_name
586 );
587 END IF;
588
589 OPEN l_olddates_csr;
590
591 FETCH l_olddates_csr
592 INTO x_old_start_date,
593 x_old_end_date;
594
595 CLOSE l_olddates_csr;
596
597 -- end debug log
598 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
599 fnd_log.STRING (fnd_log.level_procedure,
600 g_module ||
601 l_api_name,
602 '1000: Leaving ' ||
603 g_pkg_name ||
604 '.' ||
605 l_api_name
606 );
607 END IF;
608 END get_oldcontractline_dates;
609
610 PROCEDURE update_header (
611 p_chrv_rec IN okc_contract_pub.chrv_rec_type,
612 x_return_status OUT NOCOPY VARCHAR2
613 ) IS
614 l_api_name CONSTANT VARCHAR2 (30) := 'update_header';
615 l_chrv_rec okc_contract_pub.chrv_rec_type
616 := p_chrv_rec;
617 x_chrv_rec okc_contract_pub.chrv_rec_type;
618 l_api_version NUMBER := 1.0;
619 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
620 l_return_status VARCHAR2 (1)
621 := okc_api.g_ret_sts_success;
622 l_msg_count NUMBER;
623 l_msg_data VARCHAR2 (2000);
624 BEGIN
625 -- start debug log
626 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
627 fnd_log.STRING (fnd_log.level_procedure,
628 g_module ||
629 l_api_name,
630 '100: Entered ' ||
631 g_pkg_name ||
632 '.' ||
633 l_api_name
634 );
635 END IF;
636
637 okc_contract_pub.update_contract_header
638 (p_api_version => l_api_version,
639 p_init_msg_list => l_init_msg_list,
640 x_return_status => l_return_status,
641 x_msg_count => l_msg_count,
642 x_msg_data => l_msg_data,
643 p_restricted_update => 'F',
644 p_chrv_rec => l_chrv_rec,
645 x_chrv_rec => x_chrv_rec
646 );
647
648 IF l_return_status <> g_ret_sts_success THEN
649 RAISE g_error;
650 END IF;
651
652 x_return_status := l_return_status;
653
654 -- end debug log
655 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
656 fnd_log.STRING (fnd_log.level_procedure,
657 g_module ||
658 l_api_name,
659 '1000: Leaving ' ||
660 g_pkg_name ||
661 '.' ||
662 l_api_name
663 );
664 END IF;
665 EXCEPTION
666 WHEN g_error THEN
667 -- end debug log
668 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
669 fnd_log.STRING (fnd_log.level_procedure,
670 g_module ||
671 l_api_name,
672 '2500: Leaving ' ||
673 g_pkg_name ||
674 '.' ||
675 l_api_name
676 );
677 END IF;
678
679 x_return_status := l_return_status;
680 WHEN OTHERS THEN
681 -- end debug log
682 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
683 fnd_log.STRING (fnd_log.level_procedure,
684 g_module ||
685 l_api_name,
686 '4000: Leaving ' ||
687 g_pkg_name ||
688 '.' ||
689 l_api_name
690 );
691 END IF;
692
693 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
694 x_return_status := g_ret_sts_unexp_error;
695 okc_api.set_message (p_app_name => g_app_name_oks,
696 p_msg_name => g_unexpected_error,
697 p_token1 => g_sqlcode_token,
698 p_token1_value => SQLCODE,
699 p_token2 => g_sqlerrm_token,
700 p_token2_value => SQLERRM
701 );
702 END update_header;
703
704 PROCEDURE update_line (
705 p_clev_rec IN okc_contract_pub.clev_rec_type,
706 x_return_status OUT NOCOPY VARCHAR2
707 ) IS
708 l_api_name CONSTANT VARCHAR2 (30) := 'update_line';
709 l_clev_rec okc_contract_pub.clev_rec_type
710 := p_clev_rec;
711 x_clev_rec okc_contract_pub.clev_rec_type;
712 l_api_version NUMBER := 1.0;
713 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
714 l_return_status VARCHAR2 (1)
715 := okc_api.g_ret_sts_success;
716 l_msg_count NUMBER;
717 l_msg_data VARCHAR2 (2000);
718 BEGIN
719 -- start debug log
720 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
721 fnd_log.STRING (fnd_log.level_procedure,
722 g_module ||
723 l_api_name,
724 '100: Entered ' ||
725 g_pkg_name ||
726 '.' ||
727 l_api_name
728 );
729 END IF;
730
731 okc_contract_pub.update_contract_line (p_api_version => l_api_version,
732 p_init_msg_list => l_init_msg_list,
733 x_return_status => l_return_status,
734 x_msg_count => l_msg_count,
735 x_msg_data => l_msg_data,
736 p_restricted_update => 'F',
737 p_clev_rec => l_clev_rec,
738 x_clev_rec => x_clev_rec
739 );
740
741 IF l_return_status = g_ret_sts_success THEN
742 RAISE g_error;
743 END IF;
744
745 x_return_status := l_return_status;
746
747 -- end debug log
748 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
749 fnd_log.STRING (fnd_log.level_procedure,
750 g_module ||
751 l_api_name,
752 '1000: Leaving ' ||
753 g_pkg_name ||
754 '.' ||
755 l_api_name
756 );
757 END IF;
758 EXCEPTION
759 WHEN g_error THEN
760 -- end debug log
761 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
762 fnd_log.STRING (fnd_log.level_procedure,
763 g_module ||
764 l_api_name,
765 '2500: Leaving ' ||
766 g_pkg_name ||
767 '.' ||
768 l_api_name
769 );
770 END IF;
771
772 x_return_status := l_return_status;
773 WHEN OTHERS THEN
774 -- end debug log
775 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
776 fnd_log.STRING (fnd_log.level_procedure,
777 g_module ||
778 l_api_name,
779 '4000: Leaving ' ||
780 g_pkg_name ||
781 '.' ||
782 l_api_name
783 );
784 END IF;
785
786 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
787 x_return_status := g_ret_sts_unexp_error;
788 okc_api.set_message (p_app_name => g_app_name_oks,
789 p_msg_name => g_unexpected_error,
790 p_token1 => g_sqlcode_token,
791 p_token1_value => SQLCODE,
792 p_token2 => g_sqlerrm_token,
793 p_token2_value => SQLERRM
794 );
795 END update_line;
796
797 PROCEDURE update_amounts (
798 p_chr_id IN NUMBER,
799 x_return_status OUT NOCOPY VARCHAR2
800 ) IS
801 l_api_name CONSTANT VARCHAR2 (30) := 'update_amounts';
802
803 CURSOR l_topline_csr IS
804 SELECT ID
805 FROM okc_k_lines_b
806 WHERE dnz_chr_id = p_chr_id
807 AND cle_id IS NULL
808 AND lse_id IN (1, 12, 19);
809
810 CURSOR l_sublineamt_csr (
811 c_cle_id IN NUMBER
812 ) IS
813 SELECT SUM (NVL (price_negotiated, 0)) topline_amt
814 FROM okc_k_lines_b
815 WHERE dnz_chr_id = p_chr_id
816 AND cle_id = c_cle_id
817 AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35) -- added skekkar
818 AND date_cancelled IS NULL; -- added skekkar
819
820 CURSOR l_toplineamt_csr IS
821 SELECT SUM (NVL (price_negotiated, 0)) header_amt
822 FROM okc_k_lines_b
823 WHERE dnz_chr_id = p_chr_id
824 AND cle_id IS NULL
825 AND lse_id IN (1, 12, 19, 46)
826 AND date_cancelled IS NULL; -- added skekkar
827
828 -- added skekkar to update tax amount for top lines
829 CURSOR l_sublinetax_csr (
830 c_cle_id IN NUMBER
831 ) IS
832 SELECT SUM (NVL (tax_amount, 0)) topline_tax
833 FROM oks_k_lines_b
834 WHERE dnz_chr_id = p_chr_id
835 AND cle_id IN ( SELECT id
836 FROM okc_k_lines_b
837 WHERE dnz_chr_id = p_chr_id
838 AND cle_id = c_cle_id
839 AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
840 AND date_cancelled IS NULL
841 );
842
843 -- added skekkar to update tax amount for header
844 CURSOR l_toplinetax_csr IS
845 SELECT SUM (NVL (tax_amount, 0)) header_tax
846 FROM oks_k_lines_b
847 WHERE dnz_chr_id = p_chr_id
848 AND cle_id IN ( SELECT id
849 FROM okc_k_lines_b
850 WHERE dnz_chr_id = p_chr_id
851 AND cle_id IS NULL
852 AND lse_id IN (1, 12, 19, 46)
853 AND date_cancelled IS NULL
854 );
855
856 l_line_id NUMBER;
857 l_tot_subline_amt NUMBER;
858 l_tot_topline_amt NUMBER;
859 l_chrv_rec okc_contract_pub.chrv_rec_type;
860 l_clev_rec okc_contract_pub.clev_rec_type;
861 l_return_status VARCHAR2 (1) := g_ret_sts_success;
862
863 l_tot_subline_tax NUMBER;
864 l_tot_topline_tax NUMBER;
865
866 BEGIN
867 -- start debug log
868 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
869 fnd_log.STRING (fnd_log.level_procedure,
870 g_module ||
871 l_api_name,
872 '100: Entered ' ||
873 g_pkg_name ||
874 '.' ||
875 l_api_name
876 );
877 END IF;
878
879 OPEN l_topline_csr;
880
881 LOOP
882 FETCH l_topline_csr
883 INTO l_line_id;
884
885 EXIT WHEN l_topline_csr%NOTFOUND;
886
887 OPEN l_sublineamt_csr (l_line_id);
888
889 FETCH l_sublineamt_csr
890 INTO l_tot_subline_amt;
891
892 CLOSE l_sublineamt_csr;
893
894 --Update Line With the Amounts from Pricing Engine(Price Negotiated and Unit Price)
895 l_clev_rec.ID := l_line_id;
896 l_clev_rec.price_negotiated := l_tot_subline_amt;
897
898 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
899 fnd_log.STRING (fnd_log.level_statement,
900 g_module ||
901 l_api_name,
902 '106:In Update Amounts: TopLine Id: ' ||
903 l_line_id
904 );
905 fnd_log.STRING (fnd_log.level_statement,
906 g_module ||
907 l_api_name,
908 '107:In Update Amounts: Total Of Subline Amount: ' ||
909 l_tot_subline_amt
910 );
911 END IF;
912
913 --errorout_urp('In Update Amounts: TopLine Id: ' || l_line_id);
914 --errorout_urp('In Update Amounts: Total Of Subline Amount: ' || l_tot_subline_amt);
915 update_line (p_clev_rec => l_clev_rec,
916 x_return_status => l_return_status);
917
918 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
919 fnd_log.STRING
920 (fnd_log.level_statement,
921 g_module ||
922 l_api_name,
923 '108:In Update Amounts After Update Line: Status: ' ||
924 l_return_status
925 );
926 END IF;
927
928 --errorout_urp('In Update Amounts After Update Line: Status: ' || l_return_status);
929 IF l_return_status <> g_ret_sts_success THEN
930 CLOSE l_topline_csr;
931
932 RAISE g_error;
933 END IF;
934
935 l_line_id := NULL;
936 l_tot_subline_amt := NULL;
937 --Update Line With the Amounts from Pricing Engine(Price Negotiated and Unit Price)
938
939 /* Not required as done in oks_renew_contract_pvt
940 -- added skekkar to update topline tax amount
941 OPEN l_sublinetax_csr (l_line_id);
942 FETCH l_sublinetax_csr INTO l_tot_subline_tax;
943 CLOSE l_sublinetax_csr;
944
945 UPDATE oks_k_lines_b
946 SET tax_amount = l_tot_subline_tax
947 WHERE cle_id = l_line_id;
948 -- end added skekkar
949 */
950
951 END LOOP;
952
953 CLOSE l_topline_csr;
954
955 OPEN l_toplineamt_csr;
956
957 FETCH l_toplineamt_csr
958 INTO l_tot_topline_amt;
959
960 CLOSE l_toplineamt_csr;
961
962 --UPDATE HEADER;
963 l_chrv_rec.ID := p_chr_id;
964 l_chrv_rec.estimated_amount := l_tot_topline_amt;
965 --errorout_urp('Header Amount: ' || l_chrv_rec.estimated_amount);
966 update_header (p_chrv_rec => l_chrv_rec,
967 x_return_status => l_return_status);
968
969 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
970 fnd_log.STRING
971 (fnd_log.level_statement,
972 g_module ||
973 l_api_name,
974 '109:In Update Amounts After Update Header: Status: ' ||
975 l_return_status
976 );
977 END IF;
978
979 --errorout_urp('In Update Amounts After Update Header: Status: ' || l_return_status);
980 IF l_return_status <> g_ret_sts_success THEN
981 RAISE g_error;
982 END IF;
983
984 --UPDATE HEADER;
985 x_return_status := l_return_status;
986
987 /* Not required as done in oks_renew_contract_pvt
988 -- added skekkar to update header tax amount
989 OPEN l_toplinetax_csr;
990 FETCH l_toplinetax_csr INTO l_tot_topline_tax;
991 CLOSE l_toplinetax_csr;
992
993 UPDATE oks_k_headers_b
994 SET tax_amount = l_tot_topline_tax
995 WHERE chr_id = p_chr_id;
996 -- end added skekkar to update header tax amount
997 */
998
999
1000 -- end debug log
1001 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1002 fnd_log.STRING (fnd_log.level_procedure,
1003 g_module ||
1004 l_api_name,
1005 '1000: Leaving ' ||
1006 g_pkg_name ||
1007 '.' ||
1008 l_api_name
1009 );
1010 END IF;
1011 EXCEPTION
1012 WHEN g_error THEN
1013 -- end debug log
1014 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1015 fnd_log.STRING (fnd_log.level_procedure,
1016 g_module ||
1017 l_api_name,
1018 '2500: Leaving ' ||
1019 g_pkg_name ||
1020 '.' ||
1021 l_api_name
1022 );
1023 END IF;
1024
1025 x_return_status := l_return_status;
1026 WHEN OTHERS THEN
1027 -- end debug log
1028 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1029 fnd_log.STRING (fnd_log.level_procedure,
1030 g_module ||
1031 l_api_name,
1032 '4000: Leaving ' ||
1033 g_pkg_name ||
1034 '.' ||
1035 l_api_name
1036 );
1037 END IF;
1038
1039 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1040 x_return_status := g_ret_sts_unexp_error;
1041 okc_api.set_message (p_app_name => g_app_name_oks,
1042 p_msg_name => g_unexpected_error,
1043 p_token1 => g_sqlcode_token,
1044 p_token1_value => SQLCODE,
1045 p_token2 => g_sqlerrm_token,
1046 p_token2_value => SQLERRM
1047 );
1048 END update_amounts;
1049
1050 PROCEDURE calculate_tax (
1051 p_chr_id IN NUMBER,
1052 p_cle_id IN NUMBER,
1053 p_amount IN NUMBER,
1054 x_return_status OUT NOCOPY VARCHAR2
1055 ) IS
1056 l_api_name CONSTANT VARCHAR2 (30) := 'calculate_tax';
1057 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
1058 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
1059 l_exception EXCEPTION;
1060 l_rail_rec oks_tax_util_pvt.ra_rec_type;
1061 l_tax_value NUMBER;
1062 l_tax_flag VARCHAR2 (3);
1063 l_total_amount NUMBER;
1064 l_k_det_rec oks_qp_int_pvt.k_details_rec;
1065 l_return_status VARCHAR2 (1) := g_ret_sts_success;
1066 l_msg_count NUMBER;
1067 l_msg_data VARCHAR2 (2000);
1068 BEGIN
1069 -- start debug log
1070 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1071 fnd_log.STRING (fnd_log.level_procedure,
1072 g_module ||
1073 l_api_name,
1074 '100: Entered ' ||
1075 g_pkg_name ||
1076 '.' ||
1077 l_api_name
1078 );
1079 END IF;
1080
1081 -- bug 5138920 Call to calculate_tax should not error out even if tax is not setup correctly
1082 -- initialize x_return_status to success
1083 x_return_status := g_ret_sts_success;
1084
1085 l_rail_rec.amount := p_amount;
1086 oks_tax_util_pvt.get_tax (p_api_version => 1.0,
1087 p_init_msg_list => okc_api.g_true,
1088 p_chr_id => p_chr_id,
1089 p_cle_id => p_cle_id,
1090 px_rail_rec => l_rail_rec,
1091 x_msg_count => l_msg_count,
1092 x_msg_data => l_msg_data,
1093 x_return_status => l_return_status
1094 );
1095
1096 IF l_return_status <> g_ret_sts_success THEN
1097 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1098 fnd_log.STRING (fnd_log.level_statement, g_module ||l_api_name,
1099 '105: Call to oks_tax_util_pvt.get_tax FAILED, l_return_status: ' ||l_return_status
1100 );
1101 fnd_log.STRING (fnd_log.level_statement, g_module ||l_api_name,
1102 '105: l_msg_data '||l_msg_data
1103 );
1104 END IF;
1105 -- bug 5001677 even if tax api has error, proceed with renewal
1106 -- RAISE g_error;
1107 l_tax_value := 0;
1108 l_tax_flag := 'N';
1109
1110 -- bug 5138920, reset l_return_status to success
1111 l_return_status := g_ret_sts_success;
1112 END IF;
1113
1114 l_tax_value := l_rail_rec.tax_value;
1115 l_tax_flag := l_rail_rec.amount_includes_tax_flag;
1116
1117 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1118 fnd_log.STRING (fnd_log.level_statement,
1119 g_module ||
1120 l_api_name,
1121 '110:Inside Calculate Tax: l_tax_value: ' ||
1122 l_tax_value
1123 );
1124 fnd_log.STRING (fnd_log.level_statement,
1125 g_module ||
1126 l_api_name,
1127 '111:Inside Calculate Tax: l_tax_flag: ' ||
1128 l_tax_flag
1129 );
1130 END IF;
1131
1132 --errorout_urp('Inside Calculate Tax: l_tax_value: ' || l_tax_value);
1133 --errorout_urp('Inside Calculate Tax: l_tax_flag: ' || l_tax_flag);
1134 l_kln_rec_in.cle_id := p_cle_id;
1135 l_kln_rec_in.dnz_chr_id := p_chr_id;
1136 l_kln_rec_in.tax_inclusive_yn := l_tax_flag;
1137
1138 IF l_tax_flag = 'N' THEN
1139 l_kln_rec_in.tax_amount := NVL (l_tax_value, 0);
1140 ELSE
1141 l_kln_rec_in.tax_amount := 0;
1142 END IF;
1143
1144 oks_qp_int_pvt.get_k_details (p_id => p_cle_id,
1145 p_type => oks_qp_int_pvt.g_oks_line,
1146 x_k_det_rec => l_k_det_rec
1147 );
1148 l_kln_rec_in.ID := l_k_det_rec.ID;
1149 l_kln_rec_in.object_version_number := l_k_det_rec.object_version_number;
1150
1151 -- bug 4865683 SKEKKAR
1152 -- update column clvl_extended_amt in oks_k_lines_b with price_negotiated
1153 l_kln_rec_in.clvl_extended_amt := NVL(p_amount,0) ;
1154 -- end added
1155
1156 oks_contract_line_pub.update_line (p_api_version => 1.0,
1157 p_init_msg_list => 'F',
1158 x_return_status => l_return_status,
1159 x_msg_count => l_msg_count,
1160 x_msg_data => l_msg_data,
1161 p_klnv_rec => l_kln_rec_in,
1162 x_klnv_rec => l_kln_rec_out,
1163 p_validate_yn => 'N'
1164 );
1165
1166 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1167 fnd_log.STRING (fnd_log.level_statement,
1168 g_module ||
1169 l_api_name,
1170 '112:Status AFter update rule in calculate_tax: ' ||
1171 l_return_status
1172 );
1173 END IF;
1174
1175 --errorout_urp('Status AFter update rule in calculate_tax: ' || l_return_status);
1176 -- IF x_return_status <> g_ret_sts_success THEN
1177 IF l_return_status <> g_ret_sts_success THEN -- bug 5138920
1178 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1179 fnd_log.STRING (fnd_log.level_statement,
1180 g_module ||
1181 l_api_name,
1182 '113:Update line details at Calc Tax'
1183 );
1184 END IF;
1185
1186 -- errorout_urp('Update line details at Calc Tax');
1187 RAISE g_error;
1188 END IF;
1189
1190 x_return_status := l_return_status;
1191
1192 -- end debug log
1193 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1194 fnd_log.STRING (fnd_log.level_procedure,
1195 g_module ||
1196 l_api_name,
1197 '1000: Leaving ' ||
1198 g_pkg_name ||
1199 '.' ||
1200 l_api_name
1201 );
1202 END IF;
1203 EXCEPTION
1204 WHEN g_error THEN
1205 -- end debug log
1206 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1207 fnd_log.STRING (fnd_log.level_procedure,
1208 g_module ||
1209 l_api_name,
1210 '2500: Leaving ' ||
1211 g_pkg_name ||
1212 '.' ||
1213 l_api_name
1214 );
1215 END IF;
1216
1217 x_return_status := l_return_status;
1218 WHEN OTHERS THEN
1219 -- end debug log
1220 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1221 fnd_log.STRING (fnd_log.level_procedure,
1222 g_module ||
1223 l_api_name,
1224 '4000: Leaving ' ||
1225 g_pkg_name ||
1226 '.' ||
1227 l_api_name
1228 );
1229 END IF;
1230
1231 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1232 x_return_status := g_ret_sts_unexp_error;
1233 okc_api.set_message (p_app_name => g_app_name_oks,
1234 p_msg_name => g_unexpected_error,
1235 p_token1 => g_sqlcode_token,
1236 p_token1_value => SQLCODE,
1237 p_token2 => g_sqlerrm_token,
1238 p_token2_value => SQLERRM
1239 );
1240 END calculate_tax;
1241
1242 PROCEDURE update_price_info (
1243 p_chr_id IN NUMBER,
1244 p_cle_id IN NUMBER,
1245 p_lse_id IN NUMBER,
1246 p_price_type IN VARCHAR2,
1247 p_price_details IN oks_qp_pkg.price_details,
1248 x_return_status OUT NOCOPY VARCHAR2
1249 ) IS
1250 l_api_name CONSTANT VARCHAR2 (30)
1251 := 'update_price_info ';
1252 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
1253 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
1254 l_k_det_rec oks_qp_int_pvt.k_details_rec;
1255 l_return_status VARCHAR2 (1) := g_ret_sts_success;
1256 l_msg_count NUMBER;
1257 l_msg_data VARCHAR2 (2000);
1258 BEGIN
1259 -- start debug log
1260 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1261 fnd_log.STRING (fnd_log.level_procedure,
1262 g_module ||
1263 l_api_name,
1264 '100: Entered ' ||
1265 g_pkg_name ||
1266 '.' ||
1267 l_api_name
1268 );
1269 END IF;
1270
1271 oks_qp_int_pvt.get_k_details (p_id => p_cle_id,
1272 p_type => oks_qp_int_pvt.g_oks_line,
1273 x_k_det_rec => l_k_det_rec
1274 );
1275 l_kln_rec_in.ID := l_k_det_rec.ID;
1276 l_kln_rec_in.object_version_number := l_k_det_rec.object_version_number;
1277 l_kln_rec_in.cle_id := p_cle_id;
1278 l_kln_rec_in.dnz_chr_id := p_chr_id;
1279 l_kln_rec_in.status_text := p_price_details.status_text;
1280
1281 IF p_price_type = 'MAN' THEN
1282 l_kln_rec_in.clvl_list_price := NULL;
1283 l_kln_rec_in.clvl_quantity := NULL;
1284 l_kln_rec_in.clvl_extended_amt := NULL;
1285 -- l_kln_rec_in.clvl_uom_code := NULL; -- Bug 5129965
1286 l_kln_rec_in.toplvl_operand_code := NULL;
1287 l_kln_rec_in.toplvl_operand_val := NULL;
1288 l_kln_rec_in.toplvl_quantity := NULL;
1289 -- l_kln_rec_in.toplvl_uom_code := NULL; --Bug 5129965
1290 l_kln_rec_in.toplvl_adj_price := NULL;
1291 l_kln_rec_in.toplvl_price_qty := NULL; --uncommented for Bug#5359739
1292 --l_kln_rec_in.rule_information11 := p_price_details.serv_ext_amount;
1293 l_kln_rec_in.prod_price := NULL;
1294 l_kln_rec_in.service_price := NULL;
1295 ELSE
1296 --l_kln_rec_in.status_text := 'Price Derived Using Price Index';
1297 IF p_lse_id = 46 THEN
1298 --l_kln_rec_in.rule_information4 := p_price_details.prod_list_unit_price;
1299 l_kln_rec_in.toplvl_quantity := p_price_details.prod_qty;
1300 -- bug 5129965
1301 IF p_price_details.prod_priced_uom IS NOT NULL THEN
1302 l_kln_rec_in.toplvl_uom_code := p_price_details.prod_priced_uom;
1303 END IF;
1304 l_kln_rec_in.toplvl_adj_price := p_price_details.prod_adj_unit_price;
1305 --l_kln_rec_in.rule_information11 := p_price_details.prod_ext_amount;
1306 --Removed the IF condition for Bug#5359739
1307 --IF p_price_details.serv_priced_qty IS NOT NULL THEN
1308 -- l_kln_rec_in.toplvl_price_qty := p_price_details.serv_priced_qty;
1309 -- bug 5511211, for subscription lines look at p_price_details.prod_priced_qty
1310 l_kln_rec_in.toplvl_price_qty := p_price_details.prod_priced_qty;
1311 --END IF;
1312 l_kln_rec_in.service_price := p_price_details.prod_price_list_id;
1313 ELSE
1314 l_kln_rec_in.clvl_list_price := p_price_details.prod_list_unit_price;
1315 l_kln_rec_in.clvl_quantity := p_price_details.prod_priced_qty;
1316 l_kln_rec_in.clvl_extended_amt := p_price_details.prod_ext_amount;
1317 --l_kln_rec_in.rule_information4 := p_price_details.serv_list_unit_price;
1318 l_kln_rec_in.toplvl_operand_code := p_price_details.serv_operator;
1319 l_kln_rec_in.toplvl_operand_val := p_price_details.serv_operand;
1320 -- bug 5129965
1321 IF p_price_details.prod_priced_uom IS NOT NULL THEN
1322 l_kln_rec_in.clvl_uom_code := p_price_details.prod_priced_uom;
1323 END IF;
1324 l_kln_rec_in.toplvl_quantity := p_price_details.serv_qty;
1325 -- bug 5129965
1326 IF p_price_details.serv_priced_uom IS NOT NULL THEN
1327 l_kln_rec_in.toplvl_uom_code := p_price_details.serv_priced_uom;
1328 END IF;
1329 l_kln_rec_in.toplvl_adj_price := p_price_details.serv_adj_unit_price;
1330 --l_kln_rec_in.rule_information11 := p_price_details.serv_ext_amount;
1331 --Removed the IF condition for Bug#5359739
1332 --IF p_price_details.serv_priced_qty IS NOT NULL THEN
1333 l_kln_rec_in.toplvl_price_qty := p_price_details.serv_priced_qty;
1334 --END IF;
1335 l_kln_rec_in.prod_price := p_price_details.prod_price_list_id;
1336 l_kln_rec_in.service_price := p_price_details.serv_price_list_id;
1337 END IF;
1338 END IF;
1339
1340 oks_contract_line_pub.update_line (p_api_version => 1.0,
1341 p_init_msg_list => 'F',
1342 x_return_status => l_return_status,
1343 x_msg_count => l_msg_count,
1344 x_msg_data => l_msg_data,
1345 p_klnv_rec => l_kln_rec_in,
1346 x_klnv_rec => l_kln_rec_out,
1347 p_validate_yn => 'N'
1348 );
1349
1350 IF l_return_status <> g_ret_sts_success THEN
1351 RAISE g_error;
1352 END IF;
1353
1354 x_return_status := l_return_status;
1355
1356 -- end debug log
1357 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1358 fnd_log.STRING (fnd_log.level_procedure,
1359 g_module ||
1360 l_api_name,
1361 '1000: Leaving ' ||
1362 g_pkg_name ||
1363 '.' ||
1364 l_api_name
1365 );
1366 END IF;
1367 EXCEPTION
1368 WHEN g_error THEN
1369 -- end debug log
1370 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1371 fnd_log.STRING (fnd_log.level_procedure,
1372 g_module ||
1373 l_api_name,
1374 '2500: Leaving ' ||
1375 g_pkg_name ||
1376 '.' ||
1377 l_api_name
1378 );
1379 END IF;
1380
1381 x_return_status := l_return_status;
1382 WHEN OTHERS THEN
1383 -- end debug log
1384 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1385 fnd_log.STRING (fnd_log.level_procedure,
1386 g_module ||
1387 l_api_name,
1388 '4000: Leaving ' ||
1389 g_pkg_name ||
1390 '.' ||
1391 l_api_name
1392 );
1393 END IF;
1394
1395 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1396 x_return_status := g_ret_sts_unexp_error;
1397 okc_api.set_message (p_app_name => g_app_name_oks,
1398 p_msg_name => g_unexpected_error,
1399 p_token1 => g_sqlcode_token,
1400 p_token1_value => SQLCODE,
1401 p_token2 => g_sqlerrm_token,
1402 p_token2_value => SQLERRM
1403 );
1404 END update_price_info;
1405
1406 PROCEDURE calculate_price (
1407 p_chr_id IN NUMBER,
1408 p_line_id IN NUMBER,
1409 p_linecle_id IN NUMBER,
1410 p_lse_id IN NUMBER,
1411 p_intent IN VARCHAR2,
1412 p_price_type IN VARCHAR2,
1413 p_price_list IN NUMBER,
1414 p_amount IN NUMBER,
1415 x_return_status OUT NOCOPY VARCHAR2
1416 ) IS
1417 l_api_name CONSTANT VARCHAR2 (30)
1418 := 'calculate_price';
1419
1420 CURSOR l_subs_elements_csr (
1421 p_line_id NUMBER
1422 ) IS
1423 SELECT ID,
1424 osh_id,
1425 dnz_chr_id,
1426 dnz_cle_id,
1427 linked_flag,
1428 seq_no,
1429 om_interface_date,
1430 amount,
1431 start_date,
1432 end_date,
1433 quantity,
1434 uom_code,
1435 order_header_id,
1436 order_line_id,
1437 object_version_number
1438 FROM oks_subscr_elements
1439 WHERE dnz_cle_id = p_line_id;
1440
1441 l_new_amount NUMBER;
1442 l_clev_rec okc_contract_pub.clev_rec_type;
1443 l_price_type VARCHAR2 (10);
1444 l_detail_rec oks_qp_pkg.input_details;
1445 l_price_details oks_qp_pkg.price_details;
1446 l_modifier_details qp_preq_grp.line_detail_tbl_type;
1447 l_price_break_details oks_qp_pkg.g_price_break_tbl_type;
1448 l_scev_rec_in oks_subscr_elems_pub.scev_rec_type;
1449 l_scev_rec_out oks_subscr_elems_pub.scev_rec_type;
1450 l_api_version NUMBER := 1.0;
1451 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
1452 l_return_status VARCHAR2 (1) := g_ret_sts_success;
1453 l_msg_count NUMBER;
1454 l_msg_data VARCHAR2 (2000);
1455 BEGIN
1456 -- start debug log
1457 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1458 fnd_log.STRING (fnd_log.level_procedure,
1459 g_module ||
1460 l_api_name,
1461 '100: Entered ' ||
1462 g_pkg_name ||
1463 '.' ||
1464 l_api_name
1465 );
1466 END IF;
1467
1468 l_detail_rec.line_id := NVL (p_linecle_id, p_line_id);
1469
1470 IF p_linecle_id IS NULL THEN
1471 l_detail_rec.subline_id := NULL;
1472 ELSE
1473 l_detail_rec.subline_id := p_line_id;
1474 END IF;
1475
1476 l_detail_rec.intent := p_intent;
1477 l_detail_rec.price_list := p_price_list;
1478
1479 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1480 fnd_log.STRING (fnd_log.level_statement,
1481 g_module ||
1482 l_api_name,
1483 '114:l_detail_rec.line_id: ' ||
1484 l_detail_rec.line_id
1485 );
1486 fnd_log.STRING (fnd_log.level_statement,
1487 g_module ||
1488 l_api_name,
1489 '115:l_detail_rec.subline_id: ' ||
1490 l_detail_rec.subline_id
1491 );
1492 fnd_log.STRING (fnd_log.level_statement,
1493 g_module ||
1494 l_api_name,
1495 '116:l_detail_rec.intent: ' ||
1496 l_detail_rec.intent
1497 );
1498 fnd_log.STRING (fnd_log.level_statement,
1499 g_module ||
1500 l_api_name,
1501 '117:l_detail_rec.price_list: ' ||
1502 l_detail_rec.price_list
1503 );
1504 END IF;
1505
1506 --errorout_urp('l_detail_rec.line_id: ' || l_detail_rec.line_id);
1507 --errorout_urp('l_detail_rec.subline_id: ' || l_detail_rec.subline_id);
1508 --errorout_urp('l_detail_rec.intent: ' || l_detail_rec.intent);
1509 --errorout_urp('l_detail_rec.price_list: ' || l_detail_rec.price_list);
1510 oks_qp_pkg.calc_price (p_detail_rec => l_detail_rec,
1511 x_price_details => l_price_details,
1512 x_modifier_details => l_modifier_details,
1513 x_price_break_details => l_price_break_details,
1514 x_return_status => l_return_status,
1515 x_msg_count => l_msg_count,
1516 x_msg_data => l_msg_data
1517 );
1518
1519 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1520 fnd_log.STRING
1521 (fnd_log.level_statement,
1522 g_module ||
1523 l_api_name,
1524 '118:In Calculate Price Status after OKS_QP_PKG.calc_price: ' ||
1525 l_return_status
1526 );
1527 END IF;
1528
1529 --errorout_urp('In Calculate Price Status after OKS_QP_PKG.calc_price: ' || l_return_status);
1530 IF l_return_status <> g_ret_sts_success THEN
1531 RAISE g_error;
1532 END IF;
1533
1534 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1535 fnd_log.STRING (fnd_log.level_statement,
1536 g_module ||
1537 l_api_name,
1538 '119:Markup/down amount: ' ||
1539 p_amount
1540 );
1541 fnd_log.STRING (fnd_log.level_statement,
1542 g_module ||
1543 l_api_name,
1544 '120:l_price_details.serv_ext_amount: ' ||
1545 l_price_details.serv_ext_amount
1546 );
1547 fnd_log.STRING (fnd_log.level_statement,
1548 g_module ||
1549 l_api_name,
1550 '120:l_price_details.prod_ext_amount: ' ||
1551 l_price_details.serv_ext_amount
1552 );
1553 fnd_log.STRING (fnd_log.level_statement,
1554 g_module ||
1555 l_api_name,
1556 '121:l_price_details.status_text: ' ||
1557 l_price_details.status_text
1558 );
1559 END IF;
1560
1561 --errorout_urp('Markup/down amount: ' || p_amount);
1562 --errorout_urp('l_price_details.serv_ext_amount: ' || l_price_details.serv_ext_amount);
1563 --errorout_urp('l_price_details.status_text: ' || l_price_details.status_text);
1564 /*
1565 * Bug 5511211
1566 * In case of subscription pricing, QP populates l_price_details.prod_ext_amount
1567 * For lse_id = 46 , compare amount with l_price_details.prod_ext_amount
1568 */
1569
1570 --p_amount is markedup/down amount. This is already calculated by the caller.
1571 IF p_lse_id = 46 THEN
1572 -- bug 5511211
1573 IF l_price_details.prod_ext_amount <= p_amount THEN
1574 l_new_amount := l_price_details.prod_ext_amount;
1575 l_price_type := 'PCT';
1576 ELSE
1577 l_new_amount := p_amount;
1578 l_price_type := 'MAN';
1579 END IF;
1580 ELSE
1581 -- old logic
1582 IF l_price_details.serv_ext_amount <= p_amount THEN
1583 l_new_amount := l_price_details.serv_ext_amount;
1584 l_price_type := 'PCT';
1585 ELSE
1586 l_new_amount := p_amount;
1587 l_price_type := 'MAN';
1588 END IF;
1589 END IF; -- bug 5511211, p_lse_id=46
1590
1591 l_price_details.status_text :=
1592 l_price_details.status_text ||
1593 ' Price derived using price index';
1594
1595 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1596 fnd_log.STRING (fnd_log.level_statement,
1597 g_module ||
1598 l_api_name,
1599 '122:In Calulate Price : l_price_type: ' ||
1600 l_price_type
1601 );
1602 fnd_log.STRING (fnd_log.level_statement,
1603 g_module ||
1604 l_api_name,
1605 '123:Amount Goes to OKC_K_LINES_B: ' ||
1606 l_new_amount
1607 );
1608 END IF;
1609
1610 --errorout_urp('In Calulate Price : l_price_type: ' || l_price_type);
1611 --errorout_urp('Amount Goes to OKC_K_LINES_B: ' || l_new_amount);
1612 l_clev_rec.ID := p_line_id;
1613 l_clev_rec.price_negotiated := l_new_amount;
1614
1615 -- bug 5511211
1616 -- for subscription lines, look at l_price_details.prod_adj_unit_price for unit price
1617 IF p_lse_id = 46 THEN
1618 IF l_price_type = 'PCT' THEN
1619 l_clev_rec.price_unit := l_price_details.prod_adj_unit_price;
1620 END IF;
1621 ELSE
1622 -- old logic
1623 IF l_price_type = 'PCT' THEN
1624 l_clev_rec.price_unit := l_price_details.serv_adj_unit_price;
1625 END IF;
1626 END IF; -- bug 5511211, p_lse_id = 46
1627
1628 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1629 fnd_log.STRING
1630 (fnd_log.level_statement,
1631 g_module ||
1632 l_api_name,
1633 '124:Before update line in calculate_price: l_clev_rec.id: ' ||
1634 l_clev_rec.ID
1635 );
1636 fnd_log.STRING
1637 (fnd_log.level_statement,
1638 g_module ||
1639 l_api_name,
1640 '125:Before update line in calculate_price: l_clev_rec.price_negotiated: ' ||
1641 l_clev_rec.price_negotiated
1642 );
1643 fnd_log.STRING
1644 (fnd_log.level_statement,
1645 g_module ||
1646 l_api_name,
1647 '126:Before update line in calculate_price: l_clev_rec.price_unit: ' ||
1648 l_clev_rec.price_unit
1649 );
1650 END IF;
1651
1652 --errorout_urp('Before update line in calculate_price: l_clev_rec.id: ' || l_clev_rec.id);
1653 --errorout_urp('Before update line in calculate_price: l_clev_rec.price_negotiated: ' || l_clev_rec.price_negotiated);
1654 --errorout_urp('Before update line in calculate_price: l_clev_rec.price_unit: ' || l_clev_rec.price_unit);
1655 update_line (p_clev_rec => l_clev_rec,
1656 x_return_status => l_return_status);
1657
1658 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1659 fnd_log.STRING (fnd_log.level_statement,
1660 g_module ||
1661 l_api_name,
1662 '127:Status after update line in calculate_price: ' ||
1663 l_return_status
1664 );
1665 END IF;
1666
1667 --errorout_urp('Status after update line in calculate_price: ' || l_return_status);
1668 IF l_return_status <> g_ret_sts_success THEN
1669 RAISE g_error;
1670 END IF;
1671
1672 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1673 fnd_log.STRING
1674 (fnd_log.level_statement,
1675 g_module ||
1676 l_api_name,
1677 '128:In Calulate Price : l_price_details.status_text: ' ||
1678 l_price_details.status_text
1679 );
1680 END IF;
1681
1682 --errorout_urp('In Calulate Price : l_price_details.status_text: ' || l_price_details.status_text);
1683 update_price_info (p_chr_id => p_chr_id,
1684 p_cle_id => p_line_id,
1685 p_lse_id => p_lse_id,
1686 p_price_type => l_price_type,
1687 p_price_details => l_price_details,
1688 x_return_status => l_return_status
1689 );
1690
1691 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1692 fnd_log.STRING
1693 (fnd_log.level_statement,
1694 g_module ||
1695 l_api_name,
1696 '129:Status after Handle Pdl Rule in calculate_price: ' ||
1697 l_return_status
1698 );
1699 END IF;
1700
1701 --errorout_urp('Status after Handle Pdl Rule in calculate_price: ' || l_return_status);
1702 IF l_return_status <> g_ret_sts_success THEN
1703 RAISE g_error;
1704 END IF;
1705
1706 -- Update Subsciption elements
1707 IF p_lse_id = 46 THEN
1708 FOR l_subs_elements_rec IN l_subs_elements_csr (l_detail_rec.line_id) LOOP
1709 l_scev_rec_in.ID := l_subs_elements_rec.ID;
1710 l_scev_rec_in.amount :=
1711 l_subs_elements_rec.quantity *
1712 l_price_details.serv_adj_unit_price;
1713 l_scev_rec_in.object_version_number :=
1714 l_subs_elements_rec.object_version_number;
1715 oks_subscr_elems_pub.update_row (p_api_version => l_api_version,
1716 p_init_msg_list => l_init_msg_list,
1717 x_return_status => l_return_status,
1718 x_msg_count => l_msg_count,
1719 x_msg_data => l_msg_data,
1720 p_scev_rec => l_scev_rec_in,
1721 x_scev_rec => l_scev_rec_out
1722 );
1723
1724 IF l_return_status <> g_ret_sts_success THEN
1725 RAISE g_error;
1726 END IF;
1727 END LOOP;
1728 END IF;
1729
1730 --Calculate Tax For Toplines(All Covered Levels for Service and Extended Warranty)
1731 calculate_tax (p_chr_id => p_chr_id,
1732 p_cle_id => p_line_id,
1733 p_amount => l_new_amount,
1734 x_return_status => l_return_status
1735 );
1736
1737 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1738 fnd_log.STRING
1739 (fnd_log.level_statement,
1740 g_module ||
1741 l_api_name,
1742 '130:Status after calculate_tax in calculate_price: ' ||
1743 l_return_status
1744 );
1745 END IF;
1746
1747 --errorout_urp('Status after calculate_tax in calculate_price: ' || l_return_status);
1748 IF l_return_status <> g_ret_sts_success THEN
1749 RAISE g_error;
1750 END IF;
1751
1752 x_return_status := l_return_status;
1753
1754 -- end debug log
1755 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1756 fnd_log.STRING (fnd_log.level_procedure,
1757 g_module ||
1758 l_api_name,
1759 '1000: Leaving ' ||
1760 g_pkg_name ||
1761 '.' ||
1762 l_api_name
1763 );
1764 END IF;
1765 EXCEPTION
1766 WHEN g_error THEN
1767 -- end debug log
1768 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1769 fnd_log.STRING (fnd_log.level_procedure,
1770 g_module ||
1771 l_api_name,
1772 '2500: Leaving ' ||
1773 g_pkg_name ||
1774 '.' ||
1775 l_api_name
1776 );
1777 END IF;
1778
1779 x_return_status := l_return_status;
1780 WHEN OTHERS THEN
1781 -- end debug log
1782 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1783 fnd_log.STRING (fnd_log.level_procedure,
1784 g_module ||
1785 l_api_name,
1786 '4000: Leaving ' ||
1787 g_pkg_name ||
1788 '.' ||
1789 l_api_name
1790 );
1791 END IF;
1792
1793 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1794 x_return_status := g_ret_sts_unexp_error;
1795 okc_api.set_message (p_app_name => g_app_name_oks,
1796 p_msg_name => g_unexpected_error,
1797 p_token1 => g_sqlcode_token,
1798 p_token1_value => SQLCODE,
1799 p_token2 => g_sqlerrm_token,
1800 p_token2_value => SQLERRM
1801 );
1802 END calculate_price;
1803
1804 PROCEDURE call_pricing_api (
1805 p_api_version IN NUMBER,
1806 p_init_msg_list IN VARCHAR2,
1807 p_id IN NUMBER,
1808 p_id_type IN VARCHAR2,
1809 x_return_status OUT NOCOPY VARCHAR2,
1810 x_msg_count OUT NOCOPY NUMBER,
1811 x_msg_data OUT NOCOPY VARCHAR2
1812 ) IS
1813 l_api_name CONSTANT VARCHAR2 (30)
1814 := 'call_pricing_api';
1815 l_detail_rec oks_qp_pkg.input_details;
1816 l_price_details oks_qp_pkg.price_details;
1817 l_modifier_details qp_preq_grp.line_detail_tbl_type;
1818 l_price_break_details oks_qp_pkg.g_price_break_tbl_type;
1819 l_return_status VARCHAR2 (1) := g_ret_sts_success;
1820 l_msg_count NUMBER;
1821 l_msg_data VARCHAR2 (2000);
1822 BEGIN
1823 -- start debug log
1824 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1825 fnd_log.STRING (fnd_log.level_procedure,
1826 g_module ||
1827 l_api_name,
1828 '100: Entered ' ||
1829 g_pkg_name ||
1830 '.' ||
1831 l_api_name
1832 );
1833 END IF;
1834
1835 IF p_id_type IS NULL THEN
1836 okc_api.set_message (g_app_name_oks,
1837 g_required_value,
1838 g_col_name_token,
1839 'P_Id_Type'
1840 );
1841 x_return_status := g_ret_sts_error;
1842 RAISE g_error;
1843 END IF;
1844
1845 IF p_id IS NOT NULL THEN
1846 IF p_id_type = 'CHR' THEN
1847 l_detail_rec.chr_id := p_id;
1848 l_detail_rec.intent := 'HP';
1849 oks_qp_int_pvt.compute_price
1850 (p_api_version => p_api_version,
1851 p_init_msg_list => p_init_msg_list,
1852 p_detail_rec => l_detail_rec,
1853 x_price_details => l_price_details,
1854 x_modifier_details => l_modifier_details,
1855 x_price_break_details => l_price_break_details,
1856 x_return_status => l_return_status,
1857 x_msg_count => l_msg_count,
1858 x_msg_data => l_msg_data
1859 );
1860
1861 IF l_return_status <> g_ret_sts_success THEN
1862 RAISE g_error;
1863 END IF;
1864 ELSIF p_id_type = 'CLE' THEN
1865 NULL;
1866 END IF;
1867 END IF;
1868
1869 x_return_status := l_return_status;
1870
1871 -- end debug log
1872 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1873 fnd_log.STRING (fnd_log.level_procedure,
1874 g_module ||
1875 l_api_name,
1876 '1000: Leaving ' ||
1877 g_pkg_name ||
1878 '.' ||
1879 l_api_name
1880 );
1881 END IF;
1882 EXCEPTION
1883 WHEN g_error THEN
1884 -- end debug log
1885 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1886 fnd_log.STRING (fnd_log.level_procedure,
1887 g_module ||
1888 l_api_name,
1889 '2500: Leaving ' ||
1890 g_pkg_name ||
1891 '.' ||
1892 l_api_name
1893 );
1894 END IF;
1895
1896 x_return_status := l_return_status;
1897 WHEN OTHERS THEN
1898 -- end debug log
1899 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1900 fnd_log.STRING (fnd_log.level_procedure,
1901 g_module ||
1902 l_api_name,
1903 '4000: Leaving ' ||
1904 g_pkg_name ||
1905 '.' ||
1906 l_api_name
1907 );
1908 END IF;
1909
1910 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1911 okc_api.set_message (p_app_name => g_app_name_oks,
1912 p_msg_name => g_unexpected_error,
1913 p_token1 => g_sqlcode_token,
1914 p_token1_value => SQLCODE,
1915 p_token2 => g_sqlerrm_token,
1916 p_token2_value => SQLERRM
1917 );
1918 x_return_status := g_ret_sts_unexp_error;
1919 END call_pricing_api;
1920
1921 --Called from renewal.
1922 PROCEDURE call_pricing_api (
1923 p_api_version IN NUMBER,
1924 p_init_msg_list IN VARCHAR2,
1925 p_reprice_rec IN reprice_rec_type,
1926 x_return_status OUT NOCOPY VARCHAR2,
1927 x_msg_count OUT NOCOPY NUMBER,
1928 x_msg_data OUT NOCOPY VARCHAR2
1929 ) IS
1930 l_api_name CONSTANT VARCHAR2 (30)
1931 := 'call_pricing_api';
1932 -- Bug 5139658
1933 l_orig_system_id1 okc_k_headers_all_b.orig_system_id1%type;
1934 l_org_id okc_k_headers_all_b.org_id%type;
1935
1936 CURSOR get_orig_system_id1 (
1937 p_chr_id NUMBER
1938 ) IS
1939 SELECT orig_system_id1
1940 FROM okc_k_headers_all_b
1941 WHERE ID = p_chr_id;
1942
1943
1944 CURSOR get_org_id_csr (
1945 p_hdr_id NUMBER
1946 ) IS
1947 SELECT org_id
1948 FROM okc_k_headers_all_b
1949 WHERE ID = p_hdr_id;
1950
1951 -- End Bug 5139658
1952
1953 CURSOR l_pricelist_csr (
1954 p_chr_id NUMBER
1955 ) IS
1956 SELECT price_list_id
1957 FROM okc_k_headers_all_b
1958 WHERE ID = p_chr_id;
1959
1960 -- Bug Fix 4291565
1961 CURSOR l_line_csr IS
1962 SELECT ID,
1963 start_date,
1964 end_date,
1965 lse_id,
1966 price_negotiated,
1967 price_unit,
1968 cle_id,
1969 dnz_chr_id
1970 FROM okc_k_lines_b
1971 WHERE dnz_chr_id = p_reprice_rec.contract_id
1972 AND lse_id IN (7, 8, 9, 10, 11, 13, 25, 35, 46);
1973
1974 -- Bug Fix 4291565
1975 CURSOR l_usage_type_csr (
1976 p_cle_id NUMBER
1977 ) IS
1978 SELECT usage_type
1979 FROM oks_k_lines_b
1980 WHERE cle_id = p_cle_id;
1981
1982 CURSOR l_subs_elements_csr (
1983 p_line_id NUMBER
1984 ) IS
1985 SELECT ID,
1986 osh_id,
1987 dnz_chr_id,
1988 dnz_cle_id,
1989 linked_flag,
1990 seq_no,
1991 om_interface_date,
1992 amount,
1993 start_date,
1994 end_date,
1995 quantity,
1996 uom_code,
1997 order_header_id,
1998 order_line_id,
1999 object_version_number
2000 FROM oks_subscr_elements
2001 WHERE dnz_cle_id = p_line_id;
2002
2003 l_new_duration NUMBER;
2004 l_tangible BOOLEAN;
2005 l_pricing_method Varchar2(30);
2006
2007
2008
2009
2010 l_chr_rec_in okc_contract_pub.chrv_rec_type;
2011 l_chr_rec_out okc_contract_pub.chrv_rec_type;
2012 l_scev_rec_in oks_subscr_elems_pub.scev_rec_type;
2013 l_scev_rec_out oks_subscr_elems_pub.scev_rec_type;
2014 l_pricelist_id NUMBER;
2015 l_line_rec l_line_csr%ROWTYPE;
2016 l_old_start_date DATE;
2017 l_old_end_date DATE;
2018 l_prorated_price_neg NUMBER;
2019 l_prorated_unit_price NUMBER;
2020 l_intent VARCHAR2 (10);
2021 l_detail_rec oks_qp_pkg.input_details;
2022 l_price_details oks_qp_pkg.price_details := NULL;
2023 l_modifier_details qp_preq_grp.line_detail_tbl_type;
2024 l_price_break_details oks_qp_pkg.g_price_break_tbl_type;
2025 l_api_version NUMBER := 1.0;
2026 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
2027 l_return_status VARCHAR2 (1) := g_ret_sts_success;
2028 l_msg_count NUMBER;
2029 l_msg_data VARCHAR2 (2000);
2030 l_clev_rec okc_contract_pub.clev_rec_type;
2031
2032 CURSOR l_currcode_csr (
2033 p_hdr_id NUMBER
2034 ) IS
2035 SELECT currency_code
2036 FROM okc_k_headers_all_b
2037 WHERE ID = p_hdr_id;
2038
2039 l_currency VARCHAR2 (15);
2040 l_usage_type VARCHAR2 (10);
2041 --New variables for partial periods
2042 l_period_type VARCHAR2 (30);
2043 l_period_start VARCHAR2 (30);
2044 l_period_type_orig VARCHAR2 (30);
2045 l_period_start_orig VARCHAR2 (30);
2046 l_price_uom VARCHAR2 (30);
2047 l_partial_period_flag VARCHAR2 (10);
2048 l_period_type_old VARCHAR2 (30);
2049 l_period_start_old VARCHAR2 (30);
2050 l_price_uom_old VARCHAR2 (30);
2051
2052 -- Bug Fix 4363689
2053 /*
2054 Manually priced lines
2055 8 : Party
2056 10 : Site
2057 11 : System
2058 13 : Usage Counter (NPR only)
2059 35 : Customer
2060 */
2061 CURSOR l_man_priced_lines_csr IS
2062 SELECT ID,
2063 start_date,
2064 end_date,
2065 lse_id,
2066 price_negotiated,
2067 price_unit,
2068 cle_id,
2069 dnz_chr_id
2070 FROM okc_k_lines_b
2071 WHERE dnz_chr_id = p_reprice_rec.contract_id
2072 AND lse_id IN (8, 10, 11, 13, 35); -- Bug fix 4769124
2073 l_man_priced_line_rec l_man_priced_lines_csr%ROWTYPE;
2074
2075
2076 BEGIN
2077 -- start debug log
2078 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2079 fnd_log.STRING (fnd_log.level_procedure,
2080 g_module ||
2081 l_api_name,
2082 '100: Entered ' ||
2083 g_pkg_name ||
2084 '.' ||
2085 l_api_name
2086 );
2087 END IF;
2088
2089 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2090 p_perf_msg => 'Inside Reprice Api');
2091 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2092 p_perf_msg => 'Contract Id: ' ||
2093 p_reprice_rec.contract_id);
2094 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2095 p_perf_msg => 'Price List Id: ' ||
2096 p_reprice_rec.price_list_id);
2097 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2098 p_perf_msg => 'Price Type: ' ||
2099 p_reprice_rec.price_type);
2100 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2101 p_perf_msg => 'Markup Percent: ' ||
2102 p_reprice_rec.markup_percent);
2103 -- Make a save point, in case of error rollback
2104 DBMS_TRANSACTION.SAVEPOINT ('REPRICE_OKS');
2105 --Added by jvorugan begin new logic for Partial Periods
2106
2107 -- Bug 5139658
2108
2109 -- Bug 5337890
2110
2111 -- l_orig_system_id1 - refers to source contract id
2112 -- p_reprice_rec.contract_id - refers to target contract id
2113
2114 oks_renew_util_pub.get_period_defaults
2115 (p_hdr_id => p_reprice_rec.contract_id,
2116 p_org_id => NULL,
2117 x_period_type => l_period_type_orig,
2118 x_period_start => l_period_start_orig,
2119 x_price_uom => l_price_uom,
2120 x_return_status => l_return_status
2121 );
2122
2123 IF l_return_status <> g_ret_sts_success THEN
2124 RAISE g_error;
2125 END IF;
2126
2127
2128 IF l_period_start_orig IS NOT NULL
2129 AND l_period_type_orig IS NOT NULL THEN
2130 l_partial_period_flag := 'TRUE';
2131 ELSE
2132 l_partial_period_flag := 'FALSE';
2133 END IF;
2134
2135 Open get_orig_system_id1 (p_reprice_rec.contract_id);
2136 Fetch get_orig_system_id1 into l_orig_system_id1;
2137 Close get_orig_system_id1;
2138
2139 oks_renew_util_pub.get_period_defaults
2140 (p_hdr_id => l_orig_system_id1,
2141 p_org_id => NULL,
2142 x_period_type => l_period_type_old,
2143 x_period_start => l_period_start_old,
2144 x_price_uom => l_price_uom_old,
2145 x_return_status => l_return_status
2146 );
2147
2148 IF l_return_status <> g_ret_sts_success THEN
2149 RAISE g_error;
2150 END IF;
2151
2152 -- End Bug 5337890
2153
2154 --end new logic for Partial Periods
2155 IF p_reprice_rec.price_type = 'LST' THEN
2156 OPEN l_pricelist_csr (p_reprice_rec.contract_id);
2157
2158 FETCH l_pricelist_csr
2159 INTO l_pricelist_id;
2160
2161 CLOSE l_pricelist_csr;
2162
2163 --Added by jvorugan begin new logic for Partial Periods
2164 IF l_period_type_old IS NOT NULL
2165 AND l_period_start_old IS NOT NULL
2166 AND l_price_uom_old IS NOT NULL THEN -- Bug 5139658
2167
2168 update_price_uom (p_chr_id => p_reprice_rec.contract_id,
2169 p_price_uom => l_price_uom_old,
2170 x_return_status => l_return_status);
2171
2172 -- Bug 5139658
2173 ELSE
2174
2175 Open get_org_id_csr (l_orig_system_id1);
2176 Fetch get_org_id_csr into l_org_id;
2177 Close get_org_id_csr;
2178
2179 oks_renew_util_pub.get_period_defaults
2180 ( p_hdr_id => NULL,
2181 p_org_id => l_org_id,
2182 x_period_type => l_period_type_orig,
2183 x_period_start => l_period_start_orig,
2184 x_price_uom => l_price_uom_old,
2185 x_return_status => l_return_status
2186 );
2187
2188 IF l_return_status <> g_ret_sts_success THEN
2189 RAISE g_error;
2190 END IF;
2191
2192 IF (l_price_uom_old is not null) THEN
2193
2194 update_price_uom (
2195 p_chr_id => p_reprice_rec.contract_id,
2196 p_price_uom => l_price_uom_old,
2197 x_return_status => l_return_status);
2198 END IF;
2199
2200 END IF; -- l_period_type IS NOT NULL
2201
2202
2203 IF l_return_status <> okc_api.g_ret_sts_success THEN
2204 RAISE g_error;
2205 END IF;
2206
2207 --End new logic for Partial Periods
2208 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2209 p_perf_msg => 'Derived PriceList Id: ' ||
2210 l_pricelist_id);
2211
2212 -- Bug 4628808
2213 IF p_reprice_rec.price_list_id <> NVL (l_pricelist_id, -9999) THEN
2214 l_chr_rec_in.ID := p_reprice_rec.contract_id;
2215 l_chr_rec_in.price_list_id := p_reprice_rec.price_list_id;
2216 okc_contract_pub.update_contract_header
2217 (p_api_version => l_api_version,
2218 p_init_msg_list => l_init_msg_list,
2219 x_return_status => l_return_status,
2220 x_msg_count => l_msg_count,
2221 x_msg_data => l_msg_data,
2222 p_restricted_update => 'N',
2223 p_chrv_rec => l_chr_rec_in,
2224 x_chrv_rec => l_chr_rec_out
2225 );
2226 oks_renew_pvt.debug_log
2227 (p_program_name => 'Reprice',
2228 p_perf_msg => 'Status After Price List Update: ' ||
2229 l_return_status);
2230
2231 IF l_return_status <> g_ret_sts_success THEN
2232 RAISE g_error;
2233 END IF;
2234 END IF;
2235
2236 l_detail_rec.chr_id := p_reprice_rec.contract_id;
2237 l_detail_rec.intent := 'HP';
2238 oks_renew_pvt.debug_log
2239 (p_program_name => 'Reprice',
2240 p_perf_msg => 'Calling Pricing Api with intent ' ||
2241 l_detail_rec.intent);
2242 --This api will take care about updating the amounts of the contract.
2243 oks_qp_int_pvt.compute_price
2244 (p_api_version => p_api_version,
2245 p_init_msg_list => p_init_msg_list,
2246 p_detail_rec => l_detail_rec,
2247 x_price_details => l_price_details,
2248 x_modifier_details => l_modifier_details,
2249 x_price_break_details => l_price_break_details,
2250 x_return_status => l_return_status,
2251 x_msg_count => l_msg_count,
2252 x_msg_data => l_msg_data
2253 );
2254 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2255 p_perf_msg => 'Status After Compute Price: ' ||
2256 l_return_status);
2257
2258 IF l_return_status <> g_ret_sts_success THEN
2259 RAISE g_error;
2260 END IF;
2261
2262 -- Bug Fix 4363689
2263 -- added SKEKKAR to prorate manually priced lines
2264 OPEN l_currcode_csr (p_reprice_rec.contract_id);
2265
2266 FETCH l_currcode_csr
2267 INTO l_currency;
2268
2269 CLOSE l_currcode_csr;
2270
2271 l_period_type := l_period_type_orig;
2272
2273 OPEN l_man_priced_lines_csr;
2274
2275 LOOP
2276 FETCH l_man_priced_lines_csr
2277 INTO l_man_priced_line_rec;
2278
2279 EXIT WHEN l_man_priced_lines_csr%NOTFOUND;
2280
2281 BEGIN
2282 -- Step 1 : Skip Usage lines if not NPR
2283 -- Check if the line is usage and of type negotiated price
2284 IF l_man_priced_line_rec.lse_id = 13 THEN
2285 -- Initialize local variable so that it'll not mess up while looping
2286 l_usage_type := NULL;
2287
2288 --added by mchoudha for bug#5191528
2289 --for usage the period start should always be 'SERVICE'
2290
2291 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
2292 l_period_start := 'SERVICE';
2293 END IF;
2294
2295
2296 OPEN l_usage_type_csr (l_man_priced_line_rec.cle_id);
2297
2298 FETCH l_usage_type_csr
2299 INTO l_usage_type;
2300
2301 CLOSE l_usage_type_csr;
2302
2303 -- Prorate only if it is negotaietd price
2304 IF NVL (l_usage_type, '-99') <> 'NPR' THEN
2305 RAISE g_skip_proration;
2306 END IF;
2307 END IF;
2308
2309 -- Step 2: Prorate all other manually priced lines
2310 get_oldcontractline_dates (p_cle_id => l_man_priced_line_rec.ID,
2311 p_opn_code => 'RENEWAL',
2312 x_old_start_date => l_old_start_date,
2313 x_old_end_date => l_old_end_date
2314 );
2315
2316 --begin new logic for Partial Periods
2317 --If Partial period flag set,then calculate l_proprated_price_neg based on partial period logic
2318 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
2319 l_prorated_price_neg :=
2320 do_prorating_pp
2321 (p_new_start_date => l_man_priced_line_rec.start_date,
2322 p_new_end_date => l_man_priced_line_rec.end_date,
2323 p_old_start_date => l_old_start_date,
2324 p_old_end_date => l_old_end_date,
2325 p_amount => l_man_priced_line_rec.price_negotiated,
2326 p_period_type => l_period_type,
2327 p_period_start => l_period_start
2328 );
2329 ELSE
2330 l_prorated_price_neg :=
2331 do_prorating
2332 (p_new_start_date => l_man_priced_line_rec.start_date,
2333 p_new_end_date => l_man_priced_line_rec.end_date,
2334 p_old_start_date => l_old_start_date,
2335 p_old_end_date => l_old_end_date,
2336 p_amount => l_man_priced_line_rec.price_negotiated
2337 );
2338 END IF;
2339
2340 --end new logic for Partial Periods
2341 /*Added for GSI Bug 14102072*/
2342 If nvl(FND_PROFILE.value('OKS_PRICE_ROUNDING'),'N') ='N' THEN
2343 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
2344 Else
2345
2346 l_prorated_price_neg := oks_extwar_util_pvt.round_currency_amt
2347 (p_amount => l_prorated_price_neg,
2348 p_currency_code => l_currency);
2349
2350 End if;
2351 /*Added for GSI Bug 14102072*/
2352 oks_renew_pvt.debug_log
2353 (p_program_name => 'Reprice',
2354 p_perf_msg => 'Prorated Negotiated Price: ' ||
2355 l_prorated_price_neg);
2356 --Update Manually priced Lines With the Prorated Amounts(Price Negotiated and Unit Price)
2357 l_clev_rec.ID := l_man_priced_line_rec.ID;
2358 l_clev_rec.price_negotiated := l_prorated_price_neg;
2359 -- Update line's price negotiated only, unit price remains same.
2360 update_line (p_clev_rec => l_clev_rec,
2361 x_return_status => l_return_status);
2362 oks_renew_pvt.debug_log
2363 (p_program_name => 'Reprice',
2364 p_perf_msg => 'Status after line updation: ' ||
2365 l_return_status);
2366
2367 IF l_return_status <> g_ret_sts_success THEN
2368 RAISE g_error;
2369 END IF;
2370
2371 calculate_tax (p_chr_id => p_reprice_rec.contract_id,
2372 p_cle_id => l_man_priced_line_rec.ID,
2373 p_amount => l_prorated_price_neg,
2374 x_return_status => l_return_status
2375 );
2376 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2377 p_perf_msg => 'Status after Tax: ' ||
2378 l_return_status);
2379
2380 IF l_return_status <> g_ret_sts_success THEN
2381 RAISE g_error;
2382 END IF;
2383 EXCEPTION
2384 WHEN g_skip_proration THEN
2385 NULL;
2386 END;
2387 END LOOP;
2388 CLOSE l_man_priced_lines_csr;
2389 -- l_man_priced_lines_csr
2390 -- end added SKEKKAR to prorate manually priced lines
2391 ELSE
2392 -- price_type in 'MAN' or 'PCT'
2393 OPEN l_currcode_csr (p_reprice_rec.contract_id);
2394
2395 FETCH l_currcode_csr
2396 INTO l_currency;
2397
2398 CLOSE l_currcode_csr;
2399
2400 OPEN l_line_csr;
2401
2402 LOOP
2403 FETCH l_line_csr
2404 INTO l_line_rec;
2405
2406 EXIT WHEN l_line_csr%NOTFOUND;
2407
2408 BEGIN
2409 --added by mchoudha for bug#5191528
2410 --for usage the period start should always be 'SERVICE'
2411 --for effectivity based intnagible subscriptions period start should always be 'SERVICE'
2412 --for tangible subscriptions and subscription based intangible subscriptions
2413 --partial periods should be ignored as per CR1
2414 l_period_start := l_period_start_orig;
2415 l_period_type := l_period_type_orig;
2416 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
2417 IF l_line_rec.lse_id = 12 THEN
2418 l_period_start := 'SERVICE';
2419 END IF;
2420 IF l_line_rec.lse_id = 46 THEN
2421 l_tangible := OKS_SUBSCRIPTION_PUB.is_subs_tangible (l_line_rec.id);
2422 IF l_tangible THEN
2423 l_period_start := NULL;
2424 l_period_type := NULL;
2425 ELSE
2426 l_pricing_method :=FND_PROFILE.value('OKS_SUBS_PRICING_METHOD');
2427 IF nvl(l_pricing_method,'SUBSCRIPTION') <> 'EFFECTIVITY' THEN
2428 l_period_start := NULL;
2429 l_period_type := NULL;
2430 ELSE
2431 l_period_start := 'SERVICE';
2432 END IF; -- l_pricing_method <> 'EFFECTIVITY'
2433 END IF; -- IF l_tangible
2434 END IF; -- l_Line_Csr_Rec.lse_id = 46
2435 END IF;
2436
2437
2438 -- Check if the line is usage and of type negotiated price
2439 IF l_line_rec.lse_id = 13 THEN
2440 -- Initialize local variable so that it'll not mess up while looping
2441 l_usage_type := NULL;
2442
2443 OPEN l_usage_type_csr (l_line_rec.cle_id);
2444
2445 FETCH l_usage_type_csr
2446 INTO l_usage_type;
2447
2448 CLOSE l_usage_type_csr;
2449
2450 -- Prorate only if it is negotaietd price
2451 IF NVL (l_usage_type, '-99') <> 'NPR' THEN
2452 RAISE g_skip_proration;
2453 END IF;
2454 END IF;
2455
2456 --PRORATE AND OTHER STUFFS
2457 get_oldcontractline_dates (p_cle_id => l_line_rec.ID,
2458 p_opn_code => 'RENEWAL',
2459 x_old_start_date => l_old_start_date,
2460 x_old_end_date => l_old_end_date
2461 );
2462 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2463 p_perf_msg => 'Old Start Date: ' ||
2464 l_old_start_date ||
2465 ' Old End Date: ' ||
2466 l_old_end_date);
2467
2468 --begin new logic for Partial Periods
2469 --If Partial period flag set,then calculate l_proprated_price_neg based on partial period logic
2470 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
2471 --mchoudha for bug#5488312
2472 l_prorated_price_neg :=
2473 do_prorating_pp (
2474 p_id => l_line_rec.id,
2475 p_lse_id => l_line_rec.lse_id,
2476 p_unit_price => l_line_rec.price_unit,
2477 p_new_start_date => l_line_rec.start_date,
2478 p_new_end_date => l_line_rec.end_date,
2479 p_old_start_date => l_old_start_date,
2480 p_old_end_date => l_old_end_date,
2481 p_amount => l_line_rec.price_negotiated,
2482 p_period_type => l_period_type,
2483 p_period_start => l_period_start
2484 );
2485 ELSE
2486 l_prorated_price_neg :=
2487 do_prorating (p_new_start_date => l_line_rec.start_date,
2488 p_new_end_date => l_line_rec.end_date,
2489 p_old_start_date => l_old_start_date,
2490 p_old_end_date => l_old_end_date,
2491 p_amount => l_line_rec.price_negotiated
2492 );
2493 END IF;
2494
2495 --end new logic for Partial Periods
2496 /*Added for GSI Bug 14102072*/
2497 If nvl(FND_PROFILE.value('OKS_PRICE_ROUNDING'),'N') ='N' THEN
2498 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
2499 Else
2500 l_prorated_price_neg := oks_extwar_util_pvt.round_currency_amt
2501 (p_amount => l_prorated_price_neg,
2502 p_currency_code => l_currency);
2503 End if;
2504 /*Added for GSI Bug 14102072*/
2505 oks_renew_pvt.debug_log
2506 (p_program_name => 'Reprice',
2507 p_perf_msg => 'Prorated Negotiated Price: ' ||
2508 l_prorated_price_neg);
2509
2510 IF p_reprice_rec.price_type = 'MAN' THEN
2511 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
2512 l_clev_rec.ID := l_line_rec.ID;
2513 l_clev_rec.price_negotiated := l_prorated_price_neg;
2514 -- Update line's price negotiated only, unit price remains same.
2515 update_line (p_clev_rec => l_clev_rec,
2516 x_return_status => l_return_status);
2517 oks_renew_pvt.debug_log
2518 (p_program_name => 'Reprice',
2519 p_perf_msg => 'Status after line updation: ' ||
2520 l_return_status);
2521
2522 IF l_return_status <> g_ret_sts_success THEN
2523 RAISE g_error;
2524 END IF;
2525
2526 calculate_tax (p_chr_id => p_reprice_rec.contract_id,
2527 p_cle_id => l_line_rec.ID,
2528 p_amount => l_prorated_price_neg,
2529 x_return_status => l_return_status
2530 );
2531 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2532 p_perf_msg => 'Status after Tax: ' ||
2533 l_return_status);
2534
2535 IF l_return_status <> g_ret_sts_success THEN
2536 RAISE g_error;
2537 END IF;
2538
2539 IF l_line_rec.lse_id IN (7, 9, 25, 46) THEN
2540 l_price_details.serv_ext_amount := l_prorated_price_neg;
2541 l_price_details.status_text :=
2542 'Price derived using manual pricing';
2543
2544 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
2545 ) THEN
2546 fnd_log.STRING (fnd_log.level_statement,
2547 g_module ||
2548 l_api_name,
2549 '131:l_price_details.status_text: ' ||
2550 l_price_details.status_text
2551 );
2552 fnd_log.STRING (fnd_log.level_statement,
2553 g_module ||
2554 l_api_name,
2555 '132:l_price_details.serv_ext_amount: ' ||
2556 l_price_details.serv_ext_amount
2557 );
2558 END IF;
2559
2560 --errorout_urp('l_price_details.status_text: ' || l_price_details.status_text);
2561 --errorout_urp('l_price_details.serv_ext_amount: ' || l_price_details.serv_ext_amount);
2562 update_price_info (p_chr_id => p_reprice_rec.contract_id,
2563 p_cle_id => l_line_rec.ID,
2564 p_lse_id => l_line_rec.lse_id,
2565 p_price_type => p_reprice_rec.price_type,
2566 p_price_details => l_price_details,
2567 x_return_status => l_return_status
2568 );
2569 oks_renew_pvt.debug_log
2570 (p_program_name => 'Reprice',
2571 p_perf_msg => 'Status after updating pricing info: ' ||
2572 l_return_status);
2573
2574 IF l_return_status <> g_ret_sts_success THEN
2575 RAISE g_error;
2576 END IF;
2577 END IF;
2578 ELSE -- Process PCT - Markup percentage
2579 -- bug 4886923 (forward port 4887983) skekkar
2580 -- prorate Unit price only if the subline is not item, product , subscription
2581 -- for any pricelist subline, unit price should NOT be prorated
2582 IF l_line_rec.lse_id IN(7,9,25,46) THEN
2583 -- unit price is same as the original contract
2584 l_prorated_unit_price:= l_line_rec.price_unit;
2585 ELSE
2586 --begin new logic for Partial Periods
2587 --If Partial period flag set,then calculate l_proprated_unit_price based on partial period logic
2588 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
2589 l_prorated_unit_price :=
2590 do_prorating_pp (p_new_start_date => l_line_rec.start_date,
2591 p_new_end_date => l_line_rec.end_date,
2592 p_old_start_date => l_old_start_date,
2593 p_old_end_date => l_old_end_date,
2594 p_amount => l_line_rec.price_unit,
2595 p_period_type => l_period_type,
2596 p_period_start => l_period_start
2597 );
2598 ELSE
2599 l_prorated_unit_price :=
2600 do_prorating (p_new_start_date => l_line_rec.start_date,
2601 p_new_end_date => l_line_rec.end_date,
2602 p_old_start_date => l_old_start_date,
2603 p_old_end_date => l_old_end_date,
2604 p_amount => l_line_rec.price_unit
2605 );
2606 END IF; -- partial_period_flag
2607 END IF; -- end added bug 4886923 (forward port 4887983) don't prorate unit price for pricelist items
2608
2609 --end new logic for Partial Periods
2610
2611 -- added skekkar
2612 -- PCT was NOT prorating manually priced lines with markup percentage
2613 -- IF l_line_rec.lse_id NOT IN (7, 9, 25, 46) THEN
2614 IF l_line_rec.lse_id IN (8, 10, 11, 13, 35) THEN -- bug 4769124
2615 --for non priced lines, compute Prorated Amout and update
2616 --price negotiated
2617 l_prorated_price_neg :=
2618 NVL (l_prorated_price_neg, 0) +
2619 ((NVL (l_prorated_price_neg, 0) *
2620 NVL (p_reprice_rec.markup_percent, 0)
2621 ) /
2622 100
2623 );
2624 /*Added for bug 14102072*/
2625 If nvl(FND_PROFILE.value('OKS_PRICE_ROUNDING'),'N') ='N' THEN
2626 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
2627 Else
2628 l_prorated_price_neg := oks_extwar_util_pvt.round_currency_amt
2629 (p_amount => l_prorated_price_neg,
2630 p_currency_code => l_currency);
2631 End If;
2632 /*Added for bug 14102072*/
2633 END IF; -- l_line_rec.lse_id IN (8, 10, 11, 13, 35)
2634
2635 -- end added skekkar
2636
2637 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
2638 l_clev_rec.ID := l_line_rec.ID;
2639 l_clev_rec.price_negotiated := l_prorated_price_neg;
2640 l_clev_rec.price_unit := l_prorated_unit_price;
2641 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2642 p_perf_msg => 'Prorated unit price: ' ||
2643 l_clev_rec.price_negotiated);
2644 update_line (p_clev_rec => l_clev_rec,
2645 x_return_status => l_return_status);
2646 oks_renew_pvt.debug_log
2647 (p_program_name => 'Reprice',
2648 p_perf_msg => 'Status after updating line: ' ||
2649 l_return_status);
2650
2651 IF l_return_status <> g_ret_sts_success THEN
2652 RAISE g_error;
2653 END IF;
2654
2655 IF l_line_rec.lse_id IN (7, 9, 25, 46) THEN
2656 --Prorated Amout should markedup/down then pass to find the actual
2657 --price negotiated
2658 l_prorated_price_neg :=
2659 NVL (l_prorated_price_neg, 0) +
2660 ((NVL (l_prorated_price_neg, 0) *
2661 NVL (p_reprice_rec.markup_percent, 0)
2662 ) /
2663 100
2664 );
2665 /*Added for bug 14102072*/
2666 If nvl(FND_PROFILE.value('OKS_PRICE_ROUNDING'),'N') ='N' THEN
2667 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
2668 Else
2669 l_prorated_price_neg := oks_extwar_util_pvt.round_currency_amt
2670 (p_amount => l_prorated_price_neg,
2671 p_currency_code => l_currency);
2672 End If;
2673 /*Added for bug 14102072*/
2674 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2675 p_perf_msg => 'Prorated price: ' ||
2676 l_prorated_price_neg);
2677
2678 IF p_reprice_rec.price_list_id IS NOT NULL THEN
2679 IF l_line_rec.lse_id = 46 THEN
2680 l_intent := 'LM';
2681 ELSE
2682 l_intent := 'SM';
2683 END IF;
2684
2685 calculate_price (p_chr_id => p_reprice_rec.contract_id,
2686 p_line_id => l_line_rec.ID,
2687 p_linecle_id => l_line_rec.cle_id,
2688 p_lse_id => l_line_rec.lse_id,
2689 p_intent => l_intent,
2690 p_price_type => 'PCT',
2691 p_price_list => p_reprice_rec.price_list_id,
2692 p_amount => l_prorated_price_neg,
2693 x_return_status => l_return_status
2694 );
2695 oks_renew_pvt.debug_log
2696 (p_program_name => 'Reprice',
2697 p_perf_msg => 'Status after calculate_price: ' ||
2698 l_return_status);
2699
2700 IF l_return_status <> g_ret_sts_success THEN
2701 RAISE g_error;
2702 END IF;
2703 ELSE
2704 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
2705 l_clev_rec.ID := l_line_rec.ID;
2706 l_clev_rec.price_negotiated := l_prorated_price_neg;
2707 update_line (p_clev_rec => l_clev_rec,
2708 x_return_status => l_return_status);
2709 oks_renew_pvt.debug_log
2710 (p_program_name => 'Reprice',
2711 p_perf_msg => 'Status after updating line: ' ||
2712 l_return_status);
2713
2714 IF l_return_status <> g_ret_sts_success THEN
2715 RAISE g_error;
2716 END IF;
2717
2718 l_price_details.serv_ext_amount := l_prorated_price_neg;
2719 l_price_details.status_text :=
2720 'Price derived using markup pricing';
2721
2722 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
2723 ) THEN
2724 fnd_log.STRING (fnd_log.level_statement,
2725 g_module ||
2726 l_api_name,
2727 '133:l_price_details.status_text: ' ||
2728 l_price_details.status_text
2729 );
2730 fnd_log.STRING (fnd_log.level_statement,
2731 g_module ||
2732 l_api_name,
2733 '134:l_price_details.serv_ext_amount: ' ||
2734 l_price_details.serv_ext_amount
2735 );
2736 END IF;
2737
2738 --errorout_urp('l_price_details.status_text: ' || l_price_details.status_text);
2739 --errorout_urp('l_price_details.serv_ext_amount: ' || l_price_details.serv_ext_amount);
2740 update_price_info (p_chr_id => p_reprice_rec.contract_id,
2741 p_cle_id => l_line_rec.ID,
2742 p_lse_id => l_line_rec.lse_id,
2743 p_price_type => p_reprice_rec.price_type,
2744 p_price_details => l_price_details,
2745 x_return_status => l_return_status
2746 );
2747 oks_renew_pvt.debug_log
2748 (p_program_name => 'Reprice',
2749 p_perf_msg => 'Status in CP2 API in PCT where PL is NULL: ' ||
2750 l_return_status);
2751
2752 IF l_return_status <> g_ret_sts_success THEN
2753 RAISE g_error;
2754 END IF;
2755
2756 -- Update Subsciption elements
2757 IF l_line_rec.lse_id = 46 THEN
2758 FOR l_subs_elements_rec IN
2759 l_subs_elements_csr (NVL (l_line_rec.cle_id,
2760 l_line_rec.ID)) LOOP
2761 l_scev_rec_in.ID := l_subs_elements_rec.ID;
2762 l_scev_rec_in.amount :=
2763 l_subs_elements_rec.quantity *
2764 l_prorated_unit_price;
2765 l_scev_rec_in.object_version_number :=
2766 l_subs_elements_rec.object_version_number;
2767 oks_subscr_elems_pub.update_row
2768 (p_api_version => l_api_version,
2769 p_init_msg_list => l_init_msg_list,
2770 x_return_status => l_return_status,
2771 x_msg_count => l_msg_count,
2772 x_msg_data => l_msg_data,
2773 p_scev_rec => l_scev_rec_in,
2774 x_scev_rec => l_scev_rec_out
2775 );
2776 oks_renew_pvt.debug_log
2777 (p_program_name => 'Reprice',
2778 p_perf_msg => 'Status after updating Subscription elements: ' ||
2779 l_return_status);
2780
2781 IF l_return_status <> g_ret_sts_success THEN
2782 RAISE g_error;
2783 END IF;
2784 END LOOP;
2785 END IF;
2786
2787 --Calculate Tax For Toplines(All Covered Levels for Service
2788 --and Extended Warranty)
2789 calculate_tax (p_chr_id => p_reprice_rec.contract_id,
2790 p_cle_id => l_line_rec.ID,
2791 p_amount => l_prorated_price_neg,
2792 x_return_status => l_return_status
2793 );
2794 oks_renew_pvt.debug_log
2795 (p_program_name => 'Reprice',
2796 p_perf_msg => 'Status after Tax - ' ||
2797 l_return_status);
2798
2799 IF l_return_status <> g_ret_sts_success THEN
2800 RAISE g_error;
2801 END IF;
2802 END IF;
2803 ELSE
2804 calculate_tax (p_chr_id => p_reprice_rec.contract_id,
2805 p_cle_id => l_line_rec.ID,
2806 p_amount => l_prorated_price_neg,
2807 x_return_status => l_return_status
2808 );
2809 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2810 p_perf_msg => 'Status after Tax = ' ||
2811 l_return_status);
2812
2813 IF l_return_status <> g_ret_sts_success THEN
2814 RAISE g_error;
2815 END IF; -- Status Check after Tax Call
2816 END IF; -- Lse If check within price type <> 'MAN'
2817 END IF; -- Price Type
2818
2819 -- end debug log
2820 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2821 fnd_log.STRING (fnd_log.level_procedure,
2822 g_module ||
2823 l_api_name,
2824 '1000: Leaving ' ||
2825 g_pkg_name ||
2826 '.' ||
2827 l_api_name
2828 );
2829 END IF;
2830 EXCEPTION
2831 WHEN g_skip_proration THEN
2832 -- end debug log
2833 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2834 fnd_log.STRING (fnd_log.level_procedure,
2835 g_module ||
2836 l_api_name,
2837 '9200: Leaving ' ||
2838 g_pkg_name ||
2839 '.' ||
2840 l_api_name
2841 );
2842 END IF;
2843
2844 NULL;
2845 END;
2846 END LOOP; -- Loop For linecursor
2847
2848 CLOSE l_line_csr;
2849
2850 --FOR MAN and PCT the simple api is called and it will not update the amounts of the contract
2851 --Because of this update_amounts need to be called....
2852 update_amounts (p_chr_id => p_reprice_rec.contract_id,
2853 x_return_status => l_return_status);
2854 oks_renew_pvt.debug_log
2855 (p_program_name => 'Reprice',
2856 p_perf_msg => 'Status after updating amounts: ' ||
2857 l_return_status);
2858
2859 IF l_return_status <> g_ret_sts_success THEN
2860 RAISE g_error;
2861 END IF;
2862 END IF; --For Price List(LST)
2863
2864 x_return_status := l_return_status;
2865 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2866 p_perf_msg => 'Final Status: ' ||
2867 l_return_status);
2868
2869 -- end debug log
2870 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2871 fnd_log.STRING (fnd_log.level_procedure,
2872 g_module ||
2873 l_api_name,
2874 '1000: Leaving ' ||
2875 g_pkg_name ||
2876 '.' ||
2877 l_api_name
2878 );
2879 END IF;
2880 EXCEPTION
2881 WHEN g_error THEN
2882 -- end debug log
2883 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2884 fnd_log.STRING (fnd_log.level_procedure,
2885 g_module ||
2886 l_api_name,
2887 '2500: Leaving ' ||
2888 g_pkg_name ||
2889 '.' ||
2890 l_api_name
2891 );
2892 END IF;
2893
2894 DBMS_TRANSACTION.rollback_savepoint ('REPRICE_OKS');
2895
2896 IF l_line_csr%ISOPEN THEN
2897 CLOSE l_line_csr;
2898 END IF;
2899
2900 x_return_status := l_return_status;
2901 WHEN OTHERS THEN
2902 -- end debug log
2903 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2904 fnd_log.STRING (fnd_log.level_procedure,
2905 g_module ||
2906 l_api_name,
2907 '4000: Leaving ' ||
2908 g_pkg_name ||
2909 '.' ||
2910 l_api_name
2911 );
2912 END IF;
2913
2914 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2915 DBMS_TRANSACTION.rollback_savepoint ('REPRICE_OKS');
2916 okc_api.set_message (p_app_name => g_app_name_oks,
2917 p_msg_name => g_unexpected_error,
2918 p_token1 => g_sqlcode_token,
2919 p_token1_value => SQLCODE,
2920 p_token2 => g_sqlerrm_token,
2921 p_token2_value => SQLERRM
2922 );
2923 x_return_status := g_ret_sts_unexp_error;
2924 END call_pricing_api;
2925
2926 -- Pricing API for Renewal consolidation
2927 PROCEDURE call_pricing_api (
2928 p_api_version IN NUMBER,
2929 p_init_msg_list IN VARCHAR2,
2930 x_return_status OUT NOCOPY VARCHAR2,
2931 x_msg_count OUT NOCOPY NUMBER,
2932 x_msg_data OUT NOCOPY VARCHAR2,
2933 p_subject_chr_id IN NUMBER,
2934 p_subject_top_line_id IN NUMBER,
2935 p_subject_sub_line_tbl IN sub_line_tbl_type
2936 ) IS
2937 l_api_name CONSTANT VARCHAR2 (30)
2938 := 'call_pricing_api';
2939
2940 CURSOR l_k_details_csr (
2941 p_chr_id NUMBER
2942 ) IS
2943 SELECT currency_code,
2944 price_list_id
2945 FROM okc_k_headers_all_b
2946 WHERE ID = p_chr_id;
2947
2948 /* for bug 6392202 - FP 6209038, we removed the hard-coded pricing type as LST*/
2949 CURSOR l_srv_k_details_csr (
2950 p_chr_id NUMBER
2951 ) IS
2952 SELECT renewal_price_list,
2953 nvl(RENEWAL_PRICING_TYPE,'LST') AS renewal_pricing_type, -- bug 6392202 and modified for bug 15995481
2954 --'LST' AS renewal_pricing_type, -- Bug 6016714
2955 renewal_markup_percent,
2956 chr_id,
2957 ID
2958 FROM oks_k_headers_b
2959 WHERE chr_id = p_chr_id;
2960
2961 CURSOR l_line_csr (
2962 p_line_id NUMBER
2963 ) IS
2964 SELECT ID,
2965 start_date,
2966 end_date,
2967 lse_id,
2968 price_negotiated,
2969 price_unit,
2970 cle_id,
2971 dnz_chr_id
2972 FROM okc_k_lines_b
2973 WHERE ID = p_line_id;
2974
2975 CURSOR l_usage_type_csr (
2976 p_cle_id NUMBER
2977 ) IS
2978 SELECT usage_type
2979 FROM oks_k_lines_b
2980 WHERE cle_id = p_cle_id;
2981
2982 CURSOR l_subs_elements_csr (
2983 p_line_id NUMBER
2984 ) IS
2985 SELECT ID,
2986 osh_id,
2987 dnz_chr_id,
2988 dnz_cle_id,
2989 linked_flag,
2990 seq_no,
2991 om_interface_date,
2992 amount,
2993 start_date,
2994 end_date,
2995 quantity,
2996 uom_code,
2997 order_header_id,
2998 order_line_id,
2999 object_version_number
3000 FROM oks_subscr_elements
3001 WHERE dnz_cle_id = p_line_id;
3002 -- Added by Jvorugan
3003
3004 -- Bug 5337890
3005
3006 l_new_duration NUMBER;
3007 l_tangible BOOLEAN;
3008 l_pricing_method Varchar2(30);
3009
3010 -- End of changes by Jvorugan
3011
3012
3013 l_prorated_price_neg NUMBER;
3014 l_prorated_unit_price NUMBER;
3015 l_msg_count NUMBER;
3016 l_index_sub_line_tbl NUMBER;
3017 l_api_version NUMBER := 1.0;
3018 l_msg_data VARCHAR2 (2000);
3019 l_usage_type VARCHAR2 (10);
3020 l_currency VARCHAR2 (15);
3021 l_intent VARCHAR2 (10);
3022 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
3023 l_return_status VARCHAR2 (1) := g_ret_sts_success;
3024 l_old_start_date DATE;
3025 l_old_end_date DATE;
3026 l_clev_rec okc_contract_pub.clev_rec_type;
3027 l_chr_rec_in okc_contract_pub.chrv_rec_type;
3028 l_chr_rec_out okc_contract_pub.chrv_rec_type;
3029 l_scev_rec_in oks_subscr_elems_pub.scev_rec_type;
3030 l_scev_rec_out oks_subscr_elems_pub.scev_rec_type;
3031 l_modifier_details qp_preq_grp.line_detail_tbl_type;
3032 l_detail_rec oks_qp_pkg.input_details;
3033 l_price_break_details oks_qp_pkg.g_price_break_tbl_type;
3034 l_price_details oks_qp_pkg.price_details := NULL;
3035 l_k_details_rec l_k_details_csr%ROWTYPE;
3036 l_srv_k_details_rec l_srv_k_details_csr%ROWTYPE;
3037 l_line_rec l_line_csr%ROWTYPE;
3038 --new variables for partial periods
3039 l_period_type VARCHAR2 (30);
3040 l_period_start VARCHAR2 (30);
3041 l_price_uom VARCHAR2 (30);
3042 l_partial_period_flag VARCHAR2 (10);
3043 l_period_type_orig VARCHAR2 (30);
3044 l_period_start_orig VARCHAR2 (30);
3045 BEGIN
3046 -- start debug log
3047 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3048 fnd_log.STRING (fnd_log.level_procedure,
3049 g_module ||
3050 l_api_name,
3051 '100: Entered ' ||
3052 g_pkg_name ||
3053 '.' ||
3054 l_api_name
3055 );
3056 END IF;
3057
3058 fnd_file.put_line(FND_FILE.LOG,' ');
3059 fnd_file.put_line(FND_FILE.LOG,'********************************************************** ');
3060 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
3061 fnd_file.put_line(FND_FILE.LOG,'Inside PROCEDURE call_pricing_api for Renewal consolidation');
3062 fnd_file.put_line(FND_FILE.LOG,'p_subject_chr_id : '||p_subject_chr_id);
3063 fnd_file.put_line(FND_FILE.LOG,'p_subject_top_line_id : '||p_subject_top_line_id);
3064 fnd_file.put_line(FND_FILE.LOG,'p_subject_sub_line_tbl.COUNT : '||p_subject_sub_line_tbl.COUNT);
3065 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
3066 fnd_file.put_line(FND_FILE.LOG,' ');
3067
3068 DBMS_TRANSACTION.SAVEPOINT ('REPRICE_OKS');
3069
3070 OPEN l_k_details_csr (p_subject_chr_id);
3071 FETCH l_k_details_csr
3072 INTO l_k_details_rec;
3073 CLOSE l_k_details_csr;
3074
3075 fnd_file.put_line(FND_FILE.LOG,'l_k_details_rec.price_list_id : '||l_k_details_rec.price_list_id);
3076 fnd_file.put_line(FND_FILE.LOG,'l_k_details_rec.currency_code : '||l_k_details_rec.currency_code);
3077
3078 OPEN l_srv_k_details_csr (p_subject_chr_id);
3079 FETCH l_srv_k_details_csr
3080 INTO l_srv_k_details_rec;
3081 CLOSE l_srv_k_details_csr;
3082
3083 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_pricing_type : '||l_srv_k_details_rec.renewal_pricing_type);
3084 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_price_list : '||l_srv_k_details_rec.renewal_price_list);
3085 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_markup_percent : '||l_srv_k_details_rec.renewal_markup_percent);
3086
3087 oks_renew_pvt.debug_log
3088 (p_program_name => 'Renewal Consolidation Reprice::',
3089 p_perf_msg => 'PRICING TYPE = ' ||
3090 l_srv_k_details_rec.renewal_pricing_type);
3091 oks_renew_pvt.debug_log
3092 (p_program_name => 'Renewal Consolidation Reprice::',
3093 p_perf_msg => 'PRICE LIST = ' ||
3094 l_srv_k_details_rec.renewal_price_list);
3095 oks_renew_pvt.debug_log
3096 (p_program_name => 'Renewal Consolidation Reprice::',
3097 p_perf_msg => 'MARKUP = ' ||
3098 l_srv_k_details_rec.renewal_markup_percent);
3099
3100 IF l_srv_k_details_rec.renewal_pricing_type = 'LST' THEN
3101 l_chr_rec_in.ID := p_subject_chr_id;
3102 l_chr_rec_in.price_list_id := l_k_details_rec.price_list_id;
3103 l_currency := l_k_details_rec.currency_code;
3104 oks_renew_pvt.debug_log
3105 (p_program_name => 'Renewal Consolidation Reprice::',
3106 p_perf_msg => 'Pricing type - Price List - Header Pricing');
3107
3108 fnd_file.put_line(FND_FILE.LOG,'renewal_pricing_type = LST, Calling okc_contract_pub.update_contract_header');
3109
3110 okc_contract_pub.update_contract_header
3111 (p_api_version => l_api_version,
3112 p_init_msg_list => l_init_msg_list,
3113 x_return_status => l_return_status,
3114 x_msg_count => l_msg_count,
3115 x_msg_data => l_msg_data,
3116 p_restricted_update => 'N',
3117 p_chrv_rec => l_chr_rec_in,
3118 x_chrv_rec => l_chr_rec_out
3119 );
3120
3121 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling okc_contract_pub.update_contract_header x_return_status= '||l_return_status);
3122
3123 oks_renew_pvt.debug_log
3124 (p_program_name => 'Renewal Consolidation Reprice::',
3125 p_perf_msg => 'UPDATE CONTRACT HDR - RETURN = ' ||
3126 l_return_status);
3127
3128 IF l_return_status <> g_ret_sts_success THEN
3129 RAISE g_error;
3130 END IF;
3131
3132 l_detail_rec.chr_id := p_subject_chr_id;
3133 -- bug 6114024 , changed the intent to HP as before as the call
3134 -- to pricing api will be done once for the whole contract in OKS_RENCON_PVT
3135
3136 l_detail_rec.intent := 'HP';
3137
3138 -- Bug 6016714
3139 -- l_detail_rec.line_id := p_subject_top_line_id;
3140 -- l_detail_rec.intent := 'LP';
3141 -- end added bug 6016714
3142
3143 oks_renew_pvt.debug_log
3144 (p_program_name => 'Renewal Consolidation Reprice::',
3145 p_perf_msg => 'HDR PRICING - CONTRACT ID= ' ||
3146 l_detail_rec.chr_id);
3147
3148 fnd_file.put_line(FND_FILE.LOG,'Calling oks_qp_int_pvt.compute_price ');
3149
3150 --This api will take care about updating the amounts of the contract.
3151 oks_qp_int_pvt.compute_price
3152 (p_api_version => p_api_version,
3153 p_init_msg_list => p_init_msg_list,
3154 p_detail_rec => l_detail_rec,
3155 x_price_details => l_price_details,
3156 x_modifier_details => l_modifier_details,
3157 x_price_break_details => l_price_break_details,
3158 x_return_status => l_return_status,
3159 x_msg_count => l_msg_count,
3160 x_msg_data => l_msg_data
3161 );
3162
3163 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling oks_qp_int_pvt.compute_price x_return_status= '||l_return_status);
3164
3165 oks_renew_pvt.debug_log
3166 (p_program_name => 'Renewal Consolidation Reprice::',
3167 p_perf_msg => 'HDR REPRICE - RETURN = ' ||
3168 l_return_status);
3169
3170 IF l_return_status <> g_ret_sts_success THEN
3171 RAISE g_error;
3172 END IF;
3173 ELSE
3174 -- Renewal Price type is Manual or Markup. Each line has price to be updated individually.
3175 fnd_file.put_line(FND_FILE.LOG,'Renewal Price type is Manual or Markup');
3176
3177 oks_renew_pvt.debug_log
3178 (p_program_name => 'Renewal Consolidation Reprice::',
3179 p_perf_msg => 'NON PRICE LIST REPRICE - LINE/SUBLINE COUNT = ' ||
3180 p_subject_sub_line_tbl.COUNT);
3181 --begin new logic for Partial Periods
3182 oks_renew_util_pub.get_period_defaults
3183 (p_hdr_id => p_subject_chr_id,
3184 p_org_id => NULL,
3185 x_period_type => l_period_type_orig,
3186 x_period_start => l_period_start_orig,
3187 x_price_uom => l_price_uom,
3188 x_return_status => l_return_status
3189 );
3190
3191 fnd_file.put_line(FND_FILE.LOG,'After Calling oks_renew_util_pub.get_period_defaults ');
3192 fnd_file.put_line(FND_FILE.LOG,' x_return_status = '||l_return_status);
3193 fnd_file.put_line(FND_FILE.LOG,' x_period_type = '||l_period_type_orig);
3194 fnd_file.put_line(FND_FILE.LOG,' x_period_start = '||l_period_start_orig);
3195 fnd_file.put_line(FND_FILE.LOG,' x_price_uom = '||l_price_uom);
3196
3197 IF l_return_status <> g_ret_sts_success THEN
3198 RAISE g_error;
3199 END IF;
3200
3201 IF l_period_start_orig IS NOT NULL
3202 AND l_period_type_orig IS NOT NULL THEN
3203 l_partial_period_flag := 'TRUE';
3204 ELSE
3205 l_partial_period_flag := 'FALSE';
3206 END IF;
3207
3208 --end new logic for Partial Periods
3209 l_index_sub_line_tbl := p_subject_sub_line_tbl.FIRST;
3210
3211 IF p_subject_sub_line_tbl.COUNT > 0 THEN
3212 LOOP
3213 BEGIN
3214 OPEN l_line_csr (p_subject_sub_line_tbl (l_index_sub_line_tbl));
3215
3216 FETCH l_line_csr
3217 INTO l_line_rec;
3218
3219 CLOSE l_line_csr;
3220
3221 fnd_file.put_line(FND_FILE.LOG,' ');
3222 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
3223 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.ID : '||l_line_rec.ID);
3224 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.start_date : '||l_line_rec.start_date);
3225 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.end_date : '||l_line_rec.end_date);
3226 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.lse_id : '||l_line_rec.lse_id);
3227 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.price_negotiated : '||l_line_rec.price_negotiated);
3228 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.price_unit : '||l_line_rec.price_unit);
3229 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.cle_id : '||l_line_rec.cle_id);
3230 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.dnz_chr_id : '||l_line_rec.dnz_chr_id);
3231 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
3232 fnd_file.put_line(FND_FILE.LOG,' ');
3233
3234 oks_renew_pvt.debug_log
3235 (p_program_name => 'Renewal Consolidation Reprice::',
3236 p_perf_msg => 'NON PRICE LIST REPRICE - LINE/SUBLINE ID = ' ||
3237 l_line_rec.ID);
3238 oks_renew_pvt.debug_log
3239 (p_program_name => 'Renewal Consolidation Reprice::',
3240 p_perf_msg => 'NON PRICE LIST REPRICE - LINE/SUBLINE LSE ID = ' ||
3241 l_line_rec.lse_id);
3242 --added by mchoudha for bug#5191528
3243 --for usage the period start should always be 'SERVICE'
3244 --for effectivity based intnagible subscriptions period start should always be 'SERVICE'
3245 --for tangible subscriptions and subscription based intangible subscriptions
3246 --partial periods should be ignored as per CR1
3247 l_period_start := l_period_start_orig;
3248 l_period_type := l_period_type_orig;
3249 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
3250 IF l_line_rec.lse_id = 12 THEN
3251 l_period_start := 'SERVICE';
3252 END IF;
3253 IF l_line_rec.lse_id = 46 THEN
3254 l_tangible := OKS_SUBSCRIPTION_PUB.is_subs_tangible (l_line_rec.id);
3255 IF l_tangible THEN
3256
3257 l_period_start := NULL;
3258 l_period_type := NULL;
3259 ELSE
3260 l_pricing_method :=FND_PROFILE.value('OKS_SUBS_PRICING_METHOD');
3261 IF nvl(l_pricing_method,'SUBSCRIPTION') <> 'EFFECTIVITY' THEN
3262 l_period_start := NULL;
3263 l_period_type := NULL;
3264 ELSE
3265 l_period_start := 'SERVICE';
3266 END IF; -- l_pricing_method <> 'EFFECTIVITY'
3267 END IF; -- IF l_tangible
3268 END IF; -- l_Line_Csr_Rec.lse_id = 46
3269 END IF;
3270
3271
3272 -- Check if the line is usage and of type negotiated price
3273 IF l_line_rec.lse_id = 13 THEN
3274 -- Initialize local variable so that it'll not mess up while looping
3275 l_usage_type := NULL;
3276
3277 OPEN l_usage_type_csr (l_line_rec.cle_id);
3278
3279 FETCH l_usage_type_csr
3280 INTO l_usage_type;
3281
3282 CLOSE l_usage_type_csr;
3283
3284 -- Prorate only if it is negotaietd price
3285 IF NVL (l_usage_type, '-99') <> 'NPR' THEN
3286 oks_renew_pvt.debug_log
3287 (p_program_name => 'Renewal Consolidation Reprice::',
3288 p_perf_msg => 'Usage Line of type non negotiated - so no proration');
3289 RAISE g_skip_proration;
3290 END IF;
3291 END IF;
3292
3293 --PRORATE AND OTHER STUFFS
3294 get_oldcontractline_dates (p_cle_id => l_line_rec.ID,
3295 p_opn_code => 'REN_CON',
3296 x_old_start_date => l_old_start_date,
3297 x_old_end_date => l_old_end_date
3298 );
3299
3300 fnd_file.put_line(FND_FILE.LOG,'l_old_start_date= '||l_old_start_date);
3301 fnd_file.put_line(FND_FILE.LOG,'l_old_end_date= '||l_old_end_date);
3302
3303 oks_renew_pvt.debug_log
3304 (p_program_name => 'Renewal Consolidation Reprice::',
3305 p_perf_msg => 'LINE OLD START DATE = ' ||
3306 l_old_start_date);
3307 oks_renew_pvt.debug_log
3308 (p_program_name => 'Renewal Consolidation Reprice::',
3309 p_perf_msg => 'LINE OLD END DATE = ' ||
3310 l_old_end_date);
3311 oks_renew_pvt.debug_log
3312 (p_program_name => 'Renewal Consolidation Reprice::',
3313 p_perf_msg => 'LINE START DATE = ' ||
3314 l_line_rec.start_date);
3315 oks_renew_pvt.debug_log
3316 (p_program_name => 'Renewal Consolidation Reprice::',
3317 p_perf_msg => 'LINE END DATE = ' ||
3318 l_line_rec.end_date);
3319
3320 --begin new logic for Partial Periods
3321 --If Partial period flag set,then calculate l_proprated_price_neg based on partial period logic
3322 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
3323
3324 fnd_file.put_line(FND_FILE.LOG,'Calling do_prorating_pp');
3325 --mchoudha for bug#5488312
3326 l_prorated_price_neg :=
3327 do_prorating_pp (p_id => l_line_rec.id,
3328 p_lse_id => l_line_rec.lse_id,
3329 p_unit_price => l_line_rec.price_unit,
3330 p_new_start_date => l_line_rec.start_date,
3331 p_new_end_date => l_line_rec.end_date,
3332 p_old_start_date => l_old_start_date,
3333 p_old_end_date => l_old_end_date,
3334 p_amount => l_line_rec.price_negotiated,
3335 p_period_type => l_period_type,
3336 p_period_start => l_period_start
3337 );
3338
3339 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling do_prorating_pp l_prorated_price_neg = '||l_prorated_price_neg);
3340 ELSE
3341
3342 fnd_file.put_line(FND_FILE.LOG,'Calling do_prorating');
3343
3344 l_prorated_price_neg :=
3345 do_prorating (p_new_start_date => l_line_rec.start_date,
3346 p_new_end_date => l_line_rec.end_date,
3347 p_old_start_date => l_old_start_date,
3348 p_old_end_date => l_old_end_date,
3349 p_amount => l_line_rec.price_negotiated
3350 );
3351 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling do_prorating l_prorated_price_neg = '||l_prorated_price_neg);
3352 END IF;
3353
3354 -- end new logic for partial periods
3355 oks_renew_pvt.debug_log
3356 (p_program_name => 'Renewal Consolidation Reprice::',
3357 p_perf_msg => 'PRORATED AMOUNT = ' ||
3358 l_prorated_price_neg);
3359 oks_renew_pvt.debug_log
3360 (p_program_name => 'Renewal Consolidation Reprice::',
3361 p_perf_msg => 'MULTI CURRENCY CODE = ' ||
3362 l_currency);
3363
3364 /*Added for bug 14102072*/
3365 If nvl(FND_PROFILE.value('OKS_PRICE_ROUNDING'),'N') ='N' THEN
3366 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
3367
3368 fnd_file.put_line(FND_FILE.LOG,'Round l_prorated_price_neg = '||l_prorated_price_neg);
3369 Else
3370 l_prorated_price_neg := oks_extwar_util_pvt.round_currency_amt
3371 (p_amount => l_prorated_price_neg,
3372 p_currency_code => l_currency);
3373 End if;/*Added for bug 14102072*/
3374
3375 oks_renew_pvt.debug_log
3376 (p_program_name => 'Renewal Consolidation Reprice::',
3377 p_perf_msg => 'PRORATED AMOUNT FOR MULTI CURRENCY CURRENCY = ' ||
3378 l_prorated_price_neg);
3379
3380 IF l_srv_k_details_rec.renewal_pricing_type = 'MAN' THEN
3381 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_pricing_type = MANUAL');
3382 oks_renew_pvt.debug_log
3383 (p_program_name => 'Renewal Consolidation Reprice::',
3384 p_perf_msg => 'Pricing type is manual');
3385 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
3386 l_clev_rec.ID := l_line_rec.ID;
3387 l_clev_rec.price_negotiated := l_prorated_price_neg;
3388 -- Update line's price negotiated only, unit price remains same.
3389 update_line (p_clev_rec => l_clev_rec,
3390 x_return_status => l_return_status);
3391
3392 fnd_file.put_line(FND_FILE.LOG,'After update_line l_return_status = '||l_return_status);
3393
3394 oks_renew_pvt.debug_log
3395 (p_program_name => 'Renewal Consolidation Reprice::',
3396 p_perf_msg => 'UPDATE LINE (MAN) - RETURN = ' ||
3397 l_return_status);
3398
3399 IF l_return_status <> g_ret_sts_success THEN
3400 RAISE g_error;
3401 END IF;
3402
3403 calculate_tax (p_chr_id => l_line_rec.dnz_chr_id,
3404 p_cle_id => l_line_rec.ID,
3405 p_amount => l_prorated_price_neg,
3406 x_return_status => l_return_status
3407 );
3408 oks_renew_pvt.debug_log
3409 (p_program_name => 'Renewal Consolidation Reprice::',
3410 p_perf_msg => 'CALCUALTE TAX - RETURN = ' ||
3411 l_return_status);
3412
3413 fnd_file.put_line(FND_FILE.LOG,'After calculate_tax l_return_status = '||l_return_status);
3414
3415 IF l_return_status <> g_ret_sts_success THEN
3416 RAISE g_error;
3417 END IF;
3418
3419 IF l_line_rec.lse_id IN (7, 9, 25, 46) THEN
3420 oks_renew_pvt.debug_log
3421 (p_program_name => 'Renewal Consolidation Reprice::',
3422 p_perf_msg => 'Covered Item, Product or Subscription (man)');
3423 l_price_details.serv_ext_amount := l_prorated_price_neg;
3424 l_price_details.status_text :=
3425 'Price derived using manual pricing';
3426 update_price_info
3427 (p_chr_id => l_line_rec.dnz_chr_id,
3428 p_cle_id => l_line_rec.ID,
3429 p_lse_id => l_line_rec.lse_id,
3430 p_price_type => l_srv_k_details_rec.renewal_pricing_type,
3431 p_price_details => l_price_details,
3432 x_return_status => l_return_status
3433 );
3434 oks_renew_pvt.debug_log
3435 (p_program_name => 'Renewal Consolidation Reprice::',
3436 p_perf_msg => 'UPDATE PRICE INFO - RETURN = ' ||
3437 l_return_status);
3438
3439 fnd_file.put_line(FND_FILE.LOG,'After update_price_info l_return_status = '||l_return_status);
3440
3441 IF l_return_status <> g_ret_sts_success THEN
3442 RAISE g_error;
3443 END IF;
3444 END IF;
3445 ELSE -- Process PCT - Markup percentage
3446 fnd_file.put_line(FND_FILE.LOG,'Renewal Process PCT - Markup percentage');
3447 oks_renew_pvt.debug_log
3448 (p_program_name => 'Renewal Consolidation Reprice::',
3449 p_perf_msg => 'Pricing type is manual');
3450
3451 --begin new logic for Partial Periods
3452 --If Partial period flag set,then calculate l_prorated_unit_price based on partial period logic
3453 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
3454 fnd_file.put_line(FND_FILE.LOG,'Calling do_prorating_pp');
3455 l_prorated_unit_price :=
3456 do_prorating_pp (p_new_start_date => l_line_rec.start_date,
3457 p_new_end_date => l_line_rec.end_date,
3458 p_old_start_date => l_old_start_date,
3459 p_old_end_date => l_old_end_date,
3460 p_amount => l_line_rec.price_unit,
3461 p_period_type => l_period_type,
3462 p_period_start => l_period_start
3463 );
3464 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling do_prorating_pp l_prorated_unit_price = '||l_prorated_unit_price);
3465 ELSE
3466 fnd_file.put_line(FND_FILE.LOG,'Calling do_prorating');
3467 l_prorated_unit_price :=
3468 do_prorating (p_new_start_date => l_line_rec.start_date,
3469 p_new_end_date => l_line_rec.end_date,
3470 p_old_start_date => l_old_start_date,
3471 p_old_end_date => l_old_end_date,
3472 p_amount => l_line_rec.price_unit
3473 );
3474 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling do_prorating l_prorated_unit_price = '||l_prorated_unit_price);
3475 END IF;
3476
3477 --End new logic for partial periods
3478 oks_renew_pvt.debug_log
3479 (p_program_name => 'Renewal Consolidation Reprice::',
3480 p_perf_msg => 'PRORATED UNIT PRICE = ' ||
3481 l_prorated_unit_price);
3482 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
3483 l_clev_rec.ID := l_line_rec.ID;
3484 l_clev_rec.price_negotiated := l_prorated_price_neg;
3485 l_clev_rec.price_unit := l_prorated_unit_price;
3486 update_line (p_clev_rec => l_clev_rec,
3487 x_return_status => l_return_status);
3488 fnd_file.put_line(FND_FILE.LOG,'After update_line l_return_status = '||l_return_status);
3489 oks_renew_pvt.debug_log
3490 (p_program_name => 'Renewal Consolidation Reprice::',
3491 p_perf_msg => 'UPDATE LINE (PCT) - RETURN = ' ||
3492 l_return_status);
3493
3494 IF l_return_status <> g_ret_sts_success THEN
3495 RAISE g_error;
3496 END IF;
3497
3498 IF l_line_rec.lse_id IN (7, 9, 25, 46) THEN
3499 -- if covered item is item, product, suscription
3500 oks_renew_pvt.debug_log
3501 (p_program_name => 'Renewal Consolidation Reprice::',
3502 p_perf_msg => 'Covered Item, Product or Subscription (pct)');
3503 --Prorated Amout should markedup/down then pass to find the actual
3504 --price negotiated
3505 l_prorated_price_neg :=
3506 NVL (l_prorated_price_neg, 0) +
3507 ((NVL (l_prorated_price_neg, 0) *
3508 NVL (l_srv_k_details_rec.renewal_markup_percent, 0)
3509 ) /
3510 100
3511 );
3512 oks_renew_pvt.debug_log
3513 (p_program_name => 'Renewal Consolidation Reprice::',
3514 p_perf_msg => 'MARKED UP PRICE = ' ||
3515 l_prorated_price_neg);
3516 /*Added for bug 14102072*/
3517 If nvl(FND_PROFILE.value('OKS_PRICE_ROUNDING'),'N') ='N' THEN
3518 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
3519
3520 Else
3521 l_prorated_price_neg := oks_extwar_util_pvt.round_currency_amt
3522 (p_amount => l_prorated_price_neg,
3523 p_currency_code => l_currency);
3524 End If;
3525 /*Added for bug 14102072*/
3526 fnd_file.put_line(FND_FILE.LOG,'Round l_prorated_price_neg = '||l_prorated_price_neg);
3527 oks_renew_pvt.debug_log
3528 (p_program_name => 'Renewal Consolidation Reprice::',
3529 p_perf_msg => 'MARKED UP PRICE MULTI CURRENCY CODE = ' ||
3530 l_currency);
3531
3532 IF l_srv_k_details_rec.renewal_price_list IS NOT NULL THEN
3533 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_price_list IS NOT NULL');
3534 oks_renew_pvt.debug_log
3535 (p_program_name => 'Renewal Consolidation Reprice::',
3536 p_perf_msg => 'Renewal PL is null (pct)');
3537
3538 IF l_line_rec.lse_id = 46 THEN
3539 l_intent := 'LM';
3540 ELSE
3541 l_intent := 'SM';
3542 END IF;
3543
3544 fnd_file.put_line(FND_FILE.LOG,'Calling calculate_price');
3545 calculate_price
3546 (p_chr_id => l_line_rec.dnz_chr_id,
3547 p_line_id => l_line_rec.ID,
3548 p_linecle_id => l_line_rec.cle_id,
3549 p_lse_id => l_line_rec.lse_id,
3550 p_intent => l_intent,
3551 p_price_type => 'PCT',
3552 p_price_list => l_srv_k_details_rec.renewal_price_list,
3553 p_amount => l_prorated_price_neg,
3554 x_return_status => l_return_status
3555 );
3556
3557 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling calculate_price l_return_status = '||l_return_status);
3558
3559 oks_renew_pvt.debug_log
3560 (p_program_name => 'Renewal Consolidation Reprice::',
3561 p_perf_msg => 'CALC PRICE FOR MARKUP WITH PL - RETURN = ' ||
3562 l_return_status);
3563 oks_renew_pvt.debug_log
3564 (p_program_name => 'Renewal Consolidation Reprice::',
3565 p_perf_msg => 'CALC PRICE FOR MARKUP WITH PL - AMOUNT = ' ||
3566 l_prorated_price_neg);
3567
3568 IF l_return_status <> g_ret_sts_success THEN
3569 RAISE g_error;
3570 END IF;
3571 ELSE -- if renewal price list is null
3572 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
3573 l_clev_rec.ID := l_line_rec.ID;
3574 l_clev_rec.price_negotiated := l_prorated_price_neg;
3575 update_line (p_clev_rec => l_clev_rec,
3576 x_return_status => l_return_status);
3577 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling update_line l_return_status = '||l_return_status);
3578 oks_renew_pvt.debug_log
3579 (p_program_name => 'Renewal Consolidation Reprice::',
3580 p_perf_msg => 'UPDATE LINE (PCT) WITH NULL PL - RETURN = ' ||
3581 l_return_status);
3582
3583 IF l_return_status <> g_ret_sts_success THEN
3584 RAISE g_error;
3585 END IF;
3586
3587 l_price_details.serv_ext_amount := l_prorated_price_neg;
3588 l_price_details.status_text :=
3589 'Price derived using markup pricing';
3590 update_price_info
3591 (p_chr_id => l_line_rec.dnz_chr_id,
3592 p_cle_id => l_line_rec.ID,
3593 p_lse_id => l_line_rec.lse_id,
3594 p_price_type => l_srv_k_details_rec.renewal_pricing_type,
3595 p_price_details => l_price_details,
3596 x_return_status => l_return_status
3597 );
3598
3599 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling update_price_info l_return_status = '||l_return_status);
3600 oks_renew_pvt.debug_log
3601 (p_program_name => 'Renewal Consolidation Reprice::',
3602 p_perf_msg => 'UPDATE PRICE INFO (PCT) WITH NULL PL - RETURN = ' ||
3603 l_return_status);
3604
3605 IF l_return_status <> g_ret_sts_success THEN
3606 RAISE g_error;
3607 END IF;
3608
3609 -- Update Subsciption elements
3610 IF l_line_rec.lse_id = 46 THEN
3611 oks_renew_pvt.debug_log
3612 (p_program_name => 'Renewal Consolidation Reprice::',
3613 p_perf_msg => 'Subscription line for pct with null PL');
3614
3615 FOR l_subs_elements_rec IN
3616 l_subs_elements_csr (NVL (l_line_rec.cle_id,
3617 l_line_rec.ID)) LOOP
3618 l_scev_rec_in.ID := l_subs_elements_rec.ID;
3619 l_scev_rec_in.amount :=
3620 l_subs_elements_rec.quantity *
3621 l_prorated_unit_price;
3622 l_scev_rec_in.object_version_number :=
3623 l_subs_elements_rec.object_version_number;
3624 oks_renew_pvt.debug_log
3625 (p_program_name => 'Renewal Consolidation Reprice::',
3626 p_perf_msg => 'SUBSCR ELE ID = ' ||
3627 l_scev_rec_in.ID);
3628 oks_renew_pvt.debug_log
3629 (p_program_name => 'Renewal Consolidation Reprice::',
3630 p_perf_msg => 'SUBSCR ELE AMOUNT = ' ||
3631 l_scev_rec_in.amount);
3632 oks_subscr_elems_pub.update_row
3633 (p_api_version => l_api_version,
3634 p_init_msg_list => l_init_msg_list,
3635 x_return_status => l_return_status,
3636 x_msg_count => l_msg_count,
3637 x_msg_data => l_msg_data,
3638 p_scev_rec => l_scev_rec_in,
3639 x_scev_rec => l_scev_rec_out
3640 );
3641 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling oks_subscr_elems_pub.update_row l_return_status = '||l_return_status);
3642 oks_renew_pvt.debug_log
3643 (p_program_name => 'Renewal Consolidation Reprice::',
3644 p_perf_msg => 'UPDATE SUBSCR ELE - AMOUNT = ' ||
3645 l_return_status);
3646
3647 IF l_return_status <> g_ret_sts_success THEN
3648 RAISE g_error;
3649 END IF;
3650 END LOOP;
3651 END IF;
3652
3653 --Calculate Tax For Toplines(All Covered Levels for Service
3654 --and Extended Warranty)
3655 calculate_tax (p_chr_id => l_line_rec.dnz_chr_id,
3656 p_cle_id => l_line_rec.ID,
3657 p_amount => l_prorated_price_neg,
3658 x_return_status => l_return_status
3659 );
3660 fnd_file.put_line(FND_FILE.LOG,' 1 AFTER Calling calculate_tax l_return_status = '||l_return_status);
3661 oks_renew_pvt.debug_log
3662 (p_program_name => 'Renewal Consolidation Reprice::',
3663 p_perf_msg => 'CALC TAX (PCT) WITH NULL PL - RETURN = ' ||
3664 l_return_status);
3665 oks_renew_pvt.debug_log
3666 (p_program_name => 'Renewal Consolidation Reprice::',
3667 p_perf_msg => 'CALC TAX (PCT) WITH NULL PL - AMOUNT = ' ||
3668 l_prorated_price_neg);
3669
3670 IF l_return_status <> g_ret_sts_success THEN
3671 RAISE g_error;
3672 END IF;
3673 END IF;
3674 ELSE --if covered item is NOT item, product, suscription
3675 calculate_tax (p_chr_id => l_line_rec.dnz_chr_id,
3676 p_cle_id => l_line_rec.ID,
3677 p_amount => l_prorated_price_neg,
3678 x_return_status => l_return_status
3679 );
3680 fnd_file.put_line(FND_FILE.LOG,' 2 AFTER Calling calculate_tax l_return_status = '||l_return_status);
3681 oks_renew_pvt.debug_log
3682 (p_program_name => 'Renewal Consolidation Reprice::',
3683 p_perf_msg => 'CALC TAX (PCT) FOR PARTY - RETURN = ' ||
3684 l_return_status);
3685 oks_renew_pvt.debug_log
3686 (p_program_name => 'Renewal Consolidation Reprice::',
3687 p_perf_msg => 'CALC TAX (PCT) FOR PARTY - AMOUNT = ' ||
3688 l_prorated_price_neg);
3689
3690 IF l_return_status <> g_ret_sts_success THEN
3691 RAISE g_error;
3692 END IF; -- Status Check after Tax Call
3693 END IF; -- Lse If check within price type <> 'MAN'
3694 END IF; -- Price Type Check End IF
3695
3696
3697 -- end debug log
3698 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3699 fnd_log.STRING (fnd_log.level_procedure,
3700 g_module ||
3701 l_api_name,
3702 '1000: Leaving ' ||
3703 g_pkg_name ||
3704 '.' ||
3705 l_api_name
3706 );
3707 END IF;
3708 EXCEPTION
3709 WHEN g_skip_proration THEN
3710 -- end debug log
3711 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3712 ) THEN
3713 fnd_log.STRING (fnd_log.level_procedure,
3714 g_module ||
3715 l_api_name,
3716 '9200: Leaving ' ||
3717 g_pkg_name ||
3718 '.' ||
3719 l_api_name
3720 );
3721 END IF;
3722
3723 NULL;
3724 END;
3725
3726 EXIT WHEN l_index_sub_line_tbl = p_subject_sub_line_tbl.LAST;
3727 l_index_sub_line_tbl :=
3728 p_subject_sub_line_tbl.NEXT (l_index_sub_line_tbl);
3729 END LOOP; -- End loop for all the sub lines.
3730 END IF; -- Sub line table count check end if.
3731
3732 --FOR MAN and PCT the simple api is called and it will not update the amounts of the contract
3733 --Because of this update_amounts need to be called....
3734 update_amounts (p_chr_id => p_subject_chr_id,
3735 x_return_status => l_return_status);
3736 oks_renew_pvt.debug_log
3737 (p_program_name => 'Renewal Consolidation Reprice::',
3738 p_perf_msg => 'UPDATE CONTRACT AMOUNT = ' ||
3739 l_return_status);
3740
3741 IF l_return_status <> g_ret_sts_success THEN
3742 RAISE g_error;
3743 END IF;
3744 END IF; --End of check if the Price type is of Price List
3745
3746 x_return_status := l_return_status;
3747
3748 fnd_file.put_line(FND_FILE.LOG,' ');
3749 fnd_file.put_line(FND_FILE.LOG,'----- Leaving call_pricing_api ----------------------------- ');
3750 fnd_file.put_line(FND_FILE.LOG,'------------------------------------------------------------ ');
3751 fnd_file.put_line(FND_FILE.LOG,'------------------------------------------------------------ ');
3752 fnd_file.put_line(FND_FILE.LOG,'********************************************************** ');
3753 fnd_file.put_line(FND_FILE.LOG,' ');
3754
3755 -- end debug log
3756 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3757 fnd_log.STRING (fnd_log.level_procedure,
3758 g_module ||
3759 l_api_name,
3760 '1000: Leaving ' ||
3761 g_pkg_name ||
3762 '.' ||
3763 l_api_name
3764 );
3765 END IF;
3766 EXCEPTION
3767 WHEN g_error THEN
3768 -- end debug log
3769 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3770 fnd_log.STRING (fnd_log.level_procedure,
3771 g_module ||
3772 l_api_name,
3773 '2500: Leaving ' ||
3774 g_pkg_name ||
3775 '.' ||
3776 l_api_name
3777 );
3778 END IF;
3779
3780 DBMS_TRANSACTION.rollback_savepoint ('REPRICE_OKS');
3781 x_return_status := l_return_status;
3782 WHEN OTHERS THEN
3783 -- end debug log
3784 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3785 fnd_log.STRING (fnd_log.level_procedure,
3786 g_module ||
3787 l_api_name,
3788 '4000: Leaving ' ||
3789 g_pkg_name ||
3790 '.' ||
3791 l_api_name
3792 );
3793 END IF;
3794
3795 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3796 okc_api.set_message (p_app_name => g_app_name_oks,
3797 p_msg_name => g_unexpected_error,
3798 p_token1 => g_sqlcode_token,
3799 p_token1_value => SQLCODE,
3800 p_token2 => g_sqlerrm_token,
3801 p_token2_value => SQLERRM
3802 );
3803 x_return_status := g_ret_sts_unexp_error;
3804 END call_pricing_api;
3805 END oks_reprice_pvt;