[Home] [Help]
PACKAGE BODY: APPS.OKS_QP_INT_PVT
Source
1 PACKAGE BODY oks_qp_int_pvt AS
2 /* $Header: OKSQPRQB.pls 120.17 2006/09/12 18:38:52 gpriya noship $ */
3
4 ------------------------------------------------------------------------------
5 -- GLOBAL CONSTANTS
6 ------------------------------------------------------------------------------
7 g_module CONSTANT VARCHAR2 (250)
8 := 'oks.plsql.pricing.' ||
9 g_pkg_name ||
10 '.';
11 l_api_version CONSTANT NUMBER := 1.0;
12 l_init_msg_list CONSTANT VARCHAR2 (1) := 'F';
13
14 FUNCTION get_line_no (
15 p_line_id IN NUMBER
16 )
17 RETURN VARCHAR2
18 IS
19 l_api_name CONSTANT VARCHAR2 (30) := 'get_line_no';
20
21 CURSOR l_line_details_csr (
22 p_line_id NUMBER
23 )
24 IS
25 SELECT lse_id,
26 line_number
27 FROM okc_k_lines_b
28 WHERE ID = p_line_id;
29
30 CURSOR l_line_no_csr (
31 p_subline_id NUMBER
32 )
33 IS
34 SELECT b.line_number ||
35 '.' ||
36 a.line_number line_no
37 FROM okc_k_lines_b a,
38 okc_k_lines_b b
39 WHERE a.ID = p_subline_id
40 AND a.cle_id = b.ID;
41
42 l_lse_id NUMBER;
43 l_line_number VARCHAR2 (300);
44 BEGIN
45 -- start debug log
46 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
47 THEN
48 fnd_log.STRING (fnd_log.level_procedure,
49 g_module ||
50 l_api_name,
51 '100: Entered ' ||
52 g_pkg_name ||
53 '.' ||
54 l_api_name
55 );
56 END IF;
57
58 OPEN l_line_details_csr (p_line_id);
59
60 FETCH l_line_details_csr
61 INTO l_lse_id,
62 l_line_number;
63
64 IF l_line_details_csr%NOTFOUND
65 THEN
66 CLOSE l_line_details_csr;
67
68 -- end debug log
69 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
70 THEN
71 fnd_log.STRING (fnd_log.level_procedure,
72 g_module ||
73 l_api_name,
74 '1000: Leaving ' ||
75 g_pkg_name ||
76 '.' ||
77 l_api_name
78 );
79 END IF;
80
81 RETURN NULL;
82 END IF;
83
84 CLOSE l_line_details_csr;
85
86 IF l_lse_id = 46
87 THEN
88 -- end debug log
89 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
90 THEN
91 fnd_log.STRING (fnd_log.level_procedure,
92 g_module ||
93 l_api_name,
94 '1000: Leaving ' ||
95 g_pkg_name ||
96 '.' ||
97 l_api_name
98 );
99 END IF;
100
101 RETURN l_line_number;
102 ELSIF l_lse_id IN (7, 9, 25)
103 THEN
104 OPEN l_line_no_csr (p_line_id);
105
106 FETCH l_line_no_csr
107 INTO l_line_number;
108
109 IF l_line_no_csr%NOTFOUND
110 THEN
111 CLOSE l_line_no_csr;
112
113 -- end debug log
114 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
115 THEN
116 fnd_log.STRING (fnd_log.level_procedure,
117 g_module ||
118 l_api_name,
119 '1000: Leaving ' ||
120 g_pkg_name ||
121 '.' ||
122 l_api_name
123 );
124 END IF;
125
126 RETURN NULL;
127 END IF;
128
129 CLOSE l_line_no_csr;
130
131 -- end debug log
132 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
133 THEN
134 fnd_log.STRING (fnd_log.level_procedure,
135 g_module ||
136 l_api_name,
137 '1000: Leaving ' ||
138 g_pkg_name ||
139 '.' ||
140 l_api_name
141 );
142 END IF;
143
144 RETURN l_line_number;
145 ELSE
146 -- end debug log
147 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
148 THEN
149 fnd_log.STRING (fnd_log.level_procedure,
150 g_module ||
151 l_api_name,
152 '1000: Leaving ' ||
153 g_pkg_name ||
154 '.' ||
155 l_api_name
156 );
157 END IF;
158
159 RETURN NULL;
160 END IF;
161 END;
162
163 -- Function to get discount amount for sublines or subscription lines
164 FUNCTION get_subline_discount(p_subline_id IN NUMBER) RETURN NUMBER IS
165 CURSOR cs_subline_discount (cp_subline_id IN NUMBER) IS
166 SELECT kle.lse_id,
167 (NVL(cleb.toplvl_adj_price,0) - NVL(kle.line_list_price,0)) * cleb.toplvl_price_qty discount
168 FROM okc_k_lines_b kle,
169 oks_k_lines_b cleb
170 WHERE kle.ID = cp_subline_id
171 AND kle.ID = cleb.cle_id
172 AND NVL(kle.line_list_price, 0) > NVL(cleb.toplvl_adj_price,0);
173 CURSOR cs_subline_qty (cp_subline_id IN NUMBER) IS
174 SELECT number_of_items subline_qty
175 FROM okc_k_items
176 WHERE cle_id = cp_subline_id;
177 l_api_name CONSTANT VARCHAR2 (30) := 'get_subline_discount';
178 l_discount NUMBER := 0;
179 l_subline_qty NUMBER := 0;
180 l_lse_id NUMBER;
181 BEGIN
182 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
183 fnd_log.string(fnd_log.level_procedure,
184 g_module||l_api_name,
185 'Entered '||g_pkg_name||'.'||l_api_name);
186 END IF;
187
188 OPEN cs_subline_discount (p_subline_id);
189 FETCH cs_subline_discount INTO l_lse_id, l_discount;
190 CLOSE cs_subline_discount;
191 IF l_lse_id <> 46 THEN
192 OPEN cs_subline_qty (p_subline_id);
193 FETCH cs_subline_qty INTO l_subline_qty;
194 CLOSE cs_subline_qty;
195 l_discount := l_discount * l_subline_qty;
196 END IF;
197
198 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
199 fnd_log.string(fnd_log.level_procedure,
200 g_module||l_api_name,
201 'Leaving '||g_pkg_name||'.'||l_api_name);
202 END IF;
203
204 RETURN ROUND (l_discount, 29);
205 END get_subline_discount;
206
207 -- Function to get surcharge amount for sublines or subscription lines
208 FUNCTION get_subline_surcharge(p_subline_id IN NUMBER) RETURN NUMBER IS
209 CURSOR cs_subline_surcharge(cp_subline_id IN NUMBER) IS
210 SELECT kle.lse_id,
211 (NVL(cleb.toplvl_adj_price,0) - NVL(kle.line_list_price,0)) * cleb.toplvl_price_qty surcharge
212 FROM okc_k_lines_b kle,
213 oks_k_lines_b cleb
214 WHERE kle.ID = cp_subline_id
215 AND kle.ID = cleb.cle_id
216 AND NVL (kle.line_list_price, 0) < NVL (cleb.toplvl_adj_price, 0);
217 CURSOR cs_subline_qty(cp_subline_id IN NUMBER) IS
218 SELECT number_of_items subline_qty
219 FROM okc_k_items
220 WHERE cle_id = cp_subline_id;
221 l_api_name CONSTANT VARCHAR2(30):= 'get_subline_surcharge';
222 l_surcharge NUMBER := 0;
223 l_subline_qty NUMBER := 0;
224 l_lse_id NUMBER;
225 BEGIN
226 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
227 fnd_log.STRING(fnd_log.level_procedure,
228 g_module||l_api_name,
229 'Entered '||g_pkg_name||'.'||l_api_name);
230 END IF;
231
232 OPEN cs_subline_surcharge (p_subline_id);
233 FETCH cs_subline_surcharge INTO l_lse_id, l_surcharge;
234 CLOSE cs_subline_surcharge;
235 IF l_lse_id <> 46 THEN
236 OPEN cs_subline_qty (p_subline_id);
237 FETCH cs_subline_qty INTO l_subline_qty;
238 CLOSE cs_subline_qty;
239 l_surcharge := l_surcharge * l_subline_qty;
240 END IF;
241
242 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
243 fnd_log.STRING(fnd_log.level_procedure,
244 g_module||l_api_name,
245 'Leaving '||g_pkg_name ||'.' ||l_api_name);
246 END IF;
247
248 RETURN ROUND (l_surcharge, 29);
249 END;
250
251 -- function to get topline discount
252 FUNCTION get_topline_discount (
253 p_topline_id IN NUMBER
254 )
255 RETURN NUMBER
256 IS
257 l_api_name CONSTANT VARCHAR2 (30)
258 := 'GET_TOPLINE_DISCOUNT';
259
260 CURSOR cs_subline (
261 cp_cle_id NUMBER
262 )
263 IS
264 SELECT ID,
265 lse_id
266 FROM okc_k_lines_b
267 WHERE cle_id = cp_cle_id;
268
269 l_lse_id NUMBER;
270 l_topline_discount NUMBER;
271 l_topline_yn VARCHAR2 (1) := 'N';
272 BEGIN
273 -- start debug log
274 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
275 THEN
276 fnd_log.STRING (fnd_log.level_procedure,
277 g_module ||
278 l_api_name,
279 '100: Entered ' ||
280 g_pkg_name ||
281 '.' ||
282 l_api_name
283 );
284 END IF;
285
286 -- open cs_topline(p_topline_id);
287 -- fetch cs_topline into l_lse_id;
288 -- if l_lse_id = '46' -- subcription line then
289 -- then
290 -- l_topline_discount := get_subscrp_discount(p_topline_id);
291 -- else -- calculate for it's subline.
292 l_topline_discount := 0;
293
294 FOR l_subline IN cs_subline (p_topline_id)
295 LOOP
296 l_topline_yn := 'Y';
297 l_topline_discount :=
298 l_topline_discount +
299 get_subline_discount (l_subline.ID);
300 END LOOP;
301
302 IF l_topline_yn = 'N'
303 THEN
304 l_topline_discount := get_subline_discount (p_topline_id);
305 END IF;
306
307 -- end if;
308
309 -- end debug log
310 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
311 THEN
312 fnd_log.STRING (fnd_log.level_procedure,
313 g_module ||
314 l_api_name,
315 '1000: Leaving ' ||
316 g_pkg_name ||
317 '.' ||
318 l_api_name
319 );
320 END IF;
321
322 RETURN ROUND (l_topline_discount, 29);
323 END;
324
325 -- function to get topline surcharge
326 FUNCTION get_topline_surcharge (
327 p_topline_id IN NUMBER
328 )
329 RETURN NUMBER
330 IS
331 l_api_name CONSTANT VARCHAR2 (30)
332 := 'GET_TOPLINE_SURCHARGE';
333
334 CURSOR cs_subline (
335 cp_cle_id NUMBER
336 )
337 IS
338 SELECT ID,
339 lse_id
340 FROM okc_k_lines_b
341 WHERE cle_id = cp_cle_id;
342
343 l_lse_id NUMBER;
344 l_topline_surcharge NUMBER;
345 l_topline_yn VARCHAR2 (1) := 'N';
346 BEGIN
347 -- start debug log
348 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
349 THEN
350 fnd_log.STRING (fnd_log.level_procedure,
351 g_module ||
352 l_api_name,
353 '100: Entered ' ||
354 g_pkg_name ||
355 '.' ||
356 l_api_name
357 );
358 END IF;
359
360 -- open cs_topline(p_topline_id);
361 -- fetch cs_topline into l_lse_id;
362 -- if l_lse_id = '46' -- subcription line then
363 -- then
364 -- l_topline_surcharge := get_subscrp_surcharge(p_topline_id);
365 -- else -- calculate for it's subline.
366 l_topline_surcharge := 0;
367
368 FOR l_subline IN cs_subline (p_topline_id)
369 LOOP
370 l_topline_yn := 'Y';
371 l_topline_surcharge :=
372 l_topline_surcharge +
373 get_subline_surcharge (l_subline.ID);
374 END LOOP;
375
376 IF l_topline_yn = 'N'
377 THEN
378 l_topline_surcharge := get_subline_surcharge (p_topline_id);
379 END IF;
380
381 -- end if;
382 -- end debug log
383 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
384 THEN
385 fnd_log.STRING (fnd_log.level_procedure,
386 g_module ||
387 l_api_name,
388 '1000: Leaving ' ||
389 g_pkg_name ||
390 '.' ||
391 l_api_name
392 );
393 END IF;
394
395 RETURN ROUND (l_topline_surcharge, 29);
396 END;
397
398 /** 3912685 **/
399 PROCEDURE check_pricing_eligibility (
400 p_line_id IN NUMBER,
401 x_status OUT NOCOPY VARCHAR2,
402 x_return_status OUT NOCOPY VARCHAR2,
403 x_msg_count OUT NOCOPY NUMBER,
404 x_msg_data OUT NOCOPY VARCHAR2
405 )
406 IS
407 l_api_name CONSTANT VARCHAR2 (30)
408 := 'CHECK_PRICING_ELIGIBILITY';
409
410 CURSOR l_line_csr (
411 p_line_id NUMBER
412 )
413 IS
414 SELECT lse_id,
415 start_date,
416 end_date,
417 date_terminated,
418 dnz_chr_id
419 FROM okc_k_lines_b
420 WHERE ID = p_line_id;
421
422 l_line_rec l_line_csr%ROWTYPE;
423 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
424 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
425 l_k_det_rec k_details_rec;
426 -- l_rule_rec OKC_RUL_PVT.rulv_rec_type;
427 l_billed_date DATE;
428 BEGIN
429 -- start debug log
430 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
431 THEN
432 fnd_log.STRING (fnd_log.level_procedure,
433 g_module ||
434 l_api_name,
435 '100: Entered ' ||
436 g_pkg_name ||
437 '.' ||
438 l_api_name
439 );
440 END IF;
441
442 x_return_status := g_ret_sts_success;
443
444 OPEN l_line_csr (p_line_id);
445
446 FETCH l_line_csr
447 INTO l_line_rec;
448
449 IF l_line_csr%NOTFOUND
450 THEN
451 CLOSE l_line_csr;
452
453 x_return_status := g_ret_sts_error;
454 RAISE g_exc_error;
455 END IF;
456
457 CLOSE l_line_csr;
458
459 IF l_line_rec.lse_id = g_subscription
460 THEN
461 l_billed_date :=
462 oks_bill_util_pub.get_billed_upto (p_id => p_line_id,
463 p_level => 'T');
464 ELSE
465 l_billed_date :=
466 oks_bill_util_pub.get_billed_upto (p_id => p_line_id,
467 p_level => 'S');
468 END IF;
469
470 /** Bug 4194843 **/
471 x_status := g_oks_success;
472
473 IF l_billed_date IS NOT NULL
474 THEN
475 /** Bug 4194843 **/
476 IF NVL (l_line_rec.date_terminated, l_line_rec.end_date) <
477 NVL (l_billed_date, l_line_rec.start_date)
478 THEN
479 x_status := g_partial_billed;
480 get_k_details (p_id => p_line_id,
481 p_type => g_oks_line,
482 x_k_det_rec => l_k_det_rec
483 );
484 l_kln_rec_in.ID := l_k_det_rec.ID;
485 l_kln_rec_in.status_text := g_billed_line;
486 l_kln_rec_in.object_version_number :=
487 l_k_det_rec.object_version_number;
488 oks_contract_line_pub.update_line
489 (p_api_version => l_api_version,
490 p_init_msg_list => l_init_msg_list,
491 x_return_status => x_return_status,
492 x_msg_count => x_msg_count,
493 x_msg_data => x_msg_data,
494 p_klnv_rec => l_kln_rec_in,
495 x_klnv_rec => l_kln_rec_out,
496 p_validate_yn => 'N'
497 );
498
499 IF x_return_status <> g_ret_sts_success
500 THEN
501 RAISE g_exc_error;
502 END IF;
503 ELSIF trunc(NVL (l_line_rec.date_terminated, l_line_rec.end_date)) =
504 trunc(NVL (l_billed_date, l_line_rec.start_date))
505 THEN
506 x_status := g_fully_billed;
507 get_k_details (p_id => p_line_id,
508 p_type => g_oks_line,
509 x_k_det_rec => l_k_det_rec
510 );
511 l_kln_rec_in.ID := l_k_det_rec.ID;
512 l_kln_rec_in.status_text := g_billed_line;
513 l_kln_rec_in.object_version_number :=
514 l_k_det_rec.object_version_number;
515 oks_contract_line_pub.update_line
516 (p_api_version => l_api_version,
517 p_init_msg_list => l_init_msg_list,
518 x_return_status => x_return_status,
519 x_msg_count => x_msg_count,
520 x_msg_data => x_msg_data,
521 p_klnv_rec => l_kln_rec_in,
522 x_klnv_rec => l_kln_rec_out,
523 p_validate_yn => 'N'
524 );
525 ELSE
526 x_status := g_oks_success;
527 END IF;
528 /** Bug 4194843 **/
529 END IF;
530 /** Bug 4194843 **/
531 EXCEPTION
532 WHEN g_exc_error
533 THEN
534 -- end debug log
535 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
536 THEN
537 fnd_log.STRING (fnd_log.level_procedure,
538 g_module ||
539 l_api_name,
540 '2000: Leaving ' ||
541 g_pkg_name ||
542 '.' ||
543 l_api_name
544 );
545 END IF;
546
547 NULL;
548 WHEN OTHERS
549 THEN
550 -- end debug log
551 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
552 THEN
553 fnd_log.STRING (fnd_log.level_procedure,
554 g_module ||
555 l_api_name,
556 '4000: Leaving ' ||
557 g_pkg_name ||
558 '.' ||
559 l_api_name
560 );
561 END IF;
562
563 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
564 x_return_status := g_ret_sts_error;
565
566 -- end debug log
567 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
568 THEN
569 fnd_log.STRING (fnd_log.level_procedure,
570 g_module ||
571 l_api_name,
572 '1000: Leaving ' ||
573 g_pkg_name ||
574 '.' ||
575 l_api_name
576 );
577 END IF;
578 END check_pricing_eligibility;
579
580 /** **/
581 PROCEDURE get_modifier_details (
582 p_api_version IN NUMBER,
583 p_init_msg_list IN VARCHAR2,
584 p_chr_id IN NUMBER,
585 p_cle_id IN VARCHAR2,
586 x_modifiers_tbl OUT NOCOPY price_modifiers_tbl,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2
590 )
591 IS
592 l_api_name CONSTANT VARCHAR2 (30)
593 := 'GET_MODIFIER_DETAILS';
594
595 CURSOR cs_hdr_line
596 IS
597 SELECT ID
598 FROM okc_k_lines_b
599 WHERE dnz_chr_id = p_chr_id
600 AND cle_id IS NULL;
601
602 l_line_discount NUMBER;
603 l_line_surcharge NUMBER;
604 l_hdr_discount NUMBER;
605 l_hdr_surcharge NUMBER;
606 BEGIN
607 -- start debug log
608 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
609 THEN
610 fnd_log.STRING (fnd_log.level_procedure,
611 g_module ||
612 l_api_name,
613 '100: Entered ' ||
614 g_pkg_name ||
615 '.' ||
616 l_api_name
617 );
618 END IF;
619
620 x_return_status := g_ret_sts_success;
621
622 IF p_cle_id IS NOT NULL
623 THEN
624 x_modifiers_tbl (1).discount := get_topline_discount (p_cle_id);
625 x_modifiers_tbl (1).surcharge := get_topline_surcharge (p_cle_id);
626 ELSIF p_chr_id IS NOT NULL
627 THEN
628 l_hdr_discount := 0;
629 l_hdr_surcharge := 0;
630
631 FOR l_hdr_line_rec IN cs_hdr_line
632 LOOP
633 l_hdr_discount :=
634 l_hdr_discount +
635 get_topline_discount (l_hdr_line_rec.ID);
636 l_hdr_surcharge :=
637 l_hdr_surcharge +
638 get_topline_surcharge (l_hdr_line_rec.ID);
639 END LOOP;
640
641 x_modifiers_tbl (1).discount := l_hdr_discount;
642 x_modifiers_tbl (1).surcharge := l_hdr_surcharge;
643 ELSE
644 x_modifiers_tbl (1).discount := NULL;
645 x_modifiers_tbl (1).surcharge := NULL;
646 x_modifiers_tbl (1).total := NULL;
647 x_return_status := g_ret_sts_error;
648 RAISE g_exc_error;
649 END IF;
650
651 x_modifiers_tbl (1).total :=
652 x_modifiers_tbl (1).surcharge +
653 x_modifiers_tbl (1).discount;
654
655 -- end debug log
656 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
657 THEN
658 fnd_log.STRING (fnd_log.level_procedure,
659 g_module ||
660 l_api_name,
661 '1000: Leaving ' ||
662 g_pkg_name ||
663 '.' ||
664 l_api_name
665 );
666 END IF;
667 EXCEPTION
668 WHEN NO_DATA_FOUND
669 THEN
670 -- end debug log
671 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
672 THEN
673 fnd_log.STRING (fnd_log.level_procedure,
674 g_module ||
675 l_api_name,
676 '5000: Leaving ' ||
677 g_pkg_name ||
678 '.' ||
679 l_api_name
680 );
681 END IF;
682
683 x_modifiers_tbl (1).discount := 0;
684 x_modifiers_tbl (1).surcharge := 0;
685 x_modifiers_tbl (1).total := 0;
686 WHEN g_exc_error
687 THEN
688 -- end debug log
689 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
690 THEN
691 fnd_log.STRING (fnd_log.level_procedure,
692 g_module ||
693 l_api_name,
694 '2000: Leaving ' ||
695 g_pkg_name ||
696 '.' ||
697 l_api_name
698 );
699 END IF;
700
701 NULL;
702 WHEN OTHERS
703 THEN
704 -- end debug log
705 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
706 THEN
707 fnd_log.STRING (fnd_log.level_procedure,
708 g_module ||
709 l_api_name,
710 '4000: Leaving ' ||
711 g_pkg_name ||
712 '.' ||
713 l_api_name
714 );
715 END IF;
716
717 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
718 x_return_status := g_ret_sts_unexp_error;
719 okc_api.set_message (p_app_name => g_app_name,
720 p_msg_name => g_unexpected_error,
721 p_token1 => g_sqlcode_token,
722 p_token1_value => SQLCODE,
723 p_token2 => g_sqlerrm_token,
724 p_token2_value => SQLERRM
725 );
726 END get_modifier_details;
727
728 PROCEDURE get_k_details (
729 p_id IN NUMBER,
730 p_type IN VARCHAR2,
731 x_k_det_rec OUT NOCOPY k_details_rec
732 )
733 IS
734 l_api_name CONSTANT VARCHAR2 (30)
735 := 'GET_K_DETAILS';
736
737 CURSOR l_okc_chr_csr (
738 p_id NUMBER
739 )
740 IS
741 SELECT ID,
742 object_version_number
743 FROM okc_k_headers_all_b
744 WHERE ID = p_id;
745
746 CURSOR l_okc_cle_csr (
747 p_id NUMBER
748 )
749 IS
750 SELECT ID,
751 object_version_number
752 FROM okc_k_lines_b
753 WHERE ID = p_id;
754
755 CURSOR l_oks_chr_csr (
756 p_id NUMBER
757 )
758 IS
759 SELECT ID,
760 object_version_number
761 FROM oks_k_headers_b
762 WHERE chr_id = p_id;
763
764 CURSOR l_oks_cle_csr (
765 p_id NUMBER
766 )
767 IS
768 SELECT ID,
769 object_version_number
770 FROM oks_k_lines_b
771 WHERE cle_id = p_id;
772 BEGIN
773 -- start debug log
774 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
775 THEN
776 fnd_log.STRING (fnd_log.level_procedure,
777 g_module ||
778 l_api_name,
779 '100: Entered ' ||
780 g_pkg_name ||
781 '.' ||
782 l_api_name
783 );
784 END IF;
785
786 IF p_type = g_okc_hdr
787 THEN
788 OPEN l_okc_chr_csr (p_id);
789
790 FETCH l_okc_chr_csr
791 INTO x_k_det_rec;
792
793 IF l_okc_chr_csr%NOTFOUND
794 THEN
795 x_k_det_rec.ID := NULL;
796 x_k_det_rec.object_version_number := NULL;
797 END IF;
798
799 CLOSE l_okc_chr_csr;
800 ELSIF p_type = g_okc_line
801 THEN
802 OPEN l_okc_cle_csr (p_id);
803
804 FETCH l_okc_cle_csr
805 INTO x_k_det_rec;
806
807 IF l_okc_cle_csr%NOTFOUND
808 THEN
809 x_k_det_rec.ID := NULL;
810 x_k_det_rec.object_version_number := NULL;
811 END IF;
812
813 CLOSE l_okc_cle_csr;
814 ELSIF p_type = g_oks_hdr
815 THEN
816 OPEN l_oks_chr_csr (p_id);
817
818 FETCH l_oks_chr_csr
819 INTO x_k_det_rec;
820
821 IF l_oks_chr_csr%NOTFOUND
822 THEN
823 x_k_det_rec.ID := NULL;
824 x_k_det_rec.object_version_number := NULL;
825 END IF;
826
827 CLOSE l_oks_chr_csr;
828 ELSIF p_type = g_oks_line
829 THEN
830 OPEN l_oks_cle_csr (p_id);
831
832 FETCH l_oks_cle_csr
833 INTO x_k_det_rec;
834
835 IF l_oks_cle_csr%NOTFOUND
836 THEN
837 x_k_det_rec.ID := NULL;
838 x_k_det_rec.object_version_number := NULL;
839 END IF;
840
841 CLOSE l_oks_cle_csr;
842 END IF;
843
844 -- end debug log
845 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
846 THEN
847 fnd_log.STRING (fnd_log.level_procedure,
848 g_module ||
849 l_api_name,
850 '1000: Leaving ' ||
851 g_pkg_name ||
852 '.' ||
853 l_api_name
854 );
855 END IF;
856 END get_k_details;
857
858 PROCEDURE get_id (
859 p_line_id IN NUMBER,
860 x_chr_id OUT NOCOPY NUMBER,
861 x_topline_id OUT NOCOPY NUMBER,
862 x_return_status OUT NOCOPY VARCHAR2
863 )
864 IS
865 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ID';
866
867 CURSOR l_line_csr (
868 p_line_id NUMBER
869 )
870 IS
871 SELECT dnz_chr_id,
872 cle_id
873 FROM okc_k_lines_b
874 WHERE ID = p_line_id;
875 BEGIN
876 -- start debug log
877 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
878 THEN
879 fnd_log.STRING (fnd_log.level_procedure,
880 g_module ||
881 l_api_name,
882 '100: Entered ' ||
883 g_pkg_name ||
884 '.' ||
885 l_api_name
886 );
887 END IF;
888
889 x_return_status := g_ret_sts_success;
890
891 OPEN l_line_csr (p_line_id);
892
893 FETCH l_line_csr
894 INTO x_chr_id,
895 x_topline_id;
896
897 IF l_line_csr%NOTFOUND
898 THEN
899 CLOSE l_line_csr;
900
901 x_return_status := g_ret_sts_error;
902 END IF;
903
904 CLOSE l_line_csr;
905
906 -- end debug log
907 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
908 THEN
909 fnd_log.STRING (fnd_log.level_procedure,
910 g_module ||
911 l_api_name,
912 '1000: Leaving ' ||
913 g_pkg_name ||
914 '.' ||
915 l_api_name
916 );
917 END IF;
918 EXCEPTION
919 WHEN OTHERS
920 THEN
921 -- end debug log
922 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
923 THEN
924 fnd_log.STRING (fnd_log.level_procedure,
925 g_module ||
926 l_api_name,
927 '4000: Leaving ' ||
928 g_pkg_name ||
929 '.' ||
930 l_api_name
931 );
932 END IF;
933
934 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
935
936 CLOSE l_line_csr;
937
938 x_return_status := okc_api.g_ret_sts_warning;
939 END;
940
941 FUNCTION is_subs_item (
942 p_subline_id NUMBER
943 )
944 RETURN BOOLEAN
945 IS
946 l_api_name CONSTANT VARCHAR2 (30) := 'IS_SUBS_ITEM';
947
948 CURSOR l_subscr_item_csr (
949 p_subline_id NUMBER
950 )
951 IS
952 SELECT 'x'
953 FROM okc_k_lines_b kl,
954 okc_k_items ki,
955 oks_subscr_header_b sh
956 WHERE kl.ID = p_subline_id
957 AND ki.cle_id = kl.ID
958 AND sh.instance_id = ki.object1_id1;
959
960 l_dummy VARCHAR2 (1);
961 l_return BOOLEAN;
962 BEGIN
963 -- start debug log
964 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
965 THEN
966 fnd_log.STRING (fnd_log.level_procedure,
967 g_module ||
968 l_api_name,
969 '100: Entered ' ||
970 g_pkg_name ||
971 '.' ||
972 l_api_name
973 );
974 END IF;
975
976 OPEN l_subscr_item_csr (p_subline_id);
977
978 FETCH l_subscr_item_csr
979 INTO l_dummy;
980
981 IF l_subscr_item_csr%FOUND
982 THEN
983 l_return := TRUE;
984 ELSE
985 l_return := FALSE;
986 END IF;
987
988 CLOSE l_subscr_item_csr;
989
990 -- end debug log
991 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
992 THEN
993 fnd_log.STRING (fnd_log.level_procedure,
994 g_module ||
995 l_api_name,
996 '1000: Leaving ' ||
997 g_pkg_name ||
998 '.' ||
999 l_api_name
1000 );
1001 END IF;
1002
1003 RETURN (l_return);
1004 END is_subs_item;
1005
1006 PROCEDURE get_line_details (
1007 p_line_id IN NUMBER,
1008 x_name OUT NOCOPY VARCHAR2,
1009 x_description OUT NOCOPY VARCHAR2,
1010 x_return_status OUT NOCOPY VARCHAR2,
1011 x_msg_count OUT NOCOPY NUMBER,
1012 x_msg_data OUT NOCOPY VARCHAR2
1013 )
1014 IS
1015 l_api_name CONSTANT VARCHAR2 (30)
1016 := 'GET_LINE_DETAILS';
1017
1018 CURSOR l_line_csr
1019 IS
1020 SELECT cle_id,
1021 lse_id,
1022 dnz_chr_id
1023 FROM okc_k_lines_b
1024 WHERE ID = p_line_id;
1025
1026 CURSOR l_item_csr (
1027 c_cle_id NUMBER
1028 )
1029 IS
1030 SELECT object1_id1,
1031 object1_id2
1032 FROM okc_k_items
1033 WHERE cle_id = c_cle_id;
1034
1035 CURSOR l_cii_csr (
1036 c_instance_id NUMBER
1037 )
1038 IS
1039 SELECT inventory_item_id
1040 FROM csi_item_instances
1041 WHERE instance_id = c_instance_id;
1042
1043 CURSOR l_hdr_csr (
1044 c_chr_id NUMBER
1045 )
1046 IS
1047 SELECT inv_organization_id
1048 FROM okc_k_headers_all_b
1049 WHERE ID = c_chr_id;
1050
1051 CURSOR l_mtl_csr (
1052 c_item_id NUMBER,
1053 c_inv_org_id NUMBER
1054 )
1055 IS
1056 SELECT concatenated_segments,
1057 description
1058 FROM mtl_system_items_kfv
1059 WHERE inventory_item_id = c_item_id
1060 AND organization_id = c_inv_org_id;
1061
1062 l_profile_value VARCHAR2 (240)
1063 := fnd_profile.VALUE ('OKS_ITEM_DISPLAY_PREFERENCE');
1064 l_cle_id NUMBER;
1065 l_lse_id NUMBER;
1066 l_dnz_chr_id NUMBER;
1067 l_inv_org_id NUMBER;
1068 l_object1_id1 okc_k_items.object1_id1%TYPE;
1069 l_object1_id2 okc_k_items.object1_id2%TYPE;
1070 l_inventory_item_id NUMBER;
1071 l_item_cle_id NUMBER;
1072 l_name VARCHAR2 (1000);
1073 l_description VARCHAR2 (1000);
1074 BEGIN
1075 -- start debug log
1076 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1077 THEN
1078 fnd_log.STRING (fnd_log.level_procedure,
1079 g_module ||
1080 l_api_name,
1081 '100: Entered ' ||
1082 g_pkg_name ||
1083 '.' ||
1084 l_api_name
1085 );
1086 END IF;
1087
1088 x_return_status := g_ret_sts_success;
1089
1090 OPEN l_line_csr;
1091
1092 FETCH l_line_csr
1093 INTO l_cle_id,
1094 l_lse_id,
1095 l_dnz_chr_id;
1096
1097 CLOSE l_line_csr;
1098
1099 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1100 THEN
1101 fnd_log.STRING (fnd_log.level_statement,
1102 g_module ||
1103 l_api_name,
1104 '200:l_cle_id: ' ||
1105 l_cle_id
1106 );
1107 fnd_log.STRING (fnd_log.level_statement,
1108 g_module ||
1109 l_api_name,
1110 '201:l_lse_id: ' ||
1111 l_lse_id
1112 );
1113 fnd_log.STRING (fnd_log.level_statement,
1114 g_module ||
1115 l_api_name,
1116 '202:l_dnz_chr_id: ' ||
1117 l_dnz_chr_id
1118 );
1119 END IF;
1120
1121 -- errorout('l_cle_id: ' || l_cle_id);
1122 -- errorout('l_lse_id: ' || l_lse_id);
1123 -- errorout('l_dnz_chr_id: ' || l_dnz_chr_id);
1124 OPEN l_hdr_csr (l_dnz_chr_id);
1125
1126 FETCH l_hdr_csr
1127 INTO l_inv_org_id;
1128
1129 CLOSE l_hdr_csr;
1130
1131 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1132 THEN
1133 fnd_log.STRING (fnd_log.level_statement,
1134 g_module ||
1135 l_api_name,
1136 '203:l_inv_org_id: ' ||
1137 l_inv_org_id
1138 );
1139 END IF;
1140
1141 -- errorout('l_inv_org_id: ' || l_inv_org_id);
1142 OPEN l_item_csr (p_line_id);
1143
1144 FETCH l_item_csr
1145 INTO l_object1_id1,
1146 l_object1_id2;
1147
1148 CLOSE l_item_csr;
1149
1150 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1151 THEN
1152 fnd_log.STRING (fnd_log.level_statement,
1153 g_module ||
1154 l_api_name,
1155 '204:object1_id1: ' ||
1156 l_object1_id1
1157 );
1158 fnd_log.STRING (fnd_log.level_statement,
1159 g_module ||
1160 l_api_name,
1161 '205:object1_id2: ' ||
1162 l_object1_id2
1163 );
1164 END IF;
1165
1166 -- errorout('object1_id1: ' || l_object1_id1);
1167 -- errorout('object1_id2: ' || l_object1_id2);
1168 IF l_cle_id IS NULL
1169 OR l_lse_id = 7
1170 THEN
1171 OPEN l_mtl_csr (l_object1_id1, l_object1_id2);
1172
1173 FETCH l_mtl_csr
1174 INTO l_name,
1175 l_description;
1176
1177 CLOSE l_mtl_csr;
1178 ELSE
1179 OPEN l_cii_csr (l_object1_id1);
1180
1181 FETCH l_cii_csr
1182 INTO l_inventory_item_id;
1183
1184 CLOSE l_cii_csr;
1185
1186 OPEN l_mtl_csr (l_inventory_item_id, l_inv_org_id);
1187
1188 FETCH l_mtl_csr
1189 INTO l_name,
1190 l_description;
1191
1192 CLOSE l_mtl_csr;
1193 END IF;
1194
1195 IF l_cle_id IS NULL
1196 THEN
1197 x_name := l_name;
1198 x_description := l_description;
1199 ELSE
1200 IF l_profile_value = 'DISPLAY_DESC'
1201 THEN
1202 x_name := l_description;
1203 x_description := l_name;
1204 ELSIF l_profile_value = 'DISPLAY_NAME'
1205 THEN
1206 x_name := l_name;
1207 x_description := l_description;
1208 END IF;
1209 END IF;
1210
1211 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1212 THEN
1213 fnd_log.STRING (fnd_log.level_statement,
1214 g_module ||
1215 l_api_name,
1216 '206:x_name: ' ||
1217 x_name
1218 );
1219 fnd_log.STRING (fnd_log.level_statement,
1220 g_module ||
1221 l_api_name,
1222 '207:x_desc: ' ||
1223 x_description
1224 );
1225 END IF;
1226
1227 -- errorout('x_name: ' || x_name);
1228 -- errorout('x_desc: ' || x_description);
1229
1230 -- end debug log
1231 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1232 THEN
1233 fnd_log.STRING (fnd_log.level_procedure,
1234 g_module ||
1235 l_api_name,
1236 '1000: Leaving ' ||
1237 g_pkg_name ||
1238 '.' ||
1239 l_api_name
1240 );
1241 END IF;
1242 EXCEPTION
1243 WHEN OTHERS
1244 THEN
1245 -- end debug log
1246 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1247 THEN
1248 fnd_log.STRING (fnd_log.level_procedure,
1249 g_module ||
1250 l_api_name,
1251 '4000: Leaving ' ||
1252 g_pkg_name ||
1253 '.' ||
1254 l_api_name
1255 );
1256 END IF;
1257
1258 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1259 x_return_status := g_ret_sts_unexp_error;
1260 okc_api.set_message (p_app_name => g_app_name,
1261 p_msg_name => g_unexpected_error,
1262 p_token1 => g_sqlcode_token,
1263 p_token1_value => SQLCODE,
1264 p_token2 => g_sqlerrm_token,
1265 p_token2_value => SQLERRM
1266 );
1267 END get_line_details;
1268
1269 FUNCTION get_pricing_messages
1270 RETURN pricing_status_tbl
1271 IS
1272 l_api_name CONSTANT VARCHAR2 (30)
1273 := 'GET_PRICING_MESSAGES';
1274 BEGIN
1275 -- start debug log
1276 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1277 THEN
1278 fnd_log.STRING (fnd_log.level_procedure,
1279 g_module ||
1280 l_api_name,
1281 '100: Entered ' ||
1282 g_pkg_name ||
1283 '.' ||
1284 l_api_name
1285 );
1286 END IF;
1287
1288 -- end debug log
1289 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1290 THEN
1291 fnd_log.STRING (fnd_log.level_procedure,
1292 g_module ||
1293 l_api_name,
1294 '1000: Leaving ' ||
1295 g_pkg_name ||
1296 '.' ||
1297 l_api_name
1298 );
1299 END IF;
1300
1301 RETURN (g_pricing_status_tbl);
1302 END;
1303
1304 FUNCTION get_amounts (
1305 p_id IN NUMBER,
1306 p_level IN VARCHAR2
1307 )
1308 RETURN NUMBER
1309 IS
1310 l_api_name CONSTANT VARCHAR2 (30) := 'GET_AMOUNTS';
1311
1312 CURSOR l_line_csr (
1313 p_line_id NUMBER
1314 )
1315 IS
1316 SELECT SUM (price_negotiated) amount
1317 FROM okc_k_lines_b
1318 WHERE cle_id = p_line_id
1319 AND chr_id IS NULL
1320 AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
1321 AND date_cancelled IS NULL; -- bug 4870602
1322
1323 CURSOR l_hdr_csr (
1324 p_chr_id NUMBER
1325 )
1326 IS
1327 SELECT SUM (price_negotiated) amount
1328 FROM okc_k_lines_b
1329 WHERE chr_id = p_chr_id
1330 AND cle_id IS NULL
1331 AND date_cancelled IS NULL; -- bug 4870602
1332
1333 l_amount NUMBER := 0;
1334 BEGIN
1335 -- start debug log
1336 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1337 THEN
1338 fnd_log.STRING (fnd_log.level_procedure,
1339 g_module ||
1340 l_api_name,
1341 '100: Entered ' ||
1342 g_pkg_name ||
1343 '.' ||
1344 l_api_name
1345 );
1346 END IF;
1347
1348 IF p_level = g_hdr_level
1349 THEN
1350 OPEN l_hdr_csr (p_id);
1351
1352 FETCH l_hdr_csr
1353 INTO l_amount;
1354
1355 IF l_hdr_csr%NOTFOUND
1356 THEN
1357 l_amount := 0;
1358 END IF;
1359
1360 CLOSE l_hdr_csr;
1361 ELSIF p_level = g_line_level
1362 THEN
1363 OPEN l_line_csr (p_id);
1364
1365 FETCH l_line_csr
1366 INTO l_amount;
1367
1368 IF l_line_csr%NOTFOUND
1369 THEN
1370 l_amount := 0;
1371 END IF;
1372
1373 CLOSE l_line_csr;
1374 ELSE
1375 l_amount := 0;
1376 END IF;
1377
1378 -- end debug log
1379 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1380 THEN
1381 fnd_log.STRING (fnd_log.level_procedure,
1382 g_module ||
1383 l_api_name,
1384 '1000: Leaving ' ||
1385 g_pkg_name ||
1386 '.' ||
1387 l_api_name
1388 );
1389 END IF;
1390
1391 RETURN l_amount;
1392 END get_amounts;
1393
1394 PROCEDURE qualifier_party_merge (
1395 p_from_fk_id IN NUMBER,
1396 p_to_fk_id IN NUMBER,
1397 x_return_status OUT NOCOPY VARCHAR2
1398 )
1399 IS
1400 l_api_name CONSTANT VARCHAR2 (30)
1401 := 'QUALIFIER_PARTY_MERGE';
1402 l_count NUMBER;
1403 l_proc_name VARCHAR2 (240)
1404 := 'QUALIFIER_PARTY_MERGE';
1405 BEGIN
1406 -- start debug log
1407 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1408 THEN
1409 fnd_log.STRING (fnd_log.level_procedure,
1410 g_module ||
1411 l_api_name,
1412 '100: Entered ' ||
1413 g_pkg_name ||
1414 '.' ||
1415 l_api_name
1416 );
1417 END IF;
1418
1419 x_return_status := okc_api.g_ret_sts_success;
1420
1421 UPDATE oks_qualifiers
1422 SET qualifier_attr_value = TO_CHAR (p_to_fk_id),
1423 last_update_date = hz_utility_pub.last_update_date,
1424 last_updated_by = hz_utility_pub.user_id,
1425 last_update_login = hz_utility_pub.last_update_login,
1426 request_id = hz_utility_pub.request_id,
1427 program_application_id = hz_utility_pub.program_application_id,
1428 program_id = hz_utility_pub.program_id,
1429 program_update_date = SYSDATE
1430 WHERE qualifier_attr_value = TO_CHAR (p_from_fk_id);
1431
1432 l_count := SQL%ROWCOUNT;
1433 arp_message.set_name ('AR', 'AR_ROWS_UPDATED');
1434 arp_message.set_token ('NUM_ROWS', TO_CHAR (l_count));
1435
1436 -- end debug log
1437 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1438 THEN
1439 fnd_log.STRING (fnd_log.level_procedure,
1440 g_module ||
1441 l_api_name,
1442 '1000: Leaving ' ||
1443 g_pkg_name ||
1444 '.' ||
1445 l_api_name
1446 );
1447 END IF;
1448 EXCEPTION
1449 WHEN OTHERS
1450 THEN
1451 -- end debug log
1452 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1453 THEN
1454 fnd_log.STRING (fnd_log.level_procedure,
1455 g_module ||
1456 l_api_name,
1457 '4000: Leaving ' ||
1458 g_pkg_name ||
1459 '.' ||
1460 l_api_name
1461 );
1462 END IF;
1463
1464 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1465 x_return_status := fnd_api.g_ret_sts_error;
1466 arp_message.set_line (g_pkg_name ||
1467 '.' ||
1468 l_proc_name ||
1469 ': ' ||
1470 SQLERRM);
1471 END qualifier_party_merge;
1472
1473 PROCEDURE qualifier_account_merge (
1474 req_id NUMBER,
1475 set_num NUMBER
1476 )
1477 IS
1478 l_api_name CONSTANT VARCHAR2 (30)
1479 := 'QUALIFIER_ACCOUNT_MERGE';
1480
1481 TYPE merge_header_id_list_type IS TABLE OF ra_customer_merge_headers.customer_merge_header_id%TYPE
1482 INDEX BY BINARY_INTEGER;
1483
1484 merge_header_id_list merge_header_id_list_type;
1485
1486 TYPE qualifier_id_list_type IS TABLE OF oks_qualifiers.qualifier_id%TYPE
1487 INDEX BY BINARY_INTEGER;
1488
1489 primary_key_id1_list qualifier_id_list_type;
1490
1491 TYPE qualifier_attr_value_list_type IS TABLE OF oks_qualifiers.qualifier_attr_value%TYPE
1492 INDEX BY BINARY_INTEGER;
1493
1494 vchar_col1_orig_list qualifier_attr_value_list_type;
1495 vchar_col1_new_list qualifier_attr_value_list_type;
1496 l_profile_val VARCHAR2 (30);
1497
1498 CURSOR merged_records
1499 IS
1500 SELECT DISTINCT customer_merge_header_id,
1501 qualifier_id,
1502 qualifier_attr_value
1503 FROM oks_qualifiers yt,
1504 ra_customer_merges m
1505 WHERE (yt.qualifier_attr_value = m.duplicate_site_id)
1506 AND m.process_flag = 'N'
1507 AND m.request_id = req_id
1508 AND m.set_number = set_num;
1509
1510 l_last_fetch BOOLEAN := FALSE;
1511 l_count NUMBER;
1512 l_proc_name VARCHAR2 (240)
1513 := 'QUALIFIER_ACCOUNT_MERGE';
1514 BEGIN
1515 -- start debug log
1516 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1517 THEN
1518 fnd_log.STRING (fnd_log.level_procedure,
1519 g_module ||
1520 l_api_name,
1521 '100: Entered ' ||
1522 g_pkg_name ||
1523 '.' ||
1524 l_api_name
1525 );
1526 END IF;
1527
1528 arp_message.set_name ('AR', 'AR_UPDATING_TABLE');
1529 arp_message.set_token ('TABLE_NAME',
1530 'OKS_QUALIFIERS',
1531 FALSE
1532 );
1533 hz_acct_merge_util.load_set (set_num, req_id);
1534 l_profile_val := fnd_profile.VALUE ('HZ_AUDIT_ACCT_MERGE');
1535
1536 OPEN merged_records;
1537
1538 LOOP
1539 FETCH merged_records
1540 BULK COLLECT INTO merge_header_id_list,
1541 primary_key_id1_list,
1542 vchar_col1_orig_list;
1543
1544 IF merged_records%NOTFOUND
1545 THEN
1546 l_last_fetch := TRUE;
1547 END IF;
1548
1549 IF merge_header_id_list.COUNT = 0
1550 AND l_last_fetch
1551 THEN
1552 -- end debug log
1553 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1554 THEN
1555 fnd_log.STRING (fnd_log.level_procedure,
1556 g_module ||
1557 l_api_name,
1558 '1000: Leaving ' ||
1559 g_pkg_name ||
1560 '.' ||
1561 l_api_name
1562 );
1563 END IF;
1564
1565 EXIT;
1566 END IF;
1567
1568 FOR i IN 1 .. merge_header_id_list.COUNT
1569 LOOP
1570 vchar_col1_new_list (i) :=
1571 hz_acct_merge_util.getdup_site_use (vchar_col1_orig_list (i));
1572 END LOOP;
1573
1574 IF l_profile_val IS NOT NULL
1575 AND l_profile_val = 'Y'
1576 THEN
1577 FORALL i IN 1 .. merge_header_id_list.COUNT
1578 INSERT INTO hz_customer_merge_log
1579 (merge_log_id,
1580 table_name,
1581 merge_header_id,
1582 primary_key_id1,
1583 vchar_col1_orig,
1584 vchar_col1_new,
1585 action_flag,
1586 request_id,
1587 created_by,
1588 creation_date,
1589 last_update_login,
1590 last_update_date,
1591 last_updated_by
1592 )
1593 VALUES (hz_customer_merge_log_s.NEXTVAL,
1594 'OKS_QUALIFIERS',
1595 merge_header_id_list (i),
1596 primary_key_id1_list (i),
1597 vchar_col1_orig_list (i),
1598 vchar_col1_new_list (i),
1599 'U',
1600 req_id,
1601 hz_utility_pub.created_by,
1602 hz_utility_pub.creation_date,
1603 hz_utility_pub.last_update_login,
1604 hz_utility_pub.last_update_date,
1605 hz_utility_pub.last_updated_by
1606 );
1607 END IF;
1608
1609 FORALL i IN 1 .. merge_header_id_list.COUNT
1610 UPDATE oks_qualifiers yt
1611 SET qualifier_attr_value = vchar_col1_new_list (i),
1612 last_update_date = SYSDATE,
1613 last_updated_by = arp_standard.PROFILE.user_id,
1614 last_update_login = arp_standard.PROFILE.last_update_login,
1615 request_id = req_id
1616 WHERE qualifier_id = primary_key_id1_list (i);
1617 l_count := l_count +
1618 SQL%ROWCOUNT;
1619
1620 IF l_last_fetch
1621 THEN
1622 EXIT;
1623 END IF;
1624 END LOOP;
1625
1626 arp_message.set_name ('AR', 'AR_ROWS_UPDATED');
1627 arp_message.set_token ('NUM_ROWS', TO_CHAR (l_count));
1628
1629 -- end debug log
1630 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1631 THEN
1632 fnd_log.STRING (fnd_log.level_procedure,
1633 g_module ||
1634 l_api_name,
1635 '1000: Leaving ' ||
1636 g_pkg_name ||
1637 '.' ||
1638 l_api_name
1639 );
1640 END IF;
1641 EXCEPTION
1642 WHEN OTHERS
1643 THEN
1644 -- end debug log
1645 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1646 THEN
1647 fnd_log.STRING (fnd_log.level_procedure,
1648 g_module ||
1649 l_api_name,
1650 '4000: Leaving ' ||
1651 g_pkg_name ||
1652 '.' ||
1653 l_api_name
1654 );
1655 END IF;
1656
1657 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1658 arp_message.set_line (g_pkg_name ||
1659 '.' ||
1660 l_proc_name ||
1661 ': ' ||
1662 SQLERRM);
1663 RAISE;
1664 END qualifier_account_merge;
1665
1666 PROCEDURE calculate_subscription_price (
1667 p_detail_rec IN oks_qp_pkg.input_details,
1668 x_price_details OUT NOCOPY oks_qp_pkg.price_details,
1669 x_modifier_details OUT NOCOPY qp_preq_grp.line_detail_tbl_type,
1670 x_price_break_details OUT NOCOPY oks_qp_pkg.g_price_break_tbl_type,
1671 x_return_status OUT NOCOPY VARCHAR2,
1672 x_msg_count OUT NOCOPY NUMBER,
1673 x_msg_data OUT NOCOPY VARCHAR2
1674 )
1675 IS
1676 l_api_name CONSTANT VARCHAR2 (30)
1677 := 'CALCULATE_SUBSCRIPTION_PRICE';
1678
1679 CURSOR l_currcode_csr (
1680 p_hdr_id NUMBER
1681 )
1682 IS
1683 SELECT currency_code
1684 FROM okc_k_headers_all_b
1685 WHERE ID = p_hdr_id;
1686
1687 CURSOR l_line_details_csr (
1688 p_cle_id NUMBER
1689 )
1690 IS
1691 SELECT ID,
1692 start_date,
1693 end_date,
1694 lse_id,
1695 dnz_chr_id,
1696 price_negotiated,
1697 price_unit,
1698 price_unit_percent,
1699 price_list_line_id,
1700 price_list_id,
1701 line_list_price,
1702 item_to_price_yn,
1703 pricing_date,
1704 price_basis_yn,
1705 object_version_number
1706 FROM okc_k_lines_b
1707 WHERE ID = p_cle_id
1708 AND lse_id = 46;
1709
1710 CURSOR l_subs_elements_csr (
1711 p_line_id NUMBER
1712 )
1713 IS
1714 SELECT ID,
1715 osh_id,
1716 dnz_chr_id,
1717 dnz_cle_id,
1718 linked_flag,
1719 seq_no,
1720 om_interface_date,
1721 amount,
1722 start_date,
1723 end_date,
1724 quantity,
1725 uom_code,
1726 order_header_id,
1727 order_line_id,
1728 object_version_number
1729 FROM oks_subscr_elements
1730 WHERE dnz_cle_id = p_line_id;
1731
1732 l_line_details_rec l_line_details_csr%ROWTYPE;
1733 l_input_detail_rec oks_qp_pkg.input_details
1734 := p_detail_rec;
1735 l_rail_rec oks_tax_util_pvt.ra_rec_type;
1736 l_k_det_rec k_details_rec;
1737 l_khdr_rec_in oks_contract_hdr_pub.khrv_rec_type;
1738 l_khdr_rec_out oks_contract_hdr_pub.khrv_rec_type;
1739 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
1740 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
1741 l_clev_rec_in okc_contract_pub.clev_rec_type;
1742 l_clev_rec_out okc_contract_pub.clev_rec_type;
1743 l_scev_rec_in oks_subscr_elems_pub.scev_rec_type;
1744 l_scev_rec_out oks_subscr_elems_pub.scev_rec_type;
1745 l_qpprod_quantity NUMBER;
1746 l_qpprod_uom_code VARCHAR2 (240);
1747 l_tlvl_name VARCHAR2 (800);
1748 l_tlvl_desc VARCHAR2 (1000);
1749 l_return_status VARCHAR2 (1);
1750 l_line_no VARCHAR2 (300);
1751 l_status VARCHAR2 (30); --3912685
1752 BEGIN
1753 -- start debug log
1754 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1755 THEN
1756 fnd_log.STRING (fnd_log.level_procedure,
1757 g_module ||
1758 l_api_name,
1759 '100: Entered ' ||
1760 g_pkg_name ||
1761 '.' ||
1762 l_api_name
1763 );
1764 END IF;
1765
1766 x_return_status := g_ret_sts_success;
1767
1768 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1769 THEN
1770 fnd_log.STRING (fnd_log.level_statement,
1771 g_module ||
1772 l_api_name,
1773 '300:********** Entered Subscription Price ********'
1774 );
1775 fnd_log.STRING (fnd_log.level_statement,
1776 g_module ||
1777 l_api_name,
1778 '301:CHR_ID = ' ||
1779 p_detail_rec.chr_id
1780 );
1781 fnd_log.STRING (fnd_log.level_statement,
1782 g_module ||
1783 l_api_name,
1784 '302:LINE_ID = ' ||
1785 p_detail_rec.line_id
1786 );
1787 fnd_log.STRING (fnd_log.level_statement,
1788 g_module ||
1789 l_api_name,
1790 '303:SUBLINE_ID = ' ||
1791 p_detail_rec.subline_id
1792 );
1793 fnd_log.STRING (fnd_log.level_statement,
1794 g_module ||
1795 l_api_name,
1796 '304:INTENT = ' ||
1797 p_detail_rec.intent
1798 );
1799 fnd_log.STRING (fnd_log.level_statement,
1800 g_module ||
1801 l_api_name,
1802 '305:CURRENCY = ' ||
1803 p_detail_rec.currency
1804 );
1805 fnd_log.STRING (fnd_log.level_statement,
1806 g_module ||
1807 l_api_name,
1808 '306:Usage Qty = ' ||
1809 p_detail_rec.usage_qty
1810 );
1811 fnd_log.STRING (fnd_log.level_statement,
1812 g_module ||
1813 l_api_name,
1814 '307:Usage UOM Code = ' ||
1815 p_detail_rec.usage_uom_code
1816 );
1817 END IF;
1818
1819 --errorout ('********** Entered Subscription Price ********');
1820 --errorout ('CHR_ID = ' || p_detail_rec.chr_id);
1821 --errorout ('LINE_ID = ' || p_detail_rec.line_id);
1822 --errorout ('SUBLINE_ID = ' || p_detail_rec.subline_id);
1823 --errorout ('INTENT = ' || p_detail_rec.intent);
1824 --errorout ('CURRENCY = ' || p_detail_rec.currency);
1825 --errorout ('Usage Qty = ' || p_detail_rec.usage_qty);
1826 --errorout ('Usage UOM Code = ' || p_detail_rec.usage_uom_code);
1827
1828 /** 3912685 **/
1829 check_pricing_eligibility (p_line_id => p_detail_rec.line_id,
1830 x_status => l_status,
1831 x_return_status => x_return_status,
1832 x_msg_count => x_msg_count,
1833 x_msg_data => x_msg_data
1834 );
1835
1836 IF x_return_status <> g_ret_sts_success
1837 THEN
1838 RAISE g_exc_error;
1839 END IF;
1840
1841 IF l_status <> g_oks_success
1842 THEN
1843 x_price_details.status_code := l_status;
1844 RAISE g_exc_cant_price;
1845 END IF;
1846
1847 /** **/
1848 IF p_detail_rec.intent = g_subsc_ovr_pricing
1849 THEN
1850 -- Get Price negotiated amount
1851 OPEN l_line_details_csr (p_detail_rec.line_id);
1852
1853 FETCH l_line_details_csr
1854 INTO l_line_details_rec;
1855
1856 IF l_line_details_csr%NOTFOUND
1857 THEN
1858 CLOSE l_line_details_csr;
1859
1860 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1861 THEN
1862 fnd_log.STRING
1863 (fnd_log.level_statement,
1864 g_module ||
1865 l_api_name,
1866 '308:Negotiated amount ERROR at Calc Subscription call'
1867 );
1868 END IF;
1869
1870 --errorout('Negotiated amount ERROR at Calc Subscription call');
1871 RAISE g_exc_error;
1872 END IF;
1873
1874 CLOSE l_line_details_csr;
1875
1876 oks_subscription_pub.get_subs_qty
1877 (p_cle_id => p_detail_rec.line_id,
1878 x_return_status => x_return_status,
1879 x_quantity => l_qpprod_quantity,
1880 x_uom_code => l_qpprod_uom_code
1881 );
1882
1883 IF x_return_status <> g_ret_sts_success
1884 THEN
1885 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1886 THEN
1887 fnd_log.STRING (fnd_log.level_statement,
1888 g_module ||
1889 l_api_name,
1890 '309:UOM/QTY ERROR at Calc Subscription call'
1891 );
1892 END IF;
1893
1894 --errorout('UOM/QTY ERROR at Calc Subscription call');
1895 RAISE g_exc_error;
1896 END IF;
1897
1898 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1899 THEN
1900 fnd_log.STRING (fnd_log.level_statement,
1901 g_module ||
1902 l_api_name,
1903 '310:QTY = ' ||
1904 l_qpprod_quantity ||
1905 ' UOM = ' ||
1906 l_qpprod_uom_code
1907 );
1908 END IF;
1909
1910 --errorout('QTY = '||l_qpprod_quantity || ' UOM = ' || l_qpprod_uom_code);
1911 IF NVL (fnd_profile.VALUE ('OKS_USE_QP_FOR_MANUAL_ADJ'), 'NO') =
1912 'YES'
1913 THEN
1914 IF l_qpprod_quantity IS NULL
1915 OR l_qpprod_quantity = 0
1916 THEN
1917 l_input_detail_rec.asking_unit_price :=
1918 l_line_details_rec.price_negotiated;
1919 ELSE
1920 l_input_detail_rec.asking_unit_price :=
1921 l_line_details_rec.price_negotiated /
1922 l_qpprod_quantity;
1923 END IF;
1924
1925 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1926 THEN
1927 fnd_log.STRING (fnd_log.level_statement,
1928 g_module ||
1929 l_api_name,
1930 '311:Asking price = ' ||
1931 l_input_detail_rec.asking_unit_price
1932 );
1933 END IF;
1934
1935 --errorout('Asking price = '||l_input_detail_rec.asking_unit_price);
1936 oks_qp_pkg.calc_price
1937 (p_detail_rec => l_input_detail_rec,
1938 x_price_details => x_price_details,
1939 x_modifier_details => x_modifier_details,
1940 x_price_break_details => x_price_break_details,
1941 x_return_status => x_return_status,
1942 x_msg_count => x_msg_count,
1943 x_msg_data => x_msg_data
1944 );
1945
1946 IF x_return_status <> g_ret_sts_success
1947 THEN
1948 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
1949 )
1950 THEN
1951 fnd_log.STRING (fnd_log.level_procedure,
1952 g_module ||
1953 l_api_name,
1954 '101:CALC PRICE ERROR at 2nd SAPI call'
1955 );
1956 END IF;
1957
1958 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
1959 )
1960 THEN
1961 fnd_log.STRING (fnd_log.level_statement,
1962 g_module ||
1963 l_api_name,
1964 '312:CALC PRICE ERROR at 2nd SAPI call'
1965 );
1966 END IF;
1967
1968 --dbms_output.put_line('CALC PRICE ERROR at 2nd SAPI call');
1969 RAISE g_exc_error;
1970 END IF;
1971
1972 x_price_details.serv_ext_amount := x_price_details.prod_ext_amount;
1973 l_rail_rec.amount :=
1974 NVL (x_price_details.serv_ext_amount, 0);
1975 -- x_price_details.status_text := NVL(x_price_details.status_text,G_STS_TXT_SUCCESS);
1976 ELSE
1977 IF l_input_detail_rec.currency IS NULL
1978 THEN
1979 OPEN l_currcode_csr (l_line_details_rec.dnz_chr_id);
1980
1981 FETCH l_currcode_csr
1982 INTO l_input_detail_rec.currency;
1983
1984 CLOSE l_currcode_csr;
1985 END IF;
1986
1987 -- bug 5018782, don't round any amount that we get from QP
1988 l_line_details_rec.price_negotiated := ROUND (l_line_details_rec.price_negotiated, 29);
1989 /* bug 5018782
1990 oks_extwar_util_pvt.round_currency_amt
1991 (p_amount => l_line_details_rec.price_negotiated,
1992 p_currency_code => l_input_detail_rec.currency);
1993 */
1994
1995 x_price_details.serv_ext_amount :=
1996 NVL (l_line_details_rec.price_negotiated, 0);
1997 l_rail_rec.amount :=
1998 NVL (l_line_details_rec.price_negotiated, 0);
1999 x_price_details.status_text := g_manual_adj_price;
2000
2001 IF NVL (l_line_details_rec.price_negotiated, 0) > 0
2002 AND l_qpprod_quantity > 0
2003 THEN
2004 x_price_details.prod_adj_unit_price :=
2005 NVL (l_line_details_rec.price_negotiated, 0) /
2006 l_qpprod_quantity;
2007 ELSE
2008 x_price_details.prod_adj_unit_price :=
2009 NVL (l_line_details_rec.price_negotiated, 0);
2010 END IF;
2011
2012 x_price_details.prod_qty := NULL;
2013 x_price_details.prod_qty_uom := NULL;
2014 x_price_details.prod_price_list_id := NULL;
2015 x_price_details.prod_list_unit_price := NULL;
2016 x_price_details.prod_adj_unit_price := NULL;
2017 x_price_details.prod_priced_qty := NULL;
2018 x_price_details.prod_priced_uom := NULL;
2019 END IF;
2020 ELSE
2021 oks_qp_pkg.calc_price
2022 (p_detail_rec => l_input_detail_rec,
2023 x_price_details => x_price_details,
2024 x_modifier_details => x_modifier_details,
2025 x_price_break_details => x_price_break_details,
2026 x_return_status => x_return_status,
2027 x_msg_count => x_msg_count,
2028 x_msg_data => x_msg_data
2029 );
2030
2031 IF x_return_status <> g_ret_sts_success
2032 THEN
2033 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2034 THEN
2035 fnd_log.STRING (fnd_log.level_statement,
2036 g_module ||
2037 l_api_name,
2038 '313:CALC Price ERROR after SAPI call'
2039 );
2040 END IF;
2041
2042 --errorout('CALC Price ERROR after SAPI call');
2043 RAISE g_exc_error;
2044 END IF;
2045
2046 x_price_details.serv_ext_amount := x_price_details.prod_ext_amount;
2047 l_rail_rec.amount :=
2048 NVL (x_price_details.serv_ext_amount, 0);
2049 -- x_price_details.status_text := NVL(x_price_details.status_text,G_STS_TXT_SUCCESS);
2050 END IF;
2051
2052 x_price_details.serv_qty := x_price_details.prod_qty;
2053 x_price_details.serv_qty_uom := x_price_details.prod_qty_uom;
2054 x_price_details.serv_price_list_id := x_price_details.prod_price_list_id;
2055 x_price_details.serv_list_unit_price :=
2056 x_price_details.prod_list_unit_price;
2057 x_price_details.serv_adj_unit_price :=
2058 x_price_details.prod_adj_unit_price;
2059 x_price_details.serv_priced_qty := x_price_details.prod_priced_qty;
2060 x_price_details.serv_priced_uom := x_price_details.prod_priced_uom;
2061 x_price_details.prod_qty := NULL;
2062 x_price_details.prod_qty_uom := NULL;
2063 x_price_details.prod_price_list_id := NULL;
2064 x_price_details.prod_list_unit_price := NULL;
2065 x_price_details.prod_adj_unit_price := NULL;
2066 x_price_details.prod_priced_qty := NULL;
2067 x_price_details.prod_priced_uom := NULL;
2068 x_price_details.prod_ext_amount := NULL;
2069
2070 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2071 THEN
2072 fnd_log.STRING (fnd_log.level_statement,
2073 g_module ||
2074 l_api_name,
2075 '314:x_price_details.PROD_QTY = ' ||
2076 x_price_details.prod_qty
2077 );
2078 fnd_log.STRING (fnd_log.level_statement,
2079 g_module ||
2080 l_api_name,
2081 '315:x_price_details.PROD_QTY_UOM = ' ||
2082 x_price_details.prod_qty_uom
2083 );
2084 fnd_log.STRING (fnd_log.level_statement,
2085 g_module ||
2086 l_api_name,
2087 '316:x_price_details.SERV_QTY = ' ||
2088 x_price_details.serv_qty
2089 );
2090 fnd_log.STRING (fnd_log.level_statement,
2091 g_module ||
2092 l_api_name,
2093 '317:x_price_details.SERV_QTY_UOM = ' ||
2094 x_price_details.serv_qty_uom
2095 );
2096 fnd_log.STRING (fnd_log.level_statement,
2097 g_module ||
2098 l_api_name,
2099 '318:x_price_details.PROD_PRICE_LIST_ID = ' ||
2100 x_price_details.prod_price_list_id
2101 );
2102 fnd_log.STRING (fnd_log.level_statement,
2103 g_module ||
2104 l_api_name,
2105 '319:x_price_details.SERV_PRICE_LIST_ID = ' ||
2106 x_price_details.serv_price_list_id
2107 );
2108 fnd_log.STRING (fnd_log.level_statement,
2109 g_module ||
2110 l_api_name,
2111 '320:x_price_details.PROD_LIST_UNIT_PRICE = ' ||
2112 x_price_details.prod_list_unit_price
2113 );
2114 fnd_log.STRING (fnd_log.level_statement,
2115 g_module ||
2116 l_api_name,
2117 '321:x_price_details.SERV_LIST_UNIT_PRICE = ' ||
2118 x_price_details.serv_list_unit_price
2119 );
2120 fnd_log.STRING (fnd_log.level_statement,
2121 g_module ||
2122 l_api_name,
2123 '322:x_price_details.PROD_ADJ_UNIT_PRICE = ' ||
2124 x_price_details.prod_adj_unit_price
2125 );
2126 fnd_log.STRING (fnd_log.level_statement,
2127 g_module ||
2128 l_api_name,
2129 '323:x_price_details.SERV_ADJ_UNIT_PRICE = ' ||
2130 x_price_details.serv_adj_unit_price
2131 );
2132 fnd_log.STRING (fnd_log.level_statement,
2133 g_module ||
2134 l_api_name,
2135 '324:x_price_details.PROD_PRICED_QTY = ' ||
2136 x_price_details.prod_priced_qty
2137 );
2138 fnd_log.STRING (fnd_log.level_statement,
2139 g_module ||
2140 l_api_name,
2141 '325:x_price_details.PROD_PRICED_UOM = ' ||
2142 x_price_details.prod_priced_uom
2143 );
2144 fnd_log.STRING (fnd_log.level_statement,
2145 g_module ||
2146 l_api_name,
2147 '326:x_price_details.PROD_EXT_AMOUNT = ' ||
2148 x_price_details.prod_ext_amount
2149 );
2150 fnd_log.STRING (fnd_log.level_statement,
2151 g_module ||
2152 l_api_name,
2153 '327:x_price_details.SERV_PRICED_QTY = ' ||
2154 x_price_details.serv_priced_qty
2155 );
2156 fnd_log.STRING (fnd_log.level_statement,
2157 g_module ||
2158 l_api_name,
2159 '328:x_price_details.SERV_PRICED_UOM = ' ||
2160 x_price_details.serv_priced_uom
2161 );
2162 fnd_log.STRING (fnd_log.level_statement,
2163 g_module ||
2164 l_api_name,
2165 '329:x_price_details.SERV_EXT_AMOUNT = ' ||
2166 x_price_details.serv_ext_amount
2167 );
2168 fnd_log.STRING (fnd_log.level_statement,
2169 g_module ||
2170 l_api_name,
2171 '330:x_price_details.SERV_OPERAND = ' ||
2172 x_price_details.serv_operand
2173 );
2174 fnd_log.STRING (fnd_log.level_statement,
2175 g_module ||
2176 l_api_name,
2177 '331:x_price_details.SERV_OPERATOR = ' ||
2178 x_price_details.serv_operator
2179 );
2180 fnd_log.STRING (fnd_log.level_statement,
2181 g_module ||
2182 l_api_name,
2183 '332:x_price_details.STATUS_TEXT = ' ||
2184 x_price_details.status_text
2185 );
2186 fnd_log.STRING (fnd_log.level_statement,
2187 g_module ||
2188 l_api_name,
2189 '333:l_rail_rec.amount = ' ||
2190 l_rail_rec.amount
2191 );
2192 END IF;
2193
2194 --errorout('x_price_details.PROD_QTY = ' || x_price_details.PROD_QTY );
2195 --errorout('x_pri'ce_details.PROD_QTY_UOM = ' || x_price_details.PROD_QTY_UOM );
2196 --errorout('x_price_details.SERV_QTY = ' || x_price_details.SERV_QTY );
2197 --errorout('x_price_details.SERV_QTY_UOM = ' || x_price_details.SERV_QTY_UOM );
2198 --errorout('x_price_details.PROD_PRICE_LIST_ID = ' || x_price_details.PROD_PRICE_LIST_ID );
2199 --errorout('x_price_details.SERV_PRICE_LIST_ID = ' || x_price_details.SERV_PRICE_LIST_ID );
2200 --errorout('x_price_details.PROD_LIST_UNIT_PRICE = '|| x_price_details.PROD_LIST_UNIT_PRICE );
2201 --errorout('x_price_details.SERV_LIST_UNIT_PRICE = '|| x_price_details.SERV_LIST_UNIT_PRICE );
2202 --errorout('x_price_details.PROD_ADJ_UNIT_PRICE = ' || x_price_details.PROD_ADJ_UNIT_PRICE );
2203 --errorout('x_price_details.SERV_ADJ_UNIT_PRICE = ' || x_price_details.SERV_ADJ_UNIT_PRICE );
2204 --errorout('x_price_details.PROD_PRICED_QTY = ' || x_price_details.PROD_PRICED_QTY );
2205 --errorout('x_price_details.PROD_PRICED_UOM = ' || x_price_details.PROD_PRICED_UOM );
2206 --errorout('x_price_details.PROD_EXT_AMOUNT = ' || x_price_details.PROD_EXT_AMOUNT );
2207 --errorout('x_price_details.SERV_PRICED_QTY = ' || x_price_details.SERV_PRICED_QTY );
2208 --errorout('x_price_details.SERV_PRICED_UOM = ' || x_price_details.SERV_PRICED_UOM );
2209 --errorout('x_price_details.SERV_EXT_AMOUNT = ' || x_price_details.SERV_EXT_AMOUNT );
2210 --errorout('x_price_details.SERV_OPERAND = ' || x_price_details.SERV_OPERAND );
2211 --errorout('x_price_details.SERV_OPERATOR = ' || x_price_details.SERV_OPERATOR );
2212 --errorout('x_price_details.STATUS_TEXT = ' || x_price_details.STATUS_TEXT );
2213 --errorout('l_rail_rec.amount = ' || l_rail_rec.amount);
2214 l_rail_rec.tax_value := NULL;
2215 l_rail_rec.amount_includes_tax_flag := NULL;
2216 -- Calculate Tax
2217 oks_tax_util_pvt.get_tax (p_api_version => l_api_version,
2218 p_init_msg_list => l_init_msg_list,
2219 p_chr_id => l_input_detail_rec.chr_id,
2220 p_cle_id => l_input_detail_rec.line_id,
2221 px_rail_rec => l_rail_rec,
2222 x_msg_count => x_msg_count,
2223 x_msg_data => x_msg_data,
2224 x_return_status => x_return_status
2225 );
2226
2227 IF x_return_status <> g_ret_sts_success
2228 THEN
2229 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2230 THEN
2231 fnd_log.STRING (fnd_log.level_statement,
2232 g_module ||
2233 l_api_name,
2234 '334:Tax ERROR after GET_TAX call x_return_status '||x_return_status
2235 );
2236 fnd_log.STRING (fnd_log.level_statement,
2237 g_module ||
2238 l_api_name,
2239 '334:x_msg_data '||x_msg_data
2240 );
2241 END IF;
2242
2243 --errorout('Tax ERROR after GET_TAX call');
2244 -- bug 5001677, even if tax error continue
2245 -- RAISE g_exc_error;
2246 l_rail_rec.tax_value := 0;
2247 l_rail_rec.amount_includes_tax_flag := 'N';
2248 END IF;
2249
2250 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2251 THEN
2252 fnd_log.STRING (fnd_log.level_statement,
2253 g_module ||
2254 l_api_name,
2255 '336:l_rail_rec.TAX_VALUE = ' ||
2256 l_rail_rec.tax_value
2257 );
2258 fnd_log.STRING (fnd_log.level_statement,
2259 g_module ||
2260 l_api_name,
2261 '337:l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG = ' ||
2262 l_rail_rec.amount_includes_tax_flag
2263 );
2264 END IF;
2265
2266 --errorout('l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG = '||l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG );
2267 --errorout('l_rail_rec.TAX_VALUE = '|| l_rail_rec.TAX_VALUE );
2268 --errorout('l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG = '||l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG );
2269 l_kln_rec_in.cle_id := l_input_detail_rec.line_id;
2270 l_kln_rec_in.dnz_chr_id := l_input_detail_rec.chr_id;
2271 l_kln_rec_in.tax_inclusive_yn := l_rail_rec.amount_includes_tax_flag;
2272
2273 IF l_rail_rec.amount_includes_tax_flag = 'N'
2274 THEN
2275 l_kln_rec_in.tax_amount := NVL (l_rail_rec.tax_value, 0);
2276 ELSE
2277 l_kln_rec_in.tax_amount := 0;
2278 END IF;
2279
2280 IF x_price_details.prod_price_list_id IS NOT NULL
2281 THEN
2282 l_kln_rec_in.prod_price := x_price_details.prod_price_list_id;
2283 END IF;
2284
2285 IF x_price_details.serv_price_list_id IS NOT NULL
2286 THEN
2287 l_kln_rec_in.service_price := x_price_details.serv_price_list_id;
2288 END IF;
2289
2290 l_kln_rec_in.clvl_list_price := x_price_details.prod_list_unit_price;
2291 l_kln_rec_in.clvl_quantity := x_price_details.prod_priced_qty;
2292 l_kln_rec_in.clvl_extended_amt := x_price_details.prod_ext_amount;
2293 l_kln_rec_in.toplvl_operand_code := x_price_details.serv_operator;
2294 l_kln_rec_in.toplvl_operand_val := x_price_details.serv_operand;
2295 l_kln_rec_in.clvl_uom_code := x_price_details.prod_priced_uom;
2296 l_kln_rec_in.toplvl_quantity := x_price_details.serv_qty;
2297 l_kln_rec_in.toplvl_uom_code := x_price_details.serv_priced_uom;
2298 l_kln_rec_in.toplvl_adj_price := x_price_details.serv_adj_unit_price;
2299 -- GCHADHA --
2300 -- 17-NOV-2004 --
2301 -- BUG 4015739 --
2302 l_kln_rec_in.status_text :=
2303 SUBSTR (NVL (x_price_details.status_text, g_sts_txt_success),
2304 1,
2305 450
2306 );
2307 -- l_kln_rec_in.status_text := NVL(x_price_details.status_text,G_STS_TXT_SUCCESS);
2308
2309 -- END GCHADHA --
2310 l_kln_rec_in.toplvl_price_qty := x_price_details.serv_priced_qty;
2311 get_k_details (p_id => l_input_detail_rec.line_id,
2312 p_type => g_oks_line,
2313 x_k_det_rec => l_k_det_rec
2314 );
2315 l_kln_rec_in.ID := l_k_det_rec.ID;
2316 l_kln_rec_in.object_version_number := l_k_det_rec.object_version_number;
2317 oks_contract_line_pub.update_line (p_api_version => l_api_version,
2318 p_init_msg_list => l_init_msg_list,
2319 x_return_status => x_return_status,
2320 x_msg_count => x_msg_count,
2321 x_msg_data => x_msg_data,
2322 p_klnv_rec => l_kln_rec_in,
2323 x_klnv_rec => l_kln_rec_out,
2324 p_validate_yn => 'N'
2325 );
2326
2327 IF x_return_status <> g_ret_sts_success
2328 THEN
2329 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2330 THEN
2331 fnd_log.STRING (fnd_log.level_statement,
2332 g_module ||
2333 l_api_name,
2334 '338:Update line details at Calc Subscription'
2335 );
2336 END IF;
2337
2338 -- errorout('Update line details at Calc Subscription');
2339 RAISE g_exc_error;
2340 END IF;
2341
2342 l_clev_rec_in.ID := l_input_detail_rec.line_id;
2343 l_clev_rec_in.line_list_price := x_price_details.serv_list_unit_price;
2344 l_clev_rec_in.price_negotiated := l_rail_rec.amount;
2345 get_k_details (p_id => l_input_detail_rec.line_id,
2346 p_type => g_okc_line,
2347 x_k_det_rec => l_k_det_rec
2348 );
2349 l_clev_rec_in.object_version_number := l_k_det_rec.object_version_number;
2350 okc_contract_pub.update_contract_line
2351 (p_api_version => l_api_version,
2352 p_init_msg_list => l_init_msg_list,
2353 x_return_status => x_return_status,
2354 x_msg_count => x_msg_count,
2355 x_msg_data => x_msg_data,
2356 p_clev_rec => l_clev_rec_in,
2357 x_clev_rec => l_clev_rec_out
2358 );
2359
2360 IF x_return_status <> g_ret_sts_success
2361 THEN
2362 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2363 THEN
2364 fnd_log.STRING
2365 (fnd_log.level_statement,
2366 g_module ||
2367 l_api_name,
2368 '339:Update Contract line ERROR at Calc Subscription'
2369 );
2370 END IF;
2371
2372 -- errorout('Update Contract line ERROR at Calc Subscription');
2373 RAISE g_exc_error;
2374 END IF;
2375
2376 -- Update Subsciption elements
2377 FOR l_subs_elements_rec IN l_subs_elements_csr (p_detail_rec.line_id)
2378 LOOP
2379 l_scev_rec_in.ID := l_subs_elements_rec.ID;
2380 l_scev_rec_in.amount :=
2381 l_subs_elements_rec.quantity *
2382 x_price_details.serv_adj_unit_price;
2383 l_scev_rec_in.object_version_number :=
2384 l_subs_elements_rec.object_version_number;
2385
2386 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2387 THEN
2388 fnd_log.STRING (fnd_log.level_statement,
2389 g_module ||
2390 l_api_name,
2391 '340:Subscription element ID : ' ||
2392 l_scev_rec_in.ID
2393 );
2394 fnd_log.STRING (fnd_log.level_statement,
2395 g_module ||
2396 l_api_name,
2397 '341:Subscription element Amt : ' ||
2398 l_scev_rec_in.amount
2399 );
2400 END IF;
2401
2402 -- errorout('Subscription element ID : ' || l_scev_rec_in.id);
2403 -- errorout('Subscription element Amt : ' || l_scev_rec_in.amount);
2404 oks_subscr_elems_pub.update_row (p_api_version => l_api_version,
2405 p_init_msg_list => l_init_msg_list,
2406 x_return_status => x_return_status,
2407 x_msg_count => x_msg_count,
2408 x_msg_data => x_msg_data,
2409 p_scev_rec => l_scev_rec_in,
2410 x_scev_rec => l_scev_rec_out
2411 );
2412
2413 IF x_return_status <> g_ret_sts_success
2414 THEN
2415 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2416 THEN
2417 fnd_log.STRING
2418 (fnd_log.level_statement,
2419 g_module ||
2420 l_api_name,
2421 '342:ERROR while updating Subscription elements at Calc Subscription'
2422 );
2423 END IF;
2424
2425 -- errorout('ERROR while updating Subscription elements at Calc Subscription');
2426 RAISE g_exc_error;
2427 END IF;
2428 END LOOP;
2429
2430 -- If request coming from Header pricing, populate the message table
2431 IF p_detail_rec.intent = g_subsc_reg_pricing
2432 THEN
2433 -- Get subscription line details
2434 get_line_details (p_line_id => l_input_detail_rec.line_id,
2435 x_name => l_tlvl_name,
2436 x_description => l_tlvl_desc,
2437 x_return_status => x_return_status,
2438 x_msg_count => x_msg_count,
2439 x_msg_data => x_msg_data
2440 );
2441
2442 IF x_return_status <> g_ret_sts_success
2443 THEN
2444 RAISE g_exc_error;
2445 END IF;
2446
2447 g_pricing_status_tbl (g_index).service_name := l_tlvl_name;
2448 g_pricing_status_tbl (g_index).coverage_level_name := l_tlvl_desc; -- bug 5014604
2449 g_pricing_status_tbl (g_index).status_code := g_sts_code_success;
2450 l_line_no :=
2451 get_line_no (l_input_detail_rec.line_id);
2452
2453 IF l_line_no IS NULL
2454 THEN
2455 g_pricing_status_tbl (g_index).status_text :=
2456 NVL (x_price_details.status_text, g_sts_txt_success);
2457 ELSE
2458 -- bug 4730011
2459 fnd_message.set_name ('OKS', 'OKS_LINE_REPRICE_SUCCESS');
2460 fnd_message.set_token ('LINENO', l_line_no);
2461 g_pricing_status_tbl (g_index).status_text := fnd_message.get;
2462 -- l_line_no ||' ' ||NVL (x_price_details.status_text, g_sts_txt_success);
2463 END IF;
2464
2465 g_index := g_index +
2466 1;
2467 END IF;
2468
2469 x_price_details.status_text :=
2470 NVL (x_price_details.status_text, g_sts_txt_success);
2471
2472 -- end debug log
2473 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2474 THEN
2475 fnd_log.STRING (fnd_log.level_procedure,
2476 g_module ||
2477 l_api_name,
2478 '1000: Leaving ' ||
2479 g_pkg_name ||
2480 '.' ||
2481 l_api_name
2482 );
2483 END IF;
2484 EXCEPTION
2485 WHEN g_exc_cant_price
2486 THEN
2487 -- end debug log
2488 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2489 THEN
2490 fnd_log.STRING (fnd_log.level_procedure,
2491 g_module ||
2492 l_api_name,
2493 '7000: Leaving ' ||
2494 g_pkg_name ||
2495 '.' ||
2496 l_api_name
2497 );
2498 END IF;
2499
2500 IF p_detail_rec.intent = g_subsc_reg_pricing
2501 THEN
2502 -- Get subscription line details
2503 get_line_details (p_line_id => l_input_detail_rec.line_id,
2504 x_name => l_tlvl_name,
2505 x_description => l_tlvl_desc,
2506 x_return_status => l_return_status,
2507 x_msg_count => x_msg_count,
2508 x_msg_data => x_msg_data
2509 );
2510 g_pricing_status_tbl (g_index).service_name := l_tlvl_name;
2511 g_pricing_status_tbl (g_index).coverage_level_name := l_tlvl_desc; -- bug 5014604
2512 g_pricing_status_tbl (g_index).status_code := g_sts_code_success;
2513
2514 IF x_price_details.status_code = g_partial_billed
2515 or
2516 x_price_details.status_code = g_fully_billed
2517 THEN
2518 g_pricing_status_tbl (g_index).status_text := g_billed_line;
2519 ELSE
2520 g_pricing_status_tbl (g_index).status_text :=
2521 g_sts_txt_success;
2522 END IF;
2523
2524 g_index := g_index +
2525 1;
2526 END IF;
2527
2528 IF l_status = g_fully_billed
2529 OR l_status = g_partial_billed
2530 THEN
2531 x_price_details.status_code := g_billed;
2532 END IF;
2533 WHEN g_exc_error
2534 THEN
2535 -- end debug log
2536 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2537 THEN
2538 fnd_log.STRING (fnd_log.level_procedure,
2539 g_module ||
2540 l_api_name,
2541 '2000: Leaving ' ||
2542 g_pkg_name ||
2543 '.' ||
2544 l_api_name
2545 );
2546 END IF;
2547
2548 IF p_detail_rec.intent = g_subsc_reg_pricing
2549 THEN
2550 -- Get subscription line details
2551 get_line_details (p_line_id => l_input_detail_rec.line_id,
2552 x_name => l_tlvl_name,
2553 x_description => l_tlvl_desc,
2554 x_return_status => l_return_status,
2555 x_msg_count => x_msg_count,
2556 x_msg_data => x_msg_data
2557 );
2558 g_pricing_status_tbl (g_index).service_name := l_tlvl_name;
2559 g_pricing_status_tbl (g_index).coverage_level_name := l_tlvl_desc; -- bug 5014604
2560 g_pricing_status_tbl (g_index).status_code := g_sts_code_error;
2561 l_line_no :=
2562 get_line_no (l_input_detail_rec.line_id);
2563
2564 IF l_line_no IS NULL
2565 THEN
2566 g_pricing_status_tbl (g_index).status_text :=
2567 NVL (x_price_details.status_text, g_sts_txt_error);
2568 ELSE
2569 -- bug 4730011
2570 fnd_message.set_name ('OKS', 'OKS_LINE_REPRICE_SUCCESS');
2571 fnd_message.set_token ('LINENO', l_line_no);
2572 g_pricing_status_tbl (g_index).status_text := fnd_message.get;
2573 -- l_line_no ||' ' ||NVL (x_price_details.status_text, g_sts_txt_error);
2574 END IF;
2575
2576 g_index := g_index +
2577 1;
2578 END IF;
2579 WHEN OTHERS
2580 THEN
2581 -- end debug log
2582 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2583 THEN
2584 fnd_log.STRING (fnd_log.level_procedure,
2585 g_module ||
2586 l_api_name,
2587 '4000: Leaving ' ||
2588 g_pkg_name ||
2589 '.' ||
2590 l_api_name
2591 );
2592 END IF;
2593
2594 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2595 x_return_status := g_ret_sts_unexp_error;
2596 okc_api.set_message (p_app_name => g_app_name,
2597 p_msg_name => g_unexpected_error,
2598 p_token1 => g_sqlcode_token,
2599 p_token1_value => SQLCODE,
2600 p_token2 => g_sqlerrm_token,
2601 p_token2_value => SQLERRM
2602 );
2603 END calculate_subscription_price;
2604
2605 PROCEDURE calculate_subline_price (
2606 p_detail_rec IN oks_qp_pkg.input_details,
2607 x_price_details OUT NOCOPY oks_qp_pkg.price_details,
2608 x_modifier_details OUT NOCOPY qp_preq_grp.line_detail_tbl_type,
2609 x_price_break_details OUT NOCOPY oks_qp_pkg.g_price_break_tbl_type,
2610 x_return_status OUT NOCOPY VARCHAR2,
2611 x_msg_count OUT NOCOPY NUMBER,
2612 x_msg_data OUT NOCOPY VARCHAR2
2613 )
2614 IS
2615 l_api_name CONSTANT VARCHAR2 (30)
2616 := 'CALCULATE_SUBLINE_PRICE';
2617
2618 CURSOR l_currcode_csr (
2619 p_hdr_id NUMBER
2620 )
2621 IS
2622 SELECT currency_code
2623 FROM okc_k_headers_all_b
2624 WHERE ID = p_hdr_id;
2625
2626 CURSOR l_line_details_csr (
2627 p_cle_id NUMBER
2628 )
2629 IS
2630 SELECT ID,
2631 start_date,
2632 end_date,
2633 cle_id,
2634 dnz_chr_id,
2635 lse_id,
2636 price_negotiated,
2637 price_unit,
2638 price_unit_percent,
2639 price_list_line_id,
2640 price_list_id,
2641 line_list_price,
2642 item_to_price_yn,
2643 pricing_date,
2644 price_basis_yn
2645 FROM okc_k_lines_b
2646 WHERE ID = p_cle_id
2647 AND lse_id IN (7, 9, 25);
2648
2649 CURSOR l_prodpriceuom_csr (
2650 p_id NUMBER
2651 )
2652 IS
2653 SELECT price_uom
2654 FROM oks_k_lines_b
2655 WHERE cle_id = p_id;
2656
2657 CURSOR l_get_hdrid_csr (
2658 p_cle_id NUMBER
2659 )
2660 IS
2661 SELECT dnz_chr_id
2662 FROM okc_k_lines_b
2663 WHERE ID = p_cle_id;
2664
2665 CURSOR l_numitems_csr (
2666 p_subline_id NUMBER
2667 )
2668 IS
2669 SELECT number_of_items
2670 FROM okc_k_items_v
2671 WHERE cle_id = p_subline_id;
2672
2673 l_num_items NUMBER;
2674 l_duration_qty NUMBER;
2675 l_cpuom_code VARCHAR2 (200);
2676 l_line_details_rec l_line_details_csr%ROWTYPE;
2677 l_input_detail_rec oks_qp_pkg.input_details
2678 := p_detail_rec;
2679 l_rail_rec oks_tax_util_pvt.ra_rec_type;
2680 l_k_det_rec k_details_rec;
2681 l_khdr_rec_in oks_contract_hdr_pub.khrv_rec_type;
2682 l_khdr_rec_out oks_contract_hdr_pub.khrv_rec_type;
2683 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
2684 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
2685 l_clev_rec_in okc_contract_pub.clev_rec_type;
2686 l_clev_rec_out okc_contract_pub.clev_rec_type;
2687 l_prod_qty NUMBER;
2688 l_serv_qty NUMBER;
2689 l_status VARCHAR2 (30); --3912685
2690 --new variables for partial periods
2691 l_period_type VARCHAR2 (30);
2692 l_period_start VARCHAR2 (30);
2693 l_price_uom VARCHAR2 (30);
2694 l_chr_id NUMBER;
2695 invalid_hdr_id_exception EXCEPTION;
2696 BEGIN
2697 -- start debug log
2698 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2699 THEN
2700 fnd_log.STRING (fnd_log.level_procedure,
2701 g_module ||
2702 l_api_name,
2703 '100: Entered ' ||
2704 g_pkg_name ||
2705 '.' ||
2706 l_api_name
2707 );
2708 END IF;
2709
2710 x_return_status := g_ret_sts_success;
2711
2712 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2713 THEN
2714 fnd_log.STRING (fnd_log.level_statement,
2715 g_module ||
2716 l_api_name,
2717 '343:********** Entered Sub line Price ********'
2718 );
2719 fnd_log.STRING (fnd_log.level_statement,
2720 g_module ||
2721 l_api_name,
2722 '344:CHR_ID = ' ||
2723 p_detail_rec.chr_id
2724 );
2725 fnd_log.STRING (fnd_log.level_statement,
2726 g_module ||
2727 l_api_name,
2728 '345:LINE_ID = ' ||
2729 p_detail_rec.line_id
2730 );
2731 fnd_log.STRING (fnd_log.level_statement,
2732 g_module ||
2733 l_api_name,
2734 '346:SUBLINE_ID = ' ||
2735 p_detail_rec.subline_id
2736 );
2737 fnd_log.STRING (fnd_log.level_statement,
2738 g_module ||
2739 l_api_name,
2740 '347:INTENT = ' ||
2741 p_detail_rec.intent
2742 );
2743 fnd_log.STRING (fnd_log.level_statement,
2744 g_module ||
2745 l_api_name,
2746 '348:CURRENCY = ' ||
2747 p_detail_rec.currency
2748 );
2749 fnd_log.STRING (fnd_log.level_statement,
2750 g_module ||
2751 l_api_name,
2752 '349:Usage Qty = ' ||
2753 p_detail_rec.usage_qty
2754 );
2755 fnd_log.STRING (fnd_log.level_statement,
2756 g_module ||
2757 l_api_name,
2758 '350:Usage UOM Code = ' ||
2759 p_detail_rec.usage_uom_code
2760 );
2761 END IF;
2762
2763 --errorout ('********** Entered Sub line Price ********');
2764 --errorout ('CHR_ID = ' || p_detail_rec.chr_id);
2765 --errorout ('LINE_ID = ' || p_detail_rec.line_id);
2766 --errorout ('SUBLINE_ID = ' || p_detail_rec.subline_id);
2767 --errorout ('INTENT = ' || p_detail_rec.intent);
2768 --errorout ('CURRENCY = ' || p_detail_rec.currency);
2769 --errorout ('Usage Qty = ' || p_detail_rec.usage_qty);
2770 --errorout ('Usage UOM Code = ' || p_detail_rec.usage_uom_code);
2771
2772 /** 3912685 **/
2773 check_pricing_eligibility (p_line_id => p_detail_rec.subline_id,
2774 x_status => l_status,
2775 x_return_status => x_return_status,
2776 x_msg_count => x_msg_count,
2777 x_msg_data => x_msg_data
2778 );
2779
2780 IF x_return_status <> g_ret_sts_success
2781 THEN
2782 RAISE g_exc_error;
2783 END IF;
2784
2785 IF l_status <> g_oks_success
2786 THEN
2787 x_price_details.status_code := l_status;
2788 RAISE g_exc_cant_price;
2789 END IF;
2790
2791 /** 3912685 **/
2792 IF p_detail_rec.intent = g_override_pricing
2793 THEN
2794 OPEN l_line_details_csr (p_detail_rec.subline_id);
2795
2796 FETCH l_line_details_csr
2797 INTO l_line_details_rec;
2798
2799 IF l_line_details_csr%NOTFOUND
2800 THEN
2801 CLOSE l_line_details_csr;
2802
2803 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2804 THEN
2805 fnd_log.STRING
2806 (fnd_log.level_statement,
2807 g_module ||
2808 l_api_name,
2809 '351:Override amount not present in Price Negotiated column'
2810 );
2811 END IF;
2812
2813 --errorout ('Override amount not present in Price Negotiated column');
2814 okc_api.set_message (p_app_name => g_app_name,
2815 p_msg_name => g_required_value,
2816 p_token1 => g_col_name_token,
2817 p_token1_value => 'PRICE_NEGOTIATED'
2818 );
2819 x_return_status := g_ret_sts_error;
2820 RAISE g_exc_error;
2821 END IF;
2822
2823 CLOSE l_line_details_csr;
2824
2825 IF NVL (fnd_profile.VALUE ('OKS_USE_QP_FOR_MANUAL_ADJ'), 'NO') =
2826 'YES'
2827 THEN
2828 oks_qp_pkg.calc_price
2829 (p_detail_rec => l_input_detail_rec,
2830 x_price_details => x_price_details,
2831 x_modifier_details => x_modifier_details,
2832 x_price_break_details => x_price_break_details,
2833 x_return_status => x_return_status,
2834 x_msg_count => x_msg_count,
2835 x_msg_data => x_msg_data
2836 );
2837
2838 IF x_return_status <> g_ret_sts_success
2839 THEN
2840 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
2841 )
2842 THEN
2843 fnd_log.STRING (fnd_log.level_statement,
2844 g_module ||
2845 l_api_name,
2846 '352:CALC PRICE ERROR at 1st SAPI call'
2847 );
2848 END IF;
2849
2850 --errorout('CALC PRICE ERROR at 1st SAPI call');
2851 RAISE g_exc_error;
2852 END IF;
2853
2854 -- Now calculate the override unit price
2855 l_prod_qty :=
2856 NVL (x_price_details.prod_priced_qty, 1);
2857 l_serv_qty :=
2858 NVL (x_price_details.serv_priced_qty, 1);
2859 l_input_detail_rec.asking_unit_price :=
2860 l_line_details_rec.price_negotiated /
2861 (l_prod_qty *
2862 l_serv_qty
2863 );
2864
2865 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2866 THEN
2867 fnd_log.STRING (fnd_log.level_statement,
2868 g_module ||
2869 l_api_name,
2870 '353:Asking price : ' ||
2871 l_input_detail_rec.asking_unit_price
2872 );
2873 END IF;
2874
2875 --errorout('Asking price : ' || l_input_detail_rec.asking_unit_price);
2876 oks_qp_pkg.calc_price
2877 (p_detail_rec => l_input_detail_rec,
2878 x_price_details => x_price_details,
2879 x_modifier_details => x_modifier_details,
2880 x_price_break_details => x_price_break_details,
2881 x_return_status => x_return_status,
2882 x_msg_count => x_msg_count,
2883 x_msg_data => x_msg_data
2884 );
2885
2886 IF x_return_status <> g_ret_sts_success
2887 THEN
2888 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2889 )
2890 THEN
2891 fnd_log.STRING (fnd_log.level_procedure,
2892 g_module ||
2893 l_api_name,
2894 '201:CALC PRICE ERROR at 2nd SAPI call'
2895 );
2896 END IF;
2897
2898 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
2899 )
2900 THEN
2901 fnd_log.STRING (fnd_log.level_statement,
2902 g_module ||
2903 l_api_name,
2904 '354:CALC PRICE ERROR at 2nd SAPI call'
2905 );
2906 END IF;
2907
2908 --dbms_output.put_line('CALC PRICE ERROR at 2nd SAPI call');
2909 RAISE g_exc_error;
2910 END IF;
2911
2912 l_rail_rec.amount :=
2913 NVL (x_price_details.serv_ext_amount, 0);
2914 x_price_details.status_text :=
2915 NVL (x_price_details.status_text, g_sts_txt_success);
2916 ELSE
2917 OPEN l_prodpriceuom_csr (p_detail_rec.subline_id);
2918
2919 FETCH l_prodpriceuom_csr
2920 INTO l_cpuom_code;
2921
2922 CLOSE l_prodpriceuom_csr;
2923
2924 --New logic for Partial Periods
2925 OPEN l_get_hdrid_csr (p_detail_rec.subline_id);
2926
2927 FETCH l_get_hdrid_csr
2928 INTO l_chr_id;
2929
2930 CLOSE l_get_hdrid_csr;
2931
2932 IF l_chr_id IS NOT NULL
2933 THEN
2934 oks_renew_util_pub.get_period_defaults
2935 (p_hdr_id => l_chr_id,
2936 p_org_id => NULL,
2937 x_period_type => l_period_type,
2938 x_period_start => l_period_start,
2939 x_price_uom => l_price_uom,
2940 x_return_status => x_return_status
2941 );
2942
2943 IF x_return_status <> g_ret_sts_success
2944 THEN
2945 RAISE g_exc_error;
2946 END IF;
2947 ELSE
2948 RAISE invalid_hdr_id_exception;
2949 END IF;
2950
2951 --End logic for Partial Periods
2952 IF l_cpuom_code IS NOT NULL
2953 THEN
2954 l_duration_qty :=
2955 oks_time_measures_pub.get_quantity
2956 (p_start_date => l_line_details_rec.start_date,
2957 p_end_date => l_line_details_rec.end_date,
2958 p_source_uom => l_cpuom_code,
2959 p_period_type => l_period_type,
2960 --new parameter
2961 p_period_start => l_period_start
2962 --new paramter
2963 );
2964 ELSE
2965 okc_time_util_pub.get_duration
2966 (p_start_date => l_line_details_rec.start_date,
2967 p_end_date => l_line_details_rec.end_date,
2968 x_duration => l_duration_qty,
2969 x_timeunit => l_cpuom_code,
2970 x_return_status => x_return_status
2971 );
2972 END IF;
2973
2974 OPEN l_numitems_csr (p_detail_rec.subline_id);
2975
2976 FETCH l_numitems_csr
2977 INTO l_num_items;
2978
2979 CLOSE l_numitems_csr;
2980
2981 IF l_input_detail_rec.currency IS NULL
2982 THEN
2983 OPEN l_currcode_csr (l_line_details_rec.dnz_chr_id);
2984
2985 FETCH l_currcode_csr
2986 INTO l_input_detail_rec.currency;
2987
2988 CLOSE l_currcode_csr;
2989 END IF;
2990
2991 -- bug 5018782, don't round any amount that we get from QP
2992 l_line_details_rec.price_negotiated := ROUND (l_line_details_rec.price_negotiated, 29) ;
2993 /* bug 5018782
2994 oks_extwar_util_pvt.round_currency_amt
2995 (p_amount => l_line_details_rec.price_negotiated,
2996 p_currency_code => l_input_detail_rec.currency);
2997 */
2998 l_rail_rec.amount :=
2999 NVL (l_line_details_rec.price_negotiated, 0);
3000 x_price_details.status_text := g_manual_adj_price;
3001 x_price_details.serv_ext_amount :=
3002 NVL (l_line_details_rec.price_negotiated, 0);
3003
3004 IF NVL (l_line_details_rec.price_negotiated, 0) > 0
3005 AND l_duration_qty > 0
3006 AND l_num_items > 0
3007 THEN
3008 x_price_details.serv_adj_unit_price :=
3009 NVL (l_line_details_rec.price_negotiated, 0) /
3010 (l_duration_qty *
3011 l_num_items
3012 );
3013 ELSE
3014 x_price_details.serv_adj_unit_price :=
3015 NVL (l_line_details_rec.price_negotiated, 0);
3016 END IF;
3017
3018 x_price_details.serv_adj_unit_price :=
3019 ROUND (x_price_details.serv_adj_unit_price, 29);
3020 x_price_details.prod_list_unit_price := NULL;
3021 x_price_details.prod_priced_qty := NULL;
3022 x_price_details.prod_ext_amount := NULL;
3023 x_price_details.serv_list_unit_price := NULL;
3024 x_price_details.serv_operator := NULL;
3025 x_price_details.serv_operand := NULL;
3026 x_price_details.prod_priced_uom := NULL;
3027 x_price_details.serv_qty := NULL;
3028 x_price_details.serv_priced_uom := NULL;
3029 END IF;
3030 ELSE
3031 oks_qp_pkg.calc_price
3032 (p_detail_rec => l_input_detail_rec,
3033 x_price_details => x_price_details,
3034 x_modifier_details => x_modifier_details,
3035 x_price_break_details => x_price_break_details,
3036 x_return_status => x_return_status,
3037 x_msg_count => x_msg_count,
3038 x_msg_data => x_msg_data
3039 );
3040
3041 IF x_return_status <> g_ret_sts_success
3042 THEN
3043 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3044 THEN
3045 fnd_log.STRING (fnd_log.level_statement,
3046 g_module ||
3047 l_api_name,
3048 '355:CALC PRICE ERROR at 2nd SAPI call'
3049 );
3050 END IF;
3051
3052 -- errorout('CALC PRICE ERROR at 2nd SAPI call');
3053 RAISE g_exc_error;
3054 END IF;
3055
3056 l_rail_rec.amount :=
3057 NVL (x_price_details.serv_ext_amount, 0);
3058 x_price_details.status_text :=
3059 NVL (x_price_details.status_text, g_sts_txt_success);
3060 END IF;
3061
3062 l_rail_rec.tax_value := NULL;
3063 l_rail_rec.amount_includes_tax_flag := NULL;
3064 -- Calculate Tax
3065 oks_tax_util_pvt.get_tax (p_api_version => l_api_version,
3066 p_init_msg_list => l_init_msg_list,
3067 p_chr_id => l_input_detail_rec.chr_id,
3068 p_cle_id => l_input_detail_rec.subline_id,
3069 px_rail_rec => l_rail_rec,
3070 x_msg_count => x_msg_count,
3071 x_msg_data => x_msg_data,
3072 x_return_status => x_return_status
3073 );
3074
3075 IF x_return_status <> g_ret_sts_success
3076 THEN
3077 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3078 THEN
3079 fnd_log.STRING (fnd_log.level_statement,
3080 g_module ||
3081 l_api_name,
3082 '356:TAX ERROR in Subline Price x_return_status '||x_return_status
3083 );
3084 fnd_log.STRING (fnd_log.level_statement,
3085 g_module ||
3086 l_api_name,
3087 '356: x_msg_data '||x_msg_data
3088 );
3089 END IF;
3090
3091 --errorout('TAX ERROR in Subline Price');
3092 -- bug 5001677, even if tax error continue
3093 -- RAISE g_exc_error;
3094 l_rail_rec.tax_value := 0;
3095 l_rail_rec.amount_includes_tax_flag := 'N';
3096 END IF;
3097
3098 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3099 THEN
3100 fnd_log.STRING (fnd_log.level_statement,
3101 g_module ||
3102 l_api_name,
3103 '358:l_rail_rec.TAX_VALUE = ' ||
3104 l_rail_rec.tax_value
3105 );
3106 fnd_log.STRING (fnd_log.level_statement,
3107 g_module ||
3108 l_api_name,
3109 '359:l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG = ' ||
3110 l_rail_rec.amount_includes_tax_flag
3111 );
3112 END IF;
3113
3114 --errorout('l_rail_rec.TAX_VALUE = '||l_rail_rec.TAX_VALUE);
3115 --errorout('l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG = '||l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG );
3116 l_kln_rec_in.cle_id := l_input_detail_rec.subline_id;
3117 l_kln_rec_in.dnz_chr_id := l_input_detail_rec.chr_id;
3118 l_kln_rec_in.tax_inclusive_yn := l_rail_rec.amount_includes_tax_flag;
3119
3120 IF l_rail_rec.amount_includes_tax_flag = 'N'
3121 THEN
3122 l_kln_rec_in.tax_amount := NVL (l_rail_rec.tax_value, 0);
3123 ELSE
3124 l_kln_rec_in.tax_amount := 0;
3125 END IF;
3126
3127 IF x_price_details.prod_price_list_id IS NOT NULL
3128 THEN
3129 l_kln_rec_in.prod_price := x_price_details.prod_price_list_id;
3130 END IF;
3131
3132 IF x_price_details.serv_price_list_id IS NOT NULL
3133 THEN
3134 l_kln_rec_in.service_price := x_price_details.serv_price_list_id;
3135 END IF;
3136
3137 l_kln_rec_in.clvl_list_price := x_price_details.prod_list_unit_price;
3138 l_kln_rec_in.clvl_quantity := x_price_details.prod_priced_qty;
3139 l_kln_rec_in.clvl_extended_amt := x_price_details.prod_ext_amount;
3140 l_kln_rec_in.toplvl_operand_code := x_price_details.serv_operator;
3141 l_kln_rec_in.toplvl_operand_val := x_price_details.serv_operand;
3142 l_kln_rec_in.clvl_uom_code := x_price_details.prod_priced_uom;
3143 l_kln_rec_in.toplvl_quantity := x_price_details.serv_qty;
3144 l_kln_rec_in.toplvl_uom_code := x_price_details.serv_priced_uom;
3145 l_kln_rec_in.toplvl_adj_price := x_price_details.serv_adj_unit_price;
3146 -- GCHADHA --
3147 -- BUG 4015739 --
3148 -- 17-NOV-2004 --
3149 --l_kln_rec_in.status_text := NVL(x_price_details.status_text,G_STS_TXT_SUCCESS);
3150 l_kln_rec_in.status_text :=
3151 SUBSTR (NVL (x_price_details.status_text, g_sts_txt_success),
3152 1,
3153 450
3154 );
3155 -- END GCHADHA --
3156 l_kln_rec_in.toplvl_price_qty := x_price_details.serv_priced_qty;
3157 get_k_details (p_id => l_input_detail_rec.subline_id,
3158 p_type => g_oks_line,
3159 x_k_det_rec => l_k_det_rec
3160 );
3161 l_kln_rec_in.ID := l_k_det_rec.ID;
3162 l_kln_rec_in.object_version_number := l_k_det_rec.object_version_number;
3163 oks_contract_line_pub.update_line (p_api_version => l_api_version,
3164 p_init_msg_list => l_init_msg_list,
3165 x_return_status => x_return_status,
3166 x_msg_count => x_msg_count,
3167 x_msg_data => x_msg_data,
3168 p_klnv_rec => l_kln_rec_in,
3169 x_klnv_rec => l_kln_rec_out,
3170 p_validate_yn => 'N'
3171 );
3172
3173 IF x_return_status <> g_ret_sts_success
3174 THEN
3175 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3176 THEN
3177 fnd_log.STRING (fnd_log.level_statement,
3178 g_module ||
3179 l_api_name,
3180 '360:Update line details at Calc Subscription'
3181 );
3182 END IF;
3183
3184 --errorout('Update line details at Calc Subscription');
3185 RAISE g_exc_error;
3186 END IF;
3187
3188 l_clev_rec_in.ID := l_input_detail_rec.subline_id;
3189 l_clev_rec_in.line_list_price := x_price_details.serv_list_unit_price;
3190 l_clev_rec_in.price_negotiated := l_rail_rec.amount;
3191 l_clev_rec_in.price_unit :=
3192 NVL (x_price_details.serv_adj_unit_price,
3193 x_price_details.serv_list_unit_price);
3194 --bug 3360423 list_unit_price will be stored in the database instead of adj_unit_price
3195 get_k_details (p_id => l_input_detail_rec.subline_id,
3196 p_type => g_okc_line,
3197 x_k_det_rec => l_k_det_rec
3198 );
3199 l_clev_rec_in.object_version_number := l_k_det_rec.object_version_number;
3200 okc_contract_pub.update_contract_line
3201 (p_api_version => l_api_version,
3202 p_init_msg_list => l_init_msg_list,
3203 x_return_status => x_return_status,
3204 x_msg_count => x_msg_count,
3205 x_msg_data => x_msg_data,
3206 p_clev_rec => l_clev_rec_in,
3207 x_clev_rec => l_clev_rec_out
3208 );
3209
3210 IF x_return_status <> g_ret_sts_success
3211 THEN
3212 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3213 THEN
3214 fnd_log.STRING
3215 (fnd_log.level_statement,
3216 g_module ||
3217 l_api_name,
3218 '361:Contract line updation ERROR in Subline Price'
3219 );
3220 END IF;
3221
3222 -- errorout('Contract line updation ERROR in Subline Price');
3223 RAISE g_exc_error;
3224 END IF;
3225
3226 -- end debug log
3227 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3228 THEN
3229 fnd_log.STRING (fnd_log.level_procedure,
3230 g_module ||
3231 l_api_name,
3232 '1000: Leaving ' ||
3233 g_pkg_name ||
3234 '.' ||
3235 l_api_name
3236 );
3237 END IF;
3238 EXCEPTION
3239 WHEN g_exc_cant_price
3240 THEN
3241 -- end debug log
3242 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3243 THEN
3244 fnd_log.STRING (fnd_log.level_procedure,
3245 g_module ||
3246 l_api_name,
3247 '7000: Leaving ' ||
3248 g_pkg_name ||
3249 '.' ||
3250 l_api_name
3251 );
3252 END IF;
3253
3254 IF l_status = g_fully_billed
3255 OR l_status = g_partial_billed
3256 THEN
3257 x_price_details.status_code := g_billed;
3258 END IF;
3259
3260 NULL;
3261 WHEN g_exc_error
3262 THEN
3263 -- end debug log
3264 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3265 THEN
3266 fnd_log.STRING (fnd_log.level_procedure,
3267 g_module ||
3268 l_api_name,
3269 '2000: Leaving ' ||
3270 g_pkg_name ||
3271 '.' ||
3272 l_api_name
3273 );
3274 END IF;
3275
3276 NULL;
3277 WHEN invalid_hdr_id_exception
3278 THEN
3279 -- end debug log
3280 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3281 THEN
3282 fnd_log.STRING (fnd_log.level_procedure,
3283 g_module ||
3284 l_api_name,
3285 '8000: Leaving ' ||
3286 g_pkg_name ||
3287 '.' ||
3288 l_api_name
3289 );
3290 END IF;
3291
3292 okc_api.set_message (p_app_name => g_app_name,
3293 p_msg_name => g_invalid_value,
3294 p_token1 => g_col_name_token,
3295 p_token1_value => 'Header ID'
3296 );
3297 WHEN OTHERS
3298 THEN
3299 -- end debug log
3300 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3301 THEN
3302 fnd_log.STRING (fnd_log.level_procedure,
3303 g_module ||
3304 l_api_name,
3305 '4000: Leaving ' ||
3306 g_pkg_name ||
3307 '.' ||
3308 l_api_name
3309 );
3310 END IF;
3311
3312 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3313 x_return_status := g_ret_sts_unexp_error;
3314 okc_api.set_message (p_app_name => g_app_name,
3315 p_msg_name => g_unexpected_error,
3316 p_token1 => g_sqlcode_token,
3317 p_token1_value => SQLCODE,
3318 p_token2 => g_sqlerrm_token,
3319 p_token2_value => SQLERRM
3320 );
3321 END calculate_subline_price;
3322
3323 PROCEDURE calculate_topline_price (
3324 p_detail_rec IN oks_qp_pkg.input_details,
3325 x_price_details OUT NOCOPY oks_qp_pkg.price_details,
3326 x_modifier_details OUT NOCOPY qp_preq_grp.line_detail_tbl_type,
3327 x_price_break_details OUT NOCOPY oks_qp_pkg.g_price_break_tbl_type,
3328 x_return_status OUT NOCOPY VARCHAR2,
3329 x_msg_count OUT NOCOPY NUMBER,
3330 x_msg_data OUT NOCOPY VARCHAR2
3331 )
3332 IS
3333 l_api_name CONSTANT VARCHAR2 (30)
3334 := 'CALCULATE_TOPLINE_PRICE';
3335
3336 CURSOR l_subline_csr (
3337 p_cle_id NUMBER
3338 )
3339 IS
3340 SELECT ID,
3341 start_date,
3342 end_date,
3343 lse_id
3344 FROM okc_k_lines_b
3345 WHERE cle_id = p_cle_id
3346 AND lse_id IN (7, 9, 25)
3347 AND date_cancelled IS NULL --[llc]
3348 AND date_terminated IS NULL -- bug 5504157
3349 ORDER BY line_number;
3350
3351 l_input_detail_rec oks_qp_pkg.input_details;
3352 l_rail_rec oks_tax_util_pvt.ra_rec_type;
3353 l_k_det_rec k_details_rec;
3354 l_khdr_rec_in oks_contract_hdr_pub.khrv_rec_type;
3355 l_khdr_rec_out oks_contract_hdr_pub.khrv_rec_type;
3356 l_kln_rec_in oks_contract_line_pub.klnv_rec_type;
3357 l_kln_rec_out oks_contract_line_pub.klnv_rec_type;
3358 l_clev_rec_in okc_contract_pub.clev_rec_type;
3359 l_clev_rec_out okc_contract_pub.clev_rec_type;
3360 l_subs_item BOOLEAN := FALSE;
3361 l_clvl_name VARCHAR2 (240);
3362 l_clvl_desc VARCHAR2 (1000);
3363 l_tlvl_name VARCHAR2 (800);
3364 l_tlvl_desc VARCHAR2 (1000);
3365 l_line_no VARCHAR2 (300);
3366 l_status VARCHAR2 (30); --3912685
3367 BEGIN
3368 -- start debug log
3369 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3370 THEN
3371 fnd_log.STRING (fnd_log.level_procedure,
3372 g_module ||
3373 l_api_name,
3374 '100: Entered ' ||
3375 g_pkg_name ||
3376 '.' ||
3377 l_api_name
3378 );
3379 END IF;
3380
3381 x_return_status := g_ret_sts_success;
3382 l_input_detail_rec := p_detail_rec;
3383 -- Get top line details
3384 get_line_details (p_line_id => l_input_detail_rec.line_id,
3385 x_name => l_tlvl_name,
3386 x_description => l_tlvl_desc,
3387 x_return_status => x_return_status,
3388 x_msg_count => x_msg_count,
3389 x_msg_data => x_msg_data
3390 );
3391
3392 IF x_return_status <> g_ret_sts_success
3393 THEN
3394 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
3395 THEN
3396 fnd_log.STRING (fnd_log.level_statement,
3397 g_module ||
3398 l_api_name,
3399 '362:Get Line details ERROR in Topline'
3400 );
3401 END IF;
3402
3403 -- errorout('Get Line details ERROR in Topline');
3404 RAISE g_exc_error;
3405 END IF;
3406
3407 FOR l_subline_rec IN l_subline_csr (l_input_detail_rec.line_id)
3408 LOOP
3409 BEGIN
3410 IF l_subline_rec.lse_id IN (9, 25)
3411 THEN
3412 l_subs_item := is_subs_item (l_subline_rec.ID);
3413 ELSE
3414 l_subs_item := FALSE;
3415 END IF;
3416
3417 -- If its a Subscription item, skip iteration
3418 IF NOT l_subs_item
3419 THEN
3420 l_input_detail_rec.subline_id := l_subline_rec.ID;
3421 /** 3912685 **/
3422 check_pricing_eligibility
3423 (p_line_id => l_input_detail_rec.subline_id,
3424 x_status => l_status,
3425 x_return_status => x_return_status,
3426 x_msg_count => x_msg_count,
3427 x_msg_data => x_msg_data
3428 );
3429
3430 IF x_return_status <> g_ret_sts_success
3431 THEN
3432 RAISE g_exc_error;
3433 END IF;
3434
3435 IF l_status <> g_oks_success
3436 THEN
3437 x_price_details.status_code := l_status;
3438 RAISE g_exc_cant_price;
3439 END IF;
3440
3441 /** **/
3442 oks_qp_pkg.calc_price
3443 (p_detail_rec => l_input_detail_rec,
3444 x_price_details => x_price_details,
3445 x_modifier_details => x_modifier_details,
3446 x_price_break_details => x_price_break_details,
3447 x_return_status => x_return_status,
3448 x_msg_count => x_msg_count,
3449 x_msg_data => x_msg_data
3450 );
3451
3452 IF x_return_status <> g_ret_sts_success
3453 THEN
3454 IF (fnd_log.level_statement >=
3455 fnd_log.g_current_runtime_level
3456 )
3457 THEN
3458 fnd_log.STRING
3459 (fnd_log.level_statement,
3460 g_module ||
3461 l_api_name,
3462 '363:CALC PRICE ERROR after SAPI call in Topline'
3463 );
3464 END IF;
3465
3466 -- errorout('CALC PRICE ERROR after SAPI call in Topline');
3467 RAISE g_skip_exception;
3468 END IF;
3469
3470 l_rail_rec.amount :=
3471 NVL (x_price_details.serv_ext_amount, 0);
3472 l_rail_rec.tax_value := NULL;
3473 l_rail_rec.amount_includes_tax_flag := NULL;
3474 -- Calculate Tax
3475 oks_tax_util_pvt.get_tax
3476 (p_api_version => l_api_version,
3477 p_init_msg_list => l_init_msg_list,
3478 p_chr_id => l_input_detail_rec.chr_id,
3479 p_cle_id => l_input_detail_rec.subline_id,
3480 px_rail_rec => l_rail_rec,
3481 x_msg_count => x_msg_count,
3482 x_msg_data => x_msg_data,
3483 x_return_status => x_return_status
3484 );
3485
3486 IF x_return_status <> g_ret_sts_success
3487 THEN
3488 IF (fnd_log.level_statement >=
3489 fnd_log.g_current_runtime_level
3490 )
3491 THEN
3492 fnd_log.STRING (fnd_log.level_statement,
3493 g_module ||
3494 l_api_name,
3495 '364:TAX CALC ERROR in Topline x_return_status '||x_return_status
3496 );
3497 fnd_log.STRING (fnd_log.level_statement,
3498 g_module ||
3499 l_api_name,
3500 'x_msg_data '||x_msg_data
3501 );
3502 END IF;
3503
3504 -- errorout('TAX CALC ERROR in Topline');
3505 -- bug 5001677, even if tax error continue
3506 -- RAISE g_skip_exception;
3507 l_rail_rec.tax_value := 0;
3508 l_rail_rec.amount_includes_tax_flag := 'N';
3509 END IF;
3510
3511 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
3512 )
3513 THEN
3514 fnd_log.STRING (fnd_log.level_statement,
3515 g_module ||
3516 l_api_name,
3517 '366:l_rail_rec.TAX_VALUE = ' ||
3518 l_rail_rec.tax_value
3519 );
3520 fnd_log.STRING
3521 (fnd_log.level_statement,
3522 g_module ||
3523 l_api_name,
3524 '367:l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG = ' ||
3525 l_rail_rec.amount_includes_tax_flag
3526 );
3527 END IF;
3528
3529 --errorout('l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG = '||l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG );
3530 --errorout('l_rail_rec.TAX_VALUE = '|| l_rail_rec.TAX_VALUE );
3531 --errorout('l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG = '||l_rail_rec.AMOUNT_INCLUDES_TAX_FLAG );
3532 l_kln_rec_in.cle_id := l_input_detail_rec.subline_id;
3533 l_kln_rec_in.dnz_chr_id := l_input_detail_rec.chr_id;
3534 l_kln_rec_in.tax_inclusive_yn :=
3535 l_rail_rec.amount_includes_tax_flag;
3536
3537 IF l_rail_rec.amount_includes_tax_flag = 'N'
3538 THEN
3539 l_kln_rec_in.tax_amount := NVL (l_rail_rec.tax_value, 0);
3540 ELSE
3541 l_kln_rec_in.tax_amount := 0;
3542 END IF;
3543
3544 IF x_price_details.prod_price_list_id IS NOT NULL
3545 THEN
3546 l_kln_rec_in.prod_price :=
3547 x_price_details.prod_price_list_id;
3548 END IF;
3549
3550 IF x_price_details.serv_price_list_id IS NOT NULL
3551 THEN
3552 l_kln_rec_in.service_price :=
3553 x_price_details.serv_price_list_id;
3554 END IF;
3555
3556 l_kln_rec_in.clvl_list_price :=
3557 x_price_details.prod_list_unit_price;
3558 l_kln_rec_in.clvl_quantity := x_price_details.prod_priced_qty;
3559 l_kln_rec_in.clvl_extended_amt :=
3560 x_price_details.prod_ext_amount;
3561 l_kln_rec_in.toplvl_operand_code :=
3562 x_price_details.serv_operator;
3563 l_kln_rec_in.toplvl_operand_val := x_price_details.serv_operand;
3564 l_kln_rec_in.clvl_uom_code := x_price_details.prod_priced_uom;
3565 l_kln_rec_in.toplvl_quantity := x_price_details.serv_qty;
3566 l_kln_rec_in.toplvl_uom_code := x_price_details.serv_priced_uom;
3567 l_kln_rec_in.toplvl_adj_price :=
3568 x_price_details.serv_adj_unit_price;
3569 -- GCHADHA --
3570 -- BUG 4015739 --
3571 -- 17-NOV-2004 --
3572 l_kln_rec_in.status_text :=
3573 SUBSTR (NVL (x_price_details.status_text, g_sts_txt_success),
3574 1,
3575 450
3576 );
3577 --l_kln_rec_in.status_text := NVL(x_price_details.status_text,G_STS_TXT_SUCCESS);
3578 -- END GCHADHA --
3579 l_kln_rec_in.toplvl_price_qty :=
3580 x_price_details.serv_priced_qty;
3581 get_k_details (p_id => l_input_detail_rec.subline_id,
3582 p_type => g_oks_line,
3583 x_k_det_rec => l_k_det_rec
3584 );
3585 l_kln_rec_in.ID := l_k_det_rec.ID;
3586 l_kln_rec_in.object_version_number :=
3587 l_k_det_rec.object_version_number;
3588 oks_contract_line_pub.update_line
3589 (p_api_version => l_api_version,
3590 p_init_msg_list => l_init_msg_list,
3591 x_return_status => x_return_status,
3592 x_msg_count => x_msg_count,
3593 x_msg_data => x_msg_data,
3594 p_klnv_rec => l_kln_rec_in,
3595 x_klnv_rec => l_kln_rec_out,
3596 p_validate_yn => 'N'
3597 );
3598
3599 IF x_return_status <> g_ret_sts_success
3600 THEN
3601 IF (fnd_log.level_statement >=
3602 fnd_log.g_current_runtime_level
3603 )
3604 THEN
3605 fnd_log.STRING
3606 (fnd_log.level_statement,
3607 g_module ||
3608 l_api_name,
3609 '368:Update line details at Calc Subscription'
3610 );
3611 END IF;
3612
3613 --errorout('Update line details at Calc Subscription');
3614 RAISE g_skip_exception;
3615 END IF;
3616
3617 l_clev_rec_in.ID := l_input_detail_rec.subline_id;
3618 l_clev_rec_in.line_list_price :=
3619 x_price_details.serv_list_unit_price;
3620 l_clev_rec_in.price_negotiated :=
3621 x_price_details.serv_ext_amount;
3622 l_clev_rec_in.price_unit :=
3623 NVL (x_price_details.serv_adj_unit_price,
3624 x_price_details.serv_list_unit_price);
3625 --bug 3360423 list_unit_price will be stored in the database instead of adj_unit_price
3626 get_k_details (p_id => l_input_detail_rec.subline_id,
3627 p_type => g_okc_line,
3628 x_k_det_rec => l_k_det_rec
3629 );
3630 l_clev_rec_in.object_version_number :=
3631 l_k_det_rec.object_version_number;
3632 okc_contract_pub.update_contract_line
3633 (p_api_version => l_api_version,
3634 p_init_msg_list => l_init_msg_list,
3635 x_return_status => x_return_status,
3636 x_msg_count => x_msg_count,
3637 x_msg_data => x_msg_data,
3638 p_clev_rec => l_clev_rec_in,
3639 x_clev_rec => l_clev_rec_out
3640 );
3641
3642 IF x_return_status <> g_ret_sts_success
3643 THEN
3644 IF (fnd_log.level_statement >=
3645 fnd_log.g_current_runtime_level
3646 )
3647 THEN
3648 fnd_log.STRING
3649 (fnd_log.level_statement,
3650 g_module ||
3651 l_api_name,
3652 '369:Contract line updation ERROR in Subline Price - Topline Price'
3653 );
3654 END IF;
3655
3656 -- errorout ('Contract line updation ERROR in Subline Price - Topline Price');
3657 RAISE g_skip_exception;
3658 END IF;
3659
3660 -- Clear the record so as to reuse it
3661 l_clev_rec_in.ID := okc_api.g_miss_num;
3662 --l_clev_rec_in.chr_id := OKC_API.G_MISS_NUM;
3663 --l_clev_rec_in.cle_id := OKC_API.G_MISS_NUM;
3664 l_clev_rec_in.price_negotiated := okc_api.g_miss_num;
3665 l_clev_rec_in.price_unit := okc_api.g_miss_num;
3666 -- Get covered line details
3667 get_line_details (p_line_id => l_input_detail_rec.subline_id,
3668 x_name => l_clvl_name,
3669 x_description => l_clvl_desc,
3670 x_return_status => x_return_status,
3671 x_msg_count => x_msg_count,
3672 x_msg_data => x_msg_data
3673 );
3674 g_pricing_status_tbl (g_index).service_name := l_tlvl_name;
3675 g_pricing_status_tbl (g_index).coverage_level_name :=
3676 l_clvl_name;
3677 g_pricing_status_tbl (g_index).status_code :=
3678 g_sts_code_success;
3679 l_line_no :=
3680 get_line_no (l_input_detail_rec.subline_id);
3681
3682 IF l_line_no IS NULL
3683 THEN
3684 g_pricing_status_tbl (g_index).status_text :=
3685 NVL (x_price_details.status_text, g_sts_txt_success);
3686 ELSE
3687 -- bug 4730011
3688 fnd_message.set_name ('OKS', 'OKS_LINE_REPRICE_SUCCESS');
3689 fnd_message.set_token ('LINENO', l_line_no);
3690 g_pricing_status_tbl (g_index).status_text := fnd_message.get;
3691 -- l_line_no ||' ' ||NVL (x_price_details.status_text, g_sts_txt_success);
3692 END IF;
3693
3694 g_index := g_index +
3695 1;
3696 END IF; -- Skip iteration if its a Subscription item
3697
3698 -- end debug log
3699 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3700 THEN
3701 fnd_log.STRING (fnd_log.level_procedure,
3702 g_module ||
3703 l_api_name,
3704 '1000: Leaving ' ||
3705 g_pkg_name ||
3706 '.' ||
3707 l_api_name
3708 );
3709 END IF;
3710 EXCEPTION
3711 WHEN g_exc_cant_price
3712 THEN
3713 -- end debug log
3714 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3715 )
3716 THEN
3717 fnd_log.STRING (fnd_log.level_procedure,
3718 g_module ||
3719 l_api_name,
3720 '7000: Leaving ' ||
3721 g_pkg_name ||
3722 '.' ||
3723 l_api_name
3724 );
3725 END IF;
3726
3727 -- Get covered line details
3728 get_line_details (p_line_id => l_input_detail_rec.subline_id,
3729 x_name => l_clvl_name,
3730 x_description => l_clvl_desc,
3731 x_return_status => x_return_status,
3732 x_msg_count => x_msg_count,
3733 x_msg_data => x_msg_data
3734 );
3735 g_pricing_status_tbl (g_index).service_name := l_tlvl_name;
3736 g_pricing_status_tbl (g_index).coverage_level_name :=
3737 l_clvl_name;
3738 g_pricing_status_tbl (g_index).status_code :=
3739 g_sts_code_success;
3740
3741 IF x_price_details.status_code = g_partial_billed
3742 or
3743 x_price_details.status_code = g_fully_billed
3744 THEN
3745 g_pricing_status_tbl (g_index).status_text := g_billed_line;
3746 ELSE
3747 g_pricing_status_tbl (g_index).status_text :=
3748 g_sts_txt_success;
3749 END IF;
3750
3751 g_index := g_index +
3752 1;
3753
3754 IF l_status = g_fully_billed
3755 OR l_status = g_partial_billed
3756 THEN
3757 x_price_details.status_code := g_billed;
3758 END IF;
3759 WHEN g_skip_exception
3760 THEN
3761 -- end debug log
3762 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3763 )
3764 THEN
3765 fnd_log.STRING (fnd_log.level_procedure,
3766 g_module ||
3767 l_api_name,
3768 '9000: Leaving ' ||
3769 g_pkg_name ||
3770 '.' ||
3771 l_api_name
3772 );
3773 END IF;
3774
3775 -- Get covered line details
3776 get_line_details (p_line_id => l_input_detail_rec.subline_id,
3777 x_name => l_clvl_name,
3778 x_description => l_clvl_desc,
3779 x_return_status => x_return_status,
3780 x_msg_count => x_msg_count,
3781 x_msg_data => x_msg_data
3782 );
3783 g_pricing_status_tbl (g_index).service_name := l_tlvl_name;
3784 g_pricing_status_tbl (g_index).coverage_level_name :=
3785 l_clvl_name;
3786 g_pricing_status_tbl (g_index).status_code := g_sts_code_error;
3787 l_line_no :=
3788 get_line_no (l_input_detail_rec.subline_id);
3789
3790 IF l_line_no IS NULL
3791 THEN
3792 g_pricing_status_tbl (g_index).status_text :=
3793 NVL (x_price_details.status_text, g_sts_txt_error);
3794 ELSE
3795 -- bug 4730011
3796 fnd_message.set_name ('OKS', 'OKS_LINE_REPRICE_SUCCESS');
3797 fnd_message.set_token ('LINENO', l_line_no);
3798 g_pricing_status_tbl (g_index).status_text := fnd_message.get;
3799 -- l_line_no ||' ' ||NVL (x_price_details.status_text, g_sts_txt_error);
3800 END IF;
3801
3802 g_index := g_index +
3803 1;
3804 END;
3805 END LOOP;
3806
3807 l_clev_rec_in.ID := l_input_detail_rec.line_id;
3808 l_clev_rec_in.price_negotiated :=
3809 get_amounts (p_id => l_input_detail_rec.line_id,
3810 p_level => g_line_level);
3811 get_k_details (p_id => l_input_detail_rec.line_id,
3812 p_type => g_okc_line,
3813 x_k_det_rec => l_k_det_rec
3814 );
3815 l_clev_rec_in.object_version_number := l_k_det_rec.object_version_number;
3816 okc_contract_pub.update_contract_line
3817 (p_api_version => l_api_version,
3818 p_init_msg_list => l_init_msg_list,
3819 x_return_status => x_return_status,
3820 x_msg_count => x_msg_count,
3821 x_msg_data => x_msg_data,
3822 p_clev_rec => l_clev_rec_in,
3823 x_clev_rec => l_clev_rec_out
3824 );
3825
3826 IF x_return_status <> g_ret_sts_success
3827 THEN
3828 RAISE g_exc_error;
3829 END IF;
3830
3831 -- end debug log
3832 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3833 THEN
3834 fnd_log.STRING (fnd_log.level_procedure,
3835 g_module ||
3836 l_api_name,
3837 '1000: Leaving ' ||
3838 g_pkg_name ||
3839 '.' ||
3840 l_api_name
3841 );
3842 END IF;
3843 EXCEPTION
3844 WHEN g_exc_error
3845 THEN
3846 -- end debug log
3847 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3848 THEN
3849 fnd_log.STRING (fnd_log.level_procedure,
3850 g_module ||
3851 l_api_name,
3852 '2000: Leaving ' ||
3853 g_pkg_name ||
3854 '.' ||
3855 l_api_name
3856 );
3857 END IF;
3858
3859 NULL;
3860 WHEN OTHERS
3861 THEN
3862 -- end debug log
3863 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3864 THEN
3865 fnd_log.STRING (fnd_log.level_procedure,
3866 g_module ||
3867 l_api_name,
3868 '4000: Leaving ' ||
3869 g_pkg_name ||
3870 '.' ||
3871 l_api_name
3872 );
3873 END IF;
3874
3875 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3876 x_return_status := g_ret_sts_unexp_error;
3877 okc_api.set_message (p_app_name => g_app_name,
3878 p_msg_name => g_unexpected_error,
3879 p_token1 => g_sqlcode_token,
3880 p_token1_value => SQLCODE,
3881 p_token2 => g_sqlerrm_token,
3882 p_token2_value => SQLERRM
3883 );
3884 END calculate_topline_price;
3885
3886 PROCEDURE calculate_hdr_price (
3887 p_detail_rec IN oks_qp_pkg.input_details,
3888 x_price_details OUT NOCOPY oks_qp_pkg.price_details,
3889 x_modifier_details OUT NOCOPY qp_preq_grp.line_detail_tbl_type,
3890 x_price_break_details OUT NOCOPY oks_qp_pkg.g_price_break_tbl_type,
3891 x_return_status OUT NOCOPY VARCHAR2,
3892 x_msg_count OUT NOCOPY NUMBER,
3893 x_msg_data OUT NOCOPY VARCHAR2
3894 )
3895 IS
3896 l_api_name CONSTANT VARCHAR2 (30)
3897 := 'CALCULATE_HDR_PRICE';
3898
3899 CURSOR l_topline_csr (
3900 p_chr_id NUMBER
3901 )
3902 IS
3903 SELECT ID,
3904 start_date,
3905 end_date,
3906 lse_id
3907 FROM okc_k_lines_b
3908 WHERE chr_id = p_chr_id
3909 AND lse_id IN (1, 14, 19, 46)
3910 AND date_cancelled IS NULL --[llc] bug 4653406
3911 AND date_terminated IS NULL -- bug 5504157
3912 ORDER BY line_number;
3913
3914 l_input_details oks_qp_pkg.input_details;
3915 l_chrv_rec_in okc_contract_pub.chrv_rec_type;
3916 l_chrv_rec_out okc_contract_pub.chrv_rec_type;
3917 l_k_det_rec k_details_rec;
3918 BEGIN
3919 -- start debug log
3920 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
3921 THEN
3922 fnd_log.STRING (fnd_log.level_procedure,
3923 g_module ||
3924 l_api_name,
3925 '100: Entered ' ||
3926 g_pkg_name ||
3927 '.' ||
3928 l_api_name
3929 );
3930 END IF;
3931
3932 x_return_status := g_ret_sts_success;
3933
3934 FOR l_topline_rec IN l_topline_csr (p_detail_rec.chr_id)
3935 LOOP
3936 BEGIN
3937 IF l_topline_rec.lse_id = g_subscription
3938 THEN
3939 l_input_details.chr_id := p_detail_rec.chr_id;
3940 l_input_details.line_id := l_topline_rec.ID;
3941 l_input_details.subline_id := NULL;
3942 l_input_details.intent := g_subsc_reg_pricing;
3943 l_input_details.currency := p_detail_rec.currency;
3944 l_input_details.usage_qty := NULL;
3945 l_input_details.usage_uom_code := NULL;
3946 l_input_details.asking_unit_price := NULL;
3947 calculate_subscription_price
3948 (p_detail_rec => l_input_details,
3949 x_price_details => x_price_details,
3950 x_modifier_details => x_modifier_details,
3951 x_price_break_details => x_price_break_details,
3952 x_return_status => x_return_status,
3953 x_msg_count => x_msg_count,
3954 x_msg_data => x_msg_data
3955 );
3956
3957 IF x_return_status <> g_ret_sts_success
3958 THEN
3959 IF (fnd_log.level_statement >=
3960 fnd_log.g_current_runtime_level
3961 )
3962 THEN
3963 fnd_log.STRING
3964 (fnd_log.level_statement,
3965 g_module ||
3966 l_api_name,
3967 '370:CALC SUBSCRIPTION ERROR at Calc Hdr call'
3968 );
3969 END IF;
3970
3971 -- errorout('CALC SUBSCRIPTION ERROR at Calc Hdr call');
3972 RAISE g_skip_exception;
3973 END IF;
3974 ELSE
3975 l_input_details.chr_id := p_detail_rec.chr_id;
3976 l_input_details.line_id := l_topline_rec.ID;
3977 l_input_details.subline_id := NULL;
3978 l_input_details.intent := g_top_line_pricing;
3979 l_input_details.currency := p_detail_rec.currency;
3980 l_input_details.usage_qty := NULL;
3981 l_input_details.usage_uom_code := NULL;
3982 l_input_details.asking_unit_price := NULL;
3983 calculate_topline_price
3984 (p_detail_rec => l_input_details,
3985 x_price_details => x_price_details,
3986 x_modifier_details => x_modifier_details,
3987 x_price_break_details => x_price_break_details,
3988 x_return_status => x_return_status,
3989 x_msg_count => x_msg_count,
3990 x_msg_data => x_msg_data
3991 );
3992
3993 IF x_return_status <> g_ret_sts_success
3994 THEN
3995 IF (fnd_log.level_statement >=
3996 fnd_log.g_current_runtime_level
3997 )
3998 THEN
3999 fnd_log.STRING
4000 (fnd_log.level_statement,
4001 g_module ||
4002 l_api_name,
4003 '371:CALC TOPLINE ERROR at Calc Hdr call'
4004 );
4005 END IF;
4006
4007 -- errorout('CALC TOPLINE ERROR at Calc Hdr call');
4008 RAISE g_skip_exception;
4009 END IF;
4010 END IF;
4011
4012 -- end debug log
4013 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4014 THEN
4015 fnd_log.STRING (fnd_log.level_procedure,
4016 g_module ||
4017 l_api_name,
4018 '1000: Leaving ' ||
4019 g_pkg_name ||
4020 '.' ||
4021 l_api_name
4022 );
4023 END IF;
4024 EXCEPTION
4025 WHEN g_skip_exception
4026 THEN
4027 -- end debug log
4028 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level
4029 )
4030 THEN
4031 fnd_log.STRING (fnd_log.level_procedure,
4032 g_module ||
4033 l_api_name,
4034 '9000: Leaving ' ||
4035 g_pkg_name ||
4036 '.' ||
4037 l_api_name
4038 );
4039 END IF;
4040
4041 NULL;
4042 END;
4043 END LOOP;
4044
4045 l_chrv_rec_in.ID := p_detail_rec.chr_id;
4046 l_chrv_rec_in.estimated_amount :=
4047 get_amounts (p_id => p_detail_rec.chr_id,
4048 p_level => g_hdr_level);
4049 get_k_details (p_id => p_detail_rec.chr_id,
4050 p_type => g_okc_hdr,
4051 x_k_det_rec => l_k_det_rec
4052 );
4053 l_chrv_rec_in.object_version_number := l_k_det_rec.object_version_number;
4054 okc_contract_pub.update_contract_header
4055 (p_api_version => l_api_version,
4056 p_init_msg_list => l_init_msg_list,
4057 x_return_status => x_return_status,
4058 x_msg_count => x_msg_count,
4059 x_msg_data => x_msg_data,
4060 p_restricted_update => okc_api.g_false,
4061 p_chrv_rec => l_chrv_rec_in,
4062 x_chrv_rec => l_chrv_rec_out
4063 );
4064
4065 IF x_return_status <> g_ret_sts_success
4066 THEN
4067 RAISE g_exc_error;
4068 END IF;
4069
4070 -- end debug log
4071 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4072 THEN
4073 fnd_log.STRING (fnd_log.level_procedure,
4074 g_module ||
4075 l_api_name,
4076 '1000: Leaving ' ||
4077 g_pkg_name ||
4078 '.' ||
4079 l_api_name
4080 );
4081 END IF;
4082 EXCEPTION
4083 WHEN g_exc_error
4084 THEN
4085 -- end debug log
4086 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4087 THEN
4088 fnd_log.STRING (fnd_log.level_procedure,
4089 g_module ||
4090 l_api_name,
4091 '2000: Leaving ' ||
4092 g_pkg_name ||
4093 '.' ||
4094 l_api_name
4095 );
4096 END IF;
4097
4098 NULL;
4099 WHEN OTHERS
4100 THEN
4101 -- end debug log
4102 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4103 THEN
4104 fnd_log.STRING (fnd_log.level_procedure,
4105 g_module ||
4106 l_api_name,
4107 '4000: Leaving ' ||
4108 g_pkg_name ||
4109 '.' ||
4110 l_api_name
4111 );
4112 END IF;
4113
4114 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4115 x_return_status := g_ret_sts_unexp_error;
4116 okc_api.set_message (p_app_name => g_app_name,
4117 p_msg_name => g_unexpected_error,
4118 p_token1 => g_sqlcode_token,
4119 p_token1_value => SQLCODE,
4120 p_token2 => g_sqlerrm_token,
4121 p_token2_value => SQLERRM
4122 );
4123 END calculate_hdr_price;
4124
4125 PROCEDURE compute_price (
4126 p_api_version IN NUMBER,
4127 p_init_msg_list IN VARCHAR2,
4128 p_detail_rec IN oks_qp_pkg.input_details,
4129 x_price_details OUT NOCOPY oks_qp_pkg.price_details,
4130 x_modifier_details OUT NOCOPY qp_preq_grp.line_detail_tbl_type,
4131 x_price_break_details OUT NOCOPY oks_qp_pkg.g_price_break_tbl_type,
4132 x_return_status OUT NOCOPY VARCHAR2,
4133 x_msg_count OUT NOCOPY NUMBER,
4134 x_msg_data OUT NOCOPY VARCHAR2
4135 )
4136 IS
4137 l_api_name CONSTANT VARCHAR2 (30)
4138 := 'compute_price';
4139 l_input_detail_rec oks_qp_pkg.input_details
4140 := p_detail_rec;
4141 l_k_det_rec k_details_rec;
4142 l_chrv_rec_in okc_contract_pub.chrv_rec_type;
4143 l_chrv_rec_out okc_contract_pub.chrv_rec_type;
4144 l_clev_rec_in okc_contract_pub.clev_rec_type;
4145 l_clev_rec_out okc_contract_pub.clev_rec_type;
4146 BEGIN
4147 -- start debug log
4148 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4149 THEN
4150 fnd_log.STRING (fnd_log.level_procedure,
4151 g_module ||
4152 l_api_name,
4153 '100: Entered ' ||
4154 g_pkg_name ||
4155 '.' ||
4156 l_api_name
4157 );
4158 fnd_log.STRING
4159 (fnd_log.level_procedure,
4160 g_module ||
4161 l_api_name,
4162 '100: ***************** Parameters : ******************'
4163 );
4164 fnd_log.STRING (fnd_log.level_procedure,
4165 g_module ||
4166 l_api_name,
4167 '100:CHR_ID = ' ||
4168 p_detail_rec.chr_id
4169 );
4170 fnd_log.STRING (fnd_log.level_procedure,
4171 g_module ||
4172 l_api_name,
4173 '100:LINE_ID = ' ||
4174 p_detail_rec.line_id
4175 );
4176 fnd_log.STRING (fnd_log.level_procedure,
4177 g_module ||
4178 l_api_name,
4179 '100:SUBLINE_ID = ' ||
4180 p_detail_rec.subline_id
4181 );
4182 fnd_log.STRING (fnd_log.level_procedure,
4183 g_module ||
4184 l_api_name,
4185 '100:INTENT = ' ||
4186 p_detail_rec.intent
4187 );
4188 fnd_log.STRING (fnd_log.level_procedure,
4189 g_module ||
4190 l_api_name,
4191 '100:CURRENCY = ' ||
4192 p_detail_rec.currency
4193 );
4194 fnd_log.STRING (fnd_log.level_procedure,
4195 g_module ||
4196 l_api_name,
4197 '100:Usage Qty = ' ||
4198 p_detail_rec.usage_qty
4199 );
4200 fnd_log.STRING (fnd_log.level_procedure,
4201 g_module ||
4202 l_api_name,
4203 '100:Usage UOM Code = ' ||
4204 p_detail_rec.usage_uom_code
4205 );
4206 fnd_log.STRING (fnd_log.level_procedure,
4207 g_module ||
4208 l_api_name,
4209 '100:Asking Unit Price = ' ||
4210 p_detail_rec.asking_unit_price
4211 );
4212 END IF;
4213
4214 --errorout ('********** Entered Compute Price ********');
4215 --errorout ('CHR_ID = ' || p_detail_rec.chr_id);
4216 --errorout ('LINE_ID = ' || p_detail_rec.line_id);
4217 --errorout ('SUBLINE_ID = ' || p_detail_rec.subline_id);
4218 --errorout ('INTENT = ' || p_detail_rec.intent);
4219 --errorout ('CURRENCY = ' || p_detail_rec.currency);
4220 --errorout ('Usage Qty = ' || p_detail_rec.usage_qty);
4221 --errorout ('Usage UOM Code = ' || p_detail_rec.usage_uom_code);
4222 --errorout ('AskingUnit Price = ' || p_detail_rec.asking_unit_price);
4223
4224 -- Make a save point, in case of error rollback
4225 DBMS_TRANSACTION.SAVEPOINT ('COMPUTE_PRICE');
4226
4227 IF fnd_api.to_boolean (p_init_msg_list)
4228 THEN
4229 fnd_msg_pub.initialize;
4230 END IF;
4231
4232 x_return_status := g_ret_sts_success;
4233 -- Always initialize the Pricing message table
4234 g_pricing_status_tbl.DELETE;
4235 g_index := 0;
4236 fnd_message.set_name ('OKS', 'OKS_SUCCESS');
4237 g_sts_txt_success := fnd_message.get;
4238 fnd_message.set_name ('OKS', 'OKS_ERROR');
4239 g_sts_txt_error := fnd_message.get;
4240 fnd_message.set_name ('OKS', 'OKS_PRICE_STATUS_MAN_ADJ');
4241 g_manual_adj_price := fnd_message.get;
4242 fnd_message.set_name ('OKS', 'OKS_BILLED_LINE');
4243 g_billed_line := fnd_message.get;
4244
4245 IF p_detail_rec.intent = g_header_pricing
4246 THEN
4247 IF p_detail_rec.chr_id IS NOT NULL
4248 THEN
4249 calculate_hdr_price
4250 (p_detail_rec => p_detail_rec,
4251 x_price_details => x_price_details,
4252 x_modifier_details => x_modifier_details,
4253 x_price_break_details => x_price_break_details,
4254 x_return_status => x_return_status,
4255 x_msg_count => x_msg_count,
4256 x_msg_data => x_msg_data
4257 );
4258
4259 IF x_return_status <> g_ret_sts_success
4260 THEN
4261 RAISE g_exc_error;
4262 END IF;
4263 ELSE
4264 okc_api.set_message (p_app_name => g_app_name,
4265 p_msg_name => g_required_value,
4266 p_token1 => g_invalid_value,
4267 p_token1_value => 'CHR_ID'
4268 );
4269
4270 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4271 THEN
4272 fnd_log.STRING (fnd_log.level_statement,
4273 g_module ||
4274 l_api_name,
4275 '372:Contract header id is NULL'
4276 );
4277 END IF;
4278
4279 -- errorout ('Contract header id is NULL');
4280 x_return_status := g_ret_sts_error;
4281 RAISE g_exc_error;
4282 END IF;
4283 ELSIF p_detail_rec.intent = g_top_line_pricing
4284 THEN
4285 IF p_detail_rec.line_id IS NOT NULL
4286 THEN
4287 IF p_detail_rec.chr_id IS NULL
4288 THEN
4289 get_id (p_line_id => p_detail_rec.line_id,
4290 x_chr_id => l_input_detail_rec.chr_id,
4291 x_topline_id => l_input_detail_rec.subline_id,
4292 x_return_status => x_return_status
4293 );
4294
4295 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
4296 )
4297 THEN
4298 fnd_log.STRING (fnd_log.level_statement,
4299 g_module ||
4300 l_api_name,
4301 '373:Get_id chr_id in CompP = ' ||
4302 l_input_detail_rec.chr_id
4303 );
4304 fnd_log.STRING (fnd_log.level_statement,
4305 g_module ||
4306 l_api_name,
4307 '374:Get_id topline_id in CompP = ' ||
4308 l_input_detail_rec.line_id
4309 );
4310 END IF;
4311
4312 --errorout ('Get_id chr_id in CompP = ' || l_input_detail_rec.chr_id);
4313 --errorout ('Get_id topline_id in CompP = ' || l_input_detail_rec.line_id);
4314 IF x_return_status <> g_ret_sts_success
4315 THEN
4316 IF (fnd_log.level_statement >=
4317 fnd_log.g_current_runtime_level
4318 )
4319 THEN
4320 fnd_log.STRING (fnd_log.level_statement,
4321 g_module ||
4322 l_api_name,
4323 '375:Get_id return status = ' ||
4324 x_return_status
4325 );
4326 END IF;
4327
4328 --errorout ('Get_id return status = ' || x_return_status);
4329 RAISE g_exc_error;
4330 END IF;
4331 END IF;
4332
4333 calculate_topline_price
4334 (p_detail_rec => l_input_detail_rec,
4335 x_price_details => x_price_details,
4336 x_modifier_details => x_modifier_details,
4337 x_price_break_details => x_price_break_details,
4338 x_return_status => x_return_status,
4339 x_msg_count => x_msg_count,
4340 x_msg_data => x_msg_data
4341 );
4342
4343 IF x_return_status <> g_ret_sts_success
4344 THEN
4345 RAISE g_exc_error;
4346 END IF;
4347
4348 l_chrv_rec_in.ID := l_input_detail_rec.chr_id;
4349 l_chrv_rec_in.estimated_amount :=
4350 get_amounts (p_id => l_input_detail_rec.chr_id,
4351 p_level => g_hdr_level);
4352 get_k_details (p_id => l_input_detail_rec.chr_id,
4353 p_type => g_okc_hdr,
4354 x_k_det_rec => l_k_det_rec
4355 );
4356 l_chrv_rec_in.object_version_number :=
4357 l_k_det_rec.object_version_number;
4358 okc_contract_pub.update_contract_header
4359 (p_api_version => l_api_version,
4360 p_init_msg_list => l_init_msg_list,
4361 x_return_status => x_return_status,
4362 x_msg_count => x_msg_count,
4363 x_msg_data => x_msg_data,
4364 p_restricted_update => okc_api.g_false,
4365 p_chrv_rec => l_chrv_rec_in,
4366 x_chrv_rec => l_chrv_rec_out
4367 );
4368
4369 IF x_return_status <> g_ret_sts_success
4370 THEN
4371 RAISE g_exc_error;
4372 END IF;
4373 ELSE
4374 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4375 THEN
4376 fnd_log.STRING (fnd_log.level_statement,
4377 g_module ||
4378 l_api_name,
4379 '376:Top Line id is NULL'
4380 );
4381 END IF;
4382
4383 --errorout ('Top Line id is NULL');
4384 okc_api.set_message (p_app_name => g_app_name,
4385 p_msg_name => g_required_value,
4386 p_token1 => g_invalid_value,
4387 p_token1_value => 'CLE_ID'
4388 );
4389 x_return_status := g_ret_sts_error;
4390 RAISE g_exc_error;
4391 END IF;
4392 ELSIF p_detail_rec.intent = g_sub_line_pricing
4393 THEN
4394 IF p_detail_rec.subline_id IS NOT NULL
4395 THEN
4396 IF ( p_detail_rec.line_id IS NULL
4397 OR p_detail_rec.chr_id IS NULL
4398 )
4399 THEN
4400 get_id (p_line_id => p_detail_rec.subline_id,
4401 x_chr_id => l_input_detail_rec.chr_id,
4402 x_topline_id => l_input_detail_rec.line_id,
4403 x_return_status => x_return_status
4404 );
4405
4406 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
4407 )
4408 THEN
4409 fnd_log.STRING (fnd_log.level_statement,
4410 g_module ||
4411 l_api_name,
4412 '377:Get_id topline_id = ' ||
4413 l_input_detail_rec.line_id
4414 );
4415 END IF;
4416
4417 --errorout ('Get_id topline_id = ' || l_input_detail_rec.line_id);
4418 IF x_return_status <> g_ret_sts_success
4419 THEN
4420 IF (fnd_log.level_statement >=
4421 fnd_log.g_current_runtime_level
4422 )
4423 THEN
4424 fnd_log.STRING (fnd_log.level_statement,
4425 g_module ||
4426 l_api_name,
4427 '378:Get_id return status = ' ||
4428 x_return_status
4429 );
4430 END IF;
4431
4432 --errorout ('Get_id return status = ' || x_return_status);
4433 RAISE g_exc_error;
4434 END IF;
4435 END IF;
4436
4437 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4438 THEN
4439 fnd_log.STRING (fnd_log.level_statement,
4440 g_module ||
4441 l_api_name,
4442 '379:here is Sub line'
4443 );
4444 END IF;
4445
4446 --errorout ('here is Sub line');
4447 calculate_subline_price
4448 (p_detail_rec => l_input_detail_rec,
4449 x_price_details => x_price_details,
4450 x_modifier_details => x_modifier_details,
4451 x_price_break_details => x_price_break_details,
4452 x_return_status => x_return_status,
4453 x_msg_count => x_msg_count,
4454 x_msg_data => x_msg_data
4455 );
4456
4457 IF x_return_status <> g_ret_sts_success
4458 THEN
4459 RAISE g_exc_error;
4460 END IF;
4461
4462 l_clev_rec_in.ID := l_input_detail_rec.line_id;
4463 l_clev_rec_in.price_negotiated :=
4464 get_amounts (p_id => l_input_detail_rec.line_id,
4465 p_level => g_line_level);
4466 get_k_details (p_id => l_input_detail_rec.line_id,
4467 p_type => g_okc_line,
4468 x_k_det_rec => l_k_det_rec
4469 );
4470 l_clev_rec_in.object_version_number :=
4471 l_k_det_rec.object_version_number;
4472 okc_contract_pub.update_contract_line
4473 (p_api_version => l_api_version,
4474 p_init_msg_list => l_init_msg_list,
4475 x_return_status => x_return_status,
4476 x_msg_count => x_msg_count,
4477 x_msg_data => x_msg_data,
4478 p_clev_rec => l_clev_rec_in,
4479 x_clev_rec => l_clev_rec_out
4480 );
4481
4482 IF x_return_status <> g_ret_sts_success
4483 THEN
4484 RAISE g_exc_error;
4485 END IF;
4486
4487 l_chrv_rec_in.ID := l_input_detail_rec.chr_id;
4488 l_chrv_rec_in.estimated_amount :=
4489 get_amounts (p_id => l_input_detail_rec.chr_id,
4490 p_level => g_hdr_level);
4491 get_k_details (p_id => l_input_detail_rec.chr_id,
4492 p_type => g_okc_hdr,
4493 x_k_det_rec => l_k_det_rec
4494 );
4495 l_chrv_rec_in.object_version_number :=
4496 l_k_det_rec.object_version_number;
4497 okc_contract_pub.update_contract_header
4498 (p_api_version => l_api_version,
4499 p_init_msg_list => l_init_msg_list,
4500 x_return_status => x_return_status,
4501 x_msg_count => x_msg_count,
4502 x_msg_data => x_msg_data,
4503 p_restricted_update => okc_api.g_false,
4504 p_chrv_rec => l_chrv_rec_in,
4505 x_chrv_rec => l_chrv_rec_out
4506 );
4507
4508 IF x_return_status <> g_ret_sts_success
4509 THEN
4510 RAISE g_exc_error;
4511 END IF;
4512 ELSE
4513 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4514 THEN
4515 fnd_log.STRING (fnd_log.level_statement,
4516 g_module ||
4517 l_api_name,
4518 '380:Sub line id is NULL'
4519 );
4520 END IF;
4521
4522 --errorout ('Sub line id is NULL');
4523 okc_api.set_message (p_app_name => g_app_name,
4524 p_msg_name => g_required_value,
4525 p_token1 => g_invalid_value,
4526 p_token1_value => 'LINE_ID'
4527 );
4528 x_return_status := g_ret_sts_error;
4529 RAISE g_exc_error;
4530 END IF;
4531 ELSIF p_detail_rec.intent IN (g_subsc_reg_pricing, g_subsc_ovr_pricing)
4532 THEN
4533 IF p_detail_rec.line_id IS NOT NULL
4534 THEN
4535 IF p_detail_rec.chr_id IS NULL
4536 THEN
4537 get_id (p_line_id => p_detail_rec.line_id,
4538 x_chr_id => l_input_detail_rec.chr_id,
4539 x_topline_id => l_input_detail_rec.subline_id,
4540 x_return_status => x_return_status
4541 );
4542
4543 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
4544 )
4545 THEN
4546 fnd_log.STRING (fnd_log.level_statement,
4547 g_module ||
4548 l_api_name,
4549 '381:Get_id chr_id in CompP = ' ||
4550 l_input_detail_rec.chr_id
4551 );
4552 fnd_log.STRING (fnd_log.level_statement,
4553 g_module ||
4554 l_api_name,
4555 '382:Get_id topline_id in CompP = ' ||
4556 l_input_detail_rec.line_id
4557 );
4558 END IF;
4559
4560 --errorout ('Get_id chr_id in CompP = ' || l_input_detail_rec.chr_id);
4561 --errorout ('Get_id topline_id in CompP = ' || l_input_detail_rec.line_id);
4562 IF x_return_status <> g_ret_sts_success
4563 THEN
4564 IF (fnd_log.level_statement >=
4565 fnd_log.g_current_runtime_level
4566 )
4567 THEN
4568 fnd_log.STRING (fnd_log.level_statement,
4569 g_module ||
4570 l_api_name,
4571 '383:Get_id return status = ' ||
4572 x_return_status
4573 );
4574 END IF;
4575
4576 --errorout ('Get_id return status = ' || x_return_status);
4577 RAISE g_exc_error;
4578 END IF;
4579 END IF;
4580
4581 calculate_subscription_price
4582 (p_detail_rec => l_input_detail_rec,
4583 x_price_details => x_price_details,
4584 x_modifier_details => x_modifier_details,
4585 x_price_break_details => x_price_break_details,
4586 x_return_status => x_return_status,
4587 x_msg_count => x_msg_count,
4588 x_msg_data => x_msg_data
4589 );
4590
4591 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4592 THEN
4593 fnd_log.STRING
4594 (fnd_log.level_statement,
4595 g_module ||
4596 l_api_name,
4597 '384:after CSUBS - In compute price - return status = ' ||
4598 x_return_status
4599 );
4600 END IF;
4601
4602 -- errorout ('after CSUBS - In compute price - return status = ' || x_return_status);
4603 IF x_return_status <> g_ret_sts_success
4604 THEN
4605 RAISE g_exc_error;
4606 END IF;
4607
4608 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4609 THEN
4610 fnd_log.STRING (fnd_log.level_statement,
4611 g_module ||
4612 l_api_name,
4613 '385:Chr_id = ' ||
4614 l_input_detail_rec.chr_id
4615 );
4616 END IF;
4617
4618 -- errorout ('Chr_id = ' || l_input_detail_rec.chr_id);
4619 l_chrv_rec_in.ID := l_input_detail_rec.chr_id;
4620 l_chrv_rec_in.estimated_amount :=
4621 get_amounts (p_id => l_input_detail_rec.chr_id,
4622 p_level => g_hdr_level);
4623 get_k_details (p_id => l_input_detail_rec.chr_id,
4624 p_type => g_okc_hdr,
4625 x_k_det_rec => l_k_det_rec
4626 );
4627 l_chrv_rec_in.object_version_number :=
4628 l_k_det_rec.object_version_number;
4629
4630 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4631 THEN
4632 fnd_log.STRING (fnd_log.level_statement,
4633 g_module ||
4634 l_api_name,
4635 '386:Amount = ' ||
4636 l_chrv_rec_in.estimated_amount
4637 );
4638 END IF;
4639
4640 -- errorout ('Amount = ' || l_chrv_rec_in.estimated_amount);
4641 okc_contract_pub.update_contract_header
4642 (p_api_version => l_api_version,
4643 p_init_msg_list => l_init_msg_list,
4644 x_return_status => x_return_status,
4645 x_msg_count => x_msg_count,
4646 x_msg_data => x_msg_data,
4647 p_restricted_update => okc_api.g_false,
4648 p_chrv_rec => l_chrv_rec_in,
4649 x_chrv_rec => l_chrv_rec_out
4650 );
4651
4652 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4653 THEN
4654 fnd_log.STRING
4655 (fnd_log.level_statement,
4656 g_module ||
4657 l_api_name,
4658 '387:after hdr updation - In compute price - return status = ' ||
4659 x_return_status
4660 );
4661 END IF;
4662
4663 -- errorout ('after hdr updation - In compute price - return status = ' || x_return_status);
4664 IF x_return_status <> g_ret_sts_success
4665 THEN
4666 RAISE g_exc_error;
4667 END IF;
4668 ELSE
4669 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4670 THEN
4671 fnd_log.STRING (fnd_log.level_statement,
4672 g_module ||
4673 l_api_name,
4674 '388:Line id is NULL'
4675 );
4676 END IF;
4677
4678 -- errorout ('Line id is NULL');
4679 okc_api.set_message (p_app_name => g_app_name,
4680 p_msg_name => g_required_value,
4681 p_token1 => g_invalid_value,
4682 p_token1_value => 'CLE_ID'
4683 );
4684 x_return_status := g_ret_sts_error;
4685 RAISE g_exc_error;
4686 END IF;
4687 ELSIF p_detail_rec.intent = g_override_pricing
4688 THEN
4689 IF p_detail_rec.subline_id IS NOT NULL
4690 THEN
4691 IF ( p_detail_rec.line_id IS NULL
4692 OR p_detail_rec.chr_id IS NULL
4693 )
4694 THEN
4695 get_id (p_line_id => p_detail_rec.subline_id,
4696 x_chr_id => l_input_detail_rec.chr_id,
4697 x_topline_id => l_input_detail_rec.line_id,
4698 x_return_status => x_return_status
4699 );
4700
4701 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level
4702 )
4703 THEN
4704 fnd_log.STRING (fnd_log.level_statement,
4705 g_module ||
4706 l_api_name,
4707 '389:Get_id topline_id = ' ||
4708 l_input_detail_rec.line_id
4709 );
4710 END IF;
4711
4712 --errorout ('Get_id topline_id = ' || l_input_detail_rec.line_id);
4713 IF x_return_status <> g_ret_sts_success
4714 THEN
4715 --errorout ('Get_id return status = ' || x_return_status);
4716 RAISE g_exc_error;
4717 END IF;
4718 END IF;
4719
4720 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4721 THEN
4722 fnd_log.STRING (fnd_log.level_statement,
4723 g_module ||
4724 l_api_name,
4725 '390:here is Sub line'
4726 );
4727 END IF;
4728
4729 -- errorout ('here is Sub line');
4730 calculate_subline_price
4731 (p_detail_rec => l_input_detail_rec,
4732 x_price_details => x_price_details,
4733 x_modifier_details => x_modifier_details,
4734 x_price_break_details => x_price_break_details,
4735 x_return_status => x_return_status,
4736 x_msg_count => x_msg_count,
4737 x_msg_data => x_msg_data
4738 );
4739
4740 IF x_return_status <> g_ret_sts_success
4741 THEN
4742 RAISE g_exc_error;
4743 END IF;
4744
4745 l_clev_rec_in.ID := l_input_detail_rec.line_id;
4746 l_clev_rec_in.price_negotiated :=
4747 get_amounts (p_id => l_input_detail_rec.line_id,
4748 p_level => g_line_level);
4749 get_k_details (p_id => l_input_detail_rec.line_id,
4750 p_type => g_okc_line,
4751 x_k_det_rec => l_k_det_rec
4752 );
4753 l_clev_rec_in.object_version_number :=
4754 l_k_det_rec.object_version_number;
4755 okc_contract_pub.update_contract_line
4756 (p_api_version => l_api_version,
4757 p_init_msg_list => l_init_msg_list,
4758 x_return_status => x_return_status,
4759 x_msg_count => x_msg_count,
4760 x_msg_data => x_msg_data,
4761 p_clev_rec => l_clev_rec_in,
4762 x_clev_rec => l_clev_rec_out
4763 );
4764
4765 IF x_return_status <> g_ret_sts_success
4766 THEN
4767 RAISE g_exc_error;
4768 END IF;
4769
4770 l_chrv_rec_in.ID := l_input_detail_rec.chr_id;
4771 l_chrv_rec_in.estimated_amount :=
4772 get_amounts (p_id => l_input_detail_rec.chr_id,
4773 p_level => g_hdr_level);
4774 get_k_details (p_id => l_input_detail_rec.chr_id,
4775 p_type => g_okc_hdr,
4776 x_k_det_rec => l_k_det_rec
4777 );
4778 l_chrv_rec_in.object_version_number :=
4779 l_k_det_rec.object_version_number;
4780 okc_contract_pub.update_contract_header
4781 (p_api_version => l_api_version,
4782 p_init_msg_list => l_init_msg_list,
4783 x_return_status => x_return_status,
4784 x_msg_count => x_msg_count,
4785 x_msg_data => x_msg_data,
4786 p_restricted_update => okc_api.g_false,
4787 p_chrv_rec => l_chrv_rec_in,
4788 x_chrv_rec => l_chrv_rec_out
4789 );
4790
4791 IF x_return_status <> g_ret_sts_success
4792 THEN
4793 RAISE g_exc_error;
4794 END IF;
4795 ELSE
4796 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4797 THEN
4798 fnd_log.STRING (fnd_log.level_statement,
4799 g_module ||
4800 l_api_name,
4801 '391:Sub line id is NULL'
4802 );
4803 END IF;
4804
4805 -- errorout ('Sub line id is NULL');
4806 okc_api.set_message (p_app_name => g_app_name,
4807 p_msg_name => g_required_value,
4808 p_token1 => g_invalid_value,
4809 p_token1_value => 'LINE_ID'
4810 );
4811 x_return_status := g_ret_sts_error;
4812 RAISE g_exc_error;
4813 END IF;
4814 ELSE
4815 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4816 THEN
4817 fnd_log.STRING (fnd_log.level_statement,
4818 g_module ||
4819 l_api_name,
4820 '392:********** Invalid Intent ********'
4821 );
4822 END IF;
4823
4824 -- errorout ('********** Invalid Intent ********');
4825 okc_api.set_message (p_app_name => g_app_name,
4826 p_msg_name => g_required_value,
4827 p_token1 => g_invalid_value,
4828 p_token1_value => 'INTENT'
4829 );
4830 x_return_status := g_ret_sts_error;
4831 RAISE g_exc_error;
4832 END IF;
4833
4834 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
4835 THEN
4836 fnd_log.STRING (fnd_log.level_statement,
4837 g_module ||
4838 l_api_name,
4839 '393:********** Exiting Compute Price ********'
4840 );
4841 END IF;
4842
4843 -- errorout ('********** Exiting Compute Price ********');
4844
4845 -- end debug log
4846 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4847 THEN
4848 fnd_log.STRING (fnd_log.level_procedure,
4849 g_module ||
4850 l_api_name,
4851 '1000: Leaving ' ||
4852 g_pkg_name ||
4853 '.' ||
4854 l_api_name
4855 );
4856 END IF;
4857 EXCEPTION
4858 WHEN g_exc_error
4859 THEN
4860 -- end debug log
4861 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4862 THEN
4863 fnd_log.STRING (fnd_log.level_procedure,
4864 g_module ||
4865 l_api_name,
4866 '2000: Leaving ' ||
4867 g_pkg_name ||
4868 '.' ||
4869 l_api_name
4870 );
4871 END IF;
4872
4873 DBMS_TRANSACTION.rollback_savepoint ('COMPUTE_PRICE');
4874 WHEN OTHERS
4875 THEN
4876 -- end debug log
4877 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
4878 THEN
4879 fnd_log.STRING (fnd_log.level_procedure,
4880 g_module ||
4881 l_api_name,
4882 '4000: Leaving ' ||
4883 g_pkg_name ||
4884 '.' ||
4885 l_api_name
4886 );
4887 END IF;
4888
4889 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4890 DBMS_TRANSACTION.rollback_savepoint ('COMPUTE_PRICE');
4891 x_return_status := g_ret_sts_unexp_error;
4892 okc_api.set_message (p_app_name => g_app_name,
4893 p_msg_name => g_unexpected_error,
4894 p_token1 => g_sqlcode_token,
4895 p_token1_value => SQLCODE,
4896 p_token2 => g_sqlerrm_token,
4897 p_token2_value => SQLERRM
4898 );
4899 END compute_price;
4900 END oks_qp_int_pvt;