How build Report - Sales Person Target Variance Item Groupwise based on sales invoice?

ERPNext Reports
1

Hi Team ,

I want to generate Sales Person Target Variance Item Group-wise Report based on sales invoice .Currently this report based on sales order.Any help ? @chdecultot Thanks in advance

2

Hi @Sathams_Tech_TV,

This report is a script report, so you’ll have to create your own report by copying the Sales Person Target Variance Item Group Wise report and changing the data fetched from sales orders to data fetched from sales invoices.

Here is the link to the code from the original report:

Here is the interesting database query that needs to be changed:

image
image941Ă—693 72.6 KB

Good luck!

3

Thank you for the response…Will check …

4

Hi ,
I tried getting fetching error…

5

Finally done.Build sales person target variance item group-wise report based on sales invoice

Modified one for sales invoice :

#Get achieved details from sales order
def get_achieved_details(filters, sales_person, all_sales_persons, target_item_group, item_groups):
start_date, end_date = get_fiscal_year(fiscal_year = filters[“fiscal_year”])[1:]

    item_details = frappe.db.sql("""
            select
                    sum(sii.stock_qty * (st.allocated_percentage/100)) as qty,
                    sum(sii.base_net_amount * (st.allocated_percentage/100)) as amount,
                    st.sales_person, MONTHNAME(si.creation) as month_name
            from
                    `tabSales Invoice Item` sii, `tabSales Invoice` si, `tabSales Team` st
            where
                    sii.parent=si.name and si.docstatus=1 and st.parent=si.name
                    and si.creation>=%s and si.creation<=%s
                    and exists(select name from `tabSales Person` where lft >= %s and rgt <= %s and name=st.sales_person)
                    and exists(select name from `tabItem Group` where lft >= %s and rgt <= %s and name=sii.item_group)

            group by
                    sales_person, month_name
                    """,
            (start_date, end_date, all_sales_persons[sales_person].lft, all_sales_persons[sales_person].rgt,
                    item_groups[target_item_group].lft, item_groups[target_item_group].rgt), as_dict=1)

    actual_details = {}
    for d in item_details:
            actual_details.setdefault(d.month_name, frappe._dict({
                    "quantity" : 0,
                    "amount" : 0
            }))

            value_dict = actual_details[d.month_name]
            value_dict.quantity += flt(d.qty)
            value_dict.amount += flt(d.amount)

    return actual_details

Thank u for the support!!!

1 Like