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.
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.
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.
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.
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.
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.
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:
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:
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()
