# ERP Item Master HSN and GST Rate Audit SQL Generator

> Source: https://netray.co/tools/erp-item-master-hsn-gst-rate-audit-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Enter your item master and invoice line table and column names to get SQL that finds blank HSN codes, wrong digit counts, items with several rates and invoice lines that disagree with the master.

## Category

developer

## Region

India

## Audience

- ERP developer
- GST accountant
- Master data owner
- Database administrator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Item master table

**Key:** item_table

**Type:** text

**Default:** item_master

#### Item code column

**Key:** item_code

**Type:** text

**Default:** item_code

#### Item description column

**Key:** item_desc

**Type:** text

**Default:** item_description

#### HSN column

**Key:** hsn_col

**Type:** text

**Default:** hsn_code

#### GST rate column

**Key:** rate_col

**Type:** text

**Default:** gst_rate

#### Invoice line table

**Key:** line_table

**Type:** text

**Default:** sales_invoice_lines

#### Line item code column

**Key:** line_item

**Type:** text

**Default:** item_code

#### Line HSN column

**Key:** line_hsn

**Type:** text

**Default:** hsn_code

#### Line GST rate column

**Key:** line_rate

**Type:** text

**Default:** gst_rate

#### Minimum HSN digits you require

**Key:** min_digits

**Type:** select

**Options:**

- 4
- 6
- 8

**Default:** 6

**Blocks:**

#### 1. Items with blank HSN

**Body:** SELECT {{item_code}}, {{item_desc}}
FROM {{item_table}}
WHERE {{hsn_col}} IS NULL
   OR LTRIM(RTRIM({{hsn_col}})) = ''
ORDER BY {{item_code}};

#### 2. HSN shorter than your minimum, or not numeric

**Body:** SELECT {{item_code}}, {{item_desc}}, {{hsn_col}}
FROM {{item_table}}
WHERE {{hsn_col}} IS NOT NULL
  AND (LEN(LTRIM(RTRIM({{hsn_col}}))) < {{min_digits}}
       OR {{hsn_col}} LIKE '%[^0-9]%')
ORDER BY {{item_code}};

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

**Body:** SELECT {{hsn_col}}, COUNT(DISTINCT {{rate_col}}) AS rate_count,
       MIN({{rate_col}}) AS min_rate, MAX({{rate_col}}) AS max_rate,
       COUNT(*) AS item_count
FROM {{item_table}}
WHERE {{hsn_col}} IS NOT NULL
GROUP BY {{hsn_col}}
HAVING COUNT(DISTINCT {{rate_col}}) > 1
ORDER BY item_count DESC;

#### 4. Invoice lines that differ from the item master

**Body:** SELECT l.{{line_item}}, m.{{hsn_col}} AS master_hsn, l.{{line_hsn}} AS line_hsn,
       m.{{rate_col}} AS master_rate, l.{{line_rate}} AS line_rate
FROM {{line_table}} l
JOIN {{item_table}} m ON m.{{item_code}} = l.{{line_item}}
WHERE l.{{line_hsn}} <> m.{{hsn_col}}
   OR l.{{line_rate}} <> m.{{rate_col}};

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Which database does this SQL work on?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

sql query missing hsn code item master, erp hsn code audit sql, gst rate mismatch sql, item master data cleanup india, hsn code validation query, sap material master hsn check, oracle item hsn audit query, find items without hsn sql

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json