[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_EMB_EQTY_ANAL
Source
1 PACKAGE BODY HRI_OLTP_EMB_EQTY_ANAL AS
2 /* $Header: hrioembeanl.pkb 120.3.12000000.2 2007/04/16 06:27:48 vjaganat noship $ */
3
4
5 g_schema VARCHAR2(50) := 'APPS';
6
7 g_rtn VARCHAR2(30) := '
8 ';
9 g_curr_conv_rate_type VARCHAR2(20) := 'Corporate';
10 g_return_success VARCHAR2(1000)
11 := 'SUCCESS:'||'DATA_LAST_UPDATE_DATE='
12 || get_last_updated_date_msg(get_mv_last_refresh_date('HRI_MDP_SUP_WRKFC_JX_MV'));
13
14 g_your_org_msg_lbl VARCHAR2(100)
15 := REPLACE(fnd_message.get_string('HRI','HRI_407307_YOUR_ORGANIZATION'),'''','''''');
16 g_company_msg_lbl VARCHAR2(100)
17 := REPLACE(fnd_message.get_string('HRI','HRI_407312_COMPANY'),'''','''''');
18 g_mgrs_org_msg_lbl VARCHAR2(100)
19 := REPLACE(fnd_message.get_string('HRI','HRI_407317_MANAGERS_ORG'),'''','''''');
20
21 g_sal_amount_fmt VARCHAR2(100) := 'FM999,999,999,999';
22
23
24 /*
25 ** Returns the data a single materialized view was last refreshed
26 **/
27 FUNCTION get_mv_last_refresh_date (p_mv_name IN VARCHAR2) RETURN DATE
28 IS
29
30 l_mv_last_refresh_date DATE:= SYSDATE;
31
32 CURSOR cur_mv_refresh_date IS
33 SELECT last_refresh_date
34 FROM dba_mviews
35 WHERE mview_name = p_mv_name
36 AND owner = g_schema ;
37
38
39 BEGIN
40
41 OPEN cur_mv_refresh_date;
42 FETCH cur_mv_refresh_date INTO l_mv_last_refresh_date;
43 CLOSE cur_mv_refresh_date;
44
45 RETURN l_mv_last_refresh_date;
46
47 EXCEPTION WHEN OTHERS THEN
48 IF cur_mv_refresh_date%ISOPEN THEN
49 CLOSE cur_mv_refresh_date;
50 END IF;
51 RETURN SYSDATE;
52
53 END get_mv_last_refresh_date;
54
55 /* returns a translated string of the form:
56 ** "Data Last Updated: DD-MON-YYYY"
57 **/
58 FUNCTION get_last_updated_date_msg (p_date_token DATE) RETURN VARCHAR2
59 IS
60 BEGIN
61 fnd_message.set_name('BIS', 'BIS_PMV_LAST_UPDATE_DATE');
62 fnd_message.set_token('LAST_UPD_DATE', fnd_date.date_to_chardate(p_date_token));
63
64 RETURN fnd_message.get;
65 END get_last_updated_date_msg;
66
67
68 FUNCTION check_mgr_is_above_in_hrchy(p_top_mgr_person_id IN NUMBER
69 ,p_lower_mgr_person_id IN NUMBER
70 ,p_effective_date IN DATE)
71 RETURN BOOLEAN
72 IS
73
74 CURSOR cur_sup_chk IS
75 SELECT 1
76 FROM hri_cs_suph suph
77 WHERE trunc(p_effective_date) BETWEEN suph.effective_start_date AND effective_end_date
78 AND suph.sup_person_id = p_top_mgr_person_id
79 AND suph.sub_person_id = p_lower_mgr_person_id
80 AND suph.sub_invalid_flag_code = 'N';
81
82 l_sup_chk NUMBER;
83
84 BEGIN
85
86 IF (p_top_mgr_person_id = p_lower_mgr_person_id) THEN
87 RETURN TRUE;
88 END IF;
89
90 IF ( p_top_mgr_person_id IS NULL
91 OR p_lower_mgr_person_id IS NULL
92 OR p_effective_date IS NULL) THEN
93 RETURN FALSE;
94 END IF;
95
96 OPEN cur_sup_chk;
97 FETCH cur_sup_chk INTO l_sup_chk;
98
99 IF cur_sup_chk%FOUND THEN
100 IF cur_sup_chk%ISOPEN THEN
101 CLOSE cur_sup_chk;
102 END IF;
103 RETURN TRUE;
104
105 ELSE
106 IF cur_sup_chk%ISOPEN THEN
107 CLOSE cur_sup_chk;
108 END IF;
109 RETURN FALSE;
110
111 END IF;
112
113 EXCEPTION WHEN OTHERS THEN
114 IF cur_sup_chk%ISOPEN THEN
115 CLOSE cur_sup_chk;
116 END IF;
117 RETURN FALSE;
118
119 END check_mgr_is_above_in_hrchy;
120
121
122 /* Return, based on the parameter context, the summary table/MV name
123 **
124 ** Hardcoded to a new MV in first version
125 **
126 */
127 PROCEDURE get_wrkfc_fact_table(
128 p_parameter_rec IN HRI_OLTP_EMB_EQTY_ANAL.HRI_EMB_PARAM_REC_TYPE
129 ,o_sql_string OUT NOCOPY VARCHAR2
130 ,o_return_status OUT NOCOPY VARCHAR2)
131 IS
132
133 BEGIN
134
135 o_sql_string := 'HRI_MDP_SUP_WRKFC_JX_MV';
136 o_return_status := g_return_success;
137
138 EXCEPTION WHEN OTHERS THEN
139 o_return_status := 'EXCEPTION: ' || SUBSTRB(SQLERRM,50);
140 END get_wrkfc_fact_table; -- procedure
141
142 /* function call to get_wrkfc_fact_table procedure */
143 FUNCTION get_wrkfc_fact_table(p_parameter_rec IN HRI_OLTP_EMB_EQTY_ANAL.HRI_EMB_PARAM_REC_TYPE)
144 RETURN VARCHAR2
145 IS
146
147 l_return_status VARCHAR2(1000);
148 l_table_name VARCHAR2(32);
149
150 BEGIN
151 GET_WRKFC_FACT_TABLE(
152 p_parameter_rec => p_parameter_rec
153 ,o_sql_string => l_table_name
154 ,o_return_status => l_return_status);
155
156 IF l_return_status = g_return_success THEN
157 RETURN l_table_name;
158 ELSE
159 RETURN 'unknown_fact_table';
160 END IF;
161
162 EXCEPTION WHEN OTHERS THEN
163 RETURN 'unknown_fact_table';
164
165 END get_wrkfc_fact_table; -- function
166
167
168
169 PROCEDURE get_wrkfc_fact_sql(
170 p_parameter_rec IN HRI_OLTP_EMB_EQTY_ANAL.HRI_EMB_PARAM_REC_TYPE
171 ,o_sql_string OUT NOCOPY VARCHAR2
172 ,o_return_status OUT NOCOPY VARCHAR2)
173 IS
174
175 l_sql_statement VARCHAR2(32000);
176
177 l_wrkfc_fact_table VARCHAR2(32);
178
179 BEGIN
180
181 /* generate the list of fact columns and measures */
182 l_sql_statement :=
183 'SELECT' || g_rtn ||
184 'SUPERVISOR_PERSON_ID' || ' SUPERVISOR_PERSON_ID' || g_rtn ||
185 ',JOB_ID' || ' JOB_ID' || g_rtn ||
186 ',''' || p_parameter_rec.currency_code_to || '''' || ' SAL_CURRENCY_CODE'|| g_rtn ||
187 ',SUM(fact.total_headcount) TOTAL_HEADCOUNT' || g_rtn ||
188 ',MIN(hri_oltp_view_currency.convert_currency_amount(
189 fact.anl_slry_currency,
190 ''' || p_parameter_rec.currency_code_to || ''',
191 to_date(''' || p_parameter_rec.effective_date || ''',''MM/DD/YYYY''),
192 fact.min_anl_slry,
193 '''||g_curr_conv_rate_type||''')) MIN_ANL_SLRY'|| g_rtn ||
194 ',MAX(hri_oltp_view_currency.convert_currency_amount(
195 fact.anl_slry_currency,
196 ''' || p_parameter_rec.currency_code_to || ''',
197 to_date(''' || p_parameter_rec.effective_date || ''',''MM/DD/YYYY''),
198 fact.max_anl_slry,
199 '''||g_curr_conv_rate_type||''')) MAX_ANL_SLRY'|| g_rtn ||
200 ',SUM(hri_oltp_view_currency.convert_currency_amount(
201 fact.anl_slry_currency,
202 ''' || p_parameter_rec.currency_code_to || ''',
203 to_date(''' || p_parameter_rec.effective_date || ''',''MM/DD/YYYY''),
204 fact.total_anl_slry,
205 '''||g_curr_conv_rate_type||''')) TOTAL_ANL_SLRY' || g_rtn ||
206 -- bug 4888622 - check for invalid currency conversions
207 ',SUM(CASE WHEN(hri_oltp_view_currency.convert_currency_amount(
208 fact.anl_slry_currency,
209 ''' || p_parameter_rec.currency_code_to || ''',
210 to_date(''' || p_parameter_rec.effective_date || ''',''MM/DD/YYYY''),
211 fact.total_anl_slry,
212 '''||g_curr_conv_rate_type||''') = -1)
213 THEN 1 END) INVALID_CURR_CONV_IND';
214
215 /* determine which summary to use */
216 l_wrkfc_fact_table := get_wrkfc_fact_table(p_parameter_rec=>p_parameter_rec);
217
218 /* apply parameter context filters to the fact query */
219 l_sql_statement := l_sql_statement || g_rtn ||
220 'FROM '|| l_wrkfc_fact_table|| ' fact' || g_rtn ||
221 'WHERE 1=1' || g_rtn ||
222 'AND to_date(''' || p_parameter_rec.effective_date || ''',''MM/DD/YYYY'')' || g_rtn ||
223 ' BETWEEN fact.effective_start_date AND fact.effective_end_date'|| g_rtn ||
224 'AND fact.supervisor_person_id = ' || p_parameter_rec.supervisor_person_id || g_rtn ||
225 'AND fact.job_id = ' || p_parameter_rec.job_id;
226
227 /* add group by */
228 l_sql_statement := l_sql_statement || g_rtn ||
229 'GROUP BY' || g_rtn ||
230 ' fact.supervisor_person_id' || g_rtn ||
231 ',fact.job_id'|| g_rtn ||
232 ',''' || p_parameter_rec.currency_code_to || '''';
233
234 o_sql_string := l_sql_statement;
235 o_return_status := g_return_success;
236
237 EXCEPTION WHEN OTHERS THEN
238 o_return_status := 'EXCEPTION: ' || SUBSTRB(SQLERRM,50);
239
240 END get_wrkfc_fact_sql;
241
242
243 /* Returns a person_id from a DBI CHO profile option */
244 FUNCTION get_level_1_sup_id RETURN NUMBER
245 IS
246 BEGIN
247 RETURN NVL(fnd_profile.value('HRI_DBI_CHO_NMD_USR') ,-1);
248 END get_level_1_sup_id;
249
250
251 /*
252 **
253 ** Returns a CEO level (using person_id from a profile option) fact SQL query
254 **
255 ** This version is using a HRI_MDP_SUP% materialized view, however this
256 ** may be enhanced in a future version to use a 'CEO' level MV.
257 **
258 **/
259 PROCEDURE get_wrkfc_fact_ceo_sql(
260 p_parameter_rec IN HRI_OLTP_EMB_EQTY_ANAL.HRI_EMB_PARAM_REC_TYPE
261 ,o_sql_string OUT NOCOPY VARCHAR2
262 ,o_return_status OUT NOCOPY VARCHAR2)
263 IS
264
265 l_sql_statement VARCHAR2(32000);
266 l_parameter_rec HRI_OLTP_EMB_EQTY_ANAL.HRI_EMB_PARAM_REC_TYPE;
267
268 BEGIN
269
270 l_parameter_rec := p_parameter_rec;
271
272 /* override the supervisor_person_id with the CEO person id */
273 l_parameter_rec.supervisor_person_id := GET_LEVEL_1_SUP_ID;
274
275 /* call the wrkfc fact generation */
276 GET_WRKFC_FACT_SQL(p_parameter_rec => l_parameter_rec
277 ,o_sql_string => l_sql_statement
278 ,o_return_status => o_return_status);
279
280 o_sql_string := l_sql_statement;
281 o_return_status := g_return_success;
282
283 EXCEPTION WHEN OTHERS THEN
284 o_return_status := 'EXCEPTION: ' || SUBSTRB(SQLERRM,50);
285
286
287 END get_wrkfc_fact_ceo_sql;
288
289 /*
290 ** Formats the report U.I. outer columns
291 **/
292 PROCEDURE get_outer_columns(
293 p_parameter_rec IN HRI_OLTP_EMB_EQTY_ANAL.HRI_EMB_PARAM_REC_TYPE
294 ,o_sql_string OUT NOCOPY VARCHAR2
295 ,o_return_status OUT NOCOPY VARCHAR2)
296 IS
297
298 l_sql_statement VARCHAR2(1000);
299
300 l_decode_label VARCHAR2(500);
301
302 BEGIN
303
304 IF ( p_parameter_rec.logged_in_person_id <> -1
305 AND p_parameter_rec.logged_in_person_id IS NOT NULL
306 AND (p_parameter_rec.logged_in_person_id <> p_parameter_rec.supervisor_person_id)
307 ) THEN
308 -- change the label logic
309 l_decode_label :=
310 'DECODE(supervisor_person_id
311 ,'|| p_parameter_rec.supervisor_person_id || ',' ||'''' ||g_mgrs_org_msg_lbl ||'''
312 ,'|| p_parameter_rec.logged_in_person_id || ',' ||'''' ||g_your_org_msg_lbl ||'''
313 ,'|| '''' ||g_company_msg_lbl ||''') VIEWBY' || g_rtn;
314
315 ELSE
316 l_decode_label :=
317 'DECODE(supervisor_person_id
318 ,'|| p_parameter_rec.supervisor_person_id || ',' ||'''' ||g_your_org_msg_lbl ||'''
319 ,'|| '''' ||g_company_msg_lbl ||''') VIEWBY' || g_rtn;
320
321 END IF;
322
323
324 l_sql_statement :=
325 'SELECT ' || g_rtn ||
326 l_decode_label ||
327 ',qry.supervisor_person_id SUPERVISOR_PERSON_ID
328 ,qry.job_id JOB_ID' || g_rtn ||
329 ',''' || p_parameter_rec.currency_code_to || '''' || ' SAL_CURRENCY_CODE'|| g_rtn ||
330 ',TO_CHAR(qry.total_headcount) TOTAL_HEADCOUNT
331 ,TO_CHAR(DECODE(qry.INVALID_CURR_CONV_IND, 1, -1,qry.max_anl_slry),'''||g_sal_amount_fmt||''') MAX_ANL_SLRY
332 ,TO_CHAR(DECODE(qry.INVALID_CURR_CONV_IND, 1, -1,qry.min_anl_slry),'''||g_sal_amount_fmt||''') MIN_ANL_SLRY
333 ,TO_CHAR(DECODE(qry.INVALID_CURR_CONV_IND, 1, -1,qry.total_anl_slry),'''||g_sal_amount_fmt||''') TOTAL_ANL_SLRY
334 ,TO_CHAR(DECODE(qry.INVALID_CURR_CONV_IND, 1, -1,
335 DECODE(qry.total_headcount, 0, 0
336 ,(qry.total_anl_slry/qry.total_headcount))
337 ),'''||g_sal_amount_fmt||''') WEIGHTED_AVG_SAL' || g_rtn ||
338 'FROM' || g_rtn;
339
340 o_sql_string := l_sql_statement;
341 o_return_status := g_return_success;
342
343 EXCEPTION WHEN OTHERS THEN
344 o_return_status := 'EXCEPTION: ' || SUBSTRB(SQLERRM,50);
345
346 END get_outer_columns;
347
348
349 PROCEDURE get_sql( p_effective_date IN VARCHAR2
350 ,p_job_id IN NUMBER
351 ,p_supervisor_person_id IN NUMBER
352 ,p_logged_in_person_id IN NUMBER DEFAULT FND_GLOBAL.EMPLOYEE_ID
353 ,p_conv_to_currency_code IN VARCHAR2
354 ,p_sal_amount_fmt IN VARCHAR2 DEFAULT 'FM999,999,999,999'
355 ,p_ceo_row IN VARCHAR2 DEFAULT 'N'
356 ,o_sql_string OUT NOCOPY VARCHAR2
357 ,o_return_status OUT NOCOPY VARCHAR2)
358 IS
359
360 -- local variables for dynamic SQL
361 l_sql_statement VARCHAR2(32000);
362
363 l_wrkfc_sup_sql VARCHAR2(32000);
364 l_wrkfc_sup_ceo_sql VARCHAR2(32000);
365 l_wrkfc_sup_login_sql VARCHAR2(32000);
366
367 l_eqty_outer_columns1 VARCHAR2(1000);
368 l_eqty_outer_columns2 VARCHAR2(1000);
369
370 -- parameter structure
371 l_parameter_rec HRI_OLTP_EMB_EQTY_ANAL.HRI_EMB_PARAM_REC_TYPE;
372
373 -- fixed GSCC hardcoded schema issue
374 l_dummy1 VARCHAR2(2000);
375 l_dummy2 VARCHAR2(2000);
376
377
378 BEGIN
379
380 /* check required parameters are passed in */
381 IF (
382 p_effective_date IS NULL
383 OR p_job_id IS NULL
384 OR p_supervisor_person_id IS NULL
385 OR p_conv_to_currency_code IS NULL) THEN
386
387 o_return_status := 'INVALID PARAMETER CONTEXT';
388 ELSE
389
390 /* process parameters into common parameter structure type */
391 l_parameter_rec.effective_date := p_effective_date;
392 l_parameter_rec.job_id := p_job_id;
393 l_parameter_rec.supervisor_person_id := p_supervisor_person_id;
394
395 l_parameter_rec.logged_in_person_id := p_logged_in_person_id;
396
397 l_parameter_rec.currency_code_to := p_conv_to_currency_code;
398 g_sal_amount_fmt := p_sal_amount_fmt;
399
400
401 /* add another report row for the manager's manager */
402 IF ( p_logged_in_person_id <> -1
403 AND p_logged_in_person_id IS NOT NULL
404 AND p_logged_in_person_id <> p_supervisor_person_id) THEN
405
406 /* check if the p_logged_in_person_id is above hiring managers hrcy */
407 IF (check_mgr_is_above_in_hrchy(p_top_mgr_person_id => p_logged_in_person_id
408 ,p_lower_mgr_person_id => p_supervisor_person_id
409 ,p_effective_date => to_date(p_effective_date,'MM/DD/YYYY')
410 )
411 ) THEN
412
413 /* combine the fact SQL to create the report UI */
414 get_outer_columns(p_parameter_rec => l_parameter_rec
415 ,o_sql_string => l_eqty_outer_columns2
416 ,o_return_status => o_return_status);
417
418 -- Temporarily override the supervisor_id to logged in person_id
419 l_parameter_rec.supervisor_person_id := p_logged_in_person_id;
420
421 /* call the wrkfc fact generation for the Mgrs Mgr data*/
422 GET_WRKFC_FACT_SQL(p_parameter_rec => l_parameter_rec
423 ,o_sql_string => l_wrkfc_sup_login_sql
424 ,o_return_status => o_return_status);
425
426 -- reset the overriden the supervisor_id back to supervisor_id
427 l_parameter_rec.supervisor_person_id := p_supervisor_person_id;
428
429 l_sql_statement := l_sql_statement || g_rtn ||
430 l_eqty_outer_columns2 || g_rtn ||
431 '('|| l_wrkfc_sup_login_sql ||') qry';
432
433 END IF;
434
435 END IF;
436
437 /* call the wrkfc fact generation */
438 GET_WRKFC_FACT_SQL(p_parameter_rec => l_parameter_rec
439 ,o_sql_string => l_wrkfc_sup_sql
440 ,o_return_status => o_return_status);
441
442 /* combine the fact SQL to create the report UI */
443 get_outer_columns(p_parameter_rec => l_parameter_rec
444 ,o_sql_string => l_eqty_outer_columns1
445 ,o_return_status => o_return_status);
446
447 IF l_sql_statement IS NULL THEN
448 l_sql_statement := l_eqty_outer_columns1 || g_rtn ||
449 '('|| l_wrkfc_sup_sql ||') qry';
450 ELSE
451 l_sql_statement := l_sql_statement || g_rtn ||
452 'UNION ALL' || g_rtn ||
453 l_eqty_outer_columns1 || g_rtn ||
454 '('|| l_wrkfc_sup_sql ||') qry';
455 END IF;
456
457
458 /* add another report row for the CEO/Company data */
459 IF ( p_ceo_row = 'Y'
460 AND (NVL(fnd_profile.value('HRI_DBI_CHO_NMD_USR'),-1) <> -1)
461 )
462 THEN
463 /* combine the fact SQL to create the report UI */
464 get_outer_columns(p_parameter_rec => l_parameter_rec
465 ,o_sql_string => l_eqty_outer_columns2
466 ,o_return_status => o_return_status);
467
468 /* call the wrkfc fact generation for the CEO data*/
469 GET_WRKFC_FACT_CEO_SQL(p_parameter_rec => l_parameter_rec
470 ,o_sql_string => l_wrkfc_sup_ceo_sql
471 ,o_return_status => o_return_status);
472
473 l_sql_statement := l_sql_statement || g_rtn ||
474 'UNION ALL' || g_rtn ||
475 l_eqty_outer_columns2 || g_rtn ||
476 '('|| l_wrkfc_sup_ceo_sql ||') qry';
477
478 END IF;
479
480 /* return the fully formatted report SQL */
481 o_sql_string := l_sql_statement;
482
483 IF o_return_status IS NULL THEN
484 o_return_status := g_return_success;
485 END IF;
486
487 END IF;
488
489 EXCEPTION WHEN OTHERS THEN
490 o_return_status := 'EXCEPTION: ' || SUBSTRB(SQLERRM,50);
491
492 END get_sql;
493
494
495 END HRI_OLTP_EMB_EQTY_ANAL;