1 PACKAGE BODY oe_oe_totals_summary AS
2 /* $Header: OEXVTOTB.pls 120.14.12020000.3 2013/02/05 05:51:01 sujithku ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_OE_TOTALS_SUMMARY';
5
6 --rc pviprana Global table to hold the recurring amounts of an order level modifier grouped by periodicity
7 G_RECURRING_AMOUNTS_TBL recurring_amounts_tbl_type;
8
9 FUNCTION PRICE_ADJUSTMENTS
10 (
11 p_header_id IN NUMBER
12 )
13 RETURN NUMBER
14
15 IS
16 adjustment_total NUMBER;
17 --
18 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
19 --
20 BEGIN
21 adjustment_total:=oe_header_adj_util.get_adj_total(p_header_id);
22 RETURN(adjustment_total);
23
24 END PRICE_ADJUSTMENTS;
25
26
27 /* function to get adjustment amount for a ORDER level
28 * modifier to be applied to a given line */
29
30 FUNCTION LINE_PRICE_ADJ_ORDER_MODIFIER
31 (
32 x_header_id IN NUMBER,
33 x_line_id IN NUMBER,
34 x_list_line_id IN NUMBER
35 )
36 RETURN NUMBER
37
38 IS
39 adj_line_total_order_modifier NUMBER;
40 --
41 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
42 --
43 BEGIN
44
45 SELECT -1 * decode(opa.arithmetic_operator,
46 null, 0,
47 '%', opa.operand*ool.unit_list_price/100,
48 'AMT',opa.operand,
49 'NEWPRICE',ool.unit_list_price - opa.operand)
50 INTO adj_line_total_order_modifier
51 FROM oe_price_adjustments opa
52 , oe_order_lines_all ool
53 WHERE opa.HEADER_ID = x_header_id
54 and opa.line_id is null
55 and ool.line_id = x_line_id
56 and ool.header_id = x_header_id
57 and opa.list_line_id = x_list_line_id
58 and nvl(opa.applied_flag,'N') = 'Y'
59 and nvl(opa.accrual_flag,'N') = 'N'
60 and list_line_type_code in ('DIS','SUR','PBH');
61
62 RETURN(adj_line_total_order_modifier);
63
64 END LINE_PRICE_ADJ_ORDER_MODIFIER;
65
66 /* function to get extended adjustment amount for a ORDER level
67 * modifier to be applied to a given line */
68
69 FUNCTION LINE_EXT_ADJ_ORDER_MODIFIER
70 (
71 x_header_id IN NUMBER,
72 x_line_id IN NUMBER,
73 x_list_line_id IN NUMBER
74 )
75 RETURN NUMBER
76
77 IS
78 ext_line_tot_order_modifier NUMBER;
79 --
80 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
81 --
82 BEGIN
83
84 SELECT -1 * decode(opa.arithmetic_operator,
85 null, 0,
86 '%', opa.operand*ool.unit_list_price/100,
87 'AMT',opa.operand,
88 'NEWPRICE',ool.unit_list_price - opa.operand) * NVL(ool.ordered_quantity,0)
89 INTO ext_line_tot_order_modifier
90 FROM oe_price_adjustments opa
91 , oe_order_lines_all ool
92 WHERE opa.HEADER_ID = x_header_id
93 and opa.line_id is null
94 and ool.line_id = x_line_id
95 and ool.header_id = x_header_id
96 and opa.list_line_id = x_list_line_id
97 and nvl(opa.applied_flag,'N') = 'Y'
98 and nvl(opa.accrual_flag,'N') = 'N'
99 and list_line_type_code in ('DIS','SUR','PBH');
100
101 RETURN(ext_line_tot_order_modifier);
102
103 END LINE_EXT_ADJ_ORDER_MODIFIER;
104
105 /* function get total adjustment amount for a given line */
106
107 FUNCTION LINE_PRICE_ADJUSTMENTS
108 (
109 x_line_id IN NUMBER
110 )
111 RETURN NUMBER
112
113 IS
114 adjustment_line_total NUMBER;
115 --
116 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
117 --
118 BEGIN
119 adjustment_line_total:=oe_header_adj_util.get_adj_total(p_line_id => x_line_id);
120 RETURN(adjustment_line_total);
121
122 END LINE_PRICE_ADJUSTMENTS;
123
124 FUNCTION CHARGES
125 (
126 p_header_id IN NUMBER
127 )
128 RETURN NUMBER
129
130 IS
131 l_charge_total NUMBER;
132 l_msg_count NUMBER := 0;
133 l_msg_data VARCHAR2(2000):= NULL;
134 l_return_status VARCHAR2(1);
135
136 --
137 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
138 --
139 BEGIN
140
141 -- Calling the charges API to get the Order Total for charges.
142
143 OE_CHARGE_PVT.Get_Charge_Amount(
144 p_api_version_number => 1.1 ,
145 p_init_msg_list => FND_API.G_FALSE ,
146 p_header_id => p_header_id ,
147 p_line_id => NULL,
148 p_all_charges => FND_API.G_TRUE ,
149 x_return_status => l_return_status ,
150 x_msg_count => l_msg_count ,
151 x_msg_data => l_msg_data ,
152 x_charge_amount => l_charge_total
153 );
154 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159
160 RETURN l_charge_total;
161
162 END CHARGES;
163
164
165 /* Chen added */
166 FUNCTION LINE_CHARGES
167 (
168 p_header_id IN NUMBER,
169 p_line_id IN NUMBER
170 )
171 RETURN NUMBER
172
173 IS
174 l_charge_total NUMBER;
175 l_msg_count NUMBER := 0;
176 l_msg_data VARCHAR2(2000):= NULL;
177 l_return_status VARCHAR2(1);
178
179 --
180 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
181 --
182 BEGIN
183
184 -- Calling the charges API to get the Order Total for charges.
185
186 OE_CHARGE_PVT.Get_Charge_Amount(
187 p_api_version_number => 1.1 ,
188 p_init_msg_list => FND_API.G_FALSE ,
189 p_header_id => p_header_id ,
190 p_line_id => p_line_id,
191 p_all_charges => FND_API.G_FALSE ,
192 x_return_status => l_return_status ,
193 x_msg_count => l_msg_count ,
194 x_msg_data => l_msg_data ,
195 x_charge_amount => l_charge_total
196 );
197 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
200 RAISE FND_API.G_EXC_ERROR;
201 END IF;
202
203 RETURN l_charge_total;
204
205 END LINE_CHARGES;
206
207 FUNCTION TAXES
208 (
209 p_header_id IN NUMBER
210 )
211 RETURN NUMBER
212
213 IS
214
215 --
216 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
217 --
218 BEGIN
219
220 RETURN(nvl(G_TAX_VALUE,0));
221
222 END TAXES;
223
224 FUNCTION ORDER_SUBTOTALS
225 (
226 p_header_id IN NUMBER
227 )
228 RETURN NUMBER
229
230 IS
231
232 --
233 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
234 --
235 BEGIN
236 RETURN(nvl(G_TOTAL_EXTENDED_PRICE,0));
237
238 END ORDER_SUBTOTALS;
239
240 FUNCTION LINE_TOTAL
241 (p_header_id IN NUMBER,
242 p_line_id IN NUMBER,
243 p_line_number IN NUMBER,
244 p_shipment_number IN NUMBER
245 )
246 RETURN NUMBER
247
248 IS
249 l_Total NUMBER :=0;
250
251 CURSOR C1(p_header_id NUMBER,p_line_number NUMBER,p_line_id NUMBER) IS
252 SELECT NVL(Ordered_Quantity,0)*
253 NVL(unit_selling_price,0) Line_details_total,Line_Number,
254 Line_Category_Code
255 FROM oe_order_lines_all
256 WHERE header_id=p_header_id
257 AND (line_number=p_line_number
258 AND NVL(cancelled_flag,'N') ='N'
259 OR (top_model_line_id is not null
260 AND top_model_line_id=p_line_id
261 AND NVL(cancelled_flag,'N') ='N')
262 OR (service_reference_line_id is not null
263 AND service_reference_line_id=p_line_id
264 AND NVL(cancelled_flag,'N') ='N'));
265
266 /* Need to Check for performance with using 'OR'instead of Union */
267
268 /* CURSOR C2(line_number NUMBER,
269 header_id NUMBER,
270 shipment_number NUMBER)
271 IS
272 SELECT NVL(ordered_quantity,0)-NVL(cancelled_quantity,0)*
273 NVL(unit_selling_price,0) shipment_total,Line_Number
274 FROM oe_order_lines_all
275 WHERE header_id=header_id
276 AND Line_Number=Line_Number
277 AND Shipment_Number=shipment_number
278 AND cancelled_flag='N'; */
279
280 --
281 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
282 --
283 BEGIN
284 FOR Lines IN C1(p_header_id,p_line_number,p_line_id)
285
286 LOOP
287 IF lines.line_category_code <> 'RETURN' THEN
288 l_Total:=l_Total+lines.line_details_total;
289 ELSIF lines.line_category_code='RETURN' THEN
290 l_total:=l_total-lines.line_details_total;
291 END IF;
292 END LOOP;
293 RETURN(l_total);
294
295 EXCEPTION
296 WHEN no_data_found THEN
297 Null;
298 WHEN too_many_rows THEN
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 WHEN others THEN
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 END LINE_TOTAL;
303
304 /* bug - 1480491 */
305 FUNCTION SERVICE_TOTAL
306 (p_header_id IN NUMBER,
307 p_line_number IN NUMBER,
308 p_service_number IN NUMBER
309 )
310 RETURN NUMBER
311
312 IS
313 l_Total NUMBER :=0;
314
315 CURSOR C1(p_header_id NUMBER,p_line_number NUMBER,p_service_number NUMBER) IS
316 SELECT NVL(Ordered_Quantity,0)*
317 NVL(unit_selling_price,0) Line_details_total,Line_Number,
318 Line_Category_Code
319 FROM oe_order_lines_all
320 WHERE header_id=p_header_id
321 AND line_number=p_line_number
322 AND service_number=p_service_number
323 AND NVL(cancelled_flag,'N') ='N'
324 AND item_type_code = 'SERVICE';
325
326 --
327 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
328 --
329 BEGIN
330 FOR Lines IN C1(p_header_id,p_line_number,p_service_number)
331
332 LOOP
333 IF lines.line_category_code <> 'RETURN' THEN
334 l_Total:=l_Total+lines.line_details_total;
335 ELSIF lines.line_category_code='RETURN' THEN
336 l_total:=l_total-lines.line_details_total;
337 END IF;
338 END LOOP;
339 RETURN(l_total);
340
341 EXCEPTION
342 WHEN no_data_found THEN
343 Null;
344 WHEN too_many_rows THEN
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346 WHEN others THEN
347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
348 END SERVICE_TOTAL;
349 /* bug - 1480491 */
350
351 PROCEDURE GLOBAL_TOTALS(p_header_id IN NUMBER)
352 IS
353 Is_fmt BOOLEAN;
354 l_tax_hdr_chg NUMBER; --TaxER
355
356 --
357 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
358 --
359
360 BEGIN
361
362 IF p_header_id IS NOT NULL THEN
363 Is_fmt:= OE_ORDER_UTIL.Get_Precision(p_header_id=>p_header_id);
364 END IF;
365
366 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
367 OE_ORDER_UTIL.G_Precision:=2;
368 END IF;
369
370 /* changed following SQL for #3970425 */
371
372 SELECT SUM(nvl(ROUND(decode(oel.line_category_code,'RETURN',-oel.tax_value,oel.tax_value), OE_ORDER_UTIL.G_Precision),0)),
373 SUM(ROUND(nvl(oel.Ordered_Quantity,0) * (oel.unit_selling_price) * (decode(oel.line_category_code,'RETURN',-1,1)),OE_ORDER_UTIL.G_Precision))
374 INTO G_TAX_VALUE, G_TOTAL_EXTENDED_PRICE
375 FROM oe_order_lines_all oel
376 WHERE oel.header_id=p_header_id
377 AND charge_periodicity_code is NULL -- added for recurring charges
378 AND NVL(oel.cancelled_flag,'N') ='N';
379
380 --TaxER start
381 SELECT Sum(Nvl(ROUND(adjusted_amount,OE_ORDER_UTIL.G_Precision),0))
382 INTO l_tax_hdr_chg
383 FROM oe_price_adjustments
384 WHERE header_id=p_header_id
385 AND line_id IS NULL
386 AND list_line_type_code = 'TAX'
387 and applied_flag = 'N' -- added for bug12895421
388 AND parent_adjustment_id IS NOT NULL;
389
390 G_TAX_VALUE := G_TAX_VALUE + NVL(l_tax_hdr_chg,0);
391 --TaxER end
392
393
394
395
396 EXCEPTION
397 WHEN too_many_rows THEN
398 IF l_debug_level > 0 THEN
399 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
400 END IF;
401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
402 WHEN no_data_found THEN
403 IF l_debug_level > 0 THEN
404 oe_debug_pub.add('No Data found'||p_header_id,1) ;
405 END IF;
406 WHEN others THEN
407 IF l_debug_level > 0 THEN
408 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
409 END IF;
410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
411 END GLOBAL_TOTALS;
412
413 FUNCTION CONFIG_TOTALS
414 (
415 p_line_id IN NUMBER
416 )
417 RETURN NUMBER
418 IS
419 l_config_total NUMBER;
420 --
421 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
422 --
423 BEGIN
424 SELECT SUM(nvl(Ordered_Quantity,0)
425 *(unit_selling_price))
426 INTO l_config_total
427 FROM oe_order_lines_all
428 WHERE line_id=p_line_id
429 AND NVL(cancelled_flag,'N') ='N';
430
431 RETURN(l_config_total);
432
433 EXCEPTION
434 WHEN too_many_rows THEN
435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
436
437 WHEN no_data_found THEN
438 Null;
439
440 WHEN others THEN
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442
443 END CONFIG_TOTALS;
444
445 FUNCTION TOTAL_ORDERED_QTY
446 (
447 p_header_id IN NUMBER,
448 p_line_number IN NUMBER
449 )
450 RETURN NUMBER
451 IS
452 l_ordered_qty NUMBER :=0;
453 CURSOR C1(p_header_id NUMBER,p_line_number NUMBER) IS
454 SELECT NVL(Ordered_quantity,0) Qty
455 FROM oe_order_lines_all
456 WHERE header_id=p_header_id
457 AND NVL(cancelled_flag,'N') ='N'
458 AND line_number=p_line_number
459 AND item_type_code in ('STANDARD','MODEL','KIT')
460 -- And option_number is null
461 and line_id = nvl(top_model_line_id,line_id);
462
463 --
464 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
465 --
466 BEGIN
467
468 FOR Lines IN C1(p_header_id,p_line_number)
469 LOOP
470 l_ordered_qty:=l_ordered_qty+lines.qty;
471 END LOOP;
472 RETURN(l_ordered_qty);
473 EXCEPTION
474 WHEN no_data_found THEN
475 Null;
476 WHEN too_many_rows THEN
477 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
478 WHEN others THEN
479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
480 END TOTAL_ORDERED_QTY;
481
482 PROCEDURE ORDER_TOTALS
483 (
484 p_header_id IN NUMBER,
485 p_subtotal OUT NOCOPY NUMBER,
486
487 p_discount OUT NOCOPY NUMBER,
488
489 p_charges OUT NOCOPY NUMBER,
490
491 p_tax OUT NOCOPY NUMBER
492
493 )
494 IS
495
496 --
497 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
498 --
499 BEGIN
500 OE_OE_TOTALS_SUMMARY.Global_Totals
501 (
502 p_header_id
503 );
504 p_subtotal:=OE_OE_TOTALS_SUMMARY.Order_Subtotals
505 (
506 p_header_id
507 );
508
509 p_discount:=OE_OE_TOTALS_SUMMARY.Price_Adjustments
510 (
511 p_header_id
512 );
513 p_charges:=OE_OE_TOTALS_SUMMARY.Charges
514 (
515 p_header_id
516 );
517 p_tax:=OE_OE_TOTALS_SUMMARY.Taxes
518 (
519 p_header_id
520 );
521
522
523 END ORDER_TOTALS;
524
525
526 /* The function PRT_ORDER_TOTAL is used by the view ONT_PRT_ORDER_HEADERS_V to calculate the order total */
527
528 FUNCTION PRT_ORDER_TOTAL
529 (
530 p_header_id IN NUMBER
531 )
532 RETURN NUMBER
533 IS
534
535 l_subtotal NUMBER;
536 l_discount NUMBER;
537 l_charges NUMBER;
538 l_tax NUMBER;
539
540 --
541 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
542 --
543 BEGIN
544
545 OE_OE_TOTALS_SUMMARY.Global_Totals(p_header_id);
546
547 l_subtotal:=OE_OE_TOTALS_SUMMARY.Order_Subtotals(p_header_id);
548
549 -- performance bug 4060810: disabling this calculation as it is not used
550 -- l_discount:=OE_OE_TOTALS_SUMMARY.Price_Adjustments(p_header_id);
551 l_charges:=OE_OE_TOTALS_SUMMARY.Charges(p_header_id);
552 l_tax:=OE_OE_TOTALS_SUMMARY.Taxes(p_header_id);
553
554 RETURN (l_subtotal + l_charges + l_tax);
555
556 EXCEPTION
557 WHEN OTHERS THEN
558 RETURN NULL;
559 END PRT_ORDER_TOTAL;
560
561 FUNCTION OUTBOUND_ORDER_TOTAL
562 (
563 p_header_id IN NUMBER,
564 p_to_exclude_commitment IN VARCHAR2 DEFAULT NULL, -- 4013565
565 p_total_type IN VARCHAR2 DEFAULT NULL, --4013565
566 p_all_lines IN VARCHAR2 DEFAULT NULL
567 ) RETURN NUMBER
568 IS
569 l_order_total NUMBER;
570 l_tax_total NUMBER;
571 l_charges NUMBER;
572 l_outbound_total NUMBER;
573 l_commitment_total NUMBER;
574 l_chgs_w_line_id NUMBER := 0;
575 l_chgs_wo_line_id NUMBER := 0;
576 --
577 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
578 --
579 Is_fmt BOOLEAN; --5716270
580 BEGIN
581
582 Is_fmt:= OE_ORDER_UTIL.Get_Precision(p_header_id=>p_header_id); --5716270
583 -- bug4013565
584 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
585 OE_ORDER_UTIL.G_Precision:=2;
586 END IF;
587
588
589 -- added ROUND in someplaces below for 4013565
590
591 -- Select the Tax Total and Outbound Extended Price
592 -- p_all_lines 'Y' means to include all lines including all open or closed ones.
593 -- Currently this parameter is only set 'Y' when calling from Payments form.
594 --sol_ord_er #16014165, If Full Billing from OKS then no invoice, hence no payment
595 SELECT
596 SUM(ROUND(nvl(ool.tax_value,0), OE_ORDER_UTIL.G_Precision))
597 /*, SUM(ROUND(nvl(ool.Ordered_Quantity,0)
598 *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))*/ -- sol_ord_er #16014165
599 ,SUM(ROUND(NVL(ool.service_first_period_amount,nvl(ool.Ordered_Quantity,0)*(ool.unit_selling_price)),OE_ORDER_UTIL.G_Precision))
600 INTO
601 l_tax_total
602 , l_order_total
603 FROM oe_order_lines_all ool
604 WHERE ool.header_id = p_header_id
605 AND ( (ool.open_flag = 'Y' AND p_all_lines is null)
606 OR nvl(p_all_lines, 'N') = 'Y' )
607 AND ool.line_category_code <> 'RETURN'
608 AND NVL(ool.service_bill_option_code,'FBOM') <> 'FBOKS' -- sol_ord_er #16014165
609 AND ool.charge_periodicity_code is null -- Added for Recurring Charges
610 AND NOT EXISTS
611 (SELECT 'Non Invoiceable Item Line'
612 FROM mtl_system_items mti
613 WHERE mti.inventory_item_id = ool.inventory_item_id
614 AND mti.organization_id = nvl(ool.ship_from_org_id,
615 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
616 AND (mti.invoiceable_item_flag = 'N'
617 OR mti.invoice_enabled_flag = 'N'));
618
619 IF OE_Commitment_Pvt.Do_Commitment_Sequencing THEN
620 -- Select the committment applied amount if Commitment Sequencing "On"
621 SELECT SUM(ROUND(nvl(op.commitment_applied_amount,0), OE_ORDER_UTIL.G_Precision))
622 INTO l_commitment_total
623 FROM oe_payments op
624 WHERE op.header_id = p_header_id
625 AND NOT EXISTS
626 (SELECT 'Non Invoiceable Item Line'
627 FROM mtl_system_items mti, oe_order_lines_all ool
628 WHERE ool.line_id = op.line_id
629 AND mti.inventory_item_id = ool.inventory_item_id
630 AND mti.organization_id = nvl(ool.ship_from_org_id,
631 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
632 AND (mti.invoiceable_item_flag = 'N'
633 OR mti.invoice_enabled_flag = 'N'));
634 ELSE
635 -- Select the Outbound Extended Price for lines that have committment
636 SELECT /*SUM(ROUND(nvl(ool.Ordered_Quantity,0) *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))*/
637 -- sol_ord_er #16014165
638 SUM(ROUND(NVL(ool.service_first_period_amount,nvl(ool.Ordered_Quantity,0)*(ool.unit_selling_price)),OE_ORDER_UTIL.G_Precision))
639 INTO l_commitment_total
640 FROM oe_order_lines_all ool
641 WHERE ool.header_id = p_header_id
642 AND ool.commitment_id is not null
643 AND ( (ool.open_flag = 'Y' AND p_all_lines is null)
644 OR nvl(p_all_lines, 'N') = 'Y' )
645 AND ool.charge_periodicity_code is null -- Added for Recurring Charges
646 AND ool.line_category_code <> 'RETURN'
647 AND NVL(ool.service_bill_option_code,'FBOM') <> 'FBOKS' -- sol_ord_er #16014165
648 AND NOT EXISTS
649 (SELECT 'Non Invoiceable Item Line'
650 FROM mtl_system_items mti
651 WHERE mti.inventory_item_id = ool.inventory_item_id
652 AND mti.organization_id = nvl(ool.ship_from_org_id,
653 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
654 AND (mti.invoiceable_item_flag = 'N'
655 OR mti.invoice_enabled_flag = 'N'));
656 END IF;
657
658 -- Select the Outbound Charges Total
659 -- sol_ord_er #16014165, Freight Charges are not invoiced if billing involves OKS, hence payment should not be made for it.
660
661 SELECT SUM(
662 ROUND(
663 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-P.OPERAND,P.OPERAND), OE_ORDER_UTIL.G_Precision
664 )
665 )
666 INTO l_chgs_wo_line_id
667 FROM OE_PRICE_ADJUSTMENTS P
668 WHERE P.HEADER_ID = p_header_id
669 AND P.LINE_ID IS NULL
670 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
671 AND P.APPLIED_FLAG = 'Y'
672 --Bug 6072691
673 --AND NVL(P.INVOICED_FLAG, 'N') = 'N';
674 AND (( NVL(P.INVOICED_FLAG,'N') = 'Y'
675 AND NVL(p_total_type,'OTHERS') = 'INV_CHARGES'
676 )
677 OR
678 ( NVL(P.INVOICED_FLAG,'N') <> 'Y'
679 AND NVL(p_total_type,'OTHERS') = 'CHARGES'
680 )
681 OR
682 (
683 NVL(p_total_type,'OTHERS') NOT IN ('INV_CHARGES','CHARGES')
684 )
685 );
686 --bug 8217014
687 SELECT SUM(
688 ROUND(
689 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
690 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
691 DECODE(L.ORDERED_QUANTITY,0,0,-P.OPERAND),
692 (-L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)),
693 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
694 DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
695 (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT))
696 )
697 ,OE_ORDER_UTIL.G_Precision
698 )
699 )
700 INTO l_chgs_w_line_id
701 FROM OE_PRICE_ADJUSTMENTS P,
702 OE_ORDER_LINES_ALL L
703 WHERE P.HEADER_ID = p_header_id
704 AND P.LINE_ID = L.LINE_ID
705 AND NVL(L.SERVICE_BILL_OPTION_CODE,'FBOM') = 'FBOM' -- sol_ord_er #16014165
706 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
707 AND P.APPLIED_FLAG = 'Y'
708 AND L.charge_periodicity_code is null -- Added for Recurring Charges
709 AND L.header_id = p_header_id
710 AND ( (L.open_flag = 'Y' AND p_all_lines is null)
711 OR nvl(p_all_lines, 'N') = 'Y' )
712 AND L.line_category_code <> 'RETURN'
713 AND NOT EXISTS
714 (SELECT 'Non Invoiceable Item Line'
715 FROM MTL_SYSTEM_ITEMS MTI
716 WHERE MTI.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
717 AND MTI.ORGANIZATION_ID = NVL(L.SHIP_FROM_ORG_ID,
718 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', L.org_id))
719 AND (MTI.INVOICEABLE_ITEM_FLAG = 'N'
720 OR MTI.INVOICE_ENABLED_FLAG = 'N'))
721 --Bug 6072691
722 --AND NVL(P.INVOICED_FLAG, 'N') = 'N';
723 AND (( NVL(P.INVOICED_FLAG,'N') = 'Y'
724 AND NVL(p_total_type,'OTHERS') = 'INV_CHARGES'
725 )
726 OR
727 ( NVL(P.INVOICED_FLAG,'N') <> 'Y'
728 AND NVL(p_total_type,'OTHERS') = 'CHARGES'
729 )
730 OR
731 (
732 NVL(p_total_type,'OTHERS') NOT IN ('INV_CHARGES','CHARGES')
733 )
734 );
735
736 l_charges := nvl(l_chgs_wo_line_id,0) + nvl(l_chgs_w_line_id,0);
737
738 IF nvl(p_to_exclude_commitment, 'Y') = 'Y' THEN
739 l_outbound_total := nvl(l_order_total, 0) + nvl(l_tax_total, 0)
740 + nvl(l_charges, 0) - nvl(l_commitment_total,0);
741 ELSE
742 l_outbound_total := nvl(l_order_total, 0) + nvl(l_tax_total, 0)
743 + nvl(l_charges, 0);
744 END IF;
745
746 IF l_debug_level > 0 THEN
747 oe_debug_pub.add( 'CALCULATING THE TOTAL AMOUNT TO BE AUTHORIZED FOR THIS ORDER ' , 1 ) ;
748 END IF;
749 IF l_debug_level > 0 THEN
750 oe_debug_pub.add( 'ORDER TOTAL -> '||TO_CHAR ( L_ORDER_TOTAL ) , 1 ) ;
751 END IF;
752 IF l_debug_level > 0 THEN
753 oe_debug_pub.add( 'TAX TOTAL -> '||TO_CHAR ( L_TAX_TOTAL ) , 1 ) ;
754 END IF;
755 IF l_debug_level > 0 THEN
756 oe_debug_pub.add( 'COMMITMENTS -> '||TO_CHAR ( L_COMMITMENT_TOTAL ) , 1 ) ;
757 END IF;
758 IF l_debug_level > 0 THEN
759 oe_debug_pub.add( 'OTHER CHARGES -> '||TO_CHAR ( L_CHARGES ) , 1 ) ;
760 END IF;
761 IF l_debug_level > 0 THEN
762 oe_debug_pub.add( 'AMOUNT TO BE AUTHORIZED => '||TO_CHAR ( L_OUTBOUND_TOTAL ) , 1 ) ;
763 END IF;
764
765 -- bug 4013565
766 IF p_total_type = 'TAXES' THEN
767 RETURN nvl(l_tax_total, 0);
768 --Bug 6072691
769 ELSIF p_total_type IN('CHARGES','INV_CHARGES') THEN
770 RETURN nvl(l_charges, 0);
771 ELSIF p_total_type = 'SUBTOTAL' THEN
772 RETURN nvl(l_order_total,0);
773 ELSE
774 RETURN (l_outbound_total);
775 END IF;
776
777 EXCEPTION
778 WHEN OTHERS THEN
779 IF l_debug_level > 0 THEN
780 oe_debug_pub.add( 'FROM OUTBOUND TOTAL OTHERS' ) ;
781 END IF;
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783
784 END OUTBOUND_ORDER_TOTAL;
785
786 FUNCTION OUTBOUND_ORDER_SUBTOTAL
787 (
788 p_header_id IN NUMBER
789 ) RETURN NUMBER
790 IS
791 l_order_subtotal NUMBER;
792 --
793 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
794 --
795 BEGIN
796 -- Select the Outbound Extended Price
797 BEGIN
798 SELECT SUM(nvl(ool.Ordered_Quantity,0)
799 *(ool.unit_selling_price))
800 INTO l_order_subtotal
801 FROM oe_order_lines_all ool
802 WHERE ool.header_id = p_header_id
803 AND ool.open_flag = 'Y'
804 AND ool.charge_periodicity_code is null -- Added for Recurring Charges
805 AND ool.line_category_code <> 'RETURN'
806 AND NOT EXISTS
807 (SELECT 'Non Invoiceable Item Line'
808 FROM mtl_system_items mti
809 WHERE mti.inventory_item_id = ool.inventory_item_id
810 AND mti.organization_id = nvl(ool.ship_from_org_id,
811 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
812 AND (mti.invoiceable_item_flag = 'N'
813 OR mti.invoice_enabled_flag = 'N'));
814 EXCEPTION
815 WHEN no_data_found THEN
816 l_order_subtotal := 0;
817 END;
818
819 IF l_debug_level > 0 THEN
820 oe_debug_pub.add( 'CALCULATING THE ORDER SUBTOTAL AMOUNT FOR THIS ORDER ' , 1 ) ;
821 oe_debug_pub.add( 'ORDER TOTAL -> '||TO_CHAR ( L_ORDER_SUBTOTAL ) , 1 ) ;
822 END IF;
823 RETURN (nvl(l_order_subtotal,0));
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 IF l_debug_level > 0 THEN
828 oe_debug_pub.add( 'FROM OUTBOUND SUBTOTAL OTHERS' ) ;
829 END IF;
830 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
831
832 END OUTBOUND_ORDER_SUBTOTAL;
833
834 --------------------------------------------------------------------------------------
835 --Called by pricing sourcing rules.
836 --Pricing order amount is always based on sum of unit list price NOT unit selling price
837 ---------------------------------------------------------------------------------------
838 Function Get_Order_Amount(p_header_id In Number) Return Number
839 Is
840 orders_total NUMBER;
841 returns_total NUMBER;
842 l_order_amount NUMBER;
843 --
844 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
845 --
846 BEGIN
847 IF l_debug_level > 0 THEN
848 oe_debug_pub.add( 'ENTERING OE_OE_TOTALS_SUMMARY.GET_ORDER_AMOUNT' ) ;
849 END IF;
850
851 -- combining 2 SQLs into one for performance
852 SELECT SUM(DECODE(line_category_code, 'RETURN', 0, nvl(Ordered_Quantity,0)*(unit_list_price))),
853 SUM(DECODE(line_category_code, 'RETURN', nvl(Ordered_Quantity,0)*(unit_list_price),0))
854 INTO orders_total, returns_total
855 FROM oe_order_lines_all
856 WHERE header_id=p_header_id
857 AND charge_periodicity_code is null -- Added for Recurring CHarges
858 AND NVL(cancelled_flag,'N') ='N';
859
860 l_order_amount:=NVL(orders_total,0)-NVL(returns_total,0);
861
862 IF l_debug_level > 0 THEN
863 oe_debug_pub.add( ' ORDER TOTAL:='||ORDERS_TOTAL ) ;
864 END IF;
865 IF l_debug_level > 0 THEN
866 oe_debug_pub.add( ' RETURN TOTAL:='||RETURNS_TOTAL ) ;
867 END IF;
868 IF l_debug_level > 0 THEN
869 oe_debug_pub.add( ' ORDER AMOUNT:='||L_ORDER_AMOUNT ) ;
870 END IF;
871 IF l_debug_level > 0 THEN
872 oe_debug_pub.add( 'LEAVING OE_OE_TOTALS_SUMMARY.GET_ORDER_AMOUNT' ) ;
873 END IF;
874
875 return l_order_amount;
876 EXCEPTION
877 WHEN too_many_rows THEN
878 IF l_debug_level > 0 THEN
879 oe_debug_pub.add( ' TOO MANY ROWS' ) ;
880 END IF;
881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882
883 WHEN no_data_found THEN
884 IF l_debug_level > 0 THEN
885 oe_debug_pub.add( ' FROM NO DATA FOUND' ) ;
886 END IF;
887
888
889 WHEN others THEN
890 IF l_debug_level > 0 THEN
891 oe_debug_pub.add( ' FROM OTHERS' ) ;
892 END IF;
893 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
894
895 END get_order_amount;
896
897
898
899
900 -- This function is used to calculate the discount percentage for the line
901 -- items in a sales order. This uses the unit selling price and the unit
902 -- list price to calculate the discount
903 -- Input: unit_list_price, unit_selling_price Output: discount percent
904 -- Called from: OE_PRN_ORDER_LINES_V view
905 --
906
907 FUNCTION GET_DISCOUNT(p_unit_list_price IN number,p_unit_selling_price IN NUMBER)
908 RETURN NUMBER IS
909
910 l_discount_pct NUMBER;
911 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
912
913 BEGIN
914 IF l_debug_level > 0 THEN
915 oe_debug_pub.add('ENTER GET_DISCOUNT PROCEDURE');
916 oe_debug_pub.add('Unit List Price = '||p_unit_list_price );
917 oe_debug_pub.add('Unit Selling Price = '||p_unit_selling_price );
918 END IF;
919
920 IF ((p_unit_list_price IS NOT NULL AND p_unit_list_price > 0) AND
921 (p_unit_selling_price IS NOT NULL AND p_unit_selling_price > 0) AND
922 (p_unit_list_price >= p_unit_selling_price)) THEN
923 l_discount_pct := round(((p_unit_list_price-p_unit_selling_price)/p_unit_list_price)*100);
924
925 IF l_debug_level > 0 THEN
926 oe_debug_pub.add('Discount Percent = '||l_discount_pct);
927 oe_debug_pub.add('EXIT GET_DISCOUNT PROCEDURE');
928 END IF;
929 -- IF l_discount_pct < 1 THEN
930 -- RETURN NULL;
931 -- ELSE
932 RETURN l_discount_pct;
933 -- END IF;
934 ELSE
935 IF l_debug_level > 0 THEN
936 oe_debug_pub.add('EXIT GET_DISCOUNT PROCEDURE');
937 END IF;
938
939 RETURN NULL;
940 END IF;
941 EXCEPTION
942 WHEN OTHERS THEN
943 IF l_debug_level > 0 THEN
944 oe_debug_pub.add( 'GET_DISCOUNT: WHEN OTHERS Exception' ) ;
945 END IF;
946 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
947 THEN
948 OE_MSG_PUB.Add_Exc_Msg
949 ( G_PKG_NAME
950 , 'GET_DISCOUNT'
951 );
952 END IF;
953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954 END GET_DISCOUNT;
955
956
957 /* Recurring charges */
958
959 PROCEDURE GLOBAL_REC_TOTALS
960 (
961 p_header_id IN NUMBER,
962 p_charge_periodicity_code IN VARCHAR2
963 )
964 IS
965
966 Is_fmt BOOLEAN;
967
968 --
969 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
970 --
971
972 BEGIN
973
974 IF p_header_id IS NOT NULL THEN
975 Is_fmt:= OE_ORDER_UTIL.Get_Precision(p_header_id=>p_header_id);
976 END IF;
977
978 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
979 OE_ORDER_UTIL.G_Precision:=2;
980 END IF;
981
982 /* changed following SQL for #3970425 */
983
984 SELECT SUM(nvl(ROUND(decode(oel.line_category_code,'RETURN',-oel.tax_value,oel.tax_value), OE_ORDER_UTIL.G_Precision),0)),
985 SUM(ROUND(nvl(oel.Ordered_Quantity,0) * (oel.unit_selling_price) * (decode(oel.line_category_code,'RETURN',-1,1)),OE_ORDER_UTIL.G_Precision))
986 INTO G_REC_TAX_VALUE, G_REC_TOTAL_EXTENDED_PRICE
987 FROM oe_order_lines_all oel
988 WHERE oel.header_id=p_header_id
989 AND nvl(charge_periodicity_code,'ONE') = p_charge_periodicity_code
990 AND NVL(oel.cancelled_flag,'N') ='N';
991
992 EXCEPTION
993
994 WHEN too_many_rows THEN
995 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997 WHEN no_data_found THEN
998 IF l_debug_level > 0 THEN
999 oe_debug_pub.add('No Data found'||p_header_id,1) ;
1000 END IF;
1001 WHEN others THEN
1002 IF l_debug_level > 0 THEN
1003 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
1004 END IF;
1005 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006
1007 END GLOBAL_REC_TOTALS;
1008
1009 FUNCTION Rec_TAXES
1010 (
1011 p_header_id IN NUMBER
1012 )
1013 RETURN NUMBER
1014
1015 IS
1016
1017 --
1018 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1019 --
1020 BEGIN
1021
1022 RETURN(nvl(G_REC_TAX_VALUE,0));
1023
1024 END REC_TAXES;
1025
1026 FUNCTION REC_ORDER_SUBTOTALS
1027 (
1028 p_header_id IN NUMBER
1029 )
1030 RETURN NUMBER
1031
1032 IS
1033
1034 --
1035 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1036 --
1037 BEGIN
1038 RETURN(nvl(G_REC_TOTAL_EXTENDED_PRICE,0));
1039
1040 END REC_ORDER_SUBTOTALS;
1041
1042 FUNCTION REC_CHARGES
1043 (
1044 p_header_id IN NUMBER,
1045 p_charge_periodicity_code IN VARCHAR2
1046 )
1047 RETURN NUMBER
1048
1049 IS
1050 l_charge_total NUMBER;
1051 l_msg_count NUMBER := 0;
1052 l_msg_data VARCHAR2(2000):= NULL;
1053 l_return_status VARCHAR2(1);
1054
1055 --
1056 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1057 --
1058 BEGIN
1059
1060 IF l_debug_level > 0 THEN
1061 OE_DEBUG_PUB.ADD('CALLING THE CHARGES API TO GET THE ORDER TOTAL FOR CHARGES',1);
1062 OE_DEBUG_PUB.ADD('INPUTS PASSED',1);
1063 OE_DEBUG_PUB.ADD('p_header_id : '||p_header_id,1);
1064 OE_DEBUG_PUB.ADD('p_charge_periodicity_code : '||p_charge_periodicity_code,1);
1065 END IF;
1066
1067 OE_CHARGE_PVT.Get_Rec_Charge_Amount(
1068 p_api_version_number => 1.1 ,
1069 p_init_msg_list => FND_API.G_FALSE ,
1070 p_header_id => p_header_id ,
1071 p_line_id => NULL,
1072 p_all_charges => FND_API.G_TRUE ,
1073 p_charge_periodicity_code =>p_charge_periodicity_code,
1074 x_return_status => l_return_status ,
1075 x_msg_count => l_msg_count ,
1076 x_msg_data => l_msg_data ,
1077 x_charge_amount => l_charge_total
1078 );
1079 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1081 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1082 RAISE FND_API.G_EXC_ERROR;
1083 END IF;
1084
1085 RETURN l_charge_total;
1086
1087 END REC_CHARGES;
1088
1089 FUNCTION REC_PRICE_ADJUSTMENTS
1090 (
1091 p_header_id IN NUMBER,
1092 p_charge_periodicity_code IN VARCHAR2
1093 )
1094 RETURN NUMBER
1095
1096 IS
1097 adjustment_total NUMBER;
1098 --
1099 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1100 p_line_id NUMBER;
1101 --
1102 BEGIN
1103 adjustment_total:=oe_header_adj_util.get_rec_adj_total(p_header_id,p_line_id,p_charge_periodicity_code);
1104 RETURN(adjustment_total);
1105
1106 END REC_PRICE_ADJUSTMENTS;
1107
1108 PROCEDURE REC_ORDER_TOTALS
1109 (
1110 p_header_id IN NUMBER,
1111 p_charge_periodicity_code IN VARCHAR2,
1112 x_subtotal OUT NOCOPY NUMBER,
1113 x_discount OUT NOCOPY NUMBER,
1114 x_charges OUT NOCOPY NUMBER,
1115 x_tax OUT NOCOPY NUMBER,
1116 x_total OUT NOCOPY NUMBER
1117 )
1118 IS
1119
1120 --
1121 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1122 --
1123 BEGIN
1124
1125 IF l_debug_level > 0 THEN
1126 OE_DEBUG_PUB.ADD('ENTERING OE_OE_TOTALS_SUMMARY.REC_ORDER_TOTALS',1);
1127 OE_DEBUG_PUB.ADD('INPUTS TO THE API',1);
1128 OE_DEBUG_PUB.ADD('p_header_id :'||p_header_id,1);
1129 OE_DEBUG_PUB.ADD('p_charge_periodicity_code :'||p_charge_periodicity_code,1);
1130 END IF;
1131
1132 OE_OE_TOTALS_SUMMARY.Global_Rec_Totals(p_header_id => p_header_id,
1133 p_charge_periodicity_code => p_charge_periodicity_code);
1134
1135 x_tax := g_rec_tax_value;
1136 x_subtotal := g_rec_total_extended_price;
1137
1138 x_discount:=OE_OE_TOTALS_SUMMARY.Rec_Price_Adjustments
1139 (
1140 p_header_id,
1141 p_charge_periodicity_code
1142 );
1143
1144 x_charges:=OE_OE_TOTALS_SUMMARY.Rec_Charges
1145 (
1146 p_header_id,
1147 p_charge_periodicity_code
1148 );
1149
1150 x_total := x_tax + x_subtotal + x_charges;
1151
1152 IF l_debug_level > 0 THEN
1153 OE_DEBUG_PUB.ADD('EXITING OE_OE_TOTALS_SUMMARY.REC_ORDER_TOTALS',1);
1154 END IF;
1155
1156 END REC_ORDER_TOTALS;
1157
1158
1159 PROCEDURE GET_RECURRING_TOTALS
1160 (
1161 p_header_id IN NUMBER,
1162 x_rec_charges_tbl IN OUT NOCOPY OE_OE_TOTALS_SUMMARY.Rec_Charges_Tbl_Type)
1163 IS
1164 CURSOR C_rec_charge(g_header_id IN NUMBER) IS
1165 select distinct charge_periodicity_code
1166 from oe_order_lines_all
1167 where header_id = g_header_id
1168 and charge_periodicity_code is not null
1169 order by charge_periodicity_code;
1170
1171 l_code VARCHAR2(3);
1172 i NUMBER;
1173 x_tax NUMBER;
1174 x_subtotal NUMBER;
1175 x_charges NUMBER;
1176 l_debug_level CONSTANT NUMBER := Oe_Debug_Pub.g_debug_level;
1177
1178 BEGIN
1179
1180 IF l_debug_level > 0 THEN
1181 OE_DEBUG_PUB.ADD('ENTERING OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS',1);
1182 OE_DEBUG_PUB.ADD('INPUTS TO THE API',1);
1183 OE_DEBUG_PUB.ADD('p_header_id :'||p_header_id,1);
1184 END IF;
1185
1186 i := 1;
1187 OPEN C_rec_charge(p_header_id);
1188 LOOP
1189 FETCH C_rec_charge INTO l_code;
1190 EXIT WHEN C_rec_charge%NOTFOUND;
1191
1192 IF l_debug_level > 0 THEN
1193 OE_DEBUG_PUB.ADD('INSIDE LOOP FOR PERIODICITY_CODE :'||l_code,1);
1194 END IF;
1195
1196 x_rec_charges_tbl(i).charge_periodicity_code := l_code;
1197 x_rec_charges_tbl(i).charge_periodicity_meaning := OE_ID_TO_VALUE.Charge_Periodicity(l_code);
1198
1199
1200 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1201 (
1202 p_header_id => p_header_id,
1203 p_charge_periodicity_code => l_code
1204 );
1205 x_tax := g_rec_tax_value;
1206 x_subtotal := g_rec_total_extended_price;
1207
1208 x_rec_charges_tbl(i).rec_tax := x_tax;
1209 x_rec_charges_tbl(i).rec_subtotal := x_subtotal;
1210
1211 x_charges := OE_OE_TOTALS_SUMMARY.ReC_charges(p_header_id => p_header_id,
1212 p_charge_periodicity_code => l_code);
1213 x_rec_charges_tbl(i).rec_charges := x_charges;
1214 x_rec_charges_tbl(i).rec_total := x_subtotal + x_tax + x_charges;
1215
1216 IF l_debug_level > 0 THEN
1217 OE_DEBUG_PUB.ADD('TOTALS FOR PERIODICITY_CODE :'||l_code,1);
1218 OE_DEBUG_PUB.ADD('TAX :'||x_tax,1);
1219 OE_DEBUG_PUB.ADD('SUBTOTAL :'||x_subtotal,1);
1220 OE_DEBUG_PUB.ADD('CHARGES :'||x_charges,1);
1221 END IF;
1222
1223 i := i+1;
1224 END LOOP;
1225 CLOSE C_rec_charge;
1226
1227 IF l_debug_level > 0 THEN
1228 OE_DEBUG_PUB.ADD('EXITING OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS');
1229 END IF;
1230
1231 EXCEPTION
1232
1233 WHEN NO_DATA_FOUND THEN
1234 CLOSE C_rec_charge;
1235 Null;
1236
1237
1238 END Get_Recurring_Totals;
1239
1240 PROCEDURE GET_UI_RECURRING_TOTALS
1241 (
1242 p_header_id IN NUMBER,
1243 x_rec_charges_tbl IN OUT NOCOPY OE_OE_TOTALS_SUMMARY.Rec_Charges_Tbl_Type)
1244 IS
1245 CURSOR C_rec_charge(g_header_id IN NUMBER) IS
1246 select distinct nvl(charge_periodicity_code,'ONE')
1247 from oe_order_lines_all
1248 where header_id = g_header_id
1249 -- and charge_periodicity_code is not null
1250 order by nvl(charge_periodicity_code,'ONE');
1251
1252 l_code VARCHAR2(3);
1253 i NUMBER;
1254 x_tax NUMBER;
1255 x_subtotal NUMBER;
1256 x_charges NUMBER;
1257 l_debug_level CONSTANT NUMBER := Oe_Debug_Pub.g_debug_level;
1258
1259 BEGIN
1260
1261 IF l_debug_level > 0 THEN
1262 OE_DEBUG_PUB.ADD('ENTERING OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS',1);
1263 OE_DEBUG_PUB.ADD('INPUTS TO THE API',1);
1264 OE_DEBUG_PUB.ADD('p_header_id :'||p_header_id,1);
1265 END IF;
1266
1267 i := 1;
1268 OPEN C_rec_charge(p_header_id);
1269 LOOP
1270 FETCH C_rec_charge INTO l_code;
1271 EXIT WHEN C_rec_charge%NOTFOUND;
1272
1273 IF l_debug_level > 0 THEN
1274 OE_DEBUG_PUB.ADD('INSIDE LOOP FOR PERIODICITY_CODE :'||l_code,1);
1275 END IF;
1276
1277 x_rec_charges_tbl(i).charge_periodicity_code := l_code;
1278 IF (l_code <> 'ONE') THEN
1279 x_rec_charges_tbl(i).charge_periodicity_meaning := OE_ID_TO_VALUE.Charge_Periodicity(l_code);
1280 END IF;
1281
1282 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1283 (
1284 p_header_id => p_header_id,
1285 p_charge_periodicity_code => l_code
1286 );
1287 x_tax := nvl(g_rec_tax_value,0);
1288 x_subtotal := nvl(g_rec_total_extended_price,0);
1289
1290 x_rec_charges_tbl(i).rec_tax := x_tax;
1291 x_rec_charges_tbl(i).rec_subtotal := x_subtotal;
1292
1293 x_charges := OE_OE_TOTALS_SUMMARY.ReC_charges(p_header_id => p_header_id,
1294 p_charge_periodicity_code => l_code);
1295 x_rec_charges_tbl(i).rec_charges := x_charges;
1296 x_rec_charges_tbl(i).rec_total := nvl(x_subtotal,0) + nvl(x_tax,0) + nvl(x_charges,0);
1297
1298 IF l_debug_level > 0 THEN
1299 OE_DEBUG_PUB.ADD('TOTALS FOR PERIODICITY_CODE :'||l_code,1);
1300 OE_DEBUG_PUB.ADD('TAX :'||x_tax,1);
1301 OE_DEBUG_PUB.ADD('SUBTOTAL :'||x_subtotal,1);
1302 OE_DEBUG_PUB.ADD('CHARGES :'||x_charges,1);
1303 END IF;
1304
1305 i := i+1;
1306 END LOOP;
1307 CLOSE C_rec_charge;
1308
1309 IF l_debug_level > 0 THEN
1310 OE_DEBUG_PUB.ADD('EXITING OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS');
1311 END IF;
1312
1313 EXCEPTION
1314
1315 WHEN NO_DATA_FOUND THEN
1316 CLOSE C_rec_charge;
1317 Null;
1318
1319
1320 END Get_UI_Recurring_Totals;
1321
1322 PROCEDURE GET_MODEL_RECURRING_TOTALS
1323 (
1324 p_header_id IN NUMBER,
1325 p_line_id IN NUMBER,
1326 p_line_number IN NUMBER,
1327 x_rec_charges_tbl IN OUT NOCOPY OE_OE_TOTALS_SUMMARY.Rec_Charges_Tbl_Type
1328 )
1329 IS
1330
1331 CURSOR C_rec_charge(g_header_id IN NUMBER,p_line_number IN NUMBER) IS
1332 select distinct nvl(charge_periodicity_code,'ONE')
1333 from oe_order_lines_all
1334 where header_id = g_header_id
1335 -- and charge_periodicity_code is not null
1336 and line_number = p_line_number
1337 order by nvl(charge_periodicity_code,'ONE') desc;
1338
1339 CURSOR C_extended(p_header_id IN NUMBER,p_line_Id IN NUMBER,p_line_Number IN NUMBER,p_code IN VARCHAR2) IS
1340 SELECT NVL(Ordered_Quantity,0)*
1341 NVL(unit_selling_price,0) Line_details_total,tax_value,line_category_code
1342 FROM oe_order_lines_all
1343 WHERE header_id=p_header_id
1344 AND nvl(charge_periodicity_code,'ONE')=p_code -- added abghosh
1345 AND (line_number=p_line_number
1346 AND NVL(cancelled_flag,'N') ='N'
1347 OR (top_model_line_id is not null
1348 AND top_model_line_id=p_line_id
1349 -- AND charge_periodicity_code=p_code -- commented abghosh
1350 AND NVL(cancelled_flag,'N') ='N')
1351 OR (service_reference_line_id is not null
1352 AND service_reference_line_id=p_line_id
1353 AND NVL(cancelled_flag,'N') ='N'));
1354
1355 l_code VARCHAR2(3);
1356 i NUMBER;
1357 x_tax NUMBER;
1358 rec_tax Number :=0;
1359 x_subtotal NUMBER :=0;
1360 rec_subtotal Number :=0;
1361 x_charges NUMBER :=0;
1362 x_line_category_code VARCHAR2(30);
1363 x_tax_total NUMBER :=0;
1364 x_total NUMBER :=0;
1365 rec_charges NUMBER :=0;
1366 l_line_id NUMBER;
1367 l_debug_level CONSTANT NUMBER := Oe_Debug_Pub.g_debug_level;
1368
1369 BEGIN
1370
1371 IF l_debug_level > 0 THEN
1372 OE_DEBUG_PUB.ADD('ENTERING OE_OE_TOTALS_SUMMARY.GET_MODEL_RECURRING_TOTALS',1);
1373 OE_DEBUG_PUB.ADD('INPUTS TO THE API',1);
1374 OE_DEBUG_PUB.ADD('p_heder_id :'||p_header_id,1);
1375 OE_DEBUG_PUB.ADD('p_line_id_ :'||p_line_id,1);
1376 OE_DEBUG_PUB.ADD('p_line_number :'||p_line_number,1);
1377 END IF;
1378
1379 i := 1;
1380 OPEN C_rec_charge(p_header_id,p_line_number);
1381 LOOP
1382 FETCH C_rec_charge INTO l_code;
1383 EXIT WHEN C_rec_charge%NOTFOUND;
1384
1385 IF l_debug_level > 0 THEN
1386 OE_DEBUG_PUB.ADD('INSIDE OUTER LOOP FOR PERIODICITY_CODE :'||l_code,1);
1387 END IF;
1388
1389 x_rec_charges_tbl(i).charge_periodicity_code := l_code;
1390 IF (l_code <> 'ONE') THEN
1391 x_rec_charges_tbl(i).charge_periodicity_meaning := OE_ID_TO_VALUE.Charge_Periodicity(l_code);
1392 END IF;
1393 OPEN C_extended(p_header_id,p_line_id,p_line_number,l_code);
1394
1395 LOOP-- inner loop added abghosh
1396
1397 FETCH c_extended into x_subtotal,x_tax,x_line_category_code;
1398 EXIT WHEN C_extended%NOTFOUND;
1399 IF l_debug_level > 0 THEN
1400 OE_DEBUG_PUB.ADD('INSIDE INNER LOOP FOR PERIODICITY_CODE :'||l_code,1);
1401 END IF;
1402 IF x_line_category_code <> 'RETURN' THEN
1403 x_total:=x_total+x_subtotal;
1404 x_tax_total:=x_tax_total+x_tax;
1405 ELSIF x_line_category_code='RETURN' THEN
1406 x_total:=x_total-x_subtotal;
1407 x_tax_total:=x_tax_total-x_tax;
1408 END IF;
1409
1410 END LOOP;
1411 CLOSE C_Extended;
1412
1413 SELECT SUM(ROUND(
1414 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
1415 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
1416 DECODE(L.ORDERED_QUANTITY,0,0,-P.OPERAND),
1417 (-L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0))),
1418 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
1419 DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
1420 (L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
1421 )
1422 ,OE_ORDER_UTIL.G_Precision)
1423 )
1424 INTO x_charges
1425 FROM OE_PRICE_ADJUSTMENTS P,
1426 OE_ORDER_LINES_ALL L
1427 WHERE P.HEADER_ID = p_header_id
1428 AND P.LINE_ID = L.LINE_ID
1429 AND nvl(L.CHARGE_PERIODICITY_CODE,'ONE') = l_code
1430 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
1431 AND P.APPLIED_FLAG = 'Y'
1432 AND (l.line_number=p_line_number
1433 AND NVL(l.cancelled_flag,'N') ='N'
1434 OR (l.top_model_line_id is not null
1435 AND l.top_model_line_id=p_line_id
1436 AND NVL(l.cancelled_flag,'N') ='N')
1437 OR (l.service_reference_line_id is not null
1438 AND l.service_reference_line_id=p_line_id
1439 AND NVL(l.cancelled_flag,'N') ='N'));
1440
1441 rec_tax:=rec_tax+nvl(x_tax_total,0); -- 3 lines added abghosh summing up
1442 rec_charges:=rec_charges+nvl(x_charges,0);
1443 rec_subtotal:=rec_subtotal+x_total;
1444
1445
1446 x_rec_charges_tbl(i).rec_tax := rec_tax; -- adding in table added abghosh
1447 x_rec_charges_tbl(i).rec_subtotal := rec_subtotal;
1448 x_rec_charges_tbl(i).rec_charges := rec_charges;
1449 x_rec_charges_tbl(i).rec_total := rec_subtotal + rec_tax + rec_charges;
1450
1451 IF l_debug_level > 0 THEN
1452 OE_DEBUG_PUB.ADD('TOTALS FOR PERIODICITY_CODE :'||l_code,1);
1453 OE_DEBUG_PUB.ADD('TAX :'||rec_tax,1);
1454 OE_DEBUG_PUB.ADD('SUBTOTAL :'||rec_subtotal,1);
1455 OE_DEBUG_PUB.ADD('CHARGES :'||rec_charges,1);
1456 END IF;
1457
1458 i:=i+1; -- increment counter added abghosh
1459
1460
1461 rec_subtotal:=0; -- reset after one group of periodicity added abghosh
1462 rec_charges:=0;
1463 rec_tax:=0;
1464 x_total:=0;
1465 x_tax_total:=0;
1466 x_charges:=0;
1467
1468 END LOOP;
1469 CLOSE C_rec_charge;
1470
1471 IF l_debug_level > 0 THEN
1472 OE_DEBUG_PUB.ADD('EXITING OE_OE_TOTALS_SUMMARY.GET_MODEL_RECURRING_TOTALS',1);
1473 END IF;
1474
1475 END Get_Model_Recurring_Totals;
1476
1477 /* Recurring Charges */
1478
1479 --rc pviprana start
1480 PROCEDURE SET_ADJ_RECURRING_AMOUNTS
1481 (p_header_id IN NUMBER DEFAULT NULL,
1482 p_price_adjustment_id IN NUMBER DEFAULT NULL)
1483 IS
1484
1485 CURSOR c_charge_periodicity(p_header_id IN NUMBER) IS
1486 SELECT DISTINCT charge_periodicity_code
1487 FROM oe_order_lines_all
1488 WHERE header_id = p_header_id
1489 AND charge_periodicity_code is not null
1490 ORDER BY charge_periodicity_code;
1491
1492 l_code VARCHAR2(3);
1493 i PLS_INTEGER;
1494 l_header_id NUMBER;
1495 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1496
1497 BEGIN
1498
1499 IF l_debug_level > 0 THEN
1500 oe_debug_pub.add('ENTERING OE_OE_TOTALS_SUMMARY.SET_ADJ_RECURRING_AMOUNTS');
1501 END IF;
1502 IF p_price_adjustment_id IS NULL THEN
1503 RETURN;
1504 ELSE
1505 IF p_header_id IS NULL THEN
1506 BEGIN
1507 SELECT header_id INTO l_header_id
1508 FROM oe_price_adjustments
1509 WHERE price_adjustment_id = p_price_adjustment_id;
1510
1511 IF l_header_id IS NULL THEN
1512 RETURN;
1513 END IF;
1514 EXCEPTION
1515 WHEN OTHERS THEN
1516 IF l_debug_level > 0 THEN
1517 oe_debug_pub.add('Exception while querying for the adjustment record : ' || SQLERRM);
1518 END IF;
1519 RETURN;
1520 END;
1521 ELSE
1522 l_header_id := p_header_id;
1523 END IF;
1524 END IF;
1525
1526 IF l_debug_level > 0 THEN
1527 oe_debug_pub.add('l_header_id : ' || l_header_id);
1528 oe_debug_pub.add('p_price_adjustment_id : ' || p_price_adjustment_id);
1529 END IF;
1530
1531 G_RECURRING_AMOUNTS_TBL.DELETE;
1532 i := 1;
1533 OPEN c_charge_periodicity(l_header_id);
1534 LOOP
1535 FETCH c_charge_periodicity INTO l_code;
1536 EXIT WHEN c_charge_periodicity%NOTFOUND;
1537 G_RECURRING_AMOUNTS_TBL(i).charge_periodicity_code := l_code;
1538 G_RECURRING_AMOUNTS_TBL(i).charge_periodicity_meaning := OE_ID_TO_VALUE.Charge_Periodicity(l_code);
1539 G_RECURRING_AMOUNTS_TBL(i).recurring_amount := OE_HEADER_ADJ_UTIL.Get_Rec_Order_Adj_Total(l_header_id, p_price_adjustment_id, l_code);
1540
1541 IF l_debug_level > 0 THEN
1542 oe_debug_pub.add('***************************************************************');
1543 oe_debug_pub.add('charge_periodicity_code : ' || G_RECURRING_AMOUNTS_TBL(i).charge_periodicity_code);
1544 oe_debug_pub.add('charge_periodicity_meaning : ' || G_RECURRING_AMOUNTS_TBL(i).charge_periodicity_meaning);
1545 oe_debug_pub.add('recurring amount : ' || G_RECURRING_AMOUNTS_TBL(i).recurring_amount);
1546 oe_debug_pub.add('***************************************************************');
1547 END IF;
1548 i := i+1;
1549 END LOOP;
1550 CLOSE c_charge_periodicity;
1551
1552 IF l_debug_level > 0 THEN
1553 oe_debug_pub.add('EXITING OE_OE_TOTALS_SUMMARY.SET_ADJ_RECURRING_AMOUNTS');
1554 END IF;
1555
1556 END SET_ADJ_RECURRING_AMOUNTS;
1557
1558 PROCEDURE GET_ADJ_RECURRING_AMOUNTS
1559 (x_recurring_amounts_tbl IN OUT NOCOPY /* file.sql.39 change */ Recurring_Amounts_Tbl_Type)
1560 IS
1561 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1562 BEGIN
1563 IF l_debug_level > 0 THEN
1564 oe_debug_pub.add('ENTERING OE_OE_TOTALS_SUMMARY.GET_ADJ_RECURRING_AMOUNTS');
1565 END IF;
1566
1567 x_recurring_amounts_tbl := G_RECURRING_AMOUNTS_TBL;
1568
1569 IF l_debug_level > 0 THEN
1570 oe_debug_pub.add('EXITING OE_OE_TOTALS_SUMMARY.GET_ADJ_RECURRING_AMOUNTS');
1571 END IF;
1572 END;
1573
1574 --rc pviprana end
1575
1576 --rc preview/print
1577 FUNCTION PRN_REC_SUBTOTALS
1578 (
1579 p_header_id IN NUMBER,
1580 p_charge_periodicity_code IN VARCHAR2
1581 )
1582 RETURN NUMBER
1583 IS
1584 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1585 BEGIN
1586
1587 IF l_debug_level > 0 THEN
1588 oe_debug_pub.add('ENTERING REC_SUBTOTALS FUNCTION');
1589 oe_debug_pub.add('Header_id = '||p_header_id );
1590 oe_debug_pub.add('Charge periodicity code = '||p_charge_periodicity_code );
1591 END IF;
1592
1593 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1594 (
1595 p_header_id => p_header_id,
1596 p_charge_periodicity_code => p_charge_periodicity_code
1597 );
1598
1599 RETURN(G_REC_TOTAL_EXTENDED_PRICE);
1600
1601 EXCEPTION
1602 WHEN too_many_rows THEN
1603 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
1604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1605 WHEN no_data_found THEN
1606 IF l_debug_level > 0 THEN
1607 oe_debug_pub.add('No Data found'||p_header_id,1) ;
1608 END IF;
1609 WHEN others THEN
1610 IF l_debug_level > 0 THEN
1611 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
1612 END IF;
1613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1614
1615 END PRN_REC_SUBTOTALS;
1616
1617 FUNCTION PRN_REC_TAXES
1618 (
1619 p_header_id IN NUMBER,
1620 p_charge_periodicity_code IN VARCHAR2
1621 )
1622 RETURN NUMBER
1623 IS
1624 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1625 BEGIN
1626
1627 IF l_debug_level > 0 THEN
1628 oe_debug_pub.add('ENTERING REC_SUBTOTALS FUNCTION');
1629 oe_debug_pub.add('Header_id = '||p_header_id );
1630 oe_debug_pub.add('Charge periodicity code = '||p_charge_periodicity_code );
1631 END IF;
1632
1633 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1634 (
1635 p_header_id => p_header_id,
1636 p_charge_periodicity_code => p_charge_periodicity_code
1637 );
1638
1639 RETURN(g_rec_tax_value);
1640
1641 EXCEPTION
1642 WHEN too_many_rows THEN
1643 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
1644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1645 WHEN no_data_found THEN
1646 IF l_debug_level > 0 THEN
1647 oe_debug_pub.add('No Data found'||p_header_id,1) ;
1648 END IF;
1649 WHEN others THEN
1650 IF l_debug_level > 0 THEN
1651 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
1652 END IF;
1653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1654
1655 END PRN_REC_TAXES;
1656
1657 FUNCTION PRN_REC_TOTALS
1658 (
1659 p_header_id IN NUMBER,
1660 p_charge_periodicity_code IN VARCHAR2
1661 )
1662 RETURN NUMBER
1663 IS
1664 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1665 l_taxes NUMBER;
1666 l_subtotals NUMBER;
1667 l_charges NUMBER;
1668
1669 BEGIN
1670
1671 IF l_debug_level > 0 THEN
1672 oe_debug_pub.add('ENTERING REC_TOTALS FUNCTION');
1673 oe_debug_pub.add('Header_id = '||p_header_id );
1674 oe_debug_pub.add('Charge periodicity code = '||p_charge_periodicity_code );
1675 END IF;
1676
1677 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1678 (
1679 p_header_id => p_header_id,
1680 p_charge_periodicity_code => p_charge_periodicity_code
1681 );
1682
1683 l_taxes:= g_rec_tax_value;
1684 l_subtotals := G_REC_TOTAL_EXTENDED_PRICE;
1685 l_charges:= OE_OE_TOTALS_SUMMARY.ReC_charges(p_header_id => p_header_id,
1686 p_charge_periodicity_code => p_charge_periodicity_code );
1687
1688 RETURN(l_taxes + l_subtotals + l_charges);
1689
1690 EXCEPTION
1691 WHEN too_many_rows THEN
1692 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
1693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1694 WHEN no_data_found THEN
1695 IF l_debug_level > 0 THEN
1696 oe_debug_pub.add('No Data found'||p_header_id,1) ;
1697 END IF;
1698 WHEN others THEN
1699 IF l_debug_level > 0 THEN
1700 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
1701 END IF;
1702 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1703
1704 END PRN_REC_TOTALS;
1705
1706 END OE_OE_TOTALS_SUMMARY;
[Home] [Help]