View source for 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.

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

----

=== 250828 – Added Sales Order Link + Items List ===
Added Sales Order name column and Sales Order Items list (plain text, no brackets).

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

----

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

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

----

=== 250901 – Added Timesheets Costing Amount ===
Timesheet costing amount column enabled. Stock Value columns still present at this stage.

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

----

=== 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 <code>%Stores%</code>).
* Added '''Project MRS''' (Stock Entry returns where Target Warehouse LIKE <code>%Stores%</code>).
* Added '''Remainder''' column: SI Items − PI − MR − Timesheets.
* Replaced Earliest SO Date with '''Estimated Start Date''' (<code>p.expected_start_date</code>).

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

----

== Column Reference ==

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