Purpose:
Sometimes we need to show line-wise or complete order totals (Basic / Tax / Tax+Basic) in reports.
Oracle provides a standard package to calculate these values based on the parameters passed.
Package Name
Illustration with Example
Suppose 1 order has 3 lines, and each line has taxes attached:
-
Header ID:
23096
| Line ID | Line Value (Basic) | Tax Value | Total Value (Basic + Tax) |
|---|---|---|---|
| 36492 | 1,045.00 | 87.52 | 1,132.52 |
| 36494 | 505.00 | 42.30 | 547.30 |
| 36495 | 1,750.00 | 146.56 | 1,896.56 |
✅ With oe_totals_grp.get_order_total, you can:
-
Get line-level totals (basic, tax, total).
-
Get order-level totals (sum across all lines).
Calculate Line wise Tax
select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'TAXES'),0) from dual;
For Ex:
select nvl(oe_totals_grp.get_order_total (23096, 36492, 'TAXES'),0) LINE_Tax from dual;
Output : LINE_Tax=87.52
Calculate Order Taxes (All Lines)
select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'TAXES'),0) from dual;
Ex:
select nvl(oe_totals_grp.get_order_total (23096, null, 'TAXES'),0) Order_Tax from dual;
Output : Order_Tax: 276.38
Calculate Line wise Value (Without Tax)
select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'LINES'),0) from dual;
Ex:
select nvl(oe_totals_grp.get_order_total (23096, 36492, 'LINES'),0) LINE_BASIC from dual;
Output : LINE_BASIC=1045
Calculate All Lines Total (Without Tax)
select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'LINES'),0) from dual;
Ex:
select nvl(oe_totals_grp.get_order_total (23096, null, 'LINES'),0) Order_Basic from dual;
Output : Order_Basic: 3300
Calculate Line wise Value ( With Tax)
select nvl(oe_totals_grp.get_order_total (HEADER_ID, LINE_ID, 'ALL'),0) from dual;
Ex:
select nvl(oe_totals_grp.get_order_total (23096, 36492, 'ALL'),0) LINE_TOTAL from dual;
Output : LINE_TOTAL=1132.52
Calculate Order Total Value (With Tax)
select nvl(oe_totals_grp.get_order_total (HEADER_ID, NULL, 'ALL'),0) from dual;
Ex:
select nvl(oe_totals_grp.get_order_total (23096, null, 'ALL'),0) Order_Total from dual;
