[Home] [Help]
PACKAGE BODY: APPS.OKS_REPRICE_PVT
Source
1 PACKAGE BODY oks_reprice_pvt AS
2 /* $Header: OKSRRPRB.pls 120.31.12000000.4 2007/06/06 17:19:55 skekkar 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 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
2342 /*
2343 oks_extwar_util_pvt.round_currency_amt
2344 (p_amount => l_prorated_price_neg,
2345 p_currency_code => l_currency);
2346 */
2347 oks_renew_pvt.debug_log
2348 (p_program_name => 'Reprice',
2349 p_perf_msg => 'Prorated Negotiated Price: ' ||
2350 l_prorated_price_neg);
2351 --Update Manually priced Lines With the Prorated Amounts(Price Negotiated and Unit Price)
2352 l_clev_rec.ID := l_man_priced_line_rec.ID;
2353 l_clev_rec.price_negotiated := l_prorated_price_neg;
2354 -- Update line's price negotiated only, unit price remains same.
2355 update_line (p_clev_rec => l_clev_rec,
2356 x_return_status => l_return_status);
2357 oks_renew_pvt.debug_log
2358 (p_program_name => 'Reprice',
2359 p_perf_msg => 'Status after line updation: ' ||
2360 l_return_status);
2361
2362 IF l_return_status <> g_ret_sts_success THEN
2363 RAISE g_error;
2364 END IF;
2365
2366 calculate_tax (p_chr_id => p_reprice_rec.contract_id,
2367 p_cle_id => l_man_priced_line_rec.ID,
2368 p_amount => l_prorated_price_neg,
2369 x_return_status => l_return_status
2370 );
2371 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2372 p_perf_msg => 'Status after Tax: ' ||
2373 l_return_status);
2374
2375 IF l_return_status <> g_ret_sts_success THEN
2376 RAISE g_error;
2377 END IF;
2378 EXCEPTION
2379 WHEN g_skip_proration THEN
2380 NULL;
2381 END;
2382 END LOOP;
2383 CLOSE l_man_priced_lines_csr;
2384 -- l_man_priced_lines_csr
2385 -- end added SKEKKAR to prorate manually priced lines
2386 ELSE
2387 -- price_type in 'MAN' or 'PCT'
2388 OPEN l_currcode_csr (p_reprice_rec.contract_id);
2389
2390 FETCH l_currcode_csr
2391 INTO l_currency;
2392
2393 CLOSE l_currcode_csr;
2394
2395 OPEN l_line_csr;
2396
2397 LOOP
2398 FETCH l_line_csr
2399 INTO l_line_rec;
2400
2401 EXIT WHEN l_line_csr%NOTFOUND;
2402
2403 BEGIN
2404 --added by mchoudha for bug#5191528
2405 --for usage the period start should always be 'SERVICE'
2406 --for effectivity based intnagible subscriptions period start should always be 'SERVICE'
2407 --for tangible subscriptions and subscription based intangible subscriptions
2408 --partial periods should be ignored as per CR1
2409 l_period_start := l_period_start_orig;
2410 l_period_type := l_period_type_orig;
2411 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
2412 IF l_line_rec.lse_id = 12 THEN
2413 l_period_start := 'SERVICE';
2414 END IF;
2415 IF l_line_rec.lse_id = 46 THEN
2416 l_tangible := OKS_SUBSCRIPTION_PUB.is_subs_tangible (l_line_rec.id);
2417 IF l_tangible THEN
2418 l_period_start := NULL;
2419 l_period_type := NULL;
2420 ELSE
2421 l_pricing_method :=FND_PROFILE.value('OKS_SUBS_PRICING_METHOD');
2422 IF nvl(l_pricing_method,'SUBSCRIPTION') <> 'EFFECTIVITY' THEN
2423 l_period_start := NULL;
2424 l_period_type := NULL;
2425 ELSE
2426 l_period_start := 'SERVICE';
2427 END IF; -- l_pricing_method <> 'EFFECTIVITY'
2428 END IF; -- IF l_tangible
2429 END IF; -- l_Line_Csr_Rec.lse_id = 46
2430 END IF;
2431
2432
2433 -- Check if the line is usage and of type negotiated price
2434 IF l_line_rec.lse_id = 13 THEN
2435 -- Initialize local variable so that it'll not mess up while looping
2436 l_usage_type := NULL;
2437
2438 OPEN l_usage_type_csr (l_line_rec.cle_id);
2439
2440 FETCH l_usage_type_csr
2441 INTO l_usage_type;
2442
2443 CLOSE l_usage_type_csr;
2444
2445 -- Prorate only if it is negotaietd price
2446 IF NVL (l_usage_type, '-99') <> 'NPR' THEN
2447 RAISE g_skip_proration;
2448 END IF;
2449 END IF;
2450
2451 --PRORATE AND OTHER STUFFS
2452 get_oldcontractline_dates (p_cle_id => l_line_rec.ID,
2453 p_opn_code => 'RENEWAL',
2454 x_old_start_date => l_old_start_date,
2455 x_old_end_date => l_old_end_date
2456 );
2457 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2458 p_perf_msg => 'Old Start Date: ' ||
2459 l_old_start_date ||
2460 ' Old End Date: ' ||
2461 l_old_end_date);
2462
2463 --begin new logic for Partial Periods
2464 --If Partial period flag set,then calculate l_proprated_price_neg based on partial period logic
2465 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
2466 --mchoudha for bug#5488312
2467 l_prorated_price_neg :=
2468 do_prorating_pp (
2469 p_id => l_line_rec.id,
2470 p_lse_id => l_line_rec.lse_id,
2471 p_unit_price => l_line_rec.price_unit,
2472 p_new_start_date => l_line_rec.start_date,
2473 p_new_end_date => l_line_rec.end_date,
2474 p_old_start_date => l_old_start_date,
2475 p_old_end_date => l_old_end_date,
2476 p_amount => l_line_rec.price_negotiated,
2477 p_period_type => l_period_type,
2478 p_period_start => l_period_start
2479 );
2480 ELSE
2481 l_prorated_price_neg :=
2482 do_prorating (p_new_start_date => l_line_rec.start_date,
2483 p_new_end_date => l_line_rec.end_date,
2484 p_old_start_date => l_old_start_date,
2485 p_old_end_date => l_old_end_date,
2486 p_amount => l_line_rec.price_negotiated
2487 );
2488 END IF;
2489
2490 --end new logic for Partial Periods
2491 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
2492 /*
2493 oks_extwar_util_pvt.round_currency_amt
2494 (p_amount => l_prorated_price_neg,
2495 p_currency_code => l_currency);
2496 */
2497 oks_renew_pvt.debug_log
2498 (p_program_name => 'Reprice',
2499 p_perf_msg => 'Prorated Negotiated Price: ' ||
2500 l_prorated_price_neg);
2501
2502 IF p_reprice_rec.price_type = 'MAN' THEN
2503 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
2504 l_clev_rec.ID := l_line_rec.ID;
2505 l_clev_rec.price_negotiated := l_prorated_price_neg;
2506 -- Update line's price negotiated only, unit price remains same.
2507 update_line (p_clev_rec => l_clev_rec,
2508 x_return_status => l_return_status);
2509 oks_renew_pvt.debug_log
2510 (p_program_name => 'Reprice',
2511 p_perf_msg => 'Status after line updation: ' ||
2512 l_return_status);
2513
2514 IF l_return_status <> g_ret_sts_success THEN
2515 RAISE g_error;
2516 END IF;
2517
2518 calculate_tax (p_chr_id => p_reprice_rec.contract_id,
2519 p_cle_id => l_line_rec.ID,
2520 p_amount => l_prorated_price_neg,
2521 x_return_status => l_return_status
2522 );
2523 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2524 p_perf_msg => 'Status after Tax: ' ||
2525 l_return_status);
2526
2527 IF l_return_status <> g_ret_sts_success THEN
2528 RAISE g_error;
2529 END IF;
2530
2531 IF l_line_rec.lse_id IN (7, 9, 25, 46) THEN
2532 l_price_details.serv_ext_amount := l_prorated_price_neg;
2533 l_price_details.status_text :=
2534 'Price derived using manual pricing';
2535
2536 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
2537 ) THEN
2538 fnd_log.STRING (fnd_log.level_statement,
2539 g_module ||
2540 l_api_name,
2541 '131:l_price_details.status_text: ' ||
2542 l_price_details.status_text
2543 );
2544 fnd_log.STRING (fnd_log.level_statement,
2545 g_module ||
2546 l_api_name,
2547 '132:l_price_details.serv_ext_amount: ' ||
2548 l_price_details.serv_ext_amount
2549 );
2550 END IF;
2551
2552 --errorout_urp('l_price_details.status_text: ' || l_price_details.status_text);
2553 --errorout_urp('l_price_details.serv_ext_amount: ' || l_price_details.serv_ext_amount);
2554 update_price_info (p_chr_id => p_reprice_rec.contract_id,
2555 p_cle_id => l_line_rec.ID,
2556 p_lse_id => l_line_rec.lse_id,
2557 p_price_type => p_reprice_rec.price_type,
2558 p_price_details => l_price_details,
2559 x_return_status => l_return_status
2560 );
2561 oks_renew_pvt.debug_log
2562 (p_program_name => 'Reprice',
2563 p_perf_msg => 'Status after updating pricing info: ' ||
2564 l_return_status);
2565
2566 IF l_return_status <> g_ret_sts_success THEN
2567 RAISE g_error;
2568 END IF;
2569 END IF;
2570 ELSE -- Process PCT - Markup percentage
2571 -- bug 4886923 (forward port 4887983) skekkar
2572 -- prorate Unit price only if the subline is not item, product , subscription
2573 -- for any pricelist subline, unit price should NOT be prorated
2574 IF l_line_rec.lse_id IN(7,9,25,46) THEN
2575 -- unit price is same as the original contract
2576 l_prorated_unit_price:= l_line_rec.price_unit;
2577 ELSE
2578 --begin new logic for Partial Periods
2579 --If Partial period flag set,then calculate l_proprated_unit_price based on partial period logic
2580 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
2581 l_prorated_unit_price :=
2582 do_prorating_pp (p_new_start_date => l_line_rec.start_date,
2583 p_new_end_date => l_line_rec.end_date,
2584 p_old_start_date => l_old_start_date,
2585 p_old_end_date => l_old_end_date,
2586 p_amount => l_line_rec.price_unit,
2587 p_period_type => l_period_type,
2588 p_period_start => l_period_start
2589 );
2590 ELSE
2591 l_prorated_unit_price :=
2592 do_prorating (p_new_start_date => l_line_rec.start_date,
2593 p_new_end_date => l_line_rec.end_date,
2594 p_old_start_date => l_old_start_date,
2595 p_old_end_date => l_old_end_date,
2596 p_amount => l_line_rec.price_unit
2597 );
2598 END IF; -- partial_period_flag
2599 END IF; -- end added bug 4886923 (forward port 4887983) don't prorate unit price for pricelist items
2600
2601 --end new logic for Partial Periods
2602
2603 -- added skekkar
2604 -- PCT was NOT prorating manually priced lines with markup percentage
2605 -- IF l_line_rec.lse_id NOT IN (7, 9, 25, 46) THEN
2606 IF l_line_rec.lse_id IN (8, 10, 11, 13, 35) THEN -- bug 4769124
2607 --for non priced lines, compute Prorated Amout and update
2608 --price negotiated
2609 l_prorated_price_neg :=
2610 NVL (l_prorated_price_neg, 0) +
2611 ((NVL (l_prorated_price_neg, 0) *
2612 NVL (p_reprice_rec.markup_percent, 0)
2613 ) /
2614 100
2615 );
2616 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
2617 /*
2618 oks_extwar_util_pvt.round_currency_amt
2619 (p_amount => l_prorated_price_neg,
2620 p_currency_code => l_currency);
2621 */
2622 END IF; -- l_line_rec.lse_id IN (8, 10, 11, 13, 35)
2623
2624 -- end added skekkar
2625
2626 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
2627 l_clev_rec.ID := l_line_rec.ID;
2628 l_clev_rec.price_negotiated := l_prorated_price_neg;
2629 l_clev_rec.price_unit := l_prorated_unit_price;
2630 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2631 p_perf_msg => 'Prorated unit price: ' ||
2632 l_clev_rec.price_negotiated);
2633 update_line (p_clev_rec => l_clev_rec,
2634 x_return_status => l_return_status);
2635 oks_renew_pvt.debug_log
2636 (p_program_name => 'Reprice',
2637 p_perf_msg => 'Status after updating line: ' ||
2638 l_return_status);
2639
2640 IF l_return_status <> g_ret_sts_success THEN
2641 RAISE g_error;
2642 END IF;
2643
2644 IF l_line_rec.lse_id IN (7, 9, 25, 46) THEN
2645 --Prorated Amout should markedup/down then pass to find the actual
2646 --price negotiated
2647 l_prorated_price_neg :=
2648 NVL (l_prorated_price_neg, 0) +
2649 ((NVL (l_prorated_price_neg, 0) *
2650 NVL (p_reprice_rec.markup_percent, 0)
2651 ) /
2652 100
2653 );
2654 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
2655 /*
2656 oks_extwar_util_pvt.round_currency_amt
2657 (p_amount => l_prorated_price_neg,
2658 p_currency_code => l_currency);
2659 */
2660 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2661 p_perf_msg => 'Prorated price: ' ||
2662 l_prorated_price_neg);
2663
2664 IF p_reprice_rec.price_list_id IS NOT NULL THEN
2665 IF l_line_rec.lse_id = 46 THEN
2666 l_intent := 'LM';
2667 ELSE
2668 l_intent := 'SM';
2669 END IF;
2670
2671 calculate_price (p_chr_id => p_reprice_rec.contract_id,
2672 p_line_id => l_line_rec.ID,
2673 p_linecle_id => l_line_rec.cle_id,
2674 p_lse_id => l_line_rec.lse_id,
2675 p_intent => l_intent,
2676 p_price_type => 'PCT',
2677 p_price_list => p_reprice_rec.price_list_id,
2678 p_amount => l_prorated_price_neg,
2679 x_return_status => l_return_status
2680 );
2681 oks_renew_pvt.debug_log
2682 (p_program_name => 'Reprice',
2683 p_perf_msg => 'Status after calculate_price: ' ||
2684 l_return_status);
2685
2686 IF l_return_status <> g_ret_sts_success THEN
2687 RAISE g_error;
2688 END IF;
2689 ELSE
2690 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
2691 l_clev_rec.ID := l_line_rec.ID;
2692 l_clev_rec.price_negotiated := l_prorated_price_neg;
2693 update_line (p_clev_rec => l_clev_rec,
2694 x_return_status => l_return_status);
2695 oks_renew_pvt.debug_log
2696 (p_program_name => 'Reprice',
2697 p_perf_msg => 'Status after updating line: ' ||
2698 l_return_status);
2699
2700 IF l_return_status <> g_ret_sts_success THEN
2701 RAISE g_error;
2702 END IF;
2703
2704 l_price_details.serv_ext_amount := l_prorated_price_neg;
2705 l_price_details.status_text :=
2706 'Price derived using markup pricing';
2707
2708 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
2709 ) THEN
2710 fnd_log.STRING (fnd_log.level_statement,
2711 g_module ||
2712 l_api_name,
2713 '133:l_price_details.status_text: ' ||
2714 l_price_details.status_text
2715 );
2716 fnd_log.STRING (fnd_log.level_statement,
2717 g_module ||
2718 l_api_name,
2719 '134:l_price_details.serv_ext_amount: ' ||
2720 l_price_details.serv_ext_amount
2721 );
2722 END IF;
2723
2724 --errorout_urp('l_price_details.status_text: ' || l_price_details.status_text);
2725 --errorout_urp('l_price_details.serv_ext_amount: ' || l_price_details.serv_ext_amount);
2726 update_price_info (p_chr_id => p_reprice_rec.contract_id,
2727 p_cle_id => l_line_rec.ID,
2728 p_lse_id => l_line_rec.lse_id,
2729 p_price_type => p_reprice_rec.price_type,
2730 p_price_details => l_price_details,
2731 x_return_status => l_return_status
2732 );
2733 oks_renew_pvt.debug_log
2734 (p_program_name => 'Reprice',
2735 p_perf_msg => 'Status in CP2 API in PCT where PL is NULL: ' ||
2736 l_return_status);
2737
2738 IF l_return_status <> g_ret_sts_success THEN
2739 RAISE g_error;
2740 END IF;
2741
2742 -- Update Subsciption elements
2743 IF l_line_rec.lse_id = 46 THEN
2744 FOR l_subs_elements_rec IN
2745 l_subs_elements_csr (NVL (l_line_rec.cle_id,
2746 l_line_rec.ID)) LOOP
2747 l_scev_rec_in.ID := l_subs_elements_rec.ID;
2748 l_scev_rec_in.amount :=
2749 l_subs_elements_rec.quantity *
2750 l_prorated_unit_price;
2751 l_scev_rec_in.object_version_number :=
2752 l_subs_elements_rec.object_version_number;
2753 oks_subscr_elems_pub.update_row
2754 (p_api_version => l_api_version,
2755 p_init_msg_list => l_init_msg_list,
2756 x_return_status => l_return_status,
2757 x_msg_count => l_msg_count,
2758 x_msg_data => l_msg_data,
2759 p_scev_rec => l_scev_rec_in,
2760 x_scev_rec => l_scev_rec_out
2761 );
2762 oks_renew_pvt.debug_log
2763 (p_program_name => 'Reprice',
2764 p_perf_msg => 'Status after updating Subscription elements: ' ||
2765 l_return_status);
2766
2767 IF l_return_status <> g_ret_sts_success THEN
2768 RAISE g_error;
2769 END IF;
2770 END LOOP;
2771 END IF;
2772
2773 --Calculate Tax For Toplines(All Covered Levels for Service
2774 --and Extended Warranty)
2775 calculate_tax (p_chr_id => p_reprice_rec.contract_id,
2776 p_cle_id => l_line_rec.ID,
2777 p_amount => l_prorated_price_neg,
2778 x_return_status => l_return_status
2779 );
2780 oks_renew_pvt.debug_log
2781 (p_program_name => 'Reprice',
2782 p_perf_msg => 'Status after Tax - ' ||
2783 l_return_status);
2784
2785 IF l_return_status <> g_ret_sts_success THEN
2786 RAISE g_error;
2787 END IF;
2788 END IF;
2789 ELSE
2790 calculate_tax (p_chr_id => p_reprice_rec.contract_id,
2791 p_cle_id => l_line_rec.ID,
2792 p_amount => l_prorated_price_neg,
2793 x_return_status => l_return_status
2794 );
2795 oks_renew_pvt.debug_log (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; -- Status Check after Tax Call
2802 END IF; -- Lse If check within price type <> 'MAN'
2803 END IF; -- Price Type
2804
2805 -- end debug log
2806 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2807 fnd_log.STRING (fnd_log.level_procedure,
2808 g_module ||
2809 l_api_name,
2810 '1000: Leaving ' ||
2811 g_pkg_name ||
2812 '.' ||
2813 l_api_name
2814 );
2815 END IF;
2816 EXCEPTION
2817 WHEN g_skip_proration THEN
2818 -- end debug log
2819 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2820 fnd_log.STRING (fnd_log.level_procedure,
2821 g_module ||
2822 l_api_name,
2823 '9200: Leaving ' ||
2824 g_pkg_name ||
2825 '.' ||
2826 l_api_name
2827 );
2828 END IF;
2829
2830 NULL;
2831 END;
2832 END LOOP; -- Loop For linecursor
2833
2834 CLOSE l_line_csr;
2835
2836 --FOR MAN and PCT the simple api is called and it will not update the amounts of the contract
2837 --Because of this update_amounts need to be called....
2838 update_amounts (p_chr_id => p_reprice_rec.contract_id,
2839 x_return_status => l_return_status);
2840 oks_renew_pvt.debug_log
2841 (p_program_name => 'Reprice',
2842 p_perf_msg => 'Status after updating amounts: ' ||
2843 l_return_status);
2844
2845 IF l_return_status <> g_ret_sts_success THEN
2846 RAISE g_error;
2847 END IF;
2848 END IF; --For Price List(LST)
2849
2850 x_return_status := l_return_status;
2851 oks_renew_pvt.debug_log (p_program_name => 'Reprice',
2852 p_perf_msg => 'Final Status: ' ||
2853 l_return_status);
2854
2855 -- end debug log
2856 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2857 fnd_log.STRING (fnd_log.level_procedure,
2858 g_module ||
2859 l_api_name,
2860 '1000: Leaving ' ||
2861 g_pkg_name ||
2862 '.' ||
2863 l_api_name
2864 );
2865 END IF;
2866 EXCEPTION
2867 WHEN g_error THEN
2868 -- end debug log
2869 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2870 fnd_log.STRING (fnd_log.level_procedure,
2871 g_module ||
2872 l_api_name,
2873 '2500: Leaving ' ||
2874 g_pkg_name ||
2875 '.' ||
2876 l_api_name
2877 );
2878 END IF;
2879
2880 DBMS_TRANSACTION.rollback_savepoint ('REPRICE_OKS');
2881
2882 IF l_line_csr%ISOPEN THEN
2883 CLOSE l_line_csr;
2884 END IF;
2885
2886 x_return_status := l_return_status;
2887 WHEN OTHERS THEN
2888 -- end debug log
2889 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2890 fnd_log.STRING (fnd_log.level_procedure,
2891 g_module ||
2892 l_api_name,
2893 '4000: Leaving ' ||
2894 g_pkg_name ||
2895 '.' ||
2896 l_api_name
2897 );
2898 END IF;
2899
2900 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2901 DBMS_TRANSACTION.rollback_savepoint ('REPRICE_OKS');
2902 okc_api.set_message (p_app_name => g_app_name_oks,
2903 p_msg_name => g_unexpected_error,
2904 p_token1 => g_sqlcode_token,
2905 p_token1_value => SQLCODE,
2906 p_token2 => g_sqlerrm_token,
2907 p_token2_value => SQLERRM
2908 );
2909 x_return_status := g_ret_sts_unexp_error;
2910 END call_pricing_api;
2911
2912 -- Pricing API for Renewal consolidation
2913 PROCEDURE call_pricing_api (
2914 p_api_version IN NUMBER,
2915 p_init_msg_list IN VARCHAR2,
2916 x_return_status OUT NOCOPY VARCHAR2,
2917 x_msg_count OUT NOCOPY NUMBER,
2918 x_msg_data OUT NOCOPY VARCHAR2,
2919 p_subject_chr_id IN NUMBER,
2920 p_subject_top_line_id IN NUMBER,
2921 p_subject_sub_line_tbl IN sub_line_tbl_type
2922 ) IS
2923 l_api_name CONSTANT VARCHAR2 (30)
2924 := 'call_pricing_api';
2925
2926 CURSOR l_k_details_csr (
2927 p_chr_id NUMBER
2928 ) IS
2929 SELECT currency_code,
2930 price_list_id
2931 FROM okc_k_headers_all_b
2932 WHERE ID = p_chr_id;
2933
2934 CURSOR l_srv_k_details_csr (
2935 p_chr_id NUMBER
2936 ) IS
2937 SELECT renewal_price_list,
2938 'LST' AS renewal_pricing_type, -- Bug 6016714
2939 renewal_markup_percent,
2940 chr_id,
2941 ID
2942 FROM oks_k_headers_b
2943 WHERE chr_id = p_chr_id;
2944
2945 CURSOR l_line_csr (
2946 p_line_id NUMBER
2947 ) IS
2948 SELECT ID,
2949 start_date,
2950 end_date,
2951 lse_id,
2952 price_negotiated,
2953 price_unit,
2954 cle_id,
2955 dnz_chr_id
2956 FROM okc_k_lines_b
2957 WHERE ID = p_line_id;
2958
2959 CURSOR l_usage_type_csr (
2960 p_cle_id NUMBER
2961 ) IS
2962 SELECT usage_type
2963 FROM oks_k_lines_b
2964 WHERE cle_id = p_cle_id;
2965
2966 CURSOR l_subs_elements_csr (
2967 p_line_id NUMBER
2968 ) IS
2969 SELECT ID,
2970 osh_id,
2971 dnz_chr_id,
2972 dnz_cle_id,
2973 linked_flag,
2974 seq_no,
2975 om_interface_date,
2976 amount,
2977 start_date,
2978 end_date,
2979 quantity,
2980 uom_code,
2981 order_header_id,
2982 order_line_id,
2983 object_version_number
2984 FROM oks_subscr_elements
2985 WHERE dnz_cle_id = p_line_id;
2986 -- Added by Jvorugan
2987
2988 -- Bug 5337890
2989
2990 l_new_duration NUMBER;
2991 l_tangible BOOLEAN;
2992 l_pricing_method Varchar2(30);
2993
2994 -- End of changes by Jvorugan
2995
2996
2997 l_prorated_price_neg NUMBER;
2998 l_prorated_unit_price NUMBER;
2999 l_msg_count NUMBER;
3000 l_index_sub_line_tbl NUMBER;
3001 l_api_version NUMBER := 1.0;
3002 l_msg_data VARCHAR2 (2000);
3003 l_usage_type VARCHAR2 (10);
3004 l_currency VARCHAR2 (15);
3005 l_intent VARCHAR2 (10);
3006 l_init_msg_list VARCHAR2 (1) := okc_api.g_false;
3007 l_return_status VARCHAR2 (1) := g_ret_sts_success;
3008 l_old_start_date DATE;
3009 l_old_end_date DATE;
3010 l_clev_rec okc_contract_pub.clev_rec_type;
3011 l_chr_rec_in okc_contract_pub.chrv_rec_type;
3012 l_chr_rec_out okc_contract_pub.chrv_rec_type;
3013 l_scev_rec_in oks_subscr_elems_pub.scev_rec_type;
3014 l_scev_rec_out oks_subscr_elems_pub.scev_rec_type;
3015 l_modifier_details qp_preq_grp.line_detail_tbl_type;
3016 l_detail_rec oks_qp_pkg.input_details;
3017 l_price_break_details oks_qp_pkg.g_price_break_tbl_type;
3018 l_price_details oks_qp_pkg.price_details := NULL;
3019 l_k_details_rec l_k_details_csr%ROWTYPE;
3020 l_srv_k_details_rec l_srv_k_details_csr%ROWTYPE;
3021 l_line_rec l_line_csr%ROWTYPE;
3022 --new variables for partial periods
3023 l_period_type VARCHAR2 (30);
3024 l_period_start VARCHAR2 (30);
3025 l_price_uom VARCHAR2 (30);
3026 l_partial_period_flag VARCHAR2 (10);
3027 l_period_type_orig VARCHAR2 (30);
3028 l_period_start_orig VARCHAR2 (30);
3029 BEGIN
3030 -- start debug log
3031 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3032 fnd_log.STRING (fnd_log.level_procedure,
3033 g_module ||
3034 l_api_name,
3035 '100: Entered ' ||
3036 g_pkg_name ||
3037 '.' ||
3038 l_api_name
3039 );
3040 END IF;
3041
3042 fnd_file.put_line(FND_FILE.LOG,' ');
3043 fnd_file.put_line(FND_FILE.LOG,'********************************************************** ');
3044 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
3045 fnd_file.put_line(FND_FILE.LOG,'Inside PROCEDURE call_pricing_api for Renewal consolidation');
3046 fnd_file.put_line(FND_FILE.LOG,'p_subject_chr_id : '||p_subject_chr_id);
3047 fnd_file.put_line(FND_FILE.LOG,'p_subject_top_line_id : '||p_subject_top_line_id);
3048 fnd_file.put_line(FND_FILE.LOG,'p_subject_sub_line_tbl.COUNT : '||p_subject_sub_line_tbl.COUNT);
3049 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
3050 fnd_file.put_line(FND_FILE.LOG,' ');
3051
3052 DBMS_TRANSACTION.SAVEPOINT ('REPRICE_OKS');
3053
3054 OPEN l_k_details_csr (p_subject_chr_id);
3055 FETCH l_k_details_csr
3056 INTO l_k_details_rec;
3057 CLOSE l_k_details_csr;
3058
3059 fnd_file.put_line(FND_FILE.LOG,'l_k_details_rec.price_list_id : '||l_k_details_rec.price_list_id);
3060 fnd_file.put_line(FND_FILE.LOG,'l_k_details_rec.currency_code : '||l_k_details_rec.currency_code);
3061
3062 OPEN l_srv_k_details_csr (p_subject_chr_id);
3063 FETCH l_srv_k_details_csr
3064 INTO l_srv_k_details_rec;
3065 CLOSE l_srv_k_details_csr;
3066
3067 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_pricing_type : '||l_srv_k_details_rec.renewal_pricing_type);
3068 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_price_list : '||l_srv_k_details_rec.renewal_price_list);
3069 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_markup_percent : '||l_srv_k_details_rec.renewal_markup_percent);
3070
3071 oks_renew_pvt.debug_log
3072 (p_program_name => 'Renewal Consolidation Reprice::',
3073 p_perf_msg => 'PRICING TYPE = ' ||
3074 l_srv_k_details_rec.renewal_pricing_type);
3075 oks_renew_pvt.debug_log
3076 (p_program_name => 'Renewal Consolidation Reprice::',
3077 p_perf_msg => 'PRICE LIST = ' ||
3078 l_srv_k_details_rec.renewal_price_list);
3079 oks_renew_pvt.debug_log
3080 (p_program_name => 'Renewal Consolidation Reprice::',
3081 p_perf_msg => 'MARKUP = ' ||
3082 l_srv_k_details_rec.renewal_markup_percent);
3083
3084 IF l_srv_k_details_rec.renewal_pricing_type = 'LST' THEN
3085 l_chr_rec_in.ID := p_subject_chr_id;
3086 l_chr_rec_in.price_list_id := l_k_details_rec.price_list_id;
3087 l_currency := l_k_details_rec.currency_code;
3088 oks_renew_pvt.debug_log
3089 (p_program_name => 'Renewal Consolidation Reprice::',
3090 p_perf_msg => 'Pricing type - Price List - Header Pricing');
3091
3092 fnd_file.put_line(FND_FILE.LOG,'renewal_pricing_type = LST, Calling okc_contract_pub.update_contract_header');
3093
3094 okc_contract_pub.update_contract_header
3095 (p_api_version => l_api_version,
3096 p_init_msg_list => l_init_msg_list,
3097 x_return_status => l_return_status,
3098 x_msg_count => l_msg_count,
3099 x_msg_data => l_msg_data,
3100 p_restricted_update => 'N',
3101 p_chrv_rec => l_chr_rec_in,
3102 x_chrv_rec => l_chr_rec_out
3103 );
3104
3105 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling okc_contract_pub.update_contract_header x_return_status= '||l_return_status);
3106
3107 oks_renew_pvt.debug_log
3108 (p_program_name => 'Renewal Consolidation Reprice::',
3109 p_perf_msg => 'UPDATE CONTRACT HDR - RETURN = ' ||
3110 l_return_status);
3111
3112 IF l_return_status <> g_ret_sts_success THEN
3113 RAISE g_error;
3114 END IF;
3115
3116 l_detail_rec.chr_id := p_subject_chr_id;
3117 -- bug 6114024 , changed the intent to HP as before as the call
3118 -- to pricing api will be done once for the whole contract in OKS_RENCON_PVT
3119
3120 l_detail_rec.intent := 'HP';
3121
3122 -- Bug 6016714
3123 -- l_detail_rec.line_id := p_subject_top_line_id;
3124 -- l_detail_rec.intent := 'LP';
3125 -- end added bug 6016714
3126
3127 oks_renew_pvt.debug_log
3128 (p_program_name => 'Renewal Consolidation Reprice::',
3129 p_perf_msg => 'HDR PRICING - CONTRACT ID= ' ||
3130 l_detail_rec.chr_id);
3131
3132 fnd_file.put_line(FND_FILE.LOG,'Calling oks_qp_int_pvt.compute_price ');
3133
3134 --This api will take care about updating the amounts of the contract.
3135 oks_qp_int_pvt.compute_price
3136 (p_api_version => p_api_version,
3137 p_init_msg_list => p_init_msg_list,
3138 p_detail_rec => l_detail_rec,
3139 x_price_details => l_price_details,
3140 x_modifier_details => l_modifier_details,
3141 x_price_break_details => l_price_break_details,
3142 x_return_status => l_return_status,
3143 x_msg_count => l_msg_count,
3144 x_msg_data => l_msg_data
3145 );
3146
3147 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling oks_qp_int_pvt.compute_price x_return_status= '||l_return_status);
3148
3149 oks_renew_pvt.debug_log
3150 (p_program_name => 'Renewal Consolidation Reprice::',
3151 p_perf_msg => 'HDR REPRICE - RETURN = ' ||
3152 l_return_status);
3153
3154 IF l_return_status <> g_ret_sts_success THEN
3155 RAISE g_error;
3156 END IF;
3157 ELSE
3158 -- Renewal Price type is Manual or Markup. Each line has price to be updated individually.
3159 fnd_file.put_line(FND_FILE.LOG,'Renewal Price type is Manual or Markup');
3160
3161 oks_renew_pvt.debug_log
3162 (p_program_name => 'Renewal Consolidation Reprice::',
3163 p_perf_msg => 'NON PRICE LIST REPRICE - LINE/SUBLINE COUNT = ' ||
3164 p_subject_sub_line_tbl.COUNT);
3165 --begin new logic for Partial Periods
3166 oks_renew_util_pub.get_period_defaults
3167 (p_hdr_id => p_subject_chr_id,
3168 p_org_id => NULL,
3169 x_period_type => l_period_type_orig,
3170 x_period_start => l_period_start_orig,
3171 x_price_uom => l_price_uom,
3172 x_return_status => l_return_status
3173 );
3174
3175 fnd_file.put_line(FND_FILE.LOG,'After Calling oks_renew_util_pub.get_period_defaults ');
3176 fnd_file.put_line(FND_FILE.LOG,' x_return_status = '||l_return_status);
3177 fnd_file.put_line(FND_FILE.LOG,' x_period_type = '||l_period_type_orig);
3178 fnd_file.put_line(FND_FILE.LOG,' x_period_start = '||l_period_start_orig);
3179 fnd_file.put_line(FND_FILE.LOG,' x_price_uom = '||l_price_uom);
3180
3181 IF l_return_status <> g_ret_sts_success THEN
3182 RAISE g_error;
3183 END IF;
3184
3185 IF l_period_start_orig IS NOT NULL
3186 AND l_period_type_orig IS NOT NULL THEN
3187 l_partial_period_flag := 'TRUE';
3188 ELSE
3189 l_partial_period_flag := 'FALSE';
3190 END IF;
3191
3192 --end new logic for Partial Periods
3193 l_index_sub_line_tbl := p_subject_sub_line_tbl.FIRST;
3194
3195 IF p_subject_sub_line_tbl.COUNT > 0 THEN
3196 LOOP
3197 BEGIN
3198 OPEN l_line_csr (p_subject_sub_line_tbl (l_index_sub_line_tbl));
3199
3200 FETCH l_line_csr
3201 INTO l_line_rec;
3202
3203 CLOSE l_line_csr;
3204
3205 fnd_file.put_line(FND_FILE.LOG,' ');
3206 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
3207 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.ID : '||l_line_rec.ID);
3208 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.start_date : '||l_line_rec.start_date);
3209 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.end_date : '||l_line_rec.end_date);
3210 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.lse_id : '||l_line_rec.lse_id);
3211 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.price_negotiated : '||l_line_rec.price_negotiated);
3212 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.price_unit : '||l_line_rec.price_unit);
3213 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.cle_id : '||l_line_rec.cle_id);
3214 fnd_file.put_line(FND_FILE.LOG,'l_line_rec.dnz_chr_id : '||l_line_rec.dnz_chr_id);
3215 fnd_file.put_line(FND_FILE.LOG,'---------------------------------------------------------- ');
3216 fnd_file.put_line(FND_FILE.LOG,' ');
3217
3218 oks_renew_pvt.debug_log
3219 (p_program_name => 'Renewal Consolidation Reprice::',
3220 p_perf_msg => 'NON PRICE LIST REPRICE - LINE/SUBLINE ID = ' ||
3221 l_line_rec.ID);
3222 oks_renew_pvt.debug_log
3223 (p_program_name => 'Renewal Consolidation Reprice::',
3224 p_perf_msg => 'NON PRICE LIST REPRICE - LINE/SUBLINE LSE ID = ' ||
3225 l_line_rec.lse_id);
3226 --added by mchoudha for bug#5191528
3227 --for usage the period start should always be 'SERVICE'
3228 --for effectivity based intnagible subscriptions period start should always be 'SERVICE'
3229 --for tangible subscriptions and subscription based intangible subscriptions
3230 --partial periods should be ignored as per CR1
3231 l_period_start := l_period_start_orig;
3232 l_period_type := l_period_type_orig;
3233 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
3234 IF l_line_rec.lse_id = 12 THEN
3235 l_period_start := 'SERVICE';
3236 END IF;
3237 IF l_line_rec.lse_id = 46 THEN
3238 l_tangible := OKS_SUBSCRIPTION_PUB.is_subs_tangible (l_line_rec.id);
3239 IF l_tangible THEN
3240
3241 l_period_start := NULL;
3242 l_period_type := NULL;
3243 ELSE
3244 l_pricing_method :=FND_PROFILE.value('OKS_SUBS_PRICING_METHOD');
3245 IF nvl(l_pricing_method,'SUBSCRIPTION') <> 'EFFECTIVITY' THEN
3246 l_period_start := NULL;
3247 l_period_type := NULL;
3248 ELSE
3249 l_period_start := 'SERVICE';
3250 END IF; -- l_pricing_method <> 'EFFECTIVITY'
3251 END IF; -- IF l_tangible
3252 END IF; -- l_Line_Csr_Rec.lse_id = 46
3253 END IF;
3254
3255
3256 -- Check if the line is usage and of type negotiated price
3257 IF l_line_rec.lse_id = 13 THEN
3258 -- Initialize local variable so that it'll not mess up while looping
3259 l_usage_type := NULL;
3260
3261 OPEN l_usage_type_csr (l_line_rec.cle_id);
3262
3263 FETCH l_usage_type_csr
3264 INTO l_usage_type;
3265
3266 CLOSE l_usage_type_csr;
3267
3268 -- Prorate only if it is negotaietd price
3269 IF NVL (l_usage_type, '-99') <> 'NPR' THEN
3270 oks_renew_pvt.debug_log
3271 (p_program_name => 'Renewal Consolidation Reprice::',
3272 p_perf_msg => 'Usage Line of type non negotiated - so no proration');
3273 RAISE g_skip_proration;
3274 END IF;
3275 END IF;
3276
3277 --PRORATE AND OTHER STUFFS
3278 get_oldcontractline_dates (p_cle_id => l_line_rec.ID,
3279 p_opn_code => 'REN_CON',
3280 x_old_start_date => l_old_start_date,
3281 x_old_end_date => l_old_end_date
3282 );
3283
3284 fnd_file.put_line(FND_FILE.LOG,'l_old_start_date= '||l_old_start_date);
3285 fnd_file.put_line(FND_FILE.LOG,'l_old_end_date= '||l_old_end_date);
3286
3287 oks_renew_pvt.debug_log
3288 (p_program_name => 'Renewal Consolidation Reprice::',
3289 p_perf_msg => 'LINE OLD START DATE = ' ||
3290 l_old_start_date);
3291 oks_renew_pvt.debug_log
3292 (p_program_name => 'Renewal Consolidation Reprice::',
3293 p_perf_msg => 'LINE OLD END DATE = ' ||
3294 l_old_end_date);
3295 oks_renew_pvt.debug_log
3296 (p_program_name => 'Renewal Consolidation Reprice::',
3297 p_perf_msg => 'LINE START DATE = ' ||
3298 l_line_rec.start_date);
3299 oks_renew_pvt.debug_log
3300 (p_program_name => 'Renewal Consolidation Reprice::',
3301 p_perf_msg => 'LINE END DATE = ' ||
3302 l_line_rec.end_date);
3303
3304 --begin new logic for Partial Periods
3305 --If Partial period flag set,then calculate l_proprated_price_neg based on partial period logic
3306 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
3307
3308 fnd_file.put_line(FND_FILE.LOG,'Calling do_prorating_pp');
3309 --mchoudha for bug#5488312
3310 l_prorated_price_neg :=
3311 do_prorating_pp (p_id => l_line_rec.id,
3312 p_lse_id => l_line_rec.lse_id,
3313 p_unit_price => l_line_rec.price_unit,
3314 p_new_start_date => l_line_rec.start_date,
3315 p_new_end_date => l_line_rec.end_date,
3316 p_old_start_date => l_old_start_date,
3317 p_old_end_date => l_old_end_date,
3318 p_amount => l_line_rec.price_negotiated,
3319 p_period_type => l_period_type,
3320 p_period_start => l_period_start
3321 );
3322
3323 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling do_prorating_pp l_prorated_price_neg = '||l_prorated_price_neg);
3324 ELSE
3325
3326 fnd_file.put_line(FND_FILE.LOG,'Calling do_prorating');
3327
3328 l_prorated_price_neg :=
3329 do_prorating (p_new_start_date => l_line_rec.start_date,
3330 p_new_end_date => l_line_rec.end_date,
3331 p_old_start_date => l_old_start_date,
3332 p_old_end_date => l_old_end_date,
3333 p_amount => l_line_rec.price_negotiated
3334 );
3335 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling do_prorating l_prorated_price_neg = '||l_prorated_price_neg);
3336 END IF;
3337
3338 -- end new logic for partial periods
3339 oks_renew_pvt.debug_log
3340 (p_program_name => 'Renewal Consolidation Reprice::',
3341 p_perf_msg => 'PRORATED AMOUNT = ' ||
3342 l_prorated_price_neg);
3343 oks_renew_pvt.debug_log
3344 (p_program_name => 'Renewal Consolidation Reprice::',
3345 p_perf_msg => 'MULTI CURRENCY CODE = ' ||
3346 l_currency);
3347 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
3348
3349 fnd_file.put_line(FND_FILE.LOG,'Round l_prorated_price_neg = '||l_prorated_price_neg);
3350
3351 /*
3352 oks_extwar_util_pvt.round_currency_amt
3353 (p_amount => l_prorated_price_neg,
3354 p_currency_code => l_currency);
3355 */
3356 oks_renew_pvt.debug_log
3357 (p_program_name => 'Renewal Consolidation Reprice::',
3358 p_perf_msg => 'PRORATED AMOUNT FOR MULTI CURRENCY CURRENCY = ' ||
3359 l_prorated_price_neg);
3360
3361 IF l_srv_k_details_rec.renewal_pricing_type = 'MAN' THEN
3362 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_pricing_type = MANUAL');
3363 oks_renew_pvt.debug_log
3364 (p_program_name => 'Renewal Consolidation Reprice::',
3365 p_perf_msg => 'Pricing type is manual');
3366 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
3367 l_clev_rec.ID := l_line_rec.ID;
3368 l_clev_rec.price_negotiated := l_prorated_price_neg;
3369 -- Update line's price negotiated only, unit price remains same.
3370 update_line (p_clev_rec => l_clev_rec,
3371 x_return_status => l_return_status);
3372
3373 fnd_file.put_line(FND_FILE.LOG,'After update_line l_return_status = '||l_return_status);
3374
3375 oks_renew_pvt.debug_log
3376 (p_program_name => 'Renewal Consolidation Reprice::',
3377 p_perf_msg => 'UPDATE LINE (MAN) - RETURN = ' ||
3378 l_return_status);
3379
3380 IF l_return_status <> g_ret_sts_success THEN
3381 RAISE g_error;
3382 END IF;
3383
3384 calculate_tax (p_chr_id => l_line_rec.dnz_chr_id,
3385 p_cle_id => l_line_rec.ID,
3386 p_amount => l_prorated_price_neg,
3387 x_return_status => l_return_status
3388 );
3389 oks_renew_pvt.debug_log
3390 (p_program_name => 'Renewal Consolidation Reprice::',
3391 p_perf_msg => 'CALCUALTE TAX - RETURN = ' ||
3392 l_return_status);
3393
3394 fnd_file.put_line(FND_FILE.LOG,'After calculate_tax l_return_status = '||l_return_status);
3395
3396 IF l_return_status <> g_ret_sts_success THEN
3397 RAISE g_error;
3398 END IF;
3399
3400 IF l_line_rec.lse_id IN (7, 9, 25, 46) THEN
3401 oks_renew_pvt.debug_log
3402 (p_program_name => 'Renewal Consolidation Reprice::',
3403 p_perf_msg => 'Covered Item, Product or Subscription (man)');
3404 l_price_details.serv_ext_amount := l_prorated_price_neg;
3405 l_price_details.status_text :=
3406 'Price derived using manual pricing';
3407 update_price_info
3408 (p_chr_id => l_line_rec.dnz_chr_id,
3409 p_cle_id => l_line_rec.ID,
3410 p_lse_id => l_line_rec.lse_id,
3411 p_price_type => l_srv_k_details_rec.renewal_pricing_type,
3412 p_price_details => l_price_details,
3413 x_return_status => l_return_status
3414 );
3415 oks_renew_pvt.debug_log
3416 (p_program_name => 'Renewal Consolidation Reprice::',
3417 p_perf_msg => 'UPDATE PRICE INFO - RETURN = ' ||
3418 l_return_status);
3419
3420 fnd_file.put_line(FND_FILE.LOG,'After update_price_info l_return_status = '||l_return_status);
3421
3422 IF l_return_status <> g_ret_sts_success THEN
3423 RAISE g_error;
3424 END IF;
3425 END IF;
3426 ELSE -- Process PCT - Markup percentage
3427 fnd_file.put_line(FND_FILE.LOG,'Renewal Process PCT - Markup percentage');
3428 oks_renew_pvt.debug_log
3429 (p_program_name => 'Renewal Consolidation Reprice::',
3430 p_perf_msg => 'Pricing type is manual');
3431
3432 --begin new logic for Partial Periods
3433 --If Partial period flag set,then calculate l_prorated_unit_price based on partial period logic
3434 IF NVL (l_partial_period_flag, '!') = 'TRUE' THEN
3435 fnd_file.put_line(FND_FILE.LOG,'Calling do_prorating_pp');
3436 l_prorated_unit_price :=
3437 do_prorating_pp (p_new_start_date => l_line_rec.start_date,
3438 p_new_end_date => l_line_rec.end_date,
3439 p_old_start_date => l_old_start_date,
3440 p_old_end_date => l_old_end_date,
3441 p_amount => l_line_rec.price_unit,
3442 p_period_type => l_period_type,
3443 p_period_start => l_period_start
3444 );
3445 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling do_prorating_pp l_prorated_unit_price = '||l_prorated_unit_price);
3446 ELSE
3447 fnd_file.put_line(FND_FILE.LOG,'Calling do_prorating');
3448 l_prorated_unit_price :=
3449 do_prorating (p_new_start_date => l_line_rec.start_date,
3450 p_new_end_date => l_line_rec.end_date,
3451 p_old_start_date => l_old_start_date,
3452 p_old_end_date => l_old_end_date,
3453 p_amount => l_line_rec.price_unit
3454 );
3455 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling do_prorating l_prorated_unit_price = '||l_prorated_unit_price);
3456 END IF;
3457
3458 --End new logic for partial periods
3459 oks_renew_pvt.debug_log
3460 (p_program_name => 'Renewal Consolidation Reprice::',
3461 p_perf_msg => 'PRORATED UNIT PRICE = ' ||
3462 l_prorated_unit_price);
3463 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
3464 l_clev_rec.ID := l_line_rec.ID;
3465 l_clev_rec.price_negotiated := l_prorated_price_neg;
3466 l_clev_rec.price_unit := l_prorated_unit_price;
3467 update_line (p_clev_rec => l_clev_rec,
3468 x_return_status => l_return_status);
3469 fnd_file.put_line(FND_FILE.LOG,'After update_line l_return_status = '||l_return_status);
3470 oks_renew_pvt.debug_log
3471 (p_program_name => 'Renewal Consolidation Reprice::',
3472 p_perf_msg => 'UPDATE LINE (PCT) - RETURN = ' ||
3473 l_return_status);
3474
3475 IF l_return_status <> g_ret_sts_success THEN
3476 RAISE g_error;
3477 END IF;
3478
3479 IF l_line_rec.lse_id IN (7, 9, 25, 46) THEN
3480 -- if covered item is item, product, suscription
3481 oks_renew_pvt.debug_log
3482 (p_program_name => 'Renewal Consolidation Reprice::',
3483 p_perf_msg => 'Covered Item, Product or Subscription (pct)');
3484 --Prorated Amout should markedup/down then pass to find the actual
3485 --price negotiated
3486 l_prorated_price_neg :=
3487 NVL (l_prorated_price_neg, 0) +
3488 ((NVL (l_prorated_price_neg, 0) *
3489 NVL (l_srv_k_details_rec.renewal_markup_percent, 0)
3490 ) /
3491 100
3492 );
3493 oks_renew_pvt.debug_log
3494 (p_program_name => 'Renewal Consolidation Reprice::',
3495 p_perf_msg => 'MARKED UP PRICE = ' ||
3496 l_prorated_price_neg);
3497 l_prorated_price_neg := ROUND(l_prorated_price_neg,29); -- bug 5018782
3498
3499 fnd_file.put_line(FND_FILE.LOG,'Round l_prorated_price_neg = '||l_prorated_price_neg);
3500 /*
3501 oks_extwar_util_pvt.round_currency_amt
3502 (p_amount => l_prorated_price_neg,
3503 p_currency_code => l_currency);
3504 */
3505 oks_renew_pvt.debug_log
3506 (p_program_name => 'Renewal Consolidation Reprice::',
3507 p_perf_msg => 'MARKED UP PRICE MULTI CURRENCY CODE = ' ||
3508 l_currency);
3509
3510 IF l_srv_k_details_rec.renewal_price_list IS NOT NULL THEN
3511 fnd_file.put_line(FND_FILE.LOG,'l_srv_k_details_rec.renewal_price_list IS NOT NULL');
3512 oks_renew_pvt.debug_log
3513 (p_program_name => 'Renewal Consolidation Reprice::',
3514 p_perf_msg => 'Renewal PL is null (pct)');
3515
3516 IF l_line_rec.lse_id = 46 THEN
3517 l_intent := 'LM';
3518 ELSE
3519 l_intent := 'SM';
3520 END IF;
3521
3522 fnd_file.put_line(FND_FILE.LOG,'Calling calculate_price');
3523 calculate_price
3524 (p_chr_id => l_line_rec.dnz_chr_id,
3525 p_line_id => l_line_rec.ID,
3526 p_linecle_id => l_line_rec.cle_id,
3527 p_lse_id => l_line_rec.lse_id,
3528 p_intent => l_intent,
3529 p_price_type => 'PCT',
3530 p_price_list => l_srv_k_details_rec.renewal_price_list,
3531 p_amount => l_prorated_price_neg,
3532 x_return_status => l_return_status
3533 );
3534
3535 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling calculate_price l_return_status = '||l_return_status);
3536
3537 oks_renew_pvt.debug_log
3538 (p_program_name => 'Renewal Consolidation Reprice::',
3539 p_perf_msg => 'CALC PRICE FOR MARKUP WITH PL - RETURN = ' ||
3540 l_return_status);
3541 oks_renew_pvt.debug_log
3542 (p_program_name => 'Renewal Consolidation Reprice::',
3543 p_perf_msg => 'CALC PRICE FOR MARKUP WITH PL - AMOUNT = ' ||
3544 l_prorated_price_neg);
3545
3546 IF l_return_status <> g_ret_sts_success THEN
3547 RAISE g_error;
3548 END IF;
3549 ELSE -- if renewal price list is null
3550 --Update Line With the Prorated Amounts(Price Negotiated and Unit Price)
3551 l_clev_rec.ID := l_line_rec.ID;
3552 l_clev_rec.price_negotiated := l_prorated_price_neg;
3553 update_line (p_clev_rec => l_clev_rec,
3554 x_return_status => l_return_status);
3555 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling update_line l_return_status = '||l_return_status);
3556 oks_renew_pvt.debug_log
3557 (p_program_name => 'Renewal Consolidation Reprice::',
3558 p_perf_msg => 'UPDATE LINE (PCT) WITH NULL PL - RETURN = ' ||
3559 l_return_status);
3560
3561 IF l_return_status <> g_ret_sts_success THEN
3562 RAISE g_error;
3563 END IF;
3564
3565 l_price_details.serv_ext_amount := l_prorated_price_neg;
3566 l_price_details.status_text :=
3567 'Price derived using markup pricing';
3568 update_price_info
3569 (p_chr_id => l_line_rec.dnz_chr_id,
3570 p_cle_id => l_line_rec.ID,
3571 p_lse_id => l_line_rec.lse_id,
3572 p_price_type => l_srv_k_details_rec.renewal_pricing_type,
3573 p_price_details => l_price_details,
3574 x_return_status => l_return_status
3575 );
3576
3577 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling update_price_info l_return_status = '||l_return_status);
3578 oks_renew_pvt.debug_log
3579 (p_program_name => 'Renewal Consolidation Reprice::',
3580 p_perf_msg => 'UPDATE PRICE INFO (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 -- Update Subsciption elements
3588 IF l_line_rec.lse_id = 46 THEN
3589 oks_renew_pvt.debug_log
3590 (p_program_name => 'Renewal Consolidation Reprice::',
3591 p_perf_msg => 'Subscription line for pct with null PL');
3592
3593 FOR l_subs_elements_rec IN
3594 l_subs_elements_csr (NVL (l_line_rec.cle_id,
3595 l_line_rec.ID)) LOOP
3596 l_scev_rec_in.ID := l_subs_elements_rec.ID;
3597 l_scev_rec_in.amount :=
3598 l_subs_elements_rec.quantity *
3599 l_prorated_unit_price;
3600 l_scev_rec_in.object_version_number :=
3601 l_subs_elements_rec.object_version_number;
3602 oks_renew_pvt.debug_log
3603 (p_program_name => 'Renewal Consolidation Reprice::',
3604 p_perf_msg => 'SUBSCR ELE ID = ' ||
3605 l_scev_rec_in.ID);
3606 oks_renew_pvt.debug_log
3607 (p_program_name => 'Renewal Consolidation Reprice::',
3608 p_perf_msg => 'SUBSCR ELE AMOUNT = ' ||
3609 l_scev_rec_in.amount);
3610 oks_subscr_elems_pub.update_row
3611 (p_api_version => l_api_version,
3612 p_init_msg_list => l_init_msg_list,
3613 x_return_status => l_return_status,
3614 x_msg_count => l_msg_count,
3615 x_msg_data => l_msg_data,
3616 p_scev_rec => l_scev_rec_in,
3617 x_scev_rec => l_scev_rec_out
3618 );
3619 fnd_file.put_line(FND_FILE.LOG,'AFTER Calling oks_subscr_elems_pub.update_row l_return_status = '||l_return_status);
3620 oks_renew_pvt.debug_log
3621 (p_program_name => 'Renewal Consolidation Reprice::',
3622 p_perf_msg => 'UPDATE SUBSCR ELE - AMOUNT = ' ||
3623 l_return_status);
3624
3625 IF l_return_status <> g_ret_sts_success THEN
3626 RAISE g_error;
3627 END IF;
3628 END LOOP;
3629 END IF;
3630
3631 --Calculate Tax For Toplines(All Covered Levels for Service
3632 --and Extended Warranty)
3633 calculate_tax (p_chr_id => l_line_rec.dnz_chr_id,
3634 p_cle_id => l_line_rec.ID,
3635 p_amount => l_prorated_price_neg,
3636 x_return_status => l_return_status
3637 );
3638 fnd_file.put_line(FND_FILE.LOG,' 1 AFTER Calling calculate_tax l_return_status = '||l_return_status);
3639 oks_renew_pvt.debug_log
3640 (p_program_name => 'Renewal Consolidation Reprice::',
3641 p_perf_msg => 'CALC TAX (PCT) WITH NULL PL - RETURN = ' ||
3642 l_return_status);
3643 oks_renew_pvt.debug_log
3644 (p_program_name => 'Renewal Consolidation Reprice::',
3645 p_perf_msg => 'CALC TAX (PCT) WITH NULL PL - AMOUNT = ' ||
3646 l_prorated_price_neg);
3647
3648 IF l_return_status <> g_ret_sts_success THEN
3649 RAISE g_error;
3650 END IF;
3651 END IF;
3652 ELSE --if covered item is NOT item, product, suscription
3653 calculate_tax (p_chr_id => l_line_rec.dnz_chr_id,
3654 p_cle_id => l_line_rec.ID,
3655 p_amount => l_prorated_price_neg,
3656 x_return_status => l_return_status
3657 );
3658 fnd_file.put_line(FND_FILE.LOG,' 2 AFTER Calling calculate_tax l_return_status = '||l_return_status);
3659 oks_renew_pvt.debug_log
3660 (p_program_name => 'Renewal Consolidation Reprice::',
3661 p_perf_msg => 'CALC TAX (PCT) FOR PARTY - RETURN = ' ||
3662 l_return_status);
3663 oks_renew_pvt.debug_log
3664 (p_program_name => 'Renewal Consolidation Reprice::',
3665 p_perf_msg => 'CALC TAX (PCT) FOR PARTY - AMOUNT = ' ||
3666 l_prorated_price_neg);
3667
3668 IF l_return_status <> g_ret_sts_success THEN
3669 RAISE g_error;
3670 END IF; -- Status Check after Tax Call
3671 END IF; -- Lse If check within price type <> 'MAN'
3672 END IF; -- Price Type Check End IF
3673
3674
3675 -- end debug log
3676 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3677 fnd_log.STRING (fnd_log.level_procedure,
3678 g_module ||
3679 l_api_name,
3680 '1000: Leaving ' ||
3681 g_pkg_name ||
3682 '.' ||
3683 l_api_name
3684 );
3685 END IF;
3686 EXCEPTION
3687 WHEN g_skip_proration THEN
3688 -- end debug log
3689 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3690 ) THEN
3691 fnd_log.STRING (fnd_log.level_procedure,
3692 g_module ||
3693 l_api_name,
3694 '9200: Leaving ' ||
3695 g_pkg_name ||
3696 '.' ||
3697 l_api_name
3698 );
3699 END IF;
3700
3701 NULL;
3702 END;
3703
3704 EXIT WHEN l_index_sub_line_tbl = p_subject_sub_line_tbl.LAST;
3705 l_index_sub_line_tbl :=
3706 p_subject_sub_line_tbl.NEXT (l_index_sub_line_tbl);
3707 END LOOP; -- End loop for all the sub lines.
3708 END IF; -- Sub line table count check end if.
3709
3710 --FOR MAN and PCT the simple api is called and it will not update the amounts of the contract
3711 --Because of this update_amounts need to be called....
3712 update_amounts (p_chr_id => p_subject_chr_id,
3713 x_return_status => l_return_status);
3714 oks_renew_pvt.debug_log
3715 (p_program_name => 'Renewal Consolidation Reprice::',
3716 p_perf_msg => 'UPDATE CONTRACT AMOUNT = ' ||
3717 l_return_status);
3718
3719 IF l_return_status <> g_ret_sts_success THEN
3720 RAISE g_error;
3721 END IF;
3722 END IF; --End of check if the Price type is of Price List
3723
3724 x_return_status := l_return_status;
3725
3726 fnd_file.put_line(FND_FILE.LOG,' ');
3727 fnd_file.put_line(FND_FILE.LOG,'----- Leaving call_pricing_api ----------------------------- ');
3728 fnd_file.put_line(FND_FILE.LOG,'------------------------------------------------------------ ');
3729 fnd_file.put_line(FND_FILE.LOG,'------------------------------------------------------------ ');
3730 fnd_file.put_line(FND_FILE.LOG,'********************************************************** ');
3731 fnd_file.put_line(FND_FILE.LOG,' ');
3732
3733 -- end debug log
3734 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3735 fnd_log.STRING (fnd_log.level_procedure,
3736 g_module ||
3737 l_api_name,
3738 '1000: Leaving ' ||
3739 g_pkg_name ||
3740 '.' ||
3741 l_api_name
3742 );
3743 END IF;
3744 EXCEPTION
3745 WHEN g_error THEN
3746 -- end debug log
3747 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3748 fnd_log.STRING (fnd_log.level_procedure,
3749 g_module ||
3750 l_api_name,
3751 '2500: Leaving ' ||
3752 g_pkg_name ||
3753 '.' ||
3754 l_api_name
3755 );
3756 END IF;
3757
3758 DBMS_TRANSACTION.rollback_savepoint ('REPRICE_OKS');
3759 x_return_status := l_return_status;
3760 WHEN OTHERS THEN
3761 -- end debug log
3762 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3763 fnd_log.STRING (fnd_log.level_procedure,
3764 g_module ||
3765 l_api_name,
3766 '4000: Leaving ' ||
3767 g_pkg_name ||
3768 '.' ||
3769 l_api_name
3770 );
3771 END IF;
3772
3773 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3774 okc_api.set_message (p_app_name => g_app_name_oks,
3775 p_msg_name => g_unexpected_error,
3776 p_token1 => g_sqlcode_token,
3777 p_token1_value => SQLCODE,
3778 p_token2 => g_sqlerrm_token,
3779 p_token2_value => SQLERRM
3780 );
3781 x_return_status := g_ret_sts_unexp_error;
3782 END call_pricing_api;
3783 END oks_reprice_pvt;