Project and Portfolio Management Practitioners Forum
cancel

Financial Summary in Program

Highlighted
DivyaMurali
Trusted Contributor.

Financial Summary in Program

Hi all,

 

We have a requirement to get the financial details that are entered in Programs (excluding the data that is rolled up from content Projects).

 

 

fm_financial_line_cells gives the financial details as given in Program Financial summary. Can anyone please let me know whether I will be able to get only the financial details that have been entered in Program's financial summary directly, omitting the ones from content Projects.

 

Appreciate your help!

 

Regards,

Divya

2 REPLIES
DivyaMurali
Trusted Contributor.

Re: Financial Summary in Program

Please respond if anyone has any idea on this

HailieFan
New Member.

Re: Financial Summary in Program

Hi,

 

please refer to below queries:

 

select
ffs.financial_summary_id FM_ID,
fp.long_name FISCAL_YEAR,
NVL((select sum(fflc.PLAN_VALUE_LCL) from FM_FINANCIAL_LINE_CELLS fflc, FM_FORECAST_ACTUAL_LINES ffal where fflc.PARENT_TYPE_CODE = 'FORECAST' and fflc.PARENT_ID = ffal.FORECAST_ACTUAL_LINE_ID and ffal.forecast_actual_id = ffs.forecast_actual_id and ffal.SYNC_SOURCE_FLAG = 'N'), 0) tot_FORECAST,
NVL((select sum(fflc.ACTUAL_VALUE_LCL) from FM_FINANCIAL_LINE_CELLS fflc,FM_FORECAST_ACTUAL_LINES ffal where fflc.PARENT_TYPE_CODE = 'FORECAST' and fflc.PARENT_ID = ffal.FORECAST_ACTUAL_LINE_ID and ffal.forecast_actual_id = ffs.forecast_actual_id and ffal.SYNC_SOURCE_FLAG = 'N'), 0) tot_acTUAL,
NVL((select sum(AMOUNT_LCL) from fm_approved_budgets where financial_summary_id=ffs.financial_summary_id and SYNCH_SOURCE_FLAG is null ),0) tot_approve_budget,
NVL((select sum(AMOUNT_LCL) from fm_approved_budgets where financial_summary_id=ffs.financial_summary_id and SYNCH_SOURCE_FLAG is null and fiscal_period_id = fp.fiscal_period_id),0) APPROVED_BUDGET,
NVL((select sum(fflc.PLAN_VALUE_LCL) from FM_FINANCIAL_LINE_CELLS fflc, FM_FORECAST_ACTUAL_LINES ffal where fflc.PARENT_TYPE_CODE = 'FORECAST' and fflc.PARENT_ID = ffal.FORECAST_ACTUAL_LINE_ID and ffal.forecast_actual_id = ffs.forecast_actual_id and ffal.SYNC_SOURCE_FLAG = 'N' and (select fiscal_period_id from ppm_fiscal_periods where level='4' start with fiscal_period_id = fflc.period_id connect by fiscal_period_id = prior parent_period_id) = fp.fiscal_period_id), 0) prog_FORECAST,
NVL((select sum(fflc.ACTUAL_VALUE_LCL) from FM_FINANCIAL_LINE_CELLS fflc,FM_FORECAST_ACTUAL_LINES ffal where fflc.PARENT_TYPE_CODE = 'FORECAST' and fflc.PARENT_ID = ffal.FORECAST_ACTUAL_LINE_ID and ffal.forecast_actual_id = ffs.forecast_actual_id and ffal.SYNC_SOURCE_FLAG = 'N' and (select fiscal_period_id from ppm_fiscal_periods where level='4' start with fiscal_period_id = fflc.period_id connect by fiscal_period_id = prior parent_period_id) = fp.fiscal_period_id), 0) prog_ACTUAL,
NVL((select sum(AMOUNT_LCL) from fm_approved_budgets where financial_summary_id=ffs.financial_summary_id and SYNCH_SOURCE_FLAG is null and fiscal_period_id = fp.fiscal_period_id),0) - NVL((select sum(fflc.PLAN_VALUE_LCL) from FM_FINANCIAL_LINE_CELLS fflc, FM_FORECAST_ACTUAL_LINES ffal where fflc.PARENT_TYPE_CODE = 'FORECAST' and fflc.PARENT_ID = ffal.FORECAST_ACTUAL_LINE_ID and ffal.forecast_actual_id = ffs.forecast_actual_id and ffal.SYNC_SOURCE_FLAG = 'N' and (select fiscal_period_id from ppm_fiscal_periods where level='4' start with fiscal_period_id = fflc.period_id connect by fiscal_period_id = prior parent_period_id) = fp.fiscal_period_id), 0) FIN_VARIENCE
from
FM_FINANCIAL_SUMMARY ffs,
PPM_FISCAL_PERIODS fp
where 1 =1
and ffs.financial_summary_id=32465
and fp.long_name like '2012'