Projects in Process Report 251023

Projects in Process Report

This page documents the development history and SQL query versions for the Projects in Process custom report in ERPNext. Queries are listed from earliest to latest revision.

---

Report Columns

The final report includes the following columns:

• PROJECT ID – Link to Project
• Department – Link to Department
• Customer – Link to Customer
• Earliest SO – Link to Sales Order
• Estimated Start Date (or Earliest SO Date in earlier versions)
• Total SI Items (Base, by Project) – Currency
• Total Purchase Invoices (Grand Total) – Currency
• Project MR (from Stores) – Currency (Stock Entry withdrawals from Stores)
• Project MRS (to Stores) – Currency (Stock Entry returns to Stores)
• Timesheets Costing Amount (by Project) – Currency
• Remainder (SI - PI - MR - TS) – Currency
• Sales Order Items (List) – Data

---

Query Version History

250828 – Initial Working Version

Basic report with Project ID, Department, Customer, Sales Order Date. No Timesheets or MR/MRS columns yet.

SELECT
    proj.name AS "PROJECT ID:Link/Project:120",
    proj.department AS "Department:Link/Department:150",
    proj.customer AS "Customer:Link/Customer:200",
    (
        SELECT MIN(so.transaction_date)
        FROM `tabSales Order` so
        WHERE so.company = proj.company
          AND (
              so.project = proj.name
              OR EXISTS (
                  SELECT 1 FROM `tabSales Order Item` soi
                  WHERE soi.parent = so.name AND soi.project = proj.name
              )
          )
    ) AS "Sales Order Date:Date:120"
FROM `tabProject` proj
WHERE proj.company = %(company)s
ORDER BY proj.name

---

250828 – Added Sales Order Link + Items List

Added Sales Order name column and Sales Order Items list (plain text, no brackets).

SELECT
    proj.name AS "PROJECT ID:Link/Project:120",
    proj.department AS "Department:Link/Department:150",
    proj.customer AS "Customer:Link/Customer:200",
    (
        SELECT so.name
        FROM `tabSales Order` so
        WHERE so.company = proj.company
          AND (
              so.project = proj.name
              OR EXISTS (
                  SELECT 1 FROM `tabSales Order Item` soi
                  WHERE soi.parent = so.name AND soi.project = proj.name
              )
          )
        ORDER BY so.transaction_date ASC, so.name ASC
        LIMIT 1
    ) AS "Sales Order:Link/Sales Order:180",
    (
        SELECT MIN(so.transaction_date)
        FROM `tabSales Order` so
        WHERE so.company = proj.company
          AND (
              so.project = proj.name
              OR EXISTS (
                  SELECT 1 FROM `tabSales Order Item` soi
                  WHERE soi.parent = so.name AND soi.project = proj.name
              )
          )
    ) AS "Sales Order Date:Date:120",
    (
        SELECT IFNULL(
            GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '),
            ''
        )
        FROM `tabSales Order Item` soi
        WHERE soi.parent = (
            SELECT so.name
            FROM `tabSales Order` so
            WHERE so.company = proj.company
              AND (
                  so.project = proj.name
                  OR EXISTS (
                      SELECT 1 FROM `tabSales Order Item` soi2
                      WHERE soi2.parent = so.name AND soi2.project = proj.name
                  )
              )
            ORDER BY so.transaction_date ASC, so.name ASC
            LIMIT 1
        )
    ) AS "Sales Order Items (List):Data:360"
FROM `tabProject` proj
WHERE proj.company = %(company)s
ORDER BY proj.name

---

250828 – Added Purchase Invoices + Project Stock Value

Added Total Purchase Invoices and Project Stock Value (current + as-of date) using Bin and Stock Ledger Entry.

WITH
earliest_so AS (
    SELECT 
        p.name AS project,
        (
            SELECT so2.name
            FROM `tabSales Order` so2
            WHERE so2.company = p.company
              AND (so2.project = p.name OR EXISTS (
                  SELECT 1 FROM `tabSales Order Item` soi2
                  WHERE soi2.parent = so2.name AND soi2.project = p.name))
            ORDER BY so2.transaction_date ASC, so2.name ASC LIMIT 1
        ) AS so_name,
        (
            SELECT MIN(so3.transaction_date)
            FROM `tabSales Order` so3
            WHERE so3.company = p.company
              AND (so3.project = p.name OR EXISTS (
                  SELECT 1 FROM `tabSales Order Item` soi3
                  WHERE soi3.parent = so3.name AND soi3.project = p.name))
        ) AS so_date
    FROM `tabProject` p WHERE p.company = %(company)s
),
pi_totals AS (
    SELECT pii.project AS project, SUM(DISTINCT pi.grand_total) AS pi_total
    FROM `tabPurchase Invoice` pi
    JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
    WHERE pi.docstatus = 1
    GROUP BY pii.project
),
proj_wh AS (
    SELECT p.name AS project, p.company, w.name AS wh
    FROM `tabProject` p
    JOIN `tabWarehouse` w ON w.company = p.company AND w.`custom_project` = p.name
    WHERE p.company = %(company)s
),
bin_val AS (
    SELECT pw.project, SUM(b.actual_qty * IFNULL(b.valuation_rate, 0)) AS val_now
    FROM proj_wh pw
    JOIN `tabBin` b ON b.warehouse = pw.wh
    GROUP BY pw.project
),
asof_val AS (
    SELECT pw.project, SUM(sle.stock_value) AS val_asof
    FROM proj_wh pw
    JOIN (
        SELECT x.item_code, x.warehouse, IFNULL(x.batch_no, '') AS batch_no,
               MAX(CONCAT(x.posting_date, ' ', x.posting_time, ' ', x.name)) AS maxkey
        FROM `tabStock Ledger Entry` x
        WHERE x.is_cancelled = 0 AND x.posting_date <= %(to_date)s
        GROUP BY x.item_code, x.warehouse, IFNULL(x.batch_no, '')
    ) last ON last.warehouse = pw.wh
    JOIN `tabStock Ledger Entry` sle
      ON sle.item_code = last.item_code AND sle.warehouse = last.warehouse
     AND IFNULL(sle.batch_no, '') = last.batch_no
     AND CONCAT(sle.posting_date, ' ', sle.posting_time, ' ', sle.name) = last.maxkey
    GROUP BY pw.project
)
SELECT
    p.name AS "PROJECT ID:Link/Project:120",
    p.department AS "Department:Link/Department:150",
    p.customer AS "Customer:Link/Customer:200",
    e.so_name AS "Sales Order:Link/Sales Order:160",
    e.so_date AS "Sales Order Date:Date:110",
    (
        SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
        FROM `tabSales Order Item` soi WHERE soi.parent = e.so_name
    ) AS "Sales Order Items (List):Data:300",
    COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:160",
    COALESCE(bv.val_now, 0) AS "Project Stock Value (Current):Currency:160",
    COALESCE(av.val_asof, 0) AS "Project Stock Value as of To Date:Currency:170"
FROM `tabProject` p
LEFT JOIN earliest_so e ON e.project = p.name
LEFT JOIN pi_totals pt  ON pt.project = p.name
LEFT JOIN bin_val bv    ON bv.project = p.name
LEFT JOIN asof_val av   ON av.project = p.name
WHERE p.company = %(company)s
ORDER BY p.name

---

250901 – Added Timesheets Costing Amount

Timesheet costing amount column enabled. Stock Value columns still present at this stage.

SELECT
    p.name AS "PROJECT ID:Link/Project:140",
    p.department AS "Department:Link/Department:150",
    p.customer AS "Customer:Link/Customer:200",
    e.so_name AS "Earliest SO:Link/Sales Order:160",
    e.so_date AS "Earliest SO Date:Date:110",
    COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
    COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
    COALESCE(av.val_asof, 0) AS "Project Stock Value (As of Last Movement):Currency:180",
    COALESCE(ts.ts_costing_amount, 0) AS "Timesheets Costing Amount (by Project):Currency:180",
    (
        SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
        FROM `tabSales Order Item` soi WHERE soi.parent = e.so_name
    ) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
/* ... (same LEFT JOINs as above) ... */
WHERE p.company = %(company)s
  AND ( %(project_like)s = '' OR p.name LIKE %(project_like)s )
ORDER BY p.name

---

250901-01 – Replaced Stock Value with MR/MRS + Remainder Column

Key changes:

• Removed Project Stock Value column.
• Added Project MR (Stock Entry withdrawals where Source Warehouse LIKE %Stores%).
• Added Project MRS (Stock Entry returns where Target Warehouse LIKE %Stores%).
• Added Remainder column: SI Items − PI − MR − Timesheets.
• Replaced Earliest SO Date with Estimated Start Date (p.expected_start_date).

SELECT
    p.name AS "PROJECT ID:Link/Project:140",
    p.department AS "Department:Link/Department:150",
    p.customer AS "Customer:Link/Customer:200",
    e.so_name AS "Earliest SO:Link/Sales Order:160",
    p.expected_start_date AS "Estimated Start Date:Date:110",
    COALESCE(si.si_items_total_base, 0) AS "Total SI Items (Base, by Project):Currency:180",
    COALESCE(pt.pi_total, 0) AS "Total Purchase Invoices (Grand Total):Currency:170",
    COALESCE(mr.mr_amount, 0) AS "Project MR (from Stores):Currency:160",
    COALESCE(mrs.mrs_amount, 0) AS "Project MRS (to Stores):Currency:160",
    COALESCE(ts.ts_costing_amount, 0) AS "Timesheets Costing Amount (by Project):Currency:180",
    (
        COALESCE(si.si_items_total_base, 0)
      - (COALESCE(pt.pi_total, 0) + COALESCE(mr.mr_amount, 0) + COALESCE(ts.ts_costing_amount, 0))
    ) AS "Remainder (SI - PI - MR - TS):Currency:160",
    (
        SELECT IFNULL(GROUP_CONCAT(IFNULL(soi.item_name, soi.item_code) ORDER BY soi.idx SEPARATOR ', '), '')
        FROM `tabSales Order Item` soi WHERE soi.parent = e.so_name
    ) AS "Sales Order Items (List):Data:300"
FROM `tabProject` p
LEFT JOIN (
    SELECT p2.name AS project,
        (SELECT so2.name FROM `tabSales Order` so2
         WHERE so2.company = p2.company
           AND (so2.project = p2.name OR EXISTS (
               SELECT 1 FROM `tabSales Order Item` soi2
               WHERE soi2.parent = so2.name AND soi2.project = p2.name))
         ORDER BY so2.transaction_date ASC, so2.name ASC LIMIT 1) AS so_name
    FROM `tabProject` p2
    WHERE p2.company = %(company)s
      AND ( %(project_like)s = '' OR p2.name LIKE %(project_like)s )
) e ON e.project = p.name
LEFT JOIN (
    SELECT t.project, SUM(t.grand_total) AS pi_total
    FROM (SELECT DISTINCT pi.name, pii.project, pi.grand_total
          FROM `tabPurchase Invoice` pi
          JOIN `tabPurchase Invoice Item` pii ON pi.name = pii.parent
          WHERE pi.docstatus = 1 AND pi.company = %(company)s
            AND pii.project IS NOT NULL AND pii.project != ''
            AND ( %(project_like)s = '' OR pii.project LIKE %(project_like)s )) t
    GROUP BY t.project
) pt ON pt.project = p.name
LEFT JOIN (
    SELECT COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>') AS project,
           SUM(sii.base_net_amount) AS si_items_total_base
    FROM `tabSales Invoice` si
    JOIN `tabSales Invoice Item` sii ON sii.parent = si.name
    WHERE si.docstatus = 1 AND si.company = %(company)s
      AND ( %(project_like)s = '' OR sii.project LIKE %(project_like)s )
    GROUP BY COALESCE(NULLIF(sii.project, ''), '<<NO PROJECT>>')
) si ON si.project = p.name
LEFT JOIN (
    SELECT COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>') AS project,
           SUM(tsd.costing_amount) AS ts_costing_amount
    FROM `tabTimesheet Detail` tsd
    JOIN `tabTimesheet` ts ON ts.name = tsd.parent
    WHERE ts.docstatus = 1 AND ts.company = %(company)s
      AND ( %(project_like)s = '' OR tsd.project LIKE %(project_like)s )
    GROUP BY COALESCE(NULLIF(tsd.project, ''), '<<NO PROJECT>>')
) ts ON ts.project = p.name
LEFT JOIN (
    SELECT COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
           SUM(IFNULL(sed.basic_amount, 0)) AS mr_amount
    FROM `tabStock Entry` se
    JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
    WHERE se.docstatus = 1 AND se.company = %(company)s
      AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
      AND IFNULL(sed.s_warehouse, '') LIKE '%%Stores%%'
    GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mr ON mr.project = p.name
LEFT JOIN (
    SELECT COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>') AS project,
           SUM(IFNULL(sed.basic_amount, 0)) AS mrs_amount
    FROM `tabStock Entry` se
    JOIN `tabStock Entry Detail` sed ON sed.parent = se.name
    WHERE se.docstatus = 1 AND se.company = %(company)s
      AND ( %(project_like)s = '' OR COALESCE(sed.project, se.project) LIKE %(project_like)s )
      AND IFNULL(sed.t_warehouse, '') LIKE '%%Stores%%'
    GROUP BY COALESCE(NULLIF(COALESCE(sed.project, se.project), ''), '<<NO PROJECT>>')
) mrs ON mrs.project = p.name
WHERE p.company = %(company)s
  AND ( %(project_like)s = '' OR p.name LIKE %(project_like)s )
ORDER BY p.name

---

Column Reference

Column	Source	Notes
Project ID	tabProject.name	Link field
Department	tabProject.department	Link field
Customer	tabProject.customer	Link field
Earliest SO	tabSales Order (subquery)	Matches SO header OR SO Item project link
Estimated Start Date	tabProject.expected_start_date	Replaced SO Date in 250901-01
Total SI Items (Base)	tabSales Invoice Item.base_net_amount	Submitted SIs only
Total Purchase Invoices	tabPurchase Invoice.grand_total	Submitted PIs only; DISTINCT to avoid double-count
Project MR (from Stores)	tabStock Entry Detail.basic_amount	Source Warehouse LIKE %Stores%
Project MRS (to Stores)	tabStock Entry Detail.basic_amount	Target Warehouse LIKE %Stores%
Timesheets Costing Amount	tabTimesheet Detail.costing_amount	Submitted timesheets only
Remainder	SI − PI − MR − TS	Calculated column
Sales Order Items (List)	tabSales Order Item.item_name	GROUP_CONCAT, comma-separated, ordered by idx