BabiSender: Created page with "= 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''' * '''Custo..."

2026-02-25T06:54:14Z

Created page with "= 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''' * '''Custo..."

New page

= 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) ===

<pre>
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
</pre>

----

=== 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.

<pre>
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
</pre>

----

== Column Reference ==

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

----

== Notes ==

* The '''account head''' value <code>2506 - VATOUTPUT TAXES PAYABLE - ESCO</code> 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 <code>%(company)s</code>, <code>%(from_date)s</code>, and <code>%(to_date)s</code> as ERPNext report filter parameters.

SALES INVOICE TAX OUTPUT 250829 - Revision history