ERP Analytics - Projects Ending Balances

Question

Hi Team - Good Afternoon! Is there a way or subject area to get the project beginning balances and ending balances for a given month ? Thanks in advance.

RVohra · Answer

Not sure with OTBI.

Might be possible with BIP DM Query.

SELECT

hp.project_number,

hp.name AS project_name,

pt.task_number,

pt.name AS task_name,

-- Beginning balance: before start date

SUM(CASE

WHEN pei.expenditure_item_date < TO_DATE(:p_start_date, 'YYYY-MM-DD')

THEN pei.raw_cost_amount

ELSE 0

END) AS beginning_balance,

-- Ending balance: up to end date

SUM(CASE

WHEN pei.expenditure_item_date <= TO_DATE(:p_end_date, 'YYYY-MM-DD')

THEN pei.raw_cost_amount

ELSE 0

END) AS ending_balance

FROM

pjc_expenditure_items_all pei

JOIN pjc_projects_all_b hp

ON pei.project_id = hp.project_id

JOIN pjc_tasks pt

ON pei.project_id = pt.project_id

AND pei.task_id = pt.task_id

WHERE

pei.expenditure_item_date <= TO_DATE(:p_end_date, 'YYYY-MM-DD')

AND pei.project_status_code = 'APPROVED'  -- optional filter

AND pei.raw_cost_amount IS NOT NULL       -- safety filter

GROUP BY

hp.project_number,

hp.name,

pt.task_number,

pt.name

ORDER BY

hp.project_number,

pt.task_number;

Two parameters:

* :p_start_date = beginning of month (e.g., 01-OCT-2025)

* :p_end_date = end of month (e.g., 31-OCT-2025)