SALES INVOICE TAX OUTPUT 250829

must remove the SO ID via Items and move the SO ID via Project up

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

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