Developer ToolsFree generatorPDF, Excel, Word exportIndia

ERP Item Master HSN and GST Rate Audit SQL Generator

Wrong or missing HSN codes in the item master travel into every invoice, GSTR-1 HSN summary and ITC reconciliation. These four queries find the usual problems quickly, without touching data.

Fill in

Item master table

Item code column

Item description column

HSN column

GST rate column

Invoice line table

Line item code column

Line HSN column

Line GST rate column

Minimum HSN digits you require

Generated code

Download:

1. Items with blank HSN

SELECT item_code, item_description
FROM item_master
WHERE hsn_code IS NULL
   OR LTRIM(RTRIM(hsn_code)) = ''
ORDER BY item_code;

2. HSN shorter than your minimum, or not numeric

SELECT item_code, item_description, hsn_code
FROM item_master
WHERE hsn_code IS NOT NULL
  AND (LEN(LTRIM(RTRIM(hsn_code))) < 6
       OR hsn_code LIKE '%[^0-9]%')
ORDER BY item_code;

3. Same HSN carrying more than one GST rate in the master

SELECT hsn_code, COUNT(DISTINCT gst_rate) AS rate_count,
       MIN(gst_rate) AS min_rate, MAX(gst_rate) AS max_rate,
       COUNT(*) AS item_count
FROM item_master
WHERE hsn_code IS NOT NULL
GROUP BY hsn_code
HAVING COUNT(DISTINCT gst_rate) > 1
ORDER BY item_count DESC;

4. Invoice lines that differ from the item master

SELECT l.item_code, m.hsn_code AS master_hsn, l.hsn_code AS line_hsn,
       m.gst_rate AS master_rate, l.gst_rate AS line_rate
FROM sales_invoice_lines l
JOIN item_master m ON m.item_code = l.item_code
WHERE l.hsn_code <> m.hsn_code
   OR l.gst_rate <> m.gst_rate;

Table and column names are generic. Map them to your own schema, for example your ERP's item and invoice line tables. LEN and LTRIM/RTRIM syntax is for SQL Server; on Oracle use LENGTH and TRIM, and replace the LIKE pattern with REGEXP_LIKE. Run read-only on a reporting copy first.

Item master clean-up support

Optional help running a full item master audit directly from ERP data.

Optional. The tool and every export work without this. No spam.

How to use the generator

Enter your own table and column names in the fields. The four queries update with your names. Paste them into your database client against a reporting copy or read-only replica and run them in order. Export each result set to Excel and send it to the master data owner for correction. Start with query 1 and 2, because blanks and short codes are the easiest fixes and remove most of the noise.

Replace default table names with your own
Set the minimum digits to match your turnover based HSN rule
Run on a copy first and keep results as evidence

What each query catches

Query 1 lists items with no HSN at all. Query 2 lists items whose HSN is too short or contains non-numeric characters such as spaces or dots. Query 3 groups items by HSN and flags any HSN carrying more than one GST rate, which is sometimes legitimate if the rate depends on price slabs or conditions, but usually means one record is wrong. Query 4 compares posted invoice lines with the master to show where the line level data drifted from the master.

Worked example

A plant with 12,000 items runs query 2 with a minimum of 6 digits and finds 400 items with 4 digit codes copied from old vendor invoices. Query 3 shows one HSN with rates of 12 and 18 percent across 30 items. Reviewing the 30 items shows 8 were created with the old rate before a notification change. After correction, query 4 against the last quarter's invoices shows which invoices need a credit or debit note review with the GST adviser.

Common mistakes

Do not mass update the master without review, because a rate can legitimately differ for the same HSN under different conditions. Do not run the update on production without a backup and an approval trail. Remember that SQL Server and Oracle syntax differ, so adapt the string functions. Finally, treat the results as a list for review, not as a verdict that every flagged item is wrong.

Frequently Asked Questions

Which database does this SQL work on?

The syntax is SQL Server style with LEN, LTRIM and RTRIM. For Oracle, replace LEN with LENGTH, use TRIM, and use REGEXP_LIKE in place of the LIKE character class. The notes under the generator explain the swaps.

Can I use it on SAP or Oracle EBS tables?

Yes, but you must map the generic names to the real tables and fields in your system. The query shapes remain the same, though SAP stores tax classification in different structures, so confirm the right fields with your functional consultant.

Is it safe to run on production?

The queries are read-only SELECT statements, but heavy joins can still slow a live system. Run them on a reporting copy or off peak hours, and agree access with your database administrator first.

How many HSN digits should the master hold?

At least the digits you are required to show on invoices, which depends on turnover and supply type under current GST notifications. Many plants store 8 digits so they can serve both domestic and export needs.

If you want these checks scheduled inside your ERP with approval workflows and alerts, we can help build that around your data, on your own infrastructure.

Talk to an Expert Explore More Free Tools

Related Tools

Country & Tax

More Developer Tools tools

See all

Back to All Tools