Pending Orders Report in Query Builder

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.



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@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,

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

On 07-Dec-2012, at 9:10 AM, Aditya Duggal <ad...@gmail.com> wrote:

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.



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@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.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

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

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

 

 

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"

On Friday, December 7, 2012 10:16:39 AM UTC+5:30, rushabh wrote:

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 :slight_smile:

best,
Rushabh


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

On 07-Dec-2012, at 9:10 AM, Aditya Duggal <ad...@gmail.com> wrote:

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.



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-forum+un…@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.

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

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

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

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

 

 

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


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

On 07-Dec-2012, at 10:48 AM, Aditya Duggal <ad...@gmail.com> wrote:

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"

On Friday, December 7, 2012 10:16:39 AM UTC+5:30, rushabh wrote:
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

On 07-Dec-2012, at 9:10 AM, Aditya Duggal <ad...@gmail.com> wrote:

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.



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-forum+un…@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.

 

 





You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

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

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

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

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

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

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

 

 

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.


On Friday, December 7, 2012 11:02:17 AM UTC+5:30, rushabh wrote:

A quick check of the DocType tells me the fieldname is “status”


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

On 07-Dec-2012, at 10:48 AM, Aditya Duggal <ad...@gmail.com> wrote:

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"

On Friday, December 7, 2012 10:16:39 AM UTC+5:30, rushabh wrote:
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

On 07-Dec-2012, at 9:10 AM, Aditya Duggal <ad...@gmail.com> wrote:

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.



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@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.

 

 





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-forum+un…@googlegroups.com.

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

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

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

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

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

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

 

 

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.

On Friday, December 7, 2012 11:20:54 AM UTC+5:30, Aditya Duggal wrote:

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.


On Friday, December 7, 2012 11:02:17 AM UTC+5:30, rushabh wrote:
A quick check of the DocType tells me the fieldname is “status”


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

On 07-Dec-2012, at 10:48 AM, Aditya Duggal <ad...@gmail.com> wrote:

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"

On Friday, December 7, 2012 10:16:39 AM UTC+5:30, rushabh wrote:
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

On 07-Dec-2012, at 9:10 AM, Aditya Duggal <ad...@gmail.com> wrote:

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.



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@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.

 

 





You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

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

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

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

 

 




You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

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

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

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

 

 

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

On 07-Dec-2012, at 5:24 PM, Aditya Duggal <ad...@gmail.com> wrote:

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



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

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

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

 

 

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


On Friday, December 7, 2012 10:08:00 PM UTC+5:30, rushabh wrote:

Aditya,

the MySQL function for this is curdate()

and tabSales Order.transaction_date =< curdate()


best,
Rushabh


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

On 07-Dec-2012, at 5:24 PM, Aditya Duggal <ad...@gmail.com> wrote:

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



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

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

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

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

 

 

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

On Monday, December 17, 2012 6:27:20 PM UTC+5:30, Aditya Duggal wrote:

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=<wbr>tabBin.warehouse
order by tabSales Order.transaction_date desc


On Friday, December 7, 2012 10:08:00 PM UTC+5:30, rushabh wrote:
Aditya,

the MySQL function for this is curdate()

and tabSales Order.transaction_date =< curdate()


best,
Rushabh


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

On 07-Dec-2012, at 5:24 PM, Aditya Duggal <ad...@gmail.com> wrote:

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



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un...@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.

 

 

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


On Fri, Jan 11, 2013 at 10:34 AM, Aditya Duggal <ad...@gmail.com> wrote:
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


On Monday, December 17, 2012 6:27:20 PM UTC+5:30, Aditya Duggal wrote:
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


On Friday, December 7, 2012 10:08:00 PM UTC+5:30, rushabh wrote:
Aditya,

the MySQL function for this is curdate()

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


best,
Rushabh


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

On 07-Dec-2012, at 5:24 PM, Aditya Duggal <ad...@gmail.com> wrote:

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



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er…@googlegroups.com.

To unsubscribe from this group, send email to erpnext-developer-forum+un…@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



You received this message because you are subscribed to the Google Groups “ERPNext Developer Forum” group.

To post to this group, send email to er...@googlegroups.com.

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

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