DivyaMurali Absent Member.
Absent Member.
425 views

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

0 Likes
2 Replies
DivyaMurali Absent Member.
Absent Member.

Re: Financial Summary in Program

Please respond if anyone has any idea on this

0 Likes
Absent Member.. HailieFan Absent Member..
Absent 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'

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.