Oracle AI and Analytics

Ram ANeni

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

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)