1 PACKAGE BODY hri_apl_dgnstc_wrkfc AS
2 /* $Header: hriadgwf.pkb 120.14 2006/12/05 09:24:37 smohapat noship $ */
3
4 -- Returns a list of continuous periods by salary currency
5 -- where the conversion rate for primary/secondary currency
6 -- is missing
7 FUNCTION get_missing_rates_sql(p_parameter_value IN VARCHAR2)
8 RETURN VARCHAR2 IS
9
10 l_sql_stmt VARCHAR2(32000);
11 l_rate_type_code VARCHAR2(240);
12 l_currency_code VARCHAR2(240);
13
14 BEGIN
15
16 IF (p_parameter_value = 'PRIMARY') THEN
17 l_rate_type_code := fnd_profile.value('BIS_PRIMARY_RATE_TYPE');
18 l_currency_code := fnd_profile.value('BIS_PRIMARY_CURRENCY_CODE');
19 ELSE
20 l_rate_type_code := fnd_profile.value('BIS_SECONDARY_RATE_TYPE');
21 l_currency_code := fnd_profile.value('BIS_SECONDARY_CURRENCY_CODE');
22 END IF;
23
24 l_sql_stmt :=
25 'SELECT -- Missing conversion rates
26 cur.from_currency from_currency
27 ,''' || l_currency_code || ''' to_currency
28 ,cal.id start_date
29 -- End date is one day before next conversion rate exists
30 -- or period end date
31 ,(SELECT NVL(MIN(rate.conversion_date) - 1, :p_end_date)
32 FROM gl_daily_rates rate
33 WHERE rate.to_currency = ''' || l_currency_code || '''
34 AND rate.from_currency = cur.from_currency
35 AND rate.conversion_date > cal.id)
36 end_date
37 ,null col5
38 FROM
39 fii_time_day_v cal
40 ,(SELECT DISTINCT
41 pet.input_currency_code from_currency
42 FROM
43 per_pay_bases ppb
44 ,pay_input_values_f piv
45 ,pay_element_types_f pet
46 WHERE ppb.input_value_id = piv.input_value_id
47 AND piv.element_type_id = pet.element_type_id
48 AND trunc(SYSDATE) BETWEEN piv.effective_start_date
49 AND piv.effective_end_date
50 AND trunc(SYSDATE) BETWEEN pet.effective_start_date
51 AND pet.effective_end_date
52 AND pet.input_currency_code <> ''' || l_currency_code || '''
53 ) cur
54 WHERE cal.id BETWEEN :p_start_date
55 AND :p_end_date
56 -- No conversion rate exists
57 AND NOT EXISTS
58 (SELECT null
59 FROM gl_daily_rates rate
60 WHERE rate.to_currency = ''' || l_currency_code || '''
61 AND rate.from_currency = cur.from_currency
62 AND rate.conversion_type = ''' || l_rate_type_code || '''
63 AND rate.conversion_date = cal.id)
64 -- Filter out consecutive days where no conversion rate exists
65 AND (cal.id = :p_start_date
66 OR EXISTS
67 (SELECT null
68 FROM gl_daily_rates rate
69 WHERE rate.to_currency = ''' || l_currency_code || '''
70 AND rate.from_currency = cur.from_currency
71 AND rate.conversion_type = ''' || l_rate_type_code || '''
72 AND rate.conversion_date = cal.id - 1))
73 ORDER BY 1,3';
74
75 RETURN l_sql_stmt;
76
77 END get_missing_rates_sql;
78
79 -- Returns currently active users whose linked employees are
80 -- not in the supervisor hierarchy
81 FUNCTION get_user_not_in_suph
82 RETURN VARCHAR2 IS
83
84 l_sql_stmt VARCHAR2(32000);
85
86 BEGIN
87
88 l_sql_stmt :=
89 'SELECT
90 usr.user_name
91 ,usr.start_date
92 ,null col3
93 ,null col4
94 ,null col5
95 FROM
96 fnd_user usr
97 ,wf_user_role_assignments waur
98 ,wf_local_roles wlr
99 ,fnd_responsibility resp
100 WHERE resp.responsibility_id = wlr.orig_system_id
101 AND resp.responsibility_key in (''HR_LINE_MANAGER'',''DAILY_HR_INTELLIGENCE'')
102 AND wlr.orig_system = ''FND_RESP''
103 AND usr.user_name = waur.user_name
104 AND waur.role_name = wlr.name
105 AND usr.employee_id IS NOT NULL
106 AND TRUNC(SYSDATE) BETWEEN usr.start_date
107 AND NVL(usr.end_date,hr_general.end_of_time)
108 AND TRUNC(SYSDATE) BETWEEN resp.start_date
109 AND NVL(resp.end_date, hr_general.end_of_time)
110 AND NOT EXISTS
111 (SELECT null
112 FROM hri_cs_suph sup
113 WHERE sup.sub_person_id = usr.employee_id)';
114
115 RETURN l_sql_stmt;
116
117 END get_user_not_in_suph;
118
119 -- Returns currently active users who do not have a linked employee
120 FUNCTION get_user_unassigned
121 RETURN VARCHAR2 IS
122
123 l_sql_stmt VARCHAR2(32000);
124
125 BEGIN
126
127 l_sql_stmt :=
128 'SELECT
129 usr.user_name
130 ,usr.start_date
131 ,null col3
132 ,null col4
133 ,null col5
134 FROM
135 fnd_user usr
136 ,wf_user_role_assignments waur
137 ,wf_local_roles wlr
138 ,fnd_responsibility resp
139 WHERE resp.responsibility_id = wlr.orig_system_id
140 AND resp.responsibility_key IN (''HR_LINE_MANAGER'',''DAILY_HR_INTELLIGENCE'')
141 AND wlr.orig_system = ''FND_RESP''
142 AND usr.user_name = waur.user_name
143 AND waur.role_name = wlr.name
144 AND usr.employee_id IS NULL
145 AND TRUNC(SYSDATE) BETWEEN usr.start_date
146 AND NVL(usr.end_date,hr_general.end_of_time)
147 AND TRUNC(SYSDATE) BETWEEN resp.start_date
148 AND NVL(resp.end_date, hr_general.end_of_time)';
149
150 RETURN l_sql_stmt;
151
152 END get_user_unassigned;
153
154 -- CWKs who do not have a primary assignment with a projected end date
155 FUNCTION get_no_asg_proj_end_date
156 RETURN VARCHAR2 IS
157
158 l_sql_stmt VARCHAR2(32000);
159
160 BEGIN
161
162 l_sql_stmt :=
163 'SELECT /*+ parallel(asg) parallel(per) */ DISTINCT
164 per.full_name
165 ,pop.date_start
166 ,per.npw_number
167 ,bgr.name business_group_name
168 ,NULL col5
169 FROM
170 per_all_assignments_f asg
171 ,per_all_people_f per
172 ,per_periods_of_placement pop
173 ,per_assignment_status_types ast
174 ,hr_all_organization_units_tl bgr
175 WHERE asg.effective_end_date >= :p_start_date
176 AND asg.effective_start_date <= :p_end_date
177 AND per.person_id = asg.person_id
178 AND pop.person_id = asg.person_id
179 AND asg.business_group_id = bgr.organization_id
180 AND bgr.language = USERENV(''LANG'')
181 AND asg.effective_start_date BETWEEN per.effective_start_date
182 AND per.effective_end_date
183 AND asg.assignment_type = ''C''
184 AND asg.assignment_status_type_id = ast.assignment_status_type_id
185 AND ast.per_system_status <> ''TERM_ASSIGN''
186 AND NOT EXISTS
187 (SELECT null
188 FROM per_all_assignments_f asg1
189 WHERE asg1.person_id = asg.person_id
190 AND asg1.primary_flag = ''Y''
191 AND asg1.projected_assignment_end IS NOT NULL)
192 ORDER BY 4, 1, 2';
193
194 RETURN l_sql_stmt;
195
196 END get_no_asg_proj_end_date;
197
198 -- Count of secondary assignments by person type
199 -- Include all assignments in period
200 FUNCTION get_mul_asg_breakup
201 RETURN VARCHAR2 IS
202
203 l_sql_stmt VARCHAR2(32000);
204
205 BEGIN
206
207 l_sql_stmt :=
208 'SELECT /*+ parallel(asg) parallel(per) */
209 ppt.user_person_type
210 ,count(*) total
211 ,NULL col3
212 ,NULL col4
213 ,NULL col5
214 FROM
215 per_all_assignments_f asg
216 ,per_all_people_f per
217 ,per_assignment_status_types ast
218 ,per_person_type_usages_f ppu
219 ,per_person_types ppt
220 WHERE asg.effective_end_date >= :p_start_date
221 AND asg.effective_start_date <= :p_end_date
222 AND per.person_id = asg.person_id
223 AND GREATEST(asg.effective_start_date, :p_start_date)
224 BETWEEN per.effective_start_date AND per.effective_end_date
225 AND asg.primary_flag = ''N''
226 AND asg.assignment_type IN (''E'', ''C'')
227 AND asg.assignment_status_type_id = ast.assignment_status_type_id
228 AND ast.per_system_status NOT IN (''TERM_ASSIGN'')
229 AND GREATEST(asg.effective_start_date, :p_start_date)
230 BETWEEN ppu.effective_start_date AND ppu.effective_end_date
231 AND per.person_id = ppu.person_id
232 AND ppu.person_type_id = ppt.person_type_id
233 AND ppt.system_person_type IN (''EMP'', ''CWK'', ''EX_EMP'', ''EX_CWK'')
234 GROUP BY ppt.user_person_type';
235
236 RETURN l_sql_stmt;
237
238 END get_mul_asg_breakup;
239
240 -- Total headcount by supervisor as of end date
241 FUNCTION get_total_hd
242 RETURN VARCHAR2 IS
243
244 l_sql_stmt VARCHAR2(32000);
245 l_wmv_type_code VARCHAR2(30);
246 l_wmv_type VARCHAR2(240);
247
248 BEGIN
249
250 -- Get the abv type
251 l_wmv_type_code := fnd_profile.value('BIS_WORKFORCE_MEASUREMENT_TYPE');
252
253 -- Default to headcount
254 IF l_wmv_type_code IS NULL THEN
255 l_wmv_type_code := 'HEAD';
256 END IF;
257
258 -- Lookup the type
259 l_wmv_type := hr_bis.bis_decode_lookup('BUDGET_MEASUREMENT_TYPE', l_wmv_type_code);
260
261 l_sql_stmt :=
262 'SELECT /*+ parallel(asg) parallel(suph) */
263 per.full_name person
264 ,''' || REPLACE(l_wmv_type, '''', '''''') || ''' abv_unit
265 ,SUM(hri_bpl_abv.calc_abv
266 (asg.assignment_id
267 ,asg.business_group_id
268 ,''' || l_wmv_type_code || '''
269 ,:p_end_date
270 ,asg.primary_flag
271 ,NULL) ) abv
272 ,suph.sup_level suplevel
273 ,NULL col5
274 FROM
275 per_all_people_f per
276 ,hri_cs_suph suph
277 ,per_all_assignments_f asg
278 ,per_assignment_status_types ast
279 WHERE :p_end_date BETWEEN suph.effective_start_date
280 AND suph.effective_end_date
281 AND asg.assignment_type IN (''E'', ''C'')
282 AND asg.supervisor_id = suph.sub_person_id
283 AND per.person_id = suph.sup_person_id
284 AND asg.assignment_status_type_id = ast.assignment_status_type_id
285 AND ast.per_system_status <> ''TERM_ASSIGN''
286 AND :p_end_date BETWEEN asg.effective_start_date
287 AND asg.effective_end_date
288 AND :p_end_date BETWEEN per.effective_start_date
289 AND per.effective_end_date
290 GROUP BY
291 per.full_name
292 ,suph.sup_level
293 ORDER BY suph.sup_level, 1';
294
295 RETURN l_sql_stmt;
296
297 END get_total_hd;
298
299 -- Count of current users who are assigned HR DBI responsibilities
300 FUNCTION get_user_valid_setup
301 RETURN VARCHAR2 IS
302
303 l_sql_stmt VARCHAR2(32000);
304
305 BEGIN
306
307 l_sql_stmt :=
308 'SELECT count(*)
309 FROM
310 fnd_user usr
311 ,wf_user_role_assignments waur
312 ,wf_local_roles wlr
313 ,fnd_responsibility resp
314 WHERE resp.responsibility_id = wlr.orig_system_id
315 AND resp.responsibility_key IN (''HR_LINE_MANAGER'', ''DAILY_HR_INTELLIGENCE'')
316 AND wlr.orig_system = ''FND_RESP''
317 AND usr.user_name = waur.user_name
318 AND waur.role_name = wlr.name
319 AND TRUNC(SYSDATE) BETWEEN usr.start_date
320 AND NVL(usr.end_date, hr_general.end_of_time)
321 AND TRUNC(SYSDATE) BETWEEN resp.start_date
322 AND NVL(resp.end_date, hr_general.end_of_time)';
323
324 RETURN l_sql_stmt;
325
326 END get_user_valid_setup;
327
328 -- Total salaries by supervisor as at end date
329 FUNCTION get_total_sal
330 RETURN VARCHAR2 IS
331
332 l_sql_stmt VARCHAR2(32000);
333 l_currency_name VARCHAR2(240);
334 l_currency_code VARCHAR2(240);
335 l_rate_type VARCHAR2(80);
336
337 CURSOR currency_csr IS
338 SELECT name meaning
339 FROM fnd_currencies_active_v
340 WHERE currency_code = bis_common_parameters.get_currency_code;
341
342 BEGIN
343
344 -- Get currency name
345 OPEN currency_csr;
346 FETCH currency_csr INTO l_currency_name;
347 CLOSE currency_csr;
348
349 -- Get currency code and rate type
350 l_currency_code := bis_common_parameters.get_currency_code;
351 l_rate_type := bis_common_parameters.get_rate_type;
352
353 l_sql_stmt :=
354 'SELECT
355 per.full_name person
356 ,''' || REPLACE(l_currency_name, '''', '''''') || ''' currency
357 ,SUM(hri_bpl_sal.convert_amount
358 (sal.currency_code
359 ,''' || l_currency_code || '''
360 ,:p_end_date
361 ,sal.salary
362 ,''' || l_rate_type || ''')) total_salary
363 ,sal.sup_level suplevel
364 ,NULL col5
365 FROM
366 (SELECT /*+ use_hash(pro asg) parallel(pro) parallel(asg) parallel(suph)*/
367 suph.sup_person_id
368 ,CASE WHEN ppb.pay_annualization_factor IS NULL AND
369 ppb.pay_basis = ''PERIOD''
370 THEN pro.proposed_salary_n * hri_bpl_sal.get_perd_annualization_factor
371 (asg.assignment_id, pro.change_date)
372 ELSE pro.proposed_salary_n * ppb.pay_annualization_factor
373 END salary
374 ,pro.change_date change_date
375 ,NVL(pro.pay_proposal_id, -1) pay_proposal_id
376 ,NVL(pet.input_currency_code, ''NA_EDW'') currency_code
377 ,asg.assignment_id
378 ,suph.sup_level
379 FROM
380 per_all_assignments_f asg
381 ,per_assignment_status_types ast
382 ,per_pay_bases ppb
383 ,per_pay_proposals pro
384 ,pay_input_values_f piv
385 ,pay_element_types_f pet
386 ,hri_cs_suph suph
387 WHERE pro.approved = ''Y''
388 AND :p_end_date BETWEEN suph.effective_start_date
389 AND suph.effective_end_date
390 AND asg.supervisor_id = suph.sub_person_id
391 AND :p_end_date BETWEEN asg.effective_start_date
392 AND asg.effective_end_date
393 AND asg.assignment_type = ''E''
394 AND asg.assignment_status_type_id = ast.assignment_status_type_id
395 AND ast.per_system_status <> ''TERM_ASSIGN''
396 AND asg.assignment_id = pro.assignment_id
397 AND asg.pay_basis_id = ppb.pay_basis_id
398 AND ppb.input_value_id = piv.input_value_id
399 AND piv.element_type_id = pet.element_type_id
400 AND pro.change_date BETWEEN piv.effective_start_date
401 AND piv.effective_end_date
402 AND pro.change_date BETWEEN pet.effective_start_date
403 AND pet.effective_end_date
404 AND pro.change_date =
405 (SELECT max(pro2.change_date)
406 FROM per_pay_proposals pro2
407 WHERE pro2.assignment_id = pro.assignment_id
408 AND pro2.change_date <= :p_end_date
409 AND pro2.approved = ''Y'' )
410 ) sal
411 ,per_all_people_f per
412 WHERE per.person_id = sal.sup_person_id
413 AND :p_end_date BETWEEN per.effective_start_date
414 AND per.effective_end_date
415 GROUP BY
416 per.full_name
417 ,sal.sup_level
418 ORDER BY sal.sup_level, 1';
419
420 RETURN l_sql_stmt;
421
422 END get_total_sal;
423
424
425 -- Employees who have no reviews in the period
426 FUNCTION get_no_review
427 RETURN VARCHAR2 IS
428
429 l_sql_stmt VARCHAR2(32000);
430
431 BEGIN
432
433 l_sql_stmt :=
434 'SELECT
435 per.full_name
436 ,norev.effective_start_date
437 ,norev.effective_end_date
438 ,per.employee_number
439 ,bgr.name business_group_name
440 FROM
441 per_all_people_f per
442 ,hr_all_organization_units_tl bgr
443 ,(SELECT /*+ parallel(pos) parallel(ppr) parallel(papp) */
444 pos.person_id
445 ,pos.date_start effective_start_date
446 ,least(NVL(min(ppr.review_date - 1)
447 ,NVL(pos.actual_termination_date
448 ,hr_general.end_of_time))
449 ,NVL(min(decode(papp.open, ''N'', papp.appraisal_date - 1, null))
450 , NVL(pos.actual_termination_date
451 ,hr_general.end_of_time))) effective_end_date
452 FROM
453 per_periods_of_service pos
454 ,per_performance_reviews ppr
455 ,per_appraisals papp
456 WHERE NVL(pos.actual_termination_date, hr_general.end_of_time) >= :p_start_date
457 AND pos.date_start <= :p_end_date
458 AND pos.person_id = ppr.person_id (+)
459 AND pos.person_id = papp.appraisee_person_id(+)
460 GROUP BY
461 pos.person_id
462 ,pos.date_start
463 ,pos.actual_termination_date
464 HAVING NVL(min(ppr.review_date), hr_general.end_of_time) >
465 greatest(pos.date_start, :p_start_date)
466 AND NVL(min(decode(papp.open, ''N'', papp.appraisal_date, null))
467 ,hr_general.end_of_time) >
468 greatest(pos.date_start, :p_start_date)
469 ) norev
470 WHERE norev.person_id = per.person_id
471 AND per.business_group_id = bgr.organization_id
472 AND bgr.language = USERENV(''LANG'')
473 AND norev.effective_start_date BETWEEN per.effective_start_date
474 AND per.effective_end_date
475 AND norev.effective_end_date >= :p_end_date
476 ORDER BY 5, 1, 2';
477
478 RETURN l_sql_stmt;
479
480 END get_no_review;
481
482 -- Total assignments as of period end
483 FUNCTION get_total_asg
484 RETURN VARCHAR2 IS
485
486 l_sql_stmt VARCHAR2(32000);
487
488 BEGIN
489
490 l_sql_stmt :=
491 'SELECT /*+ parallel(asg) */
492 count(*) total
493 FROM
494 per_all_assignments_f asg
495 ,per_assignment_status_types ast
496 WHERE :p_end_date BETWEEN asg.effective_start_date
497 AND asg.effective_end_date
498 AND asg.assignment_type IN (''E'', ''C'')
499 AND asg.assignment_status_type_id = ast.assignment_status_type_id
500 AND ast.per_system_status <> ''TERM_ASSIGN''';
501
502 RETURN l_sql_stmt;
503
504 END get_total_asg;
505
506 -- Total assignments grouped by assignment type
507 FUNCTION get_total_asg_by_asgtype
508 RETURN VARCHAR2 IS
509
510 l_sql_stmt VARCHAR2(32000);
511
512 BEGIN
513
514 l_sql_stmt :=
515 'SELECT /*+ parallel(asg) */
516 hr_bis.bis_decode_lookup(''EMP_APL'', asg.assignment_type) meaning
517 ,count(*) total
518 ,null col3
519 ,null col4
520 ,null col5
521 FROM
522 per_all_assignments_f asg
523 ,per_assignment_status_types ast
524 WHERE :p_end_date BETWEEN asg.effective_start_date
525 AND asg.effective_end_date
526 AND asg.assignment_type IN (''E'', ''C'')
527 AND asg.assignment_status_type_id = ast.assignment_status_type_id
528 AND ast.per_system_status <> ''TERM_ASSIGN''
529 GROUP BY
530 asg.assignment_type';
531
532 RETURN l_sql_stmt;
533
534 END get_total_asg_by_asgtype;
535
536 -- Assignments with no supervisor within period
537 FUNCTION get_no_sprvsr
538 RETURN VARCHAR2 IS
539
540 l_sql_stmt VARCHAR2(32000);
541
542 BEGIN
543
544 l_sql_stmt :=
545 'SELECT /*+ parallel(asg) parallel(per) */
546 per.full_name person
547 ,asg.effective_start_date start_date
548 ,asg.effective_end_date end_date
549 ,asg.assignment_number
550 ,bgr.name business_group_name
551 FROM
552 per_all_assignments_f asg
553 ,per_all_people_f per
554 ,hr_all_organization_units_tl bgr
555 WHERE asg.effective_end_date >= :p_start_date
556 AND asg.effective_start_date <= :p_end_date
557 AND asg.assignment_type IN (''E'', ''C'')
558 AND asg.supervisor_id IS NULL
559 AND asg.person_id = per.person_id
560 AND asg.business_group_id = bgr.organization_id
561 AND bgr.language = USERENV(''LANG'')
562 AND asg.effective_start_date BETWEEN per.effective_start_date
563 AND per.effective_end_date
564 ORDER BY 5, 1, 2';
565
566 RETURN l_sql_stmt;
567
568 END get_no_sprvsr;
569
570 -- Secondary assignments in period
571 FUNCTION get_mul_asg
572 RETURN VARCHAR2 IS
573
574 l_sql_stmt VARCHAR2(32000);
575
576 BEGIN
577
578 l_sql_stmt :=
579 'SELECT
580 inner_q.person person
581 ,min(inner_q.start_date) start_date
582 ,max(inner_q.end_date) end_date
583 ,inner_q.assignment_number assignment_number
584 ,null col4
585 ,null col5
586 FROM
587 (SELECT /*+ parallel(asg) parallel(per) */
588 per.full_name person
589 ,asg.effective_start_date start_date
590 ,asg.effective_end_date end_date
591 ,asg.assignment_number assignment_number
592 FROM
593 per_all_assignments_f asg
594 ,per_all_people_f per
595 ,per_assignment_status_types ast
596 WHERE asg.effective_end_date >= :p_start_date
597 AND asg.effective_start_date <= :p_end_date
598 AND per.person_id = asg.person_id
599 AND asg.effective_start_date BETWEEN per.effective_start_date
600 AND per.effective_end_date
601 AND asg.primary_flag = ''N''
602 AND asg.assignment_type IN (''E'', ''C'')
603 AND asg.assignment_status_type_id = ast.assignment_status_type_id
604 AND ast.per_system_status <> ''TERM_ASSIGN''
605 ) inner_q
606 GROUP BY
607 inner_q.assignment_number
608 ,inner_q.person
609 ORDER BY 1, 2';
610
611 RETURN l_sql_stmt;
612
613 END get_mul_asg;
614
615 -- Terminated supervisors in period
616 FUNCTION get_term_sprvsr
617 RETURN VARCHAR2 IS
618
619 l_sql_stmt VARCHAR2(32000);
620
621 BEGIN
622
623 l_sql_stmt :=
624 'SELECT /*+ parallel(asg) parallel(per) parallel(sup_service) */
625 per.full_name person
626 ,greatest(sup_service.actual_termination_date + 1
627 ,asg.effective_start_date) termination_date
628 ,asg.effective_end_date end_date
629 ,asg.assignment_number
630 ,bgr.name business_group_name
631 FROM
632 per_all_assignments_f asg
633 ,per_periods_of_service sup_service
634 ,per_all_people_f per
635 ,per_assignment_status_types ast
636 ,hr_all_organization_units_tl bgr
637 WHERE asg.assignment_type IN (''E'', ''C'')
638 AND asg.supervisor_id = sup_service.person_id
639 AND sup_service.actual_termination_date < asg.effective_end_date
640 AND asg.person_id = per.person_id
641 AND asg.effective_start_date BETWEEN per.effective_start_date
642 AND per.effective_end_date
643 AND asg.effective_end_date >= :p_start_date
644 AND sup_service.actual_termination_date + 1 <= :p_end_date
645 AND asg.business_group_id = bgr.organization_id
646 AND bgr.language = USERENV(''LANG'')
647 AND ast.assignment_status_type_id = asg.assignment_status_type_id
648 AND ast.per_system_status <> ''TERM_ASSIGN''
649 ORDER BY 5, 1, 2';
650
651 RETURN l_sql_stmt;
652
653 END get_term_sprvsr;
654
655 -- Employees with no salary
656 FUNCTION get_no_sal
657 RETURN VARCHAR2 IS
658
659 l_sql_stmt VARCHAR2(32000);
660
661 BEGIN
662
663 l_sql_stmt :=
664 'SELECT /*+ INDEX(per) INDEX(bgr) */
665 per.full_name person_name
666 ,no_sal.asg_start effective_start_date
667 ,NVL(no_sal.earliest_salary, no_sal.asg_end)
668 effective_end_date
669 ,no_sal.assignment_number
670 ,bgr.name business_group_name
671 FROM
672 per_all_people_f per
673 ,hr_all_organization_units_tl bgr
674 ,(SELECT /*+ NO_MERGE */
675 asg.assignment_id
676 ,asg.business_group_id
677 ,asg.person_id
678 ,asg.assignment_number
679 ,MIN(ppp.change_date) earliest_salary
680 ,MIN(asg.effective_start_date) asg_start
681 ,pos.actual_termination_date asg_end
682 FROM
683 per_periods_of_service pos
684 ,per_all_assignments_f asg
685 ,per_pay_proposals ppp
686 WHERE asg.assignment_type = ''E''
687 AND pos.period_of_service_id = asg.period_of_service_id
688 AND asg.effective_start_date >= pos.date_start
689 AND asg.assignment_id = ppp.assignment_id (+)
690 AND asg.effective_start_date <= ppp.change_date (+)
691 AND ppp.approved (+) = ''Y''
692 AND pos.date_start <= :p_end_date
693 AND NVL(pos.actual_termination_date, :p_start_date) >= :p_start_date
694 GROUP BY
695 asg.assignment_id
696 ,asg.business_group_id
697 ,asg.person_id
698 ,asg.assignment_number
699 ,pos.actual_termination_date
700 HAVING (MIN(ppp.change_date) > MIN(asg.effective_start_date) OR
701 MIN(ppp.change_date) IS NULL)) no_sal
702 WHERE no_sal.person_id = per.person_id
703 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date
704 AND per.effective_end_date
705 AND no_sal.business_group_id = bgr.organization_id
706 AND bgr.language = USERENV(''LANG'')
707 ORDER BY 5, 1, 2';
708
709 RETURN l_sql_stmt;
710
711 END get_no_sal;
712
713 -- People with headcount or fte > 1
714 FUNCTION get_dbl_cnt_abv
715 RETURN VARCHAR2 IS
716
717 l_sql_stmt VARCHAR2(32000);
718
719 BEGIN
720
721 l_sql_stmt :=
722 'SELECT /*+ INDEX(per per_people_f_pk) */
723 per.full_name
724 ,MIN(dbl_cnt.effective_date) START_DATE
725 ,MAX(dbl_cnt.effective_date) END_DATE
726 ,NVL(employee_number, npw_number) emp_or_cwk_number
727 ,bgr.name business_group_name
728 --,per.person_id
729 FROM
730 per_all_people_f per
731 ,hr_all_organization_units_tl bgr
732 ,(SELECT
733 evt.person_id
734 ,SUM(evt.headcount) headcount
735 ,SUM(evt.fte) fte
736 ,tab.effective_date
737 FROM
738 hri_mb_asgn_events_ct evt
739 ,(SELECT /*+ NO_MERGE */ DISTINCT
740 person_id
741 ,CASE WHEN effective_change_date < :p_start_date
742 THEN :p_start_date
743 ELSE effective_change_date
744 END effective_date
745 FROM hri_mb_asgn_events_ct evt_date
746 WHERE
747 (:p_start_date BETWEEN effective_change_date AND effective_change_end_date
748 OR (effective_change_date BETWEEN :p_start_date AND :p_end_date AND
749 (worker_hire_ind = 1
750 OR post_hire_asgn_start_ind = 1
751 OR fte_gain_ind = 1
752 OR fte_loss_ind = 1
753 OR headcount_gain_ind = 1
754 OR headcount_loss_ind = 1)))
755 AND worker_term_ind = 0
756 AND pre_sprtn_asgn_end_ind = 0
757 UNION ALL
758 SELECT DISTINCT
759 person_id
760 ,:p_end_date
761 FROM
762 hri_mb_asgn_events_ct
763 WHERE :p_end_date > effective_change_date
764 AND :p_end_date <= effective_change_end_date
765 AND worker_term_ind = 0
766 AND pre_sprtn_asgn_end_ind = 0
767 ) tab
768 WHERE evt.person_id = tab.person_id
769 AND tab.effective_date BETWEEN evt.effective_change_date
770 AND evt.effective_change_end_date
771 GROUP BY
772 evt.person_id
773 ,tab.effective_date
774 HAVING
775 SUM(evt.headcount) > 1 OR SUM(evt.fte) > 1
776 ) dbl_cnt
777 WHERE dbl_cnt.person_id = per.person_id
778 AND per.business_group_id = bgr.organization_id
779 AND bgr.language = USERENV(''LANG'')
780 AND TRUNC(SYSDATE) BETWEEN per.effective_start_date
781 AND per.effective_end_date
782 GROUP BY
783 bgr.name
784 ,per.full_name
785 ,employee_number
786 ,npw_number
787 ,per.person_id
788 ORDER BY 5, 1, 2';
789
790 RETURN l_sql_stmt;
791
792 END get_dbl_cnt_abv;
793
794 -- Obsolete
795 FUNCTION get_smlt_abv
796 RETURN VARCHAR2 IS
797
798 l_sql_stmt VARCHAR2(32000);
799 l_abv_unit VARCHAR2(240);
800
801 BEGIN
802
803 -- Get the abv type
804 l_abv_unit := fnd_profile.value('BIS_WORKFORCE_MEASUREMENT_TYPE');
805
806 -- Default to headcount
807 IF l_abv_unit IS NULL THEN
808 l_abv_unit := 'HEAD';
809 END IF;
810
811 l_sql_stmt :=
812 'SELECT /*+ parallel(per) */ DISTINCT
813 per.full_name person
814 ,abv.effective_start_date start_date
815 ,least(abv.effective_end_date
816 ,abv2.effective_end_date) end_date
817 ,null col3
818 ,null col4
819 ,null col5
820 FROM
821 per_assignment_budget_values_f abv
822 ,per_all_assignments_f asg
823 ,per_all_people_f per
824 ,per_all_assignments_f asg2
825 ,per_assignment_budget_values_f abv2
826 WHERE NOT (least(abv.effective_end_date
827 ,abv2.effective_end_date)<:p_start_date
828 OR abv.effective_start_date>:p_end_date)
829 AND abv.unit = ''' || l_abv_unit || '''
830 AND asg.assignment_id = abv.assignment_id
831 AND abv.effective_start_date BETWEEN asg.effective_start_date
832 AND asg.effective_end_date
833 AND asg.assignment_type IN (''E'', ''C'')
834 AND per.person_id = asg.person_id
835 AND asg.effective_start_date BETWEEN per.effective_start_date
836 AND per.effective_end_date
837 AND asg2.assignment_type IN (''E'', ''C'')
838 AND abv2.unit = ''' || l_abv_unit || '''
839 AND asg2.assignment_id = abv2.assignment_id
840 AND abv2.effective_start_date BETWEEN asg2.effective_start_date
841 AND asg2.effective_end_date
842 AND asg.person_id = asg2.person_id
843 AND asg.assignment_id <> asg2.assignment_id
844 AND abv.effective_start_date BETWEEN abv2.effective_start_date
845 AND abv2.effective_end_date
846 ORDER BY 1, 2, 3';
847
848 RETURN l_sql_stmt;
849
850 END get_smlt_abv;
851
852 -- Obsolete
853 FUNCTION get_no_abv
854 RETURN VARCHAR2 IS
855
856 l_sql_stmt VARCHAR2(32000);
857 l_abv_unit VARCHAR2(240);
858
859 BEGIN
860
861 -- Get the abv type
862 l_abv_unit := fnd_profile.value('BIS_WORKFORCE_MEASUREMENT_TYPE');
863
864 -- Default to headcount
865 IF l_abv_unit IS NULL THEN
866 l_abv_unit := 'HEAD';
867 END IF;
868
869 l_sql_stmt :=
870 'SELECT /*+ parallel(per)*/
871 per.full_name person
872 ,noabv_duration.noabv_starts
873 ,noabv_duration.noabv_ends
874 ,noabv_duration.assignment_number
875 ,null col3
876 ,null col4
877 FROM
878 (SELECT /*+ parallel(asg) parallel(abv) */
879 asg.person_id
880 ,asg.assignment_number
881 ,CASE WHEN LEAD(abv.effective_start_date, 1) OVER
882 (PARTITION BY abv.assignment_id
883 ORDER BY abv.effective_start_date) IS NULL
884 THEN abv.effective_end_date+1
885 WHEN LEAD(abv.effective_start_date, 1) OVER
886 (PARTITION BY abv.assignment_id
887 ORDER BY abv.effective_start_date) <>
888 abv.effective_end_date + 1
889 THEN abv.effective_end_date+1
890 END noabv_starts
891 ,CASE WHEN LEAD(abv.effective_start_date, 1) OVER
892 (PARTITION BY abv.assignment_id
893 ORDER BY abv.effective_start_date) <>
894 abv.effective_end_date + 1
895 THEN LEAD(abv.effective_start_date, 1) OVER
896 (PARTITION BY abv.assignment_id
897 ORDER BY abv.effective_start_date)-1
898 WHEN LEAD(abv.effective_start_date, 1) OVER
899 (PARTITION BY abv.assignment_id
900 ORDER BY abv.effective_start_date) IS NULL
901 AND abv.effective_end_date =
902 (SELECT max(asg2.effective_end_date)
903 FROM per_all_assignments_f asg2
904 WHERE asg2.assignment_id = abv.assignment_id)
905 THEN null
906 ELSE (SELECT max(asg2.effective_end_date)
907 FROM per_all_assignments_f asg2
908 WHERE asg2.assignment_id = abv.assignment_id)
909 END noabv_ends
910 FROM
911 per_assignment_budget_values_f abv
912 ,per_all_assignments_f asg
913 WHERE abv.unit = ''' || l_abv_unit || '''
914 AND abv.assignment_id = asg.assignment_id
915 AND abv.effective_start_date BETWEEN asg.effective_start_date
916 AND asg.effective_end_date
917 UNION
918 SELECT
919 asg_start.person_id
920 ,asg_start.assignment_number
921 ,asg_start.noabv_starts
922 ,NVL(abv_start.start_date-1, asg_start.no_abv_ends)
923 FROM
924 (SELECT /*+ parallel(assg) */
925 assg.person_id
926 ,assg.assignment_id
927 ,assg.assignment_number
928 ,min(assg.effective_start_date) noabv_starts
929 ,max(assg.effective_end_date) no_abv_ends
930 FROM
931 per_all_assignments_f assg
932 GROUP BY
933 assg.assignment_id
934 ,assg.person_id
935 ,assg.assignment_number
936 ) asg_start
937 ,(SELECT /*+ parallel(abv) */
938 abv.assignment_id
939 ,min(abv.effective_start_date) start_date
940 FROM per_assignment_budget_values_f abv
941 WHERE abv.unit = ''' || l_abv_unit || '''
942 GROUP BY abv.assignment_id
943 ) abv_start
944 WHERE asg_start.assignment_id = abv_start.assignment_id(+)
945 AND (asg_start.noabv_starts < abv_start.start_date
946 OR abv_start.start_date IS NULL)
947 ) noabv_duration
948 ,per_all_people_f per
949 WHERE per.person_id = noabv_duration.person_id
950 AND noabv_duration.noabv_starts BETWEEN per.effective_start_date
951 AND per.effective_end_date
952 AND NOT (noabv_duration.noabv_ends<:p_start_date
953 OR noabv_duration.noabv_starts>:p_end_date)
954 ORDER BY 1, 2, 3';
955
956 RETURN l_sql_stmt;
957
958 END get_no_abv;
959
960 -- Employee separations with no leaving reason
961 FUNCTION get_no_term_rsn
962 RETURN VARCHAR2 IS
963
964 l_sql_stmt VARCHAR2(32000);
965
966 BEGIN
967
968 l_sql_stmt :=
969 'SELECT /*+ parallel(pps) parallel(per) parallel(assg) */
970 per.full_name person
971 ,pps.actual_termination_date termination_date
972 ,per.employee_number
973 ,bgr.name business_group_name
974 ,null col5
975 FROM
976 per_periods_of_service pps
977 ,per_all_people_f per
978 ,per_all_assignments_f assg
979 ,hr_all_organization_units_tl bgr
980 WHERE pps.actual_termination_date <= :p_end_date
981 AND pps.actual_termination_date + 1 >= :p_start_date
982 AND pps.person_id = per.person_id
983 AND pps.actual_termination_date+1 BETWEEN per.effective_start_date
984 AND per.effective_end_date
985 AND pps.leaving_reason IS NULL
986 AND per.person_id = assg.person_id
987 AND assg.effective_end_date = pps.actual_termination_date
988 AND assg.assignment_type IN (''E'', ''C'')
989 AND assg.primary_flag = ''Y''
990 AND assg.business_group_id = bgr.organization_id
991 AND bgr.language = USERENV(''LANG'')
992 ORDER BY 4, 1, 2';
993
994 RETURN l_sql_stmt;
995
996 END get_no_term_rsn;
997
998 -- Diagnose supervisor loops
999 FUNCTION get_sup_loop_details
1000 RETURN VARCHAR2 IS
1001
1002 l_sql_stmt VARCHAR2(32000);
1003
1004 BEGIN
1005
1006 l_sql_stmt :=
1007 'set serveroutput on
1008 DECLARE
1009
1010 TYPE sup_cache_tab_type IS TABLE OF VARCHAR2(30)
1011 INDEX BY BINARY_INTEGER;
1012
1013 CURSOR sup_csr(p_psn_id NUMBER,
1014 p_date DATE) IS
1015 SELECT
1016 sub.full_name sub_person_name
1017 ,NVL(sub.employee_number, sub.npw_number)
1018 sub_emp_cwk_number
1019 ,sup.full_name sup_person_name
1020 ,NVL(sup.employee_number, sup.npw_number)
1021 sup_emp_cwk_number
1022 ,assg.supervisor_id supervisor_id
1023 FROM
1024 per_all_assignments_f assg
1025 ,per_assignment_status_types ast
1026 ,per_people_x sup
1027 ,per_people_x sub
1028 WHERE assg.person_id = p_psn_id
1029 AND p_date BETWEEN assg.effective_start_date
1030 AND assg.effective_end_date
1031 AND assg.assignment_status_type_id = ast.assignment_status_type_id
1032 AND ast.per_system_status <> ''TERM_ASSIGN''
1033 AND assg.primary_flag = ''Y''
1034 AND assg.assignment_type IN (''E'',''C'')
1035 AND assg.person_id = sub.person_id
1036 AND assg.supervisor_id = sup.person_id;
1037
1038 l_sup_cache sup_cache_tab_type;
1039 exit_loop BOOLEAN;
1040 l_person_id NUMBER;
1041 l_effective_date DATE;
1042 l_supervisor_id NUMBER;
1043 l_person_name VARCHAR2(240);
1044 l_person_number VARCHAR2(240);
1045 l_supervisor_name VARCHAR2(240);
1046 l_supervisor_number VARCHAR2(240);
1047
1048 BEGIN
1049
1050 -- Loop variable - will be set to true when a loop is encountered
1051 -- or when the end of the supervisor chains is reached
1052 exit_loop := FALSE;
1053
1054 -- Person to sample manager of
1055 l_person_id := :person_id;
1056 l_effective_date := :effective_date;
1057
1058 -- Update cache for encountering this person
1059 l_sup_cache(l_person_id) := ''Y'';
1060
1061 -- Loop through supervisor levels
1062 WHILE NOT exit_loop LOOP
1063
1064 -- Fetch supervisor details for current person
1065 OPEN sup_csr(l_person_id, l_effective_date);
1066 FETCH sup_csr INTO
1067 l_person_name,
1068 l_person_number,
1069 l_supervisor_name,
1070 l_supervisor_number,
1071 l_supervisor_id;
1072
1073 -- If no rows are returned then exit the loop
1074 IF (sup_csr%NOTFOUND OR sup_csr%NOTFOUND IS NULL) THEN
1075 l_person_id := NULL;
1076
1077 -- Otherwise set next supervisor id and output current link
1078 ELSE
1079 l_person_id := l_supervisor_id;
1080
1081 -- Output link in supervisor chain
1082 dbms_output.put_line(l_person_name || '' ('' ||
1083 l_person_number || '') -> '' ||
1084 l_supervisor_name || '' ('' ||
1085 l_supervisor_number || '')'');
1086 END IF;
1087
1088 CLOSE sup_csr;
1089
1090 -- Trap no data found when cache is tested for a new person
1091 BEGIN
1092 -- Exit loop if no supervisor or a repeated supervisor
1093 IF (l_person_id IS NULL) THEN
1094 exit_loop := TRUE;
1095 ELSIF (l_sup_cache(l_person_id) = ''Y'') THEN
1096 exit_loop := TRUE;
1097 ELSE
1098 RAISE NO_DATA_FOUND;
1099 END IF;
1100 EXCEPTION WHEN OTHERS THEN
1101 l_sup_cache(l_person_id) := ''Y'';
1102 END;
1103
1104 END LOOP;
1105
1106 END;
1107 /';
1108
1109
1110
1111 RETURN l_sql_stmt;
1112
1113 END get_sup_loop_details;
1114
1115 -- Find Incomplete Request Sets
1116 FUNCTION get_incomplete_req_sets
1117 RETURN VARCHAR2 IS
1118
1119 l_sql_stmt VARCHAR2(32000);
1120
1121 BEGIN
1122
1123 l_sql_stmt :=
1124 'SELECT user_request_set_name,
1125 description,
1126 null col3,
1127 null col4,
1128 null col5
1129 FROM fnd_request_sets_vl
1130 WHERE request_set_name IN (
1131 SELECT request_set_name
1132 FROM bis_request_set_objects obj
1133 WHERE obj.object_name IN (SELECT object_name
1134 FROM bis_obj_properties
1135 WHERE object_name like ''HRI%''
1136 AND object_type = ''PAGE''
1137 AND implementation_flag = ''Y''
1138 AND fnd_profile.value(''HRI_IMPL_DBI'') = ''Y''
1139 AND fnd_profile.value(''HRI_IMPL_OBIEE'')= ''Y''
1140 UNION ALL
1141 SELECT object_name
1142 FROM bis_obj_properties
1143 WHERE object_name like ''HRI%SUBJECTAREA''
1144 AND object_type = ''REPORT''
1145 AND implementation_flag = ''Y''
1146 AND fnd_profile.value(''HRI_IMPL_OBIEE'')= ''Y'')
1147 GROUP BY obj.request_set_name
1148 HAVING COUNT(*) < (SELECT COUNT(*)
1149 FROM bis_obj_properties
1150 WHERE implementation_flag = ''Y''
1151 AND object_name IN (SELECT object_name
1152 FROM bis_obj_properties
1153 WHERE object_name like ''HRI%''
1154 AND object_type = ''PAGE''
1155 AND implementation_flag = ''Y''
1156 AND fnd_profile.value(''HRI_IMPL_DBI'') = ''Y''
1157 AND fnd_profile.value(''HRI_IMPL_OBIEE'')= ''Y''
1158 UNION ALL
1159 SELECT object_name
1160 FROM bis_obj_properties
1161 WHERE object_name like ''HRI%SUBJECTAREA''
1162 AND object_type = ''REPORT''
1163 AND implementation_flag = ''Y''
1164 AND fnd_profile.value(''HRI_IMPL_OBIEE'')= ''Y'')
1165 )
1166 )'
1167
1168 ;
1169
1170 RETURN l_sql_stmt;
1171
1172 END get_incomplete_req_sets;
1173
1174
1175 -- Find Vacancies without Managers
1176 FUNCTION get_vac_wtht_mngrs
1177 RETURN VARCHAR2 IS
1178
1179 l_sql_stmt VARCHAR2(32000);
1180
1181 BEGIN
1182
1183 l_sql_stmt :=
1184 'SELECT vac.name
1185 ,vac.description
1186 ,vac.date_from
1187 ,vac.date_to
1188 ,null col5
1189 FROM per_all_vacancies vac,
1190 per_requisitions prq
1191 WHERE vac.requisition_id = prq.requisition_id
1192 AND vac.manager_id IS NULL
1193 AND DECODE(fnd_profile.value(''HRI_REC_USE_PUI_MGR_KEYS''),''Y'',vac.recruiter_id) IS NULL
1194 AND DECODE(fnd_profile.value(''HRI_REC_USE_PUI_MGR_KEYS''),''Y'',prq.person_id) IS NULL
1195 AND hri_bpl_ccmgr.get_ccmgr_id(vac.organization_id) = -1
1196 AND hri_bpl_ccmgr.get_ccmgr_id(vac.business_group_id) = -1
1197 AND vac.date_from >= :p_start_date
1198 AND NVL(vac.date_to, trunc(sysdate)) <= :p_end_date
1199 ORDER BY 1';
1200
1201 RETURN l_sql_stmt;
1202
1203 END get_vac_wtht_mngrs;
1204
1205 -- Find Applicants not associated with any vacancy
1206 FUNCTION get_applcnt_wtht_vac
1207 RETURN VARCHAR2 IS
1208
1209 l_sql_stmt VARCHAR2(32000);
1210
1211 BEGIN
1212
1213 l_sql_stmt :=
1214 'SELECT /*+ parallel(asg) parallel(per) */
1215 per.full_name person
1216 ,asg.effective_start_date start_date
1217 ,asg.effective_end_date end_date
1218 ,bgr.name business_group_name
1219 ,null col5
1220 FROM per_all_assignments_f asg
1221 ,per_all_people_f per
1222 ,hr_all_organization_units_tl bgr
1223 ,per_applications apl
1224 WHERE asg.assignment_type = ''A''
1225 AND asg.vacancy_id IS NULL
1226 AND asg.person_id = per.person_id
1227 AND asg.business_group_id = bgr.organization_id
1228 AND bgr.language = USERENV(''LANG'')
1229 AND asg.effective_start_date BETWEEN per.effective_start_date
1230 AND per.effective_end_date
1231 AND asg.application_id = apl.application_id
1232 AND apl.date_received >= :p_start_date
1233 AND NVL(apl.date_end, trunc(sysdate)) <= :p_end_date
1234 ORDER BY 4, 1, 2';
1235
1236 RETURN l_sql_stmt;
1237
1238 END get_applcnt_wtht_vac;
1239
1240
1241 -- Find USERS who have the ability to access PRODUCT
1242 -- through the Line Manager responsibility but are not assigned to any user
1243
1244 FUNCTION get_user_linemgr_info
1245 RETURN VARCHAR2 IS
1246
1247 l_sql_stmt VARCHAR2(32000);
1248
1249 BEGIN
1250
1251 l_sql_stmt :=
1252 'SELECT usr.user_name
1253 ,null col2
1254 ,null col3
1255 ,null col4
1256 ,null col5
1257 FROM
1258 fnd_user usr
1259 ,wf_user_role_assignments waur
1260 ,wf_local_roles wlr
1261 ,fnd_responsibility resp
1262 WHERE resp.responsibility_id = wlr.orig_system_id
1263 AND resp.responsibility_key = ''HRI_OBI_ALL_MGRH''
1264 AND wlr.orig_system = ''FND_RESP''
1265 AND usr.user_name = waur.user_name
1266 AND waur.role_name = wlr.name
1267 AND TRUNC(SYSDATE) BETWEEN usr.start_date
1268 AND NVL(usr.end_date, hr_general.end_of_time)
1269 AND TRUNC(SYSDATE) BETWEEN resp.start_date
1270 AND NVL(resp.end_date, hr_general.end_of_time)
1271 AND usr.employee_id IS NULL';
1272
1273 RETURN l_sql_stmt;
1274
1275 END get_user_linemgr_info;
1276
1277
1278 -- Find Users who have the ability to access PRODUCT through
1279 -- the Human Resource Analyst by Manager responsibility but does not have
1280 -- the profile option HRI:HR Analyst (Manager View) Top set .
1281
1282 FUNCTION get_user_anlstmgr_info
1283 RETURN VARCHAR2 IS
1284
1285 l_sql_stmt VARCHAR2(32000);
1286
1287 BEGIN
1288
1289 l_sql_stmt :=
1290 'SELECT usr.user_name
1291 ,null col2
1292 ,null col3
1293 ,null col4
1294 ,null col5
1295 FROM
1296 fnd_user usr
1297 ,wf_user_role_assignments waur
1298 ,wf_local_roles wlr
1299 ,fnd_responsibility resp
1300 WHERE resp.responsibility_id = wlr.orig_system_id
1301 AND resp.responsibility_key = ''HRI_OBIEE_WRKFC_MGRH''
1302 AND wlr.orig_system = ''FND_RESP''
1303 AND usr.user_name = waur.user_name
1304 AND waur.role_name = wlr.name
1305 AND TRUNC(SYSDATE) BETWEEN usr.start_date
1306 AND NVL(usr.end_date, hr_general.end_of_time)
1307 AND TRUNC(SYSDATE) BETWEEN resp.start_date
1308 AND NVL(resp.end_date, hr_general.end_of_time)
1309 AND FND_PROFILE.VALUE_SPECIFIC(''HRI_OBIEE_WRKFC_MGRH_TOP''
1310 ,usr.user_id,resp.responsibility_id
1311 ,resp.application_id) IS NULL' ;
1312
1313 RETURN l_sql_stmt;
1314
1315 END get_user_anlstmgr_info;
1316
1317 -- Users who have the ability to access PRODUCT through the
1318 -- Department Manager responsibility but does not have the
1319 -- profile option HRI:Line Manager (Organization View) Top set
1320
1321 FUNCTION get_user_deptmgr_info
1322 RETURN VARCHAR2 IS
1323
1324 l_sql_stmt VARCHAR2(32000);
1325
1326 BEGIN
1327
1328 l_sql_stmt :=
1329 'SELECT usr.user_name
1330 ,null col2
1331 ,null col3
1332 ,null col4
1333 ,null col5
1334 FROM
1335 fnd_user usr
1336 ,wf_user_role_assignments waur
1337 ,wf_local_roles wlr
1338 ,fnd_responsibility resp
1339 WHERE resp.responsibility_id = wlr.orig_system_id
1340 AND resp.responsibility_key = ''HRI_OBI_ALL_ORGH''
1341 AND wlr.orig_system = ''FND_RESP''
1342 AND usr.user_name = waur.user_name
1343 AND waur.role_name = wlr.name
1344 AND TRUNC(SYSDATE) BETWEEN usr.start_date
1345 AND NVL(usr.end_date, hr_general.end_of_time)
1346 AND TRUNC(SYSDATE) BETWEEN resp.start_date
1347 AND NVL(resp.end_date, hr_general.end_of_time)
1348 AND FND_PROFILE.VALUE_SPECIFIC(''HRI_OBI_ALL_ORGH_TOP''
1349 ,usr.user_id,resp.responsibility_id
1350 , resp.application_id) IS NULL' ;
1351
1352 RETURN l_sql_stmt;
1353
1354 END get_user_deptmgr_info;
1355
1356 -- Users who have the ability to access PRODUCT through
1357 -- the Human Resource Analyst by Organization responsibility but does not have
1358 -- the profile option HRI:HR Analyst (Organization View) Top set
1359
1360
1361 FUNCTION get_user_orgmgr_info
1362 RETURN VARCHAR2 IS
1363
1364 l_sql_stmt VARCHAR2(32000);
1365
1366 BEGIN
1367
1368 l_sql_stmt :=
1369 'SELECT usr.user_name
1370 ,null col2
1371 ,null col3
1372 ,null col4
1373 ,null col5
1374 FROM
1375 fnd_user usr
1376 ,wf_user_role_assignments waur
1377 ,wf_local_roles wlr
1378 ,fnd_responsibility resp
1379 WHERE resp.responsibility_id = wlr.orig_system_id
1380 AND resp.responsibility_key = ''HRI_OBIEE_WRKFC_ORGH''
1381 AND wlr.orig_system = ''FND_RESP''
1382 AND usr.user_name = waur.user_name
1383 AND waur.role_name = wlr.name
1384 AND TRUNC(SYSDATE) BETWEEN usr.start_date
1385 AND NVL(usr.end_date, hr_general.end_of_time)
1386 AND TRUNC(SYSDATE) BETWEEN resp.start_date
1387 AND NVL(resp.end_date, hr_general.end_of_time)
1388 AND FND_PROFILE.VALUE_SPECIFIC(''HRI_OBIEE_WRKFC_ORGH_TOP''
1389 ,usr.user_id,resp.responsibility_id
1390 ,resp.application_id) IS NULL';
1391
1392 RETURN l_sql_stmt;
1393
1394 END get_user_orgmgr_info;
1395
1396
1397 -- Get workers with unassigned gender for a Business group
1398
1399 FUNCTION get_unassg_gndr_info
1400 RETURN VARCHAR2 IS
1401
1402 l_sql_stmt VARCHAR2(32000);
1403
1404 BEGIN
1405
1406 l_sql_stmt :=
1407 'SELECT per.full_name
1408 ,per.effective_start_date
1409 ,per.effective_end_date
1410 ,per.employee_number
1411 ,org.name
1412 FROM per_all_people_f per,
1413 hr_all_organization_units_tl org
1414 WHERE per.business_group_id = org.organization_id
1415 AND TRUNC(sysdate) BETWEEN per.effective_start_date AND per.effective_end_date
1416 AND org.LANGUAGE = userenv(''LANG'')
1417 AND per.sex IS NULL' ;
1418
1419 RETURN l_sql_stmt;
1420
1421 END get_unassg_gndr_info;
1422
1423
1424 END hri_apl_dgnstc_wrkfc;
[Home] [Help]