View source for 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) ===

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