[Home] [Help]
PACKAGE BODY: APPS.GHR_BREAKDOWN_RPT
Source
1 PACKAGE BODY ghr_breakdown_rpt AS
2 /* $Header: ghbrkdwn.pkb 120.1 2005/07/01 02:12:45 asubrahm noship $ */
3 --
4 l_agcy_and_selm VARCHAR2(4) := '%';
5 l_by_clause VARCHAR2(80) := 'GRADE/LEVEL';
6 l_within_clause VARCHAR2(80) := 'PAY_PLAN';
7 l_for_clause NUMBER(3) := 0;
8 l_extra_clause VARCHAR2(80);
9 l_org_strver_id per_org_structure_versions.org_structure_version_id%TYPE;
10
11 PROCEDURE Set_Effective_Date(p_date IN DATE)
12 IS
13 BEGIN
14 l_effective_date := p_date;
15 END;
16
17 FUNCTION Effective_Date RETURN DATE
18 IS
19 BEGIN
20 RETURN l_effective_date;
21 END;
22
23 PROCEDURE Set_Agency(p_agency IN VARCHAR2,
24 p_subelm IN VARCHAR2)
25 IS
26 BEGIN
27 l_agcy_and_selm := p_agency || NVL(p_subelm, '%');
28 END;
29
30 FUNCTION Agency_Subelement RETURN VARCHAR2
31 IS
32 BEGIN
33 RETURN l_agcy_and_selm;
34 END;
35
36 PROCEDURE Set_By_Clause(p_name IN VARCHAR2)
37 IS
38 BEGIN
39 l_by_clause := UPPER(p_name);
40 END;
41
42 PROCEDURE Set_within_clause(p_name IN VARCHAR2)
43 IS
44 BEGIN
45 l_within_clause := UPPER(p_name);
46 END;
47
48 PROCEDURE set_for_clause(p_value IN NUMBER)
49 IS
50 BEGIN
51 l_for_clause := p_value;
52 END;
53
54 FUNCTION get_for_clause RETURN NUMBER
55 IS
56 BEGIN
57 RETURN l_for_clause;
58 END;
59
60 PROCEDURE set_extra_clause(p_name IN VARCHAR2)
61 IS
62 BEGIN
63 l_extra_clause := UPPER(p_name);
64 END;
65
66 PROCEDURE set_hierarchy(p_org_strver_id IN NUMBER)
67 IS
68 BEGIN
69 l_org_strver_id := p_org_strver_id;
70 END;
71
72 FUNCTION get_hierarchy_level(p_position_id IN NUMBER, p_effective_date IN DATE)
73 RETURN NUMBER
74 IS
75 l_level NUMBER(3);
76 l_orgid hr_all_positions_f.organization_id%TYPE;
77 CURSOR c_level IS
78 SELECT DECODE(ORG.organization_id_child, l_orgid, LEVEL+1, LEVEL) ORG_LEVEL
79 FROM per_org_structure_elements ORG
80 WHERE (ORG.organization_id_child = l_orgid OR
81 ORG.organization_id_parent = l_orgid)
82 AND ORG.org_structure_version_id = l_org_strver_id
83 CONNECT
84 BY
85 PRIOR organization_id_child = organization_id_parent
86 AND PRIOR org_structure_version_id = org_structure_version_id
87 START
88 WITH organization_id_parent
89 NOT IN (SELECT organization_id_child
90 FROM per_org_structure_elements);
91 BEGIN
92 l_level := 0;
93 IF l_for_clause > 0 THEN
94 SELECT organization_id
95 INTO l_orgid
96 FROM hr_all_positions_f
97 WHERE position_id = p_position_id
98 AND p_effective_date BETWEEN effective_start_date AND effective_end_date;
99 OPEN c_level;
100 FETCH c_level INTO l_level;
101 IF c_level%NOTFOUND THEN
102 l_level := -1;
103 END IF;
104 CLOSE c_level;
105 END IF;
106 RETURN (l_level);
107 END;
108
109 FUNCTION get_hierarchy_codes (p_ASG_rowid IN ROWID, p_effective_date IN DATE,
110 p_mode IN VARCHAR2 := 'PARENTS')
111 RETURN VARCHAR2
112 IS
113 l_prvorg VARCHAR2(15);
114 l_result VARCHAR2(80);
115 l_count INTEGER;
116 CURSOR c_codes IS
117 SELECT LEVEL org_level,
118 organization_id_parent,
119 organization_id_child,
120 org_structure_version_id
121 FROM per_org_structure_elements
122 WHERE org_structure_version_id = l_org_strver_id
123 CONNECT
124 BY
125 PRIOR organization_id_parent = organization_id_child
126 START
127 WITH organization_id_child = (SELECT POS.organization_id
128 FROM hr_all_positions_f POS,
129 per_assignments_f ASG
130 WHERE ASG.rowid = p_ASG_rowid
131 AND ASG.position_id = POS.position_id
132 AND p_effective_date BETWEEN POS.effective_start_date
133 AND POS.effective_end_date)
134 ORDER BY LEVEL DESC;
135 BEGIN
136 l_result := NULL;
137 l_count := 1;
138 FOR r_codes IN c_codes LOOP
139 IF p_mode = 'ALL' OR
140 (l_count < l_for_clause AND p_mode = 'PARENTS')
141 THEN
142 IF l_result IS NULL THEN
143 l_result := TO_CHAR(r_codes.organization_id_parent);
144 l_prvorg := TO_CHAR(r_codes.organization_id_child);
145 ELSE
146 l_result := l_result || '-' || TO_CHAR(r_codes.organization_id_parent);
147 l_prvorg := TO_CHAR(r_codes.organization_id_child);
148 END IF;
149 l_count := l_count + 1;
150 ELSE
151 IF l_for_clause = 1 THEN
152 l_result := r_codes.organization_id_parent;
153 END IF;
154 EXIT;
155 END IF;
156 END LOOP;
157 IF l_result IS NOT NULL AND l_prvorg IS NOT NULL THEN
158 l_result := l_result || '-' || l_prvorg;
159 ELSIF l_result IS NULL AND p_mode = 'PARENTS' THEN
160 SELECT POS.organization_id
161 INTO l_result
162 FROM hr_all_positions_f POS,
163 per_assignments_f ASG
164 WHERE ASG.rowid = p_ASG_rowid
165 AND ASG.position_id = POS.position_id
166 AND p_effective_date BETWEEN POS.effective_start_date
167 AND POS.effective_end_date;
168 END IF;
169 RETURN l_result;
170 END;
171
172 FUNCTION decode_lookup(p_lookup_type IN VARCHAR2,
173 p_lookup_code IN VARCHAR2)
174 RETURN VARCHAR2
175 IS
176 l_result VARCHAR2(80);
177 CURSOR csr_lookup IS
178 SELECT meaning
179 FROM hr_lookups
180 WHERE lookup_type = p_lookup_type
181 AND lookup_code = p_lookup_code
182 AND enabled_flag = 'Y'
183 AND TRUNC(l_effective_date)
184 BETWEEN NVL(start_date_active, TRUNC(l_effective_date))
185 AND NVL(end_date_active, TRUNC(l_effective_date));
186 BEGIN
187 open csr_lookup;
188 fetch csr_lookup into l_result;
189 close csr_lookup;
190 RETURN l_result;
191 END;
192
193
194
195 -- --------------------------------------------------------------------------
196 -- |--------------------------< return_special_information >----------------|
197 -- --------------------------------------------------------------------------
198
199 Procedure return_special_information
200 (p_person_id in number
201 ,p_structure_name in varchar2
202 ,p_effective_date in date
203 ,p_special_info OUT NOCOPY ghr_api.special_information_type
204 )
205 is
206 l_proc varchar2(72) := 'return_special_information ';
207 l_id_flex_num fnd_id_flex_structures.id_flex_num%type;
208 l_max_segment per_analysis_criteria.segment1%type;
209
210 Cursor c_flex_num is
211 select flx.id_flex_num
212 from fnd_id_flex_structures_tl flx
213 where flx.id_flex_code = 'PEA' --
214 and flx.application_id = 800 --
215 and flx.id_flex_structure_name = p_structure_name
216 and flx.language = 'US';
217
218 Cursor c_sit is
219 select pea.analysis_criteria_id,
220 pan.date_from, -- added for bug fix : 609285
221 pea.start_date_active,
222 pea.segment1,
223 pea.segment2,
224 pea.segment3,
225 pea.segment4,
226 pea.segment5,
227 pea.segment6,
228 pea.segment7,
229 pea.segment8,
230 pea.segment9,
231 pea.segment10,
232 pea.segment11,
233 pea.segment12,
234 pea.segment13,
235 pea.segment14,
236 pea.segment15,
237 pea.segment16,
238 pea.segment17,
239 pea.segment18,
240 pea.segment19,
241 pea.segment20
242 from per_analysis_Criteria pea,
243 per_person_analyses pan
244 where pan.person_id = p_person_id
245 and pan.id_flex_num = l_id_flex_num
246 and pea.analysis_Criteria_id = pan.analysis_criteria_id
247 and p_effective_date
248 between nvl(pan.date_from,p_effective_date)
249 and nvl(pan.date_to,p_effective_date)
250 and p_effective_date
251 between nvl(pea.start_date_active,p_effective_date)
252 and nvl(pea.end_date_active,p_effective_date)
253 order by 2 desc ;
254
255
256 begin
257
258 for flex_num in c_flex_num loop
259 l_id_flex_num := flex_num.id_flex_num;
260 End loop;
261
262 for special_info in c_sit loop
263 p_special_info.segment1 := special_info.segment1;
264 p_special_info.segment2 := special_info.segment2;
265 p_special_info.segment3 := special_info.segment3;
266 p_special_info.segment4 := special_info.segment4;
267 p_special_info.segment5 := special_info.segment5;
268 p_special_info.segment6 := special_info.segment6;
269 p_special_info.segment7 := special_info.segment7;
270 p_special_info.segment8 := special_info.segment8;
271 p_special_info.segment9 := special_info.segment9;
272 p_special_info.segment10 := special_info.segment10;
273 p_special_info.segment11 := special_info.segment11;
274 p_special_info.segment12 := special_info.segment12;
275 p_special_info.segment13 := special_info.segment13;
276 p_special_info.segment14 := special_info.segment14;
277 p_special_info.segment15 := special_info.segment15;
278 p_special_info.segment16 := special_info.segment16;
279 p_special_info.segment17 := special_info.segment17;
280 p_special_info.segment18 := special_info.segment18;
281 p_special_info.segment19 := special_info.segment19;
282 p_special_info.segment20 := special_info.segment20;
283 exit;
284 End loop;
285 EXCEPTION
286 when others then
287 -- NOCOPY changes
288 -- Reset IN OUT params and set OUT params
289 p_special_info := null;
290 raise;
291 End return_special_information;
292
293 FUNCTION Any_Clause(p_clause IN VARCHAR2,
294 p_PER_rowid IN ROWID,
295 p_PEI_GRP1 IN per_people_extra_info%ROWTYPE,
296 p_ASG IN per_all_assignments_f%ROWTYPE)
297 RETURN VARCHAR2
298 IS
299 l_result VARCHAR2(300);
300 l_rescpy VARCHAR2(300);
301 l_int INTEGER;
302 -- record structure for special Info 'US Fed Perf Appraisal'
303 l_special_info ghr_api.special_information_type;
304 l_posei_data per_position_extra_info%ROWTYPE;
305 BEGIN
306 IF p_clause = 'GRADE/LEVEL' -- Grade or Level
307 THEN
308 SELECT GDF.segment2
309 INTO l_result
310 FROM per_grades GRD
311 ,per_grade_definitions GDF
312 WHERE GRD.grade_id = p_ASG.grade_id
313 AND GDF.grade_definition_id = GRD.grade_definition_id;
314 ELSIF p_clause = 'OCCODE_PATCOB' -- Occupational Category (PATCOB) Code
315 THEN
316 ghr_history_fetch.fetch_positionei(p_ASG.position_id, 'GHR_US_POS_GRP1',
317 l_effective_date, l_posei_data);
318 l_result := l_posei_data.poei_information6 || ' - ' ||
319 decode_lookup('GHR_US_OCC_CATEGORY_CODE', l_posei_data.poei_information6);
320 ELSIF p_clause = 'SERIES' -- Occupational Series
321 THEN
322 SELECT job.name || ' - ' || decode_lookup('GHR_US_OCC_SERIES', job.name)
323 INTO l_result
324 FROM hr_all_positions_f POS,
325 per_jobs job
326 WHERE POS.position_id = p_ASG.position_id
327 AND TRUNC(effective_date) BETWEEN POS.effective_start_date
328 AND POS.effective_end_date
329 AND JOB.job_id = POS.job_id;
330 ELSIF p_clause = 'GRADE' -- Grade
331 THEN
332 SELECT GRD.name
333 INTO l_result
334 FROM per_grades GRD
335 WHERE GRD.grade_id = p_ASG.grade_id;
336 ELSIF p_clause = 'APPOINTMENT_TYPE' -- Appointment Type
337 THEN
338 l_result := p_PEI_GRP1.pei_information3 || ' - ' ||
339 decode_lookup('GHR_US_APPOINTMENT_TYPE', p_PEI_GRP1.pei_information3);
340 ELSIF p_clause = 'HANDICAP_GROUP' -- Handicap Group
341 THEN
342 l_result := p_PEI_GRP1.pei_information11;
343 l_rescpy := l_result;
344 IF l_result = '13'
345 THEN
346 l_result := 'Speech Impairments';
347 ELSIF l_result in ('15', '16', '17')
348 THEN
349 l_result := 'Hearing Impairments';
350 ELSIF l_result in ('22', '23', '24', '25')
351 THEN
352 l_result := 'Vision Impairments';
353 ELSIF l_result in ('27', '28', '29', '32', '33', '34',
354 '35', '36', '37', '38')
355 THEN
356 l_result := 'Absences of Extremities';
357 ELSIF l_result in ('44', '45', '46', '47', '48', '49', '57', '61',
358 '62', '63', '64', '65', '66', '67', '68')
359 THEN
360 l_result := 'Nonparalytic Orthopedic Impairments, chronic pain, stiffnes or weakness';
361 ELSIF l_result IN ('70', '71', '72', '73', '74', '75', '76', '77', '78')
362 THEN
363 l_result := 'Complete Paralysis';
364 ELSIF l_result IN ('80', '81', '82', '83', '84', '86', '87', '88', '89',
365 '90', '91', '92', '93', '94')
366 THEN
367 l_result := 'Other Impairments';
368 ELSE
369 l_result := 'No Handicap';
370 END IF;
371 SELECT NVL(LKP.description, l_result)
372 INTO l_result
373 FROM hr_lookups LKP
374 WHERE LKP.lookup_type = 'GHR_US_HANDICAP_CODE'
375 AND LKP.lookup_code = l_rescpy
376 AND LKP.enabled_flag = 'Y'
377 AND TRUNC(l_effective_date)
378 BETWEEN NVL(LKP.start_date_active, TRUNC(l_effective_date))
379 AND NVL(LKP.end_date_active, TRUNC(l_effective_date));
380 ELSIF p_clause = 'TARGET_HANDICAP_CODE' -- Target Handicap Code
381 THEN
382 l_result := p_PEI_GRP1.pei_information11 || ' - ' ||
383 decode_lookup('GHR_US_HANDICAP_CODE', p_PEI_GRP1.pei_information11);
384 ELSIF p_clause = 'AA_CATEGORY' -- AA Category
385 THEN
386 BEGIN
387 ghr_history_fetch.fetch_positionei(p_ASG.position_id, 'GHR_US_POS_GRP1',
388 l_effective_date, l_posei_data);
389 SELECT NVL(LKP.description, LKP.meaning)
390 INTO l_result
391 FROM hr_lookups LKP
392 WHERE LKP.lookup_type = 'GHR_US_OCC_CATEGORY_CODE'
393 AND LKP.lookup_code = l_posei_data.poei_information6
394 AND LKP.enabled_flag = 'Y'
395 AND TRUNC(l_effective_date)
396 BETWEEN NVL(LKP.start_date_active, TRUNC(l_effective_date))
397 AND NVL(LKP.end_date_active, TRUNC(l_effective_date));
398 EXCEPTION
399 WHEN NO_DATA_FOUND THEN
400 l_result := NULL;
401 END;
402 l_int := TRUNC((TO_NUMBER(Any_Clause('GRADE/LEVEL', p_PER_rowid, p_PEI_GRP1, p_ASG))-1)/4, 0);
403 l_result := l_result ||
404 ' ' ||
405 Any_Clause('PAY_PLAN', p_PER_rowid, p_PEI_GRP1, p_ASG) ||
406 ' ' ||
407 'GRDS ' || RTRIM(TO_CHAR((l_int*4)+1, '09')) || ' - ' || RTRIM(TO_CHAR((l_int+1)*4, '09'));
408 ELSIF p_clause = 'PERFORMANCE_RATING' -- Performance Rating level
409 THEN
410 SELECT person_id
411 INTO l_result
412 FROM per_people_f
413 WHERE rowid = p_PER_rowid;
414 return_special_information(p_person_id => l_result,
415 p_structure_name => 'US Fed Perf Appraisal',
416 p_effective_date => l_effective_date,
417 p_special_info => l_special_info);
418 IF l_special_info.segment5 IS NOT NULL
419 THEN
420 l_result := l_special_info.segment5 || ' - ' ||
421 decode_lookup('GHR_US_RATING_LEVEL', l_special_info.segment5);
422 ELSE
423 l_result := '* - No Performance Rating Available';
424 END IF;
425 ELSIF p_clause = 'PAY_PLAN' -- Pay Plan
426 THEN
427 SELECT GDF.segment1
428 INTO l_result
429 FROM per_grades GRD
430 ,per_grade_definitions GDF
431 WHERE GRD.grade_id = p_ASG.grade_id
432 AND GDF.grade_definition_id = GRD.grade_definition_id;
433 ELSIF p_clause = 'FULL_NAME' THEN
434 SELECT full_name
435 INTO l_result
436 FROM per_people_f
437 WHERE rowid = p_PER_rowid;
438 ELSIF p_clause = 'PERSON_ID' THEN
439 SELECT person_id
440 INTO l_result
441 FROM per_people_f
442 WHERE rowid = p_PER_rowid;
443 ELSIF p_clause = 'EMPLOYEE_NUMBER' THEN
444 SELECT employee_number
445 INTO l_result
446 FROM per_people_f
447 WHERE rowid = p_PER_rowid;
448 ELSIF p_clause = 'AGENCY_CODE' THEN
449 l_result := ghr_api.get_position_agency_code_pos(p_ASG.position_id,
450 p_ASG.business_group_id);
451 ELSIF p_clause = 'ORGANIZATION_ID' THEN
452 SELECT POS.organization_id
453 INTO l_result
454 FROM hr_all_positions_f POS
455 WHERE POS.position_id = p_ASG.position_id
456 AND TRUNC(l_effective_date) BETWEEN POS.effective_start_date
457 AND POS.effective_end_date;
458 END IF;
459 RETURN l_result;
460 EXCEPTION
461 WHEN NO_DATA_FOUND THEN
462 l_result := NULL;
463 RETURN l_result;
464 END;
465
466 PROCEDURE Delete_Temp_Data IS
467 BEGIN
468 DELETE FROM GHR_BREAKDOWN_RESULTS WHERE session_id = USERENV('SESSIONID');
469 END;
470
471 PROCEDURE process(p_breakdown_criteria_id IN NUMBER := NULL) IS
472 CURSOR c_emp IS
473 SELECT PER.rowid PER_rowid, ASG.rowid ASG_rowid,
474 PER.person_id, PER.sex
475 FROM per_people_f PER, per_assignments_f ASG
476 WHERE l_effective_date BETWEEN PER.effective_start_date
477 AND PER.effective_end_date
478 AND PER.person_id = ASG.person_id
479 AND l_effective_date BETWEEN ASG.effective_start_date
480 AND ASG.effective_end_date
481 AND get_hierarchy_level(ASG.position_id, l_effective_date) >= l_for_clause
482 AND ghr_api.get_position_agency_code_pos(ASG.position_id, ASG.business_group_id)
483 LIKE l_agcy_and_selm
484 AND ASG.primary_flag = 'Y'
485 AND ASG.assignment_type <> 'B';
486 r_GRP1 per_people_extra_info%rowtype;
487 r_ASG per_all_assignments_f%rowtype;
488 l_result VARCHAR2(80);
489
490 -- Information to be inserted in GHR_BREAKDOWN_RESULTS
491 l_rslt_session_id ghr_breakdown_results.session_id%TYPE := USERENV('SESSIONID');
492 l_rslt_id ghr_breakdown_results.breakdown_result_id%TYPE;
493 l_rslt_for_clause ghr_breakdown_results.for_clause%TYPE;
494 l_rslt_within_clause ghr_breakdown_results.within_clause%TYPE;
495 l_rslt_by_clause ghr_breakdown_results.by_clause%TYPE;
496
497 BEGIN
498 IF p_breakdown_criteria_id IS NOT NULL THEN
499 DELETE FROM GHR_BREAKDOWN_RESULTS
500 WHERE session_id = l_rslt_session_id
501 AND breakdown_criteria_id = p_breakdown_criteria_id;
502 ELSE
503 DELETE FROM GHR_BREAKDOWN_RESULTS
504 WHERE session_id = l_rslt_session_id;
505 END IF;
506 l_rslt_id := 0;
507 FOR r_emp IN c_emp LOOP
508 ghr_history_fetch.fetch_peopleei(r_emp.person_id, 'GHR_US_PER_GROUP1',
509 l_effective_date, r_GRP1);
510 ghr_history_fetch.fetch_assignment(p_rowid => r_emp.ASG_rowid,
511 p_assignment_data => r_ASG,
512 p_result_code => l_result);
513 IF r_GRP1.pei_information5 IS NOT NULL THEN
514 l_rslt_id := l_rslt_id + 1;
515 IF l_for_clause > 0 THEN
516 l_rslt_for_clause := get_hierarchy_codes(r_emp.ASG_rowid, l_effective_date, 'PARENTS');
517 END IF;
518 l_rslt_within_clause := Any_Clause(l_within_clause, r_emp.PER_rowid, r_GRP1, r_ASG);
519 l_rslt_by_clause := Any_Clause(l_by_clause, r_emp.PER_rowid, r_GRP1, r_ASG);
520 INSERT INTO ghr_breakdown_results
521 (session_id, breakdown_result_id, breakdown_criteria_id, for_clause, within_clause,
522 by_clause, sex, ethnic_origin)
523 VALUES
524 (l_rslt_session_id, l_rslt_id, p_breakdown_criteria_id, l_rslt_for_clause,
525 l_rslt_within_clause, l_rslt_by_clause, r_emp.sex, r_GRP1.pei_information5);
526 l_rslt_for_clause := NULL;
527 l_rslt_within_clause := NULL;
528 l_rslt_by_clause := NULL;
529 END IF;
530 END LOOP;
531 END;
532
533 -- Given and org structure version iud return the org structure name (ie hierarchy)
534 FUNCTION get_org_struct_name(
535 p_org_structure_version_id per_org_structure_versions.org_structure_version_id%TYPE)
536 RETURN VARCHAR2 IS
537
538 CURSOR cur_hier IS
539 SELECT s.name
540 FROM per_organization_structures s
541 ,per_org_structure_versions v
542 WHERE v.org_structure_version_id = p_org_structure_version_id
543 AND v.organization_structure_id = s.organization_structure_id;
544
545 BEGIN
546 FOR cur_hier_rec IN cur_hier LOOP
547 RETURN(cur_hier_rec.name);
548 END LOOP;
549
550 RETURN(null);
551
552 END get_org_struct_name;
553
554 END ghr_breakdown_rpt;