[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_DTL_SALARY
Source
1 PACKAGE BODY hri_oltp_pmv_dtl_salary AS
2 /* $Header: hriopsdt.pkb 120.15 2006/01/20 06:29:50 jrstewar noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 g_unassigned VARCHAR2(50) := HRI_OLTP_VIEW_MESSAGE.get_unassigned_msg;
8
9
10 /******************************************************************************/
11 /* Salary detail for Staff = Directs */
12 /******************************************************************************/
13 PROCEDURE get_salary_detail_directs
14 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
15 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
16 p_lnk_emp_name IN VARCHAR2,
17 p_lnk_mgr_name IN VARCHAR2,
18 x_custom_sql OUT NOCOPY VARCHAR2,
19 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
20
21 /* Variable to return SQL query for report */
22 l_sqltext VARCHAR2(32000);
23 l_params_header VARCHAR2(10000);
24 l_lnk_profile_chk VARCHAR2(4000);
25
26 /* Table of custom parameters */
27 l_custom_rec BIS_QUERY_ATTRIBUTES;
28
29 /* Security clause */
30 l_security_clause VARCHAR2(1000);
31
32 /* Variables for dynamic part of SQL query */
33 l_parameter_name VARCHAR2(100);
34 l_column VARCHAR2(100);
35 l_where_clause VARCHAR2(1000);
36 l_lnk_emp_name VARCHAR2(4000);
37 l_lnk_mgr_name VARCHAR2(4000);
38 l_pow_factor NUMBER;
39
40 BEGIN
41
42 /* Initialize table/record variables */
43 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
44 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
45
46 /* Set drill URLS */
47 l_lnk_emp_name := p_lnk_emp_name;
48 l_lnk_mgr_name := p_lnk_mgr_name;
49
50 /* Set security clause */
51 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
52
53 /* Loop through parameters that have been set */
54 l_parameter_name := p_bind_tab.FIRST;
55
56 WHILE (l_parameter_name IS NOT NULL) LOOP
57
58 /* Initialize column to null */
59 l_column := NULL;
60
61 /* Check parameters */
62 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
63 l_parameter_name = 'GEOGRAPHY+AREA') THEN
64
65 /* Form parameter condition */
66 l_column := 'geog.' || REPLACE(hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
67 (l_parameter_name).fact_viewby_col, 'geo_');
68
69 ELSIF (l_parameter_name = 'JOB+JOB_FAMILY' OR
70 l_parameter_name = 'JOB+JOB_FUNCTION') THEN
71
72 /* Form parameter condition */
73 l_column := 'jobh.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
74 (l_parameter_name).fact_viewby_col;
75
76 ELSIF (l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
77 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X') THEN
78
79 /* Form parameter condition */
80 l_column := 'asgn.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
81 (l_parameter_name).fact_viewby_col;
82
83 END IF;
84
85 IF (l_column IS NOT NULL) THEN
86
87 /* Dynamically set conditions for parameter */
88 l_where_clause := l_where_clause || 'AND ' || l_column || ' IN (' ||
89 p_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
90
91 END IF;
92
93 /* Check for worker type parameter */
94 IF (l_parameter_name = 'HRI_PRSNTYP+HRI_WKTH_WKTYP') THEN
95
96 /* Staff = Direct Reports - add join and condition on dimension table */
97 l_where_clause := l_where_clause ||
98 'AND ptyp.wkth_wktyp_code IN (' ||
99 p_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
100
101 END IF;
102
103 /* Move to next parameter */
104 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
105
106 END LOOP;
107
108 /* Check Whether the Report is being run in Emp or CWK Mode */
109 IF (p_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
110 l_pow_factor := 1;
111 ELSE
112 l_pow_factor := 12;
113 END IF;
114
115 /* Formulate query */
116 l_sqltext :=
117 'SELECT -- Employee / Contingent Directs Detail' || g_rtn ||
118 /* View by name of person */
119 ' peo.value VIEWBY ' || g_rtn ||
120 /* Order by default person name sort order */
121 ',peo.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
122 /* Name of person */
123 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
124 ',peo.id HRI_P_PER_ID ' || g_rtn ||
125 ',''' || l_lnk_emp_name || '''
126 HRI_P_DRILL_URL1' || g_rtn ||
127 /* Manager of person as of effective date */
128 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
129 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
130 ',''' || l_lnk_mgr_name || '''
131 HRI_P_DRILL_URL2' || g_rtn ||
132 /* assignment organization */
133 ',org.value HRI_P_ORG_CN ' || g_rtn ||
134 /* assignment country */
135 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
136 /* assignment job name (using default display configuration) */
137 ',hri_oltp_view_job.get_job_display_name
138 (job.id
139 ,job.business_group_id
140 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
141 ',asgn.anl_slry HRI_P_SAL_ANL_CUR_PARAM_SUM_MV ' || g_rtn ||
142 ',DECODE(asgn.anl_slry_currency,
143 ''NA_EDW'','''',
144 NULL, '''',
145 asgn.anl_slry_currency)
146 HRI_P_CHAR1_GA ' || g_rtn ||
147 ',hri_oltp_view_currency.convert_currency_amount
148 (asgn.anl_slry_currency
149 ,:GLOBAL_CURRENCY
150 ,&BIS_CURRENT_ASOF_DATE
151 ,asgn.anl_slry
152 ,:GLOBAL_RATE) HRI_P_MEASURE2 ' || g_rtn ||
153 ',:GLOBAL_CURRENCY HRI_P_CHAR2_GA ' || g_rtn ||
154 /* Total period of work in years (current period) */
155 ',ROUND(months_between(&BIS_CURRENT_ASOF_DATE, asgn.pow_start_date_adj) / :POW_FACTOR
156 ,2) HRI_P_MEASURE1' || g_rtn ||
157 /* Performance Rating - for future use */
158 ',to_number(null) HRI_P_CHAR3_GA
159 ,pow.value HRI_P_CHAR5_GA
160 ,prf.value HRI_P_CHAR6_GA
161 ,asgn.pow_start_date_adj HRI_P_DATE1_GA
162 ,hri_bpl_dbi_calc_period.get_term_date
163 (asgn.assignment_id
164 ,peo.id)
165 HRI_P_DATE2_GA
166 FROM
167 hri_mb_asgn_events_ct asgn
168 ,hri_cs_geo_lochr_ct geog
169 ,hri_cs_jobh_ct jobh
170 ,hri_cs_prsntyp_ct ptyp
171 ,hri_dbi_cl_per_n_v peo
172 ,hri_dbi_cl_per_n_v sup
173 ,hri_dbi_cl_job_v job
174 ,hri_dbi_cl_org_n_v org
175 ,hri_dbi_cl_geo_country_v ctr
176 ,hri_dbi_cl_pow_all_band_v pow
177 ,hri_cl_prfmnc_rtng_x_v prf
178 WHERE asgn.supervisor_id = &HRI_PERSON+HRI_PER_USRDR_H
179 AND &BIS_CURRENT_ASOF_DATE BETWEEN asgn.effective_change_date
180 AND asgn.effective_change_end_date
181 AND asgn.SUMMARIZATION_RQD_IND = 1
182 AND asgn.worker_term_ind = 0
183 AND asgn.pre_sprtn_asgn_end_ind = 0
184 AND asgn.headcount > 0
185 AND asgn.person_id = peo.id
186 AND asgn.supervisor_id = sup.id
187 AND asgn.organization_id = org.id
188 AND geog.country_code = ctr.id
189 AND asgn.location_id = geog.location_id
190 AND asgn.job_id = job.id
191 AND asgn.job_id = jobh.job_id
192 AND asgn.prsntyp_sk_fk = ptyp.prsntyp_sk_pk
193 AND asgn.pow_band_sk_fk = pow.id
194 AND asgn.perf_band = prf.id
195 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup.effective_start_date
196 AND sup.effective_end_date
197 AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.effective_start_date
198 AND peo.effective_end_date ' || g_rtn ||
199 l_where_clause ||
200 l_security_clause || g_rtn ||
201 '&ORDER_BY_CLAUSE ';
202
203 x_custom_sql := l_sqltext;
204
205 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
206 l_custom_rec.attribute_value := p_parameter_rec.currency_code;
207 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
208 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
209 x_custom_output.extend;
210 x_custom_output(1) := l_custom_rec;
211
212 l_custom_rec.attribute_name := ':GLOBAL_RATE';
213 l_custom_rec.attribute_value := p_parameter_rec.rate_type;
214 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
215 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
216 x_custom_output.extend;
217 x_custom_output(2) := l_custom_rec;
218
219 l_custom_rec.attribute_name := ':POW_FACTOR';
220 l_custom_rec.attribute_value := l_pow_factor;
221 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
222 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
223 x_custom_output.extend;
224 x_custom_output(3) := l_custom_rec;
225
226 END get_salary_detail_directs;
227
228 /******************************************************************************/
229 /* Salary detail for Staff = All */
230 /******************************************************************************/
231 PROCEDURE get_salary_detail_all
232 (p_parameter_rec IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
233 p_bind_tab IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
234 p_lnk_emp_name IN VARCHAR2,
235 p_lnk_mgr_name IN VARCHAR2,
236 x_custom_sql OUT NOCOPY VARCHAR2,
237 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
238
239 /* Variable to return SQL query for report */
240 l_sqltext VARCHAR2(32000);
241 l_params_header VARCHAR2(10000);
242 l_lnk_profile_chk VARCHAR2(4000);
243
244 /* Table of custom parameters */
245 l_custom_rec BIS_QUERY_ATTRIBUTES;
246
247 /* Security clause */
248 l_security_clause VARCHAR2(1000);
249
250 /* Variables for dynamic part of SQL query */
251 l_parameter_name VARCHAR2(100);
252 l_where_clause VARCHAR2(1000);
253 l_lnk_emp_name VARCHAR2(4000);
254 l_lnk_mgr_name VARCHAR2(4000);
255 l_pow_factor NUMBER;
256
257 BEGIN
258
259 /* Initialize table/record variables */
260 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
261 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
262
263 /* Set drill URLS */
264 l_lnk_emp_name := p_lnk_emp_name;
265 l_lnk_mgr_name := p_lnk_mgr_name;
266
267 /* Set security clause */
268 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
269
270 /* Loop through parameters that have been set */
271 l_parameter_name := p_bind_tab.FIRST;
272
273 WHILE (l_parameter_name IS NOT NULL) LOOP
274
275 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
276 l_parameter_name = 'GEOGRAPHY+AREA' OR
277 l_parameter_name = 'JOB+JOB_FAMILY' OR
278 l_parameter_name = 'JOB+JOB_FUNCTION' OR
279 l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
280 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X') THEN
281
282 /* Dynamically set conditions for parameter */
283 l_where_clause := l_where_clause ||
284 'AND sup_asg.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
285 (l_parameter_name).fact_viewby_col ||
286 ' IN (' || p_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
287
288 END IF;
289
290 /* Move to next parameter */
291 l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
292 END LOOP;
293
294 /* Check Whether the Report is being run in Emp or CWK Mode */
295 IF (p_parameter_rec.wkth_wktyp_sk_fk = 'EMP') THEN
296 l_where_clause := l_where_clause ||
297 'AND sup_asg.wkth_wktyp_code = ''EMP'' ' || g_rtn;
298 l_pow_factor := 12;
299 ELSIF (p_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
300 l_where_clause := l_where_clause ||
301 'AND sup_asg.wkth_wktyp_code = ''CWK'' ' || g_rtn;
302 l_pow_factor := 1;
303 ELSE
304 l_pow_factor := 12;
305 END IF;
306
307 /* Formulate query */
308 l_sqltext :=
309 'SELECT -- Employee / Contingent Detail' || g_rtn ||
310 /* View by name of person */
311 ' peo.value VIEWBY ' || g_rtn ||
312 /* Order by default person name sort order */
313 ',peo.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
314 /* Name of person */
315 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
316 ',peo.id HRI_P_PER_ID ' || g_rtn ||
317 ',''' || l_lnk_emp_name || '''
318 HRI_P_DRILL_URL1' || g_rtn ||
319 /* Manager of person as of effective date */
320 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
321 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
322 ',''' || l_lnk_mgr_name || '''
323 HRI_P_DRILL_URL2' || g_rtn ||
324 /* assignment organization */
325 ',org.value HRI_P_ORG_CN ' || g_rtn ||
326 /* assignment country */
327 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
328 /* assignment job name (using default display configuration) */
329 ',hri_oltp_view_job.get_job_display_name
330 (job.id
331 ,job.business_group_id
332 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
333 ',sup_asg.anl_slry_value HRI_P_SAL_ANL_CUR_PARAM_SUM_MV ' || g_rtn ||
334 ',DECODE(sup_asg.anl_slry_currency,
335 ''NA_EDW'','''',
336 NULL, '''',
337 sup_asg.anl_slry_currency)
338 HRI_P_CHAR1_GA ' || g_rtn ||
339 ',hri_oltp_view_currency.convert_currency_amount
340 (sup_asg.anl_slry_currency
341 ,:GLOBAL_CURRENCY
342 ,&BIS_CURRENT_ASOF_DATE
343 ,sup_asg.anl_slry_value
344 ,:GLOBAL_RATE) HRI_P_MEASURE2 ' || g_rtn ||
345 ',:GLOBAL_CURRENCY HRI_P_CHAR2_GA ' || g_rtn ||
346 /* Total period of work in years (current period) */
347 ',ROUND(months_between(&BIS_CURRENT_ASOF_DATE, sup_asg.pow_start_date) / :POW_FACTOR
348 ,2) HRI_P_MEASURE1' || g_rtn ||
349 /* Performance Rating - for future use */
350 ',to_number(null) HRI_P_CHAR3_GA
351 ,pow.value HRI_P_CHAR5_GA
352 ,prf.value HRI_P_CHAR6_GA
353 ,sup_asg.pow_start_date HRI_P_DATE1_GA
354 ,hri_bpl_dbi_calc_period.get_term_date
355 (sup_asg.assignment_id
356 ,peo.id)
357 HRI_P_DATE2_GA
358 FROM
359 hri_map_sup_wrkfc_asg sup_asg
360 ,hri_dbi_cl_per_n_v peo
361 ,hri_dbi_cl_per_n_v sup
362 ,hri_dbi_cl_job_v job
363 ,hri_dbi_cl_org_n_v org
364 ,hri_dbi_cl_geo_country_v ctr
365 ,hri_dbi_cl_pow_all_band_v pow
366 ,hri_cl_prfmnc_rtng_x_v prf
367 WHERE sup_asg.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
368 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup_asg.effective_date
369 AND sup_asg.effective_end_date
370 AND sup_asg.SUMMARIZATION_RQD_IND = 1
371 AND sup_asg.headcount_value > 0
372 AND sup_asg.metric_adjust_multiplier = 1
373 AND sup_asg.person_id = peo.id
374 AND sup_asg.direct_supervisor_person_id = sup.id
375 AND sup_asg.organization_id = org.id
376 AND sup_asg.geo_country_code = ctr.id
377 AND sup_asg.job_id = job.id
378 AND sup_asg.pow_band_sk_fk = pow.id
379 AND sup_asg.perf_band = prf.id
380 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup.effective_start_date
381 AND sup.effective_end_date
382 AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.effective_start_date
383 AND peo.effective_end_date ' || g_rtn ||
384 l_where_clause ||
385 l_security_clause || g_rtn ||
386 '&ORDER_BY_CLAUSE ';
387
388 x_custom_sql := l_sqltext;
389
390 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
391 l_custom_rec.attribute_value := p_parameter_rec.currency_code;
392 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
393 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
394 x_custom_output.extend;
395 x_custom_output(1) := l_custom_rec;
396
397 l_custom_rec.attribute_name := ':GLOBAL_RATE';
398 l_custom_rec.attribute_value := p_parameter_rec.rate_type;
399 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
400 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
401 x_custom_output.extend;
402 x_custom_output(2) := l_custom_rec;
403
404 l_custom_rec.attribute_name := ':POW_FACTOR';
405 l_custom_rec.attribute_value := l_pow_factor;
406 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
407 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
408 x_custom_output.extend;
409 x_custom_output(3) := l_custom_rec;
410
411 END get_salary_detail_all;
412
413 /******************************************************************************/
414 /* Worker Detail report */
415 /******************************************************************************/
416 PROCEDURE GET_SALARY_DETAIL2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
417 x_custom_sql OUT NOCOPY VARCHAR2,
418 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
419
420
421 /* Parameter values */
422 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
423 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
424
425 /* Variables for dynamic part of SQL query */
426 l_lnk_profile_chk VARCHAR2(4000);
427 l_lnk_emp_name VARCHAR2(4000);
428 l_lnk_mgr_name VARCHAR2(4000);
429
430 BEGIN
431
432 /* Get common parameter values */
433 hri_oltp_pmv_util_param.get_parameters_from_table
434 (p_page_parameter_tbl => p_page_parameter_tbl,
435 p_parameter_rec => l_parameter_rec,
436 p_bind_tab => l_bind_tab);
437
438 /* Activite Drill URL for Link to HR Employee Directory */
439 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk
440 (p_parameter_rec => l_parameter_rec
441 ,p_bind_tab => l_bind_tab);
442
443 IF (l_lnk_profile_chk = 1 AND
444 l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
445 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
446 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
447 END IF ;
448
449 /* Check supervisor hierarchy rollup */
450 IF (l_parameter_rec.peo_sup_rollup_flag = 'N') THEN
451
452 /* Call directs only function to get the SQL */
453 get_salary_detail_directs
454 (p_parameter_rec => l_parameter_rec,
455 p_bind_tab => l_bind_tab,
456 p_lnk_emp_name => l_lnk_emp_name,
457 p_lnk_mgr_name => l_lnk_mgr_name,
458 x_custom_sql => x_custom_sql,
459 x_custom_output => x_custom_output);
460
461 ELSE
462
463 /* Call all staff function to get the SQL */
464 get_salary_detail_all
465 (p_parameter_rec => l_parameter_rec,
466 p_bind_tab => l_bind_tab,
467 p_lnk_emp_name => l_lnk_emp_name,
468 p_lnk_mgr_name => l_lnk_mgr_name,
469 x_custom_sql => x_custom_sql,
470 x_custom_output => x_custom_output);
471
472 END IF;
473
474 END get_salary_detail2;
475
476 /******************************************************************************/
477 /* HR Detail */
478 /******************************************************************************/
479 PROCEDURE get_hr_detail
480 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
481 x_custom_sql OUT NOCOPY VARCHAR2,
482 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
483
484 /* Variable to return SQL query for report */
485 l_sqltext VARCHAR2(32000);
486 l_params_header VARCHAR2(10000);
487 l_lnk_profile_chk VARCHAR2(4000);
488
489 /* Table of custom parameters */
490 l_custom_rec BIS_QUERY_ATTRIBUTES;
491
492 /* Parameter values */
493 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
494 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
495
496 /* Security clause */
497 l_security_clause VARCHAR2(1000);
498
499 /* Variables for dynamic part of SQL query */
500 l_parameter_name VARCHAR2(100);
501 l_where_clause VARCHAR2(1000);
502 l_lnk_emp_name VARCHAR2(4000);
503 l_lnk_mgr_name VARCHAR2(4000);
504 l_pow_factor NUMBER;
505
506 BEGIN
507
508 /* Initialize table/record variables */
509 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
510 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
511
512 /* Get common parameter values */
513 hri_oltp_pmv_util_param.get_parameters_from_table
514 (p_page_parameter_tbl => p_page_parameter_tbl,
515 p_parameter_rec => l_parameter_rec,
516 p_bind_tab => l_bind_tab);
517
518 /* Activite Drill URL for Link to HR Employee Directory */
519 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
520 ,p_bind_tab => l_bind_tab);
521
522 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
523 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
524 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
525 ELSE
526 l_lnk_emp_name := '';
527 l_lnk_mgr_name := '';
528 END IF ;
529
530 /* Set security clause */
531 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
532
533 /* Loop through parameters that have been set */
534 l_parameter_name := l_bind_tab.FIRST;
535
536 WHILE (l_parameter_name IS NOT NULL) LOOP
537
538 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
539 l_parameter_name = 'GEOGRAPHY+AREA' OR
540 l_parameter_name = 'JOB+JOB_FAMILY' OR
541 l_parameter_name = 'JOB+JOB_FUNCTION' OR
542 l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
543 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X') THEN
544
545 /* Dynamically set conditions for parameter */
546 l_where_clause := l_where_clause ||
547 'AND sup_asg.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
548 (l_parameter_name).fact_viewby_col ||
549 ' IN (' || l_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
550
551 END IF;
552
553 /* Move to next parameter */
554 l_parameter_name := l_bind_tab.NEXT(l_parameter_name);
555 END LOOP;
556
557 /* Check supervisor hierarchy rollup */
558 IF (l_parameter_rec.peo_sup_rollup_flag = 'N') THEN
559 l_where_clause := l_where_clause ||
560 'AND sup_asg.direct_ind = 1' || g_rtn;
561 END IF;
562
563 /* Check Whether the Report is being run in Emp or CWK Mode */
564 IF (l_parameter_rec.wkth_wktyp_sk_fk = 'EMP') THEN
565 l_where_clause := l_where_clause || g_rtn
566 ||'AND sup_asg.wkth_wktyp_code = ''EMP'' ';
567 l_pow_factor := 12;
568 ELSIF (l_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
569 l_where_clause := l_where_clause || g_rtn
570 || 'AND sup_asg.wkth_wktyp_code = ''CWK'' ';
571 l_pow_factor := 1;
572 ELSE
573 l_where_clause := l_where_clause || g_rtn
574 || 'AND 1 = 1 ';
575 l_pow_factor := 12;
576 END IF;
577
578 /* Formulate query */
579 l_sqltext :=
580 'SELECT -- HR Employee Detail' || g_rtn ||
581 /* View by name of person */
582 ' peo.value VIEWBY ' || g_rtn ||
583 /* Order by default person name sort order */
584 ',peo.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
585 /* Name of person */
586 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
587 ',peo.id HRI_P_PER_ID ' || g_rtn ||
588 ',''' || l_lnk_emp_name || '''
589 HRI_P_DRILL_URL1' || g_rtn ||
590 /* Manager of person as of effective date */
591 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
592 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
593 ',''' || l_lnk_mgr_name || '''
594 HRI_P_DRILL_URL2' || g_rtn ||
595 /* assignment organization */
596 ',org.value HRI_P_ORG_CN ' || g_rtn ||
597 /* assignment country */
598 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
599 /* assignment job name (using default display configuration) */
600 ',hri_oltp_view_job.get_job_display_name
601 (job.id
602 ,job.business_group_id
603 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
604 /* Total period of work in years (current period) */
605 ',to_number(null) HRI_P_MEASURE1' || g_rtn ||
606 /* Performance Rating - for future use */
607 ',to_number(null) HRI_P_CHAR3_GA
608 ,to_char(null) HRI_P_CHAR5_GA
609 ,to_char(null) HRI_P_CHAR6_GA
610 ,sup_asg.pow_start_date HRI_P_DATE1_GA
611 ,sysdate HRI_P_DATE2_GA
612 FROM
613 hri_map_sup_wrkfc_asg sup_asg
614 ,hri_dbi_cl_per_n_v peo
615 ,hri_dbi_cl_per_n_v sup
616 ,hri_dbi_cl_job_v job
617 ,hri_dbi_cl_org_n_v org
618 ,hri_dbi_cl_geo_country_v ctr
619 WHERE sup_asg.supervisor_person_id = NVL(hri_bpl_security.get_apps_signin_person_id, -1)
620 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup_asg.effective_date
621 AND sup_asg.effective_end_date
622 AND sup_asg.SUMMARIZATION_RQD_IND = 1
623 AND sup_asg.headcount_value > 0
624 AND sup_asg.metric_adjust_multiplier = 1
625 AND sup_asg.person_id = peo.id
626 AND sup_asg.direct_supervisor_person_id = sup.id
627 AND sup_asg.organization_id = org.id
628 AND sup_asg.geo_country_code = ctr.id
629 AND sup_asg.job_id = job.id
630 AND sup_asg.primary_job_role_code = ''HR''
631 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup.effective_start_date
632 AND sup.effective_end_date
633 AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.effective_start_date
634 AND peo.effective_end_date ' || g_rtn ||
635 l_where_clause ||
636 l_security_clause || g_rtn ||
637 '&ORDER_BY_CLAUSE ';
638
639 x_custom_sql := l_sqltext;
640
641 END get_hr_detail;
642
643 END HRI_OLTP_PMV_DTL_SALARY ;