1 PACKAGE BODY fii_ea_cumul_exp_trend_pkg AS
2 /* $Header: FIIEACETB.pls 120.3 2006/05/25 10:15:23 hpoddar noship $ */
3
4 PROCEDURE get_cumul_exp_trend
5
6 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
7 ,p_cumulative_expense_sql OUT NOCOPY VARCHAR2
8 ,p_cumulative_expense_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 ) IS
10
11
12 /* Local/Bind Variables used in the code
13
14 CURR_PERIOD_START = bind variable for start date of current period
15 CURR_PERIOD_END = bind variable for end date of current period
16 PRIOR_PERIOD_START = bind variable for start date of prior period
17 PRIOR_PERIOD_END = bind variable for end date of prior period
18 CURR_MONTH_START = bind variable for start date of the current month for the period type Year
19
20 l_budget_time_id = Local variable to hold the column name (used while calculating budget),
21 chosen from either of these tables: fii_time_ent_period , fii_time_ent_qtr
22 or fii_time_ent_year
23
24 l_budget_table_name = Local variable to hold the table name to be used for Budget calcualtions
25 l_budget_time_unit = Local variable to check the level at which budget is uploaded
26
27 l_forecast_time_id = Local variable to hold the column name (used while calculating forecast),
28 chosen from either of these tables: fii_time_ent_period , fii_time_ent_qtr
29 or fii_time_ent_year
30
31 l_forecast_table_name = Local variable to hold the table name to be used for Forecast calcualtions.
32 l_forecast_time_unit = Local variable to check the level at which forecast is uploaded
33 l_display_adjustment = Local variable to hold string that will calculate value so as to decide the number of days for which
34 data is to be shown as NULL
35
36 l_current_adjustment_days = '1' if period type is month;
37 = Difference between :CURR_PERIOD_START and :CURR_PERIOD_END for period type = Quarter
38
39 l_prior_adjustment_days = '1' if period type is month;
40 = Difference between :PRIOR_PERIOD_START and :PRIOR_PERIOD_END for period type = Quarter
41
42 l_current_adjustment_days and l_prior_adjustment_days variables are used to calculate the display label for period type quarter
43 (-89 to 0, assuming that there are 90 rows displayed)
44 l_company_security = Local variable that holds all the possible company-ids for which user has access
45 l_cost_center_security = Local variable that holds all the possible cost-center-ids for which user has access
46
47 */
48
49 -- Local variables declaration
50
51 -- Variables used to find the level at which Budget/Forecast is loaded
52
53 l_budget_time_unit VARCHAR2(1);
54 l_forecast_time_unit VARCHAR2(1);
55 l_display_adjustment VARCHAR2(1000);
56
57 -- Variables to hold different dynamic SQL statements
58
59 -- Variable to hold SQL statement involving Budget calculations, for Period Type = Year
60
61 l_sql_budget_ver_year VARCHAR2(5000) := NULL;
62
63 -- Variable to hold SQL statement involving Forecast calculations, for Period Type = Year
64
65 l_sql_forecast_ver_year VARCHAR2(5000) := NULL;
66
67 -- Variable to hold SQL statement involving Budget calculations, for Period Type = Month/Qtr
68
69 l_sql_budget_ver_month_qtr VARCHAR2(5000) := NULL;
70
71 -- Variable to hold SQL statement involving Forecast calculations, for Period Type = Month/Qtr
72
73 l_sql_forecast_ver_month_qtr VARCHAR2(5000) := NULL;
74
75 -- This will hold the actual SQL statement which will be passed to PMV via bind_variable procedure
76
77 l_actual_sql_statement VARCHAR2(10000) := NULL;
78
79 -- This will hold SQL statement for Budget/Forecast profile option = Y,
80 -- Period Type = Year, Compare To = Prior Year/Prior Period/Budget/Forecast
81
82 l_sql_statement1 VARCHAR2(10000) := NULL;
83
84
85 -- This will hold SQL statement for Budget/Forecast profile option = Y,
86 -- Period Type = Month/Quarter, Compare To = Prior Year/Prior Period/Budget/Forecast
87
88 l_sql_statement2 VARCHAR2(10000) := NULL;
89
90 -- This will hold SQL statement for Budget/Forecast profile option = N,
91 -- Period Type = Year, Compare To = Prior Year/Prior Period/Budget/Forecast
92
93 l_sql_statement3 VARCHAR2(10000) := NULL;
94
95 -- This will hold SQL statement for Budget/Forecast profile option = N,
96 -- Period Type = Month/Quarter, Compare To = Prior Year/Prior Period/Budget/Forecast
97
98 l_sql_statement4 VARCHAR2(10000) := NULL;
99
100 -- Flag used to check the profile option FII_FB_STEP, if set to 'Y' or 'N'
101
102 l_budget_forecast_profile VARCHAR2(1);
103
104 -- Budget/Forecast related variables
105 l_budget_time_id VARCHAR2(30);
106 l_forecast_time_id VARCHAR2(30);
107 l_budget_table_name VARCHAR2(30);
108 l_forecast_table_name VARCHAR2(30);
109
110 -- Variables related to Period Type = Month/Quarter
111
112 l_current_adjustment_days VARCHAR2(200);
113 l_prior_adjustment_days VARCHAR2(200);
114
115 -- Variables used in implementing Company-CC security feature
116 l_company_security VARCHAR2(4000);
117 l_cost_center_security VARCHAR2(4000);
118 l_comp_security_table VARCHAR2(300);
119 l_cc_security_table VARCHAR2(300);
120 l_comp_security_days_clause VARCHAR2(4000);
121 l_cc_security_days_clause VARCHAR2(4000);
122
123 BEGIN
124
125 -- Procedure reset_globals to re-set the global variables to NULL.
126 -- It will set financial category type to 'OE', company parameter to 'All' and cost parameter to 'All'
127 -- financial category type is set to 'OE' since Cumulative Expense Trend is an expense related report
128
129 fii_ea_util_pkg.reset_globals;
130
131 -- Assigning budget/forecast profile value to local variables
132
133 l_budget_time_unit := NVL(FND_PROFILE.Value( 'FII_BUDGET_TIME_UNIT' ),'P');
134 l_forecast_time_unit := NVL(FND_PROFILE.Value( 'FII_FORECAST_TIME_UNIT' ),'P');
135
136 -- Variables related to Period Type = Quarter
137 -- Default value will be 1 for Period Type = Month
138
139 l_current_adjustment_days := ':CURR_PERIOD_START + 1 ' ;
140 l_prior_adjustment_days := ':PRIOR_PERIOD_START + 1 ' ;
141
142 -- Procedure get_parameters to assign values to different global variables
143 -- like as of date, period type, comparison type - being given by the user
144
145 fii_ea_util_pkg.get_parameters(p_page_parameter_tbl);
146
147 -- Following exercise is done to get the required label in the report table columns
148 -- For example : For Period Type = Month/Quarter, ViewBy Label should be Day
149 -- For Period Type = Year, ViewBy Label should be Month
150
151 IF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
152 fii_ea_util_pkg.g_view_by := 'TIME+FII_TIME_ENT_PERIOD';
153 ELSE
154 fii_ea_util_pkg.g_view_by := 'TIME+FII_TIME_DAY';
155 END IF;
156
157 -- Typical values of l_budget_time_unit/l_forecast_time_unit are:
158 -- P -- Period level
159 -- Q -- Quarter level
160 -- Y -- Year level
161
162 -- Initialisation of local variables for Budget calculations, which are used in the actual SQLs
163
164 CASE fii_ea_util_pkg.g_page_period_type
165 WHEN 'FII_TIME_ENT_YEAR' THEN
166 l_budget_time_id := 'g.ent_year_id';
167 l_budget_table_name := 'fii_time_ent_year';
168 WHEN 'FII_TIME_ENT_QTR' THEN
169 IF l_budget_time_unit = 'P' OR l_budget_time_unit = 'Q' THEN
170 l_budget_time_id := 'g.ent_qtr_id';
171 l_budget_table_name := 'fii_time_ent_qtr';
172 ELSE
173 NULL;
174 END IF;
175 WHEN 'FII_TIME_ENT_PERIOD' THEN
176 IF l_budget_time_unit = 'P' THEN
177 l_budget_time_id := 'g.ent_period_id' ;
178 l_budget_table_name := 'fii_time_ent_period';
179 ELSE
180 NULL;
181 END IF;
182 ELSE
183 NULL;
184 END CASE;
185
186 -- Initialisation of local variables for Forecast calculations, which are used in the actual SQLs
187
188 CASE fii_ea_util_pkg.g_page_period_type
189 WHEN 'FII_TIME_ENT_YEAR' THEN
190 l_forecast_time_id := 'g.ent_year_id';
191 l_forecast_table_name := 'fii_time_ent_year';
192 WHEN 'FII_TIME_ENT_QTR' THEN
193 IF l_forecast_time_unit = 'P' OR l_forecast_time_unit = 'Q' THEN
194 l_forecast_time_id := 'g.ent_qtr_id';
195 l_forecast_table_name := 'fii_time_ent_qtr';
196 ELSE
197 NULL;
198 END IF;
199 WHEN 'FII_TIME_ENT_PERIOD' THEN
200 IF l_forecast_time_unit = 'P' THEN
201 l_forecast_time_id := 'g.ent_period_id' ;
202 l_forecast_table_name := 'fii_time_ent_period';
203 ELSE
204 NULL;
205 END IF;
206 ELSE
207 NULL;
208 END CASE;
209
210
211 -- Following exercise is done to implement Company-CC security feature, required in the Expense Analysis page.
212 -- Variables, l_company_security and l_cost_center_security will hold the requisite SQL's string required for
213 -- implementing Company-CC security feature
214
215 -- Obtaining all possible company-ids to which user has access
216
217 IF fii_ea_util_pkg.g_company_id = 'All' THEN
218 l_company_security :=
219 ' AND f.company_id IN (SELECT company_id
220 FROM fii_company_grants
221 WHERE user_id = fnd_global.user_id
222 AND report_region_code = '''||fii_ea_util_pkg.g_region_code||'''
223 ) ';
224 l_comp_security_table := ',fii_company_grants com ';
225
226 l_comp_security_days_clause := 'AND f.company_id = com.company_id
227 AND com.user_id = fnd_global.user_id
228 AND com.report_region_code = '''||fii_ea_util_pkg.g_region_code||''' ';
229 ELSE
230 l_company_security := ' AND f.company_id = :COMPANY_ID ' ;
231 l_comp_security_days_clause := ' AND f.company_id = :COMPANY_ID ' ;
232 END IF;
233
234 -- Obtaining all possible cost-center-ids to which user has access
235
236 IF fii_ea_util_pkg.g_cost_center_id = 'All' THEN
237 l_cost_center_security :=
238 ' AND f.cost_center_id IN (SELECT cost_center_id
239 FROM fii_cost_center_grants
240 WHERE user_id = fnd_global.user_id
241 AND report_region_code = '''||fii_ea_util_pkg.g_region_code||''' ) ';
242
243 l_cc_security_table := ' ,fii_cost_center_grants cc ';
244
245 l_cc_security_days_clause := ' AND f.cost_center_id = cc.cost_center_id
246 AND cc.user_id = fnd_global.user_id
247 AND cc.report_region_code = '''||fii_ea_util_pkg.g_region_code||''' ';
248 ELSE
249 l_cost_center_security := ' AND f.cost_center_id = :COST_CENTER_ID ';
250 l_cc_security_days_clause := ' AND f.cost_center_id = :COST_CENTER_ID ';
251
252 END IF;
253
254
255 -- Following exercise is done for two reasons and is applicable only for Period = Month/Quarter:
256 -- Reason 1: Getting the values for l_current_adjustment_days and l_prior_adjustment_days.
257 -- For period type = Month, these variables will store value = 1
258 -- For period type = Year, these variables will not be used
259 -- For period type = Quarter,it will hold the number of days in the quarter, as per the following calculation
260
261 -- Reason 2: To show NULL for all the days which fall after As of Date chosen by the user
262 -- Variable, l_display_adjustment obtained here will be used in the main SQL to restrict data display
263 -- only till As of Date and to show NULL thereafter
264
265 IF fii_ea_util_pkg.g_page_period_type <> 'FII_TIME_ENT_YEAR' THEN
266
267 -- Following is done for reason 2 above
268
269 IF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_PERIOD' THEN
270 l_display_adjustment
271 := 'EXTRACT(DAY FROM :ASOF_DATE)';
272
273 ELSE
274 l_display_adjustment
275 := ':ASOF_DATE - :CURR_PERIOD_END';
276 -- Done for reason 1 above
277 l_current_adjustment_days :=
278 ':CURR_PERIOD_END';
279
280 l_prior_adjustment_days :=
281 ':PRIOR_PERIOD_END';
282
283 END IF;
284 END IF;
285
286 -- Getting the profile option for budget/forecast.
287 -- For profile option = 'Y',Cumulative Budget/Forecast will be shown.
288 -- However, for profile option = 'N', Versioning will be taken care of, without any cumulation
289
290 l_budget_forecast_profile := NVL(FND_PROFILE.Value( 'FII_FB_STEP'),'N');
291
292 /* Program Flow will be as follows:
293
294 -- Compare To = Budget/Forecast implies that the third column in the report table (Prior Period)
295 -- will show cumulated expenses to date of prior year.
296 -- For example,If the report parameters are: Compare To = Budget/Forecast, Period Type = Quarter
297 -- and As of Date = Nov 29,2004, then this column will show cumulated expenses for Q3-03,
298 -- i.e. the period will range from 01-Oct-2003 to 31-Dec-2003
299
300 -- When Period Type = Year, then report will show cumulated monthly expenses where as
301 -- For Period Type = Month/Quarter, report will show cumulated expenses on the daily basis
302 -- i.e. 1 to 30(31) for Period type = Month
303 -- and -89 to 0 for Period Type = Quarter (assuming that the quarter is of 90 days)
304
305 */
306
307 -- Defining Budget/Forecast SQL's when Budget/Forecast profile option = 'N' and Period Type = Year
308
309 IF (l_budget_forecast_profile = 'N') THEN
310 IF fii_ea_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
311 l_sql_budget_ver_year :=
312
313 '( SELECT SUM(f.budget_g) FII_EA_BUDGET
314 FROM '||l_budget_table_name||' g -- Profile option = N, Period Type = Year
315 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
316 WHERE f.time_id = '||l_budget_time_id||'
317 '||l_company_security||l_cost_center_security||'
318 AND top_node_fin_cat_type = ''OE''
319 AND NVL(f.budget_version_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE
320 AND :ASOF_DATE BETWEEN g.start_date AND g.end_date
321 AND NVL(f.budget_version_date,time.end_date) <= time.end_date
322
323 -- To choose only those versions having version dates less than or equal to report date
324 -- Here, time is alias of the timeRelated table used in the actual SQL,
325 -- wherein, these Budget/Forecast SQL strings are actually concatenated
326 )';
327
328 l_sql_forecast_ver_year :=
329
330 '( SELECT SUM( f.forecast_g) FII_EA_FORECAST
331 FROM '||l_forecast_table_name||' g -- Profile option = N, Period Type = Year
332 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
333 WHERE f.time_id = '||l_forecast_time_id||'
334 '||l_company_security||l_cost_center_security||'
335 AND top_node_fin_cat_type = ''OE''
336 AND NVL(f.budget_version_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE
337 AND :ASOF_DATE BETWEEN g.start_date AND g.end_date
338 AND NVL(f.budget_version_date,time.end_date) <= time.end_date
339
340 -- To choose only those versions having version dates less than or equal to report date
341 -- Here, time is alias of the timeRelated table used in the actual SQL,
342 -- wherein, these Budget/Forecast SQL strings are actually concatenated
343 )';
344
345 ELSE -- Means that Period Type = Month/Quarter
346
347 IF l_budget_table_name IS NOT NULL THEN -- Check is required to ensure that Budget calculation is not done
348 -- for those cases where-in Period Type chosen is at a more granular level
349 -- than that of Budget uploaded level
350
351 l_sql_budget_ver_month_qtr :=
352
353 '( SELECT SUM(f.budget_g) FII_EA_BUDGET
354 FROM '||l_budget_table_name||' g -- Profile option = N, Period Type = Month/Quarter
355 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
356 WHERE f.time_id = '||l_budget_time_id||'
357 '||l_company_security||l_cost_center_security||'
358 AND top_node_fin_cat_type = ''OE''
359 AND NVL(f.budget_version_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE
360 AND :ASOF_DATE BETWEEN g.start_date AND g.end_date
361 AND NVL(f.budget_version_date,time.report_date) <= time.report_date
362
363 -- To choose only those versions having version dates less than or equal to report date
364 -- Here, time is alias of the timeRelated table used in the actual SQL,
365 -- wherein, these Budget/Forecast SQL strings are actually concatenated
366
367 )';
368 ELSE
369 l_sql_budget_ver_month_qtr := 'NULL'; -- To pass NULL budget value to PMV
370 END IF;
371
372 IF l_forecast_table_name IS NOT NULL THEN -- Check is required to ensure that Forecast calculation is not done
373 -- for those cases where-in Period Type chosen is at a more granular level
374 -- than that of Forecast uploaded level
375 l_sql_forecast_ver_month_qtr :=
376
377 '( SELECT SUM(f.forecast_g) FII_EA_FORECAST
378 FROM '||l_forecast_table_name||' g -- Profile option = N, Period Type = Month/Quarter
379 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
380 WHERE f.time_id = '||l_forecast_time_id||'
381 '||l_company_security||l_cost_center_security||'
382 AND top_node_fin_cat_type = ''OE''
383 AND NVL(f.budget_version_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE
384 AND :ASOF_DATE BETWEEN g.start_date AND g.end_date
385 AND NVL(f.budget_version_date,time.report_date) <= time.report_date
386
387 -- To choose only those versions having version dates less than or equal to report date
388 -- Here, time is alias of the timeRelated table used in the actual SQL,
389 -- wherein, these Budget/Forecast SQL strings are actually concatenated
390
391 )';
392 ELSE
393 l_sql_forecast_ver_month_qtr := 'NULL'; -- To pass NULL forecast value to PMV
394 END IF;
395 END IF;
396 END IF;
397
398 -- SQL statement for Budget/Forecast profile option = Y,
399 -- Period Type = Year, Compare To = Prior Year/Prior Period/Budget/Forecast
400 -- Cumulative Budget/Forecast to be shown
401
402 l_sql_statement1 :=
403
404 'SELECT VIEWBY
405 ,FII_EA_XTD_CUMUL_EXP
406 ,FII_EA_PRIOR_XTD_CUMUL_EXP
407 ,FII_EA_BUDGET
408 ,FII_EA_FORECAST
409 FROM
410 (SELECT month_name VIEWBY --SQL 1
411 ,CASE WHEN FII_EFFECTIVE_NUM > :DISPLAY_SEQUENCE
412 THEN NULL
413 ELSE SUM(FII_EA_XTD_CUMUL_EXP) OVER (ORDER BY FII_EFFECTIVE_NUM
414 ROWS UNBOUNDED PRECEDING)
415 END FII_EA_XTD_CUMUL_EXP
416 ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP) OVER (ORDER BY FII_EFFECTIVE_NUM
417 ROWS UNBOUNDED PRECEDING) FII_EA_PRIOR_XTD_CUMUL_EXP
418 FROM ( SELECT MAX(month_name) month_name
419 ,FII_EFFECTIVE_NUM FII_EFFECTIVE_NUM
420 ,SUM(FII_EA_XTD_CUMUL_EXP) FII_EA_XTD_CUMUL_EXP
421 ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP) FII_EA_PRIOR_XTD_CUMUL_EXP
422 FROM
423 (
424 -- Following SQL is to calculate the values for prior period
425 SELECT time.sequence fii_effective_num
426 ,time.name month_name
427 ,NULL FII_EA_XTD_CUMUL_EXP
428 ,NVL(SUM(FII_EA_PRIOR_XTD_CUMUL_EXP),0)FII_EA_PRIOR_XTD_CUMUL_EXP
429 FROM
430 (
431 SELECT time.sequence FII_EFFECTIVE_NUM
432 ,time.name month_name
433 ,NULL FII_EA_XTD_CUMUL_EXP
434 ,f.actual_g FII_EA_PRIOR_XTD_CUMUL_EXP
435 FROM fii_time_ent_period time
436 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
437 WHERE f.time_id = time.ent_period_id
438 '||l_company_security||l_cost_center_security||' -- To restrict MV records based on
439 -- Company-CC security access
440 AND f.period_type_id = 32
441 AND top_node_fin_cat_type = ''OE''
442 AND time.start_date >= :PRIOR_PERIOD_START
443 AND time.end_date <= :PRIOR_PERIOD_END
444 ) inner_view
445 , fii_time_ent_period time
446 WHERE inner_view.month_name (+) = time.name -- Outer join to ensure that all the months are obtained
447 AND time.start_date >= :PRIOR_PERIOD_START
448 AND time.end_date <= :PRIOR_PERIOD_END
449 GROUP BY time.sequence
450 ,time.name
451
452 UNION ALL
453
454 -- Following SQL is to calculate the values for the current period
455
456 SELECT time.sequence fii_effective_num
457 ,time.name month_name
458 ,NVL(SUM(FII_EA_XTD_CUMUL_EXP),0) FII_EA_XTD_CUMUL_EXP
459 ,NULL FII_EA_PRIOR_XTD_CUMUL_EXP
460 FROM
461 ( SELECT time.sequence FII_EFFECTIVE_NUM
462 ,time.name month_name
463 ,f.actual_g FII_EA_XTD_CUMUL_EXP
464 ,NULL FII_EA_PRIOR_XTD_CUMUL_EXP
465 FROM fii_time_ent_period time
466 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
467 WHERE f.time_id = time.ent_period_id
468 '||l_company_security||l_cost_center_security||' -- To restrict MV records based on
469 -- Company-CC security access
470 AND f.period_type_id = 32
471 AND top_node_fin_cat_type = ''OE''
472 AND time.start_date >= :CURR_PERIOD_START
473 AND time.end_date <= :CURR_PERIOD_END
474 ) inner_view
475 , fii_time_ent_period time
476 WHERE inner_view.month_name (+) = time.name -- Outer join to ensure that all the months are obtained
477 AND time.start_date >= :CURR_PERIOD_START
478 AND time.end_date <= :CURR_PERIOD_END
479 GROUP BY time.sequence
480 ,time.name
481 )
482 GROUP BY FII_EFFECTIVE_NUM
483 ORDER BY FII_EFFECTIVE_NUM
484 )) inner_view1
485 , ( SELECT time.name VIEW_BY
486 ,NVL(SUM(SUM(FII_EA_BUDGET))
487 OVER (ORDER BY time.sequence rows UNBOUNDED PRECEDING),0) FII_EA_BUDGET
488 ,NVL(SUM(SUM(FII_EA_FORECAST))
489 OVER (ORDER BY time.sequence rows UNBOUNDED PRECEDING),0) FII_EA_FORECAST
490 FROM
491 (
492 SELECT time.name month_name
493 ,f.budget_g FII_EA_BUDGET
494 ,f.forecast_g FII_EA_FORECAST
495 FROM fii_time_ent_period time
496 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
497 WHERE f.time_id = time.ent_period_id
498 '||l_company_security||l_cost_center_security||' -- To restrict MV records based on
499 -- Company-CC security access
500 AND f.period_type_id = 32
501 AND top_node_fin_cat_type = ''OE''
502 AND time.start_date >= :CURR_PERIOD_START
503 AND time.start_date <= :CURR_PERIOD_END
504 ) inner_view
505 , fii_time_ent_period time
506 WHERE inner_view.month_name (+) = time.name -- Outer join to ensure that all the months are obtained
507 AND time.start_date >= :CURR_PERIOD_START
508 AND time.end_date <= :CURR_PERIOD_END
509 GROUP BY time.sequence
510 ,time.name
511 ORDER BY time.sequence
512 ) inner_view2
513 WHERE inner_view1.viewby = inner_view2.view_by
514 ';
515
516
517 -- SQL statement for Budget/Forecast profile option = Y,
518 -- Period Type = Month/Quarter, Compare To = Prior Year/Prior Period/Budget/Forecast
519 -- Cumulative Budget/Forecast to be shown
520
521 l_sql_statement2 :=
522
523 'SELECT days VIEWBY
524 ,CASE
525 WHEN days > TO_NUMBER('||l_display_adjustment||') THEN NULL
526 ELSE
527 FII_EA_XTD_CUMUL_EXP
528 END FII_EA_XTD_CUMUL_EXP
529 ,FII_EA_PRIOR_XTD_CUMUL_EXP FII_EA_PRIOR_XTD_CUMUL_EXP
530 ,inner_view2.FII_EA_BUDGET FII_EA_BUDGET
531 ,inner_view2.FII_EA_FORECAST FII_EA_FORECAST
532 FROM
533 (
534 SELECT days --SQL 2
535 ,SUM (FII_EA_XTD_CUMUL_EXP) FII_EA_XTD_CUMUL_EXP
536 ,SUM (FII_EA_PRIOR_XTD_CUMUL_EXP) FII_EA_PRIOR_XTD_CUMUL_EXP
537 FROM (
538
539 -- SQL to calculate the expenses for current period
540 SELECT time.report_date - '||l_current_adjustment_days||' DAYS
541 ,time.report_date REPORT_DATE
542 ,NVL(SUM(SUM(FII_EA_XTD_CUMUL_EXP)) OVER (ORDER BY
543 TO_NUMBER(time.report_date - '||l_current_adjustment_days||')
544 ROWS UNBOUNDED PRECEDING
545 ),0) FII_EA_XTD_CUMUL_EXP
546 ,NULL FII_EA_PRIOR_XTD_CUMUL_EXP
547 FROM
548 (
549 SELECT time.report_date REPORT_DATE
550 ,f.actual_g FII_EA_XTD_CUMUL_EXP
551 ,NULL FII_EA_PRIOR_XTD_CUMUL_EXP
552 FROM fii_time_day time
553 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
554 '||l_comp_security_table||'
555 '||l_cc_security_table||'
556 WHERE f.time_id = time.report_date_julian
557 '||l_comp_security_days_clause||'
558 '||l_cc_security_days_clause||' -- To restrict MV records based on
559 -- Company-CC security access
560 AND top_node_fin_cat_type = ''OE''
561 AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
562 ) inner_view
563 , fii_time_day time
564 WHERE inner_view.report_date (+) = time.report_date -- Outer join to ensure that all the days are obtained
565 AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
566 GROUP BY time.report_date - '||l_current_adjustment_days||'
567 ,time.report_date
568
569 UNION ALL
570
571 -- SQL to calculate the expenses for prior period
572 SELECT time.report_date - '||l_prior_adjustment_days||' DAYS
573 ,time.report_date REPORT_DATE
574 ,NULL FII_EA_XTD_CUMUL_EXP
575 ,NVL(SUM(SUM(FII_EA_PRIOR_XTD_CUMUL_EXP)) OVER (ORDER BY
576 TO_NUMBER(time.report_date - '||l_prior_adjustment_days||')
577 ROWS UNBOUNDED PRECEDING
578 ),0) FII_EA_PRIOR_XTD_CUMUL_EXP
579 FROM
580 ( SELECT time.report_date REPORT_DATE
581 ,NULL FII_EA_XTD_CUMUL_EXP
582 ,f.actual_g FII_EA_PRIOR_XTD_CUMUL_EXP
583 FROM fii_time_day time
584 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
585 '||l_comp_security_table||'
586 '||l_cc_security_table||'
587 WHERE f.time_id = time.report_date_julian
588 '||l_comp_security_days_clause||'
589 '||l_cc_security_days_clause||' -- To restrict MV records based on
590 -- Company-CC security access
591 AND top_node_fin_cat_type = ''OE''
592 AND time.report_date BETWEEN :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
593 ) inner_view
594 , fii_time_day time
595 WHERE inner_view.report_date (+) = time.report_date -- Outer join to ensure that all the days are obtained
596 AND time.report_date BETWEEN :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
597 GROUP BY time.report_date - '||l_prior_adjustment_days||'
598 ,time.report_date
599 )
600 GROUP BY days
601 ) inner_view1
602 ,(SELECT ROUND(SUM(SUM(f.budget_g
603 /(TO_NUMBER(time.ent_period_end_date - time.ent_period_start_date) + 1)))
604 OVER (ORDER BY g.sequence )) -- Done to show Budget at monthly level
605 FII_EA_BUDGET
606 ,ROUND(SUM(SUM(f.forecast_g
607 /(TO_NUMBER(time.ent_period_end_date - time.ent_period_start_date) + 1)))
608 OVER (ORDER BY g.sequence )) -- Done to show Forecast at monthly level
609 FII_EA_FORECAST
610 ,g.sequence
611 ,time.report_date - '||l_current_adjustment_days||'
612 DAYS2
613 FROM fii_time_ent_period g
614 ,fii_time_day time
615 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
616 '||l_comp_security_table||'
617 '||l_cc_security_table||'
618 WHERE f.time_id = g.ent_period_id
619 AND f.period_type_id = 32
620 AND top_node_fin_cat_type = ''OE''
621 '||l_comp_security_days_clause||'
622 '||l_cc_security_days_clause||' -- To restrict MV records based on
623 -- Company-CC security access
624 AND g.ent_period_id = time.ent_period_id
625 AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
626 GROUP BY g.sequence
627 ,time.report_date - '||l_current_adjustment_days||'
628 ) inner_view2
629 WHERE inner_view2.days2 (+) = inner_view1.days -- Outer join to ensure that all the days are obtained
630 ORDER BY viewby
631
632 ';
633
634 -- SQL statement for Budget/Forecast profile option = N,
635 -- Period Type = Year, Compare To = Prior Year/Prior Period/Budget/Forecast
636 -- Budget Versioning feature implemented
637
638
639 l_sql_statement3 :=
640
641 'SELECT month_name VIEWBY --SQL 3
642 ,CASE WHEN FII_EFFECTIVE_NUM > :DISPLAY_SEQUENCE
643 THEN NULL
644 ELSE SUM(FII_EA_XTD_CUMUL_EXP) OVER (ORDER BY FII_EFFECTIVE_NUM
645 ROWS UNBOUNDED PRECEDING)
646 END FII_EA_XTD_CUMUL_EXP
647 ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP) OVER (ORDER BY FII_EFFECTIVE_NUM
648 ROWS UNBOUNDED PRECEDING) FII_EA_PRIOR_XTD_CUMUL_EXP
649 ,FII_EA_BUDGET FII_EA_BUDGET
650 ,FII_EA_FORECAST FII_EA_FORECAST
651 FROM ( SELECT MAX(month_name) month_name
652 ,FII_EFFECTIVE_NUM FII_EFFECTIVE_NUM
653 ,NVL(SUM(FII_EA_XTD_CUMUL_EXP),0) FII_EA_XTD_CUMUL_EXP
654 ,NVL(SUM(FII_EA_PRIOR_XTD_CUMUL_EXP),0)FII_EA_PRIOR_XTD_CUMUL_EXP
655 ,SUM(FII_EA_BUDGET) FII_EA_BUDGET
656 ,SUM(FII_EA_FORECAST) FII_EA_FORECAST
657 FROM
658 (
659 -- Following SQL is to calculate the values for prior period
660
661 SELECT time.sequence fii_effective_num
662 ,time.name month_name
663 ,NULL FII_EA_XTD_CUMUL_EXP
664 ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP) FII_EA_PRIOR_XTD_CUMUL_EXP
665 ,NULL FII_EA_BUDGET
666 ,NULL FII_EA_FORECAST
667 FROM
668 (SELECT time.sequence fii_effective_num
669 ,time.name month_name
670 ,NULL FII_EA_XTD_CUMUL_EXP
671 ,f.actual_g FII_EA_PRIOR_XTD_CUMUL_EXP
672 ,NULL FII_EA_BUDGET
673 ,NULL FII_EA_FORECAST
674 FROM fii_time_ent_period time
675 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
676 WHERE f.time_id = time.ent_period_id
677 '||l_company_security||l_cost_center_security||' -- To restrict MV records based on
678 -- Company-CC security access
679 AND f.period_type_id = 32
680 AND top_node_fin_cat_type = ''OE''
681 AND time.start_date >= :PRIOR_PERIOD_START
682 AND time.end_date <= :PRIOR_PERIOD_END
683 ) inner_view
684 ,fii_time_ent_period time
685 WHERE inner_view.month_name (+) = time.name -- Outer join to ensure that all the months are obtained
686 AND time.start_date >= :PRIOR_PERIOD_START
687 AND time.end_date <= :PRIOR_PERIOD_END
688 GROUP BY time.sequence
689 ,time.name
690
691 UNION ALL
692
693 -- Following SQL is to calculate the values for the current period
694
695 ( SELECT time.sequence fii_effective_num
696 ,time.name month_name
697 ,SUM(f.actual_g) FII_EA_XTD_CUMUL_EXP
698 ,NULL FII_EA_PRIOR_XTD_CUMUL_EXP
699 ,NULL FII_EA_BUDGET
700 ,NULL FII_EA_FORECAST
701 FROM fii_time_ent_period time
702 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
703 WHERE f.time_id = time.ent_period_id
704 '||l_company_security||l_cost_center_security||' -- To restrict MV records based on
705 -- Company-CC security access
706 AND f.period_type_id = 32
707 AND top_node_fin_cat_type = ''OE''
708 AND time.start_date >= :CURR_PERIOD_START
709 AND time.end_date <= :CURR_PERIOD_END
710 GROUP BY time.sequence
711 ,time.name
712 )
713 UNION ALL
714 -- SQL to calculate budget/forecast for the whole year irrespective of As of Date chosen by the user
715
716 SELECT time.sequence FII_EFFECTIVE_NUM
717 ,time.name month_name
718 ,NULL FII_EA_XTD_CUMUL_EXP
719 ,NULL FII_EA_PRIOR_XTD_CUMUL_EXP
720 ,'||l_sql_budget_ver_year||' FII_EA_BUDGET
721 ,'||l_sql_forecast_ver_year||' FII_EA_FORECAST
722 FROM fii_time_ent_period time
723 WHERE time.start_date >= :CURR_PERIOD_START
724 AND time.end_date <= :CURR_PERIOD_END
725 )
726 GROUP BY FII_EFFECTIVE_NUM
727 ORDER BY FII_EFFECTIVE_NUM
728 )';
729
730
731
732 -- SQL statement for Budget/Forecast profile option = N,
733 -- Period Type = Month/Quarter, Compare To = Prior Year/Prior Period/Budget/Forecast
734 -- Budget Versioning feature implemented
735
736
737 l_sql_statement4 :=
738
739 'SELECT days VIEWBY
740 ,CASE
741 WHEN days > TO_NUMBER('||l_display_adjustment||') THEN NULL
742 ELSE
743 FII_EA_XTD_CUMUL_EXP
744 END FII_EA_XTD_CUMUL_EXP
745 ,FII_EA_PRIOR_XTD_CUMUL_EXP
746 ,FII_EA_BUDGET
747 ,FII_EA_FORECAST
748 FROM
749 (SELECT days --SQL 4
750 ,SUM(FII_EA_XTD_CUMUL_EXP) FII_EA_XTD_CUMUL_EXP
751 ,SUM(FII_EA_PRIOR_XTD_CUMUL_EXP) FII_EA_PRIOR_XTD_CUMUL_EXP
752 ,SUM(FII_EA_BUDGET) FII_EA_BUDGET
753 ,SUM(FII_EA_FORECAST) FII_EA_FORECAST
754 FROM (
755
756 -- SQL to calculate the expenses for current period
757 SELECT time.report_date - '||l_current_adjustment_days||' DAYS
758 ,time.report_date REPORT_DATE
759 ,NVL(SUM(SUM(FII_EA_XTD_CUMUL_EXP)) OVER (ORDER BY
760 TO_NUMBER(time.report_date - '||l_current_adjustment_days||')
761 ROWS UNBOUNDED PRECEDING
762 ),0)
763 FII_EA_XTD_CUMUL_EXP
764 ,NULL FII_EA_PRIOR_XTD_CUMUL_EXP
765 ,'||l_sql_budget_ver_month_qtr||' FII_EA_BUDGET
766 ,'||l_sql_forecast_ver_month_qtr||' FII_EA_FORECAST
767 FROM
768 (
769 SELECT time.report_date REPORT_DATE
770 ,f.actual_g FII_EA_XTD_CUMUL_EXP
771 ,NULL FII_EA_PRIOR_XTD_CUMUL_EXP
772 ,NULL FII_EA_BUDGET
773 ,NULL FII_EA_FORECAST
774 FROM fii_time_day time
775 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
776 '||l_comp_security_table||'
777 '||l_cc_security_table||'
778 WHERE f.time_id = time.report_date_julian
779 '||l_comp_security_days_clause||'
780 '||l_cc_security_days_clause||' -- To restrict MV records based on
781 -- Company-CC security access
782 AND top_node_fin_cat_type = ''OE''
783 AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
784
785 ) inner_view
786 , fii_time_day time
787 WHERE inner_view.report_date (+) = time.report_date -- Outer join to ensure that all the days are obtained
788 AND time.report_date BETWEEN :CURR_PERIOD_START AND :CURR_PERIOD_END
789 GROUP BY time.report_date - '||l_current_adjustment_days||'
790 ,time.report_date
791
792 UNION ALL
793
794 -- SQL to calculate the expenses for prior period
795 SELECT time.report_date - '||l_prior_adjustment_days||' DAYS
796 ,time.report_date REPORT_DATE
797 ,NULL FII_EA_XTD_CUMUL_EXP
798 ,NVL(SUM(SUM(FII_EA_PRIOR_XTD_CUMUL_EXP)) OVER (ORDER BY
799 TO_NUMBER(time.report_date - '||l_prior_adjustment_days||')
800 ROWS UNBOUNDED PRECEDING
801 ),0)
802 FII_EA_PRIOR_XTD_CUMUL_EXP
803
804 ,NULL FII_EA_BUDGET
805 ,NULL FII_EA_FORECAST
806 FROM
807 (
808 SELECT time.report_date REPORT_DATE
809 ,NULL FII_EA_XTD_CUMUL_EXP
810 ,f.actual_g FII_EA_PRIOR_XTD_CUMUL_EXP
811 ,NULL FII_EA_BUDGET
812 ,NULL FII_EA_FORECAST
813 FROM fii_time_day time
814 ,fii_gl_trend_sum_mv'||fii_ea_util_pkg.g_curr_view||' f
815 '||l_comp_security_table||'
816 '||l_cc_security_table||'
817 WHERE f.time_id = time.report_date_julian
818 '||l_comp_security_days_clause||'
819 '||l_cc_security_days_clause||' -- To restrict MV records based on
820 -- Company-CC security access
821 AND top_node_fin_cat_type = ''OE''
822 AND time.report_date BETWEEN :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
823 ) inner_view
824 ,fii_time_day time
825 WHERE inner_view.report_date (+) = time.report_date -- Outer join to ensure that all the days are obtained
826 AND time.report_date BETWEEN :PRIOR_PERIOD_START AND :PRIOR_PERIOD_END
827 GROUP BY time.report_date - '||l_prior_adjustment_days||'
828 ,time.report_date
829 )
830 GROUP BY days
831 ) ORDER BY days';
832
833
834 -- Selecting the appropriate SQL to be passed to PMV, based on the input parameters
835
836 CASE l_budget_forecast_profile
837
838 WHEN 'Y' THEN
839 CASE fii_ea_util_pkg.g_page_period_type
840
841 WHEN 'FII_TIME_ENT_YEAR' THEN
842 l_actual_sql_statement := l_sql_statement1;
843
844 ELSE
845 l_actual_sql_statement := l_sql_statement2;
846 END CASE;
847
848 ELSE
849 CASE fii_ea_util_pkg.g_page_period_type
850
851 WHEN 'FII_TIME_ENT_YEAR' THEN
852 l_actual_sql_statement := l_sql_statement3;
853 ELSE
854 l_actual_sql_statement := l_sql_statement4;
855 END CASE;
856 END CASE;
857
858 -- Procedure bind_variable to pass the generated SQL statement and input/output variables to PMV
859 -- for generating Cumulative Expense Trend Report
860
861 fii_ea_util_pkg.bind_variable(
862 p_sqlstmt => l_actual_sql_statement
863 ,p_page_parameter_tbl => p_page_parameter_tbl
864 ,p_sql_output => p_cumulative_expense_sql
865 ,p_bind_output_table => p_cumulative_expense_output
866 );
867 END get_cumul_exp_trend;
868
869
870 END fii_ea_cumul_exp_trend_pkg;
[Home] [Help]