[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_SAL_SUP
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_SAL_SUP AS
2 /* $Header: hriopsbm.pkb 120.6 2005/11/16 03:12:02 cbridge noship $ */
3
4 g_rtn VARCHAR2(200) := '
5 ';
6
7 PROCEDURE get_sql2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
8 x_custom_sql OUT NOCOPY VARCHAR2,
9 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
10
11 l_sqltext VARCHAR2(32767);
12 l_security_clause VARCHAR2(4000);
13 l_custom_rec BIS_QUERY_ATTRIBUTES;
14
15 /* Parameter values */
16 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
17 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
18
19 /* Selective drill url feature */
20 l_drill_url1 VARCHAR2(300);
21 l_drill_url2 VARCHAR2(300);
22 l_drill_url3 VARCHAR2(300);
23
24 /* Direct reports string */
25 l_direct_reports_string VARCHAR2(240);
26
27 /* Dynamic SQL controls */
28 l_wrkfc_fact_params hri_bpl_fact_sup_wrkfc_sql.wrkfc_fact_param_type;
29 l_wrkfc_fact_sql VARCHAR2(10000);
30
31 BEGIN
32
33 /* Initialize out parameters */
34 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
35 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
36
37 /* Get security clause for Manager based security */
38 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
39
40 /* Set direct reports string */
41 l_direct_reports_string := hri_oltp_view_message.get_direct_reports_msg;
42
43 /* Get common parameter values */
44 hri_oltp_pmv_util_param.get_parameters_from_table
45 (p_page_parameter_tbl => p_page_parameter_tbl,
46 p_parameter_rec => l_parameter_rec,
47 p_bind_tab => l_bind_tab);
48
49 /* selective drill across urls */
50 l_drill_url1 := 'pFunctionName=HRI_P_WRKFC_TRN_SUMMARY_PVT&' ||
51 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
52 'VIEW_BY_NAME=VIEW_BY_ID&' ||
53 'pParamIds=Y';
54
55 l_drill_url2 := 'pFunctionName=HRI_P_WMV_SAL_JFM_SUP&' ||
56 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
57 'VIEW_BY_NAME=VIEW_BY_ID&' ||
58 'pParamIds=Y';
59
60 l_drill_url3 := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
61 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
62 'VIEW_BY_NAME=VIEW_BY_ID&' ||
63 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
64 'pParamIds=Y';
65
66 /* substitute for VIEWBY in order by clause */
67 l_parameter_rec.order_by := hri_oltp_pmv_util_pkg.set_default_order_by
68 (p_order_by_clause => l_parameter_rec.order_by);
69
70 /* Get SQL for workforce fact */
71 l_wrkfc_fact_params.bind_format := 'PMV';
72 l_wrkfc_fact_params.include_comp := 'Y';
73 l_wrkfc_fact_params.include_hdc := 'Y';
74 l_wrkfc_fact_params.include_sal := 'Y';
75 l_wrkfc_fact_sql := hri_bpl_fact_sup_wrkfc_sql.get_sql
76 (p_parameter_rec => l_parameter_rec,
77 p_bind_tab => l_bind_tab,
78 p_wrkfc_params => l_wrkfc_fact_params,
79 p_calling_module => 'HRI_OLTP_PMV_WMV_SAL_SUP.GET_SQL2');
80
81 l_sqltext :=
82 'SELECT -- Headcount and Salary Portlet
83 tab.mgr_id VIEWBYID
84 ,DECODE(tab.direct_ind , 0, ''Y'', ''N'') DRILLPIVOTVB
85 ,tab.mgr_value VIEWBY
86 ,tab.order_by HRI_P_ORDER_BY_1
87 ,tab.mgr_value HRI_P_CHAR2_GA
88 ,DECODE(tab.direct_ind,
89 ''1'', ''' || l_drill_url3 || ''',
90 ''' || l_drill_url1 || ''') HRI_P_DRILL_URL1
91 ,tab.curr_hdc HRI_P_WMV_SUM_MV
92 ,tab.curr_sal HRI_P_SAL_ANL_CUR_PARAM_SUM_MV
93 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
94 (p_previous_col => 'tab.comp_sal',
95 p_current_col => 'tab.curr_sal') || '
96 HRI_P_MEASURE5
97 ,tab.curr_avg_sal HRI_P_MEASURE1
98 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
99 (p_previous_col => 'tab.comp_avg_sal',
100 p_current_col => 'tab.curr_avg_sal') || '
101 HRI_P_MEASURE2
102 ,tab.comp_avg_sal HRI_P_MEASURE3
103 ,tab.comp_total_sal HRI_P_MEASURE4
104 ,tab.curr_total_hdc HRI_P_GRAND_TOTAL1
105 ,tab.curr_total_sal HRI_P_GRAND_TOTAL2
106 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
107 (p_previous_col => 'tab.comp_total_sal',
108 p_current_col => 'tab.curr_total_sal') || '
109 HRI_P_GRAND_TOTAL7
110 ,tab.curr_total_avg_sal HRI_P_GRAND_TOTAL3
111 ,tab.comp_total_avg_sal HRI_P_GRAND_TOTAL5
112 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
113 (p_previous_col => 'tab.comp_total_avg_sal',
114 p_current_col => 'tab.curr_total_avg_sal') || '
115 HRI_P_GRAND_TOTAL4
116 ,tab.comp_total_sal HRI_P_GRAND_TOTAL6
117 ,DECODE(tab.direct_ind,
118 ''0'', '''|| l_drill_url2 ||''',
119 '''') HRI_P_CHAR1_GA
120 ,DECODE(tab.direct_ind,
121 ''0'', '''',
122 ''N'') HRI_P_SUPH_RO_CA
123 FROM
124 (SELECT
125 /* View by */
126 hsal.vby_id mgr_id
127 ,DECODE(hsal.direct_ind,
128 1, ''' || l_direct_reports_string || ''',
129 per.value) mgr_value
130 ,to_char(hsal.direct_ind) || per.order_by order_by
131 ,hsal.direct_ind direct_ind
132 ,hsal.curr_hdc_end curr_hdc
133 ,hsal.comp_hdc_end comp_hdc
134 ,hsal.curr_sal_end curr_sal
135 ,hsal.comp_sal_end comp_sal
136 ,DECODE(hsal.curr_hdc_end,
137 0, to_number(null),
138 hsal.curr_sal_end / hsal.curr_hdc_end) curr_avg_sal
139 ,DECODE(hsal.comp_hdc_end,
140 0, to_number(null),
141 hsal.comp_sal_end / hsal.comp_hdc_end) comp_avg_sal
142 ,SUM(hsal.curr_hdc_end) OVER () curr_total_hdc
143 ,SUM(hsal.comp_total_hdc_end) OVER () comp_total_hdc
144 ,SUM(hsal.curr_sal_end) OVER () curr_total_sal
145 ,SUM(hsal.comp_total_sal_end) OVER () comp_total_sal
146 ,DECODE(SUM(hsal.curr_hdc_end) OVER (), 0, to_number(null),
147 SUM(hsal.curr_sal_end) OVER () /
148 SUM(hsal.curr_hdc_end) OVER ()) curr_total_avg_sal
149 ,DECODE(SUM(hsal.comp_total_hdc_end) OVER (), 0, to_number(null),
150 SUM(hsal.comp_total_sal_end) OVER () /
151 SUM(hsal.comp_total_hdc_end) OVER ()) comp_total_avg_sal
152 FROM
153 hri_dbi_cl_per_n_v per
154 ,(' || l_wrkfc_fact_sql || ') hsal
155 WHERE per.id = hsal.vby_id
156 AND (hsal.curr_hdc_end > 0
157 OR hsal.curr_sal_end > 0
158 OR hsal.direct_ind = 1)
159 AND &BIS_CURRENT_ASOF_DATE BETWEEN per.effective_start_date
160 AND per.effective_end_date
161 ) tab
162 WHERE 1 = 1
163 ' || l_security_clause || '
164 ORDER BY ' || l_parameter_rec.order_by;
165
166 x_custom_sql := l_SQLText;
167
168 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
169 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
170 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
171 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
172 x_custom_output.extend;
173 x_custom_output(1) := l_custom_rec;
174
175 l_custom_rec.attribute_name := ':GLOBAL_RATE';
176 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
177 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
178 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
179 x_custom_output.extend;
180 x_custom_output(2) := l_custom_rec;
181
182 END get_sql2;
183 --
184 -- ----------------------------------------------------------------------
185 -- Procedure to fetch the salary KPI
186 -- It fetched the values for the following KPIs
187 -- 1. Total Salary
188 -- 2. Previous Total Salary
189 -- 3. Average Salary
190 -- 4. Previous Average Salary
191 -- ----------------------------------------------------------------------
192 --
193 PROCEDURE get_sal_kpi(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
194 x_custom_sql OUT NOCOPY VARCHAR2,
195 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
196 --
197 -- The security clause
198 --
199 l_security_clause VARCHAR2(4000);
200 --
201 -- Page parameters
202 --
203 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
204 --
205 -- Bind values for SQL and PMV mode
206 --
207 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
208 --
209 -- Parameter values for getting the inner SQL
210 --
211 l_wrkfc_params hri_bpl_fact_sup_wrkfc_sql.WRKFC_FACT_PARAM_TYPE;
212 --
213 -- Inner SQL
214 --
215 l_inn_sql VARCHAR2(32767);
216 l_custom_rec BIS_QUERY_ATTRIBUTES;
217 --
218 BEGIN
219 --
220 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
221 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
222 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
223 --
224 -- Get the parameter information from the page parameter table
225 --
226 hri_oltp_pmv_util_param.get_parameters_from_table
227 (p_page_parameter_tbl => p_page_parameter_tbl,
228 p_parameter_rec => l_parameter_rec,
229 p_bind_tab => l_bind_tab);
230 --
231 -- Set the parameters for getting the inner SQL
232 --
233 l_wrkfc_params.bind_format := 'PMV';
234 l_wrkfc_params.include_comp := 'Y';
235 l_wrkfc_params.include_hdc := 'Y';
236 l_wrkfc_params.include_sal := 'Y';
237 l_wrkfc_params.kpi_mode := 'Y';
238 l_wrkfc_params.bucket_dim := '';
239 --
240 -- Get the inner SQL
241 --
242 l_inn_sql := HRI_OLTP_PMV_QUERY_WRKFC.get_sql
243 (p_parameter_rec => l_parameter_rec,
244 p_bind_tab => l_bind_tab,
245 p_wrkfc_params => l_wrkfc_params,
246 p_calling_module => 'HRI_OLTP_PMV_WMV_SAL_SUP.get_sal_kpi');
247 --
248 -- Form the SQL
249 --
250 x_custom_sql :=
251 'SELECT -- Salary KPIs
252 qry.vby_id VIEWBYID
253 ,qry.vby_id VIEWBY
254 ,qry.curr_sal_end HRI_P_MEASURE1
255 ,qry.comp_sal_end HRI_P_MEASURE2
256 ,DECODE(qry.curr_hdc_end,0, to_number(null),qry.curr_sal_end/qry.curr_hdc_end)
257 HRI_P_MEASURE4
258 ,DECODE(qry.comp_hdc_end,0, to_number(null),qry.comp_sal_end/qry.comp_hdc_end)
259 HRI_P_MEASURE5
260 ,qry.curr_sal_end HRI_P_GRAND_TOTAL1
261 ,qry.comp_sal_end HRI_P_GRAND_TOTAL2
262 ,DECODE(qry.curr_hdc_end, 0, to_number(null),qry.curr_sal_end / qry.curr_hdc_end)
263 HRI_P_GRAND_TOTAL4
264 ,DECODE(qry.comp_hdc_end,0, to_number(null),qry.comp_sal_end / qry.comp_hdc_end)
265 HRI_P_GRAND_TOTAL5
266 FROM
267 ('||l_inn_sql||') qry
268 WHERE 1=1
269 ' || l_security_clause;
270 --
271 -- Set value for global currency
272 --
273 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
274 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
275 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
276 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
277 x_custom_output.extend;
278 x_custom_output(1) := l_custom_rec;
279 --
280 -- Set the value for global rate
281 --
282 l_custom_rec.attribute_name := ':GLOBAL_RATE';
283 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
284 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
285 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
286 x_custom_output.extend;
287 x_custom_output(2) := l_custom_rec;
288 --
289 END get_sal_kpi;
290
291 END HRI_OLTP_PMV_WMV_SAL_SUP ;
292