1 PACKAGE BODY OE_OE_TOTALS_SUMMARY AS
2 /* $Header: OEXVTOTB.pls 120.9.12000000.3 2007/06/25 08:57:53 sgoli 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
355 --
356 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
357 --
358
359 BEGIN
360
361 IF p_header_id IS NOT NULL THEN
362 Is_fmt:= OE_ORDER_UTIL.Get_Precision(p_header_id=>p_header_id);
363 END IF;
364
365 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
366 OE_ORDER_UTIL.G_Precision:=2;
367 END IF;
368
369 /* changed following SQL for #3970425 */
370
371 SELECT SUM(nvl(ROUND(decode(oel.line_category_code,'RETURN',-oel.tax_value,oel.tax_value), OE_ORDER_UTIL.G_Precision),0)),
372 SUM(ROUND(nvl(oel.Ordered_Quantity,0) * (oel.unit_selling_price) * (decode(oel.line_category_code,'RETURN',-1,1)),OE_ORDER_UTIL.G_Precision))
373 INTO G_TAX_VALUE, G_TOTAL_EXTENDED_PRICE
374 FROM oe_order_lines_all oel
375 WHERE oel.header_id=p_header_id
376 AND charge_periodicity_code is NULL -- added for recurring charges
377 AND NVL(oel.cancelled_flag,'N') ='N';
378
379 EXCEPTION
380 WHEN too_many_rows THEN
381 IF l_debug_level > 0 THEN
382 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
383 END IF;
384 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
385 WHEN no_data_found THEN
386 IF l_debug_level > 0 THEN
387 oe_debug_pub.add('No Data found'||p_header_id,1) ;
388 END IF;
389 WHEN others THEN
390 IF l_debug_level > 0 THEN
391 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
392 END IF;
393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
394 END GLOBAL_TOTALS;
395
396 FUNCTION CONFIG_TOTALS
397 (
398 p_line_id IN NUMBER
399 )
400 RETURN NUMBER
401 IS
402 l_config_total NUMBER;
403 --
404 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
405 --
406 BEGIN
407 SELECT SUM(nvl(Ordered_Quantity,0)
408 *(unit_selling_price))
409 INTO l_config_total
410 FROM oe_order_lines_all
411 WHERE line_id=p_line_id
412 AND NVL(cancelled_flag,'N') ='N';
413
414 RETURN(l_config_total);
415
416 EXCEPTION
417 WHEN too_many_rows THEN
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419
420 WHEN no_data_found THEN
421 Null;
422
423 WHEN others THEN
424 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
425
426 END CONFIG_TOTALS;
427
428 FUNCTION TOTAL_ORDERED_QTY
429 (
430 p_header_id IN NUMBER,
431 p_line_number IN NUMBER
432 )
433 RETURN NUMBER
434 IS
435 l_ordered_qty NUMBER :=0;
436 CURSOR C1(p_header_id NUMBER,p_line_number NUMBER) IS
437 SELECT NVL(Ordered_quantity,0) Qty
438 FROM oe_order_lines_all
439 WHERE header_id=p_header_id
440 AND NVL(cancelled_flag,'N') ='N'
441 AND line_number=p_line_number
442 AND item_type_code in ('STANDARD','MODEL','KIT')
443 -- And option_number is null
444 and line_id = nvl(top_model_line_id,line_id);
445
446 --
447 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
448 --
449 BEGIN
450
451 FOR Lines IN C1(p_header_id,p_line_number)
452 LOOP
453 l_ordered_qty:=l_ordered_qty+lines.qty;
454 END LOOP;
455 RETURN(l_ordered_qty);
456 EXCEPTION
457 WHEN no_data_found THEN
458 Null;
459 WHEN too_many_rows THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 WHEN others THEN
462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
463 END TOTAL_ORDERED_QTY;
464
465 PROCEDURE ORDER_TOTALS
466 (
467 p_header_id IN NUMBER,
468 p_subtotal OUT NOCOPY NUMBER,
469
470 p_discount OUT NOCOPY NUMBER,
471
472 p_charges OUT NOCOPY NUMBER,
473
474 p_tax OUT NOCOPY NUMBER
475
476 )
477 IS
478
479 --
480 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
481 --
482 BEGIN
483 OE_OE_TOTALS_SUMMARY.Global_Totals
484 (
485 p_header_id
486 );
487 p_subtotal:=OE_OE_TOTALS_SUMMARY.Order_Subtotals
488 (
489 p_header_id
490 );
491
492 p_discount:=OE_OE_TOTALS_SUMMARY.Price_Adjustments
493 (
494 p_header_id
495 );
496 p_charges:=OE_OE_TOTALS_SUMMARY.Charges
497 (
498 p_header_id
499 );
500 p_tax:=OE_OE_TOTALS_SUMMARY.Taxes
501 (
502 p_header_id
503 );
504
505
506 END ORDER_TOTALS;
507
508
509 /* The function PRT_ORDER_TOTAL is used by the view ONT_PRT_ORDER_HEADERS_V to calculate the order total */
510
511 FUNCTION PRT_ORDER_TOTAL
512 (
513 p_header_id IN NUMBER
514 )
515 RETURN NUMBER
516 IS
517
518 l_subtotal NUMBER;
519 l_discount NUMBER;
520 l_charges NUMBER;
521 l_tax NUMBER;
522
523 --
524 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
525 --
526 BEGIN
527
528 OE_OE_TOTALS_SUMMARY.Global_Totals(p_header_id);
529
530 l_subtotal:=OE_OE_TOTALS_SUMMARY.Order_Subtotals(p_header_id);
531
532 -- performance bug 4060810: disabling this calculation as it is not used
533 -- l_discount:=OE_OE_TOTALS_SUMMARY.Price_Adjustments(p_header_id);
534 l_charges:=OE_OE_TOTALS_SUMMARY.Charges(p_header_id);
535 l_tax:=OE_OE_TOTALS_SUMMARY.Taxes(p_header_id);
536
537 RETURN (l_subtotal + l_charges + l_tax);
538
539 EXCEPTION
540 WHEN OTHERS THEN
541 RETURN NULL;
542 END PRT_ORDER_TOTAL;
543
544 FUNCTION OUTBOUND_ORDER_TOTAL
545 (
546 p_header_id IN NUMBER,
547 p_to_exclude_commitment IN VARCHAR2 DEFAULT NULL, -- 4013565
548 p_total_type IN VARCHAR2 DEFAULT NULL, --4013565
549 p_all_lines IN VARCHAR2 DEFAULT NULL
550 ) RETURN NUMBER
551 IS
552 l_order_total NUMBER;
553 l_tax_total NUMBER;
554 l_charges NUMBER;
555 l_outbound_total NUMBER;
556 l_commitment_total NUMBER;
557 l_chgs_w_line_id NUMBER := 0;
558 l_chgs_wo_line_id NUMBER := 0;
559 --
560 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
561 --
562 Is_fmt BOOLEAN; --5716270
563 BEGIN
564
565 Is_fmt:= OE_ORDER_UTIL.Get_Precision(p_header_id=>p_header_id); --5716270
566 -- bug4013565
567 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
568 OE_ORDER_UTIL.G_Precision:=2;
569 END IF;
570
571
572 -- added ROUND in someplaces below for 4013565
573
574 -- Select the Tax Total and Outbound Extended Price
575 -- p_all_lines 'Y' means to include all lines including all open or closed ones.
576 -- Currently this parameter is only set 'Y' when calling from Payments form.
577
578 SELECT
579 SUM(ROUND(nvl(ool.tax_value,0), OE_ORDER_UTIL.G_Precision))
580 , SUM(ROUND(nvl(ool.Ordered_Quantity,0)
581 *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))
582 INTO
583 l_tax_total
584 , l_order_total
585 FROM oe_order_lines_all ool
586 WHERE ool.header_id = p_header_id
587 AND ( (ool.open_flag = 'Y' AND p_all_lines is null)
588 OR nvl(p_all_lines, 'N') = 'Y' )
589 AND ool.line_category_code <> 'RETURN'
590 AND ool.charge_periodicity_code is null -- Added for Recurring Charges
591 AND NOT EXISTS
592 (SELECT 'Non Invoiceable Item Line'
593 FROM mtl_system_items mti
594 WHERE mti.inventory_item_id = ool.inventory_item_id
595 AND mti.organization_id = nvl(ool.ship_from_org_id,
596 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
597 AND (mti.invoiceable_item_flag = 'N'
598 OR mti.invoice_enabled_flag = 'N'));
599
600 IF OE_Commitment_Pvt.Do_Commitment_Sequencing THEN
601 -- Select the committment applied amount if Commitment Sequencing "On"
602 SELECT SUM(ROUND(nvl(op.commitment_applied_amount,0), OE_ORDER_UTIL.G_Precision))
603 INTO l_commitment_total
604 FROM oe_payments op
605 WHERE op.header_id = p_header_id
606 AND NOT EXISTS
607 (SELECT 'Non Invoiceable Item Line'
608 FROM mtl_system_items mti, oe_order_lines_all ool
609 WHERE ool.line_id = op.line_id
610 AND mti.inventory_item_id = ool.inventory_item_id
611 AND mti.organization_id = nvl(ool.ship_from_org_id,
612 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
613 AND (mti.invoiceable_item_flag = 'N'
614 OR mti.invoice_enabled_flag = 'N'));
615 ELSE
616 -- Select the Outbound Extended Price for lines that have committment
617 SELECT SUM(ROUND(nvl(ool.Ordered_Quantity,0) *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))
618 INTO l_commitment_total
619 FROM oe_order_lines_all ool
620 WHERE ool.header_id = p_header_id
621 AND ool.commitment_id is not null
622 AND ( (ool.open_flag = 'Y' AND p_all_lines is null)
623 OR nvl(p_all_lines, 'N') = 'Y' )
624 AND ool.charge_periodicity_code is null -- Added for Recurring Charges
625 AND ool.line_category_code <> 'RETURN'
626 AND NOT EXISTS
627 (SELECT 'Non Invoiceable Item Line'
628 FROM mtl_system_items mti
629 WHERE 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 END IF;
635
636 -- Select the Outbound Charges Total
637
638 SELECT SUM(
639 ROUND(
640 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-P.OPERAND,P.OPERAND), OE_ORDER_UTIL.G_Precision
641 )
642 )
643 INTO l_chgs_wo_line_id
644 FROM OE_PRICE_ADJUSTMENTS P
645 WHERE P.HEADER_ID = p_header_id
646 AND P.LINE_ID IS NULL
647 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
648 AND P.APPLIED_FLAG = 'Y'
649 --Bug 6072691
650 --AND NVL(P.INVOICED_FLAG, 'N') = 'N';
651 AND (( NVL(P.INVOICED_FLAG,'N') = 'Y'
652 AND NVL(p_total_type,'OTHERS') = 'INV_CHARGES'
653 )
654 OR
655 ( NVL(P.INVOICED_FLAG,'N') <> 'Y'
656 AND NVL(p_total_type,'OTHERS') = 'CHARGES'
657 )
658 OR
659 (
660 NVL(p_total_type,'OTHERS') NOT IN ('INV_CHARGES','CHARGES')
661 )
662 );
663
664 SELECT SUM(
665 ROUND(
666 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
667 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
668 -P.OPERAND,
669 (-L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)),
670 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
671 P.OPERAND,
672 (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT))
673 )
674 ,OE_ORDER_UTIL.G_Precision
675 )
676 )
677 INTO l_chgs_w_line_id
678 FROM OE_PRICE_ADJUSTMENTS P,
679 OE_ORDER_LINES_ALL L
680 WHERE P.HEADER_ID = p_header_id
681 AND P.LINE_ID = L.LINE_ID
682 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
683 AND P.APPLIED_FLAG = 'Y'
684 AND L.charge_periodicity_code is null -- Added for Recurring Charges
685 AND L.header_id = p_header_id
686 AND ( (L.open_flag = 'Y' AND p_all_lines is null)
687 OR nvl(p_all_lines, 'N') = 'Y' )
688 AND L.line_category_code <> 'RETURN'
689 AND NOT EXISTS
690 (SELECT 'Non Invoiceable Item Line'
691 FROM MTL_SYSTEM_ITEMS MTI
692 WHERE MTI.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
693 AND MTI.ORGANIZATION_ID = NVL(L.SHIP_FROM_ORG_ID,
694 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', L.org_id))
695 AND (MTI.INVOICEABLE_ITEM_FLAG = 'N'
696 OR MTI.INVOICE_ENABLED_FLAG = 'N'))
697 --Bug 6072691
698 --AND NVL(P.INVOICED_FLAG, 'N') = 'N';
699 AND (( NVL(P.INVOICED_FLAG,'N') = 'Y'
700 AND NVL(p_total_type,'OTHERS') = 'INV_CHARGES'
701 )
702 OR
703 ( NVL(P.INVOICED_FLAG,'N') <> 'Y'
704 AND NVL(p_total_type,'OTHERS') = 'CHARGES'
705 )
706 OR
707 (
708 NVL(p_total_type,'OTHERS') NOT IN ('INV_CHARGES','CHARGES')
709 )
710 );
711
712 l_charges := nvl(l_chgs_wo_line_id,0) + nvl(l_chgs_w_line_id,0);
713
714 IF nvl(p_to_exclude_commitment, 'Y') = 'Y' THEN
715 l_outbound_total := nvl(l_order_total, 0) + nvl(l_tax_total, 0)
716 + nvl(l_charges, 0) - nvl(l_commitment_total,0);
717 ELSE
718 l_outbound_total := nvl(l_order_total, 0) + nvl(l_tax_total, 0)
719 + nvl(l_charges, 0);
720 END IF;
721
722 IF l_debug_level > 0 THEN
723 oe_debug_pub.add( 'CALCULATING THE TOTAL AMOUNT TO BE AUTHORIZED FOR THIS ORDER ' , 1 ) ;
724 END IF;
725 IF l_debug_level > 0 THEN
726 oe_debug_pub.add( 'ORDER TOTAL -> '||TO_CHAR ( L_ORDER_TOTAL ) , 1 ) ;
727 END IF;
728 IF l_debug_level > 0 THEN
729 oe_debug_pub.add( 'TAX TOTAL -> '||TO_CHAR ( L_TAX_TOTAL ) , 1 ) ;
730 END IF;
731 IF l_debug_level > 0 THEN
732 oe_debug_pub.add( 'COMMITMENTS -> '||TO_CHAR ( L_COMMITMENT_TOTAL ) , 1 ) ;
733 END IF;
734 IF l_debug_level > 0 THEN
735 oe_debug_pub.add( 'OTHER CHARGES -> '||TO_CHAR ( L_CHARGES ) , 1 ) ;
736 END IF;
737 IF l_debug_level > 0 THEN
738 oe_debug_pub.add( 'AMOUNT TO BE AUTHORIZED => '||TO_CHAR ( L_OUTBOUND_TOTAL ) , 1 ) ;
739 END IF;
740
741 -- bug 4013565
742 IF p_total_type = 'TAXES' THEN
743 RETURN nvl(l_tax_total, 0);
744 --Bug 6072691
745 ELSIF p_total_type IN('CHARGES','INV_CHARGES') THEN
746 RETURN nvl(l_charges, 0);
747 ELSIF p_total_type = 'SUBTOTAL' THEN
748 RETURN nvl(l_order_total,0);
749 ELSE
750 RETURN (l_outbound_total);
751 END IF;
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 IF l_debug_level > 0 THEN
756 oe_debug_pub.add( 'FROM OUTBOUND TOTAL OTHERS' ) ;
757 END IF;
758 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
759
760 END OUTBOUND_ORDER_TOTAL;
761
762 FUNCTION OUTBOUND_ORDER_SUBTOTAL
763 (
764 p_header_id IN NUMBER
765 ) RETURN NUMBER
766 IS
767 l_order_subtotal NUMBER;
768 --
769 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
770 --
771 BEGIN
772 -- Select the Outbound Extended Price
773 BEGIN
774 SELECT SUM(nvl(ool.Ordered_Quantity,0)
775 *(ool.unit_selling_price))
776 INTO l_order_subtotal
777 FROM oe_order_lines_all ool
778 WHERE ool.header_id = p_header_id
779 AND ool.open_flag = 'Y'
780 AND ool.charge_periodicity_code is null -- Added for Recurring Charges
781 AND ool.line_category_code <> 'RETURN'
782 AND NOT EXISTS
783 (SELECT 'Non Invoiceable Item Line'
784 FROM mtl_system_items mti
785 WHERE mti.inventory_item_id = ool.inventory_item_id
786 AND mti.organization_id = nvl(ool.ship_from_org_id,
787 oe_sys_parameters.value('MASTER_ORGANIZATION_ID', ool.org_id))
788 AND (mti.invoiceable_item_flag = 'N'
789 OR mti.invoice_enabled_flag = 'N'));
790 EXCEPTION
791 WHEN no_data_found THEN
792 l_order_subtotal := 0;
793 END;
794
795 IF l_debug_level > 0 THEN
796 oe_debug_pub.add( 'CALCULATING THE ORDER SUBTOTAL AMOUNT FOR THIS ORDER ' , 1 ) ;
797 oe_debug_pub.add( 'ORDER TOTAL -> '||TO_CHAR ( L_ORDER_SUBTOTAL ) , 1 ) ;
798 END IF;
799 RETURN (nvl(l_order_subtotal,0));
800
801 EXCEPTION
802 WHEN OTHERS THEN
803 IF l_debug_level > 0 THEN
804 oe_debug_pub.add( 'FROM OUTBOUND SUBTOTAL OTHERS' ) ;
805 END IF;
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807
808 END OUTBOUND_ORDER_SUBTOTAL;
809
810 --------------------------------------------------------------------------------------
811 --Called by pricing sourcing rules.
812 --Pricing order amount is always based on sum of unit list price NOT unit selling price
813 ---------------------------------------------------------------------------------------
814 Function Get_Order_Amount(p_header_id In Number) Return Number
815 Is
816 orders_total NUMBER;
817 returns_total NUMBER;
818 l_order_amount NUMBER;
819 --
820 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
821 --
822 BEGIN
823 IF l_debug_level > 0 THEN
824 oe_debug_pub.add( 'ENTERING OE_OE_TOTALS_SUMMARY.GET_ORDER_AMOUNT' ) ;
825 END IF;
826
827 -- combining 2 SQLs into one for performance
828 SELECT SUM(DECODE(line_category_code, 'RETURN', 0, nvl(Ordered_Quantity,0)*(unit_list_price))),
829 SUM(DECODE(line_category_code, 'RETURN', nvl(Ordered_Quantity,0)*(unit_list_price),0))
830 INTO orders_total, returns_total
831 FROM oe_order_lines_all
832 WHERE header_id=p_header_id
833 AND charge_periodicity_code is null -- Added for Recurring CHarges
834 AND NVL(cancelled_flag,'N') ='N';
835
836 l_order_amount:=NVL(orders_total,0)-NVL(returns_total,0);
837
838 IF l_debug_level > 0 THEN
839 oe_debug_pub.add( ' ORDER TOTAL:='||ORDERS_TOTAL ) ;
840 END IF;
841 IF l_debug_level > 0 THEN
842 oe_debug_pub.add( ' RETURN TOTAL:='||RETURNS_TOTAL ) ;
843 END IF;
844 IF l_debug_level > 0 THEN
845 oe_debug_pub.add( ' ORDER AMOUNT:='||L_ORDER_AMOUNT ) ;
846 END IF;
847 IF l_debug_level > 0 THEN
848 oe_debug_pub.add( 'LEAVING OE_OE_TOTALS_SUMMARY.GET_ORDER_AMOUNT' ) ;
849 END IF;
850
851 return l_order_amount;
852 EXCEPTION
853 WHEN too_many_rows THEN
854 IF l_debug_level > 0 THEN
855 oe_debug_pub.add( ' TOO MANY ROWS' ) ;
856 END IF;
857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
858
859 WHEN no_data_found THEN
860 IF l_debug_level > 0 THEN
861 oe_debug_pub.add( ' FROM NO DATA FOUND' ) ;
862 END IF;
863
864
865 WHEN others THEN
866 IF l_debug_level > 0 THEN
867 oe_debug_pub.add( ' FROM OTHERS' ) ;
868 END IF;
869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
870
871 END get_order_amount;
872
873
874
875
876 -- This function is used to calculate the discount percentage for the line
877 -- items in a sales order. This uses the unit selling price and the unit
878 -- list price to calculate the discount
879 -- Input: unit_list_price, unit_selling_price Output: discount percent
880 -- Called from: OE_PRN_ORDER_LINES_V view
881 --
882
883 FUNCTION GET_DISCOUNT(p_unit_list_price IN number,p_unit_selling_price IN NUMBER)
884 RETURN NUMBER IS
885
886 l_discount_pct NUMBER;
887 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
888
889 BEGIN
890 IF l_debug_level > 0 THEN
891 oe_debug_pub.add('ENTER GET_DISCOUNT PROCEDURE');
892 oe_debug_pub.add('Unit List Price = '||p_unit_list_price );
893 oe_debug_pub.add('Unit Selling Price = '||p_unit_selling_price );
894 END IF;
895
896 IF ((p_unit_list_price IS NOT NULL AND p_unit_list_price > 0) AND
897 (p_unit_selling_price IS NOT NULL AND p_unit_selling_price > 0) AND
898 (p_unit_list_price >= p_unit_selling_price)) THEN
899 l_discount_pct := round(((p_unit_list_price-p_unit_selling_price)/p_unit_list_price)*100);
900
901 IF l_debug_level > 0 THEN
902 oe_debug_pub.add('Discount Percent = '||l_discount_pct);
903 oe_debug_pub.add('EXIT GET_DISCOUNT PROCEDURE');
904 END IF;
905 -- IF l_discount_pct < 1 THEN
906 -- RETURN NULL;
907 -- ELSE
908 RETURN l_discount_pct;
909 -- END IF;
910 ELSE
911 IF l_debug_level > 0 THEN
912 oe_debug_pub.add('EXIT GET_DISCOUNT PROCEDURE');
913 END IF;
914
915 RETURN NULL;
916 END IF;
917 EXCEPTION
918 WHEN OTHERS THEN
919 IF l_debug_level > 0 THEN
920 oe_debug_pub.add( 'GET_DISCOUNT: WHEN OTHERS Exception' ) ;
921 END IF;
922 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
923 THEN
924 OE_MSG_PUB.Add_Exc_Msg
925 ( G_PKG_NAME
926 , 'GET_DISCOUNT'
927 );
928 END IF;
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END GET_DISCOUNT;
931
932
933 /* Recurring charges */
934
935 PROCEDURE GLOBAL_REC_TOTALS
936 (
937 p_header_id IN NUMBER,
938 p_charge_periodicity_code IN VARCHAR2
939 )
940 IS
941
942 Is_fmt BOOLEAN;
943
944 --
945 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
946 --
947
948 BEGIN
949
950 IF p_header_id IS NOT NULL THEN
951 Is_fmt:= OE_ORDER_UTIL.Get_Precision(p_header_id=>p_header_id);
952 END IF;
953
954 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
955 OE_ORDER_UTIL.G_Precision:=2;
956 END IF;
957
958 /* changed following SQL for #3970425 */
959
960 SELECT SUM(nvl(ROUND(decode(oel.line_category_code,'RETURN',-oel.tax_value,oel.tax_value), OE_ORDER_UTIL.G_Precision),0)),
961 SUM(ROUND(nvl(oel.Ordered_Quantity,0) * (oel.unit_selling_price) * (decode(oel.line_category_code,'RETURN',-1,1)),OE_ORDER_UTIL.G_Precision))
962 INTO G_REC_TAX_VALUE, G_REC_TOTAL_EXTENDED_PRICE
963 FROM oe_order_lines_all oel
964 WHERE oel.header_id=p_header_id
965 AND nvl(charge_periodicity_code,'ONE') = p_charge_periodicity_code
966 AND NVL(oel.cancelled_flag,'N') ='N';
967
968 EXCEPTION
969
970 WHEN too_many_rows THEN
971 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
973 WHEN no_data_found THEN
974 IF l_debug_level > 0 THEN
975 oe_debug_pub.add('No Data found'||p_header_id,1) ;
976 END IF;
977 WHEN others THEN
978 IF l_debug_level > 0 THEN
979 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
980 END IF;
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
982
983 END GLOBAL_REC_TOTALS;
984
985 FUNCTION Rec_TAXES
986 (
987 p_header_id IN NUMBER
988 )
989 RETURN NUMBER
990
991 IS
992
993 --
994 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
995 --
996 BEGIN
997
998 RETURN(nvl(G_REC_TAX_VALUE,0));
999
1000 END REC_TAXES;
1001
1002 FUNCTION REC_ORDER_SUBTOTALS
1003 (
1004 p_header_id IN NUMBER
1005 )
1006 RETURN NUMBER
1007
1008 IS
1009
1010 --
1011 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1012 --
1013 BEGIN
1014 RETURN(nvl(G_REC_TOTAL_EXTENDED_PRICE,0));
1015
1016 END REC_ORDER_SUBTOTALS;
1017
1018 FUNCTION REC_CHARGES
1019 (
1020 p_header_id IN NUMBER,
1021 p_charge_periodicity_code IN VARCHAR2
1022 )
1023 RETURN NUMBER
1024
1025 IS
1026 l_charge_total NUMBER;
1027 l_msg_count NUMBER := 0;
1028 l_msg_data VARCHAR2(2000):= NULL;
1029 l_return_status VARCHAR2(1);
1030
1031 --
1032 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1033 --
1034 BEGIN
1035
1036 IF l_debug_level > 0 THEN
1037 OE_DEBUG_PUB.ADD('CALLING THE CHARGES API TO GET THE ORDER TOTAL FOR CHARGES',1);
1038 OE_DEBUG_PUB.ADD('INPUTS PASSED',1);
1039 OE_DEBUG_PUB.ADD('p_header_id : '||p_header_id,1);
1040 OE_DEBUG_PUB.ADD('p_charge_periodicity_code : '||p_charge_periodicity_code,1);
1041 END IF;
1042
1043 OE_CHARGE_PVT.Get_Rec_Charge_Amount(
1044 p_api_version_number => 1.1 ,
1045 p_init_msg_list => FND_API.G_FALSE ,
1046 p_header_id => p_header_id ,
1047 p_line_id => NULL,
1048 p_all_charges => FND_API.G_TRUE ,
1049 p_charge_periodicity_code =>p_charge_periodicity_code,
1050 x_return_status => l_return_status ,
1051 x_msg_count => l_msg_count ,
1052 x_msg_data => l_msg_data ,
1053 x_charge_amount => l_charge_total
1054 );
1055 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1056 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1057 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1058 RAISE FND_API.G_EXC_ERROR;
1059 END IF;
1060
1061 RETURN l_charge_total;
1062
1063 END REC_CHARGES;
1064
1065 FUNCTION REC_PRICE_ADJUSTMENTS
1066 (
1067 p_header_id IN NUMBER,
1068 p_charge_periodicity_code IN VARCHAR2
1069 )
1070 RETURN NUMBER
1071
1072 IS
1073 adjustment_total NUMBER;
1074 --
1075 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1076 p_line_id NUMBER;
1077 --
1078 BEGIN
1079 adjustment_total:=oe_header_adj_util.get_rec_adj_total(p_header_id,p_line_id,p_charge_periodicity_code);
1080 RETURN(adjustment_total);
1081
1082 END REC_PRICE_ADJUSTMENTS;
1083
1084 PROCEDURE REC_ORDER_TOTALS
1085 (
1086 p_header_id IN NUMBER,
1087 p_charge_periodicity_code IN VARCHAR2,
1088 x_subtotal OUT NOCOPY NUMBER,
1089 x_discount OUT NOCOPY NUMBER,
1090 x_charges OUT NOCOPY NUMBER,
1091 x_tax OUT NOCOPY NUMBER,
1092 x_total OUT NOCOPY NUMBER
1093 )
1094 IS
1095
1096 --
1097 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1098 --
1099 BEGIN
1100
1101 IF l_debug_level > 0 THEN
1102 OE_DEBUG_PUB.ADD('ENTERING OE_OE_TOTALS_SUMMARY.REC_ORDER_TOTALS',1);
1103 OE_DEBUG_PUB.ADD('INPUTS TO THE API',1);
1104 OE_DEBUG_PUB.ADD('p_header_id :'||p_header_id,1);
1105 OE_DEBUG_PUB.ADD('p_charge_periodicity_code :'||p_charge_periodicity_code,1);
1106 END IF;
1107
1108 OE_OE_TOTALS_SUMMARY.Global_Rec_Totals(p_header_id => p_header_id,
1109 p_charge_periodicity_code => p_charge_periodicity_code);
1110
1111 x_tax := g_rec_tax_value;
1112 x_subtotal := g_rec_total_extended_price;
1113
1114 x_discount:=OE_OE_TOTALS_SUMMARY.Rec_Price_Adjustments
1115 (
1116 p_header_id,
1117 p_charge_periodicity_code
1118 );
1119
1120 x_charges:=OE_OE_TOTALS_SUMMARY.Rec_Charges
1121 (
1122 p_header_id,
1123 p_charge_periodicity_code
1124 );
1125
1126 x_total := x_tax + x_subtotal + x_charges;
1127
1128 IF l_debug_level > 0 THEN
1129 OE_DEBUG_PUB.ADD('EXITING OE_OE_TOTALS_SUMMARY.REC_ORDER_TOTALS',1);
1130 END IF;
1131
1132 END REC_ORDER_TOTALS;
1133
1134
1135 PROCEDURE GET_RECURRING_TOTALS
1136 (
1137 p_header_id IN NUMBER,
1138 x_rec_charges_tbl IN OUT NOCOPY OE_OE_TOTALS_SUMMARY.Rec_Charges_Tbl_Type)
1139 IS
1140 CURSOR C_rec_charge(g_header_id IN NUMBER) IS
1141 select distinct charge_periodicity_code
1142 from oe_order_lines_all
1143 where header_id = g_header_id
1144 and charge_periodicity_code is not null
1145 order by charge_periodicity_code;
1146
1147 l_code VARCHAR2(3);
1148 i NUMBER;
1149 x_tax NUMBER;
1150 x_subtotal NUMBER;
1151 x_charges NUMBER;
1152 l_debug_level CONSTANT NUMBER := Oe_Debug_Pub.g_debug_level;
1153
1154 BEGIN
1155
1156 IF l_debug_level > 0 THEN
1157 OE_DEBUG_PUB.ADD('ENTERING OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS',1);
1158 OE_DEBUG_PUB.ADD('INPUTS TO THE API',1);
1159 OE_DEBUG_PUB.ADD('p_header_id :'||p_header_id,1);
1160 END IF;
1161
1162 i := 1;
1163 OPEN C_rec_charge(p_header_id);
1164 LOOP
1165 FETCH C_rec_charge INTO l_code;
1166 EXIT WHEN C_rec_charge%NOTFOUND;
1167
1168 IF l_debug_level > 0 THEN
1169 OE_DEBUG_PUB.ADD('INSIDE LOOP FOR PERIODICITY_CODE :'||l_code,1);
1170 END IF;
1171
1172 x_rec_charges_tbl(i).charge_periodicity_code := l_code;
1173 x_rec_charges_tbl(i).charge_periodicity_meaning := OE_ID_TO_VALUE.Charge_Periodicity(l_code);
1174
1175
1176 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1177 (
1178 p_header_id => p_header_id,
1179 p_charge_periodicity_code => l_code
1180 );
1181 x_tax := g_rec_tax_value;
1182 x_subtotal := g_rec_total_extended_price;
1183
1184 x_rec_charges_tbl(i).rec_tax := x_tax;
1185 x_rec_charges_tbl(i).rec_subtotal := x_subtotal;
1186
1187 x_charges := OE_OE_TOTALS_SUMMARY.ReC_charges(p_header_id => p_header_id,
1188 p_charge_periodicity_code => l_code);
1189 x_rec_charges_tbl(i).rec_charges := x_charges;
1190 x_rec_charges_tbl(i).rec_total := x_subtotal + x_tax + x_charges;
1191
1192 IF l_debug_level > 0 THEN
1193 OE_DEBUG_PUB.ADD('TOTALS FOR PERIODICITY_CODE :'||l_code,1);
1194 OE_DEBUG_PUB.ADD('TAX :'||x_tax,1);
1195 OE_DEBUG_PUB.ADD('SUBTOTAL :'||x_subtotal,1);
1196 OE_DEBUG_PUB.ADD('CHARGES :'||x_charges,1);
1197 END IF;
1198
1199 i := i+1;
1200 END LOOP;
1201 CLOSE C_rec_charge;
1202
1203 IF l_debug_level > 0 THEN
1204 OE_DEBUG_PUB.ADD('EXITING OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS');
1205 END IF;
1206
1207 EXCEPTION
1208
1209 WHEN NO_DATA_FOUND THEN
1210 CLOSE C_rec_charge;
1211 Null;
1212
1213
1214 END Get_Recurring_Totals;
1215
1216 PROCEDURE GET_UI_RECURRING_TOTALS
1217 (
1218 p_header_id IN NUMBER,
1219 x_rec_charges_tbl IN OUT NOCOPY OE_OE_TOTALS_SUMMARY.Rec_Charges_Tbl_Type)
1220 IS
1221 CURSOR C_rec_charge(g_header_id IN NUMBER) IS
1222 select distinct nvl(charge_periodicity_code,'ONE')
1223 from oe_order_lines_all
1224 where header_id = g_header_id
1225 -- and charge_periodicity_code is not null
1226 order by nvl(charge_periodicity_code,'ONE');
1227
1228 l_code VARCHAR2(3);
1229 i NUMBER;
1230 x_tax NUMBER;
1231 x_subtotal NUMBER;
1232 x_charges NUMBER;
1233 l_debug_level CONSTANT NUMBER := Oe_Debug_Pub.g_debug_level;
1234
1235 BEGIN
1236
1237 IF l_debug_level > 0 THEN
1238 OE_DEBUG_PUB.ADD('ENTERING OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS',1);
1239 OE_DEBUG_PUB.ADD('INPUTS TO THE API',1);
1240 OE_DEBUG_PUB.ADD('p_header_id :'||p_header_id,1);
1241 END IF;
1242
1243 i := 1;
1244 OPEN C_rec_charge(p_header_id);
1245 LOOP
1246 FETCH C_rec_charge INTO l_code;
1247 EXIT WHEN C_rec_charge%NOTFOUND;
1248
1249 IF l_debug_level > 0 THEN
1250 OE_DEBUG_PUB.ADD('INSIDE LOOP FOR PERIODICITY_CODE :'||l_code,1);
1251 END IF;
1252
1253 x_rec_charges_tbl(i).charge_periodicity_code := l_code;
1254 IF (l_code <> 'ONE') THEN
1255 x_rec_charges_tbl(i).charge_periodicity_meaning := OE_ID_TO_VALUE.Charge_Periodicity(l_code);
1256 END IF;
1257
1258 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1259 (
1260 p_header_id => p_header_id,
1261 p_charge_periodicity_code => l_code
1262 );
1263 x_tax := nvl(g_rec_tax_value,0);
1264 x_subtotal := nvl(g_rec_total_extended_price,0);
1265
1266 x_rec_charges_tbl(i).rec_tax := x_tax;
1267 x_rec_charges_tbl(i).rec_subtotal := x_subtotal;
1268
1269 x_charges := OE_OE_TOTALS_SUMMARY.ReC_charges(p_header_id => p_header_id,
1270 p_charge_periodicity_code => l_code);
1271 x_rec_charges_tbl(i).rec_charges := x_charges;
1272 x_rec_charges_tbl(i).rec_total := nvl(x_subtotal,0) + nvl(x_tax,0) + nvl(x_charges,0);
1273
1274 IF l_debug_level > 0 THEN
1275 OE_DEBUG_PUB.ADD('TOTALS FOR PERIODICITY_CODE :'||l_code,1);
1276 OE_DEBUG_PUB.ADD('TAX :'||x_tax,1);
1277 OE_DEBUG_PUB.ADD('SUBTOTAL :'||x_subtotal,1);
1278 OE_DEBUG_PUB.ADD('CHARGES :'||x_charges,1);
1279 END IF;
1280
1281 i := i+1;
1282 END LOOP;
1283 CLOSE C_rec_charge;
1284
1285 IF l_debug_level > 0 THEN
1286 OE_DEBUG_PUB.ADD('EXITING OE_OE_TOTALS_SUMMARY.GET_RECURRING_TOTALS');
1287 END IF;
1288
1289 EXCEPTION
1290
1291 WHEN NO_DATA_FOUND THEN
1292 CLOSE C_rec_charge;
1293 Null;
1294
1295
1296 END Get_UI_Recurring_Totals;
1297
1298 PROCEDURE GET_MODEL_RECURRING_TOTALS
1299 (
1300 p_header_id IN NUMBER,
1301 p_line_id IN NUMBER,
1302 p_line_number IN NUMBER,
1303 x_rec_charges_tbl IN OUT NOCOPY OE_OE_TOTALS_SUMMARY.Rec_Charges_Tbl_Type
1304 )
1305 IS
1306
1307 CURSOR C_rec_charge(g_header_id IN NUMBER,p_line_number IN NUMBER) IS
1308 select distinct nvl(charge_periodicity_code,'ONE')
1309 from oe_order_lines_all
1310 where header_id = g_header_id
1311 -- and charge_periodicity_code is not null
1312 and line_number = p_line_number
1313 order by nvl(charge_periodicity_code,'ONE') desc;
1314
1315 CURSOR C_extended(p_header_id IN NUMBER,p_line_Id IN NUMBER,p_line_Number IN NUMBER,p_code IN VARCHAR2) IS
1316 SELECT NVL(Ordered_Quantity,0)*
1317 NVL(unit_selling_price,0) Line_details_total,tax_value,line_category_code
1318 FROM oe_order_lines_all
1319 WHERE header_id=p_header_id
1320 AND nvl(charge_periodicity_code,'ONE')=p_code -- added abghosh
1321 AND (line_number=p_line_number
1322 AND NVL(cancelled_flag,'N') ='N'
1323 OR (top_model_line_id is not null
1324 AND top_model_line_id=p_line_id
1325 -- AND charge_periodicity_code=p_code -- commented abghosh
1326 AND NVL(cancelled_flag,'N') ='N')
1327 OR (service_reference_line_id is not null
1328 AND service_reference_line_id=p_line_id
1329 AND NVL(cancelled_flag,'N') ='N'));
1330
1331 l_code VARCHAR2(3);
1332 i NUMBER;
1333 x_tax NUMBER;
1334 rec_tax Number :=0;
1335 x_subtotal NUMBER :=0;
1336 rec_subtotal Number :=0;
1337 x_charges NUMBER :=0;
1338 x_line_category_code VARCHAR2(30);
1339 x_tax_total NUMBER :=0;
1340 x_total NUMBER :=0;
1341 rec_charges NUMBER :=0;
1342 l_line_id NUMBER;
1343 l_debug_level CONSTANT NUMBER := Oe_Debug_Pub.g_debug_level;
1344
1345 BEGIN
1346
1347 IF l_debug_level > 0 THEN
1348 OE_DEBUG_PUB.ADD('ENTERING OE_OE_TOTALS_SUMMARY.GET_MODEL_RECURRING_TOTALS',1);
1349 OE_DEBUG_PUB.ADD('INPUTS TO THE API',1);
1350 OE_DEBUG_PUB.ADD('p_heder_id :'||p_header_id,1);
1351 OE_DEBUG_PUB.ADD('p_line_id_ :'||p_line_id,1);
1352 OE_DEBUG_PUB.ADD('p_line_number :'||p_line_number,1);
1353 END IF;
1354
1355 i := 1;
1356 OPEN C_rec_charge(p_header_id,p_line_number);
1357 LOOP
1358 FETCH C_rec_charge INTO l_code;
1359 EXIT WHEN C_rec_charge%NOTFOUND;
1360
1361 IF l_debug_level > 0 THEN
1362 OE_DEBUG_PUB.ADD('INSIDE OUTER LOOP FOR PERIODICITY_CODE :'||l_code,1);
1363 END IF;
1364
1365 x_rec_charges_tbl(i).charge_periodicity_code := l_code;
1366 IF (l_code <> 'ONE') THEN
1367 x_rec_charges_tbl(i).charge_periodicity_meaning := OE_ID_TO_VALUE.Charge_Periodicity(l_code);
1368 END IF;
1369 OPEN C_extended(p_header_id,p_line_id,p_line_number,l_code);
1370
1371 LOOP-- inner loop added abghosh
1372
1373 FETCH c_extended into x_subtotal,x_tax,x_line_category_code;
1374 EXIT WHEN C_extended%NOTFOUND;
1375 IF l_debug_level > 0 THEN
1376 OE_DEBUG_PUB.ADD('INSIDE INNER LOOP FOR PERIODICITY_CODE :'||l_code,1);
1377 END IF;
1378 IF x_line_category_code <> 'RETURN' THEN
1379 x_total:=x_total+x_subtotal;
1380 x_tax_total:=x_tax_total+x_tax;
1381 ELSIF x_line_category_code='RETURN' THEN
1382 x_total:=x_total-x_subtotal;
1383 x_tax_total:=x_tax_total-x_tax;
1384 END IF;
1385
1386 END LOOP;
1387 CLOSE C_Extended;
1388
1389 SELECT SUM(ROUND(
1390 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
1391 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
1392 DECODE(L.ORDERED_QUANTITY,0,0,-P.OPERAND),
1393 (-L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0))),
1394 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
1395 DECODE(L.ORDERED_QUANTITY,0,0,P.OPERAND),
1396 (L.ORDERED_QUANTITY* nvl(P.ADJUSTED_AMOUNT,0)))
1397 )
1398 ,OE_ORDER_UTIL.G_Precision)
1399 )
1400 INTO x_charges
1401 FROM OE_PRICE_ADJUSTMENTS P,
1402 OE_ORDER_LINES_ALL L
1403 WHERE P.HEADER_ID = p_header_id
1404 AND P.LINE_ID = L.LINE_ID
1405 AND nvl(L.CHARGE_PERIODICITY_CODE,'ONE') = l_code
1406 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
1407 AND P.APPLIED_FLAG = 'Y'
1408 AND (l.line_number=p_line_number
1409 AND NVL(l.cancelled_flag,'N') ='N'
1410 OR (l.top_model_line_id is not null
1411 AND l.top_model_line_id=p_line_id
1412 AND NVL(l.cancelled_flag,'N') ='N')
1413 OR (l.service_reference_line_id is not null
1414 AND l.service_reference_line_id=p_line_id
1415 AND NVL(l.cancelled_flag,'N') ='N'));
1416
1417 rec_tax:=rec_tax+nvl(x_tax_total,0); -- 3 lines added abghosh summing up
1418 rec_charges:=rec_charges+nvl(x_charges,0);
1419 rec_subtotal:=rec_subtotal+x_total;
1420
1421
1422 x_rec_charges_tbl(i).rec_tax := rec_tax; -- adding in table added abghosh
1423 x_rec_charges_tbl(i).rec_subtotal := rec_subtotal;
1424 x_rec_charges_tbl(i).rec_charges := rec_charges;
1425 x_rec_charges_tbl(i).rec_total := rec_subtotal + rec_tax + rec_charges;
1426
1427 IF l_debug_level > 0 THEN
1428 OE_DEBUG_PUB.ADD('TOTALS FOR PERIODICITY_CODE :'||l_code,1);
1429 OE_DEBUG_PUB.ADD('TAX :'||rec_tax,1);
1430 OE_DEBUG_PUB.ADD('SUBTOTAL :'||rec_subtotal,1);
1431 OE_DEBUG_PUB.ADD('CHARGES :'||rec_charges,1);
1432 END IF;
1433
1434 i:=i+1; -- increment counter added abghosh
1435
1436
1437 rec_subtotal:=0; -- reset after one group of periodicity added abghosh
1438 rec_charges:=0;
1439 rec_tax:=0;
1440 x_total:=0;
1441 x_tax_total:=0;
1442 x_charges:=0;
1443
1444 END LOOP;
1445 CLOSE C_rec_charge;
1446
1447 IF l_debug_level > 0 THEN
1448 OE_DEBUG_PUB.ADD('EXITING OE_OE_TOTALS_SUMMARY.GET_MODEL_RECURRING_TOTALS',1);
1449 END IF;
1450
1451 END Get_Model_Recurring_Totals;
1452
1453 /* Recurring Charges */
1454
1455 --rc pviprana start
1456 PROCEDURE SET_ADJ_RECURRING_AMOUNTS
1457 (p_header_id IN NUMBER DEFAULT NULL,
1458 p_price_adjustment_id IN NUMBER DEFAULT NULL)
1459 IS
1460
1461 CURSOR c_charge_periodicity(p_header_id IN NUMBER) IS
1462 SELECT DISTINCT charge_periodicity_code
1463 FROM oe_order_lines_all
1464 WHERE header_id = p_header_id
1465 AND charge_periodicity_code is not null
1466 ORDER BY charge_periodicity_code;
1467
1468 l_code VARCHAR2(3);
1469 i PLS_INTEGER;
1470 l_header_id NUMBER;
1471 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1472
1473 BEGIN
1474
1475 IF l_debug_level > 0 THEN
1476 oe_debug_pub.add('ENTERING OE_OE_TOTALS_SUMMARY.SET_ADJ_RECURRING_AMOUNTS');
1477 END IF;
1478 IF p_price_adjustment_id IS NULL THEN
1479 RETURN;
1480 ELSE
1481 IF p_header_id IS NULL THEN
1482 BEGIN
1483 SELECT header_id INTO l_header_id
1484 FROM oe_price_adjustments
1485 WHERE price_adjustment_id = p_price_adjustment_id;
1486
1487 IF l_header_id IS NULL THEN
1488 RETURN;
1489 END IF;
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 IF l_debug_level > 0 THEN
1493 oe_debug_pub.add('Exception while querying for the adjustment record : ' || SQLERRM);
1494 END IF;
1495 RETURN;
1496 END;
1497 ELSE
1498 l_header_id := p_header_id;
1499 END IF;
1500 END IF;
1501
1502 IF l_debug_level > 0 THEN
1503 oe_debug_pub.add('l_header_id : ' || l_header_id);
1504 oe_debug_pub.add('p_price_adjustment_id : ' || p_price_adjustment_id);
1505 END IF;
1506
1507 G_RECURRING_AMOUNTS_TBL.DELETE;
1508 i := 1;
1509 OPEN c_charge_periodicity(l_header_id);
1510 LOOP
1511 FETCH c_charge_periodicity INTO l_code;
1512 EXIT WHEN c_charge_periodicity%NOTFOUND;
1513 G_RECURRING_AMOUNTS_TBL(i).charge_periodicity_code := l_code;
1514 G_RECURRING_AMOUNTS_TBL(i).charge_periodicity_meaning := OE_ID_TO_VALUE.Charge_Periodicity(l_code);
1515 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);
1516
1517 IF l_debug_level > 0 THEN
1518 oe_debug_pub.add('***************************************************************');
1519 oe_debug_pub.add('charge_periodicity_code : ' || G_RECURRING_AMOUNTS_TBL(i).charge_periodicity_code);
1520 oe_debug_pub.add('charge_periodicity_meaning : ' || G_RECURRING_AMOUNTS_TBL(i).charge_periodicity_meaning);
1521 oe_debug_pub.add('recurring amount : ' || G_RECURRING_AMOUNTS_TBL(i).recurring_amount);
1522 oe_debug_pub.add('***************************************************************');
1523 END IF;
1524 i := i+1;
1525 END LOOP;
1526 CLOSE c_charge_periodicity;
1527
1528 IF l_debug_level > 0 THEN
1529 oe_debug_pub.add('EXITING OE_OE_TOTALS_SUMMARY.SET_ADJ_RECURRING_AMOUNTS');
1530 END IF;
1531
1532 END SET_ADJ_RECURRING_AMOUNTS;
1533
1534 PROCEDURE GET_ADJ_RECURRING_AMOUNTS
1535 (x_recurring_amounts_tbl IN OUT NOCOPY /* file.sql.39 change */ Recurring_Amounts_Tbl_Type)
1536 IS
1537 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1538 BEGIN
1539 IF l_debug_level > 0 THEN
1540 oe_debug_pub.add('ENTERING OE_OE_TOTALS_SUMMARY.GET_ADJ_RECURRING_AMOUNTS');
1541 END IF;
1542
1543 x_recurring_amounts_tbl := G_RECURRING_AMOUNTS_TBL;
1544
1545 IF l_debug_level > 0 THEN
1546 oe_debug_pub.add('EXITING OE_OE_TOTALS_SUMMARY.GET_ADJ_RECURRING_AMOUNTS');
1547 END IF;
1548 END;
1549
1550 --rc pviprana end
1551
1552 --rc preview/print
1553 FUNCTION PRN_REC_SUBTOTALS
1554 (
1555 p_header_id IN NUMBER,
1556 p_charge_periodicity_code IN VARCHAR2
1557 )
1558 RETURN NUMBER
1559 IS
1560 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1561 BEGIN
1562
1563 IF l_debug_level > 0 THEN
1564 oe_debug_pub.add('ENTERING REC_SUBTOTALS FUNCTION');
1565 oe_debug_pub.add('Header_id = '||p_header_id );
1566 oe_debug_pub.add('Charge periodicity code = '||p_charge_periodicity_code );
1567 END IF;
1568
1569 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1570 (
1571 p_header_id => p_header_id,
1572 p_charge_periodicity_code => p_charge_periodicity_code
1573 );
1574
1575 RETURN(G_REC_TOTAL_EXTENDED_PRICE);
1576
1577 EXCEPTION
1578 WHEN too_many_rows THEN
1579 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
1580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1581 WHEN no_data_found THEN
1582 IF l_debug_level > 0 THEN
1583 oe_debug_pub.add('No Data found'||p_header_id,1) ;
1584 END IF;
1585 WHEN others THEN
1586 IF l_debug_level > 0 THEN
1587 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
1588 END IF;
1589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1590
1591 END PRN_REC_SUBTOTALS;
1592
1593 FUNCTION PRN_REC_TAXES
1594 (
1595 p_header_id IN NUMBER,
1596 p_charge_periodicity_code IN VARCHAR2
1597 )
1598 RETURN NUMBER
1599 IS
1600 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1601 BEGIN
1602
1603 IF l_debug_level > 0 THEN
1604 oe_debug_pub.add('ENTERING REC_SUBTOTALS FUNCTION');
1605 oe_debug_pub.add('Header_id = '||p_header_id );
1606 oe_debug_pub.add('Charge periodicity code = '||p_charge_periodicity_code );
1607 END IF;
1608
1609 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1610 (
1611 p_header_id => p_header_id,
1612 p_charge_periodicity_code => p_charge_periodicity_code
1613 );
1614
1615 RETURN(g_rec_tax_value);
1616
1617 EXCEPTION
1618 WHEN too_many_rows THEN
1619 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
1620 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1621 WHEN no_data_found THEN
1622 IF l_debug_level > 0 THEN
1623 oe_debug_pub.add('No Data found'||p_header_id,1) ;
1624 END IF;
1625 WHEN others THEN
1626 IF l_debug_level > 0 THEN
1627 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
1628 END IF;
1629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1630
1631 END PRN_REC_TAXES;
1632
1633 FUNCTION PRN_REC_TOTALS
1634 (
1635 p_header_id IN NUMBER,
1636 p_charge_periodicity_code IN VARCHAR2
1637 )
1638 RETURN NUMBER
1639 IS
1640 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1641 l_taxes NUMBER;
1642 l_subtotals NUMBER;
1643 l_charges NUMBER;
1644
1645 BEGIN
1646
1647 IF l_debug_level > 0 THEN
1648 oe_debug_pub.add('ENTERING REC_TOTALS FUNCTION');
1649 oe_debug_pub.add('Header_id = '||p_header_id );
1650 oe_debug_pub.add('Charge periodicity code = '||p_charge_periodicity_code );
1651 END IF;
1652
1653 OE_OE_TOTALS_SUMMARY.GLOBAL_Rec_TOTALS
1654 (
1655 p_header_id => p_header_id,
1656 p_charge_periodicity_code => p_charge_periodicity_code
1657 );
1658
1659 l_taxes:= g_rec_tax_value;
1660 l_subtotals := G_REC_TOTAL_EXTENDED_PRICE;
1661 l_charges:= OE_OE_TOTALS_SUMMARY.ReC_charges(p_header_id => p_header_id,
1662 p_charge_periodicity_code => p_charge_periodicity_code );
1663
1664 RETURN(l_taxes + l_subtotals + l_charges);
1665
1666 EXCEPTION
1667 WHEN too_many_rows THEN
1668 oe_debug_pub.add('unexpected error : '||sqlerrm,1);
1669 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1670 WHEN no_data_found THEN
1671 IF l_debug_level > 0 THEN
1672 oe_debug_pub.add('No Data found'||p_header_id,1) ;
1673 END IF;
1674 WHEN others THEN
1675 IF l_debug_level > 0 THEN
1676 oe_debug_pub.add('Others unexpected error : '||sqlerrm,1);
1677 END IF;
1678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1679
1680 END PRN_REC_TOTALS;
1681
1682 END OE_OE_TOTALS_SUMMARY;
1683
[Home] [Help]