GSTR 1 query reports

[Amit_Saxena]

I am sharing the sqls for query reports for GSTR 1 reports that I have used for my company. They are formatted to generate CSV as per the XLS sheet of GSTR1.

GSTR 1 - B2B

select 
xta.gstin as "GST Number:Text:130",
xta.name as "Invoice Number:Link/Sales Invoice:200",
xta.posting_date as "Date:Date:120" ,
xta.total as "Taxable Value:Float:120",
CAST(LPAD(xta.gst_state_number,2,'0') AS CHAR) as "Place Of Supply:Text:150",
xta.reverse_charge as "Reverse Charge:Text:20",
xta.invoice_type as "Invoice Type:Text:120",
xta.ecommerce_gstin as "E-Commerce GSTIN:Text:130",
xta.rate as "Rate:Float:130",
xta.taxable_value as "Taxable Value:Float:130",

(xta.taxable_value*xta.rate/100) as "Tax:Float:130",
0 as "Cess:Float:120",
xta.month,
xta.account_head

from
(
select 
ta.gstin ,
tsi.name ,
tsi.posting_date ,
tsi.total  ,
ta.state ,
ta.gst_state_number,
tsi.reverse_charge ,
tsi.invoice_type ,
tsi.ecommerce_gstin,

 (case when (select count(name) from `tabItem Tax` where `tabItem Tax`.parent=tsit.item_code) >0 
                     THEN (select sum(tax_rate) as tax_rate from `tabItem Tax` where `tabItem Tax`.parent = tsit.item_code and LEFT(`tabItem Tax`.tax_type,6)="IGST -")
             else  (select sum(rate) as tax_rate  from `tabSales Taxes and Charges` where `tabSales Taxes and Charges`.parent = tsi.name and
                         LEFT(tstc.account_head,6) in ("IGST -","SGST -","CGST -")) END)      
                        as rate,
(tsit.net_amount) as "taxable_value",

Left(tstc.account_head,4) as "account_head", 

Mid(tsi.posting_date,6,2) as "month"

from `tabSales Invoice Item` as tsit 
inner join `tabSales Invoice` as tsi on tsi.name=tsit.parent 

inner join `tabAddress` as ta on ta.name=tsi.customer_address
inner join `tabSales Taxes and Charges` as tstc on tstc.parent=tsi.name

     
 where 
tsi.docstatus=1 and 

Left(tstc.account_head,6) in ("CGST -","IGST -")
and ta.gstin <> "NA"



) as xta where xta.month=07 order by xta.posting_date,xta.name ;

B2Cl

select 
xta.name as "Invoice Number:Link/Sales Invoice:200",
xta.posting_date as "Date:Date:120" ,
xta.grand_total as "Invoice Value:Float:120",
case 
when xta.state="JAMMU AND KASHMIR" Then  "01"
when xta.state="HIMACHAL PRADESH" Then  "02"
when xta.state="PUNJAB" Then  "03"
when xta.state="CHANDIGARH" Then  "04"
when xta.state="UTTARAKHAND" Then  "05"
when xta.state="HARYANA" Then  "06"
when xta.state="DELHI" Then  "07"
when xta.state="RAJASTHAN" Then  "08"
when xta.state="UTTAR PRADESH" Then  "09"
when xta.state="BIHAR" Then  "10"
when xta.state="SIKKIM" Then  "11"
when xta.state="ARUNACHAL PRADESH" Then  "12"
when xta.state="NAGALAND" Then  "13"
when xta.state="MANIPUR" Then  "14"
when xta.state="MIZORAM" Then  "15"
when xta.state="TRIPURA" Then  "16"
when xta.state="MEGHLAYA" Then  "17"
when xta.state="ASSAM" Then  "18"
when xta.state="WEST BENGAL" Then  "19"
when xta.state="JHARKHAND" Then  "20"
when xta.state="ODISHA" Then  "21"
when xta.state="CHATTISGARH" Then  "22"
when xta.state="MADHYA PRADESH" Then  "23"
when xta.state="GUJARAT" Then  "24"
when xta.state="DAMAN AND DIU" Then  "25"
when xta.state="DADRA AND NAGAR HAVELI" Then  "26"
when xta.state="MAHARASHTRA" Then  "27"
when xta.state="ANDHRA PRADESH(BEFORE DIVISION)" Then  "28"
when xta.state="KARNATAKA" Then  "29"
when xta.state="GOA" Then  "30"
when xta.state="LAKSHWADEEP" Then  "31"
when xta.state="KERALA" Then  "32"
when xta.state="TAMIL NADU" Then  "33"
when xta.state="PUDUCHERRY" Then  "34"
when xta.state="ANDAMAN AND NICOBAR ISLANDS" Then  "35"
when xta.state="TELANGANA" Then  "36"
when xta.state="ANDHRA PRADESH" Then  "37"
END  as "Place Of Supply:Text:150",
xta.rate as "Rate:Float:120",
xta.taxable_value as "Taxable Value:Float:120",
0 as "Cess:Float:120",
xta.ecommerce_gstin as "E-Commerce GSTIN:Text:130",



xta.month as "Month",
xta.account_head as "Account Head"



from
(
select 
ta.gstin ,
tsi.name ,
tsi.posting_date ,
tsi.total  ,
tsi.grand_total,
ta.state ,
tsi.reverse_charge ,
tsi.invoice_type ,
tsi.ecommerce_gstin,

 (case when (select count(name) from `tabItem Tax` where `tabItem Tax`.parent=tsit.item_code) >0 
                     THEN (select sum(tax_rate) as tax_rate from `tabItem Tax` where `tabItem Tax`.parent = tsit.item_code and LEFT(`tabItem Tax`.tax_type,6)="IGST -")
             else  (select sum(rate) as tax_rate  from `tabSales Taxes and Charges` where `tabSales Taxes and Charges`.parent = tsi.name and
                         LEFT(tstc.account_head,6) in ("IGST -","SGST -","CGST -")) END)      
                        as rate,
(tsit.net_amount) as "taxable_value",

Left(tstc.account_head,4) as "account_head", 

Mid(tsi.posting_date,6,2) as "month"

from `tabSales Invoice Item` as tsit 
inner join `tabSales Invoice` as tsi on tsi.name=tsit.parent 

inner join `tabAddress` as ta on ta.name=tsi.customer_address
inner join `tabSales Taxes and Charges` as tstc on tstc.parent=tsi.name

     
 where 
tsi.docstatus=1 and 

Left(tstc.account_head,6) in ("CGST -","IGST -") and
ta.gstin = "NA" and tsi.grand_total > 250000



) as xta where xta.month=07 order by xta.posting_date,xta.name ;

B2CS

select 
if (xta.ecommerce_gstin is NULL,"OE","E") as "Type:Text:25",
case 
when xta.state="JAMMU AND KASHMIR" Then  "01"
when xta.state="HIMACHAL PRADESH" Then  "02"
when xta.state="PUNJAB" Then  "03"
when xta.state="CHANDIGARH" Then  "04"
when xta.state="UTTARAKHAND" Then  "05"
when xta.state="HARYANA" Then  "06"
when xta.state="DELHI" Then  "07"
when xta.state="RAJASTHAN" Then  "08"
when xta.state="UTTAR PRADESH" Then  "09"
when xta.state="BIHAR" Then  "10"
when xta.state="SIKKIM" Then  "11"
when xta.state="ARUNACHAL PRADESH" Then  "12"
when xta.state="NAGALAND" Then  "13"
when xta.state="MANIPUR" Then  "14"
when xta.state="MIZORAM" Then  "15"
when xta.state="TRIPURA" Then  "16"
when xta.state="MEGHLAYA" Then  "17"
when xta.state="ASSAM" Then  "18"
when xta.state="WEST BENGAL" Then  "19"
when xta.state="JHARKHAND" Then  "20"
when xta.state="ODISHA" Then  "21"
when xta.state="CHATTISGARH" Then  "22"
when xta.state="MADHYA PRADESH" Then  "23"
when xta.state="GUJARAT" Then  "24"
when xta.state="DAMAN AND DIU" Then  "25"
when xta.state="DADRA AND NAGAR HAVELI" Then  "26"
when xta.state="MAHARASHTRA" Then  "27"
when xta.state="ANDHRA PRADESH(BEFORE DIVISION)" Then  "28"
when xta.state="KARNATAKA" Then  "29"
when xta.state="GOA" Then  "30"
when xta.state="LAKSHWADEEP" Then  "31"
when xta.state="KERALA" Then  "32"
when xta.state="TAMIL NADU" Then  "33"
when xta.state="PUDUCHERRY" Then  "34"
when xta.state="ANDAMAN AND NICOBAR ISLANDS" Then  "35"
when xta.state="TELANGANA" Then  "36"
when xta.state="ANDHRA PRADESH" Then  "37"
END AS "STATE:TEXT:50",
xta.rate as "Rate:Float:120",

xta.total as "Taxable Value:Float:120",
0 as "Cess Amount:Float:120",
xta.ecommerce_gstin as "E-Commerce GSTIN:Text:130",


Mid(xta.posting_date,6,2) as "Month",
xta.posting_date as "Date:Date:120",
xta.name as "Invoice Number:Link/Sales Invoice:120"


from
(
select 
ta.gstin ,
tsi.name ,
tsi.posting_date ,
tsi.total  ,
ta.state ,
tsi.reverse_charge ,
tsi.invoice_type ,
tsi.ecommerce_gstin,

 (case when (select count(name) from `tabItem Tax` where `tabItem Tax`.parent=tsit.item_code) >0 
                     THEN (select sum(tax_rate) as tax_rate from `tabItem Tax` where `tabItem Tax`.parent = tsit.item_code and LEFT(`tabItem Tax`.tax_type,6)="IGST -")
             else  (select sum(rate) as tax_rate  from `tabSales Taxes and Charges` where `tabSales Taxes and Charges`.parent = tsi.name and
                         LEFT(tstc.account_head,6) in ("IGST -","SGST -","CGST -")) END)      
                        as rate,
(tsit.net_amount) as "taxable_value",

Left(tstc.account_head,4) as "account_head", 

Mid(tsi.posting_date,6,2) as "month"

from `tabSales Invoice Item` as tsit 
inner join `tabSales Invoice` as tsi on tsi.name=tsit.parent 

inner join `tabAddress` as ta on ta.name=tsi.customer_address
inner join `tabSales Taxes and Charges` as tstc on tstc.parent=tsi.name

     
 where 
tsi.docstatus=1 and 

Left(tstc.account_head,6) in ("CGST -","IGST -")



) as xta where xta.month=07 order by xta.posting_date,xta.name ;

HSN

select
xta.gst_hsn_code as "HSN:Text:130",
xta.item_code,
xta.item_name as "Description:Text:130",
CASE WHEN xta.uom in ("BAG","CTN","MTS","TGM","BAL","DOZ","NOS","THD","BDL","DRM","PAC",
"TON","BKL","GGK""PCS","TUB","BOU","GMS","PRS-PAIRS","UGS","BOX","GRS","QTL","UNT","BTL","GYD","ROL","YDS","BUN","KGS","SET","OTH","CAN","KLR","SQF","CBM",
"KME","SQM","CCM","MLT","SQY","CMS","MTR","TBS")	THEN (select concat(name,"-",uom_name) from `tabUOM` where name=xta.uom)
     ELSE "OTH-Others" END AS "UQM:Text:120", 
sum(xta.qty) as "Quantity:Float:100",
sum(xta.net_amount*(1+xta.rate/100)) as "Total Value:Float:130",
sum(xta.net_amount) as "Total Taxable Value:Float:130",

sum(if(xta.account_head="IGST -",(xta.net_amount*xta.rate/100),0)) as "Integrated Tax Amount:Float:130",
sum(if(xta.account_head="CGST -",(xta.net_amount*xta.rate/100),0)) as "Total Central Tax:Float:130",
sum(if(xta.account_head="CGST -",(xta.net_amount*xta.rate/100),0)) as "Total State/UT Tax:Float:130",
0 as "Total Cess:Float:120",

Mid(xta.posting_date,6,2) as "Month",

LEFT(xta.posting_date,4) as "Year"

from
(
select 
ta.gstin ,
tsi.name ,
tsit.item_code,
tsit.gst_hsn_code,
tsit.item_name,
tsit.uom,

tsit.qty,

tsit.net_amount,

tsi.posting_date ,
tsi.total  ,
ta.state ,
tsi.reverse_charge ,
tsi.invoice_type ,
tsi.ecommerce_gstin,

 (case when (select count(name) from `tabItem Tax` where `tabItem Tax`.parent=tsit.item_code) >0 
                     THEN (select (tax_rate) as tax_rate from `tabItem Tax` 
                               where `tabItem Tax`.parent = tsit.item_code and 
                                      (LEFT(`tabItem Tax`.tax_type,6) in ("IGST -","CGST -") )and `tabItem Tax`.tax_type=tstc.account_head LIMIT 1
                           )
             else  (select (rate) as tax_rate  from `tabSales Taxes and Charges` where `tabSales Taxes and Charges`.parent = tsi.name and
                         LEFT(`tabSales Taxes and Charges`.account_head,6) in ("IGST -","CGST -") LIMIT 1) END)      
                        as rate,
(tsit.net_amount) as "taxable_value",

Left(tstc.account_head,6) as "account_head", 

Mid(tsi.posting_date,6,2) as "month"

from `tabSales Invoice Item` as tsit 
inner join `tabSales Invoice` as tsi on tsi.name=tsit.parent 

inner join `tabAddress` as ta on ta.name=tsi.customer_address
inner join `tabSales Taxes and Charges` as tstc on tstc.parent=tsi.name

     
 where 
tsi.docstatus=1 and 

Left(tstc.account_head,6) in ("CGST -","IGST -") and
CAST(LEFT(tsi.posting_date,4) AS UNSIGNED) = if (MID(curdate(),6,2)="01",CAST(LEFT(curdate(),4) AS UNSIGNED)-1,CAST(LEFT(curdate(),4) AS UNSIGNED)) and
CAST(MID(tsi.posting_date,6,2) AS UNSIGNED) = if (MID(curdate(),6,2)=01,"12",CAST(MID(curdate(),6,2) AS UNSIGNED)-2)



) as xta group by concat(xta.gst_hsn_code,xta.item_name) order by xta.posting_date,xta.name ;

You may need to format the sql’s as per your requirements

-Amit

[FinForce]

@Amit_Saxena: Thank you for sharing SQL query for generating a report for GSTR1, but how run this query. I understand a little bit on database & SQL queries, but how does it actually work on ERPNext is still to explore. Could you please guide?

[Amit_Saxena]

@FinForce

1. Login into ErpNext using admin account
2. Open DocType Report in setup/ by typing in the search bar and Create a new Report
3. In Field Ref Doc Type put Sales Invoice
4. In Field Report Type select Query Report
5. In Module Select Accounts
6. In Query, copy paste the query

Then you can see the report in Module Account->Custom Reports. You can also check the report from the report by pressning Show Report

Hope this helps

-Amit

[Boacasa]

@Amit_Saxena

Thanks for sharing the codes but can you share the generic codes using which accounts hosted on ERP can also use this feature?

[Amit_Saxena]

Please see my previous mails. You can use the sqls in the query reports which will be available in the hosted accounts also.
Please note that you may have to adjust the sqls as per your setup.

-Amit

[wale]

Hi @Boacasa @Amit_Saxena

Unfortunately, there’s no way to generate your own Query reports if you’re hosting with ERPNext. It’s quite sad really

Query/Script Reports for Cloud Users

Hi Is there any arrangement for cloud users to generate query/script reports? I’m aware it’s still restricted from the UI but there should at least be some individual back-end arrangement or process to add these kinds of reports Cloud users shouldn’t be deprived of generating the reports they need and adding every custom report (from every cloud user) to the list of reports might be quite messy and overwhelming Please advise Thanks

Kind regards,

[Aditya_Iyer]

Hi ErpNext team,
Can we check if these reports are going to be rolled out in a future release. Not having these standard gst report is a challenge for Indian businesses hosted on ErpNext.
Thanks

[Boacasa]

Hi amit,

Will it be possible for you to coordinate with erpnext team and share the code so they can do a git hub pull request and everyone benefits,since most of us are from non-technical and are unable to understand what changes have to be done so it works for our setup.

[ajit.jain1]

Hi @Amit_Saxena ,

I’ve tried your codes and followed the methods as you told, but the report is not querying the data.
The report is created but it is completely blank.

Can you please help.

Thanks and regards
Ajit

[ajit.jain1]

Any update regarding this?

[dineshpanchal432]

We are also facing same issue ?? Please update soon @Amit_Saxena

Thank you !!!

[Amit_Saxena]

@Boacasa @ajit.jain1 @dineshpanchal432
Sorry for the delay in responding.
I will try to put in the script reports and merge it to the erpnext. I will keep you posted in the same.

-Amit

[Amit_Saxena]

Have created a PR #11803 for B2B B2CL and B2CS reports

-Amit