Help needed with a SQL statement for a Quick Report

Dear Simon

You do not need the outer join to the Purchase Order Charges View, as you are already using an API (Purchase_Order_API.Calc_Total_Gross_Incl_Chg(purchase_order_cfv.order_no))  to get the total for the charges. This is the reason you see multiple lines per order, if there are more than one charge.

You would need to use a join, only when you want to display additional fields or columns from the view. In this case, you are looking for the sum total of charges, and using an API for this, thus no need for the join.

Remove the below lines from the code

LEFT OUTER JOIN purchase_order_charge
ON purchase_order_cfv.order_no = purchase_order_charge.order_no

Eliminate this and then check if the PO total and the PO charges come correctly.

Regards

Vivek

Hi @VivekBhandiwad 

Thank you for your reply. The result from the API Purchase_Order_API.Calc_Total_Gross_Incl_Chg(purchase_order_cfv.order_no) is the total gross amount of the Purchase Order including charges. But I am looking for the total net amount including charges (the amount of the charges also excluding tax). But I can’t find the correct API to do so. Do you know how I can get the result I want? 

Hi Simon

Replace the API (Purchase_Order_API.Calc_Total_Gross_Incl_Chg(purchase_order_cfv.order_no)) with a sub query as follows

( select sum(d.charges_amount) from purchase_order_charge d where d.order_no = purchase_order_cfv.order_no and d.contract = purchase_order_cfv.contract ) ) sum_charges

Note:  The field name for charges is assumed (d.charges_amount) and not as per actual field value. Please go to the screen/View and replace the correct name.

Hope this helps

-Vivek

Hi @VivekBhandiwad 

I did as you suggested but IFS gives an error when I try to save the query. Do you know what is wrong? This is the statement I try to save:
 

SELECT  to_char(order_date,'IW') "Week",
              to_char(order_date, 'dd-mm-yyyy') "Order Date",
              Supplier_API.Get_Vendor_Name(vendor_no) "Supplier",
              Cf$_Pur_Or_Description "Description",
              project_id "Project ID", 
              PROJECT_API.Get_Name(PROJECT_ID) "Project Name",
              Cf$_Coordinator_Name "Coordinator",
              order_no "Purchase Order",
              to_char(wanted_receipt_date, 'dd-mm-yyyy') "Wanted Receipt Date",
              to_char(wanted_receipt_date,'IW') "Receipt Week",
              state "Status", 
              Purchase_Order_API.Calc_Order_Total(Order_No) "Total Amount",
              ( select sum(d.charge_amount) from purchase_order_charge d where d.order_no = purchase_order_cfv.order_no and d.contract = purchase_order_cfv.contract ) ) sum_charges
FROM purchase_order_cfv

WHERE OBJSTATE <> (select PURCHASE_ORDER_API.FINITE_STATE_ENCODE__('Cancelled') from dual) and OBJSTATE <> (select PURCHASE_ORDER_API.FINITE_STATE_ENCODE__('Planned') from dual)
AND     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 order_date

Hi Simon

Assume you were using plsql to build the query till now. While deploying you have to add "Ifsapp." before any view or package that you are calling.

Check the output in PLSQL and if that resolves your query related issues. 

For deploying you have to follow the IFS rules. 

Hope this helps

Regards

Vivek

Hi @VivekBhandiwad 

I am not familiar with a PLSQL tool. I always built my queries in the SQL Query Tool or in Create New Quick Report. But I have done as you suggested, but still IFS returns an error when I try to save the query. The error is ‘From keyword not found where expected’. Do you know what is wrong?

SELECT  to_char(order_date,'IW') "Week",
              to_char(order_date, 'dd-mm-yyyy') "Order Date",
              ifsapp.Supplier_API.Get_Vendor_Name(vendor_no) "Supplier",
              Cf$_Pur_Or_Description "Description",
              project_id "Project ID", 
              ifsapp.PROJECT_API.Get_Name(PROJECT_ID) "Project Name",
              Cf$_Coordinator_Name "Coordinator",
              order_no "Purchase Order",
              to_char(wanted_receipt_date, 'dd-mm-yyyy') "Wanted Receipt Date",
              to_char(wanted_receipt_date,'IW') "Receipt Week",
              state "Status", 
              ifsapp.Purchase_Order_API.Calc_Order_Total(Order_No) "Total Amount",
              ( select sum(d.charge_amount) from ifsapp.purchase_order_charge d where d.order_no = purchase_order_cfv.order_no and d.contract = purchase_order_cfv.contract ) ) sum_charges
FROM ifsapp.purchase_order_cfv
WHERE OBJSTATE <> (select ifsapp.PURCHASE_ORDER_API.FINITE_STATE_ENCODE__('Cancelled') from dual) and OBJSTATE <> (select ifsapp.PURCHASE_ORDER_API.FINITE_STATE_ENCODE__('Planned') from dual)
AND     order_date BETWEEN TRUNC(ifsapp.Context_Substitution_Var_API.Get_Client_Value__('START_OF_THIS_YEAR'))
AND     TRUNC(ifsapp.Context_Substitution_Var_API.Get_Client_Value__('END_OF_THIS_YEAR')) + (1 - 1 / (60 * 60 * 24))
ORDER BY order_date

Hi @VivekBhandiwad , 

I should have seen the extra ")”, my fault ;) 

Many thanks for your help. The statement returns exactly what I wanted! 

Need some more info if we can include ‘Landed’ costs of a PO into the above Query?

And how?

The info shared will be much helpful

@Venkat04@Venkat04..... The charges on PO along with the price, per line, would essentially provide you the Landed costs for the PO.. Note IFS does support in loading the Charges equally amongst the lines or as per the price or even for each line. Find the right field at the PO line level which provides the cost per line and you have this info.

For the actual field value, check with Mohd. Rafi,

Hope this helps.