Pending Orders Report in Query Builder

[Aditya Duggal]

Hi,

I am trying to make a new report, "Pending Orders". This is a report which is has been copied from an existing report "Delivered Items to be Billed".
Now this is based on a query report builder which I seem to guess is very efficient. Now I have run into some trouble and given my limited knowledge of coding I am unable to find the error. The code used is as below:

select
 `tabSales Order`.`name` as "S.O. No.:Link/Sales Order:120",
 `tabSales Order`.`transaction_date` as "S.O. Date",
 `tabSales Order`.`delivery_date` as "Expected Delivery Date"
 `tabSales Order`.`customer` as "Customer:Link/Customer:120",
 `tabSales Order Item`.item_code as "Item Code:Link/Item:120",
 `tabSales Order Item`.description as "Description",
 `tabSales Order Item`.qty as "Qty:Float",
 `tabSales Order Item`.delivered_qty as "Delivered Qty:Float",
 `tabSales Order`.`po_no` as "P.O. No.",

from
 `tabSales Order`, `tabSales Order Item`
where
 `tabSales Order Item`.`parent` = `tabSales Order`.`name`
 and `tabSales Order`.docstatus = 1
 and ifnull(`tabSales Order Item`.delivered_qty,0) < ifnull(`tabSales Order Item`.qty,0)
order by `tabSales Order`.transaction_date asc


Can anyone help me out with this report and where did I make a mistake.

[Rushabh Mehta]

Aditya,

Just an issue of a couple of misplaced commas.

I have added this report to the standard product as "Sales Orders Pending to be Delivered". 

Thanks for contributing :)

best,

Rushabh

W: https://erpnext.com
T: @rushabh_mehta

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To post to this group, send email to erpnext-dev...@googlegroups.com.
To unsubscribe from this group, send email to erpnext-developer...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/zYR3iRRmOJIJ.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Aditya Duggal]

Thanks Rushabh,

Just another quick question, how to separate Stopped Sales Order from the Submitted sales order since in both the cases the Status = 1.

Adding this line of on the code side does not seem to help:

 and `tabSales Order`.docstatus != "Stopped"

To unsubscribe from this group, send email to erpnext-developer-forum+unsub...@googlegroups.com.

[Rushabh Mehta]

A quick check of the DocType tells me the fieldname is "status"

W: https://erpnext.com
T: @rushabh_mehta

To unsubscribe from this group, send email to erpnext-developer...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/CC_W8mxnamsJ.

[Aditya Duggal]

Thanks for pointing out the mistake.

This has made me learn that docstatus is in numbers and there is no distinction in numbers between submitted and stopped.

Once again thanks for the quick response.

[Aditya Duggal]

One more small query how do I limit the report to show only those Sales Orders where date of the sales order is less than or equal to today.

in coding terms I want to add this line:

 and `tabSales Order`.transaction_date =< today()

But the above code is obviously not in correct syntax. Could you help me out with this as well.

[Rushabh Mehta]

Aditya,

the MySQL function for this is curdate()

and `tabSales Order`.transaction_date =< curdate()

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

best,

Rushabh

W: https://erpnext.com
T: @rushabh_mehta

[Aditya Duggal]

Hi Rushabh,

I am planning to make a new variant of the pending orders report, this report has some column from the BIN table which shows the actual quantity in stock of that item.

Can you please check the code and let me know if this code is not going to be a big load on the server, and if this code is a big load on the server then please let me know that how can I have this report on the new reports, the other thing I guess is going to be through those report which don't give data into the browser since this report was possible in the SEARCH CRITERIA

The code is below:

select
 `tabSales Order`.name as "SO No:Link/Sales Order:120",
 `tabSales Order`.transaction_date as "SO Date",
 `tabSales Order`.delivery_date as "ED Date",
 `tabSales Order`.customer as "Customer:Link/Customer:120",

 `tabSales Order Item`.item_code as "Item Code:Link/Item:120",
 `tabSales Order Item`.description as "Description",

 `tabSales Order Item`.qty as "Qty:Decimal",
 `tabSales Order Item`.delivered_qty as "Delivered Qty:Decimal",
 `tabSales Order Item`.qty - `tabSales Order Item`.delivered_qty as "Pending Qty",
 `tabBin`.reserved_qty as "Reserved Qty",
 `tabBin`.actual_qty as "Actual Qty",
 `tabSales Order`.po_no as "PO No",
 `tabSales Order`.status as "Status"

from
 `tabSales Order`, `tabSales Order Item`, `tabBin`

where
 `tabSales Order Item`.`parent` = `tabSales Order`.`name`

 and `tabSales Order`.status = "Submitted"

 and `tabSales Order`.transaction_date <= curdate()

 and ifnull(`tabSales Order Item`.delivered_qty,0) < ifnull(`tabSales Order Item`.qty,0)

 and `tabSales Order Item`.reserved_warehouse=`tabBin`.warehouse
order by `tabSales Order`.transaction_date desc

[Aditya Duggal]

Hi,

I have finally been able to join the Sales Order Table with the Bin table, basically the new report would also show the STOCK STATUS of the item in the RESERVED WAREHOUSE, i think this report or its variant would come in handy for the people to pick stock. I though I must share so that this can be incorporated in the standard product.

select
 `tabSales Order`.name as "SO No:Link/Sales Order:100",
 `tabSales Order`.transaction_date as "SO Date::80",
 `tabSales Order Item`.item_code as "Item Code:Link/Item:160",
 `tabSales Order Item`.description as "Description::300",
 (`tabSales Order Item`.qty - ifnull(`tabSales Order Item`.delivered_qty, 0)) as "PEND QTY:Decimal:70",
 `tabSales Order Item`.reserved_warehouse as "WH::80",
 `tabBin`.actual_qty as "ACT:Decimal:40",
 `tabBin`.reserved_qty as "RSVD:Decimal:40",
 `tabSales Order`.customer as "Customer:Link/Customer:150"

from
 `tabSales Order`, `tabSales Order Item`

 left join `tabBin` on (
   `tabBin`.item_code=`tabSales Order Item`.item_code
 )

where
 `tabSales Order Item`.`parent` = `tabSales Order`.`name`

 and `tabBin`.warehouse=`tabSales Order Item`.reserved_warehouse

 and `tabSales Order`.status = "Submitted"
 and `tabSales Order`.transaction_date <= curdate()

 and `tabSales Order Item`.item_code <> "CSP000000-000"
 and `tabSales Order Item`.item_code <> "HSP000000-000"
 and `tabSales Order Item`.item_code <> "JHKG-01"
 and `tabSales Order Item`.item_code <> "JHNO-0075-01"
 and `tabSales Order Item`.item_code <> "JCNO-01"
 and `tabBin`.actual_qty >0

 and ifnull(`tabSales Order Item`.delivered_qty,0) < ifnull(`tabSales Order Item`.qty,0)

order by `tabSales Order`.transaction_date asc

[Nabin Hait]

Hi Aditya,

I have made a github gist after some changes (mainly for readability) in your query. The link is below:

https://gist.github.com/4509292

Note: You are becoming a hardcore developer day by day, join erpnext :)

Regards,

Nabin Hait

--
You received this message because you are subscribed to the Google Groups "ERPNext Developer Forum" group.
To post to this group, send email to erpnext-dev...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msg/erpnext-developer-forum/-/5Px4JAP7eUAJ.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Regards,
Nabin Hait