Report for Indian Sales Tax from Purchase and Sales Invoice

Hi,

I am looking for a report which could help us in making the Sales Tax returns. Now in this we need the following data in a period

  1. Name of Customer
  2. Total Amount of Sales Made to the customer (Sum of Grand Total)
  3. Net total before Sales Tax (Please note that this Net total is including the excise duty)
  4. Rate of Sales Tax Charged
  5. Total Amount of Sales Tax Charged in the invoices.

Now currently we are using the Sales Register or the Purchase register for this purpose but this report throws a lot of columns especially in case of Purchase Register which becomes confusing.

Is there any report for this kind of functionality?

@adityaduggal, with your SQL expertise you should be easily be able to make one.

@nabinhait can you help?

I think I can make such report the only problems that I am facing are:

  1. I need to specify which invoices are only for Sales Tax.
  2. Invoices in my company are also having excise duty and there is no field to differentiate between taxes like Sales Tax or Excise or Service Tax

I would be planning to make an app for the same since I guess it would need some custom fields like in account master where we define an account as Tax.

I have added the report but I am unable to find a way to populate the tax part in the columns.

I have also created a Custom field in the account master as Tax Type.

Basically the idea is to make the report which would sum all the amount amounts before sales tax (local and central) and then give the sum of the taxes (sales taxes) in the report for the given period.

But I need to enter the condition wherein I could check if the account’s tax_type is sales tax or not and if not then I would be given the amount of net total.

The link for the report is below:

https://github.com/adityaduggal/rohit-common/tree/stax/rohit_common/rohit_common/report/st_return_sales

@nabinhait is the expert here. Nabin - can you help sometime tomorrow?

Hint:

invoices_with_sales_tax = frappe.db.sql("""
    select si.name, si.customer, sum(si_tax.tax_amount) as sales_tax_amount, si.grand_total
    from `tabSales Invoice` si, `tabSales Taxes and Charges` si_tax 
    where si.name= si_tax.parent and si_tax.docstatus=1 
        and (select tax_type from `tabAccount` where account_head=si_tax.account_head)='Sales Tax' 
    group by si.name
    order by si.posting_date""")

for inv in invoices_with_sales_tax:
    # net_total = grand_total - sales_tax_amount

Thanks alot for the hint @nabinhait.

I have now made the report for Sales Invoices.

The report can be found out @ https://github.com/adityaduggal/rohit-common/tree/stax/rohit_common/rohit_common/report/st_return_sales

Later I would be making a report for purchase invoices as well so it would cover the Sales and Purchase Cycle.

Now I want to develop an App for Indian Taxation purposes so that all other users could also use it. Something like based on base_vat. But the problem is that I have my customizations in another module and I am kind of confused as to how I could just remove them from the existing customizations and inculcate them into another app so that all other people could use it.

Hi @nabinhait

I have hit a roadblock in this report for Sales Tax. The problem is that the current code for my purchase is giving incorrect values. The report can be found here https://github.com/adityaduggal/rohit-common/blob/stax/rohit_common/rohit_common/report/st_return_purchase/st_return_purchase.py

Now the problem is that in a purchase invoice’s taxes there can be multiple accounts from same tax type. For example, look at the below charges as below:

  1. Input VAT @ 12.5 (Goes to account XYZ which is account type Sales Tax)
  2. Cess on VAT @ 5% (goes to account Input VAT which is same as above).

Now with the current code, the report is adding the net total 2 times since the same tax type is coming 2 times in the account master of the account. Is there a way I could just consider this net total only once so and add the tax types together.

I hope my explanation is clear enough.

I have a problem, see the image below for the tax table in a typical purchase invoice:

http://imgur.com/45AVk4s

Now row no.1 to 4 are one tax type whereas row no 5 and 6 are other tax type. My code in the repo see:

https://github.com/adityaduggal/rohit-common/blob/stax/rohit_common/rohit_common/report/st_return_purchase/st_return_purchase.py#L27

Now the problem is that when I use the in SUM(pi_tax.total - pi_tax.tax_amount) then the sum is taken twice for the same purchase invoice since the tax type is coming 2 times in row 5 & 6 but if I remove the sum then if there are multiple purchase invoices then that fails.

I am surely missing a trick which I am unable to resolve due to my limited knowledge of mysql.