[Home] [Help]
PACKAGE BODY: APPS.FII_GL_EXP_TREND
Source
1 PACKAGE BODY fii_gl_exp_trend AS
2 /* $Header: FIIGLETB.pls 120.32 2006/02/07 13:23:30 hpoddar noship $ */
3
4
5 PROCEDURE get_te_sum (
6 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, expense_sum_sql out NOCOPY VARCHAR2,
7 expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9 BEGIN
10 fii_gl_util_pkg.reset_globals;
11 fii_gl_util_pkg.g_fin_type := 'TE';
12
13 fii_gl_exp_trend.get_expense_sum(p_page_parameter_tbl, expense_sum_sql, expense_sum_output);
14 END get_te_sum;
15
16 PROCEDURE get_exp_sum (
17 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, expense_sum_sql out NOCOPY VARCHAR2,
18 expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
19
20 BEGIN
21 fii_gl_util_pkg.reset_globals;
22 fii_gl_util_pkg.g_fin_type := 'OE';
23
24 fii_gl_exp_trend.get_expense_sum(p_page_parameter_tbl, expense_sum_sql, expense_sum_output);
25 END get_exp_sum;
26
27 PROCEDURE get_expense_sum (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
28 expense_sum_sql out NOCOPY VARCHAR2, expense_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
29 IS
30 sqlstmt VARCHAR2(32000);
31 l_pk VARCHAR2(30);
32 l_name VARCHAR2(100);
33 l_time_comp VARCHAR2(20);
34 l_prior_or_budget VARCHAR2(1000);
35 l_prior_or_budget1 VARCHAR2(1000);
36 l_curr_effective_num NUMBER;
37 l_min_start_date DATE;
38
39
40 BEGIN
41
42 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
43 fii_gl_util_pkg.get_bitmasks;
44 fii_gl_util_pkg.get_mgr_pmv_sql;
45 fii_gl_util_pkg.get_cat_pmv_sql;
46
47 CASE fii_gl_util_pkg.g_page_period_type
48 WHEN 'FII_TIME_WEEK' THEN
49 l_pk := 'week_id';
50 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_WEEK';
51 l_name := 'replace(fnd_message.get_string(''FII'',''FII_WEEK_LABEL''),''&WEEK_NUMBER'',t.sequence)';
52
53 WHEN 'FII_TIME_ENT_PERIOD' THEN
54 l_pk := 'ent_period_id';
55 fii_gl_util_pkg.g_viewby_type :='TIME+FII_TIME_ENT_PERIOD';
56 l_name := 'to_char(t.start_date,''Mon'')';
57
58 WHEN 'FII_TIME_ENT_QTR' THEN
59 l_pk := 'ent_qtr_id';
60 fii_gl_util_pkg.g_viewby_type :='TIME+FII_TIME_ENT_QTR';
61 l_name := 'replace(fnd_message.get_string(''FII'',''FII_QUARTER_LABEL''),''&QUARTER_NUMBER'',t.sequence)';
62
63 WHEN 'FII_TIME_ENT_YEAR' THEN
64 l_pk := 'ent_year_id';
65 fii_gl_util_pkg.g_viewby_type :='TIME+FII_TIME_ENT_YEAR';
66
67 SELECT MIN(start_date) into l_min_start_date
68 FROM fii_time_ent_period;
69
70 /* fix for bug 4962173*/
71
72 SELECT NVL(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(
73 fii_time_api.ent_pyr_start(
74 fii_time_api.ent_pyr_start(
75 fii_gl_util_pkg.g_as_of_date)))),l_min_start_date)
76 INTO fii_gl_util_pkg.g_py_sday
77 FROM dual;
78
79 SELECT NVL(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(
80 fii_time_api.ent_pyr_start(
81 fii_time_api.ent_pyr_start(
82 fii_gl_util_pkg.g_previous_asof_date)))),l_min_start_date)
83 INTO fii_gl_util_pkg.g_five_yr_back
84 FROM dual;
85
86 END CASE;
87
88 /* if budget is selected, the prior amount column will return 0 */
89 IF (fii_gl_util_pkg.g_time_comp = 'SEQUENTIAL') THEN
90 l_prior_or_budget :='case when t.start_date between to_date(:P_EXP_ASOF, ''DD-MM-YYYY'')
91 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
92 then f.forecast_g else TO_NUMBER(NULL) end FORECAST ';
93 ELSIF (fii_gl_util_pkg.g_time_comp = 'YEARLY') THEN
94 l_prior_or_budget := 'to_number(NULL) FORECAST ';
95 ELSIF (fii_gl_util_pkg.g_time_comp = 'BUDGET') THEN
96 l_prior_or_budget := ' to_number(NULL) FORECAST ';
97 END IF;
98
99 /* ----------------------------------
100 FII_MEASURE1 = Time Level Name
101 FII_MEASURE2 = Current Year XTotal
102 FII_MEASURE3 = Prior Year XTotal
103 FII_MEASURE4 = Current Year XTD
104 FII_MEASURE5 = Prior Year XTD
105 * ----------------------------------*/
106
107 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
108 sqlstmt := '
109 select NULL VIEWBY,
110 NULL VIEWBYID,
111 NULL FII_MEASURE2,
112 NULL FII_MEASURE3,
113 NULL FII_MEASURE4,
114 NULL FII_MEASURE5,
115 NULL FII_MEASURE7,
116 NULL FII_MEASURE8,
117 NULL FII_CAL1
118 FROM DUAL
119 WHERE 1=2 ';
120
121 ELSIF fii_gl_util_pkg.g_page_period_type = 'FII_TIME_ENT_YEAR' THEN
122
123
124 sqlstmt := '
125 select t.name VIEWBY,
126 t.'||l_pk||' VIEWBYID,
127 sum(CY_QTOT) FII_MEASURE2,
128 sum(PY_QTOT) FII_MEASURE3,
129 sum(CY_QTD) FII_MEASURE4,
130 sum(PY_QTD) FII_MEASURE5,
131 sum(BUDGET) FII_MEASURE7,
132 sum(FORECAST) FII_MEASURE8,
133 NVL(sum(CY_QTOT), 0) + NVL(sum(CY_QTD), 0) FII_CAL1
134 from (
135 select t.sequence FII_SEQUENCE,
136 f.actual_g CY_QTOT,
137 TO_NUMBER(NULL) PY_QTOT,
138 TO_NUMBER(NULL) CY_QTD,
139 TO_NUMBER(NULL) PY_QTD,
140 f.budget_g BUDGET,
141 TO_NUMBER(NULL) FORECAST
142 from fii_gl_mgmt_sum_v'||fii_gl_util_pkg.g_global_curr_view ||' f,
143 '||fii_gl_util_pkg.g_page_period_type||' t
144 '||fii_gl_util_pkg.g_mgr_from_clause||'
145 where 1=1 '||fii_gl_util_pkg.g_gid||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||'
146 and f.time_id = t.'||l_pk||'
147 and f.period_type_id = :PERIOD_TYPE
148 and t.start_date between to_date(:FIVE_YR_BACK, ''DD-MM-YYYY'')
149 and to_date(:ENT_PYR_END, ''DD-MM-YYYY'')
150 union all
151 select t.sequence FII_SEQUENCE,
152 TO_NUMBER(NULL) CY_QTOT,
153 TO_NUMBER(NULL) PY_QTOT,
154 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
155 bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
156 then f.actual_g else TO_NUMBER(NULL) end CY_QTD,
157 TO_NUMBER(NULL) PY_QTD,
158 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
159 bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
160 then f.budget_g else to_number(null) end BUDGET,
161 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
162 bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
163 then f.forecast_g else to_number(null) end FORECAST
164 from fii_gl_mgmt_sum_v'|| fii_gl_util_pkg.g_global_curr_view ||' f,
165 FII_TIME_RPT_STRUCT cal,
166 '||fii_gl_util_pkg.g_page_period_type||' t,
167 fii_time_day day
168 '||fii_gl_util_pkg.g_mgr_from_clause||'
169 where 1=1 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
170 and f.time_id = cal.time_id
171 and f.period_type_id = cal.period_type_id
172 and cal.report_date = day.report_date
173 and day.'||l_pk||' = t.'||l_pk||'
174 and bitand(cal.record_type_id,:WHERE_PERIOD_TYPE)=cal.record_type_id
175 and cal.report_date = &BIS_CURRENT_ASOF_DATE
176 ) g1, '||fii_gl_util_pkg.g_page_period_type||' t
177 where FII_SEQUENCE (+)= t.sequence
178 and t.start_date >= to_date(:PY_SAME_DAY, ''DD-MM-YYYY'')
179 and t.end_date <= to_date(:ENT_CYR_END, ''DD-MM-YYYY'')
180 group by t.sequence, t.name, t.'||l_pk||'
181 order by t.sequence';
182 ELSIF (fii_gl_util_pkg.g_page_period_type = 'FII_TIME_ENT_QTR') and (fii_gl_util_pkg.g_time_comp = 'SEQUENTIAL') THEN
183 sqlstmt := '
184 select t.name VIEWBY,
185 t.'||l_pk||' VIEWBYID,
186 CY_QTOT FII_MEASURE2,
187 PY_QTOT FII_MEASURE3,
188 CY_QTD FII_MEASURE4,
189 PY_QTD FII_MEASURE5,
190 BUDGET FII_MEASURE7,
191 FORECAST FII_MEASURE8,
192 NVL(CY_QTOT, 0) + NVL(CY_QTD, 0) FII_CAL1
193 from
194 (select inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
195 sum(CY_QTOT) CY_QTOT,
196 sum(PY_QTOT) PY_QTOT,
197 sum(CY_QTD) CY_QTD,
198 sum(PY_QTD) PY_QTD,
199 sum(BUDGET) BUDGET,
200 sum(FORECAST) FORECAST
201 from
202 (select t.'||l_pk||' FII_SEQUENCE,
203 sum(case when t.'||l_pk||' <> :CURR_EFFECTIVE_SEQ then f.actual_g
204 else TO_NUMBER(NULL) end) CY_QTOT,
205 TO_NUMBER(NULL) PY_QTOT,
206 TO_NUMBER(NULL) CY_QTD,
207 TO_NUMBER(NULL) PY_QTD,
208 sum(case when t.start_date between to_date(:P_EXP_ASOF, ''DD-MM-YYYY'')
209 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
210 then f.budget_g else TO_NUMBER(NULL) end) BUDGET,
211 sum(case when t.start_date between to_date(:P_EXP_ASOF, ''DD-MM-YYYY'')
212 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
213 then f.forecast_g else TO_NUMBER(NULL) end) FORECAST
214 from fii_gl_mgmt_sum_v'|| fii_gl_util_pkg.g_global_curr_view ||' f,
215 '||fii_gl_util_pkg.g_page_period_type||' t
216 '||fii_gl_util_pkg.g_mgr_from_clause||'
217 where 1=1 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
218 and f.time_id = t.'||l_pk||'
219 and f.period_type_id = :PERIOD_TYPE
220 and t.start_date between to_date(:P_EXP_START, ''DD-MM-YYYY'')
221 and &BIS_CURRENT_ASOF_DATE
222 group by t.'||l_pk||'
223 union all
224 select :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
225 TO_NUMBER(NULL) CY_QTOT,
226 TO_NUMBER(NULL) PY_QTOT,
227 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
228 bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
229 then f.actual_g else TO_NUMBER(NULL) end CY_QTD,
230 TO_NUMBER(NULL) PY_QTD,
231 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
232 bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
233 then f.budget_g else to_number(null) end BUDGET,
234 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
235 bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
236 then f.forecast_g else to_number(null) end FORECAST
237 from fii_gl_mgmt_sum_v'||fii_gl_util_pkg.g_global_curr_view ||' f,
238 FII_TIME_RPT_STRUCT cal
239 '||fii_gl_util_pkg.g_mgr_from_clause||'
240 where 1=1 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
241 and f.time_id = cal.time_id
242 and f.period_type_id = cal.period_type_id
243 and bitand(cal.record_type_id,:WHERE_PERIOD_TYPE)=cal.record_type_id
244 and cal.report_date in (&BIS_CURRENT_ASOF_DATE, to_date(:P_EXP_ASOF, ''DD-MM-YYYY'') )) inner_inline_view
245 group by inner_inline_view.FII_SEQUENCE
246 ) g1, '||fii_gl_util_pkg.g_page_period_type||' t
247 where g1.fii_effective_num (+)= t.'||l_pk||'
248 and t.start_date <= &BIS_CURRENT_ASOF_DATE
249 and t.start_date > to_date(:P_EXP_START, ''DD-MM-YYYY'')
250 order by t.start_date';
251 ELSE
252 sqlstmt := '
253 select t.name VIEWBY,
254 t.'||l_pk||' VIEWBYID,
255 CY_QTOT FII_MEASURE2,
256 PY_QTOT FII_MEASURE3,
257 CY_QTD FII_MEASURE4,
258 PY_QTD FII_MEASURE5,
259 BUDGET FII_MEASURE7,
260 FORECAST FII_MEASURE8,
261 NVL(CY_QTOT, 0) + NVL(CY_QTD, 0) FII_CAL1
262 from
263 (select inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
264 sum(CY_QTOT) CY_QTOT,
265 sum(PY_QTOT) PY_QTOT,
266 sum(CY_QTD) CY_QTD,
267 sum(PY_QTD) PY_QTD,
268 sum(BUDGET) BUDGET,
269 sum(FORECAST) FORECAST
270 from
271 (select t.sequence FII_SEQUENCE,
272 case when t.sequence <> :CURR_EFFECTIVE_SEQ then (case when t.start_date between to_date(:P_EXP_ASOF, ''DD-MM-YYYY'')
273 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
274 then f.actual_g else TO_NUMBER(NULL)end) else TO_NUMBER(NULL) end CY_QTOT,
275 case when t.start_date between to_date(:P_EXP_START, ''DD-MM-YYYY'')
276 and to_date(:P_EXP_ASOF, ''DD-MM-YYYY'')
277 then f.actual_g else TO_NUMBER(NULL) end PY_QTOT,
278 TO_NUMBER(NULL) CY_QTD,
279 TO_NUMBER(NULL) PY_QTD,
280 case when t.start_date between to_date(:P_EXP_ASOF, ''DD-MM-YYYY'')
281 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
282 then f.budget_g else TO_NUMBER(NULL) end BUDGET,
283 '||l_prior_or_budget||'
284 from fii_gl_mgmt_sum_v'||fii_gl_util_pkg.g_global_curr_view ||' f,
285 '||fii_gl_util_pkg.g_page_period_type||' t
286 '||fii_gl_util_pkg.g_mgr_from_clause||'
287 where f.time_id = t.'||l_pk||'
288 and 1=1 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
289 and f.time_id = t.'||l_pk||'
290 and f.period_type_id = :PERIOD_TYPE
291 and t.start_date between to_date(:P_EXP_START, ''DD-MM-YYYY'')
292 and &BIS_CURRENT_ASOF_DATE
293 union all
294 select :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
295 TO_NUMBER(NULL) CY_QTOT,
296 TO_NUMBER(NULL) PY_QTOT,
297 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
298 bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
299 then f.actual_g else TO_NUMBER(NULL) end CY_QTD,
300 case when cal.report_date = to_date(:P_EXP_ASOF, ''DD-MM-YYYY'') and
301 bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE) = cal.record_type_id
302 then f.actual_g else TO_NUMBER(NULL) end PY_QTD,
303 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
304 bitand(cal.record_type_id, :BUDGET_PERIOD_TYPE) = cal.record_type_id
305 then f.budget_g else to_number(null) end BUDGET,
306 case when cal.report_date = &BIS_CURRENT_ASOF_DATE and
307 bitand(cal.record_type_id, :FORECAST_PERIOD_TYPE) = cal.record_type_id
308 then f.forecast_g else to_number(null) end FORECAST
309 from fii_gl_mgmt_sum_v'||fii_gl_util_pkg.g_global_curr_view ||' f,
310 FII_TIME_RPT_STRUCT cal
311 '||fii_gl_util_pkg.g_mgr_from_clause||'
312 where 1=1 '||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
313 and f.time_id = cal.time_id
314 and f.period_type_id = cal.period_type_id
315 and bitand(cal.record_type_id,:WHERE_PERIOD_TYPE)=cal.record_type_id
316 and cal.report_date in (&BIS_CURRENT_ASOF_DATE, to_date(:P_EXP_ASOF, ''DD-MM-YYYY'') )) inner_inline_view
317 group by inner_inline_view.FII_SEQUENCE
318 ) g1, '||fii_gl_util_pkg.g_page_period_type||' t
319 where g1.fii_effective_num (+)= t.sequence
320 and t.start_date <= &BIS_CURRENT_ASOF_DATE
321 and t.start_date > to_date(:P_EXP_BEGIN, ''DD-MM-YYYY'')
322 order by t.start_date';
323 END IF;
324
325 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, expense_sum_sql, expense_sum_output);
326
327 END get_expense_sum;
328
329 END fii_gl_exp_trend;
330