Universal Report againt PO & Pur. Invo Billing Report, remove workflow line if not create against po

ERPNext Accounting
1

SELECT
po.name AS purchase_order,
po.supplier AS supplier,
s.supplier_type AS supplier_type,
s.supplier_group AS supplier_group,
po.project AS project,
po.net_total AS net_total,
po.grand_total AS po_grand_total,
po.workflow_state AS workflow_state,
po.status AS status,
po.docstatus AS docstatus,
po.creation AS po_creation_date, – Purchase Order Creation Date
po.currency AS po_currency, – Purchase Order Currency
po.per_billed AS po_per_billed, – per_billed field from Purchase Order
– Calculate Invoice Grand Total by summing distinct invoice totals with docstatus=1
COALESCE(SUM(DISTINCT pi.grand_total), 0) AS invoice_grand_total,
pi.creation AS pi_creation_date, – Purchase Invoice Creation Date
pi.currency AS pi_currency, – Purchase Invoice Currency
– Calculate Billed Amount Percent
COALESCE(ROUND(
(SUM(DISTINCT pi.grand_total) / po.grand_total) * 100, 2
), 0) AS billed_amount_percent,
– Calculate PO Remaining Balance
COALESCE((po.grand_total - SUM(DISTINCT pi.grand_total)), 0) AS po_remaining_balance

FROM
tabPurchase Order po

LEFT JOIN
tabPurchase Invoice Item pii ON po.name = pii.purchase_order AND pii.docstatus = 1

LEFT JOIN
tabPurchase Invoice pi ON pii.parent = pi.name AND pi.docstatus = 1

LEFT JOIN
tabSupplier s ON po.supplier = s.name

GROUP BY
po.name

ORDER BY
po.creation DESC; – Order by latest Purchase Order creation date

2

Please check it and set the scenario.

SELECT
    po.name AS purchase_order,
    po.supplier AS supplier,
    s.supplier_type AS supplier_type,
    s.supplier_group AS supplier_group,
    po.project AS project,
    po.net_total AS net_total,
    po.grand_total AS po_grand_total,
    CASE
        WHEN po.per_billed > 0 THEN po.workflow_state
        ELSE NULL
    END AS workflow_state,
    po.status AS status,
    po.docstatus AS docstatus,
    po.creation AS po_creation_date,
    po.currency AS po_currency,
    po.per_billed AS po_per_billed,
    COALESCE(SUM(DISTINCT pi.grand_total), 0) AS invoice_grand_total,
    pi.creation AS pi_creation_date,
    pi.currency AS pi_currency,
    COALESCE(ROUND(
        (SUM(DISTINCT pi.grand_total) / po.grand_total) * 100, 2
    ), 0) AS billed_amount_percent,
    COALESCE((po.grand_total - SUM(DISTINCT pi.grand_total)), 0) AS po_remaining_balance

FROM
    `tabPurchase Order` po

LEFT JOIN
    `tabPurchase Invoice Item` pii ON po.name = pii.purchase_order AND pii.docstatus = 1

LEFT JOIN
    `tabPurchase Invoice` pi ON pii.parent = pi.name AND pi.docstatus = 1

LEFT JOIN
    `tabSupplier` s ON po.supplier = s.name

GROUP BY
    po.name

ORDER BY
    po.creation DESC;
1 Like