SALES INVOICE TAX OUTPUT 250829

Sales Invoice Tax Output Report

This page documents the development history and SQL query versions for the Sales Invoice Tax Output custom report in ERPNext. It is used to extract VAT output tax data per sales invoice, including customer tax IDs, payment references, project links, and item descriptions.

---

Report Columns

The final report includes the following columns:

• Posting Date
• Sales Invoice ID
• Customer Tax ID
• Customer
• Invoice Total
• Grand Total
• Amount (Sales Taxes and Charges) – VAT Output filtered by account head
• Sales Orders ID (via Project) – SO linked through project dimension (preferred)
• Sales Orders ID (from Items) – SO directly on SI items (secondary)
• Payment ID – Payment Entry references
• Account Head (Sales Taxes and Charges)
• Debit To
• Against Income Account
• Project
• Projects/JO (List)
• Description

---

Query Version History

250829 – Initial Version (Missing Description and SO)

SELECT
    si.posting_date                  AS "Posting Date:Date:100",
    si.name                          AS "Sales Invoice ID:Link/Sales Invoice:140",
    cust.tax_id                      AS "Customer Tax ID:Data:150",
    si.customer                      AS "Customer:Data:200",
    si.total                         AS "Invoice Total:Currency:150",
    si.grand_total                   AS "Grand Total:Currency:150",
    (
        SELECT COALESCE(SUM(stc.tax_amount_after_discount_amount), 0)
        FROM `tabSales Taxes and Charges` stc
        WHERE stc.parent = si.name
          AND stc.account_head = '2506 - VATOUTPUT TAXES PAYABLE - ESCO'
    ) AS "Amount (Sales Taxes and Charges):Currency:140",
    (
        SELECT GROUP_CONCAT(DISTINCT sii.sales_order SEPARATOR ', ')
        FROM `tabSales Invoice Item` sii
        WHERE sii.parent = si.name AND sii.sales_order IS NOT NULL
    ) AS "Sales Orders ID:Data:200",
    (
        SELECT GROUP_CONCAT(DISTINCT per.parent SEPARATOR ', ')
        FROM `tabPayment Entry Reference` per
        WHERE per.reference_doctype = 'Sales Invoice'
          AND per.reference_name = si.name
    ) AS "Payment ID:Link/Payment Entry:200",
    (
        SELECT stc2.account_head
        FROM `tabSales Taxes and Charges` stc2
        WHERE stc2.parent = si.name
          AND stc2.account_head = '2506 - VATOUTPUT TAXES PAYABLE - ESCO'
        LIMIT 1
    ) AS "Account Head (Sales Taxes and Charges):Data:240",
    si.debit_to                      AS "Debit To:Link/Account:200",
    (
        SELECT GROUP_CONCAT(DISTINCT sii3.income_account SEPARATOR ', ')
        FROM `tabSales Invoice Item` sii3
        WHERE sii3.parent = si.name
    ) AS "Against Income Account:Data:220",
    (
        SELECT sii2.project
        FROM `tabSales Invoice Item` sii2
        WHERE sii2.parent = si.name AND IFNULL(sii2.project, '') != ''
        LIMIT 1
    ) AS "Project:Link/Project:120",
    si.remarks                       AS "Remarks:Data:250"
FROM `tabSales Invoice` si
LEFT JOIN `tabCustomer` cust ON cust.name = si.customer
WHERE si.docstatus = 1
  AND si.company = %(company)s
  AND si.posting_date BETWEEN %(from_date)s AND %(to_date)s
ORDER BY si.posting_date, si.name

---

250829 – Final Version (Added Description, SO via Project, Projects List)

Key changes:

• Moved SO ID column to use project dimension (more reliable linkage).
• Kept SO ID via items as a secondary column.
• Added Description column (from SI item descriptions/names).
• Added Projects/JO (List) column.
• Reordered columns for clarity.

SELECT
    si.posting_date                  AS "Posting Date:Date:100",
    si.name                          AS "Sales Invoice ID:Link/Sales Invoice:160",
    cust.tax_id                      AS "Customer Tax ID:Data:150",
    si.customer                      AS "Customer:Data:200",
    si.total                         AS "Invoice Total:Currency:150",
    si.grand_total                   AS "Grand Total:Currency:150",
    (
        SELECT COALESCE(SUM(stc.tax_amount_after_discount_amount), 0)
        FROM `tabSales Taxes and Charges` stc
        WHERE stc.parent = si.name
          AND stc.account_head = '2506 - VATOUTPUT TAXES PAYABLE - ESCO'
    ) AS "Amount (Sales Taxes and Charges):Currency:160",
    (
        SELECT GROUP_CONCAT(DISTINCT sii_i.sales_order ORDER BY sii_i.sales_order SEPARATOR ', ')
        FROM `tabSales Invoice Item` sii_i
        WHERE sii_i.parent = si.name
          AND sii_i.sales_order IS NOT NULL AND sii_i.sales_order != ''
    ) AS "Sales Orders ID (from Items):Data:220",
    (
        SELECT GROUP_CONCAT(DISTINCT per.parent ORDER BY per.parent SEPARATOR ', ')
        FROM `tabPayment Entry Reference` per
        WHERE per.reference_doctype = 'Sales Invoice'
          AND per.reference_name = si.name
    ) AS "Payment ID:Link/Payment Entry:220",
    (
        SELECT stc2.account_head
        FROM `tabSales Taxes and Charges` stc2
        WHERE stc2.parent = si.name
          AND stc2.account_head = '2506 - VATOUTPUT TAXES PAYABLE - ESCO'
        LIMIT 1
    ) AS "Account Head (Sales Taxes and Charges):Data:260",
    si.debit_to                      AS "Debit To:Link/Account:200",
    (
        SELECT GROUP_CONCAT(DISTINCT sii_ia.income_account ORDER BY sii_ia.income_account SEPARATOR ', ')
        FROM `tabSales Invoice Item` sii_ia
        WHERE sii_ia.parent = si.name
    ) AS "Against Income Account:Data:240",
    (
        SELECT sii_p.project
        FROM `tabSales Invoice Item` sii_p
        WHERE sii_p.parent = si.name AND IFNULL(sii_p.project, '') != ''
        ORDER BY sii_p.idx ASC LIMIT 1
    ) AS "Project:Link/Project:140",
    (
        SELECT IFNULL(GROUP_CONCAT(
            IFNULL(NULLIF(sii_d.description, ''), IFNULL(sii_d.item_name, sii_d.item_code))
            ORDER BY sii_d.idx SEPARATOR '; '), '')
        FROM `tabSales Invoice Item` sii_d
        WHERE sii_d.parent = si.name
    ) AS "Description:Data:400",
    (
        SELECT IFNULL(GROUP_CONCAT(DISTINCT so_x.name ORDER BY so_x.transaction_date, so_x.name SEPARATOR ', '), '')
        FROM `tabSales Order` so_x
        WHERE so_x.company = si.company
          AND (
              EXISTS (
                  SELECT 1 FROM `tabSales Invoice Item` sii_px
                  WHERE sii_px.parent = si.name AND IFNULL(sii_px.project,'') != ''
                    AND so_x.project = sii_px.project
              )
              OR EXISTS (
                  SELECT 1 FROM `tabSales Order Item` soi_px
                  WHERE soi_px.parent = so_x.name AND IFNULL(soi_px.project,'') != ''
                    AND EXISTS (
                        SELECT 1 FROM `tabSales Invoice Item` sii_py
                        WHERE sii_py.parent = si.name AND IFNULL(sii_py.project,'') != ''
                          AND soi_px.project = sii_py.project
                    )
              )
          )
    ) AS "Sales Orders ID (via Project):Data:240",
    (
        SELECT IFNULL(GROUP_CONCAT(DISTINCT sii_pl.project ORDER BY sii_pl.project SEPARATOR ', '), '')
        FROM `tabSales Invoice Item` sii_pl
        WHERE sii_pl.parent = si.name AND IFNULL(sii_pl.project,'') != ''
    ) AS "Projects/JO (List):Data:220"
FROM `tabSales Invoice` si
LEFT JOIN `tabCustomer` cust ON cust.name = si.customer
WHERE si.docstatus = 1
  AND si.company = %(company)s
  AND si.posting_date BETWEEN %(from_date)s AND %(to_date)s
ORDER BY si.posting_date, si.name

---

Column Reference

Column	Source	Notes
Posting Date	tabSales Invoice.posting_date
Sales Invoice ID	tabSales Invoice.name	Link field
Customer Tax ID	tabCustomer.tax_id	Joined from Customer table
Customer	tabSales Invoice.customer
Invoice Total	tabSales Invoice.total	Pre-tax total
Grand Total	tabSales Invoice.grand_total	Post-tax total
VAT Amount	tabSales Taxes and Charges.tax_amount_after_discount_amount	Filtered by account head 2506 - VATOUTPUT TAXES PAYABLE - ESCO
Sales Orders ID (from Items)	tabSales Invoice Item.sales_order	Direct link on SI items
Sales Orders ID (via Project)	tabSales Order (subquery via project match)	More reliable; matches SO linked to same project as SI items
Payment ID	tabPayment Entry Reference.parent	Payment entries referencing this SI
Account Head	tabSales Taxes and Charges.account_head	VAT output account
Debit To	tabSales Invoice.debit_to	Accounts Receivable account
Against Income Account	tabSales Invoice Item.income_account	Distinct list from all SI items
Project	tabSales Invoice Item.project	First non-null project from items
Description	tabSales Invoice Item.description / item_name	Concatenated with semicolon separator
Projects/JO (List)	tabSales Invoice Item.project	Distinct list of all projects on the invoice

---

Notes

• The account head value 2506 - VATOUTPUT TAXES PAYABLE - ESCO must be updated if used for a different company.
• Sales Orders ID (via Project) is the preferred SO reference as it handles cases where the SO is not directly linked on the SI item.
• Report uses %(company)s, %(from_date)s, and %(to_date)s as ERPNext report filter parameters.