How to generate query report for Stock movement between two dates

stkitmanager · December 20, 2012, 8:01am

Dear All

I have to present a report to management that for each item wise,what we received a stock in main warehouse, what issues to vans , what they sell and what is the balance in both main warehouse and van between any two dates

Thanks

–

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/-/ZbS_d2oOyJgJ.

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

rmehta · December 20, 2012, 10:36am

Hi Mahr,

This report is in the works - will update you as soon as its done.

- Rushabh

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

On 20-Dec-2012, at 1:31 PM, Stk Intl Trading IT Manager Mahr Haider <st...@gmail.com> wrote:

Dear All

I have to present a report to management that for each item wise,what we received a stock in main warehouse, what issues to vans , what they sell and what is the balance in both main warehouse and van between any two dates

Thanks

–

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/-/ZbS_d2oOyJgJ.

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.

stkitmanager · December 20, 2012, 12:38pm

Dear

I need a option to make a query report. I can write a multi queries.

select tabItem.barcode    as “Bar Code”,
         tabItem.item_code as “Item Code”,
         tabItem.item_name as “Item Name”,
         tabItem.item_group as “Group”,
         tabItem.brand      as “Brand”,

          (select sum(tabPurchase Receipt Item.qty)
             from tabPurchase Receipt Item
            where tabPurchase Receipt Item.item_code=tabItem.item_code
              and tabPurchase Receipt Item.warehouse=‘DIP Warehouse’
          ) as “GRN Qty”,



            (select sum(tabStock Entry Detail.qty)
             from tabStock Entry,tabStock Entry Detail
            where tabStock Entry.name=tabStock Entry Detail.parent
              and tabStock Entry Detail.item_code=tabItem .item_code
              and tabStock Entry.purpose=‘Material Transfer’
              and tabStock Entry.docstatus=‘1’
              and tabStock Entry.from_warehouse=‘DIP Warehouse’
           group by tabStock Entry Detail.item_code
          ) as “Transfer To Van”,

           (select sum(tabStock Entry Detail.qty)
             from tabStock Entry,tabStock Entry Detail
            where tabStock Entry.name=tabStock Entry Detail.parent
              and tabStock Entry Detail.item_code=tabItem .item_code
              and tabStock Entry.purpose in (‘Material Receipt’,‘Material Transfer’)
              and tabStock Entry.docstatus=‘1’
              and tabStock Entry.to_warehouse=‘DIP Warehouse’
           group by tabStock Entry Detail.item_code
          ) as “Return from Van”,

          (select sum(tabStock Entry Detail.qty)
             from tabStock Entry,tabStock Entry Detail
            where tabStock Entry.name=tabStock Entry Detail.parent
              and tabStock Entry Detail.item_code=tabItem .item_code
              and tabStock Entry.purpose=‘Material Issue’
              and tabStock Entry.docstatus=‘1’
              and tabStock Entry.from_warehouse=‘DIP Warehouse’
           group by tabStock Entry Detail.item_code
          ) as “Stock Issue”,

          (select sum(tabStock Entry Detail.qty)
             from tabStock Entry,tabStock Entry Detail
            where tabStock Entry.name=tabStock Entry Detail.parent
              and tabStock Entry Detail.item_code=tabItem .item_code
              and tabStock Entry.purpose=‘Sales Return’
              and tabStock Entry.docstatus=‘1’
              and tabStock Entry.to_warehouse=‘DIP Warehouse’
           group by tabStock Entry Detail.item_code
          ) as “Sales Return”

from   tabItem
where tabItem.is_sales_item=‘Yes’
order by tabItem.item_code

On Thursday, 20 December 2012 12:01:24 UTC+4, Stk Intl Trading IT Manager Mahr Haider wrote:

Dear All

I have to present a report to management that for each item wise,what we received a stock in main warehouse, what issues to vans , what they sell and what is the balance in both main warehouse and van between any two dates

Thanks

–

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/-/lSCj0ji_DGkJ.

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