BabiSender: Created page with "= 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..."

2026-02-25T06:53:50Z

Created page with "= 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..."

New page

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

Projects in Process Report 251023 - Revision history