1 PACKAGE BODY fii_gl_cumul_rev_trend_pkg AS
2 /* $Header: FIIGLCGB.pls 120.3 2006/03/15 15:11:42 hpoddar noship $ */
3
4 PROCEDURE get_cumul_rev (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
5 cumul_rev_sql OUT NOCOPY VARCHAR2,
6 cumul_rev_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7 IS
8
9 sqlstmt VARCHAR2(32000);
10 sqlstmt2 VARCHAR2(32000);
11 sqlstmt3 VARCHAR2(32000);
12 l_adjust1 VARCHAR2(100);
13 l_adjust2 VARCHAR2(100);
14 l_forecast VARCHAR(32000); -- its the cumulated forecast value for a particular period, for value of profile option FII_FB_STEP equal to 'N'
15 l_budget VARCHAR(32000) := 1; -- similarly, its the cumulated budget value for a particular period
16 l_time_id VARCHAR2(100);
17 l_time_id2 VARCHAR2(100);
18 l_period_id VARCHAR2(100);
19 l_period_id2 VARCHAR2(100);
20 l_table_name VARCHAR2(100);
21 l_budget_time_id VARCHAR2(100);
22 l_budget_period_id VARCHAR2(100);
23 l_budget_table_name VARCHAR2(100);
24 l_if_budget VARCHAR2(1) := 'N'; -- used to specify that comparison type is budget
25 l_if_budget_zero VARCHAR2(1) := 'N'; -- used to return 0 budget for period type week
26 l_cond VARCHAR2(240); -- it is a part of where clause,
27 -- through which we get the start date for each month present in the quarter or year chosen
28 l_budget_forecast_profile VARCHAR2(1); -- flag used to indicate the value of profile option FII_FB_STEP
29 l_dummy_mgr_id VARCHAR2(100);
30
31 BEGIN
32
33 fii_gl_util_pkg.reset_globals;
34 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
35 fii_gl_util_pkg.get_bitmasks;
36 fii_gl_util_pkg.g_fin_type := 'R'; -- since it is a revenue report, we always put category as 'revenue'
37 fii_gl_util_pkg.get_mgr_pmv_sql;
38 fii_gl_util_pkg.get_cat_pmv_sql;
39
40 l_budget_forecast_profile := NVL(FND_PROFILE.Value( 'FII_FB_STEP'),'N');
41
42 IF fii_gl_util_pkg.g_time_comp = 'BUDGET' THEN
43 l_if_budget := 'Y';
44 END IF;
45
46 CASE fii_gl_util_pkg.g_page_period_type
47
48 WHEN 'FII_TIME_WEEK' THEN l_cond := 'NULL';
49
50 ELSE
51 l_cond := '(SELECT start_date FROM fii_time_ent_period WHERE ent_period_id BETWEEN :CURR_START_PERIOD_ID AND :CURR_END_PERIOD_ID)';
52 END CASE;
53
54 -- since the sql for getting forecast is generated dynamically, the following CASE statement assigns
55 -- values to different variables used to store the table-names, time-ids etc for each period.
56 -- The assignment depends upon the level at which forecast has been loaded.
57
58 CASE fii_gl_util_pkg.g_forecast_period_type
59
60 WHEN 0 THEN l_time_id := 'g.week_id';
61 l_time_id2 := 'g.week_id';
62 l_period_id := 0;
63 l_period_id2 := 0;
64 l_table_name := 'fii_time_week';
65 WHEN 256 THEN l_time_id := 'g.ent_period_id' ;
66 l_time_id2 := 'g.ent_period_id';
67 l_period_id := 32;
68 l_period_id2:= 32;
69 l_table_name := 'fii_time_ent_period';
70 WHEN 512 THEN l_time_id := 'g.ent_qtr_id';
71 l_time_id2 := 'g.ent_period_id';
72 l_period_id := 64;
73 l_period_id2:= 32;
74 l_table_name := 'fii_time_ent_qtr';
75 WHEN 128 THEN l_time_id := 'g.ent_year_id';
76 l_time_id2 := 'g.ent_qtr_id';
77 l_period_id := 128;
78 l_period_id2:= 64;
79 l_table_name := 'fii_time_ent_year';
80 END CASE;
81
82 /* sqlstmt2 gives us a constant value for forecast column, which is same for all the rows, when the profile option is set to display forecast as a single horizontal line */
83
84 sqlstmt2 := '
85 (SELECT SUM(DECODE(f.forecast_g,0,NULL,f.forecast_g))
86 FROM '||l_table_name||' g
87 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
88 WHERE f.time_id = '||l_time_id||'
89 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||'
90 and to_date(:ASOF_DATE,''DD-MM-YYYY'') between g.start_date and g.end_date
91 and f.period_type_id = '||l_period_id||'
92 )';
93
94
95
96
97 CASE fii_gl_util_pkg.g_page_period_type
98
99 WHEN 'FII_TIME_WEEK' THEN l_adjust1 := '1';
100 l_adjust2 := '1';
101 l_forecast := 'NULL';
102 l_if_budget_zero := 'Y';
103 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_DAY'; /* Done for bug 3449775 */
104 WHEN 'FII_TIME_ENT_PERIOD' THEN l_adjust1 := '1';
105 l_adjust2 := '1';
106 l_forecast := sqlstmt2;
107 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_DAY';
108 WHEN 'FII_TIME_ENT_QTR' THEN l_adjust1 := 'to_date(:P_CURR_START,''DD-MM-YYYY'')-to_date(:P_CURR_END,''DD-MM-YYYY'')';
109 l_adjust2 := 'to_date(:P_PRIOR_START,''DD-MM-YYYY'')-to_date(:P_PRIOR_END,''DD-MM-YYYY'')';
110 l_forecast := sqlstmt2;
111 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_DAY';
112 WHEN 'FII_TIME_ENT_YEAR' THEN l_adjust1 := NULL;
113 l_adjust2 := NULL;
114 l_forecast := sqlstmt2;
115 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_ENT_PERIOD';
116 END CASE;
117
118 -- similar to forecast, since the sql for budget is also generated dynamically,
119 -- the folowing CASE statement assigns values to different variables used to compute the budget value.
120 -- The assignment depends upon the level at which budget has been loaded.
121
122 CASE fii_gl_util_pkg.g_time_comp
123
124 WHEN 'BUDGET' THEN
125
126 CASE fii_gl_util_pkg.g_budget_period_type
127
128 WHEN 0 THEN l_budget_time_id := 'g.week_id';
129 l_budget_period_id := 0;
130 l_budget_table_name := 'fii_time_week';
131 WHEN 256 THEN l_budget_time_id := 'g.ent_period_id' ;
132 l_budget_period_id := 32;
133 l_budget_table_name := 'fii_time_ent_period';
134 WHEN 512 THEN l_budget_time_id := 'g.ent_qtr_id';
135 l_budget_period_id := 64;
136 l_budget_table_name := 'fii_time_ent_qtr';
137 WHEN 128 THEN l_budget_time_id := 'g.ent_year_id';
138 l_budget_period_id := 128;
139 l_budget_table_name := 'fii_time_ent_year';
140 END CASE;
141
142 /* sqlstmt3 gives us a constant value for budget column, when the comparison type is budget and the profile option is set to display budget as a single horizontal line*/
143
144 sqlstmt3 := '
145 (SELECT SUM(DECODE(f.budget_g,0,to_number(NULL),f.budget_g))
146 FROM '||l_budget_table_name||' g
147 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
148 WHERE f.time_id = '||l_budget_time_id||'
149 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
150 to_date(:ASOF_DATE,''DD-MM-YYYY'') between g.start_date and g.end_date and
151 f.period_type_id = '||l_budget_period_id||'
152 )';
153
154 CASE fii_gl_util_pkg.g_page_period_type
155
156 WHEN 'FII_TIME_ENT_PERIOD' THEN l_budget := sqlstmt3;
157 WHEN 'FII_TIME_ENT_QTR' THEN l_budget := sqlstmt3;
158 WHEN 'FII_TIME_ENT_YEAR' THEN l_budget := sqlstmt3;
159 ELSE l_budget := 'NULL';
160
161 END CASE;
162 ELSE NULL;
163
164 END CASE;
165
166 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN l_dummy_mgr_id := '-99999';
167 ELSE l_dummy_mgr_id := ' &HRI_PERSON+HRI_PER_USRDR_H';
168 END IF;
169
170 /*-------------------------------------------------
171 FII_PRIOR_TD_G = Graph column for FII_PRIOR_TD |
172 FII_CURRENT_TD = Curent Period Revenue |
173 FII_PRIOR_TD = Prior Period Revenue |
174 FII_FORECAST = Forecast |
175 ------------------------------------------------*/
176
177
178 /*
179 For period types week, month and quarter, when profile option is set to display budget as a single horizontal line, then, first inner sql gives current period revenue, next inner sql gives the prior period revenue or budget
180 else, if forecast and budget are to be displayed in the cumulative format, then, first inner sql gives current period revenue, next inner sql gives the prior period revenue or budget while the last inner sql gives the forecast
181
182 For period type year, when profile option is set to display budget as a single horizontal line, first inner sql gives the prior year revenue, second inner sql gives the revenue or budget of all
183 completed months in the current year while the third inner sql gives NULL revenue for the months ranging
184 from current month to the end of current year
185 else, if forecast and budget are to be displayed in the cumulative format, then, first inner sql gives the prior year revenue, second inner sql gives the revenue or budget of all
186 completed months in the current year, third inner sql gives NULL revenue for the months ranging
187 from current month to the end of current year while fourth inner sql gives forecast
188
189 for more detailed functionality, please refer DLD
190 At any point of time, only 1 out of 6 SQLs is executed. The flow of the code is as follows :
191
192 IF profile option = 'Y' THEN
193 IF comparison type ='BUDGET' THEN
194 IF period type = 'YEAR' THEN 1st SQL
195 ELSE 2nd SQL
196 END IF;
197 ELSIF period type = 'YEAR' THEN 3rd sql
198 ELSE 4th SQL
199 END IF;
200 ELSIF period type = 'YEAR THEN 5th sql
201 ELSE 6th sql
202 END IF;
203
204 */
205
206 CASE l_budget_forecast_profile
207
208 WHEN 'Y' THEN CASE fii_gl_util_pkg.g_time_comp
209
210 WHEN 'BUDGET' THEN
211
212 CASE fii_gl_util_pkg.g_page_period_type
213
214 WHEN 'FII_TIME_ENT_YEAR' THEN
215
216 sqlstmt := '
217 SELECT month_name VIEWBY,
218 SUM(FII_CURRENT_TD) FII_CURRENT_TD,
219 SUM(SUM(DECODE(FII_PRIOR_TD,-99999,NULL,FII_PRIOR_TD))) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING) FII_PRIOR_TD,
220 SUM(SUM(DECODE(FII_FORECAST,-99999,NULL,FII_FORECAST))) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING) FII_FORECAST
221 FROM (
222 SELECT FII_EFFECTIVE_NUM,
223 MAX(month_name) month_name ,
224 SUM(CURR) FII_CURRENT_TD,
225 DECODE(PREVIOUS,-99999,NULL,PREVIOUS ) FII_PRIOR_TD,
226 DECODE(CY_FOR,-99999,NULL,CY_FOR ) FII_FORECAST
227 FROM (
228
229 SELECT per.sequence FII_EFFECTIVE_NUM,
230 per.name month_name,
231 per.ent_period_id id,
232 NULL CURR,
233
234 (CASE WHEN per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') THEN
235 CASE f.budget_g
236 WHEN 0 THEN -99999 /* -99999 used to show NULL when budget
237 value in the DB is equal to 0 */
238 ELSE f.budget_g
239 END
240 ELSE NULL
241 END) PREVIOUS,
242
243 NULL CY_FOR
244 FROM fii_time_ent_period per
245 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
246
247 WHERE per.ent_period_id = f.time_id
248 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
249 per.start_date >= to_date(:P_PRIOR_START,''DD-MM-YYYY'') and
250 per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') and
251 f.period_type_id = 32
252
253 UNION ALL
254
255 (
256 SELECT per.sequence FII_EFFECTIVE_NUM,
257 per.name month_name,
258 per.ent_period_id id,
259
260 CASE WHEN per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
261 per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'')
262 THEN SUM(f.actual_g) OVER (ORDER BY per.ent_period_id ROWS UNBOUNDED PRECEDING)
263 ELSE to_number(NULL)
264 END CURR,
265 NULL PREVIOUS,
266 NULL CY_FOR
267 FROM fii_time_ent_period per
268 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
269
270 WHERE per.ent_period_id = f.time_id
271 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
272 per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
273 per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'') and
274 f.period_type_id = 32
275
276 UNION ALL
277
278 /* the SELECT statement below makes sure that we always return revenue for all 12 months
279 regardless of whether all the months have data or not */
280
281 SELECT per.sequence FII_EFFECTIVE_NUM,
282 per.name month_name,
283 per.ent_period_id id,
284 CASE WHEN per.start_date >= to_date(:P_TEMP,''DD-MM-YYYY'') THEN to_number(NULL)
285 ELSE 0
286 END CURR,
287 NULL PREVIOUS,
288 NULL CY_FOR
289 FROM fii_time_ent_period per
290 WHERE per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
291 per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'')
292
293 UNION ALL
294
295 SELECT per.sequence FII_EFFECTIVE_NUM,
296 per.name month_name,
297 per.ent_period_id id,
298 NULL CURR,
299 NULL PREVIOUS,
300 (CASE WHEN per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'') THEN
301 CASE f.forecast_g
302 WHEN 0 THEN -99999 /* -99999 used to show NULL when forecast
303 value in the DB is equal to 0 */
304 ELSE f.forecast_g
305 END
306 ELSE NULL
307 END) CY_FOR
308
309 FROM fii_time_ent_period per
310 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
311
312 WHERE per.ent_period_id = f.time_id
313 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
314 per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
315 per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'') and
316 f.period_type_id = 32
317
318
319 )
320 )
321 GROUP BY FII_EFFECTIVE_NUM, PREVIOUS, CY_FOR
322 )
323 GROUP BY FII_EFFECTIVE_NUM, month_name
324 ORDER BY FII_EFFECTIVE_NUM';
325
326 ELSE
327
328 sqlstmt := '
329
330 SELECT VIEWBY,
331 SUM(FII_CURRENT_TD) FII_CURRENT_TD,
332 SUM(FII_PRIOR_TD) FII_PRIOR_TD,
333 SUM(FII_FORECAST) FII_FORECAST
334 FROM (
335 SELECT days VIEWBY,
336 SUM(DECODE(SIGN(report_date - to_date(:ASOF_DATE,''DD-MM-YYYY'')),1,NULL,CY_REV)) FII_CURRENT_TD ,
337 NULL FII_PRIOR_TD,
338 NULL CY_BUD,
339 NULL CY_FOR,
340 NULL FII_FORECAST
341 FROM(
342 SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
343 report_date,
344 NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+ to_number('||l_adjust1||') ROWS UNBOUNDED PRECEDING),0) CY_REV,
345 0 PY_REV
346 FROM fii_time_day g,
347 ( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
348 WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
349 f.manager_id(+) = :MGR_MGR_ID and
350 f.gid (+) = 4 and
351 f.period_type_id (+) = 1
352 '||fii_gl_util_pkg.g_cat_join||')
353 ) f
357 GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'),report_date
354 WHERE g.report_date_julian = f.time_id (+) and
355 g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
356
358
359 )
360 GROUP BY days
361
362 UNION ALL
363
364 SELECT days VIEWBY,
365 NULL FII_CURRENT_TD,
366 SUM(SUM(DECODE(inline_view.CY_BUD,-99999,NULL,inline_view.CY_BUD))) OVER (ORDER BY days
367 rows unbounded preceding) FII_PRIOR_TD,
368 CY_BUD,
369 NULL CY_FOR,
370 NULL FII_FORECAST
371 FROM
372 ( SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
373 report_date test,
374 CASE WHEN (g.report_date IN '||l_cond||' ) THEN
375 CASE SUM(f.budget_g)
376 WHEN 0 THEN -99999
377 ELSE SUM(f.budget_g)
378 END
379 ELSE NULL
380 END CY_BUD
381
382
383 FROM fii_time_day g,
384 ( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
385 WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
386 f.manager_id(+) =:MGR_MGR_ID and
387 f.gid (+) = 4 and
388 f.period_type_id (+) = '||l_period_id2||'
389 '||fii_gl_util_pkg.g_cat_join||')
390 ) f
391 WHERE '||l_time_id2||' = f.time_id (+) and
392 g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
393
394 GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'), report_date
395
396 ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||')
397 ) inline_view
398
399 GROUP BY days,CY_BUD
400
401 UNION ALL
402
403 SELECT days VIEWBY,
404 NULL FII_CURRENT_TD,
405 NULL FII_PRIOR_TD,
406 NULL CY_BUD,
407 CY_FOR,
408 SUM(SUM(DECODE(inline_view.CY_FOR,-99999,NULL,inline_view.CY_FOR))) OVER (ORDER BY days
409 ROWS UNBOUNDED PRECEDING) FII_FORECAST
410 FROM
411 ( SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
412 report_date test,
413 CASE WHEN (g.report_date IN '||l_cond||' ) THEN
414 CASE SUM(f.forecast_g)
415 WHEN 0 THEN -99999
416 ELSE SUM(f.forecast_g)
417 END
418 ELSE NULL
419 END CY_FOR
420
421 FROM fii_time_day g,
422 ( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
423 WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
424 f.manager_id(+) =:MGR_MGR_ID and
425 f.gid (+) = 4 and
426 f.period_type_id (+) = '||l_period_id2||'
427 '||fii_gl_util_pkg.g_cat_join||')
428 ) f
429
430 WHERE '||l_time_id2||' = f.time_id (+) and
431 g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
432
433 GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'), report_date
434
435 ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||')
436 ) inline_view
437
438 GROUP BY days, CY_FOR
439 )
440
441 GROUP BY VIEWBY
442 ORDER BY VIEWBY';
443
444 END CASE;
445
446 ELSE
447
448 CASE fii_gl_util_pkg.g_page_period_type
449
450 WHEN 'FII_TIME_ENT_YEAR' THEN
451
452 sqlstmt := '
453 SELECT VIEWBY,
454 FII_CURRENT_TD,
455 SUM(FII_PRIOR_TD) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING) FII_PRIOR_TD,
456 FII_FORECAST
457 FROM (
458 SELECT FII_EFFECTIVE_NUM,
459 month_name VIEWBY,
460 SUM(FII_CURRENT_TD) FII_CURRENT_TD,
461 SUM(FII_PRIOR_TD) FII_PRIOR_TD,
462 SUM(SUM(DECODE(FII_FORECAST,-99999,NULL,FII_FORECAST))) OVER (ORDER BY FII_EFFECTIVE_NUM
463 ROWS UNBOUNDED PRECEDING) FII_FORECAST
464 FROM (
465 SELECT FII_EFFECTIVE_NUM,
466 MAX(month_name) month_name ,
467 SUM(CURR) FII_CURRENT_TD,
468 SUM(PREVIOUS) FII_PRIOR_TD,
469 DECODE(CY_FOR,-99999,NULL,CY_FOR ) FII_FORECAST
470 FROM (
471
472 SELECT per.sequence FII_EFFECTIVE_NUM,
473 per.name month_name,
474 per.ent_period_id id,
475 NULL CURR,
476 (CASE WHEN per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') THEN f.actual_g
477 ELSE to_number(NULL)
478 END) PREVIOUS,
479 NULL CY_FOR
480 FROM fii_time_ent_period per
481 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
482
483 WHERE per.ent_period_id = f.time_id
484 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
485 per.start_date >= to_date(:P_PRIOR_START,''DD-MM-YYYY'') and
486 per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') and
487 f.period_type_id = 32
488
489 UNION ALL
490
491 (
492 SELECT per.sequence FII_EFFECTIVE_NUM,
493 per.name month_name,
494 per.ent_period_id id,
495
496 CASE WHEN per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
497 per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'')
498 THEN SUM(f.actual_g) OVER (ORDER BY per.ent_period_id ROWS UNBOUNDED PRECEDING)
499 ELSE to_number(NULL)
500 END CURR,
504 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
501 NULL PREVIOUS,
502 NULL CY_FOR
503 FROM fii_time_ent_period per
505
506 WHERE per.ent_period_id = f.time_id
507 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
508 per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
509 per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'') and
510 f.period_type_id = 32
511
512 UNION ALL
513
514
515 SELECT per.sequence FII_EFFECTIVE_NUM,
516 per.name month_name,
517 per.ent_period_id id,
518 CASE WHEN per.start_date >= to_date(:P_TEMP,''DD-MM-YYYY'') THEN to_number(NULL)
519 ELSE 0
520 END CURR,
521 NULL PREVIOUS,
522 NULL CY_FOR
523 FROM fii_time_ent_period per
524 WHERE per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
525 per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'')
526
527 UNION ALL
528
529 SELECT per.sequence FII_EFFECTIVE_NUM,
530 per.name month_name,
531 per.ent_period_id id,
532 NULL CURR,
533 NULL PREVIOUS,
534 (CASE WHEN per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'') THEN
535 CASE f.forecast_g
536 WHEN 0 THEN -99999
537 ELSE f.forecast_g
538 END
539 ELSE NULL
540 END) CY_FOR
541
542 FROM fii_time_ent_period per
543 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
544
545 WHERE per.ent_period_id = f.time_id
546 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
547 per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
548 per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'') and
549 f.period_type_id = 32
550
551
552 )
553 )
554 GROUP BY FII_EFFECTIVE_NUM, CY_FOR
555 )
556 GROUP BY FII_EFFECTIVE_NUM, month_name
557 ORDER BY FII_EFFECTIVE_NUM)';
558
559 ELSE
560
561 sqlstmt := '
562
563 SELECT VIEWBY,
564 SUM(FII_CURRENT_TD) FII_CURRENT_TD,
565 SUM(FII_PRIOR_TD) FII_PRIOR_TD,
566 SUM(FII_FORECAST) FII_FORECAST
567 FROM (
568
569 SELECT VIEWBY,
570 SUM(FII_CURRENT_TD) FII_CURRENT_TD,
571 SUM(FII_PRIOR_TD) FII_PRIOR_TD,
572 ( CASE WHEN (CY_FOR = -99999 or CY_FOR = NULL) THEN NULL
573 ELSE SUM(FII_FORECAST)
574 END
575 ) FII_FORECAST
576 FROM (
577 SELECT days VIEWBY,
578 SUM(DECODE(SIGN(report_date - to_date(:ASOF_DATE,''DD-MM-YYYY'')),1,NULL,CY_REV)) FII_CURRENT_TD ,
579 SUM(DECODE(SIGN(report_date - to_date(:P_PRIOR_END,''DD-MM-YYYY'')),1,NULL,PY_REV)) FII_PRIOR_TD,
580 NULL CY_FOR,
581 NULL FII_FORECAST
582 FROM (
583 SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
584 report_date,
585 NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+
586 to_number('||l_adjust1||') ROWS UNBOUNDED PRECEDING),0) CY_REV,
587 0 PY_REV
588 FROM fii_time_day g,
589 ( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
590 WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
591 f.manager_id(+) = :MGR_MGR_ID and
592 f.gid (+) = 4 and
593 f.period_type_id (+) = 1
594 '||fii_gl_util_pkg.g_cat_join||')
595 ) f
596 WHERE g.report_date_julian = f.time_id (+) and
597 g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
598
599 GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'),report_date
600
601 UNION ALL
602
603 SELECT g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||') days,
604 report_date,
605 to_number(NULL) CY_REV,
606 NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+
607 to_number('||l_adjust2||') ROWS UNBOUNDED PRECEDING),0) PY_REV
608 FROM fii_time_day g,
609 ( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
610 WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
611 f.manager_id(+) = :MGR_MGR_ID and
612 f.gid (+) = 4 and
613 f.period_type_id (+) = 1
614 '||fii_gl_util_pkg.g_cat_join||')
615 ) f
616 WHERE f.time_id (+) = g.report_date_julian and
617 g.report_date_julian between :PRIOR_START_DAY_ID and :PRIOR_END_DAY_ID
618
619 GROUP BY g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||'),report_date
620 )
621 GROUP BY days
622
623 UNION ALL
624
625 SELECT days VIEWBY,
626 NULL FII_CURRENT_TD,
627 NULL FII_PRIOR_TD,
628 CY_FOR,
629 SUM(SUM(DECODE(inline_view.CY_FOR,-99999,NULL,inline_view.CY_FOR))) OVER (ORDER BY days
630 ROWS UNBOUNDED PRECEDING) FII_FORECAST
631 FROM
632 (SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
633 report_date test,
634 CASE WHEN (g.report_date IN '||l_cond||' ) THEN
635 CASE SUM(f.forecast_g)
636 WHEN 0 THEN -99999
637 ELSE SUM(f.forecast_g)
638 END
639 ELSE NULL
640 END CY_FOR
644 WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
641
642 FROM fii_time_day g,
643 ( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
645 f.manager_id(+) =:MGR_MGR_ID and
646 f.gid (+) = 4 and
647 f.period_type_id (+) = '||l_period_id2||'
648 '||fii_gl_util_pkg.g_cat_join||')
649 ) f
650 WHERE
651 '||l_time_id2||' = f.time_id (+) and
652 g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
653
654 GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'), report_date
655
656 ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||')
657 ) inline_view
658
659 GROUP BY days, CY_FOR
660
661
662 )
663
664 GROUP BY VIEWBY, CY_FOR
665 )
666
667 GROUP BY VIEWBY
668 ORDER BY VIEWBY';
669
670 END CASE;
671
672 END CASE;
673
674
675 ELSE
676 CASE fii_gl_util_pkg.g_page_period_type
677
678 WHEN 'FII_TIME_ENT_YEAR' THEN
679
680 sqlstmt := '
681 SELECT MONTH_NAME VIEWBY ,
682 FII_CURRENT_TD,
683 CASE WHEN '''||l_if_budget||''' = ''Y'' THEN '||l_budget||'
684 ELSE SUM(FII_PRIOR_TD) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING)
685 END FII_PRIOR_TD,
686 FII_FORECAST
687 FROM
688
689 (SELECT MAX(month_name) month_name,
690 FII_EFFECTIVE_NUM FII_EFFECTIVE_NUM,
691 SUM(CURR) FII_CURRENT_TD,
692 SUM(PREVIOUS) FII_PRIOR_TD,
693 '||l_forecast||' FII_FORECAST
694 FROM (
695
696 SELECT per.sequence FII_EFFECTIVE_NUM,
697 per.name month_name,
698 per.ent_period_id id,
699 NULL CURR,
700 CASE WHEN '''||fii_gl_util_pkg.g_time_comp||'''=''BUDGET'' THEN '||l_budget||'
701 ELSE
702 (CASE WHEN per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') THEN f.actual_g
703 ELSE to_number(NULL)
704 END)
705 END PREVIOUS
706 FROM fii_time_ent_period per
707 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
708
709 WHERE per.ent_period_id = f.time_id
710 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
711 per.start_date >= to_date(:P_PRIOR_START,''DD-MM-YYYY'') and
712 per.end_date <= to_date(:P_PRIOR_END,''DD-MM-YYYY'') and
713 f.period_type_id = 32
714
715 UNION ALL
716
717 (
718 SELECT per.sequence FII_EFFECTIVE_NUM,
719 per.name month_name,
720 per.ent_period_id id,
721
722 CASE WHEN per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'')
723 THEN SUM(f.actual_g) OVER (ORDER BY per.ent_period_id ROWS UNBOUNDED PRECEDING)
724 ELSE to_number(NULL)
725 END CURR,
726 0 PREVIOUS
727 FROM fii_time_ent_period per
728 '||fii_gl_util_pkg.g_view||fii_gl_util_pkg.g_mgr_from_clause||fii_gl_util_pkg.g_cat_from_clause||'
729
730 WHERE per.ent_period_id = f.time_id
731 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||''||fii_gl_util_pkg.g_gid||' and
732 per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
733 per.end_date <= to_date(:ASOF_DATE,''DD-MM-YYYY'') and
734 f.period_type_id = 32
735
736 UNION ALL
737
738
739 SELECT per.sequence FII_EFFECTIVE_NUM,
740 per.name month_name,
741 per.ent_period_id id,
742 CASE WHEN per.start_date >= to_date(:P_TEMP,''DD-MM-YYYY'') THEN to_number(NULL)
743 ELSE 0
744 END CURR,
745 0 PREVIOUS
746 FROM fii_time_ent_period per
747 WHERE per.start_date >= to_date(:P_CURR_START,''DD-MM-YYYY'') and
748 per.end_date <= to_date(:P_CURR_END,''DD-MM-YYYY'')
749
750 )
751 )
752 GROUP BY FII_EFFECTIVE_NUM
753 ORDER BY FII_EFFECTIVE_NUM
754 )';
755
756 ELSE
757
758 sqlstmt := '
759 SELECT days VIEWBY,
760 SUM(DECODE(SIGN(report_date - to_date(:ASOF_DATE,''DD-MM-YYYY'')),1,NULL,CY_REV)) FII_CURRENT_TD ,
761 CASE WHEN '''||fii_gl_util_pkg.g_time_comp||'''=''BUDGET'' THEN '||l_budget||'
762 ELSE SUM(DECODE(SIGN(report_date - to_date(:P_PRIOR_END,''DD-MM-YYYY'')),1,NULL,PY_REV))
763 END FII_PRIOR_TD,
764 '||l_forecast||' FII_FORECAST
765 FROM(
766 SELECT g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') days,
767 report_date,
768 NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||') ROWS UNBOUNDED PRECEDING),0) CY_REV,
769 0 PY_REV
770 FROM fii_time_day g,
771 ( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
772 WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
773 f.manager_id(+) = :MGR_MGR_ID and
774 f.gid (+) = 4 and
775 f.period_type_id (+) = 1
776 '||fii_gl_util_pkg.g_cat_join||')
777 ) f
778 WHERE g.report_date_julian = f.time_id (+) and
779 g.report_date_julian between :CURR_START_DAY_ID and :CURR_END_DAY_ID
780
781 GROUP BY g.report_date-to_date(:P_CURR_START,''DD-MM-YYYY'')+to_number('||l_adjust1||'),report_date
782
783 UNION ALL
784
785 SELECT g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||') days,
786 report_date,
787 to_number(NULL) CY_REV,
788 NVL(SUM(SUM(f.actual_g)) OVER (ORDER BY g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||') ROWS UNBOUNDED PRECEDING),0) PY_REV
789 FROM fii_time_day g,
790 ( SELECT * FROM FII_GL_MGMT_SUM_V'||fii_gl_util_pkg.g_global_curr_view||' f
791 WHERE ( 1=1 and f.person_id(+) = '||l_dummy_mgr_id||' and
792 f.manager_id(+) = :MGR_MGR_ID and
793 f.gid (+) = 4 and
794 f.period_type_id (+) = 1
795 '||fii_gl_util_pkg.g_cat_join||')
796 ) f
797 WHERE g.report_date_julian = f.time_id (+) and
798 g.report_date_julian between :PRIOR_START_DAY_ID and :PRIOR_END_DAY_ID
799
800 GROUP BY g.report_date-to_date(:P_PRIOR_START,''DD-MM-YYYY'')+to_number('||l_adjust2||'),report_date
801 )
802 GROUP BY days
803 ORDER BY days';
804
805 END CASE;
806
807 END CASE;
808
809 fii_gl_util_pkg.bind_variable(p_sqlstmt=>sqlstmt,
810 p_page_parameter_tbl=>p_page_parameter_tbl,
811 p_sql_output=>cumul_rev_sql,
812 p_bind_output_table=>cumul_rev_output);
813 END get_cumul_rev;
814
815 END fii_gl_cumul_rev_trend_pkg;
816
[Home] [Help]