Working in BI Publisher, I am working on a query to pull the necessary data to then build an rtf template with for a YTD Budget Vs Actuals report. Pulling YTD actuals for the necessary Cost Center Managers, time period and department has worked for the most part, the YTD budget values are a more complex problem.
In terms of how we want the report structured, we want the individual cost center managers to access the report in the cloud, select the date they are interested in, select their own name and their department. The resulting output should only show data relative to this selection. For the most part my query handles this well up to the point where I am trying to incorporate YTD Budget data.
Unlike the YTD Actuals associated to the specific time period, cost center manager and department, the YTD Budget values are not stored in the data as a running total, but rather the budgeted amount for that specific month. I believe I need to create some logic to sum the budget values for each month in that fiscal year. Our fiscal year runs April - March. Should I be handling this in the SQL, or should I pull the full data set (which the query currently does) and handle the complexity of the budget data in the template via xml?
Appreciate any guidance and improvements to the query!
The query I have built at the moment is as follows:
SELECT DISTINCT
glb.period_name,
TO_CHAR(
LAST_DAY(
TO_DATE(
'01-' ||
CASE
WHEN SUBSTR(glb.period_name, 1, 3) = 'Jan' THEN '01'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Feb' THEN '02'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Mar' THEN '03'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Apr' THEN '04'
WHEN SUBSTR(glb.period_name, 1, 3) = 'May' THEN '05'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Jun' THEN '06'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Jul' THEN '07'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Aug' THEN '08'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Sep' THEN '09'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Oct' THEN '10'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Nov' THEN '11'
WHEN SUBSTR(glb.period_name, 1, 3) = 'Dec' THEN '12'
END || '-20' || SUBSTR(glb.period_name, 5, 2),
'DD-MM-YYYY'
)
), 'YYYY-MM-DD'
) AS end_of_month_date,
gcc.segment2 || '-' || gcc.segment3 AS concatenated_segments,
gcc.segment1,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 1, gcc.segment1) AS description1,
gcc.segment2,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2) AS description2,
gcc.segment3,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 3, gcc.segment3) AS description3,
CASE
SUBSTR(seg_primary_val.compiled_value_attributes, 7, 1)
WHEN 'E' THEN 'Expense'
WHEN 'A' THEN 'Asset'
WHEN 'O' THEN 'Owners Equity'
WHEN 'L' THEN 'Liability'
WHEN 'R' THEN 'Revenue'
ELSE SUBSTR(seg_primary_val.compiled_value_attributes, 7, 1)
END AS account_type,
-- YTD Actuals (no aggregation needed)
ABS(
CASE
WHEN SUBSTR(seg_primary_val.compiled_value_attributes, 7, 1) = 'R' THEN
(glb.begin_balance_cr + glb.period_net_cr_beq - glb.begin_balance_dr - glb.period_net_dr_beq)
WHEN SUBSTR(seg_primary_val.compiled_value_attributes, 7, 1) = 'E' THEN
(glb.begin_balance_dr + glb.period_net_dr_beq - glb.begin_balance_cr - glb.period_net_cr_beq)
ELSE 0
END
) AS ytd_actuals,
-- Budget Data
NVL(budget.period_net_dr, 0) AS budget_period_net_dr,
NVL(budget.period_net_cr, 0) AS budget_period_net_cr
FROM
gl_balances glb
JOIN
gl_code_combinations gcc
ON glb.code_combination_id = gcc.code_combination_id
LEFT JOIN
gl_budget_balances budget
ON glb.ledger_id = budget.ledger_id
AND gcc.segment1 = budget.segment1
AND gcc.segment2 = budget.segment2
AND gcc.segment3 = budget.segment3
AND glb.period_name = budget.period_name
LEFT JOIN
hr_organization_information hoi
ON hoi.org_information1 = gcc.segment2
LEFT JOIN
per_person_names_f ppf
ON hoi.org_information6 = ppf.person_id
LEFT JOIN
fnd_flex_values seg_primary_val
ON gcc.segment3 = seg_primary_val.flex_value
LEFT JOIN
fnd_flex_value_sets seg_primary_set
ON seg_primary_val.flex_value_set_id = seg_primary_set.flex_value_set_id
AND seg_primary_set.flex_value_set_name = 'WH_SECONDARY'
WHERE
(:P_COST_CENTER_MGR IS NULL OR ppf.person_id = :P_COST_CENTER_MGR)
AND REGEXP_LIKE(gcc.segment3, '[A-Z]')
AND (:P_DEPARTMENT IS NULL OR gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2) = :P_DEPARTMENT)
ORDER BY
concatenated_segments