Solved

Help needed with a SQL statement for a Quick Report


Userlevel 4
Badge +8

Hi, 

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
P101306 12-01-2021 02 Closed 726,6
P101306 12-01-2021 02 Closed 701,6

 

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",
              Supplier_API.Get_Vendor_Name(purchase_order_cfv.vendor_no) "Supplier",
              purchase_order_cfv.Cf$_Pur_Or_Description "Description",
              purchase_order_cfv.project_id "Project ID", 
              PROJECT_API.Get_Name(purchase_order_cfv.PROJECT_ID) "Project Name",
              purchase_order_cfv.Cf$_Coordinator_Name "Coordinator",
              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"


FROM purchase_order_cfv 
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

 

icon

Best answer by VivekBhandiwad 29 March 2021, 12:07

View original

10 replies

Userlevel 3
Badge +5

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

Userlevel 4
Badge +8

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? 

Userlevel 3
Badge +5

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

Userlevel 4
Badge +8

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

Userlevel 3
Badge +5

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

Userlevel 4
Badge +8

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

Userlevel 3
Badge +5

Hi Simon

There is an extra “)”  before the field alias “sum_charges”

the query should work, if you remove the extra “)” , as below

--Original --

    ( 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

 

--revised/corrected

    ( 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

 

Regards

Vivek

 

Userlevel 4
Badge +8

Hi @VivekBhandiwad , 

 

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

 

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

Userlevel 4
Badge +5

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

Userlevel 3
Badge +5

@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. 

Reply