Hi ,
Finally i myself found the solution how to extract budget, cost, Actuals & Variance for current fiscal year and Total life cost.
This will be more helpful for who are all finding the budget & cost details from Financial plan.
Current Fiscal year :
SELECT
project_id,
'FY' || substr(to_char(fy_report_date),-2,2) CurFY,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS budget_capex_CurFY,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS budget_opex_CurFY,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 THEN cost_value ELSE 0 END) AS budget_CurFY_tot,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS cost_capex_CurFY,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS cost_opex_CurFY,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 THEN cost_value ELSE 0 END) AS cost_CurFY_tot,
SUM(CASE WHEN plan_type_code = 'ACTUAL' AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS actual_capex_CurFY,
SUM(CASE WHEN plan_type_code = 'ACTUAL' AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS actual_opex_CurFY,
SUM(CASE WHEN plan_type_code = 'ACTUAL' THEN cost_value ELSE 0 END) AS actual_CurFY_tot,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END)- SUM(CASE WHEN plan_type_code = 'ACTUAL' AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS forecast_capex_CurFY,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END)- SUM(CASE WHEN plan_type_code = 'ACTUAL' AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS forecast_opex_CurFY,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 THEN cost_value ELSE 0 END)-SUM(CASE WHEN plan_type_code = 'ACTUAL' THEN cost_value ELSE 0 END) AS Forecast_CurFY_tot,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END)- SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS variance_capex_CurFY,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END)- SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS variance_opex_CurFY,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 THEN cost_value ELSE 0 END)-SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 THEN cost_value ELSE 0 END) AS variance_CurFY_tot
FROM
(SELECT
fin.object_id AS project_id,
fin.id AS plan_id,
fin.plan_type_code AS plan_type_code,
fin.is_plan_of_record AS plan_of_record,
cst_typ.lookup_code AS plan_cost_type_code,
cst_typ.lookup_name AS plan_cost_type,
to_char(add_months(ssl.start_date,-3),'YYYY') AS Start_Year,
(select to_char(add_months(max(cop_report_date),-3),'YYYY') from odf_ca_cop_prj_statusrpt
where odf_parent_id=fin.object_id and cop_report_status='FINAL') AS fy_report_date,
round(ssl.slice * (ssl.finish_date - ssl.start_date) ) AS cost_value
FROM
fin_cost_plan_details fin_det
INNER JOIN fin_plans fin ON fin_det.plan_id = fin.id
INNER JOIN prchargecode cc on fin_det.charge_code_id = cc.prid and cc.prexternalid NOT IN ('Client')
INNER JOIN odf_ca_costplan occ ON occ.id = fin.id
left JOIN odf_ssl_cst_dtl_cost ssl ON fin_det.id = ssl.prj_object_id
LEFT JOIN (
SELECT
id,
lookup_code,
MAX(name) AS lookup_name
FROM
cmn_lookups_v
WHERE
lookup_type = 'LOOKUP_FIN_COSTTYPECODE'
AND language_code = 'en'
GROUP BY
id,
lookup_code
) cst_typ ON fin_det.cost_type_id = cst_typ.id
WHERE
fin.object_code = 'project'
AND (
(
fin.plan_type_code = 'BUDGET'
AND fin.status_code = 'APPROVED'
)
OR fin.plan_type_code = 'FORECAST'
)
AND z_delete_flag != 1
AND fin.is_plan_of_record=1
AND to_char(add_months(ssl.start_date,-3),'YYYY') = (select to_char(add_months(max(cop_report_date),-3),'YYYY') from odf_ca_cop_prj_statusrpt
where odf_parent_id=fin.object_id and cop_report_status='FINAL')
UNION ALL
SELECT
wip.investment_id AS project_id,
NULL AS plan_id,
'ACTUAL' AS plan_type_code,
1 AS plan_of_record,
cst_typ.lookup_code AS plan_cost_type_code,
cst_typ.lookup_name AS plan_cost_type,
NULL AS Start_Year,
(select to_char(add_months(max(cop_report_date),-3),'YYYY') from odf_ca_cop_prj_statusrpt
where odf_parent_id=wip.investment_id and cop_report_status='FINAL') AS fy_report_date,
wip_val.totalcost AS cost_value
FROM
ppa_wip wip
INNER JOIN prchargecode chg_cod ON wip.charge_code = chg_cod.prexternalid and chg_cod.prexternalid NOT IN ('Client')
LEFT JOIN ppa_wip_values wip_val ON wip.transno = wip_val.transno
LEFT JOIN ( SELECT id, lookup_code, MAX(Name) AS lookup_name
FROM cmn_lookups_v
WHERE lookup_type = 'LOOKUP_FIN_COSTTYPECODE'
AND language_code = 'en'
GROUP BY id, lookup_code) cst_typ
ON wip.cost_type = cst_typ.lookup_code
WHERE
wip_val.currency_type = 'HOME'
AND wip.status = 0
AND wip.sourcemodule IN (50,52, 53)
AND to_char(add_months(wip.TRANSDATE,-3),'YYYY') = (select to_char(add_months(max(cop_report_date),-3),'YYYY') from odf_ca_cop_prj_statusrpt
where odf_parent_id=wip.investment_id and cop_report_status='FINAL')
) dat
GROUP BY
project_id,fy_report_date
Total life time of the project:
SELECT
project_id,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_approved_revision = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS budget_original_capex,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_approved_revision = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS budget_original_opex,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_approved_revision = 1 THEN cost_value ELSE 0 END) AS budget_original,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS budget_revised_capex,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS budget_revised_opex,
SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 THEN cost_value ELSE 0 END) AS budget_revised,
SUM(CASE WHEN plan_type_code = 'ACTUAL' AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS actual_capex,
SUM(CASE WHEN plan_type_code = 'ACTUAL' AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS actual_opex,
SUM(CASE WHEN plan_type_code = 'ACTUAL' THEN cost_value ELSE 0 END) AS actual,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS landing_capex,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS landing_opex,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 THEN cost_value ELSE 0 END) AS landing,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END)- SUM(CASE WHEN plan_type_code = 'ACTUAL' AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS forecast_capex,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END)- SUM(CASE WHEN plan_type_code = 'ACTUAL' AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS forecast_opex,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 THEN cost_value ELSE 0 END)-SUM(CASE WHEN plan_type_code = 'ACTUAL' THEN cost_value ELSE 0 END) AS Forecast,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END)- SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 AND plan_cost_type_code = 'CAPITAL' THEN cost_value ELSE 0 END) AS variance_capex,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END)- SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 AND plan_cost_type_code = 'OPERATING' THEN cost_value ELSE 0 END) AS variance_opex,
SUM(CASE WHEN plan_type_code = 'FORECAST' AND plan_of_record = 1 THEN cost_value ELSE 0 END)-SUM(CASE WHEN plan_type_code = 'BUDGET' AND plan_of_record = 1 THEN cost_value ELSE 0 END) AS variance_tot
FROM
(SELECT
fin.object_id AS project_id,
fin.id AS plan_id,
fin.revision AS plan_revision,
fin.plan_type_code AS plan_type_code,
fin.is_plan_of_record AS plan_of_record,
cst_typ.lookup_code AS plan_cost_type_code,
cst_typ.lookup_name AS plan_cost_type,
DENSE_RANK() OVER (PARTITION BY fin.object_id, fin.plan_type_code ORDER BY fin.created_date) AS plan_approved_revision,
COUNT(DISTINCT fin.id) OVER (PARTITION BY fin.object_id, fin.plan_type_code) AS plan_count,
fin_det.total_cost AS cost_value
FROM
fin_cost_plan_details fin_det
INNER JOIN fin_plans fin ON fin_det.plan_id = fin.id
INNER JOIN prchargecode cc on fin_det.charge_code_id = cc.prid and cc.prexternalid NOT IN ('Client')
INNER JOIN odf_ca_costplan occ on occ.id =fin.id
LEFT JOIN ( SELECT id, lookup_code, MAX(Name) AS lookup_name
FROM cmn_lookups_v
WHERE lookup_type = 'LOOKUP_FIN_COSTTYPECODE'
AND language_code = 'en'
GROUP BY id, lookup_code) cst_typ
ON fin_det.cost_type_id = cst_typ.id
WHERE
fin.object_code = 'project'
AND ( (fin.plan_type_code = 'BUDGET' AND fin.status_code = 'APPROVED')
OR fin.plan_type_code = 'FORECAST')
and z_delete_flag!=1
UNION ALL
SELECT
wip.investment_id AS project_id,
NULL AS plan_id,
NULL AS plan_revision,
'ACTUAL' AS plan_type_code,
1 AS plan_of_record,
cst_typ.lookup_code AS plan_cost_type_code,
cst_typ.lookup_name AS plan_cost_type,
1 AS plan_approved_revision,
1 AS plan_count,
wip_val.totalcost AS cost_value
FROM
ppa_wip wip
INNER JOIN prchargecode chg_cod ON wip.charge_code = chg_cod.prexternalid and chg_cod.prexternalid NOT IN ('Client')
LEFT JOIN ppa_wip_values wip_val ON wip.transno = wip_val.transno
LEFT JOIN ( SELECT id, lookup_code, MAX(Name) AS lookup_name
FROM cmn_lookups_v
WHERE lookup_type = 'LOOKUP_FIN_COSTTYPECODE'
AND language_code = 'en'
GROUP BY id, lookup_code) cst_typ
ON wip.cost_type = cst_typ.lookup_code
WHERE
wip_val.currency_type = 'HOME'
AND wip.status = 0
AND wip.sourcemodule IN (50,52, 53)) dat
GROUP BY
project_id
Note : please let me know in case if you're optimizing the above query.
Regards,
Karthick