1 PACKAGE BODY ghr_per_sum AS
2 /* $Header: ghpersum.pkb 120.1.12010000.2 2008/08/05 15:09:51 ubhat ship $ */
3 --
4 PROCEDURE fetch_peopleei(
5 p_person_id IN NUMBER
6 ,p_information_type IN VARCHAR2
7 ,p_date_effective IN DATE
8 ,p_information1 IN OUT NOCOPY VARCHAR2
9 ,p_information2 IN OUT NOCOPY VARCHAR2
10 ,p_information3 IN OUT NOCOPY VARCHAR2
11 ,p_information4 IN OUT NOCOPY VARCHAR2
12 ,p_information5 IN OUT NOCOPY VARCHAR2
13 ,p_information6 IN OUT NOCOPY VARCHAR2
14 ,p_information7 IN OUT NOCOPY VARCHAR2
15 ,p_information8 IN OUT NOCOPY VARCHAR2
16 ,p_information9 IN OUT NOCOPY VARCHAR2
17 ,p_information10 IN OUT NOCOPY VARCHAR2
18 ,p_information11 IN OUT NOCOPY VARCHAR2
19 ,p_information12 IN OUT NOCOPY VARCHAR2
20 ,p_information13 IN OUT NOCOPY VARCHAR2
21 ,p_information14 IN OUT NOCOPY VARCHAR2
22 ,p_information15 IN OUT NOCOPY VARCHAR2
23 ,p_information16 IN OUT NOCOPY VARCHAR2
24 ,p_information17 IN OUT NOCOPY VARCHAR2
25 ,p_information18 IN OUT NOCOPY VARCHAR2
26 ,p_information19 IN OUT NOCOPY VARCHAR2
27 ,p_information20 IN OUT NOCOPY VARCHAR2
28 ,p_information21 IN OUT NOCOPY VARCHAR2
29 ,p_information22 IN OUT NOCOPY VARCHAR2
30 ,p_information23 IN OUT NOCOPY VARCHAR2
31 ,p_information24 IN OUT NOCOPY VARCHAR2
32 ,p_information25 IN OUT NOCOPY VARCHAR2
33 ,p_information26 IN OUT NOCOPY VARCHAR2
34 ,p_information27 IN OUT NOCOPY VARCHAR2
35 ,p_information28 IN OUT NOCOPY VARCHAR2
36 ,p_information29 IN OUT NOCOPY VARCHAR2
37 ,p_information30 IN OUT NOCOPY VARCHAR2) IS
38
39 l_per_ei_data per_people_extra_info%rowtype;
40 BEGIN
41 ghr_history_fetch.fetch_peopleei(
42 p_person_id => p_person_id
43 ,p_information_type => p_information_type
44 ,p_date_effective => p_date_effective
45 ,p_per_ei_data => l_per_ei_data);
46
47 p_information1 := l_per_ei_data.pei_information1;
48 p_information2 := l_per_ei_data.pei_information2;
49 p_information3 := l_per_ei_data.pei_information3;
50 p_information4 := l_per_ei_data.pei_information4;
51 p_information5 := l_per_ei_data.pei_information5;
52 p_information6 := l_per_ei_data.pei_information6;
53 p_information7 := l_per_ei_data.pei_information7;
54 p_information8 := l_per_ei_data.pei_information8;
55 p_information9 := l_per_ei_data.pei_information9;
56 p_information10 := l_per_ei_data.pei_information10;
57 p_information11 := l_per_ei_data.pei_information11;
58 p_information12 := l_per_ei_data.pei_information12;
59 p_information13 := l_per_ei_data.pei_information13;
60 p_information14 := l_per_ei_data.pei_information14;
61 p_information15 := l_per_ei_data.pei_information15;
62 p_information16 := l_per_ei_data.pei_information16;
63 p_information17 := l_per_ei_data.pei_information17;
64 p_information18 := l_per_ei_data.pei_information18;
65 p_information19 := l_per_ei_data.pei_information19;
66 p_information20 := l_per_ei_data.pei_information20;
67 p_information21 := l_per_ei_data.pei_information21;
68 p_information22 := l_per_ei_data.pei_information22;
69 p_information23 := l_per_ei_data.pei_information23;
70 p_information24 := l_per_ei_data.pei_information24;
71 p_information25 := l_per_ei_data.pei_information25;
72 p_information26 := l_per_ei_data.pei_information26;
73 p_information27 := l_per_ei_data.pei_information27;
74 p_information28 := l_per_ei_data.pei_information28;
75 p_information29 := l_per_ei_data.pei_information29;
76 p_information30 := l_per_ei_data.pei_information30;
77
78 END fetch_peopleei;
79
80 PROCEDURE fetch_asgei(
81 p_assignment_id IN NUMBER
82 ,p_information_type IN VARCHAR2
83 ,p_date_effective IN DATE
84 ,p_information1 IN OUT NOCOPY VARCHAR2
85 ,p_information2 IN OUT NOCOPY VARCHAR2
86 ,p_information3 IN OUT NOCOPY VARCHAR2
87 ,p_information4 IN OUT NOCOPY VARCHAR2
88 ,p_information5 IN OUT NOCOPY VARCHAR2
89 ,p_information6 IN OUT NOCOPY VARCHAR2
90 ,p_information7 IN OUT NOCOPY VARCHAR2
91 ,p_information8 IN OUT NOCOPY VARCHAR2
92 ,p_information9 IN OUT NOCOPY VARCHAR2
93 ,p_information10 IN OUT NOCOPY VARCHAR2
94 ,p_information11 IN OUT NOCOPY VARCHAR2
95 ,p_information12 IN OUT NOCOPY VARCHAR2
96 ,p_information13 IN OUT NOCOPY VARCHAR2
97 ,p_information14 IN OUT NOCOPY VARCHAR2
98 ,p_information15 IN OUT NOCOPY VARCHAR2
99 ,p_information16 IN OUT NOCOPY VARCHAR2
100 ,p_information17 IN OUT NOCOPY VARCHAR2
101 ,p_information18 IN OUT NOCOPY VARCHAR2
102 ,p_information19 IN OUT NOCOPY VARCHAR2
103 ,p_information20 IN OUT NOCOPY VARCHAR2
104 ,p_information21 IN OUT NOCOPY VARCHAR2
105 ,p_information22 IN OUT NOCOPY VARCHAR2
106 ,p_information23 IN OUT NOCOPY VARCHAR2
107 ,p_information24 IN OUT NOCOPY VARCHAR2
108 ,p_information25 IN OUT NOCOPY VARCHAR2
109 ,p_information26 IN OUT NOCOPY VARCHAR2
110 ,p_information27 IN OUT NOCOPY VARCHAR2
111 ,p_information28 IN OUT NOCOPY VARCHAR2
112 ,p_information29 IN OUT NOCOPY VARCHAR2
113 ,p_information30 IN OUT NOCOPY VARCHAR2) IS
114
115 l_asg_ei_data per_assignment_extra_info%rowtype;
116 BEGIN
117 ghr_history_fetch.fetch_asgei(
118 p_assignment_id => p_assignment_id
119 ,p_information_type => p_information_type
120 ,p_date_effective => p_date_effective
121 ,p_asg_ei_data => l_asg_ei_data);
122
123 p_information1 := l_asg_ei_data.aei_information1;
124 p_information2 := l_asg_ei_data.aei_information2;
125 p_information3 := l_asg_ei_data.aei_information3;
126 p_information4 := l_asg_ei_data.aei_information4;
127 p_information5 := l_asg_ei_data.aei_information5;
128 p_information6 := l_asg_ei_data.aei_information6;
129 p_information7 := l_asg_ei_data.aei_information7;
130 p_information8 := l_asg_ei_data.aei_information8;
131 p_information9 := l_asg_ei_data.aei_information9;
132 p_information10 := l_asg_ei_data.aei_information10;
133 p_information11 := l_asg_ei_data.aei_information11;
134 p_information12 := l_asg_ei_data.aei_information12;
135 p_information13 := l_asg_ei_data.aei_information13;
136 p_information14 := l_asg_ei_data.aei_information14;
137 p_information15 := l_asg_ei_data.aei_information15;
138 p_information16 := l_asg_ei_data.aei_information16;
139 p_information17 := l_asg_ei_data.aei_information17;
140 p_information18 := l_asg_ei_data.aei_information18;
141 p_information19 := l_asg_ei_data.aei_information19;
142 p_information20 := l_asg_ei_data.aei_information20;
143 p_information21 := l_asg_ei_data.aei_information21;
144 p_information22 := l_asg_ei_data.aei_information22;
145 p_information23 := l_asg_ei_data.aei_information23;
146 p_information24 := l_asg_ei_data.aei_information24;
147 p_information25 := l_asg_ei_data.aei_information25;
148 p_information26 := l_asg_ei_data.aei_information26;
149 p_information27 := l_asg_ei_data.aei_information27;
150 p_information28 := l_asg_ei_data.aei_information28;
151 p_information29 := l_asg_ei_data.aei_information29;
152 p_information30 := l_asg_ei_data.aei_information30;
153
154 END fetch_asgei;
155
156 PROCEDURE fetch_positionei(
157 p_position_id IN NUMBER
158 ,p_information_type IN VARCHAR2
159 ,p_date_effective IN DATE
160 ,p_information1 IN OUT NOCOPY VARCHAR2
161 ,p_information2 IN OUT NOCOPY VARCHAR2
162 ,p_information3 IN OUT NOCOPY VARCHAR2
163 ,p_information4 IN OUT NOCOPY VARCHAR2
164 ,p_information5 IN OUT NOCOPY VARCHAR2
165 ,p_information6 IN OUT NOCOPY VARCHAR2
166 ,p_information7 IN OUT NOCOPY VARCHAR2
167 ,p_information8 IN OUT NOCOPY VARCHAR2
168 ,p_information9 IN OUT NOCOPY VARCHAR2
169 ,p_information10 IN OUT NOCOPY VARCHAR2
170 ,p_information11 IN OUT NOCOPY VARCHAR2
171 ,p_information12 IN OUT NOCOPY VARCHAR2
172 ,p_information13 IN OUT NOCOPY VARCHAR2
173 ,p_information14 IN OUT NOCOPY VARCHAR2
174 ,p_information15 IN OUT NOCOPY VARCHAR2
175 ,p_information16 IN OUT NOCOPY VARCHAR2
176 ,p_information17 IN OUT NOCOPY VARCHAR2
177 ,p_information18 IN OUT NOCOPY VARCHAR2
178 ,p_information19 IN OUT NOCOPY VARCHAR2
179 ,p_information20 IN OUT NOCOPY VARCHAR2
180 ,p_information21 IN OUT NOCOPY VARCHAR2
181 ,p_information22 IN OUT NOCOPY VARCHAR2
182 ,p_information23 IN OUT NOCOPY VARCHAR2
183 ,p_information24 IN OUT NOCOPY VARCHAR2
184 ,p_information25 IN OUT NOCOPY VARCHAR2
185 ,p_information26 IN OUT NOCOPY VARCHAR2
186 ,p_information27 IN OUT NOCOPY VARCHAR2
187 ,p_information28 IN OUT NOCOPY VARCHAR2
188 ,p_information29 IN OUT NOCOPY VARCHAR2
189 ,p_information30 IN OUT NOCOPY VARCHAR2) IS
190
191 l_pos_ei_data per_position_extra_info%rowtype;
192 BEGIN
193 ghr_history_fetch.fetch_positionei(
194 p_position_id => p_position_id
195 ,p_information_type => p_information_type
196 ,p_date_effective => p_date_effective
197 ,p_pos_ei_data => l_pos_ei_data);
198
199 p_information1 := l_pos_ei_data.poei_information1;
200 p_information2 := l_pos_ei_data.poei_information2;
201 p_information3 := l_pos_ei_data.poei_information3;
202 p_information4 := l_pos_ei_data.poei_information4;
203 p_information5 := l_pos_ei_data.poei_information5;
204 p_information6 := l_pos_ei_data.poei_information6;
205 p_information7 := l_pos_ei_data.poei_information7;
206 p_information8 := l_pos_ei_data.poei_information8;
207 p_information9 := l_pos_ei_data.poei_information9;
208 p_information10 := l_pos_ei_data.poei_information10;
209 p_information11 := l_pos_ei_data.poei_information11;
210 p_information12 := l_pos_ei_data.poei_information12;
211 p_information13 := l_pos_ei_data.poei_information13;
212 p_information14 := l_pos_ei_data.poei_information14;
213 p_information15 := l_pos_ei_data.poei_information15;
214 p_information16 := l_pos_ei_data.poei_information16;
215 p_information17 := l_pos_ei_data.poei_information17;
216 p_information18 := l_pos_ei_data.poei_information18;
217 p_information19 := l_pos_ei_data.poei_information19;
218 p_information20 := l_pos_ei_data.poei_information20;
219 p_information21 := l_pos_ei_data.poei_information21;
220 p_information22 := l_pos_ei_data.poei_information22;
221 p_information23 := l_pos_ei_data.poei_information23;
222 p_information24 := l_pos_ei_data.poei_information24;
223 p_information25 := l_pos_ei_data.poei_information25;
224 p_information26 := l_pos_ei_data.poei_information26;
225 p_information27 := l_pos_ei_data.poei_information27;
226 p_information28 := l_pos_ei_data.poei_information28;
227 p_information29 := l_pos_ei_data.poei_information29;
228 p_information30 := l_pos_ei_data.poei_information30;
229
230 END fetch_positionei;
231 --
232 PROCEDURE return_special_information(
233 p_person_id IN NUMBER
234 ,p_structure_name IN VARCHAR2
235 ,p_effective_date IN DATE
236 ,p_segment1 IN OUT NOCOPY VARCHAR2
237 ,p_segment2 IN OUT NOCOPY VARCHAR2
238 ,p_segment3 IN OUT NOCOPY VARCHAR2
239 ,p_segment4 IN OUT NOCOPY VARCHAR2
240 ,p_segment5 IN OUT NOCOPY VARCHAR2
241 ,p_segment6 IN OUT NOCOPY VARCHAR2
242 ,p_segment7 IN OUT NOCOPY VARCHAR2
243 ,p_segment8 IN OUT NOCOPY VARCHAR2
244 ,p_segment9 IN OUT NOCOPY VARCHAR2
245 ,p_segment10 IN OUT NOCOPY VARCHAR2
246 ,p_segment11 IN OUT NOCOPY VARCHAR2
247 ,p_segment12 IN OUT NOCOPY VARCHAR2
248 ,p_segment13 IN OUT NOCOPY VARCHAR2
249 ,p_segment14 IN OUT NOCOPY VARCHAR2
250 ,p_segment15 IN OUT NOCOPY VARCHAR2
251 ,p_segment16 IN OUT NOCOPY VARCHAR2
252 ,p_segment17 IN OUT NOCOPY VARCHAR2
253 ,p_segment18 IN OUT NOCOPY VARCHAR2
254 ,p_segment19 IN OUT NOCOPY VARCHAR2
255 ,p_segment20 IN OUT NOCOPY VARCHAR2
256 ,p_segment21 IN OUT NOCOPY VARCHAR2
257 ,p_segment22 IN OUT NOCOPY VARCHAR2
258 ,p_segment23 IN OUT NOCOPY VARCHAR2
259 ,p_segment24 IN OUT NOCOPY VARCHAR2
260 ,p_segment25 IN OUT NOCOPY VARCHAR2
261 ,p_segment26 IN OUT NOCOPY VARCHAR2
262 ,p_segment27 IN OUT NOCOPY VARCHAR2
263 ,p_segment28 IN OUT NOCOPY VARCHAR2
264 ,p_segment29 IN OUT NOCOPY VARCHAR2
265 ,p_segment30 IN OUT NOCOPY VARCHAR2
266 ,p_person_analysis_id IN OUT NOCOPY NUMBER
267 ,p_object_version_number IN OUT NOCOPY NUMBER) IS
268
269 l_special_information_type ghr_api.special_information_type;
270 BEGIN
271 ghr_api.return_special_information(p_person_id
272 ,p_structure_name
273 ,p_effective_date
274 ,l_special_information_type);
275 --
276 p_segment1 := l_special_information_type.segment1;
277 p_segment2 := l_special_information_type.segment2;
278 p_segment3 := l_special_information_type.segment3;
279 p_segment4 := l_special_information_type.segment4;
280 p_segment5 := l_special_information_type.segment5;
281 p_segment6 := l_special_information_type.segment6;
282 p_segment7 := l_special_information_type.segment7;
283 p_segment8 := l_special_information_type.segment8;
284 p_segment9 := l_special_information_type.segment9;
285 p_segment10 := l_special_information_type.segment10;
286 p_segment11 := l_special_information_type.segment11;
287 p_segment12 := l_special_information_type.segment12;
288 p_segment13 := l_special_information_type.segment13;
289 p_segment14 := l_special_information_type.segment14;
290 p_segment15 := l_special_information_type.segment15;
291 p_segment16 := l_special_information_type.segment16;
292 p_segment17 := l_special_information_type.segment17;
293 p_segment18 := l_special_information_type.segment18;
294 p_segment19 := l_special_information_type.segment19;
295 p_segment20 := l_special_information_type.segment20;
296 p_segment21 := l_special_information_type.segment21;
297 p_segment22 := l_special_information_type.segment22;
298 p_segment23 := l_special_information_type.segment23;
299 p_segment24 := l_special_information_type.segment24;
300 p_segment25 := l_special_information_type.segment25;
301 p_segment26 := l_special_information_type.segment26;
302 p_segment27 := l_special_information_type.segment27;
303 p_segment28 := l_special_information_type.segment28;
304 p_segment29 := l_special_information_type.segment29;
305 p_segment30 := l_special_information_type.segment30;
306 p_person_analysis_id := l_special_information_type.person_analysis_id;
307 p_object_version_number := l_special_information_type.object_version_number;
308
309 END return_special_information;
310 --
311 PROCEDURE get_grade_details (p_grade_id IN NUMBER
312 ,p_grade_name IN OUT NOCOPY VARCHAR2
313 ,p_pay_plan IN OUT NOCOPY VARCHAR2
314 ,p_grade_or_level IN OUT NOCOPY VARCHAR2) IS
315 --
316 CURSOR cur_grd IS
317 SELECT gdf.segment1 pay_plan
318 ,gdf.segment2 grade_or_level
319 ,grd.name grade_name
320 FROM per_grade_definitions gdf
321 ,per_grades grd
322 WHERE grd.grade_id = p_grade_id
323 AND grd.grade_definition_id = gdf.grade_definition_id;
324 --
325 BEGIN
326
327 FOR cur_grd_rec IN cur_grd LOOP
328 p_grade_name := cur_grd_rec.grade_name;
329 p_pay_plan := cur_grd_rec.pay_plan;
330 p_grade_or_level := cur_grd_rec.grade_or_level;
331 END LOOP;
332
333 END get_grade_details;
334 --
335 PROCEDURE get_retained_grade_details (p_person_id IN NUMBER
336 ,p_effective_date IN DATE
337 ,p_person_extra_info_id IN OUT NOCOPY NUMBER
338 ,p_date_from IN OUT NOCOPY DATE
339 ,p_date_to IN OUT NOCOPY DATE
340 ,p_grade_or_level IN OUT NOCOPY VARCHAR2
341 ,p_step_or_rate IN OUT NOCOPY VARCHAR2
342 ,p_pay_plan IN OUT NOCOPY VARCHAR2
343 ,p_pay_table_id IN OUT NOCOPY VARCHAR2
344 ,p_pay_basis IN OUT NOCOPY VARCHAR2
345 ,p_temp_step IN OUT NOCOPY VARCHAR2
346 ) IS
347 CURSOR cur_pei IS
348 SELECT pei.pei_information1 date_from
349 ,pei.pei_information2 date_to
350 FROM per_people_extra_info pei
351 WHERE pei.person_extra_info_id = p_person_extra_info_id;
352
353 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
354
355 BEGIN
356 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
357 (p_person_id => p_person_id
358 ,p_effective_date => p_effective_date);
359
360 IF l_retained_grade_rec.person_extra_info_id IS NOT NULL THEN
361 p_person_extra_info_id := l_retained_grade_rec.person_extra_info_id;
362 FOR cur_pei_rec IN cur_pei LOOP
363 p_date_from := fnd_date.canonical_to_date(cur_pei_rec.date_from);
364 p_date_to := fnd_date.canonical_to_date(cur_pei_rec.date_to);
365 END LOOP;
366 p_grade_or_level := l_retained_grade_rec.grade_or_level;
367 p_step_or_rate := l_retained_grade_rec.step_or_rate;
368 p_pay_plan := l_retained_grade_rec.pay_plan;
369 p_pay_table_id := SUBSTR(ghr_pay_calc.get_user_table_name(l_retained_grade_rec.user_table_id),1,4);
370 p_pay_basis := l_retained_grade_rec.pay_basis;
371 p_temp_step := l_retained_grade_rec.temp_step;
372 END IF;
373 EXCEPTION
374 WHEN ghr_pay_calc.pay_calc_message THEN
375 -- This just means nothing was returned, no need to worry about this!
376 NULL;
377 END ;
378 --
379 FUNCTION further_retained_details_exist(p_person_id IN NUMBER
380 ,p_person_extra_info_id IN NUMBER)
381 RETURN BOOLEAN IS
382 --
383 CURSOR cur_pei IS
384 SELECT 1
385 FROM per_people_extra_info pei
386 WHERE pei.person_id = p_person_id
387 AND pei.information_type = 'GHR_US_RETAINED_GRADE'
388 AND pei.person_extra_info_id <> NVL(p_person_extra_info_id,-999);
389 --
390 BEGIN
391 FOR cur_pei_rec IN cur_pei LOOP
392 RETURN(TRUE);
393 END LOOP;
394 --
395 RETURN(FALSE);
396 --
397 END further_retained_details_exist;
398 --
399 FUNCTION get_poi_desc (p_personnel_office_id IN NUMBER)
400 RETURN VARCHAR2 IS
401 --
402 CURSOR cur_poi IS
403 SELECT poi.description
404 FROM ghr_pois poi
405 WHERE poi.personnel_office_id = p_personnel_office_id;
406
407 BEGIN
408 FOR cur_poi_rec IN cur_poi LOOP
409 RETURN(cur_poi_rec.description);
410 END LOOP;
411 --
412 RETURN(NULL);
413 --
414 END get_poi_desc;
415 --
416 PROCEDURE get_duty_station_details (p_location_id IN NUMBER
417 ,p_effective_date IN DATE
418 ,p_duty_sation_code IN OUT NOCOPY VARCHAR2
419 ,p_duty_station_desc IN OUT NOCOPY VARCHAR2
420 ,p_locality_pay_area IN OUT NOCOPY VARCHAR2
421 ,p_locality_pay_area_percentage IN OUT NOCOPY NUMBER
422 ) IS
423 CURSOR cur_ds IS
424 SELECT dst.duty_station_code
425 ,dstv.duty_station_desc
426 ,lpa.short_name
427 ,lpa.adjustment_percentage
428 FROM ghr_locality_pay_areas_f lpa
429 ,ghr_duty_stations_v dstv
430 ,ghr_duty_stations_f dst
431 ,hr_location_extra_info lei
432 WHERE lei.location_id = p_location_id
433 AND lei.information_type = 'GHR_US_LOC_INFORMATION'
434 AND dst.duty_station_id = lei.lei_information3
435 AND NVL(p_effective_date,TRUNC(sysdate)) BETWEEN dst.effective_start_date AND dst.effective_end_date
436 AND dstv.duty_station_id = dst.duty_station_id
437 AND NVL(p_effective_date,TRUNC(sysdate)) BETWEEN dstv.effective_start_date AND dstv.effective_end_date
438 AND dst.locality_pay_area_id = lpa.locality_pay_area_id
439 AND NVL(p_effective_date,TRUNC(sysdate)) BETWEEN lpa.effective_start_date AND lpa.effective_end_date;
440
441 BEGIN
442 FOR cur_ds_rec IN cur_ds LOOP
443 p_duty_sation_code := cur_ds_rec.duty_station_code;
444 p_duty_station_desc := cur_ds_rec.duty_station_desc;
445 p_locality_pay_area := cur_ds_rec.short_name;
446 p_locality_pay_area_percentage := cur_ds_rec.adjustment_percentage;
447 END LOOP;
448 --
449 END get_duty_station_details;
450 --
451 PROCEDURE get_org_details (p_org_id IN NUMBER
452 ,p_org_name IN OUT NOCOPY VARCHAR2
453 ,p_org_line1 IN OUT NOCOPY VARCHAR2
454 ,p_org_line2 IN OUT NOCOPY VARCHAR2
455 ,p_org_line3 IN OUT NOCOPY VARCHAR2
456 ,p_org_line4 IN OUT NOCOPY VARCHAR2
457 ,p_org_line5 IN OUT NOCOPY VARCHAR2
458 ,p_org_line6 IN OUT NOCOPY VARCHAR2) IS
459 --
460 CURSOR cur_org IS
461 SELECT org.name
462 FROM hr_organization_units org
463 WHERE org.organization_id = p_org_id;
464
465 CURSOR cur_oi IS
466 SELECT oi.org_information5 org_line1
467 ,oi.org_information6 org_line2
468 ,oi.org_information7 org_line3
469 ,oi.org_information8 org_line4
470 ,oi.org_information9 org_line5
471 ,oi.org_information10 org_line6
472 FROM hr_organization_information oi
473 WHERE oi.organization_id = p_org_id
474 AND oi.org_information_context = 'GHR_US_ORG_REPORTING_INFO';
475 --
476 BEGIN
477 FOR cur_org_rec IN cur_org LOOP
478 p_org_name := cur_org_rec.name;
479 END LOOP;
480 --
481 FOR cur_oi_rec IN cur_oi LOOP
482 p_org_line1 := cur_oi_rec.org_line1;
483 p_org_line2 := cur_oi_rec.org_line2;
484 p_org_line3 := cur_oi_rec.org_line3;
485 p_org_line4 := cur_oi_rec.org_line4;
486 p_org_line5 := cur_oi_rec.org_line5;
487 p_org_line6 := cur_oi_rec.org_line6;
488 END LOOP;
489 --
490 END get_org_details;
491 --
492 PROCEDURE get_element_details (p_element_name IN VARCHAR2
493 ,p_input_value_name IN VARCHAR2
494 ,p_assignment_id IN NUMBER
495 ,p_effective_date IN DATE
496 ,p_value IN OUT NOCOPY VARCHAR2
497 ,p_effective_start_date IN OUT NOCOPY DATE
498 ,p_business_group_id IN NUMBER) IS
499 --
500 -- NOTE: The effective date we get is that of the individual input value not the effective
501 -- date of the whole element as seen in the element screen.
502 --
503 CURSOR cur_ele(p_element_name IN VARCHAR2,
504 p_bg_id IN NUMBER)
505 IS
506 SELECT eev.screen_entry_value
507 ,eev.effective_start_date
508 FROM pay_element_types_f elt
509 ,pay_input_values_f ipv
510 ,pay_element_entries_f ele
511 ,pay_element_entry_values_f eev
512 WHERE p_effective_date BETWEEN elt.effective_start_date AND elt.effective_end_date
513 AND p_effective_date BETWEEN ipv.effective_start_date AND ipv.effective_end_date
514 AND p_effective_date BETWEEN ele.effective_start_date AND ele.effective_end_date
515 AND p_effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
516 AND elt.element_type_id = ipv.element_type_id
517 AND upper(elt.element_name)= upper(p_element_name)
518 AND ipv.input_value_id = eev.input_value_id
519 AND ele.assignment_id = p_assignment_id
520 AND ele.element_entry_id+0 = eev.element_entry_id
521 AND upper(ipv.name ) = upper(p_input_value_name)
522 -- AND NVL(elt.business_group_id,0) = NVL(ipv.business_group_id,0)
523 AND (elt.business_group_id is NULL or elt.business_group_id = p_bg_id);
524 --
525
526 -- Commented the below cursor as a part of bug 4016362
527 /*Cursor Cur_bg(p_assignment_id NUMBER,p_eff_date DATE) is
528 Select distinct business_group_id bg
529 from per_assignments_f
530 where assignment_id = p_assignment_id
531 and p_eff_date between effective_start_date
532 and effective_end_date;
533 --
534 ll_bg_id NUMBER; */
535
536 ll_pay_basis VARCHAR2(80);
537 ll_effective_date DATE;
538 l_new_element_name VARCHAR2(80);
539 l_session ghr_history_api.g_session_var_type;
540 --
541 BEGIN
542 --
543 --
544 -- Initialization
545 -- Pick the business group id and also pay basis for later use
546 ll_effective_date := p_effective_Date;
547
548 -- Commented this code as a part of Bug 4016362
549 /* For BG_rec in Cur_BG(p_assignment_id,ll_effective_date)
550 Loop
551 ll_bg_id:=BG_rec.bg;
552 End Loop;*/
553
554 ----
555 ---- The New Changes after 08/22 patch
556 ---- For all elements in HR User old function will fetch the same name.
557 ---- because of is_script will be FALSE
558 ----
559 ---- For all elements (except BSR) in Payroll user old function.
560 ---- for BSR a new function which will fetch from assignmnet id.
561 ----
562
563 IF (p_element_name = 'Basic Salary Rate'
564 and (fnd_profile.value('HR_USER_TYPE') = 'INT')) THEN
565 hr_utility.set_location('PAYROLL User -- BSR -- from asgid-- ', 1);
566 l_new_element_name :=
567 pqp_fedhr_uspay_int_utils.return_new_element_name(
568 p_assignment_id => p_assignment_id,
569 p_business_group_id => p_business_group_id, -- Bug 4016362
570 p_effective_date => ll_effective_date);
571
572 -- Bug 4016362 : Commented this ELSEIF condition as the condition in IF and ELSIF clauses are mutually exclusive.
573
574 /* ELSIF (fnd_profile.value('HR_USER_TYPE') <> 'INT'
575 or (p_element_name <> 'Basic Salary Rate' and (fnd_profile.value('HR_USER_TYPE') = 'INT'))) THEN*/
576 ELSE -- Bug 4016362
577 hr_utility.set_location('HR USER or PAYROLL User without BSR element -- from elt name -- ', 1);
578 l_new_element_name :=
579 pqp_fedhr_uspay_int_utils.return_new_element_name(
580 p_fedhr_element_name => p_element_name,
581 p_business_group_id => p_business_group_id, -- Bug 4016362
582 p_effective_date => ll_effective_date,
583 p_pay_basis => NULL);
584
585 END IF;
586
587 --
588 --
589 FOR cur_ele_rec IN cur_ele(l_new_element_name,p_business_group_id) LOOP -- Bug 4016362
590 p_value := cur_ele_rec.screen_entry_value;
591 p_effective_start_date := cur_ele_rec.effective_start_date;
592 Exit; -- Bug 4016362
593 END LOOP;
594 --
595 END get_element_details;
596 --
597 PROCEDURE get_status_code (p_status IN VARCHAR2
598 ,p_status_code OUT NOCOPY VARCHAR2) IS
599
600 CURSOR cur_status IS
601 SELECT fcl.lookup_code
602 FROM fnd_common_lookups fcl
603 WHERE fcl.APPLICATION_ID = 800
604 AND fcl.LOOKUP_TYPE = 'GHR_US_TSP_STATUS'
605 AND fcl.meaning = p_status;
606
607 BEGIN
608 --
609
610 FOR cur_status_rec IN cur_status LOOP
611 p_status_code := cur_status_rec.lookup_code;
612 END LOOP;
613
614 END get_status_code;
615 --
616 PROCEDURE get_element_entry_values (p_element_entry_id IN NUMBER
617 ,p_input_value_name IN VARCHAR2
618 ,p_effective_date IN DATE
619 ,p_value IN OUT NOCOPY VARCHAR2
620 ,p_effective_start_date IN OUT NOCOPY DATE) IS
621 --
622 -- NOTE: The effective date we get is that of the individual input value not the effective
623 -- date of the whole element as seen in the element screen.
624 --
625 CURSOR cur_ele_values IS
626 SELECT eev.screen_entry_value
627 ,eev.effective_start_date
628 FROM pay_element_entry_values_f eev
629 ,pay_input_values_f ipv
630 WHERE p_input_value_name = ipv.name
631 AND p_effective_date BETWEEN ipv.effective_start_date AND ipv.effective_end_date
632 AND ipv.input_value_id = eev.input_value_id
633 AND p_element_entry_id = eev.element_entry_id
634 AND p_effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date;
635
636 BEGIN
637 --
638 FOR cur_ele_values_rec IN cur_ele_values LOOP
639 p_value := cur_ele_values_rec.screen_entry_value;
640 p_effective_start_date := cur_ele_values_rec.effective_start_date;
641 END LOOP;
642 --
643 END get_element_entry_values;
644 --
645 --
646 FUNCTION info_type_is_valid (p_application_id IN NUMBER
647 ,p_responsibility_id IN NUMBER
648 ,p_info_type_table_name IN VARCHAR2
649 ,p_information_type IN VARCHAR2)
650 RETURN BOOLEAN IS
651 --
652 CURSOR cur_its IS
653 SELECT 1
654 FROM per_info_type_security its
655 WHERE its.application_id = p_application_id
656 AND its.responsibility_id = p_responsibility_id
657 AND its.info_type_table_name = p_info_type_table_name
658 AND its.information_type = p_information_type;
659 --
660 BEGIN
661 --
662 FOR cur_its_rec IN cur_its LOOP
663 RETURN(TRUE);
664 END LOOP;
665 --
666 RETURN(FALSE);
667 END info_type_is_valid;
668 --
669 FUNCTION get_workflow_id(p_workflow_name IN VARCHAR2)
670 RETURN NUMBER IS
671
672 CURSOR cur_workflow_id IS
673 SELECT hrw.workflow_id
674 FROM hr_workflows hrw
675 WHERE hrw.workflow_name = p_workflow_name;
676
677 BEGIN
678 --
679
680 FOR cur_workflow_id_rec IN cur_workflow_id LOOP
681 RETURN(cur_workflow_id_rec.workflow_id);
682 END LOOP;
683
684 END get_workflow_id;
685 --
686 PROCEDURE get_noa_code (p_pa_request_id IN NUMBER
687 ,p_noa_code OUT NOCOPY VARCHAR2) IS
688
689 CURSOR cur_noa_code IS
690 SELECT rpa.first_noa_code
691 FROM ghr_pa_requests rpa
692 WHERE rpa.pa_request_id = p_pa_request_id;
693
694 BEGIN
695 --
696
697 FOR cur_noa_code_rec IN cur_noa_code LOOP
698 p_noa_code := cur_noa_code_rec.first_noa_code;
699 END LOOP;
700
701 END get_noa_code;
702
703 --Begin Bug# 6850492
704 PROCEDURE get_second_noa_code (p_pa_request_id IN NUMBER
705 ,p_second_noa_code OUT NOCOPY VARCHAR2) IS
706
707 CURSOR cur_second_noa_code IS
708 SELECT rpa.second_noa_code
709 FROM ghr_pa_requests rpa
710 WHERE rpa.pa_request_id = p_pa_request_id;
711
712 BEGIN
713 --
714
715 FOR cur_second_noa_code_rec IN cur_second_noa_code LOOP
716 p_second_noa_code := cur_second_noa_code_rec.second_noa_code;
717 END LOOP;
718
719 END get_second_noa_code;
720 --End Bug# 6850492
721 --
722 PROCEDURE get_dob_asgstat(p_assignment_id IN NUMBER
723 ,p_effective_date IN DATE
724 ,p_dob OUT NOCOPY DATE
725 ,p_system_status OUT NOCOPY VARCHAR2) IS
726
727 CURSOR cur_dob_asgstat IS
728 SELECT per.date_of_birth
729 ,ast.per_system_status
730 FROM per_assignment_status_types ast
731 ,per_assignments_f asg
732 ,per_people_f per
733 WHERE asg.assignment_id = p_assignment_id
734 AND asg.person_id = per.person_id
735 AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
736 AND ast.assignment_status_type_id = asg.assignment_status_type_id
737 AND asg.primary_flag = 'Y'
738 AND asg.assignment_type = 'E';
739
740 BEGIN
741 --
742 FOR cur_dob_asgstat_rec IN cur_dob_asgstat LOOP
743 p_dob := cur_dob_asgstat_rec.date_of_birth;
744 p_system_status := cur_dob_asgstat_rec.per_system_status;
745 END LOOP;
746
747 null;
748 END get_dob_asgstat;
749 --
750 PROCEDURE get_current_emp_flag (p_effective_date IN DATE
751 ,p_person_id IN NUMBER
752 ,p_current_emp_flag OUT NOCOPY VARCHAR2) IS
753
754 CURSOR cur_emp_flag IS
755 select current_employee_flag from per_all_people_f
756 where person_id = p_person_id
757 and p_effective_date
758 between effective_start_date and effective_end_date;
759
760 BEGIN
761 --
762 FOR cur_emp_flag_rec IN cur_emp_flag LOOP
763 p_current_emp_flag := cur_emp_flag_rec.current_employee_flag;
764 END LOOP;
765
766 null;
767 END get_current_emp_flag;
768 --
769
770 PROCEDURE get_assignment_id(p_effective_date IN DATE
771 ,p_person_id IN NUMBER
772 ,p_assignment_id OUT NOCOPY NUMBER) IS
773
774 CURSOR cur_emp_assignment IS
775 select assignment_id from per_all_assignments_f paf
776 where paf.person_id = p_person_id
777 and p_effective_date
778 between paf.effective_start_date and paf.effective_end_date;
779
780 BEGIN
781 --
782 FOR cur_emp_assignment_rec IN cur_emp_assignment LOOP
783 p_assignment_id := cur_emp_assignment_rec.assignment_id;
784 END LOOP;
785
786 null;
787 END get_assignment_id;
788 --
789
790 FUNCTION get_payroll_period_start_date (p_assignment_id IN NUMBER
791 ,p_effective_date IN DATE)
792 RETURN DATE IS
793
794 CURSOR cur_payroll_start IS
795 select start_date
796 from per_time_periods ptp
797 where payroll_id in (select payroll_id
798 from per_assignments_f
799 where assignment_id = p_assignment_id
800 and trunc(p_effective_date) between effective_start_date
801 and effective_end_date)
802 and trunc(p_effective_date) between start_date and end_date;
803
804 BEGIN
805 --
806 FOR cur_payroll_start_rec IN cur_payroll_start LOOP
807 RETURN(cur_payroll_start_rec.start_date);
808 END LOOP;
809
810 END get_payroll_period_start_date;
811 --
812
813 END ghr_per_sum;
[Home] [Help]