Regarding Stock Balance formula

Hello all,

I am working on stock balance report. I need a formula to calculate opening qty, In qty ,out qty , closing qty. I have checked "stock_balance.js" file but i can't understand the flow how to calculate these values.

I tried this query but it gives wrong values.please check it http://pastebin.com/1qBSD6fx
Query Details
1 For opening qty, we have checked sum of actual_qty whose voucher_nos in respective table with docstatus=1 
2 For  In Qty, we have checked sum of actual_qty whose voucher_type is Purchase Receipt and Stock Entry with (purpose ='Material Receipt') and voucher_nos in respective table with docstatus=1 
3 For  Out Qty, we have checked sum of actual_qty whose voucher_type is Delivery Note and Stock Entry with (purpose= 'Material Transfer','Material Issue') and Sales Invoice and voucher_nos in respective table with docstatus=1 

Thanks
amit



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



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

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

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

I have tried again by referring "stock_balance.js" , but i am not able to calculate the In_qty, Out_qty. I have import database dump locally and i want to generate charts locally for that i need this values. I have "tabStock Ledger Entry" table with actual_qty column with +ve and -ve values. 
By considering stock_balance.js code 
for opening we are adding all values and
for in_qty , out_qty we are ignoring stock entry with purpose 'Material Transfer' (as ingnore_inflow_outflow) and adding all remaining values which are less than 0 for out and greater than 0 for in.

But still records are getting wrong. In "stock_balance.js"  we use qty column but to create qty column which filter are applied so that query will give correct results?

Can anyone please help me to generate sql query or explain which condition i should apply to get correct result....

Thanks in advance :)
Amit
 
On Wednesday, November 20, 2013 5:13:13 PM UTC+5:30, am...@indictranstech.com wrote:
Hello all,

I am working on stock balance report. I need a formula to calculate opening qty, In qty ,out qty , closing qty. I have checked "stock_balance.js" file but i can't understand the flow how to calculate these values.

I tried this query but it gives wrong values.please check it http://pastebin.com/1qBSD6fx
Query Details
1 For opening qty, we have checked sum of actual_qty whose voucher_nos in respective table with docstatus=1 
2 For  In Qty, we have checked sum of actual_qty whose voucher_type is Purchase Receipt and Stock Entry with (purpose ='Material Receipt') and voucher_nos in respective table with docstatus=1 
3 For  Out Qty, we have checked sum of actual_qty whose voucher_type is Delivery Note and Stock Entry with (purpose= 'Material Transfer','Material Issue') and Sales Invoice and voucher_nos in respective table with docstatus=1 

Thanks
amit



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



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

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

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

That is a very complex query - you will have to debug yourself...

Don't forget to send a PR if you fix it :)

- Rushabh

On Friday, November 22, 2013 1:37:45 PM UTC+5:30, am...@indictranstech.com wrote:
Hello all,

I have tried again by referring "stock_balance.js" , but i am not able to calculate the In_qty, Out_qty. I have import database dump locally and i want to generate charts locally for that i need this values. I have "tabStock Ledger Entry" table with actual_qty column with +ve and -ve values. 
By considering stock_balance.js code 
for opening we are adding all values and
for in_qty , out_qty we are ignoring stock entry with purpose 'Material Transfer' (as ingnore_inflow_outflow) and adding all remaining values which are less than 0 for out and greater than 0 for in.

But still records are getting wrong. In "stock_balance.js"  we use qty column but to create qty column which filter are applied so that query will give correct results?

Can anyone please help me to generate sql query or explain which condition i should apply to get correct result....

Thanks in advance :)
Amit
 
On Wednesday, November 20, 2013 5:13:13 PM UTC+5:30, am...@indictranstech.com wrote:
Hello all,

I am working on stock balance report. I need a formula to calculate opening qty, In qty ,out qty , closing qty. I have checked "stock_balance.js" file but i can't understand the flow how to calculate these values.

I tried this query but it gives wrong values.please check it http://pastebin.com/1qBSD6fx
Query Details
1 For opening qty, we have checked sum of actual_qty whose voucher_nos in respective table with docstatus=1 
2 For  In Qty, we have checked sum of actual_qty whose voucher_type is Purchase Receipt and Stock Entry with (purpose ='Material Receipt') and voucher_nos in respective table with docstatus=1 
3 For  Out Qty, we have checked sum of actual_qty whose voucher_type is Delivery Note and Stock Entry with (purpose= 'Material Transfer','Material Issue') and Sales Invoice and voucher_nos in respective table with docstatus=1 

Thanks
amit



Note:

 

If you are posting an issue,

  1. We should be able to replicate it at our end. So please give us as much information as you can. Please see it from the point of view of the person receiving the communication.
  2. Paste your code at http://pastebin.com or http://gist.github.com and send only the URL via email
  3. For sending images, use http://imgur.com or other similar services. Do not send images as attachments. Links are good. Same goes for any file you are going to send.

     

    End of Note



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

    To unsubscribe from this group and stop receiving emails from it, send an email to erpnext-developer-forum+un...@googlegroups.com.

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