I need some help with a SQL statement for a Quick Report I try to create. I want to show the total amount of a Purchase Order including charges (when a charge or multiple charges are applied on an order) and excluding tax. I created the statement below, but did not get the desired result.
The statement is working fine, except the bold part. The statement returns only a value in ‘Total Amount’ when there are charges applied on the Purchase Order. And also, when there are multiple charges applied on the PO, the statement returns multiple lines. See the example below.
|Purchase Order||Wanted Receipt Date||Receipt Week||Status||Total Amount|
My desired result is: When there are no charges applied on the Purchase Order, the report should show the total amount of the Purchase Order. And when there are charge(s) applied, the report should show the total amount of the PO plus the amount of the charge(s) on one row.
Is there someone that can help me out and get the desired result?
SELECT to_char(purchase_order_cfv.order_date,'IW') "Week",
to_char(purchase_order_cfv.order_date, 'dd-mm-yyyy') "Order Date",
purchase_order_cfv.project_id "Project ID",
PROJECT_API.Get_Name(purchase_order_cfv.PROJECT_ID) "Project Name",
purchase_order_cfv.order_no "Purchase Order",
to_char(purchase_order_cfv.wanted_receipt_date, 'dd-mm-yyyy') "Wanted Receipt Date",
to_char(purchase_order_cfv.wanted_receipt_date,'IW') "Receipt Week",
purchase_order_cfv.state "Status", Purchase_Order_API.Calc_Order_Total(purchase_order_cfv.Order_No)+Purchase_Order_Charge.charge_amount "Total Amount",
Purchase_Order_API.Calc_Total_Gross_Incl_Chg(purchase_order_cfv.order_no) "Total Amount incl. Tax and Charges"
LEFT OUTER JOIN purchase_order_charge
ON purchase_order_cfv.order_no = purchase_order_charge.order_no
WHERE purchase_order_cfv.OBJSTATE <> (select PURCHASE_ORDER_API.FINITE_STATE_ENCODE__('Cancelled') from dual) and purchase_order_cfv.OBJSTATE <> (select PURCHASE_ORDER_API.FINITE_STATE_ENCODE__('Planned') from dual)
AND purchase_order_cfv.order_date BETWEEN TRUNC(Context_Substitution_Var_API.Get_Client_Value__('START_OF_THIS_YEAR'))
AND TRUNC(Context_Substitution_Var_API.Get_Client_Value__('END_OF_THIS_YEAR')) + (1 - 1 / (60 * 60 * 24))
ORDER BY purchase_order_cfv.order_date
Best answer by VivekBhandiwadView original