[Home] [Help]
PACKAGE BODY: APPS.FII_GL_EXPENSE_PKG
Source
1 PACKAGE BODY FII_GL_EXPENSE_PKG AS
2 /* $Header: FIIGLEXB.pls 120.39 2006/03/15 15:12:45 hpoddar noship $ */
3
4
5 PROCEDURE get_te_per_emp (
6 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_per_emp_sql out NOCOPY VARCHAR2,
7 exp_per_emp_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_expense_pkg.get_expenses_per_emp(p_page_parameter_tbl, exp_per_emp_sql, exp_per_emp_output);
14 END get_te_per_emp;
15
16 PROCEDURE get_exp_per_emp (
17 p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL, exp_per_emp_sql out NOCOPY VARCHAR2,
18 exp_per_emp_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_expense_pkg.get_expenses_per_emp(p_page_parameter_tbl, exp_per_emp_sql, exp_per_emp_output);
25 END get_exp_per_emp;
26
27
28
29 PROCEDURE GET_EXPENSES_PER_EMP (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
30 exp_per_emp_sql out NOCOPY VARCHAR2,
31 exp_per_emp_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
32 IS
33
34 sqlstmt VARCHAR2(30000);
35 l_prior_exp VARCHAR2(5000) := NULL;
36 l_prior_hc VARCHAR2(5000) := NULL;
37 l_prior_gt_exp VARCHAR2(5000) := NULL;
38 l_prior_gt_hc VARCHAR2(5000) := NULL;
39 l_total_prior_hc NUMBER;
40
41 l_shared_hr_flag VARCHAR2(1) := NVL(fnd_profile.value('HRI_DBI_FORCE_SHARED_HR'),'N');
42
43 BEGIN
44 fii_gl_util_pkg.g_view_by := 'HRI_PERSON+HRI_PER_USRDR_H';
45 fii_gl_util_pkg.get_parameters(p_page_parameter_tbl);
46 fii_gl_util_pkg.get_bitmasks;
47 fii_gl_util_pkg.get_viewby_sql;
48 fii_gl_util_pkg.get_mgr_pmv_sql;
49 fii_gl_util_pkg.get_cat_pmv_sql;
50
51 /*
52 VIEWBY = MANAGER
53 FII_MEASURE1 = Current Expenses
54 FII_MEASURE9 = Prior Expenses
55 FII_MEASURE8 = Current headcount
56 FII_MEASURE10 = Prior headcount
57 FII_MEASURE2 = Headcount (kpi)
58 FII_MEASURE3 = Average Expenses per Head (kpi)
59 FII_MEASURE12 = Grand Total (Current Expenses)
60 FII_ATTRIBUTE10 = Grand Total (Prior Expenses)
61 FII_ATTRIBUTE11 = Grand Total (Current Headcount)
62 FII_ATTRIBUTE12 = Grand Total (Prior Headcount)
63 FII_MEASURE14 = url for VIEWBY
64 FII_MEASURE5 = null
65 */
66
67 -- -------------------------------------------------
68 -- Following bind is used to calculate total
69 -- head-count under a given manager
70 -- and used in the Expenses per Head reports
71 -- -------------------------------------------------
72 --query below gets the headcount for the reporting date
73 begin
74 select total_headcount+1 into fii_gl_util_pkg.g_total_hc
75 from hri_mdp_sup_wmv_sup_mv
76 where supervisor_person_id = fii_gl_util_pkg.g_mgr_id
77 and effective_start_date = (SELECT max(aa.effective_start_date)
78 FROM hri_mdp_sup_wmv_sup_mv aa
79 WHERE aa.supervisor_person_id = fii_gl_util_pkg.g_mgr_id
80 AND aa.effective_start_date <= decode(l_shared_hr_flag, 'N', fii_gl_util_pkg.g_as_of_date, sysdate));
81 exception
82 when others then
83 fii_gl_util_pkg.g_total_hc := 1;
84 end;
85
86 --query below gets the headcount as of the prior reporting date
87 begin
88 select total_headcount+1 into l_total_prior_hc
89 from hri_mdp_sup_wmv_sup_mv
90 where supervisor_person_id = fii_gl_util_pkg.g_mgr_id
91 and effective_start_date = (SELECT max(aa.effective_start_date)
92 FROM hri_mdp_sup_wmv_sup_mv aa
93 WHERE aa.supervisor_person_id = fii_gl_util_pkg.g_mgr_id
94 AND aa.effective_start_date <= decode(l_shared_hr_flag, 'N', fii_gl_util_pkg.g_previous_asof_date, sysdate));
95 exception
96 when others then
97 l_total_prior_hc := 1;
98 end;
99
100 -- First Union gets head count for the directs with cost center resposibility for a given manager as of reporting date.
101 -- This union excludes the Manager himself.
102 -- Second Union gets head count for the directs with cost center responsibility for a given manager as of prior reporting date.
103 --This union exlucdes the manager himself.
104 -- Third Union accounts for the directs and the manager themselves in the headcount number for both reporting date + prior reporting date.
105 -- Fourth Union gets the expenses for reporting date + prior reporting date.
106
107 IF (fii_gl_util_pkg.g_time_comp = 'BUDGET') then
108 l_prior_exp := 'to_number(null) FII_MEASURE9,';
109 l_prior_hc := 'to_number(null) FII_MEASURE10,';
110 l_prior_gt_exp := 'to_number(null) FII_ATTRIBUTE10,';
111 l_prior_gt_hc := 'to_number(null) FII_ATTRIBUTE12,';
112 ELSE
113 l_prior_exp := 'PY_XTD FII_MEASURE9,';
114 l_prior_hc := ' case when f.VIEWBY_ID = &HRI_PERSON+HRI_PER_USRDR_H
115 then '||l_total_prior_hc||'+1-sum(f.PY_HEADCNT) over()
116 else f.PY_HEADCNT
117 end FII_MEASURE10,';
118 l_prior_gt_exp := ' sum(PY_XTD) over() FII_ATTRIBUTE10,';
119 l_prior_gt_hc := l_total_prior_hc||' FII_ATTRIBUTE12,';
120 END IF;
121
122 IF fii_gl_util_pkg.g_mgr_id = -99999 THEN
123
124
125 sqlstmt := 'SELECT NULL VIEWBY,
126 NULL VIEWBYID,
127 NULL FII_MEASURE1,
128 NULL FII_MEASURE9,
129 NULL FII_MEASURE8,
130 NULL FII_MEASURE10,
131 NULL FII_MEASURE2,
132 NULL FII_MEASURE3,
133 NULL FII_MEASURE12,
134 NULL FII_ATTRIBUTE10,
135 NULL FII_ATTRIBUTE11,
136 NULL FII_ATTRIBUTE12,
137 NULL FII_MEASURE14,
138 NULL FII_MEASURE5
139 FROM DUAL
140 WHERE 1=2';
141
142 ELSE
143
144
145 sqlstmt := '
146 SELECT
147 decode(:MGR_ID, f.viewby_id, decode(:DIM_FLAG,''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
148 f.viewby_id VIEWBYID,
149 CY_XTD FII_MEASURE1,
150 '||l_prior_exp||'
151 case when f.VIEWBY_ID = &HRI_PERSON+HRI_PER_USRDR_H
152 then :TOTAL_HC+1-sum(f.headcnt) over()
153 else f.headcnt
154 end FII_MEASURE8,
155 '||l_prior_hc||'
156 case when f.VIEWBY_ID = &HRI_PERSON+HRI_PER_USRDR_H
157 then :TOTAL_HC+1-sum(f.headcnt) over()
158 else f.headcnt
159 end FII_MEASURE2,
160 CY_XTD /nullif((case when f.VIEWBY_ID = &HRI_PERSON+HRI_PER_USRDR_H
161 then :TOTAL_HC+1-sum(f.headcnt) over()
162 else f.headcnt end), 0) FII_MEASURE3,
163 sum(CY_XTD) over() FII_MEASURE12, -- Added for bug#2955837,
164 '||l_prior_gt_exp||'
165 :TOTAL_HC FII_ATTRIBUTE11,
166 '||l_prior_gt_hc||' -- Added for bug#2955837
167 decode(NVL(:MGR_ID, -9999), f.viewby_id, '''', ''pFunctionName=FII_GL_EXP_PER_EMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y'') FII_MEASURE14,
168 sum(CY_XTD) over() /
169 NULLIF(:TOTAL_HC,0) FII_MEASURE5
170 FROM (
171 SELECT /*+ NO_MERGE */ viewby_id VIEWBY_ID,
172 sum(cy_xtd) CY_XTD,
173 sum(py_xtd) PY_XTD,
174 sum(headcnt) HEADCNT,
175 sum(py_headcnt) PY_HEADCNT
176 FROM (
177 SELECT mgr.emp_id viewby_id,
178 NVL(cnt.total_headcount,0) HEADCNT,
179 to_number(NULL) PY_HEADCNT,
180 to_number(NULL) CY_XTD,
181 to_number(NULL) PY_XTD
182 FROM fii_cc_mgr_hierarchies mgr,
183 hri_mdp_sup_wmv_sup_mv cnt
184 WHERE mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
185 AND mgr.emp_level = mgr.mgr_level + 1
186 AND cnt.supervisor_person_id = mgr.emp_id
187 AND cnt.effective_start_date = (SELECT /*+ no_unnest*/ MAX(cnt2.effective_start_date)
188 FROM hri_mdp_sup_wmv_sup_mv cnt2
189 WHERE cnt.supervisor_person_id = cnt2.supervisor_person_id
190 AND cnt2.effective_start_date <= decode('''||l_shared_hr_flag||''', ''N'', &BIS_CURRENT_ASOF_DATE, sysdate))
191 AND cnt.total_headcount > 0
192 UNION ALL
193 SELECT mgr.emp_id viewby_id,
194 to_number(NULL) HEADCNT,
195 NVL(cnt.total_headcount,0) PY_HEADCNT,
196 to_number(NULL) CY_XTD,
197 to_number(NULL) PY_XTD
198 FROM fii_cc_mgr_hierarchies mgr,
199 hri_mdp_sup_wmv_sup_mv cnt
200 WHERE mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
201 AND mgr.emp_level = mgr.mgr_level + 1
202 AND cnt.supervisor_person_id = mgr.emp_id
203 AND cnt.effective_start_date = (SELECT /*+ no_unnest*/ MAX(cnt2.effective_start_date)
204 FROM hri_mdp_sup_wmv_sup_mv cnt2
205 WHERE cnt.supervisor_person_id = cnt2.supervisor_person_id
206 AND cnt2.effective_start_date <= decode('''||l_shared_hr_flag||''', ''N'', &BIS_PREVIOUS_ASOF_DATE, sysdate))
207 AND cnt.total_headcount > 0
208 UNION ALL
209 SELECT mgr.EMP_ID viewby_id,
210 1 HEADCNT,
211 1 PY_HEADCNT,
212 to_number(NULL) CY_XTD,
213 to_number(NULL) PY_XTD
214 FROM fii_cc_mgr_hierarchies mgr
215 WHERE mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
216 AND mgr.emp_level <= mgr.mgr_level + 1
217 UNION ALL
218 SELECT f.person_id viewby_id,
219 to_number(NULL) HEADCNT,
220 to_number(NULL) PY_HEADCNT,
221 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
222 then f.actual_g else to_number(null) end) CY_XTD,
223 sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
224 then f.actual_g else to_number(null) end) PY_XTD
225 FROM fii_time_rpt_struct cal'||fii_gl_util_pkg.g_view||'
226 WHERE 1=1'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
227 AND f.time_id = cal.time_id
228 AND f.period_type_id = cal.period_type_id
229 AND bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE)= cal.record_type_id
230 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
231 GROUP BY f.person_id
232 ) h
233 GROUP BY VIEWBY_ID
234 ) f, '||fii_gl_util_pkg.g_viewby_from_clause||'
235 WHERE '||fii_gl_util_pkg.g_viewby_join||'
236 ORDER BY NVL(FII_MEASURE3, -9999999999) desc';
237
238 END IF;
239
240
241 fii_gl_util_pkg.bind_variable(sqlstmt, p_page_parameter_tbl, exp_per_emp_sql, exp_per_emp_output);
242
243
244 END GET_EXPENSES_PER_EMP;
245
246 END FII_GL_EXPENSE_PKG;