[Home] [Help]
PACKAGE BODY: APPS.FII_GL_EXPENSE_PKG_TREND
Source
1 PACKAGE BODY fii_gl_expense_pkg_trend AS
2 /* $Header: FIIGLE2B.pls 120.33 2005/10/30 05:08:12 appldev noship $ */
3
4 PROCEDURE get_exp_per_emp_trend(p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_per_emp_trend_sql out NOCOPY VARCHAR2, exp_per_emp_trend_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
5 IS
6 sqlstmt VARCHAR2(32000);
7 l_pk VARCHAR2(30);
8 l_ak_region_item VARCHAR2(100);
9 g_min_start_date DATE;
10 BEGIN
11 fii_gl_util_pkg.reset_globals;
12 fii_gl_util_pkg.g_fin_type := 'OE';
13 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
14 fii_gl_util_pkg.get_bitmasks;
15 fii_gl_util_pkg.get_mgr_pmv_sql;
16 fii_gl_util_pkg.get_cat_pmv_sql;
17
18 select min(start_date) into g_min_start_date
19 from fii_time_ent_period;
20
21 CASE fii_gl_util_pkg.g_page_period_type
22 WHEN 'FII_TIME_WEEK' THEN
23 l_pk := 'week_id';
24 l_ak_region_item := 'BIS_WEEK';
25 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_WEEK';
26
27 /* Commented out for bug 3893359 and replaced with select
28
29 fii_gl_util_pkg.g_cy_period_end := fii_time_api.pwk_end(trunc(sysdate));
30 fii_gl_util_pkg.g_py_sday := fii_time_api.sd_lyswk(trunc(sysdate)); */
31
32 select NVL(fii_time_api.pwk_end(trunc(sysdate)),g_min_start_date),
33 NVL(fii_time_api.sd_lyswk(trunc(sysdate)),g_min_start_date)
34 INTO fii_gl_util_pkg.g_cy_period_end,
35 fii_gl_util_pkg.g_py_sday
36 FROM dual;
37
38 fii_gl_util_pkg.g_begin_date := trunc(sysdate) - 91;
39
40 select distinct a.sequence into fii_gl_util_pkg.g_curr_per_sequence
41 from FII_TIME_WEEK a
42 where trunc(sysdate) BETWEEN a.START_DATE AND a.END_DATE;
43
44 WHEN 'FII_TIME_ENT_PERIOD' THEN
45 l_pk := 'ent_period_id';
46 l_ak_region_item := 'MONTH';
47 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_ENT_PERIOD';
48
49 /* Commented out for bug 3893359 and replaced with select
50
51 fii_gl_util_pkg.g_cy_period_end := fii_time_api.ent_pper_end(trunc(sysdate));
52 fii_gl_util_pkg.g_py_sday := fii_time_api.ent_sd_lysper_end(trunc(sysdate)); */
53
54 select NVL(fii_time_api.ent_pper_end(trunc(sysdate)),g_min_start_date),
55 NVL(fii_time_api.ent_sd_lysper_end(trunc(sysdate)),g_min_start_date)
56 INTO fii_gl_util_pkg.g_cy_period_end,
57 fii_gl_util_pkg.g_py_sday
58 FROM dual;
59
60 fii_gl_util_pkg.g_begin_date := fii_gl_util_pkg.g_py_sday;
61
62
63 select distinct a.sequence into fii_gl_util_pkg.g_curr_per_sequence
64 from FII_TIME_ENT_PERIOD a
65 where trunc(sysdate) BETWEEN a.START_DATE AND a.END_DATE;
66
67 WHEN 'FII_TIME_ENT_QTR' THEN
68 l_pk := 'ent_qtr_id';
69 l_ak_region_item := 'QUARTER';
70 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_ENT_QTR';
71 /* Commented out for bug 3893359 and replaced with select
72
73 fii_gl_util_pkg.g_cy_period_end := fii_time_api.ent_pqtr_end(trunc(sysdate));
74 fii_gl_util_pkg.g_py_sday := fii_time_api.ent_sd_lysqtr_end(
75 fii_time_api.ent_sd_lysqtr_end(
76 trunc(sysdate))); */
77
78 select NVL(fii_time_api.ent_pqtr_end(trunc(sysdate)),g_min_start_date),
79 NVL(fii_time_api.ent_sd_lysqtr_end(fii_time_api.ent_sd_lysqtr_end(trunc(sysdate))),g_min_start_date)
80 INTO fii_gl_util_pkg.g_cy_period_end,
81 fii_gl_util_pkg.g_py_sday
82 FROM dual;
83
84 fii_gl_util_pkg.g_begin_date := fii_gl_util_pkg.g_py_sday;
85
86
87
88 select distinct ent_qtr_id
89 into fii_gl_util_pkg.g_curr_per_sequence
90 from FII_TIME_ENT_QTR a
91 where trunc(sysdate) between a.START_DATE and a.END_DATE;
92
93
94 WHEN 'FII_TIME_ENT_YEAR' THEN
95 l_pk := 'ent_year_id';
96 l_ak_region_item := 'YEAR';
97 fii_gl_util_pkg.g_viewby_type := 'TIME+FII_TIME_ENT_YEAR';
98
99 /* Commented out for bug 3893359 and replaced with select
100 fii_gl_util_pkg.g_cy_period_end := fii_time_api.ent_pyr_end(
101 trunc(sysdate));
102 fii_gl_util_pkg.g_py_sday := fii_time_api.ent_pyr_start(
103 fii_time_api.ent_pyr_start(
104 fii_time_api.ent_pyr_start(
105 fii_time_api.ent_pyr_start(
106 trunc(sysdate)))));
107 fii_gl_util_pkg.g_begin_date := fii_time_api.ent_pyr_start(
108 fii_gl_util_pkg.g_py_sday); */
109
110
111 select NVL(fii_time_api.ent_pyr_end(trunc(sysdate)),g_min_start_date),
112 NVL(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(
113 fii_time_api.ent_pyr_start(
114 fii_time_api.ent_pyr_start(
115 trunc(sysdate))))),g_min_start_date)
116 INTO fii_gl_util_pkg.g_cy_period_end,
117 fii_gl_util_pkg.g_py_sday
118 FROM dual;
119
120 SELECT NVL(fii_time_api.ent_pyr_start(fii_gl_util_pkg.g_py_sday),g_min_start_date)
121 INTO fii_gl_util_pkg.g_begin_date
122 FROM dual;
123
124
125 select distinct a.sequence
126 into fii_gl_util_pkg.g_curr_per_sequence
127 from FII_TIME_ENT_YEAR a
128 where trunc(sysdate) BETWEEN a.START_DATE AND a.END_DATE;
129
130
131 END CASE;
132
133 /*
134 VIEWBY = Time Level display name e.g. Q1, Q2, Q3, Q4
135 FII_HEADCOUNT = Headcount
136 FII_CURRENT_TD = Expenses
137 */
138
139
140 -- -First UNION gets TOTALs amounts for current year qtrs
141 -- -Second UNION gets TD amounts for ongoing quarter
142
143 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
144
145 sqlstmt := '
146 SELECT NULL VIEWBY,
147 NULL VIEWBYID,
148 NULL FII_HEADCOUNT,
149 NULL FII_CURRENT_TD
150 FROM DUAL
151 WHERE 1=2';
152
153 ELSIF fii_gl_util_pkg.g_page_period_type <> 'FII_TIME_ENT_QTR' THEN
154 sqlstmt := '
155 SELECT t.name VIEWBY,
156 t.'||l_pk||' VIEWBYID,
157 nvl(FII_HEADCOUNT, 1) FII_HEADCOUNT,
158 FII_CURRENT_TD FII_CURRENT_TD
159 FROM
160 (SELECT inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
161 sum(FII_HEADCOUNT) FII_HEADCOUNT,
162 sum(FII_CURRENT_TD) FII_CURRENT_TD
163 FROM
164 (SELECT t.sequence FII_SEQUENCE,
165 to_number(null) FII_HEADCOUNT,
166 decode(t.sequence, :CURR_EFFECTIVE_SEQ, to_number(null), f.actual_g) FII_CURRENT_TD
167 FROM '||fii_gl_util_pkg.g_page_period_type||' t '||fii_gl_util_pkg.g_view||'
168 WHERE 1=1 '||fii_gl_util_pkg.g_mgr_join||
169 fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
170 AND f.time_id = t.'||l_pk||'
171 AND f.period_type_id = :PERIOD_TYPE
172 AND t.start_date between to_date(:PY_SAME_DAY, ''DD-MM-YYYY'')
173 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
174 UNION ALL
175 SELECT :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
176 to_number(null) FII_HEADCOUNT,
177 case when c.report_date = trunc(sysdate)
178 then f.actual_g else to_number(null) end FII_CURRENT_TD
179 FROM fii_time_rpt_struct_v c
180 '|| fii_gl_util_pkg.g_view ||'
181 WHERE 1=1 '||fii_gl_util_pkg.g_mgr_join||
182 fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
183 AND f.time_id = c.time_id
184 AND f.period_type_id = c.period_type_id
185 AND bitand(c.record_type_id,:ACTUAL_PERIOD_TYPE) = c.record_type_id
186 AND c.report_date in (trunc(sysdate))
187 UNION ALL
188 SELECT t.sequence FII_SEQUENCE,
189 decode(t.sequence, :CURR_EFFECTIVE_SEQ, to_number(null), total_headcount+1) FII_HEADCOUNT,
190 to_number(null) FII_CURRENT_TD
191 FROM hri_mdp_sup_wmv_sup_mv,
192 '||fii_gl_util_pkg.g_page_period_type||' t
193 WHERE supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
194 AND effective_start_date = (SELECT max(effective_start_date)
195 FROM hri_mdp_sup_wmv_sup_mv aa
196 WHERE supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
197 AND effective_start_date <= t.end_date
198 )
199 AND t.start_date between to_date(:PY_SAME_DAY, ''DD-MM-YYYY'')
200 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
201 UNION ALL
202 SELECT :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
203 total_headcount+1 FII_HEADCOUNT,
204 to_number(null) FII_CURRENT_TD
205 FROM hri_mdp_sup_wmv_sup_mv
206 WHERE supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
207 AND effective_start_date = (SELECT max(effective_start_date)
208 FROM hri_mdp_sup_wmv_sup_mv aa
209 WHERE supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
210 AND aa.effective_start_date <= trunc(sysdate)
211 )
212 ) inner_inline_view
213 GROUP BY inner_inline_view.FII_SEQUENCE
214 ) g1, '||fii_gl_util_pkg.g_page_period_type||' t
215 WHERE g1.fii_effective_num (+)= t.sequence
216 AND t.start_date <= trunc(sysdate)
217 AND t.start_date > to_date(:BEGIN_DATE, ''DD-MM-YYYY'')
218 ORDER BY t.start_date';
219
220 ELSE
221 sqlstmt := '
222 SELECT t.name VIEWBY,
223 t.'||l_pk||' VIEWBYID,
224 nvl(FII_HEADCOUNT,1) FII_HEADCOUNT,
225 FII_CURRENT_TD FII_CURRENT_TD
226 FROM
227 (SELECT inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
228 sum(FII_HEADCOUNT) FII_HEADCOUNT,
229 sum(FII_CURRENT_TD) FII_CURRENT_TD
230 FROM
231 (SELECT t.ent_qtr_id FII_SEQUENCE,
232 to_number(null) FII_HEADCOUNT,
233 decode(t.sequence, :CURR_EFFECTIVE_SEQ, to_number(null), f.actual_g) FII_CURRENT_TD
234 FROM '||fii_gl_util_pkg.g_page_period_type||' t '||fii_gl_util_pkg.g_view||'
235 WHERE 1=1 '||fii_gl_util_pkg.g_mgr_join||
236 fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
237 AND f.time_id = t.'||l_pk||'
238 AND f.period_type_id = :PERIOD_TYPE
239 AND t.start_date between to_date(:PY_SAME_DAY, ''DD-MM-YYYY'')
240 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
241 UNION ALL
242 SELECT :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
243 to_number(null) FII_HEADCOUNT,
244 case when c.report_date = trunc(sysdate)
245 then f.actual_g else to_number(null) end FII_CURRENT_TD
246 FROM fii_time_rpt_struct_v c
247 '|| fii_gl_util_pkg.g_view ||'
248 WHERE 1=1 '||fii_gl_util_pkg.g_mgr_join||
249 fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
250 AND f.time_id = c.time_id
251 AND f.period_type_id = c.period_type_id
252 AND bitand(c.record_type_id,:ACTUAL_PERIOD_TYPE) = c.record_type_id
253 AND c.report_date in (trunc(sysdate))
254 UNION ALL
255 SELECT t.ent_qtr_id FII_SEQUENCE,
256 decode(t.sequence, :CURR_EFFECTIVE_SEQ, to_number(null), total_headcount+1) FII_HEADCOUNT,
257 to_number(null) FII_CURRENT_TD
258 FROM hri_mdp_sup_wmv_sup_mv,
259 '||fii_gl_util_pkg.g_page_period_type||' t
260 WHERE supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
261 AND effective_start_date = (SELECT max(effective_start_date)
262 FROM hri_mdp_sup_wmv_sup_mv aa
263 WHERE supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
264 AND effective_start_date <= t.end_date
265 )
266 AND t.start_date between to_date(:PY_SAME_DAY, ''DD-MM-YYYY'')
267 and to_date(:CY_PERIOD_END, ''DD-MM-YYYY'')
268 UNION ALL
269 SELECT :CURR_EFFECTIVE_SEQ FII_SEQUENCE,
270 total_headcount+1 FII_HEADCOUNT,
271 to_number(null) FII_CURRENT_TD
272 FROM hri_mdp_sup_wmv_sup_mv
273 WHERE supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
274 AND effective_start_date = (SELECT max(effective_start_date)
275 FROM hri_mdp_sup_wmv_sup_mv aa
276 WHERE supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
277 AND aa.effective_start_date <= trunc(sysdate)
278 )
279 ) inner_inline_view
280 GROUP BY inner_inline_view.FII_SEQUENCE
281 ) g1, '||fii_gl_util_pkg.g_page_period_type||' t
282 WHERE g1.fii_effective_num (+)= t.'||l_pk||'
283 AND t.start_date <= trunc(sysdate)
284 AND t.start_date > to_date(:BEGIN_DATE, ''DD-MM-YYYY'')
285 ORDER BY t.start_date';
286
287 END IF;
288
289 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_per_emp_trend_sql, exp_per_emp_trend_output);
290
291
292 END get_exp_per_emp_trend;
293
294 END fii_gl_expense_pkg_trend;