[Home] [Help]
PACKAGE BODY: APPS.HR_REPORTS
Source
1 PACKAGE BODY hr_reports AS
2 /* $Header: peperrep.pkb 120.1 2007/09/12 21:15:42 rnestor noship $
3 ******************************************************************
4 * *
5 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
6 * Chertsey, England. *
7 * *
8 * All rights reserved. *
9 * *
10 * This material has been provided pursuant to an agreement *
11 * containing restrictions on its use. The material is also *
12 * protected by copyright law. No part of this material may *
13 * be copied or distributed, transmitted or transcribed, in *
14 * any form or by any means, electronic, mechanical, magnetic, *
15 * manual, or otherwise, or disclosed to third parties without *
16 * the express written permission of Oracle Corporation UK Ltd, *
17 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
18 * England. *
19 * *
20 ******************************************************************
21 Name : hr_reports (BODY)
22 File : hr_reports.pkb
23 Description : This package declares functions which are used to
24 return Values for the SRW2 reports.
25 --
26 Change List
27 -----------
28 --
29 Version Date Author ER/CR No. Description of Change
30 -------+---------+----------+---------+--------------------------
31 70.0 17-JUN-93 JRHODES Date Created
32 70.1 23-JUN-93 JHOBBS Added get_business_group.
33 70.2 30-JUN-93 JHOBBS Changed get_organization and added
34 count_org_subordinates,
35 get_lookup_meaning,
36 get_organization_hierarchy,
37 count_pos_subordinates,
38 get_position_hierarchy
39 JRHODES Added person_matching_skills
40 get_job,
41 get_position
42 PCHAPPELL Added get_payroll_name
43 07-JUL-93 PCHAPPELL Added element_name
44 12-JUL-93 JRHODES Split_segments
45 Gen_partial_matching_lexical
46 14-JUL-93 JHOBBS Added get_desc_flex and
47 get_dvlpr_desc_flex.
48 70.3 04-AUG-93 NKHAN Added get_attributes and
49 aplit_segments
50 70.4 05-AUG-93 JRHODES Added get_grade and get_status
51 70.5 05-AUG-93 NKHAN Chahged get_attributes -
52 p_title to p_name
53 70.6 06-AUG-93 JHOBBS Added get_person_name
54 70.7 09-AUG-93 JRHODES Added get_abs_type
55 70.8 23-oct-93 NKHAN expanded l_concat_segs in both
56 get_attributes get_segments
57 from 240->2000
58 80.1/ 29-Oct-93 JHobbs B257 Increased Variable sizes for desc flex
59 70.8 procedures to 2000 characters.
60 70.9/ 25-Nov-93 Jhobbs B278 Changed variable declarations to use
61 80.2 %type definitions so that variables are
62 always the correct length.
63 70.10 01-Mar-94 JRhodes Altered person_matching_skills
64 to allow partial matching
65 70.12 23-Nov-94 RFine Suppressed index on business_group_id
66 70.13 13-Sep-95 AMills Added procedure get_desc_flex_context
67 that passes out nocopy a concatenated flex
68 field in order that the report triggers
69 pass a lexical field to the report
70 queries.
71 70.14 29-SEP-95 AMILLS Altered person_matching_skills
72 function to match on discreet segments
73 instead of an exact segments matching
74 set.
75 70.17 11-OCT-95 JTHURING Removed spurious end of comment marker
76 70.18 22-DEC-95 AMILLS 330536 Changed size of v_label_expr to 32000
77 to cope with large flexfields.
78 70.19 07-MAY-96 AMILLS 363691 Placed conditional clause inside
79 procedure get_desc_flex_context to
80 test for assignment table alias as
81 it uses ass_attribute_category as
82 opposed to attribute_category.
83 70.20 06-SEP-96 DKERR 399209 Replace 's with ''s in form_left_prompt
84 columns.
85 70.21 22-APR-97 HPATEL Bug 434553. Altered person_matching_skills to bring back the correct rows
86 --
87 70.22 20-AUG-97 ASAHAY 523506 Placed conditional clause inside
88 procedure get_desc_flex_context to
89 test for contacts table alias as
90 it uses cont_attribute_category as
91 opposed to attribute_category.
92 --
93 110.2 21-AUG-97 Khabibul N/A Modified to use _VL views due to change
94 in AOL 11.0.4.
95 110.3 28-NOV-97 MMILLMOR 563806 Changed split_segments and
96 gen_partial_matching_lexical to take
97 an additional paramater p_id_flex_code
98 to correctly identify flexfields.
99 Procedures are overloaded.
100 110.4 01-DEC-97 MMILLMOR 550991 Changed get_desc_flex_context to only
101 display fields with the given display
102 flag. Preserved old function by
103 overloading.
104 110.5 16-DEC-97 MMILLMOR 550991 Changed above fix to default to only
105 display those segments with the
106 displayed flag set to 'Y'
107 110.6 10-FEB-98 SBHATTAL 622283 Created new versions of procedures
108 1) get_dvlpr_desc_flex
109 2) split_attributes
110 3) get_attributes
111 with an extra parameter
112 (p_aol_seperator_flag).
113 Retained old versions of these
114 procedures for existing calls in
115 reports (procedure overloading).
116 110.7 03-APR-98 ASAHAY 440841 Placed conditional clause inside
117 procedure get_desc_flex_context to
118 test for assignment extra info DDF
119 table alias as paei as it uses
120 aei_information_category as opposed
121 to attribute_category.
122 110.8 12-OCT-98 ASAHAY 735632 Placed conditional clause inside
123 procedure get_desc_flex_context to
124 test for table alias (1) app as
125 it uses appl_information_category
126 as opposed to attribute_category.
127 test for table alias (2) addr as
128 it uses addr_information_category
129 as opposed to attribute_category.
130 110.9 08-FEB-99 LTAYLOR Changed and updated MLS and Date
131 formats for release 11.5
132 110.10 09-FEB-99 LSIGRIST Updated function get_element_name
133 with MLS changes.
134 115.4 11-MAY-99 CCARTER 886635 Replaced chr() function calls with
135 calls to fnd_global.local_chr();
136 115.5 06-JUL-99 CCARTER 875085 v_title changed from 40 to 60 chr
137 in get_dvlpr_desc_flex and get_desc_
138 flex_context.
139 115.6 15-SEP-99 ASAHAY 641528 Changed split_segments and
140 gen_partial_matching_lexical to take
141 an additional paramater p_id_flex_code
142 to correctly identify flexfields.
143 Procedures are overloaded.
144 115.7 21-SEP-99 CCARTER 991360 Added p_table_alias = 'f' in
145 get_desc_flex_context.
146 115.8 01-Oct-99 SCNair Date Track Position related Changes
147 115.9 22-OCT-99 ASahay 1010205 Increased variable length in
148 get_desc_flex_context.
149 115.10 31-May-00 CTredwin 1123133 Added nvl in get_attributes() to
150 prevent table access error
151 115.11 11-Jan-01 adhunter 1577078 Added clause to restrict by application_id
152 in split_segments procedure
153 115.12 11-Jan-01 adhunter Forgot to add above comment for 115.11
154 115.13 12-Jan-01 CSIMPSON 1512969 added overloaded get_position
155 function to return position name
156 for position_id on the effective
157 date parameter.
158 115.16 01-Aug-02 tabedin 2404098 added set verify and placed whenever
159 sqlerror line at the begining
160 115.17 23-Sep-02 vramanai 2567862 In get_desc_flex procedure,modified
161 v_ column_expr and v_label_expr from 20000
162 to 32000 char and in get_desc_flex_context
163 procedure,SUBSTRB function is added to
164 v_column_expr and v_label_expr
165 115.18 02-NOV-02 eumenyio Added nocopy
166 115.19 12-DEC-02 joward MLS enabled grade_name
167 115.20 27-DEC-02 joward MLS enabled job name
168 115.21 17-FEB-03 rthiagar Fix for bug 3440744. Used
169 hr_all_organization_units_tl to get
170 business group name to support
171 translation.
172 115.22 22-JUN-04 adhunter MLS enabled per_absence_attendance_types.name
173 115.23 07-FEB-05 smparame 4081149 New fucntion get_party_number added.
174 115.24 08-FEB-05 smparame 4157312 Function get_status modified to return correct
175 status.
176 115.25 02-MAY-05 bshukla 4328224 Changed SQL id 12228906: Performance Repository
177 Fix
178 115.27 21-MAR-07 ande 5651801 Changed return type of get_party_number
179 to varchar2.
180
181 =================================================================
182 */
183 --
184 --
185 FUNCTION get_budget
186 (p_budget_id NUMBER ) return VARCHAR2
187 --
188 AS
189 l_budget_name per_budgets.name%type;
190 --
191 begin
192 --
193 hr_utility.trace('Entered Get_Budget');
194 --
195 hr_utility.set_location('hr_reports.get_budget',1);
196 if p_budget_id IS NULL then
197 null;
198 else
199 begin
200 hr_utility.set_location('hr_reports.get_budget',5);
201 SELECT name
202 INTO l_budget_name
203 FROM per_budgets
204 WHERE budget_id = p_budget_id;
205 --
206 exception when NO_DATA_FOUND then null;
207 end;
208 end if;
209 --
210 return l_budget_name;
211 --
212 end get_budget;
213 --
214 --
215 FUNCTION get_budget_version
216 (p_budget_id NUMBER
217 ,p_budget_version_id NUMBER) return VARCHAR2
218 --
219 AS
220 l_budget_version_number per_budget_versions.version_number%type;
221 --
222 begin
223 --
224 hr_utility.trace('Entered Get_Budget_version');
225 --
226 hr_utility.set_location('hr_reports.get_budget_version',1);
227 if p_budget_id IS NULL OR p_budget_version_id IS NULL then
228 null;
229 else
230 begin
231 hr_utility.set_location('hr_reports.get_budget_version',5);
232 SELECT version_number
233 INTO l_budget_version_number
234 FROM per_budget_versions
235 WHERE budget_id = p_budget_id
236 AND budget_version_id = p_budget_version_id;
237 --
238 exception when NO_DATA_FOUND then null;
239 end;
240 end if;
241 --
242 return l_budget_version_number;
243 --
244 end get_budget_version;
245 --
246 --
247 procedure get_organization
248 (p_organization_id in number,
249 p_org_name out nocopy varchar2,
250 p_org_type out nocopy varchar2)
251 --
252 as
253 begin
254 --
255 hr_utility.trace('Entered get_organization');
256 --
257 hr_utility.set_location('hr_reports.get_organization',5);
258 if p_organization_id is null then
259 null;
260 else
261 begin
262 hr_utility.set_location('hr_reports.get_organization',10);
263 select orgtl.name,
264 hrl.meaning
265 into p_org_name,
266 p_org_type
267 from hr_all_organization_units_tl orgtl,
268 hr_all_organization_units org,
269 hr_lookups hrl
270 where org.organization_id = p_organization_id
271 and org.organization_id = orgtl.organization_id
272 and hrl.lookup_type (+) = 'ORG_TYPE'
273 and hrl.lookup_code (+) = org.type
274 and orgtl.LANGUAGE = userenv('LANG');
275 exception
276 when no_data_found then null;
277 end;
278 end if;
279 --
280 --
281 end get_organization;
282 --
283 --
284 FUNCTION get_job
285 (p_job_id NUMBER) return VARCHAR2
286 --
287 AS
288 l_job_name per_jobs.name%type;
289 --
290 begin
291 --
292 hr_utility.trace('Entered Get_Job');
293 --
294 begin
295 hr_utility.set_location('hr_reports.get_job',5);
296 SELECT name
297 INTO l_job_name
298 FROM per_jobs_vl
299 WHERE job_id = p_job_id;
300 --
301 exception when NO_DATA_FOUND then null;
302 end;
303 --
304 return l_job_name;
305 --
306 end get_job;
307 --
308 --
309 FUNCTION get_position
310 (p_position_id NUMBER) return VARCHAR2
311 --
312 AS
313 --
314 -- Changed 02-Oct-99 SCNair (per_positions to hr_positions) Date Tracked Positions requirement
315 --
316 l_position_name hr_positions.name%type;
317 --
318 begin
319 --
320 hr_utility.trace('Entered Get_position');
321 --
322 begin
323 --
324 -- Changed 02-Oct-99 SCNair (per_positions to hr_positions) Date Tracked Positions requirement
325 --
326 hr_utility.set_location('hr_reports.get_position',5);
327 SELECT name
328 INTO l_position_name
329 FROM hr_positions
330 WHERE position_id = p_position_id;
331 --
332 exception when NO_DATA_FOUND then null;
333 end;
334 --
335 return l_position_name;
336 --
337 end get_position;
338 --
339 --
340 FUNCTION get_position
341 (p_position_id NUMBER,
342 p_effective_date DATE) return VARCHAR2
343 --
344 -- Returns position name on the effective date parameter (this is not necessarily same
345 -- not session date, so selects from the hr_positions_f view rather than hr_positions view).
346 --
347 AS
348 --
349 l_position_name hr_all_positions_f.name%type;
350 --
351 begin
352 --
353 hr_utility.trace('Entered Get_position');
354 --
355 begin
356 --
357 --
358 hr_utility.set_location('hr_reports.get_position',5);
359 SELECT name
360 INTO l_position_name
361 FROM hr_positions_f paf
362 WHERE paf.position_id = p_position_id
363 AND p_effective_date between paf.effective_start_date and paf.effective_end_date;
364 --
365 exception when NO_DATA_FOUND then null;
366 end;
367 --
368 return l_position_name;
369 --
370 end get_position;
371 --
372 --
373 FUNCTION get_grade
374 (p_grade_id NUMBER) return VARCHAR2
375 --
376 AS
377 l_grade_name per_grades.name%type;
378 --
379 begin
380 --
381 hr_utility.trace('Entered Get_Grade');
382 --
383 begin
384 hr_utility.set_location('hr_reports.get_grade',5);
385 SELECT name
386 INTO l_grade_name
387 FROM per_grades_vl
388 WHERE grade_id = p_grade_id;
389 --
390 exception when NO_DATA_FOUND then null;
391 end;
392 --
393 return l_grade_name;
394 --
395 end get_grade;
396 --
397 --
398 FUNCTION get_status
399 (p_business_group_id NUMBER,
400 p_assignment_status_type_id NUMBER,
401 p_legislation_code VARCHAR2) return VARCHAR2
402 --
403 AS
404 l_user_status per_assignment_status_types_tl.user_status%type;
405 --
406 begin
407 --
408 hr_utility.trace('Entered Get_Status');
409 --
410 begin
411 hr_utility.set_location('hr_reports.get_status',5);
412 -- bug fix 4157312
413 -- join between per_ass_status_type_amends_tl and
414 -- per_ass_status_type_amends changed to outer join.
415
416 SELECT nvl(btl.user_status,atl.user_status)
417 INTO l_user_status
418 from per_assignment_status_types_tl atl
419 , per_assignment_status_types a
420 , per_ass_status_type_amends_tl btl
421 , per_ass_status_type_amends b
422 where b.assignment_status_type_id(+) = a.assignment_status_type_id
423 and a.assignment_status_type_id = atl.assignment_status_type_id
424 and b.ass_status_type_amend_id = btl.ass_status_type_amend_id(+)
425 and a.assignment_status_type_id = P_ASSIGNMENT_STATUS_TYPE_ID
426 and b.business_group_id(+) + 0 = P_BUSINESS_GROUP_ID
427 and nvl(a.business_group_id,P_BUSINESS_GROUP_ID) =
428 P_BUSINESS_GROUP_ID
429 and nvl(a.legislation_code, P_LEGISLATION_CODE) =
430 P_LEGISLATION_CODE
431 and nvl(b.active_flag,a.active_flag) = 'Y'
432 and nvl(b.default_flag,a.active_flag) = 'Y'
433 and decode(btl.ass_status_type_amend_id, NULL, '1', btl.language)
434 = decode(btl.ass_status_type_amend_id, NULL, '1', userenv('LANG'))
435 and atl.language = userenv('LANG');
436 --
437 exception when NO_DATA_FOUND then null;
438 end;
439 --
440 return l_user_status;
441 --
442 end get_status;
443 --
444 --
445 FUNCTION get_abs_type
446 (p_abs_att_type_id NUMBER) return VARCHAR2
447 --
448 AS
449 l_abs_name per_absence_attendance_types.name%type;
450 --
451 begin
452 --
453 hr_utility.trace('Entered Get_Abs_Type');
454 --
455 begin
456 hr_utility.set_location('hr_reports.get_abs_type',5);
457 SELECT name
458 INTO l_abs_name
459 FROM per_abs_attendance_types_vl
460 WHERE absence_attendance_type_id = p_abs_att_type_id;
461 --
462 exception when NO_DATA_FOUND then null;
463 end;
464 --
465 return l_abs_name;
466 --
467 end get_abs_type;
468 --
469 --
470 PROCEDURE get_time_period
471 (p_time_period_id IN NUMBER
472 ,p_period_name OUT NOCOPY VARCHAR2
473 ,p_start_date OUT NOCOPY DATE
474 ,p_end_date OUT NOCOPY DATE)
475 --
476 AS
477 --
478 begin
479 --
480 hr_utility.trace('Entered Get_time_period');
481 --
482 hr_utility.set_location('hr_reports.get_time_period',1);
483 if p_time_period_id IS NULL then
484 null;
485 else
486 begin
487 hr_utility.set_location('hr_reports.get_time_period',5);
488 SELECT period_name
489 , start_date
490 , end_date
491 INTO p_period_name
492 , p_start_date
493 , p_end_date
494 FROM per_time_periods
495 WHERE time_period_id = p_time_period_id;
496 --
497 exception when NO_DATA_FOUND then null;
498 end;
499 end if;
500 --
501 end get_time_period;
502 --
503 --
504 FUNCTION get_element_name
505 (p_session_date DATE,
506 p_element_type_id NUMBER) return VARCHAR2
507 --
508 AS
509 v_element_name pay_element_types_f_tl.element_name%type;
510 --
511 begin
512 hr_utility.trace('Entered hr_reports.get_element_name');
513 --
514 hr_utility.set_location('hr_reports.get_element_name',5);
515 if p_element_type_id is null then
516 null;
517 else
518 begin
519 hr_utility.set_location('hr_reports.get_element_name',10);
520 select etl.element_name
521 into v_element_name
522 from pay_element_types_f_tl etl,
523 pay_element_types_f e
524 where e.element_type_id = p_element_type_id
525 and p_session_date between
526 e.effective_start_date and
527 e.effective_end_date
528 and e.element_type_id = etl.element_type_id
529 and etl.LANGUAGE = userenv('LANG');
530 exception
531 when no_data_found then null;
532 end;
533 end if;
534 --
535 hr_utility.trace('Leaving hr_reports.get_element_name');
536 --
537 return v_element_name;
538 --
539 end get_element_name;
540 --
541 --
542 FUNCTION get_payroll_name
543 (p_session_date DATE,
544 p_payroll_id NUMBER) return VARCHAR2
545 --
546 AS
547 v_payroll_name pay_payrolls_f.payroll_name%type;
548 --
549 begin
550 --
551 hr_utility.trace('Entered hr_reports.get_payroll_name');
552 --
553 hr_utility.set_location('hr_reports.get_payroll_name',5);
554 if p_payroll_id is null then
555 null;
556 else
557 begin
558 hr_utility.set_location('hr_reports.get_payroll_name',10);
559 select p.payroll_name
560 into v_payroll_name
561 from pay_payrolls_f p
562 where payroll_id = p_payroll_id
563 and p_session_date between
564 p.effective_start_date and
565 p.effective_end_date;
566 exception
567 when no_data_found then null;
568 end;
569 end if;
570 --
571 hr_utility.trace('Leaving hr_reports.get_payroll_name');
572 --
573 return v_payroll_name;
574 --
575 end get_payroll_name;
576 --
577 --
578 FUNCTION get_business_group
579 (p_business_group_id NUMBER) return VARCHAR2
580 --
581 AS
582 v_business_group_name hr_organization_units.name%type;
583 --
584 begin
585 --
586 hr_utility.trace('Entered hr_reports.get_business_group');
587 --
588 hr_utility.set_location('hr_reports.get_business_group',5);
589 if p_business_group_id is null then
590 null;
591 else
592 begin
593 hr_utility.set_location('hr_reports.get_business_group',10);
594 select org.name
595 into v_business_group_name
596 from hr_all_organization_units_tl org
597 -- where org.organization_id + 0 = p_business_group_id
598 -- Changed for Performance Fix: Bug 4328224
599 where org.organization_id = p_business_group_id
600 and org.language(+) = userenv('LANG');
601 -- and org.business_group_id + 0 = org.organization_id;
602 exception
603 when no_data_found then null;
604 end;
605 end if;
606 --
607 hr_utility.trace('Leaving hr_reports.get_business_group');
608 --
609 return v_business_group_name;
610 --
611 end get_business_group;
612 --
613 --
614 function count_org_subordinates
615 (p_org_structure_version_id number,
616 p_parent_organization_id number) return number
617 --
618 AS
619 v_subordinate_count number;
620 --
621 begin
622 --
623 hr_utility.trace('Entered hr_reports.count_org_subordinates');
624 --
625 hr_utility.set_location('hr_reports.count_org_subordinates',5);
626 if p_org_structure_version_id is null or
627 p_parent_organization_id is null then
628 null;
629 else
630 begin
631 hr_utility.set_location('hr_reports.count_org_subordinates',10);
632 select nvl(count(*),0)
633 into v_subordinate_count
634 from per_org_structure_elements ose
635 connect by ose.organization_id_parent = prior ose.organization_id_child
636 and ose.org_structure_version_id = p_org_structure_version_id
637 start with ose.organization_id_parent = p_parent_organization_id
638 and ose.org_structure_version_id = p_org_structure_version_id;
639 exception
640 when no_data_found then null;
641 end;
642 end if;
643 --
644 hr_utility.trace('Leaving hr_reports.count_org_subordinates');
645 --
646 return v_subordinate_count;
647 --
648 end count_org_subordinates;
649 --
650 --
651 function count_pos_subordinates
652 (p_pos_structure_version_id number,
653 p_parent_position_id number) return number
654 --
655 AS
656 v_subordinate_count number;
657 --
658 begin
659 --
660 hr_utility.trace('Entered hr_reports.count_pos_subordinates');
661 --
662 hr_utility.set_location('hr_reports.count_pos_subordinates',5);
663 if p_pos_structure_version_id is null or
664 p_parent_position_id is null then
665 null;
666 else
667 begin
668 hr_utility.set_location('hr_reports.count_pos_subordinates',10);
669 select nvl(count(*),0)
670 into v_subordinate_count
671 from per_pos_structure_elements pse
672 connect by pse.parent_position_id = prior pse.subordinate_position_id
673 and pse.pos_structure_version_id = p_pos_structure_version_id
674 start with pse.parent_position_id = p_parent_position_id
675 and pse.pos_structure_version_id = p_pos_structure_version_id;
676 exception
677 when no_data_found then null;
678 end;
679 end if;
680 --
681 hr_utility.trace('Leaving hr_reports.count_pos_subordinates');
682 --
683 return v_subordinate_count;
684 --
685 end count_pos_subordinates;
686 --
687 --
688 procedure get_organization_hierarchy
689 (p_organization_structure_id in number,
690 p_org_structure_version_id in number,
691 p_org_structure_name out nocopy varchar2,
692 p_org_version out nocopy number,
693 p_version_start_date out nocopy date,
694 p_version_end_date out nocopy date)
695 --
696 AS
697 --
698 begin
699 --
700 hr_utility.trace('Entered hr_reports.get_organization_hierarchy');
701 --
702 hr_utility.set_location('hr_reports.get_organization_hierarchy',5);
703 if p_organization_structure_id is not null then
704 begin
705 hr_utility.set_location('hr_reports.get_organization_hierarchy',10);
706 select ost.name
707 into p_org_structure_name
708 from per_organization_structures ost
709 where ost.organization_structure_id = p_organization_structure_id;
710 exception
711 when no_data_found then null;
712 end;
713 elsif p_org_structure_version_id is not null then
714 begin
715 hr_utility.set_location('hr_reports.get_organization_hierarchy',15);
716 select ost.name,
717 osv.version_number,
718 osv.date_from,
719 osv.date_to
720 into p_org_structure_name,
721 p_org_version,
722 p_version_start_date,
723 p_version_end_date
724 from per_organization_structures ost,
725 per_org_structure_versions osv
726 where osv.org_structure_version_id = p_org_structure_version_id
727 and ost.organization_structure_id = osv.organization_structure_id;
728 exception
729 when no_data_found then null;
730 end;
731 end if;
732 --
733 hr_utility.trace('Leaving hr_reports.get_organization_hierarchy');
734 --
735 end get_organization_hierarchy;
736 --
737 --
738 procedure get_position_hierarchy
739 (p_position_structure_id in number,
740 p_pos_structure_version_id in number,
741 p_pos_structure_name out nocopy varchar2,
742 p_pos_version out nocopy number,
743 p_version_start_date out nocopy date,
744 p_version_end_date out nocopy date)
745 --
746 AS
747 --
748 begin
749 --
750 hr_utility.trace('Entered hr_reports.get_position_hierarchy');
751 --
752 hr_utility.set_location('hr_reports.get_position_hierarchy',5);
753 if p_position_structure_id is not null then
754 begin
755 hr_utility.set_location('hr_reports.get_position_hierarchy',10);
756 select pst.name
757 into p_pos_structure_name
758 from per_position_structures pst
759 where pst.position_structure_id = p_position_structure_id;
760 exception
761 when no_data_found then null;
762 end;
763 elsif p_pos_structure_version_id is not null then
764 begin
765 hr_utility.set_location('hr_reports.get_position_hierarchy',15);
766 select pst.name,
767 psv.version_number,
768 psv.date_from,
769 psv.date_to
770 into p_pos_structure_name,
771 p_pos_version,
772 p_version_start_date,
773 p_version_end_date
774 from per_position_structures pst,
775 per_pos_structure_versions psv
776 where psv.pos_structure_version_id = p_pos_structure_version_id
777 and pst.position_structure_id = psv.position_structure_id;
778 exception
779 when no_data_found then null;
780 end;
781 end if;
782 --
783 hr_utility.trace('Leaving hr_reports.get_position_hierarchy');
784 --
785 end get_position_hierarchy;
786 --
787 --
788 function get_lookup_meaning
789 (p_lookup_type varchar2,
790 p_lookup_code varchar2) return varchar2
791 --
792 AS
793 v_meaning hr_lookups.meaning%type;
794 --
795 begin
796 --
797 hr_utility.trace('Entered hr_reports.get_lookup_meaning');
798 --
799 begin
800 hr_utility.set_location('hr_reports.get_lookup_meaning',5);
801 select hrl.meaning
802 into v_meaning
803 from hr_lookups hrl
804 where hrl.lookup_type = p_lookup_type
805 and hrl.lookup_code = p_lookup_code;
806 exception
807 when no_data_found then null;
808 end;
809 --
810 hr_utility.trace('Leaving hr_reports.get_lookup_meaning');
811 --
812 return v_meaning;
813 --
814 end get_lookup_meaning;
815 --
816 --
817 FUNCTION person_matching_skills
818 (p_person_id IN NUMBER
819 ,p_job_position_id IN NUMBER
820 ,p_job_position_type IN VARCHAR2
821 ,p_matching_level IN VARCHAR2
822 ,p_no_of_essential IN NUMBER
823 ,p_no_of_desirable IN NUMBER) RETURN BOOLEAN AS
824 --
825 -- Local Variables
826 --
827 l_person_matches BOOLEAN := TRUE;
828 --
829 FUNCTION count_skills (p_person_id IN NUMBER
830 ,p_job_position_id IN NUMBER
831 ,p_job_position_type IN VARCHAR2
832 ,p_essential_flag IN VARCHAR2
833 ,p_number_required IN NUMBER)
834 --
835 RETURN BOOLEAN IS
836 --
837 l_number_matching NUMBER(10) := 0;
838 --
839 BEGIN
840 --
841 BEGIN
842 hr_utility.set_location('hr_reports.person_matching_skills',5);
843 select sign(count(*))
844 into l_number_matching
845 from per_person_analyses p
846 where p.person_id = P_PERSON_ID
847 and exists
848 (select null
849 from per_job_requirements j
850 , per_analysis_criteria ja
851 , per_analysis_criteria pa
852 where ((P_JOB_POSITION_TYPE = 'J' and
853 j.job_id = P_JOB_POSITION_ID)
854 or (P_JOB_POSITION_TYPE = 'P' and
855 j.position_id = P_JOB_POSITION_ID))
856 and j.essential = P_ESSENTIAL_FLAG
857 and j.analysis_criteria_id = ja.analysis_criteria_id
858 and p.analysis_criteria_id = pa.analysis_criteria_id
859 and p.analysis_criteria_id = ja.analysis_criteria_id
860 and ja.id_flex_num = pa.id_flex_num
861 and ((ja.segment1 is null or
862 (ja.segment1 is not null and
863 ja.segment1 = pa.segment1))
864 or (ja.segment2 is null or
865 (ja.segment2 is not null and
866 ja.segment2 = pa.segment2))
867 or (ja.segment3 is null or
868 (ja.segment3 is not null and
869 ja.segment3 = pa.segment3))
870 or (ja.segment4 is null or
871 (ja.segment4 is not null and
872 ja.segment4 = pa.segment4))
873 or (ja.segment5 is null or
874 (ja.segment5 is not null and
875 ja.segment5 = pa.segment5))
876 or (ja.segment6 is null or
877 (ja.segment6 is not null and
878 ja.segment6 = pa.segment6))
879 or (ja.segment7 is null or
880 (ja.segment7 is not null and
881 ja.segment7 = pa.segment7))
882 or (ja.segment8 is null or
883 (ja.segment8 is not null and
884 ja.segment8 = pa.segment8))
885 or (ja.segment9 is null or
886 (ja.segment9 is not null and
887 ja.segment9 = pa.segment9))
888 or (ja.segment10 is null or
889 (ja.segment10 is not null and
890 ja.segment10 = pa.segment10))
891 or (ja.segment11 is null or
892 (ja.segment11 is not null and
893 ja.segment11 = pa.segment11))
894 or (ja.segment12 is null or
895 (ja.segment12 is not null and
896 ja.segment12 = pa.segment12))
897 or (ja.segment13 is null or
898 (ja.segment13 is not null and
899 ja.segment13 = pa.segment13))
900 or (ja.segment14 is null or
901 (ja.segment14 is not null and
902 ja.segment14 = pa.segment14))
903 or (ja.segment15 is null or
904 (ja.segment15 is not null and
905 ja.segment15 = pa.segment15))
906 or (ja.segment16 is null or
907 (ja.segment16 is not null and
908 ja.segment16 = pa.segment16))
909 or (ja.segment17 is null or
910 (ja.segment17 is not null and
911 ja.segment17 = pa.segment17))
912 or (ja.segment18 is null or
913 (ja.segment18 is not null and
914 ja.segment18 = pa.segment18))
915 or (ja.segment19 is null or
916 (ja.segment19 is not null and
917 ja.segment19 = pa.segment19))
918 or (ja.segment20 is null or
919 (ja.segment20 is not null and
920 ja.segment20 = pa.segment20))
921 or (ja.segment21 is null or
922 (ja.segment21 is not null and
923 ja.segment21 = pa.segment21))
924 or (ja.segment22 is null or
925 (ja.segment22 is not null and
926 ja.segment22 = pa.segment22))
927 or (ja.segment23 is null or
928 (ja.segment23 is not null and
929 ja.segment23 = pa.segment23))
930 or (ja.segment24 is null or
931 (ja.segment24 is not null and
932 ja.segment24 = pa.segment24))
933 or (ja.segment25 is null or
934 (ja.segment25 is not null and
935 ja.segment25 = pa.segment25))
936 or (ja.segment26 is null or
937 (ja.segment26 is not null and
938 ja.segment26 = pa.segment26))
939 or (ja.segment27 is null or
940 (ja.segment27 is not null and
941 ja.segment27 = pa.segment27))
942 or (ja.segment28 is null or
943 (ja.segment28 is not null and
944 ja.segment28 = pa.segment28))
945 or (ja.segment29 is null or
946 (ja.segment29 is not null and
947 ja.segment29 = pa.segment29))
948 or (ja.segment30 is null or
949 (ja.segment30 is not null and
950 ja.segment30 = pa.segment30)))
951 )
952 having count(*) >= P_NUMBER_REQUIRED;
953 --
954 EXCEPTION
955 when no_data_found then null;
956 END;
957 --
958 RETURN(l_number_matching > 0);
959 --
960 END;
961 --
962 --
963 BEGIN
964 --
965 hr_utility.trace('Entered hr_reports.person_matching_skills');
966 --
967 if p_matching_level = 'A' then
968 if p_no_of_essential > 0 then
969 hr_utility.set_location('hr_reports.person_matching_skills',10);
970 l_person_matches := count_skills(p_person_id
971 ,p_job_position_id
972 ,p_job_position_type
973 ,'Y'
974 ,p_no_of_essential);
975 end if;
976 elsif p_matching_level = 'D' then
977 if p_no_of_essential > 0 then
978 hr_utility.set_location('hr_reports.person_matching_skills',10);
979 l_person_matches := count_skills(p_person_id
980 ,p_job_position_id
981 ,p_job_position_type
982 ,'Y'
983 ,p_no_of_essential);
984 end if;
985 if p_no_of_desirable > 0
986 and l_person_matches = TRUE then
987 hr_utility.set_location('hr_reports.person_matching_skills',10);
988 l_person_matches := count_skills(p_person_id
989 ,p_job_position_id
990 ,p_job_position_type
991 ,'N'
992 ,1);
993 end if;
994 else -- matching_level = 'S'
995 if p_no_of_essential > 0 then
996 hr_utility.set_location('hr_reports.person_matching_skills',10);
997 l_person_matches := count_skills(p_person_id
998 ,p_job_position_id
999 ,p_job_position_type
1000 ,'Y'
1001 ,1);
1002 end if;
1003 end if;
1004 --
1005 return(l_person_matches);
1006 --
1007 END;
1008 --
1009
1010 PROCEDURE split_segments
1011 (p_concatenated_segments VARCHAR2
1012 ,p_id_flex_num NUMBER
1013 ,p_segtab OUT NOCOPY SegmentTabType
1014 ,p_segments_used OUT NOCOPY NUMBER) IS
1015 begin
1016 hr_reports.split_segments(p_concatenated_segments,
1017 p_id_flex_num,
1018 p_segtab,
1019 p_segments_used,
1020 NULL);
1021 end;
1022
1023 PROCEDURE split_segments
1024 (p_concatenated_segments VARCHAR2
1025 ,p_id_flex_num NUMBER
1026 ,p_segtab OUT NOCOPY SegmentTabType
1027 ,p_segments_used OUT NOCOPY NUMBER
1028 ,p_id_flex_code VARCHAR2) IS
1029 begin
1030 hr_reports.split_segments(p_concatenated_segments,
1031 p_id_flex_num,
1032 p_segtab,
1033 p_segments_used,
1034 p_id_flex_code,
1035 NULL);
1036 end;
1037
1038 --
1039 PROCEDURE split_segments
1040 (p_concatenated_segments VARCHAR2
1041 ,p_id_flex_num NUMBER
1042 ,p_segtab OUT NOCOPY SegmentTabType
1043 ,p_segments_used OUT NOCOPY NUMBER
1044 ,p_id_flex_code VARCHAR2
1045 ,p_application_id NUMBER) IS
1046 --
1047 l_no_of_segs NUMBER;
1048 l_concat_segs VARCHAR2(2000);
1049 l_seg_len NUMBER;
1050 l_start_pos NUMBER;
1051 l_count NUMBER := 1;
1052 l_seg_sep VARCHAR2(1) := '.';
1053 --
1054 begin
1055 hr_utility.trace('Entered hr_reports.split_segments');
1056 --
1057 hr_utility.set_location('hr_reports.split_segments',10);
1058 --
1059 begin
1060 select DISTINCT CONCATENATED_SEGMENT_DELIMITER
1061 into l_seg_sep
1062 from fnd_id_flex_structures_vl
1063 where ID_FLEX_NUM = P_ID_FLEX_NUM
1064 and ID_FLEX_CODE=NVL(p_id_flex_code,ID_FLEX_CODE)
1065 and APPLICATION_ID = nvl(p_application_id,APPLICATION_ID);
1066 exception
1067 when NO_DATA_FOUND then null;
1068 end;
1069 --
1070 hr_utility.set_location('hr_reports.split_segments',10);
1071 --
1072 l_concat_segs := l_seg_sep || p_concatenated_segments || l_seg_sep;
1073 l_no_of_segs := length(l_concat_segs)
1074 - length(replace(l_concat_segs,l_seg_sep));
1075 --
1076 while l_count < l_no_of_segs loop
1077 l_seg_len := instr(l_concat_segs, l_seg_sep,1 , l_count+1)
1078 - instr(l_concat_segs, l_seg_sep,1, l_count) - 1;
1079 l_start_pos := instr(l_concat_segs, l_seg_sep,1, l_count) + 1;
1080 p_segtab(l_count) := substr(l_concat_segs,l_start_pos,l_seg_len);
1081 p_segments_used := l_count;
1082 l_count := l_count + 1;
1083 end loop;
1084 --
1085 end;
1086 --
1087 --
1088
1089
1090 procedure gen_partial_matching_lexical
1091 (p_concatenated_segments IN VARCHAR2
1092 ,p_id_flex_num IN NUMBER
1093 ,p_matching_lexical IN OUT NOCOPY VARCHAR2) IS
1094 begin
1095 hr_reports.gen_partial_matching_lexical(p_concatenated_segments,
1096 p_id_flex_num,
1097 p_matching_lexical,
1098 NULL);
1099 end;
1100
1101 procedure gen_partial_matching_lexical
1102 (p_concatenated_segments IN VARCHAR2
1103 ,p_id_flex_num IN NUMBER
1104 ,p_matching_lexical IN OUT NOCOPY VARCHAR2
1105 ,p_id_flex_code IN VARCHAR2) IS
1106 begin
1107 hr_reports.gen_partial_matching_lexical(p_concatenated_segments,
1108 p_id_flex_num,
1109 p_matching_lexical,
1110 p_id_flex_code,
1111 NULL);
1112 end;
1113
1114
1115 procedure gen_partial_matching_lexical
1116 (p_concatenated_segments IN VARCHAR2
1117 ,p_id_flex_num IN NUMBER
1118 ,p_matching_lexical IN OUT NOCOPY VARCHAR2
1119 ,p_id_flex_code VARCHAR2
1120 ,p_application_id IN NUMBER) IS
1121 --
1122 l_count NUMBER(10) := 0;
1123 l_segtab hr_reports.SegmentTabType;
1124 l_segments_used NUMBER(10);
1125 --
1126 -- this cursor is used to get the order of the segments from the foundation
1127 -- table
1128 --
1129 cursor c1 is
1130 select application_column_name
1131 from fnd_id_flex_segments_vl
1132 where id_flex_num = p_id_flex_num
1133 and id_flex_code = nvl(p_id_flex_code,id_flex_code)
1134 and enabled_flag = 'Y'
1135 order by segment_num;
1136 --
1137 begin
1138 --
1139 hr_utility.trace('Entered hr_reports.gen_partial_matching_lexical');
1140 --
1141 hr_utility.set_location ('hr_reports.gen_partial_matching_lexical', 5);
1142 --
1143 hr_reports.split_segments(p_concatenated_segments
1144 ,p_id_flex_num
1145 ,l_segtab
1146 ,l_segments_used
1147 ,p_id_flex_code);
1148 --
1149 hr_utility.set_location ('hr_reports.gen_partial_matching_lexical', 10);
1150 for c1rec in c1 loop
1151 l_count := l_count + 1;
1152 --
1153 if l_segtab(l_count) is null then null;
1154 else
1155 p_matching_lexical := p_matching_lexical || ' AND ' ||
1156 c1rec.application_column_name || '=''' ||
1157 l_segtab(l_count) || '''';
1158 end if;
1159 end loop;
1160 --
1161 end gen_partial_matching_lexical;
1162 --
1163 -- Added for bug fix 622283, version 110.6
1164 --
1165 procedure get_attributes
1166 (p_concatenated_segments IN VARCHAR2
1167 ,p_name IN VARCHAR2
1168 ,p_segments_used OUT NOCOPY NUMBER
1169 ,p_value1 OUT NOCOPY VARCHAR2
1170 ,p_value2 OUT NOCOPY VARCHAR2
1171 ,p_value3 OUT NOCOPY VARCHAR2
1172 ,p_value4 OUT NOCOPY VARCHAR2
1173 ,p_value5 OUT NOCOPY VARCHAR2
1174 ,p_value6 OUT NOCOPY VARCHAR2
1175 ,p_value7 OUT NOCOPY VARCHAR2
1176 ,p_value8 OUT NOCOPY VARCHAR2
1177 ,p_value9 OUT NOCOPY VARCHAR2
1178 ,p_value10 OUT NOCOPY VARCHAR2
1179 ,p_value11 OUT NOCOPY VARCHAR2
1180 ,p_value12 OUT NOCOPY VARCHAR2
1181 ,p_value13 OUT NOCOPY VARCHAR2
1182 ,p_value14 OUT NOCOPY VARCHAR2
1183 ,p_value15 OUT NOCOPY VARCHAR2
1184 ,p_value16 OUT NOCOPY VARCHAR2
1185 ,p_value17 OUT NOCOPY VARCHAR2
1186 ,p_value18 OUT NOCOPY VARCHAR2
1187 ,p_value19 OUT NOCOPY VARCHAR2
1188 ,p_value20 OUT NOCOPY VARCHAR2
1189 ,p_value21 OUT NOCOPY VARCHAR2
1190 ,p_value22 OUT NOCOPY VARCHAR2
1191 ,p_value23 OUT NOCOPY VARCHAR2
1192 ,p_value24 OUT NOCOPY VARCHAR2
1193 ,p_value25 OUT NOCOPY VARCHAR2
1194 ,p_value26 OUT NOCOPY VARCHAR2
1195 ,p_value27 OUT NOCOPY VARCHAR2
1196 ,p_value28 OUT NOCOPY VARCHAR2
1197 ,p_value29 OUT NOCOPY VARCHAR2
1198 ,p_value30 OUT NOCOPY VARCHAR2 ) IS
1199 --
1200 l_aol_seperator_flag boolean := true;
1201 --
1202 begin
1203 --
1204 hr_reports.get_attributes( p_concatenated_segments
1205 ,p_name
1206 ,l_aol_seperator_flag
1207 ,p_segments_used
1208 ,p_value1
1209 ,p_value2
1210 ,p_value3
1211 ,p_value4
1212 ,p_value5
1213 ,p_value6
1214 ,p_value7
1215 ,p_value8
1216 ,p_value9
1217 ,p_value10
1218 ,p_value11
1219 ,p_value12
1220 ,p_value13
1221 ,p_value14
1222 ,p_value15
1223 ,p_value16
1224 ,p_value17
1225 ,p_value18
1226 ,p_value19
1227 ,p_value20
1228 ,p_value21
1229 ,p_value22
1230 ,p_value23
1231 ,p_value24
1232 ,p_value25
1233 ,p_value26
1234 ,p_value27
1235 ,p_value28
1236 ,p_value29
1237 ,p_value30
1238 );
1239 --
1240 end get_attributes;
1241 --
1242 -- Added for bug fix 622283, version 110.6
1243 --
1244 procedure get_attributes
1245 (p_concatenated_segments IN VARCHAR2
1246 ,p_name IN VARCHAR2
1247 ,p_aol_seperator_flag IN BOOLEAN
1248 ,p_segments_used OUT NOCOPY NUMBER
1249 ,p_value1 OUT NOCOPY VARCHAR2
1250 ,p_value2 OUT NOCOPY VARCHAR2
1251 ,p_value3 OUT NOCOPY VARCHAR2
1252 ,p_value4 OUT NOCOPY VARCHAR2
1253 ,p_value5 OUT NOCOPY VARCHAR2
1254 ,p_value6 OUT NOCOPY VARCHAR2
1255 ,p_value7 OUT NOCOPY VARCHAR2
1256 ,p_value8 OUT NOCOPY VARCHAR2
1257 ,p_value9 OUT NOCOPY VARCHAR2
1258 ,p_value10 OUT NOCOPY VARCHAR2
1259 ,p_value11 OUT NOCOPY VARCHAR2
1260 ,p_value12 OUT NOCOPY VARCHAR2
1261 ,p_value13 OUT NOCOPY VARCHAR2
1262 ,p_value14 OUT NOCOPY VARCHAR2
1263 ,p_value15 OUT NOCOPY VARCHAR2
1264 ,p_value16 OUT NOCOPY VARCHAR2
1265 ,p_value17 OUT NOCOPY VARCHAR2
1266 ,p_value18 OUT NOCOPY VARCHAR2
1267 ,p_value19 OUT NOCOPY VARCHAR2
1268 ,p_value20 OUT NOCOPY VARCHAR2
1269 ,p_value21 OUT NOCOPY VARCHAR2
1270 ,p_value22 OUT NOCOPY VARCHAR2
1271 ,p_value23 OUT NOCOPY VARCHAR2
1272 ,p_value24 OUT NOCOPY VARCHAR2
1273 ,p_value25 OUT NOCOPY VARCHAR2
1274 ,p_value26 OUT NOCOPY VARCHAR2
1275 ,p_value27 OUT NOCOPY VARCHAR2
1276 ,p_value28 OUT NOCOPY VARCHAR2
1277 ,p_value29 OUT NOCOPY VARCHAR2
1278 ,p_value30 OUT NOCOPY VARCHAR2 ) IS
1279 --
1280 l_segtab hr_reports.SegmentTabType;
1281 l_segments_used NUMBER(10);
1282 --
1283 begin
1284 --
1285 hr_utility.trace('Entered hr_reports.get_attributes');
1286 --
1287 hr_utility.set_location ('hr_reports.get_attributes', 5);
1288 --
1289 hr_reports.split_attributes( p_concatenated_segments
1290 ,p_name
1291 ,p_aol_seperator_flag
1292 ,l_segtab
1293 ,l_segments_used);
1294 --
1295 for i in nvl(l_segments_used, 0) + 1..30 loop
1296 l_segtab(i) := null;
1297 end loop;
1298 --
1299 hr_utility.set_location ('hr_reports.get_attributes', 10);
1300 --
1301 p_segments_used := l_segments_used;
1302 p_value1 := l_segtab(1);
1303 p_value2 := l_segtab(2);
1304 p_value3 := l_segtab(3);
1305 p_value4 := l_segtab(4);
1306 p_value5 := l_segtab(5);
1307 p_value6 := l_segtab(6);
1308 p_value7 := l_segtab(7);
1309 p_value8 := l_segtab(8);
1310 p_value9 := l_segtab(9);
1311 p_value10 := l_segtab(10);
1312 p_value11 := l_segtab(11);
1313 p_value12 := l_segtab(12);
1314 p_value13 := l_segtab(13);
1315 p_value14 := l_segtab(14);
1316 p_value15 := l_segtab(15);
1317 p_value16 := l_segtab(16);
1318 p_value17 := l_segtab(17);
1319 p_value18 := l_segtab(18);
1320 p_value19 := l_segtab(19);
1321 p_value20 := l_segtab(20);
1322 p_value21 := l_segtab(21);
1323 p_value22 := l_segtab(22);
1324 p_value23 := l_segtab(23);
1325 p_value24 := l_segtab(24);
1326 p_value25 := l_segtab(25);
1327 p_value26 := l_segtab(26);
1328 p_value27 := l_segtab(27);
1329 p_value28 := l_segtab(28);
1330 p_value29 := l_segtab(29);
1331 p_value30 := l_segtab(30);
1332 --
1333 SegmentValue1 := l_segtab(1);
1334 end get_attributes;
1335 --
1336 -- Added for bug fix 622283, version 110.6
1337 --
1338 PROCEDURE split_attributes
1339 (p_concatenated_segments IN VARCHAR2
1340 ,p_title IN VARCHAR2
1341 ,p_segtab OUT NOCOPY SegmentTabType
1342 ,p_segments_used OUT NOCOPY NUMBER
1343 ) IS
1344 --
1345 l_aol_seperator_flag boolean := true;
1346 --
1347 begin
1348 --
1349 hr_reports.split_attributes( p_concatenated_segments
1350 ,p_title
1351 ,l_aol_seperator_flag
1352 ,p_segtab
1353 ,p_segments_used
1354 );
1355 --
1356 end split_attributes;
1357 --
1358 -- Added for bug fix 622283, version 110.6
1359 --
1360 PROCEDURE split_attributes
1361 (p_concatenated_segments IN VARCHAR2
1362 ,p_title IN VARCHAR2
1363 ,p_aol_seperator_flag IN BOOLEAN
1364 ,p_segtab OUT NOCOPY SegmentTabType
1365 ,p_segments_used OUT NOCOPY NUMBER
1366 ) IS
1367 --
1368 l_no_of_segs NUMBER;
1369 l_concat_segs VARCHAR2(2000);
1370 l_seg_len NUMBER;
1371 l_start_pos NUMBER;
1372 l_count NUMBER := 1;
1373 l_seg_sep VARCHAR2(1) := '.';
1374 --
1375 begin
1376 hr_utility.trace('Entered hr_reports.split_attributes');
1377 --
1378 hr_utility.set_location('hr_reports.split_attributes',10);
1379 --
1380 if (p_aol_seperator_flag = true) then
1381 --
1382 begin
1383 select CONCATENATED_SEGMENT_DELIMITER
1384 into l_seg_sep
1385 from FND_DESCRIPTIVE_FLEXS_VL
1386 where DESCRIPTIVE_FLEXFIELD_NAME = P_TITLE;
1387 exception
1388 when NO_DATA_FOUND then null;
1389 end;
1390 --
1391 else
1392 l_seg_sep := fnd_global.local_chr(127);
1393 end if;
1394 --
1395 hr_utility.set_location('hr_reports.split_attributes',10);
1396 --
1397 l_concat_segs := l_seg_sep || p_concatenated_segments || l_seg_sep;
1398 l_no_of_segs := length(l_concat_segs)
1399 - length(replace(l_concat_segs,l_seg_sep));
1400 --
1401 while l_count < l_no_of_segs loop
1402 l_seg_len := instr(l_concat_segs, l_seg_sep,1 , l_count+1)
1403 - instr(l_concat_segs, l_seg_sep,1, l_count) - 1;
1404 l_start_pos := instr(l_concat_segs, l_seg_sep,1, l_count) + 1;
1405 p_segtab(l_count) := substr(l_concat_segs,l_start_pos,l_seg_len);
1406 p_segments_used := l_count;
1407 l_count := l_count + 1;
1408 end loop;
1409 --
1410 end split_attributes;
1411 --
1412 --
1413 procedure get_segments
1414 (p_concatenated_segments IN VARCHAR2
1415 ,p_id_flex_num IN NUMBER
1416 ,p_segments_used OUT NOCOPY NUMBER
1417 ,p_value1 OUT NOCOPY VARCHAR2
1418 ,p_value2 OUT NOCOPY VARCHAR2
1419 ,p_value3 OUT NOCOPY VARCHAR2
1420 ,p_value4 OUT NOCOPY VARCHAR2
1421 ,p_value5 OUT NOCOPY VARCHAR2
1422 ,p_value6 OUT NOCOPY VARCHAR2
1423 ,p_value7 OUT NOCOPY VARCHAR2
1424 ,p_value8 OUT NOCOPY VARCHAR2
1425 ,p_value9 OUT NOCOPY VARCHAR2
1426 ,p_value10 OUT NOCOPY VARCHAR2
1427 ,p_value11 OUT NOCOPY VARCHAR2
1428 ,p_value12 OUT NOCOPY VARCHAR2
1429 ,p_value13 OUT NOCOPY VARCHAR2
1430 ,p_value14 OUT NOCOPY VARCHAR2
1431 ,p_value15 OUT NOCOPY VARCHAR2
1432 ,p_value16 OUT NOCOPY VARCHAR2
1433 ,p_value17 OUT NOCOPY VARCHAR2
1434 ,p_value18 OUT NOCOPY VARCHAR2
1435 ,p_value19 OUT NOCOPY VARCHAR2
1436 ,p_value20 OUT NOCOPY VARCHAR2
1437 ,p_value21 OUT NOCOPY VARCHAR2
1438 ,p_value22 OUT NOCOPY VARCHAR2
1439 ,p_value23 OUT NOCOPY VARCHAR2
1440 ,p_value24 OUT NOCOPY VARCHAR2
1441 ,p_value25 OUT NOCOPY VARCHAR2
1442 ,p_value26 OUT NOCOPY VARCHAR2
1443 ,p_value27 OUT NOCOPY VARCHAR2
1444 ,p_value28 OUT NOCOPY VARCHAR2
1445 ,p_value29 OUT NOCOPY VARCHAR2
1446 ,p_value30 OUT NOCOPY VARCHAR2 ) IS
1447 --
1448 l_segtab hr_reports.SegmentTabType;
1449 l_segments_used NUMBER(10);
1450 --
1451 begin
1452 --
1453 hr_utility.trace('Entered hr_reports.get_segments');
1454 --
1455 hr_utility.set_location ('hr_reports.get_segments', 5);
1456 hr_reports.split_segments(p_concatenated_segments
1457 ,p_id_flex_num
1458 ,l_segtab
1459 ,l_segments_used);
1460 --
1461 for i in l_segments_used + 1..30 loop
1462 l_segtab(i) := null;
1463 end loop;
1464 --
1465 hr_utility.set_location ('hr_reports.get_segments', 10);
1466 --
1467 p_segments_used := l_segments_used;
1468 p_value1 := l_segtab(1);
1469 p_value2 := l_segtab(2);
1470 p_value3 := l_segtab(3);
1471 p_value4 := l_segtab(4);
1472 p_value5 := l_segtab(5);
1473 p_value6 := l_segtab(6);
1474 p_value7 := l_segtab(7);
1475 p_value8 := l_segtab(8);
1476 p_value9 := l_segtab(9);
1477 p_value10 := l_segtab(10);
1478 p_value11 := l_segtab(11);
1479 p_value12 := l_segtab(12);
1480 p_value13 := l_segtab(13);
1481 p_value14 := l_segtab(14);
1482 p_value15 := l_segtab(15);
1483 p_value16 := l_segtab(16);
1484 p_value17 := l_segtab(17);
1485 p_value18 := l_segtab(18);
1486 p_value19 := l_segtab(19);
1487 p_value20 := l_segtab(20);
1488 p_value21 := l_segtab(21);
1489 p_value22 := l_segtab(22);
1490 p_value23 := l_segtab(23);
1491 p_value24 := l_segtab(24);
1492 p_value25 := l_segtab(25);
1493 p_value26 := l_segtab(26);
1494 p_value27 := l_segtab(27);
1495 p_value28 := l_segtab(28);
1496 p_value29 := l_segtab(29);
1497 p_value30 := l_segtab(30);
1498 --
1499 SegmentValue1 := l_segtab(1);
1500 end get_segments;
1501 --
1502 --
1503 procedure get_desc_flex
1504 (
1505 p_appl_short_name in varchar2,
1506 p_desc_flex_name in varchar2,
1507 p_table_alias in varchar2,
1508 p_title out nocopy varchar2,
1509 p_label_expr out nocopy varchar2,
1510 p_column_expr out nocopy varchar2
1511 ) is
1512 --
1513 cursor csr_flex_columns(p_application_id number,
1514 p_desc_flex_name varchar2) is
1515 select 1 order_col,
1516 dfcu.column_seq_num order_col2,
1517 dfcu.application_column_name column_name,
1518 replace(dfcu.form_left_prompt,'''','''''') label
1519 from fnd_descr_flex_contexts dfc,
1520 fnd_descr_flex_col_usage_vl dfcu
1521 where dfc.descriptive_flexfield_name = p_desc_flex_name
1522 and dfc.application_id = p_application_id
1523 and dfc.global_flag = 'Y'
1524 and dfc.enabled_flag = 'Y'
1525 and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
1526 and dfcu.descriptive_flexfield_name = p_desc_flex_name
1527 and dfcu.application_id = p_application_id
1528 and dfcu.enabled_flag = 'Y'
1529 UNION
1530 select distinct
1531 2 order_col,
1532 1 order_col2,
1533 dfcu.application_column_name column_name,
1534 replace(dfcu.form_left_prompt,'''','''''') label
1535 from fnd_descr_flex_contexts dfc,
1536 fnd_descr_flex_col_usage_vl dfcu
1537 where dfc.descriptive_flexfield_name = p_desc_flex_name
1538 and dfc.application_id = p_application_id
1539 and dfc.global_flag = 'N'
1540 and dfc.enabled_flag = 'Y'
1541 and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
1542 and dfcu.descriptive_flexfield_name = p_desc_flex_name
1543 and dfcu.application_id = p_application_id
1544 and dfcu.enabled_flag = 'Y'
1545 order by 1,2,3;
1546 --
1547 v_title varchar2(400);
1548 v_column_expr varchar2(32000);
1549 v_label_expr varchar2(32000);
1550 v_delimiter varchar2(1);
1551 v_application_id number;
1552 --
1553 begin
1554 --
1555 select app.application_id
1556 into v_application_id
1557 from fnd_application app
1558 where upper(app.application_short_name) = upper(p_appl_short_name);
1559 --
1560 select df.concatenated_segment_delimiter,
1561 df.title
1562 into v_delimiter,
1563 v_title
1564 from fnd_descriptive_flexs_vl df
1565 where df.descriptive_flexfield_name = p_desc_flex_name
1566 and df.application_id = v_application_id;
1567 --
1568 for flex_col in csr_flex_columns(v_application_id,
1569 p_desc_flex_name) loop
1570 --
1571 if v_column_expr is null then
1572 --
1573 v_column_expr := p_table_alias || '.' || flex_col.column_name;
1574 v_label_expr := flex_col.label;
1575 --
1576 else
1577 --
1578 v_column_expr := v_column_expr || '||''' || v_delimiter || '''||' ||
1579 p_table_alias || '.' || flex_col.column_name;
1580 v_label_expr := v_label_expr || '.' || flex_col.label;
1581 --
1582 end if;
1583 --
1584 end loop;
1585 --
1586 --
1587 --
1588 --
1589 --
1590
1591
1592
1593
1594
1595 p_title := v_title;
1596 p_label_expr := v_label_expr;
1597 p_column_expr := v_column_expr;
1598 --
1599 --
1600 end get_desc_flex;
1601 --
1602 procedure get_desc_flex_context
1603 (
1604 p_appl_short_name in varchar2,
1605 p_desc_flex_name in varchar2,
1606 p_table_alias in varchar2,
1607 p_title out nocopy varchar2,
1608 p_label_expr out nocopy varchar2,
1609 p_column_expr out nocopy varchar2
1610 ) is
1611 begin
1612 hr_reports.get_desc_flex_context
1613 (p_appl_short_name,
1614 p_desc_flex_name,
1615 p_table_alias,
1616 'Y',
1617 p_title,
1618 p_label_expr,
1619 p_column_expr);
1620 end;
1621 --
1622 procedure get_desc_flex_context
1623 (
1624 p_appl_short_name in varchar2,
1625 p_desc_flex_name in varchar2,
1626 p_table_alias in varchar2,
1627 p_display in varchar2,
1628 p_title out nocopy varchar2,
1629 p_label_expr out nocopy varchar2,
1630 p_column_expr out nocopy varchar2
1631 ) is
1632 cursor csr_flex_columns(p_application_id number,
1633 p_desc_flex_name varchar2) is
1634 select 1 order_col,
1635 dfcu.column_seq_num order_col2,
1636 dfcu.application_column_name column_name,
1637 replace(dfcu.form_left_prompt,'''','''''') label,
1638 'Y' global_flag,
1639 dfc.descriptive_flex_context_code context
1640 from fnd_descr_flex_contexts dfc,
1641 fnd_descr_flex_col_usage_vl dfcu
1642 where dfc.descriptive_flexfield_name = p_desc_flex_name
1643 and dfc.application_id = p_application_id
1644 and dfc.global_flag = 'Y'
1645 and dfc.enabled_flag = 'Y'
1646 and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
1647 and dfcu.descriptive_flexfield_name = p_desc_flex_name
1648 and dfcu.application_id = p_application_id
1649 and dfcu.enabled_flag = 'Y'
1650 and dfcu.display_flag = NVL(p_display,dfcu.display_flag)
1651 UNION
1652 select distinct
1653 2 order_col,
1654 1 order_col2,
1655 dfcu.application_column_name column_name,
1656 replace(dfcu.form_left_prompt,'''','''''') label,
1657 'N' global_flag,
1658 dfc.descriptive_flex_context_code context
1659 from fnd_descr_flex_contexts dfc,
1660 fnd_descr_flex_col_usage_vl dfcu
1661 where dfc.descriptive_flexfield_name = p_desc_flex_name
1662 and dfc.application_id = p_application_id
1663 and dfc.global_flag = 'N'
1664 and dfc.enabled_flag = 'Y'
1665 and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
1666 and dfcu.descriptive_flexfield_name = p_desc_flex_name
1667 and dfcu.application_id = p_application_id
1668 and dfcu.enabled_flag = 'Y'
1669 and dfcu.display_flag = NVL(p_display,dfcu.display_flag)
1670 order by 1,6,2;
1671 --
1672 v_title varchar2(60);
1673 v_column_expr varchar2(32000);
1674 v_label_expr varchar2(32000);
1675 v_delimiter varchar2(1);
1676 v_application_id number;
1677 v_column varchar2(250);
1678 v_column_name varchar2(200);
1679 v_attribute_category varchar2(100);
1680 v_label_name varchar2(200);
1681 v_label varchar2(250);
1682 --
1683 --
1684 begin
1685 --
1686 select app.application_id
1687 into v_application_id
1688 from fnd_application app
1689 where upper(app.application_short_name) = upper(p_appl_short_name);
1690 --
1691 select df.concatenated_segment_delimiter,
1692 df.title
1693 into v_delimiter,
1694 v_title
1695 from fnd_descriptive_flexs_vl df
1696 where df.descriptive_flexfield_name = p_desc_flex_name
1697 and df.application_id = v_application_id;
1698 --
1699 for flex_col in csr_flex_columns(v_application_id,
1700 p_desc_flex_name) loop
1701 --
1702 if flex_col.global_flag = 'Y' then
1703 if v_column_expr is null then
1704 v_column := p_table_alias || '.' || flex_col.column_name
1705 || '||'''||v_delimiter||'''';
1706 v_label :=''''|| flex_col.label||'''' || '||'''||v_delimiter
1707 || '''';
1708
1709 else
1710 v_column :='||'|| p_table_alias ||'.'|| flex_col.column_name
1711 || '||''' ||v_delimiter|| '''';
1712 v_label := '||'|| ''''||flex_col.label||'''' || '||''' || v_delimiter
1713 || '''' ;
1714 end if;
1715 --
1716 --
1717 else
1718 --
1719 if p_table_alias = 'asg' then
1720 v_attribute_category := p_table_alias || '.' || 'ass_attribute_category';
1721 elsif p_table_alias = 'addr' then
1722 v_attribute_category := p_table_alias || '.' || 'addr_attribute_category';
1723 elsif p_table_alias = 'app' then
1724 v_attribute_category := p_table_alias || '.' || 'appl_attribute_category';
1725 elsif p_table_alias = 'con' then
1726 v_attribute_category := p_table_alias || '.' || 'cont_attribute_category';
1727 elsif p_table_alias = 'paei' then
1728 v_attribute_category := p_table_alias || '.' || 'aei_information_category';
1729 elsif p_table_alias = 'f' then
1730 v_attribute_category := p_table_alias || '.' || 'aei_information_category';
1731 else
1732 v_attribute_category := p_table_alias || '.' || 'attribute_category';
1733 end if;
1734 v_label_name := flex_col.label;
1735 v_column_name := p_table_alias ||'.'||flex_col.column_name;
1736 --
1737 if v_column_expr is null then
1738 v_label :=substrb(
1739 'decode('||v_attribute_category||','''||flex_col.context||''','||
1740 '''' ||v_label_name||''''|| '||''' ||v_delimiter || '''' ||',null)',32000);
1741 --
1742 v_column :=substrb(
1743 'decode('||v_attribute_category||','''||flex_col.context||''','||
1744 v_column_name|| '||''' ||v_delimiter||''''||',null)',32000);
1745 --
1746 else
1747 --
1748 v_label := substrb(
1749 '||decode('||v_attribute_category||','''||flex_col.context||''','||
1750 ''''||v_label_name||''''|| '||''' || v_delimiter ||
1751 '''' ||',null)',32000);
1752 --
1753 v_column := substrb(
1754 '||decode('||v_attribute_category||','''||flex_col.context||''','||
1755 v_column_name|| '||''' || v_delimiter || ''''
1756 ||',null)',32000);
1757 --
1758 end if;
1759 end if;
1760 --
1761 if v_column_expr is null then
1762 --
1763 v_column_expr := v_column;
1764 v_label_expr := v_label;
1765 --
1766 else
1767 --
1768 v_column_expr := v_column_expr || v_column;
1769 v_label_expr := v_label_expr || v_label;
1770 --
1771 end if;
1772 --
1773 end loop;
1774 --
1775 p_title := v_title;
1776 p_label_expr := v_label_expr;
1777 p_column_expr := v_column_expr;
1778 --
1779 end get_desc_flex_context;
1780 --
1781 -- Added for bug fix 622283, version 110.6
1782 --
1783 procedure get_dvlpr_desc_flex
1784 (
1785 p_appl_short_name in varchar2,
1786 p_desc_flex_name in varchar2,
1787 p_desc_flex_context in varchar2,
1788 p_table_alias in varchar2,
1789 p_title out nocopy varchar2,
1790 p_label_expr out nocopy varchar2,
1791 p_column_expr out nocopy varchar2
1792 ) is
1793 --
1794 l_aol_seperator_flag boolean := true;
1795 --
1796 begin
1797 --
1798 hr_reports.get_dvlpr_desc_flex( p_appl_short_name
1799 ,p_desc_flex_name
1800 ,p_desc_flex_context
1801 ,p_table_alias
1802 ,l_aol_seperator_flag
1803 ,p_title
1804 ,p_label_expr
1805 ,p_column_expr
1806 );
1807 --
1808 end get_dvlpr_desc_flex;
1809 --
1810 --
1811 -- Added for bug fix 622283, version 110.6
1812 --
1813 procedure get_dvlpr_desc_flex
1814 (
1815 p_appl_short_name in varchar2,
1816 p_desc_flex_name in varchar2,
1817 p_desc_flex_context in varchar2,
1818 p_table_alias in varchar2,
1819 p_aol_seperator_flag in boolean,
1820 p_title out nocopy varchar2,
1821 p_label_expr out nocopy varchar2,
1822 p_column_expr out nocopy varchar2
1823 ) is
1824 --
1825 cursor csr_flex_columns(p_application_id number,
1826 p_desc_flex_name varchar2,
1827 p_desc_flex_context varchar2) is
1828 select 1 order_col,
1829 dfcu.column_seq_num order_col2,
1830 dfcu.application_column_name column_name,
1831 replace(dfcu.form_left_prompt,'''','''''') label
1832 from fnd_descr_flex_contexts dfc,
1833 fnd_descr_flex_col_usage_vl dfcu
1834 where dfc.descriptive_flexfield_name = p_desc_flex_name
1835 and dfc.application_id = p_application_id
1836 and dfc.global_flag = 'Y'
1837 and dfc.enabled_flag = 'Y'
1838 and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
1839 and dfcu.descriptive_flexfield_name = p_desc_flex_name
1840 and dfcu.application_id = p_application_id
1841 and dfcu.enabled_flag = 'Y'
1842 UNION
1843 select distinct
1844 2 order_col,
1845 dfcu.column_seq_num order_col2,
1846 dfcu.application_column_name column_name,
1847 replace(dfcu.form_left_prompt,'''','''''') label
1848 from fnd_descr_flex_contexts dfc,
1849 fnd_descr_flex_col_usage_vl dfcu
1850 where dfc.descriptive_flexfield_name = p_desc_flex_name
1851 and dfc.application_id = p_application_id
1852 and dfc.descriptive_flex_context_code = p_desc_flex_context
1853 and dfc.global_flag = 'N'
1854 and dfc.enabled_flag = 'Y'
1855 and dfcu.descriptive_flex_context_code = dfc.descriptive_flex_context_code
1856
1857 and dfcu.descriptive_flexfield_name = p_desc_flex_name
1858 and dfcu.application_id = p_application_id
1859 and dfcu.enabled_flag = 'Y'
1860 order by 1,2;
1861 --
1862 v_title varchar2(60);
1863 v_column_expr varchar2(2000);
1864 v_label_expr varchar2(2000);
1865 v_delimiter varchar2(1);
1866 v_application_id number;
1867 --
1868 begin
1869 --
1870 select app.application_id
1871 into v_application_id
1872 from fnd_application app
1873 where upper(app.application_short_name) = upper(p_appl_short_name);
1874 --
1875 select df.concatenated_segment_delimiter,
1876 df.title
1877 into v_delimiter,
1878 v_title
1879 from fnd_descriptive_flexs_vl df
1880 where df.descriptive_flexfield_name = p_desc_flex_name
1881 and df.application_id = v_application_id;
1882 --
1883 if (p_aol_seperator_flag = false) then
1884 --
1885 v_delimiter := fnd_global.local_chr(127);
1886 --
1887 end if;
1888 --
1889 for flex_col in csr_flex_columns(v_application_id,
1890 p_desc_flex_name,
1891 p_desc_flex_context) loop
1892 --
1893 if v_column_expr is null then
1894 --
1895 v_column_expr := p_table_alias || '.' || flex_col.column_name;
1896 v_label_expr := flex_col.label;
1897 --
1898 else
1899 --
1900 v_column_expr := v_column_expr || '||''' || v_delimiter || '''||' ||
1901 p_table_alias || '.' || flex_col.column_name;
1902 v_label_expr := v_label_expr || '.' || flex_col.label;
1903 --
1904 end if;
1905 --
1906 end loop;
1907 --
1908 p_title := v_title;
1909 p_label_expr := v_label_expr;
1910 p_column_expr := v_column_expr;
1911 --
1912 end get_dvlpr_desc_flex;
1913 --
1914 --
1915 function get_person_name
1916 (p_session_date date,
1917 p_person_id number) return varchar2
1918 --
1919 as
1920 v_person_name per_all_people_f.full_name%type;
1921 --
1922 begin
1923 --
1924 hr_utility.trace('entered hr_reports.get_person_name');
1925 --
1926 hr_utility.set_location('hr_reports.get_person_name',5);
1927 if p_person_id is null then
1928 null;
1929 else
1930 begin
1931 hr_utility.set_location('hr_reports.get_person_name',10);
1932 select p.full_name
1933 into v_person_name
1934 from per_all_people_f p
1935 where p.person_id = p_person_id
1936 and p_session_date between p.effective_start_date
1937 and p.effective_end_date;
1938 exception
1939 when no_data_found then null;
1940 end;
1941 end if;
1942 --
1943 hr_utility.trace('leaving hr_reports.get_person_name');
1944 --
1945 return v_person_name;
1946 --
1947 end get_person_name;
1948 --
1949 function get_party_number
1950 (p_party_id in number) return varchar2 as
1951 --
1952 l_party_number hz_parties.party_number%type;
1953 --
1954 begin
1955 --
1956 hr_utility.set_location('Entering hr_reports.get_party_number',5);
1957 --
1958 begin
1959 --
1960 select party_number
1961 into l_party_number
1962 from hz_parties
1963 where party_id = p_party_id;
1964 --
1965 hr_utility.set_location('hr_reports.get_party_number',10);
1966 --
1967 exception
1968 when no_data_found then
1969 null;
1970 hr_utility.set_location('hr_reports.get_party_number',15);
1971 end;
1972 --
1973 hr_utility.set_location('Leaving hr_reports.get_party_number',20);
1974 --
1975 return l_party_number;
1976 --
1977 end get_party_number;
1978 --
1979 end hr_reports;